Skip to content

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 .env file in backend/ 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 .env is 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_URL to 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_size 60, max_client_conn 1000, query_wait_timeout 10 s; stage and prod still hit Postgres directly, see the connection budget). The app still connects to port 5432 via DB_URL; SQLAlchemy's in-process pool (DB_POOL_SIZE/DB_MAX_OVERFLOW, set per environment in openshift-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_conn and is never the wall.
  • A transaction that waits past the bouncer's query_wait_timeout (10 s) gets psycopg.errors.ProtocolViolation: query_wait_timeout; the request-scoped session from get_db holds its slot from its first query to the end of the response (#2654), and get_current_user hands 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.connections gauge 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