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 {
index,
pgTable,
primaryKey,
text,
uniqueIndex,
} from "drizzle-orm/pg-core";
import { projects } from "./app.schema";
// Timestamps are stored as *text* (same column shape as the SQLite schema); see
// the note in pg/app.schema.ts. `isoNow` matches `new Date().toISOString()` so
// DB-defaulted and app-written values sort together lexicographically.
const isoNow = sql`to_char(now() AT TIME ZONE 'utc', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')`;
// ============================================================================
// Project memory: the shared AI context every surface (SAM, MCP, settings UI)
// reads and writes. Prose lives in the sections table; list-shaped knowledge is
// normalized so it stays joinable instead of buried in markdown.
// ============================================================================
// One row per (project, section). `key` is either a typed key
// ("business_overview", "current_goal", "positioning", "writing_preferences")
// or "custom:<slug>" for an agent-created section, in which case `title` holds
// its display name.
export const projectContextSections = pgTable(
"project_context_sections",
{
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
key: text("key").notNull(),
title: text("title"),
content: text("content").notNull(),
updatedAt: text("updated_at").notNull().default(isoNow),
updatedBy: text("updated_by", { enum: ["user", "sam", "mcp"] }).notNull(),
},
// The composite PK is project-leading, so it also serves the "load every
// section for this project" read.
(table) => [primaryKey({ columns: [table.projectId, table.key] })],
);
export const projectCompetitors = pgTable(
"project_competitors",
{
id: text("id").primaryKey(),
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
// Normalized bare host (lowercase, no protocol/www), so agents adding the
// same competitor from different surfaces upsert onto one row.
domain: text("domain").notNull(),
name: text("name"),
notes: text("notes"),
updatedAt: text("updated_at").notNull().default(isoNow),
updatedBy: text("updated_by", { enum: ["user", "sam", "mcp"] }).notNull(),
},
(table) => [
uniqueIndex("project_competitors_project_domain_idx").on(
table.projectId,
table.domain,
),
],
);
// A curated shortlist of pages that matter, NOT a page inventory — the
// inventory lives in audit_pages and GSC.
export const projectKeyPages = pgTable(
"project_key_pages",
{
id: text("id").primaryKey(),
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
url: text("url").notNull(),
role: text("role", { enum: ["hub", "spoke", "money", "other"] }).notNull(),
topic: text("topic"),
notes: text("notes"),
updatedAt: text("updated_at").notNull().default(isoNow),
updatedBy: text("updated_by", { enum: ["user", "sam", "mcp"] }).notNull(),
},
(table) => [
uniqueIndex("project_key_pages_project_url_idx").on(
table.projectId,
table.url,
),
],
);
// What research has already been bought and what it concluded, so SAM and
// Claude Code stop re-buying the same paid research. Pruned to 90 days on
// append; the date is server-stamped, never supplied by the caller.
export const projectResearchLog = pgTable(
"project_research_log",
{
id: text("id").primaryKey(),
projectId: text("project_id")
.notNull()
.references(() => projects.id, { onDelete: "cascade" }),
entryDate: text("entry_date").notNull(),
summary: text("summary").notNull(),
createdBy: text("created_by", { enum: ["user", "sam", "mcp"] }).notNull(),
// entry_date is a day stamp, so recency needs its own column — same-day
// entries would otherwise tie-break on a random uuid.
createdAt: text("created_at").notNull().default(isoNow),
},
(table) => [
index("project_research_log_project_date_idx").on(
table.projectId,
table.entryDate,
),
],
);