What was wrong

Org-level cron automations (a cron trigger in an org PLAYBOOK.md , e.g. Pirros' weekly "barriers to buy" report) fire once per tick under a single "representative" linked user. getPlaybookCronConfigs picked that representative with SELECT … WHERE org_aop_id = … LIMIT 1 , no ordering, no connection-health check.

If the arbitrarily-picked representative has an expired/revoked Google connection, building the run's automation context (runPlaybookSectionExecution → buildAutomationContext → generatePreamble → getUserContext) throws "No valid connection tokens found for user …", and the org's entire scheduled report dies before any execution row is created. The failure is near-invisible (it escapes the per-task catch; nothing user-facing is logged).

Evidence (prod, Mon 2026-08-17 07:00 PT tick)

• Pirros' weekly report agent (0a6c401c) has never produced an execution row.
• CloudWatch worker log at 14:08:33 UTC: [getUserContext] Could not get active connection for user ZOrHUjz… (<email>): No valid connection tokens , stack: getUserContext → generatePreamble → buildAutomationContext → runPlaybookSectionExecution → processAopAutomations.
• account row for Peter: Google access token expired 2026-07-30, never refreshed since Jul 22 (he owns the account but doesn't actively use Cedar).
• 2 of Pirros' 7 reps have dead connections (Peter 7/30, Ross 8/7); the representative is picked arbitrarily.
• Same failure hit two other orgs at the same tick (representatives RspInxFG8…, tx5YJRft…) , this is systemic, not Pirros-specific.

What changed

apps/server/src/services/playbook/manifest-utils.ts:

• New pickOrgCronRepresentative(db, orgAopId, now) , joins linked AOPs to their Google account, collapses to one candidate per user, and picks the healthiest representative.
• New pure, unit-tested rankOrgCronCandidates() , orders by: (1) currently-valid access token, (2) freshest token expiry, (3) most-recently-refreshed account, (4) stable aopId tiebreak (so the choice doesn't churn across ticks).
• The org branch of getPlaybookCronConfigs now uses it and logs the chosen representative ([getPlaybookCronConfigs] org cron representative selected) so the selection is auditable.

Against Pirros' real data the ranking yields zane > kas > keenan > brett > xander > ross > peter , the two dead reps sort last, and a healthy rep is always chosen when one exists.

How to test

• pnpm --filter @zero/server exec vitest run src/services/playbook/__tests__/org-cron-representative.test.ts (5 cases, incl. the real Pirros expired-vs-valid scenario).
• After deploy: on the next org-cron tick, look for [getPlaybookCronConfigs] org cron representative selected naming a rep with a live token, and an execution row for the org report agent.

Residual / follow-up

This removes the common failure (a known-dead rep is never chosen when a healthy one exists). It does not add a runtime fallback if the chosen rep's token is revoked between selection and execution , a belt-and-suspenders retry across candidates could be added later if needed.

🤖 Generated with Claude Code

Greptile Summary

The PR replaces arbitrary org-cron representative selection with deterministic ranking based on Google account metadata and adds focused ranking tests and selection logging. However, the ranking reads a different persistence model from the runtime connection-validity check, so disconnected users can still be selected.

• Adds a pure candidate-ranking helper and database-backed representative selector.
• Prefers future token expirations, then expiry and account recency, with a stable AOP tie-break.
• Adds tests covering valid, expired, absent, tied, and immutable candidate inputs.
• Logs the selected representative for org-level cron runs.

Confidence Score: 4/5

The representative selection sh