Indirect Demand Rollup — Isolated Test Guide¶
Date: 2026-06-22
Tenant: thebicycle
Scope: Indirect demand rollup — three individually-debuggable scenarios on isolated, dedicated test items/sites
Script: files/scripts/seed_indirect_isolated.py
TOC¶
- Goal & design rationale
- Indirect demand mechanics recap
- Environment facts
- The three test scenarios
- Series reference tables
- Seeded data
- Expected rollup results
- Actual verified results
- UI validation
- How to drive the test
- Cleanup
- Design decisions (Q&A)
- Risks & guards
- Out of scope
Goal & design rationale¶
The goal is to isolate three indirect-demand rollup situations so each can
be reviewed and debugged individually, without the noise of the real 690
transfer routes, 81 000+ demand_actuals rows, or the existing 10 BOM edges.
Unlike the netting test (which reuses existing items/sites because netting is
pipeline-scoped), indirect demand reads the global master.demand_actuals,
master.route, and master.bill_of_material tables — only the optional
causal-forecast seed path is pipeline-scoped. Reusing existing items/sites
would therefore let their pre-existing demand, routes and BOM edges pollute the
arithmetic. The test instead creates brand-new items and locations in a
reserved id range (9100xxx) so the test sub-graph is fully isolated: no real
route or BOM edge references any 9100xxx item or site.
Three dedicated test items, one dedicated test pipeline, one situation per item:
| # | Situation | Item (xuid) | What drives the rollup |
|---|---|---|---|
| 1 | network single-level rollup with a yield factor | INDTNET1 |
Transfer route with repair_yield = 0.8 |
| 2 | network multi-level rollup with aggregation | INDTNET2 |
2 retail leaves → 1 country WH → 1 central WH |
| 3 | kit/make multi-level BOM explosion | INDTKITP |
2-level BOM (attach_rate + scrap) |
All direct demand is seeded on aligned Monday weeks so the rollup arithmetic is trivial to verify by hand.
Indirect demand mechanics recap¶
Indirect demand lives in files/indirect/indirect_demand.py. The entry point is
run_and_store(conn, schema, pipeline_id), which computes two streams and
writes them to ClickHouse PIPE_indirect_demand.
The two streams¶
1. Network indirect demand (demand_type = 'network') — compute_network_indirect()
- Finds Transfer route types (master.route_type where planning_type/name
contains TRANSFER).
- For each destination (item, site) that has a Transfer route with a source,
picks the best-priority source (DISTINCT ON ... ORDER BY priority ASC).
- Loads all master.demand_actuals for those destination items (global,
no is_forecast / date filter), plus — when pipeline_id is supplied —
causal forecast rows from PIPE_forecast_point_values WHERE method='causal'.
- Multi-level iterative propagation: pushes each level's demand upstream
through the route (contribution = qty × repair_yield), accumulating at every
source node, then repeats with the accumulated level until no further upstream
sources exist or MAX_DEPTH = 10 passes. This is how a central warehouse sees
the total downstream demand (sum across all country warehouses, which
themselves carry the sum across all retail stores they supply).
2. Kit/Make indirect demand (demand_type = 'kit_make') — compute_kit_make_indirect()
- A WITH RECURSIVE CTE walks master.bill_of_material from every parent →
child, multiplying the cumulative ratio along the path:
cum_ratio = Π (child_qty / item_qty × attach_rate × (1 − scrap)).
- Joins master.demand_actuals on da.item_id = root_item_id with no site
filter (parent demand is aggregated across all sites) and groups by
(child_item, child_site, date).
- qty = SUM(da.qty × cum_ratio).
Persist (run_and_store)¶
- Supersession filter: items in
scenario.item_supersessionwith a global edge (site_id IS NULL) are dropped from both streams (no propagation for replaced items). - All rows are written to
PIPE_indirect_demandwithpipeline_id,scenario_id = 0(indirect demand is not scenario-scoped), andarchive_date = planning_monday(pipeline_id). The table is keyed byitem_id+site_id(nounique_id, no built-in name columns);source_item_id/source_site_idrecord the downstream (network) or parent (BOM) node whose direct demand generated each row. Rows are kept per-source — not aggregated across sources. drop_ch_partition("PIPE_indirect_demand", pipeline_id, 0)clears the prior partition before insert (auto-padsarchive_dateviaplanning_monday()).
Lookup keys¶
All URL uids are numeric (item_id_site_id, e.g. 9100101_9100102).
This is the format used everywhere in the UI, the /series API, the forecast
tables, and PIPE_forecast_point_values.
PIPE_indirect_demand stores no unique_id — it is keyed by item_id +
site_id. The API's get_indirect_demand endpoint (main.py) resolves the
URL uid to (item_id, site_id) (via demand_actuals, then numeric parse,
then master.item/master.location xuid lookup) and queries by those ids, so
users always navigate by numeric uid. The get_series_detail endpoint
resolves both formats via id = %s::integer OR xuid = %s, and the frontend
auto-redirects xuid-format URLs to their numeric equivalent.
Environment facts¶
| Item | Value |
|---|---|
| Tenant DB | thebicycle, schema scenario |
| ClickHouse DB | thebicycle |
planning_today (global, pipeline_id=-1) |
2026-06-18 (Thu) |
planning_monday (= archive_date) |
2026-06-15 |
| Transfer route type | id = 2 (name='Transfer', planning_type='TRANSFER') |
| Real transfer routes with source | 690 |
| Real BOM edges | 10 (parents 4/20/21/22/23 @ site 100) |
master.item_type / master.site_type |
empty → items/locations created with type_id = NULL |
| Demand type ids | 1 Direct, 106 Network indirect, 107 Kit/Make indirect |
| Reserved test id range | items 9100101-9100105, sites 9100101-9100107, routes/bom 9100001-9100004 |
Week alignment — unlike netting, indirect demand has no cutoff/week-derivation
logic: the demand date passes through unchanged to the upstream/child row. All
seeded demand is nonetheless placed on Mondays for readability and to match the
netting test convention. The script asserts every seeded date's weekday == Monday.
The three test scenarios¶
NET-1 INDTNET1 — network single-level rollup with yield¶
- Leaf
INDTNET1 @ INDR1(item 9100101 @ site 9100101): direct demand 10 units on2026-07-06,2026-07-13,2026-07-20. - Transfer route:
(9100101, 9100101) → (9100101, 9100102)withrepair_yield = 0.8, priority 1. - Expected source indirect demand at
9100101_9100102= 8.0 on each of the three dates.
NET-2 INDTNET2 — network multi-level rollup with aggregation¶
- Leaf A
INDTNET2 @ INDR2A(item 9100102 @ site 9100103): 10 units @2026-07-06. - Leaf B
INDTNET2 @ INDR2B(item 9100102 @ site 9100106): 10 units @2026-07-06. - Route A:
(9100102, 9100103) → (9100102, 9100104), yield 1.0. - Route B:
(9100102, 9100106) → (9100102, 9100104), yield 1.0. - Route C:
(9100102, 9100104) → (9100102, 9100105), yield 1.0. - Expected: country
9100102_9100104= 20.0 @ 07-06 (aggregation of 2 leaves); central9100102_9100105= 20.0 @ 07-06 (country's accumulated demand propagated one level further).
KIT-1 INDTKITP — kit/make multi-level BOM explosion¶
- Parent
INDTKITP @ INDKIT(item 9100103 @ site 9100107): direct demand 10 units @2026-07-06,2026-07-13. - BOM edge 1:
9100103 → 9100104—child_qty=2,item_qty=1,attach_rate=1.0,scrap=0→ level-1 ratio = 2.0. - BOM edge 2:
9100104 → 9100105—child_qty=1,item_qty=1,attach_rate=0.5,scrap=0.1→ level-2 ratio =(1/1)×0.5×(1−0.1)= 0.45, cumulative =2.0 × 0.45= 0.9. - Expected: child1
9100104_9100107=10 × 2.0= 20.0 @ each date; child29100105_9100107=10 × 0.9= 9.0 @ each date.
Series reference tables¶
All URL uids are numeric (item_id_site_id), e.g.
http://mirabelle.ddns.net/series/9100103_9100107. The tables below map
every test series to its item/location (numeric IDs + human-readable names)
so you can navigate directly to any series in the UI.
Direct demand series (leaf/parent — generate the rollup)¶
These 4 series have 52 weeks of historical demand_actuals (10 units/week) + future firm demand on specific July weeks. They are the origin of the indirect-demand rollup — their demand propagates upstream through Transfer routes (network) or down through BOM edges (kit/make).
| Case | URL uid | Item ID | Item xuid | Item name | Location ID | Location xuid | Location name | Demand |
|---|---|---|---|---|---|---|---|---|
| NET-1 leaf | 9100101_9100101 |
9100101 | INDTNET1 | Indirect Test Net1 Item | 9100101 | INDR1 | Indirect Test Retail1 | 10/wk @ 07-06,13,20 |
| NET-2 leaf A | 9100102_9100103 |
9100102 | INDTNET2 | Indirect Test Net2 Item | 9100103 | INDR2A | Indirect Test Retail2A | 10 @ 07-06 |
| NET-2 leaf B | 9100102_9100106 |
9100102 | INDTNET2 | Indirect Test Net2 Item | 9100106 | INDR2B | Indirect Test Retail2B | 10 @ 07-06 |
| KIT parent | 9100103_9100107 |
9100103 | INDTKITP | Indirect Test Kit Parent | 9100107 | INDKIT | Indirect Test Kit Site | 10 @ 07-06,13 |
Indirect demand series (source/child — receive the rollup)¶
These 5 series have no direct demand — their PIPE_indirect_demand rows are
100% derived from the leaf/parent series above. They carry a flat-0 stat
forecast so the forecast table renders columns and the expandable "Indirect
Demand" section is visible in the UI.
Navigate to these series using their numeric URL uid. The PIPE_indirect_demand
table is keyed by item_id + site_id (no unique_id); source_item_id /
source_site_id (shown in the "Source" column) trace the downstream/parent
node whose demand generated each row. The API resolves the numeric URL uid to
(item_id, site_id) when querying, so you always use the numeric uid in the URL.
| Case | URL uid | Item ID | Item xuid | Item name | Location ID | Location xuid | Location name | Source (item/site) | Expected qty | demand_type |
|---|---|---|---|---|---|---|---|---|---|---|
| NET-1 source | 9100101_9100102 |
9100101 | INDTNET1 | Indirect Test Net1 Item | 9100102 | INDC1 | Indirect Test Country1 | 9100101/9100101 (leaf) | 8.0 × 3 wks | network |
| NET-2 country (leaf A) | 9100102_9100104 |
9100102 | INDTNET2 | Indirect Test Net2 Item | 9100104 | INDC2 | Indirect Test Country2 | 9100102/9100103 (leaf A) | 10.0 × 1 wk | network |
| NET-2 country (leaf B) | 9100102_9100104 |
9100102 | INDTNET2 | Indirect Test Net2 Item | 9100104 | INDC2 | Indirect Test Country2 | 9100102/9100106 (leaf B) | 10.0 × 1 wk | network |
| NET-2 central | 9100102_9100105 |
9100102 | INDTNET2 | Indirect Test Net2 Item | 9100105 | INDCEN2 | Indirect Test Central2 | 9100102/9100104 (country) | 20.0 × 1 wk | network |
| KIT child1 | 9100104_9100107 |
9100104 | INDTKITC1 | Indirect Test Kit Child1 | 9100107 | INDKIT | Indirect Test Kit Site | 9100103/9100107 (parent) | 20.0 × 2 wks | kit_make |
| KIT child2 | 9100105_9100107 |
9100105 | INDTKITC2 | Indirect Test Kit Child2 | 9100107 | INDKIT | Indirect Test Kit Site | 9100103/9100107 (parent) | 9.0 × 2 wks | kit_make |
Routes & BOM edges driving each rollup¶
Each route/BOM edge links a "from" series (has demand) to a "to" series (receives indirect demand). The yield (network) or ratio (kit/make) determines how much demand propagates.
| Case | Route/BOM | From uid | To uid | Yield/Ratio |
|---|---|---|---|---|
| NET-1 | route 9100001 | 9100101_9100101 |
9100101_9100102 |
repair_yield=0.8 |
| NET-2 leaf A | route 9100002 | 9100102_9100103 |
9100102_9100104 |
1.0 |
| NET-2 leaf B | route 9100003 | 9100102_9100106 |
9100102_9100104 |
1.0 |
| NET-2 country→central | route 9100004 | 9100102_9100104 |
9100102_9100105 |
1.0 |
| KIT parent→child1 | BOM 9100001 | 9100103_9100107 |
9100104_9100107 |
child_qty=2 → ratio 2.0 |
| KIT child1→child2 | BOM 9100002 | 9100104_9100107 |
9100105_9100107 |
attach=0.5, scrap=0.1 → ratio 0.45 |
Keys in PIPE_indirect_demand¶
PIPE_indirect_demand stores no unique_id — it is keyed by item_id +
site_id. source_item_id / source_site_id record the downstream (network)
or parent (BOM) node whose direct demand generated each row, and rows are kept
per-source (not aggregated across sources).
This is transparent to users: the API's get_indirect_demand endpoint resolves
the URL's numeric uid to (item_id, site_id) before querying
PIPE_indirect_demand, and the get_series_detail endpoint resolves both
formats via id = %s::integer OR xuid = %s. The frontend auto-redirects any
xuid-format URL to its numeric equivalent. Always use numeric uids
(item_id_site_id) in URLs.
Seeded data¶
The script files/scripts/seed_indirect_isolated.py creates, per run:
PostgreSQL:
- config_scenario_pipeline — 1 row ("Indirect Test Lab", non_prod, maintenance_netting_mode='net').
- scen_forecast_scenarios — 1 row ("Indirect Test Maint", used as maintenance scenario).
- master.item — 5 rows (9100101-9100105, xuid INDT*).
- master.location — 7 rows (9100101-9100107, xuid IND*).
- master.demand_actuals — 215 rows: 208 historical (52 weeks × 4 leaf/parent) + 7 future firm-demand rows (is_forecast=false, demand_type_id=1, source='indirect_test_lab').
- master.route — 4 Transfer routes (9100001-9100004, type_id=2).
- master.bill_of_material — 2 BOM edges (9100001-9100002).
- master.maintenance_event — 1 row ("Indirect Test Maint Event").
- master.maintenance_bom — 1 row (event → item 9100103, qty=1, lor=1.0).
- scen_maintenance_scenario_event — 1 row.
- scen_maintenance_scenario_event_date — 2 rows (35 @ 07-06, 25 @ 07-27).
- config_forecast_parameter_set — 1 row ("Indirect Test Default", netting config).
ClickHouse (all with archive_date = planning_monday, scenario_id per stream):
- PIPE_series_characteristics — 9 rows (4 leaf/parent with mean=10, 5 upstream with mean=0).
- PIPE_series_best_methods — 9 rows (best_method='IndTestConst').
- PIPE_forecast_point_values — 468 rows (52 weeks × 9 series; leaf/parent = 10/wk, upstream = 0/wk).
- PIPE_maintenance_forecast — 2 rows (35.0 @ 07-06, 25.0 @ 07-27 for the KIT parent).
- PIPE_indirect_demand — produced by the indirect run. Rows are keyed by
item_id + site_id (no unique_id), with source_item_id /
source_site_id tracing the downstream/parent demand source; the run also
emits the real-catalogue rows (network + kit_make) because the engine reads
global tables — see Risks & guards.
- PIPE_forecast_netting — 468 rows (52 weeks × 9 series), produced by the netting run.
unique_id format: All series use numeric item_id_site_id (e.g.
9100103_9100107) in the UI, the /series API, the forecast tables, and
PIPE_forecast_point_values. PIPE_indirect_demand carries no unique_id
(it is keyed by item_id + site_id); the API resolves the numeric uid to
(item_id, site_id) when querying, so users always navigate by numeric uid.
All artifacts are tagged for cleanup: pipeline name 'Indirect Test Lab',
demand source='indirect_test_lab', xuid prefixes INDT*/IND*, and id
ranges 9100001-9100004 (routes/bom) and 9100101-9100107 (items/sites).
Expected rollup results¶
NET-1 9100101_9100102 (leaf 10 @ 07-06/13/20, yield 0.8)¶
| date | leaf qty | yield | source indirect |
|---|---|---|---|
| 2026-07-06 | 10 | 0.8 | 8.0 |
| 2026-07-13 | 10 | 0.8 | 8.0 |
| 2026-07-20 | 10 | 0.8 | 8.0 |
NET-2 9100102_9100104 (country) + 9100102_9100105 (central)¶
| date | leaf A | leaf B | country (sum) | central (via country) |
|---|---|---|---|---|
| 2026-07-06 | 10 | 10 | 20.0 | 20.0 |
KIT-1 9100104_9100107 + 9100105_9100107 (parent 10 @ 07-06/13)¶
| date | parent qty | child1 (×2.0) | child2 (×0.9) |
|---|---|---|---|
| 2026-07-06 | 10 | 20.0 | 9.0 |
| 2026-07-13 | 10 | 20.0 | 9.0 |
Actual verified results¶
Running python scripts/seed_indirect_isolated.py (test pipeline_id=93)
produced PIPE_indirect_demand rows matching the expected tables exactly.
Key verification output (rows are keyed by item_id/site_id with
source_item_id/source_site_id tracing the demand origin):
── NET-1 single-level (yield 0.8): expect 8.0 @ 07-06,07-13,07-20 item=9100101/9100102 source=9100101/9100101 ──
date | qty | demand_type | src_item | src_site
2026-07-06 | 8.00 | network | 9100101 | 9100101
2026-07-13 | 8.00 | network | 9100101 | 9100101
2026-07-20 | 8.00 | network | 9100101 | 9100101
── NET-2 country (leaf A): expect 10.0 @ 07-06 item=9100102/9100104 source=9100102/9100103 ──
date | qty | demand_type | src_item | src_site
2026-07-06 | 10.00 | network | 9100102 | 9100103
── NET-2 country (leaf B): expect 10.0 @ 07-06 item=9100102/9100104 source=9100102/9100106 ──
date | qty | demand_type | src_item | src_site
2026-07-06 | 10.00 | network | 9100102 | 9100106
── NET-2 central (multi-level): expect 20.0 @ 07-06 item=9100102/9100105 source=9100102/9100104 ──
date | qty | demand_type | src_item | src_site
2026-07-06 | 20.00 | network | 9100102 | 9100104
── KIT child1 (ratio 2.0): expect 20.0 @ 07-06,07-13 item=9100104/9100107 source=9100103/9100107 ──
date | qty | demand_type | src_item | src_site
2026-07-06 | 20.00 | kit_make | 9100103 | 9100107
2026-07-13 | 20.00 | kit_make | 9100103 | 9100107
── KIT child2 (cum_ratio 0.9): expect 9.0 @ 07-06,07-13 item=9100105/9100107 source=9100103/9100107 ──
date | qty | demand_type | src_item | src_site
2026-07-06 | 9.00 | kit_make | 9100103 | 9100107
2026-07-13 | 9.00 | kit_make | 9100103 | 9100107
Run totals: network=27083 kit_make=1302 — the large counts are the real
catalogue (see Risks & guards); the 10 test rows above are
the isolated sub-graph.
Manual verification query¶
-- ClickHouse (thebicycle)
-- PIPE_indirect_demand is keyed by item_id + site_id (no unique_id);
-- source_item_id / source_site_id trace the downstream/parent demand source.
-- Test items use ids 9100101-9100105 and sites 9100101-9100107.
SELECT item_id, site_id, source_item_id, source_site_id, demand_type, date, qty
FROM PIPE_indirect_demand
WHERE pipeline_id = 93 -- replace with your test pipeline_id
AND item_id BETWEEN 9100101 AND 9100105
ORDER BY item_id, site_id, source_item_id, demand_type, date;
UI validation¶
The forecast tab is the primary UI surface for validating indirect demand. The
expandable "Indirect Demand" section (foldable rows inside
ForecastTableWithAdjustments, TimeSeriesViewer.jsx) renders when the
forecast table has columns (monthDates), which requires a stat forecast in
PIPE_forecast_point_values — even a flat-0 one. The section auto-expands
and shows two sub-rows: Network / Transfer (sky) and BOM / Kit-Make
(violet), with weekly values aligned to the forecast table columns. Each
cell value is clickable (when > 0) and opens the
Indirect-demand drilldown modal.
What to look for¶
| Series (numeric URL uid) | Forecast tab | What you see |
|---|---|---|
9100101_9100102 (NET-1 source) |
flat-0 stat + Network: 8.0/wk | Indirect demand section shows 8.0 every week (10 × yield 0.8) |
9100102_9100104 (NET-2 country) |
flat-0 stat + Network: 20.0/wk | Aggregation of 2 leaves (10+10) |
9100102_9100105 (NET-2 central) |
flat-0 stat + Network: 20.0/wk | Multi-level: country's 20.0 propagated up |
9100104_9100107 (KIT child1) |
flat-0 stat + BOM/Kit: 20.0/wk | Parent 10 × ratio 2.0 |
9100105_9100107 (KIT child2) |
flat-0 stat + BOM/Kit: 9.0/wk | Parent 10 × 2.0 × 0.45 (attach 0.5, scrap 0.1) |
9100101_9100101 (NET-1 leaf) |
52wk history + 10/wk stat | Historical chart + flat forecast line |
9100103_9100107 (KIT parent) |
52wk history + 10/wk stat | Historical chart + flat forecast line |
Netting tab (KIT parent with maintenance)¶
Open http://localhost:5173/series/9100103_9100107?tab=netting:
| week | stat | maint | stat_cons | stat_net | supply |
|---|---|---|---|---|---|
| 07-06 | 10.0 | 35.0 | 10.0 | 0.0 | 45.0 |
| 07-13 | 10.0 | 0.0 | 10.0 | 0.0 | 10.0 |
| 07-27 | 10.0 | 25.0 | 10.0 | 0.0 | 25.0 |
Maintenance (net mode) consumes the stat forecast: maint_consumed = maint_f
(fully consumed), stat_consumed reduces stat_netted to 0 in fence weeks.
supply_plan = stat_netted + maint_f in net mode.
Why source/child series have no historical chart¶
The forecast tab's historical line comes from master.demand_actuals WHERE
unique_id = ?. Upstream/child nodes (country warehouses, BOM children) have
zero direct demand — that's the whole point of indirect demand. Their
demand is 100% derived. The chart is intentionally empty for these series; the
indirect demand section is the data they carry.
Indirect-demand drilldown modal (new)¶
Clicking any Network / Transfer (sky) or BOM / Kit-Make (violet) cell
value in the forecast-tab grid opens an IndirectDrilldownModal — the same
pattern as the existing Causal (green) and Maintenance (amber) drilldowns.
The modal shows, for the clicked date, every contributing source node
whose demand propagated into this series, with the contribution formula and
names.
Pipeline: the modal runs under pipeline 99 ("Indirect Test Lab"). The
pipeline_id is required by the endpoint (require_pipeline_id) because the
network stream's causal-forecast seed path is pipeline-scoped.
Endpoint: GET /api/series/{unique_id}/indirect/breakdown?period=YYYY-MM-DD&pipeline_id=99
(files/api/main.py, after /api/indirect/compute). Returns {network: {actual_demand, total, contributors[]}, kit_make: {actual_demand, total, contributors[]}}.
Schema dispatch: the breakdown auto-detects the live ClickHouse schema
via ch_has_column("PIPE_indirect_demand", "source_item_id"):
- v5 schema (has source_item_id): the stored per-source rows ARE the
breakdown — one CH SELECT … GROUP BY source_item_id, source_site_id.
- Legacy schema (no source_item_id — current state): re-traces the
full multi-level propagation in Python, mirroring compute_network_indirect
/ the recursive BOM CTE, so contributor totals match the stored aggregate.
Actual demand (mismatch warning): the endpoint also queries the stored
PIPE_indirect_demand aggregate (filtered by item_id + site_id + date
+ pipeline_id=99) for the modal's "Pipeline forecast" KPI cards. When
|actual − total| / max(actual, total) > 5%, an amber ⚠ mismatch banner
appears. No mismatch is expected for the isolated test series.
NET-1 — 9100101_9100102 (Country1, network, 2026-07-06)¶
Clicked series = the Transfer-route source (destination of the rollup).
| Modal field | Value |
|---|---|
| Pipeline forecast (actual) | 8.0 |
| Network total | 8.0 (1 contributor) |
| Kit/Make total | 0.0 (no contributors) |
Contributor (the downstream leaf whose demand propagated up through the route):
| Contributor uid | Item (id / name / xuid) | Site (id / name / xuid) | qty_pushed | yield | contribution |
|---|---|---|---|---|---|
9100101_9100101 |
9100101 / Indirect Test Net1 Item / INDTNET1 | 9100101 / Indirect Test Retail1 / INDR1 | 10.0 | 0.8 | 8.0 |
Source → destination: route 9100001 — source 9100101_9100102
(Indirect Test Net1 Item @ Indirect Test Country1) ← destination
9100101_9100101 (Indirect Test Net1 Item @ Indirect Test Retail1). The
leaf's 10 units of direct demand × yield 0.8 = 8.0 at the source.
qty_pushed = the destination node's direct demand (10.0); contribution =
qty_pushed × yield = 8.0.
NET-2 country — 9100102_9100104 (Country2, network, 2026-07-06)¶
Clicked series = the Transfer-route source that aggregates two leaves.
| Modal field | Value |
|---|---|
| Pipeline forecast (actual) | 20.0 |
| Network total | 20.0 (2 contributors) |
| Kit/Make total | 0.0 |
Contributors (the two retail leaves, each with its own route into Country2):
| Contributor uid | Item (id / name / xuid) | Site (id / name / xuid) | qty_pushed | yield | contribution |
|---|---|---|---|---|---|
9100102_9100103 |
9100102 / Indirect Test Net2 Item / INDTNET2 | 9100103 / Indirect Test Retail2A / INDR2A | 10.0 | 1.0 | 10.0 |
9100102_9100106 |
9100102 / Indirect Test Net2 Item / INDTNET2 | 9100106 / Indirect Test Retail2B / INDR2B | 10.0 | 1.0 | 10.0 |
Source → destination: routes 9100002 + 9100003 — source
9100102_9100104 (Country2) ← destinations 9100102_9100103 (Retail2A)
and 9100102_9100106 (Retail2B). Each leaf's 10 × yield 1.0 = 10.0;
Σ contributions = 20.0 at the country source. This is the aggregation
case — two predecessor routes feed one source node.
NET-2 central — 9100102_9100105 (Central2, network, 2026-07-06)¶
Clicked series = the multi-level source (central warehouse). Its single predecessor is the country warehouse, whose own demand (20.0) already aggregated the two retail leaves.
| Modal field | Value |
|---|---|
| Pipeline forecast (actual) | 20.0 |
| Network total | 20.0 (1 contributor) |
| Kit/Make total | 0.0 |
Contributor (the country warehouse — an intermediate node, not a leaf):
| Contributor uid | Item (id / name / xuid) | Site (id / name / xuid) | qty_pushed | yield | contribution |
|---|---|---|---|---|---|
INDTNET2_INDC2 |
9100102 / Indirect Test Net2 Item / INDTNET2 | 9100104 / Indirect Test Country2 / INDC2 | 20.0 | 1.0 | 20.0 |
Source → destination: route 9100004 — source 9100102_9100105
(Central2) ← destination 9100102_9100104 (Country2). The country
node's qty_pushed = 20.0 (its own direct demand 0 + accumulated
contributions from the two leaves below it). × yield 1.0 = 20.0 at the
central source. This is the multi-level case.
uid format note: the contributor's
unique_idisINDTNET2_INDC2(xuid-based), not9100102_9100104(numeric), because the country warehouse has nodemand_actualsrows._build_uid_map(legacy-schema fallback only) resolves uids for nodes without demand_actuals by falling back toCOALESCE(item.xuid, item.name, item.id) || '_' || COALESCE(site.xuid, site.name, site.id). Clicking it in the modal opens/series/INDTNET2_INDC2?tab=forecast, which the frontend auto-redirects to the numeric uid/series/9100102_9100104. Under the v5 schema (per-source CH read), the contributor uid is always numericitem_id_site_id.
KIT child1 — 9100104_9100107 (Kit Child1, kit_make, 2026-07-06)¶
Clicked series = the BOM child that receives exploded parent demand.
| Modal field | Value |
|---|---|
| Network total | 0.0 (no contributors) |
| Pipeline forecast (actual) | 20.0 |
| Kit/Make total | 20.0 (1 contributor) |
Contributor (the root parent whose direct demand drove the BOM explosion):
| Contributor | Parent item (id / name / xuid) | contribution | cum_ratio |
|---|---|---|---|
| root parent | 9100103 / Indirect Test Kit Parent / INDTKITP | 20.0 | 2.0 |
Parent → child: BOM edge 9100001 — parent 9100103_9100107 (Kit
Parent @ Kit Site) → child 9100104_9100107 (Kit Child1 @ Kit Site).
cum_ratio = (child_qty 2 / item_qty 1) × attach 1.0 × (1 − scrap 0) =
2.0. Parent demand 10 × cum_ratio 2.0 = 20.0 at the child.
KIT child2 — 9100105_9100107 (Kit Child2, kit_make, 2026-07-06)¶
Clicked series = the 2-level-deep BOM child.
| Modal field | Value |
|---|---|
| Network total | 0.0 (no contributors) |
| Pipeline forecast (actual) | 9.0 |
| Kit/Make total | 9.0 (1 contributor) |
Contributor:
| Contributor | Parent item (id / name / xuid) | contribution | cum_ratio |
|---|---|---|---|
| root parent | 9100103 / Indirect Test Kit Parent / INDTKITP | 9.0 | 0.9 |
Parent → child (2 levels): BOM edge 9100001 (parent → child1, ratio 2.0)
then BOM edge 9100002 (child1 → child2, ratio (1/1) × 0.5 × (1−0.1) =
0.45). cum_ratio = 2.0 × 0.45 = 0.9. Parent demand 10 × cum_ratio 0.9 =
9.0 at child2. The recursive BOM CTE walks both levels; the modal shows the
root parent (Kit Parent) with the full cumulative ratio.
Summary of all modal results¶
| Clicked series (numeric uid) | Item / Location | demand_type | actual | total | # contributors | Top contributor |
|---|---|---|---|---|---|---|
9100101_9100102 |
Net1 Item @ Country1 | network | 8.0 | 8.0 | 1 | Retail1 (10 × 0.8) |
9100102_9100104 |
Net2 Item @ Country2 | network | 20.0 | 20.0 | 2 | Retail2A + Retail2B (10+10) |
9100102_9100105 |
Net2 Item @ Central2 | network | 20.0 | 20.0 | 1 | Country2 (20 × 1.0) |
9100104_9100107 |
Kit Child1 @ Kit Site | kit_make | 20.0 | 20.0 | 1 | Kit Parent (10 × 2.0) |
9100105_9100107 |
Kit Child2 @ Kit Site | kit_make | 9.0 | 9.0 | 1 | Kit Parent (10 × 0.9) |
All actual (stored PIPE_indirect_demand) values match the total (Σ
contributions) — no mismatch warning expected. Direct-demand leaf/parent
series (9100101_9100101, 9100102_9100103, 9100102_9100106,
9100103_9100107) have no indirect demand rows; clicking is disabled
(their cells are 0 / —).
Key files¶
| File | Role |
|---|---|
files/indirect/indirect_demand.py |
network_breakdown(), kit_make_breakdown() — schema-dispatch (v5 CH read vs legacy Python re-trace); _build_uid_map, _load_network_routes, _resolve_names helpers |
files/api/main.py |
GET /api/series/{unique_id}/indirect/breakdown — resolves uid → ids, requires pipeline_id, calls both breakdowns, returns stored actual_demand per stream |
files/frontend/src/components/IndirectDrilldownModal.jsx |
2-tab modal (Network sky + Kit/Make violet) — KPI cards, mismatch warning, contributor tables, click-through to source series |
files/frontend/src/components/TimeSeriesViewer.jsx |
openIndirectDrill + fetchDrillData indirect branch; click handlers on Network + Kit-Make grid rows; modal render |
files/frontend/src/components/drilldown/FormulaCard.jsx |
Added sky + violet color palettes |
All commands run from the files/ directory.
Full seed + run + verify¶
Creates a fresh test pipeline, seeds all data, runsrun_and_store, and prints
a verification table comparing actual vs expected results.
Seed only (no indirect run)¶
Useful when you want to inspect the raw seeded routes/BOM/demand before the rollup consumes it.Clean up all test artifacts¶
Removes every tagged artifact from PostgreSQL and ClickHouse.Idempotency¶
The script is fully idempotent — re-running cleans the previous test pipeline and all 9100xxx master rows before seeding new ones. Verified across multiple consecutive runs (pipeline_ids 94 → 95 → 96 → 97) with no errors.
Run via the API (not recommended for this isolated test)¶
POST /api/indirect/compute calls run_and_store(conn, schema) without a
pipeline_id, which run_and_store rejects (ValueError: pipeline_id is
required). The pipeline runner (run_pipeline.py step indirect-demand) is
the canonical caller and always passes pipeline_id. The seed script calls
run_and_store(conn, schema, pipeline_id=pid) directly to stay isolated.
Cleanup¶
The --clean flag (and the start of every full run) removes all artifacts:
master.demand_actuals WHERE source = 'indirect_test_lab'(215 rows: history + future)master.bill_of_material WHERE id IN (9100001, 9100002)(supply-planning BOM)master.maintenance_bomfor the test event (no ON DELETE cascade)master.maintenance_event WHERE name = 'Indirect Test Maint Event'(cascades toscen_maintenance_*)master.route WHERE id IN (9100001..9100004)master.item WHERE id IN (9100101..9100105)master.location WHERE id IN (9100101..9100107)config_forecast_parameter_set WHERE name = 'Indirect Test Default'config_scenario_pipeline WHERE name = 'Indirect Test Lab'(before scen_forecast_scenarios — FK is NO ACTION)scen_forecast_scenarios WHERE name = 'Indirect Test Maint'- ClickHouse
ALTER TABLE ... DELETE WHERE pipeline_id = <old>on all six PIPE_ tables.
There are no FK ordering hazards: master.route / master.bill_of_material /
master.demand_actuals have no FKs back to the test pipeline, and the test
items/locations are only referenced by the rows deleted in steps 1-5 (deleted
before the master rows in steps 6-7). The config_scenario_pipeline row is
deleted before scen_forecast_scenarios because its maintenance_scenario_id
FK is NO ACTION.
Design decisions (Q&A)¶
| Question | Decision | Rationale |
|---|---|---|
| Reuse existing items/sites (like the netting test)? | No — dedicated 9100xxx items/sites | Network + kit streams read global demand_actuals / routes / BOM. Reusing items 13/18/19 (which already have 690 routes + 81k demand rows) would make the arithmetic unverifiable. |
| Why not pipeline-isolate? | Cannot | Only the causal-forecast seed path is pipeline-scoped; the core streams are inherently global. Isolation is achieved by a disjoint id range instead. |
Why set xuid on items/locations? |
Readable master rows | xuid/name make the seeded master.item / master.location rows human-readable. PIPE_indirect_demand itself stores no unique_id (keyed by item_id + site_id), so xuid no longer affects stored rows — it only labels the master data. |
Why numeric item_id_site_id for URLs and stat forecast? |
Single consistent format | The UI, /series API, forecast tables, and PIPE_forecast_point_values all use numeric item_id_site_id. The maintenance BOM query in forecast_netting.py also builds uid as item_id::text || '_' || site_id::text. Using numeric everywhere ensures the netting engine can match maintenance to the stat forecast series. |
| Why 0-value stat forecast for upstream/child? | UI visibility | The forecast table's monthDates requires a bestFc (stat forecast). Without it, the table has no columns and the expandable Indirect Demand section is hidden. A flat-0 forecast is legitimate — upstream/child nodes have no direct demand. |
| Why 52 weeks of historical demand? | Historical chart | The forecast tab's chart reads master.demand_actuals for the historical line. Without past demand, the chart is empty and the user can't validate the forecast visually. |
| Why maintenance on the KIT parent? | Netting tab completeness | Shows maint_forecast_qty consuming the stat forecast in net mode. Also seeds PIPE_maintenance_forecast so the forecast tab's baseline endpoint can read it. |
| Yield 0.8 in NET-1? | Exercises repair_yield |
A yield ≠ 1 proves the multiplication qty × repair_yield is applied, not just a 1:1 copy. |
| 2 leaves in NET-2? | Exercises aggregation | A single leaf would only prove propagation; two leaves prove the source node sums contributions before propagating further upstream. |
| Multi-level BOM with attach + scrap? | Exercises the cumulative ratio | child2 = 10 × 2.0 × 0.45 = 9.0 tests the recursive product of (child_qty/item_qty) × attach_rate × (1−scrap) across two levels. |
Risks & guards¶
- Indirect demand is NOT pipeline-isolated —
run_and_storefor the test pipeline also computes and stores indirect demand for the entire real catalogue (network ≈ 27 000 rows, kit_make ≈ 1 400 rows). This is expected and harmless: verification filters byitem_id BETWEEN 9100101 AND 9100105(the test sub-graph). The 9100xxx sub-graph is disjoint from every real route and BOM edge, so no real demand flows into the test source/child nodes and vice-versa. PIPE_indirect_demandis keyed byitem_id+site_id— the table carries nounique_idand no built-in name columns.source_item_id/source_site_idtrace the downstream (network) or parent (BOM) node whose direct demand generated each row, and rows are kept per-source. The API'sget_indirect_demandresolves the numeric URL uid to(item_id, site_id)when querying, andget_series_detailresolves both viaid = %s::integer OR xuid = %s. The frontend auto-redirects xuid-format URLs to numeric. Always use numeric uids (item_id_site_id) in URLs.- Reserved id range — items 9100101-9100105, sites 9100101-9100107, routes/bom 9100001-9100004 must stay reserved. The script asserts these ids are free at seed time (cleanup deletes them first). If a future importer ever allocates these ids, switch to a higher range (e.g. 9900xxx).
master.item_type/master.site_typeare empty in this tenant; test items/locations are inserted withtype_id = NULL(FK allows it).fetch_master_names()LEFT-joins these tables, so missing types yield empty*_type_name— harmless.repair_yieldcolumn — added byfiles/DDL/supply_schema.sqlmigration. The network stream usesCOALESCE(r.repair_yield, 1.0). If the migration has not been applied,compute_network_indirectwould error; the test implicitly verifies the column exists.- BOM / route active-date filters — both the route and BOM queries filter on
end_date >= CURRENT_DATE/start_date <= CURRENT_DATE. Test rows leave both NULL (= active), so they are always picked up regardless of the real calendar date. - Supersession —
scenario.item_supersessionhas no rows for the 9100xxx items, so the supersession redirect is a no-op for the test. - Maintenance net mode with 0/0/0 blend —
maintenance_pct = 0means "no scaling" in the netting engine (weight 1.0, maintenance at full value). The forecast tab's baseline endpoint hides the Maintenance row whenmaintenance_pct = 0, but the netting tab showsmaint_forecast_qtycorrectly. This is a known UI/engine inconsistency. - Planning date — the script uses
planning_today()/planning_monday()forarchive_date, neverdate.today(), per the project rule. GET /api/series/{uid}/indirecthas NO pipeline_id filter — it returns indirect demand across all pipelines. Since the test items (9100xxx) are unique, only the test pipeline's rows match. No cross-pipeline contamination.
Out of scope¶
- Causal-forecast seed path (
_load_causal_forecast_rows) — the only pipeline-scoped input to the network stream. Not seeded; all three scenarios drive the rollup viamaster.demand_actuals. Can be added later by seedingPIPE_forecast_point_valuesrows withmethod='causal'for a leaf item that has no demand_actuals, then verifying the source still gets indirect demand. - Supersession redirect (
item_supersession) — not seeded; both streams pass through unmodified. /api/indirect/computeendpoint — does not passpipeline_idand raisesValueError; tested via directrun_and_storecall instead (see How to drive the test).indirect_demand_rateupdate onmaster.item— performed by the API endpoint afterrun_and_store; the seed script does not trigger it.- Forecast tab's Maintenance blend row — hidden because
maintenance_pct = 0(the netting engine interprets 0 as "no scaling" = full weight, but the UI hides the row). The netting tab shows maintenance correctly. This is a known UI/engine inconsistency, not a test gap. - Touching the real catalogue or any existing pipeline — the test is fully isolated in its own pipeline + disjoint id range.