This is the technical half of a story I wrote up for a general audience: a data pipeline job failed silently and our monitoring flagged it. This post is for developers. It has the actual code, the queries that found the root cause, and the reasoning between them, because the bug is a pattern you have probably shipped too.

The setup

Our pipeline ingests Google Search Console data daily and runs a set of detectors over it. Each detector scans for one kind of signal, for example queries ranking on page one or two that get impressions but almost no clicks. Findings get upserted into Postgres and surface in a dashboard.

One morning the run finished as "partial" with this recorded message:

Failed detectors: within-reach (upsert_error)

One detector, one reason code, no detail. Every other detector saved fine.

Why the error was thin

The persistence function had four failure paths, and all of them looked like this:

const inserted = await supabase.from('findings').insert(toInsert)
if (inserted.error) {
  return null
}

The error object from Postgres, with its code, message, and hint, was checked and then thrown away. The caller only ever saw null. So the first lesson happened before any real debugging did: the investigation below took a morning, and with one console.error per failure path it would have taken two minutes.

Ruling things out

With no error detail, we worked from what Postgres could reject. The constraints on the findings table:

select conname, pg_get_constraintdef(oid)
from pg_constraint
where conrelid = 'public.findings'::regclass;

That returned two closed check constraints (category and confidence must come from short allowed lists), a unique constraint on (detector, emission_key), and a foreign key.

Theory one: the detector emits a category or confidence value outside the allowed lists. Checked the source: category is 'opportunity' and a confidence ternary resolves to 'medium' or 'exploratory', all valid. Dead.

Theory two: a type mismatch. The detector scores findings as (31 - position) * impressions, where position is impression-weighted and almost always fractional. If the score column were integer, every insert from this one detector would fail the cast while whole-number detectors passed, which fit the shape of the failure perfectly. Checked the column: double precision. Dead, but it was the most instructive wrong theory of the morning, because it explained a detector-specific failure through shared code, which is the thing that actually needed explaining.

Theory three: the unique constraint.

The actual bug

The detector keys each finding on the search query text, normalized through a slugifier:

function slugifyKey(value: string): string {
  const slug = value
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-+|-+$/g, '')
  return slug.length > 0 ? slug : 'item'
}

Standard, unremarkable, and lossy. Every run of non-alphanumeric characters collapses to a single dash. Which means distinct strings converge:

"next js seo" becomes next-js-seo
"next.js seo" becomes next-js-seo

Search Console records queries exactly as people type them, and people type the same search in every variation that exists. So the question became empirical: do colliding variants actually appear in our data? Replicate the slugifier in SQL and group by it:

select
  regexp_replace(
    regexp_replace(lower(trim(query)), '[^a-z0-9]+', '-', 'g'),
    '^-+|-+$', '', 'g'
  ) as slug,
  array_agg(distinct query) as queries
from search_queries_daily
where date >= current_date - 28
group by 1
having count(distinct query) > 1;

Three collisions in a 28-day window, including the pair above. Root cause confirmed.

The mechanics: when both variants of a query clear the detector's thresholds in the same run, the detector emits two findings whose keys are identical. The persistence layer batched them into one plain insert, Postgres correctly rejected the batch on the unique constraint, and the swallowed error became that thin "upsert_error."

Why it took months to fire

The collision was possible from the day the code shipped. It never fired because firing required both variants to pass the detector's impression and position thresholds in the same window. For months, only one variant at a time qualified. Then one week both did.

This is the part worth internalizing. The bug was not introduced by a deploy, a dependency bump, or a schema change. Nothing changed. The input distribution drifted until it found the hole. Bugs like this are invisible to code review of any single commit, because no single commit is wrong. The slugifier is fine. The unique constraint is fine. The insert is fine. The system is wrong.

The fix

Two changes, both in the persistence layer rather than the detector, because any detector keying on user-generated text has the same exposure:

Dedupe by emission key before insert, keeping the highest-scoring finding. The two query variants genuinely are the same opportunity, so collapsing them is correct behavior, not just crash avoidance.
Log the full Postgres error object at every path that previously discarded it.

The slugifier stays as it is. Existing rows key on its current output, and making it lossless would orphan them. The lesson is not "write a better slugifier." Lossless normalization of arbitrary human text into a key is not really achievable anyway. The lesson is that any lossy key derived from user input will eventually collide, so the layer that writes keyed data has to own collisions, and the code that swallows an error today is borrowing debugging time from whoever investigates the failure later, at a terrible interest rate.

Takeaways

Search query data is user-generated content. Treat it with the same suspicion as form input.
A lossy normalizer plus a unique constraint is a collision waiting on the right input distribution. Handle the collision where you write, not where you generate.
if (error) return null is a bug with a delay on it. Log the object.
When a failure is specific to one code path through shared infrastructure, the explanation must be something unique about that path's data. That framing killed two wrong theories fast and pointed at the right one.

The non-technical version of this incident, and why monitoring for this class of failure is part of how we run client sites, is in what website monitoring actually catches.

Two Searches, One Key: How Messy Search Data Breaks Clean Code