#200 Trial balances normalised to period movements from a declared amount basis (#199)

[Anonymous]

Originally created by: grynn-in

Fixes [#199] (warehouse side): trial-balance rows are normalised to period movements from each batch's declared amount basis.

What was wrong

Every model downstream reads a trial-balance row as a period movement (gold_balance_sheet running sums, gold_ytd_trial_balance, PRD-22's own header). ERPs export three different things: period movements, year-to-date movements, or period-end balances. Nothing declared which a batch was. On a site that uploaded period-end balances, share capital of one subsidiary showed −10.6M, −21.2M, −30.9M over three year-ends: every balance-sheet figure was the sum of all prior year-ends.

The fix

konsol (its PR) puts amount_basis on the claim row of epm_raw.trial_balance_submission_control: Period movement, Year-to-date movement or Period-end balance, never a default. This PR:

• DDL: the control table gains amount_basis String DEFAULT '' (byte-equivalent to konsol's _RAW_TABLE_DDL; tests/test_raw_submission_ddl.py pins both, and runs in CI). The CI fixture names its claim columns and declares a basis.
• Bronze carries each claimed batch's basis (read only when the column exists, so the two repos deploy in either order); assert_tb_submission_has_basis (error) names a claimed batch with an empty or unknown basis instead of guessing.
• silver_tb_movements (new) normalises every batch once, on a spine of entity periods × entity keys (account, partner): Period movement passes through; Year-to-date differences within the fiscal year; Period-end balance differences against the previous period with data, any year. A key that vanishes produces its closing movement. Two invariant tests: assert_tb_movements_balance (each entity-period nets to zero) and assert_tb_movements_cumulate_to_source (the movements add back to what the batch declared).
• silver_gl_entries reads the movements instead of bronze; nothing downstream changes.
• assert_entity_has_one_amount_basis (error): one entity's batches must share one basis.
• Docs: silver_tb_movements in the silver data dictionary, an "Amount Basis" paragraph in the deployment guide, fixture README notes.

Evidence

Test first, all in throwaway zzg_* schemas inside the stack (live tables untouched):

• D1 DDL test: FAILED (failures=2) on main → OK (7991732 → ca8cf32).
• D2 assert_tb_submission_has_basis: Database Error on main (no column) → PASS with a declared claim; Got 2 results for '' and Balances (0155aeb → 63ca80c).
• D3 fixtures for the three bases, each with a vanishing account: model absent on main → both invariant tests PASS on all three (acc8b32 → 7ac433d). Hand check of the balance case: balances 150/−50/−100 → 170/−30/−140 → 200/–/−200 give movements +20/+20/−40 and +30/+30/−60; each period nets to zero.
• D4 live clone of the stack's data with every batch declared Period-end balance: +silver_gl_entries builds green (62/62). One parent's share-capital account: declared balances −391.0M (2010), −1,087.3M, −1,215.4M, −1,392.6M; movements −391.0M, −696.3M, −128.1M, −177.2M — the year-over-year change.
• D5 assert_entity_has_one_amount_basis: Got 1 result on a mixed entity, PASS on a clean one (5701bc3 → 37b5d4a).

Deploy order

Merge and fast-forward this after konsol's Set Amount Basis action has declared the existing batches on the stack; until a batch has a basis the build stops at assert_tb_submission_has_basis, by design.

🤖 Generated with Claude Code

https://claude.ai/code/session_013WewQKFQgG7o2M3mUDPRR5

[Anonymous]

Originally posted by: grynn-in

Review round 1: four points.

1. Accepted (real). For Period-end balance files, P&L accounts reset each fiscal year, so differencing them across the year boundary puts −(prior-year result) into the first period of the new year while every period still nets to zero. Fix in progress on this branch: an explicit year-end close. For each finished year of a balance-basis entity the model synthesises the post-close position in the calendar's Closing period (P&L keys to 0, the result into the chart's declared retained-earnings account), so the close appears as its own movement (posting_layer = 'Year-end close') and the next year starts from zero. The retained-earnings account becomes a declared flag on the group chart (konsol, is_retained_earnings, one per chart); where it or the Closing period is missing, assert_year_end_close_declared names the entity-year instead of guessing. Two-year fixture with a P&L account, red first.
2. Accepted: the comment on assert_tb_movements_cumulate_to_source will say that the balance test is what catches a missing spine.
3. Accepted: model comment will state the dbt build dependency.
4. Agreed, no change.
   Also noted for the PR body: two source rows for one (account, partner) now collapse to one GL row with an empty description when their descriptions differ.

[Anonymous]

Originally posted by: grynn-in

Round-1 fixes pushed (D6, D7b, D7):

• D6 is_retained_earnings reaches silver_main_accounts (read only when the staging table has the column; DDL pinned to konsol's body).
• D7b epm_staging.fiscal_periods (the declared fiscal calendar konsol writes) is now a warehouse source: init-db DDL, dbt source, CI seed, DDL test.
• D7 the year-end close: for each finished year of a balance-basis entity, a post-close position is synthesised in the year's Closing period (P&L keys to 0, the result into the retained-earnings account), so the close is its own movement (movement_kind = 'year_end_close', posting_layer = 'Year-end close') and the next year's P&L starts from zero. assert_year_end_close_declared (error) names entity-years that need a close but lack a Closing period or a single retained-earnings account. Fixture: 2025 P12 cash 100 / revenue −100 → 2026 P1 cash 130 / revenue −30 / RE −100 gives P13 revenue +100 / RE −100 and P1 +30 / −30 / 0. On a clone of the stack's data the only failure is that test (577 entity-years: the live chart has no retained-earnings flag yet — konsol's PR adds it; flag account 3100 and it clears).

[Anonymous]

Originally posted by: grynn-in

Re-review round 2: three findings, all accepted, in progress as one row (D8): (1) assert_year_end_close_declared only flags entity-years with a non-zero P&L to close, with a balance-sheet-only must-pass fixture; (2) a warn-level assert_year_end_close_carried names a next-year file that omits the flagged retained-earnings account, plus a docs sentence that the flagged account must be the one the ERP's files carry the result in; (3) the Closing period must sort after the year's last claimed period (model and test).

[Anonymous]

Originally posted by: grynn-in

Round-2 fixes pushed (D8, D8b, D8c): assert_year_end_close_declared flags only entity-years with a non-zero P&L balance at their last period (bs-only must-pass fixture; a net-zero-result fixture still flagged); assert_year_end_close_carried (warn) names a next-year file that omits the flagged retained-earnings account; the Closing period must sort after the year's last claimed period (model and test); docs reworded.

[Anonymous]

Originally posted by: grynn-in

Round 3: two small points, both fixed in the last commit — the reason now says when a batch was claimed in the Closing period while P&L is still open (fix the file, not the calendar), and the model header says "any non-zero P&L balance". All four fixture gates re-run green.

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed