Budget Management Tables

Budgets (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). The rollup is an uncorrelated CTE grouped by v_budget_items.budget_id, so a single-budget or per-project read only aggregates the requested budget(s) — never a per-budget LEFT JOIN LATERAL over v_budget_items, which re-materialises that view's nested allocation aggregates once per outer row. See VIEWS_AND_FUNCTIONS.md for the measurements and the shapes to avoid.
• Lock mechanism prevents edits to activated budgets
• budget_status enum: Draft, Active, Archived
• project_id is immutable: the trg_budgets_prevent_project_id_change trigger raises budgets.project_id is immutable on any UPDATE that changes it. budgets is the root of the budget-tree project_id derive chain — moving it would orphan the children's already-derived project_id (the child triggers do not re-fire on a parent move), so the move is forbidden outright.

Budget Headers (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
project_id	UUID	Denormalised project ownership (NOT NULL, RLS fast path)
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:

• project_id is denormalised from budgets.project_id and always derived from the parent — trg_budget_headers_set_project_id fires BEFORE INSERT OR UPDATE OF (budget_id, project_id) and overwrites NEW.project_id from the referenced budget. A caller-supplied or caller-updated project_id is therefore ignored; the value can never be spoofed to make a header from one project appear in another. Lets the SELECT policy gate directly on the row's project_id (no chain walk to budgets).
• Cross-project re-parenting is forbidden: if an UPDATE would change the DERIVED project_id (moving a header to a budget in another project), the trigger raises budget_headers.project_id is immutable: re-parenting across projects is not allowed — otherwise the header's items/allocations would keep a stale project_id and leak across projects. Re-parenting within the same project remains allowed. The same guard exists on budget_items, budget_item_daily_allocations, and budget_allocation_transaction_items, making project_id immutable-by-derivation across the entire budget tree.
• Same-budget parent invariant: parent_budget_header_id must reference a header in the SAME budget. Enforced by the composite FK fk_budget_headers_parent_same_budget (parent_budget_header_id, budget_id) → (id, budget_id) (backed by the uq_budget_headers_id_budget_id unique index). MATCH SIMPLE lets a NULL parent_budget_header_id (top-level header) pass. The existing id-only parent FK is kept for the cascade; the composite one adds the same-budget constraint. The constraint is VALIDATED, so the invariant is proven for pre-existing rows, not only enforced going forward — that is what makes the v_budget_headers rollup's budget_id-equality join provably lossless rather than lossless-by-inspection. Pinned by a convalidated assertion in tests/05_foreign_keys_and_indexes.sql; a regression to NOT VALID fails there.
• Totals computed dynamically via v_budget_headers view using a recursive CTE. Items are aggregated once per (budget_id, budget_header_id) and then attributed to every ancestor via the header_ancestors walk; the rollup joins back on (root_header_id, budget_id) so an outer budget_id filter bounds the aggregation to the requested budget. The budget_id join conjunct is a performance bound, not a correctness filter — the same-budget parent invariant below already guarantees a header tree never spans budgets. Never restructure this as a per-header LEFT JOIN LATERAL over v_budget_items; see VIEWS_AND_FUNCTIONS.md.
• 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)

Budget Items (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
project_id	UUID	Denormalised project ownership (RLS fast path)
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
• project_id is always derived from the parent header — trg_budget_items_set_project_id fires BEFORE INSERT OR UPDATE OF (budget_header_id, project_id) and overwrites NEW.project_id, so a spoofed value is ignored; an UPDATE that would CHANGE the derived value (re-parenting under a header in another project) raises instead of stranding the item's daily allocations on a stale project_id.

INSERT policy hardening (budget_headers + budget_items):

Because project_id is now fully derived and unspoofable, the INSERT policies gate on the row's project_id via the bounded set-based permission primitive instead of the legacy per-row EXISTS join through the parent budget:

• budget_headers INSERT WITH CHECK: project_id = ANY (SELECT unnest(caller_accessible_project_ids(ARRAY['budget:edit'], false)))
• budget_items INSERT WITH CHECK: same form with ARRAY['budget:edit', 'budget:estimated_costs:manage']

The set-based caller_accessible_project_ids(...) InitPlan form is evaluated once per statement (row-count-independent), which is what makes a bulk INSERT ... SELECT (the atomic import RPC below) fast. Single-record WITH CHECK clauses elsewhere may still use caller_has_permission(...); bulk/multi-row writes must use this set-based form.

Budget Item Daily Allocations (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)
project_id	UUID	Denormalised project ownership (NOT NULL, RLS fast path)
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, converted into the project currency), balance, estimated_cost_balance. actual_total is a correlated LEFT JOIN LATERAL aggregate per allocation — never an uncorrelated GROUP BY subquery, which under RLS re-aggregates the caller's whole visible link set once per outer row. See VIEWS_AND_FUNCTIONS.md for the measurements and the shapes to avoid.
• Multi-currency (Multi-Currency Conversions V1): budgets are a project-currency view, so all transaction-sourced amounts are converted into the project currency IN PLACE — there is no separate native column. actual_total (and the balance / estimated_cost_balance derived from it) is computed convert-then-sum: SUM(line_amount × COALESCE(resolve_transaction_exchange_rate(...), 1)) per line, not SUM(line_amount) × rate, so a budget item aggregating transactions in different currencies is correct. COALESCE(rate, 1) is a no-op for same-currency lines and keeps the native value when a rate is unresolvable (an anomaly). The rate honours the project's exchange_rate_mode (live vs stamped). Same conversion applies to the per-line detail views v_budget_item_transaction_details.amount and v_budget_allocation_transaction_details.amount / item_subtotal, and to the v_budget_items direct + daily-allocation actuals paths. v_budget_headers / v_budgets roll up SUM(vi.actual_total) and inherit the converted figure.
• Allocations can be created manually, from estimated costs (added_from_estimated_costs), or auto-created during transaction processing (added_from_processing)
• Estimated cost, quantity, note, and canceled_estimated_cost changes are persisted together through batch_update_estimated_costs. The RPC rejects RLS-filtered or duplicate inputs instead of committing a partial batch, and callers also verify that every requested row and field is returned. Unrealised-cost lists filter on estimated_cost_balance > 0, so partially invoiced estimates remain visible until actual and cleared amounts consume the estimate.
• Invariant — allocations follow line-item re-classification. On write paths the triggers guard (link inserts/re-keys, and re-classification under a daily-allocation-enabled current budget), a budget_allocation_transaction_items link points at an allocation whose budget_item_id matches the linked transaction line item's budget_item_id. Links under a budget with daily allocations DISABLED are deliberately left untouched by re-classification (no allocation queries run for such budgets), so historical mismatches there persist until data repair. When a line item is re-classified (transaction_items.budget_item_id changes) on a project whose current budget has enable_daily_allocations = true, the trg_transaction_items_sync_allocation_links trigger re-points each of its links onto the equivalent allocation (same production day) of the new budget item — creating that allocation if it does not yet exist (quantity 1, rate 0, added_from_processing = true, added_from_estimated_costs = false, estimated_* = NULL; project_id derived by the set-project-id trigger), preserving each link's amount and created_at, de-duping to one survivor per day (earliest created_at), and purging any cross-project stale links. Clearing the line item's budget item to NULL deletes all of its links. Runs as a SECURITY DEFINER trigger so a transaction editor without budget:edit/budget:delete can still re-classify. See VIEWS_AND_FUNCTIONS.md (private.sync_allocation_links_on_budget_item_change).
• Invariant — allocation link amounts follow the line's subtotal. A line item's links are only meaningful when their amounts sum EXACTLY to the line's subtotal; otherwise the day-level actuals under- or over-state its real spend. The processing pipeline creates links from the subtotal it saw at processing time, so when a user later corrects transaction_items.subtotal the trg_transaction_items_update_allocation_link_amounts trigger rescales the line's EXISTING links. Weights, in precedence order: pro-rata on the old amounts (amount / old_sum, so signs are preserved); abs-value pro-rata when the old amounts cancel to zero with mixed signs; an equal 1/n split when every old amount is zero (the shape an OCR-zeroed line's links carry). Apportionment is largest-remainder in penny space — each link takes trunc() of its exact share and the |residual| links whose fractional remainder is furthest in the residual's OWN direction take one penny more (or fewer, for a negative residual) — so accumulated rounding can never drag a link past zero and the extra penny lands on the chronologically EARLIEST production day (100.00 over three equal days → 33.34 / 33.33 / 33.33). The direction is load-bearing: trunc() rounds toward zero, so a mixed-sign set whose bases sit ABOVE their exact shares produces a NEGATIVE residual, and the penny is then REMOVED from the most-negative remainders (ranking key multiplied by sign(residual)) — taking it off the largest positive remainder instead would sign-flip that link and land it more than a penny from its exact share. The behaviour is symmetric: the earliest day absorbs the adjustment whether it gains or loses a penny (−1.00 over three equal days → −0.34 / −0.33 / −0.33). Any sub-penny subtotal remainder is added to the chronologically first link, signed — half-up rounding of the subtotal can overshoot it, so the term corrects in either direction. Chronology, precisely: dated days order by calendar_date and ALWAYS precede undated days (the ordering is calendar_date NULLS LAST); among undated days the fallback key is day_number (itself NULLS LAST), with created_at/id as a deterministic tie-break. A NULL subtotal is read as £0 (it is what the pipeline apportions for an unparsed line), so → NULL behaves identically to → 0 and leaves the link geometry intact for a later correction. The trigger NEVER creates or deletes a link (that is the pipeline's job) — a line with no links is a silent no-op — and its WHEN (OLD.subtotal IS DISTINCT FROM NEW.subtotal) guard means a no-change write never fires, so hand-tuned amounts (including sub-penny ones) survive byte-identical. Its name sorts AFTER trg_transaction_items_sync_allocation_links, and same-timing triggers fire in name order, so an UPDATE changing both budget_item_id and subtotal re-points/de-dupes the links first and rescales the SURVIVING set second. Unlike the re-classification trigger it has no enable_daily_allocations / current-budget gate — it never writes the allocation tables or changes link geometry, so there is nothing such a gate would protect, and skipping the rescale would only leave stale amounts mis-stating actuals. The rescale is contained to the line's same-project links (bati.project_id = the edited transaction's project_id, the link's project_id being trigger-derived and immutable): a transaction_items.budget_item_id can point cross-project, and this DEFINER trigger must never write into a project the caller holds no permission on, so a cross-project stale link is left untouched and is not a weight in the apportionment (the re-classification trigger is what deletes such links). Non-finite monetary values are unrepresentable — the table-level CHECKs chk_transaction_items_subtotal_finite and chk_budget_alloc_tx_items_amount_finite reject NaN/±Infinity for EVERY writer (see the column notes below and in FINANCIAL.md). The trigger's own non-finite guards are retained as defense-in-depth BEHIND those constraints: it fails closed (writing nothing) when the new subtotal is non-finite or when ANY locked link amount is, since one such amount makes every weight non-finite and would poison the finite siblings through the DEFINER boundary. They are kept deliberately so the DEFINER boundary stays fail-closed if a constraint is ever dropped, and so an already-corrupt pre-constraint row cannot take its finite siblings with it. SECURITY DEFINER because the link table's UPDATE policy requires budget:edit, which transaction editors lack. One concurrency window is known and accepted: only links that EXIST when the subtotal edit commits are rescaled, so a pipeline link-INSERT whose amount was computed from a subtotal read BEFORE a concurrent subtotal edit can commit after the rescale and is not re-summed until the next subtotal change — the pre-existing pipeline read-then-write class, strictly narrowed (not introduced) by this trigger. See VIEWS_AND_FUNCTIONS.md (private.rescale_allocation_links_on_subtotal_change).

RLS Policies:

• SELECT: Requires budget:view permission, gated directly on the row's denormalised project_id (no chain walk to budgets), PLUS the sensitivity conjunct below — here keyed on the PARENT item's budget_item_id, so an allocation is visible exactly when its parent budget item is
• INSERT/UPDATE: Requires budget:edit permission (walks to budgets — write paths are low volume, still chained)
• DELETE: Requires budget:delete permission (walks to budgets)

Sensitivity gating on the budget SELECT policies

The budget_headers, budget_items and budget_item_daily_allocations SELECT policies each carry a third conjunct that hides rows a sensitive_rules entry restricts from the caller. It is expressed in the bounded set form, NOT a per-row row_is_visible_to_caller call:

AND NOT (id             = ANY (SELECT unnest(private.caller_restricted_budget_header_ids())))  -- budget_headers
AND NOT (id             = ANY (SELECT unnest(private.caller_restricted_budget_item_ids())))    -- budget_items
AND NOT (budget_item_id = ANY (SELECT unnest(private.caller_restricted_budget_item_ids())))    -- budget_item_daily_allocations

The allocations policy consumes the budget-item twin (its sensitivity is inherited from the parent item); there is no allocation-specific rule type. Each twin is the exact set-complement of row_is_visible_to_caller('<type>', id, '[]') for that policy's argument shape, built once per statement as a hashed subplan instead of a per-row DEFINER call. Because the twins derive their blocked set from sensitive_rules alone (no join to the budget tables), membership is independent of row existence, so an INSERT … RETURNING on a uuid that already carries a rule the caller is not cleared for fails closed rather than returning a row the caller could not otherwise see. Full contract, the self-exemption-unreachability derivation, and the test sentinels: VIEWS_AND_FUNCTIONS.md § "Restriction-set twins for the list/count SELECT policies".

The budget_headers / budget_items UPDATE policies still call the per-row row_is_visible_to_caller — they are single-record write paths, not list/count paths.

Budget Allocation Transaction Items (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)
project_id	UUID	Denormalised project ownership (NOT NULL, RLS fast path)
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
• amount must be FINITE (CHECK-enforced). An unconstrained NUMERIC admits NaN and ±Infinity, and PostgREST accepts their JSON string spellings, so without a constraint any writer with budget:edit (or a service-role job) could store one — and it propagates into every day-level actuals aggregate that sums the column. chk_budget_alloc_tx_items_amount_finite rejects all three spellings with SQLSTATE 23514; the column is NOT NULL, so every row is checked. This is why private.rescale_allocation_links_on_subtotal_change can treat a non-finite locked amount as a can't-happen corruption case rather than an expected input — its refusal to rescale such a line is defense-in-depth behind this constraint, not a substitute for it.
• No updated_at/updated_by columns. Of the row's two FKs, transaction_item_id is never re-keyed, while budget_item_daily_allocation_id IS re-pointed — but by exactly one writer: the re-classification sync trigger, which moves a link onto the new budget item's allocation for the same production day (preserving the link's amount, and DELETING a link that would collide with an existing one on that day rather than summing the two). Any OTHER writer's re-key is rejected outright by the guard trigger below. amount is updated in place by exactly one writer too: trg_transaction_items_update_allocation_link_amounts, which rescales it when the line item's subtotal changes (see the daily-allocations section above). Both writers are SECURITY DEFINER trigger writes, so a per-row updated_by stamp would record the trigger's owner rather than a meaningful actor; the audit trail for such a change lives on the parent transaction_items row.
• Budget-item match invariant (trigger-enforced). A link is only valid when its allocation's budget_item_id equals the linked transaction line item's budget_item_id. trg_budget_allocation_transaction_items_enforce_item_match (a fail-closed BEFORE INSERT OR UPDATE OF budget_item_daily_allocation_id, transaction_item_id guard — both FKs covered, so a re-key of either is validated) rejects any insert or re-key that would violate it. A third guard, trg_budget_item_daily_allocations_guard_budget_item_rekey (BEFORE UPDATE OF budget_item_id ON budget_item_daily_allocations), makes an allocation's own budget_item_id immutable — it rejects ANY change (re-keying would mis-file the linked transactions' actuals onto the new budget item and leave the write-once denormalised project_id stale). No app path does this; delete-and-recreate is the sanctioned path, mirroring the project_id write-once convention. The complementary trg_transaction_items_sync_allocation_links trigger on transaction_items keeps existing links in sync when the line item is re-classified (see the daily-allocations section above). All three guards' error messages echo only caller-supplied ids (not the derived budget-item ids), to avoid a DEFINER identifier oracle. See VIEWS_AND_FUNCTIONS.md (private.enforce_allocation_link_budget_item_match, private.guard_daily_allocation_budget_item_rekey).

RLS Policies:

• SELECT: Requires budget:view permission, gated directly on the row's denormalised project_id (no chain walk to budgets)
• INSERT/UPDATE: Requires budget:edit permission (walks the chain — write paths still chained)
• DELETE: Requires budget:delete permission (walks the chain)

Atomic CSV import (import_budget RPC)

CSV budget import runs through the public.import_budget(p_budget jsonb, p_headers jsonb, p_items jsonb) RPC (SECURITY INVOKER) rather than a fan-out of individual PostgREST inserts. The whole budget + header + item tree is created in ONE transaction under the caller's own RLS, so any validation failure or RLS rejection rolls the entire import back — no orphaned partial Draft budget. Contract:

• p_budget {id, project_id, title} — all required, title non-empty. status is forced Draft; enable_estimated_costs and enable_daily_allocations are forced false.
• p_headers — 1..2000 objects {id (unique across the payload), parent_budget_header_id (null or another payload id — closure-checked), account_code, title (non-empty)}. budget_id is stamped from p_budget.id (the payload does not carry it); project_id is derived by the trigger. The parent chain is additionally checked for cycles as a set — the row-level cycle trigger cannot see sibling rows inserted in the same multi-row statement (statement-snapshot invisibility), so the RPC rejects a cyclic payload up front.
• p_items — 1..10000 objects {budget_header_id (a payload header id), account_code, title (non-empty), description, original_quantity, original_rate, original_total (required), interval_type, interval_quantity, is_unbudgeted}. Item ids are DB-generated; project_id is derived by the trigger.
• No EXCEPTION handler (errors propagate → full rollback). Returns {header_count, item_count}. EXECUTE granted to authenticated only. Because the header/item inserts are set-based INSERT ... SELECT under the bounded InitPlan INSERT policies, a ~120-header / 2000-item import completes well within the statement timeout.