L3 · Data and SQL
Every function is correct on its own. The bug is in what one hands to the next: the query, the cache, the contract.
The story
A B2B analytics product adds caching to its slowest page, the monthly revenue report. The change is small and well tested: the report function checks an in-memory cache first, keyed by month.
Two days later a customer emails support a screenshot of another company's revenue. The first tenant to open October's report after each deploy filled the cache, and every other tenant who opened October within the hour got that tenant's numbers.
Every piece was right in isolation. The report query filtered by tenant. The cache stored and returned values correctly. The tests checked one tenant at a time. The bug was in the join between two correct pieces: the cache key didn't include everything the result depended on.
That's what L3 bugs look like. You can't see them by reading one function. You have to follow the data from where it enters to where it's stored and back, and check each hand-off.
Part 1: Follow the data
Pick one piece of data (an order, an amount, an email address) and trace its journey. At each hop, ask the question for that hop:
The three most productive questions in this chapter:
- Is every multi-step write in one transaction?
- Is every rule enforced by the database, or only by the app? App checks race and get bypassed. Constraints don't.
- Does every query, cache key and export include the tenant and exclude deleted rows?
Drill 1: Uniqueness and transactions
Part 2: SQL semantics that bite
These are true in Postgres and most SQL databases, and each one makes a correct-looking query return the wrong rows:
| Looks right | Actually | Write instead |
|---|---|---|
WHERE deleted_at = NULL | Never true: comparing with NULL gives NULL | WHERE deleted_at IS NULL |
WHERE id NOT IN (SELECT customer_id …) | Returns nothing if the subquery contains a NULL | NOT EXISTS (…) |
purchases / sends | Integer division: 3 / 10 = 0 | purchases::numeric / NULLIF(sends, 0) |
ORDER BY created_at for paging | Rows with equal timestamps have no fixed order | ORDER BY created_at, id |
LIMIT 50 OFFSET 1000 | Rows shift when data changes; deep pages scan and discard | Keyset: WHERE (created_at, id) < ($1, $2) |
current_date | The database server's date and time zone | Compute boundaries in the business's zone |
timestamp | Wall-clock time with no zone attached | timestamptz |
| A foreign key | Not indexed automatically in Postgres | Add the index yourself |
count(*) from node-postgres | Returned as a string (it's a bigint) | Number(count) or count(*)::int |
Drill 2: Report queries
Drill 3: Pagination and N+1
Part 3: Tenancy and soft deletes
In a multi-tenant app, every query, join, count, export, search index and cache key must include the tenant. In an app with soft deletes, every one of them must decide what to do with deleted rows. It only takes one forgotten query.
Defences that don't rely on remembering:
- Row-level security in Postgres, so the database adds the tenant filter for you.
- A repository layer where the tenant is a required argument of every method.
- Views that exclude deleted rows, and a rule that application code reads from the views.
Drill 4: Tenancy and deletes
Part 4: Money rules
Money bugs are L3 because the rules live in more than one place: the cart, the invoice, the payment provider, the accounting export. They have to agree to the paisa.
- Store integer minor units (paise) everywhere. Convert only for display.
- Decide rounding once: per line or on the total, and in which direction. Then use the same rule in every system.
- Validate quantities and amounts at the boundary: positive, integer, within limits.
- Coupons and discounts need server-side rules: which can stack, how many times each can be used, what they apply to.
- Never add amounts in different currencies.
Drill 5: Checkout totals
Part 5: Contracts between parts
Every boundary between two pieces of code is a contract: the shape, the types, the units, the status codes. Bugs appear when the two sides disagree, and nothing checks.
| Boundary | What drifts |
|---|---|
| Client ↔ API | Field names, optional vs required, null vs missing, status codes |
| Your code ↔ another API | fetch resolving on errors, units (seconds vs ms), pagination style |
| JSON | Date becomes a string, BigInt throws, undefined disappears, decimals become floats |
| Queue messages | Old consumers reading new message shapes, and the reverse |
Validate at every boundary with a schema, so a mismatch fails loudly at the edge instead of quietly in the middle.
Drill 6: Client and server
Part 6: Caching
A cache is a second copy of the data, with its own key, lifetime and update rules. Each one can be wrong independently of the source:
- Key: does it include every input that changes the result: tenant, user, permissions, locale, filters, version?
- What's cached: are errors, empty results or partial results cached as if they were real?
- Lifetime: is the TTL in the unit the library expects? Is there a size limit?
- Invalidation: does every write path update or delete the cached copy?
Drill 7: A report cache
Part 7: The schema is part of the code
Review the schema and migrations with the same care as the code. The database is where rules can be guaranteed: a constraint can't be forgotten by one code path.
Drill 8: Schema review
Cheat sheet
| Check | Catches |
|---|---|
| Multi-step write → one transaction? | Orphans, half-created records |
| Rule enforced by a constraint? | Duplicates, invalid states, races past app checks |
IS NULL, NOT EXISTS, numeric division | Silent wrong results |
Stable ORDER BY + keyset pagination | Repeated or missing rows |
| Query inside a loop? | N+1 |
| Tenant on every query, join, count, cache key, export? | Cross-tenant leaks |
| Deleted rows filtered in every read? | Ghost rows and wrong counts |
| One rounding rule, integer paise, validated quantities | Money mismatches and abuse |
res.ok, 204, units, dates after JSON | Contract mismatches |
| Cache key complete, failures not cached, TTL unit, invalidation | Stale or leaked data |
timestamptz, FK indexes, CHECK, no surprise cascades | Schema-level bugs |
Say it in the interview
"The report cache key is
revenue:monthwith no tenant, so the first tenant to load a month fills it for everyone: a cross-tenant data leak, which I'd rank first. The fix is the tenant in the key; the prevention is a cache helper that builds keys from the same arguments as the query, and row-level security underneath so a forgotten filter can't leak at the database level either."
Go deeper
- L1–L2 · Logic and TypeScript
- SD 1 · Query Optimization & EXPLAIN: why the missing index matters.
- SD 7 · Caching and Invalidation: the cache patterns in full.
- Debugging · Inside the Database: finding these from symptoms.
- Next: L4 · Concurrency and Time.