PostgreSQL MCP Server

-- E-commerce Database Schema Example -- This schema represents a typical e-commerce platform with users, products, orders, and analytics -- Users table CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, username VARCHAR(100) UNIQUE NOT NULL, first_name VARCHAR(100), last_name VARCHAR(100), phone VARCHAR(20), date_of_birth DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, is_active BOOLEAN DEFAULT true, email_verified BOOLEAN DEFAULT false, last_login TIMESTAMP ); -- Categories table CREATE TABLE categories ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, slug VARCHAR(100) UNIQUE NOT NULL, description TEXT, parent_id INTEGER REFERENCES categories(id), is_active BOOLEAN DEFAULT true, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Products table CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, slug VARCHAR(255) UNIQUE NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL, cost_price DECIMAL(10,2), sku VARCHAR(100) UNIQUE, stock_quantity INTEGER DEFAULT 0, category_id INTEGER REFERENCES categories(id), brand VARCHAR(100), weight DECIMAL(8,2), dimensions JSONB, -- {width, height, depth} images JSONB, -- Array of image URLs is_active BOOLEAN DEFAULT true, featured BOOLEAN DEFAULT false, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Customer addresses CREATE TABLE addresses ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id) ON DELETE CASCADE, type VARCHAR(20) CHECK (type IN ('billing', 'shipping')), first_name VARCHAR(100), last_name VARCHAR(100), company VARCHAR(255), address_line_1 VARCHAR(255) NOT NULL, address_line_2 VARCHAR(255), city VARCHAR(100) NOT NULL, state VARCHAR(100), postal_code VARCHAR(20), country VARCHAR(100) NOT NULL, is_default BOOLEAN DEFAULT false, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Orders table CREATE TABLE orders ( id SERIAL PRIMARY KEY, order_number VARCHAR(50) UNIQUE NOT NULL, user_id INTEGER REFERENCES users(id), status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded')), subtotal DECIMAL(10,2) NOT NULL, tax_amount DECIMAL(10,2) DEFAULT 0, shipping_amount DECIMAL(10,2) DEFAULT 0, discount_amount DECIMAL(10,2) DEFAULT 0, total_amount DECIMAL(10,2) NOT NULL, currency VARCHAR(3) DEFAULT 'USD', payment_status VARCHAR(20) DEFAULT 'pending' CHECK (payment_status IN ('pending', 'paid', 'failed', 'refunded')), payment_method VARCHAR(50), shipping_address_id INTEGER REFERENCES addresses(id), billing_address_id INTEGER REFERENCES addresses(id), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, shipped_at TIMESTAMP, delivered_at TIMESTAMP ); -- Order items CREATE TABLE order_items ( id SERIAL PRIMARY KEY, order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE, product_id INTEGER REFERENCES products(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), -- Snapshot of product name at time of order product_sku VARCHAR(100) -- Snapshot of SKU at time of order ); -- Shopping cart CREATE TABLE cart_items ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id) ON DELETE CASCADE, product_id INTEGER REFERENCES products(id) ON DELETE CASCADE, quantity INTEGER NOT NULL CHECK (quantity > 0), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(user_id, product_id) ); -- Product reviews CREATE TABLE reviews ( id SERIAL PRIMARY KEY, product_id INTEGER REFERENCES products(id) ON DELETE CASCADE, user_id INTEGER REFERENCES users(id) ON DELETE CASCADE, order_id INTEGER REFERENCES orders(id), -- Optional: link to verified purchase rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5), title VARCHAR(255), comment TEXT, is_verified_purchase BOOLEAN DEFAULT false, is_approved BOOLEAN DEFAULT false, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(product_id, user_id) -- One review per user per product ); -- Coupons/Discounts CREATE TABLE coupons ( id SERIAL PRIMARY KEY, code VARCHAR(50) UNIQUE NOT NULL, name VARCHAR(255) NOT NULL, description TEXT, type VARCHAR(20) CHECK (type IN ('percentage', 'fixed_amount', 'free_shipping')), value DECIMAL(10,2) NOT NULL, minimum_order_amount DECIMAL(10,2), maximum_discount_amount DECIMAL(10,2), usage_limit INTEGER, used_count INTEGER DEFAULT 0, is_active BOOLEAN DEFAULT true, starts_at TIMESTAMP, expires_at TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Order coupons (track which coupons were used in orders) CREATE TABLE order_coupons ( id SERIAL PRIMARY KEY, order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE, coupon_id INTEGER REFERENCES coupons(id), coupon_code VARCHAR(50) NOT NULL, discount_amount DECIMAL(10,2) NOT NULL ); -- Indexes for better performance CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_active ON users(is_active); CREATE INDEX idx_users_created_at ON users(created_at); CREATE INDEX idx_products_category ON products(category_id); CREATE INDEX idx_products_active ON products(is_active); CREATE INDEX idx_products_featured ON products(featured); CREATE INDEX idx_products_sku ON products(sku); CREATE INDEX idx_products_created_at ON products(created_at); CREATE INDEX idx_orders_user ON orders(user_id); CREATE INDEX idx_orders_status ON orders(status); CREATE INDEX idx_orders_created_at ON orders(created_at); CREATE INDEX idx_orders_number ON orders(order_number); CREATE INDEX idx_order_items_order ON order_items(order_id); CREATE INDEX idx_order_items_product ON order_items(product_id); CREATE INDEX idx_reviews_product ON reviews(product_id); CREATE INDEX idx_reviews_user ON reviews(user_id); CREATE INDEX idx_reviews_approved ON reviews(is_approved); -- Sample data for testing INSERT INTO categories (name, slug, description) VALUES ('Electronics', 'electronics', 'Electronic devices and accessories'), ('Computers', 'computers', 'Laptops, desktops, and computer accessories'), ('Smartphones', 'smartphones', 'Mobile phones and accessories'), ('Books', 'books', 'Physical and digital books'); INSERT INTO products (name, slug, description, price, cost_price, sku, stock_quantity, category_id, brand) VALUES ('MacBook Pro 16"', 'macbook-pro-16', 'Apple MacBook Pro 16-inch with M3 chip', 2499.00, 2000.00, 'MBP16-M3', 15, 2, 'Apple'), ('iPhone 15 Pro', 'iphone-15-pro', 'Latest iPhone with titanium design', 999.00, 700.00, 'IPH15-PRO', 50, 3, 'Apple'), ('The Great Gatsby', 'great-gatsby', 'Classic American novel by F. Scott Fitzgerald', 12.99, 5.00, 'BOOK-GG-001', 100, 4, 'Scribner'); -- Note: This is a simplified schema for demonstration purposes. -- In production, you might need additional tables for: -- - Inventory management -- - Product variants (size, color, etc.) -- - Shipping methods and tracking -- - Payment transactions -- - Customer support tickets -- - Analytics and reporting tables