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_SCHEMA.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, 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.
  • organization_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 (organization-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 organization_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:organization: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.

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.

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 (UNION of own + ancestor rules' required_permissions) so callers can decide UI affordances without re-implementing the cascade.
  • 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 for the <SensitiveBadge /> UI. Own row only — a child row whose ancestor is marked does NOT inherit the badge; the parent's badge already signals its restriction and a parent's field-mask doesn't restrict the child. Ancestor visibility / masking continues to flow through sensitive_required_permissions and the per-column CASE blocks.
  • Wrap restrictable columns in CASE expressions that compare the column's required key against get_user_clearance(...) and return NULL when the caller lacks 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('organization', organization_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.

Permissions

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

Key Action
sensitive_data:organization:view See sensitive entities in an organization
sensitive_data:organization: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 Organization 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 row_is_visible_to_caller(..., p_user_id). Fail-closed.
  • 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").

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:organization: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 badge for list views, driven by has_sensitive_rule from the masking view.

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.organization_id / project_id and computes visibility per recipient. Fail-closed: a missed RPC call drops the notification rather than leaking a sensitive record.

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 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 organization 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).

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.