spb/tools-spboucher-ai

1/** Drizzle schema mirroring drizzle/*.sql (the SQL files are the source of truth; drizzle-kit is not used to push). */2import { pgTable, pgView, uuid, text, boolean, timestamp, integer, smallint, bigserial, date, jsonb, customType, primaryKey, bigint } from "drizzle-orm/pg-core";34const tsvector = customType<{ data: string }>({ dataType() { return "tsvector"; } });5const vector = customType<{ data: number[]; driverData: string }>({ dataType() { return "vector(1536)"; }, toDriver(v) { return `[${v.join(",")}]`; } });67export const users = pgTable("users", {8  id: uuid().primaryKey().defaultRandom(), email: text().notNull().unique(), displayName: text().notNull(), passwordHash: text().notNull(),9  role: text().notNull().default("owner"), totpSecret: text(), totpEnabled: boolean().notNull().default(false), mustChangePassword: boolean().notNull().default(false),10  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow(),11});12export const sessions = pgTable("sessions", {13  id: uuid().primaryKey().defaultRandom(), userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }), tokenHash: text().notNull().unique(), csrfSecret: text().notNull(),14  mfaPassed: boolean().notNull().default(false), userAgent: text(), ip: text(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),15  lastSeenAt: timestamp({ withTimezone: true }).notNull().defaultNow(), rotatedAt: timestamp({ withTimezone: true }).notNull().defaultNow(),16  expiresAt: timestamp({ withTimezone: true }).notNull(), absoluteExpiresAt: timestamp({ withTimezone: true }).notNull(), revokedAt: timestamp({ withTimezone: true }),17});18export const loginAttempts = pgTable("login_attempts", { id: bigserial({ mode: "number" }).primaryKey(), ip: text().notNull(), email: text(), success: boolean().notNull(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow() });19export const webauthnCredentials = pgTable("webauthn_credentials", {20  id: text().primaryKey(), userId: uuid().notNull().references(() => users.id, { onDelete: "cascade" }), publicKey: text().notNull(), counter: bigint({ mode: "number" }).notNull().default(0),21  transports: text().array().notNull().default([]), deviceType: text(), backedUp: boolean().notNull().default(false), label: text().notNull().default("Passkey"),22  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), lastUsedAt: timestamp({ withTimezone: true }),23});24export const webauthnChallenges = pgTable("webauthn_challenges", { id: uuid().primaryKey().defaultRandom(), userId: uuid().references(() => users.id, { onDelete: "cascade" }), challenge: text().notNull(), kind: text().notNull(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow() });2526export const vendors = pgTable("vendors", { id: uuid().primaryKey().defaultRandom(), slug: text().notNull().unique(), name: text().notNull(), websiteUrl: text(), description: text(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow() });27export const categories = pgTable("categories", { id: uuid().primaryKey().defaultRandom(), slug: text().notNull().unique(), name: text().notNull(), description: text().notNull().default(""), parentId: uuid(), sortOrder: integer().notNull().default(0), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow() });28export const tags = pgTable("tags", { id: uuid().primaryKey().defaultRandom(), slug: text().notNull(), name: text().notNull(), kind: text().notNull().default("tag") });2930const toolColumns = {31  id: uuid().primaryKey().defaultRandom(), slug: text().notNull().unique(), name: text().notNull(), aliases: text().array().notNull().default([]), vendorId: uuid().references(() => vendors.id, { onDelete: "set null" }),32  homepageUrl: text().notNull(), docsUrl: text(), repoUrl: text(), pricingUrl: text(), licenseUrl: text(), privacyUrl: text(), securityUrl: text(), logoUrl: text(), logoAttribution: text(),33  productType: text().notNull(), productStatus: text().notNull().default("active"), summary: text().notNull(), description: text().notNull(),34  capabilities: text().array().notNull().default([]), workflows: text().array().notNull().default([]), advantages: text().array().notNull().default([]), limitations: text().array().notNull().default([]),35  prerequisites: text().array().notNull().default([]), audiences: text().array().notNull().default([]), comparisonNotes: text(),36  operatingSystems: text().array().notNull().default([]), executionModes: text().array().notNull().default([]), interfaces: text().array().notNull().default([]), deploymentModels: text().array().notNull().default([]),37  integrations: text().array().notNull().default([]), dataFormats: text().array().notNull().default([]), languages: text().array().notNull().default([]),38  sourceModel: text().notNull(), licenseSpdx: text(), pricingModel: text().array().notNull().default([]), pricingCheckedAt: date(), privacyPosture: text(), dataResidency: text(), selfHostable: boolean(),39  confidence: text().notNull().default("medium"), uncertaintyNote: text(), discoveryMethod: text().notNull().default("editorial-research"), depth: text().notNull().default("standard"),40  editorialStatus: text().notNull().default("draft"), visibility: text().notNull().default("public"), qualityScore: integer().notNull().default(0), duplicateOf: uuid(), linkState: text().notNull().default("unchecked"),41  featured: boolean().notNull().default(false), author: text().notNull().default("editorial"), reviewedAt: date(), reviewedBy: text(), checkedAt: timestamp({ withTimezone: true }), publishedAt: timestamp({ withTimezone: true }),42  contentHash: text(), embeddingText: text(), keywords: text().notNull().default(""), embedding: vector(), embeddingModel: text(),43  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow(), searchVector: tsvector(),44};45export const tools = pgTable("tools", toolColumns);46/** Read-only public projection (SQL view `public_tools`): no visibility/editorial/author/duplicate columns. */47const { visibility: _v, editorialStatus: _e, author: _a, duplicateOf: _d, discoveryMethod: _dm, reviewedBy: _rb, contentHash: _ch, embeddingText: _et, embeddingModel: _em, ...publicCols } = toolColumns;48export const publicTools = pgView("public_tools", publicCols).existing();4950export const toolCategories = pgTable("tool_categories", { toolId: uuid().notNull().references(() => tools.id, { onDelete: "cascade" }), categoryId: uuid().notNull().references(() => categories.id), isPrimary: boolean().notNull().default(false), label: text() }, (t) => [primaryKey({ columns: [t.toolId, t.categoryId] })]);51export const toolTags = pgTable("tool_tags", { toolId: uuid().notNull().references(() => tools.id, { onDelete: "cascade" }), tagId: uuid().notNull().references(() => tags.id, { onDelete: "cascade" }) }, (t) => [primaryKey({ columns: [t.toolId, t.tagId] })]);52export const toolAlternatives = pgTable("tool_alternatives", { toolId: uuid().notNull().references(() => tools.id, { onDelete: "cascade" }), alternativeId: uuid().notNull().references(() => tools.id, { onDelete: "cascade" }), note: text() }, (t) => [primaryKey({ columns: [t.toolId, t.alternativeId] })]);53export const sources = pgTable("sources", { id: uuid().primaryKey().defaultRandom(), url: text().notNull().unique(), canonicalUrl: text(), title: text(), sourceType: text().notNull(), httpStatus: integer(), finalUrl: text(), contentHash: text(), fetchedAt: timestamp({ withTimezone: true }), fetchError: text(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow() });54export const toolSources = pgTable("tool_sources", { toolId: uuid().notNull().references(() => tools.id, { onDelete: "cascade" }), sourceId: uuid().notNull().references(() => sources.id, { onDelete: "cascade" }), supports: text().array().notNull().default([]), checkedAt: date().notNull(), note: text() }, (t) => [primaryKey({ columns: [t.toolId, t.sourceId] })]);55export const collections = pgTable("collections", { id: uuid().primaryKey().defaultRandom(), slug: text().notNull().unique(), title: text().notNull(), description: text().notNull(), published: boolean().notNull().default(false), sortOrder: integer().notNull().default(0), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow() });56export const publicCollections = pgView("public_collections", { id: uuid(), slug: text(), title: text(), description: text(), sortOrder: integer(), updatedAt: timestamp({ withTimezone: true }) }).existing();57export const collectionItems = pgTable("collection_items", { collectionId: uuid().notNull().references(() => collections.id, { onDelete: "cascade" }), toolId: uuid().notNull().references(() => tools.id, { onDelete: "cascade" }), position: integer().notNull().default(0), blurb: text() }, (t) => [primaryKey({ columns: [t.collectionId, t.toolId] })]);5859export const projects = pgTable("projects", { id: uuid().primaryKey().defaultRandom(), slug: text().notNull().unique(), name: text().notNull(), description: text(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow() });60export const ownerNotes = pgTable("owner_notes", {61  toolId: uuid().primaryKey().references(() => tools.id, { onDelete: "cascade" }), useState: text().notNull().default("candidate"), rating: smallint(), notes: text(), clusterCompat: text().notNull().default("unknown"),62  priority: text().notNull().default("medium"), subscriptionStatus: text(), deploymentUrls: jsonb().$type<{ label: string; url: string }[]>().notNull().default([]), customTags: text().array().notNull().default([]), reviewAt: date(),63  createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow(),64});65export const projectTools = pgTable("project_tools", { projectId: uuid().notNull().references(() => projects.id, { onDelete: "cascade" }), toolId: uuid().notNull().references(() => tools.id, { onDelete: "cascade" }), role: text() }, (t) => [primaryKey({ columns: [t.projectId, t.toolId] })]);6667export const toolRevisions = pgTable("tool_revisions", { id: bigserial({ mode: "number" }).primaryKey(), toolId: uuid().notNull(), actor: text().notNull(), reason: text(), snapshot: jsonb().notNull(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow() });68export const auditLog = pgTable("audit_log", { id: bigserial({ mode: "number" }).primaryKey(), actorId: uuid(), actor: text().notNull(), action: text().notNull(), entityType: text(), entityId: text(), details: jsonb().notNull().default({}), ip: text(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow() });69export const jobs = pgTable("jobs", {70  id: bigserial({ mode: "number" }).primaryKey(), kind: text().notNull(), payload: jsonb().$type<Record<string, unknown>>().notNull().default({}), dedupeKey: text().unique(), status: text().notNull().default("queued"), priority: integer().notNull().default(5),71  attempts: integer().notNull().default(0), maxAttempts: integer().notNull().default(5), runAt: timestamp({ withTimezone: true }).notNull().defaultNow(), lockedAt: timestamp({ withTimezone: true }), lockedBy: text(),72  startedAt: timestamp({ withTimezone: true }), finishedAt: timestamp({ withTimezone: true }), lastError: text(), result: jsonb(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow(),73});74export const linkChecks = pgTable("link_checks", { id: bigserial({ mode: "number" }).primaryKey(), toolId: uuid(), field: text().notNull(), url: text().notNull(), ok: boolean().notNull(), statusCode: integer(), finalUrl: text(), redirected: boolean().notNull().default(false), error: text(), durationMs: integer(), checkedAt: timestamp({ withTimezone: true }).notNull().defaultNow() });75export const publicLinkChecks = pgView("public_link_checks", { toolId: uuid(), field: text(), url: text(), ok: boolean(), statusCode: integer(), redirected: boolean(), checkedAt: timestamp({ withTimezone: true }) }).existing();76export const errorReports = pgTable("error_reports", { id: uuid().primaryKey().defaultRandom(), toolId: uuid(), toolSlug: text(), message: text().notNull(), contact: text(), ipHash: text(), status: text().notNull().default("open"), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow() });77export const searchEvents = pgTable("search_events", { id: bigserial({ mode: "number" }).primaryKey(), query: text().notNull(), resultCount: integer().notNull(), mode: text().notNull(), durationMs: integer(), createdAt: timestamp({ withTimezone: true }).notNull().defaultNow() });78export const settings = pgTable("settings", { key: text().primaryKey(), value: jsonb().notNull(), updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow() });7980export type Tool = typeof tools.$inferSelect;81export type PublicTool = typeof publicTools.$inferSelect;82export type NewTool = typeof tools.$inferInsert;83