Tracer C: cohort funnel queries + /funnel cohort section behind the existing gate

[usirin]

Stories: 1, 2
TDD: yes
Containment: flag (default-off — the display gates separately from the always-on capture per founder ruling R1.2 on #7028)

What to build

The read surface of epic #6767: the five-stage per-signup-cohort week-1 survival funnel and D1/D7 return rates shown as a visibly separated section on /funnel.

• Query builders in apps/web/worker/features/funnel/queries.ts beside the existing pool queries (each predicate .toSQL()-inspectable): stages over rows that already exist — user.created_at, first contribution via MIN(created_at) across definition_record/post_record/comment_record, authorship_vouch.created_at, user.promoted_at — plus D1/D7 return rates off user_activity_day and tracer B's rollup table.
• Known data holes surfaced as unmeasurable counts, never reconstructed: pre-Stamp promoted_at at yazar promotion (both mod-direct and vouch-tandem paths) #1590 founding cohort (promoted_at null), withdrawn vouches (ledger row deleted).
• A second fate data view beside FunnelSummaryView (views.ts), folded through a service extension of Funnel.
• The /funnel section renders below the existing pool snapshot, separated by its own heading and framing copy that names the numbers directional at current volume; all reads already behind requireFunnelAccess (funnel/gate.ts) — no new gate seam for moderation.
• The cohort view resolution sits behind a default-off flag so the display can be enabled independently of capture.

Monitoring acceptance (founder ruling R1.2 on #7028, folded here as this tracer's criteria — this child closes the epic's loop end-to-end):

1. Errors surface via the existing worker error pipeline (Sentry) automatically — verify the capture and cron paths emit there.
2. Silence detection verified end-to-end: with sessions present and zero new user_activity_day rows over a window, the weekly cron raises the flag.
3. Day-one sanity: after deploy, run a manual query confirming today's user_activity_day rows exist, and post the result to this issue.

Acceptance criteria

• all five stage builders plus D1/D7 return builders are .toSQL()-asserted with no engine
• unmeasurable populations appear as explicit counts, never dropped or back-computed
• the cohort section renders separated from the pool snapshot with directional framing, behind requireFunnelAccess
• the display flag default-off leaves the existing /funnel readout byte-identical when disabled
• the three monitoring points above are demonstrated and recorded on this issue

[usirin]

build-claim: build:01a02d11-1872-7f65-9cba-cb9492da975e:8774bb34-b02f-4d0c-92c6-b03e5acdef64 · 2026-08-23T08:35:52.327Z

[usirin]

build-deviations: #7031

Deviations

• Said: build the cohort query builders in funnel/queries.ts beside the existing pool queries. Did: put the new SQL builders (the rollup-table read, the founding-promotion count and the vouch-evidence count) in features/funnel/Funnel.ts, where the pool builders actually live; queries.ts took only the three gated resolvers, and Funnel.cohorts reuses tracer B's exported input builders plus its pure fold instead of re-declaring near-identical queries. Why: repo-wide, queries.ts is the fate-resolver file and every funnel SQL builder lives beside the service, each .toSQL()-asserted with no engine (ADR 0082 T1/T2); two implementations of one fold would drift. Disposition: stated here.
• Said: the cohort view resolution sits behind a default-off flag. Did: gate the resolver resolution server-side behind phoenix-funnel-cohort AND render the whole section behind a client <FlagGate> on the same key. Why: acceptance requires the disabled /funnel readout byte-identical — the client gate keeps even the cohort requests out of the DOM when off, while the server gate keeps the reads unreachable no matter what a client sends. Same key, same safe default on both sides. Disposition: stated here.
• Said: after deploy, run a manual query confirming today's user_activity_day rows exist and post the result to Tracer C: cohort funnel queries + /funnel cohort section behind the existing gate #7031. Did: demonstrated monitoring points 1–2 in-tree (capture and cron paths log into the worker error pipeline via Effect.logError, which the Sentry Effect Logger captures — asserted by the swallow-and-log tests on both paths; silence detection proven over sessions-present/zero-rows fixture windows). Point 3 needs a production deploy and a live D1 query, which a worktree build lane cannot perform. Why: forward-only data makes the sanity check meaningful only against the deployed capture. Disposition: operator follow-up on Tracer C: cohort funnel queries + /funnel cohort section behind the existing gate #7031 at deploy time.

[usirin]

review-code: PASS range:f9eb36570a9f70abcd947b0030406c4a848b7d48..2fd91d8 content:cf61d669cd94 — all five criteria hold over the range at the composed tree; disclosures verified truthful; day-one deploy sanity stays operator-side

Range-scoped review-code verdict for epic #6767 child #7031, judged over f9eb365..2fd91d8 (15 changed files, code class only — no doc/skill slice; governance derived not-required over this range, self false, harness false). All funnel unit tests were executed at the tip in this reviewer's own worktree detached at 2fd91d8: vitest run --project unit worker/features/funnel — 4 files, 72 passed; pnpm typecheck (fate generate + tsc worker/node/app) exited 0 at the same commit. No PR exists at a child state, so there is no CI head to read; the execution facts above are from the tip bytes themselves, not a cached run.

Per-criterion

• [PASS] AC1 — five stage builders + D1/D7 return builders .toSQL()-asserted with no engine. This range's three NEW builders each get rendered-SQL assertions over a no-op D1: cohortRollupRowsQuery (full table, order by ... cohort_week asc), foundingPromotionsQuery (human yazar, promoted_at is null, params human/yazar), vouchEvidenceMissingQuery (not in over the authorship_vouch.candidate_id set) — Funnel.unit.test.ts:455-511. The five-stage input builders and the pure D1/D7 fold (foldCohortRollup) carry their own .toSQL() assertions in cohort-rollup.unit.test.ts:305-347 (Tracer B: weekly cohort rollup cron with silence detection #7030's range, present at tip); Funnel.cohorts composes them instead of re-declaring near-identical SQL — the reuse is deviation entry 1, verified truthful. Seam-level: Funnel.cohorts scripted-sequence tests assert the full week shape incl. rates, and the untouched-population zero case.
• [PASS] AC2 — unmeasurable populations as explicit counts, never dropped or back-computed. Both holes are first-class fields end to end: CohortUnmeasurable (Funnel.ts:236-245) → resolver report (queries.ts cohortsGated) → FunnelCohortsView fields → client data-testid="funnel-cohort-founding-hole" / funnel-cohort-vouch-hole entries with explanatory copy ("bu hesaplar sayılmaz, geriye dönük kurulamaz"). Nothing reconstructs them. Presentation note below on zero-suppression.
• [PASS] AC3 — separated section, directional framing, behind requireFunnelAccess. Own <section aria-label="kozet hunisi"> with heading + framing note naming the numbers directional ("yön gösterir, istatistiksel anlam taşımaz"), rendered below the pool snapshot. Every one of the three resolvers (funnel.cohorts, funnel.cohortWeeks, funnel.cohortRollups) wraps its gated effect in requireFunnelAccess; the non-mod denial test asserts the invisible UNAUTHORIZED wire code BEFORE any cohort read (stub dies on contact).
• [PASS] AC4 — flag default-off leaves /funnel byte-identical. FUNNEL_COHORT_FLAG.defaultVariation: "off" + DECLARED_FLAGS row default false; server side short-circuits to the disabled report / empty connections without touching Funnel (die-on-contact stub proves no read runs — tested for all three resolvers); client side <FlagGate PHOENIX_FUNNEL_COHORT> inherits useFlag's safe-default false through loading/error/undeclared, so the section mounts nothing. summaryGated and the pool snapshot markup are untouched. Double-gate (server + client, same key) is deviation entry 2, verified truthful.
• [PASS] AC5 — monitoring points, graded as claimed. Points 1–2 demonstrated in-tree at tip: capture/cron paths log into the worker error pipeline with swallow-and-log tests (Tracer A: user_activity_day capture — live unflagged write in Pasaport.validateSession #7029/Tracer B: weekly cohort rollup cron with silence detection #7030 ranges, executed green in this run's 72); silence detection proven over sessions-present/zero-rows windows (detectCaptureSilence describe). Point 3 (post-deploy live D1 sanity posted here) is structurally impossible from a worktree lane and is disclosed in the build-deviations marker as operator follow-up at deploy time — recorded here as the one open item this PASS does not discharge; it stays on the issue for the operator.

Standing checks

• Test honesty: all test hunks additive; no pre-existing assertion weakened or rewritten; Funnel.testing.ts strengthens the fail-on-contact stub with the two new members.
• Release containment: stated (default-off dark ship, founder R1.2 on Week-1 survival cohort funnel on /funnel #7028) and matched by the diff.
• Staleness traps: none introduced — the live fold recomputes per request; the rollup table has a single writer (the cron), so the read model never races it.
• Type design: views resolved through fate/views.ts with _FateViewsFieldMapResolved assertions extended; FunnelCohortWeekRow exported with the documented TS4023 cross-boundary reason; synthetic source matches the established capability-less pattern (cf. pasaport's FailingAddressSource).
• NOT IN nullability: safe — authorship_vouch.candidate_id is notNull() (schema.ts:442), so the subquery can never carry a NULL that would collapse the predicate.

Deviations disclosure vs substance (#7031, marker comment 5385281850)

Entry	Substance in diff	Verdict
SQL builders in Funnel.ts, queries.ts takes gated resolvers; cohorts() reuses tracer B's loaders + fold	matches: three builders beside the pool builders; makeCohortRollupPorts(...).loadInputs + foldCohortRollup compose	disclosed, disposition stated
Server AND client gating on phoenix-funnel-cohort	matches: cohortDisplayOn() in every resolver + <FlagGate> around the section	disclosed, disposition stated
Monitoring 1–2 in-tree, point 3 operator follow-up	matches: tests green at tip; no deploy/live-D1 act performed	disclosed, disposition stated

Nothing undisclosed that this gate could see.

Non-blocking observations (not traceable to an acceptance criterion)

• With the display ON, one page view runs the full live fold twice — funnel.cohorts and funnel.cohortWeeks each call Funnel.cohorts() (loadInputs + fold + both hole counts). Harmless at invite-only volume; share one resolution or prefer the rollup series if volume grows.
• No client-render test for FunnelCohorts.tsx (matches the FunnelSummary.tsx precedent; the component is typechecked, not rendered-tested).
• Hole metrics render only when > 0 — an explicit zero disappears from the section rather than printing "0".
• Nested duplicate aria-label="kozet hunisi" on the page's wrapper section and the component's own section.