Skip to main content
command CX Codex

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

Install into Claude Code · Plugin marketplace All methods and platforms ↓
/plugin marketplace add davepoon/buildwithclaude
/plugin install all-commands@buildwithclaude
Open on GitHub

What 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@buildwithclaude

2Manually: put the files in place

.claude/commands/design-database-schema.md

Call it with /design-database-schema. For all projects use ~/.claude/commands/

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.

design-database-schema davila7 The design-database-schema command plans database structures around requirements, relationships, and query patterns. Use it to draft a schema or rethink an existing one. CX Codex +1 1,021installs database-schema-designer borghei database-schema-designer — a Claude Code skill that turns product requirements into a normalized relational schema with tables, ORM models, RLS, indexes and migrations. Works across six platforms, including Codex, Cursor and Gemini CLI. CX Codex +1 183installs schema-consistency-checker patricio0312rev schema-consistency-checker reads your tables and columns, flags names that break the agreed style, mismatched ID types, nullable critical fields and foreign keys without an index, then hands back a fix list. CX Codex +1 265installs database-schema-design aj-geddes database-schema-design designs relational database schemas with normalization, constraints, and table relationships. Built for modeling data in PostgreSQL and MySQL. CX Codex +1 599installs database-schema-documentation aj-geddes database-schema-documentation documents database schemas, generates Mermaid ERD diagrams, and tracks table constraints. Useful when creating data dictionaries or writing migration specs. CX Codex +1 579installs database-design MoizIbnYousaf database-design — a skill for schema design, migrations and query optimization on PostgreSQL, MySQL and NoSQL. Useful whenever the agent writes SQL. CX Codex +1 624installs database-design-patterns curiositech database-design-patterns guides relational schema design, 3NF normalization, index selection, and zero-downtime migrations using the expand-contract pattern. CX Codex +1 152installs

From the same set235

The whole set →
meeting meeting runs a multi-persona AI debate on any topic and writes the complete transcript to a Markdown file. CX Codex +1 ★ 3,785repo stars soft-predict-future The soft-predict-future skill uses web research to structure questions about future outcomes. Use it for forecasts, scenario analysis, or examining where a technology or market may be heading. CX Codex +1 ★ 3,785repo stars team-workload-balancer team-workload-balancer — a slash command that reads each teammate's open tasks and commit history, scores how loaded everyone is, and suggests who should take the next task. CX Codex +1 ★ 3,785repo stars public-plugin-builder public-plugin-builder — a 23-step interview guide that helps you design, structure, and deploy Claude plugins or skills. CX Codex +1 ★ 3,785repo stars ops-inbox ops-inbox scans communication channels to detect conversations requiring answers and clean up your inbox. CX Codex +1 ★ 3,785repo stars gsd-executor The gsd-executor subagent carries out GSD plans task by task, commits each task separately, and updates project state. The execute-phase orchestrator starts it. CX Codex ★ 3,785repo stars gsd-code-fixer gsd-code-fixer reads findings from REVIEW.md, fixes code, and commits each fix atomically. Spawned by /gsd:code-review --fix. CX Codex ★ 3,785repo stars gsd-verifier gsd-verifier verifies phase goals against the codebase instead of trusting task summaries. Detects stub files and outputs a VERIFICATION.md report. CX Codex ★ 3,785repo stars

Project sponsors

Companies that keep this analytics open