ScoreResume: Deep Cut — Internals, Physics, and the Things That Will Burn You

This book goes further than "why." It goes into the physical reality of memory, network, and atomic CPU instructions that justify every pattern in ScoreResume's codebase.

Preface

The first book explained decisions. This book explains the ground they stand on.

Every design pattern in software has a physical justification — a property of memory, disk, network, or CPU that makes the pattern necessary rather than merely conventional. This book excavates those physical realities.

The reader should have read Book 1 first. This book assumes you already know what ScoreResume does and what decisions were made. Here, we ask: what would physically break if you removed each guard?

Chapter 1: The Credit Deduction — Why SET NX Is Atomic and GET + SET Is Not

The Physical Reality

Modern CPUs execute instructions on multiple cores simultaneously. Two threads on two cores can read from the same memory address in the same CPU cycle. Both reads complete successfully. Both see the same value. Both write a new value. One write is lost.

This is not a bug — it is how hardware works. Preventing it requires special CPU instructions that guarantee "read, modify, write" as an uninterruptible sequence. On x86, this is the LOCK prefix. On ARM, it is LDREX/STREX. Database engines and Redis implement their own atomicity guarantees on top of these primitives.

The Junior Dev Path

The naive code for deducting a credit:

// DANGEROUS — race condition
const pack = await prisma.pack.findUnique({ where: { id: packId } })
if (pack.creditsUsed >= pack.creditsTotal) throw new Error("No credits")
await prisma.pack.update({ data: { creditsUsed: pack.creditsUsed + 1 } })

This is a read-modify-write pattern across two separate SQL statements. Between the SELECT and the UPDATE, another worker can execute the same two statements. Both workers read creditsUsed = 4 against creditsTotal = 5. Both see credits available. Both update to creditsUsed = 5. The pack now has creditsUsed = 5 against creditsTotal = 5 — but two PDF jobs were dispatched.

The Insight

The guard must be inside the UPDATE statement itself. Not checked before it — inside it. PostgreSQL evaluates the WHERE clause and the SET expression while holding a row-level lock. The read and the write are one atomic database operation:

-- This is what Prisma generates for updateMany with a WHERE guard
UPDATE "Pack"
SET "creditsUsed" = "creditsUsed" + 1
WHERE "id" = $1
  AND "creditsUsed" < "creditsTotal";

If two workers race to execute this statement, PostgreSQL serializes them. One acquires the row lock, executes, releases. The second acquires the row lock, checks creditsUsed < creditsTotal, finds the condition false (the first worker already incremented to the limit), and updates 0 rows. The count returned by Prisma's updateMany is 0. The second worker throws "credits exhausted."

What ScoreResume Does

// lib/queue/pdf-worker.ts
const deducted = await prisma.pack.updateMany({
  where: {
    id: packId,
    creditsUsed: { lt: pack.creditsTotal },  // atomic check-and-modify
  },
  data: { creditsUsed: { increment: 1 } },
})

if (deducted.count === 0) {
  // The race was lost — or credits were exactly exhausted — abort
  await redis.del(`pdf:deduct:${job.id}`)
  throw new Error("Pack credits exhausted")
}

The { increment: 1 } translates to creditsUsed + 1 in SQL — PostgreSQL never reads the application-side value. The lt: pack.creditsTotal translates to the WHERE guard. The entire operation is one SQL round-trip.

Full Tradeoff Map

ScoreResume uses option 3. Option 4 (optimistic concurrency) would require a version column and retry logic — correct but more code than necessary for a low-throughput credit deduction.

Transferable Mental Model

Any time you read a value, check a condition, and write a new value based on the check — the check and the write must be atomic. In SQL, this means the condition goes in the WHERE clause of the UPDATE, not in a preceding SELECT. In Redis, this means SET NX (atomic set-if-not-exists) instead of EXISTS + SET. In any system, look for the atomic primitive that eliminates the gap between check and write.

Chapter 2: The Redis SET NX — Why One Atomic Command Beats Two

The Physical Reality

Redis is single-threaded for command execution. All commands are serialized. This is not a performance weakness — it is a design choice that makes every Redis command inherently atomic from the application's perspective. No two commands from different clients can interleave inside Redis.

The SET key value EX seconds NX command is a single atomic command that sets a key only if it does not exist (NX) and sets its TTL in the same operation. This is the building block for distributed locks.

The Junior Dev Path

The naive distributed lock:

// DANGEROUS — time-of-check to time-of-use (TOCTOU)
const exists = await redis.exists("ai:lock:user123")
if (exists) throw new Error("Another AI call in progress")
await redis.set("ai:lock:user123", "1")  // gap here!
await redis.expire("ai:lock:user123", 60)
// ... AI call

Between redis.exists() and redis.set(), another server instance can also check redis.exists(), also see false, and also call redis.set(). Both proceed with the AI call. The lock is broken. The budget check passes for both — "user's spend is $0" — because neither has logged usage yet.

Even with a single server, the set and expire are separate commands. If the server crashes after set but before expire, the key lives forever. The user is permanently locked out of AI features.

The Insight

SET key value EX 60 NX is one command, one round-trip, one atomic operation. Redis guarantees no other command executes between the check (does the key exist?) and the set (create it with TTL). The TTL is set in the same command — no gap, no orphan keys.

What ScoreResume Does

// lib/ai/cost-guard.ts
export async function acquireAiLock(userId: string): Promise<boolean> {
  const result = await redis.set(`ai:lock:${userId}`, "1", "EX", 60, "NX")
  return result === "OK"   // "OK" = acquired, null = someone else has it
}

Five concurrent AI requests for the same user all hit this line simultaneously. Redis processes them one at a time. The first sets the key, gets "OK", proceeds. The next four get null — the key already exists — and are rejected immediately. No user can have more than one in-flight AI call at a time.

The 60-second TTL is a dead-man's switch. If the AI call completes normally, releaseAiLock(userId) deletes the key explicitly. If the server crashes mid-call, Redis automatically removes the key after 60 seconds. No manual cleanup, no stuck users.

The TOCTOU Class of Bugs

TOCTOU (Time Of Check, Time Of Use) is the pattern where:

1. You check a condition
2. Time passes (network round-trip, context switch)
3. You act on the condition
4. The condition changed between 1 and 3

Every TOCTOU is a race condition waiting for the right timing to manifest. In production, "the right timing" often coincides with high load — when concurrent requests are most common, exactly when you least want bugs.

The fix is always the same: collapse the check and the act into a single atomic operation. In Redis: SET NX. In SQL: UPDATE WHERE. In file systems: O_CREAT | O_EXCL (create only if not exists, as an atomic syscall).

Chapter 3: The Webhook Two-Transaction Problem — Why Atomicity and Idempotency Are Different

The Physical Reality

A distributed transaction is a transaction that spans two systems. Guaranteeing atomicity across two systems requires a coordination protocol — 2-phase commit (2PC). 2PC is slow (two round-trips minimum), complex (coordinator failures, blocking), and overkill for most cases.

ScoreResume does not use 2PC. It achieves safety through a combination of idempotency and careful state encoding.

The Problem in Concrete Terms

Dodo sends payment.succeeded. ScoreResume must:

1. Create a WebhookEvent record (mark "I have seen this payment")
2. Activate the Pack (grant user access)
3. Increment the coupon usedCount
4. Mark the WebhookEvent as processed = true

These four operations are not automatically atomic. The network can fail between any two steps. The database can fail. The process can be killed.

Every possible failure point has a specific bad outcome:

Crash after step 1, before step 2:
  → WebhookEvent exists, processed=false
  → Pack is still pending
  → Dodo retries → step 1 hits P2002 → reads processed=false → runs step 2 ✓
  SAFE: retry finishes the job

Crash after step 2, before step 4:
  → Pack is active
  → WebhookEvent exists, processed=false
  → Dodo retries → step 1 hits P2002 → reads processed=false → tries step 2 again
  → activatePack() checks: is paymentId still pending_*? No → no-op
  → Continues to step 3 and 4 ✓
  SAFE: activatePack idempotency prevents double-activation

Crash after step 4:
  → Everything done → WebhookEvent.processed=true
  → Dodo retries → step 1 hits P2002 → reads processed=true → returns 200 early ✓
  SAFE: idempotency check skips completed work

What ScoreResume Does

Two independent safety mechanisms, not one:

Mechanism 1: WebhookEvent as idempotency log

// app/api/webhooks/dodo/route.ts
try {
  await prisma.webhookEvent.create({
    data: { id: payment.payment_id, processed: false, … }
  })
} catch (e) {
  if (isPrismaUniqueViolation(e)) {
    const existing = await prisma.webhookEvent.findUnique({
      where: { id: payment.payment_id },
      select: { processed: true },
    })
    if (existing?.processed) return new Response("OK", { status: 200 })
    // processed=false → previous attempt crashed → fall through to retry
  } else {
    throw e // unexpected DB error — propagate
  }
}

Mechanism 2: activatePack idempotency

// lib/payments/pack-service.ts
export async function activatePack(params) {
  const pack = await prisma.pack.findUnique({ where: { id: params.packId } })
  if (!pack) throw new Error("Pack not found")
  if (!pack.paymentId.startsWith("pending_")) {
    logger.warn({ packId: params.packId }, "Pack already active — skipping")
    return pack  // idempotent no-op
  }
  // Pack is still pending — activate it
  return prisma.pack.update({
    where: { id: params.packId },
    data: {
      paymentId: params.paymentId,
      expiresAt: calcExpiryDate(pack.packType),
    },
  })
}

The Two-Phase Commit Alternative

If ScoreResume used 2PC:

1. Prepare phase: lock Pack row, lock WebhookEvent row, reserve coupon decrement
2. Commit phase: apply all changes

2PC requires a coordinator. If the coordinator dies during the commit phase, the locks are held indefinitely — "in-doubt transactions." All involved systems must wait for the coordinator to recover. This is the blocking problem that makes 2PC impractical for web services.

ScoreResume's approach is the Saga pattern: a sequence of local transactions where each step is reversible (activatePack is idempotent, so a "undo" is trivially a no-op). No coordinator, no blocking. Just careful ordering and idempotent operations.

Full Tradeoff Map

ScoreResume is option 3: simpler than a Saga, much simpler than 2PC, safe enough for the threat model.

Transferable Mental Model

When two systems must agree on state, you cannot use a single transaction. Instead: encode state transitions idempotently (activatePack with the pending_* check), log the event before acting (WebhookEvent with processed=false), and mark completion atomically with the last action. This pattern works for any cross-system operation: email sending, SMS delivery, third-party API calls.

Chapter 4: Redis Sorted Sets — Why a Sliding Window Beats a Counter

The Physical Reality

A rate limit counter is a number that resets at fixed intervals. A 60-requests-per-minute counter resets every minute — at the boundary of each clock minute. But "60 requests per minute" should mean "no more than 60 requests in any 60-second window" — not "no more than 60 requests before the next minute ticks over."

With a counter, a burst is possible: 60 requests in the last second of one minute, 60 requests in the first second of the next minute. 120 requests in 2 seconds — twice the nominal limit. This is the "fixed window burst" problem.

The Sorted Set as a Time-Indexed Queue

Redis sorted sets store members with a numeric score. The score here is the request timestamp in milliseconds. The set for a user's rate limit window looks like:

"rl:ai_bullet:user123"
  member: "1704067200000-0.34"   score: 1704067200000  (request at T=0s)
  member: "1704067215000-0.71"   score: 1704067215000  (request at T=15s)
  member: "1704067230000-0.12"   score: 1704067230000  (request at T=30s)
  member: "1704067245000-0.85"   score: 1704067245000  (request at T=45s)

At any point in time, the window contains all requests whose timestamp (score) is within the last windowMs milliseconds. Old requests are explicitly removed before counting.

The Four-Command Pipeline

// lib/redis/with-rate-limit.ts
const pipeline = redis.pipeline()
const now = Date.now()
const windowStart = now - config.windowMs
const member = `${now}-${Math.random()}`

pipeline.zremrangebyscore(key, '-inf', windowStart)   // 1. Remove expired
pipeline.zcard(key)                                    // 2. Count remaining
pipeline.zadd(key, now, member)                        // 3. Add this request
pipeline.pexpire(key, config.windowMs + 1000)         // 4. Reset TTL

const results = await pipeline.exec()
const count = results[1][1] as number  // count BEFORE this request was added

Why is count = results[1] and not results[2]?

The zcard runs after removing expired members but before adding the current request. This gives the count of prior requests in the window. If count >= limit, reject this request — it would be the (limit+1)th.

Why windowMs + 1000 TTL? Redis TTL management runs approximately every 100ms (the "active expiry" cycle). Setting TTL to exactly windowMs would cause the key to sometimes expire while old entries are still technically within the window. The 1000ms buffer ensures the key outlives its youngest member by at least 1 second.

The Random Suffix on Member Name

const member = `${now}-${Math.random()}`

Sorted sets require unique members. Two requests at the exact same millisecond (same now value) would have the same member name and the second zadd would update the score of the existing member rather than adding a new entry. The count would show 1 instead of 2.

The random suffix (0.34, 0.71, etc.) makes collisions statistically impossible — Math.random() produces a 64-bit float from a high-entropy source. Even at 1 million requests per second, the probability of two simultaneous requests sharing both the same millisecond and the same random suffix is astronomically small.

Pipeline Atomicity

The four commands are sent in a single pipeline.exec() call. Redis pipelines are not transactions — another client's commands can interleave between the four commands. This is intentional: a transaction (MULTI/EXEC) would lock the key and prevent concurrent rate limit checks by other requests for the same user, creating a bottleneck.

The interleaving is safe here because:

• zremrangebyscore is idempotent (removing already-removed entries is a no-op)
• zcard reads a consistent snapshot of the set at that moment
• zadd adds one entry regardless of concurrent adds
• Each request adds exactly one entry and reads one count

Two concurrent requests may both see a count of 39 (both under a limit of 40) and both proceed. The count then becomes 41. This is an intentional trade-off: slightly over-allow at the exact boundary in exchange for no locking. The correct approach for strict enforcement is WATCH + MULTI/EXEC, but that serializes concurrent requests — a performance problem at high throughput.

Full Tradeoff Map

ScoreResume uses the sorted-set sliding window: accurate, fast, and implementable without Lua scripts.

Chapter 5: BullMQ Job Survival — The Worker's Power Loss Problem

The Physical Reality

The BullMQ worker dequeues a job, processes it, and marks it complete. But what happens when the server loses power (or OOM-kills the process) between dequeue and complete?

BullMQ uses Redis as its persistent store. The job is in the active set in Redis while processing. The worker periodically sends heartbeats — ZADD bull:pdf-generation:active <timestamp> <jobId> — to signal "I am still working." If the heartbeat stops (worker died), BullMQ's stalledInterval check moves stale jobs back to the waiting set after lockDuration milliseconds.

This is the BullMQ survival guarantee: a job that was dequeued but not completed will eventually be re-processed. But this guarantee creates the double-processing problem.

The Double-Processing Problem

Timeline:
  T=0s   Worker dequeues job "pdf:final:resume123"
  T=1s   Worker deducts 1 credit from Pack
  T=2s   Worker starts PDF render (CPU-intensive)
  T=4s   OOM killer kills the worker process
  T=9m   BullMQ stall detection fires — job back to waiting
  T=9m   Worker restarts, dequeues same job again
  T=9m   Worker tries to deduct credit again
  T=9m   → Would charge user twice!

The Insight

The idempotency key breaks the double-processing cycle. The key pdf:deduct:{job.id} is set with NX at deduction time and lasts 24 hours. A retried job sees the key exists — credit already deducted — and skips the deduction.

// lib/queue/pdf-worker.ts
const acquired = await redis.set(`pdf:deduct:${job.id}`, "1", "EX", 86400, "NX")
if (acquired === "OK") {
  // First time — deduct
} else {
  // Retry — skip deduction, but still render and upload
}

The key's lifetime (24 hours) must exceed the job's maximum retry window. If a job fails and is retried 10 times over 6 hours, all retries see the existing key. The deduction happens exactly once.

The Key Cleanup Before Re-enqueue

When a user re-downloads after a successful first download:

1. The BullMQ job for "pdf:final:resume123" has state completed
2. removeOnComplete: { age: 3600 } — it will be removed after 1 hour
3. Within 1 hour, the user edits the resume and re-downloads
4. addPdfJob() finds the completed job still in BullMQ
5. The state machine detects: state === "completed" → delete Redis key, remove job, add new job

Step 5 is critical. Without deleting the Redis key before removing the job:

Old job is removed from BullMQ.
New job is added with the same jobId (pdf:final:resume123).
Old Redis key still exists: pdf:deduct:pdf:final:resume123
Worker runs new job → sees existing key → skips deduction → user gets free PDF!

The fix:

// lib/queue/pdf-queue.ts
if (state === "completed" || state === "failed") {
  await redis.del(`pdf:deduct:${jobId}`)  // MUST be first
  await existing.remove()                  // THEN remove BullMQ job
  // Now add a fresh job — no stale Redis key to skip deduction
}

The delete must come before the remove. If the Redis delete fails, the function throws before removing the BullMQ job. The queue is intact, the user retries, the key is eventually deleted. Reversed order: BullMQ job removed, Redis delete fails, new job added, worker skips deduction — the user gets a free PDF.

The Two-Connection Requirement

BullMQ requires two separate Redis connections: one for the queue client (add/check jobs) and one for the worker (process jobs). The worker subscribes to keyspace events and uses blocking reads (BRPOP, BLMOVE). A single shared connection would intermingle blocking commands with non-blocking commands — the blocking commands would starve the application's Redis usage.

// lib/redis/index.ts
export const redis = new IORedis(…)            // Application connection
export const bullMqRedis = new IORedis(…)      // BullMQ-only connection

Using redis for BullMQ would cause the application to hang waiting for blocking Redis responses. This is the most common gotcha when first integrating BullMQ.

Transferable Mental Model

Any async job system has two invariants to maintain: (1) every triggered job eventually completes, and (2) every side effect happens exactly once. BullMQ guarantees (1) via stall detection and retry. ScoreResume guarantees (2) via idempotency keys with a lifetime longer than the retry window. The combination of these two guarantees gives "at-least-once delivery with exactly-once side effects" — the gold standard for async job processing.

Chapter 6: The Gemini Cost Explosion — Why Spending $0.00 Today Means Nothing About Tomorrow

The Physical Reality

Token-based AI billing has a property that per-request billing does not: cost is proportional to output length, which the model controls. A model instructed to "rewrite this bullet point" can produce 20 tokens or 2000 tokens. The application's cost is the same — the user receives a response — but the billing is 100x different.

This is different from a REST API where you know the cost per request in advance. Gemini cost per request has a distribution, not a fixed value. Budget caps must account for the worst-case tail, not the expected case.

Why checkAiBudget Is Not Enough Alone

checkAiBudget reads the sum of AiUsage.costUsd for the current day. It compares against $2 per user and $20 for the platform. If the user has spent $1.80 today, and the next call produces a $0.50 response, the budget check passes ($1.80 < $2.00) but the actual spend becomes $2.30 — 15% over budget.

This is not a bug — it is an architectural choice. Preventing this exactly would require knowing the cost of a call before making it (impossible) or refusing any call when remaining budget is less than the maximum possible call cost (too conservative — would reject many valid requests).

The platform budget cap exists for this reason: even if individual user caps are exceeded by one call each, the platform cap provides a hard ceiling that prevents a single day from burning the entire month's budget.

The Cost Guard Architecture

// lib/ai/cost-guard.ts
export async function checkAiBudget(userId: string): Promise<void> {
  const todayStart = startOfDay(new Date())

  const [userSpend, platformSpend] = await prisma.$transaction([
    prisma.aiUsage.aggregate({
      where: { userId, createdAt: { gte: todayStart } },
      _sum: { costUsd: true },
    }),
    prisma.aiUsage.aggregate({
      where: { createdAt: { gte: todayStart } },
      _sum: { costUsd: true },
    }),
  ])

  const userTotal = Number(userSpend._sum.costUsd ?? 0)
  const platformTotal = Number(platformSpend._sum.costUsd ?? 0)

  if (userTotal >= USER_DAILY_CAP) {
    throw new AiBudgetExceededError("user", userTotal)
  }
  if (platformTotal >= PLATFORM_DAILY_CAP) {
    throw new AiBudgetExceededError("platform", platformTotal)
  }
}

Two aggregate queries in one transaction: user spend and platform spend. The $transaction does not mean they're atomic mutations — it means they share a snapshot (PostgreSQL's READ COMMITTED by default, or REPEATABLE READ inside a transaction). This ensures the user spend and platform spend are queried against the same point in time.

The Exponential Backoff and Retry Storm

When Gemini returns 429 (rate limited), ScoreResume retries with exponential backoff:

const delayMs = Math.min(1000 * 2 ** attempt + Math.random() * 500, 8000)

Attempt 0 → immediate
Attempt 1 → delay: 2000ms + jitter
Attempt 2 → delay: 4000ms + jitter (capped at 8000ms)
Attempt 3 → delay: 8000ms + jitter

Without jitter, all retries from concurrent requests would fire at the same time (T=2s, T=4s, T=8s), creating synchronised bursts to Gemini. Gemini responds with 429 again to all of them. The "retry storm" can persist indefinitely.

The jitter (+ Math.random() * 500) spreads retries across a 500ms window. Concurrent retries arrive at different times, reducing the probability of all hitting Gemini's rate limit simultaneously.

The cap at 8000ms prevents exponential growth from producing multi-minute delays for high attempt counts. A 3-retry strategy with 8s max delay has total wait time of at most 2+4+8 = 14 seconds — acceptable for an AI writing feature.

The Lite vs Pro Model Split

const model = tier === "starter" ? GEMINI_MODEL_LITE : GEMINI_MODEL_PRO

Cost ratio for a typical 200-token input, 150-token output bullet rewrite:

Flash is 5.4x more expensive than Flash Lite per call. At the Starter per-user cap of 5 AI uses, the difference is marginal. But if a paid user hits the rate limit ceiling (40 bullet rewrites/day), Flash costs $0.0174/day vs Flash Lite's $0.0032/day — a 5x difference per heavy user.

The model split is a business decision embedded in a technical mechanism.

Transferable Mental Model

AI billing is not like SaaS billing. With SaaS, you know the cost per action. With AI, you know the cost per token, but not the tokens per action. Budget caps on both dimensions (user and platform) prevent individual abuse (user cap) and catastrophic incidents (platform cap). The concurrency lock prevents the TOCTOU race where multiple concurrent requests all pass the budget check before any usage is logged.

Chapter 7: The Scoring Engine's Determinism — Why Randomness Is a Product Bug

The Physical Reality

A floating-point number like 0.1 + 0.2 in IEEE 754 does not equal 0.3. It equals 0.30000000000000004. This is not a bug in your language — it is a property of how binary representation works. Floating-point arithmetic is deterministic but non-obvious.

A scoring engine that uses floating-point arithmetic without care will produce different scores on different machines, different OS versions, or after innocent library updates that change the order of floating-point operations. A score of 68.00000000001 should be 68. A score that changes from 68 to 68.00000000001 on a redeploy breaks the user's trust.

What ScoreResume Does

The scoring engine works in integers or simple fractions, converting to integer at each phase boundary:

// lib/scoring/engine.ts — Phase 5, final clamp
let finalScore = Math.round(rawScore)
finalScore = Math.min(100, Math.max(0, finalScore))

Math.round() at the end, not at each intermediate step. Rounding at each step would compound rounding errors (round down 5 times = 5 points lost). Rounding once at the end gives the most accurate integer representation of the raw float.

The weights are integers (experience: 30, skills: 20, etc.). Multiplications of integers by integers stay integers. The only floats arise from percentage calculations — which are rounded at the final step.

The AI Analysis as Pure Input

// lib/scoring/engine.ts
export async function scoreResume(
  resumeData: ResumeData,
  roleSlug: string,
  countryCode: string,
  analysis?: ResumeAnalysis,  // optional — pre-computed by Gemini
): Promise<ScoreResult>

When analysis is provided, the engine uses analysis.bulletQuality[i].isQuantified instead of running the regex isUnquantified(bullet.text). Both branches of this conditional are deterministic:

• Regex is deterministic (same input → same output, always)
• The AI analysis, once computed, is a fixed data structure — deterministic within the engine

The non-determinism is in the Gemini call that produces analysis — but that happens before the engine runs. The engine receives data, not a live AI connection.

This is the key architectural decision: AI enriches inputs, it does not participate in the scoring computation itself. The same analysis object will always produce the same ScoreResult.

The Test Suite as Ground Truth

// lib/scoring/engine.test.ts (illustrative)
test("software engineer resume scores 73 against react-developer role", async () => {
  const result = await scoreResume(swEngineerResume, "react-developer", "US")
  assert.strictEqual(result.score, 73)
})

assert.strictEqual on integers. Not "approximately 73" — exactly 73. If a rule change in the engine causes this resume to score 72 or 74, the test fails. The developer must consciously decide to update the expected score — which forces the question: "did I mean to change the scoring behaviour?"

Scoring tests are the most important tests in the codebase because scoring bugs are silent. A broken rate limiter returns 429. A broken auth check returns 401. A broken scoring rule returns 200 with a wrong number that looks correct.

Transferable Mental Model

Determinism is a product feature. Any computation that users depend on for fairness — grades, scores, prices, recommendations — must be deterministic. The mechanism: separate the non-deterministic part (AI inference, random seeds) from the deterministic part (calculation). Pass non-deterministic outputs as inputs to the deterministic computation. Test the deterministic computation with exact assertions, not ranges.

Chapter 8: Prisma vs Raw SQL — When the ORM Becomes the Problem

The Physical Reality

Prisma generates SQL. The SQL it generates is correct and safe, but it may not be optimal. prisma.pack.updateMany({ where: X, data: Y }) generates a UPDATE ... WHERE X, which is exactly what Chapter 1 used for atomic credit deduction. But prisma.$queryRaw drops you to raw SQL when Prisma's query builder cannot express what you need.

When Prisma Is the Right Tool

• CRUD operations: create, update, findUnique, findMany
• Simple aggregations: aggregate({ _sum: { costUsd: true } })
• Transactions: prisma.$transaction([...])
• Anything expressible in the Prisma query DSL

Prisma handles connection pooling (via PgBouncer), query safety (parameterized queries by default — no SQL injection), and TypeScript types for results. For 95% of ScoreResume's queries, Prisma is the right tool.

When Raw SQL Is Better

• Window functions: ROW_NUMBER() OVER (PARTITION BY userId ORDER BY createdAt)
• Recursive CTEs: hierarchical data queries
• Conflict clauses: ON CONFLICT DO NOTHING (Prisma 7 adds limited support, but raw SQL is clearer)
• Query hints: index hints, parallel query control

ScoreResume uses prisma.$queryRaw for the orphan pack cleanup — which needs a conditional WHERE clause combining multiple pack states in a way that Prisma's type system doesn't express cleanly in one query.

The N+1 Problem

A common Prisma mistake: querying inside a loop.

// DANGEROUS — N+1 queries
const users = await prisma.user.findMany()
for (const user of users) {
  const packs = await prisma.pack.findMany({ where: { userId: user.id } })
  // This fires one query per user!
}

// CORRECT — 1+1 queries
const users = await prisma.user.findMany({
  include: { packs: true },  // JOIN in one query
})

Prisma's include generates a JOIN (or a batched SELECT WHERE IN (...) for many-to-many). The N+1 problem is a performance cliff — 10 users is fine, 1000 users is 1001 database round-trips.

ScoreResume's scoring route fetches resume, user, and active pack in a single query with include: { packs: { where: { ... } } } — one round-trip.

The Prisma 7 Config Split

Prisma 7 moved the database URL out of schema.prisma. This has a physical consequence: schema.prisma is a static schema definition, parsed at build time by the Prisma code generator. The database URL is a runtime secret, loaded from environment variables at application start.

Mixing them (as Prisma 6 did) meant the schema file had to know about environment variable names — coupling schema definition to runtime configuration. The split is a clean separation: the schema defines what the database looks like, the config defines where it is.

PgBouncer and Connection Pool Math

PostgreSQL has a max_connections limit (default 100 on most servers). Each PM2 cluster instance (Next.js uses all CPU cores, so 4 instances on a 4-core server) maintains a Prisma connection pool. Prisma defaults to min: 2, max: 10 connections per instance.

4 instances × 10 connections = 40 connections. With 2 applications on the VPS (AttemptIQ + ScoreResume), that's up to 80 connections to PostgreSQL — getting close to the limit. During a load test (50 VUs sustained), this hit 630 × 502 errors. The root cause: connection pool exhaustion.

PgBouncer sits between the applications and PostgreSQL. It multiplexes application connections onto a smaller number of server connections. Application connections: 80. PgBouncer server connections: 20 (2×10, one pool per application). PostgreSQL sees only 20 connections regardless of how many PM2 instances are running.

App (80 connections) → PgBouncer (20 server connections) → PostgreSQL

PgBouncer operates in transaction mode: a server connection is borrowed from the pool only while a transaction is active, and returned immediately after COMMIT/ROLLBACK. This is the most efficient mode — Prisma's long-lived idle connections no longer hold PostgreSQL connections.

Epilogue: The Ground Beneath the Code

This book has been about what lies beneath the patterns.

The atomic updateMany with a WHERE guard is not Prisma magic — it is a single SQL statement that holds a row lock during evaluation. The SET NX lock is not Redis magic — it is a single-threaded Redis processing model that prevents race conditions by serializing commands. The BullMQ idempotency key is not framework magic — it is a TTL-keyed Redis entry that encodes "this side effect has happened" across process restarts.

Every pattern in ScoreResume exists because the physical reality of concurrent execution, network unreliability, and process failure makes simpler approaches incorrect. Understanding the physics makes the patterns obvious, not clever.

The next time you encounter a new problem, ask: what is the physical reality of this environment? What can go wrong between two steps? What atomic primitive does this system provide? The answer will lead you to the correct pattern, even before you have seen it before.

All code in this book reflects the actual ScoreResume implementation. Physical constants (Gemini pricing, Redis TTLs, BullMQ settings) are from the live codebase and may change.