# Day-3 Phase A — Dependency scope for diag-question inserts (2026-05-12) **Branch:** `day3-content-migration` (off master). **Read-only this phase** — no prod writes, no pushes. Halt after this report; wait for Pam's sign-off before Phase B. ## TL;DR - Yesterday's "178 unmatched-in-public-field" is actually **168**. Total unmatched = 182, of which 14 reach no public field (so they would never be served anyway) → **168** that need actual insertion to make local's diagnostic pool reachable on prod. - FK chain is shallow: levels → tracks → skills → skill_track → questions. No circular FKs. - **Minimum insert volume ≈ 247 rows** across 4 layers (no level inserts needed — all 6 referenced levels already exist on prod). - **3–4 stale/test tracks** in the missing set look like data-quality rejects rather than real content. Pam decision needed before insert. ## Phase A.1 — IDs to insert (saved) - `ops/dep-scope/local-diag-ids-to-insert.json` — `{count: 168, local_ids: [...]}` - `ops/dep-scope/local-fk-roster.json` — full local roster per layer - `ops/dep-scope/local-content.json` — local row content per layer (for diffing) - `ops/dep-scope/level-mapping.json` — local_level_id → prod_level_id|null - `ops/dep-scope/track-mapping.json` — local_track_id → prod_track_id|null - `ops/dep-scope/skill-mapping.json` — local_skill_id → prod_skill_id | [prod_id, ...] (ambig) | null (missing) - `ops/dep-scope/skill-track-mapping.json` — full pivot resolution (exists | missing | unresolvable) ## Phase A.2/A.3 — Per-layer scope and matching Matching strategy: - **Levels**: by `level` integer (small enough that integer match is unambiguous in this set). - **Tracks**: by `LOWER(TRIM(track))` + field name (compound, since field IDs differ across local/prod). - **Skills**: by `LOWER(TRIM(skill))` (no track context yet — see ambig column below). - **skill_track**: derived — given resolved (prod_skill_id, prod_track_id), check existence on prod. - **Questions**: already MD5-content-matched yesterday — 168 truly missing. | Layer | local count | EXISTS_ON_PROD | MISSING_ON_PROD | ambig / unresolvable | |---|---:|---:|---:|---:| | L1 levels | 6 | **6** | 0 | 0 | | L2 tracks | 62 | 55 | **7** | 0 | | L3 skills | 168 | 134 | **29** | 5 ambig (name-dup on prod) | | L4 skill_track | 168 pivot rows | 125 | 2 | 41 unresolvable* | | L5 questions | 168 | 0 | **168** | — | \* "Unresolvable" L4 pivots = local pivot rows where either the local skill_id or local track_id failed to resolve to a single prod_id at L2/L3. They become insertable once their L2/L3 inserts land. So all 168 local pivots are ultimately insertable — just with new prod IDs on one or both sides. ## Phase A.4 — Estimated insertion plan | Layer | row inserts | notes | |---|---:|---| | L1 levels | **0** | all 6 referenced levels exist on prod | | L2 tracks | **7** | minus any rejected as stale data — see flags below | | L3 skills | **29 + decisions on 5 ambig** | ambig resolution: pick prod skill via track context, treat as new, or accept dup | | L4 skill_track | **~43** | 2 missing pairs + 41 pivots that depend on L2/L3 inserts; final count = 168 minus the 125 that already exist | | L5 questions | **168** | the original goal | | **Minimum total** | **≈247 rows** | | If Pam excludes the 3–4 stale tracks (see flag #2 below), the count drops to roughly 244 with proportionally fewer dependent skill/pivot/ question rows. ## Phase A.5 — Flags / cascade complexity ### 1. No circular FKs, no self-references in scope - `questions.original_question_id` is non-null for 0 of the 168 in-scope questions, so no self-reference recursion to handle. ### 2. Likely stale data in the 7 missing tracks Of the 7 missing tracks, several look like dead data that probably should NOT be inserted onto prod: | local_id | field | track name | smells like | |---:|---|---|---| | 42 | Measurement | "Area and Perimeter Year 4" | real | | 44 | Geometry & Spatial Reasoning | "Rectangle and Square" | real | | 47 | Number & Algebra | `"Numbers up to 10\nmillion"` (embedded newline) | **data quality bug** | | 49 | Number & Algebra | "Order of Operations to 10 million* Not in use" | **flagged stale by author** | | 55 | Measurement | "Length, mass and volume Year 5 - no more in use" | **flagged stale by author** | | 59 | Geometry & Spatial Reasoning | "Triangles" | real | | 65 | Measurement | "Speed - ?" | **flagged unfinished by author** | 3–4 of these probably shouldn't ship to prod as-is. Recommendation: exclude tracks 47, 49, 55, 65 from insertion (and any questions/skills that hang off only those tracks); insert 42, 44, 59. ### 3. Ambiguous skills — 5 rows where name matches >1 prod skill ``` local 77 "equivalent fractions" -> prod [77, 377] local 106 "reading and writing numbers in numerals and in words" -> prod [30, 106] local 107 "comparing and ordering numbers" -> prod [31, 107] local 132 "rounding off answers to a specified degree of accuracy" -> prod [132, 168] local 168 "rounding off answers to a specified degree of accuracy" -> prod [132, 168] ``` Pattern: same skill seeded twice on prod (probably once in 2018, once later) — these are intra-prod dups, not local dups. local 132 and local 168 both have the same name (local also has the dup). Resolution options: - **(a) Pick one prod_id per name via track-context bridge** — for each ambig local skill, look at which track it sits in; the prod skill in the same-named prod track wins. - **(b) Treat as missing → insert 5 new prod skill rows** — cleanest but balloons future intra-prod dup count. - **(c) Accept the existing dup; pick the lower prod_id by convention.** Phase B should default to (a) and surface the residual unresolvables. ### 4. Sample missing skills (29 total, first 8) ``` local 17 "use of x" local 38 "relationship between multiplication and division" local 57 "use of abbreviated h and min" local 81 "adding and subtracting two related fractions witin one whole..." (typo: "witin") local 84 "measuring length in km and volume in ml" local 96 "perimeter of rectlinear figure, rectangle and square" (typo: "rectlinear") local 101 "draw perpendicular and parallel lines on square grid" local 103 "using different scales on axis" ``` Several have typos. Pam may want a manual editorial pass before insert. ### 5. The 2 cleanly-missing skill_track pivots ``` local: skill_id=169 track_id=52 -> prod: skill_id=153 track_id=52 (both ends exist on prod, just not paired) local: skill_id=386 track_id=8 -> prod: skill_id=1 track_id=8 ``` These are pure pivot inserts — no skill/track inserts needed. ### 6. Secondary FK dependencies on `questions` The 168 in-scope questions reference: - `difficulty_id` ∈ {1, 2, 3} — very small set, almost certainly all exist on prod (need a quick verify before Phase B). - `type_id` ∈ {1} — single value, almost certainly exists. - `user_id` ∈ {1, 2} — admin curator. **Need to verify prod has matching user_id values, or remap to a single prod admin user.** - `original_question_id` — all NULL in scope. Not a concern. ### 7. Status_id is portable All in-scope local rows have `status_id = 3` (public). The `statuses` table values (1 Only Me, 2 Restricted, 3 Public, 4 Draft, 5 Archived as of yesterday) match across local and prod. No remapping needed. ### 8. Field bridge (already established yesterday) ``` local_field 37 ("Number & Algebra") -> prod_field 31 local_field 38 ("Measurement") -> prod_field 32 local_field 39 ("Geometry & Spatial Reasoning") -> prod_field 33 local_field 40 ("Statistics & Probability") -> prod_field 34 local_field 41 ("Word Problems & Applications") -> prod_field 35 ``` ## Phase B+ proposal (for Pam to scope) Two reasonable paths: **Option α — Build a single artisan command** (`php artisan diag:port-content`) that, given the JSON maps above and a Pam-approved edits-file (which tracks to drop, which ambig resolutions to pick), performs the cascade insert in a single DB transaction. - Pro: easy to dry-run, reversible if transaction fails. - Con: complex, lots of code, longer to write. **Option β — Split into per-layer migrations** (`tag_levels`, `insert_missing_tracks`, `insert_missing_skills`, `insert_skill_track`, `insert_diagnostic_questions`). Each migration is a typed, content-addressable seeder. - Pro: incremental, easy to halt/diff between layers. - Con: 4–5 migration files, more boilerplate. Recommendation: **α** for one-shot porting; β for ongoing parity. ## Halt Phase A complete. Holding for Pam's review. Decisions needed before Phase B: 1. Confirm number is 168, not 178 (and acknowledge if my earlier "168" carried through yesterday's reports too — it did). 2. Approve or revise the stale-track exclusion list (47, 49, 55, 65). 3. Pick ambig-skill resolution strategy: (a) track-context bridge, (b) insert new, or (c) lower-id wins. 4. Decide editorial pass on missing skill typos: now or later. 5. Pick Phase B implementation strategy: α (one-shot command) or β (per-layer migrations). 6. Verify the difficulty_id/type_id/user_id assumptions on prod (the one read-only follow-up Phase A should arguably have done — happy to run if you want before Phase B kicks off).