spb/autoscholar

1import {2  customType,3  index,4  integer,5  jsonb,6  pgTable,7  text,8  timestamp,9  uniqueIndex,10  uuid,11} from "drizzle-orm/pg-core";12import { adminUsers } from "./auth.js";1314export const bytea = customType<{ data: Buffer; driverData: Buffer }>({15  dataType() {16    return "bytea";17  },18});1920export const researchProjects = pgTable("research_projects", {21  id: uuid("id").primaryKey().defaultRandom(),22  title: text("title").notNull(),23  discipline: text("discipline").notNull(),24  description: text("description").notNull().default(""),25  createdBy: uuid("created_by").references(() => adminUsers.id),26  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),27  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),28  archivedAt: timestamp("archived_at", { withTimezone: true }),29});3031/** Immutable, versioned research instruction (CLAUDE.md) uploads. Original bytes retained. */32export const projectInstructionVersions = pgTable(33  "project_instruction_versions",34  {35    id: uuid("id").primaryKey().defaultRandom(),36    projectId: uuid("project_id")37      .notNull()38      .references(() => researchProjects.id, { onDelete: "cascade" }),39    version: integer("version").notNull(),40    filename: text("filename").notNull(),41    sha256: text("sha256").notNull(),42    byteSize: integer("byte_size").notNull(),43    contentText: text("content_text").notNull(),44    originalBytes: bytea("original_bytes").notNull(),45    frontmatter: jsonb("frontmatter").$type<Record<string, unknown> | null>(),46    validation: jsonb("validation").$type<Record<string, unknown>>().notNull().default({}),47    note: text("note"),48    createdBy: uuid("created_by").references(() => adminUsers.id),49    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),50  },51  (t) => [52    uniqueIndex("piv_project_version_uq").on(t.projectId, t.version),53    uniqueIndex("piv_project_sha_uq").on(t.projectId, t.sha256),54    index("piv_project_idx").on(t.projectId),55  ],56);5758export const researchTemplates = pgTable("research_templates", {59  id: uuid("id").primaryKey().defaultRandom(),60  slug: text("slug").notNull().unique(),61  name: text("name").notNull(),62  mode: text("mode").notNull(),63  description: text("description").notNull().default(""),64  content: text("content").notNull(),65  version: integer("version").notNull().default(1),66  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),67  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),68});69