PostgreSQL

PostgreSQL (recommended) for permissions, users and collections storage. Alternative to MongoDB.

PostgreSQL or MongoDB — pick one. Prisme.ai’s three structured-data stores (users, permissions, collections) run on a single relational engine. The choice is per-deployment — you cannot mix Postgres and Mongo across these three.

We recommend PostgreSQL for new deployments: cheaper managed offerings, ubiquitous ops know-how, Entra ID / IAM passwordless auth on Azure, and simpler backups. See MongoDB only if your team already runs Mongo at scale.

Role in the platform

MongoDB holds three logical databases:

• users — user accounts, authentication metadata. Used by prismeai-api-gateway.
• permissions — access-control rules, roles, workspace memberships. Used by every backend service via the permissions storage.
• collections — workspace data, automation state and product collections. Used by prismeai-runtime.

Version compatibility

• Minimum: PostgreSQL 12+ (14+ recommended).
• sslmode=require is mandatory on all connection strings if ssl is enforced by postgresql server.

Recommended deployment

Provider	Recommended service
AWS	Amazon RDS for PostgreSQL, Multi-AZ.
Azure	Azure Database for PostgreSQL — Flexible Server, with Entra ID passwordless auth.
GCP	Cloud SQL for PostgreSQL, regional HA.
OpenShift / on-prem	Crunchy Postgres for Kubernetes operator, or self-managed with streaming replication.

Topology: primary + at least one standby replica, multi-AZ.

By default, the three databases (users, permissions, collections) live on a single shared cluster — keeps the initial deployment simple.

Under sustained high load, the auth path (users + permissions, critical on every request) can be impacted by collections growth — these have very different access patterns. If a single cluster proves insufficient, split into two PostgreSQL clusters: one for users + permissions, one for collections. They can then be sized and backed up independently.

Configuration

Three Helm keys change compared to MongoDB. Each is marked with a postgresql comment in the chart values.yaml.

URL example: postgres://user:[e-mail]:5432/permissions?sslmode=require.

prismeai-core-values.yml

global:
  storage:
    permissions:
      driver: postgresql
      existingSecret: "core-permissions"     # `url` key

prismeai-api-gateway:
  storage:
    users:
      driver: postgresql
      existingSecret: "core-prismeai-api-gateway-users"

prismeai-runtime:
  storage:
    collections:
      driver: postgresql
      existingSecret: "core-prismeai-runtime-collections"

See Helm install — PostgreSQL for the full install context.

Backup & restore

Backup with pg_dump

pg_dump "postgres://user:password@host:5432/database?sslmode=require" \
  --format=custom \
  --file=/backup/pg/$(date +%Y-%m-%d)-database.dump

Restore with pg_restore

pg_restore --clean --if-exists \
  --dbname="postgres://user:password@host:5432/database?sslmode=require" \
  /backup/pg/<date>-database.dump

Managed services

• RDS: automated daily snapshots + point-in-time recovery.
• Azure Flexible Server: built-in backup with configurable retention.
• Cloud SQL: automated backups with point-in-time recovery.

Operational strategy (RPO/RTO, retention) lives in Operations / Backup.

Updates

• Schema migrations run automatically on backend startup.
• For major Postgres version jumps (e.g. 12 → 14), follow the cloud provider’s blue/green or in-place upgrade procedure. Snapshot first.

See Operations / Updates.

Scaling

• Vertical first: PostgreSQL scales well on a single beefy primary up to most workloads.
• Read replicas when read load is heavy and your application can tolerate stale reads.
• Connection pooling: deploy PgBouncer in front of Postgres for high-concurrency workloads.
• Indexes: monitor with pg_stat_statements. Common hot paths are user lookups and workspace-scoped queries.

Azure Entra ID passwordless auth

On Azure DB for PostgreSQL Flexible Server, use Entra ID Workload Identity to inject short-lived tokens instead of static passwords. Configure azureManagedIdentityClientId on the relevant Helm storage blocks — see Azure deployment notes.