> ## Documentation Index
> Fetch the complete documentation index at: https://docs.pcm.game/llms.txt
> Use this file to discover all available pages before exploring further.

# 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.