# Database Schema Design Principles in OpenSEO with Drizzle ORM

> Discover OpenSEO's database schema design principles in OpenSEO using Drizzle ORM. Learn about dialect-agnostic design, parallel schemas, and strict parity testing.

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

---

**OpenSEO implements a dialect-agnostic, test-validated database layer using Drizzle ORM that supports both SQLite and PostgreSQL through parallel schemas, strict parity testing, and performance-optimized indexing strategies.**

OpenSEO is an open-source SEO management platform built with a data layer that prioritizes portability and type safety. The project uses Drizzle ORM to maintain identical schemas across SQLite for local development and PostgreSQL for production environments, eliminating the need for database-specific code branches.

## Provider-Aware Single Source of Truth

OpenSEO solves the multi-dialect problem through a **provider-aware barrel file** that abstracts the underlying database implementation. The [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) file dynamically re-exports the appropriate schema at runtime based on the `getDatabaseProvider()` utility, allowing all repository files to import tables from a single location while the framework handles dialect switching transparently.

```ts
// Works identically for both SQLite and PostgreSQL
import { projects, savedKeywords, rankTrackingConfigs } from "@/db";

```

Behind this abstraction, OpenSEO maintains **parallel dialect schemas** for every feature domain. Each module contains a SQLite version (e.g., [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts)) and a matching PostgreSQL version (e.g., [`src/db/pg/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/app.schema.ts)) with identical table structures, column types, and constraints.

## Automated Schema Parity Testing

To ensure the parallel schemas remain synchronized, OpenSEO implements comprehensive regression testing in [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts). This test suite performs a structural comparison between SQLite and PostgreSQL definitions, validating:

- Table names and column compositions
- Primary key and foreign key relationships
- Unique constraints and check constraints
- Enum definitions and default values

The test suite also verifies that **better-auth** integration tables maintain required indexes after generation, preventing auth-related performance regressions when switching dialects.

## Data Integrity Patterns

### Soft Deletes with Archival Semantics

Rather than physically removing records, OpenSEO implements a **soft-delete pattern** using nullable timestamp columns. The `projects.archivedAt` column marks project archival while preserving related keyword and audit data for historical reporting.

```ts
// Inserting a new project (soft-delete aware)
await db
  .insert(projects)
  .values({
    id: crypto.randomUUID(),
    organizationId: orgId,
    name: "My New Project",
    domain: "example.com",
    // archivedAt omitted → active project
  })
  .run();

```

Queries for active records filter on `isNull(projects.archivedAt)`, ensuring deleted data never surfaces in application logic.

### Cascade Constraints for Referential Integrity

All foreign key definitions include explicit `onDelete: "cascade"` configurations. For example, the `userOnboardingAnswers.userId` relationship automatically cleans up onboarding responses when a user is removed, eliminating orphaned records without application-level cleanup logic.

### Enums and Composite Constraints

OpenSEO enforces business rules at the database level using **enum columns** and **composite unique constraints**. The `rankTrackingConfigs` table uses enums for `devices` and `scheduleInterval` to restrict values to predefined sets.

Multi-column uniqueness constraints protect against duplicate entries. The `saved_keywords` table implements a unique index across `(projectId, keyword, locationCode, languageCode)`, preventing the same keyword from being saved multiple times for identical targeting parameters.

## Performance-First Indexing

The schema design follows an **index-first query pattern**, ensuring every frequently filtered column has appropriate indexing to avoid full-table scans.

### Partial Unique Indexes for Business Logic

OpenSEO leverages partial unique indexes to enforce complex single-row constraints. The `projects_one_default_per_organization_idx` guarantees only one default project per organization, while `rank_check_runs_one_active_per_config_idx` prevents concurrent execution by indexing on `status` where values are `pending` or `running`.

```ts
// Enforcing single in-flight rank check via partial unique index
await db
  .insert(rankCheckRuns)
  .values({
    id: crypto.randomUUID(),
    configId: configId,
    projectId: projectId,
    status: "pending", // <-- will fail if another pending/run exists
  })
  .run();

```

### Foreign Key Indexing

Beyond unique constraints, the schema includes secondary indexes on `projectId`, `organizationId`, and `trackingKeywordId` columns to optimize join performance and filtering operations commonly used in SEO audit workflows.

## Developer Experience Abstractions

### Centralized Batch Writes

All atomic multi-statement writes route through a single helper in [`src/db/runBatch.ts`](https://github.com/every-app/open-seo/blob/main/src/db/runBatch.ts). This abstraction isolates driver-specific `.batch` implementations, ensuring SQLite's batching semantics don't leak into PostgreSQL-specific code paths. The test suite enforces this by asserting that no other file calls `.batch` directly.

### Query Patterns for Active Records

When querying active projects, the schema leverages the indexed `organizationId` column combined with the soft-delete filter for efficient execution:

```ts
// Index on organizationId makes this cheap
const activeProjects = await db
  .select()
  .from(projects)
  .where(eq(projects.organizationId, orgId))
  .where(isNull(projects.archivedAt))
  .all();

```

## Summary

- **OpenSEO** maintains database portability through [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts), a runtime barrel file that exports SQLite or PostgreSQL schemas based on the environment.
- **Parallel schema definitions** in [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts) and [`src/db/pg/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/app.schema.ts) ensure feature parity across dialects.
- **Automated testing** in [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) guarantees structural equivalence and validates better-auth indexes.
- **Soft-delete patterns** use `archivedAt` timestamps rather than physical deletion, preserving data relationships.
- **Partial unique indexes** enforce business rules like single default projects per organization and prevent concurrent rank check runs.
- **Batch operations** are centralized through [`src/db/runBatch.ts`](https://github.com/every-app/open-seo/blob/main/src/db/runBatch.ts) to maintain driver abstraction and prevent implementation leaks.

## Frequently Asked Questions

### How does OpenSEO maintain schema consistency across SQLite and PostgreSQL?

OpenSEO uses parallel schema files ([`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts) for SQLite and [`src/db/pg/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/pg/app.schema.ts) for PostgreSQL) combined with automated parity testing. The [`src/db/schema-parity.test.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema-parity.test.ts) test suite validates that both dialects share identical table structures, constraints, and relationships, preventing drift between development and production environments.

### What pattern does OpenSEO use for soft deletes?

OpenSEO implements soft deletes through nullable timestamp columns like `archivedAt`. Records are never physically removed; instead, the presence of a timestamp indicates archival status. This preserves related data for historical analysis while allowing queries to filter active records using `isNull(projects.archivedAt)`.

### Why does OpenSEO use partial unique indexes?

Partial unique indexes enforce complex business rules that standard unique constraints cannot handle. For example, `projects_one_default_per_organization_idx` ensures only one default project exists per organization, while `rank_check_runs_one_active_per_config_idx` prevents concurrent execution of rank checks by constraining active status values.

### How are database batch operations handled in OpenSEO?

All atomic multi-statement writes route through the centralized `runBatch` helper in [`src/db/runBatch.ts`](https://github.com/every-app/open-seo/blob/main/src/db/runBatch.ts). This abstraction isolates driver-specific implementations, ensuring SQLite batching semantics remain separate from PostgreSQL operations and preventing direct `.batch` calls throughout the codebase.