---

name: database-schema-documentation description: Document database schemas, ERD diagrams, table relationships, indexes, and constraints. Use when documenting database schema, creating ERD diagrams, or writing table documentation.

Database Schema Documentation

Overview

Create comprehensive database schema documentation including entity relationship diagrams (ERD), table definitions, indexes, constraints, and data dictionaries.

When to Use

• Database schema documentation
• ERD (Entity Relationship Diagrams)
• Data dictionary creation
• Table relationship documentation
• Index and constraint documentation
• Migration documentation
• Database design specs

Schema Documentation Template

# Database Schema Documentation

**Database:** PostgreSQL 14.x
**Version:** 2.0
**Last Updated:** 2025-01-15
**Schema Version:** 20250115120000

## Overview

This database supports an e-commerce application with user management, product catalog, orders, and payment processing.

## Entity Relationship Diagram

```mermaid
erDiagram
    users ||--o{ orders : places
    users ||--o{ addresses : has
    users ||--o{ payment_methods : has
    orders ||--|{ order_items : contains
    orders ||--|| payments : has
    products ||--o{ order_items : includes
    products }o--|| categories : belongs_to
    products ||--o{ product_images : has
    products ||--o{ inventory : tracks

    users {
        uuid id PK
        string email UK
        string password_hash
        string name
        timestamp created_at
        timestamp updated_at
    }

    orders {
        uuid id PK
        uuid user_id FK
        string status
        decimal total_amount
        timestamp created_at
        timestamp updated_at
    }

    order_items {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal price
    }

    products {
        uuid id PK
        string name
        text description
        decimal price
        uuid category_id FK
        boolean active
    }

---

Tables

users

Stores user account information.

Columns:

Column	Type	Null	Default	Description
id	uuid	NO	gen_random_uuid()	Primary key
email	varchar(255)	NO	-	User email (unique)
password_hash	varchar(255)	NO	-	bcrypt hashed password
name	varchar(255)	NO	-	User's full name
email_verified	boolean	NO	false	Email verification status
two_factor_enabled	boolean	NO	false	2FA enabled flag
two_factor_secret	varchar(32)	YES	-	TOTP secret
created_at	timestamp	NO	now()	Record creation time
updated_at	timestamp	NO	now()	Last update time
deleted_at	timestamp	YES	-	Soft delete timestamp
last_login_at	timestamp	YES	-	Last login timestamp

Indexes:

CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NULL;

Constraints:

ALTER TABLE users
  ADD CONSTRAINT users_email_format
  CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

ALTER TABLE users
  ADD CONSTRAINT users_name_length
  CHECK (length(name) >= 2);

Triggers:

-- Update updated_at timestamp
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

Sample Data:

INSERT INTO users (email, password_hash, name, email_verified)
VALUES
  ('john@example.com', '$2b$12$...', 'John Doe', true),
  ('jane@example.com', '$2b$12$...', 'Jane Smith', true);

---

products

Stores product catalog information.

Columns:

Column	Type	Null	Default	Description
id	uuid	NO	gen_random_uuid()	Primary key
name	varchar(255)	NO	-	Product name
slug	varchar(255)	NO	-	URL-friendly name (unique)
description	text	YES	-	Product description
price	decimal(10,2)	NO	-	Product price in USD
compare_at_price	decimal(10,2)	YES	-	Original price (for sales)
sku	varchar(100)	NO	-	Stock keeping unit (unique)
category_id	uuid	NO	-	Foreign key to categories
brand	varchar(100)	YES	-	Product brand
active	boolean	NO	true	Product visibility
featured	boolean	NO	false	Featured product flag
metadata	jsonb	YES	-	Additional product metadata
created_at	timestamp	NO	now()	Record creation time
updated_at	timestamp	NO	now()	Last update time

Indexes:

CREATE UNIQUE INDEX idx_products_slug ON products(slug);
CREATE UNIQUE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_active ON products(active);
CREATE INDEX idx_products_featured ON products(featured) WHERE featured = true;
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

Foreign Keys:

ALTER TABLE products
  ADD CONSTRAINT fk_products_category
  FOREIGN KEY (category_id)
  REFERENCES categories(id)
  ON DELETE RESTRICT;

Full-Text Search:

-- Add full-text search column
ALTER TABLE products ADD COLUMN search_vector tsvector;

-- Create full-text index
CREATE INDEX idx_products_search ON products USING gin(search_vector);

-- Trigger to update search vector
CREATE TRIGGER products_search_vector_update
  BEFORE INSERT OR UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION
    tsvector_update_trigger(
      search_vector, 'pg_catalog.english',
      name, description, brand
    );

---

orders

Stores customer orders.

Columns:

Column	Type	Null	Default	Description
id	uuid	NO	gen_random_uuid()	Primary key
order_number	varchar(20)	NO	-	Human-readable order ID (unique)
user_id	uuid	NO	-	Foreign key to users
status	varchar(20)	NO	'pending'	Order status
subtotal	decimal(10,2)	NO	-	Items subtotal
tax	decimal(10,2)	NO	0	Tax amount
shipping	decimal(10,2)	NO	0	Shipping cost
total	decimal(10,2)	NO	-	Total amount
currency	char(3)	NO	'USD'	Currency code
notes	text	YES	-	Order notes
shipping_address	jsonb	NO	-	Shipping address
billing_address	jsonb	NO	-	Billing address
created_at	timestamp	NO	now()	Order creation time
updated_at	timestamp	NO	now()	Last update time
confirmed_at	timestamp	YES	-	Order confirmation time
shipped_at	timestamp	YES	-	Shipping time
delivered_at	timestamp	YES	-	Delivery time
cancelled_at	timestamp	YES	-	Cancellation time

Indexes:

CREATE UNIQUE INDEX idx_orders_order_number ON orders(order_number);
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);

Constraints:

ALTER TABLE orders
  ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded'));

ALTER TABLE orders
  ADD CONSTRAINT orders_total_positive
  CHECK (total >= 0);

Computed Columns:

-- Total is computed from subtotal + tax + shipping
ALTER TABLE orders
  ADD CONSTRAINT orders_total_computation
  CHECK (total = subtotal + tax + shipping);

---

order_items

Line items for each order.

Columns:

Column	Type	Null	Default	Description
id	uuid	NO	gen_random_uuid()	Primary key
order_id	uuid	NO	-	Foreign key to orders
product_id	uuid	NO	-	Foreign key to products
product_snapshot	jsonb	NO	-	Product data at order time
quantity	int	NO	-	Quantity ordered
unit_price	decimal(10,2)	NO	-	Price per unit
subtotal	decimal(10,2)	NO	-	Line item total
created_at	timestamp	NO	now()	Record creation time

Indexes:

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

Foreign Keys:

ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id)
  REFERENCES orders(id)
  ON DELETE CASCADE;

ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_product
  FOREIGN KEY (product_id)
  REFERENCES products(id)
  ON DELETE RESTRICT;

Constraints:

ALTER TABLE order_items
  ADD CONSTRAINT order_items_quantity_positive
  CHECK (quantity > 0);

ALTER TABLE order_items
  ADD CONSTRAINT order_items_subtotal_computation
  CHECK (subtotal = quantity * unit_price);

---

Views

active_products_view

Shows only active products with category information.

CREATE VIEW active_products_view AS
SELECT
  p.id,
  p.name,
  p.slug,
  p.description,
  p.price,
  p.compare_at_price,
  p.sku,
  p.brand,
  c.name as category_name,
  c.slug as category_slug,
  (SELECT COUNT(*) FROM order_items oi WHERE oi.product_id = p.id) as times_ordered,
  (SELECT AVG(rating) FROM product_reviews pr WHERE pr.product_id = p.id) as avg_rating
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.active = true;

user_order_summary

Aggregated order statistics per user.

CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
  u.id as user_id,
  u.email,
  u.name,
  COUNT(o.id) as total_orders,
  SUM(o.total) as total_spent,
  AVG(o.total) as average_order_value,
  MAX(o.created_at) as last_order_date,
  MIN(o.created_at) as first_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status != 'cancelled'
GROUP BY u.id, u.email, u.name;

-- Refresh strategy
CREATE INDEX idx_user_order_summary_user_id ON user_order_summary(user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;

---

Functions

calculate_order_total

Calculates order total with tax and shipping.

CREATE OR REPLACE FUNCTION calculate_order_total(
  p_subtotal decimal,
  p_tax_rate decimal,
  p_shipping decimal
)
RETURNS decimal AS $$
BEGIN
  RETURN ROUND((p_subtotal * (1 + p_tax_rate) + p_shipping)::numeric, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

update_updated_at_column

Trigger function to automatically update updated_at timestamp.

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

---

Data Dictionary

Enum Types

-- Order status values
CREATE TYPE order_status AS ENUM (
  'pending',
  'confirmed',
  'processing',
  'shipped',
  'delivered',
  'cancelled',
  'refunded'
);

-- Payment status values
CREATE TYPE payment_status AS ENUM (
  'pending',
  'processing',
  'succeeded',
  'failed',
  'refunded'
);

JSONB Structures

shipping_address format

{
  "street": "123 Main St",
  "street2": "Apt 4B",
  "city": "New York",
  "state": "NY",
  "postalCode": "10001",
  "country": "US"
}

product_snapshot format

{
  "name": "Product Name",
  "sku": "PROD-123",
  "price": 99.99,
  "image": "https://cdn.example.com/product.jpg"
}

---

Migrations

Migration: 20250115120000_add_two_factor_auth

-- Up
ALTER TABLE users ADD COLUMN two_factor_enabled BOOLEAN DEFAULT FALSE;
ALTER TABLE users ADD COLUMN two_factor_secret VARCHAR(32);

CREATE TABLE two_factor_backup_codes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  code_hash VARCHAR(255) NOT NULL,
  used_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_2fa_backup_codes_user_id ON two_factor_backup_codes(user_id);

-- Down
DROP TABLE two_factor_backup_codes;
ALTER TABLE users DROP COLUMN two_factor_secret;
ALTER TABLE users DROP COLUMN two_factor_enabled;

---

Performance Optimization

Recommended Indexes

-- Frequently queried columns
CREATE INDEX CONCURRENTLY idx_users_email_verified ON users(email_verified);
CREATE INDEX CONCURRENTLY idx_products_price ON products(price);
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);

-- Composite indexes for common queries
CREATE INDEX CONCURRENTLY idx_products_category_active
  ON products(category_id, active)
  WHERE active = true;

CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON orders(user_id, created_at DESC);

Query Optimization

-- EXPLAIN ANALYZE for slow queries
EXPLAIN ANALYZE
SELECT p.*, c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.active = true
ORDER BY p.created_at DESC
LIMIT 20;

-- Add covering index if needed
CREATE INDEX idx_products_active_created
  ON products(active, created_at DESC)
  INCLUDE (name, price, slug);

---

Backup & Recovery

Backup Schedule

• Full Backup: Daily at 2 AM UTC
• Incremental Backup: Every 6 hours
• WAL Archiving: Continuous
• Retention: 30 days

Backup Commands

# Full backup
pg_dump -h localhost -U postgres -Fc database_name > backup.dump

# Restore
pg_restore -h localhost -U postgres -d database_name backup.dump

# Backup specific tables
pg_dump -h localhost -U postgres -t users -t orders database_name > tables.sql

---

Data Retention Policy

Table	Retention	Archive Strategy
users	Indefinite	Soft delete after 2 years inactive
orders	7 years	Move to archive after 2 years
order_items	7 years	Move to archive with orders
logs	90 days	Delete after retention period

## Best Practices

### ✅ DO
- Document all tables and columns
- Create ERD diagrams
- Document indexes and constraints
- Include sample data
- Document foreign key relationships
- Show JSONB field structures
- Document triggers and functions
- Include migration scripts
- Specify data types precisely
- Document performance considerations

### ❌ DON'T
- Skip constraint documentation
- Forget to version schema changes
- Ignore performance implications
- Skip index documentation
- Forget to document enum values

## Resources

- [PostgreSQL Documentation](https://www.postgresql.org/docs/)
- [dbdiagram.io](https://dbdiagram.io/) - ERD tool
- [SchemaSpy](https://schemaspy.org/) - Schema documentation generator
- [Mermaid ERD Syntax](https://mermaid.js.org/syntax/entityRelationshipDiagram.html)