Database

The controller keeps everything it knows in one database. PostgreSQL is required in production. The standard main and next deploy channels start a bundled postgres service. The nodb and nodb-next channels omit that container and require POSTGRES_HOST and POSTGRES_PASSWORD for an external PostgreSQL server. See install for channel setup.

SQLite is for development, tests, and demos only. Set DATABASE_URL to a file such as file:/app/data/cpm.db to use it outside production; the controller rejects SQLite when NODE_ENV=production.

Both run the same code and the same migrations, which run on every start. The README has every connection option.

Moving from SQLite to PostgreSQL

For a demo or development install, the server copies a SQLite database into an empty PostgreSQL database itself, row for row. Ids, encrypted secrets (still sealed under the same SESSION_SECRET), sessions, API tokens and the audit log’s hash chain all arrive as they were, and each sequence is moved past the ids copied, so nothing has to be set up again.

1. Let the SQLite install finish upgrading. The copy refuses a database that this version has not migrated: start the new release on it once.

2. Stop the server, so nothing is written while it copies:

   docker compose stop web

3. Start PostgreSQL. With the bundled stack, set POSTGRES_PASSWORD in .env and:

   docker compose up -d --wait postgres

4. Check first. --dry-run reads both databases and counts what would be copied, writing nothing:

   docker compose run --rm --entrypoint /app/cpm-server web --copy-to-postgres --dry-run

5. Copy:

   docker compose run --rm --entrypoint /app/cpm-server web --copy-to-postgres

   It creates the schema, copies every table in one transaction, and then counts every table on both sides. It ends with the counts and exit code 0 when they all match; anything else is named, with exit code 1.

6. Switch over. Remove DATABASE_URL from .env (the POSTGRES_* values take over), start the server, and sign in. Keep the SQLite file until you have.

The source is DATABASE_URL and the target the POSTGRES_* values unless you name them: --from file:/app/data/cpm.db and --to postgres://user:password@host:5432/cpm. The target must be empty: a database that already holds rows is refused, unless --allow-non-empty asks to skip the rows whose key is already taken, which the counts then report. From a checkout, the same command is bun scripts/sqlite-to-postgres.ts in apps/controller.

A database from before 3.0 is a different case: the setup screen finds it and migrates it into whichever database the new install uses.