Production package for Global Trivia Game

# Production Readiness Report: Global Trivia Game

## Executive summary

ProdTwin Zero recommends a Hybrid AWS Database architecture for Global Trivia Game. Aurora DSQL is the architecture fact source for projects, access patterns, schema versions, recommendation versions, and simulation summaries. DynamoDB is the workload telemetry plane for representative events, hot-key metrics, access-pattern metrics, and replay timelines.

Readiness score: 93/100. Hot partition risk: 23/100.

## Scale target

- Target users: 1,000,000
- Peak concurrent users: 50,000
- Regions: US + EU
- Core features: Live quiz sessions, Global leaderboard, Regional leaderboard, Answer replay, Player history

## Recommended architecture

Recommended Hybrid architecture: Aurora DSQL stores project metadata, access-pattern registry, schema versions, recommendations, and simulation summaries; DynamoDB stores workload telemetry, answer events, hot-key samples, and event replay timelines.

## Cost and reliability matrix

| Strategy | Performance | Reliability | Cost | Consistency | Complexity |
| --- | ---: | ---: | ---: | ---: | ---: |
| Aurora-first | 72 | 88 | 61 | 94 | 70 |
| DynamoDB-first | 91 | 84 | 78 | 68 | 76 |
| Hybrid | 93 | 90 | 82 | 88 | 81 |

## Access pattern mapping

| Access pattern | Operation | Frequency | Consistency | Database | Implementation hint |
| --- | --- | --- | --- | --- | --- |
| Update score | read_write | high | transactional | hybrid | Write immutable events to DynamoDB, then persist the settled fact and schema lineage in Aurora DSQL. |
| Read regional leaderboard | read | high | near_real_time | dynamodb | Use PK/SK keys with run, region, bucket, and shard dimensions; avoid one logical key for all peak writes. |
| Read global leaderboard | read | burst | eventual | dynamodb | Use PK/SK keys with run, region, bucket, and shard dimensions; avoid one logical key for all peak writes. |
| Join live session | read_write | high | near_real_time | hybrid | Write immutable events to DynamoDB, then persist the settled fact and schema lineage in Aurora DSQL. |
| Read user profile | read | medium | strong | aurora_dsql | Store immutable versions and relational references in normalized tables with project_id indexes. |
| Replay session history | read | medium | transactional | hybrid | Write immutable events to DynamoDB, then persist the settled fact and schema lineage in Aurora DSQL. |
| Submit answer event | write | burst | near_real_time | dynamodb | Use PK/SK keys with run, region, bucket, and shard dimensions; avoid one logical key for all peak writes. |

## DynamoDB key design

- Table: ProdTwinTelemetry
- Primary key: PK + SK
- Bad design to avoid: PK = GAME#<gameId>, SK = ANSWER#<timestamp>#<userId>. This is intentionally risky for a live final.
- Improved design: PK = GAME#<gameId>#SHARD#<0-15>, or PK = REGION#<region>#GAME#<gameId>#SHARD#<0-31> for the regional rollup strategy, plus LEADERBOARD#REGION#<region>#BUCKET#<0-31>.

## Hot partition findings

| Logical partition key | Sample events | Estimated WCU/s | Risk | Recommendation |
| --- | ---: | ---: | --- | --- |
| REGION#US#GAME#trivia-finals#SHARD#8 | 32 | 213 | low | Keep shard count tied to estimated peak WCU and monitor per-shard skew. |
| REGION#US#GAME#trivia-finals#SHARD#30 | 27 | 180 | low | Keep shard count tied to estimated peak WCU and monitor per-shard skew. |
| REGION#US#GAME#trivia-finals#SHARD#22 | 29 | 193 | low | Keep shard count tied to estimated peak WCU and monitor per-shard skew. |
| REGION#US#GAME#trivia-finals#SHARD#20 | 32 | 213 | low | Keep shard count tied to estimated peak WCU and monitor per-shard skew. |
| REGION#US#GAME#trivia-finals#SHARD#14 | 27 | 180 | low | Keep shard count tied to estimated peak WCU and monitor per-shard skew. |
| REGION#US#GAME#trivia-finals#SHARD#0 | 29 | 193 | low | Keep shard count tied to estimated peak WCU and monitor per-shard skew. |
| REGION#EU#GAME#trivia-finals#SHARD#9 | 33 | 220 | low | Keep shard count tied to estimated peak WCU and monitor per-shard skew. |
| REGION#EU#GAME#trivia-finals#SHARD#7 | 29 | 193 | low | Keep shard count tied to estimated peak WCU and monitor per-shard skew. |

## Migration plan

- Current answer key: PK = GAME#<gameId>
- Improved answer key: PK = GAME#<gameId>#SHARD#<0-15>
- Regional rollup answer key: PK = REGION#<region>#GAME#<gameId>#SHARD#<0-31>
- Affected access patterns: Update score, Read regional leaderboard, Read global leaderboard, Submit answer event

1. Dual-write new answer events to sharded regional keys while retaining the old GAME#gameId key for replay.
2. Backfill historical answer events by scanning GAME#gameId and writing REGION#...#SHARD#... items idempotently.
3. Rebuild regional leaderboard buckets from sharded answer and score events.
4. Publish global leaderboard projections by hour window and bucket after regional pressure is stable.
5. Cut over reads after hot-key pressure stays below the threshold for a full replay window.
6. Retire old single-key writes after support replay and rollback windows close.

Rollback:

- Keep the old GAME#gameId and LEADERBOARD#GLOBAL read paths live until projections are verified.
- Gate new reads behind a feature flag so traffic can return to the previous projection immediately.
- Replay DynamoDB event timelines into Aurora settlement facts before removing old keys.

## Architecture warnings

- Single game partition can saturate during live rounds: If answer events use PK = GAME#gameId, a 50k concurrent live game can concentrate thousands of writes per second on one logical key. Mitigation: Add write sharding with GAME#gameId#SHARD#0..15 and replay by shard when rebuilding a round.
- Global leaderboard should not be a single write target: A global top-100 materialization is read-friendly, but writing every score into LEADERBOARD#GLOBAL creates a hot aggregation point. Mitigation: Maintain regional leaderboards with bucketed keys, then publish an async global projection.
- Cross-continent strong writes need regional ownership boundaries: US + EU traffic with strong score settlement should avoid pretending one global relational write domain has no topology tradeoffs. Mitigation: Use regional data domains for live play, then reconcile score facts and report projections through Aurora DSQL schema versions.
- Representative samples should be extrapolated to protect cloud cost: The simulator should write hundreds or thousands of telemetry rows, then model the 1M-user case mathematically instead of generating full production load. Mitigation: Cap each run at 5,000 sample events and calculate peak WCU/RCU from access-pattern frequency and key cardinality.

## Migration suggestions

- Store architecture facts and immutable schema versions in Aurora DSQL before generating API code.
- Store high-frequency simulation telemetry in DynamoDB using run-scoped PK/SK items.
- Use write sharding for live answer events and regional buckets for leaderboards.
- Keep simulation sample writes capped and extrapolate WCU/RCU for the 1M-user scenario.

## Next steps

1. Create the DynamoDB table ProdTwinTelemetry with PK, SK, GSI1, and GSI2.
2. Create the Aurora DSQL or Aurora PostgreSQL schema from the exported SQL.
3. Set Vercel Production environment variables for AWS and DATABASE_URL.
4. Record the Devpost demo with the bad-key to sharded-key comparison.