Database Schema Design Principles in OpenSEO with Drizzle ORM

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 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.

// 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) and a matching PostgreSQL version (e.g., 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. 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.

// 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.

// 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. 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:

// 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, a runtime barrel file that exports SQLite or PostgreSQL schemas based on the environment.
• Parallel schema definitions in src/db/app.schema.ts and src/db/pg/app.schema.ts ensure feature parity across dialects.
• Automated testing in 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 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 for SQLite and src/db/pg/app.schema.ts for PostgreSQL) combined with automated parity testing. The 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. This abstraction isolates driver-specific implementations, ensuring SQLite batching semantics remain separate from PostgreSQL operations and preventing direct .batch calls throughout the codebase.

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