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

> Discover how Open-SEO manages data. It uses D1 SQLite for storage and Drizzle ORM, fetching external API data efficiently through typed service wrappers.

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

---

**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`](https://github.com/every-app/open-seo/blob/main/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.

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts), which re-exports tables from modular sub-schemas including [`app.schema.ts`](https://github.com/every-app/open-seo/blob/main/app.schema.ts) (projects and keywords), [`gsc.schema.ts`](https://github.com/every-app/open-seo/blob/main/gsc.schema.ts) (Google Search Console connections), [`billing.schema.ts`](https://github.com/every-app/open-seo/blob/main/billing.schema.ts) (credit tracking), and [`better-auth-schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/src/server/features/rank-tracking/repositories/RankTrackingRepository.ts), the `insertSnapshots` method performs bulk inserts into the `rank_snapshots` table using a batch transaction:

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

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/serverFunctions/gsc.ts):

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/index.ts).
- **Modular schemas** in `src/db/` organize tables by domain (projects, keywords, GSC, billing) while [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/shared/billing.ts) and stored in [`billing.schema.ts`](https://github.com/every-app/open-seo/blob/main/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.