How Open-SEO Handles Data Storage and Fetching: D1 SQLite and External APIs

Open-SEO persists all application state in a Cloudflare D1 SQLite database accessed through Drizzle ORM, while fetching external SEO data via typed service wrappers that extract and persist only required fields.

Open-SEO from the every-app/open-seo repository is a modern SEO platform built on Cloudflare's edge infrastructure. The application employs a dual-layer data strategy: all internal state—from user projects to historical rank snapshots—lives in a Cloudflare D1 database, while real-time SEO metrics and search data are fetched on-demand from third-party APIs like DataForSEO and Google Search Console. This architecture ensures long-term data persistence at the edge while minimizing storage costs for volatile external datasets.

Core Data Persistence: Cloudflare D1 and Drizzle ORM

Open-SEO stores all persistent data in a Cloudflare D1 SQLite database, interacting with it exclusively through Drizzle ORM. The database connection is established in src/db/index.ts, which creates a singleton db instance bound to the env.DB D1 binding and exports it for use throughout the application.

// src/db/index.ts
import { drizzle } from 'drizzle-orm/d1';
import * as schema from './schema';

export const db = drizzle(env.DB, { schema });

The complete database schema is aggregated in src/db/schema.ts, which re-exports tables from modular sub-schemas including app.schema.ts (projects and keywords), gsc.schema.ts (Google Search Console connections), billing.schema.ts (credit tracking), and better-auth-schema.ts (authentication tables). This modular approach keeps domain logic separated while maintaining a single source of truth for the Drizzle client.

Reading Application State with Repository Patterns

Server-side code retrieves data using Drizzle's query helpers through typed repositories. For example, the keyword research feature fetches saved keywords and their cached metrics via the KeywordResearchRepository in src/server/features/keywords/repositories/KeywordResearchRepository.ts.

The repository abstracts the underlying Drizzle queries, using methods like db.query.saved_keywords.findMany or db.select joins between saved_keywords and keyword_metrics tables. This pattern ensures that business logic remains database-agnostic and easily testable.

// src/server/features/keywords/repositories/KeywordResearchRepository.ts
import { KeywordResearchRepository } from '@/server/features/keywords/repositories/KeywordResearchRepository';

async function getSavedKeywords(projectId: string) {
  return await KeywordResearchRepository.listSavedKeywordsByProject(projectId);
}

Writing Data: Inserts, Updates, and Batch Operations

Write operations use standard Drizzle methods—db.insert().values(), db.update().set(), and db.delete()—with bulk operations optimized through db.batch(). The rank-tracking system demonstrates this pattern when persisting SERP position snapshots.

In src/server/features/rank-tracking/repositories/RankTrackingRepository.ts, the insertSnapshots method performs bulk inserts into the rank_snapshots table using a batch transaction:

// src/server/features/rank-tracking/repositories/RankTrackingRepository.ts
import { db } from '@/db';
import { rankSnapshots } from '@/db/schema';

async function insertSnapshots(snapshots: Array<{ runId: string; trackingKeywordId: string; keyword: string; device: string; position?: number; url?: string }>) {
  await db.batch(snapshots.map(s => db.insert(rankSnapshots).values(s)));
}

This approach minimizes database round-trips when recording hundreds of keyword positions during a rank-check workflow.

External Data Fetching: Service Wrappers and DTOs

Unlike internal state, external SEO data is never cached in raw API form. Instead, Open-SEO maintains typed service wrappers that call third-party APIs, extract specific fields, and persist only normalized data to D1.

DataForSEO Integration

The platform leverages DataForSEO for keyword research, SERP analysis, backlinks, and Lighthouse audits. The low-level client resides in src/server/lib/dataforseo/, while higher-level services consume these utilities. For example, SERP fetching uses a dedicated module:

// src/server/lib/dataforseo/serp.ts
import { serp } from '@/server/lib/dataforseo';

async function fetchSerp(domain: string, depth: number) {
  const response = await serp.search({ target: domain, depth });
  return response.results; // parsed SERP rows
}

Google Search Console API

GSC integration is encapsulated in src/server/features/gsc/services/GscService.ts, which handles OAuth grants, site listing, and search analytics. The service returns clean DTOs rather than raw JSON, as seen in the listSitesForUserWithGrantStatus method used by server functions in src/serverFunctions/gsc.ts:

// src/server/features/gsc/services/GscService.ts
import { GscService } from '@/server/features/gsc/services/GscService';

async function listGscSites(userId: string) {
  const result = await GscService.listSitesForUserWithGrantStatus(userId);
  return result.sites; // array of { siteUrl, permissionLevel, … }
}

Error Handling and Credit Management

Every external API call carries a computational cost tracked through the billing system. Before executing paid requests, Open-SEO consults src/shared/billing.ts to verify the organization's credit balance. Costs are recorded in the billing schema tables defined in src/db/billing.schema.ts.

Credit-free operations—such as listing saved keywords via the MCP tool—read exclusively from the internal D1 database. Credit-based operations—like fresh SERP checks—trigger deductions from the organization's pool before the external request executes. Expected failures (e.g., expired OAuth tokens) trigger reconnect flows, while unexpected errors propagate for monitoring.

Summary

• Cloudflare D1 serves as the sole persistence layer for all application state, accessed through Drizzle ORM via the singleton exported from src/db/index.ts.
• Modular schemas in src/db/ organize tables by domain (projects, keywords, GSC, billing) while src/db/schema.ts provides unified exports.
• Repository patterns abstract database queries for features like keyword research and rank tracking, utilizing db.query for reads and db.batch for efficient bulk writes.
• External data flows through typed service wrappers (DataForSEO lib, GscService) that normalize API responses before persistence, storing only extracted fields rather than raw payloads.
• Billing integration in src/shared/billing.ts ensures API costs are tracked and deducted from organization credits before external requests execute.

Frequently Asked Questions

What database does Open-SEO use for data storage?

Open-SEO uses Cloudflare D1, a serverless SQLite database running on Cloudflare's edge network. The application connects to D1 through Drizzle ORM, with the connection initialized in src/db/index.ts and bound to the env.DB environment variable.

How does Open-SEO handle bulk data writes for rank tracking?

The system uses Drizzle's db.batch() method to insert multiple rank snapshots efficiently. In src/server/features/rank-tracking/repositories/RankTrackingRepository.ts, the insertSnapshots method maps snapshot arrays to insert statements and executes them as a single batch transaction against the rank_snapshots table.

What ORM does Open-SEO use?

Open-SEO uses Drizzle ORM for all database operations. This includes querying with db.query.<table>.findMany, selecting with db.select, inserting with db.insert, and performing batch operations. The schema is defined in TypeScript files under src/db/ and includes tables for projects, keywords, audits, and billing.

How does Open-SEO manage costs when fetching external SEO data?

The platform implements a credit-based billing system defined in src/shared/billing.ts and stored in billing.schema.ts. Before calling paid APIs like DataForSEO, the system verifies the organization's credit balance. Operations reading from internal D1 storage (like listing saved keywords) are credit-free, while external fetches deduct costs from the organization's pool.

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