OpenSEO Database Schema and Migrations: A Complete Technical Guide

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 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 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.

// 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 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 — Defines SQLite tables for projects, saved keywords, rank tracking, and other core entities using sqliteTable().
• src/db/pg/app.schema.ts — Mirrors the above with pgTable() for PostgreSQL compatibility.
• 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, so application code imports from a single source regardless of runtime provider:

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
Output directory	drizzle/
Dialect	sqlite
Schema source	./src/db/d1/schema.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:

# 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
Output directory	drizzle-pg/
Dialect	postgresql
Schema source	./src/db/pg/schema.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:

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 (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).
5. Apply to both providers:

   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. 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 and runbooks/d1-to-postgres-detailed.md.

Key Files Reference

Path	Purpose
src/db/schema.ts	Provider-aware schema barrel; unified export point
src/db/provider.ts	Runtime detection of DATABASE_PROVIDER
src/db/app.schema.ts	SQLite table definitions for core entities
src/db/pg/app.schema.ts	PostgreSQL table definitions
drizzle.config.ts	Drizzle-Kit configuration for SQLite/D1
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	D1 → PostgreSQL data migration utility
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) 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 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, then run pnpm db:generate and pnpm db:generate:pg to synchronize both dialects. The 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 script performs batched reads from D1 and inserts into PostgreSQL with conflict resolution. Runbooks in runbooks/d1-to-postgres-simple.md and 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 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.

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