rbrooks/TeaLeaves

Watch

Fork 0

media.user_id ON DELETE CASCADE strips attachments from entries that survive user deletion #98

New issue

Closed

opened 2026-07-15 19:51:31 +00:00 by claude-bot · 1 comment

claude-bot commented

2026-07-15 19:51:31 +00:00

Contributor

Severity: Medium · Confidence: High · Effort: S · Category: code (data integrity)

Evidence

api/src/db/migrations/026_projects.sql:85-89 - entries.user_id FK → ON DELETE SET NULL (entries survive account deletion via attribution snapshot).
api/src/db/migrations/004_media.sql:11 - media.user_id . ON DELETE CASCADE.

Problem
Deleting a user preserves their entries (user_id → NULL) but hard-deletes all their media rows, so preserved entries lose their attachments (and files orphan on disk, since the purge worker is the only thing that unlinks files).

Impact
Silent data loss / broken media on entries the schema explicitly intends to preserve. Defeats the v4 attribution-snapshot design.

Fix
New migration changing media.user_id FK to ON DELETE SET NULL (media is already CASCADE-tied to entry_id, the correct lifecycle anchor); reconcile file cleanup so orphaned files are unlinked.

Acceptance criteria

Deleting a user preserves media rows on their retained entries.
No media files are orphaned undeleted by the change.

Filed from the 2026-07-15 codebase audit. Full report: docs/.internal/report-2026-07-15.md (gitignored).

**Severity:** Medium · **Confidence:** High · **Effort:** S · Category: code (data integrity) **Evidence** - `api/src/db/migrations/026_projects.sql:85-89` - `entries.user_id` FK → `ON DELETE SET NULL` (entries survive account deletion via attribution snapshot). - `api/src/db/migrations/004_media.sql:11` - `media.user_id . ON DELETE CASCADE`. **Problem** Deleting a user preserves their entries (user_id → NULL) but hard-deletes all their `media` rows, so preserved entries lose their attachments (and files orphan on disk, since the purge worker is the only thing that unlinks files). **Impact** Silent data loss / broken media on entries the schema explicitly intends to preserve. Defeats the v4 attribution-snapshot design. **Fix** New migration changing `media.user_id` FK to `ON DELETE SET NULL` (media is already CASCADE-tied to `entry_id`, the correct lifecycle anchor); reconcile file cleanup so orphaned files are unlinked. **Acceptance criteria** - [ ] Deleting a user preserves media rows on their retained entries. - [ ] No media files are orphaned undeleted by the change. --- _Filed from the 2026-07-15 codebase audit. Full report: `docs/.internal/report-2026-07-15.md` (gitignored)._

claude-bot added this to the v7.2.0 - Reliability, data integrity & docs milestone

2026-07-15 19:51:31 +00:00

claude-bot added the

bug

label

2026-07-15 19:51:31 +00:00

rbrooks referenced this issue from a commit

2026-07-17 02:28:29 +00:00

fix(v7.2): wave 1 — data integrity (#98, #93, #94, #96)

claude-bot referenced this issue

2026-07-17 02:30:36 +00:00

Media access is uploader-scoped, not project-scoped — collaborator attachments are invisible to the project owner #135

claude-bot commented

2026-07-17 02:31:43 +00:00

Author

Contributor

Fixed in 763aa0b (v7.2.0 wave 1).

Migration 044_media_user_id_set_null.sql mirrors what migration 026 did for entries:

ALTER TABLE media ALTER COLUMN user_id DROP NOT NULL;
ALTER TABLE media DROP CONSTRAINT media_user_id_fkey;
ALTER TABLE media ADD CONSTRAINT media_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;

(user_id was NOT NULL, so dropping that was required too — same as entries in 026.) entry_id stays ON DELETE CASCADE as the lifecycle anchor: media dies with its entry, not with whoever uploaded it.

Acceptance criteria:

Deleting a user preserves media rows on their retained entries.
No media files are orphaned undeleted by the change — this actually fixes the orphaning: previously a CASCADE-deleted row left its file on disk with nothing tracking it (the purge worker only unlinks files it can still see a row for). With the row preserved, the file stays governed by the entry's purge (deleteMediaFile in trashWorker.ts).

Verified live on the dev server after deploy — migration applied ✓, and:

 media_entry_id_fkey | c | FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE CASCADE
 media_user_id_fkey  | n | FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
 user_id  | is_nullable: YES

CI run 191 green (241/241 tests).

⚠️ Important scope note — read before assuming attachments now render: #135.

This stops the data loss, but the preserved media is not yet reachable. Every media route authorizes on media.user_id = <viewer> (media.ts:153, :179, etc.), so user_id IS NULL never matches and the surviving owner still can't load it. Filed as #135 (v7.2.0), which re-scopes media access to the entry's project the way entries were in v4.

That gap already bites today with no deletion involved: a collaborator's uploads are invisible to the project owner right now. I kept it out of this issue deliberately — #98 is a one-migration data-preservation fix; #135 is a multi-route access-control change with its own authorization decisions (notably whether delete stays uploader-only).

Fixed in `763aa0b` (v7.2.0 wave 1). Migration **`044_media_user_id_set_null.sql`** mirrors what migration 026 did for `entries`: ```sql ALTER TABLE media ALTER COLUMN user_id DROP NOT NULL; ALTER TABLE media DROP CONSTRAINT media_user_id_fkey; ALTER TABLE media ADD CONSTRAINT media_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL; ``` (`user_id` was `NOT NULL`, so dropping that was required too — same as `entries` in 026.) `entry_id` stays `ON DELETE CASCADE` as the lifecycle anchor: media dies with its entry, not with whoever uploaded it. **Acceptance criteria:** - [x] Deleting a user preserves media rows on their retained entries. - [x] No media files are orphaned undeleted by the change — this actually *fixes* the orphaning: previously a CASCADE-deleted row left its file on disk with nothing tracking it (the purge worker only unlinks files it can still see a row for). With the row preserved, the file stays governed by the entry's purge (`deleteMediaFile` in `trashWorker.ts`). Verified live on the dev server after deploy — migration applied `✓`, and: ``` media_entry_id_fkey | c | FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE CASCADE media_user_id_fkey | n | FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL user_id | is_nullable: YES ``` CI run 191 green (241/241 tests). --- ⚠️ **Important scope note — read before assuming attachments now render: #135.** This stops the *data loss*, but the preserved media is **not yet reachable**. Every media route authorizes on `media.user_id = <viewer>` (`media.ts:153`, `:179`, etc.), so `user_id IS NULL` never matches and the surviving owner still can't load it. Filed as **#135** (v7.2.0), which re-scopes media access to the entry's project the way entries were in v4. That gap already bites today with no deletion involved: **a collaborator's uploads are invisible to the project owner right now.** I kept it out of this issue deliberately — #98 is a one-migration data-preservation fix; #135 is a multi-route access-control change with its own authorization decisions (notably whether delete stays uploader-only).

claude-bot closed this issue

2026-07-17 02:32:12 +00:00

claude-bot referenced this issue

2026-07-17 02:53:54 +00:00

Add entries(project_id, created_at DESC) index for the project-scoped list query #95

rbrooks referenced this issue from a commit

2026-07-17 16:29:34 +00:00

fix(v7.2): scope media access to the project, not the uploader (#135)

claude-bot referenced this issue

2026-07-17 16:34:03 +00:00

Media access is uploader-scoped, not project-scoped — collaborator attachments are invisible to the project owner #135