1/** SQLite schema for the disposable session full-text read model. */

2

3import type { DatabaseSync } from 'node:sqlite'

4import { mkdir, open } from 'node:fs/promises'

5import { dirname, resolve } from 'node:path'

6

7/** Current derived-index schema version. Incompatible versions reset in place. */

8export const SESSION_QUERY_SQLITE_SCHEMA_VERSION = 8

9

10/** SQLite application id protecting unrelated databases from derived resets. */

11export const SESSION_QUERY_SQLITE_APPLICATION_ID = 0x44534851

12

13/** Supported SQLite journal modes. */

14export type JournalMode = 'wal' | 'delete' | 'truncate' | 'persist'

15

16const DERIVED_USER_TABLES = new Set([

17 'search_state',

18 'persisted_sessions',

19 'persisted_docs',

20 'persisted_docs_data',

21 'persisted_docs_idx',

22 'persisted_docs_content',

23 'persisted_docs_docsize',

24 'persisted_docs_config',

25])

26

27/**

28 * Exclusively create a missing database file with owner-only permissions.

29 * Existing files retain their modes, and errors other than `EEXIST` propagate.

30 */

31async function createDatabaseFile(path: string): Promise<void> {

32 try {

33 const handle = await open(path, 'wx', 0o600)

34 await handle.close()

35 } catch (error) {

36 if ((error as NodeJS.ErrnoException).code !== 'EEXIST') throw error

37 }

38}

39

40/**

41 * Open, validate, and initialize persistent and connection-local schemas.

42 * @param path - dedicated derived-index path or `:memory:`; missing filesystem paths are created owner-only.

43 * @param journalMode - validated SQLite journal mode.

44 * @returns initialized database handle owned by the search service.

45 */

46export async function openSearchDatabase(path: string, journalMode: JournalMode): Promise<DatabaseSync> {

47 const actual = path === ':memory:' ? path : resolve(path)

48 if (actual !== ':memory:') {

49 await mkdir(dirname(actual), { recursive: true, mode: 0o700 })

50 await createDatabaseFile(actual)

51 }

52 const { DatabaseSync } = await import('node:sqlite')

53 const db = new DatabaseSync(actual)

54 try {

55 const { application_id: applicationId } = db.prepare('PRAGMA application_id').get() as { application_id: number }

56 const { user_version: version } = db.prepare('PRAGMA user_version').get() as { user_version: number }

57 const userTables = listUserTables(db)

58 if (applicationId !== 0 && applicationId !== SESSION_QUERY_SQLITE_APPLICATION_ID) {

59 throw new Error(`session-search database at "${actual}" belongs to another application`)

60 }

61 if (applicationId === 0 && userTables.length > 0) {

62 throw new Error(`session-search database at "${actual}" is not an empty or recognized derived index`)

63 }

64 if (applicationId === SESSION_QUERY_SQLITE_APPLICATION_ID) {

65 assertDerivedUserTables(actual, userTables)

66 if (version !== SESSION_QUERY_SQLITE_SCHEMA_VERSION) resetDerivedSchema(db, userTables)

67 }

68 // Apply mutating pragmas only after refusing foreign or canonical files.

69 // journalMode is a validated closed union, not caller-controlled SQL.

70 db.exec(`PRAGMA journal_mode = ${journalMode.toUpperCase()}`)

71 ensurePersistentSchema(db)

72 ensureTemporarySchema(db)

73 return db

74 } catch (error: unknown) {

75 db.close()

76 throw error

77 }

78}

79

80function listUserTables(db: DatabaseSync): string[] {

81 const rows = db.prepare(

82 "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT GLOB 'sqlite_*' ORDER BY name",

83 ).all() as Array<{ name: string }>

84 return rows.map(row => row.name)

85}

86

87function assertDerivedUserTables(path: string, userTables: readonly string[]): void {

88 const unknownTables = userTables.filter(name => !DERIVED_USER_TABLES.has(name))

89 if (unknownTables.length > 0) {

90 throw new Error(

91 `session-search database at "${path}" has unrecognized user tables: ${unknownTables.join(', ')}`,

92 )

93 }

94}

95

96function resetDerivedSchema(db: DatabaseSync, userTables: readonly string[]): void {

97 for (const name of userTables) {

98 db.exec(`DROP TABLE IF EXISTS ${quoteIdentifier(name)}`)

99 }

100 db.exec('PRAGMA user_version = 0')

101}

102

103function ensurePersistentSchema(db: DatabaseSync): void {

104 db.exec(`PRAGMA application_id = ${SESSION_QUERY_SQLITE_APPLICATION_ID}`)

105 db.exec(`

106 CREATE TABLE IF NOT EXISTS search_state (

107 singleton INTEGER PRIMARY KEY CHECK (singleton = 1),

108 global_generation INTEGER NOT NULL

109 ) STRICT

110 `)

111 db.exec('INSERT OR IGNORE INTO search_state (singleton, global_generation) VALUES (1, 0)')

112 db.exec(`

113 CREATE TABLE IF NOT EXISTS persisted_sessions (

114 id TEXT PRIMARY KEY,

115 version INTEGER NOT NULL,

116 created_at INTEGER NOT NULL,

117 cwd TEXT,

118 parent_session TEXT,

119 seed_length INTEGER,

120 delegation_depth INTEGER,

121 agent_preset TEXT,

122 revision TEXT NOT NULL,

123 generation INTEGER NOT NULL

124 ) STRICT

125 `)

126 db.exec(`

127 CREATE VIRTUAL TABLE IF NOT EXISTS persisted_docs USING fts5(

128 text,

129 session_id UNINDEXED,

130 seq UNINDEXED,

131 type UNINDEXED,

132 time UNINDEXED,

133 surface UNINDEXED,

134 codepoint_length UNINDEXED,

135 tokenize = 'unicode61'

136 )

137 `)

138 db.exec(`PRAGMA user_version = ${SESSION_QUERY_SQLITE_SCHEMA_VERSION}`)

139}

140

141function ensureTemporarySchema(db: DatabaseSync): void {

142 db.exec(`

143 CREATE TEMP TABLE IF NOT EXISTS live_sessions (

144 id TEXT PRIMARY KEY,

145 version INTEGER NOT NULL,

146 created_at INTEGER NOT NULL,

147 cwd TEXT,

148 parent_session TEXT,

149 seed_length INTEGER,

150 delegation_depth INTEGER,

151 agent_preset TEXT,

152 fingerprint TEXT NOT NULL,

153 persisted INTEGER NOT NULL CHECK (persisted IN (0, 1)),

154 generation INTEGER NOT NULL

155 ) STRICT

156 `)

157 db.exec(`

158 CREATE VIRTUAL TABLE IF NOT EXISTS temp.live_docs USING fts5(

159 text,

160 session_id UNINDEXED,

161 seq UNINDEXED,

162 type UNINDEXED,

163 time UNINDEXED,

164 surface UNINDEXED,

165 codepoint_length UNINDEXED,

166 tokenize = 'unicode61'

167 )

168 `)

169}

170

171function quoteIdentifier(value: string): string {

172 return `"${value.replaceAll('"', '""')}"`

173}