Subqueries with `tagged_subqueries` not re-evaluating when dependent table changes

[jbingen]

Environment

• Electric server: electricsql/electric:latest (self-hosted on Railway)
• Feature flags: ELECTRIC_FEATURE_FLAGS=allow_subqueries,tagged_subqueries
• Client packages:
  • @electric-sql/pglite-sync: 0.4.1
  • @electric-sql/pglite: 0.3.15
  • @electric-sql/client: 1.4.0
• Using syncShapeToTable from @electric-sql/pglite-sync

Description

We're building a multi-tenant app where users belong to workspaces. We need to sync profiles of all users in the same workspace as the current user.

Our WHERE clause for the profiles shape uses a subquery:

id IN (SELECT user_id FROM workspace_members WHERE workspace_id = 'known_workspace_id')

The issue: When a new user joins the workspace (new row inserted into workspace_members), their profile does NOT sync to existing workspace members - even with tagged_subqueries enabled, even after client-side refresh.

Steps to Reproduce

1. User A creates a workspace and starts syncing profiles
2. User B (already has a profile) is added to User A's workspace via direct SQL insert
3. User A's workspace_members shape updates (shows 5 members)
4. User A's profiles shape does NOT update (still shows 4 profiles)
5. Client detects mismatch, triggers refresh (stops sync, clears table, clears Electric metadata, restarts)
6. Even after refresh, the new profile is not returned

Relevant logs

[collections] workspace_members row count after sync: 5
[collections] profiles row count after sync: 5
[collections] useWorkspaceSettings: querying profiles for IDs: Array(5)
[collections] useWorkspaceSettings: profilesResult returned: Array(4)  // missing the new member!
[collections] useWorkspaceSettings: missing 1 profiles, refreshing...
[collections] refreshTables called for: profiles
[collections] stopped sync for profiles (refresh)
[collections] cleared profiles for refresh
[collections] cleared Electric metadata for profiles
[collections] restarting sync for profiles
[collections] initial sync complete for profiles
[collections] profilesResult returned: Array(4)  // STILL missing after fresh sync!

Schema (simplified)

CREATE TABLE profiles (
  id UUID PRIMARY KEY,
  email TEXT
);

CREATE TABLE workspace_members (
  id UUID PRIMARY KEY,
  workspace_id UUID NOT NULL,
  user_id UUID NOT NULL REFERENCES profiles(id),
  role TEXT NOT NULL
);

Questions

Per RFC #2931 and @balegas's comment, tagged_subqueries should "handle reactivity if any data arrives in the inner query." Our inner query is workspace_members WHERE workspace_id = 'X', and when new rows are inserted, the outer profiles shape should update.

1. Does tagged_subqueries actually re-evaluate subqueries when dependent tables change?
2. Why does a fresh shape request (new sync after clearing metadata) still not return the newly matching row?
3. Is the subquery result cached somewhere on the Electric server that persists across client reconnects?

Thanks for any guidance!

[jbingen]

Update: Found a workaround by doing a full reset:

1. Redeployed Electric server on Railway (no persistent volumes)
2. Cleared client-side IndexedDB

After both, the missing profile appeared. This suggests:

• Initial snapshot/subquery evaluation works correctly
• The issue is with runtime sync when the inner table changes
• Server-side shape data was stale and persisted until full reset

So the move-in mechanism appears to not be triggering (or failing silently) when workspace_members changes at runtime. The profile exists in Postgres and matches the subquery - it just doesn't get synced until the shape is completely rebuilt.

Clearing only client-side IndexedDB (no server redeploy) also fixes it.

[jbingen]

I think the bug is in the Materializer lifecycle. When the inner shape's Consumer dies (for any reason), the Materializer shuts down with it (handle_info({{:consumer_down, _}, ...})). But when the inner Consumer is later restored via ConsumerRegistry.publish → start_consumer_for_handle → restore_shape_and_dependencies → start_shape, no Materializer is started because:

1. start_shape only starts Materializers for the shape's own shape_dependencies
2. workspace_members has no dependencies (it's a simple table shape)
3. The Materializer was owned by the OUTER shape (profiles), not workspace_members itself
4. There's no reverse dependency tracking - workspace_members doesn't know profiles depends on it

The fix would need to either:

1. Track reverse dependencies so inner shapes know which outer shapes depend on them
2. Have outer shapes detect when their dependencies are restored and re-create Materializers
3. Not let Materializers die with their monitored Consumer (requires different restart logic)

Happy to help with a PR if you point me toward the preferred approach.

[jbingen]

Update: After debugging with ELECTRIC_LOG_LEVEL=debug, the server
processes move-ins correctly. The issue is in pglite-sync not
applying them client-side.

Server logs show:

• "Consumer reacting to 1 move ins"
• "Move-in triggered"
• "Query complete with 1 keys"

But client shows:

• workspace_members: 9 rows
• profiles: 8 rows (missing the moved-in profile)

This appears to be a pglite-sync bug, not a sync-service bug.

[KyleAMathews]

Yeah pglite doesn't have support for tagged subqueries. Only TanStack DB does as the client DB needs special support.

[jbingen]

Yeah pglite doesn't have support for tagged subqueries. Only TanStack DB does as the client DB needs special support.

Actually, with this PR it does work now! The issue wasn't that pglite-sync can't handle tagged subqueries - the Electric server was correctly processing move-ins and sending them. The problem was two client-side bugs in pglite-sync:

1. LSN filtering: Move-in messages don't have an lsn header (they come from direct DB queries, not replication). pglite-sync was defaulting missing LSN to 0 and filtering them out as "already seen".

2. Duplicate handling: Move-in data can overlap with initial sync data, causing duplicate key errors on insert.

This PR fixes both issues. I've tested it with a shape like:

WHERE id IN (SELECT user_id FROM workspace_members WHERE workspace_id = $1)

Adding a new workspace member now correctly triggers the move-in, and the profile syncs to PGlite in real-time without needing a refresh.

The server-side tagged_subqueries feature was working fine - it was just the pglite-sync client that wasn't handling the move-in messages properly.