Upgrading

An upgrade replaces the binary and applies any new database migrations. Because migrations are built into the binary, the binary and its schema always travel together.

Before the first release

SpeculaPro has no public releases yet, and there are no published release notes. Until version 1.0, expect breaking changes between versions and ask [email protected] what changed before upgrading; the migrations table below lists what each one does to the database. Rule packs can be updated without upgrading the program: see Signed rule-pack updates.

Upgrade from release to release, never from a branch

Upgrade only to released versions, and verify each one first (see Verifying releases). A release contains every database migration up to its own number. A build taken from a development branch can skip a number that another feature fills in later: once a database has a later migration applied, migrate up refuses to apply an earlier one it missed, and the upgrade stops. Mixing builds from different branches is the usual way to get there.

How to upgrade

1. Read the release notes for every version between yours and the new one. Look for new required settings and for migrations described as slow or locking.

2. Back up Postgres (a fresh point-in-time backup is enough) and confirm your encryption key backups are current.

3. Check what will change:

   SP_DB_URL=<owner connection> ./speculapro-new migrate status

   Lines marked pending are the migrations this upgrade will apply.

4. Stop the old processes, or drain them from the load balancer.

5. Apply migrations with the new binary, as speculapro_owner:

   SP_DB_URL=<owner connection> ./speculapro-new migrate up

6. Create any new topics: ./speculapro-new topics. Existing topics are untouched.

7. Start the new binary with your usual settings.

8. Verify: /readyz?role=api (or the roles that process serves) returns 204, speculapro version shows the new version, and you can sign in.

With Docker Compose

From deploy/compose/production, after the backup:

# 1. Set SPECULAPRO_IMAGE in .env to the new release, by digest
# 2. Apply migrations and create any new topics, as the owner role
docker compose run --rm cli migrate up
docker compose run --rm cli topics
# 3. Apply the grants again (safe to repeat)
docker compose exec -T postgres psql -v ON_ERROR_STOP=1 -U postgres -d speculapro -f /speculapro-sql/grants.sql
# 4. Replace the running containers
docker compose up -d

See Docker Compose in production.

With the Helm chart

helm upgrade speculapro deploy/helm/speculapro -n speculapro -f my-values.yaml

Use the new release’s chart and set the new image.digest. The migration Job (then the psql grants step) and the topics Job run before any pod is replaced, while the previous version is still serving: migrations are written so the previous version keeps working against the new schema. If a Job fails, the upgrade stops and the old pods keep running. See Kubernetes with Helm.

Notes for specific migrations

Some migrations change existing data or need a quiet moment. migrate status lists them by number.

Migration	What it changes on upgrade
00079_ai_forget_unused_key	AI assistant: deletes the stored own key (ai_settings.api_key_enc) of every workspace that is on the installation’s key, left there by builds that kept it after the switch. Such a workspace must enter its key again if it returns to its own; nothing else changes. From this version an AI answer of likely false positive that no longer stands on a held alert or case carries superseded in the API, and a request for more calls left unanswered when its month ends reads expired (worked out when read; nothing is written). Nothing to do
00078_case_held	Adds the function sp_case_held(case): whether a case is a sanctions case, is critical, is about a held subject or holds a held alert. The case list and the case page read it, so a stored AI suggestion of likely false positive no longer stands on such a case. It reads only the caller’s own workspace and needs no grant of its own. Nothing to do. A workspace’s own AI key is now deleted when the workspace switches to the installation’s key; 00079 clears keys left by builds that kept them
00077_subject_held	Adds the function sp_subject_held(type, id): whether a pep, pep_rca or sanctioned designation is in force on a subject or, for an account, on the customer who owns it. Alerts now carry held in the API, bulk closing refuses held alerts, and the AI assistant’s checks on alerts and cases use it. It reads only the caller’s own workspace and needs no grant of its own. Nothing to do. From this version, an alert about a relative or close associate of a PEP, or about a PEP’s account, can no longer be closed in bulk
00076_ai_requests_expire	Replaces the function sp_ai_request_more: when a workspace asks for more AI calls, a request of its own still waiting from an earlier month is closed as declined (Not answered in the month it was asked for.), so it no longer blocks new requests for ever. The function keeps its grants. Nothing to do
00075_ai_batches	Batches of questions to the AI assistant, and its suggestion on a case: adds ai_jobs (with row-level security; one row per alert or case asked about in a batch, deleted a week after it finishes) and, on case_ai_summaries, suggestion, confidence and suggestion_reasons (empty on summaries written before). Nothing to do. The worker role runs the batches
00074_ai_allowance_requests	Asking for more AI calls: adds provider_ai_requests and provider_ai_topups (the provider’s records, with no link to the workspace’s row so they outlive it) and the functions sp_ai_topup_calls, sp_ai_latest_request and sp_ai_request_more, through which a workspace reaches its own rows. migrate up applies the grants; if you manage grants yourself, run deploy/sql/grants.sql again (Helm does it). Without them, wherever a monthly allowance applies (a plan’s number or SP_AI_MONTHLY_CALLS above 0), the AI assistant’s settings page and every call on the installation’s key fail with permission denied for function sp_ai_topup_calls, with or without a provider console
00073_ai_usage_mirror_delta	Replaces the trigger that keeps provider_ai_usage in step: it now adds each call instead of copying the workspace’s month total, so a workspace moved in from another region no longer brings the source region’s calls into this region’s copy. Figures already copied are not corrected. Nothing to do
00072_ai_providers	Other model providers for a workspace’s own key: adds ai_settings.provider (anthropic for every existing workspace) and ai_settings.base_url. Nothing changes for existing workspaces. See SP_AI_PROVIDER under Configuration
00071_session_passkey	Adds sessions.passkey_id, the passkey that opened a session, so removing a passkey ends the sessions it opened. Sessions opened before the upgrade carry none and are not ended when their passkey is removed later; they end at sign-out or expiry
00070_ai_plan_usage	The AI assistant in the plan: adds provider_workspaces.limit_monthly_ai_calls (empty for every workspace, so the installation’s default keeps applying), the function sp_ai_call_limit(), and provider_ai_usage, the provider’s copy of AI usage, kept in step by a trigger on ai_usage and filled from the usage already counted. The copy is not deleted with a workspace. migrate up applies the grants; if you manage grants yourself, run deploy/sql/grants.sql again (Helm does it): they let the application role call sp_ai_call_limit() and revoke the copy from it. Without them the AI assistant’s settings page and every call to the assistant fail with permission denied for function sp_ai_call_limit
00069_report_ai_drafts_cleanup	AI narrative drafts become working material: adds a trigger that deletes a report’s report_ai_drafts rows when the report leaves draft, and deletes the drafts of every report that has already left draft. Nothing to do. The audit entries (ai.narrative_draft) stay
00068_rule_ai_suggestions	AI tuning suggestions for rules: adds rule_ai_suggestions (with row-level security; rows go with their rule, and what was sent and answered is encrypted). Nothing to do; nothing is written unless a workspace has the assistant on and a rule author asks
00067_report_ai_drafts	AI narrative drafts: adds report_ai_drafts (with row-level security; rows go with their report, the draft text encrypted). Nothing to do; nothing is written unless a workspace has the assistant on and a person asks for a draft
00066_case_ai_summaries	AI case summaries: adds case_ai_summaries (with row-level security; rows go with their case). Nothing to do; nothing is written unless a workspace has the assistant on and a person asks for a summary
00065_ai_settings_region	Adds ai_settings.region, the region the AI assistant’s settings were saved in, filled from each workspace’s region so nothing switches off on upgrade. From now on a workspace moved to another region arrives with the assistant off until it is switched on again there. Nothing to do; no effect on an installation with one region
00064_ai_assistant	The AI assistant: adds ai_settings, alert_ai_reviews and ai_usage, each with row-level security. The assistant is off for every workspace after the upgrade and nothing is sent anywhere until a workspace switches it on. Nothing to do; set SP_AI_API_KEY only if you want to offer your installation’s key to workspaces
00063_recovery_codes_passkeys	Recovery codes and passkeys: adds user_recovery_codes and user_passkeys (both with row-level security), lets sessions.auth_method be passkey, and adds the owner function sp_auth_find_passkey, whose grant migrate up applies. Nothing to do. People who already set up an authenticator app have no recovery codes until they make a set under Settings → Sign-in; tell them to. Passkeys need the installation’s public https address to be set (SP_SSO_PUBLIC_URL or SP_NOTIFY_PUBLIC_URL)
00062_privacy_case_evidence	Data protection reaches case notes and evidence files: adds the owner functions sp_privacy_scrub_cases (clears the closed cases of an erased customer; app and system roles) and sp_privacy_trim_case_files (removes files filed with no report from closed cases past retention; system role), and lets that path alone rewrite a timeline entry’s content. migrate up applies the grants; if you manage grants yourself, run deploy/sql/grants.sql again. Nothing is cleared at upgrade time: it happens at the next erasure or purge run
00061_evidence_files	Evidence files on cases: case_attachments gains the encrypted content and the removal record, a new table report_files (which of a case’s files go with a report, with row-level security and a trigger that freezes the choice once the report leaves draft), and the timeline type attachment_removed. Nothing to do. Files are stored in the database, so plan for it to grow: up to 10 MB a file and 50 files a case, and your database backups now hold them
00060_case_escalation	Escalation policies and team queues: adds escalation_policies.start_level and auto_escalate, cases.sla_breached_at, two indexes on cases, and the timeline type team_assigned. No policy exists after the upgrade, so cases open and escalate as before. The worker now records missed deadlines, but only those that pass after the upgrade: the migration marks every open case already past its deadline as recorded, so old misses get no timeline entry, notification or webhook. Those cases show the Deadline missed marker until their deadline next changes. Nothing to do
00057_provider_console_gaps	Adds provider_support_refusals (refused requests of a support session, counted per minute), a trigger on users that refuses one more person than a workspace’s plan allows (no effect where no limit is recorded), provider_workspace_tombstones, and the owner functions sp_provider_purge_check and sp_provider_purge_workspace. Run deploy/sql/grants.sql again
00056_installation_tables_rls	Closes the installation’s own tables (license_installation, licenses, license_usage_daily, backtest_runners, notify_channel_config) to the app role, switches row-level security on for every monthly partition, and makes sp_ensure_monthly_partitions run as the owner so the worker can create partitions as speculapro_system. Before upgrading, set SP_DB_SYSTEM_URL for every process, api and ingest included. After migrating, run deploy/sql/grants.sql (Helm does it; with Docker Compose or by hand, do it yourself): until then the worker can’t create partitions (permission denied for function sp_ensure_monthly_partitions). If your installation ran with separate database roles for more than three months before this migration, check for rows in the _default partitions: see Partitions
00055_support_access	Support access: provider_support_requests and provider_support_sessions, the function both audit logs are written through, and audit_log.actor_label with the actor types provider and support. Nothing changes for existing workspaces
00054_provider_console	The provider console’s tables (provider_staff, provider_sessions, provider_audit_log, provider_workspaces, provider_change_requests, provider_notices), closed to the application role, and license_usage_daily.decisions. Nothing is served until SP_PROVIDER_CONSOLE_ENABLED is set. Run deploy/sql/grants.sql again
00053_notification_email_details	Adds notifications.locale and notifications.params for the email layout. Nothing to do. Emails already queued go out with the same subject, body and button, without the details block
00052_model_schedules	Risk-model retraining schedules: adds risk_model_schedules (one retraining schedule per workspace, with row-level security) and risk_models.scheduled. Nothing changes for existing workspaces: every schedule starts off. See Risk models for operators
00051_scim_privileged_api_key_rotation	Adds sso_configs.scim_allow_privileged_roles (off for every workspace, so after the upgrade SCIM no longer gives or removes a privileged role until the workspace allows it. If you map identity-provider groups to Administrator, MLRO or approver roles over SCIM, those mappings stop applying after the upgrade: switch Let SCIM give privileged roles on, or give the roles under People. See Privileged roles), and on api_keys the columns rotation_due_at, rotation_due_user_id and rotated_from with two indexes. No existing key is marked: keys are marked only when their creator is deactivated after the upgrade
00050_backtest_scenarios	Backtests of scheduled rules: backtests.mode also accepts scheduled, with a new schedule column (the schedule whose runs a backtest simulates; required for that mode and empty for the others), and backtest_hits gains run_at and evidence (the simulated run a sampled hit belongs to, and its contributing transactions). No data changes. Rolling it back deletes the backtests of scheduled rules. See Backtesting a scheduled rule
00049_scim_slo	SCIM provisioning and SAML single logout: adds scim_tokens, scim_groups and scim_group_members; users.scim_external_id and users.scim_deactivated_at; on sessions, the SAML NameID and SessionIndex a session was given at sign-in; and the lookup function sp_scim_find_token. Run deploy/sql/grants.sql after it (the Helm chart does), or every SCIM request fails with permission denied. No data changes. Sessions open during the upgrade aren’t reached by single logout until the person signs in again
00048_ctr_thresholds_kept	Writes down the CTR thresholds, comparison and aggregation of workspaces that never picked a jurisdiction, keep their books in a currency other than their country’s, have CTR drafting on and never saved thresholds: the figures they were reading from their country’s definition, unchanged, so drafting doesn’t stop when such a workspace starts following Other country. No other workspace is touched. Each workspace touched is marked with the currency the figures were written for (thresholds_kept_from), which raises the warning ctr_threshold_currency_mismatch until one of its thresholds is changed. The figures are in the wrong currency and should be corrected: see Workspaces that never picked a jurisdiction
00047_cells	Multi-region hosting: adds tenants.region (the region a workspace is pinned to; empty on a single installation) and tenants.move_state, and the table workspace_moves (one row per move between regions, per side). No data changes. A single installation sets nothing and behaves as before. The global directory has its own database and migrations (speculapro directory migrate up), which a cell never runs
00045_models	Risk models: adds risk_models (the registry and training queue, with each stored model) and risk_model_stats (what each shadow or live model scored per day, as histograms), a model column on rule_hits and decisions for the live model’s score, two triggers that refuse a live model without an approver other than its proposer, and the permissions models.view (MLRO, Rule author, Rule approver, Auditor, Administrator), models.manage (Rule author) and models.approve (MLRO, Rule approver) on the built-in roles. Nothing is trained and no workspace is scored until someone moves a model to shadow. Custom roles are not changed
00041_report_attachments	Adds reports.attachments, the titles of the documents filed with a report (at most 50; titles only, never the files), and extends the report guard so the list is frozen with the rest of a report’s content once it leaves draft. Existing reports get an empty list. No data changes
00040_analytics_erasure_blank	Adds blank to privacy_analytics_ops and analytics_privacy_guards and replaces the guard trigger: when a customer is erased on request with their own records, their transactions in the optional analytics store lose the IP address, location, device and attributes too, including ones PostgreSQL had not received yet. No data changes
00039_screening_review	Screening review: adds assignee_id, assigned_at and case_id to screening_hits, the table screening_hit_events (a match’s timeline), and the permissions screening.review (Analyst, Senior analyst, L2 investigator, MLRO) and screening.accept_removal (MLRO, Administrator) on the built-in roles. Custom roles are not changed: add screening.review to yours if they should review matches
00038_analytics_privacy_guards	Data protection and the optional analytics store: adds the table analytics_privacy_guards and a trigger on privacy_analytics_ops that fills it. It keeps for good what each purge, erasure and pseudonymisation removed or rewrote among the rows the analytics writer writes (transactions, decisions, and customer and account references), so a writer that is behind on Kafka can’t write them back. The operations still in the queue are copied in. No other data changes; installations without the store carry an unused table
00037_p4_review_fixes_2	Change data capture: adds cdc_state.lease_owner and lease_until; one connector per Postgres replication source is now a lease row instead of a session lock, so it holds behind PgBouncer. Data protection: adds the table privacy_analytics_ops, the queue through which the purge, erasures and pseudonymisation reach the optional analytics store, and makes the case-deletion function queue what it deletes. No data changes; installations without the store discard the queue
00036_analytics_deletions	Adds backtests.history_source (which database a run read; empty for runs before this) and the table analytics_deletions, where PostgreSQL records deletions the optional analytics store must follow. No data changes; safe whether or not you use the store. With SP_CLICKHOUSE_URL set, the same migrate up applies clickhouse/0003_coverage.sql, which starts tracking how complete each workspace’s copy is. If you already use the analytics store, run speculapro analytics backfill once after upgrading: until then every workspace with history shows not copied and its long reads use PostgreSQL. With row policies, this migrate up also grants the reader role INSERT and ALTER DELETE
00035_screening_fixes	Screening lists get an owner: the installation, or one workspace. Every list that exists before the upgrade stays an installation list, import:<name> lists included, and from now on only the operator updates those (speculapro screening import-list, or a signed bundle). Row-level security is switched on for list sources, versions and entries: the API’s database role reads the installation’s lists and the calling workspace’s own, and writes only the workspace’s own. Also adds: an operator’s download address per fetched list (url_override) that start-up never overwrites, and the fetch status older; closed_reason and closed_at on potential matches (matches already superseded or delisted get no_longer_matches or entry_delisted, and the time of their last change); provider failure and retry state per screened subject; counterparties.updated_at with a trigger and a new scan index; the table screening_bundle_uploads for signed bundles waiting for approval; and the retention category screening. See After upgrading to 00035
00034_p4_review_fixes	Data protection fixes: adds clock_paused_at, clock_pause_reason, clock_paused_seconds and erasure_held to privacy_requests (stopping the clock on UK requests, and erasures that are due but postponed by a hold), and replaces the purge’s case-deletion function so a legal hold on a customer also keeps closed cases about their accounts and about counterparties that are those accounts. No data changes Jurisdictions and reporting (no schema change, same release): the packs global-fatf, ng-psp, ng-bank and uemoa-emi move to 1.1.0, so workspaces that installed them see Update to v1.1.0 and the changed rules wait for approval (upgrade them: uemoa-emi 1.0.0 applied the November 2026 e-money caps early). A Nigerian workspace’s CTR drafts now report cash above the threshold only, unless you choose otherwise under Settings → Reporting. A workspace following Other country that never saved CTR thresholds no longer inherits GHS 50,000: it has none until you set them. Thresholds you saved are kept
00033_analytics	Adds the index alerts_updated on alerts (tenant_id, updated_at, id), which the optional analytics role uses to copy changed alerts to ClickHouse. No data changes; safe whether or not you use the analytics store. ClickHouse’s own schema (clickhouse/0001_init.sql, 0002_rollups.sql) is applied by the same migrate up only when SP_CLICKHOUSE_URL is set
00032_screening	Sanctions and PEP screening: installation-wide list sources, versions and entries; per-workspace settings, screened subjects, potential matches, re-screens and provider configurations; the screening.view, screening.manage and screening.approve permissions added to the built-in roles; two indexes on customers and counterparties for the screening scan. After the upgrade the worker fetches the default lists and screens every workspace once
00031_jurisdictions	Adds tenants.jurisdiction (empty keeps following the workspace’s country), the installation-wide jurisdiction_bundles table, and relaxes customers.national_id_type to any well-formed key (bvn, nin, national_id…). Quick. See Jurisdictions
00030_privacy	Adds data protection: customers.relationship_ended_at (filled for customers already exited, kept up to date by a trigger) and pseudonymised_at; the privacy settings, retention policies, legal holds (never deleted; only their release can be recorded), purge runs, data subject requests, processing activities, lawful bases, transfers, breaches with their notifications and tasks (breaches never deleted), all under row-level security; two owner-only functions the purge uses to delete closed cases past their period and to drop whole monthly partitions, each re-checking holds and reports itself; privacy.view for the MLRO and Auditor; and the built-in Data protection officer role in every workspace (skipped where a custom role already has the key dpo). Quick: new, empty tables, one new column filled from the customer status. Nothing is purged after the upgrade: every workspace’s purge starts switched off until its DPO runs a dry run and switches it on. The numbers 00027 to 00029 are reserved for other features, so take releases in order. See Data protection
00029_cdc	Adds change data capture: the cdc and postgres_cdc data source kinds and their formats, and the tables cdc_mappings (mapping versions), cdc_state (positions, slot and status), cdc_applied (the ledger of applied changes) and cdc_topics (which source owns each Debezium topic), all under row-level security, with a guard so a topic is read by one source only. Quick: new empty tables and relaxed checks. Nothing is read until an administrator adds a source and saves its mapping, and a connector role runs. See Change data capture
00026_approvals_gaps	Lets a change request that creates something (an invitation, API key, webhook, data source, custom role or SSO domain claim) carry a target key, and allows only one waiting request per key, so two identical proposals can’t wait side by side. Quick: one new column and one index on the change-requests table. Requests already waiting when you upgrade that change list values, a webhook endpoint, or single sign-on settings with a stored client secret close as out of date when someone approves them; their makers propose them again. Waiting workspace-settings requests still apply
00025_whatsapp_fixes	Adds whatsapp_code_numbers, an installation-wide count of WhatsApp verification codes sent to each number (a keyed hash of the number, made with the installation’s blind-index key, never the number itself; the worker deletes entries older than 2 days), for the limit of 3 codes a day per number, and an index for the per-workspace daily limit. Quick: one empty table and an index. See WhatsApp through GOWA
00024_phase3_fixes	Fixes from the Phase 3 review. Roles: adds rules.packs_downgrade to the MLRO, and removes reports.draft from the built-in Analyst (who couldn’t use it without entities.view_pii); custom roles aren’t changed. Reports: a voided status with who, when and why; a copy of the reporting settings kept on each report at approval; a table of transactions someone cleared from a CTR draft, so the hourly check doesn’t add them back; and the controlled exception used by speculapro tenant offboard. Data sources: a count of interrupted loads per file, a record of files the connector moved, and a queue for connection tests run by the connector. Backtests and scheduled rules: a record of each backtest worker’s limits, and a back-off table for scheduled rule versions that keep failing. Quick: new columns, small tables and triggers. Once a report has been voided, this migration can’t be rolled back
00023_whatsapp	Adds WhatsApp notifications: on users, a whatsapp number (E.164), when it was confirmed and whether WhatsApp messages are on, with a check that refuses a number that hasn’t been confirmed; whatsapp as a notification channel; whatsapp_sends (verification codes as hashes, and test messages, used for rate limits; row-level security); and notify_channel_config, the installation’s last WhatsApp provider and GOWA address, so a change is audited. Quick: new nullable columns and small tables. Nothing is sent on WhatsApp until you set SP_NOTIFY_WHATSAPP_PROVIDER. See WhatsApp through GOWA
00022_change_requests	Adds maker-checker: the change_requests table (four-eyes, frozen-proposal and no-delete rules enforced by triggers, one pending request per target) and approval_policies, both under row-level security; a description on roles for custom roles, with name and key checks; the approval and read-only permissions on the built-in roles (Administrator: settings.approve, users.approve, sso.approve, api_keys.approve, webhooks.approve, datasources.approve, settings.view; MLRO: settings.approve, lists.approve, reports.view; Auditor: settings.view, reports.view); and the built-in L2 investigator role. Quick: two empty tables and small updates. Its number is lower than 00023_whatsapp, so releases carry both together: a database that had 00023 applied from a development build without 00022 would refuse it, which is why you upgrade from release to release. After it, approval defaults take effect in each workspace as soon as two people can approve them; see Approvals.
00021_license	Adds self-host licensing: the installation’s identity (its fingerprint), installed licences, daily usage counts and imported rule-pack bundles. Quick: the tables start empty. The 30-day evaluation period starts when this migration runs, so an existing installation without a licence has 30 days to install one before new workspaces, new rule versions and pack installs are paused. Monitoring is never affected. See Licensing
00020_datasources	Adds data_sources (credentials encrypted with the workspace key), data_source_files (each file claimed once by name, size and SHA-256) and data_source_results, all under row-level security, with a global unique index so each Kafka topic belongs to one workspace. Gives datasources.manage to the built-in Administrator role. Quick: three empty tables. Nothing is collected until an administrator adds a data source and a connector role runs. See Data sources
00019_scenarios	Creates scenario_runs (with row-level security) and a partial index on enabled scheduled rules. Quick, no table rewrite. New rules and pack versions don’t reach a workspace until someone selects Update to v… under Rule packs, which drafts versions of changed rules for approval. See Installing a pack
00018_sso	Adds the single sign-on tables (connection, role mappings, claimed domains, linked identities, break-glass administrators), all under row-level security; a sessions.auth_method column (password for existing sessions); a pre-login lookup function; and the sso.manage permission on the built-in Administrator role. Quick: small new tables and a column with a constant default. Nothing changes for users until an administrator sets up SSO. Before turning it on, set SP_SSO_PUBLIC_URL (or SP_NOTIFY_PUBLIC_URL)
00017_backtests	Adds the backtests queue and results table and backtest_hits (the sample of hits), both under row-level security, and gives rules.backtest to the built-in Rule author, Rule approver and MLRO roles. Custom roles aren’t changed. Quick: it creates two empty tables
00016_reports	Turns the reports table into the report register: four-eyes, lifecycle, frozen-content and no-delete triggers, the report_transactions and report_exports tables (row-level security, append-only), and a one-CTR-per-customer-per-day index. Gives reports.draft to the Analyst, Senior analyst and MLRO system roles, and reports.approve and reports.submit to the MLRO, where missing. Quick: it alters only the reports table, which is empty until reporting is used. After it, a workspace that holds reports can only be removed with speculapro tenant offboard (from 00024)
00015_query_indexes	Adds ten indexes that speed up the Command center, transaction search, decision details, rule hit counts and the notifier. No data changes. On the partitioned tables (transactions, rule_hits, decisions) Postgres builds the index on each monthly partition in turn and blocks writes to that partition while it builds, so on a large installation incoming data waits. Run it in a quiet window, or build the indexes in advance without blocking: see Before 00015 on a large installation
00014_pipeline_fixes	Adds transactions.session_id (nullable, no default), so the session a transaction came from is kept. Adding the column is quick and doesn’t rewrite the table. Transactions received before the upgrade have no session
00013_realtime	Adds the tables for real-time decisions, outcome feedback, account events, customers’ devices and webhooks. Existing decision records gain empty fields. The built-in Administrator role gains the new webhooks.manage permission, so administrators see the new Webhooks tab. Nothing else changes for existing data. Also run speculapro topics, which creates the new account.events topic and its dead-letter twin. The new decision role and the Prometheus metrics listener on :9090 start with SP_ROLES=all: check that port is free, or set SP_METRICS_ADDR
00012_controls	Temporary passwords now expire. Every temporary password not yet replaced is given 72 hours from the upgrade. People who were invited but never signed in are shown as Invited. Tell anyone waiting to sign in for the first time, or issue them a new temporary password later

Behaviour changes to check before upgrading

These come with the same releases as 00022–00026 and change what an existing installation accepts. Check each against your configuration first, since some stop SpeculaPro from starting.

Change	Who it affects	What to do
Signed-in requests that change something need an Origin header.	Scripts that sign in and reuse the session cookie. API keys are unaffected	Send Origin (the address of your SpeculaPro) on every POST, PUT, PATCH and DELETE, or get 403. See Authentication
Carrier-grade NAT addresses (100.64.0.0/10) are refused even with allow-private. The *_ALLOW_PRIVATE settings for webhooks, data sources, single sign-on and GOWA now open only RFC 1918 networks, IPv6 unique-local and loopback. Link-local, cloud metadata and 0.0.0.0/8 addresses are always refused	Anyone whose webhook receivers, SFTP servers, identity provider or GOWA server are reached over Tailscale or another overlay network that uses 100.64.0.0/10	Give the server an address in a private range, or a public https address, before upgrading. Otherwise deliveries, file drops, SSO sign-ins or WhatsApp messages fail with destination not allowed
The log provider is refused for SMS and WhatsApp outside development. With any SP_ENV other than development (staging included), SpeculaPro won’t start if SP_NOTIFY_SMS_PROVIDER or SP_NOTIFY_WHATSAPP_PROVIDER is log	Installations that used log as a placeholder	Set a real provider, or none
GOWA needs a user name and password outside development.	Installations with SP_NOTIFY_WHATSAPP_PROVIDER=gowa and no SP_NOTIFY_GOWA_USERNAME	Turn on GOWA’s APP_BASIC_AUTH and set both settings. On Helm, set notify.gowa.username
More changes need a second person once two can approve them. Switching off the workspace’s two-factor requirement, claiming or removing an SSO email domain, and importing a signed rule-pack file from Rules → Rule packs now follow the approval policy; giving someone a role that shows personal data, the audit log or reports, or that flags customers (with the built-in roles: Senior analyst, L2 investigator, Auditor) now needs approval wherever role grants do	Workspaces with two or more approvers	Tell administrators that these changes now ask for a reason and wait for Approvals
PUT /api/v1/me/notifications changes only the fields it carries. A field left out keeps its value, and an empty phone removes the number	Scripts that relied on a missing phone removing the number	Send "phone": "" to remove it

Before 00015 on a large installation

On a small installation, migrate up builds these indexes in seconds and you can skip this. On a large one, building them can block incoming transactions, rule hits and decisions for minutes. To avoid that, build them yourself before the upgrade, while SpeculaPro keeps running, then upgrade as usual. Every statement uses the same index name as the migration and IF NOT EXISTS, so migrate up then finds the indexes already there and does nothing for them.

1. Connect with psql as speculapro_owner. Use psql itself: the script relies on its \gexec command, which runs each generated statement on its own, outside a transaction, as CREATE INDEX CONCURRENTLY requires.
2. Run the script below. It can take a long time on big partitions, but reads and writes carry on throughout.
3. Check that every index is valid (the query at the end returns no rows).
4. Upgrade as described in How to upgrade.

-- 1. The partitioned tables. First an empty index on each table itself, which takes no time.
CREATE INDEX IF NOT EXISTS transactions_recent ON ONLY transactions (tenant_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS rule_hits_transaction ON ONLY rule_hits (tenant_id, transaction_id);
CREATE INDEX IF NOT EXISTS rule_hits_rule_id ON ONLY rule_hits (tenant_id, rule_id, created_at DESC);
CREATE INDEX IF NOT EXISTS decisions_id ON ONLY decisions (id);

-- 2. Build each partition's index without blocking writes.
SELECT format('CREATE INDEX CONCURRENTLY IF NOT EXISTS %I ON %I %s',
              c.relname || '_' || x.suffix, c.relname, x.cols)
FROM (VALUES ('transactions', 'recent',      '(tenant_id, occurred_at DESC)'),
             ('rule_hits',    'transaction', '(tenant_id, transaction_id)'),
             ('rule_hits',    'rule_id',     '(tenant_id, rule_id, created_at DESC)'),
             ('decisions',    'id',          '(id)')) AS x (tbl, suffix, cols)
JOIN pg_inherits i ON i.inhparent = x.tbl::regclass
JOIN pg_class c ON c.oid = i.inhrelid
\gexec

-- 3. Attach them. Each table's index becomes valid when every partition has one.
SELECT format('ALTER INDEX %I ATTACH PARTITION %I', x.tbl || '_' || x.suffix, c.relname || '_' || x.suffix)
FROM (VALUES ('transactions', 'recent'), ('rule_hits', 'transaction'),
             ('rule_hits', 'rule_id'), ('decisions', 'id')) AS x (tbl, suffix)
JOIN pg_inherits i ON i.inhparent = x.tbl::regclass
JOIN pg_class c ON c.oid = i.inhrelid
\gexec

-- 4. The other tables.
CREATE INDEX CONCURRENTLY IF NOT EXISTS alerts_closed ON alerts (tenant_id, closed_at DESC) WHERE status = 'closed';
CREATE INDEX CONCURRENTLY IF NOT EXISTS alerts_first_hit ON alerts (tenant_id, first_hit_at DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS alerts_rule_keys ON alerts USING gin (rule_keys);
CREATE INDEX CONCURRENTLY IF NOT EXISTS risk_designations_subject_id ON risk_designations (tenant_id, subject_id, created_at DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS cases_sla_due ON cases (sla_due_at) WHERE status NOT IN ('closed', 'pending_approval');
CREATE INDEX CONCURRENTLY IF NOT EXISTS user_roles_role ON user_roles (role_id);

-- 5. Check: lists any index that isn't valid yet. No rows means you're done.
SELECT c.relname AS index, i.indisvalid AS valid
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid AND (c.relname LIKE 'transactions%recent' OR c.relname LIKE 'rule_hits%transaction'
   OR c.relname LIKE 'rule_hits%rule_id' OR c.relname LIKE 'decisions%id'
   OR c.relname IN ('alerts_closed', 'alerts_first_hit', 'alerts_rule_keys', 'risk_designations_subject_id',
                    'cases_sla_due', 'user_roles_role'));

For the partitioned tables, the script first creates an empty index on the table itself, which takes no time. It then builds each partition’s index without blocking writes, and attaches it. The table’s index becomes valid once every partition’s is attached. Partitions the worker creates later get the index automatically.

If a step fails or is interrupted

A CREATE INDEX CONCURRENTLY that fails leaves an invalid index behind with the same name, and running the script again then skips it because the name exists. Drop that index (DROP INDEX CONCURRENTLY <name>;) and run the script again. It is safe to run more than once.

After upgrading to 00035

Migration 00035_screening_fixes changes who may load a screening list. Check these once the new version is running:

1. Workspaces can no longer import the national lists. Before, a workspace on a self-hosted installation could import ng-nigsac, gh-domestic, uemoa-gel and import:<name> lists through the API, and the import changed the list for every workspace. Those are now the installation’s lists, and such an import is answered 403. A workspace that did this must either:

   • ask you to import the file, with speculapro screening import-list <key> <file>; or
   • import the names as a list of its own under a new key, import:<name>, and tick it under Settings → Screening.

   See Installation lists from a file and Importing a list.

2. Existing import:<name> lists are yours to maintain. They stay installation lists, visible to every workspace, and every workspace that ticked one still screens against it. A workspace that wants to keep updating such a list itself imports it again under a different key; the old key belongs to the installation.

3. Every counterparty is looked at once more. Counterparties are now found by when their record was written or changed, and existing rows get the time of the migration. At its next passes the worker goes through all of them, in batches of SP_SCREENING_BATCH_SIZE. A counterparty whose name, country and type are unchanged since it was last screened is skipped, so this costs reads, not provider calls. A counterparty that was never screened, because it first appeared in a late or back-dated transaction, is screened now and can raise a potential match.

4. Screening one customer or counterparty on demand now needs screening.manage. Analysts with screening.view keep screening a name against the lists; an integration that called POST /api/v1/screening/subjects/{type}/{id}, or POST /api/v1/screening/screen with "providers": true, as a screening.view user gets 403. See Roles.

5. Signed bundles sent through the API now wait for approval when the workspace’s approval policy covers screening (on by default). The command line is unchanged. See Signed list bundles: through the API.

6. Workspaces in Ghana, Nigeria and the UEMOA states that never saved a choice of lists now have their national list (gh-domestic, ng-nigsac, uemoa-gel) among their defaults. It screens nothing until you have imported it.

7. The screening settings are checked at start-up. An interval or fetch timeout below one second, a batch size outside 1 to 10000 or an invalid SP_SCREENING_SOURCE_URLS now stops the process. See What stops start-up.

8. Retention has a new category, Sanctions and PEP screening. It starts at the AML minimum of the workspace’s jurisdictions and deletes by default. Nothing is removed until a new dry run has been reviewed. Tell your data protection officer. See Retention and legal holds.

9. Paging through potential matches should send before_id with before. See Screening API.

Workspaces that never picked a jurisdiction

Two behaviours change when you upgrade, and migration 00048_ctr_thresholds_kept keeps cash transaction report drafting from stopping because of the second.

Sierra Leone, Liberia, The Gambia, Guinea and Kenya have definitions. A workspace whose country is one of them, whose base currency is that country’s, and that never picked a jurisdiction, now follows its country’s definition instead of Other country. Reporting values the workspace saved are kept; values it never saved take the country’s defaults, including the cash thresholds.

A country’s definition applies only in its own currency. A workspace that never picked a jurisdiction and keeps its books in another currency than its country’s (a US-dollar workspace in Liberia or Ghana, for example) now follows Other country. Before, it took the country’s thresholds and read them in its own currency. Other country sets no cash reporting threshold. Its default sanctions lists still follow the country.

So that drafting doesn’t stop silently, the migration finds every such workspace that has cash transaction report drafting on and never saved its thresholds, and writes the figures it was using (with the comparison and aggregation) into its settings, exactly as they were read. Nothing changes for it on upgrade, but the figures are amounts in the wrong currency and should be corrected. A workspace with drafting off gets nothing written: it has no threshold, and drafting can’t be switched on until someone enters both.

If drafting is ever on with no threshold, Settings → Reporting shows a red warning, GET /api/v1/settings/reporting carries warnings: ["ctr_no_threshold"], and the worker logs ctr: drafting is on but the workspace has no CTR threshold, so no CTR is drafted; … at ERROR for that workspace, at most every six hours.

A workspace whose thresholds the migration kept has a standing warning too, because a threshold is set and the first warning doesn’t apply: Settings → Reporting shows Check the CTR thresholds… in red, GET /api/v1/settings/reporting carries warnings: ["ctr_threshold_currency_mismatch"] with ctr.thresholds_kept_from, and the worker logs ctr: the workspace's CTR thresholds are another currency's figures read in its base currency, so almost no CTR is drafted; … at ERROR, at most every six hours. All three stop only when someone changes a threshold and saves; saving anything else under Settings → Reporting leaves them standing.

To list the workspaces the migration marked, as the database owner:

SELECT slug, country, base_currency, settings->'reporting'->'ctr'->>'thresholds_kept_from' AS kept_from
FROM tenants WHERE settings->'reporting'->'ctr' ? 'thresholds_kept_from';

Review Settings → Reporting after upgrading

Every threshold, deadline and FIU detail for the five newest countries is research still to verify with counsel and the regulator. In a workspace whose currency isn’t its country’s, replace the cash thresholds with the right amounts in its own currency: the ones kept by the migration are the country’s figures read in the wrong currency, and a workspace with drafting off has none. See Workspaces created before jurisdictions.

The retention purge on Helm installations

Before this fix, the Helm chart’s copy of deploy/sql/grants.sql left out two grants. The retention purge drops old partitions and deletes closed cases through two database functions that only the system role may call (sp_privacy_drop_partition and sp_privacy_delete_cases), and on an installation set up by the chart the system role had not been given them. A workspace that switched the purge on then had every run end as Failed, with permission denied for function sp_privacy_… in the worker’s log (privacy purge or privacy purge: drop partition). Nothing was deleted that shouldn’t have been; nothing was deleted at all.

Upgrading the chart fixes it without further steps: the migration Job runs before the new pods start and applies the grants again, now with these two, as long as migrations.enabled and migrations.grants.enabled are left at true. If you switched either off and apply deploy/sql/grants.sql yourself, run the current file as the owner role after the migrations. Installations prepared by hand or with Docker Compose from deploy/sql/grants.sql already had the grants.

After upgrading, check the last purge of each workspace that has it on, under Privacy → Retention & legal holds (Last purge): the next run starts within about six hours and works through the backlog in batches.

After an upgrade that changes name matching

After upgrading to the release that changes screening’s name matching, have each workspace re-screen everyone once (Settings → Screening → Re-screen everyone, or POST /api/v1/screening/rescreen). Matching no longer raises generic words (Wallet, Agent, Bank), a loosely spelled single word, or a three-letter name that is one letter short of another (Ama, Amar), but matches already in the queue close only when their subject is screened again. No migration does this. See After an upgrade that changes matching.

Rolling back

Do not use migrate down to roll back production

Down-migrations usually drop tables and lose data. To return to the old version, restore the backup from step 2 and start the old binary.

Related

• The image and its commands
• Verifying releases
• Signed rule-pack updates
• Licensing
• PostgreSQL: migrations