lti-api/internal/database/migrations/20251110070219_create_stock_allocations_table.up.sql

CREATE TABLE IF NOT EXISTS stock_allocations (
    id BIGSERIAL PRIMARY KEY,
    product_warehouse_id BIGINT NOT NULL REFERENCES product_warehouses(id),
    stockable_type VARCHAR(100) NOT NULL,
    stockable_id BIGINT NOT NULL,
    usable_type VARCHAR(100) NOT NULL,
    usable_id BIGINT NOT NULL,
    qty NUMERIC(15,3) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    note TEXT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    released_at TIMESTAMPTZ NULL,
    deleted_at TIMESTAMPTZ NULL
);

CREATE INDEX IF NOT EXISTS stock_allocations_product_warehouse_id_idx
    ON stock_allocations (product_warehouse_id);

CREATE INDEX IF NOT EXISTS stock_allocations_lookup
    ON stock_allocations (stockable_type, stockable_id);

CREATE INDEX IF NOT EXISTS stock_allocations_usage_lookup
    ON stock_allocations (usable_type, usable_id);

CREATE INDEX IF NOT EXISTS stock_allocations_status_idx
    ON stock_allocations (status);

CREATE INDEX IF NOT EXISTS stock_allocations_released_at_idx
    ON stock_allocations (released_at);