Media library: feature gaps and "this file already exists" (2026-10-12)

Status (2026-10-13): ALL BUILT AND LIVE — phases A–H of the approved plan, shipped phase by phase (§7 has versions, rollbacks and what changed from this design). Founder asked for (1) the Library's feature gaps, thought through as a product designer, and (2) whether uploading a file that's already in Media can warn "this file already exists" — in every service, with the other cases that come with it. Owner: founder decided §5 on 2026-10-12 (scope: everything incl. "later"; storage: meter only; duplicate rules as recommended; ship each phase); build session implemented. Facts as of 2026-10-12, read from apps/media-service, packages/core-media, apps/seller-dashboard/src/modules/media, components/media/* and every upload surface in the dashboard.

1. Short answer: can we warn "this file already exists"?

Yes. Same mechanism Google Drive, Dropbox and iCloud use: a fingerprint of the file's contents (SHA-256). Two files with the same fingerprint are the same file, byte for byte, whatever they're called.

• The browser computes the fingerprint of the picked file before uploading anything (Web Crypto, about 50 ms for a 10 MB file; the file never leaves the device for the check).
• It asks media "do I already have any of these?" and media answers only with files the person could already open — so the check can't be used to discover a colleague's Only-me file.
• If there's a match, a small dialog shows the existing file (preview, name, who added it, when) and lets the person use it, upload anyway, or cancel.

What it can catch reliably: the exact same file again — same photo, same PDF, same video, under any name, from any service. What it can't catch with a fingerprint: the same picture saved differently (resized, re-compressed, a screenshot of it, a cropped version). That needs a "visual fingerprint" (perceptual hash) and gives "looks similar" hints with false positives. Possible later for images only (§4, later).

Why it isn't there today: the browser uploads straight to storage (presigned PUT), so media never sees the bytes, and no fingerprint column exists (media_files has no hash/checksum). Both are additive to fix.

2. How duplicate detection works

2.1 Fingerprints

Why two: the image editor re-encodes images (image-editor/lib/canvas.ts:283), so the stored bytes of a cropped/compressed photo never equal the original. Without source_sha256, re-picking the same photo in the Library would never match.

Why source_sha256 only matches your own files: media can't verify it (it never sees the original). If it matched everyone's files, a member could upload an altered image (a swapped payment QR code, an edited invoice scan) while claiming the fingerprint of the real one; a colleague uploading the real one would be offered the altered one. Trusting your own claim about your own files is harmless.

Partial unique index is not wanted (duplicates must stay allowed — "Upload anyway"). Index: (tenant_id, content_sha256) and (tenant_id, uploaded_by, source_sha256), both WHERE status IN ('uploaded','archived').

Existing files get content_sha256 from a one-off janitor job (read bytes → hash → store), batched; today that's ~26 rows.

2.2 The check

POST /files/match { fingerprints: [sha256, …] } (≤ 50) → for each fingerprint, the best match the caller can read:

• same workspace; status uploaded (or archived, reported as such); listable; never delegated (form and mail files are service-managed) and never pending_upload;
• passes canReadMedia (so a colleague's Only-me file is never reported);
• matches content_sha256, or source_sha256 when uploaded_by = caller.

Returns { id, fileName, mimeType, fileSize, visibility, serviceCode, uploadedBy (you / a member), createdAt, status, previewUrl }.

The check is a hint, never a gate: if it fails, times out (2 s) or the server is old, the upload just proceeds.

2.3 When it runs, per surface

Services that need a public link (blog, newsletter, social, brand logo) only consider public matches. A Workspace or Only-me match there is ignored and the upload proceeds — no dialog that ends in "you can't use it".

Choke points: browser uploads all pass through presignUpload (modules/media/api/media.api.ts:223) — via uploadFile and the composer's own hook. Put the check in one helper (findExisting(files)) called by uploadFile's callers' shared hooks (useMediaUploader, useMediaLibrary, use-media-upload, upload-image.ts), not inside uploadFile itself, because the dialog and "use this one" differ per surface.

2.4 The dialog (minimal UI)

One file:

Already in your library
[preview]  logo-final.png · 240 KB
           Added by you · 3 Oct · Workspace
                         [Upload anyway]  [Use this one]

• In the Library, the primary button reads Show it.
• Added by someone else: "Added by a member" (same wording as the detail panel's owner field).
• Esc / ✕ cancels the upload.

Several files (one dialog, not one per file):

3 of 8 files are already in your library
[thumb] logo-final.png        Skip ▾
[thumb] team.jpg              Skip ▾
[thumb] brochure.pdf          Skip ▾      (Skip / Upload anyway)
                         [Upload 5 new files]

2.5 Other cases and what happens

2.6 Not proposed: storage-level dedupe

Making two rows share one stored object would save storage silently, but every delete, visibility move and archive would have to reference-count shared bytes. R2 storage is cheap and the cap is 10 MB per file; the person-facing prompt is the value. Revisit only if storage cost becomes real (and quota G2 is enforced).

3. Gaps that are broken today (fix first)

4. Missing features, prioritised

P1 — core library hygiene

P2

Later

5. Decisions for the founder

1. Same name, different file → no warning (recommended), or warn "a file with this name exists"?
2. source_sha256 (pre-editor fingerprint) matches only your own uploads (recommended, safe), or everyone's (catches more, but a member could plant an altered image under a real one's fingerprint)?
3. In editors' pickers, ignore non-public matches (recommended) — or offer "Make it public and use it" (changes a Library file for everyone)?
4. Order: B1–B11 fixes → Rename + multi-upload + sort → duplicate check (Library first, then every uploader) → Where used → P2. Recommended; Where used could move earlier if "Use this one" in editors ships first.

6. Build sketch for the duplicate check (when approved)

1. Core migration: content_sha256, source_sha256 + two partial indexes (additive; manual-migrations/ + packages/core-database/scripts/ like the storage migration). Run before the media deploy.
2. Media: hash at /complete (read back), ingest-url, /internal/upload; presign accepts sourceSha256 (64 hex chars or ignored); POST /files/match; janitor job backfills content_sha256.
3. Dashboard: lib/fingerprint.ts (hook-free, Web Crypto), findExisting(), DuplicateDialog (single + batch), wired into Library, Overview, useMediaUploader, upload-image.ts, use-media-upload.ts.
4. Tests: match only readable files (Only-me not reported, other workspace not reported, delegated never), source_sha256 only for own files, archived reported as archived, check failure never blocks upload, batch with internal duplicates.

7. Rollout record (2026-10-12/13)

Plan: ~/.claude/plans/can-we-start-planning-misty-kernighan.md. Branch media/library-v2, fast-forwarded to main phase by phase. Founder decisions: everything including "later"; storage meter only (no enforcement); duplicate rules as recommended; ship each phase when verified.

Dashboard: Vercel logicspike-office redeploys on each push (all READY). Every media rollback above is schema-safe: the migration is additive.

In-app checks by the founder, 2026-10-14: passed — upload several files at once, "Already in your library", rename + download under the new name, 7-day share link in a private window, "Used in" on a blog cover, Replace with the previous version in the Archive, tags and stars filters, storage meter, bulk Visibility and zip Download. All checks passed.

Where the build differs from this design

• Blog paste/drop/carousel and the social composer reuse an identical public file silently (own service only) instead of showing a dialog — a dialog mid-typing was noise, and only the same service's files are reused so their visibility can't later change under the post. The Library and the editors' picker show the dialog.
• Replace keeps the previous version (keys swapped, old version archived as " (previous).ext") instead of deleting the old bytes: old public links keep working and restoring undoes it.
• Communication was not deployed: main carries its undeployed "DEPLOY-AFTER-SOAK" fail-closed guard. The brand logo for "Used in" is read through communication's existing GET /v1/sender-settings.
• The zip download uses a small store-only zip writer (lib/zip.ts) rather than a dependency.
• Existing files get content_sha256 from the janitor's 6-hourly run (backfillFingerprints, 200 per run); /internal/janitor/run needs the internal token, which this session doesn't have.

Review of B–H (2026-10-13), all fixed

• A visibility move racing a Replace could leave the file pointing at bytes in the wrong bucket and delete the previous version's bytes: the move now also guards on the key, so whichever lands second is refused.
• "Used in" said "Not used anywhere" for a file made private after being public (its key and old embeds stay): usage-terms now returns the key for every non-delegated file; the gateway answers 404 to browsers on that route, so the key stays server-side.
• An admin's Replace made the admin own the "(previous)" version: it now stays the file owner's.
• Collections "Used in" counted deleted entries and collections.
• A second duplicate prompt overwrote the first, leaving its batch stuck in "checking": prompts now queue. Closing a "looks similar" prompt now uploads (it's a different file); an exact duplicate is still skipped.
• "Used in" was cached for the whole session, so a use added since could be missed: it's kept 30 s, and archive/delete forever/making private always ask fresh.
• Leaving the page didn't stop queued uploads: nothing new starts after unmount.
• A failed Replace left its uploaded version as a stray file (now removed); a refused unstar in the Starred view lost the file from the list (now put back); the storage meter misread unlimited plans (-1); zip names could collide with a file already named "a (2).png".

Gap log (found, not fixed in this round)