Database Overview¶
📘 See also: Current ER diagram — the canonical schema reference.
The application connects to PostgreSQL using the DB_URL environment variable, supporting any accessible PostgreSQL instance (local, remote, DSI-hosted, pgbouncer, Kubernetes, etc.). This ensures modularity and scalability—just update DB_URL as needed.
Local Development¶
- Start a local PostgreSQL instance:
docker-compose up -d postgres - Create a
.envfile inbackend/or copy .env.example and change accordingly:DB_URL=postgresql://co2calculator:password@postgres:5432/co2calculator - Start backend and frontend:
# Terminal 1
cd backend
make dev
# Terminal 2
cd frontend
npm run dev
Running All Services with Docker Compose¶
- To run all services (backend, frontend, db):
docker-compose up -d - Ensure
.envis configured as above for backend connectivity. - Change the host in DB_URL to match the postgresql service name in the docker-compose file
Remote/Production Environments¶
- Set
DB_URLto your remote DB (DSI, cloud, etc.):DB_URL=postgresql://user:password@db.example.com:5432/co2calculator?sslmode=require - For Kubernetes/Helm, configure
values.yaml:database: existingSecret: name: "db-secret" keys: # use for backend and migrations job url: DB_URL - Migrations run automatically via a Helm hook Job on deployment
(
helm/templates/migration-job.yaml,post-install,pre-upgrade, so a failed migration blocks the rollout). To check it:kubectl logs -f job/co2-calculator-migration-<release revision>
Schema & Migrations¶
- All environments (local, remote) are aligned using Alembic migrations.
- To create and apply migrations:
cd backend make db-revision message="Add column" make db-migrate - Migrations are applied automatically in Helm deployments by that hook Job (
post-install,pre-upgrade, weight −1, deleted once succeeded) - See erd.md for the schema diagram.
ORM Integration¶
- Models use SQLAlchemy 2.0 (
backend/app/models/). - Do not use PostgreSQL-specific SQLAlchemy dialects (e.g.,
func.array_agg()), so tests run on SQLite in-memory. - Example test run:
pytest # Uses SQLite automatically
Notes¶
- PgBouncer sits in front of the dev DBaaS instance only (transaction
pooling since 2026-09-23,
default_pool_size60,max_client_conn1000,query_wait_timeout10 s; stage and prod still hit Postgres directly, see the connection budget). The app still connects to port 5432 viaDB_URL; SQLAlchemy's in-process pool (DB_POOL_SIZE/DB_MAX_OVERFLOW, set per environment inopenshift-app-config) is the client side of it. Consequences on dev: - The wall is 60 in-flight transactions fleet-wide, not client
connections: a server slot is held only while a transaction runs, and
an idle pooled connection costs Postgres nothing. The "1000" is
max_client_connand is never the wall. - A transaction that waits past the bouncer's
query_wait_timeout(10 s) getspsycopg.errors.ProtocolViolation: query_wait_timeout; the request-scoped session fromget_dbholds its slot from its first query to the end of the response (#2654), andget_current_userhands its own back before the route body runs (#2689). - Who opens connections, the three layers with their timeouts, the per-environment budgets and which knob to turn on each alert: connection budget.
- The
db.server.connectionsgauge is emitted by the pod heartbeat, which blocks at the bouncer during a stall, so a flat line during an incident is a frozen value. - For table/index details, see erd.md.
Last Updated: 2026-09-23