Dataset schema

The exact columns of the public dataset (the SQLite / Parquet you download), generated from the live build so it never drifts. Dates are ISO 8601 (YYYY-MM-DD); columns ending in _at are UTC timestamps. Everything joins back to municipalities.id via muni_id, or via meetings.id through meeting_id.

Download schema.md · back to the dataset

municipalities — 5,790 rows

PRIMARY KEY id. Every other table joins back here via muni_id, or via meetings through meeting_id.

meetings — 1,724,628 rows

PK id · FK muni_id → municipalities.id. One row per meeting.

briefs — 701,008 rows

PK/FK meeting_id → meetings.id. AI plain-English agenda summary.

decisions — 243,699 rows

PK/FK meeting_id → meetings.id. What passed/failed, from minutes.

briefs_i18n — 160,774 rows

FK meeting_id → meetings.id. lang is 'es' or 'fr'.

roundups — 4,469 rows

FK muni_id → municipalities.id. Weekly recap (markdown).

transcripts — 26,543 rows

PK/FK meeting_id → meetings.id. Whisper/caption transcript of meeting video.

census_demographics — 2,565 rows

FK muni_id → municipalities.id. US Census ACS 5-year.

meeting_texts — 807,915 rows

PK/FK meeting_id → meetings.id. Raw extracted source text (NOT AI).

campaign_finance — 1,397,866 rows

FK muni_id → municipalities.id. NetFile local donor transactions (predominantly California).

canada_demographics — 454 rows

FK muni_id → municipalities.id. StatCan.

home_value_trend — 2,336 rows

FK muni_id → municipalities.id. Zillow ZHVI home-value trend.

federal_awards — 21,446 rows

FK muni_id → municipalities.id. USAspending federal grants/contracts.

civic_records — 5,697 rows

FK muni_id → municipalities.id. Building permits & business licenses from municipal open-data feeds.

decision_geo — 1,907 rows

FK meeting_id → meetings.id. Per-decision address geocode + building + flood zone.

decision_echo — 170 rows

FK meeting_id → meetings.id. EPA ECHO facilities near a decision address.

decision_nonprofit — 76 rows

FK meeting_id → meetings.id. IRS nonprofit tied to a decision.

matters — 15,319 rows

officials — 237 rows

member_votes — 4,562,764 rows

cf_transactions — 2,221,015 rows

school_stats — 125 rows

gov_finances — 2,001 rows

building_permits — 2,887 rows

election_results — 40,054 rows

ballot_measures — 1,468 rows

official_bridges — 318 rows

officials_tenure — 215 rows

council_districts — 176 rows

Example queries

-- Every decision mentioning a seven-figure dollar amount, newest first
SELECT m.meeting_date, mu.name, mu.state, d.headline
FROM decisions d JOIN meetings m ON m.id = d.meeting_id
JOIN municipalities mu ON mu.id = m.muni_id
WHERE d.decisions LIKE '%,000,000%' ORDER BY m.meeting_date DESC;

-- Upcoming meetings in one state, with the plain-English brief
SELECT mu.name, m.body_name, m.meeting_date, b.headline
FROM meetings m JOIN municipalities mu ON mu.id = m.muni_id
LEFT JOIN briefs b ON b.meeting_id = m.id
WHERE mu.state = 'California' AND m.meeting_date >= date('now')
ORDER BY m.meeting_date;