erDiagram PRODUCTS ||--o{ PRODUCT_VARIANTS : contains PRODUCTS ||--o{ QR_LABELS : displayed_as PRODUCT_VARIANTS ||--o{ INVENTORY : stocked WAREHOUSE_LOCATIONS ||--o{ INVENTORY : stores ORDERS ||--|{ ORDER_ITEMS : contains PRODUCT_VARIANTS ||--o{ ORDER_ITEMS : ordered ORDERS ||--o{ STOCK_RESERVATIONS : reserves ORDERS ||--o| PICKING_TASKS : creates PICKING_TASKS ||--o{ PICKING_SCANS : records ORDERS ||--o{ PAYMENTS : paid_by PRODUCT_VARIANTS ||--o{ STOCK_MOVEMENTS : tracks

CREATE TABLE products ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, seller_id BIGINT NOT NULL, name TEXT NOT NULL, model_code TEXT NOT NULL, category TEXT NOT NULL DEFAULT 'shoes', active BOOLEAN NOT NULL DEFAULT TRUE, UNIQUE (seller_id, model_code) ); CREATE TABLE product_variants ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, product_id BIGINT NOT NULL REFERENCES products(id), sku TEXT NOT NULL UNIQUE, barcode TEXT NOT NULL UNIQUE, size TEXT NOT NULL, color TEXT NOT NULL, price NUMERIC(12,2) NOT NULL CHECK (price >= 0) ); CREATE TABLE warehouse_locations ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, code TEXT NOT NULL UNIQUE, zone TEXT NOT NULL, active BOOLEAN NOT NULL DEFAULT TRUE ); CREATE TABLE inventory ( variant_id BIGINT NOT NULL REFERENCES product_variants(id), location_id BIGINT NOT NULL REFERENCES warehouse_locations(id), on_hand INTEGER NOT NULL DEFAULT 0 CHECK (on_hand >= 0), reserved INTEGER NOT NULL DEFAULT 0 CHECK (reserved >= 0), PRIMARY KEY (variant_id, location_id), CHECK (reserved <= on_hand) ); CREATE TABLE orders ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_number TEXT NOT NULL UNIQUE, customer_id BIGINT, status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ( 'pending', 'reserved', 'picking', 'ready', 'checking', 'awaiting_payment', 'paid', 'issued', 'cancelled' )), total NUMERIC(12,2) NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE TABLE order_items ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id), variant_id BIGINT NOT NULL REFERENCES product_variants(id), product_name_snapshot TEXT NOT NULL, barcode_snapshot TEXT NOT NULL, size_snapshot TEXT NOT NULL, unit_price NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0), quantity INTEGER NOT NULL CHECK (quantity > 0), picked_quantity INTEGER NOT NULL DEFAULT 0 CHECK (picked_quantity >= 0 AND picked_quantity <= quantity) ); CREATE TABLE stock_reservations ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id), variant_id BIGINT NOT NULL REFERENCES product_variants(id), location_id BIGINT NOT NULL REFERENCES warehouse_locations(id), quantity INTEGER NOT NULL CHECK (quantity > 0), status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'consumed', 'released', 'expired')), expires_at TIMESTAMPTZ NOT NULL ); CREATE TABLE picking_scans ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_item_id BIGINT NOT NULL REFERENCES order_items(id), expected_barcode TEXT NOT NULL, scanned_barcode TEXT NOT NULL, expected_size TEXT NOT NULL, result TEXT NOT NULL CHECK (result IN ('accepted', 'rejected')), scanned_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE TABLE payments ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id BIGINT NOT NULL REFERENCES orders(id), amount NUMERIC(12,2) NOT NULL CHECK (amount >= 0), method TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'failed', 'refunded')), external_reference TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE TABLE stock_movements ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, variant_id BIGINT NOT NULL REFERENCES product_variants(id), from_location_id BIGINT REFERENCES warehouse_locations(id), to_location_id BIGINT REFERENCES warehouse_locations(id), quantity INTEGER NOT NULL CHECK (quantity > 0), reason TEXT NOT NULL, order_id BIGINT REFERENCES orders(id), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_orders_status_created ON orders(status, created_at); CREATE INDEX idx_reservations_expiry ON stock_reservations(status, expires_at); CREATE INDEX idx_stock_movements_variant ON stock_movements(variant_id, created_at);