返回资讯中心
外部精选
软件工程
#开发者实时流

Follow the Money Database – See who funds your congressperson

Free appendix from The Briefing: Tom Emmer (R-MN-06). Download as Markdown

Hacker News 最新cpobuda2026/10/18 · 2026/10/1830 分钟阅读

以下正文同步自 Hacker News 最新,版权归原站所有,已转换为易读排版。

Free appendix from The Briefing: Tom Emmer (R-MN-06). Download as Markdown

This document is a set of directions for building a database of United States congressional campaign finance data from public federal sources. Every dollar raised, every dollar spent, every donor, every recipient — all of it is public record, published by the Federal Election Commission.

You can give these directions to any AI assistant and it will generate a working implementation in your preferred language. Or you can build it yourself. The data belongs to you. It always did.

Coverage varies by file type: individual contributions (indiv), committee contributions (pas2), transfers (oth), and committee/candidate master files go back to the 1980 cycle. Operating expenditures (oppexp) begin at the 2004 cycle. Candidate-committee linkage (ccl) begins at the 2000 cycle. All are updated continuously by the FEC.

All data is published by the Federal Election Commission at:

https://www.fec.gov/data/browse-data/?tab=bulk-data

Each file type has one ZIP per 2-year election cycle (e.g., indiv24.zip for the 2024 cycle). Download all cycles you want to cover.

The FEC publishes column header files (look for *_header_file.csv) in a data_dictionaries/ folder alongside the cycle files. https://www.fec.gov/files/bulk-downloads/ redirects to the underlying storage bucket, which you can browse directly, or you can link straight to an individual header file (e.g., indiv_header_file.csv). Note that the directory URL itself is not browsable in a browser — enumerate the bucket listing or fetch known file names directly.

Files are pipe-delimited (|), no quoting, UTF-8 with occasional encoding errors.

Design your tables with these columns. Adapt types to your database engine. You may optionally add a surrogate id INTEGER PRIMARY KEY AUTOINCREMENT column to the transaction tables for internal row identification — sub_id remains the FEC-assigned deduplication key regardless.

Columns correspond one-to-one with cm_header_file.csv, plus a derived cycle column.

cycle INTEGER NOT NULL -- derived: the election-cycle ZIP this row came from cmte_id TEXT NOT NULL -- CMTE_ID; e.g., C00545749 cmte_nm TEXT -- committee name tres_nm TEXT -- treasurer name cmte_st1 TEXT -- street address line 1 cmte_st2 TEXT -- street address line 2 cmte_city TEXT cmte_st TEXT -- state cmte_zip TEXT cmte_dsgn TEXT -- designation (A/B/D/J/P/U) cmte_tp TEXT -- committee type (H/N/P/Q/S/etc.) cmte_pty_affiliation TEXT -- party cmte_filing_freq TEXT -- filing frequency (Q/M/etc.) org_tp TEXT -- organization type connected_org_nm TEXT -- connected organization cand_id TEXT -- linked candidate PRIMARY KEY (cmte_id, cycle)

candidates

Columns correspond one-to-one with cn_header_file.csv, plus a derived cycle column.

cycle INTEGER NOT NULL -- derived: the election-cycle ZIP this row came from cand_id TEXT NOT NULL -- CAND_ID; e.g., H4MN06087 cand_name TEXT cand_pty_affiliation TEXT -- party cand_election_yr INTEGER -- candidate's election year cand_office_st TEXT -- office state cand_office TEXT -- H=House, S=Senate, P=President cand_office_district TEXT -- district cand_ici TEXT -- incumbent/challenger/open cand_status TEXT cand_pcc TEXT -- principal campaign committee ID cand_st1 TEXT -- street address line 1 cand_st2 TEXT -- street address line 2 cand_city TEXT cand_st TEXT -- state cand_zip TEXT PRIMARY KEY (cand_id, cycle)

candidate_committees

Columns correspond one-to-one with ccl_header_file.csv. No cycle column: fec_election_year already scopes the row.

cand_id TEXT NOT NULL cand_election_yr INTEGER fec_election_year INTEGER -- not the same as cycle cmte_id TEXT NOT NULL cmte_tp TEXT cmte_dsgn TEXT linkage_id INTEGER -- for dedup PRIMARY KEY (cand_id, cmte_id, fec_election_year)

contributions_individual

Columns correspond one-to-one with indiv_header_file.csv, in header order, plus derived columns (cycle, zip5, zip4) and a normalized transaction_date.

cycle INTEGER NOT NULL -- derived cmte_id TEXT NOT NULL -- CMTE_ID; receiving committee amndt_ind TEXT -- N=new, A=amendment, T=termination rpt_tp TEXT -- report type: YE, Q1, M7, 30G, etc. transaction_pgi TEXT -- primary/general indicator image_num TEXT -- FEC image number transaction_tp TEXT -- transaction type: 15, 15E, 24T, etc. entity_tp TEXT -- IND, COM, ORG, etc. name TEXT city TEXT state TEXT zip5 TEXT -- derived: first 5 digits of ZIP_CODE zip4 TEXT -- derived: digits 6-9 of ZIP_CODE employer TEXT occupation TEXT transaction_date TEXT -- normalized to YYYY-MM-DD (source: MMDDYYYY) amount REAL NOT NULL DEFAULT 0 other_id TEXT -- linked committee (earmarks) tran_id TEXT -- FEC transaction ID file_num INTEGER -- FEC filing number memo_cd TEXT -- X=memo/sub-item memo_text TEXT sub_id INTEGER NOT NULL UNIQUE -- FEC record ID

contributions_committee

Columns correspond one-to-one with pas2_header_file.csv — same layout as indiv with cand_id inserted after other_id, plus derived columns and a normalized transaction_date.

cycle INTEGER NOT NULL -- derived cmte_id TEXT NOT NULL -- CMTE_ID; receiving committee amndt_ind TEXT -- N=new, A=amendment, T=termination rpt_tp TEXT -- report type: YE, Q1, M7, 30G, etc. transaction_pgi TEXT -- primary/general indicator image_num TEXT -- FEC image number transaction_tp TEXT -- transaction type: 15, 15E, 24T, etc. entity_tp TEXT -- IND, COM, ORG, etc. name TEXT city TEXT state TEXT zip5 TEXT -- derived: first 5 digits of ZIP_CODE zip4 TEXT -- derived: digits 6-9 of ZIP_CODE employer TEXT occupation TEXT transaction_date TEXT -- normalized to YYYY-MM-DD (source: MMDDYYYY) amount REAL NOT NULL DEFAULT 0 other_id TEXT -- linked committee (earmarks) cand_id TEXT -- CAND_ID; receiving candidate tran_id TEXT -- FEC transaction ID file_num INTEGER -- FEC filing number memo_cd TEXT -- X=memo/sub-item memo_text TEXT sub_id INTEGER NOT NULL UNIQUE -- FEC record ID

transfers

Columns correspond one-to-one with oth_header_file.csv — identical layout to indiv, plus derived columns and a normalized transaction_date.

cycle INTEGER NOT NULL -- derived cmte_id TEXT NOT NULL -- CMTE_ID; receiving committee amndt_ind TEXT -- N=new, A=amendment, T=termination rpt_tp TEXT -- report type: YE, Q1, M7, 30G, etc. transaction_pgi TEXT -- primary/general indicator image_num TEXT -- FEC image number transaction_tp TEXT -- transaction type: 15, 15E, 24T, etc. entity_tp TEXT -- IND, COM, ORG, etc. name TEXT city TEXT state TEXT zip5 TEXT -- derived: first 5 digits of ZIP_CODE zip4 TEXT -- derived: digits 6-9 of ZIP_CODE employer TEXT occupation TEXT transaction_date TEXT -- normalized to YYYY-MM-DD (source: MMDDYYYY) amount REAL NOT NULL DEFAULT 0 other_id TEXT -- linked committee (earmarks) tran_id TEXT -- FEC transaction ID file_num INTEGER -- FEC filing number memo_cd TEXT -- X=memo/sub-item memo_text TEXT sub_id INTEGER NOT NULL UNIQUE -- FEC record ID

disbursements

The disbursements table has a different structure from the other three transaction tables. Columns correspond one-to-one with oppexp_header_file.csv, in header order, plus derived columns and a normalized transaction_date.

cycle INTEGER NOT NULL -- derived cmte_id TEXT NOT NULL -- CMTE_ID amndt_ind TEXT -- N=new, A=amendment, T=termination rpt_yr INTEGER -- calendar year of report rpt_tp TEXT -- report type: YE, Q1, M7, 30G, etc. image_num TEXT -- FEC image number line_num TEXT form_tp_cd TEXT sched_tp_cd TEXT -- SB, SE, etc. name TEXT -- payee name city TEXT state TEXT zip5 TEXT -- derived: first 5 digits of ZIP_CODE zip4 TEXT -- derived: digits 6-9 of ZIP_CODE transaction_date TEXT -- normalized to YYYY-MM-DD (source: MM/DD/YYYY) amount REAL NOT NULL DEFAULT 0 transaction_pgi TEXT -- primary/general indicator purpose TEXT -- description of expenditure category TEXT category_desc TEXT memo_cd TEXT -- X=memo/sub-item memo_text TEXT entity_tp TEXT sub_id INTEGER NOT NULL UNIQUE file_num INTEGER -- FEC filing number tran_id TEXT -- FEC transaction ID back_ref_tran_id TEXT -- back-reference transaction ID

Note: disbursements (the oppexp file) is the only table with rpt_yr. This matters for amendment handling (see below). It also lacks several columns present in the other tables (employer, occupation, other_id, transaction_tp), and its source file uses MM/DD/YYYY date format instead of MMDDYYYY.

Create all tables without secondary indexes. Indexes slow bulk loading dramatically. You will add them after ingestion.

Include a UNIQUE constraint on sub_id for all four transaction tables. This is your primary deduplication key — the FEC assigns a globally unique identifier to every transaction record.

These are small relative to the transaction tables (thousands to tens of thousands of rows per cycle). Load cm, cn, and ccl files first. Use INSERT OR REPLACE keyed on the composite natural key ((cmte_id, cycle) for committees, (cand_id, cycle) for candidates) since the same entity appears across multiple cycle files with potentially different metadata.

For each of the four transaction types (indiv, pas2, oth, oppexp):

CRITICAL — by_date duplication (indiv files only): Starting with the 2012 cycle, FEC indiv ZIP files contain BOTH a complete data file (itcont.txt) AND the same data split into date-range chunks in a by_date/ subdirectory. If you read all files in the ZIP, every record is processed twice. The UNIQUE constraint on sub_id prevents data corruption, but you waste half your processing time. Only read the main file (itcont.txt). Skip any files in by_date/ subdirectories. The other three file types (pas2, oth, oppexp) do not have this problem — their ZIPs contain only the main data file.

After all data is loaded, create indexes on frequently queried columns:

On a 280-million-row table, each index takes 10-30 minutes. Plan accordingly.

This section describes problems that are not obvious from the FEC documentation. If you skip these, your numbers will be wrong.

The problem: When a committee amends a financial report, the FEC publishes new records alongside the originals. Both the original (amndt_ind = 'N') and the amendment (amndt_ind = 'A') remain in the bulk data. If you sum all records naively, you count every amended transaction twice.

Approximately 37-54% of all records across the four tables are amendments. This is not a minor issue.

Why you cannot simply delete originals: The intuitive approach — delete N records wherever A records exist for the same committee, cycle, and report type — is wrong. Report types like YE (year-end), Q1-Q3 (quarterly), and M2-M12 (monthly) repeat within a 2-year election cycle. A committee files YE twice per cycle: once for each calendar year. The grouping (cmte_id, cycle, rpt_tp) collapses two distinct filing periods into one bucket. If year one has an amendment and year two has an original, deleting the original destroys valid data from a different report period.

We confirmed this empirically: in the transfers table, over 98% of groupings containing both N and A records are cross-period collisions, not true amendments.

Why you cannot use file_num ordering: FEC file_num (filing number) is higher for more recent filings. But N records frequently have higher file_nums than A records within the same grouping — because they belong to a later report period, not because they are a later version of the same report. In the transfers table, 50% of mixed N/A groupings have N records with higher file_nums than A records — because those N records belong to a later report period, not because they are later corrections.

The correct approach: Keep all records. Filter at query time:

The problem: When a donor gives through a conduit like WinRed or ActBlue, the contribution appears twice in the data:

If you sum both, you double-count every earmarked dollar.

The wrong filter: memo_cd = 'X' marks memo/sub-item records, but it covers only 0.5% of individual contribution records. Meanwhile, 48% of

正文由 探How 从来源站点 RSS 同步,内容未经改写;遇到排版缺失或需要图片、视频时请以原文为准。

打开原文
去社区讨论 →