`db/migrations/0004_valuation_calls.sql`

42 lines
-- 0004_valuation_calls.sql — immutable audit log for the valuation API.
--
-- BLUEPRINT.md and METHODOLOGY.md both require an immutable audit log
-- of every published valuation. Lenders and insurers will not sign a
-- contract without it: they need to be able to point at a specific
-- call, see what we returned, and reproduce the answer. This table is
-- the canonical record; the v0 middleware writes to it fire-and-forget
-- after the response, so it doesn't add user-visible latency.
--
-- The table is INSERT-only by app convention. There's no UPDATE or
-- DELETE permission grant — when we lock down DB roles in v1.0, the
-- app role gets only INSERT + SELECT here.

CREATE TABLE IF NOT EXISTS valuation_calls (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  called_at             timestamptz NOT NULL DEFAULT now(),
  caller_ip             inet,
  -- Hashed Bearer token (sha256) — never store the raw token.
  caller_token_hash     text,
  route                 text NOT NULL,         -- '/api/valuation/:slug'
  method                text NOT NULL,         -- 'GET'
  asset_slug            text,                  -- when applicable
  response_status       int  NOT NULL,
  methodology_version   text,
  latency_ms            int,
  user_agent            text,
  request_id            uuid                   -- for trace correlation across services
);

CREATE INDEX IF NOT EXISTS valuation_calls_called_idx
  ON valuation_calls(called_at DESC);

CREATE INDEX IF NOT EXISTS valuation_calls_slug_idx
  ON valuation_calls(asset_slug, called_at DESC) WHERE asset_slug IS NOT NULL;

CREATE INDEX IF NOT EXISTS valuation_calls_ip_idx
  ON valuation_calls(caller_ip, called_at DESC) WHERE caller_ip IS NOT NULL;

-- bookkeeping
INSERT INTO schema_migrations(filename) VALUES ('0004_valuation_calls.sql')
  ON CONFLICT (filename) DO NOTHING;