Spaces:
Sleeping
Sleeping
File size: 2,080 Bytes
924a755 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | -- 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
);
|