CPL Dashboard — Element Source & Algorithm Map

Every number on the dashboard, where it comes from, and how it's computed
Click any element to expand its source, algorithm, data flow, and where it can be adjusted. Values now come from Supabase + the live CCCCO API — the Excel workbook was retired.
LIVE — scraped from CCCCO API SUPABASE — projects / workplan_goals tables EXHIBIT — from CustomReport JSON COMPUTED — pipeline algorithm HARDCODED — static in Python HISTORY — from kpi_history.json
60+ dashboard elements mapped
3 live data sources (CCCCO API, Supabase, CustomReport JSON)
8 dashboard sections
Reference refreshed 2026-07-27
📖 This map is read-only documentation — you don't change values here. To edit a project's title, metric, status, or notes (e.g. renaming 1.1.3 “WestEd CPL Scope of Work”), open the dashboard, sign in as an allowed reviewer, and click the field. Project cards on the Activities tab and the title cells on the Annual Workplan Goals tab write straight to Supabase public.projects — a title edit PATCHes projects.name, the single source of truth. The daily workflow then republishes the dashboard from Supabase.

1. Headline KPI Cards

10 CARDS
▶ KPI-1 Cumulative CPL Students LIVE FALLBACK: Supabase

Primary Source

live_metrics.json → raw.Students (scraped from CCCCO API)

CCCCO API → Cloudflare Worker → live_metrics.json → merge_live_metrics() → KPI Card

Fallback Source

Supabase projects → id 3.1 → kpi_metric (baseline "current")

Then a committed snapshot, then the legacy .xlsx — deep outage fallbacks only

🗄 Supabase: projects[id=3.1].kpi_metric

Algorithm

# compute_headline_kpis() reads the Supabase projects baseline students = get_metric("3.1") # projects[3.1].kpi_metric # Then overwritten by merge_live_metrics(): kpis["cumulative_students"]["value"] = metric["value"] # Value = ALL COLLEGES row, Students field

Breakdowns

Injected from live_metrics.json → metrics[0].breakdowns:

• Military — raw.MilitaryStudents

• Workforce — raw.NonMilitaryStudents

• Apprentice — raw.AprenticeStudents (API typo)

Override / Adjust

Primary: LIVE from the CCCCO API (raw.Students) — nothing to set.

Baseline fallback: edit projects.kpi_metric for id 3.1 via the signed-in editor. A hard override lives in kb/dashboard_config.json (the old Excel Col AF/AG override columns were retired).

▶ KPI-2 Eligible Units LIVE

Primary Source

live_metrics.json → raw.Units

API endpoint: /api/potential-savings → ALL COLLEGES row

Algorithm

# merge_live_metrics() — direct pass-through kpis["eligible_units"] = { "value": metric["value"], # formatted by Worker "breakdowns": [Mil, WF, App units] }

Breakdowns

• Military — raw.MilitaryCredits

• Workforce — raw.NonMilitaryCredits

• Apprentice — raw.ApprenticeshipCredits

• Avg per Student — raw.AverageUnits

Fallback

Supabase projects id 3.2 → kpi_metric

🗄 Supabase: projects[id=3.2].kpi_metric
▶ KPI-3 Transcribed Units LIVE + EXHIBIT

Primary Source

live_metrics.json → raw.TranscribedUnits

Algorithm

# merge_live_metrics() — direct value # merge_exhibit_metrics() adds Applied Credits: if credit_distribution: bds.append({"label": "Applied Credits", "value": cd["total_applied_credits"]})

Breakdowns

• Military — raw.TranscribedMilitaryUnits

• Workforce — raw.TranscribedNonMilitaryUnits

• Apprentice — raw.TranscribedApprenticeshipUnits

• Applied Credits — from CustomReport credit_distribution (enrichment)

Fallback

Supabase projects id 3.2 → kpi_metric (same row as eligible units)

▶ KPI-4 Credit Recommendations EXHIBIT

Primary Source

CustomReport_*.json → View_ArticulatedMAPExhibits_APIDataset

Parsed by _parse_exhibits() → exhibit_data["total_credit_recs"]

Algorithm

# _parse_exhibits() counts rows in exhibits dataset total_credit_recs = len(exhibit_rows) # merge_exhibit_metrics() replaces KPI: kpis["credit_recommendations"] = { "value": total_credit_recs, "breakdowns": [CCC Collaborative, Local] }

Breakdowns

• CCC Collaborative — exhibits where Collaborative Type = CCC

• Local — individual college articulations

• Footnote — Top 5 CPL types by count

Fallback

Supabase projects id 2.1 → kpi_metric

🗄 Supabase: projects[id=2.1].kpi_metric
▶ KPI-5 MAP Exhibits EXHIBIT

Primary Source

CustomReport_*.json → exhibit_data["unique_exhibits"]

Algorithm

# _parse_exhibits() counts unique MAP Exhibit Name values unique_exhibits = len(set(row["MAP Exhibit Name"] for row in exhibits)) # Breakdowns split CCC Collaborative vs Local

Breakdowns

• CCC Collaborative — ccc["unique_exhibits"]

• Local — exhibit_data["local"]["unique_exhibits"]

Sub-label

"{originating_colleges} originating colleges"

▶ KPI-6 CCC Collaborative Adoption EXHIBIT

Source & Algorithm

# Colleges adopting statewide exhibits value = ccc["adopting_colleges"] # Count of unique colleges with Collaborative Type = CCC # from View_ArticulatedMAPExhibits_APIDataset

Breakdowns

• Collaborative Exhibits — ccc["unique_exhibits"]

• Collaborative Credit Recs — ccc["credit_recs"]

▶ KPI-7 Active Colleges LIVE 3-of-5 TIER ALGO

Source

live_metrics.json → computed by Cloudflare Worker from per-college API data

3-of-5 Tier Classification Algorithm

# Cloudflare Worker classifies each college (115 total) criteria = [ Students ≥ 500, # Scale of program Units ≥ 3,000, # Breadth of credit recommendations AverageUnits ≥ 5, # Per-student benefit depth TranscribedUnits/Units ≥ 25%, # Back-end follow-through TranscribedAverage ≥ 3 # Verified per-student benefit ] Leading = meets ≥ 3 of 5 criteria Inactive = Students < 10 AND Units = 0 Advancing = everything else (active, building)

Breakdowns

• Leading — tiers.leading.count (13)

• Advancing — tiers.advancing.count (82)

• Inactive — tiers.inactive.count (20)

• Active Total = Leading + Advancing (95)

Footnote

Auto-generated criteria list from Worker response

🗄 Supabase Fallback: projects[id=3.3].kpi_metric
▶ KPI-8 Articulating Colleges EXHIBIT

Algorithm

# merge_exhibit_metrics() value = exhibit_data["articulation_colleges"] # = count of unique colleges in exhibits dataset breakdowns: Originating = exhibit_data["originating_colleges"] Adopting CCC = ccc["adopting_colleges"]

Source

CustomReport_*.json → View_ArticulatedMAPExhibits_APIDataset

Unique "College" values in exhibit rows

▶ KPI-9 Estimated Savings LIVE

Source

live_metrics.json → raw.Savings

Computed by CCCCO: eligible units × avg credit cost

Algorithm

# compute_headline_kpis() sets static default: savings = "$269M" # merge_live_metrics() overwrites with CCCCO value # Breakdowns proportional to Mil/WF/App credit ratios
▶ KPI-10 Veteran Sprint SUPABASE + LIVE INJECT

Primary Source

Supabase projects id 4.1 (Veteran Sprint) — kpi_metric. Post-reorg #872 the old 4.1a sub-id is gone.

Algorithm

# compute_headline_kpis() value = proj_map["4.1"]["kpi_metric"] # merge_live_metrics() replaces placeholders: {jst_credits} → MilitaryStudents from live data {eligible_cpl} → MilitaryCredits from live data # Basic Training Credit = value + " Colleges"

Breakdowns (hybrid)

• JST Credits — {jst_credits} placeholder → replaced by live_data → cumulative_students → Military breakdown

• Basic Training Credit — projects[4.1].kpi_metric + " Colleges" (Supabase)

• Eligible CPL — {eligible_cpl} placeholder → replaced by live_data → eligible_units → Military breakdown

▶ KPI-11 20-Year Impact LIVE

Source

live_metrics.json → raw.YearImpact

Computed by CCCCO: 20-year economic impact model (Beacon Economics)

Algorithm

# merge_live_metrics() — direct pass-through # Breakdowns proportional to credit ratios

2. Activity Metrics Cards

4 ACTIVITIES · 32 SUB-ACTIVITIES

Universal Source Pattern — Supabase (Excel retired)

# build_activity_kpis(projects, activities) — pure grouping over two # Supabase-loaded inputs; no Excel columns anymore. projects = load_projects(wb) # Supabase public.projects → snapshot → .xlsx (deep fallback) activities = build_workplan_goals_from_supabase(...) # workplan_goals + associations # Per sub-activity card: name = projects.name # editable title (single source of truth) metric = projects.kpi_metric # baseline; OVERWRITTEN by apply_live_activity_current() unit = projects.kpi_unit status = projects.status # pct = projects.percent_complete goals = workplan_goals.yr_2025_26 … yr_2029_30 # annual + 2030 ladder # Grouped by projects.workplan_activity (NOT the id prefix). # Activity labels come from the Supabase workplan_goals Activity rows.
Supabase projects + workplan_goals → load_projects() / build_workplan_goals_from_supabase() → build_activity_kpis() → apply_live_activity_current() → Activity Metrics cards

The "Current" big number — apply_live_activity_current()

# Priority order; card == headline KPI == Annual Workplan by construction: 1. PID_TO_KPI_KEY sub-activity → LIVE merged headline KPI value # metric_source='live' 3.1→cumulative_students · 3.2→transcribed_units · 2.1→credit_recommendations 3.3→active_colleges · 4.1→veteran_sprint 2. PID_TO_KPI_BREAKDOWN card → a headline KPI breakdown row # metric_source='live' 3.1.1→workforce · 3.1.2→military · 3.1.3→apprentice 3. else workplan_goals.current → explicit manual value # metric_source='workplan' 4. else projects.kpi_metric → Supabase baseline

The old populate_current_metrics() / _pmetric_int / _ppct / _pcount cluster and the "Excel Col R/S/T-AC" columns were deleted in the Excel-retirement work (PR #213) — ignore any doc that still cites them.

▶ Act 1 Build AI-Enhanced CPL Infrastructure SUPABASE

Sub-activities (Supabase projects, grouped by workplan_activity)

1.1 MAP Platform Development · 1.1.1 AI Certification-to-Course Matching · 1.1.2 AI Apprenticeship CPL Tools · 1.1.3 WestEd CPL Scope of Work · 1.2 System Integration & Interoperability · 1.3 CPL Student Portal · 1.4 California Credential Registry

Current value

Each card's big number = projects.kpi_metric unless apply_live_activity_current() wires it to a live headline KPI (none in Activity 1 today).

Edit

Rename / re-metric a sub-activity via the signed-in editor — the title cell PATCHes projects.name (e.g. 1.1.3 is the WestEd rename).

▶ Act 2 Faculty Workgroups & Credit Recommendations SUPABASE 1 LIVE-WIRED

Sub-activities (Supabase projects)

2.1 Statewide Credit Recommendations · 2.2 Faculty Discipline Workgroups · 2.3 Common Course Crosswalks · 2.4 Validated Skills

Live-wired card

2.1 → credit_recommendations headline KPI via PID_TO_KPI_KEY (the exhibit-count total from CustomReport JSON). The rest show projects.kpi_metric / workplan_goals.current.

Edit

Title / metric edits PATCH Supabase projects via the signed-in dashboard editor.

▶ Act 3 Build CPL Data Infrastructure SUPABASE LIVE-WIRED

Sub-activities (Supabase projects)

3.1 CPL Offers & Awards Tracking — All Populations · 3.1.1 Working Adults · 3.1.2 Veterans & Service Members · 3.1.3 Apprentices & Journeyworkers · 3.1.4 Other Populations · 3.2 CPL Units Transcription · 3.3 Statewide Adoption & Participation · 3.4 Student Impact Rate Survey · 3.5 Student Stories · 3.6 College Tracking & Recognition · 3.7 RP Group CPL Field Survey · 3.8 MIS Data Reconciliation

Live-wired cards

3.1→cumulative_students, 3.2→transcribed_units, 3.3→active_colleges (headline KPIs). Population cards 3.1.1/3.1.2/3.1.3→ the workforce / military / apprentice breakdown rows via PID_TO_KPI_BREAKDOWN.

Edit

Title / metric edits PATCH Supabase projects. Live-wired cards read the merged headline KPI, so their number can't be hand-edited here.

▶ Act 4 Sprints, Projects, Partnerships & Scale SUPABASE 1 LIVE-WIRED

Sub-activities (Supabase projects)

4.1 Veteran Sprint · 4.1.1 29 Palms Marine Corps Base Demo · 4.2 Apprenticeship Sprint · 4.3 Strategic Partnerships · 4.4 Technical Assistance & Training · 4.4.1 VRC CPL Module Revision · 4.5 Law & Regulation Review · 4.6 Sustainable Funding · 4.7 CPL Legislative Advocacy (2026 Session)

Sprints are tags now

The old 4.1 composite of 4.1a–4.1d is gone (reorg #872). Sprints are a cross-cutting sprint_tag (Veteran / Apprenticeship / Statewide Adoption); Statewide Adoption moved under 3.3.

Live-wired card

4.1 Veteran Sprint → veteran_sprint headline KPI (PID_TO_KPI_KEY). Others show projects.kpi_metric / workplan_goals.current.

3. KPI Trends Card

16 METRICS TRACKED
▶ TRENDS Daily KPI Snapshot & Sparklines HISTORY

Source

kpi_history.json — appended daily by log_daily_snapshot()

live_metrics.json + CustomReport JSON → log_daily_snapshot() → kpi_history.json → render_kpi_history_card() → Trend Card

Metrics Logged Daily

• students, students_military, students_workforce, students_apprentice

• eligible_units, transcribed_units

• savings_m (Savings / 1M), year_impact_b (YearImpact / 1B)

• active_colleges, leading_colleges, star_colleges

• credit_recs, map_exhibits, ccc_collaborative, articulating_colleges

Algorithm: Sparkline

# _sparkline_svg(): inline SVG polyline points = normalize(values, 0→height) SVG = polyline + gradient fill + end dot # _delta_badge(): % change from N days ago diff = current - previous pct = diff / previous × 100 color = green(▲) or red(▼)

Grouping

Academic quarters: Q1=Jul-Sep, Q2=Oct-Dec, Q3=Jan-Mar, Q4=Apr-Jun

Delta badges compare current entry to quarter start, 30 days ago, and 7 days ago

4. College Activity Card

PER-COLLEGE
▶ COLLEGE Per-College Activity & Military Student Counts LIVE EXHIBIT

Last Activity Per College

# _compute_college_last_activity() source: View_ArticulatedCollegeCourses_APIDataset field: "Last Submitted On" # Max datetime per college name # Filters out test colleges

Military Students Per College

# _compute_college_military_students() source: View_StudentAggregatedValues_APIDataset # Count distinct students where MilitaryCredits > 0 # Filters test colleges and potential students

Tier Classification

From live_metrics.json → tiers (Worker-computed, 3-of-5 algorithm)

Render

render_college_activity_card() — combines live tiers, last activity dates, and military counts into a sortable card

5. Workplan Trajectory Charts

2 CHARTS + TABLE
▶ CHART-G Goal Trajectory (250K target) LIVE HARDCODED BASELINES SUPABASE GOALS

Algorithm: render_workplan_charts_html()

# Baselines (hardcoded — 2024 actuals) BASE_MIL = 8,248 BASE_WF = 9,181 BASE_APP = 196 # Current values (from live data sub-populations) mil_now = live → MilitaryStudents (default 21,866) wf_now = live → NonMilitaryStudents (default 21,526) app_now = live → AprenticeStudents (default 681) # Annual goal/stretch from Supabase workplan_goals (yr_2025_26 … yr_2029_30) # Looked up by name containing "military"/"workforce"/"apprentice" # build_trajectory(baseline, annual_vals, endpoint): if annual_vals are cumulative (ascending): pts = [(2024, baseline), (2025, midpoint), (2026+i, val)] else: # incremental proportional interpolation from baseline → endpoint # Actuals (hardcoded for historical years) mil_actual = [[2024, 8248], [2025, 18500], [2026, mil_now]] wf_actual = [[2024, 9181], [2025, 19200], [2026, wf_now]] app_actual = [[2024, 196], [2025, 300], [2026, app_now]] # Chart: solid line = actuals, dashed = projection # Target lines at 250K (goal) and 500K (stretch)

Adjustable Values

• Baselines: Hardcoded in Python (line 1655)

• 2025 actuals: Hardcoded (lines 1710-1713)

• Current values: From live data sub-populations

• Goal/Stretch endpoints: From Supabase workplan_goals

• Targets: 250K / 500K hardcoded

Adjust

Baselines, 2025 actuals, and the 250K/500K targets are Python constants in excel_to_dashboard.py — change the generator to move them. Goal/stretch endpoints edit live on the Annual Workplan Goals tab (Supabase workplan_goals).

▶ TABLE Annual Workplan Goals Table SUPABASE DERIVED

Algorithm: build_workplan_goals_from_supabase()

# Reads Supabase workplan_goals (GOAL/STRETCH rows per activity_id) + # workplan_activity_associations; snapshot fallback. for each sub-activity: goal_values = workplan_goals GOAL row # yr_2025_26 … yr_2029_30 stretch_values = workplan_goals STRETCH row current = live headline KPI (if PID_TO_KPI_KEY-mapped) else workplan_goals.current (manual) else projects.kpi_metric goal_total = sum(goal_values); stretch_total = sum(stretch_values) # Percentages detected: all(0 < v < 1) → displayed as %

6. Budget Section

5 SUB-SECTIONS
▶ BUD-1 Funding Overview & Grand Total SUPABASE: budget_funding

Source: load_budget() → build_budget_from_supabase()

# Supabase budget_funding + personnel → snapshot → .xlsx (deep fallback) Funding rows: budget_funding (yr_2025_26_budget … yr_2029_30_budget, total, avg) Subtotals: Ops / Special Projects / Local Funding Grand Total: Σ funding rows # Inline editor: budget_editor.js PATCHes budget_funding (7 cells/row, # RLS-gated to is_allowed_reviewer()) # Stacked bar chart = % of grand total per source
▶ BUD-2 Expenditure Summary (by Category & Area) SUPABASE: budget tables

By Category

Top expense categories with yearly and total values

Reshaped from the loaded budget dataset (Supabase)

By Area

All expense areas with yearly and total values

Reshaped from the loaded budget dataset (Supabase)

▶ BUD-3 Expenditure Detail (Line Items) SUPABASE: budget tables

Source

# Expenditure line items from the loaded budget dataset (Supabase) Fields: description, category, type, source, yearly budgets (5 years), total # Grouped by type: Operations, Special Projects, Local Funding # Displayed as collapsible accordion
▶ BUD-4 Personnel Plan SUPABASE: personnel

Source

Supabase personnel: title, FTE per year, total compensation (+ a totals row)

Algorithm

Direct pass-through — no computation. Values from the Supabase personnel rows.

▶ BUD-5 Key Factors (FTE, COLA, Indirect, Platform) CONFIG carve-out (not yet migrated) AVERAGED

Algorithm

# Carve-out: factors + year labels are still read from the legacy # workbook (read_budget_plan) — the last budget bit not yet migrated # to Supabase (blocks retiring the .xlsx; see CLAUDE.md roadmap P5). factors = {fte, platform_maint, platform_dev, college_funding, colleges_eligible, cola_rate, indirect_rate} # cola_rate / indirect_rate displayed as % # Each factor = average of non-zero values across 5 years

7. Vision 2030 Section

3 GOALS
▶ V2030-1 Goal 1: Expand Equitable Access COMPUTED PARTIAL HARDCODE

Algorithm

# main() → data["vision2030"]["goals"][0] current = kpis['cumulative_students']['value'] # dynamic from headline KPI target = "250,000 Californians by 2030" # hardcoded progress = 17 # hardcoded percentage # Progress bar should be: current / 250000 × 100
▶ V2030-2 Goal 2: Build Unified System HARDCODED

Values

progress = 55 # hardcoded current = "MAP at 116 colleges; Dashboard live; Portal launching"

Note

Entirely hardcoded in main() — Vision 2030 is intentionally static/computed (no Excel, no Supabase migration needed). Change the generator to update it.

▶ V2030-3 Goal 3: Sustainable Policies & Resources HARDCODED

Values

progress = 60 # hardcoded current = "AB 123 chaptered; $20M allocated; 1,000+ trained"

Note

Entirely hardcoded in main() — static/computed by design; edit the generator to change it.

8. Exhibit Analysis Tables

6 ANALYSIS VIEWS
▶ EXH Statewide Exhibit Analysis (6 tables) EXHIBIT COMPUTED

Source & Algorithm: build_exhibit_analysis_tables()

# All from CustomReport_*.json → View_ArticulatedMAPExhibits_APIDataset 1. By College: group exhibits by Articulation College → count recs, exhibits, disciplines per college → classify CCC Collaborative vs Local vs Industry 2. By Discipline: TOP Code → CCC Discipline mapping (TOP_Code_Lookup.xlsx) → count recs, exhibits, courses, colleges per discipline 3. By CPL Type: group by CPL Type Description → count recs, exhibits, colleges per type 4. By Mode of Learning: group by CPL Mode of Learning → count recs, exhibits, colleges per mode 5. Top 50 Exhibits: sort by credit rec count DESC, take top 50 → show title, recs, courses, colleges, CPL type, discipline 6. Collaborative Analysis: CCC Collaborative vs Local vs Industry → count recs, exhibits, colleges per collaborative type

Output

Exported to statewide_data.js as window.CPL_STATEWIDE

Rendered interactively by statewide_interactive.js