`db/migrations/0015_unclaimed_property.sql`

33 lines
-- 0015_unclaimed_property.sql
-- Tracks state-held unclaimed property found for the user (e.g. California SCO
-- claimit.ca.gov). Holds ONLY public property data + a status the user drives.
-- Deliberately stores NO SSN / identity data — filing happens on the state
-- site, never here (AGENTS.md: drafts only, no send).

CREATE TABLE IF NOT EXISTS unclaimed_property (
  id             text PRIMARY KEY,
  user_id        text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
  jurisdiction   text NOT NULL DEFAULT 'US-CA',      -- state program
  property_id    text NOT NULL,                      -- the state's property id
  holder_name    text,                               -- who reported it (bank, PayPal, etc.)
  owner_name     text,                               -- name as listed by the holder
  co_owner       text,
  address        text,
  city           text,
  state          text DEFAULT 'CA',
  zip            text,
  amount_cents   integer,                            -- null when undisclosed ("OVER $100")
  amount_display text,                               -- raw label, e.g. '$66.50' or 'OVER $100'
  property_type  text,                               -- NAUPA type, e.g. 'PREMIUM REFUNDS'
  source         text DEFAULT 'claimit.ca.gov',
  status         text NOT NULL DEFAULT 'found',      -- found | staged | filed | paid | not_mine
  claim_id       text,                               -- the state's Claim ID, once filed
  notes          text,
  found_at       timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now(),
  UNIQUE (user_id, property_id)
);

CREATE INDEX IF NOT EXISTS unclaimed_property_user_status_idx
  ON unclaimed_property (user_id, status);