Schema for tn_state_public

CREATE TABLE public_contributions (
  "canon_id" INTEGER,  -- donor id; one id per donor across name variants
  "donor_name" TEXT,  -- the DONOR (who gave)
  "employer" TEXT,  -- the donor's employer
  "occupation" TEXT,  -- the donor's occupation
  "is_organization" INTEGER,
  "entity_type" TEXT,
  "entity_subtype" TEXT,
  "city" TEXT,  -- the DONOR's city, not the candidate's
  "state" TEXT,  -- the DONOR's state, not the candidate's
  "zip" TEXT,  -- the DONOR's ZIP, not the candidate's
  "amount" REAL,  -- dollars
  "date" TEXT,
  "is_inkind" INTEGER,
  "designated_election_phase" TEXT,
  "campaign_id" INTEGER,  -- the RECIPIENT campaign
  "reporting_period" TEXT,
  "party" TEXT,  -- the recipient candidate's party
  "election_cycle_year" INTEGER,  -- election year of the recipient campaign
  "candidate_name" TEXT,  -- the RECIPIENT candidate
  "office_name" TEXT,  -- the office the recipient ran for, e.g. 'Tennessee PAC', 'Governor', 'Tennessee Senate, District 27', 'Tennessee Senate, District 20'; names include the district, so match a whole chamber with LIKE 'Tennessee Senate%', never =
  "floor_cycle_key" TEXT
);
CREATE INDEX idx_pc_pair ON public_contributions(canon_id, campaign_id);
CREATE INDEX idx_pc_campaign ON public_contributions(campaign_id);
CREATE TABLE "expenditures" (
  "candidate" TEXT,
  "office" TEXT,
  "party" TEXT,
  "status" TEXT,
  "presentation_term" INTEGER,
  "payee" TEXT,
  "payee_city" TEXT,
  "payee_state" TEXT,
  "payee_zip" TEXT,
  "purpose" TEXT,
  "amount" REAL,
  "date" TEXT
);
CREATE TABLE "loans" (
  "candidate" TEXT,
  "office" TEXT,
  "party" TEXT,
  "status" TEXT,
  "presentation_term" INTEGER,
  "lender" TEXT,
  "amount" REAL,
  "amount_this_cycle" REAL,
  "date_received" TEXT,
  "outstanding_balance" REAL,
  "amount_repaid" REAL,
  "is_self_loan" TEXT
);
CREATE TABLE "candidate_summaries" (
  "candidate" TEXT,
  "office" TEXT,
  "party" TEXT,
  "status" TEXT,
  "presentation_term" INTEGER,
  "reported_receipts" REAL,
  "receipts_basis" TEXT,
  "receipts_hold" TEXT,
  "total_contributions" REAL,
  "total_refunds" REAL,
  "inkind_received" REAL,
  "total_self_contributions" REAL,
  "total_loans" REAL,
  "loans_received" REAL,
  "total_unitemized" REAL,
  "total_expenditures" REAL,
  "total_expenditure_refunds" TEXT,
  "total_unitemized_spent" REAL,
  "total_spent" REAL,
  "cash_on_hand" REAL,
  "contributor_count" INTEGER,
  "filing_count" INTEGER,
  "is_incumbent" TEXT
);
CREATE TABLE "filings" (
  "candidate" TEXT,
  "office" TEXT,
  "party" TEXT,
  "presentation_term" INTEGER,
  "year" INTEGER,
  "reporting_period" TEXT,
  "report_type" TEXT,
  "coverage_start" TEXT,
  "coverage_end" TEXT,
  "filing_date" TEXT,
  "review_status" TEXT,
  "published" INTEGER
);
CREATE TABLE candidate_contributions (canon_id INTEGER, campaign_id INTEGER, amount REAL, is_self INTEGER, floor_cycle_key TEXT);
CREATE INDEX idx_cc_pair ON candidate_contributions(canon_id, campaign_id);
CREATE TABLE export_meta (store TEXT, generated_at TEXT, privacy_threshold INTEGER, rows INTEGER)