POSTGRESQL REFERENCE · WORKING SOURCE

migrations/001_initial.sql

This file ships in the runnable source archive. Synthetic fixtures and demonstration policies are identified in the README.

CREATE TABLE organizations (id text PRIMARY KEY, name text NOT NULL, kind text NOT NULL CHECK (kind IN ('operator','customer')));
CREATE TABLE users (id text PRIMARY KEY, email text UNIQUE NOT NULL, name text NOT NULL, password_hash text NOT NULL);
CREATE TABLE memberships (user_id text REFERENCES users(id), org_id text REFERENCES organizations(id), role text NOT NULL CHECK(role IN ('operator','customer')), PRIMARY KEY(user_id,org_id));
CREATE TABLE operator_customers (operator_org_id text REFERENCES organizations(id), customer_org_id text REFERENCES organizations(id), PRIMARY KEY(operator_org_id,customer_org_id));
CREATE TABLE sessions (token_hash text PRIMARY KEY, user_id text NOT NULL REFERENCES users(id), csrf_token text NOT NULL, expires_at timestamptz NOT NULL, created_at timestamptz NOT NULL DEFAULT clock_timestamp());
CREATE TABLE warehouses (id text PRIMARY KEY, operator_org_id text NOT NULL REFERENCES organizations(id), name text NOT NULL, timezone text NOT NULL);
CREATE TABLE zones (id text PRIMARY KEY, warehouse_id text NOT NULL REFERENCES warehouses(id), name text NOT NULL, length_mm int NOT NULL DEFAULT 1219, width_mm int NOT NULL DEFAULT 1016, max_height_mm int NOT NULL, max_weight_kg int NOT NULL, storage_condition text NOT NULL DEFAULT 'ambient', goods text NOT NULL DEFAULT 'packaged_dry', arrangement text NOT NULL DEFAULT 'selective_rack');
CREATE TABLE positions (id text PRIMARY KEY, zone_id text NOT NULL REFERENCES zones(id), label text NOT NULL UNIQUE);
CREATE TABLE demo_clock (id int PRIMARY KEY CHECK(id=1), now_at timestamptz NOT NULL);
CREATE TABLE rate_cards (id text PRIMARY KEY, warehouse_id text NOT NULL REFERENCES warehouses(id), version text NOT NULL, currency text NOT NULL CHECK(currency='USD'), storage_cents int NOT NULL CHECK(storage_cents>=0), receiving_cents int NOT NULL CHECK(receiving_cents>=0), release_cents int NOT NULL CHECK(release_cents>=0), effective_at timestamptz NOT NULL, terms text NOT NULL, UNIQUE(warehouse_id,version));
CREATE TABLE quotes (id text PRIMARY KEY, customer_org_id text NOT NULL REFERENCES organizations(id), warehouse_id text NOT NULL REFERENCES warehouses(id), zone_id text NOT NULL REFERENCES zones(id), rate_card_id text NOT NULL REFERENCES rate_cards(id), quantity int NOT NULL CHECK(quantity>0), start_at timestamptz NOT NULL, end_at timestamptz NOT NULL, expires_at timestamptz NOT NULL, declaration jsonb NOT NULL, estimate_cents int NOT NULL, terms text NOT NULL, created_by text NOT NULL REFERENCES users(id), recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(), CHECK(start_at<end_at));
CREATE TABLE holds (id text PRIMARY KEY, quote_id text NOT NULL UNIQUE REFERENCES quotes(id), customer_org_id text NOT NULL REFERENCES organizations(id), warehouse_id text NOT NULL REFERENCES warehouses(id), expires_at timestamptz NOT NULL, status text NOT NULL CHECK(status IN ('active','confirmed','expired','cancelled')), created_by text NOT NULL REFERENCES users(id), recorded_at timestamptz NOT NULL DEFAULT clock_timestamp());
CREATE TABLE reservations (id text PRIMARY KEY, quote_id text NOT NULL UNIQUE REFERENCES quotes(id), hold_id text NOT NULL UNIQUE REFERENCES holds(id), customer_org_id text NOT NULL REFERENCES organizations(id), warehouse_id text NOT NULL REFERENCES warehouses(id), zone_id text NOT NULL REFERENCES zones(id), rate_card_id text NOT NULL REFERENCES rate_cards(id), quantity int NOT NULL CHECK(quantity>0), start_at timestamptz NOT NULL, end_at timestamptz NOT NULL, declaration jsonb NOT NULL, estimate_cents int NOT NULL, status text NOT NULL DEFAULT 'confirmed' CHECK(status IN ('confirmed','cancelled','expired','completed')), receiving_status text NOT NULL DEFAULT 'open' CHECK(receiving_status IN ('open','closed_released','closed_retained')), created_by text NOT NULL REFERENCES users(id), recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(), CHECK(start_at<end_at));
CREATE TABLE allocations (id text PRIMARY KEY, hold_id text NOT NULL REFERENCES holds(id), reservation_id text REFERENCES reservations(id), position_id text NOT NULL REFERENCES positions(id), start_at timestamptz NOT NULL, end_at timestamptz NOT NULL, state text NOT NULL CHECK(state IN ('held','committed','occupied','released')), CHECK(start_at<end_at));
CREATE INDEX allocation_position_interval ON allocations(position_id,start_at,end_at);
CREATE INDEX allocation_reservation ON allocations(reservation_id);
CREATE TABLE pallets (id text PRIMARY KEY, label text NOT NULL, external_id text, external_scheme text, owner_org_id text NOT NULL REFERENCES organizations(id), reservation_id text NOT NULL REFERENCES reservations(id), allocation_id text NOT NULL UNIQUE REFERENCES allocations(id), declaration jsonb NOT NULL, UNIQUE(owner_org_id,external_id));
CREATE TABLE custodies (id text PRIMARY KEY, pallet_id text NOT NULL REFERENCES pallets(id), position_id text REFERENCES positions(id), received_at timestamptz NOT NULL, released_at timestamptz, CHECK(released_at IS NULL OR released_at>=received_at), CHECK((released_at IS NULL AND position_id IS NOT NULL) OR (released_at IS NOT NULL AND position_id IS NULL)));
CREATE UNIQUE INDEX one_active_custody_per_pallet ON custodies(pallet_id) WHERE released_at IS NULL;
CREATE UNIQUE INDEX one_active_pallet_per_position ON custodies(position_id) WHERE released_at IS NULL;
CREATE TABLE movements (id text PRIMARY KEY, kind text NOT NULL CHECK(kind IN ('received','released','relocated','adjustment')), reservation_id text NOT NULL REFERENCES reservations(id), pallet_id text REFERENCES pallets(id), custody_id text REFERENCES custodies(id), owner_org_id text NOT NULL REFERENCES organizations(id), actor_id text NOT NULL REFERENCES users(id), warehouse_id text NOT NULL REFERENCES warehouses(id), from_position_id text REFERENCES positions(id), to_position_id text REFERENCES positions(id), occurred_at timestamptz NOT NULL, recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(), reason text NOT NULL, adjustment_of text REFERENCES movements(id), data jsonb NOT NULL DEFAULT '{}');
CREATE TABLE charge_lines (id text PRIMARY KEY, reservation_id text NOT NULL REFERENCES reservations(id), pallet_id text REFERENCES pallets(id), custody_id text REFERENCES custodies(id), kind text NOT NULL CHECK(kind IN ('storage','receiving','release','adjustment')), service_date date, quantity int NOT NULL, unit_cents int NOT NULL, total_cents int NOT NULL, rate_card_id text NOT NULL REFERENCES rate_cards(id), movement_ids text[] NOT NULL, recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(), CHECK(total_cents=quantity*unit_cents));
CREATE UNIQUE INDEX one_storage_line_per_custody_day ON charge_lines(custody_id,service_date) WHERE kind='storage';
CREATE UNIQUE INDEX one_handling_line_per_custody_kind ON charge_lines(custody_id,kind) WHERE kind IN ('receiving','release');
CREATE TABLE release_requests (id text PRIMARY KEY, reservation_id text NOT NULL REFERENCES reservations(id), customer_org_id text NOT NULL REFERENCES organizations(id), pallet_ids text[] NOT NULL, reason text NOT NULL, status text NOT NULL DEFAULT 'requested' CHECK(status IN ('requested','completed')), requested_by text NOT NULL REFERENCES users(id), recorded_at timestamptz NOT NULL DEFAULT clock_timestamp());
CREATE TABLE capacity_blocks (id text PRIMARY KEY, warehouse_id text NOT NULL REFERENCES warehouses(id), position_id text NOT NULL REFERENCES positions(id), start_at timestamptz NOT NULL, end_at timestamptz NOT NULL, reason text NOT NULL, created_by text NOT NULL REFERENCES users(id), revoked_at timestamptz, recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(), CHECK(start_at<end_at));
CREATE TABLE audit_events (id text PRIMARY KEY, actor_id text NOT NULL REFERENCES users(id), customer_org_id text REFERENCES organizations(id), warehouse_id text REFERENCES warehouses(id), reservation_id text REFERENCES reservations(id), action text NOT NULL, occurred_at timestamptz NOT NULL, recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(), reason text NOT NULL, data jsonb NOT NULL DEFAULT '{}');
CREATE TABLE idempotency (actor_id text NOT NULL REFERENCES users(id), operation text NOT NULL, key text NOT NULL, payload_hash text NOT NULL, result jsonb NOT NULL, recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(), PRIMARY KEY(actor_id,operation,key));
CREATE FUNCTION forbid_history_change() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'append-only history: add a linked adjustment'; END $$;
CREATE TRIGGER movements_immutable BEFORE UPDATE OR DELETE ON movements FOR EACH ROW EXECUTE FUNCTION forbid_history_change();
CREATE TRIGGER audit_immutable BEFORE UPDATE OR DELETE ON audit_events FOR EACH ROW EXECUTE FUNCTION forbid_history_change();
CREATE TRIGGER rate_cards_immutable BEFORE UPDATE OR DELETE ON rate_cards FOR EACH ROW EXECUTE FUNCTION forbid_history_change();
CREATE TRIGGER quotes_immutable BEFORE UPDATE OR DELETE ON quotes FOR EACH ROW EXECUTE FUNCTION forbid_history_change();
CREATE TRIGGER charge_lines_immutable BEFORE UPDATE OR DELETE ON charge_lines FOR EACH ROW EXECUTE FUNCTION forbid_history_change();