Open source alternative to Semrush and Ahrefs
TypeScript backlink-analysisgoogle-search-console-mcpkeyword-researchmcpseoseo-agentseo-auditseo-automationseo-skillsseo-toolssite-audit
import { sql } from "drizzle-orm";
import {
bigint,
boolean,
index,
integer,
pgTable,
real,
serial,
text,
uniqueIndex,
primaryKey,
} from "drizzle-orm/pg-core";
import { organization, user } from "./better-auth-schema";
// Timestamps are stored as *text* (same column shape as the SQLite schema).
// Postgres `timestamptz` would be parsed back into a JS Date by postgres-js
// (even with drizzle `mode:"string"`), silently breaking the lexicographic
// string comparisons the app does on timestamps. `isoNow` matches the format of
// `new Date().toISOString()`, so within a Postgres deployment DB-defaulted and
// app-written values sort together.
//
// NOTE: this is NOT byte-for-byte equal to the SQLite default, which uses
// `current_timestamp` (`YYYY-MM-DD HH:MM:SS`, space-separated, no millis/Z).
// Each backend is internally consistent, but a one-time D1→Postgres data
// migration MUST rewrite legacy timestamp text into this ISO format (tracked as
// the deferred timestamp backfill).
const isoNow = sql`to_char(now() AT TIME ZONE 'utc', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')`;
const timestampColumn = (name: string) => text(name);
export const userOnboardingAnswers = pgTable(
"user_onboarding_answers",
{
userId: text("user_id")
.primaryKey()
.references(() => user.id, { onDelete: "cascade" }),
organizationId: text("organization_id")
.notNull()
.references(() => organization.id, { onDelete: "cascade" }),
interestedFeatures: text("interested_features").notNull().default("[]"),
workFor: text("work_for"),
clientWebsiteCount: text("client_website_count"),
foundVia: text("found_via"),
mcpSetupIntent: text("mcp_setup_intent"),
completedAt: timestampColumn("completed_at"),
// Set when the user dismisses (or acts on) the one-time "connect Search
// Console" nudge shown to people who finished onboarding before the GSC
// step existed. Null = never shown/dismissed.
gscNudgeDismissedAt: timestampColumn("gsc_nudge_dismissed_at"),
createdAt: timestampColumn("created_at").notNull().default(isoNow),
updatedAt: timestampColumn("updated_at").notNull().default(isoNow),
},
(table) => [
index("user_onboarding_answers_organization_idx").on(table.organizationId),
],
);
// The paid-social ad (utm_medium=paid_social) a hosted signup came from, read
// from the UTM tags the marketing site carries onto the sign-up link.
// Campaign-level tags only: no click ids or other per-person ad identifiers.
// No row = not from a paid-social ad.
export const userSignupCampaigns = pgTable("user_signup_campaigns", {
userId: text("user_id")
.primaryKey()
.references(() => user.id, { onDelete: "cascade" }),
utmSource: text("utm_source"),
utmCampaign: text("utm_campaign"),
utmContent: text("utm_content"),
});
// Projects for keyword research
export const projects = pgTable(
"projects",
{
id: text("id").primaryKey(),
organizationId: text("organization_id")
.notNull()
.references(() => organization.id, { onDelete: "cascade" }),
name: text("name").notNull(),
domain: text("domain"),
// Default DataForSEO location/language for the project, set during
// onboarding and reused by every project-scoped data call.
locationCode: integer("location_code").notNull().default(2840),
languageCode: text("language_code").notNull().default("en"),
createdAt: timestampColumn("created_at").notNull().default(isoNow),
// Soft delete: archived projects are hidden everywhere but their data
// (keywords, rank tracking, audits) is preserved.
archivedAt: timestampColumn("archived_at"),
// The Prompt Research keywords from AI visibility setup, one
// per line, most important first. Null until setup runs.
aiResearchKeywords: text("ai_research_keywords"),
// The IndexNow key the site hosts at /{key}.txt. Public by design, so it
// is stored as plain text. Null until IndexNow is first used.
indexnowKey: text("indexnow_key"),
},
(table) => [
// Only the auto-created Default/null-domain project is a singleton. This
// guards the get-or-create race when several requests enter a new
// organization at once (mirrors the SQLite schema). `tryCreateDefaultProject`
// relies on this partial unique index for its onConflictDoNothing().
uniqueIndex("projects_one_default_per_organization_idx")
.on(table.organizationId)
.where(
sql`${table.name} = 'Default' AND ${table.domain} IS NULL AND ${table.archivedAt} IS NULL`,
),
// Every project listing filters by organization; the partial-unique index
// above only covers the Default-project row, so without this the org-scoped
// list queries seq-scan. Per-org row counts are small, so the archived/
// created_at ordering sorts cheaply on top of this single-column lookup.
index("projects_organization_id_idx").on(table.organizationId),
],
);
// User-saved keywords within a project. This is the canonical saved list.
export const savedKeywords = pgTable(
"saved_keywords",
{
id: text("id").primaryKey(),
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
keyword: text("keyword").notNull(),
locationCode: integer("location_code").notNull().default(2840),
languageCode: text("language_code").notNull().default("en"),
createdAt: timestampColumn("created_at").notNull().default(isoNow),
},
(table) => [
uniqueIndex("saved_keywords_unique_project_keyword_location_language").on(
table.projectId,
table.keyword,
table.locationCode,
table.languageCode,
),
index("saved_keywords_project_created_idx").on(
table.projectId,
table.createdAt,
),
],
);
export const savedKeywordTags = pgTable(
"saved_keyword_tags",
{
id: text("id").primaryKey(),
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
name: text("name").notNull(),
normalizedName: text("normalized_name").notNull(),
// Palette key (e.g. "blue", "rose"). Null = derive a stable color from the
// tag id at render time. See src/shared/tag-colors.ts.
color: text("color"),
createdAt: timestampColumn("created_at").notNull().default(isoNow),
},
(table) => [
uniqueIndex("saved_keyword_tags_project_normalized_name_idx").on(
table.projectId,
table.normalizedName,
),
index("saved_keyword_tags_project_name_idx").on(
table.projectId,
table.name,
),
],
);
export const savedKeywordTagAssignments = pgTable(
"saved_keyword_tag_assignments",
{
savedKeywordId: text("saved_keyword_id")
.notNull()
.references(() => savedKeywords.id, { onDelete: "cascade" }),
tagId: text("tag_id")
.notNull()
.references(() => savedKeywordTags.id, { onDelete: "cascade" }),
createdAt: timestampColumn("created_at").notNull().default(isoNow),
},
(table) => [
uniqueIndex("saved_keyword_tag_assignments_unique_idx").on(
table.savedKeywordId,
table.tagId,
),
// No standalone index on savedKeywordId — the unique index above has it as
// its leftmost column, so it already serves savedKeywordId lookups.
index("saved_keyword_tag_assignments_tag_idx").on(table.tagId),
],
);
// Latest cached metrics for a keyword within a project.
// This is joined onto savedKeywords when rendering the saved keyword list.
export const keywordMetrics = pgTable(
"keyword_metrics",
{
id: serial("id").primaryKey(),
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
keyword: text("keyword").notNull(),
locationCode: integer("location_code").notNull(),
languageCode: text("language_code").notNull().default("en"),
searchVolume: integer("search_volume"),
cpc: real("cpc"),
competition: real("competition"),
keywordDifficulty: integer("keyword_difficulty"),
intent: text("intent"),
monthlySearches: text("monthly_searches"),
fetchedAt: timestampColumn("fetched_at").notNull().default(isoNow),
},
(table) => [
uniqueIndex("keyword_metrics_unique_project_keyword_location_language").on(
table.projectId,
table.keyword,
table.locationCode,
table.languageCode,
),
index("keyword_metrics_lookup_idx").on(
table.projectId,
table.keyword,
table.locationCode,
table.languageCode,
table.fetchedAt,
),
],
);
// ============================================================================
// Rank Tracking tables
// ============================================================================
// One configuration per project+domain+location+language — defines what domain
// to track and how
export const rankTrackingConfigs = pgTable(
"rank_tracking_configs",
{
id: text("id").primaryKey(),
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
domain: text("domain").notNull(),
locationCode: integer("location_code").notNull().default(2840),
languageCode: text("language_code").notNull().default("en"),
devices: text("devices", {
enum: ["both", "desktop", "mobile"],
})
.notNull()
.default("both"),
serpDepth: integer("serp_depth").notNull(),
scheduleInterval: text("schedule_interval", {
enum: ["daily", "weekly", "monthly", "manual"],
})
.notNull()
.default("weekly"),
locationName: text("location_name"),
isActive: boolean("is_active").notNull().default(true),
lastCheckedAt: timestampColumn("last_checked_at"),
nextCheckAt: timestampColumn("next_check_at"),
lastSkipReason: text("last_skip_reason"),
createdAt: timestampColumn("created_at").notNull().default(isoNow),
},
(table) => [
index("rank_tracking_configs_project_active_created_idx").on(
table.projectId,
table.isActive,
table.createdAt,
),
uniqueIndex("rank_tracking_configs_national_idx")
.on(table.projectId, table.domain, table.locationCode, table.languageCode)
.where(sql`${table.locationName} IS NULL`),
uniqueIndex("rank_tracking_configs_local_idx")
.on(
table.projectId,
table.domain,
table.locationCode,
table.languageCode,
table.locationName,
)
.where(sql`${table.locationName} IS NOT NULL`),
],
);
// Keywords tracked per domain config
export const rankTrackingKeywords = pgTable(
"rank_tracking_keywords",
{
id: text("id").primaryKey(),
configId: text("config_id")
.notNull()
.references(() => rankTrackingConfigs.id, { onDelete: "cascade" }),
keyword: text("keyword").notNull(),
// Keywords are lowercased on add unless this is set, in which case the
// keyword is stored and searched exactly as typed. Google can return a
// different SERP for "Nodex" than for "nodex".
matchCase: boolean("match_case").notNull().default(false),
searchVolume: integer("search_volume"),
keywordDifficulty: integer("keyword_difficulty"),
cpc: real("cpc"),
metricsFetchedAt: timestampColumn("metrics_fetched_at"),
// Set when a user pins the keyword to the top of the tracker's table.
// Pins are shared by everyone in the project.
pinnedAt: timestampColumn("pinned_at"),
createdAt: timestampColumn("created_at").notNull().default(isoNow),
},
(table) => [
uniqueIndex("rank_tracking_keywords_config_keyword_idx").on(
table.configId,
table.keyword,
),
],
);
// One row per check execution (manual or scheduled).
// A partial unique index on `config_id WHERE status IN ('pending','running')`
// enforces at most one in-flight run per config at the DB level, which is how
// duplicate-trigger protection is implemented — INSERT of a second pending run
// for the same config fails with a unique-constraint violation.
export const rankCheckRuns = pgTable(
"rank_check_runs",
{
id: text("id").primaryKey(),
configId: text("config_id")
.notNull()
.references(() => rankTrackingConfigs.id, { onDelete: "cascade" }),
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
status: text("status", {
enum: ["pending", "running", "completed", "failed"],
})
.notNull()
.default("pending"),
keywordsTotal: integer("keywords_total").notNull().default(0),
keywordsChecked: integer("keywords_checked").notNull().default(0),
isSubsetRun: boolean("is_subset_run").notNull().default(false),
errorMessage: text("error_message"),
startedAt: timestampColumn("started_at").notNull().default(isoNow),
completedAt: timestampColumn("completed_at"),
},
(table) => [
index("rank_check_runs_config_idx").on(table.configId, table.startedAt),
index("rank_check_runs_project_idx").on(table.projectId, table.startedAt),
uniqueIndex("rank_check_runs_one_active_per_config_idx")
.on(table.configId)
.where(sql`${table.status} IN ('pending', 'running')`),
],
);
// One row per keyword per device per check run
export const rankSnapshots = pgTable(
"rank_snapshots",
{
id: serial("id").primaryKey(),
runId: text("run_id")
.notNull()
.references(() => rankCheckRuns.id, { onDelete: "cascade" }),
// No FK to rankTrackingKeywords — intentional. Historical snapshots are
// preserved after a keyword is removed from tracking so users can still
// see past position data for deleted keywords.
trackingKeywordId: text("tracking_keyword_id").notNull(),
keyword: text("keyword").notNull(),
device: text("device", { enum: ["desktop", "mobile"] }).notNull(),
position: integer("position"), // null = not found in top 20
url: text("url"),
serpFeatures: text("serp_features"), // JSON array of feature type strings
checkedAt: timestampColumn("checked_at").notNull().default(isoNow),
},
(table) => [
// No standalone index on runId — the unique index below has it as its
// leftmost column, so it already serves runId lookups.
index("rank_snapshots_keyword_device_idx").on(
table.trackingKeywordId,
table.device,
table.checkedAt,
),
uniqueIndex("rank_snapshots_run_keyword_device_idx").on(
table.runId,
table.trackingKeywordId,
table.device,
),
],
);
// Dashboard activation milestones. Organization-scoped: MCP OAuth grants are
// user-level, so any member connecting an external MCP client satisfies the
// milestone for the whole organization. Timestamps are first-occurrence only
// and never move once set.
export const organizationActivationState = pgTable(
"organization_activation_state",
{
organizationId: text("organization_id")
.primaryKey()
.references(() => organization.id, { onDelete: "cascade" }),
firstMcpAuthorizedAt: timestampColumn("first_mcp_authorized_at"),
firstMcpToolCallAt: timestampColumn("first_mcp_tool_call_at"),
updatedAt: timestampColumn("updated_at").notNull().default(isoNow),
},
);
// Per-project state for the dashboard's onboarding checklist. Most steps
// complete via real product state (projects.domain, gsc_connections, MCP
// activation); the competitor and keyword steps complete on click-through.
export const projectActivationState = pgTable("project_activation_state", {
projectId: text("project_id")
.primaryKey()
.references(() => projects.id, { onDelete: "cascade" }),
competitorStepClickedAt: timestampColumn("competitor_step_clicked_at"),
keywordStepClickedAt: timestampColumn("keyword_step_clicked_at"),
// "I already connected" on the MCP card: hides the card for this project
// without faking the org-level first-tool-call milestone, which stays
// truthful and self-heals when a real external call lands.
mcpCardDismissedAt: timestampColumn("mcp_card_dismissed_at"),
// Optional integration pitch: hiding it from the dashboard does not remove
// the GA4 connection controls from Project Settings.
ga4CardDismissedAt: timestampColumn("ga4_card_dismissed_at"),
updatedAt: timestampColumn("updated_at").notNull().default(isoNow),
});
// Retained backlink profile history. The dashboard no longer writes snapshots.
// Each row keeps its original domain when the project website changes.
export const backlinkSnapshots = pgTable(
"backlink_snapshots",
{
id: serial("id").primaryKey(),
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
domain: text("domain").notNull(),
rank: integer("rank"),
backlinks: bigint("backlinks", { mode: "number" }),
referringDomains: bigint("referring_domains", { mode: "number" }),
brokenBacklinks: bigint("broken_backlinks", { mode: "number" }),
newBacklinks: bigint("new_backlinks", { mode: "number" }),
lostBacklinks: bigint("lost_backlinks", { mode: "number" }),
newReferringDomains: bigint("new_referring_domains", { mode: "number" }),
lostReferringDomains: bigint("lost_referring_domains", { mode: "number" }),
capturedAt: timestampColumn("captured_at").notNull().default(isoNow),
},
(table) => [
index("backlink_snapshots_project_captured_idx").on(
table.projectId,
table.capturedAt,
),
],
);
// Personal checklist preferences; completion remains derived from product state.
export const dashboardStepDismissals = pgTable(
"dashboard_step_dismissals",
{
userId: text("user_id")
.notNull()
.references(() => user.id, { onDelete: "cascade" }),
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
step: text("step").notNull(),
},
(table) => [
primaryKey({ columns: [table.userId, table.projectId, table.step] }),
index("dashboard_step_dismissals_project_idx").on(table.projectId),
],
);
// Crawler-access credentials for one host (currently only Shopify's
// domain-scoped crawler signature). Stored on the project (website) it was
// added for; an audit looks across the organization's projects, so a second
// project auditing the same store still picks it up. The signature values are
// encrypted, and never returned to the client.
export const crawlerCredentials = pgTable(
"crawler_credentials",
{
id: text("id").primaryKey(),
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
// Normalized lowercase hostname, e.g. "www.store.com".
host: text("host").notNull(),
provider: text("provider", { enum: ["shopify"] }).notNull(),
signatureInput: text("signature_input").notNull(),
signature: text("signature").notNull(),
// Parsed from the RFC 9421 `expires=` parameter when present; null when
// the signature input carries no expiry.
expiresAt: timestampColumn("expires_at"),
createdByUserId: text("created_by_user_id"),
createdAt: timestampColumn("created_at").notNull().default(isoNow),
},
(table) => [
uniqueIndex("crawler_credentials_project_host_idx").on(
table.projectId,
table.host,
),
],
);
// Cross-request refresh claims survive Worker instances and prevent duplicate spend.
export { dataRefreshClaims } from "./dashboard.schema";