Skip to main content

​
AGENT_PEPM OWNER_ROLLUP Repair (Inconsistent is_active) — Proof

Purpose: Validate that AGENT_PEPM save repairs inconsistent OWNER_ROLLUP rows where effective_end_date is set but is_active=TRUE, which blocked readiness. Deployed SHA: [fill after deploy]
Cloud Run Revision: [fill]
Date: [fill]

​
Problem (Prod State)

For business_id=e948cad9d7cf99eb:
Roworg_idis_activeeffective_start_dateeffective_end_date
Acbs-mainTRUE2025-12-012025-11-30
BNULLFALSE2025-12-012025-11-30
Row A: invalid — ended (effective_end_date IS NOT NULL) but still active (is_active=TRUE).
Existing close logic only targeted effective_end_date IS NULL, so Row A was never touched. Readiness (ORG_SCOPED) could still see it as active and block.

​
Repair UPDATE (Step 1b)

New UPDATE runs immediately after the “close open OWNER_ROLLUP” step in the atomic script: 
UPDATE analytics.config_business_policy
SET
  is_active = FALSE,
  updated_at = CURRENT_TIMESTAMP(),
  updated_by = @created_by
WHERE tenant_id = @tenant_id
  AND business_id = @business_id
  AND policy_type = 'OWNER_ROLLUP'
  AND is_active = TRUE
  AND effective_end_date IS NOT NULL
  AND effective_end_date < @effective_start_date
  • No org_id filter — repairs both SHARED and ORG_SCOPED rows.
  • Safe predicate — effective_end_date < @effective_start_date avoids deactivating rows still effective for the new period.

​
Before / After BQ Query

Run before and after AGENT_PEPM save: 
SELECT policy_type, org_id, is_active, effective_start_date, effective_end_date, updated_at
FROM payroll_analytics.config_business_policy
WHERE tenant_id = 'creative_benefit_strategies'
  AND business_id = 'e948cad9d7cf99eb'
  AND policy_type = 'OWNER_ROLLUP'
ORDER BY org_id NULLS LAST, created_at DESC;
Before: Row with org_id='cbs-main', is_active=TRUE, effective_end_date=2025-11-30.
After: Same row has is_active=FALSE, updated_at bumped.

​
Readiness Before / After

Before: Readiness blocked with "OWNER_ROLLUP policy requires confirmation" for the affected business. After: Readiness should no longer block for that business (OWNER_ROLLUP no longer considered active).

​
Validation Checklist

  1. Save AGENT_PEPM for e948cad9d7cf99eb with period 2025-12-01 (or current period).
  2. Re-run BQ query — confirm is_active=FALSE on previously inconsistent row.
  3. Re-run readiness — confirm no OWNER_ROLLUP policy requires confirmation for that business.