Resource Modeling, Constraints & Calendars¶

This document covers how Mirabelle models resources, constrains routes with capacity and calendars, and applies these mechanisms across manufacturing, repair (MRO), and distribution contexts.

Audience

Supply planners, implementation consultants, and data engineers who need to set up master data for resource-constrained supply planning.

Architecture Overview¶

Mirabelle's resource model has three interconnected layers:

┌──────────────────────────────────────────────────────────────────┐
│                     1. ROUTES (master_route)                     │
│  Supply network topology: BUY / MAKE / TRANSFER / REPAIR / RETURN│
│  Each route links to calendars (8 FKs) and capacity resources    │
├──────────────────────────────────────────────────────────────────┤
│                   2. CALENDARS (master_calendar)                  │
│  Working / non-working periods → 52-bit bitmasks at runtime       │
│  Two types (working, end2end) · Two modes (available, not_avail)  │
│  Calendar closure triggers pull-forward pre-positioning           │
├──────────────────────────────────────────────────────────────────┤
│              3. CAPACITY RESOURCES (pipe_supply_capacity)         │
│  Finite weekly throughput per resource type                       │
│  SUPPLIER / PLANT / REPAIR_SHOP / TRANSPORT / Machine / Labor   │
│  Hard + soft (overtime) enforcement with cost functions           │
└──────────────────────────────────────────────────────────────────┘

The engine processes these layers in order:

Phase 0:  Calendar pull-forward → BOM explosion → Demand aggregation
Pass 1:   Upstream WHs (BUY/MAKE/REPAIR/RETURN)
          → _allocate_transfers() (Kahn topological sort)
Pass 2:   Store / leaf nodes (with injected TRANSFER receipts)
Pass 3:   Sourcing — route selection + calendar-adjusted lead times
Pass 4:   Capacity scheduling — priority-ordered resource scheduling
Post:     Expedite → Pegging → Cost breakdown

Master Data Tables¶

The following tables must be populated to enable resource-constrained supply planning:

Core Tables¶

Table	Schema	Purpose	Required
`master_route_type`	master	Defines planning types (BUY/MAKE/TRANSFER)	Yes
`master_route`	master	Supply routes with lead times, costs, lot-sizing, calendar FKs	Yes
`master_calendar`	master	Named calendars (working/end2end)	If using calendars
`master_calendar_entry`	master	Date ranges per calendar	If using calendars
`master_bill_of_material`	master	Parent→child component relationships for MAKE routes	If manufacturing
`pipe_supply_capacity`	scenario	Weekly capacity limits per resource	If using capacity
`route_resource`	scenario	Multi-resource consumption per route	If multi-resource
`master_on_hand`	master	Current inventory (serviceable + bad)	Yes
`master_item`	master	Item master (cost, price, shelf life)	Yes
`master_supply_orders_firm`	master	ERP-originated firm orders	If ERP-connected

Calendar FK Columns on `master_route`¶

Each route can reference up to 8 calendars for different lead-time components:

Column	Lead-Time Component	Typical Calendar Type
`lead_time_calendar_id`	Base lead time	`working`
`pick_pack_time_calendar_id`	Pick & pack operations	`working`
`ship_calendar_id`	Shipping availability	`end2end`
`transit_time_calendar_id`	Carrier transit schedule	`working`
`dock_calendar_id`	Dock receiving windows	`end2end`
`inspection_time_calendar_id`	QC inspection	`working`
`safety_lead_time_calendar_id`	Safety buffer	`working`
`ptf_calendar_id`	Planned-time-to-fence	`end2end`

Output Tables (engine-produced)¶

Table	Purpose
`pipe_supply_orders`	Planned/constrained/delayed/expedited/overtime orders
`pipe_supply_inventory_projection`	52-week projected inventory
`pipe_supply_pegging`	Demand↔supply FIFO links
`pipe_supply_exceptions`	Shortage, CapacityExceeded, RepairPoolEmpty, etc.
`pipe_supply_capacity` (updated)	`used_qty` filled by engine

Routes — Supply Network Topology¶

Route Types¶

`master_route_type.planning_type`	Order Generated	Use Case
`BUY`	BUY order	Procurement from external supplier
`MAKE`	BUILD order	Manufacturing/assembly at a plant
`TRANSFER`	TRANSFER order	Distribution between warehouses

Additional route types for reverse logistics (populated via reverse-logistics loader):

Route Type	Order Generated	Use Case
`RETURN`	RETURN order	Field returns to depot
`REPAIR`	REPAIR order	Refurbishment at repair shop

Key Route Fields¶

SELECT
    id, item_id, site_id, source_site_id, type_id,
    lead_time, pick_pack_time, transit_time, inspection_time, safety_lead_time, ptf,
    min_qty, mult_qty, max_qty,     -- lot-sizing
    quota, priority,                 -- sourcing policy
    yield, unit_cost, order_cost,    -- cost model
    ship_calendar_id,                -- shipping calendar FK
    return_rate, repair_yield,       -- reverse logistics
    lead_time_cv, wip_qty            -- stochastic LT + work-in-progress
FROM master_route;

Lot-Sizing Constraints¶

Field	Behaviour
`min_qty`	Minimum order quantity (MOQ). Engine raises order to at least this.
`mult_qty`	Lot multiple. Order quantity must be a multiple of this value.
`max_qty`	Maximum order quantity. Excess demand splits into multiple orders.
`quota`	Maximum share of total demand this route can serve (0–1).

The sourcing pass (sourcing.rs) applies a 7-step allocate logic:

Check quota cap
Apply max_qty cap
Floor to min_qty
Verify min_qty ≤ max_qty (skip route if not)
Round up to mult_qty
Re-apply max_qty after rounding
Floor to nearest valid multiple

Calendars — Working/Non-Working Periods¶

Calendar Model¶

master_calendar
    id, name, type, mode, description

master_calendar_entry
    id, calendar_id, date_from, date_to, label

Two types:

Type	Effect
`working`	Extends lead times by marking non-working days. A closed week adds 1 to effective lead time.
`end2end`	Constrains when operations can start or finish. Closed weeks block the operation entirely.

Two modes:

Mode	Interpretation
`not_available`	Listed date ranges are closed; everything else is open.
`available`	Listed date ranges are the only open periods; everything else is closed.

52-Bit Bitmask Conversion¶

At runtime, date-range entries are converted to a 52-bit bitmask:

Bit i (0-indexed from LSB) = week i
  1 = open/available
  0 = closed/non-working

The Rust Calendar struct (calendar.rs) provides:

Method	Description
`Calendar::from_bits(bits)`	Construct from raw u64 (lower 52 bits)
`Calendar::all_open()`	All 52 weeks open (default)
`Calendar::is_open(week)`	Check if a specific week is open
`Calendar::next_open_week(from)`	Scan forward to find next open week
`Calendar::prev_open_week(from)`	Scan backward to find previous open week
`Calendar::shift_to_open(week, direction)`	Shift to nearest open week (Forward/Backward)
`Calendar::open_weeks_in_range(start, end)`	All open weeks in inclusive range

Calendar Closure & Pull-Forward¶

When a route's shipping calendar has closed weeks, the engine applies pull-forward logic (_apply_calendar_pull_forward() in supply_runner.py):

Identify contiguous closed-week ranges from the calendar bitmask
Accumulate demand from all closed weeks
Place a single pre-position order in the last open week before the closure
Tag the order as TRANSFER_PREPOS

This is preferred over zero capacity because: - Pre-positioning provides stock visibility - The order is planned and costed properly - The planner sees why the order appears early

Annual supplier shutdown

Model recurring supplier holidays by creating a not_available calendar covering the shutdown period, then assign it to the BUY route's ship_calendar_id.

Capacity Resources¶

Resource Types¶

`resource_type`	Description	Typical Context
`SUPPLIER`	External vendor capacity	Procurement
`PLANT`	Manufacturing line/plant	Manufacturing
`REPAIR_SHOP`	Repair/refurbishment facility	MRO
`TRANSPORT`	Shipping lane or carrier	Distribution
`Machine`	Production machine	Manufacturing
`Labor`	Workforce	Manufacturing
`RepairBench`	Repair workstation	MRO
`Tool`	Tooling/fixture	Manufacturing

Capacity Table Schema¶

-- pipe_supply_capacity: weekly capacity per resource
-- Note: soft_capacity and overtime_cost_pct are added by supply_schema_v2.sql
-- (not present in the base DDL in supply_schema.sql).
CREATE TABLE pipe_supply_capacity (
    id             BIGSERIAL PRIMARY KEY,
    scenario_id    BIGINT,
    resource_type  VARCHAR(30) NOT NULL,  -- SUPPLIER/PLANT/REPAIR_SHOP/TRANSPORT
    resource_id    BIGINT NOT NULL,
    week           SMALLINT NOT NULL,    -- 0-51
    capacity_qty   DOUBLE PRECISION NOT NULL,
    used_qty       DOUBLE PRECISION DEFAULT 0,
    soft_capacity  DOUBLE PRECISION,     -- overtime ceiling (> capacity_qty)
    overtime_cost_pct DOUBLE PRECISION DEFAULT 0.5,
    UNIQUE (scenario_id, resource_type, resource_id, week)
);

Multi-Resource Consumption (route_resource)¶

When a single route consumes multiple resources simultaneously (e.g., machine + labor):

-- route_resource: links routes to their resource consumption
CREATE TABLE route_resource (
    id             SERIAL PRIMARY KEY,
    route_id       BIGINT NOT NULL REFERENCES route(id),
    resource_id    BIGINT NOT NULL,
    resource_type  VARCHAR(30) NOT NULL,  -- Machine/Labor/Transport/RepairBench/Tool
    qty_per_unit   DOUBLE PRECISION NOT NULL DEFAULT 1.0,
    description    TEXT,
    UNIQUE (route_id, resource_id)
);

The qty_per_unit field specifies how much of the resource's capacity is consumed per unit of item produced/shipped. For example, if assembling 1 unit requires 0.5 machine-hours and 2.0 labor-hours:

route_id	resource_id	resource_type	qty_per_unit
101	10	Machine	0.5
101	20	Labor	2.0

Hard vs Soft Capacity¶

Mode	Behaviour
Hard	Orders exceeding capacity are pushed forward week by week. If no slot in 52-week horizon → `Constrained` status + `CapacityExceeded` exception.
Soft	Orders within the overtime band (capacity × soft_multiplier) are scheduled with `Overtime` status at extra cost. Orders beyond the soft ceiling are pushed forward.

The soft_capacity_multiplier (default 1.2, configurable via EngineConfig) defines the overtime ceiling:

hard_cap = 100 units/week
soft_multiplier = 1.2
soft_cap = 120 units/week (overtime band = 20 units)
overtime_cost_multiplier = 1.5 (50% premium on unit_cost)

Constraint Algebra¶

The constraint system (constraints.rs) defines five constraint kinds:

Constraint Kinds¶

Kind	Fields	Description
`LaneCapacity`	`from_location_id`, `to_location_id`, `max_qty_per_week`, `premium_cost_per_unit`	Max flow per week on transport lane A→B
`LocationStock`	`location_id`, `max_qty`, `min_qty`, `holding_cost_per_unit_per_week`, `overflow_cost_per_unit_per_week`	Min/max stock at a location
`ResourceCapacity`	`resource_id`, `hard_capacity_per_week`, `soft_multiplier`, `overtime_cost_multiplier`, `subcontract_cost_per_unit`	Time-based resource capacity per week
`ProductionRate`	`resource_id`, `item_id`, `max_qty_per_week`	Per-item production rate on a resource
`CampaignBatch`	(stub — reserved for future)	Campaign/batch scheduling

Violation Cost Functions¶

Function	Formula	Use Case
`LinearPerUnit`	`cost = cost_per_unit × overflow_qty`	Simple overtime premium
`Fixed`	`cost = fixed_cost`	Fixed penalty regardless of overflow magnitude
`StepFunction`	Piecewise: `[(threshold₁, rate₁), (threshold₂, rate₂), ...]`	Escalating penalty tiers

Example: Step Function for Overtime¶

{
  "kind": "step_function",
  "steps": [
    [10.0, 2.0],
    [50.0, 5.0],
    [Infinity, 10.0]
  ]
}

First 10 overflow units: 2.0 per unit
Next 40 overflow units (10–50): 5.0 per unit
Beyond 50: 10.0 per unit

Constraint Modes¶

enum ConstraintMode {
    Hard,                                   // violation forbidden
    Soft { violation_cost: ViolationCostFn } // allowed at cost
}

The solver (solver.rs) evaluates all applicable constraints for each proposed order and picks the cheapest feasible alternative from:

Accept — schedule as-is (within limits)
Delay — push to later week with capacity
Premium transport — use expedited lane (LaneCapacity)
Overtime — use soft band (ResourceCapacity)
Subcontract — outsource beyond soft band (ResourceCapacity)

Engine Behaviour¶

Sourcing Pass (Pass 3 — `sourcing.rs`)¶

For each SKU, routes are evaluated in source_priority order (default: Repair → Return → Transfer → Build → Buy):

Check firm horizon block (don't plan before latest firm order of same type)
Check min lead-time constraint (if respect_lead_time = true)
Check repair pool (sufficient bad stock for REPAIR)
Check transfer pool (sufficient upstream inventory for TRANSFER)
Calendar adjustment:
shipping_calendar.shift_to_open(release_week, Backward) — move release to last open shipping week
receiving_calendar.shift_to_open(arrival_week, Forward) — move arrival to next open receiving week
Stochastic lead-time padding: effective_LT = lead_time + ceil(z_score × lead_time_cv × lead_time)
Lot-sizing via 7-step allocate logic

Capacity Scheduling Pass (Pass 4 — `scheduling.rs`)¶

Orders are grouped by capacity_id, then scheduled in priority order:

Priority	Order Type
5 (highest)	REPAIR
4	RETURN
3	TRANSFER
2	BUILD
1 (lowest)	BUY

Within the same priority, lower-cost orders are scheduled first.

Scheduling algorithm per constrained order:

for week in release_week..52:
    avail = capacity[(resource_id, week)]
    if avail >= needed:
        schedule at week → status = Planned
    elif avail × (1 + soft_mult) >= needed:
        schedule at week → status = Overtime
    else:
        continue to next week

if no week found:
    status = Constrained
    exception = CapacityExceeded

Calendar-Aware Lead Time in Sourcing¶

release_week = shift_to_open(target_week - lead_time, Backward)  # shipping calendar
arrival_week = shift_to_open(release_week + lead_time, Forward)  # receiving calendar

If the shipping calendar has weeks 20–23 closed: - A planned release in week 21 is shifted backward to week 19 (last open week) - The arrival is computed from week 19 + lead_time, then shifted forward on the receiving calendar

Context-Specific Patterns¶

Manufacturing (MAKE/BUILD)¶

BOM explosion: BUILD routes trigger _explode_bom_demand() — component_demand[w] += parent_demand[w] × bom_qty
Resource constraints: PLANT / Machine / Labor with qty_per_unit per route
Production rate limits: Per-item rate on a specific resource (ProductionRate constraint)
Lot-sizing: min_qty/mult_qty/max_qty with 7-step allocate logic
Setup cost: One-time changeover cost per order (setup_cost on route)
CampaignBatch: Reserved for future campaign/batch scheduling

Repair (MRO/Aerospace)¶

REPAIR routes with repair_yield (e.g., 0.60 = 60% yield) and repair_turnaround
Bad stock pool: initial_oh_bad + bad_returns[] feed the repair pool
Repair pool checking: Sourcing verifies sufficient bad stock; RepairPoolEmpty exception if insufficient
RepairBench resource type: Capacity-constrained repair shops
Causal BOM: Position-aware parts for aerospace (4-level specificity: global → position → site+position → asset+position)
Failure rate waterfall: Drives demand for repair/overhaul events
Diagnostic/scrap cost: Route-level diagnostic_cost and scrap_cost columns

Distribution (TRANSFER)¶

Multi-echelon network: Suppliers → Central WH → Regional WHs → Dealers
TRANSFER routes with shipping/receiving calendar bitmasks
Lane capacity constraints: Max flow per week on transport lane A→B
Calendar pull-forward: Pre-positioning for supplier/warehouse shutdowns
Topological allocation: _allocate_transfers() distributes upstream supply proportionally via Kahn's algorithm
Transport mode: Air/Sea/Road/Rail with mode-specific cost and lead time
Volume discounts: JSON array [{qty: 100, discount_pct: 0.05}, ...] on route
Expedite routes: is_expedite = true marks faster-but-costlier alternatives

Sample Data — Manufacturing¶

This section provides complete INSERT statements for a bicycle manufacturing scenario.

Sample SQL prerequisites

All INSERTs use explicit PKs with no ON CONFLICT. Run on a clean DB only. For existing data, add ON CONFLICT (id) DO NOTHING (or the appropriate unique key) to each statement.

Step 1: Route Types¶

-- Run on a clean DB. Add ON CONFLICT DO NOTHING for existing data.
INSERT INTO master_route_type (id, xuid, name, planning_type) VALUES
    (1, 'BUY_EXT',     'Buy External',    'BUY'),
    (2, 'MAKE_ASSY',   'Make Assembly',   'MAKE'),
    (3, 'TRANSFER',    'Transfer',        'TRANSFER'),
    (4, 'RETURN',      'Return Flow',     'RETURN'),
    (5, 'REPAIR',      'Repair/Refurb',   'REPAIR');

Step 2: Items¶

INSERT INTO master_item (id, name, description, type_id, price, cost, expiry_period_weeks) VALUES
    (1,   'Frame',             'Aluminium frame',              1, 120.00, 75.00, NULL),
    (2,   'Fork',              'Carbon fork',                  1,  90.00, 50.00, NULL),
    (3,   'Drivetrain Kit',    'Chain + cassette + derailleur',1,  65.00, 35.00, NULL),
    (4,   'Brake Kit',         'Disc brake set',               1,  55.00, 28.00, NULL),
    (5,   'Wheel Set',         'Front + rear wheel',           1,  80.00, 42.00, NULL),
    (10,  'Transmission Assy', 'Full transmission assembly',   2, 200.00, 80.00, NULL),
    (11,  'Brake Assy',        'Full brake assembly',          2, 150.00, 50.00, NULL),
    (100, 'Bicycle Pro',      'Finished bicycle',             3, 850.00, 280.00, NULL);

Step 3: Locations¶

INSERT INTO master_location (id, name, type_id) VALUES
    (1,  'Supplier Taiwan',    1),   -- supplier
    (2,  'Supplier Germany',   1),   -- supplier
    (10, 'Central WH Munich',  2),   -- central warehouse
    (11, 'Assembly Plant',     3),   -- manufacturing plant
    (20, 'WH Spain',          2),   -- regional warehouse
    (21, 'WH France',         2);   -- regional warehouse

Step 4: Calendars¶

-- Plant summer shutdown (weeks 28–31)
INSERT INTO master_calendar (id, name, type, mode, description) VALUES
    (1, 'Plant Summer Shutdown', 'working', 'not_available',
     'Assembly plant closed weeks 28-31 (August)'),
    (2, 'German WH Calendar', 'end2end', 'not_available',
     'Munich WH closed for German public holidays');

-- Plant shutdown: weeks 28-31 (August)
INSERT INTO master_calendar_entry (id, calendar_id, date_from, date_to, label) VALUES
    (1, 1, '2026-07-13', '2026-08-09', 'Summer shutdown 2026');

-- German holidays affecting WH operations
INSERT INTO master_calendar_entry (id, calendar_id, date_from, date_to, label) VALUES
    (2, 2, '2026-12-21', '2026-12-31', 'Christmas/New Year closure');

Step 5: Routes — Procurement (BUY)¶

INSERT INTO master_route (id, item_id, site_id, source_site_id, type_id,
    lead_time, min_qty, mult_qty, max_qty, quota, priority,
    unit_cost, order_cost, yield, ship_calendar_id) VALUES
    -- Frame from Taiwan supplier → Assembly Plant
    (101, 1,   11, NULL, 1, 6,  100, 50, 500, 1.0, 1, 75.00, 200.00, 1.0, NULL),
    -- Fork from Taiwan supplier → Assembly Plant
    (102, 2,   11, NULL, 1, 6,  100, 50, 500, 1.0, 1, 50.00, 200.00, 1.0, NULL),
    -- Drivetrain Kit from Germany → Central WH
    (103, 3,   10, NULL, 1, 3,  50,  25, 200, 1.0, 1, 35.00, 150.00, 1.0, NULL),
    -- Brake Kit from Germany → Central WH
    (104, 4,   10, NULL, 1, 3,  50,  25, 200, 1.0, 1, 28.00, 150.00, 1.0, NULL),
    -- Wheel Set from Taiwan → Assembly Plant
    (105, 5,   11, NULL, 1, 6,  80,  20, 400, 1.0, 1, 42.00, 180.00, 1.0, NULL);

Step 6: Routes — Manufacturing (MAKE)¶

INSERT INTO master_route (id, item_id, site_id, source_site_id, type_id,
    lead_time, min_qty, mult_qty, max_qty, quota, priority,
    unit_cost, order_cost, yield, ship_calendar_id) VALUES
    -- Transmission Assembly at plant
    (201, 10,  11, 10, 2, 2, 10, 5, 100, 1.0, 1, 80.00, 500.00, 0.98, 1),
    -- Brake Assembly at plant
    (202, 11,  11, 10, 2, 2, 10, 5, 100, 1.0, 1, 50.00, 400.00, 0.97, 1),
    -- Bicycle Pro final assembly
    (203, 100, 11, 11, 2, 1, 5,  1, 50,  1.0, 1, 280.00, 800.00, 0.96, 1);

Note: ship_calendar_id = 1 links MAKE routes to the Plant Summer Shutdown calendar.

Step 7: BOM — Manufacturing Recipe¶

INSERT INTO master_bill_of_material (id, item_id, site_id, child_item_id, child_site_id,
    item_qty, child_qty, attach_rate, "offset") VALUES
    -- Transmission Assembly ← Drivetrain Kit (1:1)
    (1,  10, 11, 3,  10, 1, 1, 1.0, 0),
    -- Brake Assembly ← Brake Kit (1:1)
    (2,  11, 11, 4,  10, 1, 1, 1.0, 0),
    -- Bicycle Pro ← Frame (1:1)
    (3,  100, 11, 1,  11, 1, 1, 1.0, 0),
    -- Bicycle Pro ← Fork (1:1)
    (4,  100, 11, 2,  11, 1, 1, 1.0, 0),
    -- Bicycle Pro ← Wheel Set (1:1)
    (5,  100, 11, 5,  11, 1, 1, 1.0, 0),
    -- Bicycle Pro ← Transmission Assy (1:1, offset 1 week)
    (6,  100, 11, 10, 11, 1, 1, 1.0, 1),
    -- Bicycle Pro ← Brake Assy (1:1, offset 1 week)
    (7,  100, 11, 11, 11, 1, 1, 1.0, 1);

Step 8: Capacity — Plant & Machine¶

INSERT INTO pipe_supply_capacity (scenario_id, resource_type, resource_id, week, capacity_qty, soft_capacity, overtime_cost_pct) VALUES
    -- Assembly Plant: 200 units/week normal, 250 overtime (50% premium)
    (1, 'PLANT',  11, 0,  200, 250, 0.5),
    (1, 'PLANT',  11, 1,  200, 250, 0.5),
    (1, 'PLANT',  11, 2,  200, 250, 0.5),
    -- ... repeat for weeks 0–27 (before shutdown)
    -- During shutdown weeks 28–31: capacity = 0
    (1, 'PLANT',  11, 28, 0,   0,   0.5),
    (1, 'PLANT',  11, 29, 0,   0,   0.5),
    (1, 'PLANT',  11, 30, 0,   0,   0.5),
    (1, 'PLANT',  11, 31, 0,   0,   0.5),
    -- Post-shutdown: resume 200
    (1, 'PLANT',  11, 32, 200, 250, 0.5),
    -- ... weeks 32–51
    -- CNC Machine (resource 101): 80 units/week
    (1, 'Machine', 101, 0, 80, 100, 0.6),
    (1, 'Machine', 101, 1, 80, 100, 0.6)
    -- ... repeat for all 52 weeks
    ;

Step 9: Multi-Resource Consumption¶

INSERT INTO route_resource (route_id, resource_id, resource_type, qty_per_unit, description) VALUES
    -- Bicycle Pro assembly: 0.5 machine-hours + 2.0 labor-hours per unit
    (203, 101, 'Machine', 0.5, 'CNC frame alignment'),
    (203, 102, 'Labor',  2.0, 'Assembly technician'),
    -- Transmission Assy: 0.3 machine-hours + 1.5 labor-hours
    (201, 101, 'Machine', 0.3, 'Drivetrain assembly press'),
    (201, 103, 'Labor',  1.5, 'Drivetrain technician');

What the Engine Does¶

With this data, the engine:

BOM explosion: When demand for Bicycle Pro reaches week W, the engine computes component demand:
Frame: bicycle_demand[W] × 1
Fork: bicycle_demand[W] × 1
Transmission Assy: bicycle_demand[W] × 1 (offset by 1 week → demand in W-1)
Brake Assy: bicycle_demand[W] × 1 (offset by 1 week → demand in W-1)
Calendar pull-forward: During weeks 28–31 (plant shutdown), the engine accumulates demand and places a pre-position order in week 27.
Capacity scheduling: Orders are checked against PLANT (200/week) and Machine (80/week) limits. Excess is pushed forward or scheduled as overtime.

Sample Data — Repair / MRO¶

This section provides INSERT statements for an aerospace landing gear MRO scenario.

Sample SQL prerequisites

All INSERTs use explicit PKs with no ON CONFLICT. Run on a clean DB only. For existing data, add ON CONFLICT (id) DO NOTHING (or the appropriate unique key) to each statement.

Step 1: Items (Aerospace Components)¶

INSERT INTO master_item (id, name, description, type_id, price, cost) VALUES
    (200, 'Carbon Brake Assembly',  'A320 carbon brake',       4, 28000.00, 9500.00),
    (201, 'Hydraulic Actuator',     'Landing gear actuator',   4, 45000.00, 14000.00),
    (202, 'Brake Disc Set',         'Replacement discs',       4,  8000.00,  2500.00),
    (203, 'Seal Kit',              'Hydraulic seal kit',       4,  1200.00,   280.00);

Step 2: Locations¶

INSERT INTO master_location (id, name, type_id) VALUES
    (50, 'LHR Hub',          2),   -- London Heathrow maintenance hub
    (51, 'CDG Workshop',     2),   -- Paris Charles de Gaulle workshop
    (52, 'Repair Centre FRA', 4),  -- Frankfurt certified repair centre
    (53, 'OEM Supplier',     1);   -- Original equipment manufacturer

Step 3: Calendars¶

-- Repair centre annual certification shutdown (weeks 32–35)
INSERT INTO master_calendar (id, name, type, mode, description) VALUES
    (10, 'FRA Repair Shutdown', 'working', 'not_available',
     'Frankfurt repair centre closed for annual certification weeks 32-35');

INSERT INTO master_calendar_entry (id, calendar_id, date_from, date_to, label) VALUES
    (10, 10, '2026-08-03', '2026-08-30', 'Annual certification 2026');

Step 4: Routes — Procurement + Repair¶

INSERT INTO master_route (id, item_id, site_id, source_site_id, type_id,
    lead_time, min_qty, mult_qty, max_qty, quota, priority,
    unit_cost, order_cost, yield,
    return_rate, repair_yield, lead_time_cv, wip_qty,
    ship_calendar_id, diagnostic_cost, scrap_cost) VALUES
    -- BUY new from OEM → LHR Hub
    (301, 200, 50, NULL, 1, 12, 1, 1, 10, 0.3, 3,
     12000.00, 500.00, 1.0,  NULL, NULL, 0.2, NULL, NULL, NULL, NULL),
    -- REPAIR at Frankfurt → LHR Hub
    (302, 200, 50, 52,     5,  4, 1, 1, 5,  0.7, 1,
      4500.00, 300.00, 0.60, 0.08, 0.60, 0.15, 3.0, 10, 200.00, 800.00),
    -- BUY new from OEM → CDG Workshop
    (303, 201, 51, NULL, 1, 14, 1, 1, 5,  0.4, 3,
     18000.00, 600.00, 1.0,  NULL, NULL, 0.25, NULL, NULL, NULL, NULL),
    -- REPAIR at Frankfurt → CDG Workshop
    (304, 201, 51, 52,     5,  6, 1, 1, 3,  0.6, 1,
      7000.00, 400.00, 0.70, 0.05, 0.70, 0.18, 2.0, 10, 350.00, 1500.00);

Key fields explained:

Field	Route 302 (Carbon Brake Repair)	Meaning
`repair_yield`	0.60	60% of bad units are successfully repaired
`return_rate`	0.08	8% of field demand returns as bad stock
`lead_time_cv`	0.15	15% CV → stochastic LT padding of ~1 week
`wip_qty`	3.0	3 units currently in repair pipeline
`diagnostic_cost`	200.00	Cost to inspect/diagnose before repair
`scrap_cost`	800.00	Cost to dispose of non-repairable units
`ship_calendar_id`	10	FRA Repair Shutdown calendar

Step 5: BOM — Repair Kit¶

INSERT INTO master_bill_of_material (id, item_id, site_id, child_item_id, child_site_id,
    item_qty, child_qty, attach_rate, "offset") VALUES
    -- Carbon Brake repair requires Brake Disc Set (90% attach rate)
    (10, 200, 52, 202, 52, 1, 1, 0.90, 0),
    -- Carbon Brake repair requires Seal Kit (100% attach rate)
    (11, 200, 52, 203, 52, 1, 2, 1.00, 0);

Step 6: Capacity — Repair Shop¶

INSERT INTO pipe_supply_capacity (scenario_id, resource_type, resource_id, week, capacity_qty, soft_capacity, overtime_cost_pct) VALUES
    -- Frankfurt Repair Centre: 8 overhauls/week normal, 10 overtime
    (1, 'REPAIR_SHOP', 52, 0,  8, 10, 0.8),
    (1, 'REPAIR_SHOP', 52, 1,  8, 10, 0.8),
    -- ... weeks 0-31 normal
    -- Certification shutdown weeks 32-35: capacity = 0
    (1, 'REPAIR_SHOP', 52, 32, 0,  0, 0.8),
    (1, 'REPAIR_SHOP', 52, 33, 0,  0, 0.8),
    (1, 'REPAIR_SHOP', 52, 34, 0,  0, 0.8),
    (1, 'REPAIR_SHOP', 52, 35, 0,  0, 0.8),
    -- Post-certification: resume
    (1, 'REPAIR_SHOP', 52, 36, 8, 10, 0.8)
    -- ... weeks 36-51
    ;

Step 7: Multi-Resource for Repair¶

INSERT INTO route_resource (route_id, resource_id, resource_type, qty_per_unit, description) VALUES
    -- Carbon Brake repair: 1 RepairBench + 2.0 Labor + 0.5 Tool
    (302, 501, 'RepairBench', 1.0, 'Brake test bench'),
    (302, 502, 'Labor',       2.0, 'Certified brake technician'),
    (302, 503, 'Tool',        0.5, 'Torque calibration set'),
    -- Hydraulic Actuator repair: 1 RepairBench + 3.0 Labor + 1.0 Tool
    (304, 501, 'RepairBench', 1.0, 'Hydraulic test rig'),
    (304, 504, 'Labor',       3.0, 'Hydraulics specialist'),
    (304, 505, 'Tool',        1.0, 'Pressure testing kit');

What the Engine Does¶

Repair pool management: Bad stock from bad_returns[] and initial_oh_bad feeds the repair pool. Sourcing checks bad_pool >= repair_qty / repair_yield before scheduling REPAIR.
Yield accounting: If repair_yield = 0.60 and 6 bad units enter repair, only 3.6 serviceable units emerge (40% scrapped, incurring scrap_cost).
Calendar pull-forward: During FRA shutdown (weeks 32–35), repair orders are pulled to week 31 (pre-position).
Priority scheduling: REPAIR orders have priority 5 (highest), so they are scheduled before TRANSFER and BUY orders on the same resource.
Stochastic LT padding: With lead_time_cv = 0.15 and lead_time = 4, the effective lead time = 4 + ceil(z × 0.15 × 4) ≈ 5 weeks.

Sample Data — Distribution¶

This section provides INSERT statements for a multi-echelon distribution network.

Sample SQL prerequisites

All INSERTs use explicit PKs with no ON CONFLICT. Run on a clean DB only. For existing data, add ON CONFLICT (id) DO NOTHING (or the appropriate unique key) to each statement.

Step 1: Items¶

-- Reuses item id=100 'Bicycle Pro' from Manufacturing sample above.
-- Skip this INSERT if you already ran the Manufacturing sample.
INSERT INTO master_item (id, name, description, type_id, price, cost) VALUES
    (100, 'Bicycle Pro',  'Finished bicycle', 3, 850.00, 280.00)
ON CONFLICT (id) DO NOTHING;

Step 2: Locations¶

INSERT INTO master_location (id, name, type_id) VALUES
    (10, 'Central WH Munich', 2),   -- central warehouse
    (20, 'WH Spain',         2),   -- regional warehouse
    (21, 'WH France',        2),   -- regional warehouse
    (30, 'Dealer Barcelona', 5),   -- dealer
    (31, 'Dealer Madrid',    5),   -- dealer
    (32, 'Dealer Paris',     5);   -- dealer

Step 3: Calendars¶

-- Spanish summer closure (weeks 30–33)
INSERT INTO master_calendar (id, name, type, mode, description) VALUES
    (3, 'Spain WH Closure', 'working', 'not_available',
     'Spanish WH closed for summer holidays weeks 30-33');

INSERT INTO master_calendar_entry (id, calendar_id, date_from, date_to, label) VALUES
    (3, 3, '2026-07-20', '2026-08-16', 'Spanish summer holidays 2026');

-- Christmas closure for all European WHs
INSERT INTO master_calendar (id, name, type, mode, description) VALUES
    (4, 'EU Christmas', 'end2end', 'not_available',
     'European WHs closed Dec 24 - Jan 2');

INSERT INTO master_calendar_entry (id, calendar_id, date_from, date_to, label) VALUES
    (4, 4, '2026-12-21', '2027-01-04', 'Christmas/New Year 2026');

Step 4: Routes — Distribution (TRANSFER)¶

INSERT INTO master_route (id, item_id, site_id, source_site_id, type_id,
    lead_time, min_qty, mult_qty, max_qty, quota, priority,
    unit_cost, order_cost, yield,
    ship_calendar_id, transit_time_calendar_id,
    transport_mode, transport_fixed_cost) VALUES
    -- Central WH → Spain WH
    (401, 100, 20, 10, 3, 2, 5, 1, 100, 0.5, 1,
     15.00, 75.00, 1.0, 3, NULL, 'Road', 50.00),
    -- Central WH → France WH
    (402, 100, 21, 10, 3, 2, 5, 1, 100, 0.5, 1,
     18.00, 80.00, 1.0, 4, NULL, 'Road', 55.00),
    -- Spain WH → Dealer Barcelona
    (411, 100, 30, 20, 3, 1, 1, 1, 20, 1.0, 1,
      5.00, 25.00, 1.0, 3, NULL, 'Road', 20.00),
    -- Spain WH → Dealer Madrid
    (412, 100, 31, 20, 3, 1, 1, 1, 20, 1.0, 1,
      5.00, 25.00, 1.0, 3, NULL, 'Road', 20.00),
    -- France WH → Dealer Paris
    (421, 100, 32, 21, 3, 1, 1, 1, 20, 1.0, 1,
      5.00, 25.00, 1.0, 4, NULL, 'Road', 20.00);

Note: ship_calendar_id references the closure calendars so the engine adjusts shipping weeks.

Step 5: Lane Capacity Constraints¶

INSERT INTO pipe_supply_capacity (scenario_id, resource_type, resource_id, week, capacity_qty, soft_capacity, overtime_cost_pct) VALUES
    -- Transport lane Munich → Spain: max 150 units/week
    (1, 'TRANSPORT', 4010, 0, 150, 180, 0.3),
    (1, 'TRANSPORT', 4010, 1, 150, 180, 0.3),
    -- ... all 52 weeks
    -- Transport lane Munich → France: max 200 units/week
    (1, 'TRANSPORT', 4020, 0, 200, 240, 0.3),
    (1, 'TRANSPORT', 4020, 1, 200, 240, 0.3);
    -- ... all 52 weeks

Step 6: On-Hand Inventory¶

INSERT INTO master_on_hand (id, item_id, site_id, type_id, qty, unit_cost) VALUES
    -- Good stock at Central WH
    (1,  100, 10, 1, 500, 280.00),
    -- Good stock at Spain WH
    (2,  100, 20, 1,  80, 295.00),
    -- Good stock at France WH
    (3,  100, 21, 1, 120, 298.00),
    -- Bad stock at Spain WH (for returns)
    (4,  100, 20, 2,   5, 280.00);   -- type_id 2 = 'bad'

Step 7: Firm Orders (ERP)¶

INSERT INTO master_supply_orders_firm
    (external_id, item_id, site_id, source_site_id, order_type, qty,
     release_dt, arrival_dt, route_id, unit_cost, order_cost, total_cost,
     erp_status, source_system) VALUES
    ('PO-2026-0451', 100, 20, 10, 'TRANSFER', 30,
     '2026-06-22', '2026-07-06', 401, 15.00, 75.00, 525.00,
     'released', 'erp_sap');

What the Engine Does¶

Topological allocation: Pass 1 plans the Central WH (BUY orders). _allocate_transfers() distributes supply proportionally to Spain WH and France WH based on demand share. Pass 2 plans dealers with injected TRANSFER receipts.
Calendar pull-forward: When Spain WH is closed weeks 30–33, the engine pre-positions demand for those weeks into week 29 (tagged TRANSFER_PREPOS).
Lane capacity: If demand from Munich to Spain exceeds 150 units/week, excess is pushed to the next week with available lane capacity. Within the soft band (180), orders are scheduled as Overtime at 30% premium.
Firm order blocking: The released transfer PO blocks the engine from planning a competing TRANSFER order in the same horizon window.

API Reference¶

Endpoint	Method	Description
`/api/supply/capacity`	GET	Capacity utilization by resource/week
`/api/supply/capacity/resource-items`	GET	Items linked to a resource via routes
`/api/supply/capacity/resource-network`	GET	Graph of locations and lanes for a resource
`/api/supply/sku-flow`	GET	Full constraint analysis for one SKU×site
`/api/supply/routes/network`	GET	Route network graph
`/api/calendars`	GET	List all calendars
`/api/calendars`	POST	Create a new calendar
`/api/calendars/{id}`	PUT	Update a calendar
`/api/calendars/{id}`	DELETE	Delete a calendar
`/api/calendars/{id}/entries`	PUT	Update calendar date-range entries

Module Reference¶

Module	Location	Role
Python orchestrator	`files/supply_runner.py`	Data loading, pre/post-processing, 2-pass coordination
Rust engine	`files/supply/src/engine.rs`	Coordinates all Rust passes
Calendar	`files/supply/src/calendar.rs`	52-bit bitmask operations (`Calendar`, `ShiftDirection`)
Constraints	`files/supply/src/constraints.rs`	Hard/Soft constraint algebra, `ViolationCostFn`, `ConstraintKind`
Sourcing	`files/supply/src/sourcing.rs`	Route selection, `allocate()` lot-sizing, calendar-adjusted LT
Scheduling	`files/supply/src/scheduling.rs`	Capacity scheduling, priority ordering, soft/hard caps
Solver	`files/supply/src/solver.rs`	Constrained solver with ranked alternatives
Types	`files/supply/src/types.rs`	`SkuPlan`, `RouteOption`, `CapacityResource`, `SupplyOrder`
Supply graph	`files/supply/src/supply_graph.rs`	Constraint chain graph for UI visualisation
API router	`files/api/supply_router.py`	FastAPI routes under `/api/supply/`
Calendar UI	`files/frontend/src/components/CalendarManager.jsx`	Calendar CRUD, year view, entry editor
Supply detail	`files/frontend/src/components/DetailSupplyTab.jsx`	Route network SVG, capacity badges

Resource Modeling, Constraints & Calendars¶

Table of Contents¶

Architecture Overview¶

Master Data Tables¶

Core Tables¶

Calendar FK Columns on master_route¶

Output Tables (engine-produced)¶

Routes — Supply Network Topology¶

Route Types¶

Key Route Fields¶

Lot-Sizing Constraints¶

Calendars — Working/Non-Working Periods¶

Calendar Model¶

52-Bit Bitmask Conversion¶

Calendar Closure & Pull-Forward¶

Capacity Resources¶

Resource Types¶

Capacity Table Schema¶

Multi-Resource Consumption (route_resource)¶

Hard vs Soft Capacity¶

Constraint Algebra¶

Constraint Kinds¶

Violation Cost Functions¶

Example: Step Function for Overtime¶

Constraint Modes¶

Engine Behaviour¶

Sourcing Pass (Pass 3 — sourcing.rs)¶

Capacity Scheduling Pass (Pass 4 — scheduling.rs)¶

Calendar-Aware Lead Time in Sourcing¶

Context-Specific Patterns¶

Manufacturing (MAKE/BUILD)¶

Repair (MRO/Aerospace)¶

Distribution (TRANSFER)¶

Sample Data — Manufacturing¶

Step 1: Route Types¶

Step 2: Items¶

Step 3: Locations¶

Step 4: Calendars¶

Step 5: Routes — Procurement (BUY)¶

Step 6: Routes — Manufacturing (MAKE)¶

Step 7: BOM — Manufacturing Recipe¶

Step 8: Capacity — Plant & Machine¶

Step 9: Multi-Resource Consumption¶

What the Engine Does¶

Sample Data — Repair / MRO¶

Step 1: Items (Aerospace Components)¶

Step 2: Locations¶

Step 3: Calendars¶

Step 4: Routes — Procurement + Repair¶

Step 5: BOM — Repair Kit¶

Step 6: Capacity — Repair Shop¶

Step 7: Multi-Resource for Repair¶

What the Engine Does¶

Sample Data — Distribution¶

Step 1: Items¶

Step 2: Locations¶

Step 3: Calendars¶

Step 4: Routes — Distribution (TRANSFER)¶

Step 5: Lane Capacity Constraints¶

Step 6: On-Hand Inventory¶

Step 7: Firm Orders (ERP)¶

What the Engine Does¶

API Reference¶

Module Reference¶

Calendar FK Columns on `master_route`¶

Sourcing Pass (Pass 3 — `sourcing.rs`)¶

Capacity Scheduling Pass (Pass 4 — `scheduling.rs`)¶