watchnews.now read three news directories at once. Now it reads one, and the three faults that came out of that change were all invisible beforehand for the same reason: duplication was hiding them.
The site is a whitelabel of a sports fixtures schema. A desk is a league row, an outlet is a team row, a story is an event with one side. It had three providers writing into that schema directly: our own news collection, a small web firehose, and a directory of classified newsrooms. Every one of them wrote its own desks and its own outlets, because leagues.slug and teams.slug are unique and rows cannot be merged across providers.
So a reader opening the world desk got this:
World
3 sections. Open one to follow its outlets.
Three entries, all called World. Ten publishers had two outlet pages each. The fix was not to name the duplicates more carefully. It was to stop reading the same sources twice: fold the two directories into the one collection upstream, where deduplication can happen once, and have the site read that.
The first fault: a slug somebody else already holds
teams.slug is unique across the whole table. The upsert conflicts on (provider, provider_key). Those are not the same key, and the gap between them is where this lives.
A row whose slug belongs to a different provider key is not an update. It is an insert against a unique index, and Postgres fails the entire statement. One clashing name loses the whole pass, not the one row.
An adapter cannot prevent this. It sees the batch it is building and nothing else. That was fine while each adapter owned a namespace. It stopped being fine when the collection started supplying a masthead: a feed keyed bbc-co-uk-2 arrives named BBC News, slugs to bbc-news, and the row keyed bbci has held that name since the first sync.
Every sync failed with duplicate key value violates unique constraint "teams_slug_key". Because collections are written before participants, the desks that had no outlets yet were created and then left empty. The site showed sections with nothing behind them.
My first attempt fixed it inside the adapter, and the adapter's own existing test refused the fix. bbci named BBC News and a domain bbc.news both slug to bbc-news. No pure function of a single row can promise global uniqueness. It has to be asked of the database. The incumbent keeps the readable slug, because it is in somebody's bookmarks and possibly in their follows, and the newcomer takes a discriminated form derived from the provider key so a re-run is stable rather than drifting.
The second fault: filed under the wrong desk
Ingest resolved an event's league as genreKeys[0], the first collection its subject belongs to. That is right for a television show. An episode belongs to the show, the show is filed under several genres, and any of them will do.
It is wrong for a subject that spans categories, and a news outlet spans categories. A publisher covering world and US news holds both desks, so every story it filed landed on whichever desk the adapter happened to see first.
The US and technology desks listed outlets and not one story. World held 455.
This had been latent the whole time. It only appeared once outlets legitimately spanned desks, which is exactly what consolidating produced. The fix matches the event's own category within its subject's collections, which leaves the other brands untouched: the television adapter writes every genre with the same category, so the lookup returns the first one just as it always did.
The third fault: a window narrower than one refresh
After both fixes, US still had no stories. The data was there. The window was not.
The upstream cursor pages by id, which means most recently inserted rather than most recently published. That is harmless while sources trickle. It is not harmless when one source writes in bulk: the directory walks its desks in order at a couple of hundred stories each, so a single pass lays down thousands of rows whose ids run in desk order.
A thousand item window landed entirely inside the tail of one pass. Measured live, the newest thousand items were 195 food, 172 climate, 138 travel, and 1 US, 1 technology, 2 business. Across the newest four thousand the same data is healthy: 687 world, 325 politics, 301 business, 257 US, 228 technology.
Nothing was broken. The window was just narrower than one upstream refresh.
What deduplication actually needs
Upstream, the collection is keyed (source_id, external_id). That answers "has this source told us this before" and it cannot answer "do we already have this story". Two sources reporting one article were two rows, with two external ids and nothing in the schema able to say they were the same thing.
Items now carry a normalised form of their URL. It drops the road and keeps the destination: scheme, www., the fragment, a default port, a trailing slash, and the tracking parameters. It deliberately keeps ?p= and ?id=, because a great many sites address an article entirely through a query parameter, and fusing those would silently discard real stories. Missing a duplicate is a much cheaper mistake than that.
It is opt-in per collection, because two sources naming one URL is not always one thing. A package index and a changelog can both point at a release page and mean different rows.
75 duplicates went in the first pass, mostly one publisher reached both from its own feed and through a directory that indexes it. Then 5 more survived, and they were a case the cross source filter deliberately cannot catch: one publisher exposing the same article through two of its own feeds, inside a single pull. The filter ignores the source being run, because a source has to be free to restate its own items or its second run would discard everything the first one wrote. So a pull now folds against itself as well.
The one I shipped broken
The dedupe query passed a JavaScript array into = any($1). Bun's Postgres driver serialises an array by joining it with commas, so Postgres received one string and rejected the statement with malformed array literal, quoting a perfectly valid japantimes.co.jp URL.
It does not fail politely. The error propagates out of the write and aborts the whole source run, and that query runs for every source in an opted-in collection. It did not break the two new sources. It broke the collection.
The fix was a helper that already existed four times over in the same file. The test now scans every query in that directory and fails if any of them interpolates a bare value into any(), because a unit test cannot reach the driver that mis-serialises the array, so the thing worth pinning is the shape of the call.
Where it ended up
Thirteen desks, one section each, no duplicate slugs, no empty sections. 3,278 stories across 645 outlets, from 47 on a single desk when the day started. Outlets carry a real masthead where the upstream has one and a bare host where it does not.
Two names still appear twice on the world desk, Al Jazeera and The Guardian, because stories are deduplicated by URL and publishers are not. Same masthead, two followable rows. Fixing that means publisher identity resolution across sources, which is real work rather than a patch, so it is written down instead of improvised.
The pattern in all three faults is the same. Duplication is not only a data problem. It is a way of not finding out that your keys are wrong.
Top comments (0)