PostgreSQL Database Designer

Design and implement professional PostgreSQL databases following industry best practices.

Core Principles

Data Integrity First: Every decision prioritizes data accuracy and consistency. Use constraints, foreign keys, and appropriate data types to enforce integrity at the database level.

Normalization as Foundation: Eliminate redundant data and prevent update anomalies. Target 3NF (Third Normal Form) unless performance analysis justifies denormalization.

Schema as Living Document: Design schemas that adapt to changing requirements without major restructuring. Use extensibility patterns from the start.

Security by Design: Implement row-level security, proper access patterns, and encryption for sensitive data from day one.

Design Lifecycle

Phase 1: Requirements Analysis

Before writing any SQL, understand the domain:

1. Identify entities (nouns in requirements) → potential tables
2. Identify actions/processes (verbs) → potential relationships
3. Identify business rules → constraints
4. Identify reporting/analytics needs → indexes, materialized views
5. Identify security requirements → RLS, encryption, audit trails

Ask clarifying questions if requirements are ambiguous:

• What entities exist and what attributes do they have?
• How do entities relate to each other? (1:1, 1:N, N:N)
• What operations will be performed most frequently?
• What are the scalability requirements?

Phase 2: Conceptual Design (ERD)

Create a high-level entity-relationship diagram using Mermaid:

erDiagram
    USERS ||--o{ ORDERS : places
    ORDERS ||--o{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : "ordered in"

    USERS {
        uuid id PK
        varchar email UK
        varchar name
        timestamptz created_at
    }

    ORDERS {
        uuid id PK
        uuid user_id FK
        varchar status
        timestamptz created_at
    }

    PRODUCTS {
        uuid id PK
        varchar name
        decimal price
        int stock_qty
    }

    ORDER_ITEMS {
        uuid order_id FK
        uuid product_id FK
        int quantity
    }

Phase 3: Logical Design (Normalized Schema)

Apply normalization rules. Target 3NF:

Document the logical model:

## Logical Model

### Users
| Column | Type | Constraints | Notes |
|--------|------|-------------|-------|
| id | UUID | PK, DEFAULT gen_random_uuid() | Surrogate key |
| email | VARCHAR(255) | UNIQUE, NOT NULL | |
| name | VARCHAR(100) | NOT NULL | |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |

### Orders
| Column | Type | Constraints | Notes |
|--------|------|-------------|-------|
| id | UUID | PK | |
| user_id | UUID | FK → users(id), NOT NULL | |
| status | VARCHAR(20) | CHECK IN ('pending','confirmed','shipped','delivered','cancelled') | |
| created_at | TIMESTAMPTZ | DEFAULT NOW() | |

### Order_Items (junction table for N:N)
| Column | Type | Constraints | Notes |
|--------|------|-------------|-------|
| order_id | UUID | FK → orders(id) | |
| product_id | UUID | FK → products(id) | |
| quantity | INTEGER | CHECK > 0, NOT NULL | |

### Products
| Column | Type | Constraints | Notes |
|--------|------|-------------|-------|
| id | UUID | PK | |
| name | VARCHAR(255) | NOT NULL | |
| price | DECIMAL(10,2) | CHECK >= 0 | |
| stock_qty | INTEGER | DEFAULT 0, CHECK >= 0 | |

Phase 4: Physical Design (PostgreSQL Optimization)

Choose data types and storage strategies:

-- Use identity columns for auto-increment (PostgreSQL 10+)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Use immutable audit pattern
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Order items with historical pricing (capture price at purchase time)
CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(id),
    product_id UUID NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price_at_purchase DECIMAL(10,2) NOT NULL,  -- Capture current price at order time
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index strategy: index foreign keys and query columns
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

Naming Conventions

Follow consistent naming for maintainability:

PostgreSQL Extensions

Use appropriate extensions based on requirements:

-- UUID generation (universally useful)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- or "pgcrypto"

-- Full-text search
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- for fuzzy matching

-- JSON handling
-- Use JSONB for indexed, structured data
ALTER TABLE products ADD COLUMN metadata JSONB;

-- Hstore for key-value (when JSONB is overkill)
CREATE EXTENSION IF NOT EXISTS hstore;

-- For temporal data
-- Use tsrange/range types for PostgreSQL 9.2+
-- Use tstzrange for timezone-aware ranges

Full-Text Search Patterns

For search features, use weighted tsvector columns with GIN indexes:

-- Add search_vector column
ALTER TABLE jobs ADD COLUMN search_vector TSVECTOR;

-- Index for fast full-text search
CREATE INDEX idx_jobs_search_vector ON jobs USING GIN(search_vector);

-- Function to update search vector with weights (A=highest, B=medium, C=low)
CREATE OR REPLACE FUNCTION jobs_search_vector_update()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(NEW.requirements, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to auto-update on insert/update
CREATE TRIGGER trigger_jobs_search_vector_update
    BEFORE INSERT OR UPDATE OF title, description, requirements ON jobs
    FOR EACH ROW EXECUTE FUNCTION jobs_search_vector_update();

-- Example search query
-- SELECT * FROM jobs WHERE search_vector @@ plainto_tsquery('english', 'senior developer python');

Implementation Patterns

Standard Pattern (Raw SQL)

Generate migration files:

-- migrations/001_create_users.sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

-- migrations/002_create_orders.sql
-- ... etc

ORM Pattern (Prisma/Drizzle)

Generate schema files and suggest documentation:

// schema.prisma
model User {
  id        DateTime   @id @default(uuid())
  email     String     @unique
  name      String
  orders    Order[]
  createdAt DateTime   @default(now())
}

model Order {
  id        String   @id @default(uuid())
  userId    String
  user      User     @relation(fields: [userId], references: [id])
  status    String
  items     OrderItem[]
  createdAt DateTime @default(now())
}

Documentation recommendation: When using ORMs, suggest generating documentation separately since ORM schemas serve as documentation.

Enterprise Patterns

Partitioning (Large Tables)

-- Range partitioning by date
CREATE TABLE readings (
    id BIGSERIAL,
    sensor_id UUID NOT NULL,
    temperature DECIMAL(6,3),
    humidity DECIMAL(6,2),
    pressure DECIMAL(8,3),
    recorded_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, recorded_at)
) PARTITION BY RANGE (recorded_at);

-- Create monthly partitions
CREATE TABLE readings_2024_01 PARTITION OF readings
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- BRIN index for time-series data (append-optimized)
CREATE INDEX idx_readings_recorded_at_brin ON readings USING BRIN (recorded_at);

-- Composite index for sensor-specific time queries
CREATE INDEX idx_readings_sensor_time ON readings (sensor_id, recorded_at DESC);

-- Partition auto-creation function
CREATE OR REPLACE FUNCTION create_monthly_partition(partition_date DATE)
RETURNS void AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := date_trunc('month', partition_date);
    end_date := start_date + INTERVAL '1 month';
    partition_name := 'readings_' || to_char(start_date, 'YYYY_MM');
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF readings FOR VALUES FROM (%L) TO (%L)',
        partition_name, start_date, end_date
    );
END;
$$ LANGUAGE plpgsql;

Materialized Views (Analytics)

For dashboards and reporting, pre-compute aggregations:

-- Materialized view for hourly aggregates
CREATE MATERIALIZED VIEW mv_hourly_readings AS
SELECT
    sensor_id,
    date_trunc('hour', recorded_at) AS hour,
    AVG(temperature) AS avg_temp,
    AVG(humidity) AS avg_humidity,
    MIN(temperature) AS min_temp,
    MAX(temperature) AS max_temp,
    COUNT(*) AS reading_count
FROM readings
GROUP BY sensor_id, date_trunc('hour', recorded_at)
WITH NO DATA;

CREATE UNIQUE INDEX idx_mv_hourly ON mv_hourly_readings (sensor_id, hour);

-- Refresh function for materialized views
CREATE OR REPLACE FUNCTION refresh_reading_views()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_hourly_readings;
END;
$$ LANGUAGE plpgsql;

Row-Level Security (Multi-Tenant)

-- Enable RLS on tables
ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Force RLS even for table owners
ALTER TABLE tenants FORCE ROW LEVEL SECURITY;
ALTER TABLE users FORCE ROW LEVEL SECURITY;

-- RLS policies for tenant isolation
CREATE POLICY tenant_isolation_policy ON tenants
    USING (id = current_setting('app.tenant_id', true)::UUID);

CREATE POLICY users_tenant_isolation_policy ON users
    USING (tenant_id = current_setting('app.tenant_id', true)::UUID);

-- Application roles for access control
CREATE ROLE app_user;
CREATE ROLE app_service;

-- Grant permissions to app_user role
GRANT CONNECT ON DATABASE CURRENT TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- app_service bypasses RLS for migrations and admin tasks
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_service;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO app_service;

-- Helper functions for context management
CREATE OR REPLACE FUNCTION set_tenant_context(p_tenant_id UUID)
RETURNS VOID AS $$
BEGIN
    PERFORM set_config('app.tenant_id', p_tenant_id::TEXT, false);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION get_current_tenant_id()
RETURNS UUID AS $$
BEGIN
    RETURN NULLIF(current_setting('app.tenant_id', true), '')::UUID;
END;
$$ LANGUAGE plpgsql STABLE;

Audit Trails (Immutable Ledger)

For financial or audit-critical data, enforce immutability:

-- Transactions table (append-only)
CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id UUID NOT NULL REFERENCES accounts(id),
    amount DECIMAL(19, 4) NOT NULL CHECK (amount > 0),
    type VARCHAR(10) NOT NULL CHECK (type IN ('credit', 'debit')),
    description TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- Index for audit queries
CREATE INDEX idx_transactions_account_created ON transactions(account_id, created_at DESC);

-- Immutability via triggers (prevents UPDATE/DELETE)
CREATE OR REPLACE FUNCTION prevent_transaction_update()
RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION 'UPDATE operations are prohibited on transactions table';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_prevent_transaction_update
    BEFORE UPDATE ON transactions FOR EACH ROW EXECUTE FUNCTION prevent_transaction_update();

CREATE TRIGGER trigger_prevent_transaction_delete
    BEFORE DELETE ON transactions FOR EACH ROW EXECUTE FUNCTION prevent_transaction_delete();

-- Computed balance view (balances are calculated, not stored)
CREATE OR REPLACE VIEW account_balances AS
SELECT
    a.id AS account_id,
    a.name AS account_name,
    COALESCE(SUM(
        CASE WHEN t.type = 'credit' THEN t.amount ELSE -t.amount END
    ), 0) AS balance
FROM accounts a
LEFT JOIN transactions t ON a.id = t.account_id
GROUP BY a.id, a.name;

Soft Delete Pattern

For user management and systems requiring data retention:

-- Users table with soft delete (deleted_at IS NULL = active)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(100) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role_id UUID NOT NULL REFERENCES roles(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ NULL  -- NULL means active
);

-- Index for filtering active users
CREATE INDEX idx_users_deleted_at ON users(deleted_at);

-- Partial index for active-only queries (performance optimization)
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;

-- View for active users only
CREATE VIEW active_users AS
    SELECT id, email, full_name, role_id, created_at, updated_at
    FROM users
    WHERE deleted_at IS NULL;

-- View for user details with role name
CREATE VIEW user_details AS
    SELECT
        u.id, u.email, u.full_name, r.name AS role,
        u.created_at, u.updated_at,
        u.deleted_at IS NOT NULL AS is_inactive
    FROM users u
    JOIN roles r ON u.role_id = r.id;

Output Structure

For every database design task, produce:

output/
├── SPEC.md              # Design decisions and rationale
├── migrations/          # SQL migration files (if raw SQL)
│   └── 001_*.sql
├── schema.sql           # Full schema (for reference)
├── seed_data.sql        # Sample/test data (if needed)
└── references/          # Additional documentation
    ├── erd.md           # Entity-relationship diagram
    ├── data_dictionary.md # Column-level documentation
    └── decisions.md     # Design decision log

Seed Data Pattern

Include seed data for reference tables and initial setup:

-- Seed data for roles
INSERT INTO roles (name, description) VALUES
    ('admin', 'Full system access'),
    ('member', 'Standard user access'),
    ('guest', 'Limited read-only access');

-- Seed data for categories
INSERT INTO categories (name, slug) VALUES
    ('Technology', 'technology'),
    ('Business', 'business'),
    ('Science', 'science');

Quick Reference

Data Type Selection

Constraint Checklist

• [ ] Primary key on every table
• [ ] Foreign keys for all relationships
• [ ] NOT NULL on required columns
• [ ] UNIQUE on columns that must be distinct
• [ ] CHECK constraints for business rules
• [ ] Indexes on foreign keys and frequently queried columns

References

• Naming Conventions
• Data Types Guide
• PostgreSQL Extensions
• Normalization Guide