Repository Public
1/**2 * Sheets: working copies, revisions, publication. Every write goes through this module.3 *4 * - The working copy (sheets.doc) always equals the document of sheets.current_revision_id.5 * - Revisions are immutable; reverting creates a new revision.6 * - Publishing copies the current revision into sheet_publications (immutable) and moves a single pointer inside7 * one transaction, so readers see either the previous publication or the new one, never a mix.8 */9import { createHash } from "node:crypto";10import { j, sql, type Tx } from "@/lib/db";11import { ApiError } from "@/lib/http";12import { audit } from "@/lib/audit";13import { diffSheets, mustParseSheet, parseSheet, readingMinutes, sheetText, slugify, stableStringify, type Locale, type PatchOp, type Sheet, type SheetDiff } from "@/schema";14import { qualityReport, type QualityReport } from "./quality";1516export type SheetStatus = "draft" | "in_review" | "published" | "archived";17export type RevisionOrigin = "manual" | "ai_generation" | "ai_edit" | "revert" | "import" | "template" | "source_check";1819export interface SheetRow {20 id: string; slug: string; locale: Locale; localization_id: string; status: SheetStatus; title: string; summary: string; doc: unknown;21 current_revision_id: string; publication_id: string | null; first_published_at: Date | null; published_at: Date | null;22 featured: boolean; featured_position: number; allow_download: boolean; seo_title: string | null; seo_description: string | null;23 canonical_url: string | null; og_asset_id: string | null; version: number; created_at: Date; updated_at: Date;24}2526export const docHash = (doc: Sheet) => createHash("sha256").update(stableStringify(doc)).digest("hex");2728/** Parses client or model input; a 422 lists what is wrong. */29export function validDoc(raw: unknown): Sheet {30 const r = parseSheet(raw);31 if (!r.ok) throw new ApiError(422, "invalid_document", "The document does not match the Sheet schema", r.issues);32 return r.sheet;33}3435async function freeSlug(tx: Tx | typeof sql, wanted: string, locale: Locale, exceptId?: string): Promise<string> {36 const base = slugify(wanted);37 for (let i = 0; i < 200; i++) {38 const candidate = i === 0 ? base : `${base.slice(0, 90)}-${i + 1}`;39 const [taken] = await tx`SELECT 1 FROM sheets WHERE locale = ${locale} AND slug = ${candidate} AND deleted_at IS NULL AND id IS DISTINCT FROM ${exceptId ?? null}`;40 if (!taken) return candidate;41 }42 throw new ApiError(409, "slug_taken", "No free slug could be found");43}4445// ── Taxonomy sync ───────────────────────────────────────────────────────────────────────────────────4647/** Topics and tags named in the document's metadata become rows; unknown topics are created on first use. */48async function syncTaxonomy(tx: Tx, sheetId: string, doc: Sheet): Promise<void> {49 await tx`DELETE FROM sheet_topics WHERE sheet_id = ${sheetId}`;50 for (const name of doc.metadata.topics) {51 const slug = slugify(name);52 let [t] = await tx<{ id: string }[]>`SELECT id FROM topics WHERE slug = ${slug} OR f_unaccent(lower(name_fr)) = f_unaccent(lower(${name})) OR f_unaccent(lower(name_en)) = f_unaccent(lower(${name})) LIMIT 1`;53 if (!t) [t] = await tx<{ id: string }[]>`INSERT INTO topics (slug, name_fr, name_en, position) VALUES (${slug}, ${name}, ${name}, (SELECT coalesce(max(position), 0) + 1 FROM topics)) ON CONFLICT (slug) DO UPDATE SET slug = EXCLUDED.slug RETURNING id`;54 await tx`INSERT INTO sheet_topics (sheet_id, topic_id) VALUES (${sheetId}, ${t.id}) ON CONFLICT DO NOTHING`;55 }56 await tx`DELETE FROM sheet_tags WHERE sheet_id = ${sheetId}`;57 for (const name of doc.metadata.tags) {58 const slug = slugify(name);59 const [t] = await tx<{ id: string }[]>`INSERT INTO tags (slug, name) VALUES (${slug}, ${name}) ON CONFLICT (slug) DO UPDATE SET slug = EXCLUDED.slug RETURNING id`;60 await tx`INSERT INTO sheet_tags (sheet_id, tag_id) VALUES (${sheetId}, ${t.id}) ON CONFLICT DO NOTHING`;61 }62 // Reference library.63 await tx`DELETE FROM sheet_sources WHERE sheet_id = ${sheetId}`;64 for (const s of doc.sources) {65 const fingerprint = s.doi ? `doi:${s.doi.toLowerCase()}` : s.url ? `url:${s.url}` : `title:${slugify(s.title)}`;66 const [row] = await tx<{ id: string }[]>`67 INSERT INTO sources (fingerprint, type, title, authors, year, container, doi, url, status, checked_at)68 VALUES (${fingerprint}, ${s.type}, ${s.title}, ${s.authors}, ${s.year ?? null}, ${s.container ?? null}, ${s.doi ?? null}, ${s.url ?? null}, ${s.status}, ${s.checkedAt ?? null})69 ON CONFLICT (fingerprint) DO UPDATE SET title = EXCLUDED.title, authors = EXCLUDED.authors, year = EXCLUDED.year, container = EXCLUDED.container, status = EXCLUDED.status, checked_at = EXCLUDED.checked_at, updated_at = now()70 RETURNING id`;71 await tx`INSERT INTO sheet_sources (sheet_id, source_id, local_id) VALUES (${sheetId}, ${row.id}, ${s.id}) ON CONFLICT DO NOTHING`;72 }73}7475// ── Create, read ────────────────────────────────────────────────────────────────────────────────────7677export async function createSheet(input: { doc: Sheet; origin: RevisionOrigin; userId: string | null; summary?: string; localizationId?: string; jobId?: string }): Promise<{ id: string; slug: string }> {78 const doc = input.doc;79 return sql.begin(async (tx) => {80 const locale = doc.metadata.locale;81 const slug = await freeSlug(tx, doc.metadata.slug ?? doc.metadata.title, locale);82 let localizationId = input.localizationId;83 if (localizationId) {84 const [clash] = await tx`SELECT 1 FROM sheets WHERE localization_id = ${localizationId} AND locale = ${locale} AND deleted_at IS NULL`;85 if (clash) throw new ApiError(409, "translation_exists", "This sheet already has a version in that language");86 } else [{ id: localizationId }] = await tx<{ id: string }[]>`INSERT INTO sheet_localizations DEFAULT VALUES RETURNING id`;87 const [sheet] = await tx<{ id: string }[]>`88 INSERT INTO sheets (slug, locale, localization_id, title, summary, doc, created_by)89 VALUES (${slug}, ${locale}, ${localizationId!}, ${doc.metadata.title}, ${doc.metadata.summary}, ${j(doc)}, ${input.userId}) RETURNING id`;90 const [rev] = await tx<{ id: string }[]>`91 INSERT INTO sheet_revisions (sheet_id, number, doc, doc_hash, origin, summary, job_id, created_by)92 VALUES (${sheet.id}, 1, ${j(doc)}, ${docHash(doc)}, ${input.origin}, ${input.summary ?? ""}, ${input.jobId ?? null}, ${input.userId}) RETURNING id`;93 await tx`UPDATE sheets SET current_revision_id = ${rev.id} WHERE id = ${sheet.id}`;94 await syncTaxonomy(tx, sheet.id, doc);95 await audit({ userId: input.userId, actor: input.userId ? "admin" : "system", action: "sheet.created", entity: "sheet", entityId: sheet.id, meta: { origin: input.origin, locale } }, tx);96 return { id: sheet.id, slug };97 });98}99100export async function getSheetRow(id: string): Promise<SheetRow> {101 const [row] = await sql<SheetRow[]>`SELECT * FROM sheets WHERE id = ${id} AND deleted_at IS NULL`;102 if (!row) throw new ApiError(404, "not_found", "Sheet not found");103 return row;104}105106export interface SheetDetail {107 id: string; slug: string; locale: Locale; status: SheetStatus; title: string; summary: string; doc: Sheet; version: number;108 currentRevisionId: string; revisionNumber: number; publishedRevisionId: string | null; hasUnpublishedChanges: boolean;109 featured: boolean; featuredPosition: number; allowDownload: boolean; seoTitle: string | null; seoDescription: string | null; canonicalUrl: string | null; ogAssetId: string | null;110 publishedAt: string | null; firstPublishedAt: string | null; createdAt: string; updatedAt: string;111 translations: { id: string; locale: Locale; slug: string; status: SheetStatus; title: string }[];112 quality: QualityReport;113}114115export async function getSheetDetail(id: string): Promise<SheetDetail> {116 const row = await getSheetRow(id);117 const doc = mustParseSheet(row.doc);118 const [rev] = await sql<{ number: number }[]>`SELECT number FROM sheet_revisions WHERE id = ${row.current_revision_id}`;119 const [pub] = row.publication_id ? await sql<{ revision_id: string }[]>`SELECT revision_id FROM sheet_publications WHERE id = ${row.publication_id}` : [];120 const translations = await sql<{ id: string; locale: Locale; slug: string; status: SheetStatus; title: string }[]>`121 SELECT id, locale, slug, status, title FROM sheets WHERE localization_id = ${row.localization_id} AND id <> ${id} AND deleted_at IS NULL`;122 return {123 id: row.id, slug: row.slug, locale: row.locale, status: row.status, title: row.title, summary: row.summary, doc, version: row.version,124 currentRevisionId: row.current_revision_id, revisionNumber: rev?.number ?? 1, publishedRevisionId: pub?.revision_id ?? null,125 hasUnpublishedChanges: !!pub && pub.revision_id !== row.current_revision_id,126 featured: row.featured, featuredPosition: row.featured_position, allowDownload: row.allow_download, seoTitle: row.seo_title, seoDescription: row.seo_description,127 canonicalUrl: row.canonical_url, ogAssetId: row.og_asset_id,128 publishedAt: row.published_at?.toISOString() ?? null, firstPublishedAt: row.first_published_at?.toISOString() ?? null,129 createdAt: row.created_at.toISOString(), updatedAt: row.updated_at.toISOString(),130 translations: [...translations], quality: qualityReport(doc),131 };132}133134export async function listSheets(q: { status?: SheetStatus; search?: string; limit: number; offset: number }) {135 const like = q.search ? `%${q.search}%` : null;136 const rows = await sql<{ id: string; slug: string; locale: Locale; status: SheetStatus; title: string; summary: string; updated_at: Date; published_at: Date | null; featured: boolean; revisions: number; dirty: boolean; total: number }[]>`137 SELECT s.id, s.slug, s.locale, s.status, s.title, s.summary, s.updated_at, s.published_at, s.featured,138 (SELECT count(*)::int FROM sheet_revisions r WHERE r.sheet_id = s.id) AS revisions,139 (s.publication_id IS NOT NULL AND (SELECT revision_id FROM sheet_publications p WHERE p.id = s.publication_id) <> s.current_revision_id) AS dirty,140 count(*) OVER ()::int AS total141 FROM sheets s142 WHERE s.deleted_at IS NULL AND (${q.status ?? null}::text IS NULL OR s.status = ${q.status ?? null})143 AND (${like}::text IS NULL OR s.title ILIKE ${like} OR s.slug ILIKE ${like})144 ORDER BY s.updated_at DESC LIMIT ${q.limit} OFFSET ${q.offset}`;145 return { total: rows[0]?.total ?? 0, items: rows.map((r) => ({ id: r.id, slug: r.slug, locale: r.locale, status: r.status, title: r.title, summary: r.summary, updatedAt: r.updated_at.toISOString(), publishedAt: r.published_at?.toISOString() ?? null, featured: r.featured, revisions: r.revisions, hasUnpublishedChanges: r.dirty })) };146}147148// ── Save (new revision) ─────────────────────────────────────────────────────────────────────────────149150export interface SaveResult { version: number; revisionId: string; revisionNumber: number; changed: boolean; diff: SheetDiff | null; quality: QualityReport }151152/**153 * Writes a new revision of the working copy. `expectedVersion` is the optimistic lock: a stale tab (or an AI job154 * that finished after a manual edit) gets a 409 instead of silently overwriting newer work.155 */156export async function saveDoc(input: { sheetId: string; doc: Sheet; expectedVersion: number; origin: RevisionOrigin; summary?: string; patch?: PatchOp[]; jobId?: string; userId: string | null }): Promise<SaveResult> {157 const doc = input.doc;158 return sql.begin(async (tx) => {159 const [row] = await tx<{ version: number; doc: unknown; current_revision_id: string; locale: Locale }[]>`SELECT version, doc, current_revision_id, locale FROM sheets WHERE id = ${input.sheetId} AND deleted_at IS NULL FOR UPDATE`;160 if (!row) throw new ApiError(404, "not_found", "Sheet not found");161 if (row.version !== input.expectedVersion) throw new ApiError(409, "version_conflict", "The sheet was changed elsewhere; reload it", { currentVersion: row.version });162 if (doc.metadata.locale !== row.locale) throw new ApiError(422, "locale_locked", "The language of a sheet cannot change; create a translation instead");163 const before = mustParseSheet(row.doc);164 const hash = docHash(doc);165 const [cur] = await tx<{ number: number; doc_hash: string }[]>`SELECT number, doc_hash FROM sheet_revisions WHERE id = ${row.current_revision_id}`;166 if (cur.doc_hash === hash) return { version: row.version, revisionId: row.current_revision_id, revisionNumber: cur.number, changed: false, diff: null, quality: qualityReport(doc) };167 const [{ next }] = await tx<{ next: number }[]>`SELECT coalesce(max(number), 0) + 1 AS next FROM sheet_revisions WHERE sheet_id = ${input.sheetId}`;168 const [rev] = await tx<{ id: string }[]>`169 INSERT INTO sheet_revisions (sheet_id, number, doc, doc_hash, origin, summary, patch, parent_id, job_id, created_by)170 VALUES (${input.sheetId}, ${next}, ${j(doc)}, ${hash}, ${input.origin}, ${(input.summary ?? "").slice(0, 500)}, ${input.patch ? j(input.patch) : null}, ${row.current_revision_id}, ${input.jobId ?? null}, ${input.userId}) RETURNING id`;171 await tx`UPDATE sheets SET doc = ${j(doc)}, title = ${doc.metadata.title}, summary = ${doc.metadata.summary}, current_revision_id = ${rev.id}, version = version + 1, updated_at = now() WHERE id = ${input.sheetId}`;172 await syncTaxonomy(tx, input.sheetId, doc);173 return { version: row.version + 1, revisionId: rev.id, revisionNumber: next, changed: true, diff: diffSheets(before, doc), quality: qualityReport(doc) };174 });175}176177export async function listRevisions(sheetId: string) {178 const rows = await sql<{ id: string; number: number; origin: RevisionOrigin; summary: string; created_at: Date; parent_id: string | null; published: boolean }[]>`179 SELECT r.id, r.number, r.origin, r.summary, r.created_at, r.parent_id,180 EXISTS (SELECT 1 FROM sheet_publications p WHERE p.revision_id = r.id) AS published181 FROM sheet_revisions r WHERE r.sheet_id = ${sheetId} ORDER BY r.number DESC LIMIT 200`;182 return rows.map((r) => ({ id: r.id, number: r.number, origin: r.origin, summary: r.summary, createdAt: r.created_at.toISOString(), parentId: r.parent_id, wasPublished: r.published }));183}184185export async function getRevision(sheetId: string, revisionId: string) {186 const [r] = await sql<{ id: string; number: number; doc: unknown; origin: RevisionOrigin; summary: string; patch: unknown; created_at: Date; parent_id: string | null }[]>`187 SELECT id, number, doc, origin, summary, patch, created_at, parent_id FROM sheet_revisions WHERE id = ${revisionId} AND sheet_id = ${sheetId}`;188 if (!r) throw new ApiError(404, "not_found", "Revision not found");189 return { id: r.id, number: r.number, doc: mustParseSheet(r.doc), origin: r.origin, summary: r.summary, patch: r.patch, createdAt: r.created_at.toISOString(), parentId: r.parent_id };190}191192/** Restores an earlier revision by writing it again as the newest one. Nothing is deleted. */193export async function revertTo(sheetId: string, revisionId: string, expectedVersion: number, userId: string): Promise<SaveResult> {194 const target = await getRevision(sheetId, revisionId);195 const r = await saveDoc({ sheetId, doc: target.doc, expectedVersion, origin: "revert", summary: `Retour à la révision ${target.number}`, userId });196 await audit({ userId, actor: "admin", action: "sheet.reverted", entity: "sheet", entityId: sheetId, meta: { toRevision: target.number } });197 return r;198}199200// ── Settings of a sheet (not part of the document) ──────────────────────────────────────────────────201202export interface SheetSettings { slug?: string; status?: "draft" | "in_review" | "archived"; featured?: boolean; featuredPosition?: number; allowDownload?: boolean; seoTitle?: string | null; seoDescription?: string | null; canonicalUrl?: string | null; ogAssetId?: string | null }203204export async function updateSettings(sheetId: string, s: SheetSettings, userId: string): Promise<void> {205 await sql.begin(async (tx) => {206 const [row] = await tx<SheetRow[]>`SELECT * FROM sheets WHERE id = ${sheetId} AND deleted_at IS NULL FOR UPDATE`;207 if (!row) throw new ApiError(404, "not_found", "Sheet not found");208 if (s.slug !== undefined && s.slug !== row.slug) {209 const [taken] = await tx`SELECT 1 FROM sheets WHERE locale = ${row.locale} AND slug = ${s.slug} AND deleted_at IS NULL AND id <> ${sheetId}`;210 if (taken) throw new ApiError(409, "slug_taken", "Another sheet already uses this address");211 // The old public address keeps answering with a 301 if the sheet has ever been public.212 if (row.first_published_at) await tx`INSERT INTO slug_redirects (locale, old_slug, sheet_id) VALUES (${row.locale}, ${row.slug}, ${sheetId}) ON CONFLICT (locale, old_slug) DO UPDATE SET sheet_id = EXCLUDED.sheet_id`;213 await tx`DELETE FROM slug_redirects WHERE locale = ${row.locale} AND old_slug = ${s.slug}`;214 await tx`UPDATE sheets SET slug = ${s.slug} WHERE id = ${sheetId}`;215 }216 if (s.status !== undefined && s.status !== row.status) {217 if (row.status === "published") throw new ApiError(409, "published", "Unpublish the sheet before changing its status");218 await tx`UPDATE sheets SET status = ${s.status} WHERE id = ${sheetId}`;219 }220 if (s.ogAssetId) { const [a] = await tx`SELECT 1 FROM assets WHERE id = ${s.ogAssetId} AND kind = 'image' AND deleted_at IS NULL`; if (!a) throw new ApiError(422, "bad_asset", "The sharing image must be an uploaded image"); }221 await tx`UPDATE sheets SET222 featured = ${s.featured ?? row.featured}, featured_position = ${s.featuredPosition ?? row.featured_position}, allow_download = ${s.allowDownload ?? row.allow_download},223 seo_title = ${s.seoTitle === undefined ? row.seo_title : s.seoTitle}, seo_description = ${s.seoDescription === undefined ? row.seo_description : s.seoDescription},224 canonical_url = ${s.canonicalUrl === undefined ? row.canonical_url : s.canonicalUrl}, og_asset_id = ${s.ogAssetId === undefined ? row.og_asset_id : s.ogAssetId}, updated_at = now()225 WHERE id = ${sheetId}`;226 await audit({ userId, actor: "admin", action: "sheet.settings", entity: "sheet", entityId: sheetId, meta: { keys: Object.keys(s) } }, tx);227 });228}229230// ── Publication ─────────────────────────────────────────────────────────────────────────────────────231232/** Images used by a published document become readable without a session; everything else stays private. */233async function exposeAssets(tx: Tx, doc: Sheet, ogAssetId: string | null): Promise<void> {234 const ids = [...doc.assets.filter((a) => a.kind === "image").map((a) => a.assetId), ...(ogAssetId ? [ogAssetId] : [])];235 if (ids.length) await tx`UPDATE assets SET is_public = true WHERE id = ANY(${ids}::uuid[]) AND kind IN ('image', 'svg')`;236}237238/**239 * Publishes the current revision. Explicit act of the administrator: nothing in the system calls this on its own.240 * Refused while the quality report has blocking errors.241 */242export async function publish(sheetId: string, expectedVersion: number, userId: string): Promise<{ publicationId: string; number: number; slug: string; locale: Locale }> {243 const out = await sql.begin(async (tx) => {244 const [row] = await tx<SheetRow[]>`SELECT * FROM sheets WHERE id = ${sheetId} AND deleted_at IS NULL FOR UPDATE`;245 if (!row) throw new ApiError(404, "not_found", "Sheet not found");246 if (row.version !== expectedVersion) throw new ApiError(409, "version_conflict", "The sheet was changed elsewhere; reload it", { currentVersion: row.version });247 const doc = mustParseSheet(row.doc);248 const q = qualityReport(doc);249 if (!q.publishable) throw new ApiError(422, "quality_errors", "The quality report has blocking errors", q.items.filter((i) => i.severity === "error"));250 const [{ next }] = await tx<{ next: number }[]>`SELECT coalesce(max(number), 0) + 1 AS next FROM sheet_publications WHERE sheet_id = ${sheetId}`;251 if (row.publication_id) await tx`UPDATE sheet_publications SET unpublished_at = now() WHERE id = ${row.publication_id}`;252 const [pub] = await tx<{ id: string }[]>`253 INSERT INTO sheet_publications (sheet_id, revision_id, number, doc, slug, title, summary, reading_minutes, search_text, published_by)254 VALUES (${sheetId}, ${row.current_revision_id}, ${next}, ${j(doc)}, ${row.slug}, ${doc.metadata.title}, ${doc.metadata.summary}, ${readingMinutes(doc)}, ${sheetText(doc).slice(0, 200_000)}, ${userId}) RETURNING id`;255 await tx`UPDATE sheets SET status = 'published', publication_id = ${pub.id}, published_at = now(), first_published_at = coalesce(first_published_at, now()), updated_at = now() WHERE id = ${sheetId}`;256 await exposeAssets(tx, doc, row.og_asset_id);257 // A fresh PDF for readers, when downloads are allowed (rendered by the worker).258 if (row.allow_download) await tx`SELECT pg_notify('sheet_jobs', 'export')`;259 if (row.allow_download) await tx`INSERT INTO export_jobs (sheet_id, publication_id, format, options, is_public, created_by) VALUES (${sheetId}, ${pub.id}, 'pdf', ${j({})}, true, ${userId})`;260 await audit({ userId, actor: "admin", action: "sheet.published", entity: "sheet", entityId: sheetId, meta: { publication: next, revisionId: row.current_revision_id, warnings: q.warnings } }, tx);261 return { publicationId: pub.id, number: next, slug: row.slug, locale: row.locale };262 });263 return out;264}265266export async function unpublish(sheetId: string, userId: string): Promise<void> {267 await sql.begin(async (tx) => {268 const [row] = await tx<SheetRow[]>`SELECT * FROM sheets WHERE id = ${sheetId} AND deleted_at IS NULL FOR UPDATE`;269 if (!row) throw new ApiError(404, "not_found", "Sheet not found");270 if (!row.publication_id) throw new ApiError(409, "not_published", "The sheet is not published");271 await tx`UPDATE sheets SET status = 'draft', publication_id = NULL, published_at = NULL, featured = false, updated_at = now() WHERE id = ${sheetId}`;272 await tx`UPDATE sheet_publications SET unpublished_at = now() WHERE id = ${row.publication_id}`;273 // Files that were public only because of this publication stop being served: its reader PDFs, and its images274 // unless another sheet that is still published uses them.275 await tx`UPDATE assets SET is_public = false WHERE id IN (SELECT asset_id FROM export_jobs WHERE sheet_id = ${sheetId} AND is_public AND asset_id IS NOT NULL)`;276 await tx`UPDATE assets a SET is_public = false WHERE a.is_public AND a.kind IN ('image', 'svg')277 AND (SELECT doc::text FROM sheet_publications WHERE id = ${row.publication_id}) LIKE '%' || a.id::text || '%'278 AND NOT EXISTS (SELECT 1 FROM sheets s JOIN sheet_publications p ON p.id = s.publication_id WHERE s.deleted_at IS NULL AND s.id <> ${sheetId} AND (p.doc::text LIKE '%' || a.id::text || '%' OR s.og_asset_id = a.id))`;279 await audit({ userId, actor: "admin", action: "sheet.unpublished", entity: "sheet", entityId: sheetId }, tx);280 });281}282283export async function deleteSheet(sheetId: string, userId: string): Promise<void> {284 await sql.begin(async (tx) => {285 const [row] = await tx<SheetRow[]>`SELECT * FROM sheets WHERE id = ${sheetId} AND deleted_at IS NULL FOR UPDATE`;286 if (!row) throw new ApiError(404, "not_found", "Sheet not found");287 if (row.publication_id) throw new ApiError(409, "published", "Unpublish the sheet before deleting it");288 // Soft delete: revisions stay for the audit trail; the slug is released.289 await tx`UPDATE sheets SET deleted_at = now(), status = 'archived' WHERE id = ${sheetId}`;290 await tx`DELETE FROM slug_redirects WHERE sheet_id = ${sheetId}`;291 await audit({ userId, actor: "admin", action: "sheet.deleted", entity: "sheet", entityId: sheetId }, tx);292 });293}294295// ── Public queries: publications only ───────────────────────────────────────────────────────────────296// Every function below reads sheet_publications through sheets.publication_id. Drafts are unreachable from here.297298export interface PublicCard { slug: string; locale: Locale; title: string; summary: string; readingMinutes: number; publishedAt: string; updatedAt: string; level: string; kind: string; topics: { slug: string; name: string }[]; featured: boolean; theme: string; accent: string;299 /** LaTeX of the first equation of the sheet: drawn on its cover. */300 formula: string | null }301302interface CardRow { slug: string; locale: Locale; title: string; summary: string; reading_minutes: number; first_published_at: Date; published_at: Date; level: string; kind: string; featured: boolean; theme: string; accent: string; formula: string | null; topics: { slug: string; name_fr: string; name_en: string }[] | null; total?: number }303const card = (r: CardRow, uiLocale: Locale): PublicCard => ({304 slug: r.slug, locale: r.locale, title: r.title, summary: r.summary, readingMinutes: r.reading_minutes,305 publishedAt: r.first_published_at.toISOString(), updatedAt: r.published_at.toISOString(), level: r.level, kind: r.kind, featured: r.featured, theme: r.theme, accent: r.accent, formula: r.formula,306 topics: (r.topics ?? []).map((t) => ({ slug: t.slug, name: uiLocale === "en" ? t.name_en : t.name_fr })),307});308309const CARD = sql`310 s.slug, s.locale, p.title, p.summary, p.reading_minutes, s.first_published_at, p.published_at, s.featured,311 coalesce(p.doc->'metadata'->>'level', 'intermediate') AS level, coalesce(p.doc->'metadata'->>'kind', 'explainer') AS kind,312 coalesce(p.doc->'theme'->>'preset', 'scientific-light') AS theme, coalesce(p.doc->'theme'->>'accent', 'blue') AS accent,313 (SELECT b.value->>'latex' FROM jsonb_array_elements(p.doc->'blocks') WITH ORDINALITY AS b(value, n) WHERE b.value->>'type' = 'equation' ORDER BY b.n LIMIT 1) AS formula,314 (SELECT json_agg(json_build_object('slug', t.slug, 'name_fr', t.name_fr, 'name_en', t.name_en) ORDER BY t.position)315 FROM sheet_topics st JOIN topics t ON t.id = st.topic_id WHERE st.sheet_id = s.id) AS topics`;316317export interface PublicQuery { locale?: Locale; topic?: string; tag?: string; level?: string; kind?: string; q?: string; featured?: boolean; limit: number; offset: number; uiLocale: Locale }318319export async function listPublished(q: PublicQuery): Promise<{ total: number; items: PublicCard[] }> {320 const term = q.q?.trim().slice(0, 120) || null;321 const rows = await sql<CardRow[]>`322 SELECT ${CARD}, count(*) OVER ()::int AS total,323 CASE WHEN ${term}::text IS NULL THEN 0 ELSE324 ts_rank(CASE WHEN s.locale = 'fr' THEN p.search_fr ELSE p.search_en END, websearch_to_tsquery(CASE WHEN s.locale = 'fr' THEN 'french'::regconfig ELSE 'english'::regconfig END, f_unaccent(${term})))325 + similarity(f_unaccent(lower(p.title)), f_unaccent(lower(${term}))) END AS rank326 FROM sheets s JOIN sheet_publications p ON p.id = s.publication_id327 WHERE s.deleted_at IS NULL AND s.status = 'published'328 AND (${q.locale ?? null}::text IS NULL OR s.locale = ${q.locale ?? null})329 AND (${q.level ?? null}::text IS NULL OR p.doc->'metadata'->>'level' = ${q.level ?? null})330 AND (${q.kind ?? null}::text IS NULL OR p.doc->'metadata'->>'kind' = ${q.kind ?? null})331 AND (${q.featured ?? null}::boolean IS NULL OR s.featured = ${q.featured ?? null})332 AND (${q.topic ?? null}::text IS NULL OR EXISTS (SELECT 1 FROM sheet_topics st JOIN topics t ON t.id = st.topic_id WHERE st.sheet_id = s.id AND t.slug = ${q.topic ?? null}))333 AND (${q.tag ?? null}::text IS NULL OR EXISTS (SELECT 1 FROM sheet_tags st JOIN tags t ON t.id = st.tag_id WHERE st.sheet_id = s.id AND t.slug = ${q.tag ?? null}))334 AND (${term}::text IS NULL335 OR (CASE WHEN s.locale = 'fr' THEN p.search_fr ELSE p.search_en END) @@ websearch_to_tsquery(CASE WHEN s.locale = 'fr' THEN 'french'::regconfig ELSE 'english'::regconfig END, f_unaccent(${term}))336 OR f_unaccent(lower(p.title)) % f_unaccent(lower(${term}))337 OR f_unaccent(lower(p.title)) LIKE '%' || f_unaccent(lower(${term})) || '%')338 ORDER BY rank DESC, ${q.featured ? sql`s.featured_position ASC,` : sql``} s.first_published_at DESC339 LIMIT ${q.limit} OFFSET ${q.offset}`;340 return { total: rows[0]?.total ?? 0, items: rows.map((r) => card(r, q.uiLocale)) };341}342343export interface PublishedSheet {344 sheetId: string; publicationId: string; slug: string; locale: Locale; doc: Sheet; title: string; summary: string; readingMinutes: number;345 publishedAt: string; updatedAt: string; seoTitle: string | null; seoDescription: string | null; canonicalUrl: string | null; ogAssetId: string | null; allowDownload: boolean;346 translations: { locale: Locale; slug: string }[]; topics: { slug: string; name_fr: string; name_en: string }[]; tags: { slug: string; name: string }[];347 pdfAssetId: string | null;348}349350/** A published sheet by address, or where its old address now points. Prefers the visitor's language on a tie. */351export async function getPublished(slug: string, prefer: Locale): Promise<{ sheet: PublishedSheet } | { redirect: string } | null> {352 const rows = await sql<{ id: string; publication_id: string; slug: string; locale: Locale; localization_id: string; doc: unknown; title: string; summary: string; reading_minutes: number; first_published_at: Date; published_at: Date; seo_title: string | null; seo_description: string | null; canonical_url: string | null; og_asset_id: string | null; allow_download: boolean }[]>`353 SELECT s.id, s.publication_id, s.slug, s.locale, s.localization_id, p.doc, p.title, p.summary, p.reading_minutes, s.first_published_at, p.published_at,354 s.seo_title, s.seo_description, s.canonical_url, s.og_asset_id, s.allow_download355 FROM sheets s JOIN sheet_publications p ON p.id = s.publication_id356 WHERE s.slug = ${slug} AND s.deleted_at IS NULL AND s.status = 'published'`;357 const r = rows.find((x) => x.locale === prefer) ?? rows[0];358 if (!r) {359 const [red] = await sql<{ slug: string }[]>`360 SELECT s.slug FROM slug_redirects d JOIN sheets s ON s.id = d.sheet_id361 WHERE d.old_slug = ${slug} AND s.deleted_at IS NULL AND s.status = 'published' ORDER BY (d.locale = ${prefer}) DESC LIMIT 1`;362 return red ? { redirect: red.slug } : null;363 }364 const translations = await sql<{ locale: Locale; slug: string }[]>`SELECT locale, slug FROM sheets WHERE localization_id = ${r.localization_id} AND id <> ${r.id} AND status = 'published' AND deleted_at IS NULL`;365 const topics = await sql<{ slug: string; name_fr: string; name_en: string }[]>`SELECT t.slug, t.name_fr, t.name_en FROM sheet_topics st JOIN topics t ON t.id = st.topic_id WHERE st.sheet_id = ${r.id} ORDER BY t.position`;366 const tags = await sql<{ slug: string; name: string }[]>`SELECT t.slug, t.name FROM sheet_tags st JOIN tags t ON t.id = st.tag_id WHERE st.sheet_id = ${r.id} ORDER BY t.name`;367 const [pdf] = r.allow_download ? await sql<{ asset_id: string }[]>`SELECT asset_id FROM export_jobs WHERE publication_id = ${r.publication_id} AND is_public AND format = 'pdf' AND status = 'succeeded' AND asset_id IS NOT NULL ORDER BY created_at DESC LIMIT 1` : [];368 return { sheet: {369 sheetId: r.id, publicationId: r.publication_id, slug: r.slug, locale: r.locale, doc: mustParseSheet(r.doc), title: r.title, summary: r.summary, readingMinutes: r.reading_minutes,370 publishedAt: r.first_published_at.toISOString(), updatedAt: r.published_at.toISOString(), seoTitle: r.seo_title, seoDescription: r.seo_description, canonicalUrl: r.canonical_url,371 ogAssetId: r.og_asset_id, allowDownload: r.allow_download, translations: [...translations], topics: [...topics], tags: [...tags], pdfAssetId: pdf?.asset_id ?? null,372 } };373}374375export async function relatedPublished(sheetId: string, uiLocale: Locale, limit = 3): Promise<PublicCard[]> {376 const rows = await sql<CardRow[]>`377 SELECT ${CARD}, (SELECT count(*) FROM sheet_topics a JOIN sheet_topics b ON a.topic_id = b.topic_id WHERE a.sheet_id = s.id AND b.sheet_id = ${sheetId}) AS shared378 FROM sheets s JOIN sheet_publications p ON p.id = s.publication_id379 WHERE s.deleted_at IS NULL AND s.status = 'published' AND s.id <> ${sheetId}380 AND s.localization_id <> (SELECT localization_id FROM sheets WHERE id = ${sheetId})381 ORDER BY shared DESC, (s.locale = ${uiLocale}) DESC, s.first_published_at DESC LIMIT ${limit}`;382 return rows.map((r) => card(r, uiLocale));383}384385export async function listTopics(uiLocale: Locale) {386 const rows = await sql<{ slug: string; name_fr: string; name_en: string; description_fr: string; description_en: string; tone: string; n: number }[]>`387 SELECT t.slug, t.name_fr, t.name_en, t.description_fr, t.description_en, t.tone,388 (SELECT count(*)::int FROM sheet_topics st JOIN sheets s ON s.id = st.sheet_id WHERE st.topic_id = t.id AND s.status = 'published' AND s.deleted_at IS NULL) AS n389 FROM topics t ORDER BY t.position, t.name_fr`;390 return rows.map((t) => ({ slug: t.slug, name: uiLocale === "en" ? t.name_en : t.name_fr, description: uiLocale === "en" ? t.description_en : t.description_fr, tone: t.tone, count: t.n }));391}392393export async function allPublishedForFeeds() {394 return sql<{ slug: string; locale: Locale; title: string; summary: string; first_published_at: Date; published_at: Date; localization_id: string }[]>`395 SELECT s.slug, s.locale, p.title, p.summary, s.first_published_at, p.published_at, s.localization_id396 FROM sheets s JOIN sheet_publications p ON p.id = s.publication_id WHERE s.deleted_at IS NULL AND s.status = 'published' ORDER BY s.first_published_at DESC LIMIT 2000`;397}398399/** One more view today. Stores a count per sheet and day — nothing about the visitor. */400export async function countView(sheetId: string): Promise<void> {401 await sql`INSERT INTO sheet_views (sheet_id, day, views) VALUES (${sheetId}, current_date, 1) ON CONFLICT (sheet_id, day) DO UPDATE SET views = sheet_views.views + 1`;402}403