spb/autoscholar

1import type { RunManifest } from "@autoscholar/schemas";2import {3  bigint,4  bigserial,5  boolean,6  index,7  integer,8  jsonb,9  numeric,10  pgTable,11  text,12  timestamp,13  uniqueIndex,14  uuid,15} from "drizzle-orm/pg-core";16import { adminUsers } from "./auth.js";17import { projectInstructionVersions, researchProjects } from "./projects.js";18import { computeWorkers } from "./workers.js";1920export const researchRuns = pgTable(21  "research_runs",22  {23    id: uuid("id").primaryKey().defaultRandom(),24    projectId: uuid("project_id")25      .notNull()26      .references(() => researchProjects.id),27    instructionVersionId: uuid("instruction_version_id")28      .notNull()29      .references(() => projectInstructionVersions.id),30    idempotencyKey: text("idempotency_key").notNull(),31    title: text("title").notNull(),32    status: text("status").notNull().default("draft"),33    researchMode: text("research_mode").notNull(),34    secondaryModes: text("secondary_modes").array().notNull().default([]),35    discipline: text("discipline").notNull(),36    computeProfile: text("compute_profile").notNull().default("standard"),37    provider: text("provider").notNull().default("anthropic"),38    model: text("model").notNull(),39    reviewerModel: text("reviewer_model").notNull(),40    maxBudgetUsd: numeric("max_budget_usd", { precision: 10, scale: 2 }).notNull(),41    maxRuntimeMinutes: integer("max_runtime_minutes").notNull(),42    maxAttempts: integer("max_attempts").notNull().default(2),43    autoPublish: boolean("auto_publish").notNull().default(false),44    qualityPolicyVersion: text("quality_policy_version").notNull(),45    manifest: jsonb("manifest").$type<RunManifest>().notNull(),46    manifestSha256: text("manifest_sha256").notNull(),47    currentAttemptNo: integer("current_attempt_no").notNull().default(0),48    currentStage: text("current_stage"),49    fencingToken: bigint("fencing_token", { mode: "number" }).notNull().default(0),50    lastEventSeq: bigint("last_event_seq", { mode: "number" }).notNull().default(0),51    cancelRequested: boolean("cancel_requested").notNull().default(false),52    failureReason: text("failure_reason"),53    totalCostUsd: numeric("total_cost_usd", { precision: 10, scale: 4 }).notNull().default("0"),54    paperId: uuid("paper_id"),55    queuedAt: timestamp("queued_at", { withTimezone: true }),56    startedAt: timestamp("started_at", { withTimezone: true }),57    finishedAt: timestamp("finished_at", { withTimezone: true }),58    createdBy: uuid("created_by").references(() => adminUsers.id),59    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),60    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),61  },62  (t) => [63    uniqueIndex("runs_idempotency_uq").on(t.idempotencyKey),64    index("runs_status_idx").on(t.status, t.queuedAt),65    index("runs_project_idx").on(t.projectId),66  ],67);6869export const runAttempts = pgTable(70  "run_attempts",71  {72    id: uuid("id").primaryKey().defaultRandom(),73    runId: uuid("run_id")74      .notNull()75      .references(() => researchRuns.id, { onDelete: "cascade" }),76    attemptNo: integer("attempt_no").notNull(),77    workerId: uuid("worker_id").references(() => computeWorkers.id),78    status: text("status").notNull().default("leased"), // leased | running | completed | failed | cancelled | expired | budget_exceeded | timeout79    fencingToken: bigint("fencing_token", { mode: "number" }).notNull(),80    resumeFromStage: text("resume_from_stage"),81    startedAt: timestamp("started_at", { withTimezone: true }).notNull().defaultNow(),82    endedAt: timestamp("ended_at", { withTimezone: true }),83    error: text("error"),84    costUsd: numeric("cost_usd", { precision: 10, scale: 4 }).notNull().default("0"),85    summary: jsonb("summary").$type<Record<string, unknown>>(),86  },87  (t) => [uniqueIndex("attempts_run_no_uq").on(t.runId, t.attemptNo), index("attempts_run_idx").on(t.runId)],88);8990export const stageExecutions = pgTable(91  "stage_executions",92  {93    id: uuid("id").primaryKey().defaultRandom(),94    runId: uuid("run_id")95      .notNull()96      .references(() => researchRuns.id, { onDelete: "cascade" }),97    attemptId: uuid("attempt_id")98      .notNull()99      .references(() => runAttempts.id, { onDelete: "cascade" }),100    stage: text("stage").notNull(),101    status: text("status").notNull().default("pending"),102    startedAt: timestamp("started_at", { withTimezone: true }).notNull().defaultNow(),103    endedAt: timestamp("ended_at", { withTimezone: true }),104    costUsd: numeric("cost_usd", { precision: 10, scale: 4 }).notNull().default("0"),105    turns: integer("turns").notNull().default(0),106    usage: jsonb("usage").$type<Record<string, unknown>>(),107    error: text("error"),108  },109  (t) => [110    uniqueIndex("stage_exec_attempt_stage_uq").on(t.attemptId, t.stage),111    index("stage_exec_run_idx").on(t.runId),112  ],113);114115export const stageEvents = pgTable(116  "stage_events",117  {118    id: bigserial("id", { mode: "number" }).primaryKey(),119    runId: uuid("run_id")120      .notNull()121      .references(() => researchRuns.id, { onDelete: "cascade" }),122    attemptId: uuid("attempt_id"),123    workerId: uuid("worker_id"),124    seq: bigint("seq", { mode: "number" }).notNull(),125    stage: text("stage"),126    level: text("level").notNull().default("info"),127    kind: text("kind").notNull(),128    message: text("message").notNull(),129    data: jsonb("data").$type<Record<string, unknown>>(),130    at: timestamp("at", { withTimezone: true }).notNull(),131    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),132  },133  (t) => [uniqueIndex("stage_events_run_seq_uq").on(t.runId, t.seq)],134);135136export const jobLeases = pgTable("job_leases", {137  runId: uuid("run_id")138    .primaryKey()139    .references(() => researchRuns.id, { onDelete: "cascade" }),140  attemptId: uuid("attempt_id").notNull(),141  workerId: uuid("worker_id")142    .notNull()143    .references(() => computeWorkers.id),144  fencingToken: bigint("fencing_token", { mode: "number" }).notNull(),145  acquiredAt: timestamp("acquired_at", { withTimezone: true }).notNull().defaultNow(),146  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),147  lastHeartbeatAt: timestamp("last_heartbeat_at", { withTimezone: true }).notNull().defaultNow(),148});149150export const checkpoints = pgTable(151  "checkpoints",152  {153    id: uuid("id").primaryKey().defaultRandom(),154    runId: uuid("run_id")155      .notNull()156      .references(() => researchRuns.id, { onDelete: "cascade" }),157    attemptId: uuid("attempt_id").notNull(),158    stage: text("stage").notNull(),159    data: jsonb("data").$type<Record<string, unknown>>().notNull().default({}),160    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),161  },162  (t) => [index("checkpoints_run_idx").on(t.runId, t.createdAt)],163);164