Database Schema That Scales — From Requirements to Migration Files

The Problem

Database schema design seems simple until it isn't. Common mistakes that cost weeks to fix later:

• No indexes on foreign keys (queries go from 5ms to 5 seconds at scale)
• Wrong data types (storing prices as floats → rounding errors)
• No constraints (orphaned records, invalid states)
• Over-normalization or under-normalization
• No soft deletes (data loss is permanent)
• No audit trails (who changed what when?)
• Missing indexes for your actual query patterns
• No consideration for future sharding

These mistakes don't surface until you have 1 million rows. Then everything is slow, and migrating a production database with 50 million rows is terrifying.

The Solution

The Database Design skill generates complete schemas from domain requirements — properly normalized, indexed for your query patterns, with constraints, audit columns, and migration files.

The Process

You: Design a database for a multi-tenant SaaS project
management tool. Features: organizations, projects, tasks,
users, comments, file attachments, activity log, billing.
PostgreSQL. Needs to handle 10M+ tasks across 5K+ orgs.

The agent generates:

• Complete schema with 12+ tables, all properly normalized
• Foreign keys with cascading rules
• Indexes optimized for the most common query patterns
• Composite indexes for multi-column filters
• Partial indexes for soft-deleted records
• Row-level security policies for multi-tenancy
• Audit columns (created_at, updated_at, created_by)
• Migration files (up + down)
• Seed data for development

Key decisions the agent makes (and documents why):

• BIGINT for IDs (not INT — you'll exceed 2.1B)
• NUMERIC(12,2) for money (not FLOAT)
• TIMESTAMPTZ for all timestamps (not TIMESTAMP)
• UUID for public-facing IDs (not auto-increment)
• organization_id on every table (tenant isolation)
• GIN indexes on JSONB columns
• Partial indexes: WHERE deleted_at IS NULL

The Results

Setup on MrChief

skills:
  - database-design
  - data-pipeline  # For ETL considerations