Data and failure

A program whose job is to keep data safe and find it again quickly, while many other programs read and change it at once. Your app asks it questions over a connection. If your app crashes, or the database itself does, what it said it saved is still there.

Agents reach for the nearest thing that works: a JSON file, an array, a local SQLite file. All of those pass a demo with one user. The difference only appears with two copies of your server, two requests at once, or a crash. So choosing the store is your decision, not a detail to leave to the agent.

A table is a named collection of records that all have the same fields, like a strict spreadsheet. A row is one record, such as one order. A column is one field, such as its total, and every value in it is the same kind of thing.

Agents copy the shape of the objects in their code straight into tables, often one big table with a text column full of JSON. It runs, and then it can't be searched, checked or joined. Describing the tables yourself is one of the cheapest ways to steer the whole design.

The declared shape of your data: which tables exist, which columns they have, what type each holds, and the rules each must obey, such as not empty, or never below zero. The database enforces those rules on every write, whoever is writing.

A rule in your code protects the one path that has it. An agent adding a new route, a script or a backfill won't know the rule is there. A constraint in the schema holds against all of them, including the agent itself. It is backpressure that lives in the data.

The column that identifies one row and only one, for ever. It must be present and it must be unique. Usually it's a number or a random id with no meaning, because anything with meaning, like an email address, can change.

An agent asked to model users may reach for the email as the key, which works until someone changes their email and every link to them breaks. It may also pick counting numbers for public URLs, which tells anyone how many orders you have and lets them guess the next one. Say what you want.

A column in one table that must match the primary key of a row in another. An order's customer_id has to point at a real customer. The database refuses anything that would leave it pointing at nothing, and you choose what happens when the row it points at is deleted.

Without one, nothing stops an order pointing at a customer who no longer exists, and an agent writing a clean-up script will happily create thousands of them. With one, choose the delete rule yourself. An agent adding CASCADE to fix an error has decided that deleting one customer may silently delete their whole history.

A rule that no two rows may have the same value in a column, or in a set of columns. The database checks it as each row goes in, so it holds even when two people try in the same millisecond. The loser gets an error.

Asked to stop duplicate signups, an agent writes the obvious thing: look for the email, and insert if it isn't there. That passes every test, because tests run one at a time. In production, two requests can both look before either inserts. The only check that can't be raced is the one inside the database.

The language for asking a relational database for things. You describe what you want, such as which rows, in what order and how many, and the database works out how to get it. SELECT reads. INSERT, UPDATE and DELETE change. It looks like English, and small mistakes in it can be enormous.

You don't need to write much SQL when an agent writes it for you. You do need to read what it wrote, especially the part after WHERE, because an UPDATE or a DELETE with the wrong condition, or with none, runs without complaint. Ask to see the statement before it runs against anything you care about.

The steps the database chose to answer your query: which table to read first, whether to read all of it or use a shortcut, and how to join. You write what you want, and a part of the database called the planner decides how. EXPLAIN shows you its decision.

An agent asked why a page is slow will guess, usually by rewriting code that isn't the problem. The plan isn't a guess. And it can change by itself as a table grows, so a query that was fine on your laptop's 100 rows can be a different query on production's 10 million.

A separate sorted structure that lets the database find rows without reading the whole table, like the index at the back of a book. The usual kind is a B-tree, a wide shallow tree you can walk from top to bottom in a few steps, however many rows there are.

On your laptop with 100 rows, a missing index is invisible, so no test or demo will show it. Agents rarely add one unless asked. But they also over-add when asked to make things fast, and every index is more work on every write. Ask for the index a real query needs, and for the plan that proves it gets used.

One query to fetch a list, then one more for each item in it. A page of 50 orders that looks up each order's customer separately makes 51 trips to the database. Each is fast, and the page is slow. One query can usually fetch them all.

This is the signature performance bug of agent-written code, because the code reads perfectly: loop over the orders, show each customer's name. It passes with 3 rows in the test database. Only a real list shows it, and only if something is counting queries.

A library that lets you work with database rows as objects in your language, and writes the SQL for you. Prisma, Drizzle, Django and Active Record have one. It's convenient, and it hides how many queries a single innocent line of code will run.

An agent writes ORM code fluently, and the ORM makes the expensive thing look exactly like the cheap thing: order.customer.name might be a field you already have, or a trip to the database. You can't tell from the diff. So turn on query logging and read the SQL it really sends.

A group of database changes that succeed or fail as one. You BEGIN, make the changes, then COMMIT. If anything fails before the COMMIT, or the power goes, none of them happened. Moving money is the classic case: the debit and the credit must both happen, or neither.

Agents write each statement correctly and often don't wrap them, because every test passes without it. Tests don't crash between two lines. Production does, on a deploy, an out-of-memory kill or a lost connection. Look at any function with two writes in it and ask what happens if it dies between them.

Four promises a transaction makes. Atomic: all of it or none. Consistent: your rules still hold afterwards. Isolated: others don't see it half-done. Durable: once it's committed it stays, through a crash. It's a word you'll hear in interviews, and each letter is something you can break.

Agents will pick a data store for you, and some of the popular ones give up one of these letters by default or by configuration. Knowing the four lets you ask a real question: what happens to this write if the machine dies a millisecond after you told the user it worked?

How much one transaction can see of others running at the same moment. Stronger levels hide more, and may refuse a transaction and make it retry. Weaker levels let strange things through. Dirty read, non-repeatable read and lost update are the names of those strange things.

Every isolation bug needs two transactions at once, so your tests will never hit one, and the agent can't reason its way to one unless you ask. And the default may not be what you assume. Say which level a piece of code relies on, and if you go stronger, say what retries.

A bug where the result depends on who gets there first. Two people buy the last ticket: both read one left, both subtract one, both write zero. Two sales, one ticket. Each step was right, and the bug was the order they ran in.

An agent's code is usually tested by running one thing at a time, which is the one order that never goes wrong. So a race can sit in green code for months, and show up only when traffic grows. You have to ask for it by name, and for a test that really runs things at once.

Three ways out of a race. An atomic update does the maths inside one statement: SET stock = stock - 1. A lock makes others wait: SELECT ... FOR UPDATE. A version check lets everyone try and makes the loser start again. Try them in that order.

Asked to fix a race, an agent often adds a lock inside the program, which works perfectly until you run two copies of the server. The fix has to live where the shared data lives. And the simplest fix is often just moving the arithmetic into the UPDATE.

Two transactions each hold something the other wants, so neither can move. One locked row A and wants B, and the other locked B and wants A. They would wait for ever, so the database notices the cycle, kills one of them, and that one has to try again.

Locks fix races and make deadlocks possible, and an agent fixing the first won't think about the second. They appear only under real concurrency, as an occasional error nobody can reproduce. The fix is boring: take locks in the same order everywhere, and make sure anything that can be killed can retry.

A script that changes the structure of a live database, such as adding a column, and is kept in your repo so that every copy can run it in order. The hard part is that, for a while, old code and new code are running against it at once.

An agent treats a migration as one edit next to another, and on a laptop it is. In production the deploy isn't instant, so the old code meets the new schema, or the new code meets the old one. A migration that looks like one tidy line can also lock a busy table while it runs. Neither shows up in tests.

Making a breaking change in safe steps. Add the new thing alongside the old, have the code write to both, copy the old data over, move reads across, stop using the old, and only then remove it. At each step the old and the new code both work.

Told to rename a column, an agent will rename the column. Told "use expand and contract, as separate pull requests", it will produce a sequence you can pause and reverse. It feels slow, and it's the difference between a dull week and an outage. Agents make the tedious version cheap, so there's little excuse left.

A backup is a copy of your data, kept somewhere a mistake can't reach it. A restore is getting it back. Until you have really done a restore, you don't know the backup works, and a replica doesn't count, because it copies your mistakes too.

An agent will tell you backups are configured, because a setting says so. That is not evidence that you can get the data back, how long it takes, or that the backup isn't kept in the same blast radius as the thing it protects. Ask it to restore one and count the rows.

When some of the work happened and some didn't, and you may not be able to tell which. On one machine, a thing either ran or it crashed. Across a network, you sent a request and heard nothing: it might have failed, or it might have worked and the reply got lost.

Agents write the happy path as a straight line: charge the card, save the order, send the email. Each line is correct, and a crash between any two leaves a state nobody designed. Ask what happens at every gap. Wrapping it all in a database transaction doesn't help, because a rollback can't uncharge a card.

An operation is idempotent if doing it twice has the same effect as doing it once. A client makes that true of a payment by sending a unique key with the request. The server saves the first result against that key, and a repeat gets that result back and charges nothing.

Agents add retries readily, since retries look like robustness, and a retry on a charge is how a customer pays twice. It doesn't appear in tests, because the test network never loses a reply. Done properly, this is the whole sentence: in one transaction, insert the key under a unique constraint, and only the winner does the work.

Trying a failed call again, and waiting longer each time. Backoff is the growing wait. Jitter is making each wait random, so that clients which failed together don't all come back together. Without those, retries can keep a struggling server down.

Ask an agent to make something robust and you get a retry loop, often with a fixed delay, at every level. It looks responsible and passes tests. Under a real failure, every client turns one request into several at exactly the moment the server can't cope. Ask for a limit, backoff and jitter, and only on what is safe to repeat.

What a message system promises. At most once: it may be lost, and it never repeats. At least once: it won't be lost, and it may arrive twice. Most systems you use give you at least once, so your code has to cope with seeing the same thing again.

Webhook handlers and queue workers are written by agents as if each message came once, in order, because that's how a test sends them. In production that assumption is simply false, and it's written in the docs of the service you're using, which the agent didn't read. This is the trap in our experiment.

Messages can arrive in a different order from the one they were sent in, because a retry of an old one can land after a newer one. If your code treats the latest to arrive as the latest to happen, a late "paid" event can undo a refund.

This is the behaviour nobody asks for, so nobody gets it. In our experiment, an agent told to handle payment webhooks passed the hidden scenarios 27% of the time, and 33% with types, lint, tests and mutation testing all behind it. Told that events arrive at least once and out of order, it passed 100%. One small model and one-function tasks, so read the shape and not the size.

A timeout limits one call. A deadline limits the whole job: the moment by which the answer stops being any use, handed down through every call that serves it, so nobody keeps working for a user who has gone.

The network chapter said to set a timeout. An agent then sets one per call, each a round number, and retries inside each. The sum can be far longer than anyone will wait, and the work carries on long after the page has given up. Timeouts are a chain, so they have to be set as one.

A switch around a call to something that might fail. Count the failures, and past a limit stop calling it for a while and fail at once. After that, let one trial call through. If it works, close again. Named after the thing in your fuse box.

A timeout means that every request waits the full time before failing, and when the thing you depend on is down, that's all your capacity waiting. A breaker turns slow failure into fast failure. Agents won't add one unasked, and when they do it deserves a test, since a breaker that's never been open is one you're only hoping works.

A list of jobs waiting to be done, usually run by a separate service. One program adds a message, and a worker takes it later and deletes it when it's finished. The caller doesn't wait, and a spike of work waits in the queue and not on your server.

Told to send emails on signup, an agent writes the call inline, and now a slow mail server makes signup slow. Told to use a queue, it may not think about a worker dying halfway, a job that always fails, or a queue growing faster than it drains. Those are the questions to ask.

Doing less when part of the system is unwell, in place of falling over. Hide the recommendations, serve a slightly old list, turn off the search, and keep the page up. You decide in advance what the product can live without, and what it can't.

An agent's default is to wait for everything and then render, so one optional panel can take down your whole checkout. You have to name which parts are optional. And you have to name what must never be faked: a stale recommendation is fine, a stale price at the moment of paying is not.

Computers store most decimals in binary, which can't hold 0.1 exactly, so sums drift. A float is fine for measuring distance, and wrong for money. Store money as a whole number of the smallest unit, such as pence, or use a decimal type, and decide where rounding happens.

An agent will write price * 1.2 in a float if nothing stops it, and the test passes, because the test checks one amount that happens to round well. The bug appears as a total that's a penny out on one invoice in a thousand. Say integers, say which unit, and say who gets the odd penny.

An instant is one point in time, everywhere. What a clock on the wall shows depends on where it is and on that place's current rules. Store instants in UTC and convert when you display. A time in the future, like a meeting, is different: store the wall-clock time and the place.

Tests run in one time zone, on a normal day. So an agent's date code passes, and then a birthday shows up a day early in New York, or a daily job runs an hour off for half the year. Two days a year, some wall-clock times exist twice or not at all. Ask what happens on those days.

Keeping copies of a database on other machines. One copy, the leader, takes the writes and sends them to the followers, which can serve reads. Usually it sends them after telling the client yes, so a follower is a little behind. That gap is called replica lag.

An agent asked to scale reads will send them to a replica, and it works in every test, where there's no lag. In production someone saves their profile, the page reloads from a replica, and the old value comes back. And if the leader dies, writes that had been confirmed but not yet copied are gone.

A promise that all copies will agree if you stop changing things and wait. Until then, different readers can see different answers. It's a fair trade for speed and uptime, as long as the product was designed for it and it isn't a surprise.

An agent writes code that reads back what it just wrote, because that always works locally. Against an eventually consistent store, that code is wrong some of the time. And when two copies took different writes, something has to pick. The default is often last write wins, which means one person's change is silently thrown away.

When a network splits your copies of the data apart, you have to choose. Either refuse some requests, so nobody gets a wrong answer, or answer them all, and accept that some answers are out of date. You can't have both while the split lasts.

It's a staple of interviews, and an agent can recite it confidently, including the slogan that's wrong. What matters is the question it forces on you: for this one piece of data, during an outage, would you rather show an error or show something stale? A bank balance and a like count have different answers.

Anything that can be tied to a real person: a name, an email address, a location, an account number. It carries legal duties in much of the world. It gets everywhere, into logs, backups, test databases and, now, an agent's context, which may leave your machine.

Agents log whole request bodies when debugging, seed test databases by copying production, and paste rows into a prompt to reason about them. All of it is helpful, and each one moves personal data somewhere you didn't decide on. A scrubber only catches what has a shape, so name the fields that may leave, and keep the rest out.

An agent with production credentials can delete data as fast as it can type. So keep credentials it can read away from production, give it a read-only role when it needs to look, and put destructive steps behind something it can't talk its way past. A sentence in its instructions doesn't count.

An agent stuck on a problem looks around for a way through, and a token in a file is a way through. It doesn't feel the difference between staging and production, and it may only find out which one it was in by reading the error afterwards. Instructions are guides, and it can read them and do the other thing.