[VS-NfD] Remove trashed content from the search index instead of filtering at query time #195

New Issue

fable-5 · 2026-07-30T01:43:32+02:00

fable-5 commented

2026-07-30 01:43:32 +02:00

Plan reference: 20-massnahmenplan.md -> Phase 2
ADR: n/a
Effort: M (2 AT)
Depends on: —

Context

The full-text index holds plaintext of trashed pages and ponds; only the
query hides them. Any future query path that forgets the filter leaks
content, and the index is a content copy that a deletion concept has to
account for.

Current state

apps/api/src/search/postgres-search.provider.ts:41 — the weighted
tsvector lives on page_content_cache.search_vector.
:142–143 — the search query joins
pages p ON … p.deleted_at IS NULL and
ponds po ON … po.deleted_at IS NULL: query-side filtering.
:73 shows the vector can be nulled per page
(UPDATE page_content_cache SET search_vector = NULL WHERE page_id = …).

Acceptance criteria

Trashing a page or pond clears the search vector of the affected
pages; restoring rebuilds it.
The query-side deleted_at IS NULL guards stay (defence in
depth) and a test asserts both layers independently.
Test: a trashed page's unique term is absent from the index rows
themselves, not merely from results; restore makes it findable again.
A one-off backfill clears vectors of already-trashed content.
docs/architecture/security.md records that the index holds no
trashed content (the file has no search section today — add one, or
extend §"Content & upload security").

Out of scope

Encrypting or removing the plaintext cache for live pages, and any change
of search engine.

**Plan reference:** `20-massnahmenplan.md` -> Phase 2 **ADR:** n/a **Effort:** M (2 AT) **Depends on:** — ## Context The full-text index holds plaintext of trashed pages and ponds; only the query hides them. Any future query path that forgets the filter leaks content, and the index is a content copy that a deletion concept has to account for. ## Current state - `apps/api/src/search/postgres-search.provider.ts:41` — the weighted `tsvector` lives on `page_content_cache.search_vector`. - `:142–143` — the search query joins `pages p ON … p.deleted_at IS NULL` and `ponds po ON … po.deleted_at IS NULL`: query-side filtering. - `:73` shows the vector can be nulled per page (`UPDATE page_content_cache SET search_vector = NULL WHERE page_id = …`). ## Acceptance criteria - [ ] Trashing a page or pond clears the search vector of the affected pages; restoring rebuilds it. - [ ] The query-side `deleted_at IS NULL` guards **stay** (defence in depth) and a test asserts both layers independently. - [ ] Test: a trashed page's unique term is absent from the index rows themselves, not merely from results; restore makes it findable again. - [ ] A one-off backfill clears vectors of already-trashed content. - [ ] `docs/architecture/security.md` records that the index holds no trashed content (the file has no search section today — add one, or extend §"Content & upload security"). ## Out of scope Encrypting or removing the plaintext cache for live pages, and any change of search engine.

fable-5 added this to the M24 — VS-NfD: security quick wins milestone 2026-07-30 01:43:32 +02:00

fable-5 added the

area:storage

effort:M

vs-nfd

labels 2026-07-30 01:43:32 +02:00

fable-5 referenced this issue from a commit

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

#195: trashed content leaves the search index itself

fable-5 referenced this issue

2026-07-30 14:07:54 +02:00

#195: trashed content leaves the search index itself #250

fable-5 commented

2026-07-30 14:22:50 +02:00

Delivered by PR #250 (960a806), merged to main fast-forward. CI run 497 green. Both layers proven independently (index rows + query guards); backfill migration included. Closing.

Delivered by PR #250 (960a806), merged to main fast-forward. CI run 497 green. Both layers proven independently (index rows + query guards); backfill migration included. Closing.

fable-5 closed this issue

2026-07-30 14:22:50 +02:00

Sign in to join this conversation.