Sensitive Data Tables

These tables back the sensitive-data layer described in SENSITIVE_DATA_SYSTEM.md (architecture-level overview of row/field/cascade masking, subject self-visibility, the Mark Sensitive UI, and approval-routing filters).

A rule is a no-op for its subject. Row visibility and field masks are self-exempted for the person a record is about — the entity whose entities.user_id matches the evaluated user, directly or via project_relationships.entity_id. Badge metadata (the rule's existence and its required permissions) is never self-hidden. Loan-out companies (user_id IS NULL) are not exempted. Because the exemption keys off entities.user_id, that column is write-restricted to trusted server paths (excluded from the authenticated INSERT/UPDATE grants). See SENSITIVE_DATA_SYSTEM.md § Subject self-visibility.

Field masking is enforced in DEFINER chokepoint helpers, not inline in the views. The maskable value columns (entities.email / phone_number / payment_details; project_relationships.payment_details) are column-grant-revoked from authenticated on the base tables, so the masking views (v_entities / v_project_relationships, which read private.entity_masked_fields / private.project_relationship_masked_fields) are the only authenticated read path — the raw column cannot be selected off the base table to bypass the mask. entities.search_query no longer embeds any maskable value.

Sensitive Rules (sensitive_rules)

Purpose: Typed rule catalogue — one row per sensitive record. Drives row-level visibility and field-level masking through the visibility predicate (row_is_visible_to_caller) and the per-table masking views (v_entities, v_project_relationships, v_transactions, v_payments, v_budget_headers, v_budget_items).

Column	Type	Description
id	UUID	Primary key, default gen_random_uuid(). Surrogate key — the polymorphic ref (record_type, record_id) is the natural key
record_type	TEXT	One of: entity, project_relationship, transaction, payment, budget_header, budget_item. NOT NULL
record_id	UUID	PK of the row in the corresponding source table. NOT NULL
organisation_id	UUID	Denormalised at INSERT, immutable. Drives RLS scope checks. NOT NULL
project_id	UUID	Denormalised at INSERT, immutable. NULL only for entity rules; drives project-scope RLS checks
required_permissions	TEXT[]	Caller must hold AT LEAST ONE to see the row through any masking view. Empty = field-mask only (row visible, columns masked). NOT NULL, default '{}'
field_required_permissions	JSONB	Map of column_name → permission_key. Empty = no field masks. Validated against the sensitive_fields catalogue by the validate_sensitive_rule_fields trigger. NOT NULL, default '{}'
scope_filter	JSONB	Per-type cascade narrowing. Today {"transaction_types": [...]} on a project-relationship / entity rule masks only descendants matching those types. NOT NULL, default '{}'
auto_applied	BOOLEAN	TRUE when applied automatically during transaction ingest (a newly created entity / project relationship marked sensitive by the project's per-type metadata.transaction.<type key>.entities_created_as_sensitive setting, evaluated at AI type discovery), FALSE for a manual Manage Access mark. NOT NULL, default false. Surfaced by the masking views as sensitive_auto_applied to drive the transaction UI's quick-lift affordance
created_by_user_id	UUID	NOT NULL, default auth.uid()
created_at	TIMESTAMPTZ	NOT NULL, default now()
updated_by_user_id	UUID	NOT NULL, default auth.uid()
updated_at	TIMESTAMPTZ	NOT NULL, default now()

Constraints:

• UNIQUE (record_type, record_id) — natural key, prevents duplicate rules for the same source row
• CHECK cardinality(required_permissions) > 0 OR field_required_permissions <> '{}' OR scope_filter <> '{}' — at least one of the three must be set (nonsensical rule otherwise)

Key Indexes:

• idx_sensitive_rules_org btree(organisation_id) WHERE project_id IS NULL — entity rules
• idx_sensitive_rules_proj btree(project_id)
• idx_sensitive_rules_required_perms_gin gin(required_permissions) WHERE required_permissions <> '{}'

RLS Policies:

• SELECT: members can view rules in scope (organisation or project)
• INSERT: authorized users can mark records sensitive (caller permission enforced inline via get_user_clearance(scope, scope_id))
• UPDATE: authorized users can adjust the rule
• DELETE: authorized users can unmark the record

Sensitive Fields (sensitive_fields)

Purpose: Catalogue of which columns are eligible for field-level masking. Drives the Mark Sensitive dialog (toggle list) and validates writes to sensitive_rules.field_required_permissions.

Column	Type	Description
id	UUID	Primary key
record_type	TEXT	Matches sensitive_rules.record_type values. NOT NULL
column_name	TEXT	Column on the source table that can be masked. Must exist on the base table. NOT NULL
display_label	TEXT	Human-readable label rendered next to the toggle in the Mark Sensitive dialog. NOT NULL
default_permission_key	TEXT	Permission key the UI prefills into sensitive_rules.field_required_permissions when the toggle flips on. NOT NULL
category	TEXT	Grouping label for the dialog (pii, payment_details, …). NOT NULL
display_order	INTEGER	Sort key within a category, lower = earlier. NOT NULL, default 0
is_active	BOOLEAN	Soft-disable flag. Inactive rows are skipped by the UI picker, but the masking view still respects existing field_required_permissions entries. NOT NULL, default true
created_at	TIMESTAMPTZ	NOT NULL, default now()
updated_at	TIMESTAMPTZ	NOT NULL, default now()

Constraints: UNIQUE (record_type, column_name)

RLS Policies: SELECT TO authenticated (read-only catalogue). No write policies — managed via migrations.