Design and implement production-ready PostgreSQL databases from requirements through deployment.
Use this skill whenever someone wants to create a database schema, design entity relationships,
normalize data structures, create tables/indices, write SQL migrations, or implement PostgreSQL
databases — whether for simple CRUD apps or complex enterprise systems. Triggers on phrases like
"design a database", "create schema", "normalize tables", "PostgreSQL", "write migration",
"ERD", "entity relationship", "database design", or when asked to organize data into tables
with foreign keys. Respects implementation preferences (raw SQL, Prisma, Drizzle, etc.) and
adapts documentation output accordingly.
Design and implement production-ready PostgreSQL databases from requirements through deployment.
Use this skill whenever someone wants to create a database schema, design entity relationships,
normalize data structures, create tables/indices, write SQL migrations, or implement PostgreSQL
databases — whether for simple CRUD apps or complex enterprise systems. Triggers on phrases like
"design a database", "create schema", "normalize tables", "PostgreSQL", "write migration",
"ERD", "entity relationship", "database design", or when asked to organize data into tables
with foreign keys. Respects implementation preferences (raw SQL, Prisma, Drizzle, etc.) and
adapts documentation output accordingly.
license
MIT
compatibility
PostgreSQL 12+
metadata
{"author":"agent-skills","version":"1.0"}
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.
-- Use identity columns for auto-increment (PostgreSQL 10+)CREATE TABLE users (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULLUNIQUE,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW()
);
-- Use immutable audit patternCREATE TABLE orders (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
user_id UUID NOT NULLREFERENCES users(id),
status VARCHAR(20) NOT NULLCHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT NOW()
);
-- Order items with historical pricing (capture price at purchase time)CREATE TABLE order_items (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
order_id UUID NOT NULLREFERENCES orders(id),
product_id UUID NOT NULLREFERENCES products(id),
quantity INTEGERNOT NULLCHECK (quantity >0),
price_at_purchase DECIMAL(10,2) NOT NULL, -- Capture current price at order time
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW()
);
-- Index strategy: index foreign keys and query columnsCREATE 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:
Object
Convention
Example
Tables
plural_snake_case
users, order_items
Columns
singular_snake_case
user_id, created_at
Primary Keys
id or {table_singular}_id
id, user_id
Foreign Keys
{referenced_table_singular}_id
user_id
Indexes
idx_{table}_{column(s)}
idx_orders_user_id
Constraints
{table}_{column}_{type}
users_email_unique
Sequences
{table}_{column}_seq
users_id_seq
PostgreSQL Extensions
Use appropriate extensions based on requirements:
-- UUID generation (universally useful)CREATE EXTENSION IF NOTEXISTS "uuid-ossp"; -- or "pgcrypto"-- Full-text searchCREATE EXTENSION IF NOTEXISTS pg_trgm; -- for fuzzy matching-- JSON handling-- Use JSONB for indexed, structured dataALTER TABLE products ADDCOLUMN metadata JSONB;
-- Hstore for key-value (when JSONB is overkill)CREATE EXTENSION IF NOTEXISTS 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 columnALTER TABLE jobs ADDCOLUMN search_vector TSVECTOR;
-- Index for fast full-text searchCREATE INDEX idx_jobs_search_vector ON jobs USING GIN(search_vector);
-- Function to update search vector with weights (A=highest, B=medium, C=low)CREATEOR REPLACE FUNCTION jobs_search_vector_update()
RETURNSTRIGGERAS $$
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');
RETURNNEW;
END;
$$ LANGUAGE plpgsql;
-- Trigger to auto-update on insert/updateCREATETRIGGER trigger_jobs_search_vector_update
BEFORE INSERTORUPDATEOF title, description, requirements ON jobs
FOREACHROWEXECUTEFUNCTION 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.sqlCREATE TABLE users (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULLUNIQUE,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ NOT NULLDEFAULT 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 dateCREATE 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)
) PARTITIONBYRANGE (recorded_at);
-- Create monthly partitionsCREATE TABLE readings_2024_01 PARTITIONOF readings
FORVALUESFROM ('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 queriesCREATE INDEX idx_readings_sensor_time ON readings (sensor_id, recorded_at DESC);
-- Partition auto-creation functionCREATEOR 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 aggregatesCREATE MATERIALIZED VIEW mv_hourly_readings ASSELECT
sensor_id,
date_trunc('hour', recorded_at) AShour,
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
GROUPBY sensor_id, date_trunc('hour', recorded_at)
WITHNO DATA;
CREATEUNIQUE INDEX idx_mv_hourly ON mv_hourly_readings (sensor_id, hour);
-- Refresh function for materialized viewsCREATEOR 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 tablesALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- Force RLS even for table ownersALTER TABLE tenants FORCE ROW LEVEL SECURITY;
ALTER TABLE users FORCE ROW LEVEL SECURITY;
-- RLS policies for tenant isolationCREATE 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 controlCREATE ROLE app_user;
CREATE ROLE app_service;
-- Grant permissions to app_user roleGRANTCONNECTON DATABASE CURRENTTO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANTSELECT, INSERT, UPDATE, DELETEONALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECTONALL SEQUENCES IN SCHEMA public TO app_user;
-- app_service bypasses RLS for migrations and admin tasksGRANTALLONALL TABLES IN SCHEMA public TO app_service;
GRANTALLONALL SEQUENCES IN SCHEMA public TO app_service;
-- Helper functions for context managementCREATEOR 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;
CREATEOR REPLACE FUNCTION get_current_tenant_id()
RETURNS UUID AS $$
BEGINRETURNNULLIF(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 KEYDEFAULT gen_random_uuid(),
account_id UUID NOT NULLREFERENCES accounts(id),
amount DECIMAL(19, 4) NOT NULLCHECK (amount >0),
type VARCHAR(10) NOT NULLCHECK (type IN ('credit', 'debit')),
description TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);
-- Index for audit queriesCREATE INDEX idx_transactions_account_created ON transactions(account_id, created_at DESC);
-- Immutability via triggers (prevents UPDATE/DELETE)CREATEOR REPLACE FUNCTION prevent_transaction_update()
RETURNSTRIGGERAS $$
BEGIN
RAISE EXCEPTION 'UPDATE operations are prohibited on transactions table';
END;
$$ LANGUAGE plpgsql;
CREATETRIGGER trigger_prevent_transaction_update
BEFORE UPDATEON transactions FOREACHROWEXECUTEFUNCTION prevent_transaction_update();
CREATETRIGGER trigger_prevent_transaction_delete
BEFORE DELETEON transactions FOREACHROWEXECUTEFUNCTION prevent_transaction_delete();
-- Computed balance view (balances are calculated, not stored)CREATEOR REPLACE VIEW account_balances ASSELECT
a.id AS account_id,
a.name AS account_name,
COALESCE(SUM(
CASEWHEN t.type ='credit'THEN t.amount ELSE-t.amount END
), 0) AS balance
FROM accounts a
LEFTJOIN transactions t ON a.id = t.account_id
GROUPBY 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 KEYDEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULLUNIQUE,
password_hash VARCHAR(100) NOT NULL,
full_name VARCHAR(100) NOT NULL,
role_id UUID NOT NULLREFERENCES roles(id),
created_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT NOW(),
deleted_at TIMESTAMPTZ NULL-- NULL means active
);
-- Index for filtering active usersCREATE 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 ISNULL;
-- View for active users onlyCREATEVIEW active_users ASSELECT id, email, full_name, role_id, created_at, updated_at
FROM users
WHERE deleted_at ISNULL;
-- View for user details with role nameCREATEVIEW user_details ASSELECT
u.id, u.email, u.full_name, r.name AS role,
u.created_at, u.updated_at,
u.deleted_at ISNOT NULLAS is_inactive
FROM users u
JOIN roles r ON u.role_id = r.id;
Include seed data for reference tables and initial setup:
-- Seed data for rolesINSERT INTO roles (name, description) VALUES
('admin', 'Full system access'),
('member', 'Standard user access'),
('guest', 'Limited read-only access');
-- Seed data for categoriesINSERT INTO categories (name, slug) VALUES
('Technology', 'technology'),
('Business', 'business'),
('Science', 'science');
Quick Reference
Data Type Selection
Data
Use
Avoid
IDs
UUID or BIGSERIAL
VARCHAR for IDs
Names
VARCHAR(n)
TEXT (unless truly unlimited)
Prices
DECIMAL(10,2)
FLOAT
Booleans
BOOLEAN
CHAR(1), INTEGER
Timestamps
TIMESTAMPTZ
DATE + TIME separate
Enums
VARCHAR with CHECK
PostgreSQL ENUM (hard to modify)
JSON
JSONB (with indexes)
JSON (parsing each time)
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
