Postgres & backups

PLUR Enterprise runs on PostgreSQL 16 + Apache AGE (graph extension) + pgvector (semantic recall). This page covers how the database is structured, how roles are tightened, and how backups work.

Roles: the two-DSN model

The cluster is reached through two roles — a least-privilege split so a compromised app cannot take over the database or host:

Role	Used by	Privileges
plur_admin	Bootstrap (extension install, role creation) and privileged migrations only	superuser / CREATEROLE
plur_app	All runtime queries — what the running server connects as	Least-privilege (curated grants)

Two connection strings map onto them:

• DATABASE_URL — the runtime DSN, plur_app. The running server connects with this.
• MIGRATION_DATABASE_URL — the migration DSN, plur_admin. Used only by the short-lived startup step that runs migrations and grants plur_app its runtime access.

Both point at the same database; only the role differs. MIGRATION_DATABASE_URL is optional — when unset, migrations fall back to DATABASE_URL, so a single-DSN deployment keeps working (without least-privilege at runtime).

plur_app holds only the floor it needs and is denied the ceiling that enables host takeover (CREATE ROLE, ALTER SYSTEM, CREATE EXTENSION, server-file read, COPY … PROGRAM). The grant list is asserted by test/security/postgres-roles.test.ts — floor and ceiling.

Migrations

Numbered SQL files in src/db/migrations/, applied sequentially and idempotently on every container start (via the migration DSN). Currently 18 migrations, from 001-base-schema (engrams, episodes, users, audit_log) through OAuth/OIDC/SAML/SCIM/API-key/webhook tables, saved packs, review policies, 015-audit-signed-chain (tamper-resistant audit chain — a privileged migration that reassigns audit_log ownership away from the runtime role), and 016-engrams-embedding (pgvector column + HNSW index for semantic recall).

Most migrations run fine as plur_app; privileged ones self-check and fail fast under an insufficient role.

AGE

Apache AGE is a Postgres extension that adds Cypher graph queries. PLUR uses it for the permission graph: users, groups, and projects 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 — no separate database to operate. The bundled DB image ships with AGE and pgvector preinstalled.

pgvector (semantic recall)

Hybrid recall (BM25 + embedding cosine similarity, fused with Reciprocal Rank Fusion) is shipped. Embeddings are computed in-process with a local model — no external API. Where pgvector is absent, recall degrades cleanly to keyword-only and the recall API reports which mode actually ran.

Backups

Daily logical dumps (pg_dump custom format), GPG-encrypted with BACKUP_ENCRYPTION_KEY. Configured via env:

Variable	Default
BACKUP_TARGET	local (additional targets planned)
BACKUP_LOCAL_PATH	./backups
BACKUP_ENCRYPTION_KEY	auto-generated on first backup — store it in a vault
BACKUP_RETENTION_DAYS	30
BACKUP_RETENTION_COUNT	keep at least 10 regardless of age

Off-host push

To survive host loss, encrypted dumps are pushed to a separate host via rsync-over-SSH (systemd timer units, documented in the bundled setup guide). For object storage, swap the rsync call for rclone/restic — the encrypt-then-push shape is unchanged. Off-host copies also double as tamper evidence for the signed audit chain.

Restore

gpg --batch --passphrase-file /path/to/passphrase --decrypt \
    plur_enterprise.dump.gpg > plur_enterprise.dump

pg_restore --dbname plur_enterprise --clean --if-exists plur_enterprise.dump

Restore onto a host with Postgres + AGE + pgvector installed. A full restore runbook ships with the deployment bundle.

Multi-org

A single deployment can host multiple orgs. They share the database but live in separate schemas (org_<orgId>), each with its own AGE graph, managed by src/db/tenant.ts. Permission resolution is org-fenced at the resolver level; cross-org reads are impossible by construction.