​

Updated Plan Review — ChatGPT Plan v2 (Phases 0-2)

​

Executive Summary

​

✅ CRITICAL RED FLAGS — RESOLVED

​

✅ RED FLAG #1: Missing Dataset Configuration — FIXED

• Phase 1 now includes ALL three datasets:
  • BQ_DATASET_ANALYTICS="payroll_analytics"
  • BQ_DATASET_RAW="payroll_raw" ✅ ADDED
  • BQ_DATASET_PROCESSED="payroll_processed" ✅ ADDED
• Identifier builder supports all three: bq_table(dataset_kind: Literal["analytics","raw","processed"], table: str)
• Tests check for ALL hardcoded datasets ✅

​

✅ RED FLAG #2: BigQuery Naming Rules — ADDRESSED

• Plan says “Use BigQuery-accurate rules (per plan review)”
• Specifies rejection of backticks/spaces/dots/newlines (security-critical)
• Plan acknowledges need for accurate validation but defers exact regex to implementation (acceptable)

# Project: starts with letter, contains [a-z0-9-], length 6-30
# Dataset/Table: ^[a-zA-Z_][a-zA-Z0-9_]*$ (no hyphens, no leading numbers)

​

✅ RED FLAG #3: Phase 0 Authentication — FIXED

• Explicit env vars: API_TOKEN, API_USER, API_PASSWORD, TENANT_ID
• Auth flow specified:
  1. If API_TOKEN set: use it
  2. Else POST /api/v1/auth/token with credentials ✅ CORRECT ENDPOINT
  3. Validate tenant_id matches TENANT_ID env var ✅
• Default tenant: creative_benefit_strategies ✅

​

✅ RED FLAG #4: Phase 0 Diff Tolerances — FIXED

• Explicit field lists:
  • FINANCIAL_FIELDS = ["gross_payout","chargebacks","net_payout","business_owner_commission","agent_commissions","total_commission"]
  • COUNT_FIELDS = ["total_employees","total_businesses","total_policies","total_lives","new_businesses","dropped_businesses"]
• Explicit tolerances:
  • FINANCIAL_TOLERANCE = Decimal("0.01")
  • COUNT_TOLERANCE = 0 (exact match)
  • PERCENT_TOLERANCE = Decimal("0.01")
• New fields: warn but don’t fail ✅

​

✅ MEDIUM-PRIORITY ISSUES — RESOLVED

​

✅ Issue #1: Phase 1 Test Coverage — FIXED

• Test test_no_hardcoded_project_dataset_remains_in_queries_py() scans for:
  • payroll-bi-gauntlet.payroll_analytics
  • payroll-bi-gauntlet.payroll_raw ✅ ADDED
  • payroll-bi-gauntlet.payroll_processed ✅ ADDED
• Also checks for backticked fully-qualified hardcodes ✅

​

✅ Issue #2: Phase 2 Preflight SQL — ADDRESSED

• Plan says “either .sql files or python script” (flexible)
• References validation document Task A1-A4 SQL templates
• Stores results in docs/DASHBOARD_PREFLIGHT_RESULTS.md ✅

​

✅ Issue #3: Phase 2 Mapping Table — FIXED

• Plan includes scripts/generate_wiring_map.py ✅
• Specifies data sources:
  1. dashboard/src/types/dashboard.ts (CardId list)
  2. Dashboard API client (endpoint usage)
  3. api/routes/*.py (endpoint → query functions)
  4. api/bigquery/queries.py (query function → table identifiers)
• Outputs to docs/DASHBOARD_WIRING_VALIDATION.md ✅

​

🟡 MINOR ISSUES — ACCEPTABLE

​

Issue #1: Shadow Dataset Conditional Logic

• scripts/create_shadow_datasets.py creates all three shadow datasets
• repo_b/upload_to_bq.py uses BQ_SHADOW_MODE=1 to write to shadow datasets
• Multiple scripts reference payroll_raw_shadow and payroll_processed_shadow

​

Issue #2: Identifier Builder Location

​

Issue #3: Baseline Storage Path

​

🔍 ADDITIONAL VALIDATION

​

Security Validation — EXCELLENT

• ✅ Identifier validation with fail-fast on invalid chars
• ✅ Rejects backticks, spaces, dots, semicolons, newlines
• ✅ RuntimeError on invalid values (deploy fails loudly)
• ✅ No string concatenation from user input (uses env vars only)

​

Regression Testing — EXCELLENT

• ✅ Phase 0 baseline capture BEFORE code changes
• ✅ Phase 1 regression proof: re-run baseline capture with defaults
• ✅ Phase 1 diff: compare golden vs post-refactor (must pass)
• ✅ Explicit tolerances prevent false positives

​

Shadow Cutover Logic — EXCELLENT

• ✅ Conditional shadow dataset usage (only if exists)
• ✅ Phase 2 shadow parity comparison
• ✅ GO/NO-GO decision gate
• ✅ Cutover/rollback steps in final document

​

📋 VALIDATION CHECKLIST

• Plan addresses RED FLAG #1 (dataset configurability) ✅ ALL THREE DATASETS
• Plan aligns with validation document structure ✅
• Plan includes baseline capture (Phase 0) ✅ WITH AUTH FLOW
• Plan includes security validation (Phase 1) ✅ SQL INJECTION PREVENTION
• Plan includes parity gates (Phase 2) ✅
• Plan covers ALL datasets (raw, processed, analytics) ✅ FIXED
• Plan specifies authentication flow (Phase 0) ✅ FIXED
• Plan specifies diff tolerances (Phase 0) ✅ FIXED
• Plan validates BigQuery naming rules (Phase 1) ✅ ADDRESSED
• Plan automates mapping table generation (Phase 2) ✅ FIXED

​

🎯 FINAL ASSESSMENT

​

✅ APPROVED FOR EXECUTION

• RED FLAG #1: All datasets included ✅
• RED FLAG #2: Naming rules addressed ✅
• RED FLAG #3: Auth flow specified ✅
• RED FLAG #4: Tolerances specified ✅

• Test coverage expanded ✅
• Preflight SQL addressed ✅
• Mapping table automated ✅

• SQL injection prevention ✅
• Fail-fast validation ✅
• No user input concatenation ✅

• Baseline capture ✅
• Diff validation ✅
• Explicit tolerances ✅

​

📝 MINOR RECOMMENDATIONS (Non-Blocking)

​

1. BigQuery Naming Regex (Implementation Detail)

import re

PROJECT_PATTERN = re.compile(r'^[a-z][a-z0-9-]{5,29}$')  # 6-30 chars, starts with letter
DATASET_PATTERN = re.compile(r'^[a-zA-Z_][a-zA-Z0-9_]*$')  # No hyphens, no leading numbers
TABLE_PATTERN = re.compile(r'^[a-zA-Z_][a-zA-Z0-9_]*$')  # Same as dataset

​

2. Shadow Dataset Existence Check

# Check if shadow datasets exist before using them
def check_shadow_datasets_exist(client: bigquery.Client, project_id: str) -> Dict[str, bool]:
    """Check which shadow datasets exist."""
    datasets = {
        "raw": f"{project_id}.payroll_raw_shadow",
        "processed": f"{project_id}.payroll_processed_shadow",
        "analytics": f"{project_id}.payroll_analytics_shadow"
    }
    # Use BigQuery client to check existence
    # Return dict of {dataset_kind: exists}

​

3. Preflight SQL Parameterization

-- Use @tenant_id parameter instead of hardcoding
DECLARE tenant STRING DEFAULT @tenant_id;
SELECT ... WHERE tenant_id = tenant

​

🎯 GO/NO-GO RECOMMENDATION

​

✅ QA CHECKLIST (Post-Implementation)

1. Security:
   • Identifier validation rejects backticks/spaces/dots/newlines
   • Invalid env vars cause RuntimeError at startup
   • No SQL injection vectors
2. Regression:
   • Baseline diff passes (golden vs post-refactor with defaults)
   • All endpoints return identical responses (within tolerances)
3. Shadow Cutover:
   • Setting env vars flips to shadow datasets (verify via logs)
   • Wiring map shows 100% widgets reading from shadow
   • Parity gates pass for July 2025
4. Code Quality:
   • No hardcoded dataset references remain in queries.py
   • All tests pass
   • Logging shows effective dataset config at startup