Database Schema

Mirabelle uses three PostgreSQL schemas across two databases, plus one ClickHouse database for analytics. This page documents every table, its purpose, and the relationships between them.

---

Schema Architecture Overview

Mirabelle's PostgreSQL layer is organised into three schemas, each with a distinct role and lifecycle:

Schema	Database	Purpose	Can be dropped?
scenario	Tenant DB (e.g. thebicycle)	Primary application working schema. Holds all pipeline outputs, forecast scenarios, configuration, user accounts, overrides, allocation results, and interaction tables.	No — dropping it destroys the entire tenant. Every query the API makes targets this schema.
master	Tenant DB + forecastai_master	Scenario-independent reference data (items, sites, routes, BOMs, on-hand, calendars, demand actuals). In the master database, it holds multi-tenancy control tables (accounts, superadmins, user_accounts, parameters).	No in master DB (breaks authentication). In tenant DB, it can be re-seeded from the ERP, but this is a destructive operation.
import	Tenant DB	ETL staging area. Flat, forgiving landing tables for ERP/external data before the ETL pipeline processes them into scenario.* and master.*. Stateless drop zone — truncating or dropping and re-creating is safe.	Yes — it is a stateless staging area. Re-run files/DDL/import_schema.sql to recreate. No business data is lost.

scenario must never be dropped

The scenario schema is the core of the application. It contains all configuration (config_*), pipeline outputs (pipe_*), scenario definitions (scen_*), user-editable tables (interact_*), and system tables (sys_*). Dropping it is equivalent to deleting the tenant entirely. There is no recovery short of restoring from backup.

Schema placeholder {schema}

In DDL files under files/DDL/, every table reference uses {schema} which is substituted at runtime with the tenant's actual schema name. In production the schema is always scenario unless overridden in master.accounts.schema_name.

---

Table Naming Convention

Tables in the scenario schema use a prefix convention to indicate their domain. The DDL files create tables with these prefixes; the prefix is part of the actual PostgreSQL table name.

Prefix	Meaning	Example	Mutable?
master_	Immutable reference/master data (shared across pipelines)	master_item, master_demand_actuals, master_route	Written by ETL only; pipeline steps only read
pipe_	Pipeline output facts (one row per series per run)	pipe_series_characteristics, pipe_supply_orders	Overwritten on each pipeline run
scen_	Scenario definition and configuration	scen_forecast_scenarios, scen_causal_scenarios	User-edited (scenario manager UI)
config_	System configuration and parameter routing	config_parameters, config_segment, config_scenario_pipeline	User-edited (settings UI)
interact_	User-editable interaction/workflow tables	interact_forecast_adjustments, interact_exception_log	User-edited (planner overrides, approvals)
sys_	System/housekeeping tables	sys_users, sys_audit_log, sys_revoked_tokens	System-managed
allocation_	Allocation engine tables	allocation_parameter_set, allocation_result	User-edited (config) + pipeline output (results)
meio_	MEIO parameter sets (segment-routable)	meio_parameter_set, meio_parameter_set_segment	User-edited (settings UI)
demand_profile	Demand profile templates	demand_profile	User-edited
replay_	Replay engine tables	replay_run, replay_iteration	System-managed (API creates/updates)

DDL vs. documentation names

This documentation often drops the prefix for readability (e.g. writing {schema}.item instead of {schema}.master_item). When querying the database directly, always use the full prefixed name (e.g. SELECT * FROM scenario.master_item).

---

Database Topology

flowchart TD
    subgraph masterDB["forecastai_master (PostgreSQL)"]
        MA["master.accounts"]
        MS["master.superadmins"]
        MP["master.parameters"]
        MU["master.user_accounts"]
    end

    subgraph tenantDB["Tenant DB (e.g. thebicycle)"]
        subgraph scenarioSchema["scenario schema"]
            MASTER["master_* tables\nItems · Sites · Routes · BOM\nDemand actuals · On-hand"]
            PIPE["pipe_* tables\nForecasts · Backtests · Supply\nMEIO · Exceptions · Allocation"]
            SCEN["scen_* tables\nForecast scenarios · Causal\nMaintenance · Inventory"]
            CONFIG["config_* tables\nParameters · Segments\nPipelines · ABC"]
            INTERACT["interact_* tables\nAdjustments · Approvals\nExceptions · Events"]
            SYS["sys_* tables\nUsers · Audit · Tokens · Process log"]
            ALLOC["allocation_* tables\nPriority · Starvation\nReservation · Fairness · Results"]
            MEIO["meio_* tables\nMEIO parameter sets"]
        end

        subgraph masterSchema["master schema (tenant DB)"]
            MPV["master.pipeline_version_index\nFreeze registry + KPIs"]
        end

        subgraph importSchema["import schema"]
            IMP["import.* tables\n29 staging tables\nERP flat files → ETL"]
        end
    end

    subgraph clickhouse["ClickHouse (optional analytics)"]
        CH_PIPE["PIPE_* tables\n54 fact tables\nPartitioned by (pipeline_id, scenario_id, archive_date)"]
        CH_MASTER["MASTER_* tables\nPostgreSQL-engine mirrors"]
        CH_CONFIG["CONFIG_* tables\nPostgreSQL-engine mirrors"]
        CH_INTERACT["INTERACT_* tables\nPostgreSQL-engine mirrors"]
    end

    IMP -->|ETL| MASTER
    IMP -->|ETL| SCEN
    PIPE -->|freeze| CH_PIPE
    MASTER -->|PG engine mirror| CH_MASTER

Pipeline outputs (forecasts, supply plans, netting, exceptions) are also stored in ClickHouse for sub-second analytics — see ClickHouse PIPE_ Tables at the bottom of this page.

---

Master Database (forecastai_master)

The master database holds only multi-tenancy control data. It has no per-tenant business data.

master.accounts

Purpose: One row per registered tenant. Maps the tenant account UUID to the PostgreSQL database name, schema, and optional connection overrides.

Column	Type	Description
id	UUID PK	Unique account identifier (embedded in every JWT)
display_name	TEXT UNIQUE	Human-readable tenant name shown in the UI toolbar
db_name	TEXT UNIQUE	PostgreSQL database name for this tenant (e.g. thebicycle)
schema_name	TEXT	Schema inside the tenant DB (default scenario)
connection_params	JSONB	Optional connection overrides: host, port, user, password, sslmode. NULL means use same server as master DB
is_active	BOOLEAN	Set to FALSE to disable a tenant without deleting data
created_at	TIMESTAMPTZ	Account creation timestamp
updated_at	TIMESTAMPTZ	Last modification timestamp

master.superadmins

Purpose: Global administrator accounts that can log into any tenant. Never stored in tenant databases.

Column	Type	Description
id	UUID PK	SuperAdmin UUID
email	TEXT UNIQUE	Login email
display_name	TEXT	Display name in UI
hashed_password	TEXT	bcrypt hash; NULL for OAuth-only accounts
auth_provider	TEXT	local / microsoft / google
is_active	BOOLEAN	Disable without deleting
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last modification

master.parameters

Purpose: Shared configuration values — primarily the JWT secret and token expiry. Mirrors the structure of {schema}.parameters in tenant DBs.

Column	Type	Description
id	SERIAL PK	Row identifier
parameter_type	TEXT	Type key (e.g. auth)
name	TEXT	Parameter set name (e.g. Default)
parameters_set	JSONB	Configuration values. For auth: {"jwt_secret": "...", "token_expiry_minutes": 480}
is_default	BOOLEAN	Whether this is the active default for its type
created_at	TIMESTAMPTZ	Creation timestamp

master.user_accounts

Purpose: Junction table mapping user emails to the accounts they can access. Used by the login flow when a user has access to multiple tenants.

Column	Type	Description
email	TEXT	User email (matches {schema}.users.email)
account_id	UUID FK	References master.accounts.id
created_at	TIMESTAMPTZ	Assignment timestamp

---

Tenant Database ({schema})

All tables below live in the scenario schema of a specific tenant's PostgreSQL database.

---

Domain: Master / Lookup Data

These tables are immutable (not pipeline-scoped). They represent the physical world: what items exist, where sites are, who customers are.

{schema}.item_type

Purpose: Lookup table for classifying items (e.g. finished good, component, service part).

Column	Type	Description
id	BIGINT PK	Type identifier
xuid	TEXT	External unique identifier
name	TEXT	Type name
description	TEXT	Human-readable description

{schema}.site_type

Purpose: Lookup table for classifying sites (e.g. warehouse, factory, retail).

Column	Type	Description
id	BIGINT PK	Type identifier
xuid	TEXT	External unique identifier
name	TEXT	Type name
description	TEXT	Human-readable description

{schema}.item

Purpose: Item (SKU) master data. Every forecasted series is associated with an item.

Column	Type	Description
id	BIGINT PK	Item identifier
xuid	TEXT	External unique identifier (ERP key)
name	TEXT	Item name
description	TEXT	Item description
attributes	JSONB	Flexible key-value attributes
type_id	BIGINT FK → item_type	Item type classification
image_url	TEXT	Optional product image URL
price	DOUBLE PRECISION	Selling price
cost	DOUBLE PRECISION	Unit cost

{schema}.site

Purpose: Site (location) master data. Sites include warehouses, factories, repair shops, and customer locations.

Column	Type	Description
id	BIGINT PK	Site identifier
xuid	TEXT	External unique identifier
name	TEXT	Site name
description	TEXT	Description
attributes	JSONB	Flexible key-value attributes
type_id	BIGINT FK → site_type	Site type classification
longitude	NUMERIC(10,7)	Geographic longitude
latitude	NUMERIC(10,7)	Geographic latitude

{schema}.customer

Purpose: Customer master. In bicycle demo data, customers are teams (professional / amateur). In aerospace, customers are airlines with fleets.

Column	Type	Description
customer_id	SERIAL PK	Customer identifier
code	TEXT UNIQUE	Short code
name	TEXT	Customer name
customer_type	TEXT	professional, amateur, team, etc.
country	TEXT	ISO country code
contact_email	TEXT	Primary contact
attributes	JSONB	Flexible: num_riders, num_bikes, sla_level, billing_currency, credit_limit_eur, notes
created_at	TIMESTAMPTZ	Creation timestamp

{schema}.customer_site

Purpose: Defines return logistics parameters for each customer-site pair (e.g. wash rate and lead time for product returns).

Column	Type	Description
customer_id	BIGINT FK → customer	Customer
site_id	BIGINT FK → site	Site
wash_rate	DOUBLE PRECISION	Fraction of returned units lost (0–1)
lead_time	DOUBLE PRECISION	Days to return asset/part back to site
updated_at	TIMESTAMPTZ	Last update

{schema}.demand_type

Purpose: Qualifies the nature of demand. Seeded with four standard values.

Column	Type	Description
id	BIGSERIAL PK	Type identifier
name	TEXT UNIQUE	Regular demand, Urgent demand, Promotional demand, Service demand

{schema}.channel

Purpose: Sales channel lookup (e.g. retail, wholesale, online). Referenced by demand_actuals.channel and the ETL pipeline.

Column	Type	Description
xuid	TEXT PK	External unique identifier
name	TEXT	Channel name
description	TEXT	Channel description

{schema}.currency_conversion

Purpose: Exchange rate table for multi-currency cost/price display. Used by useValueMode.js (frontend) and fmtValue() to convert values from the global default currency to the user's selected currency.

Column	Type	Description
id	BIGSERIAL PK	Conversion record
from_currency	TEXT	Source currency code (e.g. USD)
to_currency	TEXT	Target currency code (e.g. EUR)
rate	DOUBLE PRECISION	Conversion rate
effective_date	DATE	Date the rate is valid from

{schema}.item_location (also referenced as item_site)

Purpose: Item-site level cost parameters used by MEIO, supply planning, and allocation. Stores order cost, holding rate, cost price, selling price, and unit cost per item per site.

Column	Type	Description
item_id	BIGINT PK (composite)	Item
site_id	BIGINT PK (composite)	Site
order_cost	DOUBLE PRECISION	Fixed cost per order placement
holding_rate	DOUBLE PRECISION	Annual holding cost rate (fraction of unit cost)
cost_price	DOUBLE PRECISION	Standard cost price
price	DOUBLE PRECISION	Selling price
cost	DOUBLE PRECISION	Unit cost

---

Domain: Demand

{schema}.demand_actuals

Purpose: Core historical demand fact table. One row per item/site/customer/date/channel combination. This table is immutable — the ETL writes it and downstream processes only read from it or write corrected values to demand_corrections.

pg_mooncake columnar storage

If pg_mooncake is installed, this table is wrapped in a columnar extension table called mooncake_demand_actuals for analytical query performance. The standard heap table is always created as the canonical source.

Column	Type	Description
item_id	BIGINT FK → item	Item
site_id	BIGINT FK → site	Site
customer_id	BIGINT FK → customer	Customer (may be NULL for site-aggregated demand)
demand_type_id	BIGINT FK → demand_type	Demand qualifier
channel	TEXT	Sales channel
date	DATE	Week start date (Monday)
qty	DOUBLE PRECISION	Raw demand quantity
corrected_qty	DOUBLE PRECISION	Inline corrected quantity (pipeline-level corrections are in demand_corrections)
item_name	TEXT	Denormalised item name
site_name	TEXT	Denormalised site name
unique_id	TEXT	Series identifier — format: {item_id}_{site_id} or {item_id}_{site_id}_{customer_id}

Key indexes: idx_demand_unique_id, idx_demand_item_site, idx_demand_item_site_d (descending date for recent queries).

{schema}.demand_corrections

Purpose: Pipeline-scoped outlier corrections. When the outlier detection step decides a demand value should be replaced, it writes the corrected quantity here rather than modifying demand_actuals. Each pipeline has its own set of corrections.

Column	Type	Description
unique_id	TEXT	Series identifier
date	DATE	The week being corrected
corrected_qty	DOUBLE PRECISION	Replacement quantity
pipeline_id	BIGINT NOT NULL	Pipeline this correction belongs to (part of PK)
note	TEXT	Optional correction note
updated_at	TIMESTAMPTZ	Last update

Primary key: (unique_id, date, pipeline_id) — multiple pipelines can have different corrections for the same data point.

---

Domain: Pipeline Outputs

These tables receive one row per series per pipeline run. All have pipeline_id and scenario_id columns as part of their scope.

{schema}.demand_corrected

Purpose: Pipeline-scoped outlier corrections written by the embedded pre-forecast outlier detection step. Each pipeline maintains its own set of corrections; re-running a pipeline overwrites only its rows.

Written by: Outlier detection pre-step inside run_pipeline.py --only forecast

Column	Type	Description
item_id	BIGINT	Item (parsed from unique_id)
site_id	BIGINT	Site (parsed from unique_id)
date	DATE	The corrected week
pipeline_id	BIGINT NOT NULL	Pipeline scope (part of PK)
original_value	DOUBLE PRECISION	Raw demand value before correction
corrected_value	DOUBLE PRECISION	Replacement value after correction strategy
detection_method	TEXT	iqr, zscore, or stl
z_score	DOUBLE PRECISION	Standardised distance from series mean
lower_bound	DOUBLE PRECISION	Lower detection fence
upper_bound	DOUBLE PRECISION	Upper detection fence

Primary key: (item_id, site_id, date, pipeline_id)

unique_id parsing rule

Never JOIN demand_corrected against demand_actuals on item_id + site_id — this creates a cross-product. Always parse item_id / site_id directly from the unique_id string and query with WHERE item_id = %s AND site_id = %s.

{schema}.series_characteristics

Purpose: Stores the full characterisation output for each series. One row per (series, scenario, pipeline) combination.

Written by: Characterisation step (files/characterization/)

Column	Type	Description
id	SERIAL PK	Row identifier
unique_id	TEXT	Series identifier
n_observations	INTEGER	Number of non-null demand data points
date_range_start	TEXT	Earliest demand date
date_range_end	TEXT	Latest demand date
mean	DOUBLE PRECISION	Mean demand
std	DOUBLE PRECISION	Standard deviation
has_seasonality	BOOLEAN	Whether significant seasonality detected
seasonal_periods	JSONB	List of detected seasonal periods (e.g. [52])
seasonal_strength	DOUBLE PRECISION	STL seasonal strength (0–1)
has_trend	BOOLEAN	Whether a significant trend is present
trend_direction	TEXT	up, down, or flat
trend_strength	DOUBLE PRECISION	Trend component strength (0–1)
is_intermittent	BOOLEAN	Whether ADI > threshold or zero_ratio > threshold
zero_ratio	DOUBLE PRECISION	Proportion of zero-demand periods
adi	DOUBLE PRECISION	Average Demand Interval (periods between non-zero demand)
cov	DOUBLE PRECISION	Coefficient of variation (σ/μ)
is_stationary	BOOLEAN	ADF test result
adf_pvalue	DOUBLE PRECISION	p-value of the ADF test
complexity_score	DOUBLE PRECISION	Composite complexity score
complexity_level	TEXT	low, medium, high
sufficient_for_ml	BOOLEAN	Whether enough data for ML methods
sufficient_for_deep_learning	BOOLEAN	Whether enough data for deep learning
recommended_methods	JSONB	List of recommended method names
frequency	TEXT	Time series frequency (W for weekly)
scenario_id	BIGINT NOT NULL FK → forecast_scenarios	Scenario scope
pipeline_id	BIGINT	Pipeline scope

Unique index: (unique_id, scenario_id, pipeline_id)

{schema}.forecast_results

Purpose: Stores the point forecast and quantile forecasts for each (series, method) combination.

Written by: Forecasting step (files/forecasting/)

Columnar storage

This table is optionally wrapped by pg_mooncake (mooncake_forecast_results) for analytical queries.

Column	Type	Description
unique_id	TEXT	Series identifier
method	TEXT	Model name (e.g. AutoARIMA, NHITS)
point_forecast	JSONB	Array of 24 weekly forecast values
quantiles	JSONB	Dict of quantile → array mappings (e.g. {"0.1": [...], "0.9": [...]})
hyperparameters	JSONB	Fitted model hyperparameters
training_time	DOUBLE PRECISION	Model training duration in seconds
scenario_id	BIGINT NOT NULL	Scenario scope
customer_id	BIGINT	Customer scope (for customer-level causal forecasts)
pipeline_id	BIGINT	Pipeline scope
site_id	BIGINT	Site scope (causal V2 — coverage-spread results)

{schema}.series_backtest_metrics

Purpose: Rolling-window backtesting results — one row per (series, method, origin date, horizon).

Written by: Evaluation step (files/evaluation/)

Column	Type	Description
unique_id	TEXT	Series identifier
method	TEXT	Model name
forecast_origin	DATE	The date from which this backtest window was projected
horizon	INTEGER	Forecast horizon step (1 = next week)
mae	DOUBLE PRECISION	Mean Absolute Error
rmse	DOUBLE PRECISION	Root Mean Squared Error
mape	DOUBLE PRECISION	Mean Absolute Percentage Error
smape	DOUBLE PRECISION	Symmetric MAPE
mase	DOUBLE PRECISION	Mean Absolute Scaled Error
bias	DOUBLE PRECISION	Mean forecast bias (positive = over-forecast)
crps	DOUBLE PRECISION	Continuous Ranked Probability Score
winkler_score	DOUBLE PRECISION	Winkler interval score
coverage_50/80/90/95	DOUBLE PRECISION	Interval coverage at respective confidence levels
quantile_loss	DOUBLE PRECISION	Pinball / quantile loss
aic	DOUBLE PRECISION	Akaike Information Criterion (statistical models only)
bic	DOUBLE PRECISION	Bayesian Information Criterion
aicc	DOUBLE PRECISION	AIC corrected for small samples
metric_source	TEXT	rolling_window (default)
scenario_id	BIGINT NOT NULL FK → forecast_scenarios	Scenario scope
pipeline_id	BIGINT	Pipeline scope

{schema}.forecasts_by_origin

Purpose: Point forecasts paired with actual values for each backtesting origin date. Used to render the ridge chart in TimeSeriesViewer.jsx.

Written by: Evaluation step

Column	Type	Description
unique_id	TEXT	Series identifier
method	TEXT	Model name
forecast_origin	DATE	Origin date of this backtest window
horizon_step	INTEGER	Step within the horizon (1–24)
point_forecast	DOUBLE PRECISION	Forecast value at this step
actual_value	DOUBLE PRECISION	Realised demand at this step
scenario_id	BIGINT NOT NULL	Scenario scope
pipeline_id	BIGINT	Pipeline scope

{schema}.series_best_methods

Purpose: The winner of the best-method selection competition per series. Also stores the runner-up and full ranking. Supports method locking by planners.

Written by: Best Method Selection (files/selection/best_method.py)

Column	Type	Description
id	SERIAL PK	Row identifier
unique_id	TEXT	Series identifier
best_method	TEXT	Name of the best-scoring model
best_score	DOUBLE PRECISION	Composite score of the winner (lower is better)
runner_up_method	TEXT	Name of the second-best model
runner_up_score	DOUBLE PRECISION	Composite score of runner-up
all_rankings	JSONB	Full ranked list of all evaluated methods
scenario_id	BIGINT NOT NULL	Scenario scope
pipeline_id	BIGINT	Pipeline scope
locked_method	TEXT	If set, this method is used regardless of scoring
locked_by	TEXT	Email of the planner who locked the method
locked_at	TIMESTAMPTZ	When the method was locked

Unique index: (unique_id, scenario_id, pipeline_id)

{schema}.fitted_distributions

Purpose: Fitted parametric demand distributions for MEIO safety-stock calculation. The best-fitting distribution is selected by KS statistic.

Written by: Distribution Fitting (files/distribution/fitting.py)

Column	Type	Description
unique_id	TEXT	Series identifier
method	TEXT	Forecasting method whose residuals were fitted
forecast_horizon	INTEGER	Horizon over which distribution was computed
distribution_type	TEXT	normal, gamma, lognormal, negbin, weibull
mean	DOUBLE PRECISION	Distribution mean
std	DOUBLE PRECISION	Distribution standard deviation
params	JSONB	Full parameter set (shape, scale, etc.)
ks_statistic	DOUBLE PRECISION	Kolmogorov-Smirnov test statistic (lower = better fit)
ks_pvalue	DOUBLE PRECISION	KS test p-value
service_level_quantiles	JSONB	Pre-computed quantiles at common service levels
scenario_id	BIGINT NOT NULL	Scenario scope
pipeline_id	BIGINT	Pipeline scope

{schema}.forecast_hyperparameters

Purpose: Model training hyperparameters per (series × method × pipeline × scenario). One row per (unique_id, method, pipeline_id, scenario_id). Stores the fitted model configuration for reproducibility and audit.

Written by: Forecasting step

Column	Type	Description
unique_id	TEXT	Series identifier
method	TEXT	Model name
hyperparameters	JSONB	Fitted model hyperparameters
computed_at	TIMESTAMPTZ	When this record was created
scenario_id	BIGINT NOT NULL	Scenario scope
pipeline_id	BIGINT	Pipeline scope

{schema}.series_detected_outliers

Purpose: Raw outlier detection results before correction. Stores the detection method, z-score, and bounds for each flagged data point, separate from the corrected output in demand_corrected.

Written by: Outlier detection (embedded in Forecasting step)

Column	Type	Description
item_id	BIGINT	Item
site_id	BIGINT	Site
date	DATE	The flagged week
pipeline_id	BIGINT NOT NULL	Pipeline scope
original_value	DOUBLE PRECISION	Raw demand value
detection_method	TEXT	iqr, zscore, or stl
z_score	DOUBLE PRECISION	Standardised distance from mean
lower_bound	DOUBLE PRECISION	Detection lower fence
upper_bound	DOUBLE PRECISION	Detection upper fence

{schema}.demand_profile

Purpose: Demand profile templates that define segment-level demand shaping parameters (e.g. growth rate, seasonal multiplier). Used by the segmentation and pipeline steps for profile-based routing.

Column	Type	Description
id	BIGSERIAL PK	Profile identifier
name	TEXT	Profile name
description	TEXT	Description
parameters	JSONB	Profile parameters (growth rate, seasonal factors, etc.)

---

Domain: MEIO / Inventory

{schema}.inventory_scenario_tag

Purpose: Lookup table classifying MEIO scenario types.

Column	Type	Description
id	SERIAL PK	Tag identifier
code	TEXT UNIQUE	kcurve, safety_stock, reorder_point
label	TEXT	Display label
description	TEXT	Description

{schema}.inventory_scenario

Purpose: A named inventory optimisation scenario. Stores both the input parameters and the computed results as JSONB.

Column	Type	Description
scenario_id	BIGSERIAL PK	Scenario identifier
name	TEXT	Scenario name
tag_id	INTEGER FK → inventory_scenario_tag	Type classification
parameters	JSONB	Input parameters (e.g. k_factor, target_budget, target_service_level)
results	JSONB	Output results (e.g. inv_val, orders_per_year, per-item EOQ quantities)
pipeline_id	BIGINT	Last pipeline that ran this scenario
created_by	TEXT	User who created the scenario
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

{schema}.on_hand_type

Purpose: Lookup for on-hand inventory categories (usable, quarantine, in-transit, etc.).

Column	Type	Description
id	BIGINT PK	Type identifier
xuid	TEXT	External identifier
name	TEXT	Type name
description	TEXT	Description
planning_type	TEXT	Planning-relevant type code
allocation_sequence	SMALLINT	Priority order for allocation

{schema}.on_hand

Purpose: Current on-hand inventory balances by item, site, and type.

Column	Type	Description
id	BIGINT PK	Balance record identifier
item_id	BIGINT	Item
site_id	BIGINT	Site
type_id	BIGINT FK → on_hand_type	Inventory type
tag	TEXT	Optional tag
qty	DOUBLE PRECISION	On-hand quantity
unit_cost	DOUBLE PRECISION	Unit cost
unit_cost_currency_id	BIGINT	Currency reference
expiry_date	DATE	Expiry date (for perishables)
attributes	JSONB	Additional attributes
feature_id	BIGINT	Feature/lot tracking reference

{schema}.item_site

Purpose: Item-site level cost parameters used by MEIO and supply planning (order cost, holding rate, cost price).

Column	Type	Description
item_id	BIGINT PK (composite)	Item
site_id	BIGINT PK (composite)	Site
order_cost	DOUBLE PRECISION	Fixed cost per order placement
holding_rate	DOUBLE PRECISION	Annual holding cost rate (fraction of unit cost)
cost_price	DOUBLE PRECISION	Standard cost price
price	DOUBLE PRECISION	Selling price
cost	DOUBLE PRECISION	Unit cost

{schema}.meio_parameter_set

Purpose: Named MEIO (Multi-Echelon Inventory Optimisation) parameter sets. Similar structure to config_parameters but specifically for MEIO-related configuration (service level targets, K-curve settings, reorder point parameters). Supports segment-level routing via meio_parameter_set_segment.

Column	Type	Description
id	BIGSERIAL PK	Parameter set identifier
scenario_id	BIGINT NOT NULL	Inventory scenario scope
parameter_type	TEXT	Type key (e.g. meio, kcurve, safety_stock)
name	TEXT NOT NULL	Set name
label	TEXT	Display label
parameters_set	JSONB	Configuration values
is_default	BOOLEAN	Whether this is the scenario-level default
sort_order	SMALLINT	Display order
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

{schema}.meio_parameter_set_segment

Purpose: Segment-level parameter routing for MEIO. Links a non-default MEIO parameter set to a segment so that items in that segment use this set.

Column	Type	Description
parameter_set_id	BIGINT FK → meio_parameter_set	MEIO parameter set
segment_id	BIGINT NOT NULL	Target segment

Primary key: (parameter_set_id, segment_id)

---

Domain: Supply Planning

{schema}.supply_run

Purpose: Log of each supply planning run. Records metadata, timing, and summary metrics.

Written by: Supply planning engine (files/api/supply_router.py)

Column	Type	Description
id	BIGSERIAL PK	Run identifier
scenario_id	BIGINT	Supply scenario used
pipeline_id	BIGINT	Pipeline this run belongs to
started_at	TIMESTAMPTZ	Run start time
completed_at	TIMESTAMPTZ	Run completion time
status	VARCHAR(20)	running / completed / failed
sku_count	INTEGER	Total SKUs in scope
active_sku_count	INTEGER	SKUs that generated orders
order_count	INTEGER	Total orders generated
horizon_weeks	SMALLINT	Planning horizon (default 52)
parameters	JSONB	Full parameter snapshot
metrics	JSONB	Summary metrics (total cost, total shortage, fill rate)
error_msg	TEXT	Error details if status = failed

{schema}.supply_order

Purpose: Individual planned supply orders. Each row is a single order for one item at one location.

Column	Type	Description
id	BIGSERIAL PK	Order identifier
run_id	BIGINT FK → supply_run	Parent run
scenario_id	BIGINT	Supply scenario
sku_id	BIGINT	Item (maps to item.id)
location_id	BIGINT	Destination site (maps to site.id)
source_location_id	BIGINT	Source site for TRANSFER orders
order_type	VARCHAR(20)	BUY / BUILD / REPAIR / TRANSFER / REPLENISH / RETURN
qty	DOUBLE PRECISION	Order quantity
release_week	SMALLINT	Week index (0–51) when to place/release
arrival_week	SMALLINT	Week index (0–51) when inventory arrives
unit_cost	DOUBLE PRECISION	Unit cost at time of order
order_cost	DOUBLE PRECISION	Fixed cost for this order
total_cost	DOUBLE PRECISION	qty × unit_cost + order_cost
route_id	BIGINT	Route used for this order
status	VARCHAR(20)	planned / constrained / delayed / expedited
attributes	JSONB	Additional order attributes

{schema}.supply_pegging

Purpose: Links each supply order to the demand buckets it satisfies. Enables demand/supply traceability.

Column	Type	Description
id	BIGSERIAL PK	Pegging record
run_id	BIGINT FK → supply_run	Parent run
order_id	BIGINT FK → supply_order	Supply order
sku_id	BIGINT	Item
location_id	BIGINT	Site
demand_week	SMALLINT	Week of the demand being covered
supply_week	SMALLINT	Week the supply arrives
qty	DOUBLE PRECISION	Quantity pegged from this order to this demand

{schema}.supply_inventory_projection

Purpose: Week-by-week inventory projection for each item/site combination in a supply run. The primary output used for inventory position charts.

Column	Type	Description
id	BIGSERIAL PK	Row identifier
run_id	BIGINT FK → supply_run	Parent run
scenario_id	BIGINT	Supply scenario
sku_id	BIGINT	Item
location_id	BIGINT	Site
week	SMALLINT	Week index (0–51)
projected_inventory	DOUBLE PRECISION	Projected on-hand at end of week
demand	DOUBLE PRECISION	Demand in this week
supply_received	DOUBLE PRECISION	Supply arriving in this week
shortage	DOUBLE PRECISION	Unmet demand (positive = shortage)
safety_stock	DOUBLE PRECISION	Safety-stock target for this week

Unique constraint: (run_id, sku_id, location_id, week)

{schema}.supply_exception

Purpose: Supply exceptions generated during a planning run (shortages, capacity violations, late orders, unmet demand, empty repair pools).

Column	Type	Description
id	BIGSERIAL PK	Exception identifier
run_id	BIGINT FK → supply_run	Parent run
scenario_id	BIGINT	Supply scenario
sku_id	BIGINT	Affected item
location_id	BIGINT	Affected site
week	SMALLINT	Week of the exception
exception_type	VARCHAR(50)	Shortage / CapacityExceeded / LateOrder / UnmetDemand / RepairPoolEmpty
severity	VARCHAR(20)	Critical / Warning / Info
qty	DOUBLE PRECISION	Quantity involved
message	TEXT	Human-readable exception description
attributes	JSONB	Additional detail
created_at	TIMESTAMPTZ	Exception creation timestamp

{schema}.supply_capacity

Purpose: Weekly capacity constraints for suppliers, plants, repair shops, and transport lanes.

Column	Type	Description
id	BIGSERIAL PK	Constraint record
scenario_id	BIGINT	Supply scenario
resource_type	VARCHAR(30)	SUPPLIER / PLANT / REPAIR_SHOP / TRANSPORT
resource_id	BIGINT	ID of the resource
week	SMALLINT	Week index
capacity_qty	DOUBLE PRECISION	Maximum capacity for this week
used_qty	DOUBLE PRECISION	Capacity consumed by planned orders

Supply route calendars → master.calendar + master.calendar_entry

Purpose: Working calendars for supply route availability (plant shutdowns, holidays, supplier closures). Uses the same shared Mirabelle calendar model as forecasting calendars. Managed via Settings → Master Data → Calendars.

master.route.ship_calendar_id references master.calendar.id. The supply engine reads the date-range entries and converts them to 52-week bitmasks at runtime relative to the current planning horizon start.

Table	Key columns	Notes
master.calendar	id, name, type (working/end2end), mode (available/not_available)	Header row; not_available means listed dates are closed
master.calendar_entry	calendar_id, date_from, date_to, label	One row per closed (or open) date range

Removed: {schema}.supply_calendar (bitmask table) was consolidated into master.calendar in June 2026. Any existing master.supply_calendar rows should be migrated: DROP TABLE master.supply_calendar;

{schema}.supply_scenario

Purpose: A named supply planning scenario. Links to a default parameter set and supports segment-level parameter overrides via supply_scenario_segment.

Column	Type	Description
id	BIGSERIAL PK	Scenario identifier
name	TEXT	Scenario name
description	TEXT	Description
parameter_id	INTEGER FK → parameters	Default supply parameter set
is_base	BOOLEAN	Whether this is the base scenario
created_by	TEXT	Creator email
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

{schema}.supply_scenario_segment

Purpose: Segment-level parameter overrides within a supply scenario. The resolution order walks rows by sort_order ASC; the first segment a SKU belongs to wins. Unmatched SKUs use the scenario's default parameter_id.

Column	Type	Description
id	SERIAL PK	Row identifier
scenario_id	BIGINT FK → supply_scenario	Parent scenario
segment_id	INTEGER FK → segment	Target segment
parameter_id	INTEGER FK → parameters	Override parameter set
sort_order	SMALLINT	Resolution priority (ascending)

{schema}.master_supply_orders_firm

Purpose: ERP-originated firm supply orders (purchase orders, work orders, transfers) that are committed and therefore constitute an input to the supply engine. Replaces the legacy mechanism of reading status IN ('firm','released','in_transit') from pipe_supply_orders.

Written by: ERP sync / manual upload / POST /api/supply/firm-orders[/import]. Read by: supply_runner._load_firm_supply_orders() (Python), allocation_runner.load_supplies() via the CH PG-engine mirror MASTER_supply_orders_firm, and GET /api/supply/orders?include_firm=true for UI display.

Column	Type	Description
id	BIGSERIAL PK	Row identifier
external_id	TEXT	ERP PO/work-order ID for round-tripping. Part of unique key.
item_id	BIGINT	Item (no FK — master_item is a view in tenant DBs)
site_id	BIGINT	Destination site
source_site_id	BIGINT	Source site (TRANSFER orders)
order_type	VARCHAR(20)	BUY, BUILD, REPAIR, TRANSFER, REPLENISH, RETURN, BALANCING
qty	DOUBLE	Order quantity (must be > 0)
release_dt	TIMESTAMPTZ	When the order is released. The supply engine buckets this to a week index relative to today() for the per-type block-horizon check.
arrival_dt	TIMESTAMPTZ	When inventory becomes available. Bucketed for scheduled_receipts.
route_id	BIGINT	Optional route reference
unit_cost, order_cost, total_cost	DOUBLE	Optional cost fields
erp_status	VARCHAR(20)	firm / released / in_transit / received. received rows are skipped by the engine and the allocation runner.
received_qty	DOUBLE	Partial-receipt tracking (engine subtracts from qty when building scheduled_receipts)
expiry_date	DATE	Shelf-life expiry
attributes	JSONB	Free-form metadata
source_system	TEXT	'erp_sap' / 'manual_upload' / 'legacy_pipe_supply_orders'. Part of unique key.
created_at, updated_at	TIMESTAMPTZ	Audit timestamps

Unique constraint: (external_id, source_system) — makes ERP imports idempotent.

Indexes: - idx_firm_so_item_site (item_id, site_id) - idx_firm_so_arrival (arrival_dt) - idx_firm_so_type_site (item_id, site_id, order_type, release_dt) — used by _load_firm_supply_orders to compute latest_firm_by_type - idx_firm_so_status (erp_status)

ClickHouse mirror: MASTER_supply_orders_firm (PostgreSQL engine, read-only). Used by allocation_runner.load_supplies() and the supply orders endpoint to UNION firm rows into responses.

---

Domain: Allocation

The allocation engine distributes limited supply (inventory, production capacity) across competing demands using configurable priority, starvation, reservation, fairness, routing, aging, and lateness policies.

{schema}.allocation_parameter_set

Purpose: Named parameter sets for each allocation policy type. Seven parameter_type values are supported:

parameter_type	Content
allocation_priority	PriorityScores + business weight multipliers
allocation_starvation	StarvationConfig (threshold, bonus, escalation)
allocation_reservation	ReservationConfig (lookahead, fences, penalty)
allocation_fairness	FairnessConfig (group_field, target_shares, cap)
allocation_routing	RoutingPolicy (one policy per row, keyed by policy_id)
allocation_aging	AgingPolicy (one policy per row, keyed by policy_id)
allocation_lateness	LatenessCurve (one curve per row, keyed by curve_id)

Global types (priority/starvation/reservation/fairness): one default per scenario; segment overrides via allocation_parameter_set_segment. Policy types (routing/aging/lateness): many per scenario; loaded into engine config HashMaps.

Column	Type	Description
id	BIGSERIAL PK	Parameter set identifier
scenario_id	BIGINT NOT NULL	Supply scenario scope
parameter_type	VARCHAR(40) NOT NULL	One of the seven types above
name	VARCHAR(120) NOT NULL	Set name
label	VARCHAR(120)	Human-readable display name
params	JSONB NOT NULL DEFAULT '{}'	Configuration values
is_default	BOOLEAN NOT NULL DEFAULT FALSE	Whether this is the scenario-level default for its type
sort_order	SMALLINT NOT NULL DEFAULT 0	Display order in UI
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Last update

Unique index: (scenario_id, parameter_type) WHERE is_default = TRUE — only one default per type per scenario.

{schema}.allocation_parameter_set_segment

Purpose: Segment-level linkage for allocation parameter sets. For global types: links a non-default set to a segment so that demands in that segment use this set. For policy types: controls which policies are visible when resolving demands within a segment.

Column	Type	Description
parameter_set_id	BIGINT FK → allocation_parameter_set	Parameter set
segment_id	BIGINT NOT NULL	Target segment

Primary key: (parameter_set_id, segment_id)

{schema}.allocation_run

Purpose: Log of each allocation run. Records the parameter sets used, timing, and summary metrics.

Column	Type	Description
id	BIGSERIAL PK	Run identifier
pipeline_id	BIGINT NOT NULL	Pipeline scope
scenario_id	BIGINT NOT NULL	Scenario scope
started_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Run start time
completed_at	TIMESTAMPTZ	Run completion time
status	VARCHAR(20) NOT NULL DEFAULT 'running'	running / completed / failed
demand_count	INTEGER	Total demands in scope
supply_count	INTEGER	Total supply lines
allocated_count	INTEGER	Successfully allocated demands
unfulfilled_count	INTEGER	Demands with shortfall
fill_rate	DOUBLE PRECISION	Overall fill rate
priority_param_id	BIGINT FK → allocation_parameter_set	Priority parameter set used
starvation_param_id	BIGINT FK → allocation_parameter_set	Starvation parameter set used
reservation_param_id	BIGINT FK → allocation_parameter_set	Reservation parameter set used
fairness_param_id	BIGINT FK → allocation_parameter_set	Fairness parameter set used
resolved_config	JSONB	Full assembled config snapshot (for auditability/replay)
metrics	JSONB	Summary metrics
error_msg	TEXT	Error details if failed

{schema}.allocation_result

Purpose: Individual demand-to-supply allocation assignments. Each row records which demand was allocated to which supply, the total score, breakdown, and any penalties.

Column	Type	Description
id	BIGSERIAL PK	Result record
run_id	BIGINT FK → allocation_run	Parent run
pipeline_id	BIGINT NOT NULL	Pipeline scope
scenario_id	BIGINT NOT NULL	Scenario scope
demand_id	VARCHAR(120) NOT NULL	Demand identifier
supply_id	VARCHAR(120) NOT NULL	Supply identifier
item_id	BIGINT	Item
site_id	BIGINT	Site
allocated_qty	DOUBLE PRECISION NOT NULL	Quantity allocated
total_score	DOUBLE PRECISION NOT NULL	Composite score
score_priority	DOUBLE PRECISION	Priority component
score_aging	DOUBLE PRECISION	Aging component
score_lateness	DOUBLE PRECISION	Lateness component
score_strategic	DOUBLE PRECISION	Strategic weight
score_revenue	DOUBLE PRECISION	Revenue weight
score_criticality	DOUBLE PRECISION	Criticality weight
penalty_routing	DOUBLE PRECISION	Routing penalty
penalty_fairness	DOUBLE PRECISION	Fairness penalty
penalty_reservation	DOUBLE PRECISION	Reservation penalty
explanation	TEXT	Human-readable explanation
score_breakdown	JSONB	Detailed score breakdown

{schema}.allocation_unfulfilled

Purpose: Demands that could not be fully satisfied. Records the shortfall quantity and the reasons (insufficient supply, capacity constraint, etc.).

Column	Type	Description
id	BIGSERIAL PK	Unfulfilled record
run_id	BIGINT FK → allocation_run	Parent run
pipeline_id	BIGINT NOT NULL	Pipeline scope
scenario_id	BIGINT NOT NULL	Scenario scope
demand_id	VARCHAR(120) NOT NULL	Demand identifier
item_id	BIGINT	Item
site_id	BIGINT	Site
requested_qty	DOUBLE PRECISION NOT NULL	Quantity demanded
allocated_qty	DOUBLE PRECISION NOT NULL DEFAULT 0	Quantity actually allocated
shortfall_qty	DOUBLE PRECISION NOT NULL	requested_qty - allocated_qty
reasons	TEXT[]	Array of reason codes

---

Domain: Causal / Asset-Driven Demand

{schema}.causal_asset_type

Purpose: Master table of asset types (e.g. aircraft model, bicycle type). Stores AOG (Aircraft On Ground) cost and mean AOG duration for aerospace use cases.

Column	Type	Description
asset_type_id	BIGSERIAL PK	Asset type identifier
code	TEXT UNIQUE	Short code
name	TEXT	Asset type name
aog_cost_per_day	DOUBLE PRECISION	Cost per day when asset is grounded
mean_aog_days	DOUBLE PRECISION	Mean duration of an AOG event
updated_at	TIMESTAMPTZ	Last update

{schema}.causal_asset

Purpose: Individual asset instances (V2). Each row is a specific physical asset with its own serial number and status.

Column	Type	Description
asset_id	TEXT PK	Asset identifier (e.g. tail number)
asset_type_id	BIGINT FK → causal_asset_type	Asset type
name	TEXT	Display name
serial_number	TEXT	Serial number
manufactured_at	DATE	Manufacture date
status	TEXT	active, retired, maintenance
attributes	TEXT	JSON stored as TEXT (ClickHouse compatibility)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

{schema}.causal_bom

Purpose: Bill of Materials for causal forecasting — links items (parts) to asset types. Supports LRU→SRU parent chains. V2 adds sub_asset_name and position for position-level failure rate differentiation.

Column	Type	Description
bom_id	BIGSERIAL PK	BOM line identifier
asset_type_id	BIGINT FK → causal_asset_type	Asset type this part belongs to
item_id	BIGINT	Item (part)
qty_per_asset	DOUBLE PRECISION	Quantity of this part per asset
usage_type_id	BIGINT FK → causal_usage_type	Usage type (flying hours, cycles, etc.)
is_lru	BOOLEAN	Whether this is a Line Replaceable Unit
repair_yield	DOUBLE PRECISION	Fraction repaired successfully (0–1)
parent_bom_id	BIGINT FK → causal_bom	Parent LRU for SRU hierarchies
sub_asset_name	TEXT	Sub-assembly name (V2)
position	TEXT	Position code (V2 — for position-level failure rates)
updated_at	TIMESTAMPTZ	Last update

Unique index (V2): (asset_type_id, item_id, COALESCE(sub_asset_name,''), COALESCE(position,''))

{schema}.causal_failure_rate

Purpose: Failure rates — how often a part fails per unit of asset usage. Uses a 4-level waterfall hierarchy: global → position → site+position → asset+position (most specific wins). V2 adds asset_id, site_id, position columns.

Column	Type	Description
failure_rate_id	BIGSERIAL PK	Row identifier
customer_id	INT FK → customer	Customer (NULL = global)
scenario_id	BIGINT	Scenario scope (default 1)
item_id	BIGINT	Part
failure_rate	DOUBLE PRECISION	Failures per unit of usage
usage_type_id	BIGINT FK → causal_usage_type	Usage type
period_start	DATE	Validity start
period_end	DATE	Validity end
asset_id	TEXT	Specific asset (V2 — NULL = any)
site_id	BIGINT	Specific site (V2 — NULL = any)
position	TEXT	Position code (V2 — NULL = any)
updated_at	TIMESTAMPTZ	Last update

{schema}.causal_asset_deployment

Purpose: Records when and where each asset is deployed. Used to compute exposure time for demand calculation.

Column	Type	Description
deployment_id	BIGSERIAL PK	Deployment identifier
asset_id	TEXT	Asset identifier
site_id	BIGINT FK → site	Base site
customer_id	INT FK → customer	Operating customer
deploy_date	DATE	Date of deployment
qty	DOUBLE PRECISION	Number of units deployed
scenario_id	BIGINT	Scenario scope
notes	TEXT	Optional notes
created_at	TIMESTAMPTZ	Record creation timestamp

{schema}.causal_asset_coverage

Purpose: Defines which site services which asset (coverage allocation). Used to spread causal demand across sites.

Column	Type	Description
coverage_id	BIGSERIAL PK	Coverage record
asset_id	TEXT	Specific asset (NULL = any of this type)
asset_type_id	BIGINT FK → causal_asset_type	Asset type
site_id	BIGINT NOT NULL FK → site	Servicing site
customer_id	INT FK → customer	Customer
coverage_pct	DOUBLE PRECISION	Fraction of demand serviced by this site (0–1)
scenario_id	BIGINT	Scenario scope
valid_from	DATE	Coverage validity start
valid_to	DATE	Coverage validity end
created_at	TIMESTAMPTZ	Record creation timestamp

{schema}.causal_scenarios

Purpose: Named causal forecasting scenarios with fleet, failure rate, usage, and coverage overrides as JSONB.

Column	Type	Description
scenario_id	BIGSERIAL PK	Scenario identifier
name	TEXT	Scenario name
description	TEXT	Description
is_base	BOOLEAN	Whether this is the base scenario
created_by	TEXT	Creator email
created_at	TIMESTAMPTZ	Creation timestamp
fleet_overrides	JSONB	Fleet quantity overrides (V2)
failure_rate_overrides	JSONB	Failure rate overrides (V2)
usage_overrides	JSONB	Usage factor overrides (V2)
coverage_overrides	JSONB	Coverage allocation overrides (V2)
linked_meio_scenario_id	BIGINT	Links to an MEIO scenario

{schema}.causal_fleet_plan

Purpose: Planned deployment of asset types to customers per period. Used to compute total fleet exposure.

Column	Type	Description
fleet_plan_id	BIGSERIAL PK	Plan record
scenario_id	BIGINT FK → forecast_scenarios	Scenario scope
asset_type_id	BIGINT FK → causal_asset_type	Asset type
customer_id	INT FK → customer	Customer operating these assets
asset_qty	INTEGER	Number of assets
period_start	DATE	Plan period start
period_end	DATE	Plan period end

{schema}.causal_usage_type

Purpose: Lookup for causal usage types (e.g. flying hours, cycles, landings). Referenced by causal_bom and causal_failure_rate.

Column	Type	Description
id	BIGSERIAL PK	Usage type identifier
name	TEXT UNIQUE	Usage type name (e.g. flying_hours, cycles)

{schema}.causal_asset_usage

Purpose: Planned usage (e.g. flying hours) per asset per period. Used by the causal forecasting engine to compute expected demand from fleet exposure and failure rates.

Column	Type	Description
usage_id	BIGSERIAL PK	Usage record
scenario_id	BIGINT FK → forecast_scenarios	Scenario scope
asset_type_id	BIGINT FK → causal_asset_type	Asset type
customer_id	INT FK → customer	Customer
usage_type_id	BIGINT FK → causal_usage_type	Usage type
usage_qty	DOUBLE PRECISION	Planned usage quantity per period
period_start	DATE	Period start
period_end	DATE	Period end

Maintenance Tables

{schema}.maintenance_event

Purpose: Catalogue of maintenance event types (e.g. "12,000-hour overhaul", "annual inspection").

Column	Type	Description
id	BIGSERIAL PK	Event identifier
name	TEXT	Event name
note	TEXT	Optional notes

{schema}.maintenance_bom

Purpose: Parts required for each maintenance event, with likelihood-of-removal factor.

Column	Type	Description
id	BIGSERIAL PK	Row identifier
event_id	BIGINT FK → maintenance_event	Maintenance event
item_id	BIGINT FK → item	Required part
qty	DOUBLE PRECISION	Quantity required
likelihood_of_removal	DOUBLE PRECISION	Probability this part is actually replaced (0–1)

{schema}.maintenance_scenario_event

Purpose: Junction table linking forecast scenarios to maintenance events.

Column	Type	Description
scenario_id	BIGINT FK → forecast_scenarios	Forecast scenario (composite PK)
event_id	BIGINT FK → maintenance_event	Event (composite PK)

{schema}.maintenance_scenario_event_date

Purpose: Specific occurrence dates for each scenario/event combination, optionally scoped to a site.

Column	Type	Description
id	BIGSERIAL PK	Occurrence record
scenario_id	BIGINT	Forecast scenario
event_id	BIGINT	Maintenance event
event_date	DATE	Date of the maintenance event
site_id	BIGINT FK → site	Where the event occurs
qty	DOUBLE PRECISION	Number of assets/units at this occurrence

---

Domain: BOM and Routes

{schema}.route_type

Purpose: Lookup for route planning types (BUY, MAKE, TRANSFER).

Column	Type	Description
id	BIGINT PK	Type identifier
xuid	TEXT	External identifier
name	TEXT	Type name
description	TEXT	Description
planning_type	TEXT	BUY / MAKE / TRANSFER
calc_supply_type_id	BIGINT	Supply type for calculation
calc_dep_demand_type_id	BIGINT	Dependent demand type

{schema}.route

Purpose: Supply routes — the lane from source to destination for each item. Defines lead times, costs, order constraints, and calendars.

Column	Type	Description
id	BIGINT PK	Route identifier
item_id	BIGINT	Item
site_id	BIGINT	Destination site
supplier_id	BIGINT	Supplier (for BUY routes)
type_id	BIGINT FK → route_type	Route type
source_item_id	BIGINT	Source item (for MAKE/TRANSFER)
source_site_id	BIGINT	Source site
min_qty	DOUBLE PRECISION	Minimum order quantity
mult_qty	DOUBLE PRECISION	Order quantity multiple
max_qty	DOUBLE PRECISION	Maximum order quantity
lead_time	SMALLINT	Lead time in weeks
pick_pack_time	SMALLINT	Weeks for pick/pack
transit_time	SMALLINT	Weeks in transit
inspection_time	SMALLINT	Weeks for inspection
safety_lead_time	SMALLINT	Buffer lead time
ptf	SMALLINT	Planning time fence in weeks
yield	DOUBLE PRECISION	Expected yield fraction
unit_cost	DOUBLE PRECISION	Unit cost on this route
order_cost	DOUBLE PRECISION	Fixed order cost
end_date	DATE	Route expiry date
*_calendar_id	BIGINT	Calendar IDs for each lead-time component

{schema}.bill_of_material

Purpose: Parent-child component relationships for manufactured items.

Column	Type	Description
id	BIGINT PK	BOM line identifier
item_id	BIGINT	Parent item
site_id	BIGINT	Parent site
child_item_id	BIGINT	Component item
child_site_id	BIGINT	Component site
item_qty	DOUBLE PRECISION	Parent quantity per BOM
child_qty	DOUBLE PRECISION	Component quantity per parent
attach_rate	DOUBLE PRECISION	Fraction of parents that use this component
start_date	DATE	BOM validity start
end_date	DATE	BOM validity end
"offset"	SMALLINT	Time offset in weeks
scrap	DOUBLE PRECISION	Scrap factor

{schema}.item_chain

Purpose: Item supersession and substitution chains. Links old items to their replacements.

Column	Type	Description
id	BIGINT PK	Chain record
description	TEXT	Description
item_id	BIGINT	Parent (being superseded)
site_id	BIGINT	Site
child_item_id	BIGINT	Replacement item
child_site_id	BIGINT	Replacement site
policy_id	BIGINT	Supersession policy

{schema}.route_resource

Purpose: Maps supply routes to resource types (supplier, plant, repair shop, transport). Used by the supply engine for capacity-constrained planning.

Column	Type	Description
id	BIGSERIAL PK	Resource mapping record
route_id	BIGINT FK → route	Supply route
resource_type	VARCHAR(30)	SUPPLIER / PLANT / REPAIR_SHOP / TRANSPORT
resource_id	BIGINT	Resource identifier

---

Domain: Configuration

{schema}.parameters

Purpose: All pipeline and system configuration. Each row is a named parameter set for a specific subsystem. The parameters_set JSONB column holds the actual configuration.

Column	Type	Description
id	SERIAL PK	Parameter set identifier
parameter_type	TEXT	Subsystem key: data_source, outlier_detection, characterization, forecasting, backtesting, meio, parallel, auth, segmentation, supply
name	TEXT	Set name (e.g. Default, High seasonality)
label	TEXT	Display label in UI
parameters_set	JSONB	Configuration key-value pairs
description	TEXT	Human-readable description
is_default	BOOLEAN	Active default for this parameter_type
sort_order	INTEGER	Display order in UI
updated_at	TIMESTAMPTZ	Last update
created_at	TIMESTAMPTZ	Creation timestamp

Unique constraint: (parameter_type, name)

{schema}.parameter_segment

Purpose: Associates parameter sets with segments for segment-based parameter routing.

Column	Type	Description
id	SERIAL PK	Association identifier
parameter_id	INTEGER FK → parameters	Parameter set
segment_id	INTEGER FK → segment	Target segment

{schema}.series_parameter_assignment

Purpose: Per-series parameter overrides. Allows specific SKUs to use non-default parameter sets for any pipeline step. Part of the 3-level ParameterResolver hierarchy.

Column	Type	Description
unique_id	TEXT PK	Series identifier
item_id	BIGINT	Item
site_id	BIGINT	Site
forecasting_parameter_id	INTEGER FK → parameters	Forecasting parameter override
outlier_detection_parameter_id	INTEGER FK → parameters	Outlier detection override
characterization_parameter_id	INTEGER FK → parameters	Characterisation override
backtesting_parameter_id	INTEGER FK → parameters	Backtesting / method-selection override (renamed from best_method)
valuation_parameter_id	INTEGER FK → parameters	Valuation/MEIO override
allocation_parameter_id	INTEGER FK → allocation_parameter_set	Allocation priority parameter override
updated_at	TIMESTAMPTZ	Last update

Note: evaluation_parameter_id and best_method_parameter_id were dropped (the evaluation type was purged; best_method was renamed to backtesting). The per-segment series_parameter_map snapshot (synced to CH PIPE_preprocessed) additionally carries meio_parameter_ids, hyperparameter_set_id, supply_parameter_id, meio_target_parameter_id, order_frequency_parameter_id, and the six allocation_* policy columns. netting_parameter_id / method_selection_parameter_id were never live (their config lives in the forecast parameter set / scenario JSON respectively).

{schema}.segment

Purpose: Named groupings of SKUs. Used to apply different parameter sets, access controls, and ABC classifications to different product families.

Column	Type	Description
id	SERIAL PK	Segment identifier
name	TEXT UNIQUE	Segment name (seeded with All)
description	TEXT	Description
criteria	JSONB	Filter criteria for dynamic assignment
is_default	BOOLEAN	Whether this is the catch-all default segment
characterization_horizon	INTEGER	Horizon for this segment's characterisation (default 24)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

{schema}.segment_membership

Purpose: Records which series belong to which segment. Populated by the segmentation process.

Column	Type	Description
id	SERIAL PK	Membership record
segment_id	INTEGER FK → segment	Segment
unique_id	TEXT	Series identifier
item_id	BIGINT	Item
site_id	BIGINT	Site
assigned_at	TIMESTAMPTZ	Assignment timestamp

{schema}.abc_configuration

Purpose: Configuration for ABC/XYZ classification runs. Multiple configurations can coexist.

Column	Type	Description
id	SERIAL PK	Configuration identifier
name	TEXT UNIQUE	Configuration name
metric	TEXT	hits / demand / value
lookback_months	INTEGER	Historical window for classification (default 12)
granularity	TEXT	item_site or item
method	TEXT	cumulative_pct / rank_pct / rank_absolute
class_labels	JSONB	Labels array (e.g. ["A","B","C"])
thresholds	JSONB	Threshold percentiles (e.g. [80, 95])
segment_id	INTEGER FK → segment	Apply results as a segment
is_active	BOOLEAN	Whether this config is active
dynamic_anchor	BOOLEAN	Use dynamic date anchor for lookback
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

{schema}.abc_results

Purpose: Results of an ABC classification run.

Column	Type	Description
id	SERIAL PK	Result record
config_id	INTEGER FK → abc_configuration	Configuration used
unique_id	TEXT	Series identifier
class_label	TEXT	Assigned class (e.g. A, B, C)
metric_value	DOUBLE PRECISION	Metric value for this series
rank	INTEGER	Rank within the classification
cumulative_pct	DOUBLE PRECISION	Cumulative percentage at this rank
computed_at	TIMESTAMPTZ	Computation timestamp

---

Domain: Workflow / Exception Management

{schema}.interact_exception_log

Purpose: Tracks supply chain exceptions at the item/site level with workflow states. Uses the interact_ prefix (user-editable tables). Populated automatically at the end of each Exceptions pipeline step via exceptions_runner.generate_exception_log().

Column	Type	Description
id	SERIAL PK	Exception record
pipeline_id	INTEGER	Pipeline scope
item_id	INTEGER	Affected item
site_id	INTEGER	Affected site
exception_types	TEXT[]	Array of exception type codes
issues	JSONB	Detailed issue list
status	TEXT	open / reviewed / snoozed / actioned
snoozed_until	TIMESTAMPTZ	If snoozed, when to re-surface
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update
resolved_at	TIMESTAMPTZ	When resolved

{schema}.io_overrides

Purpose: Inventory optimisation overrides — service-level, EOQ, and fill-rate overrides per item/site/pipeline/scenario.

Column	Type	Description
id	SERIAL PK	Override record
pipeline_id	INTEGER	Pipeline scope
scenario_id	INTEGER	Scenario scope
item_id	INTEGER	Item
site_id	INTEGER	Site
sl_override	NUMERIC	Service-level override (0–1)
eoq_override	NUMERIC	EOQ quantity override
fr_override	NUMERIC	Fill-rate override (0–1)
created_by	TEXT	Creator email
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

{schema}.order_approvals

Purpose: Approval records for supply orders (planned → approved workflow).

Column	Type	Description
id	SERIAL PK	Approval record
order_id	INTEGER	Supply order ID
approved_by	TEXT	Approver email
approved_at	TIMESTAMPTZ	Approval timestamp
approved_quantity	NUMERIC	Approved quantity (may differ from planned)
approved_availability_date	DATE	Committed availability date

{schema}.auto_approval_rule

Purpose: Rules that automatically approve supply order recommendations matching specific criteria (type, quantity range, item/site patterns). Evaluated by the recommendation approval workflow.

Column	Type	Description
id	BIGSERIAL PK	Rule identifier
recommendation_type	TEXT	Type of recommendation this rule applies to
name	TEXT	Rule name
description	TEXT	Rule description
conditions	JSONB	Match conditions (item category, quantity range, etc.)
action	TEXT	approve / reject
is_active	BOOLEAN	Whether the rule is currently enabled
created_by	TEXT	Creator email
created_at	TIMESTAMPTZ	Creation timestamp

Indexes: idx_aar_type_active (recommendation_type, is_active)

{schema}.recommendation_approval

Purpose: Records of approval/rejection decisions for supply order recommendations. Links to an optional auto-approval rule.

Column	Type	Description
id	BIGSERIAL PK	Approval record
pipeline_id	BIGINT NOT NULL	Pipeline scope
scenario_id	BIGINT	Scenario scope
recommendation_type	TEXT	Type of recommendation
item_id	BIGINT	Item
site_id	BIGINT	Site
decision	TEXT	approved / rejected / pending
decided_by	TEXT	Approver email (or auto if rule-based)
decided_at	TIMESTAMPTZ	Decision timestamp
auto_rule_id	BIGINT FK → auto_approval_rule	Auto-approval rule that triggered this decision (NULL = manual)
source	TEXT	Source of the recommendation
source_detail	TEXT	Additional source detail
notes	TEXT	Approver notes

Indexes: idx_rec_appr_type (recommendation_type, decided_at DESC), idx_rec_appr_pipeline (pipeline_id, decided_at DESC), idx_rec_appr_item_site (item_id, site_id)

{schema}.planning_event

Purpose: Calendar-style planning events (e.g. product launches, promotions, supply disruptions). Used by the planning workflow to annotate and coordinate decisions.

Column	Type	Description
id	BIGSERIAL PK	Event identifier
pipeline_id	BIGINT NOT NULL FK → scenario_pipeline	Pipeline scope
scenario_id	BIGINT	Scenario scope
event_date	DATE NOT NULL	When the event occurs
event_type	TEXT NOT NULL	promotion / launch / disruption / shutdown / custom
title	TEXT	Event title
description	TEXT	Event description
impact	JSONB	Expected impact details
created_by	TEXT	Creator email
created_at	TIMESTAMPTZ	Creation timestamp

Indexes: idx_event_date (event_date, event_type), idx_event_pipeline (pipeline_id, event_date)

{schema}.series_param_overrides

Purpose: Per-series parameter overrides (beyond series_parameter_assignment). Allows granular parameter tuning at the series level for any parameter type.

Column	Type	Description
id	SERIAL PK	Override record
unique_id	TEXT	Series identifier
parameter_type	TEXT	Parameter type key
overrides	JSONB	Override key-value pairs
updated_at	TIMESTAMPTZ	Last update

{schema}.annotation

Purpose: User-created annotations attached to any entity (series, item, site). Used for collaborative notes and context sharing.

Column	Type	Description
id	BIGSERIAL PK	Annotation identifier
entity_type	TEXT	Target entity type (series, item, site)
entity_id	TEXT	Target entity identifier
content	TEXT	Annotation text
created_by	TEXT	Creator email
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

{schema}.bookmark

Purpose: User bookmarks for quick navigation to frequently accessed series, items, or pages.

Column	Type	Description
id	BIGSERIAL PK	Bookmark identifier
user_email	TEXT	User who created the bookmark
entity_type	TEXT	Target entity type
entity_id	TEXT	Target entity identifier
label	TEXT	Display label
created_at	TIMESTAMPTZ	Creation timestamp

{schema}.process_log

Purpose: Records every pipeline step execution. Supports live log streaming to the frontend.

Column	Type	Description
id	SERIAL PK	Log entry
run_id	TEXT	Unique run identifier (UUID string)
step_name	TEXT	Step name (e.g. ETL, Outlier Detection, Forecasting)
status	TEXT	pending / running / completed / failed
started_at	TIMESTAMPTZ	Step start time
ended_at	TIMESTAMPTZ	Step end time
duration_s	DOUBLE PRECISION	Duration in seconds
rows_processed	INTEGER	Number of series/rows processed
error_message	TEXT	Error details if status = failed
log_tail	TEXT	Last N lines of log output
pipeline_id	BIGINT	Pipeline this run belongs to
scenario_id	INTEGER	Scenario scope
created_at	TIMESTAMPTZ	Record creation timestamp

{schema}.sys_audit_log

Purpose: Immutable audit trail for all significant state changes — configuration, security, forecasting, pipeline, and model events. The single surface queried by the /audit UI (GET /api/audit-log). Writes go through the centralized helper in files/utils/audit.py (audit_log in-transaction, audit_log_standalone fire-and-forget).

Column	Type	Description
id	SERIAL PK	Audit entry
entity_type	TEXT	Type of entity changed (see catalog below)
entity_id	INTEGER	ID of the changed entity (NULL for auth / aggregate events)
action	TEXT	Verb performed on the entity (see catalog below)
old_value	JSONB	Previous state
new_value	JSONB	New state
changed_by	TEXT	User email or system
ip	TEXT	Client IP of the actor (added by enrichment migration)
user_agent	TEXT	User-Agent header of the actor
request_id	TEXT	Correlation id (X-Request-Id) / workflow job_id
outcome	TEXT	success / failure / denied (auth + run lifecycle)
created_at	TIMESTAMPTZ	Change timestamp (wall-clock, never planning_today)

The ip, user_agent, request_id, and outcome columns are added by the idempotent migration files/DDL/migrations/audit_log_enrich.sql (also ensured by db.init_schema on every startup). All are nullable; the helper writes them only when present, so it works before, during, and after the migration.

(entity_type, action) event catalog

entity_type	actions
parameter	create, update, delete, reorder
parameter_segment	update
parameter_set	create, update, delete (forecast & MEIO parameter sets)
segment	create, update, delete
meio_scenario	create, update, delete, clone
scenario	delete (forecast / exception / classification / causal / inventory cascade wipes)
pipeline	create, update, delete, clone, copy_from, promote_prod
pipeline_segment	attach, detach
planning_date	set, delete
override	set, delete (param + hyperparameter series overrides)
adjustment	set, delete (forecast adjustments / overrides)
import	firm_orders, fleet_plan, bom (CSV / bulk data ingress)
historization	freeze (mirrored from sys_historization_log)
process_run	run, run_start, run_complete, run_fail
auth	login, logout, failed_login, change_password, switch_account
user	create, update, delete, role_change, disable, enable
auto_approval_rule	create, update (rule-set upload + governance transition)
model	promote, retrain (auto-approval champion lifecycle)
export	csv, excel (data egress — best-effort)

Sibling audit tables (not in the /audit UI): sys_historization_log (freeze/pin/unpin operations) and interact_planning_event (planning events + auto-approval recommendation transition chain, surfaced via GET /api/auto-approval/{id}/audit).

---

Domain: Authentication

{schema}.users

Purpose: Per-tenant user accounts. Admins and regular users. SuperAdmins are stored in master.superadmins, not here.

Column	Type	Description
id	UUID PK	User identifier
email	TEXT UNIQUE	Login email
display_name	TEXT	Display name in UI
hashed_password	TEXT	bcrypt hash; NULL for OAuth-only accounts
auth_provider	TEXT	local / microsoft / google
role	TEXT	admin or user
is_active	BOOLEAN	Disable without deleting
allowed_segments	JSONB	List of segment IDs the user can view
can_run_process	BOOLEAN	Permission to trigger pipeline runs
can_create_override	BOOLEAN	Permission to apply forecast overrides
allowed_segments_edit	JSONB	List of segment IDs the user can edit
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

{schema}.revoked_tokens

Purpose: Blocklist of revoked JWT token identifiers (jti claims). Checked on every authenticated request to implement logout.

Column	Type	Description
jti	TEXT PK	JWT ID from the token's jti claim
revoked_at	TIMESTAMPTZ	Revocation timestamp

{schema}.historization_log

Purpose: Log of all historization (freeze) operations. Records each freeze, pin, un-pin, and retention cleanup event for auditability.

Column	Type	Description
id	BIGSERIAL PK	Log entry
pipeline_id	BIGINT	Pipeline that was frozen
archive_date	DATE	Archive date of the operation
operation	TEXT	freeze / pin / unpin / retention_delete
created_by	TEXT	User or system that triggered the operation
snapshot_ts	TIMESTAMPTZ	Exact timestamp
details	JSONB	Additional operation details
created_at	TIMESTAMPTZ	Log creation timestamp

---

Domain: Overrides

{schema}.forecast_adjustments

Purpose: Planner-applied adjustments and overrides to specific forecast dates. Supports both percentage adjustments and absolute overrides.

Column	Type	Description
id	SERIAL PK	Adjustment record
unique_id	TEXT	Series identifier
forecast_date	DATE	The forecast date being adjusted
adjustment_type	TEXT	adjustment (multiplicative %) or override (absolute value)
value	DOUBLE PRECISION	Adjustment value
note	TEXT	Planner's note explaining the adjustment
created_by	TEXT	Creator (ui for UI-driven changes)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

Unique constraint: (unique_id, forecast_date, adjustment_type)

{schema}.series_hyperparameters_overrides

Purpose: Per-series, per-method hyperparameter overrides. Allows specific SKUs to use non-default model hyperparameters.

Column	Type	Description
id	SERIAL PK	Override record
unique_id	TEXT	Series identifier
method	TEXT	Model name
overrides	JSONB	Hyperparameter key-value overrides
updated_at	TIMESTAMPTZ	Last update

Unique constraint: (unique_id, method)

---

Domain: Scenarios and Pipelines

{schema}.forecast_scenario_type

Purpose: Lookup for scenario types. Seeded with three standard values.

Seeded values: Direct demand, Causal forecasting, Planned maintenance

{schema}.forecast_scenarios

Purpose: Named forecast scenarios. Each scenario is associated with a type, demand type, and a set of parameter and demand overrides.

Column	Type	Description
scenario_id	BIGSERIAL PK	Scenario identifier (seeded: 1 = Base)
name	TEXT	Scenario name
description	TEXT	Description
is_base	BOOLEAN	Whether this is the base scenario
scenario_type_id	BIGINT FK → forecast_scenario_type	Scenario type
demand_type_id	BIGINT FK → demand_type	Demand type
status	TEXT	pending / running / complete / failed
run_at	TIMESTAMPTZ	Last run timestamp
error_msg	TEXT	Error if failed
created_by	TEXT	Creator email
created_at	TIMESTAMPTZ	Creation timestamp
param_overrides	JSONB	Parameter overrides for this scenario
demand_overrides	JSONB	Demand overrides for this scenario

{schema}.scenario_pipeline

Purpose: The central pipeline record that ties all scenarios and runs together. Selected by the user via the left toolbar dropdown in the UI.

Column	Type	Description
pipeline_id	BIGSERIAL PK	Pipeline identifier
name	TEXT	Pipeline name
description	TEXT	Description
series_scenario_id	BIGINT FK → forecast_scenarios	Direct demand scenario
causal_scenario_id	BIGINT FK → causal_scenarios	Causal scenario
maintenance_scenario_id	BIGINT FK → forecast_scenarios	Maintenance scenario
inventory_scenario_id	BIGINT FK → inventory_scenario	MEIO scenario
supply_scenario_id	BIGINT	Supply scenario
meio_scenario_id	BIGINT	MEIO parameter scenario
causal_pct	DOUBLE PRECISION	Blend % from causal forecasting
series_pct	DOUBLE PRECISION	Blend % from direct demand
maintenance_pct	DOUBLE PRECISION	Blend % from maintenance
status	TEXT	draft / running / complete
data_source_type	TEXT	master (ETL/ERP) or pipeline (copied from another run)
data_source_pipeline_id	BIGINT FK → self	Source pipeline if copying
created_by	TEXT	Creator email
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

Constraint: causal_pct + series_pct + maintenance_pct = 100.0 or all three must be 0.

{schema}.scenario_pipeline_segment

Purpose: Controls which segments are in scope for each pipeline run.

Column	Type	Description
id	BIGSERIAL PK	Record identifier
pipeline_id	BIGINT FK → scenario_pipeline	Pipeline
segment_id	BIGINT FK → segment	Segment in scope

---

Domain: Calendar

{schema}.calendar

Purpose: Named working calendars used in supply route lead-time calculations.

Column	Type	Description
id	BIGSERIAL PK	Calendar identifier
name	TEXT UNIQUE	Calendar name
type	TEXT	working or end2end
mode	TEXT	available or not_available
description	TEXT	Description
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update

{schema}.calendar_entry

Purpose: Date ranges within a calendar. Combined with the calendar's mode, defines working or non-working periods.

Column	Type	Description
id	BIGSERIAL PK	Entry identifier
calendar_id	BIGINT FK → calendar	Parent calendar
date_from	DATE	Period start
date_to	DATE	Period end (must be ≥ date_from)
label	TEXT	Optional label (e.g. "Christmas shutdown")

---

Import Schema (import.*)

The import schema is a stateless staging area for ERP and external data. Tables are flat, forgiving landing zones with no foreign key constraints. The ETL pipeline reads from import.* tables, transforms and validates the data, and writes it into scenario.* (and master.* in the tenant DB). Customers truncate and repopulate import.* tables whenever they want — there are no audit columns and no _loaded_at tracking.

Drop safety

The import schema is safe to drop and re-create. Run files/DDL/import_schema.sql to restore. No business data is lost because import tables are temporary staging copies.

Audit Tables

import.import_run

Purpose: Log of each import ETL run. Records timing, status, and summary counts.

Column	Type	Description
id	BIGSERIAL PK	Run identifier
triggered_by	TEXT	User or system that triggered the import
started_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Run start time
completed_at	TIMESTAMPTZ	Run completion time
status	TEXT NOT NULL DEFAULT 'running'	running / completed / completed_with_warnings / failed
total_rows_loaded	INTEGER DEFAULT 0	Total rows loaded across all tables
total_rows_rejected	INTEGER DEFAULT 0	Total rows rejected
error_message	TEXT	Error details if failed
notes	TEXT	Additional notes

import.import_table_result

Purpose: Per-table results within an import run. One row per staging table processed.

Column	Type	Description
id	BIGSERIAL PK	Result identifier
run_id	BIGINT FK → import_run	Parent run
table_name	TEXT NOT NULL	Staging table name
target_table	TEXT NOT NULL	Target table in scenario.* or master.*
load_mode	TEXT NOT NULL	truncate_replace / upsert
rows_staged	INTEGER DEFAULT 0	Rows in staging table
rows_loaded	INTEGER DEFAULT 0	Rows successfully loaded
rows_rejected	INTEGER DEFAULT 0	Rows rejected
error_sample	TEXT	Sample error messages
started_at	TIMESTAMPTZ	Table processing start
completed_at	TIMESTAMPTZ	Table processing end
duration_ms	INTEGER	Processing duration in milliseconds

Unique constraint: (run_id, table_name)

Staging Tables

All staging tables use xuid (TEXT) as the universal external key. No foreign key constraints — FK resolution happens in the ETL SQL. Primary keys are on natural keys so bulk-reloads without TRUNCATE are safe.

Staging Table	Target in scenario.* / master.*	Load Mode	Natural Key
import.item_type	scenario.master_item_type	truncate_replace	xuid
import.site_type	scenario.master_site_type	truncate_replace	xuid
import.demand_type	scenario.master_demand_type	truncate_replace	name
import.on_hand_type	scenario.master_on_hand_type	truncate_replace	xuid
import.route_type	scenario.master_route_type	truncate_replace	xuid
import.causal_usage_type	scenario.master_causal_usage_type	truncate_replace	name
import.item	scenario.master_item	upsert	xuid
import.site	scenario.master_location	upsert	xuid
import.customer	scenario.master_customer	upsert	code
import.customer_site	scenario.master_customer_site	upsert	customer_code + site_xuid
import.channel	scenario.master_channel	upsert	xuid
import.item_location	scenario.master_item_location	upsert	item_xuid + site_xuid
import.demand_actuals	scenario.master_demand_actuals	truncate_replace	composite
import.on_hand	scenario.master_on_hand	upsert	item_xuid + site_xuid + type_xuid
import.bill_of_material	scenario.master_bill_of_material	upsert	parent_item_xuid + site_xuid + child_item_xuid
import.route	scenario.master_route	upsert	item_xuid + site_xuid
import.item_chain	scenario.master_item_chain	upsert	item_xuid + site_xuid + child_item_xuid
import.calendar	scenario.master_calendar	upsert	name
import.calendar_entry	scenario.master_calendar_entry	upsert	calendar_name + date_from + date_to
import.currency_conversion	scenario.master_currency_conversion	upsert	from_currency + to_currency + effective_date
import.firm_supply_orders	scenario.master_supply_orders_firm	upsert	external_id + source_system
import.causal_asset_type	scenario.master_causal_asset_type	upsert	code
import.causal_asset	scenario.master_causal_asset	upsert	asset_id
import.causal_bom	scenario.master_causal_bom	upsert	composite
import.causal_failure_rate	scenario.master_causal_failure_rate	upsert	full waterfall key
import.maintenance_event	scenario.master_maintenance_event	upsert	name
import.maintenance_bom	scenario.master_maintenance_bom	upsert	composite

master_channel location

master_channel lives in {schema} (confirmed in schema.sql / db.py) — not in the master PostgreSQL schema.

---

ClickHouse (PIPE_* Tables)

All pipeline output tables live in ClickHouse (single shared instance, default database). Tenant isolation is by pipeline_id; there is no per-tenant CH database.

The schema DDL is in files/DDL/ch_schema.sql. All tables use ReplacingMergeTree for idempotent re-runs.

Naming Convention

PIPE_{step}_{entity} — e.g. PIPE_supply_orders, PIPE_forecast_netting.

Table Reference

Table	Populated by	Purpose
PIPE_forecast_fingerprints	forecasting/ step	Pipeline-scoped forecast skip cache: combined input fingerprint MD5(data_hash\|param_hash) per (pipeline_id, scenario_id, item_id, site_id). ReplacingMergeTree(computed_at), PARTITION BY (pipeline_id, scenario_id), read with FINAL. Isolates the forecast skip per pipeline so multiple pipelines on one tenant cannot clobber each other's skip state
PIPE_forecast_netting	netting/forecast_netting.py	Netted demand: stat + return + maintenance vs firm orders. Includes is_past flag, consumption_enabled, netting_direction, return_forecast_qty, return_netted_qty
PIPE_supply_orders	Supply step (Rust engine)	Planned BUY / TRANSFER / BUILD / REPAIR / RETURN orders
PIPE_supply_inventory_projection	Supply step	Week-by-week projected OH, demand, receipts, safety stock per item×site
PIPE_supply_exceptions	Supply step	Shortages, capacity exceedances, late orders per week. exception_type_label is the human-readable description
PIPE_supply_capacity	Supply step (_persist_capacity_usage)	Resource utilisation vs capacity per week. No id column — keyed by (pipeline_id, scenario_id, resource_id, week)
PIPE_meio_results	MEIO step	committed_buffer, fill_rate, wait_time, marginal_value per item×site
PIPE_series_characteristics	Characterization step	has_seasonality, has_trend, is_intermittent, adi, zero_ratio
PIPE_series_exceptions	Exceptions step	Series-level exception flags per (item_id, site_id, exc_id): exc_type, severity, metric_value, threshold. Read by exceptions_runner.generate_exception_log() to populate interact_exception_log
PIPE_preprocessed	Segmentation step (segmentation.py:build_series_parameter_map)	Resolved parameter IDs per series: forecasting_parameter_id, outlier_detection_parameter_id, characterization_parameter_id, supply_parameter_id, allocation_parameter_id. One row per (pipeline_id, item_id, site_id). Used by the forecast and supply steps to look up their parameter sets efficiently
PIPE_process_log	All steps (via ProcessLogger)	Step execution log with started_at, ended_at, status, rows_processed, error_message, log_tail
PIPE_demand_corrections	Outlier detection (embedded in forecast)	Original vs corrected values, detection method, z-score, bounds
PIPE_forecast_point_values	forecasting/ step	Normalised point forecast values per (series, method, forecast_week). Keyed by item_id+site_id (no unique_id column)
PIPE_forecast_hyperparameters	forecasting/ step	Fitted model hyperparameters per (series, method)
PIPE_backtest_forecast	Evaluation step	Point forecasts paired with actuals per backtesting origin
PIPE_backtest_fingerprints	Backtesting step	Incremental backtest cache keyed by (pipeline_id, scenario_id, item_id, site_id) (no unique_id); fingerprint = MD5(data_hash + sorted methods + overrides + param_hash), payload defaults_v=3. ReplacingMergeTree(computed_at), read with FINAL
PIPE_causal_forecast	Causal forecasting step	Causal/asset-driven demand forecasts per (asset_type, item, site)
PIPE_indirect_demand	Indirect demand step	Dependent/indirect demand from BOM explosion
PIPE_maintenance_forecast	Maintenance step	Maintenance-event-driven demand forecasts
PIPE_meio_group_results	MEIO step	Group-level MEIO results (aggregated K-curve values)
PIPE_segment_membership	Segmentation step	Which series belong to which segment
PIPE_series_hashes	Demand hashing (tenant-global)	Demand-only data_hash per (item_id, site_id) — no pipeline_id column (the only PIPE_* table without one); forecast_hash is legacy/unused (the forecast fingerprint now lives in PIPE_forecast_fingerprints). ensure_ch_series_hashes refuses to boot if the pre-v6 unique_id-keyed schema is still present (run files/DDL/ch_migration_v6_drop_unique_id.sql); ETL _upsert_series_hashes bails loudly if the frame lacks item_id/site_id
PIPE_abc_results	ABC classification step	ABC/XYZ classification results
PIPE_allocation_results	Allocation step	Demand-to-supply allocation assignments with scores
PIPE_allocation_explanations	Allocation step	Explanation texts for allocation decisions
PIPE_allocation_unfulfilled	Allocation step	Demands that could not be fully allocated
PIPE_on_hand_snapshot	Freeze step	On-hand inventory snapshot at freeze time
PIPE_service_actuals	ETL step	Service/maintenance actual demand records
PIPE_lead_time_actuals	ETL step	Actual lead time performance data
PIPE_order_arrival_actuals	ETL step	Actual order arrival data for forecast accuracy
PIPE_version_delta_cache	Historization	Pre-computed delta between two archives for fast comparison
PIPE_pipeline_version	Historization	Pipeline version registry (legacy, replaced by master.pipeline_version_index)

Key Design Decisions

• is_past flag in netting: PIPE_forecast_netting contains both historical (is_past=1) and future (is_past=0) rows. The supply engine always filters AND is_past = 0 to avoid using past demand as future demand.
• Partition drop on re-run: Each step calls ALTER TABLE PIPE_xxx DROP PARTITION (pipeline_id, [scenario_id,] archive_date) before inserting, ensuring a re-run is always a clean replace, not an append.
• No id column in capacity: PIPE_supply_capacity has no auto-increment id. Queries must use the composite key.

---

Historization Tables

master.pipeline_version_index

Purpose: Registry of all frozen pipeline archives. One row per (pipeline_id, archive_date) for un-pinned archives; multiple rows allowed for pinned archives on the same date.

Column	Type	Description
id	BIGSERIAL PK	Surrogate row identifier
pipeline_id	BIGINT	Parent pipeline
scenario_id	BIGINT	Optional scenario filter (NULL = all scenarios)
archive_date	DATE	Monday of the freeze week — primary identifier
snapshot_ts	TIMESTAMPTZ	Exact freeze timestamp
created_by	TEXT	User or system that triggered the freeze
comment	TEXT	Free-text annotation
frozen	BOOLEAN	True = immutable; False = in-progress or failed
pinned	BOOLEAN	Pinned archives skip auto-replacement and retention
parent_archive_date	DATE	Optional lineage link to prior archive
replaced_archive_date	DATE	Date of the archive this one replaced in the same week
row_count_total	BIGINT	Total rows captured across all PIPE_* tables
table_counts	JSONB	Per-table row counts: {"PIPE_forecast_point_values": 12500, ...}
kpi_revenue	DOUBLE PRECISION	Total projected revenue
kpi_inventory_value	DOUBLE PRECISION	Total inventory investment
kpi_fill_rate	DOUBLE PRECISION	Average service fill rate
kpi_shortage_qty	DOUBLE PRECISION	Total shortage units
kpi_forecast_mase	DOUBLE PRECISION	Mean Absolute Scaled Error
kpi_forecast_bias	DOUBLE PRECISION	Mean forecast bias
kpi_nervousness	DOUBLE PRECISION	Plan change magnitude vs prior archive
kpi_supply_churn_pct	DOUBLE PRECISION	Percentage of orders changed
kpi_capacity_util	DOUBLE PRECISION	Average capacity utilisation

Unique index: (pipeline_id, archive_date) WHERE frozen = TRUE AND pinned = FALSE

archive_date model

The archive_date always equals toMonday(snapshot_ts). The live working partition has archive_date = toMonday(today()). See Historization — Archive Date Model for full details.

ClickHouse: archive_date partition column

Every PIPE_* table with historization support includes:

archive_date  Date  DEFAULT toMonday(today())

Partition keys: - Arity-3: PARTITION BY (pipeline_id, scenario_id, archive_date) - Arity-2: PARTITION BY (pipeline_id, archive_date) - PIPE_on_hand_snapshot: PARTITION BY archive_date

The live partition (archive_date = toMonday(today())) is dropped and re-inserted on every pipeline run. Frozen partitions are never touched after creation.

See Historization — Archive Date Model for the full technical reference.

Replay Engine Tables

scenario.replay_run

Purpose: One row per replay execution. Each replay run creates its own pipeline (is_replay = TRUE) and iterates through a date range, running supply + allocation at each step.

Column	Type	Description
id	BIGSERIAL PK	Run identifier
pipeline_id	BIGINT NOT NULL	The replay pipeline (cloned from source)
name	TEXT NOT NULL	User-provided name
description	TEXT	Optional description
source_pipeline_id	BIGINT NOT NULL	The pipeline being replayed
start_date	DATE NOT NULL	First iteration planning date
end_date	DATE NOT NULL	Last iteration planning date
current_date	DATE	Currently executing iteration date
granularity	VARCHAR(10)	'daily' or 'weekly' (default)
status	VARCHAR(20)	pending, running, completed, failed, cancelled
iteration_count	INTEGER	Total iterations (computed from date range)
current_iteration	INTEGER	Current iteration number (0 = not started)
config	JSONB	Replay parameters (return_rate overrides, etc.)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
completed_at	TIMESTAMPTZ	Completion timestamp
error_msg	TEXT	Error message if failed

Indexes: idx_replay_run_pipeline (pipeline_id), idx_replay_run_source_pipeline (source_pipeline_id), idx_replay_run_status (status)

scenario.replay_iteration

Purpose: One row per iteration within a replay run. Tracks the planning date, execution status, and per-iteration metrics.

Column	Type	Description
id	BIGSERIAL PK	Iteration identifier
replay_run_id	BIGINT NOT NULL FK	→ replay_run.id (CASCADE)
iteration_num	INTEGER NOT NULL	1-based iteration index
planning_date	DATE NOT NULL	The date this iteration simulates
status	VARCHAR(20)	pending, running, completed, failed, skipped
archive_date	DATE	Written to CH as archive_date (= planning_date)
supply_run_id	BIGINT	FK to pipe_supply_run
allocation_run_id	BIGINT	FK to allocation_run
started_at	TIMESTAMPTZ	Iteration start timestamp
completed_at	TIMESTAMPTZ	Iteration end timestamp
error_msg	TEXT	Error details if failed
metrics	JSONB	{fill_rate, total_stock, total_shortage, ...}

Unique constraint: (replay_run_id, iteration_num)

config_scenario_pipeline.is_replay

A boolean column (DEFAULT FALSE) added to the pipeline registry. Replay pipelines are marked is_replay = TRUE so they can be filtered from normal pipeline lists and cleaned up on deletion.

llocation_run.archive_date

A DATE column added to allocation_run. For standard runs, it defaults to planning_today(). For replay iterations, it is set to the iteration's planning_date. This enables time-series queries across both replay iterations and standard historized runs.

See Replay Engine Internals for the full technical reference.