# 08a — Data Integrity · Unified Platform (2026-09-18)

**Method:** live `supabase-db` probes (schema, migrations log, FK inventory, orphan counts, row-class checks). All queries read-only.

## Migrations
- Boot-run, fail-loud (`unified._migrations` log; per-pool tracking for roster/milestone DBs).
- **All applied** — latest `031_reimb_saved_views.sql` (2026-09-17 12:10). 030/031 (reporting rebuild) verified applied.
- `/api/health` `migrations:true`.
- ⚠️ Two prefix collisions: `026_rename_access_columns_to_roster` + `026_grand_rounds_tb`; `027_eoy_evaluations` + `027_grand_rounds_send_tracking` (all applied — tracked by filename; rename for sanity before numbering matters). P3.

## Referential integrity (verified clean)
- 12 FKs in `unified` schema; **orphan counts = 0** for: `reimb_allocations→persons`, `reimb_submissions→persons`, `reimb_files→submissions`.
- `reimb_files.uploaded_by → app_access(email)` FK present (cascade) — good hygiene for upload provenance.

## Row-level data-quality findings
| # | Finding | Evidence | Sev |
|---|---|---|---|
| DI-1 | **9 active `reimb_persons` rows have no email/EZ** — 5 residents (Cheng, Nauheim, Loloi, Wallace, Dave, created 2026-08-12) + Sabin Sajin duplicated ×2 (faculty-typed, empty). These ghosts can never authenticate and pollute "active residents" counts (21 includes them). | DB probe | P2 |
| DI-2 | **2 active reimb residents are CRM-`Archived`** (Karki, Kassam — archived 2026-07-14, after PGY-5 graduation). Their reimb rows remain `is_active` → still counted in active resident ledgers. If either needs access again, login is hard-blocked by the Archived gate. | DB probe | P2 (link to CR-2 role regression) |
| DI-3 | 1 junk `app_access` row with empty email + 2 `contacts` rows with empty email (all-false flags). | DB probe | P3 |
| DI-4 | Leftover backup tables inside the live schema: `reimb_allocations_backup_20260801`, `reimb_persons_cls_backup_20260827`. | schema list | P3 |
| DI-5 | `reimb_persons.ez_id` format variance (`517772` vs contacts' `000517772`) breaks JZ-ID joins used by tooling; only email joins are reliable. | Danzig probe | P3 |

## Backup / DR coverage (verified)
- `backup.sh` (daily 03:05, retention 14d): 4× pg_dump (`postgres`, `milestone_evaluations`, `or_schedule`, `urology_roster`) + SQLite file + `uploads/` + CRM json + `.env`. **Ran healthy today** (`unified-20260918-030501`, 3.8 MB). `/root/backup-db.sh` also runs 03:00. Manual probes of the backup path succeeded (Aug-20 FIX-13 evidence stands).
- Gap noted in 02/03: **code itself has no automated backup to git** (37 unpushed commits; lost-fix incident) — process finding, not a data-store gap.

## Cleared
- Call-schedule data current: `public.call_schedule` covers 2026-07-01 → 2027-01-03 (561 rows).
- No seed-vs-live drift trap: health seed check green (the Aug 503 class does not recur).