Data Model

Skill: data-model

End-to-end runner for the persistence cut: data model + migrations + drift check in one pass. Greenfield-first by default; brownfield delta as --mode brownfield. Output is shippable — full .up.sql + .down.sql, not a plan — but staged under docs/features/<slug>/migrations/, never written into the live migrations/ tree. implement promotes the staged pair into migrations/ (with the real sequence number / timestamp) only when the feature is actually being built. This is deliberate: data-model is a design stage four steps before implement, so a stray migrate up (a teammate's loop, CI, a deploy) must not be able to apply a half-designed schema to a real database. (Same staging discipline the drift fixes already use under _drift/.)

Stack-agnostic by design — it imposes no DB philosophy and writes no rules file. data-model derives the DB + migration conventions from the architecture — architecture-map.md (the migration tool/naming survey recorded) + the sad.md persistence decisions (§4 strategy / §5 building blocks / §8 crosscutting) + the Accepted ADRs — and follows them; the live migrations/ + schema corroborate and fill anything the architecture left implicit. On a greenfield repo with no architecture signal, it confirms each schema choice with the user (Socratic) instead of defaulting to a house style. What it applies regardless of stack is migration safety (staging, reversibility, FK indexes, zero-downtime decomposition, no-PII) — never a stance on updated_at vs not, hard vs soft delete, UUID vs sequence, or whether CHECK constraints are allowed. The size matrix (→ ../_shared/size-matrix.md) governs how much you produce; the aggregate-roots dialogue uses ../_shared/ask-style.md.

data-model.md prose follows artifact_language — SQL, table/column identifiers, headings and frontmatter stay English → ../_shared/artifact-language.md.

Owner

Backend Lead.

Inputs

• <slug> — feature slug.
• Gate (hard refuse if missing): docs/features/<slug>/spec.md (entities live in §5 acceptance criteria) and docs/features/<slug>/sad.md (entity candidates: §5 building blocks — the persistence containers — + the §6 persist notes). Missing → «run specify / design first».
• Optional: the sequence diagrams in sad.md §6 — each writes/reads <entity> note is an index candidate (one index per query, justified).
• (Expected) sad.md frontmatter target_surfaces — context for which containers persist what. Absent or empty → warn («surfaces undeclared — re-run design, or proceeding as backend-service») and treat as [backend-service] (→ ../_shared/surfaces.md).
• Convention source: docs/architecture-map.md §Migrations (from survey) + the sad.md persistence decisions (§4/§5/§8) + Accepted ADRs — the migration tool/naming + the DB approach are derived from here, not invented (the map also gives module layout, saving a re-scan). For drift detection specifically, explorer still reads the actual domain layer (the map gives layout; drift needs the real struct/field source). Stack-agnostic — no hard-coded path or language.

Conventions — detect and follow (stack-agnostic)

data-model imposes no DB philosophy. For each topic below it follows the architecture's decision — architecture-map.md §Migrations + the sad.md persistence decisions + Accepted ADRs — corroborated by the repo's existing migrations/schema; on a greenfield repo with no signal it confirms the choice with the user. It never legislates a house style.

What it does apply regardless of stack — migration safety, not DB philosophy:

Protocol

1. Prereq check (hard). Both spec.md and sad.md present, else refuse with a pointer to the missing one.
2. Derive conventions from the architecture (read-only — never write a rules file). Read architecture-map.md §Migrations (the tool/naming survey recorded) + the sad.md persistence decisions (§4 strategy / §5 building blocks / §8 crosscutting) + the Accepted ADRs first — they choose the migration tool/naming + the DB approach (PK / audit / delete / constraints). Corroborate with the live migrations/ folder + schema (Explore or ls) for anything the architecture left implicit — e.g. statement-per-file rules (golang-migrate's transaction-per-file), the next sequence value. Record the migration naming as a promote-time hint in the audit report (e.g. «sequential, next ≈ 000023 — implement assigns the real number at promotion, since another feature may promote first»). On a greenfield repo with no architecture signal, the schema choices are confirmed with the user (steps 4–6), not defaulted to a house style. Do not pick a final number, do not write into the live migrations/, and do not impose a convention the architecture/repo doesn't use — staging happens in step 9, promotion in implement; flag any divergence (architecture vs repo) in the report.
3. Read prereqs in order: spec §5 (entity candidates from AC) → sad §5 building blocks (the persistence containers — which container owns which data) → sad.md §6 sequences (each writes/reads <entity> note → an entity + index candidate) → (optional) the Explore-discovered domain layer for a struct-vs-DDL map.
4. Aggregate roots. Ask (or infer from AC) which aggregate roots own what — without explicit aggregates the FK graph turns into a hairball. Phrase per ../_shared/ask-style.md.
5. PK strategy. Follow the repo's detected PK convention; on greenfield (or if an AC demands a specific PK like a lookup slug), confirm with the user — UUID, bigint-identity, sequence, composite, whatever fits. No default imposed.
6. Columns + constraints per entity, matching the repo's conventions: string/JSON types per the repo's norms (VARCHAR(N) sized from AC validation limits / TEXT / JSON, as the repo does); audit columns (created_at / updated_at / none) per the repo's pattern; CHECK / DB DEFAULT / triggers iff the repo uses them; <!-- TBD --> where honestly undecided. On greenfield, confirm these with the user.
7. Indexes per query. Each sequence note becomes one index candidate; discard candidates with no concrete query; print a "Query it serves" justification column.
8. Write docs/features/<slug>/data-model.md from ./templates/data-model.md: ER Mermaid (clean ordered block) + entity tables per aggregate + indexes table. Validate the erDiagram per ../_shared/mermaid-check.md (render-parse with mmdc if available, else the structural lint — valid cardinality glyphs + type name attribute lines; fix before continuing).
9. Generate migration files — STAGED in the feature folder, not the live tree. A run that finds no schema change legitimately produces a minimal data-model.md (documenting the existing entities the feature touches) with zero staged migrations — that is a valid outcome, not a failure; note that api would also have accepted the fast-lane skip (→ size-matrix fast lane). Write the pairs into docs/features/<slug>/migrations/ with a feature-local ordinal name (01_create_<entity>.up.sql + .down.sql, 02_…) that preserves intra-feature order. The SQL is full and shippable; only the location + the final number differ from a live migration. Never write into the repo's live migrations/ here — implement promotes these (assigning the real sequence number / timestamp per the convention detected in step 2, in ordinal order) when it runs the layer: migration task. The SQL content rules are unchanged:
   • Greenfield: one create-<entity> .up.sql + .down.sql per entity (or per small aggregate). IF NOT EXISTS everywhere; ON CONFLICT DO NOTHING on seeds.
   • Existing-table index: use the concurrent, non-blocking form, and warn that the file must contain only that one statement if your migration tool wraps each file in a transaction (e.g. golang-migrate).
   • New NOT NULL on existing table / rename / drop: emit the 3-step expand→backfill→contract sequence (separate ordinal files); the user reviews the backfill SQL.
10. Seeds (3 buckets). Bootstrap (deterministic hardcoded UUID v7) → first migration; lookup data → separate migration with ON CONFLICT DO NOTHING; test fixtures → NOT in migrations/ — generate them in the form the repo uses (factory functions / fixtures / builders), documented under "Test fixtures". PII guard (hard): no real-looking email/name/phone in any seed — use admin@example.test, user-<uuid>@example.test, Test User.
11. Drift detection (always; --drift-only short-circuits here). If the Explore subagent found a domain layer, map each field to a column and report field-without-column / column-without-field / type-mismatch / nullability-mismatch; auto-propose fix migrations under _drift/ for the user to review.
12. Self-check (4 mandatory, stack-agnostic). Naming matches the repo's convention; down reversibility (every CREATE has a DROP, every ADD COLUMN a DROP COLUMN, every CREATE INDEX a DROP INDEX); FK indexes (every REFERENCES other(id) has an index on the FK column); convention adherence (the schema follows the repo's detected conventions — flag any deliberate divergence in the report, never silently impose a house style). Any failure → fix or surface, never silent-commit.
13. Audit report docs/features/<slug>/_audit/data-model-<date>.md: the staged migration files (their docs/features/<slug>/migrations/<NN>_* paths), the promote-time convention hint (e.g. «repo is sequential, next ≈ 000024 — implement assigns the real number at promotion»), convention deviations, drift findings, breaking-change decompositions, every <!-- TBD -->. State plainly: «migrations are staged — not yet in the live migrations/ tree; implement promotes them». Next stage api <slug>.
14. Propose commit + handoff. data-model: <slug> (data-model.md + staged migrations). Then emit the stage-handoff block per ../_shared/handoff.md — What I did + Review (data-model.md, staged migrations/) + Run next — resolve the next stage per .route (the Routes table in ../_shared/size-matrix.md): forward /sdd:api <slug>; api's N/A condition = no contract change (no new/changed endpoint, event, or public signature), skip target /sdd:tasks <slug> (on quick — auto-skip with the reason + inverted ↳ or; on standard — offer the ↳ or; on full — no skip line).