Split Insight into an engineering (data contract) layer and a presentation layer

[dzarlax]

Summary

Split Insight into two clearly separated parts:

• Engineering layer — collection, unification, and identity resolution. Produces
  a contract: a set of tables that behave as an API. This side is optimized for
  correctness, stability, and reliability.
• Presentation layer — a BI-like layer on top of the contract: metric math,
  methodology, and visualization. Optimized for fast, safe iteration.

The two halves have fundamentally different engineering profiles (heavy data
engineering vs. rapidly-changing reporting) and should be developed, tested, and
scaled independently.

Motivation

• Separation of responsibility. The engineering side owns hard data problems
  (collect, normalize, deduplicate, resolve identity). The presentation side owns
  interpretation and display and can move much faster.
• The engineering layer is useful on its own. With a stable table contract,
  a consumer who does not want our UI can point any BI tool at the contract.
• Independent evolution and testing. The two sides have different testing
  approaches and different release cadences; a stable/unstable presentation build
  can point at the same engineering contract.
• Read/write split maps cleanly onto a clustered ClickHouse deployment. The
  engineering side is write-heavy and runs periodically; the presentation side is
  read-heavy and serves constant load — different node groups, easier to parallelize.
• Contract versioning. The contract can be versioned; new tables can be added
  while existing views keep working (old frontend on new tables), like any
  client–server application.

Contract boundary

• The contract is silver (normalized facts) + identity artifacts.
• The gold layer is not part of the contract — it belongs to the presentation
  layer (see below).
• Identity resolution stays in the engineering layer; only its artifacts (ready
  keys and relations) flow into the presentation layer, which does not recompute them.

Requirements (product-level)

The presentation layer must guarantee three properties: safe for the source data,
fast to change, simple.

Safe for the source data

• The presentation layer reads the contract under a read-only (SELECT-only)
  role. No presentation code path can DROP/INSERT/ALTER/TRUNCATE engineering tables.
  The public query API accepts exactly one SELECT/WITH and rejects everything else.
• Everything the presentation layer creates (views, materialized views, scratch
  tables, snapshots) lives in a separate namespace from the contract. A broken
  query or experiment can destroy at most the presentation scratch, never the source.
• Multi-tenant isolation is mandatory at the presentation boundary (tenant scoping +
  hierarchical access: a manager sees their subtree, an IC sees only themselves).
  The mechanism is an open decision (see below).

Fast to change

• A new metric or view can be added without touching the engineering layer and
  without re-ingesting data (a read-only query API accepts new queries over the
  contract).
• The change→preview loop is measured in minutes, not two-repo/two-PR/CI cycles:
  one place to change presentation, an auto-provisioned preview environment per
  branch, and a data-source switch via environment variable.
• Methodology (taxonomy, thresholds, metric definitions) is declarative config,
  re-derivable on demand from the same contract — no re-collect.

Simple

• One source of truth per panel (windowed and full render never drift).
• A "passport" for every number (source → formula → notes) living next to the code
  that computes it, guarded by a drift test.
• Config-first: identity/company/semantic config edited via UI, with discovery to
  pre-populate and an effective-config inspector.

Trust surface (cross-cutting): precision badges (exact/heuristic), honest NULLs
instead of zeros when a source is missing, floor/upper-bound honesty for estimated
metrics, and PARTIAL/staleness banners.

Frontend: UI composed from a reusable panel/widget library (not bespoke per
screen); FE talks only to the read-only query API; thresholds/passports/units come
from the metric catalog rather than being hardcoded; no mocks-as-truth (render
honestly from the real contract); slicing and drill are first-class.

Gold layer ownership and loading

• The gold layer belongs to the presentation layer and is materialized in the
  presentation's own namespace over the read-only contract.
• Presentation role rights: SELECT on the contract + CREATE/INSERT only in its
  own schema (INSERT INTO presentation.gold_x SELECT FROM contract.silver_y — the
  contract is untouched).
• Loading model, simplest first:
  • default: plain VIEW (computed on read, no load);
  • materialized gold: refreshable MV (REFRESH EVERY) or a scheduled
    INSERT…SELECT in the presentation schema; refresh cadence tied to silver updates;
  • FINAL when reading silver ReplacingMergeTree(_version) for dedup.

What moves where

• Today's gold views and the metric aggregation (query_ref in the analytics API
  seed) move to the presentation layer.
• The analytics API shrinks to "execute a read-only SELECT from the catalog against
  the contract".
• The metric catalog becomes a single registry in the presentation layer
  (definition + formula + passport + threshold/unit + aggregation in one place),
  collapsing today's three scattered sources of truth (analytics-API seed +
  frontend threshold config + catalog spec) into one.

Resolved decisions

1. Semantic layer lives in the presentation layer. The metric catalog
   (definitions, formulas, thresholds, units, aggregation, passports) lives in the
   presentation layer over the immutable raw + identity contract. Engineering does
   not store metric semantics.
2. Contract cut at silver; gold belongs to the presentation layer, materialized
   in the presentation namespace over the read-only contract. This resolves the
   apparent conflict "gold is written vs. presentation is read-only": read-only
   applies to the contract, while the presentation owns a writable namespace of its
   own.

Open decisions

1. Tenant isolation mechanism — ClickHouse row policies vs. schema/DB per tenant
   vs. filtering by injected WHERE in the API. To be decided by a benchmark.
   Target scenario: ~1000 organizations, each querying ~1/sec, plus hierarchical
   subtree access. Research summary available; key findings: prefer a shared table
   with the tenant column leading the ORDER BY; pass tenant scope per query over a
   single shared read-only pool (do not create a DB user/role per tenant — that is
   what explodes the connection pool); a materialized view does not inherit the
   source table's row policy, so isolation is best injected at read/API time; verify
   the ClickHouse build carries the fix for the row-policy move-to-PREWHERE
   regression.
2. Read-only for v1. Write-back is considered orthogonal and out of scope for the
   first version. To confirm.
3. Frontend repository structure — a separate prototype repo/branch vs. a single
   repo with per-branch preview environments.

Critical path (minimum loop that makes fast iteration real)

1. A read-only query API over the contract (accepts a SELECT, returns data).
2. A writable presentation namespace where gold views/MVs are created over silver.
3. Per-branch preview environment + env-var data-source switch (the biggest
   current bottleneck).
4. A panel/widget library so new screens are composition, not bespoke code.

Precondition: the silver contract must be stable and versioned — additive changes
only — so presentation work does not build on shifting ground.

Out of scope

• Write-back / bidirectional connectors (orthogonal; a later, separate track).
• Same-day AI attribution, AI-only cost-per-line, duplicated screens, and
  "smeared" model-over-time charts (on demand only, in an experimental section).