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 CASCADE,
  household_item_budget_id TEXT REFERENCES household_item_budgets(id) ON DELETE CASCADE,
  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 CASCADE from budget lines: When a budget line is deleted, its junction rows are also deleted. Originally ON DELETE SET NULL was planned, but SQLite enforces CHECK constraints during FK SET NULL actions — clearing a budget FK to NULL violates the XOR CHECK constraint (both FKs become NULL). CASCADE is simpler and consistent with the invoice→junction CASCADE pattern. If budget lines are reorganized, the invoice's itemized breakdown is reset and the user re-links as needed.

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