Database Schema Designer

Iron Law

NO SCHEMA MIGRATION WITHOUT A DOWN (ROLLBACK) SCRIPT AND AN INDEX ON EVERY FOREIGN KEY — unindexed FKs cause full table scans; irreversible migrations cause production incidents

Database Schema Designer

Design production-ready database schemas with best practices built-in.

Triggers

| Trigger | Example | |---------|---------| | design schema | "design a schema for user authentication" | | database design | "database design for multi-tenant SaaS" | | create tables | "create tables for a blog system" | | schema for | "schema for inventory management" | | model data | "model data for real-time analytics" | | I need a database | "I need a database for tracking orders" | | design NoSQL | "design NoSQL schema for product catalog" |

Quick Reference

| Task | Approach | Key Consideration | |------|----------|-------------------| | New schema | Normalize to 3NF first | Domain modeling over UI | | SQL vs NoSQL | Access patterns decide | Read/write ratio matters | | Primary keys | INT or UUID | UUID for distributed systems | | Foreign keys | Always constrain | ON DELETE strategy critical | | Indexes | FKs + WHERE columns | Column order matters | | Migrations | Always reversible | Backward compatible first |

Process

Phase 1: Analyze

• Identify entities and relationships
• Determine access patterns (read-heavy vs write-heavy)
• Choose SQL or NoSQL based on requirements

Phase 2: Design

• Normalize to 3NF (SQL) or determine embed/reference strategy (NoSQL)
• Define primary keys and foreign keys
• Choose appropriate data types -- read reference/data-types-reference.md for type guides
• Add constraints -- read reference/constraints-and-relationships.md for patterns

Read reference/normalization-guide.md for 1NF/2NF/3NF rules and examples.

Phase 3: Optimize

• Plan indexing strategy -- read reference/indexing-strategy.md for when to index and composite index rules
• Consider denormalization for read-heavy queries
• Add timestamps (createdat, updatedat)

Phase 4: Migrate

• Generate migration scripts (up + down)
• Ensure backward compatibility
• Plan zero-downtime deployment

Read reference/migration-patterns.md for zero-downtime patterns and rollback strategies.

Read reference/expand-contract-migrations.md for zero-downtime Expand-Contract strategy, CONCURRENTLY index operations, and batched SKIP LOCKED backfills.

NoSQL Design

For MongoDB, Firestore, and other document databases, read reference/nosql-design-patterns.md for embedding vs referencing patterns and Firestore-specific design rules.

Commands

| Command | When to Use | |---------|-------------| | design schema for {domain} | Start fresh -- full schema generation | | normalize {table} | Fix existing table -- apply normalization rules | | add indexes for {table} | Performance issues -- generate index strategy | | migration for {change} | Schema evolution -- create reversible migration | | review schema | Code review -- audit existing schema |

Anti-Patterns

PostgreSQL Type Forbidden List

NEVER use these types — they have silent, hard-to-debug failure modes:

| Forbidden type | Problem | Use instead | |----------------|---------|-------------| | timestamp (no tz) | Stores local time, breaks across timezones | timestamptz | | timetz | Doesn't handle DST — reports wrong time after clock changes | timestamptz | | char(n) / varchar(n) | char pads with spaces, silently breaks equality checks | text | | money | Locale-dependent formatting, rounding errors across servers | numeric | | serial | Sequence ownership breaks on pg_dump/restore | generated always as identity | | float for money | Binary floating-point rounding errors | numeric(10,2) |

Schema Anti-Patterns

| Avoid | Why | Instead | |-------|-----|---------| | VARCHAR(255) everywhere | Wastes storage, hides intent | Size appropriately per field | | Missing FK constraints | Orphaned data | Always define foreign keys | | No indexes on FKs | Slow JOINs | Index every foreign key | | Storing dates as strings | Cannot compare/sort | DATE, TIMESTAMPTZ types | | Non-reversible migrations | Cannot rollback | Always write DOWN migration |

Verification

After designing a schema, run through reference/schema-design-checklist.md to verify completeness.

Documentation Sources

Before generating schemas or queries, consult these sources:

| Source | URL / Tool | Purpose | |--------|-----------|---------| | PostgreSQL | PostgreSQL MCP server | Schema-aware SQL, introspection, admin-safe workflows | | Firebase Firestore | Firebase MCP server | Document design, rules, indexes for NoSQL schemas |

Reference Files

| File | Contents | |------|----------| | reference/schema-design-checklist.md | Pre-design, table design, and deployment checklist | | reference/normalization-guide.md | 1NF/2NF/3NF explanations, examples, denormalization guide | | reference/data-types-reference.md | String, numeric, date/time, JSON type guides | | reference/indexing-strategy.md | When to index, composite indexes, B-tree vs hash, EXPLAIN | | reference/constraints-and-relationships.md | PKs, FKs, CHECK, UNIQUE, relationship patterns | | reference/nosql-design-patterns.md | MongoDB/Firestore embedding vs referencing | | reference/migration-patterns.md | Zero-downtime migrations, rollback strategies | | reference/expand-contract-migrations.md | Expand-Contract pattern, CONCURRENTLY index ops, SKIP LOCKED backfills | | assets/templates/migration-template.sql | SQL migration file template | | reference/postgresql-review-checklist.md | PostgreSQL review checklist (used by postgresql-database-reviewer agent) |

Error Handling

Migration conflicts: When migrations fail, check for column type mismatches or missing dependent migrations. Never modify an applied migration — create a new corrective one.

Index creation failures: Verify the column exists and data types support the index type. For large tables, use CREATE INDEX CONCURRENTLY.

Hard Prohibitions

• No DROP TABLE or DROP COLUMN without explicit human approval
• Use plural table names (users, orders, payments)
• Database credentials rotated every 90 days (see security-review-checklist.md §8)

Post-Code Review

After writing SQL/migration code, dispatch this reviewer agent:

• postgresql-database-reviewer — query optimization, schema correctness, index coverage, security

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: kumaran-is
• Source: kumaran-is/claude-code-onboarding
• License: MIT

Install and usage instructions live in the source repository linked above.