libfec

libfec SQL Reference ​

WARNING

This documentation is incomplete!

The libfec export command can export FEC filings into a SQLite database. All header, cover, and itemizations records from a FEC filing are inserted into various SQLite tables, allowing you to write SQL to extract out the exact information you need.

bash
libfec export --committee=

The SQL tables that are created are based on

libfec_filings ​

See libfec_filings SQL schema
sql
CREATE TABLE libfec_filings(
  /**
   * 
   * 
   */

  --- Unique numeric identifier for this filing, assigned by the FEC, ex 1884420
  filing_id TEXT PRIMARY KEY NOT NULL,
  
  --- Version of the FEC filing format, ex '8.4'
  fec_version TEXT NOT NULL,
  
  --- Name of the software that produced this filing, ex 'NetFile'
  software_name TEXT NOT NULL,

  --- Version of the software that produced this filing, ex '2022451'
  software_version TEXT NOT NULL,

  --- If this filing is an amendment, the report_id of the original filing, otherwise null. ex 1884419
  report_id INTEGER,

  --- Sequential number of amendments
  report_number TEXT,

  --- Any header comments provided by the filer
  comment TEXT,

  --- Form type of the cover record, ex 'F3'
  cover_record_form TEXT NOT NULL,
  cover_record_form_amendment_indicator TEXT,

  filer_id TEXT NOT NULL,
  filer_name TEXT NOT NULL,
  report_code TEXT,
  coverage_from_date TEXT,
  coverage_through_date TEXT
)

The libfec_filings table contains a single row for every FEC filing export. That rows contains the ("header record") and "cover record" for a given filing. For example:

bash
libfec export FEC-1848680  FEC-1870171 -o sanchez.db

The libfec_filings table inside of sanchez.db will have 2 rows in the table:

sql
select 
  filing_id, 
  cover_record_form, 
  filer_id, 
  filer_name, 
  report_code
from libfec_filings;
┌───────────┬───────────────────┬───────────┬────────────────────┬─────────────┐
│ filing_id │ cover_record_form │ filer_id  │ filer_name         │ report_code │
├───────────┼───────────────────┼───────────┼────────────────────┼─────────────┤
│ 1870171   │ F3                │ C00384057 │ Stand With Sanchez │ YE          │
├───────────┼───────────────────┼───────────┼────────────────────┼─────────────┤
│ 1848680   │ F1                │ C00384057 │ Stand With Sanchez │             │
└───────────┴───────────────────┴───────────┴────────────────────┴─────────────┘

The first filing, FEC-1870171 , is the FEC Form 3 "Year-End" financial report filed by Stand with Sanchez , the principal campaign committee for Linda Sanchez (CA-38). The second filing, FEC-1848680 , is the FEC Form 1 "Statement of Organization" that the Sanchez campaign submitted in the 2024 election cycle.

The libfec_filings table is the core table for all the other exported itemizations. Every other table in the sanchez.db database (libfec_F1, libfec_schedule_a, etc.) all have foreign keys that point to the libfec_filings.filing_id primary key column.

Cover Tables ​

"Cover records" refer to the second line in a .fec file, which specifies which form the candidate or committee submitted. When exporting to a SQLite database, A every form type will have it's own table.

In the sanchez.db example from above, there are two cover record tables: libfec_F1 and libfec_F3.

T

sql