Analytics recipes and query builder

This page explains the query path of the analytics module. src/cluster_erp/modules/analytics/registry.py holds the validated catalogue, src/cluster_erp/modules/analytics/query_builder.py builds safe aggregate queries with correctness gates, and src/cluster_erp/modules/analytics/recipe_engine.py with recipe_schemas.py runs declarative preparation recipes. How the catalogue is filled by other modules is on analytics facts.

In short¶

• A user asks with semantic keys only: a fact, measures, dimensions, filters, order, and a page (src/cluster_erp/modules/analytics/schemas.py:1-11, src/cluster_erp/modules/analytics/schemas.py:47-61).
• Table and column names come only from module declarations; values are bound parameters. There is no string concatenation into SQL (src/cluster_erp/modules/analytics/query_builder.py:5-10).
• Four gates refuse a number that would be wrong: non-additive sums, semi-additive sums over an unpinned axis, mixed currencies, and dates without a grain (src/cluster_erp/modules/analytics/query_builder.py:11-23).
• A recipe is a stored list of steps from a closed vocabulary; the engine records rows in and rows out for each step (src/cluster_erp/modules/analytics/recipe_engine.py:1-21, src/cluster_erp/modules/analytics/recipe_schemas.py:1-33).

How it works¶

The recipe and query pipeline¶

flowchart TD
 A["POST /analysis/run with AnalysisSpec"] --> C["AnalyticsService.catalogue: collect and visible_to"]
 P["POST /analysis/prepare with RecipeDefinition"] --> C
 C --> F["FactCatalogue.get fact"]
 F --> S{"summaries.plan fits"}
 S -->|yes| QS["query_builder._build_from_summary"]
 S -->|no| QB["query_builder.build"]
 QB --> G["gates: additivity, currency, grain"]
 QS --> G
 G --> X["execute, cap at MAX_ROWS"]
 X --> R["AnalysisResult with source summary or live"]
 F --> RE["RecipeEngine.run"]
 RE --> ST["apply steps, count rows in and out"]
 ST --> EX["check expectations"]
 EX --> RR["RecipeRunResult with observations"]

In prose:

1. Both entry points first build the caller's catalogue: registry.collect asks every module, and visible_to keeps only fact sets whose read_permission the caller holds (src/cluster_erp/modules/analytics/service.py:55-62, src/cluster_erp/modules/analytics/registry.py:100-109).
2. For a run, summaries.plan decides whether the pre-aggregated cells can answer; otherwise the query runs on the fact table (src/cluster_erp/modules/analytics/service.py:139-148). See reports, summaries, and cross.
3. query_builder.build builds one relation with all declared columns, adds grouped dimensions, measures, filters, the live filter, order, and a page of at most MAX_ROWS 10000 plus one row to detect truncation (src/cluster_erp/modules/analytics/query_builder.py:71-96, src/cluster_erp/modules/analytics/query_builder.py:375-470).
4. The result states whether it came from summary or live, and why the live path was taken (src/cluster_erp/modules/analytics/service.py:157-168).
5. For a recipe, the engine starts from every declared column keyed by its semantic name, applies the live filter, runs each enabled step, and measures the row count before and after (src/cluster_erp/modules/analytics/recipe_engine.py:195-262).

Correctness gates¶

Only eq pins a value; in does not, because two selected currencies summed are still a mixed total (src/cluster_erp/modules/analytics/query_builder.py:62-68). Two further rules hold: no round() in SQL, and monetary medians use percentile_disc so that every figure is an amount that occurred (src/cluster_erp/modules/analytics/query_builder.py:24-31).

Recipes¶

A recipe (RecipeDefinition) has one input, steps, expectations, and a limit of up to 100000 rows (src/cluster_erp/modules/analytics/recipe_schemas.py:255-283).

Each step has a stable id, a presentation seq, a note, and a disabled flag; a disabled step is skipped instead of being removed (src/cluster_erp/modules/analytics/recipe_schemas.py:47-60, src/cluster_erp/modules/analytics/recipe_engine.py:221). Step ids must be unique (src/cluster_erp/modules/analytics/recipe_schemas.py:274-281). A step names semantic keys; a key that the fact set never declared is rejected (src/cluster_erp/modules/analytics/recipe_engine.py:69-93).

Why it is built this way¶

• The query builder docstring names one promise: never produce a wrong number (src/cluster_erp/modules/analytics/query_builder.py:1-4).
• The recipe schema keeps op a closed vocabulary because it leaves no injection surface and keeps the space of next-step suggestions small (src/cluster_erp/modules/analytics/recipe_schemas.py:12-17).
• Joins are not implemented because a join without a row-multiplication gate would multiply every measure (src/cluster_erp/modules/analytics/recipe_engine.py:17-21, src/cluster_erp/modules/analytics/recipe_schemas.py:186-190).

Rules and boundaries¶

• A recipe accepts exactly one input; more than one is refused until the join gate exists (src/cluster_erp/modules/analytics/recipe_schemas.py:262-272).
• An order key must be one of the selected dimensions or measures (src/cluster_erp/modules/analytics/query_builder.py:454-463).
• The registry refuses physical names that do not match a strict identifier pattern, as defence in depth (src/cluster_erp/modules/analytics/registry.py:41-48).

Interaction with other parts¶

Limits and open points¶

• RecipeInput.as_of is stored with the recipe as the snapshot moment; a read of as_of in recipe_engine.py was not found (src/cluster_erp/modules/analytics/recipe_schemas.py:244-253).
• A result carries the generated SQL text in sql (src/cluster_erp/modules/analytics/service.py:164, src/cluster_erp/modules/analytics/query_builder.py:543-552).

Terms introduced¶

Related pages¶

• Analytics module
• Analytics facts
• Statistics and inference
• Money and decimal

Verified against commit 92f8051 on 2026-11-13.