Summary

Electric currently limits shapes to single-table filtering, preventing developers from expressing common authorization patterns that require cross-table relationships. This RFC proposes extending the shape system to support subqueries in WHERE clauses by automatically creating and maintaining "index shapes" - specialized shapes that materialize subquery results and propagate changes to dependent parent shapes, enabling developers to write natural queries like WHERE author_id IN (SELECT user_id FROM team_members WHERE team_id = 123) without schema denormalization or client-side workarounds.

Background

Electric is a Postgres sync engine that streams data from Postgres into local applications using HTTP-based shape subscriptions. Shapes define subsets of data using a table name, optional WHERE clause, and optional column selection. The Electric sync service maintains a log of operations for each shape and streams changes to subscribed clients in real-time.

Currently, shape WHERE clauses can only reference columns within the target table. This restriction exists because Electric processes the Postgres logical replication stream, which provides row-level changes without join context. When a row changes, Electric can evaluate simple predicates against that row's columns but cannot resolve relationships to other tables without additional infrastructure.

This limitation forces developers to implement authorization patterns through schema denormalization (duplicating user_id or tenant_id across all tables), client-side filtering (over-fetching data and filtering locally), or proxy-based authorization (implementing complex middleware that reconstructs and filters shapes). Each workaround introduces complexity, performance overhead, or security risks.

The previous Electric implementation attempted to solve multi-table filtering using a per-user directed graph to track foreign key relationships. This approach proved memory-intensive and was abandoned before optimization. The graph maintained row-to-row relationships to handle "move-out" scenarios where related rows must be removed when their parent row no longer matches a filter.

Problem

The inability to express cross-table relationships in shape WHERE clauses prevents developers from implementing standard authorization patterns with Electric. This creates three specific problems:

1. Authorization Requires Schema Denormalization

Applications typically implement access control through relational patterns: users belong to teams, content has owners, resources exist within organizational hierarchies. Without subqueries, developers must duplicate authorization fields (user_id, team_id, org_id) across every table they want to sync. This denormalization increases storage costs, complicates migrations, and risks data inconsistency.

2. Middleware Complexity for Relationship-Based Filtering

Developers must implement authorization logic in API proxies or middleware layers, reconstructing SQL joins in application code. This moves database logic into the application layer, creating maintenance burden and potential security vulnerabilities. Each relationship pattern requires custom proxy code that must be kept in sync with schema changes.

3. Performance Degradation from Workaround Overhead

Current workarounds introduce significant performance penalties. Schema denormalization requires updating multiple tables on every authorization change, creating write amplification. Proxy-based filtering adds network hops and computation overhead. Client-side filtering wastes bandwidth by over-fetching. These workarounds make Electric unsuitable for applications with complex authorization models or high performance requirements.

These limitations prevent Electric adoption in multi-tenant SaaS applications, collaborative tools, and any system with relationship-based access control—effectively excluding most production applications.

Proposal

This RFC proposes extending Electric shapes to support subqueries in WHERE clauses by automatically creating and maintaining "index shapes" - specialized shapes that materialize subquery results.

Core Architecture

When a shape is created with a subquery WHERE clause like:

posts WHERE author_id IN (SELECT user_id FROM team_members WHERE team_id = 123)

Electric will:

1. Parse the WHERE clause and identify required subqueries
2. Create an index shape for team_members WHERE team_id = 123 if it doesn't exist
3. Maintain bidirectional dependency tracking between parent and index shapes
4. Propagate changes from index shapes to dependent parent shapes

Index Shape Design

Index shapes are regular Electric shapes with special lifecycle management:

• Identity: Keyed by normalized table + WHERE clause to enable sharing
• Creation: Automatically created when first referenced by a parent shape
• Persistence: Stored like regular 8000 shapes with operation logs on disk
• Memory Management: Hot index shapes cached in memory with LRU eviction
• Lifecycle: Reference counted but destruction deferred (no immediate GC)

Change Propagation

When changes arrive via logical replication:

For Index Shape Changes:

1. Update the index shape's operation log
2. Look up dependent parent shapes via reverse index
3. For each parent shape, determine if change requires propagation

For Move-Ins (rows added to index shape):

1. Extract the key values (e.g., new user_id added to team)
2. Query Postgres for related rows in parent table
3. Write INSERT operations to parent shape logs
4. Batch multiple move-ins to minimize Postgres queries

Move-In Consistency Considerations (to investigate during prototyping):

• Buffering Strategy: Should we buffer incoming transactions while querying Postgres, or continue processing and reconcile later?
• Shape State During Move-Ins: Should we pause the parent shape entirely, continue writing to its log but delay the "up-to-date" message, or use some other consistency mechanism?
• Filter Update Timing: When should we update the parent shape's effective WHERE clause - immediately when the index shape changes, or after the Postgres snapshot completes?
• Handling Concurrent Updates: How do we handle updates that arrive for rows that match the new filter but haven't been moved in yet? Options include creating them as inserts, buffering until move-in completes, or other strategies.
• Log Position Correctness: How do we ensure move-in operations are placed at the correct position in the parent shape log relative to concurrent operations?
• Transaction Consistency: Should move-ins be tagged with the transaction ID of the original index shape change to help clients maintain consistency across related shapes?

For Move-Outs (rows removed from index shape):

1. Extract the removed key values from index shape DELETE operation
2. Scan parent shape log with metadata (avoiding JSON deserialization)
3. Write DELETE operations for matching rows
4. Optimize with indexes on foreign key fields in shape log metadata

Implementation Details

Metadata Storage: Add foreign key values to shape log entries as indexed metadata, either in the log file or a separate index file, to enable efficient scanning without JSON parsing.

Index Storage Evolution: Initial implementation will use in-memory structures for index shapes. Future optimization may convert index shapes into embedded SQLite databases for better query performance and memory efficiency, but this will be investigated after validating the core approach.

Query Normalization: Normalize WHERE clauses to canonical form to maximize index shape sharing across parent shapes with equivalent conditions.

Concurrent Creation: Coordinate index shape creation when multiple clients request shapes with the same subquery simultaneously to avoid duplicate work.

Complex WHERE Clauses: Support multiple subqueries by creating multiple index shapes and evaluating the composite conditions using Electric's existing WHERE clause matching logic.

Error Handling: Failed Postgres queries for move-ins follow existing Electric error handling patterns (retry with backoff, shape error states).

Limitations

Phase 1 supports only:

• Single-table subqueries with IN operators
• Simple WHERE conditions in subqueries
• No aggregations or complex functions
• No nested subqueries

Phase 2 will extend to:

• Multiple subqueries with AND/OR combinations
• More complex WHERE patterns
• Multi-level relationships

Definition of Success

The implementation will be considered successful when it meets the following criteria:

Functional Correctness

• Shapes with subquery WHERE clauses produce identical results to equivalent Postgres queries
• Move-in operations correctly sync all related rows when entries are added to index shapes
• Move-out operations correctly remove all dependent rows when entries are removed from index shapes
• Complex WHERE clauses with multiple subqueries evaluate correctly using AND/OR logic
• No data inconsistencies between parent shapes and their index shape dependencies

Performance Targets

• Shape creation time with pre-existing index shapes matches single-table shape creation (within 10% variance)
• Authorization changes propagate to affected shapes within 100ms for 100k+ affected users
• Memory usage remains bounded by number of tables, not number of shapes or users
• Move-out operations via log scanning complete in under 50ms for shapes with <1M entries
• Postgres query batching reduces move-in query load by >80% compared to naive implementation

Developer Experience

• Zero client code changes required - subquery shapes appear identical to single-table shapes
• Clear error messages for unsupported subquery patterns
• Natural SQL WHERE clause syntax without special encoding
• No schema changes required to existing applications
• Works with all existing Electric deployment modes (embedded, Docker, cloud)

Production Readiness

• Handles 10k+ concurrent shapes with subqueries without degradation
• Supports tables with 10M+ rows in both parent and index shapes
• Graceful degradation under memory pressure via LRU cache eviction
• No memory leaks or unbounded growth over extended operation
• Crash recovery rebuilds index shapes from persisted parent shape definitions

Phase 1 Delivery

A working prototype that demonstrates:

• Basic IN (SELECT ...) subquery support
• Correct move-in/move-out behavior
• Memory-based index shape implementation
• Performance within 5x of production targets

This foundation will validate the architecture before optimizing for Phase 2 production requirements.