Financial Management Tables

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

Transactions (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	Per-project transaction code in format TRX-000001, auto-generated on creation (numbered per project; unique on (project_id, transaction_code))
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, Paid, or On Hold (foreign currency with no project rate)
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)
expense_project_relationship_id	UUID	Person an Expense relates to — snapshot of payment_method's assignee at matching; not updated if assignment changes. Nullable (FK to project_relationships)
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)
reject_reason	TEXT	Reason for rejection when status is 'Rejected' (from approval workflow)
exchange_rate_id	UUID	FK to the immutable project_currency_rates version this transaction was processed under — decision-time audit link only. Amounts convert live in views from the currently-active rate (a correction recomputes every figure); this only records which rate version the decision used. Read the rate number via the join. NULL when no conversion applied
line_item_mode	ENUM	line_item_processing_mode — the line-item processing mode the pipeline applied to this transaction's current item set: detailed (every source line kept) or summary (condensed). Stamped by replace_transaction_line_items. NULL = pre-feature, semantically detailed
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 per-project transaction codes (TRX-000001 format; numbered independently per project)
• 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)

Transaction Items (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	NUMERIC	Line item number from the source document. NOT NULL — a non-numeric printed identifier is not storable here, so extraction treats it as "no line number"
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
ocr_adjusted_for_rounding	BOOLEAN	True if OCR absorbed sub-penny rounding into this line to match doc totals. Default false
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
• total must stay consistent with the discount: OCR often reads the PRE-discount gross into a line's total, so on a discounted line normalizeLineItem (utils/calculations.ts) recomputes total = subtotal + tax_total rather than trusting the OCR value (which would leave subtotal < total at 0% tax). Non-discount lines keep the printed total.
• ocr_adjusted_for_rounding marks the single last line whose subtotal/tax_total/total were nudged by the OCR pipeline to reconcile with the document-level totals (supplier banker's rounding / truncation)
• subtotal, total and tax_total must be FINITE (CHECK-enforced). An unconstrained NUMERIC admits NaN and ±Infinity, and PostgREST accepts their JSON string spellings, so without a constraint any authorized editor (or service-role job, or AI processor) could store one — and it then propagates into every transaction and budget aggregate that sums the column, turning a whole budget's actuals into NaN. chk_transaction_items_subtotal_finite, chk_transaction_items_total_finite and chk_transaction_items_tax_total_finite reject all three spellings with SQLSTATE 23514. NULL remains allowed (a CHECK only fails on FALSE): all three columns are nullable, a NULL subtotal is read as £0 throughout, and tax_total stays NULL for zero-rated / no-tax lines. private.rescale_allocation_links_on_subtotal_change's own non-finite guard is defense-in-depth behind the subtotal constraint, not a substitute for it — see BUDGETS.md.

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

Payment Methods (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)
bank_overseas_code	TEXT	SWIFT / BIC / IFSC code for international bank transfers (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

Project Currency Rates (project_currency_rates)

Purpose: Stores static per-project exchange rates used to convert foreign-currency transactions into the project currency (Multi-Currency Conversions V1).

Use Case Example: A GBP project receives an INR invoice. A rate row (from INR, rate 0.0087) is set once; every INR transaction on the project is then valued at native × 0.0087 in budgets, reports and approval-tier evaluation. The conversion target is always the project's own currency, so it is not stored on the rate row.

Column	Type	Description
id	UUID	Unique identifier for the rate row
project_id	UUID	Reference to the project this rate belongs to; the conversion target is this project's currency. FK to projects(id) ON DELETE CASCADE
from_currency_code	TEXT	Foreign (native) currency being converted from. FK to currencies(code)
rate	NUMERIC	Multiplier: native_amount × rate = project_currency_amount. Must be > 0
suggested_rate	NUMERIC	FX rate suggested by the external source at creation time (nullable); the user may override
suggestion_source	TEXT	Source of the suggested rate, e.g. frankfurter (nullable)
suggestion_date	DATE	As-of date the suggested rate was fetched for (nullable)
valid_from	TIMESTAMPTZ	When this rate became active
valid_to	TIMESTAMPTZ	When this rate was superseded. NULL = currently active
superseded_by	UUID	The rate row that replaced this one, when corrected. Self-FK to project_currency_rates(id) (nullable)
created_by_user_id	UUID	User who set this rate (default auth.uid())
created_at	TIMESTAMPTZ	Timestamp when the rate was created
updated_at	TIMESTAMPTZ	Timestamp when the rate was last updated

Key Features:

• Target currency is derived, not stored: the conversion target is always the project's currency_code, resolved live from project_id. No to_currency_code column — that would be a second source of truth that could drift from the project. A rate is only ever created for a currency that differs from the project currency.
• Immutable + versioned: rate rows are never updated in place. A correction closes the current row (valid_to + superseded_by) and inserts a new active row, preserving a full audit trail. Live views always read the single active row (valid_to IS NULL), so corrections self-heal historical figures deterministically.
• One active rate per (project, from-currency): enforced by a partial unique index WHERE valid_to IS NULL.
• Native amounts stay native everywhere; conversion is applied live in views (budget actuals, v_transactions) rather than stored. CHECK (rate > 0) and CHECK (valid_to IS NULL OR valid_to >= valid_from).
• Removal invariant (DB-enforced): a rate that live transactions still depend on may be updated (superseded) but never removed. A transaction is "blocking" when it belongs to the same (project_id, currency_code) and its status is not terminal (terminal = Cancelled / Rejected; On Hold and every other status — and a NULL status — count as blocking). The BEFORE UPDATE trigger trg_guard_project_currency_rate_removal raises when a bare removal (an active row being closed with no replacement) is attempted while such transactions exist. A supersede-close (driven by inserting a replacement active row) is always allowed — see the atomic-supersede + removal-guard triggers in VIEWS_AND_FUNCTIONS.md. The same guard also blocks two sideways removals: re-keying an active row onto a different (project, currency) (which would strand the original currency) and hard-deleting an active row (trg_guard_project_currency_rate_delete), both against the original currency's blocking-transaction check. All three paths share the project_currency_has_blocking_transactions(uuid, text) helper so their definition of "blocking" (including NULL status) is identical. The service layer (closeProjectCurrencyRateGated) applies the same check up front for a friendlier message; the triggers are the airtight backstop against a raw UPDATE/DELETE.

RLS Policies:

• SELECT: any project member (via v_user_accessible_projects).
• INSERT / UPDATE / DELETE: project member and caller_has_permission('project:manage_currency_rates', project_id) — mirrors the payment_methods pattern.

Payments (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)
external_id	TEXT	External provider transaction identifier for CSV-imported payments (e.g. Equals "Transaction ID", Revolut "ID"). Used to dedupe re-imports of the same statement row. NULL for manually-entered payments. Partial index idx_payments_external_id covers the non-NULL set
source	ENUM	payment_source enum. Creation provenance, immutable after INSERT. manual (user filled the create form), csv_import (payments-import worker created from a CSV statement line), auto_transaction (transaction processor auto-created the row when an inbound transaction matched a payment_method — covers both the petty-cash float withdrawal path and the generic transaction-processor path). The Reconciled-tab Auto-vs-Manual badge derives from this column. Default manual
is_imported_match	BOOLEAN	TRUE when the CSV import worker matched this row on a re-import / overlap (matched_paid / matched_scheduled_promoted outcomes). Independent of source: a manual scheduled payment matched by a later import becomes (manual, true). Default false
is_cash_float_bump	BOOLEAN	TRUE for petty-cash float top-ups created by the import worker. The payment row is the audit trail; payment_methods.amount caches total bumps; v_petty_cash_floats derives the live balance. Default false
is_fee_movement	BOOLEAN	TRUE for provider-fee lines linked to a main statement-match payment via parent_payment_id. Default false
is_manually_reconciled	BOOLEAN	TRUE when a user explicitly reconciled the payment via the manual-reconcile flow. Independent of is_imported_match; the app combines both flags to decide if a balanced payment counts as Reconciled. Default false
reconciled_at	TIMESTAMPTZ	Timestamp the payment became fully reconciled. Maintained app-side by services/payment (import worker, processPaymentCreation, manual reconcile action, allocation CRUD). NULL means unreconciled
reconciled_by_user_id	UUID	User who reconciled the payment — set alongside reconciled_at. FK to users(id). NULL when not reconciled
is_reconciled	BOOLEAN	GENERATED ALWAYS AS (reconciled_at IS NOT NULL) STORED. Free boolean for indexable queries / filters
parent_payment_id	UUID	Self-FK linking a fee payment to the main payment it accompanies. Set when a single CSV row produces both a main payment and a paired fee payment (Equals card-with-fee, cash-withdrawal-with-fee). ON DELETE SET NULL. NULL on stand-alone payments
import_run_id	UUID	FK to import_runs. Set by the import worker on every action that creates or updates this payment (CASH_BUMP, NEW_PAYMENT, MATCHED_SCHEDULED_PROMOTED, MATCHED_PAID, FEE_LINKED). Lets the Reconciliations tab jump back to the run page for audit. ON DELETE SET NULL
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
• idx_payments_unique_supplier_date — partial UNIQUE on (project_id, entity_id, scheduled_date, sign(total_amount)) WHERE status = 'Scheduled'. One open Scheduled payment per supplier per day per project per DIRECTION: a credit movement owed back to a supplier and a bill owed to them can both be scheduled for the same day, while a second movement in the SAME direction is still refused as the duplicate the index exists to prevent. (The predicate is = 'Scheduled', not != 'Cancelled' — Paid and Cancelled payments are both outside it.)
• CSV-import identity (external_id, source = 'csv_import', is_imported_match) lives on the payment, so the same identifier is visible across every reconciliation row that belongs to that payment
• Fee payments are linked to their main payment via parent_payment_id and flagged with is_fee_movement; queries that want the pair as a unit join payments p to payments fee ON fee.parent_payment_id = p.id
• Reconciliation state is materialised on the payment itself: reconciled_at / reconciled_by_user_id (maintained app-side) and the generated is_reconciled boolean for indexable queries
• Process-job payment creation persists paid_at and paid_by_user_id inside create_transaction_payment_fenced, in the same transaction that inserts the already-Paid payment and its allocation. Repairing the caller's own orphan first locks the proposed payment and revalidates its complete identity/status tuple, then fills only missing paid stamps; converged and reused payments retain their original audit values. The application re-reads and finalises the canonical payment on retries and converged, so a prior allocation commit followed by a failed Scheduled → Paid promotion cannot be mistaken for completed work
• import_run_id lets the Reconciliations tab walk back from a payment to the run that produced or last touched it; the Queue tab read computes candidate transactions live at table-load time rather than reading a denormalised view column

RLS Policies:

• Project-based access via user_accesses table

Payment Reconciliations (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	Signed amount allocated to this transaction. Non-zero (chk_payment_reconciliation_amount_nonzero), and its SIGN must match both the parent payment's total_amount (when that total is non-zero) and the transaction's total
status	payment_reconciliation_status	Active or Voided. Voided rows are kept for history but excluded from every balance/total/listing (default Active)
voided_at	TIMESTAMPTZ	Timestamp when the reconciliation was voided (NULL while Active)
voided_by_user_id	UUID	User who voided the reconciliation (NULL while Active)
void_reason	TEXT	Reason the reconciliation was voided, carried from the transaction void reason (NULL while Active)
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

The CSV-import identity used to live on this table. It moved to payments so a single payment carries the identity once, regardless of how many allocation rows reference it. Read payment.external_id / payment.source / payment.is_imported_match (the post-DEV-572 column names — is_statement_match was renamed to is_imported_match and source was added at the same time). v_payment_details exposes the same fields when joining through the view.

Key Features:

• Links payments to specific transactions
• Supports partial payment allocations
• Signed movements: a refund or credit note is a transaction with a NEGATIVE total, settled by a payment with a negative total_amount through an allocation with a negative amount. An ordinary bill keeps every value positive. Direction is the sign, not a separate column
• Finite movements only: chk_payment_reconciliation_amount_finite rejects PostgreSQL NUMERIC NaN, Infinity, and -Infinity at the table boundary. This remains necessary for transaction-less zero-total import stubs, which intentionally bypass direction/cap validation but still feed payment aggregates
• Validates that allocated amounts don't exceed transaction totals in MAGNITUDE — abs(current_allocated + amount) > abs(transaction_total) raises (only Active rows on non-Cancelled payments count toward the cap, self-excluded on UPDATE). The sign rules guarantee every contributing row shares one sign, so abs() of the sum is exact. Under the previous signed comparison a partial refund (-50 against -100) was rejected while an unrelated -1000 against +100 was accepted
• Direction agreement, enforced by trg_check_payment_reconciliation_total: the amount must share the sign of the parent payment's total (every non-voided row, INCLUDING transaction_id IS NULL stubs; a ZERO-total payment states no direction and constrains nothing — the state import stubs and mid-recompute payments hold) and, when a transaction is linked, the sign of the transaction total. A NULL or zero transaction total RAISES rather than passing unchecked
• Lock protocol: allocation writers first take a transaction-scoped advisory gate keyed by transaction id, then lock the transaction row and payment row. schedule_payment_group, the final create_transaction_payment_fenced definition, and process_payments_atomic take the same gate before parent rows. This gate is the serialization point between allocation's transaction → payment order and PostgreSQL's unavoidable payment → AFTER-trigger → transaction path. A row transitioning to Voided still returns first and takes no parent locks
• Parent direction/cap guards: trg_check_transaction_total_sign_flip (BEFORE UPDATE OF total on transactions) and trg_check_payment_total_sign_flip (BEFORE UPDATE OF total_amount on payments) refuse a sign change while a live allocation depends on the original direction. They also refuse a same-sign shrink below the sum of live allocation magnitudes; equality and growth remain valid, and removing the last allocation before writing zero stays permitted
• Promote-only Paid model: all three paid-marking triggers share private.transaction_is_fully_settled(total, total_paid) — settled means money moved in the SAME direction as the total, reaching at least its magnitude. For a positive total this is exactly the previous strictest form, so the positive flow is unchanged; a negative total is now settled only when actually refunded (the old total - total_paid <= 0 was true for every UNALLOCATED refund, flipping it to Paid on the first reconciliation event), and a ZERO total is never auto-Paid
• Atomic manual scheduling: schedule_payment_group(p_existing_payment_id, p_payment, p_items) (SECURITY INVOKER) writes the payment and its whole allocation set in ONE transaction — complete or nothing. Authenticated calls require the exact operation key in addition to invoker RLS: payment:schedule for Scheduled create/top-up and payment:process for fresh Paid; service workers retain their bypass. It validates the payload as a set (non-empty, no zero amounts, one distinct transaction per item, one shared direction, non-zero net), gates/locks every named transaction in id order before the payment, and requires every item to match project, currency, and role-derived payee. A top-up re-compares identity under its row lock and refuses an opposing direction. A new Paid payment defaults paid_at to now() and resolves paid_by_user_id from explicit actor → payment creator → auth.uid(), raising if no actor exists. See VIEWS_AND_FUNCTIONS.md § Payment Scheduling Functions
• Atomic Paid transition: every existing payment must enter Paid through process_payments_atomic. The owner-only BEFORE trigger rejects direct authenticated/service-role UPDATEs before the paid-marking AFTER trigger can acquire locks in reverse order. The RPC checks exact payment:process, forces an authenticated actor to auth.uid() and its paid timestamp to the database clock, and admits authenticated metadata only for a single-payment promotion tied to that caller's unfinished, same-project payments import run. Under lock it prevents established payee replacement, validates any supplied relationship's project/entity pair, and permits reconciliation stamps only when fully allocated; those stamps are normalized to the Paid timestamp and auth.uid(). Service/owner workers may supply an explicit actor and the fixed metadata allowlist. The RPC gates and locks affected transactions before payments, revalidates Scheduled status under lock, and commits status, paid stamps, optional method/notes, and permitted metadata in one UPDATE. Two partial payments settling one transaction therefore serialize and the second observes the first
• Partial unique index on (payment_id, transaction_id) WHERE status = 'Active' prevents duplicate live allocations while letting a Voided row coexist with a future Active one
• Cascades deletes when parent payment is deleted
• Voiding a transaction soft-voids its reconciliations (status = 'Voided'); the payment is cancelled outright if this was its only Active allocation, otherwise the payment survives with its total recomputed from the remaining Active rows

RLS Policies:

• SELECT: Visible to anyone who can see the parent payment (scoped through payments.payment_id, no extra permission)
• INSERT / UPDATE / DELETE: Require the payment:reconciliations permission on the parent payment's project. The check resolves project_id through the parent payments row and calls caller_has_permission(ARRAY['payment:reconciliations'], p.project_id, false) inside the membership EXISTS — mirroring the payment_method_assignments parent-reach pattern. This is the server-side backstop: an active project member without payment:reconciliations cannot create, amend, or remove allocation rows even by calling the server action directly.
• Cascade note: Removing the last reconciliation on a payment deletes the now-empty parent payment (service layer), which runs under the same user's client. The payments DELETE policy therefore admits payment:reconciliations alongside payment:schedule / payment:schedule:cancel, so a reconciler can complete that cascade.

Payment Method Assignments (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

Import Mappings (import_mappings)

Purpose: Saved CSV column-to-field mappings shared across every import domain. Polymorphic over entity_type — today only 'payments' ships, but future domains ('budgets', 'transactions', …) drop their own rows here with zero schema changes. Domain-specific discriminators ride on the opaque domain_config JSONB column rather than dedicated columns, so adding a new domain is a data-only change.

There are three row kinds:

• Universal generic fallback row — entity_type=NULL, domain_config=NULL, is_custom=false. Rendered by every domain whose ImportFlowConfig.canCreateCustomMappings is true as the "no preset opinion" tile on the Provider step.
• Per-domain built-ins — entity_type='payments' (etc.), is_custom=false, project_id=NULL. Seeded by the migration with brand logo + example CSV attachments and a starter mapping_json. Immutable from the app (UPDATE / DELETE RLS policies require is_custom=true) — changing one takes a data migration, and the content pins in supabase/tests/17_import_mapping_contents.sql must be updated with it. Card built-ins anchor scheduled_date on the provider's PURCHASE-date column (Equals Created date (UTC), Revolut Date started (UTC)), never the settlement date — card movements settle 1–2 days after purchase, and the import worker's date matching anchors on scheduled_date, so a settlement-date mapping makes every match miss and duplicates the payment.
• User-saved customs — is_custom=true, project_id set. Created from the wizard's "Save as custom preset" affordance. Editable + deletable by project members.

Use Case Example: A producer uploads a Revolut card statement; once they've manually mapped the columns and saved the mapping as "Revolut UK", the next import on the same project auto-picks the mapping by name (and by the card.revolut preset key). On a Bank Account import, the wizard looks up domain_config->>'payment_method_id' to find the saved mapping for the picked PM.

Column	Type	Description
id	UUID	Primary key
project_id	UUID	FK to projects(id) ON DELETE CASCADE. NULL for built-ins (global templates); NOT NULL for customs (enforced by the INSERT RLS policy + createImportMappingSchema)
entity_type	TEXT	Target table name ('payments' today; future 'budgets' etc.). NULL marks the universal generic built-in. Plain text — adding a domain is a data-only change
source_preset	TEXT	Built-in preset key this row is rooted in (generic, bank.generic, card.equals, card.revolut, …). The framework's preset registry resolves the key to a parser implementation
name	TEXT	User-supplied preset name. For entity_type='payments': required for card customs (per-project uniqueness via uq_import_mappings_card_name), NULL for bank customs and built-ins
description	TEXT	Optional free-text description shown on the saved-preset tile in the wizard. Not used in dedup / matching logic
provider	TEXT	Free-text provider tag matching payment_methods.provider (e.g. 'Equals', 'Revolut'). NULL marks provider-agnostic rows. The Provider step narrows candidates so an Equals statement only sees Equals + universal generic
is_custom	BOOLEAN	TRUE for user-saved customs; FALSE for the seeded built-ins. Built-ins are immutable from the app (UPDATE / DELETE RLS policies block is_custom=false rows). NOT NULL, default TRUE
logo_attachment_id	UUID	FK to attachments(id) ON DELETE SET NULL. Brand logo shown on the Provider step tile. Customs leave this NULL and fall back to initials
example_attachment_id	UUID	FK to attachments(id) ON DELETE SET NULL. Downloadable example CSV for the provider. NULL for customs and for built-ins until the asset ships
domain_config	JSONB	Opaque per-domain payload. NULL for the universal generic row and for any future domain whose preset shape needs no extra configuration. Payments stores {"payment_method_type": "Bank Account", "payment_method_id": "<uuid>"} for bank customs and {"payment_method_type": "Card"} for card rows. The DB enforces no schema — domain ownership lives in the app-side Zod schema (e.g. paymentsCreateImportMappingSchema) + the partial unique indexes reading domain_config->>'…'
mapping_json	JSONB	JSON object mapping CSV column header → ordered list of field keys that feed it. Multiple sources concatenate via ". " in array order. Keys correspond to import_fields.key rows for the active entity_type. NOT NULL
created_at	TIMESTAMPTZ	NOT NULL, default now()
updated_at	TIMESTAMPTZ	NOT NULL, default now()
created_by_user_id	UUID	FK to users(id). NULL for the seeded built-ins (system-authored); NOT NULL for customs
updated_by_user_id	UUID	FK to users(id). Same nullability rules as created_by_user_id

Constraints:

• No DB-level shape CHECK. Domain shape rules (e.g. payments' bank-needs-PM-id, card-needs-name) live in the per-domain Zod schemas. Letting CHECK constraints carry domain logic would make adding a new domain a migration — keeping them off the DB makes it data-only.

Indexes:

• idx_import_mappings_project_id — partial on project_id WHERE project_id IS NOT NULL. List reads scoped to a project.
• idx_import_mappings_project_entity_type — partial (project_id, entity_type) WHERE project_id IS NOT NULL. The framework wizard always filters by both.
• idx_import_mappings_builtins_by_provider — partial (entity_type, provider) WHERE project_id IS NULL. Provider step's built-in tile lookup. Domain-agnostic — any future domain reads through this index.
• idx_import_mappings_customs_by_provider — partial (project_id, entity_type, provider) WHERE project_id IS NOT NULL. Per-project custom-tile lookup.
• idx_import_mappings_domain_payment_method_id — partial expression index on (domain_config->>'payment_method_id') WHERE domain_config ? 'payment_method_id'. Payments-specific: the bank flow's TARGET step asks "which custom was saved for this PM?". A future domain whose domain_config carries its own FK would add its own equivalent.
• idx_import_mappings_logo_attachment_id, idx_import_mappings_example_attachment_id — FK coverage.
• idx_import_mappings_created_by_user_id, idx_import_mappings_updated_by_user_id — FK coverage.
• uq_import_mappings_bank_per_pm — partial UNIQUE (project_id, entity_type, (domain_config->>'payment_method_id')) WHERE entity_type='payments' AND domain_config->>'payment_method_type'='Bank Account' AND is_custom=true. One bank mapping per (project, payment_method). Reads JSONB keys — adding a similar partial unique for another domain ships its own predicate.
• uq_import_mappings_card_name — partial UNIQUE (project_id, entity_type, lower(name)) WHERE entity_type='payments' AND domain_config->>'payment_method_type'='Card' AND is_custom=true. Case-insensitive name uniqueness within a project.

RLS Policies:

• SELECT: built-ins (project_id IS NULL) visible to every authenticated user; customs gate on project membership via v_user_accessible_projects.
• INSERT / UPDATE / DELETE: is_custom=true AND project membership (built-ins are immutable from the app).

Import Fields (import_fields)

Purpose: Catalog of destination fields per import entity_type. Drives the Mapping step UI and the parser-side row schema. One row per (entity_type, key). Adding a new field is one INSERT — no TS code change.

Column	Type	Description
id	UUID	Primary key
entity_type	TEXT	Target table name (matches import_mappings.entity_type / import_runs.entity_type). NOT NULL
key	TEXT	Stable field key referenced by parsers and mapping_json (e.g. 'scheduled_date'). Never localised. NOT NULL
label	TEXT	Human-readable label shown in the Mapping step's first column. NOT NULL
description	TEXT	Helper text shown under the label. NULL hides the helper line
required	BOOLEAN	TRUE if the field MUST be mapped before the wizard advances past Mapping. Gating is client-side (canAdvance) + server-side (the per-domain TS commit worker skips rows with required values NULL). Default FALSE
allow_multiple	BOOLEAN	TRUE if multiple file columns can feed this field (parser concatenates with ". " in pick order). Today only payments.description uses this. Default FALSE
value_kind	TEXT	Generic coercion contract. See CHECK constraint below
display_order	INTEGER	Render order within an entity_type. Gaps of 10 to leave room for inserts. NOT NULL
created_at	TIMESTAMPTZ	NOT NULL, default now()
updated_at	TIMESTAMPTZ	NOT NULL, default now()

Constraints:

• chk_import_fields_value_kind — closed framework-level CHECK: value_kind IN ('date','number','text','currency_code','external_id'). Domain-specific display tweaks (e.g. masked-PAN truncation for payments' card_last_four field) live in the per-domain typing layer keyed off field.key, not by adding a domain-flavoured value_kind here.
• uq_import_fields_entity_key — UNIQUE (entity_type, key). One row per destination per domain.

Coercion contract for value_kind:

Kind	Output
date	ISO yyyy-mm-dd (parsed via per-preset dateFormats)
number	JS number (parses thousands separators + parens-for-negative)
text	Trimmed string
currency_code	ISO 4217 currency code (GBP / USD / …)
external_id	Opaque dedupe key (provider's own transaction ID)

RLS Policies:

• SELECT: granted to authenticated (catalog is global, read-only from the app).
• INSERT / UPDATE / DELETE: not exposed to the app. Mutations go through migrations under service_role.

Import Runs (import_runs)

Purpose: Per-import audit log. One row per import run, written by the per-domain TS commit worker. The source file is stored as a private attachment (source_attachment_id) so the run can be replayed; per-row outcomes live in row_outcomes JSONB (no child table — bounded by file size).

Column	Type	Description
id	UUID	Primary key
project_id	UUID	FK to projects(id) ON DELETE CASCADE. NOT NULL
entity_type	TEXT	Target table name (matches import_mappings.entity_type / import_fields.entity_type). Default 'payments'. Lets per-domain audit lists filter to their surface without joining import_mappings
import_mapping_id	UUID	FK to import_mappings(id) ON DELETE RESTRICT. NOT NULL — every run is bound to the parser config that produced it; the mapping cannot be deleted while runs reference it. The mapping carries source_preset + domain_config; reads that need those values join through this FK
domain_config	JSONB	Opaque per-domain JSONB (mirrors import_mappings.domain_config). Payments stuffs {payment_method_id} for bank-flow runs; future domains store their own keys. Queryable via PostgREST ->> for narrow lookups (e.g. idx_import_runs_payment_method_id expression index)
source_attachment_id	UUID	FK to attachments(id) ON DELETE RESTRICT. NOT NULL. The source file stored in a private bucket. Replaying or auditing the run reads bytes via this FK; ON DELETE RESTRICT so we never lose the source while the run still exists
total_rows	INTEGER	Total rows in the source file BEFORE the preset's include-filter ran. Includes rows the parser dropped (wrong Type / Pending status / etc.). Always ≥ processed_rows
processed_rows	INTEGER	Rows handed to the commit worker after the include-filter. On a clean run: processed_rows = created_rows + updated_rows + skipped_rows + errored_rows. ≤ total_rows
created_rows	INTEGER	Rows the worker INSERTed as new payments. For Payments: new_payment + fee_linked + cash_bump + unmatched (needs-review). matched_paid / matched_scheduled_promoted count as updates instead
updated_rows	INTEGER	Rows the worker UPDATEd in place (e.g. Scheduled → Paid promotions on existing payments)
skipped_rows	INTEGER	Rows the worker dropped intentionally — per-row reason in row_outcomes (missing_required_field, user_skipped, already_imported, parent_payment_not_committed, …)
errored_rows	INTEGER	Rows that raised an unexpected error mid-run. Indicates data-quality issues, not worker bugs
status	import_run_status	Enum: 'success' (errored_rows = 0), 'partial' (0 < errored_rows < processed_rows), 'failed' (worker threw before reaching the finaliser OR errored_rows ≥ processed_rows). The framework's runImport lifecycle helper picks the value
error_summary	JSONB	Error envelope when status = 'failed'. NULL otherwise
row_outcomes	JSONB	Per-row audit array — opaque per-domain shape. Payments uses {row_index, action, payment_id?, transaction_id?, score?, reason?, suggestions?[]} with action ∈ {matched_paid, matched_scheduled_promoted, new_payment, fee_linked, cash_bump, unmatched, skipped, errored}. To list "which file lines were skipped" filter the array for action === 'skipped'. Loaded only on the run-detail page (potentially MB-sized); never selected in list views
started_at	TIMESTAMPTZ	NOT NULL, default now(). When the commit worker started executing
finished_at	TIMESTAMPTZ	When the commit worker reached the finaliser. NULL if the run is still in-flight
created_at	TIMESTAMPTZ	NOT NULL, default now(). Record creation timestamp
created_by_user_id	UUID	NOT NULL, default auth.uid(). FK to users(id). User who triggered the import

Constraints:

• chk_import_runs_counts_nonneg — total_rows, processed_rows, created_rows, updated_rows, skipped_rows, errored_rows all ≥ 0.

Indexes:

• idx_import_runs_project_id — list per project.
• idx_import_runs_import_mapping_id — FK coverage on import_mapping_id. Audit-by-mapping.
• idx_import_runs_payment_method_id — JSONB expression index on (domain_config->>'payment_method_id') WHERE domain_config ? 'payment_method_id'. Payments-side lookup: "runs for this PM."
• idx_import_runs_source_attachment_id — FK coverage on source_attachment_id.
• idx_import_runs_created_by_user_id — FK coverage.
• idx_import_runs_project_entity_started_at — composite (project_id, entity_type, started_at DESC). Backs the per-domain "recent runs" list (each domain's import page scopes by its own entity_type).

RLS Policies:

• SELECT: project membership via v_user_accessible_projects.
• INSERT / UPDATE: written by each domain's TS commit worker (Server Action) on every import run.
• DELETE: not exposed (immutable audit log).