# Open‑SEO Database Schema: Complete Drizzle ORM Model Reference

> Explore the Open-SEO database schema featuring a dual-dialect Drizzle ORM for SQLite and PostgreSQL. Understand the model reference for projects, keywords, rank tracking, site audits, and AI sessions.

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

---

**Open‑SEO uses a dual‑dialect Drizzle ORM schema supporting both SQLite (development) and PostgreSQL (production), with 20+ tables spanning projects, keywords, rank tracking, site audits, and AI assistant sessions.**

The database layer in **every-app/open-seo** is built around a **schema barrel** pattern that lets the same TypeScript code run unchanged against two different SQL dialects. This article breaks down every table, relationship, and implementation detail found in the source code.

---

## Dual‑Dialect Architecture

Open‑SEO maintains **parallel schema definitions** rather than relying on Drizzle's dialect abstraction alone. This ensures optimal indexing and type precision for each database engine.

- **SQLite schemas**: [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts), [`src/db/audit.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/audit.schema.ts), [`src/db/sam.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/sam.schema.ts), plus auth/billing tables
- **PostgreSQL schemas**: Mirrored under `src/db/pg/` (e.g., [`src/db/pg/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/app.schema.ts))

The runtime selection happens in [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts):

```typescript
const runtimeSchema =
  getDatabaseProvider() === "postgres"
    ? { ...pgApp, ...pgAudit, ...pgSam, ...pgAuth, ...pgBilling, ...pgGsc, ...pgReddit, ...pgTelemetry }
    : { ...sqliteApp, ...sqliteAudit, ...sqliteSam, ...sqliteAuth, ...sqliteBilling, ...sqliteGsc, ...sqliteReddit, ...sqliteTelemetry };

export const schema = runtimeSchema as unknown as AppSchema;

```

A dedicated test suite in [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) enforces structural parity between dialects, catching column type mismatches or missing indexes at build time.

---

## Core Application Tables

These tables power Open‑SEO's keyword research and project management workflows.

### Projects and Keywords

| Table | Purpose | Key Design |
|-------|---------|------------|
| `projects` | Container for keyword research per organization | `organizationId` FK, `archivedAt` soft‑delete, composite unique on default flag |
| `savedKeywords` | Canonical keywords within a project | Client‑generated UUID, natural key of (`projectId`, `keyword`, `locationCode`, `languageCode`) |
| `savedKeywordTags` | User‑defined labels for keywords | `normalizedName` for case‑insensitive deduplication |
| `savedKeywordTagAssignments` | Many‑to‑many junction | Composite PK on (`savedKeywordId`, `tagId`) |

### Rank Tracking

The rank tracking subsystem uses four coordinated tables:

- **`rankTrackingConfigs`** — Domain‑wide tracking setup with `scheduleInterval`, `devices` array, and `isActive` toggle
- **`rankTrackingKeywords`** — Keywords assigned to a config with cached `searchVolume` and `keywordDifficulty`
- **`rankCheckRuns`** — Execution records with `status`, `startedAt`, `completedAt` timestamps
- **`rankSnapshots`** — Individual position results per keyword/device/run, using an auto‑incrementing `id` for efficient pagination

Critical indexes include `rank_check_runs_one_active_per_config_idx`, a **partial unique index** preventing duplicate in‑flight checks for the same config.

### Keyword Metrics Caching

The `keywordMetrics` table stores **denormalized snapshots** from external APIs (search volume, CPC, difficulty). The composite key (`projectId`, `keyword`, `locationCode`, `languageCode`) with `fetchedAt` descending lets queries retrieve the latest cached data efficiently.

---

## Activation and Onboarding Tables

Open‑SEO tracks user progress through two state machines:

| Table | Scope | Tracked Milestones |
|-------|-------|-------------------|
| `userOnboardingAnswers` | Per‑user | Organization selection, use‑case questionnaire |
| `organizationActivationState` | Per‑organization | `firstMcpAuthorizedAt`, `firstMcpToolCallAt` for MCP OAuth flows |
| `projectActivationState` | Per‑project | `competitorStepClickedAt`, `mcpCardDismissedAt` for UI checklist |

These tables use **timestamp‑only schemas** to minimize write amplification while preserving full audit history.

---

## Site Audit Tables

Defined in [`src/db/audit.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/audit.schema.ts) (and [`src/db/pg/audit.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/audit.schema.ts)), the audit subsystem spans:

- **`audits`** — Top‑level crawl configuration and status
- **`auditPages`** — Discovered URLs with fetch status, response time, content hash
- **`auditLinks`** — Internal link graph (source → target relationships)
- **`auditIssues`** — Detected SEO issues with severity, category, remediation hints
- **`auditLighthouseResults`** — Stored Lighthouse JSON reports with key metric extraction

Foreign‑key cascades ensure that deleting an audit removes all dependent pages, links, and issues.

---

## AI Assistant Tables

The "Sam" assistant maintains conversational context through:

- **`samSessions`** — Session metadata with `projectId` scoping and expiration
- **`samProjectMemory`** — Key‑value memory per project for long‑term context retention

Both tables are defined in [`src/db/sam.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/sam.schema.ts), with PostgreSQL variants under [`src/db/pg/sam.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/sam.schema.ts).

---

## Authentication and Billing Tables

Open‑SEO delegates identity to **better-auth**, whose schema includes:

- `user`, `session`, `account`, `verification` — Core authentication
- `organization`, `member`, `invitation` — Multi‑tenant organization support

Billing integration adds:

- `billingCustomerStatus` — Subscription state and payment method status

Additional integration tables include:

- `gscConnections` — OAuth tokens for Google Search Console
- `redditAttributions` — UTM and referral tracking for Reddit campaigns
- `telemetryState` — Opt‑in analytics and error reporting flags

---

## Querying the Schema

All tables export through the unified `schema` object. Example operations from the codebase:

```typescript
import { db } from "@/db";
import { projects, savedKeywords } from "@/db/schema";

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

// Insert a keyword with client‑generated ID
await db.insert(savedKeywords).values({
  id: "kw_456",
  projectId: "proj_789",
  keyword: "open source seo tools",
  locationCode: 2840,
  languageCode: "en",
});

// Fetch latest cached metrics
const metrics = await db
  .select()
  .from(keywordMetrics)
  .where(and(
    eq(keywordMetrics.projectId, "proj_789"),
    eq(keywordMetrics.keyword, "open source seo tools")
  ))
  .orderBy(desc(keywordMetrics.fetchedAt))
  .limit(1);

```

These queries execute identically against SQLite or PostgreSQL through Drizzle's type‑safe abstraction.

---

## Indexing Strategy

The Open‑SEO schema applies several **performance patterns**:

- **Foreign‑key indexes** on all `projectId`, `configId`, and `organizationId` columns
- **Composite unique indexes** for business constraints (e.g., one default project per organization)
- **Partial indexes** for status‑filtered queries (active runs only)
- **Descending indexes** on timestamp columns for "latest first" access patterns

---

## Summary

- **Dual‑dialect design** lets Open‑SEO run on SQLite locally and PostgreSQL in production without code changes
- **Schema barrel** ([`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts)) provides unified exports while [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) guarantees parity
- **20+ tables** span SEO workflows: projects, keywords, rank tracking, site audits, AI sessions, auth, and billing
- **Robust indexing** with composite, partial, and descending indexes supports high‑volume keyword operations
- **Soft deletes** via `archivedAt` timestamps preserve historical data for reporting

---

## Frequently Asked Questions

### What ORM does Open‑SEO use for database operations?

Open‑SEO uses **Drizzle ORM** with explicit dual schema definitions for SQLite and PostgreSQL rather than relying on automatic dialect translation. This approach, implemented in [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts), ensures type safety and optimal query plans for each database engine.

### How does Open‑SEO handle database migrations between SQLite and PostgreSQL?

The codebase maintains **structural parity through testing**, not migration scripts. The [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) file validates that SQLite and PostgreSQL schemas define identical tables, columns, and indexes. Application code references tables through the unified `schema` export, making the underlying dialect transparent at runtime.

### Why does the Open‑SEO database schema use client‑generated UUIDs?

Tables like `savedKeywords` use client‑generated identifiers to enable **optimistic UI updates** and idempotent inserts. This avoids round‑trips for ID assignment and simplifies offline‑first patterns in the frontend. The `cuid2` or `ulid` formats provide lexicographically sortable, collision‑resistant identifiers without database coordination.

### What is the purpose of the `organizationActivationState` and `projectActivationState` tables?

These tables implement **progressive onboarding state machines** at different scopes. `organizationActivationState` tracks MCP (Model Context Protocol) OAuth completion and first tool usage across an entire organization, while `projectActivationState` records per‑project UI interactions like competitor analysis engagement. This separation allows granular feature gating and targeted user guidance.