#2010 feat(nurture): batch cohort reads — account-grain identification that carries its evidence

Parent: the product-nurture epic. Depends on the flow catalog.

Problem

Every flow needs the same underlying facts about an operative: when did they sign up, when were they last active, how many days have they ever been active, which command families have they run, did anything fail. /admin/marketing answers versions of these as aggregate counts and names nobody; nurture needs them per account, with the account's email attached.

What ships

A NurtureCohortReads port plus its ClickHouse + Mongo implementation, wired through chInfra(...) in the composition root.

Account grain, resolved through identity_map. Devices are not people — the counting rule from the activation analysis is ~11.6×. Start from the Mongo account population (username + email + createdAt), resolve each to its device set, then measure activity over those devices. Never count raw distinct_ids.

Cheap substrates first. cli_daily is day-grain and distinct_id-keyed — that is the sparkline lesson from #1510 (199× fewer rows, and constant in platform volume). active_days_by_id folds event, CLI and grant-only membership at insert time and is PARTITION BY toYYYYMM(day), so a day floor prunes whole monthly partitions.

One read that legitimately cannot use them. cli_daily filters result_status = 'success', so needs_help_failure must read swamp.events with result_status = 'error' — a materialized column, never a properties parse. That is affordable here only because this is a batch job.

Exclusions, at the grain and not at the resolved owner. collective:% distinct_ids are dropped (#1679): a swamp serve on a collective key is a credential, not a person. Banned and enterprise-hidden operatives are dropped too.

Batch only. These reads take no request-path caller and must never acquire one. swamp.events per-identity reads are the #1317/#1586 trap; a nightly job may pay that cost, a page may not.

Verification

• tests/infrastructure/nurture_cohort_reads_test.ts — captured-SQL assertions: the collective gate present on every query, the day floor inside each branch (outside the union it lands past the table scans and prunes nothing), no JSON*(properties, …) anywhere.
• A static sweep asserting nothing under routes/ or islands/ imports this module — the same shape as heartbeat_retired_test.ts.
• Measured against the local stack: row counts and bytes read per flow, recorded on the issue. If any single flow's read is not comfortably a batch cost, say so before Phase A3 depends on it.

Out of scope

Writes, dedupe, sending. This issue reads and returns; it decides nothing.