Admin analytics cutover

Current state (September 12, 2026, 23:37 UTC): credential rotation and Neon logical replication are complete. Both computes recovered. The restricted managed ClickPipe has completed its initial snapshot and is Running; natural live rows, the selected schema, full-day usage and a bounded ledger sample passed verification. Six canonical views are applied. The production web/admin application has not been deployed from this branch, and pricing/capture/admin flags remain off. Worker CDC readiness remains false until genuine capture and the remaining report gates pass. See connection results and verification limits and credential cutover results. The sequence below records completed and remaining steps; do not repeat credential/restart setup.

Architecture

flowchart LR
  N[Neon: product transactions] -->|Allowlisted CDC| C[ClickHouse]
  S[Both Stripe accounts] --> W[Railway analytics worker]
  O[OpenRouter: actual account usage] --> W
  P[PostHog batch export] --> B[Private Railway S3 bucket]
  B --> W
  W <--> C
  W --> R[Redis: prepared reports]
  R --> A[Authenticated admin API]
  A --> U[Admin UI and update notifications]
  U -->|Moderation decisions only| N

The web service never asks vendors or ClickHouse to compute a report on a page request. Redis reads have a 250ms budget and a process-local last-good fallback. This is the report-storage budget, not a guarantee for end-to-end authentication/network latency. Cold reports return unavailable. The worker has bounded batches, resumable cursors, a renewable Redis lease, sanitized failure logs and durable snapshots in ClickHouse. HTTPS warehouse reads use sequential consistency so a completed cursor cannot be read ahead of its acknowledged facts across replicas.

Target refresh cadence: Overview every minute, vendor imports every five minutes, finance/tokens/retention every 15 minutes, atlas every hour. Actual freshness depends on source latency and backfill completion; each report carries source timestamps. The UI reuses cached data immediately, listens for revisions, and polls only while visible. Independent cards can remain usable during another card's failure.

Services and secrets

Create one private Railway analytics worker using railway.analytics.toml / Dockerfile.analytics. Start with one instance, 1GB RAM and the existing region; no public business API is needed. /health reports process progress and whether it has published. The lock prevents overlapping workers during deployments; process health alone does not certify source freshness.

Use packages/server/src/analytics/worker.env.example as a variable-name inventory. Set secrets in Railway, never in the repository. The worker needs the existing shared Redis, the scoped ClickHouse importer, separate restricted keys for corporate and legacy Stripe, and the verified OpenRouter management key. No production DATABASE_URL, Better Auth secret or user API-key encryption key belongs on this worker. Its optional source URL is for bounded initial test backfills only, not production polling.

Stripe keys must allow reading the current account identity (GET /v1/account, Accounts Read / connected_account_read) as well as balance transactions and charge/customer/checkout/invoice/product records used for purchase attribution. The worker pins each real account ID and rejects duplicates across both currently verified and previously pinned aliases. A failing account pauses only its own import/enrichment; healthy sources continue. Combined finance remains unavailable until both accounts and OpenRouter have completed the same cutoff. The owner has enabled the legacy key's Accounts Read permission; both account identities and historical ledger imports now pass.

The web service receives ADMIN_ANALYTICS_ENABLED and ANALYTICS_CAPTURE_ENABLED. Both remain false until their prerequisites pass. It needs Redis but no Stripe analytics, OpenRouter management or ClickHouse credentials. The game lifecycle receiver uses ANALYTICS_CAPTURE_ENABLED too.

DDL uses the separate ClickHouse migration credential. The runtime importer needs SELECT/INSERT on analytics tables and SELECT on the allowlisted raw tables/views. It must not receive DROP/ALTER/CREATE or access to unrelated source databases. The reader role needs SELECT on analytics data and the selected raw columns used by invoker-security views, with no write access. The ClickPipe has its own destination role, including READ ON S3 for import staging and SELECT(value, metric) on system.metrics for its setup check.

Preparation sequence

1. Run the branch's build, typecheck and analytics tests. Docker image build and phone/desktop browser verification remain explicit release checks; do not infer visual QA from typecheck.
2. Apply src/analytics/migrations/source-telemetry.sql using the source migration role, before deploying code. The new nullable usage columns are used by Drizzle inserts even when capture is off. Additive table/column creation is bounded by lock and statement timeouts. Retry a timed-out migration later; do not remove the timeout.
3. Deploy web capture with the new admin flag still false. Then enable ANALYTICS_CAPTURE_ENABLED=true only after the tables exist. Confirm provider-reported cost/request IDs and intervals arrive. No historical cost is invented.
4. Apply the ClickHouse schema with the migration identity: pnpm --filter @yumina/server analytics:schema. This creates tables, not a Neon connector. The same compiled command is node dist/analytics-worker.js --apply-schema. No DDL runs on normal worker startup.
5. During the later maintenance window, enable Neon logical replication, wait for compute recovery and validate application writes/reconnections. Use the direct Neon hostname for CDC, not the pooler. Create the restricted source role/publication. See the credential sequence below before changing any existing secret.
6. Create a managed PostgreSQL ClickPipe into yumina_raw, with the selected publication in source-publication.sql. Apply matching column exclusions to the initial snapshot as well as CDC. A publication column list alone is not proof the connector's snapshot omitted other fields. Verify the destination schema before copying production data. Configure JSONB tags as String for the prepared views, or adapt/test the mapping if the connector selects native JSON.
7. Start a conservative initial snapshot (one or two parallel table readers). Do not replicate messages, prompts, world schemas, names/emails from account rows, auth sessions, encrypted keys or impression logs. The published worlds.name is the world title; credit_transactions.description is retained to classify grants and may contain administrative text, so raw access stays restricted.
8. Check that all table snapshots completed, CDC has caught up, and row counts / daily tokens / ledger totals reconcile after applying FINAL and deletion filtering. A heartbeat proves liveness, not an atomic multi-table transaction or successful initial snapshot. Only then apply analytics:views and set ANALYTICS_CDC_READY=true.
9. Start the worker with ANALYTICS_ENABLED=true. Keep ADMIN_ANALYTICS_ENABLED=false while historical vendor imports and enrichment finish. Verify all five periods and all reports, including missing-data states. An initial import may span multiple cycles.
10. Reconcile Stripe and OpenRouter against provider dashboards for identical UTC cutoffs. Then enable the new admin flag on every web replica. Existing usage/engagement tabs redirect to Overview; old full-history analytics GETs return 410. The three old admin-only Neon rollups stop refreshing; recommendation engagement statistics and feed pruning keep running.

source-publication.sql is a one-time reviewed setup file, not an idempotent application migration. If that publication already exists, inspect it and reconcile its table/column list explicitly; do not drop a live publication automatically. The like tables use their existing unique indexes as replica identity. Keep source key columns immutable.

For a column-restricted source login, grant SELECT on ctid as well as the selected business columns on each source table. ClickPipes uses CTID range queries during its initial snapshot; ordinary selected-column LIMIT 0 checks alone will miss this requirement. Verify an actual CTID predicate without scanning rows. Do not add CTID to the publication or warehouse, and do not broaden the grant to all table columns.

PostHog

The hourly JSONLines/gzip export is configured in project 318450, restricted by event = 'signup_completed', using the private yumina-analytics-export Railway bucket. Files are capped at 32MiB. Railway uses virtual-host addressing: set ANALYTICS_POSTHOG_EXPORT_PATH_STYLE=false. These bucket-scoped credentials have write capability as well as read/list; they cannot access the product-assets bucket. PostHog custom-column/HogQL exports are not enabled for this project, so the supported standard events export is used. The warehouse importer retains only IDs, timestamps and UTM attribution; raw event/person properties remain in private export objects.

A separate paused daily export supplied the historical backfill without creating thousands of hourly files. PostHog adjusted the requested April 1 start to July 28, 2026; earlier attribution is unavailable from this export. Keep it unattributed. The normal hourly export stays active. The importer validates object identity with ETags, batches manifest lookups, processes at most 25 objects per cycle within a 120-second budget, and checkpoints without skipping unfinished pages. Replay is idempotent by event UUID.

The importer stores only event ID, account ID, timestamps and UTM source/medium/campaign, and replays object ETags idempotently. Client tracking is incomplete; missing identity/source stays unattributed. Account counts come from Neon. Legacy anonymous events are not automatically matched by email or guessed identity. For ad ROI, a signup cohort's identified receipts and observed AI costs are a contribution ceiling before ad and infrastructure costs, not a completed ROAS calculation.

The acquisition detail compares recorded activation within seven days by source, using only accounts old enough to have a complete window. Its counts deduplicate repeat actions. Age summaries use the existing account birth_year column from the allowlisted publication, with missing/invalid values kept Unknown; only bands and counts are exposed by the report API. Reported years are approximate and may reflect the onboarding default. No user gender is collected or inferred.

PostHog batch exports are the supported recurring export path; its query API is not a bulk export replacement.

Accounting and metric release gates

Metric	Contract / required reconciliation
Revenue	Corporate and legacy balance transactions keyed by account + ID. USD gross, refunds, disputes, cash fees and fee credits separated. Gross customer receipts are a cash metric, not accrued subscription revenue.
AI spend	OpenRouter account credits_usage after cache pricing, including applicable usage fees. Do not apply cache discounts twice. Funding/top-up fees excluded. Other providers require their own importer.
Per person / tier	Only attributed users and products; unmapped receipts remain visible. Per-product provider cost is allocated by a payer's receipt share. Refund-only and fee-only groups remain visible even without new sales. Missing historical request costs remain null.
Signup economics	Signup-month cohorts, observed net receipts and provider costs within 7/30/90 days; immature windows blank. Receipts without a reliable account match are excluded and disclosed.
Active people	Recorded authenticated play, saved Studio work, community posts/replies/reactions and explicit check-in/reward activity. Visits, automatic grants, bots and game guests do not qualify. Surfaces overlap.
Game time	Measured main-app foreground intervals since capture began; cross-session overlap unioned per person, split at UTC midnight. No historical duration reconstruction. Standalone game input is counted as activity; its input-active milliseconds are not relabelled as wall-clock playing time.
Tokens / BYOK	Input + output separately, platform/BYOK totals and users. Provider-reported vs estimated/legacy observations labelled. Studio BYOK/unlimited logging is fixed without changing billing.
Mushie flow	Historical per-user pro-rata funding allocation. It is not FIFO or a literal source-lot trace. Calls without a debit sit outside that flow; missing provider cost is not zero.
Retention	Signup-month active cohorts and first-token-month token cohorts, starting April. Exact daily/weekly return windows; monthly calendar windows. Incomplete cohort windows remain blank.
Worlds	Shared authenticated players; family-deduped 2+ world metric. Measured time, model tokens and D7 with denominator. Filterable top-500 catalog, up to 80 visible nodes and 3,000 shared-player edges; the UI states the limit. Tag filters are descriptive, not proof of genre-caused retention.

Match OpenRouter dashboard timezone, all Yumina keys, credits usage vs funding, and end-of-day cutoffs before comparing totals. Historical request-level cost gaps cannot be fixed from published model prices, because caching and routing change the actual charge. Track vendor total minus attributed request charges as an explicit reconciliation gap before considering any unit economics exact.

Credential rotation — completed procedure

September 12, 23:05 UTC credential cutover: shared ownership and timeout/default-grant setup are applied. Both web replicas, game runtime and GitHub SQL jobs use yumina_mycelium; the old password is rejected on primary/read pooled/direct endpoints, with zero old-role sessions remaining. Existing deployed code revisions were preserved. The owner subsequently enabled logical replication at 23:12 UTC; the admin/pricing release remains off. See verified cutover results. The sequence below records the rotation procedure; do not repeat completed steps.

1. Inventory every consumer: web replicas, game lifecycle/control services, jobs/workflows, migrations, direct/read Neon URLs, local tools and the new ClickPipe. Record role names and secret locations only.
2. Prepare separate application, migration and replication roles. Test the intended privileges on a non-production branch. Creating replacement credentials before revoking the old ones supports a staged switch.
3. During the agreed window, update the Railway primary and read URLs and every other consumer. Roll services so pools open with the replacement credentials. Verify login, message save, streamed replies, check-in, payment webhook writes, moderation and game lifecycle ingestion.
4. Confirm no consumer still uses the former role. Revoke its ability to connect, rotate/revoke its credential, and terminate its remaining sessions where appropriate. A password change alone does not terminate existing sessions; restarting Neon does not rotate the password.
5. Verify CDC resumes from the same slot and snapshots stay current. Rotate any exposed vendor keys separately after their replacement has been verified. Auth/session secrets and encryption keys are not database credentials and must not be casually changed as part of this operation.

Rollback and operations

Turn off ADMIN_ANALYTICS_ENABLED to return to the old admin and resume its rollups on deployment. Turn off capture independently if it fails. Keep additive schema columns; do not drop data or credentials as a rollback shortcut. A worker failure keeps the last-good reports visible with timestamps; Redis loss restores them from ClickHouse on worker restart.

Before broad rollout measure warm report p50/p95, navigation without duplicate fetches, worker cycle duration/memory, connector lag, source query load, and actual vendor reconciliation. Target sub-500ms warm report requests, under two-minute Overview lag and vendor figures within 15 minutes after initial backfill. These are acceptance targets, not measured production results.

Alert on stale source watermarks, repeated import failure, unknown-cost/request share, unmapped payments, failed usage-log writes and retained WAL growth. Account deletion cascades through the new source facts and CDC deletion flags; raw CDC versions require the connector's deletion cleanup / retention process. Purge linked PostHog enrichment and payment user mappings under the account-deletion retention policy before calling the analytics privacy rollout complete. Do not discard legally retained financial ledger totals.