design-database-schema · davepoon — Claude Code command: database schema design
design-database-schema — a command for Claude Code and Cursor that designs database schemas with indexes, constraints, and relationships. Triggered by an explicit request for a schema or ER model.
MIT ★ 3,785 updated 2026-10-25
/plugin marketplace add davepoon/buildwithclaude
/plugin install all-commands@buildwithclaudeWhat design-database-schema does
The command helps design database structure: tables, fields, data types, foreign keys, indexes, and constraints. It includes an instruction that covers requirements analysis, ER diagram construction, and sample schemas for users, profiles, roles, categories, products, and orders. It outputs PostgreSQL SQL, sets up full-text search with tsvector, adds validation via CHECK and ENUM, and normalizes data across related tables.
When working across multiple platforms, install it in Claude Code via the marketplace; in Cursor, copy .claude/commands/design-database-schema.md manually to .cursor/commands/.
The command generates ready-to-run DDL for PostgreSQL. For MySQL or SQLite, you will need to adjust syntax manually — types, triggers, and generated columns differ.
What it helps with
- design a normalized database schema with indexes
- build an ER model for e-commerce or SaaS
- set up table relationships, roles, and permissions
- add full-text search and integrity constraints
- describe data structure before migrations
Example prompts for the agent
- Design a database schema for an online store: products, orders, categories, users.
- Build an ER model for a blog with roles and permissions.
- Create a table schema for a SaaS with subscriptions and pricing tiers.
How to install design-database-schema
1Plugin marketplace
/plugin marketplace add davepoon/buildwithclaude/plugin install all-commands@buildwithclaude2Manually: put the files in place
.claude/commands/design-database-schema.mdCall it with /design-database-schema. For all projects use ~/.claude/commands/
Manually: put the files in place
~/.codex/prompts/design-database-schema.mdCall it with /prompts:design-database-schema. Codex marks this format deprecated in favour of skills
Manually: put the files in place
.github/prompts/design-database-schema.prompt.mdManually: put the files in place
.gemini/commands/design-database-schema.tomlGemini stores commands as TOML: the prompt text goes into the prompt field
Questions about design-database-schema
How do I install design-database-schema in Claude Code?
Run /plugin marketplace add davepoon/buildwithclaude, then /plugin install all-commands@buildwithclaude. The command will appear in .claude/commands/design-database-schema.md.
What does design-database-schema do?
It designs database schemas: tables, relationships, indexes, constraints, and data types. Generates PostgreSQL SQL with validation, full-text search, and foreign keys.
Which databases does this command support?
It generates SQL for PostgreSQL: ENUM, tsvector, JSONB, triggers. For MySQL or SQLite, you need to adjust syntax — types and functions differ.
Source text
design-database-schema.md · MIT
---
description: Design optimized database schemas
category: database-operations
---
# Design Database Schema
Design optimized database schemas
## Instructions
1. **Requirements Analysis and Data Modeling**
- Analyze business requirements and data relationships
- Identify entities, attributes, and relationships
- Define data types, constraints, and validation rules
- Plan for scalability and future requirements
- Consider data access patterns and query requirements
2. **Entity Relationship Design**
- Create comprehensive entity relationship diagrams:
**User Management Schema:**
```sql
-- Users table with proper indexing and constraints
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
username VARCHAR(50) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
phone VARCHAR(20),
date_of_birth DATE,
email_verified BOOLEAN DEFAULT FALSE,
phone_verified BOOLEAN DEFAULT FALSE,
status user_status DEFAULT 'active',
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
last_login_at TIMESTAMP WITH TIME ZONE,
deleted_at TIMESTAMP WITH TIME ZONE,
-- Constraints
CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
CONSTRAINT users_username_format CHECK (username ~* '^[a-zA-Z0-9_]{3,50}$'),
CONSTRAINT users_names_not_empty CHECK (LENGTH(TRIM(first_name)) > 0 AND LENGTH(TRIM(last_name)) > 0)
);
-- User status enum
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended', 'pending_verification');
-- User profiles table for extended information
CREATE TABLE user_profiles (
user_id BIGINT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
avatar_url VARCHAR(500),
bio TEXT,
website VARCHAR(255),
location VARCHAR(255),
timezone VARCHAR(50) DEFAULT 'UTC',
language VARCHAR(10) DEFAULT 'en',
notification_preferences JSONB DEFAULT '{}',
privacy_settings JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- User roles and permissions
CREATE TABLE roles (
id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL,
description TEXT,
permissions JSONB DEFAULT '[]',
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE user_roles (
user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
assigned_by BIGINT REFERENCES users(id),
PRIMARY KEY (user_id, role_id)
);
```
**E-commerce Schema Example:**
```sql
-- Categories with hierarchical structure
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
description TEXT,
parent_id INTEGER REFERENCES categories(id),
sort_order INTEGER DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
meta_title VARCHAR(255),
meta_description TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Products table with comprehensive attributes
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
sku VARCHAR(100) UNIQUE NOT NULL,
description TEXT,
short_description TEXT,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
compare_price DECIMAL(10,2) CHECK (compare_price >= price),
cost_price DECIMAL(10,2) CHECK (cost_price >= 0),
weight DECIMAL(8,2),
dimensions JSONB, -- {length: x, width: y, height: z, unit: 'cm'}
category_id INTEGER REFERENCES categories(id),
brand_id INTEGER REFERENCES brands(id),
vendor_id BIGINT REFERENCES vendors(id),
status product_status DEFAULT 'draft',
visibility product_visibility DEFAULT 'visible',
inventory_tracking BOOLEAN DEFAULT TRUE,
inventory_quantity INTEGER DEFAULT 0,
low_stock_threshold INTEGER DEFAULT 5,
allow_backorder BOOLEAN DEFAULT FALSE,
requires_shipping BOOLEAN DEFAULT TRUE,
is_digital BOOLEAN DEFAULT FALSE,
tax_class VARCHAR(50) DEFAULT 'standard',
featured BOOLEAN DEFAULT FALSE,
tags TEXT[],
attributes JSONB DEFAULT '{}',
seo_title VARCHAR(255),
seo_description TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
published_at TIMESTAMP WITH TIME ZONE,
-- Full text search
search_vector tsvector GENERATED ALWAYS AS (
to_tsvector('english', COALESCE(name, '') || ' ' || COALESCE(description, '') || ' ' || COALESCE(sku, ''))
) STORED
);
-- Product status and visibility enums
CREATE TYPE product_status AS ENUM ('draft', 'active', 'inactive', 'archived');
CREATE TYPE product_visibility AS ENUM ('visible', 'hidden', 'catalog_only', 'search_only');
-- Orders table with comprehensive tracking
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
order_number VARCHAR(50) UNIQUE NOT NULL,
user_id BIGINT REFERENCES users(id),
status order_status DEFAULT 'pending',
currency CHAR(3) DEFAULT 'USD',
subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
tax_total DECIMAL(10,2) NOT NULL DEFAULT 0,
shipping_total DECIMAL(10,2) NOT NULL DEFAULT 0,
discount_total DECIMAL(10,2) NOT NULL DEFAULT 0,
total DECIMAL(10,2) NOT NULL DEFAULT 0,
-- Billing information
billing_first_name VARCHAR(100),
billing_last_name VARCHAR(100),
billing_company VARCHAR(255),
billing_address_line_1 VARCHAR(255),
billing_address_line_2 VARCHAR(255),
billing_city VARCHAR(100),
billing_state VARCHAR(100),
billing_postal_code VARCHAR(20),
billing_country CHAR(2),
billing_phone VARCHAR(20),
-- Shipping information
shipping_first_name VARCHAR(100),
shipping_last_name VARCHAR(100),
shipping_company VARCHAR(255),
shipping_address_line_1 VARCHAR(255),
shipping_address_line_2 VARCHAR(255),
shipping_city VARCHAR(100),
shipping_state VARCHAR(100),
shipping_postal_code VARCHAR(20),
shipping_country CHAR(2),
shipping_phone VARCHAR(20),
shipping_method VARCHAR(100),
tracking_number VARCHAR(255),
notes TEXT,
internal_notes TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
shipped_at TIMESTAMP WITH TIME ZONE,
delivered_at TIMESTAMP WITH TIME ZONE
);
CREATE TYPE order_status AS ENUM (
'pending', 'processing', 'shipped', 'delivered',
'cancelled', 'refunded', 'on_hold'
);
-- Order items with detailed tracking
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT REFERENCES products(id),
product_variant_id BIGINT REFERENCES product_variants(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL,
total_price DECIMAL(10,2) NOT NULL,
product_name VARCHAR(255) NOT NULL, -- Snapshot at time of order
product_sku VARCHAR(100), -- Snapshot at time of order
product_attributes JSONB, -- Snapshot of selected variants
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
```
3. **Advanced Schema Patterns**
- Implement complex data patterns:
**Audit Trail Pattern:**
```sql
-- Generic audit trail for tracking all changes
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
table_name VARCHAR(255) NOT NULL,
record_id BIGINT NOT NULL,
operation audit_operation NOT NULL,
old_values JSONB,
new_values JSONB,
changed_fields TEXT[],
user_id BIGINT REFERENCES users(id),
ip_address INET,
user_agent TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- Index for efficient querying
INDEX idx_audit_log_table_record (table_name, record_id),
INDEX idx_audit_log_user_time (user_id, created_at),
INDEX idx_audit_log_operation_time (operation, created_at)
);
CREATE TYPE audit_operation AS ENUM ('INSERT', 'UPDATE', 'DELETE');
-- Trigger function for automatic audit logging
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
old_data JSONB;
new_data JSONB;
changed_fields TEXT[];
BEGIN
IF TG_OP = 'DELETE' THEN
old_data = to_jsonb(OLD);
INSERT INTO audit_log (table_name, record_id, operation, old_values, user_id)
VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', old_data, COALESCE(current_setting('app.current_user_id', true)::BIGINT, NULL));
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
old_data = to_jsonb(OLD);
new_data = to_jsonb(NEW);
-- Find changed fields
SELECT array_agg(key) INTO changed_fields
FROM jsonb_each(old_data)
WHERE key IN (SELECT key FROM jsonb_each(new_data))
AND value IS DISTINCT FROM (new_data->key);
INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_fields, user_id)
VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', old_data, new_data, changed_fields, COALESCE(current_setting('app.current_user_id', true)::BIGINT, NULL));
RETURN NEW;
ELSIF TG_OP = 'INSERT' THEN
new_data = to_jsonb(NEW);
INSERT INTO audit_log (table_name, record_id, operation, new_values, user_id)
VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', new_data, COALESCE(current_setting('app.current_user_id', true)::BIGINT, NULL));
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
```
**Soft Delete Pattern:**
```sql
-- Add soft delete to any table
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMP WITH TIME ZONE;
-- Create views that exclude soft-deleted records
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;
CREATE VIEW active_products AS
SELECT * FROM products WHERE deleted_at IS NULL;
-- Soft delete function
CREATE OR REPLACE FUNCTION soft_delete(table_name TEXT, record_id BIGINT)
RETURNS VOID AS $$
BEGIN
EXECUTE format('UPDATE %I SET deleted_at = CURRENT_TIMESTAMP WHERE id = $1 AND deleted_at IS NULL', table_name)
USING record_id;
END;
$$ LANGUAGE plpgsql;
-- Restore function
CREATE OR REPLACE FUNCTION restore_deleted(table_name TEXT, record_id BIGINT)
RETURNS VOID AS $$
BEGIN
EXECUTE format('UPDATE %I SET deleted_at = NULL WHERE id = $1', table_name)
USING record_id;
END;
$$ LANGUAGE plpgsql;
```
4. **Performance Optimization Schema Design**
- Design for optimal query performance:
**Strategic Indexing:**
```sql
-- Single column indexes for frequently queried fields
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_users_username ON users(username);
CREATE INDEX CONCURRENTLY idx_users_status ON users(status) WHERE status != 'active';
CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);
-- Composite indexes for common query patterns
CREATE INDEX CONCURRENTLY idx_products_category_status
ON products(category_id, status) WHERE status = 'active';
CREATE INDEX CONCURRENTLY idx_products_featured_category
ON products(featured, category_id) WHERE featured = true AND status = 'active';
CREATE INDEX CONCURRENTLY idx_orders_user_status_date
ON orders(user_id, status, created_at);
-- Partial indexes for specific conditions
CREATE INDEX CONCURRENTLY idx_products_low_stock
ON products(inventory_quantity)
WHERE inventory_tracking = true AND inventory_quantity <= low_stock_threshold;
-- Functional indexes for text search and computed values
CREATE INDEX CONCURRENTLY idx_products_search_vector
ON products USING gin(search_vector);
CREATE INDEX CONCURRENTLY idx_users_full_name_lower
ON users(lower(first_name || ' ' || last_name));
-- JSON/JSONB indexes for flexible data
CREATE INDEX CONCURRENTLY idx_user_profiles_notifications
ON user_profiles USING gin(notification_preferences);
CREATE INDEX CONCURRENTLY idx_products_attributes
ON products USING gin(attributes);
```
**Partitioning Strategy:**
```sql
-- Partition large tables by date for better performance
CREATE TABLE orders_partitioned (
LIKE orders INCLUDING ALL
) PARTITION BY RANGE (created_at);
-- Create monthly partitions
CREATE TABLE orders_2024_01 PARTITION OF orders_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders_partitioned
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- Automatic partition management
CREATE OR REPLACE FUNCTION create_monthly_partitions(
table_name TEXT,
start_date DATE,
end_date DATE
)
RETURNS VOID AS $$
DECLARE
current_date DATE := start_date;
partition_name TEXT;
next_date DATE;
BEGIN
WHILE current_date < end_date LOOP
next_date := current_date + INTERVAL '1 month';
partition_name := table_name || '_' || to_char(current_date, 'YYYY_MM');
EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
partition_name, table_name, current_date, next_date);
current_date := next_date;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- Schedule partition creation
SELECT create_monthly_partitions('orders_partitioned', '2024-01-01'::DATE, '2025-01-01'::DATE);
```
5. **Data Integrity and Constraints**
- Implement comprehensive data validation:
**Advanced Constraints:**
```sql
-- Complex check constraints
ALTER TABLE products ADD CONSTRAINT products_price_logic
CHECK (
CASE
WHEN compare_price IS NOT NULL THEN price <= compare_price
ELSE true
END
);
ALTER TABLE products ADD CONSTRAINT products_inventory_logic
CHECK (
CASE
WHEN inventory_tracking = false THEN inventory_quantity IS NULL
WHEN inventory_tracking = true THEN inventory_quantity >= 0
ELSE true
END
);
-- Custom domain types for reusable validation
CREATE DOMAIN email_address AS VARCHAR(255)
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
CREATE DOMAIN phone_number AS VARCHAR(20)
CHECK (VALUE ~* '^\+?[\d\s\-\(\)]{10,20}$');
CREATE DOMAIN positive_decimal AS DECIMAL(10,2)
CHECK (VALUE >= 0);
-- Use domains in table definitions
CREATE TABLE contacts (
id BIGSERIAL PRIMARY KEY,
email email_address NOT NULL,
phone phone_number,
balance positive_decimal DEFAULT 0
);
-- Foreign key constraints with cascading options
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;
-- Unique constraints for business logic
ALTER TABLE user_roles
ADD CONSTRAINT unique_user_role_active
UNIQUE (user_id, role_id);
-- Exclusion constraints for complex business rules
ALTER TABLE product_promotions
ADD CONSTRAINT no_overlapping_promotions
EXCLUDE USING gist (
product_id WITH =,
daterange(start_date, end_date, '[]') WITH &&
);
```
6. **Temporal Data and Versioning**
- Handle time-based data requirements:
**Temporal Tables:**
```sql
-- Product price history tracking
CREATE TABLE product_price_history (
id BIGSERIAL PRIMARY KEY,
product_id BIGINT REFERENCES products(id) ON DELETE CASCADE,
price DECIMAL(10,2) NOT NULL,
compare_price DECIMAL(10,2),
effective_from TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
effective_to TIMESTAMP WITH TIME ZONE,
created_by BIGINT REFERENCES users(id),
reason TEXT,
-- Ensure no overlapping periods
EXCLUDE USING gist (
product_id WITH =,
tstzrange(effective_from, effective_to, '[)') WITH &&
)
);
-- Function to get current price
CREATE OR REPLACE FUNCTION get_current_price(p_product_id BIGINT)
RETURNS DECIMAL(10,2) AS $$
DECLARE
current_price DECIMAL(10,2);
BEGIN
SELECT price INTO current_price
FROM product_price_history
WHERE product_id = p_product_id
AND effective_from <= CURRENT_TIMESTAMP
AND (effective_to IS NULL OR effective_to > CURRENT_TIMESTAMP)
ORDER BY effective_from DESC
LIMIT 1;
RETURN current_price;
END;
$$ LANGUAGE plpgsql;
-- Trigger to update price history when product price changes
CREATE OR REPLACE FUNCTION update_price_history()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.price IS DISTINCT FROM NEW.price THEN
-- Close current price period
UPDATE product_price_history
SET effective_to = CURRENT_TIMESTAMP
WHERE product_id = NEW.id AND effective_to IS NULL;
-- Insert new price period
INSERT INTO product_price_history (product_id, price, compare_price, created_by)
VALUES (NEW.id, NEW.price, NEW.compare_price,
COALESCE(current_setting('app.current_user_id', true)::BIGINT, NULL));
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_product_price_history
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_price_history();
```
7. **JSON/NoSQL Integration**
- Leverage JSON columns for flexible data:
**JSONB Schema Design:**
```sql
-- Flexible product attributes using JSONB
CREATE TABLE product_attributes (
product_id BIGINT REFERENCES products(id) ON DELETE CASCADE,
attributes JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (product_id)
);
-- JSONB indexes for efficient querying
CREATE INDEX idx_product_attributes_gin ON product_attributes USING gin(attributes);
CREATE INDEX idx_product_attributes_color ON product_attributes USING gin((attributes->'color'));
CREATE INDEX idx_product_attributes_size ON product_attributes USING gin((attributes->'size'));
-- Function to query products by attributes
CREATE OR REPLACE FUNCTION find_products_by_attributes(search_attributes JSONB)
RETURNS TABLE(product_id BIGINT, product_name VARCHAR, attributes JSONB) AS $$
BEGIN
RETURN QUERY
SELECT p.id, p.name, pa.attributes
FROM products p
JOIN product_attributes pa ON p.id = pa.product_id
WHERE pa.attributes @> search_attributes;
END;
$$ LANGUAGE plpgsql;
-- Usage examples:
-- SELECT * FROM find_products_by_attributes('{"color": "red", "size": "large"}');
-- Settings table with JSONB for flexible configuration
CREATE TABLE application_settings (
id SERIAL PRIMARY KEY,
category VARCHAR(100) NOT NULL,
key VARCHAR(100) NOT NULL,
value JSONB NOT NULL,
description TEXT,
is_public BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
UNIQUE(category, key)
);
-- Function to get setting value with type casting
CREATE OR REPLACE FUNCTION get_setting(p_category VARCHAR, p_key VARCHAR, p_default ANYELEMENT DEFAULT NULL)
RETURNS ANYELEMENT AS $$
DECLARE
setting_value JSONB;
BEGIN
SELECT value INTO setting_value
FROM application_settings
WHERE category = p_category AND key = p_key;
IF setting_value IS NULL THEN
RETURN p_default;
END IF;
RETURN (setting_value #>> '{}')::TEXT::pg_typeof(p_default);
END;
$$ LANGUAGE plpgsql;
```
8. **Database Security Schema**
- Implement security at the schema level:
**Row Level Security:**
```sql
-- Enable RLS on sensitive tables
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
-- Create policies for data access
CREATE POLICY orders_user_access ON orders
FOR ALL TO authenticated_users
USING (user_id = current_user_id());
CREATE POLICY orders_admin_access ON orders
FOR ALL TO admin_users
USING (true);
-- Function to get current user ID from session
CREATE OR REPLACE FUNCTION current_user_id()
RETURNS BIGINT AS $$
BEGIN
RETURN COALESCE(current_setting('app.current_user_id', true)::BIGINT, 0);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Create database roles with specific permissions
CREATE ROLE app_readonly;
GRANT CONNECT ON DATABASE myapp TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
CREATE ROLE app_readwrite;
GRANT app_readonly TO app_readwrite;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;
-- Sensitive data encryption
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Function to encrypt sensitive data
CREATE OR REPLACE FUNCTION encrypt_sensitive_data(data TEXT)
RETURNS TEXT AS $$
BEGIN
RETURN encode(encrypt(data::bytea, current_setting('app.encryption_key'), 'aes'), 'base64');
END;
$$ LANGUAGE plpgsql;
-- Function to decrypt sensitive data
CREATE OR REPLACE FUNCTION decrypt_sensitive_data(encrypted_data TEXT)
RETURNS TEXT AS $$
BEGIN
RETURN convert_from(decrypt(decode(encrypted_data, 'base64'), current_setting('app.encryption_key'), 'aes'), 'UTF8');
END;
$$ LANGUAGE plpgsql;
```
9. **Schema Documentation and Maintenance**
- Document and maintain schema design:
**Database Documentation:**
```sql
-- Add comments to tables and columns
COMMENT ON TABLE users IS 'User accounts and authentication information';
COMMENT ON COLUMN users.email IS 'Unique email address for user authentication';
COMMENT ON COLUMN users.status IS 'Current status of user account (active, inactive, suspended, pending_verification)';
COMMENT ON COLUMN users.email_verified IS 'Whether the user has verified their email address';
COMMENT ON TABLE products IS 'Product catalog with inventory and pricing information';
COMMENT ON COLUMN products.search_vector IS 'Full-text search vector generated from name, description, and SKU';
COMMENT ON COLUMN products.attributes IS 'Flexible product attributes stored as JSONB (color, size, material, etc.)';
-- Create a view for schema documentation
CREATE VIEW schema_documentation AS
SELECT
t.table_name,
t.table_type,
obj_description(c.oid) AS table_comment,
col.column_name,
col.data_type,
col.is_nullable,
col.column_default,
col_description(c.oid, col.ordinal_position) AS column_comment
FROM information_schema.tables t
JOIN pg_class c ON c.relname = t.table_name
JOIN information_schema.columns col ON col.table_name = t.table_name
WHERE t.table_schema = 'public'
ORDER BY t.table_name, col.ordinal_position;
```
10. **Schema Testing and Validation**
- Implement schema testing procedures:
**Schema Validation Tests:**
```sql
-- Test data integrity constraints
DO $$
DECLARE
test_result BOOLEAN;
BEGIN
-- Test email validation
BEGIN
INSERT INTO users (email, username, password_hash, first_name, last_name)
VALUES ('invalid-email', 'testuser', 'hash', 'Test', 'User');
RAISE EXCEPTION 'Email validation failed - invalid email accepted';
EXCEPTION
WHEN check_violation THEN
RAISE NOTICE 'Email validation working correctly';
END;
-- Test price constraints
BEGIN
INSERT INTO products (name, slug, sku, price, compare_price)
VALUES ('Test Product', 'test-product', 'TEST-001', 100.00, 50.00);
RAISE EXCEPTION 'Price validation failed - compare_price less than price accepted';
EXCEPTION
WHEN check_violation THEN
RAISE NOTICE 'Price validation working correctly';
END;
-- Test foreign key constraints
BEGIN
INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price, product_name)
VALUES (999999, 999999, 1, 10.00, 10.00, 'Test Product');
RAISE EXCEPTION 'Foreign key validation failed - non-existent order_id accepted';
EXCEPTION
WHEN foreign_key_violation THEN
RAISE NOTICE 'Foreign key validation working correctly';
END;
END;
$$;
-- Performance test queries
CREATE OR REPLACE FUNCTION test_query_performance()
RETURNS TABLE(test_name TEXT, execution_time INTERVAL) AS $$
DECLARE
start_time TIMESTAMP;
end_time TIMESTAMP;
BEGIN
-- Test user lookup by email
start_time := clock_timestamp();
PERFORM * FROM users WHERE email = '[email protected]';
end_time := clock_timestamp();
test_name := 'User lookup by email';
execution_time := end_time - start_time;
RETURN NEXT;
-- Test product search
start_time := clock_timestamp();
PERFORM * FROM products WHERE search_vector @@ to_tsquery('english', 'laptop');
end_time := clock_timestamp();
test_name := 'Product full-text search';
execution_time := end_time - start_time;
RETURN NEXT;
-- Test order history query
start_time := clock_timestamp();
PERFORM o.* FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 1
ORDER BY o.created_at DESC
LIMIT 20;
end_time := clock_timestamp();
test_name := 'User order history';
execution_time := end_time - start_time;
RETURN NEXT;
END;
$$ LANGUAGE plpgsql;
-- Run performance tests
SELECT * FROM test_query_performance();
```
© BuildWithClaude Community · MIT · davepoon/buildwithclaude
Similar slash commands
All slash commands for Data and databases →design-database-schema by other authors comes first.