Rebuilding a lost column, and proving the rule first

The problem

My site records, for each visit, the page the visitor left from. On 16 October 2026, while checking the figures in my own case studies, I found that column empty on every row since the end of August. Everything around it was working. Channels were still being classified, and the browser's engagement beacons had kept arriving the whole time, each carrying the visit's id and the page it was sent from. The data needed to fill the column was in the database. It just never reached the row.

A day-level query put an exact edge on it. The last populated exit page was at 19:16 UTC on 25 August 2026. The first empty one was at 21:19 UTC the same evening. In between, the site's API moved from its old Node functions to a C# Function App.

How a port loses a statement

The C# version of the API was written in May 2026. In July, the Node version gained a small addition: every engagement beacon also updated its visit row, setting the exit page from the latest page summary and moving the "last active" time forward. The C# port predated that change and never picked it up. A later fix after the cutover restored the attribution columns written when a visit is created, but not this update.

So from 25 August the C# endpoint stored each engagement row and did nothing else. Two values stopped: the exit page, and the last-activity time, which meant visit durations were understated as well.

Nothing failed. A port is usually checked by asking whether every endpoint answers. This endpoint answered perfectly. The missing behaviour was a side effect on a different table, and nothing checked for it.

The fix

The repair restores the Node statement in the C# service that owns engagement recording, as a named constant beside the statement that stores the engagement itself. Only a page summary names an exit page. Other engagement types pass an empty value, and the update keeps the stored exit page when that happens, so a later "time engaged" beacon cannot wipe out a recorded exit.

I considered working the exit page out at read time in a database view instead. I chose against it: the column, the queries that read it and the dashboards all assume a stored value, and a view would leave the gap in ingestion for whatever reads the table next.

The tests drive the real service through a database context whose connection and commands are intercepted, so they assert the exact statement and its values with no database present. Two of the four failed against the unfixed service, because no update was issued at all, and all four pass with the fix.

Proving the backfill rule first

The fix stops the gap growing. It does nothing for the seven weeks already empty. Those could be rebuilt, because the engagement rows were all still there. The question was whether the rebuilding rule was right.

There was a way to check that without guessing. Before the cutover, the Node code had recorded the exit page for real, and those older rows still held that value. They had a known correct answer. I ran the backfill rule against them, read-only on production, and compared. It reproduced the stored exit page for all but two of them.

That gave me something to stand on. The rule is the live code's rule:

• the exit page is the page of the visit's latest page summary, with ties broken by the engagement's id so the answer is always the same;
• the last-activity time moves forward to the latest engagement of any kind, and never back.

The migration was then proven on a local database, using the shipped statement read from the C# source and the migration read from its own file. It fills only empty values, never lowers a last-activity time, ignores every row from before the cutover, and changes nothing on a second run.

The ordering trap

The release pipeline has a gate that fails a release while any migration in the repository is unapplied in the target environment. That gate is normally a safeguard. Here it would have forced the backfill to run before the fix was deployed. Any visit recorded between the backfill and the deployment would then stay empty, and the migration would never run again to catch it.

So the backfill went on its own branch, to be merged and applied in each environment only after the fix was live there. Its header records that order, and says how to recover if it is ever applied too early: the two updates only fill what is still empty, so they can be re-run by hand after the deployment.

Where it stands

The backfill was applied to dev, test and production on 16 October 2026. In production, about two thirds of the rows without an exit page were filled, which was every one that could be. The rest never sent a page summary, so there is no record of where they left, and they correctly stay empty. The fix that writes the column is live, so the gap is not growing back.

A second, quieter blank

The same audit turned up a related problem in the attribution tables. When the browser posted a snapshot of campaign tags and click IDs, it sent every field whether or not it had a value, and the API stored an absent value as an empty string. Every row in the table read as "filled" for columns that held nothing.

It was also destructive. The update used "keep the stored value unless a new one arrives", and an empty string counts as a new value. So a later blank post could overwrite a real campaign source.

Absent values are now stored as NULL, normalised at write time in the one place each table's values already pass through, instead of in each caller or in two separate SQL statements where unit tests cannot see the rule. A guarded, re-runnable migration nulls the blanks already stored. On dev, every blank value was converted, and it has since run in production too, on 23 October.

What to take from this

• A port can pass every endpoint test and still lose behaviour. List what each endpoint changes, including other tables, and check the new version does the same.
• Test a backfill rule where you already know the answer. If older rows hold the true value, run the rule against them first. All but two of the known values reproduced is a result you can decide on; "it looks right" is not.
• Make a backfill fill blanks only, and safe to repeat. Then it cannot damage good data, and a run in the wrong order can be put right.
• Check how your release gate orders migrations and code. A gate that applies everything first is the wrong order for a repair that depends on the code being live.
• An empty string is a value. If "missing" is stored as '', every "is it filled?" count and every "keep the old value" update is quietly wrong.

Related

If this is your problem too

A rewritten backend that quietly does less than the one it replaced is exactly what a backend and API audit looks for. The deployment side of the same cutover is in a green health check is not a deployment.