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 — 8,270 rows

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

meetings — 2,114,220 rows

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

briefs — 1,750,373 rows

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

decisions — 765,895 rows

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

briefs_i18n — 170,856 rows

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

roundups — 10,525 rows

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

transcripts — 48,402 rows

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

census_demographics — 5,520 rows

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

meeting_texts — 1,883,956 rows

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

campaign_finance — 3,298,338 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 — 4,081 rows

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

federal_awards — 36,565 rows

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

civic_records — 213,049 rows

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

decision_geo — 25,887 rows

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

decision_echo — 1,295 rows

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

decision_nonprofit — 949 rows

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

matters — 33,726 rows

officials — 207,457 rows

member_votes — 8,994,128 rows

people — 59,675 rows

people_aliases — 70,867 rows

person_vote_stats — 59,675 rows

cf_transactions — 46,372,205 rows

gov_payments — 1,474,857 rows

dot_projects — 44,747 rows

infrastructure_projects — 32,738 rows

sba_loans — 436,303 rows

business_patterns — 8,842 rows

school_stats — 965 rows

literacy — 4,457 rows

staar_results — 53,631 rows

school_finance — 120,160 rows

crime_stats — 278 rows

crime_incidents — 9,064,148 rows

gov_finances — 2,409 rows

building_permits — 2,887 rows

election_results — 74,492 rows

official_elections — 3,176 rows

ballot_measures — 1,579 rows

ma_election_results — 87,307 rows

official_bridges — 727 rows

officials_tenure — 215 rows

council_districts — 176 rows

food_inspections — 19,092,984 rows

gov_contracts — 1,767,326 rows

public_salaries — 3,539,824 rows

service_requests_rollup — 6,898 rows

service_requests_recent — 100 rows

bps_permits — 4,280 rows

fiscal_health — 2,409 rows

pension_state — 96 rows

business_formations — 3,105,341 rows

property_sales — 38,935 rows

lobbying_registrations — 5,217,388 rows

official_disclosures — 1,128,215 rows

lobbying_expenditures — 687,821 rows

muni_financials — 4,751 rows

local_sales_tax — 51,997 rows

muni_debt — 2,290 rows

fiscal_stress_official — 49 rows

nonprofits — 50 rows

state_bills — 1,456,284 rows

state_votes — 1,181,341 rows

state_legislators — 21,605 rows

air_quality — 231 rows

water_violations — 32,661 rows

college_stats — 6,119 rows

warn_notices — 2,827 rows

professional_licenses — 7,873,634 rows

police_stops_rollup — 2,384 rows

assisted_contracts — 24,308 rows

lihtc_projects — 55,345 rows

insured_mortgages — 74,247 rows

subsidized_households — 97,022 rows

debt_issues — 77,086 rows

fac_audits — 170,436 rows

fac_findings — 50,666 rows

fema_grants — 846,627 rows

usda_rd_properties — 12,420 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;