# How Rank Tracking Timestamps and Snapshot Queries Work in Open-SEO

> Learn how Open-SEO uses SQLite timestamps and Drizzle-ORM snapshot queries for advanced rank tracking and historical data analysis. Explore trends and matrix views.

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: internals
- Published: 2026-07-27

---

**Open-SEO converts JavaScript dates into SQLite-compatible text timestamps using `toSqliteTimestamp` and retrieves historical ranking data through type-safe Drizzle-ORM snapshot queries that support history tracking, trend analysis, and matrix views.**

Open-SEO is an open-source SEO platform that tracks search engine positions by storing every SERP scrape as an immutable snapshot. Understanding how rank tracking timestamps and snapshot queries work is essential for building dashboards that display historical keyword positions and trend distributions. The system relies on two core components located in [`src/server/features/rank-tracking/rankTrackingTimestamps.ts`](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/rankTrackingTimestamps.ts) and [`src/server/features/rank-tracking/repositories/snapshotQueries.ts`](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/repositories/snapshotQueries.ts) to ensure data is stored efficiently and retrieved performantly across SQLite (D1) and PostgreSQL backends.

## SQLite-Compatible Timestamp Handling

Open-SEO stores temporal data as **text** in `YYYY-MM-DD HH:MM:SS` format rather than native Date objects or Unix integers. This design choice enables fast lexical comparisons in SQL without casting overhead.

### The toSqliteTimestamp Utility

The `toSqliteTimestamp` function in [`src/server/features/rank-tracking/rankTrackingTimestamps.ts`](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/rankTrackingTimestamps.ts) converts JavaScript `Date` objects into strings that SQLite can sort and compare using standard operators like `>=` and `<=`.

```typescript
// src/server/features/rank-tracking/rankTrackingTimestamps.ts
export function toSqliteTimestamp(date: Date): string {
  // → "2026-06-09 12:34:56"
  return date.toISOString().slice(0, 19).replace("T", " ");
}

```

This format ensures that `checkedAt` (when the SERP was scraped) and `startedAt` (when the run began) columns in `rankCheckRuns` and `rankSnapshots` tables maintain deterministic ordering.

### Calculating Cutoff Dates for Time-Boxed Queries

To support "last N days" filters in dashboards, the system calculates cutoff timestamps by subtracting days from the current time and formatting the result:

```typescript
function cutoffTimestamp(sinceDays: number): string {
  return toSqliteTimestamp(
    new Date(Date.now() - sinceDays * 24 * 60 * 60 * 1000),
  );
}

```

Because the output is a plain string, SQLite performs the date filter as a cheap string comparison rather than a computational cast, enabling efficient indexing for queries like "last 30 days" or "last 90 days".

## Snapshot Query Helpers in snapshotQueries.ts

All read operations for historical ranking data live in [`src/server/features/rank-tracking/repositories/snapshotQueries.ts`](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/repositories/snapshotQueries.ts). These helpers query two core tables: `rankCheckRuns` (metadata about each scraping job) and `rankSnapshots` (individual keyword/device/position records).

### Core Query Functions

The repository exposes several specialized functions for different analytical views:

- **`getKeywordHistory(configId, trackingKeywordId, sinceDays)`** – Returns a flat chronological series of positions for a single keyword across all completed runs, ordered from oldest to newest.

- **`getConfigTrend(configId, device, sinceDays)`** – Aggregates position distributions per run into buckets (top 3, positions 4-10, positions 11-20, not ranking) for a specific device type.

- **`getPositionMatrix(configId, device, runLimit)`** – Retrieves a recent keyword-by-run matrix limited to the last *N* completed runs, which the client pivots into a grid visualization.

- **`getSnapshotsForConfig(configId, { beforeDate?, order })`** – Internal helper that picks one snapshot per keyword/device using `GROUP BY` with self-joins, supporting both `latest` and `earliest` ordering.

- **`getLatestSnapshotsForKeywords(configId)`** – Shortcut for `getSnapshotsForConfig` with `order: "latest"`, used by dashboards to display current rank views.

- **`getEarliestSnapshotsForKeywords(configId, keywordIds)`** – Retrieves the first snapshot ever recorded for specific keyword IDs, used for calculating "history start" baselines.

### Database-Agnostic Design with Drizzle ORM

All queries use Drizzle-ORM primitives (`and`, `eq`, `inArray`, `gte`, `lte`, `max`, `min`, `sql`) to remain type-safe and portable between SQLite (Cloudflare D1) and PostgreSQL backends. The `getSnapshotsForConfig` function performs the "latest per keyword/device" selection entirely in SQL using aggregation functions, avoiding the memory overhead of loading entire datasets into JavaScript.

## Data Flow: From Scrape to Dashboard

The rank tracking lifecycle follows a predictable pipeline:

1. **Run Scheduling** creates a `rankCheckRuns` row with status `pending` and a `startedAt` timestamp generated via `toSqliteTimestamp`.

2. **SERP Scraping** executes asynchronously; upon completion, the system inserts multiple `rankSnapshots` rows (one per keyword/device combination) with `checkedAt` timestamps.

3. **Dashboard Queries** call the snapshot helpers to populate visualizations:
   - **History charts** use `getKeywordHistory` to draw position-over-time lines.
   - **Trend charts** use `getConfigTrend` to render distribution bar charts.
   - **Matrix grids** use `getPositionMatrix` to show keyword rankings across recent runs.

4. **Current State Views** call `getLatestSnapshotsForKeywords` to display the most recent position for every tracked keyword without loading historical data.

This architecture minimizes memory usage by pushing aggregation logic into the database layer while maintaining type safety through Drizzle-ORM.

## Practical Code Examples

Server-side functions are typically invoked through TanStack Server Functions. Here are common usage patterns:

Fetch the last 30 days of position history for a specific keyword:

```typescript
import { getKeywordHistory } from "@/server/features/rank-tracking/repositories/snapshotQueries";

const history = await getKeywordHistory(
  "config-123",          // Rank-tracking config UUID
  "keyword-456",         // TrackingKeyword UUID
  30                     // Days to look back
);
// Returns: [{ device: "desktop", checkedAt: "2026-06-09 12:00:00", position: 5 }, ...]

```

Retrieve trend distribution for mobile devices over 90 days:

```typescript
import { getConfigTrend } from "@/server/features/rank-tracking/repositories/snapshotQueries";

const trend = await getConfigTrend(
  "config-123",
  "mobile",
  90
);
// Returns: [{ runId, checkedAt, total, top3, top4to10, top11to20 }, ...]

```

Build a position matrix for the last 10 desktop runs:

```typescript
import { getPositionMatrix } from "@/server/features/rank-tracking/repositories/snapshotQueries";

const matrix = await getPositionMatrix(
  "config-123",
  "desktop",
  10                     // Number of recent runs
);

```

Get current snapshots for all tracked keywords:

```typescript
import { getLatestSnapshotsForKeywords } from "@/server/features/rank-tracking/repositories/snapshotQueries";

const latest = await getLatestSnapshotsForKeywords("config-123");
// Returns: [{ id, runId, trackingKeywordId, keyword, device, position, ... }, ...]

```

## Summary

- **Timestamp normalization** via `toSqliteTimestamp` converts JavaScript dates into `YYYY-MM-DD HH:MM:SS` strings that SQLite can compare lexically without casting.
- **Two core tables** store rank data: `rankCheckRuns` for run metadata and `rankSnapshots` for individual keyword positions.
- **Six query helpers** in [`snapshotQueries.ts`](https://github.com/every-app/open-seo/blob/main/snapshotQueries.ts) provide history tracking, trend aggregation, matrix views, and latest/earliest snapshot retrieval.
- **Database portability** is achieved through Drizzle-ORM, enabling the same code to run on SQLite (D1) and PostgreSQL.
- **Performance optimization** occurs by filtering dates as string comparisons and performing "latest per keyword" selections in SQL rather than application code.

## Frequently Asked Questions

### How does Open-SEO handle timezone differences in rank tracking timestamps?

All timestamps are stored in UTC format using JavaScript's `toISOString()` method before being sliced into the SQLite-compatible string. This ensures consistent ordering regardless of the server's local timezone. When displaying data to users, the client application is responsible for converting UTC timestamps to the viewer's local timezone.

### What is the difference between `getLatestSnapshotsForKeywords` and `getEarliestSnapshotsForKeywords`?

`getLatestSnapshotsForKeywords` returns the most recent position recorded for each keyword/device combination (using `order: "latest"`), typically used for dashboard "current rank" views. `getEarliestSnapshotsForKeywords` accepts a specific array of keyword IDs and returns the first snapshot ever recorded for each (using `order: "earliest"`), used to establish baseline positions when calculating rank change over time.

### Why does Open-SEO store timestamps as text instead of integers in SQLite?

While Unix timestamps (integers) work for comparisons, the `YYYY-MM-DD HH:MM:SS` text format provides human readability in raw SQL queries while remaining lexically sortable. This format matches SQLite's native datetime functions and avoids integer overflow issues, though the code would function similarly with Unix timestamps stored as integers.

### How can I query rank history for a specific date range?

Use the `getKeywordHistory` function with the `sinceDays` parameter to set a cutoff date, which internally calls `cutoffTimestamp` to generate the comparison string. For custom date ranges (between specific start and end dates), you would extend the query pattern in [`snapshotQueries.ts`](https://github.com/every-app/open-seo/blob/main/snapshotQueries.ts) to use both `gte` (greater than or equal) and `lte` (less than or equal) filters on the `checkedAt` column, passing formatted timestamps via `toSqliteTimestamp` for both boundaries.