data/schema.sql

-- AutoSource warehouse schema (plan §6). Only mcp_server touches this DB.

CREATE TABLE IF NOT EXISTS inventory (
    sku           TEXT PRIMARY KEY,
    name          TEXT NOT NULL,
    unit          TEXT NOT NULL,
    qty_on_hand   INTEGER NOT NULL,
    reorder_point INTEGER NOT NULL,
    reorder_qty   INTEGER NOT NULL,
    list_price    REAL NOT NULL,
    status        TEXT NOT NULL DEFAULT 'ok'
                  CHECK (status IN ('ok', 'low', 'on_order'))
);

CREATE TABLE IF NOT EXISTS suppliers (
    vendor_id      TEXT PRIMARY KEY,
    name           TEXT NOT NULL,
    persona        TEXT NOT NULL CHECK (persona IN ('aggressive', 'flexible', 'premium')),
    base_price     REAL NOT NULL,
    cost_floor     REAL NOT NULL,          -- hidden: never leaves the MCP server
    lead_time_days INTEGER NOT NULL,
    reliability    REAL NOT NULL CHECK (reliability BETWEEN 0 AND 1)
);

CREATE TABLE IF NOT EXISTS supplier_skus (
    vendor_id  TEXT NOT NULL REFERENCES suppliers(vendor_id),
    sku        TEXT NOT NULL REFERENCES inventory(sku),
    base_price REAL NOT NULL,
    in_stock   INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (vendor_id, sku)
);

CREATE TABLE IF NOT EXISTS vendor_memory (
    vendor_id       TEXT NOT NULL,
    sku             TEXT NOT NULL,
    last_price      REAL,
    rounds          INTEGER,
    settled         INTEGER,               -- bool
    reliability_obs REAL,
    updated_at      TEXT,
    PRIMARY KEY (vendor_id, sku)
);

CREATE TABLE IF NOT EXISTS purchase_orders (
    po_id       TEXT PRIMARY KEY,
    sku         TEXT NOT NULL,
    vendor_id   TEXT NOT NULL,
    qty         INTEGER NOT NULL,
    unit_price  REAL NOT NULL,
    total       REAL NOT NULL,
    status      TEXT NOT NULL DEFAULT 'created',
    created_at  TEXT NOT NULL,
    approved_by TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS run_trace (
    trace_id     INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id       TEXT NOT NULL,
    ts           TEXT NOT NULL,
    actor        TEXT NOT NULL,
    event_type   TEXT NOT NULL,
    payload_json TEXT NOT NULL
);