Skip to main content

Konsolidat — Roadmap

Last updated: 2026-06-19

See ../prd/README.md for the per-feature PRD index.

Status Summary​

AreaStatus
Data pipeline (Bronze → Silver → Gold)Done — 85 dbt models, 150+ tests
Consolidation (FX, IC elimination, CTA, NCI)Done — IFRS/GAAP compliant
Hierarchy, equity method, acquisition/disposalDone
Allocations (multi-step cascade)Done — dynamic N-step engine. Reciprocal & tiered engine macros exist but are not yet wired into gold_allocation_results (see Allocation enhancements)
Budget write-back (Excel → CH)Done — EPMSAVE() from Excel + Frappe API
Budget write-back (CH → ERP)Implemented — pushes approved budget to D365 BudgetRegisterEntries (d365_writeback.py, OData $batch replace); pending live-tenant validation
Scenario managementDone — budget/forecast/whatif via API
Variance analysisDone — actual vs budget with favorable logic
Excel VBA integrationDone — =EPM() + 6 functions (EPM, EPM_BUDGET, EPM_VARIANCE, EPM_DEBIT, EPM_CREDIT, EPMSAVE), ODBC + REST
Frappe app (konsol)Done — DocTypes, ClickHouse sync, background jobs
Docs site (Docusaurus)Done — docs.konsolidat.com, 65 pages
Custom domainDone — docs.konsolidat.com on Netlify
One-click deployDone — git clone && ./deploy.sh, full Docker stack
Multi-ERP canonical staging + D365 F&O adapterDone — 7 canonical models, 16 D365 F&O adapter models (PR #10)
Multi-ERP connectors (SAP, D365 BC, ERPNext)In progress — ERPNext connector done (source-erpnext/ + 7 staging models); SAP / D365 BC remaining
FastAPI / Streamlit / DagsterRetired — replaced by Frappe konsol app
Dynamic schema (dimensions, measures, facts)In progress — Dimension + Measure + Fact registries done, API generalisation done; ClickHouse ADD COLUMN DDL + Budget Input custom fields applied on publish (schema_apply). Remaining: applying schema on bare save (vs publish) + gold-table measure columns
Security / Entra ID SSONot started
Excel Online Add-in (Office.js)Done — Task pane add-in, pipeline orchestration, Frappe session auth, plus 6 Office.js custom functions (=K.EPM / … / EPMSAVE)
Cash flow statementNot started
Multi-GAAPNot started
Rolling forecastsNot started
Consolidation enhancementsPartial — disposal CTA recycling + historical equity-rate translation done; goodwill-CTA retranslation & NCI-in-combinations (full/partial goodwill) not started
Allocation enhancements (circular, reciprocal)In progress — iterative-convergence reciprocal engine macro built (allocation_engine_reciprocal); not yet wired into a gold model
Planning enhancements (driver-based, recurring journals)Not started
Reporting enhancements (waterfall, trend, commentary)Not started
Close assertion suite (reconciliation gate)In progress — Close Run + Assertion Result doctypes run the dbt assert_* suite with --store-failures and capture failing rows; sign-off gate + dashboard remaining

Phase 1: One-Click Deploy (~3 days) DONE​

Full docker-compose stack + single deploy script. Completed in PRs #7 and #9.

  • 9 Docker services: Frappe backend/worker/scheduler, MariaDB, Redis (cache + queue), ClickHouse, Cube.js, Caddy (plus a backup service)
  • 2 one-shot init containers: configurator (site setup), dbt_init (seed + build)
  • deploy.sh — generates secrets, clones konsol, runs compose up, health checks
  • Caddy reverse proxy with auto-SSL
  • Static assets via gunicorn SharedDataMiddleware (PR #9)
  • Initial Setup Guide documentation
  • Subcommands: ./deploy.sh backup, restore, status, logs, down

Phase 2: Dynamic Schema — Dimensions, Measures & Facts (~5 days) IN PROGRESS​

Make the data model fully registry-driven from Frappe. Adding a dimension, measure, or fact table should be a UI operation in Frappe Desk, not a code change across 6 files.

What's done​

The dbt layer is fully dynamic — Dimension and Measure registries in Frappe drive dbt_project.yml vars, and macros (dim_select(), dim_group_by(), measure_select()) generate SQL from those vars. Gold models like gold_trial_balance already use them. Source-layer abstraction is complete (dim_select_from_source() maps ERP source columns to canonical dimension names).

What remains​

The Frappe API is now registry-driven (epm_value takes a generic fact + dimensions dict, validated against the Fact/Dimension/Measure registries), and publishing a Dimension/Fact applies ClickHouse ALTER TABLE ADD COLUMN, Budget Input custom-field sync, and dbt sources (schema_apply.apply_schema). What remains is applying those schema changes on individual metadata save rather than only on the deliberate Publish action, plus auto-generating gold-table measure columns.

2.1 Dimension Registry (1 day) DONE​

  • Frappe Dimension doctype — on publish, regenerates dbt_project.yml vars.dimensions via dbt_config.regenerate_vars (saves are pure metadata)
  • Fields: dimension_name, source_column, label, cube_type, in_budget, allocation_role
  • Allocation engine uses allocation_role from dimension config
  • ClickHouse ALTER TABLE ADD COLUMN applied on dimension publish (schema_apply._apply_clickhouse_columns); not yet on bare metadata save
  • Budget Input doctype field generation via Frappe Custom Field API (schema_apply._sync_budget_custom_fields), applied on publish

2.2 Measure Registry (1 day) DONE​

  • Frappe Measure doctype — on publish, regenerates dbt_project.yml vars.base_measures via dbt_config.regenerate_vars
  • Fields: measure_name, expression, label, cube_type
  • Default measures pre-seeded: period_debit, period_credit, period_net_amount, transaction_count
  • dbt macros (measure_select(), measure_passthrough()) consume measures dynamically
  • API validates measure against the active (Published) Measure registry ∩ the fact's allowed measures (api._resolve_and_validate / _published_measures) — no hardcoded ALLOWED_MEASURES dict exists
  • ClickHouse gold table columns auto-generated on measure save

2.3 Fact Registry (1.5 days) DONE​

PRD: Fact Registry

  • Frappe Fact Table doctype: fact_name, source_type (ERP GL / Budget / Statistical / Sub-ledger), grain description, refresh_frequency
  • Core facts (pre-seeded, always present):
    • GL Journal Entries — debits/credits by account/period/entity (the universal financial fact)
    • Budget Input — budget submissions per cell
    • Variance Analysis — derived actual-vs-budget output
  • Statistical facts (customer-configurable):
    • Headcount — employees per cost center per period (for allocation drivers)
    • Area (sqm) — square metres per cost center (for facilities allocation)
    • Revenue by Product — for revenue-based allocation
  • Sub-ledger facts (for detailed reporting):
    • Accounts Payable — invoice-level detail for cash flow
    • Fixed Assets — asset register for depreciation / investing cash flow
    • Accounts Receivable — aging for working capital analysis
  • Each Fact Table defines: required dimensions, required measures, ClickHouse table name, dbt model name
  • On publish: generates ClickHouse staging table DDL + dbt source definition

2.4 API Generalisation (1 day) DONE​

PRD: API Generalisation

  • Replace hardcoded cost_center, department params with generic dimensions dict
  • =EPM("USMF", 2024, "Q1", "401100", dimensions={"dim_cost_center": "CC001", "dim_project": "P01"})
  • measure parameter validates against active (Published) Measure registry
  • New fact parameter: resolves the source fact (wins over scenario); can query GL, budget, or statistical facts
  • ClickHouse query builder reads active dimensions/measures/facts from registry

Legacy named params (cost_center, department, dim_* kwargs) were removed, not mapped — callers must use the dimensions dict (see test_api_generalisation). The earlier "backward-compatible old named params" goal was dropped.

2.5 Source Layer Abstraction (0.5 days) DONE​

  • dbt macro {{ dim_select_from_source() }} reads dimension list and generates extraction SQL per ERP
  • D365: auto-extract from LedgerDimensionValuesJson by source_column name
  • dim_select(), dim_group_by(), dim_partition_by() — generate SQL from dimension vars
  • [~] Per-connector dimension mapping — ERPNext done (stg_erpnext__* + dim_harmonize_* macros + Dimension Mapping doctype); SAP remaining
  • Fact-specific source macros: GL extraction vs. budget extraction vs. statistical extraction

Phase 3: Multi-ERP Support (~8 weeks) IN PROGRESS​

Konsolidat's silver/gold layers are already ERP-agnostic. The canonical staging interface, D365 F&O adapter, and ERPNext connector are complete, as are dimension harmonization and the Frappe Connector Registry. Remaining work is adding connectors for SAP (S/4HANA, ECC, B1) and D365 Business Central, plus scale (ClickHouse clustering validation).

Note: D365 F&O and D365 Business Central are completely different products (different APIs, entity models, dimension systems). BC needs its own connector.

Work Breakdown​

TaskEffortStatus
Canonical Staging Schema & Adapter Interface2 daysDone (PR #10) — 7 canonical models, UNION ALL from adapters
D365 F&O Adapter Refactor1 dayDone (PR #10) — 16 models renamed stg_d365_fo__*, canonical output
D365 F&O Budget Write-Back2 daysDone — OData $batch write-back on budget approval (d365_writeback.py); pending live-tenant validation
D365 Business Central Connector3 daysNot started — PRD: D365 BC
SAP S/4HANA Connector3 daysNot started — PRD: SAP S/4HANA
SAP ECC 6.0 Connector3 daysNot started — PRD: SAP ECC 6.0
SAP Business One Connector2 daysNot started — PRD: SAP B1
ERPNext Connector2 daysDone — source-erpnext/ Airbyte source + 7 stg_erpnext__* canonical-adapter models — PRD: ERPNext
Dimension Harmonization3 daysDone — dim_harmonize_select() / dim_harmonize_joins() macros wired into canonical stg_gl_entries / stg_budget_entries, Connector Dimension Map doctype, gold_unmapped_dimension_values — PRD: Dimension Harmonization
Scale Architecture (50–500 LEs)5 daysPartial — bronze partitioning + cursor-based incremental extraction (D365/ERPNext) + per-connector health monitoring done; ClickHouse cluster scaffolded (off by default, unvalidated) — PRD: Scale Architecture
Connector Registry (Frappe)2 daysDone — Connector doctype drives vars.erp_sources, records LEs (Connector Legal Entity) + dimension maps + Connector Health — PRD: Connector Registry

Dependency Graph​

graph TD
    A[Canonical Schema] --> B[D365 F&O Adapter]
    A --> C[D365 Business Central]
    A --> D[SAP S/4HANA]
    A --> E[SAP ECC 6.0]
    A --> F[SAP Business One]
    A --> G[ERPNext]
    B --> H[Dimension Harmonization]
    C --> H
    D --> H
    H --> I[Scale 50-500 LEs]
    I --> J[Connector Registry]

Connector Details​

PRDConnectorAPIGL Source EntityBudget Write-Back TargetAirbyte Source
31D365 F&O (refactor)OData v2GeneralJournalAccountEntryBiEntitiesBudgetRegisterEntries (OData POST)Existing
32D365 Business CentralREST v2.0 / OData v4generalLedgerEntriesbudgets (REST API)Airbyte BC connector
33SAP S/4HANAOData v4 (CDS views)I_JournalEntry, I_GLAccountLineItemA_BudgetPeriodBalance (CDS)Airbyte SAP OData
34SAP ECC 6.0RFC/BAPI or IDocBSEG + BKPF tablesBAPI_BUDGET_POST (RFC)Airbyte SAP (RFC)
35SAP Business OneService Layer RESTJournalEntries (JDT1)BudgetScenarios (Service Layer)Airbyte HTTP
36ERPNextFrappe REST APIGL Entry doctypeBudget doctype (Frappe API)Airbyte ERPNext or direct Frappe API

Architecture​

Raw ERP Data (Airbyte / direct API)
    ↓
Per-ERP Adapters (models/staging/<erp>/)
    ↓
Canonical Staging (models/staging/canonical/) ← UNION ALL from adapters
    ↓
Bronze → Silver → Gold (unchanged, ERP-agnostic)

D365 Budget Write-Back​

On budget approval in Frappe, push the approved budget to D365 F&O via OData to BudgetRegisterEntries.

Source of Truth Decision:

ConcernSource of TruthRationale
Budget planning & scenariosClickHouse (via Frappe)Multi-layer budgets (base/challenge/management/board), spreading profiles, scenario modeling — none of this exists in D365
Actuals / GLD365 (synced to CH via Airbyte)ERP owns transactional data
Budget vs Actuals reportingClickHouse (has both)Single query engine for all analytics
Budget control in ERPD365 (receives approved budget)D365 uses BudgetRegisterEntries for PO/expense validation

ClickHouse is the authoritative store for budget. D365 is a downstream sync target — a one-way push so D365's native budget control features have the numbers.

Round-trip prevention: Do NOT Airbyte-sync BudgetRegisterEntries back from D365 into epm_raw. EPM-originated entries are tagged BudgetModelId = 'EPM-<name>' so dbt can filter them out.

Implementation: (in konsol/d365_writeback.py, gated by enable_d365_budget_writeback, off by default)

  • Frappe hook on Budget Input Submitted → Approved transition → triggers async write-back (_maybe_enqueue_d365_writeback via frappe.enqueue)
  • D365 OData write-back: maps budget rows to BudgetRegisterEntries lines (LegalEntityId, AccountingDate, MainAccountId, AccountingCurrencyAmount, BudgetType) via an OData $batch changeset with replace semantics (pre-flight GET → DELETE existing → POST new)
  • Dimension mapping: canonical EPM dimensions → D365 LedgerDimensionValues (CostCenter / Department) via _dimension_values()
  • Error handling: per-operation batch failures detected; d365_writeback_status / d365_writeback_error written back to the Budget Input doc
  • Idempotency: lines tagged BudgetModelId = 'EPM-<name>'; re-push replaces rather than duplicates, and already-Pushed docs are skipped
  • Config: D365 OAuth settings in EPM Settings (tenant, client ID, secret, resource URL) using the client-credentials flow
Pending live-tenant validation

The module's own docstring marks a few things NEEDS-LIVE-TENANT: the exact OData field / LedgerDimensionValues attribute names and the atomicity of the $batch changeset are coded and unit-tested (test_d365_writeback*.py) but not yet verified against a real D365 F&O environment.

Scale Architecture​

For deployments with 50–500 legal entities across multiple ERPs:

  • Partitioned bronze tables — bronze_general_journal_account_entries partitioned by toYear(accounting_date), budget by toYYYYMM(transaction_date), FX by toYear(valid_from)
  • Parallel dbt builds — per-ERP adapter builds can run concurrently (adapter pattern supports this)
  • Incremental extraction — cursor-based incremental sync for D365 F&O (OData $filter on SourceKey/date) and ERPNext (modified high-water mark); remaining only for the unbuilt SAP/BC connectors
  • [~] ClickHouse cluster — cluster.sql macros + docker-compose.cluster.yml (3-shard, cityHash64(data_area_id) sharding) scaffolded but off by default and not yet validated
  • Monitoring — per-connector health dashboard: Connector Health doctype refreshed by a 5-min refresh_connector_health job (status / lag / rows, operator alerts)

Phase 4: Security & Entra ID SSO (~2 days)​

PRD: Security & Microsoft Entra ID SSO

4.1 Entra ID Integration (1 day)​

  • Register Konsol in Microsoft Entra ID (OAuth2 / OpenID Connect)
  • Map Entra ID groups to Frappe roles (Reader, Planner, Controller, Admin)
  • Test SSO login flow

4.2 Reverse Proxy & TLS (0.5 days)​

  • Caddy in docker-compose: auto-cert TLS ({$SITE_DOMAIN} → automatic HTTPS) — done (also covered by Phase 1)
  • CORS for Excel Online + rate limiting (100 req/min per user) — not yet configured

4.3 ClickHouse Network Isolation (0.5 days)​

  • ClickHouse ports bound to ${CLICKHOUSE_BIND:-127.0.0.1} (loopback by default) in docker-compose — not reachable off-host
  • Explicit production verification / runbook step

Phase 5: Excel Online Add-in (~3 days) DONE​

Task pane add-in built and deployed. Source: excel-addin/, served from Frappe public/excel-addin/.

  • Office.js Task Pane app (manifest.xml, taskpane.html/js/css, icons)
  • Frappe session-based authentication (login/logout via cookie)
  • Pipeline orchestration: trigger Extract + dbt Build, poll status (5s interval)
  • Status display: Queued/Extracting/Transforming/Success/Failed badges
  • Deployed to Frappe static assets, sideloadable via manifest.xml
  • Documentation: README + user guide + architecture diagram
  • Office.js custom functions — the add-in also ships 6 live custom functions (=K.EPM, EPM_BUDGET, EPM_VARIANCE, EPM_DEBIT, EPM_CREDIT, EPMSAVE) via a shared runtime that reuses the Frappe login session cookie, so =EPM()-style reads work in Excel Online too, not just VBA

Not included (by design):

  • No MSAL/OAuth (uses Frappe session cookies instead)

Phase 6: Analytical Gaps (~2 weeks)​

6.1 Cash Flow Statement (2–3 days)​

PRD: Cash Flow Statement (Indirect Method)

  • gold_cash_flow_indirect.sql — derive from balance sheet delta method
  • Categories: Operating, Investing, Financing
  • Account mapping seed: cash_flow_categories.csv
  • Consolidated cash flow (after FX translation)
  • Tests: operating + investing + financing = net change in cash

6.2 Multi-GAAP / Dual Reporting (1 week)​

PRD: Multi-GAAP / Dual Reporting

  • reporting_standard dimension (LOCAL_GAAP, IFRS)
  • Separate adjustment rules per standard
  • Gold models produce one output per standard
  • Tests: each standard balances independently

6.3 Rolling Forecasts (2–3 days)​

PRD: Rolling Forecasts

  • 12-month forward window, shifts monthly
  • Actual for closed periods + forecast for open periods
  • Scenario type rolling

6.4 Budget Cell Locking (1–2 days) IN PROGRESS​

PRD: Budget Cell Locking (Concurrency Control)

  • Optimistic locking: budget_cell_save() compares the client's base_modified against the committed modified (FOR UPDATE) and refuses stale writes with HTTP 409
  • Conflict response returns the latest value (current_amount + current_modified) so Excel can prompt the user
  • Optional pessimistic locking: Budget Lock doctype with auto-expiry (5 min)
  • VBA retry logic on conflict (refresh cell, re-prompt)

6.5 Multi-Step Budget Approval Chain (0.5 day)​

PRD: Multi-Step Budget Approval Chain

  • Extend budget_input_workflow.json: Draft → Submitted → Dept Manager Approved → Controller Approved → CFO Approved
  • Each transition gated by a separate Frappe role
  • Use Frappe docstatus = 1 (Submit) on final approval to permanently lock document
  • Email notifications on workflow state changes (Frappe Notification doctype)

6.6 Consolidation Enhancements (1–2 weeks) PARTIAL​

PRD: Consolidation Enhancements

  • Historical (temporal) rate for equity line items — historical_equity_rates staging + is_equity translation-rate logic in gold_consolidated_trial_balance (IAS 21)
  • Remeasurement vs translation distinction (separate functional currency handling)
  • Goodwill CTA — CTA on goodwill arising from acquisition accounting
  • Recycling CTA to P&L on disposal of a foreign operation — gold_disposal_adjustments cta_recycling (IAS 21.48)
  • NCI in business combinations — goodwill allocation to NCI (full vs partial goodwill methods)
  • Changes in ownership without loss of control — equity transactions between parent and NCI

6.7 Allocation Enhancements (3–5 days)​

PRD: Allocation Enhancements (Circular & Reciprocal)

  • [~] Circular (iterative) allocations — iterative-convergence engine built as allocation_engine_reciprocal (max 10 iterations, Δ<0.01); remaining work is wiring it into gold_allocation_results
  • Reciprocal allocation method — simultaneous-equations approach (alternative to iteration)

6.8 Planning Enhancements (1 week)​

PRD: Planning Enhancements (Driver-Based & Recurring)

  • Driver-based planning — revenue × price × volume decomposition
  • Phasing templates at account-group level (apply seasonal patterns by account type)
  • Recurring journal templates — auto-generate topside journals on schedule
  • Topside journal approval workflow (separate from budget approval)

6.9 Reporting Enhancements (3–5 days)​

PRD: Reporting Enhancements (Waterfall, Trend, Commentary)

  • Waterfall / bridge analysis — price, volume, mix decomposition of variances
  • Trend analysis — period-over-period and rolling averages
  • Commentary / annotation on variances — attach narrative to variance cells

6.10 Close Assertion Suite — Reconciliation Gate (3–5 days) IN PROGRESS​

PRD: Close Assertion Suite — Reconciliation Gate

Every close runs against an automated assertion suite. Green means the numbers reconcile; red tells you exactly which row broke, and why — before anyone signs off.

  • Surface the dbt assert_* tests as a per-close suite — run_close_assertions() runs dbt test --select test_type:singular --store-failures
  • Close Run + Assertion Result doctypes — one row per assertion with pass/fail (green/red), category, and a link to its failing rows
  • Capture the failing rows + reason from dbt --store-failures (parsed from run_results.json)
  • Close sign-off gate — block close approval (ties into the budget/consolidation approval chain, §6.5) until the suite is green, with an explicit, audited override
  • Dashboard — green/red board per close period; drill a red assertion into its failing rows

Net-new work remaining is the sign-off gate (blocking close approval until green) and the dashboard UI; the assertion run, result capture, and failing-row parsing already exist.

Phase 7: Production Hardening (~3 days)​

PRD: Production Hardening

  • [~] ClickHouse backup — docker/backup/backup.sh dumps MariaDB + ClickHouse + Frappe files with optional S3 upload + retention (./deploy.sh backup); built-in cron scheduling still remaining
  • Monitoring: ClickHouse query latency + Frappe response times (Prometheus + Grafana)
  • Alerting: email/Slack on pipeline failure or dbt test failure
  • Load testing: simulate 50 concurrent Excel users
  • Runbook: monthly close process
  • Disaster recovery procedure

Effort Summary​

PhaseEffortDependencies
Phase 1: One-click deploy~3 daysNone
Phase 2: Dynamic schema (dimensions, measures, facts)5 days ~1 day remaining (apply-on-save, gold measure columns)None
Phase 3: Multi-ERP (4 connectors remaining + cluster validation)8 weeks ~5 weeks remainingPhase 2 (dimension abstraction)
Phase 4: Security & SSO~2 daysPhase 1
Phase 5: Excel Online Add-in3 days Done—
Phase 6: Analytical gaps, consolidation/allocation/planning/reporting enhancements~6 weeksPhase 2 (dimensions)
Phase 7: Production hardening~3 daysPhase 1
Total~7 weeks

Phases 1 and 2 can run in parallel. Phase 3 depends on Phase 2 (schema abstraction). Phases 4–7 can overlap.