spb/polyllm

1import "server-only";2import { and, asc, desc, eq, inArray, isNull, sql } from "drizzle-orm";3import { getDb, projects, projectFiles, prompts, conversations, type Project } from "@/db";4import { newId } from "@/lib/ids";5import { ApiError } from "@/lib/api";6import { listConversations } from "@/lib/conversations/service";7import { toPublicFile } from "@/lib/library/service";8import { toPublicPrompt } from "@/lib/prompts/service";910/** Public shape (dates as ISO strings, counts for cards). */11export function toPublicProject(p: Project, counts?: { conversationCount?: number; fileCount?: number; promptCount?: number }) {12  return {13    id: p.id,14    name: p.name,15    description: p.description,16    icon: p.icon,17    color: p.color,18    instructions: p.instructions,19    preferredModelKeys: p.preferredModelKeys ?? [],20    defaultSettings: p.defaultSettings ?? {},21    notes: p.notes,22    archived: p.archived,23    sortOrder: p.sortOrder,24    conversationCount: counts?.conversationCount ?? 0,25    fileCount: counts?.fileCount ?? 0,26    promptCount: counts?.promptCount ?? 0,27    createdAt: p.createdAt.toISOString(),28    updatedAt: p.updatedAt.toISOString(),29  };30}31export type PublicProject = ReturnType<typeof toPublicProject>;3233export interface ProjectInput {34  name: string;35  description?: string | null;36  icon?: string | null;37  color?: string | null;38  instructions?: string | null;39  preferredModelKeys?: string[];40  defaultSettings?: Record<string, unknown>;41  notes?: string | null;42  archived?: boolean;43  sortOrder?: number;44}4546async function countsFor(userId: string) {47  const db = getDb();48  const [convs, files, prms] = await Promise.all([49    db50      .select({ projectId: conversations.projectId, n: sql<number>`count(*)::int` })51      .from(conversations)52      .where(and(eq(conversations.userId, userId), sql`${conversations.projectId} is not null`))53      .groupBy(conversations.projectId),54    db55      .select({ projectId: projectFiles.projectId, n: sql<number>`count(*)::int` })56      .from(projectFiles)57      .where(and(eq(projectFiles.userId, userId), sql`${projectFiles.projectId} is not null`))58      .groupBy(projectFiles.projectId),59    db60      .select({ projectId: prompts.projectId, n: sql<number>`count(*)::int` })61      .from(prompts)62      .where(and(eq(prompts.userId, userId), sql`${prompts.projectId} is not null`))63      .groupBy(prompts.projectId),64  ]);65  const map = new Map<string, { conversationCount: number; fileCount: number; promptCount: number }>();66  const get = (id: string) => {67    let v = map.get(id);68    if (!v) {69      v = { conversationCount: 0, fileCount: 0, promptCount: 0 };70      map.set(id, v);71    }72    return v;73  };74  for (const r of convs) if (r.projectId) get(r.projectId).conversationCount = r.n;75  for (const r of files) if (r.projectId) get(r.projectId).fileCount = r.n;76  for (const r of prms) if (r.projectId) get(r.projectId).promptCount = r.n;77  return map;78}7980export async function listProjects(userId: string, opts: { includeArchived?: boolean } = {}) {81  const conds = [eq(projects.userId, userId)];82  if (!opts.includeArchived) conds.push(eq(projects.archived, false));83  const [rows, counts] = await Promise.all([84    getDb()85      .select()86      .from(projects)87      .where(and(...conds))88      .orderBy(asc(projects.sortOrder), desc(projects.updatedAt)),89    countsFor(userId),90  ]);91  return rows.map((p) => toPublicProject(p, counts.get(p.id)));92}9394export async function getProject(userId: string, id: string): Promise<Project> {95  const [p] = await getDb()96    .select()97    .from(projects)98    .where(and(eq(projects.id, id), eq(projects.userId, userId)))99    .limit(1);100  if (!p) throw new ApiError(404, "Project not found", "NOT_FOUND");101  return p;102}103104/** Full detail for the project page: project, its conversations, files, prompts and aggregate stats. */105export async function getProjectDetail(userId: string, id: string) {106  const db = getDb();107  const p = await getProject(userId, id);108  const [convs, files, prms, [stats]] = await Promise.all([109    listConversations(userId, { projectId: id, limit: 200 }),110    db.select().from(projectFiles).where(and(eq(projectFiles.userId, userId), eq(projectFiles.projectId, id))).orderBy(desc(projectFiles.createdAt)),111    db.select().from(prompts).where(and(eq(prompts.userId, userId), eq(prompts.projectId, id))).orderBy(desc(prompts.favorite), desc(prompts.updatedAt)),112    db113      .select({114        conversations: sql<number>`count(*)::int`,115        messages: sql<number>`coalesce(sum(${conversations.messageCount}), 0)::int`,116        totalCostUsd: sql<number>`coalesce(sum(${conversations.totalCostUsd}), 0)::float8`,117        totalInputTokens: sql<number>`coalesce(sum(${conversations.totalInputTokens}), 0)::int`,118        totalOutputTokens: sql<number>`coalesce(sum(${conversations.totalOutputTokens}), 0)::int`,119        lastActivityAt: sql<Date | null>`max(${conversations.updatedAt})`,120      })121      .from(conversations)122      .where(and(eq(conversations.userId, userId), eq(conversations.projectId, id))),123  ]);124  const publicFiles = files.map(toPublicFile);125  const last = stats?.lastActivityAt ? new Date(stats.lastActivityAt) : null;126  return {127    project: toPublicProject(p, { conversationCount: stats?.conversations ?? 0, fileCount: files.length, promptCount: prms.length }),128    conversations: convs.conversations,129    files: publicFiles,130    prompts: prms.map(toPublicPrompt),131    stats: {132      conversations: stats?.conversations ?? 0,133      messages: stats?.messages ?? 0,134      files: files.length,135      prompts: prms.length,136      totalCostUsd: stats?.totalCostUsd ?? 0,137      totalInputTokens: stats?.totalInputTokens ?? 0,138      totalOutputTokens: stats?.totalOutputTokens ?? 0,139      fileBytes: files.reduce((n, f) => n + f.sizeBytes, 0),140      fileTokens: files.reduce((n, f) => n + (f.estimatedTokens ?? 0), 0),141      lastActivityAt: last && !Number.isNaN(last.getTime()) ? last.toISOString() : null,142    },143  };144}145export type ProjectDetail = Awaited<ReturnType<typeof getProjectDetail>>;146147function clean(input: Partial<ProjectInput>): Partial<typeof projects.$inferInsert> {148  const set: Partial<typeof projects.$inferInsert> = {};149  if (input.name !== undefined) set.name = input.name.trim().slice(0, 80) || "Untitled project";150  if (input.description !== undefined) set.description = input.description?.trim() || null;151  if (input.icon !== undefined) set.icon = input.icon?.trim().slice(0, 8) || null;152  if (input.color !== undefined) set.color = input.color?.trim().slice(0, 24) || null;153  if (input.instructions !== undefined) set.instructions = input.instructions?.trim() ? input.instructions : null;154  if (input.preferredModelKeys !== undefined) set.preferredModelKeys = Array.from(new Set(input.preferredModelKeys.map((k) => k.trim()).filter(Boolean))).slice(0, 8);155  if (input.defaultSettings !== undefined) set.defaultSettings = input.defaultSettings;156  if (input.notes !== undefined) set.notes = input.notes?.trim() ? input.notes : null;157  if (input.archived !== undefined) set.archived = input.archived;158  if (input.sortOrder !== undefined) set.sortOrder = input.sortOrder;159  return set;160}161162export async function createProject(userId: string, input: ProjectInput) {163  const [maxRow] = await getDb()164    .select({ max: sql<number>`coalesce(max(${projects.sortOrder}), -1)::int` })165    .from(projects)166    .where(eq(projects.userId, userId));167  const [p] = await getDb()168    .insert(projects)169    .values({ id: newId("prj"), userId, ...clean({ ...input, sortOrder: input.sortOrder ?? (maxRow?.max ?? -1) + 1 }), name: input.name.trim().slice(0, 80) || "Untitled project" })170    .returning();171  return toPublicProject(p);172}173174export async function updateProject(userId: string, id: string, patch: Partial<ProjectInput>) {175  const [p] = await getDb()176    .update(projects)177    .set({ ...clean(patch), updatedAt: new Date() })178    .where(and(eq(projects.id, id), eq(projects.userId, userId)))179    .returning();180  if (!p) throw new ApiError(404, "Project not found", "NOT_FOUND");181  const counts = await countsFor(userId);182  return toPublicProject(p, counts.get(p.id));183}184185/** Deletes the project. Conversations are kept (project_id → null); project files cascade; prompts are detached. */186export async function deleteProject(userId: string, id: string) {187  const db = getDb();188  await getProject(userId, id);189  await db.update(conversations).set({ projectId: null }).where(and(eq(conversations.userId, userId), eq(conversations.projectId, id)));190  await db.delete(projects).where(and(eq(projects.id, id), eq(projects.userId, userId)));191}192193/** Move a set of conversations into (or out of, with `null`) a project. Only the caller's conversations are touched. */194export async function moveConversations(userId: string, conversationIds: string[], projectId: string | null) {195  if (!conversationIds.length) return 0;196  if (projectId) await getProject(userId, projectId);197  const res = await getDb()198    .update(conversations)199    .set({ projectId, updatedAt: new Date() })200    .where(and(eq(conversations.userId, userId), inArray(conversations.id, conversationIds)))201    .returning({ id: conversations.id });202  return res.length;203}204205/** Conversations not attached to any project (for the "Add existing chats" picker). */206export async function listUnassignedConversations(userId: string, limit = 100) {207  const rows = await getDb()208    .select({ id: conversations.id, title: conversations.title, modelKey: conversations.modelKey, updatedAt: conversations.updatedAt, messageCount: conversations.messageCount })209    .from(conversations)210    .where(and(eq(conversations.userId, userId), isNull(conversations.projectId), eq(conversations.archived, false)))211    .orderBy(desc(conversations.updatedAt))212    .limit(limit);213  return rows.map((r) => ({ ...r, updatedAt: r.updatedAt.toISOString() }));214}215