# OpenSEO Database Schema and Migration Strategy: A Complete Technical Guide

> Explore the OpenSEO database schema and migration strategy. Discover its dual-dialect architecture supporting Cloudflare D1 and PostgreSQL with Drizzle ORM for type-safe, runtime-switchable connections.

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

---

**OpenSEO implements a provider-agnostic dual-dialect architecture using Drizzle ORM that simultaneously supports Cloudflare D1 (SQLite) and PostgreSQL Hyperdrive, enabling seamless runtime switching via environment variables while maintaining type safety through canonical schema barrels.**

The open-source SEO platform [every-app/open-seo](https://github.com/every-app/open-seo) manages all persistent data through a sophisticated database abstraction layer. Unlike traditional single-database architectures, OpenSEO maintains parallel SQLite and PostgreSQL schema definitions that share identical type shapes, allowing developers to run locally on D1 while deploying to production PostgreSQL instances without code changes.

## Dual-Dialect Schema Architecture

OpenSEO’s database layer revolves around a **provider-aware schema barrel** that dynamically selects the appropriate dialect at runtime. This design eliminates vendor lock-in while ensuring consistent data structures across environments.

### Provider Detection and Runtime Selection

The system detects the active database provider through `getDatabaseProvider()` in [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts), which reads the `DATABASE_PROVIDER` environment variable. When set to `"postgres"`, the application loads PostgreSQL table definitions; otherwise, it defaults to SQLite modules.

```ts
// src/db/schema.ts (excerpt)
import { getDatabaseProvider } from "./provider";
import * as sqliteApp from "./app.schema";
import * as pgApp from "./pg/app.schema";

type AppSchema = typeof sqliteApp & typeof sqliteAudit;

const runtimeSchema =
  getDatabaseProvider() === "postgres"
    ? { ...pgApp, ...pgAudit }
    : { ...sqliteApp, ...sqliteAudit };

export const { projects, savedKeywords, audits, user } = 
  runtimeSchema as unknown as AppSchema;

```

### Canonical Schema Barrel Structure

The central [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) acts as the single source of truth for the entire application. It imports raw SQLite definitions from `src/db/*.schema.ts` files and PostgreSQL equivalents from `src/db/pg/*.schema.ts`, then re-exports a unified interface. This allows business logic to import tables without concern for the underlying dialect.

### Type Safety and Schema Parity

To prevent drift between dialects, OpenSEO includes [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts), a dedicated test suite that enforces structural parity between SQLite and PostgreSQL table definitions. A type assertion (`schema as unknown as AppSchema`) guarantees that both dialects expose identical shapes, catching column mismatches or type inconsistencies during development rather than at runtime.

## Table Definitions and Structure

Each logical entity exists as parallel implementations optimized for its respective database engine while maintaining API compatibility.

### Core Application Tables

The SQLite canonical definitions reside in [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts), defining core entities including projects, saved keywords, rank-tracking data, and audit results using Drizzle’s `sqliteTable` helper:

```ts
// src/db/app.schema.ts (conceptual)
import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";

export const projects = sqliteTable("projects", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  deleted: integer("deleted", { mode: "timestamp" }),
});

```

### Authentication Schema

User authentication tables (`user`, `session`, `account`) are defined in [`src/db/better-auth-schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/better-auth-schema.ts) following the Better Auth specification, ensuring compatibility with the authentication library while maintaining the dual-dialect pattern.

### PostgreSQL Equivalents

PostgreSQL variants live in [`src/db/pg/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/app.schema.ts) and utilize the `pgTable` helper. These mirror the SQLite structures but leverage PostgreSQL-specific features where appropriate, such as native UUID support or array types, while maintaining interface consistency.

## Migration Handling with Drizzle-Kit

OpenSEO maintains **separate migration pipelines** for each dialect, using Drizzle-Kit to generate SQL migration files while providing npm scripts for common operations.

### SQLite/D1 Migration Pipeline

Configuration for Cloudflare D1 resides in [`drizzle.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts) at the repository root:

```ts
import { defineConfig } from "drizzle-kit";
import { getLocalD1Url } from "@every-app/sdk/cloudflare/server";

export default defineConfig({
  dialect: "sqlite",
  schema: "./src/db/d1/schema.ts",
  out: "./drizzle",
  dbCredentials: { url: getLocalD1Url() || "" },
});

```

Apply SQLite migrations using Wrangler commands:

```bash

# Local development

wrangler d1 migrations apply DB --local

# Production

wrangler d1 migrations apply DB --remote

```

Shortcut via package.json scripts:

```bash
pnpm db:migrate:local  # Apply to local D1

pnpm db:migrate:prod   # Apply to production D1

```

### PostgreSQL Migration Pipeline

PostgreSQL configuration lives in [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts), reading credentials from environment variables:

```ts
import { defineConfig } from "drizzle-kit";
import { loadLocalEnv } from "./scripts/cli-utils";

loadLocalEnv(); // Loads POSTGRES_DATABASE_URL from .env.local

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/db/pg/schema.ts",
  out: "./drizzle-pg",
  dbCredentials: { url: process.env.POSTGRES_DATABASE_URL! },
});

```

Execute PostgreSQL migrations via:

```bash
pnpm db:migrate:pg  # Runs drizzle-kit migrate

```

### Schema Generation Workflow

When modifying table structures, developers must update both dialects to maintain parity:

1. Edit [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts) (SQLite) and [`src/db/pg/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/app.schema.ts) (PostgreSQL) simultaneously
2. Run `pnpm db:generate` to regenerate TypeScript types and create SQLite migration files in `drizzle/`
3. Run `pnpm db:generate:pg` for PostgreSQL equivalents in `drizzle-pg/`
4. Apply migrations to both providers using the respective commands

Generated migrations are plain SQL files (e.g., [`drizzle/0001_round_unus.sql`](https://github.com/every-app/open-seo/blob/main/drizzle/0001_round_unus.sql)) stored in dialect-specific directories and applied automatically by their respective tooling.

## Cross-Provider Data Migration

For production transitions from D1 to PostgreSQL, OpenSEO provides [`scripts/migrate-d1-to-postgres.ts`](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts), a utility script that performs paginated batch transfers while preserving IDs and handling conflicts. This one-time migration tool reads from SQLite in chunks and inserts into PostgreSQL tables, with detailed runbooks available in [`runbooks/d1-to-postgres-simple.md`](https://github.com/every-app/open-seo/blob/main/runbooks/d1-to-postgres-simple.md) and [`runbooks/d1-to-postgres-detailed.md`](https://github.com/every-app/open-seo/blob/main/runbooks/d1-to-postgres-detailed.md).

## Provider-Agnostic Query Patterns

Application code remains database-agnostic by importing from the canonical barrel:

```ts
import { db } from "@/db"; // Provider-aware Drizzle client
import { projects } from "@/db/schema";

async function listActiveProjects() {
  return await db
    .select()
    .from(projects)
    .where(projects.deleted.isNull())
    .all();
}

```

The `db` client initialization automatically configures the correct driver based on `DATABASE_PROVIDER`, ensuring queries execute identically regardless of the underlying engine.

## Summary

- **Dual-dialect architecture**: OpenSEO supports both Cloudflare D1 (SQLite) and PostgreSQL through parallel schema definitions in `src/db/` and `src/db/pg/`.
- **Runtime provider selection**: The `getDatabaseProvider()` function switches implementations based on the `DATABASE_PROVIDER` environment variable.
- **Type safety**: [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) enforces structural parity between dialects, while [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) provides a unified export interface.
- **Separate migration pipelines**: SQLite uses [`drizzle.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts) and Wrangler commands; PostgreSQL uses [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts) and Drizzle-Kit.
- **Workflow automation**: npm scripts (`pnpm db:generate`, `pnpm db:migrate:pg`) streamline schema updates across both providers.
- **Data portability**: Built-in migration scripts facilitate one-time transfers from D1 to PostgreSQL without data loss.

## Frequently Asked Questions

### How does OpenSEO switch between SQLite and PostgreSQL at runtime?

OpenSEO reads the `DATABASE_PROVIDER` environment variable through `getDatabaseProvider()` in [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts). When set to `"postgres"`, the runtime schema barrel exports PostgreSQL table definitions; otherwise, it exports SQLite equivalents. Both implementations share identical TypeScript interfaces through a type assertion, ensuring compile-time safety regardless of the active provider.

### Where are the database migrations stored in the OpenSEO repository?

Migration files reside in dialect-specific directories: `drizzle/` contains SQLite migration SQL files for D1, while `drizzle-pg/` stores PostgreSQL migrations. These are generated by Drizzle-Kit based on the respective config files ([`drizzle.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts) and [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts)) and should be committed to version control to ensure consistency across environments.

### What happens if the SQLite and PostgreSQL schemas diverge?

The repository includes [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts), a test suite that validates structural equivalence between the two dialects. Additionally, the type system enforces parity through the `AppSchema` type in [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts), which requires both SQLite and PostgreSQL module types to align. Any divergence will trigger TypeScript compilation errors or test failures during the build process.

### Can I migrate existing data from Cloudflare D1 to PostgreSQL without downtime?

OpenSEO provides [`scripts/migrate-d1-to-postgres.ts`](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts) specifically for this scenario. The script executes paginated reads from SQLite and batch inserts into PostgreSQL while preserving primary keys and handling unique constraint conflicts. While the migration runs, you should pause writes to the D1 database, run the script, then update `DATABASE_PROVIDER` to `"postgres"` and redeploy.