// src/lib/db/domain-schema.ts // SEO Insight domain tables. Tenancy FKs point at Better Auth's organization/user. import { relations, type SQL, sql } from "drizzle-orm"; import { boolean, check, date, index, integer, jsonb, pgTable, primaryKey, real, text, timestamp, uniqueIndex, varchar, } from "drizzle-orm/pg-core"; import type { BacklinkSource, Effort, LinkType, SourceKind, } from "@/lib/backlink/types"; import type { ConnectorProvider } from "@/lib/connectors/types"; import type { CrawlOnpagePayload } from "@/lib/keywords/serp-types"; import type { TargetStatus } from "@/lib/keywords/targets"; import type { KeywordSource, KeywordStage, KeywordStatus, KeywordTheme, MonthlyVolume, SearchIntent, SourceMetrics, } from "@/lib/keywords/types"; import type { SerpSemanticsPayload, StoredSemanticProfile, } from "@/lib/semantic/profile-types"; import { organization, user } from "./auth-schema"; export type BacklinkStatus = | "prospect" | "planned" | "in_progress" | "live" | "lost"; /** A site IS a project: the brand-domain we work on, scoped to an org. */ export const sites = pgTable( "sites", { id: text("id").primaryKey(), orgId: text("org_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), // The org, not the user, owns the site's SEO data — onDelete:set null // (no cascade) so a site survives its creator's account deletion, // orphaned but intact and reassignable. Same pattern as // admin_audit_log.actorId. No app code reads this value functionally // today (audit/attribution field only), so orphaning is a safe default. ownerUserId: text("owner_user_id").references(() => user.id, { onDelete: "set null", }), name: text("name").notNull(), url: text("url"), primaryLanguage: text("primary_language").notNull().default("en"), languages: text("languages") .array() .$type() .notNull() .default([]), targetCountries: text("target_countries") .array() .$type() .notNull() .default([]), // Mapped Google Search Console property (e.g. "sc-domain:example.com"). Null = unmapped. gscProperty: text("gsc_property"), // Qui a branché SON compte Google sur ce site. L'autorité est un attribut // du site, pas de l'org : dans une agence, le consultant qui gère le site // a les accès Search Console du client, pas l'owner du compte SEO Insight. // SET NULL et pas CASCADE : supprimer un utilisateur ne supprime pas le // site. La propriété mappée (gscProperty) SURVIT à la perte d'autorité, // pour que la reprise en main soit un clic. gscAuthorizedUserId: text("gsc_authorized_user_id").references( () => user.id, { onDelete: "set null" }, ), // QUEL compte Google de l'autorité est interrogé. Porte `account.account_id` // (l'identifiant Google), PAS `account.id` : c'est ce que `body.accountId` // de `auth.api.getAccessToken` attend. Pas de clé étrangère pour cette // raison — et parce que la disparition du compte doit se traduire par un // échec de résolution typé (`no-google-account`), pas par une erreur de // contrainte. Sans cette colonne, Better Auth refait sa propre sélection // arbitraire parmi les comptes Google de l'utilisateur (account.mjs:270) : // le jeton pourrait venir d'un compte sans droit sur `gscProperty`. gscAuthorizedAccountId: text("gsc_authorized_account_id"), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [ // Priority: org-scoped lookups + ORDER BY name run on nearly every request. index("sites_org_id_name_idx").on(t.orgId, t.name), index("sites_owner_idx").on(t.ownerUserId), // S3 (sec audit): a GSC property must map to at most one site, GLOBALLY — // not just per-org. Two sites sharing a property would duplicate GSC // snapshot writes (gsc_query_snapshots/gsc_page_snapshots PK is // (siteId, date, ...)) and silently split/duplicate the same search data // across two dashboards. No legitimate multi-org case was found: a mapping // pins the authority on the site itself — the (gscAuthorizedUserId, // gscAuthorizedAccountId) couple resolved by src/lib/gsc/site-token.ts — // so a verified property is only ever fetched through the single Google // account that mapped it. Partial (WHERE NOT NULL) so multiple unmapped // sites coexist. uniqueIndex("sites_gsc_property_uidx") .on(t.gscProperty) .where(sql`${t.gscProperty} IS NOT NULL`), index("sites_gsc_authorized_user_idx").on(t.gscAuthorizedUserId), ], ); /** Per-member site sharing. Owners/admins see ALL org sites; a `member` * sees ONLY the sites granted here (one row per site they may access). Absence * of a row = no access (secure by default). */ export const siteAccess = pgTable( "site_access", { siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), userId: text("user_id") .notNull() .references(() => user.id, { onDelete: "cascade" }), grantedAt: timestamp("granted_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [ primaryKey({ columns: [t.siteId, t.userId] }), index("site_access_user_idx").on(t.userId), ], ); /** Keywords are per-site; rich source metrics / trend kept as jsonb. */ export const keywords = pgTable( "keywords", { id: text("id").primaryKey(), siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), term: text("term").notNull(), lang: text("lang").notNull().default("en"), country: text("country").notNull().default("US"), theme: text("theme").$type(), status: text("status").$type().notNull(), intent: text("intent") .array() .$type() .notNull() .default([]), stages: text("stages") .array() .$type() .notNull() .default([]), primarySource: text("primary_source").$type().notNull(), sources: text("sources") .array() .$type() .notNull() .default([]), metrics: jsonb("metrics") .$type>>() .notNull() .default({}), trend: jsonb("trend").$type().notNull().default([]), /** Trend série par source d'importation (sparklines par-source). `trend` * reste la dernière série importée (fallback / saisonnalité). */ trendBySource: jsonb("trend_by_source") .$type>>() .notNull() .default({}), serpFeatures: text("serp_features").array().$type(), briefs: jsonb("briefs") .$type>() .notNull() .default({}), gsc: jsonb("gsc").$type< import("@/lib/keywords/types").GscTracking | null >(), // Overlay GSC par pays cible (clé = alpha-2, ex. "FR") — miroir du pattern `serp`. gscByCountry: jsonb("gsc_by_country") .$type< Partial> >() .notNull() .default({}), serp: jsonb("serp") .$type>() .notNull() .default({}), // Profil sémantique SERP par pays (moteur semantic-core). Merge par-locale // comme `serp`/`briefs` ; TOAST lz4 (migration). semantic: jsonb("semantic").$type>(), // Rail de statut d'enrichissement par provider (générique) : provider → // { status, at, error? }. Merge SQL-side (`enrichment || {...}`) par // enrich-status.ts ; sous-tend le spinner par-ligne du réservoir. enrichment: jsonb("enrichment") .$type() .notNull() .default({}), // Suivi des exports d'enrichissement : outil → date ISO du dernier export (ELEVA-49). enrichmentExports: jsonb("enrichment_exports") .$type< Partial> >() .notNull() .default({}), // Mapping page ciblée (module Pages) : la page de l'inventaire assignée à ce // kw. `targetPageSource` = gsc | content | text | manual (priorité de // signal) ; `targetPageScore` (0-100) renseigné pour `text` seulement. // FK ON DELETE SET NULL : supprimer une page délie le kw (ré-mappable). targetPageId: text("target_page_id").references(() => sitePages.id, { onDelete: "set null", }), targetPageSource: text("target_page_source").$type< "gsc" | "content" | "text" | "manual" >(), targetPageScore: integer("target_page_score"), // Colonnes générées STORED : dénormalisent les métriques de la source primaire // depuis `metrics` (jsonb) pour un tri/filtre serveur indexable, sans write-path // à maintenir. NULL quand la source primaire n'a pas de métrique (GSC-only/manual). volumePrimary: real("volume_primary").generatedAlwaysAs( (): SQL => sql`((metrics -> primary_source ->> 'volume')::real)`, ), kdPrimary: real("kd_primary").generatedAlwaysAs( (): SQL => sql`((metrics -> primary_source ->> 'kd')::real)`, ), cpcPrimary: real("cpc_primary").generatedAlwaysAs( (): SQL => sql`((metrics -> primary_source ->> 'cpc')::real)`, ), growthPrimary: real("growth_primary").generatedAlwaysAs( (): SQL => sql`((metrics -> primary_source ->> 'growthPct')::real)`, ), }, (t) => [ index("keywords_site_term_idx").on(t.siteId, t.term), index("keywords_stages_gin").using("gin", t.stages), index("keywords_site_volume_idx").on( t.siteId, t.volumePrimary.desc().nullsLast(), ), index("keywords_site_kd_idx").on(t.siteId, t.kdPrimary), ], ); /** Per-site keyword categories (themes). keywords.theme stores `slug`. */ export const keywordThemes = pgTable( "keyword_themes", { id: text("id").primaryKey(), // `${siteId}-${slug}` siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), slug: text("slug").notNull(), label: text("label").notNull(), }, (t) => [index("keyword_themes_site_idx").on(t.siteId)], ); /** Per-keyword × country targeting status (candidate/planned/live) for Recherche. */ export const keywordTargets = pgTable( "keyword_targets", { keywordId: text("keyword_id") .notNull() .references(() => keywords.id, { onDelete: "cascade" }), siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), country: text("country").notNull(), status: text("status").$type().notNull(), }, (t) => [ primaryKey({ columns: [t.keywordId, t.country] }), index("keyword_targets_site_idx").on(t.siteId), ], ); /** Per-site slugs to keep OUT of a site's keywords — a GSC re-sync skips them. * Populated when the user excludes an unwanted GSC-imported keyword. */ export const keywordExclusions = pgTable( "keyword_exclusions", { siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), slug: text("slug").notNull(), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [primaryKey({ columns: [t.siteId, t.slug] })], ); /** Per-site working cart of keywords (F8 shortlist) — build a selection across * filters/pages before acting on it. */ export const keywordShortlist = pgTable( "keyword_shortlist", { siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), keywordId: text("keyword_id") .notNull() .references(() => keywords.id, { onDelete: "cascade" }), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [ primaryKey({ columns: [t.siteId, t.keywordId] }), index("keyword_shortlist_keyword_idx").on(t.keywordId), ], ); /** Async ETL jobs for the analytics service (queue + status). */ export const analyticsJobs = pgTable( "analytics_jobs", { id: text("id").primaryKey(), siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), /** Job kind — drives the switch in the analytics worker. `csv-import` * (default, legacy) reads `source`/`objectKey` ; `site-crawl` has neither ; * `serp-semantics` reads `payload` (keywordId/country/urls curées) ; * `crawl-onpage` reads `payload` (keywordId). */ type: text("type") .$type<"csv-import" | "site-crawl" | "serp-semantics" | "crawl-onpage">() .notNull() .default("csv-import"), // `source`/`objectKey` : requis par csv-import, NULL pour site-crawl (qui // n'a ni fichier importé ni source de mots-clés). source: text("source").$type(), objectKey: text("object_key"), /** Language chosen at import (CSV carries none for Semrush/SE-Ranking). Null * → the worker falls back to the site's primary language. Drives lang + * country so rows aren't stamped « fr-US ». */ lang: text("lang"), /** Where the keywords land. "recherche" tags them with the "research" stage; * "reservoir" (default) leaves stages untouched. */ destination: text("destination") .$type<"reservoir" | "recherche">() .notNull() .default("reservoir"), status: text("status") .$type<"queued" | "running" | "done" | "failed">() .notNull() .default("queued"), totals: jsonb("totals") .$type<{ read?: number; matched?: number; created?: number; upserted?: number; crawled?: number; blocked?: number; failed?: number; }>() .notNull() .default({}), error: text("error"), // Payload structuré des jobs qui ne rentrent pas dans les colonnes plates // (serp-semantics : keywordId, country, urls curées, clientUrl ; // crawl-onpage : keywordId). payload: jsonb("payload").$type< SerpSemanticsPayload | CrawlOnpagePayload | null >(), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [index("analytics_jobs_site_idx").on(t.siteId)], ); /** Per-site inventory of crawled pages (sitemap → métas), façon Screaming Frog. * One row per URL, upserted idempotently on each crawl (ON CONFLICT * (siteId,url)). `issues` are computed at upsert (voir src/lib/pages/issues.ts). * Feeds the keyword↔page mapping (keywords.targetPageId). */ export const sitePages = pgTable( "site_pages", { // `${siteId}-${sha256(normalizedUrl).slice(0,16)}` — short stable hash. id: text("id").primaryKey(), siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), // URL telle que crawlée (après redirects). UNIQUE (siteId, url). url: text("url").notNull(), // HTTP final status (200, 404, 0 = fetch raté/timeout). status: integer("status"), title: text("title"), metaDescription: text("meta_description"), // Première H1 (texte trimé) ; h1Count = nombre total de H1. h1: text("h1"), h1Count: integer("h1_count").notNull().default(0), // href du . canonical: text("canonical"), // contenu du (ex. "noindex,follow"). robots: text("robots"), lang: text("lang"), wordCount: integer("word_count").notNull().default(0), // Calculées à l'upsert (title-long, description-missing, noindex, …). issues: text("issues").array().$type().notNull().default([]), crawledAt: timestamp("crawled_at", { withTimezone: true }).notNull(), }, (t) => [ index("site_pages_site_idx").on(t.siteId), uniqueIndex("site_pages_site_url_uidx").on(t.siteId, t.url), ], ); /** GLOBAL reservoir (directory) of link sources, shared across all org sites. */ export const linkSources = pgTable( "link_sources", { id: text("id").primaryKey(), orgId: text("org_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), platform: text("platform").notNull(), url: text("url").notNull(), domainNorm: text("domain_norm").notNull(), kind: text("kind").$type().notNull(), da: integer("da"), effort: text("effort").$type().notNull(), linkType: text("link_type").$type().notNull(), language: text("language").notNull().default("en"), themes: text("themes").array().$type().notNull().default([]), tags: text("tags").array().$type().notNull().default([]), flags: jsonb("flags") .$type<{ toxic: boolean; disavow: boolean }>() .notNull() .default({ toxic: false, disavow: false }), note: text("note"), // Part of the curated global catalog (provisioned to new orgs). Super-admin only. global: boolean("global").notNull().default(false), }, // domain_norm intentionally repeats (path-aware dedup). Row identity = id. (t) => [ index("link_sources_org_domain_idx").on(t.orgId, t.domainNorm), // Partial index for the curated global catalog scan (god-mode). The WHERE // clause is hand-written into the generated migration (Drizzle can't emit it). index("link_sources_global_idx").on(t.id).where(sql`"global"`), // S2 (sec audit): `da` (Domain Authority) is a 0-100 score; nullable // (unknown until fetched). check( "link_sources_da_range", sql`${t.da} IS NULL OR ${t.da} BETWEEN 0 AND 100`, ), ], ); /** Per-site relation to a source (the "targeting": prospect → live). */ export const backlinks = pgTable( "backlinks", { id: text("id").primaryKey(), linkSourceId: text("link_source_id") .notNull() .references(() => linkSources.id, { onDelete: "cascade" }), siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), status: text("status") .$type() .notNull() .default("prospect"), anchor: text("anchor"), toxic: boolean("toxic").notNull().default(false), disavow: boolean("disavow").notNull().default(false), firstSeen: timestamp("first_seen", { withTimezone: true }), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [ uniqueIndex("backlinks_source_site_uq").on(t.linkSourceId, t.siteId), index("backlinks_site_idx").on(t.siteId), ], ); /** Suivi d'outreach léger — une ligne par backlink (upsert). */ export const backlinkOutreach = pgTable("backlink_outreach", { backlinkId: text("backlink_id") .primaryKey() .references(() => backlinks.id, { onDelete: "cascade" }), contact: text("contact"), channel: text("channel"), attempts: integer("attempts").notNull().default(0), lastContactAt: timestamp("last_contact_at", { withTimezone: true }), notes: text("notes"), updatedAt: timestamp("updated_at", { withTimezone: true }) .defaultNow() .notNull(), }); /** Per-site INBOUND backlink profile (audit) — the links pointing AT our domain. * Distinct from `backlinks` (acquisition targeting). Re-import replaces a site's rows. */ export const domainBacklinks = pgTable( "domain_backlinks", { id: text("id").primaryKey(), // `${siteId}:${sha1(sourceUrl|targetUrl)}` siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), /** Fournisseur de l'export (semrush par défaut pour les lignes existantes). * Le REPLACE à l'import est par-source : importer Ahrefs n'efface pas Semrush. */ source: text("source").$type().notNull().default("semrush"), sourceUrl: text("source_url").notNull(), sourceTitle: text("source_title"), sourceDomain: text("source_domain").notNull(), targetUrl: text("target_url").notNull(), anchor: text("anchor"), authority: integer("authority"), nofollow: boolean("nofollow").notNull().default(false), sponsored: boolean("sponsored").notNull().default(false), ugc: boolean("ugc").notNull().default(false), sitewide: boolean("sitewide").notNull().default(false), placement: text("placement").notNull().default("text"), firstSeen: timestamp("first_seen", { withTimezone: true }), lastSeen: timestamp("last_seen", { withTimezone: true }), isNew: boolean("is_new").notNull().default(false), isLost: boolean("is_lost").notNull().default(false), importedAt: timestamp("imported_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [ index("domain_backlinks_site_idx").on(t.siteId), index("domain_backlinks_site_authority_idx").on(t.siteId, t.authority), index("domain_backlinks_site_source_idx").on(t.siteId, t.source), // S2 (sec audit): `authority` is a 0-100 score; nullable (unknown until // fetched from the source's export). check( "domain_backlinks_authority_range", sql`${t.authority} IS NULL OR ${t.authority} BETWEEN 0 AND 100`, ), ], ); /** Shared per-domain Ahrefs Domain Rating cache (org-agnostic; keyed by domain). */ export const domainRatings = pgTable( "domain_ratings", { domainNorm: text("domain_norm").primaryKey(), dr: integer("dr").notNull(), // 0-100, rounded fetchedAt: timestamp("fetched_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [ // S2 (sec audit): `dr` is always populated (notNull) — no NULL branch needed. check("domain_ratings_dr_range", sql`${t.dr} BETWEEN 0 AND 100`), ], ); export type ContentStatus = | "idea" | "brief" | "writing" | "review" | "published"; // Nature du travail, orthogonale au status. Axe extensible (refresh/merge/…). export type ContentKind = "new" | "optimize"; export const contentItems = pgTable( "content_items", { id: text("id").primaryKey(), siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), title: text("title").notNull(), status: text("status").$type().notNull().default("idea"), primaryKeywordId: text("primary_keyword_id").references(() => keywords.id, { onDelete: "set null", }), lang: text("lang").notNull().default("fr"), country: text("country").notNull().default("FR"), assignee: text("assignee"), dueDate: timestamp("due_date", { withTimezone: true }), url: text("url"), kind: text("kind").$type().notNull().default("new"), warnedPageUrl: text("warned_page_url"), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [ index("content_items_site_idx").on(t.siteId), index("content_items_primary_kw_idx").on(t.primaryKeywordId), index("content_items_site_created_idx").on(t.siteId, t.createdAt), ], ); /** Keyword↔page link (primary = the page's main keyword; mirrors content_items.primary_keyword_id). */ export const contentKeywords = pgTable( "content_keywords", { contentItemId: text("content_item_id") .notNull() .references(() => contentItems.id, { onDelete: "cascade" }), keywordId: text("keyword_id") .notNull() .references(() => keywords.id, { onDelete: "cascade" }), siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), role: text("role") .$type<"primary" | "secondary">() .notNull() .default("secondary"), }, (t) => [ primaryKey({ columns: [t.contentItemId, t.keywordId] }), index("content_keywords_keyword_idx").on(t.keywordId), index("content_keywords_site_idx").on(t.siteId), ], ); /** Daily GSC search-analytics snapshot, dimension query (worker-written). */ export const gscQuerySnapshots = pgTable( "gsc_query_snapshots", { siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), date: date("date").notNull(), query: text("query").notNull(), clicks: integer("clicks").notNull(), impressions: integer("impressions").notNull(), ctr: real("ctr").notNull(), position: real("position").notNull(), }, (t) => [primaryKey({ columns: [t.siteId, t.date, t.query] })], ); /** Daily GSC search-analytics snapshot, dimension page (worker-written). */ export const gscPageSnapshots = pgTable( "gsc_page_snapshots", { siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), date: date("date").notNull(), page: text("page").notNull(), clicks: integer("clicks").notNull(), impressions: integer("impressions").notNull(), ctr: real("ctr").notNull(), position: real("position").notNull(), }, (t) => [primaryKey({ columns: [t.siteId, t.date, t.page] })], ); /** One row per worker run (observability: last snapshot, failures). */ export const gscSnapshotRuns = pgTable("gsc_snapshot_runs", { id: text("id").primaryKey(), startedAt: timestamp("started_at", { withTimezone: true }).notNull(), finishedAt: timestamp("finished_at", { withTimezone: true }), sitesOk: integer("sites_ok").notNull().default(0), sitesFailed: integer("sites_failed").notNull().default(0), rowsUpserted: integer("rows_upserted").notNull().default(0), error: text("error"), }); /** * Per-site spec audit runs (ELEVA-94). Append-only history; the latest row per * site is the current audit. `results` is the flattened check verdicts. */ export const siteAudits = pgTable( "site_audits", { id: text("id").primaryKey(), siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), ranAt: timestamp("ran_at", { withTimezone: true }).defaultNow().notNull(), score: integer("score").notNull(), results: jsonb("results") .$type() .notNull(), // Dénormalisé depuis `results` par persistSiteAudit — évite de rapatrier // le jsonb (TOAST, ~156 items) juste pour compter les statuts sur les // vues d'overview (dashboard + tool MCP audit_overview). passCount: integer("pass_count").notNull().default(0), failCount: integer("fail_count").notNull().default(0), warnCount: integer("warn_count").notNull().default(0), }, (t) => [ // Un seul audit conservé par site (pas de versionning — la table ne grossit // pas avec les re-runs) : l'action upsert sur ce unique. uniqueIndex("site_audits_site_unique").on(t.siteId), // S2 (sec audit): `score` is a 0-100 composite of the check verdicts, always // populated (notNull). check("site_audits_score_range", sql`${t.score} BETWEEN 0 AND 100`), ], ); /** * Per-org add-on entitlements (ELEVA-100). Presence of a row = the add-on is * enabled for that org. Toggled by the super-admin (god-mode); billing later. */ export const orgAddons = pgTable( "org_addons", { orgId: text("org_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), addon: text("addon").notNull(), // e.g. "checklist_mcp" enabledAt: timestamp("enabled_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [primaryKey({ columns: [t.orgId, t.addon] })], ); /** * Per-org API keys for the MCP server (ELEVA-100). Only the sha256 hash of the * key is stored — the clear key is shown once at creation. `prefix` is the * display fragment ("eli_" + 4 chars). Revocation is a soft delete (revokedAt). */ export const mcpApiKeys = pgTable( "mcp_api_keys", { id: text("id").primaryKey(), orgId: text("org_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), // The key's creator/owner — keys are per-user (ELEVA-102). userId: text("user_id") .notNull() .references(() => user.id, { onDelete: "cascade" }), name: text("name").notNull(), prefix: text("prefix").notNull(), keyHash: text("key_hash").notNull(), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), lastUsedAt: timestamp("last_used_at", { withTimezone: true }), revokedAt: timestamp("revoked_at", { withTimezone: true }), // Number of MCP requests authenticated with this key (cumulative). requestCount: integer("request_count").notNull().default(0), // Monthly metering (ELEVA-110): the month being counted ("YYYY-MM") and the // number of requests within it. Rolled over atomically in resolveMcpOrg. requestMonth: text("request_month"), monthlyCount: integer("monthly_count").default(0).notNull(), }, (t) => [ index("mcp_api_keys_org_idx").on(t.orgId), // S7 (sec audit): `keyHash` is the AUTHENTICATION lookup key (src/lib/mcp/auth.ts // resolves the org from it) — a plain index allowed two rows to collide on // the same hash, which would make auth.ts's `.where(eq(keyHash, ...))` // ambiguous across orgs (picks whichever row postgres returns first). // UNIQUE closes that; keeps the same lookup performance. uniqueIndex("mcp_api_keys_hash_uidx").on(t.keyHash), index("mcp_api_keys_user_idx").on(t.userId), ], ); /** * MCP credit-pack purchases (3C) — audit log of one-off Stripe Checkout * purchases. `stripeSessionId` is UNIQUE so webhook retries can't double-credit * (fulfillment inserts with ON CONFLICT DO NOTHING and only increments * `organization.mcpCreditBalance` when the row was actually inserted). */ export const mcpCreditPurchases = pgTable( "mcp_credit_purchases", { id: text("id").primaryKey(), orgId: text("org_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), stripeSessionId: text("stripe_session_id").notNull().unique(), credits: integer("credits").notNull(), amountCents: integer("amount_cents").notNull(), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [index("mcp_credit_purchases_org_idx").on(t.orgId)], ); /** * SE-Ranking sync top-up purchases (monétisation slice 2) — audit log of * one-off Stripe Checkout purchases. `stripeSessionId` is UNIQUE so webhook * retries can't double-credit (fulfillment inserts with ON CONFLICT DO * NOTHING and only increments `organization.serankingSyncBalance` when the * row was actually inserted). Mirror of `mcpCreditPurchases` (credits→syncs). */ export const serankingSyncPurchases = pgTable( "seranking_sync_purchases", { id: text("id").primaryKey(), orgId: text("org_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), stripeSessionId: text("stripe_session_id").notNull().unique(), syncs: integer("syncs").notNull(), amountCents: integer("amount_cents").notNull(), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [index("seranking_sync_purchases_org_idx").on(t.orgId)], ); // ── Support ticketing (ELEVA-104) ──────────────────────────────────────────── /** * Support tickets (ELEVA-104). A ticket is opened by an authenticated org user * (client). Staff = super-admins (no global role; reuse the super-admin gate). * `assignedTo` is the staff user handling it (T2). `orgId` = the creator's org * (cascade with the org). `status` index powers the staff inbox sort. */ export const supportTickets = pgTable( "support_tickets", { id: text("id").primaryKey(), // uuid title: varchar("title", { length: 256 }).notNull(), status: text("status") .$type() .notNull() .default("open"), priority: text("priority") .$type() .notNull() .default("medium"), // A ticket is a support record, not the creator's property — it must // survive the creator's account deletion. onDelete:set null (nullable) // instead of notNull, same pattern as admin_audit_log.actorId. userId: text("user_id").references(() => user.id, { onDelete: "set null", }), orgId: text("org_id").references(() => organization.id, { onDelete: "cascade", }), // Staff assignee — also survives the assignee's account deletion. assignedTo: text("assigned_to").references(() => user.id, { onDelete: "set null", }), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [ index("support_tickets_user_idx").on(t.userId), index("support_tickets_status_idx").on(t.status), index("support_tickets_org_idx").on(t.orgId), index("support_tickets_assigned_idx").on(t.assignedTo), ], ); /** A message in a ticket thread (chat bubble). senderType styles the bubble. */ export const supportMessages = pgTable( "support_messages", { id: text("id").primaryKey(), ticketId: text("ticket_id") .notNull() .references(() => supportTickets.id, { onDelete: "cascade" }), senderId: text("sender_id").notNull(), // user.id senderType: text("sender_type") .$type() .notNull(), content: text("content").notNull(), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [index("support_messages_ticket_idx").on(t.ticketId)], ); /** * Rail générique de connecteurs par org (chantier Mangools, extensible IA). * Une ligne = un provider branché pour une org, avec son token API BYO chiffré * au repos (AES-256-GCM, src/lib/connectors/crypto.ts). `id` = `${orgId}-${provider}` * (déterministe → upsert par PK). `status` passe à "invalid" quand le token est * rejeté par le provider (le store renvoie alors null au lieu du token). Le * store bas-niveau vit dans src/lib/connectors/store.ts. */ export const orgConnectors = pgTable( "org_connectors", { id: text("id").primaryKey(), // `${orgId}-${provider}` orgId: text("org_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), provider: text("provider").$type().notNull(), // Token API chiffré (jamais en clair) — voir connectors/crypto.ts. encryptedToken: text("encrypted_token").notNull(), status: text("status") .$type<"active" | "invalid">() .notNull() .default("active"), connectedBy: text("connected_by").notNull(), // user.id createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [ // Un seul connecteur par (org, provider) — l'upsert du store cible cet unique. uniqueIndex("org_connectors_org_provider_uidx").on(t.orgId, t.provider), ], ); /** * Cache de résolution pays (ISO2) → location_id Mangools (chantier connecteur). * KWFinder/SERPChecker exigent un `location_id` EXPLICITE : chez Mangools, `0` * est un repli US SILENCIEUX — jamais acceptable. La résolution * (src/lib/mangools/locations.ts) lit d'abord ce cache ; sur miss elle interroge * l'API `mangools/locations` (par nom de pays), garde le hit `target_type = * "Country"` du bon `country_code`, le persiste ici, et renvoie `null` si rien * ne matche (jamais de valeur par défaut). PK = code pays ISO2 en MAJUSCULES. * Table org-agnostique (la géo Mangools est globale) ; seed FR=2250 en migration. */ export const mangoolsLocations = pgTable("mangools_locations", { countryCode: text("country_code").primaryKey(), // ISO2 upper (ex. "FR") locationId: integer("location_id").notNull(), label: text("label").notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }) .defaultNow() .notNull(), }); // ── Relations ────────────────────────────────────────────────────────────── export const sitesRelations = relations(sites, ({ one, many }) => ({ organization: one(organization, { fields: [sites.orgId], references: [organization.id], }), owner: one(user, { fields: [sites.ownerUserId], references: [user.id] }), keywords: many(keywords), backlinks: many(backlinks), })); export const keywordsRelations = relations(keywords, ({ one }) => ({ site: one(sites, { fields: [keywords.siteId], references: [sites.id] }), targetPage: one(sitePages, { fields: [keywords.targetPageId], references: [sitePages.id], }), })); export const sitePagesRelations = relations(sitePages, ({ one, many }) => ({ site: one(sites, { fields: [sitePages.siteId], references: [sites.id] }), keywords: many(keywords), })); export const linkSourcesRelations = relations(linkSources, ({ one, many }) => ({ organization: one(organization, { fields: [linkSources.orgId], references: [organization.id], }), backlinks: many(backlinks), })); export const backlinksRelations = relations(backlinks, ({ one }) => ({ linkSource: one(linkSources, { fields: [backlinks.linkSourceId], references: [linkSources.id], }), site: one(sites, { fields: [backlinks.siteId], references: [sites.id] }), })); /** Dismissal de la checklist d'activation, par (user, org). La progression * elle-même n'est JAMAIS stockée : elle est dérivée de l'état produit * (src/lib/onboarding/activation/derive.ts). */ export const activationDismissals = pgTable( "activation_dismissals", { userId: text("user_id") .notNull() .references(() => user.id, { onDelete: "cascade" }), orgId: text("org_id") .notNull() .references(() => organization.id, { onDelete: "cascade" }), dismissedAt: timestamp("dismissed_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [primaryKey({ columns: [t.userId, t.orgId] })], ); /** * Progression du tutoriel, PAR UTILISATEUR (pas par organisation) : un tour * explique l'interface, pas les données d'une org. Changer d'organisation ne * doit pas rejouer les mêmes tours — c'est pourquoi, contrairement à * `activation_dismissals`, il n'y a pas d'`org_id` ici. * * PK composite (user, tour) : un tour a UN statut, un second passage l'écrase * (`onConflictDoUpdate`) au lieu d'empiler des lignes. */ export const tourProgress = pgTable( "tour_progress", { userId: text("user_id") .notNull() .references(() => user.id, { onDelete: "cascade" }), tourId: text("tour_id").notNull(), status: text("status").$type<"completed" | "dismissed">().notNull(), updatedAt: timestamp("updated_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [primaryKey({ columns: [t.userId, t.tourId] })], );