spb/autoscholar

1import { type Db, projectInstructionVersions, researchProjects } from "@autoscholar/db";2import { validateInstruction } from "@autoscholar/research-contract";3import { INSTRUCTION_MAX_BYTES } from "@autoscholar/schemas";4import { desc, eq, sql } from "drizzle-orm";5import { audit } from "./audit.js";6import { invalid, notFound } from "./errors.js";78export async function createProject(9  db: Db,10  input: { title: string; discipline: string; description: string },11  actor: { id: string; ip?: string | null },12) {13  const [p] = await db14    .insert(researchProjects)15    .values({ ...input, createdBy: actor.id })16    .returning();17  await audit(18    db,19    { type: "admin", id: actor.id, ip: actor.ip },20    "project.create",21    { type: "project", id: p!.id },22    { title: input.title },23  );24  return p!;25}2627export async function listProjects(db: Db) {28  const rows = await db29    .select({30      project: researchProjects,31      versions: sql<number>`(select count(*)::int from project_instruction_versions v where v.project_id = ${researchProjects.id})`,32      runs: sql<number>`(select count(*)::int from research_runs r where r.project_id = ${researchProjects.id})`,33    })34    .from(researchProjects)35    .orderBy(desc(researchProjects.createdAt));36  return rows;37}3839export async function getProject(db: Db, id: string) {40  const [p] = await db.select().from(researchProjects).where(eq(researchProjects.id, id)).limit(1);41  if (!p) throw notFound("project");42  const versions = await db43    .select({44      id: projectInstructionVersions.id,45      version: projectInstructionVersions.version,46      filename: projectInstructionVersions.filename,47      sha256: projectInstructionVersions.sha256,48      byteSize: projectInstructionVersions.byteSize,49      note: projectInstructionVersions.note,50      validation: projectInstructionVersions.validation,51      frontmatter: projectInstructionVersions.frontmatter,52      createdAt: projectInstructionVersions.createdAt,53    })54    .from(projectInstructionVersions)55    .where(eq(projectInstructionVersions.projectId, id))56    .orderBy(desc(projectInstructionVersions.version));57  return { project: p, versions };58}5960/** Stores the exact original bytes + SHA-256 as a new immutable version. Identical content is deduplicated. */61export async function uploadInstruction(62  db: Db,63  projectId: string,64  bytes: Buffer,65  filename: string,66  note: string | undefined,67  actor: { id: string; ip?: string | null },68) {69  if (bytes.length > INSTRUCTION_MAX_BYTES)70    throw invalid(`Instruction exceeds ${INSTRUCTION_MAX_BYTES} bytes`);71  const [p] = await db72    .select({ id: researchProjects.id })73    .from(researchProjects)74    .where(eq(researchProjects.id, projectId))75    .limit(1);76  if (!p) throw notFound("project");77  const v = validateInstruction(bytes, filename);78  if (!v.ok)79    throw invalid(`Instruction rejected: ${v.errors.join("; ")}`, { errors: v.errors, warnings: v.warnings });80  const result = await db.transaction(async (tx) => {81    const [existing] = await tx82      .select({ id: projectInstructionVersions.id, version: projectInstructionVersions.version })83      .from(projectInstructionVersions)84      .where(85        sql`${projectInstructionVersions.projectId} = ${projectId} and ${projectInstructionVersions.sha256} = ${v.sha256}`,86      )87      .limit(1);88    if (existing) return { id: existing.id, version: existing.version, deduplicated: true };89    await tx.execute(sql`select pg_advisory_xact_lock(hashtext(${projectId}))`);90    const [last] = await tx91      .select({ v: sql<number>`coalesce(max(${projectInstructionVersions.version}), 0)::int` })92      .from(projectInstructionVersions)93      .where(eq(projectInstructionVersions.projectId, projectId));94    const version = (last?.v ?? 0) + 1;95    const [row] = await tx96      .insert(projectInstructionVersions)97      .values({98        projectId,99        version,100        filename: filename.replace(/[^A-Za-z0-9._-]/g, "_").slice(0, 120) || "CLAUDE.md",101        sha256: v.sha256,102        byteSize: v.byteSize,103        contentText: v.text,104        originalBytes: bytes,105        frontmatter: v.frontmatter,106        validation: { warnings: v.warnings, headings: v.headings, sections: v.sections },107        note: note ?? null,108        createdBy: actor.id,109      })110      .returning({ id: projectInstructionVersions.id, version: projectInstructionVersions.version });111    await tx112      .update(researchProjects)113      .set({ updatedAt: new Date() })114      .where(eq(researchProjects.id, projectId));115    await audit(116      tx,117      { type: "admin", id: actor.id, ip: actor.ip },118      "instruction.upload",119      { type: "instruction_version", id: row!.id },120      { projectId, version, sha256: v.sha256, bytes: v.byteSize },121    );122    return { id: row!.id, version: row!.version, deduplicated: false };123  });124  return { ...result, validation: v };125}126127export async function getInstructionVersion(db: Db, id: string) {128  const [row] = await db129    .select()130    .from(projectInstructionVersions)131    .where(eq(projectInstructionVersions.id, id))132    .limit(1);133  if (!row) throw notFound("instruction version");134  return row;135}136