`db/migrations/0004_claims.sql`

44 lines
-- 0004_claims.sql — claim_case + action_queue tables (Phase 4 starter)

BEGIN;

CREATE TABLE IF NOT EXISTS claim_case (
  id              text PRIMARY KEY,
  user_id         text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  asset_table     text,                       -- 'purchase', 'recall_match'
  asset_id        text,
  claim_type      text NOT NULL,              -- refund | replace | repair | recall_remedy | dispute
  routing         text NOT NULL,              -- merchant | manufacturer | issuer | regulator
  jurisdiction    text DEFAULT 'US-CA',
  state           text NOT NULL DEFAULT 'draft',  -- draft | sent | resolved | abandoned
  desired_remedy  text,
  due_at          timestamptz,
  draft_subject   text,
  draft_body      text,
  cited_rules_jsonb jsonb NOT NULL DEFAULT '[]'::jsonb,
  evidence_jsonb  jsonb NOT NULL DEFAULT '[]'::jsonb,
  metadata_jsonb  jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  resolved_at     timestamptz
);
CREATE INDEX IF NOT EXISTS claim_case_user_state_idx ON claim_case (user_id, state, due_at);
CREATE INDEX IF NOT EXISTS claim_case_asset_idx ON claim_case (asset_table, asset_id);

CREATE TABLE IF NOT EXISTS action_queue (
  id              text PRIMARY KEY,
  case_id         text NOT NULL REFERENCES claim_case(id) ON DELETE CASCADE,
  action_type     text NOT NULL,              -- compose_letter | send_email | file_dispute | open_calendar | request_signature
  approval_level  text NOT NULL,              -- auto | user_required
  scheduled_at    timestamptz,
  executed_at     timestamptz,
  state           text NOT NULL DEFAULT 'pending',  -- pending | approved | executed | denied | failed
  payload_jsonb   jsonb NOT NULL DEFAULT '{}'::jsonb,
  result_jsonb    jsonb,
  created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS action_queue_case_idx ON action_queue (case_id, created_at);
CREATE INDEX IF NOT EXISTS action_queue_state_idx ON action_queue (state, scheduled_at);

COMMIT;