Design Database Schema

Design optimized database schemas

Instructions

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

Entity Relationship Design

Create comprehensive entity relationship diagrams:

User Management Schema:

-- 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:

-- 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
);

Advanced Schema Patterns

Implement complex data patterns:

Audit Trail Pattern:

-- 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:

-- 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;

Performance Optimization Schema Design

Design for optimal query performance:

Strategic Indexing:

-- 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:

-- 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);

Data Integrity and Constraints

Implement comprehensive data validation:

Advanced Constraints:

-- 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 &&
);

Temporal Data and Versioning

Handle time-based data requirements:

Temporal Tables:

-- 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();

JSON/NoSQL Integration

Leverage JSON columns for flexible data:

JSONB Schema Design:

-- 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;

Database Security Schema

Implement security at the schema level:

Row Level Security:

-- 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;

Schema Documentation and Maintenance

Document and maintain schema design:

Database Documentation:

-- 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;

Schema Testing and Validation

Implement schema testing procedures:

Schema Validation Tests:

-- 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 = 'test@example.com';
  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();

/design-database-schema

Design Database Schema

Instructions

Other plugins with /design-database-schema