database-schema-designer

Design production-ready 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.

Jump to install

Repository: JPeetz/agent-skills
Last source activity: August 31, 2026 at 06:31
Detected SKILL.md language: English
Stars: 5
Forks: 1

Install options

The review-first prompt is selected by default. You can switch to a direct command or download a local copy.

Review the source files

Read SKILL.md and any companion files shown by SkillsMP before deciding whether to install.

Back to search Back to installation

name	database-schema-designer
description	Design production-ready 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.
version	1.0.0
platforms	["openclaw","claude","codex","cursor","gemini","copilot","opencode","windsurf"]
author	{"name":"Skill Foundry (Forge)","source":"PlanetScale Database Skills, Supabase Agent Skills, softaworks/agent-toolkit"}
license	MIT
risk_tier	L2
tags	["database","schema","sql","nosql","postgres","mysql","migration","optimization","design"]
requires	{"binaries":[]}

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:

Never DROP without backup — Always generate backup commands first
Always generate rollback — Every migration includes verified reversal
Test migrations on staging — Never run directly on production
Lock-aware design — Schema changes must consider lock duration
Data integrity first — Validate before and after every migration
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)

-- 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)
);

Database	Index Types	When to Use
PostgreSQL	B-tree (default), Hash, GiST, GIN, BRIN, SP-GiST	B-tree for equality/range; GIN for full-text/arrays; BRIN for large sequential data
MySQL/InnoDB	B-tree (clustered PK), Full-text, Spatial	B-tree for most cases; Full-text for text search
SQLite	B-tree (default)	All standard cases
MongoDB	Single-field, Compound, Multikey, Text, Geospatial, Hashed, TTL	Compound for common queries; TTL for expiring data

-- 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;

Problem	Symptom	Fix
Missing index	Seq Scan on large table	Add covering index for WHERE + JOIN + SELECT
N+1 queries	Many small queries	Use JOIN or batch load
Over-fetching	SELECT * on wide tables	Select only needed columns
Lock contention	UPDATE waiting on locks	Batch updates, use SKIP LOCKED
Statistics stale	Planner choosing wrong plan	ANALYZE table; adjust default_statistics_target

Pattern	Description	Best For	Trade-offs
Database per tenant	Separate DB per customer	High security, compliance	Many connections, harder cross-tenant queries
Schema per tenant	Separate schema per customer	Medium security, shared DB	Easier backups, moderate isolation
Row-level (shared)	tenant_id column everywhere	Simplicity, shared resources	Weakest isolation, query complexity
Hybrid	Combine patterns by tier	Enterprise customers get isolation	Operational complexity

// 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("...")
});

# 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