Konsol — Security Architecture & Excel Online Integration

Last updated: 2026-06-06

Overview

This document describes the security architecture for exposing Konsol to Excel Online and web users, using Frappe as the application layer and ClickHouse (cloud-hosted) as the analytical backend.

Design principles:

Frappe owns application concerns (users, config, workflows, write-back, audit)
ClickHouse owns analytical concerns (GL data, consolidation, reporting)
ClickHouse is never exposed to the internet — all access goes through Frappe or Cube
Excel users get =EPM(...) custom functions that work in Online, Desktop, and iPad

Architecture

┌──────────────────────────────────────────────────────┐
│  Users                                                │
│  ┌──────────┐  ┌──────────────┐  ┌────────────────┐  │
│  │ Excel    │  │ Excel Online │  │ Frappe Desk    │  │
│  │ Desktop  │  │ / iPad       │  │ (Web UI)       │  │
│  │ (ODBC)   │  │ (Add-in)     │  │                │  │
│  └────┬─────┘  └──────┬───────┘  └───────┬────────┘  │
└───────┼───────────────┼──────────────────┼────────────┘
        │               │                  │
        │       HTTPS + Frappe session     │
        ▼               ▼                  ▼
┌──────────────────────────────────────────────────────┐
│  Frappe (Application Layer)                           │
│                                                       │
│  DocTypes:                                            │
│  ├─ Budget Entry        (write-back, workflow)        │
│  ├─ Scenario            (budget/forecast/whatif)      │
│  ├─ Consolidation Group (entities, ownership %)       │
│  ├─ IC Elimination Rule (debit/credit pairs)          │
│  ├─ Allocation Rule     (step, driver, accounts)      │
│  └─ EPM Report          (saved report definitions)    │
│                                                       │
│  Built-in: Auth, RBAC, Workflows, Audit, REST API     │
│  DB: MariaDB/PostgreSQL (metadata + config only)      │
└──────────────────┬───────────────────────────────────┘
                   │ clickhouse-connect (Python)
                   ▼
┌──────────────────────────────────────────────────────┐
│  ClickHouse (Azure / AWS — managed or self-hosted)    │
│                                                       │
│  epm_bronze  ← Airbyte (D365 OData extraction)       │
│  epm_silver  ← dbt (standardized)                    │
│  epm_gold    ← dbt (consolidated TB, IC elim, CTA)   │
│  epm_staging ← Frappe writes budget data here         │
│                                                       │
│  Cube SQL API (port 15432) → Excel ODBC / Add-in     │
└──────────────────────────────────────────────────────┘

Security Layers

1. Identity & Authentication

The shipped implementation uses Frappe's native session authentication. Entra ID SSO is a roadmap target, not yet implemented.

Mechanism	Details	Status
Frappe session login	Users sign in with username/password via `POST /api/method/login`; Frappe issues a session cookie	Shipped
Excel Add-in auth	The add-in calls `/api/method/login` and reuses the session cookie on every request (`fetch(..., credentials: "include")`) — no tokens or Bearer headers	Shipped
API access	Whitelisted (`@frappe.whitelist`) methods read the caller from `frappe.session.user`; RBAC enforced from the session	Shipped
2FA	Frappe built-in — configurable per role	Available
Entra ID SSO	Microsoft Entra ID (Azure AD), same tenant as D365 F&O, via Frappe Social Login	Roadmap

2. Authorization (Role-Based Access)

Role	Desk UI	Excel Read	Excel Write	Config Edit
Reader	View reports, dashboards	`=EPM(...)` queries	No	No
Planner	View + submit budgets	Full read	`=EPMSAVE(...)` budget write-back	No
Controller	Full access	Full read	Full write	Edit consolidation groups, IC rules, allocations
Admin	Full access	Full read	Full write	All config + user management

Frappe RBAC enforces this on every DocType and API endpoint automatically.

3. Transport & Network

Layer	Implementation
TLS	Caddy reverse proxy with automatic Let's Encrypt certificates
CORS	Whitelist `*.officeapps.live.com` + company tenant domain
Rate limiting	100 requests/min per user (prevents runaway Excel refresh loops)
ClickHouse isolation	Private network only — no public endpoint. All access via Frappe or Cube.
Cube SQL API	Internal network only, or VPN for desktop Excel ODBC users

4. Audit Trail

Frappe provides automatic audit logging on every DocType:

Every create, update, delete is logged with user, timestamp, and field-level diff
Budget submissions tracked: who submitted, when, what values, approval chain
API access logged: which user queried which dimensions
Configuration changes tracked: who changed consolidation groups, IC rules, ownership %

No custom code needed — this is Frappe's built-in behavior.

5. Data Protection

Concern	Approach
PII in GL data	ClickHouse stores account-level aggregates, not transactional PII. Personal data stays in D365.
Budget confidentiality	Role-based: planners see only their entity/cost center (Frappe user permissions)
Encryption at rest	ClickHouse Cloud and Azure/AWS managed disks provide this by default
Encryption in transit	TLS everywhere (Frappe ↔ browser, Frappe ↔ ClickHouse, Airbyte ↔ D365)
Backup	ClickHouse Cloud: automatic. Self-hosted: scheduled snapshots to blob storage.

Excel Online Integration: Custom Functions

The HSGETVALUE Equivalent

Konsol provides an Excel Custom Functions Add-in that registers cell formulas working in Excel Online, Desktop, and iPad. Read functions are debounced and batched into a single epm_batch POST on recalc:

Formula	Purpose	API Endpoint
`=EPM(entity, year, period, account, [measure], [scenario], [cost_center], [dept])`	Single cell value (HSGETVALUE equivalent, actuals by default)	`POST /api/method/konsol.api.epm_batch` (batched)
`=EPM_BUDGET(entity, year, period, account, [cost_center], [dept])`	Budget amount	`POST /api/method/konsol.api.epm_batch` (batched)
`=EPM_VARIANCE(entity, year, period, account)`	Actual vs budget variance	`POST /api/method/konsol.api.epm_batch` (batched)
`=EPM_DEBIT(entity, year, period, account)`	Period debit	`POST /api/method/konsol.api.epm_batch` (batched)
`=EPM_CREDIT(entity, year, period, account)`	Period credit	`POST /api/method/konsol.api.epm_batch` (batched)
`=EPMSAVE(amount, entity, year, period, account, scenario_id, layer)`	Write budget data back (planner role only)	`POST /api/method/konsol.api.budget_cell_save`

A single-value GET /api/method/konsol.api.epm_value endpoint is also exposed for direct queries.

Example Usage in a Spreadsheet

       A              B          C            D             E
1   Entity         Account    Actuals      Budget        Variance
2   USMF           4000       =EPM(        =EPM_BUDGET(  =EPM_VARIANCE(
                               "USMF",       "USMF",       "USMF",
                               2026,1,       2026,1,       2026,1,
                               "4000")       "4000")       "4000")
3   GBMF           4000       125,340      130,000       (4,660)
4   DKMF           5100        45,200       42,000        3,200

How It Compares to Hyperion SmartView

	Hyperion SmartView	Konsol Add-in
Cell formula	`=HsGetValue(...)`	`=EPM(...)`
Bulk refresh	Ad hoc retrieve	Auto-recalc or ribbon refresh button
Submit data	SmartView submit	`=EPMSAVE(amount, ...)`
Auth	EPM workspace login	Frappe session login (Entra ID SSO on roadmap)
Install	SmartView plugin (desktop only)	Office Add-in (web + desktop + iPad)
Works in Excel Online	No	Yes
Works on iPad	No	Yes

Add-in Technical Details

Technology: JavaScript, Office.js Custom Functions API
Auth: Frappe session login via /api/method/login; the session cookie is sent on every API call (fetch(..., credentials: "include")) — no MSAL/Bearer tokens (Entra ID SSO is a roadmap item)
Caching: Results cached per dimension combination during recalc
Batch optimization: Multiple =EPM(...) calls in a sheet are debounced and batched into a single /api/method/konsol.api.epm_batch POST during recalc
Deployment: Upload to Microsoft 365 admin center for org-wide availability
Scaffold: npx yo office --type excel-functions-shared generates the project skeleton

Frappe DocTypes (Replace CSV Seeds)

Configuration that was previously managed as CSV seed files in dbt is now managed as Frappe DocTypes — web-editable, versioned, audited, and role-protected.

DocType	Replaces	Key Fields	Workflow
Budget Entry	`epm_staging.budget_input`	entity, year, period, account, cost_center, amount, scenario, submitted_by	Draft → Submitted → Approved
Scenario	`scenario_definitions.csv`	scenario_id, name, type (budget/forecast/whatif), base_scenario, status	No
Consolidation Group	`consolidation_groups.csv`	group_name, entity, ownership_pct, reporting_currency	No
IC Elimination Rule	`ic_elimination_rules.csv`	debit_account, credit_account, description	No
Allocation Rule	`allocation_rules.csv`	step_order, source_account, target_account, driver_type, source_cost_center	No
Allocation Driver	`allocation_drivers_*.csv`	cost_center, fiscal_period, driver_value, driver_type	No

Config Sync Flow

Controller edits Consolidation Group in Frappe Desk
  → Frappe saves to MariaDB (audited)
  → Frappe server script writes to ClickHouse epm_staging.consolidation_groups
  → Dagster detects change → triggers dbt build
  → Gold layer regenerated with new ownership %
  → Excel users see updated consolidated numbers on next refresh

Budget Approval Workflow (Frappe Built-in)

Planner submits budget in Excel
  → =EPMSAVE(amount, entity, year, period, account, "BUDGET_2026_Q2", layer) per cell
  → Frappe creates Budget Entry DocType records (status: Draft)
  → Planner clicks "Submit" in Frappe Desk (or auto-submits via API)
  → Workflow transitions to "Pending Approval"
  → Controller receives email notification
  → Controller reviews in Frappe Desk → Approves or Rejects
  → On Approve: data written to ClickHouse epm_staging.budget_input
  → dbt unions into gold_scenario_trial_balance
  → Variance analysis updates automatically

No custom workflow engine needed — this is Frappe's standard DocType workflow feature.

ClickHouse Hosting Options

Option	Service	Cost (~50GB GL data)	Managed?
ClickHouse Cloud	clickhouse.com (on Azure/AWS)	~$200–400/mo	Yes — auto-scaling, backups, monitoring
Azure VM	D4s_v5 + managed disk	~$150–250/mo	Self-hosted — you manage ClickHouse
AWS EC2	m6i.xlarge + EBS	~$150–250/mo	Self-hosted — you manage ClickHouse
Aiven for ClickHouse	Managed on Azure/AWS	~$300–500/mo	Yes — managed, multi-cloud

For mid-market EPM workloads (a few GB of GL data, monthly refresh cycles), the smallest ClickHouse Cloud tier is sufficient.

What Frappe Closes vs. FastAPI

Gap	FastAPI (current)	Frappe
User management & SSO	Build from scratch	Built-in — users, roles, 2FA, OAuth, LDAP
Role-based access	Build from scratch	Built-in — RBAC on every DocType
Workflow/approvals	Not implemented	Built-in — multi-step with email notifications
Audit trail	Build from scratch	Built-in — every change logged with field-level diff
Web UI for end users	Streamlit (admin only)	Built-in — Desk UI, list views, forms, reports
REST API	Manual endpoint coding	Built-in — auto-generated for every DocType
Report builder	None	Built-in — query builder + chart views
Real-time updates	None	Built-in — Socket.io
Config management	CSV seeds in git	Built-in — DocTypes (web-editable, versioned)

Remaining Gaps (dbt analytical layer — not Frappe's job)

Gap	Effort	Owner
Cash flow statement	~2–3 days dbt work	Data engineer
Multi-GAAP / dual reporting	~1 week dbt work	Data engineer
Rolling forecasts	~2–3 days dbt work	Data engineer

Build Effort Summary

Component	Effort	Notes
Frappe app scaffold + DocTypes	2–3 days	`bench new-app konsol`, define 6 DocTypes
ClickHouse integration (server scripts)	1–2 days	`clickhouse-connect` from Frappe to read/write
Entra ID SSO configuration (roadmap)	1 day	Frappe Social Login + Entra app registration
Budget approval workflow	Half day	Frappe workflow builder (configuration, not code)
Caddy reverse proxy + TLS	Half day	~20 lines of Caddyfile
CORS configuration	2 hours	Frappe `site_config.json`
Excel Custom Functions Add-in	2–3 days	JavaScript, Office.js scaffold, Frappe session login
Add-in deployment (org-wide)	Half day	Upload to Microsoft 365 admin center
Migrate CSV seeds → DocTypes	1 day	One-time data migration
Total	~10–12 days

Overview​

Architecture​

Security Layers​

1. Identity & Authentication​

2. Authorization (Role-Based Access)​

3. Transport & Network​

4. Audit Trail​

5. Data Protection​

Excel Online Integration: Custom Functions​

The HSGETVALUE Equivalent​

Example Usage in a Spreadsheet​

How It Compares to Hyperion SmartView​

Add-in Technical Details​

Frappe DocTypes (Replace CSV Seeds)​

Config Sync Flow​

Budget Approval Workflow (Frappe Built-in)​

ClickHouse Hosting Options​

What Frappe Closes vs. FastAPI​

Remaining Gaps (dbt analytical layer — not Frappe's job)​

Build Effort Summary​