Views, Functions & Triggers¶

Database Views¶

User Permissions View (`v_user_permissions`)¶

Purpose: Shows all permissions a user has across all organisations and projects

Use Case Example: Used as the base view for checking any user permission in the system.

View Definition:

Combines role-based and direct permission grants from user_access table
Expands role permissions via permission_role_link
Returns user_id, permission_key, scope_type, and scope_id
Security invoker enabled to respect RLS policies

User Organisation Permissions View (`v_user_organisation_permissions`)¶

Purpose: Shows all permissions a user has for each organisation

Use Case Example: Check if a user can edit organisation settings or manage organisation users.

View Definition:

Filters user_permissions view for organisation scope
Returns user_id, organisation_id, and array of permission_keys
Groups permissions by user and organisation

User Project Permissions View (`v_user_project_permissions`)¶

Purpose: Shows all permissions a user has for each project through direct grants

Use Case Example: Check if a user has budget:edit permission for a specific project.

View Definition:

Filters user_permissions view for project scope
Returns user_id, project_id, permission_keys array, and project_relationship_ids array
Groups permissions by user and project

User Accessible Organisations View (`v_user_accessible_organisations`)¶

Purpose: Shows all organisations a user has access to with full details and permissions

Use Case Example: Populate organisation switcher dropdown in the UI with permission-based features.

View Definition:

Combines users with explicit permissions and organisation creators
Returns all organisation fields plus user_id and permission_keys array
Organisation creators automatically get all organisation permissions
Filter by user_id in the application layer
Excludes creators who also have explicit permissions to avoid duplicates

Organisation Members View (`v_organisation_members`)¶

Purpose: Shows all members of an organisation with their profile details, deduplicated

Use Case Example: Organisation settings page lists members with their names, avatars, and owner status.

View Definition:

Joins user_accesses with users, attachments, and organisations
Uses DISTINCT ON (user_id, scope_id) to deduplicate users with multiple access grants (roles/permissions)
Filters for organisation scope type and active status
Returns user_id, organisation_id, email, phone, name fields, avatar_url, joined_at, and is_owner flag
Security invoker enabled to respect RLS policies

User Accessible Projects View (`v_user_accessible_projects`)¶

Purpose: Shows all projects a user has access to with project type information

Use Case Example: Dashboard queries this view to show all projects for a specific user, including project type details for filtering and display.

View Definition:

Combines users with explicit project permissions and project creators
Returns project_id, user_id, and project_relationship_ids array
Uses LATERAL join to include project type information (project_type_category_id, project_type, is_media, is_accounting, is_personal_accounting)
Filter by user_id in the application layer
Excludes creators who also have explicit access to avoid duplicates
Security invoker enabled to respect RLS policies

Returned Fields:

All fields from projects table (including country_code, currency_code, tax_scheme_id) — note: status column was removed from projects
project_type_category_id: Category ID of the project type
project_type: Code of the project type category
is_media: Boolean flag indicating if it's a media project
is_accounting: Boolean flag indicating if it's an accounting project
is_personal_accounting: Boolean flag indicating if it's a personal accounting project

Note: The view uses currency_code and tax_scheme_id (without "default_" prefix) which are auto-populated from country_code via trigger

User Accessible AI Prompt Functions View (`v_user_accessible_ai_prompt_functions`)¶

Purpose: Shows AI prompts with their functions filtered by user permissions

Use Case Example: When Sana needs to load the GatewayPrompt with its functions, this view ensures only functions the user has permission to access are returned.

View Definition:

Joins ai_prompt, ai_prompt_function, and user_accessible_ai_functions tables
Returns one row per prompt-function combination
Filters by user permissions (can_access = true)
Includes all prompt and function fields with prefixed naming

People View (`v_people`)¶

Purpose: Filtered view of entities table showing only Person type entities

Use Case Example: Used when querying for individuals (cast, crew, contacts) without businesses.

View Definition:

Filters entities table where type = 'Person'
Returns all entity columns relevant to individuals
Includes: id, organisation_id, user_id, first_name, last_name, preferred_name, date_of_birth, email, phone_number, address_id, metadata, is_active, created_at, updated_at, created_by_user_id, updated_by_user_id
Security invoker enabled to respect RLS policies

Businesses View (`v_businesses`)¶

Purpose: Filtered view of entities table showing only Business type entities

Use Case Example: Used when querying for business entities (vendors, suppliers, production companies) without individuals.

View Definition:

Filters entities table where type = 'Business'
Returns all entity columns relevant to businesses
Includes: id, organisation_id, business_name, email, phone_number, address_id, metadata, is_active, is_secondary_supplier, created_at, updated_at, created_by_user_id, updated_by_user_id
Security invoker enabled to respect RLS policies

User Accessible Menu Items View (`v_user_accessible_menu_items`)¶

Purpose: Shows menu items accessible to a user based on their permissions, project feature flags, and project type flags

Use Case Example: Dynamically build navigation menus showing only items the user has permission to access, filtered by project type (media, accounting, personal accounting) and feature flags (e.g., approvals_enabled).

View Definition:

Joins menu_items with user permissions and menu_item_categories
Returns menu items with access status (true/false) for each user
Checks required_permission_key against user's permissions
Checks required_feature_flag against project columns (e.g., if required_feature_flag = 'approvals_enabled', verifies projects.approvals_enabled = true)
Includes category-based project type flags (is_media, is_accounting, is_personal_accounting) directly from the linked category
Filters menu items based on project type for context-specific navigation
Security invoker enabled to respect RLS policies

Returned Fields:

All fields from menu_items table (including required_feature_flag)
can_access: Boolean indicating if user has permission
is_media: Boolean flag from category (NULL if menu item not linked to a category)
is_accounting: Boolean flag from category (NULL if menu item not linked to a category)
is_personal_accounting: Boolean flag from category (NULL if menu item not linked to a category)
Category display order for sorting within specific project types

Feature Flag Filtering: When a menu item has required_feature_flag set, the view checks if the corresponding project column is true. For example, approvals menu items have required_feature_flag = 'approvals_enabled' and only appear when the project has approvals enabled. Unknown feature flags default to visible (fail open).

User Accessible AI Functions View (`v_user_accessible_ai_functions`)¶

Purpose: Shows AI functions accessible to a user based on their permissions

Use Case Example: Filter available AI functions for WhatsApp bot based on user's permissions.

View Definition:

Joins ai_functions with user permissions
Returns AI functions the user can access
Checks required_permissions array against user's permissions
Security invoker enabled to respect RLS policies

User Abuse Actions View (`v_user_abuse_actions`)¶

Purpose: Shows abuse detection actions taken against users

Use Case Example: Monitor and review abuse prevention actions across the system.

View Definition:

Combines data from abuse_patterns and abuse_action_histories
Shows patterns detected and actions taken
Includes user information and timestamps
Security invoker enabled to respect RLS policies

Processing Ready Steps View (`v_processing_ready_steps`)¶

Purpose: Shows processing steps (AI and manual) that are ready to be executed based on their dependencies

Use Case Example: System queries this view to determine which steps can be executed next in a processing job.

View Definition:

Identifies steps where all dependencies are completed
Returns step_id, job_id, step_key, template_step_id
Includes execution configuration (execution_group, can_run_parallel, max_retries, timeout_seconds)
Only shows steps with status 'pending' or 'ready' whose dependencies are satisfied
Uses CTEs to efficiently check dependency completion
Security invoker enabled to respect RLS policies

Pending Processing Issues View (`v_pending_processing_issues`)¶

Purpose: Filtered view of pending processing issues from current documents that require review before approval submission.

Use Case Example: Check if a transaction has unresolved issues before allowing "Submit for Approval" action.

View Definition:

Filters processing_issues where status='pending' and is_current=true (excludes issues from replaced documents)
Also exposes the issue's first-class project_id column, so the sensitive-approval reconcile sweep can filter pending issues by project without a JOIN
Security invoker enabled to respect RLS policies
Used by approval submission logic to detect blocking issues

Rebuilt onto the issue registry: the view now JOINs processing_issue_templates, because processing_issues no longer stores the key, copy, severity or resolvability — those five columns were dropped as duplicates of the registry.

issue_key — projected as t.key AS issue_key. The base column is gone, but the FIELD NAME survives here, so key-based reads and filters need no rewrite.
effective_title, effective_description, effective_severity, effective_submitter_resolvable — COALESCE(per-raise override, template default). These are deliberately NOT named after the old snapshot columns: a stale reader asking for issue_title fails loudly with 42703 rather than silently reading a registry default it did not ask for.
title_override, description_override, severity_override, submitter_resolvable_override — the raw overrides, exposed alongside so a caller can distinguish "this raise chose a value" from "the registry default applied".
issue_template_id, display_name, can_ignore, can_send_query, can_replace_document, configurable, applies_to_transaction_types, template_is_active — registry columns the settings and resolution UIs read.

Save Project Transaction Settings (`save_project_transaction_settings`)¶

Purpose: Atomically save a project's transaction settings across three tables — projects.metadata.transaction, project_transaction_fallbacks and project_issue_settings.

Signature: save_project_transaction_settings(p_project_id uuid, p_transaction_metadata jsonb, p_fallbacks jsonb, p_issue_overrides jsonb) RETURNS jsonb → {fallback_count, override_count}.

Why an RPC: PostgREST auto-commits per request, so the three-table save had no atomicity — a mid-flight failure left the metadata updated and the child rows stale. Called via callFunctionOrThrow. Same rationale and shape as import_budget.

SECURITY INVOKER — this is load-bearing. RLS is the only thing enforcing project:edit on the child tables; a DEFINER rewrite would hand every authenticated caller unrestricted writes to any project's settings. A caller without project:edit is stopped at the FIRST write: the projects UPDATE policy also requires project:edit, so the RLS-filtered UPDATE matches zero rows and the function's ROW_COUNT guard raises before either child table is touched. The child-table policies are independently fail-closed (a direct PostgREST INSERT by a bare member returns 42501).

Strict SQL-side validation, so a malformed payload persists nothing: the metadata map must carry exactly the six type keys, each holding only entities_created_as_sensitive / line_item_processing; fallback and override entries reject unknown fields, off-enum types, duplicate pairs, and any entry whose direction is not present exactly when the type is Invoice. Every failure RAISEs, and the whole call is one transaction.

Prune semantics: p_fallbacks and p_issue_overrides are the COMPLETE desired state, not deltas — an omitted pair is DELETED. A fallback entry carrying neither coa_reference_id nor budget_item_id means "no fallback configured" and leaves no row behind. Behaviour pinned in tests/28_project_transaction_fallbacks_and_save_rpc.sql.

Project Categories View (`v_project_categories`)¶

Purpose: Flattened view of project categories with category hierarchy information and project type flags

Use Case Example: Query all categories associated with a project, including inherited category properties for filtering and display.

View Definition:

Joins project_categories with categories, category_groups, and parent categories
Returns complete category information for each project-category association
Includes project type flags (is_media, is_accounting, is_personal_accounting) directly from category
Security invoker enabled to respect RLS policies

Returned Fields:

project_id, category_id: Junction table IDs
group_id, group_code, group_name: Category group information
parent_id, parent_code, parent_name: Parent category information (NULL for top-level)
category_code, category_name: Category identification
is_active, is_default, display_order: Category properties
is_media, is_accounting, is_personal_accounting: Project type flags
metadata: JSONB category configuration
Timestamps and user tracking fields

Category Hierarchy View (`v_category_hierarchy`)¶

Purpose: Hierarchical view of categories with level and path information

Use Case Example: Build category trees for UI components, showing complete hierarchies with breadcrumb paths.

View Definition:

Uses recursive CTE to traverse category parent-child relationships
Returns categories with their depth level and complete path from root to leaf
Includes all category fields including project type flags
Orders by display_order within each level
Security invoker enabled to respect RLS policies

Returned Fields:

All fields from categories table
level: Integer depth in hierarchy (1 for top-level)
path: Text array showing codes from root to current category
group_code, parent_code: Denormalized lookup fields
description: Category description text
group_description: Description from the category_groups table

Available Project Types View (`v_available_project_types`)¶

Purpose: Filtered view of categories in the 'project_type' group, showing only active and available types

Use Case Example: Populate project type dropdowns in create/edit forms with only valid, active project types.

View Definition:

Filters categories to only 'project_type' group
Returns only active categories (is_active = true)
Includes project type flags for filtering media vs accounting types
Sorted by display_order for consistent presentation
Security invoker enabled to respect RLS policies

Returned Fields:

All fields from categories table
group_code, parent_code: Denormalized for easier querying
level, path: Hierarchy information from v_category_hierarchy
Includes is_media, is_accounting, is_personal_accounting flags

Approval Types View (`v_approval_types`)¶

Purpose: Approval types and subtypes from category hierarchy, filtered by the approval_type group

Use Case Example: Populate approval configuration UI with available approval types (level=1) and subtypes (level=2). Frontend filters by project flags (is_media, is_accounting, is_personal_accounting).

View Definition:

Filters v_category_hierarchy where group_code = 'approval_type'
Orders by level, display_order, and name
Security invoker enabled to respect RLS policies

Returned Fields:

id, code, name: Category identifiers
parent_id, parent_code: Parent category reference
level: 1 for types, 2 for subtypes
display_order: Ordering within level
is_active: Whether the type is enabled
metadata: JSONB with optional menu_key linking to menu_items for icons
is_media, is_accounting, is_personal_accounting: Project type flags for filtering
description, group_description: Human-readable descriptions

User Approval History View (`v_user_approval_history`)¶

Purpose: User's completed approval instances with type/subtype information for categorized tabs in UI

Use Case Example: Display user's approval history with filtering by approval type tabs. Shows all decisions the user has made (Approved, Rejected, Skipped).

View Definition:

Joins approval_instances with requests, configurations, projects, and categories
Filters by status IN ('Approved', 'Rejected', 'Skipped', 'Cancelled')
Includes type and subtype category information for tab grouping
Security invoker enabled to respect RLS policies

Returned Fields:

Instance fields: approval_instance_id, approval_request_id, tier_number, entity_id, user_id, instance_status, decision_at, decision_note, assigned_at
Request fields: record_type, record_id, project_id, request_status, submitted_by_user_id, submitted_at, final_decision_at, request_reject_reason
Project fields: project_title, organisation_id
Configuration fields: approval_configuration_id
Type category fields: type_category_id, type_code, type_name
Subtype category fields (nullable): subtype_category_id, subtype_code, subtype_name
UI fields: tab_label (COALESCE of subtype_name, type_name), total_tiers
Submitter fields: submitter_first_name, submitter_last_name, submitter_name

User Approval Queries View (`v_user_approval_queries`)¶

Purpose: Approval queries visible to users. Returns one row per (query, user who can reply).

Use Case Example: Filter by user_id to get queries a specific user can reply to.

View Definition:

Gets latest message for each queried approval request
Joins with approval_instances to get all approvers (any tier)
Excludes query poster from results (they can't reply to their own query)
Filters by request status = 'Queried'
Security invoker enabled to respect RLS policies

Returned Fields:

User field: user_id (user who can reply to this query, approver at any tier)
Query fields: message_id, approval_request_id, query_text, query_poster_user_id, query_poster_entity_id, query_posted_at, query_poster_tier
Request fields: record_type, record_id, project_id, current_tier_number, request_status, submitted_by_user_id, submitted_at
Project fields: project_title, organisation_id
Configuration fields: approval_configuration_id
Type category fields: type_category_id, type_code, type_name
Subtype category fields (nullable): subtype_category_id, subtype_code, subtype_name
UI fields: tab_label, total_tiers, query_poster_name, message_count

Usage:

Filter by user_id to get queries the user can reply to
Query poster is automatically excluded from results

User Pending Approvals View (`v_user_pending_approvals`)¶

Purpose: One row per (pending approval instance, user who can act on it) across a UNION of formal-tier approvers and soft-approval approvers. Backs the pending-approvals list; callers filter by project_id / user_id. 53-column output signature pinned in tests/02.

Performance invariants (never regress these — they timed the pending-approvals read out at ~5s for 13 rows in production):

The pending_requests CTE is declared AS NOT MATERIALIZED (PG17). It is a pure SELECT, so this lets the planner push the project_id / user_id predicate down into BOTH UNION ALL branches instead of first materializing every org-wide Pending/Queried request. Never drop the hint (or add a data-modifying statement to the CTE that would force materialization).
record_summary computes each role entity's display name INLINE from flat project_relationships + entities LEFT JOIN pairs (customer_rel_*, supplier_rel_*, reimbursement_rel_*, expense_rel_*), using the same name CASE as format_entity_display_name. Never reintroduce v_project_relationships joins here for display names — each of the eight masked-view joins dragged private.project_relationship_masked_fields + private.entity_masked_fields in per candidate row under the caller's RLS. The display name is a plain name expression, not a masked column, so the lean joins are equivalent. tests/14 pins expression-level display-name parity plus the config-drift regression scenarios; the full-row old-vs-new comparison was a one-time migration verification, not a committed pin.
count_pending_processing_issues (LATERAL) and total_tiers (correlated subquery) are measured cheap and stay as-is.

EXPLAIN sentinel: a filtered read (WHERE project_id = X AND user_id = Y) as an authenticated persona must show NO CTE Scan on pending_requests (inlined) and NO reference to entity_masked_fields / project_relationship_masked_fields / format_entity in the plan.

Available Approvers View (`v_available_approvers`)¶

Purpose: Returns entities that can be selected as approvers for a project

Use Case Example: When configuring approval tiers, only show entities whose linked users have the approval:view permission.

View Definition:

Joins project_relationships with the masking view v_entities (NOT the raw entities base table) so the email / phone_number contact fields are correctly field-masked and subject-self-visible — the raw contact columns are no longer readable by authenticated
Filters to only include entities with linked users (user_id IS NOT NULL)
Checks that the linked user has the approval:view permission for the project via v_user_project_permissions
Security invoker enabled to respect RLS policies

Returned Fields:

Field	Type	Description
project_id	UUID	The project this approver can be assigned to
entity_id	UUID	Entity ID (used as approver reference)
user_id	UUID	Linked user ID
entity_type	TEXT	Entity type (person/business)
first_name	TEXT	First name (for persons)
last_name	TEXT	Last name (for persons)
preferred_name	TEXT	Preferred/display name
company_name	TEXT	Company name (for businesses)
email	TEXT	Entity email
phone	TEXT	Entity phone
is_active	BOOLEAN	Whether entity is active
organisation_id	UUID	Organisation the entity belongs to

Key Constraint: Only includes entities where:

Entity has a project_relationship in the project
Entity has a linked user (user_id IS NOT NULL)
That user has the approval:view permission for the project

Conversations with Details View (`v_conversations_with_details`)¶

Purpose: Comprehensive flat view of conversations with user, project (including category flags), and relationship details for AI processing

Use Case Example: When Sana's AI needs to generate category-aware prompts, this view provides all necessary context in a single flat structure without complex joins.

View Definition:

Joins conversations with users, user_settings, languages, v_user_accessible_projects, attachments, organisations, project_relationships, entities, and role_definitions
Returns flat structure with prefixed field names (user*, project_, org__, entity*, role*)
Includes category flags from v_user_accessible_projects (is_media, is_accounting, is_personal_accounting)
Security invoker enabled to respect RLS policies

Returned Fields:

All fields from conversations table
User fields (flattened with prefix):
user_email, user_phone, user_language_code
Language fields:
language_code, language_name
Project fields (flattened with prefix):
project_code, project_title, project_poster_url
is_media, is_accounting, is_personal_accounting (category flags)
Organisation fields (flattened with prefix):
org_id, organisation_code, org_name
Project relationship fields:
relationship_status, role_title_override
Entity fields (flattened with prefix):
entity_id, entity_first_name, entity_last_name, entity_preferred_name
Role definition fields:
role_definition_id, role_title, role_is_active

Key Use Cases:

AI prompt generation with category-aware context
Switch project/engagement functionality with proper display based on project type
Notification template variable population (project_type_description)
Efficient querying without multiple joins in application code

Compliance Capture View (`v_compliance_capture`)¶

Purpose: Aggregated view of root messages with entity, project, and organisation details, including thread-wide AI-tagged compliance categories and attachment information for compliance tracking

Use Case Example: Compliance officers query this view to see all captured communications with aggregated tags from entire threads, entity details, and attachments, filtering by project or organisation for audit purposes.

View Definition:

Shows only root messages (parent_id IS NULL) to avoid showing individual thread replies
Aggregates all attachments from entire message threads (includes replies)
Aggregates all compliance tags from entire message threads (root + all replies)
Joins with the masking view v_entities (NOT the raw entities base table), projects, organisations, tags, and taggings tables — so the entity_email / entity_phone fields it exposes are field-masked and subject-self-visible (the raw contact columns are no longer readable by authenticated)
Returns comprehensive message data with entity context, tags with AI metadata, and deduplicated attachment information
Security invoker enabled to respect RLS policies

Returned Fields:

Message fields:
id, project_id, entity_id, content_text, source, content_json, delivered_at
sender - Calculated sender identification
Entity fields (for sender/recipient context):
business_name, entity_first_name, entity_last_name, entity_email, entity_phone
entity_preferred_name, entity_type, entity_name (computed from type-specific fields)
Project and Organisation fields:
project_code, project_title, organisation_code, organisation_name
Thread-wide tag aggregation:
tags - Array of tag objects with AI metadata from entire thread (id, name, color, category, confidence, relevance, reasoning)
tag_names - Simple array of tag names from entire thread for easy filtering
Thread-wide attachment aggregation:
attachment_count - Total number of unique attachments across entire thread
attachment_names - Array of all attachment file names in thread (deduplicated)
attachment_ids - Array of all attachment IDs in thread (deduplicated)
attachment_paths - Array of all attachment storage paths in thread (deduplicated)

Message Threading Features:

Only displays root messages (filters WHERE parent_id IS NULL)
Attachment aggregation includes all messages in thread via root_message_id
Tag aggregation includes all messages in thread via root_message_id - each reply keeps its own tags with full AI metadata (confidence, relevance, reasoning)
Uses DISTINCT to deduplicate attachments when same file is attached to multiple messages in thread
Thread replies are accessible through MessageViewer component, not shown in list view

Key Use Cases:

Compliance audit trails with complete message threads
Filter messages by AI-detected categories (e.g., safety, financial, contractual)
Track all communications with specific entities across projects
Export compliance data for legal/regulatory requirements
Search and filter by attachment presence or specific file types

Payments View (`v_payments`)¶

Purpose: Payment summary view with entity details (including bank fields), payment method info, and user joins. One row per payment (no reconciliation detail).

Use Case Example: Display a list of all payments with supplier/vendor names, bank details, and the users who scheduled or paid each payment.

View Definition:

Joins payments with entities to resolve entity name; joins with project_relationships to source the payee bank details
Joins with v_payment_methods for payment method details (including amount and display_label)
Joins with users for paid-by and scheduled-by user details
Security invoker enabled to respect RLS policies

Bank-detail sourcing: entity_bank_* columns prefer project_relationships.payment_details (the PR-level override) with a per-field fallback to entities.payment_details (the organisation-level default). Sensitive masking follows the source of each field: PR-sourced values are gated by the PR's sensitive_rules row against get_user_clearance('project', project_id); entity-sourced values by the entity's row against get_user_clearance('organisation', organisation_id). A sensitive PR cannot be bypassed via the entity fallback.

Returned Fields:

All payment base fields (id, project_id, project_relationship_id, entity_id, payment_method_id, scheduled_date, status, total_amount, currency_code, notes, paid_at, cancelled_at, cancel_reason, external_id, is_statement_match, parent_payment_id, etc.)
Payment method fields: payment_method_type, payment_method_provider, payment_method_card_type, card_last_four, bank_account_number, payment_method_amount, linking_code, payment_method_display_label
Entity fields: entity_type, entity_business_name, entity_first_name, entity_last_name, entity_preferred_name, entity_trading_name, entity_bank_account_number, entity_bank_name, entity_bank_sort_code, entity_bank_payee_name, entity_bank_account_type, entity_bank_overseas_code
Paid-by user fields: paid_by_first_name, paid_by_last_name, paid_by_preferred_name
Scheduled-by user fields: scheduled_by_first_name, scheduled_by_last_name, scheduled_by_preferred_name
Computed field: balance — payment total_amount minus the sum of payment_reconciliations.amount for the same payment, mirroring v_transactions.balance. Lets the reconciliation UI render outstanding payment balances without a second query.
Voided reconciliations excluded: the balance subquery and the reconciled_transaction_codes / reconciled_reference_numbers aggregates only count payment_reconciliations rows where status = 'Active', so an allocation voided by a transaction void stops contributing.
One-line addresses (appended; both NULL when absent so exports degrade to blank):
entity_address — the payee entity's postal address (entities.address_id, rendered as a single comma-joined line via private.format_address). Visibility rides the entity row: ve.address_id is only non-NULL when the caller passed entities RLS for that entity, which by construction satisfies caller_can_access_address's "a visible entity references it" arm — so the view no longer re-evaluates that function per row (it used to pay it twice: once in a CASE gate, once via the addresses RLS on a join). NOT under the payment_details field-mask (consistent with v_entities exposing address_id).
entity_bank_address — the bank's postal address. Sourced from v_project_relationships.entity_bank_address (PR-first, mask-aware — see that view); falls back to the masked entity-level v_entities.bank_address only when the payment has no project_relationship_id, matching v_payment_details.bank_address so all payment surfaces agree.
entity_display_name — reuses v_entities.entity_name (computed inline there); it must never call format_entity_display_name(uuid) per row again (each call is a non-inlined INVOKER function that re-reads the entity under full RLS, ~10ms/row — one of the shapes that timed the Paid list out in production).
payment_method_owner_display_name — the newest ACTIVE payment_method_assignments row visible to the caller for the payment's method+project (deterministic start_date DESC, id tiebreak), resolved via one flat LEFT JOIN on a DISTINCT ON derived table over the BASE tables so the RLS policy subplans amortize once per statement. The previous shape — a correlated subquery over v_payment_method_assignments (three nested masked views) — cost ~1s PER ROW and must not be reintroduced. Visibility semantics are unchanged (verified against the old shape): an assignment whose ENTITY row isn't resolvable ranks with a NULL name; an assignment cascade-hidden by a sensitivity-restricted RELATIONSHIP (the assignments RLS row_is_visible_to_caller ancestor arm hides the row itself) falls back to the newest visible assignment — exactly as the old RLS-gated subquery behaved.
Performance invariants (the Paid-list timeout fix — keep these when editing this view): the single-row mask helpers are declared ROWS 1 so the planner probes the masked views by PK instead of materializing them; no per-row scalar function may re-establish caller context (display-name function, caller_can_access_address) — such work is either inlined, amortized into a flat join, or routed through an already-gated private helper. Correlated per-row subqueries against RLS-gated tables re-initialize the policies' hashed subplans on EVERY row (~45ms measured) — express per-row lookups as flat joins instead.

Payment Details View (`v_payment_details`)¶

Purpose: Flattened view of payment reconciliations joined with payments, entities (including bank details), payment methods, and users. One row per reconciliation line.

Use Case Example: Display payment scheduling list with line-level detail, including supplier/vendor names, bank details, and the user who scheduled each payment.

View Definition:

Joins payment_reconciliations with payments to get payment-level details
Joins with entities to resolve entity name; joins with project_relationships to source the payee bank details
Joins with v_payment_methods for payment method details (including amount and display_label)
Joins with users for paid-by and scheduled-by user details
Security invoker enabled to respect RLS policies
Filters WHERE payment_reconciliations.status = 'Active' so a reconciliation voided when its transaction was voided no longer surfaces as a payment line (prevents the duplicate/extra entry a voided transaction otherwise left behind)

Bank-detail sourcing: mirrors v_payments — entity_bank_* columns prefer project_relationships.payment_details with a per-field fallback to entities.payment_details, and sensitive masking follows the source of each field (PR row at project scope, entity row at organisation scope).

Returned Fields:

All payment reconciliation base fields (id, payment_id, transaction_id, approval_instance_id, amount, etc.)
All payment fields (project_id, project_relationship_id, payment_entity_id, scheduled_date, payment_status, payment_total_amount, currency_code, notes, paid_at, cancelled_at, cancel_reason)
Entity fields: entity_type, entity_business_name, entity_first_name, entity_last_name, entity_preferred_name, entity_trading_name, entity_bank_account_number, entity_bank_name, entity_bank_sort_code, entity_bank_payee_name, entity_bank_account_type, entity_bank_overseas_code
Payment method fields: payment_method_id, payment_method_type, payment_method_provider, payment_method_card_type, card_last_four, bank_account_number, payment_method_amount, linking_code, payment_method_display_label
Paid-by user fields: paid_by_first_name, paid_by_last_name, paid_by_preferred_name
Scheduled-by user fields: scheduled_by_first_name, scheduled_by_last_name, scheduled_by_preferred_name
CSV-import identity (sourced from joined payment row): external_id, is_statement_match
Computed fields: is_payment_reconciled — TRUE when payments.is_statement_match is TRUE AND the sum of payment_reconciliations.amount for the payment equals payments.total_amount. balance — per-payment outstanding amount (total_amount minus the sum of allocations); repeated on every reconciliation row that belongs to the same payment, so consumers must de-dup on payment_id if they sum across rows.
Payment Request Sheet fields (line-grain export of scheduled payments, one worksheet per currency, grouped by project relationship):
Transaction line (LEFT JOIN base transactions on transaction_id): transaction_reference_number, transaction_code, transaction_accounting_memo, transaction_ai_document_description, transaction_document_type, transaction_tax_total, transaction_total, transaction_date. All eight are plain base columns (no masking / no computed sensitivity involved), so the join reads base transactions under its own RLS — a PK probe — rather than v_transactions. Visibility is unchanged (v_transactions is security_invoker over the same transactions RLS), but this drops v_transactions' per-row machinery (exchange-rate + paid-aggregate laterals, four entity-name probes, planning weight) that contributed nothing but these eight columns. Do NOT reintroduce v_transactions here unless a masked or computed-sensitivity column is genuinely needed.
Payee contact (passed through from v_payments, which this view previously dropped): entity_phone_number, entity_email.
Pro-rated per-allocation amounts: reconciled_vat_amount = ROUND(txn.tax_total × (recon.amount / NULLIF(txn.total, 0)), 2) and reconciled_net_amount = recon.amount − reconciled_vat_amount. Correct for partial allocations (one invoice split across payments), so summing per payment never double-counts VAT. When txn.total is NULL/0 both amounts are NULL — consumers coalesce VAT→0 and Net→amount.
One-line addresses: payee_address and bank_address are passed through from v_payments (entity_address / entity_bank_address) so they inherit its gating: entity-row visibility for the postal address (see v_payments.entity_address — provenance-equivalent to caller_can_access_address) AND the payment_details field-mask for the bank address. An earlier definition resolved bank_address from the RAW project_relationships/entities JSONB with only the row-visibility gate, which let a row-visible but uncleared caller read a bank address the masking views hid — never reintroduce a raw payment_details read here. Behavioural persona pins: tests/08_payment_address_masking.sql.

Payment Messages View (`v_payment_messages`)¶

Purpose: Source-agnostic message-thread view scoped to a payment via the typed messages.payment_id FK. Mirrors v_approval_messages so the payments side has a consistent chat-shape join (entity-first display-name fallback, sender_user fallback) without callers re-joining entities + users at every consumption site.

Use Case Example: The payment-detail page's Messages tab renders this view through the <Chat> primitive; the Resolve drawer's ConversationCard reads the same shape via getPaymentReconciliationMessages. Future surfaces that want to list "every message tied to a payment" (notes, threads, approval-style queries) plug in by stamping messages.payment_id.

View Definition:

Selects from messages filtered on payment_id IS NOT NULL
JOINs payments → projects to expose project_id + organisation_id for scope filtering
LEFT JOINs entities (via messages.entity_id) and users (via messages.sender_user_id) to derive sender_name
sender_name resolves entity-first (preferred_name → first_name + last_name for persons; business_name otherwise), then falls back to the team-side user (preferred_name → first_name + last_name); NULL when neither column joins
ORDER BY messages.created_at so the chat surface reads oldest-first without re-sorting
Security invoker enabled to respect RLS policies

Returned Fields:

Message identity: message_id, payment_id, parent_id, root_message_id, origin_message_id
Scope: project_id, organisation_id
Content: content_text, direction, source, metadata
Sender: sender_user_id, sender_entity_id, sender_name (derived)
Timing: sent_at (alias for messages.created_at)

Filtering: callers narrow by source for thread-shape filtering — source = 'Supporting Document Request' is the only producer today, but metadata.outcome lets the same source carry sub-types (uploaded, missing_justification) when the Sana-side reply lands.

Payment Method Movements View (`v_payment_method_movements`)¶

Purpose: Per-PM movements timeline — one row per balance-affecting event with a SQL-computed running balance. Drives the Movements tab on the PM setup page for Petty Cash Floats; the most-recent row's running_balance matches v_petty_cash_floats.current_balance (same source data, same predicate) so the two surfaces stay aligned without UI-side reconciliation.

Use Case Example: PM setup page Movements tab renders a chronological timeline like Creation £150 / Bump +£80 / Outflow −£50 / Balance £180 — the UI reads running_balance directly off the view.

View Definition:

CTE UNIONs two row shapes:
Creation row (synthetic): one row per v_payment_methods row — payment_id is NULL, kind = 'creation', description = 'Creation', occurred_at = pm.created_at, signed_amount = COALESCE(pm.amount, 0). The UI branches on kind to suppress the View-Payment row action for this row.
Bump / Outflow rows: every payments row with payment_method_id IS NOT NULL AND status = 'Paid' — kind = 'bump' when is_cash_float_bump = true (signed positive), kind = 'outflow' otherwise (signed negative). Description resolves to notes → external_id → kind-literal fallback.
Outer SELECT adds running_balance via SUM(signed_amount) OVER (PARTITION BY payment_method_id ORDER BY occurred_at, kind-priority ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
kind-priority is a CASE ordering: creation = 0, bump = 1, outflow = 2 — so same-timestamped rows always read Creation → Bump → Outflow regardless of locale or enum text ordering.
Security invoker enabled to respect RLS policies (the underlying payments + v_payment_methods rows already enforce project scope).

Returned Fields:

Identity: payment_method_id, project_id, payment_id (NULL for the creation row)
Discriminator: kind (creation / bump / outflow)
Display: description, occurred_at, signed_amount, currency_code
Computed: running_balance

Type-agnostic: the view returns rows for any PM type. The UI gates the Movements tab visibility by PaymentMethodType so non-floats never see it.

Transactions View (`v_transactions`)¶

⚠️ Adding a column to v_transactions also requires updating createCancelledDuplicateTransaction in services/transaction/base.ts. That function reads a transaction via getTransaction() (which selects * from the view) and destructures the view-only columns OUT before inserting into the base transactions table. A new view-only column left in the spread makes the file-hash-dedup insert fail with PGRST204 (Could not find the '<col>' column of 'transactions' in the schema cache), silently breaking duplicate detection. Add the new column to that destructure alongside balance / has_sensitive_rule / sensitive_required_permissions / sensitive_auto_applied.

Purpose: Transactions with computed balance field. Balance is calculated as total minus the sum of paid reconciliation amounts.

Use Case Example: Any read query that needs the transaction balance (e.g., transaction detail view, payment scheduling).

View Definition:

Selects all columns from transactions table
LEFT JOINs a LATERAL subquery on payment_reconciliations (status = 'Active') + payments (status = 'Paid') to compute total_paid
Computes balance = COALESCE(total, 0) - COALESCE(total_paid, 0)
Security invoker enabled to respect RLS policies

Returned Fields:

All transaction base fields (id, project_id, entity_id, transaction_code, type, direction, total, status, etc.)
Display-name fields: entity_name (direction-aware: the transaction's own entity when inbound, the customer entity when outbound), reimbursement_entity_name, expense_entity_name. These are computed inline from flat entities LEFT JOINs (de/ce/re/ee), using the same name CASE as format_entity_display_name (NULL-guarded for the LEFT JOIN miss). The view must never call format_entity_display_name(uuid) per row again — each call is a non-inlined INVOKER function that re-reads the entity under full RLS (~10ms/row) and was one of the shapes that timed the Paid/list reads out in production.
Computed field: balance (total minus sum of paid reconciliation amounts)
Sensitivity fields: has_sensitive_rule (own rule exists), sensitive_required_permissions (effective, own + inherited, via get_record_effective_required_permissions), and sensitive_auto_applied — TRUE when ANY of the transaction's project_relationship / entity ancestor rules is auto_applied (marked automatically during ingest). Drives the "Auto Applied Sensitive" badge + the "Not sensitive" quick-lift button in the transaction view.
Multi-currency conversion fields (project currency): project_currency_code, exchange_rate_used (the effective per-transaction rate resolved by resolve_transaction_exchange_rate, honouring the project's exchange_rate_mode setting; NULL when a foreign transaction has no resolvable rate), and the native amounts converted into the project currency — converted_subtotal, converted_tax_total, converted_total, converted_balance. Converted amounts are NULL when exchange_rate_used is NULL (no rate) so the UI shows "no rate" instead of a wrong figure. Amounts are converted LIVE (nothing stored), so a later rate correction self-heals every figure. Same-currency transactions pass through with exchange_rate_used = 1.
line_item_mode (appended at the END of the select list — CREATE OR REPLACE VIEW cannot reorder or insert columns). A real base column of transactions, not a view-only computed one, so it must NOT be added to the createCancelledDuplicateTransaction destructure above. The list RPC query_transactions deliberately does NOT return it: the mode is read on the detail/processing path, not rendered in the list.

Schedulable Transactions View (`v_schedulable_transactions`)¶

Purpose: Approved transactions with remaining schedulable amount, excluding fully-paid and fully-scheduled transactions.

Use Case Example: Payment scheduling UI — shows only transactions that still have an amount available to schedule.

View Definition:

Selects from v_transactions (the masking view) with status = 'Approved', so it inherits the computed sensitivity columns and RLS masking
LEFT JOINs a LATERAL subquery on payment_reconciliations (status = 'Active') + payments (status = 'Paid') to compute balance
LEFT JOINs a correlated LATERAL subquery on payment_reconciliations (status = 'Active') + payments (status = 'Scheduled'), keyed WHERE pr.transaction_id = t.id, to compute amount_scheduled. This was previously a pre-aggregated GROUP BY transaction_id subquery joined flat — under RLS the policy-filtered outer scan estimates ~1 row, so the planner materialised the whole aggregation and re-ran the per-row join filter (rls-policies.md § "Views joining pre-aggregated subqueries"). The ungrouped correlated aggregate always returns exactly one row (COALESCE(sum(...), 0) handles the zero-reconciliation case) — identical output values, robust plan — supported by the idx_payment_reconciliations_transaction_id probe index
Computes balance = COALESCE(total, 0) - COALESCE(total_paid, 0)
Computes schedulable_amount = balance - amount_scheduled
Filters on the total's OWN direction: sign(COALESCE(total,0)) * schedulable_amount > 0. A bare schedulable_amount > 0 reads "still owed" only for a positive total — for a refund or credit note (negative total) the same expression is true exactly when the credit has been OVER-refunded and false while it is still outstanding, precisely inverted. Multiplying by the sign restores the intended meaning in both directions; a zero total yields zero and is excluded, as before. So a credit note appears with a NEGATIVE schedulable_amount while it remains unapplied, and an over-paid bill does not appear at all
Security invoker enabled to respect RLS policies
The Active-only reconciliation filter is defense-in-depth: Approved transactions never have voided allocations today (voiding sets the transaction to Cancelled), but the filter keeps paid/scheduled totals correct if a reconciliation is ever voided while its transaction stays Approved. v_project_outstanding_bills carries the same filter.
v_project_outstanding_bills (inbound + Approved) applies the SAME direction-aware shape to its own balance filter: sign(COALESCE(total,0)) * (COALESCE(total,0) - COALESCE(total_paid,0)) > 0. An unapplied credit note therefore appears with a NEGATIVE balance while it is outstanding, and an over-paid bill is excluded. Its amount_scheduled join carries the same correlated LATERAL shape as the one described above (it too was a flat-joined GROUP BY transaction_id subquery), so both views compute the scheduled rollup with one index-bounded probe per outer row instead of a materialised whole-table aggregation.

Returned Fields:

Transaction base fields: id, project_id, transaction_code, reference_number, type, direction, total, currency_code, transaction_date, due_date, ai_justification, entity_id
Relationship FK fields: supplier/customer/reimbursement/expense_project_relationship_id
Computed fields: balance (total minus paid), amount_scheduled (sum of reconciliation amounts for Scheduled payments), schedulable_amount (balance minus scheduled). All three are SIGNED: they carry the direction of the transaction's own total, so a credit note's remaining amount is negative
Sensitivity fields (from v_transactions): has_sensitive_rule (own rule exists) and sensitive_required_permissions (effective permissions, populated when sensitive directly OR inherited from a sensitive parent) — drive the Sensitive badge on the scheduling table

Project Relationships View (`v_project_relationships`)¶

Purpose: Comprehensive view of project relationships with full entity details (including bank fields), role definitions, user references, and project info. One row per relationship. Used as the source of truth for getProjectRelationshipById so all consumers get the server-computed relationship_type field for free.

Use Case Example: Display a project's team members with their entity details, roles, bank information, and who invited/created each relationship.

View Definition:

Joins project_relationships with the masking view v_entities for full entity details including bank fields, and joins private.project_relationship_masked_fields_all() (the once-per-statement bulk twin, ON prmf.project_relationship_id = pr.id) for the relationship's own payment_details — field masks (and subject self-visibility) are applied in that DEFINER helper, not inline in this view; the raw payment_details column is not readable by authenticated. The bulk twin replaced the per-row LEFT JOIN LATERAL private.project_relationship_masked_fields(pr.id) (see "Field-masking chokepoint helpers"); output is byte-identical
Joins with role_definitions for role metadata (title, department, on-screen status)
Joins with users for created-by, updated-by, invited-by, and cancelled-by user details
Joins with projects for project metadata
Includes a subquery for role_category_ids from role_definition_categories
Computes relationship_type based on entity type, customer flag, and role is_on_screen flag (identical CASE to the lean v_project_relationship_types view)
entity_display_name reuses v_entities.entity_name (an inline name expression), never a per-row format_entity_display_name(uuid) call — matching v_payments
Exposes the metadata jsonb and accepted_at columns from the base table (added in migration 20260403000000) so UI consumers (RelationshipEmergencyContactTab, IdentityDocumentMetadata, the Sana acceptance flow) can read them without falling back to a raw table read
Security invoker enabled to respect RLS policies

Returned Fields:

All project_relationship base fields (id, project_id, entity_id, role_definition_id, role_title_override, status, metadata, accepted_at, expense_reimbursement_code, invited_at, cancelled_at, etc.)
Computed fields: relationship_type (customer, supplier, cast, or crew)
Entity fields: entity_type, entity_organisation_id, entity_user_id, entity_first_name, entity_last_name, entity_preferred_name, entity_business_name, entity_registration_number, entity_trading_name, entity_email, entity_phone_number, entity_bank_account_number, entity_bank_name, entity_bank_sort_code, entity_bank_payee_name, entity_bank_account_type, entity_is_active, entity_is_secondary_supplier, entity_is_customer, entity_search_query, entity_tax_number, entity_citizenship_country_code, entity_residency_country_code, entity_date_of_birth, entity_address_id, entity_metadata
entity_bank_address (appended): one-line postal address of the payee's bank, resolved like the other entity_bank_* columns — PR-level payment_details->bank->address_id first (a present-but-masked override yields NULL without falling back), else the entity-level masked v_entities.bank_address. The PR-level arm renders the address via private.format_address(<bank address_id from the MASKED payment_details>) (provenance case #2 of that helper — mirrors v_entities.bank_address), NOT a correlated subquery on addresses; the earlier direct-addresses subquery re-evaluated caller_can_access_address (~17ms/call, ~220ms on the prod payments list since v_payments reads this column) even though the id already came from a mask-gated pointer. Deliberately NOT added to the query_project_relationships RPC (the relationships list has no consumer for it; it is consumed via v_payments / v_payment_details) — add it to the RPC + transform per the dual-list-path rule only when a relationships-list consumer appears.
entity_display_name: the linked entity's display name — reuses v_entities.entity_name (computed inline there); it must never call format_entity_display_name(uuid) per row again (each call re-reads the entity under full RLS, ~10ms/row — one of the shapes that timed the pending-approvals / list reads out in production). For a pure count/grouping by relationship_type, read the lean v_project_relationship_types view instead — it avoids this view's masked-field machinery entirely.
deal_terms (appended): plain pass-through of project_relationships.deal_terms (free-text engagement terms). Unmasked/non-sensitive by design. Like expense_reimbursement_code, it is NOT in the query_project_relationships RPC or the flat-list transform — it is a detail-read field (getProjectRelationshipById); add it to the RPC + transform per the dual-list-path rule only when a relationships-list consumer appears.
Role fields: role_title, role_description, role_is_on_screen, role_can_be_vendor, role_can_be_person, role_department_id, role_is_hod, role_is_chain_of_title, role_is_not_common, role_is_active, role_category_ids
Created-by user fields: created_by_email, created_by_first_name, created_by_last_name, created_by_preferred_name
Updated-by user fields: updated_by_email, updated_by_first_name, updated_by_last_name, updated_by_preferred_name
Invited-by user fields: invited_by_email, invited_by_first_name, invited_by_last_name, invited_by_preferred_name
Cancelled-by user fields: cancelled_by_email, cancelled_by_first_name, cancelled_by_last_name, cancelled_by_preferred_name
Project fields: project_title, project_url_key, project_organisation_id, project_is_active, project_country_code — note: project_status was removed as the status column was dropped from the projects table

Project Relationship Types View (`v_project_relationship_types`)¶

Purpose: Lean relationship-classification surface for counts and grouping (e.g. the Reports stakeholder KPI tiles). One row per project_relationship visible to the caller, carrying only id, project_id, is_active, and the derived relationship_type.

Why it exists: countActiveProjectRelationshipsByType previously grouped/counted v_project_relationships, whose LEFT JOIN LATERAL private.project_relationship_masked_fields(pr.id) (and the nested v_entities mask helper) runs per row even for a pure count — function RTEs are never join-eliminated, so counting four integers took ~4.16s in production. This view classifies with the identical CASE to v_project_relationships.relationship_type but touches no masked-field machinery.

View Definition:

FROM project_relationships pr LEFT JOIN entities e ON e.id = pr.entity_id LEFT JOIN role_definitions rd ON pr.role_definition_id = rd.id
The join to entities is a LEFT join by design: a relationship visible to the caller whose entity row is RLS-hidden must still be counted. When e.* is NULL the CASE falls to its ELSE 'crew' arm — exactly as v_project_relationships classifies the same all-NULL-entity case (pinned by tests/14).
Security invoker enabled; RLS on the underlying tables gates rows.

Grants: SELECT to authenticated, service_role; NOT anon.

Payment Method Assignments View (`v_payment_method_assignments`)¶

Purpose: Comprehensive view of payment method assignments with full payment method details, linking codes, and user references. One row per assignment.

Use Case Example: Display all card/bank assignments for a crew member, showing the payment method name, card last four, digital card number, and who created each assignment.

View Definition:

Joins payment_method_assignments with v_payment_methods for method details (raw columns + computed display_label + amount)
LEFT JOINs project_relationships + v_entities so the assignment carries its entity context (entity_id, entity_name, project_relationship_is_active) — used by the CSV-import Resolve step and petty-cash float reporting
LEFT JOINs payments (status = 'Paid') via correlated subquery to compute the running float balance
Security invoker enabled to respect RLS policies

Returned Fields:

All payment_method_assignment base fields (id, project_relationship_id, payment_method_id, digital_card_number, start_date, end_date, notes, created_at, updated_at, created_by_user_id, updated_by_user_id)
Payment method fields: project_id, payment_method_type, payment_method_provider, payment_method_card_type, card_last_four, cardholder_name, bank_account_number, bank_sort_code, bank_payee_name, linking_code, payment_method_is_active, payment_method_display_label
Created-by user fields: created_by_first_name, created_by_last_name, created_by_preferred_name
Updated-by user fields: updated_by_first_name, updated_by_last_name, updated_by_preferred_name
Entity-side fields (NULL when the assignment points at a soft-deleted or now-inactive project_relationship): entity_id, project_relationship_is_active, entity_name
Float fields:
payment_method_amount — the PM's tracked starting amount. Populated for petty-cash floats (the original cash hand-out); NULL for card / bank / other PMs that don't carry a float balance concept.
balance — running float balance: payment_method_amount minus the sum of Paid payments routed through the same payment_method_id. NULL when payment_method_amount is NULL (non-float PMs), so the UI should not render a balance for those rows.

Budget Item Daily Allocations View (`v_budget_item_daily_allocations`)¶

Purpose: Daily allocations with computed totals from linked transaction items.

Use Case Example: Display a budget item's daily allocation showing the budgeted total, actual spend from linked transaction items, and remaining balance.

View Definition:

Selects all columns from budget_item_daily_allocations
actual_total is a correlated LEFT JOIN LATERAL aggregate keyed on bati.budget_item_daily_allocation_id = bida.id, NOT an uncorrelated GROUP BY subquery. This is the same fix v_budget_items carries, for the same reason: under RLS the policy-filtered outer scan estimates ~1 row, so the planner routinely picks a nested loop, and a pre-aggregated subquery then re-runs the aggregation over the caller's entire visible link set once per outer row. Measured on a two-budget local dataset (2190 allocations / 3048 links) a single budget's read re-executed the grouped aggregate 1095 times — 675ms for the grouped shape, and 5994ms for a variant that additionally joined the budget tree inside the subquery to make an outer budget_id qual pushable — versus 132ms for the LATERAL form. Do not "optimise" this back into a grouped subquery, and do not try to make a budget_id filter push down by adding budget_id as a grouping key + a two-column join: pushdown does happen, but it does not stop the per-outer-row re-execution and it makes each re-execution far more expensive. The correlated probe rides idx_budget_alloc_tx_items_allocation_id, so a per-row re-execution touches only that allocation's links and the nested-loop plan is harmless by construction. An ungrouped aggregate always returns exactly one row, so LEFT JOIN LATERAL … ON true preserves the absent-group semantics the outer COALESCE(act.actual_total, 0) already handles.
The aggregate joins through transaction_items → transactions — the base table, not v_transactions. Only status and the exchange rate are needed, so it calls private.resolve_transaction_exchange_rate(t.project_id, t.currency_code, t.exchange_rate_id) directly (exactly what v_transactions.exchange_rate_used resolves) rather than materialising the full transactions view per contributing row.
Filters WHERE t.status NOT IN ('Cancelled','Rejected') so voided/rejected transactions never consume budget. Per-day actual_total reconciles with v_budget_items.actual_total.
Computes total, actual_total, balance, and estimated_cost_balance
security_invoker is load-bearing, not stylistic: actual_total is summed from RLS-gated transactions / transaction_items / budget_allocation_transaction_items, so spend from a transaction the caller cannot see must never reach the total. A DEFINER rewrite would leak it.
Backed by indexes idx_bati_allocation_id, idx_bida_budget_item_id, idx_bida_budget_item_created, idx_bi_budget_header_id, idx_pd_production_phase_id
Semantics are pinned behaviourally by tests/10_allocation_link_sync.sql S15–S20 (currency conversion, Cancelled/Rejected exclusion, multi-link summation, links summing to zero vs. no links at all, estimated_cost_balance arithmetic) plus a security_invoker reloption pin.

Returned Fields:

All base fields from budget_item_daily_allocations
Computed fields:
total = quantity * rate
actual_total = SUM(budget_allocation_transaction_items.amount × resolved exchange rate), already in the project currency
balance = total - actual_total
estimated_cost_balance = estimated_cost - actual_total - canceled_estimated_cost

Budget Items View (`v_budget_items`)¶

Purpose: Budget items with computed totals aggregated from all daily allocations.

Use Case Example: Display a budget item showing the total allocated across all production days, average/max quantities, and actual spend.

View Definition:

Selects all columns from budget_items
Aggregates are correlated per item via LEFT JOIN LATERAL keyed on budget_item_id, probing the allocation/transaction indexes (idx_budget_item_daily_allocations_budget_item_id, idx_budget_alloc_tx_items_allocation_id, idx_transaction_items_budget) so a filtered read touches only the queried items' rows. The earlier uncorrelated GROUP BY shape looked cheaper but was re-executed once per outer row when RLS misestimation made the planner pick a nested-loop join — a single 465-item budget read re-ran the whole-visible-set actuals aggregation 465 times (~8.7s). Full-view scans cost the same in both shapes now that the probe indexes exist (they did not when the pre-aggregated shape was introduced)
Security invoker enabled to respect RLS policies

Returned Fields:

All base fields from budget_items
Computed fields:
allocated_total = SUM of daily allocation totals (qty * rate)
allocated_avg_quantity = AVG of daily allocation quantities
allocated_max_quantity = MAX of daily allocation quantities
estimated_cost_total = SUM of daily allocation estimated_costs
actual_total = SUM of daily allocation actual_totals
estimated_cost_balance = estimated_cost_total - actual_total

Actuals exclusion: Both actuals paths — daily-allocation (budget_allocation_transaction_items) and direct (transaction_items.subtotal) — join through to transactions and skip rows whose parent transaction is in a terminal state (Cancelled, Rejected). Voided/rejected transactions never consume budget. v_budget_headers, v_budgets, and v_project_weekly_spend inherit this filter transitively.

Multi-currency (Multi-Currency Conversions V1): budgets display in the project currency, so both actuals paths convert each transaction line in place before summing (convert-then-sum): SUM(line_amount × COALESCE(resolve_transaction_exchange_rate(t.project_id, t.currency_code, t.exchange_rate_id), 1)). actual_total is therefore already project-currency; v_budget_headers / v_budgets SUM(vi.actual_total) inherit it. The rate honours the project's exchange_rate_mode (live vs stamped); COALESCE(rate, 1) is a no-op for same-currency and a native fallback for an unresolvable rate. The two drill-down detail views (v_budget_item_transaction_details, v_budget_allocation_transaction_details) convert the same way, reusing v_transactions.exchange_rate_used. v_budget_item_daily_allocations produces the identical figure but calls private.resolve_transaction_exchange_rate(...) on the base transactions table directly — it needs only status and the rate, so joining the full v_transactions view per contributing line was pure overhead. Both spellings resolve the same rate; keep them in step when the resolver's argument list changes.

Budget Headers View (`v_budget_headers`)¶

Purpose: Budget headers with computed totals from all items including nested sub-headers at any depth.

Use Case Example: Display an "Above the Line" header showing the total cost including all nested sub-headers (Cast, Director, Producers) and their items.

View Definition:

Rolls up in two uncorrelated CTEs keyed by budget_id: item_rollup aggregates v_budget_items once per (budget_id, budget_header_id), then rollup_to_ancestor redistributes each header's totals onto every ancestor via the recursive header_ancestors walk (which the sensitivity column already computes). The final join is agg.root_header_id = bh.id AND agg.budget_id = bh.budget_id.
The budget_id conjunct in that join is load-bearing for performance, not for correctness. A header tree never spans budgets, so it can never drop a contributing row; it exists so an outer WHERE budget_id = … propagates into the rollup CTEs and the planner reaches the items through idx_budget_items_budget_header_id, probing only the requested budget's headers. Without it the aggregate consumes the caller's entire visible item set.
This replaced an older header_tree descendant CTE joined to a GROUP BY subquery that carried no key the outer filter could restrict. Ancestor-attribution and descendant-aggregation produce the identical partition of items across headers, and the second recursive walk was duplicated work.
Do not convert these rollups to a per-header LEFT JOIN LATERAL. The sibling rewrite of v_budget_item_daily_allocations correctly went the other way (grouped subquery → LATERAL) because there the probe is a single index-driven lookup on a base table. That reasoning does not transfer: here the probed relation is v_budget_items, whose own body carries nested per-item allocation LATERALs and costs ~36ms to materialise, so a per-header LATERAL pays that cost once per outer row. Measured on a four-budget local dataset (496 items / 4380 allocations / 6096 links), a per-header LATERAL bounded by the outer budget ran 1252ms against 37ms for the CTE shape. The invariant that matters is "the item view is scanned exactly once per statement, and that scan is restricted to the requested budget" — only an uncorrelated CTE keyed by budget_id delivers both.
Security invoker enabled to respect RLS policies — every total is summed under the caller's RLS on budget_items / budget_item_daily_allocations / transactions, so rows the caller cannot see contribute nothing.
Semantics and the bounded-walk invariant are pinned by packages/database/supabase/tests/17_budget_rollup_views.sql (multi-level subtree rollup, exactly-once attribution, daily-vs-direct actuals, original_total fallback, canceled subtraction, per-line FX, cross-budget isolation, security_invoker).

Returned Fields:

All base fields from budget_headers
Computed fields:
total = SUM of allocated_total from all items under this header and all descendants
estimated_cost_total = SUM of estimated_cost from all descendant items
actual_total = SUM of actual_total from all descendant items
balance = total - actual_total
estimated_cost_balance = estimated_cost_total - actual_total

Budgets View (`v_budgets`)¶

Purpose: Budgets with computed totals from all items across all headers.

Use Case Example: Display the total budget amount, estimated costs, actual spend, and remaining balance for a project budget.

View Definition:

Selects all columns from budgets
LEFT JOINs an uncorrelated CTE grouped by v_budget_items.budget_id, so an outer id / project_id filter bounds the aggregation to the requested budget(s) instead of every visible budget item. The item view already exposes budget_id, so the intermediate budget_headers join the older shape used purely to reach that column is gone.
The previous shape grouped by bh.budget_id through a budget_headers join and carried no key the outer filter could restrict, so WHERE id = <b> filtered only the outer relation while the aggregate consumed the caller's whole visible item set. Under RLS the policy-filtered outer scan estimates ~1 row, so the planner chose a nested loop and re-materialised that aggregate once per outer row: on a four-budget local dataset a single WHERE id = <b> read re-ran the item view 47 times for 2218ms, against 34ms for the current shape.
Do not convert this rollup to a per-budget LEFT JOIN LATERAL — see the note under v_budget_headers above; v_budget_items is far too expensive to re-materialise per outer row.
Security invoker enabled to respect RLS policies — totals are summed under the caller's RLS, so rows the caller cannot see contribute nothing.
Semantics pinned by packages/database/supabase/tests/17_budget_rollup_views.sql (including allocated_total counting only allocation-backed items, the daily-vs-direct actuals switch on enable_daily_allocations, and cross-budget isolation).

Returned Fields:

All base fields from budgets
Computed fields:
total = SUM of all items' allocated_total across all headers
estimated_cost_total = SUM of all items' estimated_cost_total
actual_total = SUM of all items' actual_total
balance = total - actual_total
estimated_cost_balance = estimated_cost_total - actual_total

Budget Allocation Transaction Details View (`v_budget_allocation_transaction_details`)¶

Purpose: Transaction details linked to budget daily allocations via budget_allocation_transaction_items. Used for tracking actuals in daily allocation mode.

Use Case Example: Display transaction line items that contribute to the actual spend for a specific daily allocation (e.g., Day 3 catering expenses).

View Definition:

JOINs budget_allocation_transaction_items → transaction_items → v_transactions (the masking view, so sensitivity + row-masking are inherited)
LEFT JOINs entities for supplier name
WHERE transactions.status NOT IN ('Cancelled','Rejected') — voided/rejected transactions are excluded so drill-downs reconcile with v_budget_items.actual_total
Security invoker enabled to respect RLS policies

Returned Fields:

id - budget_allocation_transaction_items.id
allocation_id - budget_item_daily_allocation_id
transaction_item_id
amount - from budget_allocation_transaction_items
item_description - from transaction_items
item_subtotal - transaction_items.subtotal
budget_item_id - from transaction_items
transaction_id
transaction_date
transaction_created_at - transactions.created_at
transaction_type - transactions.type
transaction_direction - transactions.direction
transaction_code - transactions.transaction_code
reference_number - transactions.reference_number
supplier_entity_id - transactions.entity_id
supplier_name - COALESCE(trading_name, business_name, first + last name)
has_sensitive_rule - from v_transactions (own rule exists)
sensitive_required_permissions - from v_transactions; effective permissions (own + inherited from a sensitive parent) — drives the Sensitive badge on the budget transaction tables. The view joins v_transactions (not base transactions) so cascade semantics + row-masking match everywhere.
allocation_budget_item_id - the allocation's own budget_item_id (from budget_item_daily_allocations); always equal to budget_item_id above (the line item's), an invariant enforced by trg_budget_allocation_transaction_items_enforce_item_match and maintained by trg_transaction_items_sync_allocation_links. Exposed so callers can assert the two match.

Budget Item Transaction Details View (`v_budget_item_transaction_details`)¶

Purpose: Transaction details linked directly to budget items via transaction_items.budget_item_id. Used for tracking actuals when daily allocations are disabled.

Use Case Example: Display all transaction line items charged against a budget item when the budget does not use daily allocation mode.

View Definition:

JOINs transaction_items → v_transactions (the masking view, so sensitivity + row-masking are inherited)
LEFT JOINs entities for supplier name
WHERE transaction_items.budget_item_id IS NOT NULL AND transactions.status NOT IN ('Cancelled','Rejected') — voided/rejected transactions are excluded so drill-downs reconcile with v_budget_items.actual_total
Security invoker enabled to respect RLS policies

Returned Fields:

transaction_item_id
budget_item_id
item_description - from transaction_items
amount - transaction_items.subtotal
transaction_id
transaction_date
transaction_created_at - transactions.created_at
transaction_type - transactions.type
transaction_direction - transactions.direction
transaction_code - transactions.transaction_code
reference_number - transactions.reference_number
supplier_entity_id - transactions.entity_id
supplier_name - COALESCE(trading_name, business_name, first + last name)
has_sensitive_rule - from v_transactions (own rule exists)
sensitive_required_permissions - from v_transactions; effective permissions (own + inherited from a sensitive parent) — drives the Sensitive badge on the budget transaction tables. The view joins v_transactions (not base transactions) so cascade semantics + row-masking match everywhere.

Project Integrations View (`v_project_integrations`)¶

Purpose: Project integrations with provider details and supported country codes.

Use Case Example: Display the list of integrations for a project with provider name, logo, auth method, and which countries the provider supports.

View Definition:

JOINs project_integrations -> integration_providers
Subquery aggregates integration_provider_countries into an array
Security invoker enabled to respect RLS policies

Returned Fields:

All project_integrations columns (id, project_id, integration_provider_id, organisation_id, connection_status, is_auto_enabled, is_active, connected_at, disconnected_at, external_account_id, external_account_name, external_metadata, connected_by_user_id, disconnected_by_user_id, created_at, updated_at, created_by_user_id, updated_by_user_id)
provider_code - integration_providers.code
provider_name - integration_providers.name
provider_description - integration_providers.description
provider_logo_attachment_id - integration_providers.logo_attachment_id
provider_auth_method - integration_providers.auth_method
provider_credential_scope - integration_providers.credential_scope
provider_base_url - integration_providers.base_url
provider_sandbox_base_url - integration_providers.sandbox_base_url
provider_documentation_url - integration_providers.documentation_url
provider_developer_portal_url - integration_providers.developer_portal_url
provider_developer_portal_label - integration_providers.developer_portal_label
provider_setup_instructions - integration_providers.setup_instructions
provider_entity_urls - integration_providers.entity_urls (JSONB map of entity type to production URL template)
provider_sandbox_entity_urls - integration_providers.sandbox_entity_urls (JSONB map of entity type to sandbox URL template)
provider_oauth_grant_type - extracted from integration_providers.oauth_config->>'grant_type' (e.g., 'authorization_code', 'client_credentials')
provider_country_codes - TEXT[] array of country codes from integration_provider_countries

Integration Sync Logs View (`v_integration_sync_logs`)¶

Purpose: Sync logs with provider and feature details for UI display.

Use Case Example: Display sync history with provider name, feature name, status, duration, and record counts for troubleshooting.

View Definition:

JOINs integration_sync_logs -> project_integrations -> integration_providers
LEFT JOINs project_integration_features -> integration_features
Security invoker enabled to respect RLS policies

Returned Fields:

All integration_sync_logs columns (id, project_integration_id, project_integration_feature_id, sync_trigger, sync_direction, status, started_at, completed_at, duration_ms, records_processed, records_created, records_updated, records_failed, records_skipped, error_message, error_details, webhook_event_type, request_metadata, response_metadata, triggered_by_user_id, created_at)
provider_code - integration_providers.code
provider_name - integration_providers.name
feature_code - integration_features.code
feature_name - integration_features.name
project_id - project_integrations.project_id

Shareable Integration Credentials View (`v_shareable_integration_credentials`)¶

Purpose: Shows per-project credentials that can be reused across projects in the same organisation. Used when setting up OAuth2 integrations to allow copying app credentials (client_id/client_secret) from another project.

Use Case Example: When setting up QuickBooks on Project B, the user can see credentials from Project A (same org) and copy them instead of re-entering client_id/client_secret.

View Definition:

JOINs integration_credentials -> project_integrations -> projects
Filters: credential_scope = 'per_project', is_active = true, can_be_shared = true, client_id_secret_id IS NOT NULL
Security invoker enabled to respect RLS policies

Returned Fields:

id - integration_credentials.id
integration_provider_id - integration_credentials.integration_provider_id
name - integration_credentials.name
project_integration_id - integration_credentials.project_integration_id
project_id - project_integrations.project_id
organisation_id - project_integrations.organisation_id
project_name - projects.name
client_id_secret_id - integration_credentials.client_id_secret_id
client_secret_secret_id - integration_credentials.client_secret_secret_id
additional_secrets - integration_credentials.additional_secrets
created_at - integration_credentials.created_at

Project Integration Send To Targets View (`v_project_integration_send_to_targets`)¶

Purpose: Provides a pre-joined view of enabled integration features that support manual sync, with provider details and effective trigger config. Entity-agnostic — consumers filter by entity/type/direction client-side.

Use Case Example: On a transaction detail page, query this view filtered by project_id to determine which "Send To" integration buttons to display (e.g., "Send to QuickBooks").

View Definition:

JOINs project_integration_features -> integration_features -> project_integrations -> integration_providers -> attachments
Filters: is_enabled = true, is_active = true, connection_status = 'Connected', 'on_manual' = ANY(supported_triggers)
Uses COALESCE(pif.trigger_config, if2.default_trigger_config) for effective trigger config
Security invoker enabled to respect RLS policies

Returned Fields:

project_integration_feature_id - project_integration_features.id
project_integration_id - project_integration_features.project_integration_id
integration_feature_id - project_integration_features.integration_feature_id
trigger_config - COALESCE(project-level, feature default) trigger configuration
feature_code - integration_features.code
feature_name - integration_features.name
supported_triggers - integration_features.supported_triggers
project_id - project_integrations.project_id
provider_code - integration_providers.code
provider_name - integration_providers.name
provider_logo_url - attachments.storage_path_or_url
provider_entity_urls - integration_providers.entity_urls (JSONB map of entity type to production URL template)
provider_sandbox_entity_urls - integration_providers.sandbox_entity_urls (JSONB map of entity type to sandbox URL template)

Transaction Audit Trail View (`v_transaction_audit_trail`)¶

Purpose: Unified audit trail timeline for transactions, combining events from creation, processing, issues, approvals, and payments into a single chronological view.

Use Case Example: Display a full lifecycle timeline on the transaction detail page showing when a transaction was created, processed, issues detected/resolved, sent for approval, approved/rejected, and paid.

View Definition:

UNION ALL across transactions, processing_jobs, processing_issues, approval_requests, approval_instances, payments/payment_reconciliations
LEFT JOINs to users and entities for actor names
Filters processing tables by processable_type = 'transactions' and approval tables by record_type = 'transactions'
Approval-instance events respect activated_at: instances are created up-front for every tier at submission, so a Pending instance whose tier the flow has not reached (activated_at IS NULL) emits NO event, and the approval_assigned event is dated from activated_at — matching v_approval_timeline
Security invoker enabled to respect RLS policies

Returned Fields:

transaction_id (UUID) - The transaction this event belongs to
project_id (UUID) - Project for RLS filtering
event_type (TEXT) - One of: transaction_created, processing_started, processing_completed, processing_failed, issue_detected, issue_resolved, approval_submitted, approval_assigned, approval_approved, approval_rejected, approval_queried, approval_skipped, payment_scheduled, payment_paid, payment_cancelled
actor_user_id (UUID, nullable) - User who performed the action (NULL for system events)
actor_name (TEXT, nullable) - Display name of the actor ("System" for automated events)
event_detail (TEXT, nullable) - Additional context (e.g. issue title, tier number, payment amount)
event_note (TEXT, nullable) - Notes (e.g. error message, decision note, cancel reason)
related_entity_id (UUID, nullable) - ID of the source record (processing_job, approval_instance, payment, etc.)
event_at (TIMESTAMPTZ) - When the event occurred

Database Functions & Triggers¶

Auth Sync Functions¶

handle_new_auth_user() - Creates users and entity records when new auth user signs up, also creates Personal organisation if needed
handle_auth_user_update() - Syncs email/phone changes from auth.users to users table
handle_user_update() - Syncs name changes from users table to entities table (skips internal users)
handle_auth_user_delete() - Invited placeholder users (is_invite_pending=true) are hard-deleted via delete_invited_user; accepted users are soft-deleted (auth_deleted=true, email/phone suffixed to free unique indexes)
delete_invited_user(user_id) - Hard-deletes an invited placeholder user along with their conversations, Personal entity, user_settings, and Personal organisation. No-op unless is_invite_pending=true. Used when swapping an entity's linked user during a phone-number change
prevent_sync_field_updates() - Prevents direct updates to email/phone fields in users table

Project Management Functions¶

handle_project_setup() - Automatically creates team member, relationship, default budget and schedule when project is created
generate_entity_code(name, entity_type) - Consolidated function to generate unique codes for entities (projects, organisations) from their names
set_project_code() - Trigger function that automatically generates project_code using generate_entity_code if not provided
set_organisation_code() - Trigger function that automatically generates organisation_code using generate_entity_code if not provided
generate_url_key() - Generates a unique 6-character alphanumeric key for URL use (e.g., "a3b7x9")
set_url_key() - Trigger function that automatically generates url_key for entities if not provided
handle_production_phase_days() - Manages production days when phases are created, updated, or deleted. Uses a soft-delete approach (is_removed flag) instead of hard DELETE when phases are shortened. Skips day generation entirely when start_date or number_of_days is NULL. Reactivates previously soft-deleted days before creating new ones when a phase is extended. Uses positional ordering (ROW_NUMBER over calendar_date) instead of day_number for date recalculation, correctly handling Travel/Rest days (NULL day_number) and cross-phase-renumbered Working days. Defers the UNIQUE constraint on (production_phase_id, calendar_date) during date shifts.

Feature Flag Functions¶

is_registration_enabled() - Returns boolean indicating whether user registration is enabled. Toggle by replacing the function body with SELECT false::boolean to disable registration. Used by middleware to block /register route and by login page to hide the sign-up link.

Utility Functions¶

update_updated_at_column() - Updates the updated_at timestamp on record modification
get_user_project_menu() - Returns personalized menu structure based on user permissions
cleanup_e2e_test_data() - Removes E2E test data from all tables
pluralize_type(val TEXT) - Pluralizes English words for display in approval tab labels
Handles -y → -ies pattern (e.g., "Query" → "Queries")
Handles -s, -sh, -ch, -x, -z → -es pattern (e.g., "Tax" → "Taxes")
Default: adds -s (e.g., "Invoice" → "Invoices")
Used by approval views for tab_label column

Full-Text Search Functions¶

build_fts_query(search_term TEXT) - Builds FTS query string with proper prefix matching for regular words and email addresses
Returns formatted query string for use with to_tsquery
Handles email addresses by splitting on @ and adding prefix matching
Adds :* to each word for prefix matching
Returns NULL for empty search terms
query_transactions(p_project_id, p_type, p_status, p_source, p_direction, search_term, p_limit, p_offset, p_count_only) - Query transactions with optional FTS search
⚠️ The transactions list has TWO data paths: the default (no column filter/sort) path calls THIS RPC; the filtered/sorted path calls findMany on v_transactions with transactionsListSelect (services/transaction/base.ts). A column added to the view select is silently absent from the DEFAULT list unless it is ALSO added to this RPC's RETURNS TABLE + SELECT body — and because adding to RETURNS TABLE changes the return type, that requires DROP FUNCTION + recreate (then re-GRANT). Keep both paths in sync.
Queries from v_transactions view (includes computed balance field)
Searches across transaction search_query, linked entity search_query, customer entity search_query, and reimbursement entity search_query
Returns results with search ranking when search term provided
Returns entity_data, customer_entity_data, and reimbursement_entity_data as JSONB (entity linked via reimbursement_project_relationship_id → project_relationships → entities)
Returns has_sensitive_rule (own rule exists) and sensitive_required_permissions (effective permissions, populated when sensitive directly OR by inheriting from a sensitive parent) so the list can show the Sensitive badge for inherited-sensitive rows
Passes through the multi-currency conversion columns from v_transactions (project_currency_code, exchange_rate_used, converted_subtotal, converted_tax_total, converted_total, converted_balance) so the default list has the same converted figures as the filtered path
Passes through the display-name columns from v_transactions (entity_name, reimbursement_entity_name, expense_entity_name) rather than recomputing them — the view now computes these inline, so the RPC must NOT reintroduce a per-row format_entity_display_name(uuid) call
Supports count-only mode for efficient pagination
Orders by search rank (when searching) or transaction date
query_project_relationships(p_project_id, p_entity_type, search_term, p_limit, p_offset, p_count_only) - Query project relationships with optional FTS search
Searches across entity search_query, role title, and role title override
Returns full entity and role data with search ranking
Supports count-only mode for efficient pagination
Orders by search rank (when searching) or created date
⚠️ Same DUAL list-path as transactions: the default (no column filter/sort) relationships list calls THIS RPC; the filtered/sorted path calls findMany on v_project_relationships (services/project/base.ts). A column added to the view is silently absent from the DEFAULT list unless it is ALSO added to this RPC's RETURNS TABLE + BOTH SELECT branches (the main query AND the count-only NULL row) — DROP FUNCTION + recreate + re-GRANT (anon, authenticated, service_role). transformFlatRowToRelationship (+ ProjectRelationshipFlatBase) must also map the new field, or the RPC path reads undefined. Keep view + RPC + transform in sync.

Currency Conversion Functions¶

resolve_transaction_exchange_rate(p_project_id, p_transaction_currency, p_exchange_rate_id) - Returns the effective multiplier to convert an amount from a transaction's currency into its project currency (Multi-Currency Conversions V1). SECURITY INVOKER + STABLE + search_path=''.
Reads the project's mode from the projects.exchange_rate_mode column (NOT NULL, default 'live').
live (and stamped with a NULL stamp): the currently-active project_currency_rates row (valid_to IS NULL) for (project, from_currency). A rate correction moves every figure.
stamped (with a non-NULL p_exchange_rate_id): the rate on that immutable project_currency_rates version — a decision-time snapshot that a later correction leaves frozen.
Same-currency (transaction currency = project currency) ⇒ 1; unresolvable foreign currency ⇒ NULL (callers show "no rate" instead of a wrong figure).
Single home for the mode/stamp/fallback logic — v_transactions and the budget transaction-detail views call it instead of re-implementing the CASE. Granted to authenticated + service_role; revoked from anon.
Implementation: the body lives in private.resolve_transaction_exchange_rate (SECURITY DEFINER twin), and the converting views call the twin DIRECTLY. The function runs once per visible transaction row inside the views' aggregates; as a plain INVOKER body its projects/project_currency_rates reads re-evaluated those tables' RLS on every call (~7ms each — an 11.5s read for a 2,000-item budget, caught by the budget-import read gate). The DEFINER twin does keyed lookups without per-call policy evaluation and is safe there because the security_invoker views already establish per-row visibility. It lives in private (off the Data API), REVOKE FROM PUBLIC, anon + GRANT authenticated, service_role. The public name is a service-role-only delegate: authenticated has NO EXECUTE — an API-callable pass-through into the DEFINER twin would let any authenticated user probe /rpc/resolve_transaction_exchange_rate with arbitrary project ids and read rates for projects outside their RLS (caught by the rls-policy-reviewer audit; same exposure line as private.caller_restricted_project_relationship_ids). ACL/definer/view-call sentinels in tests/03_functions.sql.

Messaging System Functions¶

update_conversation_last_message_at() - Updates last_message_at timestamps when new messages are added
check_rate_limits_and_abuse(p_user_id uuid, p_channel text, p_conversation_id uuid, p_user_type text DEFAULT 'all') - Checks rate limits and abuse patterns before allowing message creation. SECURITY DEFINER, search_path='', EXECUTE granted to service_role only.
Returns exactly one row: allowed (boolean), reason (text), reset_at (timestamptz), limit_type (text).
Evaluation order: active abuse actions (most severe of block > suspend > throttle) → existing throttle → per-minute → per-hour → per-day → per-instance. limit_type is abuse_<action_type>, throttled, minute, hourly, daily, instance, or NULL when allowed.
The per-instance count covers the ACTIVE conversation_instances row only. Summing closed and summarized instances made the cap permanent — the historical sum only ever grows, so rolling to a fresh instance could never relieve it, and every message after 500 was rejected for the life of the conversation.
The instance verdict is not a rejection. It carries allowed = false with a NULL reset_at (no clock-based reset applies) and instructs the caller to roll the conversation onto a fresh instance via rollover_conversation_instance and retry. Note that the NULL previously went out untyped, so every call reaching this branch aborted with SQLSTATE 42804 instead of returning a verdict.
rollover_conversation_instance(p_conversation_id uuid, p_channel text, p_user_type text DEFAULT 'all') - Atomically closes a conversation instance that has reached its per_instance message cap and opens its successor. SECURITY DEFINER, search_path='', EXECUTE granted to service_role only (an API-callable version would let any authenticated user close other users' instances).
Returns exactly one row: outcome (text), closed_instance_id (uuid), active_instance_id (uuid), abuse_action_type (text), abuse_expires_at (timestamptz).

`outcome`	Meaning	`closed_instance_id`	`active_instance_id`
`rolled`	Capped instance closed, successor created	the closed instance	the new instance
`already_current`	Idempotent no-op — under cap, or lost a rollover race	NULL	the live instance, or NULL when the conversation has no active instance at all
`refused_abuse`	An active abuse action blocks the rollover	NULL	NULL (by design)
`not_found`	No such conversation	NULL	NULL
- The `conversations` row lock taken first is the linearization point. Concurrent rollovers of the same conversation serialize on it, so at most one performs the close+create and the losers observe `already_current`. This is what makes retry-after-`instance`-verdict safe from multiple inbound messages.
- The abuse guard is a snapshot taken inside that lock — after it is acquired and before every success/no-op return, so a request that queued on the lock cannot inherit an instance created just before a penalty landed. It does not promise wall-clock exclusion against abuse actions inserted after the snapshot.
- On `refused_abuse` the `active_instance_id` is deliberately NULL so no caller can mistake the capped instance for a usable successor.
- Creating the FIRST instance of a conversation is the application's job; this primitive only rolls an existing one (it returns `already_current` with a NULL `active_instance_id` in that case).
- The successor takes `MAX(instance_number) + 1` and the closed instance is stamped `auto_closed_reason = 'message_limit'`.

detect_spam_patterns() - Detects spam and abuse patterns in recent messages
get_next_abuse_action() - Determines progressive penalty action based on user offense history
manage_current_conversation_status() - Ensures only one conversation per user per channel can have status=current

Message Threading Functions¶

get_root_message_id(p_message_id UUID) - Recursively traverses the parent_id chain to find the root message of a thread
Returns the UUID of the root message (the message with parent_id = NULL)
Uses iterative approach with circular reference protection (max 100 iterations)
Returns NULL if message doesn't exist or if circular reference detected
Used by the smart tagging logic to tag root messages when replies have new attachments
get_thread_attachment_hashes(p_message_id UUID) - Gets all unique attachment file hashes in a message thread
First finds the root message using get_root_message_id()
Then returns all unique file_hash values from attachments linked to messages in the thread
Used to detect duplicate attachments when processing reply messages
Enables smart tagging: skip tagging if ALL reply attachments already exist in thread
set_root_message_id() - Trigger function that automatically populates root_message_id
Executes BEFORE INSERT OR UPDATE OF parent_id on messages table
If parent_id is NULL, sets root_message_id to the message's own id
If parent_id is set, calls get_root_message_id() to find and set the root
Ensures root_message_id is always accurate for efficient thread querying

Message Outbox Functions¶

Eight functions carry the whole lifecycle of an outbound send: enqueue, claim, start, complete/fail, resolve, recover, prune. All are SECURITY INVOKER with search_path = '' and EXECUTE granted to service_role ONLY (revoked from PUBLIC/anon/authenticated) — the dispatcher, the sweeper cron and the status webhook are all server-side service-role callers, and none of this is a Data API surface. None has an EXCEPTION handler: a genuine database error must reach the caller with its SQLSTATE intact, and every EXPECTED rejection is returned as an outcome value instead. Every transition is compare-and-swapped on the caller's claim_id under a FOR UPDATE row lock. See COMMUNICATION.md → Message Outbox for the table design and the pacing / FIFO / ambiguity invariants; the behavioural pins are tests/29_message_outbox.sql.

enqueue_message_outbox(p_idempotency_key text, p_channel channel_type, p_sender_key text, p_recipient_key text, p_send_kind message_outbox_send_kind, p_payload jsonb, p_payload_version integer, p_log_context jsonb, p_notification_delivery_id uuid, p_max_attempts integer) - Records an outbound send intent before any provider call
Returns TABLE(outcome text, outbox_id uuid, state message_outbox_state)
Ensures the message_outbox_pairs row exists first (ON CONFLICT DO NOTHING) — the pair is pure infrastructure the claim path depends on, and an existing pair carries a live lease and pacing cursor this enqueue has no business disturbing
Outcomes: created (a new intent was inserted; outbox_id and state describe it) | exists (the idempotency_key was already queued — the EXISTING row's id and current state are returned and nothing is written, so a retried webhook or replayed job adopts the intent rather than queueing the message a second time)
claim_message_outbox_pair(p_channel channel_type, p_pair_interval_ms integer, p_lease_seconds integer) - Claims one eligible pair of the given channel and its strict-FIFO head row for dispatch
Returns TABLE(outcome text, outbox_id uuid, claim_id uuid, send_kind message_outbox_send_kind, payload jsonb, payload_version integer, log_context jsonb, notification_delivery_id uuid, sender_key text, recipient_key text, attempt_count integer, max_attempts integer, has_more boolean, next_due_at timestamptz)
Eligibility is all four conditions together: the pair's lease is free or lapsed, the pair has NO row in state sending, p_pair_interval_ms has elapsed since the pair's last_attempt_started_at, and the pair's own HEAD row (state = 'pending' ordered created_at, id) is due. Testing the HEAD rather than "any due row" is the FIFO guarantee — a backed-off head keeps its pair idle instead of letting a newer message overtake it
The in-flight exclusion is what keeps a lapsed lease the recovery sweep's business rather than this function's. A pair whose lease expired while a row sits in sending belongs to recover_stale_message_outbox_leases, which matches that row on the PAIR's claim_id. Claiming past it would overwrite the pair's claim_id and orphan the sending row (recovery could never match it again), break FIFO by putting a newer row on the wire ahead of it, and — if the old dispatcher is merely slow rather than dead — put two messages of one pair on the wire at once, the exact thing the pair lock exists to prevent. Served by the partial index idx_message_outbox_sending_pair on (channel, sender_key, recipient_key) WHERE state = 'sending'
One pair is locked per call with FOR UPDATE SKIP LOCKED, which is what lets many dispatchers run concurrently: each takes a different pair rather than queueing behind the same one. The pair row is the serialisation point, so the head is re-read under its lock, and the row transition is compare-and-swapped on state = 'pending'
Taking over a LAPSED lease needs no explicit compare-and-swap on (claim_id, lease_expires_at), because the eligibility filter and the row lock are ONE statement. A second dispatcher racing for the same lapsed pair either SKIPs it (the winner still holds the lock) or waits and is then re-checked by PostgreSQL's EvalPlanQual against the winner's committed row version — where the lease is no longer lapsed, so it matches nothing and returns none. Splitting the eligibility filter out of the locking SELECT, or dropping SKIP LOCKED, would reintroduce a stale-lease read and allow two concurrent sends to one pair. Pinned by a prosrc sentinel in tests/03_functions.sql
Outcomes: claimed (row fields populated; the pair lease is taken for p_lease_seconds, the intent moves pending → sending under the fresh claim_id) | none (no eligible pair, or the head moved between the eligibility probe and the claim — entry fields are NULL, while backlog fields remain meaningful)
On claimed, has_more / next_due_at describe the backlog left behind on the claimed pair: whether any pending row remains and the minimum next_attempt_at among them
On none, has_more describes pending work across the channel and next_due_at is the earliest absolute time a non-in-flight pair's FIFO head can clear both its retry time and pacing cursor. This is what distinguishes an empty queue from a wake-up that arrived inside the 6.5-second pacing window; the dispatcher republishes itself for only the remaining interval instead of stranding the tail until the sweep cron
start_message_outbox_attempt(p_outbox_id uuid, p_claim_id uuid) - Opens one wire attempt under a held claim
Returns TABLE(outcome text, attempt_id uuid, attempt_number integer)
Increments attempt_count BEFORE the wire call (so an attempt that dies mid-wire still counts against max_attempts and still leaves an attempt row for the recovery sweep to find), appends the message_outbox_attempts row, and stamps message_outbox_pairs.last_attempt_started_at = now()
The pacing stamp lands at attempt START, never at completion. The provider counted the request when it arrived regardless of how it ended, so crediting the pair only on success would let a failing send retry immediately and re-trip the limit
The pair LEASE is checked as well as the row claim: a lapsed lease means the recovery sweep is entitled to settle this row, and a dispatcher that has lost its lease must not put another message on the wire under it
Outcomes: started (carrying the new attempt_id and attempt_number) | stale_claim (the intent is no longer sending, its claim_id is not the caller's, or the pair lease is not the caller's / has expired) | not_found (no such intent, or the intent's pair row is missing)
complete_message_outbox_attempt(p_outbox_id uuid, p_claim_id uuid, p_attempt_id uuid, p_provider_message_id text, p_requires_persistence boolean) - Records provider acceptance
Returns TABLE(outcome text)
Closes the attempt as accepted with its provider_message_id, moves the intent sending → accepted carrying that id, clears the intent's claim_id, and releases the pair lease (claim_id/lease_expires_at NULL, conditional on the pair still holding the caller's claim)
p_requires_persistence controls persisted_at: false stamps it now() — a kind with no post-acceptance persistence (a read receipt has no message row to write) is finished the moment it is accepted; true leaves the existing value (NULL on a first acceptance) so the persistence-retry scan can find the row
The attempt is closed FIRST and the write is row-count checked. A mis-threaded attempt id is reported, never absorbed: the intent stays sending under its original claim so the lease sweep can recover it honestly, rather than advancing while a real attempt is left in flight forever
That close is a compare-and-swap, not a lookup: the predicate is (id, outbox_id, claim_id = p_claim_id, completed_at IS NULL). An attempt of another intent, one made under an EARLIER claim, or one already settled by the dispatcher or the recovery sweep is never rewritten — the attempt log is append-only, and a verdict already recorded is evidence, not a mutable field
Outcomes: completed | attempt_not_found (p_attempt_id names no OPEN attempt of this intent under this claim — nothing is written: no intent transition, no provider_message_id, no lease release) | stale_claim (intent not sending, or claim_id is not the caller's) | not_found
fail_message_outbox_attempt(p_outbox_id uuid, p_claim_id uuid, p_attempt_id uuid, p_classification text, p_error_code integer, p_http_status integer, p_error_body jsonb, p_next_attempt_at timestamptz) - Records a failed wire attempt and routes the intent
Returns TABLE(outcome text, provider_message_id text)
p_classification accepts exactly retryable, permanent or ambiguous. On every write path the attempt row is closed with the matching verdict (failed_retryable / failed_permanent / ambiguous) plus error_code, http_status and error_body; the intent records the same error fields as last_error_code / last_error_status / last_error, drops its claim_id, and the pair lease is released

`outcome`	Meaning	Intent state written
`requeued`	`retryable` with `attempt_count < max_attempts`	`pending`, `next_attempt_at = COALESCE(p_next_attempt_at, now())`
`exhausted`	`retryable` at or above the attempt ceiling	`failed`
`failed`	`permanent` — a 4xx that would fail identically on every retry	`failed`
`ambiguous`	Outcome unknown mid-wire, and nothing correlates it; the provider may already have delivered it	`ambiguous` — never retried, never pruned
`accepted_by_evidence`	`ambiguous`, but a status webhook had already stamped `provider_message_id` on the still-claimed row	`accepted` + `ambiguous_resolved_at`; the wamid is RETURNED
`failed_by_evidence`	`ambiguous`, but a status webhook had already left the rejection marker in `last_error`	`failed` + `ambiguous_resolved_at`; the marker is PRESERVED
`invalid_classification`	`p_classification` NULL or outside the three values	nothing is written — the intent is left exactly as it was
`attempt_not_found`	`p_attempt_id` names no OPEN attempt of this intent under this claim	nothing is written — no state, no `last_error_*`, no lease release
`stale_claim`	Intent not `sending`, or `claim_id` is not the caller's	nothing
`not_found`	No such intent	nothing
- The settle CONSUMES webhook evidence rather than parking past it. A status webhook that arrived while this row was still `sending` could not transition it — only the claim holder may — so it left evidence. That evidence answers the very question ambiguity exists to hold open, so an `ambiguous` classification checks for it FIRST and settles the intent on the spot. Parking anyway would leave the row waiting for a webhook that has already been and gone. `provider_message_id` is returned alongside `accepted_by_evidence` precisely so the dispatcher can run its normal persistence step without a second read; it is NULL on every other outcome
- `failed_by_evidence` preserves the marker in `last_error`. Overwriting it with the send-side error body would erase the only record of where the terminal verdict came from, which is the sole thing distinguishing it from an ordinary failure
- Both evidence outcomes still close the attempt as `ambiguous`. The log records what was known when the call returned; the intent advancing does not rewrite that history
- `invalid_classification` is rejected before anything is touched. An unrecognised classification is a caller defect, not a send outcome, so the dispatcher can be fixed without a half-written row to clean up
- The attempt is closed FIRST and the write is row-count checked. A mis-threaded attempt id is reported, never absorbed: the intent stays `sending` under its original claim so the lease sweep can recover it honestly, rather than being routed while a real attempt is left in flight forever
- That close is a compare-and-swap, not a lookup: the predicate is `(id, outbox_id, claim_id = p_claim_id, completed_at IS NULL)`. An attempt of another intent, one made under an EARLIER claim, or one already settled by the dispatcher or the recovery sweep is never rewritten — the attempt log is append-only, and a verdict already recorded is evidence, not a mutable field
- Backoff is computed application-side. The database only stores `p_next_attempt_at` in `next_attempt_at`, and only on the `requeued` path; every other outcome leaves `next_attempt_at` unchanged

resolve_message_outbox_ambiguous(p_outbox_id uuid, p_provider_message_id text, p_resolution text) - Applies a provider status webhook to an outbox intent
Returns TABLE(outcome text). p_resolution is 'accepted' or 'rejected'; anything else is a caller defect rejected before the row is even read
On an ambiguous row the webhook settles the question the state exists to hold open:
- 'accepted' → accepted with provider_message_id stamped (resolved)
- 'rejected' → failed, with {"source":"status_webhook","status":"failed"} in last_error (resolved_failed). A Meta failed status correlated to our outbox id is proof of rejection, so the row becomes terminal instead of sitting unresolved forever
- ambiguous_resolved_at is stamped either way
On a row still sending the webhook is recorded as EVIDENCE ONLY (evidence_recorded) — provider_message_id for an acceptance, the last_error marker for a rejection, with no state, claim or lease change. A status webhook can outrun the dispatcher's own call, and a claimed row belongs to its claim holder: only the holder or recover_stale_message_outbox_leases may transition it. Transitioning (or releasing the lease) here would let a second dispatcher claim the pair while the first is still on the wire. The recovery sweep later reads that evidence and settles the row without needing a second webhook
Only the intent advances — the attempt keeps its ambiguous verdict. The log records what was known when the call returned, and rewriting it would erase the evidence that this send was ever in doubt
Outcomes: resolved | resolved_failed | evidence_recorded | not_ambiguous (already accepted or failed, so a duplicate webhook is idempotent; writes nothing) | not_found | invalid_resolution (writes nothing)
recover_stale_message_outbox_leases(p_channel channel_type) - Reclaims pairs of the given channel whose dispatcher lease has lapsed
Returns TABLE(requeued_count integer, ambiguous_count integer, resolved_by_evidence_count integer, failed_by_evidence_count integer) — the four settlement counts, not an outcome string
Iterates the channel's pairs holding a claim_id past lease_expires_at, with FOR UPDATE SKIP LOCKED so a pair a live dispatcher is actively working is left alone rather than contended for. For each such pair it takes the sending intent held under that pair's claim_id and branches on whether an attempt row exists under the same claim with completed_at IS NULL:
- In-flight attempt found → the dispatcher died with a request already on the wire. The attempt is closed ambiguous either way (that is what was known when it ended, and the log is append-only), then the intent settles on evidence:
- provider_message_id already set → a status webhook correlated the send while the row was still claimed and could only record it as evidence. That answers the very question ambiguity holds a row open for, so the intent goes straight to accepted with ambiguous_resolved_at stamped (resolved_by_evidence_count) instead of waiting for a webhook that has already been and gone
- last_error carries {"source":"status_webhook","status":"failed"} → the symmetric case: the webhook proved the provider REFUSED the send. The question is equally answered, so the intent goes straight to failed with ambiguous_resolved_at stamped (failed_by_evidence_count)
- no evidence → a retry could duplicate a delivered message, so the intent becomes ambiguous (ambiguous_count), parked until a status webhook settles it
- No attempt started under that claim → the dispatcher died after claiming but before any request left. Nothing reached the provider, so the intent CASes back to pending (requeued_count) and retrying loses no message
The lease is released either way (including when the pair holds no sending intent at all) — leaving it held would idle the pair until the next sweep even though nothing is working it
Channel-scoped by contract: a sweep for one channel never touches another channel's expired lease
prune_message_outbox(p_accepted_retention_days integer, p_failed_retention_days integer) - Deletes settled outbox history past its per-state retention, then collects the pair rows it emptied
Returns TABLE(deleted_count integer, deleted_pairs_count integer)
Deletes accepted rows older than p_accepted_retention_days and failed rows older than p_failed_retention_days, each measured against its own window on the updated_at basis. message_outbox_attempts rows cascade
Never deletes pending, sending or ambiguous rows regardless of age. Pending and sending are live work; an ambiguous row is an unresolved delivery question whose answer may still arrive by webhook, and deleting it would destroy the only record that the send ever happened
Then collects orphaned pairs (deleted_pairs_count). message_outbox_pairs rows are created on demand by every enqueue and nothing else ever deletes them, so a system that has messaged many recipients once accumulates a pair per conversation forever — and every claim scans that table. A pair is collectable only when it holds no lease (claim_id IS NULL and no live lease_expires_at), has been idle past the accepted-retention window (last_attempt_started_at, else created_at), and has no message_outbox rows of any state. The emptiness test is the load-bearing one: a pair carrying any row — including the pending and ambiguous rows this prune deliberately kept — is live infrastructure, and deleting it would drop that row's pacing cursor and serialisation lock while its work is still queued

Production Phase Functions¶

handle_production_phase_days() - Manages production days when phases are created, updated, or deleted
AFTER trigger on production_phases for INSERT, UPDATE, DELETE
On INSERT: creates number_of_days production days starting from start_date (skips if dates are NULL)
On UPDATE: adjusts days when dates/duration change — reactivates soft-removed days before creating new ones when extending; soft-removes excess days when shortening; recalculates calendar dates using positional ordering (ROW_NUMBER over calendar_date) instead of day_number, correctly handling Travel/Rest days and cross-phase-renumbered Working days
On UPDATE: calls renumber_working_days(NEW.id) at the end of every UPDATE so newly inserted or reactivated Working days are immediately numbered. Without this, extending a phase via a direct PATCH on production_phases left the new days with day_number = NULL until something else triggered a renumber. The dedicated add_days_to_phase RPC disables this trigger while it operates, so it is unaffected and continues to call renumber itself once at its own tail.
On DELETE: soft-removes all active days (preserves budget allocation history)
Defers the UNIQUE constraint on (production_phase_id, calendar_date) during date shifts to avoid intermediate violations
renumber_working_days(p_phase_id UUID) - Renumbers working days sequentially for a phase
RPC function callable via supabase.rpc('renumber_working_days', { p_phase_id })
First NULLs ALL day_numbers to avoid unique constraint violations
Then assigns sequential numbers (1, 2, 3...) to Working days only, ordered by calendar_date
Travel and Rest days keep NULL day_number
Called via debounced client-side hook after day type changes, removes, or reactivations

Payment System Functions¶

check_payment_reconciliation_total() - Enforces DIRECTION agreement and a MAGNITUDE cap on every allocation
Trigger function that executes BEFORE INSERT OR UPDATE on payment_reconciliations table
A row transitioning to Voided returns immediately and takes NO locks, so a void cascade is never blocked or made to wait. This arm is first, deliberately
Lock protocol: for a linked transaction, the trigger first takes the shared transaction-scoped payment-processing advisory gate, then locks transaction → payment. schedule_payment_group, the final fenced-payment function, and process_payments_atomic take the same gate before parent rows. This prevents the allocation path from deadlocking with PostgreSQL's unavoidable payment → AFTER-trigger → transaction path. A transaction_id IS NULL stub takes only the payment lock
Payment-side sign rule (every non-voided row, INCLUDING transaction-less stubs): the amount must share the sign of the parent payment's total_amount. A ZERO-total payment states no direction and constrains nothing — that is the state import stubs and mid-recompute payments legitimately hold
Transaction-side rules (skipped for stubs): a NULL or zero transaction total RAISES, rather than passing unchecked as it did under the old (allocated + amount) > total comparison (x > NULL is NULL, which is not TRUE); the amount must share the sign of the transaction total
The cap is on MAGNITUDE: abs(current_allocated + amount) > abs(transaction_total) raises. Because every row in the aggregated set passed the sign rule against the same total, the running sum shares that sign and abs() is exact. The aggregation set is unchanged — Active rows on non-Cancelled payments, self-excluded on UPDATE — so a Voided allocation still frees its room
Under the previous signed comparison a partial refund (-50 against a -100 total) was REJECTED while an unrelated -1000 against a +100 total was accepted

1a. check_transaction_total_sign_flip() / check_payment_total_sign_flip() - Refuse a parent total sign flip under a live allocation

trg_check_transaction_total_sign_flip is BEFORE UPDATE OF total on transactions; trg_check_payment_total_sign_flip is BEFORE UPDATE OF total_amount on payments
Direction agreement is validated on the ALLOCATION's own write; a later sign flip of the parent would invalidate that agreement with no write of its own to catch it
Each refuses a sign change while a live allocation exists (transactions: Active rows on non-Cancelled payments — the cap's set; payments: Active rows). It also refuses a same-sign shrink whose new magnitude is below the sum of those live allocation magnitudes; equality and growth remain valid
Removing the last allocation BEFORE writing the parent total to zero is therefore permitted — the order the established amend/delete flows already write in
Serialization is free: every allocation writer locks the parent rows before validating, and these UPDATEs hold those very locks
mark_transaction_paid_on_payment() - Marks transactions as Paid when fully paid via reconciliations
Trigger function that executes AFTER INSERT OR UPDATE on payments table
Runs when a payment is inserted as 'Paid' or transitions to 'Paid' (no-op updates and non-Paid statuses are skipped)
Checks all transactions linked to the payment via payment_reconciliations
Sums Active allocations from Paid payments for each linked transaction and tests them with private.transaction_is_fully_settled (see below)
Sets transaction status to 'Paid' when that predicate holds and the transaction is currently Approved
Cannot cover the pay-now write order on its own: pay-now inserts the payment as 'Paid' before its reconciliations exist, so this trigger finds no allocations to sum at INSERT time — that gap is closed by mark_transaction_paid_on_reconciliation() below
mark_transaction_paid_on_reconciliation() - Marks transactions as Paid when an allocation against a Paid payment is created
Trigger function that executes AFTER INSERT OR UPDATE on payment_reconciliations table
Only runs for Active reconciliations whose parent payment is already 'Paid' (otherwise the payments-side trigger handles the eventual transition)
Re-runs the same private.transaction_is_fully_settled check as mark_transaction_paid_on_payment() for the affected transaction
Closes the pay-now gap (payment inserted 'Paid' first, reconciliation inserted second) and protects any other path that allocates against an already-Paid payment
check_transaction_paid_on_approval() - Auto-marks transactions as Paid when approved if already fully paid
Trigger function that executes BEFORE UPDATE on transactions table
Only runs when transaction status changes to 'Approved'
Computes total paid from Active reconciliations of Paid payments
If private.transaction_is_fully_settled(NEW.total, total_paid) holds, sets status to 'Paid' instead of 'Approved'
Handles edge case where payments were paid before transaction was approved

4a. private.transaction_is_fully_settled(p_total numeric, p_total_paid numeric) → boolean - The single settled predicate

IMMUTABLE, private-schema, service_role-only EXECUTE (never a Data API surface); reads nothing but its arguments
p_total IS NOT NULL AND p_total <> 0 AND sign(p_total_paid) = sign(p_total) AND abs(p_total_paid) >= abs(p_total) — settled means money moved in the SAME direction as the total, reaching at least its magnitude
Shared by all three paid-marking triggers so they can never disagree
For a POSITIVE total it is exactly the strictest previous form (total_paid >= total AND total_paid > 0), so the positive flow is unchanged. Two behaviours change: a NEGATIVE total is now settled only when actually refunded (the old total - total_paid <= 0 was true for every unallocated refund, flipping it to Paid on the first reconciliation event), and a ZERO total is never auto-Paid

Import Functions¶

The CSV-import commit step has no SQL dispatcher. Each domain ships its own TS commit worker (a Server Action under services/<domain>/) that consumes the parsed rows + the wizard's domain context payload, calls the existing per-table service helpers (e.g. createPayment, updatePayment), and writes the run + per-row outcomes to import_runs (see FINANCIAL.md → Import Runs). The framework owns parsing, mapping, dedupe-by-file-hash, and the run audit table; domains own writes. Adding a new domain means: seed import_fields + import_mappings rows, register the entity_type with the wizard, and ship a per-domain commit worker.

Transaction Line-Item Functions¶

replace_transaction_line_items(p_transaction_id uuid, p_items jsonb, p_line_item_mode line_item_processing_mode) → JSONB — atomically swaps a transaction's ENTIRE line-item set and stamps transactions.line_item_mode
PostgREST has no cross-request transactions, so a service-layer delete-then-insert leaves the transaction with ZERO line items on any mid-flight failure. This RPC does the whole swap in one transaction under one row lock.
LANGUAGE plpgsql, SECURITY INVOKER, SET search_path = ''. REVOKE EXECUTE FROM PUBLIC, anon, authenticated + GRANT EXECUTE TO service_role — the processing pipeline is the sole caller. INVOKER is correct precisely because that caller is service_role (which bypasses RLS anyway); a DEFINER here would hand an RLS bypass to any role that later gained EXECUTE. Because CREATE OR REPLACE FUNCTION re-grants EXECUTE to PUBLIC, the revoke must be the LAST word on the ACL in any migration touching this function.
The mode parameter takes the enum type directly (matching get_user_clearance / get_processing_job_status_summary), so an off-domain label is rejected as 22P02 at the call site and the body never restates the domain. A NULL mode raises 22023; a non-array p_items raises 22023; an unknown transaction raises P0002 (never a silent no-op).
Settled transactions are refused under the lock: a status in Rejected / Approved / Paid / Cancelled (mirroring NON_EDITABLE_TRANSACTION_STATUSES in constants/transaction.ts) raises 55000 and writes NOTHING — no items deleted, no mode stamped. The app-layer editability gates run before the job is queued and again before the rebuild, but both read the row OUTSIDE this lock, so an approval or payment flow can settle the transaction in the gap; only the check taken under the lock is race-free. The main pipeline always operates on a Processing-status row, so this never fires for it.
Sequence: lock the transaction (FOR UPDATE) → refuse a non-editable status → capture the parent budget_item_daily_allocations ids currently reached by this transaction's items → delete the items (their budget_allocation_transaction_items links cascade via the FK, so the RPC never deletes links itself) → delete captured parents left with ZERO links only when added_from_processing → insert p_items → stamp the mode.
Allocation-cleanup invariant: a parent allocation survives if it still has links from another transaction's items, or if it is added_from_estimated_costs, or if it carries neither flag (hand-created by a budget editor). Only the pipeline's own now-empty allocations are removed — the same provenance the trg_transaction_items_sync_allocation_links trigger stamps when it creates one (see BUDGETS.md).
transaction_id is forced to the locked transaction, never read from the payload; line_number falls back to the payload's array position when the item omits it.
Returns { deleted_items, deleted_allocation_links, deleted_empty_allocations, inserted_items: [{id, line_number}] }.
The allocation-sync triggers fire on UPDATE OF budget_item_id / UPDATE OF subtotal only, so this delete+insert cycle does not contend with them.
Behavioural contract pinned in tests/25_replace_transaction_line_items.sql; shape/ACL in tests/03_functions.sql.

Entity Deduplication Functions¶

find_matching_business_entity(p_organisation_id, p_business_name, p_registration_number, p_phone_number, p_email) - Atomically searches for an existing business entity
SECURITY DEFINER with search_path = ''
Prevents duplicate entity creation during concurrent transaction processing
Uses multi-signal priority matching (mirrors AI entity matching logic):
1. Registration number (exact match)
2. Phone number (exact match)
3. Email (case-insensitive exact match)
4. Business name (case-insensitive, trimmed)
Returns JSONB { entity_id, match_signal } if found, NULL if no match
Entity creation remains in the application layer via createBusinessEntity
Supported by partial indexes: idx_entities_org_business_name_lower, idx_entities_org_registration_number, idx_entities_org_phone_number, idx_entities_org_email_lower

Payment Scheduling Functions¶

schedule_payment_group(p_existing_payment_id uuid, p_payment jsonb, p_items jsonb) → TABLE(payment_id uuid, reconciliation_ids uuid[]) - Schedules one payment and its whole allocation set atomically
SECURITY INVOKER with search_path = ''; EXECUTE granted to authenticated + service_role, revoked from PUBLIC/anon. Every write still passes caller RLS. Because the payments policy intentionally accepts several operation keys, the RPC additionally requires the exact key from authenticated callers: payment:schedule for Scheduled create/top-up; payment:process for a fresh Paid payment. service_role remains available to trusted workers
Why it exists: manual scheduling writes a payment plus N allocations. Issued as separate PostgREST requests they auto-commit one by one, so any mid-batch rejection (the cap, the sign rules, the unique index) left a payment carrying a partial allocation set that no caller asked for and no retry converges on
Carries no EXCEPTION handler: every rejection RAISES and rolls the whole call back. Callers branch on the SQLSTATE/message, never on a partial result
Payload validated as a SET first, before any lock: p_payment an object, p_items a non-empty array, no zero or missing item amount, one distinct transaction per item, one shared direction across every amount (a mixed-sign group would produce a payment total no allocation could agree in sign with), and a non-zero net
Takes each named transaction's shared advisory gate, then locks transactions in id order before the payment, keeping overlapping groups and atomic processors on the same protocol. A named transaction that does not exist RAISES by id
Before the first write, every locked transaction must match the payment's project, currency, and role-derived payee entity: reimbursement person, outbound customer, otherwise supplier
p_existing_payment_id NULL → INSERTs a fresh payment (explicit column allowlist; total_amount is the group net; status defaults to Scheduled, source to manual). A Paid insert defaults missing paid_at to now() and resolves paid_by_user_id from explicit actor → payment creator → auth.uid(); it raises if all three are NULL. Creation/update audit ids fall back to the current caller
p_existing_payment_id non-NULL → locks that payment FOR UPDATE, requires status Scheduled, re-compares project / entity / relationship / currency under the lock (the caller matched them against a pre-call snapshot an amendment can have moved past), refuses a direction opposing the group, and grows total_amount by the net. A legacy NULL entity/relationship pair is backfilled together from the payload; a populated pair must agree. An UPDATE hidden by RLS raises before allocations are written
Per item: increments the live Active allocation for the (payment, transaction) pair if one exists, else INSERTs — matching the partial unique index that admits at most one Active row per pair. Update audit ids use the payload actor or current caller. Every write re-enters the allocation trigger, which re-validates sign and cap under the locks already held
Returns the payment id plus the ids of every allocation touched
process_payments_atomic(p_payment_ids uuid[], p_payment_method_id uuid, p_notes text, p_replace_details boolean, p_paid_at timestamptz, p_paid_by_user_id uuid, p_payment_metadata jsonb) → TABLE(payment_id uuid) - The only application path for transitioning existing payments to Paid
SECURITY DEFINER only so its UPDATE executes as the payments owner through trg_guard_payment_paid_transition; before bypassing RLS it requires distinct, existing, same-project payment ids and exact payment:process from authenticated callers. Authenticated actor is always auth.uid() (a mismatched explicit id raises) and paid_at is always the database clock. Authenticated metadata is accepted only for one non-detail-replacement payment tied to an unfinished payments import run created by that caller in the payment project (and matching its run-level payment method when present). Imported-match must be true; under the payment lock, established payee identity cannot be replaced, a supplied relationship must prove the resulting project/entity pair, and reconciled stamps require full allocation and are normalized to the Paid timestamp/auth.uid(). EXECUTE is authenticated/service_role only, and its owner is pinned equal to the payments owner
Takes the shared advisory gates for all affected transactions in id order, locks transaction rows, then payment rows, and revalidates that every payment is still Scheduled in the same project. Direct authenticated/service-role status UPDATEs are rejected by the invoker BEFORE trigger before the existing Paid AFTER trigger runs
p_replace_details=true atomically replaces the common method/notes after proving the method belongs to the project. For trusted service/owner workers, p_payment_metadata is a fixed allowlist (entity_id, project_relationship_id, import identity, reconciled stamps, waiting-doc flag), so import and transaction-reuse metadata cannot land without the Paid transition and arbitrary columns cannot cross the DEFINER boundary
Two processors affecting the same transaction serialize before either parent row. The second statement sees the first payment as Paid, so concurrent partial payments cannot both miss the fully-settled promotion. Stale retries fail because every target must still be Scheduled

Invite System Functions¶

accept_invite_transaction(p_user_id, p_entity_id, p_relationship_id, p_updated_by_user_id, p_first_name, p_last_name, p_preferred_name, p_entity_date_of_birth, p_entity_metadata, p_entity_first_name, p_entity_last_name, p_entity_preferred_name, p_relationship_metadata, p_accepted_at) - Atomically processes invite acceptance
SECURITY DEFINER with search_path = ''
Wraps 4 updates in a single PostgreSQL transaction:
1. Sets users.is_invite_pending = false and updates name fields (if p_user_id provided)
2. Updates entity with metadata, date_of_birth, and optionally name fields (when no linked user)
3. Sets project_relationships.status = 'Active' with accepted_at timestamp and metadata
4. Sets all user_accesses.status = 'Active' for the relationship
When p_user_id IS NOT NULL, the handle_user_update trigger syncs names to linked entities
When p_user_id IS NULL, names are set directly on the entity
Returns JSONB { user_updated, entity_updated, relationship_updated, accesses_updated }
Called from confirmPersonalDetails form handler in Sana (WhatsApp bot)

Approval Message Functions¶

send_approval_message_atomic(p_approval_request_id, p_kind, p_content_text, p_sender_user_id, p_created_by_user_id, p_entity_id, p_attachment_ids, p_origin_message_id, p_metadata, p_expected_root_query_message_id) - Atomically sends an approval query or reply
SECURITY INVOKER with search_path = '' — every read and write runs under the caller's RLS; GRANT EXECUTE TO authenticated, service_role, revoked from PUBLIC/anon
Locks the approval_requests row (FOR UPDATE; under RLS this applies both SELECT and UPDATE USING policies, with a plain-SELECT fallback to classify rows that are visible but not lockable), then validates the request is still the record's current writable request in the state the operation requires: query = Pending → Queried, reply = Queried → Pending (threading under the latest root source = 'Approval Message' message)
Inserts the approval message (+ message_attachments links) and transitions the request status in ONE transaction — a failed validation writes nothing
Returns typed jsonb result codes as DATA (never RAISE for expected outcomes, since the app's RPC helper would swallow the exception): OK (with message, approval_request, and for replies query_message), APPROVAL_REQUEST_SUPERSEDED (target Cancelled and/or a newer open request exists — current_approval_request_id carries the successor), APPROVAL_REQUEST_INVALID_STATE, APPROVAL_REQUEST_NOT_FOUND, NO_QUERY_TO_REPLY (no root query message, or the latest one carries no sender), NO_SUBMITTER (query only), NOT_AUTHORIZED (visible but not lockable under the caller's RLS), DUPLICATE_ORIGIN (same origin_message_id replayed — returns the existing message plus a content_matches flag, zero writes; the app's send fns surface a same-content replay as an idempotent success and a different-content one as a loud failure, since that is origin-id reuse, not a replay)
Identity binding: when auth.uid() is non-NULL the sender/creator params are ignored in favour of auth.uid(); explicit actor params are honoured only under service-role (Sana / email inbound)
Query-generation guard — p_expected_root_query_message_id (trailing, defaults NULL) pins the query generation a reply is authorised to answer. On the reply path a non-NULL value that differs from the latest root query message returns STALE_QUERY_GENERATION (carrying the current root_query_message_id) and writes nothing, so an authorisation minted against one query (an emailed reply link, a queued inbound message) can never answer a LATER one. NULL opts out — callers acting on the live thread (the in-app reply) are unaffected — and the parameter is ignored entirely for p_kind = 'query'
Replaces the non-atomic createApprovalMessage → updateApprovalQueryStatus/updateApprovalReplyStatus write pair; called from sendApprovalMessageAtomic in services/approval/base.ts (UI query/reply mutations, Sana inbound, email action processors)
claim_approval_query_response(p_approval_request_id, p_expected_root_query_message_id) → jsonb - Claims the single permitted response to an approval query for the document-replacement path
SECURITY INVOKER with search_path = ''; GRANT EXECUTE TO authenticated, service_role, revoked from PUBLIC/anon (mirrors send_approval_message_atomic)
The document-replacement path resolves a query by replacing a document rather than by sending a message, so it has no atomic send to ride. Under ONE row lock (FOR UPDATE; as in send_approval_message_atomic, RLS applies both the SELECT and UPDATE USING policies, with a plain-SELECT fallback to classify rows that are visible but not lockable) this fn validates the request is still Queried and that its latest root source = 'Approval Message' message (selected identically to send_approval_message_atomic, so both paths agree on what "the current generation" is) matches the caller's expectation, then transitions Queried → Pending
Returns typed jsonb codes as DATA, never RAISE: OK (with the claimed root_query_message_id), APPROVAL_REQUEST_NOT_FOUND (no row visible to the caller), NOT_AUTHORIZED (row visible but not lockable under the caller's RLS UPDATE policy — distinct from NOT_FOUND so the client can tell "not allowed" from "refresh"), APPROVAL_REQUEST_INVALID_STATE (carries status; a reply or an earlier upload already claimed the query), STALE_QUERY_GENERATION (carries the current root_query_message_id). Every non-OK path writes nothing
p_expected_root_query_message_id is NOT NULL by contract. A claim exists to answer one specific query generation; a NULL would satisfy the IS DISTINCT FROM guard against a Queried request carrying no root query message and claim a generation nobody was authorised for. The fn therefore returns STALE_QUERY_GENERATION for a NULL, ahead of any row lookup. Callers holding no generation (legacy authorisations) must skip the claim entirely rather than pass NULL
Mutual exclusion invariant: the reply path performs the SAME Queried → Pending transition under the same lock, so exactly one response — a reply OR an upload — resolves a given query generation; the loser sees APPROVAL_REQUEST_INVALID_STATE
restore_claimed_approval_query(p_approval_request_id, p_expected_root_query_message_id) → jsonb - Compensating restore for a claim whose authorised work then failed
SECURITY INVOKER with search_path = ''; GRANT EXECUTE TO authenticated, service_role, revoked from PUBLIC/anon (mirrors claim_approval_query_response)
claim_approval_query_response transitions Queried → Pending BEFORE the work it authorises (replacing the queried document) is known to have succeeded. When that work fails the claim must be given back — but a plain Pending → Queried compare-and-swap is ABA-unsafe: between the claim and the compensation the request can have been queried AGAIN and that newer query already answered, leaving the row Pending for a completely different reason, and the revert would reopen somebody else's completed work
Invariant: a restore may only reopen the EXACT query generation the failed claim had claimed. Any newer root source = 'Approval Message' message means the Pending state is not ours to revert, so the call returns STALE_QUERY_GENERATION and writes nothing. The generation is selected identically to send_approval_message_atomic and claim_approval_query_response
Returns typed jsonb codes as DATA, never RAISE: OK (request returned to Queried), APPROVAL_REQUEST_NOT_FOUND (no row visible to the caller), NOT_AUTHORIZED (row visible but not lockable under the caller's RLS UPDATE policy — distinct from NOT_FOUND), APPROVAL_REQUEST_INVALID_STATE (carries status; the request is not Pending, so it moved on under its own rules and there is nothing to restore), STALE_QUERY_GENERATION (carries the current root_query_message_id, NULL when the request carries no root query at all). Every non-OK path writes nothing
p_expected_root_query_message_id is NOT NULL by contract, exactly as for the claim it compensates: a NULL would satisfy the IS DISTINCT FROM guard against a request carrying no root query message and reopen a generation nobody claimed, so a NULL returns STALE_QUERY_GENERATION ahead of any row lookup

Root-query generation selection is TOTAL across all three functions: ORDER BY created_at DESC, id DESC. Two root queries can share a created_at (app-stamped second precision vs DB microseconds), and without the id tie-break the three functions could disagree about which generation is current — a claim and a reply would then both believe they hold the live query. Pinned behaviourally by section S of tests/11_approval_message_atomic.sql.

Processing Job Lifecycle & Stall Recovery Functions¶

Every function in this group takes the parent processing_jobs row FOR UPDATE as its linearization point, so a recovery claim, a lifecycle transition and a concurrent AI callback's step write are strictly ordered and can never interleave. All are SECURITY INVOKER with search_path = '', VOLATILE, and granted EXECUTE to service_role ONLY (revoked from PUBLIC/anon/authenticated) — they are internal pipeline machinery, and exposing them to authenticated would hand any user a lever on another tenant's job state. None has an EXCEPTION handler: a genuine database error propagates with its SQLSTATE intact and rolls back, while every EXPECTED rejection is returned as an outcome value. Every rejection outcome writes NOTHING — the assertions are all made before the first write, under the lock. See AI_PROCESSING.md → Stall recovery for the surrounding design.

claim_job_recovery(p_job_id, p_observed_watermark, p_observed_restart_count, p_max_attempts) - Transactionally claims a stalled job for recovery
Returns TABLE(outcome text, sweep_attempts integer, live_watermark timestamptz, fingerprint jsonb)
Locks the job row, then walks an outcome ladder, writing nothing on any rejection: not_found (no such job) → terminal (overall_status is not pending/in_progress) → superseded (restart_count ≠ p_observed_restart_count, so the sweeper's whole observation describes a dead run) → progressed (the live watermark COALESCE(MAX(processing_job_steps.updated_at), job.created_at) differs from p_observed_watermark, meaning a callback landed between the sweeper's read and this claim)
Otherwise computes the live fingerprint {"terminal_steps", "max_terminal_group"} over steps in a terminal step_status (completed/failed/skipped/not_needed), joined to process_template_steps for execution_group. A fingerprint differing from the stored sweep_fingerprint means real progress since the last recovery episode, so sweep_attempts resets to 1; otherwise it increments
exhausted when the resulting count exceeds p_max_attempts, else claimed. Both outcomes persist sweep_attempts, sweep_fingerprint, and last_swept_at — exhaustion must be durable, or an unchanged pipeline would be retried forever
Candidate scan is served by the partial index idx_processing_jobs_live_updated_at
update_processing_step_guarded(p_step_id, p_expected_restart_count, p_expected_statuses, p_new_status, p_result_data, p_error_message, p_request_data, p_retry_count, p_duration_seconds, p_expected_claim_id) - Guarded compare-and-swap write of a processing step
Returns TABLE(outcome text, current_status text, current_restart_count integer)
Casts p_new_status to step_status FIRST, so an off-enum value fails loudly (22P02) before any lock is taken or row written
Locks the PARENT job (not the step) — that is what serializes it against claim_job_recovery, the four lifecycle RPCs below and the five fenced domain-write RPCs — then re-reads the step under that lock, since its status may have moved while waiting
Outcomes: not_found, stale_generation (job restart_count ≠ p_expected_restart_count — the write belongs to a run that has since been restarted), job_terminal (the job's overall_status is no longer pending/in_progress on THIS generation), claim_lost, status_conflict (step status not in p_expected_statuses; returns the live status — a NULL p_expected_statuses also lands here, refused fail-closed because it would assert nothing and become the unconditional write the guard exists to prevent), updated
p_expected_claim_id uuid DEFAULT NULL asserts execution-group claim ownership after the generation and job_terminal arms, so those keep reporting the outcomes their callers already branch on. A caller whose claim was replaced on the SAME generation — a resume that rewound the pointer, or a competitor that re-minted the token — is refused with claim_lost rather than merely bounded by the per-step status CAS. A NULL asserts nothing, for a callback whose dispatch token predates claim binding. The parameter was added by DROP FUNCTION + CREATE (a CREATE OR REPLACE cannot add one, and a coexisting old signature would make the name ambiguous to PostgREST), with EXECUTE re-granted on the new signature only
The job_terminal refusal is checked AFTER the generation assertion, so a restart — which returns the job to a live status under a NEW generation — keeps reporting stale_generation, the outcome its callers already branch on. It exists because a step write landing after a concurrent fail-out or halted-finalize would reopen a settled job's step set behind its own terminal status, leaving the two permanently inconsistent with nothing to recompute them. Every caller reaches this function on a live-run path (the callback route's already-completed / already-failed re-entry paths skip the write entirely and only re-run their cascades), so a terminal parent always means another actor already resolved the job's fate
On updated: writes status; result_data, request_data, retry_count and duration_seconds via COALESCE(p_…, existing) so a NULL means "leave it alone" rather than erasing it; error_message set when supplied and cleared when moving back into a live status (pending/ready/in_progress); stamps started_at on in_progress and completed_at on a terminal status — mirroring the app's updateStepStatus stamping rules in services/processing/base.ts
restart_processing_job_atomic(p_job_id, p_expected_restart_count, p_from_step_key) - Restarts a job, or retries it from one step, in ONE transaction
Returns TABLE(outcome text, new_restart_count integer)
Compare-and-swaps the caller's observed generation, bumps it, rewinds the job to in_progress with completed_at/error_message NULL, the execution-group claim (pointer, token, stamp) cleared and the sweep bookkeeping (sweep_attempts, sweep_fingerprint, last_swept_at) reset, then resets the step rows the restart revives — all under the one lock, so no transition can interleave between the compare-and-swap that authorises the reset and the reset itself
p_from_step_key NULL is a FULL restart: every step returns to its initial state including result_data/request_data and retry_count = 0, execution group 1 ready and later groups pending; the job's progress_percentage returns to 0 and started_at is re-stamped
A named p_from_step_key is a from-point retry: that step returns to ready with its error and stale result cleared but its accumulated retry_count deliberately PRESERVED (that count is what a retry ladder is measured against), and only the non-completed steps the failure invalidated are reset — later execution groups, direct dependents (depends_on_steps @> ARRAY[p_from_step_key]), and same-group siblings still in failed/pending/ready. A completed step is never reset: its result is still valid and re-running it would repeat its side-effects. Job progress and started_at are kept
Clearing the execution-group high-water mark is safe ONLY because of the generation bump: the bump retires every callback token the abandoned attempt minted, and without the rewind a from-point retry could not re-claim the very group it just reset (a claim must be strictly beyond the mark)
Outcomes: restarted (carrying new_restart_count) | superseded (generation moved) | step_not_found (p_from_step_key names no step on this job) | not_found
Callers MUST dispatch under the returned new_restart_count, never a re-read — between the transaction and a re-read a concurrent restart can establish a later generation, and adopting it would drive a group claim under someone else's run
resume_processing_job_atomic(p_job_id, p_expected_restart_count, p_from_group, p_blocked_processable_statuses) - Resumes a finalized-then-reopened job from an execution group, in ONE transaction
Returns TABLE(outcome text, new_restart_count integer)
Compare-and-swaps the observed generation and BUMPS it, returns the job to in_progress with completed_at NULL, the execution-group claim cleared (a halted job left the mark parked on the group it stopped at, which would otherwise reject the re-run as already claimed) and the sweep bookkeeping reset, then makes every step at or after p_from_group runnable (that group ready, later groups pending, payload and retry_count cleared) regardless of its current status — crucially including not_needed, which is exactly what finalize_halted_job_atomic stamped on the tail this resume exists to run. Earlier groups keep their completed results, so the record is not re-processed from scratch
The bump is deliberate and is the contract: this call re-drives the resumed group itself, resetting its steps regardless of status, so no in-flight result for that group could survive the resume anyway. Bumping is what makes a straggler callback from the halted run report a confirmed absence rather than land on freshly reset steps, and what makes a second concurrent resume — or a stall sweeper's fenced finalize — lose cleanly instead of resetting this run a second time
p_blocked_processable_statuses text[] DEFAULT NULL fences the resume against a cancellation. When supplied and the job's processable_type is transactions, the record is read FOR UPDATE after the job lock — the row LOCK, not the status read, is what orders this resume against a concurrent cancel compare-and-swap — and a record already in one of those statuses is refused with record_terminal, zero-write, before the step reset. A missing record is deliberately left alone: this arm exists to refuse REVIVING a terminal record, and inventing an outcome for an absent one would change the contract for the non-transaction processable types it does not cover. It keeps a DEFAULT (unlike the five fenced domain RPCs) so a caller predating the fence gets exactly the prior best-effort behaviour mid-rollout. Values are validated non-empty and castable to transaction_status; invalid input RAISES. Added by DROP FUNCTION + CREATE, as for the guarded step write
Outcomes: resumed (carrying new_restart_count) | superseded | record_terminal | no_steps | not_found. As for restart, callers dispatch under the returned generation. no_steps is checked before any write: no step sits at or after p_from_group, so reopening the job would return it to a live status with nothing runnable in it
fail_stalled_job_atomic(p_job_id, p_expected_restart_count, p_error_message) - Brings a stalled job to a terminal state in ONE transaction
Returns TABLE(outcome text, job_status text)
p_expected_restart_count is MANDATORY: a fail-out decision is always made from a specific observation, and a restart since then means terminating the job would kill a live run
Rejects superseded (generation moved) and already_terminal (overall_status not pending/in_progress) before any write
The step set is derived under the lock, never from a caller snapshot — a snapshot taken before the lock can name steps that have since reached a terminal verdict, and marking those failed would destroy a genuine result
Two shapes, distinguished by whether any step is still non-terminal. Missed final write (every step already settled): the run itself is not wrong, so the legitimate status is recomputed from the steps and NO error_message is recorded — forcing failed here would destroy a successful run's verdict. Genuine death: every still-unfinished step is marked failed carrying p_error_message, and the job status is derived from the resulting set
The job write clears current_processing_group_claim_id/_claimed_at (a terminal job owns no group) but deliberately PRESERVES current_processing_group — the high-water mark stays meaningful for diagnostics and for a later restart — and keeps an existing completed_at so a stamp a step handler already made keeps the audit trail's real ordering
Outcomes name the status actually WRITTEN, never which shape was taken, so a caller keying a user-facing alert off the outcome can never mistake a failed run for a merely-missed status write: completed_recomputed | partial_success | failed | superseded | already_terminal | not_found
finalize_halted_job_atomic(p_job_id, p_expected_restart_count) - Finalizes a job whose processing was intentionally halted, in ONE transaction
Returns TABLE(outcome text, job_status text)
For a run stopped on purpose (e.g. the record was put On Hold, or reached a terminal status mid-pipeline) rather than one that died. Asserts the caller's observed generation and that the job is still live, marks every not-yet-run step (ready/pending/in_progress) not_needed, then derives and writes the terminal overall_status from the resulting step set, preserving an already-stamped completed_at
A job with no step rows has nothing to derive a verdict from; that is checked BEFORE the sweep so the rejection is genuinely zero-write
Outcomes: finalized (carrying the status written) | superseded | already_terminal | no_steps | not_found. Every non-finalized outcome is a legitimate no-op for the caller to log, not an error

Fenced Processor Domain-Write Functions¶

A step processor's domain writes were sequential PostgREST requests: no transaction, and no assertion that the run producing them was still the job's current run. The five functions below each carry out ONE processor's whole domain commit inside one transaction, behind the same fence the lifecycle RPCs and update_processing_step_guarded use. The guarantee is LINEARIZATION, not erasure: once a lifecycle transition commits, no fenced write from the superseded generation can commit after it, because every fenced call takes the same processing_jobs row lock. Writes that committed BEFORE the transition are old-run work the new run supersedes by re-driving its steps. See PROCESSING_ARCHITECTURE.md → Fenced processor domain commits for the surrounding design and the residuals.

TWO row locks, two distinct guarantees. The processing_jobs row lock serializes generation, execution-group claim and lifecycle ordering; it does NOT serialize against record cancellation, which never touches the job row. The transactions row lock, taken after it, separately serializes record eligibility against a concurrent cancel/reject compare-and-swap — a plain status read races that CAS. Lock order is always job → transaction → children, matching every other path in the pipeline; nothing in the trigger graph reached by these writes acquires them in reverse (the paid-marking triggers on payments/payment_reconciliations write transactions but never touch processing_jobs, and the call already holds the transaction row lock before their INSERT fires).

All five are SECURITY INVOKER, search_path = '' with every object schema-qualified, VOLATILE, EXECUTE granted to service_role ONLY (revoked from PUBLIC/anon/authenticated) — orchestration primitives for the pipeline's server-side callers, never a Data API surface. None carries an EXCEPTION handler: an expected rejection is returned as an outcome and writes NOTHING, while a genuine write error RAISES so the whole call rolls back with its SQLSTATE intact. TypeScript wrappers in services/processing/base.ts call them through callSingleRowFunction and branch on the outcome, never on a SQLSTATE.

Shared rejection ladder, evaluated in a FIXED precedence entirely BEFORE any write (so no outcome is ever returned from between two writes):

Outcome	Meaning
`not_found`	The job row is absent
`job_record_mismatch`	The job does not process the transaction the call named — the fence cannot be pointed at another record
`stale_generation`	`restart_count` ≠ `p_expected_restart_count`; the caller's run has been superseded
`job_terminal`	The job's fate is already settled on this generation
`claim_lost`	The execution-group claim moved to another worker (skipped when `p_expected_claim_id` is NULL)
`record_missing`	The transaction row is absent — `processable_id` is polymorphic and carries no foreign key
`record_terminal`	The transaction reached a status in `p_blocked_statuses`

p_blocked_statuses and p_expected_claim_id are REQUIRED on all five, with no defaults. A status set is safety-critical, and passing an explicit NULL claim (which skips the claim assertion, for a caller whose dispatch token predates claim binding) must be a conscious act rather than an omitted argument. Status arrays are validated non-empty and castable to transaction_status by the shared private.assert_transaction_status_set(text[], text) helper; invalid input is a caller bug and RAISES rather than returning an outcome. All jsonb inputs are type-checked, and object payloads are key-allowlisted.

Three rejections must not be treated as benign. record_terminal, job_record_mismatch and record_missing all leave the job's generation, liveness and claim valid, so the guarded step write that follows would accept a completion for a commit that never happened. record_terminal raises ProcessingRecordTerminalError and the orchestrator routes through the halt path; the other two are broken invariants (a job wired to the wrong record, or a record that is gone) and are surfaced as genuine errors so the step FAILS. Only not_found / stale_generation / job_terminal / claim_lost are completion-safe, because the guarded write independently refuses each of them.

create_transaction_payment_fenced(p_job_id, p_expected_restart_count, p_expected_claim_id, p_transaction_id, p_blocked_statuses, p_payment jsonb, p_reconciliation jsonb) - Creates a transaction's payment and its allocation in ONE transaction
Returns TABLE(outcome text, payment_id uuid, reconciliation_id uuid)
The convergence probe runs under BOTH locks and follows the live uniqueness semantics — the partial unique index idx_payment_reconciliations_unique (payment_id, transaction_id) WHERE status = 'Active' — picking the winner on the same (created_at, id) total order the pre-RPC guard used, so concurrent runs agree on one survivor. Voided allocations are deliberately ignored: they are the audit of a withdrawn attempt, not live work
Three success arms, ALL returning the same canonical id pair: converged (a live allocation already covered the record; nothing written), repaired (the caller's OWN proposed payment id exists without a live allocation, is locked FOR UPDATE, and still exactly matches the proposed identity/status tuple before its allocation is added; an unrelated or concurrently amended payment is never adopted), created (both rows inserted). Callers MUST adopt and re-read the returned payment id on every success arm: a prior attempt can commit the allocation and then fail its separate Scheduled → Paid promotion, so neither a pre-read allocation nor converged alone proves finalisation succeeded
A freshly inserted Paid payment receives paid_at and paid_by_user_id in this same fenced transaction. The payload values are preferred; paid_at falls back to now() and paid_by_user_id falls back to the payment or reconciliation creator. The repaired arm fills only missing stamps on the caller's own orphan Paid payment, while converged and reused payments remain untouched
p_payment.reuse_payment_id names a payment the caller matched exactly; it is allocated against but never created, repaired or cancelled by this call. After the job fence the function takes the shared transaction advisory gate, then transaction → payment rows; the allocation trigger re-enters the same xact gate. The reused row is re-validated under lock against status, project, method, date, total, currency, and payee expectations. A candidate that moved returns reuse_conflict (zero-write); processPaymentCreation retries once without a reuse id
Outcomes: created | repaired | converged | reuse_conflict | the seven shared rejections. This replaces the hand-rolled convergence guard and its self-documented double-create window (nothing in the schema serialized two simultaneous runs — the only payments unique index that could is partial on status = 'Scheduled', and this path writes the payment already Paid)
claim_transaction_for_approval_fenced(p_job_id, p_expected_restart_count, p_expected_claim_id, p_transaction_id, p_blocked_statuses, p_allowed_from_statuses, p_new_status, p_actor_user_id) - Compare-and-swaps a transaction into its pre-approval status
Returns TABLE(outcome text, current_status text)
The transaction row lock is what makes the status the CAS reads the status it writes against. p_allowed_from_statuses is a POSITIVE in-list, so a status the caller did not name is refused rather than assumed claimable
p_actor_user_id is REQUIRED and stamped onto updated_by_user_id (NOT NULL with no trigger behind it) by the RPC — attribution is never caller-writable payload data
Outcomes: claimed | status_conflict (the record left the allowed set without being terminal — a legitimate concurrent transition, carrying the live status) | the seven shared rejections
Scope note: the fence excludes a stale run claiming AFTER a lifecycle transition committed. A claim that committed before the transition is old-run work, and a crash between the claim and the approval submission remains a pre-existing residual of the approval design, unchanged here
bind_transaction_entities_fenced(p_job_id, p_expected_restart_count, p_expected_claim_id, p_transaction_id, p_blocked_statuses, p_updates jsonb, p_actor_user_id) - Commits entity matching's binding onto a transaction
Returns TABLE(outcome text)
p_updates is restricted to an allowlist of bindable columns (entity_id, customer_entity_id, supplier_project_relationship_id, customer_project_relationship_id, ai_linking_logic, ai_role_logic); any other key RAISES, so this can never become an arbitrary transactions writer. An empty object RAISES too — the caller skips the call rather than sending one. Only supplied keys are written, so a partial binding never clears a column another step established
p_actor_user_id is REQUIRED and stamped onto updated_by_user_id, never taken from p_updates
Outcomes: bound | the seven shared rejections
Entity/relationship find-or-create stays OUTSIDE the fence by design: a row a stale run created is a convergent-dedupe row the next run reuses, whereas a BINDING written by a stale run would attach that run's conclusion to the live record
replace_transaction_items_fenced(p_job_id, p_expected_restart_count, p_expected_claim_id, p_transaction_id, p_blocked_statuses, p_items jsonb) - Replaces a transaction's line items wholesale
Returns TABLE(outcome text, items_written integer)
Atomicity is the second purpose: issued as two PostgREST requests the delete and the insert could leave a record with NO line items at all, whereas an insertion failure here RAISES and rolls the delete back with it, preserving the original items. Rows are fully prepared by the caller and line numbers taken as supplied
Outcomes: replaced (carrying the row count written) | the seven shared rejections
Manual UI edits keep the ordinary createTransactionItems path; this is the processor's commit only
insert_daily_allocations_fenced(p_job_id, p_expected_restart_count, p_expected_claim_id, p_transaction_id, p_blocked_statuses, p_allocations jsonb, p_links jsonb) - Inserts a transaction's daily budget allocations and their line-item links
Returns TABLE(outcome text, allocations_written integer, links_written integer)
Allocations are find-or-create on the live natural key budget_item_daily_allocations_budget_item_id_production_day_key (ON CONFLICT DO NOTHING, inserted ordered by the FULL unique key so two concurrent callers with overlapping payloads contend on the speculative index entries in one agreed order — ordering by only the day leaves ties resolved by input order, which is the deadlock. The link insert is ordered by its own full key for the same reason). project_id is deliberately unset — the set_project_id BEFORE trigger derives it
Each supplied allocation id is a PROPOSAL: where a row already existed the caller's id was discarded, so every link is REMAPPED onto the canonical allocation id before insertion, or it would violate the foreign key or attach actuals to the wrong row. The remapping is TOTAL and unambiguous by construction — duplicate (budget_item_id, production_day_id) keys in p_allocations, a supplied id that is absent or repeated across two natural keys (the remap table keys on it, so a one-to-many map is structurally impossible), or a link naming an allocation absent from p_allocations, RAISE and roll the whole call back rather than silently dropping or misfiling actuals
Every link's transaction_item_id must belong to p_transaction_id, validated before any insert. The column's foreign key pins it to transaction_items, not to THIS record, so a stale or mis-assembled payload would otherwise write actuals against a record this call never locked and never fenced
Links converge on their own unique key (budget_item_daily_allocation_id, transaction_item_id), so a duplicate callback re-running this step writes nothing new
Outcomes: inserted (carrying the counts of allocations and links NEWLY written — zeroes mean the call converged onto existing rows, which is success) | the seven shared rejections

Processing Status Summary Function¶

get_processing_job_status_summary(p_processable_type, p_project_id, p_completed_since, p_limit_per_status, p_bucket, p_cursor_created_at, p_cursor_completed_at, p_cursor_id) — the processing status bar's single read path. Returns jsonb. SECURITY INVOKER, STABLE, search_path = '', EXECUTE granted to authenticated + service_role (revoked from PUBLIC/anon).

SECURITY INVOKER is the entire security model, not a detail. Every row and every count is post-RLS: the function only ORDERS, LIMITS and COUNTS rows the caller's own processing_jobs SELECT policy already admits, so it inherits that policy's project membership arm, its creator disjunct, and its record-visibility conjunct — which itself inherits the whole transactions SELECT policy including the sensitivity restriction twin and the pre-pipeline window gate (see AI_PROCESSING.md → Processing Jobs). No visibility rule is restated here, so none can drift. Converting it to SECURITY DEFINER would turn it into an open /rpc endpoint returning any tenant's job metadata and counts for an arbitrary project id. The behavioural containment proof — a job whose transaction is RLS-hidden appears in neither a page nor a count — is tests/23_processing_status_summary_rpc.sql; the INVOKER/ACL sentinels are in tests/03_functions.sql.

Two call modes.

p_bucket IS NULL (initial call) — returns page one of all five buckets PLUS exact per-bucket counts.
p_bucket set (load-more) — returns one bucket's next page; counts is JSON null (present-and-null, so a consumer can tell "not requested" from a malformed response). Counts are not recomputed, because the client already has them.

Return shape (keys pinned exactly by pgTAP):

{
  "counts": {
    "pending": 3,
    "in_progress": 1,
    "failed": 1,
    "partial_success": 1,
    "completed": 4,
  } /* or null on a load-more call */,
  "buckets": {
    "pending": [/* rows */],
    "in_progress": [],
    "failed": [],
    "partial_success": [],
    "completed": [],
    /* a load-more response carries ONLY the requested bucket key */
  },
}

Each row carries exactly eleven slim fields — id, overall_status, processable_id, processable_type, project_id, created_at, completed_at, template_name, template_description, completed_ui_steps, total_ui_steps. Deliberately absent: the process_templates embed, the step rows, and the request_data / result_data jsonb columns that carry full AI prompt I/O (~40KB per job) and dominated the old payload. Step DETAIL is fetched on demand by the details popover via a plain PostgREST select, not here. Every bucket value is an array, never null.

Ordering and cursors. Non-completed buckets order created_at DESC, id DESC and take the (p_cursor_created_at, p_cursor_id) pair. The completed bucket orders completed_at DESC, id DESC and takes (p_cursor_completed_at, p_cursor_id). Both use a row-value keyset comparison ((completed_at, id) < (cursor_ts, cursor_id)), never OFFSET — offset pagination re-reads and discards every skipped row, and drifts when a new job arrives mid-scroll. The trailing id is not cosmetic: two jobs sharing a completed_at are ordered by id, and a completed_at-only cursor would silently skip the tied sibling.

No NULLS LAST anywhere, and that is an invariant rather than an omission: p_completed_since is a non-null lower bound on completed_at, so every row in the completed bucket has a non-null completed_at by construction. A completed job whose completed_at is NULL is excluded from the bucket entirely.

p_completed_since is mandatory. It is the caller's local midnight, which the database cannot derive (the server has no knowledge of the browser's timezone). Defaulting it would make the bucket silently disagree with the "completed today" label the UI renders.

Validation — every rejection RAISEs rather than returning empty, because a silently-empty result is indistinguishable from "this project has no jobs" and would render a permanently-stale bar: null p_processable_type / p_project_id / p_completed_since; p_bucket outside the five bucket names; cursor arguments supplied without p_bucket; a half-supplied cursor pair; and a cursor column belonging to the other ordering (e.g. p_cursor_created_at on the completed bucket).

p_limit_per_status is clamped server-side — LEAST(GREATEST(COALESCE(p_limit_per_status, 12), 1), 100). NULL takes 12 (the UI's PAGINATION_DEFAULTS.ITEMS_PER_PAGE × INITIAL_BATCH_MULTIPLIER), 0/negative floor to 1, oversized caps at 100. A caller-supplied value never reaches LIMIT directly.

Query shape — page ids FIRST. Per bucket, an ordered, limit-applied CTE selects only the id and cursor columns; the template join and the step-progress aggregate then run over at most limit ids per bucket. This is the whole point: measured at 3019 jobs / 18 285 steps, the step aggregate probes 60 ids (loops=60, 8 step rows each) instead of the window's entire step set, and the initial call reads 5 862 buffers against the replaced fat select's 101 374 — 17.3× fewer. The process_templates join is a LEFT JOIN deliberately: the FK is NOT NULL with ON DELETE RESTRICT so the row always exists, but the table carries its own RLS, and an INNER join would let a template the caller cannot read drop an otherwise-visible job from the page while the counts still included it. Absent template columns degrade to null; a job never disappears.

completed_ui_steps / total_ui_steps mirror the UI's progress arithmetic verbatim — ProcessingStatusIndicator's progress bar, and processingJobProgressPercent (utils/processing.ts) which derives the percentage from this pair: the denominator counts steps whose process_template_steps.display_in_ui is true; the numerator counts those in status completed OR not_needed (a step the pipeline determined was unnecessary is progress, not an omission). A job with no step rows reports 0 of 0 and still appears in its page.

result_summary projects processing_jobs.metadata ->> 'summary' — the only metadata key any UI surface renders (the completed-job result alert). It is null when the key is absent or non-textual, which is exactly the alert's render condition, so the whole metadata jsonb never has to cross the wire.

Indexing. Only the completed bucket has a supporting index, idx_processing_jobs_type_project_completed_at — partial on overall_status = 'completed', ordered (processable_type, project_id, completed_at DESC, id DESC) so the range filter and the keyset row-value comparison both resolve as index conditions. There is deliberately NO equivalent for the four non-completed buckets: under the processing_jobs SELECT policy the planner never chose one, because the policy's record-visibility CASE cannot become an index condition, so every candidate row must be evaluated and an ordered index can never terminate early. A (processable_type, project_id, overall_status, created_at DESC, id DESC) index was built and measured at 13× production scale — never selected, so it would be pure write amplification on a table the pipeline updates on every step transition. Re-measure under RLS before adding it back; a service-role-only benchmark will mislead you (there the same index returns in 0.036 ms).

Integration System Functions¶

auto_enable_integrations() - Automatically enables/disables integrations based on project country_code
Trigger function that executes AFTER INSERT OR UPDATE OF country_code on projects table
SECURITY DEFINER with search_path = ''
On INSERT: creates project_integrations for providers matching the project's country via integration_provider_countries where auto_enable_for_country = true, then auto-enables all active features for those integrations with default trigger configs
On UPDATE (country_code change): disables auto-enabled integrations whose provider no longer covers the new country (sets is_active = false, connection_status = 'Disconnected'), enables new integrations for the new country (or re-enables previously disabled ones), and auto-enables features for newly created/re-enabled integrations
Uses ON CONFLICT DO NOTHING for inserts and ON CONFLICT DO UPDATE for upserts to handle idempotency

Budget Functions¶

batch_update_estimated_costs(p_updates JSONB) - Bulk-updates estimated cost fields on many budget_item_daily_allocations rows in a single transaction
Input: JSONB array of {allocation_id, estimated_cost?, estimated_quantity?, estimated_cost_note?, canceled_estimated_cost?}
Returns: SETOF budget_item_daily_allocations (the updated rows)
SECURITY INVOKER with search_path = '', GRANT EXECUTE TO authenticated; allocation RLS applies as the caller
Replaces the previous client-side Promise.all per-row UPDATE pattern that triggered N concurrent expensive RLS evaluations and crashed the database when autofilling estimated costs
Only writes fields explicitly present in the input JSON; missing fields fall back to current row values, including the manually cleared estimate amount
Rejects the statement when RLS filters any requested row or duplicate IDs prevent one returned row per input, preserving all-or-nothing behavior
Sets updated_by_user_id and updated_at automatically
import_budget(p_budget JSONB, p_headers JSONB, p_items JSONB) → JSONB - Atomic CSV budget import
Creates the budget + full header/item tree in ONE transaction under the caller's own RLS. SECURITY INVOKER (writes are policy-checked as the caller — a non-cleared caller cannot bypass the write policies), VOLATILE, search_path = ''
p_budget {id, project_id, title} (all required, title non-empty); status forced Draft, enable_estimated_costs / enable_daily_allocations forced false
p_headers 1..2000 objects {id (unique across payload), parent_budget_header_id (null or a payload id — closure-checked, and the chain is validated to be acyclic as a set — the row-level cycle trigger cannot see sibling rows of the same multi-row INSERT), account_code, title (non-empty)} — budget_id stamped from p_budget.id; project_id derived by the BEFORE INSERT trigger
p_items 1..10000 objects {budget_header_id (a payload header id), account_code, title (non-empty), description, original_quantity, original_rate, original_total (required), interval_type, interval_quantity, is_unbudgeted} — ids DB-generated; project_id derived
Static SQL validation over jsonb_to_recordset; explicit column allowlists on every INSERT; no EXCEPTION handler so any failure (validation or RLS) rolls the whole import back — no orphaned partial Draft budget. Returns {header_count, item_count}
GRANT EXECUTE TO authenticated; REVOKE FROM PUBLIC, anon. Replaces the previous multi-round-trip PostgREST fan-out that could time out mid-import and strand a Draft budget
private.sync_allocation_links_on_budget_item_change() → trigger (DEFINER, private schema)
Fired by trg_transaction_items_sync_allocation_links — AFTER UPDATE OF budget_item_id ON transaction_items FOR EACH ROW WHEN (OLD.budget_item_id IS DISTINCT FROM NEW.budget_item_id).
Keeps a line item's budget_allocation_transaction_items links pointing at allocations of the CURRENT budget item. Gated up front on the transaction's project → its current_budget_id → budgets.enable_daily_allocations = true; returns early (no allocation-table read) otherwise. Clearing the budget item to NULL deletes all the item's links. On a real change it fails closed if the new budget item is not in the project's current budget (RAISE), deletes cross-project stale links, find-or-creates the target allocation per production day (defaults: quantity 1, rate 0, added_from_processing = true, added_from_estimated_costs = false, estimated_* = NULL; project_id derived by the set-project-id trigger; ON CONFLICT DO NOTHING so pre-existing allocations are never touched), then per day either dedupes (deletes stale links when a target link already exists — never sums amounts) or re-points the earliest-created stale link (preserving amount + created_at) and deletes the rest.
SECURITY DEFINER with search_path = '' so a transaction editor without budget:edit/budget:delete can still re-classify; trigger-only, so REVOKE EXECUTE FROM PUBLIC, anon, authenticated.
private.rescale_allocation_links_on_subtotal_change() → trigger (DEFINER, private schema)
Fired by trg_transaction_items_update_allocation_link_amounts — AFTER UPDATE OF subtotal ON transaction_items FOR EACH ROW WHEN (OLD.subtotal IS DISTINCT FROM NEW.subtotal).
Rescales a line item's EXISTING budget_allocation_transaction_items links so their amounts sum EXACTLY to the line's new subtotal (the pipeline creates links from the subtotal it saw at processing time, so a later human correction would otherwise be lost to the budget). A NULL subtotal is read as 0. Weights, in precedence order: pro-rata on the old amounts (amount / old_sum, signs preserved); abs-value pro-rata when the old amounts cancel to zero with mixed signs; an equal 1/n split when every old amount is zero. Apportionment is largest-remainder in penny space (each link takes trunc() of its exact share; the |residual| links whose fractional remainder is furthest in the residual's OWN direction take one penny more/fewer, ties broken by production day then created_at), so accumulated rounding can never drag a link past zero — the failure mode of round-each-then-dump-the-residual at high link counts — and the extra penny lands on the chronologically earliest day. The direction is load-bearing and the ranking key is therefore multiplied by sign(residual): trunc() rounds toward zero, so a mixed-sign set whose bases sit ABOVE their exact shares yields a NEGATIVE residual, and the penny must then be REMOVED from the most-negative remainders rather than taken off the largest positive one (doing the latter sign-flips a link and lands it more than a penny from its exact share). With a zero residual the sign is 0, no adjustment applies, and the key falls through to the chronological tie-break. Any sub-penny subtotal remainder goes on the chronologically first link, signed — half-up rounding of the subtotal can overshoot, so the term corrects in either direction. Chronology, precisely: dated days order by calendar_date and ALWAYS precede undated days (the ordering is calendar_date NULLS LAST); among undated days the fallback key is day_number (itself NULLS LAST), with created_at/id as a deterministic tie-break. The ordering keys are projected out of the locked subquery and restated in an explicit row_number() OVER (ORDER BY …) window, so the ordinals never depend on the inner subquery's physical output order reaching the window node. Contained to the line's same-project links (bati.project_id = the edited transaction's project_id, the link's project_id being trigger-derived and immutable) — a budget_item_id can point cross-project, and this DEFINER fn must never write into a project the caller holds no permission on, so a cross-project stale link is left untouched and never weights the apportionment (the re-classification trigger deletes those). Non-finite inputs are never rescaled: the fn returns without writing when the new subtotal is non-finite or when ANY locked link amount is (one such amount makes every weight non-finite and would poison the finite siblings through the DEFINER boundary). The durable rejection of non-finite monetary values across ALL writers lives in the table-level CHECKs chk_transaction_items_{subtotal,total,tax_total}_finite and chk_budget_alloc_tx_items_amount_finite; these in-fn guards are defense-in-depth behind them, kept deliberately so the DEFINER boundary stays fail-closed if a constraint is ever dropped (pgTAP suite 22 tests them by dropping the two constraints inside its rolled-back transaction). Never creates or deletes a link (that is the pipeline's job) and never writes the allocation tables, so it needs no enable_daily_allocations / current-budget gate and no fail-closed RAISE; a line with no links is a no-op. Locks ONLY the link rows (FOR UPDATE OF the link alias) — locking the allocation/day rows would let a caller holding merely transaction:edit take locks on budget records through this DEFINER fn. The trigger name sorts AFTER trg_transaction_items_sync_allocation_links, so an UPDATE changing both budget_item_id and subtotal re-points/de-dupes first and rescales the surviving set second.
Known, accepted concurrency window: only links that EXIST when the subtotal edit commits are rescaled, so a pipeline link-INSERT whose amount was computed from a subtotal read BEFORE a concurrent subtotal edit can commit after the rescale and is not re-summed until the next subtotal change. That is the pre-existing pipeline read-then-write class, strictly narrowed (not introduced) by this trigger.
SECURITY DEFINER with search_path = '' because the link table's UPDATE policy requires budget:edit, which transaction editors lack; trigger-only, so REVOKE EXECUTE FROM PUBLIC, anon, authenticated.
private.enforce_allocation_link_budget_item_match() → trigger (DEFINER, private schema)
Fired by trg_budget_allocation_transaction_items_enforce_item_match — BEFORE INSERT OR UPDATE OF budget_item_daily_allocation_id, transaction_item_id ON budget_allocation_transaction_items FOR EACH ROW (unconditional; both FK columns are covered so a re-key of EITHER is validated).
Fail-closed guard: rejects (RAISE) any link whose allocation's budget_item_id differs from the linked transaction line item's budget_item_id. The error text echoes only the caller-supplied ids (the transaction item + allocation), never the derived budget-item ids — this DEFINER fn must not become an identifier oracle. Reads the item row FOR SHARE (serialises against a concurrent budget-item edit; lock order item-row → link-row matches the sync trigger for every app path, and PostgreSQL's deadlock detector turns any residual cross-path interleaving into a retryable error).
SECURITY DEFINER with search_path = ''; trigger-only, so REVOKE EXECUTE FROM PUBLIC, anon, authenticated.
private.guard_daily_allocation_budget_item_rekey() → trigger (DEFINER, private schema)
Fired by trg_budget_item_daily_allocations_guard_budget_item_rekey — BEFORE UPDATE OF budget_item_id ON budget_item_daily_allocations FOR EACH ROW.
Fail-closed guard making budget_item_id immutable on this table: rejects (RAISE) ANY change, referenced or not. Re-keying would mis-file linked transactions' actuals onto the new budget item AND leave the write-once denormalised project_id stale (the set-project-id trigger derives project_id on INSERT only). The guard is deliberately unconditional — a links-EXISTS relaxation would be racy against a concurrent link INSERT (an unlocked read the trigger can't re-run after the FK wait), and no app path re-keys an allocation's budget item anyway; delete-and-recreate is the sanctioned path (mirroring the project_id write-once convention). Error text echoes only the caller-supplied allocation id.
SECURITY DEFINER with search_path = ''; trigger-only, so REVOKE EXECUTE FROM PUBLIC, anon, authenticated.
private.guard_attachment_form_submission_id() → trigger (INVOKER, private schema)
Fired by trg_attachments_guard_form_submission_id — BEFORE INSERT OR UPDATE OF form_submission_id ON attachments FOR EACH ROW.
Server-management guard: rejects (RAISE … insufficient_privilege) an INSERT that sets, or an UPDATE that changes, attachments.form_submission_id when current_user IN ('authenticated','anon'). SECURITY INVOKER on purpose — under DEFINER current_user would be the function owner and the caller could never be observed; INVOKER makes current_user the actual caller role, catching both JWT-carrying and claim-less authenticated/anon sessions (an auth.role() check alone misses the claim-less case). No-op when form_submission_id is and stays NULL. service-role and migration roles pass.
search_path = ''; trigger-only, so REVOKE EXECUTE FROM PUBLIC, anon, authenticated.
private.guard_form_attachment_insert() → trigger (DEFINER, private schema)
Fired by trg_attachments_guard_form_attachment_insert — BEFORE INSERT ON attachments FOR EACH ROW WHEN (NEW.form_submission_id IS NOT NULL).
Race guard: locks the submission row FOR UPDATE, requires status IN ('requested','in_progress'), and enforces a per-submission cap of 20 attachments. Serialises against the submission's terminal transition so a post-completion attachment INSERT is rejected regardless of the TS-layer check ordering. DEFINER so it reads form_submissions past the caller's RLS.
SECURITY DEFINER with search_path = ''; trigger-only, so REVOKE EXECUTE FROM PUBLIC, anon, authenticated.
private.guard_form_attachment_delete() → trigger (DEFINER, private schema)
Fired by trg_attachments_guard_form_attachment_delete — BEFORE DELETE ON attachments FOR EACH ROW WHEN (OLD.form_submission_id IS NOT NULL).
Race guard: locks the submission row FOR UPDATE, requires active status, and rejects (RAISE) the delete when the attachment has already been promoted (referenced by project_relationship_attachments or entity_attachments). DEFINER so it reads the submission + join tables past the caller's RLS.
SECURITY DEFINER with search_path = ''; trigger-only, so REVOKE EXECUTE FROM PUBLIC, anon, authenticated.

Auth Security Functions¶

check_phone_exists(p_phone_number TEXT) - Securely checks if a user with the given phone number exists
Returns JSONB: { "exists": boolean, "is_invite_pending": boolean }
SECURITY DEFINER with search_path = ''
Replaces the vulnerable anon SELECT policy on the users table
Callable by anon role for pre-auth phone verification
Only returns minimal data to prevent PII exposure

Vault Wrapper Functions¶

SECURITY DEFINER wrappers in the public schema that provide access to vault.decrypted_secrets, vault.create_secret, and vault.update_secret. Required because the vault schema cannot be exposed via the Supabase Data API on newer projects.

get_vault_secret_by_name(secret_name TEXT) → TEXT - Retrieve a decrypted secret by name
get_vault_secret_by_id(secret_id UUID) → TEXT - Retrieve a decrypted secret by UUID
create_vault_secret(new_secret TEXT, new_name TEXT, new_description TEXT) → UUID - Create a new vault secret
update_vault_secret(secret_id UUID, new_secret TEXT) → VOID - Update an existing vault secret

All are SECURITY DEFINER with search_path = '', REVOKE ALL FROM PUBLIC, GRANT EXECUTE TO service_role only.

Key Triggers¶

Auth sync triggers on auth.users for INSERT, UPDATE, DELETE
Updated_at triggers on all tables with updated_at column
Project setup trigger on project INSERT
Production phase days trigger (handle_production_phase_days) AFTER INSERT/UPDATE/DELETE on production_phases — manages production days using soft-delete; skips generation when dates are NULL; reactivates removed days on phase extension; uses positional ordering for date recalculation (not day_number)
Renumber working days RPC (renumber_working_days(p_phase_id UUID)) — NULLs all day_numbers then assigns sequential numbers to Working days only, ordered by calendar_date
Message insert trigger - Updates conversation and instance timestamps
Message threading trigger - Auto-populates root_message_id on INSERT/UPDATE of parent_id
Person name sync trigger - Syncs firstName/lastName changes to auth.users metadata
Payment status trigger (trg_mark_transaction_paid_on_payment) AFTER INSERT OR UPDATE on payments - Marks transactions as Paid when a payment is inserted/becomes Paid and private.transaction_is_fully_settled holds for the linked record
Reconciliation status trigger (trg_mark_transaction_paid_on_reconciliation) AFTER INSERT OR UPDATE on payment_reconciliations - Marks the affected transaction Paid when an Active allocation against an already-Paid payment fully settles it; covers the pay-now write order (payment inserted Paid before its reconciliations)
Transaction approval trigger (trg_check_transaction_paid_on_approval) - Auto-marks transaction as Paid on approval if already fully settled via reconciliations
Transaction direction guard (trg_check_transaction_total_sign_flip) BEFORE UPDATE OF total on transactions - Refuses a change of the total's SIGN while an Active allocation on a non-Cancelled payment settles the record
Payment direction guard (trg_check_payment_total_sign_flip) BEFORE UPDATE OF total_amount on payments - Refuses a change of the total's SIGN while an Active allocation moves money on the payment
Auto-enable integrations trigger (trg_auto_enable_integrations) AFTER INSERT OR UPDATE OF country_code on projects — automatically enables country-specific integrations and their features when a project is created or its country changes
Budget header cycle prevention (trg_prevent_budget_header_cycle) BEFORE INSERT/UPDATE OF parent_budget_header_id on budget_headers — runs prevent_budget_header_cycle(), which walks the ancestor chain (capped depth, recursive CTE) and raises if the row itself appears in the chain. Trigger-only; no backfill of existing rows
Budget-tree project_id derivation (trg_budget_headers_set_project_id / trg_budget_items_set_project_id / trg_budget_item_daily_allocations_set_project_id / trg_budget_allocation_transaction_items_set_project_id) BEFORE INSERT OR UPDATE OF the parent FK and project_id on each budget-tree table — each overwrites NEW.project_id with the parent row's project_id (budgets → headers → items → daily allocations → allocation-transaction items). On UPDATE, if the DERIVED value would differ from OLD.project_id (i.e. the row is being re-parented under a parent in another project) the trigger raises <table>.project_id is immutable: re-parenting across projects is not allowed — the triggers refresh only the directly updated row, so a cross-project move would strand that row's own descendants on a stale project_id and leak them across projects. Same-project re-parenting stays allowed. Firing on UPDATE of the parent FK / project_id (not just INSERT) closes the spoof where a caller supplies or updates a project_id that differs from the parent's, which the SELECT policies key on. All are SECURITY DEFINER, search_path='', EXECUTE revoked from PUBLIC/anon/authenticated (fire implicitly)
Budgets project_id immutability (trg_budgets_prevent_project_id_change, function budgets_prevent_project_id_change) BEFORE UPDATE OF project_id on budgets — raises budgets.project_id is immutable if the value changes. budgets is the root of the derive chain above; moving it would orphan the children's already-derived project_id (the child triggers do not re-fire on a parent move). SECURITY INVOKER, search_path='', EXECUTE revoked (fires implicitly)
Atomic supersede for project_currency_rates — two triggers make setting a new active rate atomic and enforce it for every writer:
trg_close_active_project_currency_rate (close_active_project_currency_rate, BEFORE INSERT): when a new active row (valid_to IS NULL) is inserted, closes the prior active row (valid_to = now()) so the incoming row satisfies the WHERE valid_to IS NULL partial unique index. Also brackets its close UPDATE with the transaction-local GUC app.superseding_currency_rate (set to '1' immediately before the UPDATE, back to '0' immediately after) so the removal-guard trigger recognises this close as a supersede and allows it.
trg_link_superseded_project_currency_rate (link_superseded_project_currency_rate, AFTER INSERT): stamps superseded_by = NEW.id on the most-recently-closed unsuperseded predecessor, completing the version chain (covers both the row just closed and a previously-removed row being re-added).
Exchange-rate removal invariant (trg_guard_project_currency_rate_removal, function guard_project_currency_rate_removal) BEFORE UPDATE on project_currency_rates — blocks a bare removal (an active row being closed: valid_to NULL → NOT NULL) when any non-terminal transaction still uses the rate's (project_id, from_currency_code). Terminal = Cancelled / Rejected; every other status (On Hold included) and a NULL status count as blocking. A supersede-close is exempted via the app.superseding_currency_rate GUC set by close_active_project_currency_rate — so you can always UPDATE (supersede) a rate, but cannot strand live transactions by removing it. SECURITY DEFINER, search_path='', EXECUTE revoked from PUBLIC/anon/authenticated (fires implicitly). Backstops the service-layer closeProjectCurrencyRateGated check against a raw UPDATE (TOCTOU-safe). Raises: Cannot remove an exchange rate while non-terminal transactions use its currency; update (supersede) it instead. The same trigger also blocks a re-key — an active row whose (project_id, from_currency_code) changed while it stays active — checked against the original (OLD) currency, since that silently removes the rate from it (a supersede never re-keys, so the GUC does not exempt this).
Exchange-rate delete guard (trg_guard_project_currency_rate_delete, function guard_project_currency_rate_delete) BEFORE DELETE on project_currency_rates — blocks hard-deleting an active rate whose (project_id, from_currency_code) still has non-terminal transactions, closing the last way (a raw DELETE) to strand a live transaction. SECURITY DEFINER, search_path='', EXECUTE revoked. Raises: Cannot delete an active exchange rate while non-terminal transactions use its currency; update (supersede) it instead.
project_currency_has_blocking_transactions(uuid, text) — the shared STABLE SECURITY DEFINER boolean helper (search_path='', EXECUTE revoked) used by all three removal-guard paths (close / re-key / delete) so their "blocking" definition — non-terminal status, NULL included — is identical. The app-layer projectCurrencyHasBlockingTransactions predicate mirrors it exactly.

Permission-Resolution Helpers¶

These resolve the permission-resolution contract (distinct from — and looser than — the sensitive-data clearance contract below): via v_user_permissions, status Active OR Invited, not expired, relationship (when present) active. They are the access-scoping primitives for RLS policies on project-scoped tables.

caller_accessible_project_ids(p_permission_keys text[], p_check_org_cascade boolean) → uuid[]

CONTRACT (from the DB COMMENT): permission-resolution, caller identity, set-based. Returns the array of project ids on which auth.uid() holds any of p_permission_keys (empty array = any grant), with p_check_org_cascade extending organisation-scope grants to their projects. Depends only on auth.uid() and constant args, so its evaluation can be made independent of row count — but in RLS SELECT policies that bound is guaranteed only by the bounded form project_id = ANY (SELECT unnest(caller_accessible_project_ids(...))) (InitPlan / hashed subplan in every plan shape; the function is PARALLEL SAFE, so the precise bound is once per executor process, not globally once per statement). A bare = ANY (fn(...)) re-executes per row in scan/filter plans, and = ANY ((SELECT fn(...))) is invalid (subquery-form ANY resolves to uuid = uuid[] and fails after parsing). NOT a sensitivity contract — do not substitute for get_user_clearance.

caller_has_permission(p_permission_keys text[], p_project_id uuid, p_check_org_cascade boolean) → boolean

CONTRACT (from the DB COMMENT): permission-resolution, caller identity, single-project probe. Same Active+Invited / expiry / relationship semantics as caller_accessible_project_ids, but parameterised by p_project_id — when called with a row column it is PER-ROW and re-runs the v_user_permissions walk for every candidate row. In RLS SELECT policies over many rows prefer the set-based caller_accessible_project_ids in the unnest-InitPlan form; keep this for single-record checks and single-record WITH CHECK clauses (one row inserted/updated at a time). For BULK / multi-row writes — a set-returning INSERT ... SELECT, or a WITH CHECK evaluated against many candidate rows (e.g. the budget-import INSERTs) — it re-runs the permission walk per row; use the set-based caller_accessible_project_ids(...) in the col = ANY (SELECT unnest(...)) form there instead. Inlineable SQL implementation (the plpgsql version was opaque to the planner).

caller_member_project_ids() → uuid[] (DEV-781)

CONTRACT (from the DB COMMENT): project membership, caller identity, set-based — a THIRD contract, distinct from both permission resolution and clearance. Returns the project ids where auth.uid() holds ANY user_accesses row with scope_type='project', status='Active' (never Invited), not expired — no relationship-status, grant-validity or revoked_at filters. It is the exact set-based mirror of the former project_relationships SELECT-policy EXISTS, and its admitted set deliberately diverges from caller_accessible_project_ids (which admits Invited and filters on relationship status) and from get_user_clearance (which additionally requires revoked_at IS NULL and active roles). DEFINER (avoids tunnelling through user_accesses RLS; returns only the caller's own membership). In RLS SELECT policies use ONLY the bounded project_id = ANY (SELECT unnest(caller_member_project_ids())) form. Consumed by the project_relationships SELECT policy; reusable by other membership-EXISTS policies.

Composite index backing all three: idx_user_accesses_user_scope ON user_accesses(user_id, scope_type, scope_id).

Sensitive Data Helpers¶

The sensitive-data layer is built on one generic visibility predicate that consults a single typed-rule table. Adding a new sensitive-eligible table = one new RLS policy with that table's ancestor list — no new helpers, no per-record-type visibility chain to extend. See ADDING_SENSITIVE_DATA.md for a step-by-step runbook and SENSITIVE_DATA_SYSTEM.md for the end-to-end architecture (database + service + hooks + UI + approval routing).

Sensitive-gated tables (current set)¶

Source tables — sensitive_rules.record_type values you can target with mark_record_sensitive:

entities — organisation-scoped
project_relationships — project-scoped
budget_headers — project-scoped (recursive parent walk handled in scope resolution)
budget_items — project-scoped
transactions — project-scoped
payments — project-scoped

Each source table has a corresponding masking view (v_entities, v_project_relationships, v_transactions, v_payments, v_budget_headers, v_budget_items) exposing sensitive_required_permissions, sensitive_field_required_permissions, and has_sensitive_rule — registered in TABLE_TO_VIEW_MAPPING so callers polymorphic over record_type can translate without a hidden lookup.

v_entities, v_project_relationships, and v_transactions additionally expose sensitive_auto_applied. On the entity / project-relationship views it reflects the row's OWN rule's auto_applied flag (plus entity_sensitive_auto_applied on v_project_relationships for the joined entity); on v_transactions it is TRUE when any of the transaction's project_relationship / entity ancestor rules is auto-applied. See SENSITIVE_DATA_SYSTEM.md § Auto-applied marks.

Field-restrictable columns (seeded in sensitive_fields):

entities.email, entities.phone_number, entities.payment_details
project_relationships.payment_details

Masking is enforced in DEFINER chokepoint helpers, not inline in the views, and the raw columns are unreadable. v_entities and v_project_relationships source their masked value columns from the bulk twins private.entity_masked_fields_all() / private.project_relationship_masked_fields_all() (see "Field-masking chokepoint helpers" below), which apply the clearance check AND the subject self-exemption. 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 are the ONLY authenticated read path — the mask cannot be bypassed by selecting the base table. Fail-closed: a future maskable column must be OMITTED from the base-table SELECT grant list and routed through a helper + view. entities.search_query no longer embeds any maskable value.

Derived columns inherit the source column's mask. Every view column computed FROM a maskable column resolves through a mask-aware source — the entity_bank_* columns on v_project_relationships/v_payments, v_entities.bank_address, v_project_relationships.entity_bank_address, and the address pass-throughs on v_payments/v_payment_details all read the helper's masked payment_details (guarded per-field by raw_present for the PR overrides) or an already-masked upstream view column, NEVER the raw JSONB. Reading the raw JSONB in a downstream view recreates the bypass fixed in v_payment_details.bank_address (pinned by tests/08_payment_address_masking.sql).

Storage¶

public.sensitive_rules — typed rule catalogue. One row per sensitive record. id uuid PRIMARY KEY DEFAULT gen_random_uuid() (surrogate) plus a UNIQUE (record_type, record_id) constraint backing the polymorphic lookup. Stores required_permissions text[] (row-level mask: caller's clearance must contain ALL keys — the predicate is required_permissions <@ clearance — to see the row), field_required_permissions jsonb (per-column {column → permission_key} map driving the masking views' CASE blocks), and scope_filter jsonb (per-child cascade narrowing: {"<child_table>": {"enabled": bool, "types"?: [...]}} — a cascade-only rule (empty required_permissions + non-empty scope_filter) hides matching child rows from callers without sensitive_data:<scope>:view while leaving the parent visible). organisation_id / project_id are denormalised at INSERT time so the visibility predicate can resolve scope without joining back to the source. Audit columns created_by_user_id / created_at / updated_by_user_id / updated_at maintained by the standard update_updated_at_column trigger.
public.sensitive_fields — catalogue of which columns are eligible for field masking. Drives the Mark Sensitive dialog and validates writes to sensitive_rules.field_required_permissions via the validate_sensitive_rule_fields BEFORE INSERT/UPDATE trigger. Today seeded with 4 rows (entity email/phone/payment_details + project_relationship payment_details); add a row + a CASE block in the masking view to introduce a new maskable column.

Visibility predicate (single helper, DEFINER, STABLE)¶

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) → boolean

CONTRACT (from the DB COMMENT): sensitive-data row visibility, caller identity by default (4th arg switches to explicit-user). PER-ROW predicate — parameterised by record id, so RLS policies pay one sensitive_rules scan per candidate row (cheap on miss, a hit adds a clearance walk). Clearance resolves through the Active-only get_user_clearance(_for_user) contract, never the Active+Invited permission-resolution contract.

Single-scan candidate lookup. Each call expands p_ancestors once into a MATERIALIZED targets CTE (NULL ids filtered out there), then does ONE scan of sensitive_rules pre-filtered by record_id = ANY (<target ids> || ARRAY[p_own_id]) and joined to targets on the exact (record_type, record_id) pair. The earlier shape joined sensitive_rules against a LATERAL jsonb_to_recordset(p_ancestors), which re-expanded the ancestor jsonb once per rule row and resolved as up to seven sensitive_rules_record_unique probes per call (1 own + 6 ancestors for a transaction) — a fixed cost multiplied by the row count of every list and count query across 18 RLS policies and 4 approval views. sensitive_rules is a single-page table (tens of rows), so one filtered scan is strictly cheaper than the probe fan-out; the array membership test is only a pre-filter, never the matching rule — the exact pair predicate is retained, so an ancestor id can never match a rule of a different record_type that happens to share the uuid.

Invariants the shape must preserve (all pinned by persona assertions in tests/07_access_primitive_semantics.sql):

is_own_row is derived from the RULE side (sr.record_type = p_own_type AND sr.record_id = p_own_id), never from which target matched. Rule (record_type, record_id) uniqueness does not stop a CALLER repeating the own pair inside p_ancestors; under a target-derived flag the own rule would also surface as an is_own=false ancestor candidate, and an empty-required_permissions rule with an enabled scope_filter would gain the cascade fallback key and hide a row that is visible today. With the rule-side derivation such a target yields a candidate whose is_own_row is still true and whose is_ancestor_match is false (its leading NOT is_own_row conjunct).
One candidate row per (rule, matching target) pair — is_ancestor_match is evaluated per pair and the blocking NOT EXISTS is satisfied by ANY blocking pair, so a rule reachable through several ancestor slots must not be aggregated down to one row.
NULL-id ancestors contribute nothing, and a '[]' or SQL NULL p_ancestors leaves only the own-row candidate.

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 AND the caller is not the subject of the record (see self-exemption below). Inputs:

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. Transactions pass 6 ancestors (4 PR FKs + 2 entity FKs); budget tables pass '[]' because the recursive header walk lives in the rule's own scope resolution.
p_user_id — selects between auth.uid() (default, RLS callers) and an explicit user_id (service_role callers — notification gates, digest cron). Internally chooses get_user_clearance vs get_user_clearance_for_user accordingly.
p_own_subtype — the row's own cascade subtype (e.g. transactions.type::text), matched against a rule's scope_filter types narrowing when the row is a cascade child.

Subject self-exemption. The blocking WHERE ends with a final conjunct that keeps the row visible when the row (or an ancestor) resolves to an entities row whose user_id equals the evaluated user (COALESCE(p_user_id, auth.uid())). Four probes cover every shape: own entity row, own relationship row (via pr.entity_id), an entity ancestor, a relationship ancestor. Business/loan-out entities (user_id IS NULL) never match. The probes reference only 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. This makes entities.user_id a security boundary: it must only be writable via trusted server-side paths (it is excluded from the authenticated INSERT/UPDATE column grants).

MUST be DEFINER: called from RLS policies on tables that are themselves RLS-gated (including entities / project_relationships, which the self-exemption probes read). INVOKER would tunnel through sensitive_rules's own SELECT policy creating recursion, or hide rules the caller doesn't have membership for and falsely return TRUE. The owner must equal those tables' owner and the tables must not have FORCE ROW LEVEL SECURITY, or the probes fail closed.

Empty required_permissions (field-mask-only rules) DON'T hide the row — they only mask columns via the masking helpers. This lets a producer say "show the row, hide the bank details" without removing the row from list views.

private.record_sensitivity_ancestors(p_record_type text, p_record_id uuid) → TABLE(ancestors jsonb, own_subtype text) (DEFINER, private schema)

Single source of truth for a record's sensitivity-ancestor list + cascade subtype, resolved from the record id. Branches: 'transactions' → 4 PR + 2 entity FKs, subtype = type; 'payments' → 1 PR + 1 entity FK, no subtype; 'project_relationships' → 1 entity FK (its entity_id), no subtype; other types → '[]' + NULL. Consumed by both record_is_visible_to_user and get_record_effective_required_permissions (previously each inlined its own ancestor arrays). Mirrors the inline ancestor arrays the per-table RLS policies pass to row_is_visible_to_caller — the two MUST change together (add a record_type's policy ancestor list → add the matching branch here in the same migration). The project_relationships branch was added so the explicit-user visibility path and the effective-permission computation honour the entity-cascade for relationships exactly like the RLS policies do. Lives in private (not PostgREST-exposed); resolves FK graphs for arbitrary ids so it is callable only via the two DEFINER functions below — REVOKE EXECUTE FROM PUBLIC, anon, authenticated.

get_record_effective_required_permissions(p_record_type text, p_record_id uuid) → text[] (DEFINER)

Caller-independent UNION of the effective required-permission keys gating a record — the same own-row + ancestor-cascade matching as row_is_visible_to_caller, but returning the key set instead of a per-caller boolean (no clearance check). Resolves the record's ancestors + subtype via private.record_sensitivity_ancestors (see above). Effective key per matched rule: non-empty required_permissions → those keys; empty + ancestor cascade (project rule) → sensitive_data:project:view; empty + ancestor cascade (org rule) → sensitive_data:organisation:view; empty + own-row (field-mask only) → nothing.

Deliberately NOT self-exempted (unlike row_is_visible_to_caller): this is caller-independent metadata (badges, approval routing), so the subject of a rule still sees the badge and its required permissions — only the row/field values are self-exempted, never the fact that the record is marked.

Exists because the masking views' historical unnest(required_permissions) form for sensitive_required_permissions dropped cascade-only rules (a parent marked sensitive for its children via scope_filter, empty required_permissions on the parent), so a transaction/payment hidden by such a rule reported '{}' and bypassed approval-router sensitivity gating. v_transactions and v_payments now compute that column via this helper. MUST be DEFINER for the same reason as row_is_visible_to_caller (reads RLS-gated sensitive_rules). Returns only permission-key strings (no gated row data), so GRANT EXECUTE TO authenticated, service_role; REVOKE FROM public, anon.

private.caller_restricted_project_relationship_ids() → uuid[] (DEFINER, DEV-781)

CONTRACT (from the DB COMMENT): sensitive-data restriction set, caller identity, own-row rules only — the set-based sensitivity primitive Story 3 (DEV-779) deferred. Returns the ids of project_relationships whose OWN sensitive_rules row has non-empty required_permissions NOT contained (<@, ALL keys required) in the caller's Active-only clearance for the rule's scope (project when rule.project_id is set, else organisation), excluding relationships whose entity is linked to the caller (entities.user_id = auth.uid()) — the exact set-based mirror of row_is_visible_to_caller's subject self-exemption for p_own_type = 'project_relationships', keeping its documented "exact mirror of the chokepoint's project_relationships semantics" contract true. The self-exclusion is a NOT EXISTS, so NULL entity_id / user_id rows stay blocked. Field-mask-only rules (empty required_permissions) never restrict; ancestor-cascade rules are OUTSIDE this contract (per-row row_is_visible_to_caller with ancestors remains the primitive for those). Bounded by RULE count, never candidate-row count; clearance computed once per distinct rule scope via MATERIALIZED CTEs; empty for unauthenticated callers.

Lives in the private schema — NOT in PostgREST's exposed schemas (supabase/config.toml → schemas), because its raw output enumerates cross-tenant restricted-record ids: policy evaluation only needs schema USAGE + function EXECUTE for the querying role, while an API-exposed schema would make it an authenticated /rpc/ endpoint. Never move it to public or widen its grants. Consumed by the user_accesses SELECT policy in the bounded NOT (project_relationship_id = ANY (SELECT unnest(...))) form.

Restriction-set twins for the list/count SELECT policies¶

private.caller_restricted_transaction_ids() → uuid[], private.caller_restricted_entity_ids() → uuid[], private.caller_restricted_relationship_ids_cascading() → uuid[], private.caller_restricted_budget_item_ids() → uuid[], private.caller_restricted_budget_header_ids() → uuid[] (all DEFINER, private, zero-arg, STABLE PARALLEL UNSAFE SET search_path='')

CONTRACT: each returns the ids the CALLER may not see on ONE table, and is the exact set-complement of row_is_visible_to_caller for that table's policy argument shape. Consumed only as the bounded negated conjunct NOT (id = ANY (SELECT unnest(...))) in the table's SELECT policy, replacing a per-row DEFINER call with a blocked set built once per statement (an uncorrelated InitPlan / hashed subplan). Measured locally: bare transactions count 6890 → 348 buffers; v_project_relationship_types badge count 1298 → 235; budget_item_daily_allocations count at 1095 rows 49.1ms → 0.87ms.

caller_restricted_entity_ids() — the entities policy passes '[]' ancestors and no subtype, so own-row rules only, and only those with cardinality(required_permissions) > 0 (empty-permission own rules are field-mask only and never hide). Self-exemption is the entity's own user_id. A near-verbatim mirror of caller_restricted_project_relationship_ids.
caller_restricted_transaction_ids() — the full blocked(t) semantics: own-row transactions rules with explicit keys, PLUS entities / project_relationships rules reached through the SIX ancestor FK columns, matching via the explicit-keys cascade arm (perms > 0 AND scope_filter = '{}') OR the scope_filter arm (enabled true, types absent/empty/containing the transaction's type::text). It must NOT copy the reference primitive's cardinality(required_permissions) > 0 prefilter for ancestor rules — cascade-only rules (empty permissions + enabled scope_filter) block through the sensitive_data:{project,organisation}:view fallback key and exist in production; filtering them out fails OPEN. Self-exemption is ROW-GLOBAL: any of the six slots resolving to the caller's own entity exempts the whole row, whichever rule blocked it.
caller_restricted_relationship_ids_cascading() — own-row relationship rules (perms > 0) PLUS entities-ancestor rules cascading through pr.entity_id. Distinct from caller_restricted_project_relationship_ids, which is own-row-only and serves user_accesses — that primitive is byte-pinned and unchanged; pick by CONTRACT, never by name similarity. The policy passes no subtype, so a scope_filter whose types array is present and non-empty never matches here.
caller_restricted_budget_item_ids() — the budget_items policy passes '[]' ancestors and no subtype, so own-row rules only (perms > 0; empty-permission own rules are field-mask only and never hide). Also consumed by the budget_item_daily_allocations SELECT policy, which gates on its PARENT item in the same argument shape — NOT (budget_item_id = ANY (SELECT unnest(...))) — so an allocation disappears exactly when its parent item does; there is no allocation-specific set.
caller_restricted_budget_header_ids() — the identical reduction with record_type = 'budget_headers', serving the budget_headers SELECT policy.

These two are derived from sensitive_rules + get_user_clearance ALONE — they do NOT join the budget tables, unlike the three twins above. All four self-exemption arms of row_is_visible_to_caller are unreachable for p_own_type IN ('budget_items','budget_headers'): the two own-type arms fail the literal type equality (they test 'entities' / 'project_relationships'), and the two ancestor arms iterate targets, which is empty under '[]' ancestors. Since self-exemption is the only part of the function that must inspect a ROW rather than a RULE, no live-table join is needed. Aggregating sensitive_rules.record_id directly also makes membership independent of row existence, which is the fail-closed property the other twins lack: SELECT policies are evaluated against PROPOSED rows for INSERT … RETURNING / ON CONFLICT, and a set built by scanning the table could not contain a proposed row's id, so a pre-existing rule on that uuid would not block it. Orphan rule ids (rule present, row deleted) are harmless — they are extra members of a set used only in a NOT-ANY membership test — so the parity oracles compare per existing row, never whole-set equality.

All five evaluate get_user_clearance once per DISTINCT (scope_type, scope_id) through a WITH … AS MATERIALIZED CTE (the barrier is load-bearing — a flattenable derived table re-runs the clearance walk per rule/row pair), and canonicalize output as COALESCE(array_agg(DISTINCT <record id> ORDER BY <same>), ARRAY[]::uuid[]) — the BLOCKED RECORD id, never the rule id, deduplicated across multiple matching slots and stably ordered.

NULL-uid contract: each returns {} (nothing blocked) under a NULL-uid context, where row_is_visible_to_caller fails closed. This is safe ONLY behind an explicit non-NULL-uid guard, which conjunct 1 of every consuming policy supplies (all are TO authenticated); service_role bypasses RLS and deliberately holds no EXECUTE. Verified by actual policy enforcement, not a recomposed predicate: under SET LOCAL ROLE authenticated with the claims GUC NULL, all the consuming tables return zero rows.

Blast radius (bounded by a constraint). As DEFINER set-builders the two cascading twins evaluate, once per statement, every rule JOINED to a live row they can reach — across tenants, not only the tenant whose rows the query touches. A malformed scope_filter would therefore fail every caller's statement on that table, where the per-row form failed only queries touching a row that reached the rule: one tenant's bad row would be a cross-tenant outage.

That widening is closed at the source rather than absorbed per read. sensitive_rules_scope_filter_shape (a validated CHECK delegating to private.is_valid_scope_filter_shape(jsonb)) makes the malformed value unrepresentable: scope_filter must be an object whose every value is an object, with an optional ARRAY types and an optional BOOLEAN enabled. Unknown sibling keys are permitted so a future per-child-type knob needs no constraint change. Since the shape the twins depend on is now guaranteed at rest, no twin — and no future scope_filter consumer — has to defend against it, and the cross-tenant failure mode cannot be reached. The constraint binds every role (a CHECK is not RLS), so the privileged rule-writing paths are covered too; it is pinned structurally in tests/01_tables_and_pks.sql and behaviourally in tests/07_access_primitive_semantics.sql.

Rules with no live row on the other side of the join are never evaluated, and caller_restricted_entity_ids and the two budget twins never evaluate scope_filter at all (no ancestor arm). The budget twins join no live table, so they have no reachability precondition: every key-bearing rule of their record_type is evaluated once per statement.

Lives in the private schema for the same reason as the reference primitive — raw output enumerates cross-tenant restricted-record ids; catastrophic as an RPC. REVOKE FROM PUBLIC, anon; GRANT EXECUTE TO authenticated ONLY. Byte-identical body pins, catalog pins (STABLE / PARALLEL UNSAFE / DEFINER / search_path), 4-way ACL sentinels (incl. service_role-absent) and DEFINER trust-chain sentinels (owner alignment for every relation each twin reads + no FORCE RLS on transactions) in tests/03_functions.sql; per-persona semantics and policy-level enforcement in tests/07_access_primitive_semantics.sql; full-predicate policy equality sentinels in tests/06_rls_policies.sql. Parity oracle: localScripts/perf-mask-bulk/oracle_m2.sql.

private.auto_sensitive_project_ids() → uuid[] (DEFINER, private schema)

CONTRACT: Auto-Sensitive project set, caller-independent, set-based. Returns the ids of every project whose per-type transaction settings enable "created as sensitive" for at least one of the six canonical transaction-type keys (projects.metadata->'transaction'-><key>->>'entities_created_as_sensitive' = 'true', over Expense / Invoice_inbound / Invoice_outbound / Payroll Invoice / Reimbursement / Unknown). A missing or malformed key counts as false (matches the app rule that a missing key means "not sensitive"; ->> coerces a JSON boolean true and a JSON string "true" to text 'true', and yields NULL for a nested object/array). It reads only constant project metadata (no auth.uid()), so it is PARALLEL SAFE; STABLE; SET search_path=''.

It exists for the pre-pipeline transactions visibility window: between ingest and type discovery a transaction's eventual sensitivity rules do not exist yet, so the transactions SELECT policy uses this set (in the bounded project_id = ANY (SELECT unnest(...)) form) to identify Auto-Sensitive projects and, for window-status rows, demand BOTH sensitive_data:organisation:view (via caller_accessible_project_ids(..., true) — cascade because the org permission is organisation-scoped) AND sensitive_data:project:view (via caller_accessible_project_ids(..., false)). See SENSITIVE_DATA_SYSTEM.md § "Pre-pipeline window gate". Lives in the private schema (off the Data API); REVOKE FROM PUBLIC, anon + GRANT EXECUTE TO authenticated, service_role. ACL / volatility / body sentinels in tests/16_pre_pipeline_sensitive_window.sql.

record_is_visible_to_user(p_record_type text, p_record_id uuid, p_user_id uuid) → boolean (DEFINER)

CONTRACT (from the DB COMMENT): sensitive-data record visibility, EXPLICIT-user identity; PER-RECORD. Resolves the ancestor chain for transactions/payments and delegates to row_is_visible_to_caller(..., p_user_id) under the Active-only clearance contract. Used by approval routing: an approver is eligible for a sensitive record iff they can see it.

Whether a specific USER can see the (possibly sensitive) record — resolves the record's ancestors + subtype via the shared private.record_sensitivity_ancestors resolver (the same list the RLS policies pass) and delegates to row_is_visible_to_caller(..., p_user_id, own_subtype), so each rule is evaluated at ITS OWN scope (an organisation-level rule against the user's organisation-scope clearance, a project-level rule against their project-scope clearance) and the subject self-exemption applies exactly as it does at row level (a user is "cleared" for a record about themselves). Exists for approval routing: approver eligibility for a sensitive record is "can this user see it". The previous app-side check flattened every rule's keys into one union and compared it against project-scope clearance only, wrongly excluding users cleared via an organisation-scope grant (e.g. organisation:owner). GRANT EXECUTE TO authenticated, service_role; REVOKE FROM public, anon.

private.count_pending_processing_issues(p_processable_type text, p_processable_id uuid) → integer (DEFINER, private schema)

Count of pending + current processing_issues for one record. Exists purely for performance: reading processing_issues under the caller's RLS runs its per-row SELECT policy, which re-derives the caller's whole permission set (user_accesses → project_relationships via row_is_visible_to_caller → permission_role_links → permissions) plus a nested EXISTS on processing_jobs calling caller_has_permission — production EXPLAIN ANALYZE showed ~4060 loops / ~3.35 s to annotate a single approval row. DEFINER skips that RLS. UNCHECKED — it counts for any id it is handed, so it lives in the private schema (not a PostgREST /rpc endpoint) and its only consumer, v_user_pending_approvals, gates on row_is_visible_to_caller(record_type, record_id) before invoking it via a LATERAL. Originally a public function granted to authenticated; moved to private because that made it directly callable with arbitrary UUIDs, leaking issue counts for invisible records. LANGUAGE sql STABLE. GRANT EXECUTE TO authenticated, service_role (the invoker view evaluates it as the querying role); REVOKE FROM PUBLIC, anon.

count_pending_issues_by_resolvability(p_processable_type text, p_processable_ids uuid[]) → TABLE(processable_id uuid, submitter_resolvable int, non_submitter_resolvable int) (INVOKER guard → private.count_pending_issues_by_resolvability_unchecked, DEFINER)

Per-record pending + current issue counts split by submitter_resolvable. Two-layer shape: the public function is a SECURITY INVOKER guard that filters the requested ids through the caller's transactions RLS (project membership + sensitivity — invisible ids simply return no row, unknown processable types fail closed) and delegates the counting to private.count_pending_issues_by_resolvability_unchecked, the SECURITY DEFINER twin of private.count_pending_processing_issues (same rationale: skips the per-issue-row processing_issues RLS that made the transactions list reads ~5-6 s in production). The guard exists because a public DEFINER counter granted to authenticated was directly callable via /rpc with arbitrary UUIDs. Consumed server-side by getPendingProcessingIssuesCountBatch and getPendingIssuesByResolvabilityBulk (which pre-seed {0,0} for every requested id, so invisible ids degrade to zero counts). Behavioural persona coverage: tests/07_access_primitive_semantics.sql. LANGUAGE sql STABLE. Both layers: GRANT EXECUTE TO authenticated, service_role; REVOKE FROM PUBLIC, anon. service_role also holds USAGE on schema private so the guard can delegate under a service-role caller (RLS-bypassing, sees all ids).

caller_can_access_address(p_address_id uuid) → boolean (INVOKER)

Scopes the addresses SELECT policy. Returns TRUE when the caller can see a row that references the address: entities.address_id, transactions.billing_address_id / customer_address_id, or the payment_details.bank.address_id JSONB pointer on project_relationships / entities. (addresses has no UPDATE policy — it is immutable for the authenticated role; see SYSTEM.md § "Addresses".)

The two payment_details bank arms are field-mask-aware AND subject-self-aware: they LEFT JOIN sensitive_rules and apply the same payment_details mask predicate as the masking views (project-scope clearance for the relationship override, organisation-scope for the entity default), in the views' fail-open CASE shape (rule row invisible to the caller → treated as unmasked) so table-level and view-level answers never diverge. Each arm additionally carries an OR self-disjunct — when the rule would mask the bank details, the arm still resolves for the subject (the relationship arm via pr.entity_id → entities.user_id, the entity arm via e2.user_id, both = auth.uid()), so the subject can still resolve their own bank address. Without the mask a row-visible but uncleared caller could read — or bulk-sweep — masked bank-address rows straight off public.addresses even though every view NULLs them (this function is the table's only SELECT gate). The postal-address and transactions arms stay mask-free (those columns are not maskable). If a future referencing column is derived from a maskable field, its EXISTS arm must carry the mask too. Behavioural pins: tests/08_payment_address_masking.sql (direct-table persona assertions); sentinel: tests/03_functions.sql.

The function also declares COST 1000 plus two supporting expression indexes (idx_entities_bank_address_id, idx_project_relationships_bank_address_id): as the per-row addresses SELECT policy it must plan as a PK probe, not a policy-filtered whole-table scan — at the default cost the planner chose Join-Filter scans that re-ran the policy rows×loops times and timed out the payment request sheet export.

MUST be INVOKER (unlike row_is_visible_to_caller): each inner EXISTS must run under the caller's own RLS so a referencing row counts only if the caller can already see it — that is what makes "access rides the parent record" work without replicating each table's scoping. There is no recursion because none of entities / transactions / project_relationships reference addresses in their own policies. This is the single chokepoint for addresses visibility — when a new table references addresses, add one more EXISTS clause here. GRANT EXECUTE TO authenticated, service_role; REVOKE FROM public, anon.

Because the scoped SELECT can't see a brand-new (not-yet-referenced) address, address creation runs under service-role in the server layer (address/server.findOrCreateAddress; relationship bank-address resolution in project/server.updateProjectRelationship).

Field-masking chokepoint helpers (DEFINER, STABLE, private schema)¶

Field masking is enforced in these DEFINER helpers (two per-PK originals and their two bulk twins), NOT inline in the masking views. The maskable value columns are column-grant-revoked from authenticated on the base tables, so the helpers (read via the views) are the only authenticated read path — the mask cannot be bypassed by selecting the base table. All are SECURITY DEFINER (they read the revoked value columns), STABLE, live in private (not PostgREST-exposed, so not RPC endpoints), and do NOT enforce row-level security — the invoker view must already hold the row via its own RLS. REVOKE EXECUTE FROM PUBLIC, anon; GRANT EXECUTE TO authenticated, service_role (the invoker view evaluates them as the querying role). COST 200.

private.entity_masked_fields(p_entity_id uuid) → TABLE(email text, phone_number text, payment_details jsonb, redacted jsonb)

Returns entities.email / phone_number / payment_details masked to NULL when the entity's rule requires a permission the caller lacks at organisation-scope clearance AND the caller is not the subject (entities.user_id = auth.uid()), plus redacted — the per-caller map of fields actually masked for THIS caller (present-valued only, excluding fields the subject self-exemption keeps visible). Formerly LEFT JOIN LATERALed by v_entities; the view now sources those columns (email / phone_number / payment_details / sensitive_field_redacted, and bank_address keyed off the masked payment_details) from the bulk twin below — this per-PK form is retained as the behavioural parity oracle and for genuine single-row callers.

private.project_relationship_masked_fields(p_relationship_id uuid) → TABLE(payment_details jsonb, raw_present jsonb, redacted jsonb)

Returns project_relationships.payment_details masked to NULL at project-scope clearance with the same subject self-arm (resolved via pr.entity_id → entities.user_id), plus redacted (per-caller map) and raw_present — a per-field boolean map (booleans only, no values) of whether the relationship HAS each raw bank field. v_project_relationships uses raw_present to preserve the exact entity_bank_* derived-column fallback semantics: a relationship that HAS a raw bank field but has it masked reads NULL and does NOT fall through to the entity value; a relationship that lacks the field falls through to the (already-masked) entity value from v_entities.

Both per-PK helpers are declared ROWS 1 (they always return exactly one row). This is load-bearing for the planner: at the default ROWS 1000 estimate, views joining them produced multi-million-row join estimates and materialized the ENTIRE masked view (helper executed for every row in reach) instead of probing by PK — the primary shape behind the production payments-list timeout. Pinned by prorows sentinels in tests/03_functions.sql; any new single-row helper joined LATERAL in a view must carry ROWS 1 too.

Bulk twins (once-per-statement, zero-argument): private.entity_masked_fields_all() → TABLE(entity_id uuid, email text, phone_number text, payment_details jsonb, redacted jsonb) and private.project_relationship_masked_fields_all() → TABLE(project_relationship_id uuid, payment_details jsonb, raw_present jsonb, redacted jsonb). Even at ROWS 1, a per-PK LATERAL helper is a SECURITY DEFINER RETURNS TABLE SQL function — Postgres NEVER inlines those, so a view join re-executes the whole clearance/rule machinery once per reachable row. On the payments list the planner materialises the entire visible-entity set (RLS row=1 estimates), so the mask helpers dominated runtime (~69% staging / ~62% prod exec). The bulk twins compute the SAME 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 — the MATERIALIZED barrier is load-bearing (a flattenable derived table would re-run the clearance walk per joined row). Fail-closed with COALESCE(<scope>.clearance, '{}'::text[]) on a missed scope join. v_entities / v_project_relationships join the twin directly (LEFT JOIN private.<fn>() x ON x.<id> = <base>.id), collapsing the per-row helper executions to a bounded FunctionScan-node count per statement (≤3 across the v_payments expansion: 2× entity + 1× relationship). The FunctionScan is unparameterized (zero-arg call) — a nested-loop rescan only REWINDS the tuplestore, it does not re-execute the body (so plans show loops>1 without re-running the mask; do NOT assert loops=1). Per-row output of a twin for any id under any claims context is byte-identical to the per-PK helper called with that id — a pure set-based generalisation with identical per-row claims semantics; the per-PK helper is the parity oracle (localScripts/perf-mask-bulk/oracle.sql); the per-PK helpers have NO production view/RPC call sites and are retained as parity oracles / for genuine single-row callers. Trust boundary: the twins are enumerable by a hypothetical SQL-level authenticated caller (a documented widening vs the per-PK "guess a UUID" surface), accepted within the SAME private-schema trust model as caller_restricted_project_relationship_ids — private is not in PostgREST's exposed schemas and EXECUTE is revoked from PUBLIC/anon. ROWS 1000 tracks current cardinality (cost grows with TOTAL table size, not caller scope — revisit if a tenant's entities grow into the tens of thousands). REVOKE FROM PUBLIC, anon; GRANT EXECUTE TO authenticated, service_role. ACL + shape + DEFINER trust-chain sentinels in tests/03_functions.sql: owner-alignment for EVERY relation each twin reads (sensitive_rules, entities, organisations; relationship twin also project_relationships, projects), no FORCE RLS on entities/organisations/project_relationships/projects (+sensitive_rules pinned elsewhere), view→twin integration pins (viewdef LIKE _all() / NOT LIKE the per-PK call), exact result-signature pins, and WITH … AS MATERIALIZED CTE-syntax pins.

private.format_address(p_address_id uuid) → text (DEFINER, STABLE, STRICT, COST 10)

UNCHECKED single-address formatter: returns the one-line comma-joined rendering of a public.addresses row, reading AS OWNER — it deliberately bypasses the addresses RLS (caller_can_access_address, ~15ms/evaluation) because its approved call sites pass only ids whose visibility is ALREADY established:

a non-NULL address_id of an RLS-visible entities row (v_payments.entity_address) — a visible entity referencing the address is precisely caller_can_access_address's first arm, so the check is tautologically TRUE on this path;
a bank address_id read from the MASKED payment_details returned by the field-masking helpers above (v_entities.bank_address) — the mask only yields the pointer to callers the address function's clearance arm admits (or the subject).

Any NEW call site needs a security review against those provenance rules — never pass an attacker-controllable or ungated id. STRICT (never executes for NULL). Lives in private (not API-exposed); REVOKE FROM PUBLIC, anon; GRANT EXECUTE TO authenticated, service_role. ACL + DEFINER trust-chain sentinels (owner matches addresses owner, addresses has no FORCE RLS) in tests/03_functions.sql.

Mark-RPCs (INVOKER)¶

mark_record_sensitive(record_type text, record_id uuid, required_permissions text[] DEFAULT NULL, field_required_permissions jsonb DEFAULT '{}', scope_filter jsonb DEFAULT '{}') → sensitive_rules — UPSERT RPC. Resolves organisation_id / project_id from the source record, then INSERTs / UPDATEs the rule. Raises a CHECK violation when required_permissions, field_required_permissions, AND scope_filter are all empty (nonsensical rule — at least one of the three must be set). RLS on sensitive_rules enforces caller permission inline via get_user_clearance(scope, scope_id) — see "Clearance helpers" below.
unmark_record_sensitive(record_type text, record_id uuid) → boolean — single-row DELETE. Returns TRUE if a rule existed.

Clearance helpers (DEFINER, STABLE)¶

CONTRACT (from the DB COMMENT): sensitive-data clearance — semantics are STRICTER than permission resolution: status Active ONLY (never Invited), not expired, revoked_at IS NULL, role is_active. Do NOT substitute the Active+Invited permission-resolution contract for this Active-only contract, or vice versa. STABLE is an optimiser promise, not a per-statement cache — callers needing row-count-independent evaluation must use an uncorrelated subquery / InitPlan form. DEFINER to avoid tunnelling through user_accesses RLS during policy evaluation.

get_user_clearance(p_scope_type scope_type, p_scope_id uuid) → text[] — Caller identity (auth.uid()). Flat set of permission keys the calling user holds in the given scope. UNION of direct grant_type='permission' grants and role-derived grant_type='role' grants, filtered per the contract above. Returns '{}' (empty array, never NULL).
get_user_clearance_for_user(p_user_id uuid, p_scope_type scope_type, p_scope_id uuid) → text[] — EXPLICIT-user twin used by service_role callers where auth.uid() is NULL (approval routing, digest cron). Identical Active-only/expiry/revoked_at/role-active semantics. Reads user_accesses directly to avoid v_user_*_permissions recursion.

Role bindings for the four sensitive_data permission keys (:organisation:view, :organisation:mark, :project:view, :project:mark) plus the two field-category keys (:view_pii, :view_payment_details) are applied inline by the Phase 1 migration via INSERT INTO permission_role_links … ON CONFLICT DO NOTHING covering Organisation Owner / Project Owner / Project Admin. Future migrations that adjust the implies chain repeat the same inline INSERT pattern — there is no helper function to invoke.

Realtime-Enabled Tables¶

The following tables have Supabase Realtime enabled for live updates:

notifications - Real-time delivery of in-app notifications
notification_deliveries - Track notification delivery status changes
messages - Live message delivery and status updates
processing_jobs - Track processing job status changes (includes progress_percentage for live progress bars)
processing_issues - Real-time issue creation and resolution updates

Previously published tables (projects, project_relationships, user_accesses, attachments, transactions, transaction_items, taggings, payments, payment_reconciliations, approval_requests, approval_instances) were removed from the publication in migration 20260410033627 to reduce WAL listener overhead. These tables now use refetch-after-mutation and refetch-on-focus patterns instead.

Database Indexes¶

Important: Every foreign key constraint should have a corresponding index for optimal query performance.

Database Provider Swap Guide¶

This section documents every place in the codebase that is tied to the current database provider stack (Supabase / PostgREST / GoTrue / Supabase Storage / Supabase Realtime). Use it as a checklist when evaluating or executing a provider swap.

1. Adapter layer — rewrite these files only¶

The service layer (base.ts files) is fully provider-agnostic. All provider-specific code is concentrated in the files below. A swap only requires rewriting these files; everything else in the codebase imports through them.

File	What it encapsulates	Swap action
`packages/app/src/lib/databaseHelper.ts`	PostgREST query building — `applyConditions`, `orFilter`, `andGroup`, `geoWithin`, all fragment builders	Rewrite `applyConditions` and the PostgREST fragment builders. The semantic helpers (`conditions`, `orFilter`, `andGroup`, `notNull`, `arrayOverlaps`, etc.) and their signatures stay unchanged.
`packages/app/src/lib/authHelper.ts`	GoTrue / Supabase Auth — `signInWithOtp`, `verifyOtp`, `signOut`, `getUser`, `getSession`, `refreshSession`, `updateUser`, `setSession`, `onAuthStateChange`, `admin.createUser`, `admin.updateUserById`, `admin.deleteUser`	Full rewrite. Replace with the new auth provider's SDK.
`packages/app/src/lib/storageHelper.ts`	Supabase Storage SDK — `storage.from(bucket).upload/download/move/copy/remove/createSignedUrl/getPublicUrl`	Full rewrite. Replace with S3, GCS, or equivalent SDK. The "bucket" concept maps to S3 bucket / GCS bucket.
`packages/app/src/lib/realtimeHelper.ts`	Supabase Realtime — `database.channel()`, `channel.on('postgres_changes', …)`, `database.removeChannel()`. Also exports `eqFilter` which encodes the PostgREST filter string format.	Full rewrite. Replace with the new realtime engine. Update `eqFilter` (and any future filter helpers) to emit the new engine's filter syntax.
`packages/app/src/lib/database/` (entire folder)	Client construction (`createServerClient`, `createBrowserClient` via `@supabase/ssr`), middleware cookie wiring, `createServiceRoleClient` (RLS-bypass client), server-side auth utilities (`withAuthenticatedUser`, `getAuthUser`).	Rewrite client factories and cookie wiring for the new provider. The "service role" concept (RLS bypass) must be replicated — typically a privileged connection pool or separate credentials.
`packages/app/src/types/database.ts`	Re-exports `SupabaseClient` as `DatabaseClient`, and `Session`/`User`/`AuthChangeEvent`/`AuthError`/`UserMetadata` from `@supabase/supabase-js` as `AuthSession`/`AuthUser`/`AuthChangeEvent`/`AuthError`/`AuthUserMetadata`.	Point re-exports at the new provider's types. All downstream code imports via `@/types/database` and stays unchanged.

2. `AuthChangeEvent` string values¶

AuthStateListener.tsx and PostLoginActionsListener.tsx switch on AuthChangeEvent string values ('SIGNED_IN', 'SIGNED_OUT', 'TOKEN_REFRESHED', 'PASSWORD_RECOVERY', 'USER_UPDATED'). These are GoTrue / Supabase Auth event names re-exported through types/database.ts.

On an auth provider swap:

Update the AuthChangeEvent re-export in types/database.ts to the new provider's event type.
Verify every switch/case branch in the files below maps correctly to the new event vocabulary:
packages/app/src/listeners/AuthStateListener/AuthStateListener.tsx
packages/app/src/listeners/PostLoginActionsListener/PostLoginActionsListener.tsx
packages/app/src/hooks/queries/useAuthQuery.ts

3. Service-role / RLS-bypass pattern¶

Many route handlers and server-side services call createServiceRoleClient() to obtain a database client that bypasses Row Level Security for privileged operations:

packages/app/src/app/api/ai-processing/callback/route.ts
packages/app/src/app/api/ai-processing/start-job/route.ts
packages/app/src/app/api/sana/process/route.ts
packages/app/src/app/api/integrations/sync/scheduled/route.ts
packages/app/src/app/api/email/inbound/webhook/route.ts
packages/app/src/app/api/email/inbound/process/route.ts
packages/app/src/app/api/cron/digest-notifications/route.ts
packages/app/src/services/auth/server.ts
packages/app/src/services/transaction/actions.ts
packages/app/src/services/processing/context.ts
packages/app/src/services/entity/server.ts
packages/app/src/services/conversation/server.ts
packages/app/src/sana/webhook/index.ts

All call sites import the factory from lib/database/server/serviceClient.ts (adapter layer). Only the factory needs rewriting on a swap; call sites are unchanged.

4. Realtime filter strings¶

TableSubscription.filter in packages/app/src/types/realtime.ts accepts a provider-specific filter string. Use eqFilter from packages/app/src/lib/realtimeHelper.ts to build these strings — do not write raw filter syntax at call sites.

Current call sites using eqFilter:

packages/app/src/listeners/RealtimeListener/RealtimeListener.tsx — notifications filtered by recipient_user_id

On a realtime provider swap, update eqFilter (and add other filter helpers as needed) in realtimeHelper.ts. Call sites stay unchanged.

5. CSP host whitelist¶

packages/app/src/lib/securityHeaders.ts hardcodes *.supabase.co and wss://*.supabase.co in the Content Security Policy connect-src and img-src directives (storage, realtime, and auth endpoints all live on this domain).

On a swap, update the CSP host list to match the new provider's domain(s). Consider extracting to an environment variable so a config change is sufficient.

6. Logger error-shape assumption¶

packages/app/src/lib/logger/client/index.ts (lines ~130–139) duck-types PostgREST error objects by checking for code, details, and hint fields — the PostgreSQL/PostgREST error contract. The check is guarded ('code' in error) so it is safe at runtime, but the field names are provider-specific. Revisit this branch after a swap.

7. Storage — fully abstracted¶

No storage calls exist outside lib/storageHelper.ts. The storage layer is completely encapsulated; no other files require changes.

8. Test and mock files¶

These files will fail to compile after a swap (no runtime impact today):

File	What to update
`packages/app/__mocks__/@supabase/supabase-js.ts`	Replace mock module with equivalent for new provider
`packages/app/__mocks__/@supabase/ssr.ts`	Replace mock module with equivalent for new provider
`packages/app/src/lib/__mocks__/authHelper.ts`	Update re-exported types to match new `@/types/database` exports
`packages/app/src/services/auth/__mocks__/authClient.ts`	Same
`packages/app/src/hooks/useNotifications.test.ts`	`jest.mock('@supabase/supabase-js', …)` — update to mock new provider
`packages/app/src/lib/databaseHelper.test.ts`	`@supabase/postgrest-js` type imports — update to new query layer

Views, Functions & Triggers¶

Database Views¶

User Permissions View (v_user_permissions)¶

User Organisation Permissions View (v_user_organisation_permissions)¶

User Project Permissions View (v_user_project_permissions)¶

User Accessible Organisations View (v_user_accessible_organisations)¶

Organisation Members View (v_organisation_members)¶

User Accessible Projects View (v_user_accessible_projects)¶

User Accessible AI Prompt Functions View (v_user_accessible_ai_prompt_functions)¶

People View (v_people)¶

Businesses View (v_businesses)¶

User Accessible Menu Items View (v_user_accessible_menu_items)¶

User Accessible AI Functions View (v_user_accessible_ai_functions)¶

User Abuse Actions View (v_user_abuse_actions)¶

Processing Ready Steps View (v_processing_ready_steps)¶

Pending Processing Issues View (v_pending_processing_issues)¶

Save Project Transaction Settings (save_project_transaction_settings)¶

Project Categories View (v_project_categories)¶

Category Hierarchy View (v_category_hierarchy)¶

Available Project Types View (v_available_project_types)¶

Approval Types View (v_approval_types)¶

User Approval History View (v_user_approval_history)¶

User Approval Queries View (v_user_approval_queries)¶

User Pending Approvals View (v_user_pending_approvals)¶

Available Approvers View (v_available_approvers)¶

Conversations with Details View (v_conversations_with_details)¶

Compliance Capture View (v_compliance_capture)¶

Payments View (v_payments)¶

Payment Details View (v_payment_details)¶

Payment Messages View (v_payment_messages)¶

Payment Method Movements View (v_payment_method_movements)¶

Transactions View (v_transactions)¶

Schedulable Transactions View (v_schedulable_transactions)¶

Project Relationships View (v_project_relationships)¶

Project Relationship Types View (v_project_relationship_types)¶

Payment Method Assignments View (v_payment_method_assignments)¶

Budget Item Daily Allocations View (v_budget_item_daily_allocations)¶

Budget Items View (v_budget_items)¶

Budget Headers View (v_budget_headers)¶

Budgets View (v_budgets)¶

Budget Allocation Transaction Details View (v_budget_allocation_transaction_details)¶

Budget Item Transaction Details View (v_budget_item_transaction_details)¶

Project Integrations View (v_project_integrations)¶

Integration Sync Logs View (v_integration_sync_logs)¶

Shareable Integration Credentials View (v_shareable_integration_credentials)¶

Project Integration Send To Targets View (v_project_integration_send_to_targets)¶

Transaction Audit Trail View (v_transaction_audit_trail)¶

Database Functions & Triggers¶

Auth Sync Functions¶

Project Management Functions¶

Feature Flag Functions¶

Utility Functions¶

Full-Text Search Functions¶

Currency Conversion Functions¶

Messaging System Functions¶

Message Threading Functions¶

Message Outbox Functions¶

Production Phase Functions¶

Payment System Functions¶

Import Functions¶

Transaction Line-Item Functions¶

Entity Deduplication Functions¶

Payment Scheduling Functions¶

Invite System Functions¶

Approval Message Functions¶

Processing Job Lifecycle & Stall Recovery Functions¶

Fenced Processor Domain-Write Functions¶

Processing Status Summary Function¶

Integration System Functions¶

Budget Functions¶

Auth Security Functions¶

Vault Wrapper Functions¶

Key Triggers¶

Permission-Resolution Helpers¶

Sensitive Data Helpers¶

Sensitive-gated tables (current set)¶

Storage¶

Visibility predicate (single helper, DEFINER, STABLE)¶

Restriction-set twins for the list/count SELECT policies¶

Field-masking chokepoint helpers (DEFINER, STABLE, private schema)¶

User Permissions View (`v_user_permissions`)¶

User Organisation Permissions View (`v_user_organisation_permissions`)¶

User Project Permissions View (`v_user_project_permissions`)¶

User Accessible Organisations View (`v_user_accessible_organisations`)¶

Organisation Members View (`v_organisation_members`)¶

User Accessible Projects View (`v_user_accessible_projects`)¶

User Accessible AI Prompt Functions View (`v_user_accessible_ai_prompt_functions`)¶

People View (`v_people`)¶

Businesses View (`v_businesses`)¶

User Accessible Menu Items View (`v_user_accessible_menu_items`)¶

User Accessible AI Functions View (`v_user_accessible_ai_functions`)¶

User Abuse Actions View (`v_user_abuse_actions`)¶

Processing Ready Steps View (`v_processing_ready_steps`)¶

Pending Processing Issues View (`v_pending_processing_issues`)¶

Save Project Transaction Settings (`save_project_transaction_settings`)¶

Project Categories View (`v_project_categories`)¶

Category Hierarchy View (`v_category_hierarchy`)¶

Available Project Types View (`v_available_project_types`)¶

Approval Types View (`v_approval_types`)¶

User Approval History View (`v_user_approval_history`)¶

User Approval Queries View (`v_user_approval_queries`)¶

User Pending Approvals View (`v_user_pending_approvals`)¶

Available Approvers View (`v_available_approvers`)¶

Conversations with Details View (`v_conversations_with_details`)¶

Compliance Capture View (`v_compliance_capture`)¶

Payments View (`v_payments`)¶

Payment Details View (`v_payment_details`)¶

Payment Messages View (`v_payment_messages`)¶

Payment Method Movements View (`v_payment_method_movements`)¶

Transactions View (`v_transactions`)¶

Schedulable Transactions View (`v_schedulable_transactions`)¶

Project Relationships View (`v_project_relationships`)¶

Project Relationship Types View (`v_project_relationship_types`)¶

Payment Method Assignments View (`v_payment_method_assignments`)¶

Budget Item Daily Allocations View (`v_budget_item_daily_allocations`)¶

Budget Items View (`v_budget_items`)¶

Budget Headers View (`v_budget_headers`)¶

Budgets View (`v_budgets`)¶

Budget Allocation Transaction Details View (`v_budget_allocation_transaction_details`)¶

Budget Item Transaction Details View (`v_budget_item_transaction_details`)¶

Project Integrations View (`v_project_integrations`)¶

Integration Sync Logs View (`v_integration_sync_logs`)¶

Shareable Integration Credentials View (`v_shareable_integration_credentials`)¶

Project Integration Send To Targets View (`v_project_integration_send_to_targets`)¶

Transaction Audit Trail View (`v_transaction_audit_trail`)¶

2. `AuthChangeEvent` string values¶