Product Schema (E-Commerce)

Products

CREATE TABLE product.products (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    category_id UUID REFERENCES product.categories(id),

    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,

    short_description VARCHAR(500),
    description TEXT,

    base_price DECIMAL(20,2) NOT NULL,
    sale_price DECIMAL(20,2),
    cost_price DECIMAL(20,2),

    currency VARCHAR(3) NOT NULL DEFAULT 'RUB',
    status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
    -- DRAFT, ACTIVE, OUT_OF_STOCK, DISCONTINUED

    stock_quantity INT DEFAULT 0,
    low_stock_threshold INT DEFAULT 10,

    weight_grams INT,
    dimensions JSONB,  -- {length, width, height}

    career_points_value DECIMAL(20,2) DEFAULT 0,  -- MLM points for purchase

    is_featured BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,

    published_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_products_category ON product.products(category_id);
CREATE INDEX idx_products_slug ON product.products(slug);
CREATE INDEX idx_products_status ON product.products(status);
CREATE INDEX idx_products_sku ON product.products(sku);

Product Categories

CREATE TABLE product.categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,

    parent_id UUID REFERENCES product.categories(id),
    path LTREE,  -- For efficient hierarchy queries

    image_url VARCHAR(500),
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_categories_path ON product.categories USING GIST(path);
CREATE INDEX idx_categories_parent ON product.categories(parent_id);

Product Images

CREATE TABLE product.product_images (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    product_id UUID NOT NULL REFERENCES product.products(id) ON DELETE CASCADE,

    url VARCHAR(500) NOT NULL,
    alt_text VARCHAR(200),
    sort_order INT DEFAULT 0,
    is_primary BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_images_product ON product.product_images(product_id);

Carts

CREATE TABLE product.carts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES core.users(id),
    session_id VARCHAR(100),  -- For guest carts

    referring_partner_id UUID REFERENCES mlm.partners(id),

    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    -- ACTIVE, CONVERTED, ABANDONED

    subtotal DECIMAL(20,2) DEFAULT 0,
    discount_total DECIMAL(20,2) DEFAULT 0,
    total DECIMAL(20,2) DEFAULT 0,
    currency VARCHAR(3) DEFAULT 'RUB',

    expires_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_carts_user ON product.carts(user_id);
CREATE INDEX idx_carts_session ON product.carts(session_id);
CREATE INDEX idx_carts_status ON product.carts(status);

Cart Items

CREATE TABLE product.cart_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    cart_id UUID NOT NULL REFERENCES product.carts(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES product.products(id),

    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(20,2) NOT NULL,
    total_price DECIMAL(20,2) NOT NULL,

    added_at TIMESTAMP NOT NULL DEFAULT NOW(),

    UNIQUE(cart_id, product_id)
);

CREATE INDEX idx_cart_items_cart ON product.cart_items(cart_id);

Order State Machine

Orders follow a strict finite state machine to ensure data integrity and valid business transitions.

State Diagram

                    ┌─────────────┐
                    │   PENDING   │ ← Initial state (order created)
                    └──────┬──────┘
                           │ payment_success
                           ▼
                    ┌─────────────┐
        ┌──────────│  CONFIRMED  │──────────┐
        │          └──────┬──────┘          │
        │                 │ start_processing│ cancel (before shipping)
        │                 ▼                 │
        │          ┌─────────────┐          │
        │          │ PROCESSING  │──────────┤
        │          └──────┬──────┘          │
        │                 │ ship            │
        │                 ▼                 ▼
        │          ┌─────────────┐   ┌─────────────┐
        │          │   SHIPPED   │   │  CANCELLED  │
        │          └──────┬──────┘   └─────────────┘
        │                 │ deliver
        │                 ▼
        │          ┌─────────────┐
        │          │  DELIVERED  │
        │          └──────┬──────┘
        │                 │ request_refund (within 14 days)
        │                 ▼
        │          ┌─────────────┐
        └─────────→│  REFUNDED   │ ← Also reachable via chargeback
                   └─────────────┘

Valid Transitions Table

From State	To State	Trigger	Conditions
PENDING	CONFIRMED	payment_success	Payment completed
PENDING	CANCELLED	cancel	User cancels before payment
CONFIRMED	PROCESSING	start_processing	Admin/system starts fulfillment
CONFIRMED	CANCELLED	cancel	Before shipping only
PROCESSING	SHIPPED	ship	Tracking number assigned
PROCESSING	CANCELLED	cancel	Before shipping only
SHIPPED	DELIVERED	deliver	Carrier confirms delivery
DELIVERED	REFUNDED	refund	Within refund window (14 days)
ANY (paid)	REFUNDED	chargeback	Payment provider dispute

Transition Validation (Application Layer)

const ORDER_TRANSITIONS: Record<OrderStatus, OrderStatus[]> = {
  PENDING: ['CONFIRMED', 'CANCELLED'],
  CONFIRMED: ['PROCESSING', 'CANCELLED'],
  PROCESSING: ['SHIPPED', 'CANCELLED'],
  SHIPPED: ['DELIVERED'],
  DELIVERED: ['REFUNDED'],
  CANCELLED: [],  // Terminal state
  REFUNDED: [],   // Terminal state
};

function canTransition(from: OrderStatus, to: OrderStatus): boolean {
  return ORDER_TRANSITIONS[from]?.includes(to) ?? false;
}

Database Trigger for Transition Validation

CREATE OR REPLACE FUNCTION product.validate_order_transition()
RETURNS TRIGGER AS $$
BEGIN
    -- Skip if status not changed
    IF OLD.status = NEW.status THEN
        RETURN NEW;
    END IF;

    -- Validate transition
    IF NOT (
        (OLD.status = 'PENDING' AND NEW.status IN ('CONFIRMED', 'CANCELLED')) OR
        (OLD.status = 'CONFIRMED' AND NEW.status IN ('PROCESSING', 'CANCELLED')) OR
        (OLD.status = 'PROCESSING' AND NEW.status IN ('SHIPPED', 'CANCELLED')) OR
        (OLD.status = 'SHIPPED' AND NEW.status = 'DELIVERED') OR
        (OLD.status = 'DELIVERED' AND NEW.status = 'REFUNDED')
    ) THEN
        RAISE EXCEPTION 'Invalid order status transition: % -> %', OLD.status, NEW.status;
    END IF;

    -- Set timestamps
    CASE NEW.status
        WHEN 'CONFIRMED' THEN NEW.confirmed_at = NOW();
        WHEN 'SHIPPED' THEN NEW.shipped_at = NOW();
        WHEN 'DELIVERED' THEN NEW.delivered_at = NOW();
        WHEN 'CANCELLED' THEN NEW.cancelled_at = NOW();
        WHEN 'REFUNDED' THEN NEW.refunded_at = NOW();
        ELSE NULL;
    END CASE;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_status_transition
    BEFORE UPDATE ON product.orders
    FOR EACH ROW
    EXECUTE FUNCTION product.validate_order_transition();

---

Orders

CREATE TABLE product.orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_number VARCHAR(20) NOT NULL UNIQUE,
    user_id UUID NOT NULL REFERENCES core.users(id),

    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    -- PENDING, CONFIRMED, PROCESSING, SHIPPED, DELIVERED, CANCELLED, REFUNDED

    subtotal DECIMAL(20,2) NOT NULL,
    discount_total DECIMAL(20,2) DEFAULT 0,
    shipping_cost DECIMAL(20,2) DEFAULT 0,
    tax_amount DECIMAL(20,2) DEFAULT 0,
    total DECIMAL(20,2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'RUB',

    shipping_address_id UUID REFERENCES product.addresses(id),
    billing_address_id UUID REFERENCES product.addresses(id),
    shipping_method_id UUID REFERENCES product.shipping_methods(id),

    promo_code_id UUID,
    referring_partner_id UUID REFERENCES mlm.partners(id),

    notes TEXT,

    confirmed_at TIMESTAMP,
    shipped_at TIMESTAMP,
    delivered_at TIMESTAMP,
    cancelled_at TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_user ON product.orders(user_id, created_at DESC);
CREATE INDEX idx_orders_status ON product.orders(status);
CREATE INDEX idx_orders_number ON product.orders(order_number);

Order Items

CREATE TABLE product.order_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_id UUID NOT NULL REFERENCES product.orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES product.products(id),

    sku VARCHAR(50) NOT NULL,  -- Snapshot
    name VARCHAR(200) NOT NULL,  -- Snapshot
    quantity INT NOT NULL,
    unit_price DECIMAL(20,2) NOT NULL,
    total_price DECIMAL(20,2) NOT NULL,
    career_points_value DECIMAL(20,2) DEFAULT 0,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_order_items_order ON product.order_items(order_id);

Addresses

CREATE TABLE product.addresses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES core.users(id) ON DELETE CASCADE,

    type VARCHAR(20) NOT NULL,  -- SHIPPING, BILLING

    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    company VARCHAR(200),

    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20) NOT NULL,
    country VARCHAR(3) NOT NULL,  -- ISO 3166-1 alpha-3

    phone VARCHAR(20),
    is_default BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_addresses_user ON product.addresses(user_id);

Payments

CREATE TABLE product.payments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_id UUID NOT NULL REFERENCES product.orders(id),

    amount DECIMAL(20,2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'RUB',

    method VARCHAR(20) NOT NULL,  -- CARD, EWALLET, BANK_TRANSFER
    provider VARCHAR(20) NOT NULL,  -- STRIPE, YOOKASSA, etc.

    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    -- PENDING, PROCESSING, COMPLETED, FAILED, REFUNDED

    provider_reference VARCHAR(255),
    provider_response JSONB,

    paid_at TIMESTAMP,
    failed_at TIMESTAMP,
    refunded_at TIMESTAMP,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_payments_order ON product.payments(order_id);
CREATE INDEX idx_payments_status ON product.payments(status);

Shipments

CREATE TABLE product.shipments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_id UUID NOT NULL REFERENCES product.orders(id),

    carrier VARCHAR(50) NOT NULL,  -- CDEK, DHL, etc.
    tracking_number VARCHAR(100),

    status VARCHAR(20) NOT NULL DEFAULT 'PREPARING',
    -- PREPARING, SHIPPED, IN_TRANSIT, DELIVERED, RETURNED

    shipped_at TIMESTAMP,
    estimated_delivery DATE,
    delivered_at TIMESTAMP,

    tracking_events JSONB,

    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_shipments_order ON product.shipments(order_id);
CREATE INDEX idx_shipments_tracking ON product.shipments(tracking_number);

Shipping Methods

CREATE TABLE product.shipping_methods (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,

    carrier VARCHAR(50) NOT NULL,
    base_price DECIMAL(20,2) NOT NULL,
    estimated_days_min INT,
    estimated_days_max INT,

    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,

    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

---

Related Documents

• Investment Schema
• Operations Schema