Open‑SEO Database Schema: Complete Drizzle ORM Model Reference

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, src/db/audit.schema.ts, src/db/sam.schema.ts, plus auth/billing tables
• PostgreSQL schemas: Mirrored under src/db/pg/ (e.g., src/db/pg/app.schema.ts)

The runtime selection happens in src/db/schema.ts:

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 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 (and 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, with PostgreSQL variants under 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:

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) provides unified exports while 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, 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 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.

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