# MyTown dataset schema

Generated from the live build. Dates are ISO 8601 (YYYY-MM-DD); `_at` columns are UTC.
Join everything to `municipalities.id` via `muni_id`, or via `meetings.id` through `meeting_id`.

## municipalities  (5,989 rows)
_PRIMARY KEY id. Every other table joins back here via muni_id, or via meetings through meeting_id._

| column | type |
|---|---|
| `id` (key) | integer |
| `slug` | text |
| `name` | text |
| `state` | text |
| `country` | text |
| `platform` | text |

## meetings  (1,740,994 rows)
_PK id · FK muni_id → municipalities.id. One row per meeting._

| column | type |
|---|---|
| `id` (key) | integer |
| `muni_id` | integer |
| `body_name` | text |
| `meeting_date` | text |
| `meeting_time` | text |
| `location` | text |
| `agenda_url` | text |
| `minutes_url` | text |
| `video_url` | text |
| `detail_url` | text |
| `archived_pdf_key` | text |

## briefs  (706,388 rows)
_PK/FK meeting_id → meetings.id. AI plain-English agenda summary._

| column | type |
|---|---|
| `meeting_id` (key) | integer |
| `headline` | text |
| `summary` | text |
| `notable_items` | text |
| `tags` | text |
| `created_at` | text |

## decisions  (243,699 rows)
_PK/FK meeting_id → meetings.id. What passed/failed, from minutes._

| column | type |
|---|---|
| `meeting_id` (key) | integer |
| `headline` | text |
| `summary` | text |
| `decisions` | text |
| `tags` | text |
| `created_at` | text |

## briefs_i18n  (160,779 rows)
_FK meeting_id → meetings.id. lang is 'es' or 'fr'._

| column | type |
|---|---|
| `meeting_id` (key) | integer |
| `lang` (key) | text |
| `headline` | text |
| `summary` | text |
| `notable_items` | text |

## roundups  (4,469 rows)
_FK muni_id → municipalities.id. Weekly recap (markdown)._

| column | type |
|---|---|
| `muni_id` (key) | integer |
| `week_of` (key) | text |
| `headline` | text |
| `article_md` | text |
| `created_at` | text |

## transcripts  (26,543 rows)
_PK/FK meeting_id → meetings.id. Whisper/caption transcript of meeting video._

| column | type |
|---|---|
| `meeting_id` (key) | integer |
| `duration_s` | integer |
| `text` | text |
| `created_at` | text |

## census_demographics  (2,565 rows)
_FK muni_id → municipalities.id. US Census ACS 5-year._

| column | type |
|---|---|
| `muni_id` (key) | integer |
| `population` | integer |
| `median_income` | integer |
| `per_capita_income` | integer |
| `median_home_value` | integer |
| `housing_units` | integer |
| `updated_at` | text |

## meeting_texts  (826,860 rows)
_PK/FK meeting_id → meetings.id. Raw extracted source text (NOT AI)._

| column | type |
|---|---|
| `meeting_id` (key) | integer |
| `agenda_text` | text |
| `minutes_text` | text |
| `updated_at` | text |

## campaign_finance  (1,397,866 rows)
_FK muni_id → municipalities.id. NetFile local donor transactions (predominantly California)._

| column | type |
|---|---|
| `txn_id` (key) | text |
| `muni_id` | integer |
| `filing_id` | text |
| `filer_name` | text |
| `txn_date` | text |
| `amount` | real |
| `txn_type` | text |
| `schedule` | text |
| `contributor` | text |
| `employer` | text |
| `occupation` | text |
| `address` | text |
| `updated_at` | text |

## canada_demographics  (454 rows)
_FK muni_id → municipalities.id. StatCan._

| column | type |
|---|---|
| `muni_id` (key) | integer |
| `population` | integer |
| `median_income` | integer |
| `median_home_value` | integer |
| `updated_at` | text |

## home_value_trend  (3,538 rows)
_FK muni_id → municipalities.id. Zillow ZHVI home-value trend._

| column | type |
|---|---|
| `muni_id` (key) | integer |
| `zhvi_latest` | real |
| `latest_month` | text |
| `yoy_pct` | real |
| `five_yr_pct` | text |
| `yearly_series_json` | text |
| `updated_at` | text |

## federal_awards  (21,446 rows)
_FK muni_id → municipalities.id. USAspending federal grants/contracts._

| column | type |
|---|---|
| `muni_id` (key) | integer |
| `award_uid` | text |
| `recipient` | text |
| `amount` | real |
| `agency` | text |
| `description` | text |
| `official_url` | text |
| `updated_at` | text |

## civic_records  (5,697 rows)
_FK muni_id → municipalities.id. Building permits & business licenses from municipal open-data feeds._

| column | type |
|---|---|
| `muni_id` | integer |
| `kind` | text |
| `record_no` | text |
| `address` | text |
| `rec_type` | text |
| `description` | text |
| `applicant` | text |
| `value` | text |
| `applied_date` | text |
| `issued_date` | text |
| `status` | text |
| `official_url` | text |
| `fetched_at` | text |

## decision_geo  (1,907 rows)
_FK meeting_id → meetings.id. Per-decision address geocode + building + flood zone._

| column | type |
|---|---|
| `meeting_id` (key) | integer |
| `address` | text |
| `lat` | real |
| `lon` | real |
| `building_height_m` | real |
| `building_floors` | text |
| `building_name` | text |
| `building_class` | text |
| `building_subtype` | text |
| `flood_zone` | text |
| `flood_subtype` | text |
| `in_flood_zone` | integer |
| `updated_at` | text |

## decision_echo  (170 rows)
_FK meeting_id → meetings.id. EPA ECHO facilities near a decision address._

| column | type |
|---|---|
| `meeting_id` (key) | integer |
| `registry_id` | text |
| `fac_name` | text |
| `fac_street` | text |
| `compliance_status` | text |
| `curr_sv` | text |
| `viol_flag` | text |
| `last_inspection` | text |
| `echo_url` | text |
| `updated_at` | text |

## decision_nonprofit  (76 rows)
_FK meeting_id → meetings.id. IRS nonprofit tied to a decision._

| column | type |
|---|---|
| `meeting_id` (key) | integer |
| `ein` | text |
| `name` | text |
| `ntee` | text |
| `category` | text |
| `propublica_url` | text |
| `updated_at` | text |

## matters  (15,319 rows)
| column | type |
|---|---|
| `meeting_id` | integer |
| `muni_id` | integer |
| `matter_type` | text |
| `title` | text |
| `case_no` | text |
| `address` | text |
| `action` | text |
| `staff_rec` | text |
| `outcome` | text |
| `vote` | text |
| `units` | text |
| `affordable_units` | text |
| `height_ft` | text |
| `sqft` | text |
| `acreage` | text |
| `parking` | text |
| `est_value` | text |
| `conditions` | text |
| `lat` | text |
| `lon` | text |
| `created_at` | text |

## officials  (237 rows)
| column | type |
|---|---|
| `muni_id` | integer |
| `body_name` | text |
| `name` | text |
| `role` | text |
| `first_seen_date` | text |
| `last_seen_date` | text |
| `meeting_count` | integer |
| `updated_at` | text |

## member_votes  (4,562,764 rows)
| column | type |
|---|---|
| `meeting_id` | integer |
| `muni_id` | integer |
| `body_name` | text |
| `motion` | text |
| `member_name` | text |
| `vote` | text |
| `contested` | integer |
| `created_at` | text |

## cf_transactions  (2,318,515 rows)
| column | type |
|---|---|
| `source` | text |
| `level` | text |
| `state` | text |
| `txn_type` | text |
| `filer_name` | text |
| `filer_id` | text |
| `office` | text |
| `counterparty_name` | text |
| `counterparty_type` | text |
| `employer` | text |
| `occupation` | text |
| `city` | text |
| `region` | text |
| `zip` | text |
| `amount` | text |
| `txn_date` | text |
| `election_cycle` | text |
| `report_id` | text |
| `external_id` | text |

## school_stats  (125 rows)
| column | type |
|---|---|
| `muni_id` | integer |
| `leaid` | text |
| `enrollment` | integer |
| `teachers_fte` | integer |
| `schools_count` | integer |
| `per_pupil_spending` | integer |
| `revenue_total` | integer |
| `year` | integer |
| `updated_at` | text |

## gov_finances  (2,001 rows)
| column | type |
|---|---|
| `muni_id` | integer |
| `fiscal_year` | integer |
| `population` | integer |
| `government_type` | text |
| `total_revenue` | integer |
| `total_taxes` | integer |
| `property_tax` | integer |
| `intergov_revenue` | integer |
| `total_expenditure` | integer |
| `debt_outstanding` | integer |
| `per_capita_total_revenue` | real |
| `per_capita_total_taxes` | real |
| `per_capita_total_expenditure` | real |
| `per_capita_debt_outstanding` | real |
| `updated_at` | text |

## building_permits  (2,887 rows)
| column | type |
|---|---|
| `muni_id` | integer |
| `year` | integer |
| `units_1` | integer |
| `units_2_4` | integer |
| `units_5plus` | integer |
| `units_total` | integer |
| `value_total` | integer |
| `updated_at` | text |

## election_results  (40,054 rows)
| column | type |
|---|---|
| `muni_id` | text |
| `place_name` | text |
| `state` | text |
| `county` | text |
| `office` | text |
| `election_date` | text |
| `candidate_name` | text |
| `party` | text |
| `votes` | integer |
| `total_votes_in_race` | integer |
| `is_winner` | integer |
| `term_years` | text |
| `source` | text |
| `source_url` | text |

## ballot_measures  (1,468 rows)
| column | type |
|---|---|
| `muni_id` | integer |
| `place_name` | text |
| `state` | text |
| `county` | text |
| `election_date` | text |
| `measure_title` | text |
| `measure_type` | text |
| `topic` | text |
| `yes_votes` | integer |
| `no_votes` | integer |
| `total_votes` | integer |
| `passed` | integer |
| `jurisdiction_type` | text |
| `source_url` | text |

## official_bridges  (318 rows)
| column | type |
|---|---|
| `muni_id` | text |
| `local_name` | text |
| `state` | text |
| `legislator_name` | text |
| `openstates_id` | text |
| `chamber` | text |
| `party` | text |
| `term_start` | text |
| `term_end` | text |

## officials_tenure  (215 rows)
| column | type |
|---|---|
| `muni_id` | integer |
| `name` | text |
| `party` | text |
| `first_won` | text |
| `last_won` | text |
| `terms_won` | integer |
| `is_incumbent` | text |
| `updated_at` | text |

## council_districts  (176 rows)
| column | type |
|---|---|
| `muni_id` | integer |
| `city` | text |
| `state` | text |
| `district_id` | text |
| `member_name` | text |
| `centroid_lat` | text |
| `centroid_lon` | text |
| `bbox` | text |
| `source_url` | text |