Subquery-based shape fails to sync DELETE when CASCADE removes the join-table rows

[ibarakaiev]

Bug description

When a shape uses a subquery WHERE clause that references a join table (e.g., id IN (SELECT project_id FROM project_members WHERE user_id = ...)), and the parent row is hard-DELETEd with ON DELETE CASCADE on the join table's FK, Electric never syncs the deletion to clients.

The CASCADE removes the join-table rows in the same transaction as the DELETE. Electric then re-evaluates the subquery against post-commit state, finds no matching membership rows, and concludes no shape cared about the deleted row.

This is a common pattern for multi-tenant or team-based access control where a membership/join table determines which users can see which resources.

Reproduction

Full repro with Bun + Drizzle + Docker Compose:

https://github.com/ibarakaiev/electric-cascade-repro

bun install
docker compose up -d --wait
bun test

Expected behavior

The DELETE should be synced to all clients whose shapes included the row at the time of deletion.

Suggested approach

The WAL entries for the CASCADE contain old row values. Electric sees both events in the same transaction — the membership DELETE (with project_id and user_id) and the project DELETE (with id). Evaluating shape membership using pre-transaction state or old WAL row values instead of post-commit DB state would fix this.

Versions

• Electric: 1.4.13 and canary (both reproduce)
• @electric-sql/client: 1.5.12
• Postgres: 17
• Feature flags: allow_subqueries,tagged_subqueries

---

Appendix: source-level analysis (Claude)

The following is Claude's analysis of the sync-service codebase. It may contain inaccuracies — take it as a starting point, not gospel.

Execution path

1. WAL ingestion (replication_client.ex → message_converter.ex): Postgres sends all DML changes (including CASCADE-triggered deletes) as individual messages within a transaction. MessageConverter buffers them into TransactionFragment objects.

2. Transaction routing (transaction_builder.ex → event_router.ex): Fragments are assembled into Transaction objects. EventRouter determines which shapes are affected via Filter.affected_shapes().

3. Shape membership evaluation (consumer.ex:506-533): This is where the bug lives. do_handle_txn/2 calls Materializer.get_all_as_refs() to get the current subquery values — but this queries committed DB state, where the CASCADE has already removed the join-table rows.

4. WHERE clause check (change_handling.ex:59-115 → shape.ex:569-617 → where_clause.ex:7-14): The stale extra_refs are passed to WhereClause.includes_record?(where, old_record, extra_refs_old). Since extra_refs no longer contains the deleted membership's project_id, Electric concludes no shape cared about the row and skips syncing the DELETE.

Key files

File	Role
consumer.ex:506-533	Calls Materializer.get_all_as_refs() with post-commit state
materializer.ex:65-74, 167-170	Returns value_counts from committed DB — membership rows already gone
shape.ex:569-617	convert_change() uses post-commit extra_refs to evaluate WHERE
where_clause.ex:7-14	Final WHERE evaluation — receives wrong values

Fix direction

The materializer needs to evaluate shape membership for old_record using pre-transaction state (or old row values from the WAL) rather than re-querying committed DB state. The WAL already contains the CASCADE delete on project_members with old values (project_id, user_id) — those could be used to reconstruct the pre-transaction extra_refs.