[VS-NfD] Store read events in their own table with retention and partitioning #224

New Issue

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

fable-5 commented

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

Plan reference: 20-massnahmenplan.md -> Phase 3, Variante A
ADR: ADR 0023
Effort: M (2 AT)
Depends on: #222

Context

Read events have a different volume profile, a different retention period
and a different legal basis than audit_log. Mixing them would force one
policy onto both.

Current state

audit_log (apps/api/prisma/schema.prisma:74–91) holds auth and admin
events and has no retention job yet (#196) and no partitioning.

Acceptance criteria

Separate table with an index set matched to the expected queries
("who read page X", "what did user Y read", both within a period).
Time-based partitioning, with the partition-maintenance job included
and tested — not left as an operational chore.
Own configurable retention period, independent of #196, with a
documented default and the deletion itself logged.
A Site-Admin query path exists (or its deliberate absence is
documented) — evidence nobody can read is not evidence.
Growth measured and stated (rows and bytes per 1000 reads) so an
operator can size storage.
docs/architecture/data-model.md documents the table.

Out of scope

SIEM forwarding of read events beyond the catalogue from #201, and
tamper-proofing.

**Plan reference:** `20-massnahmenplan.md` -> Phase 3, Variante A **ADR:** ADR 0023 **Effort:** M (2 AT) **Depends on:** #222 ## Context Read events have a different volume profile, a different retention period and a different legal basis than `audit_log`. Mixing them would force one policy onto both. ## Current state - `audit_log` (`apps/api/prisma/schema.prisma:74–91`) holds auth and admin events and has no retention job yet (#196) and no partitioning. ## Acceptance criteria - [ ] Separate table with an index set matched to the expected queries ("who read page X", "what did user Y read", both within a period). - [ ] Time-based partitioning, with the partition-maintenance job included and tested — not left as an operational chore. - [ ] Own configurable retention period, independent of #196, with a documented default and the deletion itself logged. - [ ] A Site-Admin query path exists (or its deliberate absence is documented) — evidence nobody can read is not evidence. - [ ] Growth measured and stated (rows and bytes per 1000 reads) so an operator can size storage. - [ ] `docs/architecture/data-model.md` documents the table. ## Out of scope SIEM forwarding of read events beyond the catalogue from #201, and tamper-proofing.

fable-5 added this to the M29 — VS-NfD: read-access audit trail milestone 2026-07-30 01:43:53 +02:00

fable-5 added the

vs-nfd

area:storage

effort:M

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

fable-5 referenced this issue

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

[VS-NfD] Make the read trail switchable and document its purpose limitation #225

fable-5 referenced this issue

2026-07-31 12:25:53 +02:00

#224: read-trail storage - partitioning, retention, admin query path #280

fable-5 referenced this issue

2026-07-31 12:25:54 +02:00

#225: read-trail master switch and purpose limitation #281

fable-5 referenced this issue from a commit

2026-07-31 12:53:38 +02:00

#224: read-trail storage — partitioning, retention, admin query path

fable-5 commented

2026-07-31 12:59:08 +02:00

Implemented in PR #280 (commit 2bdb0ec), contained in the merged chain (PR #281, run 596 green; own run 593 failed only on runner infra). Monthly RANGE partitions with DEFAULT safety-net partition; daily read-trail-maintenance job (job fence 9->10) creates months ahead incl. per-partition dedup indexes and prunes under readTrail.retentionDays (default 365) - whole months by DROP, remainder by ranged delete, audited as read_trail.pruned (catalogue v1.2). Site-Admin query path GET /admin/system/read-events (pageId/actor/channel/period). Growth measured: ~1 MB per 1000 events incl. indexes (data-model.md). Fresh-DB test runs the real migration chain and proves partition shape, per-partition P2002 and DROP pruning. Live: read_events on test is relkind=p with 4 partitions.

Implemented in PR #280 (commit 2bdb0ec), contained in the merged chain (PR #281, run 596 green; own run 593 failed only on runner infra). Monthly RANGE partitions with DEFAULT safety-net partition; daily read-trail-maintenance job (job fence 9->10) creates months ahead incl. per-partition dedup indexes and prunes under readTrail.retentionDays (default 365) - whole months by DROP, remainder by ranged delete, audited as read_trail.pruned (catalogue v1.2). Site-Admin query path GET /admin/system/read-events (pageId/actor/channel/period). Growth measured: ~1 MB per 1000 events incl. indexes (data-model.md). Fresh-DB test runs the real migration chain and proves partition shape, per-partition P2002 and DROP pruning. Live: read_events on test is relkind=p with 4 partitions.

fable-5 closed this issue

2026-07-31 12:59:08 +02:00

fable-5 referenced this issue

2026-07-31 14:40:09 +02:00

#219: verified reproducible build without network access #287

fable-5 referenced this issue from a commit

2026-07-31 14:40:11 +02:00

#219: verified reproducible build without network access

fable-5 referenced this issue

2026-07-31 15:01:48 +02:00

[VS-NfD] Make the build reproducible without network access #219

fable-5 referenced this issue