/data-model

name: data-model
model: inherit
effort: medium
agents: [explorer]
description: >
  Use to design the data model AND generate the actual forward + rollback migrations in one
  pass — shippable SQL, not a plan. Triggers on "data model for {slug}", "schema for {slug}",
  "generate migrations for {slug}", "DB design + migration", "/sdd:data-model {slug}",
  "модель даних для {slug}", "схема для {slug}", "згенеруй міграції". Reads spec.md §5 +
  sad.md §5 building blocks + the §6 sequence diagrams, then writes docs/features/{slug}/data-model.md plus
  paired *.up.sql / *.down.sql migrations STAGED under docs/features/{slug}/migrations/ (NOT the
  live migrations/ tree — implement promotes them when the feature is actually built) and an audit
  report. Greenfield-first; brownfield delta via --mode brownfield; drift-only via --drift-only.
  Hard-refuses if spec.md or sad.md is missing. Stack-agnostic: detects and FOLLOWS the repo's DB +
  migration conventions and domain layer — it imposes no DB philosophy and writes no rules file.

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`](../_shared/size-matrix.md)) governs how much you produce; the aggregate-roots dialogue uses [`../_shared/ask-style.md`](../_shared/ask-style.md).

`data-model.md` prose follows `artifact_language` — SQL, table/column identifiers, headings and frontmatter stay English → [`../_shared/artifact-language.md`](../_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`](../_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.

| Topic | How `data-model` decides | |---|---| | Naming (tables / columns) | Follow the repo's convention (detected from existing migrations + schema); greenfield → confirm. | | PK strategy | Follow the repo (UUID / bigint-identity / sequence / composite — whatever it uses); greenfield → confirm. | | Audit columns (`created_at` / `updated_at` / …) | Match the repo's pattern; greenfield → confirm. No stance imposed either way. | | Delete strategy (hard / soft / status) | Match the repo; greenfield → confirm. | | Constraints (`CHECK` / `TRIGGER` / DB `DEFAULT`) | Use them **iff the repo does**; never add or forbid them by fiat. | | String / JSON types | Match the repo's norms (`VARCHAR(N)` / `TEXT` / JSON); greenfield → confirm. |

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

| Mechanic | Why (stack-agnostic) | |---|---| | **Staged** as `docs/features/<slug>/migrations/<NN>_<verb>_<entity>.up.sql` + `.down.sql` (NN = feature-local ordinal), promoted by `implement` (real number/timestamp assigned at promote-time) | Keeps the live tree free of half-designed schema; late numbering avoids early-grab collisions. | | Idempotent DDL where the tool suppor