PostgreSQL

PostgreSQL holds SpeculaPro’s permanent record: workspaces and users, customers and accounts, transactions, rules and their versions, alerts, cases and the audit log. It is the system you must protect and back up most carefully.

Requirements

• PostgreSQL 18 or later. The schema uses uuidv7(), which first appeared in Postgres 18. On older versions the first migration fails.
• The citext extension (case-insensitive text, used for email addresses). It ships with PostgreSQL and is created by the migrations automatically.
• In production, Pigsty is the recommended way to run Postgres. It packages high availability (Patroni), connection pooling (PgBouncer), backups (pgBackRest) and monitoring (Prometheus and Grafana) into one tested distribution. Any well-run PostgreSQL 18 works, including managed services.

Database roles

SpeculaPro uses three database users, each with only the rights it needs. This is a safety measure: the part of SpeculaPro that faces the internet cannot read across institutions or change the schema, even if it is compromised.

Role	Used by	Rights
speculapro_owner	speculapro migrate only	Owns the tables; creates and changes the schema
speculapro_app	The api and ingest roles (SP_DB_URL)	Reads and writes data. Row-level security applies: every query only sees the current workspace’s rows
speculapro_system	The decision, persist, stream, connector, analytics and worker roles for their batch work, and every process for the installation’s own tables (SP_DB_SYSTEM_URL)	Reads and writes data across all workspaces in batches. Has BYPASSRLS. It is also the only role that can read the installation’s own tables

Set SP_DB_SYSTEM_URL for every process

The decision role, the persister, the stream evaluator, the connector, the analytics role and the worker handle every workspace at once, so they need speculapro_system. If SP_DB_SYSTEM_URL is not set, they fall back to SP_DB_URL, which in production is speculapro_app, and row-level security would block their writes.

So they check before doing anything: at start-up, a process running any of those roles asks PostgreSQL whether its database user can bypass row-level security (superuser or BYPASSRLS). If it can’t, the process refuses to start with:

error: persist/stream/worker/decision roles and the provider console: database role cannot bypass row-level security (set SP_DB_SYSTEM_URL to the speculapro_system role)

Set it in the environment of every process, the ones that run only api or ingest included:

SP_DB_SYSTEM_URL='postgres://speculapro_system:<password>@pg.internal:5432/speculapro?sslmode=verify-full'

An api or ingest process still does everything a person or an API key asks through SP_DB_URL (speculapro_app), under row-level security. It uses the system connection for one thing: reading the licence and its usage figures, which speculapro_app is not allowed to read (see Tables that belong to the installation). On a self-hosted installation outside development, such a process refuses to start without it:

error: license: database role cannot read the installation's licence tables: SP_DB_SYSTEM_URL is not set, so this process would read the licence as the application role and never enforce it (set SP_DB_SYSTEM_URL to the speculapro_system role, as for every process)

This is deliberate. A licence that can’t be read allows every action (a licensing fault never locks anyone out), so a process that could never read it would run with no licence limits at all and only a line in the log to show for it. Set SP_DB_SYSTEM_URL and start it again.

In local development everything connects as the superuser, so the check passes without it.

Tables that belong to the installation

A few tables hold the installation’s own state, not a workspace’s: the licence, its daily usage figures and the key that signs usage reports (license_installation, licenses, license_usage_daily), the backtest runners’ heartbeats (backtest_runners) and the notification channel record (notify_channel_config). They have no workspace column for row-level security to filter on, so from migration 00056 they are closed to speculapro_app altogether, twice over: row-level security is switched on with no policy, so the role is refused every row, and grants.sql revokes its privileges on the tables. Only speculapro_system and the owner read them.

The same migration and grants.sql also:

• switch row-level security on, with no policy, on every monthly partition (and on each new one as it is created). A query that names a partition directly, such as transactions_p202610, is therefore refused for speculapro_app; queries through the parent table are filtered by the parent’s policy as before;
• leave speculapro_app able to read and add to the shared, signed catalogues (rule_packs, rule_pack_bundles, jurisdiction_bundles) but not to change or delete them, able to read but not write the migration tool’s version table, and unable to rewrite the per-number count of WhatsApp codes.

What is row-level security?

Row-level security (RLS) is a PostgreSQL feature that filters rows inside the database itself. SpeculaPro tells Postgres which workspace each request belongs to, and Postgres then refuses to return or change any other workspace’s rows, even if the application has a bug. See Tenant isolation.

Superusers skip row-level security

The local Docker Compose stack connects as the superuser speculapro, so RLS does not protect anything there. That is fine for development. In production, the application must connect as speculapro_app.

How to prepare the database

Two files in deploy/sql/ set up the database: roles.sql (before migrations) and grants.sql (after migrations). Do the steps below once, in this order.

1. Set real passwords. Copy deploy/sql/roles.sql and replace each change-me with a long random password (openssl rand -base64 32 is one way to make one). Store the passwords in your secret manager.

2. Create the database, as a PostgreSQL superuser:

   psql -c "CREATE DATABASE speculapro"

3. Create the roles, as a superuser, connected to the new database:

   psql -d speculapro -f roles.sql

   This creates the three roles, makes speculapro_owner the owner of the database and of the public schema, removes the default rights every PostgreSQL user has to connect and create objects, and gives the app and system roles access to every table the owner creates later.

4. Run the migrations as the owner:

   SP_DB_URL='postgres://speculapro_owner:<password>@pg.internal:5432/speculapro?sslmode=verify-full' \
     speculapro migrate up

5. Grant the sign-in functions, as a superuser or as speculapro_owner:

   psql -d speculapro -f deploy/sql/grants.sql

   Before someone signs in, SpeculaPro doesn’t yet know which workspace they belong to, so it can’t rely on row-level security to find their account. A few narrow database functions do exactly those lookups (find a user, a session, an API key, a workspace for single sign-on, a SCIM token) and nothing else. grants.sql lets the app and system roles call them. The functions are created by the migrations, which is why this step comes last.

Skipping step 5 means nobody can sign in

Without grants.sql, the application starts normally but every sign-in and every API-key request fails with permission denied for function sp_auth_find_…. Run it again after every upgrade; it is safe to repeat, and several upgrades depend on it: an upgrade that adds a lookup function, such as sp_scim_find_token for SCIM provisioning (migration 00049), needs it, or every SCIM request fails with permission denied for function sp_scim_find_token; migration 00056 needs it for the worker to keep creating monthly partitions (sp_ensure_monthly_partitions) and to close the installation’s own tables to the app role. The Helm chart runs it for you at every helm upgrade.

Always run migrations as speculapro_owner

roles.sql grants the app and system roles access to tables created by speculapro_owner. If you run migrations as another user, the new tables are not covered: the application starts but every query fails with permission denied.

With Pigsty

Declare the three users in your Pigsty inventory’s pg_users (with bypassrls for speculapro_system) and the database in pg_databases, owned by speculapro_owner, instead of running roles.sql by hand. Give speculapro_app pgbouncer: true so it can connect through the pooler; leave the other two out of it. Then run the ALTER SCHEMA, REVOKE, GRANT and default-privilege statements from roles.sql in the speculapro database, run the migrations as speculapro_owner, and finish with grants.sql. See the Pigsty documentation for the exact inventory syntax.

Direct or through PgBouncer

SpeculaPro connects to PostgreSQL with up to three logins, and they don’t all need the same thing:

Connection	Holds session state?	Where to point it
System role (SP_DB_SYSTEM_URL): the background roles	Yes. The worker’s leader lock, the licence check, the screening runner and the analytics sync each hold a session-level advisory lock on one connection	The primary directly (Pigsty’s default service, port 5436), or a pool in session mode. A transaction-mode pool breaks these locks
Owner role: SP_DB_URL set to speculapro_owner for speculapro migrate only	Yes. A migration run holds a session lock	The same: direct, or session mode
App role (SP_DB_URL): the api and ingest roles	No. It sets the workspace for each transaction and uses only transaction-scoped locks	A transaction-mode pool (Pigsty’s primary service, port 5433, which goes through PgBouncer), with the conditions below. Direct works too

For the app role through a transaction-mode PgBouncer, one of these must hold, because the database driver runs queries as prepared statements:

• PgBouncer 1.21 or newer with max_prepared_statements above 0 (recent releases set it to 200 by default), or
• any PgBouncer, with default_query_exec_mode=cache_describe added to the connection string, so the driver keeps no named statements on the server.

One login

If SP_DB_SYSTEM_URL isn’t set, one login does everything, session locks included, so connect it directly (port 5436). SpeculaPro’s own automated tests connect directly; if you change your pooler’s mode or version, try the app role against it on a test installation first.

You may not need a pooler at all: each SpeculaPro process keeps its own pool and reuses connections (SP_DB_MAX_CONNS, default 20 per pool per process), so connecting directly never means a connection per request. PgBouncer starts to matter when the number of API replicas times that figure approaches the server’s max_connections.

How to run migrations

Migrations are built into the binary. Point SP_DB_URL at the owner role for these commands only:

export SP_DB_URL='postgres://speculapro_owner:<password>@pg.internal:5432/speculapro?sslmode=verify-full'

speculapro migrate status   # what is applied, what is pending
speculapro migrate up       # apply everything pending

status prints one line per migration, marked applied or pending. up is safe to run repeatedly; it only applies what is pending. In development, make migrate and make migrate-status do the same with the local settings. When SP_DB_OWNER_URL is set they connect with it instead of SP_DB_URL, so migrations run as the schema owner even where SP_DB_URL names speculapro_app; the connection string isn’t printed.

migrate down deletes data

speculapro migrate down rolls back the most recent migration, and most rollbacks drop tables. Never run it on a production database. Restore from backup instead.

Partitions

The largest tables are split by month into partitions, so old months can be archived or dropped cheaply and queries over recent data stay fast:

Table	Split by
transactions	When the transaction happened
decisions	When the decision was made
rule_hits	When the rule fired
audit_log	When the event was recorded
customer_events	When the account event happened

The migrations create partitions from one month back to three months ahead of the day you run them, plus a default partition that catches anything outside that range.

The worker keeps partitions ahead of time

The worker role creates any missing monthly partitions, from one month back to three months ahead (the same range as the migrations), every time it runs (at start-up, then every 6 hours by default). As long as one worker is running somewhere in your installation, there is nothing to do by hand.

The worker connects as speculapro_system, which owns no tables and may not create any. So it creates partitions through an owner function, sp_ensure_monthly_partitions, that grants.sql lets it call. Before migration 00056 the function ran with the caller’s rights, so on an installation with separate database roles the worker’s calls failed with permission denied for schema public (logged as worker: ensure partitions) and no partitions were created after the ones the migrations had made. See Upgrading if your installation ran that way for more than three months.

Not running the worker role?

If your installation runs without any worker process, create future partitions yourself at least once a month as speculapro_owner, for example from cron:

SELECT sp_ensure_monthly_partitions('transactions');
SELECT sp_ensure_monthly_partitions('decisions');
SELECT sp_ensure_monthly_partitions('rule_hits');
SELECT sp_ensure_monthly_partitions('audit_log');
SELECT sp_ensure_monthly_partitions('customer_events');

Each call creates whatever is missing from last month to three months ahead, and does nothing if they already exist. If partitions run out, new rows land in the default partition. That loses no data, but it makes queries slower and stops PostgreSQL creating that month’s partition later until the rows are moved out.

Connections

Each SpeculaPro process opens at most SP_DB_MAX_CONNS connections (default 20) per pool. Every process with SP_DB_SYSTEM_URL set has a second pool for the system role, so it can open up to twice that (an api-only process uses its second pool very little: the licence state is read at most every 30 seconds). A worker with SP_DB_REPLICA_URL set opens a third pool, on the replica. Add up all processes and keep the total below the server’s max_connections, leaving room for administrators and backups. Because each process pools its own connections, you often don’t need a pooler in front; if you add PgBouncer, see which connection can go through it.

A read replica for backtests

A backtest reads up to a year of one workspace’s transactions in time order: the period, up to 180 days, plus up to 180 days of warm-up before it. These are long sequential reads. On a small installation the primary handles them easily. On a large one, send them to a streaming replica so they don’t compete with live monitoring:

1. Set up a streaming (physical) replica of the SpeculaPro database, as you would for failover. Pigsty and managed services offer this.

2. On every worker process that runs backtests (SP_BACKTEST_WORKERS above 0), set SP_DB_REPLICA_URL to the replica, connecting as speculapro_system:

   SP_DB_REPLICA_URL='postgres://speculapro_system:<password>@pg-replica.internal:5432/speculapro?sslmode=verify-full'

3. Restart the workers. At start-up each one checks that this user can bypass row-level security, and refuses to start otherwise with SP_DB_REPLICA_URL: database role cannot bypass row-level security (use the speculapro_system role on the replica). Backtest queries filter by workspace themselves, as the other system-role queries do.

Only the history reads go to the replica. The queue, progress and results are written to the primary. A replica that lags behind the primary simply means the most recent data isn’t in the run: each result records data up to, the latest time a transaction it read was received.

Backups

• Use continuous archiving with point-in-time recovery (pgBackRest, which Pigsty includes, or your managed service’s equivalent), not only nightly dumps. Monitoring records are evidence; losing a day of them is a compliance problem.
• Keep the encryption keys out of the database backup and store them separately. A backup is only useful together with the keys.
• Test a restore regularly, into an isolated environment.
• Check how long your regulator requires you to keep records, and set retention accordingly.

Related

• Configuration reference: database
• Tenant isolation
• Data model