ADR 030 Orphan Budget Lines And Origin Column

ADR-030: Orphan Budget Lines via Nullable Parent FK + origin Discriminator

Status

Accepted

Context

Cornerstone's budget model groups every budget line under either a work item (work_item_budgets.work_item_id) or a household item (household_item_budgets.household_item_id). Both columns were NOT NULL and the link was treated as immutable after creation — every itemization on an invoice had to be hand-typed and parented at the moment of entry.

Two new requirements broke this contract:

1. Auto-itemization of Paperless OCR invoices (Story #1547). When the system extracts line items from an OCR'd invoice it does not know which work item or household item each line belongs to. The user assigns them afterwards. The extraction must persist something so the user can review and edit the lines, which means a budget line row that has no parent at creation.
2. Manual draft itemization (Story #1545). Even without LLM extraction, the user may want to capture line items on an invoice the moment they see the PDF — before they have decided which work item each line will roll up against. Today they have to invent a placeholder work item or skip itemization until the rest of the project is fleshed out.

Story #1546 adds a third concern: distinguishing how a budget line was created. Auto-extracted lines may be lower-confidence than hand-typed ones; the "Replace" mode in the auto-itemize flow needs to clear only auto-extracted lines so the user's manual itemizations on the same invoice are preserved.

Decision

Two coordinated schema changes ship in migration 0036_orphan_work_item_budgets.sql:

1. Make work_item_budgets.work_item_id nullable

A NULL work_item_id represents an orphan / unassigned budget line: it exists in the budget table and is linked to an invoice via invoice_budget_lines, but no parent work item.

• The FK constraint stays: work_item_id TEXT REFERENCES work_items(id) ON DELETE CASCADE. Cascade still fires when a referenced work item is deleted — orphans (NULL) are unaffected because there is no parent to cascade from.
• household_item_budgets.household_item_id stays NOT NULL. Household lines are always purchased for a specific household item; the auto-itemize flow lands lines as work-item-style orphans and lets the user reassign to a household item afterward via the one-shot assignment endpoint.

2. Add origin TEXT NOT NULL DEFAULT 'manual' to both budget tables

• 'manual' — user-created via the existing forms or the one-shot assignment endpoint.
• 'auto' — system-generated, currently reserved for the BudgetExtractionService output (#1546/#1547).
• Existing rows are backfilled as 'manual'.

3. Pair the orphan state with a one-shot assignment endpoint

POST /api/budget-lines/:id/assign (introduced in Story #1545) is the only way to give an orphan a parent. After assignment, the immutability invariant returns: the line cannot be reassigned. The endpoint distinguishes two paths:

• targetType: 'work_item' — single UPDATE of work_item_id.
• targetType: 'household_item' — transactional cross-table move (INSERT new household_item_budgets row with budget_category_id = 'bc-household-items', repoint the invoice_budget_lines junction, DELETE the orphan work_item_budgets row). The XOR check on the junction is preserved because the repoint happens before the delete.

Attempting to assign an already-parented line returns 409 BUDGET_LINE_ALREADY_ASSIGNED (dedicated BudgetLineAlreadyAssignedError, not the generic ConflictError).

4. Downstream service-layer treatment

Orphans are visible everywhere it makes sense and hidden where it doesn't:

• invoiceBudgetLineService.resolveDetail returns parentItemType: 'unassigned', parentItemId: null, parentItemTitle: null for orphan rows. The invoice budget-lines table renders them with an "Unassigned" badge and an inline "Assign…" action.
• budgetOverviewService excludes orphans from per-work-item rollups (WHERE work_item_id IS NOT NULL on the UNION feeding work item totals). Orphans still count toward source / category aggregates because they consume real money.
• budgetSourceService.buildWorkItemBudgetLine filters orphans out of its input list — BudgetSourceBudgetLine.parentId is a non-null contract field, so listing orphans there would lie.

5. Type-level signaling

• WorkItemBudgetLine.workItemId stays string in shared/src/types/workItemBudget.ts. The list endpoint /api/work-items/:id/budgets is nested under a specific work item — orphans cannot appear there, and consumers downstream rely on the non-null contract.
• InvoiceBudgetLineDetailResponse.parentItemType becomes 'work_item' | 'household_item' | 'unassigned' and parentItemId / parentItemTitle become string | null.
• BaseBudgetLine.origin?: 'manual' | 'auto' is added (optional for backwards compatibility with consumers that don't care).

Alternatives Considered

(a) Dedicated unassigned_budget_lines table. Cleaner separation but requires a third destination in every read path (invoice_budget_lines junction, budget overview UNION, budget source listing). The "assignment" then becomes a real cross-table move every time, not just for the work_item → household_item case. The nullable-FK approach reuses the existing work_item_budgets row format and confines the cross-table move to the rarer household_item path.

(b) Full reparent service. Allow any budget line to be reassigned at any time. Rejected to keep the audit trail simple and to match the user's deliberate "first assignment only" preference. Reassignment is a separate, larger feature; today's invariant — once parented, always parented — is preserved.

(c) Sentinel placeholder work item per invoice / household. A pseudo work item like wi-unassigned-<invoiceId> would let work_item_id stay NOT NULL. Rejected: clutters the work item table, breaks user-facing work item lists, requires cleanup when the line is finally assigned, and obscures the orphan state in every read query that filters or groups by work_item_id.

(d) Origin column on invoice_budget_lines (junction) instead of the budget tables. The junction is where invoices and budget lines meet, but origin is a property of how the budget line was created, not of how it was linked to an invoice. Putting it on the budget table also means future budget lines not linked to invoices (rare today, possible tomorrow) still carry the origin tag.

Consequences

Positive

• Stories #1546 and #1547 land cleanly on top of #1545: the LLM extraction writes orphans + origin = 'auto', the user assigns them via the existing one-shot endpoint.
• Manual draft itemization gets first-class support (the "I see the invoice, I'll figure out the work item later" flow).
• Downstream rollups stay consistent because the orphan filtering rules are explicit and audit-checked per service.
• 'auto' origin is the discriminator that lets "Replace" mode in #1547 delete only auto-extracted lines, preserving user-curated manual itemizations on the same invoice.

Negative / Watch-outs

• Every new read path against work_item_budgets must consider the orphan case. The audit at the time of writing this ADR covered invoiceBudgetLineService, budgetOverviewService, budgetSourceService, and workItemBudgetService. Any future service that joins work_item_budgets to work_items must handle a NULL work_item_id explicitly.
• The orphan row appears in source/category rollups but not work-item rollups — a discrepancy users may need help reading. UI surfaces ("Unassigned" badge, dedicated picker workflow) mitigate this.
• The cross-table move in the household_item assignment path is the most fragile new code path. It runs in a db.transaction and is covered by integration tests, but future changes to either budget table's schema must keep field parity with the move logic in budgetLineAssignService.assignToHouseholdItem.
• BaseBudgetLine.origin is optional in the type but NOT NULL in the DB. Treat the optional in TypeScript as a forward-compat seam; producers always populate it.

Related

• Migration 0036_orphan_work_item_budgets.sql
• server/src/services/budgetLineAssignService.ts
• server/src/routes/budgetLineAssign.ts
• ADR-018: Invoice Budget Line Junction Table (XOR invariant on invoice_budget_lines.workItemBudgetId / householdItemBudgetId)
• ADR-019: Discretionary Funding ('discretionary-system' source used as the default for auto-extracted lines)
• Issue #1545 (this story), #1546 (LLM gateway), #1547 (auto-itemize endpoint)