Configuration Reference

All configuration surfaces in Konsolidat, from environment variables to dbt project settings to Excel workbook properties.

Environment Variables (`.env`)

Read by docker-compose.yml (and docker-compose.cluster.yml) and can be overridden at runtime. Copy .env.example to .env, or run ./deploy.sh, which auto-generates .env with random secrets on first run.

Required secrets

Four variables are guarded in docker-compose.yml with ${VAR:?...} and have no fallback — the stack refuses to start until they are set: CLICKHOUSE_PASSWORD, DB_ROOT_PASSWORD, ADMIN_PASSWORD, and CUBEJS_API_SECRET. Run ./deploy.sh to generate them. The placeholder values in .env.example are intentionally non-functional.

Frappe

Variable	Default	Required	Description
`ADMIN_PASSWORD`	`admin123` (placeholder)	✅	Frappe Administrator password (`:?` guarded in compose)
`SITE_NAME`	`konsolidat.local`		Frappe site name; also used in the backend health-check `Host` header
`FRAPPE_PORT`	`8069`		Host port mapped to the Frappe backend
`FRAPPE_BRANCH`	`version-15`		Frappe Framework branch to build

MariaDB

Variable	Default	Required	Description
`DB_ROOT_PASSWORD`	`rootpassword` (placeholder)	✅	MariaDB root password (`:?` guarded in compose)

ClickHouse

Variable	Default	Required	Description
`CLICKHOUSE_USER`	`default`		ClickHouse login user
`CLICKHOUSE_PASSWORD`	`open_epm_dev` (placeholder)	✅	ClickHouse password (`:?` guarded; `deploy.sh` overwrites with a random secret)
`CLICKHOUSE_HTTP_PORT`	`8123`		Host port for the ClickHouse HTTP API
`CLICKHOUSE_NATIVE_PORT`	`9000`		Host port for the ClickHouse native protocol
`EXCEL_ODBC_PORT`	`15432`		Host port for the PostgreSQL wire protocol (Excel/ODBC)
`CLICKHOUSE_BIND`	`127.0.0.1`		Host interface the ClickHouse ports bind to. Loopback by default so the DB is not publicly exposed; set `0.0.0.0` only with TLS and a strong password
`DBT_TARGET`	`dev`		dbt ClickHouse transport: `dev` (plaintext loopback) or `prod` (TLS). Set `prod` for any networked deployment. The konsol app's own ClickHouse TLS is configured separately in EPM Settings

Cube.js

Variable	Default	Required	Description
`CUBEJS_PORT`	`4000`		Host port for the Cube.js API
`CUBEJS_API_SECRET`	`CHANGE_ME_run_deploy_sh` (placeholder)	✅	Cube.js API signing secret (`:?` guarded in compose)
`CUBEJS_DEV_MODE`	`false`		Enables the Cube.js developer playground

Caddy reverse proxy

Variable	Default	Description
`HTTP_PORT`	`80`	Host HTTP port for Caddy
`HTTPS_PORT`	`443`	Host HTTPS port for Caddy
`SITE_DOMAIN`	`localhost`	Public domain. Setting a real domain enables Caddy auto-SSL

Backup

Variable	Default	Description
`BACKUP_RETENTION_DAYS`	`7`	Days of backups to retain
`BACKUP_PATH`	`./backups`	Host directory mounted into the backup service at `/backups`
`BACKUP_S3_BUCKET`	(unset)	Optional S3 bucket for off-host backups

Optional overrides

These are commented out in .env.example. The D365 and Airbyte values are normally configured in the Frappe UI (EPM Settings) rather than as env vars.

Variable	Description
`KONSOL_REPO`	Override the konsol app Git repository URL
`KONSOL_BRANCH`	Override the konsol app branch
`D365_TENANT_ID`	Azure AD tenant ID for D365 F&O
`D365_CLIENT_ID`	D365 OAuth client ID
`D365_CLIENT_SECRET`	D365 OAuth client secret
`D365_ENVIRONMENT_URL`	D365 F&O environment URL
`AIRBYTE_API_URL`	Airbyte API base URL (Airbyte installs as a separate stack via `scripts/setup-airbyte.sh`)

Frappe EPM Settings

A Single DocType (issingle: 1) named EPM Settings, in the Pipeline module, edited from the Frappe Desk UI. Stores connection and access-control details read by the konsol app at runtime. Defined in epm_settings.json.

ClickHouse Connection

Field	Type	Default	Description
`clickhouse_host`	Data	`localhost`	ClickHouse server hostname
`clickhouse_port`	Data	`8123`	ClickHouse port
`clickhouse_user`	Data	`default`	ClickHouse user for API queries
`clickhouse_password`	Password	—	ClickHouse password
`clickhouse_secure`	Check	`0`	Use HTTPS (TLS) so credentials and results are encrypted in transit. Required over any non-loopback network
`clickhouse_verify_tls`	Check	`1`	Validate the server certificate (shown only when `clickhouse_secure` is on). Disable only for self-signed certs on a trusted network

Airbyte Connection

Field	Type	Default	Description
`airbyte_api_url`	Data	`http://localhost:8000`	Airbyte API base URL
`airbyte_connection_id`	Data	—	Airbyte connection UUID
`airbyte_client_id`	Data	—	Airbyte API client ID
`airbyte_client_secret`	Password	—	Airbyte API client secret
`webhook_secret`	Password	—	Shared secret for Airbyte webhook auth (`X-Webhook-Secret` header)

The Airbyte Sync Status section additionally exposes read-only fields last_airbyte_sync_at (Datetime), last_airbyte_sync_status (Select: Success / Failed / Partial / Running), and last_airbyte_sync_rows (Int).

D365 F&O Budget Write-Back (OAuth)

One-way push of approved budgets to D365 BudgetRegisterEntries; ClickHouse stays the source of truth.

Field	Type	Default	Description
`enable_d365_budget_writeback`	Check	`0`	Master switch for budget write-back
`d365_resource_url`	Data	—	D365 resource URL, e.g. `https://yourorg.operations.dynamics.com`
`d365_tenant_id`	Data	—	Azure AD tenant ID
`d365_client_id`	Data	—	D365 OAuth client ID
`d365_client_secret`	Password	—	D365 OAuth client secret
`d365_fiscal_year_start_month`	Int	`1`	Month the D365 fiscal year begins (1=Jan, 4=Apr, 7=Jul, 10=Oct). Maps budget period numbers to OData `AccountingDate`. Shown only when write-back is enabled. Must match the D365 fiscal calendar for the target legal entity

dbt Configuration

Field	Type	Default	Description
`dbt_project_path`	Data	`/home/pd/open_epm/dbt_project`	Absolute path to the dbt project directory

Access Control

Permission scoping is driven by Frappe User Permissions on the linked DocTypes. Leave a field blank to allow all authenticated users.

Field	Type	Description
`entity_permission_doctype`	Link (DocType)	DocType whose User Permissions restrict which entities (data areas) a user may query via the EPM API. Blank = all entities allowed
`account_permission_doctype`	Link (DocType)	DocType (typically a Virtual DocType proxying main account categories) whose User Permissions restrict which account categories a user may write budgets for. Blank = all accounts allowed

Permission: System Manager role only (read / write / create).

dbt Project Variables (`dbt_project.yml`)

`dimensions`

A list of dimension definitions that auto-propagate through all Gold models via dimension helper macros.

vars:
  dimensions:
    - name: dim_cost_center
      source_column: CostCenter       # D365 OData field name
      label: "Cost Center"
      cube_type: string
      in_budget: true                  # Include in budget models
      allocation_role: cost_center     # Used by allocation engine
    - name: dim_department
      source_column: Department
      label: "Department"
      cube_type: string
      in_budget: true
    - name: dim_business_unit
      source_column: BusinessUnit
      label: "Business Unit"
      cube_type: string
      in_budget: false

`base_measures`

Aggregate expressions applied in gold_trial_balance. Referenced by measure_select() and measure_passthrough() macros.

vars:
  base_measures:
    - name: period_debit
      expression: "sum(debit_amount)"
      label: "Debit"
      cube_type: sum
    - name: period_credit
      expression: "sum(credit_amount)"
      label: "Credit"
      cube_type: sum
    - name: period_net_amount
      expression: "sum(accounting_currency_amount)"
      label: "Net Amount"
      cube_type: sum
    - name: transaction_count
      expression: "count(*)"
      label: "Transaction Count"
      cube_type: sum

`fiscal_extra_periods`

Special fiscal periods beyond the standard 1–12.

vars:
  fiscal_extra_periods:
    - { period: 0,  label: OPN, quarter: OPN, half: OPN }
    - { period: 13, label: CLS, quarter: CLS, half: CLS }

`fiscal_quarter_mapping` / `fiscal_half_mapping`

Maps fiscal period numbers to quarter/half labels. Calendar-aligned by default.

vars:
  fiscal_quarter_mapping:
    1: Q1
    2: Q1
    3: Q1
    4: Q2
    # ... through 12: Q4
  fiscal_half_mapping:
    1: H1
    2: H1
    # ... 6: H1, 7: H2 ... 12: H2

Model Materialization

Layer	Schema	Materialization	Tags
staging	`staging`	view	staging
bronze	`bronze`	table	bronze
silver	`silver`	table	silver
gold	`gold`	table	gold

Seed Column Types

Custom column types are specified per seed in dbt_project.yml:

consolidation_groups: ownership_pct → Decimal(5,2)
consolidation_adjustments: All columns typed — consolidation_group (String), fiscal_year (UInt16), fiscal_period (UInt8), debit_amount / credit_amount (Decimal(18,2)), etc.

Excel VBA Configuration

`EPM_API_URL` (Custom Document Property)

The VBA module reads the API base URL from a workbook-level Custom Document Property named EPM_API_URL.

Property	Type	Default	Set By
`EPM_API_URL`	String	`http://localhost:8069`	`EPM_SetServer` macro

To set programmatically: run EPM_SetServer from the Excel macro menu, or set via VBA:

ActiveWorkbook.CustomDocumentProperties.Add _
    Name:="EPM_API_URL", _
    LinkToContent:=False, _
    Type:=msoPropertyTypeString, _
    Value:="https://your-frappe-server.com"

VBA Module Constants

Constant	Value	Description
`DEFAULT_API_URL`	`http://localhost:8069`	Fallback when no Custom Document Property is set
`LOG_SHEET_NAME`	`_EPM_Log`	Hidden sheet for debug logging

ClickHouse Databases

Created by clickhouse/init-db.sql on first startup:

Database	Purpose
`epm_bronze`	Airbyte-managed raw tables
`epm_staging`	dbt staging views
`epm_silver`	dbt silver tables
`epm_gold`	dbt gold tables (consumed by API)

Docker Compose Services

Service	Image	Ports
`clickhouse`	`clickhouse/clickhouse-server:24.8-alpine`	8123 (HTTP), 9000 (native), 15432 (PostgreSQL wire)

Volumes: clickhouse_data, clickhouse_logs Health check: clickhouse-client --query "SELECT 1" every 10s.

D365 Airbyte Connection

15 OData entities extracted with cross_company=true:

GeneralJournalAccountEntries, GeneralJournalEntries, MainAccounts, MainAccountCategories, LegalEntities, FiscalCalendars, FiscalCalendarYears, FinancialDimensions, FinancialDimensionValues, ExchangeRateCurrencyPairs, ExchangeRateTypes, BudgetRegisterEntries, BudgetTransactionLines, ConsolidationAccountGroups, LedgerTrialBalanceFiscalYearSnapshotDataEntity

Destination: ClickHouse at host.docker.internal:8123, database epm_bronze.

Environment Variables (.env)​

Frappe​

MariaDB​

ClickHouse​

Cube.js​

Caddy reverse proxy​

Backup​

Optional overrides​

Frappe EPM Settings​

ClickHouse Connection​

Airbyte Connection​

D365 F&O Budget Write-Back (OAuth)​

dbt Configuration​

Access Control​

dbt Project Variables (dbt_project.yml)​

dimensions​

base_measures​

fiscal_extra_periods​

fiscal_quarter_mapping / fiscal_half_mapping​

Model Materialization​

Seed Column Types​

Excel VBA Configuration​

EPM_API_URL (Custom Document Property)​

VBA Module Constants​

ClickHouse Databases​

Docker Compose Services​

D365 Airbyte Connection​