Analytics store (ClickHouse)

In short

PostgreSQL is SpeculaPro’s system of record, and it is all SpeculaPro needs. But questions that sweep over many months of transactions (a backtest with a long warm-up, a 90-day scheduled rule, a year of dashboard figures) get slow in PostgreSQL once an installation holds a few hundred million transactions.

For those, SpeculaPro can keep a second copy of its transactions, decisions, rule hits, alerts and cases in ClickHouse, a database built for exactly this kind of question. This copy is the analytics store.

Think of PostgreSQL as the ledger and the analytics store as a set of summary books copied from it: quicker to add up, never the legal record, and always possible to write out again from the ledger.

ingest / decision ──▶ Kafka: sp.txn.ingested ──▶ persist ──▶ PostgreSQL  (system of record)
                                   │                            │
                                   │                            │ rule hits, alerts, cases
                                   ▼                            ▼
                              analytics role ─────────────▶ ClickHouse   (derived copy)
                                                                │
                              api (Analytics page), worker (long backtests, long scheduled rules)

Five things to know before anything else:

• It is optional. Leave SP_CLICKHOUSE_URL empty and nothing on this page runs. Everything reads PostgreSQL, as it did before.
• You run ClickHouse. SpeculaPro connects to one; neither the Helm chart nor the program installs or manages it. The production Compose file can start a single-node one for you.
• It can always be rebuilt from PostgreSQL or from Kafka, so it doesn’t need the same care as your PostgreSQL backups.
• Nothing reads it while it is incomplete or behind. SpeculaPro tracks how complete each workspace’s copy is and how far the copy has got. A reader that can’t rely on the store reads PostgreSQL instead. See When a reader uses the store.
• The data protection purge reaches it. What the purge or an erasure request removes or pseudonymises in PostgreSQL is applied to the copy too, from a queue that survives ClickHouse being down. See Data protection.

What reads it

	Without the analytics store	With it
Analytics page	The last 3 months, from PostgreSQL	Up to 24 months, once the workspace’s history has been copied. The page says where it read from and, when it offers fewer than 24 months, why
Backtests	PostgreSQL	A run whose history, warm-up included, spans more than SP_CLICKHOUSE_BACKTEST_THRESHOLD (30 days) first checks that the store can be relied on, then reads its transactions from it. Otherwise it reads PostgreSQL and says why in its result. Shorter runs read PostgreSQL. Every run records which of the two it read
Scheduled rules	PostgreSQL	I2 (refund ratio) and I3 (distinct payers), when the window they read is at least SP_CLICKHOUSE_SCENARIO_THRESHOLD (30 days), the workspace’s copy is complete from the start of that window, and the writer has caught up. Every other scheduled rule stays on PostgreSQL
Everything else: alerts, cases, the Command center, real-time decisions, post-event rules, reports	PostgreSQL	PostgreSQL. Nothing on the decision path or the analysts’ daily pages reads ClickHouse

The project’s tests check that a backtest and the Analytics page give the same results from either database, and that I2 and I3 raise their findings when read from the analytics store. Whichever database answers, hits, alerts and results are written to PostgreSQL exactly as before.

When a reader uses the store

The store is a derived copy, so there are two situations in which it holds less than PostgreSQL:

• It is partial while your history is being copied for the first time, and during a rebuild.
• It trails PostgreSQL by the time the analytics role takes to write: normally a couple of seconds, longer when the role is stopped or behind.

SpeculaPro tracks both, and every reader checks them before it reads. A reader that can’t rely on the store reads PostgreSQL instead, so a partial or late copy never produces a short count.

How complete each workspace’s copy is

For each workspace the store records one of these states. speculapro analytics status shows them in its copy column.

State	Meaning
not copied	No copy from PostgreSQL has finished for this workspace
copy running or interrupted	A copy from PostgreSQL is running, or one started and didn’t finish
complete	The store holds the workspace’s whole history
complete from <date>	The store holds everything from that day on. This is what a backfill run with --from leaves

A workspace becomes complete when speculapro analytics backfill, or speculapro analytics rebuild --from postgres, finishes for it.

• A new workspace needs no backfill. A workspace that has no transactions at all when the analytics role first sees it is marked complete automatically: everything it ingests from then on reaches the store through Kafka.

• A workspace that already has history when ClickHouse is switched on stays not copied until the backfill has run. The analytics role logs this once per workspace:

  analytics: this workspace's history was never copied to the store; its long reads use Postgres until `speculapro analytics backfill` has run

• Emptying the store empties these states too. After a rebuild, no workspace is complete until it has been copied again.

How far the writer has got

The analytics role records how far it has read Kafka, for each partition of txn.ingested:

• for a busy partition, the time of the last record it committed;
• for a partition with nothing left to read, the time of the check, which it makes every 2 seconds.

The store has caught up to a given instant when every partition has passed it. This doesn’t depend on a particular workspace having traffic: a quiet workspace is served like any other.

The rule every reader follows

A reader uses ClickHouse only when all three of these hold:

1. the workspace’s copy is complete from the start of the period the reader needs;
2. no copy from PostgreSQL is running for the workspace;
3. the writer has caught up.

Otherwise it reads PostgreSQL. What “caught up” means depends on the reader:

Reader	How current the store must be
Backtests, and the scheduled rules I2 and I3	Caught up to the moment the run starts. If the writer is within 30 seconds of that moment, the run waits up to 10 seconds for it, so the normal 2-second writing delay doesn’t send a run to PostgreSQL
Analytics page	Up to 15 minutes behind is accepted: its figures are monthly totals

Two consequences are worth spelling out:

• During a first backfill or a rebuild, nothing reads the partial store. You don’t need to remove SP_CLICKHOUSE_URL from any process or hold back long backtests: they read PostgreSQL until the copy has finished, and a backtest’s result says so.
• A store that holds only recent history is still used for what it holds. If a workspace is complete from a date (a backfill run with --from), a backtest or scheduled rule whose period starts on or after that date reads the store, and one that starts earlier reads PostgreSQL. The Analytics page reads the store for the whole months it holds when that is more than 3 months: for example it offers 6 months and greys out 12 and 24. With 3 months or fewer it reads PostgreSQL.

To see where you stand, run speculapro analytics status, and watch the gauge speculapro_analytics_incomplete_workspaces (see Monitoring).

What it holds

Table	Filled from	Holds
transactions	Kafka (txn.ingested), or PostgreSQL during a backfill	One row per transaction: times, direction, type, channel, status, amounts, currencies, countries, merchant code, IP address, location, device fingerprint, counterparty fingerprint, your customer and account references, and the custom attributes you sent
decisions	The decision carried on the same Kafka record	Outcome, score, whether it was degraded, latency, and the rules that fired
rule_hits	PostgreSQL	Each hit with its rule’s key, name and category at the time it was copied
alerts, cases	PostgreSQL	Status, severity or priority, disposition and dates. No comments, timeline or evidence
txn_hourly	Computed from transactions	Quarter-hour totals by channel, type and status, which the Analytics page’s volume panel reads. (The table keeps the name txn_hourly.) Every time zone’s offset is a multiple of fifteen minutes, so calendar months are exact in all of them
tenant_state, writer_progress, tenants_seen	SpeculaPro	Housekeeping for what a reader may rely on: how complete each workspace’s copy is, how far the writer has read each Kafka partition, and which workspaces the store has ever received rows for. Workspace IDs, times and counters only
sync_state, schema_migrations	SpeculaPro	Housekeeping: where the copy from PostgreSQL got to, and which schema versions are applied

What it doesn’t hold: names, dates of birth, ID numbers, phone numbers, emails, payment references, descriptions, case notes or reports. Customers and accounts appear only as the references your systems sent (external_id), and counterparties as a keyed fingerprint.

It is still personal data

Customer references, IP addresses, locations, device fingerprints and custom attributes relate to identifiable people. Protect the ClickHouse server, its disks and its backups as you protect PostgreSQL: network access limited to SpeculaPro, TLS, encrypted storage, and a named owner. SpeculaPro’s field-level encryption covers PostgreSQL columns; it does not encrypt anything in ClickHouse.

Before you start

You need	Notes
A ClickHouse server	SpeculaPro’s Compose files use clickhouse/clickhouse-server:25.8, the version the project is tested against. Run it in your cluster, on its own machines, or as a managed service
The native protocol	Port 9000, or 9440 with TLS. SpeculaPro doesn’t use ClickHouse’s HTTP port
A user for SpeculaPro	It needs to create a database and tables (during migrate), insert, select, and run ALTER … DELETE (deletions) and TRUNCATE (a rebuild). On Kubernetes you can give the migration its own, more privileged user. With row policies the everyday user carries only SELECT, INSERT and ALTER DELETE, and migrate and analytics rebuild run as the migration user
Disk	The copy is compressed and much smaller than the same rows in PostgreSQL, but it grows with your history. Rows leave it when the data protection purge removes them from PostgreSQL: see Data protection
custom_settings_prefixes left at SQL_	Only if you use row policies. It is ClickHouse’s default; the Compose file sets it explicitly
Kafka partitions	The analytics role is a consumer group of its own. Its consumers in total shouldn’t exceed the partition count of txn.ingested

How to set it up

The steps are the same everywhere: point SpeculaPro at ClickHouse, create the schema, start the analytics role, copy your existing history.

With Docker Compose

The production Compose file can run a single-node ClickHouse beside the other services.

1. In .env, add analytics to the profiles and set the address and a password:

   COMPOSE_PROFILES=all,analytics
   SP_CLICKHOUSE_URL=clickhouse://clickhouse:9000
   SP_CLICKHOUSE_PASSWORD=<a long random password>
   CLICKHOUSE_MEMORY=4G

   With the split layout, also add the role to the background container:

   SP_BACKGROUND_ROLES=stream,persist,worker,connector,analytics

   With all, there is nothing to add: --role=all includes analytics whenever SP_CLICKHOUSE_URL is set.

2. Start ClickHouse and create the schema:

   docker compose up -d clickhouse
   docker compose run --rm cli migrate up

   clickhouse: applied 0001_init.sql
   clickhouse: applied 0002_rollups.sql
   clickhouse: applied 0003_coverage.sql

3. Restart SpeculaPro so every container picks up the new settings:

   docker compose up -d

4. Copy the history you already have, then check that every workspace’s copy is complete:

   docker compose run --rm cli analytics backfill
   docker compose run --rm cli analytics status

To use a ClickHouse that runs somewhere else, leave analytics out of COMPOSE_PROFILES and set SP_CLICKHOUSE_URL, SP_CLICKHOUSE_USER and SP_CLICKHOUSE_PASSWORD to point at it. Put any other SP_CLICKHOUSE_* setting, such as TLS, in speculapro.env.

With Helm

The chart never installs ClickHouse. deploy/helm/speculapro/examples/ has two starting points: clickhouse-altinity.yaml (the Altinity operator) and clickhouse-bitnami-values.yaml (a single-node community chart). Size them for your volume; they are examples, not tuned clusters.

1. Put the password in a Secret, under the key SP_CLICKHOUSE_PASSWORD (and SP_CLICKHOUSE_MIGRATION_PASSWORD if the migration uses its own user).

2. Add to your values:

   clickhouse:
     enabled: true
     url: clickhouse://clickhouse-speculapro.analytics.svc:9000
     user: speculapro
     existingSecret: speculapro-clickhouse   # default: secrets.existingSecret
   roles:
     analytics:
       enabled: true
   networkPolicy:
     egress:
       clickhouse:
         ports: [9000]
         to:
           - namespaceSelector: {matchLabels: {kubernetes.io/metadata.name: analytics}}

3. Run helm upgrade. The migration Job creates the ClickHouse schema straight after PostgreSQL’s, before the new pods start.

4. Copy existing history once, from a pod that has the system database connection, such as a worker pod (the Deployment is named after your release, here speculapro):

   kubectl exec deploy/speculapro-worker -- /usr/local/bin/speculapro analytics backfill

See Kubernetes with Helm for every clickhouse.* value.

Anywhere else

# 1. Point SpeculaPro at it, on every process
SP_CLICKHOUSE_URL=clickhouse://clickhouse.internal:9000
SP_CLICKHOUSE_USER=speculapro
SP_CLICKHOUSE_PASSWORD=...

# 2. Create the schema (after PostgreSQL's, in the same command)
speculapro migrate up

# 3. Run the analytics role somewhere: with all, or on its own
speculapro run --role=analytics

# 4. Copy existing history once
speculapro analytics backfill

Set SP_CLICKHOUSE_URL on every process, not only the analytics role

The api role reads the store for the Analytics page and the worker role reads it for backtests and scheduled rules. A process without the setting simply reads PostgreSQL.

Until the first backfill finishes, long reads stay on PostgreSQL

Setting SP_CLICKHOUSE_URL doesn’t move anything to a half-filled store. A workspace that already has history is not copied until the backfill has finished for it, and until then its long backtests, the scheduled rules I2 and I3 and the Analytics page read PostgreSQL, exactly as before. The Analytics page says so under its title, and a long backtest’s result says so under How this was computed. So run the backfill straight after migrate, at a quiet time; there is nothing to hold back meanwhile. A workspace created after the store was switched on needs no backfill. See When a reader uses the store.

The analytics role

The analytics role keeps the store current. It is separate from persist on purpose: it has its own Kafka consumer group, so a ClickHouse outage or a rebuild never slows writes to PostgreSQL.

It does three jobs:

1. Transactions and decisions, from Kafka. SP_CLICKHOUSE_WORKERS consumers (default 2) read txn.ingested in the group SP_CLICKHOUSE_GROUP. ClickHouse prefers a few large inserts to many small ones, so each consumer gathers up to SP_CLICKHOUSE_BATCH_MAX records (50,000), or whatever arrived within SP_CLICKHOUSE_FLUSH_INTERVAL (2 seconds), inserts them, refreshes the quarter-hour totals for the hours it touched, and only then tells Kafka the batch is done. It also reports how far it has read each partition, which is how readers know whether the store has caught up.
2. Rule hits, alerts and cases, from PostgreSQL. These aren’t on Kafka, so every SP_CLICKHOUSE_SYNC_INTERVAL (30 seconds) the role copies what changed in each workspace since its last pass. It re-reads the last SP_CLICKHOUSE_SYNC_OVERLAP (5 minutes) each time, so a row whose database transaction committed late isn’t missed.
3. Deletions, from PostgreSQL. At the start of each of those passes it applies the deletions PostgreSQL has recorded for the store, such as an offboarded workspace. See How deletions reach the store.

Copies are safe to repeat. Every table keeps one row per ID and, where a row can change (an alert being closed), the newest copy wins. Every read asks ClickHouse for the de-duplicated view. So a batch delivered twice after a restart, or a backfill run over live data, is never counted twice.

Records it can’t read are skipped and counted in speculapro_analytics_errors_total{stage="decode"}. The persister parks the same records on the dead-letter topic, so nothing is lost by skipping them here.

A decided transaction that reuses an external ID is an edge case the role handles on purpose. When a transaction that came through the real-time decision API carries an external ID that was already ingested, the persister keeps its decision and writes no second transaction row. The analytics store ends the same way, whichever of the persister and the analytics writer handles the event first: the persister records the declined transaction as a deletion, the writer skips its transaction row (the decision row is still written), and if the writer had already written it, the role removes it and corrects the volume totals. A copy from PostgreSQL gives the same result.

How deletions reach the store

Deletions follow PostgreSQL. PostgreSQL has a table, analytics_deletions. Whatever deletes rows in PostgreSQL that the copy also holds records that deletion there, in the same database transaction, so a deletion is recorded exactly when it happens. Today two things record one:

What	What is recorded
Offboarding a workspace	The whole workspace
The persister declining a duplicate decided transaction (above)	That transaction

What happens to a record:

1. It is applied at the start of the next copy pass, so within SP_CLICKHOUSE_SYNC_INTERVAL (30 seconds).
2. A failure is retried on every pass. The record keeps the number of attempts and the last error, the role logs analytics: deletion not applied; it will be retried, and speculapro_analytics_errors_total{stage="delete"} goes up. A ClickHouse outage delays a deletion; it never loses one.
3. It is applied once more about 10 minutes after it was recorded, then closed.
4. The writer checks these records before every insert. A replay of Kafka (a rebuild from Kafka, or a consumer that was behind) therefore never writes an offboarded workspace or a deleted row back.

If ClickHouse isn’t configured, the records simply wait in PostgreSQL, and are applied if you enable the store later. speculapro analytics status shows how many are waiting.

Starting it

How you run SpeculaPro	The role runs when
--role=all	SP_CLICKHOUSE_URL is set. You don’t name it
Roles listed one by one	You add analytics to the list
Helm, mode: split	roles.analytics.enabled: true (the chart refuses it without clickhouse.enabled)
Helm, mode: all	clickhouse.enabled: true

A process asked to run analytics without SP_CLICKHOUSE_URL stops at start-up with the analytics role needs SP_CLICKHOUSE_URL. Like the other background roles it needs SP_DB_SYSTEM_URL, because it reads every workspace.

How many

One process is enough for most installations; two give you resilience.

• Kafka work is shared. SP_CLICKHOUSE_WORKERS × the number of analytics processes shouldn’t exceed the partition count of txn.ingested (64 by default). Extra consumers sit idle.
• The PostgreSQL copy isn’t. It takes an advisory lock, so only one process copies at a time and the others skip their pass. More replicas add Kafka throughput and a standby, nothing else.

The first time the consumer group exists it starts from the oldest record still on the topic.

Maintenance commands

All four need SP_CLICKHOUSE_URL and a database connection that can read every workspace (SP_DB_SYSTEM_URL, or the owner connection the Compose cli service uses). Without the URL they stop with SP_CLICKHOUSE_URL is not set: this installation has no analytics store (everything reads Postgres).

How to see what the store holds

speculapro analytics status

writer: caught up to 2026-10-11T12:01:07Z (3s ago)
deletions: none waiting

workspace                              copy                                 transactions    decisions    rule_hits     alerts      cases
0199c4c2-6c1e-7a52-b2a8-0d6c1f8a41e7   complete                                  1204377        88213         4310        932        211
0199d1aa-93b0-7c11-8e55-2f7d0c9b6a10   not copied: run analytics backfill           48211         1022            0          0          0

not in Postgres any more (offboarded), still in the store:
0198f7e3-1d2c-7b9a-a1f4-6f0e5d4c3b2a   orphaned: analytics purge --tenant-id        90412         7001          310         44          9

How to read it:

Part	Meaning
writer:	How far the analytics role has read Kafka, across every partition. It reads writer: no position reported yet (the analytics role hasn't run since the store was created or emptied) when that is the case
deletions:	Deletions recorded in PostgreSQL that the store hasn’t finished applying. When some are waiting it reads deletions: N waiting to be applied (M failing; oldest recorded <time>): the analytics role applies them
The workspace lines	One per workspace that exists in PostgreSQL, by its ID, with the de-duplicated row count of each table. Compare the numbers with PostgreSQL to check a backfill or a rebuild
copy	How complete the copy is: complete, complete from YYYY-MM-DD, copy running or interrupted, or not copied: run analytics backfill
The last block	Copies of workspaces PostgreSQL no longer has. orphaned: analytics purge --tenant-id means nothing is recorded to remove it: delete it by its ID. deletion recorded, being applied means the analytics role is removing it. The block is left out when there is nothing to list

With row policies switched on, copies of offboarded workspaces that were written before this release are listed only when you run status as the migration user.

How to copy existing history (backfill)

The analytics role only sees what arrives after it starts, plus whatever Kafka still retains. A backfill copies everything else from PostgreSQL.

speculapro analytics backfill                                   # every workspace, all history
speculapro analytics backfill --workspace acme --from 2025-01-01 --to 2026-01-01

Option	Meaning
--workspace	Only this workspace, by its slug. Default: all
--from, --to	Transactions and decisions from this day up to, but not including, that day (UTC, YYYY-MM-DD). Rule hits, alerts and cases are always copied in full

With no options it copies every workspace from the beginning. What it does for each workspace:

1. Marks the copy as running. From this moment the workspace’s readers use PostgreSQL.
2. Copies transactions, decisions, rule hits, alerts and cases from PostgreSQL, in pages.
3. Removes what the store holds and PostgreSQL doesn’t: transactions and decisions that PostgreSQL has since purged or erased and a replay of Kafka brought back, or that PostgreSQL never kept. Only rows received more than an hour before the copy began are considered. Newer ones are left alone, because the persister may not have written them yet.
4. Recomputes the volume totals.
5. Marks the workspace complete: from the --from day when you gave one, otherwise from the beginning.

It prints its progress, and its last line lists the rows copied per table and how many were removed.

Things to know before you run one:

• It is safe to run while the analytics role is running, and to run twice. Rows already there are replaced by identical ones.
• One copy from PostgreSQL runs at a time across the installation. A second one stops with analytics: another copy from Postgres is running; try again when it has finished.
• If it fails or is interrupted, run it again. The workspace stays copy running or interrupted, and its readers stay on PostgreSQL, until a backfill completes for it.
• Run it when the persist role isn’t badly behind. Step 3 trusts PostgreSQL for anything received more than an hour ago, so with a persister that is hours behind, rows it hasn’t written to PostgreSQL yet could be removed from the store. Check consumer lag first.
• With --to in the past, the copy doesn’t change what the store is known to cover, and the command says so: –to is in the past: this copy doesn’t change what the store is known to cover. Use it to refill a range, not to make a workspace complete.
• It reads PostgreSQL in pages on the primary, so run a large one outside your busiest hours.

How to rebuild the store

A rebuild empties the analytics store and fills it again. Use it after restoring PostgreSQL from a backup, when the counts don’t match, or to remove data that PostgreSQL has since purged.

# From PostgreSQL: everything PostgreSQL holds today
speculapro analytics rebuild --from postgres --confirm rebuild

# From Kafka: replay the topic
# 1. stop every process running the analytics role
speculapro analytics rebuild --from kafka --confirm rebuild
# 2. start the analytics role again, and wait until status shows the writer caught up
speculapro analytics status
# 3. copy what Kafka no longer retains, and mark each workspace complete
speculapro analytics backfill

	--from postgres	--from kafka
What it does	Empties the store, then runs a full backfill	Empties the store and resets the role’s consumer group. When the role next starts it replays txn.ingested from the start, skipping what PostgreSQL has recorded as deleted, and copies rule hits, alerts and cases from PostgreSQL again
How far back	Everything in PostgreSQL	The replay reaches only as far as the topic’s retention. The backfill in step 3 copies the rest
Analytics role	Can keep running	Must be stopped first. Kafka won’t delete a group that has members; the command stops with reset consumer group … (stop the analytics role first)
When workspaces are complete again	One by one, as each is copied	Only when the backfill in step 3 has finished

Without --confirm rebuild the command refuses: this empties the analytics store first; add –confirm rebuild. Nothing in PostgreSQL is touched.

While a rebuild runs, readers use PostgreSQL

Emptying the store also empties what it knows about each workspace’s copy, so no workspace is complete until it has been copied again. Meanwhile the Analytics page covers 3 months and says why, and long backtests and long I2 / I3 runs read PostgreSQL. Nothing reads the half-filled store, so you don’t need to remove SP_CLICKHOUSE_URL from the workers. A rebuild still puts load on PostgreSQL and ClickHouse: choose a quiet time.

After a rebuild from Kafka, run the backfill

Kafka still holds every transaction within its retention period, including ones the data protection purge or an erasure has since removed from PostgreSQL. The writer checks PostgreSQL’s record of those purges and erasures before every insert, so the replay doesn’t write them back: purged rows are left out, and pseudonymised ones are written as PostgreSQL now holds them. See Data protection. The exception is anything purged before that record existed (before migration 00038): the replay writes those rows, nothing reads them because no workspace is complete until the backfill in step 3 has finished, and that backfill removes every transaction and decision PostgreSQL no longer has. Don’t skip step 3. Without it the store also stays unused. To avoid the question altogether, use --from postgres.

With row policies, rebuild as the migration user

Emptying the store needs ClickHouse’s TRUNCATE privilege, which the reader role doesn’t carry. Run analytics rebuild with SP_CLICKHOUSE_USER and SP_CLICKHOUSE_PASSWORD of the migration user. Otherwise the command fails with ClickHouse’s error followed by (emptying the store needs TRUNCATE, which the reader role doesn’t grant: run rebuild with SP_CLICKHOUSE_USER and SP_CLICKHOUSE_PASSWORD of the migration user).

How to delete one workspace’s copy

You rarely need this: offboarding removes a workspace’s copy by itself. Use it for a copy that analytics status lists as orphaned, or to throw away one workspace’s copy and copy it again.

speculapro analytics purge --workspace acme --confirm acme                 # a workspace that still exists
speculapro analytics purge --tenant-id 0198f7e3-1d2c-7b9a-a1f4-6f0e5d4c3b2a \
  --confirm 0198f7e3-1d2c-7b9a-a1f4-6f0e5d4c3b2a                           # by ID, also after offboarding

Give exactly one of --workspace and --tenant-id, and repeat the same value after --confirm. speculapro analytics status lists the IDs. The command deletes that workspace’s rows from every table and waits until ClickHouse has applied the deletion. PostgreSQL’s own data isn’t touched.

The workspace	What happens	The command prints
No longer exists in PostgreSQL (offboarded)	The copy is deleted and the deletion is recorded, so a replay of Kafka never writes it back	analytics copy of <id> deleted; its events on Kafka will not be written again
Still exists	The copy is deleted, nothing is recorded against the workspace, and its state becomes not copied. New transactions keep arriving from Kafka; its long reads use PostgreSQL until you copy it again	analytics copy of acme deleted. The workspace still exists: its long reads use Postgres until `speculapro analytics backfill --workspace acme` has copied it again

Purging by a slug that no longer exists fails with workspace "acme": … and a pointer to --tenant-id: a deleted workspace has no slug to look up, so use its ID.

Workspace isolation

ClickHouse has no equivalent of PostgreSQL’s row-level security tied to a connection, so SpeculaPro enforces workspace separation in two layers.

1. The query layer, always. Every read of workspace data is built by one piece of code. A query that names no workspace is refused before it is sent (analytics: query without a workspace refused). Its first condition is always the workspace, tables and columns are fixed names in the program rather than text, and every value is sent as a bound parameter, never pasted into the SQL.

2. Row policies, if you switch them on. Set SP_CLICKHOUSE_READER_ROLE to a role name, for example speculapro_reader. speculapro migrate up then creates that role in ClickHouse, grants it SELECT, INSERT and ALTER DELETE on the database, and puts a row policy on every workspace table: a row is visible only when its workspace matches a setting, SQL_speculapro_tenant, that the query layer sends with every read. A session that names no workspace gets an error, not rows.

   Those three privileges are what SpeculaPro needs while it runs: reading, the analytics role’s writes, and applying deletions. The role carries nothing that changes the schema or empties a table.

   Afterwards, once, give the role to the user SpeculaPro connects with. These two statements are all it takes; no other grant is needed:

   GRANT speculapro_reader TO speculapro;
   ALTER USER speculapro DEFAULT ROLE speculapro_reader;

   The user that runs migrate needs ClickHouse’s access management enabled to create roles and policies.

Row policies protect against a query that forgot its filter. They are not a boundary against a compromised SpeculaPro process, which chooses the setting itself. ClickHouse applies row policies to SELECT only: the analytics role writes rows exactly as Kafka and PostgreSQL hold them.

Everything is tested running as the restricted user

Every read names its workspace, including the statement with which the analytics role computes the volume totals. The analytics role, the backfill, deletions and every reader are tested running as a user whose only role is the reader role, and the row policies still hold for it. You don’t need a separate user for the analytics processes.

Data protection: how the purge reaches the copy

The retention purge, the erasure of a data subject request and pseudonymisation all happen in PostgreSQL first. Each one also writes down, in the same database transaction, exactly which rows it removed or rewrote. The worker role then applies that list to ClickHouse, about once a minute, and a second time about 10 minutes after the entry was recorded. Until that second pass an entry counts as waiting.

In PostgreSQL	In the analytics store
Transactions, decisions, rule hits, closed alerts and closed cases deleted by the purge	The same rows are deleted, by id. The volume totals for those hours are recomputed
A whole month dropped at once	That workspace’s rows of the month are deleted
Transactions pseudonymised (by the purge, or by an erasure request)	The IP address, location, device and custom attributes are cleared. Amounts, types, channels, countries and dates stay
A customer or account pseudonymised or erased	Their reference on the transactions up to that moment is replaced by the same pseudonym
A customer erased on request, together with their own records	Every transaction of that customer up to that moment also loses its IP address, location, device and custom attributes in the copy, including ones that reached the copy before PostgreSQL. Transactions a report covers keep them

Legal holds are honoured without ClickHouse knowing about them. Only what PostgreSQL actually removed is passed on, so a record that a legal hold, an open case or a report kept in PostgreSQL is never touched in the copy either.

Nothing comes back. The analytics role may be behind on Kafka when a purge or an erasure is applied, and would otherwise write the original rows into the store afterwards. So PostgreSQL also keeps, for good, what the writer must respect (the table analytics_privacy_guards, filled by a database trigger in the same transaction as the list above). Before every insert the writer checks it and does to its own rows exactly what the purge or erasure did:

What was done	What the writer does with a late row
A transaction or decision purged, by id or with its whole month	Doesn’t write it
A transaction pseudonymised	Writes it without the IP address, location, device and custom attributes
A customer or account erased or pseudonymised	Writes the pseudonym in place of their reference, on transactions dated before the erasure
A customer erased on request, together with their own records	Also writes those transactions without the IP address, location, device and custom attributes, even ones PostgreSQL had not received when the erasure ran. Transactions a report covers keep their details, as in PostgreSQL

The second pass after 10 minutes covers a writer that made its check just before the entry existed and inserted just after the first pass. analytics_privacy_guards is never pruned: it holds ids, time ranges, and the old and new references of erased customers and accounts, so treat it as part of the main database’s personal data. Rule hits, alerts and cases are not in it, because those are copied from PostgreSQL and never come from Kafka.

If ClickHouse is down, nothing is lost. The list waits in PostgreSQL (the table privacy_analytics_ops) and each entry is tried again, with a growing delay of up to an hour, until it succeeds. While entries are failing, the worker logs an error that says so plainly:

privacy: data purged or erased in Postgres is STILL IN THE ANALYTICS STORE: ClickHouse refused or couldn't be reached. Retrying.

To see what is waiting, as a database administrator:

SELECT target, action, count(*), min(created_at) AS oldest, max(last_error) AS last_error
FROM privacy_analytics_ops WHERE done_at IS NULL GROUP BY 1, 2;

An empty result means the copy is in step. An entry stays in this result for about 10 minutes after it was recorded even when all is well, because it waits for its second pass; look at last_error to tell a waiting entry from a failing one. Applied entries are kept for 30 days as a record.

What this doesn’t cover

• ClickHouse’s own backups keep purged rows until the backups themselves expire. Set their retention with your data protection officer.
• An insert that takes more than 10 minutes. If one of the writer’s inserts is in flight for longer than that across a purge or an erasure (a ClickHouse that hangs, for example), its rows can still land after the second pass. A backfill puts them right: it copies PostgreSQL’s rows again and removes what PostgreSQL no longer has.
• A rebuild from Kafka and purges made before this release. The writer leaves out what a purge or erasure removed, as above, for everything recorded since migration 00038. Anything purged earlier and still within Kafka’s retention is replayed; nothing reads it, and the backfill that ends the rebuild removes it. See rebuilding.
• Removing rows from ClickHouse rewrites the files that hold them, which is heavier than an insert. A large first purge, after years without one, can keep ClickHouse busy for a while; later runs are small.

Record the analytics store in your register of processing as a second location of transaction data. This page isn’t legal advice; confirm the approach with your data protection officer and counsel.

When ClickHouse is down

SpeculaPro starts and keeps working without it. Nothing on the decision path, ingest or the analysts’ daily pages depends on ClickHouse.

	What happens
Analytics page	Answers from PostgreSQL over 3 months, and says under its title that the analytics store can’t be reached at the moment. The failure is logged as analytics: ClickHouse read failed; answering from Postgres
A long backtest about to start	Reads PostgreSQL instead. The worker logs backtest: analytics store unavailable; reading Postgres, and the result carries the note Transactions were read from PostgreSQL: the analytics store could not be reached.
A long backtest already reading ClickHouse	Fails, like any interrupted run. Run it again
Scheduled rules I2 and I3	Read PostgreSQL; the worker logs scenarios: analytics store read failed; reading Postgres
The analytics role	Keeps retrying the insert, waiting from a quarter of a second up to 30 seconds between attempts, and commits nothing to Kafka meanwhile. When ClickHouse returns it carries on from where it stopped. Its /readyz on the metrics port answers 503 not ready: clickhouse
Deletions waiting to be applied	Stay recorded in PostgreSQL and are retried on every pass. speculapro_analytics_errors_total{stage="delete"} counts the failures
api and worker readiness	Unaffected when they run without the analytics role: ClickHouse isn’t one of their dependencies. A process that also runs analytics (including --role=all with the store configured) reports clickhouse on the metrics-port /readyz; the API-port checks (/readyz?role=…) never include it

If ClickHouse is down for longer than Kafka’s retention of txn.ingested, the role can’t catch up from Kafka alone. Run a backfill for the gap.

A store that is behind isn’t read

When ClickHouse comes back, the analytics role still has to work through what it missed. Until it has caught up, long backtests and the scheduled rules I2 and I3 read PostgreSQL, and a backtest’s result says the analytics role is behind: it hasn’t written every transaction ingested so far. The Analytics page goes back to the store once it is within 15 minutes of live. Nobody has to remember to wait: see When a reader uses the store. Keep the lag alert on all the same: a store that stays behind isn’t being used.

Monitoring

The role publishes five metrics on the metrics listener:

Metric	Meaning
speculapro_analytics_lag_seconds{source}	How far behind the store is. kafka: the age of the oldest record in the batch just written, 0 when caught up. postgres: the time since the last complete copy of hits, alerts and cases
speculapro_analytics_rows_total{table}	Rows written, by table. Repeated copies are counted here even though the store keeps one
speculapro_analytics_insert_seconds{table}	How long each batched insert took
speculapro_analytics_errors_total{stage}	Failures by stage. insert, rollup, sync, delete: a write to the store is failing and being retried. filter, progress, commit: the writer’s check against recorded deletions, its position report, and Kafka offset commits. decode: a record that was skipped. read: a reader fell back to PostgreSQL
speculapro_analytics_incomplete_workspaces	Workspaces whose history hasn’t been copied to the store completely: never copied, or a copy is running or was interrupted. Their long reads use PostgreSQL. No labels

The worker also counts speculapro_scenario_analytics_reads_total{scenario}: scheduled-rule windows served by the analytics store instead of PostgreSQL.

What to alert on:

Condition	Meaning
max(speculapro_analytics_lag_seconds) > 600 for 15 minutes	The store is more than 10 minutes behind, so long backtests and I2 / I3 are reading PostgreSQL. Check the role and ClickHouse; scale the role, up to the partition count
increase(speculapro_analytics_errors_total{stage=~"insert|rollup|sync|delete"}[15m]) > 0 for 15 minutes	Writes are failing: inserts, the volume totals, the copy from PostgreSQL or deletions. Nothing is lost, but the store is falling behind. Check ClickHouse’s disk, credentials and “too many parts” errors

The Helm chart’s PrometheusRule includes both, as SpeculaProAnalyticsLag and SpeculaProAnalyticsErrors, when clickhouse.enabled is true. There is no ready-made alert on speculapro_analytics_incomplete_workspaces: a value above zero that doesn’t fall means a backfill is still needed, or one was interrupted. Monitor ClickHouse itself (disk, memory, merges) with its own tools: SpeculaPro reports only its side.

Backups and upgrades

Backups. The store is a derived copy: if you lose it, rebuild it. You don’t need to back it up to protect your records. If you do back it up, to avoid a long rebuild, remember those backups hold personal data and aren’t touched by the purge either.

After restoring PostgreSQL from a backup, rebuild the analytics store from PostgreSQL, so the two agree.

Upgrades. speculapro migrate up applies ClickHouse’s schema changes straight after PostgreSQL’s whenever SP_CLICKHOUSE_URL is set, and speculapro migrate status lists them as clickhouse/0001_init.sql and so on. ClickHouse migrations only go forward: migrate down leaves ClickHouse alone and says so. To go back, restore the older release and rebuild the store.

Upgrading an installation that already uses the store. The record of how complete each workspace’s copy is arrives with clickhouse/0003_coverage.sql. It starts empty, so after the upgrade every workspace that has history shows not copied and its long reads use PostgreSQL, even if you copied its history under the earlier release. Run speculapro analytics backfill once after upgrading. It also recomputes the volume totals as quarter-hour totals.

Performance

The design targets are:

	Target
Analytics page, 12 months	Each panel answers in under a second
A 12-month backtest over a very large history (hundreds of millions of transactions)	Finishes in minutes

The Analytics page is built to meet its target: the volume panel reads quarter-hour totals rather than raw transactions.

A long backtest’s read of its history works like this:

1. The period is cut into windows of about 50,000 transactions each, planned from a single count of the period in five-minute steps.
2. Several windows are read at once: half the worker’s CPU cores, never fewer than 2 or more than 8.
3. The windows are handed to the replay in order, with at most about 300,000 transactions read ahead of it, so memory stays bounded.
4. The store holds customers, accounts and counterparties by reference, so their SpeculaPro IDs are resolved in PostgreSQL once per window and cached for the rest of the run.

What has been measured

The figures below are indicative laptop measurements, not a benchmark. They come from the repository’s TestPerformance, run on a developer laptop that was busy with other work: 8 cores, with ClickHouse, PostgreSQL and the test on the same machine. The data was 8 million transactions over 12 months, 6.4 million of them in the workspace that was read, with 100,000 customers, 120,000 accounts and 200,000 counterparties in PostgreSQL.

	Reading one window at a time	Reading windows in parallel, as now
Reading the 12 months for a backtest (best of three runs)	37.9 seconds	11.0 seconds

On the same data, each Analytics panel answered in 30 to 140 milliseconds.

Read these with care:

• They are laptop figures, not a benchmark of a production cluster.
• They time the read only, not a whole backtest.
• Expect variation. The three runs after the change took 11.7, 23.1 and 11.0 seconds.

What this means for a long backtest

A backtest reads its history and then replays every transaction through the rule engine. The run time is dominated by the replay, and the limits on a run (SP_BACKTEST_TIMEOUT, SP_BACKTEST_MAX_EVENTS) still apply. So a faster read shortens a long run without making it short, and the target of finishing in minutes over hundreds of millions of transactions has not been demonstrated. What the analytics store reliably does for a long backtest is take the read load off your PostgreSQL primary.

To measure your own installation, the repository’s TestPerformance in backend/internal/analytics loads a volume you choose and times inserts, the backtest read and each Analytics panel.

Not yet

• Only two scheduled rules (I2 and I3) have an analytics-store version.
• Short run times for long backtests over very large histories haven’t been demonstrated. See Performance.
• There is no setting to keep less history in ClickHouse than in PostgreSQL.

Related

• Analytics page
• Configuration reference: analytics store
• Running roles
• Kubernetes with Helm
• Monitoring
• Retention and legal holds