database.ts - oh-my-ppt

database.ts

1	import { createClient } from '@libsql/client'
2	import { drizzle } from 'drizzle-orm/libsql'
3	import { eq, ne, gt, lte, count, max, asc, desc, sql, and, or, isNull, inArray } from 'drizzle-orm'
4	import * as schema from './schema'
5	import path from 'path'
6	import { app } from 'electron'
7	import { is } from '@electron-toolkit/utils'
8	import fs from 'fs'
9	import crypto from 'crypto'
10	import { runDatabasePatches } from './patch'
11	import {
12	compareStyleVersion,
13	listStylePackageDirectories,
14	normalizeStyleVersion,
15	readStylePackage,
16	styleRowToPackageJson
17	} from '../styles'
18	import type { HtmlThumbnailResourceType } from '@shared/thumbnail'
19	import type {
20	ModelUsageByHour,
21	ModelUsagePeriod,
22	ModelUsageStats,
23	ModelUsageTotals
24	} from '@shared/model-usage'
25	import type { AnimationPreferencesPayload } from '@shared/generation'
26	import { normalizeThinkingParameterMode } from '@shared/model-config'
27	import { requirePersistedSlideSize, type SlideSizePresetId } from '@shared/slide-size'
28	import type { HtmlEditDocument, HtmlEditMessage, HtmlEditVersion } from './schema'
29
30	type SessionStatus = 'active' \| 'completed' \| 'failed' \| 'archived'
31	type MessageRole = 'user' \| 'assistant' \| 'system' \| 'tool'
32	type MessageType = 'text' \| 'tool_call' \| 'tool_result' \| 'stream_chunk'
33	type ChatScope = 'main' \| 'page'
34	type StyleSource = 'builtin' \| 'custom' \| 'override'
35	type GenerationRunMode =
36	\| 'generate'
37	\| 'retry'
38	\| 'edit'
39	\| 'import'
40	\| 'addPage'
41	\| 'retrySinglePage'
42	\| 'style-switch'
43	\| 'page-beautify'
44	type GenerationRunStatus = 'running' \| 'completed' \| 'failed' \| 'partial'
45	export type SessionJobKind =
46	\| 'standard'
47	\| 'template'
48	\| 'retry'
49	\| 'add-page'
50	\| 'single-page-retry'
51	\| 'page-edit'
52	\| 'deck-edit'
53	\| 'style-switch'
54	\| 'page-beautify'
55	export type SessionJobStatus = 'pending' \| 'active' \| 'finished' \| 'aborted'
56	type GenerationPageStatus = 'pending' \| 'running' \| 'completed' \| 'failed'
57	type SessionPageStatus = schema.SessionPageStatus
58	type SourcePageSkeletonRole = 'chapter-divider' \| 'content'
59	type SourcePageSkeletonConfidence = 'high' \| 'medium' \| 'low'
60	type SessionOperationType =
61	\| 'generate'
62	\| 'edit'
63	\| 'addPage'
64	\| 'retry'
65	\| 'import'
66	\| 'rollback'
67	\| 'reorder'
68	\| 'delete'
69	type SessionOperationScope = 'session' \| 'deck' \| 'page' \| 'selector' \| 'shell'
70	type SessionOperationStatus = 'committing' \| 'completed' \| 'failed' \| 'noop'
71
72	export interface Session {
73	id: string
74	title: string
75	topic: string \| null
76	styleId: string \| null
77	page_count: number \| null
78	slideSizeId?: SlideSizePresetId
79	slideWidth?: number
80	slideHeight?: number
81	reference_document_path: string \| null
82	referenceDocumentPath?: string \| null
83	status: SessionStatus
84	provider: string
85	model: string
86	created_at: number
87	updated_at: number
88	metadata: string \| null
89	designContract?: string \| null
90	currentOperationId?: string \| null
91	currentCommit?: string \| null
92	}
93
94	export interface Message {
95	id: string
96	session_id: string
97	chat_scope: ChatScope
98	page_id: string \| null
99	selector: string \| null
100	image_paths: string[] \| null
101	video_paths: string[] \| null
102	role: MessageRole
103	content: string
104	type: MessageType
105	tool_name: string \| null
106	tool_call_id: string \| null
107	token_count: number \| null
108	run_model: string \| null
109	created_at: number
110	}
111
112	interface MemorySummary {
113	id: string
114	session_id: string
115	message_range_start: number
116	message_range_end: number
117	summary: string
118	token_count: number \| null
119	created_at: number
120	}
121
122	interface UserPreference {
123	key: string
124	value: unknown
125	confidence: number
126	source_sessions: string[]
127	created_at: number
128	updated_at: number
129	last_used_at: number \| null
130	}
131
132	interface Project {
133	id: string
134	session_id: string
135	title: string
136	output_path: string
137	root_path: string \| null
138	file_count: number
139	total_size: number
140	status: 'draft' \| 'published' \| 'exported'
141	created_at: number
142	updated_at: number
143	}
144
145	export interface GenerationRunRecord {
146	id: string
147	session_id: string
148	mode: GenerationRunMode
149	status: GenerationRunStatus
150	total_pages: number
151	error: string \| null
152	metadata: string \| null
153	animation_preferences: string \| null
154	model_config_id: string \| null
155	created_at: number
156	updated_at: number
157	}
158
159	export interface SessionJobRecord {
160	id: string
161	session_id: string
162	kind: SessionJobKind
163	previous_session_status: SessionStatus
164	target_page_id: string \| null
165	target_page_number: number \| null
166	selector: string \| null
167	total_pages: number \| null
168	status: SessionJobStatus
169	abort_reason: string \| null
170	created_at: number
171	activated_at: number \| null
172	updated_at: number
173	finished_at: number \| null
174	}
175
176	type GenerationRunCreateData = {
177	id?: string
178	sessionId: string
179	mode: GenerationRunMode
180	totalPages: number
181	metadata?: unknown
182	animationPreferences?: AnimationPreferencesPayload \| null
183	modelConfigId?: string \| null
184	}
185
186	type SessionJobCreateData = {
187	id: string
188	sessionId: string
189	kind: SessionJobKind
190	status: Extract<SessionJobStatus, 'pending' \| 'active'>
191	previousSessionStatus: SessionStatus
192	targetPageId?: string
193	targetPageNumber?: number
194	selector?: string
195	totalPages?: number
196	}
197
198	type GenerationPageCreateData = {
199	pageId: string
200	pageNumber: number
201	title: string
202	contentOutline?: string \| null
203	layoutIntent?: string \| null
204	htmlPath?: string \| null
205	status?: Extract<GenerationPageStatus, 'pending' \| 'running'>
206	error?: string \| null
207	retryCount?: number
208	}
209
210	export interface GenerationPageRecord {
211	id: string
212	run_id: string
213	session_id: string
214	page_id: string
215	page_number: number
216	title: string
217	content_outline: string \| null
218	layout_intent: string \| null
219	html_path: string \| null
220	status: GenerationPageStatus
221	error: string \| null
222	retry_count: number
223	created_at: number
224	updated_at: number
225	}
226
227	export interface SessionPageRecord {
228	id: string
229	session_id: string
230	legacy_page_id: string \| null
231	file_slug: string
232	page_number: number
233	title: string
234	html_path: string
235	status: SessionPageStatus
236	error: string \| null
237	created_at: number
238	updated_at: number
239	deleted_at: number \| null
240	}
241
242	export type ThumbnailStatus = 'queued' \| 'running' \| 'completed' \| 'failed'
243
244	export interface ThumbnailRecord {
245	key: string
246	resourceType: HtmlThumbnailResourceType
247	resourceId: string
248	variant: string
249	sourcePath: string
250	sourceMtimeMs: number
251	signature: string
252	thumbnailPath: string
253	status: ThumbnailStatus
254	error: string \| null
255	createdAt: number
256	updatedAt: number
257	}
258
259	export interface SourcePageSkeletonRecord {
260	id: string
261	session_id: string
262	page_number: number
263	title: string
264	role: SourcePageSkeletonRole
265	source_document_path: string
266	source_document_name: string \| null
267	source_heading: string
268	heading_level: number
269	line_start: number
270	line_end: number
271	reason: string \| null
272	confidence: SourcePageSkeletonConfidence
273	created_at: number
274	updated_at: number
275	}
276
277	export interface SessionPageInput {
278	id: string
279	sessionId: string
280	legacyPageId?: string \| null
281	fileSlug: string
282	pageNumber: number
283	title: string
284	htmlPath: string
285	status?: SessionPageStatus
286	error?: string \| null
287	}
288
289	export interface SessionWithPageCount {
290	session: Session
291	pageCount: number
292	}
293
294	export const sessionPageRecordToInput = (page: SessionPageRecord): SessionPageInput => ({
295	id: page.id,
296	sessionId: page.session_id,
297	legacyPageId: page.legacy_page_id,
298	fileSlug: page.file_slug,
299	pageNumber: page.page_number,
300	title: page.title,
301	htmlPath: page.html_path,
302	status: page.status,
303	error: page.error
304	})
305
306	export interface StyleRow {
307	id: string
308	style: string
309	styleName: string
310	styleNameZh: string
311	styleNameEn: string
312	description: string
313	category: string
314	aliases: string // JSON array
315	source: StyleSource
316	styleSkill: string // plain markdown
317	version: string
318	styleCase: string
319	packageDir: string
320	active: boolean
321	favoriteAt: number \| null
322	createdAt: number
323	updatedAt: number
324	}
325
326	export interface SessionStyleSnapshotRow {
327	id: string
328	sessionId: string
329	styleId: string
330	styleKey: string
331	styleName: string
332	styleNameZh: string
333	styleNameEn: string
334	description: string
335	category: string
336	aliases: string
337	source: StyleSource
338	version: string
339	styleCase: string
340	packageDir: string
341	styleSkill: string
342	createdAt: number
343	}
344
345	export interface ModelConfigRow {
346	id: string
347	name: string
348	provider: string
349	model: string
350	apiKey: string
351	baseUrl: string
352	maxTokens: number
353	disableTemperature: number
354	thinkingParameterMode: string
355	active: number
356	createdAt: number
357	updatedAt: number
358	}
359
360	export interface ImageModelConfigRow {
361	id: string
362	name: string
363	provider: string
364	active: number
365	modelConfig: string
366	createdAt: number
367	updatedAt: number
368	}
369
370	export interface ImageGenerationHistoryRow {
371	id: string
372	sessionId: string
373	pageId: string
374	prompt: string
375	imagePaths: string
376	modelConfigId: string
377	provider: string
378	model: string
379	createdAt: number
380	}
381
382	export interface SessionOperationRecord {
383	id: string
384	session_id: string
385	type: SessionOperationType
386	status: SessionOperationStatus
387	scope: SessionOperationScope \| null
388	prompt: string \| null
389	parent_operation_id: string \| null
390	before_commit: string \| null
391	after_commit: string \| null
392	target_operation_id: string \| null
393	target_commit: string \| null
394	changed_files_json: string
395	changed_pages_json: string
396	tracked_files_json: string
397	metadata_json: string
398	created_at: number
399	completed_at: number \| null
400	}
401
402	export interface SessionOperationPageRecord {
403	id: string
404	operation_id: string
405	session_id: string
406	page_id: string
407	legacy_page_id: string \| null
408	file_slug: string
409	page_number: number
410	title: string
411	html_path: string
412	status: SessionPageStatus
413	error: string \| null
414	created_at: number
415	updated_at: number
416	}
417
418	export class PPTDatabase {
419	private db: ReturnType<typeof drizzle>
420	private client: ReturnType<typeof createClient>
421	private _storagePath: string \| null = null
422	private _initialized = false
423	private _stylesCache: StyleRow[] = []
424
425	constructor(dbPath?: string) {
426	const defaultPath = is.dev
427	? path.join(process.cwd(), 'ohmyppt.dev.db')
428	: path.join(app.getPath('userData'), 'ohmyppt.db')
429	const resolvedPath = dbPath \|\| defaultPath
430
431	const dir = path.dirname(resolvedPath)
432	if (!fs.existsSync(dir)) {
433	fs.mkdirSync(dir, { recursive: true })
434	}
435
436	const url = resolvedPath.startsWith('file:') ? resolvedPath : `file:${resolvedPath}`
437
438	this.client = createClient({ url })
439	this.db = drizzle(this.client, { schema })
440	this._storagePath = null
441	}
442
443	async init(): Promise<void> {
444	if (this._initialized) return
445	await runDatabasePatches({
446	client: this.client,
447	db: this.db,
448	resolveStoragePath: async () =>
449	(await this.getSetting<string>('storage_path').catch(() => '')) \|\| ''
450	})
451	await this._refreshStylesCache()
452	this._initialized = true
453	}
454
455	getStoragePath(): string {
456	return this._storagePath \|\| ''
457	}
458
459	async setStoragePath(storagePath: string): Promise<void> {
460	await this.setSetting('storage_path', storagePath)
461	this._storagePath = storagePath
462	if (!fs.existsSync(storagePath)) {
463	fs.mkdirSync(storagePath, { recursive: true })
464	}
465	}
466
467	async close(): Promise<void> {
468	await this.client.close()
469	this._initialized = false
470	}
471
472	// ========== HTML Editor ==========
473
474	async createHtmlEditDocument(data: {
475	id: string
476	title: string
477	sourcePath?: string \| null
478	htmlPath: string
479	designWidth: number
480	createdAt: number
481	updatedAt: number
482	}): Promise<void> {
483	await this.db.insert(schema.htmlEditDocuments).values({
484	id: data.id,
485	title: data.title,
486	sourcePath: data.sourcePath ?? null,
487	htmlPath: data.htmlPath,
488	designWidth: data.designWidth,
489	createdAt: data.createdAt,
490	updatedAt: data.updatedAt
491	})
492	}
493
494	async touchHtmlEditDocument(docId: string, updatedAt: number): Promise<void> {
495	await this.db
496	.update(schema.htmlEditDocuments)
497	.set({ updatedAt })
498	.where(eq(schema.htmlEditDocuments.id, docId))
499	}
500
501	async createHtmlEditMessage(data: {
502	id: string
503	docId: string
504	role: 'user' \| 'assistant'
505	content: string
506	intent?: string \| null
507	planJson?: string \| null
508	requiresConfirmation?: boolean
509	selectedElement?: {
510	selector: string
511	label?: string
512	elementTag?: string
513	elementText?: string
514	} \| null
515	createdAt: number
516	}): Promise<void> {
517	const selectedElement = data.selectedElement?.selector ? data.selectedElement : null
518	await this.db
519	.insert(schema.htmlEditMessages)
520	.values({
521	id: data.id,
522	docId: data.docId,
523	role: data.role,
524	content: data.content,
525	intent: data.intent ?? null,
526	planJson: data.planJson ?? null,
527	requiresConfirmation: data.requiresConfirmation ? 1 : 0,
528	selectedSelector: selectedElement?.selector.slice(0, 2_000) ?? null,
529	selectedLabel: selectedElement?.label?.slice(0, 500) ?? null,
530	selectedElementTag: selectedElement?.elementTag?.slice(0, 80) ?? null,
531	selectedElementText: selectedElement?.elementText?.slice(0, 2_000) ?? null,
532	createdAt: data.createdAt
533	})
534	.run()
535	}
536
537	async listHtmlEditMessages(docId: string, limit = 100): Promise<HtmlEditMessage[]> {
538	const safeLimit = Math.max(1, Math.min(Math.floor(limit), 500))
539	const rows = await this.db
540	.select()
541	.from(schema.htmlEditMessages)
542	.where(eq(schema.htmlEditMessages.docId, docId))
543	.orderBy(desc(schema.htmlEditMessages.createdAt))
544	.limit(safeLimit)
545	.all()
546	return rows.reverse()
547	}
548
549	async clearHtmlEditMessages(docId: string): Promise<void> {
550	await this.db
551	.delete(schema.htmlEditMessages)
552	.where(eq(schema.htmlEditMessages.docId, docId))
553	.run()
554	}
555
556	async createHtmlEditVersion(data: {
557	id: string
558	docId: string
559	commitSha: string
560	message: string
561	createdAt: number
562	}): Promise<void> {
563	await this.db.insert(schema.htmlEditVersions).values({
564	id: data.id,
565	docId: data.docId,
566	commitSha: data.commitSha,
567	message: data.message,
568	createdAt: data.createdAt
569	})
570	}
571
572	async createHtmlEditDocumentWithVersion(data: {
573	document: {
574	id: string
575	title: string
576	sourcePath?: string \| null
577	htmlPath: string
578	designWidth: number
579	createdAt: number
580	updatedAt: number
581	}
582	version: {
583	id: string
584	commitSha: string
585	message: string
586	createdAt: number
587	}
588	}): Promise<void> {
589	await this.db.transaction(async (tx) => {
590	await tx.insert(schema.htmlEditDocuments).values({
591	id: data.document.id,
592	title: data.document.title,
593	sourcePath: data.document.sourcePath ?? null,
594	htmlPath: data.document.htmlPath,
595	designWidth: data.document.designWidth,
596	createdAt: data.document.createdAt,
597	updatedAt: data.document.updatedAt
598	})
599	await tx.insert(schema.htmlEditVersions).values({
600	id: data.version.id,
601	docId: data.document.id,
602	commitSha: data.version.commitSha,
603	message: data.version.message,
604	createdAt: data.version.createdAt
605	})
606	})
607	}
608
609	async createHtmlEditVersionAndTouch(data: {
610	id: string
611	docId: string
612	commitSha: string
613	message: string
614	createdAt: number
615	}): Promise<void> {
616	await this.db.transaction(async (tx) => {
617	await tx.insert(schema.htmlEditVersions).values({
618	id: data.id,
619	docId: data.docId,
620	commitSha: data.commitSha,
621	message: data.message,
622	createdAt: data.createdAt
623	})
624	await tx
625	.update(schema.htmlEditDocuments)
626	.set({ updatedAt: data.createdAt })
627	.where(eq(schema.htmlEditDocuments.id, data.docId))
628	})
629	}
630
631	async listHtmlEditVersions(docId: string): Promise<HtmlEditVersion[]> {
632	return this.db
633	.select()
634	.from(schema.htmlEditVersions)
635	.where(eq(schema.htmlEditVersions.docId, docId))
636	.orderBy(desc(schema.htmlEditVersions.createdAt))
637	}
638
639	async getHtmlEditVersion(versionId: string): Promise<HtmlEditVersion \| undefined> {
640	const rows = await this.db
641	.select()
642	.from(schema.htmlEditVersions)
643	.where(eq(schema.htmlEditVersions.id, versionId))
644	.limit(1)
645	return rows[0]
646	}
647
648	async listHtmlEditDocuments(): Promise<HtmlEditDocument[]> {
649	return this.db
650	.select()
651	.from(schema.htmlEditDocuments)
652	.orderBy(desc(schema.htmlEditDocuments.updatedAt))
653	}
654
655	async getHtmlEditDocument(docId: string): Promise<HtmlEditDocument \| undefined> {
656	const rows = await this.db
657	.select()
658	.from(schema.htmlEditDocuments)
659	.where(eq(schema.htmlEditDocuments.id, docId))
660	.limit(1)
661	return rows[0]
662	}
663
664	/** 删除文档的数据库记录（含版本行）。不删磁盘文件——文件留存供审计/恢复。 */
665	async deleteHtmlEditDocument(docId: string): Promise<void> {
666	await this.db.delete(schema.htmlEditVersions).where(eq(schema.htmlEditVersions.docId, docId))
667	await this.db.delete(schema.htmlEditDocuments).where(eq(schema.htmlEditDocuments.id, docId))
668	}
669
670	// ========== Session ==========
671
672	async createSession(data: {
673	id?: string
674	title: string
675	topic?: string
676	styleId?: string
677	pageCount?: number
678	slideSizeId?: SlideSizePresetId
679	slideWidth?: number
680	slideHeight?: number
681	referenceDocumentPath?: string \| null
682	provider: string
683	model: string
684	}): Promise<string> {
685	const id = data.id \|\| crypto.randomUUID()
686	const now = Math.floor(Date.now() / 1000)
687
688	const slideSize = requirePersistedSlideSize({
689	id: data.slideSizeId,
690	width: data.slideWidth,
691	height: data.slideHeight
692	})
693
694	await this.db
695	.insert(schema.sessions)
696	.values({
697	id,
698	title: data.title,
699	topic: data.topic \|\| null,
700	styleId: data.styleId \|\| null,
701	pageCount: data.pageCount \|\| null,
702	slideSizeId: slideSize.id,
703	slideWidth: slideSize.width,
704	slideHeight: slideSize.height,
705	referenceDocumentPath: data.referenceDocumentPath \|\| null,
706	status: 'active',
707	provider: data.provider,
708	model: data.model,
709	createdAt: now,
710	updatedAt: now,
711	metadata: null
712	})
713	.run()
714
715	if (this._stylesCache.length > 0) {
716	await this.createSessionStyleSnapshot(id, data.styleId)
717	}
718
719	return id
720	}
721
722	async getSession(sessionId: string): Promise<Session \| undefined> {
723	const result = await this.db
724	.select()
725	.from(schema.sessions)
726	.where(eq(schema.sessions.id, sessionId))
727	.get()
728	return result as unknown as Session \| undefined
729	}
730
731	async updateSessionHistoryPointer(args: {
732	sessionId: string
733	operationId: string \| null
734	commit: string \| null
735	}): Promise<void> {
736	await this.db
737	.update(schema.sessions)
738	.set({
739	currentOperationId: args.operationId,
740	currentCommit: args.commit,
741	updatedAt: Math.floor(Date.now() / 1000)
742	})
743	.where(eq(schema.sessions.id, args.sessionId))
744	.run()
745	}
746
747	async updateSessionStatus(sessionId: string, status: SessionStatus): Promise<void> {
748	const now = Math.floor(Date.now() / 1000)
749	await this.db
750	.update(schema.sessions)
751	.set({ status, updatedAt: now })
752	.where(eq(schema.sessions.id, sessionId))
753	.run()
754	}
755
756	async updateSessionMetadata(sessionId: string, metadata: object): Promise<void> {
757	await this.db
758	.update(schema.sessions)
759	.set({ metadata: JSON.stringify(metadata), updatedAt: Math.floor(Date.now() / 1000) })
760	.where(eq(schema.sessions.id, sessionId))
761	.run()
762	}
763
764	async updateSessionTitle(sessionId: string, title: string): Promise<void> {
765	const updatedAt = Math.floor(Date.now() / 1000)
766	await this.db
767	.update(schema.sessions)
768	.set({ title, updatedAt })
769	.where(eq(schema.sessions.id, sessionId))
770	.run()
771	await this.db
772	.update(schema.projects)
773	.set({ title, updatedAt })
774	.where(eq(schema.projects.sessionId, sessionId))
775	.run()
776	}
777
778	async updateSessionStyleId(sessionId: string, styleId: string): Promise<void> {
779	const now = Math.floor(Date.now() / 1000)
780	await this.db
781	.update(schema.sessions)
782	.set({ styleId, updatedAt: now })
783	.where(eq(schema.sessions.id, sessionId))
784	.run()
785	if (this._stylesCache.length > 0) {
786	await this.replaceSessionStyleSnapshot(sessionId, styleId)
787	}
788	}
789
790	async restoreSessionStyleState(
791	sessionId: string,
792	styleId: string \| null,
793	snapshot?: SessionStyleSnapshotRow
794	): Promise<void> {
795	const now = Math.floor(Date.now() / 1000)
796	await this.db.transaction(async (tx) => {
797	await tx
798	.update(schema.sessions)
799	.set({ styleId, updatedAt: now })
800	.where(eq(schema.sessions.id, sessionId))
801	.run()
802	await tx
803	.delete(schema.sessionStyleSnapshots)
804	.where(eq(schema.sessionStyleSnapshots.sessionId, sessionId))
805	.run()
806	if (!snapshot) return
807	await tx
808	.insert(schema.sessionStyleSnapshots)
809	.values({
810	id: snapshot.id,
811	sessionId,
812	styleId: snapshot.styleId,
813	styleKey: snapshot.styleKey,
814	styleName: snapshot.styleName,
815	styleNameZh: snapshot.styleNameZh,
816	styleNameEn: snapshot.styleNameEn,
817	description: snapshot.description,
818	category: snapshot.category,
819	aliases: snapshot.aliases,
820	source: snapshot.source,
821	version: snapshot.version,
822	styleCase: snapshot.styleCase,
823	packageDir: snapshot.packageDir,
824	styleSkill: snapshot.styleSkill,
825	createdAt: snapshot.createdAt
826	})
827	.run()
828	})
829	}
830
831	async updateSessionDesignContract(sessionId: string, designContract: unknown): Promise<void> {
832	await this.db
833	.update(schema.sessions)
834	.set({
835	designContract: designContract ? JSON.stringify(designContract) : null,
836	updatedAt: Math.floor(Date.now() / 1000)
837	})
838	.where(eq(schema.sessions.id, sessionId))
839	.run()
840	}
841
842	async listSessions(limit = 50, offset = 0): Promise<Session[]> {
843	const results = await this.db
844	.select()
845	.from(schema.sessions)
846	.where(ne(schema.sessions.status, 'archived'))
847	.orderBy(desc(schema.sessions.updatedAt))
848	.limit(limit)
849	.offset(offset)
850	.all()
851
852	return results as unknown as Session[]
853	}
854
855	async listSessionsWithPageCounts(limit = 50, offset = 0): Promise<SessionWithPageCount[]> {
856	const rows = await this.db
857	.select({
858	session: schema.sessions,
859	pageCount: count(schema.sessionPages.id)
860	})
861	.from(schema.sessions)
862	.leftJoin(
863	schema.sessionPages,
864	and(
865	eq(schema.sessionPages.sessionId, schema.sessions.id),
866	isNull(schema.sessionPages.deletedAt)
867	)
868	)
869	.where(ne(schema.sessions.status, 'archived'))
870	.groupBy(schema.sessions.id)
871	.orderBy(desc(schema.sessions.updatedAt))
872	.limit(limit)
873	.offset(offset)
874	.all()
875
876	return rows.map((row) => ({
877	session: row.session as unknown as Session,
878	pageCount: Number(row.pageCount \|\| 0)
879	}))
880	}
881
882	async deleteSession(sessionId: string): Promise<void> {
883	await this.db.transaction(async (tx) => {
884	await tx
885	.delete(schema.sessionOperationPages)
886	.where(eq(schema.sessionOperationPages.sessionId, sessionId))
887	.run()
888	await tx
889	.delete(schema.sessionOperations)
890	.where(eq(schema.sessionOperations.sessionId, sessionId))
891	.run()
892	await tx
893	.delete(schema.sourcePageSkeletons)
894	.where(eq(schema.sourcePageSkeletons.sessionId, sessionId))
895	.run()
896	await tx.delete(schema.sessionPages).where(eq(schema.sessionPages.sessionId, sessionId)).run()
897	await tx
898	.delete(schema.imageGenerationHistories)
899	.where(eq(schema.imageGenerationHistories.sessionId, sessionId))
900	.run()
901	await tx
902	.delete(schema.memorySummaries)
903	.where(eq(schema.memorySummaries.sessionId, sessionId))
904	.run()
905	await tx.delete(schema.messages).where(eq(schema.messages.sessionId, sessionId)).run()
906	await tx
907	.delete(schema.generationPages)
908	.where(eq(schema.generationPages.sessionId, sessionId))
909	.run()
910	await tx
911	.delete(schema.generationRuns)
912	.where(eq(schema.generationRuns.sessionId, sessionId))
913	.run()
914	await tx.delete(schema.projects).where(eq(schema.projects.sessionId, sessionId)).run()
915	await tx.delete(schema.sessions).where(eq(schema.sessions.id, sessionId)).run()
916	})
917	}
918
919	// ========== Generation Records ==========
920
921	private normalizeGenerationRunRow(row: Record<string, unknown>): GenerationRunRecord {
922	return {
923	id: String(row.id \|\| ''),
924	session_id: String(row.sessionId ?? row.session_id ?? ''),
925	mode: String(row.mode \|\| 'generate') as GenerationRunMode,
926	status: String(row.status \|\| 'running') as GenerationRunStatus,
927	total_pages: Number(row.totalPages ?? row.total_pages ?? 0) \|\| 0,
928	error: typeof row.error === 'string' ? String(row.error) : null,
929	metadata: typeof row.metadata === 'string' ? String(row.metadata) : null,
930	animation_preferences:
931	typeof (row.animationPreferences ?? row.animation_preferences) === 'string'
932	? String(row.animationPreferences ?? row.animation_preferences)
933	: null,
934	model_config_id:
935	typeof (row.modelConfigId ?? row.model_config_id) === 'string'
936	? String(row.modelConfigId ?? row.model_config_id)
937	: null,
938	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
939	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0
940	}
941	}
942
943	private normalizeSessionJobRow(row: Record<string, unknown>): SessionJobRecord {
944	const status = String(row.status \|\| 'pending')
945	const kind = String(row.kind \|\| 'standard')
946	const previousSessionStatus = String(
947	row.previousSessionStatus ?? row.previous_session_status ?? 'active'
948	)
949	return {
950	id: String(row.id \|\| ''),
951	session_id: String(row.sessionId ?? row.session_id ?? ''),
952	kind: (kind === 'template' \|\|
953	kind === 'retry' \|\|
954	kind === 'add-page' \|\|
955	kind === 'single-page-retry' \|\|
956	kind === 'page-edit' \|\|
957	kind === 'deck-edit' \|\|
958	kind === 'style-switch' \|\|
959	kind === 'page-beautify'
960	? kind
961	: 'standard') as SessionJobKind,
962	previous_session_status:
963	previousSessionStatus === 'completed' \|\|
964	previousSessionStatus === 'failed' \|\|
965	previousSessionStatus === 'archived'
966	? previousSessionStatus
967	: 'active',
968	target_page_id:
969	typeof (row.targetPageId ?? row.target_page_id) === 'string' &&
970	String(row.targetPageId ?? row.target_page_id).trim().length > 0
971	? String(row.targetPageId ?? row.target_page_id)
972	: null,
973	target_page_number:
974	typeof (row.targetPageNumber ?? row.target_page_number) === 'number'
975	? Number(row.targetPageNumber ?? row.target_page_number)
976	: null,
977	selector:
978	typeof row.selector === 'string' && row.selector.trim().length > 0 ? row.selector : null,
979	total_pages:
980	typeof (row.totalPages ?? row.total_pages) === 'number'
981	? Math.max(1, Number(row.totalPages ?? row.total_pages) \|\| 1)
982	: null,
983	status: (status === 'active' \|\| status === 'finished' \|\| status === 'aborted'
984	? status
985	: 'pending') as SessionJobStatus,
986	abort_reason:
987	typeof (row.abortReason ?? row.abort_reason) === 'string'
988	? String(row.abortReason ?? row.abort_reason)
989	: null,
990	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
991	activated_at:
992	typeof (row.activatedAt ?? row.activated_at) === 'number'
993	? Number(row.activatedAt ?? row.activated_at)
994	: null,
995	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0,
996	finished_at:
997	typeof (row.finishedAt ?? row.finished_at) === 'number'
998	? Number(row.finishedAt ?? row.finished_at)
999	: null
1000	}
1001	}
1002
1003	private normalizeGenerationPageRow(row: Record<string, unknown>): GenerationPageRecord {
1004	return {
1005	id: String(row.id \|\| ''),
1006	run_id: String(row.runId ?? row.run_id ?? ''),
1007	session_id: String(row.sessionId ?? row.session_id ?? ''),
1008	page_id: String(row.pageId ?? row.page_id ?? ''),
1009	page_number: Number(row.pageNumber ?? row.page_number ?? 0) \|\| 0,
1010	title: String(row.title \|\| ''),
1011	content_outline:
1012	typeof (row.contentOutline ?? row.content_outline) === 'string'
1013	? String(row.contentOutline ?? row.content_outline)
1014	: null,
1015	layout_intent:
1016	typeof (row.layoutIntent ?? row.layout_intent) === 'string'
1017	? String(row.layoutIntent ?? row.layout_intent)
1018	: null,
1019	html_path:
1020	typeof (row.htmlPath ?? row.html_path) === 'string'
1021	? String(row.htmlPath ?? row.html_path)
1022	: null,
1023	status: String(row.status \|\| 'pending') as GenerationPageStatus,
1024	error: typeof row.error === 'string' ? String(row.error) : null,
1025	retry_count: Number(row.retryCount ?? row.retry_count ?? 0) \|\| 0,
1026	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1027	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0
1028	}
1029	}
1030
1031	private normalizeSessionPageRow(row: Record<string, unknown>): SessionPageRecord {
1032	return {
1033	id: String(row.id \|\| ''),
1034	session_id: String(row.sessionId ?? row.session_id ?? ''),
1035	legacy_page_id:
1036	typeof (row.legacyPageId ?? row.legacy_page_id) === 'string'
1037	? String(row.legacyPageId ?? row.legacy_page_id)
1038	: null,
1039	file_slug: String(row.fileSlug ?? row.file_slug ?? ''),
1040	page_number: Number(row.pageNumber ?? row.page_number ?? 0) \|\| 0,
1041	title: String(row.title \|\| ''),
1042	html_path: String(row.htmlPath ?? row.html_path ?? ''),
1043	status: String(row.status \|\| 'pending') as SessionPageStatus,
1044	error: typeof row.error === 'string' ? row.error : null,
1045	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1046	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0,
1047	deleted_at:
1048	typeof (row.deletedAt ?? row.deleted_at) === 'number'
1049	? Number(row.deletedAt ?? row.deleted_at)
1050	: null
1051	}
1052	}
1053
1054	private normalizeSourcePageSkeletonRow(row: Record<string, unknown>): SourcePageSkeletonRecord {
1055	return {
1056	id: String(row.id \|\| ''),
1057	session_id: String(row.sessionId ?? row.session_id ?? ''),
1058	page_number: Number(row.pageNumber ?? row.page_number ?? 0) \|\| 0,
1059	title: String(row.title \|\| ''),
1060	role: String(row.role \|\| 'content') === 'chapter-divider' ? 'chapter-divider' : 'content',
1061	source_document_path: String(row.sourceDocumentPath ?? row.source_document_path ?? ''),
1062	source_document_name:
1063	typeof (row.sourceDocumentName ?? row.source_document_name) === 'string'
1064	? String(row.sourceDocumentName ?? row.source_document_name)
1065	: null,
1066	source_heading: String(row.sourceHeading ?? row.source_heading ?? ''),
1067	heading_level: Number(row.headingLevel ?? row.heading_level ?? 0) \|\| 1,
1068	line_start: Number(row.lineStart ?? row.line_start ?? 0) \|\| 1,
1069	line_end: Number(row.lineEnd ?? row.line_end ?? 0) \|\| 1,
1070	reason:
1071	typeof row.reason === 'string' && row.reason.trim().length > 0 ? String(row.reason) : null,
1072	confidence: row.confidence === 'medium' \|\| row.confidence === 'low' ? row.confidence : 'high',
1073	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1074	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0
1075	}
1076	}
1077
1078	async createGenerationRun(data: GenerationRunCreateData): Promise<string> {
1079	const id = data.id \|\| crypto.randomUUID()
1080	const now = Math.floor(Date.now() / 1000)
1081	const animationPreferences = data.animationPreferences
1082	? JSON.stringify(data.animationPreferences)
1083	: null
1084	await this.db
1085	.insert(schema.generationRuns)
1086	.values({
1087	id,
1088	sessionId: data.sessionId,
1089	mode: data.mode,
1090	status: 'running',
1091	totalPages: Math.max(0, Math.floor(data.totalPages \|\| 0)),
1092	error: null,
1093	metadata: data.metadata ? JSON.stringify(data.metadata) : null,
1094	animationPreferences,
1095	modelConfigId:
1096	typeof data.modelConfigId === 'string' && data.modelConfigId.trim().length > 0
1097	? data.modelConfigId.trim()
1098	: null,
1099	createdAt: now,
1100	updatedAt: now
1101	})
1102	.onConflictDoUpdate({
1103	target: schema.generationRuns.id,
1104	set: {
1105	sessionId: data.sessionId,
1106	mode: data.mode,
1107	status: 'running',
1108	totalPages: Math.max(0, Math.floor(data.totalPages \|\| 0)),
1109	error: null,
1110	metadata: data.metadata ? JSON.stringify(data.metadata) : null,
1111	animationPreferences,
1112	modelConfigId:
1113	typeof data.modelConfigId === 'string' && data.modelConfigId.trim().length > 0
1114	? data.modelConfigId.trim()
1115	: null,
1116	updatedAt: now
1117	}
1118	})
1119	.run()
1120	return id
1121	}
1122
1123	async createGenerationRunWithSessionJob(data: {
1124	run: GenerationRunCreateData & { id: string }
1125	job: SessionJobCreateData
1126	}): Promise<void> {
1127	await this.createGenerationRunWithSessionJobAndPages({ ...data, pages: [] })
1128	}
1129
1130	async createGenerationRunWithSessionJobAndPages(data: {
1131	run: GenerationRunCreateData & { id: string }
1132	job: SessionJobCreateData
1133	pages: GenerationPageCreateData[]
1134	}): Promise<void> {
1135	if (data.run.id !== data.job.id) {
1136	throw new Error('generation run and session job must share the same id')
1137	}
1138	if (data.run.sessionId !== data.job.sessionId) {
1139	throw new Error('generation run and session job must belong to the same session')
1140	}
1141
1142	const now = Math.floor(Date.now() / 1000)
1143	const animationPreferences = data.run.animationPreferences
1144	? JSON.stringify(data.run.animationPreferences)
1145	: null
1146	const runTotalPages = Math.max(0, Math.floor(data.run.totalPages \|\| 0))
1147	const modelConfigId =
1148	typeof data.run.modelConfigId === 'string' && data.run.modelConfigId.trim().length > 0
1149	? data.run.modelConfigId.trim()
1150	: null
1151	const jobTotalPages =
1152	typeof data.job.totalPages === 'number' && Number.isFinite(data.job.totalPages)
1153	? Math.max(1, Math.floor(data.job.totalPages))
1154	: null
1155
1156	await this.db.transaction(async (tx) => {
1157	await tx.insert(schema.generationRuns).values({
1158	id: data.run.id,
1159	sessionId: data.run.sessionId,
1160	mode: data.run.mode,
1161	status: 'running',
1162	totalPages: runTotalPages,
1163	error: null,
1164	metadata: data.run.metadata ? JSON.stringify(data.run.metadata) : null,
1165	animationPreferences,
1166	modelConfigId,
1167	createdAt: now,
1168	updatedAt: now
1169	})
1170	await tx.insert(schema.sessionJobs).values({
1171	id: data.job.id,
1172	sessionId: data.job.sessionId,
1173	kind: data.job.kind,
1174	previousSessionStatus: data.job.previousSessionStatus,
1175	targetPageId: data.job.targetPageId \|\| null,
1176	targetPageNumber: data.job.targetPageNumber ?? null,
1177	selector: data.job.selector \|\| null,
1178	totalPages: jobTotalPages,
1179	status: data.job.status,
1180	abortReason: null,
1181	createdAt: now,
1182	activatedAt: data.job.status === 'active' ? now : null,
1183	updatedAt: now,
1184	finishedAt: null
1185	})
1186
1187	if (data.pages.length === 0) return
1188	await tx.insert(schema.generationPages).values(
1189	data.pages.map((page) => ({
1190	id: `${data.run.id}:${page.pageId}`,
1191	runId: data.run.id,
1192	sessionId: data.run.sessionId,
1193	pageId: page.pageId,
1194	pageNumber: Math.max(1, Math.floor(page.pageNumber)),
1195	title: page.title,
1196	contentOutline: page.contentOutline \|\| null,
1197	layoutIntent: page.layoutIntent \|\| null,
1198	htmlPath: page.htmlPath \|\| null,
1199	status: page.status \|\| 'pending',
1200	error: page.error \|\| null,
1201	retryCount: Math.max(0, Math.floor(page.retryCount \|\| 0)),
1202	createdAt: now,
1203	updatedAt: now
1204	}))
1205	)
1206	})
1207	}
1208
1209	async updateSessionJobStatus(
1210	jobId: string,
1211	status: SessionJobStatus,
1212	options?: { abortReason?: string \| null }
1213	): Promise<void> {
1214	const now = Math.floor(Date.now() / 1000)
1215	const set: Record<string, unknown> = {
1216	status,
1217	updatedAt: now
1218	}
1219	if (status === 'active') {
1220	set.activatedAt = now
1221	set.finishedAt = null
1222	set.abortReason = null
1223	}
1224	if (status === 'finished') {
1225	set.finishedAt = now
1226	set.abortReason = null
1227	}
1228	if (status === 'aborted') {
1229	set.finishedAt = now
1230	set.abortReason = options?.abortReason \|\| null
1231	}
1232	await this.db.update(schema.sessionJobs).set(set).where(eq(schema.sessionJobs.id, jobId)).run()
1233	}
1234
1235	async getSessionJob(jobId: string): Promise<SessionJobRecord \| undefined> {
1236	const row = await this.db
1237	.select()
1238	.from(schema.sessionJobs)
1239	.where(eq(schema.sessionJobs.id, jobId))
1240	.get()
1241	return row ? this.normalizeSessionJobRow(row as Record<string, unknown>) : undefined
1242	}
1243
1244	async getLatestSessionJob(
1245	sessionId: string,
1246	kinds?: readonly SessionJobKind[]
1247	): Promise<SessionJobRecord \| undefined> {
1248	const where =
1249	kinds && kinds.length > 0
1250	? and(
1251	eq(schema.sessionJobs.sessionId, sessionId),
1252	inArray(schema.sessionJobs.kind, [...kinds])
1253	)
1254	: eq(schema.sessionJobs.sessionId, sessionId)
1255	const row = await this.db
1256	.select()
1257	.from(schema.sessionJobs)
1258	.where(where)
1259	.orderBy(desc(schema.sessionJobs.updatedAt), desc(schema.sessionJobs.createdAt))
1260	.limit(1)
1261	.get()
1262	return row ? this.normalizeSessionJobRow(row as Record<string, unknown>) : undefined
1263	}
1264
1265	async listActiveSessionJobs(kinds?: readonly SessionJobKind[]): Promise<SessionJobRecord[]> {
1266	const where =
1267	kinds && kinds.length > 0
1268	? and(
1269	inArray(schema.sessionJobs.status, ['pending', 'active']),
1270	inArray(schema.sessionJobs.kind, [...kinds])
1271	)
1272	: inArray(schema.sessionJobs.status, ['pending', 'active'])
1273	const rows = await this.db
1274	.select()
1275	.from(schema.sessionJobs)
1276	.where(where)
1277	.orderBy(asc(schema.sessionJobs.createdAt))
1278	.all()
1279	return rows.map((row) => this.normalizeSessionJobRow(row as Record<string, unknown>))
1280	}
1281
1282	async updateGenerationRunStatus(
1283	runId: string,
1284	status: GenerationRunStatus,
1285	error?: string \| null
1286	): Promise<void> {
1287	await this.db
1288	.update(schema.generationRuns)
1289	.set({
1290	status,
1291	error: error \|\| null,
1292	updatedAt: Math.floor(Date.now() / 1000)
1293	})
1294	.where(eq(schema.generationRuns.id, runId))
1295	.run()
1296	}
1297
1298	async updateGenerationRunMetadata(runId: string, metadata: unknown): Promise<void> {
1299	await this.db
1300	.update(schema.generationRuns)
1301	.set({
1302	metadata: metadata ? JSON.stringify(metadata) : null,
1303	updatedAt: Math.floor(Date.now() / 1000)
1304	})
1305	.where(eq(schema.generationRuns.id, runId))
1306	.run()
1307	}
1308
1309	async getGenerationRun(runId: string): Promise<GenerationRunRecord \| undefined> {
1310	const row = await this.db
1311	.select()
1312	.from(schema.generationRuns)
1313	.where(eq(schema.generationRuns.id, runId))
1314	.get()
1315	return row ? this.normalizeGenerationRunRow(row as Record<string, unknown>) : undefined
1316	}
1317
1318	async getLatestGenerationRun(sessionId: string): Promise<GenerationRunRecord \| undefined> {
1319	const row = await this.db
1320	.select()
1321	.from(schema.generationRuns)
1322	.where(eq(schema.generationRuns.sessionId, sessionId))
1323	.orderBy(desc(schema.generationRuns.createdAt))
1324	.limit(1)
1325	.get()
1326	return row ? this.normalizeGenerationRunRow(row as Record<string, unknown>) : undefined
1327	}
1328
1329	async upsertGenerationPage(data: {
1330	runId: string
1331	sessionId: string
1332	pageId: string
1333	pageNumber: number
1334	title: string
1335	contentOutline?: string \| null
1336	layoutIntent?: string \| null
1337	htmlPath?: string \| null
1338	status: GenerationPageStatus
1339	error?: string \| null
1340	retryCount?: number
1341	}): Promise<void> {
1342	const now = Math.floor(Date.now() / 1000)
1343	const id = `${data.runId}:${data.pageId}`
1344	const values = {
1345	id,
1346	runId: data.runId,
1347	sessionId: data.sessionId,
1348	pageId: data.pageId,
1349	pageNumber: data.pageNumber,
1350	title: data.title,
1351	contentOutline: data.contentOutline \|\| null,
1352	layoutIntent: data.layoutIntent \|\| null,
1353	htmlPath: data.htmlPath \|\| null,
1354	status: data.status,
1355	error: data.error \|\| null,
1356	retryCount: Math.max(0, Math.floor(data.retryCount \|\| 0)),
1357	createdAt: now,
1358	updatedAt: now
1359	}
1360	await this.db
1361	.insert(schema.generationPages)
1362	.values(values)
1363	.onConflictDoUpdate({
1364	target: schema.generationPages.id,
1365	set: {
1366	pageNumber: values.pageNumber,
1367	title: values.title,
1368	contentOutline: values.contentOutline,
1369	layoutIntent: values.layoutIntent,
1370	htmlPath: values.htmlPath,
1371	status: values.status,
1372	error: values.error,
1373	retryCount: values.retryCount,
1374	updatedAt: now
1375	}
1376	})
1377	.run()
1378	}
1379
1380	async listGenerationPages(runId: string): Promise<GenerationPageRecord[]> {
1381	const rows = await this.db
1382	.select()
1383	.from(schema.generationPages)
1384	.where(eq(schema.generationPages.runId, runId))
1385	.orderBy(asc(schema.generationPages.pageNumber))
1386	.all()
1387	return rows.map((row) => this.normalizeGenerationPageRow(row as Record<string, unknown>))
1388	}
1389
1390	async listLatestFailedGenerationPages(sessionId: string): Promise<GenerationPageRecord[]> {
1391	const run = await this.getLatestGenerationRun(sessionId)
1392	if (!run) return []
1393	return (await this.listGenerationPages(run.id)).filter((page) => page.status === 'failed')
1394	}
1395
1396	async listLatestGenerationPageSnapshot(sessionId: string): Promise<GenerationPageRecord[]> {
1397	const rows = await this.db
1398	.select()
1399	.from(schema.generationPages)
1400	.where(eq(schema.generationPages.sessionId, sessionId))
1401	.orderBy(desc(schema.generationPages.updatedAt), desc(schema.generationPages.createdAt))
1402	.all()
1403	const latestByPageId = new Map<string, GenerationPageRecord>()
1404	for (const row of rows) {
1405	const page = this.normalizeGenerationPageRow(row as Record<string, unknown>)
1406	if (!page.page_id \|\| latestByPageId.has(page.page_id)) continue
1407	latestByPageId.set(page.page_id, page)
1408	}
1409	return Array.from(latestByPageId.values()).sort((a, b) => a.page_number - b.page_number)
1410	}
1411
1412	async listSessionPages(
1413	sessionId: string,
1414	options?: { includeDeleted?: boolean }
1415	): Promise<SessionPageRecord[]> {
1416	const conditions = [eq(schema.sessionPages.sessionId, sessionId)]
1417	if (!options?.includeDeleted) {
1418	conditions.push(isNull(schema.sessionPages.deletedAt))
1419	}
1420	const rows = await this.db
1421	.select()
1422	.from(schema.sessionPages)
1423	.where(and(...conditions))
1424	.orderBy(asc(schema.sessionPages.pageNumber))
1425	.all()
1426	return rows.map((row) => this.normalizeSessionPageRow(row as Record<string, unknown>))
1427	}
1428
1429	async replaceSourcePageSkeletons(args: {
1430	sessionId: string
1431	sourceDocumentPath: string
1432	sourceDocumentName?: string \| null
1433	confidence?: SourcePageSkeletonConfidence
1434	items: Array<{
1435	pageNumber: number
1436	title: string
1437	role: SourcePageSkeletonRole
1438	sourceHeading: string
1439	headingLevel: number
1440	lineStart: number
1441	lineEnd: number
1442	reason?: string \| null
1443	}>
1444	}): Promise<void> {
1445	const now = Math.floor(Date.now() / 1000)
1446	await this.db
1447	.delete(schema.sourcePageSkeletons)
1448	.where(eq(schema.sourcePageSkeletons.sessionId, args.sessionId))
1449	.run()
1450	const values = args.items
1451	.filter((item) => item.sourceHeading.trim().length > 0)
1452	.map((item) => {
1453	const pageNumber = Math.max(1, Math.floor(item.pageNumber))
1454	const lineStart = Math.max(1, Math.floor(item.lineStart \|\| 1))
1455	const lineEnd = Math.max(lineStart, Math.floor(item.lineEnd \|\| lineStart))
1456	return {
1457	id: `${args.sessionId}:${pageNumber}`,
1458	sessionId: args.sessionId,
1459	pageNumber,
1460	title: item.title.trim() \|\| `Slide ${pageNumber}`,
1461	role: item.role === 'chapter-divider' ? 'chapter-divider' : 'content',
1462	sourceDocumentPath: args.sourceDocumentPath,
1463	sourceDocumentName: args.sourceDocumentName \|\| null,
1464	sourceHeading: item.sourceHeading,
1465	headingLevel: Math.max(1, Math.floor(item.headingLevel \|\| 1)),
1466	lineStart,
1467	lineEnd,
1468	reason: item.reason \|\| null,
1469	confidence: args.confidence \|\| 'high',
1470	createdAt: now,
1471	updatedAt: now
1472	}
1473	})
1474	if (values.length === 0) return
1475	await this.db.insert(schema.sourcePageSkeletons).values(values).run()
1476	}
1477
1478	async upsertSourcePageSkeleton(args: {
1479	sessionId: string
1480	pageNumber: number
1481	title: string
1482	role?: SourcePageSkeletonRole
1483	sourceDocumentPath: string
1484	sourceDocumentName?: string \| null
1485	sourceHeading: string
1486	headingLevel?: number
1487	lineStart?: number
1488	lineEnd?: number
1489	reason?: string \| null
1490	confidence?: SourcePageSkeletonConfidence
1491	}): Promise<void> {
1492	const now = Math.floor(Date.now() / 1000)
1493	const pageNumber = Math.max(1, Math.floor(args.pageNumber))
1494	const lineStart = Math.max(1, Math.floor(args.lineStart \|\| pageNumber))
1495	const lineEnd = Math.max(lineStart, Math.floor(args.lineEnd \|\| lineStart))
1496	const value = {
1497	id: `${args.sessionId}:${pageNumber}`,
1498	sessionId: args.sessionId,
1499	pageNumber,
1500	title: args.title.trim() \|\| `Slide ${pageNumber}`,
1501	role: args.role === 'chapter-divider' ? 'chapter-divider' : 'content',
1502	sourceDocumentPath: args.sourceDocumentPath,
1503	sourceDocumentName: args.sourceDocumentName \|\| null,
1504	sourceHeading: args.sourceHeading.trim(),
1505	headingLevel: Math.max(1, Math.floor(args.headingLevel \|\| 1)),
1506	lineStart,
1507	lineEnd,
1508	reason: args.reason \|\| null,
1509	confidence: args.confidence \|\| 'medium',
1510	createdAt: now,
1511	updatedAt: now
1512	}
1513	if (!value.sourceHeading) return
1514	await this.db
1515	.insert(schema.sourcePageSkeletons)
1516	.values(value)
1517	.onConflictDoUpdate({
1518	target: schema.sourcePageSkeletons.id,
1519	set: {
1520	title: value.title,
1521	role: value.role,
1522	sourceDocumentPath: value.sourceDocumentPath,
1523	sourceDocumentName: value.sourceDocumentName,
1524	sourceHeading: value.sourceHeading,
1525	headingLevel: value.headingLevel,
1526	lineStart: value.lineStart,
1527	lineEnd: value.lineEnd,
1528	reason: value.reason,
1529	confidence: value.confidence,
1530	updatedAt: now
1531	}
1532	})
1533	.run()
1534	}
1535
1536	async deleteSourcePageSkeleton(sessionId: string, pageNumber: number): Promise<void> {
1537	await this.db
1538	.delete(schema.sourcePageSkeletons)
1539	.where(
1540	and(
1541	eq(schema.sourcePageSkeletons.sessionId, sessionId),
1542	eq(schema.sourcePageSkeletons.pageNumber, pageNumber)
1543	)
1544	)
1545	.run()
1546	}
1547
1548	async deleteSourcePageSkeletons(sessionId: string, pageNumbers: number[]): Promise<void> {
1549	if (!Array.isArray(pageNumbers) \|\| pageNumbers.length === 0) return
1550	await this.db
1551	.delete(schema.sourcePageSkeletons)
1552	.where(
1553	and(
1554	eq(schema.sourcePageSkeletons.sessionId, sessionId),
1555	inArray(schema.sourcePageSkeletons.pageNumber, pageNumbers)
1556	)
1557	)
1558	.run()
1559	}
1560
1561	async listSourcePageSkeletons(sessionId: string): Promise<SourcePageSkeletonRecord[]> {
1562	const rows = await this.db
1563	.select()
1564	.from(schema.sourcePageSkeletons)
1565	.where(eq(schema.sourcePageSkeletons.sessionId, sessionId))
1566	.orderBy(asc(schema.sourcePageSkeletons.pageNumber))
1567	.all()
1568	return rows.map((row) => this.normalizeSourcePageSkeletonRow(row as Record<string, unknown>))
1569	}
1570
1571	async upsertSessionPage(page: SessionPageInput): Promise<void> {
1572	const now = Math.floor(Date.now() / 1000)
1573	await this.db
1574	.insert(schema.sessionPages)
1575	.values({
1576	id: page.id,
1577	sessionId: page.sessionId,
1578	legacyPageId: page.legacyPageId \|\| null,
1579	fileSlug: page.fileSlug,
1580	pageNumber: page.pageNumber,
1581	title: page.title,
1582	htmlPath: page.htmlPath,
1583	status: page.status \|\| 'pending',
1584	error: page.error \|\| null,
1585	createdAt: now,
1586	updatedAt: now,
1587	deletedAt: null
1588	})
1589	.onConflictDoUpdate({
1590	target: schema.sessionPages.id,
1591	set: {
1592	legacyPageId: page.legacyPageId \|\| null,
1593	fileSlug: page.fileSlug,
1594	pageNumber: page.pageNumber,
1595	title: page.title,
1596	htmlPath: page.htmlPath,
1597	status: page.status \|\| 'pending',
1598	error: page.error \|\| null,
1599	deletedAt: null,
1600	updatedAt: now
1601	}
1602	})
1603	.run()
1604	}
1605
1606	async replaceSessionPageOrder(
1607	sessionId: string,
1608	pages: Array<{ id: string; pageNumber: number }>
1609	): Promise<void> {
1610	if (pages.length === 0) return
1611	const now = Math.floor(Date.now() / 1000)
1612	const pageIds = pages.map((page) => page.id)
1613	const caseWhenFragments = pages.map(
1614	(page) => sql`WHEN ${schema.sessionPages.id} = ${page.id} THEN ${page.pageNumber}`
1615	)
1616	const pageNumberExpr = sql<number>`CASE ${sql.join(caseWhenFragments, sql` `)} ELSE ${schema.sessionPages.pageNumber} END`
1617	await this.db
1618	.update(schema.sessionPages)
1619	.set({
1620	pageNumber: pageNumberExpr,
1621	updatedAt: now
1622	})
1623	.where(
1624	and(eq(schema.sessionPages.sessionId, sessionId), inArray(schema.sessionPages.id, pageIds))
1625	)
1626	.run()
1627	}
1628
1629	async persistSessionPageState(data: {
1630	sessionId: string
1631	pages: Array<{ id: string; pageNumber: number }>
1632	deletedPageIds?: string[]
1633	metadata: object
1634	}): Promise<void> {
1635	const now = Math.floor(Date.now() / 1000)
1636	await this.db.transaction(async (tx) => {
1637	if (data.deletedPageIds?.length) {
1638	await tx
1639	.update(schema.sessionPages)
1640	.set({ deletedAt: now, updatedAt: now })
1641	.where(
1642	and(
1643	eq(schema.sessionPages.sessionId, data.sessionId),
1644	inArray(schema.sessionPages.id, data.deletedPageIds)
1645	)
1646	)
1647	.run()
1648	}
1649	if (data.pages.length > 0) {
1650	const pageIds = data.pages.map((page) => page.id)
1651	const caseWhenFragments = data.pages.map(
1652	(page) => sql`WHEN ${schema.sessionPages.id} = ${page.id} THEN ${page.pageNumber}`
1653	)
1654	const pageNumberExpr = sql<number>`CASE ${sql.join(caseWhenFragments, sql` `)} ELSE ${schema.sessionPages.pageNumber} END`
1655	await tx
1656	.update(schema.sessionPages)
1657	.set({ pageNumber: pageNumberExpr, updatedAt: now })
1658	.where(
1659	and(
1660	eq(schema.sessionPages.sessionId, data.sessionId),
1661	inArray(schema.sessionPages.id, pageIds)
1662	)
1663	)
1664	.run()
1665	}
1666	await tx
1667	.update(schema.sessions)
1668	.set({ metadata: JSON.stringify(data.metadata), updatedAt: now })
1669	.where(eq(schema.sessions.id, data.sessionId))
1670	.run()
1671	})
1672	}
1673
1674	async softDeleteSessionPages(sessionId: string, ids: string[]): Promise<void> {
1675	if (!Array.isArray(ids) \|\| ids.length === 0) return
1676	const now = Math.floor(Date.now() / 1000)
1677	await this.db
1678	.update(schema.sessionPages)
1679	.set({
1680	deletedAt: now,
1681	updatedAt: now
1682	})
1683	.where(
1684	and(eq(schema.sessionPages.sessionId, sessionId), inArray(schema.sessionPages.id, ids))
1685	)
1686	.run()
1687	}
1688
1689	async hardDeleteSessionPages(sessionId: string, ids: string[]): Promise<void> {
1690	if (!Array.isArray(ids) \|\| ids.length === 0) return
1691	await this.db
1692	.delete(schema.sessionPages)
1693	.where(
1694	and(eq(schema.sessionPages.sessionId, sessionId), inArray(schema.sessionPages.id, ids))
1695	)
1696	.run()
1697	}
1698
1699	// ========== Session History ==========
1700
1701	private normalizeSessionOperationRow(row: Record<string, unknown>): SessionOperationRecord {
1702	return {
1703	id: String(row.id \|\| ''),
1704	session_id: String(row.sessionId ?? row.session_id ?? ''),
1705	type: String(row.type \|\| 'edit') as SessionOperationType,
1706	status: String(row.status \|\| 'completed') as SessionOperationStatus,
1707	scope:
1708	typeof (row.scope ?? row.scope) === 'string'
1709	? (String(row.scope) as SessionOperationScope)
1710	: null,
1711	prompt:
1712	typeof row.prompt === 'string' && row.prompt.trim().length > 0 ? String(row.prompt) : null,
1713	parent_operation_id:
1714	typeof (row.parentOperationId ?? row.parent_operation_id) === 'string'
1715	? String(row.parentOperationId ?? row.parent_operation_id)
1716	: null,
1717	before_commit:
1718	typeof (row.beforeCommit ?? row.before_commit) === 'string'
1719	? String(row.beforeCommit ?? row.before_commit)
1720	: null,
1721	after_commit:
1722	typeof (row.afterCommit ?? row.after_commit) === 'string'
1723	? String(row.afterCommit ?? row.after_commit)
1724	: null,
1725	target_operation_id:
1726	typeof (row.targetOperationId ?? row.target_operation_id) === 'string'
1727	? String(row.targetOperationId ?? row.target_operation_id)
1728	: null,
1729	target_commit:
1730	typeof (row.targetCommit ?? row.target_commit) === 'string'
1731	? String(row.targetCommit ?? row.target_commit)
1732	: null,
1733	changed_files_json: String(row.changedFilesJson ?? row.changed_files_json ?? '[]'),
1734	changed_pages_json: String(row.changedPagesJson ?? row.changed_pages_json ?? '[]'),
1735	tracked_files_json: String(row.trackedFilesJson ?? row.tracked_files_json ?? '[]'),
1736	metadata_json: String(row.metadataJson ?? row.metadata_json ?? '{}'),
1737	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1738	completed_at:
1739	typeof (row.completedAt ?? row.completed_at) === 'number'
1740	? Number(row.completedAt ?? row.completed_at)
1741	: null
1742	}
1743	}
1744
1745	private normalizeSessionOperationPageRow(
1746	row: Record<string, unknown>
1747	): SessionOperationPageRecord {
1748	return {
1749	id: String(row.id \|\| ''),
1750	operation_id: String(row.operationId ?? row.operation_id ?? ''),
1751	session_id: String(row.sessionId ?? row.session_id ?? ''),
1752	page_id: String(row.pageId ?? row.page_id ?? ''),
1753	legacy_page_id:
1754	typeof (row.legacyPageId ?? row.legacy_page_id) === 'string'
1755	? String(row.legacyPageId ?? row.legacy_page_id)
1756	: null,
1757	file_slug: String(row.fileSlug ?? row.file_slug ?? ''),
1758	page_number: Number(row.pageNumber ?? row.page_number ?? 0) \|\| 0,
1759	title: String(row.title \|\| ''),
1760	html_path: String(row.htmlPath ?? row.html_path ?? ''),
1761	status: String(row.status \|\| 'pending') as SessionPageStatus,
1762	error: typeof row.error === 'string' ? String(row.error) : null,
1763	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1764	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0
1765	}
1766	}
1767
1768	async createSessionOperation(data: {
1769	id: string
1770	sessionId: string
1771	type: SessionOperationType
1772	status?: SessionOperationStatus
1773	scope?: SessionOperationScope \| null
1774	prompt?: string \| null
1775	parentOperationId?: string \| null
1776	beforeCommit?: string \| null
1777	targetOperationId?: string \| null
1778	targetCommit?: string \| null
1779	metadata?: unknown
1780	}): Promise<void> {
1781	const now = Math.floor(Date.now() / 1000)
1782	await this.db
1783	.insert(schema.sessionOperations)
1784	.values({
1785	id: data.id,
1786	sessionId: data.sessionId,
1787	type: data.type,
1788	status: data.status \|\| 'committing',
1789	scope: data.scope \|\| null,
1790	prompt: data.prompt \|\| null,
1791	parentOperationId: data.parentOperationId \|\| null,
1792	beforeCommit: data.beforeCommit \|\| null,
1793	afterCommit: null,
1794	targetOperationId: data.targetOperationId \|\| null,
1795	targetCommit: data.targetCommit \|\| null,
1796	changedFilesJson: '[]',
1797	changedPagesJson: '[]',
1798	trackedFilesJson: '[]',
1799	metadataJson: data.metadata ? JSON.stringify(data.metadata) : '{}',
1800	createdAt: now,
1801	completedAt: null
1802	})
1803	.run()
1804	}
1805
1806	async completeSessionOperation(data: {
1807	id: string
1808	status: 'completed' \| 'noop' \| 'failed'
1809	afterCommit?: string \| null
1810	changedFiles?: unknown[]
1811	changedPages?: unknown[]
1812	trackedFiles?: string[]
1813	metadata?: unknown
1814	}): Promise<void> {
1815	await this.db
1816	.update(schema.sessionOperations)
1817	.set({
1818	status: data.status,
1819	afterCommit: data.afterCommit \|\| null,
1820	changedFilesJson: JSON.stringify(data.changedFiles \|\| []),
1821	changedPagesJson: JSON.stringify(data.changedPages \|\| []),
1822	trackedFilesJson: JSON.stringify(data.trackedFiles \|\| []),
1823	metadataJson: JSON.stringify(data.metadata \|\| {}),
1824	completedAt: Math.floor(Date.now() / 1000)
1825	})
1826	.where(eq(schema.sessionOperations.id, data.id))
1827	.run()
1828	}
1829
1830	async updateSessionOperationMetadata(
1831	operationId: string,
1832	metadata: Record<string, unknown>
1833	): Promise<void> {
1834	await this.db
1835	.update(schema.sessionOperations)
1836	.set({ metadataJson: JSON.stringify(metadata) })
1837	.where(eq(schema.sessionOperations.id, operationId))
1838	.run()
1839	}
1840
1841	async getSessionOperation(operationId: string): Promise<SessionOperationRecord \| undefined> {
1842	const row = await this.db
1843	.select()
1844	.from(schema.sessionOperations)
1845	.where(eq(schema.sessionOperations.id, operationId))
1846	.get()
1847	return row ? this.normalizeSessionOperationRow(row as Record<string, unknown>) : undefined
1848	}
1849
1850	async hasAnyOperationPageSnapshots(sessionId: string): Promise<boolean> {
1851	const row = await this.db
1852	.select({ id: schema.sessionOperationPages.id })
1853	.from(schema.sessionOperationPages)
1854	.where(eq(schema.sessionOperationPages.sessionId, sessionId))
1855	.limit(1)
1856	.get()
1857	return !!row
1858	}
1859
1860	async cleanupSessionOperations(sessionId: string): Promise<number> {
1861	const rows = await this.db
1862	.select({ id: schema.sessionOperations.id })
1863	.from(schema.sessionOperations)
1864	.where(eq(schema.sessionOperations.sessionId, sessionId))
1865	.all()
1866	if (rows.length === 0) {
1867	await this.updateSessionHistoryPointer({ sessionId, operationId: null, commit: null })
1868	return 0
1869	}
1870	const ids = rows.map((r) => r.id)
1871	await this.db
1872	.delete(schema.sessionOperationPages)
1873	.where(inArray(schema.sessionOperationPages.operationId, ids))
1874	.run()
1875	await this.db
1876	.delete(schema.sessionOperations)
1877	.where(inArray(schema.sessionOperations.id, ids))
1878	.run()
1879	await this.updateSessionHistoryPointer({ sessionId, operationId: null, commit: null })
1880	return ids.length
1881	}
1882
1883	async listSessionOperations(
1884	sessionId: string,
1885	options?: { limit?: number; includeNoop?: boolean }
1886	): Promise<SessionOperationRecord[]> {
1887	const rows = await this.db
1888	.select()
1889	.from(schema.sessionOperations)
1890	.where(eq(schema.sessionOperations.sessionId, sessionId))
1891	.orderBy(desc(schema.sessionOperations.createdAt))
1892	.limit(Math.max(1, Math.min(200, Math.floor(options?.limit \|\| 50))))
1893	.all()
1894	return rows
1895	.map((row) => this.normalizeSessionOperationRow(row as Record<string, unknown>))
1896	.filter((row) =>
1897	options?.includeNoop
1898	? row.status === 'completed' \|\| row.status === 'noop'
1899	: row.status === 'completed'
1900	)
1901	}
1902
1903	async replaceSessionOperationPages(
1904	operationId: string,
1905	sessionId: string,
1906	pages: Array<{
1907	pageId: string
1908	legacyPageId?: string \| null
1909	fileSlug: string
1910	pageNumber: number
1911	title: string
1912	htmlPath: string
1913	status?: SessionPageStatus
1914	error?: string \| null
1915	}>
1916	): Promise<void> {
1917	const now = Math.floor(Date.now() / 1000)
1918	await this.db
1919	.delete(schema.sessionOperationPages)
1920	.where(eq(schema.sessionOperationPages.operationId, operationId))
1921	.run()
1922	for (const page of pages) {
1923	await this.db
1924	.insert(schema.sessionOperationPages)
1925	.values({
1926	id: `${operationId}:${page.pageId}`,
1927	operationId,
1928	sessionId,
1929	pageId: page.pageId,
1930	legacyPageId: page.legacyPageId \|\| null,
1931	fileSlug: page.fileSlug,
1932	pageNumber: page.pageNumber,
1933	title: page.title,
1934	htmlPath: page.htmlPath,
1935	status: page.status \|\| 'pending',
1936	error: page.error \|\| null,
1937	createdAt: now,
1938	updatedAt: now
1939	})
1940	.run()
1941	}
1942	}
1943
1944	async listSessionOperationPages(operationId: string): Promise<SessionOperationPageRecord[]> {
1945	const rows = await this.db
1946	.select()
1947	.from(schema.sessionOperationPages)
1948	.where(eq(schema.sessionOperationPages.operationId, operationId))
1949	.orderBy(asc(schema.sessionOperationPages.pageNumber))
1950	.all()
1951	return rows.map((row) => this.normalizeSessionOperationPageRow(row as Record<string, unknown>))
1952	}
1953
1954	// ========== Messages ==========
1955
1956	async getSessionMessages(
1957	sessionId: string,
1958	options?: {
1959	chatScope?: ChatScope
1960	pageId?: string
1961	}
1962	): Promise<Message[]> {
1963	const chatScope = options?.chatScope ?? 'main'
1964	const normalizedPageId =
1965	typeof options?.pageId === 'string' && options.pageId.trim().length > 0
1966	? options.pageId.trim()
1967	: null
1968	if (chatScope === 'page' && !normalizedPageId) {
1969	return []
1970	}
1971	if (chatScope === 'page' && normalizedPageId) {
1972	// Rollback / page-management may switch between canonical id and fileSlug.
1973	// Query messages by all known aliases to keep page chat continuous.
1974	const aliases = new Set<string>([normalizedPageId])
1975	const directRows = await this.db
1976	.select({
1977	id: schema.sessionPages.id,
1978	fileSlug: schema.sessionPages.fileSlug,
1979	legacyPageId: schema.sessionPages.legacyPageId
1980	})
1981	.from(schema.sessionPages)
1982	.where(
1983	and(
1984	eq(schema.sessionPages.sessionId, sessionId),
1985	or(
1986	eq(schema.sessionPages.id, normalizedPageId),
1987	eq(schema.sessionPages.fileSlug, normalizedPageId),
1988	eq(schema.sessionPages.legacyPageId, normalizedPageId)
1989	)
1990	)
1991	)
1992	.all()
1993	const matchedSlugs = Array.from(
1994	new Set(
1995	directRows
1996	.map((row) => String(row.fileSlug \|\| '').trim())
1997	.filter((item) => item.length > 0)
1998	)
1999	)
2000	if (matchedSlugs.length > 0) {
2001	const relatedRows = await this.db
2002	.select({
2003	id: schema.sessionPages.id,
2004	fileSlug: schema.sessionPages.fileSlug,
2005	legacyPageId: schema.sessionPages.legacyPageId
2006	})
2007	.from(schema.sessionPages)
2008	.where(
2009	and(
2010	eq(schema.sessionPages.sessionId, sessionId),
2011	inArray(schema.sessionPages.fileSlug, matchedSlugs)
2012	)
2013	)
2014	.all()
2015	for (const row of relatedRows) {
2016	if (typeof row.id === 'string' && row.id.trim().length > 0) aliases.add(row.id.trim())
2017	if (typeof row.fileSlug === 'string' && row.fileSlug.trim().length > 0)
2018	aliases.add(row.fileSlug.trim())
2019	if (typeof row.legacyPageId === 'string' && row.legacyPageId.trim().length > 0)
2020	aliases.add(row.legacyPageId.trim())
2021	}
2022	}
2023	const results = await this.db
2024	.select()
2025	.from(schema.messages)
2026	.where(
2027	and(
2028	eq(schema.messages.sessionId, sessionId),
2029	eq(schema.messages.chatScope, 'page'),
2030	inArray(schema.messages.pageId, Array.from(aliases))
2031	)
2032	)
2033	.orderBy(asc(schema.messages.createdAt))
2034	.all()
2035	return results.map((message) => this.normalizeMessageRow(message as Record<string, unknown>))
2036	}
2037	const whereClause = and(
2038	eq(schema.messages.sessionId, sessionId),
2039	eq(schema.messages.chatScope, 'main')
2040	)
2041	const results = await this.db
2042	.select()
2043	.from(schema.messages)
2044	.where(whereClause)
2045	.orderBy(asc(schema.messages.createdAt))
2046	.all()
2047
2048	return results.map((message) => this.normalizeMessageRow(message as Record<string, unknown>))
2049	}
2050
2051	private normalizeAssetPaths(value: unknown, prefix: './images/' \| './videos/'): string[] \| null {
2052	if (typeof value !== 'string' \|\| value.trim().length === 0) return null
2053	try {
2054	const parsed = JSON.parse(value) as unknown
2055	if (!Array.isArray(parsed)) return null
2056	const valid = parsed
2057	.map((item) => String(item \|\| '').trim())
2058	.filter((item) => item.startsWith(prefix))
2059	.slice(0, 10)
2060	return valid.length > 0 ? valid : null
2061	} catch {
2062	return null
2063	}
2064	}
2065
2066	private normalizeMessageRow(message: Record<string, unknown>): Message {
2067	const rawImagePaths = message.imagePaths ?? message.image_paths ?? null
2068	const rawVideoPaths = message.videoPaths ?? message.video_paths ?? null
2069	const imagePaths = this.normalizeAssetPaths(rawImagePaths, './images/')
2070	const videoPaths = this.normalizeAssetPaths(rawVideoPaths, './videos/')
2071	return {
2072	id: String(message.id \|\| ''),
2073	session_id: String(message.sessionId ?? message.session_id ?? ''),
2074	chat_scope: message.chatScope === 'page' \|\| message.chat_scope === 'page' ? 'page' : 'main',
2075	page_id:
2076	typeof (message.pageId ?? message.page_id) === 'string'
2077	? String(message.pageId ?? message.page_id)
2078	: null,
2079	selector:
2080	typeof message.selector === 'string' && message.selector.trim().length > 0
2081	? message.selector.trim()
2082	: null,
2083	image_paths: imagePaths,
2084	video_paths: videoPaths,
2085	role: String(message.role \|\| 'system') as MessageRole,
2086	content: String(message.content \|\| ''),
2087	type: String(message.type \|\| 'text') as MessageType,
2088	tool_name:
2089	typeof (message.toolName ?? message.tool_name) === 'string'
2090	? String(message.toolName ?? message.tool_name)
2091	: null,
2092	tool_call_id:
2093	typeof (message.toolCallId ?? message.tool_call_id) === 'string'
2094	? String(message.toolCallId ?? message.tool_call_id)
2095	: null,
2096	token_count:
2097	typeof (message.tokenCount ?? message.token_count) === 'number'
2098	? Number(message.tokenCount ?? message.token_count)
2099	: null,
2100	run_model:
2101	typeof (message.runModel ?? message.run_model) === 'string'
2102	? String(message.runModel ?? message.run_model)
2103	: null,
2104	created_at:
2105	typeof (message.createdAt ?? message.created_at) === 'number'
2106	? Number(message.createdAt ?? message.created_at)
2107	: Math.floor(Date.now() / 1000)
2108	}
2109	}
2110
2111	async addMessage(
2112	sessionId: string,
2113	message: {
2114	role: MessageRole
2115	content: string
2116	type?: MessageType
2117	tool_name?: string \| null
2118	tool_call_id?: string \| null
2119	token_count?: number \| null
2120	chat_scope?: ChatScope
2121	page_id?: string \| null
2122	selector?: string \| null
2123	image_paths?: string[] \| null
2124	video_paths?: string[] \| null
2125	run_model?: string \| null
2126	id?: string
2127	}
2128	): Promise<string> {
2129	const id = message.id \|\| crypto.randomUUID()
2130	const now = Math.floor(Date.now() / 1000)
2131	const chatScope = message.chat_scope === 'page' ? 'page' : 'main'
2132	const pageId =
2133	chatScope === 'page' &&
2134	typeof message.page_id === 'string' &&
2135	message.page_id.trim().length > 0
2136	? message.page_id.trim()
2137	: null
2138	const selector =
2139	chatScope === 'page' &&
2140	typeof message.selector === 'string' &&
2141	message.selector.trim().length > 0
2142	? message.selector.trim()
2143	: null
2144	const imagePathsRaw = Array.isArray(message.image_paths) ? message.image_paths : []
2145	const imagePaths =
2146	imagePathsRaw.length > 0
2147	? imagePathsRaw
2148	.map((item) => String(item \|\| '').trim())
2149	.filter((item) => item.startsWith('./images/'))
2150	.slice(0, 10)
2151	: []
2152	const videoPathsRaw = Array.isArray(message.video_paths) ? message.video_paths : []
2153	const videoPaths =
2154	videoPathsRaw.length > 0
2155	? videoPathsRaw
2156	.map((item) => String(item \|\| '').trim())
2157	.filter((item) => item.startsWith('./videos/'))
2158	.slice(0, 10)
2159	: []
2160	const imagePathsJson = imagePaths.length > 0 ? JSON.stringify(imagePaths) : null
2161	const videoPathsJson = videoPaths.length > 0 ? JSON.stringify(videoPaths) : null
2162	if (chatScope === 'page' && !pageId) {
2163	throw new Error('page chat message requires page_id')
2164	}
2165
2166	await this.db
2167	.insert(schema.messages)
2168	.values({
2169	id,
2170	sessionId,
2171	chatScope,
2172	pageId,
2173	selector,
2174	imagePaths: imagePathsJson,
2175	videoPaths: videoPathsJson,
2176	role: message.role,
2177	content: message.content,
2178	type: message.type \|\| 'text',
2179	toolName: message.tool_name \|\| null,
2180	toolCallId: message.tool_call_id \|\| null,
2181	tokenCount: message.token_count \|\| null,
2182	runModel:
2183	typeof message.run_model === 'string' && message.run_model.trim().length > 0
2184	? message.run_model
2185	: null,
2186	createdAt: now
2187	})
2188	.run()
2189
2190	await this.db
2191	.update(schema.sessions)
2192	.set({ updatedAt: now })
2193	.where(eq(schema.sessions.id, sessionId))
2194	.run()
2195
2196	return id
2197	}
2198
2199	async getMessageCount(sessionId: string): Promise<number> {
2200	const result = await this.db
2201	.select({ count: count() })
2202	.from(schema.messages)
2203	.where(eq(schema.messages.sessionId, sessionId))
2204	.get()
2205	return result?.count ?? 0
2206	}
2207
2208	async getRecentMessages(sessionId: string, count: number): Promise<Message[]> {
2209	const results = await this.db
2210	.select()
2211	.from(schema.messages)
2212	.where(eq(schema.messages.sessionId, sessionId))
2213	.orderBy(desc(schema.messages.createdAt))
2214	.limit(count)
2215	.all()
2216
2217	return results.map((message) => this.normalizeMessageRow(message as Record<string, unknown>))
2218	}
2219
2220	// ========== Memory ==========
2221
2222	async getLastSummary(sessionId: string): Promise<MemorySummary \| undefined> {
2223	const result = await this.db
2224	.select()
2225	.from(schema.memorySummaries)
2226	.where(eq(schema.memorySummaries.sessionId, sessionId))
2227	.orderBy(desc(schema.memorySummaries.messageRangeEnd))
2228	.limit(1)
2229	.get()
2230
2231	return result as MemorySummary \| undefined
2232	}
2233
2234	async saveSummary(
2235	sessionId: string,
2236	data: {
2237	rangeStart: number
2238	rangeEnd: number
2239	summary: string
2240	tokenCount?: number
2241	}
2242	): Promise<string> {
2243	const id = crypto.randomUUID()
2244	const now = Math.floor(Date.now() / 1000)
2245
2246	await this.db
2247	.insert(schema.memorySummaries)
2248	.values({
2249	id,
2250	sessionId,
2251	messageRangeStart: data.rangeStart,
2252	messageRangeEnd: data.rangeEnd,
2253	summary: data.summary,
2254	tokenCount: data.tokenCount \|\| null,
2255	createdAt: now
2256	})
2257	.run()
2258
2259	return id
2260	}
2261
2262	async getLastCompressedIndex(sessionId: string): Promise<number> {
2263	const result = await this.db
2264	.select({ maxIndex: max(schema.memorySummaries.messageRangeEnd) })
2265	.from(schema.memorySummaries)
2266	.where(eq(schema.memorySummaries.sessionId, sessionId))
2267	.get()
2268	return result?.maxIndex ?? 0
2269	}
2270
2271	async getMessagesForCompression(
2272	sessionId: string,
2273	batchSize: number
2274	): Promise<(Message & { idx: number })[]> {
2275	const lastCompressedIndex = await this.getLastCompressedIndex(sessionId)
2276
2277	const results = await this.db
2278	.select({
2279	id: schema.messages.id,
2280	sessionId: schema.messages.sessionId,
2281	chatScope: schema.messages.chatScope,
2282	pageId: schema.messages.pageId,
2283	role: schema.messages.role,
2284	content: schema.messages.content,
2285	type: schema.messages.type,
2286	toolName: schema.messages.toolName,
2287	toolCallId: schema.messages.toolCallId,
2288	tokenCount: schema.messages.tokenCount,
2289	runModel: schema.messages.runModel,
2290	createdAt: schema.messages.createdAt
2291	})
2292	.from(schema.messages)
2293	.where(
2294	and(
2295	eq(schema.messages.sessionId, sessionId),
2296	gt(schema.messages.createdAt, lastCompressedIndex)
2297	)
2298	)
2299	.orderBy(asc(schema.messages.createdAt))
2300	.limit(batchSize)
2301	.all()
2302
2303	let idx = lastCompressedIndex + 1
2304	return results.map((r) => ({
2305	...this.normalizeMessageRow(r as Record<string, unknown>),
2306	idx: idx++
2307	}))
2308	}
2309
2310	// ========== Settings ==========
2311
2312	async recordModelUsage(data: {
2313	provider: string
2314	model: string
2315	modelConfigId?: string
2316	inputTokens: number
2317	outputTokens: number
2318	totalTokens: number
2319	source: 'provider' \| 'estimated'
2320	}): Promise<void> {
2321	await this.db
2322	.insert(schema.modelUsageEvents)
2323	.values({
2324	id: crypto.randomUUID(),
2325	provider: data.provider,
2326	model: data.model,
2327	modelConfigId: data.modelConfigId \|\| null,
2328	inputTokens: Math.max(0, Math.floor(data.inputTokens)),
2329	outputTokens: Math.max(0, Math.floor(data.outputTokens)),
2330	totalTokens: Math.max(0, Math.floor(data.totalTokens)),
2331	usageSource: data.source,
2332	createdAt: Math.floor(Date.now() / 1000)
2333	})
2334	.run()
2335	}
2336
2337	async getModelUsageStats(period: ModelUsagePeriod): Promise<ModelUsageStats> {
2338	const now = new Date()
2339	let startedAt: number \| null = null
2340	if (period === 'today') {
2341	const start = new Date(now.getFullYear(), now.getMonth(), now.getDate())
2342	startedAt = Math.floor(start.getTime() / 1000)
2343	} else if (period !== 'all') {
2344	const days = period === '7d' ? 7 : 30
2345	const start = new Date(now.getFullYear(), now.getMonth(), now.getDate() - days + 1)
2346	startedAt = Math.floor(start.getTime() / 1000)
2347	}
2348	const whereSql = startedAt === null ? '' : ' WHERE created_at >= ?'
2349	const args = startedAt === null ? [] : [startedAt]
2350	const totalsResult = await this.client.execute({
2351	sql: `
2352	SELECT
2353	COUNT(*) AS call_count,
2354	SUM(CASE WHEN usage_source = 'provider' THEN 1 ELSE 0 END) AS exact_call_count,
2355	SUM(CASE WHEN usage_source = 'estimated' THEN 1 ELSE 0 END) AS estimated_call_count,
2356	COALESCE(SUM(input_tokens), 0) AS input_tokens,
2357	COALESCE(SUM(output_tokens), 0) AS output_tokens,
2358	COALESCE(SUM(total_tokens), 0) AS total_tokens
2359	FROM model_usage_events${whereSql}
2360	`,
2361	args
2362	})
2363	const byModelResult = await this.client.execute({
2364	sql: `
2365	SELECT
2366	provider,
2367	model,
2368	COUNT(*) AS call_count,
2369	SUM(CASE WHEN usage_source = 'provider' THEN 1 ELSE 0 END) AS exact_call_count,
2370	SUM(CASE WHEN usage_source = 'estimated' THEN 1 ELSE 0 END) AS estimated_call_count,
2371	COALESCE(SUM(input_tokens), 0) AS input_tokens,
2372	COALESCE(SUM(output_tokens), 0) AS output_tokens,
2373	COALESCE(SUM(total_tokens), 0) AS total_tokens
2374	FROM model_usage_events${whereSql}
2375	GROUP BY provider, model
2376	ORDER BY total_tokens DESC
2377	`,
2378	args
2379	})
2380	const byDayResult = await this.client.execute({
2381	sql: `
2382	SELECT
2383	date(created_at, 'unixepoch', 'localtime') AS date,
2384	COUNT(*) AS call_count,
2385	SUM(CASE WHEN usage_source = 'provider' THEN 1 ELSE 0 END) AS exact_call_count,
2386	SUM(CASE WHEN usage_source = 'estimated' THEN 1 ELSE 0 END) AS estimated_call_count,
2387	COALESCE(SUM(input_tokens), 0) AS input_tokens,
2388	COALESCE(SUM(output_tokens), 0) AS output_tokens,
2389	COALESCE(SUM(total_tokens), 0) AS total_tokens
2390	FROM model_usage_events${whereSql}
2391	GROUP BY date
2392	ORDER BY date ASC
2393	`,
2394	args
2395	})
2396
2397	const byHourResult =
2398	period === 'today'
2399	? await this.client.execute({
2400	sql: `
2401	SELECT
2402	CAST(strftime('%H', created_at, 'unixepoch', 'localtime') AS INTEGER) AS hour,
2403	COUNT(*) AS call_count,
2404	SUM(CASE WHEN usage_source = 'provider' THEN 1 ELSE 0 END) AS exact_call_count,
2405	SUM(CASE WHEN usage_source = 'estimated' THEN 1 ELSE 0 END) AS estimated_call_count,
2406	COALESCE(SUM(input_tokens), 0) AS input_tokens,
2407	COALESCE(SUM(output_tokens), 0) AS output_tokens,
2408	COALESCE(SUM(total_tokens), 0) AS total_tokens
2409	FROM model_usage_events${whereSql}
2410	GROUP BY hour
2411	ORDER BY hour ASC
2412	`,
2413	args
2414	})
2415	: null
2416
2417	const readTotals = (row: Record<string, unknown> \| undefined): ModelUsageTotals => ({
2418	callCount: Number(row?.call_count \|\| 0),
2419	exactCallCount: Number(row?.exact_call_count \|\| 0),
2420	estimatedCallCount: Number(row?.estimated_call_count \|\| 0),
2421	inputTokens: Number(row?.input_tokens \|\| 0),
2422	outputTokens: Number(row?.output_tokens \|\| 0),
2423	totalTokens: Number(row?.total_tokens \|\| 0)
2424	})
2425
2426	const byHour: ModelUsageByHour[] = []
2427	if (byHourResult) {
2428	const hourMap = new Map<number, ModelUsageTotals>()
2429	for (const row of byHourResult.rows) {
2430	const hour = Number((row as Record<string, unknown>).hour \|\| 0)
2431	hourMap.set(hour, readTotals(row as Record<string, unknown>))
2432	}
2433	for (let hour = 0; hour < 24; hour += 1) {
2434	byHour.push({ hour, ...(hourMap.get(hour) \|\| readTotals(undefined)) })
2435	}
2436	}
2437
2438	return {
2439	period,
2440	startedAt,
2441	totals: readTotals(totalsResult.rows[0] as Record<string, unknown> \| undefined),
2442	byModel: byModelResult.rows.map((row) => ({
2443	provider: String(row.provider \|\| ''),
2444	model: String(row.model \|\| ''),
2445	...readTotals(row as Record<string, unknown>)
2446	})),
2447	byDay: byDayResult.rows.map((row) => ({
2448	date: String(row.date \|\| ''),
2449	...readTotals(row as Record<string, unknown>)
2450	})),
2451	byHour
2452	}
2453	}
2454
2455	async getSetting<T>(key: string): Promise<T \| undefined> {
2456	const result = await this.db
2457	.select({ value: schema.settings.value })
2458	.from(schema.settings)
2459	.where(eq(schema.settings.key, key))
2460	.get()
2461	if (!result) return undefined
2462	try {
2463	return JSON.parse(result.value) as T
2464	} catch {
2465	return result.value as T
2466	}
2467	}
2468
2469	async setSetting<T>(key: string, value: T): Promise<void> {
2470	const now = Math.floor(Date.now() / 1000)
2471	await this.db
2472	.insert(schema.settings)
2473	.values({ key, value: JSON.stringify(value), updatedAt: now })
2474	.onConflictDoUpdate({
2475	target: schema.settings.key,
2476	set: { value: JSON.stringify(value), updatedAt: now }
2477	})
2478	.run()
2479	}
2480
2481	async getAllSettings(): Promise<Record<string, unknown>> {
2482	const results = await this.db.select().from(schema.settings).all()
2483	const result: Record<string, unknown> = {}
2484	for (const row of results) {
2485	try {
2486	result[row.key] = JSON.parse(row.value)
2487	} catch {
2488	result[row.key] = row.value
2489	}
2490	}
2491	return result
2492	}
2493
2494	// ========== Model Configs ==========
2495
2496	async listModelConfigs(): Promise<ModelConfigRow[]> {
2497	const results = await this.db
2498	.select()
2499	.from(schema.modelConfigs)
2500	.orderBy(desc(schema.modelConfigs.active), desc(schema.modelConfigs.updatedAt))
2501	.all()
2502	return results as unknown as ModelConfigRow[]
2503	}
2504
2505	async getActiveModelConfig(): Promise<ModelConfigRow \| undefined> {
2506	const result = await this.db
2507	.select()
2508	.from(schema.modelConfigs)
2509	.where(eq(schema.modelConfigs.active, 1))
2510	.limit(1)
2511	.get()
2512	return result as unknown as ModelConfigRow \| undefined
2513	}
2514
2515	async getModelConfig(id: string): Promise<ModelConfigRow \| undefined> {
2516	const result = await this.db
2517	.select()
2518	.from(schema.modelConfigs)
2519	.where(eq(schema.modelConfigs.id, id))
2520	.limit(1)
2521	.get()
2522	return result as unknown as ModelConfigRow \| undefined
2523	}
2524
2525	async upsertModelConfig(data: {
2526	id?: string
2527	name: string
2528	provider: string
2529	model: string
2530	apiKey: string
2531	baseUrl: string
2532	maxTokens?: number
2533	disableTemperature?: boolean
2534	thinkingParameterMode?: string
2535	active?: boolean
2536	}): Promise<string> {
2537	const id = data.id \|\| crypto.randomUUID()
2538	const now = Math.floor(Date.now() / 1000)
2539	const maxTokens = data.maxTokens \|\| 4096
2540	const disableTemperature = data.disableTemperature ? 1 : 0
2541	const thinkingParameterMode = normalizeThinkingParameterMode(data.thinkingParameterMode)
2542	if (data.active) {
2543	await this.db
2544	.update(schema.modelConfigs)
2545	.set({ active: 0, updatedAt: now })
2546	.where(eq(schema.modelConfigs.active, 1))
2547	.run()
2548	}
2549	await this.db
2550	.insert(schema.modelConfigs)
2551	.values({
2552	id,
2553	name: data.name,
2554	provider: data.provider,
2555	model: data.model,
2556	apiKey: data.apiKey,
2557	baseUrl: data.baseUrl,
2558	maxTokens,
2559	disableTemperature,
2560	thinkingParameterMode,
2561	active: data.active ? 1 : 0,
2562	createdAt: now,
2563	updatedAt: now
2564	})
2565	.onConflictDoUpdate({
2566	target: schema.modelConfigs.id,
2567	set: {
2568	name: data.name,
2569	provider: data.provider,
2570	model: data.model,
2571	apiKey: data.apiKey,
2572	baseUrl: data.baseUrl,
2573	maxTokens,
2574	disableTemperature,
2575	thinkingParameterMode,
2576	active: data.active ? 1 : 0,
2577	updatedAt: now
2578	}
2579	})
2580	.run()
2581	return id
2582	}
2583
2584	async setActiveModelConfig(id: string): Promise<void> {
2585	const now = Math.floor(Date.now() / 1000)
2586	const existing = await this.db
2587	.select()
2588	.from(schema.modelConfigs)
2589	.where(eq(schema.modelConfigs.id, id))
2590	.get()
2591	if (!existing) throw new Error('Model config does not exist')
2592	await this.db
2593	.update(schema.modelConfigs)
2594	.set({ active: 0, updatedAt: now })
2595	.where(eq(schema.modelConfigs.active, 1))
2596	.run()
2597	await this.db
2598	.update(schema.modelConfigs)
2599	.set({ active: 1, updatedAt: now })
2600	.where(eq(schema.modelConfigs.id, id))
2601	.run()
2602	}
2603
2604	async deleteModelConfig(id: string): Promise<void> {
2605	const existing = await this.db
2606	.select()
2607	.from(schema.modelConfigs)
2608	.where(eq(schema.modelConfigs.id, id))
2609	.get()
2610	if (!existing) throw new Error('Model config does not exist')
2611	await this.db.delete(schema.modelConfigs).where(eq(schema.modelConfigs.id, id)).run()
2612	}
2613
2614	// ========== Image Model Configs ==========
2615
2616	async listImageModelConfigs(): Promise<ImageModelConfigRow[]> {
2617	const results = await this.db
2618	.select()
2619	.from(schema.imageModelConfigs)
2620	.orderBy(desc(schema.imageModelConfigs.active), desc(schema.imageModelConfigs.updatedAt))
2621	.all()
2622	return results as unknown as ImageModelConfigRow[]
2623	}
2624
2625	async getActiveImageModelConfig(): Promise<ImageModelConfigRow \| undefined> {
2626	const result = await this.db
2627	.select()
2628	.from(schema.imageModelConfigs)
2629	.where(eq(schema.imageModelConfigs.active, 1))
2630	.limit(1)
2631	.get()
2632	return result as unknown as ImageModelConfigRow \| undefined
2633	}
2634
2635	async getImageModelConfig(id: string): Promise<ImageModelConfigRow \| undefined> {
2636	const result = await this.db
2637	.select()
2638	.from(schema.imageModelConfigs)
2639	.where(eq(schema.imageModelConfigs.id, id))
2640	.limit(1)
2641	.get()
2642	return result as unknown as ImageModelConfigRow \| undefined
2643	}
2644
2645	async upsertImageModelConfig(data: {
2646	id?: string
2647	name: string
2648	provider: string
2649	modelConfig: string
2650	active?: boolean
2651	}): Promise<string> {
2652	const id = data.id \|\| crypto.randomUUID()
2653	const now = Math.floor(Date.now() / 1000)
2654	if (data.active) {
2655	await this.db
2656	.update(schema.imageModelConfigs)
2657	.set({ active: 0, updatedAt: now })
2658	.where(eq(schema.imageModelConfigs.active, 1))
2659	.run()
2660	}
2661	await this.db
2662	.insert(schema.imageModelConfigs)
2663	.values({
2664	id,
2665	name: data.name,
2666	provider: data.provider,
2667	modelConfig: data.modelConfig,
2668	active: data.active ? 1 : 0,
2669	createdAt: now,
2670	updatedAt: now
2671	})
2672	.onConflictDoUpdate({
2673	target: schema.imageModelConfigs.id,
2674	set: {
2675	name: data.name,
2676	provider: data.provider,
2677	modelConfig: data.modelConfig,
2678	active: data.active ? 1 : 0,
2679	updatedAt: now
2680	}
2681	})
2682	.run()
2683	return id
2684	}
2685
2686	async setActiveImageModelConfig(id: string): Promise<void> {
2687	const now = Math.floor(Date.now() / 1000)
2688	const existing = await this.db
2689	.select()
2690	.from(schema.imageModelConfigs)
2691	.where(eq(schema.imageModelConfigs.id, id))
2692	.get()
2693	if (!existing) throw new Error('Image model config does not exist')
2694	await this.db
2695	.update(schema.imageModelConfigs)
2696	.set({ active: 0, updatedAt: now })
2697	.where(eq(schema.imageModelConfigs.active, 1))
2698	.run()
2699	await this.db
2700	.update(schema.imageModelConfigs)
2701	.set({ active: 1, updatedAt: now })
2702	.where(eq(schema.imageModelConfigs.id, id))
2703	.run()
2704	}
2705
2706	async deleteImageModelConfig(id: string): Promise<void> {
2707	const existing = await this.db
2708	.select()
2709	.from(schema.imageModelConfigs)
2710	.where(eq(schema.imageModelConfigs.id, id))
2711	.get()
2712	if (!existing) throw new Error('Image model config does not exist')
2713	await this.db.delete(schema.imageModelConfigs).where(eq(schema.imageModelConfigs.id, id)).run()
2714	}
2715
2716	// ========== Image Generation Histories ==========
2717
2718	async listImageGenerationHistories(
2719	sessionId: string,
2720	pageId: string
2721	): Promise<ImageGenerationHistoryRow[]> {
2722	const results = await this.db
2723	.select()
2724	.from(schema.imageGenerationHistories)
2725	.where(
2726	and(
2727	eq(schema.imageGenerationHistories.sessionId, sessionId),
2728	eq(schema.imageGenerationHistories.pageId, pageId)
2729	)
2730	)
2731	.orderBy(desc(schema.imageGenerationHistories.createdAt))
2732	.limit(50)
2733	.all()
2734	return results as unknown as ImageGenerationHistoryRow[]
2735	}
2736
2737	async insertImageGenerationHistory(data: {
2738	id?: string
2739	sessionId: string
2740	pageId: string
2741	prompt: string
2742	imagePaths: string[]
2743	modelConfigId: string
2744	provider: string
2745	model: string
2746	createdAt?: number
2747	}): Promise<string> {
2748	const id = data.id \|\| crypto.randomUUID()
2749	await this.db
2750	.insert(schema.imageGenerationHistories)
2751	.values({
2752	id,
2753	sessionId: data.sessionId,
2754	pageId: data.pageId,
2755	prompt: data.prompt,
2756	imagePaths: JSON.stringify(data.imagePaths),
2757	modelConfigId: data.modelConfigId,
2758	provider: data.provider,
2759	model: data.model,
2760	createdAt: data.createdAt \|\| Math.floor(Date.now() / 1000)
2761	})
2762	.run()
2763	return id
2764	}
2765
2766	// ========== Preferences ==========
2767
2768	async getActiveUserPreferences(): Promise<UserPreference[]> {
2769	const results = await this.db
2770	.select()
2771	.from(schema.userPreferences)
2772	.where(gt(schema.userPreferences.confidence, 0.3))
2773	.orderBy(desc(schema.userPreferences.confidence), desc(schema.userPreferences.lastUsedAt))
2774	.limit(10)
2775	.all()
2776
2777	return results.map((r) => ({
2778	key: r.key,
2779	value: JSON.parse(r.value),
2780	confidence: r.confidence,
2781	source_sessions: r.sourceSessions ? JSON.parse(r.sourceSessions) : [],
2782	created_at: r.createdAt,
2783	updated_at: r.updatedAt,
2784	last_used_at: r.lastUsedAt
2785	})) as unknown as UserPreference[]
2786	}
2787
2788	async upsertPreference(
2789	key: string,
2790	data: { value: unknown; confidence?: number; sourceSessions?: string[] }
2791	): Promise<void> {
2792	const now = Math.floor(Date.now() / 1000)
2793	const existing = await this.db
2794	.select()
2795	.from(schema.userPreferences)
2796	.where(eq(schema.userPreferences.key, key))
2797	.get()
2798
2799	if (existing) {
2800	const existingSources = existing.sourceSessions ? JSON.parse(existing.sourceSessions) : []
2801	const newSources = data.sourceSessions
2802	? [...new Set([...existingSources, ...data.sourceSessions])]
2803	: existingSources
2804	const baseConfidence = existing.confidence ?? 0.5
2805	const increment = (data.confidence ?? 0.5) * 0.3
2806	const newConfidence = Math.min(1.0, baseConfidence + increment)
2807
2808	await this.db
2809	.update(schema.userPreferences)
2810	.set({
2811	value: JSON.stringify(data.value),
2812	confidence: newConfidence,
2813	sourceSessions: JSON.stringify(newSources),
2814	updatedAt: now,
2815	lastUsedAt: now
2816	})
2817	.where(eq(schema.userPreferences.key, key))
2818	.run()
2819	} else {
2820	await this.db
2821	.insert(schema.userPreferences)
2822	.values({
2823	key,
2824	value: JSON.stringify(data.value),
2825	confidence: data.confidence \|\| 0.5,
2826	sourceSessions: JSON.stringify(data.sourceSessions \|\| []),
2827	createdAt: now,
2828	updatedAt: now,
2829	lastUsedAt: now
2830	})
2831	.run()
2832	}
2833	}
2834
2835	async decayPreferences(): Promise<void> {
2836	await this.db
2837	.update(schema.userPreferences)
2838	.set({ confidence: sql`${schema.userPreferences.confidence} * 0.95` })
2839	.where(gt(schema.userPreferences.confidence, 0.1))
2840	.run()
2841
2842	await this.db
2843	.delete(schema.userPreferences)
2844	.where(lte(schema.userPreferences.confidence, 0.1))
2845	.run()
2846	}
2847
2848	// ========== Projects ==========
2849
2850	async createProject(data: {
2851	session_id: string
2852	title: string
2853	output_path: string
2854	root_path?: string \| null
2855	}): Promise<string> {
2856	const id = crypto.randomUUID()
2857	const now = Math.floor(Date.now() / 1000)
2858
2859	await this.db
2860	.insert(schema.projects)
2861	.values({
2862	id,
2863	sessionId: data.session_id,
2864	title: data.title,
2865	outputPath: data.output_path,
2866	rootPath: data.root_path \|\| data.output_path,
2867	fileCount: 0,
2868	totalSize: 0,
2869	status: 'draft',
2870	createdAt: now,
2871	updatedAt: now
2872	})
2873	.run()
2874
2875	return id
2876	}
2877
2878	async getProject(sessionId: string): Promise<Project \| undefined> {
2879	const row = await this.db
2880	.select({
2881	id: schema.projects.id,
2882	session_id: schema.projects.sessionId,
2883	title: schema.projects.title,
2884	output_path: schema.projects.outputPath,
2885	root_path: schema.projects.rootPath,
2886	file_count: schema.projects.fileCount,
2887	total_size: schema.projects.totalSize,
2888	status: schema.projects.status,
2889	created_at: schema.projects.createdAt,
2890	updated_at: schema.projects.updatedAt
2891	})
2892	.from(schema.projects)
2893	.where(eq(schema.projects.sessionId, sessionId))
2894	.orderBy(desc(schema.projects.createdAt))
2895	.limit(1)
2896	.get()
2897
2898	return row as Project \| undefined
2899	}
2900
2901	async updateProjectStatus(
2902	projectId: string,
2903	status: 'draft' \| 'published' \| 'exported'
2904	): Promise<void> {
2905	const now = Math.floor(Date.now() / 1000)
2906	await this.db
2907	.update(schema.projects)
2908	.set({ status, updatedAt: now })
2909	.where(eq(schema.projects.id, projectId))
2910	.run()
2911	}
2912
2913	// ========== Styles ==========
2914
2915	async countStyles(): Promise<number> {
2916	const result = await this.db.select({ count: count() }).from(schema.styles).get()
2917	return result?.count ?? 0
2918	}
2919
2920	async syncInstalledStylesToDatabase(installedRootPath: string): Promise<void> {
2921	const systemPath = path.join(installedRootPath, 'system')
2922	const userPath = path.join(installedRootPath, 'user')
2923	await this._refreshStylesCache()
2924
2925	const syncDirectory = async (root: string, scope: 'system' \| 'user'): Promise<void> => {
2926	if (!fs.existsSync(root)) return
2927	const packageNames = await listStylePackageDirectories(root)
2928	for (const packageName of packageNames) {
2929	try {
2930	const stylePackage = await readStylePackage(path.join(root, packageName))
2931	const item = stylePackage.json
2932	const existing = this._stylesCache.find((row) => row.style === item.style)
2933	const source: StyleSource =
2934	scope === 'system' ? 'builtin' : item.source === 'override' ? 'override' : 'custom'
2935	const packageDir = path.posix.join(scope, packageName)
2936
2937	if (!existing) {
2938	await this.createStyleRow({
2939	id: scope === 'user' ? packageName : undefined,
2940	style: item.style,
2941	styleName: item.name.zh,
2942	styleNameZh: item.name.zh,
2943	styleNameEn: item.name.en,
2944	description: item.description,
2945	category: item.category,
2946	aliases: item.aliases,
2947	source,
2948	styleSkill: stylePackage.skillMarkdown,
2949	version: item.version,
2950	styleCase: item.styleCase,
2951	packageDir
2952	})
2953	continue
2954	}
2955
2956	if (scope === 'system') {
2957	if (existing.source === 'builtin') {
2958	await this.updateStyleRow(existing.id, {
2959	styleName: item.name.zh,
2960	styleNameZh: item.name.zh,
2961	styleNameEn: item.name.en,
2962	description: item.description,
2963	category: item.category,
2964	aliases: item.aliases,
2965	styleSkill: stylePackage.skillMarkdown,
2966	version: item.version,
2967	styleCase: item.styleCase,
2968	packageDir
2969	})
2970	continue
2971	}
2972	if (
2973	existing.source === 'override' &&
2974	compareStyleVersion(item.version, existing.version) > 0
2975	) {
2976	await this.updateStyleRow(existing.id, { version: item.version })
2977	}
2978	continue
2979	}
2980	await this.updateStyleRow(existing.id, {
2981	styleName: item.name.zh,
2982	styleNameZh: item.name.zh,
2983	styleNameEn: item.name.en,
2984	description: item.description,
2985	category: item.category,
2986	aliases: item.aliases,
2987	source,
2988	styleSkill: stylePackage.skillMarkdown,
2989	version: item.version,
2990	styleCase: item.styleCase,
2991	packageDir
2992	})
2993	} catch (error) {
2994	console.warn('[db] failed to sync installed style package', {
2995	path: path.join(root, packageName),
2996	message: error instanceof Error ? error.message : String(error)
2997	})
2998	}
2999	}
3000	}
3001
3002	await syncDirectory(systemPath, 'system')
3003	await syncDirectory(userPath, 'user')
3004	await this._refreshStylesCache()
3005	}
3006
3007	private async _refreshStylesCache(): Promise<void> {
3008	const results = await this.db
3009	.select()
3010	.from(schema.styles)
3011	.orderBy(asc(schema.styles.style))
3012	.all()
3013	this._stylesCache = (results as unknown as StyleRow[]).map((row) => ({
3014	...row,
3015	version: normalizeStyleVersion(row.version)
3016	}))
3017	}
3018
3019	/** Synchronous read from in-memory cache. Used by prompt builders. */
3020	listStyleRowsSync(): StyleRow[] {
3021	return this._stylesCache
3022	}
3023
3024	/** Synchronous cache lookup. */
3025	getStyleRowSync(styleId: string): StyleRow \| undefined {
3026	return this._stylesCache.find((r) => r.id === styleId)
3027	}
3028
3029	/** Synchronous cache lookup by style key. */
3030	getStyleRowByStyleSync(style: string): StyleRow \| undefined {
3031	return this._stylesCache.find((r) => r.style === style)
3032	}
3033
3034	async listStyleRows(): Promise<StyleRow[]> {
3035	const results = await this.db
3036	.select()
3037	.from(schema.styles)
3038	.orderBy(asc(schema.styles.style))
3039	.all()
3040	return (results as unknown as StyleRow[]).map((row) => ({
3041	...row,
3042	version: normalizeStyleVersion(row.version)
3043	}))
3044	}
3045
3046	async getStyleRow(styleId: string): Promise<StyleRow \| undefined> {
3047	const result = await this.db
3048	.select()
3049	.from(schema.styles)
3050	.where(eq(schema.styles.id, styleId))
3051	.get()
3052	return result
3053	? ({
3054	...(result as unknown as StyleRow),
3055	version: normalizeStyleVersion((result as unknown as StyleRow).version)
3056	} as StyleRow)
3057	: undefined
3058	}
3059
3060	async getStyleRowByStyle(style: string): Promise<StyleRow \| undefined> {
3061	const result = await this.db
3062	.select()
3063	.from(schema.styles)
3064	.where(eq(schema.styles.style, style))
3065	.get()
3066	return result
3067	? ({
3068	...(result as unknown as StyleRow),
3069	version: normalizeStyleVersion((result as unknown as StyleRow).version)
3070	} as StyleRow)
3071	: undefined
3072	}
3073
3074	async createStyleRow(data: {
3075	id?: string
3076	style: string
3077	styleName: string
3078	styleNameZh?: string
3079	styleNameEn?: string
3080	description?: string
3081	category?: string
3082	aliases?: string[]
3083	source?: StyleSource
3084	styleSkill?: string
3085	version?: string \| number
3086	styleCase?: string
3087	packageDir?: string
3088	}): Promise<string> {
3089	const id = data.id \|\| crypto.randomUUID()
3090	const now = Math.floor(Date.now() / 1000)
3091	await this.db
3092	.insert(schema.styles)
3093	.values({
3094	id,
3095	style: data.style,
3096	styleName: data.styleName,
3097	styleNameZh: data.styleNameZh \|\| data.styleName,
3098	styleNameEn: data.styleNameEn \|\| '',
3099	description: data.description \|\| '',
3100	category: data.category \|\| '',
3101	aliases: JSON.stringify(data.aliases \|\| []),
3102	source: data.source \|\| 'custom',
3103	styleSkill: data.styleSkill \|\| '',
3104	version: normalizeStyleVersion(data.version),
3105	styleCase: data.styleCase \|\| '',
3106	packageDir: data.packageDir \|\| '',
3107	createdAt: now,
3108	updatedAt: now
3109	})
3110	.run()
3111	await this._refreshStylesCache()
3112	return id
3113	}
3114
3115	async updateStyleRow(
3116	styleId: string,
3117	data: {
3118	styleName?: string
3119	styleNameZh?: string
3120	styleNameEn?: string
3121	description?: string
3122	category?: string
3123	aliases?: string[]
3124	source?: StyleSource
3125	styleSkill?: string
3126	version?: string \| number
3127	styleCase?: string
3128	packageDir?: string
3129	active?: boolean
3130	}
3131	): Promise<void> {
3132	const now = Math.floor(Date.now() / 1000)
3133	const set: Record<string, unknown> = { updatedAt: now }
3134	if (data.styleName !== undefined) set.styleName = data.styleName
3135	if (data.styleNameZh !== undefined) set.styleNameZh = data.styleNameZh
3136	if (data.styleNameEn !== undefined) set.styleNameEn = data.styleNameEn
3137	if (data.description !== undefined) set.description = data.description
3138	if (data.category !== undefined) set.category = data.category
3139	if (data.aliases !== undefined) set.aliases = JSON.stringify(data.aliases)
3140	if (data.source !== undefined) set.source = data.source
3141	if (data.styleSkill !== undefined) set.styleSkill = data.styleSkill
3142	if (data.version !== undefined) set.version = normalizeStyleVersion(data.version)
3143	if (data.styleCase !== undefined) set.styleCase = data.styleCase
3144	if (data.packageDir !== undefined) set.packageDir = data.packageDir
3145	if (data.active !== undefined) set.active = data.active
3146	await this.db.update(schema.styles).set(set).where(eq(schema.styles.id, styleId)).run()
3147	await this._refreshStylesCache()
3148	}
3149
3150	async setStyleFavorite(styleId: string, favoriteAt: number \| null): Promise<number \| null> {
3151	const existing = await this.getStyleRow(styleId)
3152	if (!existing) {
3153	throw new Error(`Style not found: ${styleId}`)
3154	}
3155	await this.db
3156	.update(schema.styles)
3157	.set({ favoriteAt })
3158	.where(eq(schema.styles.id, styleId))
3159	.run()
3160	await this._refreshStylesCache()
3161	return favoriteAt
3162	}
3163
3164	async deleteStyleRow(styleId: string): Promise<boolean> {
3165	const existing = await this.getStyleRow(styleId)
3166	if (!existing) return false
3167	await this.db.delete(schema.styles).where(eq(schema.styles.id, styleId)).run()
3168	await this._refreshStylesCache()
3169	return true
3170	}
3171
3172	async getThumbnailRecord(
3173	resourceType: HtmlThumbnailResourceType,
3174	resourceId: string,
3175	variant = 'default'
3176	): Promise<ThumbnailRecord \| undefined> {
3177	const row = await this.db
3178	.select()
3179	.from(schema.thumbnails)
3180	.where(
3181	and(
3182	eq(schema.thumbnails.resourceType, resourceType),
3183	eq(schema.thumbnails.resourceId, resourceId),
3184	eq(schema.thumbnails.variant, variant)
3185	)
3186	)
3187	.get()
3188	return row as ThumbnailRecord \| undefined
3189	}
3190
3191	async getThumbnailRecords(
3192	resourceType: HtmlThumbnailResourceType,
3193	resourceIds: string[],
3194	variant = 'default'
3195	): Promise<ThumbnailRecord[]> {
3196	const ids = Array.from(
3197	new Set(resourceIds.map((id) => String(id \|\| '').trim()).filter(Boolean))
3198	)
3199	if (ids.length === 0) return []
3200	const rows = await this.db
3201	.select()
3202	.from(schema.thumbnails)
3203	.where(
3204	and(
3205	eq(schema.thumbnails.resourceType, resourceType),
3206	inArray(schema.thumbnails.resourceId, ids),
3207	eq(schema.thumbnails.variant, variant)
3208	)
3209	)
3210	.all()
3211	return rows as ThumbnailRecord[]
3212	}
3213
3214	async upsertThumbnailRecord(data: {
3215	resourceType: HtmlThumbnailResourceType
3216	resourceId: string
3217	variant: string
3218	sourcePath: string
3219	sourceMtimeMs: number
3220	signature: string
3221	thumbnailPath: string
3222	status: ThumbnailStatus
3223	error?: string \| null
3224	}): Promise<void> {
3225	const now = Date.now()
3226	const key = crypto
3227	.createHash('sha256')
3228	.update(
3229	JSON.stringify({
3230	resourceType: data.resourceType,
3231	resourceId: data.resourceId,
3232	variant: data.variant
3233	})
3234	)
3235	.digest('hex')
3236	.slice(0, 32)
3237	await this.db
3238	.insert(schema.thumbnails)
3239	.values({
3240	key,
3241	resourceType: data.resourceType,
3242	resourceId: data.resourceId,
3243	variant: data.variant,
3244	sourcePath: data.sourcePath,
3245	sourceMtimeMs: data.sourceMtimeMs,
3246	signature: data.signature,
3247	thumbnailPath: data.thumbnailPath,
3248	status: data.status,
3249	error: data.error \|\| null,
3250	createdAt: now,
3251	updatedAt: now
3252	})
3253	.onConflictDoUpdate({
3254	target: schema.thumbnails.key,
3255	set: {
3256	sourcePath: data.sourcePath,
3257	sourceMtimeMs: data.sourceMtimeMs,
3258	signature: data.signature,
3259	thumbnailPath: data.thumbnailPath,
3260	status: data.status,
3261	error: data.error \|\| null,
3262	updatedAt: now
3263	}
3264	})
3265	.run()
3266	}
3267
3268	async failInterruptedThumbnailTasks(): Promise<void> {
3269	await this.db
3270	.update(schema.thumbnails)
3271	.set({
3272	status: 'failed',
3273	error: '应用退出时任务尚未完成',
3274	updatedAt: Date.now()
3275	})
3276	.where(inArray(schema.thumbnails.status, ['queued', 'running']))
3277	.run()
3278	}
3279
3280	async getSessionStyleSnapshot(sessionId: string): Promise<SessionStyleSnapshotRow \| undefined> {
3281	const row = await this.db
3282	.select()
3283	.from(schema.sessionStyleSnapshots)
3284	.where(eq(schema.sessionStyleSnapshots.sessionId, sessionId))
3285	.get()
3286	return row as unknown as SessionStyleSnapshotRow \| undefined
3287	}
3288
3289	async createSessionStyleSnapshot(
3290	sessionId: string,
3291	styleId?: string \| null
3292	): Promise<SessionStyleSnapshotRow> {
3293	const style = this.resolveSnapshotStyleRow(styleId)
3294	const now = Math.floor(Date.now() / 1000)
3295	await this.db
3296	.insert(schema.sessionStyleSnapshots)
3297	.values({
3298	id: crypto.randomUUID(),
3299	sessionId,
3300	styleId: style.id,
3301	styleKey: style.style,
3302	styleName: style.styleName,
3303	styleNameZh: style.styleNameZh \|\| style.styleName,
3304	styleNameEn: style.styleNameEn \|\| '',
3305	description: style.description,
3306	category: style.category,
3307	aliases: style.aliases \|\| '[]',
3308	source: style.source,
3309	version: normalizeStyleVersion(style.version),
3310	styleCase: style.styleCase,
3311	packageDir: style.packageDir \|\| '',
3312	styleSkill: style.styleSkill,
3313	createdAt: now
3314	})
3315	.onConflictDoNothing({ target: schema.sessionStyleSnapshots.sessionId })
3316	.run()
3317	const existing = await this.getSessionStyleSnapshot(sessionId)
3318	if (!existing) throw new Error('Session style snapshot was not created')
3319	return existing
3320	}
3321
3322	async replaceSessionStyleSnapshot(
3323	sessionId: string,
3324	styleId?: string \| null
3325	): Promise<SessionStyleSnapshotRow> {
3326	await this.db
3327	.delete(schema.sessionStyleSnapshots)
3328	.where(eq(schema.sessionStyleSnapshots.sessionId, sessionId))
3329	.run()
3330	return this.createSessionStyleSnapshot(sessionId, styleId)
3331	}
3332
3333	async getOrCreateSessionStyleSnapshot(sessionId: string): Promise<SessionStyleSnapshotRow> {
3334	const existing = await this.getSessionStyleSnapshot(sessionId)
3335	if (existing) return existing
3336	const session = await this.getSession(sessionId)
3337	return this.createSessionStyleSnapshot(sessionId, session?.styleId)
3338	}
3339
3340	async copySessionStyleSnapshot(sourceSessionId: string, targetSessionId: string): Promise<void> {
3341	const source = await this.getOrCreateSessionStyleSnapshot(sourceSessionId)
3342	await this.db
3343	.delete(schema.sessionStyleSnapshots)
3344	.where(eq(schema.sessionStyleSnapshots.sessionId, targetSessionId))
3345	.run()
3346	await this.db
3347	.insert(schema.sessionStyleSnapshots)
3348	.values({
3349	id: crypto.randomUUID(),
3350	sessionId: targetSessionId,
3351	styleId: source.styleId,
3352	styleKey: source.styleKey,
3353	styleName: source.styleName,
3354	styleNameZh: source.styleNameZh \|\| source.styleName,
3355	styleNameEn: source.styleNameEn \|\| '',
3356	description: source.description,
3357	category: source.category,
3358	aliases: source.aliases,
3359	source: source.source,
3360	version: normalizeStyleVersion(source.version),
3361	styleCase: source.styleCase,
3362	packageDir: source.packageDir \|\| '',
3363	styleSkill: source.styleSkill,
3364	createdAt: Math.floor(Date.now() / 1000)
3365	})
3366	.onConflictDoNothing({ target: schema.sessionStyleSnapshots.sessionId })
3367	.run()
3368	}
3369
3370	async backfillSessionStyleSnapshots(): Promise<{
3371	scanned: number
3372	created: number
3373	fallback: number
3374	failed: number
3375	}> {
3376	const rows = await this.db
3377	.select({ session: schema.sessions })
3378	.from(schema.sessions)
3379	.leftJoin(
3380	schema.sessionStyleSnapshots,
3381	eq(schema.sessionStyleSnapshots.sessionId, schema.sessions.id)
3382	)
3383	.where(isNull(schema.sessionStyleSnapshots.id))
3384	.all()
3385
3386	let created = 0
3387	let fallback = 0
3388	let failed = 0
3389	for (const row of rows) {
3390	const session = row.session as unknown as Session
3391	try {
3392	const snapshot = await this.createSessionStyleSnapshot(session.id, session.styleId)
3393	if (!session.styleId \|\| session.styleId !== snapshot.styleId) {
3394	fallback += 1
3395	await this.updateSessionStyleId(session.id, snapshot.styleId)
3396	}
3397	created += 1
3398	} catch (error) {
3399	failed += 1
3400	console.warn('[db] failed to backfill session style snapshot', {
3401	sessionId: session.id,
3402	message: error instanceof Error ? error.message : String(error)
3403	})
3404	}
3405	}
3406	return { scanned: rows.length, created, fallback, failed }
3407	}
3408
3409	styleRowToPackageJson(styleId: string): ReturnType<typeof styleRowToPackageJson> {
3410	const row = this.getStyleRowSync(styleId)
3411	if (!row) throw new Error('style 不存在：' + styleId)
3412	return styleRowToPackageJson({
3413	style: row.style,
3414	styleName: row.styleName,
3415	styleNameZh: row.styleNameZh \|\| row.styleName,
3416	styleNameEn: row.styleNameEn \|\| '',
3417	description: row.description,
3418	category: row.category,
3419	aliases: row.aliases,
3420	source: row.source,
3421	version: row.version,
3422	styleCase: row.styleCase
3423	})
3424	}
3425
3426	private resolveSnapshotStyleRow(styleId?: string \| null): StyleRow {
3427	if (styleId) {
3428	const byId = this._stylesCache.find((row) => row.id === styleId)
3429	if (byId) return byId
3430	const byStyle = this._stylesCache.find((row) => row.style === styleId)
3431	if (byStyle) return byStyle
3432	}
3433	const activeRows = this._stylesCache.filter((row) => row.active !== false)
3434	const fallback =
3435	activeRows.find((row) => row.style === 'minimal-white') \|\|
3436	this._stylesCache.find((row) => row.style === 'minimal-white') \|\|
3437	activeRows[0] \|\|
3438	this._stylesCache[0]
3439	if (!fallback) throw new Error('No style rows available for session snapshot')
3440	return fallback
3441	}
3442	}
3443

3443 lines TYPESCRIPT