Admin analytics upgrade

Implementation checkout: codex/admin-analytics-upgrade.

Accepted scope

Use the V10 admin design as the visual reference for the whole admin, including moderation and review. V11 preserves that design and promotes Users to a primary destination immediately after Overview. Keep independent analytics processing, actual provider accounting, meaningful-action retention and a shared-player world atlas. Retain ClickHouse Scale; reasonable production infrastructure spending is accepted. The original mockups and research remain in the original checkout's ignored packages/server/scripts/money-dive/data.local directory.

Visual follow-up after the production release: yumina-admin-concept-v12.html adds the Users counts, filters and sorting preview while preserving v11's navigation Overview → Users → Moderation → Operations. Its Users screen uses fictional sample accounts. The React implementation preserves existing user search, effective-plan/status filters, balances, account details and authorized actions; it adds prepared global rankings. See the visual-port record for implementation and validation, and the production release record for the earlier deployment. The preparation and rollout notes below are historical, not a current request to rotate or restart anything.

The user explicitly deferred Neon restart/replication enablement, database credential rotation and the associated existing-service secret changes. Do not initiate or schedule them. Subsequent authorization covers backend setup and browser checks: the separate worker and export storage are now deployed, vendor imports have started, and additive telemetry DDL has been applied. The production web/admin has not been switched. See current readiness.

Delivery checklist

• [x] Versioned metric contracts, bounded warehouse queries and coverage semantics.
• [x] Separate analytics worker, durable import cursors and prepared snapshots.
• [x] Stripe account-separated imports and OpenRouter actual-cost history; both Stripe identities are verified and both historical ledgers are imported.
• [x] New provider-cost and meaningful-activity facts; measured main-app time intervals. Standalone game duration remains a follow-up.
• [x] Shared client query cache, cancellation and nonblocking report endpoints.
• [x] V9/V10-based overview, finance, tokens, retention and filtered world atlas; desktop/mobile browser checks completed.
• [x] Consolidated moderation review, selected-edition preview and atomic decisions with revision checks.
• [x] Failure/replay/accounting tests, build and typecheck.
• [x] Prepared deployment and combined credential-rotation/replication runbook.
• [x] Independent Railway Docker deployment, private Redis benchmark, ClickHouse schema and PostHog export/backfill setup.
• [ ] Later: source replication, full live report reconciliation and production UI enablement.

Rules

Report requests may read prepared data only; they must not initiate vendor calls or full-history Neon scans. Unknown is not zero. Preserve separate source timestamps and distinguish exact, estimated, allocated and unavailable metrics. All-time totals include available pre-April history; charts/cohorts start April 1, 2026. Internal users remain a visible segment. Provider usage already includes cache pricing; never apply a second discount. OpenRouter funding fees are separate from consumed usage. Stripe fee credits are not cash fees or customer revenue.

Active people perform a confirmed action in play, Studio, community or explicit reward claiming. Visits and automatic grants do not qualify. Game time retains the existing foreground heartbeat protocol: a 45-second session lease and a 90-second maximum accepted heartbeat gap, with overlapping intervals unioned per person. A five-minute input-idle policy has not been implemented. Standalone game input activity qualifies as activity, but is not converted into wall-clock playing time. Historical gaps cannot be retroactively measured. Revenue per signup uses matched signup cohorts, not daily cash divided by unrelated daily signups.

Implementation status

Application changes remain isolated in the implementation checkout. Backend preparation now includes the independent Railway worker, its scoped runtime settings, the private export bucket, ClickHouse analytical tables, and additive Neon telemetry columns/tables. Existing application credentials and the production admin UI remain unchanged. See current readiness for exact states.

Validated locally: root build and typecheck; analytics cash conservation, refunds, cache failure, authorization, lease ownership, source migration and moderation transaction tests; existing billing/game regression tests; client query reuse and cancellation. Disposable ClickHouse databases exercise real report SQL, CDC view updates/deletes, replay deduplication, funding allocation, overlapping time intervals and incomplete-source behavior. These tests use synthetic fixtures, not a production backfill.

Still required before enabling the replacement: completed payment enrichment, the deferred source replication step, live full-report reconciliation, and source-load measurements during initial replication. The old Stripe Accounts Read permission is now enabled and its historical ledger imported. Browser navigation, Docker builds and the live Railway-to-Redis serving path have been checked. Historical request-level costs cannot be reconstructed exactly. The world atlas filters the top 500 worlds and renders up to 80 at a time; complete-catalog server search and standalone-game world/time attribution remain explicit follow-ups. Account-deletion cleanup for raw CDC history and imported enrichment remains a release check.

Audience refinement

The activity graph now shares its row with a wider participation/acquisition/age panel. The source detail view sorts by signup count or recorded 7-day activation. Its denominator includes only signups with a complete seven-day window; the numerator is a distinct person with a qualifying action in that window. Retained history can undercount activity, which the detail view discloses. These summaries are built by the worker and add no page-request queries.

Reported age uses the existing nullable birth_year account column, included in the prepared publication and ClickHouse account view. Only aggregate bands reach the admin report. Missing/invalid years are Unknown. The UI states that onboarding preselects a year, so this is reported, not verified, demographic information. No user-gender field is collected; fictional-character gender and content preferences are not used as proxies.

The finance chart readout keeps every label above its amount, with numeric alignment and responsive wrapping. The V10 standalone preview in the original research directory preserves V9 for comparison; age/source-quality figures in that preview remain explicitly unconnected.

Later credential rotation

Inventory every database consumer without copying secrets into this document: Railway app replicas, game services, workers, direct/read connection URLs, scheduled jobs, migration tooling and analytics connectors. Create replacement scoped roles, update each consumer's secrets and verify connectivity, then revoke old credentials and terminate old sessions during the coordinated window. Rotating a password alone does not invalidate already-established connections. A Neon compute restart does not rotate passwords. Keep auth/session/encryption secrets independent from database-password rotation.