Skip to main content

​
Red-Flag Scan: OWNER_ROLLUP ↔ AGENT_PEPM Exclusivity Fix

Insertion Point: api/bigquery/business_onboarding_queries.py lines 4859–4874 (close_policy_query inside AGENT_PEPM branch of save_business_config())

​
1. save_business_config() — OWNER_ROLLUP Branch

File: api/bigquery/business_onboarding_queries.py
Lines: 4666–4846
  • Close assignments: 4676–4687 (close_assignments_query)
  • Close PEPM: 4689–4700 (close_pepm_query)
  • Insert OWNER_ROLLUP policy: 4719–4725
  • Atomic script: 4736–4745 (assignments → pepm → policy)
  • No policy close (creates new policy; previous assignments/PEPM already closed)

​
2. save_business_config() — AGENT_PEPM Branch

File: api/bigquery/business_onboarding_queries.py
Lines: 4848–5155 Before fix:
  • close_policy_query (4859–4870): Updated effective_end_date, updated_at, updated_by
  • Missing:
    • is_active = FALSE (required so readiness no longer treats policy as active)
    • AND effective_start_date <= @effective_start_date (SCD2-safe: do not close future policies)
    • AND is_active = TRUE (only close active policies)
After fix:
  • SET: effective_end_date, is_active = FALSE, updated_at, updated_by
  • WHERE: tenant_id, {org_filter}, business_id, policy_type = 'OWNER_ROLLUP', is_active = TRUE, effective_end_date IS NULL, effective_start_date <= @effective_start_date

​
3. Readiness Policy Detection

File: api/bigquery/business_onboarding_queries.py
Function: get_business_readiness()
Lines: 310–327 (owner_rollup_policies CTE) Readiness considers config_business_policy rows where:
  • policy_type = 'OWNER_ROLLUP'
  • is_active = TRUE
  • COALESCE(pol.effective_start_date, @as_of_date) <= @as_of_date
  • (pol.effective_end_date IS NULL OR pol.effective_end_date >= @as_of_date)
After AGENT_PEPM save closes the policy (effective_end_date set, is_active = FALSE), the policy no longer appears in owner_rollup_policies and readiness uses assignment/PEPM logic instead.

​
4. Existing Close Helpers

HelperFile:LinePurpose
_build_close_paramsbusiness_onboarding_queries.py:4427–4434Returns tenant_id, business_id, close_end_date, created_by
_build_save_params_agent_pepmbusiness_onboarding_queries.py:4447–4458Combines close params + effective_start_date + array param + org params
close_end_dateAGENT_PEPM branch:4857effective_start_date - timedelta(days=1)
effective_start_date is already included in all_params via _build_save_params_agent_pepm, so the policy close query has access to @effective_start_date.

​
Insertion Point Summary

Exact location: api/bigquery/business_onboarding_queries.py lines 4859–4874, inside the elif mode == "AGENT_PEPM": block, the close_policy_query string. Change: Add is_active = FALSE to SET; add AND is_active = TRUE and AND effective_start_date <= @effective_start_date to WHERE.