Migration Completeness Audit (2026-06-22)
A source-exhaustive pre-staging audit of the legacy Supabase → RDP migration. Bar: every legacy element (table, column, JSON-key, storage object, row) must be migrated, dropped-with-an-explicit-reason, or flagged UNACCOUNTED — UNACCOUNTED = a silent drop = what this audit hunts. Companion to the 2026-06-15-mvp-parity-audit (which asked “is the feature present?”); this asks “is the data accounted for?” Drives corrections into migration-etl. This is a cutover-readiness gate — the data is touched once at cutover; an unflagged drop is unrecoverable later.
Method
Enumerated the source from the live Supabase (read-only audit_ro role; 24 base tables, 12 views,
8 storage buckets / 2139 objects) and the backup (tmp/range_day_supabase.backup, for the locked
auth schema + the dry-run). 14 coverage finders (10 table-clusters + storage / drift / row-reconciliation
/ auth), each adversarially verified (a second agent tried to refute every “covered” claim — a false
“covered” is a silent drop), then synthesized into one ledger and run past a completeness critic. The
transform map + skip-list were treated as a hypothesis to falsify, not ground truth.
Scope note: this covers coverage (is each element handled?), not full semantic correctness (per-item gates + the dry-run cover value-fidelity for the data-touching items). Storage = object inventory; byte-transfer fidelity is unverified (see residuals). Full per-gap evidence: Appendix A/B.
Headline
The ETL is row-complete and gated for the big tables (users count, hit_factors count, completed_drills no-shoot field — all reconcile). The risk is a long tail of silent drops with no transform read and no skip-list reason, plus a migration-etl spec that is factually wrong in four places that the next implementer would otherwise trust. Totals: 188 migrated · 78 dropped-with-reason · 82 gaps (7 high-severity).
High-severity gaps — real user data lost at cutover
Grouped into 7 themes (12 ledger line-items; see Appendix A):
- Achievements catalog + earned rows + badges. Skip-list says
achievementsis “empty (0 rows, RDP seeds 40)”; live has 49 definitions. Seed0009seeds only 40 →achievement.rs:69-74drops every earned row whose id isn’t seeded → 2,015 earned achievements silently dropped (ids 5=2012, 41=2, 42=1). The drop rationale is false on two surfaces (code msg + doc:161 “never existed”). The FK check passes precisely because the rows were dropped pre-insert; there is no source-count gate. Plusachievements.photo_url(49/49) + the 49-objectachievementsbadge bucket — reachable by no transform. - drills.loadout — gear list (130 items across 15 curated drills; keys
name/link/image) read by no transform, not in media SOURCES, absent from the skip-list. Its images are the only referencer of the 23-objectloadoutbucket. Entire gear list + media dropped. - custom_drills.custom_stage — 8,479 / 16,312 user-drawn stage layouts dropped; worse, doc:299
affirmatively but falsely states it is migrated to
drills.stage_layout(the target column exists and the app writes it; the migration does not). Related to stage-diagram-builder. - Embedded score/course images —
completed_multi_shooter_drills.shooter_results.uesrUploadedImage(6 Supabase images / 3 rows) +completed_custom_courses.course_results.uesrUploadedImage(8 images / 2 rows) — no media source for these JSON keys; silently dropped. - course_results.notes — the non-squad stage path overwrites
scores.noteswith the positional label"[Course stage N]"(score.rs:933); 187 user-entered stage notes across 62 courses 100% discarded. Recoverable user text. - auth.users.email_confirmed_at — verification state dropped;
users.verified_atis never written → all ~72k landNULL. Currently inert (EmailNotVerifiedenforced nowhere) but a latent full-base lockout if enforcement is ever switched on, with no reset path that restores it.
Critic’s additional findings (beyond the structural ledger)
- Declined/pending challenge invitations migrated as active participants.
challenge.rswrites everyuser_challengesrow as achallenge_participantregardless of status;statusis read only to decide score emission (:262). 6 declined + 43 pending = 49 non-accepted invitations recorded as joined. The doc documents only the completed→Score mapping. Value-domain gap. - Media byte-transfer has no integrity check.
transfer_objectrecordssize_bytes = downloaded lengthwith no Content-Length compare, no checksum/ETag, no read-back — a truncated download stores as “complete.” And the dry-run only does a HEAD sweep — it never fetches/uploads a byte, so it gives zero evidence the ~2,044 in-scope objects transfer faithfully. - The dry-run structurally cannot validate auth or buckets. It drops the COPY data of every non-
publicschema (0auth.usersrows restored) and stubsstorage.objectswith a fake 7-row COPY (cli/main.rs:1779). So every auth gap + every bucket gap is invisible to the existing harness — there is no auth/bucket reconciliation anywhere. - Deterministic-ID flat namespace, no collision guard.
00000000-0000-0000-0000-{N:012}is shared (unhashed, additive) across curated drills (base 1000→1001-16), dry-fire (2000→2001-05), achievements (bare 1-49), sentinels (100/102/256/2999/9999). No collision today, but fragile to future additions, no guard. - drills.is_active dropped — 1 of 16 curated drills is
is_active=false;migrate_curated+ seed read onlyid, drill_name→ that deactivated drill shows as active in RDP. - Email-merge collapse, no gate —
user.rsmerges case-insensitive-duplicate emails into one account; zero such collisions live today (verified), but no gate guards a future cutover backup that introduces one (would silently collapse two users). - Clean negatives (verified-safe): no soft-delete columns on any public table;
created_atpreserved asDateTime<Utc>; favorites have proper per-element source/skip accounting;public.users(72,033 live) vsauth.users(72,001 backup) delta is plausibly just live-vs-snapshot time drift (worth a final confirm).
Cross-cutting verification gaps (elevate every tail gap)
- No scores source-count hard gate. The largest fan-out (53,535 parents → 75,096 scores, 4 source tables)
has no source-count reconciliation; empty
shooter_results/ emptycourse_resultsconsume a parent into a done-marker without incrementingskipped(). Dormant on the current backup; the weakest point in row-accounting. - No drills-unit reconciliation — drill checks are FK-only, so every drill-unit gap (custom_stage, loadout, category, community course/qual shares) passes all gates silently.
- Dry-run PASS not independently reproduced — 576 MB plain-SQL backup, single un-re-runnable run; must be re-run from a HEAD-built binary before cutover (a stale release binary would skip 26k hit_factor rows + 2 gates).
- The skip-list doc is a stale snapshot AND wrong in places — its silence is not evidence of intent. Corrected in migration-etl (findings #8–#12 + inline ⚠️ flags on L110, L161, L299, L522, L684).
Decisions owed (James / stakeholders)
These hinge on product intent — they gate the high/medium dispositions. (Resolve like the parity audit’s decision table: each resolution → either a code fix on migration-etl or an explicit skip-list entry.)
| # | Decision | Default if no answer | Drives |
|---|---|---|---|
| D1 | Achievements: seed the 9 missing definitions (5,7,18,19,40,41,42,48,49) so the 2,015 earned rows migrate — or document why they’re excluded? | Seed them (recover the 2,015 rows) | achievement seed + earned-row migration |
| D2 | Achievement badge images (49) + loadout images (23) — migrate, or drop with reason? | Migrate (they’re referenced, live) | new media.rs sources |
| D3 | drills.loadout gear list — is per-drill loadout a shipped feature? (relates to mobile-range-bag “loadout” F9 from the parity audit) | Migrate the data | loadout transform |
| D4 | custom_stage (8,479 layouts) — migrate to drills.stage_layout now, or formally defer to stage-diagram-builder? | Migrate the data now (display later) | custom_stage transform |
| D5 | Embedded score/course images (14) — in scope? | Migrate | media.rs sources |
| D6 | Challenge participation status — preserve declined/pending vs flatten to participant? | Preserve status | challenge.rs branch |
| D7 | Challenge expiry (challenges.expires_at, 153/153) — do challenges expire in RDP? | Skip-list entry | target col or skip |
| D8 | Platform admin — re-grant the 3 is_admin + 1 is_super_admin post-migration? | Re-grant via seed | documented re-grant |
| D9 | community course/qual shares (28 + 39) — support, or drop with reason? | Skip-list entry | course.rs or skip |
| D10 | Email-verification continuity — set migrated users verified, or carry email_confirmed_at? | Set verified | user.rs |
Gaps → disposition (summary)
| Bucket | Count | Disposition |
|---|---|---|
| Fix-before-cutover (code) | 7 high themes + ~6 medium | New/extended transforms + the score-notes append fix; gated by new reconciliation checks. Lands on migration-etl. |
| Add self-defending gates | — | scores source-count, achievements source-count, empty-parent skipped++, bucket/object reconciliation, an auth-aware dry-run path, media byte-integrity (Content-Length/checksum). |
| Correct the spec | 4 false claims + 3 doc inconsistencies | Inline ⚠️ flags + findings #8–#12 on migration-etl (done by this audit). |
| Document-as-intentional-drop | ~46 low | One-line skip-list entries for denormalized / derivable / all-zero / redundant columns + JSON keys. |
| Needs product decision first | D1–D10 above | Hold in the decisions table until resolved. |
Honest residuals / limitations
- Coverage ≠ semantic correctness — per-item gates + the dry-run cover value-fidelity for data-touching items.
- Media is object-inventory only — byte-transfer fidelity is unverified (no integrity check in
transfer_object; dry-run is HEAD-only). - auth + buckets are unverifiable by the current dry-run — it drops auth + stubs
storage.objects. - Dry-run PASS + fan-out numbers not independently reproduced (no full restore performed in the audit).
- Backup-vs-live schema diff compared column NAME sets only — type/nullability/default drift across the 276 shared columns not exhaustively compared (clean spot-check; close during the pre-cutover restore).
- Live audit captured a point-in-time snapshot (2026-06-22); re-confirm against the actual cutover dataset.
Provenance
Run via a 14-finder multi-agent workflow against audit_ro (read-only Supabase) + the backup, 2026-06-22.
Working artifacts (gitignored): tmp/audit/inventory.md (shared brief), tmp/audit/ledger_full.txt (raw
ledger). Appendices A/B below are the committed, durable copy.
Appendix A — Full gap ledger (82)
HIGH (12 line-items / 7 themes)
| Element | Why UNACCOUNTED | Recommended action |
|---|---|---|
user_achievements rows for ach_id {5,41,42} (2,015) | seed 0009 seeds 40/49; achievement.rs:69-74 drops un-seeded ids; FK check passes because dropped pre-insert; no source-count gate | Seed the 9 missing defs (D1) or add source-count gate + fix false rationale |
achievements catalog table (49 live) | skip-list:110 says “empty”; no transform reads legacy achievements | Correct skip-list; decide the 9 unseeded (D1) |
achievements.photo_url (49/49) | not in media SOURCES; no target col; points at badge bucket | Add media source (D2) or live-accurate skip entry |
achievements bucket (49 PNG) | referenced only by photo_url, read by no transform | Add media source (D2) or document drop |
drills.loadout (130 items / 15 drills) | read by no transform; not in media SOURCES; absent from doc | Add loadout media+content source (D2/D3) or skip entry |
loadout bucket (23) | referenced only by drills.loadout[].image | Same as loadout column |
custom_drills.custom_stage (8,479) | read by no transform; target col exists; doc:299 falsely claims migrated | Implement stage_layout write (D4) or correct doc:299 |
| doc:299 false “custom_stage→stage_layout” claim | active false “covered” in the authority doc | Correct doc:299 regardless of code decision |
MSD shooter_results.uesrUploadedImage (6 imgs / 3 rows) | no media SOURCES entry for shooter_results | Add media source (D5) or document |
CCC course_results.uesrUploadedImage (8 imgs / 2 rows) | no media SOURCES entry for course_results | Add media source (D5) or document |
CCC course_results.notes (187 / 62 courses) | non-squad path overwrites notes with “[Course stage N]” (score.rs:933) | Carry notes (append) in the non-squad branch |
auth.users.email_confirmed_at (72001/72001) | verified_at never written → all NULL; latent lockout if enforced | Write verified_at (D10) + skip-list entry |
MEDIUM (~24)
| Element | Why | Action |
|---|---|---|
custom_drills.is_drill (230 false) | read by no transform; no WHERE; possible mis-route of 230 non-drills | Investigate; route/skip + entry |
community_drills.course_id (28) | polymorphic; only drill_id handled | course-share handling (D9) or skip; fix doc:303 |
community_drills.qual_id (39) | no qual handling anywhere | Decide quals↔drills (D9); skip entry |
custom_drills.drill_steps[].stepValue | build_steps reads only stepDescription | Investigate; carry or skip entry |
custom_drills.drill_steps[].shotsOnTarget | same | Investigate; carry or skip entry |
validation.rs no drills-unit source-count gate | drill checks FK-only; all drill gaps pass silently | Add custom_drills source-count + population gates |
completed_drills.drill_steps (40,065) | not in SELECT (score.rs:595-598) | Decide; SELECT+target or skip entry |
MSD shooter_results.steps (4,406) | never read | Decide; transform or skip entry |
CCC course_results.steps (270) | never read | Decide; transform or skip entry |
challenges.participants (1 array-only) | participants rebuilt from user_challenges; 1 array-only member dropped | Union array-only member or add count-recon gate |
challenge_participants completeness | same root (335 vs 340 distinct) | Same |
user_challenges.drill_steps (127) | not in SELECT | Decide; transform or skip entry |
push_press.notification_title (6/6) | read by no transform; no target | Carry to content_entries or skip entry |
push_press.notification_body (6/6) | read by no transform; no target | Carry or skip entry |
push_press bucket (22) | no media source; ~6 row-referenced, ~16 orphan | Add source or bucket-drop entry |
custom_courses.courses[].parTimeForCustom | per-stage par snapshot; 2,081 deleted-drill elements carry only par | Decide override col or skip entry |
auth.users…is_admin (3) | platform admin; no landing concept | Re-grant decision (D8) + document |
auth.users.is_super_admin (1) | same family | Re-grant (D8) + document |
| DRY-RUN result not independently reproduced | single un-re-runnable run; HEAD-binary dependent | Re-run from HEAD binary before cutover |
| scores EntityStat — no source-count gate | empty-parent paths drop intent with no skipped++/gate | Add source-count gate + empty-parent skipped++ |
| schema_drift: media doc premise stale | doc:510-522,684 contradicts shipped media.rs | Rewrite doc media section to match media.rs |
LOW (~46) — mostly “add a one-line skip-list entry”
Denormalized / derivable / all-zero / redundant, low or nil data-loss risk; converting each to
dropped_with_reason closes the gap:
drills.category (16; seed omits — add to seed or entry) · users.deleted_custom_drills (all 0) ·
completed_drills.target_scores.time (8,582; per-target time collapsed) · completed_drills.category
(48,647) · completed_drills.distance_metric (48,647) · completed_drills.is_community_drill (137) ·
MSD par_time/unique_id/shooter_count/distance_metric (4,406 each) · CCC course_title (525) /
stage_count / is_msd (255) / shooter_count / is_community_course (5) · CCC course_results
.descriptionForCustom (440) / .parTimeForCustom (525) / .distanceMetric (525) / .uesrUploadedVideo
(0 live) · MSD shooter_results.uesrUploadedVideo (0 live) · challenges.expires_at (153; D7) /
participant_count (153; recomputable) · user_challenges.user_tag (341) / is_notification_open (341) /
drill_steps sub-keys · push_press.dry_fire_drill_id (0/6) / social / trex_messages / new_drills /
sales (6 each, push-audience flags) · Private bucket (1, branding asset) · friendships.is_notification_open
(279t/226f; product call) · custom_courses.stage_count (derivable) · custom_courses.courses[]
.descriptionForCustom / .drillName / .steps / .targets / .distanceMetric / .notes /
.{alpha,charlie,delta,miss,noShoot} / .{totalPoints,totalTime,hitFactor} / .DTBDresults (all on 4,414
elements; redundant for surviving drills, lost for 2,081 deleted-drill elements) · schema_drift: type/nullability
not compared across 276 cols (close at restore) · schema_drift: CREATE-TABLE-parse assumption (confirm at restore) ·
doc:303 community polymorphic rows undocumented · doc:680 vs :510 push_press.drill_id inconsistency.
Appendix B — Dropped-with-reason ledger (documented exclusions, ~90)
The deliberate, reasoned exclusions (recorded so they aren’t re-investigated). Highlights by group:
- users:
trex_notifications/new_item_notifications/sales_notifications/opt_in_emails(marketing prefs, read only to feed the discard counter) ·fcm_token(skip doc:248, stale Flutter tokens) ·phone_number(dropped per Friend-Discovery decision) ·is_influencer(KILL Decision #2) ·dry_fire_drill_count(sync-only, reset at cutover, doc migration-sync.md:378). - completed_drills / MSD / CCC: per-row
alphaValue..noShootValuezone overrides (hardcoded USPSA profile, doc:354/393/420) ·drill_description(doc:632/679 dead column) ·target_scores.name(per-target label, 5-zone aggregation doc:360) ·shooter_results.DTBDresults/course_results.DTBDresults(doc:398, no RDP equivalent) · CCCcourse_results.{uesrUploadedImage,Video}(0 non-empty/0 http live) · CCC stage-content loss for deleted drills (course.rs:100-105, logged). - drills / dry_fire_drills (curated, seed-authoritative): rows not row-copied (id-map only);
created_at,drill_targets,drill_steps,last_update_time,index,video_url_youtube(non-Supabase host, no target). - community_drills: rows not entity-migrated (visibility-only UPDATE);
created_at,added_by,drill_owned_by,drill_owner_name,itemName,is_favorited(no destination; shares ≠ copies). - courses (0 rows): entire table empty (skip-list:109). in_app_messages (97): 100%
is_challenge=true, verified live (content.rs:8-11, doc:508). notifications (8,152): ephemeral, not outbox-captured, regenerated. influencer_followers (2): KILL Decision #2. audit_log (0): unused. drills_test_table (44) / dry_fire_drills_test_table (14): dev artifacts, verified FK-safe to drop. - push_press:
image_url/video_url/link/drill_id(doc:510/680 discard list; caveat: drill_id named only at :510). - friendships.id (composite-PK redesign, no surrogate) · user_achievements.id (natural PK). · achievements catalog {id,title,description,category} for the 40 seeded (re-authored in seed 0009; the 9 unseeded fold into the HIGH gap).
- Views (12): all pure projections over migrated base tables (verified via
pg_get_viewdef); skip-list:116. - auth schema (backup):
encrypted_password(→ NULL, reset-on-login, doc:241) ·identities(100% email, 0 OAuth) ·mfa_*(0 factors) ·webauthn_*(0) ·phone*(0) ·sessions/refresh_tokens(ephemeral) ·last_sign_in_at,banned_until/deleted_at/is_anonymous(all 0 / operational) ·flow_state,one_time_tokens,oauth_*/saml_*/sso_*,instances,audit_log_entries,schema_migrations(GoTrue internals). - schema diff: 24 backup tables 24 live; 276 backup columns 276 live (byte-identical name sets, no add/drop).