​

Save vs Readiness Table Trace

​

Summary

​

Write Path

​

Endpoint

• Both admin drawer and wizard drawer use: /api/v1/admin/onboarding/businesses/{id}/save
• Client method: businessOnboardingClient.saveBusinessConfig()
• Backend handler: save_business_config_endpoint() in api/routes/business_onboarding.py
• BigQuery function: save_business_config() in api/bigquery/business_onboarding_queries.py

​

Tables Written

1. config_business_agent_assignment
   • Written when mode = 'AGENT_PEPM'
   • Fields: effective_start_date, effective_end_date (NULL for new rows)
   • Org context: org_id (NULL for SHARED, org_id for ORG_SCOPED)
2. config_business_agent_pepm_assignment
   • Written when mode = 'AGENT_PEPM'
   • Fields: effective_start_date, effective_end_date (NULL for new rows)
   • Org context: org_id (NULL for SHARED, org_id for ORG_SCOPED)
3. config_business_policy
   • Written when mode = 'OWNER_ROLLUP'
   • Fields: effective_start_date, effective_end_date (NULL for new rows)
   • Org context: org_id (NULL for SHARED, org_id for ORG_SCOPED)

​

Effective Start Date Logic

# P1: Default effective_start_date to period_label if missing (deterministic safeguard)
effective_start_date_str = request.effective_start_date
if not effective_start_date_str:
    if request.period_label:
        effective_start_date_str = request.period_label
        logger.info(f"[SAVE_CONFIG] effective_start_date missing, defaulting to period_label: {effective_start_date_str}")
    else:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Either effective_start_date or period_label must be provided"
        )

# Parse effective_start_date
try:
    effective_start_date = datetime.strptime(effective_start_date_str, "%Y-%m-%d").date()
except ValueError:
    raise HTTPException(
        status_code=status.HTTP_400_BAD_REQUEST,
        detail=f"effective_start_date must be YYYY-MM-DD format, got: {effective_start_date_str}"
    )

• If request.effective_start_date provided → use it
• Else if request.period_label provided → use it (defaults to period_label)
• Format: YYYY-MM-DD (parsed to DATE)

• Written: effective_start_date = DATE('2026-02-01')

​

Read Path (Readiness Query)

​

Query Function

• Function: get_business_readiness() in api/bigquery/business_onboarding_queries.py
• Called by: /api/v1/intake/ingestion-wizard/readiness endpoint

​

Tables Read

1. config_business_onboarding
   • Source for business list
   • Org filter: (scope = 'SHARED' OR (scope = 'ORG_SCOPED' AND owning_org_id = @org_id))
2. config_business_agent_assignment
   • Filter: effective_start_date <= @as_of_date AND (effective_end_date IS NULL OR effective_end_date >= @as_of_date)
   • Org filter: (org_id = @org_id OR org_id IS NULL)
3. config_business_agent_pepm_assignment
   • Filter: effective_start_date <= @as_of_date AND (effective_end_date IS NULL OR effective_end_date >= @as_of_date)
   • Org filter: (org_id = @org_id OR org_id IS NULL)
4. config_business_policy
   • Filter: effective_start_date <= @as_of_date AND (effective_end_date IS NULL OR effective_end_date >= @as_of_date)
   • Org filter: (org_id = @org_id OR org_id IS NULL)

​

Effective Date Filter Logic

# Period filter (for as_of_date in PEPM/policy checks) - REQUIRED, deterministic
try:
    from api.utils.date_helpers import normalize_period_for_bigquery
except ImportError:
    from utils.date_helpers import normalize_period_for_bigquery
normalized_period = normalize_period_for_bigquery(period_label)
params.append(bigquery.ScalarQueryParameter("as_of_date", "DATE", normalized_period))

AND pol.effective_start_date <= @as_of_date
AND (pol.effective_end_date IS NULL OR pol.effective_end_date >= @as_of_date)

• Input: period_label (YYYY-MM-01 format)
• Normalized: normalize_period_for_bigquery(period_label) → DATE
• Format: YYYY-MM-01 → DATE (first day of month)

• Read filter: effective_start_date <= DATE('2026-02-01') AND (effective_end_date IS NULL OR effective_end_date >= DATE('2026-02-01'))

​

Comparison

​

✅ Tables Match

• Write: config_business_agent_assignment, config_business_agent_pepm_assignment, config_business_policy
• Read: Same tables

​

✅ Effective Dating Logic Matches

• Write: effective_start_date <= @as_of_date AND (effective_end_date IS NULL OR effective_end_date >= @as_of_date)
• Read: Same filter

​

⚠️ Potential Mismatch: Date Normalization

• Accepts effective_start_date as YYYY-MM-DD (any day of month)
• Defaults to period_label (YYYY-MM-01) if missing
• Written as DATE (preserves day)

• Uses period_label (YYYY-MM-01) normalized to DATE
• Filter: effective_start_date <= DATE(period_label)

1. Write: effective_start_date = "2026-02-15" (mid-month)
   • Written: effective_start_date = DATE('2026-02-15')
2. Read: period_label = "2026-02-01"
   • Filter: effective_start_date <= DATE('2026-02-01')
   • Result: Row NOT matched (2026-02-15 > 2026-02-01)

1. Write: effective_start_date missing, period_label = "2026-02-01"
   • Written: effective_start_date = DATE('2026-02-01')
2. Read: period_label = "2026-02-01"
   • Filter: effective_start_date <= DATE('2026-02-01')
   • Result: Row matched ✅

​

Frontend Effective Date Derivation

const deriveDefaultEffectiveStart = (): string => {
  // CRITICAL: Never return empty string or 2000-01-01
  // Always return a valid period start date (YYYY-MM-01)
  
  // If periodLabel is 2000-01-01 or invalid, derive current month start
  if (periodLabel === '2000-01-01' || periodLabel.startsWith('2000-')) {
    const now = new Date();
    const year = now.getFullYear();
    const month = String(now.getMonth() + 1).padStart(2, '0');
    return `${year}-${month}-01`;
  }
  
  // Default: use periodLabel (already YYYY-MM-01 format)
  return periodLabel;
};

// Derive effective_start_date from periodLabel (always YYYY-MM-01)
const effectiveStartDate = deriveDefaultEffectiveStart();

​

Conclusion

• Write: effective_start_date defaults to periodLabel (YYYY-MM-01) via deriveDefaultEffectiveStart()
• Read: Uses periodLabel (YYYY-MM-01) normalized to DATE as @as_of_date
• Result: Perfect match ✅

• Write: config_business_agent_assignment, config_business_agent_pepm_assignment, config_business_policy
• Read: Same tables

• Write: effective_start_date = DATE(periodLabel) (YYYY-MM-01)
• Read: effective_start_date <= DATE(periodLabel) AND (effective_end_date IS NULL OR effective_end_date >= DATE(periodLabel))
• Result: Row written with effective_start_date = DATE('2026-02-01') will match read filter effective_start_date <= DATE('2026-02-01') ✅