`db/migrations/0005_rights_rules.sql`

25 lines
-- 0005_rights_rules.sql — versioned policy/rule corpus

BEGIN;

CREATE TABLE IF NOT EXISTS rights_rule_snapshot (
  id              text PRIMARY KEY,
  domain          text NOT NULL,              -- 'card_dispute' | 'warranty' | 'airline' | 'returns' | 'medical' | 'recall'
  jurisdiction    text NOT NULL,              -- 'US' | 'US-CA' | 'EU' | 'UK' | 'CA' | 'BR' | 'INTL' (ICAO)
  citation        text NOT NULL,              -- '12 CFR §1026.12(c)' | 'FTC §700' | 'DOT 14 CFR §259.5' | 'EU 261/2004' | etc.
  source_url      text,
  effective_date  date,
  superseded_at   timestamptz,                -- NULL while active
  short_title     text NOT NULL,              -- one-line label
  applies_when    jsonb NOT NULL DEFAULT '{}'::jsonb,  -- selector: { reason_codes: [...], min_amount: N, route_through: 'merchant', ... }
  rule_summary    text NOT NULL,              -- one-paragraph plain-English statement
  citation_block  text,                       -- block to drop into a letter ("Per 12 CFR §1026.12(c)…")
  created_at      timestamptz NOT NULL DEFAULT now(),
  metadata_jsonb  jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX IF NOT EXISTS rights_rule_domain_idx ON rights_rule_snapshot (domain, jurisdiction) WHERE superseded_at IS NULL;
CREATE INDEX IF NOT EXISTS rights_rule_applies_idx ON rights_rule_snapshot USING gin (applies_when);
CREATE UNIQUE INDEX IF NOT EXISTS rights_rule_citation_active ON rights_rule_snapshot (citation) WHERE superseded_at IS NULL;

COMMIT;