spb/note-spboucher-ai

1/**2 * Note SPBoucher AI — relational schema (Drizzle / PostgreSQL 17 + pgvector).3 * Conventions: uuid PKs, timestamptz, soft delete via deleted_at on content tables, jsonb for flexible content.4 */5import { pgTable, uuid, text, timestamp, boolean, integer, jsonb, doublePrecision, index, uniqueIndex, customType, primaryKey, bigint } from "drizzle-orm/pg-core";6import { sql, relations as rel } from "drizzle-orm";78export const vector = customType<{ data: number[]; driverData: string }>({9  dataType() { return "vector(1536)"; },10  toDriver(v: number[]) { return `[${v.join(",")}]`; },11  fromDriver(v: string) { return v.slice(1, -1).split(",").map(Number); },12});13const tsvector = customType<{ data: string }>({ dataType() { return "tsvector"; } });1415const ts = (name?: string) => (name ? timestamp(name, { withTimezone: true, mode: "date" }) : timestamp({ withTimezone: true, mode: "date" }));16const created = () => ts().notNull().defaultNow();1718// ---------- identity & auth19export const users = pgTable("users", {20  id: uuid().primaryKey().defaultRandom(),21  username: text().notNull().unique(),22  displayName: text("display_name").notNull(),23  email: text(),24  passwordHash: text("password_hash").notNull(),25  totpSecret: text("totp_secret"),26  totpEnabled: boolean("totp_enabled").notNull().default(false),27  preferences: jsonb().$type<Record<string, unknown>>().notNull().default({}),28  createdAt: created(), updatedAt: created(),29});30export const sessions = pgTable("sessions", {31  id: uuid().primaryKey().defaultRandom(),32  userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),33  tokenHash: text("token_hash").notNull().unique(),34  userAgent: text("user_agent"), ip: text(),35  createdAt: created(), lastSeenAt: created(),36  expiresAt: ts().notNull(), absoluteExpiresAt: ts("absolute_expires_at").notNull(),37  revokedAt: ts("revoked_at"), mfaPassed: boolean("mfa_passed").notNull().default(false),38}, (t) => [index("sessions_user_idx").on(t.userId)]);39export const loginAttempts = pgTable("login_attempts", {40  id: uuid().primaryKey().defaultRandom(),41  ip: text().notNull(), username: text(), success: boolean().notNull(), createdAt: created(),42}, (t) => [index("login_attempts_ip_idx").on(t.ip, t.createdAt)]);43export const webauthnCredentials = pgTable("webauthn_credentials", {44  id: text().primaryKey(), userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),45  publicKey: text("public_key").notNull(), counter: bigint({ mode: "number" }).notNull().default(0),46  transports: jsonb().$type<string[]>().default([]), deviceType: text("device_type"), backedUp: boolean("backed_up").default(false),47  label: text(), createdAt: created(), lastUsedAt: ts("last_used_at"),48});49export const webauthnChallenges = pgTable("webauthn_challenges", {50  id: uuid().primaryKey().defaultRandom(), userId: uuid("user_id"), challenge: text().notNull(), kind: text().notNull(), createdAt: created(),51});5253// ---------- workspace & pages54export const workspaces = pgTable("workspaces", {55  id: uuid().primaryKey().defaultRandom(), ownerId: uuid("owner_id").notNull().references(() => users.id),56  name: text().notNull(), slug: text().notNull().unique(), settings: jsonb().$type<Record<string, unknown>>().notNull().default({}),57  createdAt: created(), updatedAt: created(),58});59export const pages = pgTable("pages", {60  id: uuid().primaryKey().defaultRandom(),61  workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),62  parentId: uuid("parent_id"),63  databaseId: uuid("database_id"), // set when the page is a row page of a database64  type: text().notNull().default("page"), // page | database | research | dashboard65  section: text().notNull().default("notes"), // notes | documents | databases | projects | research66  title: text().notNull().default("Untitled"), icon: text(), cover: text(),67  content: jsonb().$type<Record<string, unknown>>().notNull().default({ type: "doc", content: [] }),68  plainText: text("plain_text").notNull().default(""),69  tsv: tsvector("tsv").generatedAlwaysAs(sql`setweight(to_tsvector('simple', coalesce(title,'')), 'A') || setweight(to_tsvector('simple', coalesce(plain_text,'')), 'B')`),70  properties: jsonb().$type<Record<string, unknown>>().notNull().default({}),71  position: doublePrecision().notNull().default(0),72  pinned: boolean().notNull().default(false), archived: boolean().notNull().default(false),73  version: integer().notNull().default(1), wordCount: integer("word_count").notNull().default(0),74  createdAt: created(), updatedAt: created(), lastOpenedAt: ts("last_opened_at"), deletedAt: ts("deleted_at"),75}, (t) => [index("pages_ws_parent_idx").on(t.workspaceId, t.parentId), index("pages_tsv_idx").using("gin", t.tsv), index("pages_updated_idx").on(t.updatedAt), index("pages_db_idx").on(t.databaseId), index("pages_title_trgm_idx").using("gin", sql`${t.title} gin_trgm_ops`)]);76export const pageVersions = pgTable("page_versions", {77  id: uuid().primaryKey().defaultRandom(), pageId: uuid("page_id").notNull().references(() => pages.id, { onDelete: "cascade" }),78  version: integer().notNull(), title: text().notNull(), content: jsonb().notNull(), properties: jsonb().default({}),79  reason: text().notNull().default("autosave"), createdAt: created(), wordCount: integer("word_count").default(0),80}, (t) => [uniqueIndex("page_versions_unique").on(t.pageId, t.version)]);8182// ---------- databases83export const databases = pgTable("databases", {84  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),85  pageId: uuid("page_id").notNull().references(() => pages.id, { onDelete: "cascade" }), // the page that hosts the database86  name: text().notNull(), description: text(), icon: text(), isTemplate: boolean("is_template").notNull().default(false),87  templateOf: uuid("template_of"), createdAt: created(), updatedAt: created(), deletedAt: ts("deleted_at"),88});89export const databaseProperties = pgTable("database_properties", {90  id: uuid().primaryKey().defaultRandom(), databaseId: uuid("database_id").notNull().references(() => databases.id, { onDelete: "cascade" }),91  name: text().notNull(), type: text().notNull(), // text number date checkbox status select multi_select url file relation rollup formula created_time last_edited_time ai92  config: jsonb().$type<Record<string, unknown>>().notNull().default({}), position: integer().notNull().default(0), width: integer().default(180),93  createdAt: created(),94}, (t) => [index("db_props_db_idx").on(t.databaseId)]);95export const databaseRows = pgTable("database_rows", {96  id: uuid().primaryKey().defaultRandom(), databaseId: uuid("database_id").notNull().references(() => databases.id, { onDelete: "cascade" }),97  pageId: uuid("page_id").notNull().references(() => pages.id, { onDelete: "cascade" }),98  values: jsonb().$type<Record<string, unknown>>().notNull().default({}), position: doublePrecision().notNull().default(0),99  createdAt: created(), updatedAt: created(), deletedAt: ts("deleted_at"),100}, (t) => [index("db_rows_db_idx").on(t.databaseId), index("db_rows_values_idx").using("gin", t.values)]);101export const databaseViews = pgTable("database_views", {102  id: uuid().primaryKey().defaultRandom(), databaseId: uuid("database_id").notNull().references(() => databases.id, { onDelete: "cascade" }),103  name: text().notNull(), type: text().notNull().default("table"), // table board calendar gallery timeline list104  config: jsonb().$type<Record<string, unknown>>().notNull().default({}), position: integer().notNull().default(0), isDefault: boolean("is_default").notNull().default(false), createdAt: created(),105});106export const relationsTable = pgTable("relations", {107  id: uuid().primaryKey().defaultRandom(), propertyId: uuid("property_id").notNull().references(() => databaseProperties.id, { onDelete: "cascade" }),108  fromRowId: uuid("from_row_id").notNull().references(() => databaseRows.id, { onDelete: "cascade" }), toRowId: uuid("to_row_id").notNull().references(() => databaseRows.id, { onDelete: "cascade" }),109  createdAt: created(),110}, (t) => [uniqueIndex("relations_unique").on(t.propertyId, t.fromRowId, t.toRowId), index("relations_to_idx").on(t.toRowId)]);111112// ---------- attachments, tags, tasks, links113export const attachments = pgTable("attachments", {114  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),115  pageId: uuid("page_id").references(() => pages.id, { onDelete: "set null" }),116  filename: text().notNull(), mime: text().notNull(), size: bigint({ mode: "number" }).notNull(), sha256: text().notNull(), storagePath: text("storage_path").notNull(),117  extractedText: text("extracted_text"), pageCount: integer("page_count"), meta: jsonb().default({}), createdAt: created(), deletedAt: ts("deleted_at"),118}, (t) => [index("attachments_page_idx").on(t.pageId), index("attachments_sha_idx").on(t.sha256)]);119export const tags = pgTable("tags", {120  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),121  name: text().notNull(), color: text().default("gray"), createdAt: created(),122}, (t) => [uniqueIndex("tags_unique").on(t.workspaceId, t.name)]);123export const pageTags = pgTable("page_tags", { pageId: uuid("page_id").notNull().references(() => pages.id, { onDelete: "cascade" }), tagId: uuid("tag_id").notNull().references(() => tags.id, { onDelete: "cascade" }) }, (t) => [primaryKey({ columns: [t.pageId, t.tagId] })]);124export const tasks = pgTable("tasks", {125  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),126  pageId: uuid("page_id").references(() => pages.id, { onDelete: "set null" }), title: text().notNull(), status: text().notNull().default("todo"), priority: text().default("medium"),127  dueAt: ts("due_at"), completedAt: ts("completed_at"), project: text(), createdAt: created(), updatedAt: created(), deletedAt: ts("deleted_at"),128}, (t) => [index("tasks_due_idx").on(t.dueAt)]);129export const links = pgTable("links", {130  id: uuid().primaryKey().defaultRandom(), fromPageId: uuid("from_page_id").notNull().references(() => pages.id, { onDelete: "cascade" }), toPageId: uuid("to_page_id").notNull().references(() => pages.id, { onDelete: "cascade" }),131  kind: text().notNull().default("link"), createdAt: created(),132}, (t) => [uniqueIndex("links_unique").on(t.fromPageId, t.toPageId, t.kind), index("links_to_idx").on(t.toPageId)]);133export const knowledgeEdges = pgTable("knowledge_edges", {134  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),135  fromId: uuid("from_id").notNull(), toId: uuid("to_id").notNull(), fromType: text("from_type").notNull().default("page"), toType: text("to_type").notNull().default("page"),136  kind: text().notNull(), // link | backlink | relation | ai_suggested | concept137  status: text().notNull().default("accepted"), // suggested | accepted | rejected138  confidence: doublePrecision(), rationale: text(), model: text(), createdAt: created(), decidedAt: ts("decided_at"),139}, (t) => [uniqueIndex("kedges_unique").on(t.fromId, t.toId, t.kind), index("kedges_ws_idx").on(t.workspaceId, t.status)]);140141// ---------- search142export const chunks = pgTable("chunks", {143  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),144  sourceType: text("source_type").notNull(), // page | attachment | row145  sourceId: uuid("source_id").notNull(), pageId: uuid("page_id"), ordinal: integer().notNull(), blockId: text("block_id"), heading: text(),146  content: text().notNull(), tokenCount: integer("token_count").notNull().default(0), contentHash: text("content_hash").notNull(),147  tsv: tsvector("tsv").generatedAlwaysAs(sql`to_tsvector('simple', coalesce(heading,'') || ' ' || content)`),148  createdAt: created(),149}, (t) => [index("chunks_source_idx").on(t.sourceType, t.sourceId), index("chunks_tsv_idx").using("gin", t.tsv), uniqueIndex("chunks_unique").on(t.sourceType, t.sourceId, t.ordinal)]);150export const embeddings = pgTable("embeddings", {151  chunkId: uuid("chunk_id").primaryKey().references(() => chunks.id, { onDelete: "cascade" }),152  model: text().notNull(), version: integer().notNull().default(1), embedding: vector("embedding").notNull(), createdAt: created(),153}, (t) => [index("embeddings_hnsw_idx").using("hnsw", sql`${t.embedding} vector_cosine_ops`)]);154export const indexState = pgTable("index_state", {155  sourceType: text("source_type").notNull(), sourceId: uuid("source_id").notNull(), contentHash: text("content_hash").notNull(), model: text().notNull(), version: integer().notNull().default(1), indexedAt: created(),156}, (t) => [primaryKey({ columns: [t.sourceType, t.sourceId] })]);157158// ---------- AI159export const aiProviders = pgTable("ai_providers", {160  id: text().primaryKey(), name: text().notNull(), enabled: boolean().notNull().default(true), lastDiscoveryAt: ts("last_discovery_at"), status: text().default("unknown"), statusDetail: text("status_detail"),161});162export const aiModels = pgTable("ai_models", {163  id: text().primaryKey(), provider: text().notNull().references(() => aiProviders.id), displayName: text("display_name"),164  category: text().notNull().default("text"), available: boolean().notNull().default(true), enabled: boolean().notNull().default(true),165  contextWindow: integer("context_window"), maxOutput: integer("max_output"),166  inputPrice: doublePrecision("input_price"), cachedPrice: doublePrecision("cached_price"), outputPrice: doublePrecision("output_price"), pricingSource: text("pricing_source"),167  probes: jsonb().$type<Record<string, { status: string; detail?: string; ms?: number }>>().default({}),168  benchmark: jsonb().$type<Record<string, unknown>>().default({}), // summary.json aggregate for this model169  capabilities: jsonb().default({}), notes: jsonb().$type<string[]>().default([]), discoveredAt: ts("discovered_at"), probedAt: ts("probed_at"), benchmarkedAt: ts("benchmarked_at"), createdAt: created(), updatedAt: created(),170});171export const aiConversations = pgTable("ai_conversations", {172  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),173  title: text().notNull().default("New conversation"), pageId: uuid("page_id").references(() => pages.id, { onDelete: "set null" }), model: text(), routing: text().default("auto"),174  pinned: boolean().notNull().default(false), createdAt: created(), updatedAt: created(), deletedAt: ts("deleted_at"),175}, (t) => [index("ai_conv_ws_idx").on(t.workspaceId, t.updatedAt)]);176export const aiMessages = pgTable("ai_messages", {177  id: uuid().primaryKey().defaultRandom(), conversationId: uuid("conversation_id").notNull().references(() => aiConversations.id, { onDelete: "cascade" }),178  role: text().notNull(), content: text().notNull(), model: text(), provider: text(),179  sources: jsonb().$type<{ chunkId: string; pageId: string; title: string; excerpt: string; score: number }[]>().default([]),180  usage: jsonb().default({}), costUsd: doublePrecision("cost_usd"), latencyMs: integer("latency_ms"), ttftMs: integer("ttft_ms"), meta: jsonb().default({}), createdAt: created(),181}, (t) => [index("ai_msgs_conv_idx").on(t.conversationId, t.createdAt)]);182export const aiExecutions = pgTable("ai_executions", {183  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),184  agent: text().notNull(), task: text().notNull(), model: text(), status: text().notNull().default("pending"), // pending running awaiting_approval approved rejected completed failed185  scopes: jsonb().$type<string[]>().notNull().default([]), toolsInvoked: jsonb("tools_invoked").$type<{ tool: string; input: unknown; output?: unknown; at: string }[]>().default([]),186  documentsAccessed: jsonb("documents_accessed").$type<string[]>().default([]), proposedChanges: jsonb("proposed_changes").$type<unknown[]>().default([]),187  approvals: jsonb().$type<{ change: number; approved: boolean; at: string }[]>().default([]), outcome: text(), result: text(), error: text(),188  costUsd: doublePrecision("cost_usd").default(0), tokensIn: integer("tokens_in").default(0), tokensOut: integer("tokens_out").default(0),189  createdAt: created(), startedAt: ts("started_at"), finishedAt: ts("finished_at"),190}, (t) => [index("ai_exec_ws_idx").on(t.workspaceId, t.createdAt)]);191export const aiUsage = pgTable("ai_usage", {192  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id"), provider: text().notNull(), model: text().notNull(), task: text().notNull(),193  inputTokens: integer("input_tokens").notNull().default(0), outputTokens: integer("output_tokens").notNull().default(0), cachedTokens: integer("cached_tokens").default(0), reasoningTokens: integer("reasoning_tokens").default(0),194  costUsd: doublePrecision("cost_usd"), latencyMs: integer("latency_ms"), ttftMs: integer("ttft_ms"), ok: boolean().notNull().default(true), error: text(), refId: uuid("ref_id"), createdAt: created(),195}, (t) => [index("ai_usage_created_idx").on(t.createdAt)]);196export const aiComparisons = pgTable("ai_comparisons", {197  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),198  prompt: text().notNull(), models: jsonb().$type<string[]>().notNull(), results: jsonb().$type<unknown[]>().notNull().default([]), createdAt: created(),199});200201// ---------- research202export const researchReferences = pgTable("references", {203  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),204  pageId: uuid("page_id").references(() => pages.id, { onDelete: "set null" }), citeKey: text("cite_key").notNull(), entryType: text("entry_type").notNull().default("article"),205  title: text().notNull(), authors: jsonb().$type<string[]>().default([]), year: integer(), journal: text(), doi: text(), url: text(), bibtex: text(), fields: jsonb().default({}),206  verified: text().notNull().default("user"), // user | doi_verified | ai_suggested207  attachmentId: uuid("attachment_id").references(() => attachments.id, { onDelete: "set null" }), createdAt: created(), updatedAt: created(), deletedAt: ts("deleted_at"),208}, (t) => [uniqueIndex("references_key_unique").on(t.workspaceId, t.citeKey)]);209export const experiments = pgTable("experiments", {210  id: uuid().primaryKey().defaultRandom(), workspaceId: uuid("workspace_id").notNull().references(() => workspaces.id, { onDelete: "cascade" }),211  pageId: uuid("page_id").references(() => pages.id, { onDelete: "set null" }), name: text().notNull(), status: text().notNull().default("planned"),212  params: jsonb().default({}), metrics: jsonb().default({}), datasetRef: text("dataset_ref"), repoUrl: text("repo_url"), notes: text(), startedAt: ts("started_at"), finishedAt: ts("finished_at"), createdAt: created(), updatedAt: created(),213});214215// ---------- ops216export const auditLogs = pgTable("audit_logs", {217  id: uuid().primaryKey().defaultRandom(), userId: uuid("user_id"), action: text().notNull(), targetType: text("target_type"), targetId: text("target_id"), detail: jsonb().default({}), ip: text(), createdAt: created(),218}, (t) => [index("audit_created_idx").on(t.createdAt)]);219export const backupJobs = pgTable("backup_jobs", {220  id: uuid().primaryKey().defaultRandom(), kind: text().notNull().default("full"), status: text().notNull().default("running"), path: text(), sizeBytes: bigint("size_bytes", { mode: "number" }), sha256: text(), offsite: boolean().default(false), error: text(), startedAt: created(), finishedAt: ts("finished_at"),221});222export const dashboardLayouts = pgTable("dashboard_layouts", {223  id: uuid().primaryKey().defaultRandom(), userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }), name: text().notNull().default("Home"),224  widgets: jsonb().$type<{ id: string; type: string; size: string; config?: Record<string, unknown> }[]>().notNull().default([]), isDefault: boolean("is_default").notNull().default(true), updatedAt: created(),225});226export const settings = pgTable("settings", { key: text().primaryKey(), value: jsonb().notNull(), updatedAt: created() });227228export const pagesRelations = rel(pages, ({ many, one }) => ({ versions: many(pageVersions), tags: many(pageTags), workspace: one(workspaces, { fields: [pages.workspaceId], references: [workspaces.id] }) }));229export const databasesRelations = rel(databases, ({ many }) => ({ properties: many(databaseProperties), rows: many(databaseRows), views: many(databaseViews) }));230