Backfill db with file_id #660

Follow-up to #513, with what we have learned since. This issue is about the approach; nothing has been run yet.

Why

About 1.99M of production's ~2.07M entries rows have no file_id (#513, 2026-10-04). Since #654 and #656, imports look existing entries up by file_id first. An old row without one is not found, so importing the file again creates a second row. file_id is indexed on production and name is not (#650).

What we have to work with

• Montage stores no images and no hashes. Jurors load each file from Commons by name, so they see whatever the name shows at that moment. A row holds the name plus metadata from its first import: upload date, uploader, width, height, MIME type, and create_date. Later imports reuse the row by name without updating it.
• The stored upload date means different things over time. Depending on when the row was first imported, it is the newest version, or some older version, or the earliest version of the file. Full-CSV imports stored whatever the CSV said.
• On Commons, file_id identifies a name, not an image. The file table exists since MediaWiki 1.44 (2025); files deleted before that have no file_id at all. A deleted file keeps its row, and a later upload under the same name (a restore, or a different image) reuses that row and its file_id (LocalFile::acquireFileIdFromName). Only the sha1 of a version identifies the image.
• The upload log is the most complete history. Every upload and overwrite entry records the version's sha1 and timestamp. The entries stay after deletion, overwrites, hidden versions and moves. Moves leave them under the old title. Only a hidden log entry is missing from the history. Entries before mid-2013 carry no sha1, only the timestamp.

Approach

Two separate questions, answered separately:

• When is a match certain? This is about the facts: which evidence proves that a Montage row and a Commons file are the same image.
• Can someone else reproduce the result? This is about execution: the same inputs and rules must give the same answer, whoever runs them.

Fill file_id only where the first answer is "certain", and leave every other row NULL. Then take the harder cases one tier at a time.

1. When is a match certain (facts)

"Deleted" means four different things here, and they are kept apart: the whole file deleted; one old version deleted (moved to the archive) while the file stays live; a version hidden by revision deletion; a version suppressed by oversight.

Proposed as certain: set file_id

• One upload, never changed. The name has exactly one upload log entry and nothing else of type upload. Its file has exactly one version, and that version's sha1 and timestamp equal the log entry. The name has shown one image since it was uploaded, so every import saw that image.
• Several versions, all older than the row's first import, all by the original uploader or a bot. Every import saw the current version. Bot changes (rotation, crop, optimisation) are reviewed per bot.

Montage's stored date and size are checked against these and counted, but they do not decide (their meaning changed over the years).

Proposed as certain: leave NULL

• Whole file still deleted, deleted after the last import. A later upload under the name would inherit the file_id and be attached to the old row. The deleted image stays identifiable by its archived sha1.
• Whole file deleted before the 2025 migration: it has no file_id.

(A file deleted before a row's first import cannot be the row's image: the row is a later file under the same name, and falls under "name used again" below.)

Open, to decide once the counts are in

1. Uploads before mid-2013: the log has no sha1, so the rules above can only compare timestamps. Enough?
2. Other people's overwrites before the import (including tool edits such as CropTool): jurors saw the final image, but whose work is it?
3. Versions added after the first import: different imports saw different versions.
4. One version deleted, hidden or suppressed while the file stays live.
5. Whole file deleted and restored, with the same sha1 and timestamp as before.
6. Moved away: Montage's name is now a redirect (or broken). Give the row the moved file's file_id?
7. Moved away and the old name used again; moved into Montage's name from elsewhere.
8. Whole file deleted and the name used again for another image, including deletions between two imports.
9. Deleted as a duplicate, the name now a redirect to the kept file.
10. Stored metadata that fits no version (mostly full-CSV imports).
11. Names not in canonical form (spaces, prefix, case, Unicode); hidden log entries; rare cases (history merges, imported files).

A row imported in several campaigns over the years must have shown the same image at every import. This is checked against the dates of uploads, moves and deletions.

2. Can someone else reproduce it (execution)

• Rules written once, as code with tests. Each tier's rule is one function, and its tests cover every edge case above. The rule is the code, not a description.
• Read-only analysis first. It runs on Toolforge against montage-dev's copy of production (Give montage-dev a production-sized database: a copy of production with the jury votes shuffled #652) and the Commons replica, and writes nothing to any database. Only counts are reported.
• A decision file with its evidence. For every row, the analysis records its decision and the evidence behind it: the upload log entry, the version, both sha1s and timestamps, and the replica time it read. Anyone can recheck a single row without rerunning everything.
• Commons keeps changing. A rerun can differ where Commons changed in the meantime. Each difference must be explained by a log entry after the first run's time; any other difference means the rules are not deterministic.
• A second run by someone else on the same copy, with the published script, must give the same counts.
• Writes only from the reviewed decision file, not by recomputing at write time. A journal records every write, so a rollback restores exactly what was there. Production comes last, after a backup.

Not part of this

• Duplicate rows: 43,993 names occur twice (Category imports time out on production: every file-name lookup reads the whole entries table #650). They are counted per tier; merging them is a separate issue.
• Changing file_id's meaning, or adding a unique key: neither is in scope.

Questions

1. Certainty: are the two "set" rules strict enough, and the two "leave NULL" rules right? Is a case missing from the open list?
2. Reproducibility: is a decision file with evidence per row, plus a second independent run, enough? Or should the write wait for review by someone outside this work?
3. Since file_id identifies a name rather than an image, should Montage also store the sha1 of the version it imported, so it can tell later whether a name still shows the same image?
4. Are there constraints on running read-only analysis against the replica and ToolsDB (load, timing)?

Related: #513, #520, #645, #650, #652, #654, #656.

Written by Claude (Claude Code) on behalf of @lgelauff.