How the Pagination Helper Optimizes Large Result Sets in Claude-Mem

The PaginationHelper class eliminates expensive COUNT(*) queries by using a LIMIT + 1 fetch strategy and deterministic epoch-based ordering to serve large SQLite tables with constant-time database work per page.

As the SQLite store in Claude-Mem grows, pulling entire tables into memory becomes wasteful and slow. The pagination helper optimizes large result sets by centralizing an efficient pagination strategy in src/services/worker/PaginationHelper.ts that keeps response times low while reducing database load across all API endpoints.

Centralized Pagination Logic in PaginationHelper

All three primary API endpoints—/observations, /summaries, and /prompts—delegate to a single private method paginate<T>() defined at lines 164-196 in src/services/worker/PaginationHelper.ts. This guarantees a consistent, DRY approach and avoids duplicated query code across the service.

The generic paginate<T>() method handles the SQL construction, parameter binding, and result mapping for any entity type. By centralizing the logic, the helper ensures that optimizations applied to one endpoint automatically benefit all consumers.

The LIMIT+1 Optimization Strategy

The most significant performance gain comes from the LIMIT + 1 trick implemented at line 185. Instead of executing a separate COUNT(*) query—which would require scanning the entire table to determine if more pages exist—the helper requests one extra row beyond the requested limit.

// Line 185: Fetch limit + 1 to detect if more pages exist
const stmt = this.db.prepare(`
  SELECT * FROM ${table}
  ${projectFilter}
  ORDER BY created_at_epoch DESC
  LIMIT ? OFFSET ?
`);
const rows = stmt.all(...params, limit + 1, offset);

The extra row is only used to set the hasMore boolean flag. The final payload is trimmed back to the requested limit at lines 190-194, ensuring the consumer receives exactly the number of items requested while avoiding the expensive count operation.

Deterministic Ordering and Filtering

Epoch-Based Sorting for Consistency

To ensure deterministic pagination even when rows are inserted concurrently, queries order results by created_at_epoch DESC (lines 184-185). This timestamp-based sorting prevents items from shifting between pages during active write operations, which is critical for maintaining a stable UI experience when the user is paginating through large histories.

Project-Scoped Queries

When a project argument is supplied, the helper adds a WHERE project = ? clause at lines 179-182. This narrows the dataset early in the query execution plan, reducing the amount of data SQLite must sort and scan. For large installations with multiple projects, this filtering significantly reduces the working set before the LIMIT is applied.

Type-Safe Returns and Sanitization

The helper returns a strongly-typed PaginatedResult<T> object containing items, hasMore, offset, and limit (lines 90-96). This predictable contract allows REST routes and React hooks to rely on a consistent interface, making UI pagination straightforward to implement.

For observations specifically, getObservations() runs each result through sanitizeObservation() at lines 85-87. This strips absolute project paths from files_read and files_modified fields, keeping the payload lightweight and secure without requiring additional post-processing in the frontend.

Implementation Example

To fetch the second page of observations with 20 items per page:

// Route handler using PaginationHelper
const page = 2;
const limit = 20;
const offset = (page - 1) * limit;
const project = 'my-cool-app';

const obsPage = paginationHelper.getObservations(offset, limit, project);
// Returns: { items: Observation[], hasMore: boolean, offset: number, limit: number }

On the frontend, a React hook consumes the same helper via the API:

function useObservations(page: number, project?: string) {
  const { data, error, isLoading } = useSWR(
    ['/api/observations', page, project],
    async () => {
      const offset = (page - 1) * 20;
      const response = await fetch(
        `/api/observations?offset=${offset}&limit=20&project=${project ?? ''}`
      );
      return response.json();
    }
  );
  
  return {
    observations: data?.items ?? [],
    hasMore: data?.hasMore ?? false,
    error,
    isLoading
  };
}

Summary

• The PaginationHelper class in src/services/worker/PaginationHelper.ts centralizes all pagination logic for the /observations, /summaries, and /prompts endpoints.
• The LIMIT + 1 trick eliminates expensive COUNT(*) queries by fetching one extra row to determine if more pages exist, then trimming the result.
• Epoch-based ordering (created_at_epoch DESC) ensures deterministic pagination even during concurrent writes.
• Project-scoped filtering narrows the dataset early, reducing the working set before limits are applied.
• Type-safe returns via PaginatedResult<T> and observation-specific sanitization keep payloads lightweight and secure.

Frequently Asked Questions

How does the pagination helper avoid slow COUNT(*) queries?

The helper uses a LIMIT + 1 fetch strategy. Instead of counting all rows to determine if a next page exists, it requests one more item than the user asked for. If that extra row exists, hasMore is set to true and the row is discarded. This avoids a full table scan that COUNT(*) would require.

Why does the helper order results by created_at_epoch?

Ordering by created_at_epoch DESC ensures deterministic pagination. Without a strict ordering, concurrent inserts could cause rows to shift between pages during navigation, leading to skipped or duplicate items. The epoch timestamp provides a stable sort key that remains consistent even when the database is actively receiving new observations.

Can the pagination helper filter by project?

Yes. When a project parameter is provided, the helper injects a WHERE project = ? clause into the SQL query before applying the LIMIT and OFFSET. This filters the dataset at the database level, reducing the amount of data that must be sorted and scanned, which significantly improves performance for installations with multiple projects.

What sanitization does the helper perform on observations?

After fetching observations, the helper passes each result through sanitizeObservation() to strip absolute file system paths from the files_read and files_modified fields. This reduces payload size and prevents leaking sensitive directory structures to the frontend, while keeping the sanitization logic centralized in the data layer rather than duplicating it across UI components.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}