Supersession — Developer Internals¶

Status: Implemented & renovated 2026-09-02. User guide: Supersession Manager. Canonical DDL: files/DDL/schema.sql (table …item_supersession).

Directed replacement edges (from_item → to_item) that tell the forecast pipeline, both supply engines, and the indirect-demand rollup how to treat an old part and its successor. This page is the developer reference for the engine touchpoints, schema contract, and the September-2026 renovation.

1. Schema contract¶

Tenant table — `{schema}.item_supersession`¶

Defined in files/DDL/schema.sql (canonical) and idempotently guarded at runtime by api.main._ensure_supersession_table — either creator wins first, both write the same definition, so fresh installs do not depend on call order.

CREATE TABLE IF NOT EXISTS {schema}.item_supersession (
  id              BIGSERIAL PRIMARY KEY,
  from_item_id    BIGINT NOT NULL,
  to_item_id      BIGINT NOT NULL,
  site_id         BIGINT,                -- NULL = global edge
  effective_date  DATE,                  -- NULL = immediate
  demand_factor   DOUBLE PRECISION NOT NULL DEFAULT 1.0,
  stock_rollable  BOOLEAN NOT NULL DEFAULT TRUE,
  notes           TEXT,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW(),
  CONSTRAINT item_supersession_no_self CHECK (from_item_id <> to_item_id)
);

Natural key = COALESCE-expression unique index¶

The uniqueness mechanism is an expression unique index, not a plain constraint:

CREATE UNIQUE INDEX IF NOT EXISTS uq_item_supersession_nat_key
    ON {schema}.item_supersession (
      from_item_id, to_item_id,
      COALESCE(site_id, 0),                       -- NULL ⇒ 'global' slot 0
      COALESCE(effective_date, DATE '0001-01-01') -- NULL ⇒ 'immediate' sentinel
    );

Why a plain UNIQUE constraint failed

A plain UNIQUE (from_item_id, to_item_id, site_id) treats NULLs as distinct, so two API/UI-created global edges (site_id IS NULL) could co-exist, and the import ETL's ON CONFLICT (from_item_id, to_item_id, effective_date) could never infer the target. Imports silently rewrote edges on each run. The COALESCE index makes global and immediate explicit slots.

The migration for pre-existing tenants is files/DDL/migrations/supersession_canonical_natkey.sql — run it per schema. It drops the legacy item_supersession_from_to_key / item_supersession_from_to_site_key constraints if present and creates the index. Legacy constraints are also dropped idempotently by _ensure_supersession_table.

Import staging — `import.item_supersession`¶

Staging table (xuid-resolved in files/DDL/import_schema.sql): from_item_xuid, to_item_xuid, nullable site_xuid, nullable effective_date, demand_factor (default 1), stock_rollable (default TRUE — aligned 2026-09-02 with the tenant table), notes. The ETL step (import_etl.py::item_supersession) conflicts on the same COALESCE expressions as the tenant natural key — staged pk_cols and the SQL upsert point at the exact same expressions.

2. Engine touchpoints¶

Supersession is consulted at three pipeline surfaces and one API surface; each picks active edges only (effective_date IS NULL OR effective_date <= ref), where ref is the tenant's planning "today" via planning_today(pipeline_id) after the renovation (previously some consumers used CURRENT_DATE, diverging from the golden harness and the /active endpoint).

flowchart LR
    A[item_supersession edges] --> P[run_pipeline forecast step]
    A --> M[supply MRP runner + LP]
    A --> I[indirect rollup pass 1 + 2]
    A --> API[/api/supersession/active]

2.1 Forecast pipeline — `files/run_pipeline.py`¶

_apply_supersession_to_demand(df, edges, reference_date, config_path):

Active edge — the predecessor series is zeroed and its points are rolled onto the successor's series (× demand_factor) at the same (uid, date) via a left-join merge.
Future edge — the predecessor is zeroed only from effective_date; successor extension is left to netting/supply.
Site scope — edge fires only at its site_id; other sites stay unsuppressed.
Uid resolution — goes through db.db.resolve_uid_series_to_ids (numeric fast-path + lazy master.demand_actuals map). A (item_id, site_id) → uid map finds the successor's series (synthesized "{to_i}_{sid_u}" numeric form when the successor has no recorded series). This fixes import-seeded (xuid) tenants whose chains were silently skipped.

The step logs a summary ("N active edge(s), M site-specific"). Pre-2026 the caller's activity judgement used a miscounted helper; it now uses planning_today(pipeline_id) passed as reference_date.

2.2 Supply (MRP + LP)¶

files/supply_runner.py::_load_supersession_edges_supply (and the equivalent import inside files/supply_solver_runner.py) loads all edges; the runner filters activity via planning_today(pipeline_id) and separates:

_superseded_item_ids — global edges → injected into the routes CTE (superseded_route_filter) that blocks BUY/MAKE/TRANSFER/USE/BALANCING/ ZONE_BALANCING routes for the item.
_stock_rollup_edges — active edges with stock_rollable = true → the predecessor's master.on_hand is summed per site and folded into the successor plan's initial_inventory.

Both consumers then pass these to _load_sku_plans(...) / _apply_supersession_to_demand(...) as needed. A fail-loud loader (no try/except) raises immediately when the table is unreadable.

2.3 Indirect rollup — `files/indirect/indirect_demand.py`¶

_load_supersession_for_indirect(conn, schema, pipeline_id) returns the active global edges (site_id IS NULL); _filter_superseded(rows, ids) drops rows for those items in both passes (run_and_store + run_netted_reseed) across all demand types (network, good_replenishment, kit_make, use_return, return_receipt). The loader fails loudly and compares against planning_today(pipeline_id). Site-scoped edges deliberately do not drop rollup rows (other sites must keep working).

2.4 API chain-walk — `files/api/main.py::_walk_chain`¶

BFS upstream/downstream over the flat edge list + a recursive WITH RECURSIVE chain SQL CTE to pull chain context. Roles:

replaced — has outgoing, no incoming
top — no outgoing
middle — both

Used by GET /api/supersession/item/{item_id} and rendered in the badge / manager graphs.

3. API surface¶

All endpoints live in files/api/main.py and call _ensure_supersession_table first (idempotent guard — cheap because CREATE TABLE IF NOT EXISTS / CREATE INDEX IF NOT EXISTS are no-ops after bootstrap).

Method	Path	Notes
`GET`	`/api/supersession`	List with joined names/xuids
`POST`	`/api/supersession`	Create (admin). Self-edge → 400.
`PUT`	`/api/supersession/{id}`	Update (admin)
`DELETE`	`/api/supersession/{id}`	Delete (admin)
`GET`	`/api/supersession/item/{item_id}`	Chain context + role
`GET`	`/api/supersession/active?as_of=YYYY-MM-DD`	When `as_of` is absent the reference date is the tenant's planning "today" via `planning_today()` — this is part of the 2026-09-02 renovation (previously `CURRENT_DATE`).

Cascade: DELETE /api/items/{id} and DELETE /api/sites/{id} also delete edges where the item/site participates (_ensure_[supersession/item/side]_cascade paths).

4. Deprecation — legacy `master_item_chain`¶

A second, older substitution-graph concept still exists ({schema}.master_item_chain + import.item_chain). It is deprecated: consumed solely by MEIO V1's io_kit_bom_superseeded SQL (files/MEIO/intial/IOLibrary.py). Flagged deprecation in schema.sql, import_schema.sql, IOLibrary.py, and docs/developer/database.md. Do not propagate it to new consumers; the directed-edge table above is the canonical mechanism.

5. Renovation of 2026-09-02 (all fixes on that date)¶

Canonical DDL in schema.sql + supersession_canonical_natkey.sql migration; _ensure_supersession_table kept as an idempotent guard.
All activity comparisons use planning_today() / planning_today(pipeline_id) — forecast, supply, indirect, /active. CURRENT_DATE is gone from these loaders.
Import staging defaults aligned with the tenant table (stock_rollable DEFAULT TRUE, effective_date nullable).
Natural key is the COALESCE-expression unique index uq_item_supersession_nat_key; ETL ON CONFLICT/pk_cols now hit the exact same expressions.
Silent empty loaders removed — missing table raises with a clear hint.
Legacy master_item_chain marked deprecated (kept for MEIO V1 io_kit_bom_superseeded).
Forecast demand-redirect resolves every uid via db.db.resolve_uid_series_to_ids so import-seeded (xuid) tenants' chains are honored.
Dedicated unit tests — files/tests/test_supersession.py (12 cases).

6. Key files¶

Area	File
API + chain-walk + guard DDL	`files/api/main.py`
Forecast redirect	`files/run_pipeline.py` (`_load_supersession_edges`, `_apply_supersession_to_demand`)
Supply (MRP)	`files/supply_runner.py` (`_load_supersession_edges_supply`)
Supply (LP)	`files/supply_solver_runner.py` (same loader)
Indirect rollup	`files/indirect/indirect_demand.py` (`_load_supersession_for_indirect`, `_filter_superseded`)
Canonical DDL	`files/DDL/schema.sql` (tenant) / `files/DDL/import_schema.sql` (staging)
Migration	`files/DDL/migrations/supersession_canonical_natkey.sql`
ETL step	`files/etl/import_etl.py::item_supersession`
UI manager	`files/frontend/src/components/SupersessionManager.jsx`
UI badge	`files/frontend/src/components/SupersessionBadge.jsx`
Tests	`files/tests/test_supersession.py`
Deprecated legacy	`files/DDL/schema.sql::master_item_chain`, `files/MEIO/intial/IOLibrary.py`
User guide	`docs/user-guide/supersession.md`

Supersession — Developer Internals¶

1. Schema contract¶

Tenant table — {schema}.item_supersession¶

Natural key = COALESCE-expression unique index¶

Import staging — import.item_supersession¶

2. Engine touchpoints¶

2.1 Forecast pipeline — files/run_pipeline.py¶