undefined

erDiagram
    users ||--o{ orders : places
    users ||--o{ addresses : has
    users ||--o{ payment_methods : has
    orders ||--|{ order_items : contains
    orders ||--|| payments : has
    products ||--o{ order_items : includes
    products }o--|| categories : belongs_to
    products ||--o{ product_images : has
    products ||--o{ inventory : tracks

    users {
        uuid id PK
        string email UK
        string password_hash
        string name
        timestamp created_at
        timestamp updated_at
    }

    orders {
        uuid id PK
        uuid user_id FK
        string status
        decimal total_amount
        timestamp created_at
        timestamp updated_at
    }

    order_items {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal price
    }

    products {
        uuid id PK
        string name
        text description
        decimal price
        uuid category_id FK
        boolean active
    }

CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_deleted_at ON users(deleted_at) WHERE deleted_at IS NULL;

ALTER TABLE users
  ADD CONSTRAINT users_email_format
  CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

ALTER TABLE users
  ADD CONSTRAINT users_name_length
  CHECK (length(name) >= 2);

-- Update updated_at timestamp
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

INSERT INTO users (email, password_hash, name, email_verified)
VALUES
  ('john@example.com', '$2b$12$...', 'John Doe', true),
  ('jane@example.com', '$2b$12$...', 'Jane Smith', true);

CREATE UNIQUE INDEX idx_products_slug ON products(slug);
CREATE UNIQUE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_active ON products(active);
CREATE INDEX idx_products_featured ON products(featured) WHERE featured = true;
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

ALTER TABLE products
  ADD CONSTRAINT fk_products_category
  FOREIGN KEY (category_id)
  REFERENCES categories(id)
  ON DELETE RESTRICT;

-- Add full-text search column
ALTER TABLE products ADD COLUMN search_vector tsvector;

-- Create full-text index
CREATE INDEX idx_products_search ON products USING gin(search_vector);

-- Trigger to update search vector
CREATE TRIGGER products_search_vector_update
  BEFORE INSERT OR UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION
    tsvector_update_trigger(
      search_vector, 'pg_catalog.english',
      name, description, brand
    );

CREATE UNIQUE INDEX idx_orders_order_number ON orders(order_number);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

ALTER TABLE orders
  ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded'));

ALTER TABLE orders
  ADD CONSTRAINT orders_total_positive
  CHECK (total >= 0);

-- Total is computed from subtotal + tax + shipping
ALTER TABLE orders
  ADD CONSTRAINT orders_total_computation
  CHECK (total = subtotal + tax + shipping);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

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;

ALTER TABLE order_items
  ADD CONSTRAINT order_items_quantity_positive
  CHECK (quantity > 0);

ALTER TABLE order_items
  ADD CONSTRAINT order_items_subtotal_computation
  CHECK (subtotal = quantity * unit_price);

CREATE VIEW active_products_view AS
SELECT
  p.id,
  p.name,
  p.slug,
  p.description,
  p.price,
  p.compare_at_price,
  p.sku,
  p.brand,
  c.name as category_name,
  c.slug as category_slug,
  (SELECT COUNT(*) FROM order_items oi WHERE oi.product_id = p.id) as times_ordered,
  (SELECT AVG(rating) FROM product_reviews pr WHERE pr.product_id = p.id) as avg_rating
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.active = true;

CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
  u.id as user_id,
  u.email,
  u.name,
  COUNT(o.id) as total_orders,
  SUM(o.total) as total_spent,
  AVG(o.total) as average_order_value,
  MAX(o.created_at) as last_order_date,
  MIN(o.created_at) as first_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status != 'cancelled'
GROUP BY u.id, u.email, u.name;

-- Refresh strategy
CREATE INDEX idx_user_order_summary_user_id ON user_order_summary(user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;

CREATE OR REPLACE FUNCTION calculate_order_total(
  p_subtotal decimal,
  p_tax_rate decimal,
  p_shipping decimal
)
RETURNS decimal AS $$
BEGIN
  RETURN ROUND((p_subtotal * (1 + p_tax_rate) + p_shipping)::numeric, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Order status values
CREATE TYPE order_status AS ENUM (
  'pending',
  'confirmed',
  'processing',
  'shipped',
  'delivered',
  'cancelled',
  'refunded'
);

-- Payment status values
CREATE TYPE payment_status AS ENUM (
  'pending',
  'processing',
  'succeeded',
  'failed',
  'refunded'
);

{
  "street": "123 Main St",
  "street2": "Apt 4B",
  "city": "New York",
  "state": "NY",
  "postalCode": "10001",
  "country": "US"
}

{
  "name": "Product Name",
  "sku": "PROD-123",
  "price": 99.99,
  "image": "https://cdn.example.com/product.jpg"
}

-- Up
ALTER TABLE users ADD COLUMN two_factor_enabled BOOLEAN DEFAULT FALSE;
ALTER TABLE users ADD COLUMN two_factor_secret VARCHAR(32);

CREATE TABLE two_factor_backup_codes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  code_hash VARCHAR(255) NOT NULL,
  used_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_2fa_backup_codes_user_id ON two_factor_backup_codes(user_id);

-- Down
DROP TABLE two_factor_backup_codes;
ALTER TABLE users DROP COLUMN two_factor_secret;
ALTER TABLE users DROP COLUMN two_factor_enabled;

-- Frequently queried columns
CREATE INDEX CONCURRENTLY idx_users_email_verified ON users(email_verified);
CREATE INDEX CONCURRENTLY idx_products_price ON products(price);
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);

-- Composite indexes for common queries
CREATE INDEX CONCURRENTLY idx_products_category_active
  ON products(category_id, active)
  WHERE active = true;

CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON orders(user_id, created_at DESC);

-- EXPLAIN ANALYZE for slow queries
EXPLAIN ANALYZE
SELECT p.*, c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.active = true
ORDER BY p.created_at DESC
LIMIT 20;

-- Add covering index if needed
CREATE INDEX idx_products_active_created
  ON products(active, created_at DESC)
  INCLUDE (name, price, slug);

undefined

---

undefined