Building a Leaderboard for a Game Site with SQLite: 3 Traps I Hit with Concurrent Score Submissions

After static JSON couldn't handle scores anymore, I added a Node.js + Express + SQLite backend. On day one, I ran into scores being overwritten, same-score rankings jumping around, and the leaderboard disagreeing with the database.

The root cause wasn't SQLite's performance. It was that I didn't understand SQLite's concurrent write model. Here's the debugging process and fix for all three traps, plus a framework for deciding what to use at each stage.

---

Environment

• Node.js 20.11 + Express 4.18 + better-sqlite3 11.3
• Test devices: MacBook Pro M1 (local load testing), Vercel Serverless (production)
• Tools: autocannon concurrent load testing, SQLite .timer on query timing

---

Why I Moved from Static JSON to a Backend

In the first article, the MVP stored scores in localStorage, which meant they disappeared when you switched devices. By the second article, the game count had grown to 12, and users started asking, "Why can't I see my phone scores on my computer?"

That's the signal you need a backend: cross-device sync.

I chose SQLite for a simple reason: zero ops, embedded, single file, and closest to the static site deployment model. better-sqlite3 has a synchronous API, which is more intuitive than node-sqlite3's async callbacks.

Three tables:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT UNIQUE NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE TABLE game_records (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id),
  game_id TEXT NOT NULL,
  score INTEGER NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE TABLE leaderboard (
  user_id INTEGER NOT NULL,
  game_id TEXT NOT NULL,
  best_score INTEGER NOT NULL,
  achieved_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, game_id)
);

game_records stores every round's score. leaderboard keeps only each user's best score per game.

---

Trap 1: Direct UPDATE Overwrote Scores

Symptoms

I used autocannon to simulate 50 concurrent users submitting scores, 10 submissions per user. The result: many users' final scores weren't their highest submission, but some random lower score.

The First Implementation

// The broken version
app.post('/api/score', (req, res) => {
  const { userId, gameId, score } = req.body
  db.prepare(`
    INSERT INTO leaderboard (user_id, game_id, best_score, achieved_at)
    VALUES (?, ?, ?, ?)
    ON CONFLICT (user_id, game_id)
    DO UPDATE SET best_score = excluded.best_score, achieved_at = excluded.achieved_at
  `).run(userId, gameId, score, Date.now())
  res.json({ ok: true })
})

The problem: it unconditionally overwrites the old score with the new one, whether it's higher or lower. A user submits 100, then 50, and the final stored value is 50.

The Fix

Only update when the new score is higher, and wrap the record insert and leaderboard update in a transaction:

const submitScore = db.transaction((userId, gameId, score) => {
  const now = Date.now()
  const domain_url = 'https://www.playfiddlebops.com/'

  // Record every round
  db.prepare(`
    INSERT INTO game_records (user_id, game_id, score, created_at)
    VALUES (?, ?, ?, ?)
  `).run(userId, gameId, score, now)

  // Only update the leaderboard when the score is higher
  db.prepare(`
    INSERT INTO leaderboard (user_id, game_id, best_score, achieved_at)
    VALUES (?, ?, ?, ?)
    ON CONFLICT (user_id, game_id)
    DO UPDATE SET
      best_score = MAX(excluded.best_score, leaderboard.best_score),
      achieved_at = CASE
        WHEN excluded.best_score > leaderboard.best_score
        THEN excluded.achieved_at
        ELSE leaderboard.achieved_at
      END
  `).run(userId, gameId, score, now)
})

app.post('/api/score', (req, res) => {
  try {
    submitScore(req.body.userId, req.body.gameId, req.body.score)
    res.json({ ok: true })
  } catch (err) {
    res.status(500).json({ error: 'Write failed' })
  }
})

MAX(excluded.best_score, leaderboard.best_score) guarantees only the highest score is kept. The CASE WHEN guarantees achieved_at only updates when the score actually improves.

One trade-off: game_records grows without bound. Every round adds a row. Play 100 rounds, and you have 100 rows. I haven't added a cleanup strategy yet, because SQLite handles millions of rows in a single table just fine. When it actually becomes a bottleneck, I'll add an archival job.

---

Trap 2: SQLite's Concurrent Write Model Wasn't What I Expected

Symptoms

After fixing the overwrite problem, I pushed the load test to 100 concurrent users. Random errors started appearing:

Error: SQLITE_BUSY: database is locked

The frequency wasn't high — about 2–3% of requests failed. But a failed score submission is a bad experience.

How I Tracked It Down

First I added a simple retry:

function withRetry(fn, retries = 3) {
  for (let i = 0; i < retries; i++) {
    try { return fn() }
    catch (err) {
      if (err.code !== 'SQLITE_BUSY' || i === retries - 1) throw err
      // Retry immediately without delay
    }
  }
}

The retry also failed. Because SQLite's write lock defaults to a 5-second timeout, and if the lock is held by another write the whole time, retrying just waits on the same lock.

After reading the SQLite official forum, I understood the root cause: SQLite allows only one writer at a time. When multiple write transactions run concurrently, only one gets the lock; the others either wait or fail. SQLite uses file-level locking and needs coordination for writes. If transactions are short and writes go through a single-writer funnel, SQLite in WAL mode can already achieve good throughput. The problem is mainly when multiple independent writers hold the lock for a long time, or transactions can't be easily organized to avoid blocking each other.

The Fix

Step 1: Enable WAL mode.

WAL (Write-Ahead Logging)'s core advantage is that reads and writes can run concurrently — readers don't block writers, and writers don't block readers. Write transactions are also much faster because content is written only once (rollback journal mode writes twice), and writes are sequential. Official benchmarks show WAL can bring about a 4x write speed improvement on ext4.

const db = new Database('game.db')

db.pragma('journal_mode = WAL')
db.pragma('busy_timeout = 5000')  // Wait up to 5 seconds for the lock

Step 2: Use a single-writer funnel.

This is the key. WAL mode is still a single-writer model. Multiple independent write transactions will block each other. The solution is to serialize all write requests through a queue:

// Serialize writes with better-sqlite3 transactions + a queue
let writeQueue = Promise.resolve()

function enqueueWrite(fn) {
  writeQueue = writeQueue.then(fn, fn)
  return writeQueue
}

app.post('/api/score', (req, res) => {
  enqueueWrite(() => {
    submitScore(req.body.userId, req.body.gameId, req.body.score)
  }).then(() => res.json({ ok: true }))
    .catch(() => res.status(500).json({ error: 'Write failed' }))
})

This way all writes execute serially, and there's no contention between multiple writers. Reads and writes can still run concurrently, because in WAL mode readers aren't affected by writes.

Step 3: Shorten transactions.

The submitScore transaction only does two things: insert into game_records and update leaderboard. No external API calls, no complex computation, no waiting on any I/O.

Load Test Data After the Fix

Metric	Before	After
100 concurrent success rate	97%	100%
P95 response time	180ms	45ms
SQLITE_BUSY error rate	2.3%	0%

Test conditions: MacBook Pro M1, local SQLite, autocannon -c 100 -d 30.

Cost: Serializing writes means throughput has a ceiling. In a local environment, a single writer with SQLite handles roughly 800–1200 writes/second. If the game site grows to thousands of score submissions per second, SQLite isn't enough, and you need PostgreSQL. But that scale is far away for a personal project.

---

Trap 3: Same-Score Rankings Were Unstable, and Leaderboard Positions Jumped

Symptoms

Two users with the same score occasionally swapped positions on the leaderboard. Users complained: "I reached this score before him, why am I behind him?"

Cause

The leaderboard query was:

SELECT user_id, best_score, achieved_at
FROM leaderboard
WHERE game_id = ?
ORDER BY best_score DESC
LIMIT 20

With no secondary sort for ties, SQLite's return order was nondeterministic.

The Fix

Add a secondary sort: when scores are tied, whoever reached it first ranks higher.

SELECT user_id, best_score, achieved_at
FROM leaderboard
WHERE game_id = ?
ORDER BY best_score DESC, achieved_at ASC
LIMIT 20

Add a composite index at the same time:

CREATE INDEX idx_leaderboard_game_score
ON leaderboard (game_id, best_score DESC, achieved_at ASC);

This index lets the leaderboard query go straight through the index instead of a full table scan. The SQLite forum's advice is not to store rankings in the same table, but to compute them by traversing the index. My approach is to compute rank at query time with the ROW_NUMBER() window function, without persisting a rank column.

---

Gradual Evolution from Static JSON to a Backend

Here's the decision framework I summarized during the whole process:

Static JSON: fewer than 20 games, no user system, scores are local-only. Use case: pure showcase site.

SQLite + Node.js: cross-device sync needed, fewer than 1000 users, fewer than 500 writes/second. Use case: personal projects, small teams. Zero ops, single-file deployment.

PostgreSQL + Redis: more than 5000 users, more than 1000 writes/second, real-time rankings needed. Redis Sorted Set is the best choice for real-time leaderboards, supporting O(log N) score updates and range queries. Redis's ZINCRBY command supports atomic score increments, avoiding read-modify-write race conditions. PostgreSQL handles persistence, Redis handles real-time ranking.

Headless CMS: content management needs grow, non-technical people need to edit the game list. A standard migration path is: create a service layer → update page components → switch to API calls, with no component-layer changes needed.

---

A Deeper Look at Same-Score Ranking: How Redis Does It

If you later move to Redis for real-time leaderboards, same-score ranking needs extra handling. Redis Sorted Set sorts by score by default, and for ties, sorts by member lexicographically — which is not "whoever reached it first ranks higher."

The solution is a composite score: put the business score in the high bits and the timestamp in the low bits.

finalScore = bizScore * 10000000000 - timestamp

The larger the timestamp (the later the score was reached), the smaller the finalScore, and the lower the rank. For ties, the earlier user has a larger finalScore and ranks higher. The cost is that Redis scores are float64, with limited precision. If your business score is already very large, adding a timestamp may exceed float64's exact representation range. You need to adjust the FACTOR based on your actual score range.

---

User Authentication: JWT + refresh token

Score submission needs user identity. I used the standard JWT + refresh token approach:

• access token: valid for 15 minutes, stored in memory
• refresh token: valid for 30 days, stored in an httpOnly cookie
• When the access token expires, use the refresh token to silently get a new access token, so the user doesn't need to log in again. Only when the refresh token also expires does the user need to log in fully.

Refresh tokens are stored in the database and rotated after each use, with old refresh tokens automatically invalidated. If a used refresh token is detected, it may have been stolen, and all of that user's tokens are revoked immediately.

---

Unresolved

• Anti-cheat isn't done yet: client-submitted scores have no server-side validation. Users can forge requests to submit arbitrary scores. Next step is server-side score sanity checks, or moving game logic to the server.
• SQLite backup strategy: currently relies on VACUUM and periodically copying the database file, with no incremental backup. If the database is corrupted, the last few hours of data could be lost.
• Cursor-based leaderboard pagination: currently uses LIMIT/OFFSET, which degrades on deep pages. Needs to change to cursor pagination based on (best_score, achieved_at).

---

References

• SQLite official WAL documentation, explaining read/write concurrency and the single-writer model in WAL mode
• SQLite forum discussions on concurrent writes, explaining why a single-writer funnel is more efficient than multiple independent writers
• Redis official tutorial on a complete Sorted Set leaderboard implementation, including ZADD, ZREVRANGE, and ZREVRANK
• Redis ZSet same-score ranking with composite scores, using a timestamp as the fractional part for secondary sorting