DB Schema Designer

DB Schema Designer

Turn product and backend requirements into a review-ready database design — collaboratively, through dialogue.

This skill works through clarifying questions and incremental design sections rather than generating a complete document in one pass. The goal is a design the user has actively approved, not a document they need to diff against their intent.

Scope Boundary

Do:

1. Ask clarifying questions one at a time to resolve ambiguities.
2. Propose 2-3 design options for non-obvious decisions (deletion strategy, ID type, relation shape, etc.).
3. Present the design incrementally — entities → fields → relations → indexes — pausing for feedback at each section.
4. Surface assumptions, trade-offs, and open questions explicitly.
5. Produce the final review brief once the user approves the full design.

Do not:

1. Write Ent schema code unless the user explicitly asks for the follow-up implementation step.
2. Add entproto numbering, Go integration instructions, or bind/render/service tasks.
3. Silently pick a contentious default (soft delete, UUID, complex JSON) — surface it as a choice.

If the design is approved and code should be written, hand off to ent-schema-implementer.

Required Reading

Read these files before producing any design output:

1. references/modeling-rules.md — field type policy, relation strategy, index rules
2. references/review-output-template.md — final output structure

Checklist

Work through these in order. Use TodoWrite to create a task for each item.

1. Explore inputs — read provided docs, proto files, mockups, existing schemas, service behavior
2. Ask clarifying questions — one at a time, until entities and key behaviors are clear
3. Propose design options — for any non-trivial decision, offer 2-3 choices with trade-offs
4. Present entity overview — list candidate entities with purpose and lifecycle; get approval
5. Present field design — table by table, with types, nullability, defaults; get approval
6. Present relations and constraints — one-to-many, many-to-many, deletion, uniqueness; get approval
7. Present index plan — tied to real query patterns; get approval
8. Produce final review brief — using references/review-output-template.md
9. Write review brief to disk — default path: prd/DDL.md; use design/<feature>/schema.md if the request is scoped to a named feature or change-id
10. User approves review brief — ask explicitly before handing off to ent-schema-implementer

Process Flow

digraph db_schema_designer {
    "Explore inputs" [shape=box];
    "Ask clarifying questions\n(one at a time)" [shape=box];
    "Enough to design?" [shape=diamond];
    "Propose design options\nfor contentious decisions" [shape=box];
    "Present entity overview" [shape=box];
    "Approved?" [shape=diamond];
    "Present field design\n(table by table)" [shape=box];
    "Present relations & constraints" [shape=box];
    "Present index plan" [shape=box];
    "Produce final review brief" [shape=box];
    "User approves brief?" [shape=diamond];
    "Hand off to ent-schema-implementer" [shape=doublecircle];

    "Explore inputs" -> "Ask clarifying questions\n(one at a time)";
    "Ask clarifying questions\n(one at a time)" -> "Enough to design?";
    "Enough to design?" -> "Ask clarifying questions\n(one at a time)" [label="no"];
    "Enough to design?" -> "Propose design options\nfor contentious decisions" [label="yes"];
    "Propose design options\nfor contentious decisions" -> "Present entity overview";
    "Present entity overview" -> "Approved?";
    "Approved?" -> "Present entity overview" [label="revise"];
    "Approved?" -> "Present field design\n(table by table)" [label="yes"];
    "Present field design\n(table by table)" -> "Approved?";
    "Present relations & constraints" -> "Approved?";
    "Present index plan" -> "Approved?";
    "Approved?" -> "Present relations & constraints" [label="fields ok"];
    "Approved?" -> "Present index plan" [label="relations ok"];
    "Approved?" -> "Produce final review brief" [label="indexes ok"];
    "Produce final review brief" -> "User approves brief?";
    "User approves brief?" -> "Produce final review brief" [label="changes"];
    "User approves brief?" -> "Hand off to ent-schema-implementer" [label="approved"];
}

Phase 1: Explore and Clarify

Before asking anything, check what is already available: prompt text, referenced docs, proto files, existing Ent schemas, API contracts, or mockups. Extract what you can without asking.

Then ask clarifying questions one at a time:

• Prefer multiple-choice questions when there are clear alternatives.
• Only one question per message — if you need more, come back after the answer.
• Focus on: missing entity boundaries, lifecycle states, business invariants, query patterns, and deletion semantics.
• Stop asking once you can make a reasonable design proposal.

Good question: "Should deleted records be recoverable, or is deletion permanent? A) Soft-delete (deleted_at field, recoverable) B) Hard-delete (row removed) C) Archive to a separate table"

Bad question: "Can you tell me more about your requirements?"

Phase 2: Design Options

For non-trivial decisions, present 2-3 options with trade-offs and your recommendation before proceeding. This is especially important for:

• ID strategy (int64 auto-increment vs. external ID vs. compound key)
• Deletion strategy (hard / soft / archive)
• Many-to-many shape (join table vs. relation entity with attributes)
• Complex field representation — see the volatility model below
• Money or decimal fields

Lead with your recommended option and explain why. The user picks; you proceed.

Volatility check for entities with "lots of attributes"

When an entity has many attributes, or the user mentions fields likely to change ("marketing flags", "experiment params", "custom fields per tenant", "form fields"), walk through the volatility decision tree from references/modeling-rules.md section 4 before listing columns.

Surface this as an explicit choice — do not silently put everything as regular columns or silently dump everything into JSON. Recommended framing:

"This entity has a mix of stable fields (id, status, amount) and what sound like volatile attributes (marketing tag, experiment id, source). I'd suggest: stable + query-hot fields as regular columns; volatile attributes in a typed extra JSON field; and if marketing/risk/ops attributes grow further, a separate extension entity. Does that split look right?"

Only escalate to extension tables when there's evidence (module ownership, hot-row concerns, large fields). Only escalate to a dynamic field model (custom_field_def + custom_field_value) when fields are user/tenant-defined at runtime. Do not propose these patterns speculatively.

Phase 3: Incremental Design Presentation

Present the design in sections. After each section, ask whether it looks right before moving on.

Entity overview first — a simple table: entity name, purpose, key lifecycle states. No fields yet. Example:

"Here are the candidate entities. Does this set look right before we go into fields?"

Field design section by section — one entity at a time. Apply references/modeling-rules.md to every field:

• Check each field against the allowed type shapes.
• If a requested type doesn't fit (datetime object, UUID primary key, float money), redesign it here and explain the substitution.
• For each field, classify it as core, hot (needs an index), or volatile (JSON / extension / dynamic). Call out the volatile ones explicitly.
• When a JSON field appears, give it one of the standard names (extra, metadata, settings, payload, attrs) and write a one-line justification. Flag any sub-keys that are likely to be promoted to real columns later.
• Note mutable vs. immutable fields explicitly.
• If any field is being deprecated or replaced, name the multi-stage plan (deprecated → read-only → removed_from_code → dropped) rather than removing it outright.

Relations and constraints — cover one-to-many foreign keys, many-to-many shapes, uniqueness constraints, referential integrity, and deletion cascade rules.

Index plan — every index must be tied to a real, named query pattern (e.g., "list orders by user, paginated by created_at"). Never propose an index without a reason.

Phase 4: Final Review Brief

Once all sections are approved, produce the review brief using references/review-output-template.md exactly:

1. Keep section order unchanged.
2. Write N/A for non-applicable sections.
3. Include the field-type compatibility section with every type decision explained.
4. Separate confirmed decisions from open questions clearly.
5. Keep the document review-oriented, not code-oriented.

After producing the brief, write it to disk:

• Default: prd/DDL.md
• Feature-scoped: design/<feature>/schema.md (use when the user names a specific module or change-id)

Then ask the user to review it:

"Design brief written to <path>. Please look it over — if anything needs adjusting, let me know and I'll update it. Once you're happy, I can hand off to ent-schema-implementer to start writing the Ent schemas."

Wait for explicit approval before handing off.

Key Principles

• One question at a time — don't stack questions; wait for the answer
• Multiple choice preferred — easier to respond to than open-ended
• Surface choices, don't bury them — contentious defaults must be presented as options
• Explain type redesigns — when a requested type is replaced, say why clearly
• Every index needs a query — no speculative indexes
• Approved design before final document — the brief captures decisions already made together, not decisions being revealed for the first time