Skip to content

Adding Sensitive Data Support — Runbook

How to extend the sensitive-data system to cover a new table (so its rows can be hidden) or a new field (so a specific column can be masked). For the architectural overview see SENSITIVE_DATA_SYSTEM.md.

The system is layered (database → service → hooks → UI). Each layer has a stable contract — when you extend the system you touch a small, predictable set of files in each layer. If you find yourself rewriting any of the core helpers (row_is_visible_to_caller, mark_record_sensitive, the MarkSensitiveDialog body), stop and re-read this doc: you almost certainly only need to add a row to a catalogue or a single SQL block.

Part 1 — Adding a new sensitive-eligible table

You want rows of <your_table> to be hideable via the Manage Access dialog.

When you need this

You're adding a brand-new entity that should be hideable end-to-end. Example: a new crew_contracts table whose rows should disappear from list views, joins, approvals, and notifications when a producer marks one sensitive.

When you DON'T need this

  • The table is a derived child of an existing sensitive table (e.g. transaction_line_items is downstream of transactions). Children inherit visibility through their parent's rule via the ancestor list in the parent's RLS policy — you only need to extend the parent's policy ancestor list.
  • The table is a polymorphic attachment / message / approval target. Those tables already gate on row_is_visible_to_caller with the polymorphic record reference — no new wiring needed.

Step-by-step

1. Decide the rule scope

Every sensitive_rules row carries organization_id and (optionally) project_id. Pick which scope the rule belongs to:

  • Organization-scoped — the table sits under organization_id directly (like entities). Rules use the sensitive_data:organization:{view,mark} keys; mark checks resolve against get_user_clearance('organization', organization_id).
  • Project-scoped — the table sits under project_id (like project_relationships, budget_*, transactions). Rules use the sensitive_data:project:{view,mark} keys; mark checks resolve against get_user_clearance('project', project_id).

If your table has BOTH, default to project — it's the narrower scope.

2. Identify the ancestor FKs

List every foreign key the row inherits visibility from. For transactions it's 6 (4 PR FKs + 2 entity FKs); for entity_attachments it's 1 (entity_id); for budget_items it's 1 (budget_header_id).

These become the p_ancestors array passed to row_is_visible_to_caller from your RLS policy. Each entry is {"type": "<ancestor_table>", "id": "<ancestor_pk>"}.

The single visibility helper does NOT walk recursive FKs — for recursive structures like budget_headers (parent_budget_header_id), the predicate's own scope resolution handles the walk. Don't try to flatten the chain in the policy.

3. Write the migration

Run the /migration skill (never write migration SQL by hand). It will create the migration file and walk you through the conventions. Inside the migration:

3.1 Update the RLS SELECT policy

Replace (or augment, if the table already has a policy) the table's SELECT policy:

DROP POLICY IF EXISTS "<your_table>_select" ON public.<your_table>;
CREATE POLICY "<your_table>_select"
  ON public.<your_table>
  AS PERMISSIVE
  FOR SELECT
  TO authenticated
  USING (
    (SELECT auth.uid()) IS NOT NULL
    AND <your existing org/project membership check>
    AND public.row_is_visible_to_caller(
      '<your_table>'::text,
      id,
      jsonb_build_array(
        jsonb_build_object('type', '<ancestor_table_a>', 'id', <ancestor_fk_a>),
        jsonb_build_object('type', '<ancestor_table_b>', 'id', <ancestor_fk_b>)
        -- one entry per ancestor FK
      ),
      NULL,        -- p_user_id stays NULL for RLS callers (auth.uid())
      <subtype_col>::text  -- ONLY if your row has a type column that scope_filter can narrow on; otherwise omit
    )
  );

If your table has no scope_filter-narrowed children (i.e. it's never the target of scope_filter[<your_table>].types), omit the p_own_subtype arg entirely (the parameter has a default).

3.2 Add the table to TABLE_TO_VIEW_MAPPING (if it has a masking view)

Edit packages/app/src/constants/database.ts:

export const TABLE_TO_VIEW_MAPPING: Partial<
  Record<DatabaseTable, DatabaseView>
> = {
  // … existing
  [DatabaseTable.YOUR_TABLE]: DatabaseView.YOUR_TABLE,
};

Without this entry, getRecordRequiredPermissions(recordType, recordId) returns [] for your record_type, which means the approval router and notification gates will treat every row as non-sensitive (correctness break).

3.3 Create / re-issue the masking view

Even if you don't have field-level restrictions today, a masking view is required to expose the G1 surface (sensitive_required_permissions, has_sensitive_rule) the FE reads.

Template (project-scoped table, no field masks):

CREATE OR REPLACE VIEW public.v_<your_table>
  WITH (security_invoker = on)
AS
SELECT
  t.<every base column in its existing order>,
  -- G1 surface (must be appended at the end)
  COALESCE(m.required_permissions, '{}'::text[]) AS sensitive_required_permissions,
  COALESCE(m.field_required_permissions, '{}'::jsonb) AS sensitive_field_required_permissions,
  -- Per-caller authoritative redacted-fields map. Always '{}' until you add
  -- a field mask to this table (see Part 2 — Adding a new restrictable
  -- field). Surfaced anyway so the UI shape is consistent across all v_*
  -- views and consumers can wire MaskedValue / redactedFields once.
  '{}'::jsonb AS sensitive_field_redacted,
  -- Own row only. Ancestor rules surface their own badges on the parent
  -- rows; this column is the trigger for THIS row's "Sensitive" badge.
  (m.record_id IS NOT NULL) AS has_sensitive_rule
FROM public.<your_table> t
LEFT JOIN public.sensitive_rules m
  ON m.record_type = '<your_table>' AND m.record_id = t.id;

COMMENT ON VIEW public.v_<your_table> IS
  'Masking view over <your_table>. security_invoker preserves source-table RLS. sensitive_required_permissions is the UNION of own + ancestor rules. sensitive_field_redacted is per-caller (keys the viewer lacks AND that have a value on the row). has_sensitive_rule reflects ONLY the row''s own rule.';

GRANT SELECT ON public.v_<your_table> TO authenticated;

CREATE OR REPLACE VIEW preserves column order. If the view already exists, append the G1 columns at the END of the SELECT list. Reordering or inserting mid-list errors with SQLSTATE 42P16.

If your table has ancestor rules to UNION in (like v_transactions UNIONing the transaction's own rule with the PR rule and the entity rule), copy the pattern from v_transactions in the Phase 2 migration. The sensitive_required_permissions column should be ARRAY(SELECT DISTINCT unnest(... || ... || ...)) over every applicable rule's array.

has_sensitive_rule is own-row only. A child row whose ancestor is marked sensitive must NOT light up its own badge — the parent's badge already signals the restriction, and a parent's field-mask doesn't restrict the child itself. Build the column from EXISTS (SELECT 1 FROM sensitive_rules WHERE record_type = '<your_table>' AND record_id = t.id), never from the OR of ancestor rules. (Visibility + masking from ancestors still flow through sensitive_required_permissions and the per-column masks.)

3.4 Add tests

Edit packages/database/supabase/tests/06_rls_policies.sql:

  • Test that a row hidden by an own rule is invisible to a non-cleared user.
  • Test that a row hidden by an ancestor rule is invisible to a non-cleared user.
  • Test that a row visible to a cleared user shows through.

If your view UNIONs ancestor rules: edit 02_views.sql and add a has_column assertion for sensitive_required_permissions + a happy-path query test.

Run cd packages/database && npx supabase test db to verify.

4. Wire the FE — optional cascade catalogue entry

If your table can be cascaded TO (i.e. someone marking an ancestor sensitive can opt into "also restrict this child"), edit packages/app/src/constants/sensitive.ts:

export const SENSITIVE_CASCADE_CATALOG: Partial<
  Record<DatabaseTable, readonly SensitiveCascadeChild[]>
> = {
  [DatabaseTable.<ANCESTOR>]: [
    // … existing children
    {
      childTable: DatabaseTable.YOUR_TABLE,
      label: 'Your descriptive label',
      hint: 'Optional explanatory text shown under the label',
      subtypeColumn: 'type',                       // omit if no type column
      subtypeOptions: Object.values(YourEnum),     // omit if no type column
      subtypeLabels: YOUR_TYPE_LABELS,             // optional; falls back to raw values
    },
  ],
};

Entry exists → cascade chips show in the Manage Access dialog when the ancestor is selected as the target. No entry → cascade section silently hides for that child.

Cascade gate semantics. When an ancestor rule has empty required_permissions and a scope_filter entry that enables this child (optionally narrowed by types), child rows matching that entry are hidden from callers who don't hold sensitive_data:<ancestor's scope>:view (project for project-scoped rules, organization for entity-scoped). The ancestor row itself stays visible to everyone. This is enforced inside row_is_visible_to_caller — no extra wiring needed on the child table.

If your table is the source (i.e. someone can open Manage Access on a row of <your_table> directly), nothing else to add — the dialog handles it via the catalogue entry you registered above and via the existing scope-permission map:

export const SENSITIVE_VIEW_PERMISSION_BY_RECORD_TYPE: Partial<
  Record<DatabaseTable, string>
> = {
  // … existing
  [DatabaseTable.YOUR_TABLE]: 'sensitive_data:project:view',
  // or 'sensitive_data:organization:view' for org-scoped
};

5. Wire the FE — Manage Access trigger button

In the page / view component where the row is displayed (the entity header, the row detail dialog, etc.):

import { MarkSensitiveDialog } from '@/components/sensitive/MarkSensitiveDialog';
import { MarkUnmarkSensitiveButton } from '@/components/sensitive/MarkUnmarkSensitiveButton';
import { DatabaseTable } from '@/constants/database';

const [markDialogOpen, setMarkDialogOpen] = useState(false);

// …in your action bar:
{
  canMarkSensitive && row && (
    <MarkUnmarkSensitiveButton
      isSensitive={row.has_sensitive_rule ?? false}
      onClick={() => setMarkDialogOpen(true)}
      testId="<your-area>-toggle-sensitive"
    />
  );
}

{
  canMarkSensitive && row && (
    <MarkSensitiveDialog
      recordType={DatabaseTable.YOUR_TABLE}
      recordId={row.id}
      recordLabel={row.title ?? 'this <thing>'}
      open={markDialogOpen}
      onOpenChange={setMarkDialogOpen}
    />
  );
}

canMarkSensitive should be derived from usePermissions().hasPermission(PERMISSIONS.SENSITIVE_DATA_<SCOPE>_MARK) at the relevant scope. The DB enforces the real gate via the sensitive_rules mark policies — the UI gate is purely a UX convenience to hide the button when the user couldn't use it.

6. Decide whether the table needs approval-routing coverage

If <your_table> can be submitted for approval, the approval router will already filter approvers by clearance correctly — getRecordRequiredPermissions(recordType, recordId) returns the right array as long as you registered the table in TABLE_TO_VIEW_MAPPING (step 3.2). No router changes needed.

If <your_table> can be the target of notifications, the per-event send sites should call recordIsVisibleToUser(recipientUserId, recordType, recordId) before each send. Same pattern as the existing approval notifications — see services/approval/server.ts for examples.

7. Update documentation

8. Verify

After the migration runs locally:

# DB
cd packages/database && npx supabase test db

# App
cd ../.. && yarn typecheck && yarn lint && yarn test

Browser smoke test:

  1. As a user WITHOUT the relevant :view key, list <your_table>. Restricted rows should be absent.
  2. As a user WITH :mark, open a row, click Manage Access, pick Restrict access, Apply.
  3. As the non-cleared user from step 1, list <your_table> again. The newly restricted row should now be invisible.
  4. If your table has approval workflows, submit one of the restricted rows for approval. Verify in approval_instances that uncleared approvers were stamped Skipped with skipped_reason mentioning sensitivity, and cleared approvers got Pending instances.

Part 2 — Adding a new restrictable field

You want a specific column of an already-sensitive-eligible table to become hideable via the Restrict specific fields mode of the Manage Access dialog.

When you need this

A column carries sensitive content (PII, payment details, internal codes) that should be masked even when the row itself is visible. Example: adding a tax_id column to entities that should NULL out for non-cleared users.

When you DON'T need this

  • The column is sensitive at the row level, not the field level. Field-mask is for "show the row, hide the column"; row-mask is for "hide the row entirely."
  • The column is on a downstream table (transactions, payments). Field-mask lives on source tables — derived rows are not field-restrictable. If you need to hide a column on a derived table, lift it up to the source or model it as a row-restriction.

Step-by-step

1. Decide the permission key

Every restrictable field is gated by a single permission key. Pick from the existing category keys:

  • sensitive_data:view_pii — personally identifiable information (email, phone, dates of birth, addresses)
  • sensitive_data:view_payment_details — financial credentials (IBAN, bank account, SWIFT, bank name)

If your field genuinely doesn't fit either, you need a new category key — see step 5 (separate workflow; rare).

2. Pick the category

The dialog groups fields by their category column for display. Existing categories:

  • pii
  • payment_details

If you're adding a brand-new category (rare), see step 5.

3. Write the migration

Run the /migration skill. Inside the migration:

3.1 INSERT the catalogue row
INSERT INTO public.sensitive_fields (
  record_type,
  column_name,
  display_label,
  default_permission_key,
  category,
  display_order
)
VALUES (
  '<your_table>',
  '<your_column>',
  'Your Field Label',
  'sensitive_data:view_<category>',
  '<your_category>',
  10  -- or whatever fits the existing order within the category
)
ON CONFLICT (record_type, column_name) DO NOTHING;

The validate_sensitive_rule_fields trigger uses this row to reject mark_record_sensitive calls that try to mask a column not in the catalogue. Without the INSERT, the field is invisible to the dialog AND the RPC would reject any attempt to mask it.

3.2 Add a CASE block to the masking view AND extend sensitive_field_redacted

The dialog shows the toggle and the RPC accepts the write — but the actual NULL-out happens in the masking view. Without a CASE expression there, the column reads through unmasked even when the field rule is set.

Two things need editing in the view: the CASE that NULLs out the column, AND the sensitive_field_redacted expression's CASE key WHEN ... THEN <value-present-check> END so the FE can tell "empty" from "redacted from me" for the new field.

CREATE OR REPLACE VIEW public.v_<your_table>
  WITH (security_invoker = on)
AS
SELECT
  t.<existing columns in their existing order>,
  CASE
    WHEN m.field_required_permissions ? '<your_column>'
     AND NOT ((m.field_required_permissions->>'<your_column>') = ANY (
              public.get_user_clearance('<scope>'::public.scope_type, t.<scope_id>)))
    THEN NULL
    ELSE t.<your_column>
  END AS <your_column>,
  -- … other masked columns
  -- G1 surface (append at end if you're inserting a new column)
  COALESCE(m.required_permissions, '{}'::text[]) AS sensitive_required_permissions,
  COALESCE(m.field_required_permissions, '{}'::jsonb) AS sensitive_field_required_permissions,
  -- Per-caller redacted-fields. Extend the CASE inside with a clause for
  -- the new column's value-presence check. Without this clause the FE
  -- can't distinguish "you don't have permission" from "row has no value"
  -- and will surface a "Restricted" placeholder on empty rows too.
  COALESCE(
    (SELECT jsonb_object_agg(kv.key, kv.value)
       FROM jsonb_each_text(COALESCE(m.field_required_permissions, '{}'::jsonb)) AS kv
      WHERE NOT (kv.value = ANY (
              public.get_user_clearance('<scope>'::public.scope_type, t.<scope_id>)
            ))
        AND CASE kv.key
              -- existing field clauses…
              WHEN '<your_column>'   THEN t.<your_column> IS NOT NULL
                                       AND t.<your_column> <> ''  -- drop for non-text columns
              ELSE TRUE
            END),
    '{}'::jsonb
  ) AS sensitive_field_redacted,
  -- Own row only — never OR ancestor rules into this column.
  (m.record_id IS NOT NULL) AS has_sensitive_rule
FROM public.<your_table> t
LEFT JOIN public.sensitive_rules m
  ON m.record_type = '<your_table>' AND m.record_id = t.id;

CREATE OR REPLACE VIEW preserves column order. The <your_column> CASE must replace the existing t.<your_column> selection in-place (same alias, same position). If <your_column> is brand new on the base table too, append both at the end of the SELECT list.

Always update both the CASE-NULL-out AND the sensitive_field_redacted CASE in the same edit. Forgetting the second one means the masking still works at the DB level, but the FE renders "Restricted" on rows where the field is genuinely empty — confusing UX and visible "data" where there is none.

Type matters in the value-presence check. Text columns need <> '' to filter empty strings; JSONB columns just need IS NOT NULL (or check the JSON shape if "empty" means a particular structure); numeric/date/uuid columns just need IS NOT NULL. Get this wrong and the FE will surface "Restricted" on rows that look populated but have only empty-string defaults.

If the masking view UNIONs ancestor rules (like v_transactions), the CASE block must reference all relevant rules' field_required_permissions — copy the pattern from existing fields in the same view.

3.3 Re-grant the view if needed

If you re-created the view (vs CREATE OR REPLACE):

GRANT SELECT ON public.v_<your_table> TO authenticated;
3.4 Update tests

packages/database/supabase/tests/02_views.sql:

  • Add a has_column assertion that confirms the masked column still exists in the view (the CASE-as-alias preserves the column name).

packages/database/supabase/tests/06_rls_policies.sql:

  • Add a happy-path test: a cleared user reads the unmasked value.
  • Add a negative-path test: a non-cleared user reads NULL for the column even though the row is visible.

Run cd packages/database && npx supabase test db.

4. Wire the FE (usually nothing)

The Manage Access dialog reads the catalogue via useSensitiveFieldsQuery(recordType) and renders one FieldRow per row. No FE code change is needed for the Manage Access dialog — the field appears automatically the first time the catalogue query runs after the migration.

Read-side rendering: use <MaskedValue redactedFields={...} />

When you render the new field in a view component (transaction header, entity sheet, project relationship details, etc.), wrap the cell with MaskedValue and pass the row's sensitive_field_redacted map (or entity_sensitive_field_redacted for entity-derived columns on v_project_relationships):

import { MaskedValue } from '@/components/sensitive/MaskedValue';

<MaskedValue
  value={entity.<your_column> ?? null}
  fieldName="<your_column>"
  redactedFields={entity.sensitive_field_redacted}
/>

MaskedValue renders the value when it's truthy, the emptyPlaceholder (default em-dash) when it's null AND not redacted, and a "Restricted" placeholder when the field is in redactedFields. The DB-side value-presence check (Part 2 → 3.2) keeps that placeholder from firing on rows where the column is genuinely empty.

Don't gate the surrounding block on the value alone. If your card normally renders the field only when entity.<your_column> is truthy, also include '<your_column>' in (entity.sensitive_field_redacted ?? {}) in the guard — otherwise the block hides for redacted viewers even when there's content they should see is restricted. See VendorInformationCard and CustomerInformationCard for the pattern.

Optional: category label / icon overrides

If your field uses an existing category (pii or payment_details), nothing else to do — the label and icon come from the existing override map and icon registry. If you added a brand-new category that stringToLabel can't capitalise correctly (e.g. an acronym like pii → Pii), add an override:

packages/app/src/constants/sensitive.ts:

export const FIELD_CATEGORY_LABEL_OVERRIDES: Record<string, string> = {
  pii: 'PII',
  <your_category>: 'Your Display Label',
};

packages/app/src/constants/icons.ts:

export const SENSITIVE_FIELD_CATEGORY_ICONS: Record<string, LucideIcon> = {
  pii: UserCircle,
  payment_details: Landmark,
  <your_category>: YourLucideIcon,
};

Both fall back to safe defaults (init-capped label, Tag icon), so these are pure polish.

5. New permission key or new category (rare)

Only do this if your field genuinely doesn't fit view_pii or view_payment_details.

  1. Add the new permission key in a Phase-1-style migration (INSERT INTO public.permissions + INSERT INTO public.permission_role_links to bind it to the appropriate roles).
  2. Update the implies chain — the new key is typically implied by the same scope :view keys.
  3. Update the PERMISSIONS_SYSTEM.md § Sensitive Data Permissions table.
  4. Use the new key in your sensitive_fields row (step 3.1).
  5. Add the category to FIELD_CATEGORY_LABEL_OVERRIDES and SENSITIVE_FIELD_CATEGORY_ICONS (step 4).

6. Verify

cd packages/database && npx supabase test db
cd ../.. && yarn typecheck && yarn lint && yarn test

Browser smoke test:

  1. As a :mark user, open Manage Access on a row of <your_table>. Confirm the new field appears in the "Restrict specific fields" mode picker with the correct category badge and permission badge.
  2. Toggle the field on and Apply.
  3. As a user without the field's permission key, read the row through the masking view. The field should be NULL.
  4. As a user with the key, read the same row. The field should show the unmasked value.

Cross-reference