--- title: Analytics sink description: An opt-in, cross-tenant analytics read model fed by the shared event stream — fleet-wide reporting without touching per-tenant databases. --- Every fabriq tenancy mode is built around one invariant: no cross-tenant queries. That is exactly why fleet-wide reporting ("orders fleet-wide, by status") has nowhere to run — not in a shared database, not across shards, not across thousands of per-tenant databases — without punching a hole in the isolation the rest of the system guarantees. The analytics sink is the one deliberate, narrow, auditable exception: an opt-in projection that co-locates a **redacted, allow-listed** subset of tenant data for operator-only reporting. **Trust boundary.** This is the only place fabriq deliberately co-locates data from many tenants in one store. It is operator-only: never wire it to a tenant-facing dashboard, API, or export. Every table carries `tenant_id` and every query pattern below filters or groups by it — but there is deliberately **no RLS**, because RLS defends a store scoped to one tenant on `ctx`, and this store's entire purpose is cross-tenant aggregation. PII exposure is bounded by allow-listing fields per entity (see [Enabling it](#enabling-it)), not by row-level policy. See [ADR 0013](/docs/fabriq/reference/decisions) for the full trust-boundary rationale. ## Topology The analytics sink is a **third projection peer** to graph and search — it reads the same shared event stream, not a new transport: `proj:analytics` is an ordinary Redis consumer group on `registry.StreamKey()`, consumed through the exported `projection.Source` seam (`core/analytics.Consumer` deliberately does not wrap `projection.Engine`, which is hardwired to graph/search's closed `Mutation` set). Each envelope is turned into a redacted `Fact` + `Event` by a pure `core/analytics.Applier`, then written through the `core/analytics.Sink` port. The reference adapter, `adapters/pganalytics`, is a shared Postgres database. Because it rides the existing stream, the analytics sink starts in **every** tenancy mode — static and catalog alike — with no per-shard or per-tenant-database wiring. ## Enabling it The sink is off unless `Config.Analytics.DSN` is set, and the `proj:analytics` consumer only starts when a DSN is configured AND at least one entity opts in: ```yaml analytics: dsn: postgres://fabriq_analytics@analytics-db:5432/fabriq_analytics batch: 128 # backfill write batch size (default 128) ``` The analytics DSN **must differ** from every tenant, shard, and catalog control DSN — `ValidateAnalyticsConfig` checks this at `fabriq.Open` and refuses to start on a collision. Separate database, separate credential, enforced at boot, not left as an operational convention. Mark an aggregate for analytics with an allow-list of payload fields on its `registry.EntitySpec`: ```go r.Register(registry.EntitySpec{ Name: "order", // ... Analytics: ®istry.AnalyticsSpec{ Include: []string{"status", "category", "total"}, }, }) ``` Everything not named in `Include` is stripped before the record leaves the projection — deny-by-default at the aggregate level, then field minimization within a marked aggregate. Field names may be **dot-paths** (`"meta.region"`) that descend through nested JSON objects, so you can keep or hash a single nested leaf and drop its PII siblings; a path whose intermediate segment is missing or isn't an object simply contributes nothing. Leaf values keep their exact bytes (numbers and ids never lose precision). `IncludeAll: true` ships the whole payload unredacted; use it only when you have already reviewed the payload for PII. A spec with no `Include`, `Hash`, or `IncludeAll` is rejected by the registry (a marked-but-empty spec would silently analyticize nothing). ### Pseudonymizing a field Sometimes you need to *analyze by* a sensitive field — count distinct users across the fleet, group orders by customer — without co-locating the raw value. List those fields under `Hash`: their value is replaced with a stable, salted hash before it crosses the boundary. Equal values hash equally (so group-by and count-distinct still work), but the raw value never lands and is not recoverable. ```go Analytics: ®istry.AnalyticsSpec{ Include: []string{"status", "total"}, Hash: []string{"customerId"}, // pseudonymized, not raw }, ``` The hash uses `Config.Analytics.HashSalt` — a stable, secret, deployment-wide salt (a leaked salt makes the hashes brute-forceable). It is **required** when any entity uses `Hash`; `Open` fails fast otherwise. Because the salt is stable, live apply, backfill, and reproject all produce the same hash for the same value. A hashed field is implicitly included — it need not also appear in `Include`. ## Choosing a backend `Config.Analytics.DSN`'s URL scheme selects the sink adapter at `Open` — there is no separate driver setting: | Scheme | Adapter | Example | | --- | --- | --- | | `postgres://`, `postgresql://`, or a bare keyword DSN (no `://`) | `adapters/pganalytics` (default) | `postgres://fabriq_analytics@analytics-db:5432/fabriq_analytics` | | `clickhouse://` | `adapters/chanalytics` | `clickhouse://user:pass@analytics-db:9000/fabriq_analytics` | | `duckdb://` | `adapters/duckanalytics` | `duckdb:///var/lib/fabriq/analytics.db` or `duckdb://:memory:` | An unknown scheme fails fast at boot: `fabriq: unknown analytics DSN scheme "…"`. All three adapters satisfy the same `core/analytics.Sink` port and run through the identical conformance suite, so they are drop-in interchangeable at that level — the differences below are operational, not behavioral. ### Postgres (default) The reference adapter described throughout this page. It is the only backend that supports `partitionEvents` (see [Partitioning at scale](#partitioning-at-scale)) — DuckDB and ClickHouse don't have a native partition-drop primitive wired into the sink, so `partitionEvents: true` with a non-postgres DSN scheme is also a boot error: `fabriq: analytics PartitionEvents is only supported on the postgres backend`. ### ClickHouse ```yaml analytics: dsn: clickhouse://user:pass@analytics-db:9000/fabriq_analytics ``` The three tables are `ReplacingMergeTree`, ClickHouse's columnar merge-on-read engine — there is no in-place `UPDATE`, so the version-gate described above is enforced by keeping the highest-versioned row per key and letting background merges (or a query-time `FINAL`/`argMax`) resolve to the winner. This changes how you read the store directly: - **Point/aggregate reads issued by the sink itself** never need `FINAL` — they use `argMax(col, _dedup)` (facts/events) or plain `max(version)` (watermarks), which is correct without waiting for a background merge. - **Ad hoc operator queries** against `fabriq_analytics_facts` or `fabriq_analytics_events` should add `FINAL` to the `FROM` clause (or aggregate with `argMax` the same way) — without it, a query can see both a stale and a current row for the same key until the next merge. - `payload` is stored as `String` (ClickHouse's native `JSON` type is still experimental), so query into it with `JSONExtract*` functions, e.g. `JSONExtractString(payload, 'status')`, rather than a JSONB-style `->>`. - Prune and purge (`fabriq analytics prune-events`, `PurgeTenant`) issue a lightweight `DELETE FROM … WHERE …`, generally available since **ClickHouse 23.3**. On an older server, enable `allow_experimental_lightweight_delete` or upgrade before relying on those operations. The ClickHouse driver is pure Go — it ships in the default `CGO_ENABLED=0` release binary with no extra build tag. ### DuckDB ```yaml analytics: dsn: duckdb:///var/lib/fabriq/analytics.db # file-backed # dsn: duckdb://:memory: # in-process, non-durable ``` DuckDB is embedded — no server to run, no network hop — and columnar like ClickHouse, but with full mutable SQL (`UPDATE`/`DELETE` in place), so its adapter reads closer to the Postgres one than the ClickHouse one. **Requires a CGO build.** The DuckDB driver is cgo-linked, so it is compiled in only under the `duckdb` build tag with `CGO_ENABLED=1`: `CGO_ENABLED=1 go build -tags duckdb ./...`. The default release binary (`CGO_ENABLED=0`, no build tag) does not include it — configuring a `duckdb://` DSN against that binary fails at boot with `fabriq: duckdb analytics support not built into this binary (rebuild with -tags duckdb)`. Because it's in-process, a DuckDB sink is tied to the fabriq instance's own disk and lifetime — there's no separate database to point a second reader at. It suits a single-node deployment or local/edge reporting more than a shared fleet-wide store queried by other tools; for that, ClickHouse or Postgres remain the better fit. ## Schema `adapters/pganalytics` ensures its schema idempotently at `Open` (three `CREATE TABLE IF NOT EXISTS` statements) rather than joining fabriq's tenant migration chain — this is a self-contained operator store: | Table | Shape | Purpose | | --- | --- | --- | | `fabriq_analytics_facts` | PK `(tenant_id, aggregate, agg_id)`, `version`, JSONB `payload`, `deleted` | Latest denormalized state per aggregate instance. Upserts are version-gated: `WHERE excluded.version > fabriq_analytics_facts.version`. | | `fabriq_analytics_events` | PK `(tenant_id, aggregate, agg_id, version)`, `type`, JSONB `payload` | Append-only history of every applied change. Inserts use `ON CONFLICT DO NOTHING`. | | `fabriq_analytics_applied` | PK `(tenant_id, aggregate, agg_id)`, `version` | Per-aggregate watermark, used for backfill resumability. | Every table has `tenant_id NOT NULL`. There is no RLS on this database — see the trust boundary callout above. The `version` gate on `fabriq_analytics_facts` is what turns Redis's at-least-once delivery into an idempotent effect: redelivery of an already-applied envelope is a no-op, with no separate dedupe table or lock required. The steady-state apply path only ever reads and writes the analytics database — it never touches a tenant database. ## Query patterns Every query against the analytics database filters or groups by `tenant_id` — that is the whole point of retaining the column even though there is no RLS to enforce it. Fleet-wide aggregation groups across tenants deliberately; per-tenant reporting still filters by it. Fleet-wide count of live orders by status: ```sql SELECT payload->>'status' AS status, count(*) FROM fabriq_analytics_facts WHERE aggregate = 'order' AND NOT deleted GROUP BY payload->>'status'; ``` Per-tenant history for one order: ```sql SELECT version, type, payload, at FROM fabriq_analytics_events WHERE tenant_id = $1 AND aggregate = 'order' AND agg_id = $2 ORDER BY version; ``` ## Backfill runbook Backfill replays a tenant's **current-state snapshot** — not the event log — through the same `Applier` the live consumer uses, so a re-run is a no-op (version-gated) and safe to run alongside live traffic. Use it when you mark a new entity for analytics (its history predates the marking) or to recover a dropped/rebuilt analytics database. CLI, one tenant or the whole fleet: ```bash export FABRIQ_ANALYTICS_DSN=postgres://fabriq_analytics@analytics-db:5432/fabriq_analytics fabriq analytics backfill --tenant acme fabriq analytics backfill --all-tenants --concurrency 8 ``` With the admin extension mounted and `WithAnalyticsAdmin()` enabled, the `analytics.admin`/`analytics.read` capabilities expose the same operation over HTTP: | Method + path | Effect | | --- | --- | | `POST {base}/analytics/backfill` `{"tenant":"acme"}` or `{"all":true,"concurrency":8}` | Backfill one tenant or the fleet — synchronous, returns per-tenant row counts once the replay completes | | `GET {base}/analytics/status` | `{enabled, tenantCount}` — whether the sink is configured, without triggering any work | **Async for large fleets.** A fleet-wide op (`"all": true`) on a big fleet can run longer than an HTTP timeout. Add `"async": true` to `backfill`, `reproject`, or `reconcile` and the endpoint returns `202` with a `{"jobId"}` immediately; poll `GET {base}/analytics/jobs/:id` for `{state, result}` (`running` → `done`/`failed`), or stream state changes over SSE at `GET {base}/analytics/jobs/:id/stream`. Single-tenant ops stay synchronous. A synchronous fleet backfill (`"all": true`) that partially fails returns HTTP `207` with the partial per-tenant counts and an error string, rather than discarding successful tenants' results. Backfill is deliberately synchronous today — an async job/SSE variant (matching the tenant provisioning and migration job APIs) is a documented future enhancement, not built here. ## Erasure and offboarding Because the sink co-locates many tenants' data, it needs an explicit way to remove a tenant — for offboarding and for right-to-be-forgotten requests. `PurgeTenant` hard-deletes **all** of one tenant's rows across the three tables (facts, events, and watermarks) in a single transaction and reports the count removed. It is idempotent (purging an absent tenant deletes nothing) and is the **only** supported way to remove a tenant from the store. ```bash # CLI — the destructive erase requires an explicit --yes. fabriq analytics purge --tenant acme --yes ``` | Method + path | Effect | | --- | --- | | `POST {base}/analytics/purge` `{"tenant":"acme"}` | Erase one tenant's facts, events, and watermarks; returns `{tenant, rowsDeleted}`. Gated on `analytics.admin`. | Erasure is **never automatic**. Suspending or offboarding a tenant in the catalog does not touch the analytics store — mirroring fabriq's rule that it never drops a tenant database on its own. Purge is a deliberate operator step, and it is irreversible: the append-only event history for that tenant is gone. Run it as the final step of an offboarding runbook, after any last export. ## Changing redaction after the fact Tightening an entity's `Include` allow-list (or dropping a field) does **not** retroactively strip that field from rows already in the sink: backfill replays at each row's current version, and the fact upsert is version-gated, so a same-version replay is a no-op — and the event log is immutable. **Reprojection** closes that gap. It re-projects every stored fact and event payload through the entity's *current* spec, in place, rewriting only rows that actually change: ```bash fabriq analytics reproject --tenant acme fabriq analytics reproject --all-tenants --concurrency 8 ``` | Method + path | Effect | | --- | --- | | `POST {base}/analytics/reproject` `{"tenant":"acme"}` or `{"all":true,"concurrency":8}` | Re-apply the current allow-list to stored rows; returns per-tenant rewrite counts. Gated on `analytics.admin`. | Reprojection can only **narrow** — it removes fields from the already-stored (possibly wider) payload, which is exactly what a privacy tightening needs. It cannot *widen* (bring a previously-stripped field back), because that data was never co-located; re-adding a field requires the source database, i.e. writing the row again so live apply re-ingests it. It is idempotent: a second run rewrites nothing. ## Event-log retention `fabriq_analytics_facts` holds one latest-state row per aggregate and stays bounded, but `fabriq_analytics_events` is an append-only history that grows without limit. Set a retention window to bound it — a worker pruner then deletes history events older than the window on an hourly cadence (facts are never pruned): ```yaml analytics: dsn: postgres://…/analytics eventRetention: 2160h # keep 90 days of history; 0 (default) keeps forever ``` The pruner runs in both the static worker and the catalog sweeper planes and emits `fabriq_analytics_events_pruned_total`. For a one-off trim (or when no worker runs), use the CLI: ```bash fabriq analytics prune-events --older-than 2160h ``` ### Partitioning at scale At a very large fleet, deleting expired rows one-by-one is expensive. Set `partitionEvents` to create the event log as a **monthly range-partitioned** table: retention then reclaims space by **dropping whole partitions** (instant) instead of a delete-scan, and queries prune irrelevant months automatically. ```yaml analytics: dsn: postgres://…/analytics eventRetention: 2160h partitionEvents: true # monthly partitions; drop-partition retention ``` A worker maintainer creates upcoming month partitions ahead of the write clock and drops any partition entirely older than the retention window. A **default partition** catches any write outside the maintained window (e.g. a backfill appending an event with a historical timestamp), so writes never fail; the row-level pruner sweeps that default partition for the boundary. Partitioning takes effect on a **fresh** analytics database only — `CREATE TABLE IF NOT EXISTS` will not convert an existing non-partitioned event table in place. Migrating an existing deployment (create partitioned, copy, swap) is a manual operation. The partitioned event log's primary key includes `at` (the partition key), which is fixed per version, so dedup is unchanged — the full sink contract is verified identically in both modes. ## Reconciling drift The consumer skips any event it cannot upcast or apply (poison-avoidance), so a deployment bug can leave a fact permanently missing or stale — the same way it would for the graph and search projections, which heal from Postgres via their reconcilers. **Reconciliation** is the analytics equivalent: it reads each marked aggregate's current source state, compares it against the stored watermark, and re-applies only the aggregates that are missing or behind. ```bash fabriq analytics reconcile --tenant acme fabriq analytics reconcile --all-tenants --concurrency 8 ``` Each tenant reports `checked / drifted (missing, stale) / healed`. It is idempotent — a healthy tenant reports zero drift — and safe to run alongside live traffic (the same version gate protects it). Unlike backfill, which re-applies every row blindly, reconcile only touches what actually diverged, so it doubles as a **drift audit**: a non-zero `drifted` count is a signal that the consumer skipped events (check `fabriq_analytics_failures_total` and the logs). ## Freshness, lag, and metrics The sink is near-real-time: lag is relay latency plus apply time, sub-second in a healthy deployment, because it rides the same stream graph and search already consume from. Two counters, defined in `internal/metrics`, are emitted by the `proj:analytics` consumer: | Metric | Type | Meaning | | --- | --- | --- | | `fabriq_analytics_applied_total` | counter | Envelopes successfully applied by the analytics consumer. | | `fabriq_analytics_failures_total` | counter | Envelopes the consumer failed to apply (transient; left pending for redelivery). | | `fabriq_analytics_lag_seconds` | gauge | Worst-case freshness — the **stalest** tenant's lag. Sampled per-tenant every 15s, so one stalled tenant is not masked by others. | | `fabriq_analytics_tenants_behind` | gauge | How many tenants exceed the 60s lag alarm threshold. Non-zero → query `fabriq_analytics_facts` grouped by `tenant_id` to find which. | **Planned, not yet built:** a `fabriq_analytics_backfill_rows_total` counter (backfill runs outside the worker's metrics loop today). Until then, use the CLI/admin backfill row counts to gauge one-off catch-up volume. The full metric catalog and health endpoints. Catalog mode — the tenancy mode the analytics sink is designed to sit alongside without per-tenant-database wiring.