Skip to main content

​
DASHBOARD WIRING VALIDATION — Red Flags & Alignment Check

Date: 2025-12-19
Reviewer: AI Assistant
Status: 🔴 CRITICAL RED FLAGS IDENTIFIED — Cutover blocked until resolved

​
Executive Summary

The validation document DASHBOARD_WIRING_VALIDATION.md contains accurate assumptions about architecture and widget inventory, but identifies a CRITICAL BLOCKER: The API layer (api/bigquery/queries.py) has hardcoded dataset names with no mechanism to switch from payroll_analytics to payroll_analytics_shadow. Impact: The document’s Step 2 cutover plan (“Switch API dataset pointer via env var”) cannot be executed because the code doesn’t support dataset switching.

​
✅ ALIGNMENT VERIFIED

​
1. Widget Inventory (100% Match)

Document Assumption: Core dashboard widgets are the CardId set from dashboard/src/types/dashboard.ts Verification: ✅ CONFIRMED
  • File: dashboard/src/types/dashboard.ts (lines 6-23)
  • All 12 widgets listed in document match exactly:
    • KPI: kpi_totalBusinesses, kpi_totalEmployees, kpi_grossPayout, kpi_totalChargebacks
    • Commissions: commissions_agentCommissions, commissions_ownerNetCommission
    • Charts: charts_businessHealthDistribution, charts_employeeGrowthLossAnalysis
    • Growth: growth_splitGrowthLossTable, growth_newLostBusinesses
    • Rankings: rankings_top10Businesses, rankings_top10Agents

​
2. API Routes Exist (100% Match)

Document Assumption: Dashboard reads via FastAPI routes, not direct BQ Verification: ✅ CONFIRMED
  • All routes exist in api/routes/ceo_metrics.py and api/routes/dashboard.py:
    • /api/v1/ceo-metrics ✅ (line 49, api/routes/ceo_metrics.py)
    • /api/v1/top-businesses ✅ (line 35, api/routes/dashboard.py)
    • /api/v1/top-agents ✅ (line 81, api/routes/dashboard.py)
    • /api/v1/growth-loss ✅ (line 135, api/routes/dashboard.py)
    • /api/v1/growth-loss-details ✅ (line 213, api/routes/dashboard.py)
    • /api/v1/new-lost-businesses ✅ (line 260, api/routes/dashboard.py)
    • /api/v1/business-health ✅ (line 330, api/routes/dashboard.py)

​
3. Routes Call api/bigquery/queries.py Functions (100% Match)

Document Assumption: Routes call functions from api/bigquery/queries.py Verification: ✅ CONFIRMED
  • ceo_metrics.py imports: get_ceo_snapshot_from_view, get_ceo_metrics_range (lines 17-20)
  • dashboard.py imports: get_top_businesses_from_view, get_top_agents_from_view, get_growth_loss_summary, get_growth_loss_details, get_business_health_distribution, get_new_lost_businesses (lines 11-18)

​
4. Multi-Tenant Isolation (100% Match)

Document Assumption: JWT → tenant_id dependency enforces isolation Verification: ✅ CONFIRMED
  • All routes use tenant_id: str = Depends(get_tenant_id) (e.g., ceo_metrics.py line 54, dashboard.py line 40)
  • get_tenant_id extracts tenant_id from JWT token (api/dependencies.py)

​
5. Shadow Dataset Publishing (100% Match)

Document Assumption: Repo B publishes to payroll_analytics_shadow Verification: ✅ CONFIRMED
  • Script: scripts/publish_repo_b_to_shadow.py (line 207: dataset_id = "payroll_analytics_shadow")
  • Publishes to payroll_analytics_shadow.ceo_metrics table (line 208)
  • Creates payroll_analytics_shadow.ceo_snapshot view (line 305)

​
🔴 CRITICAL RED FLAGS

​
RED FLAG #1: Hardcoded Dataset Names in API Layer (BLOCKER)

Severity: 🔴 CRITICAL — Blocks cutover Issue: api/bigquery/queries.py hardcodes payroll-bi-gauntlet.payroll_analytics.* in all SQL queries. There is no environment variable or configuration mechanism to switch to payroll_analytics_shadow. Evidence: 
# api/bigquery/queries.py (line 361)
FROM `payroll-bi-gauntlet.payroll_analytics.ceo_snapshot`

# api/bigquery/queries.py (line 650)
FROM `payroll-bi-gauntlet.payroll_analytics.top_businesses`

# api/bigquery/queries.py (line 616)
FROM `payroll-bi-gauntlet.payroll_analytics.business_growth_loss`
Grep Results: Found 105 hardcoded references to payroll-bi-gauntlet.payroll_analytics.* in api/bigquery/queries.py Document Assumption vs Reality:
  • Document says: “Preferred architecture: ONE config value controls dataset selection (e.g. env var BQ_DATASET_ANALYTICS)”
  • Reality: No such env var exists. Project ID is configurable (GCP_PROJECT_ID), but dataset name is hardcoded.
Impact:
  1. Cutover Step 2 cannot be executed — There’s no env var to flip
  2. Dashboard will continue reading legacy dataset even after shadow data is published
  3. Parity validation will pass (shadow data exists), but production dashboard won’t use it
Required Fix:
  1. Add BQ_DATASET_ANALYTICS env var (defaults to payroll_analytics)
  2. Refactor all queries in api/bigquery/queries.py to use configurable dataset name
  3. Update Cloud Run deployment to set BQ_DATASET_ANALYTICS=payroll_analytics_shadow for cutover
Estimated Effort: 2-4 hours (find/replace + testing)

​
RED FLAG #2: Table vs View Confusion

Severity: 🟡 MEDIUM — Needs clarification Issue: Document mentions ceo_metrics table, but some queries use ceo_snapshot view. Need to clarify which endpoints use which. Evidence:
  • Document Task A1 queries payroll_analytics_shadow.ceo_metrics (table)
  • api/bigquery/queries.py line 361 queries payroll_analytics.ceo_snapshot (view)
  • Shadow publisher creates BOTH:
    • Table: payroll_analytics_shadow.ceo_metrics (from publish_repo_b_to_shadow.py line 208)
    • View: payroll_analytics_shadow.ceo_snapshot (from publish_repo_b_to_shadow.py line 305)
Clarification Needed:
  • Which endpoints should read from ceo_metrics table vs ceo_snapshot view?
  • Document Task B4 mapping table shows ceo_metrics for all KPI widgets — is this correct?
Impact: Low — Both exist in shadow dataset, but mapping table may be inaccurate.

​
RED FLAG #3: Missing Dataset Configuration Pattern

Severity: 🟡 MEDIUM — Inconsistency with Repo B Issue: Repo B has BQ_SHADOW_MODE env var pattern (repo_b/upload_to_bq.py line 62), but API layer doesn’t follow same pattern. Evidence: 
# repo_b/upload_to_bq.py (line 62)
shadow_mode = os.getenv("BQ_SHADOW_MODE", "0").strip() == "1"
if shadow_mode:
    return f"{base_name}_shadow"

# api/bigquery/queries.py (line 98)
PROJECT_ID = os.getenv("GCP_PROJECT_ID", "payroll-bi-gauntlet")
# NO DATASET CONFIGURATION
Recommendation:
  • Option A: Use same BQ_SHADOW_MODE pattern (adds _shadow suffix)
  • Option B: Use explicit BQ_DATASET_ANALYTICS env var (more flexible)
Impact: Low — Architectural inconsistency, but doesn’t block functionality.

​
RED FLAG #4: Document Task B3 Assumption Not Validated

Severity: 🟡 MEDIUM — Validation incomplete Issue: Document Task B3 says “inspect api/bigquery/queries.py for hard-coded dataset strings” — this check hasn’t been run yet (document is a template). Required Action: Complete Task B3 mapping table by:
  1. Grepping api/bigquery/queries.py for all dataset references
  2. Mapping each query function to its dataset/table
  3. Confirming all CEO/lifecycle endpoints read from shadow dataset (after RED FLAG #1 is fixed)
Impact: Medium — Validation cannot be completed until RED FLAG #1 is fixed.

​
🟢 MINOR ALIGNMENT ISSUES

​
Issue #1: Document SQL Uses Placeholder PROJECT

Severity: 🟢 MINOR — Documentation clarity Issue: Document Task A1-A4 SQL uses PROJECT placeholder, but actual project ID is payroll-bi-gauntlet. Recommendation: Update document SQL examples to use actual project ID or @project_id parameter.

​
Issue #2: Document Mentions ceo_snapshots Plural

Severity: 🟢 MINOR — Typo Issue: Document line mentions “ceo_metrics and/or ceo_snapshots” — should be singular ceo_snapshot (it’s a view, not a table). Recommendation: Fix typo in document.

​
📋 REQUIRED ACTIONS BEFORE CUTOVER

​
Priority 1 (BLOCKER)

  1. Fix RED FLAG #1: Implement dataset configuration in api/bigquery/queries.py
    • Add BQ_DATASET_ANALYTICS env var (default: payroll_analytics)
    • Refactor all queries to use configurable dataset name
    • Test with both payroll_analytics and payroll_analytics_shadow

​
Priority 2 (HIGH)

  1. Complete Task B3: Generate actual mapping table
    • Run grep to find all dataset references
    • Map each widget → endpoint → query function → dataset/table
    • Verify 100% of dashboard endpoints will read from shadow after cutover
  2. Clarify RED FLAG #2: Document which endpoints use ceo_metrics table vs ceo_snapshot view
    • Update Task B4 mapping table with correct table/view names

​
Priority 3 (MEDIUM)

  1. Fix RED FLAG #3: Align dataset configuration pattern with Repo B (optional)
    • Decide on single pattern (BQ_SHADOW_MODE vs BQ_DATASET_ANALYTICS)
    • Update both codebases to use same pattern
  2. Fix MINOR ISSUES: Update document SQL examples and fix typos

​
🎯 GO/NO-GO RECOMMENDATION

Current Status: 🔴 NO-GO Blockers:
  • RED FLAG #1: No dataset switching mechanism in API layer
Required Before GO:
  1. ✅ Implement BQ_DATASET_ANALYTICS env var support
  2. ✅ Complete Task B3 mapping table validation
  3. ✅ Test cutover with shadow dataset (smoke test endpoints)
  4. ✅ Verify parity gate (Task C) passes for July 2025
Estimated Time to GO: 4-6 hours (fix RED FLAG #1 + validation)

​
📝 NOTES

​
Dataset Configuration Implementation Pattern

Recommended approach (based on existing PROJECT_ID pattern): 
# api/bigquery/queries.py (add after line 98)
DATASET_ANALYTICS = os.getenv("BQ_DATASET_ANALYTICS", "payroll_analytics")
DATASET_RAW = os.getenv("BQ_DATASET_RAW", "payroll_raw")
DATASET_PROCESSED = os.getenv("BQ_DATASET_PROCESSED", "payroll_processed")

# Then in queries:
FROM `{PROJECT_ID}.{DATASET_ANALYTICS}.ceo_snapshot`
Alternative approach (matches Repo B pattern): 
# api/bigquery/queries.py
SHADOW_MODE = os.getenv("BQ_SHADOW_MODE", "0").strip() == "1"
DATASET_ANALYTICS = f"payroll_analytics{'}_shadow' if SHADOW_MODE else ''"
Recommendation: Use explicit env var (BQ_DATASET_ANALYTICS) for flexibility and clarity.

​
✅ VALIDATION CHECKLIST

  • Widget inventory matches document
  • API routes exist and are correctly wired
  • Routes call api/bigquery/queries.py functions
  • Multi-tenant isolation implemented
  • Shadow dataset publishing confirmed
  • Dataset configuration mechanism exists ← BLOCKER
  • Task B3 mapping table completed
  • Table vs view usage clarified
  • Parity gate validation passed
Next Steps: Fix RED FLAG #1, then re-run validation checklist.