Skip to content

Sensitive Data System

End-to-end model for sensitive-data visibility and field masking across the database, the service layer, the UI, and the approval pipeline.

The model is "G1": a single typed-rule table + one generic visibility predicate, no per-record-type helper chain. Whether a row is hidden or only its fields are masked is decided by which JSONB column of its rule you populate.

For step-by-step "I'm adding a table / a field" instructions see ADDING_SENSITIVE_DATA.md.

For the helper-function catalogue see database/VIEWS_AND_FUNCTIONS.md § Sensitive Data Helpers. For the permission keys and role bindings see PERMISSIONS_SYSTEM.md § Sensitive Data Permissions. For the accepted Supabase advisor warnings on every DEFINER helper see SUPABASE_ADVISOR_ACCEPTED_WARNINGS.md.

Overview

Some film-production records are operationally sensitive: a star's loan-out company, an above-the-line department head's salary line, an entire confidential budget tree, the bank details of a key supplier. The system needs to hide these from most users — including downstream artifacts like transactions, payments, approvals, attachments, and notifications tied to them — while letting a small set of producers and accountants see, edit, and toggle visibility.

Two dimensions of masking are supported, and both are managed through the same Manage Access dialog and the same sensitive_rules row:

  • Row-level restriction — the row disappears from list views, gets filtered out of joins, is invisible at the RLS layer, removed from approval routes, removed from notifications.
  • Field-level restriction — the row stays visible, but selected columns return NULL until the caller holds the right permission. Useful for "show me the supplier but hide their bank details."

A third option, cascade-only, lets you restrict child records of a parent (e.g. an entity's attachments) without restricting the parent itself.

Layers

The system spans four layers. Each one is independently testable, each one has a single responsibility:

  • Database — stores rules; gates RLS reads; resolves caller clearance; UPSERT / DELETE RPCs.
  • sensitive_rules, sensitive_fields
  • row_is_visible_to_caller, record_is_visible_to_user, mark_record_sensitive, unmark_record_sensitive
  • get_user_clearance, get_user_clearance_for_user
  • Masking views: v_entities, v_project_relationships, v_transactions, v_payments, v_budget_headers, v_budget_items + the approval views
  • Service — wraps the RPCs, fetches per-record required permissions, gates approval routing & notification dispatch by recipient / approver.
  • services/sensitive/base.ts, server.ts, actions.ts
  • services/user/base.ts — recordIsVisibleToUser, getUserClearanceForUser
  • services/approval/base.ts — clearance-filtered router + decision-time re-eval
  • Hooks — React Query reads of the rule + field catalogue + mark / unmark mutations.
  • hooks/queries/useSensitiveRulesQuery.ts
  • UI — the Manage Access dialog (3-mode picker, field toggles, cascade chips), trigger button, sensitive badge.
  • components/sensitive/MarkSensitiveDialog/*
  • components/sensitive/MarkUnmarkSensitiveButton
  • components/sensitive/SensitiveBadge

Storage: two reference tables

  • public.sensitive_rules — typed rule catalogue. One row per sensitive record. Surrogate id uuid PRIMARY KEY plus a UNIQUE (record_type, record_id) constraint that backs the polymorphic lookup pattern. Fields:
  • required_permissions text[] — row-level mask. Caller must hold AT LEAST ONE listed permission key to see the row. Empty = no row-level mask (rule only does field masking or cascade narrowing).
  • field_required_permissions jsonb — per-column mask, shape {"<column>": "<permission_key>"}. Empty = no field masks.
  • scope_filter jsonb — per-child cascade narrowing. Shape {"<child_table>": {"enabled": bool, "types"?: string[]}}. Example: {"entity_attachments": {"enabled": true, "types": ["bank_details"]}} on an entity rule restricts only the entity's bank-details attachments without touching the entity row itself.
  • organisation_id, project_id — denormalised at INSERT time so visibility resolution doesn't have to join back to the source. Immutable. project_id is NULL only for entity rules (organisation-scoped).
  • Audit pair: created_by_user_id / created_at / updated_by_user_id / updated_at. The standard update_updated_at_column trigger maintains updated_at.
  • public.sensitive_fields — catalogue of which columns are eligible for field-level restriction. Drives the Manage Access dialog's per-field toggle list AND validates writes to sensitive_rules.field_required_permissions via the validate_sensitive_rule_fields BEFORE INSERT/UPDATE trigger. Today seeded with entity email / phone_number / payment_details + project_relationship payment_details. Adding a new restrictable column = INSERT a row + add a CASE block to the relevant masking view (see ADDING_SENSITIVE_DATA.md).

Visibility predicate (the only helper)

public.row_is_visible_to_caller(
  p_own_type text,
  p_own_id uuid,
  p_ancestors jsonb DEFAULT '[]'::jsonb,
  p_user_id uuid DEFAULT NULL,
  p_own_subtype text DEFAULT NULL
) RETURNS boolean

Returns TRUE unless a sensitive_rules entry with non-empty required_permissions matches the row itself or one of its ancestors AND the caller's clearance for the rule's own scope does not contain the required key. The rule's denormalised organisation_id / project_id decides which scope to consult — caller doesn't need to know.

  • p_own_type, p_own_id — the row being checked (e.g. 'transactions', transaction uuid).
  • p_ancestors — JSONB array of {"type": "<table>", "id": "<uuid>"} entries for parent FKs the row inherits from. A transaction passes 6 ancestors (4 PR FKs + 2 entity FKs); a budget header passes '[]' (recursive header walks happen in the rule's own scope, not the predicate's).
  • p_user_id — defaults NULL → use auth.uid() via get_user_clearance. When non-NULL → use get_user_clearance_for_user(p_user_id, ...). Lets service_role callers (notification gates, digest cron) pass the recipient's identity explicitly.
  • p_own_subtype — when the rule's scope_filter narrows by subtype (transaction type, attachment type), the predicate matches against this column. Callers pass the row's own subtype column value (e.g. t.type::text, attachment_type::text).

MUST be DEFINER: called from RLS policies on tables that are themselves RLS-gated. INVOKER would tunnel through sensitive_rules's own SELECT policy creating recursion.

Empty required_permissions semantics depend on how the rule matched:

  • Own-row match — the rule never hides the row. The masking view applies field_required_permissions separately to NULL-out specific columns. scope_filter has no effect on the row itself, only on children walking up to it as an ancestor.
  • Ancestor match via scope_filter (cascade-only rule) — the child row IS hidden unless the caller holds the rule's scope :view key (sensitive_data:project:view for project-scoped rules, sensitive_data:organisation:view for entity-scoped). This is how "show the parent to everyone, restrict matching children" works — e.g. mark a PR with {"transactions": {"enabled": true, "types": ["Invoice"]}} and only callers with :project:view see that PR's invoices; the PR itself stays visible.
  • Ancestor match without a scope_filter entry for this child — the rule has no intent for this child type. No effect.

Subject self-visibility

A sensitivity rule is a no-op for its own subject. The person a record is about keeps exactly what their ordinary permissions already allow — the rule hides nothing extra from them and grants nothing extra. "About" means the row itself, or any ancestor in its sensitivity-ancestor list, resolves to an entities row whose user_id matches the evaluated user (directly, or via project_relationships.entity_id).

This applies to both dimensions:

  • Row visibility — row_is_visible_to_caller ends with a subject self-exemption conjunct: even when a matching rule's clearance check fails, the row stays visible if the row (or an ancestor) resolves to an entity linked to the evaluated user. Four probes cover every shape: own entity row, own relationship row, an entity ancestor, a relationship ancestor. The probes reference only the function arguments, so they evaluate once per call and only after a matching rule with failed clearance is reached (the common no-rule path never touches them).
  • Field masks — the DEFINER masked-fields helpers apply the same self-arm: the subject sees their own email / phone_number / payment_details UNMASKED, and no redacted marker is emitted for their own fields. caller_can_access_address's two payment_details arms carry the same self-arm so the subject can still resolve their own bank address.

Badge metadata is never self-hidden either — but for the opposite reason. The subject still SEES that they are marked sensitive and what the rule requires: get_record_effective_required_permissions is deliberately caller-INDEPENDENT (no self-exemption), so the badge and its required-permission keys are metadata that everyone, including the subject, can read. Only the row/field values are self-exempted.

Loan-out companies are NOT exempted (known gap). A Business entity acting as a personal-service company has user_id IS NULL, so it never matches the subject probe. Extending the exemption to a person via their loan-out company would ride project_relationships.loan_out_via_project_relationship_id — that is the seam, but it is a product decision that has not been taken.

entities.user_id is therefore a SECURITY BOUNDARY. Because the whole self-exemption keys off it, user_id must only ever be written by trusted server-side paths — it is excluded from the authenticated INSERT/UPDATE column grants, so a client cannot self-link an entity to another user and inherit their visibility. See § "entities.user_id write boundary" below.

Read side: RLS + masking views

RLS pattern

Every gateable table's SELECT policy AND-chains its existing org/project membership check with a single row_is_visible_to_caller(<own_type>, <pk>, <ancestors>) call. The pattern looks identical for every table:

USING (
  (SELECT auth.uid()) IS NOT NULL
  AND <existing org/project membership check>
  AND public.row_is_visible_to_caller(
    'transactions', id,
    jsonb_build_array(
      jsonb_build_object('type', 'entities',              'id', entity_id),
      jsonb_build_object('type', 'project_relationships', 'id', expense_project_relationship_id)
      -- ...one entry per ancestor FK
    ),
    NULL,
    t.type::text   -- pass own subtype if the row has a type column that scope_filter narrows on
  )
)

Adding a new sensitive-eligible table = one new RLS policy with this template, filled with that table's ancestor list. No new helpers, no chain rewrite. Adding a new ancestor type = update one policy's ancestor list. The single row_is_visible_to_caller function never changes.

The transactions SELECT policy carries ONE further AND conjunct beyond this template — the pre-pipeline window gate — because a transaction's sensitivity rules do not exist during ingest. See § "Pre-pipeline window gate" under Auto-applied marks.

Restriction-set twins: the five list/count-critical tables

transactions, entities, project_relationships, budget_items and budget_headers no longer use the per-row template above. Their SELECT policies were the measured hot paths (a per-row DEFINER call across a 505-row transactions count, a 719-entity badge sweep, the budget read paths), so each replaced ONLY its row_is_visible_to_caller(...) conjunct with the bounded negated set form:

AND NOT (id = ANY (SELECT unnest(private.caller_restricted_transaction_ids())))

Five twins serve six rewritten SELECT policies — budget_item_daily_allocations gates on its PARENT item, reusing the budget-item twin against its own budget_item_id column:

  • private.caller_restricted_transaction_ids() — transactions
  • private.caller_restricted_entity_ids() — entities
  • private.caller_restricted_relationship_ids_cascading() — project_relationships
  • private.caller_restricted_budget_item_ids() — budget_items AND budget_item_daily_allocations
  • private.caller_restricted_budget_header_ids() — budget_headers

Each is an exact set-complement of row_is_visible_to_caller for its policy's argument shape, proven row-by-row against it under every persona. Every other conjunct (membership, creator, the transactions window gate) is byte-preserved. Full contracts in docs/architecture/database/VIEWS_AND_FUNCTIONS.md § "Restriction-set twins".

The two budget twins derive purely from sensitive_rules + get_user_clearance, with no join to the gated table at all. Their consuming policies pass '[]' ancestors and no subtype, so only the own-row arm of row_is_visible_to_caller is reachable: the cascade and scope_filter arms never fire, and rules with empty required_permissions are field-mask-only and never hide. With no reachable self-exemption arm there is no live row to read, which is what lets them skip the base-table join the other three twins need.

Two things to know before touching them:

  • The per-row template remains correct and in use for the other ~15 gated tables, and for the two entities/project_relationships UPDATE policies (single-row writes — per-row IS the right shape there). Only convert a table when it demonstrably sweeps many rows.
  • A twin that copies the own-row-only reference primitive's cardinality(required_permissions) > 0 prefilter onto ANCESTOR rules fails OPEN, because cascade-only rules (empty permissions + an enabled scope_filter) block through the sensitive_data:{project,organisation}:view fallback key. Such rules exist in production.

Blast radius of the set form: a twin evaluates, once per statement, every rule joined to a live row it can reach — across tenants, not only the tenant whose rows the query touches. So a malformed value on ANY reachable relevant rule would fail every caller's statement on that table. Unreachable rules are never evaluated; the entities and budget twins never evaluate scope_filter at all.

Malformed shapes are now unrepresentable at rest rather than an accepted live risk: migration 20260727215153 adds the validating CHECK constraint sensitive_rules_scope_filter_shape (backed by the IMMUTABLE private.is_valid_scope_filter_shape), which requires scope_filter to be NULL or an object whose every value is an object with an optional array types and an optional boolean enabled. It was added as a plain validating constraint (not NOT VALID) after verifying zero violating rows in local, staging and production, so it covers pre-existing rows too. This sorts BEFORE the twins: a bad write is rejected at its source, so no reader ever has to survive one.

NULL-uid divergence (unreachable): the twins return {} under a NULL-uid context where the per-row function fails closed. Conjunct 1 (auth.uid() IS NOT NULL) gates every consuming policy, all are TO authenticated, and service_role holds no EXECUTE — verified by actual policy enforcement (zero rows on the gated tables with the claims GUC NULL), not by a recomposed predicate.

When retrofitting the gate onto an EXISTING policy, preserve the exact predicate that was there — do not downgrade a permission check to a bare project/org membership check. The sensitivity gate is defence-in-depth; it only ever restricts further. It must NOT become the sole gate. DEV-720: a cascade migration recreated form_submissions's SELECT policy with v_user_accessible_projects (project access) instead of the original v_user_project_permissions … 'form:view_submission' predicate, silently exposing raw KYC data to anyone on the project. When you DROP … ; CREATE … a policy, diff the new USING clause against the live one (pg_get_expr(polqual, polrelid)) and confirm no permission predicate was dropped.

Masking views

v_entities, v_project_relationships, v_transactions, v_payments, v_budget_headers, v_budget_items plus the three approval views (v_user_pending_approvals, v_user_approval_history, v_user_approval_queries) and v_project_approval_requests all JOIN sensitive_rules LEFT JOIN style and:

  • Expose sensitive_required_permissions — the UNION of the effective required-permission keys gating the row (own + ancestor rules) so callers can decide UI affordances without re-implementing the cascade. For v_transactions and v_payments this column is computed by get_record_effective_required_permissions(record_type, record_id) (a DEFINER helper mirroring row_is_visible_to_caller's matching), so it correctly includes cascade-only ancestor rules — a parent marked sensitive for its children via scope_filter with empty required_permissions — which resolve to the rule's scope :view key. The other masking views compute the column inline from required_permissions, which is correct for them because their rows are never cascade children (entities/PRs are cascade parents, budgets are not cascade-eligible). Historically all views used the inline unnest(required_permissions) form, which silently dropped cascade-only rules — a transaction hidden by a cascade-only PR rule reported '{}' and so bypassed approval-router sensitivity gating; the helper fixes that.
  • Expose sensitive_field_required_permissions for source tables that carry field-level rules.
  • Expose sensitive_field_redacted — per-caller subset of sensitive_field_required_permissions containing only the keys the caller LACKS at the relevant scope AND whose underlying column has a value on this row. This is the authoritative client-side signal for "this field is hidden FROM ME on THIS row" — the FE wraps cells with <MaskedValue redactedFields={row.sensitive_field_redacted} /> and renders a "Restricted" placeholder when the field name is present, an empty placeholder otherwise. The value-presence check is what stops "Restricted" from rendering on rows where the column is genuinely empty (e.g. an entity with no phone). v_project_relationships also exposes entity_sensitive_field_redacted for the parent-entity column derivations.
  • Expose has_sensitive_rule boolean = own row only (a rule exists FOR this row). The <SensitiveBadge /> gate depends on the record type: entities / project_relationships / budgets badge on has_sensitive_rule (they are cascade parents or non-cascade, so they never inherit); transactions / payments badge on isRecordSensitive(row) (utils/sensitive.ts) = has_sensitive_rule || sensitive_required_permissions.length > 0, so a row sensitive ONLY by inheriting a parent's cascade rule (has_sensitive_rule=false, non-empty sensitive_required_permissions) still shows the badge. isSensitivityInherited(row) (own=false + perms non-empty) additionally HIDES the per-record Manage Access control — access is managed on the parent. Every transaction/payment/budget table cell uses the shared components/sensitive/SensitiveCell wrapper (record + testId + children); inline card sites (reconciliation drawer, report drill) call isRecordSensitive + SensitiveBadge directly. Ancestor visibility / masking continues to flow through sensitive_required_permissions and the masked-fields helpers. Badge metadata columns (has_sensitive_rule, sensitive_required_permissions, sensitive_field_required_permissions, sensitive_auto_applied) are NEVER masked — even the subject of a rule sees that they are marked and what the rule requires; only the row/field values are hidden.
  • Return restrictable columns from a DEFINER masked-projection chokepoint helper (private.entity_masked_fields / private.project_relationship_masked_fields), not from an inline CASE. Each helper compares the column's required key against get_user_clearance(...) and returns NULL when the caller lacks it AND the caller is not the subject (see § Subject self-visibility). v_available_approvers and v_compliance_capture read the already-masked contact fields from v_entities rather than the raw base table.
  • Bulk (once-per-statement) resolution. A per-PK LATERAL helper is a SECURITY DEFINER RETURNS TABLE SQL function — Postgres never inlines it — so a masking view re-executes the entire clearance/rule machinery once per reachable row. On the payments list the planner materialises the whole visible-entity set, so the mask helpers dominated runtime. v_entities / v_project_relationships therefore JOIN the zero-argument bulk twins private.entity_masked_fields_all() / private.project_relationship_masked_fields_all() (ON <twin>.<id> = <base>.id) instead of LEFT JOIN LATERAL-ing the per-PK helper. The twins compute the SAME per-row masking over ALL rows in one pass, evaluating get_user_clearance once per scope (organisation for entities / project for relationships) via a WITH … AS MATERIALIZED CTE, fail-closed with COALESCE(clearance, '{}'). Per-row output for any id under any claims context is byte-identical to the per-PK helper — the per-PK helper is the parity oracle (no production view/RPC call sites remain; retained for genuine single-row callers). The bulk FunctionScan is unparameterized, so a nested-loop rescan only rewinds its tuplestore (never re-runs the mask body). Trust model: the twins are enumerable by a hypothetical SQL-level authenticated caller — a documented, accepted widening vs the per-PK "guess a UUID" surface, within the same private-schema boundary as caller_restricted_project_relationship_ids (private is not a PostgREST-exposed schema; EXECUTE revoked from PUBLIC/anon). The deployed system offers authenticated users no SQL path. See VIEWS_AND_FUNCTIONS.md § "Field-masking chokepoint helpers".

The masking is enforced in the DEFINER helpers, not in the views. The value columns (entities.email / phone_number / payment_details; project_relationships.payment_details) are column-grant-revoked from the authenticated role on the base tables — the raw columns are simply not readable via the Data API — so the masking views (through the helpers) are the ONLY authenticated read path, and the mask cannot be bypassed by selecting the base table directly. project_relationships.payment_details is SELECT-revoked but still writable (INSERT/UPDATE cover it). This is fail-closed: a future maskable column must be OMITTED from the base-table authenticated SELECT column-grant list and routed through the helper + view; a future non-maskable column must be ADDED to the SELECT grant list or it is unreadable. entities.search_query (the full-text vector) no longer contains any maskable value — it was reduced to non-sensitive identity fields only, so a row-visible-but-uncleared caller cannot infer a masked value by probing idx_entities_search.

The project_relationship_masked_fields helper also returns a per-field raw_present boolean map (booleans only, no values) so the derived entity_bank_* columns preserve their exact fallback semantics: a relationship that HAS a raw bank field but has it masked reads NULL and does NOT fall through to the entity-level value; a relationship that genuinely lacks the field falls through to the (already-masked) entity value.

Masked-NULL write-back protection. Because the relationship editor seeds its form from the masking view, an uncleared (but row-visible) editor reads payment_details as NULL. updateProjectRelationship OVERWRITES the whole payment_details JSONB (it does not deep-merge), so blindly sending the field on every save would silently destroy stored bank details. useRelationshipForm therefore includes payment_details in the update ONLY when the field is actually dirty (any dotted payment_details.* react-hook-form dirty key, or a truthy leaf in a nested payment_details value); when untouched it omits the key entirely so the column is left intact. A user deliberately clearing their bank details still dirties the field, so that legitimate write is preserved.

Derived columns must carry the source column's mask — in EVERY view that computes them. Any column computed from a maskable column (the entity_bank_* derivations of payment_details, the bank-address resolutions v_entities.bank_address / v_project_relationships.entity_bank_address / v_payments.entity_bank_address / v_payment_details.bank_address) must resolve through the mask-aware expression or an already-masked upstream view column — never the raw JSONB. A downstream view that reads pr.payment_details -> 'bank' ->> … directly with only a row-visibility gate (e.g. caller_can_access_address) recreates the bypass that v_payment_details.bank_address shipped with: a row-visible but uncleared caller read the bank address that every other surface masked. Fallback semantics for PR-level overrides are also part of the contract: a PR value that is present but masked yields NULL and must NOT fall back to the entity-level value. Behavioural persona pins live in packages/database/supabase/tests/08_payment_address_masking.sql.

Masking a derived value in views is not enough when the value lives in its own directly-readable table. Bank addresses are addresses rows, and authenticated users can SELECT public.addresses directly — so the field-mask must ALSO be enforced in that table's gate (caller_can_access_address's payment_details arms), or an uncleared caller simply reads the masked rows off the table while every view dutifully returns NULL. When masking a new derived value, enumerate every read path to the underlying row (views, direct table reads, RPCs, embeds), not just the views that render it.

Views are security_invoker=on so source-table RLS still applies; the view's job is only to NULL-out restricted columns and expose the G1 surface.

The mapping from source table → masking view lives in TABLE_TO_VIEW_MAPPING so any caller polymorphic over record_type (notification dispatch, approval router, attachment polymorphic refs) can translate without a hidden lookup.

Write side: mark / unmark

Two RPCs replace the old "flip is_sensitive on the source row" pattern:

public.mark_record_sensitive(
  p_record_type text,
  p_record_id uuid,
  p_required_permissions text[] DEFAULT NULL,
  p_field_required_permissions jsonb DEFAULT '{}',
  p_scope_filter jsonb DEFAULT '{}'
) RETURNS sensitive_rules

public.unmark_record_sensitive(p_record_type text, p_record_id uuid) RETURNS boolean

Both are INVOKER. RLS on sensitive_rules enforces caller permission inline by calling get_user_clearance('organisation', organisation_id) (for entity rules) or get_user_clearance('project', project_id) (for everything else) and asserting that the resulting array contains sensitive_data:<scope>:mark. The CHECK constraint on sensitive_rules rejects rules where required_permissions is empty AND field_required_permissions is empty AND scope_filter is empty (nonsensical rule).

mark_record_sensitive is an UPSERT — calling it again on the same record updates the existing rule's required_permissions / field_required_permissions / scope_filter in place.

Auto-applied marks (transaction ingest)

sensitive_rules.auto_applied boolean (default false) distinguishes rules a user set manually via Manage Access (false) from rules the system applied automatically during transaction ingest (true).

Opting in is per transaction type: projects.metadata.transaction.<type key>.entities_created_as_sensitive (the six direction-aware fallback keys — see PROJECTS.md). When the flag is on for the processed transaction's type, an entity that was created fresh during that transaction's ingest is marked sensitive with sensitive_data:organisation:view, and a project relationship created fresh alongside it (including a fresh relationship on a matched pre-existing entity) is marked with sensitive_data:project:view. Their transactions then inherit sensitivity through the normal ancestor cascade. Matched pre-existing records are never auto-marked — they were already vetted and keep whatever access they had.

Timing — the mark lands at type discovery, not at record creation. Records are created by the transaction_entity_matching step (execution group 5), but the transaction's type is only determined by transaction_type_discovery (group 6), so a per-type gate cannot fire at creation. Provenance is stamped on the created record itself: when ingest creates an entity or project relationship, it adds created_by_processing_job_id to that record's own metadata JSONB (alongside the existing {source, created_by_id} convention; matched pre-existing records are never stamped). processTypeDiscovery then treats the transaction's direction-appropriate entity/relationship as created-fresh iff the stamp equals its own job id, evaluates the gate via getTransactionFallbackKey(type, direction) (a missing/invalid AI type gates as Unknown; a direction-less invoice fails safe — it marks if either invoice key is enabled), and applies the marks. Properties: restart-proof (the stamp survives a full job restart, unlike step result_data), reprocess-safe (a reprocess runs under a new job id, so previously-created records aren't re-treated as fresh and a lifted mark stays lifted), and dedup-safe (a record created moments earlier by a different transaction's job carries that job's id). One accepted delta versus marking at creation: created records are unmarked until type discovery completes.

This mark does not go through mark_record_sensitive. Ingest runs under the service_role client (processing/context.ts) — it has no auth.uid(), so the RPC's RLS-based permission check + created_by_user_id default don't apply. Instead services/sensitive/base.autoMarkRecordSensitive inserts the sensitive_rules row directly with an explicit actorUserId (the transaction's created_by_user_id) and auto_applied = true. It no-ops when a rule already exists for the record, so it never overwrites a manual mark and stays idempotent on re-processing.

The three masking views expose the flag so the UI can offer a quick-lift affordance: v_entities / v_project_relationships surface their own rule's auto_applied (sensitive_auto_applied, plus entity_sensitive_auto_applied on the PR view for the joined entity); v_transactions.sensitive_auto_applied is TRUE when any of the transaction's project_relationship / entity ancestor rules is auto-applied. Dense list rows (transactions, payments, project relationships, entities) render the badge icon-only (a lock + tooltip via SensitiveCell / iconOnly); detail headers render the full "Sensitive (auto)" badge. The transaction view also shows a "Not sensitive" button (LiftAutoSensitiveButton) that lifts the auto-applied rules on the transaction's ancestors — but only those the caller can act on (project relationship rules need sensitive_data:project:mark, the org-scoped entity rule needs sensitive_data:organisation:mark); rules the caller can't lift, and any manual restriction, are left untouched.

Pre-pipeline window gate (interim visibility before the marks land)

The auto-mark lands only at type discovery (group 6). Between ingest and that point a transaction's status walks the pre-pipeline window Received → Processing → On Hold, during which the eventual sensitivity rules do not exist yet — so row_is_visible_to_caller finds no candidate rule and would return TRUE for everyone. That briefly exposes a row that is about to become sensitive.

The transactions SELECT policy closes this window with an extra conjunct. While a transaction is in the pre-pipeline window and its project has any Auto-Sensitive flag enabled, the row is SELECT-visible only to callers who hold both clearances the eventual rule pair will demand:

  • sensitive_data:organisation:view — the future org-scoped entity rule's clearance.
  • sensitive_data:project:view — the future project-scoped relationship rule's clearance.

Both are required because row_is_visible_to_caller ANDs every matching rule, so the interim gate mirrors that AND. It is fail-closed: the creator is not exempt (unlike the settled-status membership disjunct), and a NULL-status row on an Auto-Sensitive project is hidden from callers lacking both clearances. Over-restriction self-heals: once entity matching + type discovery write the real rules the transaction leaves the window (In Approval/Approved/…), the window conjunct is bypassed, and visibility is governed by the actual rules through the normal ancestor cascade.

The project set is supplied by private.auto_sensitive_project_ids() (see VIEWS_AND_FUNCTIONS.md) and both clearance checks use the bounded caller_accessible_project_ids primitive — the org call with cascade=true (the org permission is organisation-scoped and must expand org-scope grants to the project), the project call with cascade=false (project-scoped). All three list read paths (base transactions, v_transactions, query_transactions) route through this one policy.

Processing jobs inherit this gate. A transaction hidden by the window gate (or by any sensitivity rule) used to remain observable indirectly: the processing_jobs / processing_job_steps SELECT policies gated only on project permission and job authorship, so an uncleared caller still received job metadata, progress percentages and step counts for the very rows this gate hides — and the window statuses are exactly the ones jobs run against. The processing_jobs SELECT policy now carries a top-level record-visibility conjunct (CASE processable_type WHEN 'transactions' THEN EXISTS (SELECT 1 FROM public.transactions t WHERE t.id = processable_id) ELSE false END) whose inner reference is RLS-filtered for the caller, so it inherits this gate and the restriction twin with no restatement; steps follow via their parent-job EXISTS. The creator is not exempt, matching this gate's own rule. This replaced a client-side compensation (dropJobsWithoutRelatedItem in ProcessingStatusBar) that filtered such jobs out of the rendered list after the payload had already reached the browser; that flag and its filter have been removed, since the database now never emits such a row. Full contract: database/AI_PROCESSING.md § Processing Jobs.

entities.user_id write boundary

Because subject self-visibility keys entirely off entities.user_id, the column is a security boundary: a client that could set an entity's user_id to another user could inherit that user's sensitivity self-exemption. It is therefore write-restricted:

  • Excluded from the authenticated INSERT/UPDATE column grants on the base table, so no Data-API write path can set it.
  • Absent from every public create/update DTO. entity.schema.ts drops user_id from the create/update person/business schemas; updateEntityAction strips any client-supplied user_id before it reaches the service; public entity DTOs no longer carry it.

The link is written only by trusted server-side paths, all via the base primitive entity/base.linkEntityUser (writes ONLY user_id, optional compare-and-set guard on the current link) run under a service-role context:

  • entity/server.createOwnUserEntity — creates a Person entity for the context user and stamps user_id from ctx().userId (never caller input), after asserting org membership under the caller's RLS client first. Used by createProject and createOrganisation when the acting user has no entity in the org yet, and by the invite flows.
  • entity/server.unlinkEntityUser — service-role, expected-current-user-guarded. Invite cancellation unlinks the entity FIRST, then deactivates the user (never the reverse — an unlink failure aborts before deactivation, leaving both sides intact for retry).
  • The phone-change re-link and ensureEntityHasUser paths route their user_id writes through linkEntityUser as well, inside their existing service-role contexts.

Permissions

Four scope-action keys plus two field-category keys:

Key Action
sensitive_data:organisation:view See sensitive entities in an organisation
sensitive_data:organisation:mark Mark / unmark entities sensitive
sensitive_data:project:view See sensitive project-scoped records (PRs, transactions, payments, budget items)
sensitive_data:project:mark Mark / unmark project-scoped records sensitive
sensitive_data:view_pii Unmask field-level PII (email, phone) — implied by :view
sensitive_data:view_payment_details Unmask field-level payment details — implied by :view

:mark implies :view and the field-category keys. Implies are flattened at grant time by the Phase 1 migration's inline INSERT INTO permission_role_links ... ON CONFLICT DO NOTHING (no closure-walk at query time). Roles Organisation Owner / Project Owner / Project Admin carry the full set.

Service layer

Three small, single-responsibility files:

services/sensitive/base.ts

Direct calls to the RPCs + a polymorphic read helper. No ServiceResponse envelope — that lives in server.ts. Pure CRUD.

  • getSensitiveRule(recordType, recordId) → findOne against sensitive_rules.
  • getSensitiveFieldsForRecordType(recordType) → findMany against sensitive_fields, ordered by (category, display_order).
  • markRecordSensitive(input) / unmarkRecordSensitive(recordType, recordId) → callFunction against the two RPCs.
  • getRecordRequiredPermissions(recordType, recordId) → polymorphic dispatcher reading sensitive_required_permissions from the right masking view via TABLE_TO_VIEW_MAPPING. Used by approval routing + notification gates that need the effective gate for a record without re-implementing the cascade.

services/sensitive/server.ts

wrapService envelopes around every base.ts function (uniform ServiceResponse<T> + consistent info/error logging). Never reach into base.ts from a server action — go through server.ts.

services/sensitive/actions.ts

Thin 'use server' boundary over server.ts. UI mutations call these via the React Query hooks. Suffix matches the codebase convention (getSensitiveRuleAction, markRecordSensitiveAction, etc.).

services/user/base.ts

Two helpers used by service-role gates:

  • recordIsVisibleToUser(userId, recordType, recordId) → wraps record_is_visible_to_user, the cascade-aware DEFINER helper that resolves the record's ancestors inside the DB and evaluates each rule at its own scope (org rule → org-scope clearance, project rule → project-scope), identical to RLS. Fail-closed. This is also the approval router's "clearance" primitive — an approver is cleared for a sensitive record iff they can see it. (The earlier form called row_is_visible_to_caller with an EMPTY ancestor list, so cascade-only sensitivity was invisible to it.)
  • getUserClearanceForUser(userId, scopeType, scopeId) → wraps get_user_clearance_for_user. Returns [] when the user has nothing at that scope (treat as "filter this user out"). Single-scope by design — do NOT use it to gate a multi-scope rule set; that is recordIsVisibleToUser's job.

Hooks layer

hooks/queries/useSensitiveRulesQuery.ts exposes:

  • useSensitiveRuleQuery(recordType, recordId) — single rule. Cached on (recordType, recordId); shared between the dialog and the badge.
  • useSensitiveFieldsQuery(recordType) — field catalogue for one record_type. Cached per recordType so opening the dialog on multiple entities doesn't re-fetch.
  • useMarkRecordSensitiveMutation() — UPSERT via mark_record_sensitive. onSuccess invalidates (a) the rule cache for the (recordType, recordId) pair and (b) the source-table cache so masking-view-derived has_sensitive_rule / sensitive_required_permissions re-fetch. invalidateSourceTableCache is a small switch keyed by recordType.
  • useUnmarkRecordSensitiveMutation() — DELETE counterpart. Same invalidation set.

UI layer

The Manage Access dialog (components/sensitive/MarkSensitiveDialog)

Generic dialog covering every sensitive-eligible source record_type. Three modes:

  • Don't restrict — required_permissions = [], field_required_permissions = {}. Cascade rules to specific children via scope_filter without restricting the parent row.
  • Restrict access — required_permissions = [scope_key]. Row is hidden from list views / joins for callers without the key; cascade is implicit and full.
  • Restrict specific fields — required_permissions = [], field_required_permissions populated. Row stays visible; selected columns masked. Card only shown when useSensitiveFieldsQuery returns at least one row for the record_type (loading-aware to avoid first-render flicker).

Composition:

  • ModeOption — one of the three radio-style cards in the mode picker. Matches the selectable-card pattern from EntityTypeSelector / PaymentMethodTypeSelector / etc.
  • FieldRow — one field's row in the "Which fields to restrict" section. Shows label, column name, category badge (PII / Payment Details), permission badge with tooltip, switch. Active row gets a primary-coloured left bar + primary-tinted label.
  • CascadeChildBlock — one cascade-child block with subtype chip filter. Active block gets a primary-coloured border + primary-tinted text.
  • UnmarkConfirmDialog — destructive confirmation rendered when the user clicks "Lift restriction".

Catalogues that drive the UI:

  • SENSITIVE_CASCADE_CATALOG — Record<DatabaseTable, SensitiveCascadeChild[]>. Per-source-record-type list of children that can be cascaded to + their subtype options. Entry exists → cascade section shows; no entry → hidden.
  • SENSITIVE_VIEW_PERMISSION_BY_RECORD_TYPE — Record<DatabaseTable, string>. The single permission key the "Restrict access" mode writes for each source record_type (e.g. entities → sensitive_data:organisation:view).
  • FIELD_CATEGORY_LABEL_OVERRIDES — override map for sensitive_fields.category values where stringToLabel doesn't produce the desired capitalisation (e.g. pii → PII).
  • SENSITIVE_FIELD_CATEGORY_ICONS — Record<string, LucideIcon> for the per-category icon shown on the field-row Type badge. Falls back to Tag for unregistered categories.

Shared building blocks

  • components/permissions/PermissionBadge — inline pill that surfaces a permission's friendly name with a tooltip containing the raw key + description. Used anywhere the app shows "this permission gates X."
  • components/permissions/PermissionDisplay — thin wrapper that adds TooltipProvider so callers can drop a single badge into any layout.
  • components/sensitive/MarkUnmarkSensitiveButton — single "Manage Access" button (variant toggles between filled and outlined based on whether a rule already exists; label is constant).
  • components/sensitive/SensitiveBadge — small inline "Sensitive" badge for list/detail views. Gate it via isRecordSensitive for transactions/payments (own OR inherited) and via has_sensitive_rule for entities/PRs/budgets (own only). Prefer the components/sensitive/SensitiveCell wrapper for table cells.

Where the trigger is wired

The Manage Access button is rendered next to the existing edit / archive actions in:

Each caller gates rendering on a canMarkSensitive prop derived from the caller's permission check.

Approval routing & decision-time re-evaluation

The approval router in services/approval/base.ts is sensitivity-aware:

At submission (submitForApproval)

  1. Resolve the record's sensitive_required_permissions via getRecordRequiredPermissions(recordType, recordId).
  2. Walk the configured tiers. For each tier, fetch every approver's clearance via getUserClearanceForUser(user_id, 'project', projectId) and filter out approvers who don't hold every key in required_permissions. Entity-only approvers (no user_id) are dropped — they have no clearance to evaluate.
  3. Tiers with zero cleared approvers are stamped as Skipped (condition_met: true, status: Skipped, with the audit trail recording the sensitivity cause).
  4. The approval_request is anchored on the first tier that still has at least one cleared approver. Only cleared approvers in each routable tier get PENDING instances.
  5. If no tier is routable, the router falls through to submitSoftApproval with a sensitivityFallback hint:
  6. Soft approvers are filtered by the same clearance rule.
  7. At least one cleared soft approver → request becomes a soft approval routed to that subset.
  8. Zero cleared soft approvers → request is auto-approved, with the reason text surfacing the routing failure.

At decision (submitApprovalDecision)

When the current tier completes (last approver acts), before activating the next tier:

  1. Re-fetch the record's sensitive_required_permissions (clearance + record state may have shifted since submission).
  2. For each pending instance in the upcoming tiers:
  3. No linked user → skip with reason 'Lost required sensitive-data clearance (no linked user)'.
  4. User no longer holds the required keys → skip with reason 'Lost required sensitive-data clearance'.
  5. Surviving instances activate normally.
  6. If every upcoming tier loses its last cleared approver, the request auto-approves with a final_decision_note recording the bypass.

The clearance lookups are memoised per (user_id) within the call so a single submission / decision never re-queries the RPC for the same user twice.

Service-role notification gates

services/transaction/server.ts (digest cron) and services/approval/server.ts (per-event notification sends) run under service_role and bypass RLS. They gate notifications by the recipient's clearance with the 4-arg form of the visibility predicate:

const visible = await recordIsVisibleToUser(
  recipientUserId,
  recordType,
  recordId
);
if (!visible) continue;

The DEFINER body resolves the rule's own scope from sensitive_rules.organisation_id / project_id and computes visibility per recipient. Fail-closed: a missed RPC call drops the notification rather than leaking a sensitive record.

Payment scheduling: the payee-visibility asymmetry

Scheduling a payment reads one record (a transaction) and writes another (a payment) whose payee identity is a DIFFERENT row — and the two do not share a visibility verdict. Three app-layer mechanisms close the gap.

1. A relationship ancestor never recurses into its entity

row_is_visible_to_caller matches only the LITERAL ancestor ids it is handed. A reimbursement transaction's ancestors are the supplier entity, the customer entity, and the four relationship FKs — the reimbursed person's entity is not among them, because it is reachable only by dereferencing project_relationships.entity_id, one hop the predicate does not take.

Consequence: an entity-placed rule on a reimbursed person hides payments rows carrying that entity_id (the payee entity IS a literal ancestor there) but does NOT hide the reimbursement transaction. An uncleared scheduler therefore sees a transaction they may not create the payment for. This asymmetry is deliberate — the transaction is legitimately theirs to view; only paying the restricted person is gated — so it is closed in the app layer, not by widening the predicate (recursing relationship → entity is a large, perf-sensitive change to a hot RLS path).

2. Picker filter: a null embed means "unavailable or restricted"

getSchedulableTransactions (services/transaction/base.ts) selects the reimbursement relationship's raw entity_id alongside the v_entities embed:

reimbursement_relationship:project_relationships!reimbursement_project_relationship_id (
  entity_id,
  entity:v_entities!entity_id (*)
)

Both embedded relations are RLS-gated, so the embeds themselves are the detector. A row is DROPPED when its reimbursement_project_relationship_id is set and either:

  • the relationship embed came back null (the relationship row itself was stripped), or
  • the relationship carries a non-null entity_id but the entity embed came back null (the payee entity was stripped).

A visible relationship with a genuinely-null entity_id is retained — that payee is merely incomplete, and the existing skipped-payee reporting already covers it. Projecting entity_id is what makes "stripped" distinguishable from "absent"; drop it from the select and the filter silently becomes a no-op while every mocked test stays green (hence the SELECT-string assertion in services/transaction/base.test.ts).

entity_id is deliberately NOT flattened onto the returned row. getTransactionPayee falls back to the raw reimbursement entity FK, so exposing it would let the client resolve a payee pair it cannot read.

3. Pre-write visibility gate (why it must precede the INSERT)

The picker filter is a UX gate, not a security boundary — a client can post any item set. The authoritative gate is in preflightPaymentItems (services/payment/base.ts), which after resolving each transaction's payee pair batch-probes entities for the distinct payee ids:

  • runs on the ambient caller client — never elevated — because caller visibility is precisely the property under test;
  • each id came from a foreign key on an authoritative transaction row, so the entity certainly EXISTS; an id absent from the probe therefore means "not readable by this caller", not "missing";
  • read failures propagate (an error is never interpreted as absence);
  • the id IN (…) list is chunked, so a truncated result can never be misread as restricted;
  • a missing id throws PaymentSchedulingFailureError with code RESTRICTED_PAYEE.

It must run before any write because the payments INSERT policy carries no sensitivity conjunct while its SELECT policy does. Without the guard the insert SUCCEEDS, the insert().select('*').single() RETURNING is filtered away by the SELECT policy, and databaseHelper.create() throws PGRST116 — leaving an orphaned payment row the creator cannot see, plus an opaque "Failed to process payments" toast. Both scheduling entry points (schedulePayments, payNowPayments) share this one preflight, and the pair written is exactly the pair preflight resolved.

The failure crosses the Server Action boundary as a serialisable outcome, never as an Error instance (production Flight masks Error fields) — see the house typed-outcome pattern; the DTO/enum/error helpers live in types/payment.ts, constants/payment.ts, and utils/payment.ts.

Residual windows (documented, not compensated)

  • Policy-relevant change between preflight and INSERT. PostgREST has no transactions, so a rule added (or a clearance / membership revoked) in the sub-second gap still orphans a payment row and surfaces the generic error. Compensating would need a service-role delete or an atomic RPC.
  • Post-insert reconciliation failure. A payment created before a reconciliation write fails leaves a payment with fewer allocations than intended; the existing throw surfaces it, but nothing rolls the payment back.
  • Top-up path. Amending an existing Scheduled payment fails closed on a zero-row update (the total was not incremented, so attaching reconciliations would leave the payment under-totalled) rather than continuing on an unapplied write.

Lifecycle summary

User → "Manage Access"
  └─ MarkSensitiveDialog opens
       ├─ useSensitiveRuleQuery(recordType, recordId)  — current rule (cached)
       ├─ useSensitiveFieldsQuery(recordType)          — field catalogue (cached per recordType)
       └─ useAllPermissionsQuery()                     — names + descriptions for tooltips
  └─ User picks mode + cascade + fields → Apply
       └─ useMarkRecordSensitiveMutation
            ↓
       markRecordSensitiveAction (server action)
            ↓
       sensitiveServer.markRecordSensitive (ServiceResponse envelope)
            ↓
       sensitiveBase.markRecordSensitive (callFunction)
            ↓
       public.mark_record_sensitive RPC
            ↓
       INSERT/UPDATE sensitive_rules

Downstream effects (all automatic):
  ├─ RLS policies on every consumer table now hide rows for non-cleared callers
  ├─ Masking views NULL-out restricted columns
  ├─ Approval routing skips tiers whose approvers can't see the record
  ├─ Notification dispatch drops recipients who can't see the record
  └─ Existing in-flight approvals re-evaluate clearance on tier advance

Performance characteristics

Read-side cost depends on which shape a table's SELECT policy uses.

For the ~15 tables still on the per-row template, cost is one bitmap-OR of N index probes against idx_sensitive_rules_record per row scanned (N = number of ancestor FKs for that table). With <1000 sensitive rules per organisation the probes are buffer-cached at all times; planner cost typically rounds to zero per row. See the Phase 0 spike in .scratch/sensitive-rebuild-phase3-spike/REPORT.md for measured numbers at 50 rules (typical) and 5000 rules (worst-case).

That per-row model is what the list/count hot paths outgrew. On the six set-based SELECT policies (§ "Restriction-set twins") the sensitivity gate is no longer per row at all: the twin is evaluated once per statement as an uncorrelated hashed subplan, and each row costs one hash probe against the resulting id set. Cost there scales with the number of sensitive RULES, not with the number of rows scanned — which is the whole point, since the previous shape re-ran a DEFINER call per row (a 505-row transactions count, a 719-entity badge sweep).

Consuming VIEWS carry their own shape rules, because a view can silently reintroduce per-row work above a policy that no longer does any. See docs/architecture/database/VIEWS_AND_FUNCTIONS.md § "Field-masking chokepoint helpers" for the bulk mask twins, and .claude/rules/rls-policies.md for when an aggregate belongs in a fenced CTE, a keyed uncorrelated CTE, or a LATERAL probe.

Write-side cost is one row insert per mark, regardless of how many descendants the rule applies to. No cascading writes, no per-derived-table backfill.

Approval-routing clearance lookups are one get_user_clearance_for_user RPC per distinct (approver_user_id, project_id) pair encountered during the walk. Memoised within a single submission / decision call.