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

> Discover how the Pagination Helper optimizes large result sets in Claude-Mem. Learn its LIMIT + 1 fetch strategy and constant-time database work for efficient SQLite tables.

- Repository: [Alex Newman/claude-mem](https://github.com/thedotmack/claude-mem)
- Tags: deep-dive
- Published: 2026-02-16

---

**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`](https://github.com/thedotmack/claude-mem/blob/main/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`](https://github.com/thedotmack/claude-mem/blob/main/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.

```typescript
// 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:

```typescript
// 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:

```typescript
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`](https://github.com/thedotmack/claude-mem/blob/main/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.