← back to AbramsOS
db/migrations/0016_settlement_claims.sql
56 lines
-- 0016_settlement_claims.sql — class-action settlement claim tracker + openclaw fill queue.
--
-- Source: the "Mode Class Actions" daily newsletter (Dan N. <daniel@mail.modemobile.com>),
-- which surfaces class-action settlements with payouts + deadlines + claim URLs. This module
-- ingests those emails, dedupes to distinct settlements, tracks Steve's eligibility decision,
-- and stages an openclaw "fill (not submit)" job for the ones he confirms he qualifies for.
--
-- HARD RULE (encoded in fill_state + the app): the pipeline never fabricates eligibility and
-- never clicks the final penalty-of-perjury SUBMIT. fill_state stops at 'prefilled_awaiting_submit'.
BEGIN;
CREATE TABLE IF NOT EXISTS settlement_claim (
id text PRIMARY KEY,
user_id text NOT NULL REFERENCES user_account(id) ON DELETE CASCADE,
slug text NOT NULL, -- dedupe key from the mode URL path
name text NOT NULL, -- settlement name, e.g. "PHH Mortgage Insurance Kickbacks"
mode_url text, -- modeclassactionsdaily.com/<slug> landing page
admin_url text, -- resolved official claim-administrator site (filled later)
payout_text text, -- verbatim payout blurb, e.g. "$875 per eligible loan"
payout_max_cents bigint, -- best-effort parsed ceiling for ranking (nullable)
deadline date, -- claim filing deadline
proof_required boolean, -- true if receipts/docs needed; false if "no proof required"
no_claim_required boolean DEFAULT false, -- true = automatic payment, nothing to file
eligibility_question text, -- the "are you a class member?" test, in plain English
eligibility_state text NOT NULL DEFAULT 'unreviewed',
-- unreviewed | eligible | not_eligible | maybe (Steve's decision — gates any fill)
fill_state text NOT NULL DEFAULT 'none',
-- none | queued | prefilled_awaiting_submit | submitted | skipped | expired
category text, -- data_breach | financial | product | employment | other
source_email_id text, -- Gmail message id it was extracted from
source_email_date timestamptz,
first_seen_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
notes text,
raw_block text, -- the raw newsletter text block, for audit
UNIQUE (user_id, slug)
);
CREATE INDEX IF NOT EXISTS settlement_claim_user_idx ON settlement_claim (user_id, deadline);
CREATE INDEX IF NOT EXISTS settlement_claim_state_idx ON settlement_claim (user_id, eligibility_state, fill_state);
-- One row per openclaw fill attempt (append-only audit of what the browser agent did).
CREATE TABLE IF NOT EXISTS settlement_fill_job (
id text PRIMARY KEY,
claim_id text NOT NULL REFERENCES settlement_claim(id) ON DELETE CASCADE,
user_id text NOT NULL,
state text NOT NULL DEFAULT 'staged', -- staged | filling | prefilled_awaiting_submit | submitted | failed
brief_jsonb jsonb NOT NULL DEFAULT '{}'::jsonb, -- the openclaw job brief (URL, fields, guardrails)
result_jsonb jsonb, -- screenshots/paths/notes from the run
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS settlement_fill_job_claim_idx ON settlement_fill_job (claim_id, created_at);
COMMIT;