How Data Is Stored in Open-SEO: PostgreSQL, Cloudflare D1, and Drizzle ORM Architecture

Open-SEO uses a relational data model powered by Drizzle ORM that dynamically switches between PostgreSQL for self-hosted deployments and Cloudflare D1 (SQLite-compatible) for SaaS environments based on the DB environment variable.

The open-seo repository by every-app implements a sophisticated dual-database architecture designed for flexibility across deployment scenarios. Whether you're running a self-managed instance or using the hosted SaaS version, the data layer remains consistent through abstracted schema definitions and unified query patterns. This article explains exactly how data storage works in Open-SEO, with direct references to the source code structure.

Dual Database Provider Support

Open-SEO's most distinctive feature is its runtime database provider selection. The system evaluates the DB environment variable at startup to determine which database engine to use.

When DB equals "postgres", the application loads the PostgreSQL schema and connects to a standard Postgres instance. For any other value (or when undefined), it defaults to Cloudflare D1, a SQLite-compatible edge database. This design allows the same codebase to power both enterprise self-hosting and serverless SaaS deployments without conditional logic scattered throughout the application.

The provider abstraction lives in src/db/provider.ts, which exports a unified db instance that hides the underlying engine differences from the rest of the application.

Schema Organization and File Structure

Database schemas are organized into distinct folders based on target engine, with intentional mirroring between implementations:

PostgreSQL Schemas

PostgreSQL-specific table definitions reside in src/db/pg/*.schema.ts files. Key files include:

• src/db/pg/app.schema.ts — Core application tables (projects, keywords, audits, rank tracking)
• src/db/pg/better-auth-schema.ts — Authentication and multi-tenancy tables including user, session, account, organization, member, invitation, and apikey

SQLite/D1 Schemas

The Cloudflare D1 equivalent schemas live directly in src/db/*.schema.ts, with src/db/app.schema.ts mirroring the PostgreSQL structure for cross-compatibility.

Both implementations use Drizzle's type-safe table helpers — pgTable for PostgreSQL and sqliteTable for D1 — ensuring consistent column types and constraints across engines.

Core Data Entities

The Open-SEO schema covers the complete SEO tool domain across six functional areas:

Authentication and Tenancy

• user — Platform users
• session — Active authentication sessions
• account — OAuth-linked accounts
• organization — Multi-tenant workspace boundary
• member — Organization membership records
• invitation — Pending organization invites
• apikey — Programmatic access credentials

Projects and Keyword Management

• projects — SEO campaign containers
• savedKeywords — Keyword research library
• savedKeywordTags — Taxonomy for keyword organization
• savedKeywordTagAssignments — Many-to-many tag relationships
• keywordMetrics — Historical search volume and difficulty data

Rank Tracking

• rankTrackingConfigs — Monitoring configuration per project
• rankTrackingKeywords — Keywords under active surveillance
• rankCheckRuns — Individual ranking check executions
• rankSnapshots — SERP position records with timestamps

SEO Audits

• audits — Crawl campaign records
• auditPages — Discovered URLs with metadata
• auditIssues — Detected technical SEO problems
• auditLighthouseResults — Performance and quality scores

Backlinks and Telemetry

• backlinkSnapshots — External link profile captures
• telemetryState — Internal operational metrics

Third-Party Integrations

• gscConnections — Google Search Console OAuth links
• ga4Connections — Google Analytics 4 bindings
• samSessions and samProjectMemory — Integration state management

Migration Strategy

Database migrations follow Drizzle's standard patterns with engine-specific storage:

• PostgreSQL migrations live in drizzle-pg/ as raw SQL files (e.g., 0000_fixed_nico_minoru.sql for initial table creation)
• Migrations apply automatically during application startup when PostgreSQL is selected
• D1 migrations leverage Cloudflare's native migration system through Wrangler

This split approach respects each platform's operational model while maintaining schema parity.

Querying Data: Server and Client Patterns

Server-Side Database Access

Server functions in src/serverFunctions/* import the abstracted db instance from @/db and execute type-safe queries using Drizzle's query builder:

// Example: fetching a project and its keywords (Postgres or D1)
import { db } from "@/db";
import { projects, savedKeywords } from "@/db/app.schema";

export async function getProjectWithKeywords(projectId: string) {
  const project = await db
    .select()
    .from(projects)
    .where(eq(projects.id, projectId))
    .limit(1);

  const keywords = await db
    .select()
    .from(savedKeywords)
    .where(eq(savedKeywords.projectId, projectId));

  return { ...project[0], keywords };
}

The eq() helper and other Drizzle operators provide SQL injection-safe parameter binding regardless of underlying engine.

Insert and Update Operations

// Example: inserting a new rank-tracking run
import { db } from "@/db";
import { rankCheckRuns } from "@/db/app.schema";

export async function startRankCheckRun(configId: string, userId: string) {
  const [run] = await db
    .insert(rankCheckRuns)
    .values({ configId, createdBy: userId, startedAt: new Date() })
    .returning();
  return run.id;
}

The .returning() clause — supported by both PostgreSQL and SQLite 3.35+ — enables immediate access to generated values like auto-increment IDs.

Client-Side Data Fetching

Browser code does not access the database directly. Instead, Open-SEO uses TanStack Query utilities located in src/client/tanstack-db/ to communicate with server endpoints. This architecture provides:

• Automatic caching and background refetching
• Optimistic updates for responsive UI
• Request deduplication and stale-while-revalidate behavior

The queryClient.ts file in this directory configures the global query client with appropriate defaults for the Open-SEO data access patterns.

Key Implementation Files

Purpose	Path	Description
Central schema exports	src/db/schema.ts	Re-exports appropriate schema based on build
PostgreSQL tables	src/db/pg/app.schema.ts	Production-grade column types with indexes
D1 tables	src/db/app.schema.ts	SQLite-optimized equivalents
Auth schema	src/db/pg/better-auth-schema.ts	Better-Auth integration tables
Initial migration	drizzle-pg/0000_fixed_nico_minoru.sql	Base table creation statements
Provider selector	src/db/provider.ts	Runtime engine detection and client export
Query client	src/client/tanstack-db/queryClient.ts	TanStack Query configuration
Usage example	src/serverFunctions/projects.ts	Real-world database access patterns

Summary

• Open-SEO stores data relationally using Drizzle ORM with dual-engine support for PostgreSQL and Cloudflare D1.
• Provider selection happens at runtime in src/db/provider.ts based on the DB environment variable.
• Schema files are engine-specific but structurally mirrored in src/db/pg/ and src/db/ directories.
• All major SEO domains are covered: authentication, projects, keywords, rank tracking, audits, backlinks, and integrations.
• Migrations are engine-segregated with PostgreSQL using Drizzle's SQL file approach and D1 using Cloudflare's system.
• Server functions use unified db imports while client code accesses data through TanStack Query abstractions.

Frequently Asked Questions

What database does Open-SEO use by default?

If the DB environment variable is unset or set to any value other than "postgres", Open-SEO defaults to Cloudflare D1, a SQLite-compatible serverless database. This suits the SaaS deployment model where Cloudflare's edge infrastructure hosts the application. For self-hosted installations, explicitly setting DB=postgres activates the PostgreSQL code path.

Can I migrate from D1 to PostgreSQL or vice versa?

The schemas in src/db/pg/app.schema.ts and src/db/app.schema.ts are designed to be structurally equivalent, making logical migration possible. However, Open-SEO does not include automated migration tooling between engines. You would need to export data from the source database and import into the target, adjusting for any auto-increment sequence differences.

How does Open-SEO handle type safety across both database engines?

Drizzle ORM provides the foundation — both pgTable and sqliteTable helpers generate TypeScript types from schema definitions. The src/db/provider.ts module exports a unified db instance typed according to the selected engine, so consuming code receives appropriate autocompletion and compile-time checking without engine-specific conditionals.

Where are the database migration files located?

PostgreSQL migrations are stored in the drizzle-pg/ directory as versioned SQL files like 0000_fixed_nico_minoru.sql. These execute automatically during application startup. D1 migrations follow Cloudflare's convention and are typically managed through Wrangler CLI commands rather than stored in the repository's drizzle folder.

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