# Database Diagnostics — `api` (production-shaped dev DB)

**Run:** 2026-05-08 16:57:09 SGT · **Connection:** `mysql -u root -p'…' api` (MySQL 8.0.30) · **Read-only.** No writes, no schema changes.

---

## 1. Role seed conflict resolution

```sql
SELECT id, role FROM roles ORDER BY id;
```

```
id  role
 1  Administrator
 2  Principal
 3  Department Head
 4  Teacher
 5  Non-editing Teacher
 6  Student
 7  Parent
 9  System Admin
10  QA Reviewer
11  Content Creator
12  Partner Admin
13  Analytics Viewer
16  QA Lead
17  Publisher
18  QA Editor
```

**Interpretation:** `id=6` is `Student`, matching the legacy `RoleSeeder.php` ordering and the Phase 1A `OTPService::findOrCreateUser` hardcoded default — Phase 1A's role assignment is correct in this DB. The migration-based seed (which would have placed `student` at `id=2`) did not win against the existing data; the divergence noted in the Phase 1A comment block is therefore a documentation concern only on this instance, not a live mis-assignment.

---

## 2. Phase-0-introduced broken signups

```sql
SELECT COUNT(*) AS null_role   FROM users WHERE role_id IS NULL;
SELECT COUNT(*) AS null_access FROM users WHERE access_type IS NULL;
SELECT id, email, phone_number, role_id, access_type, created_at
FROM users
WHERE role_id IS NULL OR access_type IS NULL
ORDER BY created_at DESC LIMIT 20;
```

```
null_role
0

null_access
0

(no rows for the LIMIT 20 query)
```

**Interpretation:** Both counts are zero — Phase 0 has not yet been deployed to this DB, so no users were created with null privileged fields and **no `BackfillSignupDefaults` command is needed**. Once Phase 0 + Phase 1A deploy together (i.e. the mass-assignment trim and the OTPService restore land in the same release), this stays zero by construction.

---

## 3. BackfillLivesRegen affected user count

```sql
SELECT COUNT(*) AS stuck_users
FROM users
WHERE lives = 0 AND lives_restore_queue IS NOT NULL AND JSON_LENGTH(lives_restore_queue) > 0;

SELECT id, email, lives, JSON_LENGTH(lives_restore_queue) AS queue_size
FROM users
WHERE lives = 0 AND lives_restore_queue IS NOT NULL AND JSON_LENGTH(lives_restore_queue) > 0
ORDER BY id LIMIT 10;
```

```
stuck_users
0

(no rows for the sample query)
```

**Interpretation:** Zero stuck users — for the same reason as section 2, Phase 0 hasn't deployed here, so the lives-stuck-at-zero regression hasn't had a chance to manifest. `php artisan lives:backfill-regen --dry-run` will report "Affected users: 0" against this DB; the command remains valuable for production once Phase 0 deploys, but is a no-op locally.

---

## 4. `users.auth0` column inhabitants (drop-migration safety check)

```sql
SELECT COUNT(*) AS rows_total, COUNT(auth0) AS rows_with_value FROM users;
SELECT id, email, auth0 FROM users WHERE auth0 IS NOT NULL LIMIT 5;
```

```
rows_total  rows_with_value
       326                0

(no rows for the WHERE auth0 IS NOT NULL query)
```

**Interpretation:** All 326 users have `auth0 IS NULL` — the column was added by the 2024-05-09 migration but never populated, confirming AUTH0_USAGE_REPORT.md's finding that the OTP cutover happened before any Auth0 IDs got written. **The Phase 1 `drop_unused_auth0_column_from_users_table` migration is safe to ship as-is**: dropping `auth0` and the `users_auth0_unique` index destroys no data.