Dbdesign

DB Design

Design database schemas from requirements, existing data, and query patterns. This skill covers modeling decisions and review quality; use vd:miudb or the sqlit CLI to inspect live databases, vd:diagram for ERDs, and vd:cnpg for CloudNativePG provisioning.

Reference Router

Read only what the task needs:

• references/workflow.md: intake, schema inspection, output format, and approval gates.
• references/transactional.md: OLTP entities, relationships, constraints, indexes, soft delete, tenancy, and audit columns.
• references/analytics.md: OLAP/star schema, facts, dimensions, SCD, summaries, and incremental refresh design.
• references/engine-notes.md: PostgreSQL, MySQL/MariaDB, SQLite/D1, BigQuery, and MongoDB-specific design notes.
• references/migration-review.md: migration safety, rollout sequencing, backfills, rollback, partitioning, and risk checks.
• references/design-doc.md: compact database design document template.

Workflow

1. Identify the database engine and context: new schema, extension of existing schema, analytics model, data import, migration review, or design doc.
2. Inspect existing schema before proposing changes when a database or repo is available. Use vd:miudb, the sqlit CLI, migrations, ORM models, dbt models, or schema files as source of truth.
3. Capture requirements before DDL:
   • entities and relationships
   • main reads/writes/reports
   • expected scale and growth
   • tenancy, permissions, privacy, retention, and audit needs
   • consistency requirements and transaction boundaries
4. Load the relevant references:
   • OLTP or CRUD app -> transactional.md
   • analytics/reporting -> analytics.md
   • engine-specific DDL -> engine-notes.md
   • migration/change review -> migration-review.md
   • design document -> design-doc.md
5. Propose the model first, then DDL. Do not execute DDL unless the user explicitly asks and the environment is safe.
6. Explain tradeoffs: normalization vs denormalization, surrogate vs natural keys, JSON vs columns, partitioning, index cost, retention, and operational risk.
7. Finish with a checklist and open questions. If assumptions remain, label them instead of pretending certainty.

Design Defaults

• Model the business language first; table names should reflect domain concepts, not UI screens.
• Prefer boring relational constraints for integrity: primary keys, foreign keys, unique constraints, checks, and NOT NULL.
• Index from actual query patterns, not from every column.
• Add comments/descriptions for tables and important columns when the engine supports them.
• Use exact numeric types for money and measured quantities.
• Store timestamps consistently; prefer timezone-aware types when the engine supports them.
• Plan retention and deletion behavior up front, especially for PII and audit logs.
• Separate operational tables from analytics/reporting models when workloads diverge.
• Treat schema migrations as production changes: reversible when possible, staged when large, and tested with realistic data.

Validate DBML with dbdocs

When the design uses DBML (you write or edit a .dbml file):

• Gate: after writing/editing the file, run dbdocs validate <path/to/schema.dbml>. A clean parse (Parse succeeded without errors.) is required before the design is done. dbdocs is the validator (e.g. dbdocs/0.14.0).
• No apostrophes inside single-quoted notes. note: 'the row''s id' is not valid DBML and fails with Expect a comma ',' - reword to drop the apostrophe ("the id on this row"); triple-quoted '''...''' notes are fine.
• Avoid reserved/ambiguous words as column names (e.g. index, now) - they can break the parser; document the column in the table Note instead, or quote/rename it.

Hard Rules

• Do not invent a schema when existing tables might already cover the domain. Inspect first or ask for the schema.
• Do not execute DDL by default. Present DDL and migration steps for approval.
• Do not generate irreversible destructive migrations without an explicit warning and safer alternatives.
• Do not design indexes without naming the query pattern each index serves.
• Do not put secrets, credentials, or real PII in design docs, examples, PR bodies, or committed files.
• Do not preserve old source skill names or Claude-specific paths in new work.