M&A: A Wikidata Merge Assistant

Compares two Wikidata items and groups their labels, aliases, sitelinks and statements into identical / similar / distinct / one-sided, flagging anything that would block a merge. Candidate pairs come from a weekly hunt over a local mirror of Wikidata items (loaded from the weekly entity dump); logged-in users review them and merge or mark them as different, editing Wikidata directly.

Toolchain

React 19 + TypeScript 7 SPA on the Vite+ unified toolchain (the vp CLI), a Hono (Node) API server, and Drizzle on MariaDB. Managed with pnpm; designed to run on Wikimedia Toolforge.

Development

Needs a local MariaDB. On macOS:

brew install mariadb && brew services start mariadb
# Homebrew MariaDB uses unix_socket auth for root, so connect as your own user
# (no -u root). The utf8mb4_bin collation is required — see the note below.
mariadb -e "CREATE DATABASE mergers CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
  CREATE USER 'mergers'@'localhost' IDENTIFIED BY 'mergers';
  GRANT ALL ON mergers.* TO 'mergers'@'localhost';"

The database is created with the utf8mb4_bin collation (not the usual utf8mb4_unicode_ci): every string column inherits it, so comparisons are exact (case- and accent-sensitive). This is correct for external identifiers — two IDs that differ only in case are genuinely different — and restores the binary comparison the original SQLite schema used. Candidate search still folds case, because it lowercases both sides explicitly (lower(primary_label) LIKE …). On Trove, create the database with the same COLLATE utf8mb4_bin.

cp .env.example .env   # local DB defaults match the setup above
pnpm install
pnpm db:migrate        # apply migrations
DUMP_LIMIT=2000 pnpm job:import-dump  # load a slice of the mirror from a local WIKIDATA_JSON_DUMP
pnpm dev               # Vite SPA (proxying /api) + the Hono server on :8000

Commands

pnpm build             # build the SPA to dist/client (vp build)
pnpm start             # run the production server (serves API + dist/client)

pnpm db:generate       # generate a migration from db/schema.ts (drizzle-kit)
pnpm db:migrate        # apply pending migrations
pnpm import-items Q1 Q2 # import specific items live from Special:EntityData

pnpm job:import-dump   # load the mirror from the Wikidata entity JSON dump
pnpm job:hunt          # run the duplicate-candidate hunt once
pnpm job:sync-properties / :sync-entity-labels / :sync-place-ancestors
pnpm job:resolve-sitelinks / :resolve-creations / :resolve-merges
pnpm job:prune-sessions # delete expired login sessions

Formatting, linting, testing, and type-checking go through the vp CLI directly:

vp check       # format, lint, and type-check in one pass
vp test        # run tests with Vitest (DB-backed tests are skipped unless DB_TEST=1)

DB-backed tests

The *.db.test.ts files under server/ exercise the API routes and the hunt against a real MariaDB. They are skipped by default because they truncate every table between tests. To run them, point them at a dedicated database (the name must contain test; the migrations are applied automatically):

mariadb -e "CREATE DATABASE test_mergers CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;"
DB_TEST=1 DB_NAME=test_mergers vp test

(The mergers user created above can already create test_* databases.) CI runs them against a MariaDB service container, together with a from-scratch migration run and a schema-drift check — see .github/workflows/ci.yml.

Authentication

Login is Wikimedia OAuth 2.0 (authorization code + PKCE, confidential client). Anyone can browse candidates; dismissing/reopening and the Wikidata edits below need a login. With none of the OAUTH_* variables set the app runs read-only and the login link is hidden.

1. Register a consumer at Special:OAuthConsumerRegistration/propose/oauth2 on meta.wikimedia.org: OAuth 2.0, not owner-only, callback URL exactly <BASE_URL>/api/auth/callback, applicable projects wikidatawiki (and testwikidatawiki for dev), grants "Basic rights" + "Edit existing pages". Consumers that request edit grants are approved by hand, which can take a few days; an owner-only consumer works for the owner immediately in the meantime.
2. Set the variables listed under # --- authentication in .env.example (.env locally; toolforge envvars create on Toolforge). SESSION_SECRET signs the login-state cookie; TOKEN_ENC_KEY encrypts the stored OAuth tokens; BASE_URL is the public origin (cookies are Secure iff https).
3. Optionally, put your own central user id in ADMIN_USERS; it only marks you as "admin" in the header.

Session cookies are HttpOnly; SameSite=Lax, the DB stores only their hash, and state-changing API calls must carry a same-origin Sec-Fetch-Site/Origin. The nightly prune-sessions job deletes expired sessions.

Editing Wikidata

Every edit is made under the logged-in user's own account through their OAuth grant (server/wikidata-client.ts) — never a shared or bot account, never with bot=1. Wikidata enforces the user's real rights. Accounts blocked on Wikidata are locked out of the app altogether, not just out of editing: login refuses a blocked profile, an edit Wikidata rejects as blocked ends the account's sessions, and the nightly prune-sessions job re-checks every logged-in account's profile so a block placed after login catches up within a day.

• Merge (POST /api/candidates/:id/merge) runs wbmergeitems in the app's order — the higher QID into the lower — with an edit summary crediting the tool. The only ignoreconflicts kind ever sent is description (the survivor keeps its description, the merged-away item's is dropped). Because that leaves the ignored description on the source, wbmergeitems won't empty it and so won't redirect it; the app then finishes the job the way a human does — clears the source (against the merge revision, so a concurrent edit fails rather than being wiped) and redirects it to the survivor. This is best effort: if it fails the merge still stands, its resolution noting that the source item was not redirected. Clashing sitelinks and items that link to each other are never overridden: the confirm dialog warns when the mirror predicts one, Wikidata refuses the merge, and the user fixes the items by hand first. The one clash the app clears itself is a sitelink to a page that redirects to the other item's page on the same wiki: once nothing else blocks the merge, it asks that wiki's API (live, not the nightly sitelink_pages data) and, if the redirect is confirmed, removes that sitelink with wbsetsitelink before merging. Each removal is its own edit, listed in the merge's audit row and on the result. On success the candidate becomes merged (with both revision ids linked from the page), the merged-away item is dropped from the mirror so the hunt stops pairing it, and any other open candidate that referenced it is settled as "merged elsewhere". A merge takes a short merging claim on the row first, so a double click or a second tab can't submit it twice; a claim abandoned by a crash goes stale after ten minutes. If the request times out, the app checks whether the source became a redirect before deciding: a merge that did go through is settled as usual, and one that can't be checked keeps its claim until it goes stale rather than inviting a retry against a redirect.
• Mark as different (POST /api/candidates/:id/different) adds a different from (P1889) statement in each direction via wbcreateclaim, mirrors it locally, and dismisses the candidate. It takes the same claim as a merge, so two submits can't each add their own copy of the statements.

Requests carry maxlag=5 and an assert=user&assertuser= check; the client retries once on badtoken and once on maxlag, drops the stored tokens and asks for a re-login when the grant was revoked, and surfaces Wikidata's own error text (permissiondenied, blocked, conflicts, …) verbatim. Every attempt — success or failure — is recorded in wikidata_edits with the user, the revision ids, and any error. Edits are rate limited per user (EDIT_RATE_LIMIT/minute, default 10). Point WIKIDATA_API_URL at https://test.wikidata.org/w/api.php to develop against Test Wikidata (the consumer must list testwikidatawiki; QIDs there won't match the mirror).

Loading the mirror from the Wikidata dump

pnpm job:import-dump (jobs/import-dump.ts → server/dump-import.ts) streams the Wikidata entity JSON dump once and upserts every item whose instance of (best rank) is one of the classes in src/lib/import-classes.ts (video games, films and TV series, music, anime and manga, companies, museums, railway stations, stadiums, …; exact QIDs, no subclasses), together with its external ids and the properties table (labels, datatypes, formatter URLs). Items carry their descriptions, aliases and sitelinks (so there is no separate description sync: until the first full pass has run, the comparison view shows no descriptions), and time values keep their precision. It never talks to QLever. Humans (Q5) are the exception to "every item": of Wikidata's ~13M, only about 2.6M are imported: game people (~20k) — those a mirrored video game (or developer, publisher, …) links to, those whose occupation is a game one (video game developer, designer, programmer, esports player, …), and those with a game-database person id (MobyGames, Giant Bomb, …) — anime staff (~17k), anyone with an AniList or MyAnimeList person id, and Olympians (~254k), anyone with an Olympedia, Olympics.com, Sports-Reference, InterSportStats or The-Sports.org id, and authors, musicians and other creators (~470k): novelists, poets, comics artists, mangaka, voice actors, animators, board and RPG designers, and anyone with an ISFDB, Goodreads, LibraryThing, Discogs, AllMusic or similar person id, and film/TV people (~560k), anyone with an IMDb id, composers (~130k), and athletes (~1.2M more): the 40 largest sports occupations (association football, basketball, athletics, …). The bare "writer" occupation is still left out, as are smaller sports. The lists are PEOPLE in src/lib/import-classes.ts. The linked set is read from the mirror before each pass, so a newly linked person arrives with the next week's dump and one that no longer qualifies is pruned a week later. Reading it is a minute-long JSON_TABLE query on a full mirror, so the pass's first worker runs it and stores the result (dump_import_linked) for the others. After a complete pass it deletes items the dump no longer contains (merged away, deleted, retyped) and settles their open candidates; it refuses to drop more than 20% of the mirror at once unless DUMP_PRUNE_FORCE=1.

Each row records the Wikidata revision it was converted from and the converter version (server/converter-version.ts) that converted it. A later pass skips an item without parsing it when the dump has the same revision and the version is still current, and only restamps the row as seen. Most items aren't edited from one week to the next. When you change what the conversion stores (entityToItem, externalIdRows, prepareRow), server/converter-version.test.ts fails with a new hash to append to CONVERTER_OUTPUTS, and the next pass reconverts every item. DUMP_FULL=1 parses every item regardless, as an escape hatch.

On Toolforge the dump is on the read-only NFS mount, which a build-service job only sees with --mount all:

# quick timing/validation run: stop after 2000 items, no pruning
toolforge envvars create DUMP_LIMIT 2000
toolforge jobs run import-dump-test --image tool-mna/tool-mna:latest \
  --command "node --max-old-space-size=3072 jobs/import-dump.ts" --mount all --mem 4Gi --cpu 1 --emails onfailure
toolforge envvars delete DUMP_LIMIT

A full pass (156 GB gzip, ~1.6 TB inflated) is split across six jobs; see below. jobs.yaml schedules them weekly (Wednesdays, after the Tuesday dump). Locally, point WIKIDATA_JSON_DUMP at any .json.gz / .json in the same format (one entity per line; the .bz2 dump is refused as far too slow).

Splitting the pass across jobs

The pass is CPU-bound (inflate plus the line scan), and a single job reading the whole file takes hours, but the dump's .gz is a concatenation of ~2,000 independent gzip members (the generator gzips each 65k-entity batch on its own and cats them together), so the file can be read in slices, each widened to whole members so that the slices cover the file exactly once.

The weekly jobs are six identical workers sharing a queue of 256 such slices ("segments", ~600 MB of .gz each, about 8 gzip members) in dump_import_segments. Each worker claims the lowest free segment, scans it, marks it done, and claims the next until none is left, so a worker on a fast node takes on more of the file instead of sitting idle while one on a busy node finishes a fixed share. To run them by hand:

for i in 1 2 3 4 5 6; do
  toolforge jobs run import-dump-$i --image tool-mna/tool-mna:latest \
    --command "node --max-old-space-size=384 jobs/import-dump.ts --worker import-dump-$i" \
    --mount all --mem 512Mi --cpu 2 --emails onfailure
done

Every worker upserts on its own and stamps its items with the dump (the date in the file name); the pruning happens once, by whichever worker marks the dump's last segment done. Workers can be added or removed without touching the others; each only needs its own name (--worker <name>), which a retry of the same job keeps. A claim is refreshed every minute while its segment is scanned: a retried job takes back the segment its dead predecessor held, and any claim silent for 10 minutes is taken over by another worker, so a crash costs one segment, not a sixth of the dump. A worker with nothing left to claim waits for the other workers' segments, to take over any that go stale. Starting workers on a dump that is already fully imported does nothing, unless DUMP_PRUNE_FORCE=1 (which runs the prune the 20% cap refused, without rescanning) or DUMP_REDO=<token> (which imports it again, e.g. with DUMP_FULL=1 after a converter change). Give every worker of the re-import the same token, and a new one for the next: the set is reset once per token, so a worker that starts after the others have finished (it sat Pending, or it's a retry) doesn't start the whole dump over, and neither does a DUMP_REDO left behind in the tool's envvars. Every worker must agree on the segment count (DUMP_SEGMENTS, default 256).

Two CPUs per worker let the inflate (on libuv's threadpool) and the line scan (on the main thread) overlap rather than take turns on one core, and a worker keeps both busy (~1.87 of 2 CPUs), so the pass speeds up with more workers, not more CPUs per worker. With fixed slices, 4 x 2 CPUs finished in ~35-40 minutes, faster than 8 x 1 CPU (~50 minutes, paced by slices that landed together on busy nodes). Speed varies along the file: stretches dense with in-scope items run at ~100 MB/s inflated per worker (parsing them is the bottleneck), sparse ones at ~240 MB/s (inflate-bound).

The 12 CPUs and 3Gi this needs are more than the default tool quota (2 CPUs in total, shared with the web service), so it needs a quota increase.

--shard i/N instead reads one fixed N-th of the file, for local and one-off runs: N jobs with the same N cover the file once, and the one that completes the set prunes. Without --worker or --shard the job reads the whole file, which is --shard 1/1.

Resolving sitelink redirects

When two items link different pages on the same wiki, Wikidata refuses to merge them — but often one page is only a redirect to the other. The dump's "sitelink to redirect" badges don't say where a redirect points, and they're missing on pages that became redirects after being linked. So pnpm job:resolve-sitelinks (jobs/resolve-sitelinks.ts → server/sitelink-redirects.ts, daily, after the weekly hunt's time) looks up every clashing sitelink on an open candidate in that wiki's page / redirect tables on the Wiki Replicas and stores the answer in sitelink_pages. It connects to <wiki>.analytics.db.svc.wikimedia.cloud (database <wiki>_p) with the tool's TOOL_REPLICA_USER / TOOL_REPLICA_PASSWORD, one wiki at a time. Pages are re-checked after a week.

The replicas are only reachable from Toolforge. To run the job from a dev machine, tunnel through the bastion and point REPLICA_HOST / REPLICA_PORT / REPLICA_DB at the tunnel ({wiki} is replaced with the site id), with the replica credentials from the tool's replica.my.cnf in the TOOL_REPLICA_* variables.

Item creators

Reviewers find it useful to know who created each item in a pair, when, and how: a duplicate made by a drive-by account or a bulk import is more likely an accident than one made by an editor they know. The detail page shows each item's creator (with their edit count and bot flag), the creation date, and the tool behind it when the edit summary or tags name one (QuickStatements batch, OpenRefine / EditGroups batch, Mix'n'match, …).

pnpm job:resolve-creations (jobs/resolve-creations.ts → server/item-creations.ts, daily, after the weekly hunt's time) reads each open candidate's items' first revision from the wikidatawiki replica into item_creations, with the same replica settings as above. Only new items are read, plus rows older than 30 days, to refresh the creator's stats. A pair viewed before the job has reached it is looked up through the Action API instead (GET /api/candidates/:id/creations) and cached the same way.

Merges made outside the app

An item merged on Wikidata by someone else leaves the mirror only when the next weekly dump import prunes it, so until then its pairs stay open and a merge attempt fails with "Cannot access content, revision may be deleted." pnpm job:resolve-merges (jobs/resolve-merges.ts → server/outside-merges.ts, nightly, before the weekly hunt's time) looks every open candidate's items up in the wikidatawiki replica. A pair with a side that is now a redirect is settled as merged; one with a side that was deleted, as dismissed. Those items' rows leave the mirror, as with a merge made in the app.

Claude review

Once a month, jobs/llm-review.ts (→ server/llm-review-job.ts) asks Claude, through the Message Batches API, about open pairs and hides the ones two models agree aren't duplicates. It never edits Wikidata and never changes a score; a hidden pair is listed under "Hidden by automation" and can be reopened.

• 1st of the month, first-pass: Claude Haiku reviews every open pair that has never had a review.
• 2nd, confirm: Claude Opus reviews only the pairs Haiku called "different", at any probability.
• Hourly on the 1st to 3rd, collect: writes back whatever batches have ended to llm_reviews. When Opus also says "different" with a probability under LLM_CONFIRM_MAX_P (0.1), the pair moves to auto_dismissed, unless a human resolved it first.

None of the jobs waits on the API: each submits or collects, then exits. A batch ends within 24 h, usually within the hour, so the confirm run a day later finds Haiku's answers in. A pair is reviewed once per stage; only a failed review is sent again, up to three times. Spending stops at LLM_MONTHLY_BUDGET_USD ($200, both models), counting each request at its model's average cost so far. When the API refuses for lack of credit or the workspace spend limit, the run logs it and exits cleanly; the pairs it didn't send wait for next month. Set the key once with toolforge envvars create ANTHROPIC_API_KEY. For a small first run, or to catch up by hand:

toolforge jobs run llm-review-once --image tool-mna/tool-mna:latest \
  --command "node jobs/llm-review.ts first-pass --max-pairs 300 --wait" --mem 1Gi
toolforge jobs logs llm-review-once -f

(--filelog needs --mount all, which this job otherwise doesn't need.)

Deploying to Toolforge

The Build Service builds the image from the GitHub repo directly — it clones the repo itself, so there is no checkout on the bastion to git pull; each build pulls the latest default branch (pass --ref <branch> to build another). Watch it with toolforge build show; when it succeeds toolforge images lists the tool-mna/tool-mna:latest image the jobs and web service reference:

toolforge build start https://github.com/connorshea/mergers-and-acquisitions

Once the build succeeds, restart the web service to pick up the new image — but run the migrate job (below) first if the build includes new migrations. The new code may query tables or columns that only exist after the migration, so restarting before migrating can break the live site:

toolforge webservice restart

A migration that drops or renames columns the running code still uses (such as item_sync, which moves the dump import's bookkeeping off items) breaks the old web service the moment it lands, and an old import-dump-* worker writing across it would leave rows the new code doesn't know about. For those, stop the web service and check that no import-dump-* job is running (toolforge jobs list) before migrating, then start it again on the new image:

toolforge webservice stop
toolforge jobs run migrate --image tool-mna/tool-mna:latest \
  --command "node scripts/migrate.ts" --wait
toolforge webservice buildservice start --mount none

Then apply migrations as a one-off job, load the mirror with the import-dump-1..6 worker jobs above, start the web service (toolforge webservice buildservice start --mount none; the build service requires an explicit mount flag, and the web process needs no NFS), and load the schedule from jobs.yaml (below). A finished one-off job deletes itself, so the same command reruns it:

toolforge jobs run migrate --image tool-mna/tool-mna:latest \
  --command "node scripts/migrate.ts" --wait

Loading the job schedule

With no checkout on the bastion, jobs.yaml isn't there either: fetch it from main on GitHub, then load it. Run this as the tool (become mna) whenever jobs.yaml changes:

curl -fsSL https://raw.githubusercontent.com/connorshea/mergers-and-acquisitions/main/jobs.yaml \
  -o ~/jobs.yaml && toolforge jobs load ~/jobs.yaml

load recreates every job whose definition changed, which kills a run of it that's in progress, so don't load while the import-dump-* workers are running (check toolforge jobs list). It stops at the first job it can't create, so read its output to the end. GitHub's raw file can lag a merge by a few minutes.

On Wednesdays, after the dump lands on Tuesday, the jobs run in this order (UTC):

Time	Job	Notes
06:00	import-dump-1..6	usually done in 30-40 min; 3 h timeout
09:00	sync-properties	properties + class_ancestors for the new dump
09:15	sync-place-ancestors	place_ancestors, so the hunt scores with them
10:00	hunt, sync-entity-labels	the hunt only runs weekly; its input is weekly
12:00	resolve-sitelinks	daily; 12:00 so it follows the hunt
12:30	resolve-creations	daily; likewise

resolve-merges (02:30) and prune-sessions (02:45) run nightly. The hunt used to run nightly too, but it only rescored an almost unchanged mirror on the six nights between imports. So a change to scoring (src/lib/compare.ts) reaches the queue at the next Wednesday's hunt; to apply it sooner, deploy it and run the hunt as a one-off job (a different name from the scheduled hunt, and the same heap, memory and CPU):

toolforge jobs run hunt-manual --image tool-mna/tool-mna:latest \
  --command "node --max-old-space-size=1536 jobs/hunt.ts" --mem 2Gi --cpu 1 \
  --emails onfailure

Don't start it while the import-dump-* workers are running: it would score a half-imported mirror.

Job and Procfile commands call node directly rather than npm run …: the launch image has npm but not pnpm, and npm 11 refuses to run scripts because devEngines.packageManager (set for Vite+) names pnpm.

The DB is a MariaDB 12.2 database on Trove (Cloud VPS), created with CHARACTER SET utf8mb4 COLLATE utf8mb4_bin (see the collation note above); connection details come from the DB_* env vars.

The server checks the schema before it binds its port (server/preflight.ts): if the database has fewer migrations applied than db/migrations contains, it logs DB schema is behind: N migrations in db/migrations, M applied. and exits with status 1, so run the migrate job before (re)starting the web service after a deploy that adds a migration. An unreachable database is retried a few times and then also exits, leaving the restart to Kubernetes.