spb/worthdoing Public

1/**2 * WorthDoing.ai3 * Author: Simon-Pierre Boucher4 * Contact: contact@spboucher.ai5 * File: src/lib/db/schema.ts6 * Description: Drizzle PostgreSQL schema — investigations, hypotheses, evidence, opportunities, and the full agent data model.7 */8import {9  pgTable,10  uuid,11  text,12  timestamp,13  integer,14  real,15  boolean,16  jsonb,17  bigserial,18  uniqueIndex,19  index,20  primaryKey,21} from "drizzle-orm/pg-core";2223/* ----------------------------- users ----------------------------- */2425export const users = pgTable("users", {26  id: uuid("id").primaryKey().defaultRandom(),27  email: text("email").unique(),28  name: text("name"),29  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),30});3132/* ------------------------- investigations ------------------------ */3334export type BudgetLimits = {35  maxAgentSteps: number;36  maxSearches: number;37  maxScrapes: number;38  maxCrawls: number;39  maxWallTimeMs: number;40};4142export type BudgetUsed = {43  agentSteps: number;44  searches: number;45  scrapes: number;46  crawls: number;47  inputTokens: number;48  outputTokens: number;49  costUsd: number;50};5152export const investigations = pgTable(53  "investigations",54  {55    id: uuid("id").primaryKey().defaultRandom(),56    userId: uuid("user_id").references(() => users.id),57    objective: text("objective").notNull(),58    status: text("status", {59      enum: ["pending", "running", "completed", "failed", "cancelled"],60    })61      .notNull()62      .default("pending"),63    phase: text("phase", {64      enum: ["scouting", "investigating", "skeptic", "synthesizing", "done"],65    })66      .notNull()67      .default("scouting"),68    stopReason: text("stop_reason"), // structured: objective_met | budget_steps | budget_wall_time | agent_finished | error | cancelled69    conclusion: text("conclusion"),70    outcome: text("outcome", {71      enum: ["opportunities_found", "insufficient_evidence", "no_strong_opportunity"],72    }),73    budget: jsonb("budget").$type<BudgetLimits>().notNull(),74    budgetUsed: jsonb("budget_used").$type<BudgetUsed>().notNull(),75    promptVersion: text("prompt_version").notNull(),76    toolSchemaVersion: text("tool_schema_version").notNull(),77    model: text("model").notNull(),78    error: text("error"),79    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),80    startedAt: timestamp("started_at", { withTimezone: true }),81    completedAt: timestamp("completed_at", { withTimezone: true }),82  },83  (t) => [index("investigations_status_idx").on(t.status), index("investigations_created_idx").on(t.createdAt)],84);8586export const investigationSteps = pgTable(87  "investigation_steps",88  {89    id: uuid("id").primaryKey().defaultRandom(),90    investigationId: uuid("investigation_id")91      .notNull()92      .references(() => investigations.id, { onDelete: "cascade" }),93    stepNumber: integer("step_number").notNull(),94    model: text("model").notNull(),95    inputTokens: integer("input_tokens").notNull().default(0),96    outputTokens: integer("output_tokens").notNull().default(0),97    costUsd: real("cost_usd").notNull().default(0),98    latencyMs: integer("latency_ms").notNull().default(0),99    toolName: text("tool_name"),100    toolArgs: jsonb("tool_args"),101    toolResultSummary: text("tool_result_summary"),102    decisionSummary: text("decision_summary"),103    error: text("error"),104    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),105  },106  (t) => [index("steps_investigation_idx").on(t.investigationId, t.stepNumber)],107);108109/* -------------------------- agent events ------------------------- */110111export const agentEvents = pgTable(112  "agent_events",113  {114    id: bigserial("id", { mode: "number" }).primaryKey(),115    investigationId: uuid("investigation_id")116      .notNull()117      .references(() => investigations.id, { onDelete: "cascade" }),118    seq: integer("seq").notNull(),119    type: text("type").notNull(),120    payload: jsonb("payload").notNull(),121    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),122  },123  (t) => [uniqueIndex("agent_events_seq_idx").on(t.investigationId, t.seq)],124);125126/* ----------------------------- searches -------------------------- */127128export const searches = pgTable(129  "searches",130  {131    id: uuid("id").primaryKey().defaultRandom(),132    investigationId: uuid("investigation_id")133      .notNull()134      .references(() => investigations.id, { onDelete: "cascade" }),135    query: text("query").notNull(),136    intent: text("intent"), // explore | verify | falsify | market | technical137    provider: text("provider").notNull().default("firecrawl"),138    resultCount: integer("result_count").notNull().default(0),139    results: jsonb("results").notNull(), // [{url,title,description}]140    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),141  },142  (t) => [index("searches_investigation_idx").on(t.investigationId)],143);144145/* ------------------------ sources & contents --------------------- */146147export const sources = pgTable(148  "sources",149  {150    id: uuid("id").primaryKey().defaultRandom(),151    url: text("url").notNull(),152    canonicalUrl: text("canonical_url").notNull(),153    domain: text("domain").notNull(),154    title: text("title"),155    description: text("description"),156    firstSeenInvestigationId: uuid("first_seen_investigation_id").references(() => investigations.id),157    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),158  },159  (t) => [uniqueIndex("sources_canonical_idx").on(t.canonicalUrl), index("sources_domain_idx").on(t.domain)],160);161162export const sourceContents = pgTable(163  "source_contents",164  {165    id: uuid("id").primaryKey().defaultRandom(),166    sourceId: uuid("source_id")167      .notNull()168      .references(() => sources.id, { onDelete: "cascade" }),169    contentHash: text("content_hash").notNull(),170    markdown: text("markdown").notNull(),171    httpStatus: integer("http_status"),172    wordCount: integer("word_count").notNull().default(0),173    retrievedAt: timestamp("retrieved_at", { withTimezone: true }).notNull().defaultNow(),174  },175  (t) => [index("source_contents_source_idx").on(t.sourceId, t.retrievedAt), index("source_contents_hash_idx").on(t.contentHash)],176);177178/* ------------------------------ claims --------------------------- */179180export const claims = pgTable(181  "claims",182  {183    id: uuid("id").primaryKey().defaultRandom(),184    investigationId: uuid("investigation_id")185      .notNull()186      .references(() => investigations.id, { onDelete: "cascade" }),187    text: text("text").notNull(),188    status: text("status", { enum: ["open", "supported", "contradicted", "mixed"] })189      .notNull()190      .default("open"),191    confidence: real("confidence").notNull().default(0.5),192    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),193  },194  (t) => [index("claims_investigation_idx").on(t.investigationId)],195);196197/* ----------------------------- evidence -------------------------- */198199export const evidence = pgTable(200  "evidence",201  {202    id: uuid("id").primaryKey().defaultRandom(),203    investigationId: uuid("investigation_id")204      .notNull()205      .references(() => investigations.id, { onDelete: "cascade" }),206    sourceId: uuid("source_id")207      .notNull()208      .references(() => sources.id),209    claimId: uuid("claim_id").references(() => claims.id),210    kind: text("kind", { enum: ["support", "contradict", "context"] }).notNull(),211    quote: text("quote").notNull(), // exact or near-exact excerpt from the source212    summary: text("summary").notNull(), // agent's one-line interpretation213    strength: real("strength").notNull().default(0.5), // 0..1 evidential weight214    fingerprint: text("fingerprint").notNull(), // hash(sourceId + normalized quote) for dedup215    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),216  },217  (t) => [218    index("evidence_investigation_idx").on(t.investigationId),219    uniqueIndex("evidence_fingerprint_idx").on(t.investigationId, t.fingerprint),220  ],221);222223/* ---------------------------- hypotheses ------------------------- */224225export const hypotheses = pgTable(226  "hypotheses",227  {228    id: uuid("id").primaryKey().defaultRandom(),229    investigationId: uuid("investigation_id")230      .notNull()231      .references(() => investigations.id, { onDelete: "cascade" }),232    parentHypothesisId: uuid("parent_hypothesis_id"),233    title: text("title").notNull(),234    statement: text("statement").notNull(), // falsifiable statement235    status: text("status", {236      enum: ["proposed", "investigating", "supported", "weakened", "rejected", "validated"],237    })238      .notNull()239      .default("proposed"),240    confidence: real("confidence").notNull().default(0.5),241    rationale: text("rationale"),242    adversarialChecked: boolean("adversarial_checked").notNull().default(false),243    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),244    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),245  },246  (t) => [index("hypotheses_investigation_idx").on(t.investigationId)],247);248249export const hypothesisEvidence = pgTable(250  "hypothesis_evidence",251  {252    hypothesisId: uuid("hypothesis_id")253      .notNull()254      .references(() => hypotheses.id, { onDelete: "cascade" }),255    evidenceId: uuid("evidence_id")256      .notNull()257      .references(() => evidence.id, { onDelete: "cascade" }),258    relation: text("relation", { enum: ["supports", "contradicts"] }).notNull(),259    weight: real("weight").notNull().default(0.5),260  },261  (t) => [primaryKey({ columns: [t.hypothesisId, t.evidenceId] })],262);263264/* --------------------------- opportunities ----------------------- */265266export const opportunities = pgTable(267  "opportunities",268  {269    id: uuid("id").primaryKey().defaultRandom(),270    investigationId: uuid("investigation_id")271      .notNull()272      .references(() => investigations.id, { onDelete: "cascade" }),273    hypothesisId: uuid("hypothesis_id").references(() => hypotheses.id),274    title: text("title").notNull(),275    summary: text("summary").notNull(),276    problem: text("problem").notNull(),277    whyNow: text("why_now").notNull(),278    risks: text("risks").notNull(),279    skepticCase: text("skeptic_case").notNull(), // strongest argument against — mandatory280    reportMd: text("report_md"), // streamed synthesis report281    status: text("status", { enum: ["candidate", "validated", "rejected"] })282      .notNull()283      .default("candidate"),284    worthScore: real("worth_score"),285    evidenceConfidence: real("evidence_confidence"),286    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),287    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),288  },289  (t) => [index("opportunities_investigation_idx").on(t.investigationId), index("opportunities_score_idx").on(t.worthScore)],290);291292export const opportunityEvidence = pgTable(293  "opportunity_evidence",294  {295    opportunityId: uuid("opportunity_id")296      .notNull()297      .references(() => opportunities.id, { onDelete: "cascade" }),298    evidenceId: uuid("evidence_id")299      .notNull()300      .references(() => evidence.id, { onDelete: "cascade" }),301    role: text("role", { enum: ["demand", "neglect", "feasibility", "why_now", "risk", "competition", "context"] })302      .notNull()303      .default("context"),304  },305  (t) => [primaryKey({ columns: [t.opportunityId, t.evidenceId] })],306);307308export const opportunityScores = pgTable(309  "opportunity_scores",310  {311    id: uuid("id").primaryKey().defaultRandom(),312    opportunityId: uuid("opportunity_id")313      .notNull()314      .references(() => opportunities.id, { onDelete: "cascade" }),315    dimension: text("dimension", {316      enum: ["demand", "neglectedness", "feasibility", "why_now", "impact", "competition", "risk"],317    }).notNull(),318    score: real("score").notNull(), // 0..100319    confidence: real("confidence").notNull(), // 0..1, evidence-backed320    reasoning: text("reasoning").notNull(),321    evidenceIds: jsonb("evidence_ids").$type<string[]>().notNull(),322    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),323  },324  (t) => [uniqueIndex("opportunity_scores_dim_idx").on(t.opportunityId, t.dimension)],325);326327/* ---------------------------- competitors ------------------------ */328329export const competitors = pgTable(330  "competitors",331  {332    id: uuid("id").primaryKey().defaultRandom(),333    name: text("name").notNull(),334    url: text("url"),335    description: text("description"),336    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),337  },338  (t) => [uniqueIndex("competitors_name_idx").on(t.name)],339);340341export const opportunityCompetitors = pgTable(342  "opportunity_competitors",343  {344    opportunityId: uuid("opportunity_id")345      .notNull()346      .references(() => opportunities.id, { onDelete: "cascade" }),347    competitorId: uuid("competitor_id")348      .notNull()349      .references(() => competitors.id, { onDelete: "cascade" }),350    note: text("note"),351  },352  (t) => [primaryKey({ columns: [t.opportunityId, t.competitorId] })],353);354355/* ------------------------- saved opportunities ------------------- */356357export const savedOpportunities = pgTable(358  "saved_opportunities",359  {360    userId: uuid("user_id")361      .notNull()362      .references(() => users.id, { onDelete: "cascade" }),363    opportunityId: uuid("opportunity_id")364      .notNull()365      .references(() => opportunities.id, { onDelete: "cascade" }),366    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),367  },368  (t) => [primaryKey({ columns: [t.userId, t.opportunityId] })],369);370