Database schema designer

Database Schema Designer

Overview

This skill designs database schemas from requirements, including table structures, relationships, indexes, constraints, row-level security policies, and migration scripts. It produces production-ready SQL with proper normalization and performance considerations.

Instructions

Step 1: Understand the Domain

Identify the core entities, their relationships, and access patterns:

• What are the main entities? (users, orders, products, etc.)
• What are the relationships? (one-to-many, many-to-many)
• What queries will be most frequent? (reads vs writes, filtering patterns)
• Is multi-tenancy needed? What isolation level?

Step 2: Design the Schema

Apply these principles:

• 3NF by default — normalize to third normal form unless denormalization is justified by read performance
• UUIDs for public IDs — use UUID v7 (time-ordered) for primary keys exposed to clients; serial for internal-only
• Timestamps everywhere — created_at and updated_at on all tables
• Soft deletes when appropriate — deleted_at TIMESTAMPTZ for recoverable records
• JSONB for flexible fields — use for tenant-specific settings, metadata, but not for frequently queried fields

Step 3: Add Indexes

-- Always index foreign keys
CREATE INDEX idx_tasks_project_id ON tasks(project_id);

-- Composite indexes for common query patterns
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status) WHERE deleted_at IS NULL;

-- Partial indexes for filtered queries
CREATE INDEX idx_tasks_active ON tasks(assigned_to, due_date) WHERE status != 'done';

Step 4: Implement Tenant Isolation (if multi-tenant)

For shared-database multi-tenancy:

-- Add tenant_id to all tenant-scoped tables
ALTER TABLE projects ADD COLUMN tenant_id UUID NOT NULL REFERENCES tenants(id);

-- Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Create isolation policy
CREATE POLICY tenant_isolation ON projects
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID)
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::UUID);

-- Ensure tenant_id is in composite indexes
CREATE INDEX idx_projects_tenant ON projects(tenant_id, created_at DESC);

Step 5: Generate Migration Files

Output as numbered migration files compatible with common tools:

migrations/
  001_create_tenants.sql
  002_create_users.sql
  003_create_projects.sql
  004_enable_rls_policies.sql

Each migration should be idempotent where possible (CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS).

Examples

Example 1: Project Management Schema

User prompt:

Design a schema for a project management app with projects, tasks, members, and comments.

Agent output:

CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'archived', 'deleted')),
    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    title VARCHAR(500) NOT NULL,
    description TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'todo'
        CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
    priority SMALLINT NOT NULL DEFAULT 2 CHECK (priority BETWEEN 0 AND 4),
    assigned_to UUID REFERENCES users(id),
    due_date DATE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Indexes for common access patterns
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);
CREATE INDEX idx_tasks_assigned_due ON tasks(assigned_to, due_date)
    WHERE status NOT IN ('done');
CREATE INDEX idx_tasks_tenant ON tasks(tenant_id, created_at DESC);

Example 2: Adding RLS to Existing Tables

User prompt:

Add row-level security to our existing users and orders tables for multi-tenancy.

Agent output:

-- Step 1: Add tenant_id if missing
ALTER TABLE orders ADD COLUMN tenant_id UUID;
UPDATE orders SET tenant_id = (SELECT tenant_id FROM users WHERE users.id = orders.user_id);
ALTER TABLE orders ALTER COLUMN tenant_id SET NOT NULL;

-- Step 2: Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Step 3: Create policies
CREATE POLICY tenant_orders ON orders FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

CREATE POLICY tenant_users ON users FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

-- Step 4: Superuser bypass (for admin/migration scripts)
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
CREATE POLICY admin_bypass ON orders FOR ALL TO admin_role USING (true);

Guidelines

• RLS policies must cover ALL operations — use FOR ALL or create separate policies for SELECT, INSERT, UPDATE, DELETE
• Test RLS thoroughly — write integration tests that attempt cross-tenant access and verify it fails
• Index tenant_id first in composite indexes — PostgreSQL uses leftmost columns for index scans
• Don't over-normalize — if you always fetch user.name with orders, a denormalized order.user_name saves a JOIN on read-heavy tables
• Use CHECK constraints — they're free documentation and prevent invalid data at the database level
• Foreign keys with ON DELETE — always specify CASCADE, SET NULL, or RESTRICT explicitly
• Consider partitioning at scale — for tables exceeding 100M rows, partition by tenant_id or date range