Support Stable Pagination and Deferred Update Handling for Large Query Results

Problem

In real-time systems, large query results can drift over time as new data arrives or existing data is modified.
Without stability controls, users may experience:

• Sudden reordering of lists during active interaction (e.g., mid-scroll).
• Loss of context, focus, or perceived application instability.
• Disruptive background refreshes that interrupt ongoing activity.

This is especially critical during pagination or infinite scroll use cases where partial data is loaded progressively.

Proposed Solution

Introduce two explicit capabilities:

1. Stable Pagination API

• When paginating, a snapshot version or anchor point (e.g., timestamp, version token) is associated with the query.
• Subsequent pages fetched must be consistent with the original snapshot.
• If the snapshot becomes invalid or inconsistent due to backend changes, the system should trigger an explicit stability warning rather than silently corrupting the pagination sequence.

2. Deferred Update Mode for Queries

• Allow queries to opt into a "stale-aware" mode.
• When underlying data changes, the system does not automatically update visible results.
• Instead, expose a lightweight indicator (hasPendingUpdates: boolean) on the query object.
• Developers can render a UI control (e.g., "New updates available. Click to refresh.").

When the user accepts, the query refetches and updates the visible data.

Example API Concepts

const { results, hasPendingUpdates, refresh } = useDb()
  .from('posts')
  .orderBy(['created_at', 'DESC'])
  .paginate({ limit: 20 })
  .withStability();

if (hasPendingUpdates) {
  // Show "Refresh" button to user
  refresh();
}

Design Principles

• Default behavior remains real-time and reactive for small, manageable datasets.
• Stability features are opt-in for developers handling large, user-visible lists.
• No hidden background invalidations; user intent controls when visible data shifts.
• Query snapshotting should be implicit and lightweight where possible.

Future Work

• Automatic merging of stable pagination results across reconnections or offline periods.
• Server-driven snapshot token propagation if available.
• Fine-grained control over when and how pending updates are surfaced (e.g., throttling, batching notifications).

Start by locating the query, pagination, and reactive update entry points in the TypeScript client; the issue names no files or tests. Map how pages and data changes are currently handled before deciding how snapshot stability and hasPendingUpdates should fit. Done means the opt-in behavior, explicit invalidation warning, refresh flow, and tests for these cases are defined and implemented.