Postgres & backups

PLUR Enterprise runs on PostgreSQL 16 + Apache AGE (graph extension). This page covers how the database is structured, how roles are tightened, how backups work, and what to know operating it.

Roles

The Enterprise server runs as a non-superuser role: plur_app. The docker-compose stack creates this automatically; production deployments must bootstrap it once against a fresh database.

Role	Used by	Privileges
plur_test	Bootstrap only — extension install, role creation, ALTER DATABASE. Historical name; some test scripts still hit it directly.	Postgres superuser
plur_app	The Enterprise server, migrations, all runtime queries.	Least-privilege (curated grants)

The full grant list and the reasoning behind each grant is documented inline in docker/02-plur-app-role.sql. Verification lives in test/security/postgres-roles.test.ts — that suite asserts both the floor (what the app must be able to do) and the ceiling (what a compromised app must not be able to do).

Local docker-compose

Nothing to do. docker compose up -d postgres runs both init scripts in order — 01-init.sql installs AGE as plur_admin, then 02-plur-app-role.sh creates plur_app with the curated grants.

The two DSNs are predictable:

# Bootstrap (existing tests still use this)
postgresql://plur_test:plur_test_only@localhost:5432/plur_enterprise_test

# Application — what production servers should use
postgresql://plur_app:plur_app_only@localhost:5432/plur_enterprise_test

The runtime DSN that PLUR Enterprise uses must always point at plur_app, not plur_test. The latter is for bootstrap and tests only.

Production bootstrap

Once you have a fresh Postgres database:

# As a superuser (postgres / your DBA account):
psql $SUPERUSER_DSN -f docker/01-init.sql
psql $SUPERUSER_DSN -v ON_ERROR_STOP=1 -f docker/02-plur-app-role.sql

# Then run migrations as plur_app:
PLUR_DATABASE_URL=postgresql://plur_app:...@host/db \
  docker run --rm ghcr.io/plur-ai/enterprise:latest npm run migrate

After this, PLUR_DATABASE_URL in production points at plur_app exclusively.

Migrations

Numbered SQL files in src/db/migrations/:

src/db/migrations/
├── 001-init-schema.sql
├── 002-add-engram-tags.sql
├── ...
└── 010-audit-aggregate.sql

Runner: src/db/migrate.ts. Idempotent — runs on every container start, skips already-applied migrations.

For zero-downtime deploys with breaking schema changes: deploy the migration in N, deploy the code change that uses it in N+1. The migration runner tolerates running ahead of code.

AGE

Apache AGE is a Postgres extension that adds Cypher graph queries. PLUR uses it for the permission graph: users, groups, scopes, and memberships are graph nodes and edges. Scope resolution is a Cypher query (src/db/graph.ts).

If you’ve never touched AGE before: it’s just another extension. CREATE EXTENSION age;, then you can run Cypher inside Postgres via the AGE-provided functions. No separate database to operate.

Backups

Daily logical dumps run inside the Postgres container (docker/backup.sh):

/var/backups/plur/
├── plur-2026-05-25.sql.gz
├── plur-2026-05-24.sql.gz
└── ...

Default retention: 14 days local. Configure with PLUR_BACKUP_RETENTION_DAYS.

Off-host push

To survive host loss, push backups to an S3-compatible target:

# In .env
PLUR_BACKUP_S3_URL=s3://my-bucket/plur-backups/
PLUR_BACKUP_S3_ACCESS_KEY=...
PLUR_BACKUP_S3_SECRET_KEY=...
PLUR_BACKUP_S3_REGION=eu-central-1

The backup script uploads each new dump after compression. Failed uploads alert via SMTP (if configured) and via the backup.failed webhook event.

Restore

gunzip -c plur-2026-05-25.sql.gz | psql $SUPERUSER_DSN

Restore as superuser (the dump includes role creation and extension installation). Migrations don’t need to re-run after restore — the dump already includes the latest applied migrations.

Tuning

PLUR Enterprise is not a heavy database workload. Defaults work for orgs up to a few thousand users. The hot paths are:

• Recall — BM25 + embedding similarity. Embedding columns are indexed with pgvector (or AGE indexes for graph queries). On the order of <10ms per recall.
• Audit writes — append-heavy. The audit table is partitioned by month (src/db/migrations/008-audit-partition.sql) so old partitions can be detached / archived cheaply.
• Graph queries — scope resolution. Cached at the session level; refreshed on group-membership changes. Sub-millisecond after warm.

For larger deployments, the obvious knobs:

• shared_buffers — default 25% of RAM
• effective_cache_size — 75% of RAM
• work_mem — 16 MB
• Connection pool size — match to PLUR_PG_POOL_SIZE env var (default 10)

Multi-org

A single deployment can host multiple orgs. They share the database but live in separate logical schemas managed by src/db/tenant.ts. Permission resolution is org-fenced at the resolver level; cross-org reads are impossible by construction.

For very large orgs (>10k users), consider a dedicated deployment instead. Talk to the PLUR team about sizing.