# OpenSEO Database Schema for Projects and Keywords Explained

> Explore the OpenSEO database schema for projects and keywords using Drizzle-ORM. Understand how OpenSEO structures your data for SQLite and PostgreSQL compatibility.

- Repository: [Every App/open-seo](https://github.com/every-app/open-seo)
- Tags: database-schema
- Published: 2026-08-14

---

**OpenSEO stores projects and keywords in a **Drizzle-ORM** schema compatible with both SQLite and PostgreSQL, defined in [`src/db/app.schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts) and re-exported through [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts) lines 44-80, with re-exports in [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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

```typescript
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

```typescript
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

```typescript
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`](https://github.com/every-app/open-seo/blob/main/src/db/app.schema.ts) | Declares `projects` and `saved_keywords` tables |
| [`src/db/schema.ts`](https://github.com/every-app/open-seo/blob/main/src/db/schema.ts) | Runtime schema selection and barrel exports |
| [`src/server/features/projects/repositories/ProjectRepository.ts`](https://github.com/every-app/open-seo/blob/main/src/server/features/projects/repositories/ProjectRepository.ts) | Data access patterns for projects |
| [`src/server/features/projects/services/projects.ts`](https://github.com/every-app/open-seo/blob/main/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`](https://github.com/every-app/open-seo/blob/main/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.