Skip to content

Route Closure Over a Period — Isolated Test Guide

Date: 2026-06-24 Tenant: thebicycle Scope: Closing a supply route over a date window — two mechanisms (calendar closure + resource capacity closure) on the same three TRANSFER routes, plus the import-loader that makes calendar-linked routes importable. Scripts: files/supply/test_calendar_closure_setup.sql, files/supply/test_capacity_closure_setup.sql, files/supply/verify_route_closure.py

TOC

  1. Goal & design rationale
  2. The two closure mechanisms
  3. Data model — tables informed
  4. Do the import tables suffice?
  5. Environment facts
  6. The three test scenarios
  7. How to drive the test
  8. Expected results
  9. Verification
  10. Cleanup
  11. Code review findings & fixes
  12. Risks & guards
  13. Out of scope

Goal & design rationale

The goal is to demonstrate two distinct ways to close a supply route over a period of time, on the same three routes, so the behavioral difference is directly comparable:

# Mechanism Lane scope Pull-forward? Output signal
A Calendar closure single lane yes (TRANSFER_PREPOS) pre-position order, zero regular TRANSFER in window, possible shortage
B Resource capacity closure all routes sharing a resource no order caps/cuts, CapacityExceeded exceptions, used_qty saturated

Both close the same window (weeks 20-23) on the same three TRANSFER routes (France WH → the three French stores), for the same item (Chain Lube), so the only variable is the mechanism.

A third test case documents the import-loader fix that makes calendar-linked routes importable via the standard import.route staging table (the route loader was previously broken — it ingested bill-of-material SQL under the "route" name).

The two closure mechanisms

A "route" is master.route (one row per item × site × sourcing type). The supply engine loads each route into a RouteOption (supply_runner._build_route_option, supply_runner.py:2649) carrying two 52-bit week bitmasks derived from calendars: - shipping_calendar (from route.ship_calendar_id) — can the source ship this week? - receiving_calendar (from route.lead_time_calendar_id) — can the destination receive?

A week is closed when the relevant bitmask bit is 0. There is no separate "route open/closed" flag — closure is expressed either via calendars (lane-specific) or via resource capacity (lane group).

Mechanism A — Calendar closure (lane-specific, pull-forward)

  • A not_available calendar with a date range = CLOSED window; all other weeks open.
  • Assign it to master.route.ship_calendar_id (can't ship out of the source during the window) and/or lead_time_calendar_id (can't receive at the destination).
  • _load_calendars (supply_runner.py:3904) converts master.calendar_entry date ranges → a 52-bit week bitmask anchored on Monday of the current week (week 0). mode='not_available' clears bits in the listed ranges; mode='available' sets bits only in the listed ranges.
  • _apply_calendar_pull_forward (supply_runner.py:2784): when all TRANSFER routes serving a (item, site) are closed in a window, demand from the closed weeks is accumulated into the last open week before the closure → the Rust engine emits one TRANSFER_PREPOS order arriving in that anchor week. If no earlier open week exists (closure at week 0), the engine surfaces a shortage.
  • BUY/MAKE-only plans are skipped (their supply is independent of hub calendars).

Mechanism B — Resource capacity closure (group, cap/cut, no pull-forward)

  • master.route_resource (route_id, resource_id, resource_type, qty_per_unit) declares which resource each route consumes.
  • pipe_supply_capacity (scenario_id, resource_type, resource_id, week, capacity_qty) sets a per-week cap per resource.
  • Setting capacity_qty = 0 for a resource in weeks W..W+n closes every route consuming that resource during those weeks. With capacity_enforcement='soft' the check is used <= capacity_qty * soft_capacity_multiplier; 0 * 1.2 = 0 → still blocks. With 'hard' orders are split/blocked.
  • This does not trigger pull-forward pre-positioning (that logic keys only on transfer-route calendars). Instead it caps/cuts orders and raises CapacityExceeded exceptions (exceptions_runner.py:464, supply_solver/model.py:298).

Comparison

Calendar (A) Resource capacity (B)
Granularity single lane all routes sharing a resource
Pull-forward pre-position yes (TRANSFER_PREPOS) no
Output signal TRANSFER_PREPOS orders, zero regular TRANSFER in closed weeks, possible shortage order caps/cuts, CapacityExceeded exceptions, used_qty saturating
Tables to inform master.calendar, master.calendar_entry, master.route.ship_calendar_id/lead_time_calendar_id master.route_resource, pipe_supply_capacity (scenario-scoped)

Data model — tables informed

Mechanism A (calendar)

Table Schema What to set
master.calendar master one row: name, type='working', mode='not_available' (closed-window semantics) or 'available' (open-window semantics)
master.calendar_entry master one+ rows: calendar_id, date_from, date_to (the closure window). Multiple non-contiguous ranges allowed.
master.route master set ship_calendar_id (and/or lead_time_calendar_id) to the calendar id, for the route(s) to close

Mechanism B (capacity)

Table Schema What to set
master.route_resource master link each target route to a resource (resource_id, resource_type e.g. TRANSPORT/PLANT, qty_per_unit)
pipe_supply_capacity scenario rows capacity_qty=0 for that (resource_type, resource_id, week) across the closed weeks, scoped to the supply scenario_id

Output tables to observe (both)

Table Schema What to look for
PIPE_supply_orders ClickHouse order_type TRANSFER_PREPOS (A) vs capped/cut orders (B); release_week/arrival_week
PIPE_supply_exceptions ClickHouse Shortage (A when no anchor) / CapacityExceeded (B)
PIPE_supply_capacity ClickHouse used_qty vs capacity_qty per (resource, week) (B)
PIPE_supply_inventory_projection ClickHouse drawdown through the closed window

Do the import tables suffice?

No — not before this change. Three gaps existed in the import pipeline (files/etl/import_etl.py), all now fixed for Mechanism A:

Gap 1 — Route loader was broken (FIXED)

The import entry "name": "route" declared target="master.route" but its sql_truncate/sql_upsert/sql_insert/sql_count/sql_rejected were all the bill_of_material SQL. A standard import run did NOT load import.route → master.route at all (routes were seeded only via seed_supply_demo.py or the Excel cost-only adapter). Now split into a real "route" entry + a real "bill_of_material" entry.

Gap 2 — ship_calendar_name was never linked (FIXED)

import.route.ship_calendar_name (TEXT) existed, and import.calendar / import.calendar_entry ingest correctly into master.calendar / master.calendar_entry (by name). But the route loader did not JOIN ship_calendar_name → master.calendar.id to populate master.route.ship_calendar_id. Now the route upsert LEFT JOIN master.calendar c ON c.name = i.ship_calendar_name and emits c.id into ship_calendar_id. The calendar + calendar_entry import steps were moved before the route step so a full import resolves calendar links in the same pass.

Gap 3 — No import tables for Mechanism B (NOT fixed — follow-up)

There is no import.route_resource and no import.supply_capacity. So resource-capacity closure cannot be configured via import at all. This is a documented follow-up (Gap 4c in the plan); Mechanism B still requires direct SQL on master.route_resource + pipe_supply_capacity.

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 / week-0 anchor) 2026-06-15 (Mon)
Horizon 52 weeks
Item Chain Lube Finish Line 500ml (item_id = 1)
Source site France Country Warehouse (site_id = 201)
Destination sites Cycles Paris (301), Velo Lyon (302), Velo Marseille (303)
Routes 169 (→301), 197 (→302), 225 (→303), all TRANSFER, lead_time = 1 week
Default pipeline pipeline_id = 4 ("Default")
Default supply scenario scenario_id = 1 ("Baseline")
Hard-capacity supply scenario scenario_id = 7 ("Hard Capacity")
Closed window weeks 20-23 (= Mon 2026-10-19 → Sun 2026-11-15)

Week-index math — week 0 = the Monday of the planning week (planning_monday = 2026-06-15). Week 20 starts 2026-06-15 + 20 weeks = 2026-10-19 (Mon); week 23 ends 2026-11-15 (Sun). The calendar bitmask converter (_calendar_entries_to_bitmask) overlaps a week if the entry range touches any day Mon-Sun in that week.

The three test scenarios

Test A — Calendar closure (Mechanism A)

Script: files/supply/test_calendar_closure_setup.sql

Creates a master.calendar 'France WH Closure 2026' (mode='not_available', closed-window semantics) with one master.calendar_entry covering 2026-10-19 → 2026-11-15 (weeks 20-23). Assigns it to ship_calendar_id on routes 169, 197, 225. Clear ship_calendar_id first if the capacity test was previously applied (to isolate the calendar mechanism).

After applying: re-run Supply Planning for pipeline 4 / supply scenario 1, then verify.

Test B — Resource capacity closure (Mechanism B)

Script: files/supply/test_capacity_closure_setup.sql

Links routes 169/197/225 to a synthetic shared TRANSPORT resource (id = 9001) via master.route_resource (qty_per_unit=1.0). Sets pipe_supply_capacity.capacity_qty=0 for weeks 20-23 (9999 elsewhere) on resource 9001, scoped to supply scenario_id = 1. Clears ship_calendar_id on the three routes (to isolate the capacity mechanism — no calendar closure).

Note: with capacity_enforcement='soft' (the Baseline scenario default), 0 * soft_capacity_multiplier(1.2) = 0 → still blocks. For harder cut-off (orders split/blocked, not just warned), run against supply scenario 7 ("Hard Capacity") or override the supply scenario's capacity_enforcement='hard'.

After applying: re-run Supply Planning for pipeline 4 / supply scenario 1, then verify.

Test C — Import-loader (calendared route via staging)

No SQL script — this validates the import_etl.py route entry directly.

Populate import.calendar, import.calendar_entry, and import.route (with ship_calendar_name set), then run a full import. Verify that master.route.ship_calendar_id is populated for the matching routes. This tests that the import pipeline now (a) actually loads routes (was broken) and (b) links the calendar by name.

See Import-loader test below.

How to drive the test

All commands run from the repository root. Set UTF-8 encoding first (PowerShell) because the test scripts contain accented site names.

Test A — Calendar closure

$env:PGCLIENTENCODING="UTF8"
psql -d thebicycle -f files/supply/test_calendar_closure_setup.sql
# Re-run Supply Planning in the UI (Supply tab → Run) for pipeline 4 / scenario 1
# OR trigger via API:
#   POST /api/pipeline/run-supply  { "pipeline_id": 4, "scenario_id": 1 }

Test B — Resource capacity closure

$env:PGCLIENTENCODING="UTF8"
psql -d thebicycle -f files/supply/test_capacity_closure_setup.sql
# Re-run Supply Planning for pipeline 4 / scenario 1 (clear Test A's calendar first —
# the script already clears ship_calendar_id on routes 169/197/225).

Run A and B on separate supply runs (each test sets up the DB state, then you run supply, then you verify, then you rollback before the next test).

Verify either scenario

$env:PYTHONIOENCODING="utf-8"
# After re-running Supply Planning:
python files/supply/verify_route_closure.py `
    --pipeline-id 4 --scenario-id 1 `
    --site-id 301,302,303 --window 20,23 `
    --mechanism calendar   # or: --mechanism capacity --resource-id 9001

The script prints a per-week summary (orders, exceptions, capacity) and a PASS/FAIL evaluation with exit code 0 (pass) / 1 (fail) / 2 (CH error).

Import-loader test

# 1. Stage a calendar + a route referencing it by name
psql -d thebicycle <<'SQL'
INSERT INTO import.calendar (name, type, mode, description)
VALUES ('Closure Test Cal', 'working', 'not_available', 'test')
ON CONFLICT (name) DO NOTHING;
INSERT INTO import.calendar_entry (calendar_name, date_from, date_to, label)
VALUES ('Closure Test Cal', '2026-10-19', '2026-11-15', 'test closure')
ON CONFLICT DO NOTHING;
-- Stage a route (item/site/route_type must already exist in master via xuid)
INSERT INTO import.route (item_xuid, site_xuid, route_type_xuid, source_site_xuid,
                          lead_time, unit_cost, ship_calendar_name)
SELECT 'chain_lube', 'cycles_paris', 'TRANSFER', 'france_wh', 1, 5.0, 'Closure Test Cal'
ON CONFLICT (item_xuid, site_xuid, route_type_xuid) DO UPDATE
    SET ship_calendar_name = EXCLUDED.ship_calendar_name;
SQL

# 2. Run the import (calendars first, then route — the STEPS order enforces this)
python -c "import sys; sys.path.insert(0,'files'); from etl.import_etl import ImportETLRunner; ImportETLRunner().run(tables=['calendar','calendar_entry','route'], modes={'route':'upsert'})"

# 3. Verify the link was created
psql -d thebicycle -c "SELECT r.id, r.ship_calendar_id, c.name FROM master.route r LEFT JOIN master.calendar c ON c.id = r.ship_calendar_id WHERE r.id IN (169,197,225);"

Expected results

Test A — Calendar closure

Check Expectation
TRANSFER_PREPOS orders One per store, arriving in the anchor week (week 19 = last open week before the closure)
Regular TRANSFER orders in weeks 20-23 Zero (all three routes closed)
CapacityExceeded exceptions None (calendar closure uses pull-forward, not capacity)
Shortage Possibly, only if the anchor week can't absorb all closed-window demand

Test B — Resource capacity closure

Check Expectation
TRANSFER_PREPOS orders None (capacity closure does NOT pull forward)
TRANSFER orders in weeks 20-23 Zero (resource 9001 blocked)
CapacityExceeded exceptions Present in weeks 20-23 on resource 9001
PIPE_supply_capacity.used_qty in weeks 20-23 0 (capped at capacity_qty=0)
PIPE_supply_capacity.capacity_qty in weeks 20-23 0

Test C — Import-loader

Check Expectation
master.route.ship_calendar_id (routes matching the staged xuids) Populated (non-NULL), pointing at the 'Closure Test Cal' calendar
import_table_result for route step rows_loaded > 0, rows_rejected = 0
Routes not in staging Unchanged (upsert updates-in-place, preserves id + route_resource)

Verification

Automated — verify_route_closure.py

python files/supply/verify_route_closure.py --pipeline-id 4 --scenario-id 1 `
    --site-id 301,302,303 --window 20,23 --mechanism calendar
Output (example, after a calendar-closure supply run): 
-- Supply orders (site, type, arrival_week, qty) ------------------
  site=301   TRANSFER_PREPOS   wk=19  qty=40.00
  site=302   TRANSFER_PREPOS   wk=19  qty=40.00
  site=303   TRANSFER_PREPOS   wk=19  qty=40.00
-- Exceptions in closed window (site, week, type, qty, count) -----
  (none)
-- Evaluation ------------------------------------------------------
  PASS: TRANSFER_PREPOS pull-forward found, anchor week=19 (< window start, correct)
  PASS: no regular TRANSFER orders in the closed window.
  PASS: no CapacityExceeded exceptions (calendar closure uses pull-forward, not capacity).
RESULT: PASS (calendar closure expectations)

For the capacity mechanism (--mechanism capacity --resource-id 9001): 

-- Capacity (week, capacity_qty, used_qty) -------------------------
  wk=20  capacity=0.00  used=0.00
  wk=21  capacity=0.00  used=0.00
  wk=22  capacity=0.00  used=0.00
  wk=23  capacity=0.00  used=0.00
-- Evaluation ------------------------------------------------------
  PASS: no TRANSFER_PREPOS orders (capacity closure does not pull forward).
  PASS: CapacityExceeded exceptions in closed window: 6 across sites/weeks.
  PASS: capacity_qty=0 in weeks [20, 21, 22, 23] (resource 9001).
RESULT: PASS (capacity closure expectations)

Manual — ClickHouse queries

-- Orders by type/week for the three stores (either test)
SELECT site_id, order_type, arrival_week, sum(qty) AS qty
FROM PIPE_supply_orders
WHERE pipeline_id = 4 AND scenario_id = 1
  AND site_id IN (301,302,303)
  AND arrival_week BETWEEN 18 AND 25
GROUP BY site_id, order_type, arrival_week
ORDER BY site_id, arrival_week, order_type;

-- Exceptions in the closed window (Test B)
SELECT site_id, week, exception_type, sum(qty) AS qty, count() AS n
FROM PIPE_supply_exceptions
WHERE pipeline_id = 4 AND scenario_id = 1
  AND site_id IN (301,302,303)
  AND week BETWEEN 20 AND 23
GROUP BY site_id, week, exception_type
ORDER BY site_id, week;

-- Capacity usage (Test B)
SELECT week, max(capacity_qty) AS cap, max(used_qty) AS used
FROM PIPE_supply_capacity
WHERE pipeline_id = 4 AND scenario_id = 1 AND resource_id = 9001
  AND week BETWEEN 20 AND 23
GROUP BY week ORDER BY week;

Cleanup

Test A (calendar)

UPDATE master.route SET ship_calendar_id = NULL WHERE id IN (169, 197, 225);
DELETE FROM master.calendar_entry
  WHERE calendar_id = (SELECT id FROM master.calendar WHERE name = 'France WH Closure 2026');
DELETE FROM master.calendar WHERE name = 'France WH Closure 2026';

Test B (capacity)

DELETE FROM master.route_resource WHERE resource_id = 9001;
DELETE FROM scenario.pipe_supply_capacity
  WHERE resource_type = 'TRANSPORT' AND resource_id = 9001 AND scenario_id = 1;

Test C (import-loader)

-- Restore the route's calendar link (clear the test link)
UPDATE master.route SET ship_calendar_id = NULL
  WHERE ship_calendar_id = (SELECT id FROM master.calendar WHERE name = 'Closure Test Cal');
DELETE FROM import.calendar_entry WHERE calendar_name = 'Closure Test Cal';
DELETE FROM import.calendar WHERE name = 'Closure Test Cal';
DELETE FROM import.route WHERE ship_calendar_name = 'Closure Test Cal';
DELETE FROM master.calendar WHERE name = 'Closure Test Cal';

Code review findings & fixes

The implementation was reviewed (local uncommitted review) and the following were fixed:

1. CRITICAL — Broken route import loader (Gap 1)

Problem: The "route" entry in import_etl.py declared target="master.route" but all its SQL was bill_of_material — so a standard import never loaded routes from import.route.

Fix: Split into a real "route" entry (upsert from import.route) + a real "bill_of_material" entry.

2. CRITICAL — ship_calendar_name never linked (Gap 2)

Problem: Even with routes loading, ship_calendar_name was ignored — no JOIN to master.calendar.id, so calendar closure couldn't be configured via import.

Fix: Route upsert LEFT JOIN master.calendar c ON c.name = i.ship_calendar_name → ship_calendar_id. Calendar + calendar_entry import steps moved before the route step (verified: calendar idx 16/17 < route idx 18) so a full import resolves links in one pass. A sql_rejected check flags routes whose ship_calendar_name doesn't match an existing calendar.

3. WARNING — TRUNCATE master.route CASCADE destroyed route_resource

Problem: The initial route entry used default_mode: "truncate_replace" with TRUNCATE master.route CASCADE. Because route_resource.route_id is FK ON DELETE CASCADE, this wiped every resource link — unrecoverable from import.route (which has no resource columns).

Fix: Switched route default_mode to "upsert" with an id-preserving two-statement upsert: (1) UPDATE existing routes in place (preserves id → preserves route_resource), (2) INSERT only genuinely-new natural keys. The demo's 46 duplicate (item,site,type) groups block a true ON CONFLICT unique index, so the update-in-place + insert-new pattern is the correct id-preserving alternative. The TRUNCATE ... CASCADE is retained for explicit truncate_replace mode (full ERP snapshot reload) with a comment documenting that it destroys resource links.

4. WARNING — _execute_step KeyError for truncate_replace mode

Problem: step[f"sql_{mode}"] raised KeyError('sql_truncate_replace') for every default_mode="truncate_replace" step (demand_actuals, on_hand, route, BOM) — silently failing those steps on a full API import.

Fix: Changed to step.get(f"sql_{mode}") or step.get("sql_upsert", step.get("sql_insert")) so truncate_replace falls back to the upsert SQL (run after the truncate block). This restores intended behavior — a deploy-time note that the first full API import will now actually load these tables (previously silently errored).

5. SUGGESTION — Dead code in verify_route_closure.py

Problem: A leftover if False else [] branch in evaluate() was unreachable and immediately overwritten.

Fix: Removed the dead branch.

Risks & guards

  • Isolation — Run A and B on separate supply runs. Each test sets up DB state; verify, then rollback before the next. The capacity test explicitly clears ship_calendar_id on the three routes so the calendar mechanism doesn't interfere.
  • capacity_enforcement mode — soft mode (Baseline scenario default) still blocks at capacity_qty=0 (0 * 1.2 = 0). For harder cut-off use the "Hard Capacity" scenario (id 7).
  • Calendar window dates — must align to the planning week. With planning_monday = 2026-06-15, week 20 = 2026-10-19. If the planning date changes, the calendar_entry dates must be recomputed. The verify script uses week indices (not dates), so it's planning-date-independent.
  • route_resource schema — lives in master (not scenario). The capacity test script was corrected to reference master.route_resource.
  • Import route id stability — the upsert preserves existing route ids (update-in-place), so route_resource FK children survive. Explicit truncate_replace mode reassigns ids via ROW_NUMBER() and destroys route_resource — use only for a full ERP snapshot reload when resources are also re-imported.
  • Duplicate natural keys — the demo has 46 duplicate (item,site,type) route groups. The upsert updates the lowest-id row per group; extras are left untouched (intentional dups are not deleted).
  • Planning date — the verify script and test scripts use week indices derived from planning_monday. No date.today() in business logic.

Out of scope

  • Import tables for Mechanism B (import.route_resource, import.supply_capacity) — documented follow-up (Gap 4c). Mechanism B still requires direct SQL.
  • mode='available' calendars (open-window semantics) — not tested; the tests use not_available (closed-window). The bitmask converter supports both.
  • Multi-resource routes (a route consuming both a Machine and Labor resource) — the capacity test uses a single TRANSPORT resource. The engine supports multi-resource via master.route_resource rows.
  • Calendar closure on BUY/MAKE routes — pull-forward only applies to TRANSFER routes; BUY/MAKE supply is calendar-independent. Not tested.