Repository Public
1/** Small admin services: preview links, dashboard figures, audit listing, conversation, custom templates, topics. */2import { j, sql } from "@/lib/db";3import { ApiError } from "@/lib/http";4import { randomToken, sha256 } from "@/lib/crypto";5import { env } from "@/lib/env";6import { mustParseSheet, type Locale, type Sheet } from "@/schema";7import { aiHealth, spend } from "@/ai/client";8import { scannerStatus } from "./assets";9import { queueHealth } from "./jobs";10import { getSettings } from "./settings";11import { storage } from "./storage";1213// ── Private preview ─────────────────────────────────────────────────────────────────────────────────1415/** A link that shows the working copy to whoever holds it, for a limited time. Stored hashed; revocable. */16export async function createPreviewLink(sheetId: string, userId: string, hours = 24): Promise<{ url: string; expiresAt: string }> {17 const token = randomToken(32);18 const [r] = await sql<{ expires_at: Date }[]>`INSERT INTO preview_tokens (sheet_id, token_hash, expires_at, created_by) VALUES (${sheetId}, ${sha256(token)}, now() + ${hours} * interval '1 hour', ${userId}) RETURNING expires_at`;19 return { url: `${env.appUrl}/preview/${token}`, expiresAt: r.expires_at.toISOString() };20}21export async function revokePreviewLinks(sheetId: string): Promise<void> { await sql`UPDATE preview_tokens SET revoked_at = now() WHERE sheet_id = ${sheetId} AND revoked_at IS NULL`; }22export async function sheetFromPreviewToken(token: string): Promise<{ doc: Sheet; title: string } | null> {23 if (!/^[A-Za-z0-9_-]{20,80}$/.test(token)) return null;24 const [r] = await sql<{ doc: unknown; title: string }[]>`25 SELECT s.doc, s.title FROM preview_tokens t JOIN sheets s ON s.id = t.sheet_id26 WHERE t.token_hash = ${sha256(token)} AND t.revoked_at IS NULL AND t.expires_at > now() AND s.deleted_at IS NULL`;27 return r ? { doc: mustParseSheet(r.doc), title: r.title } : null;28}2930// ── Dashboard ───────────────────────────────────────────────────────────────────────────────────────3132export async function dashboard() {33 const [c] = await sql<{ drafts: number; in_review: number; published: number; archived: number; revisions: number; topics: number; assets: number }[]>`34 SELECT count(*) FILTER (WHERE status = 'draft')::int AS drafts, count(*) FILTER (WHERE status = 'in_review')::int AS in_review,35 count(*) FILTER (WHERE status = 'published')::int AS published, count(*) FILTER (WHERE status = 'archived')::int AS archived,36 (SELECT count(*)::int FROM sheet_revisions r JOIN sheets s2 ON s2.id = r.sheet_id WHERE s2.deleted_at IS NULL) AS revisions,37 (SELECT count(*)::int FROM topics) AS topics, (SELECT count(*)::int FROM assets WHERE deleted_at IS NULL AND kind <> 'export') AS assets38 FROM sheets WHERE deleted_at IS NULL`;39 const settings = await getSettings();40 const [v] = settings.analyticsEnabled41 ? await sql<{ d7: number; d30: number }[]>`SELECT coalesce(sum(views) FILTER (WHERE day > current_date - 7), 0)::int AS d7, coalesce(sum(views) FILTER (WHERE day > current_date - 30), 0)::int AS d30 FROM sheet_views`42 : [null];43 const top = settings.analyticsEnabled44 ? await sql<{ id: string; title: string; views: number }[]>`SELECT s.id, s.title, sum(w.views)::int AS views FROM sheet_views w JOIN sheets s ON s.id = w.sheet_id WHERE w.day > current_date - 30 AND s.deleted_at IS NULL GROUP BY s.id, s.title ORDER BY views DESC LIMIT 5`45 : [];46 let db = { ok: true, detail: "" };47 try { const [r] = await sql<{ v: string; size: string }[]>`SELECT current_setting('server_version') AS v, pg_size_pretty(pg_database_size(current_database())) AS size`; db = { ok: true, detail: `PostgreSQL ${r.v}, ${r.size}` }; } catch (e) { db = { ok: false, detail: (e as Error).message }; }48 const [ai, queue, store, scanner, cost] = await Promise.all([aiHealth(), queueHealth(), storage().health(), scannerStatus(), spend()]);49 return {50 counts: { drafts: c.drafts, inReview: c.in_review, published: c.published, archived: c.archived, revisions: c.revisions, topics: c.topics, assets: c.assets },51 views: v ? { last7Days: v.d7, last30Days: v.d30, top: [...top] } : null,52 health: { database: db, ai, queue, storage: store, scanner },53 spend: { todayUsd: cost.today, monthUsd: cost.month, dailyBudgetUsd: settings.aiBudgetDailyUsd, monthlyBudgetUsd: settings.aiBudgetMonthlyUsd },54 };55}5657export async function listAudit(q: { limit: number; offset: number; action?: string }) {58 const rows = await sql<{ id: string; at: Date; actor: string; action: string; entity: string; entity_id: string; meta: unknown; ip: string | null; total: number }[]>`59 SELECT id, at, actor, action, entity, entity_id, meta, ip, count(*) OVER ()::int AS total FROM audit_events60 WHERE (${q.action ?? null}::text IS NULL OR action LIKE ${(q.action ?? "") + "%"}) ORDER BY at DESC, id DESC LIMIT ${q.limit} OFFSET ${q.offset}`;61 return { total: rows[0]?.total ?? 0, items: rows.map((r) => ({ id: String(r.id), at: r.at.toISOString(), actor: r.actor, action: r.action, entity: r.entity, entityId: r.entity_id, meta: r.meta, ip: r.ip })) };62}6364export async function conversation(sheetId: string) {65 const rows = await sql<{ id: string; role: string; content: string; job_id: string | null; revision_id: string | null; diff: unknown; created_at: Date; number: number | null }[]>`66 SELECT m.id, m.role, m.content, m.job_id, m.revision_id, m.diff, m.created_at, r.number67 FROM ai_messages m JOIN ai_conversations c ON c.id = m.conversation_id LEFT JOIN sheet_revisions r ON r.id = m.revision_id68 WHERE c.sheet_id = ${sheetId} ORDER BY m.created_at, m.role DESC LIMIT 300`;69 return rows.map((m) => ({ id: m.id, role: m.role, content: m.content, jobId: m.job_id, revisionId: m.revision_id, revisionNumber: m.number, diff: m.diff, createdAt: m.created_at.toISOString() }));70}7172// ── The author's own templates ──────────────────────────────────────────────────────────────────────7374export async function listCustomTemplates() {75 const rows = await sql<{ id: string; key: string; title: string; description: string; locale: Locale; category: string; created_at: Date }[]>`SELECT id, key, title, description, locale, category, created_at FROM templates WHERE deleted_at IS NULL ORDER BY created_at DESC`;76 return rows.map((t) => ({ id: t.id, key: t.key, title: t.title, description: t.description, locale: t.locale, category: t.category, createdAt: t.created_at.toISOString() }));77}78export async function saveAsTemplate(input: { doc: Sheet; title: string; description: string; userId: string }) {79 const key = `custom-${randomToken(6).toLowerCase().replace(/[^a-z0-9]/g, "x")}`;80 // A template carries structure and wording, not the identity of the sheet it came from.81 const doc: Sheet = { ...input.doc, metadata: { ...input.doc.metadata, slug: undefined, example: undefined } };82 const [t] = await sql<{ id: string }[]>`INSERT INTO templates (key, title, description, locale, doc, created_by) VALUES (${key}, ${input.title}, ${input.description}, ${doc.metadata.locale}, ${j(doc)}, ${input.userId}) RETURNING id`;83 return { id: t.id, key };84}85export async function customTemplateDoc(key: string): Promise<Sheet> {86 const [t] = await sql<{ doc: unknown }[]>`SELECT doc FROM templates WHERE key = ${key} AND deleted_at IS NULL`;87 if (!t) throw new ApiError(404, "not_found", "Template not found");88 return mustParseSheet(t.doc);89}90export async function deleteCustomTemplate(id: string) { await sql`UPDATE templates SET deleted_at = now() WHERE id = ${id}`; }9192// ── Topics ──────────────────────────────────────────────────────────────────────────────────────────9394export async function adminTopics() {95 return sql<{ id: string; slug: string; name_fr: string; name_en: string; description_fr: string; description_en: string; tone: string; position: number; sheets: number }[]>`96 SELECT t.*, (SELECT count(*)::int FROM sheet_topics st JOIN sheets s ON s.id = st.sheet_id WHERE st.topic_id = t.id AND s.deleted_at IS NULL) AS sheets FROM topics t ORDER BY t.position, t.name_fr`;97}98export async function updateTopic(id: string, p: { nameFr?: string; nameEn?: string; descriptionFr?: string; descriptionEn?: string; tone?: string; position?: number }) {99 const r = await sql`UPDATE topics SET name_fr = coalesce(${p.nameFr ?? null}, name_fr), name_en = coalesce(${p.nameEn ?? null}, name_en), description_fr = coalesce(${p.descriptionFr ?? null}, description_fr),100 description_en = coalesce(${p.descriptionEn ?? null}, description_en), tone = coalesce(${p.tone ?? null}, tone), position = coalesce(${p.position ?? null}, position) WHERE id = ${id} RETURNING id`;101 if (!r.length) throw new ApiError(404, "not_found", "Topic not found");102}103export async function deleteTopic(id: string) {104 const [used] = await sql`SELECT 1 FROM sheet_topics WHERE topic_id = ${id} LIMIT 1`;105 if (used) throw new ApiError(409, "topic_in_use", "Sheets still use this topic");106 await sql`DELETE FROM topics WHERE id = ${id}`;107}108