Authoring Data Model

authoring-data-model — SKILL.md

Variant: standard · When to use: authoring a data model document. This skill supplies the producer METHOD and the integrity/queryability BAR; the section STRUCTURE comes from a separate data-model template tool you compose with. Do not restate that template's outline here.

Overview

A data model document is the persistence/domain model of a system's stored data — the design an engineer implements the schema from and everyone who queries the data reads. This skill guides a producer to author one: deriving entities from the upstream feature-spec, detecting the storage paradigm and modeling in it, making integrity rules explicit, and justifying every index by an access pattern. It teaches the how-to — the research method and the quality bar — and composes with a separate template tool that supplies the section structure. The bar is "an engineer can create the schema and write correct queries from it, and the model's integrity rules + tradeoffs are explicit."

It is paradigm-aware: the same modeling rigor (entities, relationships, integrity, justified indexes, stated tradeoffs) applies to relational/SQL, document/NoSQL, graph, wide-column, and key-value stores; only the artifact adapts. Relational + document are the two detailed tracks below; the others are modeled in their own idioms (Step 2).

When to activate

• Authoring or substantially revising a data model / schema design document for a system or feature, given an upstream feature-spec (and an architecture-doc naming the stores, where present).
• The producer needs the method (how to derive entities, model relationships, justify indexes, state tradeoffs) and the bar a buildable, queryable model must meet — not a blank page.

Do NOT activate when:

• Reviewing/judging a finished data model — that is a runtime review gate, not authoring.
• Authoring the API wire contract (operations + request/response DTOs) — that is the api-spec; it references this model's entities (downstream, one-directional), it does not redefine them.
• Designing the persistence implementation (ORM/repository/access-layer) — that is a technical-design doc; the system's data-store topology is an architecture doc.
• Producing the executable DDL / migration scripts — this is the design document (entities, constraints, migration plan), not the code.
• Authoring a different document type.

Inputs

Read every document the plan hands you — your depends_on set (the upstream documents discovery determined inform this one) — and trace this document's content back to them. Do not assume a fixed input: the typical upstreams this skill names are method guidance, not a cap on what you receive. Be self-contained — produce the document from whatever context you actually receive; when an expected informing document is absent, proceed on what you have and surface the gap as an explicit assumption, never fabricate to fill it. And use a research capability where one is available (deep-research) to make the document comprehensive and exhaustive, not merely to fill the template.

Capability context (when provided): If a capability_record (a record from capability-map.yaml product_capabilities) is injected by the caller, read it before Step 1. It defines your scope boundary: owns = entities you cover; refs = entities you reference but do not own; publishes/consumes = events you surface; entry_points/exit_points = how users arrive and leave; has_ui/has_api/has_persistence = which surfaces apply. When present, treat it as a hard constraint — do not stray outside the boundary it defines.

Workflow

The section structure comes from the data-model template tool you compose with. The steps below are the METHOD that fills it — apply them to whatever sections that template carries; never restate its outline.

Step 1: Get the structure + ground the inputs

Obtain the data-model template's section set from the template tool. Read the upstream feature-spec (the data the features manipulate) and, where present, the architecture-doc (the chosen stores). You are elaborating these — never starting blank. Ground modeling decisions in established practice (ER modeling, normalization theory, the chosen paradigm's idioms) via a research capability rather than inventing.

If a capability_record is present: define primary tables/collections for all owns entities; treat refs entities as FK references only (no data duplication); include outbox/event entries for publishes events.

Step 2: Detect the paradigm and pick the track

From the architecture-doc / chosen store, decide the paradigm per store (a system may use more than one — model each in its own track). Apply the same rigor either way; the artifact differs:

• Relational/SQL — entities map to tables; ER diagram + per-attribute spec tables; primary/foreign keys + referential integrity; normalize, then denormalize deliberately.
• Document/NoSQL — access-pattern-first (model from the query list, not the entities, because ad-hoc query flexibility is limited); collections + document shapes; embedding vs referencing; co-locate entities read together; single-table design — overload a generic partition+sort key across entity types into item collections, add a GSI/LSI only for an access pattern the base key can't serve, avoid a hot (low-cardinality) partition key.
• Other paradigms (model in their idioms, proportional — not full tracks): graph — entities are nodes, relationships are first-class typed/directed edges (with edge properties), and the access patterns are traversals (e.g. friends-of-friends); do NOT force a relational junction table. Wide-column — a partition key + clustering key for wide, append-heavy rows; design for the read, denormalize freely, TTL-based retention, no joins. Key-value — access strictly by key; model the value shape + the key scheme. The cross-cutting rules (typed entities, a key per entity, every index/secondary structure justified by an access pattern, stated tradeoffs, lifecycle) hold in every paradigm; references/modeling-method.md carries the per-paradigm idioms.

Step 3: Enumerate access patterns, then model

List the reads/writes the system must serve against this data first. For relational they justify the indexes; for NoSQL they determine the schema. Then:

• Entities — derive from the feature-spec's nouns. Type every attribute; mark required/optional; give defaults where they exist; record value constraints. Keep the persistence entity distinct from the api-spec's wire DTO — they often differ; model the stored shape, not the DTO.
• Relationships — every one states its cardinality (1:1 / 1:M / M:N), the foreign key or reference realizing it, which side owns the lifecycle, and the referential rule (on-delete: restrict/cascade/set-null — or, for NoSQL, embed-vs-reference and how referential consistency is held without DB-enforced FKs). Keep the ER diagram and the relationship list in sync with the entity catalog.
• Keys + constraints — a primary key per entity (natural vs surrogate, with the reason; for NoSQL the partition+sort key and any overloaded-key strategy); uniqueness (incl. multi-column); check/domain constraints; cross-field invariants. Explicit, not implied.

Step 4: Justify indexes, state tradeoffs, address lifecycle

• Indexes — each one traces to an access pattern from Step 3 (the columns in WHERE/JOIN/ORDER BY and the foreign keys those queries hit). An index with no justifying access pattern is removed; over-indexing bloats storage and slows writes.
• Normalization/denormalization — state the form (relational: typically 3NF/BCNF) or the NoSQL embedding strategy, and record every deliberate denormalization with the read pattern it serves, the write-cost/staleness tradeoff accepted, and how the redundant copy is kept consistent. The core tradeoff: normalization favors write efficiency + integrity; denormalization favors read performance.
• Data lifecycle — retention/archival per entity (soft vs hard delete, with the query/uniqueness consequence of a tombstone); any stored derived/computed value with its source of truth and how it stays current; temporal/audit/versioning where history matters (effective-dated rows, an audit/append-only log, a slowly-changing-dimension strategy, row versioning for optimistic concurrency).
• Data privacy & classification — classify sensitive attributes (PII / sensitive / internal / public), key retention + residency to that classification, and apply data-minimization (store only what's needed); protect secrets at the data layer (hash+salt passwords, tokenize cards — never plaintext). See references/modeling-method.md.
• Migration & schema evolution — the migration/seeding plan (how the schema is created and evolved, backfill, seed data) — the plan, not the DDL. Classify a change additive (backward-compatible) vs breaking; run a breaking change on a populated store as expand-and-contract (expand → backfill → transition reads → contract — never rename/remove in place), and state the backward/forward compatibility stance under rolling deploys. Depth + the amend procedure live in references/lifecycle-and-amend.md.

Step 5: Surface gaps, then self-check against the bar

Record unknowns as explicit assumptions/open-questions — never invent. Then verify the document against the quality bar in ## Output before handing off. Express everything textually (a Mermaid erDiagram, attribute/constraint spec tables, index/relationship lists, and a fenced document-shape example for NoSQL) — the method and bar are independent of the medium.

Step 6: Amending an approved model (a schema-change delta)

When the input is a change request against an approved model (a new entity/attribute, a relationship/cardinality change, a denormalization, a key change), edit-not-redraw: amend only the touched entities + their relationships/constraints/indexes, in place. The migration is the amend mechanism — two data-model-specific properties:

• Classify + migrate. Classify the change additive (a new nullable column / table / index — backward-compatible) vs breaking (a dropped/renamed/narrowed column, a cardinality change, a key change); plan a breaking change as expand-and-contract (never an in-place rewrite) with a backward/forward compatibility analysis. Mark superseded entities/attributes/indexes as deprecated — never silently delete — and bump the doc version + changelog.
• Forward/downward ripple. A schema change ripples to the downstream api-spec (whose resources map onto changed entities), the impl/test-plan touching the schema, and any migration/runbook; the upstream feature-spec is amended first (spec→plan→impl order). Flag the ripple — don't silently break a downstream reader.

Full procedure in references/lifecycle-and-amend.md. On a greenfield first build this step is n/a.

Rules

Hard rules (never violate):

• Compose, don't duplicate. Take the section structure from the template tool; this skill supplies the method + bar. Never restate the template's outline in the produced doc or reason as if this skill owns the structure.
• Every entity is typed and keyed; every relationship carries cardinality + the referential rule. No untyped attribute, keyless entity, or relationship missing cardinality/referential behavior ships.
• Every index traces to an access pattern. No unjustified index; no missing-but-needed index.
• Ground in the feature-spec (+ architecture-doc), never a blank page. Entities and attributes reflect the real domain; gaps are surfaced as assumptions, not invented.
• Persistence model, one-directional vs the api-spec. This is the stored schema. The api-spec is a downstream consumer that references these entities — it is never an input here (treating it as one would create a circular dependency). The stored entity is not the wire DTO.
• Capability boundary (when capability_record provided). Scope the document output to that record's boundary. Producing content outside the boundary (covering a refs entity as if it were owned; designing flows past an exit_point) is a scope violation — equivalent to inventing content that isn't in the spec.

Preferences (override-able):

• Size proportionally: "comprehensive" sets ambition; a thin store collapses sections it does not need. The bar is integrity + queryability completeness + access-pattern coverage, not word count.
• Prefer a surrogate primary key unless a natural key is stable and meaningful; state the choice either way.

Gotchas

• Relational-only reflex on a NoSQL store. Defaulting to tables + 3NF when the store is document/NoSQL produces an unusable model. Detect the paradigm first (Step 2) and model access-pattern-first for NoSQL.
• Indexes added "to be safe." An index with no access pattern behind it is dead weight — it slows writes and bloats storage. Tie each to a Step 3 query or drop it.
• Relationship lines with no cardinality or on-delete rule. An ER diagram that shows a line but not 1:M/M:N and not what happens on delete is not buildable. Every relationship needs both.
• Modeling the wire DTO instead of the stored entity. The api-spec's request/response shapes often differ from what is stored (computed fields, omitted internals, flattened joins). Model the persistence shape; let the api-spec map onto it.
• ER diagram and tables drifting apart. An entity or relationship present in one but absent in the other breaks the reader. Keep the Mermaid diagram, the entity catalog, and the relationship list in sync.
• Emitting DDL/migration code. The deliverable is the design (entities + constraints + migration plan), not executable schema scripts.

Anti-patterns

• "The api-spec already lists the fields, so I'll model from it." That inverts the dependency and conflates wire DTO with stored entity. Model from the feature-spec; the api-spec references this model, not the reverse.
• "I'll normalize everything to BCNF and stop." Normal form is a starting point, not the goal — name the deliberate denormalizations the access patterns require, with their tradeoffs.
• "I'll restate the template's sections so the doc looks complete." Duplicating the outline is not authoring — supply the method-filled content the structure asks for.
• "The store is obvious, no need to justify it." State the storage choice and paradigm with the rationale (data shape, consistency, scale, access patterns) — a downstream reader cannot infer it.

Output

A data model document (textual markdown) that meets this integrity + queryability bar. These nine conditions are single-sourced with the reviewing-data-model gate — its checklist IS this list, so produce-bar and review-bar do not drift. Self-check against them before handing off:

1. Entities & attributes — every entity has typed attributes + a key (primary, or partition+sort for NoSQL); the modeled shape is the stored shape, not the api-spec's wire DTO; entities trace to the feature-spec's domain.
2. Relationships & referential integrity — every relationship states cardinality; an M:N is realized by a junction entity (relational/document; a graph uses an edge); the FK/reference + owning side named; the on-delete rule stated (relational) or embed-vs-reference + the consistency-without-FK strategy stated (NoSQL/other).
3. Keys, constraints & access-pattern-justified indexes — a PK per entity (natural-vs-surrogate stated); uniqueness + check/cross-field constraints explicit; the access patterns enumerated; every index traces to an access pattern (no unjustified index, no missing-but-needed one).
4. Normalization & storage paradigm — the normal form (relational) or embedding/single-table strategy (NoSQL/other) stated; the paradigm chosen with rationale; each deliberate denormalization records its read-pattern + tradeoff + consistency strategy.
5. Data lifecycle & migration — retention/archival + soft-vs-hard delete where needed; derived data names its source-of-truth + freshness; temporal/audit where history matters; a migration & seeding plan (expand-and-contract for a breaking change; backward/forward compatibility where rolling deploys apply).
6. Diagram ⇄ catalog ⇄ tables in sync — every entity/relationship in the Mermaid erDiagram appears in the catalog + relationship/index lists and vice-versa; no orphan/drift.
7. Cross-cutting data quality — sensitive attributes classified (PII/sensitive) + retention/residency where the data warrants; at-rest security for secrets (no plaintext credentials); the model viable at the target scale (no hot partition / unindexed hot query / unbounded row) — addressed where the data warrants.
8. Grounded, honest & consistent — entities/attributes reflect the feature-spec (not invented/boilerplate); assumptions explicit (thin-input → a blocker, not an invented schema); no fabrication; one-directional vs the api-spec (derived from the feature-spec, the api-spec only a downstream consumer — never reverse-engineered from DTOs); consistent with the shipped schema where one exists.
9. Delta-scoped amend (on a change request) — the changed entities meet conditions 1–8 on what they touched; the change is classified additive/breaking with a migration plan + compatibility analysis; the forward/downward ripple flagged; the doc version bumped + changelog; superseded items marked. (Greenfield first build → n/a.)

Proportionality: a thin store legitimately collapses what it does not need (one entity, no M:N, no denormalization, no temporal history, no migration on a first draft, no PII). Judge integrity + queryability completeness, not word count.

The abstract consumer: the engineers who implement persistence and write queries, a downstream api-spec author whose resources map onto these entities, and the reviewing-data-model gate that checks the doc against the bar above.

Related

• A content-template tool — supplies the data-model section structure this skill composes with (the structure; this skill is the method + bar).
• A deep-research capability — grounds modeling decisions in established practice (ER modeling, normalization theory, the paradigm's idioms) instead of inventing.
• The upstream feature-spec (+ architecture-doc where present) — the input context; this model designs the persistence the feature-spec'd behavior needs.
• An api-spec authoring concern — a downstream consumer: its resources map onto this model's entities (one-directional). Distinct artifact — the wire contract, not the stored schema.
• reviewing-data-model — the dedicated acceptance gate (the twin): it judges the finished model against the same nine conditions in ## Output (single-sourced) and emits VERDICT: approve|revise. A finished data-model artifact routes there, not to the generic design-review (which carves it out, like the technical-design + architecture-doc artifacts).
• A references/ pair: references/modeling-method.md (per-paradigm idioms + the entities/relationships/keys/index/normalization depth) and references/lifecycle-and-amend.md (retention/temporal/privacy + the expand-and-contract migration + the amend procedure + the forward/downward ripple).

Progressive disclosure

Heavy content lives in subfolders, loaded only on demand:

• references/modeling-method.md — the per-paradigm idioms (relational/document/graph/wide-column/key-value) + the depth on entities/attributes, relationships/referential-integrity, keys/constraints/access-pattern-justified-indexes, normalization/paradigm-choice, and data privacy/classification. Load when modeling a specific paradigm or a borderline integrity decision.
• references/lifecycle-and-amend.md — retention/soft-delete/derived-data/temporal depth + the expand-and-contract migration procedure + the amend method (classify additive/breaking, compatibility, the forward/downward ripple). Load on a lifecycle or amend/delta task.
• references/sources.md — research provenance for the method + bar. Load when verifying a claim's grounding.

This skill ships no scripts/ or assets/.

Body budget

• description ≤ 1,024 chars (agentskills.io cap).
• Body ~500 lines / 5,000 tokens (soft target — quality takes precedence; flag if consistently over 700 lines / 7,000 tokens).
• Per reference file: warn >10k tokens, error >25k. Total references: warn >25k, error >50k.

Changelog

• 1.3.0 (2026-06-21) — capability_record context injection: optional caller-injected capability_record consumed in Step 1 (scope boundary from owns/refs/publishes/consumes); capability_boundary rule added; body budget softened to target. Graceful fallback when no record injected.
• 1.2.0 (2026-06-15) — production-grade restructure: added the iteration/amend method (Step 6 — classify additive/breaking + expand-and-contract migration + backward/forward compatibility + the forward/downward ripple); deepened schema-evolution/migration, temporal/audit/SCD, and data privacy/classification in the lifecycle; broadened the paradigm coverage (graph/wide-column/key-value idioms + NoSQL single-table depth); restructured ## Output to nine conditions single-sourced 1:1 with the new reviewing-data-model twin (added cross-cutting data quality + delta-scoped amend); routed the finished artifact to reviewing-data-model (not the generic design-review); split depth into references/modeling-method.md + references/lifecycle-and-amend.md. Method + input contract + medium unchanged (additive).
• 1.1.0 (2026-06-04) — initial reviewed release.