Skip to content

JPeetz/agent-skills / skills/data/database-schema-designer

database-schema-designer

Verified

Use this skill when designing or reviewing database schemas for SQL and NoSQL databases. Covers normalization, indexing strategy, migration management with rollback safety, query optimization, and multi-tenant patterns. Supports PostgreSQL, MySQL, SQLite, MongoDB, and Vitess.

Database Schema Designer

Design production-ready database schemas with built-in best practices. Covering SQL normalization, indexing strategy, migration management with safe rollback patterns, query optimization, and multi-tenant architecture patterns across PostgreSQL, MySQL, SQLite, MongoDB, and Vitess.

When to Use This Skill

Use this skill when:

  • Designing a new database schema from scratch
  • Reviewing an existing schema for performance or correctness
  • Planning a database migration with safe rollback
  • Optimizing slow queries on a production database
  • Converting between database engines (MySQL → PostgreSQL, etc.)
  • Designing multi-tenant data architectures
  • Any request like "design a schema for X", "review my database", "optimize this query", "create migrations", "normalize this table"

Safety Rules (Risk Tier L2)

Database operations can be destructive. This skill enforces:

  1. Never DROP without backup — Always generate backup commands first
  2. Always generate rollback — Every migration includes verified reversal
  3. Test migrations on staging — Never run directly on production
  4. Lock-aware design — Schema changes must consider lock duration
  5. Data integrity first — Validate before and after every migration
  6. No data loss — Backfill before dropping columns, migrate before deleting

Design Methodology

Phase 1: Domain Modeling

Start with entities, not tables. Map the domain before writing DDL:

DOMAIN CANVAS:
├── Entities: What things exist? (User, Order, Product, Invoice)
├── Relationships: How do they connect? (one-to-many, many-to-many)
├── Attributes: What properties do they have?
├── Constraints: What must always be true?
├── Access Patterns: What queries will run most often?
└── Growth Projections: How many rows? At what rate?

Phase 2: Schema Design — SQL

Normalization checklist:

  • 1NF: Atomic columns, no repeating groups
  • 2NF: No partial dependencies on composite keys
  • 3NF: No transitive dependencies
  • BCNF: Every determinant is a candidate key (when needed)
  • Denormalize intentionally (document why, measure benefit)

Example: E-Commerce Schema

-- Users table (normalized, with soft delete)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ  -- soft delete
);
CREATE INDEX idx_users_email ON users(email) WHERE deleted_at IS NULL;

-- Products with inventory tracking
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
    inventory_count INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_products_sku ON products(sku);

-- Orders with status state machine
CREATE TYPE order_status AS ENUM (
    'pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled'
);

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    status order_status NOT NULL DEFAULT 'pending',
    total_cents INTEGER NOT NULL DEFAULT 0,
    shipping_address JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status)
    WHERE status IN ('pending', 'processing');

-- Order items (many-to-many with quantities)
CREATE TABLE order_items (
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Phase 3: Indexing Strategy

The Index Selection Algorithm:

  1. WHERE clause columns → Candidate for index
  2. JOIN columns → Index foreign keys
  3. ORDER BY columns → Consider composite with WHERE cols
  4. SELECT columns → Consider covering indexes
  5. Cardinality check → Don't index low-cardinality columns alone

Index types by database:

DatabaseIndex TypesWhen to Use
PostgreSQLB-tree (default), Hash, GiST, GIN, BRIN, SP-GiSTB-tree for equality/range; GIN for full-text/arrays; BRIN for large sequential data
MySQL/InnoDBB-tree (clustered PK), Full-text, SpatialB-tree for most cases; Full-text for text search
SQLiteB-tree (default)All standard cases
MongoDBSingle-field, Compound, Multikey, Text, Geospatial, Hashed, TTLCompound for common queries; TTL for expiring data

Index anti-patterns:

  • Indexing every column "just in case" — wastes write performance
  • Missing composite index leading column — index on (a, b) doesn't help queries on b alone
  • Redundant indexes — index on (a, b) makes index on (a) redundant
  • Unused indexes — audit with pg_stat_user_indexes or sys.dm_db_index_usage_stats

Phase 4: Migration Design

Migration file structure:

-- migrations/001_add_user_preferences.sql
-- UP: Forward migration (what to apply)
-- DOWN: Rollback migration (how to undo)

-- UP MIGRATION
BEGIN;
CREATE TABLE user_preferences (
    user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    theme TEXT NOT NULL DEFAULT 'light',
    notifications JSONB NOT NULL DEFAULT '{}',
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Backfill existing users with defaults
INSERT INTO user_preferences (user_id)
SELECT id FROM users
ON CONFLICT (user_id) DO NOTHING;
COMMIT;

-- DOWN MIGRATION
-- BEGIN;
-- DROP TABLE IF EXISTS user_preferences;
-- COMMIT;

Safe migration rules:

  1. Add columns → Safe (nullable with default)
  2. Drop columns → Three-step: stop writing → backfill → drop
  3. Rename columns → Two-step: add new + dual-write → drop old
  4. Change type → Add new column → backfill → switch reads → drop old
  5. Add NOT NULL → Add with default → backfill nulls → add constraint
  6. Create index → Use CONCURRENTLY (PostgreSQL) or ONLINE (MySQL 8+)
  7. Drop index → Use CONCURRENTLY if available
  8. Add foreign key → Validate existing data first, validate constraint

Phase 5: Query Optimization

The EXPLAIN-based optimization workflow:

  1. Run EXPLAIN (ANALYZE, BUFFERS) on the slow query
  2. Identify the bottleneck: sequential scan? nested loop? sort?
  3. Check if statistics are up to date: ANALYZE table_name
  4. Consider: new index, query rewrite, schema change, or config tuning
  5. Test and measure improvement (not just EXPLAIN cost, actual timing)

Common optimization patterns:

ProblemSymptomFix
Missing indexSeq Scan on large tableAdd covering index for WHERE + JOIN + SELECT
N+1 queriesMany small queriesUse JOIN or batch load
Over-fetchingSELECT * on wide tablesSelect only needed columns
Lock contentionUPDATE waiting on locksBatch updates, use SKIP LOCKED
Statistics stalePlanner choosing wrong planANALYZE table; adjust default_statistics_target

Phase 6: Multi-Tenant Architecture

PatternDescriptionBest ForTrade-offs
Database per tenantSeparate DB per customerHigh security, complianceMany connections, harder cross-tenant queries
Schema per tenantSeparate schema per customerMedium security, shared DBEasier backups, moderate isolation
Row-level (shared)tenant_id column everywhereSimplicity, shared resourcesWeakest isolation, query complexity
HybridCombine patterns by tierEnterprise customers get isolationOperational complexity

NoSQL Design Patterns

MongoDB Schema Design

// Denormalized by access pattern
db.products.insertOne({
  _id: ObjectId(),
  name: "Widget",
  price: 9.99,
  // Embedded reviews (accessed together)
  reviews: [
    { user: "alice", rating: 5, comment: "Great!" },
    { user: "bob", rating: 4, comment: "Good value" }
  ],
  // Reference pattern for frequently-updated data
  inventory_warehouse_id: ObjectId("...")
});

MongoDB data modeling rules:

  • Embed for "contains" relationships (order → line items)
  • Reference for "uses" relationships (order → product)
  • Embed when data is read together, updated together
  • Reference when data is shared across documents
  • Size limit: documents must be under 16MB

Key-Value (Redis) Design

# Session store with TTL
SETEX session:abc123 3600 '{"user_id": 42, "role": "admin"}'

# Rate limiting with sliding window
INCR rate:user:42
EXPIRE rate:user:42 60

# Leaderboard with sorted sets
ZADD leaderboard:weekly 1000 player:42 950 player:17
ZREVRANGE leaderboard:weekly 0 9 WITHSCORES

Platform Notes

  • All platforms: This skill provides declarative knowledge — the agent applies patterns using its existing database tools and query capabilities.
  • Risk tier L2: Schema design is read-heavy during design phases. Migration execution requires human approval gates.
  • Database-specific tools: The skill supports SQL command generation but defers to the agent's MCP or native database tools for actual execution.
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall