ADR 018 Invoice Budget Line Junction Table

ADR-018: Invoice-Budget-Line Junction Table

Status

Accepted

Context

The current schema links invoices to budget lines via direct FK columns on the invoices table: work_item_budget_id and household_item_budget_id. This is a 1:1 model -- each invoice can link to at most one budget line.

In practice, homeowners frequently receive a single invoice covering multiple budget categories (e.g., one contractor invoice covering both labor and materials). The 1:1 model forces users to either:

1. Create duplicate invoices (one per category), splitting the amount manually
2. Link the invoice to only one budget line, losing the category-level cost breakdown

Neither option accurately reflects the real-world invoice structure. Accurate per-category cost attribution is essential for:

• Budget overview and projected budget calculations
• Subsidy calculations (which are category-based)
• Bank reporting and cost tracking

Decision

Replace the direct FK columns (invoices.work_item_budget_id and invoices.household_item_budget_id) with a junction table invoice_budget_lines that supports many-to-many linking with per-link itemized amounts.

Junction Table Design

CREATE TABLE invoice_budget_lines (
  id TEXT PRIMARY KEY,
  invoice_id TEXT NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
  work_item_budget_id TEXT REFERENCES work_item_budgets(id) ON DELETE SET NULL,
  household_item_budget_id TEXT REFERENCES household_item_budgets(id) ON DELETE SET NULL,
  itemized_amount REAL NOT NULL CHECK(itemized_amount > 0),
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  CHECK (
    (work_item_budget_id IS NOT NULL AND household_item_budget_id IS NULL) OR
    (work_item_budget_id IS NULL AND household_item_budget_id IS NOT NULL)
  )
);

Key Design Decisions

1. Surrogate id PK (TEXT UUID): Unlike other junction tables in the schema that use composite PKs, this table uses a surrogate ID. The reason: the junction rows carry meaningful data (itemized_amount) and need to be individually addressable for CRUD operations (PUT to update amount, DELETE to unlink). A composite PK of (invoice_id, work_item_budget_id) would not work because one of the budget FK columns is always NULL (XOR constraint).

2. XOR constraint via CHECK: Exactly one of work_item_budget_id or household_item_budget_id must be non-null. This mirrors the polymorphic pattern used elsewhere (e.g., document_links.entity_type), but since we only have two possible targets and both are typed FK columns, we use a simple CHECK constraint instead of a discriminator column.

3. Exclusive linking via partial UNIQUE indexes: Each budget line can be linked to at most one invoice. This is enforced by two partial unique indexes:

   • UNIQUE(work_item_budget_id) WHERE work_item_budget_id IS NOT NULL
   • UNIQUE(household_item_budget_id) WHERE household_item_budget_id IS NOT NULL

   SQLite supports partial indexes (since 3.8.0), making this the most robust enforcement approach. The alternative (application-level enforcement only) risks race conditions and data inconsistency.

4. ON DELETE CASCADE from invoice: When an invoice is deleted, all its budget line links are removed. This is the natural lifecycle -- the links exist only because of the invoice.

5. ON DELETE SET NULL from budget lines: When a budget line is deleted, the junction row survives with the FK cleared to NULL. This prevents data loss but creates an orphaned row. The application layer should clean up these rows (delete junction rows where both budget FKs are NULL). This approach was chosen over CASCADE because an invoice may still need to track that it had budget line allocations even if the budget lines themselves are reorganized.

6. Itemized amount validation: The sum of all itemized_amount values for an invoice must not exceed the invoice's amount. This is enforced at the application layer (not as a database constraint) because cross-row aggregate constraints are not expressible as CHECK constraints in SQLite. The API returns 400 ITEMIZED_SUM_EXCEEDS_INVOICE when this rule is violated.

7. "Remaining" amount is computed, not stored: The difference between the invoice total and the sum of itemized amounts is computed at the API layer and returned as remainingAmount in responses. Storing it would create a denormalization risk.

Alternatives Considered

A. Polymorphic discriminator column (budget_type + budget_id)

Similar to document_links, use a single budget_id column with a budget_type discriminator ('work_item' | 'household_item'). This loses the FK constraint enforcement. Given that we only have two target tables and both have stable schemas, typed FK columns with a CHECK constraint provide stronger guarantees.

B. Two separate junction tables

invoice_work_item_budgets and invoice_household_item_budgets. This avoids the XOR constraint but doubles the schema surface, complicates the API (two endpoints instead of one), and makes cross-entity queries (e.g., "all budget lines for this invoice") require UNION queries. The single-table approach is simpler for this scale.

C. Keep 1:1 FKs, add amount tracking to the FK

Add an itemized_amount column to invoices alongside the existing FKs. This does not solve the many-to-many requirement (one invoice, multiple budget lines).

Data Migration Strategy

The migration (0017) migrates existing data by:

1. Creating invoice_budget_lines rows from existing invoices.work_item_budget_id and invoices.household_item_budget_id values
2. Setting itemized_amount equal to the invoice amount (preserving the 1:1 semantics for existing data)
3. Recreating the invoices table without the budget FK columns

This is backward-compatible: existing invoices retain their budget line links through the junction table.

Consequences

Easier

• Linking multiple budget lines to a single invoice (the primary use case)
• Per-category cost attribution from a single invoice
• Accurate subsidy calculations based on itemized amounts per category
• Budget overview calculations using actual itemized costs

More Difficult

• Invoice CRUD is more complex (junction table management alongside invoice create/update/delete)
• actualCost computation changes from a simple FK-based SUM to a junction table JOIN
• All existing code that references invoice.workItemBudgetId or invoice.householdItemBudgetId must be updated
• The budget line delete guard must check the junction table instead of the direct FK
• Subsidy calculations must use itemized amounts (where available) as the cost basis