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 { nanoid } from 'nanoid'
11	import { runDatabasePatches } from './patch'
12	import {
13	compareStyleVersion,
14	listStylePackageDirectories,
15	normalizeStyleVersion,
16	readStylePackage,
17	styleRowToPackageJson
18	} from '../styles'
19	import type { HtmlThumbnailResourceType } from '@shared/thumbnail'
20	import type {
21	ModelUsageByHour,
22	ModelUsagePeriod,
23	ModelUsageStats,
24	ModelUsageTotals
25	} from '@shared/model-usage'
26	import type { AnimationPreferencesPayload } from '@shared/generation'
27	import { normalizeThinkingParameterMode } from '@shared/model-config'
28	import { requirePersistedSlideSize, type SlideSizePresetId } from '@shared/slide-size'
29	import type { HtmlEditDocument, HtmlEditMessage, HtmlEditVersion } from './schema'
30
31	type SessionStatus = 'active' \| 'completed' \| 'failed' \| 'archived'
32	type MessageRole = 'user' \| 'assistant' \| 'system' \| 'tool'
33	type MessageType = 'text' \| 'tool_call' \| 'tool_result' \| 'stream_chunk'
34	type ChatScope = 'main' \| 'page'
35	type StyleSource = 'builtin' \| 'custom' \| 'override'
36	type GenerationRunMode =
37	\| 'generate'
38	\| 'retry'
39	\| 'edit'
40	\| 'import'
41	\| 'addPage'
42	\| 'retrySinglePage'
43	\| 'style-switch'
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	export type SessionJobStatus = 'pending' \| 'active' \| 'finished' \| 'aborted'
55	type GenerationPageStatus = 'pending' \| 'running' \| 'completed' \| 'failed'
56	type SessionPageStatus = schema.SessionPageStatus
57	type SourcePageSkeletonRole = 'chapter-divider' \| 'content'
58	type SourcePageSkeletonConfidence = 'high' \| 'medium' \| 'low'
59	type SessionOperationType =
60	\| 'generate'
61	\| 'edit'
62	\| 'addPage'
63	\| 'retry'
64	\| 'import'
65	\| 'rollback'
66	\| 'reorder'
67	\| 'delete'
68	type SessionOperationScope = 'session' \| 'deck' \| 'page' \| 'selector' \| 'shell'
69	type SessionOperationStatus = 'committing' \| 'completed' \| 'failed' \| 'noop'
70
71	export interface Session {
72	id: string
73	title: string
74	topic: string \| null
75	styleId: string \| null
76	page_count: number \| null
77	slideSizeId?: SlideSizePresetId
78	slideWidth?: number
79	slideHeight?: number
80	reference_document_path: string \| null
81	referenceDocumentPath?: string \| null
82	status: SessionStatus
83	provider: string
84	model: string
85	created_at: number
86	updated_at: number
87	metadata: string \| null
88	designContract?: string \| null
89	currentOperationId?: string \| null
90	currentCommit?: string \| null
91	visual_enabled?: number
92	visualEnabled?: number
93	image_model_config_id?: string \| null
94	imageModelConfigId?: string \| null
95	}
96
97	export interface Message {
98	id: string
99	session_id: string
100	chat_scope: ChatScope
101	page_id: string \| null
102	selector: string \| null
103	image_paths: string[] \| null
104	video_paths: string[] \| null
105	role: MessageRole
106	content: string
107	type: MessageType
108	tool_name: string \| null
109	tool_call_id: string \| null
110	token_count: number \| null
111	run_model: string \| null
112	created_at: number
113	}
114
115	interface MemorySummary {
116	id: string
117	session_id: string
118	message_range_start: number
119	message_range_end: number
120	summary: string
121	token_count: number \| null
122	created_at: number
123	}
124
125	interface UserPreference {
126	key: string
127	value: unknown
128	confidence: number
129	source_sessions: string[]
130	created_at: number
131	updated_at: number
132	last_used_at: number \| null
133	}
134
135	interface Project {
136	id: string
137	session_id: string
138	title: string
139	output_path: string
140	root_path: string \| null
141	file_count: number
142	total_size: number
143	status: 'draft' \| 'published' \| 'exported'
144	created_at: number
145	updated_at: number
146	}
147
148	export interface GenerationRunRecord {
149	id: string
150	session_id: string
151	mode: GenerationRunMode
152	status: GenerationRunStatus
153	total_pages: number
154	error: string \| null
155	metadata: string \| null
156	animation_preferences: string \| null
157	model_config_id: string \| null
158	created_at: number
159	updated_at: number
160	}
161
162	export interface SessionJobRecord {
163	id: string
164	session_id: string
165	kind: SessionJobKind
166	previous_session_status: SessionStatus
167	target_page_id: string \| null
168	target_page_number: number \| null
169	selector: string \| null
170	total_pages: number \| null
171	status: SessionJobStatus
172	abort_reason: string \| null
173	created_at: number
174	activated_at: number \| null
175	updated_at: number
176	finished_at: number \| null
177	}
178
179	type GenerationRunCreateData = {
180	id?: string
181	sessionId: string
182	mode: GenerationRunMode
183	totalPages: number
184	metadata?: unknown
185	animationPreferences?: AnimationPreferencesPayload \| null
186	modelConfigId?: string \| null
187	}
188
189	type SessionJobCreateData = {
190	id: string
191	sessionId: string
192	kind: SessionJobKind
193	status: Extract<SessionJobStatus, 'pending' \| 'active'>
194	previousSessionStatus: SessionStatus
195	targetPageId?: string
196	targetPageNumber?: number
197	selector?: string
198	totalPages?: number
199	}
200
201	type GenerationPageCreateData = {
202	pageId: string
203	pageNumber: number
204	title: string
205	contentOutline?: string \| null
206	layoutIntent?: string \| null
207	layoutId?: string \| null
208	layoutContractVersion?: number \| null
209	htmlPath?: string \| null
210	status?: Extract<GenerationPageStatus, 'pending' \| 'running'>
211	error?: string \| null
212	retryCount?: number
213	}
214
215	export interface GenerationPageRecord {
216	id: string
217	run_id: string
218	session_id: string
219	page_id: string
220	page_number: number
221	title: string
222	content_outline: string \| null
223	layout_intent: string \| null
224	layout_id: string \| null
225	layout_contract_version: number \| null
226	html_path: string \| null
227	status: GenerationPageStatus
228	error: string \| null
229	retry_count: number
230	created_at: number
231	updated_at: number
232	}
233
234	export interface SessionPageRecord {
235	id: string
236	session_id: string
237	legacy_page_id: string \| null
238	file_slug: string
239	page_number: number
240	title: string
241	html_path: string
242	layout_intent: string \| null
243	layout_id: string \| null
244	layout_contract_version: number \| null
245	status: SessionPageStatus
246	error: string \| null
247	created_at: number
248	updated_at: number
249	deleted_at: number \| null
250	}
251
252	export type ImageFulfillmentJobStatus =
253	\| 'pending'
254	\| 'running'
255	\| 'finalizing'
256	\| 'completed'
257	\| 'degraded'
258	\| 'failed'
259	\| 'cancelled'
260
261	export type ImageFulfillmentIntentStatus =
262	\| 'pending'
263	\| 'generating'
264	\| 'generated'
265	\| 'used'
266	\| 'fallback'
267	\| 'layout_failed'
268	\| 'failed'
269	\| 'cancelled'
270
271	export interface ImageFulfillmentJobRecord {
272	id: string
273	run_id: string
274	session_id: string
275	session_page_id: string
276	page_id: string
277	layout_id: string \| null
278	layout_contract_version: number \| null
279	image_model_config_id: string \| null
280	image_provider: string \| null
281	image_model: string \| null
282	attempt: number
283	retry_of_job_id: string \| null
284	idempotency_key: string \| null
285	status: ImageFulfillmentJobStatus
286	error: string \| null
287	cancel_requested_at: number \| null
288	lease_owner: string \| null
289	lease_expires_at: number \| null
290	finalization_manifest_path: string \| null
291	created_at: number
292	started_at: number \| null
293	updated_at: number
294	finished_at: number \| null
295	}
296
297	export interface ImageFulfillmentIntentRecord {
298	id: string
299	job_id: string
300	slot_id: string
301	layout_slot_id: string
302	role: string
303	layer: string
304	request_version: number
305	size_hint: string \| null
306	subject: string
307	text_zone: string \| null
308	subject_zone: string \| null
309	negative_space: string \| null
310	avoid_json: string \| null
311	request_json: string
312	image_history_id: string \| null
313	asset_path: string \| null
314	width: number \| null
315	height: number \| null
316	mime_type: string \| null
317	attempt: number
318	retry_of_intent_id: string \| null
319	status: ImageFulfillmentIntentStatus
320	error: string \| null
321	created_at: number
322	updated_at: number
323	}
324
325	export type ImageFulfillmentIntentCreateData = {
326	id?: string
327	slotId: string
328	layoutSlotId: string
329	role: string
330	layer: string
331	requestVersion?: number
332	sizeHint?: string \| null
333	subject: string
334	textZone?: string \| null
335	subjectZone?: string \| null
336	negativeSpace?: string \| null
337	avoidJson?: string \| null
338	requestJson: string
339	retryOfIntentId?: string \| null
340	}
341
342	export type ThumbnailStatus = 'queued' \| 'running' \| 'completed' \| 'failed'
343
344	export interface ThumbnailRecord {
345	key: string
346	resourceType: HtmlThumbnailResourceType
347	resourceId: string
348	variant: string
349	sourcePath: string
350	sourceMtimeMs: number
351	signature: string
352	thumbnailPath: string
353	status: ThumbnailStatus
354	error: string \| null
355	createdAt: number
356	updatedAt: number
357	}
358
359	export interface SourcePageSkeletonRecord {
360	id: string
361	session_id: string
362	page_number: number
363	title: string
364	role: SourcePageSkeletonRole
365	source_document_path: string
366	source_document_name: string \| null
367	source_heading: string
368	heading_level: number
369	line_start: number
370	line_end: number
371	agenda_items_json: string \| null
372	reason: string \| null
373	confidence: SourcePageSkeletonConfidence
374	created_at: number
375	updated_at: number
376	}
377
378	const serializeSourcePageSkeletonAgendaItems = (value: unknown): string \| null => {
379	if (!Array.isArray(value)) return null
380	const agendaItems = value
381	.slice(0, 500)
382	.map((item) => {
383	if (!item \|\| typeof item !== 'object' \|\| Array.isArray(item)) return null
384	const record = item as Record<string, unknown>
385	const title = typeof record.title === 'string' ? record.title.trim() : ''
386	const lineStart = Number(record.lineStart ?? record.line_start)
387	return title && Number.isFinite(lineStart) && lineStart >= 1
388	? { title, lineStart: Math.floor(lineStart) }
389	: null
390	})
391	.filter((item): item is { title: string; lineStart: number } => Boolean(item))
392	return agendaItems.length > 0 ? JSON.stringify(agendaItems) : null
393	}
394
395	export interface SessionPageInput {
396	id: string
397	sessionId: string
398	legacyPageId?: string \| null
399	fileSlug: string
400	pageNumber: number
401	title: string
402	htmlPath: string
403	layoutIntent?: string \| null
404	layoutId?: string \| null
405	layoutContractVersion?: number \| null
406	status?: SessionPageStatus
407	error?: string \| null
408	}
409
410	export interface SessionWithPageCount {
411	session: Session
412	pageCount: number
413	}
414
415	export const sessionPageRecordToInput = (page: SessionPageRecord): SessionPageInput => ({
416	id: page.id,
417	sessionId: page.session_id,
418	legacyPageId: page.legacy_page_id,
419	fileSlug: page.file_slug,
420	pageNumber: page.page_number,
421	title: page.title,
422	htmlPath: page.html_path,
423	layoutIntent: page.layout_intent,
424	layoutId: page.layout_id,
425	layoutContractVersion: page.layout_contract_version,
426	status: page.status,
427	error: page.error
428	})
429
430	export interface StyleRow {
431	id: string
432	style: string
433	styleName: string
434	styleNameZh: string
435	styleNameEn: string
436	description: string
437	category: string
438	aliases: string // JSON array
439	source: StyleSource
440	styleSkill: string // plain markdown
441	version: string
442	styleCase: string
443	imageGenerationPrompt: string
444	packageDir: string
445	active: boolean
446	favoriteAt: number \| null
447	createdAt: number
448	updatedAt: number
449	}
450
451	export interface SessionStyleSnapshotRow {
452	id: string
453	sessionId: string
454	styleId: string
455	styleKey: string
456	styleName: string
457	styleNameZh: string
458	styleNameEn: string
459	description: string
460	category: string
461	aliases: string
462	source: StyleSource
463	version: string
464	styleCase: string
465	imageGenerationPrompt: string
466	packageDir: string
467	styleSkill: string
468	createdAt: number
469	}
470
471	export interface ModelConfigRow {
472	id: string
473	name: string
474	provider: string
475	model: string
476	apiKey: string
477	baseUrl: string
478	maxTokens: number
479	disableTemperature: number
480	thinkingParameterMode: string
481	active: number
482	createdAt: number
483	updatedAt: number
484	}
485
486	export interface ImageModelConfigRow {
487	id: string
488	name: string
489	provider: string
490	active: number
491	modelConfig: string
492	createdAt: number
493	updatedAt: number
494	}
495
496	export interface ImageGenerationHistoryRow {
497	id: string
498	sessionId: string
499	pageId: string
500	prompt: string
501	imagePaths: string
502	modelConfigId: string
503	provider: string
504	model: string
505	createdAt: number
506	}
507
508	export interface SessionOperationRecord {
509	id: string
510	session_id: string
511	type: SessionOperationType
512	status: SessionOperationStatus
513	scope: SessionOperationScope \| null
514	prompt: string \| null
515	parent_operation_id: string \| null
516	before_commit: string \| null
517	after_commit: string \| null
518	target_operation_id: string \| null
519	target_commit: string \| null
520	changed_files_json: string
521	changed_pages_json: string
522	tracked_files_json: string
523	metadata_json: string
524	created_at: number
525	completed_at: number \| null
526	}
527
528	export interface SessionOperationPageRecord {
529	id: string
530	operation_id: string
531	session_id: string
532	page_id: string
533	legacy_page_id: string \| null
534	file_slug: string
535	page_number: number
536	title: string
537	html_path: string
538	status: SessionPageStatus
539	error: string \| null
540	created_at: number
541	updated_at: number
542	}
543
544	export class PPTDatabase {
545	private db: ReturnType<typeof drizzle>
546	private client: ReturnType<typeof createClient>
547	private _storagePath: string \| null = null
548	private _initialized = false
549	private _stylesCache: StyleRow[] = []
550
551	constructor(dbPath?: string) {
552	const defaultPath = is.dev
553	? path.join(process.cwd(), 'ohmyppt.dev.db')
554	: path.join(app.getPath('userData'), 'ohmyppt.db')
555	const resolvedPath = dbPath \|\| defaultPath
556
557	const dir = path.dirname(resolvedPath)
558	if (!fs.existsSync(dir)) {
559	fs.mkdirSync(dir, { recursive: true })
560	}
561
562	const url = resolvedPath.startsWith('file:') ? resolvedPath : `file:${resolvedPath}`
563
564	this.client = createClient({ url })
565	this.db = drizzle(this.client, { schema })
566	this._storagePath = null
567	}
568
569	async init(): Promise<void> {
570	if (this._initialized) return
571	await runDatabasePatches({
572	client: this.client,
573	db: this.db,
574	resolveStoragePath: async () =>
575	(await this.getSetting<string>('storage_path').catch(() => '')) \|\| ''
576	})
577	await this._refreshStylesCache()
578	this._initialized = true
579	}
580
581	getStoragePath(): string {
582	return this._storagePath \|\| ''
583	}
584
585	async setStoragePath(storagePath: string): Promise<void> {
586	await this.setSetting('storage_path', storagePath)
587	this._storagePath = storagePath
588	if (!fs.existsSync(storagePath)) {
589	fs.mkdirSync(storagePath, { recursive: true })
590	}
591	}
592
593	async close(): Promise<void> {
594	await this.client.close()
595	this._initialized = false
596	}
597
598	// ========== HTML Editor ==========
599
600	async createHtmlEditDocument(data: {
601	id: string
602	title: string
603	sourcePath?: string \| null
604	htmlPath: string
605	designWidth: number
606	createdAt: number
607	updatedAt: number
608	}): Promise<void> {
609	await this.db.insert(schema.htmlEditDocuments).values({
610	id: data.id,
611	title: data.title,
612	sourcePath: data.sourcePath ?? null,
613	htmlPath: data.htmlPath,
614	designWidth: data.designWidth,
615	createdAt: data.createdAt,
616	updatedAt: data.updatedAt
617	})
618	}
619
620	async touchHtmlEditDocument(docId: string, updatedAt: number): Promise<void> {
621	await this.db
622	.update(schema.htmlEditDocuments)
623	.set({ updatedAt })
624	.where(eq(schema.htmlEditDocuments.id, docId))
625	}
626
627	async createHtmlEditMessage(data: {
628	id: string
629	docId: string
630	role: 'user' \| 'assistant'
631	content: string
632	intent?: string \| null
633	planJson?: string \| null
634	requiresConfirmation?: boolean
635	selectedElement?: {
636	selector: string
637	label?: string
638	elementTag?: string
639	elementText?: string
640	} \| null
641	createdAt: number
642	}): Promise<void> {
643	const selectedElement = data.selectedElement?.selector ? data.selectedElement : null
644	await this.db
645	.insert(schema.htmlEditMessages)
646	.values({
647	id: data.id,
648	docId: data.docId,
649	role: data.role,
650	content: data.content,
651	intent: data.intent ?? null,
652	planJson: data.planJson ?? null,
653	requiresConfirmation: data.requiresConfirmation ? 1 : 0,
654	selectedSelector: selectedElement?.selector.slice(0, 2_000) ?? null,
655	selectedLabel: selectedElement?.label?.slice(0, 500) ?? null,
656	selectedElementTag: selectedElement?.elementTag?.slice(0, 80) ?? null,
657	selectedElementText: selectedElement?.elementText?.slice(0, 2_000) ?? null,
658	createdAt: data.createdAt
659	})
660	.run()
661	}
662
663	async listHtmlEditMessages(docId: string, limit = 100): Promise<HtmlEditMessage[]> {
664	const safeLimit = Math.max(1, Math.min(Math.floor(limit), 500))
665	const rows = await this.db
666	.select()
667	.from(schema.htmlEditMessages)
668	.where(eq(schema.htmlEditMessages.docId, docId))
669	.orderBy(desc(schema.htmlEditMessages.createdAt))
670	.limit(safeLimit)
671	.all()
672	return rows.reverse()
673	}
674
675	async clearHtmlEditMessages(docId: string): Promise<void> {
676	await this.db
677	.delete(schema.htmlEditMessages)
678	.where(eq(schema.htmlEditMessages.docId, docId))
679	.run()
680	}
681
682	async createHtmlEditVersion(data: {
683	id: string
684	docId: string
685	commitSha: string
686	message: string
687	createdAt: number
688	}): Promise<void> {
689	await this.db.insert(schema.htmlEditVersions).values({
690	id: data.id,
691	docId: data.docId,
692	commitSha: data.commitSha,
693	message: data.message,
694	createdAt: data.createdAt
695	})
696	}
697
698	async createHtmlEditDocumentWithVersion(data: {
699	document: {
700	id: string
701	title: string
702	sourcePath?: string \| null
703	htmlPath: string
704	designWidth: number
705	createdAt: number
706	updatedAt: number
707	}
708	version: {
709	id: string
710	commitSha: string
711	message: string
712	createdAt: number
713	}
714	}): Promise<void> {
715	await this.db.transaction(async (tx) => {
716	await tx.insert(schema.htmlEditDocuments).values({
717	id: data.document.id,
718	title: data.document.title,
719	sourcePath: data.document.sourcePath ?? null,
720	htmlPath: data.document.htmlPath,
721	designWidth: data.document.designWidth,
722	createdAt: data.document.createdAt,
723	updatedAt: data.document.updatedAt
724	})
725	await tx.insert(schema.htmlEditVersions).values({
726	id: data.version.id,
727	docId: data.document.id,
728	commitSha: data.version.commitSha,
729	message: data.version.message,
730	createdAt: data.version.createdAt
731	})
732	})
733	}
734
735	async createHtmlEditVersionAndTouch(data: {
736	id: string
737	docId: string
738	commitSha: string
739	message: string
740	createdAt: number
741	}): Promise<void> {
742	await this.db.transaction(async (tx) => {
743	await tx.insert(schema.htmlEditVersions).values({
744	id: data.id,
745	docId: data.docId,
746	commitSha: data.commitSha,
747	message: data.message,
748	createdAt: data.createdAt
749	})
750	await tx
751	.update(schema.htmlEditDocuments)
752	.set({ updatedAt: data.createdAt })
753	.where(eq(schema.htmlEditDocuments.id, data.docId))
754	})
755	}
756
757	async listHtmlEditVersions(docId: string): Promise<HtmlEditVersion[]> {
758	return this.db
759	.select()
760	.from(schema.htmlEditVersions)
761	.where(eq(schema.htmlEditVersions.docId, docId))
762	.orderBy(desc(schema.htmlEditVersions.createdAt))
763	}
764
765	async getHtmlEditVersion(versionId: string): Promise<HtmlEditVersion \| undefined> {
766	const rows = await this.db
767	.select()
768	.from(schema.htmlEditVersions)
769	.where(eq(schema.htmlEditVersions.id, versionId))
770	.limit(1)
771	return rows[0]
772	}
773
774	async listHtmlEditDocuments(): Promise<HtmlEditDocument[]> {
775	return this.db
776	.select()
777	.from(schema.htmlEditDocuments)
778	.orderBy(desc(schema.htmlEditDocuments.updatedAt))
779	}
780
781	async getHtmlEditDocument(docId: string): Promise<HtmlEditDocument \| undefined> {
782	const rows = await this.db
783	.select()
784	.from(schema.htmlEditDocuments)
785	.where(eq(schema.htmlEditDocuments.id, docId))
786	.limit(1)
787	return rows[0]
788	}
789
790	/** 删除文档的数据库记录（含版本行）。不删磁盘文件——文件留存供审计/恢复。 */
791	async deleteHtmlEditDocument(docId: string): Promise<void> {
792	await this.db.delete(schema.htmlEditVersions).where(eq(schema.htmlEditVersions.docId, docId))
793	await this.db.delete(schema.htmlEditDocuments).where(eq(schema.htmlEditDocuments.id, docId))
794	}
795
796	// ========== Session ==========
797
798	async createSession(data: {
799	id?: string
800	title: string
801	topic?: string
802	styleId?: string
803	pageCount?: number
804	slideSizeId?: SlideSizePresetId
805	slideWidth?: number
806	slideHeight?: number
807	referenceDocumentPath?: string \| null
808	visualEnabled?: boolean
809	imageModelConfigId?: string \| null
810	provider: string
811	model: string
812	}): Promise<string> {
813	const id = data.id \|\| crypto.randomUUID()
814	const now = Math.floor(Date.now() / 1000)
815	if (data.visualEnabled && !data.imageModelConfigId?.trim()) {
816	throw new Error('imageModelConfigId is required when visualEnabled is true')
817	}
818
819	const slideSize = requirePersistedSlideSize({
820	id: data.slideSizeId,
821	width: data.slideWidth,
822	height: data.slideHeight
823	})
824
825	await this.db
826	.insert(schema.sessions)
827	.values({
828	id,
829	title: data.title,
830	topic: data.topic \|\| null,
831	styleId: data.styleId \|\| null,
832	pageCount: data.pageCount \|\| null,
833	slideSizeId: slideSize.id,
834	slideWidth: slideSize.width,
835	slideHeight: slideSize.height,
836	referenceDocumentPath: data.referenceDocumentPath \|\| null,
837	visualEnabled: data.visualEnabled ? 1 : 0,
838	imageModelConfigId: data.visualEnabled ? data.imageModelConfigId \|\| null : null,
839	status: 'active',
840	provider: data.provider,
841	model: data.model,
842	createdAt: now,
843	updatedAt: now,
844	metadata: null
845	})
846	.run()
847
848	if (this._stylesCache.length > 0) {
849	await this.createSessionStyleSnapshot(id, data.styleId)
850	}
851
852	return id
853	}
854
855	async getSession(sessionId: string): Promise<Session \| undefined> {
856	const result = await this.db
857	.select()
858	.from(schema.sessions)
859	.where(eq(schema.sessions.id, sessionId))
860	.get()
861	return result as unknown as Session \| undefined
862	}
863
864	async updateSessionHistoryPointer(args: {
865	sessionId: string
866	operationId: string \| null
867	commit: string \| null
868	}): Promise<void> {
869	await this.db
870	.update(schema.sessions)
871	.set({
872	currentOperationId: args.operationId,
873	currentCommit: args.commit,
874	updatedAt: Math.floor(Date.now() / 1000)
875	})
876	.where(eq(schema.sessions.id, args.sessionId))
877	.run()
878	}
879
880	async updateSessionStatus(sessionId: string, status: SessionStatus): Promise<void> {
881	const now = Math.floor(Date.now() / 1000)
882	await this.db
883	.update(schema.sessions)
884	.set({ status, updatedAt: now })
885	.where(eq(schema.sessions.id, sessionId))
886	.run()
887	}
888
889	async updateSessionVisualSettings(data: {
890	sessionId: string
891	visualEnabled: boolean
892	imageModelConfigId?: string \| null
893	}): Promise<void> {
894	if (data.visualEnabled && !data.imageModelConfigId?.trim()) {
895	throw new Error('imageModelConfigId is required when visualEnabled is true')
896	}
897	await this.db
898	.update(schema.sessions)
899	.set({
900	visualEnabled: data.visualEnabled ? 1 : 0,
901	imageModelConfigId: data.visualEnabled ? data.imageModelConfigId!.trim() : null,
902	updatedAt: Math.floor(Date.now() / 1000)
903	})
904	.where(eq(schema.sessions.id, data.sessionId))
905	.run()
906	}
907
908	async updateSessionMetadata(sessionId: string, metadata: object): Promise<void> {
909	await this.db
910	.update(schema.sessions)
911	.set({ metadata: JSON.stringify(metadata), updatedAt: Math.floor(Date.now() / 1000) })
912	.where(eq(schema.sessions.id, sessionId))
913	.run()
914	}
915
916	async updateSessionTitle(sessionId: string, title: string): Promise<void> {
917	const updatedAt = Math.floor(Date.now() / 1000)
918	await this.db
919	.update(schema.sessions)
920	.set({ title, updatedAt })
921	.where(eq(schema.sessions.id, sessionId))
922	.run()
923	await this.db
924	.update(schema.projects)
925	.set({ title, updatedAt })
926	.where(eq(schema.projects.sessionId, sessionId))
927	.run()
928	}
929
930	async updateSessionStyleId(sessionId: string, styleId: string): Promise<void> {
931	const now = Math.floor(Date.now() / 1000)
932	await this.db
933	.update(schema.sessions)
934	.set({ styleId, updatedAt: now })
935	.where(eq(schema.sessions.id, sessionId))
936	.run()
937	if (this._stylesCache.length > 0) {
938	await this.replaceSessionStyleSnapshot(sessionId, styleId)
939	}
940	}
941
942	async restoreSessionStyleState(
943	sessionId: string,
944	styleId: string \| null,
945	snapshot?: SessionStyleSnapshotRow
946	): Promise<void> {
947	const now = Math.floor(Date.now() / 1000)
948	await this.db.transaction(async (tx) => {
949	await tx
950	.update(schema.sessions)
951	.set({ styleId, updatedAt: now })
952	.where(eq(schema.sessions.id, sessionId))
953	.run()
954	await tx
955	.delete(schema.sessionStyleSnapshots)
956	.where(eq(schema.sessionStyleSnapshots.sessionId, sessionId))
957	.run()
958	if (!snapshot) return
959	await tx
960	.insert(schema.sessionStyleSnapshots)
961	.values({
962	id: snapshot.id,
963	sessionId,
964	styleId: snapshot.styleId,
965	styleKey: snapshot.styleKey,
966	styleName: snapshot.styleName,
967	styleNameZh: snapshot.styleNameZh,
968	styleNameEn: snapshot.styleNameEn,
969	description: snapshot.description,
970	category: snapshot.category,
971	aliases: snapshot.aliases,
972	source: snapshot.source,
973	version: snapshot.version,
974	styleCase: snapshot.styleCase,
975	imageGenerationPrompt: snapshot.imageGenerationPrompt \|\| '',
976	packageDir: snapshot.packageDir,
977	styleSkill: snapshot.styleSkill,
978	createdAt: snapshot.createdAt
979	})
980	.run()
981	})
982	}
983
984	async updateSessionDesignContract(sessionId: string, designContract: unknown): Promise<void> {
985	await this.db
986	.update(schema.sessions)
987	.set({
988	designContract: designContract ? JSON.stringify(designContract) : null,
989	updatedAt: Math.floor(Date.now() / 1000)
990	})
991	.where(eq(schema.sessions.id, sessionId))
992	.run()
993	}
994
995	async listSessions(limit = 50, offset = 0): Promise<Session[]> {
996	const results = await this.db
997	.select()
998	.from(schema.sessions)
999	.where(ne(schema.sessions.status, 'archived'))
1000	.orderBy(desc(schema.sessions.updatedAt))
1001	.limit(limit)
1002	.offset(offset)
1003	.all()
1004
1005	return results as unknown as Session[]
1006	}
1007
1008	async listSessionsWithPageCounts(limit = 50, offset = 0): Promise<SessionWithPageCount[]> {
1009	const rows = await this.db
1010	.select({
1011	session: schema.sessions,
1012	pageCount: count(schema.sessionPages.id)
1013	})
1014	.from(schema.sessions)
1015	.leftJoin(
1016	schema.sessionPages,
1017	and(
1018	eq(schema.sessionPages.sessionId, schema.sessions.id),
1019	isNull(schema.sessionPages.deletedAt)
1020	)
1021	)
1022	.where(ne(schema.sessions.status, 'archived'))
1023	.groupBy(schema.sessions.id)
1024	.orderBy(desc(schema.sessions.updatedAt))
1025	.limit(limit)
1026	.offset(offset)
1027	.all()
1028
1029	return rows.map((row) => ({
1030	session: row.session as unknown as Session,
1031	pageCount: Number(row.pageCount \|\| 0)
1032	}))
1033	}
1034
1035	async deleteSession(sessionId: string): Promise<void> {
1036	await this.db.transaction(async (tx) => {
1037	await tx
1038	.delete(schema.sessionOperationPages)
1039	.where(eq(schema.sessionOperationPages.sessionId, sessionId))
1040	.run()
1041	await tx
1042	.delete(schema.sessionOperations)
1043	.where(eq(schema.sessionOperations.sessionId, sessionId))
1044	.run()
1045	await tx
1046	.delete(schema.sourcePageSkeletons)
1047	.where(eq(schema.sourcePageSkeletons.sessionId, sessionId))
1048	.run()
1049	await tx.delete(schema.sessionPages).where(eq(schema.sessionPages.sessionId, sessionId)).run()
1050	await tx
1051	.delete(schema.imageGenerationHistories)
1052	.where(eq(schema.imageGenerationHistories.sessionId, sessionId))
1053	.run()
1054	await tx
1055	.delete(schema.memorySummaries)
1056	.where(eq(schema.memorySummaries.sessionId, sessionId))
1057	.run()
1058	await tx.delete(schema.messages).where(eq(schema.messages.sessionId, sessionId)).run()
1059	await tx
1060	.delete(schema.generationPages)
1061	.where(eq(schema.generationPages.sessionId, sessionId))
1062	.run()
1063	await tx
1064	.delete(schema.generationRuns)
1065	.where(eq(schema.generationRuns.sessionId, sessionId))
1066	.run()
1067	await tx.delete(schema.projects).where(eq(schema.projects.sessionId, sessionId)).run()
1068	await tx.delete(schema.sessions).where(eq(schema.sessions.id, sessionId)).run()
1069	})
1070	}
1071
1072	// ========== Generation Records ==========
1073
1074	private normalizeGenerationRunRow(row: Record<string, unknown>): GenerationRunRecord {
1075	return {
1076	id: String(row.id \|\| ''),
1077	session_id: String(row.sessionId ?? row.session_id ?? ''),
1078	mode: String(row.mode \|\| 'generate') as GenerationRunMode,
1079	status: String(row.status \|\| 'running') as GenerationRunStatus,
1080	total_pages: Number(row.totalPages ?? row.total_pages ?? 0) \|\| 0,
1081	error: typeof row.error === 'string' ? String(row.error) : null,
1082	metadata: typeof row.metadata === 'string' ? String(row.metadata) : null,
1083	animation_preferences:
1084	typeof (row.animationPreferences ?? row.animation_preferences) === 'string'
1085	? String(row.animationPreferences ?? row.animation_preferences)
1086	: null,
1087	model_config_id:
1088	typeof (row.modelConfigId ?? row.model_config_id) === 'string'
1089	? String(row.modelConfigId ?? row.model_config_id)
1090	: null,
1091	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1092	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0
1093	}
1094	}
1095
1096	private normalizeSessionJobRow(row: Record<string, unknown>): SessionJobRecord {
1097	const status = String(row.status \|\| 'pending')
1098	const kind = String(row.kind \|\| 'standard')
1099	const previousSessionStatus = String(
1100	row.previousSessionStatus ?? row.previous_session_status ?? 'active'
1101	)
1102	return {
1103	id: String(row.id \|\| ''),
1104	session_id: String(row.sessionId ?? row.session_id ?? ''),
1105	kind: (kind === 'template' \|\|
1106	kind === 'retry' \|\|
1107	kind === 'add-page' \|\|
1108	kind === 'single-page-retry' \|\|
1109	kind === 'page-edit' \|\|
1110	kind === 'deck-edit' \|\|
1111	kind === 'style-switch'
1112	? kind
1113	: 'standard') as SessionJobKind,
1114	previous_session_status:
1115	previousSessionStatus === 'completed' \|\|
1116	previousSessionStatus === 'failed' \|\|
1117	previousSessionStatus === 'archived'
1118	? previousSessionStatus
1119	: 'active',
1120	target_page_id:
1121	typeof (row.targetPageId ?? row.target_page_id) === 'string' &&
1122	String(row.targetPageId ?? row.target_page_id).trim().length > 0
1123	? String(row.targetPageId ?? row.target_page_id)
1124	: null,
1125	target_page_number:
1126	typeof (row.targetPageNumber ?? row.target_page_number) === 'number'
1127	? Number(row.targetPageNumber ?? row.target_page_number)
1128	: null,
1129	selector:
1130	typeof row.selector === 'string' && row.selector.trim().length > 0 ? row.selector : null,
1131	total_pages:
1132	typeof (row.totalPages ?? row.total_pages) === 'number'
1133	? Math.max(1, Number(row.totalPages ?? row.total_pages) \|\| 1)
1134	: null,
1135	status: (status === 'active' \|\| status === 'finished' \|\| status === 'aborted'
1136	? status
1137	: 'pending') as SessionJobStatus,
1138	abort_reason:
1139	typeof (row.abortReason ?? row.abort_reason) === 'string'
1140	? String(row.abortReason ?? row.abort_reason)
1141	: null,
1142	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1143	activated_at:
1144	typeof (row.activatedAt ?? row.activated_at) === 'number'
1145	? Number(row.activatedAt ?? row.activated_at)
1146	: null,
1147	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0,
1148	finished_at:
1149	typeof (row.finishedAt ?? row.finished_at) === 'number'
1150	? Number(row.finishedAt ?? row.finished_at)
1151	: null
1152	}
1153	}
1154
1155	private normalizeGenerationPageRow(row: Record<string, unknown>): GenerationPageRecord {
1156	return {
1157	id: String(row.id \|\| ''),
1158	run_id: String(row.runId ?? row.run_id ?? ''),
1159	session_id: String(row.sessionId ?? row.session_id ?? ''),
1160	page_id: String(row.pageId ?? row.page_id ?? ''),
1161	page_number: Number(row.pageNumber ?? row.page_number ?? 0) \|\| 0,
1162	title: String(row.title \|\| ''),
1163	content_outline:
1164	typeof (row.contentOutline ?? row.content_outline) === 'string'
1165	? String(row.contentOutline ?? row.content_outline)
1166	: null,
1167	layout_intent:
1168	typeof (row.layoutIntent ?? row.layout_intent) === 'string'
1169	? String(row.layoutIntent ?? row.layout_intent)
1170	: null,
1171	layout_id:
1172	typeof (row.layoutId ?? row.layout_id) === 'string'
1173	? String(row.layoutId ?? row.layout_id)
1174	: null,
1175	layout_contract_version:
1176	typeof (row.layoutContractVersion ?? row.layout_contract_version) === 'number'
1177	? Number(row.layoutContractVersion ?? row.layout_contract_version)
1178	: null,
1179	html_path:
1180	typeof (row.htmlPath ?? row.html_path) === 'string'
1181	? String(row.htmlPath ?? row.html_path)
1182	: null,
1183	status: String(row.status \|\| 'pending') as GenerationPageStatus,
1184	error: typeof row.error === 'string' ? String(row.error) : null,
1185	retry_count: Number(row.retryCount ?? row.retry_count ?? 0) \|\| 0,
1186	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1187	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0
1188	}
1189	}
1190
1191	private normalizeSessionPageRow(row: Record<string, unknown>): SessionPageRecord {
1192	return {
1193	id: String(row.id \|\| ''),
1194	session_id: String(row.sessionId ?? row.session_id ?? ''),
1195	legacy_page_id:
1196	typeof (row.legacyPageId ?? row.legacy_page_id) === 'string'
1197	? String(row.legacyPageId ?? row.legacy_page_id)
1198	: null,
1199	file_slug: String(row.fileSlug ?? row.file_slug ?? ''),
1200	page_number: Number(row.pageNumber ?? row.page_number ?? 0) \|\| 0,
1201	title: String(row.title \|\| ''),
1202	html_path: String(row.htmlPath ?? row.html_path ?? ''),
1203	layout_intent:
1204	typeof (row.layoutIntent ?? row.layout_intent) === 'string'
1205	? String(row.layoutIntent ?? row.layout_intent)
1206	: null,
1207	layout_id:
1208	typeof (row.layoutId ?? row.layout_id) === 'string'
1209	? String(row.layoutId ?? row.layout_id)
1210	: null,
1211	layout_contract_version:
1212	typeof (row.layoutContractVersion ?? row.layout_contract_version) === 'number'
1213	? Number(row.layoutContractVersion ?? row.layout_contract_version)
1214	: null,
1215	status: String(row.status \|\| 'pending') as SessionPageStatus,
1216	error: typeof row.error === 'string' ? row.error : null,
1217	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1218	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0,
1219	deleted_at:
1220	typeof (row.deletedAt ?? row.deleted_at) === 'number'
1221	? Number(row.deletedAt ?? row.deleted_at)
1222	: null
1223	}
1224	}
1225
1226	private normalizeImageFulfillmentJobRow(row: Record<string, unknown>): ImageFulfillmentJobRecord {
1227	const status = String(row.status \|\| 'pending')
1228	return {
1229	id: String(row.id \|\| ''),
1230	run_id: String(row.runId ?? row.run_id ?? ''),
1231	session_id: String(row.sessionId ?? row.session_id ?? ''),
1232	session_page_id: String(row.sessionPageId ?? row.session_page_id ?? ''),
1233	page_id: String(row.pageId ?? row.page_id ?? ''),
1234	layout_id:
1235	typeof (row.layoutId ?? row.layout_id) === 'string'
1236	? String(row.layoutId ?? row.layout_id)
1237	: null,
1238	layout_contract_version:
1239	typeof (row.layoutContractVersion ?? row.layout_contract_version) === 'number'
1240	? Number(row.layoutContractVersion ?? row.layout_contract_version)
1241	: null,
1242	image_model_config_id:
1243	typeof (row.imageModelConfigId ?? row.image_model_config_id) === 'string'
1244	? String(row.imageModelConfigId ?? row.image_model_config_id)
1245	: null,
1246	image_provider:
1247	typeof (row.imageProvider ?? row.image_provider) === 'string'
1248	? String(row.imageProvider ?? row.image_provider)
1249	: null,
1250	image_model:
1251	typeof (row.imageModel ?? row.image_model) === 'string'
1252	? String(row.imageModel ?? row.image_model)
1253	: null,
1254	attempt: Number(row.attempt \|\| 1) \|\| 1,
1255	retry_of_job_id:
1256	typeof (row.retryOfJobId ?? row.retry_of_job_id) === 'string'
1257	? String(row.retryOfJobId ?? row.retry_of_job_id)
1258	: null,
1259	idempotency_key:
1260	typeof (row.idempotencyKey ?? row.idempotency_key) === 'string'
1261	? String(row.idempotencyKey ?? row.idempotency_key)
1262	: null,
1263	status: (status === 'running' \|\|
1264	status === 'finalizing' \|\|
1265	status === 'completed' \|\|
1266	status === 'degraded' \|\|
1267	status === 'failed' \|\|
1268	status === 'cancelled'
1269	? status
1270	: 'pending') as ImageFulfillmentJobStatus,
1271	error: typeof row.error === 'string' ? row.error : null,
1272	cancel_requested_at:
1273	typeof (row.cancelRequestedAt ?? row.cancel_requested_at) === 'number'
1274	? Number(row.cancelRequestedAt ?? row.cancel_requested_at)
1275	: null,
1276	lease_owner:
1277	typeof (row.leaseOwner ?? row.lease_owner) === 'string'
1278	? String(row.leaseOwner ?? row.lease_owner)
1279	: null,
1280	lease_expires_at:
1281	typeof (row.leaseExpiresAt ?? row.lease_expires_at) === 'number'
1282	? Number(row.leaseExpiresAt ?? row.lease_expires_at)
1283	: null,
1284	finalization_manifest_path:
1285	typeof (row.finalizationManifestPath ?? row.finalization_manifest_path) === 'string'
1286	? String(row.finalizationManifestPath ?? row.finalization_manifest_path)
1287	: null,
1288	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1289	started_at:
1290	typeof (row.startedAt ?? row.started_at) === 'number'
1291	? Number(row.startedAt ?? row.started_at)
1292	: null,
1293	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0,
1294	finished_at:
1295	typeof (row.finishedAt ?? row.finished_at) === 'number'
1296	? Number(row.finishedAt ?? row.finished_at)
1297	: null
1298	}
1299	}
1300
1301	private normalizeImageFulfillmentIntentRow(
1302	row: Record<string, unknown>
1303	): ImageFulfillmentIntentRecord {
1304	const status = String(row.status \|\| 'pending')
1305	return {
1306	id: String(row.id \|\| ''),
1307	job_id: String(row.jobId ?? row.job_id ?? ''),
1308	slot_id: String(row.slotId ?? row.slot_id ?? ''),
1309	layout_slot_id: String(row.layoutSlotId ?? row.layout_slot_id ?? ''),
1310	role: String(row.role \|\| ''),
1311	layer: String(row.layer \|\| ''),
1312	request_version: Number(row.requestVersion ?? row.request_version ?? 1) \|\| 1,
1313	size_hint:
1314	typeof (row.sizeHint ?? row.size_hint) === 'string'
1315	? String(row.sizeHint ?? row.size_hint)
1316	: null,
1317	subject: String(row.subject \|\| ''),
1318	text_zone:
1319	typeof (row.textZone ?? row.text_zone) === 'string'
1320	? String(row.textZone ?? row.text_zone)
1321	: null,
1322	subject_zone:
1323	typeof (row.subjectZone ?? row.subject_zone) === 'string'
1324	? String(row.subjectZone ?? row.subject_zone)
1325	: null,
1326	negative_space:
1327	typeof (row.negativeSpace ?? row.negative_space) === 'string'
1328	? String(row.negativeSpace ?? row.negative_space)
1329	: null,
1330	avoid_json:
1331	typeof (row.avoidJson ?? row.avoid_json) === 'string'
1332	? String(row.avoidJson ?? row.avoid_json)
1333	: null,
1334	request_json: String(row.requestJson ?? row.request_json ?? '{}'),
1335	image_history_id:
1336	typeof (row.imageHistoryId ?? row.image_history_id) === 'string'
1337	? String(row.imageHistoryId ?? row.image_history_id)
1338	: null,
1339	asset_path:
1340	typeof (row.assetPath ?? row.asset_path) === 'string'
1341	? String(row.assetPath ?? row.asset_path)
1342	: null,
1343	width: typeof row.width === 'number' ? Number(row.width) : null,
1344	height: typeof row.height === 'number' ? Number(row.height) : null,
1345	mime_type:
1346	typeof (row.mimeType ?? row.mime_type) === 'string'
1347	? String(row.mimeType ?? row.mime_type)
1348	: null,
1349	attempt: Number(row.attempt \|\| 1) \|\| 1,
1350	retry_of_intent_id:
1351	typeof (row.retryOfIntentId ?? row.retry_of_intent_id) === 'string'
1352	? String(row.retryOfIntentId ?? row.retry_of_intent_id)
1353	: null,
1354	status: (status === 'generating' \|\|
1355	status === 'generated' \|\|
1356	status === 'used' \|\|
1357	status === 'fallback' \|\|
1358	status === 'layout_failed' \|\|
1359	status === 'failed' \|\|
1360	status === 'cancelled'
1361	? status
1362	: 'pending') as ImageFulfillmentIntentStatus,
1363	error: typeof row.error === 'string' ? row.error : null,
1364	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1365	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0
1366	}
1367	}
1368
1369	private normalizeSourcePageSkeletonRow(row: Record<string, unknown>): SourcePageSkeletonRecord {
1370	return {
1371	id: String(row.id \|\| ''),
1372	session_id: String(row.sessionId ?? row.session_id ?? ''),
1373	page_number: Number(row.pageNumber ?? row.page_number ?? 0) \|\| 0,
1374	title: String(row.title \|\| ''),
1375	role: String(row.role \|\| 'content') === 'chapter-divider' ? 'chapter-divider' : 'content',
1376	source_document_path: String(row.sourceDocumentPath ?? row.source_document_path ?? ''),
1377	source_document_name:
1378	typeof (row.sourceDocumentName ?? row.source_document_name) === 'string'
1379	? String(row.sourceDocumentName ?? row.source_document_name)
1380	: null,
1381	source_heading: String(row.sourceHeading ?? row.source_heading ?? ''),
1382	heading_level: Number(row.headingLevel ?? row.heading_level ?? 0) \|\| 1,
1383	line_start: Number(row.lineStart ?? row.line_start ?? 0) \|\| 1,
1384	line_end: Number(row.lineEnd ?? row.line_end ?? 0) \|\| 1,
1385	agenda_items_json:
1386	typeof (row.agendaItemsJson ?? row.agenda_items_json) === 'string' &&
1387	String(row.agendaItemsJson ?? row.agenda_items_json).trim().length > 0
1388	? String(row.agendaItemsJson ?? row.agenda_items_json)
1389	: null,
1390	reason:
1391	typeof row.reason === 'string' && row.reason.trim().length > 0 ? String(row.reason) : null,
1392	confidence: row.confidence === 'medium' \|\| row.confidence === 'low' ? row.confidence : 'high',
1393	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
1394	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0
1395	}
1396	}
1397
1398	async createGenerationRun(data: GenerationRunCreateData): Promise<string> {
1399	const id = data.id \|\| crypto.randomUUID()
1400	const now = Math.floor(Date.now() / 1000)
1401	const animationPreferences = data.animationPreferences
1402	? JSON.stringify(data.animationPreferences)
1403	: null
1404	await this.db
1405	.insert(schema.generationRuns)
1406	.values({
1407	id,
1408	sessionId: data.sessionId,
1409	mode: data.mode,
1410	status: 'running',
1411	totalPages: Math.max(0, Math.floor(data.totalPages \|\| 0)),
1412	error: null,
1413	metadata: data.metadata ? JSON.stringify(data.metadata) : null,
1414	animationPreferences,
1415	modelConfigId:
1416	typeof data.modelConfigId === 'string' && data.modelConfigId.trim().length > 0
1417	? data.modelConfigId.trim()
1418	: null,
1419	createdAt: now,
1420	updatedAt: now
1421	})
1422	.onConflictDoUpdate({
1423	target: schema.generationRuns.id,
1424	set: {
1425	sessionId: data.sessionId,
1426	mode: data.mode,
1427	status: 'running',
1428	totalPages: Math.max(0, Math.floor(data.totalPages \|\| 0)),
1429	error: null,
1430	metadata: data.metadata ? JSON.stringify(data.metadata) : null,
1431	animationPreferences,
1432	modelConfigId:
1433	typeof data.modelConfigId === 'string' && data.modelConfigId.trim().length > 0
1434	? data.modelConfigId.trim()
1435	: null,
1436	updatedAt: now
1437	}
1438	})
1439	.run()
1440	return id
1441	}
1442
1443	async createGenerationRunWithSessionJob(data: {
1444	run: GenerationRunCreateData & { id: string }
1445	job: SessionJobCreateData
1446	}): Promise<void> {
1447	await this.createGenerationRunWithSessionJobAndPages({ ...data, pages: [] })
1448	}
1449
1450	async createGenerationRunWithSessionJobAndPages(data: {
1451	run: GenerationRunCreateData & { id: string }
1452	job: SessionJobCreateData
1453	pages: GenerationPageCreateData[]
1454	}): Promise<void> {
1455	if (data.run.id !== data.job.id) {
1456	throw new Error('generation run and session job must share the same id')
1457	}
1458	if (data.run.sessionId !== data.job.sessionId) {
1459	throw new Error('generation run and session job must belong to the same session')
1460	}
1461
1462	const now = Math.floor(Date.now() / 1000)
1463	const animationPreferences = data.run.animationPreferences
1464	? JSON.stringify(data.run.animationPreferences)
1465	: null
1466	const runTotalPages = Math.max(0, Math.floor(data.run.totalPages \|\| 0))
1467	const modelConfigId =
1468	typeof data.run.modelConfigId === 'string' && data.run.modelConfigId.trim().length > 0
1469	? data.run.modelConfigId.trim()
1470	: null
1471	const jobTotalPages =
1472	typeof data.job.totalPages === 'number' && Number.isFinite(data.job.totalPages)
1473	? Math.max(1, Math.floor(data.job.totalPages))
1474	: null
1475
1476	await this.db.transaction(async (tx) => {
1477	await tx.insert(schema.generationRuns).values({
1478	id: data.run.id,
1479	sessionId: data.run.sessionId,
1480	mode: data.run.mode,
1481	status: 'running',
1482	totalPages: runTotalPages,
1483	error: null,
1484	metadata: data.run.metadata ? JSON.stringify(data.run.metadata) : null,
1485	animationPreferences,
1486	modelConfigId,
1487	createdAt: now,
1488	updatedAt: now
1489	})
1490	await tx.insert(schema.sessionJobs).values({
1491	id: data.job.id,
1492	sessionId: data.job.sessionId,
1493	kind: data.job.kind,
1494	previousSessionStatus: data.job.previousSessionStatus,
1495	targetPageId: data.job.targetPageId \|\| null,
1496	targetPageNumber: data.job.targetPageNumber ?? null,
1497	selector: data.job.selector \|\| null,
1498	totalPages: jobTotalPages,
1499	status: data.job.status,
1500	abortReason: null,
1501	createdAt: now,
1502	activatedAt: data.job.status === 'active' ? now : null,
1503	updatedAt: now,
1504	finishedAt: null
1505	})
1506
1507	if (data.pages.length === 0) return
1508	await tx.insert(schema.generationPages).values(
1509	data.pages.map((page) => ({
1510	id: `${data.run.id}:${page.pageId}`,
1511	runId: data.run.id,
1512	sessionId: data.run.sessionId,
1513	pageId: page.pageId,
1514	pageNumber: Math.max(1, Math.floor(page.pageNumber)),
1515	title: page.title,
1516	contentOutline: page.contentOutline \|\| null,
1517	layoutIntent: page.layoutIntent \|\| null,
1518	layoutId: page.layoutId \|\| null,
1519	layoutContractVersion: page.layoutContractVersion \|\| null,
1520	htmlPath: page.htmlPath \|\| null,
1521	status: page.status \|\| 'pending',
1522	error: page.error \|\| null,
1523	retryCount: Math.max(0, Math.floor(page.retryCount \|\| 0)),
1524	createdAt: now,
1525	updatedAt: now
1526	}))
1527	)
1528	})
1529	}
1530
1531	async updateSessionJobStatus(
1532	jobId: string,
1533	status: SessionJobStatus,
1534	options?: { abortReason?: string \| null }
1535	): Promise<void> {
1536	const now = Math.floor(Date.now() / 1000)
1537	const set: Record<string, unknown> = {
1538	status,
1539	updatedAt: now
1540	}
1541	if (status === 'active') {
1542	set.activatedAt = now
1543	set.finishedAt = null
1544	set.abortReason = null
1545	}
1546	if (status === 'finished') {
1547	set.finishedAt = now
1548	set.abortReason = null
1549	}
1550	if (status === 'aborted') {
1551	set.finishedAt = now
1552	set.abortReason = options?.abortReason \|\| null
1553	}
1554	await this.db.update(schema.sessionJobs).set(set).where(eq(schema.sessionJobs.id, jobId)).run()
1555	}
1556
1557	async getSessionJob(jobId: string): Promise<SessionJobRecord \| undefined> {
1558	const row = await this.db
1559	.select()
1560	.from(schema.sessionJobs)
1561	.where(eq(schema.sessionJobs.id, jobId))
1562	.get()
1563	return row ? this.normalizeSessionJobRow(row as Record<string, unknown>) : undefined
1564	}
1565
1566	async getLatestSessionJob(
1567	sessionId: string,
1568	kinds?: readonly SessionJobKind[]
1569	): Promise<SessionJobRecord \| undefined> {
1570	const where =
1571	kinds && kinds.length > 0
1572	? and(
1573	eq(schema.sessionJobs.sessionId, sessionId),
1574	inArray(schema.sessionJobs.kind, [...kinds])
1575	)
1576	: eq(schema.sessionJobs.sessionId, sessionId)
1577	const row = await this.db
1578	.select()
1579	.from(schema.sessionJobs)
1580	.where(where)
1581	.orderBy(desc(schema.sessionJobs.updatedAt), desc(schema.sessionJobs.createdAt))
1582	.limit(1)
1583	.get()
1584	return row ? this.normalizeSessionJobRow(row as Record<string, unknown>) : undefined
1585	}
1586
1587	async listActiveSessionJobs(kinds?: readonly SessionJobKind[]): Promise<SessionJobRecord[]> {
1588	const where =
1589	kinds && kinds.length > 0
1590	? and(
1591	inArray(schema.sessionJobs.status, ['pending', 'active']),
1592	inArray(schema.sessionJobs.kind, [...kinds])
1593	)
1594	: inArray(schema.sessionJobs.status, ['pending', 'active'])
1595	const rows = await this.db
1596	.select()
1597	.from(schema.sessionJobs)
1598	.where(where)
1599	.orderBy(asc(schema.sessionJobs.createdAt))
1600	.all()
1601	return rows.map((row) => this.normalizeSessionJobRow(row as Record<string, unknown>))
1602	}
1603
1604	async updateGenerationRunStatus(
1605	runId: string,
1606	status: GenerationRunStatus,
1607	error?: string \| null
1608	): Promise<void> {
1609	await this.db
1610	.update(schema.generationRuns)
1611	.set({
1612	status,
1613	error: error \|\| null,
1614	updatedAt: Math.floor(Date.now() / 1000)
1615	})
1616	.where(eq(schema.generationRuns.id, runId))
1617	.run()
1618	}
1619
1620	async updateGenerationRunMetadata(runId: string, metadata: unknown): Promise<void> {
1621	await this.db
1622	.update(schema.generationRuns)
1623	.set({
1624	metadata: metadata ? JSON.stringify(metadata) : null,
1625	updatedAt: Math.floor(Date.now() / 1000)
1626	})
1627	.where(eq(schema.generationRuns.id, runId))
1628	.run()
1629	}
1630
1631	async getGenerationRun(runId: string): Promise<GenerationRunRecord \| undefined> {
1632	const row = await this.db
1633	.select()
1634	.from(schema.generationRuns)
1635	.where(eq(schema.generationRuns.id, runId))
1636	.get()
1637	return row ? this.normalizeGenerationRunRow(row as Record<string, unknown>) : undefined
1638	}
1639
1640	async getLatestGenerationRun(sessionId: string): Promise<GenerationRunRecord \| undefined> {
1641	const row = await this.db
1642	.select()
1643	.from(schema.generationRuns)
1644	.where(eq(schema.generationRuns.sessionId, sessionId))
1645	.orderBy(desc(schema.generationRuns.createdAt))
1646	.limit(1)
1647	.get()
1648	return row ? this.normalizeGenerationRunRow(row as Record<string, unknown>) : undefined
1649	}
1650
1651	async upsertGenerationPage(data: {
1652	runId: string
1653	sessionId: string
1654	pageId: string
1655	pageNumber: number
1656	title: string
1657	contentOutline?: string \| null
1658	layoutIntent?: string \| null
1659	layoutId?: string \| null
1660	layoutContractVersion?: number \| null
1661	htmlPath?: string \| null
1662	status: GenerationPageStatus
1663	error?: string \| null
1664	retryCount?: number
1665	}): Promise<void> {
1666	const now = Math.floor(Date.now() / 1000)
1667	const id = `${data.runId}:${data.pageId}`
1668	const values = {
1669	id,
1670	runId: data.runId,
1671	sessionId: data.sessionId,
1672	pageId: data.pageId,
1673	pageNumber: data.pageNumber,
1674	title: data.title,
1675	contentOutline: data.contentOutline \|\| null,
1676	layoutIntent: data.layoutIntent \|\| null,
1677	layoutId: data.layoutId \|\| null,
1678	layoutContractVersion: data.layoutContractVersion \|\| null,
1679	htmlPath: data.htmlPath \|\| null,
1680	status: data.status,
1681	error: data.error \|\| null,
1682	retryCount: Math.max(0, Math.floor(data.retryCount \|\| 0)),
1683	createdAt: now,
1684	updatedAt: now
1685	}
1686	await this.db
1687	.insert(schema.generationPages)
1688	.values(values)
1689	.onConflictDoUpdate({
1690	target: schema.generationPages.id,
1691	set: {
1692	pageNumber: values.pageNumber,
1693	title: values.title,
1694	contentOutline: values.contentOutline,
1695	layoutIntent: values.layoutIntent,
1696	layoutId: values.layoutId,
1697	layoutContractVersion: values.layoutContractVersion,
1698	htmlPath: values.htmlPath,
1699	status: values.status,
1700	error: values.error,
1701	retryCount: values.retryCount,
1702	updatedAt: now
1703	}
1704	})
1705	.run()
1706	}
1707
1708	async listGenerationPages(runId: string): Promise<GenerationPageRecord[]> {
1709	const rows = await this.db
1710	.select()
1711	.from(schema.generationPages)
1712	.where(eq(schema.generationPages.runId, runId))
1713	.orderBy(asc(schema.generationPages.pageNumber))
1714	.all()
1715	return rows.map((row) => this.normalizeGenerationPageRow(row as Record<string, unknown>))
1716	}
1717
1718	async listLatestFailedGenerationPages(sessionId: string): Promise<GenerationPageRecord[]> {
1719	const run = await this.getLatestGenerationRun(sessionId)
1720	if (!run) return []
1721	return (await this.listGenerationPages(run.id)).filter((page) => page.status === 'failed')
1722	}
1723
1724	async listLatestGenerationPageSnapshot(sessionId: string): Promise<GenerationPageRecord[]> {
1725	const rows = await this.db
1726	.select()
1727	.from(schema.generationPages)
1728	.where(eq(schema.generationPages.sessionId, sessionId))
1729	.orderBy(desc(schema.generationPages.updatedAt), desc(schema.generationPages.createdAt))
1730	.all()
1731	const latestByPageId = new Map<string, GenerationPageRecord>()
1732	for (const row of rows) {
1733	const page = this.normalizeGenerationPageRow(row as Record<string, unknown>)
1734	if (!page.page_id \|\| latestByPageId.has(page.page_id)) continue
1735	latestByPageId.set(page.page_id, page)
1736	}
1737	return Array.from(latestByPageId.values()).sort((a, b) => a.page_number - b.page_number)
1738	}
1739
1740	async listSessionPages(
1741	sessionId: string,
1742	options?: { includeDeleted?: boolean }
1743	): Promise<SessionPageRecord[]> {
1744	const conditions = [eq(schema.sessionPages.sessionId, sessionId)]
1745	if (!options?.includeDeleted) {
1746	conditions.push(isNull(schema.sessionPages.deletedAt))
1747	}
1748	const rows = await this.db
1749	.select()
1750	.from(schema.sessionPages)
1751	.where(and(...conditions))
1752	.orderBy(asc(schema.sessionPages.pageNumber))
1753	.all()
1754	return rows.map((row) => this.normalizeSessionPageRow(row as Record<string, unknown>))
1755	}
1756
1757	async replaceSourcePageSkeletons(args: {
1758	sessionId: string
1759	sourceDocumentPath: string
1760	sourceDocumentName?: string \| null
1761	confidence?: SourcePageSkeletonConfidence
1762	items: Array<{
1763	pageNumber: number
1764	title: string
1765	role: SourcePageSkeletonRole
1766	sourceHeading: string
1767	headingLevel: number
1768	lineStart: number
1769	lineEnd: number
1770	agendaItems?: Array<{
1771	title: string
1772	lineStart: number
1773	}>
1774	reason?: string \| null
1775	}>
1776	}): Promise<void> {
1777	const now = Math.floor(Date.now() / 1000)
1778	await this.db
1779	.delete(schema.sourcePageSkeletons)
1780	.where(eq(schema.sourcePageSkeletons.sessionId, args.sessionId))
1781	.run()
1782	const values = args.items
1783	.filter((item) => item.sourceHeading.trim().length > 0)
1784	.map((item) => {
1785	const pageNumber = Math.max(1, Math.floor(item.pageNumber))
1786	const lineStart = Math.max(1, Math.floor(item.lineStart \|\| 1))
1787	const lineEnd = Math.max(lineStart, Math.floor(item.lineEnd \|\| lineStart))
1788	return {
1789	id: `${args.sessionId}:${pageNumber}`,
1790	sessionId: args.sessionId,
1791	pageNumber,
1792	title: item.title.trim() \|\| `Slide ${pageNumber}`,
1793	role: item.role === 'chapter-divider' ? 'chapter-divider' : 'content',
1794	sourceDocumentPath: args.sourceDocumentPath,
1795	sourceDocumentName: args.sourceDocumentName \|\| null,
1796	sourceHeading: item.sourceHeading,
1797	headingLevel: Math.max(1, Math.floor(item.headingLevel \|\| 1)),
1798	lineStart,
1799	lineEnd,
1800	agendaItemsJson: serializeSourcePageSkeletonAgendaItems(item.agendaItems),
1801	reason: item.reason \|\| null,
1802	confidence: args.confidence \|\| 'high',
1803	createdAt: now,
1804	updatedAt: now
1805	}
1806	})
1807	if (values.length === 0) return
1808	await this.db.insert(schema.sourcePageSkeletons).values(values).run()
1809	}
1810
1811	async upsertSourcePageSkeleton(args: {
1812	sessionId: string
1813	pageNumber: number
1814	title: string
1815	role?: SourcePageSkeletonRole
1816	sourceDocumentPath: string
1817	sourceDocumentName?: string \| null
1818	sourceHeading: string
1819	headingLevel?: number
1820	lineStart?: number
1821	lineEnd?: number
1822	agendaItems?: Array<{
1823	title: string
1824	lineStart: number
1825	}>
1826	reason?: string \| null
1827	confidence?: SourcePageSkeletonConfidence
1828	}): Promise<void> {
1829	const now = Math.floor(Date.now() / 1000)
1830	const pageNumber = Math.max(1, Math.floor(args.pageNumber))
1831	const lineStart = Math.max(1, Math.floor(args.lineStart \|\| pageNumber))
1832	const lineEnd = Math.max(lineStart, Math.floor(args.lineEnd \|\| lineStart))
1833	const value = {
1834	id: `${args.sessionId}:${pageNumber}`,
1835	sessionId: args.sessionId,
1836	pageNumber,
1837	title: args.title.trim() \|\| `Slide ${pageNumber}`,
1838	role: args.role === 'chapter-divider' ? 'chapter-divider' : 'content',
1839	sourceDocumentPath: args.sourceDocumentPath,
1840	sourceDocumentName: args.sourceDocumentName \|\| null,
1841	sourceHeading: args.sourceHeading.trim(),
1842	headingLevel: Math.max(1, Math.floor(args.headingLevel \|\| 1)),
1843	lineStart,
1844	lineEnd,
1845	agendaItemsJson: serializeSourcePageSkeletonAgendaItems(args.agendaItems),
1846	reason: args.reason \|\| null,
1847	confidence: args.confidence \|\| 'medium',
1848	createdAt: now,
1849	updatedAt: now
1850	}
1851	if (!value.sourceHeading) return
1852	await this.db
1853	.insert(schema.sourcePageSkeletons)
1854	.values(value)
1855	.onConflictDoUpdate({
1856	target: schema.sourcePageSkeletons.id,
1857	set: {
1858	title: value.title,
1859	role: value.role,
1860	sourceDocumentPath: value.sourceDocumentPath,
1861	sourceDocumentName: value.sourceDocumentName,
1862	sourceHeading: value.sourceHeading,
1863	headingLevel: value.headingLevel,
1864	lineStart: value.lineStart,
1865	lineEnd: value.lineEnd,
1866	agendaItemsJson: value.agendaItemsJson,
1867	reason: value.reason,
1868	confidence: value.confidence,
1869	updatedAt: now
1870	}
1871	})
1872	.run()
1873	}
1874
1875	async deleteSourcePageSkeleton(sessionId: string, pageNumber: number): Promise<void> {
1876	await this.db
1877	.delete(schema.sourcePageSkeletons)
1878	.where(
1879	and(
1880	eq(schema.sourcePageSkeletons.sessionId, sessionId),
1881	eq(schema.sourcePageSkeletons.pageNumber, pageNumber)
1882	)
1883	)
1884	.run()
1885	}
1886
1887	async deleteSourcePageSkeletons(sessionId: string, pageNumbers: number[]): Promise<void> {
1888	if (!Array.isArray(pageNumbers) \|\| pageNumbers.length === 0) return
1889	await this.db
1890	.delete(schema.sourcePageSkeletons)
1891	.where(
1892	and(
1893	eq(schema.sourcePageSkeletons.sessionId, sessionId),
1894	inArray(schema.sourcePageSkeletons.pageNumber, pageNumbers)
1895	)
1896	)
1897	.run()
1898	}
1899
1900	async listSourcePageSkeletons(sessionId: string): Promise<SourcePageSkeletonRecord[]> {
1901	const rows = await this.db
1902	.select()
1903	.from(schema.sourcePageSkeletons)
1904	.where(eq(schema.sourcePageSkeletons.sessionId, sessionId))
1905	.orderBy(asc(schema.sourcePageSkeletons.pageNumber))
1906	.all()
1907	return rows.map((row) => this.normalizeSourcePageSkeletonRow(row as Record<string, unknown>))
1908	}
1909
1910	async upsertSessionPage(page: SessionPageInput): Promise<void> {
1911	const now = Math.floor(Date.now() / 1000)
1912	await this.db
1913	.insert(schema.sessionPages)
1914	.values({
1915	id: page.id,
1916	sessionId: page.sessionId,
1917	legacyPageId: page.legacyPageId \|\| null,
1918	fileSlug: page.fileSlug,
1919	pageNumber: page.pageNumber,
1920	title: page.title,
1921	htmlPath: page.htmlPath,
1922	layoutIntent: page.layoutIntent \|\| null,
1923	layoutId: page.layoutId \|\| null,
1924	layoutContractVersion: page.layoutContractVersion \|\| null,
1925	status: page.status \|\| 'pending',
1926	error: page.error \|\| null,
1927	createdAt: now,
1928	updatedAt: now,
1929	deletedAt: null
1930	})
1931	.onConflictDoUpdate({
1932	target: schema.sessionPages.id,
1933	set: {
1934	legacyPageId: page.legacyPageId \|\| null,
1935	fileSlug: page.fileSlug,
1936	pageNumber: page.pageNumber,
1937	title: page.title,
1938	htmlPath: page.htmlPath,
1939	// Omitted layout fields come from callers that only update status/order.
1940	// Preserve their stable source; explicit null is still the intentional clear path.
1941	layoutIntent:
1942	page.layoutIntent === undefined ? schema.sessionPages.layoutIntent : page.layoutIntent,
1943	layoutId: page.layoutId === undefined ? schema.sessionPages.layoutId : page.layoutId,
1944	layoutContractVersion:
1945	page.layoutContractVersion === undefined
1946	? schema.sessionPages.layoutContractVersion
1947	: page.layoutContractVersion,
1948	status: page.status \|\| 'pending',
1949	error: page.error \|\| null,
1950	deletedAt: null,
1951	updatedAt: now
1952	}
1953	})
1954	.run()
1955	}
1956
1957	async replaceSessionPageOrder(
1958	sessionId: string,
1959	pages: Array<{ id: string; pageNumber: number }>
1960	): Promise<void> {
1961	if (pages.length === 0) return
1962	const now = Math.floor(Date.now() / 1000)
1963	const pageIds = pages.map((page) => page.id)
1964	const caseWhenFragments = pages.map(
1965	(page) => sql`WHEN ${schema.sessionPages.id} = ${page.id} THEN ${page.pageNumber}`
1966	)
1967	const pageNumberExpr = sql<number>`CASE ${sql.join(caseWhenFragments, sql` `)} ELSE ${schema.sessionPages.pageNumber} END`
1968	await this.db
1969	.update(schema.sessionPages)
1970	.set({
1971	pageNumber: pageNumberExpr,
1972	updatedAt: now
1973	})
1974	.where(
1975	and(eq(schema.sessionPages.sessionId, sessionId), inArray(schema.sessionPages.id, pageIds))
1976	)
1977	.run()
1978	}
1979
1980	async persistSessionPageState(data: {
1981	sessionId: string
1982	pages: Array<{ id: string; pageNumber: number }>
1983	deletedPageIds?: string[]
1984	metadata: object
1985	}): Promise<void> {
1986	const now = Math.floor(Date.now() / 1000)
1987	await this.db.transaction(async (tx) => {
1988	if (data.deletedPageIds?.length) {
1989	await tx
1990	.update(schema.sessionPages)
1991	.set({ deletedAt: now, updatedAt: now })
1992	.where(
1993	and(
1994	eq(schema.sessionPages.sessionId, data.sessionId),
1995	inArray(schema.sessionPages.id, data.deletedPageIds)
1996	)
1997	)
1998	.run()
1999	}
2000	if (data.pages.length > 0) {
2001	const pageIds = data.pages.map((page) => page.id)
2002	const caseWhenFragments = data.pages.map(
2003	(page) => sql`WHEN ${schema.sessionPages.id} = ${page.id} THEN ${page.pageNumber}`
2004	)
2005	const pageNumberExpr = sql<number>`CASE ${sql.join(caseWhenFragments, sql` `)} ELSE ${schema.sessionPages.pageNumber} END`
2006	await tx
2007	.update(schema.sessionPages)
2008	.set({ pageNumber: pageNumberExpr, updatedAt: now })
2009	.where(
2010	and(
2011	eq(schema.sessionPages.sessionId, data.sessionId),
2012	inArray(schema.sessionPages.id, pageIds)
2013	)
2014	)
2015	.run()
2016	}
2017	await tx
2018	.update(schema.sessions)
2019	.set({ metadata: JSON.stringify(data.metadata), updatedAt: now })
2020	.where(eq(schema.sessions.id, data.sessionId))
2021	.run()
2022	})
2023	}
2024
2025	async softDeleteSessionPages(sessionId: string, ids: string[]): Promise<void> {
2026	if (!Array.isArray(ids) \|\| ids.length === 0) return
2027	const now = Math.floor(Date.now() / 1000)
2028	await this.db
2029	.update(schema.sessionPages)
2030	.set({
2031	deletedAt: now,
2032	updatedAt: now
2033	})
2034	.where(
2035	and(eq(schema.sessionPages.sessionId, sessionId), inArray(schema.sessionPages.id, ids))
2036	)
2037	.run()
2038	}
2039
2040	async hardDeleteSessionPages(sessionId: string, ids: string[]): Promise<void> {
2041	if (!Array.isArray(ids) \|\| ids.length === 0) return
2042	await this.db
2043	.delete(schema.sessionPages)
2044	.where(
2045	and(eq(schema.sessionPages.sessionId, sessionId), inArray(schema.sessionPages.id, ids))
2046	)
2047	.run()
2048	}
2049
2050	// ========== Session History ==========
2051
2052	private normalizeSessionOperationRow(row: Record<string, unknown>): SessionOperationRecord {
2053	return {
2054	id: String(row.id \|\| ''),
2055	session_id: String(row.sessionId ?? row.session_id ?? ''),
2056	type: String(row.type \|\| 'edit') as SessionOperationType,
2057	status: String(row.status \|\| 'completed') as SessionOperationStatus,
2058	scope:
2059	typeof (row.scope ?? row.scope) === 'string'
2060	? (String(row.scope) as SessionOperationScope)
2061	: null,
2062	prompt:
2063	typeof row.prompt === 'string' && row.prompt.trim().length > 0 ? String(row.prompt) : null,
2064	parent_operation_id:
2065	typeof (row.parentOperationId ?? row.parent_operation_id) === 'string'
2066	? String(row.parentOperationId ?? row.parent_operation_id)
2067	: null,
2068	before_commit:
2069	typeof (row.beforeCommit ?? row.before_commit) === 'string'
2070	? String(row.beforeCommit ?? row.before_commit)
2071	: null,
2072	after_commit:
2073	typeof (row.afterCommit ?? row.after_commit) === 'string'
2074	? String(row.afterCommit ?? row.after_commit)
2075	: null,
2076	target_operation_id:
2077	typeof (row.targetOperationId ?? row.target_operation_id) === 'string'
2078	? String(row.targetOperationId ?? row.target_operation_id)
2079	: null,
2080	target_commit:
2081	typeof (row.targetCommit ?? row.target_commit) === 'string'
2082	? String(row.targetCommit ?? row.target_commit)
2083	: null,
2084	changed_files_json: String(row.changedFilesJson ?? row.changed_files_json ?? '[]'),
2085	changed_pages_json: String(row.changedPagesJson ?? row.changed_pages_json ?? '[]'),
2086	tracked_files_json: String(row.trackedFilesJson ?? row.tracked_files_json ?? '[]'),
2087	metadata_json: String(row.metadataJson ?? row.metadata_json ?? '{}'),
2088	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
2089	completed_at:
2090	typeof (row.completedAt ?? row.completed_at) === 'number'
2091	? Number(row.completedAt ?? row.completed_at)
2092	: null
2093	}
2094	}
2095
2096	private normalizeSessionOperationPageRow(
2097	row: Record<string, unknown>
2098	): SessionOperationPageRecord {
2099	return {
2100	id: String(row.id \|\| ''),
2101	operation_id: String(row.operationId ?? row.operation_id ?? ''),
2102	session_id: String(row.sessionId ?? row.session_id ?? ''),
2103	page_id: String(row.pageId ?? row.page_id ?? ''),
2104	legacy_page_id:
2105	typeof (row.legacyPageId ?? row.legacy_page_id) === 'string'
2106	? String(row.legacyPageId ?? row.legacy_page_id)
2107	: null,
2108	file_slug: String(row.fileSlug ?? row.file_slug ?? ''),
2109	page_number: Number(row.pageNumber ?? row.page_number ?? 0) \|\| 0,
2110	title: String(row.title \|\| ''),
2111	html_path: String(row.htmlPath ?? row.html_path ?? ''),
2112	status: String(row.status \|\| 'pending') as SessionPageStatus,
2113	error: typeof row.error === 'string' ? String(row.error) : null,
2114	created_at: Number(row.createdAt ?? row.created_at ?? 0) \|\| 0,
2115	updated_at: Number(row.updatedAt ?? row.updated_at ?? 0) \|\| 0
2116	}
2117	}
2118
2119	async createSessionOperation(data: {
2120	id: string
2121	sessionId: string
2122	type: SessionOperationType
2123	status?: SessionOperationStatus
2124	scope?: SessionOperationScope \| null
2125	prompt?: string \| null
2126	parentOperationId?: string \| null
2127	beforeCommit?: string \| null
2128	targetOperationId?: string \| null
2129	targetCommit?: string \| null
2130	metadata?: unknown
2131	}): Promise<void> {
2132	const now = Math.floor(Date.now() / 1000)
2133	await this.db
2134	.insert(schema.sessionOperations)
2135	.values({
2136	id: data.id,
2137	sessionId: data.sessionId,
2138	type: data.type,
2139	status: data.status \|\| 'committing',
2140	scope: data.scope \|\| null,
2141	prompt: data.prompt \|\| null,
2142	parentOperationId: data.parentOperationId \|\| null,
2143	beforeCommit: data.beforeCommit \|\| null,
2144	afterCommit: null,
2145	targetOperationId: data.targetOperationId \|\| null,
2146	targetCommit: data.targetCommit \|\| null,
2147	changedFilesJson: '[]',
2148	changedPagesJson: '[]',
2149	trackedFilesJson: '[]',
2150	metadataJson: data.metadata ? JSON.stringify(data.metadata) : '{}',
2151	createdAt: now,
2152	completedAt: null
2153	})
2154	.run()
2155	}
2156
2157	async completeSessionOperation(data: {
2158	id: string
2159	status: 'completed' \| 'noop' \| 'failed'
2160	afterCommit?: string \| null
2161	changedFiles?: unknown[]
2162	changedPages?: unknown[]
2163	trackedFiles?: string[]
2164	metadata?: unknown
2165	}): Promise<void> {
2166	await this.db
2167	.update(schema.sessionOperations)
2168	.set({
2169	status: data.status,
2170	afterCommit: data.afterCommit \|\| null,
2171	changedFilesJson: JSON.stringify(data.changedFiles \|\| []),
2172	changedPagesJson: JSON.stringify(data.changedPages \|\| []),
2173	trackedFilesJson: JSON.stringify(data.trackedFiles \|\| []),
2174	metadataJson: JSON.stringify(data.metadata \|\| {}),
2175	completedAt: Math.floor(Date.now() / 1000)
2176	})
2177	.where(eq(schema.sessionOperations.id, data.id))
2178	.run()
2179	}
2180
2181	async updateSessionOperationMetadata(
2182	operationId: string,
2183	metadata: Record<string, unknown>
2184	): Promise<void> {
2185	await this.db
2186	.update(schema.sessionOperations)
2187	.set({ metadataJson: JSON.stringify(metadata) })
2188	.where(eq(schema.sessionOperations.id, operationId))
2189	.run()
2190	}
2191
2192	async getSessionOperation(operationId: string): Promise<SessionOperationRecord \| undefined> {
2193	const row = await this.db
2194	.select()
2195	.from(schema.sessionOperations)
2196	.where(eq(schema.sessionOperations.id, operationId))
2197	.get()
2198	return row ? this.normalizeSessionOperationRow(row as Record<string, unknown>) : undefined
2199	}
2200
2201	async hasAnyOperationPageSnapshots(sessionId: string): Promise<boolean> {
2202	const row = await this.db
2203	.select({ id: schema.sessionOperationPages.id })
2204	.from(schema.sessionOperationPages)
2205	.where(eq(schema.sessionOperationPages.sessionId, sessionId))
2206	.limit(1)
2207	.get()
2208	return !!row
2209	}
2210
2211	async cleanupSessionOperations(sessionId: string): Promise<number> {
2212	const rows = await this.db
2213	.select({ id: schema.sessionOperations.id })
2214	.from(schema.sessionOperations)
2215	.where(eq(schema.sessionOperations.sessionId, sessionId))
2216	.all()
2217	if (rows.length === 0) {
2218	await this.updateSessionHistoryPointer({ sessionId, operationId: null, commit: null })
2219	return 0
2220	}
2221	const ids = rows.map((r) => r.id)
2222	await this.db
2223	.delete(schema.sessionOperationPages)
2224	.where(inArray(schema.sessionOperationPages.operationId, ids))
2225	.run()
2226	await this.db
2227	.delete(schema.sessionOperations)
2228	.where(inArray(schema.sessionOperations.id, ids))
2229	.run()
2230	await this.updateSessionHistoryPointer({ sessionId, operationId: null, commit: null })
2231	return ids.length
2232	}
2233
2234	async listSessionOperations(
2235	sessionId: string,
2236	options?: { limit?: number; includeNoop?: boolean }
2237	): Promise<SessionOperationRecord[]> {
2238	const rows = await this.db
2239	.select()
2240	.from(schema.sessionOperations)
2241	.where(eq(schema.sessionOperations.sessionId, sessionId))
2242	.orderBy(desc(schema.sessionOperations.createdAt))
2243	.limit(Math.max(1, Math.min(200, Math.floor(options?.limit \|\| 50))))
2244	.all()
2245	return rows
2246	.map((row) => this.normalizeSessionOperationRow(row as Record<string, unknown>))
2247	.filter((row) =>
2248	options?.includeNoop
2249	? row.status === 'completed' \|\| row.status === 'noop'
2250	: row.status === 'completed'
2251	)
2252	}
2253
2254	async replaceSessionOperationPages(
2255	operationId: string,
2256	sessionId: string,
2257	pages: Array<{
2258	pageId: string
2259	legacyPageId?: string \| null
2260	fileSlug: string
2261	pageNumber: number
2262	title: string
2263	htmlPath: string
2264	status?: SessionPageStatus
2265	error?: string \| null
2266	}>
2267	): Promise<void> {
2268	const now = Math.floor(Date.now() / 1000)
2269	await this.db
2270	.delete(schema.sessionOperationPages)
2271	.where(eq(schema.sessionOperationPages.operationId, operationId))
2272	.run()
2273	for (const page of pages) {
2274	await this.db
2275	.insert(schema.sessionOperationPages)
2276	.values({
2277	id: `${operationId}:${page.pageId}`,
2278	operationId,
2279	sessionId,
2280	pageId: page.pageId,
2281	legacyPageId: page.legacyPageId \|\| null,
2282	fileSlug: page.fileSlug,
2283	pageNumber: page.pageNumber,
2284	title: page.title,
2285	htmlPath: page.htmlPath,
2286	status: page.status \|\| 'pending',
2287	error: page.error \|\| null,
2288	createdAt: now,
2289	updatedAt: now
2290	})
2291	.run()
2292	}
2293	}
2294
2295	async listSessionOperationPages(operationId: string): Promise<SessionOperationPageRecord[]> {
2296	const rows = await this.db
2297	.select()
2298	.from(schema.sessionOperationPages)
2299	.where(eq(schema.sessionOperationPages.operationId, operationId))
2300	.orderBy(asc(schema.sessionOperationPages.pageNumber))
2301	.all()
2302	return rows.map((row) => this.normalizeSessionOperationPageRow(row as Record<string, unknown>))
2303	}
2304
2305	// ========== Messages ==========
2306
2307	async getSessionMessages(
2308	sessionId: string,
2309	options?: {
2310	chatScope?: ChatScope
2311	pageId?: string
2312	}
2313	): Promise<Message[]> {
2314	const chatScope = options?.chatScope ?? 'main'
2315	const normalizedPageId =
2316	typeof options?.pageId === 'string' && options.pageId.trim().length > 0
2317	? options.pageId.trim()
2318	: null
2319	if (chatScope === 'page' && !normalizedPageId) {
2320	return []
2321	}
2322	if (chatScope === 'page' && normalizedPageId) {
2323	// Rollback / page-management may switch between canonical id and fileSlug.
2324	// Query messages by all known aliases to keep page chat continuous.
2325	const aliases = new Set<string>([normalizedPageId])
2326	const directRows = await this.db
2327	.select({
2328	id: schema.sessionPages.id,
2329	fileSlug: schema.sessionPages.fileSlug,
2330	legacyPageId: schema.sessionPages.legacyPageId
2331	})
2332	.from(schema.sessionPages)
2333	.where(
2334	and(
2335	eq(schema.sessionPages.sessionId, sessionId),
2336	or(
2337	eq(schema.sessionPages.id, normalizedPageId),
2338	eq(schema.sessionPages.fileSlug, normalizedPageId),
2339	eq(schema.sessionPages.legacyPageId, normalizedPageId)
2340	)
2341	)
2342	)
2343	.all()
2344	const matchedSlugs = Array.from(
2345	new Set(
2346	directRows
2347	.map((row) => String(row.fileSlug \|\| '').trim())
2348	.filter((item) => item.length > 0)
2349	)
2350	)
2351	if (matchedSlugs.length > 0) {
2352	const relatedRows = await this.db
2353	.select({
2354	id: schema.sessionPages.id,
2355	fileSlug: schema.sessionPages.fileSlug,
2356	legacyPageId: schema.sessionPages.legacyPageId
2357	})
2358	.from(schema.sessionPages)
2359	.where(
2360	and(
2361	eq(schema.sessionPages.sessionId, sessionId),
2362	inArray(schema.sessionPages.fileSlug, matchedSlugs)
2363	)
2364	)
2365	.all()
2366	for (const row of relatedRows) {
2367	if (typeof row.id === 'string' && row.id.trim().length > 0) aliases.add(row.id.trim())
2368	if (typeof row.fileSlug === 'string' && row.fileSlug.trim().length > 0)
2369	aliases.add(row.fileSlug.trim())
2370	if (typeof row.legacyPageId === 'string' && row.legacyPageId.trim().length > 0)
2371	aliases.add(row.legacyPageId.trim())
2372	}
2373	}
2374	const results = await this.db
2375	.select()
2376	.from(schema.messages)
2377	.where(
2378	and(
2379	eq(schema.messages.sessionId, sessionId),
2380	eq(schema.messages.chatScope, 'page'),
2381	inArray(schema.messages.pageId, Array.from(aliases))
2382	)
2383	)
2384	.orderBy(asc(schema.messages.createdAt))
2385	.all()
2386	return results.map((message) => this.normalizeMessageRow(message as Record<string, unknown>))
2387	}
2388	const whereClause = and(
2389	eq(schema.messages.sessionId, sessionId),
2390	eq(schema.messages.chatScope, 'main')
2391	)
2392	const results = await this.db
2393	.select()
2394	.from(schema.messages)
2395	.where(whereClause)
2396	.orderBy(asc(schema.messages.createdAt))
2397	.all()
2398
2399	return results.map((message) => this.normalizeMessageRow(message as Record<string, unknown>))
2400	}
2401
2402	private normalizeAssetPaths(value: unknown, prefix: './images/' \| './videos/'): string[] \| null {
2403	if (typeof value !== 'string' \|\| value.trim().length === 0) return null
2404	try {
2405	const parsed = JSON.parse(value) as unknown
2406	if (!Array.isArray(parsed)) return null
2407	const valid = parsed
2408	.map((item) => String(item \|\| '').trim())
2409	.filter((item) => item.startsWith(prefix))
2410	.slice(0, 10)
2411	return valid.length > 0 ? valid : null
2412	} catch {
2413	return null
2414	}
2415	}
2416
2417	private normalizeMessageRow(message: Record<string, unknown>): Message {
2418	const rawImagePaths = message.imagePaths ?? message.image_paths ?? null
2419	const rawVideoPaths = message.videoPaths ?? message.video_paths ?? null
2420	const imagePaths = this.normalizeAssetPaths(rawImagePaths, './images/')
2421	const videoPaths = this.normalizeAssetPaths(rawVideoPaths, './videos/')
2422	return {
2423	id: String(message.id \|\| ''),
2424	session_id: String(message.sessionId ?? message.session_id ?? ''),
2425	chat_scope: message.chatScope === 'page' \|\| message.chat_scope === 'page' ? 'page' : 'main',
2426	page_id:
2427	typeof (message.pageId ?? message.page_id) === 'string'
2428	? String(message.pageId ?? message.page_id)
2429	: null,
2430	selector:
2431	typeof message.selector === 'string' && message.selector.trim().length > 0
2432	? message.selector.trim()
2433	: null,
2434	image_paths: imagePaths,
2435	video_paths: videoPaths,
2436	role: String(message.role \|\| 'system') as MessageRole,
2437	content: String(message.content \|\| ''),
2438	type: String(message.type \|\| 'text') as MessageType,
2439	tool_name:
2440	typeof (message.toolName ?? message.tool_name) === 'string'
2441	? String(message.toolName ?? message.tool_name)
2442	: null,
2443	tool_call_id:
2444	typeof (message.toolCallId ?? message.tool_call_id) === 'string'
2445	? String(message.toolCallId ?? message.tool_call_id)
2446	: null,
2447	token_count:
2448	typeof (message.tokenCount ?? message.token_count) === 'number'
2449	? Number(message.tokenCount ?? message.token_count)
2450	: null,
2451	run_model:
2452	typeof (message.runModel ?? message.run_model) === 'string'
2453	? String(message.runModel ?? message.run_model)
2454	: null,
2455	created_at:
2456	typeof (message.createdAt ?? message.created_at) === 'number'
2457	? Number(message.createdAt ?? message.created_at)
2458	: Math.floor(Date.now() / 1000)
2459	}
2460	}
2461
2462	async addMessage(
2463	sessionId: string,
2464	message: {
2465	role: MessageRole
2466	content: string
2467	type?: MessageType
2468	tool_name?: string \| null
2469	tool_call_id?: string \| null
2470	token_count?: number \| null
2471	chat_scope?: ChatScope
2472	page_id?: string \| null
2473	selector?: string \| null
2474	image_paths?: string[] \| null
2475	video_paths?: string[] \| null
2476	run_model?: string \| null
2477	id?: string
2478	}
2479	): Promise<string> {
2480	const id = message.id \|\| crypto.randomUUID()
2481	const now = Math.floor(Date.now() / 1000)
2482	const chatScope = message.chat_scope === 'page' ? 'page' : 'main'
2483	const pageId =
2484	chatScope === 'page' &&
2485	typeof message.page_id === 'string' &&
2486	message.page_id.trim().length > 0
2487	? message.page_id.trim()
2488	: null
2489	const selector =
2490	chatScope === 'page' &&
2491	typeof message.selector === 'string' &&
2492	message.selector.trim().length > 0
2493	? message.selector.trim()
2494	: null
2495	const imagePathsRaw = Array.isArray(message.image_paths) ? message.image_paths : []
2496	const imagePaths =
2497	imagePathsRaw.length > 0
2498	? imagePathsRaw
2499	.map((item) => String(item \|\| '').trim())
2500	.filter((item) => item.startsWith('./images/'))
2501	.slice(0, 10)
2502	: []
2503	const videoPathsRaw = Array.isArray(message.video_paths) ? message.video_paths : []
2504	const videoPaths =
2505	videoPathsRaw.length > 0
2506	? videoPathsRaw
2507	.map((item) => String(item \|\| '').trim())
2508	.filter((item) => item.startsWith('./videos/'))
2509	.slice(0, 10)
2510	: []
2511	const imagePathsJson = imagePaths.length > 0 ? JSON.stringify(imagePaths) : null
2512	const videoPathsJson = videoPaths.length > 0 ? JSON.stringify(videoPaths) : null
2513	if (chatScope === 'page' && !pageId) {
2514	throw new Error('page chat message requires page_id')
2515	}
2516
2517	await this.db
2518	.insert(schema.messages)
2519	.values({
2520	id,
2521	sessionId,
2522	chatScope,
2523	pageId,
2524	selector,
2525	imagePaths: imagePathsJson,
2526	videoPaths: videoPathsJson,
2527	role: message.role,
2528	content: message.content,
2529	type: message.type \|\| 'text',
2530	toolName: message.tool_name \|\| null,
2531	toolCallId: message.tool_call_id \|\| null,
2532	tokenCount: message.token_count \|\| null,
2533	runModel:
2534	typeof message.run_model === 'string' && message.run_model.trim().length > 0
2535	? message.run_model
2536	: null,
2537	createdAt: now
2538	})
2539	.run()
2540
2541	await this.db
2542	.update(schema.sessions)
2543	.set({ updatedAt: now })
2544	.where(eq(schema.sessions.id, sessionId))
2545	.run()
2546
2547	return id
2548	}
2549
2550	async getMessageCount(sessionId: string): Promise<number> {
2551	const result = await this.db
2552	.select({ count: count() })
2553	.from(schema.messages)
2554	.where(eq(schema.messages.sessionId, sessionId))
2555	.get()
2556	return result?.count ?? 0
2557	}
2558
2559	async getRecentMessages(sessionId: string, count: number): Promise<Message[]> {
2560	const results = await this.db
2561	.select()
2562	.from(schema.messages)
2563	.where(eq(schema.messages.sessionId, sessionId))
2564	.orderBy(desc(schema.messages.createdAt))
2565	.limit(count)
2566	.all()
2567
2568	return results.map((message) => this.normalizeMessageRow(message as Record<string, unknown>))
2569	}
2570
2571	// ========== Memory ==========
2572
2573	async getLastSummary(sessionId: string): Promise<MemorySummary \| undefined> {
2574	const result = await this.db
2575	.select()
2576	.from(schema.memorySummaries)
2577	.where(eq(schema.memorySummaries.sessionId, sessionId))
2578	.orderBy(desc(schema.memorySummaries.messageRangeEnd))
2579	.limit(1)
2580	.get()
2581
2582	return result as MemorySummary \| undefined
2583	}
2584
2585	async saveSummary(
2586	sessionId: string,
2587	data: {
2588	rangeStart: number
2589	rangeEnd: number
2590	summary: string
2591	tokenCount?: number
2592	}
2593	): Promise<string> {
2594	const id = crypto.randomUUID()
2595	const now = Math.floor(Date.now() / 1000)
2596
2597	await this.db
2598	.insert(schema.memorySummaries)
2599	.values({
2600	id,
2601	sessionId,
2602	messageRangeStart: data.rangeStart,
2603	messageRangeEnd: data.rangeEnd,
2604	summary: data.summary,
2605	tokenCount: data.tokenCount \|\| null,
2606	createdAt: now
2607	})
2608	.run()
2609
2610	return id
2611	}
2612
2613	async getLastCompressedIndex(sessionId: string): Promise<number> {
2614	const result = await this.db
2615	.select({ maxIndex: max(schema.memorySummaries.messageRangeEnd) })
2616	.from(schema.memorySummaries)
2617	.where(eq(schema.memorySummaries.sessionId, sessionId))
2618	.get()
2619	return result?.maxIndex ?? 0
2620	}
2621
2622	async getMessagesForCompression(
2623	sessionId: string,
2624	batchSize: number
2625	): Promise<(Message & { idx: number })[]> {
2626	const lastCompressedIndex = await this.getLastCompressedIndex(sessionId)
2627
2628	const results = await this.db
2629	.select({
2630	id: schema.messages.id,
2631	sessionId: schema.messages.sessionId,
2632	chatScope: schema.messages.chatScope,
2633	pageId: schema.messages.pageId,
2634	role: schema.messages.role,
2635	content: schema.messages.content,
2636	type: schema.messages.type,
2637	toolName: schema.messages.toolName,
2638	toolCallId: schema.messages.toolCallId,
2639	tokenCount: schema.messages.tokenCount,
2640	runModel: schema.messages.runModel,
2641	createdAt: schema.messages.createdAt
2642	})
2643	.from(schema.messages)
2644	.where(
2645	and(
2646	eq(schema.messages.sessionId, sessionId),
2647	gt(schema.messages.createdAt, lastCompressedIndex)
2648	)
2649	)
2650	.orderBy(asc(schema.messages.createdAt))
2651	.limit(batchSize)
2652	.all()
2653
2654	let idx = lastCompressedIndex + 1
2655	return results.map((r) => ({
2656	...this.normalizeMessageRow(r as Record<string, unknown>),
2657	idx: idx++
2658	}))
2659	}
2660
2661	// ========== Settings ==========
2662
2663	async recordModelUsage(data: {
2664	provider: string
2665	model: string
2666	modelConfigId?: string
2667	inputTokens: number
2668	outputTokens: number
2669	totalTokens: number
2670	source: 'provider' \| 'estimated'
2671	}): Promise<void> {
2672	await this.db
2673	.insert(schema.modelUsageEvents)
2674	.values({
2675	id: crypto.randomUUID(),
2676	provider: data.provider,
2677	model: data.model,
2678	modelConfigId: data.modelConfigId \|\| null,
2679	inputTokens: Math.max(0, Math.floor(data.inputTokens)),
2680	outputTokens: Math.max(0, Math.floor(data.outputTokens)),
2681	totalTokens: Math.max(0, Math.floor(data.totalTokens)),
2682	usageSource: data.source,
2683	createdAt: Math.floor(Date.now() / 1000)
2684	})
2685	.run()
2686	}
2687
2688	async getModelUsageStats(period: ModelUsagePeriod): Promise<ModelUsageStats> {
2689	const now = new Date()
2690	let startedAt: number \| null = null
2691	if (period === 'today') {
2692	const start = new Date(now.getFullYear(), now.getMonth(), now.getDate())
2693	startedAt = Math.floor(start.getTime() / 1000)
2694	} else if (period !== 'all') {
2695	const days = period === '7d' ? 7 : 30
2696	const start = new Date(now.getFullYear(), now.getMonth(), now.getDate() - days + 1)
2697	startedAt = Math.floor(start.getTime() / 1000)
2698	}
2699	const whereSql = startedAt === null ? '' : ' WHERE created_at >= ?'
2700	const args = startedAt === null ? [] : [startedAt]
2701	const totalsResult = await this.client.execute({
2702	sql: `
2703	SELECT
2704	COUNT(*) AS call_count,
2705	SUM(CASE WHEN usage_source = 'provider' THEN 1 ELSE 0 END) AS exact_call_count,
2706	SUM(CASE WHEN usage_source = 'estimated' THEN 1 ELSE 0 END) AS estimated_call_count,
2707	COALESCE(SUM(input_tokens), 0) AS input_tokens,
2708	COALESCE(SUM(output_tokens), 0) AS output_tokens,
2709	COALESCE(SUM(total_tokens), 0) AS total_tokens
2710	FROM model_usage_events${whereSql}
2711	`,
2712	args
2713	})
2714	const byModelResult = await this.client.execute({
2715	sql: `
2716	SELECT
2717	provider,
2718	model,
2719	COUNT(*) AS call_count,
2720	SUM(CASE WHEN usage_source = 'provider' THEN 1 ELSE 0 END) AS exact_call_count,
2721	SUM(CASE WHEN usage_source = 'estimated' THEN 1 ELSE 0 END) AS estimated_call_count,
2722	COALESCE(SUM(input_tokens), 0) AS input_tokens,
2723	COALESCE(SUM(output_tokens), 0) AS output_tokens,
2724	COALESCE(SUM(total_tokens), 0) AS total_tokens
2725	FROM model_usage_events${whereSql}
2726	GROUP BY provider, model
2727	ORDER BY total_tokens DESC
2728	`,
2729	args
2730	})
2731	const byDayResult = await this.client.execute({
2732	sql: `
2733	SELECT
2734	date(created_at, 'unixepoch', 'localtime') AS date,
2735	COUNT(*) AS call_count,
2736	SUM(CASE WHEN usage_source = 'provider' THEN 1 ELSE 0 END) AS exact_call_count,
2737	SUM(CASE WHEN usage_source = 'estimated' THEN 1 ELSE 0 END) AS estimated_call_count,
2738	COALESCE(SUM(input_tokens), 0) AS input_tokens,
2739	COALESCE(SUM(output_tokens), 0) AS output_tokens,
2740	COALESCE(SUM(total_tokens), 0) AS total_tokens
2741	FROM model_usage_events${whereSql}
2742	GROUP BY date
2743	ORDER BY date ASC
2744	`,
2745	args
2746	})
2747
2748	const byHourResult =
2749	period === 'today'
2750	? await this.client.execute({
2751	sql: `
2752	SELECT
2753	CAST(strftime('%H', created_at, 'unixepoch', 'localtime') AS INTEGER) AS hour,
2754	COUNT(*) AS call_count,
2755	SUM(CASE WHEN usage_source = 'provider' THEN 1 ELSE 0 END) AS exact_call_count,
2756	SUM(CASE WHEN usage_source = 'estimated' THEN 1 ELSE 0 END) AS estimated_call_count,
2757	COALESCE(SUM(input_tokens), 0) AS input_tokens,
2758	COALESCE(SUM(output_tokens), 0) AS output_tokens,
2759	COALESCE(SUM(total_tokens), 0) AS total_tokens
2760	FROM model_usage_events${whereSql}
2761	GROUP BY hour
2762	ORDER BY hour ASC
2763	`,
2764	args
2765	})
2766	: null
2767
2768	const readTotals = (row: Record<string, unknown> \| undefined): ModelUsageTotals => ({
2769	callCount: Number(row?.call_count \|\| 0),
2770	exactCallCount: Number(row?.exact_call_count \|\| 0),
2771	estimatedCallCount: Number(row?.estimated_call_count \|\| 0),
2772	inputTokens: Number(row?.input_tokens \|\| 0),
2773	outputTokens: Number(row?.output_tokens \|\| 0),
2774	totalTokens: Number(row?.total_tokens \|\| 0)
2775	})
2776
2777	const byHour: ModelUsageByHour[] = []
2778	if (byHourResult) {
2779	const hourMap = new Map<number, ModelUsageTotals>()
2780	for (const row of byHourResult.rows) {
2781	const hour = Number((row as Record<string, unknown>).hour \|\| 0)
2782	hourMap.set(hour, readTotals(row as Record<string, unknown>))
2783	}
2784	for (let hour = 0; hour < 24; hour += 1) {
2785	byHour.push({ hour, ...(hourMap.get(hour) \|\| readTotals(undefined)) })
2786	}
2787	}
2788
2789	return {
2790	period,
2791	startedAt,
2792	totals: readTotals(totalsResult.rows[0] as Record<string, unknown> \| undefined),
2793	byModel: byModelResult.rows.map((row) => ({
2794	provider: String(row.provider \|\| ''),
2795	model: String(row.model \|\| ''),
2796	...readTotals(row as Record<string, unknown>)
2797	})),
2798	byDay: byDayResult.rows.map((row) => ({
2799	date: String(row.date \|\| ''),
2800	...readTotals(row as Record<string, unknown>)
2801	})),
2802	byHour
2803	}
2804	}
2805
2806	async getSetting<T>(key: string): Promise<T \| undefined> {
2807	const result = await this.db
2808	.select({ value: schema.settings.value })
2809	.from(schema.settings)
2810	.where(eq(schema.settings.key, key))
2811	.get()
2812	if (!result) return undefined
2813	try {
2814	return JSON.parse(result.value) as T
2815	} catch {
2816	return result.value as T
2817	}
2818	}
2819
2820	async setSetting<T>(key: string, value: T): Promise<void> {
2821	const now = Math.floor(Date.now() / 1000)
2822	await this.db
2823	.insert(schema.settings)
2824	.values({ key, value: JSON.stringify(value), updatedAt: now })
2825	.onConflictDoUpdate({
2826	target: schema.settings.key,
2827	set: { value: JSON.stringify(value), updatedAt: now }
2828	})
2829	.run()
2830	}
2831
2832	async getAllSettings(): Promise<Record<string, unknown>> {
2833	const results = await this.db.select().from(schema.settings).all()
2834	const result: Record<string, unknown> = {}
2835	for (const row of results) {
2836	try {
2837	result[row.key] = JSON.parse(row.value)
2838	} catch {
2839	result[row.key] = row.value
2840	}
2841	}
2842	return result
2843	}
2844
2845	// ========== Model Configs ==========
2846
2847	async listModelConfigs(): Promise<ModelConfigRow[]> {
2848	const results = await this.db
2849	.select()
2850	.from(schema.modelConfigs)
2851	.orderBy(desc(schema.modelConfigs.active), desc(schema.modelConfigs.updatedAt))
2852	.all()
2853	return results as unknown as ModelConfigRow[]
2854	}
2855
2856	async getActiveModelConfig(): Promise<ModelConfigRow \| undefined> {
2857	const result = await this.db
2858	.select()
2859	.from(schema.modelConfigs)
2860	.where(eq(schema.modelConfigs.active, 1))
2861	.limit(1)
2862	.get()
2863	return result as unknown as ModelConfigRow \| undefined
2864	}
2865
2866	async getModelConfig(id: string): Promise<ModelConfigRow \| undefined> {
2867	const result = await this.db
2868	.select()
2869	.from(schema.modelConfigs)
2870	.where(eq(schema.modelConfigs.id, id))
2871	.limit(1)
2872	.get()
2873	return result as unknown as ModelConfigRow \| undefined
2874	}
2875
2876	async upsertModelConfig(data: {
2877	id?: string
2878	name: string
2879	provider: string
2880	model: string
2881	apiKey: string
2882	baseUrl: string
2883	maxTokens?: number
2884	disableTemperature?: boolean
2885	thinkingParameterMode?: string
2886	active?: boolean
2887	}): Promise<string> {
2888	const id = data.id \|\| crypto.randomUUID()
2889	const now = Math.floor(Date.now() / 1000)
2890	const maxTokens = data.maxTokens \|\| 4096
2891	const disableTemperature = data.disableTemperature ? 1 : 0
2892	const thinkingParameterMode = normalizeThinkingParameterMode(data.thinkingParameterMode)
2893	if (data.active) {
2894	await this.db
2895	.update(schema.modelConfigs)
2896	.set({ active: 0, updatedAt: now })
2897	.where(eq(schema.modelConfigs.active, 1))
2898	.run()
2899	}
2900	await this.db
2901	.insert(schema.modelConfigs)
2902	.values({
2903	id,
2904	name: data.name,
2905	provider: data.provider,
2906	model: data.model,
2907	apiKey: data.apiKey,
2908	baseUrl: data.baseUrl,
2909	maxTokens,
2910	disableTemperature,
2911	thinkingParameterMode,
2912	active: data.active ? 1 : 0,
2913	createdAt: now,
2914	updatedAt: now
2915	})
2916	.onConflictDoUpdate({
2917	target: schema.modelConfigs.id,
2918	set: {
2919	name: data.name,
2920	provider: data.provider,
2921	model: data.model,
2922	apiKey: data.apiKey,
2923	baseUrl: data.baseUrl,
2924	maxTokens,
2925	disableTemperature,
2926	thinkingParameterMode,
2927	active: data.active ? 1 : 0,
2928	updatedAt: now
2929	}
2930	})
2931	.run()
2932	return id
2933	}
2934
2935	async setActiveModelConfig(id: string): Promise<void> {
2936	const now = Math.floor(Date.now() / 1000)
2937	const existing = await this.db
2938	.select()
2939	.from(schema.modelConfigs)
2940	.where(eq(schema.modelConfigs.id, id))
2941	.get()
2942	if (!existing) throw new Error('Model config does not exist')
2943	await this.db
2944	.update(schema.modelConfigs)
2945	.set({ active: 0, updatedAt: now })
2946	.where(eq(schema.modelConfigs.active, 1))
2947	.run()
2948	await this.db
2949	.update(schema.modelConfigs)
2950	.set({ active: 1, updatedAt: now })
2951	.where(eq(schema.modelConfigs.id, id))
2952	.run()
2953	}
2954
2955	async deleteModelConfig(id: string): Promise<void> {
2956	const existing = await this.db
2957	.select()
2958	.from(schema.modelConfigs)
2959	.where(eq(schema.modelConfigs.id, id))
2960	.get()
2961	if (!existing) throw new Error('Model config does not exist')
2962	await this.db.delete(schema.modelConfigs).where(eq(schema.modelConfigs.id, id)).run()
2963	}
2964
2965	// ========== Image Model Configs ==========
2966
2967	async listImageModelConfigs(): Promise<ImageModelConfigRow[]> {
2968	const results = await this.db
2969	.select()
2970	.from(schema.imageModelConfigs)
2971	.orderBy(desc(schema.imageModelConfigs.active), desc(schema.imageModelConfigs.updatedAt))
2972	.all()
2973	return results as unknown as ImageModelConfigRow[]
2974	}
2975
2976	async getActiveImageModelConfig(): Promise<ImageModelConfigRow \| undefined> {
2977	const result = await this.db
2978	.select()
2979	.from(schema.imageModelConfigs)
2980	.where(eq(schema.imageModelConfigs.active, 1))
2981	.limit(1)
2982	.get()
2983	return result as unknown as ImageModelConfigRow \| undefined
2984	}
2985
2986	async getImageModelConfig(id: string): Promise<ImageModelConfigRow \| undefined> {
2987	const result = await this.db
2988	.select()
2989	.from(schema.imageModelConfigs)
2990	.where(eq(schema.imageModelConfigs.id, id))
2991	.limit(1)
2992	.get()
2993	return result as unknown as ImageModelConfigRow \| undefined
2994	}
2995
2996	async upsertImageModelConfig(data: {
2997	id?: string
2998	name: string
2999	provider: string
3000	modelConfig: string
3001	active?: boolean
3002	}): Promise<string> {
3003	const id = data.id \|\| crypto.randomUUID()
3004	const now = Math.floor(Date.now() / 1000)
3005	if (data.active) {
3006	await this.db
3007	.update(schema.imageModelConfigs)
3008	.set({ active: 0, updatedAt: now })
3009	.where(eq(schema.imageModelConfigs.active, 1))
3010	.run()
3011	}
3012	await this.db
3013	.insert(schema.imageModelConfigs)
3014	.values({
3015	id,
3016	name: data.name,
3017	provider: data.provider,
3018	modelConfig: data.modelConfig,
3019	active: data.active ? 1 : 0,
3020	createdAt: now,
3021	updatedAt: now
3022	})
3023	.onConflictDoUpdate({
3024	target: schema.imageModelConfigs.id,
3025	set: {
3026	name: data.name,
3027	provider: data.provider,
3028	modelConfig: data.modelConfig,
3029	active: data.active ? 1 : 0,
3030	updatedAt: now
3031	}
3032	})
3033	.run()
3034	return id
3035	}
3036
3037	async setActiveImageModelConfig(id: string): Promise<void> {
3038	const now = Math.floor(Date.now() / 1000)
3039	const existing = await this.db
3040	.select()
3041	.from(schema.imageModelConfigs)
3042	.where(eq(schema.imageModelConfigs.id, id))
3043	.get()
3044	if (!existing) throw new Error('Image model config does not exist')
3045	await this.db
3046	.update(schema.imageModelConfigs)
3047	.set({ active: 0, updatedAt: now })
3048	.where(eq(schema.imageModelConfigs.active, 1))
3049	.run()
3050	await this.db
3051	.update(schema.imageModelConfigs)
3052	.set({ active: 1, updatedAt: now })
3053	.where(eq(schema.imageModelConfigs.id, id))
3054	.run()
3055	}
3056
3057	async deleteImageModelConfig(id: string): Promise<void> {
3058	const existing = await this.db
3059	.select()
3060	.from(schema.imageModelConfigs)
3061	.where(eq(schema.imageModelConfigs.id, id))
3062	.get()
3063	if (!existing) throw new Error('Image model config does not exist')
3064	const [{ value: referencedSessionCount }] = await this.db
3065	.select({ value: count() })
3066	.from(schema.sessions)
3067	.where(and(eq(schema.sessions.imageModelConfigId, id), eq(schema.sessions.visualEnabled, 1)))
3068	.all()
3069	if (referencedSessionCount > 0) {
3070	throw new Error('Image model config is used by visual-enabled sessions')
3071	}
3072	const [{ value: activeJobCount }] = await this.db
3073	.select({ value: count() })
3074	.from(schema.imageFulfillmentJobs)
3075	.where(
3076	and(
3077	eq(schema.imageFulfillmentJobs.imageModelConfigId, id),
3078	inArray(schema.imageFulfillmentJobs.status, ['pending', 'running', 'finalizing'])
3079	)
3080	)
3081	.all()
3082	if (activeJobCount > 0) {
3083	throw new Error('Image model config is used by active image fulfillment jobs')
3084	}
3085	await this.db.delete(schema.imageModelConfigs).where(eq(schema.imageModelConfigs.id, id)).run()
3086	}
3087
3088	// ========== Image Fulfillment Jobs ===========
3089
3090	async createImageFulfillmentJob(data: {
3091	id?: string
3092	runId: string
3093	sessionId: string
3094	sessionPageId: string
3095	pageId: string
3096	layoutId?: string \| null
3097	layoutContractVersion?: number \| null
3098	imageModelConfigId: string
3099	imageProvider: string
3100	imageModel: string
3101	idempotencyKey?: string \| null
3102	retryOfJobId?: string \| null
3103	intents: ImageFulfillmentIntentCreateData[]
3104	}): Promise<{
3105	job: ImageFulfillmentJobRecord
3106	intents: ImageFulfillmentIntentRecord[]
3107	created: boolean
3108	}> {
3109	const pageId = data.pageId.trim()
3110	const idempotencyKey = data.idempotencyKey?.trim() \|\| null
3111	const intents = data.intents.map((intent) => ({
3112	...intent,
3113	slotId: intent.slotId.trim(),
3114	layoutSlotId: intent.layoutSlotId.trim(),
3115	role: intent.role.trim(),
3116	layer: intent.layer.trim(),
3117	subject: intent.subject.trim(),
3118	requestJson: intent.requestJson.trim()
3119	}))
3120	if (!pageId) throw new Error('image fulfillment pageId is required')
3121	if (!data.imageModelConfigId.trim())
3122	throw new Error('image fulfillment model config is required')
3123	if (intents.length === 0) throw new Error('image fulfillment requires at least one intent')
3124	if (
3125	intents.some(
3126	(intent) =>
3127	!intent.slotId \|\|
3128	!intent.layoutSlotId \|\|
3129	!intent.role \|\|
3130	!intent.layer \|\|
3131	!intent.subject \|\|
3132	!intent.requestJson
3133	)
3134	) {
3135	throw new Error('image fulfillment intent is incomplete')
3136	}
3137	if (new Set(intents.map((intent) => intent.slotId)).size !== intents.length) {
3138	throw new Error('image fulfillment slot IDs must be unique within one job')
3139	}
3140
3141	const now = Math.floor(Date.now() / 1000)
3142	return this.db.transaction(async (tx) => {
3143	if (idempotencyKey) {
3144	const existing = await tx
3145	.select()
3146	.from(schema.imageFulfillmentJobs)
3147	.where(
3148	and(
3149	eq(schema.imageFulfillmentJobs.sessionId, data.sessionId),
3150	eq(schema.imageFulfillmentJobs.idempotencyKey, idempotencyKey)
3151	)
3152	)
3153	.get()
3154	if (existing) {
3155	const job = this.normalizeImageFulfillmentJobRow(existing as Record<string, unknown>)
3156	const existingIntents = await tx
3157	.select()
3158	.from(schema.imageFulfillmentIntents)
3159	.where(eq(schema.imageFulfillmentIntents.jobId, job.id))
3160	.orderBy(asc(schema.imageFulfillmentIntents.createdAt))
3161	.all()
3162	return {
3163	job,
3164	intents: existingIntents.map((intent) =>
3165	this.normalizeImageFulfillmentIntentRow(intent as Record<string, unknown>)
3166	),
3167	created: false
3168	}
3169	}
3170	}
3171
3172	const sessionPage = await tx
3173	.select()
3174	.from(schema.sessionPages)
3175	.where(
3176	and(
3177	eq(schema.sessionPages.id, data.sessionPageId),
3178	eq(schema.sessionPages.sessionId, data.sessionId)
3179	)
3180	)
3181	.get()
3182	if (!sessionPage) throw new Error('image fulfillment page does not belong to the session')
3183	if (
3184	pageId !== sessionPage.fileSlug &&
3185	pageId !== sessionPage.id &&
3186	pageId !== (sessionPage.legacyPageId \|\| '')
3187	) {
3188	throw new Error('image fulfillment pageId does not match the session page')
3189	}
3190
3191	if (data.retryOfJobId) {
3192	const source = await tx
3193	.select()
3194	.from(schema.imageFulfillmentJobs)
3195	.where(eq(schema.imageFulfillmentJobs.id, data.retryOfJobId))
3196	.get()
3197	const sourceStatus = String(source?.status \|\| '')
3198	if (
3199	!source \|\|
3200	source.sessionId !== data.sessionId \|\|
3201	source.sessionPageId !== data.sessionPageId \|\|
3202	!['completed', 'degraded', 'failed', 'cancelled'].includes(sourceStatus)
3203	) {
3204	throw new Error('image fulfillment retry source is not eligible')
3205	}
3206	}
3207
3208	const activeJob = await tx
3209	.select({ id: schema.imageFulfillmentJobs.id })
3210	.from(schema.imageFulfillmentJobs)
3211	.where(
3212	and(
3213	eq(schema.imageFulfillmentJobs.sessionId, data.sessionId),
3214	eq(schema.imageFulfillmentJobs.sessionPageId, data.sessionPageId),
3215	inArray(schema.imageFulfillmentJobs.status, ['pending', 'running', 'finalizing'])
3216	)
3217	)
3218	.get()
3219	if (activeJob) throw new Error('an image fulfillment job is already active for this page')
3220
3221	const latestAttempt = await tx
3222	.select({ value: max(schema.imageFulfillmentJobs.attempt) })
3223	.from(schema.imageFulfillmentJobs)
3224	.where(
3225	and(
3226	eq(schema.imageFulfillmentJobs.runId, data.runId),
3227	eq(schema.imageFulfillmentJobs.sessionPageId, data.sessionPageId)
3228	)
3229	)
3230	.get()
3231	const attempt = Math.max(0, Number(latestAttempt?.value \|\| 0)) + 1
3232	const id = data.id \|\| nanoid()
3233	const values = {
3234	id,
3235	runId: data.runId,
3236	sessionId: data.sessionId,
3237	sessionPageId: data.sessionPageId,
3238	pageId,
3239	layoutId: data.layoutId \|\| null,
3240	layoutContractVersion: data.layoutContractVersion \|\| null,
3241	imageModelConfigId: data.imageModelConfigId.trim(),
3242	imageProvider: data.imageProvider.trim() \|\| null,
3243	imageModel: data.imageModel.trim() \|\| null,
3244	attempt,
3245	retryOfJobId: data.retryOfJobId \|\| null,
3246	idempotencyKey,
3247	status: 'pending' as const,
3248	error: null,
3249	cancelRequestedAt: null,
3250	leaseOwner: null,
3251	leaseExpiresAt: null,
3252	finalizationManifestPath: null,
3253	createdAt: now,
3254	startedAt: null,
3255	updatedAt: now,
3256	finishedAt: null
3257	}
3258	await tx.insert(schema.imageFulfillmentJobs).values(values)
3259	const intentValues = intents.map((intent) => ({
3260	id: intent.id \|\| nanoid(),
3261	jobId: id,
3262	slotId: intent.slotId,
3263	layoutSlotId: intent.layoutSlotId,
3264	role: intent.role,
3265	layer: intent.layer,
3266	requestVersion: Math.max(1, Math.floor(intent.requestVersion \|\| 1)),
3267	sizeHint: intent.sizeHint?.trim() \|\| null,
3268	subject: intent.subject,
3269	textZone: intent.textZone?.trim() \|\| null,
3270	subjectZone: intent.subjectZone?.trim() \|\| null,
3271	negativeSpace: intent.negativeSpace?.trim() \|\| null,
3272	avoidJson: intent.avoidJson?.trim() \|\| null,
3273	requestJson: intent.requestJson,
3274	imageHistoryId: null,
3275	assetPath: null,
3276	width: null,
3277	height: null,
3278	mimeType: null,
3279	attempt,
3280	retryOfIntentId: intent.retryOfIntentId \|\| null,
3281	status: 'pending' as const,
3282	error: null,
3283	createdAt: now,
3284	updatedAt: now
3285	}))
3286	await tx.insert(schema.imageFulfillmentIntents).values(intentValues)
3287	return {
3288	job: this.normalizeImageFulfillmentJobRow(values as Record<string, unknown>),
3289	intents: intentValues.map((intent) =>
3290	this.normalizeImageFulfillmentIntentRow(intent as Record<string, unknown>)
3291	),
3292	created: true
3293	}
3294	})
3295	}
3296
3297	async getImageFulfillmentJob(jobId: string): Promise<ImageFulfillmentJobRecord \| undefined> {
3298	const row = await this.db
3299	.select()
3300	.from(schema.imageFulfillmentJobs)
3301	.where(eq(schema.imageFulfillmentJobs.id, jobId))
3302	.get()
3303	return row ? this.normalizeImageFulfillmentJobRow(row as Record<string, unknown>) : undefined
3304	}
3305
3306	async listImageFulfillmentJobs(
3307	sessionId: string,
3308	sessionPageId?: string
3309	): Promise<ImageFulfillmentJobRecord[]> {
3310	const where = sessionPageId
3311	? and(
3312	eq(schema.imageFulfillmentJobs.sessionId, sessionId),
3313	eq(schema.imageFulfillmentJobs.sessionPageId, sessionPageId)
3314	)
3315	: eq(schema.imageFulfillmentJobs.sessionId, sessionId)
3316	const rows = await this.db
3317	.select()
3318	.from(schema.imageFulfillmentJobs)
3319	.where(where)
3320	.orderBy(desc(schema.imageFulfillmentJobs.createdAt))
3321	.all()
3322	return rows.map((row) => this.normalizeImageFulfillmentJobRow(row as Record<string, unknown>))
3323	}
3324
3325	async listImageFulfillmentIntents(jobId: string): Promise<ImageFulfillmentIntentRecord[]> {
3326	const rows = await this.db
3327	.select()
3328	.from(schema.imageFulfillmentIntents)
3329	.where(eq(schema.imageFulfillmentIntents.jobId, jobId))
3330	.orderBy(asc(schema.imageFulfillmentIntents.createdAt))
3331	.all()
3332	return rows.map((row) =>
3333	this.normalizeImageFulfillmentIntentRow(row as Record<string, unknown>)
3334	)
3335	}
3336
3337	async requestImageFulfillmentCancellation(jobId: string): Promise<boolean> {
3338	const now = Math.floor(Date.now() / 1000)
3339	const result = await this.db
3340	.update(schema.imageFulfillmentJobs)
3341	.set({ cancelRequestedAt: now, updatedAt: now })
3342	.where(
3343	and(
3344	eq(schema.imageFulfillmentJobs.id, jobId),
3345	inArray(schema.imageFulfillmentJobs.status, ['pending', 'running', 'finalizing'])
3346	)
3347	)
3348	.run()
3349	return Number(result.rowsAffected \|\| 0) > 0
3350	}
3351
3352	async claimImageFulfillmentJob(args: {
3353	jobId: string
3354	leaseOwner: string
3355	leaseDurationSec: number
3356	}): Promise<boolean> {
3357	const now = Math.floor(Date.now() / 1000)
3358	const result = await this.db
3359	.update(schema.imageFulfillmentJobs)
3360	.set({
3361	status: 'running',
3362	leaseOwner: args.leaseOwner,
3363	leaseExpiresAt: now + Math.max(1, Math.floor(args.leaseDurationSec)),
3364	startedAt: now,
3365	updatedAt: now
3366	})
3367	.where(
3368	and(
3369	eq(schema.imageFulfillmentJobs.id, args.jobId),
3370	inArray(schema.imageFulfillmentJobs.status, ['pending', 'running']),
3371	isNull(schema.imageFulfillmentJobs.cancelRequestedAt),
3372	or(
3373	isNull(schema.imageFulfillmentJobs.leaseExpiresAt),
3374	lte(schema.imageFulfillmentJobs.leaseExpiresAt, now)
3375	)
3376	)
3377	)
3378	.run()
3379	return Number(result.rowsAffected \|\| 0) > 0
3380	}
3381
3382	async transitionImageFulfillmentJob(args: {
3383	jobId: string
3384	from: ImageFulfillmentJobStatus[]
3385	status: ImageFulfillmentJobStatus
3386	error?: string \| null
3387	finalizationManifestPath?: string \| null
3388	imageProvider?: string \| null
3389	imageModel?: string \| null
3390	leaseOwner?: string \| null
3391	leaseExpiresAt?: number \| null
3392	}): Promise<boolean> {
3393	if (args.from.length === 0) return false
3394	const now = Math.floor(Date.now() / 1000)
3395	const terminal = ['completed', 'degraded', 'failed', 'cancelled'].includes(args.status)
3396	const values: Record<string, unknown> = {
3397	status: args.status,
3398	updatedAt: now
3399	}
3400	if (args.error !== undefined) values.error = args.error
3401	if (args.finalizationManifestPath !== undefined) {
3402	values.finalizationManifestPath = args.finalizationManifestPath
3403	}
3404	if (args.imageProvider !== undefined) values.imageProvider = args.imageProvider
3405	if (args.imageModel !== undefined) values.imageModel = args.imageModel
3406	if (args.leaseOwner !== undefined) values.leaseOwner = args.leaseOwner
3407	if (args.leaseExpiresAt !== undefined) values.leaseExpiresAt = args.leaseExpiresAt
3408	if (terminal) {
3409	values.finishedAt = now
3410	values.leaseOwner = null
3411	values.leaseExpiresAt = null
3412	}
3413	const result = await this.db
3414	.update(schema.imageFulfillmentJobs)
3415	.set(values)
3416	.where(
3417	and(
3418	eq(schema.imageFulfillmentJobs.id, args.jobId),
3419	inArray(schema.imageFulfillmentJobs.status, args.from)
3420	)
3421	)
3422	.run()
3423	return Number(result.rowsAffected \|\| 0) > 0
3424	}
3425
3426	async completeImageFulfillmentJob(args: {
3427	jobId: string
3428	sessionId: string
3429	pageId: string
3430	modelConfigId: string
3431	provider: string
3432	model: string
3433	assets: Array<{
3434	intentId: string
3435	prompt: string
3436	assetPath: string
3437	mimeType: string
3438	width: number
3439	height: number
3440	}>
3441	}): Promise<boolean> {
3442	if (args.assets.length === 0) throw new Error('image fulfillment completion requires assets')
3443	const now = Math.floor(Date.now() / 1000)
3444	return this.db.transaction(async (tx) => {
3445	const job = await tx
3446	.select({ id: schema.imageFulfillmentJobs.id })
3447	.from(schema.imageFulfillmentJobs)
3448	.where(
3449	and(
3450	eq(schema.imageFulfillmentJobs.id, args.jobId),
3451	eq(schema.imageFulfillmentJobs.status, 'finalizing'),
3452	isNull(schema.imageFulfillmentJobs.cancelRequestedAt)
3453	)
3454	)
3455	.get()
3456	if (!job) return false
3457
3458	for (const asset of args.assets) {
3459	const historyId = crypto.randomUUID()
3460	await tx.insert(schema.imageGenerationHistories).values({
3461	id: historyId,
3462	sessionId: args.sessionId,
3463	pageId: args.pageId,
3464	prompt: asset.prompt,
3465	imagePaths: JSON.stringify([asset.assetPath]),
3466	modelConfigId: args.modelConfigId,
3467	provider: args.provider,
3468	model: args.model,
3469	createdAt: now
3470	})
3471	const updatedIntent = await tx
3472	.update(schema.imageFulfillmentIntents)
3473	.set({
3474	status: 'used',
3475	error: null,
3476	imageHistoryId: historyId,
3477	assetPath: asset.assetPath,
3478	width: asset.width,
3479	height: asset.height,
3480	mimeType: asset.mimeType,
3481	updatedAt: now
3482	})
3483	.where(
3484	and(
3485	eq(schema.imageFulfillmentIntents.id, asset.intentId),
3486	eq(schema.imageFulfillmentIntents.jobId, args.jobId),
3487	eq(schema.imageFulfillmentIntents.status, 'generated')
3488	)
3489	)
3490	.run()
3491	if (Number(updatedIntent.rowsAffected \|\| 0) !== 1) {
3492	throw new Error('image fulfillment intent changed before completion')
3493	}
3494	}
3495
3496	const updatedJob = await tx
3497	.update(schema.imageFulfillmentJobs)
3498	.set({
3499	status: 'completed',
3500	error: null,
3501	updatedAt: now,
3502	finishedAt: now,
3503	leaseOwner: null,
3504	leaseExpiresAt: null
3505	})
3506	.where(
3507	and(
3508	eq(schema.imageFulfillmentJobs.id, args.jobId),
3509	eq(schema.imageFulfillmentJobs.status, 'finalizing'),
3510	isNull(schema.imageFulfillmentJobs.cancelRequestedAt)
3511	)
3512	)
3513	.run()
3514	if (Number(updatedJob.rowsAffected \|\| 0) !== 1) {
3515	throw new Error('image fulfillment job changed before completion')
3516	}
3517	return true
3518	})
3519	}
3520
3521	async recoverExpiredImageFulfillmentJobs(args?: {
3522	error?: string
3523	/** Pending jobs only become stale when their creating process has exited. */
3524	includePending?: boolean
3525	}): Promise<ImageFulfillmentJobRecord[]> {
3526	const error = args?.error \|\| 'Image fulfillment lease expired; retry the image stage.'
3527	const includePending = args?.includePending === true
3528	const now = Math.floor(Date.now() / 1000)
3529	const staleRows = await this.db
3530	.select()
3531	.from(schema.imageFulfillmentJobs)
3532	.where(
3533	or(
3534	and(
3535	inArray(schema.imageFulfillmentJobs.status, ['running', 'finalizing']),
3536	lte(schema.imageFulfillmentJobs.leaseExpiresAt, now)
3537	),
3538	includePending ? eq(schema.imageFulfillmentJobs.status, 'pending') : undefined
3539	)
3540	)
3541	.all()
3542	if (staleRows.length === 0) return []
3543	const ids = staleRows.map((row) => row.id)
3544	await this.db
3545	.update(schema.imageFulfillmentJobs)
3546	.set({
3547	status: 'failed',
3548	error,
3549	updatedAt: now,
3550	finishedAt: now,
3551	leaseOwner: null,
3552	leaseExpiresAt: null
3553	})
3554	.where(
3555	and(
3556	inArray(schema.imageFulfillmentJobs.id, ids),
3557	or(
3558	and(
3559	inArray(schema.imageFulfillmentJobs.status, ['running', 'finalizing']),
3560	lte(schema.imageFulfillmentJobs.leaseExpiresAt, now)
3561	),
3562	includePending ? eq(schema.imageFulfillmentJobs.status, 'pending') : undefined
3563	)
3564	)
3565	)
3566	.run()
3567	await this.db
3568	.update(schema.imageFulfillmentIntents)
3569	.set({
3570	status: 'failed',
3571	error,
3572	updatedAt: now
3573	})
3574	.where(
3575	and(
3576	inArray(schema.imageFulfillmentIntents.jobId, ids),
3577	inArray(schema.imageFulfillmentIntents.status, ['pending', 'generating', 'generated'])
3578	)
3579	)
3580	.run()
3581	return staleRows.map((row) =>
3582	this.normalizeImageFulfillmentJobRow(row as Record<string, unknown>)
3583	)
3584	}
3585
3586	async transitionImageFulfillmentIntent(args: {
3587	intentId: string
3588	from: ImageFulfillmentIntentStatus[]
3589	status: ImageFulfillmentIntentStatus
3590	error?: string \| null
3591	imageHistoryId?: string \| null
3592	assetPath?: string \| null
3593	width?: number \| null
3594	height?: number \| null
3595	mimeType?: string \| null
3596	}): Promise<boolean> {
3597	if (args.from.length === 0) return false
3598	const values: Record<string, unknown> = {
3599	status: args.status,
3600	updatedAt: Math.floor(Date.now() / 1000)
3601	}
3602	if (args.error !== undefined) values.error = args.error
3603	if (args.imageHistoryId !== undefined) values.imageHistoryId = args.imageHistoryId
3604	if (args.assetPath !== undefined) values.assetPath = args.assetPath
3605	if (args.width !== undefined) values.width = args.width
3606	if (args.height !== undefined) values.height = args.height
3607	if (args.mimeType !== undefined) values.mimeType = args.mimeType
3608	const result = await this.db
3609	.update(schema.imageFulfillmentIntents)
3610	.set(values)
3611	.where(
3612	and(
3613	eq(schema.imageFulfillmentIntents.id, args.intentId),
3614	inArray(schema.imageFulfillmentIntents.status, args.from)
3615	)
3616	)
3617	.run()
3618	return Number(result.rowsAffected \|\| 0) > 0
3619	}
3620
3621	// ========== Image Generation Histories ==========
3622
3623	async listImageGenerationHistories(
3624	sessionId: string,
3625	pageId: string
3626	): Promise<ImageGenerationHistoryRow[]> {
3627	const results = await this.db
3628	.select()
3629	.from(schema.imageGenerationHistories)
3630	.where(
3631	and(
3632	eq(schema.imageGenerationHistories.sessionId, sessionId),
3633	eq(schema.imageGenerationHistories.pageId, pageId)
3634	)
3635	)
3636	.orderBy(desc(schema.imageGenerationHistories.createdAt))
3637	.limit(50)
3638	.all()
3639	return results as unknown as ImageGenerationHistoryRow[]
3640	}
3641
3642	async insertImageGenerationHistory(data: {
3643	id?: string
3644	sessionId: string
3645	pageId: string
3646	prompt: string
3647	imagePaths: string[]
3648	modelConfigId: string
3649	provider: string
3650	model: string
3651	createdAt?: number
3652	}): Promise<string> {
3653	const id = data.id \|\| crypto.randomUUID()
3654	await this.db
3655	.insert(schema.imageGenerationHistories)
3656	.values({
3657	id,
3658	sessionId: data.sessionId,
3659	pageId: data.pageId,
3660	prompt: data.prompt,
3661	imagePaths: JSON.stringify(data.imagePaths),
3662	modelConfigId: data.modelConfigId,
3663	provider: data.provider,
3664	model: data.model,
3665	createdAt: data.createdAt \|\| Math.floor(Date.now() / 1000)
3666	})
3667	.run()
3668	return id
3669	}
3670
3671	// ========== Preferences ==========
3672
3673	async getActiveUserPreferences(): Promise<UserPreference[]> {
3674	const results = await this.db
3675	.select()
3676	.from(schema.userPreferences)
3677	.where(gt(schema.userPreferences.confidence, 0.3))
3678	.orderBy(desc(schema.userPreferences.confidence), desc(schema.userPreferences.lastUsedAt))
3679	.limit(10)
3680	.all()
3681
3682	return results.map((r) => ({
3683	key: r.key,
3684	value: JSON.parse(r.value),
3685	confidence: r.confidence,
3686	source_sessions: r.sourceSessions ? JSON.parse(r.sourceSessions) : [],
3687	created_at: r.createdAt,
3688	updated_at: r.updatedAt,
3689	last_used_at: r.lastUsedAt
3690	})) as unknown as UserPreference[]
3691	}
3692
3693	async upsertPreference(
3694	key: string,
3695	data: { value: unknown; confidence?: number; sourceSessions?: string[] }
3696	): Promise<void> {
3697	const now = Math.floor(Date.now() / 1000)
3698	const existing = await this.db
3699	.select()
3700	.from(schema.userPreferences)
3701	.where(eq(schema.userPreferences.key, key))
3702	.get()
3703
3704	if (existing) {
3705	const existingSources = existing.sourceSessions ? JSON.parse(existing.sourceSessions) : []
3706	const newSources = data.sourceSessions
3707	? [...new Set([...existingSources, ...data.sourceSessions])]
3708	: existingSources
3709	const baseConfidence = existing.confidence ?? 0.5
3710	const increment = (data.confidence ?? 0.5) * 0.3
3711	const newConfidence = Math.min(1.0, baseConfidence + increment)
3712
3713	await this.db
3714	.update(schema.userPreferences)
3715	.set({
3716	value: JSON.stringify(data.value),
3717	confidence: newConfidence,
3718	sourceSessions: JSON.stringify(newSources),
3719	updatedAt: now,
3720	lastUsedAt: now
3721	})
3722	.where(eq(schema.userPreferences.key, key))
3723	.run()
3724	} else {
3725	await this.db
3726	.insert(schema.userPreferences)
3727	.values({
3728	key,
3729	value: JSON.stringify(data.value),
3730	confidence: data.confidence \|\| 0.5,
3731	sourceSessions: JSON.stringify(data.sourceSessions \|\| []),
3732	createdAt: now,
3733	updatedAt: now,
3734	lastUsedAt: now
3735	})
3736	.run()
3737	}
3738	}
3739
3740	async decayPreferences(): Promise<void> {
3741	await this.db
3742	.update(schema.userPreferences)
3743	.set({ confidence: sql`${schema.userPreferences.confidence} * 0.95` })
3744	.where(gt(schema.userPreferences.confidence, 0.1))
3745	.run()
3746
3747	await this.db
3748	.delete(schema.userPreferences)
3749	.where(lte(schema.userPreferences.confidence, 0.1))
3750	.run()
3751	}
3752
3753	// ========== Projects ==========
3754
3755	async createProject(data: {
3756	session_id: string
3757	title: string
3758	output_path: string
3759	root_path?: string \| null
3760	}): Promise<string> {
3761	const id = crypto.randomUUID()
3762	const now = Math.floor(Date.now() / 1000)
3763
3764	await this.db
3765	.insert(schema.projects)
3766	.values({
3767	id,
3768	sessionId: data.session_id,
3769	title: data.title,
3770	outputPath: data.output_path,
3771	rootPath: data.root_path \|\| data.output_path,
3772	fileCount: 0,
3773	totalSize: 0,
3774	status: 'draft',
3775	createdAt: now,
3776	updatedAt: now
3777	})
3778	.run()
3779
3780	return id
3781	}
3782
3783	async getProject(sessionId: string): Promise<Project \| undefined> {
3784	const row = await this.db
3785	.select({
3786	id: schema.projects.id,
3787	session_id: schema.projects.sessionId,
3788	title: schema.projects.title,
3789	output_path: schema.projects.outputPath,
3790	root_path: schema.projects.rootPath,
3791	file_count: schema.projects.fileCount,
3792	total_size: schema.projects.totalSize,
3793	status: schema.projects.status,
3794	created_at: schema.projects.createdAt,
3795	updated_at: schema.projects.updatedAt
3796	})
3797	.from(schema.projects)
3798	.where(eq(schema.projects.sessionId, sessionId))
3799	.orderBy(desc(schema.projects.createdAt))
3800	.limit(1)
3801	.get()
3802
3803	return row as Project \| undefined
3804	}
3805
3806	async updateProjectStatus(
3807	projectId: string,
3808	status: 'draft' \| 'published' \| 'exported'
3809	): Promise<void> {
3810	const now = Math.floor(Date.now() / 1000)
3811	await this.db
3812	.update(schema.projects)
3813	.set({ status, updatedAt: now })
3814	.where(eq(schema.projects.id, projectId))
3815	.run()
3816	}
3817
3818	// ========== Styles ==========
3819
3820	async countStyles(): Promise<number> {
3821	const result = await this.db.select({ count: count() }).from(schema.styles).get()
3822	return result?.count ?? 0
3823	}
3824
3825	async syncInstalledStylesToDatabase(installedRootPath: string): Promise<void> {
3826	const systemPath = path.join(installedRootPath, 'system')
3827	const userPath = path.join(installedRootPath, 'user')
3828	await this._refreshStylesCache()
3829
3830	const syncDirectory = async (root: string, scope: 'system' \| 'user'): Promise<void> => {
3831	if (!fs.existsSync(root)) return
3832	const packageNames = await listStylePackageDirectories(root)
3833	for (const packageName of packageNames) {
3834	try {
3835	const stylePackage = await readStylePackage(path.join(root, packageName))
3836	const item = stylePackage.json
3837	const existing = this._stylesCache.find((row) => row.style === item.style)
3838	const source: StyleSource =
3839	scope === 'system' ? 'builtin' : item.source === 'override' ? 'override' : 'custom'
3840	const packageDir = path.posix.join(scope, packageName)
3841
3842	if (!existing) {
3843	await this.createStyleRow({
3844	id: scope === 'user' ? packageName : undefined,
3845	style: item.style,
3846	styleName: item.name.zh,
3847	styleNameZh: item.name.zh,
3848	styleNameEn: item.name.en,
3849	description: item.description,
3850	category: item.category,
3851	aliases: item.aliases,
3852	source,
3853	styleSkill: stylePackage.skillMarkdown,
3854	version: item.version,
3855	styleCase: item.styleCase,
3856	imageGenerationPrompt: item.imageGeneration?.prompt \|\| '',
3857	packageDir
3858	})
3859	continue
3860	}
3861
3862	if (scope === 'system') {
3863	if (existing.source === 'builtin') {
3864	await this.updateStyleRow(existing.id, {
3865	styleName: item.name.zh,
3866	styleNameZh: item.name.zh,
3867	styleNameEn: item.name.en,
3868	description: item.description,
3869	category: item.category,
3870	aliases: item.aliases,
3871	styleSkill: stylePackage.skillMarkdown,
3872	version: item.version,
3873	styleCase: item.styleCase,
3874	imageGenerationPrompt: item.imageGeneration?.prompt \|\| '',
3875	packageDir
3876	})
3877	continue
3878	}
3879	if (
3880	existing.source === 'override' &&
3881	compareStyleVersion(item.version, existing.version) > 0
3882	) {
3883	await this.updateStyleRow(existing.id, { version: item.version })
3884	}
3885	continue
3886	}
3887	await this.updateStyleRow(existing.id, {
3888	styleName: item.name.zh,
3889	styleNameZh: item.name.zh,
3890	styleNameEn: item.name.en,
3891	description: item.description,
3892	category: item.category,
3893	aliases: item.aliases,
3894	source,
3895	styleSkill: stylePackage.skillMarkdown,
3896	version: item.version,
3897	styleCase: item.styleCase,
3898	imageGenerationPrompt: item.imageGeneration?.prompt \|\| '',
3899	packageDir
3900	})
3901	} catch (error) {
3902	console.warn('[db] failed to sync installed style package', {
3903	path: path.join(root, packageName),
3904	message: error instanceof Error ? error.message : String(error)
3905	})
3906	}
3907	}
3908	}
3909
3910	await syncDirectory(systemPath, 'system')
3911	await syncDirectory(userPath, 'user')
3912	await this.client.execute(`
3913	UPDATE session_style_snapshots
3914	SET image_generation_prompt = COALESCE((
3915	SELECT styles.image_generation_prompt
3916	FROM styles
3917	WHERE styles.id = session_style_snapshots.style_id
3918	), '')
3919	WHERE COALESCE(image_generation_prompt, '') = ''
3920	`)
3921	await this._refreshStylesCache()
3922	}
3923
3924	private async _refreshStylesCache(): Promise<void> {
3925	const results = await this.db
3926	.select()
3927	.from(schema.styles)
3928	.orderBy(asc(schema.styles.style))
3929	.all()
3930	this._stylesCache = (results as unknown as StyleRow[]).map((row) => ({
3931	...row,
3932	version: normalizeStyleVersion(row.version)
3933	}))
3934	}
3935
3936	/** Synchronous read from in-memory cache. Used by prompt builders. */
3937	listStyleRowsSync(): StyleRow[] {
3938	return this._stylesCache
3939	}
3940
3941	/** Synchronous cache lookup. */
3942	getStyleRowSync(styleId: string): StyleRow \| undefined {
3943	return this._stylesCache.find((r) => r.id === styleId)
3944	}
3945
3946	/** Synchronous cache lookup by style key. */
3947	getStyleRowByStyleSync(style: string): StyleRow \| undefined {
3948	return this._stylesCache.find((r) => r.style === style)
3949	}
3950
3951	async listStyleRows(): Promise<StyleRow[]> {
3952	const results = await this.db
3953	.select()
3954	.from(schema.styles)
3955	.orderBy(asc(schema.styles.style))
3956	.all()
3957	return (results as unknown as StyleRow[]).map((row) => ({
3958	...row,
3959	version: normalizeStyleVersion(row.version)
3960	}))
3961	}
3962
3963	async getStyleRow(styleId: string): Promise<StyleRow \| undefined> {
3964	const result = await this.db
3965	.select()
3966	.from(schema.styles)
3967	.where(eq(schema.styles.id, styleId))
3968	.get()
3969	return result
3970	? ({
3971	...(result as unknown as StyleRow),
3972	version: normalizeStyleVersion((result as unknown as StyleRow).version)
3973	} as StyleRow)
3974	: undefined
3975	}
3976
3977	async getStyleRowByStyle(style: string): Promise<StyleRow \| undefined> {
3978	const result = await this.db
3979	.select()
3980	.from(schema.styles)
3981	.where(eq(schema.styles.style, style))
3982	.get()
3983	return result
3984	? ({
3985	...(result as unknown as StyleRow),
3986	version: normalizeStyleVersion((result as unknown as StyleRow).version)
3987	} as StyleRow)
3988	: undefined
3989	}
3990
3991	async createStyleRow(data: {
3992	id?: string
3993	style: string
3994	styleName: string
3995	styleNameZh?: string
3996	styleNameEn?: string
3997	description?: string
3998	category?: string
3999	aliases?: string[]
4000	source?: StyleSource
4001	styleSkill?: string
4002	version?: string \| number
4003	styleCase?: string
4004	imageGenerationPrompt?: string
4005	packageDir?: string
4006	}): Promise<string> {
4007	const id = data.id \|\| crypto.randomUUID()
4008	const now = Math.floor(Date.now() / 1000)
4009	await this.db
4010	.insert(schema.styles)
4011	.values({
4012	id,
4013	style: data.style,
4014	styleName: data.styleName,
4015	styleNameZh: data.styleNameZh \|\| data.styleName,
4016	styleNameEn: data.styleNameEn \|\| '',
4017	description: data.description \|\| '',
4018	category: data.category \|\| '',
4019	aliases: JSON.stringify(data.aliases \|\| []),
4020	source: data.source \|\| 'custom',
4021	styleSkill: data.styleSkill \|\| '',
4022	version: normalizeStyleVersion(data.version),
4023	styleCase: data.styleCase \|\| '',
4024	imageGenerationPrompt: data.imageGenerationPrompt \|\| '',
4025	packageDir: data.packageDir \|\| '',
4026	createdAt: now,
4027	updatedAt: now
4028	})
4029	.run()
4030	await this._refreshStylesCache()
4031	return id
4032	}
4033
4034	async updateStyleRow(
4035	styleId: string,
4036	data: {
4037	styleName?: string
4038	styleNameZh?: string
4039	styleNameEn?: string
4040	description?: string
4041	category?: string
4042	aliases?: string[]
4043	source?: StyleSource
4044	styleSkill?: string
4045	version?: string \| number
4046	styleCase?: string
4047	imageGenerationPrompt?: string
4048	packageDir?: string
4049	active?: boolean
4050	}
4051	): Promise<void> {
4052	const now = Math.floor(Date.now() / 1000)
4053	const set: Record<string, unknown> = { updatedAt: now }
4054	if (data.styleName !== undefined) set.styleName = data.styleName
4055	if (data.styleNameZh !== undefined) set.styleNameZh = data.styleNameZh
4056	if (data.styleNameEn !== undefined) set.styleNameEn = data.styleNameEn
4057	if (data.description !== undefined) set.description = data.description
4058	if (data.category !== undefined) set.category = data.category
4059	if (data.aliases !== undefined) set.aliases = JSON.stringify(data.aliases)
4060	if (data.source !== undefined) set.source = data.source
4061	if (data.styleSkill !== undefined) set.styleSkill = data.styleSkill
4062	if (data.version !== undefined) set.version = normalizeStyleVersion(data.version)
4063	if (data.styleCase !== undefined) set.styleCase = data.styleCase
4064	if (data.imageGenerationPrompt !== undefined) {
4065	set.imageGenerationPrompt = data.imageGenerationPrompt
4066	}
4067	if (data.packageDir !== undefined) set.packageDir = data.packageDir
4068	if (data.active !== undefined) set.active = data.active
4069	await this.db.update(schema.styles).set(set).where(eq(schema.styles.id, styleId)).run()
4070	await this._refreshStylesCache()
4071	}
4072
4073	async setStyleFavorite(styleId: string, favoriteAt: number \| null): Promise<number \| null> {
4074	const existing = await this.getStyleRow(styleId)
4075	if (!existing) {
4076	throw new Error(`Style not found: ${styleId}`)
4077	}
4078	await this.db
4079	.update(schema.styles)
4080	.set({ favoriteAt })
4081	.where(eq(schema.styles.id, styleId))
4082	.run()
4083	await this._refreshStylesCache()
4084	return favoriteAt
4085	}
4086
4087	async deleteStyleRow(styleId: string): Promise<boolean> {
4088	const existing = await this.getStyleRow(styleId)
4089	if (!existing) return false
4090	await this.db.delete(schema.styles).where(eq(schema.styles.id, styleId)).run()
4091	await this._refreshStylesCache()
4092	return true
4093	}
4094
4095	async getThumbnailRecord(
4096	resourceType: HtmlThumbnailResourceType,
4097	resourceId: string,
4098	variant = 'default'
4099	): Promise<ThumbnailRecord \| undefined> {
4100	const row = await this.db
4101	.select()
4102	.from(schema.thumbnails)
4103	.where(
4104	and(
4105	eq(schema.thumbnails.resourceType, resourceType),
4106	eq(schema.thumbnails.resourceId, resourceId),
4107	eq(schema.thumbnails.variant, variant)
4108	)
4109	)
4110	.get()
4111	return row as ThumbnailRecord \| undefined
4112	}
4113
4114	async getThumbnailRecords(
4115	resourceType: HtmlThumbnailResourceType,
4116	resourceIds: string[],
4117	variant = 'default'
4118	): Promise<ThumbnailRecord[]> {
4119	const ids = Array.from(
4120	new Set(resourceIds.map((id) => String(id \|\| '').trim()).filter(Boolean))
4121	)
4122	if (ids.length === 0) return []
4123	const rows = await this.db
4124	.select()
4125	.from(schema.thumbnails)
4126	.where(
4127	and(
4128	eq(schema.thumbnails.resourceType, resourceType),
4129	inArray(schema.thumbnails.resourceId, ids),
4130	eq(schema.thumbnails.variant, variant)
4131	)
4132	)
4133	.all()
4134	return rows as ThumbnailRecord[]
4135	}
4136
4137	async upsertThumbnailRecord(data: {
4138	resourceType: HtmlThumbnailResourceType
4139	resourceId: string
4140	variant: string
4141	sourcePath: string
4142	sourceMtimeMs: number
4143	signature: string
4144	thumbnailPath: string
4145	status: ThumbnailStatus
4146	error?: string \| null
4147	}): Promise<void> {
4148	const now = Date.now()
4149	const key = crypto
4150	.createHash('sha256')
4151	.update(
4152	JSON.stringify({
4153	resourceType: data.resourceType,
4154	resourceId: data.resourceId,
4155	variant: data.variant
4156	})
4157	)
4158	.digest('hex')
4159	.slice(0, 32)
4160	await this.db
4161	.insert(schema.thumbnails)
4162	.values({
4163	key,
4164	resourceType: data.resourceType,
4165	resourceId: data.resourceId,
4166	variant: data.variant,
4167	sourcePath: data.sourcePath,
4168	sourceMtimeMs: data.sourceMtimeMs,
4169	signature: data.signature,
4170	thumbnailPath: data.thumbnailPath,
4171	status: data.status,
4172	error: data.error \|\| null,
4173	createdAt: now,
4174	updatedAt: now
4175	})
4176	.onConflictDoUpdate({
4177	target: schema.thumbnails.key,
4178	set: {
4179	sourcePath: data.sourcePath,
4180	sourceMtimeMs: data.sourceMtimeMs,
4181	signature: data.signature,
4182	thumbnailPath: data.thumbnailPath,
4183	status: data.status,
4184	error: data.error \|\| null,
4185	updatedAt: now
4186	}
4187	})
4188	.run()
4189	}
4190
4191	async failInterruptedThumbnailTasks(): Promise<void> {
4192	await this.db
4193	.update(schema.thumbnails)
4194	.set({
4195	status: 'failed',
4196	error: '应用退出时任务尚未完成',
4197	updatedAt: Date.now()
4198	})
4199	.where(inArray(schema.thumbnails.status, ['queued', 'running']))
4200	.run()
4201	}
4202
4203	async getSessionStyleSnapshot(sessionId: string): Promise<SessionStyleSnapshotRow \| undefined> {
4204	const row = await this.db
4205	.select()
4206	.from(schema.sessionStyleSnapshots)
4207	.where(eq(schema.sessionStyleSnapshots.sessionId, sessionId))
4208	.get()
4209	return row as unknown as SessionStyleSnapshotRow \| undefined
4210	}
4211
4212	async createSessionStyleSnapshot(
4213	sessionId: string,
4214	styleId?: string \| null
4215	): Promise<SessionStyleSnapshotRow> {
4216	const style = this.resolveSnapshotStyleRow(styleId)
4217	const now = Math.floor(Date.now() / 1000)
4218	await this.db
4219	.insert(schema.sessionStyleSnapshots)
4220	.values({
4221	id: crypto.randomUUID(),
4222	sessionId,
4223	styleId: style.id,
4224	styleKey: style.style,
4225	styleName: style.styleName,
4226	styleNameZh: style.styleNameZh \|\| style.styleName,
4227	styleNameEn: style.styleNameEn \|\| '',
4228	description: style.description,
4229	category: style.category,
4230	aliases: style.aliases \|\| '[]',
4231	source: style.source,
4232	version: normalizeStyleVersion(style.version),
4233	styleCase: style.styleCase,
4234	imageGenerationPrompt: style.imageGenerationPrompt \|\| '',
4235	packageDir: style.packageDir \|\| '',
4236	styleSkill: style.styleSkill,
4237	createdAt: now
4238	})
4239	.onConflictDoNothing({ target: schema.sessionStyleSnapshots.sessionId })
4240	.run()
4241	const existing = await this.getSessionStyleSnapshot(sessionId)
4242	if (!existing) throw new Error('Session style snapshot was not created')
4243	return existing
4244	}
4245
4246	async replaceSessionStyleSnapshot(
4247	sessionId: string,
4248	styleId?: string \| null
4249	): Promise<SessionStyleSnapshotRow> {
4250	await this.db
4251	.delete(schema.sessionStyleSnapshots)
4252	.where(eq(schema.sessionStyleSnapshots.sessionId, sessionId))
4253	.run()
4254	return this.createSessionStyleSnapshot(sessionId, styleId)
4255	}
4256
4257	async getOrCreateSessionStyleSnapshot(sessionId: string): Promise<SessionStyleSnapshotRow> {
4258	const existing = await this.getSessionStyleSnapshot(sessionId)
4259	if (existing) return existing
4260	const session = await this.getSession(sessionId)
4261	return this.createSessionStyleSnapshot(sessionId, session?.styleId)
4262	}
4263
4264	async copySessionStyleSnapshot(sourceSessionId: string, targetSessionId: string): Promise<void> {
4265	const source = await this.getOrCreateSessionStyleSnapshot(sourceSessionId)
4266	await this.db
4267	.delete(schema.sessionStyleSnapshots)
4268	.where(eq(schema.sessionStyleSnapshots.sessionId, targetSessionId))
4269	.run()
4270	await this.db
4271	.insert(schema.sessionStyleSnapshots)
4272	.values({
4273	id: crypto.randomUUID(),
4274	sessionId: targetSessionId,
4275	styleId: source.styleId,
4276	styleKey: source.styleKey,
4277	styleName: source.styleName,
4278	styleNameZh: source.styleNameZh \|\| source.styleName,
4279	styleNameEn: source.styleNameEn \|\| '',
4280	description: source.description,
4281	category: source.category,
4282	aliases: source.aliases,
4283	source: source.source,
4284	version: normalizeStyleVersion(source.version),
4285	styleCase: source.styleCase,
4286	imageGenerationPrompt: source.imageGenerationPrompt \|\| '',
4287	packageDir: source.packageDir \|\| '',
4288	styleSkill: source.styleSkill,
4289	createdAt: Math.floor(Date.now() / 1000)
4290	})
4291	.onConflictDoNothing({ target: schema.sessionStyleSnapshots.sessionId })
4292	.run()
4293	}
4294
4295	async backfillSessionStyleSnapshots(): Promise<{
4296	scanned: number
4297	created: number
4298	fallback: number
4299	failed: number
4300	}> {
4301	const rows = await this.db
4302	.select({ session: schema.sessions })
4303	.from(schema.sessions)
4304	.leftJoin(
4305	schema.sessionStyleSnapshots,
4306	eq(schema.sessionStyleSnapshots.sessionId, schema.sessions.id)
4307	)
4308	.where(isNull(schema.sessionStyleSnapshots.id))
4309	.all()
4310
4311	let created = 0
4312	let fallback = 0
4313	let failed = 0
4314	for (const row of rows) {
4315	const session = row.session as unknown as Session
4316	try {
4317	const snapshot = await this.createSessionStyleSnapshot(session.id, session.styleId)
4318	if (!session.styleId \|\| session.styleId !== snapshot.styleId) {
4319	fallback += 1
4320	await this.updateSessionStyleId(session.id, snapshot.styleId)
4321	}
4322	created += 1
4323	} catch (error) {
4324	failed += 1
4325	console.warn('[db] failed to backfill session style snapshot', {
4326	sessionId: session.id,
4327	message: error instanceof Error ? error.message : String(error)
4328	})
4329	}
4330	}
4331	return { scanned: rows.length, created, fallback, failed }
4332	}
4333
4334	styleRowToPackageJson(styleId: string): ReturnType<typeof styleRowToPackageJson> {
4335	const row = this.getStyleRowSync(styleId)
4336	if (!row) throw new Error('style 不存在：' + styleId)
4337	return styleRowToPackageJson({
4338	style: row.style,
4339	styleName: row.styleName,
4340	styleNameZh: row.styleNameZh \|\| row.styleName,
4341	styleNameEn: row.styleNameEn \|\| '',
4342	description: row.description,
4343	category: row.category,
4344	aliases: row.aliases,
4345	source: row.source,
4346	version: row.version,
4347	styleCase: row.styleCase,
4348	imageGenerationPrompt: row.imageGenerationPrompt
4349	})
4350	}
4351
4352	private resolveSnapshotStyleRow(styleId?: string \| null): StyleRow {
4353	if (styleId) {
4354	const byId = this._stylesCache.find((row) => row.id === styleId)
4355	if (byId) return byId
4356	const byStyle = this._stylesCache.find((row) => row.style === styleId)
4357	if (byStyle) return byStyle
4358	}
4359	const activeRows = this._stylesCache.filter((row) => row.active !== false)
4360	const fallback =
4361	activeRows.find((row) => row.style === 'minimal-white') \|\|
4362	this._stylesCache.find((row) => row.style === 'minimal-white') \|\|
4363	activeRows[0] \|\|
4364	this._stylesCache[0]
4365	if (!fallback) throw new Error('No style rows available for session snapshot')
4366	return fallback
4367	}
4368	}
4369

4369 lines TYPESCRIPT