Skip to main content
OpenCouncil uses PostgreSQL with the PostGIS extension for geospatial features. This guide covers database setup, schema migrations, and seeding.

​
Requirements

OpenCouncil requires PostgreSQL 14+ with PostGIS 3.3.5+ for production. Development environments use these versions by default.
Core requirements:
  • PostgreSQL 14 or higher
  • PostGIS 3.3.5 or higher (3.5 works but 3.3.5 matches production)
  • Prisma CLI (included in dev environments)

​
Database schema

OpenCouncil uses Prisma as an ORM. The schema is defined in prisma/schema.prisma with 30+ models: Key models:
  • City - Municipalities with PostGIS geometry
  • CouncilMeeting - Meetings with video/audio media
  • Person - Council members with voice recognition
  • Party - Political parties
  • SpeakerSegment - Speaker time intervals
  • Utterance & Word - Transcription data
  • Subject - Agenda items with location/topic
  • Notification - User notifications
  • TaskStatus - Async job tracking
Multi-tenant design: Many models use composite keys (cityId, id) for data isolation. View the full schema: 
cat prisma/schema.prisma

​
Setting up a new database

1

Create database with PostGIS

CREATE DATABASE opencouncil;
\c opencouncil
CREATE EXTENSION IF NOT EXISTS postgis;
2

Configure connection strings

Add to .env:
DATABASE_URL="postgresql://user:password@host:5432/opencouncil?sslmode=require"
DIRECT_URL="postgresql://user:password@host:5432/opencouncil?sslmode=require"
See Environment setup for details.
3

Run migrations

npx prisma migrate deploy
This applies all migrations from prisma/migrations/.
4

Seed the database (optional)

npx prisma db seed
See Database seeding below.

​
Schema migrations

OpenCouncil uses Prisma Migrate for schema versioning and deployments.

​
Development workflow

Always use --create-only when making schema changes. This prevents accidentally applying migrations to the wrong database.
1

Edit the schema

Modify prisma/schema.prisma:
Example: Add field
model City {
  id        String   @id @default(cuid())
  name      String
  // Add new field
  population Int?
  // ...
}
2

Create migration (do not apply)

npx prisma migrate dev --name add_population_to_cities --create-only
This generates a migration file in prisma/migrations/ but does not apply it.
3

Review the migration SQL

cat prisma/migrations/<timestamp>_add_population_to_cities/migration.sql
Verify the SQL is correct before applying.
4

Test against local database

npx prisma migrate deploy
Apply to your local database and test the application.
5

Commit and deploy

git add prisma/
git commit -m "Add population field to cities"
Production deployments will apply the migration automatically.

​
Production deployments

Migrations are applied automatically during deployment: 
nix run .#dev  # Runs migrations on startup

​
Migration history

OpenCouncil has 80+ migrations dating back to September 2024. View all migrations: 
ls -1 prisma/migrations/
Example migrations: 
20260207211304_add_speaker_name_to_contributions/
20260202164839_add_diavgeia_decisions/
20260125144744_add_discussed_in_field/
20251026111508_add_notifications/
20250703144238_add_equipment_and_physical_presence_to_offers/
20250203100741_add_administrative_bodies/
20250112125551_add_geometry_to_cities/
Prisma Migrate does not support automatic rollbacks. To revert:
  1. Create a new migration that reverses the changes
  2. Test thoroughly
  3. Deploy the new migration
Never manually edit applied migrations in prisma/migrations/. This breaks migration history.

​
Database seeding

OpenCouncil provides two seeding workflows:

​
Automatic seeding (development)

Local development environments automatically seed the database: 
nix run .#dev  # Seeds automatically on first run

​
Seed data source

The seed script (prisma/seed.ts):
  1. Checks for prisma/seed_data.json locally
  2. If not found, downloads from GitHub repository
  3. Creates test users with different permission levels
  4. Seeds cities, meetings, transcripts, and related data
Test users created:
  • Super Admin (full access)
  • City Admin (city-level access)
  • Party Admin (party-level access)
  • Person Admin (person-level access)
  • Read Only (no admin permissions)
In development, a floating panel allows instant user switching without email authentication.

​
Custom seed data

Generate seed data from an existing database: 
npm run generate-seed -- \
  --source="postgresql://user:pass@host:5432/db" \
  --pairs=chania/latest,athens/latest \
  --output=./prisma/seed_data.json
Options:
  • --source: Source database URL
  • --pairs: Comma-separated cityId/meetingId pairs (use latest for most recent meeting)
  • --output: Path to save JSON file
Seed data structure: 
{
  "topics": [...],
  "cities": [...],
  "administrativeBodies": [...],
  "parties": [...],
  "persons": [...],
  "meetings": [...],
  "voiceprints": [...]
}
See docs/database-seeding.md in the source repository for details.

​
Environment variables

Customize seeding behavior: 
# URL to download seed data from
SEED_DATA_URL=https://custom-url/seed_data.json

# Path to local seed data file
SEED_DATA_PATH=./custom/path/seed_data.json

# City ID for test users
DEV_TEST_CITY_ID=chania

​
Database maintenance

​
Reset local database

This is destructive and cannot be undone. All data will be lost.
nix run .#cleanup  # Removes .data/postgres and .next
nix run .#dev      # Creates fresh database with seed data

​
Prisma Studio (visual database editor)

Open Prisma Studio to browse and edit data: 
# Starts automatically with dev server
# Access at http://localhost:5555

​
Backup and restore

Backup with pg_dump: 
# Full backup
pg_dump "postgresql://user:pass@host:5432/db" > backup.sql

# Schema only
pg_dump --schema-only "postgresql://user:pass@host:5432/db" > schema.sql

# Data only
pg_dump --data-only "postgresql://user:pass@host:5432/db" > data.sql
Restore: 
psql "postgresql://user:pass@host:5432/newdb" < backup.sql

​
Database size monitoring

Check database size: 
SELECT 
  pg_size_pretty(pg_database_size('opencouncil')) as db_size,
  pg_size_pretty(pg_total_relation_size('"CouncilMeeting"')) as meetings_size,
  pg_size_pretty(pg_total_relation_size('"Utterance"')) as utterances_size;

​
Connection pooling

For production deployments with high concurrency, use connection pooling: 
# Install PgBouncer
sudo apt-get install pgbouncer

# Configure pgbouncer.ini
[databases]
opencouncil = host=localhost port=5432 dbname=opencouncil

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
Update .env: 
# Pooled connection
DATABASE_URL="postgresql://user:pass@localhost:6432/opencouncil"

# Direct connection (for migrations)
DIRECT_URL="postgresql://user:pass@localhost:5432/opencouncil"

​
Managed pooling

Many managed PostgreSQL services offer built-in pooling:
  • Supabase: Provides connection pooler on port 6543
  • Neon: Automatic connection pooling
  • AWS RDS Proxy: Managed connection pooling

​
Troubleshooting

​
PostGIS extension not found

CREATE EXTENSION IF NOT EXISTS postgis;
If this fails, install PostGIS on your server: 
# Ubuntu/Debian
sudo apt-get install postgresql-14-postgis-3

# macOS (Homebrew)
brew install postgis

​
Migration failed: version mismatch

Ensure PostGIS versions match between local and production: 
SELECT PostGIS_Version();
OpenCouncil targets PostGIS 3.3.5 for production compatibility.

​
Shadow database access denied

Prisma needs a “shadow database” for migrations. Grant permissions: 
GRANT CREATE ON DATABASE opencouncil TO your_user;
Or use DIRECT_URL pointing to a database where you have full permissions.

​
Connection limit reached

Check active connections: 
SELECT count(*) FROM pg_stat_activity;
Increase max_connections or use connection pooling.

​
Next steps

Environment setup

Configure database URLs and secrets

Docker deployment

Deploy with Docker Compose

Nix deployment

Deploy with Nix flakes

Build docs developers (and LLMs) love

Get started for freeTalk to us