Backing transaction ledger: replace applied field with Postings #19

New issue

Closed

opened 2026-04-17 17:24:46 -06:00 by claude-code · 0 comments

claude-code commented

2026-04-17 17:24:46 -06:00

Collaborator

Goal

Replace the hand-edited applied column on month_entry with a backing ledger of individual transactions (Postings). Applied becomes a derived sum. Planned becomes click-to-edit instead of an always-on input. Some entries will have one posting per month (mortgage); others will have many (groceries).

Schema

New table posting:

id              INTEGER PK
month_entry_id  INTEGER NOT NULL FK -> month_entry(id) ON DELETE CASCADE, INDEX
occurred_on     DATE NOT NULL
amount          NUMERIC(10,2) NOT NULL   (negative allowed: refunds / corrections)
description     TEXT NULL
payee           TEXT NULL
created_at      TIMESTAMP
updated_at      TIMESTAMP

Model class Posting. UI and docs continue to call them "transactions"; the schema/ORM names use the accounting-correct term.

Drop `month_entry.applied`

Applied is computed as SUM(amount) over the entry's postings. No stored column.

Migration (single alembic revision)

Create posting table.
For every month_entry with a non-zero applied value, insert one "opening balance" posting:
- occurred_on = month.activated_at OR month.created_at
- amount = month_entry.applied
- description = "opening balance"
- payee = NULL
Drop the applied column from month_entry.

Closed months go through the same migration path; their totals stay correct via the opening-balance posting.

Service layer

month_service.add_posting(db, month, entry, date, amount, description=None, payee=None) -> Posting
month_service.update_posting(db, month, posting_id, **fields) -> Posting | None
month_service.delete_posting(db, month, posting_id) -> bool
entry.applied becomes a computed property / dataclass field sourced from summing postings.
update_month_entry no longer accepts applied; the keyword is removed.
ensure_editable(month) still gates every mutation.

Routes

POST   /month/{ym}/entries/{entry_id}/postings            add
POST   /month/{ym}/postings/{posting_id}                  edit (date/amount/description/payee)
DELETE /month/{ym}/postings/{posting_id}                  delete

Each returns the updated section partial + OOB swaps for zero widget and group totals (same pattern as existing mutations). ensure_editable guard applies.

POST /month/{ym}/entries/{id} loses the applied form field but keeps name / planned / notes.

UI

Planned: rendered as static text. Click to enter inline edit mode (HTMX + small input overlay, change-to-commit). Matches existing notes edit pattern.
Applied: rendered as static text, never editable directly. Shows a small tracked-caps count next to it: $412.33 · 7 txns when postings exist.
Row expansion: a hairline caret in the row's left gutter toggles an expanded view below. Adds no horizontal space when collapsed; vertical only when expanded.
Expanded view: compact transactions table ordered by occurred_on DESC with columns date / description / payee / amount / delete. Inline add form at the bottom with date (defaults to today), amount, optional description, optional payee, submit.
Users see "transactions" labels throughout. Schema term "posting" stays behind the scenes.

Validation

occurred_on: must parse as ISO date (YYYY-MM-DD); free-form, NOT constrained to the month for now.
amount: any decimal, negative allowed. Zero also allowed.

Deviation state

Adding / editing / deleting postings does NOT flip the row's deviation state. Deviation tracks drift from the snapshot (name, planned), not the flow of transactions.

Zero amount, progress bars, group totals

All keep their current semantics, reading through the computed applied. Close gate stays; balanced at $0.00 applied zero still required to close.

Acceptance criteria

Alembic migration creates posting, seeds opening-balance postings, drops month_entry.applied
Posting model with cascade from month_entry
Service CRUD + derived applied in all views / dataclasses
Three new routes (add / update / delete); legacy update route rejects applied
UI: planned is click-to-edit; applied is derived; rows collapse/expand; transaction table + add form render in expanded view
OOB updates on zero widget and group totals fire on every posting mutation
Closed months reject all posting mutations with 400
Pytest covers: migration seeds correct opening balances, derived applied sums, deviation unaffected by postings, close gate still works via derived sum, edit locking on closed months
No regression on existing tests (service, routes, groups, lifecycle, notes, zero amount)

Out of scope

Date constraint (postings must fall within the month)
Tags or categories beyond payee
CSV import / bulk entry
Recurring posting schedules
Bank-statement reconciliation

## Goal Replace the hand-edited `applied` column on `month_entry` with a backing ledger of individual transactions (Postings). Applied becomes a derived sum. Planned becomes click-to-edit instead of an always-on input. Some entries will have one posting per month (mortgage); others will have many (groceries). ## Schema New table `posting`: ``` id INTEGER PK month_entry_id INTEGER NOT NULL FK -> month_entry(id) ON DELETE CASCADE, INDEX occurred_on DATE NOT NULL amount NUMERIC(10,2) NOT NULL (negative allowed: refunds / corrections) description TEXT NULL payee TEXT NULL created_at TIMESTAMP updated_at TIMESTAMP ``` Model class `Posting`. UI and docs continue to call them "transactions"; the schema/ORM names use the accounting-correct term. ### Drop `month_entry.applied` Applied is computed as `SUM(amount)` over the entry's postings. No stored column. ### Migration (single alembic revision) 1. Create `posting` table. 2. For every `month_entry` with a non-zero applied value, insert one "opening balance" posting: * `occurred_on = month.activated_at OR month.created_at` * `amount = month_entry.applied` * `description = "opening balance"` * `payee = NULL` 3. Drop the `applied` column from `month_entry`. Closed months go through the same migration path; their totals stay correct via the opening-balance posting. ## Service layer * `month_service.add_posting(db, month, entry, date, amount, description=None, payee=None) -> Posting` * `month_service.update_posting(db, month, posting_id, **fields) -> Posting | None` * `month_service.delete_posting(db, month, posting_id) -> bool` * `entry.applied` becomes a computed property / dataclass field sourced from summing postings. * `update_month_entry` no longer accepts `applied`; the keyword is removed. * `ensure_editable(month)` still gates every mutation. ## Routes ``` POST /month/{ym}/entries/{entry_id}/postings add POST /month/{ym}/postings/{posting_id} edit (date/amount/description/payee) DELETE /month/{ym}/postings/{posting_id} delete ``` Each returns the updated section partial + OOB swaps for zero widget and group totals (same pattern as existing mutations). `ensure_editable` guard applies. `POST /month/{ym}/entries/{id}` loses the `applied` form field but keeps name / planned / notes. ## UI * **Planned**: rendered as static text. Click to enter inline edit mode (HTMX + small input overlay, change-to-commit). Matches existing notes edit pattern. * **Applied**: rendered as static text, never editable directly. Shows a small tracked-caps count next to it: `$412.33 · 7 txns` when postings exist. * **Row expansion**: a hairline caret in the row's left gutter toggles an expanded view below. Adds no horizontal space when collapsed; vertical only when expanded. * **Expanded view**: compact transactions table ordered by `occurred_on DESC` with columns date / description / payee / amount / delete. Inline add form at the bottom with date (defaults to today), amount, optional description, optional payee, submit. * **Users see "transactions" labels** throughout. Schema term "posting" stays behind the scenes. ## Validation * `occurred_on`: must parse as ISO date (`YYYY-MM-DD`); free-form, NOT constrained to the month for now. * `amount`: any decimal, negative allowed. Zero also allowed. ## Deviation state Adding / editing / deleting postings does NOT flip the row's deviation state. Deviation tracks drift from the snapshot (name, planned), not the flow of transactions. ## Zero amount, progress bars, group totals All keep their current semantics, reading through the computed applied. Close gate stays; balanced at `$0.00 applied zero` still required to close. ## Acceptance criteria * [ ] Alembic migration creates `posting`, seeds opening-balance postings, drops `month_entry.applied` * [ ] `Posting` model with cascade from `month_entry` * [ ] Service CRUD + derived applied in all views / dataclasses * [ ] Three new routes (add / update / delete); legacy update route rejects `applied` * [ ] UI: planned is click-to-edit; applied is derived; rows collapse/expand; transaction table + add form render in expanded view * [ ] OOB updates on zero widget and group totals fire on every posting mutation * [ ] Closed months reject all posting mutations with 400 * [ ] Pytest covers: migration seeds correct opening balances, derived applied sums, deviation unaffected by postings, close gate still works via derived sum, edit locking on closed months * [ ] No regression on existing tests (service, routes, groups, lifecycle, notes, zero amount) ## Out of scope * Date constraint (postings must fall within the month) * Tags or categories beyond `payee` * CSV import / bulk entry * Recurring posting schedules * Bank-statement reconciliation