V. Database Strategy & Schemas

The Firestore/PostgreSQL synchronization bridge, eventual consistency, and security rules.

5.1 The Dual-Database Philosophy & The Sync Bridge

Operating a high-speed NoSQL database (Firestore) alongside a rigid relational ledger (Cloud SQL) introduces the classic distributed systems challenge: Eventual Consistency and State Synchronization.

• The Resolution Bridge: The Next.js client never writes directly to Cloud SQL.
• When the 60-second timer expires in Firestore, the client does nothing.
• The Cloud Scheduler triggers the Cloud Run GameLogicManager, which reads the final state from Firestore, opens a secure transaction with Cloud SQL to award currencies, and only then updates the Firestore table status to resolved.

5.2 Live Game State (Cloud Firestore - NoSQL)

Firestore is optimized for massive parallel reads and real-time websocket pushes; it is strictly for “in-flight” data.

1. The GlobalState Edge Cache

• Clients do not subscribe directly to the Firestore GlobalState document to prevent massive read costs.
• Instead, the Next.js backend fetches the state every 60 seconds, caches it at the Edge, and serves it via a standard API route (/api/state).

2. The ActiveTables Collection & Secret Vector Exploit

• Firestore client SDKs fetch entire documents, meaning the secret Z-axis vector cannot be stored in the public table document without exposing it to Chrome DevTools.
• Public Document: ActiveTables/{table_uuid} contains the headline, timer, and seats. Clients listen to this.
• Private Subcollection: ActiveTables/{table_uuid}/secure_data/solution contains the secret_vector. It is locked via security rules and only accessible via the Cloud Run Admin SDK.

3. Dead Match Handling (PlayerPresence)

• When the websocket severs, Firestore writes online: false.
• A Cloud Run cron job scans this collection. If a player is offline but locked in a waiting_for_players seat, the server instantly ejects them to prevent dead lobbies.

5.3 Core Economy & Asset Library (Cloud SQL - PostgreSQL)

Cloud SQL is the fortress; it handles all currency and progression and requires ACID compliance.

• Connection Pooling: Cloud Run scales horizontally. To prevent connection exhaustion, containers connect to a proxy (PgBouncer or Cloud SQL Auth Proxy) configured with strict limits.
• Atomic Transactions: When a player spends currency, the database executes a strict SELECT ... FOR UPDATE to lock the wallet row, preventing race conditions and double-spending.

5.4 Telemetry & Analytics (BigQuery)

BigQuery acts as the “God View” for economy tracking.

• Ingestion: The GameLogicManager uses the BigQuery Storage Write API to stream batches of resolved match data asynchronously, preventing the game loop from bottlenecking.
• Cost Control (Partitioning & Clustering): The MatchHistory table is time-partitioned by DATE(resolved_at) and clustered by winning_quadrant to minimize bytes scanned per query.
• Materialized Views: The Looker Studio dashboard queries pre-calculated Materialized Views instead of raw tables, reducing load times to milliseconds and costs to fractions of a penny.