# Open-SEO Rank Tracking System Architecture and Keyword Position Persistence

> Explore the Open-SEO rank tracking system architecture. Discover how immutable snapshots and Drizzle ORM maintain keyword position persistence in SQLite and PostgreSQL databases. Learn more about every-app open-seo.

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

---

**Open-SEO implements rank tracking as a bounded server-side feature using a TanStack-inspired layered architecture where immutable snapshots store keyword positions in a Drizzle ORM-managed database, supporting both SQLite and PostgreSQL.**

The Open-SEO repository structures its **rank tracking system** as a self-contained server feature following clean architecture principles. This design schedules automated SERP checks through DataForSEO and persists historical keyword positions as immutable snapshots. Understanding this architecture reveals how the application maintains data integrity while enabling time-series analysis of search rankings.

## Layered Architecture Overview

Open-SEO follows the TanStack "server-function → service → repository" pattern recommended in the project’s engineering guidelines. This creates clear separation of concerns across four distinct layers.

### API and Server-Function Layer

The public endpoints reside in [[`src/serverFunctions/rank-tracking.ts`](https://github.com/every-app/open-seo/blob/main/src/serverFunctions/rank-tracking.ts)](https://github.com/every-app/open-seo/blob/main/src/serverFunctions/rank-tracking.ts). This file exposes functions like `createRankTrackingConfig` and `getLatestResults` that the UI consumes. Payload validation occurs here using Zod schemas defined in [[`src/types/schemas/rank-tracking.ts`](https://github.com/every-app/open-seo/blob/main/src/types/schemas/rank-tracking.ts)](https://github.com/every-app/open-seo/blob/main/src/types/schemas/rank-tracking.ts).

### Service Layer

Business logic lives in [[`src/server/features/rank-tracking/services/RankTrackingService.ts`](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/services/RankTrackingService.ts)](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/services/RankTrackingService.ts). This layer handles scheduling checks, throttling DataForSEO calls, computing next-run timestamps, and aggregating results.

Supporting services include:
- **Scheduled workers**: [[`scheduledRankChecks.ts`](https://github.com/every-app/open-seo/blob/main/scheduledRankChecks.ts)](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/services/scheduledRankChecks.ts) runs cron-like jobs at configurable intervals for desktop and mobile checks across different locations.
- **Guard utilities**: [[`rankCheckRunGuards.ts`](https://github.com/every-app/open-seo/blob/main/rankCheckRunGuards.ts)](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/services/rankCheckRunGuards.ts) validates that checks respect daily limits and valid device/location combinations.
- **Result collector**: [[`rankTrackingResults.ts`](https://github.com/every-app/open-seo/blob/main/rankTrackingResults.ts)](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/services/rankTrackingResults.ts) normalizes raw DataForSEO responses into the internal `RankedKeyword` shape.

### Repository Layer

Data access is abstracted through [[`RankTrackingRepository.ts`](https://github.com/every-app/open-seo/blob/main/RankTrackingRepository.ts)](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/repositories/RankTrackingRepository.ts), which uses **Drizzle ORM** to persist both configurations and snapshot results. The repository supports SQLite for local development and PostgreSQL for production environments without code changes.

Read-only queries for UI visualizations live in [[`snapshotQueries.ts`](https://github.com/every-app/open-seo/blob/main/snapshotQueries.ts)](https://github.com/every-app/open-seo/blob/main/src/server/features/rank‑tracking/repositories/snapshotQueries.ts), supplying helpers to fetch latest snapshots and historical trend data.

### Shared Utilities

Common constants and schedule calculation logic reside in [[`src/shared/rank-tracking.ts`](https://github.com/every-app/open-seo/blob/main/src/shared/rank-tracking.ts)](https://github.com/every-app/open-seo/blob/main/src/shared/rank-tracking.ts). This includes `MAX_TRACKED_KEYWORD_LENGTH` and the `computeNextCheckAt` function used to determine when the next rank check should occur.

## How Keyword Positions Are Persisted

The **rank tracking system** persists keyword positions through an immutable snapshot pattern that preserves complete history for time-series analysis.

### The Persistence Flow

1. **Configuration Creation**: When a user creates a tracking config via the UI, the `createRankTrackingConfig` server function validates the payload and calls `RankTrackingRepository.createConfig`. Drizzle translates this into an `INSERT` on the `rankTrackingConfigs` table.

2. **Scheduled Execution**: The [`scheduledRankChecks.ts`](https://github.com/every-app/open-seo/blob/main/scheduledRankChecks.ts) worker runs at intervals defined by `scheduleInterval`. Before invoking DataForSEO, it executes guard checks via [`rankCheckRunGuards.ts`](https://github.com/every-app/open-seo/blob/main/rankCheckRunGuards.ts) to ensure rate limits and validation rules pass.

3. **Data Normalization**: Upon receiving DataForSEO's "live ranked keywords" response, the system passes the raw JSON to [`rankTrackingResults.ts`](https://github.com/every-app/open-seo/blob/main/rankTrackingResults.ts). This extracts the `ranking_position` and corresponding URL for each keyword.

4. **Snapshot Storage**: The service writes immutable records via `RankTrackingRepository.createSnapshot` to the `rankTrackingSnapshots` table. Each row contains:
   - `configId` — Foreign key to the tracking configuration
   - `keyword` — The tracked search term
   - `position` — Numeric SERP rank (1-N, or `null` if not found)
   - `url` — The landing page URL occupying that position
   - `device` — Either `desktop` or `mobile`
   - `checkedAt` — Timestamp of the API call

5. **Historical Retrieval**: When the UI requests latest positions through `getLatestResults`, the system calls `snapshotQueries.getLatestForConfig` to retrieve the most recent snapshot per keyword. The `rankChange` field is computed by comparing the current snapshot with the previous historical record.

## Practical Implementation Examples

### Creating a Rank Tracking Configuration

```typescript
await fetch('/api/rank-tracking', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    domain: 'example.com',
    keywords: ['open seo', 'rank tracking'],
    devices: ['desktop', 'mobile'],
    scheduleInterval: 'daily',
    locations: [{ country: 'US', city: 'New York' }],
  }),
});

```

### Fetching Latest Keyword Positions

```typescript
const resp = await fetch(`/api/rank-tracking/${configId}/results`);
const { results } = await resp.json(); 
// [{ keyword, position, url, rankChange }, …]

```

### Computing Next Check Time

```typescript
import { computeNextCheckAt, scheduleLabel } from '@/shared/rank-tracking';

export function scheduleNextCheck(config: RankTrackingConfig) {
  const next = computeNextCheckAt(config.lastCheckedAt, config.scheduleInterval);
  // Store `next` in the DB so the worker knows when to run again
}

```

## Summary

- **Open-SEO** implements rank tracking as a bounded server feature using a layered architecture: server-functions expose APIs, services contain business logic, and repositories handle data persistence.
- **Immutable snapshots** store keyword positions in the `rankTrackingSnapshots` table, enabling complete historical analysis without data loss.
- **Drizzle ORM** abstracts database operations, allowing the same code to run on SQLite (development) and PostgreSQL (production).
- **Scheduled workers** trigger rank checks at configurable intervals, with guard utilities enforcing rate limits and validation rules.
- **Time-series queries** compute `rankChange` by comparing consecutive snapshots, powering the UI's trend visualizations.

## Frequently Asked Questions

### What database systems does the rank tracking system support?

The **rank tracking system** uses Drizzle ORM in [`RankTrackingRepository.ts`](https://github.com/every-app/open-seo/blob/main/RankTrackingRepository.ts), which abstracts database operations to support both SQLite for local development and PostgreSQL for production environments. This dual-database compatibility requires zero code changes between environments.

### How does the system prevent duplicate or excessive rank checks?

The [`rankCheckRunGuards.ts`](https://github.com/every-app/open-seo/blob/main/rankCheckRunGuards.ts) utility validates each scheduled attempt against daily limits and configuration constraints before invoking DataForSEO. The scheduled worker in [`scheduledRankChecks.ts`](https://github.com/every-app/open-seo/blob/main/scheduledRankChecks.ts) respects these guards, ensuring throttled, compliant API usage that stays within provider rate limits.

### Can I query historical ranking trends for a keyword?

Yes. The [`snapshotQueries.ts`](https://github.com/every-app/open-seo/blob/main/snapshotQueries.ts) repository provides read-only helpers that fetch historical trend data from the `rankTrackingSnapshots` table. Because the system stores immutable snapshots rather than updating records in place, you can analyze position changes across any time range by comparing `checkedAt` timestamps.

### What happens when a keyword is not found in the SERP results?

When DataForSEO returns no ranking for a tracked keyword, the [`rankTrackingResults.ts`](https://github.com/every-app/open-seo/blob/main/rankTrackingResults.ts) normalizer stores `null` in the `position` field of the snapshot. The `url` field may also be null in this case. This distinction allows the UI to differentiate between a temporary API failure and a keyword that has genuinely dropped out of the index.