DEV Community

Cover image for Single-table DynamoDB design for a real-time game (rooms, rounds, leaderboards)
ARUNABH PRIYADARSHI
ARUNABH PRIYADARSHI

Posted on

Single-table DynamoDB design for a real-time game (rooms, rounds, leaderboards)

#architecture #aws #database #gamedev

For the H0 Hackathon I built Kauwa Udd, a real-time multiplayer reaction game on Vercel + Amazon DynamoDB. People hear "DynamoDB" and reach for a table per entity — users table, rooms table, scores table. I put everything in one table. Here's the design, why it works for a live game, and the exact key patterns.

Why single-table?

  • Fewer round-trips: related items share a partition, so one Query fetches a room and all its players.
  • One thing to provision + secure: my Vercel OIDC IAM role is scoped to a single table ARN (+ its indexes). Less surface area.
  • It fits the access patterns — and in DynamoDB you design for access patterns, not entities.

The trade-off is upfront key modeling. For a game with a handful of well-known access patterns, that's a feature, not a chore.

The keys

Two attributes drive the base table: PK (partition) and SK (sort). One GSI — GSI1 (GSI1PK / GSI1SK) — powers leaderboards.

Entity PK SK GSI1PK / GSI1SK
Content object OBJ#<id> META —
Room ROOM#<code> META —
Player in room ROOM#<code> PLAYER#<playerId> —
Round ROOM#<code> ROUND#<n> —
Reaction (click) ROOM#<code> CLICK#<n>#<playerId> —
User stats USER#<id> STATS —
Leaderboard entry LB#<period>#<scope> USER#<id> LB#<period>#<scope> / <score>

The pattern: a room is a partition. Its META, every player, every round, and every reaction live under ROOM#<code> — so the whole live game state is one partition, queryable in a single call.

Access patterns → queries

Get a room + its players (lobby render):

const res = await ddb.send(new QueryCommand({
  TableName: TABLE,
  KeyConditionExpression: 'PK = :pk',
  ExpressionAttributeValues: { ':pk': `ROOM#${code}` },
}))
// one round-trip → META + all PLAYER# + ROUND# items
Enter fullscreen mode Exit fullscreen mode

Collect a round's reactions (the game loop reads these at the 3-second boundary):

await ddb.send(new QueryCommand({
  TableName: TABLE,
  KeyConditionExpression: 'PK = :pk AND begins_with(SK, :sk)',
  ExpressionAttributeValues: { ':pk': `ROOM#${code}`, ':sk': `CLICK#${round}#` },
}))
Enter fullscreen mode Exit fullscreen mode

begins_with on the sort key is the workhorse here — "all players' clicks for round N" is just a prefix.

Leaderboards with one GSI

DynamoDB has no ORDER BY, so top-N comes from a GSI whose sort key is the score. Each leaderboard bucket (LB#daily#global, LB#all#room#<code>, …) is a GSI partition; query it descending, limit N:

await ddb.send(new QueryCommand({
  TableName: TABLE,
  IndexName: 'GSI1',
  KeyConditionExpression: 'GSI1PK = :pk',
  ExpressionAttributeValues: { ':pk': `LB#${period}#${scope}` },
  ScanIndexForward: false,   // highest score first
  Limit: 20,
}))
Enter fullscreen mode Exit fullscreen mode

I store the score zero-padded (or as a numeric GSI sort key) so lexical order matches numeric order. The same GSI serves global daily/weekly boards and per-room boards just by changing the partition value — including the public per-room leaderboard you can share after a match.

Honest caveat: top-N is easy; "your exact rank among millions" is the one thing single-table DynamoDB doesn't do cheaply (you'd add a Redis sorted set or, in my roadmap, Aurora DSQL). For top-N + your-own-best, the GSI is perfect.

TTL for the ephemeral stuff

Rooms, rounds, and clicks are throwaway. Each carries a ttl (epoch seconds) and DynamoDB sweeps them automatically — no cleanup jobs, no cron. Durable stuff (user stats, leaderboards) just omits ttl.

Auth in the same table

Even Auth.js sessions live here via the DynamoDB adapter (configured to the same PK/SK + GSI1). Auth item keys (USER#…, ACCOUNT#…) don't collide with game keys (ROOM#…, OBJ#…, LB#…), so one table holds the whole app.

Takeaways

  • Model partitions around your access unit (here, the room) → live state in one query.
  • begins_with(SK, …) turns "all the X for Y" into a prefix scan within a partition.
  • One GSI with score as the sort key covers every leaderboard variant.
  • TTL makes ephemeral game data self-cleaning.

▶️ Play it: https://kauwa-udd.vercel.app
💻 Code: https://github.com/nextjedi/kauwa-udd

#H0Hackathon #DynamoDB #AWS #Vercel #serverless #webdev

Top comments (0)