query: there's something in your database and it's been there since 2017

I'm writing a query, nothing fancy. I need a list of users who signed up in a certain window, and I'm poking around the database to see where things live. And I find a table that I don't really recognize. user_archive_old_v2.

Now, every part of that name is just a giant warning. Archive means somebody meant to stop using it. Old means somebody already thought of it as the past. And v2 means there was a v1, and something went wrong with it badly enough that we needed a second one.

I run the count. 2 million rows. I check the most recent row. Written to this morning.

I ask the team, "Hey, what's user_archive_old_v2?" Nobody knows. A couple of people have seen it. Nobody's queried it on purpose. Then I go ask the senior engineer. They've been there the longest. If anyone knows, they know. And they say, "Don't touch it."

I say, "Okay, but what does it do?"

"I don't know. But once, a few years ago, somebody touched it, and something broke, and they put it back. And nobody's touched it since."

The oldest row in that table is from 2017.

Welcome back to Chaotic Commits. I'm Joanne Skiles. I'm an engineer, I teach computer science, I advise companies on how their AI systems are actually designed, and my research is on transparent systems. Which in practice means I'm the person in the room who keeps asking "where does this data actually come from?" long after it stopped being a fun question.

It's October, and this month we're spending time with the things living inside our systems. The stuff we didn't build, can't fully see, and are a little afraid to touch. This one is about the basement.

And just a quick note: the table and the story are a composite. I have seen some version of it at more than one company, so I've changed the name and the details. But I'm gonna be honest with you. If you have a production database that's more than five years old, you probably have something like this. You might have several. They're down there right now, getting written to.

But here's the argument. Data outlasts code. You can rewrite a service. You can't always rewrite the database. The rows from 2017 are still there, and something you can't see is still adding to them. The longer nobody goes into the basement, the more the basement costs.

So we're going to do this as one investigation. One table, start to finish. What's down there? What's writing to it? What's it costing? And how do you find out which parts of it you can actually trust? So let's get ready to go down the stairs.

First thing I do is look at the table structure, the columns. And it looks almost like the users table. Same ID column, email, name, address, phone, a created_at, a bunch of preference fields. Plus one extra column at the end: archived_at.

It's a copy of users. Or it was, at some point. Then I put the two side by side. The real users table has gained 23 columns since 2017. A deleted_at, marketing consent fields, a time zone, something called new_onboarding_flag that, honestly, I'm not touching today. The archive table has none of them. It's frozen. It has the exact shape of the users table on one particular day in 2017.

This is something I love about databases, in a slightly, probably unhealthy way. The schema is an archaeological record. You can read a company's history in it. The columns from year one are clean. Sensible names, not null, good defaults. Somebody had time to think about it. The columns from year three are nullable, because they got added to a table that already had a million rows in it and nobody wanted to backfill.

By year six, you've got a column called temp_flag, and one called legacy_user_id, and my personal favorite, do_not_use. Which I'd like to point out is still in use.

And here's the thing that bothers me about this table. If I pull 10 random rows and put them next to 10 rows in the real users table, I honestly can't tell them apart. They look exactly like real users. They're the same shape. They have the same kind of data. Real names and real emails.

Which kind of reminds me of a movie. In 1982, John Carpenter made The Thing. A research station in Antarctica, 12 men, a lot of snow (because it's Antarctica), and something they dug out of the ice that can perfectly imitate any living thing it touches. A dog, like the huskies in the film. A man. Your friend.

The scary part of that movie isn't the monster. It's that it looks exactly like everyone else, and you can't tell who's real by looking. So hold on to that, because there's a very famous scene, and we're going to come back to it.

Something is writing rows to this table, rows from this morning. So the next question is: what is writing to this table?

The normal move is to search the codebase. I grep every service for user_archive_old_v2, and there's nothing. Not one reference in any application code. That's not looking good. Next, I check the database's query logs to see what's connecting and writing to the table. And the writes don't line up with any service I know about. There's no app user, no service account that I can match to it.

This is the moment where a lot of teams stop, because now it really does feel a little bit haunted. Nothing in the code touches it, no service writes to it, and yet every morning there are new rows.

And then it hits me, because I've been burned by this before. If nothing outside the database is writing to it, then something inside the database is. A trigger.

If you haven't worked with them, a database trigger is a little piece of code that lives inside the database itself. Not in your app, not in your repo. You attach it to a table and say, "Whenever a row in this table changes, run this." And it runs every time, forever, without any service asking it to.

I look, and there it is. A trigger on the users table. Before every update, take the old version of the row and copy it into user_archive_old_v2. Created in 2017.

And now the story writes itself. In 2017, somebody was doing a big change to the users table. A risky one. And as a safety net, they added a trigger: every time a user record changes, keep a copy of what it looked like before. That way, if the change goes wrong, you can recover. That's a good idea. That's a responsible engineer.

And the change went fine. The trigger stayed, because it wasn't hurting anything. Every time someone updates their profile, changes their password, changes their email, a copy of their old record goes down the stairs into the basement. Every day since 2017. That's your 2 million rows.

And now I understand the senior engineer's story too. Somebody touched it. Something broke. They put it back.

I go looking, and I find it. In 2019, someone built a dashboard for the growth team. They needed to see how often users changed their plan, and they found this table full of every old version of every user and thought, perfect, that's history. So they pointed the dashboard at it. And when someone tried to clean the table up, the growth dashboard went blank, and somebody important noticed, and it got put back.

That's how data becomes immortal. There are really only three reasons you can't delete something. One, compliance: you're legally required to keep it for some amount of time. Two, dependency: something reads it, and you can't find what. Three, fear: you don't know what breaks if you remove it.

This table hit two and three. And the funny thing is, it's almost never number one. When people say "we can't delete that," it's usually fear wearing a compliance costume.

So here's the part where it stops being a fun mystery. A user sends in a deletion request. They want their data gone. Under privacy laws like GDPR in Europe, or California's rules here in the US, that's their right, and the company is supposed to actually do it.

The team does what the deletion process says. They mark the user as deleted. Marked. Not deleted. This company, like a lot of companies, uses soft deletes. Instead of actually removing the row, you set a deleted_at timestamp, and the app treats that user as gone. It's common. It's useful. It lets you undo mistakes.

But think about what a soft delete actually is from the database's point of view. It's an update. And what happens before every update to the users table? The trigger fires. And it copies the user's full record (their name, their email, their address, their phone) down to the basement, where it will live forever, because nobody touches that table.

The act of deleting this person is what made a permanent copy of them.

Sit with that for a second, because this is what unknown data actually costs. It's not the storage. Storage is cheap. It's gotten so cheap. It's that you've told a person their data is gone, and you believed it when you told them, and it's sitting in a table that only exists because of a safety net from 2017.

Nobody lied. Nobody did anything malicious. The 2017 engineer was being careful, and they definitely needed to be careful. The deletion process was written correctly against the tables it knew about. It just didn't know about the basement, because nobody does.

And then it gets a little bit worse. Over the years, some users were fully hard deleted. Actually removed from the users table, through a cleanup script or a support tool. Their old versions are still in the archive. Rows pointing at a user ID that doesn't exist anymore anywhere else in the system.

Orphans. Rows that belong to nobody.

And that's the real cost. Every time you need to answer a simple question about your own data, like "Have we actually deleted this person?" or "How many active users do we have?" or "Where did this number on the dashboard come from?", the basement makes the honest answer "We're not sure."

And "we're not sure" is a very expensive sentence to say to a regulator. Or to a customer. Or to yourself at two in the morning during an incident.

So back to The Thing, because I promised you the famous scene. The blood test.

Partway through the movie, the men at the station know the thing is among them. They know it could be any of them, and nobody trusts anybody. There's a line where Kurt Russell's character, MacReady, is talking into a tape recorder, and he says, "Nobody trusts anybody now, and we're all very tired."

That is, word for word, the state of a team that doesn't trust its own data.

And then MacReady figures out the test. His theory is that every piece of the thing is alive on its own. Every drop of blood. So if you take a drop of its blood and hit it with something hot, that drop will try to save itself. It'll react. Human blood doesn't. So he ties everybody up, takes a blood sample from every single person, heats a wire, and touches it to each dish, one at a time.

Here's why that scene is the answer to the basement. MacReady doesn't look at people and guess. He doesn't pick three guys at random, test them, and call it good. He tests every sample, because the whole problem is that the bad ones look exactly like the good ones. Spot-checking is useless. The only thing that works is a test that runs against every single one, with a clear rule for what a reaction means.

That's what you do with the basement table. You don't open it up and eyeball 10 rows. 10 rows will look fine. They always look fine. That's the problem.

You write the blood test: a set of checks that run against all 2 million rows. Does this row's user ID still exist in the users table? If not, it's an orphan. Does it belong to a user who's been deleted? If yes, that's a privacy problem with a name on it. Does anything still read this row? Check the query logs for the dashboard and find out exactly which columns it uses. And you run every check against every row. Not a sample.

When you do this (and I've watched teams do it), the results are usually not what anyone guessed. In a table like this one, it's normal to find that a big chunk of the rows belong to accounts that no longer exist. A meaningful slice belong to people who asked to be deleted. And the dashboard that everybody was afraid of breaking uses three columns out of the nineteen.

And now, finally, you can make decisions. Not fear-based ones. Real ones. The dashboard gets pointed at a proper history table that only has the three columns it needs. The trigger gets replaced with an actual audit log, with a retention period written down and an owner. A person with a name. The deletion process gets updated to include the archive, and anything else the blood test found.

And the rows that belong to the deleted users get deleted. I mean seriously, actually deleted.

That's not a full cleanup of every table in the house. That's one basement. But that's how it goes. One table at a time, one blood test at a time.

The data audit is not glamorous work. Unfortunately, nobody is going to get promoted for it. But it's also not optional if you want to actually own your system instead of renting it from 2017.

So here's where we are. The table is still there. The last time it was written to was this morning. But now I know what writes to it. A trigger, added by a very careful engineer on a stressful day in 2017 (and that was probably a stressful year), doing exactly what it was told.

I know what reads it. I know which rows belong to the people who asked to be forgotten. And I know which ones belong to nobody at all.

Nothing about the table changed. What changed is that we went downstairs, turned on a light, and saw what was in the basement.

I think about The Thing a lot when I think about data. Not because of the flamethrower, but because of the tiredness. "Nobody trusts anybody now." That's what a basement does to a team over time. You stop trusting your numbers. You add a little asterisk to every report. You say "probably" and "maybe" and "most likely" in every meeting. You're hedging your answers. You build new things around the old table instead of getting on top of it, because nobody really knows what's going on down there.

And in The Thing, the blood test isn't really about finding the monster. It's about getting to trust each other again. After the test, in the movie, they know who's who. That's worth more than the test itself. Your data can get there too.

Data outlasts code. That's not a curse. It just means somebody has to own it for longer than anyone owns the code.

So go look at your database. Find a table with "old" in the name, or "v2," or "archived." Check when it was last written to. And if the answer is this morning, you know what to do.

Heat the wire.

That's this episode of Chaotic Commits. I'm Joanne Skiles, and I just spent 20 minutes comparing a database trigger to an alien that eats Antarctic research scientists, and I stand by every word of it. New episodes wherever you get podcasts. No highlight reel, just the commit history.