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

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 and 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 converts JavaScript Date objects into strings that SQLite can sort and compare using standard operators like >= and <=.

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

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. 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:

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:

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:

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:

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 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 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.

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