Guest game analytics

PvZ's assigned regional relay reports cumulative gameplay counters through the game-events Cloudflare Worker and its queue. Opening the asset page or joining a lobby does not qualify as active. No analytics database query runs in the relay's input loop.

Data path

1. Signed lifecycle events land in game_lifecycle_events; verified login links land in game_guest_links.
2. The existing Neon publication and ClickPipe replicate both tables to yumina_raw. Initial copy was reconciled on September 13, 2026. A destination table existing is not evidence its snapshot is complete.
3. The analytics worker archives production events into game_events and identity links into game_identity_links. Operational event retention must not delete analytical history. The import excludes QA, archives late arrivals, and deduplicates retries by event identity.
4. V2 hosts report player-action counts and elapsed match time after the first action. A new action or an increase in qualified time counts as play. Empty lockstep batches cannot start qualification. A rematch requires another action; replay cannot add actions or time. Linked guests use their account identity; unlinked guest identifiers remain separate browser identities. These are not guaranteed unique humans across devices.
5. ClickHouse prepares reports; Redis serves them. Admin interactions never scan Neon. Visited periods stay in the client cache for 30 minutes, with background revalidation. Overview targets a minute, worlds five minutes; timestamps expose actual freshness when work falls behind.

Time

Active people require a recorded play request, Studio work/assistant/playtest action, community post/reply/reaction, explicit check-in claim, or host-observed gameplay. Free and BYOK play qualify without spending Mushies. Opening a page, joining a lobby, running a foreground timer, and automatic credit grants do not qualify. The same action definition drives Overview, acquisition activation, active retention and financial active-person denominators. The Users page also keeps foreground time separate from active status and last meaningful action.

Timestamped main-app foreground intervals began September 13, 2026 at 00:35:50.952 UTC. Earlier lifetime account counters are retained, but cannot be redistributed into invented daily values. Period quick stats show a dash for incomplete coverage; the recorded portion remains in the tooltip and detail view.

Host counter increases are measured duration, positioned at the end of their reporting window. Their exact position within that window is estimated. Overlapping intervals count once per identity, including linked main-app and guest sessions. Lifetime totals add only game time proven disjoint from existing account counters: unlinked guest history and incremental union time after timestamped capture. Pre-capture signed-in game duration is not added a second time.

World time uses retained main-app session totals for All time, and recorded intervals for dated periods. Deleted sessions cannot be assigned back to a world. Time-per-timed-player pills use players with positive measured time; the 10-minute group qualifies across the selected period.

V1 game events contain only network time. They remain in recorded game hours, but cannot establish active players or retention. V2's activeMs preserves that time counter, actions counts fresh nonempty intent batches' actions, and engagedMs measures ongoing rounds after the first action in each match. The relay validates action syntax and membership, not the full client simulation: this is player-intent evidence, not an authoritative verdict that a plant placement succeeded. No individual game inputs are stored in analytics.

For the published Tom and Jerry / Liar's Bar cards, the audited local engines distinguish human and AI turns. Suitable minimums are a successful player shot/item use and an accepted player card submission/challenge, respectively. These hooks are not yet instrumented. A future sandbox event must be bound to the authenticated play session and world, emitted from the accepted human-action path, deduplicated and rate-limited. Loading, setting a preference, background AI turns, autosaves and elapsed browser time cannot trigger it. Existing account status does not relax qualification. Do not reconstruct past action counts from session lifetime counters.

Retention

Active cohorts enter on account signup or an unlinked guest's first measured play. Meaningful play, Studio, community and reward activity count as returns. Guest links remove the separate guest cohort identity.

Activated (3+) and Engaged (10+) play cohorts retain the legacy definition: qualify during the initial 1-, 7-, or 30-day entry window, then return with any play message. D7 is day seven; W1 is days 7–13; play M1 is days 30–59. Entry qualification and mature denominators differ by interval, so these three percentages are not one survival curve.

Setup and limits

Apply 005_game_telemetry.sql explicitly with the schema-admin credentials (worker.ts --apply-game-schema). Grant the CDC source role SELECT, including the snapshot cursor's CTID access, on the two source tables. Add mappings while the connector is paused, then verify the initial row counts and live heartbeat before deploying the importer. source-publication.sql documents the full fresh-install publication; do not rerun it against an existing publication.

For action qualification, apply additive SQL columns in src/db/game-lifecycle.sql and warehouse migration 006_game_actions.sql (--apply-game-actions), then deploy the API and edge consumer accepting V1/V2 before V2 regional hosts. The analytics worker understands both versions. Drain each host before restarting an in-memory relay; do not terminate occupied matches to change analytics. No Neon restart is required.

The PvZ relay match mode is connected. Historical solo Adventure page-duration events are not verified gameplay time. Krew must emit this signed host lifecycle contract before it contributes: stable guest identity, game/session IDs, event sequence and cumulative active milliseconds, with verified account linking. Its page traffic alone must not become active players or game hours. Readiness requires QA-environment lifecycle smoke tests and production delivery verification for each new game.

Verification covers reconnect/retry deduplication, overlapping sessions, guest-to-account linking, lobby exclusion, UTC clipping, source freshness and period denominators. Source replication and report freshness are separate checks.