git:20261008.3f25f14 to git:20261015.f440cbb

- description: Use when designing a data model for a new feature or bounded context — entities, relationships, normalization, indexing, constraints, partitioning. Triggers on "data model", "schema design", "database design", "ER model", "数据模型", "表结构设计", "数据库设计".

- Design the data model that sits beneath the API and beside the domain language. `architecture`

- picks the datastore type; `domain-modeling` sharpens the vocabulary; this skill designs the actual

- shape of the data — entities, relationships, indexes, constraints, and how the model scales. A

- schema designed reactively (added-to-as-features-arrive) becomes a performance and correctness

- liability that is expensive to fix later.

-

- - Designing entities and relationships for a new feature or bounded context

- - Choosing normalization level, indexing strategy, or partitioning for a new model

- - Reviewing a proposed schema for correctness, performance, or future evolution

- - Triggers on "data model", "schema design", "database design", "ER model", "数据模型", "表结构设计", "数据库设计"

-

- > **Scope:** assumes a relational datastore (Postgres/MySQL/SQLite). For document stores

- > (MongoDB), key-value (DynamoDB), or graph stores, the embedding-vs-referencing and

- > sharding decisions differ — use `architecture` for datastore selection, then adapt these

- > patterns.

- **Not for:** choosing the datastore type (use `architecture`); ubiquitous-language glossary (use

- `domain-modeling`); API request/response shapes (use `api-design`); changing an existing schema

- in production (use `deprecation-migration`).

- ### 1. Map entities and relationships

-

- Start from the domain model (`domain-modeling` output / CONTEXT.md) and translate nouns into

- entities, verbs into relationships. For each entity, identify its identity (primary key), its

- attributes, and its lifecycle (created, mutated, archived, deleted).

-

- - Draw an ER diagram (Mermaid erDiagram — see `${CLAUDE_PLUGIN_ROOT}/references/mermaid-diagrams.md`)

- - Cardinality for every relationship: one-to-one, one-to-many, many-to-many

- - Resolve many-to-many with junction tables; never hide them in a comma-separated column

-

- _Verify: every entity in the domain model is represented; every relationship has documented

- cardinality._

-

- ### 2. Choose normalization level

-

- Normalize by default (3NF / BCNF) — it eliminates anomalies and keeps the model honest. Denormalize

- deliberately, only when a measured read pattern justifies the write complexity and consistency

- risk. Document every denormalization as a trade-off: what read it optimizes, what write it

- complicates, how consistency is maintained.

-

- - Foreign keys for every relationship (enforce referential integrity at the DB level)

- - Surrogate keys (UUID/serial) over natural keys unless the natural key is truly immutable

- - NULL semantics: every nullable column has a documented meaning — "unknown" vs. "none" vs. "not yet"

-

- _Verify: every FK is enforced; every denormalization is documented with its trade-off._

-

- ### 3. Design indexes

-

- Index for the queries, not the columns. Start from the access patterns (how will this data be

- read?), then create indexes that serve them. Every index has a cost — write amplification and

- storage — so justify each one against a concrete query.

-

- - Composite indexes ordered by selectivity and equality-before-range

- - Covering indexes for hot read paths (include columns to avoid table lookups)

- - Partial / filtered indexes for common WHERE predicates (e.g. `WHERE deleted_at IS NULL`)

- - Unique indexes to enforce business invariants (one active subscription per user)

-

- _Verify: every index maps to a named query; `EXPLAIN` on each access pattern uses an index, not a

- seq scan._

-

- ### 4. Define constraints and invariants

-

- The database is the last line of defense for data integrity. Push invariants into constraints —

- they are enforced regardless of application bugs:

-

- - `NOT NULL` unless the column genuinely can be absent

- - `CHECK` constraints for range/format invariants (price ≥ 0, status in allowed set)

- - `UNIQUE` constraints for business keys (email, slug, active-session-per-user)

- - Foreign key actions: `ON DELETE` / `ON UPDATE` (RESTRICT / CASCADE / SET NULL) chosen deliberately

-

- _Verify: every business invariant is backed by a constraint, not just application logic._

-

- ### 5. Plan for scale and evolution

-

- Design the model to survive growth without a painful migration:

- - Partitioning strategy for large tables (range by date, hash by tenant) — decide before the table

- hits 100M rows, not after

- - Avoid hot writes to a single sequence (use scattered keys or per-tenant sequences at scale)

- - Soft delete (`deleted_at` timestamp) for audit-relevant data; hard delete for ephemeral data —

- choose per entity, document why

- - Forward-compatible types: `timestamptz` (not `timestamp`), `UUID` or `bigint` (not `int` for IDs

- that may exceed 2B)

- _Verify: partitioning decision is documented; ID types won't overflow; soft/hard-delete policy is

- stated per entity._

- ### 6. Document

- **Output:** `docs/design/SCHEMA.md` — the data model document: ER diagram, entity definitions with

- columns/types/constraints, index plan mapped to access patterns, partitioning strategy, and any

- denormalization trade-offs. Reference ADRs for significant model decisions. Close with an AUD

- block (6–12 items: domain-model consistency, FK/constraint coverage, index-to-query mapping,

- partitioning decision, migration safety) — implementation starts only after the human audit

- passes. Format and statuses: `${CLAUDE_PLUGIN_ROOT}/references/human-audit.md`.

- - [ ] ER diagram drawn (Mermaid); every entity and relationship documented

- - [ ] Normalization level stated; every denormalization documented with trade-off

- - [ ] Foreign keys enforced at DB level; referential integrity does not rely on application code

- - [ ] Indexes mapped to named queries; `EXPLAIN` confirms index usage on access patterns

- - [ ] Business invariants backed by CHECK / UNIQUE constraints

- - [ ] ID types won't overflow; partitioning strategy stated for growth tables

- - [ ] `docs/design/SCHEMA.md` produced with ER diagram + entity/index/constraint plan

-

- **Red flags:** comma-separated lists in a column; missing foreign keys; indexing every column (or

- no indexes); `SELECT *` driving the schema; `int` IDs on tables that will grow; NULL with no

- documented meaning; relying solely on application code for integrity; no partitioning plan for a

- table that will exceed memory.

- - [${CLAUDE_PLUGIN_ROOT}/references/engineering-principles.md](${CLAUDE_PLUGIN_ROOT}/references/engineering-principles.md) — shared discipline (surface assumptions, enforce simplicity, verify don't assume)

- - [${CLAUDE_PLUGIN_ROOT}/references/mermaid-diagrams.md](${CLAUDE_PLUGIN_ROOT}/references/mermaid-diagrams.md) — Mermaid erDiagram syntax for entity-relationship diagrams

- - [${CLAUDE_PLUGIN_ROOT}/references/human-audit.md](${CLAUDE_PLUGIN_ROOT}/references/human-audit.md) — audit-block baseline: AUD vs 确认 blocks, status lifecycle, readability bar