# ADR-037: transaction_lines Uses Soft Polymorphism — source_id Has No FK Constraint

## Status

Accepted, 2026-06-22.

## Status History

```yaml
status_history:
  - date: 2026-06-22
    status: Proposed
    changed_by: hkl
    reason: Formalising polymorphic FK decision in custodian.transaction_lines
    changed_via: adr-kit (360lm)
  - date: 2026-06-22
    status: Accepted
    changed_by: hkl
    reason: Pattern live in custodian.transaction_lines; referenced by advance/receipt/expense rows
    changed_via: adr-kit (360lm)
```

## Context

`custodian.transaction_lines` records individual line items attached to financial transactions (advance payments to vendors, receipt captures, expense line breakdowns). A line item belongs to one transaction but its "source" (the parent record that generated it) can be of different types: a vendor advance, a petty cash receipt, or an expense sheet. A standard FK can only point to one table. Options are: (a) separate typed tables (one per source type), (b) multiple nullable FKs (one per type, exactly one populated), or (c) a `source_type` enum + `source_id` with no DB FK constraint (soft polymorphism). Option (c) was chosen for flexibility and simplicity.

## Decision

`custodian.transaction_lines` stores:
- `source_type TEXT` — enum of valid parent types (`'advance'`, `'receipt'`, `'expense'`)
- `source_id BIGINT` — ID of the parent record in the corresponding table; NO foreign key constraint in the DB

Application code is responsible for:
- Always setting both columns together (no orphaned `source_id` without `source_type`).
- When deleting a parent transaction, explicitly deleting its lines (`DELETE FROM custodian.transaction_lines WHERE source_type = $1 AND source_id = $2`) — there is no CASCADE because there is no FK.
- Validating `source_type` values in the application or as a CHECK constraint (not as a FK).

**Known gotcha:** `transaction_lines.source_id` is polymorphic (no FK). When deleting a parent transaction, lines need explicit DELETE. This is documented in code and dbt_vendors.md.

**Decision Maker:** hkl

## Alternatives Considered

- **Separate typed tables (advance_lines, receipt_lines, expense_lines).** Rejected: identical columns duplicated across 3+ tables; any schema change (add a "quantity" column) must be applied to all tables; query to get "all lines for a payee" requires UNION across tables.
- **Multiple nullable FK columns (advance_id, receipt_id, expense_id — exactly one populated).** Rejected: schema grows a new nullable column per source type; check constraint required to enforce exactly-one; JOIN queries require COALESCE across all FK columns; adding a new source type requires an ALTER TABLE.
- **Postgres table inheritance or partitioning.** Rejected: inheritance in PG adds complexity to PostgREST queries (base table queries don't automatically include child rows in standard PostgREST); partitioning is for performance on single-type tables, not polymorphism.

## Consequences

**Positive:**
- Adding a new source type requires no schema migration — just a new valid `source_type` value.
- Single table query for "all lines of type X" or "all lines for transaction Y" — no UNION needed.
- Works cleanly with PostgREST filtering (`?source_type=eq.advance&source_id=eq.123`).

**Negative / Trade-offs:**
- No referential integrity at DB level — a `source_id` can reference a deleted or non-existent parent; DB will not catch this.
- Deleting a parent record MUST be accompanied by explicit line deletion — easy to forget, especially in admin data repair scenarios.
- `source_type` values are soft enum (TEXT, not PG ENUM) — typos in source_type create silent ghost lines.

**Risks and mitigations:**
- Orphaned lines after parent deletion: mitigated by documentation (this ADR, dbt_vendors.md gotcha), and a DB CHECK constraint on valid source_type values; periodic audit query can find orphans.
- source_type typo: mitigated by defining valid values as application-layer constants, not free-form strings.

## Related Decisions

- ADR-036 (custodian.payees shared payee master) — transaction_lines are linked to payees via the parent transaction.
- ADR-007 (SECURITY DEFINER triggers) — any trigger on transaction_lines that cross-writes must use SECURITY DEFINER.
- ADR-038 (capture-to-catalog trigger) — the `fn_line_enrich_catalog` trigger fires on transaction_lines INSERT.

## References

- `memory/dbt_vendors.md` — "Known Gotchas: transaction_lines.source_id is polymorphic (no FK)"
- `finance/custodian/index.html` — transaction_lines INSERT with source_type + source_id
- `finance/vendors/index.html` — advance line capture using transaction_lines