Scenario Review, History & Comparison Platform — Design

Tenant for examples: thebicycle | Schema: scenario | CH database: thebicycle | Default Pipeline: pipeline_id=4, scenario_id=4 Status: Phase 1 design + foundation code. Living document — update as phases land.

Archive Date Migration (June 2026)

The integer pipeline_version_id identifier has been replaced by archive_date (a Date always equal to the Monday of the freeze week). All API endpoints, CH partition keys, and the PG registry table have been updated. The live state that was formerly pipeline_version_id = 0 is now archive_date = toMonday(today()). See Historization — Archive Date Model for the full specification.

---

Table of Contents

1. Overview & Goals
2. Core Concepts & Vocabulary
3. Architectural Principles
4. Reconciliation With Existing Code
5. Historization Strategy (Path Y2)
6. Data Model — CH
7. Data Model — PostgreSQL
8. Plan vs Actual Tables
9. Recommendation Approval & Auto-Approval
10. Snapshot Lifecycle
11. Freeze Trigger Design
12. Delta Engine
13. Temporal Analytics Engine
14. Stability Metrics
15. Explainability & Event Correlation
16. Backend API Surface
17. React Component Tree & Routes
18. UX Wireframes (ASCII)
19. Performance & Caching Strategy
20. AI Integration Points
21. Planner Collaboration Workflow
22. Example Queries
23. Example API Payloads
24. Phase 1 Code Scope
25. Phase Plan & Roadmap
26. Test-Drive (theBicycle)
27. Glossary

---

1. Overview & Goals

The APS already supports planning scenarios via the Pipeline concept (config_scenario_pipeline, references series/causal/supply/MEIO scenarios). Every run of a pipeline drops fact rows into ClickHouse PIPE_* tables, overwriting prior results for the same (pipeline_id, scenario_id) partition.

This platform extends the pipeline concept along a new temporal axis: every run produces an immutable, versioned snapshot that is retained indefinitely. Planners can:

• replay any past run exactly as it was
• compare any two versions of the same pipeline (Mon vs Tue vs Wed)
• compare different pipelines at the same point in time
• compare plan vs actual for any historical projection horizon
• audit which AI/system recommendations were approved/rejected/modified by whom and when
• analyze plan stability (nervousness), bias, forecast accuracy evolution
• train auto-approval rules from historical planner decisions

The platform is built directly inside the existing APS (FastAPI + React + Postgres + ClickHouse). It is not a separate BI tool.

---

2. Core Concepts & Vocabulary

Concept	Definition
Pipeline	A planning scenario container. Existing: config_scenario_pipeline row. Identified by pipeline_id. Mutable name/description.
Pipeline Version	An immutable snapshot of one pipeline run. Identified by surrogate pipeline_version_id (Int64). Carries pipeline_id, bucket_start, bucket_period, created_at, created_by, comment, frozen, parent_version_id.
Live Version	The currently-running, mutable state. Always pipeline_version_id = 0 per pipeline_id. Every pipeline run overwrites this.
Frozen Version	Any pipeline_version_id > 0. Append-only, never mutated after creation.
Derived Pipeline	A new config_scenario_pipeline row that inherits from another via parent_pipeline_id + param_overrides. Different from a version — derived pipelines are new mutable pipelines, versions are frozen copies of existing runs.
Snapshot	Synonymous with Frozen Version. The act of "snapshotting" a pipeline = the freeze operation.
Bucket	A time window during which only one snapshot per pipeline is retained. Defined by snapshot_period (week/month/quarter) and bucket_start (Monday for week, first-of-month for month, etc.). Re-freezing inside the same bucket replaces the prior snapshot in that bucket.
Planning Time	The future bucket date a forecast/plan refers to (e.g. demand for 2026-W40). Stored as forecast_week, week_date, arrival_week, etc.
Execution Time	When the snapshot was created (pipeline_version.created_at).
Actual	Realized outcome data for a planning bucket once it becomes past. Stored in master_*_actuals PG tables (not pipeline-scoped).
Delta	Computed row-level or aggregate diff between two versions / versions+actual / pipelines.
Explanation	Ranked attribution of a KPI change to underlying drivers (lead time shift, forecast uplift, policy change, planner override).
Recommendation	A system-or-AI-generated suggested change to plan state (forecast adjustment, order quantity, IO override, etc.).
Approval	Planner decision on a Recommendation: approve / reject / modify, with remark + timestamp.

---

3. Architectural Principles

3.1 Immutability after freeze

Once pipeline_version.frozen = 1, no row in any CH partition tagged with that pipeline_version_id may be mutated. This is enforced at the application layer; CH has no read-only flag, so writers must consult a guard before any INSERT/ALTER/DELETE on a non-zero version_id partition.

3.2 Live = version 0 ALWAYS

pipeline_version_id = 0 is reserved for the current/working state of a pipeline. All existing writers (orchestrator, supply_runner, MEIO, etc.) continue to write to version 0 unchanged after migration — the only adaptation is they must include pipeline_version_id in their row payloads (or rely on column default = 0). DROP PARTITION calls must pass all three partition values now.

3.3 Frozen versions never auto-delete

Retention is manual or scheduled by an explicit cleanup job, never on overwrite. The one exception: same-bucket re-freeze deletes the prior snapshot in that same bucket — but this is intentional, user-initiated, and audit-logged.

3.4 Temporal dimensions are orthogonal

Every fact row carries: - pipeline_id — which pipeline - pipeline_version_id — which snapshot of that pipeline - scenario_id — which scenario branch inside the pipeline (already exists) - planning-time field (forecast_week, arrival_week, week_date, date)

The pipeline_version registry exposes the execution-time field (created_at, bucket_start).

3.5 Plan vs Actual is just a join

forecast_point_values (planned demand) vs master_demand_actuals (actual demand) is a simple LEFT JOIN on (item_id, site_id, forecast_week). No new pipeline_id needed — actuals are pipeline-independent.

For inventory: supply_inventory_projection (planned OH) vs PIPE_on_hand_snapshot (frozen OH at later version) joined on (item_id, site_id, week_date).

3.6 Cross-version queries prune by primary index, not partition

With PARTITION BY (pipeline_id, scenario_id, pipeline_version_id), a query filtering WHERE pipeline_id = 4 AND scenario_id = 4 still prunes to all version partitions of (4,4). The ORDER BY tuple starts with the same three columns so the primary index narrows further. Time-travel queries (KPI evolution across N versions) require reading N partitions but each is small.

3.7 No row-level updates in CH ever

DROP PARTITION + INSERT is the only mutation pattern in PIPE_* tables. For approval/recommendation log we use a separate INTERACT_ table (PG-backed) so row-level updates remain easy.

---

4. Reconciliation With Existing Code

The repo already contains a partial historization layer:

File	What it does today	Path Y2 disposition
files/DDL/hist_schema.sql	21 HIST_* shadow tables partitioned (hist_week_start, pipeline_id).	Retired. Y2 keeps history in the same PIPE_ tables. Existing HIST_ tables remain temporarily as a fallback read source, marked DEPRECATED in DDL header. New writers do not target them.
files/historization/historize.py	Copies PIPE_ rows → HIST_ rows weekly via INSERT ... SELECT.	Replaced by files/historization/freeze.py (new). Old module kept for back-fill of pre-migration history.
files/DDL/ch_schema.sql	22 PIPE_* tables, partition key = (pipeline_id, scenario_id) or (pipeline_id,).	Migrated. New DDL ch_schema_v2.sql rebuilds each PIPE_* with extended partition key.
files/db/db.py:drop_ch_partition	Variadic — accepts 1 or 2 partition values, builds DROP PARTITION X or (X, Y).	Updated to accept 3 values. New table arity registry _PIPE_PARTITION_ARITY consulted to validate caller passed enough values.
32 writer call sites (orchestrator, supply_runner, MEIO, etc.)	Call drop_ch_partition("PIPE_x", pid, sid) and bulk_insert_ch("PIPE_x", rows) where each row has pipeline_id/scenario_id but no version.	All 32 sites updated to pass pipeline_version_id=0 as the trailing value to drop_ch_partition. All bulk_insert_ch row payloads add pipeline_version_id: 0.
POST /api/historization/run, GET /api/historization/status	Trigger old weekly historization.	Deprecated. New POST /api/history/freeze, GET /api/history/versions etc. replace.
files/api/main.py	Reads PIPE_* tables filtered by pipeline_id + scenario_id.	Updated to add AND pipeline_version_id = 0 filter by default (live read). New /history/* and /compare/* endpoints accept explicit pipeline_version_id.

---

5. Historization Strategy (Path Y2)

Decision recap (locked): Add pipeline_version_id Int64 to every PIPE_* table and change PARTITION BY to include it. Live runs always write pipeline_version_id = 0. Freezing copies version 0 → a new version_id.

5.1 Partition key changes

For each PIPE_* table:

Old PARTITION BY	New PARTITION BY
(pipeline_id, scenario_id)	(pipeline_id, scenario_id, pipeline_version_id)
(pipeline_id)	(pipeline_id, pipeline_version_id)

ORDER BY is extended similarly, putting pipeline_version_id immediately after the existing partition columns. This keeps reads filtered by (pipeline_id, scenario_id) partition-prunable; reads filtered by pipeline_version_id add primary-key skip-index pruning.

5.2 Migration mechanism (per table)

CH does not allow ALTER TABLE ... MODIFY PARTITION BY on a populated table. Per-table rebuild:

-- 1. Create v2 next to original
CREATE TABLE PIPE_xxx_v2 (...existing cols..., pipeline_version_id Int64 DEFAULT 0)
ENGINE = MergeTree()
PARTITION BY (pipeline_id, scenario_id, pipeline_version_id)
ORDER BY (pipeline_id, scenario_id, pipeline_version_id, ...);

-- 2. Backfill — existing rows become live (version 0)
INSERT INTO PIPE_xxx_v2 SELECT *, 0 AS pipeline_version_id FROM PIPE_xxx;

-- 3. Atomic rename
RENAME TABLE PIPE_xxx TO PIPE_xxx_v1_archive,
             PIPE_xxx_v2 TO PIPE_xxx;

-- 4. After verification (1-2 weeks): DROP TABLE PIPE_xxx_v1_archive;

Migration script files/DDL/ch_migration_v2.sql ships all 22 rebuilds in one runnable file. Run once per tenant.

5.3 Surrogate version_id allocation

pipeline_version_id is a 64-bit positive integer allocated by:

INSERT INTO master.pipeline_version_index (pipeline_id, bucket_start, ...)
VALUES (...) RETURNING pipeline_version_id;

PG BIGSERIAL ensures uniqueness across tenant. Version 0 is reserved (DEFAULT, never inserted into the registry).

5.4 Same-bucket dedup

The freeze operation:

1. Compute bucket_start from current time + snapshot_period parameter:
2. week → Monday of week of now()
3. month → first day of month
4. quarter → first day of quarter
5. SELECT pipeline_version_id FROM master.pipeline_version_index WHERE pipeline_id=$1 AND bucket_start=$2 AND bucket_period=$3 — if a prior version exists, capture its id as old_vid.
6. If old_vid found:
7. For each of 22 PIPE_* tables: ALTER TABLE PIPE_x DROP PARTITION (pid, sid, old_vid) (or the 2-tuple variant for non-scenario tables).
8. Delete row in master.pipeline_version_index.
9. Allocate new_vid (INSERT into registry).
10. For each PIPE_* table: INSERT INTO PIPE_x SELECT cols..., new_vid AS pipeline_version_id FROM PIPE_x WHERE pipeline_id=pid AND (scenario_id=sid AND) pipeline_version_id = 0.
11. Snapshot on_hand: INSERT INTO PIPE_on_hand_snapshot SELECT ..., new_vid, now() FROM master.on_hand.
12. Update master.pipeline_version_index row → frozen=1, row_count=... per table.

All steps in a single PG transaction (registry side) + CH commands. CH inserts are eventually-consistent; we accept that a concurrent reader during freeze may see partial state for ~1s. Frozen version is published atomically by setting frozen=1 in PG only after all CH INSERTs succeed.

5.5 Failure modes

Failure	Behavior
CH unreachable during freeze	Registry row inserted with frozen=0. Background reconciler retries. Reader UI hides frozen=0 rows by default.
Partial CH INSERT (some tables OK, some failed)	Registry row stays frozen=0. Manual recovery: re-run freeze with same pipeline_version_id (idempotent via DROP PARTITION + INSERT).
Same-bucket dedup mid-flight	Prior old_vid is deleted first; new freeze runs to completion. A failure between delete-old and insert-new = lost prior snapshot. Acceptable per user spec ("replace").

---

6. Data Model — CH

6.1 Existing PIPE_* tables (post-migration)

All 22 existing PIPE_* tables get a new column and partition key. Sample (PIPE_forecast_point_values):

CREATE TABLE PIPE_forecast_point_values (
    pipeline_id          Int64,
    scenario_id          Int64,
    pipeline_version_id  Int64 DEFAULT 0,     -- NEW: 0 = live, >0 = frozen
    item_id              Int64,
    site_id              Int64,
    method               String,
    forecast_week        Date,
    point_forecast       Float64,
    q10                  Nullable(Float64),
    q50                  Nullable(Float64),
    q90                  Nullable(Float64),
    -- denorm + cost (unchanged)
    item_name            String,
    item_type_name       String,
    site_name            String,
    site_type_name       String,
    segment_id           Int32,
    segment_name         String,
    unit_cost            Float64,
    price                Float64,
    best_method          String,
    locked_method        String
)
ENGINE = MergeTree()
PARTITION BY (pipeline_id, scenario_id, pipeline_version_id)
ORDER BY (pipeline_id, scenario_id, pipeline_version_id, item_id, site_id, method, forecast_week)
SETTINGS index_granularity = 8192;

The 22 tables to migrate:

PIPE_demand_corrected            PIPE_indirect_demand
PIPE_series_characteristics      PIPE_series_backtest_metrics
PIPE_backtest_forecast           PIPE_forecast_point_values
PIPE_forecast_hyperparameters    PIPE_causal_forecast
PIPE_maintenance_forecast        PIPE_forecast_netting
PIPE_series_best_methods         PIPE_series_exceptions
PIPE_segment_membership          PIPE_fitted_distributions
PIPE_meio_results                PIPE_meio_group_results
PIPE_abc_results                 PIPE_supply_orders
PIPE_supply_inventory_projection PIPE_supply_pegging
PIPE_supply_exceptions           PIPE_supply_capacity

6.2 New table — PIPE_pipeline_version (registry mirror)

PG-engine table reading master.pipeline_version_index live:

CREATE TABLE PIPE_pipeline_version (
    pipeline_version_id  Int64,
    pipeline_id          Int64,
    scenario_id          Int64,
    bucket_period        String,                -- 'week'|'month'|'quarter'
    bucket_start         Date,
    snapshot_ts          DateTime,
    created_by           String,
    comment              String,
    frozen               UInt8,
    parent_version_id    Nullable(Int64),
    row_count_total      Int64,
    kpi_revenue          Nullable(Float64),
    kpi_inventory_value  Nullable(Float64),
    kpi_fill_rate        Nullable(Float64),
    kpi_shortage_qty     Nullable(Float64),
    kpi_forecast_mase    Nullable(Float64),
    kpi_forecast_bias    Nullable(Float64),
    kpi_nervousness      Nullable(Float64)
)
ENGINE = PostgreSQL('{{PG_HOST}}:{{PG_PORT}}', '{{TENANT}}', 'pipeline_version_index', 'postgres', 'postgres', 'master');

The kpi_* columns are precomputed at freeze time so KPI timelines do not require scanning fact tables. See §13.

6.3 New table — PIPE_on_hand_snapshot

CREATE TABLE PIPE_on_hand_snapshot (
    pipeline_version_id  Int64,
    snapshot_ts          DateTime,
    item_id              Int64,
    site_id              Int64,
    type_id              Nullable(Int64),
    qty                  Float64,
    unit_cost            Nullable(Float64),
    expiry_date          Nullable(Date),
    inventory_value      Float64,
    item_name            String,
    item_type_name       String,
    site_name            String
)
ENGINE = MergeTree()
PARTITION BY pipeline_version_id
ORDER BY (pipeline_version_id, item_id, site_id, type_id)
SETTINGS index_granularity = 8192;

PARTITION BY pipeline_version_id only — on_hand is pipeline-independent and a single snapshot table serves all pipelines. The "which pipeline took this snapshot" association lives in master.pipeline_version_index (one snapshot per freeze regardless of which pipeline drove it; if 3 pipelines freeze in the same minute we keep 3 rows since each carries its own version_id).

6.4 New tables — actuals (MergeTree, append-only)

All four pure-CH MergeTree, partitioned monthly by date.

CREATE TABLE PIPE_lead_time_actuals (
    item_id            Int64,
    site_id            Int64,
    source_site_id     Nullable(Int64),
    route_id           Nullable(Int64),
    order_id           Nullable(Int64),
    planned_lead_time  Float64,
    actual_lead_time   Float64,
    lead_time_delta    Float64,                -- actual - planned
    as_of_date         Date,
    loaded_at          DateTime DEFAULT now(),
    item_name          String,
    site_name          String,
    source_site_name   String
)
ENGINE = ReplacingMergeTree(loaded_at)
PARTITION BY toYYYYMM(as_of_date)
ORDER BY (item_id, site_id, source_site_id, as_of_date);

CREATE TABLE PIPE_service_actuals (
    item_id              Int64,
    site_id              Int64,
    week_date            Date,
    planned_fill_rate    Nullable(Float64),
    actual_fill_rate     Float64,
    planned_otif         Nullable(Float64),
    actual_otif          Float64,
    shortage_qty         Float64,
    backlog_qty          Float64,
    loaded_at            DateTime DEFAULT now(),
    item_name            String,
    site_name            String
)
ENGINE = ReplacingMergeTree(loaded_at)
PARTITION BY toYYYYMM(week_date)
ORDER BY (item_id, site_id, week_date);

CREATE TABLE PIPE_order_arrival_actuals (
    order_id              Int64,
    item_id               Int64,
    site_id               Int64,
    source_site_id        Nullable(Int64),
    planned_arrival_week  Int32,
    planned_arrival_date  Date,
    actual_arrival_date   Date,
    arrival_delta_days    Int32,
    qty_planned           Float64,
    qty_received          Float64,
    qty_delta             Float64,
    loaded_at             DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(loaded_at)
PARTITION BY toYYYYMM(planned_arrival_date)
ORDER BY (order_id);

MASTER_demand_actuals already exists (PG-engine). No new table for demand.

6.5 New tables — recommendation approval log (PG-engine in CH)

PG-side tables backing these are described in §7. CH side reads them via PG-engine for analytic queries; PG side stays mutable.

CREATE TABLE INTERACT_recommendation_approval (
    id                   Int64,
    pipeline_id          Int64,
    pipeline_version_id  Nullable(Int64),
    recommendation_type  String,
    recommendation_id    String,
    source               String,         -- 'ai'|'rule'|'system'|'planner'
    source_detail        Nullable(String),
    item_id              Nullable(Int64),
    site_id              Nullable(Int64),
    recommended_payload  String,         -- JSON
    approved_payload     Nullable(String),
    decision             String,         -- 'approve'|'reject'|'modify'|'auto'
    approver             String,
    decided_at           DateTime,
    remark               Nullable(String),
    features_at_decision Nullable(String),  -- JSON: ABC class, segment, accuracy, etc.
    auto_rule_id         Nullable(Int64)
)
ENGINE = PostgreSQL('{{PG_HOST}}:{{PG_PORT}}', '{{TENANT}}', 'interact_recommendation_approval', 'postgres', 'postgres', 'scenario');

CREATE TABLE INTERACT_auto_approval_rule (
    id                   Int64,
    name                 String,
    recommendation_type  String,
    condition_expr       String,         -- DSL or JSON predicate
    action               String,         -- 'auto_approve'|'auto_reject'|'auto_modify'
    action_payload       Nullable(String),
    scope_segments       Array(Int32),
    scope_pipelines      Array(Int64),
    is_active            UInt8,
    priority             Int32,
    created_by           String,
    created_at           DateTime,
    last_evaluated_at    Nullable(DateTime),
    fire_count           Int64,
    success_count        Int64
)
ENGINE = PostgreSQL('{{PG_HOST}}:{{PG_PORT}}', '{{TENANT}}', 'interact_auto_approval_rule', 'postgres', 'postgres', 'scenario');

6.6 New table — PIPE_planning_event

For event correlation (§15):

CREATE TABLE PIPE_planning_event (
    event_id           Int64,
    pipeline_id        Nullable(Int64),
    event_type         String,           -- 'supplier_disruption'|'demand_spike'|...
    event_date         Date,
    item_id            Nullable(Int64),
    site_id            Nullable(Int64),
    severity           String,
    description        String,
    payload            String,           -- JSON
    created_by         String,
    created_at         DateTime
)
ENGINE = PostgreSQL('{{PG_HOST}}:{{PG_PORT}}', '{{TENANT}}', 'interact_planning_event', 'postgres', 'postgres', 'scenario');

6.7 New table — PIPE_version_delta_cache (pre-aggregated diffs)

CREATE TABLE PIPE_version_delta_cache (
    base_version_id      Int64,
    target_version_id    Int64,
    delta_dimension      String,       -- 'kpi'|'forecast'|'order'|'inventory'
    metric_name          String,
    item_id              Nullable(Int64),
    site_id              Nullable(Int64),
    base_value           Float64,
    target_value         Float64,
    abs_delta            Float64,
    pct_delta            Float64,
    computed_at          DateTime DEFAULT now()
)
ENGINE = MergeTree()
PARTITION BY (base_version_id, target_version_id)
ORDER BY (base_version_id, target_version_id, delta_dimension, metric_name, item_id, site_id);

Populated lazily (compute-on-first-request) and on a background job for the most-recent version against the version just before it.

---

7. Data Model — PostgreSQL

7.1 master.pipeline_version_index

CREATE TABLE master.pipeline_version_index (
    pipeline_version_id   BIGSERIAL PRIMARY KEY,
    pipeline_id           BIGINT NOT NULL REFERENCES scenario.config_scenario_pipeline(pipeline_id),
    scenario_id           BIGINT,
    bucket_period         TEXT NOT NULL CHECK (bucket_period IN ('week','month','quarter','ad_hoc')),
    bucket_start          DATE NOT NULL,
    snapshot_ts           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by            TEXT NOT NULL,
    comment               TEXT,
    frozen                BOOLEAN NOT NULL DEFAULT FALSE,
    parent_version_id     BIGINT REFERENCES master.pipeline_version_index(pipeline_version_id),
    row_count_total       BIGINT,
    table_counts          JSONB,          -- {"PIPE_forecast_point_values": 32500, ...}
    -- precomputed KPI snapshot for fast timeline rendering
    kpi_revenue           DOUBLE PRECISION,
    kpi_inventory_value   DOUBLE PRECISION,
    kpi_fill_rate         DOUBLE PRECISION,
    kpi_shortage_qty      DOUBLE PRECISION,
    kpi_forecast_mase     DOUBLE PRECISION,
    kpi_forecast_bias     DOUBLE PRECISION,
    kpi_nervousness       DOUBLE PRECISION,
    kpi_supply_churn_pct  DOUBLE PRECISION,
    kpi_capacity_util     DOUBLE PRECISION,
    -- audit
    bucket_dedup_replaced_version_id BIGINT
);

-- One snapshot per (pipeline, bucket_period, bucket_start)
CREATE UNIQUE INDEX idx_pvi_bucket ON master.pipeline_version_index
    (pipeline_id, bucket_period, bucket_start)
    WHERE frozen = TRUE AND bucket_period <> 'ad_hoc';

CREATE INDEX idx_pvi_pipeline_time ON master.pipeline_version_index
    (pipeline_id, snapshot_ts DESC);

CREATE INDEX idx_pvi_parent ON master.pipeline_version_index (parent_version_id);

The unique index enforces the "one snapshot per bucket" rule at DB level. ad_hoc (user-clicked freeze outside scheduled cadence) is exempt — multiple ad-hoc snapshots per bucket allowed.

7.2 New PG actuals tables

These are mirrored to CH via PIPE_*_actuals MergeTree (§6.4) by an ETL/ingestion step that writes both sides (or PG-only with PG-engine read from CH for dimensional joins).

Decision for Phase 1: native CH tables for fast analytic reads; no PG mirror. Ingestion API endpoint writes directly to CH. PG keeps no copy.

7.3 PG interact_recommendation_approval

CREATE TABLE scenario.interact_recommendation_approval (
    id                    BIGSERIAL PRIMARY KEY,
    pipeline_id           BIGINT NOT NULL,
    pipeline_version_id   BIGINT,                 -- NULL = decision on live state
    recommendation_type   TEXT NOT NULL,          -- enum below
    recommendation_id     TEXT NOT NULL,          -- FK-like, scoped by type
    source                TEXT NOT NULL,          -- 'ai'|'rule'|'system'|'planner'
    source_detail         TEXT,                   -- e.g. 'gpt4-rec-engine v3'|'auto_rule:42'
    item_id               BIGINT,
    site_id               BIGINT,
    recommended_payload   JSONB NOT NULL,         -- original recommendation
    approved_payload      JSONB,                  -- planner-modified version (null if approve-as-is or reject)
    decision              TEXT NOT NULL CHECK (decision IN ('approve','reject','modify','auto')),
    approver              TEXT NOT NULL,
    decided_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    remark                TEXT,
    features_at_decision  JSONB,                  -- snapshot of features available to ML at this moment
    auto_rule_id          BIGINT REFERENCES scenario.interact_auto_approval_rule(id),
    duration_ms           INTEGER                 -- time-to-decision (UI captures from rec shown → click)
);

CREATE INDEX idx_rec_appr_type        ON scenario.interact_recommendation_approval (recommendation_type, decided_at DESC);
CREATE INDEX idx_rec_appr_pipeline    ON scenario.interact_recommendation_approval (pipeline_id, decided_at DESC);
CREATE INDEX idx_rec_appr_item_site   ON scenario.interact_recommendation_approval (item_id, site_id);
CREATE INDEX idx_rec_appr_approver    ON scenario.interact_recommendation_approval (approver, decided_at DESC);
CREATE INDEX idx_rec_appr_source      ON scenario.interact_recommendation_approval (source, source_detail);

recommendation_type enum (8 covered in Phase 1):

Type	recommendation_id format	Example use
forecast_adjustment	<unique_id>:<forecast_date>	AI suggests +10% uplift for series X in W42
supply_order	<order_id>	Engine plans order of qty 500 for item A site B week W30
io_override	<pipeline_id>:<item_id>:<site_id>:<override_kind>	Engine recommends raising SL from 90% → 95%
meio_param	<scenario_id>:<segment_id>:<param_key>	Recommend changing default_repair_yield from 0.85 → 0.90
exception_action	<exc_id>	Recommend snoozing exception X until 2026-06-01
alert_action	<alert_id>	Recommend escalating alert Y to supply manager
supersession	<item_id>:<replacement_item_id>	NPI: replace item 18 with item 119 starting W40
npi	<npi_id>	NPI: introduce item 119 in W36 with seed forecast

7.4 PG interact_auto_approval_rule

CREATE TABLE scenario.interact_auto_approval_rule (
    id                    BIGSERIAL PRIMARY KEY,
    name                  TEXT NOT NULL,
    description           TEXT,
    recommendation_type   TEXT NOT NULL,
    -- Condition tree: stored as JSON predicate AST (AND/OR/comparisons)
    -- Example: {"and":[{"<=":["abs_pct_delta",5]},{"in":["abc_class",["C"]]}]}
    condition_expr        JSONB NOT NULL,
    action                TEXT NOT NULL CHECK (action IN ('auto_approve','auto_reject','auto_modify')),
    action_payload        JSONB,                 -- for auto_modify
    scope_segments        INTEGER[] DEFAULT '{}',
    scope_pipelines       BIGINT[]  DEFAULT '{}',
    is_active             BOOLEAN NOT NULL DEFAULT FALSE,
    priority              INTEGER NOT NULL DEFAULT 100,
    created_by            TEXT NOT NULL,
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at            TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_evaluated_at     TIMESTAMPTZ,
    fire_count            BIGINT NOT NULL DEFAULT 0,
    success_count         BIGINT NOT NULL DEFAULT 0,    -- # times planner did not later reverse
    rollback_count        BIGINT NOT NULL DEFAULT 0
);

CREATE INDEX idx_aar_type_active ON scenario.interact_auto_approval_rule (recommendation_type, is_active);

Rule evaluation order: highest priority first; first matching rule fires; remaining are skipped.

Condition DSL (Phase 1 — JSON predicate AST):

{
  "and": [
    {">=": ["abc_class_rank", 3]},
    {"<=": ["abs_pct_delta", 5]},
    {"==": ["segment_id", 1]},
    {"in": ["source", ["ai","rule"]]}
  ]
}

Available variables: abs_delta, pct_delta, abs_pct_delta, abc_class, abc_class_rank (A=1,B=2,C=3), segment_id, item_id, site_id, source, recent_accuracy_mase, recent_bias, recent_stability, recommendation_type, plus all keys in features_at_decision.

7.5 PG interact_planning_event

CREATE TABLE scenario.interact_planning_event (
    event_id      BIGSERIAL PRIMARY KEY,
    pipeline_id   BIGINT,
    event_type    TEXT NOT NULL,         -- supplier_disruption|demand_spike|transport_issue|planner_override|policy_change|ai_recommendation|approval|disruption_resolved
    event_date    DATE NOT NULL,
    item_id       BIGINT,
    site_id       BIGINT,
    severity      TEXT,                  -- 'info'|'warn'|'critical'
    description   TEXT NOT NULL,
    payload       JSONB,
    created_by    TEXT NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_event_date ON scenario.interact_planning_event (event_date, event_type);
CREATE INDEX idx_event_pipeline ON scenario.interact_planning_event (pipeline_id, event_date);

7.6 PG scenario.config_scenario_pipeline extension

ALTER TABLE scenario.config_scenario_pipeline
    ADD COLUMN IF NOT EXISTS parent_pipeline_id BIGINT REFERENCES scenario.config_scenario_pipeline(pipeline_id),
    ADD COLUMN IF NOT EXISTS branch_overrides JSONB,
    ADD COLUMN IF NOT EXISTS freeze_on_run BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS freeze_period TEXT,                    -- override system param per-pipeline
    ADD COLUMN IF NOT EXISTS scheduled_freeze_cron TEXT;            -- per-pipeline cron override

CREATE INDEX IF NOT EXISTS idx_csp_parent ON scenario.config_scenario_pipeline (parent_pipeline_id);

parent_pipeline_id builds the lineage tree (Scenario Tree View, §17). Distinguishes from version lineage which lives in master.pipeline_version_index.parent_version_id.

7.7 New system parameter

config_parameters gets a new parameter_type = 'history' row:

{
  "snapshot_period": "week",                    // 'week'|'month'|'quarter'|'none'
  "scheduled_freeze_enabled": true,
  "scheduled_freeze_cron": "0 2 * * 1",         // Monday 02:00 — default
  "retention_versions": 52,                     // keep last N versions per pipeline
  "retention_min_age_days": 0,                  // never delete frozen newer than N days
  "auto_compute_kpis_on_freeze": true,
  "delta_cache_lookback_versions": 3,           // precompute deltas vs N most recent
  "approval_features_attach": true              // populate features_at_decision automatically
}

Added to files/api/main.py:PARAMETER_REGISTRY.

---

8. Plan vs Actual Tables

8.1 Demand

Already wired. MASTER_demand_actuals exists. P-v-A query:

SELECT
    fp.item_id, fp.site_id, fp.forecast_week,
    fp.point_forecast AS planned_qty,
    coalesce(da.qty, 0)   AS actual_qty,
    coalesce(da.qty, 0) - fp.point_forecast AS deviation
FROM PIPE_forecast_point_values fp
LEFT JOIN MASTER_demand_actuals da
    ON da.item_id = fp.item_id
   AND da.site_id = fp.site_id
   AND da.date    = fp.forecast_week
WHERE fp.pipeline_version_id = {version_id}
  AND fp.method = fp.best_method

8.2 Inventory

Compare projected inventory at a planning bucket to the frozen on-hand snapshot taken AT that bucket (post-bucket).

SELECT
    p.item_id, p.site_id, p.week_date,
    p.projected_inventory AS planned_oh,
    o.qty                 AS actual_oh,
    o.qty - p.projected_inventory AS deviation
FROM PIPE_supply_inventory_projection p
JOIN PIPE_on_hand_snapshot o
    ON o.pipeline_version_id = {snapshot_after_bucket_version_id}
   AND o.item_id = p.item_id
   AND o.site_id = p.site_id
WHERE p.pipeline_version_id = {planning_version_id}
  AND p.week_date = {bucket_planning_date}

The "snapshot_after_bucket" is the first frozen version with bucket_start > planning_date. Endpoint logic resolves it.

8.3 Lead time

SELECT
    a.item_id, a.site_id, a.source_site_id,
    a.planned_lead_time, a.actual_lead_time,
    a.lead_time_delta,
    avg(a.lead_time_delta) OVER (PARTITION BY a.item_id, a.site_id ORDER BY a.as_of_date ROWS 12 PRECEDING) AS trailing_12_avg
FROM PIPE_lead_time_actuals a
WHERE a.as_of_date BETWEEN {from} AND {to};

8.4 Service

SELECT
    s.item_id, s.site_id, s.week_date,
    s.planned_fill_rate, s.actual_fill_rate,
    s.planned_otif,      s.actual_otif,
    s.shortage_qty
FROM PIPE_service_actuals s
WHERE s.week_date BETWEEN {from} AND {to}
  AND s.item_id = {item};

8.5 Order arrivals

SELECT
    a.order_id, a.item_id, a.site_id,
    a.planned_arrival_date, a.actual_arrival_date,
    a.arrival_delta_days,
    a.qty_planned, a.qty_received, a.qty_delta
FROM PIPE_order_arrival_actuals a
WHERE a.planned_arrival_date BETWEEN {from} AND {to};

---

9. Recommendation Approval & Auto-Approval

9.1 Capture flow

Every existing API write path that originates from a recommendation must log to interact_recommendation_approval before applying the change. Hook points:

Endpoint	Recommendation type	Hook location
POST /api/forecast/adjustments	forecast_adjustment	files/api/main.py adjustments handler
POST /api/supply/orders/approve	supply_order	files/api/main.py order approve handler
PUT /api/supply/io-overrides/{id}	io_override	files/api/main.py io override handler
PUT /api/meio/parameter-sets/{id}	meio_param	files/api/main.py MEIO param handler
POST /api/exceptions/{id}/action	exception_action	files/api/main.py exception action handler
POST /api/alerts/{id}/action	alert_action	files/api/main.py alert action handler
POST /api/npi/supersessions	supersession	files/api/main.py NPI handler
POST /api/npi/items	npi	files/api/main.py NPI handler

Each hook constructs a payload:

log_recommendation_decision(
    pipeline_id        = ctx.pipeline_id,
    pipeline_version_id= None,                  # live decision
    recommendation_type= "supply_order",
    recommendation_id  = str(order_id),
    source             = "system",              # 'ai' if AI engine produced rec, else 'system'/'planner'
    source_detail      = "supply_runner v1.4",
    item_id            = order.item_id,
    site_id            = order.site_id,
    recommended_payload= {"qty": rec_qty, "release_week": rec_rw, "arrival_week": rec_aw},
    approved_payload   = {"qty": approved_qty, "release_week": approved_rw, "arrival_week": approved_aw},
    decision           = "modify" if approved_qty != rec_qty else "approve",
    approver           = user.email,
    remark             = body.remark,
    features_at_decision = collect_features(item_id, site_id, pipeline_id),
    duration_ms        = body.ui_decision_ms,
)

collect_features() reads from live PIPE_* tables: ABC class, segment, recent backtest accuracy, recent bias, recent stability score, current on_hand, etc.

9.2 Auto-approval evaluator

New module files/historization/auto_approval.py:

def evaluate_auto_rules(rec_type: str, payload: dict, ctx: dict) -> tuple[str, dict, int|None]:
    """
    Returns (decision, modified_payload, fired_rule_id) or ('manual', None, None) if no rule fires.
    decision in {'auto_approve','auto_reject','auto_modify','manual'}
    """
    rules = load_active_rules(rec_type)
    for rule in sorted(rules, key=lambda r: -r.priority):
        if evaluate_predicate(rule.condition_expr, ctx, payload):
            return (rule.action, rule.action_payload, rule.id)
    return ('manual', None, None)

evaluate_predicate() recursively walks the JSON AST. Operators: and, or, not, ==, !=, <, <=, >, >=, in. Atoms are either constants or variable references (looked up in ctx + payload).

Wired into every endpoint listed in §9.1 BEFORE the existing approval logic. If auto_* returned, decision recorded with source='rule' and auto_rule_id set; planner never sees the prompt.

9.3 Auto-rule analytics

The UI surfaces three feedback loops:

1. Acceptance rate — success_count / fire_count per rule (success = decision not later reversed by planner via override).
2. Mining suggestions — query interact_recommendation_approval for high-frequency (recommendation_type, decision) patterns where decision='approve' with no payload modification and abs_pct_delta low → propose new auto-rules.
3. Rollback alerts — when planner manually overrides a recently auto-approved item, increment rollback_count and surface in admin UI.

---

10. Snapshot Lifecycle

┌──────────┐  run completes  ┌───────────────┐  freeze_trigger? ┌──────────────┐
│ live v=0 ├────────────────▶│ live v=0 +    ├─────────────────▶│ frozen v=N   │
│ (mutable)│   (overwrite)   │ candidate     │     yes          │ (immutable)  │
└──────────┘                 │ for freeze    │                  └──────┬───────┘
                             └───────────────┘                         │
                                                              same-bucket re-freeze
                                                                        │
                                                                        ▼
                                                              ┌──────────────────┐
                                                              │ DROP partition   │
                                                              │ v=N for all      │
                                                              │ tables, register │
                                                              │ deleted_for=N+1  │
                                                              └──────────────────┘

Retention: scheduled job deletes frozen versions older than retention_min_age_days
           when count exceeds retention_versions.

10.1 States

State	Storage	Mutable?	Read default?
live (v=0)	PIPE_* with pipeline_version_id=0	yes (overwrite per run)	yes (default reader filter)
frozen draft	PIPE_* with v>0 AND pipeline_version_index.frozen=FALSE	no (in-flight publish)	no (hidden until frozen=TRUE)
frozen published	PIPE_* with v>0 AND frozen=TRUE	no	only via explicit version_id
deleted	gone	n/a	n/a

10.2 Retention

System parameter history.retention_versions (default 52) + history.retention_min_age_days (default 0). Cleanup job runs daily:

WITH ranked AS (
    SELECT pipeline_version_id, pipeline_id, snapshot_ts,
           ROW_NUMBER() OVER (PARTITION BY pipeline_id ORDER BY snapshot_ts DESC) AS rn
    FROM master.pipeline_version_index
    WHERE frozen = TRUE
)
SELECT pipeline_version_id, pipeline_id
FROM ranked
WHERE rn > {retention_versions}
  AND snapshot_ts < NOW() - INTERVAL '{retention_min_age_days} days';

For each candidate: ALTER TABLE PIPE_x DROP PARTITION (pid, sid, vid) × 22 tables + delete pipeline_version_index row.

10.3 Pinned versions

pipeline_version_index.pinned BOOLEAN DEFAULT FALSE — pinned versions are skipped by retention. UI shows a pin toggle per version. Used for "important" snapshots (post-disruption recovery plan, annual audit baseline).

---

11. Freeze Trigger Design

11.1 Three triggers

Trigger	Source	Bucket	Idempotent?
Manual	POST /api/history/freeze	ad_hoc	No (always creates new version)
Workflow flag	Workflow step's freeze_on_success: true	inferred from system param	Yes (same-bucket replace)
Scheduled cron	New daemon files/historization/scheduler.py	system param	Yes

11.2 Workflow integration

Workflows define a list of pipeline runs to execute sequentially. Adding to the workflow step definition:

{
  "step_id": "run_baseline",
  "pipeline_id": 4,
  "scenario_id": 4,
  "freeze_on_success": true,
  "freeze_period": "week",
  "freeze_comment": "{{workflow_name}} — auto-freeze"
}

When the step completes successfully, the orchestrator calls freeze_pipeline(pipeline_id, period=freeze_period, comment=...).

11.3 Scheduled daemon

files/historization/scheduler.py runs as a separate background process (or async task in API startup). Reads cron expression from history.scheduled_freeze_cron (default 0 2 * * 1 = Monday 02:00). For each cron firing:

1. Lookup all pipelines with freeze_on_schedule=TRUE in config_scenario_pipeline.
2. For each, call freeze_pipeline(pid, period=resolve_period(pid)).
3. Log to pipe_process_log with step_name='freeze'.

A poor-man's start: cron via Windows Task Scheduler / system cron invoking python -m historization.scheduler once per period. No daemon needed initially.

11.4 Manual freeze API

POST /api/history/freeze
Body: {
  "pipeline_id": 4,
  "scenario_id": 4,
  "comment": "post-S&OP review baseline",
  "period": "ad_hoc",          // optional, default to system param
  "pin": true                  // optional
}
Response: {
  "pipeline_version_id": 1234,
  "bucket_start": "2026-05-11",
  "bucket_period": "ad_hoc",
  "snapshot_ts": "2026-05-15T10:32:11Z",
  "table_counts": {...},
  "duration_ms": 4521
}

---

12. Delta Engine

12.1 Row-level diff

Generic structural diff for any (table, base_version, target_version) tuple:

-- ADDED rows (in target, not in base)
SELECT t.* FROM PIPE_x t
LEFT JOIN PIPE_x b
    USING (pipeline_id, scenario_id, {row_key})
WHERE t.pipeline_version_id = {target}
  AND b.pipeline_version_id = {base}
  AND b.{first_col} IS NULL;

-- REMOVED rows (in base, not in target)
SELECT b.* FROM PIPE_x b
LEFT JOIN PIPE_x t
    USING (pipeline_id, scenario_id, {row_key})
WHERE b.pipeline_version_id = {base}
  AND t.pipeline_version_id = {target}
  AND t.{first_col} IS NULL;

-- CHANGED rows (in both, value differs)
SELECT t.*, b.* AS base_*
FROM PIPE_x t
INNER JOIN PIPE_x b USING (pipeline_id, scenario_id, {row_key})
WHERE t.pipeline_version_id = {target}
  AND b.pipeline_version_id = {base}
  AND ({any_value_col_differs});

{row_key} per table is defined in a Python registry. For PIPE_supply_orders: (id). For PIPE_forecast_point_values: (item_id, site_id, method, forecast_week).

12.2 Aggregate diff (KPI)

KPI deltas precomputed at freeze time and stored in pipeline_version_index.kpi_*. Per-(base, target) deltas computed on-demand and cached in PIPE_version_delta_cache.

KPI computation queries at freeze time:

-- kpi_inventory_value
SELECT round(sum(unit_cost * projected_inventory), 2)
FROM PIPE_supply_inventory_projection
WHERE pipeline_id = $1 AND scenario_id = $2 AND pipeline_version_id = $3
  AND week = 0;

-- kpi_fill_rate
SELECT round(avg(fill_rate), 4)
FROM PIPE_meio_results
WHERE pipeline_id = $1 AND scenario_id = $2 AND pipeline_version_id = $3;

-- kpi_shortage_qty
SELECT round(sum(shortage), 2)
FROM PIPE_supply_inventory_projection
WHERE pipeline_id = $1 AND scenario_id = $2 AND pipeline_version_id = $3;

-- kpi_forecast_mase
SELECT round(avg(avg_mase), 4)
FROM PIPE_series_backtest_metrics
WHERE pipeline_id = $1 AND scenario_id = $2 AND pipeline_version_id = $3
  AND forecast_origin = '__AGG__'
  AND method = best_method;

-- kpi_nervousness (see §14)
-- requires prior version → computed lazily after freeze; null on first ever freeze.

12.3 Delta dimensions exposed by the API

Dimension	Tables involved	Key
kpi	pipeline_version_index	metric_name
forecast	PIPE_forecast_point_values	(item_id, site_id, forecast_week)
order	PIPE_supply_orders	(id)
inventory	PIPE_supply_inventory_projection	(item_id, site_id, week)
meio_buffer	PIPE_meio_results	(item_id, site_id)
exceptions	PIPE_series_exceptions, PIPE_supply_exceptions	(exc_id)
best_method	PIPE_series_best_methods	(item_id, site_id)

12.4 Streaming delta for large tables

PIPE_supply_inventory_projection can hit 10M+ rows for big tenants. Delta computation streams via cursor + paginated chunks (chunk_size=100k rows). Frontend Delta Explorer uses virtualized table; backend paginates with LIMIT/OFFSET plus a row-checksum hash for cursor stability.

---

13. Temporal Analytics Engine

13.1 KPI time series

SELECT
    pipeline_version_id, snapshot_ts, bucket_start,
    kpi_revenue, kpi_inventory_value, kpi_fill_rate,
    kpi_shortage_qty, kpi_forecast_mase, kpi_forecast_bias,
    kpi_nervousness, kpi_supply_churn_pct, kpi_capacity_util
FROM PIPE_pipeline_version
WHERE pipeline_id = {pid} AND frozen = 1
ORDER BY snapshot_ts;

Read directly through PG-engine table — no scan of fact tables needed. Sub-50ms for any pipeline with ≤ 200 versions.

13.2 KPI-by-segment time series

SELECT
    pv.snapshot_ts,
    mr.segment_name,
    round(avg(mr.fill_rate), 4) AS fill_rate
FROM PIPE_meio_results mr
JOIN PIPE_pipeline_version pv USING (pipeline_version_id)
WHERE mr.pipeline_id = {pid} AND mr.scenario_id = {sid}
  AND pv.frozen = 1
  AND pv.snapshot_ts >= now() - INTERVAL 90 DAY
GROUP BY pv.snapshot_ts, mr.segment_name
ORDER BY pv.snapshot_ts;

13.3 Rolling evolution

Window functions on pipeline_version_index:

SELECT
    pipeline_version_id, snapshot_ts,
    kpi_inventory_value,
    avg(kpi_inventory_value) OVER (ORDER BY snapshot_ts ROWS 4 PRECEDING) AS rolling_5_avg,
    stddev(kpi_inventory_value) OVER (ORDER BY snapshot_ts ROWS 8 PRECEDING) AS rolling_9_std,
    kpi_inventory_value - lag(kpi_inventory_value) OVER (ORDER BY snapshot_ts) AS delta_vs_prev
FROM master.pipeline_version_index
WHERE pipeline_id = {pid} AND frozen = TRUE
ORDER BY snapshot_ts;

13.4 Plan vs Actual accuracy evolution

SELECT
    pv.snapshot_ts,
    avg(abs(fp.point_forecast - coalesce(da.qty,0)) / nullif(fp.point_forecast, 0)) AS mape,
    avg(fp.point_forecast - coalesce(da.qty,0)) AS bias
FROM PIPE_forecast_point_values fp
JOIN PIPE_pipeline_version pv USING (pipeline_version_id)
LEFT JOIN MASTER_demand_actuals da
    ON da.item_id = fp.item_id AND da.site_id = fp.site_id AND da.date = fp.forecast_week
WHERE fp.pipeline_id = {pid}
  AND pv.frozen = 1
  AND fp.method = fp.best_method
  AND fp.forecast_week BETWEEN pv.snapshot_ts AND pv.snapshot_ts + INTERVAL 28 DAY
GROUP BY pv.snapshot_ts;

---

14. Stability Metrics

14.1 Supply plan churn

Fraction of (item, site, week) supply orders whose quantity changed between consecutive versions:

WITH base AS (
    SELECT item_id, site_id, arrival_week, sum(qty) AS qty
    FROM PIPE_supply_orders
    WHERE pipeline_version_id = {base_vid}
    GROUP BY item_id, site_id, arrival_week
),
tgt AS (
    SELECT item_id, site_id, arrival_week, sum(qty) AS qty
    FROM PIPE_supply_orders
    WHERE pipeline_version_id = {target_vid}
    GROUP BY item_id, site_id, arrival_week
)
SELECT
    sum(abs(coalesce(t.qty,0) - coalesce(b.qty,0))) AS churn_qty,
    sum(abs(coalesce(t.qty,0) - coalesce(b.qty,0))) / nullif(sum(b.qty), 0) AS churn_pct
FROM base b FULL OUTER JOIN tgt t USING (item_id, site_id, arrival_week);

Stored as kpi_supply_churn_pct in registry at freeze (vs prior version).

14.2 Forecast volatility

Standard deviation of point_forecast for the same (item_id, site_id, forecast_week) across the last N frozen versions:

SELECT
    item_id, site_id, forecast_week,
    stddev(point_forecast) AS forecast_volatility,
    avg(point_forecast)    AS forecast_mean,
    stddev(point_forecast) / nullif(avg(point_forecast), 0) AS cov
FROM PIPE_forecast_point_values
WHERE pipeline_id = {pid}
  AND pipeline_version_id IN (
      SELECT pipeline_version_id
      FROM master.pipeline_version_index
      WHERE pipeline_id = {pid} AND frozen = TRUE
      ORDER BY snapshot_ts DESC LIMIT 8
  )
  AND method = best_method
GROUP BY item_id, site_id, forecast_week;

14.3 Order rescheduling frequency (nervousness)

For each (order_id) that exists across multiple versions, count how many times arrival_week or qty changed:

SELECT
    o.id AS order_id,
    count(DISTINCT (o.arrival_week, o.qty)) - 1 AS reschedule_count
FROM PIPE_supply_orders o
JOIN PIPE_pipeline_version pv USING (pipeline_version_id)
WHERE o.pipeline_id = {pid} AND pv.frozen = 1
  AND pv.snapshot_ts >= now() - INTERVAL 90 DAY
GROUP BY o.id
ORDER BY reschedule_count DESC;

Aggregate to pipeline-level: avg(reschedule_count) AS pipeline_nervousness.

14.4 Allocation instability

For each (item, site, week) compare allocation share across versions. Phase 2 — depends on allocation_fact table not yet defined.

14.5 Bias / forecast stability

Trailing rolling-window bias is already in series_backtest_metrics.bias (and avg_bias). Stability metric:

SELECT
    item_id, site_id,
    stddev(avg_bias) AS bias_stability,
    avg(avg_bias)    AS bias_mean
FROM PIPE_series_backtest_metrics
WHERE pipeline_id = {pid}
  AND pipeline_version_id IN (last_n_frozen)
  AND forecast_origin = '__AGG__'
GROUP BY item_id, site_id;

---

15. Explainability & Event Correlation

15.1 Driver attribution

Compare two versions, decompose KPI delta into ranked drivers. Algorithm for kpi_inventory_value delta:

total_inventory_delta = target.kpi_inventory_value - base.kpi_inventory_value

Decomposition:
  d_safety_stock     = sum(delta_safety_stock * unit_cost)       — over all items
  d_lead_time        = sum(delta_planned_lt * avg_demand * unit_cost)
  d_forecast_uplift  = sum(delta_forecast_mean * unit_cost)
  d_policy_change    = derived from io_overrides version comparison
  d_unexplained      = total_delta - sum(d_*)

Each component computed as a separate query against the two versions' fact rows.

15.2 Event correlation

For each KPI shift between versions, list events with event_date between base.snapshot_ts and target.snapshot_ts. Score by:

• proximity in time (closer = higher)
• scope overlap (item/site match)
• severity weight

Return ranked list.

15.3 Output payload

{
  "base_version_id": 1233,
  "target_version_id": 1234,
  "kpi": "kpi_inventory_value",
  "base_value": 1200000,
  "target_value": 1325000,
  "abs_delta": 125000,
  "pct_delta": 10.4,
  "drivers": [
    {"name": "safety_stock_increase", "contribution_pct": 62, "abs_value": 77500, "detail": {...}},
    {"name": "supplier_lead_time_drift", "contribution_pct": 28, "abs_value": 35000, "detail": {...}},
    {"name": "forecast_uplift",        "contribution_pct": 10, "abs_value": 12500, "detail": {...}}
  ],
  "events": [
    {"event_id": 88, "type": "supplier_disruption", "date": "2026-04-12", "severity": "warn", "correlation_score": 0.82}
  ]
}

---

16. Backend API Surface

All routes prefixed /api/. Auth required (existing JWT middleware). Tenant context from JWT.

16.1 Version registry

GET    /api/history/versions
       ?pipeline_id={pid}&scenario_id={sid}&period={week|month|quarter|ad_hoc|all}
       &from={iso_date}&to={iso_date}&frozen_only={bool}&limit=100&offset=0
       → list of versions w/ KPIs

GET    /api/history/versions/{version_id}
       → full version detail (counts, KPIs, parent, comments, events linked)

POST   /api/history/freeze
       Body: {pipeline_id, scenario_id?, comment?, period?, pin?}
       → 201 + version detail

PATCH  /api/history/versions/{version_id}
       Body: {comment?, pin?, tags?}
       → updated version detail

DELETE /api/history/versions/{version_id}
       → 204 (only allowed if !pinned, executes 22-table DROP PARTITION)

16.2 Compare

GET /api/compare/versions?base={vid1}&target={vid2}
    &dimension={kpi|forecast|order|inventory|meio_buffer|exceptions|best_method}
    → delta payload (cached via PIPE_version_delta_cache)

GET /api/compare/pipelines?base_pipeline_id={pid1}&target_pipeline_id={pid2}
    &base_version_id={vid1}?&target_version_id={vid2}?
    → same as above, allows cross-pipeline diff

GET /api/compare/rolling?pipeline_id={pid}&versions={vid1,vid2,vid3,...}
    &dimension={kpi|forecast|order|...}&item_id={iid}?&site_id={sid}?
    → multi-version evolution series

GET /api/compare/plan-vs-actual?pipeline_id={pid}&version_id={vid}
    &dimension={demand|inventory|lead_time|service|order_arrival}
    &item_id?&site_id?&from?&to?
    → planned curve + actual curve + deviation series

16.3 Replay

GET /api/history/replay/{version_id}
    ?view={dashboard|series|meio|supply}&item_id?&site_id?
    → response shaped identically to the live endpoint for that view, but with
      pipeline_version_id implicitly filtered to {version_id}

GET /api/history/replay/{version_id}/series/{unique_id}
    → time series data for one series exactly as it appeared at freeze time

16.4 Timeline

GET /api/history/timeline?pipeline_id={pid}&from?&to?
    → list of timeline entries: versions, events, approvals, kpi jumps, comments
    Sorted by timestamp.

Timeline entry types:

[
  {"ts": "...", "type": "version_frozen", "version_id": 1233, "comment": "...", "kpi_summary": {...}},
  {"ts": "...", "type": "event", "event_id": 88, "event_type": "supplier_disruption", ...},
  {"ts": "...", "type": "approval", "rec_type": "supply_order", "approver": "...", "decision": "modify", ...},
  {"ts": "...", "type": "kpi_jump", "kpi": "kpi_inventory_value", "magnitude": 0.12, "between_versions": [1232,1233]},
  {"ts": "...", "type": "annotation", "user": "...", "text": "..."}
]

16.5 Stability & explainability

GET /api/stability/pipeline/{pid}
    ?window=8&metric={churn|nervousness|forecast_volatility|bias_stability}
    → series of stability metrics by version

GET /api/explain/delta?base={vid1}&target={vid2}&kpi={kpi_name}
    → driver attribution payload (see §15)

GET /api/explain/events?from={iso}&to={iso}&pipeline_id={pid}
    → correlated events for the window

16.6 Pareto

GET /api/pareto/versions?pipeline_id={pid}
    &x_metric={kpi_inventory_value}&y_metric={kpi_fill_rate}
    → scatter of all frozen versions on the (x,y) axes, with dominance flag

16.7 Approvals

GET  /api/approvals
     ?recommendation_type=&from=&to=&approver=&decision=&pipeline_id=&item_id=&site_id=
     → paginated approval log

GET  /api/approvals/{id}
     → full record + features_at_decision

POST /api/approvals/replay
     Body: {approval_id, target_pipeline_id?}
     → re-applies the recommendation to a target pipeline (admin only)

16.8 Auto-approval rules

GET    /api/auto-rules
       ?recommendation_type=&is_active=
       → list of rules with fire_count, success_count, rollback_count

POST   /api/auto-rules
       Body: {name, recommendation_type, condition_expr, action, action_payload?, scope_segments?, scope_pipelines?, priority?}

PUT    /api/auto-rules/{id}
PATCH  /api/auto-rules/{id}/toggle    → flip is_active
DELETE /api/auto-rules/{id}

POST   /api/auto-rules/simulate
       Body: {condition_expr, sample_size?}
       → counts of approvals this rule WOULD have fired on historical data,
         with acceptance/rollback projections

16.9 Events

GET    /api/events?from=&to=&type=&item_id=&site_id=&pipeline_id=
POST   /api/events       Body: {event_type, event_date, item_id?, site_id?, severity, description, payload?, pipeline_id?}
PATCH  /api/events/{id}
DELETE /api/events/{id}

16.10 Annotations & Bookmarks

GET    /api/annotations?version_id=&pipeline_id=
POST   /api/annotations  Body: {version_id?, pipeline_id?, kind, target_ref, text}
DELETE /api/annotations/{id}

GET    /api/bookmarks/comparisons
POST   /api/bookmarks/comparisons Body: {name, base_version_id, target_version_id, dimension?, filters?}

16.11 AI

POST /api/ai/explain    Body: {kind: 'delta'|'evolution'|'planner_decision', context: {...}}
                        → natural-language explanation generated via LLM

POST /api/ai/recommend-similar-scenario
     Body: {pipeline_id, target_kpi: {...}}
     → finds historical version with closest profile + lists differing params

POST /api/ai/predict-stability
     Body: {pipeline_id}
     → predicts likelihood the next run will exhibit high nervousness

Phase 2 — backed by Anthropic API or local Ollama via existing ai parameter type.

---

17. React Component Tree & Routes

17.1 New routes (lazy-loaded)

/history                              → ScenarioHistory.jsx (landing — pipeline explorer)
/history/:pipelineId                  → ScenarioTimeline.jsx
/history/:pipelineId/version/:vid     → SnapshotReplay.jsx
/history/:pipelineId/version/:vid/series/:uniqueId  → SnapshotReplay → TimeSeriesViewer
/history/:pipelineId/compare/:vid1/:vid2            → KpiComparison.jsx
/history/:pipelineId/compare/:vid1/:vid2/delta      → DeltaExplorer.jsx
/history/:pipelineId/pareto                         → ParetoView.jsx
/history/tree                                       → ScenarioTree.jsx
/history/plan-vs-actual                             → PlanVsActual.jsx
/history/stability                                  → StabilityDashboard.jsx
/history/explain/:vid1/:vid2                        → ExplainabilityPanel.jsx
/history/events                                     → EventTimeline.jsx
/history/approvals                                  → ApprovalLog.jsx
/history/auto-rules                                 → AutoRules.jsx

Top-level menu item: Scenario Review (new) — chevron expands to 14 sub-pages.

17.2 Shared hooks

useVersion(versionId)              → {data, isLoading, error}
useVersionList(pipelineId, filters) → list of versions w/ KPIs
useDelta(baseVid, targetVid, dim)  → cached delta payload
useTimeline(pipelineId, range)     → timeline entries
usePlanVsActual(versionId, dim)    → P-v-A payload
useStability(pipelineId, window)   → stability series
useExplain(baseVid, targetVid, kpi) → driver attribution
useApprovals(filters)              → paginated log
useAutoRules()                     → rule list w/ counts
useEvents(range, filters)          → event list

Built on existing utils/api.js axios instance. SWR-style caching on hooks.

17.3 Component map

ScenarioHistory.jsx
├── PipelineCard (per pipeline w/ latest version mini-sparkline)
└── PipelineSearch + Filters

ScenarioTimeline.jsx
├── TimelineHeader (pipeline selector, range, freeze button)
├── TimelineCanvas (vertical git-history-style)
│   ├── VersionNode (KPI delta chips + comment)
│   ├── EventNode (severity-colored)
│   ├── ApprovalNode
│   └── AnnotationNode
└── TimelineSidebar (selected version detail)

SnapshotReplay.jsx
├── VersionContextBar (you are viewing v=N from ts=...)
├── (embeds existing Dashboard.jsx / TimeSeriesViewer.jsx / SupplyPlan etc.
│    via a HistoryContext provider that injects pipeline_version_id into all api calls)
└── BackToLive button

KpiComparison.jsx
├── KpiHeaderCards (base vs target, side-by-side, delta chips)
├── KpiChart (multi-line per KPI over time, two versions highlighted)
└── DrillToDelta button → DeltaExplorer.jsx

DeltaExplorer.jsx
├── DimensionTabs (kpi | forecast | order | inventory | meio_buffer | exceptions | best_method)
├── DeltaTable (virtualized, three sections: added | removed | changed)
├── ChangeBadge (per row: +qty / -qty / Δ%)
└── ExportCSV button

PlanVsActual.jsx
├── DimensionTabs (demand | inventory | lead_time | service | order_arrival)
├── PvAChart (planned curve, actual curve, deviation band)
├── AccuracyKpis (MAPE, WAPE, bias, percentile accuracy)
└── DrillToSeries

StabilityDashboard.jsx
├── PipelinePicker
├── MetricCards (churn, nervousness, forecast volatility, bias stability — all w/ trend arrows)
├── HeatmapByItemSite (which items contribute most to nervousness)
└── RollingTrendChart

ExplainabilityPanel.jsx
├── KpiPicker
├── DriverBarChart (ranked contributions)
├── EventCorrelationList
└── NaturalLanguageBox (AI explanation; Phase 2)

ParetoView.jsx
├── ScatterChart (versions on x/y, dominance highlight)
├── EfficientFrontierOverlay
└── AiManualOverlay (filter versions by source: AI-generated vs planner-edited)

ScenarioTree.jsx
├── LineageGraph (D3 tree, parent_pipeline_id edges)
└── PipelineCard popovers

EventTimeline.jsx
├── EventList (paginated)
├── EventCreateModal
└── EventCorrelationView

ApprovalLog.jsx
├── ApprovalTable (virtualized)
│   ├── Filters: type, approver, decision, source, date, item/site
│   ├── ApprovalRow (recommended payload | approved payload | diff badge)
│   └── ApprovalDetailDrawer
└── ApprovalAnalytics (charts: decision distribution, modify-vs-approve rate, top approvers)

AutoRules.jsx
├── RuleList (toggle active, see fire_count, success_count)
├── RuleEditor (condition builder UI for JSON predicate AST)
├── SimulateButton (counts approvals this rule WOULD HAVE fired on)
└── MiningSuggestions (proposed rules from historical pattern analysis)

17.4 Shared UI primitives

• KpiChip — small "value [Δ% green/red]" pill
• VersionPicker — searchable dropdown w/ KPI sparkline per option
• DeltaBadge — colored pill +12 (+5%)
• VirtualizedDeltaTable — react-window backed
• MiniSparkline — inline SVG (same as Dashboard sparklines)
• HistoryContextProvider — wraps any existing page with {pipeline_version_id, isHistorical} to inject into api calls

17.5 New left-nav entry

Existing nav
├── Dashboard
├── Processes
├── ...
└── Scenario Review (NEW)
    ├── Timeline
    ├── Compare
    ├── Plan vs Actual
    ├── Stability
    ├── Pareto
    ├── Lineage Tree
    ├── Events
    ├── Approvals
    └── Auto-Rules

---

18. UX Wireframes (ASCII)

18.1 Scenario Timeline

┌─────────────────────────────────────────────────────────────────────────────────┐
│ Scenario Review › Default Pipeline (id=4)         [+ Freeze now]  [⚙ Settings] │
├─────────────────────────────────────────────────────────────────────────────────┤
│ Range:  [Last 6 months ▼]  Period: [Week ▼]  KPI: [Inventory Value ▼]          │
│                                                                                 │
│  ●━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━  │
│  │                                                                              │
│  │  2026-05-12  v=1234  ◆ frozen  📌 pinned                                    │
│  │             Inventory $1.32M (+10%↑)  Fill 94.2% (-0.4%↓)  Churn 7%        │
│  │             "Post-S&OP review baseline"           by bruno@ketteq.com       │
│  │             [Replay] [Compare with…] [Delta vs prev]                        │
│  │                                                                              │
│  ●  ────────────────────── ⚡ supplier_disruption (Acme Inc, severity: warn)    │
│  │             "Lead time +14 days starting 2026-05-08"                        │
│  │                                                                              │
│  │  2026-05-05  v=1233  ◆ frozen                                               │
│  │             Inventory $1.20M  Fill 94.6%  Churn 4%                          │
│  │             [Replay] [Compare with v=1234]                                  │
│  │                                                                              │
│  ●  ────────────────────── ✅ Approval (forecast_adj × 18): modify +12%        │
│  │             by sandra@ketteq.com                                            │
│  │                                                                              │
│  │  2026-04-28  v=1232  ◆ frozen                                               │
│  │             Inventory $1.18M  Fill 95.0%  Churn 3%                          │
│  ●━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━  │
└─────────────────────────────────────────────────────────────────────────────────┘

18.2 KPI Comparison

┌─ Compare ────────────────────────────────────────────────────────────────────┐
│ Base: v=1232 (2026-04-28) │  Target: v=1234 (2026-05-12)                    │
├────────────────────────────┼──────────────────────────────────────────────────┤
│  Inventory Value           │  $1.18M  →  $1.32M     +$140K (+12%) ↑          │
│  Fill Rate                 │   95.0%  →  94.2%      -0.8%pt          ↓      │
│  Shortage Qty              │     230  →    520      +290 units (+126%) ↑    │
│  Forecast MASE             │    0.82  →   0.91      +0.09 (+11%) worse      │
│  Forecast Bias             │   -3.2%  →   +1.1%     pivoted (over→under)    │
│  Nervousness (churn %)     │      3%  →     7%      +4pt worse              │
│  Capacity Utilization      │     78%  →    81%      +3pt                    │
├────────────────────────────┴──────────────────────────────────────────────────┤
│ [Drill: Inventory] [Drill: Forecast] [Explain ↗]                            │
└──────────────────────────────────────────────────────────────────────────────┘

18.3 Delta Explorer (Inventory dimension)

┌─ Delta Explorer › Inventory ─────────────────────────────────────────────────┐
│ base v=1232  →  target v=1234                                                │
│ Filters: [Item ▼] [Site ▼] [Segment ▼] [Δ% > 10%] [Sort by abs Δ value ▼]  │
├──────────────────────────────────────────────────────────────────────────────┤
│ Item                Site             Week    Planned (base) → (target)  Δ   │
│ ──── Changed (382) ──────────────────────────────────────────────────────── │
│ Carbon Frame (20)   Brussels (100)   W22       1,200    1,820    +620 +52% │
│ Wheelset (18)       Verona (335)     W22         800    1,350    +550 +69% │
│ Brake Pads (26)     Brussels (100)   W23       1,500    1,750    +250 +17% │
│ ... (379 more)                                                              │
│ ──── Added (12) ───────────────────────────────────────────────────────────│
│ Tube Continental    Berlin (312)     W24           -      300    +300 NEW  │
│ ... (11 more)                                                              │
│ ──── Removed (3) ──────────────────────────────────────────────────────────│
│ Saddle XYZ          Madrid (340)     W22         150        0    -150  GONE│
│                                                                              │
│ [Export CSV] [Open Series Viewer] [Mark for Investigation]                  │
└──────────────────────────────────────────────────────────────────────────────┘

18.4 Plan vs Actual

┌─ Plan vs Actual › Inventory › Carbon Frame × Brussels ─────────────────────┐
│ Snapshot: v=1232 (2026-04-28)   Bucket: W22 (2026-05-25)                  │
│                                                                             │
│   1500 ┤                          ╭─────╮                                  │
│        │                       ╭──╯     ╰── actual (from v=1235)           │
│   1000 ┤            planned ───┘                                            │
│        │                                                                    │
│    500 ┤                                                                    │
│      0 ┼────────────────────────────────────────────────────────────────    │
│        W18  W19  W20  W21  W22  W23  W24  W25  W26                          │
│                                                                             │
│   MAPE: 8.4%  |  WAPE: 11.2%  |  Bias: -4.1%  |  P90 deviation: 312 units  │
└─────────────────────────────────────────────────────────────────────────────┘

18.5 Approval Log

┌─ Approvals ──────────────────────────────────────────────────────────────────┐
│ Filters: Type=[forecast_adjustment ▼]  Source=[ai ▼]  Approver=[any ▼]      │
│         Date=[Last 30d ▼]  Decision=[any ▼]                                 │
├──────────────────────────────────────────────────────────────────────────────┤
│ Time          Type            Item × Site         Recommended      Decision │
│ 2026-05-13    forecast_adj    Carbon Frame×100    +12% uplift      ✅approve│
│   by sandra@ketteq.com  remark: "matches seasonal pattern"                  │
│ 2026-05-13    supply_order    Wheelset×335        qty 800→qty 1200 ✏ modify│
│   by bruno@ketteq.com   remark: "supplier confirmed extra cap"              │
│ 2026-05-12    io_override     Brake Pads×100      SL 90→95         ❌reject │
│   by sandra@ketteq.com  remark: "would blow $ budget"                       │
│ 2026-05-12    forecast_adj    Inner Tube×312      +5% uplift       🤖 auto  │
│   rule "low-impact C-class auto-approve" (fire_count=312, success_rate=97%) │
│ ...                                                                         │
└──────────────────────────────────────────────────────────────────────────────┘

18.6 Auto-Rule Editor

┌─ New Auto-Rule ──────────────────────────────────────────────────────────────┐
│ Name:           [Low-impact C-class auto-approve              ]              │
│ Type:           [forecast_adjustment ▼]                                      │
│ Action:         [auto_approve ▼]                                             │
│ Priority:       [100]                                                        │
│ Scope segments: [+ ABC=C][+ Aftermarket]                                     │
│                                                                              │
│ Conditions (all must hold):                                                  │
│   ┌──────────────────────────────────────────────────────────┐              │
│   │ abc_class == C                       [✖]                 │              │
│   │ abs_pct_delta <= 5                   [✖]                 │              │
│   │ source in (ai, rule)                 [✖]                 │              │
│   │ recent_accuracy_mase < 1.5           [✖]                 │              │
│   │ + Add condition                                          │              │
│   └──────────────────────────────────────────────────────────┘              │
│                                                                              │
│ [Simulate on last 90 days]  →  Would fire: 312 times                        │
│                                  Projected success rate: ~97%                │
│                                  Avg time saved: 18 sec/decision = 1.5 hr   │
│                                                                              │
│ [Save Draft]  [Activate]  [Cancel]                                          │
└──────────────────────────────────────────────────────────────────────────────┘

---

19. Performance & Caching Strategy

19.1 Partition pruning targets

Query type	Filter set	Pruning
Live read (current state)	pipeline_id=X, scenario_id=Y, pipeline_version_id=0	Full partition prune to one partition
Single-version replay	+ pipeline_version_id=N	Full partition prune
KPI evolution	pipeline_id=X only	Reads registry table only (small, PG-backed)
Multi-version delta	pipeline_version_id IN (V1, V2)	Reads two partitions
Item-scoped historical	+ item_id=I, site_id=S	Two partitions + skip-index by item/site

19.2 Pre-aggregated KPIs

pipeline_version_index.kpi_* columns precomputed at freeze. Timeline queries hit a single PG SELECT, never CH.

19.3 Delta cache

PIPE_version_delta_cache (CH MergeTree) populated by background job for adjacent version pairs. Lookup pattern:

SELECT * FROM PIPE_version_delta_cache
WHERE base_version_id = X AND target_version_id = Y AND delta_dimension = 'kpi';

Miss → compute live and INSERT into cache. Eviction: 90-day TTL on computed_at.

19.4 Replay endpoint pattern

Existing endpoint reads:

WHERE pipeline_id = $1 AND scenario_id = $2

Replay endpoint adds:

WHERE pipeline_id = $1 AND scenario_id = $2 AND pipeline_version_id = $3

No additional query patterns needed — same code path with one extra parameter. Implementation: HistoryContext injects pipeline_version_id into all api.js calls; backend accepts ?pipeline_version_id=N on every existing read endpoint.

19.5 Materialized views

CH materialized views auto-refresh on INSERT to source table. Considered for:

• mv_pipeline_kpi_weekly — pipeline_id, week, KPI tuple
• mv_top_churners — items with high cross-version qty volatility

Phase 2 (after measuring baseline performance). Phase 1 ships without.

19.6 In-memory cache (Python)

Existing API has data_cache dict. Extend with:

• version_kpi_cache — version_id → KPI dict (TTL 5 min, or until freeze)
• delta_cache_inproc — (base, target, dim) → delta result (LRU 50)
• timeline_cache — pipeline_id → timeline entries (TTL 60 sec)

Invalidated on freeze / version delete events.

19.7 Frontend caching

• SWR / TanStack Query for all useVersion* hooks (stale-while-revalidate)
• 5-min cache key for version lists, 30-sec for timeline (events stream in)
• Optimistic updates for annotations, bookmarks, rule toggles

---

20. AI Integration Points

20.1 Endpoints (defined in §16.11)

/api/ai/explain, /api/ai/recommend-similar-scenario, /api/ai/predict-stability are scaffolded as 501 Not Implemented in Phase 1. Body schema fixed so the UI can ship buttons that gray-out.

20.2 Training data

interact_recommendation_approval is designed as ML training data from day 1:

• features_at_decision JSON captures the full state at decision time
• decision + approved_payload is the label
• auto_rule_id distinguishes human vs auto decisions
• duration_ms flags rushed vs deliberate decisions
• rollback_count (joined from auto-rule table or computed) flags decisions later reversed

Exports:

GET /api/approvals/export/jsonl?type={rec_type}&from=&to=
    → JSONL stream suitable for fine-tuning or classical ML

20.3 Stability prediction (Phase 2)

Model: gradient boosted tree (LightGBM) over historical version metadata, predicting next-version kpi_nervousness from current pipeline state + recent churn trajectory. Training set: pipeline_version_index rows + lagged KPIs.

20.4 Similar-scenario retrieval (Phase 2)

Vector embedding of pipeline state (KPIs + segment mix + parameter sets) → k-NN over historical versions to find the closest historical scenario. Used to answer "show me a past scenario that achieved this KPI mix".

20.5 LLM explainability (Phase 2)

/api/ai/explain prompts the LLM (Anthropic Claude via existing ai parameter type) with the structured delta + driver attribution. Returns prose for non-technical audiences.

---

21. Planner Collaboration Workflow

21.1 Annotations

Any user can annotate: - a version (comment on a snapshot) - a version pair (comment on a comparison) - a specific row in a delta (item × site comment) - an event

Stored in scenario.interact_annotation. Surfaced in Timeline + DeltaExplorer.

21.2 Bookmarks

A bookmark = saved comparison or saved version filter. Stored in scenario.interact_bookmark. Per-user, can be shared=true to make it visible to the team.

21.3 Review workflow

Optional gated workflow: a frozen version can be moved through states draft → in_review → approved → published. State changes recorded in interact_review_action. Phase 2.

21.4 Notifications

On version_frozen, all users subscribed to the pipeline receive a notification (in-app bell + optional email). Stored in scenario.interact_notification. Phase 2.

21.5 Decision tracking

Every freeze of a pipeline includes who triggered it (created_by) and an optional comment. Manual reviews of past decisions can be done via the Approval Log filtered by approver.

---

22. Example Queries

22.1 List all frozen versions for pipeline 4 in last 90 days

SELECT pipeline_version_id, snapshot_ts, bucket_start, bucket_period,
       comment, kpi_inventory_value, kpi_fill_rate
FROM master.pipeline_version_index
WHERE pipeline_id = 4 AND frozen = TRUE
  AND snapshot_ts >= NOW() - INTERVAL '90 days'
ORDER BY snapshot_ts DESC;

22.2 Replay forecast for Carbon Frame × Brussels from a specific snapshot

SELECT forecast_week, point_forecast, q10, q90
FROM PIPE_forecast_point_values
WHERE pipeline_id = 4
  AND scenario_id = 4
  AND pipeline_version_id = 1232
  AND unique_id = '20_100'
  AND method = best_method
ORDER BY forecast_week;

22.3 Compare supply orders between two versions

WITH base AS (
    SELECT id, item_id, site_id, arrival_week, qty
    FROM PIPE_supply_orders
    WHERE pipeline_id = 4 AND scenario_id = 4 AND pipeline_version_id = 1232
),
target AS (
    SELECT id, item_id, site_id, arrival_week, qty
    FROM PIPE_supply_orders
    WHERE pipeline_id = 4 AND scenario_id = 4 AND pipeline_version_id = 1234
)
SELECT
    coalesce(b.id, t.id) AS order_id,
    coalesce(b.item_id, t.item_id) AS item_id,
    coalesce(b.site_id, t.site_id) AS site_id,
    b.qty AS base_qty,
    t.qty AS target_qty,
    coalesce(t.qty, 0) - coalesce(b.qty, 0) AS qty_delta,
    CASE
        WHEN b.id IS NULL THEN 'added'
        WHEN t.id IS NULL THEN 'removed'
        WHEN b.qty <> t.qty OR b.arrival_week <> t.arrival_week THEN 'changed'
        ELSE 'unchanged'
    END AS status
FROM base b
FULL OUTER JOIN target t USING (id)
WHERE status <> 'unchanged';

22.4 Forecast accuracy evolution over last 12 versions

SELECT
    pv.snapshot_ts,
    avg(abs(fp.point_forecast - coalesce(da.qty, 0))) AS mae,
    avg(abs(fp.point_forecast - coalesce(da.qty, 0))
        / nullif(fp.point_forecast, 0)) AS mape,
    avg(fp.point_forecast - coalesce(da.qty, 0))      AS bias
FROM PIPE_forecast_point_values fp
JOIN PIPE_pipeline_version pv USING (pipeline_version_id)
LEFT JOIN MASTER_demand_actuals da
    ON da.item_id = fp.item_id AND da.site_id = fp.site_id AND da.date = fp.forecast_week
WHERE fp.pipeline_id = 4
  AND pv.frozen = 1
  AND fp.method = fp.best_method
GROUP BY pv.snapshot_ts
ORDER BY pv.snapshot_ts
LIMIT 12;

22.5 Top 20 items by supply plan churn (last 4 frozen versions)

WITH last_versions AS (
    SELECT pipeline_version_id, snapshot_ts,
           row_number() OVER (ORDER BY snapshot_ts DESC) AS rn
    FROM PIPE_pipeline_version
    WHERE pipeline_id = 4 AND scenario_id = 4 AND frozen = 1
)
SELECT
    o.item_id, o.site_id, o.item_name, o.site_name,
    stddev(o.qty) AS qty_stddev,
    avg(o.qty)    AS qty_mean,
    stddev(o.qty) / nullif(avg(o.qty), 0) AS cov
FROM PIPE_supply_orders o
JOIN last_versions lv USING (pipeline_version_id)
WHERE lv.rn <= 4
GROUP BY o.item_id, o.site_id, o.item_name, o.site_name
HAVING avg(o.qty) > 100
ORDER BY cov DESC
LIMIT 20;

22.6 Approvals with planner overrides (modify decisions) last 30 days

SELECT
    ra.decided_at, ra.recommendation_type, ra.approver,
    ra.recommended_payload, ra.approved_payload,
    ra.remark, ra.item_id, ra.site_id
FROM scenario.interact_recommendation_approval ra
WHERE ra.decision = 'modify'
  AND ra.decided_at >= NOW() - INTERVAL '30 days'
ORDER BY ra.decided_at DESC;

22.7 Auto-rule effectiveness

SELECT
    r.id, r.name, r.recommendation_type, r.fire_count, r.success_count,
    r.rollback_count,
    round(r.success_count::numeric / nullif(r.fire_count, 0), 3) AS success_rate
FROM scenario.interact_auto_approval_rule r
WHERE r.is_active = TRUE
ORDER BY r.fire_count DESC;

22.8 Snapshot replay — full pipeline dashboard (CH)

-- "Show me the state of pipeline 4 as of version 1232"
-- (this is just every existing dashboard query with one extra WHERE clause)

-- Series table:
SELECT item_id, site_id, item_name, site_name, best_method, avg_mase, avg_bias
FROM PIPE_series_best_methods
WHERE pipeline_id = 4 AND scenario_id = 4 AND pipeline_version_id = 1232;

-- Supply summary:
SELECT round(sum(total_shortage), 0) AS shortage_qty,
       round(avg(avg_inventory), 0)  AS avg_inv
FROM (
    SELECT total_shortage, avg_inventory
    FROM PIPE_supply_inventory_projection
    WHERE pipeline_id = 4 AND scenario_id = 4 AND pipeline_version_id = 1232
    LIMIT 1 BY item_id, site_id
) summary;

---

23. Example API Payloads

23.1 GET /api/history/versions?pipeline_id=4

{
  "pipeline_id": 4,
  "total": 18,
  "versions": [
    {
      "pipeline_version_id": 1234,
      "snapshot_ts": "2026-05-12T14:23:01Z",
      "bucket_start": "2026-05-11",
      "bucket_period": "week",
      "frozen": true,
      "pinned": true,
      "parent_version_id": 1233,
      "created_by": "bruno@ketteq.com",
      "comment": "Post-S&OP review baseline",
      "row_count_total": 845232,
      "kpis": {
        "revenue": null,
        "inventory_value": 1325000,
        "fill_rate": 0.942,
        "shortage_qty": 520,
        "forecast_mase": 0.91,
        "forecast_bias": 0.011,
        "nervousness": 0.07,
        "supply_churn_pct": 0.07,
        "capacity_util": 0.81
      },
      "delta_vs_prev": {
        "inventory_value_pct": 0.104,
        "fill_rate_pct": -0.008,
        "nervousness_pct": 0.04
      }
    },
    { "pipeline_version_id": 1233, "...": "..." }
  ]
}

23.2 POST /api/history/freeze

Request:

{ "pipeline_id": 4, "scenario_id": 4, "comment": "post-S&OP baseline", "pin": true }

Response 201:

{
  "pipeline_version_id": 1234,
  "bucket_start": "2026-05-11",
  "bucket_period": "week",
  "snapshot_ts": "2026-05-12T14:23:01Z",
  "duration_ms": 4521,
  "table_counts": {
    "PIPE_forecast_point_values": 432100,
    "PIPE_supply_inventory_projection": 280000,
    "PIPE_supply_orders": 8200,
    "...": "..."
  },
  "kpis": { "inventory_value": 1325000, "fill_rate": 0.942, "...": "..." },
  "replaced_version_id": null
}

When same-bucket re-freeze:

{ "...": "...", "replaced_version_id": 1230 }

23.3 GET /api/compare/versions?base=1232&target=1234&dimension=kpi

{
  "base_version_id": 1232,
  "target_version_id": 1234,
  "dimension": "kpi",
  "deltas": [
    { "metric": "inventory_value", "base": 1180000, "target": 1325000, "abs": 145000, "pct": 0.123, "direction": "up" },
    { "metric": "fill_rate",       "base": 0.950,    "target": 0.942,   "abs": -0.008,   "pct": -0.008, "direction": "down" },
    { "metric": "shortage_qty",    "base": 230,      "target": 520,     "abs": 290,      "pct": 1.26,   "direction": "up" },
    { "metric": "forecast_mase",   "base": 0.82,     "target": 0.91,    "abs": 0.09,     "pct": 0.110,  "direction": "up" },
    { "metric": "forecast_bias",   "base": -0.032,   "target": 0.011,   "abs": 0.043,    "pct": null,   "direction": "pivot" },
    { "metric": "nervousness",     "base": 0.03,     "target": 0.07,    "abs": 0.04,     "pct": 1.33,   "direction": "up" }
  ],
  "cached": true,
  "cache_ts": "2026-05-12T14:30:14Z"
}

23.4 GET /api/compare/versions?base=1232&target=1234&dimension=order

{
  "base_version_id": 1232,
  "target_version_id": 1234,
  "dimension": "order",
  "summary": { "added": 12, "removed": 3, "changed": 382, "unchanged": 7833 },
  "rows": [
    {
      "order_id": 88412, "item_id": 20, "site_id": 100,
      "item_name": "Carbon Frame", "site_name": "Brussels",
      "base":   { "arrival_week": 22, "qty": 1200 },
      "target": { "arrival_week": 22, "qty": 1820 },
      "qty_delta": 620, "qty_pct": 0.517,
      "status": "changed"
    }
  ],
  "page": 1, "page_size": 100, "total_pages": 4
}

23.5 GET /api/compare/plan-vs-actual?...&dimension=demand

{
  "pipeline_id": 4,
  "version_id": 1232,
  "dimension": "demand",
  "horizon_weeks": 8,
  "series": [
    {
      "unique_id": "20_100",
      "item_name": "Carbon Frame", "site_name": "Brussels",
      "points": [
        { "week": "2026-05-04", "planned": 1200, "actual": 1180, "deviation": -20 },
        { "week": "2026-05-11", "planned": 1300, "actual": 1410, "deviation": 110 },
        { "week": "2026-05-18", "planned": 1250, "actual": null, "deviation": null }
      ],
      "metrics": { "mape": 0.058, "wape": 0.061, "bias": 0.030, "n_observations": 2 }
    }
  ]
}

23.6 POST /api/auto-rules/simulate

Request:

{
  "recommendation_type": "forecast_adjustment",
  "condition_expr": {
    "and": [
      {"==": ["abc_class", "C"]},
      {"<=": ["abs_pct_delta", 5]}
    ]
  },
  "lookback_days": 90
}

Response:

{
  "would_fire_count": 312,
  "approve_outcomes": 304,
  "modify_outcomes": 6,
  "reject_outcomes": 2,
  "projected_success_rate": 0.974,
  "projected_time_saved_seconds": 5616,
  "items_affected": 47,
  "approvers_replaced": ["sandra@ketteq.com", "bruno@ketteq.com"],
  "sample_decisions": [
    { "approval_id": 88421, "rec_type": "forecast_adjustment", "decision": "approve", "...": "..." }
  ]
}

23.7 GET /api/history/timeline?pipeline_id=4&from=2026-04-01

{
  "pipeline_id": 4,
  "entries": [
    { "ts": "2026-05-12T14:23:01Z", "type": "version_frozen", "version_id": 1234, "comment": "Post-S&OP review baseline", "kpis": {"...": "..."} },
    { "ts": "2026-05-10T09:11:00Z", "type": "event", "event_id": 88, "event_type": "supplier_disruption", "severity": "warn", "description": "Acme lead time +14d" },
    { "ts": "2026-05-09T16:42:11Z", "type": "approval", "approval_id": 9120, "rec_type": "forecast_adjustment", "decision": "modify", "approver": "sandra@ketteq.com" },
    { "ts": "2026-05-09T16:30:00Z", "type": "kpi_jump", "kpi": "forecast_mase", "from": 0.85, "to": 0.91, "magnitude": 0.071, "between_versions": [1233, 1234] },
    { "ts": "2026-05-05T02:00:00Z", "type": "version_frozen", "version_id": 1233 }
  ]
}

23.8 GET /api/explain/delta?base=1232&target=1234&kpi=inventory_value

{
  "kpi": "inventory_value",
  "base_version_id": 1232,
  "target_version_id": 1234,
  "base_value": 1180000,
  "target_value": 1325000,
  "abs_delta": 145000,
  "pct_delta": 0.123,
  "drivers": [
    { "name": "safety_stock_increase", "contribution_pct": 0.62, "abs_contribution": 89900,
      "detail": { "items_affected": 42, "avg_buffer_delta": 28, "top_items": ["Carbon Frame", "Wheelset"] } },
    { "name": "supplier_lead_time_drift", "contribution_pct": 0.28, "abs_contribution": 40600,
      "detail": { "avg_lt_delta_days": 7.2, "top_suppliers": ["Acme Inc"] } },
    { "name": "forecast_uplift", "contribution_pct": 0.10, "abs_contribution": 14500,
      "detail": { "items_affected": 8 } },
    { "name": "unexplained", "contribution_pct": 0.00, "abs_contribution": 0, "detail": {} }
  ],
  "correlated_events": [
    { "event_id": 88, "type": "supplier_disruption", "date": "2026-05-08", "correlation_score": 0.82,
      "narrative": "Disruption began 4 days before frozen snapshot; affects 18 items in inventory delta." }
  ]
}

---

24. Phase 1 Code Scope

24.1 SQL files added

File	Purpose
files/DDL/ch_migration_v2.sql	Rebuild all 22 PIPE_* tables with new partition key + add column
files/DDL/ch_schema_v2.sql	New CH tables: PIPE_pipeline_version, PIPE_on_hand_snapshot, 3× actuals, INTERACT_recommendation_approval, INTERACT_auto_approval_rule, INTERACT_planning_event, PIPE_version_delta_cache
files/DDL/pg_history_schema.sql	PG side: master.pipeline_version_index, scenario.interact_recommendation_approval, scenario.interact_auto_approval_rule, scenario.interact_planning_event, ALTER on config_scenario_pipeline

24.2 Python modules added/modified

New:

• files/historization/freeze.py — freeze_pipeline(), compute_bucket_start(), compute_kpis()
• files/historization/auto_approval.py — evaluate_auto_rules(), evaluate_predicate(), log_recommendation_decision()
• files/historization/scheduler.py — CLI entry point for cron-driven freeze
• files/historization/retention.py — retention cleanup CLI

Modified:

• files/db/db.py — drop_ch_partition() updated to handle 3-tuple partition keys via new _PIPE_PARTITION_ARITY registry
• files/run_pipeline.py — new --freeze, --freeze-period, --freeze-comment flags
• files/api/main.py — register new endpoints (versions, freeze, compare, replay, timeline, approvals, auto-rules, events), add history param type to PARAMETER_REGISTRY

Not modified in Phase 1 (deferred to Phase 2):

• 32 writer call sites including pipeline_version_id in row payloads — relies on column DEFAULT 0 for now. Migration is column-default-safe.
• React UI (deferred to Phase 2 per user spec: "UI = doc only either way")

24.3 Endpoints implemented in Phase 1

Phase 1 minimal endpoint set (rest are documented in §16 but return 501 until Phase 2):

POST   /api/history/freeze                    ✅
GET    /api/history/versions                  ✅
GET    /api/history/versions/{vid}            ✅
PATCH  /api/history/versions/{vid}            ✅
DELETE /api/history/versions/{vid}            ✅
GET    /api/compare/versions                  ✅ (kpi dimension only)
GET    /api/history/timeline                  ✅
POST   /api/approvals                         ✅
GET    /api/approvals                         ✅
GET    /api/auto-rules                        ✅
POST   /api/auto-rules                        ✅
PUT    /api/auto-rules/{id}                   ✅
DELETE /api/auto-rules/{id}                   ✅
POST   /api/auto-rules/simulate               ✅
GET    /api/events                            ✅
POST   /api/events                            ✅

24.4 Param registry entry

Added to PARAMETER_REGISTRY in files/api/main.py:

{
    "parameter_type": "history",
    "label": "Scenario History",
    "description": "Snapshot period, retention, and auto-freeze scheduling",
}

Seeded into config_parameters on next migration run.

24.5 Multi-tenant provisioning wiring

files/historization/migrations.py exposes three idempotent functions, composed by apply_history_schema():

• apply_history_schema_pg(schema) — runs pg_history_schema.sql against the current tenant's PG database with {schema} substituted.
• apply_base_ch_schema() — bootstraps ch_schema.sql (base PIPE_, MASTER_, CONFIG_, INTERACT_ tables) when the CH database has no PIPE_* tables yet. Skipped on subsequent calls. This means brand-new tenants get their analytical layer initialised automatically — no manual clickhouse-client --multiquery < ch_schema.sql needed.
• apply_history_schema_ch() — creates the new CH tables (PIPE_pipeline_version mirror, PIPE_on_hand_snapshot, 3 actuals, INTERACT_* mirrors, PIPE_version_delta_cache) in the current tenant's CH database. Logs a warning if the existing PIPE_* tables have not yet been migrated to the v2 partition key (destructive migration is not auto-applied).

Both are called from db.db.init_schema() immediately after the main DDL block, so every tenant lifecycle path gets the history schema for free:

Path	Calls init_schema()?	Applies history schema?
API startup (@app.on_event("startup") → _init_and_load_all → _init_db_schema())	yes, per tenant	yes
Provision new tenant (admin.py:_provision_empty → _run_schema_on_new_db)	yes	yes
Clone tenant (admin.py:_provision_clone)	yes (after pg_restore)	yes
Admin "Run migrations" endpoint (POST /api/admin/run-migrations)	yes, per tenant	yes

The result dict from init_schema(collect_results=True) now includes a history_schema key with PG + base-CH + history-CH outcomes:

{
  "phase1": "ok",
  "statements": [...],
  "history_schema": {
    "pg":      {"status": "ok", "error": null},
    "base_ch": {"status": "skipped", "reason": "already_bootstrapped"},
    "ch":      {"status": "ok", "tables": ["PIPE_pipeline_version","PIPE_on_hand_snapshot","..."], "failures": []}
  }
}

Lifecycle by tenant maturity:

Tenant state	Startup flow	Manual step required?
Brand new (CH db empty)	apply_history_schema_pg → apply_base_ch_schema (creates all base PIPE_*) → apply_history_schema_ch	None — fully automated
Existing pre-v2 (PIPE_* tables exist, no pipeline_version_id)	apply_history_schema_pg → apply_base_ch_schema (skipped) → apply_history_schema_ch (warns)	Run ch_migration_v2.sql once for that tenant
Existing post-v2	apply_history_schema_pg → apply_base_ch_schema (skipped) → apply_history_schema_ch (idempotent)	None

ch_migration_v2.sql is destructive (RENAME) and not idempotent; running it on a tenant that has already been migrated will fail. Brand-new tenants never need it — apply_base_ch_schema() uses the same ch_schema.sql as before (no pipeline_version_id columns), and an upgrade story for them is to run ch_migration_v2.sql after they have written real data, OR — preferred — update ch_schema.sql to include pipeline_version_id from the start so new tenants never have a pre-v2 state. The latter is a Phase 2 cleanup task.

---

25. Phase Plan & Roadmap

Phase 1 (this delivery)

• DDL migration scripts (CH rebuild + PG history schema)
• Freeze module + scheduler stub + retention CLI
• Auto-approval evaluator
• drop_ch_partition 3-tuple support
• CLI flag in run_pipeline.py
• Minimal API endpoints (§24.3)
• Param registry entry
• This doc

Phase 2

• Update 32 writer call sites to explicitly emit pipeline_version_id=0 in payloads
• Hook recommendation logging into 8 existing approval/write endpoints
• Compute KPIs at freeze time (currently null until Phase 2)
• React Scenario Review module (14 routes per §17)
• Snapshot Replay HistoryContext wiring
• Delta Explorer virtualized table
• Plan vs Actual UI + ingestion endpoints for lead_time/service/order_arrival actuals

Phase 3

• Stability heatmaps, event correlation UI
• AI endpoints implementation (explain, similar-scenario, predict-stability)
• Auto-rule mining suggestions
• Annotations, bookmarks, notifications, review workflow
• Materialized views for performance
• ClickHouse columnar storage tuning
• Multi-tenant freeze quotas

Phase 4

• LLM natural-language explainability
• Vector embedding similar-scenario retrieval
• Automated A/B comparison of auto-rule effectiveness
• Replay-based scenario fork (clone version into new pipeline)

---

26. Test-Drive (theBicycle)

After Phase 1 deploys, validate against the thebicycle tenant:

26.1 Run the migration

# CH rebuild
Get-Content files\DDL\ch_migration_v2.sql `
  -replace '{{TENANT}}', 'thebicycle' `
  | clickhouse-client --user mirabelle --password mirabelle2026 `
      --database thebicycle --multiquery

# CH new schema
Get-Content files\DDL\ch_schema_v2.sql `
  -replace '{{TENANT}}', 'thebicycle' `
  -replace '{{PG_HOST}}', 'localhost' `
  -replace '{{PG_PORT}}', '5432' `
  | clickhouse-client --user mirabelle --password mirabelle2026 `
      --database thebicycle --multiquery

# PG history schema
psql -d thebicycle -v schema=scenario -f files\DDL\pg_history_schema.sql

26.2 Verify migration

-- CH side
SELECT name, partition_key FROM system.tables
WHERE database = 'thebicycle' AND name LIKE 'PIPE_%' AND name <> 'PIPE_feature_values'
ORDER BY name;
-- Expect partition_key containing 'pipeline_version_id' for all 22 tables.

SELECT count() FROM `thebicycle`.PIPE_forecast_point_values
WHERE pipeline_version_id = 0;
-- Expect ~432000 (existing live rows).

-- PG side
\d master.pipeline_version_index
\d scenario.interact_recommendation_approval
\d scenario.interact_auto_approval_rule

26.3 First freeze (Default pipeline = pid 4)

curl -X POST http://localhost:8002/api/history/freeze \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"pipeline_id": 4, "scenario_id": 4, "comment": "first historization snapshot", "pin": true}'

Response should include pipeline_version_id > 0 and table_counts for 22 tables.

26.4 Verify snapshot rows exist

-- Replace 1 with the returned version_id
SELECT count() FROM `thebicycle`.PIPE_forecast_point_values WHERE pipeline_version_id = 1;
-- Expect same count as version 0 had.

SELECT count() FROM `thebicycle`.PIPE_supply_inventory_projection WHERE pipeline_version_id = 1;
SELECT count() FROM `thebicycle`.PIPE_supply_orders WHERE pipeline_version_id = 1;

26.5 Same-bucket re-freeze (dedup)

# Re-freeze within the same week — should replace v=1 with new vid
curl -X POST http://localhost:8002/api/history/freeze \
  -d '{"pipeline_id": 4, "scenario_id": 4, "comment": "replace test"}'

Response should include "replaced_version_id": 1.

SELECT pipeline_version_id, frozen FROM master.pipeline_version_index WHERE pipeline_id = 4;
-- Expect only the new version_id; v=1 deleted.

SELECT count() FROM `thebicycle`.PIPE_forecast_point_values WHERE pipeline_version_id = 1;
-- Expect 0 (partition dropped).

26.6 Listing versions

curl 'http://localhost:8002/api/history/versions?pipeline_id=4'

26.7 Approval log smoke test

curl -X POST http://localhost:8002/api/approvals \
  -d '{
    "pipeline_id": 4,
    "recommendation_type": "forecast_adjustment",
    "recommendation_id": "20_100:2026-W25",
    "source": "ai",
    "item_id": 20,
    "site_id": 100,
    "recommended_payload": {"delta_pct": 0.12, "method": "uplift"},
    "approved_payload": {"delta_pct": 0.12, "method": "uplift"},
    "decision": "approve",
    "approver": "bruno@ketteq.com",
    "remark": "matches seasonal"
  }'

curl 'http://localhost:8002/api/approvals?recommendation_type=forecast_adjustment'

26.8 Auto-rule definition + simulation

# Create
curl -X POST http://localhost:8002/api/auto-rules \
  -d '{
    "name": "low-impact C-class auto-approve",
    "recommendation_type": "forecast_adjustment",
    "condition_expr": {"and": [{"==": ["abc_class","C"]}, {"<=": ["abs_pct_delta", 5]}]},
    "action": "auto_approve",
    "priority": 100
  }'

# Simulate
curl -X POST http://localhost:8002/api/auto-rules/simulate \
  -d '{
    "recommendation_type": "forecast_adjustment",
    "condition_expr": {"and": [{"==": ["abc_class","C"]}, {"<=": ["abs_pct_delta", 5]}]},
    "lookback_days": 90
  }'

26.9 KPI delta sanity

# Get the two latest version IDs from /api/history/versions
curl 'http://localhost:8002/api/compare/versions?base=1&target=2&dimension=kpi'

All KPI fields will be null in Phase 1 (KPIs precomputed only after Phase 2 wires KPI compute into freeze). Skeleton response shape verified.

---

27. Glossary

Term	Meaning
Bucket	A discrete period (week/month/quarter) holding at most one frozen version per pipeline.
Bucket start	First date of the bucket (Monday of week, day-1 of month, day-1 of quarter).
Churn	Sum of absolute qty differences in supply orders between two versions.
Cov	Coefficient of variation = stddev / mean. Used for forecast volatility.
Delta	Diff between two snapshots, can be row-level or aggregate.
Driver	A named contributor to a KPI delta (e.g. safety_stock_increase).
Event	A planning-relevant occurrence (supplier disruption, policy change, etc.) stored in interact_planning_event.
Freeze	Action of cloning live version (v=0) into a new immutable version (v=N).
Frozen	A version that has completed freeze and is frozen=TRUE in the registry.
Live	The current mutable state of a pipeline, always pipeline_version_id=0.
Nervousness	Measure of plan instability — number of times an order's qty/arrival changes across recent versions.
Path Y2	The migration strategy chosen: ALTER PARTITION BY of all PIPE_* tables to include pipeline_version_id.
PIPE_	CH naming prefix for MergeTree fact tables.
Pin	Marker on a version preventing automatic retention deletion.
Recommendation	A system or AI-proposed change presented to a planner for approval.
Source	Origin of a recommendation: ai, rule (auto-rule), system (engine), or planner (manual override).
Version 0	Reserved version_id for the live/mutable state of every pipeline.

---

Maintainer: Bruno Zindy (bruno.zindy@ketteq.com) Last updated: 2026-05-15 (Phase 1 design)