get-schema

# Get Schema ## Overview `get-schema` turns an approved PRD into a documented database schema. It reads the PRD, derives the entities the product actually needs, defines their columns and relationships, and writes the result to `docs/schema/<name>.md` as tables a developer can read without opening a migration file. The reason this exists as its own step: **a PRD describes behaviour, not tables.** It says "a user can record a signal against an experiment". It does not say whether a signal is a row, a column, or an event stream, and it should not — that is a data modeling decision. When nobody makes that decision explicitly, it gets made implicitly by whoever writes the first migration, and every later feature inherits it. The schema document sits between the PRD and the task breakdown: ``` get-prd -> docs/prd/<name>.md (what and why) get-schema -> docs/schema/<name>.md (what the data looks like) <- this skill get-tasks -> tasks/<name>/*.md (in what order to build it) ``` It exists before `get-tasks` because tasks need to know the shape of the data they read and write. A task that says "store the signal" is not useful until "signal" has columns. The output is a Markdown document with entity tables, a relationship table, an index table, and a constraints section. It is deliberately **not** SQL. DDL goes stale the moment an ORM is chosen or a migration is refactored; a table describing intent survives. ## When to Use Use this skill when: - A PRD or spec exists and the data model needs to be designed before implementation starts - The user says "get schema", "bikin schema", "rancang database", "desain tabel", or "what tables do we need" - You are about to create the first migration and no written model exists - A new feature in an existing system needs its tables designed and recorded **When NOT to use:** - No PRD or spec exists — run `get-prd` first. Deriving tables from an unwritten product is guessing - The schema already exists and the user wants a small column added — edit the migration, then update `docs/schema/` to match - The user wants SQL DDL — this skill produces the model, not the DDL. Write the migration separately, ideally alongside `incremental-implementation` - The user wants an ADR about *why* a data decision was made — that is `documentation-and-adrs`. This skill records the shape; the ADR records the argument - A single-table change with no relationships and no ambiguity — just make the change ## The Process `get-schema` runs in eight phases. Each phase has an exit condition. Do not advance past a phase whose exit condition is unmet. ``` 0. Locate the PRD -> which document is the source? 1. Identify entities -> what nouns does the product actually persist? 2. Choose the engine -> which database, and why? 3. Define attributes -> what columns does each entity have? 4. Relationships -> how do entities connect, and what happens on delete? 5. Indexes and constraints -> what must be fast, and what must be impossible? 6. Quiz the user -> entities, engine, relationships, naming 7. Write and verify -> docs/schema/<name>.md, then check it ``` **Designing is read-only.** Phases 0 through 6 produce a document and questions, not migrations or code. Do not create a table, write a migration, or edit an ORM schema file during those phases. Writing `docs/schema/<name>.md` is the only filesystem change. --- ### Phase 0: Locate the PRD Find the source document before doing anything else. 1. **Convention first.** `get-prd` saves to `docs/prd/<name>.md`. Look there first. 2. **Then the wider search.** If `docs/prd/` is empty, look for `SPEC.md`, `docs/SPEC.md`, or a document the user names. 3. **If more than one candidate exists**, do not guess. List them and ask which governs. 4. **If none exists**, stop and route the user to `get-prd`. Deriving a schema from a vague request produces tables nobody asked for. Also check whether `docs/schema/<name>.md` already exists. If it does, this is a revision, not a first pass — read it before continuing so the new version is a deliberate change rather than a rewrite. Record the exact path. Every later phase references it. **Exit condition:** a single source document is identified, and the existing schema state (none, or a path) is on the record. --- ### Phase 1: Identify Entities Derive the entity list from the PRD. Do not invent entities and do not copy a generic starter schema. Read the PRD's **User Stories** and **Objective** first. Entities are the nouns the product persists, extracted from behaviour: ``` "as a user, I want to see all my running experiments in one place" -> Experiment "as a user, I want to record an early signal against an experiment" -> Signal, with a link to Experiment ``` Rules: - **Every entity traces to a User Story or Objective.** If you cannot name the story an entity serves, it does not belong. A junction table is the exception — it traces to a relationship, not a story. - **Nouns that are not persisted are not entities.** "Report" may be a query over experiments, not a table. Decide explicitly and say so. - **Nouns that are enums are not entities.** "Status" is usually a column with a fixed set of values, not a table, unless the product lets users define new ones. - **Name entities in the singular.** `experiment`, not `experiments`. The table name is a separate decision. - **Use the PRD's vocabulary, not a synonym.** If the PRD says "signal", the entity is `signal`. Introducing "event" here creates two names for one thing. - **Prefer the smallest entity set that satisfies the stories.** A premature table is harder to remove than to add. List the entities before moving on, with the story each one serves. If a story needs an entity you are unsure about, note it as an open question rather than deciding silently. **Exit condition:** every entity is listed, and each names the PRD story or objective it serves. --- ### Phase 2: Choose the Engine The database engine shapes types, index options, and constraint support. Do not assume one. **If the PRD already names an engine, use it and say so.** Do not re-litigate a settled decision. **If it does not, ask the user, with a recommendation and a reason.** Never pick silently. Present it like this: ``` The PRD does not name a database. My recommendation is <engine>, because <reason tied to this product's actual needs>. Q: <engine choice>? Options: - <engine A> - <when it fits, and the cost> - <engine B> - <when it fits, and the cost> ``` What makes a recommendation good: - **Tie it to the product, not to fashion.** "Postgres because the signal payloads vary per experiment and a JSONB column avoids a schema migration per signal type" is a reason. "Postgres is standard" is not. - **Name the trade-off you are accepting.** Every engine choice costs something: operational complexity, hosting cost, query limitations, or migration friction. - **Consider what the project already uses.** If the repo has a migration folder for one engine, a second engine is almost never the answer. Check before recommending. - **Ask once.** The engine is not the subject of this document; it is a precondition. If the user has no preference and genuinely does not know, offer the smallest set of options that covers their likely deployment, with one clearly marked as the default. Record the chosen engine and the reason at the top of the document. The reason is what lets a future reader re-evaluate the choice. **Exit condition:** the engine is chosen and the reason is recorded, or explicitly inherited from the PRD. --- ### Phase 3: Define Attributes For each entity, define its columns. | Column | Type | Nullable | Default | Notes | |---|---|---|---|---| | id | uuid | no | generated | primary key | | name | text | no | - | user-facing label | | created_at | timestamptz | no | now() | | Rules: - **Every table gets a primary key.** Name it once per document and be consistent. If the project already has a convention, follow it. - **Types are the engine's types.** `timestamptz`, not `datetime`; `text`, not `string`. If the engine is undecided, use logical types and say so. - **Say why a column is nullable.** Nullable is a decision, not a default. A column that is nullable "just in case" invites bugs that treat null as a value. - **Every column traces to behaviour.** A column no story reads or writes is either future-proofing you have not been asked for, or a leftover. - **Timestamps come in pairs.** If a row can change, it needs `created_at` and `updated_at`. If it is append-only, say so, and skip `updated_at`. - **Foreign keys are named as columns here, defined in Phase 4.** `<entity>_id`, not `owner` or `parent`. - **Mark enum-like columns explicitly.** A status column with a fixed set of values gets a constraints entry in Phase 5, not a free-text type. - **Do not add a `deleted_at` unless the PRD requires soft delete.** It is a product decision with real cost, not a default. For anything the PRD leaves open — an optional field, a length limit, a precision — record it as an open question rather than choosing silently. **Exit condition:** every entity has a complete column table, and every nullable or defaulted column has a stated reason. --- ### Phase 4: Define Relationships Describe how entities connect. ```markdown | From | To | Cardinality | On delete | Notes | |---|---|---|---|---| | signal | experiment | many-to-one | cascade | deleting an experiment removes its signals | | experiment | user | many-to-one | restrict | an experiment cannot outlive its owner | ``` Rules: - **Every relationship names its cardinality**, one of one-to-one, one-to-many, many-to-one, many-to-many. "Related" is not a relationship. - **Many-to-many is two one-to-many edges through a junction entity.** Name the junction, and give it the two foreign keys plus any attributes the relationship itself carries (a role, a joined-at timestamp). - **On-delete behaviour is mandatory, not optional.** Every foreign key declares `cascade`, `restrict`, `set null`, or `no action`. Omitting it means choosing the most dangerous default. Deleting a user should not silently delete their billing history — say so here. - **On-update behaviour follows the same rule** if it is not the engine default. - **A foreign key implies an index.** Note it here, confirm it in Phase 5. - **Self-references get an explicit note.** A tree of comments is a self-referencing one-to-many; say so, and name the column. - **No cycles in ownership.** If two entities each require the other, either one is optional or they are one entity. **Exit condition:** every foreign key is declared with cardinality and on-delete behaviour, and every junction entity is named. --- ### Phase 5: Indexes and Constraints Write down what must be fast and what must be impossible. **Indexes:** ```markdown | Table | Columns | Type | Rationale | |---|---|---|---| | signal | (experiment_id, created_at desc) | btree | list view reads the latest signal per experiment | | experiment | (owner_id, status) | btree | dashboard filters running experiments by owner | ``` Rules: - **Every index names the query it serves.** An index with no named query is a guess, and guesses cost write throughput forever. If you cannot name the query, do not add the index. - **Every foreign key gets an index** unless one is already implied by a composite index that leads with it. - **Unique constraints are constraints, not indexes.** Record them in the constraints section and note that the engine may index them implicitly. - **Composite column order is a decision.** State the leftmost column and why it leads. - **Do not index everything.** Each index slows writes and costs storage. The default is no index until a query proves it needs one. **Constraints:** ```markdown | Table | Constraint | Type | Definition | |---|---|---|---| | experiment | status is a known value | check | status in ('running', 'paused', 'done') | | signal | one signal per experiment per minute | unique | (experiment_id, minute_bucket) | | experiment | name is required | not null | name | ``` Rules: - **Encode business rules the database can enforce.** If the product says "an experiment name is unique per owner", that is a unique constraint, not a validation note. Application-level checks are the backup, not the primary defence. - **Enum-like columns get a check constraint** listing the valid values. - **State referential actions here too** if the engine models them outside foreign keys. - **Every constraint is testable.** If you cannot describe a failing case, it is not a constraint. **Exit condition:** every index names its query, and every business rule the database can enforce is written as a constraint. --- ### Phase 6: Quiz the User Present the model before writing the file. Show the entity list, the engine, the relationships, and the indexes — not the full column tables: ```markdown **Engine:** PostgreSQL - JSONB for per-experiment signal payloads **Entities** | Entity | Serves | |---|---| | experiment | "see all my running experiments" | | signal | "record an early signal" | **Relationships** | From | To | Cardinality | On delete | |---|---|---|---| | signal | experiment | many-to-one | cascade | **Indexes**: signal(experiment_id, created_at desc), experiment(owner_id, status) ``` Then ask: - **Are these the right entities?** Anything the product persists that is missing? Anything here that is actually a query, not a table? - **Is the engine right?** Any constraint on deployment that changes it? - **Are the relationships right?** Especially: is every on-delete behaviour the one you want? Cascading deletes are the most common data-loss accident. - **Is the naming consistent?** Agent or human, whoever reads this next should not wonder whether "signal" and "event" are the same thing. Iterate until approved. Do not write the file before approval — the table is cheap to change, the document is not. **Exit condition:** the user has approved the model, explicitly. --- ### Phase 7: Write, and Verify Write the document to `docs/schema/<name>.md`, where `<name>` matches the PRD's name. Create `docs/schema/` if it does not exist. **Document template:** ```markdown # Schema: <Name> **Source:** docs/prd/<name>.md **Database:** <engine> - <reason for this choice> ## Overview One paragraph. What this data model supports, and which part of the PRD motivated it. ## Entities ### <entity> Serves: <the PRD user story or objective this entity exists for> | Column | Type | Nullable | Default | Notes | |---|---|---|---|---| | id | uuid | no | generated | primary key | | <column> | <type> | <yes/no> | <default> | <why, when not obvious> | ## Relationships | From | To | Cardinality | On delete | On update | Notes | |---|---|---|---|---|---| | <entity> | <entity> | many-to-one | cascade | - | <why> | ## Indexes | Table | Columns | Type | Rationale | |---|---|---|---| | <table> | (<columns>) | btree | <the query it serves> | ## Constraints | Table | Constraint | Type | Definition | |---|---|---|---| | <table> | <what it enforces> | check / unique / not null | <expression or rule> | ## Migration Notes - <Anything the implementer must know: backfill requirements, ordering constraints, columns that must be added in a separate deploy, whether expand/contract applies> ## Open Questions - <What the PRD did not settle, and which area it affects> ``` Rules: - **Every section is filled or marked "Not applicable" with a reason.** Never leave a heading with nothing under it. - **No SQL DDL.** The document describes intent; the migration is written separately. - **No Mermaid diagram.** The tables are the diagram. - **Migration Notes are not optional when there is existing data.** A new table is easy; a column added to a populated table is a deploy sequence, and the implementer needs to know that here rather than in production. - **Open Questions name the area they affect.** A question no one can act on is noise. Then verify what you wrote: - [ ] Every entity traces to a PRD story or objective - [ ] Every entity has a primary key - [ ] Every column names its type, and every nullable column names a reason - [ ] Every foreign key has a cardinality and an on-delete behaviour - [ ] Every junction entity is named and carries both foreign keys - [ ] Every index names the query it serves - [ ] Every business rule the database can enforce is a constraint - [ ] Migration Notes are present, or explicitly "Not applicable" - [ ] No SQL, no diagram, no blank sections - [ ] The document names its source PRD and the chosen engine with a reason **Exit condition:** the file exists, the checklist passes, and the handoff is stated. ## Handing Off `get-schema` produces a document, not a database. After approval: - **To `get-tasks`** — the usual next step. Tasks that touch data should cite the schema document and reference entity names, not invent their own. - **To `documentation-and-adrs`** — if a data decision needs its reasoning recorded permanently (why JSONB, why soft delete, why this cardinality), the schema records the shape and the ADR records the argument. - **To `deprecation-and-migration`** — when the schema document describes a change to a populated table rather than a new one.