# OpenSEO Database Schema and Migrations: A Complete Technical Guide

> Explore OpenSEO's dual-dialect database schema and migrations. Learn how Drizzle ORM supports Cloudflare D1 SQLite and PostgreSQL with provider-aware definitions and separate pipelines.

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

---

**OpenSEO uses a dual-dialect schema with Drizzle ORM that supports both Cloudflare D1 (SQLite) and PostgreSQL, with provider-aware table definitions and separate migration pipelines for each database engine.**

The `every-app/open-seo` repository implements a sophisticated database architecture designed for flexibility across serverless and traditional database environments. This guide examines the schema structure, migration approach, and operational commands that power OpenSEO's persistence layer.

## Dual-Dialect Schema Architecture

OpenSEO's database layer abstracts **two SQL dialects** behind a unified interface. The canonical schema lives in [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) as a **provider-aware barrel** that dynamically selects SQLite or PostgreSQL table definitions at runtime.

### How Provider Selection Works

The `getDatabaseProvider()` function in [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts) reads the `DATABASE_PROVIDER` environment variable. When set to `"postgres"`, the runtime spreads PostgreSQL modules into the active schema; otherwise, it defaults to SQLite modules imported from `src/db/*.schema.ts`.

```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,
  // … all tables re-exported
} = runtimeSchema as unknown as AppSchema;

```

A **type assertion** (`schema as unknown as AppSchema`) guarantees that both dialects expose identical type shapes. The test suite in [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) enforces this structural parity automatically.

## Table Definitions and Organization

Each logical entity is defined twice—once for each dialect—using dialect-specific Drizzle helpers.

### Core Application Tables

- **[`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts)** — Defines SQLite tables for projects, saved keywords, rank tracking, and other core entities using `sqliteTable()`.
- **[`src/db/pg/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/app.schema.ts)** — Mirrors the above with `pgTable()` for PostgreSQL compatibility.
- **[`src/db/better-auth-schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/better-auth-schema.ts)** — Authentication tables (`user`, `session`, `account`, `verification`) shared across both dialects.

All tables are re-exported from [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts), so application code imports from a single source regardless of runtime provider:

```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();
}

```

## Migration Pipeline with Drizzle-Kit

OpenSEO maintains **separate migration pipelines** for each database dialect, each with dedicated configuration files, output directories, and npm scripts.

### SQLite (Cloudflare D1) Migrations

| Aspect | Configuration |
|--------|---------------|
| Config file | [`drizzle.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts) |
| Output directory | `drizzle/` |
| Dialect | `sqlite` |
| Schema source | [`./src/db/d1/schema.ts`](https://github.com/every-app/open-seo/blob/main/./src/db/d1/schema.ts) |

```ts
// drizzle.config.ts (excerpt)
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 via Wrangler:

```bash

# Local development

wrangler d1 migrations apply DB --local

# Production

wrangler d1 migrations apply DB --remote

```

### PostgreSQL Migrations

| Aspect | Configuration |
|--------|---------------|
| Config file | [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts) |
| Output directory | `drizzle-pg/` |
| Dialect | `postgresql` |
| Schema source | [`./src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/./src/db/pg/schema.ts) |

```ts
// drizzle-pg.config.ts (excerpt)
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! },
});

```

Apply PostgreSQL migrations:

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

```

## Developer Workflow for Schema Changes

Adding or modifying tables requires synchronized updates across both dialects:

1. **Edit SQLite definition** in [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts) (e.g., add `newFlag: boolean("new_flag").default(false)` to the `projects` table).
2. **Generate SQLite schema**: `pnpm db:generate`
3. **Generate PostgreSQL schema**: `pnpm db:generate:pg`
4. **Create migration**: `pnpm db:generate` auto-creates a new `.sql` file in `drizzle/` (e.g., [`drizzle/0001_round_unus.sql`](https://github.com/every-app/open-seo/blob/main/drizzle/0001_round_unus.sql)).
5. **Apply to both providers**:
   ```bash
   pnpm db:migrate:local  # SQLite local

   pnpm db:migrate:pg     # PostgreSQL

   ```

## Cross-Provider Data Migration

For production transitions from D1 to PostgreSQL, OpenSEO includes [`scripts/migrate-d1-to-postgres.ts`](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts). This script performs a **one-time data migration** by:

- Reading SQLite data in **paginated batches**
- Inserting into PostgreSQL tables with **conflict handling**
- **Preserving primary keys** and relationships

Documentation lives 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).

## Key Files Reference

| Path | Purpose |
|------|---------|
| [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) | Provider-aware schema barrel; unified export point |
| [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts) | Runtime detection of `DATABASE_PROVIDER` |
| [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts) | SQLite table definitions for core entities |
| [`src/db/pg/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/app.schema.ts) | PostgreSQL table definitions |
| [`drizzle.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts) | Drizzle-Kit configuration for SQLite/D1 |
| [`drizzle-pg.config.ts`](https://github.com/every-app/open-seo/blob/main/drizzle-pg.config.ts) | Drizzle-Kit configuration for PostgreSQL |
| `drizzle/*.sql` | Generated SQLite migration files |
| `drizzle-pg/*.sql` | Generated PostgreSQL migration files |
| [`scripts/migrate-d1-to-postgres.ts`](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts) | D1 → PostgreSQL data migration utility |
| [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) | Automated parity validation between dialects |

## Summary

- **Dual-dialect design**: SQLite (D1) as canonical source, PostgreSQL as runtime alternative via `DATABASE_PROVIDER`.
- **Provider abstraction**: Single import point ([`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts)) abstracts dialect differences from application code.
- **Separate pipelines**: Independent Drizzle-Kit configs and migration directories for each database engine.
- **Type safety**: Runtime type assertions plus automated [`schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/schema-parity.test.ts) ensure structural equivalence.
- **Operational tooling**: One-time migration script and documented runbooks support provider transitions.

## Frequently Asked Questions

### How does OpenSEO handle schema changes across both database providers?

Developers modify the SQLite schema first in [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts), then run `pnpm db:generate` and `pnpm db:generate:pg` to synchronize both dialects. The [`schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/schema-parity.test.ts) test suite fails if the PostgreSQL definitions drift from the SQLite canonical source, enforcing manual synchronization.

### What migration commands should I use for local development?

For SQLite/D1 local development, run `pnpm db:migrate:local` (which wraps `wrangler d1 migrations apply DB --local`). For PostgreSQL local development, ensure `POSTGRES_DATABASE_URL` is set in `.env.local`, then run `pnpm db:migrate:pg`.

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

Yes. The [`scripts/migrate-d1-to-postgres.ts`](https://github.com/every-app/open-seo/blob/main/scripts/migrate-d1-to-postgres.ts) script performs batched reads from D1 and inserts into PostgreSQL with conflict resolution. Runbooks 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) provide step-by-step guidance for this one-time operation.

### Does OpenSEO support running both databases simultaneously in production?

No. The `getDatabaseProvider()` function in [`src/db/provider.ts`](https://github.com/every-app/open-seo/blob/main/src/db/provider.ts) selects a **single active provider** at runtime based on `DATABASE_PROVIDER`. The architecture supports migration between providers, not active-active or multi-tenant usage across both engines.