Database Schema Documentation¶

This document provides a comprehensive overview of the database schema for the FarmCove Delta application - a film production management system with robust user management, permissions, and workflow features.

Table of Contents¶

Enumerated Types
Core Tables
Project Management Tables
Permission System Tables
Navigation System Tables
Internationalization Tables
Category System Tables
Attachment System Tables
Financial Management Tables
Tax Configuration Tables
Address Management Tables
Budget Management Tables
Schedule Management Tables
Notification System Tables
Messaging System Tables
Abuse Prevention Tables
AI Integration Tables
Processing System Tables
Tagging System Tables
Export System Tables
Approval System Tables
Integration System Tables
Database Views
Database Functions & Triggers
Realtime-Enabled Tables
Database Indexes

Enumerated Types¶

The database uses PostgreSQL enum types for better type safety and data consistency. All enums were converted from CHECK constraints in migration 20250912113410_convert_check_constraints_to_enums.sql.

Entity Types¶

entity_type: Person, Business

User & Settings¶

theme_type: light, dark, system

Projects & Production¶

project_relationship_status: Invited, Active, Inactive, Declined
budget_status: Draft, Active, Superseded, Archived
schedule_status: Draft, Proposed, Approved, Active, Superseded, Archived
production_phase_type: Pre-Production, Shooting, Post-Production
day_type: Working, Travel, Rest

Financial & Documents¶

document_type: Tax Invoice, Receipt, Proforma Invoice, Quote, Purchase Order, Other
transaction_type_enum: Expense, Invoice, Payroll Invoice, Reimbursement, Unknown
transaction_direction: inbound, outbound - Indicates if a transaction represents money coming in (received) or going out (sent)
transaction_status: Received, Processing, In Approval, Approved, Rejected, Queried, Cancelled, Paid
payment_status: Scheduled, Paid, Cancelled
bank_account_type: personal, business - Type of entity bank account
payment_method_type: Card, Bank Account, Petty Cash Float
card_type: Debit, Credit
payroll_classification: Salary/Fee, Overtime, Equipment, Expenses, Combination, `` (empty string)
accounting_breakdown: Above the Line, Below the Line

Communication¶

channel_type: whatsapp, email, in_app
conversation_status: active, archived, blocked, current
instance_status: active, closed, summarized
close_reason: message_limit, time_limit, abuse_detected, manual
sentiment_type: positive, neutral, negative, mixed
message_direction: inbound, outbound, internal
message_sender_type: user, assistant, system, functions, tool, tool_calls
delivery_status: pending, sent, delivered, read, failed
notification_sender_type: email, phone

System & Security¶

data_source: Email, WhatsApp, Manual, CSV, System
abuse_action_type: warning, throttle, suspend, block, report
abuse_pattern_type: spam, flooding, gibberish, profanity, phishing, api_abuse
rate_limit_type: per_minute, per_hour, per_day, per_instance
rate_limit_user_type: all, new, verified, premium

AI Processing¶

processable_type: transactions, budgets, schedules
failure_action: fail_job, skip_dependents, continue
job_status: pending, in_progress, completed, failed, partial_success
step_status: pending, ready, in_progress, completed, failed, skipped, not_needed
processing_issue_status: pending, requested, resolved, ignored
processing_issue_severity: critical, high, medium, low

Tagging¶

tag_category: Compliance, Transaction

Export System¶

export_status: pending, processing, completed, failed
export_type: transactions, compliance

Access Control¶

access_status: Active, Invited, Revoked
grant_type: role, permission
scope_type: organization, project - Used for user access scope and permission/role scope

menu_type: main, sidebar, dropdown, quickaction, sana

Integrations¶

integration_auth_method: oauth2, api_key, basic_auth, saml, jwt, certificate, webhook, none
integration_credential_scope: global, per_project
integration_connection_status: Connected, Disconnected, Error, Expired, Pending
sync_trigger_type: on_status_change, on_manual, on_schedule, on_link, realtime
sync_status: pending, in_progress, success, partial_success, failed
sync_direction: inbound, outbound, bidirectional
integration_feature_category: tax_validation, company_validation, company_details - Categorizes integration features by purpose. company_details features store results in project_integrations.external_account_name + external_metadata instead of integration_reference_data

Attachments¶

entity_attachment_type: identity_document, certificate, photo, signature, bank_details, tax_document, insurance, license, other — note: contract was removed in migration 20260403000000_create_project_relationship_attachments.sql because contracts now belong to a specific project relationship, not the entity globally
project_relationship_attachment_type: identity_document, contract, starter_form, proof_of_work, certificate, other — relationship-scoped attachment categories

Core Tables¶

1. Entities Table (`entities`)¶

Purpose: Flexible table for storing both individuals and businesses as entities. Replaces the old people and vendors tables.

Use Case Example: When adding a director, actor, supplier, or any other participant to a project, create an entity record with the appropriate type.

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
type	ENUM	Entity type: 'Person' or 'Business'
organization_id	UUID	Organization this entity belongs to (required)
user_id	UUID	Reference to public.users if this entity is a system user (nullable)
first_name	TEXT	First name (required for Person type)
last_name	TEXT	Last name (required for Person type)
preferred_name	TEXT	Preferred display name for the entity (Person type)
business_name	TEXT	Business name (required for Business type)
registration_number	TEXT	Entity registration number (e.g., Company Registration Number or Schedule D Number for tax purposes)
trading_name	TEXT	Trading name for businesses that operate under a different name than their legal business name
is_accounting_entity	BOOLEAN	Flag indicating this entity was created from an accounting project (personal or company accounting, default: false)
establishment_date	DATE	Date when entity was established. For companies: incorporation date. For personal/self-employed: Self-Employment Start Date
date_of_birth	DATE	Date of birth (Person type only)
email	TEXT	Email address of the entity (nullable)
phone_number	TEXT	Phone number of the entity (nullable)
bank_account_number	TEXT	Entity bank account number for receiving payments. Applicable to both Person and Business types
bank_name	TEXT	Entity bank name (e.g., Barclays, HSBC, ANZ). Applicable to both Person and Business types
bank_sort_code	TEXT	Entity bank sort code / routing number / BSB for receiving payments. Applicable to both Person and Business types
bank_payee_name	TEXT	Entity bank payee / account holder name. Applicable to both Person and Business types
bank_account_type	ENUM	Type of entity bank account: personal or business. Applicable to both Person and Business entity types (nullable)
address_id	UUID	Reference to the entity's address (nullable)
tax_number	TEXT	Tax identification number (e.g., TIN, SSN, VAT number) - nullable
citizenship_country_code	TEXT	Country of citizenship - references country(code) (Person type only, nullable)
residency_country_code	TEXT	Country of residency - references country(code) (nullable)
metadata	JSONB	Additional metadata about the entity (see Metadata Structure below)
is_active	BOOLEAN	Whether the entity is active (default: true)
is_secondary_supplier	BOOLEAN	Indicates if this entity is a secondary supplier (Business type, default: false)
is_customer	BOOLEAN	Flag indicating if this entity is a customer who receives invoices from us (default: false)
search_query	TSVECTOR	Full-text search vector for name, email, phone, and bank fields (auto-generated)
created_at	TIMESTAMPTZ	Timestamp when the entity was created
updated_at	TIMESTAMPTZ	Timestamp when the entity was last updated
created_by_user_id	UUID	User who created this entity
updated_by_user_id	UUID	User who last updated this entity

Key Features:

Unified table for both individuals and businesses
Type-specific constraints ensure data integrity
Automatic sync with users table for system users
Metadata field tracks change history
Can exist without a corresponding user account
Used for cast/crew, suppliers, vendors, etc.
Linked to organizations for multi-tenancy support
Full-text search support with GIN index on search_query column

Entity Metadata JSONB Structure:

The metadata field can store the following structured data:

{
  "emergency_contact": {
    "full_name": "string",
    "phone": "string (E.164 format, e.g., +44123456789)",
    "email": "string (optional)",
    "relationship": "string (e.g., Spouse, Parent, Sibling, Friend)"
  },
  "identity_documents": {
    "passport": {
      "number": "string",
      "place_of_issue": "string",
      "date_of_issue": "YYYY-MM-DD",
      "expiry_date": "YYYY-MM-DD",
      "country_code": "string (ISO 3166-1 alpha-2)"
    },
    "drivers_licence": {
      "number": "string",
      "place_of_issue": "string",
      "date_of_issue": "YYYY-MM-DD",
      "expiry_date": "YYYY-MM-DD",
      "country_code": "string (ISO 3166-1 alpha-2)"
    },
    "other_ids": [
      {
        "type": "string (e.g., National ID, Social Security Card)",
        "number": "string",
        "place_of_issue": "string (optional)",
        "date_of_issue": "YYYY-MM-DD (optional)",
        "expiry_date": "YYYY-MM-DD (optional)",
        "country_code": "string (optional)"
      }
    ]
  }
}

2. Users Table (`users`)¶

Purpose: System users who can authenticate and access the application

Use Case Example: When a new crew member needs system access, a user account is created and automatically creates a corresponding entity record with auth credentials.

Column	Type	Description
id	UUID	Same as auth.users.id for consistency
first_name	TEXT	User's first name
last_name	TEXT	User's last name
preferred_name	TEXT	User's preferred display name
email	TEXT	Email address (synced from auth.users)
phone	TEXT	Phone number (synced from auth.users)
is_active	BOOLEAN	Whether the user account is active and can log in
is_invite_pending	BOOLEAN	True for invited users who haven't completed the Sana acceptance flow (default: false)
is_internal	BOOLEAN	Flag to identify internal/system users (default: false)
auth_deleted	BOOLEAN	Soft delete flag when auth user is deleted
auth_deleted_at	TIMESTAMPTZ	When the auth user was deleted
last_login_at	TIMESTAMPTZ	Last successful login timestamp
created_at	TIMESTAMPTZ	When the user account was created
updated_at	TIMESTAMPTZ	Last update timestamp
avatar_attachment_id	UUID	Reference to attachment for user's profile picture
bio	TEXT	User's biography/description

Key Features:

Automatically synced with auth.users via triggers
Soft deleted when auth user is deleted
Email/phone fields are read-only (synced from auth)
Name fields (first_name, last_name, preferred_name) stored directly on users table
Synced with corresponding entity record via triggers
Internal users (is_internal = true) don't have auth.users records

3. User Settings Table (`user_settings`)¶

Purpose: User preferences and settings

Use Case Example: Users can customize their experience by setting preferred language (es, en), theme (dark/light), timezone for accurate scheduling, and their country for regional settings.

Column	Type	Description
user_id	UUID	Reference to the users record (primary key)
language_code	TEXT	User's preferred language (default: 'en')
preferred_theme	TEXT	UI theme preference (light/dark/system)
timezone_id	TEXT	User's timezone for date/time display
country_code	TEXT	User's country code (ISO 3166-1 alpha-2)
notification_preferences	JSONB	User notification preferences - channel settings and per-template overrides (see below)
organization_id	UUID	Default organization for the user
updated_at	TIMESTAMPTZ	Timestamp when settings were last updated

Notification Preferences JSON Structure:

{
  "email_enabled": false,           // Global email notifications (default: false)
  "whatsapp_enabled": true,         // Global WhatsApp notifications (default: true)
  "in_app_enabled": true,           // Global in-app notifications (default: true)
  "digest_frequency": "immediate",  // Options: "immediate", "daily", "weekly"
  "quiet_hours_start": null,        // Start of quiet hours in "HH:MM" format or null
  "quiet_hours_end": null,          // End of quiet hours in "HH:MM" format or null
  "template_overrides": {           // Per-template overrides (optional)
    "<template_key>": {
      "email_enabled": boolean,
      "whatsapp_enabled": boolean,
      "in_app_enabled": boolean
    }
  }
}

Project Management Tables¶

4. Projects Table (`projects`)¶

Purpose: Core project/production management

Use Case Example: Create a main project "Summer Blockbuster 2024" with sub-projects for "VFX Unit" and "Second Unit", each tracking their own budgets and teams.

Column	Type	Description
id	UUID	Unique identifier
organization_id	UUID	Reference to organization that owns this project
entity_id	UUID	Reference to the entity that owns/operates this project (for accounting projects, this represents the business or individual) - References entities(id)
project_code	TEXT	Unique project code (auto-generated by trigger from title)
url_key	TEXT	Unique 6-character alphanumeric key used in URLs instead of UUID
parent_project_id	UUID	Reference to parent project (for sub-projects)
title	TEXT	Project title
created_by_user_id	UUID	User who created the project
updated_by_user_id	UUID	User who last updated
country_code	TEXT	ISO country code where the production company is headquartered (required, determines currency and tax scheme) - References countries(code)
currency_code	TEXT	Currency for financial transactions (auto-populated from country via trigger) - References currencies(code)
fixed_exchange_rates_json	JSONB	Fixed exchange rates if applicable
tax_scheme_id	UUID	Tax scheme for the project (auto-populated from country via trigger) - References tax_schemes(id)
poster_attachment_id	UUID	Reference to the project poster image
logo_attachment_id	UUID	Reference to the project logo image
start_date	DATE	Date when the project started or will start
is_active	BOOLEAN	Whether the project is active or archived
is_tax_registered	BOOLEAN	Whether the production company or individual is registered for tax (GST/VAT). Default is false (not registered)
metadata	JSONB	Additional project metadata including email addresses for inbound parsing (e.g., {"emails": {"generic": "project.title_urlkey@docs.farmcove.co.uk"}})
current_budget_id	UUID	The budget currently being tracked (baseline budget). Set via UI when user selects active budget
current_schedule_id	UUID	The schedule currently being tracked (baseline schedule). Initially set on project creation, can be updated via UI
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Indexes:

idx_projects_country_code on country_code for efficient country-based lookups
idx_projects_entity_id on entity_id for efficient entity-based lookups

5. Organizations Table (`organizations`)¶

Purpose: Represents organizations that own projects. Each user gets a "Personal" organization by default.

Use Case Example: When a user signs up, they automatically get a "Personal" organization. Companies can create separate organizations for team collaboration.

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Organization name
organization_code	TEXT	Unique code generated from organization name for internal use
url_key	TEXT	Unique 6-character alphanumeric key used in URLs instead of UUID
logo_attachment_id	UUID	Reference to attachment for organization logo
is_active	BOOLEAN	Whether the organization is active or archived
created_by_user_id	UUID	User who created the organization
updated_by_user_id	UUID	User who last updated the organization
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
is_default	BOOLEAN	Whether this is the default organization for new users

6. User Accesses Table (`user_accesses`)¶

Purpose: Unified permission system that grants roles or permissions to users at organization or project scope.

Use Case Example: Grant Jane the "Line Producer" role for "Summer Blockbuster 2024" project, or grant Bob "organization:edit" permission for the entire production company.

Column	Type	Description
id	UUID	Unique identifier
user_id	UUID	Reference to user being granted access
grant_type	TEXT	Type of grant: 'role' or 'permission'
grant_id	UUID	ID of permission_role or permission being granted
scope_type	TEXT	Scope of grant: 'organization' or 'project'
scope_id	UUID	ID of organization or project
project_relationship_id	UUID	Reference to project_relationship (for project scope)
status	TEXT	Status: 'Active', 'Invited', or 'Revoked'
granted_by_user_id	UUID	User who granted this access
expires_at	TIMESTAMPTZ	Optional expiration timestamp
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Unique constraint on (user_id, grant_type, grant_id, scope_type, scope_id, project_relationship_id)
Supports both role-based and direct permission grants
Can grant access at organization or project level
Links to project_relationship for project-level grants

7. Role Definitions Table (`role_definitions`)¶

Purpose: Defines standard production industry roles with department classification and role characteristics

Use Case Example: Create roles like "Director", "Producer", "Cinematographer" that can be assigned to entities on projects.

Column	Type	Description
id	UUID	Unique identifier
title	TEXT	Role title (e.g., Director)
description	TEXT	Detailed description of the role and its responsibilities
department_id	UUID	Reference to departments table (nullable)
is_hod	BOOLEAN	Whether this is a Head of Department role
is_chain_of_title	BOOLEAN	Whether this role affects chain of title
is_on_screen	BOOLEAN	Whether this is an on-screen role (e.g., Cast)
is_not_common	BOOLEAN	Whether this role is uncommon in standard productions
can_be_vendor	BOOLEAN	Whether this role can be assigned to Organization entities
can_be_person	BOOLEAN	Whether this role can be assigned to Person entities
is_active	BOOLEAN	Whether role is currently used
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Department classification links roles to production departments (Above/Below the Line)
Boolean flags identify special role characteristics (HOD, chain of title, on-screen, uncommon)
Project types array indicates which project types the role applies to
Supports both individual persons and vendor organizations
Includes 305 standard production roles and 64 business service roles

8. Project Relationships Table (`project_relationships`)¶

Purpose: Links entities to projects with specific production roles

Use Case Example:

Add Steven Spielberg as "Director" (Person entity)
Add a production company as "Producer" (Organization entity)
Add catering service as "Supplier" (Organization entity)

Column	Type	Description
id	UUID	Unique identifier
project_id	UUID	Reference to project
entity_id	UUID	Reference to entity (required)
role_definition_id	UUID	Standard role from role_definitions table
role_title_override	TEXT	Custom role title if different
status	TEXT	Invited/Active/Inactive/Declined/Cancelled
expense_reimbursement_code	TEXT	Code used by production to match submitted receipts/expenses to this entity (nullable)
metadata	JSONB	Relationship-specific data (contains identity_document object with document details captured at acceptance time). Default: '{}'::JSONB
invited_by_user_id	UUID	User who sent the invitation to join the project
invited_at	TIMESTAMPTZ	Timestamp when the invitation was sent
accepted_at	TIMESTAMPTZ	Timestamp when the invitation was accepted
cancelled_by_user_id	UUID	User who cancelled the invitation
cancelled_at	TIMESTAMPTZ	Timestamp when the invitation was cancelled
created_by_user_id	UUID	User who created the relationship
updated_by_user_id	UUID	User who last updated the relationship
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

entity_id references the entities table (can be Person or Organization type)
Supports all types of project participants through unified entity model
Role definitions determine applicable entity types
metadata.identity_document stores a snapshot of identity details at the time of acceptance (entity always has latest data, but each relationship preserves the state when accepted). The actual file linkage now lives in project_relationship_attachments (with attachment_type = 'identity_document'); the legacy identity_attachment_id column was dropped in migration 20260403000000 and existing rows were backfilled
cancelled_by_user_id and cancelled_at track who cancelled an invitation and when
Status flow: Invited → Active (accepted) | Declined (user declined) | Cancelled (inviter cancelled)

Permission System Tables¶

9. Permissions Table (`permissions`)¶

Purpose: Defines all system permissions

Use Case Example: Define permission "budget:edit" allowing users to edit budgets.

Column	Type	Description
id	UUID	Unique identifier
permission_key	TEXT	Unique key (e.g., 'project:view')
description	TEXT	Human-readable description
module	TEXT	Feature area (Projects, Budgets, Reports)
scope	scope_type	The scope where this permission applies: 'organization' or 'project' (default: 'project')

10. Permission Roles Table (`permission_roles`)¶

Purpose: Defines roles that bundle multiple permissions together

Use Case Example: Create "Organization Admin" role with permissions for managing users, projects, and organization settings.

Column	Type	Description
id	UUID	Unique identifier for the role
name	TEXT	Display name of the role
description	TEXT	Optional description of the role
role_key	TEXT	Unique key identifier for the role
priority	INTEGER	Priority order for displaying roles (lower numbers first)
is_active	BOOLEAN	Whether this role is currently active and can be assigned
scope	scope_type	The scope where this role applies: 'organization' or 'project' (default: 'project')
created_at	TIMESTAMPTZ	Timestamp when the role was created
updated_at	TIMESTAMPTZ	Timestamp when the role was last updated

11. Permission Role Links Table (`permission_role_links`)¶

Purpose: Links permissions to roles

Use Case Example: Grant "organization:view", "organization:edit", and "organization:users" permissions to the "Organization Admin" role.

Column	Type	Description
permission_role_id	UUID	Reference to permission role
permission_id	UUID	Reference to permission

12. Menu Items Table (`menu_items`)¶

Purpose: Dynamic navigation menu structure

Use Case Example: Create menu hierarchy: Setup > Budgets > Version History, where each level can have different permissions and icons.

Column	Type	Description
id	UUID	Unique identifier
parent_id	UUID	Parent menu item (for nesting)
menu_key	TEXT	Unique menu identifier (required)
name	TEXT	Display name
icon	TEXT	Icon name (lucide-react)
display_order	INTEGER	Display order
is_active	BOOLEAN	Whether item is shown
required_permission_key	TEXT	Simple permission check
required_feature_flag	TEXT	Project column name that must be true for item to appear (e.g., 'approvals_enabled'). NULL = always visible.
is_parent_active	BOOLEAN	Whether parent menu shows when selected
menu_type	TEXT	Menu type: main/sidebar/dropdown/quickaction/sana
notification_template_id	UUID	Notification template to trigger when menu item selected
description	TEXT	Optional description text for additional context
metadata	JSONB	Flexible field for additional properties (e.g., function_to_call)

Feature Flags: Menu items can be conditionally displayed based on project-level feature flags using the required_feature_flag column. When set, the menu item will only appear if the corresponding project column is true. For example, required_feature_flag = 'approvals_enabled' means the menu item only appears when projects.approvals_enabled = true. This is checked in the v_user_accessible_menu_items view.

Default Menu Structure:

Web Application Menus (type: App):

- Home (/projects/{projectId}) - menu_key: projects.home
- Setup - menu_key: projects.setup - icon: FolderCog
  - Approvals (/projects/{projectId}/setup/approvals) - menu_key: projects.setup.approvals - icon: SlidersHorizontal - requires: approval_configuration:view (display_order: 10)
  - Budgets (/projects/{projectId}/setup/budgets) - menu_key: projects.setup.budgets - icon: Calculator - requires: budget:view (display_order: 20)
  - Payment Methods (/projects/{projectId}/setup/payment-methods) - menu_key: projects.setup.payment-methods - icon: WalletCards - requires: payment:method:manage (display_order: 30)
  - Users (/projects/{projectId}/setup/users) - menu_key: projects.setup.users - requires: user:view (display_order: 40)
  - Production Phases (/projects/{projectId}/setup/production-phases) - menu_key: projects.setup.production-phases - icon: CalendarDays - requires: production_phase:manage (display_order: 15, media projects only)
  - Integrations - menu_key: projects.setup.integrations - icon: Plug (display_order: 25)
    - Configuration (/projects/{projectId}/setup/integrations/configuration) - menu_key: projects.setup.integrations.configuration - icon: Settings - requires: integration:manage (display_order: 10)
    - Logs (/projects/{projectId}/setup/integrations/logs) - menu_key: projects.setup.integrations.logs - icon: ScrollText - requires: integration:logs:view (display_order: 20)
- Settings (/projects/{projectId}/settings) - menu_key: projects.settings - icon: Settings - requires: project:edit
- Management - menu_key: projects.management - icon: Settings
  - Entities - menu_key: projects.management.entities - icon: Users - requires: project_relationship:view
    - All (/projects/{projectId}/management/entities/all) - menu_key: projects.management.entities.all - requires: project_relationship:view (all project types)
    - Crew (/projects/{projectId}/management/entities/crew) - menu_key: projects.management.entities.crew - requires: project_relationship:view (media projects only)
    - Cast (/projects/{projectId}/management/entities/cast) - menu_key: projects.management.entities.cast - requires: project_relationship:view (media projects only)
    - Suppliers (/projects/{projectId}/management/entities/suppliers) - menu_key: projects.management.entities.suppliers - requires: project_relationship:view (all project types)
    - Customers (/projects/{projectId}/management/entities/customers) - menu_key: projects.management.entities.customers - icon: Handshake - requires: project_relationship:view (all project types)
  - Transactions - menu_key: projects.management.transactions - icon: Receipt - requires: transaction:view
    - Expense (/projects/{projectId}/management/transactions/expense) - menu_key: projects.management.transactions.expense - requires: transaction:view
    - Invoice (/projects/{projectId}/management/transactions/invoice) - menu_key: projects.management.transactions.invoice - requires: transaction:view
    - Payroll Invoice (/projects/{projectId}/management/transactions/payroll-invoice) - menu_key: projects.management.transactions.payroll_invoice - requires: transaction:view
    - Reimbursement (/projects/{projectId}/management/transactions/reimbursement) - menu_key: projects.management.transactions.reimbursement - requires: transaction:view
    - Others (/projects/{projectId}/management/transactions/others) - menu_key: projects.management.transactions.others - requires: transaction:view
  - Approvals - menu_key: projects.management.approvals - icon: ClipboardCheck - requires: approval:view - feature_flag: approvals_enabled
    - Pending (/projects/{projectId}/management/approvals/pending) - menu_key: projects.management.approvals.pending - icon: CircleDashed - requires: approval:view - feature_flag: approvals_enabled
    - Queries (/projects/{projectId}/management/approvals/queries) - menu_key: projects.management.approvals.queries - icon: CircleHelp - requires: approval:view - feature_flag: approvals_enabled
    - History (/projects/{projectId}/management/approvals/history) - menu_key: projects.management.approvals.history - icon: History - requires: approval:view - feature_flag: approvals_enabled
  - Payments - menu_key: projects.management.payments - icon: HandCoins - requires: payment:view (media, personal_accounting, company_accounting project types)
    - Scheduling (/projects/{projectId}/management/payments/scheduling) - menu_key: projects.management.payments.scheduling - icon: CalendarClock - requires: payment:schedule (media, personal_accounting, company_accounting project types)
    - Process (/projects/{projectId}/management/payments/process) - menu_key: projects.management.payments.process - icon: CreditCard - requires: payment:process (media, personal_accounting, company_accounting project types)
    - Paid (/projects/{projectId}/management/payments/paid) - menu_key: projects.management.payments.paid - icon: CircleCheck - requires: payment:view (media, personal_accounting, company_accounting project types)
  - Budget - menu_key: projects.management.budget - icon: Banknote - requires: budget:view - feature_flag: has_current_budget
    - Actuals (/projects/{projectId}/management/budget/actuals) - menu_key: projects.management.budget.actuals - icon: Gauge - requires: budget:actuals:view - feature_flag: has_current_budget
    - Estimated Costs (/projects/{projectId}/management/budget/estimated-costs) - menu_key: projects.management.budget.estimated_costs - icon: FileSpreadsheet - requires: budget:estimated_costs:view - feature_flag: has_current_budget_with_estimated_costs
  - Compliance Capture (/projects/{projectId}/management/compliance-capture) - menu_key: projects.management.compliance_capture - icon: FileCheck - requires: compliance:capture:view
- Reports (/projects/{projectId}/reports) - menu_key: projects.reports - icon: BarChart3 - requires: reporting:view

Sana WhatsApp Bot Menus (type: Sana):

- Submit Transactions - menu_key: sana.submit_transactions - requires: transaction:create
- Onboard Entities - menu_key: sana.onboard_people - requires: project_relationship:create
- Logging - menu_key: sana.logging - requires: time:track
  - Start Logging for today - menu_key: sana.logging.start - function: StartLogging
  - Stop Logging for today - menu_key: sana.logging.stop - function: StopLogging
- Financial Reporting - menu_key: sana.financial_reporting - requires: budget:view
  - Supplier Invoicing - menu_key: sana.financial_reporting.supplier_invoicing
  - Budget Used - menu_key: sana.financial_reporting.budget_used
- Switch Project Engagement - menu_key: sana.switch_project_engagement - function: SwitchProjectEngagement
- Invoice Finder - menu_key: sana.invoice_finder - requires: payment:view - function: PaymentTransactionFinder

Internationalization Tables¶

13. Countries Table (`countries`)¶

Purpose: ISO country codes for internationalization with regional settings

Use Case Example: Store production locations and crew nationalities using standard ISO codes like "US" for United States, "GB" for United Kingdom, with default timezone, currency, and language settings.

Column	Type	Description
code	TEXT	ISO 3166-1 alpha-2 code (e.g., US, GB)
name	TEXT	Full country name
timezone	TEXT	Default timezone for the country (e.g., America/New_York)
currency_code	TEXT	Default currency code (FK to currencies table)
language_code	TEXT	Default language code (FK to languages table)
consumption_tax_name	TEXT	Common name or acronym for the country's main consumption tax (e.g., VAT, GST, IVA)

Key Features:

Foreign key relationships to currencies and languages tables
Helps auto-populate user preferences based on country selection
Provides regional defaults for new projects
Consumption tax names used for validation and user-friendly error messages

14. Currencies Table (`currencies`)¶

Purpose: ISO currency codes for financial transactions

Use Case Example: Track project budgets in multiple currencies - main budget in USD, European expenses in EUR, UK expenses in GBP.

Column	Type	Description
code	TEXT	ISO 4217 code (e.g., USD, EUR)
name	TEXT	Full currency name
symbol	TEXT	Currency symbol (e.g., $, €)

15. Languages Table (`languages`)¶

Purpose: ISO language codes for UI localization

Use Case Example: Users can set their UI language preference to "es" for Spanish or "en" for English interface.

Column	Type	Description
code	TEXT	ISO 639-1 code (e.g., en, es)
name	TEXT	Full language name

Category System Tables¶

The category system provides a flexible, hierarchical classification structure that replaces rigid type definitions. It supports infinite depth through self-referencing relationships and organizes categories into namespaced groups.

16. Category Groups Table (`category_groups`)¶

Purpose: High-level namespaces for different category types (e.g., project_type, task_priority)

Use Case Example: Create a "project_type" group containing Film, Television, etc., and a separate "task_priority" group with Low, Medium, High classifications.

Column	Type	Description
id	UUID	Primary key (auto-generated UUID)
code	TEXT UNIQUE	Unique identifier (e.g., 'project_type')
name	TEXT	Display name (e.g., 'Project Types')
description	TEXT	Human-readable description of the group's purpose and usage
created_at	TIMESTAMPTZ	When the group was created
updated_at	TIMESTAMPTZ	When the group was last updated

Key Features:

Provides namespacing to prevent category code conflicts
Allows different classification systems to coexist
Public read access via RLS policies

17. Categories Table (`categories`)¶

Purpose: Hierarchical categories with self-referencing parent-child relationships

Use Case Example:

Film (top-level) → Feature Film (child) → Action (grandchild)
Television (top-level) → Series (child) → Drama (grandchild)

Column	Type	Description
id	UUID	Primary key (auto-generated UUID)
group_id	UUID	FK to category_groups (namespace)
parent_id	UUID	Self-referencing FK (NULL for top-level)
code	TEXT	Unique within group+parent context
name	TEXT	Display name
description	TEXT	Human-readable description of what this category represents
is_active	BOOLEAN	Whether category is active (default: true)
is_default	BOOLEAN	Whether this is the default category in its group
display_order	INTEGER	Display order within parent or group
is_media	BOOLEAN	Whether this is a media project type (default: false)
is_accounting	BOOLEAN	Whether this is an accounting project type (default: false)
is_personal_accounting	BOOLEAN	Whether this is personal accounting type (default: false)
metadata	JSONB	Flexible storage for category-specific data
created_at	TIMESTAMPTZ	When the category was created
updated_at	TIMESTAMPTZ	When the category was last updated

Key Features:

Infinite Hierarchy: Add unlimited levels via parent_id
Project Type Flags: Direct boolean columns (is_media, is_accounting, is_personal_accounting) replace JSONB metadata for better performance and type safety
Flag Propagation: For media projects, is_media = true is automatically propagated to all child categories via recursive update
Metadata Storage: Store default_phases, can_have_parent, etc. in JSONB for flexible configuration
Unique Constraint: (group_id, parent_id, code) ensures uniqueness within context
Indexed Flags: Partial indexes on is_media, is_accounting, is_personal_accounting (WHERE true) for query performance
Backward Compatibility: Migrated data from project_type_defaults stored in metadata

Example Hierarchy:

Categories (group_id = 'project_type'):
  ├─ Film (parent_id = NULL)
  │   ├─ Feature Film (parent_id = Film.id)
  │   └─ Short Film (parent_id = Film.id)
  └─ Television (parent_id = NULL)
      ├─ Series (parent_id = Television.id)
      └─ Miniseries (parent_id = Television.id)

18. Project Categories Table (`project_categories`)¶

Purpose: Many-to-many junction table linking projects to categories

Use Case Example: A project can be tagged with multiple categories like "Film" + "Action" + "High Budget".

Column	Type	Description
id	UUID	Primary key
project_id	UUID	FK to projects table
category_id	UUID	FK to categories table
created_by_user_id	UUID	FK to users - who created this link
created_at	TIMESTAMPTZ	When the link was created

Key Features:

Primary Key: id (UUID)
Unique Constraint: (project_id, category_id) - prevents duplicate category assignments
Cascade Delete: Removes links when project or category is deleted
Default User: created_by_user_id defaults to auth.uid()
RLS Policies: Public read access for authenticated users (USING true) - project-level RLS controls access to actual project data

Helper Function: get_category_hierarchy(p_group_code TEXT)

Returns recursive hierarchy for a category group
Includes level and path for tree traversal
Used by service layer to build tree structures

19. Role Definition Categories Table (`role_definition_categories`)¶

Purpose: Many-to-many junction table linking role definitions to categories

Use Case Example: A "Director" role can be applicable to both "Film" and "Television" categories.

Column	Type	Description
id	UUID	Primary key
role_definition_id	UUID	FK to role_definitions table
category_id	UUID	FK to categories table
created_at	TIMESTAMPTZ	When the link was created

Key Features:

Primary Key: id (UUID)
Unique Constraint: (role_definition_id, category_id) - prevents duplicate category assignments
Cascade Delete: Removes links when role definition or category is deleted
RLS Policies: Authenticated read-only. Junction table managed by service_role
Indexed: Both foreign keys indexed for query performance

20. Menu Item Categories Table (`menu_item_categories`)¶

Purpose: Many-to-many junction table linking menu items to categories with category-specific display order

Use Case Example: A "Budget" menu item can appear in multiple project categories but with different ordering (e.g., position 3 in "Film" but position 5 in "Television").

Column	Type	Description
id	UUID	Primary key
menu_item_id	UUID	FK to menu_items table
category_id	UUID	FK to categories table
display_order	INTEGER	Display order for this menu item within this specific category (allows different positions)
created_at	TIMESTAMPTZ	When the link was created

Key Features:

Primary Key: id (UUID)
Unique Constraint: (menu_item_id, category_id) - prevents duplicate category assignments
Cascade Delete: Removes links when menu item or category is deleted
RLS Policies: Authenticated read-only. Junction table managed by service_role
Indexed: Foreign keys and (category_id, display_order) for sorting queries
Category-Specific Ordering: Same menu item can have different display orders in different categories

Attachment System Tables¶

21. Attachments Table (`attachments`)¶

Purpose: Manages file attachments for various entities or as shared resources

Use Case Example: Upload a project poster image that displays on the project dashboard, or attach a call sheet PDF to a specific shooting day.

Column	Type	Description
id	UUID	Unique identifier
file_name_original	TEXT	Original filename as uploaded
file_name_stored	TEXT	System-generated unique filename
storage_path_or_url	TEXT	Full path in storage or external URL
mime_type	TEXT	File MIME type (e.g., 'image/jpeg', 'application/pdf')
file_size_bytes	BIGINT	File size in bytes
file_hash	TEXT	SHA-256 hash of file content for duplicate detection
description	TEXT	Optional description of the file
uploaded_by_user_id	UUID	User who uploaded the file
parent_entity_type	TEXT	Type of parent entity (e.g., 'project', 'budget_item')
parent_entity_id	UUID	ID of the parent entity
external_id	TEXT	External ID from third-party services (e.g., WhatsApp media ID)
external_id_created_at	TIMESTAMPTZ	When the external ID was created (for expiration tracking)
project_id	UUID	Optional reference to the project (FK to projects)
organization_id	UUID	Optional reference to the organization (FK to organizations). NULL for user-level attachments like avatars
superseded_by_id	UUID	Reference to the attachment that supersedes this one (FK to attachments)
created_at	TIMESTAMPTZ	Upload timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Can be linked to any entity type via parent_entity_type/id
Tracks both internal storage and external URLs
Maintains upload history with user tracking
Supports project posters and document attachments
Supports attachment versioning via superseded_by_id self-reference

22. Entity Attachments Table (`entity_attachments`)¶

Purpose: Links attachments to entities with categorization and versioning support for identity documents, contracts, and other entity-related files.

Use Case Example: Upload a passport scan for a cast member entity, which can later be superseded by a newer passport when renewed.

Column	Type	Description
id	UUID	Unique identifier
entity_id	UUID	Reference to entity (FK to entities)
attachment_id	UUID	Reference to attachment record (FK to attachments)
attachment_type	entity_attachment_type	Type of attachment (identity_document, contract, certificate, etc.)
document_key	TEXT	Unique key for document type per entity (e.g., 'gb_passport', 'nz_driver_license')
description	TEXT	Optional description of the attachment
metadata	JSONB	Additional metadata (OCR extracted data, expiry dates, etc.)
is_superseded	BOOLEAN	Whether this attachment has been superseded by a newer version (default false)
created_at	TIMESTAMPTZ	Creation timestamp
created_by_user_id	UUID	User who created the link (FK to users)

Key Features:

Links attachments to entities with type categorization
document_key enables versioning of same document type (e.g., multiple passports from different countries)
Partial unique index ensures only one active (non-superseded) document per entity per document_key
is_superseded flag maintains historical records while tracking current valid documents
metadata field stores OCR-extracted data for identity documents
RLS policies use v_user_accessible_organizations for access control

23. Project Relationship Attachments Table (`project_relationship_attachments`)¶

Purpose: Links attachments to a specific project_relationship (a person-in-this-project), distinct from entity_attachments which are global to the entity. Used for documents that belong to the relationship rather than the person — contracts, starter forms, proof of work, certificates, and the per-relationship copy of an identity document used to verify the person for THIS project role.

Use Case Example: Upload a signed contract for a crew member when they accept their role on a specific film production. The same person might have a different contract on a different project — each relationship gets its own row.

Column	Type	Description
id	UUID	Unique identifier
project_relationship_id	UUID	Reference to the project_relationship this attachment belongs to (FK to project_relationships, ON DELETE CASCADE)
attachment_id	UUID	Reference to the underlying attachment record (FK to attachments)
attachment_type	project_relationship_attachment_type	Type/category of the attachment (identity_document, contract, starter_form, proof_of_work, certificate, other)
description	TEXT	Optional description or notes about the attachment
metadata	JSONB	Additional metadata about the attachment (e.g., OCR extraction results, parsed document details)
is_superseded	BOOLEAN	Whether this attachment has been replaced by a newer version (default false). TRUE means it is a historical record
superseded_by_id	UUID	When is_superseded is true, points to the newer project_relationship_attachments row that replaced this one (self-FK). Forms an explicit replace chain
created_at	TIMESTAMPTZ	Creation timestamp
created_by_user_id	UUID	User who created this record (FK to users, default auth.uid())

Key Features:

Distinct from entity_attachments — these are scoped to a specific person-in-this-project, not globally to the entity
attachment_type covers contracts, starter forms, proof of work, certificates, identity documents (the per-relationship copy), and a catch-all other
Partial unique index (project_relationship_id, attachment_type) WHERE is_superseded = false AND attachment_type <> 'other' enforces "one active doc per type per relationship", with other exempt as a multi-use bucket
superseded_by_id is a self-FK forming an explicit replace chain so previous versions can be walked back from the current row
The legacy project_relationships.identity_attachment_id column was dropped in migration 20260403000000 and existing references were backfilled into this table with attachment_type = 'identity_document'
The corresponding attachments.parent_entity_type is set to project_relationships and parent_entity_id to the project_relationship id, so attachment-history queries (useSupersededAttachmentsQuery) work the same way as for transactions and entities
RLS policies gate access by joining through project_relationships → projects to v_user_accessible_organizations for auth.uid()

Financial Management Tables¶

17. Departments Table (`departments`)¶

Purpose: Production departments organized by accounting breakdown (Above the Line vs Below the Line) for budgeting and cost tracking.

Use Case Example: Categorize production roles and costs into Above the Line (creative/executive) and Below the Line (technical/crew) departments for accurate budget planning and financial reporting.

Column	Type	Description
id	UUID	Unique identifier for the department
name	TEXT	Department name (e.g., Production, Camera, Sound)
accounting_breakdown	accounting_breakdown	Classification: Above the Line or Below the Line
description	TEXT	Detailed description of the department responsibilities
is_active	BOOLEAN	Whether this department is currently active (default: true)
created_at	TIMESTAMPTZ	Timestamp when the department was created
updated_at	TIMESTAMPTZ	Timestamp when the department was last updated
created_by_user_id	UUID	User who created this department record
updated_by_user_id	UUID	User who last updated this department record

Key Features:

Pre-seeded with 14 standard production departments
Above the Line departments: Production, Direction, Casting, Cast
Below the Line departments: Camera, Grip, Electric, Art, Costumes and Wardrobe, Hair and Makeup, Sound, Visual Effects, Transportation, Vendors
Unique constraint on department names
RLS Policies: Authenticated read-only. Pre-seeded reference data managed by service_role

Accounting Breakdown:

Above the Line: Creative, executive, and talent costs (directors, actors, producers)
Below the Line: Technical crew, equipment, and production services

18. Transactions Table (`transactions`)¶

Purpose: Financial transactions for projects including invoices, expenses, and receipts

Use Case Example: Record an invoice from a camera rental company for $5,000, or track expense receipts submitted by crew members.

Column	Type	Description
id	UUID	Unique identifier for the transaction
project_id	UUID	Reference to the project this transaction belongs to
entity_id	UUID	Reference to the supplier/vendor entity for inbound transactions, or project entity for outbound (nullable)
customer_entity_id	UUID	Reference to the customer entity for outbound transactions (nullable)
transaction_code	TEXT	Unique transaction code in format TRX-000001, auto-generated on creation
document_type	TEXT	Document type: Tax Invoice, Receipt, Proforma Invoice, Quote, Purchase Order, Other
type	TEXT	Transaction categorization: Expense, Invoice, Payroll Invoice, Reimbursement, Unknown
direction	TEXT	Transaction direction: 'inbound' (invoice/receipt received from supplier) or 'outbound' (invoice issued to customer). Defaults to inbound
total	NUMERIC	Total amount including tax
subtotal	NUMERIC	Subtotal amount before tax
currency_code	TEXT	Currency code for the transaction amount
tax_total	NUMERIC	Total tax amount included in the transaction
transaction_date	DATE	Date when the transaction occurred
due_date	DATE	Payment due date for invoices
status	TEXT	Current status: Received, Processing, In Approval, Approved, Rejected, Queried, Cancelled, or Paid
cancel_reason	TEXT	Reason for cancellation when status is Cancelled (e.g., duplicate file detection)
duplicate_of_transaction_id	UUID	Reference to the original transaction when this is a cancelled duplicate (FK to transactions.id)
billing_entity_name	TEXT	For INBOUND: Name of the billing entity (vendor/supplier)
billing_address_id	UUID	For INBOUND: Reference to the supplier billing address. For OUTBOUND: Reference to our entity address
billing_entity_contact_email	TEXT	For INBOUND: Contact email for the billing entity
billing_entity_contact_mobile	TEXT	For INBOUND: Contact mobile for the billing entity
customer_entity_name	TEXT	For OUTBOUND: Name of the customer entity receiving the invoice
customer_entity_contact_email	TEXT	For OUTBOUND: Contact email for the customer entity
customer_entity_contact_mobile	TEXT	For OUTBOUND: Contact mobile for the customer entity
customer_registration_number	TEXT	For OUTBOUND: Customer registration/tax number
customer_address_id	UUID	For OUTBOUND: Reference to the customer address
shipping_address_id	UUID	Reference to the shipping/delivery address
payment_terms	TEXT	Payment terms (e.g., Net 30, Net 60)
reference_number	TEXT	Invoice or receipt reference number
tax_number	TEXT	Tax registration number (e.g., VAT number)
accounting_memo	TEXT	Short user-provided description that syncs to the memo field in accounting integrations (e.g. QuickBooks)
ai_document_description	TEXT	AI-generated description of the document
ai_linking_logic	TEXT	Logic used by AI to link with project relationship
ai_type_logic	TEXT	Logic used by AI to determine transaction type
ai_justification	TEXT	AI-generated justification for the expense
additional_explanation	TEXT	User-provided additional explanation/justification when AI justification is insufficient (Issue Resolution System)
handwritten_notes	TEXT	Handwritten notes identified on the document by AI
tax_eligibility_assessment	TEXT	AI assessment of tax reclaim eligibility
ocr_notes	TEXT	Notes from the OCR process
has_project_name	BOOLEAN	Indicates whether the project name appears in the document (for media projects, case-insensitive match with punctuation handling). Always false for non-media
submission_note	TEXT	Note provided by the person submitting the document
submission_address	TEXT	The mobile number or Email Address from which this transaction was submitted
source	TEXT	Indicates the original source of this document (Email, Whatsapp, Manual, CSV, System)
attachment_id	UUID	Reference to the supporting document attachment for this transaction
needs_justification	BOOLEAN	Flag indicating if the transaction requires additional justification from the user
supplier_project_relationship_id	UUID	The supplier relationship for inbound transactions (FK to project_relationships, nullable)
customer_project_relationship_id	UUID	The customer relationship for outbound transactions (FK to project_relationships, nullable)
reimbursement_project_relationship_id	UUID	The person to be reimbursed for reimbursement transactions (FK to project_relationships, nullable)
billing_entity_bank_account_number	TEXT	OCR-extracted bank account number from the transaction document (nullable)
billing_entity_bank_name	TEXT	OCR-extracted bank name from the transaction document (nullable)
billing_entity_bank_sort_code	TEXT	OCR-extracted bank sort code from the transaction document (nullable)
billing_entity_bank_payee_name	TEXT	OCR-extracted bank payee name from the transaction document (nullable)
billing_entity_card_last_four	TEXT	OCR-extracted last 4 digits of card number from the transaction document (nullable)
billing_entity_registration_number	TEXT	Company registration number of the billing entity, extracted from document via OCR (nullable)
integration_data	JSON	Stores raw and parsed API data from integration validations. Uses JSON (not JSONB) to preserve key ordering. Keys: tax_validation, company_validation. Each contains { raw, parsed, validatedAt, providerName } (nullable)
search_query	TSVECTOR	Full-text search vector across transaction fields: codes, entity names/contacts, amounts, notes, AI content (auto-generated)
created_at	TIMESTAMPTZ	Timestamp when the transaction was created
updated_at	TIMESTAMPTZ	Timestamp when the transaction was last updated
created_by_user_id	UUID	User who created this transaction
updated_by_user_id	UUID	User who last updated this transaction

Key Features:

Auto-generated unique transaction codes (TRX-000001 format)
Supports different transaction types (invoices, expenses, receipts)
Links to project_relationship for vendor/person tracking
Includes tax tracking
Status workflow for approval process
Full-text search support with GIN index on search_query column
Search includes multiple transaction code formats (with/without dashes, with/without leading zeros)
Issue Resolution System Integration: When needs_justification is true, a processing_issue is created with three resolution options:
Send: Request justification via WhatsApp (Sana)
Add: User provides additional_explanation manually
Ignore: Mark as processed without additional justification
See ISSUE_RESOLUTION_SYSTEM.md for details

RLS Policies:

SELECT: Users can view their own transactions (created_by_user_id) OR all transactions in projects where they have the transaction:view:all permission
INSERT: Requires transaction:create permission on the project
UPDATE: Users can update their own transactions (created_by_user_id) OR all transactions in projects where they have the transaction:edit permission
DELETE: Not allowed (transactions should never be deleted - use Cancelled status instead)

19. Transaction Items Table (`transaction_items`)¶

Purpose: Line items for transactions with budget tracking

Use Case Example: Break down a $5,000 camera rental invoice into individual items: $3,000 for camera body, $1,500 for lenses, $500 for accessories.

Column	Type	Description
id	UUID	Unique identifier for the transaction item
transaction_id	UUID	Reference to the parent transaction
budget_item_id	UUID	Optional link to budget item for tracking
description	TEXT	Description of the line item
line_number	TEXT	Line item number from the source document
quantity	NUMERIC	Quantity of items
unit_price	NUMERIC	Price per unit
discount_type	TEXT	Type of discount: 'percentage' or 'fixed'. NULL means no discount
discount_value	NUMERIC	Raw discount value from invoice (e.g., 40.00 for 40% or 1200.00 for fixed). NULL means no discount
discount_amount	NUMERIC	Computed monetary discount deducted from (qty * unit_price) to derive subtotal. Default 0
subtotal	NUMERIC	Amount before tax for this line item (after discount)
tax_rate_id	UUID	Reference to the applicable tax rate
tax_total	NUMERIC	Tax amount for this line item
total	NUMERIC	Total amount including tax for this line item
payroll_classification	TEXT	Classification for payroll invoice items (Salary/Fee, Overtime, Equipment, Expenses, Combination, '')
expense_account_reference_id	UUID	FK to integration_reference_data (reference_type=chart_of_accounts). Expense account for this item
tracking_category_reference_id	UUID	FK to integration_reference_data (reference_type=class). Tracking category/class for this item
created_at	TIMESTAMPTZ	Timestamp when the item was created
updated_at	TIMESTAMPTZ	Timestamp when the item was last updated

Key Features:

Links individual line items to budget items for cost tracking
Supports quantity and unit price calculations
Per-item tax rates for complex tax scenarios
Per-item discounts (percentage or fixed amount) with computed discount_amount
Subtotal = (quantity * unit_price) - discount_amount

RLS Policies:

SELECT: Users can view items for transactions they own (via parent transactions.created_by_user_id) OR items in projects where they have the transaction:view:all permission
INSERT: Requires transaction:create permission on the project
UPDATE: Users can update items for transactions they own (via parent transactions.created_by_user_id) OR items in projects where they have the transaction:edit permission
DELETE: Users can delete items for transactions they own (via parent transactions.created_by_user_id) OR items in projects where they have the transaction:edit permission

20. Payment Methods Table (`payment_methods`)¶

Purpose: Stores payment method details for making payments to suppliers and vendors

Use Case Example: Track company credit cards, bank accounts, and petty cash floats used to pay suppliers.

Column	Type	Description
id	UUID	Unique identifier for the payment method
project_id	UUID	Reference to the project this payment method belongs to
type	TEXT	Payment method type: Card, Bank Account, Petty Cash Float
provider	TEXT	Payment provider/bank name (e.g., "Barclays", "HSBC", "American Express")
card_type	TEXT	Type of card: Debit or Credit. NULL for non-card payment methods
card_last_four	TEXT	Last 4 digits of card number (for Card type, nullable)
card_expiry_month	INTEGER	Card expiry month 1-12 (for Card type, nullable)
card_expiry_year	INTEGER	Card expiry year (for Card type, nullable)
cardholder_name	TEXT	Name on the card (for Card type, nullable)
bank_account_number	TEXT	Bank account number (for Bank Account type, nullable)
bank_sort_code	TEXT	Bank sort code (for Bank Account type, nullable)
bank_payee_name	TEXT	Payee/account holder name (for Bank Account type, nullable)
amount	NUMERIC	Float amount for petty cash payment methods. Stored as NUMERIC for precision
country_code	TEXT	Country associated with the payment method. FK to countries(code). NOT NULL
linking_code	TEXT	Code for linking transactions to this payment method (nullable)
is_active	BOOLEAN	Whether this payment method is currently active (default: true)
notes	TEXT	Additional notes about the payment method (nullable)
account_reference_id	UUID	Links this payment method to a chart of account in the integration system (e.g., QBO bank or credit card account). Nullable
created_at	TIMESTAMPTZ	Timestamp when the payment method was created
updated_at	TIMESTAMPTZ	Timestamp when the payment method was last updated
created_by_user_id	UUID	User who created this payment method
updated_by_user_id	UUID	User who last updated this payment method

Key Features:

Supports multiple payment method types (cards, bank accounts, petty cash)
Links to projects for multi-project payment tracking
Stores sensitive payment details securely
Tracks active/inactive status for payment methods
Links to integration reference data for QBO sync (Purchase AccountRef)

RLS Policies:

Project-based access via user_accesses table

21. Payments Table (`payments`)¶

Purpose: Aggregates multiple transactions into scheduled payment batches for a single supplier

Use Case Example: Group three invoices from the same camera rental company totaling $15,000 into a single payment scheduled for next Friday.

Column	Type	Description
id	UUID	Unique identifier for the payment
project_id	UUID	Reference to the project this payment belongs to
project_relationship_id	UUID	Reference to the supplier/vendor relationship
entity_id	UUID	Reference to the supplier/vendor entity
payment_method_id	UUID	Reference to the payment method used (nullable)
scheduled_date	DATE	Date when payment is scheduled
status	TEXT	Payment status: Scheduled, Paid, Cancelled
total_amount	NUMERIC	Total payment amount (default: 0)
currency_code	TEXT	Currency code for the payment amount
notes	TEXT	Additional notes about the payment (nullable)
paid_at	TIMESTAMPTZ	Timestamp when payment was marked as paid (nullable)
paid_by_user_id	UUID	User who marked the payment as paid (nullable)
cancelled_at	TIMESTAMPTZ	Timestamp when payment was cancelled (nullable)
cancelled_by_user_id	UUID	User who cancelled the payment (nullable)
cancel_reason	TEXT	Reason for cancellation (nullable)
created_at	TIMESTAMPTZ	Timestamp when the payment was created
updated_at	TIMESTAMPTZ	Timestamp when the payment was last updated
created_by_user_id	UUID	User who created this payment
updated_by_user_id	UUID	User who last updated this payment

Key Features:

Groups multiple transactions into single payments
Tracks payment scheduling and execution
Links to project relationships and entities
Supports payment method tracking
Unique partial index ensures one open payment per supplier per day per project (WHERE status != 'Cancelled')

RLS Policies:

Project-based access via user_accesses table

22. Payment Reconciliations Table (`payment_reconciliations`)¶

Purpose: Links individual transactions and approval instances to payments for reconciliation

Use Case Example: Allocate $5,000 from a $15,000 payment to cover invoice TRX-001234, $8,000 to cover TRX-001235, and $2,000 to cover TRX-001236.

Column	Type	Description
id	UUID	Unique identifier for the reconciliation
payment_id	UUID	Reference to the parent payment (CASCADE on delete)
transaction_id	UUID	Reference to the transaction being paid (nullable)
approval_instance_id	UUID	Reference to the approval instance (nullable)
amount	NUMERIC	Amount allocated to this transaction (must be positive)
created_at	TIMESTAMPTZ	Timestamp when the reconciliation was created
updated_at	TIMESTAMPTZ	Timestamp when the reconciliation was last updated
created_by_user_id	UUID	User who created this reconciliation
updated_by_user_id	UUID	User who last updated this reconciliation

Key Features:

Links payments to specific transactions
Supports partial payment allocations
Validates that allocated amounts don't exceed transaction totals
Unique index on (payment_id, transaction_id) prevents duplicate allocations
Cascades deletes when parent payment is deleted

RLS Policies:

Access controlled via parent payment's project

23. Payment Method Assignments Table (`payment_method_assignments`)¶

Purpose: Links payment methods to specific project relationships (people), enabling the system to trace card/bank transactions back to the assigned person for reimbursement detection.

Use Case Example: Assign a company credit card ending in 4242 to crew member John Doe, with a digital card number (Apple Pay last 4 digits) of 5678, effective from 2026-01-01. When the AI processes a transaction with card ending 5678, it can identify John as the reimbursement person.

Column	Type	Description
id	UUID	Unique identifier for the assignment
project_relationship_id	UUID	Reference to the person's project relationship (FK to project_relationships)
payment_method_id	UUID	Reference to the payment method assigned (FK to payment_methods)
digital_card_number	TEXT	Last 4 digits of digital/virtual card (e.g., Apple Pay) if different (nullable)
start_date	DATE	Date the assignment becomes effective
end_date	DATE	Date the assignment ends (nullable, NULL = still active)
notes	TEXT	Additional notes about the assignment (nullable)
created_at	TIMESTAMPTZ	Timestamp when the assignment was created
updated_at	TIMESTAMPTZ	Timestamp when the assignment was last updated
created_by_user_id	UUID	User who created this assignment
updated_by_user_id	UUID	User who last updated this assignment

Key Features:

Links payment methods to specific people (crew/cast) within a project
Supports digital card numbers for virtual/mobile payment variants (Apple Pay, Google Pay)
Temporal validity with start_date/end_date for tracking assignment periods
CHECK constraint: end_date IS NULL OR end_date >= start_date
UNIQUE constraint on (project_relationship_id, payment_method_id, COALESCE(digital_card_number, ''), COALESCE(start_date, '1970-01-01')) prevents exact duplicates
Indexes on project_relationship_id, payment_method_id, digital_card_number (partial), active assignments
AI prompt enrichment: active assignments are sent to the AI for reimbursement person identification

RLS Policies:

Project-based access via project_relationships -> v_user_accessible_projects

Production Phases Permissions¶

Permission Key	Name	Description	Module	Scope
production_phase:manage	Manage Production Phases	Create, edit, and manage production phases and days for the project	Production Phases	project

Payment System Permissions¶

Permission Key	Name	Description	Module	Scope
payment:method:manage	Manage Payment Methods	Create, edit, and manage payment methods for the project	Payments	project
payment:view	View Payments	View payments	Payments	project
payment:schedule	Schedule Payments	Schedule payments for approved transactions	Payments	project

Role Assignments:

project:owner role includes payment:schedule permission

Tax Configuration Tables¶

23. Tax Schemes Table (`tax_schemes`)¶

Purpose: Tax configuration for different regions/productions

Use Case Example: Create "California Production" scheme with 8.5% sales tax and "Georgia Production" with 4% tax plus special incentives.

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Scheme name (e.g., "US Standard")
country_code	TEXT	Country this scheme applies to (FK to countries)
is_active	BOOLEAN	Whether scheme is available for use (default: true)
rules	TEXT	Tax eligibility rules for AI prompt customization
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Constraints:

UNIQUE constraint on country_code ensures only one tax scheme per country for reliable auto-population when creating projects

24. Tax Rates Table (`tax_rates`)¶

Purpose: Specific tax rates within tax schemes

Use Case Example: UK VAT scheme has Standard Rate (20%), Reduced Rate (5%), Zero Rate (0%), and Exempt categories.

Column	Type	Description
id	UUID	Unique identifier
tax_scheme_id	UUID	Reference to the parent tax scheme
label	TEXT	Tax rate label (e.g., "Standard Rate", "Zero Rate")
description	TEXT	Detailed description of when this rate applies
percentage	NUMERIC	Tax percentage (e.g., 0.20 for 20%)
is_default	BOOLEAN	Whether this is the default rate for the scheme
is_active	BOOLEAN	Whether this rate is currently available for use
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Multiple rates per tax scheme
Unique constraint on (tax_scheme_id, label)
Support for zero and exempt rates
RLS Policies: Authenticated read-only. Writes managed by service_role (reference data)

Address Management Tables¶

25. Addresses Table (`addresses`)¶

Purpose: Store physical addresses for billing, shipping, and vendor locations

Use Case Example: Store the production office address, vendor billing addresses, or equipment delivery locations.

Column	Type	Description
id	UUID	Unique identifier
address_line_1	TEXT	Primary address line (street number and name)
address_line_2	TEXT	Secondary address line (suite, unit, etc.)
city	TEXT	City name
state_province	TEXT	State or province name
postal_code	TEXT	Postal or ZIP code
country	TEXT	Country name
location	geography(Point)	PostGIS geographic point for mapping/distance calculations
metadata	JSONB	JSON metadata including external_address_id (e.g., Google Place ID), provider, etc.
search_query	TSVECTOR	Full-text search vector for fast address matching and deduplication (auto-generated)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

PostGIS support for geographic queries
Full-text search support for efficient address matching
Unique constraint on all address fields combined
Can be referenced by multiple entities
RLS Policies: Authenticated users can SELECT, INSERT, and UPDATE. DELETE intentionally omitted — addresses are preserved for audit history and linked records

Budget Management Tables¶

26. Budgets Table (`budgets`)¶

Purpose: Core budget table linked to projects with daily allocation model support

Use Case Example: Create a budget in Draft status, define headers and items, allocate daily costs to production days, then activate. Track actual costs via transaction item allocations.

Column	Type	Description
id	UUID	Unique identifier
project_id	UUID	Reference to the project
title	TEXT	Title for this budget
status	TEXT	Draft, Active, or Archived
locked	BOOLEAN	Whether the budget is locked for editing
activated_at	TIMESTAMPTZ	Timestamp when the budget was activated
activated_by_user_id	UUID	User who activated the budget (FK users)
uploaded_from_attachment_id	UUID	Reference to the CSV attachment this budget was imported from (FK attachments, nullable)
enable_estimated_costs	BOOLEAN	Whether estimated cost tracking is enabled for this budget (default false)
enable_daily_allocations	BOOLEAN	Whether daily allocation functionality is enabled for this budget (default true)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this budget
updated_by_user_id	UUID	User who last updated this budget

Key Features:

Only one Active budget per project at a time
Totals computed dynamically via v_budgets view (not stored)
Lock mechanism prevents edits to activated budgets
budget_status enum: Draft, Active, Archived

27. Budget Headers Table (`budget_headers`)¶

Purpose: Hierarchical budget structure for organizing line items into categories

Use Case Example: Create "Above the Line" header with child headers "Cast" and "Director/Producers". Under "Cast", add individual line items for lead actors.

Column	Type	Description
id	UUID	Unique identifier
budget_id	UUID	Reference to budget
parent_budget_header_id	UUID	Reference to parent header (for nesting)
account_code	TEXT	Account code for this budget category (e.g., "1000", "2000")
title	TEXT	Header title
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this header
updated_by_user_id	UUID	User who last updated this header

Key Features:

Totals computed dynamically via v_budget_headers view using recursive CTE
Supports infinite nested hierarchy with parent_budget_header_id
Account codes help with financial reporting
Header totals include items from itself AND all descendant headers at any depth

Common Budget Headers:

Above the Line (Cast, Director, Producers, Script)
Below the Line (Crew, Equipment, Locations)
Post-Production (Editing, VFX, Sound, Music)
Other (Insurance, Legal, Contingency)

28. Budget Items Table (`budget_items`)¶

Purpose: Individual line items within budget headers with daily allocation model

Use Case Example: Add "Camera Operator" under Crew header with original estimated cost. Set as daily interval type, then allocate specific quantities and rates per production day.

Column	Type	Description
id	UUID	Unique identifier
budget_header_id	UUID	Reference to parent header
account_code	TEXT	Full account code (e.g., "1100-001")
title	TEXT	Title of the budget item (required)
description	TEXT	Optional description of the budget item
original_quantity	NUMERIC	Original budgeted quantity
original_rate	NUMERIC	Original budgeted rate per unit
original_total	NUMERIC	Original budgeted total cost
interval_type	TEXT	daily (per production day) or fixed (flat cost)
interval_quantity	NUMERIC	Number of days for daily interval items
unit_cost	NUMERIC	Cost per unit
total_estimated_cost_cents	BIGINT	Total estimated cost in cents
cost_code_part_1	VARCHAR(50)	First part of cost code
cost_code_part_2	VARCHAR(50)	Second part of cost code
currency_code	TEXT	Currency for this item
unit_type	VARCHAR(50)	Type of unit (days, weeks, flat, etc.)
is_manual_total	BOOLEAN	Whether total is manually set vs calculated
tax_rate	NUMERIC	Tax rate for this item (0.00 to 1.00)
linked_production_item_id	UUID	Link to production tracking
is_group_header	BOOLEAN	Whether this is a grouping item
parent_budget_item_id	UUID	Parent item for sub-items
child_items_total_formula	TEXT	Formula for calculating child totals
quantity_calculation_formula	TEXT	Formula for dynamic quantity
total_estimated_cost	NUMERIC	Total in currency units (computed)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this item
updated_by_user_id	UUID	User who last updated this item

Key Features:

budget_interval_type enum: daily, fixed
Daily items are allocated per production day via budget_item_daily_allocations
Computed fields via v_budget_items view: allocated_total, allocated_avg_quantity, allocated_max_quantity, estimated_cost_total, actual_total, estimated_cost_balance
Supports hierarchical items with parent_budget_item_id

28a. Budget Item Daily Allocations Table (`budget_item_daily_allocations`)¶

Purpose: Daily allocations of budget items to specific production days

Use Case Example: Allocate a "Camera Operator" budget item to Day 1 with quantity 1 at rate $500, and to Day 2 with quantity 2 at rate $500 (overtime day).

Column	Type	Description
id	UUID	Unique identifier
budget_item_id	UUID	Reference to the budget item (FK, ON DELETE CASCADE)
production_day_id	UUID	Reference to the production day (FK, ON DELETE CASCADE)
quantity	NUMERIC	Allocated quantity for this production day
rate	NUMERIC	Rate per unit for this production day
estimated_cost	NUMERIC	Estimated cost for this allocation
estimated_quantity	NUMERIC	Estimated quantity for this allocation
estimated_cost_note	TEXT	Note explaining the estimated cost
canceled_estimated_cost	NUMERIC	Canceled estimated cost amount (set when estimation is cleared)
added_from_estimated_costs	BOOLEAN	Whether this allocation was auto-created from estimated costs
added_from_processing	BOOLEAN	Whether this allocation was auto-created during transaction processing
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this allocation
updated_by_user_id	UUID	User who last updated this allocation

Key Features:

UNIQUE(budget_item_id, production_day_id) - one allocation per item per day
Computed fields via v_budget_item_daily_allocations view: total (qty * rate), actual_total (from transaction items), balance, estimated_cost_balance
Allocations can be created manually, from estimated costs (added_from_estimated_costs), or auto-created during transaction processing (added_from_processing)

RLS Policies:

SELECT: Requires budget:view permission (traverses budget_items → budget_headers → budgets → project)
INSERT/UPDATE: Requires budget:edit permission
DELETE: Requires budget:delete permission

28b. Budget Allocation Transaction Items Table (`budget_allocation_transaction_items`)¶

Purpose: Links daily budget allocations to transaction line items for actual cost tracking

Use Case Example: A $500 transaction line item for "Camera Operator Day 1" is allocated to the corresponding daily allocation record. Partial allocations are supported.

Column	Type	Description
id	UUID	Unique identifier
budget_item_daily_allocation_id	UUID	Reference to the daily allocation (FK, ON DELETE CASCADE)
transaction_item_id	UUID	Reference to the transaction line item (FK, ON DELETE CASCADE)
amount	NUMERIC	Amount allocated from the transaction item (partial/full)
created_at	TIMESTAMPTZ	Creation timestamp
created_by_user_id	UUID	User who created this link

Key Features:

UNIQUE(budget_item_daily_allocation_id, transaction_item_id) - one link per pair
amount allows partial allocation of a transaction item's total across multiple daily allocations
No updated_at/updated_by needed (join records are created/deleted, not updated)

RLS Policies:

SELECT: Requires budget:view permission (traverses daily_allocations → budget_items → budget_headers → budgets → project)
INSERT/UPDATE: Requires budget:edit permission
DELETE: Requires budget:delete permission

Schedule Management Tables¶

29. Schedules Table (`schedules`)¶

Purpose: Manages project schedules with version control support

Use Case Example: Create "v1.0" as Draft while planning, then create "v2.0" as Active once approved. Previous versions remain for historical reference.

Column	Type	Description
id	UUID	Unique identifier
project_id	UUID	Reference to the project
version_number	NUMERIC	Version number (e.g., 1, 2, 3)
title	TEXT	Title for this schedule
status	TEXT	Draft/Proposed/Approved/Active/Superseded/Archived
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this schedule
updated_by_user_id	UUID	User who last updated this schedule

Key Features:

Unique constraint on (project_id, version_number)
Supports draft and approved workflows
Maintains version history

30. Production Phases Table (`production_phases`)¶

Purpose: Defines production phases within a project with optional automatic day generation

Use Case Example: Add a "Shooting" phase to a project with a start date of Jan 1st for 30 days. The system automatically generates the individual production days. Phases can also be created without dates for planning purposes.

Column	Type	Description
id	UUID	Unique identifier
project_id	UUID	Reference to project (ON DELETE CASCADE)
name	TEXT	Phase name (e.g., Development, Pre-Production)
description	TEXT	Optional description of the phase
phase_type	production_phase_type	Type of phase: Pre-Production, Shooting, or Post-Production
start_date	DATE	Planned start date (nullable - can be set later)
number_of_days	INTEGER	Duration in days (nullable, CHECK > 0 - can be set later)
end_date	DATE	End date (computed as start_date + number_of_days - 1, nullable)
country_code	TEXT	Country where this phase takes place (FK to countries, nullable)
production_base	TEXT	Base location for this phase (nullable)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this phase
updated_by_user_id	UUID	User who last updated this phase

Key Features:

Now linked directly to projects (replacing schedule_id FK)
Day generation is skipped when start_date or number_of_days is NULL
Uses soft-delete approach (is_removed) for day management instead of hard DELETE
end_date is computed automatically by a BEFORE trigger from start_date + number_of_days - 1
Phases are ordered by phase_type (enum order), then start_date, then created_at
Triggers automatically manage production days

Indexes:

idx_production_phases_project_id on project_id
idx_production_phases_country_code on country_code

31. Production Days Table (`production_days`)¶

Purpose: Individual days within production phases with customizable work schedules

Use Case Example: "Shooting" phase has 30 days. Mark day 15 as a Travel day, and soft-delete days when a phase is shortened so they can be reactivated if the phase is later extended again.

Column	Type	Description
id	UUID	Unique identifier
production_phase_id	UUID	Reference to production phase
day_number	INTEGER	Sequential day number (nullable - only Working days get a number)
calendar_date	DATE	Actual calendar date
day_of_week	TEXT	Day of week name (Monday-Sunday) - generated from calendar_date
day_type	day_type	Type of day: Working, Travel, or Rest (default: Working)
notes	TEXT	Notes about the day
is_removed	BOOLEAN	Whether this day has been soft-deleted (default: false)
removed_at	TIMESTAMPTZ	Timestamp when the day was soft-deleted (nullable)
removed_by_user_id	UUID	User who soft-deleted this day (FK to users, nullable)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Automatically created/updated by phase triggers
Uses soft-delete (is_removed) instead of hard DELETE when phases are shortened
Previously removed days are reactivated before creating new ones when phases are extended
day_number is nullable: only Working days get sequential numbers; Travel/Rest days have NULL
Renumbering is done via the renumber_working_days(p_phase_id) RPC function
Days are ordered by calendar_date (not day_number) since day_number is nullable
Unique constraint on (production_phase_id, day_number) — NULLs allowed
Unique constraint on (production_phase_id, calendar_date) — DEFERRABLE INITIALLY IMMEDIATE, allowing the trigger to shift dates without intermediate violations

Indexes:

idx_production_days_is_removed on is_removed
idx_production_days_removed_by_user_id on removed_by_user_id

Notification System Tables¶

32. Notification Senders Table (`notification_senders`)¶

Purpose: Stores email addresses and phone numbers used to send notifications

Use Case Example: Configure notifications@farmcove.com as the default email sender and "+1234567890" as the WhatsApp sender for system notifications.

Column	Type	Description
id	UUID	Unique identifier
sender_type	TEXT	Type of sender: 'email' or 'phone'
sender_value	TEXT	The actual email address or phone number
sender_name	TEXT	Display name shown to recipients (e.g., "FarmCove Support")
provider	TEXT	Service provider (e.g. resend, twilio, ses, sendgrid)
is_active	BOOLEAN	Whether this sender is currently active
is_default	BOOLEAN	Whether this is the default sender for its type
category_id	UUID	Optional FK to categories - links sender to specific project types (NULL = all categories)
metadata	JSONB	Provider-specific configuration
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Unique constraint on (sender_type, sender_value)
Only one default sender per type
Supports multiple providers for redundancy
Optional category association allows senders to be project-type specific (e.g., WhatsApp for media projects)

Default Senders:

sender_value	sender_name	Purpose
`noreply@farmcove.co.uk`	FarmCove	Default email sender (exports)
`approval@farmcove.co.uk`	Farm Cove Approvals	Approval workflow emails

33. Notification Template Categories Table (`notification_template_categories`)¶

Purpose: Categories for grouping notification templates in the UI

Use Case Example: Group all budget-related notifications under "Budget & Finance" category for better organization in user preferences.

Column	Type	Description
id	UUID	Unique identifier
code	TEXT	Unique code for the category (e.g., system, project, team)
title	TEXT	Display title for the category
description	TEXT	Description of the category to show in UI
display_order	INTEGER	Order for displaying categories in UI
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Unique constraint on code
Used to organize notification preferences UI
Categories include: system, project, team, budget, transaction, schedule
RLS Policies: Authenticated read-only. Notification categories managed by service_role

34. Notification Templates Table (`notification_templates`)¶

Purpose: Templates for different types of notifications with multi-channel support

Use Case Example: Create a "budget-threshold-reached" template that sends email alerts, WhatsApp messages, and in-app notifications when budget exceeds 80%.

Column	Type	Description
id	UUID	Unique identifier
template_key	TEXT	Unique key used in code (e.g., 'invoice-created')
name	TEXT	Human-readable name for admin UI
category_id	UUID	Reference to notification_template_category
description	TEXT	Description for admin UI
is_active	BOOLEAN	Whether this template is currently active and can be used
email_available	BOOLEAN	Whether this notification can be sent via email
whatsapp_available	BOOLEAN	Whether this notification can be sent via WhatsApp
in_app_available	BOOLEAN	Whether this notification can be shown in-app
subject_template	TEXT	Email subject line with ${variables}.
body_template	TEXT	HTML for email, plain text for in-app
body_template_text	TEXT	Plain text version for email clients
whatsapp_config	JSONB	WhatsApp configuration including template: {name, language}, flow: { id, name, first_screen} and components
metadata	JSONB	Additional metadata including media information (media_type, media_url) and other template-specific data
default_email_sender_id	UUID	Reference to default email sender
default_phone_sender_id	UUID	Reference to default phone sender
show_in_preferences	BOOLEAN	Whether this notification template should be shown in user preferences UI
mandatory_channels	TEXT[]	Array of channels that are mandatory for this notification template (cannot be disabled by users). Valid values: in_app, email, whatsapp
default_email_enabled	BOOLEAN	Whether email is enabled by default for this template (user can override in preferences). Default: false
default_whatsapp_enabled	BOOLEAN	Whether WhatsApp is enabled by default for this template (user can override in preferences). Default: false
default_in_app_enabled	BOOLEAN	Whether in-app is enabled by default for this template (user can override in preferences). Default: true
required_permissions	TEXT[]	Array of permission keys required to see this template in preferences. NULL or empty = visible to all users
email_template	TEXT	HTML template specifically for email channel. If NULL, body_template is used for email. Supports rich HTML with styled action buttons
digest_category_parent_code	TEXT	Parent category code for digest grouping (e.g., 'transactions'). Links templates to category-based digest preferences
digest_category_group_code	TEXT	Category group code for digest grouping (e.g., 'approval_types'). Used with parent code to identify category hierarchy for digests
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Templates control which channels are available via *_available flags
Templates define default enabled state via default_*_enabled flags
Supports variable substitution with ${variables}
WhatsApp configuration consolidated in single JSONB field
Mandatory notifications cannot be disabled by users
Category relationship for better organization
Mandatory channels array for granular control per template
Permission-based visibility: templates can require specific permissions to be shown in user preferences

Channel Priority:

When determining which channels to use for a notification:

Check if channel is available on template (*_available = true)
Check if channel is mandatory (mandatory_channels array) - user cannot disable
Check if user has template-specific override in preferences
Otherwise use template default (default_*_enabled)

Category-Aware Templates:

Several templates now support dynamic content based on project category flags (is_media, is_accounting, is_personal_accounting):

known_person - Uses ${project_type_description} variable:
Media: "You are talking to {person_name}, who {person_relationship} for the project {project_title}, as {role_name}..."
Personal Accounting: "You are talking to {person_name}, who {person_relationship} for the {project_title} personal accounting project..."
Company Accounting: "You are talking to {person_name}, who {person_relationship} for the {project_title} company accounting project..."
switch_project (renamed from switch_film) - Generic project switching instructions
Template key: switch_project
Body: "Please choose a project or onboard to a new one using the button."
switch_success - Uses ${project_type_description} variable:
Media: "You are now working on the project {project_title}, as {role_name}, please just let me know if you need to switch context."
Personal Accounting: "You are now working on the {project_title} personal accounting project, please just let me know if you need to switch context."
Company Accounting: "You are now working on the {project_title} company accounting project, please just let me know if you need to switch context."

Approval Templates (Category: approval):

Templates for approval workflow notifications:

Template Key	Name	Description	Variables
`approval_query`	Approval Query	Notification sent to submitter when an approver queries a submission	`${name}`, `${reference}`
`approval_rejection`	Approval Rejection	Notification sent to submitter when their submission is rejected	`${name}`, `${reference}`, `${reason}`
`approval_reply_received`	Approval Reply Received	Notification sent to approver when submitter replies to their query	`${name}`, `${submitter_name}`, `${reference}`
`transaction_approval`	Transaction Approval Request	Notification sent to approvers when a transaction is submitted	Email/In-App: `${transaction_type}`, `${transaction_identifier}`, `${reference}`, `${submitter_name}`, `${description}`, `${total}`. WhatsApp body: `${attachmentId}`, `${typeAndProject}`, `${typeAndReference}`, `${reference}`, `${submitter_name}`, `${description}`. WhatsApp flowData: `${heading}`, `${subheading}`, `${flowDescription}`, `${minDate}`, `${scheduledPaymentDate}`, `${scheduledPaymentAmount}`, `${queryHistory}`, `${options}`

All approval templates have:

in_app_available: true
whatsapp_available: true
email_available: true
mandatory_channels: ['in_app']

35. Notifications Table (`notifications`)¶

Purpose: Central log of all notifications sent to users

Use Case Example: When budget reaches 80%, system creates notification record with rendered content and tracks which channels were used to deliver it.

Column	Type	Description
id	UUID	Unique identifier
project_id	UUID	Project this notification relates to (optional)
recipient_user_id	UUID	User who received the notification
created_by_user_id	UUID	User who created/triggered this notification (NULL for system-generated notifications)
template_id	UUID	Template used (optional for custom notifications)
subject	TEXT	Rendered email subject
content_html	TEXT	HTML version for email
content_text	TEXT	Plain text for in-app/WhatsApp
data	JSONB	Original template variables for re-rendering/audit
whatsapp_data	JSONB	Rendered WhatsApp component data
channels_sent	JSONB	Which channels were used {"email": true, "whatsapp": false}
allow_mark_as_unread	BOOLEAN	Whether this notification can be marked as unread by the recipient (disabled by default)
created_at	TIMESTAMPTZ	When notification was created

Key Features:

Immutable audit log of all notifications
Stores rendered content for display
Tracks which channels were actually used
Enabled for Realtime to power in-app notifications

36. Notification Deliveries Table (`notification_deliveries`)¶

Purpose: Tracks delivery status for each channel

Use Case Example: Email sent via Resend at 2 PM, delivered at 2:01 PM, read at 3 PM. WhatsApp message failed due to invalid number.

Column	Type	Description
id	UUID	Unique identifier
notification_id	UUID	Reference to parent notification
channel	TEXT	Delivery channel: email/whatsapp/in_app
recipient_address	TEXT	Email, phone number, or user_id
sender_address	TEXT	Email or phone used to send
sender_id	UUID	Reference to notification sender
provider	TEXT	Service provider used (resend, twilio, supabase-realtime)
status	TEXT	pending/sent/delivered/failed/read
error_message	TEXT	Error details if failed
sent_at	TIMESTAMPTZ	When notification was sent
delivered_at	TIMESTAMPTZ	When reached recipient device/inbox
read_at	TIMESTAMPTZ	When recipient read/opened
retry_count	INTEGER	Number of retry attempts
created_at	TIMESTAMPTZ	Creation timestamp
notification_action_token_id	UUID	FK to notification_action_tokens, nullable - for EMAIL and WHATSAPP deliveries with action links

Key Features:

One record per channel per notification
Tracks complete delivery lifecycle
Supports retry logic with attempt counting
WhatsApp-specific tracking moved to message.whatsapp_metadata
Links to email action tokens for traceability of email action links
Digest notifications use live-query pattern: cron jobs query pending items directly at digest time rather than storing stale delivery records

37. Notification Attachments Table (`notification_attachments`)¶

Purpose: Links notifications to file attachments

Use Case Example: Invoice notification includes PDF attachment sent via email, but not via WhatsApp.

Column	Type	Description
id	UUID	Unique identifier
notification_id	UUID	Reference to notification
attachment_id	UUID	Reference to attachment
channel	TEXT	Which channel this attachment is for (email/whatsapp)
created_at	TIMESTAMPTZ	Creation timestamp

Key Features:

Supports channel-specific attachments
Links to existing attachment system
Allows different attachments per channel

Messaging System Tables¶

38. Conversations Table (`conversations`)¶

Purpose: Permanent container for all interactions between a user and the system across any channel

Use Case Example: When a user sends a WhatsApp message to Sana, a conversation record tracks all their interactions on that channel for a specific project and role.

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
user_id	UUID	Reference to user who owns this conversation
channel	TEXT	Communication channel: whatsapp/sms/web/in_app
channel_identifier	TEXT	Phone number for WhatsApp/SMS, session ID for web
notification_sender_id	UUID	Reference to notification sender (business phone number) for this conversation
status	TEXT	active/archived/blocked/current (only one current per channel/sender)
project_id	UUID	Project this conversation is associated with
project_relationship_id	UUID	Specific project relationship (role) this conversation is for
last_message_at	TIMESTAMPTZ	Timestamp of most recent message
rate_limit_data	JSONB	Stores rate limiting information for the conversation
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this conversation record (defaults to auth.uid())
updated_by_user_id	UUID	User who last updated this conversation record (defaults to auth.uid())

Key Features:

One conversation per user per channel per project per role (project_relationship) per notification sender
Support for 'current' status - only one conversation can be current per user/channel/notification_sender
Never deleted, only archived or blocked (except when project is deleted - cascade delete applies)
Permanent audit trail of all interactions
Unique constraint on (user_id, channel, channel_identifier, project_id, project_relationship_id, notification_sender_id)
Partial unique index ensures only one current conversation per user/channel/notification_sender
CASCADE DELETE on project_id foreign key - conversations are automatically deleted when their associated project is deleted
Automatic timestamp tracking via triggers
Allows separate conversations per role when user has multiple roles in same project
Multi-sender support: Different business phone numbers (notification_senders) maintain separate conversation contexts

rate_limit_data JSONB structure tracks message counts and throttle status:

{
  "daily_message_count": 0,
  "daily_reset_at": null,
  "hourly_message_count": 0,
  "hourly_reset_at": null,
  "minute_message_count": 0,
  "minute_reset_at": null,
  "is_throttled": false,
  "throttled_until": null
}

39. Conversation Instances Table (`conversation_instances`)¶

Purpose: Time-based grouping of messages within a conversation (8-hour windows)

Use Case Example: User has a conversation about budgets in the morning. After 8 hours of inactivity, their afternoon messages start a new instance with fresh context. If the conversation was triggered by a notification (e.g., "Budget threshold reached"), the notification_id links back to that original notification.

Column	Type	Description
id	UUID	Unique identifier
conversation_id	UUID	Reference to parent conversation
notification_id	UUID	Reference to notification that created or triggered this conversation instance
instance_number	INTEGER	Sequential number within conversation
status	TEXT	active/closed/summarized
started_at	TIMESTAMPTZ	When this instance began
closed_at	TIMESTAMPTZ	When instance was closed
last_message_at	TIMESTAMPTZ	Timestamp of most recent message
message_count	INTEGER	Total messages in this instance
auto_closed_reason	TEXT	message_limit/time_limit/abuse_detected/manual
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this conversation instance record (defaults to auth.uid())
updated_by_user_id	UUID	User who last updated this conversation instance record (defaults to auth.uid())

Key Features:

8-hour inactivity creates new instance
Limits of 500 messages per instance
AI summaries generated on closure
Maintains conversation continuity
Optional link to triggering notification via notification_id

40. Messages Table (`messages`)¶

Purpose: Individual messages within conversation instances

Use Case Example: User sends "Show me my spending", Sana responds with budget analysis - both stored as message records.

Column	Type	Description
id	UUID	Unique identifier
conversation_instance_id	UUID	Reference to parent instance (nullable)
notification_delivery_id	UUID	Reference to notification_delivery if triggered by notification
approval_request_id	UUID	Reference to approval_request for approval workflow messages
direction	TEXT	inbound (from user), outbound (to user), or internal (for AI context)
sender_type	TEXT	user, assistant, system, functions, tool, or tool_calls
sender_user_id	UUID	Reference to user (NULL for system messages)
entity_id	UUID	Reference to entity (contact/company) that sent or received message
project_id	UUID	Reference to project this message belongs to (required)
source	TEXT	Source: WhatsApp, Email, Manual, or Approval Message (default: WhatsApp)
external_id	TEXT	External message ID (e.g., email Message-ID header) for deduplication
parent_id	UUID	Reference to parent message for threading (e.g., email replies)
root_message_id	UUID	Reference to root message in thread (auto-populated via trigger)
origin_message_id	UUID	Links Approval Message to source WhatsApp/Email message (for inbound queries/replies)
content_text	TEXT	Plain text message content
content_json	JSONB	Rich content (buttons, media, interactive) or WhatsApp/Email payload
whatsapp_metadata	JSONB	WhatsApp-specific data (message_id, flow_token, process_name)
ai_function_id	UUID	Reference to AI function if this is a function call/response
include_in_ai_context	BOOLEAN	Whether to include this message in AI conversation context
status	TEXT	pending/sent/delivered/read/failed (default: pending)
metadata	JSONB	Extra metadata added to the message
channel_address	TEXT	The channel-specific address (phone for WhatsApp, email for email) used for this message
error_message	TEXT	Error details if status = failed
sent_at	TIMESTAMPTZ	When message was sent
delivered_at	TIMESTAMPTZ	When message was delivered
read_at	TIMESTAMPTZ	When message was read
created_at	TIMESTAMPTZ	Creation timestamp
created_by_user_id	UUID	User who created this message record

Key Features:

Full message history preserved
Rich content support via JSON (also stores WhatsApp payload for outbound messages)
Delivery tracking per message with 'initiated' as default status
WhatsApp-specific metadata storage
AI function tracking for bot interactions
Context control for AI conversations
Support for 'internal' direction for system-generated AI context messages
conversation_instance_id is nullable for flexibility
channel_address field tracks the channel-specific address (phone for WhatsApp, email for email) used for this message
Multiple sender_type values to support AI message roles
Optional entity_id links messages to specific contacts/companies for compliance tracking
Message Threading Support:
parent_id creates parent-child relationships for message replies (e.g., email threads)
root_message_id denormalized field pointing to the root of the thread (auto-populated via trigger)
external_id prevents duplicate processing of messages (e.g., same email processed twice)
Trigger automatically sets root_message_id by recursively traversing parent_id chain
Enables efficient querying of entire message threads without recursive CTEs
Smart tagging logic: Reply messages with ALL duplicate attachments skip tagging; replies with NEW attachments tag both reply and root message
Approval Message Architecture:
source = 'Approval Message' identifies UI thread messages for approval workflows
Separate from WhatsApp/Email delivery records which have their own source values
approval_request_id links messages to their approval workflow
origin_message_id links UI thread messages to their source WhatsApp/Email inbound message (for queries/replies via external channels)
v_approval_messages view filters by source='Approval Message' to show only UI thread
Check constraints ensure data integrity:
Inbound messages must have sender_type='user' with non-null sender_user_id and created_by_user_id
Outbound and internal messages cannot have sender_type='user'

41. Message Attachments Table (`message_attachments`)¶

Purpose: Links messages to file attachments

Use Case Example: User sends photo of receipt via WhatsApp, attachment linked to their message.

Column	Type	Description
id	UUID	Unique identifier
message_id	UUID	Reference to message
attachment_id	UUID	Reference to attachment record
created_at	TIMESTAMPTZ	Creation timestamp

Key Features:

Many-to-many relationship
Reuses existing attachment system
Supports multiple attachments per message

42. Conversation Summaries Table (`conversation_summaries`)¶

Purpose: AI-generated summaries of closed conversation instances

Use Case Example: After 8-hour conversation about project planning, AI summarizes key decisions and action items for future context.

Column	Type	Description
id	UUID	Unique identifier
conversation_instance_id	UUID	Reference to summarized instance
summary_text	TEXT	Natural language summary
key_points	JSONB	Array of key points discussed
topics	TEXT[]	Extracted topics for searchability
sentiment	TEXT	positive/neutral/negative/mixed
action_items	JSONB	Array of identified follow-ups
metadata	JSONB	Additional AI-generated insights
created_at	TIMESTAMPTZ	Creation timestamp
created_by_user_id	UUID	User who triggered the summary generation

Key Features:

Preserves context across instances
Searchable topics array
Sentiment analysis included
Action items tracked

Abuse Prevention Tables¶

43. Rate Limit Configs Table (`rate_limit_configs`)¶

Purpose: Configurable rate limits by channel and user type

Use Case Example: WhatsApp limited to 5 messages/minute for regular users, 10 for premium users.

Column	Type	Description
id	UUID	Unique identifier
channel	TEXT	Channel to apply limit to
limit_type	TEXT	per_minute/per_hour/per_day/per_instance
message_count	INTEGER	Maximum messages allowed
user_type	TEXT	User category for differentiated limits
is_active	BOOLEAN	Whether this limit is enforced
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Flexible limits per channel
Different limits for user types
Can be updated without code changes
Per-instance limits prevent endless conversations
RLS Policies: Authenticated read-only. Rate limit configuration managed by service_role

User Type Examples:

The user_type field enables differentiated rate limiting based on user categories:

'all' (default): Standard rate limits applied to all users
Example: 5 messages/minute, 60/hour, 200/day
'new': Stricter limits for newly registered users (e.g., first 7 days)
Example: 3 messages/minute, 30/hour, 100/day
Use case: Prevent spam from newly created accounts
'verified': Standard limits for users who have verified their phone/email
Example: 5 messages/minute, 60/hour, 200/day
Use case: Normal usage for authenticated users
'premium': Higher limits for paid subscribers
Example: 10 messages/minute, 120/hour, 500/day
Use case: Enhanced service for paying customers
'trusted': Minimal limits for long-standing users with good history
Example: 20 messages/minute, 240/hour, 1000/day
Use case: Reward loyal users with fewer restrictions

Implementation Note: The check_rate_limits_and_abuse() function accepts a p_user_type parameter (defaults to 'all') to apply the appropriate limits based on user category.

44. Abuse Patterns Table (`abuse_patterns`)¶

Purpose: Tracks detected abuse patterns in messaging

Use Case Example: User sends identical message 10 times rapidly, system detects spam pattern with 95% confidence.

Column	Type	Description
id	UUID	Unique identifier
user_id	UUID	Reference to user
conversation_id	UUID	Reference to conversation
pattern_type	TEXT	spam/flooding/gibberish/profanity/phishing/api_abuse
confidence_score	NUMERIC	Pattern detection confidence (0.00-1.00)
detected_at	TIMESTAMPTZ	When pattern was detected
sample_messages	TEXT[]	Sample of problematic messages
action_taken	TEXT	warning/throttle/block/report
metadata	JSONB	Additional pattern details
created_at	TIMESTAMPTZ	Creation timestamp

Key Features:

Multiple pattern types detected
Confidence scoring for accuracy
Preserves evidence samples
Tracks enforcement actions

45. Abuse Action Histories Table (`abuse_actions`)¶

Purpose: Progressive penalty system for repeat offenders

Use Case Example: User's first spam gets warning, second gets 15min throttle, third gets 1hr throttle, etc.

Column	Type	Description
id	UUID	Unique identifier
user_id	UUID	Reference to user
action_type	TEXT	Type of action taken
action_severity	INTEGER	1=warning, 2=short throttle, 3=long, 4=block
expires_at	TIMESTAMPTZ	When action expires
created_at	TIMESTAMPTZ	Creation timestamp

Key Features:

Progressive penalties increase
Time-based expiration
30-day lookback for repeat offenses
Automatic enforcement

AI Integration Tables¶

46. AI Functions Table (`ai_functions`)¶

Purpose: Stores AI functions available for WhatsApp bot and other AI services

Use Case Example: Define "DisplayMenu" function that allows Sana to send WhatsApp interactive menus, or "SpendQuery" that analyzes spending by supplier.

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Unique function name (e.g., DisplayMenu, GetBudgetInformation)
description	TEXT	Detailed description of what the function does
system_prompt	TEXT	System prompt that defines function behavior
inputschemajson	JSONB	JSON Schema defining the function input schema
required_permissions	TEXT[]	Array of permission keys user must have to use this function
need_location	BOOLEAN	Whether function requires user location data
always_use_latest	BOOLEAN	If true, only use most recent result in conversation
is_active	BOOLEAN	Whether function is currently available for use
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Defines available AI capabilities
JSON Schema validation for parameters
Permission-based access control
Support for external API endpoints
Function behavior flags (location, latest results, agent triggers)

47. AI Prompts Table (`ai_prompts`)¶

Purpose: Stores AI prompt templates for WhatsApp bot and other AI services

Use Case Example: "GatewayPrompt" defines Sana's personality and behavior, while "SummaryPrompt" creates conversation summaries.

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Unique prompt name (e.g., GatewayPrompt, SummaryPrompt)
description	TEXT	Description of the prompt purpose
system_prompt	TEXT	System prompt that sets AI behavior and context
user_prompt	TEXT	User prompt template with {!variables} for substitution
model_config	JSONB	Model config: {model, max_tokens, config: {...params}}
outputschemajson	JSONB	JSON schema defining the expected output format for the AI prompt
need_menu	BOOLEAN	Whether to include menu options in response
is_active	BOOLEAN	Whether prompt is currently available for use
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Template-based prompt management
Model configuration per prompt
Variable substitution support
Menu integration flags

48. AI Prompt Functions Table (`ai_prompt_functions`)¶

Purpose: Junction table linking AI prompts to available functions

Use Case Example: GatewayPrompt has access to all 8 functions (DisplayMenu, GetLoggingInformation, etc.) in a specific order.

Column	Type	Description
id	UUID	Unique identifier
ai_prompt_id	UUID	Reference to AI prompt
ai_function_id	UUID	Reference to AI function
display_order	INTEGER	Order in which functions are presented to the AI
created_at	TIMESTAMPTZ	Creation timestamp

Key Features:

Many-to-many relationship between prompts and functions
Ordered function presentation
Unique constraint on (ai_prompt_id, ai_function_id)

Processing System Tables¶

49. Process Templates Table (`process_templates`)¶

Purpose: Define processing workflows (both AI and manual validation) for different entity types

Use Case Example: Create "Transaction Import Processing" template with OCR, classification, extraction, validation steps, and manual checks like total verification.

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Template name (e.g., "Transaction Import Processing")
description	TEXT	Description of what this template processes
processable_type	TEXT	Entity type: transactions, budgets, or schedules
service_name	TEXT	Name of the service that handles processing
is_active	BOOLEAN	Whether this template is currently available
metadata	JSONB	Additional template configuration (e.g., {"notification_on_completion": true})
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

One active template per processable type
Defines multi-step processing workflows (both AI and manual)
Metadata for template-specific configuration
Example metadata: {"notification_on_completion": true} to notify users when processing completes

50. Process Template Steps Table (`process_template_steps`)¶

Purpose: Define individual steps within a processing template (AI or manual)

Use Case Example: AI steps like OCR extraction, manual validation steps like checkTransactionTotals, or transformation steps.

Column	Type	Description
id	UUID	Unique identifier
process_template_id	UUID	Reference to parent template
step_key	TEXT	Unique key for this step (e.g., "ocr", "check_totals")
display_name	TEXT	User-friendly name for status display
description	TEXT	Detailed description of what this step does
type	process_step_type	Type of step: 'ai' (AI Hub) or 'manual' (custom validation/processing)
display_order	INTEGER	Display order for steps in the UI (lower numbers appear first)
display_in_ui	BOOLEAN	Whether this step should be displayed in UI progress indicators (default: true)
ai_hub_step_type	TEXT	Type of AI processing step (PascalCase) - for AI steps only
ai_prompt_id	UUID	Optional reference to AI prompt - for AI steps only
processor_method	TEXT	Method name in the service to call when processing this step
execution_group	INTEGER	Steps with same group can run in parallel
depends_on_steps	TEXT[]	Array of step_keys this step depends on
is_active	BOOLEAN	Whether this step is active or not
can_run_parallel	BOOLEAN	Whether this step can run in parallel with others in its group
expected_duration_seconds	INTEGER	Expected duration for progress estimation
max_retries	INTEGER	Maximum number of retry attempts if step fails
timeout_seconds	INTEGER	Maximum time allowed for step execution before timeout
condition_expression	TEXT	SQL-like expression evaluated in app to determine if step should run
condition_description	TEXT	Human-readable description of when this step runs
on_failure_action	TEXT	What to do if this step fails: fail_job, skip_dependents, or continue
on_retry_action	JSONB	Retry configuration: {switch_models: [model1, model2], raise_max_tokens_by: 2000}
metadata	JSONB	Step-specific configuration
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Supports both AI and manual processing steps via type field
AI steps send requests to AI Hub, manual steps run custom validation logic
Supports parallel and sequential execution
Conditional execution based on previous results
Optional steps that don't block on failure
Display control via display_in_ui flag (useful for internal processing steps)
Manual steps can create issues for user resolution
Retry configuration via on_retry_action:
switch_models: Array of models to cycle through on retries (cycles with original model)
raise_max_tokens_by: Amount to increase max output tokens per retry (no cap, increases linearly)

51. Processing Jobs Table (`processing_jobs`)¶

Purpose: Track processing job instances (AI and manual)

Use Case Example: When a user uploads a receipt, create a job to process it through OCR, classification, data extraction, and validation checks.

Column	Type	Description
id	UUID	Unique identifier
processable_id	UUID	ID of the entity being processed
processable_type	TEXT	Type of entity: transactions, budgets, or schedules
process_template_id	UUID	Reference to the template used
overall_status	TEXT	Status: pending, in_progress, completed, failed, partial_success
progress_percentage	INTEGER	Overall progress (0-100)
error_message	TEXT	Error message if job failed
metadata	JSONB	Job-specific metadata
project_id	UUID	Reference to the project this job belongs to (nullable)
current_processing_group	INTEGER	Tracks which execution group is being processed (prevents race conditions, nullable)
restart_count	INTEGER	Counter incremented each time job is restarted, used to detect stale callbacks (default: 0)
started_at	TIMESTAMPTZ	When processing started
completed_at	TIMESTAMPTZ	When processing completed
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who initiated the job

Key Features:

Tracks overall job progress for both AI and manual processing
Polymorphic design for different entity types
Metadata for job-specific data
Can create processing issues during validation steps
Uses current_processing_group for atomic execution group locking to prevent race conditions when multiple AI callbacks complete simultaneously
Uses restart_count to detect and discard stale callbacks from previous job runs when a job is restarted

52. Processing Job Steps Table (`processing_job_steps`)¶

Purpose: Track individual step execution within a job

Use Case Example: Track that OCR completed successfully in 2 seconds, classification identified "Receipt", validation found total mismatch requiring resolution.

Column	Type	Description
id	UUID	Unique identifier
processing_job_id	UUID	Reference to parent job
process_template_step_id	UUID	Reference to template step definition
status	TEXT	Status: pending, ready, in_progress, completed, failed, skipped, not_needed
result_data	JSONB	Output data from this step
request_data	JSONB	Request sent to AI model (sanitized, without base64 data)
error_message	TEXT	Error details if step failed
retry_count	INTEGER	Number of times this step has been retried
duration_seconds	INTEGER	Actual execution duration in seconds
started_at	TIMESTAMPTZ	When step execution started
completed_at	TIMESTAMPTZ	When step execution completed
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Tracks individual step progress for both AI and manual steps
Stores step results for use by subsequent steps
Error tracking for debugging
Supports conditional execution with not_needed status
Manual validation steps can create processing issues

53. Processing Issues Table (`processing_issues`)¶

Purpose: Track validation and processing issues that require human resolution

Use Case Example: When total validation fails, create an issue with context (expected: $875.06, actual: $850.00) and suggested resolution, requiring user to fix before continuing.

Column	Type	Description
id	UUID	Unique identifier
processable_type	TEXT	Type of entity with issue: transactions, budgets, schedules
processable_id	UUID	UUID of the entity with the issue
processing_job_step_id	UUID (nullable)	Optional reference to specific step that detected the issue (CASCADE)
issue_key	TEXT	Machine-readable identifier (e.g., 'total_mismatch', 'justification_needed')
issue_title	TEXT	Human-readable title for UI display
issue_description	TEXT	Detailed description of the issue
severity	processing_issue_severity	Issue severity: critical, high, medium, low
context_data	JSON	Additional context data for the issue stored as JSON (preserves key ordering, unlike jsonb)
status	processing_issue_status	Current status: pending, resolved, ignored
resolution_steps	JSONB (array)	Array of resolution actions tracking multi-step workflows (action history)
resolution_notes	TEXT	Optional notes from the person who resolved the issue
resolved_by_user_id	UUID	User who resolved or ignored the issue
resolved_at	TIMESTAMPTZ	Timestamp when issue was resolved or ignored
submitter_resolvable	BOOLEAN	Whether this issue can be resolved by the submitter (true) or requires approver review (false)
is_current	BOOLEAN	False when issue is from an old document that has been replaced (default: true)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

Flexible issue tracking across all entity types
Links to processing jobs and steps for traceability
Context data stores issue-specific information (JSONB for flexibility)
Multi-step resolution tracking with resolution_steps array (supports workflows like query → response via approval system)
Resolution tracking with user, timestamp, and resolution details
Severity levels for prioritization
Status tracking through resolution lifecycle (pending → resolved/ignored)
Justification requests now use the approval query system instead of direct status changes

Example Context Data Structures:

// Total mismatch
{
  "transaction_total": 875.06,
  "line_items_sum": 850.00,
  "difference": 25.06
}

// Vendor match issue
{
  "field": "billing_entity_name",
  "current_value": "Desert Storm",
  "similar_vendors": [
    {"id": "uuid1", "name": "Desert Storm Equipment"},
    {"id": "uuid2", "name": "Desert Rentals LLC"}
  ]
}

// Format issue
{
  "field": "tax_number",
  "current_value": "92345679",
  "expected_format": "IN followed by 8 digits",
  "suggested_value": "IN92345679"
}

Example Resolution Steps Structure:

// Multi-step workflow: query via approval system → add explanation
[
  {
    "action": "query_sent",
    "timestamp": "2024-01-15T10:30:00Z",
    "user_id": "uuid1",
    "notes": "Sent query via approval system requesting justification"
  },
  {
    "action": "resolved",
    "timestamp": "2024-01-15T14:20:00Z",
    "user_id": "uuid2",
    "data": {
      "additional_explanation": "Camera rental for shoot #42"
    },
    "notes": "Added explanation from approval query response"
  }
]

Note: For detailed processing flow including issue creation and resolution, see PROCESSING_ARCHITECTURE.md and ISSUE_RESOLUTION_SYSTEM.md.

Tagging System Tables¶

54. Tags Table (`tags`)¶

Purpose: Flexible tagging system with polymorphic ownership

Use Case Example: Create global tags like "Important" or "Review", organization-specific tags like "Marketing", or project-specific tags like "Phase 1".

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Display name of the tag
slug	TEXT	URL-safe version of name (auto-generated)
color	TEXT	Hex color code for UI display (e.g., #FF5733)
description	TEXT	Optional description of the tag
owner_id	UUID	ID of the entity that owns this tag (NULL for global tags)
owner_type	TEXT	Type of owner entity (e.g., organizations, projects, entities)
category	tag_category	Tag category: Compliance or Transaction
metadata	JSONB	Additional tag properties (keywords, etc.)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this tag
updated_by_user_id	UUID	User who last updated this tag

Key Features:

Polymorphic ownership - tags can belong to any entity type
Global tags (owner_id = NULL) available system-wide
Scoped tags belong to specific entities
Auto-generated slugs for URL-safe identifiers
Color support for visual differentiation

55. Taggings Table (`taggings`)¶

Purpose: Associate tags with any entity type (polymorphic association)

Use Case Example: Apply the "Important" tag to a transaction, "Review" tag to a budget, or "Phase 1" tag to a schedule item.

Column	Type	Description
id	UUID	Unique identifier
tag_id	UUID	Reference to the tag being applied
taggable_id	UUID	ID of the entity being tagged
taggable_type	TEXT	Type of entity being tagged (e.g., entities, transactions, budgets, projects)
metadata	JSONB	AI-generated metadata including confidence, relevance, and reasoning for the tag assignment
created_at	TIMESTAMPTZ	When the tag was applied
created_by_user_id	UUID	User who applied this tag

Key Features:

Polymorphic association - any entity can be tagged
Unique constraint prevents duplicate tag applications
Tracks who applied each tag
Cascade delete when tag is removed
AI metadata support for storing confidence scores, relevance levels, and reasoning

Export System Tables¶

The export system enables users to download data exports (transactions, compliance documents, etc.) with associated attachments in a ZIP format. Exports are processed asynchronously, stored temporarily in the exports storage bucket, and made available via time-limited signed URLs.

1. Exports Table (`exports`)¶

Purpose: Tracks export requests, their processing status, and download information

Schema:

Column	Type	Description
id	UUID	Unique identifier for the export
user_id	UUID	User who requested the export
project_id	UUID	Project the export is for (nullable for organization-level exports)
organization_id	UUID	Organization the export belongs to
type	export_type	Type of export: `transactions`, `compliance`
status	export_status	Current status: `pending`, `processing`, `completed`, `failed`
file_path	TEXT	Path to the zip file in the exports bucket (e.g., `user_id/transactions_export_timestamp.zip`)
download_url	TEXT	Current signed URL for downloading (valid until expires_at)
file_size	BIGINT	Size of the exported file in bytes
record_count	INTEGER	Number of records included in the export
filters	JSONB	JSON object containing the filters applied during export (for audit trail)
error_message	TEXT	Error details if the export failed
expires_at	TIMESTAMPTZ	When the download link expires (typically 24 hours after completion)
metadata	JSONB	Additional metadata: {download_link_regenerations, last_regenerated_at, etc.}
created_at	TIMESTAMPTZ	When the export was requested
updated_at	TIMESTAMPTZ	Last time the export record was updated
created_by_user_id	UUID	User who created the export (default: auth.uid())
updated_by_user_id	UUID	User who last updated the export (default: auth.uid())

Key Features:

Tracks export lifecycle from request to completion
Stores signed download URLs with expiration times
Supports multiple export types (transactions, compliance, etc.)
Includes audit trail with filters and metadata
Links to organization and optionally to project
Automatic cleanup after expiration

Indexes:

idx_exports_user_id - User's exports
idx_exports_project_id - Project-specific exports
idx_exports_organization_id - Organization exports
idx_exports_status - Query by status
idx_exports_created_at - Order by creation time
idx_exports_expires_at - Find expired exports (partial index where status = 'completed')

RLS Policies:

Users can view their own exports
Users can create exports
Users can update their own exports
Service role has full access

Storage Bucket: exports

Private bucket (requires signed URLs)
File structure: {user_id}/{export_filename}.zip
500MB max file size
Allowed MIME types: application/zip, application/x-zip-compressed, application/octet-stream
RLS policies ensure users can only access their own export files

Notification Templates:

export_ready - Sent when export is complete (in-app + optional email)
export_failed - Sent when export fails (in-app + optional email)

Approval System Tables¶

The approval system provides configurable multi-tier approval workflows per project for different record types (transactions, contracts, documents, etc.). See APPROVAL_SYSTEM.md for comprehensive architecture documentation, flow examples, and business rules.

New Enum Type¶

approval_status: Pending, Approved, Rejected, Skipped, Cancelled, Queried - Status of approval requests and instances. Skipped = workflow auto-skip (tier conditions, same-tier). Cancelled = user-initiated cancellation (document replaced, duplicate detected)

1. Approval Configurations Table (`approval_configurations`)¶

Purpose: Defines that a specific type/subtype combination requires approvals for a project

Use Case Example: Enable approval workflows for all "Invoice" transactions in "Summer Film 2024" project.

Column	Type	Description
id	UUID	Unique identifier
project_id	UUID	FK to projects
organization_id	UUID	FK to organizations (for RLS, denormalized from project)
scope	scope_type	'project' (future: 'organization')
type_category_id	UUID	FK to categories (parent type, e.g., 'transactions')
subtype_category_id	UUID	FK to categories (child subtype, e.g., 'Invoice') - nullable
is_active	BOOLEAN	Whether this configuration is active (default: true)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this configuration (default: auth.uid())
updated_by_user_id	UUID	User who last updated this configuration (default: auth.uid())

Constraints:

UNIQUE (project_id, type_category_id, subtype_category_id)
subtype_category_id.parent_id must equal type_category_id (validated by trigger)

Indexes:

idx_approval_configurations_project_id on project_id
idx_approval_configurations_organization_id on organization_id
idx_approval_configurations_type_category_id on type_category_id
idx_approval_configurations_subtype_category_id on subtype_category_id

2. Approval Tiers Table (`approval_tiers`)¶

Purpose: Defines sequential approval levels within a configuration with optional conditions

Use Case Example: Create Tier 1 (Manager approval for amounts > $100), Tier 2 (Finance Director for amounts > $1000), Tier 3 (CFO for amounts > $5000).

Column	Type	Description
id	UUID	Unique identifier
approval_configuration_id	UUID	FK to approval_configurations
tier_number	INTEGER	Tier order (1, 2, 3...) - evaluated sequentially
name	TEXT	Optional display name for the tier
conditions	JSONB	Conditional rules for when this tier applies (see below)
is_active	BOOLEAN	Whether this tier is active (default: true)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who created this tier (default: auth.uid())
updated_by_user_id	UUID	User who last updated this tier (default: auth.uid())

Conditions JSONB Structure:

{
  "logic": "ANY",
  "rules": [
    {
      "field": "amount",
      "operator": "gt",
      "value": "1000"
    },
    {
      "field": "entity_name",
      "operator": "in",
      "value_list": ["Test LTD", "Acme Corp"]
    }
  ]
}

logic: "ANY" (OR) or "ALL" (AND) - how multiple rules combine
field: Field to evaluate ("amount", "entity_name")
operator: "gt" | "gte" | "lt" | "lte" | "eq" | "neq" | "in" | "not_in"
value: Single value for comparison operators
value_list: Array for "in" / "not_in" operators
If conditions is NULL or empty, tier ALWAYS requires approval

Constraints:

UNIQUE (approval_configuration_id, tier_number)

Indexes:

idx_approval_tiers_approval_configuration_id on approval_configuration_id

3. Approval Tier Approvers Table (`approval_tier_approvers`)¶

Purpose: Links entities (approvers) to approval tiers

Use Case Example: Assign John Smith and Jane Doe as Tier 1 approvers, Finance Director as Tier 2 approver.

Column	Type	Description
id	UUID	Unique identifier
approval_tier_id	UUID	FK to approval_tiers
entity_id	UUID	FK to entities (must have user_id for notifications)
is_active	BOOLEAN	Whether this approver assignment is active (default: true)
created_at	TIMESTAMPTZ	Creation timestamp
created_by_user_id	UUID	User who created this assignment (default: auth.uid())

Validation (application layer):

Entity must have user_id IS NOT NULL
Entity must have active project_relationship for the project

Constraints:

UNIQUE (approval_tier_id, entity_id)

Indexes:

idx_approval_tier_approvers_approval_tier_id on approval_tier_id
idx_approval_tier_approvers_entity_id on entity_id

4. Approval Requests Table (`approval_requests`)¶

Purpose: Tracks a record submitted for approval (one per record per configuration, or soft approvals for records with issues but no config)

Use Case Example: When an Invoice transaction is created and needs approval, an approval_request is created to track it through the workflow. For soft approvals (records with processing issues but no formal approval config), approval_configuration_id is NULL.

Column	Type	Description
id	UUID	Unique identifier
approval_configuration_id	UUID (nullable)	FK to approval_configurations. NULL for soft approvals (records with issues but no formal config)
record_type	TEXT	Table name (e.g., 'transactions')
record_id	UUID	ID of the record being approved
project_id	UUID	FK to projects (denormalized for queries)
organization_id	UUID	FK to organizations (denormalized for RLS)
record_created_by_user_id	UUID	User who created the original record (denormalized for RLS - allows record creators to reply to queries)
current_tier_number	INTEGER	Current tier being evaluated
status	approval_status	Pending, Approved, Rejected, Queried
submitted_by_user_id	UUID	User who submitted (NULL if system/internal)
submitted_by_address	TEXT	Email/phone if internal user submitted
notes	TEXT	Notes from submitter when submitting the record for approval
final_decision_at	TIMESTAMPTZ	When final decision was made
final_decision_by_user_id	UUID	User who made final decision
reject_reason	TEXT	Reason if rejected
reference	TEXT	Human-readable reference for the record (e.g., transaction_code). Populated at submission
escalated_at	TIMESTAMPTZ	When urgent escalation was triggered (NULL if not escalated)
escalated_by_user_id	UUID	FK to users - User who triggered the urgent escalation (NULL if not escalated)
is_soft_approval	BOOLEAN	True if this approval was created due to processing issues without formal approval configuration
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Constraints:

UNIQUE (approval_configuration_id, record_type, record_id) WHERE status <> 'Skipped' AND approval_configuration_id IS NOT NULL - for regular approvals
UNIQUE (record_type, record_id) WHERE status <> 'Skipped' AND approval_configuration_id IS NULL - for soft approvals

Indexes:

idx_approval_requests_record on (record_type, record_id) - lookup by record
idx_approval_requests_project_status on (project_id, status) - project dashboard
idx_approval_requests_approval_configuration_id on approval_configuration_id
idx_approval_requests_organization_id on organization_id
idx_approval_requests_record_created_by_user_id on record_created_by_user_id - RLS for record creators
idx_approval_requests_escalated_by on escalated_by_user_id - FK index for escalation tracking

5. Approval Instances Table (`approval_instances`)¶

Purpose: Individual approval assignments tracking each approver's decision (full audit trail)

Use Case Example: When Tier 1 has two approvers (John and Jane), two approval_instances are created. When John approves, his instance is marked 'Approved' and Jane's is 'Skipped'.

Column	Type	Description
id	UUID	Unique identifier
approval_request_id	UUID	FK to approval_requests
approval_tier_id	UUID (nullable)	FK to approval_tiers. NULL for soft approvals
tier_number	INTEGER	Denormalized for efficient queries
entity_id	UUID	FK to entities (the approver)
user_id	UUID	Denormalized for "my approvals" queries
status	approval_status	Pending, Approved, Rejected, Skipped, Cancelled, Queried
condition_met	BOOLEAN	Whether tier condition was met for this record
decision_at	TIMESTAMPTZ	When decision was made
decision_note	TEXT	Optional note with decision
skipped_reason	TEXT	Why auto-skipped (condition not met, another approver, etc.)
skipped_at	TIMESTAMPTZ	When instance was auto-skipped
cancel_reason	TEXT	Why cancelled (document replaced, duplicate transaction, etc.)
cancelled_at	TIMESTAMPTZ	When instance was cancelled due to user action
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Constraints:

UNIQUE (approval_request_id, approval_tier_id, entity_id)

Indexes:

idx_approval_instances_user_status on (user_id, status) - "My pending approvals"
idx_approval_instances_request on (approval_request_id, tier_number) - request timeline
idx_approval_instances_approval_tier_id on approval_tier_id
idx_approval_instances_entity_id on entity_id

Changes to Existing Tables¶

projects table:

Added approvals_enabled (BOOLEAN, default: false) - Master switch to enable/disable approvals for the entire project

transactions table:

Added reject_reason (TEXT) - Reason for rejection when status is 'Rejected' (from approval workflow)

messages table:

Added approval_request_id (UUID, FK to approval_requests) - Links message to approval request for query/discussion threads
Added index idx_messages_approval_request_id (partial, where approval_request_id IS NOT NULL)

Approval System Views¶

The following views support the approval system (see Database Views section for full documentation):

v_approval_types - Approval types and subtypes from category hierarchy (types are level=1, subtypes are level=2)
v_approval_configuration_summary - Configuration overview with tier/approver counts. Includes type-level configs where subtype_category_id IS NULL
v_approval_tier_details - Detailed tier view with approvers (configuration editor)
v_user_pending_approvals - User's pending approval items with type/subtype info for tabs. Includes tab_label, total_tiers, can_approve, assignment ('mine' | 'lower_tier'), is_soft_approval flag, transaction-specific fields (transaction_reference_number, transaction_date, transaction_due_date, transaction_total, transaction_currency_code, transaction_ai_justification, transaction_billing_entity_name), and record_summary (generic searchable text per record type for "All" tab filtering) via LEFT JOIN to transactions table when record_type='transactions'
v_user_approval_history - User's completed approval instances (Approved, Rejected, Skipped). Includes type/subtype for tabs and is_soft_approval flag
v_user_approval_queries - Approval queries with user_id for filtering. Returns one row per (query, user who can reply). Excludes query poster. Includes is_soft_approval flag
v_approval_request_status - Request progress summary with tier statistics. Includes is_soft_approval flag
v_approval_request_timeline - Full audit trail for a request
v_approval_messages - Discussion thread messages for a request (UI only). Filters by source='Approval Message' to exclude WhatsApp/Email delivery records. Includes parent_id, root_message_id, and origin_message_id for threading and tracing to source channel messages. sender_name is resolved from entity first, falling back to users table when entity_id is null (for approver queries)
v_soft_approval_approvers - Users who can approve soft approvals based on approval:soft:view permission. Returns project_id, entity_id, user_id, organization_id for each eligible user

Approval System Permissions¶

Permission Key	Description	Scope
approval_rule:view	View approval configurations	project
approval_rule:create	Create approval configurations	project
approval_rule:edit	Edit approval configurations	project
approval_rule:delete	Delete approval configurations	project
approval:view	View approval requests	project
approval:approve	Approve/reject/query records	project
approval:can:action:queried	Approve/reject items that are in Queried status	project
approval:escalate:urgent	Skip to highest tier and send urgent notifications	project
approval:soft:view	View and action records with processing issues when no formal approval config	project

6. Notification Action Tokens Table (`notification_action_tokens`)¶

Purpose: Generic secure token system for notification action links (approvals, query responses, exports, etc.). Supports both EMAIL and WHATSAPP channels. Uses metadata.record_ids array for flexible single/bulk record references.

Use Case Example: When sending an approval notification via email or WhatsApp, a token is generated that allows the approver to take action (approve/reject/query) directly from the notification without logging in. Digest notifications can include bulk tokens referencing multiple pending items.

Column	Type	Description
id	UUID	Unique identifier
token	TEXT	Unique cryptographically secure token string (64 hex characters)
action_type	TEXT	Type of action: 'approval_review', 'approval_review_bulk', 'query_response', 'export_download', etc.
user_id	UUID	User the token was issued to (optional, for authorization)
metadata	JSONB	Action context including `record_ids` array: `[{approval_instance_id, approval_request_id}, ...]`
expires_at	TIMESTAMPTZ	When the token expires (typically 7 days from creation)
used_at	TIMESTAMPTZ	When the token was consumed (NULL if unused)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who triggered the token creation (system notifications = NULL)

Constraints:

UNIQUE on token
FK on user_id references users(id) ON DELETE CASCADE

Indexes:

idx_notification_action_tokens_token on token - Token lookup
idx_notification_action_tokens_metadata_record_ids GIN index on (metadata->'record_ids') - Find tokens by record references
idx_notification_action_tokens_expires on expires_at WHERE used_at IS NULL - Cleanup of unused expired tokens

Security:

Tokens are one-time use (marked as used after successful action)
Default expiration: 7 days (168 hours)
No authentication required - token acts as the auth credential
RLS policies allow service role access only (tokens are managed server-side)

Action Types:

Action Type	Metadata.record_ids Structure	Purpose
approval_review	`[{approval_instance_id, approval_request_id}]`	Approver reviews a single submission
approval_review_bulk	`[{approval_instance_id, approval_request_id}, ...]`	Approver reviews multiple items from digest
query_response	`[{approval_request_id}]`	Submitter responds to a query

Integration System Tables¶

1. Integration Providers Table (`integration_providers`)¶

Purpose: Registry of available third-party integrations. Developer seed data only.

Use Case Example: Stores provider definitions for QuickBooks, Companies House, and HMRC with their authentication configuration and API endpoints.

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
code	TEXT	Machine-readable unique key (e.g., quickbooks, companies_house, hmrc)
name	TEXT	Human-readable display name
description	TEXT	Description of what this integration provides
logo_attachment_id	UUID	FK to attachments table for provider logo (nullable, ON DELETE SET NULL)
auth_method	ENUM	Authentication method: integration_auth_method
credential_scope	ENUM	Whether credentials are global or per-project: integration_credential_scope
oauth_config	JSONB	OAuth configuration (URLs, scopes, grant_type). NULL for non-OAuth providers
auth_config	JSONB	Configuration template for non-OAuth auth (field definitions, NOT actual secrets)
auto_enable_for_country	BOOLEAN	Whether to automatically enable this integration when a project matches a supported country
base_url	TEXT	Production API base URL
sandbox_base_url	TEXT	Sandbox/development API base URL. Used when NODE_ENV !== production
documentation_url	TEXT	URL to external API documentation
developer_portal_url	TEXT	URL to the developer portal for creating OAuth apps
developer_portal_label	TEXT	Custom label for the developer portal link
setup_instructions	JSONB	Array of setup steps [{label, url}] for credential configuration
entity_urls	JSONB	Map of entity type to production URL template (e.g., {account, bill, vendor}). Supports {companyId}, {entityId} placeholders
sandbox_entity_urls	JSONB	Map of entity type to sandbox URL template. Same structure as entity_urls. Falls back to entity_urls when NULL
provider_type	ENUM	Provider category: integration_provider_type (accounting, government)
is_active	BOOLEAN	Whether this provider is currently available (default: true)
display_order	INTEGER	Display order in the UI (default: 0)
created_at	TIMESTAMPTZ	Timestamp when the provider was created
updated_at	TIMESTAMPTZ	Timestamp when the provider was last updated

Constraints: uq_integration_providers_code UNIQUE(code)

Indexes: idx_integration_providers_code (code), idx_integration_providers_active (is_active), idx_integration_providers_logo (logo_attachment_id)

RLS: Enabled. Authenticated users can SELECT all providers (read-only).

Seed Data: Companies House (api_key, global, auto-enable for GB), HMRC (oauth2, global, auto-enable for GB), QuickBooks (oauth2, per_project, manual enable)

2. Integration Provider Countries Table (`integration_provider_countries`)¶

Purpose: Junction table linking integration providers to supported countries.

Use Case Example: Companies House and HMRC are linked to GB, while QuickBooks has no country restrictions.

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
integration_provider_id	UUID	FK to integration_providers (ON DELETE CASCADE)
country_code	TEXT	FK to countries table
created_at	TIMESTAMPTZ	Timestamp when the link was created

Constraints: uq_provider_country UNIQUE(integration_provider_id, country_code)

Indexes: idx_ipc_integration_provider (integration_provider_id), idx_provider_countries_country (country_code)

RLS: Enabled. Authenticated users can SELECT all records (read-only).

3. Integration Features Table (`integration_features`)¶

Purpose: Sync features/points per integration provider. Self-referencing for hierarchy (e.g., Transactions > Expenses). Developer seed data.

Use Case Example: QuickBooks has features like "Chart of Accounts" (inbound, on_link), "Vendors" (outbound, on_status_change), and "Transactions > Expenses" (hierarchical, outbound).

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
integration_provider_id	UUID	FK to integration_providers (ON DELETE CASCADE)
parent_integration_feature_id	UUID	FK to integration_features for hierarchy. NULL = top-level feature (ON DELETE CASCADE)
code	TEXT	Machine-readable code, unique within provider
name	TEXT	Human-readable display name
description	TEXT	Description of what this feature syncs
sync_direction	ENUM	Direction of data flow: sync_direction (default: 'bidirectional')
default_trigger	ENUM	Default trigger type when feature is enabled: sync_trigger_type (default: 'on_manual')
default_trigger_config	JSONB	Default configuration for the trigger (e.g., {"entity": "transaction", "status": "Approved"})
supported_triggers	sync_trigger_type[]	Array of allowed trigger types for this feature (default: ARRAY['on_manual'])
run_on_link	BOOLEAN	Whether to run this feature immediately when integration is linked (one-time initial sync, default: false)
api_endpoint	TEXT	API endpoint path (appended to provider base_url). Supports {{companyNumber}} and {{targetVrn}} placeholders
feature_category	ENUM	integration_feature_category ('tax_validation', 'company_validation', 'company_details'). NULL for non-categorized features
is_active	BOOLEAN	Whether this feature is currently available (default: true)
display_order	INTEGER	Display order in the UI (default: 0)
created_at	TIMESTAMPTZ	Timestamp when the feature was created
updated_at	TIMESTAMPTZ	Timestamp when the feature was last updated

Constraints: uq_integration_features_provider_code UNIQUE(integration_provider_id, code)

Indexes: idx_if_integration_provider (integration_provider_id), idx_if_parent_integration_feature (parent_integration_feature_id), idx_integration_features_active (is_active)

RLS: Enabled. Authenticated users can SELECT all features (read-only).

3b. Integration Feature Dependencies Table (`integration_feature_dependencies`)¶

M:N dependency relationships between integration features within the same provider. A feature cannot be enabled unless all its prerequisites are enabled.

Column	Type	Description
id	UUID (PK)	Unique identifier for the dependency
integration_feature_id	UUID	FK to integration_features — the dependent feature (ON DELETE CASCADE)
depends_on_integration_feature_id	UUID	FK to integration_features — the prerequisite feature (ON DELETE CASCADE)
created_at	TIMESTAMPTZ	Timestamp when the dependency was created (DEFAULT NOW())

Constraints: uq_feature_dependency UNIQUE(integration_feature_id, depends_on_integration_feature_id), chk_no_self_dependency CHECK(integration_feature_id != depends_on_integration_feature_id)

Indexes: idx_ifd_integration_feature (integration_feature_id), idx_ifd_depends_on_integration_feature (depends_on_integration_feature_id)

RLS: Enabled. Authenticated and anonymous users can SELECT (read-only reference data).

Seed Data (QuickBooks):

qb_expenses depends on qb_vendors
qb_expenses depends on qb_chart_of_accounts
qb_bills depends on qb_vendors
qb_bills depends on qb_chart_of_accounts

4. Project Integrations Table (`project_integrations`)¶

Purpose: Tracks which integrations are enabled per project and their connection status.

Use Case Example: A UK film project auto-enables Companies House and HMRC on creation. QuickBooks is manually linked when the production accountant connects their account.

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
project_id	UUID	FK to projects (ON DELETE CASCADE)
integration_provider_id	UUID	FK to integration_providers (ON DELETE RESTRICT)
organization_id	UUID	FK to organizations, used for RLS scoping (ON DELETE CASCADE)
connection_status	ENUM	Current connection status: integration_connection_status (default: 'Disconnected')
is_auto_enabled	BOOLEAN	Whether this integration was automatically enabled based on project country
is_active	BOOLEAN	Soft disable flag (default: true)
connected_at	TIMESTAMPTZ	When the integration was last connected
disconnected_at	TIMESTAMPTZ	When the integration was last disconnected
external_account_id	TEXT	Account ID in the external system (e.g., QuickBooks realm_id)
external_account_name	TEXT	Account name in the external system
external_metadata	JSONB	Provider-specific metadata
connected_by_user_id	UUID	FK to auth.users — User who connected this integration
disconnected_by_user_id	UUID	FK to auth.users — User who disconnected this integration
created_at	TIMESTAMPTZ	Timestamp when the record was created
updated_at	TIMESTAMPTZ	Timestamp when the record was last updated
created_by_user_id	UUID	User who created this record (default: auth.uid())
updated_by_user_id	UUID	User who last updated this record (default: auth.uid())

Constraints: uq_project_integration UNIQUE(project_id, integration_provider_id)

Indexes: idx_project_integrations_project (project_id), idx_pi_integration_provider (integration_provider_id), idx_project_integrations_organization (organization_id), idx_project_integrations_status (connection_status), idx_project_integrations_active (is_active), idx_project_integrations_connected_by_user_id (connected_by_user_id), idx_project_integrations_disconnected_by_user_id (disconnected_by_user_id)

RLS: Enabled. SELECT, INSERT, UPDATE, DELETE for authenticated users with project access via v_user_accessible_projects.

5. Integration Credentials Table (`integration_credentials`)¶

Purpose: Stores vault secret IDs for integration credentials. NEVER stores plaintext tokens or passwords.

Use Case Example: After OAuth2 flow with QuickBooks, the access and refresh token vault secret UUIDs are stored here alongside token expiry metadata.

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
project_integration_id	UUID	FK to project_integrations. NULL for global credentials (ON DELETE CASCADE)
integration_provider_id	UUID	FK to integration_providers (ON DELETE RESTRICT)
credential_scope	ENUM	Whether this credential is global or per-project: integration_credential_scope
access_token_secret_id	UUID	Vault secret UUID for OAuth2 access token
refresh_token_secret_id	UUID	Vault secret UUID for OAuth2 refresh token
client_id_secret_id	UUID	Vault secret UUID for OAuth2 client_id
client_secret_secret_id	UUID	Vault secret UUID for OAuth2 client_secret
token_expires_at	TIMESTAMPTZ	When the access token expires
token_refreshed_at	TIMESTAMPTZ	When the token was last refreshed
token_scopes	TEXT[]	Granted OAuth scopes
api_key_secret_id	UUID	Vault secret UUID for API key
username_secret_id	UUID	Vault secret UUID for basic auth username
password_secret_id	UUID	Vault secret UUID for basic auth password
additional_secrets	JSONB	Maps field names to vault secret UUIDs for extensibility
name	TEXT	Human-readable name for the credential set (e.g., "Ecobride QuickBooks")
can_be_shared	BOOLEAN	Whether credential can be copied to other projects in same org (default: true)
is_active	BOOLEAN	Whether this credential is currently active (default: true)
created_at	TIMESTAMPTZ	Timestamp when the record was created
updated_at	TIMESTAMPTZ	Timestamp when the record was last updated
created_by_user_id	UUID	User who created this record (default: auth.uid())
updated_by_user_id	UUID	User who last updated this record (default: auth.uid())

Indexes: idx_integration_credentials_project_integration (project_integration_id), idx_ic_integration_provider (integration_provider_id), uq_integration_credentials_global_active UNIQUE(integration_provider_id) WHERE credential_scope = 'global' AND is_active = true

RLS: Enabled. Authenticated users can SELECT limited columns (id, project_integration_id, integration_provider_id, credential_scope, is_active, name, can_be_shared, and vault secret UUID references) for credential existence checks. Actual secret values are only accessible via service_role through the Vault. All credential mutations must go through service_role.

6. Project Integration Features Table (`project_integration_features`)¶

Purpose: Tracks which sync features are enabled per project integration with trigger configuration.

Use Case Example: A project's QuickBooks integration has "Vendors" feature enabled with on_status_change trigger configured to fire when entity status changes to "Active".

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
project_integration_id	UUID	FK to project_integrations (ON DELETE CASCADE)
integration_feature_id	UUID	FK to integration_features (ON DELETE RESTRICT)
is_enabled	BOOLEAN	Whether this feature is currently enabled (default: true)
sync_trigger	ENUM	Primary trigger type (copied from feature default, user-overridable): sync_trigger_type
trigger_config	JSONB	Trigger parameters (e.g., {"entity": "transaction", "status": "Approved"} for on_status_change)
last_sync_at	TIMESTAMPTZ	When this feature was last synced
last_sync_status	ENUM	Status of the last sync operation: sync_status
next_scheduled_sync_at	TIMESTAMPTZ	When the next scheduled sync should run (for on_schedule triggers)
is_active	BOOLEAN	Soft disable flag (default: true)
created_at	TIMESTAMPTZ	Timestamp when the record was created
updated_at	TIMESTAMPTZ	Timestamp when the record was last updated
created_by_user_id	UUID	User who created this record (default: auth.uid())
updated_by_user_id	UUID	User who last updated this record (default: auth.uid())

Constraints: uq_project_integration_feature UNIQUE(project_integration_id, integration_feature_id)

Indexes: idx_pif_project_integration (project_integration_id), idx_pif_integration_feature (integration_feature_id), idx_pif_next_sync (next_scheduled_sync_at) WHERE next_scheduled_sync_at IS NOT NULL, idx_pif_enabled (is_enabled)

RLS: Enabled. SELECT, INSERT, UPDATE for authenticated users with project access via project_integrations -> v_user_accessible_projects.

7. Integration Sync Logs Table (`integration_sync_logs`)¶

Purpose: Audit trail for all sync operations. Append-mostly table.

Use Case Example: Records every sync operation including trigger type, direction, record counts, errors, and duration for troubleshooting and monitoring.

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
project_integration_id	UUID	FK to project_integrations (ON DELETE CASCADE)
project_integration_feature_id	UUID	FK to project_integration_features. NULL for full-integration syncs (ON DELETE SET NULL)
sync_trigger	ENUM	What triggered this sync: sync_trigger_type
sync_direction	ENUM	Direction of data flow: sync_direction
status	ENUM	Current status: sync_status (default: 'pending')
started_at	TIMESTAMPTZ	When the sync started (default: NOW())
completed_at	TIMESTAMPTZ	When the sync completed
duration_ms	INTEGER	Duration of the sync in milliseconds
records_processed	INTEGER	Total records processed (default: 0)
records_created	INTEGER	Records created during sync (default: 0)
records_updated	INTEGER	Records updated during sync (default: 0)
records_failed	INTEGER	Records that failed to sync (default: 0)
records_skipped	INTEGER	Records skipped during sync (default: 0)
error_message	TEXT	Error message if sync failed
error_details	JSONB	Detailed error information (stack trace, API response)
webhook_event_type	TEXT	Webhook event type that triggered this sync
webhook_payload_hash	TEXT	Hash of webhook payload for deduplication
request_metadata	JSONB	Sanitized request details
response_metadata	JSONB	Response summary
triggered_by_user_id	UUID	User who triggered this sync. NULL for system triggers (FK to users)
created_at	TIMESTAMPTZ	Timestamp when the record was created

Indexes: idx_sync_logs_project_integration (project_integration_id), idx_sync_logs_feature (project_integration_feature_id), idx_sync_logs_status (status), idx_sync_logs_started_at (started_at), idx_sync_logs_triggered_by (triggered_by_user_id)

RLS: Enabled. SELECT for authenticated users with project access via project_integrations -> v_user_accessible_projects. Writes via service_role only.

8. Integration Entity Mappings Table (`integration_entity_mappings`)¶

Purpose: Maps Delta records to external system records for sync continuity.

Use Case Example: A Delta entity (supplier) is mapped to a QuickBooks Vendor with external_id "123", allowing bidirectional updates and conflict detection via sync_hash.

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
project_integration_id	UUID	FK to project_integrations (ON DELETE CASCADE)
entity_type	TEXT	Delta table/entity name (e.g., entities, transactions, transaction_items)
entity_id	UUID	Delta record UUID
external_id	TEXT	ID in the external system
external_type	TEXT	Type in the external system (e.g., Customer, Invoice, Account)
external_metadata	JSONB	Additional reconciliation data from external system
last_synced_at	TIMESTAMPTZ	When this mapping was last synced
sync_token	TEXT	External system version token (e.g., QuickBooks SyncToken). Per-record tracking
sync_hash	TEXT	Hash of last synced data for change detection
is_out_of_sync	BOOLEAN	True when our sync_token is stale compared to external system (default: false)
created_at	TIMESTAMPTZ	Timestamp when the mapping was created
updated_at	TIMESTAMPTZ	Timestamp when the mapping was last updated

Constraints: uq_entity_mapping_internal UNIQUE(project_integration_id, entity_type, entity_id), uq_entity_mapping_external UNIQUE(project_integration_id, external_id, external_type)

Indexes: idx_entity_mappings_project_integration (project_integration_id), idx_entity_mappings_entity (entity_type, entity_id), idx_entity_mappings_external (external_id, external_type), idx_entity_mappings_out_of_sync (is_out_of_sync) WHERE is_out_of_sync = true

RLS: Enabled. SELECT for authenticated users with project access via project_integrations -> v_user_accessible_projects.

9. Integration Reference Data Table (`integration_reference_data`)¶

Purpose: Generic table for reference data pulled from integrations (Chart of Accounts, Tax Codes, Classes, etc.).

Use Case Example: After connecting QuickBooks, the Chart of Accounts is pulled and stored here with reference_type = 'chart_of_accounts', enabling line item categorization in Delta.

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
project_integration_id	UUID	FK to project_integrations (ON DELETE CASCADE)
reference_type	TEXT	Type of reference data (e.g., chart_of_accounts, tax_code, class)
external_id	TEXT	ID in the external system
code	TEXT	Account/tax code (e.g., 4000, VAT20)
name	TEXT	Display name (e.g., Sales Revenue, Standard Rate VAT)
description	TEXT	Description of the reference data entry
parent_external_id	TEXT	Parent external ID for hierarchical data (e.g., chart of accounts parent)
category	TEXT	Classification (e.g., Revenue, Expense, Asset, Liability)
is_active	BOOLEAN	Whether this entry is active in the external system (default: true)
metadata	JSONB	Type-specific additional fields as JSONB
sync_token	TEXT	External system version token
last_synced_at	TIMESTAMPTZ	When this entry was last synced
created_at	TIMESTAMPTZ	Timestamp when the record was created
updated_at	TIMESTAMPTZ	Timestamp when the record was last updated

Constraints: uq_reference_data UNIQUE(project_integration_id, reference_type, external_id)

Indexes: idx_reference_data_project_integration (project_integration_id), idx_reference_data_type (reference_type), idx_reference_data_active (is_active), idx_reference_data_parent (parent_external_id), idx_reference_data_category (category)

RLS: Enabled. SELECT for authenticated users with project access via project_integrations -> v_user_accessible_projects.

10. Tax Rate Mappings Table (`tax_rate_mappings`)¶

Purpose: Maps internal tax rates to external integration tax codes. Provider-agnostic — one mapping per tax rate per project integration.

Use Case Example: Map Delta's "Standard Rate 20%" tax rate to QuickBooks Online's "20.0% S" tax code for a specific project integration, enabling correct tax code assignment when syncing transactions.

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
tax_rate_id	UUID	FK to tax_rates (ON DELETE CASCADE)
integration_reference_data_id	UUID	FK to integration_reference_data — the external tax code (ON DELETE CASCADE)
project_integration_id	UUID	FK to project_integrations — scopes the mapping (ON DELETE CASCADE)
created_at	TIMESTAMPTZ	Timestamp when the mapping was created
updated_at	TIMESTAMPTZ	Timestamp when the mapping was last updated
created_by_user_id	UUID	User who created the mapping (default: auth.uid()). FK to auth.users
updated_by_user_id	UUID	User who last updated the mapping (default: auth.uid()). FK to auth.users

Constraints: uq_tax_rate_mapping UNIQUE(tax_rate_id, project_integration_id)

Indexes: idx_tax_rate_mappings_tax_rate_id, idx_tax_rate_mappings_reference_data_id, idx_tax_rate_mappings_project_integration_id, idx_tax_rate_mappings_created_by_user_id, idx_tax_rate_mappings_updated_by_user_id

RLS: Enabled. SELECT, INSERT, UPDATE, DELETE for authenticated users with project access via project_integrations -> v_user_accessible_projects.

Database Views¶

1. User Permissions View (`v_user_permissions`)¶

Purpose: Shows all permissions a user has across all organizations 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

2. User Organization Permissions View (`v_user_organization_permissions`)¶

Purpose: Shows all permissions a user has for each organization

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

View Definition:

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

3. 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

4. User Accessible Organizations View (`v_user_accessible_organizations`)¶

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

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

View Definition:

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

5. Organization Members View (`v_organization_members`)¶

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

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

View Definition:

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

6. 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 in DEV-290
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

6. 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

7. 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, organization_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

8. 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, organization_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

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

8. 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

9. 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

10. 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

10a. 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)
Returns essential fields: id, processing_job_id, record_type, record_id, description, severity, status, resolution_type, resolution_data, created_at
Security invoker enabled to respect RLS policies
Used by approval submission logic to detect blocking issues

11. 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

12. 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

13. 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

14. 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

15. 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, organization_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

16. 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, organization_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

17. 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 entities to get team members
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
organization_id	UUID	Organization 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

11. 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, organizations, 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)
Organization fields (flattened with prefix):
org_id, organization_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

14. Compliance Capture View (`v_compliance_capture`)¶

Purpose: Aggregated view of root messages with entity, project, and organization 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 organization 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 entities, projects, organizations, tags, and taggings tables
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 Organization fields:
project_code, project_title, organization_code, organization_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

15. 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 and bank fields
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

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, 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
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

16. 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 and bank fields
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

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
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

17. Transactions View (`v_transactions`)¶

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 + 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.)
Computed field: balance (total minus sum of paid reconciliation amounts)

18. 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 transactions with status = 'Approved'
LEFT JOINs a LATERAL subquery on payment_reconciliations + payments (status = 'Paid') to compute balance
LEFT JOINs a subquery on payment_reconciliations + payments (status = 'Scheduled') to compute amount_scheduled
Computes balance = COALESCE(total, 0) - COALESCE(total_paid, 0)
Computes schedulable_amount = balance - amount_scheduled
Filters out rows where schedulable_amount <= 0
Security invoker enabled to respect RLS policies

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_project_relationship_id
Computed fields: balance (total minus paid), amount_scheduled (sum of reconciliation amounts for Scheduled payments), schedulable_amount (balance minus scheduled)

19. 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 entities for full entity details including bank fields
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
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_organization_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
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_organization_id, project_is_active, project_country_code — note: project_status was removed in DEV-290 as the status column was dropped from the projects table

20. 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 payment_methods for method details
Joins with v_payment_methods for display_label
Joins with users for created-by and updated-by user details
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

21. 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
LEFT JOINs a pre-aggregated subquery (GROUP BY budget_item_daily_allocation_id) on budget_allocation_transaction_items to compute actual_total — previously used LEFT JOIN LATERAL which re-evaluated the sum per row and caused Supabase instability (DEV-487)
Computes total, actual_total, balance, and estimated_cost_balance
Security invoker enabled to respect RLS policies
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

Returned Fields:

All base fields from budget_item_daily_allocations
Computed fields:
total = quantity * rate
actual_total = SUM(budget_allocation_transaction_items.amount)
balance = total - actual_total
estimated_cost_balance = estimated_cost - actual_total

22. 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
LEFT JOINs a pre-aggregated subquery (GROUP BY budget_item_id) over budget_item_daily_allocations, which itself pulls from a pre-aggregated subquery on budget_allocation_transaction_items — replaces nested LEFT JOIN LATERAL chain (DEV-487)
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

23. 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:

Uses recursive CTE (header_tree) to walk the parent-child hierarchy to find all descendants at any depth
Aggregates items from the entire subtree for each header by joining to v_budget_items (which already contains optimized per-item aggregates) rather than re-deriving them with nested LATERAL joins (DEV-487)
Security invoker enabled to respect RLS policies

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

24. 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 a pre-aggregated subquery (GROUP BY budget_id) that joins budget_headers to v_budget_items, reusing v_budget_items' optimized aggregation instead of a nested LATERAL chain (DEV-487)
Security invoker enabled to respect RLS policies

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

25. 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 → transactions
LEFT JOINs entities for supplier name
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)

26. 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 → transactions
LEFT JOINs entities for supplier name
WHERE transaction_items.budget_item_id IS NOT NULL
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)

27. 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, organization_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

28. 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

29. Shareable Integration Credentials View (`v_shareable_integration_credentials`)¶

Purpose: Shows per-project credentials that can be reused across projects in the same organization. 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
organization_id - project_integrations.organization_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

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

31. 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'
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 organization 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_deleted() - Soft deletes users record when auth user is deleted
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, organizations) from their names
set_project_code() - Trigger function that automatically generates project_code using generate_entity_code if not provided
set_organization_code() - Trigger function that automatically generates organization_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
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)
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

Messaging System Functions¶

get_or_create_conversation_instance() - Gets active conversation instance or creates new one if needed
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) - Checks rate limits and abuse patterns before allowing message creation
Returns: allowed (boolean), reason (text), reset_at (timestamp), limit_type (text)
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

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 (DEV-485 sibling fix). 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() - Validates that total allocated payment amounts don't exceed transaction total
Trigger function that executes BEFORE INSERT OR UPDATE on payment_reconciliations table
Calculates sum of all reconciliation amounts for the transaction (including new/updated amount)
Raises exception if total reconciliation amount exceeds the transaction total
Ensures payment allocations are valid and prevents over-allocation
mark_transaction_paid_on_payment() - Marks transactions as Paid when fully paid via reconciliations
Trigger function that executes AFTER UPDATE on payments table
Only runs when payment status changes to 'Paid'
Checks all transactions linked to the payment via payment_reconciliations
Computes balance (total - sum of paid reconciliation amounts) for each linked transaction
Sets transaction status to 'Paid' when computed balance reaches 0 and transaction is currently Approved
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 reconciliations of Paid payments
If total paid >= transaction total, sets status to 'Paid' instead of 'Approved'
Handles edge case where payments were paid before transaction was approved

Entity Deduplication Functions¶

find_matching_business_entity(p_organization_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

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)

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 (DEV-485)
Input: JSONB array of {allocation_id, estimated_cost?, estimated_quantity?, estimated_cost_note?}
Returns: SETOF budget_item_daily_allocations (the updated rows)
SECURITY DEFINER with search_path = '', GRANT EXECUTE TO authenticated
Performs ONE permission check covering ALL allocations against v_user_project_permissions (budget:edit or budget:estimated_costs:manage), then runs ONE bulk UPDATE statement
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
Sets updated_by_user_id and updated_at automatically

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) - Marks transactions as Paid when payment becomes Paid and computed balance reaches 0
Transaction approval trigger (trg_check_transaction_paid_on_approval) - Auto-marks transaction as Paid on approval if already fully paid via reconciliations
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

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.

Security Features¶

Row Level Security (RLS):

All tables containing user data have RLS enabled
All RLS policies explicitly target TO authenticated — the anon role has zero grants and zero policies on public tables
Anon-facing features (phone existence check, notification token validation) use createServiceRoleClient() server-side, bypassing RLS entirely
Service role bypasses RLS — no service_role-specific policies are needed

RLS Policy Design by Table Category:

User data tables (projects, transactions, budgets, etc.): Full CRUD policies scoped to authenticated users via project membership or ownership checks
Reference/config data tables (SELECT-only for authenticated, writes via service_role): tax_rates, tax_schemes, departments, countries, currencies, languages, role_definitions, role_definition_categories, permission_roles, permission_role_links, permissions, menu_items, menu_item_categories, categories, category_groups, notification_template_categories, rate_limit_configs, integration_feature_dependencies
Addresses table: SELECT + INSERT + UPDATE only — DELETE intentionally omitted to preserve address history for audit trails and linked records (addresses may be referenced by entities, transactions, etc.)

Why some tables lack INSERT/UPDATE/DELETE policies: Tables classified as reference or configuration data are pre-seeded via migrations and managed exclusively through service_role operations. The absence of write policies for authenticated users is intentional — it prevents client-side mutation of system reference data. Service role bypasses RLS entirely, so no additional policies are needed for administrative writes.

Soft Delete Pattern:

User records are never hard deleted
Email/phone appended with timestamp on deletion
Person records preserved for historical data

Database Schema Documentation¶

Table of Contents¶

Enumerated Types¶

Entity Types¶

User & Settings¶

Projects & Production¶

Financial & Documents¶

Communication¶

System & Security¶

AI Processing¶

Tagging¶

Export System¶

Access Control¶

Menu¶

Integrations¶

Attachments¶

Core Tables¶

1. Entities Table (entities)¶

2. Users Table (users)¶

3. User Settings Table (user_settings)¶

Project Management Tables¶

4. Projects Table (projects)¶

5. Organizations Table (organizations)¶

6. User Accesses Table (user_accesses)¶

7. Role Definitions Table (role_definitions)¶

8. Project Relationships Table (project_relationships)¶

Permission System Tables¶

9. Permissions Table (permissions)¶

10. Permission Roles Table (permission_roles)¶

11. Permission Role Links Table (permission_role_links)¶

Navigation System Tables¶

12. Menu Items Table (menu_items)¶

Internationalization Tables¶

13. Countries Table (countries)¶

14. Currencies Table (currencies)¶

15. Languages Table (languages)¶

Category System Tables¶

16. Category Groups Table (category_groups)¶

17. Categories Table (categories)¶

18. Project Categories Table (project_categories)¶

19. Role Definition Categories Table (role_definition_categories)¶

20. Menu Item Categories Table (menu_item_categories)¶

Attachment System Tables¶

21. Attachments Table (attachments)¶

22. Entity Attachments Table (entity_attachments)¶

23. Project Relationship Attachments Table (project_relationship_attachments)¶

Financial Management Tables¶

17. Departments Table (departments)¶

18. Transactions Table (transactions)¶

19. Transaction Items Table (transaction_items)¶

20. Payment Methods Table (payment_methods)¶

21. Payments Table (payments)¶

22. Payment Reconciliations Table (payment_reconciliations)¶

23. Payment Method Assignments Table (payment_method_assignments)¶

Production Phases Permissions¶

Payment System Permissions¶

Tax Configuration Tables¶

23. Tax Schemes Table (tax_schemes)¶

24. Tax Rates Table (tax_rates)¶

Address Management Tables¶

25. Addresses Table (addresses)¶

Budget Management Tables¶

26. Budgets Table (budgets)¶

27. Budget Headers Table (budget_headers)¶

28. Budget Items Table (budget_items)¶

28a. Budget Item Daily Allocations Table (budget_item_daily_allocations)¶

28b. Budget Allocation Transaction Items Table (budget_allocation_transaction_items)¶

Schedule Management Tables¶

29. Schedules Table (schedules)¶

30. Production Phases Table (production_phases)¶

31. Production Days Table (production_days)¶

Notification System Tables¶

32. Notification Senders Table (notification_senders)¶

33. Notification Template Categories Table (notification_template_categories)¶

34. Notification Templates Table (notification_templates)¶

35. Notifications Table (notifications)¶

36. Notification Deliveries Table (notification_deliveries)¶

37. Notification Attachments Table (notification_attachments)¶

Messaging System Tables¶

38. Conversations Table (conversations)¶

1. Entities Table (`entities`)¶

2. Users Table (`users`)¶

3. User Settings Table (`user_settings`)¶

4. Projects Table (`projects`)¶

5. Organizations Table (`organizations`)¶

6. User Accesses Table (`user_accesses`)¶

7. Role Definitions Table (`role_definitions`)¶

8. Project Relationships Table (`project_relationships`)¶

9. Permissions Table (`permissions`)¶

10. Permission Roles Table (`permission_roles`)¶

11. Permission Role Links Table (`permission_role_links`)¶

12. Menu Items Table (`menu_items`)¶

13. Countries Table (`countries`)¶

14. Currencies Table (`currencies`)¶

15. Languages Table (`languages`)¶

16. Category Groups Table (`category_groups`)¶

17. Categories Table (`categories`)¶

18. Project Categories Table (`project_categories`)¶

19. Role Definition Categories Table (`role_definition_categories`)¶

20. Menu Item Categories Table (`menu_item_categories`)¶

21. Attachments Table (`attachments`)¶

22. Entity Attachments Table (`entity_attachments`)¶

23. Project Relationship Attachments Table (`project_relationship_attachments`)¶

17. Departments Table (`departments`)¶

18. Transactions Table (`transactions`)¶

19. Transaction Items Table (`transaction_items`)¶

20. Payment Methods Table (`payment_methods`)¶

21. Payments Table (`payments`)¶

22. Payment Reconciliations Table (`payment_reconciliations`)¶

23. Payment Method Assignments Table (`payment_method_assignments`)¶

23. Tax Schemes Table (`tax_schemes`)¶

24. Tax Rates Table (`tax_rates`)¶

25. Addresses Table (`addresses`)¶

26. Budgets Table (`budgets`)¶

27. Budget Headers Table (`budget_headers`)¶

28. Budget Items Table (`budget_items`)¶

28a. Budget Item Daily Allocations Table (`budget_item_daily_allocations`)¶

28b. Budget Allocation Transaction Items Table (`budget_allocation_transaction_items`)¶

29. Schedules Table (`schedules`)¶

30. Production Phases Table (`production_phases`)¶

31. Production Days Table (`production_days`)¶

32. Notification Senders Table (`notification_senders`)¶

33. Notification Template Categories Table (`notification_template_categories`)¶

34. Notification Templates Table (`notification_templates`)¶

35. Notifications Table (`notifications`)¶

36. Notification Deliveries Table (`notification_deliveries`)¶

37. Notification Attachments Table (`notification_attachments`)¶

38. Conversations Table (`conversations`)¶

39. Conversation Instances Table (`conversation_instances`)¶

40. Messages Table (`messages`)¶

41. Message Attachments Table (`message_attachments`)¶

42. Conversation Summaries Table (`conversation_summaries`)¶

43. Rate Limit Configs Table (`rate_limit_configs`)¶

44. Abuse Patterns Table (`abuse_patterns`)¶

45. Abuse Action Histories Table (`abuse_actions`)¶

46. AI Functions Table (`ai_functions`)¶

47. AI Prompts Table (`ai_prompts`)¶

48. AI Prompt Functions Table (`ai_prompt_functions`)¶

49. Process Templates Table (`process_templates`)¶

50. Process Template Steps Table (`process_template_steps`)¶

51. Processing Jobs Table (`processing_jobs`)¶

52. Processing Job Steps Table (`processing_job_steps`)¶

53. Processing Issues Table (`processing_issues`)¶

54. Tags Table (`tags`)¶

55. Taggings Table (`taggings`)¶

1. Exports Table (`exports`)¶

1. Approval Configurations Table (`approval_configurations`)¶

2. Approval Tiers Table (`approval_tiers`)¶

3. Approval Tier Approvers Table (`approval_tier_approvers`)¶

4. Approval Requests Table (`approval_requests`)¶

5. Approval Instances Table (`approval_instances`)¶

6. Notification Action Tokens Table (`notification_action_tokens`)¶

1. Integration Providers Table (`integration_providers`)¶

2. Integration Provider Countries Table (`integration_provider_countries`)¶

3. Integration Features Table (`integration_features`)¶

3b. Integration Feature Dependencies Table (`integration_feature_dependencies`)¶

4. Project Integrations Table (`project_integrations`)¶

5. Integration Credentials Table (`integration_credentials`)¶

6. Project Integration Features Table (`project_integration_features`)¶

7. Integration Sync Logs Table (`integration_sync_logs`)¶