#186 Governed group chart drives classification and translation (konsol#182 PR2 + PR3)

[Anonymous]

Originally created by: grynn-in

Part of grynn-in/konsol#182. This PR is the plan's PR2 and PR3 combined.

Decisions

1. Product decision, verbatim:

   "i dont want anything to do with erpnext or airbyte or d365. I want to freshly look at it. For now the canonical path is upload of the trial balance via CSV. Any source from now on has to follow the shape defined by konsol."

So the chart has one source: the group chart governed in konsol.

1. Translation follows the declaration in this PR. A declared fx_method that translation ignored would be exactly the silent mismatch this work removes. With the ERP path gone, PR3's other half (moving the ERP literals into the adapter) no longer applies, so PR3 is folded in here.

What's in

Chart (plan PR2)

• silver_main_accounts reads only epm_staging.main_accounts (konsol's Main Account, Published leaves). There is no bronze_main_accounts read, no D365 enum decoding, and no ERP fallback. An account that is not declared is not in silver, and its trial-balance rows reach neither statement. Heading nodes are left out.
• Same 11 contract columns (names, types, order), filled from the declaration:
• account_type_name = account_type;
• is_pnl and is_balance_sheet from statement_section;
• is_equity = fx_method = 'historical';
• debit_credit_default = normal_balance.

Eleven new columns follow: statement_section, sub_section, normal_balance, time_balance, fx_method, is_posting, parent_account, allow_ic, cf_category, cf_line_item, is_cash. There is no chart_origin: it would always be 'konsol', and nothing reads it.

• macros/governed_chart.sql holds three macros:
• governed_chart_relation(): with the table absent, silver is empty and keeps every column and type;
• governed_chart_problems();
• governed_chart_guard(): a pre_hook that refuses the build before silver is replaced when a Published declaration is unusable. That covers:
  • Published duplicates that differ in any declared column, with the differing columns named;
  • a section or fx_method outside the vocabulary;
  • P&L at the historical rate, or balance sheet at the average rate.
• Deterministic dedup. Only identical copies get past the guard, and SELECT DISTINCT collapses them. limit 1 by, which picked an arbitrary copy, is gone. If the guard were ever bypassed, both versions would survive and unique_silver_main_accounts_main_account_id would name the code.
• DDL in clickhouse/init-db.sql, byte-identical to konsol's _REFERENCE_TABLE_DDL entry, and the dbt source entry.

Translation (plan PR3)

• gold_consolidated_trial_balance joins silver_main_accounts for fx_method. The rate is:
• the as-of historical rate when historical and a tranche exists;
• the average rate when average;
• otherwise the closing rate. That covers closing, historical before the first tranche, and an undeclared account, as today.

is_equity = toUInt8(fx_method = 'historical'). A P&L account declared at closing (IAS 29) now translates at closing. No output column added. fx_method stays out of rated and consolidated, so the positional append and partner_data_area_id as the last column are untouched. DESCRIBE is identical to main.

Tests

• New CI job warehouse contract tests in .github/workflows/dbt-checks.yml runs python -m unittest -v tests.test_governed_chart_ddl. It uses plain unittest and needs no installs. clickhouse/init-db.sql and the test file are added to the workflow's paths. The test checks that:
• the init-db line equals konsol's DDL string;
• silver's governed branch reads every DDL column, matched as whole tokens (a dropped main_account no longer "passes" by matching inside main_account_category);
• the guard's duplicate comparison lists every DDL column except main_account and status.
• New warn tests:
• assert_translation_follows_fx_method: translated rows only, tolerance 1e-6;
• assert_undeclared_accounts_in_trial_balance: the key one; it names each trial-balance account missing from the konsol chart, with entities and periods;
• assert_governed_chart_declarations_usable and assert_governed_chart_grain_unique;
• accepted_values on fx_method and statement_section.
• Deleted:
• assert_bs_uses_closing_rate and assert_pnl_uses_average_rate, which tied the rate to the statement.
• assert_gl_accounts_in_chart, merged into assert_undeclared_accounts_in_trial_balance. Its LEFT JOIN … IS NULL form never fires under join_use_nulls=0, and the NOT IN rewrite would have been the undeclared test again.
• Updated:
• assert_favorable_revenue / _expense and gold_variance_analysis's favourability now match only 'Revenue' and 'Expense'. The generic Profit and loss type has no favourable direction, by design.
• The header of assert_every_account_is_pnl_or_balance_sheet.

The docs that listed the deleted tests now name their replacements.

Left alone

• The D365 and ERPNext staging and bronze models, the Airbyte code, and macros/source_adapters/d365_account_types.sql all stay, unused by this path. map_account_type now has no caller; it stays in place, and removing it is a separate decision.
• gold_variance_analysis's outer-join defect is filed as [#187]: under join_use_nulls=0, budget-only rows lose their keys and actual-only rows look budgeted.

Evidence

Built on the test stack into scratch schemas only. Sources were redirected to kab_182_src_*, which have the live structure and no live rows; nothing in epm_* was written. The selection was dbt run --select +gold_consolidated_trial_balance+ silver_main_accounts+ --exclude gold_spread_budget+ --full-refresh, and every build reported OK created for silver_main_accounts and gold_consolidated_trial_balance; gold_variance_analysis was built separately with its upstream. Every scratch database and container copy has been removed. Comparisons are multiset diffs of toString(tuple(*)). The plan's sum(cityHash64(*)) and count()-over-EXCEPT are unreliable on ClickHouse 24.8: the hash is NULL on rows with a NULL Nullable column, and count() over EXCEPT returns 0 under the new analyzer when rows differ.

Fixture: the konsol chart

Account	Declaration
ZZ0001	heading
ZZ4000	Revenue, P&L, average
ZZ1000	Asset, Balance Sheet, closing
ZZ3000	Equity, Balance Sheet, historical, with a 0.90 historical rate
ZZ5000	Expense, P&L, closing
ZZ6000	Draft only

A balanced FY2026 P1 trial balance for ZZOP (CHF, 100% owned by ZZGRP, USD) is landed the TB-submission way. It posts to all of these and to an undeclared ZZ9999. Governed rates: Closing 1.10, Average 1.05.

Check	Result
DESCRIBE gold_consolidated_trial_balance	identical to main; partner_data_area_id still last
Rate per account vs expected	0 differing rows. ZZ1000 BS closing 1.10; ZZ3000 equity historical 0.90; ZZ4000 P&L average 1.05; ZZ5000 P&L closing 1.10 (1.05 with PR2 alone); ZZ6000 and ZZ9999 undeclared, closing 1.10
PR2 alone vs PR2+PR3	18/20 tables identical. Only the ZZ5000 row of the consolidated TB and the CTA (exactly -(400 x 0.05)) differ
Mutation: ZZ3000 re-declared closing	only ZZ3000's rows change, and CTA moves by exactly 800 x 0.20
assert_translation_follows_fx_method	PASS; WARN 1 on PR2-alone code, naming ZZ5000
assert_undeclared_accounts_in_trial_balance	WARN 2, naming ZZ9999 and ZZ6000 (entities and periods); PASS once both are published
Review fixes vs the reviewed head (ad14af5)	20/20 tables identical on clean data
Guard: duplicate differing only in account_name	refused: ZZ4000: duplicate Published rows disagree on account_name; silver keeps its rows and metadata_modification_time; declarations and grain tests WARN 1
Guard: duplicate differing in normal_balance and allow_ic	refused: ZZ1000: duplicate Published rows disagree on normal_balance, allow_ic
Guard: Published leaf with section X	refused: ZZBAD: statement_section [X] is neither Profit and Loss nor Balance Sheet
Identical duplicate	builds; one silver row; unique PASS; assert_governed_chart_grain_unique WARN 1
Python test mutations (locally, the CI command)	red on each of: silver dropping main_account, silver dropping allow_ic, the guard dropping account_name, a changed DDL type; green unmutated
Other tests	equity-rate tests, pnl-or-balance-sheet, accepted_values, not_null and unique PASS; assert_favorable_revenue / _expense PASS with the edited gold_variance_analysis

The same 6 models error, and 10 skip, in this selection on main as on the branch: alloc_results (a syntax error with no allocation rules), and gold_pnl_half_yearly, gold_pnl_quarterly, gold_tb_at_hierarchy_node, gold_unassigned_hierarchy_members and gold_fully_consolidated_tb, each of which depends on a model outside the selection. None of them is from this change.

Deploy

Deploying this PR empties silver_main_accounts on every site until a group chart is published in konsol. Until then no account reaches either statement, and assert_undeclared_accounts_in_trial_balance names every trial-balance account. That is intended under the new direction. Deploy order matters in one direction: deploy konsol#183 with or before this PR. This PR alone empties the chart, and without [#183] there is no Main Account to publish one from. The reverse (#183 first) is safe: governed_chart_relation() reads a missing table as "no chart". Deploy with ./deploy.sh.

🤖 Generated with Claude Code

https://claude.ai/code/session_013WewQKFQgG7o2M3mUDPRR5

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed