`db/schema.sql`

122 lines
-- ca_donations — California public-records donations data product
-- Two record families: political contributions (donor-level, above the $100 CA
-- itemization threshold) and charitable records (org + grant, NOT person-level).
-- See ~/.claude/plans/use-high-codex-deep-lovely-sparrow.md for the source map.

-- ---------------------------------------------------------------------------
-- provenance: every row traces to a source + ingest run
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sources (
  id            SERIAL PRIMARY KEY,
  slug          TEXT UNIQUE NOT NULL,      -- e.g. 'calaccess', 'fec_bulk', 'propublica', 'irs_990', 'ca_ag'
  name          TEXT NOT NULL,
  url           TEXT,
  jurisdiction  TEXT,                       -- state | federal | local
  family        TEXT NOT NULL,              -- political | charitable
  granularity   TEXT,                       -- donor | org | grant | aggregate
  access        TEXT,                       -- bulk | api | csv | scrape
  notes         TEXT
);

CREATE TABLE IF NOT EXISTS ingest_runs (
  id          SERIAL PRIMARY KEY,
  source_slug TEXT NOT NULL,
  started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  finished_at TIMESTAMPTZ,
  rows_in     INTEGER,
  rows_upsert INTEGER,
  status      TEXT NOT NULL DEFAULT 'running',  -- running | ok | error
  detail      TEXT
);

-- ---------------------------------------------------------------------------
-- POLITICAL — donor-level contributions (CAL-ACCESS RCPT + Forms 461/496/497,
-- FEC itcont filtered to CA, local NetFile/Socrata). Above the $100 CA
-- itemization threshold; sub-threshold stays aggregate (not stored here).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS political_contributions (
  id                BIGSERIAL PRIMARY KEY,
  source_slug       TEXT NOT NULL,
  external_id       TEXT,                    -- source's own tran/record id (for idempotent upsert)
  donor_name        TEXT,
  donor_employer    TEXT,
  donor_occupation  TEXT,
  donor_city        TEXT,
  donor_state       TEXT,
  donor_zip         TEXT,
  amount            NUMERIC(14,2),
  contribution_date DATE,
  recipient_name    TEXT,                    -- committee / candidate / PAC
  recipient_id      TEXT,
  office            TEXT,
  jurisdiction      TEXT NOT NULL,           -- state | federal | local
  form              TEXT,                    -- 460A | 461 | 496 | 497 | FEC_A | local
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (source_slug, external_id)
);
CREATE INDEX IF NOT EXISTS pc_donor_name_idx  ON political_contributions USING gin (to_tsvector('simple', coalesce(donor_name,'')));
CREATE INDEX IF NOT EXISTS pc_recipient_idx   ON political_contributions (recipient_name);
CREATE INDEX IF NOT EXISTS pc_employer_idx    ON political_contributions (donor_employer);
CREATE INDEX IF NOT EXISTS pc_date_idx        ON political_contributions (contribution_date);
CREATE INDEX IF NOT EXISTS pc_juris_idx       ON political_contributions (jurisdiction);

-- ---------------------------------------------------------------------------
-- CHARITABLE — organizations (registration/status) + grant lines
-- (990-PF Part XV outbound, 990 Schedule I / F, 990-PF Sched B foundation
-- contributors where public). NOT individual public-charity donors.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS charitable_orgs (
  id            BIGSERIAL PRIMARY KEY,
  ein           TEXT UNIQUE,
  name          TEXT NOT NULL,
  city          TEXT,
  state         TEXT,
  ntee_code     TEXT,
  subsection    TEXT,                        -- e.g. 501(c)(3), 990-PF private foundation
  ca_ag_status  TEXT,                        -- may-operate | may-not-operate | undetermined | revoked | (null)
  source_slug   TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS co_name_idx   ON charitable_orgs USING gin (to_tsvector('simple', coalesce(name,'')));
CREATE INDEX IF NOT EXISTS co_status_idx ON charitable_orgs (ca_ag_status);
CREATE INDEX IF NOT EXISTS co_ntee_idx   ON charitable_orgs (ntee_code);

CREATE TABLE IF NOT EXISTS charitable_grants (
  id             BIGSERIAL PRIMARY KEY,
  source_slug    TEXT NOT NULL,
  external_id    TEXT,
  grantor_ein    TEXT,
  grantor_name   TEXT,
  grantee_name   TEXT,
  grantee_city   TEXT,
  grantee_state  TEXT,
  amount         NUMERIC(14,2),
  purpose        TEXT,
  tax_year       INTEGER,
  grant_type     TEXT,                       -- 990pf_partxv | 990_sched_i | 990_sched_f | 990pf_schedb
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (source_slug, external_id)
);
CREATE INDEX IF NOT EXISTS cg_grantor_idx ON charitable_grants (grantor_ein);
CREATE INDEX IF NOT EXISTS cg_grantee_idx ON charitable_grants USING gin (to_tsvector('simple', coalesce(grantee_name,'')));
CREATE INDEX IF NOT EXISTS cg_year_idx    ON charitable_grants (tax_year);

-- ---------------------------------------------------------------------------
-- POLITICAL AGG — materialized k-anon rollup of political_contributions.
-- Precomputed so /api/political/agg reads an indexed table (sub-second) instead
-- of a 15.7M-row full-table GROUP BY (~174s, guarded to 503). refresh-political-agg.mjs
-- rebuilds it atomically on every ingest. The k-anon floor is BAKED IN: the
-- populate step enforces HAVING count(distinct donor_name) >= 5, so this table
-- can NEVER contain a group that singles out fewer than 5 donors.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS political_agg (
  dimension          TEXT NOT NULL,           -- recipient | employer | city | jurisdiction
  group_key          TEXT NOT NULL,
  total_amount       NUMERIC(14,2),
  contribution_count BIGINT,
  distinct_donors    INT,
  refreshed_at       TIMESTAMPTZ,
  UNIQUE (dimension, group_key)
);
CREATE INDEX IF NOT EXISTS pa_dim_amount_idx ON political_agg (dimension, total_amount DESC);