Reconciling what was sold with what was on air

The request

The network sells ad time on about 15,400 monitors15,444 monitors in the occupancy bands, report of 2026-10-01 in residential buildings and malls. Sales closes campaigns in one system: the order, the value, and how much of it is paid, bonus or barter. Operations schedules the campaigns in another system: which content plays on which screen, on which days. These are two different worlds with two different ids.

Sales used an occupancy screen that was one of the first ones we built, years ago, and nobody had updated it since. It showed occupancy per publication, without the sales order number, without value, and matching a publication to its sale was manual work. The formal request had six items: order id, value, the paid / bonus / barter split, Excel export, automatic matching between publication and order, and the dates on air. When I showed the first wireframe, the sales manager said she had been waiting three years to see that screen.

The number underneath came first

To answer "how much of this network is sold" you need occupancy per screen per day. We had three engines computing it: one per environment, and a third one in the malls environment that was a stored procedure living only inside the database, without version control. They didn't agree with each other. A new screen on top of that would be a nice view of a number nobody trusts, so the first days went into the engine and the screen came after.

One engine for both environments, same rules. Two definitions I had to discuss about with the business, because code can't decide them: occupancy is what was sold, with or without the creative already uploaded; and bonus inventory doesn't occupy, it fills what's left.

Then the validation. I compared the new engine with the source system for every machine on every day of a 30-day window:

Window 2026-09-28 to 2026-10-28, compared on 2026-09-28. All 18 differences fall in three days at the end of the window, and I haven't closed their cause yet.

I compare per machine and per day on purpose. Totals hide compensating errors: one screen counted twice and another one missing gives the right sum and two wrong screens. The shape of the query, with generic names:

SELECT
    count()                                                      AS machine_days,
    countIf(isNull(e.slots) OR isNull(s.slots) OR e.slots != s.slots) AS different
FROM engine_occupancy AS e
FULL OUTER JOIN source_occupancy AS s USING (machine_id, day)
WHERE day >= '2026-09-28' AND day <= '2026-10-28'
SETTINGS join_use_nulls = 1;

join_use_nulls matters here. Without it ClickHouse fills the missing side with zeros, and a machine that exists only in one side looks like a machine with zero occupancy, which can match by accident.

Today the engine holds 5,392,734 machine-days for 20265,292,528 residential, 100,871 malls; read-only query, 2026-10-12, and 1,234,304 of them are in the future: schedule already sold and not yet played. The table goes until April 2027.

Matching sales to schedule when ids are shared only sometimes

There is an integration table that links a sales item to its publications. When it exists, that's the match. It covered 82% of what came from sales. For the rest, the link lived in a free-text field of the publication, where people type the sales order: something like ID SALES: 1234 - Home - 5678.

Reading ids out of text is dangerous, people type them wrong. So the text only counts when it passes a double check: the item it cites must exist in the sales system, and it must belong to the order it cites. If one of the two fails, the publication stays without a match and shows up as an alert.

private const RE_ITEM = '/ID\s*SALES\s*:\s*(\d+)\s*-\s*(?:\w+\s*-\s*)?(\d+)/i';

foreach ($publications as $p) {
    $key = $p['env'] . '|' . $p['pub_id'];
    if (isset($linked[$key])) {
        continue;                                    // integration already linked it
    }
    if (!preg_match(self::RE_ITEM, (string) $p['notes'], $m)) {
        continue;
    }
    $item = $items[$p['env'] . '|' . $m[2]] ?? null; // the item has to exist in sales
    if ($item && (int) $item['order_id'] === (int) $m[1]) {   // and belong to that order
        $linked[$key] = $item + ['via' => 'text'];
    }
}

Each match carries a via field all the way to the screen, so anyone can see if an order was matched by the integration or by the text, and that changes how much you trust it.

With both paths, 100% of what came from sales got linked. Values follow the same rule the financial pipeline uses, split across the items of an order with a window function in SQL. I checked against finance before anyone used it: net value matched 1,277 of 1,277 items in malls and 382 of 382 orders in residential. On the way, 291 orders that showed as "client not identified" got their real names back, because the copy of the advertiser table was behind.

A snapshot, not a query

Nothing on the screen queries the live databases. Every 15 minutes a scheduled command reads the views once, joins everything in PHP and writes a compressed JSON snapshot in a positional format, so the whole network fits in about 1 MB. The browser downloads it and calculates the period you choose, including the Excel file, with ExcelJS. In the first runs, the snapshot had 552 KB with the previous month included and took 8.8 seconds to build. Before the change, repeating the services view in each query cost 90 seconds by itself.

Two details made it safe to run every 15 minutes:

private const MIN_PUBLICATIONS = 500;   // fewer than this = empty source, keep the old one

private static function write(string $file, string $json): void
{
    $tmp = $file . '.tmp';
    file_put_contents($tmp, gzencode($json, 6));
    rename($tmp, $file);                // atomic: readers get the old file or the new one
}

Because the rename is atomic, someone downloading in the middle of a run gets the previous snapshot whole, never half of one. The minimum guard is for the day a source comes empty: the command refuses to replace a good snapshot with a nearly empty one. The server answers with an ETag built from the file time and size, and the browser gets a 304 when nothing changed:

$etag = '"' . filemtime($file) . '-' . filesize($file) . '"';
$this->response->setHeader('Cache-Control', 'private, no-cache')->setHeader('ETag', $etag);
if (trim($this->request->getHeaderLine('If-None-Match')) === $etag) {
    return $this->response->setStatusCode(304);
}

This design got tested in its first day in production. When I added the previous month, the snapshot doubled and the command ran out of the 128 MB memory limit of the PHP CLI. The build failed, the old file stayed, and the screen kept serving it while I raised the limit, same day. Past months later moved to one file each, around 0.8 MB, loaded only when someone navigates there.

The mismatches are the product

When you put sold and scheduled side by side, the differences jump out, and each one is a decision for somebody. The alerts tab has five types:

• cancelled in sales and still on air;
• dates in sales different from the dates on air;
• bonus in sales counted as sold;
• sales item not found (linked only by the text);
• discount plus bonus above the price table.

And orders sold with no publication at all appear in the order view, which is the classic "sold but not on air".

One more view came from a conversation after launch. Many contracts with malls are revenue share with a monthly minimum guarantee and no carry-over: a great month doesn't cover a weak one. Per mall and per month, the screen shows where the minimum is not reached and where there is idle inventory, so the flexible part of the budget can go where it pays.

Where the agents helped, and where they didn't decide

I used Claude Code a lot in this, and the split was clear. The engine discovery ran as four parallel read-only investigations (target model, efficiency, who else reads the database, the malls diagnosis). Five interface concepts were generated in parallel; I rejected the first two versions and ended with a hybrid of two concepts. A subagent reviewed the PR as if it was from someone else and found four important points, none blocking. The wireframe ran with real data and was tested with click simulations of 33 and then 45 steps.

I kept the decisions, and they were the ones that defined the product: put it inside the sales system with its own screen; move data with NiFi; the same rules for malls and residential; occupancy equals sold; bonus doesn't occupy; the sales order as the main view; keep franchisees out because the screen shows contract values of the whole network.

What happened next

On 2026-09-27 the unified engine went to production. The first commit of the screen was on 2026-09-30 at 15:55, and it was live at 22:49 the same day: the hard part was underneath, already done. Eleven commits until the next day and 25 more between October 2 and 5, all of them mine, many of them things the users asked for in meetings, a few merged while the meeting was still going.

Before all this, the commercial director had offered his own budget to hire an outside team for this. In the review he said it was more than a sprint: "the Ferrari is already in the garage". Four days later, after working with it on the weekend, he told the team: "This has to be used every day, because if we don't set a rhythm, we don't close this quarter."

What I'd reuse anywhere

• Fix the number before the screen. The request describes the symptom.
• Validate per unit (machine-day, order, item) against the source. Totals hide errors that cancel each other.
• Match by id first, by text second, with a double check, and show the user which path each match took.
• Precompute a snapshot, write it atomically, guard against an empty source, and serve it with ETag.
• Show the mismatches. A reconciliation that only shows the matched part tells nobody what to fix.

Filed under the problem it solves

• data reconciliation
• reliability
• clickhouse
• ai engineering
• reporting
• precomputed snapshot