Prevent duplicate database records for the same file provider item - #10020

Iron out logical-path duplicates in RealmItemMetadata and prevent them from re-accumulating.

Customers on unstable connections see arbitrary folders in the file provider appear renamed with a " 2" suffix. macOS does this when the file provider returns two siblings with identical names — the file system can only represent one. A customer Realm confirms the cause: two non-deleted rows at the same (account, serverUrl, fileName) but different ocId and syncTime ~2h apart. RealmItemMetadata uses ocId as its primary key, so Realm's update: .all / .modified upserts dedupe by ocId alone. When the server returns the same logical path with a fresh ocId (restore-from-trash, another client recreating the item
during reconnect, the upload finalizer assigning a server-issued ocId), a second row is inserted beside the first.

No schema changes — Realm is being replaced medium-term. This is a focused code-only hotfix.

Changes

• New FilesDatabaseManager+Deduplication.swift — two helpers:
  • evictLogicalDuplicates(of:in:now:) — within an active write transaction, soft-deletes every non-deleted row at the incoming row's logical address with a different ocId. Skips in-flight rows (status ≠ normal) and lock files of local origin. Bumps syncTime on evicted rows so pendingWorkingSetChanges
    signals the framework.
  • cleanupPreexistingLogicalDuplicates() — one-shot startup pass: buckets every non-deleted, non-lock row by (account, serverUrl, fileName), keeps the row with the most recent syncTime per bucket (lexicographically greater ocId breaks ties), soft-deletes the rest. Opens a write transaction only when
    collisions exist — zero cost for clean databases.
• FilesDatabaseManager.swift
  • addItemMetadata runs evictLogicalDuplicates inside its existing write transaction, so every caller of the function (Item create/modify/fetch/trash/lockfile, materialised observer, paginated enumeration via addItemMetadataPreservingLocalState, the trash-marking pass in Enumerator.swift) is covered.
  • depth1ReadUpdateItemMetadatas pre-evicts for every item in metadatasToCreate and metadatasToUpdate inside its existing transaction; the update path is included defensively for rename targets that collide with a third row.
  • addItemMetadataPreservingLocalState falls back to a logical-address lookup when the ocId lookup misses, and carries over keepDownloaded / downloaded / visitedDirectory / lockToken if exactly one candidate exists. Without this, an ocId rotation would silently drop the user's "Always keep
    downloaded" state — the regression First item in folder not marked for keeping downloaded #9923 originally fixed for the non-rotation case. The merge is conservative: when multiple candidates exist (i.e. the DB is already in the duplicated state), no merge is performed and eviction prunes both prior rows.
  • init calls cleanupPreexistingLogicalDuplicates() after Realm initialisation succeeds.

Edge cases

• In-flight rows are never touched in either path; soft-deleting one would yank the file out from under a pending NSURLSession task. Skips are logged at error level for support visibility.
• Lock files of local origin are excluded as both candidates and evictors, mirroring the existing exclusion in pendingWorkingSetChanges.
• Same-ocId upserts remain a no-op for eviction (the upserted row is excluded from the collision query).
• Working-set signalling fires only for rows the framework has previously been told about via enumeration. Duplicates that were never enumerated to the framework are still corrected in the database, but they don't surface as a "delete" event — which is correct, since the framework has no reference to delete.

Out of scope

Eviction inside renameItemMetadata and renameDirectoryAndPropagateToChildren is deliberately not part of this hotfix — the recursive directory rename has subtle correctness traps (children's destination addresses are computed in the same transaction) that aren't worth carrying here. Any rename-induced
collision will heal on the next enumeration of the destination directory.

Risks

1. Soft-deletion signals the framework via pendingWorkingSetChanges only when the row has been previously enumerated to the framework. Duplicates that were never enumerated are healed in the DB but don't surface as a "delete" event.
2. The transient window where addItemMetadata collides with an in-flight sibling leaves two live rows briefly; the next post-completion write heals it. Acceptable for a hotfix targeting persistent duplicates.
3. The upload finalizer in Item+Modify.swift now correctly evicts the prior local-ocId row when the server assigns a new ocId — desired, but a behavioural change from the prior code that left both rows present.
4. The medium-term Realm replacement should treat (account, serverUrl, fileName) as a unique constraint and handle ocId rotation at the schema level. This hotfix addresses the symptom, not the underlying race.