# CockroachDB E-commerce Database Architecture Rule ## Overview This rule provides a comprehensive guide for designing and implementing a scalable e-commerce database architecture using CockroachDB. It covers schema design, table relationships, indexing strategies, and performance optimizations specific to e-commerce workloads including high-volume transactions, inventory management, and global distribution. ## Implementation ### 1. Core E-commerce Schema Design #### Users and Authentication ```sql -- Users table with global distribution CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email VARCHAR(255) UNIQUE NOT NULL, username VARCHAR(100) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, first_name VARCHAR(100), last_name VARCHAR(100), phone VARCHAR(20), status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended')), created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), region VARCHAR(10) NOT NULL -- For geo-partitioning ); -- Geo-partition users by region ALTER TABLE users PARTITION BY LIST (region) ( PARTITION us_east VALUES IN ('us-east'), PARTITION us_west VALUES IN ('us-west'), PARTITION eu_west VALUES IN ('eu-west'), PARTITION asia_pacific VALUES IN ('asia-pac') ); -- User addresses with foreign key relationship CREATE TABLE user_addresses ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, address_type VARCHAR(20) DEFAULT 'shipping' CHECK (address_type IN ('shipping', 'billing')), street_address VARCHAR(255) NOT NULL, city VARCHAR(100) NOT NULL, state VARCHAR(100), postal_code VARCHAR(20), country VARCHAR(100) NOT NULL, is_default BOOLEAN DEFAULT false, created_at TIMESTAMPTZ DEFAULT now() ); ``` #### Product Catalog ```sql -- Categories with hierarchical structure CREATE TABLE categories ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), parent_id UUID REFERENCES categories(id), name VARCHAR(255) NOT NULL, slug VARCHAR(255) UNIQUE NOT NULL, description TEXT, image_url VARCHAR(500), sort_order INT DEFAULT 0, is_active BOOLEAN DEFAULT true, created_at TIMESTAMPTZ DEFAULT now() ); -- Products with optimized indexing CREATE TABLE products ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), sku VARCHAR(100) UNIQUE NOT NULL, name VARCHAR(255) NOT NULL, slug VARCHAR(255) 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 >= 0), cost_price DECIMAL(10,2) CHECK (cost_price >= 0), weight DECIMAL(8,2) DEFAULT 0, dimensions JSONB, -- {"length": 10, "width": 5, "height": 3} category_id UUID REFERENCES categories(id), brand VARCHAR(100), status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'discontinued')), is_digital BOOLEAN DEFAULT false, requires_shipping BOOLEAN DEFAULT true, tags VARCHAR(255)[], metadata JSONB, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now() ); -- Product variants for size, color, etc. CREATE TABLE product_variants ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE, sku VARCHAR(100) UNIQUE NOT NULL, name VARCHAR(255) NOT NULL, price DECIMAL(10,2) CHECK (price >= 0), compare_price DECIMAL(10,2) CHECK (compare_price >= 0), attributes JSONB NOT NULL, -- {"size": "M", "color": "blue"} image_url VARCHAR(500), sort_order INT DEFAULT 0, is_active BOOLEAN DEFAULT true, created_at TIMESTAMPTZ DEFAULT now() ); ``` #### Inventory Management ```sql -- Inventory tracking with warehouse support CREATE TABLE inventory ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), product_id UUID REFERENCES products(id) ON DELETE CASCADE, variant_id UUID REFERENCES product_variants(id) ON DELETE CASCADE, warehouse_id UUID NOT NULL, quantity_available INT NOT NULL DEFAULT 0 CHECK (quantity_available >= 0), quantity_reserved INT NOT NULL DEFAULT 0 CHECK (quantity_reserved >= 0), quantity_incoming INT NOT NULL DEFAULT 0 CHECK (quantity_incoming >= 0), reorder_point INT DEFAULT 10, reorder_quantity INT DEFAULT 50, last_updated TIMESTAMPTZ DEFAULT now(), UNIQUE(product_id, variant_id, warehouse_id) ); -- Inventory movements for audit trail CREATE TABLE inventory_movements ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), inventory_id UUID NOT NULL REFERENCES inventory(id), movement_type VARCHAR(20) NOT NULL CHECK (movement_type IN ('in', 'out', 'adjustment', 'reserved', 'released')), quantity INT NOT NULL, reference_type VARCHAR(50), -- 'order', 'purchase', 'adjustment' reference_id UUID, reason TEXT, created_at TIMESTAMPTZ DEFAULT now(), created_by UUID REFERENCES users(id) ); ``` #### Orders and Transactions ```sql -- Orders table with distributed design CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_number VARCHAR(50) UNIQUE NOT NULL, user_id UUID NOT NULL REFERENCES users(id), status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded')), currency VARCHAR(3) DEFAULT 'USD', subtotal DECIMAL(10,2) NOT NULL CHECK (subtotal >= 0), tax_amount DECIMAL(10,2) DEFAULT 0 CHECK (tax_amount >= 0), shipping_amount DECIMAL(10,2) DEFAULT 0 CHECK (shipping_amount >= 0), discount_amount DECIMAL(10,2) DEFAULT 0 CHECK (discount_amount >= 0), total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0), shipping_address JSONB NOT NULL, billing_address JSONB NOT NULL, payment_status VARCHAR(20) DEFAULT 'pending' CHECK (payment_status IN ('pending', 'paid', 'failed', 'refunded', 'partial')), fulfillment_status VARCHAR(20) DEFAULT 'unfulfilled' CHECK (fulfillment_status IN ('unfulfilled', 'partial', 'fulfilled')), notes TEXT, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), region VARCHAR(10) NOT NULL -- For geo-partitioning ); -- Partition orders by region and date ALTER TABLE orders PARTITION BY LIST (region) ( PARTITION us_east VALUES IN ('us-east'), PARTITION us_west VALUES IN ('us-west'), PARTITION eu_west VALUES IN ('eu-west'), PARTITION asia_pacific VALUES IN ('asia-pac') ); -- Order items with product relationships CREATE TABLE order_items ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id UUID NOT NULL REFERENCES products(id), variant_id UUID REFERENCES product_variants(id), quantity INT NOT NULL CHECK (quantity > 0), unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0), total_price DECIMAL(10,2) NOT NULL CHECK (total_price >= 0), product_snapshot JSONB NOT NULL -- Store product details at time of order ); ``` #### Shopping Cart ```sql -- Shopping cart with session support CREATE TABLE shopping_carts ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID REFERENCES users(id) ON DELETE CASCADE, session_id VARCHAR(255), -- For guest users created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), expires_at TIMESTAMPTZ DEFAULT (now() + INTERVAL '30 days'), CONSTRAINT cart_user_or_session CHECK (user_id IS NOT NULL OR session_id IS NOT NULL) ); CREATE TABLE cart_items ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), cart_id UUID NOT NULL REFERENCES shopping_carts(id) ON DELETE CASCADE, product_id UUID NOT NULL REFERENCES products(id), variant_id UUID REFERENCES product_variants(id), quantity INT NOT NULL CHECK (quantity > 0), added_at TIMESTAMPTZ DEFAULT now(), UNIQUE(cart_id, product_id, variant_id) ); ``` ### 2. Performance Optimizations #### Strategic Indexing ```sql -- Product search and filtering indexes CREATE INDEX idx_products_category_status ON products(category_id, status) WHERE status = 'active'; CREATE INDEX idx_products_price_range ON products(price) WHERE status = 'active'; CREATE INDEX idx_products_brand_status ON products(brand, status) WHERE status = 'active'; CREATE INDEX idx_products_tags ON products USING GIN(tags) WHERE status = 'active'; -- Full-text search index CREATE INDEX idx_products_search ON products USING GIN(to_tsvector('english', name || ' ' || COALESCE(description, ''))); -- Order performance indexes CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC); CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC); CREATE INDEX idx_orders_region_created ON orders(region, created_at DESC); -- Inventory tracking indexes CREATE INDEX idx_inventory_product_warehouse ON inventory(product_id, warehouse_id); CREATE INDEX idx_inventory_low_stock ON inventory(quantity_available) WHERE quantity_available <= reorder_point; ``` #### Materialized Views for Analytics ```sql -- Daily sales summary CREATE MATERIALIZED VIEW daily_sales_summary AS SELECT DATE(created_at) as sale_date, region, COUNT(*) as order_count, SUM(total_amount) as total_revenue, AVG(total_amount) as avg_order_value, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_orders FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '90 days' GROUP BY DATE(created_at), region; -- Product performance view CREATE MATERIALIZED VIEW product_performance AS SELECT p.id, p.name, p.category_id, COUNT(oi.id) as times_ordered, SUM(oi.quantity) as total_quantity_sold, SUM(oi.total_price) as total_revenue, AVG(oi.unit_price) as avg_selling_price FROM products p LEFT JOIN order_items oi ON p.id = oi.product_id LEFT JOIN orders o ON oi.order_id = o.id WHERE o.status NOT IN ('cancelled', 'refunded') GROUP BY p.id, p.name, p.category_id; ``` ### 3. Advanced E-commerce Features #### Coupons and Discounts ```sql CREATE TABLE coupons ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), code VARCHAR(50) UNIQUE NOT NULL, type VARCHAR(20) NOT NULL CHECK (type IN ('percentage', 'fixed', 'free_shipping')), value DECIMAL(10,2) NOT NULL CHECK (value >= 0), minimum_amount DECIMAL(10,2) DEFAULT 0, maximum_discount DECIMAL(10,2), usage_limit INT, usage_count INT DEFAULT 0, valid_from TIMESTAMPTZ NOT NULL, valid_until TIMESTAMPTZ NOT NULL, is_active BOOLEAN DEFAULT true, created_at TIMESTAMPTZ DEFAULT now() ); CREATE TABLE coupon_usages ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), coupon_id UUID NOT NULL REFERENCES coupons(id), order_id UUID NOT NULL REFERENCES orders(id), user_id UUID NOT NULL REFERENCES users(id), discount_amount DECIMAL(10,2) NOT NULL, used_at TIMESTAMPTZ DEFAULT now(), UNIQUE(coupon_id, order_id) ); ``` #### Reviews and Ratings ```sql CREATE TABLE product_reviews ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE, user_id UUID NOT NULL REFERENCES users(id), order_id UUID REFERENCES orders(id), rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5), title VARCHAR(255), review_text TEXT, is_verified BOOLEAN DEFAULT false, is_approved BOOLEAN DEFAULT false, helpful_count INT DEFAULT 0, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), UNIQUE(product_id, user_id, order_id) ); -- Aggregate ratings view CREATE MATERIALIZED VIEW product_ratings AS SELECT product_id, COUNT(*) as review_count, AVG(rating) as avg_rating, COUNT(CASE WHEN rating = 5 THEN 1 END) as five_star_count, COUNT(CASE WHEN rating = 4 THEN 1 END) as four_star_count, COUNT(CASE WHEN rating = 3 THEN 1 END) as three_star_count, COUNT(CASE WHEN rating = 2 THEN 1 END) as two_star_count, COUNT(CASE WHEN rating = 1 THEN 1 END) as one_star_count FROM product_reviews WHERE is_approved = true GROUP BY product_id; ``` ## Best Practices ### 1. Data Consistency and Transactions - Use `BEGIN; ... COMMIT;` transactions for multi-table operations (orders + order_items + inventory updates) - Implement optimistic locking for inventory updates to prevent overselling - Use CHECK constraints to ensure data integrity (positive quantities, valid status values) - Leverage foreign key constraints for referential integrity ### 2. Scaling Strategies - Partition large tables (orders, inventory_movements) by region or date - Use UUIDs for primary keys to avoid hotspots in distributed environments - Implement read replicas for analytics and reporting queries - Cache frequently accessed data (product catalogs, categories) at application level ### 3. Performance Optimization - Create composite indexes for common query patterns - Use materialized views for complex aggregations and reporting - Implement proper pagination for large result sets - Monitor query performance and optimize slow queries ### 4. Security Considerations - Never store plain text passwords - use bcrypt or similar hashing - Implement proper access controls at application level - Use prepared statements to prevent SQL injection - Audit sensitive operations (order changes, inventory adjustments) ## Common Issues ### 1. Inventory Overselling **Problem**: Multiple users ordering the same item simultaneously can cause negative inventory. **Solution**: ```sql -- Use SELECT FOR UPDATE to prevent race conditions BEGIN; SELECT quantity_available FROM inventory WHERE product_id = $1 AND warehouse_id = $2 FOR UPDATE; -- Check availability before updating UPDATE inventory SET quantity_available = quantity_available - $3, quantity_reserved = quantity_reserved + $3 WHERE product_id = $1 AND warehouse_id = $2 AND quantity_available >= $3; COMMIT; ``` ### 2. Cart Abandonment and Cleanup **Problem**: Old cart data accumulating in database. **Solution**: ```sql -- Clean up expired carts DELETE FROM shopping_carts WHERE expires_at < now() - INTERVAL '7 days'; -- Reset expired reservations UPDATE inventory SET quantity_reserved = quantity_reserved - ( SELECT COALESCE(SUM(ci.quantity), 0) FROM cart_items ci JOIN shopping_carts sc ON ci.cart_id = sc.id WHERE sc.expires_at < now() ); ``` ### 3. Price Consistency **Problem**: Product prices changing after items are added to cart. **Solution**: ```sql -- Store price snapshot in cart items ALTER TABLE cart_items ADD COLUMN unit_price DECIMAL(10,2); ALTER TABLE cart_items ADD COLUMN price_updated_at TIMESTAMPTZ; -- Update cart prices periodically UPDATE cart_items SET unit_price = p.price, price_updated_at = now() FROM products p WHERE cart_items.product_id = p.id AND cart_items.price_updated_at < now() - INTERVAL '1 hour'; ``` ### 4. Order Number Generation **Problem**: Ensuring unique, sequential order numbers across distributed nodes. **Solution**: ```sql -- Use sequence with proper caching CREATE SEQUENCE order_number_seq CACHE 100; -- Generate order numbers with prefix CREATE OR REPLACE FUNCTION generate_order_number() RETURNS VARCHAR(50) AS $$ BEGIN RETURN 'ORD-' || TO_CHAR(now(), 'YYYYMMDD') || '-' || LPAD(nextval('order_number_seq')::TEXT, 6, '0'); END; $$ LANGUAGE plpgsql; ```