OpenSEO Database Schema and Migration Strategy: A Complete Technical Guide

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 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, which reads the DATABASE_PROVIDER environment variable. When set to "postgres", the application loads PostgreSQL table definitions; otherwise, it defaults to SQLite modules.

// 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 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, 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, defining core entities including projects, saved keywords, rank-tracking data, and audit results using Drizzle’s sqliteTable helper:

// 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 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 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 at the repository root:

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:

# Local development

wrangler d1 migrations apply DB --local

# Production

wrangler d1 migrations apply DB --remote

Shortcut via package.json scripts:

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, reading credentials from environment variables:

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:

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

Provider-Agnostic Query Patterns

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

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 enforces structural parity between dialects, while src/db/schema.ts provides a unified export interface.
• Separate migration pipelines: SQLite uses drizzle.config.ts and Wrangler commands; PostgreSQL uses 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. 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 and 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, 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, 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 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.

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