Open-SEO Rank Tracking System Architecture and Keyword Position Persistence

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

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). 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/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/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/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/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/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). 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 worker runs at intervals defined by scheduleInterval. Before invoking DataForSEO, it executes guard checks via 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. 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

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

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

Computing Next Check Time

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, 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 utility validates each scheduled attempt against daily limits and configuration constraints before invoking DataForSEO. The scheduled worker in 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 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 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.

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