Page → API → SQL Reference

SQL examples predate the v6 unique_id migration

The SQL snippets on this page use the legacy unique_id column, which was dropped from every table in the v6 migration. All series are now keyed by item_id + site_id — replace unique_id selection / filtering / joining / ORDER BY / GROUP BY with item_id, site_id (and the built-in item_name / site_name columns where applicable). This page is retained as a historical test artifact; do not copy its SQL verbatim.

Maps each UI page to its API calls and the underlying SQL queries, with observed timings. Includes maximal-load test results (2026-04-26, theBicycle tenant, 1 177 series, pipeline_id=1).

---

Table of Contents

1. Dashboard
2. Series Detail Page
3. Supply Tab (Detail Page)
4. Processes (ProcessRunner)
5. MEIO Scenarios
6. Segments
7. Backtest / Rolling Origins
8. Alerts
9. Maximal Load Test Results
10. ClickHouse Query Routing
11. Views Inventory (PostgreSQL + ClickHouse)

---

1. Dashboard

Page: / → Dashboard.jsx

1a. Series list (progressive loading)

API: GET /api/series?limit=200&skip=0&pipeline_id={id}&scenario_id={id} then GET /api/series?limit=50000&skip=200&pipeline_id={id}&scenario_id={id} (background)

SQL (main.py ~line 2550):

SELECT sc.unique_id, sc.has_seasonality, sc.has_trend, sc.is_intermittent,
       sc.adi, sc.zero_ratio, sc.recommended_methods,
       sbm.best_method, sbm.best_score, sbm.runner_up_method,
       ...
FROM {schema}.pipe_series_characteristics sc
LEFT JOIN {schema}.pipe_series_best_methods sbm ON sbm.unique_id = sc.unique_id
WHERE sc.pipeline_id = %s  -- or latest pipeline
ORDER BY sc.unique_id
LIMIT %s OFFSET %s

Timings: first 200 rows = 3.6 ms · all 1 793 rows = 11.9 ms

1b. Netting summary

API: GET /api/forecast/netting/summary?pipeline_id={id}

SQL: aggregates pipe_forecast_netting per pipeline.

1c. Accuracy/Precision chart

API: GET /api/analytics/accuracy-precision?pipeline_id={id}&scenario_id={id}

---

2. Series Detail Page

Page: /series/:uniqueId → TimeSeriesViewer.jsx

All five calls below are fired in parallel (Promise.allSettled).

2a. Consolidated detail (main chart data)

API: GET /api/series/{unique_id}/detail?scenario_id={id}&pipeline_id={id}

SQL (main.py ~line 2705–2895) — 8 sequential queries on one connection:

Step	Query	~ms
1	SELECT date, qty FROM pipe_demand_actuals WHERE unique_id=%s ORDER BY date	1–5 ms
2	SELECT … FROM master_item WHERE id=%s OR xuid=%s	1 ms
3	SELECT … FROM master_location WHERE id=%s OR xuid=%s	1 ms
4	SELECT DISTINCT ON (method) … FROM pipe_forecast_results WHERE unique_id=%s AND scenario_id=%s ORDER BY method, pipeline_id DESC	4 ms
5	SELECT * FROM pipe_series_characteristics WHERE unique_id=%s AND scenario_id=%s ORDER BY pipeline_id DESC LIMIT 1	2 ms
6	SELECT method, AVG(mae)… FROM pipe_series_backtest_metrics WHERE unique_id=%s AND pipeline_id=(SELECT MAX…) GROUP BY method	3 ms
7	SELECT … FROM pipe_series_best_methods WHERE unique_id=%s ORDER BY id DESC LIMIT 1	1 ms
8	SELECT … FROM pipe_demand_corrected WHERE item_id=%s AND site_id=%s	2 ms
9	SELECT DISTINCT ON (method) … FROM pipe_fitted_distributions WHERE unique_id=%s	1 ms

Total DB time: ~15 ms · HTTP response time: ~90 ms server-side (+ 210 ms Windows loopback TCP)

2b. Backtest rolling origins chart

API: GET /api/forecasts/{unique_id}/origins?scenario_id={id}&pipeline_id={id}

SQL:

SELECT unique_id, method, forecast_origin, horizon_step, point_forecast, actual_value
FROM {schema}.pipe_backtest_forecast
WHERE unique_id = %s AND pipeline_id = %s
ORDER BY forecast_origin, horizon_step

Timing: 10.6 ms for 832 rows (one series, 24 horizon × multiple origins)

2c. Method explanation

API: GET /api/series/{unique_id}/method-explanation

SQL: Simple lookup on pipe_series_characteristics.

2d. Indirect demand

API: GET /api/series/{unique_id}/indirect

SQL: Lookup on pipe_indirect_demand for this unique_id.

2e. Navigation series list (background)

API: GET /api/series?limit=200&skip=0 then ?limit=50000&skip=200 (background fetch)

Same query as Dashboard §1a. First 200 load immediately for navigation dropdowns.

---

3. Supply Tab (Detail Page)

Component: DetailSupplyTab.jsx — lazy-loaded when the Supply tab is clicked.

Fires four calls in parallel:

3a. Inventory projection

API: GET /api/supply/inventory?pipeline_id={id}&scenario_id={id}&item_id={id}&site_id={id}

SQL (supply_router.py ~line 910) — 7 sequential queries:

Step	Query	~ms
1	Main projection + item/site JOIN (no LATERAL)	3 ms
2	REPAIR supply orders aggregate	5 ms
3	RETURN supply orders aggregate	3 ms
4	Sales return table	2 ms
5	On-hand bad stock	2 ms
6	Demand netting breakdown (pipe_forecast_netting)	3 ms
7	Transfer-out supply orders	3 ms

-- Main projection query (step 1 — fixed 2026-04-26, no LATERAL)
SELECT sip.item_id, sip.site_id, sip.week,
       sip.projected_inventory, sip.demand, sip.supply_received,
       sip.shortage, sip.safety_stock,
       COALESCE(sip.expired_qty, 0) AS expired_qty,
       i.name AS item_name, s.name AS site_name,
       sip.item_id::text || '_' || sip.site_id::text AS unique_id,
       i.expiry_period_weeks,
       COALESCE(NULLIF(ist.unit_cost, 0), NULLIF(i.unit_cost, 0), 0) AS unit_cost
FROM {schema}.pipe_supply_inventory_projection sip
LEFT JOIN {schema}.master_item i ON i.id = sip.item_id
LEFT JOIN {schema}.master_location s ON s.id = sip.site_id
LEFT JOIN {schema}.master_item_location ist
       ON ist.item_id = sip.item_id AND ist.site_id = sip.site_id
WHERE sip.pipeline_id = %s AND sip.scenario_id = %s
  AND sip.item_id = %s AND sip.site_id = %s
ORDER BY sip.item_id, sip.site_id, sip.week ASC

Performance fix (2026-04-26): The original query used a LATERAL (SELECT MAX(unique_id) FROM pipe_demand_actuals …) join. Because pipe_demand_actuals is a pg_mooncake columnar table, the LATERAL was evaluated per-row and cost 360 ms per call. Replacing it with sip.item_id::text || '_' || sip.site_id::text brings this to 3 ms (100× speedup).

Total DB time: ~21 ms · HTTP response time: ~380 ms server-side (210 ms TCP + 40 ms JWT revocation check + 80 ms auth middleware overhead + 50 ms JSON serialization)

3b. Supply orders (incoming)

API: GET /api/supply/orders?pipeline_id={id}&scenario_id={id}&item_id={id}&site_id={id}&limit=2000

SQL: SELECT … FROM pipe_supply_orders WHERE pipeline_id=%s AND scenario_id=%s AND item_id=%s AND site_id=%s ORDER BY release_week LIMIT 2000

Timing: 5 ms (single SKU)

3c. Supply orders (outgoing — transfers)

Same endpoint with source_site_id filter instead of site_id.

3d. Route network

API: GET /api/routes/network?item_id={id}&site_id={id}&depth=3

SQL: Recursive CTE over master_route table up to depth 3.

---

4. Processes (ProcessRunner)

Page: /processes → ProcessRunner.jsx

4a. Process log

API: GET /api/process-log?limit=200&pipeline_id={id}

SQL: SELECT … FROM pipe_process_log WHERE pipeline_id=%s ORDER BY started_at DESC LIMIT 200

4b. Forecast parameter sets

API: GET /api/forecast/pipelines/{pipeline_id}/parameter-sets

SQL: SELECT … FROM forecast_parameter_set WHERE pipeline_id=%s ORDER BY sort_order

---

5. MEIO Scenarios

Page: /meio → MeioScenarios.jsx

API: GET /api/meio/results?pipeline_id={id}&scenario_id={id}

SQL:

SELECT mr.item_id, mr.site_id, mr.committed_buffer, mr.fill_rate,
       mr.marginal_value, mr.wait_time, mr.leg_lead_time,
       i.name AS item_name, s.name AS site_name
FROM {schema}.pipe_meio_results mr
LEFT JOIN {schema}.master_item i ON i.id = mr.item_id
LEFT JOIN {schema}.master_location s ON s.id = mr.site_id
WHERE mr.pipeline_id = %s AND mr.scenario_id = %s

Timing: 4.7 ms for 2 714 rows

---

6. Segments

Page: /segments → Segments.jsx

API: GET /api/segments and GET /api/segments/{id}/details

SQL:

SELECT s.id, s.name, s.criteria, COUNT(sm.unique_id) AS member_count
FROM {schema}.segments s
LEFT JOIN {schema}.pipe_segment_membership sm ON sm.segment_id = s.id
GROUP BY s.id, s.name, s.criteria
ORDER BY s.name

---

7. Backtest / Rolling Origins

Component: Ridge chart in TimeSeriesViewer.jsx, panel backtesting.

API: GET /api/forecasts/{unique_id}/origins?pipeline_id={id}

SQL:

SELECT unique_id, method, forecast_origin, horizon_step,
       point_forecast, actual_value
FROM {schema}.pipe_backtest_forecast
WHERE unique_id = %s AND pipeline_id = %s
ORDER BY forecast_origin, horizon_step

Timing: 10.6 ms (832 rows for one series)

The full table has 602 680 rows for pipeline_id=1; COUNT query takes 80 ms. No index bottleneck — the table has a composite index on (unique_id, pipeline_id).

---

8. Alerts

API: GET /api/alerts?pipeline_id={id}

SQL: Reads from pipe_alerts (1 237 rows); COUNT = 2.4 ms.

---

9. Maximal Load Test Results

Tested 2026-04-26 against theBicycle tenant (1 177 series, pipeline_id=1, PostgreSQL local). All times are pure DB execution time measured in Python (excludes HTTP overhead).

Query	Rows	Time
Dashboard /series characteristics (all)	1 793	6.8 ms
Dashboard /series + best_method JOIN (all)	5 133	11.9 ms
Dashboard /series first 200	200	3.6 ms
Detail forecasts (1 series)	2	4.0 ms
Detail backtest metrics (1 series)	2	3.3 ms
Supply /inventory 1 SKU (no LATERAL, fixed)	52	3.2 ms ✅
~~Supply /inventory 1 SKU (old LATERAL)~~	52	~~360 ms~~ ❌
Supply /inventory ALL SKUs	37 076	59.5 ms
Supply orders all	26 310	53.9 ms
Backtest rolling origins (1 series)	832	10.6 ms
Backtest rolling origins total rows	602 680	80.1 ms
MEIO results all	2 714	4.7 ms
demand_actuals COUNT (columnar pg_mooncake)	81 522	3.9 ms
demand_actuals (1 series)	230	1.4 ms
Alerts COUNT	1 237	2.4 ms

No slow queries found after the LATERAL fix. The single bottleneck was the LATERAL (SELECT MAX(unique_id) FROM pipe_demand_actuals …) pattern in the supply inventory endpoint, which triggered a full columnar-table scan per projected row.

HTTP overhead breakdown (Windows localhost)

Layer	Cost
Windows loopback TCP connect	~210 ms
JWT decode + revocation DB check	~40 ms
Starlette BaseHTTPMiddleware async wrapping	~50 ms
JSON serialization + response	~10–30 ms
Total fixed per-request overhead	~310–330 ms

This overhead is constant regardless of query complexity. It means even a 1 ms DB query shows ~350 ms total HTTP response time on Windows localhost. On Linux (production) loopback connect is ~0.5 ms, reducing total overhead to ~100 ms.

---

10. ClickHouse Query Routing

All pipeline output tables (PIPE_*) are written to both PostgreSQL (scenario schema) and ClickHouse (tenant database, e.g. thebicycle). The API currently routes two query types to CH when available; all other reads still go to PG. This section documents every CH query, its status, and the migration path for the remaining PG-only reads.

Write pattern (Python + Rust)

All CH writes are exclusively DROP PARTITION → INSERT. There are no UPDATE, MERGE, or row-level DELETE operations anywhere in the codebase.

# Python pattern (run_pipeline.py, all steps)
drop_ch_partition("PIPE_forecast_results", pipeline_id, scenario_id)   # DROP PARTITION (4, 1)
bulk_insert_ch("PIPE_forecast_results", rows)

# Single-key partition (PIPE_abc_results, PIPE_segment_membership)
drop_ch_partition("PIPE_abc_results", pipeline_id)   # DROP PARTITION 4

Rust supply engine writes nothing to CH directly — its output goes through run_pipeline.py --only supply which applies the same drop+insert pattern.

10a. Backtest metrics per series (FIXED — was dead)

API: GET /api/series/{unique_id}/detail (step 4 of 8)
API: GET /api/series/{unique_id}/method-comparison

Status: Fixed 2026-04-27. Both endpoints previously queried the dead mv_backtest_by_method AggregatingMergeTree MV which no longer exists in ch_schema.sql. They now query PIPE_series_backtest_metrics directly with GROUP BY method.

CH parameter syntax: clickhouse-connect requires typed named parameters: {pid:Int64}, {uid:String}. Bare {pid} is rejected by CH server with a syntax error. All CH queries in this codebase use the typed form.

CH query (main.py ~line 2798 and ~line 3745):

SELECT method,
       count()            AS n_windows,
       avg(mae)           AS mae,
       avg(rmse)          AS rmse,
       avg(bias)          AS bias,
       avg(mape)          AS mape,
       avg(smape)         AS smape,
       avg(mase)          AS mase,
       avg(crps)          AS crps,
       avg(winkler_score) AS winkler_score,
       avg(coverage_50)   AS coverage_50,
       avg(coverage_80)   AS coverage_80,
       avg(coverage_90)   AS coverage_90,
       avg(coverage_95)   AS coverage_95,
       avg(quantile_loss) AS quantile_loss
FROM PIPE_series_backtest_metrics
WHERE pipeline_id = {pid} AND unique_id = {uid}
GROUP BY method

Fallback (PG): If CH is unavailable the endpoint falls through to the equivalent AVG(...) aggregation over pipe_series_backtest_metrics in PostgreSQL, scoped to the latest pipeline_id.

10b. Accuracy/Precision scatter (ADDED — 2026-04-27)

API: GET /api/analytics/accuracy-precision?pipeline_id={id}

Status: CH routing added 2026-04-27. Previously always read from PG with a 2-table JOIN.

CH query (main.py ~line 4005):

SELECT
    sbm.unique_id,
    coalesce(nullIf(bm.locked_method,''), bm.best_method) AS best_method,
    avg(abs(sbm.bias))  AS accuracy,
    avg(sbm.rmse)       AS precision_val,
    avg(sbm.mape)       AS mape,
    avg(sbm.smape)      AS smape
FROM PIPE_series_backtest_metrics sbm
JOIN PIPE_series_best_methods bm
  ON bm.unique_id = sbm.unique_id
 AND sbm.method = coalesce(nullIf(bm.locked_method,''), bm.best_method)
WHERE sbm.pipeline_id = {pid}
  AND bm.pipeline_id = {pid}
  [AND coalesce(nullIf(bm.locked_method,''), bm.best_method) = {method}]   -- optional
  [AND sbm.scenario_id = {sid}]                                             -- optional
GROUP BY sbm.unique_id,
         coalesce(nullIf(bm.locked_method,''), bm.best_method)
ORDER BY sbm.unique_id

CH routing is skipped when pipeline_id is not supplied (falls back to PG).

10c. Pending migrations (PG only, no CH routing yet)

These reads still go to PostgreSQL. They are candidates for CH migration once the denorm columns (item_name, site_name, segment_name) are properly populated on insert. Currently those columns are written as empty strings in most PIPE_ tables.

Dashboard series list (main.py ~line 2502)

Most expensive dashboard query. Joins 5 tables to resolve item/site names and count outliers.

-- Current PG query (simplified)
SELECT sc.unique_id, sc.n_observations, sc.date_range_start, sc.date_range_end,
       sc.mean, sc.is_intermittent, sc.has_seasonality, sc.has_trend,
       sc.complexity_level, sc.recommended_methods,
       sbm.best_method,
       COALESCE(MAX(i_x.name), MAX(i_fk.name)) AS item_name,
       COALESCE(MAX(s_x.name), MAX(s_fk.name)) AS site_name,
       COUNT(DISTINCT sdo.id) AS n_outliers
FROM {schema}.pipe_series_characteristics sc
LEFT JOIN (
    SELECT DISTINCT ON (unique_id) unique_id, best_method
    FROM {schema}.pipe_series_best_methods
    WHERE scenario_id = %s AND pipeline_id = %s
    ORDER BY unique_id, id DESC
) sbm ON sbm.unique_id = sc.unique_id
LEFT JOIN (
    -- outlier count per unique_id
    SELECT da2.unique_id, dc2.id, dc2.pipeline_id
    FROM {schema}.pipe_demand_corrected dc2
    JOIN master.demand_actuals da2 ON da2.item_id = dc2.item_id AND da2.site_id = dc2.site_id
    WHERE dc2.pipeline_id = %s
) sdo ON sdo.unique_id = sc.unique_id AND sdo.pipeline_id = %s
LEFT JOIN (
    -- name resolution subquery
    SELECT DISTINCT unique_id AS uid,
           item_id::text, site_id::text,
           LEFT(unique_id, STRPOS(unique_id,'_')-1)   AS item_part,
           SUBSTR(unique_id, STRPOS(unique_id,'_')+1) AS site_part
    FROM master.demand_actuals
    WHERE unique_id IS NOT NULL AND STRPOS(unique_id,'_') > 0
) parts ON parts.uid = sc.unique_id
LEFT JOIN master.item     i_fk ON i_fk.id::text   = parts.item_id_text
LEFT JOIN master.location s_fk ON s_fk.id::text   = parts.site_id_text
LEFT JOIN master.item     i_x  ON i_x.xuid::text  = parts.item_part
LEFT JOIN master.location s_x  ON s_x.xuid::text  = parts.site_part
WHERE sc.scenario_id = %s AND sc.pipeline_id = %s
  [AND search/filter clauses]
GROUP BY sc.unique_id, ...
ORDER BY sc.unique_id
LIMIT %s OFFSET %s

Target CH query (once denorm columns populated):

SELECT sc.unique_id, sc.n_observations, sc.complexity_level,
       sc.has_seasonality, sc.has_trend, sc.is_intermittent,
       sc.recommended_methods_list, sc.mean,
       sc.item_name, sc.site_name,
       bm.best_method,
       -- outlier count: join PIPE_demand_corrected or embed n_outliers in sc
       dc.n_outliers
FROM PIPE_series_characteristics sc
LEFT JOIN PIPE_series_best_methods bm ON bm.unique_id = sc.unique_id
    AND bm.pipeline_id = sc.pipeline_id AND bm.scenario_id = sc.scenario_id
LEFT JOIN (
    SELECT unique_id, count() AS n_outliers
    FROM PIPE_demand_corrected
    WHERE pipeline_id = {pid}
    GROUP BY unique_id
) dc ON dc.unique_id = sc.unique_id
WHERE sc.pipeline_id = {pid} AND sc.scenario_id = {sid}
  [AND search clause on item_name, site_name]
ORDER BY sc.unique_id
LIMIT {lim} OFFSET {off}

Blocker: item_name, site_name are empty strings in PIPE_series_characteristics rows. Fix: populate them during the characterization CH sync step in run_pipeline.py.

Data cache load (main.py ~line 696)

Loads all characteristics into the in-process Dask cache on first request.

-- Step 1: all characteristics
SELECT * FROM {schema}.pipe_series_characteristics;

-- Step 2: name resolution (5-table JOIN, same as dashboard)
SELECT parts.uid AS unique_id,
       COALESCE(MAX(i_x.name), MAX(i_fk.name)) AS item_name,
       COALESCE(MAX(s_x.name), MAX(s_fk.name)) AS site_name,
       COALESCE(MAX(i_x.image_url), MAX(i_fk.image_url)) AS item_image_url
FROM (SELECT DISTINCT unique_id AS uid, item_id::text, site_id::text, …
      FROM master.demand_actuals WHERE …) parts
LEFT JOIN master.item     i_fk …
LEFT JOIN master.location s_fk …
LEFT JOIN master.item     i_x  …
LEFT JOIN master.location s_x  …
GROUP BY parts.uid

Then Python merge of the two DataFrames. Target: single query to PIPE_series_characteristics in CH.

Forecast sparklines (main.py ~line 5524)

Called by GET /api/series/sparklines for dashboard mini-charts.

-- Query 1: last 12 weeks of actuals
SELECT unique_id, date, y
FROM (SELECT unique_id, date, qty AS y,
             ROW_NUMBER() OVER (PARTITION BY unique_id ORDER BY date DESC) AS rn
      FROM master.demand_actuals
      WHERE unique_id = ANY(%s)) hist
WHERE rn <= 12 ORDER BY unique_id, date

-- Query 2: best method's point_forecast JSONB
SELECT DISTINCT ON (fr.unique_id)
    fr.unique_id, fr.point_forecast
FROM {schema}.pipe_forecast_results fr
JOIN (SELECT DISTINCT ON (unique_id) unique_id, best_method
      FROM {schema}.pipe_series_best_methods
      ORDER BY unique_id, id DESC) bm
  ON bm.unique_id = fr.unique_id AND bm.best_method = fr.method
WHERE fr.unique_id = ANY(%s)
ORDER BY fr.unique_id

Target: Query PIPE_forecast_results in CH (has best_method denorm column). Actuals remain in PG/columnar until a MASTER_demand_actuals CH mirror is verified.

---

11. Views Inventory (PostgreSQL + ClickHouse)

PostgreSQL views

No permanent views exist in the scenario schema (confirmed 2026-04-27 against theBicycle tenant).

One transient view is created dynamically by segmentation.py when a segmentation scenario uses a different base schema (cross-tenant setup). It is a simple column-select pass-through:

# segmentation/segmentation.py ~line 296
cur.execute(f"DROP VIEW IF EXISTS {schema}.{tbl} CASCADE")
cur.execute(f"CREATE VIEW {schema}.{tbl} AS SELECT {all_cols} FROM {base_schema}.{base_tbl}")

This view is recreated on every segmentation run and is not persistent schema. It does not appear in the information_schema.views list because it is scoped to the segmentation session.

Recommendation: keep as-is — it is a narrow adapter, not a query-complexity smell.

ClickHouse views / materialized views

mv_backtest_by_method (DEAD — removed)

This was an AggregatingMergeTree MV that pre-aggregated PIPE_series_backtest_metrics rows using AggregateFunction(avg, Float64) state columns. It was defined in an older version of ch_schema.sql and removed when the schema was rewritten (2026-04-19 migration).

The MV no longer exists in any tenant CH database. The API had two calls that still referenced it (main.py:2798 and main.py:3745) — both fixed 2026-04-27 to query PIPE_series_backtest_metrics directly with standard avg() aggregation.

Why it was removed: The avg_* columns in PIPE_series_backtest_metrics (avg_mae, avg_rmse, etc.) were intended to replace it as pre-computed values written at pipeline time. As of 2026-04-27 those columns are populated as 0.0 — they were never wired up in run_pipeline.py. The current approach (on-the-fly avg() aggregation in CH) is correct and fast enough; the avg_* columns can either be populated or dropped.

Dead MV DDL (for reference, do not recreate):

-- OLD — do not use
CREATE MATERIALIZED VIEW mv_backtest_by_method
ENGINE = AggregatingMergeTree()
PARTITION BY pipeline_id
ORDER BY (pipeline_id, scenario_id, unique_id, method)
AS SELECT
    pipeline_id, scenario_id, unique_id, method,
    countState()       AS n_windows_state,
    avgState(mae)      AS mae_state,
    avgState(rmse)     AS rmse_state,
    avgState(bias)     AS bias_state,
    avgState(mape)     AS mape_state,
    avgState(smape)    AS smape_state,
    avgState(mase)     AS mase_state,
    avgState(crps)     AS crps_state,
    avgState(winkler_score) AS winkler_score_state,
    avgState(coverage_50)   AS coverage_50_state,
    avgState(coverage_80)   AS coverage_80_state,
    avgState(coverage_90)   AS coverage_90_state,
    avgState(coverage_95)   AS coverage_95_state,
    avgState(quantile_loss) AS quantile_loss_state
FROM PIPE_series_backtest_metrics;

No other ClickHouse views exist

ch_schema.sql contains only MergeTree-family base tables (no MATERIALIZED VIEW, no VIEW). The PIPE_* tables are all written directly by the Python pipeline. There are no CH query-rewrite views.

avg_* columns in PIPE_series_backtest_metrics

The table has pre-aggregation columns (avg_mae, avg_rmse, avg_mase, avg_mape, avg_smape, avg_bias, avg_wape, avg_coverage_90, n_windows) that currently hold 0.0. These were designed to carry per-method averages written at pipeline time, eliminating the need for avg() aggregation at query time.

Decision required: either wire them up in run_pipeline.py (populate from pipe_series_backtest_metrics PG table during the evaluation sync), or drop them to reduce table width. Until that decision is made the API uses live avg() which is fast enough at current scale.