Deleting a stream orphans its stream_data and stream_events rows: the worker processes deleted streams forever

[Xhristin3]

Problem

Deleting a stream leaves its stream_data and stream_events rows behind, and the worker keeps polling and processing events for deleted streams forever. The schema (database/schema.sql) declares the foreign keys without cascades:

CREATE TABLE IF NOT EXISTS stream_data (
    id SERIAL PRIMARY KEY,
    stream_id INTEGER NOT NULL REFERENCES streams(id),   -- ← no ON DELETE CASCADE
    ...
);

CREATE TABLE IF NOT EXISTS stream_events (
    id SERIAL PRIMARY KEY,
    stream_id INTEGER NOT NULL REFERENCES streams(id),   -- ← no ON DELETE CASCADE
    ...
);

(Compare stream_tags, webhook_subscriptions, and notifications, which all use ON DELETE CASCADE.) StreamsDbRepository.delete() (api/src/streams/repository/streams-db.repository.ts) issues a bare DELETE FROM streams WHERE id = $1. getPendingEvents() reads stream_data with no join to streams, so orphaned rows keep being returned:

SELECT stream_id, data, timestamp
FROM stream_data
ORDER BY timestamp ASC
LIMIT $1 OFFSET $2        -- ← no filter on the stream still existing

Consequence: after DELETE /streams/:id (which returns 204 and the dashboard clears the stream), the worker continues to fetch and route the deleted stream's events — spawning sessions, acquiring locks, and publishing — against a stream that no longer exists. Orphaned stream_events rows also survive, so future analytics/replay work that scans by stream_id accumulates dead rows, and the idx_stream_data_stream_id index can never help prune them. The in-memory repository's delete() (api/src/streams/repository/streams.repository.ts) does remove eventsByStream, so the two implementations already disagree about what delete means.

Root cause

-- database/schema.sql
stream_id INTEGER NOT NULL REFERENCES streams(id)   -- ← no ON DELETE CASCADE on stream_data / stream_events

-- api/src/streams/repository/streams-db.repository.ts — delete()
DELETE FROM streams WHERE id = $1                   -- ← leaves stream_data and stream_events rows orphaned

Why this is architecturally hard

1. There are two viable fixes with different blast radius: (a) a migration adding ON DELETE CASCADE to both tables (and optionally to the stream_locks-style worker state), which is schema-level and self-pruning; or (b) an application-level cleanup transaction in StreamsDbRepository.delete() that deletes stream_data, stream_events, and any webhook rows before the stream row. The schema fix is simpler but changes delete semantics for any future row type referencing streams; the app-level fix keeps the schema explicit but must be repeated per repository and risks forgetting a table. Pick one and document it.
2. Deleting a stream while the worker holds a lock/session for it is a concurrent hazard: the worker may be mid-publish when the stream disappears. The lock manager (xstreamroll-processing/src/leader-election.ts) keys locks by streamId strings and has no knowledge of deletion; the drain endpoint (separate issue) must tolerate a stream that vanished mid-processing.
3. getPendingEvents returns events without validating the stream exists, which is the direct cause of the worker loop. Even with cascades, the query should join or filter on stream existence so a mid-delete race cannot serve rows for a half-deleted stream.
4. This is a correctness/data-integrity boundary: the platform's event log is meant to be the durable record (stream_events, GET /streams/:id/events), and silently dropping those rows on delete is a product decision (retention vs purge) that should be stated in the acceptance criteria.

Acceptance criteria

Data integrity

• After DELETE /streams/:id, no stream_data, stream_events, or other stream-referencing rows remain for that stream (per the chosen cascade or cleanup strategy), verified against the test database.
• The migration has a down migration and applies cleanly via cd api && npm run migrate.
• The in-memory StreamsRepository and the DB-backed StreamsDbRepository implement the same delete semantics.

Worker

• GET /streams/pending no longer returns rows for streams that do not exist (join/filter on streams), so a worker cannot process a deleted stream's events.
• A worker mid-processing a stream that is deleted concurrently does not crash and does not publish events for the deleted stream.

Tests

• Integration test: create a stream, insert stream_data and stream_events rows, delete the stream, assert the tables hold no rows for that stream_id.
• Test that GET /streams/pending excludes rows whose stream was deleted mid-poll.
• Existing streams repository/service/controller specs pass after the delete change.

Documentation

• The delete semantics (what happens to the event log on delete) are documented in the DELETE /streams/:id Swagger description and the schema comment.

Out of scope

Event-log retention policies for live streams, and tombstoning/soft-delete of streams.

Getting started

Real files in scope: database/schema.sql, database/migrations/ (new migration), api/src/streams/repository/streams-db.repository.ts (delete, getPendingEvents), api/src/streams/repository/streams.repository.ts (in-memory delete), api/src/database.integration.spec.ts, xstreamroll-processing/src/worker.ts (poll loop tolerance).

Verify with:

cd api && npm run typecheck && npm test

Good first files to read: database/schema.sql (the stream_data/stream_events FK definitions), api/src/streams/repository/streams-db.repository.ts (delete, getPendingEvents).

[voildman9]

Hello maintainer please I will like to work on this issue

[grantfox-oss]

🦊 GrantFox — @voildman9 has been assigned to this issue as part of the Third Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #529)
2. Your PR will be reviewed by the XStreamRollz maintainers

Good luck! Track your progress on GrantFox.