The Ticket Nobody Wants

A plumber comes to your house because something under the sink is dripping. He looks at it, taps a few things, listens, then reaches in and tightens one nut. The dripping stops. He is there for forty minutes and he charges you for all of them, and some small part of you thinks: but you only turned one nut. That took ten seconds.

And he could say, quite fairly: you are not paying me to turn the nut. You are paying me to know which nut.

I think about that a lot, because software is exactly the same and we pretend it is not. The fix is almost never the expensive part. Working out what the fix is, that is the expensive part. And here is the strange bit, the bit I want to sit with you on for a while: when we finish a piece of work, we keep the nut, and we throw away the knowing. Every time. On purpose. And nobody thinks it is odd.

Let me show you what I mean with a real bug. I will hand it to you the way it was handed to me, we will fix it together, and then we will add up what it actually cost. Stay with me, because the ending is the part that changed how I think about all this. 🙂

Here is the ticket

A bug, of the kind that lands on a Monday. Tickets for an experience (a tour, a museum, that sort of thing) can be uploaded in bulk by the supplier, and something about that upload is going wrong. The ticket asks for three things:

1. Put a limit on how many tickets can be inserted at once, say twenty thousand, so we stop overloading the database.

2. Add proper error handling to the afterCreate hook, so failures get surfaced instead of quietly swallowed.

3. Add a rollback, so that if the insert fails halfway, the half that landed gets cleaned up.

Read it like you would on a Monday morning. Three requirements, all specific, all sensible. You can already picture the diff: a constant, a check, a try/catch, a transaction. An hour of work, maybe two.

Go on then. Fix it.

The two minute fix

Requirement two looks easiest, so start there. Find the hook.

// models/tickets.js
Tickets.afterCreate(async (ticket) => {
  if (![TICKET_TYPE_A, TICKET_TYPE_B].includes(ticket.type)) return
  ticket.serialNo = generateSerialNumber(ticket.id)
  await ticket.save({ hooks: false })     // no try/catch. that is the bug.
})

Six lines. The fix is obvious, you wrap it, you go to lunch.

But before you do, ask a question nobody in the ticket asked: why does this hook exist at all? Why is the serial number written afterwards, in a second pass, instead of just being set when the row is created?

Look at the line: the serial number is generated from the ticket's id. And the id is handed out by the database, at the moment of insert. So you cannot know the serial number until the row already exists, which means you cannot write it at the same time as the row.

Which means giving a ticket its serial number takes two trips to the database, not one. Hold on to that. It comes back.

Ten seconds that cost me a day

The other two requirements are about the bulk insert, so find it.

// routes/handlers.js
const tickets = await Tickets.bulkCreate(rows, { individualHooks: true })

There it is, and there is no transaction, exactly like the ticket says. So wrap it in one, add the limit above it, and you are done. Forty minutes in, all three requirements, home in time for tea.

Except.

What does individualHooks: true actually do?

You know what it does. I knew what it did. Everybody knows what it does: it inserts the rows in bulk, and it also runs the per row hooks. That is the only thing those two words can possibly mean.

It is worth ten seconds to check. So I opened the library.

// node_modules/sequelize/lib/model.js, inside bulkCreate
if (options.individualHooks) {
  await Promise.all(instances.map(async (instance) => {
    await instance.save(individualOptions)   // one INSERT. each.
  }))
} else {
  const results = await model.queryInterface.bulkInsert(...)   // the actual bulk insert
}

Read that twice, because it says the opposite of what the name says.

The real bulk insert, the single fat SQL statement you assumed was happening, is in the else. Turning on individualHooks does not add hooks to the bulk insert. It means you never reach the bulk insert at all. It quietly swaps it for a loop, and fires the whole loop off at once.

So when a supplier clicked upload once, with 34,329 tickets in the file, that was not one insert. That was 34,329 separate inserts. And remember the hook from a minute ago? It fires on every single one of them, and each one does an update.

34,329 x 2 = 68,658 queries, all leaving at the same moment.

And now you are nowhere near the bug you were sent to fix, and you cannot stop, because those 68,658 queries have to get to the database somehow.

Five doors

They get there through a connection pool. So how big is the pool? Find where the database handle is built.

// db/index.js
const sequelize = new Sequelize(config.postgres.url, config.postgres.options)

The answer is one file further on, and this is the step where you either solve this or you do not. Open the config and look at what options actually contains.

// config.js
options: {
  dialect: 'postgres',
  logging: loggerForPostgres(isProd)
}                          // that is it. no pool. anywhere. in any environment.

There is no pool configured. Not here, not in staging, not in production. So the library's own default quietly takes over, and that default does not live in our code, it lives inside the library:

// sequelize/lib/dialects/abstract/connection-manager.js
config.pool = defaults(config.pool || {}, {
  max: 5,           // five connections. that is the doorway.
  acquire: 60000    // wait 60 seconds for one, then throw.
})

Sixty eight thousand queries. Five doors. And a query that has waited sixty seconds does not wait politely any longer. It throws.

The arithmetic writes itself

Now you have everything you need, and you can do the sum on the back of an envelope. The last query in the queue waits about 2N x latency / 5. Guess ten milliseconds for a round trip, and check it against the real uploads from that morning, which were sitting in the investigation ticket all along:

Your little model, built entirely out of things you read rather than things you guessed, lands exactly on the boundary the real world found. It even explains the line in the ticket that says this happens "sometimes, not every time". Of course it does. The limit is not a number of rows, it is time spent waiting, and the same upload can sail through at 3am and fall over at noon when the database is busy.

So what happened on the day? The queue overflowed, and some queries threw. Not the last ones, not any tidy subset. Arbitrary ones. And because there was no transaction, every insert that had already landed stayed committed, while an arbitrary handful of the updates, the ones that write the serial number, simply never ran.

About two hundred tickets went out into the world with no serial number on them. People bought them, tapped "view my ticket", and got a blank screen.

Now go back and read requirement one

Cap the upload at twenty thousand.

Where did that number come from? Look at the real data again. 2,324 passed. 7,500 passed. 2,100 passed. 34,329 failed.

Nobody has ever uploaded twenty thousand tickets. Not once, in either direction. It is the midpoint of a gap that nobody has ever tested. And by the arithmetic you just did on the back of that envelope, forty thousand queries through five doors is about eighty seconds, and the doors give up at sixty, so twenty thousand is probably on the wrong side of the cliff anyway.

And it gets better. Requirement three says wrap it all in a transaction. But a Postgres transaction is pinned to a single connection. So wrapping the whole fan out in a transaction does not spread those forty thousand round trips across five doors. It funnels every last one of them through one.

Sit with that for a second, because it is genuinely funny. Requirement one sets a limit of twenty thousand. Requirement three then guarantees that twenty thousand can never finish. Do exactly what the ticket asks, ship it, and you will have traded an occasional data corruption for a guaranteed seven minute timeout. 😅

And you could not possibly have known that on Monday morning. You could only know it by opening nine files, one of which was inside a library, one of which was a lockfile, and by doing a piece of arithmetic that nobody asked you for.

Add up what that cost

Count what you actually had to open to get here:

1. The model, where the hook lives and writes the serial number in a second pass.

2. The helper, because the serial number comes from the id, and the database assigns the id.

3. The handler, the call site, with individualHooks: true and no transaction.

4. The library, where that flag turns the bulk insert off.

5. The database setup, where the handle is built from the config.

6. The config, which contains no pool at all.

7. The library again, for the default: five connections, sixty seconds.

8. The lockfile, because which version of the library is any of this even true for?

9. The investigation ticket's own table: 7,500 passed, 34,329 failed, twenty thousand never tried.

Nine files. Not one of them named in the ticket. Every one of them load bearing.

That was a day. And then you write the fix, which is a constant, a guard, a try/catch and a transaction. Forty lines. About an hour.

One hour of writing. One day of finding out what to write. And the finding out is the only part that produced anything you did not already know.

And now the strange bit: you throw the day away

Open the pull request. It has forty lines in it.

It does not have the nine files in it. It does not have the moment in the library where the flag turned out to mean the opposite. It does not have the envelope arithmetic, or the discovery that the ticket's own cure was worse than the disease, or the fact that twenty thousand was a number somebody made up.

All of that lived in exactly one place, which was your head, and by Friday it will be gone from there too.

Six months later another ticket about ticket uploads arrives. A different engineer picks it up, opens the same handler, sees the same friendly looking flag, and starts, from zero, to buy the same day all over again at full price.

We do this constantly. We have a name for it. We call it "getting up to speed", we put it in the estimate, and nobody thinks it is strange.

The fix is the cheap part. The expensive part is knowing which fix, and we throw that away every single time.

So what would you actually want a machine to do?

Here is where I landed, and it is not where I expected to land.

You do not want the machine to write the forty lines. You just watched yourself write them. They were an hour, they were mechanical, and honestly any competent engineer or any decent model can produce them.

You want the day. You want something that opens the nine files, reads the library at the exact version the lockfile pins, does the arithmetic, notices that twenty thousand is a number nobody ever measured, and then writes all of it down somewhere the next person will actually find it, with citations they can go and check for themselves.

That is the thing I have been building. It is not a code generator. It is a machine for working out how a system really behaves and then refusing to forget it. The code it writes is a by product, and honestly it is the least valuable thing it produces.

One last thing, and it is the reason for everything that follows

We gave this exact ticket to the agent.

It came back with a beautiful document. Sequence diagrams. An impact analysis table. Risk ratings. Eight numbered open questions. It looked like the best spec anyone on the team had written all quarter.

It kept individualHooks: true, and wrapped it in a transaction.

It had read the handler. It had not read the library, because nothing in its toolset let it open a dependency's source code. So it did exactly what you and I nearly did an hour ago: it worked out what a flag does by reading the flag's name.

You caught that because I told you to spend ten seconds checking. It had nobody to tell it.

And here is the part that genuinely worries me. When you are unsure, you hedge. You write "I think", you add a caveat, and a reviewer can hear the wobble in your voice. When a language model is unsure, it produces exactly the same fluent, confident, diagram bearing document as when it is certain. There is no wobble. There is no tell. And the more polished the thing looks, the more reviewers it sails straight past.

Which means an agent that reconstructs a system badly is worse than no agent at all.

So everything that comes next in this series, all the tools and the vetoes and the gates and the guardrails and the transcript and the evals, is machinery in service of one goal. Not making the thing smarter. Making it correctable.

And we start at the very bottom, with a model that cannot even open a file. 🙂

It is the glory of God to conceal a thing: but the honour of kings is to search out a matter. - Proverbs 25:2