#94 Budget storage is fragmented across 4 stores — unify entry paths into one canonical monthly budget fact (manual spread = identity profile)

[Anonymous]

Originally created by: grynn-in

Problem

How a budget is entered currently determines where it is stored, and the downstream readers (K.EPM, the consolidated/scenario trial balance, reports) do not agree on a single source. There is no canonical budget fact. As a result, a budget entered one way is invisible to a reader wired to another store.

Concretely observed: a full FY2024 budget loaded via the monthly-direct path (budget_save_batch → Budget Sheet → cycle lock) lands in epm_gold.budget_monthly_input and is completely invisible to K.EPM, because K.EPM's budget_input fact reads epm_gold.gold_spread_budget (the annual+spread path's output). Same scenario name, same grain, different table → returns 0.

This violates the intended UX: users should be able to spread budgets themselves (manual monthly) or via a profile (annual × weights), and the result should land in the same place regardless of who/how it was spread.

Current state — four disconnected budget stores

Entry path	Spread by	Lands in	Read by
Monthly direct — budget_save_batch / budget_cell_save → Budget Sheet → on_submit sync	user, by hand	epm_gold.budget_monthly_input (budget_sheet.py:27 CLICKHOUSE_TABLE, sync :87)	nobody downstream
Annual + profile (PRD-6) — budget_annual_input → dbt	spread_profiles weights	epm_gold.gold_spread_budget (models/gold/gold_spread_budget.sql)	K.EPM (budget_input fact → clickhouse_table=epm_gold.gold_spread_budget)
Write-back / API staging	—	epm_staging.budget_input (DDL only, clickhouse/init-db.sql; ~1 placeholder row, no app writer)	gold_scenario_trial_balance.sql:47-52 (source('epm_staging','budget_input'))
D365	—	silver_budget_entries	gold_scenario_trial_balance.sql:33-44 (BUDGET branch)

So gold_scenario_trial_balance reads stores [#3] (empty) and [#4] (D365) but not [#1] or [#2], and K.EPM reads only [#2]. No path reads budget_monthly_input at all.

Proposed design — unify via "identity spread"

Key insight: a manual monthly entry is just a spread whose 12 weights are the numbers the user typed. Both entry modes are the same operation; only the source of the weights differs. Therefore they can share one model and one output table.

1. One canonical monthly budget fact — extend gold_spread_budget (or introduce gold_budget) to be the single source of truth, at grain (scenario_id, data_area_id, fiscal_year, fiscal_period, main_account, <budget dims>). It is a UNION of:
2. annual × profile — existing PRD-6 spread logic (budget_annual_input × normalized spread_profiles weights); and
3. manual monthly — rows from the monthly-direct path (budget_monthly_input), modeled as a per-line custom/identity profile (profile_id = 'manual', period_weight = period_amount / annual, so the same columns/semantics as the profile path).
4. Single write convergence — the monthly-direct path (budget_sheet._sync_to_clickhouse) and the annual path should both feed the canonical model's inputs, so locking a cycle makes manual entries first-class budget data (not a dead-end table).
5. Point all readers at the one model:
6. K.EPM budget_input fact clickhouse_table → the canonical model (already there if we extend gold_spread_budget).
7. gold_scenario_trial_balance API/input branch → read the canonical model instead of the empty epm_staging.budget_input placeholder.
8. Retire / repurpose epm_staging.budget_input (currently DDL-only and unwired) so it stops masquerading as a live source.

This makes "who spread it / how it was spread" irrelevant to where it lands, and incidentally fixes the invisible-budget bug.

Acceptance criteria

• [ ] A budget entered via manual monthly (budget_save_batch + cycle lock) and a budget entered via annual + profile both appear in one canonical budget table at monthly grain, with identical column semantics.
• [ ] K.EPM(entity, year, period, account, "period_amount", "budget", …, scenarioId) returns the value for both entry modes (no zeros for manually-entered budgets).
• [ ] gold_scenario_trial_balance includes manually-entered + profile-spread budgets for the requested scenario_id (so consolidated/variance reporting sees them).
• [ ] A manual monthly entry is representable as a spread profile (profile_id='manual' or equivalent) with period_weight summing to 1.0 per line; FY total reconciles to the sum of 12 periods.
• [ ] epm_staging.budget_input is no longer a silent dead source (removed, or actually populated by the convergence path).
• [ ] Regression: existing demo BUDGET_2025 (profile-spread) values are unchanged after unification.

Evidence / references

• budget_input fact → clickhouse_table: epm_gold.gold_spread_budget (konsol-cli fact show budget_input).
• Monthly-direct target: konsol/epm/doctype/budget_sheet/budget_sheet.py:27 (CLICKHOUSE_TABLE = "epm_gold.budget_monthly_input"), sync at :87.
• Spread model: dbt_project/models/gold/gold_spread_budget.sql (from budget_annual_input × spread_profiles).
• Scenario fact readers: dbt_project/models/gold/gold_scenario_trial_balance.sql:33-52.
• Empty placeholder: epm_staging.budget_input created by clickhouse/init-db.sql (MergeTree, ~1 row, no app writer).
• Repro: load FY2024 budget via konsol.api.budget_save_batch → lock cycle → K.EPM(...,2024,...,"budget",...,"BUDGET_2024") returns 0, while the rows exist in epm_gold.budget_monthly_input.

[Anonymous]

Originally posted by: grynn-in

Resolved by [#99]. gold_spread_budget is now the single canonical monthly budget fact (see its header comment):

• UNIONs both entry methods into the same table/grain: (a) annual + profile spread (PRD-6), and (b) manually-entered monthly (epm_gold.budget_monthly_input) treated as an identity spread (profile_id = 'manual').
• Read identically by K.EPM's budget_input fact and gold_scenario_trial_balance — so a budget is visible regardless of how it was entered (the original symptom: monthly-direct invisible to K.EPM).
• Budget layers (base/challenge/management/board) carried as rows with a layer column; K.EPM sums by default, filters when a layer is passed.
• The dead epm_staging.budget_input store was removed (34b0cb1).

The four-store fragmentation is unified. Closing.

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed