OpenSEO Database Schema: Modular Drizzle ORM Architecture for Cloudflare D1

TLDR: OpenSEO uses a modular SQLite-based Drizzle ORM schema backed by Cloudflare D1, with domain-specific table definitions organized under src/db/ and aggregated into a single type-safe schema object.

OpenSEO (every-app/open-seo) persists all application data in a Cloudflare D1 SQLite database accessed through Drizzle ORM. The OpenSEO database schema follows a domain-driven modular design, utilizing Drizzle's sqlite-core helpers to define tables while splitting concerns across focused files for maintainability.

Schema Entry Points and Configuration

Database Instance (src/db/index.ts)

The primary entry point src/db/index.ts initializes the Drizzle client using the D1 driver (drizzle-orm/d1). It binds the database instance to the env.DB Cloudflare Workers binding and passes the combined schema object imported from schema.ts [source: /cache/repos/github.com/every-app/open-seo/main/src/db/index.ts].

Schema Aggregation (src/db/schema.ts)

Rather than a monolithic definition, src/db/schema.ts serves as a barrel file that re-exports all table definitions from domain-specific modules. This aggregation allows the Drizzle instance to receive a complete schema while keeping table definitions organized by function [source: /cache/repos/github.com/every-app/open-seo/main/src/db/schema.ts].

Domain-Specific Table Definitions

All table definitions use Drizzle's sqlite-core utilities—including sqliteTable, text, integer, real, uniqueIndex, and index—to declare columns, primary keys, foreign keys, and indexes compatible with SQLite.

Core Application Tables (src/db/app.schema.ts)

The src/db/app.schema.ts file defines the primary business entities: projects, saved_keywords, rank_tracking, audit, and numerous helper tables that power OpenSEO's keyword research and rank tracking features [source: /cache/repos/github.com/every-app/open-seo/main/src/db/app.schema.ts].

Authentication Schema (src/db/better-auth-schema.ts)

Authentication tables—including user, session, account, and organization—are defined in src/db/better-auth-schema.ts to integrate with the better-auth library [source: /cache/repos/github.com/every-app/open-seo/main/src/db/better-auth-schema.ts].

Billing and Integration Tables

• Billing (src/db/billing.schema.ts): Subscription and payment-related tables.
• Google Search Console (src/db/gsc.schema.ts): Tables managing GSC connections and data.
• Reddit Attribution (src/db/reddit-attribution.schema.ts): Tables for Reddit campaign tracking.

Querying the Database

With the schema fully typed, you can import the configured db instance and table definitions to perform type-safe operations using Drizzle's query builder:

// src/db/index.ts already exports the configured db instance
import { db } from "@/src/db/index";
import { projects, savedKeywords } from "@/src/db/app.schema";
import { eq } from "drizzle-orm";

// 1. Get a project by its ID
const project = await db
  .select()
  .from(projects)
  .where(eq(projects.id, "proj_123"))
  .then((rows) => rows[0]);

// 2. Insert a new saved keyword
await db.insert(savedKeywords).values({
  id: crypto.randomUUID(),
  projectId: project.id,
  keyword: "open seo",
  locationCode: 2840,
  languageCode: "en",
});

// 3. Query saved keywords for a project
const keywords = await db
  .select()
  .from(savedKeywords)
  .where(eq(savedKeywords.projectId, project.id));

Summary

• OpenSEO stores persistent data in Cloudflare D1 SQLite, accessed via Drizzle ORM's D1 driver.
• The OpenSEO database schema is modular, with table definitions split across domain files under src/db/.
• src/db/schema.ts aggregates all tables into a single schema object consumed by src/db/index.ts.
• Key files include app.schema.ts (core SEO entities), better-auth-schema.ts (authentication), and dedicated files for billing, GSC, and Reddit data.
• The architecture supports automatic migrations via drizzle-kit and provides fully type-safe database access for Cloudflare Workers.

Frequently Asked Questions

What type of database does OpenSEO use?

OpenSEO uses Cloudflare D1, a serverless SQLite database designed for Cloudflare Workers. The Drizzle schema is built specifically for SQLite using Drizzle ORM's sqlite-core helpers and the drizzle-orm/d1 driver.

Where are the table definitions located in OpenSEO?

Table definitions reside in src/db/, split across domain-specific files like src/db/app.schema.ts (projects, keywords, audits), src/db/better-auth-schema.ts (users, sessions), and separate files for billing and integrations. These are aggregated in src/db/schema.ts.

How does OpenSEO handle database migrations?

OpenSEO uses drizzle-kit for schema migrations. Since the schema targets SQLite/D1, you can generate and apply migrations using Drizzle's CLI tools, which read the schema definition and produce SQL compatible with Cloudflare D1.

Is the OpenSEO schema compatible with other ORMs?

No, the schema is specifically designed for Drizzle ORM using its sqlite-core primitives. The table definitions utilize Drizzle-specific functions like sqliteTable(), text(), and integer(), making them incompatible with other ORMs without conversion.

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