Backfill reports new links every time it runs, even when it made none

Flow

What my backfill does, in order

Steps two and five are where it goes wrong.

1. 1Read sessions⌄

   Pull a few months of sessions with their branches and commits.

2. 2Match⌄

   Find tickets, pull requests and error reports. Empty hashes can slip in here.

3. 3Add placeholder⌄

   Create a bare session row if Postgres lacks one.

4. 4Write link⌄

   The database skips links that already exist.

5. 5Count⌄

   Counts tries instead of real inserts.

Each run of the backfill says it made new links, even when it can't have made any. The writes are safe to repeat. The number it reports isn't, and one of the matchers has a worse problem.

What the backfill is for

My session tracker stores coding sessions in one database, along with the git branch and commit each session was on. Tickets, pull requests, error reports and the links between them and sessions live in a second database, Postgres. When I connect a new ticket tracker, older sessions aren't linked to the new tickets. The backfill goes back over a few months of sessions and adds those links.

It used to be a script I ran by hand. Now it runs on its own: after a tracker's first sync, after the error tracker is set up, and at the end of every code-host sync.

How it matches and writes

Tickets come from branch names, but only for known key prefixes. Pull requests match on branch name. Error reports match on commit hashes, where a short hash can match a long one.

Each link needs its session row to exist in Postgres first. So the backfill adds a bare placeholder session when one is missing, then writes the link. If that link already exists, the database skips it. Running it twice gives you the same rows as running it once.

The count is wrong

The backfill counts every link it tries as "created", even when the database skipped it. If you build something like this, count what the database says it inserted, not what you asked for:

The change is to add the number of rows the database says it inserted, which is zero when the link already existed.

Since the code-host sync runs it every time, the log reports the same "new" links on every sync. A dry run uses the same counter, so a dry run and a real run look identical. The old script did this too.

An empty commit could match everything

If I'm reading it right, this is the bigger problem. The old script threw out empty branch and commit values. The new one can keep an empty commit for a session that saw a branch but no commit. Every hash starts with an empty string, so that session would match every error report that lists a commit. I haven't checked if any real session has one. Filtering out empty and too-short hashes would stop it, and that filter isn't in yet.

The sync flag, the ID lookup and the fakes

One bad session marks the whole code-host sync as failed. The only tests that touch the backfill replace it with a fake, so none of this has met a real database in a test. And the placeholder check looks up a session by ID alone, not by ID plus org.