Database Schema

All authenticated users across the platform. Central identity table.

Extended civic profile for citizens — values, interests, engagement level.

Tracks which officials a citizen follows for notifications.

Citizen bookmarks for campaigns they want to track.

Issues a citizen cares about, used for Values Compass matching.

RSVPs for city council meetings.

Audit trail of all civic actions (proposals, votes, comments, donations).

All political contributions tracked for Levine Act compliance. Core compliance table.

Data sources for contribution records (NetFile, manual entry, etc.).

Detected conflicts of interest between contributions and proceedings.

Timeline of compliance-related events (filings, disclosures, alerts).

Calculated compliance scores for officers based on filing history.

Alerts generated before council meetings about potential conflicts.

Flagged contributions requiring additional review.

Governance-level flags for systemic compliance issues.

AI-generated cross-references between donors and agenda items.

Government jurisdictions (cities, counties, states).

Elected officials and appointed officers.

Government proceedings (agenda items, resolutions, ordinances).

Junction table linking officers to proceedings they participated in.

Links proceedings to specific meeting dates.

City-level metrics for dashboards (budget, population, etc.).

City departments and their budgets.

Citizen complaints filed with the city.

API keys for external service integrations.

Core proposals in the Fora system. Citizens submit proposals for civic action.

Formal submissions of proposals to government bodies.

Version history of proposal edits.

Citizens who support (endorse) a proposal.

Proposed solutions within a proposal. Multiple solutions per proposal.

Votes on individual solutions.

Pro/con arguments on proposals, AI-refinable.

Votes on individual arguments.

Overall votes on proposals.

Official sponsors (organizations, officials) of proposals.

Fundraising campaigns attached to proposals.

Volunteer signups for proposal campaigns.

Official responses/adoptions of citizen proposals.

Implementation milestones for adopted proposals.

Citizens who support an officer's adoption of a proposal.

Funding sources identified for proposal solutions.

Itemized cost breakdown for solutions.

Terms and conditions for funding sources.

Nonprofit and campaign organizations.

Members of organizations with roles.

Links between political campaigns and Fora proposals.

Non-monetary contributions (services, goods, volunteer time).

Campaign advisory team members.

Campaign expenditure tracking.

Scheduled volunteer shifts for campaign activities.

Volunteer signups for campaign shifts.

Campaign events (fundraisers, town halls, canvassing).

RSVPs for campaign events.

Payment processing accounts for campaigns.

Community events (I Heart Escondido, service weekends, etc.).

Campaigns associated with community events.

Sponsors of community events.

Conflict declarations from event sponsors (Levine Act compliance).

Alerts when funding is concentrated from few sources.

Official promise/commitment videos from elected officials.

Historical nonprofit impact scores.

Cached results from AI investigation queries.