Database schema must run on both SQLite (D1) and Postgres; the dual-backend constraint is enforced at the type level in src/db/schema.ts and verified by schema-parity.test.ts, with getDatabaseProvider() switching backends at runtime. OpenSEO's core tables (projects, rankTrackingConfigs, rankCheckRuns, savedKeywords, and their related lookup tables) use partial unique indexes, enums, and soft-delete timestamps to prevent race conditions, enforce business rules, and preserve audit trails across both database engines.
Database schema changes and queries must remain compatible with both SQLite (D1) and Postgres — the dual-backend constraint is a hard invariant.[1] D1 (SQLite) is the default database backend; Postgres is an opt-in backend for installs that outgrow D1, documented in docs/LOCAL_POSTGRES.md.[2] The D1 binding is named DB, the database is named open-seo, and migrations are stored in the drizzle/ directory, as declared in wrangler.jsonc.[3] Hyperdrive is the ONLY way the app connects to Postgres from a Worker — there is no direct-connection fallback. The Hyperdrive binding is kept commented out in wrangler.jsonc and is used only for local Postgres development, never read by Alchemy or Docker deployments.[3]
src/db/schema.ts is the canonical schema barrel: repositories import their tables from here and receive the SQLite type definitions, while at runtime the values are either the SQLite or Postgres schema depending on the active getDatabaseProvider() result — so each repository is written once for both backends.[4] In src/db/schema.ts the exported type identity is typeof sqliteApp & … (the SQLite definitions), while the runtime values are cast to that type — a cast guarded by schema-parity.test.ts, which asserts that the two dialect schemas are structurally interchangeable (same tables, columns, nullability, PKs, and unique indexes).[4] src/db/pg/schema.ts is a pure re-export barrel for all Postgres dialect schema modules: app, project-context, audit, sam, better-auth, billing, ga4, gsc, and telemetry.[5]
Projects support soft-delete via an archivedAt timestamp: archived projects are hidden everywhere, but their data — keywords, rank tracking, and audits — is preserved.[6] A partial unique index on the projects table prevents more than one un-archived Default project (with null domain) per organization, guarding against a get-or-create race when multiple requests enter a new organization simultaneously.[6] The projects table defaults DataForSEO locationCode to 2840 (United States) and languageCode to 'en'; these are set during onboarding and reused by every project-scoped data call.[6]
The rankTrackingConfigs table enforces separate uniqueness for national configs (no locationName) and local configs (with locationName) via two conditional partial unique indexes, allowing the same domain/location combination to coexist only if one is national and one is local.[6] The rankTrackingConfigs table's scheduleInterval column is an enum of 'daily', 'weekly', 'monthly', 'manual', defaulting to 'weekly'.[6] The rankTrackingConfigs table's devices column is an enum of 'both', 'desktop', 'mobile', defaulting to 'both'.[6] The rank_check_runs table enforces at most one in-flight run per config at the DB level via a partial unique index on config_id WHERE status IN ('pending', 'running') — a second pending run for the same config fails with a unique-constraint violation.[6] The rankCheckRuns table's status column is an enum of 'pending', 'running', 'completed', 'failed', defaulting to 'pending'.[6]
The savedKeywords table enforces uniqueness on (projectId, keyword, locationCode, languageCode), preventing duplicate keyword entries for the same project, location, and language combination.[6] Tag colors in savedKeywordTags are optional: a null color column means the render layer should derive a stable color from the tag ID at render time, as implemented in src/shared/tag-colors.ts.[6] The savedKeywordTagAssignments table intentionally omits a standalone index on savedKeywordId because the unique index on (savedKeywordId, tagId) already serves those lookups as its leftmost column.[6] The keywordMetrics table stores the latest cached DataForSEO metrics per (projectId, keyword, locationCode, languageCode) and is joined onto savedKeywords when rendering the saved keyword list.[6]
The userOnboardingAnswers table has a gscNudgeDismissedAt column that tracks when the user resolves the Search Console ask — either during onboarding or via a one-time re-engagement nudge for legacy users; null means not yet shown or resolved.[6]
Sources