From skill-forge
Enforce schema discipline: queries-first design, index-every-filter, safe migrations, verify-at-scale thinking. Prevents the common failure of designing schemas for demos that break in production. Use when creating tables, planning migrations, designing data models, or when user mentions database, schema, migration, indexing, multi-tenant.
How this skill is triggered — by the user, by Claude, or both
Slash command
/skill-forge:db-schemaThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Enforce queries-first schema discipline — design every table around its access patterns, index every filter, and verify migrations are safe at production scale.
Enforce queries-first schema discipline — design every table around its access patterns, index every filter, and verify migrations are safe at production scale.
ACTIVE on every schema change. Every CREATE TABLE, every ALTER, every migration. Think at scale before writing DDL.
Before writing ANY schema:
1. QUERIES FIRST — what queries will run against this? (schema serves queries, not the other way around)
2. SCALE QUESTION — what happens at 10M rows? 100M? Does this still work?
3. INDEX PLAN — every WHERE, JOIN, ORDER BY gets an index. No exceptions.
4. MIGRATION SAFETY — can this ALTER run without locking the table for minutes?
5. VERIFY — explain analyze the critical queries. Prove they use indexes.
created_at/updated_at on any tableBefore creating any table:
| Situation | Strategy | Why |
|---|---|---|
| Read-heavy, rarely changes | Denormalize | Avoid JOINs at query time |
| Write-heavy, consistency critical | Normalize (3NF) | Single source of truth |
| Analytics/reporting | Star schema (denormalized) | Optimized for aggregation |
| User-facing CRUD | 3NF with strategic denorm | Balance of integrity + speed |
-- Index every column that appears in:
-- WHERE, JOIN ON, ORDER BY, GROUP BY
-- Composite index order matters: most selective first
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- This supports: WHERE user_id = ? AND status = ?
-- Also supports: WHERE user_id = ? (leftmost prefix)
-- Does NOT support: WHERE status = ? (not leftmost)
-- Covering index (includes all columns query needs)
CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);
-- Query can be answered entirely from index (no table lookup)
Never DROP COLUMN in production without a 2-step process:
Never rename columns directly:
Always make migrations reversible:
-- UP
ALTER TABLE users ADD COLUMN display_name VARCHAR(255);
-- DOWN (must always exist)
ALTER TABLE users DROP COLUMN display_name;
Lock-free schema changes for large tables:
-- BAD: locks table for minutes on 10M+ rows
ALTER TABLE orders ADD COLUMN priority INT DEFAULT 0;
-- GOOD: add nullable first, backfill in batches
ALTER TABLE orders ADD COLUMN priority INT;
-- Then backfill in 10K batches with sleep between
Test migrations against production-sized data:
| Pattern | Isolation | Complexity | When to Use |
|---|---|---|---|
| Shared table + tenant_id | Logical | Low | < 1000 tenants, simple data |
| Schema per tenant | Strong | Medium | Enterprise, compliance needs |
| Database per tenant | Maximum | High | Regulated industries, massive scale |
-- Every table has tenant_id
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
name VARCHAR(255) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Row-level security (PostgreSQL)
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
USING (tenant_id = current_setting('app.tenant_id')::UUID);
-- Composite index (tenant first!)
CREATE INDEX idx_projects_tenant ON projects(tenant_id, created_at DESC);
CREATE TABLE posts (
id UUID PRIMARY KEY,
title TEXT NOT NULL,
deleted_at TIMESTAMPTZ, -- NULL = active, timestamp = deleted
-- ...
);
-- Default scope excludes deleted
CREATE VIEW active_posts AS SELECT * FROM posts WHERE deleted_at IS NULL;
-- Index for active records only (partial index)
CREATE INDEX idx_posts_active ON posts(created_at) WHERE deleted_at IS NULL;
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
record_id UUID NOT NULL,
action TEXT NOT NULL, -- INSERT, UPDATE, DELETE
old_data JSONB,
new_data JSONB,
actor_id UUID,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Trigger-based (automatic)
CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log(table_name, record_id, action, old_data, new_data, actor_id)
VALUES (TG_TABLE_NAME, COALESCE(NEW.id, OLD.id), TG_OP,
to_jsonb(OLD), to_jsonb(NEW), current_setting('app.actor_id', true)::UUID);
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
-- BAD: string type column
-- commentable_type: 'Post' | 'Video' | 'Comment'
-- GOOD: separate FKs (type-safe, indexable)
CREATE TABLE comments (
id UUID PRIMARY KEY,
body TEXT NOT NULL,
post_id UUID REFERENCES posts(id),
video_id UUID REFERENCES videos(id),
-- Exactly one must be non-null
CONSTRAINT one_parent CHECK (
(post_id IS NOT NULL)::int + (video_id IS NOT NULL)::int = 1
)
);
created_at/updated_at to every tablenpx claudepluginhub adit-jain-srm/skill-forge --plugin skill-forgeProvides UI/UX resources: 50+ styles, color palettes, font pairings, guidelines, charts for web/mobile across React, Next.js, Vue, Svelte, Tailwind, React Native, Flutter. Aids planning, building, reviewing interfaces.
Fetches up-to-date documentation from Context7 for libraries and frameworks like React, Next.js, Prisma. Use for setup questions, API references, and code examples.