# Data Layer

> Server-side concepts for filtering, faceted search, cursor pagination, and chart data

Source: https://data-table.openstatus.dev/docs/data-layer · Docs index: https://data-table.openstatus.dev/llms.txt · Full docs: https://data-table.openstatus.dev/llms-full.txt

These concepts apply regardless of your ORM — Drizzle, Prisma, or raw SQL. The [Drizzle ORM](/docs/drizzle-orm) guide shows a concrete implementation. See the [/infinite](/infinite) demo for a live example with all data layer features in action.

### API Response Shape

Your API endpoint should return this shape (serialized with [SuperJSON](https://github.com/flightcontrolhq/superjson) to preserve `Date` objects):

```ts
{
  data: ColumnSchema[];
  meta: {
    totalRowCount: number;      // Total rows in the table (unfiltered)
    filterRowCount: number;     // Rows matching current filters
    chartData: BaseChartSchema[];
    facets: Record<string, FacetMetadataSchema>;
    metadata?: TMeta;           // Custom metadata (e.g. percentiles)
  };
  nextCursor: number | null;    // Timestamp (ms) of last row
  prevCursor: number | null;    // Timestamp (ms) of first row
}
```

### Request Format

The reserved query parameters, and how a filter value is encoded, used to exist
only implicitly inside the client's serializer. They are now written down as
`REQUEST_PARAMS` and `FILTER_ENCODING`:

| Parameter      | Meaning                                                           |
| -------------- | ----------------------------------------------------------------- |
| `cursor`       | Page cursor — epoch ms for the default strategy                   |
| `direction`    | `next` (older) or `prev` (newer)                                  |
| `size`         | Page size — a target, see [Cursor Pagination](#cursor-pagination) |
| `sort`         | `<columnKey>.<asc\|desc>`                                         |
| `uuid`, `live` | View state — these never narrow the result set                    |

Every other parameter is a column key:

| Filter type | Encoding                                       |
| ----------- | ---------------------------------------------- |
| `checkbox`  | `?level=error,warn`                            |
| `slider`    | `?latency=100-3000` (inclusive both ends)      |
| `timerange` | `?date=1700000000000-1700003600000` (epoch ms) |
| `input`     | `?host=example.com`                            |

Two constraints follow from the delimiters: a checkbox value may not contain a
comma, and slider or timerange bounds may not be negative.

Because `uuid` and `live` do not narrow results, an endpoint that rejects
unrecognised query parameters will break on them. Ignore what you do not know.

### Validating a Response

`validateListResponse` checks a payload against the contract. It returns issues
rather than throwing, split by severity: an `error` means the table cannot
render the response, a `warning` means it can but the endpoint is not doing what
it claimed:

```ts
import { isContractValid, validateListResponse } from "@/lib/data-table";

const issues = validateListResponse(payload, {
  capabilities: { facets: true, totalRowCount: true },
});

if (!isContractValid(issues)) {
  console.error(issues.filter((issue) => issue.severity === "error"));
}
```

Passing `capabilities` is what turns "this field is absent" into a warning: a
response with no `meta.facets` is fine unless the endpoint said it computes
them.

To check a whole endpoint rather than one response, see
[Headless Tables](/docs/headless#proving-an-endpoint-conforms).

### Three-Pass Filtering

A strategy to keep slider bounds stable when users adjust them. Without it, dragging a slider collapses its own min/max range.

**Pass 1 — Date only**
Apply only the date range filter. This gives the base time window for all facet computation.

**Pass 2 — Date + non-slider filters**
Add checkbox, input, and other non-slider filters. Use these conditions to compute slider facet bounds (min/max). Because slider values are excluded, moving a slider doesn't shrink its own range.

**Pass 3 — All filters (including sliders)**
The final set of conditions used for the data query, counts, and non-slider facets.

```
Date filters ──────────────────────────┐
                                       ├─▶ Pass 1 → (used for date-only facets)
                                       │
+ Non-slider filters ─────────────────├─▶ Pass 2 → Slider min/max bounds
                                       │
+ Slider filters ─────────────────────└─▶ Pass 3 → Data, counts, checkbox facets
```

### Faceted Search

Facets provide grouped counts for filter options and min/max ranges for sliders. They're returned in `meta.facets`:

```ts
type FacetMetadataSchema = {
  rows: Array<{ value: string | number | boolean; total: number }>;
  total: number;
  min?: number; // For slider and numeric columns
  max?: number;
};
```

**Checkbox facets** — grouped counts per value (e.g. `{ success: 120, error: 8 }`). Computed from Pass 3 conditions.

**Slider facets** — `min`/`max` of the column. Computed from Pass 2 conditions so they don't collapse.

**Array columns** — unnest the array values before grouping (e.g. PostgreSQL `unnest()`).

All facet queries can run in parallel for better performance.

#### Client-Side Usage

Inject server-side facets into your filter fields at runtime so checkboxes show counts and sliders get dynamic bounds:

```tsx
const dynamicFilterFields = React.useMemo(() => {
  return filterFields.map((field) => {
    const facetsField = facets?.[field.value as string];
    if (!facetsField) return field;
    if (field.options && field.options.length > 0) return field;

    const options = facetsField.rows.map(({ value }) => ({
      label: `${value}`,
      value,
    }));

    if (field.type === "slider") {
      return {
        ...field,
        min: facetsField.min ?? field.min,
        max: facetsField.max ?? field.max,
        options,
      };
    }

    return { ...field, options };
  });
}, [facets]);
```

### Cursor Pagination

Cursor-based pagination (not offset) is used for infinite scroll. It's stable under concurrent inserts and performs better on large tables.

- Uses a timestamp column (e.g. `date`) as the cursor
- **Next page** (older rows): `WHERE date < cursor ORDER BY date DESC LIMIT size`
- **Previous page** (newer rows, for live mode): `WHERE date > cursor ORDER BY date ASC LIMIT size` (then reverse the results)
- Client sends `cursor` + `direction`, server returns `nextCursor` + `prevCursor`
- `size` is a target, not a hard limit. Because the next page is read with a strict `<`, rows that share one cursor value cannot be split across two pages, so a page ends before such a group — or, when the group is longer than `size`, returns it whole. `createDrizzleHandler` does this, and `runEndpointConformance` accepts it.

### Chart Data

Optional time-series aggregation for the timeline chart. Group rows into time buckets and count by category:

```ts
type BaseChartSchema = {
  timestamp: number; // UNIX ms (bucket start)
  [key: string]: number; // e.g. "success", "warning", "error"
};
```

Use `date_bin()` (PostgreSQL) or equivalent for intelligent bucketing. The interval should adapt to the date range — smaller ranges get finer buckets.

### Indexing

Add database indexes on columns used for filtering, sorting, and pagination:

- **Cursor column** (e.g. `date`) — required for cursor pagination performance
- **Frequently filtered columns** (e.g. `level`, `status`) — speeds up WHERE clauses
- **Composite indexes** — useful when filters are often combined

Use `EXPLAIN ANALYZE` to verify your indexes are being used.

```ts
import { index, pgTable } from "drizzle-orm/pg-core";

export const logs = pgTable(
  "logs",
  {
    // ... columns
  },
  (table) => [
    index("logs_date_idx").on(table.date),
    index("logs_level_idx").on(table.level),
    index("logs_status_idx").on(table.status),
  ],
);
```
