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, qualified UGC engagement, 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, a timer without input, and automatic credit grants do not qualify. The same definition drives Overview, acquisition activation, active retention, Users and financial active-person denominators.

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.

UGC uses the shared sandbox instead of individual card instrumentation. A trusted pointer/key/scroll event inside a session frame supplies a recent-input flag (no key, text, target or coordinates). The existing authenticated playtime lease awards server time; Redis accumulates 60 seconds while input remains recent (120s). Hidden, disconnected, idle and replayed ticks cannot build engagement. Once qualified, at most one foreground-engaged-60s event per session/minute feeds the same activity pipeline. Redis outages fail closed without delaying game responses. This is browser engagement, not proof of a legal game move. It works for Tom and Jerry and Liar's Bar without editing their published cards. Existing accounts follow the same rule; legacy activity is not backfilled from lifetime time 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+) qualify in their first day, UTC calendar week, or first 30 days; any later play message is a return. Weeks run Monday–Sunday UTC. W1, including world-map rings, is any return in the following week, even Sunday-to-Monday. D1 is the next day; play M1 remains days 30–59. Entry qualification and mature denominators differ by interval. Headers show D1/W1/M1 and pool completed cohorts by population; incomplete weeks are blank.

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.