Legacy motif share query returns soft-deleted entries #93

New issue

Closed

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

claude-bot commented

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

Contributor

Severity: Low · Confidence: High · Effort: XS · Category: security

Evidence

api/src/routes/share.ts:65-80 - legacy motif_collaborators branch selects entries with WHERE e.user_id = $2 and no deleted_at IS NULL.
api/src/routes/share.ts:138 (project-share path) and api/src/routes/public.ts:428 (public detail) filter correctly.

Problem
The legacy share-token entry query omits the soft-delete filter every other read path applies.

Impact
A holder of a legacy (UUID) motif share link sees entries that were trashed but still linked to the motif. Bounded by possession of the unguessable token.

Fix
Add AND e.deleted_at IS NULL to the legacy entries (and connections) query.

Acceptance criteria

A soft-deleted entry no longer appears via a legacy motif share token.

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

**Severity:** Low · **Confidence:** High · **Effort:** XS · Category: security **Evidence** - `api/src/routes/share.ts:65-80` - legacy `motif_collaborators` branch selects entries with `WHERE e.user_id = $2` and no `deleted_at IS NULL`. - `api/src/routes/share.ts:138` (project-share path) and `api/src/routes/public.ts:428` (public detail) filter correctly. **Problem** The legacy share-token entry query omits the soft-delete filter every other read path applies. **Impact** A holder of a legacy (UUID) motif share link sees entries that were trashed but still linked to the motif. Bounded by possession of the unguessable token. **Fix** Add `AND e.deleted_at IS NULL` to the legacy entries (and connections) query. **Acceptance criteria** - [ ] A soft-deleted entry no longer appears via a legacy motif share token. --- _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:29 +00:00

claude-bot added the

bug

label

2026-07-15 19:51:29 +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 commented

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

Author

Contributor

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

Added AND e.deleted_at IS NULL to the legacy entries query in share.ts, matching the project-share path.

Broader than filed — the motif itself leaked too. The legacy motif_collaborators lookup joined motifs m without checking m.deleted_at, so a trashed motif was still served in full (title, description, and its entries) to a legacy token holder. The new path guards this (p.deleted_at IS NULL / m.deleted_at IS NULL); the legacy one didn't. Added AND m.deleted_at IS NULL there as well.

On the connections query: no filter needed. connections has no deleted_at column (soft-delete exists only on projects, motifs, entries per migration 026), and the query is already scoped by entryIds — which now excludes trashed entries, so connections touching them drop out transitively.

Acceptance criteria:

A soft-deleted entry no longer appears via a legacy motif share token.
(bonus) A soft-deleted motif no longer resolves via its legacy share token at all.

CI run 191 green.

Fixed in `763aa0b` (v7.2.0 wave 1). Added `AND e.deleted_at IS NULL` to the legacy entries query in `share.ts`, matching the project-share path. **Broader than filed — the motif itself leaked too.** The legacy `motif_collaborators` lookup joined `motifs m` without checking `m.deleted_at`, so a **trashed motif** was still served in full (title, description, and its entries) to a legacy token holder. The new path guards this (`p.deleted_at IS NULL` / `m.deleted_at IS NULL`); the legacy one didn't. Added `AND m.deleted_at IS NULL` there as well. On the connections query: no filter needed. `connections` has no `deleted_at` column (soft-delete exists only on `projects`, `motifs`, `entries` per migration 026), and the query is already scoped by `entryIds` — which now excludes trashed entries, so connections touching them drop out transitively. **Acceptance criteria:** - [x] A soft-deleted entry no longer appears via a legacy motif share token. - [x] (bonus) A soft-deleted *motif* no longer resolves via its legacy share token at all. CI run 191 green.

claude-bot closed this issue

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