spb/note-spboucher-ai

1/** Working-paper tracker: every research page with status, counts and last activity. */2import { sql as dsql } from "drizzle-orm";3import { db, schema } from "@/db";4export const PAPER_STATUSES = ["planning", "research", "development", "writing", "submitted", "published"] as const;5export type PaperStatus = (typeof PAPER_STATUSES)[number];6export interface PaperMeta { status?: PaperStatus; venue?: string; deadline?: string; repoUrl?: string; datasets?: string[]; tags?: string[] }7export interface PaperOverview { id: string; title: string; icon: string | null; status: PaperStatus | null; venue: string | null; deadline: string | null; repoUrl: string | null; datasets: string[]; wordCount: number; references: number; experiments: number; experimentsRunning: number; openTasks: number; overdueTasks: number; attachments: number; updatedAt: string; lastActivity: string; createdAt: string }89export async function researchOverview(wsId: string): Promise<PaperOverview[]> {10  const rows = await db.execute(dsql`11    select p.id, p.title, p.icon, p.properties, p.word_count, p.updated_at, p.created_at,12      (select count(*) from "references" r where r.page_id = p.id and r.deleted_at is null) as refs,13      (select max(r.updated_at) from "references" r where r.page_id = p.id and r.deleted_at is null) as refs_at,14      (select count(*) from experiments e where e.page_id = p.id) as exps,15      (select count(*) from experiments e where e.page_id = p.id and e.status = 'running') as exps_running,16      (select max(e.updated_at) from experiments e where e.page_id = p.id) as exps_at,17      (select count(*) from tasks t where t.page_id = p.id and t.deleted_at is null and t.status <> 'done') as open_tasks,18      (select count(*) from tasks t where t.page_id = p.id and t.deleted_at is null and t.status <> 'done' and t.due_at < now()) as overdue,19      (select max(t.updated_at) from tasks t where t.page_id = p.id and t.deleted_at is null) as tasks_at,20      (select count(*) from attachments a where a.page_id = p.id and a.deleted_at is null) as files21    from pages p where p.workspace_id = ${wsId} and p.type = 'research' and p.deleted_at is null and p.database_id is null22    order by p.updated_at desc`);23  return (rows as any[]).map((r) => {24    const props = (r.properties ?? {}) as PaperMeta; const last = [r.updated_at, r.refs_at, r.exps_at, r.tasks_at].filter(Boolean).map((d) => new Date(d).getTime());25    return { id: r.id, title: r.title, icon: r.icon, status: PAPER_STATUSES.includes(props.status as any) ? (props.status as PaperStatus) : null, venue: props.venue ?? null, deadline: props.deadline ?? null, repoUrl: props.repoUrl ?? null, datasets: Array.isArray(props.datasets) ? props.datasets : [], wordCount: Number(r.word_count), references: Number(r.refs), experiments: Number(r.exps), experimentsRunning: Number(r.exps_running), openTasks: Number(r.open_tasks), overdueTasks: Number(r.overdue), attachments: Number(r.files), updatedAt: new Date(r.updated_at).toISOString(), lastActivity: new Date(Math.max(...last)).toISOString(), createdAt: new Date(r.created_at).toISOString() };26  });27}28