A read-only aggregate of one field across the child records reached through a to-many relation: a Company's Total Pipeline is the SUM of its Deals' amount; # of Deals is a COUNT.

Relation links to a record · Lookup shows one of its fields · Rollup adds them up.

Unlike a lookup, a rollup is stored: it owns a physical JSONB column and the value is written by the engine, never by a record payload. That single difference drives most of the design below — a lookup that stops resolving just renders blank on the next read, but a rollup that stops being recomputed would leave a stale total sitting on the record looking current.

Configuration

options on a rollup field:

Accepted target types for sum/avg are number and currency only.

currency is not a numeric column — it is VARCHAR(50) holding a money string ("1234.56 USD"), so the aggregate parses the amount out with moneyAmountRef, the same helper the record filter and sort use. Summing that column directly raises function sum(character varying) does not exist and fails the whole recompute, which is why RollupTargetMeta carries the field type through to the compute step. rating is an ordinal (summing "4 stars" is meaningless) and serial_number is an identity counter, not a quantity — both are numeric in storage and rejected at config time rather than producing a plausible-looking wrong total.

min / max / count_distinct / concat and a child filter are specified in FRD-33 but not implemented; the validator rejects them so a config that saves is always one the engine can compute.

Read-time descriptor

RollupDescriptorEnricher stamps relation_field_label and target_field_label onto options on every read that returns a field descriptor, resolving both through RollupConfigLoader — the same loader the recompute engine uses, so a descriptor can never label a configuration the engine would refuse to run. target_field_label is absent for count, and both are absent when the config no longer resolves.

Neither is persisted, and neither is cached: they are derived from the relation and the child's field list, which do not invalidate this object's field cache.

Validation

Two layers, both before any write:

1. RollupOptionsValidator — structural, no DB. Function in range, targeted function names a target, presentation options in range.
2. RollupConfigService — resolves against the database through the same RollupConfigLoader the engine uses, so a config that saves is one that computes. It cannot drift into "creatable but never computed".

Rejections carry a machine-readable code:

Stored value — the envelope

{
  "value": 25,
  "sum": 25,
  "count": 2,
  "computed_at": "2026-07-28T09:12:44.100Z",
}

value is the display scalar. sum / count are bookkeeping — they let a client render "average of 3 deals" without a second query, and they are why an avg does not need to re-scan children to be explained.

• count rollups store sum: null. Writing 0 there would read as "the children summed to zero".
• avg divides by the contributing child count (children whose target is non-null), not by the number of linked children.
• An avg over zero contributing children follows division_by_zero: null (nothing to show), zero, or { "error": "DIVISION_BY_ZERO" } — never NaN.
• computed_at is the freshness signal: the client compares it against the record's own updatedAt to decide whether to show an "updating…" state.

Recompute

Totals move as a side-effect of child records changing, so the work is dispatched from the child's write path onto the records.rollup Bull queue and runs out-of-band. A rollup is therefore eventually consistent — the lag is that queue.

The subtle part is finding the affected parents at all:

• RollupDispatcher.capture() runs before an update or delete, while the links still exist. It is the only chance to learn which parent a record is about to be deleted from or re-parented away from.
• dispatch() runs after commit and queues the work; the processor unions the captured pre-image with a fresh post-write resolution. That union is what makes a re-parent correct — a Deal moved from Company A to Company B leaves both totals wrong, and only the two images together name them both.
• Creates need no pre-image: a new record can only add itself to a parent.

The other direction — a link rewritten from the parent end

Both images above walk outward from a child, so neither can see the second way a link changes: editing the relation cell on the record that carries the rollup (setting a Company's "Deals", not a Deal's "Company"). No child record changes then, so a child-side resolution finds nothing and the total would simply never move.

The write therefore names those parents itself, in directlyAffected on the same job:

• the record being written, whenever the payload carried relations — on create as well, since a record born holding links has no total yet;
• any record a reverse-FK re-parent took a child away from. Setting contact.orders = [order-7] when order 7 belonged to contact 3 moves two totals, and contact 3 is reachable only through the FK the same statement is about to overwrite — so RecordRelationsService reads it first and reports it back as displacedParentIds. Junction relations never displace anyone; they only add rows.

bulkUpdate does not dispatch any of this yet: a bulk edit of children, or of their links, leaves parent totals stale until the next recompute touches them.

RollupComputeService aggregates in SQL (one statement per config per batch, LEFT JOIN so a parent whose children all vanished still gets rewritten to empty) and assembles the envelope in TypeScript. Null-handling and division-by-zero live in TypeScript deliberately — as nested SQL CASE trees the rules that decide what a user sees would not be unit-testable.

The parent's updated_at is not stamped. A total moving because a child changed is not an edit of the parent; stamping it would make every recompute look like a user edit in the audit trail and re-order every recency-sorted list.

Back-fill

Creating a rollup over populated data enqueues a back-fill that walks every existing parent in keyset pages. Without it the column reads null on every row until each parent happens to be touched — indistinguishable, to a user, from a rollup that does not work.

Telling the parent's watchers — data.rollup_updated

Because updated_at is deliberately left alone, nothing downstream can detect that a total moved. The recompute therefore reports what it wrote and the processor pushes it:

• RollupComputeService.recompute() returns the envelopes it wrote, assembled as it writes them. Re-reading afterwards instead would risk picking up a later recompute's value and attributing another change's total to this one.
• RollupEngineService groups them per parent record and resolves ids → slugs in one query (an internal id must never cross the wire, CLAUDE.md §4.2). A parent whose slug no longer resolves — deleted in the interim — is dropped.
• RecordsRollupProcessor emits one data.rollup_updated per parent object via DataEventsGateway. The broadcast never throws: the totals are already committed, and failing the job over a socket would retry the whole recompute.

Unlike data.lookup_stale, this event carries values, and a single room broadcast is enough to do it. A lookup value is per-reader — it mirrors another object and is resolved through the reader's rights there — while a rollup is aggregated once and stored on the parent's own column, so every reader of that record already receives this exact number from the HTTP read path. Broadcasting it is parity with the read contract, not a new disclosure.

Back-fill is deliberately silent: it rewrites every parent record, so relaying it would push the whole object down a socket one page at a time. The client's post-field-create refetch is the same data in one request.

Isolation

ISOLATION.md — Workspace Isolation Law

This file is the law for multi-tenant data isolation on the shared crm_hyper database. Read it before touching any DB access path, background worker, event listener, or migration that runs on hyper. The CLAUDE.md "Workspace Isolation" section is the quick reference; this is the full contract, the rationale, the honest trade-offs, and the checklists.

Companion law: AUTHORIZATION.md (who may act). This document is about which rows exist at all for a given workspace — a boundary enforced below authorization, by Postgres itself.

1. The model in one paragraph

On the shared crm_hyper DB, Postgres Row-Level Security (RLS) is the security boundary — not application code. Every workspace-scoped table has ENABLE + FORCE ROW LEVEL SECURITY with a ws_isolation policy keyed on a per-connection setting (app.workspace_id). The application connects as role crm_app (NOSUPERUSER, NOBYPASSRLS), so the policy always fires. A request or job becomes workspace-scoped only by pinning a connection — running SET app.workspace_id = N once and routing all queries through that connection. The value of N is always server-derived (from the JWT / job payload), and never accepted from the client.

policy ws_isolation:
  USING/WITH CHECK ( workspace_id = NULLIF(current_setting('app.workspace_id', true), '')::int )

This is fail-closed: forget to scope a query and you get zero rows (and writes throw), never another workspace's data. The previous model — manual WHERE workspace_id = ? at every call site — failed open: one missing filter leaked across workspaces. That inversion is the entire reason this model exists.

2. The three tenancy modes (know which you are in)

if (workspaceId) guards in shared helpers are fine — they no-op on main/dedicated and engage on hyper.

3. The pinning contract — THE rule you must follow

RLS only fires on a pinned connection. There is exactly one way to get DB access in any code path, and it is ContextAwareRepositoryProvider (repoProvider). It routes to the pinned QueryRunner when one exists, and to the normal pool otherwise.

3.1 Request-scoped code (controllers, normal services)

Pinning is automatic via WorkspacePinningInterceptor (src/common/interceptors/workspace-pinning.interceptor.ts). You do nothing except use repoProvider:

constructor(private readonly repoProvider: ContextAwareRepositoryProvider) {}
private get repo() { return this.repoProvider.getRepository(MyEntity); }

• Do NOT add manual workspace_id filters — they are redundant and signal a misunderstanding of the model. RLS already scopes it.
• Do NOT inject @InjectRepository(X) and use it for hyper-scoped tables — that repo is bound to the raw pool, not the pinned connection.

3.2 Non-HTTP code (Bull jobs, cron, WebSocket, event listeners)

There is no interceptor here — you pin explicitly. Two tools:

Bull processors — decorate the handler with @TenantScoped() (src/api/v1/tenant/scope/tenant-scoped.decorator.ts):

@TenantScoped()              // MUST be above @Process
@Process('my-job')
async handle(job: Job<MyJobData>) { /* repoProvider is pinned for the whole job */ }

Requirements:

1. The job payload type extends TenantJobData ({ tenantDatabase; workspaceId; requestId? }).
2. Every enqueue site populates tenantDatabase + workspaceId from the current context. A job queued without them dead-letters (fails closed) on hyper.

Everything else (cron, WS gateways, @OnEvent listeners) — wrap the body in TenantScope.runInScope(spec, fn):

await this.tenantScope.runInScope(
  {
    tenantDatabase,
    workspaceId: workspaceId ?? null,
    requestId: `my-task-${id}`,
  },
  async () => {
    /* repoProvider is pinned in here */
  },
);

For events: the emit site must carry tenantDatabase + workspaceId in the event payload, because the listener runs on a different stack with no ambient context.

3.3 The absolute prohibition

Never reach past repoProvider to the raw pool for a workspace-scoped query. These bypass the GUC → reads return zero rows, writes throw new row violates row-level security policy:

// ❌ BANNED on any hyper-reachable path
something.getDataSource().query(...)
something.getDataSource().getRepository(X)
something.getDataSource().transaction(...)
dataSource.getRepository(X)            // raw, unpinned
this.dataSource.manager.getRepository(X) // same pool, one hop further
new QueryRunner() / createQueryRunner() // fresh, unpinned

The .manager hop hid the worst instance of this: AuditPersistenceService held the ROOT DataSource, so every tenant's changelog row was written to the main DB. done_by then referenced an admin id belonging to a different person, Postgres rejected the row on FK_changelog_done_by, and the tenant's own history read back nearly empty. A singleton service that a per-tenant collaborator calls into must re-enter the tenant's scope — see AuditPersistenceService.runInTenantScope.

This is enforced mechanically by src/common/tests/rls-pinning.guardrail.spec.ts, which fails CI if a raw getDataSource().query/getRepository/transaction — with or without a .manager hop — appears in src/. If a site is genuinely main-DB-only (never runs on hyper), annotate the line with a rls-pinning-ignore comment and a one-line reason.

4. Carve-outs (the honest compromises)

Two things are NOT plain strict-RLS. Know exactly why.

4.1 admin — login-aware RLS

Login looks up admin by email before any workspace is known (findOneBy({ email })). A strict policy would return zero rows and break login. So admin uses a login-aware policy:

USING/WITH CHECK ( app.workspace_id IS NULL OR workspace_id = app.workspace_id )

• Unpinned (login): GUC is NULL → all admins visible → email lookup works.
• Pinned (authenticated request/job): strict → only this workspace's admins.

This closed the cross-workspace admin leak (a new workspace used to see other workspaces' admins). It is correct and proven, but it is the one table whose strictness depends on the request being pinned rather than being unconditional. Honest trade-off: the textbook-ideal design separates global auth identity (main DB) from scoped workspace membership, so no table needs the "NULL GUC ⇒ see all" escape hatch. We did not restructure that. Until we do, any new pre-auth lookup on a hyper table must use the login-aware policy, never strict — or login breaks.

4.2 permissions — global catalog, no RLS

permissions is a global, non-workspace catalog read before workspace context exists. It intentionally has no RLS. It holds no workspace data, so there is nothing to leak.

No other table gets a carve-out. Adding one is a security decision — it belongs in this file with its justification, or it does not happen.

5. Migrations that touch crm_hyper

• RLS/policy migrations are duplicated in src/database/migrations/ and src/database/migrations/tenant/ (the established convention), and guarded so they fire only on hyper:

  if (current_database() !== (process.env.HYPER_TENANT_DATABASE || 'crm_hyper'))
    return;

  They become no-ops on main and dedicated DBs. Follow this exact guard shape (see 1769200000001-EnableRowLevelSecurityOnSharedDb.ts and 1800000000000-CloseRlsGapsAndAdminLoginAwarePolicy.ts).

• Ordering law: enable RLS on a table only after every write path to it is pinned. Enabling RLS before the writers pin → inserts throw in production. Pinning ships first, the RLS migration follows.

• Migrations run as the postgres superuser, which is BYPASSRLS — so backfills and policy DDL work. Never grant BYPASSRLS to crm_app.

6. Checklists

Adding DB access to any service/worker:

• Use repoProvider — never raw getDataSource()/@InjectRepository for hyper tables.
• No manual workspace_id filter (RLS does it). Exception: documented carve-out tables.
• Non-HTTP path? It is pinned via @TenantScoped() or runInScope().
• New Bull job? Payload extends TenantJobData; every enqueue site sets tenantDatabase + workspaceId.
• New @OnEvent listener? Emit site carries tenantDatabase + workspaceId; listener wraps work in runInScope.
• Guardrail spec still passes.

Adding a new hyper table:

• Has a workspace_id INTEGER NOT NULL column.
• ENABLE + FORCE ROW LEVEL SECURITY + ws_isolation policy + GUC default, in a hyper-guarded migration, ordered after its writers pin.
• Not a pre-auth lookup (if it is, use the login-aware policy and document it in §4).

7. Verdict / known residual risk

This is the right architecture for a shared multi-tenant DB: DB-enforced, fail-closed, regression-guarded, and proven against live Postgres (src/common/tests/rls-policy-mechanism.spec.ts, test/hyper-tenant-rls.e2e-spec.ts).

The residual risks to keep watching:

1. Pinning surface area. RLS is unconditional, but pinning every non-HTTP path is a contract humans must honor. The guardrail catches raw bypasses; it cannot prove every job payload carries tenantDatabase. Treat a new unpinned worker as a P1.
2. The admin carve-out (§4.1) is pragmatic, not ideal. The clean fix is to separate global identity from scoped membership.
3. Shared-DB blast radius. One stray BYPASSRLS grant or superuser connection in a hot path defeats the boundary. Keep crm_app powerless.

Not implemented

• Comparators and sorting. FIELD_TYPE_OPERATORS['rollup'] is ["is_empty", "is_not_empty"]; > / < / between and ORDER BY need (col->>'value')::numeric in the filter builder and sort clause plus an expression index.
• Dashboard measures. AggregationQueryService aggregates the bare column, which is a hard error on JSONB; a rollup measure needs ->>'value' extraction and rollup added to NUMERIC_FIELD_TYPES.
• rollup_status: 'ok' | 'broken' on the read descriptor. The loader already drops unresolvable configs (which is what "broken" means); surfacing it alongside the stored options is the remaining step.
• A reconcile cron. Recompute is best-effort per write — a dropped job leaves a stale total with nothing to heal it.