Data flow and storage

1. Foundational mental model

Four stores hold state, each with one owner:

Store	Owner	Holds	Durability
Convex	apps/web/src/convex	Accounts, XP, friends, daily completions, Learn, aggregated country stats, globe feed, replays, rewind rollups	Managed database, the system of record
stats.db	Bun server (StatisticsManager)	Per-country play volume and flag-difficulty counters, forward dedupe log	File on the VPS volume, default journal mode
diagnostics.db	Bun server (DiagnosticsManager)	Per-match event logs for incident debugging	File on the VPS volume, WAL, 7-day retention
R2 bucket flags	Build and upload scripts	Flag images, audio, daily OG images, SvelteKit client chunks	Object storage behind cdn.flags.games and assets.flags.games

The browser keeps settings, pending XP tokens, seat tickets, and local progress in localStorage and sessionStorage. The service worker caches flag images, audio, and the app shell in Cache Storage. No code in apps/web/src or apps/web/static/sw.js opens IndexedDB.

Write paths. Only the Bun server and SvelteKit hold BACKEND_CONVEX_SHARED_SECRET; the browser talks to Convex with its own JWT.

2. Implementation status & known gaps

Active invariants & limitations

• Active today: every Bun-to-Convex write goes through an HTTP action that checks x-convex-secret against BACKEND_CONVEX_SHARED_SECRET or BACKEND_CONVEX_SHARED_SECRET_PREV with a timing-safe compare.
• Active today: country forwards are deduplicated twice: once in stats.db (convex_country_forward_log, INSERT OR IGNORE) and once in Convex (processedCountryStatEvents, looked up by eventKey).
• Active today: diagnostics.db runs PRAGMA journal_mode = WAL; stats.db sets no journal mode and uses SQLite’s default rollback journal.
• Known gap: the Convex forward is fire-and-forget (void fetch(...)) with no retry queue. A failed forward releases the local reservation so a later identical eventKey could retry, but nothing re-sends the original.
• Known gap: the SQLite files live on one VPS volume. Backups and local copies are manual (scp, then bun run pull:prod:stats).
• Known gap: flag_* difficulty tables are fed only by solo ledger replays. Multiplayer answers do not update them.

3. Concrete implementation

Convex tables

schema.ts spreads authTables from @convex-dev/auth (authSessions, authAccounts, authRefreshTokens, authVerificationCodes, authVerifiers, authRateLimits) and defines 40 tables of its own, including an override of users.

Group	Tables
Identity	users, players, usernameReservations
Social	friendships, friendRequests, presence, activityFeed
XP and progress	playerStats, gameTokens, xpAwards, retentionSummaries
Daily challenge	dailyChallengeCompletions, dailyChallengeLeaderboardSnapshots, dailyChallengeDayArchives, dailyChallengeMetrics, processedDailyChallengeMetricEvents, dailyPlayVolumeSnapshots
Learn	learnCards, learnReviewEvents
Country stats	countryStats, countryStatsStratum, countryPlayerSubmissions, pendingCountryStatEvents, countryStatPerformanceCredits, processedCountryStatEvents
Trust	trustProfiles, trustEvents, fingerprintCohortSessions
Globe and site activity	globePlayEvents, globeSnapshotCounters, siteActivityEvents, siteActivitySnapshotCounters, globalCounters
Replays	replays
Rewind rollups	monthlyXpRollups, monthlyCommunityRollups, userYearSnapshots, globalYearSnapshots
Infrastructure	rollingWindowThrottleState, supporterWaitlist

apps/web/src/convex/http.ts exposes four trusted POST routes: /game-result (Bun), /replay-save (Bun), /daily-challenge-metrics (SvelteKit daily-session-validate.ts), and /challenge-completions (SvelteKit forward-challenge-completion.ts).

Bun SQLite files

Both managers resolve apps/server/data/ and pick a file name by NODE_ENV:

apps/server/src/lib/diagnostics/diagnostics-manager.ts

const fileName =
  env.NODE_ENV === "production" ? "diagnostics.production.db" : "diagnostics.db";
this.db = new Database(path.join(dataDir, fileName));
this.db.run("PRAGMA journal_mode = WAL");

apps/server/src/lib/managers/stats-management.ts

const fileName = env.NODE_ENV === "production" ? "stats.production.db" : "stats.db";
const dataDir = path.join(this.serverRootPath, "data");
mkdirSync(dataDir, { recursive: true });
const dbPath = path.join(dataDir, fileName);
this.db = new Database(dbPath);

stats.db table	Key	Purpose
country_stats	countryCode	Players and games per player country
country_stats_stratum	countryCode, difficulty, gameType	Average score, accuracy, response time per stratum
player_submissions	userId, countryCode	First-seen check so totalPlayers counts each session once
convex_country_forward_log	event_key	Reservation that blocks duplicate Convex forwards; rows older than 30 days are deleted, at most hourly
globe_actor_last_globe_emit	actor_hash	Last globe dot per anonymous actor (60 s cooldown)
globe_actor_country_last_globe_emit	actor_hash, country_code	Last globe dot per actor and country (15 min cooldown)
flag_recognition_stats	countryCode	Times each flag was prompted and answered correctly
flag_confusion_stats	targetCountryCode, guessedCountryCode	Wrong-answer pairs
flag_prompt_stratum	countryCode, gameType, difficulty	Recognition split by mode and difficulty
flag_option_exposure	targetCountryCode, optionCountryCode, gameType, difficulty	How often a distractor was offered and picked

diagnostics.db has diagnostic_matches, diagnostic_events, and diagnostic_event_overflow. A timer runs cleanup() every hour and deletes rows older than RETENTION_MS = 7 * 24 * 60 * 60 * 1000.

R2 objects

Prefix	Written by	Served from
images/flags/… (readable and opaque o/ keys)	bun run upload:flags	https://cdn.flags.games/images/flags/…
audio/…	bun run upload:audio	CDN
assets/_app/immutable/…	upload-client-build.ts during the web build	assets.flags.games Worker (WEB_ASSETS binding)
Daily OG images	/api/revalidate cron via $lib/daily-og	https://cdn.flags.games/static/og/daily/<utcDate>/…

Browser storage

Key	Storage	Contents
settings	localStorage	GameSettings, including includeExtendedTerritories
__convexAuthJWT_<deployment>, __convexAuthRefreshToken_<deployment>	localStorage (legacy)	Older @convex-dev/auth credentials. bootstrap-convex-auth-session.ts moves the refresh token into a cookie and removes both keys once the cookie write succeeds.
pendingXpTokens, pendingDailyXpTokens	localStorage (legacy copy migrated out of sessionStorage)	Signed XP tokens awaiting a signed-in claim
spaced-repetition-v2, flags-learn-review-pending-v1	localStorage	Learn card state and review events not yet synced
daily-session-v2	localStorage	In-progress daily session
multiplayerSeatTicket	sessionStorage	Reconnect credentials for a multiplayer seat, scoped to the tab
session_token	httpOnly cookie	Anonymous session id (see Dual identity trust)

4. Internal mechanics & mathematics

A solo result’s journey

Solo result, from the last answer to Convex. Steps on the right run only when the player's session is linked to a Convex user.

The country written to country_stats is the player’s country from edge geolocation (context.arcjet.countryCode), not a flag. If geolocation returns nothing or the player opted out of leaderboards, the response status is no_country or opted_out and no Convex forward starts. The flag tables are written regardless.

A multiplayer answer’s journey

One multiplayer answer. Nothing leaves the process until the game ends.

Idempotency keys

The forward key is built from game facts, so a retry of the same result produces the same key:

apps/server/src/lib/country-stats/convex-country-forward.ts

export function buildGlobeEventKey(params: {
  gameKind: "solo" | "multiplayer";
  roomId: string;
  gameStartTime: number;
  userId: string;
  totalQuestions: number;
  correctAnswers: number;
  score: number;
}): string {
  if (params.gameKind === "solo") {
    return `globe:solo:${params.userId}:${params.gameStartTime}:${params.totalQuestions}:${params.correctAnswers}:${params.score}`;
  }
  return `globe:multi:${params.roomId}:${params.gameStartTime}:${params.userId}`;
}

The local reservation is one statement:

apps/server/src/lib/country-stats/convex-country-forward-reservation.ts

const INSERT_RESERVED =
  "INSERT OR IGNORE INTO convex_country_forward_log (event_key, reserved_at) VALUES (?, ?)";

export function reserveConvexCountryForwardSlot(
  database: SqliteRunTarget,
  eventKey: string,
  reservedAtMs: number
): boolean {
  const outcome = database.run(INSERT_RESERVED, [eventKey, reservedAtMs]);
  return outcome.changes > 0;
}

changes > 0 means this process won the key. On a non-OK response or network error, onComplete({ ok: false }) deletes the row.

With $r$ forwards per day and the 30-day prune, the log holds at most about $30r$ rows between hourly cleanups:

$$\text{rows} \le r \cdot 30 + r \cdot \tfrac{1}{24}$$

The second term is the worst case of one missed hourly prune window.

Globe throttling

For an actor hash $a$ and player country $c$, the server sets skipGlobeFeed when

$$t - \text{last}(a) < 60\,\text{s} \quad\lor\quad t - \text{last}(a, c) < 15\,\text{min}$$

The stats row is still recorded in Convex; only the anonymous globe dot is skipped. globeActorHash is the first 16 hex characters of $\text{HMAC-SHA256}(\text{secret}, \text{sessionUserId})$, so the session id never reaches Convex’s globe tables.

5. Threat model, failure modes & edge cases

• Lost forwards. A Convex outage during game end drops the forward for that result. SQLite still has the local row, so /api/leaderboard/countries on the Bun server and Convex countryStats can disagree until more games arrive. No reconciliation job exists.
• Single-writer SQLite. bun:sqlite calls are synchronous on the event loop. stats.db in rollback-journal mode blocks readers during a write; WAL on diagnostics.db lets reads continue while events append. Flag observations are batched in one db.transaction per result.
• Server restart. Room state, replay buffers, and the in-memory trust map are lost. SQLite files survive because they are on the Coolify volume. A match in progress at restart never forwards.
• Idle games. processGameResults returns early when no answers were recorded, and Convex /game-result also answers recorded: false when answersRecorded === 0.
• Retention mismatch. Diagnostics keep 7 days, the forward log 30 days, and Convex purges processedCountryStatEvents on a cron. A forward with a previously seen eventKey that arrives after both windows would be counted twice. Keys include gameStartTime, so this needs a resubmission of the same result weeks later.
• Browser tokens. Pending XP tokens sit in localStorage in plain text. They are signed and bound to claimUserId, so a copied token only works for the account it names, and Convex rejects it after expiresAt (30 minutes).

6. Architectural tradeoffs & non-goals

Choice	Alternative	Why this one
SQLite on the game server for hot counters	Write every game straight to Convex	The server can answer /api/leaderboard/countries and flag-difficulty queries without a network hop, and a Convex outage does not block gameplay.
Fire-and-forget Convex forward	Durable outbox table with retries	Simpler, and a missed stats row is low cost. The reservation release leaves room for a retry path later.
WAL only on diagnostics.db	WAL on both	Diagnostics append many small rows and are read by the MCP tools during incidents. stats.db has not needed it.
Aggregation through pendingCountryStatEvents and a 5-minute cron	Update countryStats inside /game-result	Keeps the HTTP action short and avoids write contention on hot country rows.
One shared secret for all trusted writes	Per-route credentials	One value to rotate, with _PREV for overlap. The cost is a large blast radius if it leaks.

Non-goals: exactly-once delivery from Bun to Convex, cross-region SQLite replication, and storing raw IPs or session ids in Convex globe tables.