example.com and example.com/ were two rows, and 86.9% of my database was one bug

The Bug

A scheduled job re-audited domains on an interval. Cheap, idempotent, ran for weeks without complaint. Then I looked at row counts. One domain had 1831 audit rows. It should have had a few dozen.

Two rows in the domains table:

• example.com
• example.com/

A unique index on the URL column. Both rows satisfy it - they're different strings. The database was doing exactly what I asked.

The job then did roughly this: take a domain, run the audit, write the result keyed by the URL the audit resolved to. The audit followed redirects and normalized. So it read example.com and wrote example.com/. Next tick: example.com still has no recent result - the result went to the other row. Audit it again. Write to the slashed row again. Nothing errored. Every individual run was correct. The loop simply never converged, because the key it read by and the key it wrote by were never the same key.

Across the table, rows attributable to this were 86.9% of all audit rows. Not 86.9% of one domain. Of the table.

Why It Stayed Invisible

Every signal I had was pointed the wrong way:

• No errors. Each audit succeeded. Logs were clean.
• The unique index looked like protection. I had convinced myself duplicates were structurally impossible, so when I saw two similar rows my first thought was that I was misreading the query.
• Cost looked like growth. Audit volume climbing looked like the product being used. It was the same handful of domains being re-audited forever.
• That one stings - I had a metric moving in the right direction for entirely the wrong reason, and I felt good about it for weeks.
• Per-domain views looked fine. Open one domain, see a sane history. The pathology only appears when you group by normalized key and compare against raw key, which is not a query anyone writes by accident.

The Fix

Normalize at the boundary, once, before anything touches storage.

import re
from urllib.parse import urlsplit

SCHEME_RE = re.compile(r"^[a-z][a-z0-9+.\-]*://", re.I)

def normalize(url: str) -> str:
    url = url.strip()
    if not SCHEME_RE.match(url):
        url = "https://" + url
    p = urlsplit(url)
    host = (p.hostname or "").lower()
    if host.startswith("www."):
        host = host[4:]
    if not host:
        raise ValueError(f"no host in {url!r}")
    port = f":{p.port}" if p.port and p.port not in (80, 443) else ""
    path = p.path.rstrip("/") or "/"
    return f"{p.scheme.lower()}://{host}{port}{path}"

Two Load-Bearing Details

Two load-bearing details, and I got one of them wrong the first time I wrote this out.

1. rstrip("/") or "/" - Strip the trailing slash, but if that leaves an empty path, put one back - so the root is always exactly / and never the empty string. Without the or "/" you've just invented a third spelling of the same page and replaced the bug with a subtler one.

2. The scheme has to go on before urlsplit, not after. urlsplit("example.com") does not give you a host. With no //, it reads the whole string as a path - hostname comes back None. My first version handled that with p.hostname or "", which silently produced a URL with no host in it at all, for the single most common input a user types by hand. The SCHEME_RE prepend fixes it; the raise makes sure that if a host is still missing, I hear about it instead of storing something shaped like a URL. That second one is the same class of bug as the story above: a value that's wrong but well-formed enough to store, so nothing complains.

Two Rules That Matter More Than the Function

1. One normalizer, called at the boundary. Not in the job, not in the API handler, not in the audit. In the one place a URL becomes a record. Every additional call site is a chance for two of them to disagree, which is the bug again wearing a different hat.
2. Normalize before the uniqueness check, not after. A unique index only protects the shape you hand it. Mine was enforcing uniqueness on a key I hadn't canonicalized, which is enforcement theater.

Then backfill: normalize existing rows, merge collisions, keep the earliest created_at and the most recent result. The 1831-row domain came out at 19.

Careful with www and rstrip

Two things in that function are opinions, not facts, and you should hold them deliberately:

• Dropping www treats www.example.com and example.com as the same entity. Almost always what you want, because almost every site redirects one to the other. Not universally true - if a host serves genuinely different content on the two, this merges two things that aren't one.
• Stripping the trailing path slash treats /about and /about/ as the same page. Also almost always right, also not guaranteed by HTTP. Two different resources at those two paths is legal and rude, and it exists.

I took both trade-offs on purpose. The cost of merging two things that were actually one is a slightly wrong row. The cost of not merging is what I just described.

The Transferable Part

The trailing slash isn't the lesson. The lesson is the shape: a loop that reads by one key and writes by another will never terminate, and will never raise. If you have a scheduled job that's supposed to converge, the question to ask isn't "is it erroring." It's: is the key I select by byte-identical to the key I write by? If a normalizer, a redirect, or a .lower() sits between the read and the write, the answer is no, and the job will run forever while every individual execution looks correct.

Cheapest check available - group by normalized key, count distinct raw keys:

SELECT lower(rtrim(url, '/')) AS norm,
       count(DISTINCT url) AS spellings,
       count(*) AS rows
FROM domains
GROUP BY 1
HAVING count(DISTINCT url) > 1
ORDER BY rows DESC;

If that returns anything, you have the bug. It took me weeks to think of running it.

Tomorrow, outside this series: my CI hadn't run a chunk of my test suite in two months, and the checkmark was green the whole time.