Follow the Money Database – See who funds your congressperson

A practical how-to for building a relational database from Federal Election Commission bulk data that captures every reported federal campaign dollar: individual donations, committee-to-candidate contributions, committee transfers, operating disbursements, committee and candidate master records, and candidate-committee linkages. It points to the FEC bulk-download endpoint and enumerates the file prefixes to fetch per two‑year cycle (cm, cn, ccl, indiv, pas2, oth, oppexp), notes coverage by cycle (most files back to 1980; oppexp from 2004; ccl from 2000), and describes file format quirks (pipe-delimited, UTF‑8 with occasional encoding errors, header files in a data_dictionaries folder, and the storage-bucket listing behavior).

Concrete schema and ingestion guidance specify table columns mapped to each header file plus derived fields (cycle, zip5/zip4, normalized transaction_date), primary keys, and the FEC-assigned sub_id as the unique deduplication key. Disbursements (oppexp) use a different layout (includes rpt_yr and omits employer/occupation/other_id) and a distinct date format. Recommended ingestion: create tables without secondary indexes, enforce UNIQUE(sub_id) on transaction tables, load cm/cn/ccl first using INSERT OR REPLACE keyed on natural composites, then bulk-load transaction cycles, and add indexes only after bulk loading. An optional surrogate INTEGER primary key is suggested for internal use.