OpenSEO Database Schema for Projects and Keywords Explained

OpenSEO stores projects and keywords in a Drizzle-ORM schema compatible with both SQLite and PostgreSQL, defined in src/db/app.schema.ts and re-exported through src/db/schema.ts.

The OpenSEO codebase organizes SEO tracking data around two core entities: projects that group related work, and keywords that represent individual search terms to monitor. This guide examines the exact database schema, constraints, and usage patterns implemented in the open-source repository.

Projects Table Schema

The projects table serves as the top-level container for SEO campaigns in OpenSEO.

Column Definitions

Column	Type	Constraints	Purpose
id	text	Primary key	Globally unique project identifier
organizationId	text	NOT NULL, FK → organization.id, ON DELETE CASCADE	Owning organization
name	text	NOT NULL	Human-readable project name
domain	text	—	Optional tracked domain
locationCode	integer	NOT NULL, default 2840	Default DataForSEO location code
languageCode	text	NOT NULL, default 'en'	Default language
createdAt	text	NOT NULL, default CURRENT_TIMESTAMP	Creation timestamp
archivedAt	text	—	Soft-delete timestamp for hiding projects

Indexes and Constraints

• Partial-unique index projects_one_default_per_organization_idx enforces exactly one "Default" project (where name = 'Default' AND domain IS NULL) per organization
• Index projects_organization_id_idx accelerates organization-scoped queries

The source definition appears in src/db/app.schema.ts lines 44-80, with re-exports in src/db/schema.ts lines 64-67.

Keywords Table Schema

Keywords are stored in the saved_keywords table and belong to exactly one project.

Column Definitions

Column	Type	Constraints	Purpose
id	text	Primary key	Unique keyword entry identifier
projectId	text	NOT NULL, FK → projects.id, ON DELETE CASCADE	Parent project reference
keyword	text	NOT NULL	The search term itself
locationCode	integer	NOT NULL, default 2840	Market-specific location
languageCode	text	NOT NULL, default 'en'	Language for the keyword
createdAt	text	NOT NULL, default CURRENT_TIMESTAMP	When saved

Indexes and Constraints

• Partial-unique index saved_keywords_unique_project_keyword_location_language prevents duplicate keyword + location + language combinations within the same project
• Composite index saved_keywords_project_created_idx optimizes pagination and recent-keyword listings

Find this definition in src/db/app.schema.ts lines 84-99.

Runtime Schema Selection

OpenSEO automatically selects the correct schema implementation based on the active database provider. As implemented in src/db/schema.ts lines 38-50, the runtime detects whether SQLite or PostgreSQL is configured and exports the appropriate table definitions. The canonical AppSchema is what the rest of the application imports.

Practical Drizzle-ORM Examples

These snippets work identically across SQLite and PostgreSQL backends.

Creating a Project

import { db } from "@/db";
import { projects } from "@/db/schema";

await db
  .insert(projects)
  .values({
    id: crypto.randomUUID(),
    organizationId: orgId,
    name: "My New SEO Project",
    domain: "example.com",
    // locationCode and languageCode default to 2840 and 'en'
  })
  .run();

Adding a Keyword

import { db } from "@/db";
import { savedKeywords } from "@/db/schema";

await db
  .insert(savedKeywords)
  .values({
    id: crypto.randomUUID(),
    projectId: projectId,
    keyword: "best coffee beans",
    // optional: override locationCode or languageCode
  })
  .run();

Querying Active Keywords

import { db } from "@/db";
import { savedKeywords, projects } from "@/db/schema";
import { desc, and, eq, isNull } from "drizzle-orm";

const keywords = await db
  .select()
  .from(savedKeywords)
  .where(
    and(
      eq(savedKeywords.projectId, projectId),
      isNull(projects.archivedAt), // exclude archived projects
    ),
  )
  .orderBy(desc(savedKeywords.createdAt))
  .all();

Key Files Reference

File	Purpose
src/db/app.schema.ts	Declares projects and saved_keywords tables
src/db/schema.ts	Runtime schema selection and barrel exports
src/server/features/projects/repositories/ProjectRepository.ts	Data access patterns for projects
src/server/features/projects/services/projects.ts	Business logic for project lifecycle operations

Summary

• OpenSEO uses Drizzle-ORM with a unified schema supporting SQLite and PostgreSQL
• Projects enforce one default per organization via partial-unique index
• Keywords prevent duplicates through composite unique constraints on keyword + location + language
• Soft deletes are implemented via archivedAt timestamp rather than hard deletion
• Foreign key cascades ensure cleanup when organizations or projects are removed

Frequently Asked Questions

What database engines does OpenSEO support?

OpenSEO supports SQLite as the default and PostgreSQL as an alternative. The runtime automatically selects the correct Drizzle-ORM schema implementation based on your configuration in src/db/schema.ts.

How does OpenSEO prevent duplicate keywords?

The saved_keywords_unique_project_keyword_location_language partial-unique index enforces uniqueness across the combination of projectId, keyword, locationCode, and languageCode. This allows the same keyword text in different markets or languages within one project.

What happens when a project is archived?

The archivedAt column receives a timestamp, hiding the project from active listings without deleting its data or associated keywords. Queries typically filter with isNull(projects.archivedAt) to exclude archived projects, as shown in the repository's query patterns.

Can I change the default location or language codes?

Yes. While locationCode defaults to 2840 (United States) and languageCode defaults to 'en', both columns accept explicit values at insert time. These defaults align with DataForSEO's API conventions for market-specific ranking data.

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