homelab/stack
1
0
Fork 0
Code Issues 114 Pull Requests 1 Actions Packages Projects 1 Releases Wiki Activity

zotero: rid the library of duplicates — merge-based dedupe + close the three ingest/ops holes #665

New Issue
Closed
opened 2026-08-26 15:31:40 +00:00 by kert · 1 comment
kert commented 2026-08-26 15:31:40 +00:00
Owner
Copy Link

Symptom: the Zotero library holds ~9,000 surplus duplicate items (plus a handful of upstream bib duplicates). Full evidence + implementation plan: docs/superpowers/plans/2026-08-26-zotero-dedupe.md.

Root causes (measured 2026-08-26 on a DB snapshot)

Class A — 2026-05-14 key-matched restore (~8,981 surplus items in 8,955 same-URL clusters).
Timeline: pre-rekey.bak taken 05-13 16:03 → commit 8149da5 (05-13 16:40) dropped L from the Zotero key charset and live rows were rekeyed in place → on 05-14 13:41–13:46 an uncommitted one-off restored rows from the pre-rekey backup matching by key: every rekeyed row looked "missing" and was re-inserted under its old L-key with original dateAdded (8,938/8,981 twins carry an L; itemIDs ~371k–376k; 22,734 child rows came along). bib.sync matches by URL and updates only the first-found copy, so twins rot (avg 5.1 tags vs 15.5) — but ~23% of sampled clusters have a twin holding MORE child notes than the keeper, so blind deletion loses data.

Class B — listserv re-sends (bib-level, 17 title clusters / 34 surplus).
Mail ingest identity is email:<Message-ID>; CMS re-sends identical content under fresh Message-IDs on different days. The nightly --dedupe-collection Cmsupdates pass groups on (title, date), so cross-day re-sends never group — and it deletes without merging.

Class C — pre-#624 ingest URL dups in bib (3 clusters, ~17 surplus rows, e.g. aco-reach ×12), mirrored into Zotero.

Class D — 2,080 same-DOI clusters with zero PMID collisions = PubMed's own dual records + Class A twins; residue after A/C is a human triage call, not an auto-merge.

Not duplicates (protected): ~11k Zotero-only personal/archive items (audio, dictionary entries, artwork…); same-title different-PMID records (Cochrane updates); page-range junk in url fields.

Fix (per the plan)

  1. zot.merge engine: fidelity-preserving merge (union tags/collections/relations, adopt missing notes/attachments, keep-earliest, dry-run + JSON merge logs, skip type-mismatched clusters).
  2. find_item_by_url gets ORDER BY itemID; nightly zotero-sync runs library-wide --merge-dupes instead of the lossy title+date collection pass.
  3. Mail ingest aliases re-sends by (title, body-sha1) into extra_json.alias_mids instead of creating new items.
  4. One-off bib legacy cleanup (Classes B+C) keyed off the same merge decisions.
  5. Runbook: rehearse on snapshot → backup → live run in the zotero-stop window → census must show zero same-URL clusters → Class D triage report.

Prevention note: the 05-14 one-off was never committed; the guard is structural — restores/merges must match by URL (not key), and any bulk op ends with the dry-run cluster census.

**Symptom:** the Zotero library holds ~9,000 surplus duplicate items (plus a handful of upstream bib duplicates). Full evidence + implementation plan: `docs/superpowers/plans/2026-08-26-zotero-dedupe.md`. ## Root causes (measured 2026-08-26 on a DB snapshot) **Class A — 2026-05-14 key-matched restore (~8,981 surplus items in 8,955 same-URL clusters).** Timeline: `pre-rekey.bak` taken 05-13 16:03 → commit 8149da5 (05-13 16:40) dropped `L` from the Zotero key charset and live rows were rekeyed in place → on 05-14 13:41–13:46 an uncommitted one-off restored rows from the pre-rekey backup **matching by key**: every rekeyed row looked "missing" and was re-inserted under its old L-key with original dateAdded (8,938/8,981 twins carry an `L`; itemIDs ~371k–376k; 22,734 child rows came along). `bib.sync` matches by URL and updates only the first-found copy, so twins rot (avg 5.1 tags vs 15.5) — but ~23% of sampled clusters have a twin holding MORE child notes than the keeper, so blind deletion loses data. **Class B — listserv re-sends (bib-level, 17 title clusters / 34 surplus).** Mail ingest identity is `email:<Message-ID>`; CMS re-sends identical content under fresh Message-IDs on different days. The nightly `--dedupe-collection Cmsupdates` pass groups on (title, date), so cross-day re-sends never group — and it deletes without merging. **Class C — pre-#624 ingest URL dups in bib (3 clusters, ~17 surplus rows,** e.g. aco-reach ×12), mirrored into Zotero. **Class D — 2,080 same-DOI clusters with zero PMID collisions** = PubMed's own dual records + Class A twins; residue after A/C is a human triage call, not an auto-merge. **Not duplicates (protected):** ~11k Zotero-only personal/archive items (audio, dictionary entries, artwork…); same-title different-PMID records (Cochrane updates); page-range junk in `url` fields. ## Fix (per the plan) 1. `zot.merge` engine: fidelity-preserving merge (union tags/collections/relations, adopt missing notes/attachments, keep-earliest, dry-run + JSON merge logs, skip type-mismatched clusters). 2. `find_item_by_url` gets `ORDER BY itemID`; nightly zotero-sync runs library-wide `--merge-dupes` instead of the lossy title+date collection pass. 3. Mail ingest aliases re-sends by (title, body-sha1) into `extra_json.alias_mids` instead of creating new items. 4. One-off bib legacy cleanup (Classes B+C) keyed off the same merge decisions. 5. Runbook: rehearse on snapshot → backup → live run in the zotero-stop window → census must show zero same-URL clusters → Class D triage report. **Prevention note:** the 05-14 one-off was never committed; the guard is structural — restores/merges must match by URL (not key), and any bulk op ends with the dry-run cluster census.
kert added the databibquality labels 2026-08-26 15:31:40 +00:00
kert commented 2026-08-26 19:56:06 +00:00
Author
Owner
Copy Link

Executed and verified 2026-08-26. Full record: docs/superpowers/plans/2026-08-26-zotero-dedupe.md (Outcome section) and .state/zotero-dedupe/.

Live run (zotero stop window ~15:14–15:29 EDT, backups *.pre-dedupe-20260826.bak):

  • bib legacy dedupe: 31 rows removed, 14 Message-IDs aliased (3 url + 9 email groups).
  • Zotero URL merge: 8,980 duplicate items merged across 8,954 clusters in 326 s — preserving 1,373 child notes, 1,779 attachments, 822 collection memberships that lived only on the duplicates. Email-pair pass merged 14 more.
  • Post-run: 199,420 → 190,426 top-level items; zero merge-eligible same-URL clusters remain; protected personal/archive items and PRISMA stage: tags unchanged; FK check clean; the sample PMIDs now unique.

Prevention shipped: zot.merge engine (merge, never blind-delete; pre-delete decision log); deterministic find_item_by_url; nightly --merge-dupes replaces the lossy title+date collection pass (logs land host-durable in data/zotero/data/dedupe-logs/); mail ingest aliases re-sends by body hash (with empty-body guard + self-exclusion); body_sha1 backfilled on 217 legacy email keepers.

Accepted residuals: one intentional type-mismatch cluster (GTVEBJ3T document / 76PA8PRE webpage @ qpp.cms.gov resource-library — shows as the nightly's standing skipped_type_mismatch: 1); 185 legacy email items with truncated abstracts can each spawn at most one new item on their next re-send; 3 same-DOI clusters (1 genuine PubMed dual record, 2 DOI-field noise) left for human triage in .state/zotero-dedupe/doi-clusters.md.

Rollback: restore the three .bak files inside a zotero-stop window.

**Executed and verified 2026-08-26.** Full record: `docs/superpowers/plans/2026-08-26-zotero-dedupe.md` (Outcome section) and `.state/zotero-dedupe/`. **Live run** (zotero stop window ~15:14–15:29 EDT, backups `*.pre-dedupe-20260826.bak`): - bib legacy dedupe: 31 rows removed, 14 Message-IDs aliased (3 url + 9 email groups). - Zotero URL merge: **8,980 duplicate items merged** across 8,954 clusters in 326 s — preserving 1,373 child notes, 1,779 attachments, 822 collection memberships that lived only on the duplicates. Email-pair pass merged 14 more. - Post-run: 199,420 → 190,426 top-level items; **zero merge-eligible same-URL clusters remain**; protected personal/archive items and PRISMA `stage:` tags unchanged; FK check clean; the sample PMIDs now unique. **Prevention shipped:** `zot.merge` engine (merge, never blind-delete; pre-delete decision log); deterministic `find_item_by_url`; nightly `--merge-dupes` replaces the lossy title+date collection pass (logs land host-durable in `data/zotero/data/dedupe-logs/`); mail ingest aliases re-sends by body hash (with empty-body guard + self-exclusion); `body_sha1` backfilled on 217 legacy email keepers. **Accepted residuals:** one intentional type-mismatch cluster (GTVEBJ3T document / 76PA8PRE webpage @ qpp.cms.gov resource-library — shows as the nightly's standing `skipped_type_mismatch: 1`); 185 legacy email items with truncated abstracts can each spawn at most one new item on their next re-send; 3 same-DOI clusters (1 genuine PubMed dual record, 2 DOI-field noise) left for human triage in `.state/zotero-dedupe/doi-clusters.md`. Rollback: restore the three `.bak` files inside a zotero-stop window.
kert closed this issue 2026-08-26 19:56:06 +00:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
api
REST API (FastAPI)
 auth
Authentication and authorization
 bib
Bibliography, Zotero, citations
 ci
CI/CD pipelines and Woodpecker plugins
 cli
CLI commands and UX
 cloud
Cloud provider abstraction (AWS, GCP, Azure, self-hosted)
 config
Configuration and wiring
 data
Data loading, CCLF, BCDA, seeds
 databricks
Databricks jobs and deployment
 docs
Documentation and generated references
 image:api
image:docs
image:mc
image:notebooks
image:zotero
infra
lakehouse
Nessie, Polaris, Trino, Iceberg
 llm
llm module — local RAG over comments + Zotero corpus
 notebooks
marimo notebook errors (auto-filed by nb-watcher/nb-integration)
 packaging
Python packaging, optional deps, build system
 pfs
pipeline
Pipeline execution and validation
 pipeline:ci
pipeline:deploy
pipeline:harden
pipeline:infra-ci
pipeline:rebuild-all
pipeline:release
qpp
quality
Testing, coverage, error handling
 step:build
step:deploy
step:hadolint
step:lint
step:publish
step:scan
step:test
step:validate
styles
Charts, figures, visual theming
 tree
Project directory structure and file organization
No Label bib data quality
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
kert renovate-bot
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: homelab/stack#665
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?