Database

PostgreSQL

Retrospend runs on PostgreSQL 16 with Prisma as the ORM. Prisma handles schema management and migrations. The database stores all user data, expenses, settings, and audit logs.

Connection Settings

The default pool uses up to 20 connections with a 30-second idle timeout and a 5-second connection timeout. The connection string format is postgresql://user:password@host:5432/database. When running with Docker Compose, the host is the service name (e.g. postgres).

Row-Level Security

Row-Level Security (RLS) is enabled on all user-facing tables. Every query is scoped to the current user via app.current_user_id, set at the start of each request. This means even raw SQL queries cannot access another user's data. RLS policies are defined in the Prisma migrations.

Migrations

Migrations run automatically on startup. For manual migration:

npx prisma migrate deploy

To regenerate the Prisma client after schema changes:

npx prisma generate

Backups

The sidecar service runs automated database backups on the BACKUP_CRON schedule (default: daily at 3 AM UTC). Old backups are cleaned up after BACKUP_RETENTION_DAYS (default: 30 days). To create a manual backup:

docker exec retrospend-postgres pg_dump -U postgres retrospend > backup.sql