Switch from SQLite to PostgreSQL for database backend

[chetanr25]

Problem

FireForm currently uses SQLite as its database. The database file (fireform.db) lives inside a Docker volume and is accessed directly by the app container. This works for a single developer running things locally, but it falls apart when we think about real deployments.

SQLite allows only one writer at a time. If two API requests try to write to the database at the same time, one of them has to wait. With gunicorn running multiple workers in production (which we already do see WEB_CONCURRENCY in the prod compose file), this becomes a real problem. Multiple workers means multiple processes trying to write to the same SQLite file, and that leads to lock contention and failed requests.

Beyond concurrency, there is a real risk of data loss with the current setup. The SQLite file lives inside a Docker volume. If the container gets recreated, the volume gets accidentally pruned, or someone runs docker compose down -v (which we even expose as make super-clean), the entire database is gone. Even with volumes mounted properly, Docker volumes are not a reliable storage layer for production data. There is no built-in replication, no point-in-time recovery, no automated backups, and no way to recover if the host machine fails. For a tool that stores form templates and submission records for organisations, losing that data is not acceptable.

Proposal

Switch to PostgreSQL. This has two parts - how we handle it in development and how we handle it in production. These are intentionally different because the requirements are different.

Part 1: Development - PostgreSQL in Docker Compose

For local development, we add a postgres service directly in docker/dev/compose.yml. This keeps the developer experience simple - you run make fireform and everything comes up, including the database.

The service would look something like this:

postgres:
  image: postgres:17-alpine
  container_name: fireform-postgres
  environment:
    POSTGRES_USER: fireform
    POSTGRES_PASSWORD: fireform
    POSTGRES_DB: fireform
  volumes:
    - postgres_data:/var/lib/postgresql/data
  ports:
    - '127.0.0.1:5432:5432'
  networks:
    - fireform-network
  healthcheck:
    test: ['CMD-SHELL', 'pg_isready -U fireform']
    interval: 10s
    timeout: 5s
    retries: 5

And in .env.dev:

DATABASE_URL=postgresql://fireform:fireform@postgres:5432/fireform

The app container would depends_on postgres with condition: service_healthy, same pattern we already use for ollama. For development, we mount the postgres data to a Docker volume. If the volume gets wiped, it does not matter since this is just local dev data that can be recreated by restarting the app (seed data gets re-inserted on startup via init_db()).

Part 2: Production - External PostgreSQL, NOT in Docker

This is the important part. We should NOT run PostgreSQL inside Docker in production.

Running a database in a Docker container in production is generally a bad idea for several reasons:

• Docker containers are designed to be disposable. Databases are not. Even with mounted volumes, a single misplaced docker compose down -v or a volume driver issue can wipe production data permanently.
• Docker volumes are not a substitute for a proper storage layer. They do not provide checksumming, replication, or any guarantees beyond "the data is somewhere on the host filesystem." If the host disk fails, the data is gone.
• No automated backups, failover, or replication out of the box. You would have to build all of that yourself on top of Docker, which defeats the purpose.
• Performance overhead from the storage driver layer.
• If the host goes down, the database goes down with it and recovery is entirely manual, assuming the volume data is even intact.

The bottom line is that production data needs to live in a system designed to keep it safe. Docker is not that system.

For production, organisations deploying FireForm should use a proper PostgreSQL instance outside of Docker. This could be:

• Managed services: AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL, Supabase, Neon, etc. These handle backups, replication, scaling, and security patches automatically.
• Self-hosted PostgreSQL: Installed directly on a server (not in a container). This is fine too if the org has a DBA or someone who knows how to manage Postgres.

Either way, the database lives outside of Docker, managed by systems that are actually built for data durability, automated backups, replication, and recovery. From FireForm's perspective, the only thing that changes is the DATABASE_URL in .env.prod. That is the whole point of this design. The application code does not care where PostgreSQL is running.

The production compose file (docker/prod/compose.yml) would not include a postgres service. It stays as-is with just ollama, whisper, and the app. The app just needs the database URL:

DATABASE_URL=postgresql://user:password@your-postgres-host:5432/fireform

This means the prod deployment instructions would be something like:

1. Set up a PostgreSQL database (managed or self-hosted, whatever your org uses)
2. Create a database called fireform
3. Put the connection URL in .env.prod as DATABASE_URL
4. Run make fireform - the app handles table creation on startup via init_db()

[chetanr25]

Would love to hear your feedback @marcvergees @vharkins1 @abhishek-8081

[marcvergees]

Sounds good to us, greenlight to go ahead with option 1.

[chetanr25]

Raised Issue in #558
and fixed in PR #559 #560

[chetanr25]

Closing this discussion as the migration is made and PR is merged.