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

> Discover how Open-SEO stores data using PostgreSQL or Cloudflare D1 with Drizzle ORM. Explore the flexible architecture for self-hosted and SaaS deployments.

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

---

**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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/pg/app.schema.ts)** — Core application tables (projects, keywords, audits, rank tracking)
- **[`src/db/pg/better-auth-schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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:

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

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) | Re-exports appropriate schema based on build |
| PostgreSQL tables | [`src/db/pg/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/app.schema.ts) | Production-grade column types with indexes |
| D1 tables | [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts) | SQLite-optimized equivalents |
| Auth schema | [`src/db/pg/better-auth-schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/better-auth-schema.ts) | Better-Auth integration tables |
| Initial migration | [`drizzle-pg/0000_fixed_nico_minoru.sql`](https://github.com/every-app/open-seo/blob/main/drizzle-pg/0000_fixed_nico_minoru.sql) | Base table creation statements |
| Provider selector | [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts) | Runtime engine detection and client export |
| Query client | [`src/client/tanstack-db/queryClient.ts`](https://github.com/every-app/open-seo/blob/main/src/client/tanstack-db/queryClient.ts) | TanStack Query configuration |
| Usage example | [`src/serverFunctions/projects.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/pg/app.schema.ts) and [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.