# OpenSEO Database Schema: Modular Drizzle ORM Architecture for Cloudflare D1

> Discover OpenSEO's modular Drizzle ORM schema for Cloudflare D1. Learn how its SQLite based architecture organizes domain specific tables for type safe data access.

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: architecture
- Published: 2026-06-26

---

**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`](https://github.com/every-app/open-seo/blob/main/src/db/index.ts))

The primary entry point [`src/db/index.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/schema.ts) [source: /cache/repos/github.com/every-app/open-seo/main/src/db/index.ts].

### Schema Aggregation ([`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts))

Rather than a monolithic definition, [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts))

The [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/better-auth-schema.ts))

Authentication tables—including **user**, **session**, **account**, and **organization**—are defined in [`src/db/better-auth-schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/billing.schema.ts)): Subscription and payment-related tables.
- **Google Search Console** ([`src/db/gsc.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/gsc.schema.ts)): Tables managing GSC connections and data.
- **Reddit Attribution** ([`src/db/reddit-attribution.schema.ts`](https://github.com/every-app/open-seo/blob/main/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:

```typescript
// 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`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) aggregates all tables into a single schema object consumed by [`src/db/index.ts`](https://github.com/every-app/open-seo/blob/main/src/db/index.ts).
- Key files include [`app.schema.ts`](https://github.com/every-app/open-seo/blob/main/app.schema.ts) (core SEO entities), [`better-auth-schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts) (projects, keywords, audits), [`src/db/better-auth-schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/better-auth-schema.ts) (users, sessions), and separate files for billing and integrations. These are aggregated in [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/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.