Task: Generate DATA.md — Persistent-Data Layer

Task: Generate DATA.md — Persistent-Data Layer

Objective

Produce a DATA.md that serves as the single source of truth for the system's persistent-data layer: which datastore(s) the system uses and why, the ER (or collection) diagram, the schema for every table or collection with column-by-column typing and constraints, every index with its rationale, the ordered list of migrations, the retention and deletion policy per table, the sensitive-data inventory per column, and the consistency and concurrency strategies applied across entities. Crucially, DATA.md extends traditional database documentation with an access-patterns section that maps every query the system actually runs to the index that serves it — schemas without access patterns are under-specified. An agent reading this document alone can explain, for any query the system issues, which index serves it and at what cost class; for any table, which aggregate it persists and which invariants its constraints enforce; for any column holding sensitive data, its classification, masking rule, and encryption posture.

DATA.md is read by /operations (for migration sequencing, retention runbooks, and backup posture), /security (for the sensitive-data inventory), every per-unit /SPEC.md that touches persistence (to draw typing and constraints from a single source), /code-review for conformance against the registered schema, and /system-verification during bootstrap. The defining discipline — and the commonest violation — is every query is indexed or explicitly accepted as a scan: a schema that lists columns and indexes without the access-pattern map is half a design, because it does not prove the indexes are the right ones. Co-locating schema, indexes, and access patterns in one artifact is the deliberate read-pattern optimisation that makes this skill exist.

Inputs

1. DOMAIN.md (required) — § 2 Bounded Contexts supply owning-context values for each table; § 4 Entities and § 5 Aggregates supply the conceptual targets that schema persists (every table maps to at least one aggregate or entity); § 9 Invariants Index supplies INV-NN references for invariants enforced at the database level (uniqueness, foreign-key integrity, check constraints).
2. ARCHITECTURE.md (required) — § 2 Components and § 5 ADRs pin the datastore technology choice (Postgres, DynamoDB, MySQL, Mongo, SQLite, Redis, S3, …). The ADR-NN that chose the storage engine is cited verbatim in § 1 Datastore Overview. Multi-store architectures (a relational primary plus a blob store plus a cache) produce multiple datastore sub-blocks.
3. Existing schema / migrations (optional, auto-discovered — only if refining) — if migration files (e.g., migrations/, db/migrations/, priv/repo/migrations/, Flyway, Alembic, ActiveRecord, Knex) are present, read them fully. Every shipped migration appears in § 6 with its original filename preserved. Do not re-describe shipped migrations from memory — quote their actual effect.
4. Existing queries / ORM code (optional, auto-discovered — access-pattern inference) — if source trees contain SQL (.sql files, query-builder DSL, EXPLAIN artefacts), ORM model code (repositories, DAOs, Active Record models), or data-access layers, scan them to harvest the queries the system actually runs. Each harvested query maps to a row in § 5 with its origin file and function cited in the Uses index column's footnote.
5. Existing DATA.md (auto-discovered — only if refining) — if a DATA.md already exists at the expected path, read it fully. Shipped migration filenames are permanent; never rename or reorder them. Column names, table names, and index names that are already live are load-bearing — changing them requires a migration row, not a silent edit.

Read set size: 2 required + up to 3 auto-discovered. Read both required inputs end-to-end; for refinement, read every migration file and every data-access source file before drafting. Omitted reading causes two specific failure modes: tables with no owning aggregate (DOMAIN drift) and indexes with no serving query (orphan indexes — the schema is lying about which queries matter).

Workflow

Data-layer design proceeds in nine phases: datastore anchoring, aggregate-to-schema mapping, index inventory, access-pattern mapping (the critical phase), migration enumeration, retention and deletion, sensitive-data inventory, consistency and concurrency, and validation. Phases are sequential but revisit earlier phases when a later one reveals a missing index, a missing migration, or a mis-sized constraint. Do not skip the access-patterns phase — it is the phase that proves the schema is the right schema.

Phase 1: Datastore Anchoring

Read ARCHITECTURE.md § 2 and § 5 to fix the datastore technology. For each distinct datastore the system uses (typically one primary relational or document store, optionally a blob store, a cache, a message queue's durable state, a search index), record: kind (postgres, mysql, dynamodb, mongo, sqlite, redis, s3, elasticsearch, cassandra, …), version pinned where load-bearing, connection model (pooled / per-request / serverless), transactional semantics (single-row atomic / single-shard / multi-shard with 2PC / none), and consistency guarantees this datastore provides out of the box (read-after-write, snapshot isolation, eventual, tunable quorum). Cite the originating ADR-NN from ARCHITECTURE.md § 5.

The primary datastore kind lands in frontmatter datastore:. If multiple datastores exist, datastore: carries the primary (the one that owns aggregate persistence); secondary stores (cache, blob) are declared in § 10 Non-Relational Side and do not contribute to datastore:.

Phase 2: Aggregate-to-Schema Mapping

For each aggregate in DOMAIN.md § 5 (and for entities in § 4 that persist independently), choose the schema representation:

• One-table-per-aggregate — default for relational stores. The aggregate root becomes a table; member entities with cardinality 1 may be inlined as columns or as a composite type; member entities with cardinality N become child tables with a foreign key on the root's primary key. Cite the Represents field in every table with the aggregate name.
• Single-table with type discriminator — appropriate when polymorphic aggregates share many columns (classic accounts table holding both IndividualAccount and OrganisationAccount). Record the discriminator column and the per-discriminator invariants.
• Document-per-aggregate — default for document stores. The aggregate root plus its value-object members serialise to one document; member entities with cardinality N live as embedded arrays or as a separate collection with a parent reference.
• Event-sourced — the aggregate persists as an append-only sequence of events; current-state projections live as separate tables or materialised views. Rare; specify only when ARCHITECTURE.md § 5 has an ADR for it.