Skip to content

Projections

Read models and analytics

Tier: Read · Requirement module: Analytics and BI · Status: ⬜ Not started

Purpose

Projections serves every question that is not "what is the operative record right now". Operational dashboards, late-filing trends, sector heatmaps, capital adequacy summaries, AML/CFT trends, complaint statistics, inspection planning, and export packs are all built from denormalised read models fed by change events.

This makes the seventh law true. Quarter-end is when every firm files and every supervisor runs reports; those two activities must not contend for the same rows.

Scope

Capability Status Notes
Operational dashboards ✅ Compliance rate, overdue count, high-risk firms, pending reviews
Late filing trends ⬜ By period, licence category, return type
Sector heatmaps ⬜ Risk band by category and return, drillable to firm
Capital adequacy summaries ⬜ Sector-wide early warning distribution
AML/CFT trends ⬜ STR volumes, PEP exposure, screening gaps (R07–R10)
Complaint statistics ⬜ Volume, resolution time, root cause (R18)
Inspection plans ⬜ Risk-ranked candidate lists with the drivers that ranked them
Export packs ⬜ Board and ministerial reporting bundles

The pipeline

engine state change
        │
        ▼
   outbox table          ← same transaction as the change
        │
        ▼
      Kafka
        │
        ▼
 projection consumer     ← idempotent, replayable
        │
        ▼
 reporting schema        ← typed columns, read-only credentials

Two properties this buys:

A projection can never claim something the record does not contain. The outbox is written in the same transaction as the state change, so there is no window in which an event exists without its cause, or a cause without its event.

Projections are rebuildable. Consumers are idempotent and events are retained, so a projection with a bug is fixed by correcting the consumer and replaying. This is the reason projections may be denormalised aggressively — they are derived, and derived data can be thrown away.

Flattening the template shape

Submissions are stored as jsonb keyed by field id, which is right for the write path and wrong for analytics. Projections flatten the fields analysts actually query into typed columns:

Column Source Type
total_assets values ->> 'R02-X01' numeric(20,2)
early_warning_ratio values ->> 'R02-X08' numeric(6,3)
breaches values ->> 'R02-F08' integer
risk_score Prism score smallint
days_late Cadence integer

So reporting gets real types, real indexes, and query plans a DBA can reason about, while the write path keeps the flexibility a configurable template demands. The projection definition is generated from the template, so a new field becomes a new column without hand-written DDL.

Staleness, stated

Projections are eventually consistent — typically sub-second, but not guaranteed.

Every screen makes an explicit choice, recorded in its specification:

Screen Source Why
Returns triage register Projection Hundreds of rows, seconds of staleness is immaterial
Return detail under review Record The reviewer is deciding on it now; staleness is unacceptable
Sector heatmap Projection Aggregate by nature
Audit trail on a return Record Evidence is read from Seal directly

A screen that shows projected data displays the projection's lag when it exceeds a threshold, rather than silently presenting stale figures as current.

Owns

  • Read model schemas and consumers
  • Aggregates, trends, and heatmap data
  • Export pack generation

Does not own

  • Any authoritative fact. Every value here is derived and can be rebuilt from the engines.

Open questions

  • Warehouse boundary. Projection tables in PostgreSQL are sufficient for supervisory scale. Whether a supervisor eventually wants an external warehouse for cross-year analytics is a procurement question, not an architectural one — the events are already the integration point.
  • Firm-facing analytics. Should a firm see its own position against anonymised sector benchmarks? Valuable for compliance behaviour, and a disclosure question for the supervisor.