Database Design

3. Index Strategy ⚡ (Power Mode)

Steps:

1. Analyze expected query patterns.
2. Suggest indexes: B-tree for equality/range, GIN for full-text/JSONB.
3. Warn about over-indexing (write penalty) and missing indexes (slow reads).
4. Document index rationale.

4. Seed Data 🌿 (Eco Mode)

Steps:

1. Generate seed data scripts for development and testing.
2. Include realistic but anonymized sample data.
3. Make seeds idempotent (upsert pattern).

Outputs

Edge Cases

• Schema change on a table with millions of rows — Recommend online DDL tools (pt-online-schema-change, pg_repack).
• Circular foreign keys — Break with nullable FK + application-level checks.

Scope

In Scope

• Designing relational schemas (PostgreSQL, MySQL, SQLite) and document schemas (MongoDB, DynamoDB).
• Normalization analysis (1NF through BCNF) and intentional denormalization with documented justification.
• Writing and reviewing DDL statements (CREATE TABLE, ALTER TABLE, CREATE INDEX).
• Generating migration files (up/down) in SQL or ORM-specific formats.
• Index strategy: selecting index types, analyzing query patterns, flagging missing or redundant indexes.
• Generating ER diagrams (Mermaid) and seed data scripts.

Out of Scope

• Writing application-level data-access or ORM code (use data-access skill).
• Database server installation, configuration, or performance tuning at the infrastructure level (use db-tuning skill).
• Backup, replication, or disaster-recovery planning.
• API or service layer design that consumes the schema (use api-contract-design skill).
• Data warehouse or OLAP schema design (star/snowflake schemas).

Guardrails

• Never execute destructive DDL (DROP TABLE, DROP COLUMN, type narrowing) without explicit user approval.
• All migrations must include both up and down scripts and be idempotent.
• Require justification (as a comment in the migration) for any denormalization beyond 3NF.
• Primary keys must be explicitly defined — never rely on implicit row IDs.
• Foreign keys must specify ON DELETE and ON UPDATE behavior.
• Warn when adding an index on a high-write table with more than 5 existing indexes.
• Seed data must use anonymized or synthetic values — never include real PII.
• Preview the full DDL diff before applying any schema change.

Ask-When-Ambiguous

Triggers

• Target database engine is not specified (PostgreSQL vs MySQL vs MongoDB vs DynamoDB).
• The user asks to "add a field" but the entity could belong to multiple tables.
• Normalization level is debatable (e.g., repeated groups that could be extracted to a join table).
• A destructive migration is required and rollback risk is unclear.
• The user provides entity names but no cardinality (one-to-many vs many-to-many).

Question Templates

• "Which database engine is this schema targeting — PostgreSQL, MySQL, MongoDB, or another?"
• "The field {field} could belong to {table_a} or {table_b}. Which entity owns this data?"
• "Storing {data} inline denormalizes the schema. Should I extract it into a separate table with a foreign key, or keep it inline for read performance?"
• "This migration drops column {column} from {table}. This is irreversible in production. Should I proceed, or use a soft-deprecation approach (rename + nullable)?"
• "What is the cardinality between {entity_a} and {entity_b} — one-to-one, one-to-many, or many-to-many?"

Decision Criteria

Success Criteria

• Schema DDL executes without errors on the target database engine.
• All tables have explicit primary keys and appropriate foreign key constraints.
• Normalization level is at least 3NF, with documented justification for any denormalization.
• Migration files are idempotent and include reversible down scripts.
• Index strategy covers all expected query patterns without over-indexing.
• ER diagram accurately reflects the final schema.
• Seed data scripts run idempotently and use anonymized/synthetic values.
• No destructive DDL was applied without explicit user approval.

Failure Modes

Audit Log

• [{timestamp}] SCHEMA_DESIGNED — Tables: [{table_names}] | Engine: {db_engine} | Normalization: {level}
• [{timestamp}] MIGRATION_GENERATED — File: {filename} | Direction: up+down | Changes: {summary}
• [{timestamp}] INDEX_ADDED — Table: {table} | Columns: [{columns}] | Type: {btree|gin|hash} | Rationale: "{reason}"
• [{timestamp}] DESTRUCTIVE_CHANGE_APPROVED — Table: {table} | Operation: {drop_column|drop_table|type_change} | User confirmed: yes
• [{timestamp}] ER_DIAGRAM_GENERATED — Format: Mermaid | Entities: {count} | File: {filename}
• [{timestamp}] SEED_DATA_GENERATED — Tables: [{table_names}] | Rows: {count} | Idempotent: yes