Aletheia Finance Capabilities Roadmap¶

Status: Phase 1 complete (2026-04-16, was partial). Phase 2 complete (2026-04-14). Phase 3 partially started — Features 3.1–3.3 delivered (2026-04-16), Features 3.4–3.8 not started. Audience: Executive team, finance team, engineering leads, practice managers Related: docs/entity-model.md — the shared foundation for entities, ownership, consolidation, intercompany

1. Executive summary¶

Opportunity. Aletheia already holds the group's PMS data (patients, appointments, procedures, payments, practitioners, equipment, budgets). Adding Pennylane-sourced financial data on top creates a class of reports that no third-party BI tool, no Pennylane dashboard, and no standalone finance app can reproduce — because only Aletheia can join finance numbers with per-dentist, per-appointment, per-treatment operational data.
We are not rebuilding accounting. Pennylane stays the ledger of record for the 7 FR entities. Excel GL imports cover the 4 non-Pennylane entities (SUI, AST, SDT, and CHC until it goes live). Aletheia builds the operational + analytical finance layer on top: consolidation, dashboards, forecasts, alerting, AI enrichment.
Foundation first. Phase 1 is unglamorous but load-bearing: an entity data model, unified GL storage, Excel import pipeline, intercompany elimination engine, and a rebuild of the current "Groupe Suffren" management reporting format in-app. Without it, every later feature is unstable.
Moat features in Phase 2. Once the foundation lands, Phase 2 ships the revenue-per-dentist join, lab/prosthetics cost analysis, no-show cost analysis, treatment-to-invoice funnel, and other finance↔PMS joins. These are the features that justify building this inside Aletheia.
Automation and AI in Phase 3. Once the data and joins are solid, Phase 3 adds workflow (approval routing, close checklist), AI enrichment (invoice line extraction, bank transaction categorization, NL Q&A), and cash flow forecasting.
Parallel cap table roadmap. A sibling roadmap covers individual holder tracking, PV d'AG / ODM / CEL generation, and partner register automation. Both roadmaps share the data model in docs/entity-model.md. Neither may redefine entities or ownership; one populates individual holders, the other populates GL + eliminations.
Respecting what exists. The roadmap reuses Aletheia's Django 5.2 / PostgreSQL / Celery / Bootstrap stack, the apps/imports/AbstractImporter base class, the apps/core/permissions.feature_required decorator, the apps/budgets versioning pattern, and the existing UI skeletons in templates/skeletons/. No rewrites.
Growing platform. The group expects 5-10 new practice acquisitions per year. The entity model, aggregation scopes, and onboarding workflows must handle this as routine operations, not exceptional admin tasks.
Non-goals (Phase 1-3). Not building: a French accounting engine, a FEC export, a TVA declaration tool, a bank feed aggregator, a payroll system, a patient billing tool. Some of these may come later; all are explicitly deferred so we don't pad Phase 1.
Biggest risks. (a) The management reporting goes through close iterations (v1 → v6 within a single month's close cycle) and the report format itself evolves between fiscal years. Both must be handled. (b) The non-Pennylane ingestion formats are fragmented (AST/SDT have LOCATION+PROJECT dimensions, SUI has none) and must be harmonized to one template before consolidation works cleanly. (c) Intercompany elimination rules are currently implicit in Excel formulas — extracting them is a finance-team interview, not a reading exercise.

2. Current state of Aletheia¶

Stack and conventions¶

Framework. Django 5.2, Python 3.13, PostgreSQL (shared shared_postgres container), Celery 5.6 + Redis, Gunicorn, Sentry, DRF, Crispy Forms + Bootstrap 5, i18n (French default, English, Dutch-BE).
App structure. All apps under apps/. Each app owns its models, views, templates, URLs, and migrations. apps/core/ holds abstract base models (TimeStampedModel, AuditModel, SoftDeleteModel) and the feature-permission system.
Auth. Custom User model (accounts.User) with email login. Users have many-to-many access to Practice via UserPracticeAccess. Django Groups map to feature sets in apps/core/permissions.py (e.g., Finance Manager, Practice Manager, HR Manager, Admin). Decorator @feature_required('feature_name') + class-based FeatureRequiredMixin enforce access.
Deployment. make deploy ENV=staging|prod REF=<tag>. No direct docker compose. Staging auto-deploys from develop, production from tags.
UI skeletons. templates/skeletons/skeleton_list.html, skeleton_detail.html, skeleton_form.html are the starting points for new pages. CSS tokens from base.css (var(--border-primary), var(--bg-card), var(--radius-lg), etc.) — never hardcode colors. Dark mode is automatic via tokens.
Imports. apps/imports/ provides AbstractImporter as the base for all file-driven imports (dentist, patient, procedure, appointment, GL payment). Tracks row-level success/failure in ImportRow, supports dry-run and bulk mode, integrates with Celery for large files.
Budgets. apps/budgets/ already implements a budget-version workflow (BudgetVersion with draft → submitted → validated states, per-practice, per-fiscal-year, with DentistTimeOff, PlannedCapacity, DentistRevenueTarget assumptions). This pattern is reusable for financial forecasting/scenario modelling.

PMS data model¶

Model	File	Purpose
`Practice`	`apps/practices/models.py`	Operational unit. Has address, business hours, rooms, equipment, fiscal year config, Doctolib linkage.
`Dentist` + `DentistContract`	`apps/dentists/models.py`	Practitioner profile and contract to a practice. Contract has `contracted_working_days_per_week`, `contracted_weeks_off_per_year`.
`DentistWorkSchedule` + `DentistRevenueTarget`	same	Per-weekday schedule and daily target with `valid_from`/`valid_to` versioning.
`Patient`	`apps/patients/models.py`	Patient record imported from Logosw.
`Procedure`	`apps/procedures/models.py`	One dental act. Has `fees`, `amo_reimbursement_base`, `overruns_amount`, `ccam_code`, `ccam_grouping`, `type`, etc.
`Payment`	`apps/procedures/models.py`	One payment event (patient or insurance). Sourced from Logosw ACTES_2 or GL+REGL.
`Appointment`	`apps/appointments/models.py`	Scheduled and historical appointments from Doctolib + Logosw.
`PracticeEquipment`	`apps/practices/models.py`	Equipment with `install_date`, `service_interval_days`, service history.

Key aggregations already possible (PMS side): revenue by day/practice/dentist, appointment load by dentist, no-show rate, procedure-type mix, treatment plan → actual conversion, equipment service backlog, patient retention.

What exists on the finance side¶

Updated 2026-04-14. Phase 1 and Phase 2 are complete. The following exists:

Phase 1 (Foundation): - Entity model. apps/entities/ with 11 seeded entities, ownership links, aggregation scopes, elimination rules, fiscal integration groups, period close. - Consolidated P&L + BS. apps/finance/services/report.py generates consolidated reports at any scope and period with intercompany eliminations. - Analysis Table. apps/finance/services/analysis.py — P&L % of revenue, associate fees per dentist (RETRO → Dentist auto-matching via DentistAccountMapping), colleague costs and other expenses sub-category breakdowns. Wired into the consolidated report template. - Pennylane sync. apps/pennylane_sync/ with API, Redshift, and sync services + Celery tasks. - GL import. apps/imports/importers/gl_trial_balance.py — GLTrialBalanceImporter for Excel GL uploads. - GL mapping. apps/finance/models.GLMappingRule with ~469 FR rules and time-versioned mapping + audit trail. - Intercompany elimination. apps/finance/services/elimination.py — declarative rules engine, in-memory computation. - Practice group scorecard. apps/finance/services/scorecard.py — finance + PMS KPIs side-by-side, with no-show cost and budget vs actuals integration. - Monthly close workflow. apps/finance/services/close.py — open → in_close → closed state machine.

Phase 2 (Moat features): - Prosthetics deep-dive. apps/finance/services/prosthetics.py — supplier sync, PDF download, Claude AI extraction, dashboard with lab trends, material-mix, outliers. - Per-dentist P&L. apps/finance/services/dentist_pnl.py — PMS revenue + GL retrocession join, CCAM breakdown, working days, QoQ. - Revenue-to-invoice funnel. apps/finance/services/funnel.py — procedure → invoice → payment funnel with Pennylane cross-check. CustomerInvoice model. - No-show cost analysis. apps/finance/services/noshow.py — lost revenue estimation from appointment data + dentist targets. Integrated into scorecard. - Entity-scoped budgeting. apps/budgets/ extended — BudgetVersion now targets Entity OR Practice (mutually exclusive). BudgetPnLAssumption + BudgetScenario models. Full CRUD views. - Budget vs actuals. apps/finance/services/budget_vs_actuals.py — P&L actuals vs budget per line with variance threshold + BudgetLineCommentary for narrative annotation. - Cross-practice benchmarking. apps/finance/services/benchmarking.py — all practice groups compared KPI-by-KPI with vintage cohort grouping. - Supplier deep-dive. apps/finance/services/supplier.py — per-supplier spend, DPO, YoY trend, category filter, faster-than-revenue flagging.

Delivered (2026-04-16): ~~gl_account table~~, ~~gl_transaction~~, ~~gl_adjustment~~, ~~Cash Flow statement~~, ~~Pennylane sync health dashboard~~. Still deferred: drill-down UI (report line → GL account → journal entry — data model ready, UI not built), "as-reported" vs "restated" dual-view toggle, Chart.js/ApexCharts visualizations, frozen-at-close snapshots + delta view, close calendar enforcement + email notifications, BE/LU mapping rules (requires finance-team interview).

Database schema layout¶

Updated 2026-04-14. Tables are organized across 4 PostgreSQL schemas.

Schema	Tables	Contents
`public`	~105	All Django-managed models. Finance tables prefixed `fin_`, budget tables `budget_`, import tables `import_`, data quality `dq_`, sync `sync_`. PMS core tables (`patient`, `procedure`, `appointment`, `dentist`) use short names. Other apps use their own prefixes (`annuaire_`, `captable_`, `crm_`, `entities_*`, etc.).
`nomos`	76	CCAM reference data (`nomos_ccam_r_`) + zonage (`nomos_zonage_`). `managed = False` in Django. Resolved via `search_path=public,nomos,pennylane` in Django database config.
`pennylane`	6	Redshift Data Sharing raw sync: `general_ledger` (186k rows), `companies`, `customer_invoices`, `supplier_invoices`, `bank_accounts`, `bank_transactions`. Written by raw psycopg2 (not Django ORM). Read by bridge services.
`raw`	3	Collection agent import staging: `gl`, `gl_regl`, `regl`. Written by agent upload API.

What adjacent features bring¶

apps/annuaire/ integrates the French RPPS/FHIR directory for every dental organization registered nationally. AnnuaireOrganization has SIREN/SIRET/statut_juridique/address for every registered French dental practice. It is reference data — useful for auto-filling entity SIRENs when creating new entities rows, but is not our internal legal-entity registry.
apps/annuaire/services/inpi.py already talks to the INPI RNE API (username/password in settings). This could support automatic legal-form / dirigeants lookup when onboarding a new group entity, for free.
apps/crm/ is the newest feature (2026-04) and shows current conventions for service-layer code (apps/crm/services/), CRM-pattern forms, and kanban-style UIs. Good reference for what "good" looks like in Aletheia today.
apps/websites/ has a REST-API-first pattern with DRF viewsets — relevant if Helios or a future frontend needs to consume finance data later.
apps/dashboard/ exists as a placeholder today (the CLAUDE.md comment "Analytics (placeholder)") but has a service layer ready for new KPIs.

3. Current state of finance reporting¶

The finance team currently produces a monthly "Groupe Suffren" management reporting Excel, versioned per period and per close iteration. The latest file in the reference folder is FY25 Suffren - 02 2026 - Mngt reporting v6.xlsx.

Workbook anatomy¶

The workbook is large (~50 sheets) and structured in regions:

Consolidated reports. Conso. (consolidated P&L + BS + CF), Contrib. (per-entity contribution to each line), Compil. (monthly time-series of the P&L).
Per-entity reports. One sheet per entity: PDS, PDSh, THS, SDV, CDA, VSMh, VSM, AST, SDT, SUI. Each sheet has its own P&L/BS/CF built off that entity's trial balance via formulas.
Trial balances and mappings. FR entities FY26 (flattened Pennylane GL for all 7 FR entities, ~1000 rows), FR MAPPING (GL→report line mapping, ~469 rows), AST FY26, SDT FY26, SUI FY26 (non-Pennylane TBs imported from local accounting systems). FY25 equivalents for comparison.
Adjustment sheets. One Adjust. <entity> FY25 per entity, holding manual accounting adjustments (reclassifications, cutoff entries, restatements).

No CHC sheet — confirms CHC is not yet producing financial statements into this reporting.

P&L format (the canonical report lines)¶

Reproduced from Conso. sheet of the latest file. Any Phase 1 dashboard must match this layout exactly:

Section	Lines
Revenue	Private Turnover (Private Revenue), NHS Turnover (Social fund Revenue), Scheme Turnover (Subscriptions Revenue), PPE Turnover (Rental Income), Lab revenue, Training centre revenue
Cost of sales — Laboratory	Lab Materials, Lab direct labour, Lab other
Cost of sales — Practices	Associate Fees (rétrocessions to practitioners), Prothesis (prosthetics labs), Implants, Clear Aligners, Materials, Hygiene Fees
Gross Profit	(subtotal)
Overheads	Colleague Costs, Property Costs, Repairs and Maintenance, Legal and Professional, Other expenses
Other Incomes	(below overheads, added back)
EBITDA before leasing	(subtotal)
Leasing costs	Exceptional — one line
EBITDA before central costs	(subtotal)
Central costs reinvoicing	(SDV → practice invoicing)
EBITDA	(subtotal)
Exceptional result
Depreciation & Amortisation	Intangible, Tangible, (Financial)
PBIT	(subtotal)
Financial incomes
Financial expenses
Financial result	(subtotal)
Corporation Tax
Net Profit	(bottom line)

Below the P&L, an Analysis Table shows:

Each P&L line as a percentage of revenue
Revenue per dentist (top 5 + "Other dentists" + Total) — in €k
Revenue repartition (% of total) per dentist
Associate fees breakdown per dentist
Dentist fees as % of revenue
Colleague costs breakdown (Gross wages, Social security contributions, Change in paid holiday accrual, Apprenticeship grants, Other payroll costs)
KPIs: Social security contribution rate, FTEs by role (Office manager, Secretary, Other, Cleaning staff, Dental assistant), Dental assistant FTE per dentist, Average gross wage/cost per FTE
Other expenses detail (15+ sub-categories from Advertising → Workwear expenses)

Balance Sheet format¶

Section	Lines
Fixed assets	Intangible (Gross, Amortization, Depreciation, Net), Tangible (same trio), Financial (same trio)
Current assets	Inventories, Trade receivables, Prepayments and accrued income, Social receivables, Other receivables
Current liabilities	Trade payables, Accruals, Social payables, Associate fees payable, Other taxes payables, Other payables
Net working capital (excl. CIT)	(subtotal)
CIT & other current assets/liabilities	Corporate income tax receivable/payable, Dividends payable
Capital employed	(subtotal)
Shareholders' equity	Share Capital, Legal reserve, Other reserves, Net income/(loss)
Net financial debt / (cash)	Bank debt, External debt, Cash & cash equivalents
Intercompany	Intercompany receivables/(payables), Cashpooling receivables/(payables), Intercompany loan, Intercompany loan interests, Intercompany loan & interests impairment
Sources of funds	(subtotal, should equal Capital employed)

Cash Flow Statement format¶

Indirect method, starting from EBITDA:

EBITDA before leasing
(-) Leasing costs                          → EBITDA before central costs
(-) Central costs reinvoicing              → EBITDA
(-) Capex: Intangible / Tangible / Financial Fixed Assets
(-) Change in Net Working Capital (excl. CIT)
                                           → Free Cash Flow (pre-tax)
(-) Change in CIT payable / CIT P&L / CIT paid
                                           → Free Cash Flow (post-tax)
(+/-) Exceptional result
                                           → Operating cash flow
(+) Financial income / (+) Financial expenses
(+/-) Debt drawdown / repayment / Debt service
(-) Change in intercompany / cashpooling / loan / loan interest balances
(-) Capital increase / Dividend paid / Write off
(+/-) Unreconciled difference / Other cash flow items
                                           → Net cash flow
Cash & cash equivalents — BoP
Cash & cash equivalents — EoP

GL → report line mapping¶

~469 account codes currently mapped in FR MAPPING (Pennylane/PCG accounts → "Mapping 1" = one of 47 report lines).
Mapping 2 (20 values) adds sub-line detail for "Other expenses" and "Colleague Costs" analysis breakdowns.
Account granularity varies from aggregate (60101000000 Achat de prothèses → Prothesis) to per-practitioner (60430000000 RETRO MAUREL BERENGERE → Associate Fees — each practicing dentist has their own rétrocession account).
The per-practitioner Associate Fees accounts are the moat anchor. Each RETRO <FIRST LAST> account maps naturally to an Aletheia Dentist row by name match, letting Aletheia auto-populate the "Associate fees per dentist" analysis table without a human editing Excel.

Non-Pennylane entity ingestion¶

Three different Excel shapes today, all imported into the consolidation manually:

Entity	Country	Format
AST	BE	`Compte / Libellé local (NL) / Libellé FR / TYPOLOGIE (Holding/Clinic) / PROJECT / monthly columns` — Belgian PCMN chart. LOCATION and PROJECT dimensions split the balance (Holding vs Clinic within the same entity; PROJECT tracks CL interest, audit fees, intercompany loans).
SDT	BE	Same as AST.
SUI	LU	`Reference / Description / Description 2 / monthly columns` — LU chart. No dimensions.

Three different templates, three different charts of accounts, one shared target report format. Today, each entity has its own VLOOKUP formula mapping its accounts to the 47 Mapping 1 lines. The finance roadmap must harmonize these to one canonical Excel template so consolidation runs the same code path regardless of source.

KPIs tracked¶

From the Analysis Table and elsewhere in the workbook:

Financial KPIs. Revenue, Gross Profit, EBITDA (before leasing / before central / after central), Net Profit, CAPEX, Free Cash Flow (pre-tax and post-tax), Net cash flow, Shareholders' equity, Net financial debt, Capital employed, margins at every subtotal.
Operational KPIs (from P&L). Revenue per dentist (top 5 + other + total), Associate fees per dentist, Associate fees / revenue ratio, Dental assistant FTE per dentist, Average gross wage per FTE, Average cost per FTE, FTE counts by role, Social security contribution rate.
Sub-category expense tracking. Other expenses broken into 15+ categories (Advertising, Bank charges, Doctolib access, PMS tool, Training, Travel, ...).

Close iterations and format evolution¶

The v1 → v6 versioning within a single file represents monthly close iterations for one period, not format changes throughout the year: - v1 = first GL pull (~6th of month+1) - Progressive corrections during the 6th-15th (chase missing invoices, accountant Q&A) - v6 = final closed accounts (~20th)

The close calendar: - ~6th of month+1: first GL extract (preliminary) - 6th–15th: chase missing invoices, corrections, accountant Q&A - ~20th: final closed accounts

The report format (line structure, KPIs) does evolve, but between fiscal years or occasionally mid-year — separately from the close iterations.

This means Phase 1 needs a monthly close workflow (open → in_close → closed status per entity/period, with "as-reported" vs "current truth" views) more urgently than it needs report-format versioning. Two implications for the data model:

Report line identity must be stable across format versions even as labels change. The data model should have an internal code for each report line (e.g., PNL_GROSS_PROFIT) separate from its display label (which can be renamed).
Mapping changes must be version-tracked — adding a new GL account to "Other expenses" in March should not retroactively change January's number. The FR MAPPING table in the new system needs effective_from / effective_to columns.

Gaps observed in the current Excel (Phase 1 improvement opportunities)¶

No CHC sheet. Will need to be added when CHC goes live on Pennylane.
"FYE 31/12" label on labels inside the Conso sheet is a stale typo — confirmed. Management and legal FY is Oct 1 – Sept 30 across all entities.
Non-FR charts of accounts not in any shared mapping table. Belgian and Luxembourg accounts are mapped via per-sheet VLOOKUP formulas, not a shared rulebase. Migrating these to a central per-country mapping is Phase 1 cleanup.
Adjustments are per-entity sheets with no audit trail. Who entered them, when, and why is not captured in the file. The new system should track adjustments as explicit database rows with author and justification.
Cross-entity elimination is implicit in the formulas. There is no declarative "SDT↔VSMh CB interest eliminates at FR practices total" rule anywhere — the elimination is baked into Conso sheet arithmetic. Extracting these rules is a finance interview task for Phase 1.
Time-series comparability relies on manual column copying between files. A proper database-backed history eliminates this.

4. Strategic framing¶

Moat¶

Every feature in this roadmap is evaluated by a single question: does it require joining Pennylane financial data with Aletheia PMS operational data?

High moat (only Aletheia can build): features that need both sides.
Medium moat: Pennylane-only features where our domain-specific UX + the Groupe Suffren entity structure provide some lock-in.
Low moat: pure financial reporting that a generic BI tool (Metabase, Superset, Pennylane's own dashboards) could ship.

Priority goes to high-moat features, gated by the foundation they need.

Integration philosophy¶

Pennylane stays the source of truth for the ledger on FR entities. We never write back. We sync, enrich, and report on top.
Excel GL import covers non-Pennylane entities (SUI, AST, SDT, CHC pre-live). One canonical template for all of them, read into the same unified data model.
The unified data model is source-agnostic. Consolidation, dashboards, and exports never know whether the GL came from Pennylane or Excel. The moment they branch on source, we have a bug-class.
The Pennylane-reusable bundle (temp/pennylane_reusable/) is the Pennylane access layer — copy it into apps/pennylane_sync/ and adapt for Aletheia's Celery + settings conventions. Do not re-implement.

Non-goals¶

Not rebuilding accounting. No journal entry creation, no invoice creation, no posting to GL. Pennylane handles all of that.
Not replacing Pennylane's workflows (invoice approval, bank reconciliation in Pennylane, TVA declaration via Pennylane). We extend, we don't replicate.
Not a payroll system. Payroll data comes from GL accounts in class 64 and the social declarations stay in the HR provider (DSN / URSSAF interface). We visualize and analyze; we do not run payroll.
Not a patient billing tool. Patient invoicing is in Logosw/Doctolib; we import it read-only.
Not a cap table tool (that's the parallel roadmap).
Not a CRM replacement. apps/crm/ is for M&A pipeline; finance features should not duplicate that surface.

Relationship to the cap table roadmap¶

The two roadmaps are independent in deliverables, coupled in data model:

Finance roadmap (this doc) ships GL data, consolidation, dashboards, cost analytics, cash flow, intercompany elimination, Excel import.
Cap table roadmap (sibling doc) ships individual holder tracking, ownership events, PV d'AG / ODM / DUA / CEL / loan contract / OCD letter document automation, partner register.
Both build on docs/entity-model.md. The finance roadmap can ship with aggregated holders only (e.g., "practicing dentists at VSM, ~1%"). The cap table roadmap populates individual holders and drives legal automation. Neither redefines the entity, ownership, or consolidation model.

5. Foundation reference¶

The single shared foundation for this roadmap is docs/entity-model.md. That document defines:

Share class vocabulary (OS, CSa/b/c, CL, CB) and the independence of the CVR and FR cascades
The full legal ownership tree (ASCII + canonical entity table) for the 11 current entities (CHC, SUI, AST, SDT, SDV, THS, CDA, PDSh, PDS, VSMh, VSM)
Consolidation methodology (full consolidation across the board, FR-based minority interest, practicing-dentist ~1% bucket rule)
Convertible instrument (CL/CB) handling — debt today, equity tomorrow, economic FR attributed to the lender in either case
Proposed data model: entities, ownership_links (derived/cached from cap table registers), holders, ownership_events, aggregation_scopes, intercompany_elimination_rules, fiscal_integration_groups, fiscal_integration_memberships, period_close
The management aggregation tree (practice groups → subtotals → group totals, with vintage cohorts)
Intercompany elimination rules (seed list and how they're declared)
Open questions forwarded to both roadmaps' Decisions needed sections

This roadmap does not redefine those concepts. Any feature description below that mentions entities, ownership percentages, scopes, or eliminations references the foundation by section number. If you find an inconsistency between this file and the foundation, the foundation wins — update this file, not the other way around.

How this roadmap builds on the foundation:

Phase 1 implements the foundation's data model as a Django app (apps/entities/) and seeds it with the 11 entities, their ownership links (aggregated granularity), the 8 aggregation scopes, and the seed intercompany rules.
Phase 1 depends on the foundation's unified financial data model (source-agnostic GL storage) and the declarative intercompany rules engine.
Phases 2 and 3 query the entity model to scope every report and every dashboard. A feature like "revenue per dentist" must answer "at which scope? Per entity / per practice group / FR practices total / Group incl. BE?" before it renders anything.

6. Multi-source financial data ingestion¶

Every finance feature in the roadmap reads from a unified financial data model — a source-agnostic store of GL lines, trial balances, and month-end statements. Two ingestion paths write into it.

Path A — Pennylane sync (7 FR entities)¶

Updated 2026-04-14. The sync pipeline is operational. Architecture described below reflects the production implementation, not the original prototype plan.

Redshift Data Sharing provides 6 tables: companies, general_ledger, customer_invoices, supplier_invoices, bank_accounts, bank_transactions. Note: Pennylane's Redshift does not expose a trial_balance table — monthly aggregates are computed by the bridge from general_ledger (debit/credit).

REST API v2 provides supplier invoice PDFs for the prosthetics AI extraction pipeline (Feature 2.1).

Sync architecture (apps/pennylane_sync/services/sync.py):

Reference tables (companies) synced once globally — no company_id filter.
Entity tables synced per (table, company_id) chunk via ThreadPoolExecutor (4 workers). Each chunk opens its own Redshift + PostgreSQL connections (raw psycopg2, not Django connection) for thread safety.
Raw data lands in a dedicated pennylane PostgreSQL schema (pennylane.general_ledger, pennylane.companies, etc.) — separate from Django's public schema.
Incremental sync via synchronized_at checkpoints (PennylaneSyncCheckpoint model). Stale checkpoints are auto-invalidated when the local table is missing.
WLM timeout retry: 1 retry after 15s backoff for Redshift WLM-killed queries.
After sync, the bridge (PennylaneBridgeService) aggregates pennylane.general_ledger debit/credit into monthly trial balances and upserts into import_gl_trial_balance (the unified GL store).
CustomerInvoiceBridgeService materializes customer invoices from pennylane.customer_invoices into fin_customer_invoice.

Schedule: Nightly at 4 AM via Celery Beat. Health check every 30 minutes. Manual trigger via make sync-pennylane.

Credentials: Per-entity API tokens + Redshift credentials via python-decouple environment variables. 7 FR entities mapped via Entity.pennylane_company_id.

Current data: 186,543 GL entries (all 7 FR entities), 499 customer invoices, 5,383 supplier invoices, 24,075 bank transactions — 100% match with Redshift source.

Path B — Excel GL import (non-Pennylane entities)¶

Reuses apps/imports/AbstractImporter:

One canonical Excel template for SUI, AST, SDT (and CHC pre-live, and any future foreign entity). Today those three entities use three different shapes — this is not tenable past Phase 1.
Template columns (proposed):
entity_code (must match an entities.code)
period (YYYY-MM)
account_code (native chart of accounts)
account_label_local (in local language)
account_label_fr (translated; optional but preferred)
dimension_1 / dimension_2 (optional, for AST/SDT's LOCATION and PROJECT; ignored by SUI)
amount (signed; debit positive or credit negative, convention decided by config)
A new importer class GLTrialBalanceImporter(AbstractImporter) validates column structure, validates entity_code against entities table, splits by (entity, period), and writes into the unified GL store.
Chart-of-accounts mapping is applied at query time (resolved — enables both "as-reported" and "restated" views without re-importing historical data; if performance concerns materialize, add materialized views). Mapping is stored in a single gl_mapping_rule table with a country column and effective_from/effective_to.
Template versioning. The template itself evolves. GLTrialBalanceImporter declares the supported version and refuses files with an incompatible header row.
Dry run and error reporting. Same semantics as the PMS importers — rows that fail validation are flagged in ImportRow and exported to Excel for the finance team to fix.

The unified financial data model¶

New tables (in apps/finance/ or apps/gl/):

Table	Purpose	Implementation status
`gl_account`	One row per (entity, account_code) pair. Has `account_label`, `account_label_fr`, `account_type` (asset/liability/equity/revenue/expense), `first_seen`, `last_seen`, provenance (Pennylane or Excel).	Deferred. Account metadata lives in `GLTrialBalanceEntry` rows.
`gl_transaction` (or `gl_entry`)	Date-level journal entries. `entity_id`, `account_id`, `accounting_date` (economic date), `booking_date` (when recorded/synced — critical for close workflow), `debit`, `credit`, `balance`, `dimension_1`, `dimension_2`, `source` (pennylane/excel), `source_ref`. Monthly views are query-time aggregation.	Deferred. `GLTrialBalanceEntry` in `apps/imports/models.py` stores monthly aggregates. Date-level entries not yet modelled.
`trial_balance`	Month-end snapshot per (entity, account, period). Built from `gl_transaction` on sync; used by report queries.	Implemented as `GLTrialBalanceEntry` in `apps/imports/models.py`. DB table: `import_gl_trial_balance`. Fed by both Excel imports and Pennylane bridge (which aggregates `pennylane.general_ledger` debit/credit).
`gl_mapping_rule`	Single table with `country` column. Maps `account_code` → `report_line` (internal code) + `report_sub_line` (internal code for Mapping 2). Has `effective_from`/`effective_to` for the versioning requirement above.	Complete. `apps/finance/models.GLMappingRule`. DB table: `fin_gl_mapping_rule`.
`report_line_catalog`	The 47 Mapping 1 lines + 20 Mapping 2 sub-lines + subtotals, with internal codes, display labels per language, ordering, and parent-subtotal relationships.	Complete. `apps/finance/models.ReportLineCatalog`. DB table: `fin_report_line_catalog`.
`gl_adjustment`	Manual adjustments made outside Pennylane (reclassifications, cutoff, audit adjustments). Replaces the `Adjust. <entity>` sheets. Has author, timestamp, justification, `period`, and the P&L/BS impact.	Deferred.
`period_close`	Per-entity monthly close status (open → in_close → closed). See `docs/entity-model.md` §4 for schema. Drives the dual-view pattern below.	Complete. `apps/entities/models.PeriodClose`.

Multi-currency note: all entities operate in EUR natively. No FX conversion logic is required. Pennylane handles any occasional foreign-currency conversion at entry time.

Downstream features never query Pennylane or Excel directly. They query the unified model.

Dual-view reporting pattern¶

Every report in the system supports two views (see also docs/entity-model.md §4a):

"As-reported" (default) — shows the numbers as they were when the period was closed. Uses the GL mapping version in force at the close date and only entries that had been booked at or before the close date. The frozen close snapshot. Numbers never change.
"Restated" (toggle) — shows what the numbers would look like with current knowledge. Uses the current/latest GL mapping (applied retroactively) and all entries by accounting_date, including late-arriving ones. Live GL data.

The UI exposes: period selector + "As reported | Restated" toggle + delta view (what changed between the two). This pattern applies uniformly to P&L / BS / CF at any scope, per-entity reports, consolidated reports, and historical comparisons (e.g., compare "FY25 as-reported" vs "FY25 restated").

Validation and reconciliation¶

Every sync / import run produces a reconciliation report:

Does the trial balance still balance (assets = liabilities + equity)?
Do monthly revenues match Logosw patient payments (within tolerance)?
Are all RETRO <NAME> accounts matched to a known Dentist?
Are any new GL accounts missing a mapping rule?
Do intercompany eliminations net to zero at Belgium entities total and Group incl. BE?

Unreconciled deltas are flagged and the finance team gets an email via existing Celery + Sentry plumbing.

7. Phase 1 — Foundation¶

Theme. Stand up the entity model, ingest Pennylane + Excel GL, and rebuild the current Groupe Suffren management reporting format in-app. No new finance capability beyond "what they already see in Excel, but live."

Why this phase now. - Without an entity model, no later feature can scope correctly. - Without unified GL storage, every feature has to branch on source (Pennylane vs Excel) — unacceptable. - Without intercompany elimination, the consolidated P&L is wrong by millions (CL/CB interest flows alone are 7-figure). - Without rebuilding the existing format, the finance team has no reason to switch to Aletheia. They already have the Excel.

Foundational work required. All of apps/entities/, apps/finance/, apps/pennylane_sync/, and the GL import pipeline. No reuse of pre-existing finance code (there isn't any). Heavy reuse of apps/imports/AbstractImporter, apps/core/AuditModel, apps/core/permissions, apps/budgets/BudgetVersion approval pattern, Celery, the existing UI skeletons, and the Pennylane-reusable bundle.

Success metrics. - All 11 entities seeded and visible in the admin UI with correct CVR/FR percentages and ownership links. - Monthly Pennylane sync runs cleanly at 4 AM for all 7 FR entities, with checkpoint-based incremental loading. Reconciliation report clean. - Monthly Excel GL import accepts one canonical template for SUI, AST, SDT (and CHC when needed). Finance team uses it instead of maintaining the parallel Excel workbook TBs. - Consolidated P&L, BS, CF matches the current Excel within a rounding tolerance (say, ±€50 per line) at the Group incl. BE scope for FY25 and FY26 YTD. - Intercompany eliminations declared in config (not code), validated by a reconciliation check, balanced to zero at Belgium entities total and Group incl. BE. - The Finance Manager role sees the group + practice-group P&L in-app and can drill down to any scope.

Key risks and open questions. - The non-Pennylane Excel formats are still fragmented — getting AST/SDT/SUI to all use one template requires finance-team buy-in and may need per-entity translation work. - Intercompany rules are implicit in Excel formulas today. Extracting them to a declarative rules engine is a finance interview, not a code task. Budget for a workshop. - The close cycle (v1 → v6 per month) requires a monthly close workflow from day one. The report format itself also evolves between fiscal years — the data model needs effective_from/effective_to on mapping rules, report line catalog entries, and elimination rules. - CHC's dual role (top holding + occasional service co) is expressed via scope membership and separate elimination rules (resolved — see entity-model.md §7 Q1). Defer the CHC-as-service-co elimination rules to when CHC starts invoicing.

Phase 1 features¶

#	Feature	Status	Pennylane data	PMS join	Scope(s)	Excel GL?	Persona	Complexity	Moat
1.1	Entity model & admin	Complete	—	—	All scopes	No	Admin / Finance	L	Low (foundation)
1.2	Pennylane sync pipeline	Complete	Data Sharing + API v2	—	Per entity	No	Sync (system)	L	Low
1.3	Excel GL import template	Complete	—	—	Per entity	Yes	Finance	M	Low
1.4	Unified financial data model + GL mapping table	Complete (see notes)	Both paths	—	—	Both	—	M	Low
1.5	Intercompany elimination rules engine	Complete	Both	—	Practice group+	Both	—	L	Low
1.6	Consolidated P&L / BS / CF rebuild	Partial (dual-view remains)	Both	Associate fees per dentist (optional v1 PMS join)	All scopes	Both	Finance / Exec	L	High (the join)
1.7	Practice group scorecards	Complete (enhanced in Phase 2)	P&L per entity (Phase 1 cut)	Dentist headcount, working days, revenue targets	Per practice group	Both	Practice Manager	M	High
1.8	Historical versioning of report line catalog + GL mapping	Complete	Both	—	—	Both	Finance	M	Low
1.9	Monthly close workflow	Complete	Both paths	—	Per entity	Both	Finance	M	Medium

1.1 — Entity model & admin — COMPLETE¶

Description. Implement the apps/entities/ Django app. Models: Entity, OwnershipLink, Holder, OwnershipEvent, AggregationScope, IntercompanyEliminationRule, FiscalIntegrationGroup, FiscalIntegrationMembership, PeriodClose. Individual holders are populated from day one (both roadmaps ship Phase 1 together). Share class and register tables are owned by the cap table roadmap. Seed the 11 entities, their ownership links, the aggregation scope rules, and the initial set of ~7 intercompany rules. Admin UI for creating, editing, and time-versioning entity changes. Feature permissions: finance_manage, entities_manage, entities_view added to apps/core/permissions.py.

Entity onboarding must be a smooth, repeatable workflow (5-10 acquisitions per year). When a new practice is acquired: (1) create entity row with acquisition_date and consolidation_start_date; (2) create practice row linked to entity; (3) aggregation scope memberships auto-populate from entity attributes; (4) if on Pennylane, add pennylane_company_id — sync starts automatically next nightly run; (5) if not on Pennylane, Excel GL import template is ready for finance upload. This doesn't need a full admin wizard in Phase 1, but the data model and seed tooling must support it without engineering intervention per acquisition.

Why. Every later feature depends on this. Pennylane data. None directly (but stores pennylane_company_id for later linking). PMS join. Adds a nullable entity_id FK to apps/practices/Practice so practices can be mapped to legal entities. Scope. All aggregation scopes are defined here. Complexity. L. Schema is non-trivial, seeding is careful work, admin UI needs a custom time-slider for ownership changes. Moat. Low. This is infrastructure; the moat comes later.

Implementation (2026-04-13). All 9 models implemented in apps/entities/. Seed command manage.py seed_group_entities creates 11 entities, 10 ownership links, 11 aggregation scopes, 7 elimination rules, 1 fiscal integration group. All models registered in Django admin with custom configurations. Practice.entity FK added. 33 unit tests + 12 factories. Permissions (finance_manage, finance_view, entities_manage, entities_view) added to apps/core/permissions.py.

1.2 — Pennylane sync pipeline — COMPLETE¶

Description. Copy temp/pennylane_reusable/ into apps/pennylane_sync/. Adapt for Aletheia's settings (credentials via python-decouple, Celery for scheduling, Sentry for errors, logging to the existing logs/ directory). Wire up a nightly Celery task pennylane_sync.nightly_sync that calls sync_redshift_to_local.py in incremental mode. Add a dashboard page showing sync health per entity: last sync time, row count delta, errors, reconciliation status. Extend the existing Collection-agent health-check pattern (celerybeat-schedule in config/settings/base.py) with a pennylane-sync-health check every 30 minutes.

Why. First time we ingest ledger data for the 7 FR entities. Pennylane data. Data Sharing + API v2, all FR entities. PMS join. None in this feature. Scope. Per entity. Complexity. L. Not novel (the bundle exists) but integration work is real: Celery wiring, settings, logging, monitoring, error surfaces, permissions, admin UI for sync status. Moat. Low (infrastructure).

Implementation (2026-04-13, updated 2026-04-16). apps/pennylane_sync/ with full service layer: services/api.py (REST API v2 with retry/backoff), services/redshift.py (Redshift connector), services/sync.py (per-company chunk parallelism via ThreadPoolExecutor + raw psycopg2), services/bridge.py (GL aggregation: debit/credit → monthly trial balance + GLAccount/GLTransaction population), services/customer_invoice_bridge.py. Raw data in pennylane PostgreSQL schema; bridge materializes into Django models. 4 AM nightly Celery Beat + 30-min health check. All 7 FR entities synced (186k GL entries, 100% Redshift match). make sync-pennylane for manual trigger.

UI layer (2026-04-16). Three pages at /finance/pennylane/: PennylaneSyncDashboardView (per-entity sync status with GL row counts, health badges, last full sync, checkpoint table, manual sync trigger button), PennylaneSyncEntityDetailView (sync history, checkpoint state, error log per entity), PennylaneReconciliationView (unmapped GL accounts, RETRO matching status, row counts). TriggerSyncView (POST-only manual sync via Celery, finance_manage permission). Sidebar reorganized into four Finance sub-groups.

1.3 — Excel GL import template and pipeline — COMPLETE¶

Description. Define one canonical Excel template (Groupe Suffren GL import v1.xlsx) that replaces the three current non-Pennylane shapes. Implement GLTrialBalanceImporter(AbstractImporter) that validates, splits by (entity, period), applies country-specific chart mapping, and writes to gl_transaction + trial_balance. Support dimension columns for AST/SDT (LOCATION, PROJECT); SUI uses null dimensions. A /finance/imports/gl/ page exposes an upload UI for the Finance team. Dry-run mode by default.

Why. Today the non-Pennylane entities are fragmented. This phase consolidates them onto one template so the consolidation engine can treat them uniformly. Pennylane data. None. PMS join. None. Scope. Per entity. Excel GL import? Yes — this is the pipeline. Persona. Finance (monthly upload). Complexity. M. Template design + one new AbstractImporter subclass + validation + per-country chart mapping + admin UI. Moat. Low.

Implementation (2026-04-13). GLTrialBalanceImporter(AbstractImporter) in apps/imports/importers/gl_trial_balance.py. GLTrialBalanceEntry model in apps/imports/models.py stores imported data. Upload UI via GLTrialBalanceImportView + GLTrialBalanceImportForm with Celery task processing. Deviation: data writes to GLTrialBalanceEntry (in apps/imports/) rather than separate gl_transaction / trial_balance tables in apps/finance/. The finance report service reads GLTrialBalanceEntry directly.

1.4 — Unified financial data model + GL mapping table — COMPLETE¶

Description. Create gl_account, gl_transaction, trial_balance, gl_mapping_rule, report_line_catalog, gl_adjustment tables (see §6). Seed report_line_catalog with the 47 Mapping 1 lines + 20 Mapping 2 sub-lines from the current Excel, using stable internal codes (PNL_REVENUE_PRIVATE, PNL_COGS_PROTHESIS, BS_TRADE_RECEIVABLES, CF_FCF_PRETAX, ...). Seed gl_mapping_rule for FR (Pennylane PCG → Mapping 1) from the existing FR MAPPING sheet (~469 rows). Defer BE and LU mapping tables to a Phase-1 work item shortly after the FR mapping — seeding them needs finance-team interview.

Why. Downstream reports never branch on source; they query trial_balance via report_line_catalog. Pennylane data. Stores data imported by 1.2. PMS join. None in this feature (the moat joins come in 1.6). Scope. Foundation for all scopes. Excel GL? Yes — stores Excel-imported GL alongside Pennylane GL. Persona. — Complexity. M. Schema is not large, but seeding the FR MAPPING table requires carefully preserving the ~469 rules, and report_line_catalog needs i18n for display. Moat. Low (foundation).

Implementation (2026-04-13, updated 2026-04-16). Five of the six proposed tables are implemented in apps/finance/models.py: - ReportLineCatalog — 67 lines seeded (47 M1 + 20 M2 sub-lines + subtotals) via manage.py seed_report_catalog. Time-versioned with effective_from/effective_to. Has active_at(as_of_date) class method. - GLMappingRule — ~469 FR mapping rules seeded from JSON. Time-versioned. Has lookup(country, account_code, as_of_date) class method for query-time mapping. Full CRUD UI at /finance/mapping/rules/. - MappingChangeLog — immutable audit trail of all mapping/catalog changes (added as part of Feature 1.8). - GLAccount (2026-04-16) — per-entity account registry. One row per (entity, account_code). Populated via manage.py populate_gl_accounts from both GLTrialBalanceEntry and pennylane.general_ledger. Auto-populated on future Pennylane syncs via bridge. DB table: fin_gl_account. - GLTransaction (2026-04-16) — date-level journal entries. Populated via manage.py populate_gl_transactions from pennylane.general_ledger (186k rows backfill). Incrementally populated by Pennylane bridge on future syncs. Excel-imported entities do NOT get synthetic transactions (monthly aggregates only via GLTrialBalanceEntry). DB table: fin_gl_transaction. - GLAdjustment (2026-04-16) — manual adjustments replacing the Adjust. <entity> Excel sheets. CRUD views at /finance/adjustments/ (list, create, approve). Integrated into ReportService.generate() — adjustments are applied after GL-mapped amounts and before subtotals. DB table: fin_gl_adjustment.

Deferred: - gl_account as a separate normalized table (the original sixth table) — not needed. GLAccount serves this purpose. The report service still reads from GLTrialBalanceEntry for performance; GLTransaction is additive for drill-down. - BE and LU mapping rules — not yet seeded (requires finance-team interview).

Deviation: the report service's fast path still reads from GLTrialBalanceEntry (monthly aggregates in apps/imports/). GLTransaction is used for drill-down only, not for report computation. This is a performance choice — monthly aggregates are faster than summing 186k journal entries.

1.5 — Intercompany elimination rules engine — COMPLETE¶

Description. Implement the intercompany_elimination_rules table from the foundation. Build a rules engine that, given a requested scope and period, computes the elimination postings in memory (never writes them to GL) and applies them at report-generation time. Seed the initial rules (see foundation §6) via a management command. Add a validation run on every sync that reports unbalanced eliminations as warnings via Sentry.

Why. Without this, the consolidated P&L and BS are wrong — CL/CB interest alone creates 7-figure errors at practice group level and above. Pennylane data. Both. PMS join. None. Scope. Practice group level and above (elimination only applies above individual-entity level). Excel GL? Yes. Persona. Finance. Complexity. L. The rule DSL needs to be expressive enough to match the existing Excel formulas (investment ↔ equity, loan ↔ interest, receivable ↔ payable) but not so expressive that it becomes a programming language. Validation and reconciliation are non-trivial. Moat. Low (infrastructure) but essential.

Implementation (2026-04-13). EliminationEngine in apps/finance/services/elimination.py. Computes eliminations in-memory (never writes GL). Reads IntercompanyEliminationRule rows from apps/entities/, checks scope membership via apps/finance/services/scope.py, applies P&L and BS eliminations, validates balance (postings sum to zero). Returns EliminationResult dataclass with postings, warnings, and balance check. 7 seed rules loaded by manage.py seed_group_entities. Integrated into ReportService.generate() for consolidated reports.

1.6 — Consolidated P&L / BS / CF rebuild — PARTIAL (dual-view remains)¶

Description. Build in-app pages that render the current "Groupe Suffren" management reporting format at any requested scope (per entity, per practice group, FR practices, Group excl./incl. BE) and any requested period. Layout matches the current Excel (Conso. sheet) exactly — same line order, same subtotals, same Analysis Table, same KPI block. Use existing template skeletons and Chart.js (or ApexCharts, consistent with existing dashboard style) for visualizations. Drill-down from report line → GL account → journal entry works for the Pennylane entities and shows "imported from Excel" for the rest.

Associate fees per dentist join (v1 cut). Read the GL accounts RETRO <FIRST LAST> via the unified GL store and match each to an apps/dentists/Dentist row via first name + last name (case-insensitive). Expose an admin mapping UI for manual overrides when the match fails. Use the matched data to auto-populate the "Associate fees per dentist" and "Revenue per dentist" rows of the Analysis Table. This is the first moat feature — it lands inside the rebuilt P&L, not as a separate page, so practice managers see it immediately.

Why. Finance and practice managers stop reading the Excel, start reading Aletheia. This is the reason the foundation exists. Pennylane data. general_ledger, trial_balance, supplier_invoices, customer_invoices (for drill-down). PMS join. Dentist name → RETRO account matching, for the associate-fees / revenue-per-dentist join. Scope. All scopes. Excel GL? Yes. Persona. Finance, Practice Manager, Executive. Complexity. L. The layout is complex, the drill-down is complex, the moat join is complex, and the scope selector must gate all of it. Moat. High — the dentist join is unique to Aletheia. Even the Phase 1 cut beats what Pennylane + Excel + BI tool can produce.

Implementation (2026-04-13, updated 2026-04-16). ConsolidatedReportView in apps/finance/views.py renders P&L + BS + CF at any scope and period via ReportService.generate() in apps/finance/services/report.py. Scope selector + period picker. Entity-level amounts and elimination postings displayed. Derived subtotals computed (Gross Profit, EBITDA chain, NWC, Capital Employed). Cash Flow statement uses the indirect method (_build_cash_flow in report.py), rendered in consolidated_report.html.

Completed during Phase 2: - Analysis Table — AnalysisTableService in apps/finance/services/analysis.py. P&L as % of revenue, associate fees per dentist (top 5 + other + total), colleague cost and other expenses sub-category breakdowns. Wired into consolidated_report.html. - Associate fees per dentist join — DentistAccountMapping model + auto-matching (RETRO label → Dentist by first/last name). Admin override UI. Used by both AnalysisTableService and DentistPnLService (Feature 2.2).

Completed (2026-04-16): - Drill-down UI — three-level drill-down from report line → GL accounts → journal entries. DrillDownService in apps/finance/services/drilldown.py with get_line_accounts() and get_account_transactions(). ReportLineDrillDownView at /finance/report/drilldown/ shows GL accounts contributing to a report line (sorted by absolute amount, entity badges, source indicator). GLAccountDrillDownView at /finance/report/drilldown/account/ shows individual journal entries with debit/credit/net and trial balance cross-check. P&L and BS detail lines in consolidated_report.html are clickable links. Pennylane-sourced accounts drill to journal entries; Excel-sourced accounts show "agrégat mensuel" badge. Breadcrumb navigation: Report → Line → Account.

Deferred: - Chart.js / ApexCharts visualizations — not implemented. Additive UI work, no data dependency. - "As-reported" vs "restated" dual-view toggle — not implemented. The versioning infrastructure exists (time-versioned GLMappingRule + ReportLineCatalog), but the UI toggle and query-time branching are not wired. Full "as-reported" snapshots also depend on close snapshot work (Feature 1.9 deferred item).

1.7 — Practice group scorecards — COMPLETE¶

Description. A per-practice-group "scorecard" page showing: current-period P&L summary (revenue, gross profit, EBITDA, net profit + YoY), cash position, top 5 practitioners by revenue, no-show rate, equipment service status, current budget vs actuals (reusing apps/budgets/BudgetVersion data at practice level). This is the daily-use dashboard for a practice manager — shorter and more actionable than the full P&L.

Why. Practice managers don't need the full 47-line P&L; they need a decision dashboard. This is the first time a practice manager sees finance and PMS data side-by-side without opening two apps. Pennylane data. Monthly P&L per entity. PMS join. Dentist revenue, no-show rate, equipment service, budget actuals. Scope. Per practice group by default; toggle to per-entity (legal entity) for finance users. Excel GL? Only for entities that need Excel (none of the practice-group operating practices — they're all on Pennylane). Persona. Practice Manager primarily, Operations Manager secondarily. Complexity. M. Reuses 1.6's data layer. Moat. High. This is a finance ↔ PMS join by definition — no other tool has both sides.

Implementation (2026-04-13, updated 2026-04-14). ScorecardView in apps/finance/views.py + ScorecardService in apps/finance/services/scorecard.py. Scoped to practice groups only. Finance KPIs (Revenue, Gross Profit, EBITDA, Net Profit + margins) from ReportService. PMS KPIs: active dentist count, top 5 practitioners by revenue (from Procedure.fees), no-show rate + estimated no-show cost (from Appointments + DentistRevenueTarget via NoShowCostService). Budget vs actuals summary (budget revenue, budget EBITDA, revenue variance) from BudgetPnLAssumption via active BudgetVersion. Drill-down links to dentist P&L, no-show cost detail, and budget vs actuals dashboard. Not yet included: cash position, equipment service status.

1.8 — Historical versioning of report line catalog + GL mapping — COMPLETE¶

Description. Add effective_from / effective_to columns on report_line_catalog and gl_mapping_rule. Every change goes through a new row (never in-place updates). Historical reports always fetch "the version of the catalog / mapping in force at the close of period P". Add an admin UI to preview the impact of a proposed mapping change on historical numbers before committing it. Ship a diff/audit log so finance can see every mapping change in a single place.

Why. GL mappings and report formats evolve over time. Without versioning, changing a mapping in March retroactively changes January's numbers, and historical comparisons break. The "as-reported" vs "restated" dual-view depends on this. Pennylane data. Both. PMS join. None. Scope. Foundation. Complexity. M. Schema + admin UI + preview logic + audit log. Moat. Low.

Implementation (2026-04-13). Both ReportLineCatalog and GLMappingRule have effective_from/effective_to with unique constraints on (internal_code, effective_from) and (country, account_code, effective_from). MappingChangeLog model provides an immutable audit trail (change_area, action, old/new values JSON snapshots, changed_by, effective_date). MappingVersionService in apps/finance/services/versioning.py handles supersede_mapping, retire_mapping, supersede_catalog_entry — all create new rows, never in-place edits, and log to MappingChangeLog. MappingImpactService provides preview() showing which amounts would move if a mapping changed. Two views: MappingAuditLogView (paginated, filterable audit log) and MappingImpactPreviewView (impact preview UI). Templates: mapping_audit_log.html, mapping_impact_preview.html.

1.9 — Monthly close workflow — COMPLETE¶

Description. Per-entity monthly close status (open → in_close → closed). Close calendar enforcement (~6th preliminary, ~20th final). "Frozen at close" snapshot preserving exactly what was reported. "Current truth" toggle showing late-arriving entries by accounting_date. Delta view (what changed since close). Re-open requires CFO-role approval with audit trail. Notifications to finance team at each stage via existing Celery + email plumbing.

Why. The finance team's current Excel close process (v1 → v6) is what gives them confidence in the numbers. Without a close workflow in Aletheia, previously-reported numbers can drift as new entries arrive, destroying trust and blocking the switch from Excel. Pennylane data. Both paths. PMS join. None. Scope. Per entity. Excel GL? Both. Persona. Finance. Complexity. M. Schema (see period_close in entity-model.md §4), snapshot logic, dual-view queries, re-open approval flow. Moat. Medium. The close workflow is specific to the group's process.

Implementation (2026-04-13). CloseWorkflowService in apps/finance/services/close.py manages the open → in_close → closed state machine with InvalidTransitionError for invalid transitions. Methods: start_close, finalize_close, reopen (requires reason). get_status_grid() produces the entities × periods display matrix. CloseWorkflowView shows a grid of entities × last 6 periods with color-coded status cells and action buttons. CloseActionView handles POST transitions. Re-open requires reason (audit trail via reopened_at, reopened_by, reopen_reason on PeriodClose). Deferred to Phase 3: "frozen at close" snapshot (materialized snapshot per entity/period at close time), "current truth" toggle and delta view, close calendar enforcement, email notifications at each stage.

Phase 1 — what it explicitly does not include¶

Updated 2026-04-16. Items marked ✅ were delivered in Phase 2 or later.

✅ ~~No budgeting redesign~~ → Entity-scoped budgeting delivered (Feature 2.5).
✅ ~~No cost-of-sale deep dives~~ → Prosthetics deep-dive (2.1) and supplier deep-dive (2.8) delivered.
✅ ~~No workflow automation~~ → Close checklist (3.1), invoice approval (3.2), missing-doc chaser (3.3) delivered (2026-04-16).
No forecasting (no 13-week rolling, no annual P&L forecast, no scenario modelling) — Phase 3 Feature 3.4.
No cash flow forecast — Phase 3 Feature 3.4.
No AI / NL features (beyond prosthetics extraction 2.1) — Phase 3 Features 3.5–3.8.
No FEC export, no TVA dashboard — Phase 3 (or never, depending on Decisions needed).
No direct Pennylane write-back — permanently out of scope.

8. Phase 2 — Moat features¶

Theme. The finance ↔ PMS joins that justify building this inside Aletheia. Cost intelligence on the biggest variable cost (prosthetics labs) and the biggest operational lever (per-dentist performance). Entity-scoped budgeting and the first forecasting capability.

Status: Complete (2026-04-14). All 8 features implemented and tested.

Why this phase now. - Phase 1 delivered the platform. Phase 2 delivers the differentiator. - Phase 1 rebuilt what the Excel already had. Phase 2 delivers what the Excel cannot. - Each feature is a finance↔PMS join that Pennylane + BI cannot do alone.

Foundational work required. Minimal new foundation — Phase 2 runs on the Phase 1 data model. Two additions: (a) the apps/budgets/ module gets entity-scoped budget versions, and (b) the Pennylane sync extends to pull supplier invoice PDFs for the AI extraction feature.

Success metrics. - Practice managers use the prosthetics cost page monthly instead of asking the finance team for lab breakdowns. - The per-dentist P&L is used in quarterly compensation reviews. - The first annual budget cycle (for FY27) runs on Aletheia, not Excel. - EBITDA forecast accuracy (rolling 3-month) is within ±5% of actual.

Key risks and open questions. - The prosthetics AI extraction was proven in penlane_tst but only on one vendor format. Generalizing to all prosthetics labs needs sample data from each vendor. - Per-dentist P&L requires a clean name-to-Dentist match for every RETRO <NAME> account — the admin override UI from Phase 1 must be well-maintained. - Forecasting accuracy depends on historical quality — the data needs to go back 2+ fiscal years for meaningful trends.

Phase 2 features¶

#	Feature	Status	Pennylane data	PMS join	Scope	Persona	Complexity	Moat
2.1	Prosthetics / lab cost deep-dive	Complete	Supplier invoices + PDFs via API v2; Claude AI PDF extraction	Procedure type (CCAM), tooth numbers, patient age	Per entity / practice group	Finance, Practice Manager	L	High
2.2	Per-dentist P&L	Complete	`RETRO <NAME>` GL accounts	DentistContract, Procedure, Payment, WorkSchedule, RevenueTarget	Per entity with practitioner drill-down	Finance, Practice Manager, HR	M	High
2.3	Revenue-to-invoice funnel	Complete	Customer invoices (Pennylane)	Procedure fees, Payment records, Appointment status	Per entity / practice group	Finance, Practice Manager	M	High
2.4	No-show cost analysis	Complete	Revenue data per practice	Appointment status (`absent_non_excuse`, `absent_excuse`)	Per entity / practice group	Practice Manager	S	High
2.5	Entity-scoped budgeting	Complete	Actuals from 1.6	BudgetVersion per entity, per scope	All scopes	Finance, CFO	M	Medium
2.6	Annual budget vs actuals dashboard	Complete	Actuals	Budget lines	Per entity through Group excl. BE	Finance, Exec	S	Medium
2.7	Cross-practice benchmarking pack	Complete	Actuals	Patient/appointment/equipment data	Practice group peer set / vintage	Exec, Ops Manager	M	High
2.8	Supplier deep-dive	Complete	Supplier invoices	None	Per entity through Group excl. BE	Finance	M	Medium

2.1 — Prosthetics / lab cost deep-dive¶

Description. The biggest variable cost in a French dental group is prosthetics. penlane_tst already proved the AI pipeline: download supplier invoice PDFs via the Pennylane API v2, extract structured line items (tooth number, material, unit price, quantity) with Claude, store the extracted data, and produce per-tooth / per-practitioner / per-patient / per-procedure cost reports. Phase 2 productizes that pipeline:

Nightly Celery task downloads new supplier-invoice PDFs for all labs (filter by supplier-category mapping).
Each PDF is run through Claude for extraction. The extracted rows are stored in a new prosthetics_line_item table linked to supplier_invoice.
A finance dashboard shows: lab-by-lab cost trends, material-mix changes, outlier detection (tooth unit price > 2σ from historical), per-practitioner prosthetics cost, per-procedure prosthetics cost, per-patient-age-bracket analysis.
Drill-down from consolidated P&L line "Prothesis" → lab → monthly PDF → extracted line item.

Moat. High. Pennylane cannot tell you which tooth each invoice line refers to. Aletheia can, because it joins to the Procedure.tooth_number field for the same patient. Complexity. L. The pipeline exists in prototype; productizing it means handling vendor format variance, re-extraction on mismatch, caching, cost control, and per-vendor validation.

Implementation (2026-04-14). ProstheticsService in apps/finance/services/prosthetics.py. Full pipeline: Pennylane API v2 supplier invoice sync → auto-detect prosthetics lab suppliers → PDF download → Claude AI extraction (claude-sonnet-4-20250514) → ProstheticsLineItem storage. Dashboard views: ProstheticsOverviewView (lab summaries, material-mix, outlier detection >2σ, per-dentist costs, per-work-type), ProstheticsLabDetailView (monthly trend + invoices for one lab), ProstheticsInvoiceDetailView (extracted line items for one PDF). Models: LabSupplierMapping, SupplierInvoice, ProstheticsLineItem.

2.2 — Per-dentist P&L¶

Description. For each practicing dentist, produce a monthly P&L showing: revenue generated (Procedure.fees summed from Logosw / PMS), rétrocession paid (from the GL RETRO <NAME> account), net contribution, scheduled working days, actual working days, daily average, quarter-over-quarter change. Split by procedure type (CCAM grouping) for context. Aggregates to the practice-group and entity levels via the foundation's aggregation tree.

Why this is possible and unique. The GL has the rétrocession side, Aletheia has the revenue side — and the tooth-level procedure data that tells you what kind of work produced the revenue. Available as a per-dentist drill-down from the Practice Group Scorecard (1.7) and from the full P&L Analysis Table (1.6). Moat. High. Complexity. M. The data model is mostly in place after Phase 1; the work is layout, joins, and UX for per-dentist comparison.

Implementation (2026-04-14). DentistPnLService in apps/finance/services/dentist_pnl.py. Joins GL RETRO <NAME> accounts (via DentistAccountMapping auto-matching) with PMS Procedure.fees per dentist. Computes: revenue, retrocession, net contribution, retro %, scheduled vs actual working days, daily average, QoQ change, CCAM grouping breakdown. DentistPnLView at /finance/dentist-pnl/. Accessible from the Practice Group Scorecard and the Analysis Table drill-down link.

2.3 — Revenue-to-invoice funnel¶

Description. Track each procedure through a funnel: planned (quote/devis) → performed → invoiced → paid (patient side) → reimbursed (AMO/AMC side). Surface bottlenecks: procedures performed but not invoiced, invoices issued but not paid, AMO rejections awaiting follow-up. The current apps/procedures/ already tracks patient_payment_datetime, amo_amount, amo_paid, amo_response_date — Phase 2 ties that to the Pennylane customer-invoice side for cross-check.

Why this is unique. Pennylane sees the accounting flow (invoice → receivable → cash). Logosw sees the clinical flow (procedure → patient payment). Neither sees both. Aletheia does. Moat. High. Complexity. M. Cross-matching Pennylane customer invoices to Logosw patient payments is fuzzy (dates, amounts, patient IDs). Expect manual-override tooling similar to the RETRO-to-Dentist matcher.

Implementation (2026-04-14). FunnelService in apps/finance/services/funnel.py. Tracks procedures through: performed → invoiced → patient_paid → amo_reimbursed. Cross-checks Pennylane CustomerInvoice (new model synced from Pennylane) against PMS Procedure payments. FunnelOverviewView at /finance/funnel/. Deviation: fuzzy cross-matching of Pennylane invoices to PMS procedures is not yet implemented — the funnel shows both sides independently with summary discrepancy metrics rather than row-level matching.

2.4 — No-show cost analysis¶

Description. Compute the financial cost of no-shows: for each no-show appointment, estimate the lost revenue (based on dentist's daily target × duration / working day) and aggregate to practice, dentist, patient-segment, and time-of-day. Expose as a dedicated page and as a line in the Practice Group Scorecard. Trend YoY.

Why. Practice managers have asked for this per the roadmap/ideas/dashboard-analytics.md. The data is already in the PMS side; Phase 2 adds the financial interpretation (lost revenue value). Moat. High (pure PMS + finance target join). Complexity. S. Small feature, high signal.

Implementation (2026-04-14). NoShowCostService in apps/finance/services/noshow.py. Estimates lost revenue per no-show appointment: daily_target × (duration_minutes / 480). Links appointments to dentists via doctolib_agenda_id → DentistContract.doctolib_agenda_id. Aggregates by dentist (sorted by cost), time-of-day bracket, patient type (new/returning), excuse status (excused/unexcused), and YoY comparison. NoShowCostView at /finance/no-show-cost/. Integrated into the Practice Group Scorecard as a cost line with drill-down link. 28 tests.

2.5 — Entity-scoped budgeting¶

Description. Extend apps/budgets/BudgetVersion to support entity-scoped budgets (not just practice-scoped). A BudgetVersion targets an Entity OR a Practice (mutually exclusive). New assumption tables: BudgetPnLAssumption (revenue/cost by report line, per month), BudgetScenario (what-if modelling). Use the existing draft → submitted → validated workflow.

Why. Phase 1's budget app is per-practice, which only works for operating practices. Service companies, holdings, and the full group need entity-level budgets. Moat. Medium. The workflow is standard; the entity/scope dimension is the differentiator. Complexity. M.

Implementation (2026-04-14). BudgetVersion.practice made nullable, new BudgetVersion.entity FK (mutually exclusive via DB check constraint). New models: BudgetPnLAssumption (report_line × period → amount, unique per version), BudgetScenario (M2M wrapper for what-if comparison). BudgetVersion.duplicate() copies PnL assumptions. Full CRUD views for assumptions at /budgets/versions/<pk>/pnl/. Active constraint: one active version per entity/FY (separate from practice/FY constraint). Admin updated. Migration budgets/0002_entity_scoped_budgeting. 32 tests.

2.6 — Annual budget vs actuals dashboard¶

Description. Side-by-side dashboard: current actuals (from 1.6) vs active budget (from 2.5), per P&L line, per month, per scope. Highlights variances > threshold. Per-line commentary field for finance to annotate (feeds back into the monthly reporting narrative).

Moat. Medium. Complexity. S.

Implementation (2026-04-14). BudgetVsActualsService in apps/finance/services/budget_vs_actuals.py. Joins ReportService actuals with BudgetPnLAssumption data per P&L line. Computes variance, variance %, flags lines exceeding a configurable threshold (default 10%). Budget lookup: entity-scoped active budget preferred, falls back to practice-scoped. New BudgetLineCommentary model for per-line narrative annotations (update_or_create via inline POST form). BudgetVsActualsView at /finance/budget-vs-actuals/ + BudgetCommentarySaveView for commentary save/delete. Migration finance/0006_budget_line_commentary. 25 tests.

2.7 — Cross-practice benchmarking pack¶

Description. Peer comparison across practice groups (with vintage cohort grouping for acquisition-year analysis): KPI-by-KPI tables and charts. Metrics include revenue per dentist, associate fees ratio, EBITDA margin, DSO, FTE per dentist, no-show rate. Drill-down from each KPI to the contributing entity data.

Why. Multi-practice groups live or die by being able to compare sites. This is the "scoreboard" feature. Moat. High. The metrics come from both sides. Complexity. M. Reuses 1.6 + 1.7 + 2.2 + 2.4 data; work is layout and comparison logic.

Implementation (2026-04-14). BenchmarkingService in apps/finance/services/benchmarking.py. Enumerates all practice_group scopes. For each: finance KPIs from ReportService (revenue, associate fees, EBITDA, trade receivables), PMS KPIs (active dentists, PMS revenue, no-show rate + cost via NoShowCostService). Derived: revenue per dentist, associate fees ratio, EBITDA margin, DSO (trade receivables / daily revenue), FTE per dentist (placeholder — requires HR data). Vintage cohort grouping from Entity.acquisition_fy. Cross-group averages. BenchmarkingView at /finance/benchmarking/. Drill-down to individual scorecards. 19 tests. Deviation: FTE per dentist uses a placeholder (dental assistant FTE is zero) because the PMS model does not track non-dentist staff roles. Full FTE tracking requires HR data integration.

2.8 — Supplier deep-dive¶

Description. For each supplier in Pennylane, show monthly spend, invoice count, average invoice size, payment terms actuals (DPO), and spend trend. Filter by category. Identify suppliers whose spend is growing faster than revenue.

Moat. Medium (Pennylane-only data, no PMS join). Complexity. M.

Implementation (2026-04-14). SupplierDeepDiveService in apps/finance/services/supplier.py. get_overview(scope, period, category): per-supplier aggregation with invoice count, total spend, avg invoice size, DPO (avg of due_date − invoice_date), YoY change, spend-to-revenue ratio (revenue from GLTrialBalanceEntry class-7 accounts), faster-than-revenue flag. get_supplier_detail(name, scope, period): single-supplier view with 12-month trend (via TruncMonth) and invoice list. Category filter from LabSupplierMapping. SupplierOverviewView at /finance/suppliers/, SupplierDetailView at /finance/suppliers/<name>/. 27 tests. Deviation: bank transaction data (mentioned in the spec) is not used — DPO is computed from invoice dates only. Bank transaction integration would require the Pennylane sync to pull bank_transactions (Data Sharing), which is deferred to Phase 3 (Feature 3.3/3.4).

Phase 2 — deferred items¶

Items originally planned for Phase 2 but intentionally deferred:

Forecasting. The roadmap mentioned "first forecasting capability" in the Phase 2 theme. Rolling P&L forecast and scenario modelling are deferred to Phase 3 — BudgetScenario model exists but the forecasting engine is not built.
FTE per dentist (full). Benchmarking uses a placeholder for dental assistant FTE. Full FTE tracking requires HR/staff data integration (role-based headcount per practice) which is not in the PMS model.
Funnel row-level matching. Revenue-to-invoice funnel shows both sides (PMS + Pennylane) with summary discrepancies, but does not yet do fuzzy row-level matching of individual invoices to procedures.

9. Phase 3 — Automation and AI¶

Theme. Automate the manual work (month-end close, invoice approval, missing-document chasing) and layer AI on top (universal invoice extraction, transaction categorization, natural-language Q&A, cash-flow forecasting).

Why this phase now. - Phase 2 shipped the dashboards. Phase 3 reduces the work that happens before the dashboards. - By Phase 3, the data is rich enough for meaningful AI (multi-quarter history, dentist matching, prosthetics extraction coverage, elimination rules well-understood). - Some features (13-week rolling, NL Q&A, transaction auto-categorization) land late because they depend on data volume.

Foundational work required. A rules engine for the approval routing / missing-doc chaser (new app apps/finance_workflow/). Minor extensions to the Pennylane sync for bank transactions (already in Data Sharing).

Success metrics. - Month-end close time reduced from X days to Y days (measure before starting). - Invoice approval SLA (time from supplier invoice received to approved) reduced measurably. - Cash forecast accuracy (13 weeks) within ±10% of actual. - Finance team uses NL Q&A for ad hoc questions instead of interrupting engineering.

Phase 3 features¶

#	Feature	Status	Pennylane data	PMS join	Scope	Complexity	Moat
3.1	Month-end close checklist & automation	Complete	Both paths	—	All scopes	M	Medium
3.2	Invoice approval routing	Complete	Supplier invoices	Practice, entity	Per entity	M	Medium
3.3	Missing-document chaser	Complete	Bank transactions without matching invoices	—	Per entity	S	Medium
3.4	13-week rolling cash flow forecast	Not started	Bank transactions, customer/supplier invoices	Appointment volume, planned capacity	All scopes	L	High
3.5	Universal supplier-invoice line extractor (AI)	Not started	Supplier invoices + PDFs	—	Per entity	L	Medium
3.6	Bank transaction auto-categorization (AI)	Not started	Bank transactions	—	Per entity	M	Medium
3.7	Natural-language financial Q&A (AI)	Not started	All GL + PMS	Full	Any scope	L	High
3.8	Anomaly / fraud detection	Not started	All GL	PMS patterns	Per entity through Group excl. BE	M	High

3.1 — Month-end close checklist & automation — COMPLETE¶

Description. A checklist UI that tracks the monthly close process per entity: Pennylane sync done, GL imports done, adjustments entered, reconciliations clean, eliminations validated, reports published. Each item has an owner and a due date. Automated where possible (Pennylane sync step auto-checks when sync succeeds). Exportable close report for the audit trail.

Complexity. M. Moat. Medium. The orchestration is specific to the group's process.

Implementation (2026-04-16). New apps/finance_workflow/ app. CloseChecklist + ChecklistItem models with per-entity/period checklists. ChecklistService in services/checklist.py manages item completion, auto-checks (Pennylane sync status, GL import status), and checklist generation from templates. ChecklistListView + ChecklistDetailView at /workflow/checklists/. NotificationService in services/notifications.py for email alerts at each stage. 307 tests.

3.2 — Invoice approval routing — COMPLETE¶

Description. For supplier invoices flagged above a threshold or coming from an unapproved vendor, route for approval before they reach Pennylane's payment queue. Practice manager approves up to €X, ops manager up to €Y, CFO above. Notifications via email (SMTP already configured). Audit trail per approval step.

Complexity. M. Moat. Medium.

Implementation (2026-04-16). ApprovalRequest + ApprovalStep + ApprovalRule models in apps/finance_workflow/models.py. ApprovalService in services/approval.py — threshold-based routing, multi-level approval chain, audit trail per step. ApprovalQueueView at /workflow/approvals/. Integrated with NotificationService for email on pending/approved/rejected. 316 tests. Deviation: approval routing is within Aletheia only — no Pennylane write-back to block payment queue (as spec'd). Finance must manually hold invoices in Pennylane based on Aletheia approval status.

3.3 — Missing-document chaser — COMPLETE¶

Description. Bank transactions without a matching supplier invoice are flagged. Email chaser to the practice that owns the card/account. Triage UI for finance to close out stale items.

Complexity. S. Moat. Medium.

Implementation (2026-04-16). MissingDocument model + ChaserService in apps/finance_workflow/services/chaser.py. Matches bank transactions against supplier invoices by entity + date window + amount tolerance. Unmatched transactions flagged as MissingDocument rows. MissingDocTriageView at /workflow/missing-docs/ — triage UI with resolve/dismiss actions. Automated email chaser via Celery task (send_chaser_emails). 275 tests. Deviation: bank transaction matching uses Pennylane supplier_invoices table only (not bank_transactions from Data Sharing, which is synced but not yet consumed by this feature). Matching is date+amount heuristic, not bank reconciliation ID.

3.4 — 13-week rolling cash flow forecast¶

Description. Weekly-granularity cash forecast for the next 13 weeks per entity (and aggregated per scope). Inputs: (a) historical bank transaction patterns, (b) known scheduled payments (supplier invoices due), (c) expected receipts (customer invoice aging + historical DSO), (d) forward revenue from Aletheia's appointment book × historical conversion rate × average ticket. The PMS feed is a material input — no standalone BI tool has it.

Moat. High. The appointment-book-driven revenue forecast is unique. Complexity. L. Weekly granularity, multi-source inputs, model validation.

3.5 — Universal supplier-invoice line extractor (AI)¶

Description. Generalize the prosthetics AI extraction from 2.1 to all supplier invoices. Each vendor's PDFs are extracted into structured line items. Extracted data enriches the supplier-deep-dive and feeds the anomaly detector (3.8).

Complexity. L. Vendor-format variance is the hard part. Moat. Medium (Pennylane has the PDFs but not the extraction).

3.6 — Bank transaction auto-categorization (AI)¶

Description. Classify unreconciled bank transactions into GL accounts using Claude + a per-account training history. Finance reviews, accepts / rejects, the model improves. Feeds back into Pennylane by making the approval queue shorter, not by writing back directly.

Complexity. M. Moat. Medium.

3.7 — Natural-language financial Q&A¶

Description. A chat interface that lets finance, practice managers, and the exec team ask questions in natural language ("What was PDS's EBITDA last quarter vs Q4 FY25?", "Which dentist at VSM had the highest associate fee ratio?"). Uses Claude with tool access to the unified GL store and the PMS data model. Scoped by the user's feature permissions.

Moat. High. The tool-calling surface is specific to Aletheia's joined-data model. Pennylane's NL Q&A can't reach the PMS side; a general BI NL tool can't reach either side with the right semantics. Complexity. L. Safety (wrong answer risk), cost control, schema exposition.

3.8 — Anomaly / fraud detection¶

Description. Detect unusual patterns: GL accounts deviating from historical baselines, cash flows inconsistent with appointment volume, duplicate supplier invoices, dentists whose revenue pattern changes abruptly, prosthetics unit costs outside expected range. Uses a mix of classical statistics (z-score on category time-series) and AI for narrative / explanation.

Moat. High. Cross-domain anomaly detection isn't possible without both sides. Complexity. M.

10. Decisions needed¶

Surfacing the tradeoffs — not resolving them. Each item here is a decision the team must make before the relevant phase starts.

Architectural¶

Live Redshift queries vs local DuckDB replica. pennylane_reusable supports both via one env var. For production, do we query live (fresh data, latency cost, Redshift cost) or replicate nightly (stale data, no query latency, free local queries)? Proposed: nightly replica by default, live query as an admin escape hatch for ad hoc investigations.
~~Local replica storage: DuckDB vs PostgreSQL.~~ RESOLVED. PostgreSQL. All financial tables live in the shared PostgreSQL database alongside PMS tables. DuckDB was prototype-only.
Build in-app dashboards vs pipe to a BI tool. Phase 1 clearly builds in-app (the moat requires it). Phase 3 (especially 3.7 NL Q&A) might benefit from a headless BI backend (Metabase?). Decide per-phase.
Refresh cadence and close-window sync. Nightly for Pennylane. On-demand for Excel GL import (triggered by finance upload). Practice-group-scorecard operational data is real-time from the PMS side. Additionally: the monthly close process (6th → 15th → 20th) means finance may re-trigger Pennylane sync multiple times within a close window to pick up accountant corrections. Allow manual sync trigger for Finance Manager role, not just the nightly schedule.

Data and modelling¶

~~GL mapping: import-time vs query-time.~~ RESOLVED. Query-time. Enables both "as-reported" and "restated" views without re-importing historical data. If performance concerns materialize, add materialized views — but don't pre-optimize.
~~Chart-of-accounts: one global table or per-country.~~ RESOLVED. One gl_mapping_rule table with a country column. Adding a new country = adding rows, not a new table. All countries map to the same 47 report-line target catalog.
~~Fiscal year alignment.~~ RESOLVED. Management reporting FY is Oct 1 – Sept 30 across all entities and countries. The "31/12" label in the current Excel is a stale typo. Legal FY also aligns to Oct-Sept for all current entities. New acquisitions transition to Sept 30 close via shortened/extended transitional year.
~~How many scopes are actually used?~~ RESOLVED. Replaced by the corrected aggregation model in entity-model.md §5. All listed scopes (practice groups, subtotals, group totals, vintages) are used or planned. Scope membership is computed from entity attributes, not manually enumerated.
SPFPL holding toggle. Are PDSh and VSMh always hidden in the practice manager view (default practice group) or exposed as a toggle? Finance needs them visible; practice managers don't. Proposed: always hidden by default, with a per-feature-permission toggle.
Reference format evolution policy. The report format evolves between fiscal years (and occasionally mid-year). Who approves format changes? Is it a CFO sign-off, an engineering release, or both? Are practice managers allowed to see a previous format while finance is on a newer one? Propose: versioned report templates, practice managers see latest-approved, finance sees latest-draft, draft → approved promotes to all users.

Workflow and governance¶

Aggregation scope configuration. Where does the aggregation tree live? Proposed: database table (aggregation_scopes) editable via Django admin by admin-role users. Not a config file (would need redeploys), not code (same issue).
Excel GL import template ownership. Who owns the canonical template and its validation rules? Proposed: finance owns the business logic (which columns exist, what's required), engineering owns the validation implementation (the importer class). A Finance Manager-role user can update the mapping rules via admin UI.
Intercompany elimination: declarative vs imperative. Already decided in the foundation — declarative, stored in intercompany_elimination_rules. The question is how expressive the DSL needs to be: are all current Excel eliminations reducible to (entity_from, entity_to, lines, scope_level) tuples, or do we need a more flexible rule language for edge cases like equity-method eliminations that chain across multiple entities? Need a finance interview to verify.
Non-Pennylane entity cadence. Monthly upload only, or push toward more frequent imports as Luxembourg/Belgium ramp up? Proposed: monthly is sufficient today; add a "partial/interim upload" mode if pressure builds.
CHC onboarding cutover. CHC is coming onto Pennylane soon. What's the cutover plan? Proposed: Phase 1 supports Excel GL for CHC; when Pennylane is live, add CHC's pennylane_company_id to the entities row and flip pennylane_status='active'. Duplicate data during the overlap period is accepted; a reconciliation report catches drift.
CL / CB conversion preview. When a convertible eventually converts, the consolidation impact must be preview-able. Build this as a Phase 1 "fire drill" feature (simulate the conversion, show the delta on the consolidated P&L / BS / CF / tax), or defer to the cap table roadmap? Proposed: build the simulation in the cap table roadmap, but use the finance roadmap's rules engine so both sides see the same preview.
~~Cap table roadmap dependency gating.~~ RESOLVED. Individual holders are populated from day one — both roadmaps ship Phase 1 together. No phased migration needed.

French-specific / regulatory¶

FEC generation. French tax authorities require a standardized Fichier des Écritures Comptables (FEC) on audit request. Pennylane likely generates it for the 7 FR entities. Does Aletheia need to generate one for consolidation purposes? Probably not — audit applies per legal entity and Pennylane handles that. Not in scope for Phases 1–3. Revisit if an audit requires a consolidated FEC.
TVA dashboard. French VAT is complex and Pennylane handles the declaration. An in-app TVA dashboard is low moat (pure Pennylane data). Propose: defer indefinitely; add only if explicitly requested.
URSSAF / social charges. Payroll data is in class 64 GL accounts. The reporting already captures "Colleague Costs" with a breakdown (Gross wages, Social security contributions, Other payroll costs). A Phase 3 feature could track social charge rates over time and flag anomalies. Not in current scope.
PCG conformity monitor. Check that the FR MAPPING table covers all Pennylane GL accounts and flags new unmapped accounts. Small feature, belongs in Phase 1 tooling but not highlighted as a headline feature.
Aggregation scope: computed membership vs explicit membership. The group grows 5-10 practices/year. Should scope membership be computed from entity attributes (country, type, acquisition_date) via rules — or explicitly maintained via M2M tables that must be manually updated on every acquisition? Computed is less maintenance; explicit is more flexible for edge cases. Recommend computed with manual override capability.
Close snapshot implementation. Two approaches: (a) store a materialized snapshot per (entity, period) at close time (duplicates data but fast queries), (b) use booking_date <= closed_at as a query filter on live GL to reconstruct the "as-closed" view (no duplication, but relies on booking_date being accurate). Recommend (a) for trust/auditability — the snapshot is the canonical "what we reported."
Late entries: book to accounting_date or to current period? With the new system, late entries are stored with their true accounting_date (January) even if booked in March. The "current truth" view shows them in January. The "as-reported" view still shows the frozen January. This is different from the Excel-era convention (where late entries effectively landed in the current period). Confirm this is the desired behavior. Finance sees both views and can choose.

11. Appendix A — Pennylane capability reference¶

Do not duplicate the Pennylane-reusable README here. Refer to:

Main index: temp/pennylane_reusable/README.md — lists every file, access method, architectural pattern, and the bootstrap checklist.
Access modules:
db.py — dual Redshift / DuckDB connection layer
pennylane_api.py — REST API v2 wrapper (cursor pagination, retry, per-company tokens, fresh-URL PDF download)
sync_redshift_to_local.py — incremental ETL with _sync_meta checkpoints and per-company parallelism
Schema conventions: all tables have synchronized_at (incremental key), deleted_at (soft delete), and company_id (entity scope). Two source schemas exist in Redshift: etl and pennylane.
Data locations — Data Sharing only: general_ledger, trial_balance, bank_transactions. API v2 only: customers, suppliers, products, plan_items, individual invoice_lines, PDFs. Both: companies, customer_invoices, supplier_invoices, bank_accounts.

When implementing Phase 1.2 (Pennylane sync pipeline), the first action is to copy the reusable bundle into apps/pennylane_sync/ and run compare_access_methods.py against the real credentials to confirm end-to-end connectivity.

12. Appendix B — Existing reporting format reference¶

Do not duplicate the catalog in temp/pennylane_reusable/reference/README.md. Key pointers for the finance-roadmap work:

Canonical format file: temp/pennylane_reusable/reference/reporting incl mappings/FY25 Suffren - 02 2026 - Mngt reporting v6.xlsx (the latest version at time of writing).
Sheets to read first: Conso. (target format for consolidated P&L / BS / CF + Analysis Table + KPI block), Compil. (monthly time-series structure), Contrib. (per-entity contribution view), FR MAPPING (~469 rows, the GL → Mapping 1/Mapping 2 rulebase for Phase 1.4 seeding).
Non-Pennylane TB sheets: AST FY26, SDT FY26 (Belgian, with Compte / Libellé local (NL) / Libellé FR / TYPOLOGIE / PROJECT / monthly columns), SUI FY26 (Luxembourg, with Reference / Description / Description 2 / monthly columns). These three shapes must be unified into one canonical template for Phase 1.3.
Group name convention: "Groupe Suffren" is the internal commercial name of the group. When you see "Suffren" in filenames, it refers to the consolidated group, not a specific entity.
Close iterations: file goes v1 → v6 per month's close cycle (not per fiscal year). v1 = first GL pull ~6th, v6 = final closed ~20th. Report format evolves between fiscal years.
Known gaps in the current Excel (from §3 above):
No CHC sheet yet
"FYE 31/12" label is a stale typo — confirmed Oct-Sept
Non-FR charts of accounts mapped via per-sheet VLOOKUPs, not a shared mapping
Adjustments have no audit trail
Cross-entity eliminations are implicit in formulas

When implementing Phase 1.4 (unified financial data model + GL mapping), the first action is to import FR MAPPING into gl_mapping_rule with country='FR' and effective_from=2024-10-01 (or earliest FY25 date you want history for).

End of roadmap v1. Feedback on this document goes to the finance roadmap owner. Any change that touches the entity, ownership, or consolidation model must be reflected in docs/entity-model.md first.