# Day-3 Phase B — Single-file content-port migration drafted (2026-05-12)

**Branch:** `day3-content-migration`. **Drafted, NOT deployed.**
**NOT run locally yet** — that's Phase C.

Halt status: written, php-lint clean, JSON bundle loads correctly via
`ReflectionClass` smoke test. Holding for Pam's review before Phase C
local test.

## File

- **Path:** `database/migrations/2026_05_12_091938_seed_missing_diag_content.php`
- **Size:** 180,479 bytes (most of that is the inline JSON data bundle)
- **`php -l` result:** `No syntax errors detected in database/migrations/2026_05_12_091938_seed_missing_diag_content.php`

The file is self-contained: it carries its own ~170KB data bundle inline
as a nowdoc-quoted JSON literal parsed in `__construct()`. No sibling
files are required at migration time.

## Data bundle counts (verified via ReflectionClass instantiation)

```
TRACK_DATA:     58
SKILL_DATA:     167
AMBIG_RESOLVED: 5
PIVOT_PAIRS:    162
QUESTION_DATA:  167
TAG_PROD_IDS:   64
```

These match the locked Phase B0 spec (`ops/preflight-fk-verify-2026-05-12.md`):

| spec | bundle | reconciled |
|---|---:|---|
| 58 in-scope tracks (3 insert + 55 resolve to existing) | 58 | ✓ |
| 167 in-scope skills (28 insert + 134 resolve + 5 ambig) | 167 | ✓ |
| 5 ambig-resolved overrides | 5 | ✓ |
| 162 in-scope pivots | 162 | ✓ |
| 167 in-scope questions to insert | 167 | ✓ |
| 64 prod IDs to UPDATE is_diagnostic=1 | 64 | ✓ |

## Helper function signatures

```php
public function __construct(): void
//   Loads the embedded JSON bundle into the six private array properties.

public function up(): void
//   DB::transaction wrapping:
//     $trackMap = $this->processTracks();
//     $skillMap = $this->processSkills();
//     $this->processPivots($trackMap, $skillMap);
//     $this->processQuestions($skillMap);
//     $this->tagCleanMatches();

public function down(): void
//   Forward-only no-op with explanatory comment. Roll back via DB dump.

private function processTracks(): array
//   For each of 58 tracks: MD5(LOWER(TRIM(track))) + prod field_id match.
//   If existing, reuse prod id. Else insertGetId with prod-resolved
//   field_id/level_id. Returns local_id => prod_id.

private function processSkills(): array
//   For each of 167 skills: if local_id in AMBIG_RESOLVED, use the
//   pre-bridged prod_id. Else MD5(LOWER(TRIM(skill))) match, else insert.
//   Returns local_id => prod_id.

private function processPivots(array $trackMap, array $skillMap): void
//   For each of 162 (local_skill, local_track) pivot pairs: resolve both
//   to prod ids via the maps; INSERT into skill_track unless the pair
//   already exists. Throws if either side fails to resolve.

private function processQuestions(array $skillMap): void
//   For each of 167 questions: resolve skill via skillMap; MD5(TRIM(
//   question)) match; INSERT with is_diagnostic=1 if absent. All ~20
//   content columns carried verbatim from local.

private function tagCleanMatches(): void
//   UPDATE questions SET is_diagnostic=1 WHERE id IN (64 prod IDs)
//   AND is_diagnostic=0 — idempotent.

private function resolveFieldId(string $fieldName): int
//   Cached LOWER(TRIM(field)) lookup against fields WHERE status_id=3.
//   Throws if not found (caught by transaction → all-or-nothing rollback).

private function resolveLevelId(int $levelInt): int
//   Cached lookup on levels.level. Throws if not found.
```

## Idempotency design

Every insert is gated by a content-hash existence check first:
- **tracks** by `MD5(LOWER(TRIM(track)))` AND `field_id` AND `status_id=3`
- **skills** by `MD5(LOWER(TRIM(skill)))` AND `status_id=3` (with explicit
  `AMBIG_RESOLVED` overrides for the 5 cross-track-bridged cases)
- **skill_track pivots** by `(skill_id, track_id)` pair existence
- **questions** by `MD5(TRIM(question))`
- **tag UPDATEs** by `is_diagnostic=0` WHERE clause

Running the migration a second time on a fully-applied prod is therefore
a no-op (a few SELECTs, no INSERTs/UPDATEs).

## Transaction boundary

All five steps execute inside one `DB::transaction(function () { ... })`.
Any insert or update failure rolls back the whole port — prod ends in
the same state it started. The Laravel-level migration record is also
backed out by the transaction.

## What this migration deliberately does NOT do

- Does **not** include the 12 ambiguous-multi-match questions. Pam
  decided to defer those.
- Does **not** insert 4 stale tracks (47, 49, 55, 65) or anything that
  hangs off only those tracks (1 question, 1 skill, 6 pivots dropped).
- Does **not** fix the typo skills ("witin", "rectlinear", etc.) —
  deferred to a post-beta follow-up migration.
- Does **not** insert the 14 local diag rows reaching no public field
  (they would never be served anyway).
- Does **not** ship new Word Problems content. Per Option 1: accepts
  WP at 8 reachable on prod for beta.

## Phase C precondition checklist

Before Phase C runs:
- [ ] Pam reviews the migration file (or this report) and signs off.
- [ ] `mysqldump` local snapshot taken FIRST so Phase C step 6 (restore)
      is possible.
- [ ] The reset-to-prod-like SQL script must zero `is_diagnostic` on the
      same 64 tag-target IDs AND drop the 167 unmatched local questions
      AND undo the local skill/track/pivot rows that exist only locally
      (so local mirrors prod's missing-content state at port time).

## Files added this phase

- `database/migrations/2026_05_12_091938_seed_missing_diag_content.php`
- `ops/content-migration-draft-2026-05-12.md` (this report)

Also worth noting: a reproducibility helper exists at
`ops/.tmp-generate-migration.php` (uncommitted by default — it's a
build-time script, not a deliverable). It rebuilds the migration file
from `ops/dep-scope/migration-data-bundle.json` if Pam wants to
regenerate after data tweaks.

## Halt

Phase B complete. Awaiting Pam's sign-off before Phase C local test.