Durable Analytics rollout, ClickHouse backfill, and rollback¶

PostgreSQL raw is the authoritative event log. The ingress process does only one thing: commit each backend event to that log. PostgreSQL-derived tables and ClickHouse are independent, restartable consumers. This means a consumer deployment, ClickHouse outage, or initial backfill does not restart the TCP listener and cannot discard an in-memory fan-out queue.

The controls are deliberately independent:

SPIFFWORKFLOW_BACKEND_EVENT_STREAM_ACK_ENABLED is an Arena/backend setting. It enables the database-agnostic acknowledged event protocol; Arena does not know which analytical warehouse consumes the durable log.
SPIFFWORKFLOW_ANALYTICS_CLICKHOUSE_ENABLED starts the Analytics ClickHouse consumer.
ANALYTICS_DATA_BACKEND selects API reads and defaults to postgres.

In infra these are managed by spiffworkflow_analytics_clickhouse_enabled and spiffworkflow_analytics_data_backend; changing the latter recreates only the Analytics API. Ingress and both consumers connect directly to PostgreSQL because their schema and singleton advisory locks are session-scoped. The query API may continue through transaction-pooling PgBouncer.

Delivery guarantees and limits¶

In ACK mode the backend gives every event a UUID, negotiates protocol version 2, and waits for ACK <uuid>. Ingress sends that ACK only after the raw event and its UUID receipt commit in one PostgreSQL transaction. If the connection breaks before the ACK, the backend reconnects and retries the exact same UUID. event_receipt.event_id makes that retry idempotent.

An established TCP connection cannot move between processes. During an ingress replacement it will close and reconnect, but an ACK-mode backend retains the unacknowledged event, so the interruption does not lose it. The synchronous retry can add request latency while ingress or PostgreSQL is unavailable; that backpressure is intentional. This does not preserve an event across simultaneous backend process death before acknowledgement. A transactional Arena outbox would be required for that stronger guarantee.

Upgrade order from the legacy dual writer¶

Keep API reads on PostgreSQL throughout these steps.

Set SPIFFWORKFLOW_ANALYTICS_CLICKHOUSE_ENABLED=false. Leave backend ACK mode disabled.
Apply the event-monitoring PostgreSQL migration from the new image while the old listener remains running, then deploy the image. Adding raw.id takes a PostgreSQL table lock and builds its primary-key index; measure this on a production-sized copy and schedule the one-time operation appropriately. The old listener’s in-memory PostgreSQL writer retains events while an insert is blocked by the DDL, provided that listener is not restarted during the migration.

After pulling/selecting the new image tag without recreating the old container:
```
docker compose run --rm --no-deps event-listener python manage_db.py migrate
```
Verify that raw.id, event_receipt, and consumer_cursor exist before replacing ingress. Legacy schema files recorded their database creation date as last_schema_update; the current migrator detects and repairs that misleading version, but this verification also catches an old image or an incomplete migration before traffic is affected.
Compose now runs event-listener as stable ingress and analytics-consumer separately. Confirm ingress port 8006 and metrics 9101; confirm consumer metrics 9103 even though its ClickHouse child is disabled.
Deploy the ACK-capable Arena backend with SPIFFWORKFLOW_BACKEND_EVENT_STREAM_ACK_ENABLED=false. This is wire-compatible with both listener versions.
Replace/backfill the existing shadow ClickHouse store as described below.
Enable the ClickHouse consumer and verify it reaches zero lag.
Set SPIFFWORKFLOW_BACKEND_EVENT_STREAM_ACK_ENABLED=true and deploy only the backend. Confirm ingress duplicate-retry and failure metrics remain healthy.
Run parity checks. Switch API reads only after the observation period succeeds.

The first replacement of a legacy listener is the only transition without ACK protection. Quiesce event-producing requests for that one deployment if the environment cannot tolerate that residual window. Later consumer deployments never touch ingress; later ingress deployments are protected by ACK/retry.

Online initial load or replacement¶

The backfill and live ClickHouse consumer take the same PostgreSQL advisory lock. Ingress does not take it and continues committing new raw events throughout the load.

Run a read-only preflight with a direct PostgreSQL connection rather than PgBouncer:

docker compose run --rm --no-deps \
  -e PGHOST=spiffdb -e PGPORT=5432 \
  analytics-consumer python backfill_clickhouse.py \
    --workers 2 --chunks 128 --ch-send-receive-timeout 7200 --dry-run

For a new empty ClickHouse store:

docker compose run --rm --no-deps \
  -e PGHOST=spiffdb -e PGPORT=5432 \
  analytics-consumer python backfill_clickhouse.py \
    --workers 2 --chunks 128 --ch-send-receive-timeout 7200

For a shadow store populated by the old direct dual writer, stage a fresh copy and atomically replace it:

docker compose run --rm --no-deps \
  -e PGHOST=spiffdb -e PGPORT=5432 \
  analytics-consumer python backfill_clickhouse.py \
    --workers 2 --chunks 128 --ch-send-receive-timeout 7200 \
    --restart --replace-existing

The command acquires the consumer lock, captures max(raw.id) as a cutoff, loads disjoint ranges into raw_events_backfill, verifies the live target did not change during the load, and exchanges the raw tables. It reconstructs each layer-2 aggregate replacement from bounded raw-ID chunks before exchanging that table. It then builds the versioned serving tables from bounded raw-ID chunks and advances their durable cursor only after both process and task publication succeed. Ingress may add IDs above the cutoff at any time. When the lock is released, the consumer resumes from ClickHouse’s maximum postgres_raw_id and copies and publishes every later event.

--workers bounds simultaneous PostgreSQL-to-ClickHouse queries; --chunks creates smaller, progress-visible units without increasing that concurrency. The two-worker, 128-chunk form is the conservative default for a large shared single-server environment. The longer ClickHouse client timeout applies to each chunk and avoids treating a healthy long-running query as failed.

If a staging worker fails, the live table is unchanged. Fix the cause and repeat with --restart (and --replace-existing when applicable). Keep ClickHouse API reads disabled until raw exchange and both aggregation exchanges finish.

Verification¶

Run the full API parity fixture:

./bin/check_dual_store_parity

Monitor:

spiff_analytics_clickhouse_source_head
spiff_analytics_clickhouse_consumer_cursor
spiff_analytics_clickhouse_consumer_lag_events
spiff_analytics_clickhouse_consumer_events_total
spiff_analytics_clickhouse_consumer_failures_total
spiff_analytics_clickhouse_consumer_last_success_unixtime
spiff_analytics_listener_postgres_events_inserted_total
spiff_analytics_listener_postgres_duplicate_events_total
spiff_analytics_listener_postgres_insert_failures_total
API query latency/errors by backend and query
Prometheus up for listener, consumer, API, and ClickHouse

After deploying an image that changes an incremental raw-to-aggregate materialized view or aggregation-table definition, keep reads on PostgreSQL, stop analytics-consumer, and rebuild the aggregate tables before restarting it:

docker compose stop analytics-consumer

docker compose run --rm --no-deps \
  -e PGHOST=spiffdb -e PGPORT=5432 \
  analytics-consumer python backfill_clickhouse.py --rebuild-aggregations

docker compose up -d analytics-consumer

docker compose run creates a one-off container from the stopped service’s new image and configuration. It does not start the normal consumer service. If the rebuild exits nonzero, leave API reads on PostgreSQL and investigate before starting the consumer or allowing ClickHouse reads.

The rebuild uses the same singleton lock and atomically exchanges each completed layer-2 replacement table. Its default reconstruction queries cover at most 500,000 raw IDs, use at most two ClickHouse threads, and have a 2 GiB per-query memory ceiling. Those bounds are independently adjustable by the backfill CLI. A failed chunk leaves the live aggregate untouched.

Incremental serving publication and freshness¶

Schema versions 2 and 3 introduced query-ordered snapshots and precomputed durations. Their refreshable materialized views recopied every process and task each minute, which is unsafe at production scale. Schema version 4 drops those refresh jobs but deliberately retains their last target tables as a rollback snapshot.

Schema 4 adds query-ordered ReplacingMergeTree serving tables and serving_publication_state. For each bounded raw-ID range the publisher:

finds the process and task IDs touched by that range;
writes tombstones for their prior serving rows;
finalizes only those IDs from the layer-2 aggregate state;
writes the replacement rows at a higher deterministic version; and
advances the serving cursor only after both entity tables succeed.

A retry uses the same versions, so interruption before cursor advancement is idempotent. Tombstones also handle the uncommon correction that changes an entity’s model or start-time sorting key. Public views use FINAL and filter tombstones while retaining model/time ordering for dashboard queries.

Schema version 5 makes event time the primary process/task state ordering key and uses PostgreSQL raw ID only to break same-millisecond ties. This prevents older task events that were inserted after a process-completion event from reopening the completed process. It also removes tasks without a valid process instance from the finalized layer-2 view. Existing layer-2 rows retain their old aggregate states until --rebuild-aggregations completes; use the bounded rebuild procedure above before enabling ClickHouse API reads.

Schema version 6 exposes a task terminal timestamp only when its latest state is COMPLETED, ERROR, or CANCELLED. Tasks that recover from an error no longer retain a stale end time or completed duration. The migration changes only the finalized layer-2 view; republish layer 3 to correct existing serving rows. It also adds a process-instance skipping index to the task serving table so task queries can follow the process-window contract without scanning unrelated task rows.

Schema version 7 preserves the raw event timestamp’s microsecond remainder inside the existing aggregate ordering key. This resolves task transitions that occur within one millisecond but reach PostgreSQL in the opposite order. PostgreSQL raw ID still breaks genuinely identical timestamp ties. Existing layer-2 aggregate states must be rebuilt before ClickHouse API reads are enabled; the bounded aggregation rebuild and clean-volume enablement paths both apply the corrected ordering.

Schema version 8 replaces the layer-3 task table with a layout ordered by process model, process instance, task start time, and task ID. Detail queries first select process instances by the process window and can therefore prune directly to those instances instead of scanning every historical task for the model. The migration leaves the complete legacy table and public view intact but clears serving publication state. Keep API reads on PostgreSQL and run a serving rebuild before enabling ClickHouse reads. After the replacement has been populated, validated, and made public, the publisher drops the legacy layer-3 task table. This rebuild does not recopy raw events or reconstruct layer 2.

A clean ClickHouse volume also receives the latest schema, but it must recopy every raw event from PostgreSQL before performing the same aggregate and serving builds. Retaining a healthy volume is therefore normally faster and preserves the durable raw layer. A clean-volume run is useful as an explicit installation test, not as a required upgrade step.

The migration does not point public views at empty new tables. Initialize and validate them while API reads remain on PostgreSQL:

docker compose stop analytics-consumer

docker compose run --rm --no-deps \
  -e PGHOST=spiffdb -e PGPORT=5432 \
  analytics-consumer python backfill_clickhouse.py --publish-serving

docker compose up -d analytics-consumer

Use --restart-serving only to discard and recreate the new serving tables. That clean-start path snapshots the already-finalized layer-2 process and task tables directly, with one thread, a bounded memory limit, and external sort spilling by default. It does not replay every raw-ID range. Without --restart-serving, --publish-serving resumes from the durable serving cursor. The full initial backfill and --rebuild-aggregations initialize serving publication automatically. If the direct snapshot exceeds a server limit, the publisher clears its partial rows and automatically falls back to the slower bounded raw-ID path.

After initialization, every live raw insert updates layer 2 synchronously and the consumer publishes touched entities after accumulating 50,000 raw IDs or waiting 10 seconds, whichever comes first. Publication uses one thread and a 2 GiB query ceiling by default. The batching threshold and flush interval are configurable with SPIFFWORKFLOW_ANALYTICS_CLICKHOUSE_SERVING_BATCH_SIZE and SPIFFWORKFLOW_ANALYTICS_CLICKHOUSE_SERVING_FLUSH_INTERVAL_SECONDS. The API reports serving_uninitialized until initial publication is ready and serving_stale whenever the serving cursor trails the ClickHouse raw cursor.

Completed durations remain stored. Public views calculate the moving duration for only the filtered active processes and unfinished tasks at query time, so they no longer require periodic full-table rewrites.

Monitor these consumer metrics in addition to raw-log lag:

spiff_analytics_clickhouse_serving_publish_cursor
spiff_analytics_clickhouse_serving_publish_lag_events
spiff_analytics_clickhouse_serving_publish_ready
spiff_analytics_clickhouse_serving_publish_last_success_unixtime
spiff_analytics_clickhouse_serving_publish_duration_seconds
spiff_analytics_clickhouse_serving_publish_batches_total

An older consumer refuses to run against a newer schema before changing any ClickHouse objects; leave the latest consumer running or disable ClickHouse ingestion during an application rollback. The durable PostgreSQL event log, layer-2 aggregate data, and legacy serving snapshot are unaffected.

The Grafana Spiff Analytics: PostgreSQL vs ClickHouse dashboard and Alertmanager rules cover scrape health, durable consumer lag/stalls/failures, query performance, and ClickHouse merge/part pressure.

Routine deployment and rollback¶

Deploying analytics-consumer restarts only derived-table and ClickHouse consumers. Ingress and its backend TCP connections stay untouched. Advisory locks make overlapping old/new consumer instances safe.

To roll back reads, set ANALYTICS_DATA_BACKEND=postgres and restart only the Analytics API. To pause ClickHouse ingestion, set SPIFFWORKFLOW_ANALYTICS_CLICKHOUSE_ENABLED=false and restart only analytics-consumer; PostgreSQL ingress continues. Re-enabling the consumer resumes from its durable raw-ID boundary.