Skip to content

SOP-059 — IaC / schema migration apply (Platform Engineer)

Guide

<!-- last-reviewed: 2026-06-01 · owner: Engineering (Platform Engineer) -->

SOP-059 — IaC / schema migration apply (Platform Engineer)

SOP · owner: Engineering (Platform Engineer). Layer: SOP/Runbook (docs/canon.md). Applies a Neon/Drizzle schema change and keeps the intranet’s local table re-declaration in sync. The db/schema.ts contract is Data & Analytics’; the apply path is ours (ADR-0014, linked not restated).

Interface Contract

  • Inputs: schema change (db/schema.ts) ← from <Data & Analytics Director (SOP-060/061 spec)> · trigger: requirements issued
  • Outputs: applied migration + synced intranet/src/lib/db.ts → to Engineering Director (review) · trigger: review requested
  • Outputs: dependency added/removed/changed → to Legal Director · trigger: dependency changed
  • Handoff trigger (in): a schema change is specified in the db/schema.ts contract and ready to apply
  • Handoff trigger (out): the migration runs cleanly, the local re-declaration matches, and drift-check passes
  • Definition of Done before handoff:
    • [ ] Migration applied against Neon via DATABASE_URL_UNPOOLED (migrations only, ADR-0014)
    • [ ] intranet/src/lib/db.ts re-declaration updated to match the changed tables
    • [ ] node scripts/check-schema-drift.mjs → [schema-drift] OK (column presence)
    • [ ] Column types/nullability eyeballed against db/schema.ts (the guard checks presence only — Phase 0 F2)
    • [ ] cd intranet && npm run build green
    • [ ] Any new dependency flagged to Legal (dependency changed); migration note captured to docs/knowledge/

Trigger phrases drawn from docs/handoff-triggers.md. requirements issued is the Data & Analytics → Engineering schema-spec hand-down; dependency changed is the Engineering → Legal license-re-audit edge (Wave 1 additions). We implement the apply path; we never alter the schema contract or scoring logic.

Purpose

Apply a schema change safely and keep the two homes of the schema (db/schema.ts canonical, intranet/src/lib/db.ts bundle-local) in sync — so the static build and the Vercel Functions read the same shape.

When to use

When Data & Analytics specifies a schema change in the db/schema.ts contract and it is ready to apply. Migration logic is theirs; you run the apply path.

Flow

flowchart LR
  A["requirements issued<br/>(schema change)"] --> B["apply migration<br/>(UNPOOLED)"]
  B --> C["sync intranet/src/lib/db.ts"]
  C --> D{"check-schema-drift<br/>OK?"}
  D -->|no| C
  D -->|yes| E["build green →<br/>review requested"]
  classDef s fill:#1d4ed8,color:#fff,stroke:#1e3a8a
  classDef d fill:#0f766e,color:#fff,stroke:#134e4a
  class A,B,C,E s
  class D d

Text fallback: a schema change arrives (requirements issued) → apply the migration against Neon using DATABASE_URL_UNPOOLED → update the intranet-local db.ts re-declaration → run check-schema-drift.mjs until OK → build green → hand up for review (review requested).

Steps

  1. Read the contract. The schema change is defined in db/schema.ts (Data & Analytics-owned). Do not alter it; implement the apply path only.
  2. Apply the migration against Neon via DATABASE_URL_UNPOOLED (migrations only — never the pooled DATABASE_URL, ADR-0014).
  3. Sync the bundle-local re-declaration. Update intranet/src/lib/db.ts to match the changed tables (it re-declares only the tables the API routes need, so the Vercel Function bundle stays self-contained).
  4. Run the drift guard: node scripts/check-schema-drift.mjs → expect [schema-drift] OK. The guard checks column presence only — eyeball types/nullability against db/schema.ts (Phase 0 F2).
  5. Build: cd intranet && npm run build → green.
  6. Flag dependencies + capture. If a dependency changed, flag Legal (dependency changed). Capture the migration note to docs/knowledge/. Hand up for review (review requested, SOP-054/SOP-058).

Roles / approvers

Run by the Platform Engineer. Schema contract owner: Data & Analytics Director. Review: Engineering Director (SOP-054). Secrets (DATABASE_URL_UNPOOLED): Operations + Security (SOP-004) — never in-repo.

Guardrails

  • DATABASE_URL_UNPOOLED for migrations only; pooled DATABASE_URL for queries (ADR-0014). Do not cross the two.
  • No schema-contract or scoring-logic changes — Data & Analytics owns those.
  • No secrets in the repo (SOP-004); the connection string is a Vercel/Neon env var.
  • API security thresholds are policy — Security sets them (unstaffed → Principal); you implement guidance.
  • Do not restate ADR-0014 — link to it.

Job aid: deploy/verify steps in docs/units/engineering/job-aids/JA-058-deploy-intranet-steps.md; migration-apply is captured in this SOP’s Steps (no separate job aid yet — GR-4, will add if the apply path recurs enough to warrant a cheat-sheet). See also: docs/units/engineering/manuals/platform-engineer.md · SOP-058 · ADR-0014 · scripts/check-schema-drift.mjs · docs/handoff-triggers.md.