WF-6.8 Gold View Map

Version 1.0 | 2026-05-03 | Owner: Roger Thompson Value Drivers: VD6 (cross-cutting data infrastructure for VD1–VD5) Status: Design — implementation kicks off 2026-07-01 per WF-6.7 Phase 2.

Purpose

This document is the canonical map of every gold-layer view in the Cedarfell warehouse. It is the contract between the data layer (Schema v1.3 source tables) and the consumer layer (scorecards, dashboards, SOPs, ad-hoc queries). Every dbt model in dbt/models/gold/ declares the view it implements via this document’s view name. Every Metabase dashboard, Python notebook, or scorecard workbook reads from a named view here — not from raw source tables.

Why a gold view layer at all: Cedarfell’s analytical workload is multi-source by design. A single decision (Apex 2 contract standing) reads from six different tables. Without a stable view layer, every consumer reimplements the same joins, drift accumulates, and the pipeline becomes fragile. Gold views move the join logic into one place, version it, and let the consumer think in terms of “the view that answers my question” instead of “which seven tables do I join?”

Why follow this doc rather than improvising in dbt: The view set descends from the Decision Catalogue and KPI Taxonomy v1.1 by design. Every view named here exists because at least one recurring operational decision needs it. Improvising in dbt means losing that linkage — and once the linkage is lost, retiring stale views becomes guesswork. WF-6.8 is the audit trail.

How to use

For consumers (scorecards, dashboards): find the view that matches your grain (driver × month, route × day, fleet × week, etc.) and read it directly. Don’t join source tables; the view has done it.

For dbt model authors: the DDL in this document is the spec. Implementation in dbt/models/gold/<view_name>.sql should match the DDL exactly except for dialect quirks. If a change is needed, update WF-6.8 first, then the model — never the other way around.

For Roger’s KPI work: when proposing a new KPI in v1.2+, check whether an existing view surfaces it. If yes, reference the view ID. If no, this document gets a new view — log it in the change log and Decisions-Log.

Phase tagging

Each view carries one of three phase tags:

• P0 — day-1 unblocked at Phase 2 kickoff (2026-07-01). All source tables exist with usable data captured today. 9 views.
• P1 — unblocked once the Phase 1 capture sprint lands (Coverage Event Log, VEDR capture SOP, attendance log owner decision, MEDALS portal, etc.). 17 views.
• P2 — blocked on a future SOP, modeling harness, or business decision that has not yet been made. 6 views.

DDL is fully production-ready for P0 and P1. DDL for P2 views includes TODO blocks for placeholder source tables that don’t exist yet — these compile against stubs but produce empty results until the upstream SOP lands.

View catalog (summary)

Total: 9 P0 / 17 P1 / 6 P2 = 32 views.

DDL conventions

All DDL is DuckDB SQL. Key dialect notes:

• strftime(date_col, '%Y-%m') — month key as text
• date_trunc('week', d) returns Monday-anchored week start; we adapt to the FedEx week (Sat–Fri) where needed
• COUNT(*) FILTER (WHERE …) is the conditional count pattern
• NULLIF(denominator, 0) guards every division
• COALESCE(value, 0) for facts; NULL for averages where absence is meaningful
• Window functions used sparingly; favor CTEs for readability

Naming conventions:

• View names use snake_case and end with the grain hint (_daily, _weekly, _monthly, _quarterly) where unambiguous
• CTEs inside views use suffix _m, _w, _d, _q to indicate aggregation grain
• period_start / period_end for date ranges; month_key (text YYYY-MM) and week_start (date) for grouping

FedEx settlement week vs. calendar week: FedEx settlements run Sat–Fri. Most operational views use calendar week (Mon–Sun) because GroundCloud and Gusto align there. Settlement-aligned views (weekly_settlement_summary, route_profitability_weekly, thirteen_week_cash_flow) use Sat–Fri explicitly.

Domain 1 — Service Quality / Driver Performance (7 views)

1. monthly_driver_scorecard — P0

Purpose. Backs the WF-4.4 Monthly Driver Scorecard workbook. One row per active driver per month with 9 metrics, anchored to a calendar month. Already documented in Schema v1.3 — DDL reproduced here with v1.1 column additions for completeness.

Grain. driver × month. Decisions supported. VD4 W1 (driver scorecard build), VD4 M1 (monthly scorecard review), VD4 M4 (monthly service quality report). KPIs supported. OP.VD4.01 (ILS%), OP.VD4.02, OP.VD4.03 (DNA root-cause), OP.VD4.05 (RYDE), OP.VD4.06 (1-star), OP.VD4.10 (VEDR confirmed — v1.1), OP.VD5.03 (band distribution), OP.VD5.04 (incident rate), OP.VD5.10 (attendance call-outs — v1.1). Source tables. wsw_daily_detail, driver_productivity_weekly, ryde_scores, vedr_events, attendance_log, drivers.

CREATE OR REPLACE VIEW monthly_driver_scorecard AS
WITH
wsw_m AS (
  SELECT
    driver_id,
    strftime(activity_date, '%Y-%m') AS month_key,
    ROUND(SUM(ils_pct * actual_del_stops) / NULLIF(SUM(actual_del_stops), 0), 2)        AS ils_pct,
    ROUND(SUM(dna_count) * 1000.0 / NULLIF(SUM(actual_del_stops), 0), 2)                AS dna_per_1000,
    ROUND(SUM(actual_del_stops) * 100.0 / NULLIF(SUM(planned_del_stops), 0), 2)         AS completion_pct,
    SUM(actual_del_stops) AS month_stops
  FROM wsw_daily_detail
  WHERE driver_id IS NOT NULL
  GROUP BY driver_id, month_key
),
sph_m AS (
  SELECT
    driver_id,
    strftime(period_end, '%Y-%m') AS month_key,
    ROUND(SUM(sph_duty * stops) / NULLIF(SUM(stops), 0), 2) AS sph
  FROM driver_productivity_weekly
  WHERE driver_id IS NOT NULL
  GROUP BY driver_id, month_key
),
ryde_m AS (
  SELECT
    driver_id,
    strftime(period_end, '%Y-%m') AS month_key,
    AVG(ryde_score)               AS ryde_score,
    SUM(count_5_star)             AS five_star,
    SUM(count_1_star)             AS one_star
  FROM ryde_scores
  WHERE driver_id IS NOT NULL
  GROUP BY driver_id, month_key
),
vedr_m AS (
  SELECT
    driver_id,
    strftime(event_date, '%Y-%m') AS month_key,
    COUNT(*) FILTER (WHERE confirmed = TRUE) AS vedr_events
  FROM vedr_events
  GROUP BY driver_id, month_key
),
att_m AS (
  SELECT
    driver_id,
    strftime(event_date, '%Y-%m') AS month_key,
    COUNT(*) FILTER (WHERE event_type IN ('call_out','no_show')) AS callouts
  FROM attendance_log
  GROUP BY driver_id, month_key
)
SELECT
  d.driver_id,
  d.first_name || ' ' || d.last_name AS driver_name,
  w.month_key,
  w.ils_pct,
  w.dna_per_1000,
  r.ryde_score,
  r.five_star,
  r.one_star,
  s.sph,
  w.completion_pct,
  COALESCE(v.vedr_events, 0) AS vedr_events,
  COALESCE(a.callouts, 0)    AS callouts,
  w.month_stops
FROM drivers d
JOIN wsw_m  w  ON w.driver_id  = d.driver_id
LEFT JOIN sph_m  s ON s.driver_id  = d.driver_id AND s.month_key = w.month_key
LEFT JOIN ryde_m r ON r.driver_id  = d.driver_id AND r.month_key = w.month_key
LEFT JOIN vedr_m v ON v.driver_id  = d.driver_id AND v.month_key = w.month_key
LEFT JOIN att_m  a ON a.driver_id  = d.driver_id AND a.month_key = w.month_key
WHERE d.status = 'active'
ORDER BY d.driver_id, w.month_key;

Edge cases. No WSW activity → driver excluded that month. VEDR/attendance/RYDE absent → COALESCE to 0 for counts; NULL for averages. ILS and Completion are volume-weighted (heavier-load drivers’ percentages count more). Calendar month boundary used; switch to settlement month requires a month_calendar dimension table — not in P0 scope.

2. weekly_driver_summary — P0

Purpose. Faster-cadence sibling of monthly_driver_scorecard. Drives BC weekly review (Block 4 of Weekly Scorecard) and probationary driver weekly check-in (VD1 W2).

Grain. driver × week (Mon-anchored calendar week). Decisions supported. VD1 W2 (probation), VD4 D2 (pre-route coaching), VD4 W3 (RYDE trend). KPIs supported. OP.VD4.01/02 (ILS), OP.VD4.05 (RYDE), OP.VD2.04/05 (exception/DNA rate), OP.VD1.07 (probationary SPH ramp). Source tables. wsw_daily_detail, driver_productivity_weekly, ryde_scores, drivers.

CREATE OR REPLACE VIEW weekly_driver_summary AS
WITH
wsw_w AS (
  SELECT
    driver_id,
    date_trunc('week', activity_date) AS week_start,
    ROUND(SUM(ils_pct * actual_del_stops) / NULLIF(SUM(actual_del_stops), 0), 2) AS ils_pct,
    ROUND(SUM(dna_count) * 1000.0 / NULLIF(SUM(actual_del_stops), 0), 2)         AS dna_per_1000,
    ROUND(SUM(exceptions) * 1000.0 / NULLIF(SUM(actual_del_stops), 0), 2)        AS exception_per_1000,
    ROUND(SUM(actual_del_stops) * 100.0 / NULLIF(SUM(planned_del_stops), 0), 2)  AS completion_pct,
    SUM(actual_del_stops) AS week_stops
  FROM wsw_daily_detail
  WHERE driver_id IS NOT NULL
  GROUP BY driver_id, week_start
),
prod_w AS (
  -- driver_productivity_weekly already has period_end → align on week_start
  SELECT
    driver_id,
    date_trunc('week', period_end) AS week_start,
    AVG(sph_duty) AS sph,
    AVG(sph_road) AS sph_road
  FROM driver_productivity_weekly
  WHERE driver_id IS NOT NULL
  GROUP BY driver_id, week_start
),
ryde_w AS (
  SELECT
    driver_id,
    date_trunc('week', period_end) AS week_start,
    AVG(ryde_score) AS ryde_score,
    SUM(count_5_star) AS five_star,
    SUM(count_1_star) AS one_star
  FROM ryde_scores
  WHERE driver_id IS NOT NULL
  GROUP BY driver_id, week_start
)
SELECT
  d.driver_id,
  d.first_name || ' ' || d.last_name AS driver_name,
  w.week_start,
  w.week_start + INTERVAL 6 DAY AS week_end,
  w.ils_pct,
  w.dna_per_1000,
  w.exception_per_1000,
  w.completion_pct,
  p.sph,
  p.sph_road,
  r.ryde_score,
  r.five_star,
  r.one_star,
  w.week_stops,
  -- probation flag — compare against driver hire_date
  CASE
    WHEN d.probation_end_date IS NOT NULL AND w.week_start <= d.probation_end_date
    THEN TRUE ELSE FALSE
  END AS in_probation
FROM drivers d
JOIN wsw_w w ON w.driver_id = d.driver_id
LEFT JOIN prod_w p ON p.driver_id = d.driver_id AND p.week_start = w.week_start
LEFT JOIN ryde_w r ON r.driver_id = d.driver_id AND r.week_start = w.week_start
WHERE d.status = 'active'
ORDER BY d.driver_id, w.week_start;

Edge cases. RYDE captured monthly today (per WF-6.5) — weekly RYDE rows will be sparse until weekly capture lands. Workbook tolerates NULL. Probation flag uses hire_date + 90 day convention from drivers.probation_end_date.

3. daily_route_summary — P0

Purpose. Daily BC scorecard Block 2 (yesterday’s results per route). One row per dispatched route per day.

Grain. route × day. Decisions supported. VD2 D11 (late-package handling), VD2 EOD3 (BC sign-off), VD4 D1 (morning DSW review), VD4 D6 (EOD reconciliation). KPIs supported. OP.VD2.01 (Completion %), OP.VD2.02 (plan vs actual variance), OP.VD2.04 (exception rate), OP.VD2.05 (DNA rate), OP.VD4.01 (ILS %). Source tables. wsw_daily_detail, eas053_daily_detail, routes.

CREATE OR REPLACE VIEW daily_route_summary AS
WITH wsw_r AS (
  SELECT
    activity_date,
    wa_number,
    wa_name,
    driver_id,
    SUM(planned_del_stops)  AS planned_stops,
    SUM(actual_del_stops)   AS actual_stops,
    SUM(actual_del_pkgs)    AS actual_pkgs,
    SUM(actual_pu_stops)    AS pu_stops,
    SUM(dna_count)          AS dna,
    SUM(exceptions)         AS exceptions,
    SUM(code_85)            AS code_85,
    AVG(ils_pct)            AS ils_pct
  FROM wsw_daily_detail
  GROUP BY activity_date, wa_number, wa_name, driver_id
),
eas_r AS (
  SELECT
    activity_date,
    driver_id,
    SUM(delivery_stops)     AS eas_delivery_stops,
    SUM(ecomm_div_stops)    AS ecomm_stops,
    SUM(sp_only_stops)      AS sp_only_stops
  FROM eas053_daily_detail
  GROUP BY activity_date, driver_id
)
SELECT
  w.activity_date,
  w.wa_number,
  w.wa_name,
  r.route_type,
  r.loading_method,
  w.driver_id,
  w.planned_stops,
  w.actual_stops,
  w.actual_pkgs,
  w.pu_stops,
  ROUND(w.actual_stops * 100.0 / NULLIF(w.planned_stops, 0), 2)                     AS completion_pct,
  ROUND((w.actual_stops - w.planned_stops) * 100.0 / NULLIF(w.planned_stops, 0), 2) AS variance_pct,
  w.dna,
  ROUND(w.dna * 1000.0 / NULLIF(w.actual_stops, 0), 2)                              AS dna_per_1000,
  w.exceptions,
  ROUND(w.exceptions * 1000.0 / NULLIF(w.actual_stops, 0), 2)                       AS exceptions_per_1000,
  w.code_85,
  w.ils_pct,
  e.ecomm_stops,
  e.sp_only_stops,
  -- holding-status routes flag (per WF-2.4 — exclude from Completion% conclusions)
  CASE
    WHEN w.wa_name IN ('EST EXP CLOSE', 'EST EXP P/U') THEN TRUE ELSE FALSE
  END AS is_holding_route
FROM wsw_r w
LEFT JOIN routes r ON r.wa_number = w.wa_number
LEFT JOIN eas_r e ON e.activity_date = w.activity_date AND e.driver_id = w.driver_id
ORDER BY w.activity_date DESC, w.wa_number;

Edge cases. Holding-status routes (EST EXP CLOSE, EST EXP P/U) flagged — consumer should exclude from completion-% conclusions per Plan-vs-Actual memory. EAS053 join is per-day-per-driver (since EAS053 doesn’t break down by route); ecomm_stops is therefore “this driver’s eComm stops that day” not “this route’s.” Acceptable for daily review.

4. daily_csa_summary — P0

Purpose. Daily Owner scorecard. Fleet-wide rollup for Roger’s 10-minute morning review.

Grain. fleet (CSA 301562) × day. Decisions supported. VD2 EOD3 (BC sign-off — fleet view), VD4 D1 (morning DSW — fleet aggregate), VD4 M4 (monthly service quality report — daily input). KPIs supported. OP.VD2.01, OP.VD4.01, OP.VD2.04/05, OP.VD2.08 (EOD close latency), OP.VD6.05 (data quality flags). Source tables. wsw_daily_detail, eas053_daily_detail, daily_route_count, settlements (week-aligned).

CREATE OR REPLACE VIEW daily_csa_summary AS
WITH wsw_d AS (
  SELECT
    activity_date,
    SUM(planned_del_stops)                                                        AS planned_stops,
    SUM(actual_del_stops)                                                         AS actual_stops,
    SUM(actual_del_pkgs)                                                          AS actual_pkgs,
    SUM(actual_pu_stops)                                                          AS pu_stops,
    SUM(dna_count)                                                                AS dna,
    SUM(exceptions)                                                               AS exceptions,
    ROUND(SUM(ils_pct * actual_del_stops) / NULLIF(SUM(actual_del_stops), 0), 2)  AS ils_pct,
    COUNT(DISTINCT wa_number)                                                     AS routes_dispatched,
    COUNT(DISTINCT driver_id) FILTER (WHERE driver_id IS NOT NULL)                AS drivers_dispatched,
    COUNT(*) FILTER (WHERE driver_id IS NULL)                                     AS rows_missing_driver
  FROM wsw_daily_detail
  GROUP BY activity_date
),
eas_d AS (
  SELECT
    activity_date,
    SUM(delivery_stops)   AS eas_total_delivery_stops,
    SUM(ecomm_div_stops)  AS ecomm_stops
  FROM eas053_daily_detail
  GROUP BY activity_date
),
route_count_d AS (
  SELECT route_date, billable_routes FROM daily_route_count
)
SELECT
  w.activity_date,
  w.routes_dispatched,
  rc.billable_routes,
  w.drivers_dispatched,
  w.planned_stops,
  w.actual_stops,
  w.actual_pkgs,
  w.pu_stops,
  ROUND(w.actual_stops * 100.0 / NULLIF(w.planned_stops, 0), 2)               AS completion_pct,
  w.dna,
  ROUND(w.dna * 1000.0 / NULLIF(w.actual_stops, 0), 2)                        AS dna_per_1000,
  w.exceptions,
  ROUND(w.exceptions * 1000.0 / NULLIF(w.actual_stops, 0), 2)                 AS exceptions_per_1000,
  w.ils_pct,
  e.ecomm_stops,
  ROUND(e.ecomm_stops * 100.0 / NULLIF(e.eas_total_delivery_stops, 0), 2)     AS ecomm_pct,
  -- data quality flags
  w.rows_missing_driver,
  CASE WHEN rc.billable_routes IS NULL THEN 'missing_route_count' ELSE NULL END AS dq_flag
FROM wsw_d w
LEFT JOIN eas_d e         ON e.activity_date = w.activity_date
LEFT JOIN route_count_d rc ON rc.route_date  = w.activity_date
ORDER BY w.activity_date DESC;

Edge cases. WSW rows without driver_id (route created but no driver assigned) are counted in rows_missing_driver — surfaces data-quality gaps Roger reviews per OP.VD6.05. dq_flag returns the first detected problem; multi-flag rendering happens in the consumer.

5. exception_root_cause — P1

Purpose. Decompose exceptions by root cause and surface coding-discipline %. Drives VD4 M2 pattern escalation and Apex 2 OP.VD4.12.

Grain. exception_type × week (with by-driver decomposition available via additional groupby). Decisions supported. VD4 D1 (morning DSW), M1 (scorecard review), M2 (pattern escalation). KPIs supported. OP.VD4.03 (DNA root-cause), OP.VD4.11 (Code 7 / Code 2), OP.VD4.12 (coding discipline %). Source tables. service_exceptions, wsw_daily_detail.

CREATE OR REPLACE VIEW exception_root_cause AS
WITH ex_w AS (
  SELECT
    date_trunc('week', exception_date) AS week_start,
    exception_type,
    investigation_result,
    driver_id,
    COUNT(*) AS event_count
  FROM service_exceptions
  GROUP BY week_start, exception_type, investigation_result, driver_id
),
wsw_w AS (
  SELECT
    date_trunc('week', activity_date) AS week_start,
    driver_id,
    SUM(actual_del_stops) AS week_stops,
    SUM(dna_count)        AS dna_count,
    SUM(code_85)          AS code_85_count,
    SUM(exceptions)       AS exception_total
  FROM wsw_daily_detail
  WHERE driver_id IS NOT NULL
  GROUP BY week_start, driver_id
),
coding_w AS (
  -- Coding discipline %: of items investigated, what fraction were coded correctly
  -- "not_on_truck" investigations that were actually on truck = mis-coded
  SELECT
    week_start,
    driver_id,
    COUNT(*)                                                               AS investigated,
    COUNT(*) FILTER (WHERE investigation_result = 'not_on_truck')          AS coded_not_on_truck,
    COUNT(*) FILTER (
      WHERE investigation_result = 'driver_error'
        AND notes LIKE '%was actually on truck%'
    )                                                                       AS miscoded_as_not_on_truck
  FROM service_exceptions
  GROUP BY week_start, driver_id
)
SELECT
  ex.week_start,
  ex.exception_type,
  ex.investigation_result,
  ex.driver_id,
  d.first_name || ' ' || d.last_name AS driver_name,
  ex.event_count,
  w.week_stops,
  ROUND(ex.event_count * 1000.0 / NULLIF(w.week_stops, 0), 2) AS rate_per_1000,
  c.investigated                                              AS investigated_total,
  c.coded_not_on_truck,
  c.miscoded_as_not_on_truck,
  -- Coding discipline %: investigated minus miscoded, divided by investigated
  ROUND(
    (c.investigated - COALESCE(c.miscoded_as_not_on_truck, 0)) * 100.0
    / NULLIF(c.investigated, 0),
    2
  ) AS coding_discipline_pct
FROM ex_w ex
LEFT JOIN wsw_w w   ON w.week_start = ex.week_start AND w.driver_id = ex.driver_id
LEFT JOIN coding_w c ON c.week_start = ex.week_start AND c.driver_id = ex.driver_id
LEFT JOIN drivers d ON d.driver_id   = ex.driver_id
ORDER BY ex.week_start DESC, ex.exception_type, ex.event_count DESC;

Edge cases. “Miscoded as not_on_truck” detection currently uses a notes-text heuristic. Once Exception Investigation Log SOP lands (per WF-4.3 v1.1), add a structured was_on_truck_actually boolean column and update this view’s filter accordingly. Code 7 / Code 2 surface naturally as exception_type rows — consumer filters.

6. vedr_event_summary — P1

Purpose. Driver × month VEDR aggregation, with confirmed-only counts feeding Apex 2 and the WF-4.4 monthly scorecard. Coaching-tracking subset for VD5 D2.

Grain. driver × month. Decisions supported. VD2 ER1 (en-route monitoring), VD4 D4 (en-route service quality monitoring), VD4 W4 (contract standing), VD5 D2 (on-the-spot coaching). KPIs supported. OP.VD4.10 (VEDR confirmed events / driver), OP.VD5.04 (incident rate). Source tables. vedr_events, drivers.

CREATE OR REPLACE VIEW vedr_event_summary AS
SELECT
  v.driver_id,
  d.first_name || ' ' || d.last_name AS driver_name,
  strftime(v.event_date, '%Y-%m')    AS month_key,
  COUNT(*)                                                                      AS events_total,
  COUNT(*) FILTER (WHERE v.confirmed = TRUE)                                    AS confirmed,
  COUNT(*) FILTER (WHERE v.confirmed = FALSE)                                   AS false_positives,
  COUNT(*) FILTER (WHERE v.confirmed = TRUE AND v.coached = TRUE)               AS coached,
  COUNT(*) FILTER (WHERE v.confirmed = TRUE AND v.coached = FALSE)              AS not_yet_coached,
  COUNT(*) FILTER (WHERE v.confirmed = TRUE AND v.event_type = 'harsh_braking') AS harsh_braking,
  COUNT(*) FILTER (WHERE v.confirmed = TRUE AND v.event_type = 'harsh_accel')   AS harsh_accel,
  COUNT(*) FILTER (WHERE v.confirmed = TRUE AND v.event_type = 'distracted')    AS distracted,
  COUNT(*) FILTER (WHERE v.confirmed = TRUE AND v.event_type = 'no_seatbelt')   AS no_seatbelt,
  COUNT(*) FILTER (WHERE v.confirmed = TRUE AND v.event_type = 'speeding')      AS speeding,
  COUNT(*) FILTER (WHERE v.confirmed = TRUE AND v.event_type = 'cornering')     AS cornering,
  COUNT(*) FILTER (WHERE v.confirmed = TRUE AND v.severity = 'high')            AS high_severity
FROM vedr_events v
JOIN drivers d ON d.driver_id = v.driver_id
GROUP BY v.driver_id, driver_name, month_key
ORDER BY month_key DESC, confirmed DESC;

Edge cases. False positives kept in the view (column false_positives) so coaching-team productivity is visible — high false-positive rates indicate VEDR-AI tuning needs. Coaching coverage % = coached / NULLIF(confirmed, 0); consumer derives.

7. contract_standing_composite — P1

Purpose. The Apex 2 view. Stitches every leading indicator into one row per fleet per week, with GREEN/YELLOW/RED status applied. Anchors every Weekly Scorecard.

Grain. fleet × week. Decisions supported. VD4 W4 (contract standing monitoring), STR-MEDALS. KPIs supported. OP.VD4.09 (contract standing composite — Apex 2 with v1.1 inputs). Source tables. wsw_daily_detail, ryde_scores, vedr_events, service_exceptions, attendance_log, coverage_event_log (TODO — Phase 1 build), MEDALS state table (TODO — Phase 1 capture).

CREATE OR REPLACE VIEW contract_standing_composite AS
WITH
weeks AS (
  SELECT DISTINCT date_trunc('week', activity_date) AS week_start
  FROM wsw_daily_detail
),
ils_w AS (
  SELECT
    date_trunc('week', activity_date) AS week_start,
    ROUND(SUM(ils_pct * actual_del_stops) / NULLIF(SUM(actual_del_stops), 0), 2) AS ils_pct,
    ROUND(SUM(dna_count) * 1000.0 / NULLIF(SUM(actual_del_stops), 0), 2)         AS dna_per_1000,
    ROUND(SUM(exceptions) * 1000.0 / NULLIF(SUM(actual_del_stops), 0), 2)        AS exceptions_per_1000
  FROM wsw_daily_detail
  GROUP BY week_start
),
ryde_w AS (
  SELECT
    date_trunc('week', period_end) AS week_start,
    AVG(ryde_score)              AS ryde_score
  FROM ryde_scores
  WHERE driver_id IS NULL  -- CSA-aggregate rows
  GROUP BY week_start
),
vedr_w AS (
  SELECT
    date_trunc('week', event_date) AS week_start,
    COUNT(*) FILTER (WHERE confirmed = TRUE) AS vedr_confirmed
  FROM vedr_events
  GROUP BY week_start
),
coding_w AS (
  -- coding discipline % at fleet level
  SELECT
    date_trunc('week', exception_date) AS week_start,
    COUNT(*)                                                                            AS investigated,
    COUNT(*) FILTER (
      WHERE investigation_result = 'driver_error'
        AND notes LIKE '%was actually on truck%'
    )                                                                                    AS miscoded
  FROM service_exceptions
  GROUP BY week_start
),
medals_w AS (
  -- TODO Phase 1: source from medals_capture table once captured
  SELECT
    date_trunc('week', period_start) AS week_start,
    medals_level
  FROM (
    -- placeholder; replace with real source post-capture-design
    SELECT CAST(NULL AS DATE) AS period_start, CAST(NULL AS INTEGER) AS medals_level WHERE FALSE
  )
),
sit_w AS (
  -- sit-the-route events in trailing 4 weeks
  SELECT
    week_start,
    COALESCE(
      (SELECT COUNT(*) FROM sit_route_events_log s
       WHERE s.event_date BETWEEN week_start - INTERVAL 27 DAY AND week_start + INTERVAL 6 DAY),
      0
    ) AS sit_route_trailing_4w
  FROM weeks
),
borrow_w AS (
  -- 2 PM ceiling hit-rate
  SELECT
    date_trunc('week', request_date) AS week_start,
    ROUND(
      COUNT(*) FILTER (WHERE landed_before_2pm = TRUE) * 100.0
      / NULLIF(COUNT(*), 0),
      1
    ) AS two_pm_hit_rate_pct
  FROM coverage_event_log
  WHERE event_type = 'borrow_request'
  GROUP BY week_start
)
SELECT
  w.week_start,
  i.ils_pct,
  i.dna_per_1000,
  i.exceptions_per_1000,
  r.ryde_score,
  COALESCE(v.vedr_confirmed, 0)                                              AS vedr_confirmed,
  ROUND((c.investigated - COALESCE(c.miscoded, 0)) * 100.0
        / NULLIF(c.investigated, 0), 1)                                       AS coding_discipline_pct,
  m.medals_level,
  s.sit_route_trailing_4w,
  b.two_pm_hit_rate_pct,
  -- Apex 2 status band per KPI Taxonomy v1.1
  CASE
    WHEN m.medals_level >= 2
      OR i.ils_pct < 99.0
      OR (c.investigated > 0 AND
          (c.investigated - COALESCE(c.miscoded, 0)) * 100.0 / NULLIF(c.investigated, 0) < 90)
      OR b.two_pm_hit_rate_pct < 60
      OR s.sit_route_trailing_4w > 0
      THEN 'RED'
    WHEN COALESCE(v.vedr_confirmed, 0) > 0
      OR (c.investigated > 0 AND
          (c.investigated - COALESCE(c.miscoded, 0)) * 100.0 / NULLIF(c.investigated, 0) BETWEEN 90 AND 95)
      OR b.two_pm_hit_rate_pct BETWEEN 60 AND 80
      OR i.ils_pct < 99.8
      OR r.ryde_score < 4.0
      THEN 'YELLOW'
    ELSE 'GREEN'
  END AS contract_standing_band
FROM weeks w
LEFT JOIN ils_w i    ON i.week_start  = w.week_start
LEFT JOIN ryde_w r   ON r.week_start  = w.week_start
LEFT JOIN vedr_w v   ON v.week_start  = w.week_start
LEFT JOIN coding_w c ON c.week_start  = w.week_start
LEFT JOIN medals_w m ON m.week_start  = w.week_start
LEFT JOIN sit_w s    ON s.week_start  = w.week_start
LEFT JOIN borrow_w b ON b.week_start  = w.week_start
ORDER BY w.week_start DESC;

Edge cases. TWO upstream tables are referenced as P1-pending: coverage_event_log and sit_route_events_log (Coverage Event Log build per WF-1.4); medals_capture (MEDALS portal capture design — see Open Item #8 in Schema). Until both land, the relevant columns return NULL and the band logic gracefully degrades to GREEN/YELLOW based on the metrics that are available. The view does NOT short-circuit — it returns whatever is available, which is the right behavior for a leading-indicator dashboard.

Domain 2 — Coverage / Talent Pipeline (5 views)

The five views in this domain all read from a single source: the Coverage Event Log (built per WF-1.4 §10, schema below). Build sequence: log first → all five views unblock simultaneously.

Coverage Event Log schema (referenced by views 8–11):

CREATE TABLE coverage_event_log (
  id INTEGER PRIMARY KEY,
  event_date DATE NOT NULL,
  event_type TEXT NOT NULL,        -- 'callout', 'no_show', 'borrow_request', 'sit_route'
  driver_id INTEGER,                -- NULL for sit-the-route
  notice_time TEXT,                 -- 'night_before', 'morning_before_dispatch', 'after_dispatch'
  request_method TEXT,              -- 'phone', 'text', 'slack'
  partner_isp TEXT,                 -- e.g., 'Reliant', 'WGL', 'Turtle', 'Wifawn', NULL if internal
  request_date DATE,                -- for borrow requests, when initiated
  response_at TIMESTAMP,            -- when partner responded
  driver_landed BOOLEAN,            -- did borrow actually arrive
  landed_before_2pm BOOLEAN,        -- borrow vs ceiling
  stops_absorbed INTEGER,
  cost DECIMAL(10,2),               -- TAH or borrow rate
  outcome TEXT,                     -- 'covered_internal', 'covered_borrow', 'absorbed', 'sat'
  bc_on_duty TEXT,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_coverage_event_date ON coverage_event_log(event_date);
CREATE INDEX idx_coverage_partner    ON coverage_event_log(partner_isp);
CREATE INDEX idx_coverage_outcome    ON coverage_event_log(outcome);

The sit_route_events_log referenced from contract_standing_composite is a virtual filter on this same table where outcome = 'sat'.

8. coverage_event_summary — P1

Purpose. Weekly rollup of all coverage events with cost. Drives VD1 W4 (coverage cost weekly tracking) and feeds VD1 D1 / VD2 MO3 with historical context.

Grain. week × source × cost. Decisions supported. VD1 D1 (call-out triage), VD1 W4 (cost tracking), VD1 A3 (peak prep), VD2 MO3 (BC drives a route). KPIs supported. OP.VD1.03 (coverage event frequency), OP.VD1.04 (cost per event), OP.VD1.09 (borrow utilization by source). Source tables. coverage_event_log.

CREATE OR REPLACE VIEW coverage_event_summary AS
SELECT
  date_trunc('week', event_date) AS week_start,
  COUNT(*)                                                AS events_total,
  COUNT(*) FILTER (WHERE event_type = 'callout')          AS callouts,
  COUNT(*) FILTER (WHERE event_type = 'no_show')          AS no_shows,
  COUNT(*) FILTER (WHERE event_type = 'borrow_request')   AS borrow_requests,
  COUNT(*) FILTER (WHERE outcome = 'covered_internal')    AS covered_internal,
  COUNT(*) FILTER (WHERE outcome = 'covered_borrow')      AS covered_borrow,
  COUNT(*) FILTER (WHERE outcome = 'absorbed')            AS absorbed,
  COUNT(*) FILTER (WHERE outcome = 'sat')                 AS sat_route_events,
  SUM(cost)                                                AS total_cost,
  ROUND(AVG(cost) FILTER (WHERE cost IS NOT NULL), 2)     AS avg_cost_per_event,
  -- Borrow performance
  COUNT(*) FILTER (WHERE event_type = 'borrow_request' AND driver_landed = TRUE)  AS borrows_landed,
  COUNT(*) FILTER (WHERE event_type = 'borrow_request' AND landed_before_2pm = TRUE) AS borrows_before_2pm,
  ROUND(
    COUNT(*) FILTER (WHERE event_type = 'borrow_request' AND landed_before_2pm = TRUE) * 100.0
    / NULLIF(COUNT(*) FILTER (WHERE event_type = 'borrow_request'), 0),
    1
  ) AS two_pm_hit_rate_pct
FROM coverage_event_log
GROUP BY week_start
ORDER BY week_start DESC;

Edge cases. cost is NULL for some events (BC-drove with internal stop pay accounted elsewhere); avg_cost_per_event filters them out. borrow_requests includes both landed and unlanded; borrows_landed / borrows_before_2pm give the success funnel.

9. borrow_utilization_by_source — P1

Purpose. Mutual-aid dependency tracking. Names which partner ISPs Cedarfell relies on most and how reliable each is. Feeds the cross-contractor BC register decision (STR BC-REG).

Grain. source ISP × week. Decisions supported. VD1 D1, VD1 A3 (peak prep), STR BC-REG. KPIs supported. OP.VD1.09 (borrow utilization by source). Source tables. coverage_event_log.

CREATE OR REPLACE VIEW borrow_utilization_by_source AS
SELECT
  date_trunc('week', event_date) AS week_start,
  partner_isp,
  COUNT(*)                                                  AS requests,
  COUNT(*) FILTER (WHERE driver_landed = TRUE)              AS landed,
  COUNT(*) FILTER (WHERE landed_before_2pm = TRUE)          AS landed_before_2pm,
  COUNT(*) FILTER (WHERE driver_landed = FALSE)             AS no_show,
  ROUND(
    COUNT(*) FILTER (WHERE driver_landed = TRUE) * 100.0
    / NULLIF(COUNT(*), 0),
    1
  ) AS land_rate_pct,
  ROUND(
    COUNT(*) FILTER (WHERE landed_before_2pm = TRUE) * 100.0
    / NULLIF(COUNT(*) FILTER (WHERE driver_landed = TRUE), 0),
    1
  ) AS before_2pm_rate_pct,
  SUM(cost)                                                  AS total_cost,
  ROUND(AVG(cost) FILTER (WHERE cost IS NOT NULL), 2)        AS avg_cost
FROM coverage_event_log
WHERE event_type = 'borrow_request'
  AND partner_isp IS NOT NULL
GROUP BY week_start, partner_isp
ORDER BY week_start DESC, requests DESC;

Edge cases. Reliant is the first confirmed source per Vincent WF-1.4 Q11. WGL, Turtle, Wifawn are Brandon’s named partners. Each partner_isp value should be normalized at log time — case-sensitive matches matter here.

10. sit_route_events — P1

Purpose. The absolute coverage backstop tracker. Each occurrence is a named FedEx contract risk and triggers Apex 2 RED for the trailing 4 weeks.

Grain. quarter × event (with rolling-4-week summary for Apex 2 consumption). Decisions supported. VD1 D2 (BC-driving logic), VD2 MO3. KPIs supported. OP.VD1.08 (sit-the-route events per quarter). Source tables. coverage_event_log (filter on outcome = 'sat').

CREATE OR REPLACE VIEW sit_route_events AS
WITH events AS (
  SELECT
    event_date,
    bc_on_duty,
    notes
  FROM coverage_event_log
  WHERE outcome = 'sat'
)
SELECT
  date_trunc('quarter', event_date) AS quarter_start,
  EXTRACT(YEAR FROM event_date) AS year,
  EXTRACT(QUARTER FROM event_date) AS quarter,
  COUNT(*)                                                                AS events_in_quarter,
  COUNT(*) FILTER (WHERE event_date >= CURRENT_DATE - INTERVAL 27 DAY)    AS events_trailing_4w,
  COUNT(*) FILTER (WHERE event_date >= CURRENT_DATE - INTERVAL 90 DAY)    AS events_trailing_quarter,
  -- list of dates for audit visibility
  STRING_AGG(event_date::TEXT, ', ' ORDER BY event_date) AS event_dates_list
FROM events
GROUP BY quarter_start, year, quarter
ORDER BY quarter_start DESC;

Edge cases. Target is 0 per quarter. Any non-zero quarter triggers a workshop. Trailing-4-week count feeds contract_standing_composite Apex 2 RED logic.

11. two_pm_borrow_ceiling_daily — P1

Purpose. Daily visibility on whether the BC’s hold-late-for-borrow strategy is actually paying off. Hit-rate ≥ 80% targets the GREEN band.

Grain. day × outcome. Decisions supported. VD2 MO3 (BC drives — late-hold logic), VD1 D2. KPIs supported. OP.VD1.13 (2 PM borrow ceiling utilization — Apex 2 input). Source tables. coverage_event_log.

CREATE OR REPLACE VIEW two_pm_borrow_ceiling_daily AS
SELECT
  request_date AS day,
  COUNT(*)                                              AS requests,
  COUNT(*) FILTER (WHERE driver_landed = TRUE)          AS landed,
  COUNT(*) FILTER (WHERE landed_before_2pm = TRUE)      AS landed_before_2pm,
  COUNT(*) FILTER (WHERE driver_landed = TRUE
                     AND landed_before_2pm = FALSE)     AS landed_after_2pm,
  COUNT(*) FILTER (WHERE driver_landed = FALSE)         AS did_not_land,
  ROUND(
    COUNT(*) FILTER (WHERE landed_before_2pm = TRUE) * 100.0
    / NULLIF(COUNT(*), 0),
    1
  ) AS hit_rate_pct,
  -- per WF-1.4: outcome flag
  CASE
    WHEN COUNT(*) = 0 THEN 'no_request'
    WHEN COUNT(*) FILTER (WHERE landed_before_2pm = TRUE) = COUNT(*) THEN 'all_hit'
    WHEN COUNT(*) FILTER (WHERE driver_landed = FALSE) = COUNT(*) THEN 'all_missed'
    ELSE 'mixed'
  END AS day_outcome
FROM coverage_event_log
WHERE event_type = 'borrow_request'
GROUP BY request_date
ORDER BY request_date DESC;

Edge cases. Days with no borrow request return zero requests and day_outcome = 'no_request' — those are good days, not data gaps. Consumer dashboard should distinguish.

12. recruiting_funnel_daily — P1

Purpose. The Vincent Q19 self-asked dashboard. Daily counts of applications, interviews, hires, and 90-day survival crossings.

Grain. date × stage_count. Decisions supported. VD1 D3 (intake review), VD1 W1 (pipeline review), VD1 M1 (funnel review). KPIs supported. OP.VD1.10 (daily recruiting funnel), OP.VD1.01 (hire conversion), OP.VD1.06 (days to fill). Source tables. applications (NEW — RouteElite import), interviews_calendar (NEW — Aaliyah calendar import), drivers (hires + 90-day cohort).

-- Source tables not yet built (P1 capture work):
-- CREATE TABLE applications (id INT PK, application_date DATE, applicant_name TEXT,
--   source TEXT, status TEXT, screened_out_date DATE, interviewed_date DATE, hired_date DATE);
-- CREATE TABLE interviews_calendar (id INT PK, interview_date DATE, applicant_name TEXT,
--   interviewer TEXT, outcome TEXT);

CREATE OR REPLACE VIEW recruiting_funnel_daily AS
WITH dates AS (
  -- Build a continuous date axis from oldest application to today
  SELECT generate_series AS day
  FROM generate_series(
    (SELECT MIN(application_date) FROM applications),
    CURRENT_DATE,
    INTERVAL 1 DAY
  )
),
apps_d AS (
  SELECT
    application_date AS day,
    COUNT(*)                                  AS apps_received,
    COUNT(*) FILTER (WHERE source = 'route_elite') AS apps_route_elite,
    COUNT(*) FILTER (WHERE source = 'referral')    AS apps_referral,
    COUNT(*) FILTER (WHERE source = 'walk_in')     AS apps_walk_in
  FROM applications
  GROUP BY day
),
interviews_d AS (
  SELECT
    interview_date AS day,
    COUNT(*)                              AS interviews_held,
    COUNT(*) FILTER (WHERE outcome = 'pass') AS interviews_pass,
    COUNT(*) FILTER (WHERE outcome = 'fail') AS interviews_fail
  FROM interviews_calendar
  GROUP BY day
),
hires_d AS (
  SELECT
    hire_date AS day,
    COUNT(*) AS hires
  FROM drivers
  WHERE role = 'driver'
  GROUP BY day
),
survivors_d AS (
  -- 90-day survivors: drivers whose probation ended on this day and who are still active
  SELECT
    probation_end_date AS day,
    COUNT(*) FILTER (WHERE status = 'active' OR status = 'inactive')           AS survived_90,
    COUNT(*) FILTER (WHERE status = 'terminated')                              AS terminated_within_90
  FROM drivers
  WHERE role = 'driver' AND probation_end_date IS NOT NULL
  GROUP BY day
)
SELECT
  d.day,
  COALESCE(a.apps_received, 0)        AS apps_received,
  COALESCE(a.apps_route_elite, 0)     AS apps_route_elite,
  COALESCE(a.apps_referral, 0)        AS apps_referral,
  COALESCE(a.apps_walk_in, 0)         AS apps_walk_in,
  COALESCE(i.interviews_held, 0)      AS interviews_held,
  COALESCE(i.interviews_pass, 0)      AS interviews_pass,
  COALESCE(h.hires, 0)                AS hires,
  COALESCE(s.survived_90, 0)          AS survived_90_today,
  COALESCE(s.terminated_within_90, 0) AS terminated_within_90_today,
  -- 7-day rolling pipeline coverage
  SUM(COALESCE(a.apps_received, 0)) OVER (
    ORDER BY d.day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS apps_trailing_7d,
  SUM(COALESCE(i.interviews_held, 0)) OVER (
    ORDER BY d.day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS interviews_trailing_7d
FROM dates d
LEFT JOIN apps_d a       ON a.day = d.day
LEFT JOIN interviews_d i ON i.day = d.day
LEFT JOIN hires_d h      ON h.day = d.day
LEFT JOIN survivors_d s  ON s.day = d.day
ORDER BY d.day DESC;

Edge cases. applications and interviews_calendar are P1-pending. Until built, the view returns rows with COALESCE-zeroed columns from drivers only. RouteElite export format determines the eventual applications.source enum — confirm with Aaliyah.

Domain 3 — People Management (4 views)

13. attendance_summary — P1

Purpose. Driver × month attendance rollup. Schema v1.3 attendance_log is ready; this view surfaces it. Companion to monthly_driver_scorecard metric 9.

Grain. driver × month. Decisions supported. VD5 D1 (daily attendance), VD5 W2 (weekly call-out pattern review). KPIs supported. OP.VD5.07 (call-out patterns), OP.VD5.10 (attendance call-outs per driver). Source tables. attendance_log, drivers.

CREATE OR REPLACE VIEW attendance_summary AS
SELECT
  a.driver_id,
  d.first_name || ' ' || d.last_name AS driver_name,
  strftime(a.event_date, '%Y-%m')    AS month_key,
  COUNT(*)                                                       AS events_total,
  COUNT(*) FILTER (WHERE a.event_type = 'call_out')              AS call_outs,
  COUNT(*) FILTER (WHERE a.event_type = 'no_show')               AS no_shows,
  COUNT(*) FILTER (WHERE a.event_type = 'late')                  AS late_arrivals,
  COUNT(*) FILTER (WHERE a.event_type = 'early_out')             AS early_outs,
  COUNT(*) FILTER (WHERE a.event_type = 'sick')                  AS sick_days,
  COUNT(*) FILTER (WHERE a.event_type = 'bereavement')           AS bereavement_days,
  COUNT(*) FILTER (WHERE a.event_type = 'scheduled_off')         AS scheduled_off,
  -- Scorecard-relevant unplanned absence
  COUNT(*) FILTER (WHERE a.event_type IN ('call_out','no_show')) AS unplanned_absence,
  -- Advance notice quality
  COUNT(*) FILTER (WHERE a.advance_notice = 'day_before' AND a.event_type = 'call_out') AS callout_with_notice,
  COUNT(*) FILTER (WHERE a.advance_notice IN ('same_day_after_dispatch','none')
                     AND a.event_type = 'call_out')                                       AS callout_no_notice,
  SUM(a.coverage_cost_estimate) AS total_coverage_cost
FROM attendance_log a
JOIN drivers d ON d.driver_id = a.driver_id
GROUP BY a.driver_id, driver_name, month_key
ORDER BY month_key DESC, unplanned_absence DESC;

Edge cases. scheduled_off and bereavement are kept in the log per Schema v1.3 design — pattern matters even though scorecard counts only unplanned absence (call_outs + no_shows). Coverage cost is optional — many call-outs don’t get cost-estimated at log time, so total_coverage_cost will under-report.

14. morale_risk_indicator — P1

Purpose. Brandon’s 180-stop morale early-warning, quantified. Days where any driver was assigned ≥180 stops AND fleet had active call-outs that day.

Grain. week × event_count. Decisions supported. VD5 M4 (monthly culture / morale check). KPIs supported. OP.VD5.09 (morale risk indicator). Source tables. wsw_daily_detail, attendance_log.

CREATE OR REPLACE VIEW morale_risk_indicator AS
WITH high_stop_days AS (
  -- Days where at least one driver was loaded with 180+ stops
  SELECT
    activity_date,
    COUNT(DISTINCT driver_id) FILTER (WHERE actual_del_stops >= 180) AS drivers_180_plus
  FROM wsw_daily_detail
  WHERE driver_id IS NOT NULL
  GROUP BY activity_date
),
short_staff_days AS (
  -- Days where call-outs happened
  SELECT
    event_date AS activity_date,
    COUNT(*) FILTER (WHERE event_type IN ('call_out','no_show')) AS callouts_that_day
  FROM attendance_log
  GROUP BY activity_date
),
risk_days AS (
  SELECT
    h.activity_date,
    h.drivers_180_plus,
    COALESCE(s.callouts_that_day, 0) AS callouts_that_day,
    CASE
      WHEN h.drivers_180_plus > 0 AND COALESCE(s.callouts_that_day, 0) > 0
      THEN TRUE ELSE FALSE
    END AS is_morale_risk_day
  FROM high_stop_days h
  LEFT JOIN short_staff_days s ON s.activity_date = h.activity_date
)
SELECT
  date_trunc('week', activity_date) AS week_start,
  COUNT(*) FILTER (WHERE is_morale_risk_day = TRUE) AS morale_risk_days,
  SUM(drivers_180_plus) FILTER (WHERE is_morale_risk_day = TRUE) AS driver_events_at_risk,
  SUM(callouts_that_day) FILTER (WHERE is_morale_risk_day = TRUE) AS callouts_during_risk_days
FROM risk_days
GROUP BY week_start
ORDER BY week_start DESC;

Edge cases. Target is 0 risk days per week during normal volume. Peak season (Nov–Dec) will breach this — consumer should compare against same-week-prior-year, not absolute, during peak. Driver events count means a single day with 3 drivers ≥180 stops counts as 3.

15. ot_summary_weekly — P0

Purpose. Universal OT eligibility (Roger 2026-04-29) makes weekly OT visibility a real cost control. Brandon’s hedge — call missed-shift drivers first to recover the shift before incurring OT premium — depends on this view.

Grain. driver × week. Decisions supported. VD5 W1 (weekly OT review), VD6 FA5 (headcount sizing). KPIs supported. OP.VD5.02 (OT % of labor hours). Source tables. timecard_entries, payroll_records.

CREATE OR REPLACE VIEW ot_summary_weekly AS
WITH timecard_w AS (
  -- Sum daily duty hours, then identify hours over 8/day as OT
  SELECT
    driver_id,
    date_trunc('week', entry_date) AS week_start,
    SUM(duration_hours) FILTER (WHERE entry_type = 'Clocked Time')                          AS total_duty_hours,
    SUM(GREATEST(duration_hours - 8, 0)) FILTER (WHERE entry_type = 'Clocked Time')         AS daily_ot_hours
  FROM timecard_entries
  GROUP BY driver_id, week_start
),
payroll_w AS (
  -- Pull regular pay for the matching pay period
  SELECT
    driver_id,
    date_trunc('week', pay_period_start) AS week_start,
    regular_hours,
    regular_amount,
    gross_earnings
  FROM payroll_records
)
SELECT
  d.driver_id,
  d.first_name || ' ' || d.last_name AS driver_name,
  t.week_start,
  t.total_duty_hours,
  t.daily_ot_hours,
  ROUND(t.daily_ot_hours * 100.0 / NULLIF(t.total_duty_hours, 0), 1) AS ot_pct,
  p.regular_hours,
  p.regular_amount,
  p.gross_earnings,
  -- target threshold flag
  CASE
    WHEN t.daily_ot_hours = 0 THEN 'green'
    WHEN ROUND(t.daily_ot_hours * 100.0 / NULLIF(t.total_duty_hours, 0), 1) <= 8 THEN 'yellow'
    ELSE 'red'
  END AS ot_band
FROM drivers d
JOIN timecard_w t ON t.driver_id = d.driver_id
LEFT JOIN payroll_w p ON p.driver_id = d.driver_id AND p.week_start = t.week_start
WHERE d.status = 'active' AND d.role IN ('driver','bc')
ORDER BY t.week_start DESC, t.daily_ot_hours DESC;

Edge cases. Daily OT is over 8 hrs/day per Gusto convention (per project_compensation_structure memory). Weekly 40-hour-threshold OT is separately calculable; not exposed in this view. BCs included because Vincent and Brandon both clock in.

16. turnover_summary_quarterly — P0

Purpose. Quarterly turnover with 90-day cohort survival decomposition. Drives VD1 M2 termination review.

Grain. quarter × cohort. Decisions supported. VD1 M2 (termination + exit pattern), VD5 retention investment. KPIs supported. OP.VD5.01 (turnover rate, 90-day rolling annualized), OP.VD1.02 (90-day retention). Source tables. drivers.

CREATE OR REPLACE VIEW turnover_summary_quarterly AS
WITH quarter_axis AS (
  SELECT generate_series AS quarter_start
  FROM generate_series(
    DATE '2025-10-01',  -- earliest quarter with any Cedarfell hires
    date_trunc('quarter', CURRENT_DATE),
    INTERVAL 3 MONTH
  )
),
hires_q AS (
  SELECT
    date_trunc('quarter', hire_date) AS quarter_start,
    COUNT(*)                                                      AS hires,
    COUNT(*) FILTER (WHERE role = 'driver')                       AS hires_drivers
  FROM drivers
  WHERE hire_date IS NOT NULL
  GROUP BY quarter_start
),
terms_q AS (
  SELECT
    date_trunc('quarter', termination_date) AS quarter_start,
    COUNT(*)                                                                  AS terminations,
    COUNT(*) FILTER (WHERE role = 'driver')                                   AS terminations_drivers,
    COUNT(*) FILTER (WHERE termination_date - hire_date <= 90 AND role = 'driver') AS terminations_within_90
  FROM drivers
  WHERE termination_date IS NOT NULL
  GROUP BY quarter_start
),
active_q AS (
  -- avg headcount during the quarter (start + end snapshot / 2 — approximation)
  SELECT
    q.quarter_start,
    (SELECT COUNT(*) FROM drivers
     WHERE hire_date <= q.quarter_start
       AND (termination_date IS NULL OR termination_date > q.quarter_start)
       AND role = 'driver')
    AS active_drivers_start,
    (SELECT COUNT(*) FROM drivers
     WHERE hire_date <= q.quarter_start + INTERVAL 3 MONTH - INTERVAL 1 DAY
       AND (termination_date IS NULL OR termination_date > q.quarter_start + INTERVAL 3 MONTH - INTERVAL 1 DAY)
       AND role = 'driver')
    AS active_drivers_end
  FROM quarter_axis q
),
cohort_survival AS (
  -- For drivers hired in this quarter, did they cross 90 days?
  SELECT
    date_trunc('quarter', hire_date) AS quarter_start,
    COUNT(*) FILTER (WHERE role = 'driver')                                   AS cohort_size,
    COUNT(*) FILTER (
      WHERE role = 'driver'
        AND (termination_date IS NULL OR termination_date - hire_date > 90)
    )                                                                          AS cohort_survived_90
  FROM drivers
  WHERE hire_date IS NOT NULL
  GROUP BY quarter_start
)
SELECT
  q.quarter_start,
  q.quarter_start + INTERVAL 3 MONTH - INTERVAL 1 DAY AS quarter_end,
  COALESCE(h.hires, 0)                              AS hires,
  COALESCE(h.hires_drivers, 0)                      AS hires_drivers,
  COALESCE(t.terminations, 0)                       AS terminations,
  COALESCE(t.terminations_drivers, 0)               AS terminations_drivers,
  COALESCE(t.terminations_within_90, 0)             AS terminations_within_90,
  a.active_drivers_start,
  a.active_drivers_end,
  ROUND((a.active_drivers_start + a.active_drivers_end) / 2.0, 1) AS avg_drivers,
  -- Annualized turnover rate
  ROUND(
    COALESCE(t.terminations_drivers, 0) * 4.0 * 100.0
    / NULLIF((a.active_drivers_start + a.active_drivers_end) / 2.0, 0),
    1
  ) AS turnover_rate_annualized_pct,
  -- 90-day retention for this hire cohort
  cs.cohort_size,
  cs.cohort_survived_90,
  ROUND(cs.cohort_survived_90 * 100.0 / NULLIF(cs.cohort_size, 0), 1) AS retention_90_pct
FROM quarter_axis q
LEFT JOIN hires_q h         ON h.quarter_start = q.quarter_start
LEFT JOIN terms_q t         ON t.quarter_start = q.quarter_start
LEFT JOIN active_q a        ON a.quarter_start = q.quarter_start
LEFT JOIN cohort_survival cs ON cs.quarter_start = q.quarter_start
ORDER BY q.quarter_start DESC;

Edge cases. Avg headcount approximation uses start + end snapshots; for low-volume quarters with mid-period changes this can drift. Acceptable for trend; consumer should not over-interpret single-quarter values. Annualized rate is quarterly × 4, standard convention.

Domain 4 — Financial / Route Profitability (7 views)

17. weekly_settlement_summary — P0

Purpose. Per-week settlement with revenue + key cost components and contract-level fields. Drives VD6 W1 (settlement review), W4 (cash position), and the Apex 1 financial layer.

Grain. fleet × week (Sat–Fri FedEx settlement week). Decisions supported. VD6 W1, W2, W3, W4. KPIs supported. OP.VD6.01 (cash position), OP.VD6.02 (burn vs plan), OP.VD6.03 (settlement variance). Source tables. settlements, settlement_daily.

CREATE OR REPLACE VIEW weekly_settlement_summary AS
SELECT
  s.settlement_id,
  s.week_ending,
  s.week_ending - INTERVAL 6 DAY AS week_starting,
  s.contract_id,
  s.vehicles_count,
  s.service_charge,
  s.ground_dl_stops,
  s.ground_dl_packages,
  s.pickup_stops,
  s.pickup_packages,
  s.ecomm_dl_stops,
  s.ecomm_dl_packages,
  s.total_stops,
  s.ecomm_pct,
  s.fuel_surcharge,
  s.surge_charge,
  s.brand_promotion,
  s.large_pkg_mix,
  s.total_settlement,
  ROUND(s.total_settlement / NULLIF(s.total_stops, 0), 4) AS revenue_per_stop,
  ROUND(s.fuel_surcharge / NULLIF(s.total_stops, 0), 4) AS fuel_surcharge_per_stop,
  -- WoW deltas
  s.total_settlement - LAG(s.total_settlement) OVER (ORDER BY s.week_ending) AS wow_delta_revenue,
  s.total_stops - LAG(s.total_stops) OVER (ORDER BY s.week_ending)            AS wow_delta_stops,
  ROUND(
    (s.total_settlement - LAG(s.total_settlement) OVER (ORDER BY s.week_ending)) * 100.0
    / NULLIF(LAG(s.total_settlement) OVER (ORDER BY s.week_ending), 0),
    1
  ) AS wow_pct_change_revenue
FROM settlements s
ORDER BY s.week_ending DESC;

Edge cases. Settlement-week vs calendar-week boundary: this view uses settlement-week (Sat–Fri) explicitly. Contract change-over (C8889777 → C8891645) tracked via contract_id so YoY comparisons can flag the contract boundary.

18. route_profitability_weekly — P2

Purpose. The FA1 view. Per-route weekly contribution margin combining revenue, labor, fuel, and maintenance allocations. The most-requested business question — and the most-blocked view in the system.

Grain. route × week. Decisions supported. VD6 FA1 (route profitability), FA2 (truck strategy informed by route economics). KPIs supported. FIN.06 (route profitability), FIN.03/04 (cost per stop), FIN.05 (revenue per stop). Source tables. wsw_daily_detail, eas053_daily_detail, settlements, payroll_records, fuel_transactions, vehicle_crossref, maintenance_work_orders, routes.

-- TODO P2 dependencies before this view is meaningful:
--   1. WF-3.2 fuel-card register operational (fuel_card_to_truck mapping current)
--   2. Driver → route allocation logic (currently driver works one WA per day, but bench drivers split)
--   3. Maintenance allocation rule: per-vehicle costs allocated to whichever WA the vehicle ran most that week
--   4. Service-charge / overhead allocation rule: by stop count? by route count? Roger to decide.
--   5. eComm vs Ground revenue split per route: needs EAS053 aggregation by route, not just driver

CREATE OR REPLACE VIEW route_profitability_weekly AS
WITH
revenue_w AS (
  -- Per-route revenue: weight settlement by stop volume share
  SELECT
    w.wa_number,
    date_trunc('week', w.activity_date) AS week_start,
    SUM(w.actual_del_stops + w.actual_pu_stops) AS route_total_stops,
    -- Allocate fleet revenue by stop share (placeholder — refine with eComm premium)
    SUM(w.actual_del_stops + w.actual_pu_stops) * (
      SELECT total_settlement / NULLIF(total_stops, 0)
      FROM settlements s
      WHERE s.week_ending = date_trunc('week', w.activity_date) + INTERVAL 4 DAY  -- Fri
      LIMIT 1
    ) AS allocated_revenue
  FROM wsw_daily_detail w
  WHERE w.driver_id IS NOT NULL
  GROUP BY w.wa_number, week_start
),
labor_w AS (
  -- Per-route labor: driver pay days on this route × that day's pay
  -- TODO: this is the most fragile join — driver's weekly pay covers all routes they ran that week
  SELECT
    w.wa_number,
    date_trunc('week', w.activity_date) AS week_start,
    -- Approximation: split each driver's stop pay + base by stops on this route vs total stops that week
    SUM(
      (w.actual_del_stops + w.actual_pu_stops)
      * (p.gross_earnings / NULLIF((
          SELECT SUM(actual_del_stops + actual_pu_stops)
          FROM wsw_daily_detail w2
          WHERE w2.driver_id = w.driver_id
            AND date_trunc('week', w2.activity_date) = date_trunc('week', w.activity_date)
        ), 0))
    ) AS allocated_labor
  FROM wsw_daily_detail w
  LEFT JOIN payroll_records p
    ON p.driver_id = w.driver_id
    AND p.pay_period_start = date_trunc('week', w.activity_date)
  WHERE w.driver_id IS NOT NULL
  GROUP BY w.wa_number, week_start
),
fuel_w AS (
  -- Per-route fuel: vehicle assigned to route × vehicle's fuel that week
  SELECT
    w.wa_number,
    date_trunc('week', w.activity_date) AS week_start,
    SUM(f.amount) AS allocated_fuel
  FROM wsw_daily_detail w
  LEFT JOIN vehicle_crossref vc ON vc.groundcloud_id = w.gc_vehicle_id
    AND w.activity_date BETWEEN COALESCE(vc.card_assigned_to_truck_from, '1900-01-01')
                              AND COALESCE(vc.card_assigned_to_truck_to, '9999-12-31')
  LEFT JOIN fuel_transactions f ON f.vehicle_id = vc.vehicle_id
    AND date_trunc('week', f.fill_date) = date_trunc('week', w.activity_date)
  WHERE w.driver_id IS NOT NULL
  GROUP BY w.wa_number, week_start
),
maint_w AS (
  -- Per-route maintenance: vehicle on route × vehicle's maintenance that week
  SELECT
    w.wa_number,
    date_trunc('week', w.activity_date) AS week_start,
    SUM(m.parts_cost + m.labor_cost) AS allocated_maintenance
  FROM wsw_daily_detail w
  LEFT JOIN vehicle_crossref vc ON vc.groundcloud_id = w.gc_vehicle_id
  LEFT JOIN maintenance_work_orders m ON m.vehicle_id = vc.vehicle_id
    AND date_trunc('week', m.work_date) = date_trunc('week', w.activity_date)
  WHERE w.driver_id IS NOT NULL
  GROUP BY w.wa_number, week_start
)
SELECT
  rev.wa_number,
  r.wa_name,
  r.route_type,
  rev.week_start,
  rev.route_total_stops,
  ROUND(rev.allocated_revenue, 2)                                                             AS revenue,
  ROUND(COALESCE(lab.allocated_labor, 0), 2)                                                  AS labor,
  ROUND(COALESCE(fl.allocated_fuel, 0), 2)                                                    AS fuel,
  ROUND(COALESCE(mt.allocated_maintenance, 0), 2)                                             AS maintenance,
  ROUND(rev.allocated_revenue
        - COALESCE(lab.allocated_labor, 0)
        - COALESCE(fl.allocated_fuel, 0)
        - COALESCE(mt.allocated_maintenance, 0), 2)                                            AS contribution_margin,
  ROUND((rev.allocated_revenue
        - COALESCE(lab.allocated_labor, 0)
        - COALESCE(fl.allocated_fuel, 0)
        - COALESCE(mt.allocated_maintenance, 0)) * 100.0 / NULLIF(rev.allocated_revenue, 0), 1) AS margin_pct,
  ROUND(rev.allocated_revenue / NULLIF(rev.route_total_stops, 0), 4)                          AS revenue_per_stop,
  ROUND((COALESCE(lab.allocated_labor, 0) + COALESCE(fl.allocated_fuel, 0) + COALESCE(mt.allocated_maintenance, 0))
        / NULLIF(rev.route_total_stops, 0), 4)                                                 AS cost_per_stop
FROM revenue_w rev
LEFT JOIN routes r ON r.wa_number = rev.wa_number
LEFT JOIN labor_w lab ON lab.wa_number = rev.wa_number AND lab.week_start = rev.week_start
LEFT JOIN fuel_w  fl  ON fl.wa_number  = rev.wa_number AND fl.week_start  = rev.week_start
LEFT JOIN maint_w mt  ON mt.wa_number  = rev.wa_number AND mt.week_start  = rev.week_start
ORDER BY rev.week_start DESC, contribution_margin DESC;

Edge cases. This view is P2 because three allocation rules are not yet decided (overhead, service-charge, eComm-Ground revenue split). The DDL above uses linear stop-share allocation as a placeholder. When rules are decided, update each _w CTE accordingly. The view will produce results today against the DDL — but the results are noisy until the upstream allocation rules are tightened.

19. cost_per_stop_weekly — P1

Purpose. Fleet cost-per-stop and route-level cost-per-stop (where allocations exist). Bridge between Apex 1 financial drivers and operational decisions.

Grain. fleet + route × week. Decisions supported. VD6 M1 (P&L review), M3 (fuel by truck), FA1 (route profitability stepping stone). KPIs supported. FIN.03 (cost per stop fleet avg), FIN.04 (cost per stop by route). Source tables. settlements, payroll_records, fuel_invoice_summary, financial_periods.

CREATE OR REPLACE VIEW cost_per_stop_weekly AS
WITH labor_w AS (
  SELECT
    date_trunc('week', pay_period_start) AS week_start,
    SUM(total_employer_cost) AS labor_cost
  FROM payroll_records
  GROUP BY week_start
),
fuel_w AS (
  SELECT
    week_ending - INTERVAL 6 DAY AS week_start,
    SUM(total_amount) AS fuel_cost
  FROM fuel_invoice_summary
  GROUP BY week_start
),
overhead_w AS (
  -- Approximation: overhead from financial_periods divided by # weeks in month
  SELECT
    date_trunc('week', period_month) AS approx_week_start,
    total_other_expense / 4.0 AS estimated_overhead_per_week
  FROM financial_periods
)
SELECT
  s.week_ending - INTERVAL 6 DAY AS week_start,
  s.week_ending,
  s.total_stops,
  s.total_settlement                                                              AS revenue,
  COALESCE(l.labor_cost, 0)                                                        AS labor_cost,
  COALESCE(f.fuel_cost, 0)                                                         AS fuel_cost,
  COALESCE(o.estimated_overhead_per_week, 0)                                       AS estimated_overhead,
  COALESCE(l.labor_cost, 0) + COALESCE(f.fuel_cost, 0) + COALESCE(o.estimated_overhead_per_week, 0) AS total_cost,
  ROUND(s.total_settlement / NULLIF(s.total_stops, 0), 4)                          AS revenue_per_stop,
  ROUND(COALESCE(l.labor_cost, 0) / NULLIF(s.total_stops, 0), 4)                   AS labor_per_stop,
  ROUND(COALESCE(f.fuel_cost, 0) / NULLIF(s.total_stops, 0), 4)                    AS fuel_per_stop,
  ROUND(
    (COALESCE(l.labor_cost, 0) + COALESCE(f.fuel_cost, 0) + COALESCE(o.estimated_overhead_per_week, 0))
    / NULLIF(s.total_stops, 0),
    4
  ) AS total_cost_per_stop,
  ROUND(s.total_settlement
        - COALESCE(l.labor_cost, 0) - COALESCE(f.fuel_cost, 0) - COALESCE(o.estimated_overhead_per_week, 0), 2)
    AS gross_margin
FROM settlements s
LEFT JOIN labor_w   l ON l.week_start    = s.week_ending - INTERVAL 6 DAY
LEFT JOIN fuel_w    f ON f.week_start    = s.week_ending - INTERVAL 6 DAY
LEFT JOIN overhead_w o ON o.approx_week_start = date_trunc('week', s.week_ending)
ORDER BY s.week_ending DESC;

Edge cases. Maintenance and vehicle-cost lines are not yet allocated weekly — they fold into overhead. P1 → P2 evolution: as route_profitability_weekly matures, this view’s per-route grain will derive from that. Today it’s fleet-only.

20. monthly_pnl_rollup — P0

Purpose. Tensor P&L driven view, with category-level rollup and trend math. Drives VD6 M1 (monthly P&L review), M5 (vs prior owner), M6 (cost reduction triggers).

Grain. month × category. Decisions supported. VD6 M1, M5, M6, FA4 (net margin tracking). KPIs supported. FIN.01 (net margin), FIN.07 (labor %), FIN.08 (fleet %), FIN.09 (overhead %), FIN.12 (CC reconciliation), OP.VD6.01/02 (cash position + burn). Source tables. financial_periods, credit_card_transactions.

CREATE OR REPLACE VIEW monthly_pnl_rollup AS
WITH cc_m AS (
  SELECT
    date_trunc('month', transaction_date) AS period_month,
    cc.category_id,
    cat.category_name,
    cat.aop_bucket,
    SUM(cc.amount) AS cc_total
  FROM credit_card_transactions cc
  LEFT JOIN cost_categories cat ON cat.category_id = cc.category_id
  GROUP BY period_month, cc.category_id, cat.category_name, cat.aop_bucket
),
cc_pivot AS (
  -- Pivot CC categories to known buckets
  SELECT
    period_month,
    SUM(CASE WHEN aop_bucket = 'fleet'    THEN cc_total ELSE 0 END) AS cc_fleet,
    SUM(CASE WHEN aop_bucket = 'overhead' THEN cc_total ELSE 0 END) AS cc_overhead,
    SUM(CASE WHEN aop_bucket = 'labor'    THEN cc_total ELSE 0 END) AS cc_labor,
    SUM(cc_total) AS cc_total_all
  FROM cc_m
  GROUP BY period_month
)
SELECT
  fp.period_month,
  fp.total_revenue,
  fp.total_labor,
  fp.total_fuel,
  fp.total_maintenance,
  fp.total_insurance,
  fp.total_vehicle_cost,
  fp.total_other_expense,
  fp.net_income,
  fp.net_margin_pct,
  -- Ratios
  ROUND(fp.total_labor * 100.0 / NULLIF(fp.total_revenue, 0), 1)                                AS labor_pct,
  ROUND((fp.total_fuel + fp.total_maintenance + fp.total_vehicle_cost) * 100.0
        / NULLIF(fp.total_revenue, 0), 1)                                                        AS fleet_pct,
  ROUND((fp.total_insurance + fp.total_other_expense) * 100.0 / NULLIF(fp.total_revenue, 0), 1) AS overhead_pct,
  -- MoM deltas
  fp.total_revenue - LAG(fp.total_revenue) OVER (ORDER BY fp.period_month) AS mom_delta_revenue,
  fp.net_income - LAG(fp.net_income) OVER (ORDER BY fp.period_month)       AS mom_delta_net_income,
  -- Trailing 3-month average net margin
  ROUND(
    AVG(fp.net_margin_pct) OVER (ORDER BY fp.period_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
    1
  ) AS net_margin_trailing_3mo_pct,
  -- CC reconciliation
  cc.cc_fleet,
  cc.cc_overhead,
  cc.cc_total_all,
  ROUND(fp.total_other_expense - COALESCE(cc.cc_overhead, 0), 2) AS cc_reconciliation_residual,
  -- Threshold flags (per WF-1.2 v1.2 — to be tuned)
  CASE WHEN fp.net_margin_pct >= 18 THEN 'green'
       WHEN fp.net_margin_pct >= 10 THEN 'yellow'
       ELSE 'red' END AS net_margin_band,
  CASE WHEN ROUND(fp.total_labor * 100.0 / NULLIF(fp.total_revenue, 0), 1) <= 50 THEN 'green'
       WHEN ROUND(fp.total_labor * 100.0 / NULLIF(fp.total_revenue, 0), 1) <= 60 THEN 'yellow'
       ELSE 'red' END AS labor_pct_band
FROM financial_periods fp
LEFT JOIN cc_pivot cc ON cc.period_month = fp.period_month
ORDER BY fp.period_month DESC;