# Database Migrations and Drizzle Schema Structure in OpenSEO

> Explore OpenSEO's database migrations and Drizzle schema structure. Supports Cloudflare D1 and PostgreSQL with 20+ versioned SQL migrations for robust data management.

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

---

**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`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts)** – Targets Cloudflare D1 (SQLite) using the `d1-http` driver
- **[`drizzle-prod.config.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/gsc.schema.ts) and [`src/db/ga4.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/ga4.schema.ts)): Houses `gsc_connections` and `ga4_connections` tables for third-party API integrations.

- **Backlinks** ([`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts)): Contains `backlink_snapshots` for external link monitoring.

- **Telemetry** ([`src/db/telemetry.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/telemetry.schema.ts)): Stores internal metrics in the `telemetry` table.

- **SAM (Search-Ads-Metrics)** ([`src/db/sam.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/sam.schema.ts)): Dedicated module for search advertising data tables.

- **Billing** ([`src/db/billing.schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/0001_striped_bulldozer.sql)** – Adds foreign-key constraints and performance indexes to core tables
- **[`0002_clean_moira_mactaggert.sql`](https://github.com/every-app/open-seo/blob/main/0002_clean_moira_mactaggert.sql)** – Introduces `rank_tracking_configs` with unique indexes
- **[`0003_sturdy_may_parker.sql`](https://github.com/every-app/open-seo/blob/main/0003_sturdy_may_parker.sql)** – Creates `rank_tracking_keywords` with uniqueness rules
- **[`0004_dashing_betty_ross.sql`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/0005_talented_wild_pack.sql)** – Creates `rank_snapshots` with composite indexes for fast lookups
- **[`0006_location_name.sql`](https://github.com/every-app/open-seo/blob/main/0006_location_name.sql)** – Adds the `location_name` column to `rank_tracking_configs`
- **[`0007_same_marvel_zombies.sql`](https://github.com/every-app/open-seo/blob/main/0007_same_marvel_zombies.sql)** – Introduces keyword tagging: `saved_keywords`, `saved_keyword_tags`, and `saved_keyword_tag_assignments`
- **[`0008_yummy_annihilus.sql`](https://github.com/every-app/open-seo/blob/main/0008_yummy_annihilus.sql)** – Adds indexes and constraints to `keyword_metrics`
- **[`0009_supreme_captain_stacy.sql`](https://github.com/every-app/open-seo/blob/main/0009_supreme_captain_stacy.sql)** – Creates `user_onboarding_answers` table
- **[`0010_overrated_amazoness.sql`](https://github.com/every-app/open-seo/blob/main/0010_overrated_amazoness.sql)** – Adds `account`, `session`, and related authentication indexes
- **[`0011_friendly_morlun.sql`](https://github.com/every-app/open-seo/blob/main/0011_friendly_morlun.sql)** – Introduces `invitation` and `member` tables for organization management
- **[`0012_dashboard.sql`](https://github.com/every-app/open-seo/blob/main/0012_dashboard.sql)** – Creates `organization_activation_state` and `project_activation_state` tracking tables
- **[`0013_sleepy_black_tarantula.sql`](https://github.com/every-app/open-seo/blob/main/0013_sleepy_black_tarantula.sql)** – Adds `backlink_snapshots`
- **[`0014_solid_centennial.sql`](https://github.com/every-app/open-seo/blob/main/0014_solid_centennial.sql)** – Creates `verification` table
- **[`0015_sticky_dagger.sql`](https://github.com/every-app/open-seo/blob/main/0015_sticky_dagger.sql)** – Adds `gsc_connections` for Google Search Console integration
- **[`0016_panoramic_blob.sql`](https://github.com/every-app/open-seo/blob/main/0016_panoramic_blob.sql)** and **[`0017_ga4_connections.sql`](https://github.com/every-app/open-seo/blob/main/0017_ga4_connections.sql)** – Create `ga4_connections` for Google Analytics 4
- **[`0018_drop_reddit_attributions.sql`](https://github.com/every-app/open-seo/blob/main/0018_drop_reddit_attributions.sql)** – Removes the deprecated `reddit_attributions` table
- **[`0019_clammy_selene.sql`](https://github.com/every-app/open-seo/blob/main/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:

```bash

# 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`](https://github.com/every-app/open-seo/blob/main/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:

```bash
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`](https://github.com/every-app/open-seo/blob/main/src/db/d1/schema.ts) and [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts) (development/D1) and [`drizzle-prod.config.ts`](https://github.com/every-app/open-seo/blob/main/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:

```typescript
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`](https://github.com/every-app/open-seo/blob/main/src/db/d1/schema.ts) and [`src/db/pg/schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/app.schema.ts) (projects and keywords), [`audit.schema.ts`](https://github.com/every-app/open-seo/blob/main/audit.schema.ts) (audits), [`better-auth-schema.ts`](https://github.com/every-app/open-seo/blob/main/better-auth-schema.ts) (authentication), [`billing.schema.ts`](https://github.com/every-app/open-seo/blob/main/billing.schema.ts) (subscriptions), [`gsc.schema.ts`](https://github.com/every-app/open-seo/blob/main/gsc.schema.ts) (Search Console), [`ga4.schema.ts`](https://github.com/every-app/open-seo/blob/main/ga4.schema.ts) (Analytics), [`sam.schema.ts`](https://github.com/every-app/open-seo/blob/main/sam.schema.ts) (ads), and [`telemetry.schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/drizzle.config.ts) (D1) and [`drizzle-prod.config.ts`](https://github.com/every-app/open-seo/blob/main/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.