Database Migrations and Drizzle Schema Structure in OpenSEO

OpenSEO utilizes Drizzle-Kit to manage a dual-provider database architecture supporting both Cloudflare D1 (SQLite) and PostgreSQL, with 20+ versioned SQL migrations for PostgreSQL housed in drizzle-pg/ and runtime schema generation for D1.

The every-app/open-seo repository implements a provider-agnostic, type-safe database layer using Drizzle ORM. The schema is designed to work seamlessly across SQLite (via Cloudflare D1) and PostgreSQL environments while maintaining a single source of truth for table definitions under src/db.

Drizzle Schema Architecture

OpenSEO employs a provider-aware barrel pattern to abstract database dialect differences. Two Drizzle-Kit configuration files manage the separate providers:

• drizzle.config.ts – Targets Cloudflare D1 (SQLite) using the d1-http driver
• drizzle-prod.config.ts – Targets PostgreSQL for production deployments

Each configuration points to a barrel file that re-exports schema modules from provider-specific subdirectories:

• SQLite barrel: src/db/d1/schema.ts re-exports from ../app.schema, ../audit.schema, ../sam.schema, ../better-auth-schema, ../billing.schema, ../ga4.schema, ../gsc.schema, and ../telemetry.schema
• PostgreSQL barrel: src/db/pg/schema.ts performs the same re-exports but resolves to the pg/ directory variants

This structure allows application code to import tables without knowing the underlying driver, ensuring type safety across both database providers.

Core Schema Modules

The database is organized into nine primary modules under src/db/, each handling distinct domain concerns:

• Authentication & Organizations (src/db/better-auth-schema.ts): Contains organization, user, account, member, invitation, and session tables with proper foreign-key constraints and indexes for the Better Auth integration.

• Projects & Keywords (src/db/app.schema.ts): Manages projects, saved_keywords, saved_keyword_tags, saved_keyword_tag_assignments, and keyword_metrics tables for SEO campaign data.

• Rank Tracking (src/db/app.schema.ts): Includes rank_tracking_configs, rank_tracking_keywords, rank_check_runs, and rank_snapshots with composite indexes for performance.

• Audits (src/db/audit.schema.ts): Defines audits, audit_pages, and audit_lighthouse_results for storing technical SEO audit data.

• Search Console & GA4 (src/db/gsc.schema.ts and src/db/ga4.schema.ts): Houses gsc_connections and ga4_connections tables for third-party API integrations.

• Backlinks (src/db/app.schema.ts): Contains backlink_snapshots for external link monitoring.

• Telemetry (src/db/telemetry.schema.ts): Stores internal metrics in the telemetry table.

• SAM (Search-Ads-Metrics) (src/db/sam.schema.ts): Dedicated module for search advertising data tables.

• Billing (src/db/billing.schema.ts): Handles subscription and payment-related tables.

Each table is declared using Drizzle-ORM helpers (sqliteTable, pgTable, text, integer, real, index, uniqueIndex) with dialect-specific column types, foreign-key constraints, partial unique indexes, and default values generated at runtime.

Database Migration System

OpenSEO maintains separate migration strategies for its two database providers. The PostgreSQL implementation uses versioned SQL files, while the D1 implementation relies on runtime schema synchronization.

PostgreSQL Migration Files

All PostgreSQL migrations reside in the drizzle-pg/ directory and follow sequential numbering (e.g., 0000_*, 0001_*). The current schema includes 20+ migration files that build the database incrementally:

• 0000_fixed_nico_minoru.sql – Creates foundational tables including audit_lighthouse_results, audit_pages, audits, keyword_metrics, projects, rank_check_runs, and rank_snapshots
• 0001_striped_bulldozer.sql – Adds foreign-key constraints and performance indexes to core tables
• 0002_clean_moira_mactaggert.sql – Introduces rank_tracking_configs with unique indexes
• 0003_sturdy_may_parker.sql – Creates rank_tracking_keywords with uniqueness rules
• 0004_dashing_betty_ross.sql – Adds rank_check_runs with a partial-unique constraint enforcing one active run per configuration
• 0005_talented_wild_pack.sql – Creates rank_snapshots with composite indexes for fast lookups
• 0006_location_name.sql – Adds the location_name column to rank_tracking_configs
• 0007_same_marvel_zombies.sql – Introduces keyword tagging: saved_keywords, saved_keyword_tags, and saved_keyword_tag_assignments
• 0008_yummy_annihilus.sql – Adds indexes and constraints to keyword_metrics
• 0009_supreme_captain_stacy.sql – Creates user_onboarding_answers table
• 0010_overrated_amazoness.sql – Adds account, session, and related authentication indexes
• 0011_friendly_morlun.sql – Introduces invitation and member tables for organization management
• 0012_dashboard.sql – Creates organization_activation_state and project_activation_state tracking tables
• 0013_sleepy_black_tarantula.sql – Adds backlink_snapshots
• 0014_solid_centennial.sql – Creates verification table
• 0015_sticky_dagger.sql – Adds gsc_connections for Google Search Console integration
• 0016_panoramic_blob.sql and 0017_ga4_connections.sql – Create ga4_connections for Google Analytics 4
• 0018_drop_reddit_attributions.sql – Removes the deprecated reddit_attributions table
• 0019_clammy_selene.sql – Final cleanup migration adding missing indexes and constraints

D1 (SQLite) Runtime Schema

The Cloudflare D1 provider does not use static .sql migration files. Instead, the schema is generated directly from the TypeScript definitions at runtime. All structural changes for D1 are reflected immediately in the schema modules under src/db/d1/, and Drizzle-Kit applies these changes through the D1 HTTP API without intermediate SQL files.

Running Migrations

For PostgreSQL deployments, execute the migration suite using the Drizzle-Kit CLI:

# Install the CLI if not present

pnpm add -D drizzle-kit

# Execute all pending migrations in order

pnpm drizzle-kit migrate --config drizzle-prod.config.ts

The CLI reads drizzle-prod.config.ts, connects to the target database, and executes the numbered SQL files in drizzle-pg/ sequentially. To generate a new migration after schema changes:

pnpm drizzle-kit generate --config drizzle-prod.config.ts --name add_feature_table

How the Pieces Fit Together

The database architecture follows a four-layer workflow:

1. Schema Definition – TypeScript modules under src/db/* export Drizzle-ORM table objects using provider-specific helpers (sqliteTable vs pgTable).

2. Provider Barrels – src/db/d1/schema.ts and src/db/pg/schema.ts re-export these modules, allowing the application to import from a single path while the correct dialect resolves at runtime.

3. Configuration – drizzle.config.ts (development/D1) and drizzle-prod.config.ts (production/PostgreSQL) point to the appropriate barrel and specify the dialect and driver.

4. Migration Execution – For PostgreSQL, the CLI executes the drizzle-pg/*.sql files in order. For D1, the schema is pushed directly via the Drizzle-Kit API.

Importing Tables in Application Code

Use the barrel imports to query tables with full TypeScript type safety:

import { db } from "@every-app/sdk/drizzle";
import { projects } from "./src/db/app.schema";
import { eq, desc } from "drizzle-orm";

// Fetch active projects for an organization
const orgId = "org_123";
const activeProjects = await db
  .select()
  .from(projects)
  .where(eq(projects.organizationId, orgId))
  .orderBy(desc(projects.createdAt));

This import pattern works identically for both SQLite and PostgreSQL deployments because the barrel file abstracts the driver-specific implementation details.

Summary

• OpenSEO supports dual database providers: Cloudflare D1 (SQLite) for edge deployment and PostgreSQL for traditional hosting.
• The Drizzle schema is organized into nine modular files under src/db/, covering authentication, audits, rank tracking, billing, and integrations.
• PostgreSQL migrations are versioned SQL files (20+ total) stored in drizzle-pg/, executed sequentially by Drizzle-Kit.
• D1 uses runtime schema generation rather than static migration files, applying changes directly from TypeScript definitions.
• Provider barrels at src/db/d1/schema.ts and src/db/pg/schema.ts enable dialect-agnostic imports throughout the application.

Frequently Asked Questions

What is the difference between D1 and PostgreSQL migrations in OpenSEO?

PostgreSQL migrations are explicit SQL files in the drizzle-pg/ directory that Drizzle-Kit executes sequentially, providing a versioned history of schema changes. D1 (SQLite) migrations do not use static SQL files; instead, the schema is generated and applied directly from the TypeScript definitions at runtime using the Drizzle-Kit push command.

How do I run database migrations in OpenSEO?

For PostgreSQL, run pnpm drizzle-kit migrate --config drizzle-prod.config.ts from the project root. This executes all pending .sql files in drizzle-pg/ in numerical order. For D1, use the push command against the D1 HTTP endpoint, which synchronizes the live database with the current TypeScript schema definitions.

Where are the Drizzle table definitions located?

Table definitions are located in src/db/ under specific schema modules: app.schema.ts (projects and keywords), audit.schema.ts (audits), better-auth-schema.ts (authentication), billing.schema.ts (subscriptions), gsc.schema.ts (Search Console), ga4.schema.ts (Analytics), sam.schema.ts (ads), and telemetry.schema.ts (metrics).

Can I use both SQLite and PostgreSQL simultaneously in the same deployment?

While the codebase supports both providers through configuration files, a single deployment typically targets one provider. The drizzle.config.ts (D1) and drizzle-prod.config.ts (PostgreSQL) are mutually exclusive at runtime, though the shared schema modules allow easy switching between providers during development or migration phases.

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