# PostgreSQL setup and migration

Migrate explicitly, verify the data, and switch web and worker processes together.

Use a PostgreSQL deployment with pgvector available and a separate administrator connection for maintenance. Set RALTI_DATABASE_ADMIN_URL only in the trusted maintenance environment. Web and worker processes receive DATABASE_URL for a restricted runtime role. Ralti rejects superusers, table owners, and roles that bypass row-level security.

```bash
npm run database -- migrate
npm run database -- provision --role ralti_app --output .env.postgres
```

Migrations under migrations/postgres/ run in order under an advisory lock and record checksums. Modify the schema with a new migration; do not edit an already applied migration. Provisioning generates a credential unless an explicit private runtime password is supplied, refuses an existing role or output file, and writes the connection privately. Transfer it to your deployment secret store.

## Rehearse before cutover

```bash
npm run database -- import-sqlite --source .atlas/atlas.sqlite --backup-dir ../ralti-private-backups/rehearsal
```

The importer first takes a SQLite online backup, including committed WAL data, then checks integrity and foreign keys. It requires an empty destination and verifies imported tables by row counts and canonical content hashes before committing. IDs, timestamps, history, deduplication keys, and encrypted credentials are preserved; unknown source tables or mismatches fail the import.

- Run the imported copy in an isolated environment with outbound workers disabled.
- Check sign-in, records, roles, history, files, and retrieval. Make and restore-verify a PostgreSQL backup.
- For final cutover, stop application and worker writes, repeat the consistent import into a fresh destination, copy private files, and retain the original snapshot.
- Point web and worker processes at the same runtime database, preserve the encryption key, and complete signed-in acceptance checks.

> **Connection budget** DATABASE_POOL_SIZE applies per process. Count web and worker replicas together. Use verified TLS and the database provider’s supported direct or pooling connection. Do not disable certificate checks to work around a CA problem.

Once PostgreSQL accepts new writes, the old SQLite file is stale. The tooling does not offer a lossless PostgreSQL-to-SQLite reverse migration.