Spaces:

CommunityOne
/

open-navigator

Running on CPU Upgrade

App Files Files Community

open-navigator / website /docs /data-sources /data-model-erd.md

jcbowyer

Clean HuggingFace deployment without binary files

61d29fc 28 days ago

preview code

raw

history blame contribute delete

144 kB

	---
	displayed_sidebar: developersSidebar
	sidebar_position: 1
	---

	import ZoomableMermaid from '@site/src/components/ZoomableMermaid';

	# Data Model & Entity Relationship Diagram

	Comprehensive overview of all data entities extracted, processed, and uploaded to HuggingFace datasets.

	## � HuggingFace Dataset Structure

	### Current Datasets Being Uploaded

	```
	open-navigator-data/
	├── jurisdictions/ # 🏛️ Core jurisdiction data
	│ ├── cities # 19,000+ incorporated places
	│ ├── counties # 3,144 U.S. counties
	│ ├── states # 50 states + DC, territories
	│ ├── school_districts # 13,000+ districts (NCES data)
	│ └── census_data # Basic FIPS codes & census year reference
	│
	├── demographics/ # 👥 Comprehensive demographic data (U.S. Census)
	│ ├── population # Total population, age distribution
	│ ├── race_ethnicity # Race and ethnicity breakdowns
	│ ├── income_economics # Income, poverty, SNAP benefits
	│ ├── education # Educational attainment levels
	│ ├── housing # Housing units, ownership, values
	│ ├── employment # Unemployment, labor force participation
	│ └── health_insurance # Insurance coverage (uninsured, Medicaid, Medicare)
	│
	├── social/ # 📱 Social media presence
	│ ├── twitter # Twitter/X accounts
	│ ├── facebook # Facebook pages
	│ ├── instagram # Instagram accounts
	│ └── linkedin # LinkedIn pages
	│
	├── videos/ # 📹 Video & streaming platforms
	│ ├── youtube_channels # Government YouTube channels
	│ ├── vimeo # Vimeo accounts
	│ └── livestreams # Live meeting streams
	│
	├── platforms/ # 🖥️ Meeting management systems
	│ ├── legistar # Legistar URLs
	│ ├── granicus # Granicus links
	│ ├── suiteone # SuiteOne systems
	│ └── civicplus # CivicPlus platforms
	│
	├── domains/ # 🌐 Official government websites
	│ ├── gsa_domains # .gov domain registry
	│ ├── municipal_websites # City/county websites
	│ └── state_portals # State government sites
	│
	├── events/ # 📋 Meetings, Hearings & Public Events
	│ ├── events # Government meetings, public hearings, community forums, town halls
	│ ├── event_participants # Officials and organizations participating in events
	│ ├── event_agenda_items # Individual agenda topics discussed
	│ ├── event_documents # Agendas, minutes, presentations, handouts
	│ ├── event_media # Video recordings, livestreams, audio files
	│ └── event_bills # Bills discussed or considered at meetings
	│
	├── contacts/ # 👥 All People - Officials, Candidates, Donors, Constituents
	│ ├── officials # Elected and appointed officials (mayors, council members, legislators)
	│ ├── official_roles # Current and historical positions held
	│ ├── official_contacts # Email, phone, office addresses
	│ ├── official_identifiers # External IDs (Twitter, OpenStates, Ballotpedia)
	│ ├── official_links # Websites, social media profiles
	│ ├── candidates # Political candidates (House, Senate, President - FEC data)
	│ ├── nonprofit_donors # Nonprofit leadership political giving (FEC analysis)
	│ └── constituents # Donors, volunteers, members, beneficiaries (all people)
	│
	├── nonprofits/ # 🏢 Nonprofit organizations & churches
	│ ├── irs_eobmf # IRS EO-BMF bulk data (1.9M+ organizations) - PRIMARY SOURCE
	│ ├── irs_nonprofits # Legacy IRS 990 data (deprecated - use irs_eobmf)
	│ ├── propublica_data # ProPublica API (financials, NTEE codes)
	│ ├── everyorg_data # Every.org API (missions, causes, logos)
	│ ├── nonprofit_990s # Detailed Form 990 financials (yearly filings)
	│ ├── congregations # 🛐 Church & congregation data (ARDA, HIFLD, NCS)
	│ ├── constituents # 🤝 Donors, volunteers, members, beneficiaries (Microsoft CDM)
	│ ├── donations # 💝 Financial contributions and in-kind gifts (Microsoft CDM)
	│ ├── campaigns # 📣 Fundraising campaigns and appeals (Microsoft CDM)
	│ ├── memberships # 🎫 Member enrollment and renewals (Microsoft CDM)
	│ ├── volunteer_activities # 🙋 Volunteer hours and activities (Microsoft CDM)
	│ ├── program_delivery # 🎯 Programs and services delivered (Microsoft CDM)
	│ └── program_outcomes # 📊 Impact metrics and outcome measurements (Microsoft CDM)
	│
	├── grants/ # 💵 Grant funding transactions
	│ ├── nonprofit_grants # Grants to nonprofits (from 990 Schedule I)
	│ ├── government_grants # Government grants to orgs/jurisdictions
	│ ├── foundation_grants # Private foundation grants
	│ └── federal_grants # Federal funding programs
	│
	├── causes/ # 🎯 Cause & category taxonomy
	│ ├── ntee_codes # IRS NTEE classification system
	│ └── everyorg_causes # Every.org cause tags
	│
	├── budgets/ # 💰 Government budgets & finances
	│ ├── city_budgets # City/municipal budgets & spending
	│ ├── county_budgets # County budgets & expenditures
	│ ├── state_budgets # State government finances
	│ ├── school_budgets # School district finances (NCES F-33)
	│ ├── bond_debt # Municipal bonds & debt obligations
	│ ├── budget_line_items # 📊 Detailed budget categories & line items
	│ └── budget_deltas # 🔍 Budget-to-Minutes Delta analysis (political economy)
	│
	├── decisions/ # ⚖️ Policy decisions & political economy analysis
	│ ├── policy_decisions # Extracted decisions from meetings
	│ ├── decision_frames # Frame analysis (rhetoric patterns)
	│ ├── decision_options # Options considered & rejected
	│ ├── decision_tradeoffs # Tradeoffs discussed (cost vs benefit, etc.)
	│ ├── stakeholder_positions # 👥 Who spoke for/against (Influence Radar)
	│ ├── decision_votes # Detailed vote records per decision
	│ ├── deferral_patterns # 📅 Stalling detection (same topic, multiple deferrals)
	│ ├── deferral_instances # Individual tabling events linked to patterns
	│ ├── keyword_density # Quantitative indicators (grant/taxpayer/emergency)
	│ └── deferral_patterns # Tabled/delayed decisions (temporal analysis)
	│
	├── elections/ # 📅 Election cycles & temporal analysis
	│ ├── election_cycles # Election dates & periods
	│ └── election_influences # Pre/post-election decision patterns
	│
	├── campaigns/ # 💰 Political campaign finance (FEC data)
	│ ├── committees # PACs, Super PACs, campaign committees
	│ └── contributions # Individual political contributions $200+
	│
	├── civic/ # 🗳️ Google Civic & Wikidata
	│ ├── civic_divisions # OCD divisions
	│ ├── representatives # From Google Civic API
	│ ├── wikidata_entities # Structured entities
	│ └── dbpedia_resources # Wikipedia infobox data
	│
	├── ballots/ # 🗳️ Ballot initiatives & referendums
	│ ├── state_measures # State propositions (fluoridation votes!)
	│ ├── local_measures # City/county ballot questions
	│ └── election_results # Historical voting outcomes
	│
	├── bills/ # 📜 Legislation & Lawmaking
	│ ├── bills # Bills and resolutions (1.5M+ from all 50 states)
	│ ├── bill_actions # Bill history (introduced, committee, floor vote, signed)
	│ ├── bill_sponsorships # Primary sponsors and co-sponsors
	│ ├── bill_abstracts # Bill summaries and descriptions
	│ ├── bill_versions # Different versions of bill text (introduced, amended, enrolled)
	│ ├── bill_version_links # Links to PDF/HTML bill text
	│ ├── bill_documents # Supporting documents (fiscal notes, amendments, analysis)
	│ ├── bill_document_links # Links to supporting documents
	│ ├── bill_subjects # Subject/topic tags per bill
	│ ├── legislative_sessions # Session identifiers (2023 Regular, 2024 Special, etc.)
	│ ├── vote_events # Roll call votes on bills
	│ ├── vote_counts # Vote tallies (yes, no, abstain, absent)
	│ └── individual_votes # Individual legislator votes (yes/no/abstain)
	│
	├── topics/ # 🎯 Advocacy causes & campaigns
	│ ├── topic_definitions # Validated survey questions from Roper Center
	│ ├── survey_questions # Public opinion question wording library
	│ ├── jurisdiction_topics # What each city is discussing
	│ └── advocacy_alerts # Opportunities for engagement
	│
	├── surveys/ # 📊 Public opinion research & polling data
	│ ├── survey_providers # Polling organizations (Gallup, Pew, Roper, etc.)
	│ ├── survey_studies # Individual survey studies/waves
	│ ├── survey_variables # Questions/items asked in surveys
	│ ├── survey_responses # Aggregate and individual response data
	│ ├── ipoll_metadata # Roper iPoll catalog metadata
	│ └── survey_crosstabs # Breakdowns by demographics, geography
	│
	├── factchecks/ # ✅ Fact-checking & claim verification
	│ ├── claim_reviews # Google Fact Check API (ClaimReview schema)
	│ ├── politifact # PolitiFact Truth-O-Meter ratings
	│ ├── factcheck_org # FactCheck.org verified claims
	│ └── verified_claims # Aggregated fact-check database
	│
	├── civic_tech/ # 💻 Open source projects & hackathons
	│ ├── github_repositories # Civic tech projects (GitHub API)
	│ ├── project_metadata # Code for America, USDR, Civic Tech Field Guide
	│ ├── contributors # Maintainers and core contributors
	│ ├── project_issues # Good first issues, contribution opportunities
	│ ├── hackathons # Civic hackathon events
	│ ├── hackathon_projects # Projects built at hackathons
	│ ├── brigade_chapters # Code for America brigade locations
	│ └── project_funding # GitHub Sponsors, grants, OpenCollective
	│
	├── community_solutions/ # 🌟 Community engagement & use cases
	│ ├── engagement_spectrum # Spectrum of Community Engagement to Ownership
	│ ├── use_case_catalog # Harvard Data-Smart City Solutions examples
	│ ├── data_academies # Brookings Institution training programs
	│ ├── success_stories # Real-world outcomes (Providence, Portland, Tempe)
	│ ├── metric_templates # Pre-built analytics for common challenges
	│ └── workflow_guides # Step-by-step community data workflows
	│
	├── analytics/ # 📊 Time dimensions & metric views
	│ ├── date_dimension # Date/time reference table (YYYY-MM-DD, day_of_week, fiscal_year)
	│ ├── temporal_relationships # Time-series joins for all entities
	│ ├── metric_views # Pre-computed analytics (advocacy, spending, nonprofit impact)
	│ ├── aggregated_stats # Monthly/quarterly/yearly rollups
	│ └── dashboard_metrics # Real-time dashboard data feeds
	│
	├── standards/ # 🌐 Schema.org, Popolo, CEDS, IATI exports
	│ ├── schema_org_jsonld # JSON-LD exports (Event, Person, Organization, Legislation, ClaimReview)
	│ ├── popolo_exports # Popolo-compliant JSON (Person, Organization, Membership, VoteEvent)
	│ ├── ceds_aligned # CEDS-compliant education data (Element IDs, Option Sets)
	│ ├── ocd_divisions # Open Civic Data division IDs
	│ ├── iati_activities # IATI Standard v2.03 XML (programs, grants, humanitarian aid)
	│ └── rdf_triples # RDF/Turtle semantic web exports
	│
	├── vocabulary/ # 🔧 OMOP-inspired concept & terminology (SYSTEM-INTERNAL)
	│ ├── concept # Master concept table (cities, causes, officials)
	│ ├── vocabulary # Vocabulary sources (OCD_ID, IRS_NTEE, US_Census)
	│ ├── concept_class # Concept classifications (City, County, 501c3, Mayor)
	│ ├── concept_relationship # Relationships (City → County, Topic → Legislation)
	│ └── domain # Domain groupings (Jurisdiction, Nonprofit, Policy)
	│
	└── exports/ # 📤 API-ready formatted exports
	├── csv_bulk # CSV downloads for all datasets
	├── json_api # REST API JSON responses
	├── graphql_schema # GraphQL schema definitions
	└── parquet_optimized # Compressed Parquet (default format)
	```

	### Parquet File Naming Convention

	Rule: Use underscores (`_`) consistently, NOT hyphens (`-`)

	Format: `{category}_{subcategory}.parquet`

	Examples:
	```
	✅ CORRECT (using underscores):
	jurisdictions_cities.parquet
	jurisdictions_counties.parquet
	jurisdictions_states.parquet
	jurisdictions_school_districts.parquet
	social_twitter.parquet
	social_facebook.parquet
	videos_youtube_channels.parquet
	events_events.parquet
	contacts_officials.parquet
	bills_bills.parquet
	nonprofits_organizations.parquet
	nonprofits_financials.parquet
	nonprofits_programs.parquet
	nonprofits_locations.parquet
	nonprofits_irs_eobmf.parquet
	nonprofits_constituents.parquet
	nonprofits_donations.parquet
	nonprofits_campaigns.parquet # Nonprofit fundraising campaigns (NOT political)
	contacts_candidates.parquet # Political candidates (FEC)
	contacts_nonprofit_donors.parquet # Nonprofit leadership political giving (FEC analysis)
	contacts_constituents.parquet # Donors, volunteers, members, beneficiaries
	campaigns_committees.parquet # Political committees/PACs (FEC)
	campaigns_contributions.parquet # Political contributions (FEC)
	nonprofits_memberships.parquet
	nonprofits_volunteer_activities.parquet
	nonprofits_program_delivery.parquet
	nonprofits_program_outcomes.parquet
	grants_federal_grants.parquet
	contacts_officials.parquet
	contacts_official_roles.parquet
	contacts_official_contacts.parquet
	contacts_official_identifiers.parquet
	contacts_official_links.parquet
	contacts_candidates.parquet
	contacts_nonprofit_donors.parquet
	contacts_constituents.parquet
	bills_bills.parquet
	bills_bill_actions.parquet
	bills_bill_sponsorships.parquet
	bills_bill_abstracts.parquet
	bills_bill_versions.parquet
	bills_bill_version_links.parquet
	bills_bill_documents.parquet
	bills_bill_document_links.parquet
	bills_bill_subjects.parquet
	bills_legislative_sessions.parquet
	bills_vote_events.parquet
	bills_vote_counts.parquet
	bills_individual_votes.parquet
	events.parquet
	event_participants.parquet
	event_agenda_items.parquet
	event_documents.parquet
	event_media.parquet
	event_bills.parquet
	budgets_city_budgets.parquet
	surveys_national_polls.parquet
	surveys_roper_questions.parquet
	surveys_survey_providers.parquet
	surveys_survey_studies.parquet
	surveys_survey_variables.parquet
	surveys_survey_responses.parquet
	surveys_ipoll_metadata.parquet
	factchecks_claim_reviews.parquet
	factchecks_politifact.parquet
	analytics_date_dimension.parquet
	analytics_metric_views.parquet
	analytics_temporal_relationships.parquet
	standards_schema_org_jsonld.parquet
	standards_popolo_exports.parquet
	standards_ceds_aligned.parquet
	standards_iati_activities.parquet
	vocabulary_concept.parquet
	vocabulary_vocabulary.parquet
	vocabulary_concept_class.parquet
	vocabulary_concept_relationship.parquet

	❌ INCORRECT (using hyphens):
	jurisdictions-cities.parquet
	social-twitter.parquet
	meetings-government-meetings.parquet
	surveys-national-polls.parquet
	factchecks-claim-reviews.parquet
	analytics-date-dimension.parquet
	standards-schema-org.parquet
	```

	Why Underscores?
	- ✅ Python-friendly variable names (can use `data.jurisdictions_cities`)
	- ✅ SQL-compatible column names
	- ✅ Consistent with folder structure (`school_districts`, not `school-districts`)
	- ✅ Better for programmatic access
	- ✅ Avoids shell escaping issues

	Repository Name Exception:
	- HuggingFace repo: `CommunityOne/open-navigator-data` (hyphen is fine for URLs)
	- File names inside repo: Use underscores (`jurisdictions_cities.parquet`)

	## 🔄 Data Extraction Pipeline

	### Phase 1: Discovery (Bronze Layer)
	1. Census Data → Jurisdictions list
	2. GSA Domains → Government websites
	3. NCES → School districts with financial data (F-33 forms)
	4. IRS EO-BMF → ALL 1.9M+ U.S. tax-exempt organizations (PRIMARY SOURCE)
	5. IRS TEOS → Legacy nonprofit EINs (deprecated - use IRS EO-BMF)
	6. Census of Governments → Municipal budgets & finances
	7. URL Discovery → Meeting platforms, YouTube, budget PDFs
	8. Social Media → Twitter, Facebook accounts

	### Phase 2: Enrichment (Silver Layer)
	1. IRS EO-BMF → Complete nonprofit registry with 28 data fields per organization
	2. ProPublica Nonprofit Explorer → Enhanced financial data, detailed 990 filings
	3. Every.org API → Nonprofit causes, missions, logos
	3. ARDA (Association of Religion Data Archives) → Congregation characteristics, health ministries
	4. HIFLD Places of Worship → Geospatial church locations (350K+ congregations)
	5. National Congregations Study → Social service provision patterns
	6. NCES F-33 Finance Survey → School district budgets, per-pupil spending
	7. Census Annual Survey → State/local government finances
	8. Municipal Securities Rulemaking Board (EMMA) → Bond debt data
	9. YouTube API → Channel statistics
	10. Open States PostgreSQL Database → Complete legislative data (~10 GB monthly dump)
	- 8,600+ people (legislators, governors, mayors) across all 50 states + DC + Puerto Rico
	- 1.5M+ bills with full text and history
	- 13M+ bill actions (introduced, committee, amendments, floor votes, signed)
	- 7.2M+ sponsorships (primary sponsors and co-sponsors)
	- 3.5M+ bill versions (as introduced, committee substitute, enrolled, enacted)
	- 180K+ events (legislative meetings, hearings, committee sessions)
	- 835K+ event participants (who spoke, testified, or attended)
	- 524K+ agenda items from meetings
	- Vote events with individual legislator positions
	- Organizations (legislative bodies, committees)
	- Jurisdictions (states, territories)
	- Updated monthly from https://data.openstates.org/postgres/monthly/
	11. OpenStates People Repository → Current legislator contact info
	- GitHub repo: https://github.com/openstates/people
	- YAML files with email, phone, district offices
	- Social media profiles and website links
	- Updated daily via automated scrapers
	12. Wikidata SPARQL → Entity relationships
	12. DBpedia → Wikipedia structured data
	13. Google Civic → Representatives
	14. OpenFEC API → Political contributions, candidates, committees (campaign finance)
	15. GitHub API → Civic tech projects, contributors, issues
	16. Civic Tech Field Guide → Curated project taxonomy
	17. Code for America → Brigade projects and hackathons
	18. Digital Public Goods Alliance → DPG-certified open source projects

	### Phase 3: Processing (Gold Layer)
	1. Meeting Extraction → Agenda/minutes text
	2. Video Transcripts → YouTube captions
	3. Document Analysis → Keyword detection
	4. Relationship Mapping → Entity connections
	5. Oral Health Filtering → Topic classification
	6. Temporal Indexing → Date dimension table, time-series relationships
	7. Metric View Creation → Pre-computed analytics (advocacy activity, government spending, nonprofit impact)
	8. Schema.org JSON-LD → Structured data exports (Event, Person, Organization, Legislation, ClaimReview)
	9. Popolo Compliance → Open government standard exports (Person, Organization, Membership, VoteEvent)
	10. CEDS Alignment → Education data mapping to NCES Element IDs and Option Sets

	### New Dataset Categories Explained

	#### 📊 Analytics Datasets

	Purpose: Enable time-series analysis, trend detection, and dashboard metrics without complex SQL queries.

	\| Dataset \| Description \| Refresh Frequency \|
	\|---------\|-------------\|-------------------\|
	\| `analytics_date_dimension` \| Calendar reference table with fiscal years, quarters, day-of-week, holidays \| Static (updated annually) \|
	\| `analytics_temporal_relationships` \| Pre-joined date keys for all time-based entities (meetings, votes, budgets, filings) \| Daily \|
	\| `analytics_metric_views` \| Pre-computed analytics like advocacy_activity, government_spending, nonprofit_impact \| Hourly \|
	\| `analytics_aggregated_stats` \| Monthly/quarterly/yearly rollups (meeting counts, budget totals, grant sums) \| Daily \|
	\| `analytics_dashboard_metrics` \| Real-time feeds for dashboards (active meetings today, trending topics, hot ballot measures) \| Every 5 minutes \|

	Example Use Case:
	```sql
	-- Instead of complex joins, use metric view:
	SELECT * FROM analytics_metric_views
	WHERE metric_name = 'advocacy_activity'
	AND jurisdiction_id = 'ocd-division/country:us/state:al/place:birmingham'
	AND date_period = '2024-Q1';
	```

	#### 🌐 Standards-Compliant Exports

	Purpose: Maximum interoperability with civic tech platforms, search engines, and semantic web tools.

	\| Dataset \| Standard \| Use Case \| Consumers \|
	\|---------\|----------\|----------\|-----------\|
	\| `standards_schema_org_jsonld` \| Schema.org JSON-LD \| Google Search rich results, voice assistants \| Google, Bing, Alexa, Siri \|
	\| `standards_popolo_exports` \| Popolo Project \| Civic tech platform integration \| mySociety, OpenNorth, Sunlight Foundation \|
	\| `standards_ceds_aligned` \| Common Education Data Standards \| Education data exchange, NCES reporting \| State education depts, Ed-Fi, IMS Global \|
	\| `standards_ocd_divisions` \| Open Civic Data IDs \| Cross-platform jurisdiction referencing \| Google Civic, Ballotpedia, Vote Smart \|
	\| `standards_rdf_triples` \| RDF/Turtle \| Linked open data, knowledge graphs \| DBpedia, Wikidata, SPARQL endpoints \|

	Example Schema.org Export:
	```json
	{
	"@context": "https://schema.org",
	"@type": "GovernmentOrganization",
	"name": "Birmingham City Council",
	"address": {
	"@type": "PostalAddress",
	"addressLocality": "Birmingham",
	"addressRegion": "AL"
	},
	"event": [{
	"@type": "Event",
	"name": "Regular City Council Meeting",
	"startDate": "2024-01-15T18:00:00-06:00"
	}]
	}
	```

	#### ✅ Fact-Checking Datasets

	Purpose: Verify claims made in meetings, legislation, and political speech.

	\| Dataset \| Source \| Fields \| Update Frequency \|
	\|---------\|--------\|--------\|------------------\|
	\| `factchecks_claim_reviews` \| Google Fact Check API \| claimReviewed, reviewRating, author, datePublished \| Daily \|
	\| `factchecks_politifact` \| PolitiFact web scraping \| claim, ruling, truth_o_meter, context \| Daily \|
	\| `factchecks_factcheck_org` \| FactCheck.org API/scraping \| claim, verdict, analysis, sources \| Daily \|
	\| `factchecks_verified_claims` \| Aggregated + deduplicated \| claim_text, consensus_rating, verification_sources \| Daily \|

	Integration with Meetings:
	- Cross-reference meeting transcripts with verified claims
	- Flag unverified statements in legislative debates
	- Track politician accuracy scores over time

	## �📊 Complete Data Model (ERD)

	<ZoomableMermaid
	title="Interactive Entity Relationship Diagram"
	value={`
	erDiagram
	%% ========================================
	%% CORE JURISDICTION ENTITIES
	%% ========================================
	%% Schema.org type: AdministrativeArea
	%% OCD-ID format: ocd-division/country:us/state:al/place:birmingham

	JURISDICTION \|\|--o{ EVENT : hosts
	JURISDICTION \|\|--o{ LEADER : employs
	JURISDICTION \|\|--o{ YOUTUBE_CHANNEL : operates
	JURISDICTION \|\|--o{ SOCIAL_MEDIA : maintains
	JURISDICTION \|\|--o{ MEETING_PLATFORM : uses
	JURISDICTION {
	string jurisdiction_id PK
	string name
	string jurisdiction_type
	string state_code
	string county_name
	string website_url
	float latitude
	float longitude
	string fips_code
	int completeness_score
	datetime discovered_at
	}

	%% Census and Government Data
	JURISDICTION \|\|--o\| CENSUS_DATA : has
	CENSUS_DATA {
	string jurisdiction_id PK
	string county_fips
	string place_fips
	datetime census_year
	}

	JURISDICTION \|\|--o\| GSA_DOMAIN : uses
	GSA_DOMAIN {
	string domain PK
	string jurisdiction_id FK
	string domain_type
	string agency_name
	string organization_type
	string city
	string state
	datetime created_date
	}

	%% Government Finances
	JURISDICTION \|\|--o{ GOVERNMENT_BUDGET : has
	GOVERNMENT_BUDGET {
	string budget_id PK
	string jurisdiction_id FK
	int fiscal_year
	float total_revenue
	float total_expenditures
	float total_debt
	float property_tax_revenue
	float sales_tax_revenue
	float federal_grants
	float state_grants
	float general_fund_balance
	string budget_document_url
	datetime published_date
	}

	%% ========================================
	%% SCHOOL DISTRICTS (NCES)
	%% ========================================
	%% Based on CEDS (Common Education Data Standards) and NCES data elements
	%% See: https://ceds.ed.gov/ and https://github.com/CEDStandards
	%% Schema.org type: EducationalOrganization

	JURISDICTION \|\|--o{ SCHOOL_DISTRICT : contains
	SCHOOL_DISTRICT \|\|--o{ LEADER : governed_by
	SCHOOL_DISTRICT {
	string nces_id PK
	string district_name
	string jurisdiction_id FK
	string state_code
	string county_name
	string district_type
	int total_students
	int total_schools
	float total_revenue
	float total_expenditures
	float per_pupil_spending
	float federal_revenue
	float state_revenue
	float local_revenue
	string phone
	string website
	string superintendent
	datetime school_year
	}

	%% ========================================
	%% EVENTS & MEETINGS
	%% ========================================
	%% Schema.org types: Event, GovernmentEvent, VideoObject, DigitalDocument
	%% Parquet files: events.parquet, event_documents.parquet, event_participants.parquet, etc.

	%% EVENT - Parent entity for all public events
	JURISDICTION \|\|--o{ EVENT : hosts
	EVENT \|\|--o{ EVENT_PARTICIPANT : includes
	EVENT \|\|--o{ EVENT_AGENDA_ITEM : contains
	EVENT \|\|--o{ EVENT_DOCUMENT : produces
	EVENT \|\|--o{ EVENT_MEDIA : recorded_as
	EVENT \|\|--o{ EVENT_BILL : discusses
	EVENT {
	string event_id PK
	string jurisdiction_id FK
	string event_type "meeting/hearing/forum/workshop/town_hall"
	string event_category "legislative/planning/public_health/education"
	string meeting_type "regular/special/emergency/work_session/executive"
	string platform "legistar/granicus/zoom/youtube_live"
	string meeting_number "Sequential identifier"
	datetime event_date
	datetime end_date
	string event_title
	string body_name "City Council/Planning Commission/School Board"
	string status "scheduled/in_progress/completed/cancelled/postponed"
	string location_type "in_person/virtual/hybrid"
	string venue_name
	string venue_address
	string agenda_packet_url
	string minutes_url
	string source_url
	boolean requires_registration
	float registration_fee
	int max_capacity
	string presenter
	string training_topic
	string target_audience
	int agenda_item_count
	int document_count
	boolean has_video
	boolean has_transcript
	boolean oral_health_related
	string data_source "openstates/legistar/granicus/manual"
	datetime agenda_published_at
	datetime minutes_approved_at
	datetime created_at
	datetime extracted_at
	}

	%% EVENT_PARTICIPANT - Who participated in the event
	EVENT_PARTICIPANT {
	string participant_id PK
	string event_id FK
	string participant_name
	string participant_type "official/legislator/staff/public/expert/advocate"
	string organization_name
	string organization_id FK "Links to ORGANIZATION or LEADER"
	string role "chair/member/speaker/witness/observer"
	string participation_type "voting/testifying/presenting/attending"
	int speaking_order
	int speaking_duration_seconds
	string remarks_summary
	datetime participation_date
	}

	%% EVENT_AGENDA_ITEM - Individual topics discussed at event
	EVENT_AGENDA_ITEM {
	string agenda_item_id PK
	string event_id FK
	string item_number "1.A/2.B/etc"
	string title
	string description
	string item_type "action/discussion/report/public_comment/consent"
	string sponsor_name
	string department "Public Works/Health Dept/Finance"
	int sequence_order
	int estimated_duration_minutes
	int actual_duration_minutes
	string outcome "approved/rejected/tabled/amended/continued"
	string vote_result "5-2/unanimous/voice vote"
	boolean requires_public_hearing
	datetime scheduled_time
	string keywords_found
	boolean oral_health_related
	}

	%% EVENT_DOCUMENT - All documents (agendas, minutes, presentations, handouts)
	%% Consolidates former AGENDA, MINUTES, DOCUMENT entities
	EVENT_DOCUMENT {
	string document_id PK
	string event_id FK
	string agenda_item_id FK "Optional - links to specific agenda item"
	string document_type "agenda/minutes/presentation/handout/staff_report/ordinance/resolution"
	string title
	string full_text
	string summary_text
	string file_url
	string pdf_url
	string file_type "pdf/docx/pptx/txt"
	int file_size_bytes
	int page_count
	string action_items "For minutes - extracted action items"
	string votes "For minutes - vote records"
	string keywords_found
	boolean oral_health_related
	datetime published_at
	datetime approved_at "For minutes"
	datetime uploaded_at
	}

	%% EVENT_MEDIA - Video recordings, livestreams, audio files
	%% Consolidates former VIDEO entity
	EVENT_MEDIA {
	string media_id PK
	string event_id FK
	string media_type "video/audio/livestream/recording"
	string platform "youtube/vimeo/granicus/zoom"
	string media_url
	string embed_url
	string thumbnail_url
	int duration_seconds
	int view_count
	string transcript_text
	string transcript_url
	string caption_language "en/es/etc"
	boolean has_captions
	string video_quality "720p/1080p/4K"
	datetime published_at
	datetime recorded_at
	}

	%% EVENT_BILL - Bills discussed or voted on at event
	%% Links events to legislative bills
	EVENT_BILL {
	string event_bill_id PK
	string event_id FK
	string bill_id FK "Links to BILL entity"
	string agenda_item_id FK "Optional - specific agenda item"
	string action_taken "introduced/discussed/committee_vote/floor_vote/signed"
	string vote_result "passed/failed/tabled"
	int yes_votes
	int no_votes
	int abstain_votes
	string discussion_summary
	datetime action_date
	}

	%% ========================================
	%% POLITICAL ECONOMY ANALYSIS
	%% ========================================
	%% Entities supporting the 4-step advocacy framework:
	%% Step 1: Rhetoric Gap (Frame Analysis)
	%% Step 2: Displacement Matrix (Budget Delta)
	%% Step 3: Influence Radar (Stakeholder Analysis)
	%% Step 4: Deferral Pattern (Temporal Voting Analysis)
	%% See: extraction/decision_analyzer.py, extraction/budget_analyzer.py

	EVENT \|\|--o{ POLICY_DECISION : produces
	POLICY_DECISION \|\|--o{ DECISION_FRAME : framed_as
	POLICY_DECISION \|\|--o{ DECISION_OPTION : considered
	POLICY_DECISION \|\|--o{ DECISION_TRADEOFF : discussed
	POLICY_DECISION \|\|--o{ STAKEHOLDER_POSITION : influenced_by
	POLICY_DECISION \|\|--o{ DECISION_VOTE : voted_on
	POLICY_DECISION {
	string decision_id PK
	string event_id FK "Links to EVENT"
	string agenda_item_id FK "Optional - links to specific agenda item"
	string decision_summary
	string outcome "approved/rejected/tabled/amended"
	string chosen_option
	string primary_frame "Economic Development/Public Safety/Equity"
	string primary_rationale
	string vote_result "5-2/unanimous/voice vote"
	int contention_score "0-100: Ratio of dissent"
	string implementation_timeline
	string cost_estimate
	float confidence_score
	datetime event_date
	datetime extracted_at
	}

	%% STEP 1: RHETORIC GAP - Frame Analysis
	DECISION_FRAME {
	string frame_id PK
	string decision_id FK
	string frame_type "primary/competing"
	string frame_name "public health/fiscal responsibility/equity"
	string framing_language "Specific phrases used"
	int mention_count
	}

	DECISION_OPTION {
	string option_id PK
	string decision_id FK
	string option_description
	boolean was_chosen
	string rejection_reason
	string opportunity_cost
	}

	DECISION_TRADEOFF {
	string tradeoff_id PK
	string decision_id FK
	string tradeoff_type "cost vs benefit/autonomy vs community"
	string discussion_text
	string evidence_cited
	}

	%% STEP 3: INFLUENCE RADAR - Stakeholder Analysis
	STAKEHOLDER_POSITION {
	string position_id PK
	string decision_id FK
	string stakeholder_name
	string stakeholder_role "Health Dept/PTA/Taxpayer Assoc"
	string stakeholder_affiliation
	string position_type "supporter/opponent/undecided"
	string main_argument
	string evidence_type "study/expert opinion/data"
	int speaking_order "Track who speaks when"
	}

	%% STEP 4: DEFERRAL PATTERN - Temporal Voting Analysis
	DECISION_VOTE {
	string vote_record_id PK
	string decision_id FK
	string leader_id FK
	string vote_value "yes/no/abstain/absent"
	string stated_reason
	boolean switched_position
	int days_since_election "Temporal context"
	}

	%% Deferral Pattern Tracking - Links same topic across multiple meetings
	POLICY_DECISION \|\|--o{ DEFERRAL_INSTANCE : part_of
	DEFERRAL_PATTERN \|\|--o{ DEFERRAL_INSTANCE : tracks
	DEFERRAL_PATTERN {
	string pattern_id PK
	string topic "Community Dental Clinic Funding"
	string conclusion "Strategic delay or genuine study"
	datetime first_mentioned "When first introduced"
	datetime last_discussed "Most recent tabling"
	int total_deferrals "How many times tabled"
	int months_in_limbo "Time since first mention"
	string pattern_type "Rationale of Attrition/Sincere Analysis/Political Timing"
	string strategic_inference "Waiting for momentum to fade"
	int discomfort_score "1-10: Political sensitivity"
	string next_review_date "When scheduled to revisit"
	boolean still_pending
	}

	DEFERRAL_INSTANCE {
	string instance_id PK
	string pattern_id FK
	string decision_id FK
	datetime deferral_date
	string stated_reason "More study needed/Budget constraints/Public input"
	string speaker "Who gave the justification"
	int months_since_first "Time elapsed"
	string previous_reason "What they said last time"
	boolean reason_changed "Shifting justifications"
	}

	%% STEP 2: DISPLACEMENT MATRIX - Budget-to-Minutes Delta
	GOVERNMENT_BUDGET \|\|--o{ BUDGET_LINE_ITEM : contains
	BUDGET_LINE_ITEM \|\|--o{ BUDGET_DELTA : analyzed_as
	BUDGET_LINE_ITEM {
	string line_item_id PK
	string budget_id FK
	string category "Education/Health/Infrastructure"
	string subcategory
	string description
	float current_amount
	float previous_amount
	float change_amount
	float percent_change
	string funding_source "grants/taxpayer/bonds"
	int fiscal_year
	}

	BUDGET_DELTA {
	string delta_id PK
	string line_item_id FK
	int event_mentions "How many times discussed at events"
	string praise_level "High/Medium/Low/None"
	string funding_change "Expansion/Stagnant/Decreased"
	string delta_type "Expansion/Lip Service/Hidden Priority/Aligned"
	float delta_score "-1 to +1: rhetoric vs reality gap"
	string stated_rationale "What they said at events"
	string inferred_rationale "What budget reveals"
	string underlying_logic "Genuine/Performative/Bureaucratic"
	datetime analyzed_at
	}

	%% QUANTITATIVE INDICATORS
	EVENT \|\|--o{ KEYWORD_DENSITY : measured_by
	KEYWORD_DENSITY {
	string density_id PK
	string event_id FK "Links to EVENT"
	string keyword "grant/taxpayer/emergency/equity"
	string keyword_category "funding_source/urgency/values"
	int occurrence_count
	float per_1000_words
	datetime analyzed_at
	}

	%% ELECTION CYCLE ANALYSIS
	JURISDICTION \|\|--o{ ELECTION_CYCLE : holds
	ELECTION_CYCLE \|\|--o{ POLICY_DECISION : influences
	ELECTION_CYCLE {
	string election_id PK
	string jurisdiction_id FK
	datetime election_date
	string election_type "municipal/school board/state"
	int decisions_12mo_before
	int decisions_6mo_before
	int decisions_3mo_before
	int decisions_6mo_after
	float avg_project_cost_before
	float avg_project_cost_after
	boolean pre_election_spike_detected
	string inference "incumbency protection/normal variance"
	}

	%% ========================================
	%% LEADERS & OFFICIALS
	%% ========================================
	%% Based on Popolo Project schema for representing people and positions
	%% See: https://github.com/popolo-project/popolo-spec
	%% Schema.org types: Person, GovernmentOfficial

	LEADER \|\|--o{ VOTE : casts
	LEADER \|\|--o{ SOCIAL_MEDIA : maintains
	LEADER {
	string leader_id PK
	string jurisdiction_id FK
	string full_name
	string title
	string position_type
	string office
	string party_affiliation
	string email
	string phone
	string website
	string photo_url
	datetime term_start
	datetime term_end
	boolean is_active
	datetime verified_at
	}

	VOTE {
	string vote_id PK
	string leader_id FK
	string event_id FK "Links to EVENT"
	string agenda_item_id FK "Optional - links to specific agenda item"
	string item_description
	string vote_value
	datetime vote_date
	}

	%% ========================================
	%% STATE LEGISLATORS & LEGISLATIVE DATA (Open States/Plural Policy)
	%% ========================================
	%% Data Source: Open States PostgreSQL dump (~10 GB)
	%% Coverage: 7,300+ state legislators across all 50 states + DC + Puerto Rico
	%% See: https://open.pluralpolicy.com/data/ and https://github.com/openstates/people/blob/master/schema.md
	%% Schema.org types: Person, GovernmentOfficial, Legislation, VoteAction
	%% Popolo Project compliance: https://www.popoloproject.com/

	JURISDICTION \|\|--o{ LEGISLATOR : represents
	LEGISLATOR \|\|--o{ LEGISLATOR_OFFICE : has
	LEGISLATOR \|\|--o{ COMMITTEE_MEMBERSHIP : serves_on
	LEGISLATOR \|\|--o{ BILL_SPONSOR : sponsors
	LEGISLATOR \|\|--o{ LEGISLATOR_VOTE : casts
	LEGISLATOR \|\|--o{ SOCIAL_MEDIA : maintains
	LEGISLATOR {
	string legislator_id PK "ocd-person/{uuid}"
	string jurisdiction_id FK "ocd-division/country:us/state:al"
	string full_name
	string given_name "First name"
	string family_name "Last name"
	string middle_name
	string suffix
	string gender "Male/Female/Other"
	string email "Official email"
	string biography "Official bio text"
	string birth_date "YYYY-MM-DD"
	string death_date "YYYY-MM-DD"
	string image_url "Official photo"
	string twitter_handle "@username"
	string youtube_handle
	string instagram_handle
	string facebook_handle
	string party_name "Democratic/Republican/Independent"
	datetime party_start_date
	datetime party_end_date
	string chamber "upper/lower/legislature"
	string district "District name/number"
	datetime term_start_date
	datetime term_end_date
	string end_reason "resignation/death/term_limit/defeated"
	string website_url
	boolean is_active
	datetime last_updated
	}

	LEGISLATOR_OFFICE {
	string office_id PK
	string legislator_id FK
	string office_type "District Office/Capitol Office"
	string address "Mailing address"
	string voice "Phone number"
	string fax "Fax number"
	string email "Office email"
	string city
	string state
	string zip_code
	boolean is_primary
	}

	COMMITTEE \|\|--o{ COMMITTEE_MEMBERSHIP : includes
	COMMITTEE {
	string committee_id PK "ocd-organization/{uuid}"
	string jurisdiction_id FK
	string name "Committee on Health and Human Services"
	string chamber "upper/lower/legislature"
	string classification "committee/subcommittee"
	string parent_committee_id FK "If subcommittee"
	datetime created_date
	datetime abolished_date
	boolean is_active
	}

	COMMITTEE_MEMBERSHIP {
	string membership_id PK
	string committee_id FK
	string legislator_id FK
	string role "chair/vice_chair/ranking_member/member"
	datetime start_date
	datetime end_date
	boolean is_active
	}

	BILL \|\|--o{ BILL_SPONSOR : has
	BILL \|\|--o{ BILL_ACTION : tracked_by
	BILL \|\|--o{ BILL_VERSION : has_versions
	BILL \|\|--o{ VOTE_EVENT : subject_of
	BILL {
	string bill_id PK "ocd-bill/{uuid}"
	string jurisdiction_id FK
	string session_id "2024 Regular Session"
	string chamber "upper/lower"
	string identifier "HB 123/SB 456"
	string title "An Act to provide dental screenings in schools"
	string classification "bill/resolution/concurrent_resolution"
	string subject "Health/Education/Budget"
	string full_text "Complete bill text"
	string summary "Bill summary"
	datetime introduced_date
	datetime first_reading_date
	datetime second_reading_date
	datetime third_reading_date
	datetime committee_referral_date
	string committee_assigned
	datetime committee_vote_date
	string committee_outcome "favorable/unfavorable"
	datetime floor_vote_date
	string floor_outcome "passed/failed"
	datetime signed_date
	datetime effective_date
	string status "introduced/committee/floor/passed/failed/signed/vetoed"
	string source_url "Link to official bill text"
	datetime last_action_date
	string last_action_description
	boolean is_oral_health_related
	datetime created_at
	datetime updated_at
	}

	BILL_SPONSOR {
	string sponsor_id PK
	string bill_id FK
	string legislator_id FK
	string sponsor_type "primary/cosponsor"
	int sponsor_order "1 for primary, 2+ for cosponsors"
	datetime sponsored_date
	}

	BILL_ACTION {
	string action_id PK
	string bill_id FK
	datetime action_date
	string action_description "Referred to Committee on Health"
	string action_type "introduction/referral/committee/amendment/vote/signing"
	string chamber "upper/lower"
	int sequence_number
	}

	BILL_VERSION {
	string version_id PK
	string bill_id FK
	string version_name "As Introduced/Committee Substitute/Enrolled"
	string full_text "Complete version text"
	string pdf_url
	datetime version_date
	string note "Amendments adopted"
	}

	VOTE_EVENT \|\|--o{ LEGISLATOR_VOTE : includes
	VOTE_EVENT {
	string vote_event_id PK "ocd-vote/{uuid}"
	string bill_id FK
	string jurisdiction_id FK
	string chamber "upper/lower"
	datetime vote_date
	string motion_text "Passage of HB 123"
	string motion_classification "passage/amendment/procedural"
	string result "passed/failed"
	int yes_count
	int no_count
	int abstain_count
	int absent_count
	int not_voting_count
	int total_count
	float pass_threshold "0.5 for simple majority, 0.67 for supermajority"
	boolean passed
	string source_url "Link to official vote record"
	}

	LEGISLATOR_VOTE {
	string legislator_vote_id PK
	string vote_event_id FK
	string legislator_id FK
	string vote_position "yes/no/abstain/absent/not_voting/excused"
	string voter_name "Name at time of vote"
	string voter_party "Party at time of vote"
	string voter_district "District at time of vote"
	}

	%% ========================================
	%% ORGANIZATIONS (NONPROFITS & CHURCHES)
	%% ========================================
	%% Based on Popolo Project schema for organizations
	%% See: https://github.com/popolo-project/popolo-spec
	%% Schema.org types: Organization, NGO, Church, WorshipPlace
	%% PRIMARY DATA SOURCE: IRS EO-BMF (1.9M+ organizations, 28 fields)

	ORGANIZATION \|\|--o{ SOCIAL_MEDIA : maintains
	ORGANIZATION \|\|--o{ LEADER : employs
	ORGANIZATION \|\|--o{ NONPROFIT_FINANCES : files
	ORGANIZATION \|\|--o{ CAMPAIGN : runs
	ORGANIZATION \|\|--o{ PROGRAM_DELIVERY : delivers
	ORGANIZATION \|\|--o{ CONGREGATION : has
	ORGANIZATION {
	string org_id PK
	string ein "Employer ID Number (IRS EO-BMF)"
	string name "Organization legal name (IRS EO-BMF)"
	string sort_name "Alphabetic sort name (IRS EO-BMF)"
	string ntee_code "NTEE classification (IRS EO-BMF)"
	string ntee_description
	string subsection_code "501(c)(3) = 03, etc (IRS EO-BMF)"
	string foundation_code "15=Public Charity, etc (IRS EO-BMF)"
	string organization_code "1=Corporation, 2=Trust (IRS EO-BMF)"
	string deductibility_status "1=Deductible (IRS EO-BMF)"
	string exempt_status_code "1=Unconditional (IRS EO-BMF)"
	string causes "Every.org tags"
	string org_type
	string state_code "2-letter state (IRS EO-BMF)"
	string city "City name (IRS EO-BMF)"
	string street_address "Street address (IRS EO-BMF)"
	string zip_code "ZIP code (IRS EO-BMF)"
	float asset_amount "Total assets (IRS EO-BMF)"
	float income_amount "Annual income (IRS EO-BMF)"
	float revenue_amount "Total revenue (IRS EO-BMF)"
	string ruling_date "Tax-exempt ruling date YYYYMM (IRS EO-BMF)"
	string tax_period "Tax period YYYYMM (IRS EO-BMF)"
	string activity_codes "Activity classification (IRS EO-BMF)"
	string group_exemption "Group ruling number (IRS EO-BMF)"
	string affiliation_code "Subordinate code (IRS EO-BMF)"
	int employee_count "From Form 990"
	string mission_statement "ProPublica/Every.org"
	string description "ProPublica/Every.org"
	string logo_url "Every.org"
	string website "Discovered URL"
	boolean is_verified "Data quality flag"
	string data_source "IRS_EO_BMF/ProPublica/EveryOrg"
	datetime last_updated
	}

	%% Churches & Congregations (org_type='church', ntee_code='X20')
	%% Data sources: IRS TEOS, ARDA, HIFLD Places of Worship, National Congregations Study
	%% Many churches provide health ministries, food programs, and community services
	CONGREGATION {
	string congregation_id PK
	string org_id FK
	string denomination
	string religious_tradition
	int congregation_size
	int weekly_attendance
	boolean has_health_ministry
	boolean has_food_program
	boolean has_youth_program
	boolean has_senior_program
	string service_schedule
	string clergy_name
	string clergy_title
	int volunteer_count
	float community_outreach_budget
	string facility_type
	int seating_capacity
	datetime founded_year
	string parent_denomination
	boolean serves_underserved
	}

	%% Nonprofit 990 Financial Data
	NONPROFIT_FINANCES {
	string filing_id PK
	string ein FK
	int tax_year
	float total_revenue
	float total_expenses
	float total_assets
	float total_liabilities
	float net_assets
	float program_expenses
	float admin_expenses
	float fundraising_expenses
	float grants_paid
	float contributions_received
	float government_grants
	float foundation_grants
	float corporate_donations
	float individual_donations
	float membership_dues
	float special_events_revenue
	float program_service_revenue
	float investment_income
	float rental_income
	float sale_of_assets
	float other_revenue
	float employee_compensation
	int employee_count
	int volunteer_count
	float overhead_ratio
	float fundraising_efficiency
	string form_990_url
	datetime filing_date
	}

	%% Grant Transactions (Individual Grants)
	ORGANIZATION \|\|--o{ GRANT : receives
	JURISDICTION \|\|--o{ GRANT : awards
	GRANT {
	string grant_id PK
	string recipient_ein FK
	string recipient_name
	string recipient_type
	string funder_name
	string funder_ein
	string funder_type
	float grant_amount
	string grant_purpose
	string program_area
	datetime award_date
	datetime start_date
	datetime end_date
	int grant_duration_months
	string grant_status
	string funding_source
	boolean multi_year
	string restrictions
	string reporting_requirements
	}

	%% ========================================
	%% MEDIA & COMMUNICATIONS
	%% ========================================

	YOUTUBE_CHANNEL {
	string channel_id PK
	string jurisdiction_id FK
	string channel_name
	string channel_url
	int subscriber_count
	int video_count
	int total_views
	string description
	datetime created_date
	datetime last_scraped
	}

	SOCIAL_MEDIA {
	string account_id PK
	string entity_id FK
	string entity_type
	string platform
	string handle
	string profile_url
	int follower_count
	int post_count
	boolean is_verified
	datetime last_updated
	}

	MEETING_PLATFORM {
	string platform_id PK
	string jurisdiction_id FK
	string platform_name
	string base_url
	string api_endpoint
	string calendar_url
	string archive_url
	boolean has_api
	datetime discovered_at
	}

	%% ========================================
	%% OPEN STATES (LEGISLATIVE DATA)
	%% ========================================

	STATE_LEGISLATURE \|\|--o{ STATE_LEGISLATOR : has_member
	STATE_LEGISLATURE \|\|--o{ STATE_BILL : introduces
	STATE_LEGISLATURE \|\|--o{ STATE_COMMITTEE : contains
	STATE_LEGISLATURE {
	string legislature_id PK
	string state_code
	string session_year
	string session_type
	datetime session_start
	datetime session_end
	}

	STATE_LEGISLATOR {
	string legislator_id PK
	string legislature_id FK
	string full_name
	string party
	string district
	string chamber
	string email
	string capitol_phone
	string photo_url
	string openstates_id
	}

	STATE_BILL {
	string bill_id PK
	string legislature_id FK
	string bill_number
	string title
	string summary
	string status
	string sponsors
	datetime introduced_date
	datetime last_action_date
	string openstates_url
	}

	STATE_COMMITTEE {
	string committee_id PK
	string legislature_id FK
	string name
	string chamber
	string parent_id FK
	string members
	}

	%% ========================================
	%% CIVIC TECH & OPEN SOURCE PROJECTS
	%% ========================================
	%% Data sources: GitHub API, Civic Tech Field Guide, Code for America, USDR, Digital Public Goods Alliance
	%% Treats open source projects as first-class civic entities

	CIVIC_TECH_PROJECT \|\|--o{ PROJECT_CONTRIBUTOR : has
	CIVIC_TECH_PROJECT \|\|--o{ PROJECT_ISSUE : tracks
	CIVIC_TECH_PROJECT \|\|--o{ PROJECT_FUNDING : receives
	CIVIC_TECH_PROJECT \|\|--o{ HACKATHON_PROJECT : originates_from
	CIVIC_TECH_PROJECT {
	string project_id PK
	string repository_url
	string github_owner
	string github_repo
	string project_name
	string description
	string readme_content
	string primary_language
	string tech_stack
	int star_count
	int fork_count
	int contributor_count
	int open_issue_count
	string license_type
	string project_category
	string civic_tech_topics
	string issue_area
	boolean is_dpg_certified
	string project_status
	string homepage_url
	string documentation_url
	datetime created_at
	datetime last_commit
	datetime last_updated
	}

	PROJECT_CONTRIBUTOR {
	string contributor_id PK
	string project_id FK
	string github_username
	string display_name
	string email
	string role
	int commit_count
	int pr_count
	int issue_count
	boolean is_maintainer
	boolean is_core_contributor
	string sponsor_url
	datetime first_contribution
	datetime last_contribution
	}

	PROJECT_ISSUE {
	string issue_id PK
	string project_id FK
	int issue_number
	string title
	string description
	string status
	string labels
	boolean is_good_first_issue
	boolean is_help_wanted
	int reaction_count
	int comment_count
	string assigned_to
	datetime created_at
	datetime closed_at
	}

	PROJECT_FUNDING {
	string funding_id PK
	string project_id FK
	string funding_source
	float monthly_revenue
	int sponsor_count
	string grant_name
	float grant_amount
	string funding_platform
	datetime funding_date
	}

	%% Hackathons & Civic Tech Events
	HACKATHON \|\|--o{ HACKATHON_PROJECT : produces
	HACKATHON \|\|--o{ HACKATHON_PARTICIPANT : includes
	HACKATHON {
	string hackathon_id PK
	string event_name
	string organizer
	string location
	string city
	string state
	datetime start_date
	datetime end_date
	string event_type
	string focus_area
	int participant_count
	int project_count
	string event_url
	string registration_url
	boolean is_virtual
	string brigade_chapter
	string sponsor_organizations
	datetime created_at
	}

	HACKATHON_PROJECT {
	string hackathon_project_id PK
	string hackathon_id FK
	string project_id FK
	string project_name
	string description
	string team_members
	string repository_url
	string demo_url
	string presentation_url
	boolean won_award
	string award_category
	boolean became_ongoing_project
	datetime created_at
	}

	HACKATHON_PARTICIPANT {
	string participant_id PK
	string hackathon_id FK
	string participant_name
	string email
	string github_username
	string role
	string skills
	string team_name
	datetime registered_at
	}

	%% Code for America Brigades
	BRIGADE_CHAPTER \|\|--o{ HACKATHON : hosts
	BRIGADE_CHAPTER \|\|--o{ CIVIC_TECH_PROJECT : maintains
	BRIGADE_CHAPTER {
	string brigade_id PK
	string brigade_name
	string city
	string state
	string website_url
	string github_org
	string meetup_url
	int member_count
	int project_count
	string meeting_schedule
	string contact_email
	boolean is_active
	datetime founded_date
	datetime last_activity
	}

	%% ========================================
	%% WIKIDATA ENTITIES
	%% ========================================

	WIKIDATA_ENTITY \|\|--o{ WIKIDATA_RELATIONSHIP : source
	WIKIDATA_ENTITY \|\|--o{ WIKIDATA_RELATIONSHIP : target
	WIKIDATA_ENTITY {
	string wikidata_id PK
	string entity_type
	string label
	string description
	string wikipedia_url
	string image_url
	string aliases
	string properties
	datetime last_updated
	}

	WIKIDATA_RELATIONSHIP {
	string relationship_id PK
	string source_id FK
	string target_id FK
	string predicate
	datetime start_date
	datetime end_date
	string qualifiers
	}

	%% ========================================
	%% COMMUNITY SOLUTIONS - USE CASE TEMPLATES
	%% ========================================

	COMMUNITY_SOLUTION \|\|--o{ SOLUTION_STAKEHOLDER : involves
	COMMUNITY_SOLUTION \|\|--o{ SOLUTION_METRIC : tracks
	COMMUNITY_SOLUTION {
	string solution_id PK
	string title
	string challenge_description
	string engagement_level "Spectrum: Inform/Consult/Involve/Collaborate/Defer"
	string sector_focus "CBOs/City-County/Philanthropy/Facilitative Leaders"
	string harvard_use_case_id "Links to Harvard Data-Smart catalog"
	string brookings_academy_id "Links to Data Academy programs"
	string ocd_division_id FK "Jurisdiction implementing solution"
	date implementation_start
	date implementation_end
	string status "Planning/Active/Completed"
	string outcome_summary
	string data_sources "Which datasets used"
	timestamp created_at
	timestamp updated_at
	}

	SOLUTION_STAKEHOLDER {
	string stakeholder_id PK
	string solution_id FK
	string stakeholder_type "CBO/Government/Funder/Facilitator"
	string organization_id "Links to NONPROFIT/JURISDICTION/GRANT_MAKER/OFFICIAL"
	string role_description
	string engagement_level
	boolean is_lead_organization
	timestamp created_at
	timestamp updated_at
	}

	SOLUTION_METRIC {
	string metric_id PK
	string solution_id FK
	string metric_name
	string metric_type "KPI/Output/Outcome/Impact"
	string data_source "Which dataset(s)"
	string metric_view_id "Links to /analytics/metric_views"
	decimal baseline_value
	decimal target_value
	decimal current_value
	date measurement_date
	string notes
	timestamp created_at
	timestamp updated_at
	}

	%% ========================================
	%% DBPEDIA ENTITIES
	%% ========================================

	DBPEDIA_RESOURCE {
	string resource_uri PK
	string label
	string description
	string categories
	string classes
	string infobox_properties
	string wikipedia_url
	int ref_count
	datetime extracted_at
	}

	%% ========================================
	%% GOOGLE CIVIC DATA
	%% ========================================

	CIVIC_DIVISION \|\|--o{ CIVIC_REPRESENTATIVE : has
	CIVIC_DIVISION {
	string ocd_id PK
	string division_name
	string division_type
	string state
	string county
	string office_types
	string boundaries_geojson
	}

	CIVIC_REPRESENTATIVE {
	string representative_id PK
	string ocd_id FK
	string name
	string office_name
	string party
	string phones
	string emails
	string urls
	string social_channels
	string photo_url
	}

	CIVIC_ELECTION {
	string election_id PK
	string name
	datetime election_day
	string ocd_division FK
	string contests
	string polling_locations
	}

	%% ========================================
	%% USER & SOCIAL FEATURES
	%% ========================================

	USER \|\|--o{ USER_FOLLOW : follows
	USER \|\|--o{ LEADER_FOLLOW : follows_leader
	USER \|\|--o{ ORG_FOLLOW : follows_org
	USER \|\|--o{ CAUSE_FOLLOW : follows_cause
	USER {
	int user_id PK
	string email
	string username
	string full_name
	string oauth_provider
	string state
	string county
	string city
	string school_board
	boolean profile_completed
	datetime created_at
	}

	USER_FOLLOW {
	int id PK
	int follower_id FK
	int following_id FK
	datetime created_at
	}

	LEADER_FOLLOW {
	int id PK
	int user_id FK
	string leader_id FK
	datetime created_at
	}

	ORG_FOLLOW {
	int id PK
	int user_id FK
	string org_id FK
	datetime created_at
	}

	CAUSE \|\|--o{ CAUSE_FOLLOW : followed_by
	CAUSE {
	int cause_id PK
	string name
	string slug
	string description
	string category
	string icon_url
	string color
	int follower_count
	}

	CAUSE_FOLLOW {
	int id PK
	int user_id FK
	int cause_id FK
	datetime created_at
	}

	%% ========================================
	%% MEETINGBANK (HUGGINGFACE DATASET)
	%% ========================================

	MEETINGBANK_MEETING {
	string instance_id PK
	string city_name
	datetime meeting_date
	string transcript_text
	string summary_text
	string source_url
	string split
	datetime ingested_at
	}

	%% ========================================
	%% MICROSOFT CDM: NONPROFIT CONSTITUENT MANAGEMENT
	%% Based on Microsoft Common Data Model for Nonprofits
	%% See: https://github.com/microsoft/Nonprofits/
	%% ========================================

	CONSTITUENT \|\|--o{ DONATION : makes
	CONSTITUENT \|\|--o{ MEMBERSHIP : enrolls_in
	CONSTITUENT \|\|--o{ VOLUNTEER_ACTIVITY : participates_in
	CONSTITUENT {
	string constituent_id PK
	string constituent_type "Donor, Volunteer, Member, Beneficiary"
	string first_name
	string last_name
	string email
	string phone
	string address
	string city
	string state_code
	string zip_code
	datetime first_engagement_date
	datetime last_engagement_date
	float lifetime_giving_total
	int volunteer_hours_total
	string preferred_communication
	boolean is_active
	datetime created_at
	}

	CAMPAIGN \|\|--o{ DONATION : receives
	ORGANIZATION \|\|--o{ CAMPAIGN : runs
	CAMPAIGN {
	string campaign_id PK
	string org_id FK
	string campaign_name
	string campaign_type "Annual Fund, Capital, Major Gifts, Peer-to-Peer"
	datetime start_date
	datetime end_date
	float goal_amount
	float raised_amount
	int donor_count
	string status "Planning, Active, Completed, Cancelled"
	string description
	datetime created_at
	}

	DONATION \|\|--o\| DESIGNATION : allocated_to
	DONATION {
	string donation_id PK
	string constituent_id FK
	string campaign_id FK
	string designation_id FK "Program, Fund, or Campaign"
	float amount
	string donation_type "Cash, Stock, In-Kind, Pledge"
	datetime donation_date
	string payment_method "Check, Credit Card, ACH, Wire"
	string acknowledgment_status "Pending, Sent, Thanked"
	boolean is_recurring
	string recurring_frequency "Monthly, Quarterly, Annual"
	string receipt_number
	datetime created_at
	}

	DESIGNATION {
	string designation_id PK
	string designation_name "General Fund, Building Fund, Program X"
	string designation_type "Unrestricted, Restricted, Endowment"
	string fund_code
	float current_balance
	string description
	}

	MEMBERSHIP {
	string membership_id PK
	string constituent_id FK
	string org_id FK
	string membership_type "Individual, Family, Corporate, Lifetime"
	datetime start_date
	datetime end_date
	float membership_fee
	string status "Active, Expired, Cancelled, Grace Period"
	boolean auto_renew
	datetime renewal_date
	string benefits "Newsletter, Events, Discounts"
	datetime created_at
	}

	VOLUNTEER_ACTIVITY {
	string activity_id PK
	string constituent_id FK
	string org_id FK
	string activity_type "Event, Ongoing, Skilled, Board Service"
	datetime activity_date
	float hours_logged
	string role "Coordinator, Assistant, Specialist"
	string skills_used "IT, Legal, Marketing, Manual Labor"
	string supervisor
	string notes
	datetime created_at
	}

	PROGRAM_DELIVERY \|\|--o{ PROGRAM_OUTCOME : achieves
	ORGANIZATION \|\|--o{ PROGRAM_DELIVERY : delivers
	JURISDICTION \|\|--o{ PROGRAM_DELIVERY : serves
	PROGRAM_DELIVERY {
	string program_id PK "Also iati-identifier"
	string org_id FK
	string program_name
	string program_type "Direct Service, Advocacy, Education, Research"
	string target_population "Youth, Seniors, Low-Income, Veterans"
	int beneficiaries_served
	datetime start_date "IATI: activity-date[@type='start-planned']"
	datetime end_date "IATI: activity-date[@type='end-actual']"
	float program_budget "IATI: budget[@type='original']"
	float program_expenses "IATI: transaction[@type='4']"
	string status "Active, Completed, On Hold"
	string description
	string iati_identifier "Unique IATI activity ID"
	string iati_activity_status "1=Pipeline, 2=Active, 3=Completed, 4=Suspended, 5=Cancelled"
	string iati_sector_code "OECD DAC 5-digit sector code"
	string iati_sector_vocabulary "DAC, NTEE, Custom"
	string recipient_country_code "ISO 3166-1 alpha-2"
	string recipient_region "Sub-national region"
	datetime created_at
	}

	PROGRAM_OUTCOME {
	string outcome_id PK
	string program_id FK
	string outcome_name "Literacy Rate, Job Placement, Health Improvement"
	string metric_type "Percentage, Count, Score, Binary"
	float baseline_value "IATI: baseline[@year, @value]"
	float target_value "IATI: target[@value]"
	float actual_value "IATI: actual[@value]"
	string measurement_period "Monthly, Quarterly, Annual"
	datetime measurement_date
	datetime period_start "IATI: period-start[@iso-date]"
	datetime period_end "IATI: period-end[@iso-date]"
	string data_source "Survey, Administrative, Third-Party"
	string iati_result_type "1=Output, 2=Outcome, 3=Impact, 9=Other"
	string iati_indicator_measure "1=Unit, 2=Percentage, 3=Nominal, 4=Ordinal, 5=Qualitative"
	boolean iati_ascending "True if higher is better"
	string notes
	datetime created_at
	}

	%% ========================================
	%% BALLOT MEASURES & ADVOCACY
	%% Data Sources: Ballotpedia (comprehensive measures),
	%% MIT Election Lab (federal results),
	%% OpenElections (certified state results)
	%% See: ballot-election-sources.md
	%% Schema.org type: Legislation (with referendumProposal property)
	%% ========================================

	JURISDICTION \|\|--o{ BALLOT_MEASURE : hosts
	STATE_LEGISLATURE \|\|--o{ BALLOT_MEASURE : proposes
	POLICY_TOPIC \|\|--o{ BALLOT_MEASURE : addresses
	BALLOT_MEASURE {
	string measure_id PK
	string jurisdiction_id FK "OCD-ID format"
	string state_code "Two-letter code"
	datetime election_date
	string measure_number "Proposition 15, Question 2"
	string title "Measure title"
	string description "Full description"
	string measure_type "Initiative, Referendum, Bond"
	string topic_category "fluoridation, education, tax"
	string status "qualified, certified, passed, failed"
	string result "passed, failed, pending"
	int yes_votes "Total yes votes"
	int no_votes "Total no votes"
	float yes_percentage "Yes vote percentage"
	string full_text_url "Official measure text"
	string ballotpedia_url "Ballotpedia reference"
	string openelections_source "OpenElections CSV file"
	datetime created_at
	}

	POLICY_TOPIC \|\|--o{ MEETING : discussed_in
	POLICY_TOPIC \|\|--o{ LEGISLATION : addresses
	POLICY_TOPIC \|\|--o{ SURVEY_VARIABLE : measured_by
	POLICY_TOPIC {
	string topic_id PK
	string topic_name "Water Fluoridation Support"
	string category "health_policy"
	string description "Public opinion on fluoridation"
	string keywords "fluoride, water treatment"
	int priority_level "1-10 importance ranking"
	string icon "🦷"
	int jurisdiction_count "How many jurisdictions discuss"
	datetime created_at
	}

	%% ========================================
	%% PUBLIC OPINION SURVEYS & POLLING
	%% Data Sources: Roper Center iPoll, Pew Research, Gallup, ANES
	%% Standardized survey metadata and response data
	%% ========================================

	SURVEY_PROVIDER \|\|--o{ SURVEY : conducts
	SURVEY_PROVIDER {
	string provider_id PK
	string provider_name "Gallup, Pew Research Center, Roper Center"
	string organization_type "Academic, Commercial, Government, Nonprofit"
	string country "US, UK, International"
	string website_url
	string methodology_url
	boolean is_member_aapor "American Association for Public Opinion Research"
	boolean is_member_ncpp "National Council on Public Polls"
	string data_access_policy "Open, Restricted, Membership, Purchase"
	datetime founded_date
	datetime created_at
	}

	SURVEY \|\|--o{ SURVEY_VARIABLE : contains
	JURISDICTION \|\|--o{ SURVEY : targets
	SURVEY {
	string survey_id PK
	string provider_id FK
	string study_name "Gallup Poll Social Series: Environment"
	string study_number "USGALLUP.031915 (Roper format)"
	string roper_ipoll_id "Unique iPoll identifier"
	datetime field_start_date "First day of data collection"
	datetime field_end_date "Last day of data collection"
	int sample_size "Total respondents"
	string population "U.S. adults 18+, Registered voters"
	string sampling_method "Random digit dial, Address-based, Online panel"
	string mode "Telephone, Web, In-person, Mail, Mixed"
	float response_rate "AAPOR RR1 response rate percentage"
	float margin_of_error "95% confidence interval MOE"
	string weighting_variables "Age, gender, race, education, region"
	string geographic_coverage "National, State, County, City"
	string jurisdiction_id FK "If targeted to specific jurisdiction"
	string funding_source "NSF, Private foundation, Media sponsor"
	string data_collection_firm "Contract research organization"
	boolean is_cross_sectional "True if one-time, False if panel/longitudinal"
	string language "English, Spanish, Bilingual"
	string questionnaire_url "Link to full questionnaire PDF"
	string topline_results_url "Frequency tables"
	string microdata_url "Individual-level data file"
	datetime created_at
	}

	SURVEY_VARIABLE \|\|--o{ SURVEY_RESPONSE : has_responses
	POLICY_TOPIC \|\|--o{ SURVEY_VARIABLE : measures
	SURVEY_VARIABLE {
	string variable_id PK
	string survey_id FK
	string topic_id FK "Links to POLICY_TOPIC"
	string variable_name "Q12, V0023, FLUORIDE_SUPPORT"
	string question_text "Do you favor or oppose adding fluoride to your community's water supply?"
	string question_wording_exact "Full verbatim question including intro"
	int question_number "Order in questionnaire"
	string variable_label "Fluoride support"
	string response_type "Single choice, Multiple choice, Numeric, Text"
	string scale_type "Binary, Likert 5-point, 0-10, Thermometer"
	string response_options_json "JSON array of coded responses"
	boolean is_required "Skip logic"
	string filter_condition "If Q11=Yes, ask Q12"
	string universe "All respondents, Homeowners only, etc"
	string notes "Interviewer instructions, context"
	datetime created_at
	}

	SURVEY_RESPONSE {
	string response_id PK
	string variable_id FK
	string jurisdiction_id FK "For geographic crosstabs"
	string response_value "Favor, Oppose, Don't know"
	int response_code "1, 2, 98"
	int unweighted_count "Raw number of respondents"
	int weighted_count "Weighted frequency"
	float percentage "Weighted percentage"
	float standard_error "SE for percentage estimate"
	string demographic_filter "Age 18-29, College grad, Republican"
	string geographic_filter "Northeast region, Urban, Alabama"
	string crosstab_type "Total, By age, By education, By party ID"
	datetime created_at
	}

	JURISDICTION \|\|--o{ LEGISLATION : enacts
	STATE_LEGISLATURE \|\|--o{ LEGISLATION : proposes
	%% Schema.org type: Legislation
	LEGISLATION {
	string bill_id PK
	string jurisdiction_id FK
	string state_code
	string bill_number
	string title
	string description
	string status
	string sponsor
	datetime introduced_date
	datetime passed_date
	datetime effective_date
	string full_text_url
	string openstates_url
	string topic_category
	string chamber
	int vote_yes
	int vote_no
	}

	%% ========================================
	%% POLITICAL CAMPAIGN FINANCE (FEC DATA)
	%% ========================================
	%% Data Source: OpenFEC API (https://api.open.fec.gov/developers/)
	%% Tracks individual political contributions, candidates, and committees
	%% Links to nonprofits via employer matching and officer name matching

	JURISDICTION \|\|--o{ POLITICAL_CANDIDATE : represents
	POLITICAL_CANDIDATE \|\|--o{ POLITICAL_CONTRIBUTION : receives
	POLITICAL_COMMITTEE \|\|--o{ POLITICAL_CONTRIBUTION : receives
	ORGANIZATION \|\|--o{ NONPROFIT_POLITICAL_DONOR : employs
	POLITICAL_CONTRIBUTION \|\|--o{ NONPROFIT_POLITICAL_DONOR : matched_from

	POLITICAL_CANDIDATE {
	string candidate_id PK "FEC candidate ID"
	string state_code FK
	string candidate_name
	string party "DEM, REP, IND, etc"
	string office "H=House, S=Senate, P=President"
	string office_full "House, Senate, President"
	string district "Congressional district (for House)"
	string election_year
	string incumbent_challenge "I=Incumbent, C=Challenger, O=Open"
	string candidate_status "Active, Inactive"
	int cycle "Election cycle year"
	datetime created_at
	}

	POLITICAL_COMMITTEE {
	string committee_id PK "FEC committee ID"
	string state_code FK
	string committee_name
	string committee_type "H=House, S=Senate, P=Presidential, N=PAC, Q=Super PAC"
	string committee_type_full
	string designation "P=Principal, A=Authorized, J=Joint"
	string designation_full
	string party "DEM, REP, etc"
	string treasurer_name
	string organization_type
	string filing_frequency
	datetime created_at
	}

	POLITICAL_CONTRIBUTION {
	string contribution_id PK "FEC sub_id"
	string state_code FK
	string contributor_name
	string contributor_city
	string contributor_state
	string contributor_zip
	string contributor_employer "Links to ORGANIZATION"
	string contributor_occupation
	float contribution_amount
	datetime contribution_date
	string recipient_committee_id FK
	string recipient_committee_name
	string candidate_id FK
	string candidate_name
	string election_type
	string entity_type "Individual, Committee, etc"
	string memo_text
	datetime created_at
	}

	%% Analysis table linking political contributions to nonprofit leadership
	NONPROFIT_POLITICAL_DONOR {
	string donor_analysis_id PK
	string ein FK "Links to ORGANIZATION"
	string organization_name
	string contributor_name
	string contributor_title "Inferred from employer or matched from officers"
	float contribution_amount
	datetime contribution_date
	string recipient_name "Committee or candidate name"
	string candidate_name
	string match_method "Employer Name, Officer Name Match"
	datetime created_at
	}

	%% ========================================
	%% SYSTEM-INTERNAL VOCABULARY TABLES
	%% (OMOP-Inspired Concept System)
	%% ========================================
	%% Based on OHDSI Athena vocabulary structure
	%% ID range: 2,000,000,000+ for custom civic concepts
	%% These tables appear at bottom to avoid confusing non-technical users

	VOCABULARY \|\|--o{ CONCEPT : contains
	CONCEPT_CLASS \|\|--o{ CONCEPT : categorizes
	DOMAIN \|\|--o{ CONCEPT : groups
	CONCEPT \|\|--o{ CONCEPT_RELATIONSHIP : source
	CONCEPT \|\|--o{ CONCEPT_RELATIONSHIP : target

	%% Concept references from other entities
	CONCEPT \|\|--o{ JURISDICTION : city_concept_id
	CONCEPT \|\|--o{ ORGANIZATION : organization_concept_id
	CONCEPT \|\|--o{ LEADER : position_concept_id
	CONCEPT \|\|--o{ POLICY_TOPIC : topic_concept_id

	VOCABULARY {
	string vocabulary_id PK "OCD_ID, IRS_NTEE, US_Census, OHDSI_Gender"
	string vocabulary_name
	string vocabulary_reference "URL or citation"
	string vocabulary_version
	string vocabulary_concept_id FK
	}

	DOMAIN {
	string domain_id PK "Jurisdiction, Nonprofit, Policy, Gender"
	string domain_name
	string domain_concept_id FK
	}

	CONCEPT_CLASS {
	string concept_class_id PK "City, County, 501c3, Mayor"
	string concept_class_name
	string concept_class_concept_id FK
	}

	CONCEPT {
	int concept_id PK "2B+ for custom civic concepts"
	string concept_name "Display name"
	string domain_id FK "Jurisdiction, Nonprofit, Policy"
	string vocabulary_id FK "OCD_ID, IRS_NTEE, US_Census"
	string concept_class_id FK "City, County, 501c3, Mayor"
	string standard_concept "S=Standard, C=Classification"
	string concept_code "External identifier"
	datetime valid_start_date
	datetime valid_end_date
	string invalid_reason
	}

	CONCEPT_RELATIONSHIP {
	int concept_id_1 FK "Source concept"
	int concept_id_2 FK "Target concept"
	string relationship_id "Is part of, Regulates, Addresses"
	datetime valid_start_date
	datetime valid_end_date
	string invalid_reason
	}
	`}
	/>

	## ⚖️ Political Economy Analysis Framework

	The ERD includes specialized entities for political economy analysis - understanding the "WHY" behind government decisions, not just the "WHAT". These entities support a 4-step advocacy framework to expose gaps between rhetoric and reality:

	### The 4-Step Framework for Effective Change

	#### Step 1: Rhetoric Gap - Frame Analysis
	Goal: Establish they ALREADY agree it's important (stop the "need" debate)

	ERD Support:
	- DECISION_FRAME: Tracks how decisions are framed ("public health" vs "fiscal responsibility" vs "equity")
	- KEYWORD_DENSITY: Measures rhetoric patterns ("priority", "essential", "critical" per 1000 words)
	- POLICY_DECISION.primary_frame: Primary framing language used

	Analysis Output:
	```
	Frame Distribution:
	12x public health
	8x fiscal responsibility
	5x equity/access
	3x economic development

	→ They SAY oral health is a "priority" - hold them to it!
	```

	#### Step 2: Displacement Matrix - Budget-to-Minutes Delta
	Goal: Show they HAD the money (stop the "budget constraint" excuse)

	ERD Support:
	- BUDGET_LINE_ITEM: Detailed budget categories with year-over-year changes
	- BUDGET_DELTA: Compares meeting rhetoric to actual funding changes
	- BUDGET_DELTA.delta_type: Classifies as "Expansion", "Lip Service", or "Hidden Priority"
	- BUDGET_DELTA.delta_score: Quantifies rhetoric vs reality gap (-1 to +1)

	Analysis Output:
	```
	🎭 Lip Service Detected:
	• School dental program: -$50,000 decrease
	Mentioned 12x in meetings as "critical for children"
	Logic: PERFORMATIVE POLITICS

	💰 Hidden Priority (Where the money REALLY went):
	• IT Infrastructure: +$200,000 increase
	Only mentioned 1x in meetings
	Logic: Bureaucratic inertia - avoiding scrutiny
	```

	#### Step 3: Influence Radar - Stakeholder Analysis
	Goal: Name who's blocking it (force personal accountability)

	ERD Support:
	- STAKEHOLDER_POSITION: Who spoke for/against with their arguments
	- STAKEHOLDER_POSITION.speaking_order: Track who speaks when (early speakers often most influential)
	- DECISION_VOTE: Individual vote records with stated reasons
	- DECISION_VOTE.switched_position: Flag when officials change their stance

	Analysis Output:
	```
	👥 Blocking Coalition:
	• Taxpayer Association (opponent) - spoke 1st
	Argument: "Cost concerns in tight budget"
	Counter: School nurses budget UP $300K same meeting

	• Council Member Smith (voted NO)
	Stated reason: "Need more study"
	Pattern: Has voted NO on 3 oral health items since 2022
	```

	#### Step 4: Deferral Pattern - Temporal Voting Analysis
	Goal: Show they're stalling, not studying (expose the tactic)

	ERD Support:
	- DEFERRAL_PATTERN: Tracks same topic across multiple meetings
	- `first_mentioned`: When decision was first introduced
	- `last_discussed`: Most recent tabling date
	- `total_deferrals`: How many times tabled/postponed
	- `months_in_limbo`: Time elapsed since first mention
	- `pattern_type`: "Rationale of Attrition" / "Sincere Analysis" / "Political Timing"
	- `strategic_inference`: Inferred reason for delay
	- `next_review_date`: When scheduled to revisit (if stated)
	- DEFERRAL_INSTANCE: Individual tabling events with dates
	- `deferral_date`: When it was tabled
	- `stated_reason`: Official justification given
	- `speaker`: Who gave the reason
	- `months_since_first`: Time elapsed
	- `reason_changed`: Flag for shifting justifications
	- POLICY_DECISION.outcome: Captures "tabled/deferred/postponed"
	- DECISION_OPTION.rejection_reason: Stated excuses for delay
	- ELECTION_CYCLE: Links decisions to election timelines
	- ELECTION_CYCLE.pre_election_spike_detected: Flag incumbent protection patterns

	Complete Date Tracking:
	```sql
	-- All dates needed to track stalling:
	MEETING.meeting_date -- When meeting occurred
	POLICY_DECISION.meeting_date -- When decision was discussed
	DEFERRAL_PATTERN.first_mentioned -- First introduction date
	DEFERRAL_PATTERN.last_discussed -- Most recent tabling
	DEFERRAL_INSTANCE.deferral_date -- Each individual deferral
	ELECTION_CYCLE.election_date -- Election timing context
	DECISION_VOTE.days_since_election -- Temporal political context
	```

	Analysis Output:
	```
	📅 Stalling Pattern DETECTED:
	• Topic: "Fluoridation proposal"
	• First mentioned: 2020-03-15 (4 years, 1 month ago)
	• Total deferrals: 4 times
	• Pattern type: Rationale of Attrition

	📊 Timeline of Shifting Justifications:
	- 2020-03: "Need more study" (12mo before election)
	- 2020-09: "Budget constraints" (6mo before election)
	- 2022-01: "Need public input" (new council members)
	- 2024-05: "Awaiting staff report" (still pending)

	🔍 Strategic Inference:
	"They're NOT studying - they're AVOIDING! The board isn't
	debating the merit; they're waiting for the advocate's
	momentum to fade before the next election cycle."

	🚨 Discomfort Score: 10/10 (Extremely politically sensitive)
	```

	### Quantitative "Why" Indicators

	Additional metrics to infer governance logic:

	\| Entity \| Metric \| Reveals \|
	\|--------\|--------\|---------\|
	\| POLICY_DECISION.contention_score \| Ratio of dissent (0-100) \| Political sensitivity of topic \|
	\| KEYWORD_DENSITY \| "grant" vs "taxpayer" frequency \| Decision driver: outside funding vs local demand \|
	\| KEYWORD_DENSITY \| "emergency" occurrence \| Reactive vs planned governance \|
	\| ELECTION_CYCLE.avg_project_cost_before \| Spending spikes pre-election \| Incumbency protection tactics \|
	\| BUDGET_DELTA.underlying_logic \| Genuine vs Performative vs Bureaucratic \| Real priorities revealed \|

	### Implementation Files

	These analyses are fully implemented in the codebase:

	- `extraction/decision_analyzer.py` - Frame analysis, stakeholder extraction
	- `extraction/budget_analyzer.py` - Budget delta calculation, opportunity cost mapping
	- `extraction/temporal_analyzer.py` - Election cycle analysis, deferral patterns
	- `examples/tuscaloosa_political_economy.py` - Complete end-to-end analysis

	See [Political Economy Analysis Guide](/docs/guides/political-economy) for detailed implementation status and usage examples.

	---

	## 🌐 Data Standards & Interoperability

	### Popolo Project Alignment

	Our data model follows the [Popolo Project](https://www.popoloproject.com/) specification for representing people, organizations, and elected positions. Popolo is an international open government data standard adopted by 30+ civic tech organizations worldwide including mySociety, Sunlight Foundation, OpenNorth, and Civic Commons.

	#### Popolo Class Mappings

	\| Popolo Class \| Our Entity \| Description \| Key Fields \|
	\|--------------\|------------\|-------------\|------------\|
	\| Person \| LEADER \| Elected officials, government employees \| `full_name`, `email`, `phone`, `photo_url` \|
	\| Organization \| ORGANIZATION \| Nonprofits, government agencies, companies \| `name`, `org_type`, `address`, `website` \|
	\| Membership \| LEADER ↔ ORGANIZATION \| Relationship between people and organizations \| `leader_id`, `jurisdiction_id`, `term_start`, `term_end` \|
	\| Post \| LEADER.position_type \| Positions within organizations \| `title`, `office`, `position_type` \|
	\| Contact Detail \| Embedded fields \| Communication methods \| `email`, `phone`, `website` in multiple entities \|
	\| Motion \| AGENDA, LEGISLATION \| Formal proposals for decision \| `title`, `description`, `status` \|
	\| Vote Event \| VOTE \| Voting on motions/bills \| `vote_date`, `item_description` \|
	\| Count \| VOTE, LEGISLATION \| Vote tallies \| `vote_yes`, `vote_no`, `vote_value` \|
	\| Area \| JURISDICTION \| Geographic/political boundaries \| `jurisdiction_type`, `state_code`, `county_name` \|
	\| Event \| MEETING \| Gatherings with agendas \| `meeting_date`, `meeting_type`, `body_name` \|
	\| Speech \| MINUTES, VIDEO \| Spoken statements \| `transcript_text`, `summary_text` \|

	#### Schema.org Type Mappings

	Our entities map to [Schema.org](https://schema.org/) types for SEO-optimized structured data and semantic web compatibility:

	\| Our Entity \| Schema.org Type \| Properties \| JSON-LD Export \|
	\|------------\|----------------\|------------\|----------------\|
	\| JURISDICTION \| [AdministrativeArea](https://schema.org/AdministrativeArea) \| name, address, geo, telephone, url \| ✅ City/county pages \|
	\| MEETING \| [Event](https://schema.org/Event) \| name, startDate, endDate, location, organizer \| ✅ Google Calendar rich results \|
	\| LEADER \| [Person](https://schema.org/Person) + [GovernmentOfficial](https://schema.org/GovernmentOfficial) \| name, email, telephone, jobTitle \| ✅ Official profiles \|
	\| ORGANIZATION \| [Organization](https://schema.org/Organization) + [NGO](https://schema.org/NGO) \| name, address, telephone, foundingDate \| ✅ Nonprofit listings \|
	\| LEGISLATION \| [Legislation](https://schema.org/Legislation) \| name, legislationDate, legislationPassedBy \| ✅ Bill tracking \|
	\| BALLOT_MEASURE \| [Legislation](https://schema.org/Legislation) \| name, datePosted, legislationChanges \| ✅ Ballot guides \|
	\| VOTE \| [VoteAction](https://schema.org/VoteAction) \| agent, candidate, actionOption \| ✅ Voting records \|
	\| FACT_CHECK \| [ClaimReview](https://schema.org/ClaimReview) \| claimReviewed, reviewRating, author \| ✅ Google Fact Check Explorer \|
	\| SCHOOL_DISTRICT \| [EducationalOrganization](https://schema.org/EducationalOrganization) \| name, numberOfStudents, address \| ✅ School district info \|
	\| VIDEO \| [VideoObject](https://schema.org/VideoObject) \| name, description, uploadDate, duration \| ✅ YouTube integration \|
	\| DOCUMENT \| [DigitalDocument](https://schema.org/DigitalDocument) \| name, fileFormat, datePublished \| ✅ Document library \|
	\| Microsoft CDM Nonprofit Entities \| \| \| \|
	\| CONSTITUENT \| [Person](https://schema.org/Person) \| name, email, telephone, address \| ✅ Donor/volunteer profiles \|
	\| DONATION \| [DonateAction](https://schema.org/DonateAction) \| agent (Person), recipient (Organization), price \| ✅ Donation receipts \|
	\| CAMPAIGN \| [FundingScheme](https://schema.org/FundingScheme) \| name, startDate, endDate, url \| ✅ Fundraising campaigns \|
	\| MEMBERSHIP \| [ProgramMembership](https://schema.org/ProgramMembership) \| member (Person), hostingOrganization, membershipNumber \| ✅ Member cards \|
	\| VOLUNTEER_ACTIVITY \| [VolunteerAction](https://schema.org/VolunteerAction) \| agent (Person), startTime, endTime, location \| ✅ Volunteer tracking \|
	\| PROGRAM_DELIVERY \| [Service](https://schema.org/Service) \| name, provider, serviceType, areaServed \| ✅ Program catalog \|
	\| PROGRAM_OUTCOME \| [Observation](https://schema.org/Observation) \| measurementTechnique, measuredValue, observationDate \| ✅ Impact reporting \|

	Example: Meeting as Schema.org Event

	```json
	{
	"@context": "https://schema.org",
	"@type": "Event",
	"@id": "https://www.communityone.com/meetings/city-council-2024-01-15",
	"name": "Birmingham City Council Regular Meeting",
	"description": "Monthly city council meeting covering budget, zoning, and public health initiatives",
	"startDate": "2024-01-15T18:00:00-06:00",
	"endDate": "2024-01-15T20:30:00-06:00",
	"eventStatus": "https://schema.org/EventScheduled",
	"eventAttendanceMode": "https://schema.org/MixedEventAttendanceMode",
	"location": {
	"@type": "Place",
	"name": "Birmingham City Hall",
	"address": {
	"@type": "PostalAddress",
	"streetAddress": "710 N 20th St",
	"addressLocality": "Birmingham",
	"addressRegion": "AL",
	"postalCode": "35203"
	}
	},
	"organizer": {
	"@type": "GovernmentOrganization",
	"name": "Birmingham City Council",
	"url": "https://www.birminghamal.gov/council/"
	},
	"recordedIn": {
	"@type": "VideoObject",
	"name": "City Council Meeting Recording",
	"uploadDate": "2024-01-16",
	"duration": "PT2H30M",
	"thumbnailUrl": "https://example.com/thumbnail.jpg",
	"contentUrl": "https://youtube.com/watch?v=example"
	},
	"subEvent": [
	{
	"@type": "Event",
	"name": "Public Comment Period",
	"startDate": "2024-01-15T18:15:00-06:00"
	}
	]
	}
	```

	#### Common Education Data Standards (CEDS) Alignment

	Our SCHOOL_DISTRICT entity follows [CEDS](https://ceds.ed.gov/) specifications for education data interoperability:

	\| Our Field \| CEDS Element \| Element ID \| NCES Alignment \|
	\|-----------\|--------------\|------------\|----------------\|
	\| `nces_id` \| LEA Identifier (NCES) \| 000827 \| CCD LEA ID \|
	\| `district_name` \| Name of Institution \| 000168 \| Official district name \|
	\| `district_type` \| LEA Type \| 000108 \| Regular/Specialized/Service Agency \|
	\| `total_students` \| Student Count \| 001475 \| Fall enrollment \|
	\| `total_schools` \| Number of Schools \| 000856 \| Operational schools count \|
	\| `total_revenue` \| Total Revenue \| 000612 \| F-33 Survey Line A09 \|
	\| `total_expenditures` \| Total Expenditures \| 000611 \| F-33 Survey Line B13 \|
	\| `per_pupil_spending` \| Expenditure per Student \| 000613 \| Total exp / enrollment \|
	\| `federal_revenue` \| Federal Revenue \| 000614 \| ESEA Title I, IDEA \|
	\| `state_revenue` \| State Revenue \| 000615 \| State aid formulas \|
	\| `local_revenue` \| Local Revenue \| 000616 \| Property tax, bonds \|
	\| `superintendent` \| Chief Administrator \| 000240 \| District superintendent \|
	\| `school_year` \| School Year \| 000243 \| YYYY-YYYY format \|

	CEDS Option Sets:
	- LEA Type (000108): Regular local school district, Specialized (charter/magnet), Supervisory Union, Service Agency, State/Federal Agency
	- Operational Status (000533): Open, Closed, New, Changed Agency, Temporarily Closed
	- Locale Type (001315): City (Large/Midsize/Small), Suburb, Town, Rural (NCES Urban-centric codes)

	Benefits:
	- ✅ Compatible with NCES Common Core of Data (CCD) and F-33 Finance Survey
	- ✅ Aligns with Ed-Fi Alliance, IMS Global, and SIF Association standards
	- ✅ Supports federal reporting for ESSA, Title I, IDEA compliance

	#### Microsoft Common Data Model for Nonprofits

	Our nonprofit constituent management entities follow [Microsoft's Common Data Model for Nonprofits](https://github.com/microsoft/Nonprofits/), enabling seamless integration with Dynamics 365 and Power Platform:

	\| Our Entity \| Microsoft CDM Entity \| Description \| Key Relationships \|
	\|------------\|---------------------\|-------------\|------------------\|
	\| CONSTITUENT \| Constituent \| Donors, volunteers, members, beneficiaries \| → DONATION, MEMBERSHIP, VOLUNTEER_ACTIVITY \|
	\| DONATION \| Donation \| Financial contributions and in-kind gifts \| ← CONSTITUENT, → CAMPAIGN, → DESIGNATION \|
	\| CAMPAIGN \| Campaign \| Fundraising campaigns and appeals \| → DONATION \|
	\| DESIGNATION \| Designation \| Fund allocation (programs, unrestricted, endowment) \| ← DONATION \|
	\| MEMBERSHIP \| Membership \| Member enrollment and renewals \| ← CONSTITUENT, ← ORGANIZATION \|
	\| VOLUNTEER_ACTIVITY \| Volunteer Preference \| Volunteer activities and hours \| ← CONSTITUENT \|
	\| PROGRAM_DELIVERY \| Delivery Framework \| Programs and services delivered \| ← ORGANIZATION, → PROGRAM_OUTCOME \|
	\| PROGRAM_OUTCOME \| Objective \| Measurable impact and KPIs \| ← PROGRAM_DELIVERY \|

	Microsoft CDM Core Patterns:

	1. Constituent-Centric Design: All engagement activities (donations, volunteering, membership) link to CONSTITUENT
	2. Designation-Based Accounting: Donations are allocated to specific designations (funds, programs, campaigns)
	3. Campaign Tracking: Multi-channel fundraising campaigns track goals, raised amounts, donor counts
	4. Outcome Measurement: Programs track objectives with target vs. actual metrics
	5. Temporal Tracking: Start/end dates on memberships, campaigns, and programs for lifecycle management

	Integration Points:

	\| Microsoft Product \| Integration Type \| Use Case \|
	\|------------------\|------------------\|----------\|
	\| Dynamics 365 Nonprofit \| Native CDM compatibility \| CRM for constituent relationship management \|
	\| Power BI \| Direct data connection \| Fundraising dashboards, donor analytics \|
	\| Power Apps \| Low-code app builder \| Volunteer management apps, event registration \|
	\| Power Automate \| Workflow automation \| Donation receipts, membership renewals \|
	\| Azure Synapse \| Cloud analytics \| Large-scale constituent analytics \|

	Example: Constituent-Donation Relationship

	```sql
	-- Find top 10 donors by lifetime giving
	SELECT
	c.constituent_id,
	c.first_name,
	c.last_name,
	c.email,
	c.lifetime_giving_total,
	COUNT(d.donation_id) as donation_count,
	AVG(d.amount) as avg_donation,
	MAX(d.donation_date) as last_donation_date
	FROM CONSTITUENT c
	JOIN DONATION d ON c.constituent_id = d.constituent_id
	WHERE c.constituent_type = 'Donor'
	GROUP BY c.constituent_id, c.first_name, c.last_name, c.email, c.lifetime_giving_total
	ORDER BY c.lifetime_giving_total DESC
	LIMIT 10;
	```

	Benefits:
	- ✅ Microsoft Ecosystem: Native compatibility with Dynamics 365, Power Platform, Azure
	- ✅ Industry Standard: Used by large nonprofits (United Way, Boys & Girls Clubs, etc.)
	- ✅ Grant Reporting: Built-in support for outcome tracking and funder reporting
	- ✅ LYBNT Analysis: "Last Year But Not This Year" donor reactivation queries
	- ✅ Constituent 360: Unified view of all engagement touchpoints

	#### Underlying Standards

	Popolo builds upon W3C, IETF, and DCMI specifications for maximum interoperability:

	\| Standard \| Use Case \| Example in Our Model \|
	\|----------\|----------\|---------------------\|
	\| FOAF (Friend of a Friend) \| Social network, people relationships \| LEADER connections, ORGANIZATION networks \|
	\| vCard (IETF RFC 6350) \| Contact information \| email, phone, address fields across entities \|
	\| Schema.org \| Structured web data \| Meeting metadata, organization profiles for SEO \|
	\| DCMI Terms \| Metadata, provenance \| `created_at`, `updated_at`, `source_url` timestamps \|
	\| W3C Organization Ontology \| Hierarchical organizations \| Government hierarchy, nonprofit structures \|
	\| ISA Location Core Vocabulary \| Address standardization \| `address`, `city`, `state_code`, `latitude`, `longitude` \|
	\| GeoNames Ontology \| Geographic identifiers \| Place names, jurisdiction boundaries \|
	\| SKOS \| Taxonomies and classification \| NTEE codes, policy topic categories \|

	#### Benefits of Standards Compliance

	1. Interoperability: Data can be easily shared with other civic tech platforms
	2. API Compatibility: Standard field names work with existing tools (e.g., EveryPolitician, Open Civic Data)
	3. Semantic Web: RDF/JSON-LD export capabilities for linked open data
	4. Tooling: Existing libraries and validators (e.g., `pupa`, `everypolitician-popolo`)
	5. Documentation: Well-documented schemas reduce onboarding time

	#### Example: Popolo-Compatible JSON-LD Export

	```json
	{
	"@context": "http://www.popoloproject.com/contexts/person.jsonld",
	"@type": "Person",
	"id": "ocd-person/12345678-90ab-cdef-1234-567890abcdef",
	"name": "Jane Doe",
	"email": "jane.doe@example.gov",
	"links": [{
	"note": "official website",
	"url": "https://example.gov/mayor"
	}],
	"memberships": [{
	"@type": "Membership",
	"organization_id": "ocd-organization/jurisdiction/us/city/springfield",
	"post_id": "mayor",
	"role": "Mayor",
	"start_date": "2022-01-01",
	"end_date": "2026-12-31"
	}],
	"contact_details": [{
	"type": "email",
	"value": "jane.doe@example.gov",
	"note": "official"
	}, {
	"type": "voice",
	"value": "+1-555-123-4567"
	}],
	"sources": [{
	"url": "https://example.gov/government/officials",
	"note": "Official city website"
	}]
	}
	```

	### Open Civic Data (OCD-ID) Identifiers

	We use OCD-IDs for jurisdiction identifiers following [OCDEP 2](https://open-civic-data.readthedocs.io/en/latest/proposals/0002.html):

	```
	Format: ocd-division/country:<country_code>/<type>:<type_id>

	Examples:
	- State: ocd-division/country:us/state:al
	- County: ocd-division/country:us/state:al/county:jefferson
	- City: ocd-division/country:us/state:al/place:birmingham
	- School District: ocd-division/country:us/state:al/school_district:birmingham_city
	```

	Normalization Rules:
	- Lowercase ASCII characters (a-z)
	- Numbers (0-9)
	- Valid punctuation: `._~-`
	- Spaces → underscores
	- Remove special characters

	## 📊 Data Statistics

	\| Entity Type \| Estimated Count \| Source \|
	\|------------\|----------------\|--------\|
	\| Jurisdictions \| 22,000+ \| Census Gazetteer \|
	\| Counties \| 3,144 \| FIPS codes \|
	\| Cities \| 19,000+ \| Incorporated places \|
	\| School Districts \| 13,000+ \| NCES CCD \|
	\| School District Budgets \| 13,000+ \| NCES F-33 Finance Survey \|
	\| Government Budgets \| 22,000+ \| Census of Governments \|
	\| Municipal Bonds \| TBD \| EMMA (MSRB) \|
	\| Nonprofits \| 3,000,000+ \| IRS TEOS \|
	\| Nonprofit 990 Filings \| 10,000,000+ \| ProPublica (10+ years) \|
	\| Microsoft CDM: Nonprofit Engagement \| \| \|
	\| Constituents \| TBD \| Donors, volunteers, members, beneficiaries (Microsoft CDM) \|
	\| Donations \| TBD \| Financial contributions and in-kind gifts (Microsoft CDM) \|
	\| Campaigns \| TBD \| Fundraising campaigns and appeals (Microsoft CDM) \|
	\| Memberships \| TBD \| Member enrollments and renewals (Microsoft CDM) \|
	\| Volunteer Activities \| TBD \| Volunteer hours and service events (Microsoft CDM) \|
	\| Program Delivery Records \| TBD \| Programs and services delivered (Microsoft CDM) \|
	\| Program Outcomes \| TBD \| Impact metrics and KPIs (Microsoft CDM) \|
	\| Grants (Individual Awards) \| TBD \| IRS 990-I, USASpending.gov, Foundation Center \|
	\| Federal Grants \| 100,000+ \| USASpending.gov API \|
	\| Nonprofit Causes \| 600+ \| NTEE + Every.org \|
	\| YouTube Channels \| 5,000+ \| Discovery pipeline \|
	\| Meeting Platforms \| 10,000+ \| URL detection \|
	\| State Legislators \| 7,300+ \| Open States \|
	\| Meetings & Events \| 500,000+ \| Scraped (govt, hearings, events, trainings) \|
	\| Trainings \| TBD \| Professional development, workshops \|
	\| Documents \| 2,000,000+ \| PDF extraction \|
	\| Ballot Measures \| TBD \| State/local election sites \|
	\| State Bills \| 100,000+ \| Open States API \|
	\| Policy Topics \| ~50 \| Curated + extracted \|
	\| Analytics & Standards \| \| \|
	\| Date Dimension Records \| ~7,300 \| 20 years (2010-2030) \|
	\| Metric Views \| ~100 \| Pre-computed analytics definitions \|
	\| Temporal Relationships \| ~1M+ \| Date keys for all time-based entities \|
	\| Schema.org JSON-LD Exports \| ~500K+ \| Event, Person, Organization, Legislation, ClaimReview \|
	\| Popolo Exports \| ~100K+ \| Person, Organization, Membership, VoteEvent \|
	\| CEDS-Aligned Records \| 13,000+ \| School districts with NCES Element IDs \|
	\| OCD Division IDs \| 22,000+ \| All jurisdictions with standardized identifiers \|
	\| IATI Activity Files \| TBD \| Programs, grants, humanitarian aid (v2.03 XML) \|
	\| Fact-Checking \| \| \|
	\| Verified Claims \| ~50K+ \| Google Fact Check API \|
	\| PolitiFact Ratings \| ~20K+ \| Truth-O-Meter rulings \|
	\| FactCheck.org Articles \| ~10K+ \| Verified fact-checks \|
	\| Vocabulary & Concepts (OMOP-Inspired) \| \| \|
	\| Concept Entries \| ~2M+ \| Cities, nonprofits, officials, topics, demographics \|
	\| Vocabularies \| 10+ \| OCD_ID, IRS_NTEE, US_Census, NCES, OHDSI (Gender/Race/Ethnicity) \|
	\| Concept Classes \| 20+ \| City, County, 501c3, Mayor, Health Policy, etc. \|
	\| Concept Relationships \| ~5M+ \| Hierarchies (City→County→State), Associations (Topic→Legislation) \|
	\| OHDSI Gender Concepts \| 3 \| MALE, FEMALE, OTHER (Athena standard) \|
	\| OHDSI Race Concepts \| 20+ \| Census OMB categories (Athena standard) \|
	\| OHDSI Ethnicity Concepts \| 2 \| Hispanic or Latino, Not Hispanic or Latino (Athena standard) \|

	## � See Also

	:::tip[Complete Citations & Attributions]
	For full citations, licenses, BibTeX references, and detailed attribution for all data sources, standards, and research:

	👉 [View Citations & Data Sources](./citations.md)

	Includes academic research, government data APIs, civic tech standards (OCD-ID, Popolo, Schema.org, CEDS, IATI), Microsoft CDM, OMOP CDM, fact-checking sources, and more.
	:::

	## �📝 Meeting & Event Types

	### Event Categories in the MEETING Entity

	The MEETING entity tracks 4 main event categories to capture all civic engagement opportunities:

	#### 1. Government Meetings (`event_category: "government_meeting"`)
	- City council meetings, school board meetings, county commissions
	- Official business conducted by elected bodies
	- Fields: `body_name`, `meeting_type` (regular, special, emergency)
	- Example: "Tuscaloosa City Council Regular Meeting - 3rd Tuesday"

	#### 2. Public Hearings (`event_category: "public_hearing"`)
	- Public comment sessions on specific issues
	- Budget hearings, zoning hearings, policy feedback
	- Fields: `meeting_type` (budget, zoning, policy)
	- Example: "Public Hearing on FY2026 Water System Fluoridation Budget"

	#### 3. Community Events (`event_category: "community_event"`)
	- Town halls, community forums, listening sessions
	- Informal engagement between government and citizens
	- Fields: `location_type` (in-person, virtual, hybrid)
	- Example: "Town Hall on Community Health Priorities"

	#### 4. Trainings (`event_category: "training"`) ⭐ NEW
	- Professional development workshops
	- Continuing education for healthcare workers, teachers, officials
	- Certification courses, skill-building sessions
	- Fields:
	- `training_topic` - Subject matter (e.g., "Pediatric Oral Health", "Water Fluoridation Safety")
	- `target_audience` - Who should attend (e.g., "Dental Hygienists", "School Nurses", "Water Operators")
	- `presenter` - Trainer/instructor name or organization
	- `requires_registration` - Boolean flag
	- `registration_fee` - Cost to attend (0 for free)
	- `max_capacity` - Attendance limit
	- `end_date` - Training end time (multi-day events)
	- Example: "Fluoride Varnish Application Training for School Nurses (3 CEU)"

	### Why Trainings Matter for Advocacy

	Capacity Building:
	- ✅ Identify training gaps ("No fluoride varnish training in past 2 years")
	- ✅ Track professional development opportunities
	- ✅ Monitor continuing education credits (CEUs) offered

	Stakeholder Engagement:
	- ✅ Find healthcare workers trained in specific skills
	- ✅ Identify champions (frequent training attendees)
	- ✅ Target outreach to trained professionals

	Policy Implementation:
	- ✅ "City wants dental screenings but no trained staff" → Show available trainings
	- ✅ Track certification status (who's qualified to implement policy)
	- ✅ Link training availability to policy feasibility

	Example Questions Now Answerable:
	1. "What oral health trainings are offered in Alabama?" → Filter by `training_topic` LIKE '%oral%'
	2. "Which jurisdictions offer free fluoride training?" → `registration_fee = 0` AND `training_topic` LIKE '%fluoride%'
	3. "How many school nurses attended varnish training last year?" → Count attendees by `target_audience`
	4. "Are there upcoming water fluoridation operator trainings?" → `training_topic` AND `meeting_date` > TODAY

	### Meeting Types Within Each Category

	Government Meetings:
	- Regular sessions, special sessions, emergency meetings
	- Work sessions, committee meetings, executive sessions

	Public Hearings:
	- Budget hearings, zoning hearings, policy feedback sessions
	- Environmental impact hearings, license applications

	Community Events:
	- Town halls, listening sessions, community forums
	- Neighborhood meetings, stakeholder roundtables

	Trainings:
	- Professional development workshops
	- Certification courses (CPR, fluoride application, etc.)
	- Continuing education (CEU/CME credits)
	- Skill-building sessions (motivational interviewing, cultural competency)

	## �💰 Nonprofit Funding Source Tracking

	### Revenue Source Breakdown (Form 990 Data)

	The NONPROFIT_FINANCES entity tracks 10 different revenue sources to understand how nonprofits are funded:

	#### 1. Grant Revenue (Institutional Funding)
	- `government_grants` - Federal, state, local government grants
	- `foundation_grants` - Private foundation grants (Gates, Ford, etc.)
	- Why it matters: Grant-dependent orgs may be less sustainable, more restrictive

	#### 2. Donation Revenue (Community Funding)
	- `individual_donations` - Direct donations from people
	- `corporate_donations` - Corporate giving programs
	- `membership_dues` - Member subscriptions/fees
	- Why it matters: Grassroots funding = community support, more flexible use

	#### 3. Earned Revenue (Self-Sufficiency)
	- `program_service_revenue` - Fees for services (clinic visits, classes, etc.)
	- `special_events_revenue` - Galas, fundraisers, events
	- `rental_income` - Property rentals
	- `sale_of_assets` - Asset sales
	- Why it matters: Self-generated revenue = sustainability, independence

	#### 4. Investment Revenue
	- `investment_income` - Interest, dividends, capital gains
	- Why it matters: Endowment size, financial health

	#### 5. Other Revenue
	- `other_revenue` - Miscellaneous sources
	- Why it matters: Unusual funding patterns

	### Calculated Metrics

	- `overhead_ratio` = (admin_expenses + fundraising_expenses) / total_expenses
	- Lower = more efficient (more goes to programs)
	- Industry benchmark: <25% overhead is "good"

	- `fundraising_efficiency` = contributions_received / fundraising_expenses
	- Higher = better (more money raised per dollar spent)
	- Industry benchmark: $4+ raised per $1 spent

	### Why This Matters for Advocacy

	Find sustainable partners:
	- ✅ High individual donations = community trust
	- ✅ Diversified revenue = financial stability
	- ⚠️ Single-grant dependent = risky partnership

	Evaluate efficiency:
	- ✅ Low overhead ratio = more program dollars
	- ✅ High fundraising efficiency = good stewardship
	- ⚠️ High admin costs = potential waste

	Identify funding gaps:
	- Compare similar nonprofits' revenue mix
	- Find underutilized funding sources (e.g., membership programs)
	- Target corporate donation opportunities

	Example Questions Now Answerable:
	1. "Which dental nonprofits have the most individual donors?" (community support)
	2. "What's the average overhead for oral health organizations?" (efficiency benchmark)
	3. "Are dental nonprofits more grant-dependent or self-sufficient?" (sustainability)
	4. "Which funders support oral health work?" (foundation grants analysis)

	## 💵 Grant Tracking System

	### Individual Grant Transactions (GRANT Entity)

	The GRANT entity tracks individual grant awards beyond just aggregate 990 financials. This provides transaction-level detail for:

	#### Grant Fields
	- Recipient Info: `recipient_ein`, `recipient_name`, `recipient_type` (nonprofit, government, etc.)
	- Funder Info: `funder_name`, `funder_ein`, `funder_type` (foundation, government, corporate)
	- Grant Details: `grant_amount`, `grant_purpose`, `program_area`
	- Timeline: `award_date`, `start_date`, `end_date`, `grant_duration_months`
	- Status: `grant_status` (active, completed, terminated)
	- Type: `funding_source` (federal, state, foundation, corporate)
	- Restrictions: `multi_year`, `restrictions`, `reporting_requirements`

	#### Data Sources

	IRS Form 990 Schedule I:
	- Grants PAID by nonprofits to other organizations
	- Required for organizations granting >$5,000/year
	- Shows foundation giving patterns

	USASpending.gov API (FREE):
	- All federal grants to states, localities, nonprofits
	- Contract and grant transactions $25K+
	- Real-time data updated daily

	Foundation Center/Candid:
	- Private foundation grants (990-PF data)
	- Grant descriptions, amounts, recipients

	State Grant Databases:
	- State-level grant programs
	- Varies by state

	### Why Grant Tracking Matters

	Follow the Money:
	- ✅ "Who funds oral health work in Alabama?" → Track all grants by `program_area`
	- ✅ "Which foundations support fluoridation?" → Search grant purposes
	- ✅ "How much federal money goes to dental access?" → Sum `funding_source = federal`

	Find Funding Opportunities:
	- ✅ Identify active grant programs (similar grants to similar orgs)
	- ✅ Discover new funders entering a program area
	- ✅ Track grant sizes and typical durations

	Partnership Intelligence:
	- ✅ "Who else is this foundation funding?" → Find collaborators
	- ✅ "What's this nonprofit's grant portfolio?" → Assess stability
	- ✅ Multi-year grants = long-term commitment signal

	Policy Implementation:
	- ✅ "Is there grant funding for this program?" → Search active grants
	- ✅ "Which jurisdictions received similar grants?" → Learn from others
	- ✅ Track grant requirements and restrictions

	#### Example Questions Now Answerable:

	1. "What federal grants support dental health in Alabama schools?"
	→ `funding_source = 'federal'` AND `program_area` LIKE '%dental%' AND `recipient_type = 'school_district'`

	2. "Which foundations give the largest oral health grants?"
	→ GROUP BY `funder_name` WHERE `program_area` LIKE '%oral health%' ORDER BY SUM(`grant_amount`)

	3. "How long do typical dental access grants last?"
	→ AVG(`grant_duration_months`) WHERE `program_area` = 'dental access'

	4. "Which nonprofits receive multi-year fluoridation funding?"
	→ `multi_year = true` AND `grant_purpose` LIKE '%fluoride%'

	5. "What grants end in the next 6 months?"
	→ `end_date` BETWEEN NOW() AND NOW() + 6 MONTHS (renewal opportunities!)

	### Dataset Structure

	```
	grants/
	├── nonprofit_grants # Grants TO nonprofits (Schedule I recipients)
	├── government_grants # Federal/state grants to jurisdictions
	├── foundation_grants # Private foundation giving (990-PF)
	└── federal_grants # USASpending.gov federal grants
	```

	## ⏰ Time Dimension Modeling

	To enable robust time-series analysis, trend tracking, and temporal comparisons, we implement a comprehensive time dimension alongside our fact tables.

	### Time Dimension Table

	```sql
	DATE_DIMENSION {
	date date PK
	year int
	quarter int
	quarter_name string
	month int
	month_name string
	month_abbr string
	day_of_month int
	day_of_week int
	day_name string
	day_abbr string
	week_of_year int
	fiscal_year int
	fiscal_quarter int
	fiscal_month int
	is_weekend boolean
	is_holiday boolean
	holiday_name string
	is_business_day boolean
	days_in_month int
	year_month string
	year_quarter string
	}
	```

	### Temporal Relationships

	All time-bound entities link to the date dimension for consistent temporal analysis:

	```mermaid
	erDiagram
	DATE_DIMENSION \|\|--o{ MEETING : "meeting_date"
	DATE_DIMENSION \|\|--o{ GOVERNMENT_BUDGET : "fiscal_year_start"
	DATE_DIMENSION \|\|--o{ BALLOT_MEASURE : "election_date"
	DATE_DIMENSION \|\|--o{ LEGISLATION : "introduced_date"
	DATE_DIMENSION \|\|--o{ GRANT : "start_date, end_date"
	DATE_DIMENSION \|\|--o{ NONPROFIT_FILING : "tax_period_end"
	DATE_DIMENSION \|\|--o{ POLICY_TRACKER : "tracked_date"
	DATE_DIMENSION \|\|--o{ SURVEY : "field_date_start, field_date_end"
	DATE_DIMENSION \|\|--o{ FACT_CHECK : "published_date"

	DATE_DIMENSION {
	date date PK
	year int
	quarter int
	month int
	fiscal_year int
	is_business_day boolean
	}
	```

	### Temporal Analysis Patterns

	Year-over-Year Comparisons:
	```sql
	SELECT
	d.year,
	d.quarter_name,
	COUNT(m.meeting_id) as meeting_count,
	COUNT(m.meeting_id) - LAG(COUNT(m.meeting_id)) OVER (ORDER BY d.year, d.quarter) as yoy_change
	FROM MEETING m
	JOIN DATE_DIMENSION d ON m.meeting_date = d.date
	WHERE d.year BETWEEN 2023 AND 2025
	GROUP BY d.year, d.quarter, d.quarter_name
	ORDER BY d.year, d.quarter;
	```

	Fiscal Period Aggregation:
	```sql
	SELECT
	d.fiscal_year,
	d.fiscal_quarter,
	SUM(b.total_expenditures) as total_spending,
	AVG(b.total_expenditures) as avg_spending
	FROM GOVERNMENT_BUDGET b
	JOIN DATE_DIMENSION d ON b.fiscal_year = d.fiscal_year
	WHERE b.jurisdiction_type = 'city'
	GROUP BY d.fiscal_year, d.fiscal_quarter;
	```

	Trend Detection:
	```sql
	-- Identify growing advocacy momentum
	SELECT
	d.year_month,
	COUNT(DISTINCT pt.topic_id) as active_topics,
	COUNT(m.meeting_id) as related_meetings,
	COUNT(bm.measure_id) as ballot_initiatives
	FROM DATE_DIMENSION d
	LEFT JOIN MEETING m ON m.meeting_date = d.date AND m.oral_health_related = true
	LEFT JOIN POLICY_TRACKER pt ON d.date BETWEEN pt.start_date AND COALESCE(pt.end_date, CURRENT_DATE)
	LEFT JOIN BALLOT_MEASURE bm ON bm.election_date = d.date
	WHERE d.date >= DATE_SUB(CURRENT_DATE, INTERVAL 24 MONTH)
	GROUP BY d.year_month
	ORDER BY d.year_month;
	```

	## 📊 Metric Views

	Metric views provide pre-aggregated, analysis-ready datasets combining multiple source tables with built-in dimensions, measures, and filters.

	### Core Metric View Components

	\| Component \| Description \| Example \|
	\|-----------\|-------------\|---------\|
	\| Source \| Base table, view, or SQL query containing the data \| `MEETING`, `GOVERNMENT_BUDGET`, `NONPROFIT_FILING` \|
	\| Dimensions \| Column attributes used to segment or group metrics \| `jurisdiction_type`, `fiscal_year`, `policy_topic` \|
	\| Measures \| Column aggregations that produce metrics \| `COUNT(meeting_id) as meeting_count`, `SUM(grant_amount) as total_funding` \|
	\| Filters \| Conditions applied to source data to define scope \| `oral_health_related = true`, `fiscal_year > 2020` \|
	\| Joins \| Relationships between tables to enrich data \| `JOIN JURISDICTION ON meeting.jurisdiction_id = jurisdiction.jurisdiction_id` \|

	### Example Metric Views

	#### 1. Advocacy Activity Metrics

	Purpose: Track oral health advocacy momentum across jurisdictions

	```sql
	CREATE VIEW metric_advocacy_activity AS
	SELECT
	-- Dimensions
	j.jurisdiction_id,
	j.jurisdiction_type,
	j.state_code,
	j.county_name,
	d.year,
	d.quarter_name,
	d.month_name,
	pt.topic_name,

	-- Measures
	COUNT(DISTINCT m.meeting_id) as meeting_count,
	COUNT(DISTINCT bm.measure_id) as ballot_measure_count,
	COUNT(DISTINCT l.bill_id) as legislation_count,
	COUNT(DISTINCT fc.claim_id) as fact_check_count,

	-- Calculated Metrics
	SUM(CASE WHEN m.oral_health_related THEN 1 ELSE 0 END) as oral_health_meeting_count,
	AVG(CASE WHEN bm.result = 'passed' THEN 1 ELSE 0 END) as ballot_success_rate,
	COUNT(DISTINCT n.nonprofit_id) as active_nonprofit_count

	FROM JURISDICTION j
	JOIN DATE_DIMENSION d ON d.date BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 365 DAY) AND CURRENT_DATE
	LEFT JOIN MEETING m ON m.jurisdiction_id = j.jurisdiction_id AND m.meeting_date = d.date
	LEFT JOIN POLICY_TRACKER pt ON pt.jurisdiction_id = j.jurisdiction_id
	LEFT JOIN BALLOT_MEASURE bm ON bm.jurisdiction_id = j.jurisdiction_id AND bm.election_date = d.date
	LEFT JOIN LEGISLATION l ON l.state_code = j.state_code AND l.introduced_date = d.date
	LEFT JOIN FACT_CHECK fc ON fc.published_date = d.date
	LEFT JOIN NONPROFIT n ON n.jurisdiction_id = j.jurisdiction_id

	WHERE
	-- Filters
	d.is_business_day = true
	AND (
	m.oral_health_related = true
	OR pt.topic_area LIKE '%oral health%'
	OR pt.topic_area LIKE '%dental%'
	OR pt.topic_area LIKE '%fluoride%'
	)

	GROUP BY
	j.jurisdiction_id, j.jurisdiction_type, j.state_code, j.county_name,
	d.year, d.quarter_name, d.month_name, pt.topic_name;
	```

	Usage:
	```sql
	-- Find top 10 most active jurisdictions for oral health advocacy
	SELECT
	jurisdiction_id,
	state_code,
	jurisdiction_type,
	SUM(meeting_count) as total_meetings,
	SUM(ballot_measure_count) as total_ballot_measures,
	SUM(oral_health_meeting_count) as oral_health_meetings
	FROM metric_advocacy_activity
	WHERE year = 2025
	GROUP BY jurisdiction_id, state_code, jurisdiction_type
	ORDER BY total_meetings DESC
	LIMIT 10;
	```

	#### 2. Government Spending Metrics

	Purpose: Analyze government budget allocations and trends

	```sql
	CREATE VIEW metric_government_spending AS
	SELECT
	-- Dimensions
	j.jurisdiction_id,
	j.jurisdiction_type,
	j.state_code,
	j.population,
	d.fiscal_year,
	d.fiscal_quarter,
	bc.category_name as budget_category,

	-- Measures
	SUM(gb.total_revenue) as total_revenue,
	SUM(gb.total_expenditures) as total_expenditures,
	SUM(gb.total_debt) as total_debt,
	SUM(gb.property_tax_revenue) as property_tax_revenue,
	SUM(gb.federal_grants) as federal_grants,
	SUM(gb.state_grants) as state_grants,

	-- Calculated Metrics
	SUM(gb.total_revenue) / NULLIF(j.population, 0) as revenue_per_capita,
	SUM(gb.total_expenditures) / NULLIF(j.population, 0) as spending_per_capita,
	SUM(gb.total_debt) / NULLIF(j.population, 0) as debt_per_capita,
	(SUM(gb.total_revenue) - SUM(gb.total_expenditures)) as budget_surplus_deficit,
	SUM(bc.amount) / NULLIF(SUM(gb.total_expenditures), 0) * 100 as category_pct_of_budget

	FROM JURISDICTION j
	JOIN DATE_DIMENSION d ON d.fiscal_year BETWEEN 2020 AND 2025
	JOIN GOVERNMENT_BUDGET gb ON gb.jurisdiction_id = j.jurisdiction_id AND gb.fiscal_year = d.fiscal_year
	LEFT JOIN BUDGET_CATEGORY bc ON bc.budget_id = gb.budget_id

	WHERE
	-- Filters
	gb.total_expenditures > 0
	AND j.population > 0

	GROUP BY
	j.jurisdiction_id, j.jurisdiction_type, j.state_code, j.population,
	d.fiscal_year, d.fiscal_quarter, bc.category_name;
	```

	Usage:
	```sql
	-- Compare spending per capita across jurisdiction types
	SELECT
	jurisdiction_type,
	fiscal_year,
	AVG(spending_per_capita) as avg_spending_per_capita,
	AVG(revenue_per_capita) as avg_revenue_per_capita,
	AVG(debt_per_capita) as avg_debt_per_capita
	FROM metric_government_spending
	GROUP BY jurisdiction_type, fiscal_year
	ORDER BY fiscal_year DESC, avg_spending_per_capita DESC;
	```

	#### 3. Nonprofit Impact Metrics

	Purpose: Measure nonprofit activity, funding, and service delivery

	```sql
	CREATE VIEW metric_nonprofit_impact AS
	SELECT
	-- Dimensions
	n.nonprofit_id,
	n.organization_name,
	n.state,
	n.city,
	nc.ntee_code,
	nc.category_name,
	d.tax_year,

	-- Measures
	SUM(nf.total_revenue) as total_revenue,
	SUM(nf.total_expenses) as total_expenses,
	SUM(nf.total_assets) as total_assets,
	SUM(nf.program_service_expenses) as program_expenses,
	SUM(nf.fundraising_expenses) as fundraising_expenses,
	SUM(nf.management_expenses) as management_expenses,
	COUNT(DISTINCT g.grant_id) as grants_received_count,
	SUM(g.grant_amount) as total_grants_received,

	-- Calculated Metrics
	SUM(nf.program_service_expenses) / NULLIF(SUM(nf.total_expenses), 0) * 100 as program_expense_ratio,
	SUM(nf.fundraising_expenses) / NULLIF(SUM(nf.total_revenue), 0) * 100 as fundraising_efficiency,
	(SUM(nf.total_revenue) - SUM(nf.total_expenses)) as net_income,
	COUNT(DISTINCT nf.year) as years_active

	FROM NONPROFIT n
	JOIN DATE_DIMENSION d ON d.year BETWEEN 2020 AND 2025
	JOIN NONPROFIT_FILING nf ON nf.ein = n.ein AND nf.tax_year = d.year
	LEFT JOIN NONPROFIT_CAUSE nc ON nc.cause_id = n.primary_cause_id
	LEFT JOIN GRANT g ON g.recipient_ein = n.ein AND g.tax_year = d.year

	WHERE
	-- Filters
	nf.total_revenue > 0
	AND (
	nc.category_name LIKE '%health%'
	OR nc.category_name LIKE '%dental%'
	OR n.mission_statement LIKE '%oral health%'
	)

	GROUP BY
	n.nonprofit_id, n.organization_name, n.state, n.city,
	nc.ntee_code, nc.category_name, d.tax_year;
	```

	Usage:
	```sql
	-- Identify high-performing health nonprofits by program efficiency
	SELECT
	organization_name,
	state,
	tax_year,
	total_revenue,
	program_expense_ratio,
	fundraising_efficiency
	FROM metric_nonprofit_impact
	WHERE
	tax_year = 2024
	AND total_revenue > 1000000
	AND program_expense_ratio > 75 -- More than 75% goes to programs
	ORDER BY program_expense_ratio DESC, total_revenue DESC
	LIMIT 20;
	```

	### Metric View Best Practices

	1. Grain Definition: Clearly define the granularity of each metric view (e.g., per jurisdiction per month)
	2. Performance: Pre-aggregate expensive calculations to improve query performance
	3. Incremental Updates: Design views to support incremental refresh rather than full rebuilds
	4. Documentation: Document all dimension values, measure calculations, and filter logic
	5. Naming Convention: Use `metric_` prefix followed by descriptive name (e.g., `metric_advocacy_activity`)
	6. Testing: Validate measure calculations against source data to ensure accuracy

	### Query Optimization

	For large-scale analytics, metric views can be materialized:

	```sql
	-- Materialize for fast querying
	CREATE MATERIALIZED VIEW metric_advocacy_activity_mat AS
	SELECT * FROM metric_advocacy_activity;

	-- Refresh incrementally
	REFRESH MATERIALIZED VIEW metric_advocacy_activity_mat;

	-- Add indexes on common filter/join columns
	CREATE INDEX idx_advocacy_state_year
	ON metric_advocacy_activity_mat(state_code, year);

	CREATE INDEX idx_advocacy_jurisdiction
	ON metric_advocacy_activity_mat(jurisdiction_id);
	```

	## 🔧 System-Internal Tables (OMOP-Inspired Vocabulary)

	Purpose: Standardized terminology and concept management following OHDSI OMOP Common Data Model principles. These tables provide semantic interoperability and enable precise data linkage across all entities.

	⚠️ Technical Note: These are internal reference tables used by data engineers and ETL pipelines. Non-technical users can ignore this section.

	<ZoomableMermaid
	title="Vocabulary & Concept System (OMOP-Inspired)"
	initialScale={8}
	value={`
	erDiagram
	%% ========================================
	%% VOCABULARY SYSTEM (OMOP-Inspired)
	%% Based on OHDSI Athena vocabulary structure
	%% ID range: 2,000,000,000+ for custom civic concepts
	%% ========================================

	VOCABULARY \|\|--o{ CONCEPT : contains
	CONCEPT_CLASS \|\|--o{ CONCEPT : categorizes
	DOMAIN \|\|--o{ CONCEPT : groups
	CONCEPT \|\|--o{ CONCEPT_RELATIONSHIP : source
	CONCEPT \|\|--o{ CONCEPT_RELATIONSHIP : target

	VOCABULARY {
	string vocabulary_id PK
	string vocabulary_name
	string vocabulary_reference
	string vocabulary_version
	string vocabulary_concept_id FK
	}

	DOMAIN {
	string domain_id PK
	string domain_name
	string domain_concept_id FK
	}

	CONCEPT_CLASS {
	string concept_class_id PK
	string concept_class_name
	string concept_class_concept_id FK
	}

	CONCEPT {
	int concept_id PK "Unique ID (2B+ for custom)"
	string concept_name "Display name"
	string domain_id FK "Jurisdiction, Nonprofit, Policy"
	string vocabulary_id FK "OCD_ID, IRS_NTEE, US_Census"
	string concept_class_id FK "City, County, 501c3, Mayor"
	string standard_concept "S=Standard, C=Classification"
	string concept_code "External identifier"
	datetime valid_start_date
	datetime valid_end_date
	string invalid_reason
	}

	CONCEPT_RELATIONSHIP {
	int concept_id_1 FK
	int concept_id_2 FK
	string relationship_id "Is part of, Regulates, etc"
	datetime valid_start_date
	datetime valid_end_date
	string invalid_reason
	}

	%% ========================================
	%% CONCEPT MAPPING TO EXISTING TABLES
	%% ========================================

	CONCEPT \|\|--o{ JURISDICTION : city_concept_id
	CONCEPT \|\|--o{ ORGANIZATION : organization_concept_id
	CONCEPT \|\|--o{ LEADER : position_concept_id
	CONCEPT \|\|--o{ POLICY_TOPIC : topic_concept_id

	%% ========================================
	%% DEMOGRAPHICS
	%% ========================================
	%% Comprehensive demographic data from U.S. Census Bureau
	%% Separate entity for population characteristics

	JURISDICTION \|\|--o\| DEMOGRAPHICS : has_demographics
	DEMOGRAPHICS {
	string demographics_id PK
	string jurisdiction_id FK
	string census_tract
	int total_population
	int population_2020
	int population_2024
	int median_age

	%% Race & Ethnicity
	int white_alone
	int black_alone
	int asian_alone
	int native_american_alone
	int pacific_islander_alone
	int other_race_alone
	int two_or_more_races
	int hispanic_latino
	int not_hispanic_latino

	%% Gender
	int male_population
	int female_population

	%% Age Distribution
	int under_18
	int age_18_to_64
	int age_65_plus

	%% Income & Economics
	float median_household_income
	float per_capita_income
	float poverty_rate
	int households_snap_benefits

	%% Education
	int high_school_graduate_pct
	int bachelors_degree_pct
	int graduate_degree_pct

	%% Housing
	int total_housing_units
	int owner_occupied_pct
	int renter_occupied_pct
	float median_home_value
	float median_rent

	%% Employment
	float unemployment_rate
	int labor_force_participation_pct

	%% Health Insurance
	int uninsured_pct
	int medicaid_pct
	int medicare_pct

	datetime census_year
	string acs_table_id "ACS Table reference"
	}
	`}
	/>

	### Vocabulary Sources

	\| Vocabulary ID \| Vocabulary Name \| Source \| Use Case \|
	\|---------------\|-----------------\|--------\|----------\|
	\| OCD_ID \| Open Civic Data Division IDs \| https://github.com/opencivicdata/ocd-division-ids \| Standard jurisdiction identifiers \|
	\| IRS_NTEE \| IRS National Taxonomy of Exempt Entities \| IRS TEOS \| Nonprofit classification \|
	\| US_Census \| U.S. Census Bureau \| Census Gazetteer, ACS \| Demographics, geography \|
	\| NCES \| National Center for Education Statistics \| NCES CCD, F-33 \| School districts, education data \|
	\| OHDSI_Gender \| OHDSI Gender \| Athena \| Standard gender concepts (interoperable with medical research) \|
	\| OHDSI_Race \| OHDSI Race \| Athena \| Standard race concepts (OMB Classification) \|
	\| OHDSI_Ethnicity \| OHDSI Ethnicity \| Athena \| Standard ethnicity concepts (Hispanic/Latino) \|
	\| OpenNavigator \| Custom Civic Concepts \| Internal \| Cities, officials, topics (ID > 2,000,000,000) \|

	### Concept Classes for Civic Data

	\| Concept Class \| Domain \| Examples \|
	\|---------------\|--------\|----------\|
	\| City \| Jurisdiction \| Incorporated places, consolidated city-counties \|
	\| County \| Jurisdiction \| U.S. counties, county equivalents \|
	\| State \| Jurisdiction \| U.S. states, territories, DC \|
	\| School District \| Jurisdiction \| LEAs (Local Educational Agencies) \|
	\| 501c3 \| Nonprofit \| Tax-exempt charitable organizations \|
	\| 501c4 \| Nonprofit \| Social welfare organizations \|
	\| Mayor \| Position \| Chief executive of city government \|
	\| Council Member \| Position \| Legislative member \|
	\| Superintendent \| Position \| School district chief administrator \|
	\| Health Policy \| Topic \| Fluoridation, nutrition, dental care \|
	\| Education Policy \| Topic \| School funding, curriculum, facilities \|

	### Example Concept Entries

	```sql
	-- Standard concept for a city
	INSERT INTO CONCEPT VALUES (
	2000000001, -- concept_id (custom range)
	'Birmingham, Alabama', -- concept_name
	'Jurisdiction', -- domain_id
	'OCD_ID', -- vocabulary_id
	'City', -- concept_class_id
	'S', -- standard_concept (Standard)
	'ocd-division/country:us/state:al/place:birmingham', -- concept_code
	'2020-01-01', -- valid_start_date
	'2099-12-31', -- valid_end_date
	NULL -- invalid_reason
	);

	-- Concept for a nonprofit cause
	INSERT INTO CONCEPT VALUES (
	2000000101, -- concept_id
	'Animal Welfare', -- concept_name
	'Nonprofit', -- domain_id
	'IRS_NTEE', -- vocabulary_id
	'501c3', -- concept_class_id
	'C', -- standard_concept (Classification)
	'D20', -- concept_code (NTEE code)
	'2020-01-01',
	'2099-12-31',
	NULL
	);

	-- Gender concept from OHDSI Athena (interoperable with medical research)
	INSERT INTO CONCEPT VALUES (
	8507, -- concept_id (OHDSI standard)
	'MALE', -- concept_name
	'Gender', -- domain_id
	'OHDSI_Gender', -- vocabulary_id
	'Gender', -- concept_class_id
	'S',
	'M',
	'1970-01-01',
	'2099-12-31',
	NULL
	);
	```

	### Concept Relationships

	```sql
	-- City is part of County
	INSERT INTO CONCEPT_RELATIONSHIP VALUES (
	2000000001, -- Birmingham, AL (concept_id_1)
	2000000050, -- Jefferson County, AL (concept_id_2)
	'Is part of', -- relationship_id
	'2020-01-01',
	'2099-12-31',
	NULL
	);

	-- Topic regulates Legislation
	INSERT INTO CONCEPT_RELATIONSHIP VALUES (
	2000000201, -- Water Fluoridation topic
	2000000305, -- Ordinance 101
	'Regulates',
	'2024-01-15',
	'2099-12-31',
	NULL
	);

	-- Organization addresses Topic
	INSERT INTO CONCEPT_RELATIONSHIP VALUES (
	2000000401, -- Dental Health Foundation
	2000000201, -- Water Fluoridation
	'Addresses',
	'2023-06-01',
	'2099-12-31',
	NULL
	);
	```

	### Implementation in Existing Tables

	#### Updated JURISDICTION Schema
	```sql
	CREATE TABLE JURISDICTION (
	jurisdiction_id VARCHAR(255) PRIMARY KEY,
	name VARCHAR(255),
	jurisdiction_type VARCHAR(50),

	-- OMOP-style concept references
	city_concept_id INTEGER REFERENCES CONCEPT(concept_id),
	city_type_concept_id INTEGER REFERENCES CONCEPT(concept_id), -- e.g., "Consolidated City-County"
	source_value VARCHAR(255), -- Original text from data source (e.g., "SF, Calif")

	state_code VARCHAR(2),
	county_name VARCHAR(100),
	population INTEGER,
	-- ... other fields
	);
	```

	#### Updated ORGANIZATION Schema
	```sql
	CREATE TABLE ORGANIZATION (
	org_id VARCHAR(255) PRIMARY KEY,
	ein VARCHAR(20),
	name VARCHAR(255),

	-- OMOP-style concept references
	organization_concept_id INTEGER REFERENCES CONCEPT(concept_id),
	cause_concept_id INTEGER REFERENCES CONCEPT(concept_id), -- Links to NTEE concept
	org_type_concept_id INTEGER REFERENCES CONCEPT(concept_id), -- 501c3, 501c4, etc.
	source_value VARCHAR(255), -- Original organization name from IRS

	ntee_code VARCHAR(10),
	-- ... other fields
	);
	```

	#### Updated DEMOGRAPHICS Schema
	```sql
	CREATE TABLE DEMOGRAPHICS (
	demographics_id VARCHAR(255) PRIMARY KEY,
	jurisdiction_id VARCHAR(255) REFERENCES JURISDICTION(jurisdiction_id),

	-- OMOP-style concept references (OHDSI Athena vocabularies)
	race_concept_id INTEGER REFERENCES CONCEPT(concept_id), -- Standard OHDSI race codes
	ethnicity_concept_id INTEGER REFERENCES CONCEPT(concept_id), -- Hispanic/Latino classification
	gender_concept_id INTEGER REFERENCES CONCEPT(concept_id), -- MALE/FEMALE/OTHER

	race_source_value VARCHAR(100), -- Census text (e.g., "White alone")
	ethnicity_source_value VARCHAR(100), -- Census text
	gender_source_value VARCHAR(50), -- Census text

	-- ... other demographic fields
	);
	```

	### Benefits of OMOP-Style Vocabulary

	1. Semantic Interoperability: Civic data can be joined with healthcare research data using standard OHDSI demographic concepts
	2. Reproducible IDs: Deterministic hashing (uuid5) on strings like "JURISDICTION:CITY:NEW_YORK" generates consistent concept_id values
	3. Version Control: `valid_start_date` and `valid_end_date` track concept changes over time
	4. Relationship Tracking: `CONCEPT_RELATIONSHIP` table captures hierarchies (City → County → State) and associations (Topic → Legislation)
	5. Source Traceability: `source_value` preserves original text while `concept_id` provides standardized reference

	### ETL Mapping Strategy

	```python
	import uuid

	def generate_concept_id(domain: str, type: str, identifier: str) -> int:
	"""
	Generate deterministic concept_id using UUID5.
	Returns integer > 2,000,000,000 for custom civic concepts.
	"""
	namespace = uuid.UUID('6ba7b810-9dad-11d1-80b4-00c04fd430c8') # OMOP namespace
	concept_string = f"{domain}:{type}:{identifier}".upper()
	concept_uuid = uuid.uuid5(namespace, concept_string)
	# Convert to integer in custom range
	return 2_000_000_000 + (int(concept_uuid.hex[:8], 16) % 1_000_000_000)

	# Example usage
	city_concept_id = generate_concept_id("JURISDICTION", "CITY", "NEW_YORK")
	print(city_concept_id) # e.g., 2045879021 (repeatable)
	```

	### Downloading OHDSI Athena Vocabularies

	1. Visit https://athena.ohdsi.org/
	2. Select vocabularies:
	- ✅ Gender
	- ✅ Race
	- ✅ Ethnicity
	- ✅ Geography (US Counties, States)
	3. Download CSV files
	4. Import into `vocabulary/` folder:
	- `vocabulary_gender.parquet`
	- `vocabulary_race.parquet`
	- `vocabulary_ethnicity.parquet`
	- `vocabulary_geography.parquet`

	## 🎯 Missing Datasets to Add

	### High Priority
	- [x] Ballot Measures - ✅ Added to data model! Fluoridation votes, bond measures
	- [x] State Legislation - ✅ Added to data model! Open States API (FREE)
	- [x] Policy Topics - ✅ Added to data model! Oral health advocacy tracking
	- [x] Government Finances - ✅ Added to data model! City/county/state budgets, Census of Governments
	- [x] School Finances - ✅ Added to data model! NCES F-33 per-pupil spending, revenues
	- [x] Nonprofit Financials - ✅ Added to data model! Form 990 detailed financials (10M+ filings)
	- [ ] Census Demographics - Full census data per jurisdiction (beyond population)
	- [ ] Procurement Records - Government contracts
	- [ ] Election Results - Historical voting data
	- [ ] Health Outcomes - CDC PLACES data (oral health metrics!)
	- [ ] Environmental Data - EPA water quality (fluoridation levels)

	### Medium Priority
	- [ ] Property Records - Public assessment data
	- [ ] Crime Statistics - UCR/NIBRS data
	- [ ] Business Licenses - Local business registrations (dental clinics!)
	- [ ] Building Permits - Construction activity
	- [ ] Code Violations - Inspection records
	- [ ] Police Reports - Public safety incidents
	- [ ] Fire Department Data - Emergency response
	- [ ] Parks & Recreation - Facilities & programs
	- [ ] Transportation Data - Traffic & transit

	### Integration Improvements
	- [ ] Full Wikidata Sync - All civic entities
	- [ ] DBpedia Expansion - Complete local government coverage
	- [ ] Ballotpedia Data - (if budget allows) Electoral info & analysis
	- [ ] Court Records - Public dockets
	- [ ] Tax Records - Property tax data

	## 🚀 Implementation Status

	### ✅ Completed
	- Jurisdiction discovery pipeline
	- YouTube channel discovery
	- Meeting platform detection
	- NCES school district ingestion
	- Open States API integration
	- Wikidata SPARQL queries
	- DBpedia Lookup API
	- Google Civic API (code ready)
	- Social media discovery
	- HuggingFace upload pipeline

	### 🔨 In Progress
	- Meeting minutes extraction (Tuscaloosa pilot)
	- Video transcript processing
	- Document keyword detection
	- Nonprofit data enrichment

	### 📋 Planned
	- Automated meeting scraping at scale
	- Real-time meeting notifications
	- Budget document parsing
	- Full census integration
	- Health outcome correlation

	## 📚 Related Documentation

	### Data Standards & Specifications

	- [Popolo Project](https://github.com/popolo-project/popolo-spec) - Open government data specification for people, organizations, and elected positions. Our LEADER, ORGANIZATION, and JURISDICTION entities follow Popolo schema conventions for maximum interoperability with civic tech platforms.

	- [Schema.org](https://schema.org/) - W3C structured data vocabulary for semantic web. Our entities map to Schema.org types (Event, Person, Organization, Legislation, ClaimReview, etc.) enabling SEO-optimized JSON-LD exports, Google Search rich results, and voice assistant compatibility.

	- [Common Education Data Standards (CEDS)](https://ceds.ed.gov/) - U.S. Department of Education data standards for K-12, postsecondary, and workforce data. Our SCHOOL_DISTRICT entity aligns with CEDS Element IDs and NCES survey specifications (CCD, F-33 Finance).

	- [Open Civic Data (OCD-IDs)](https://open-civic-data.readthedocs.io/en/latest/proposals/0002.html) - Standardized division identifiers for jurisdictions. Format: `ocd-division/country:us/state:al/place:birmingham`

	### Internal Documentation

	- [HuggingFace Publishing Guide](../guides/huggingface-publishing.md)
	- [Data Sources Overview](./overview.md)
	- [Discovery Pipeline](./jurisdiction-discovery.md)

	---

	Last Updated: {new Date().toISOString().split('T')[0]}

	Data Model Version: 2.1 (Popolo-compatible)