1/**

2 * Schema + open-time helpers for the SQLite storage backend: the physical

3 * layout version, the database open/configure sequence (permissions, pragmas,

4 * version stamp/reject), and the unit metadata tables. Unit record tables are

5 * created per descriptor in `unit.ts`.

6 * @module @deepseek-ai/dsh-storage-sqlite/schema

7 */

8

9import { DatabaseSync } from 'node:sqlite'

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

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

12import { StorageError } from '@deepseek-ai/dsh-storage'

13

14/**

15 * The on-disk physical layout version, stored in `PRAGMA user_version`.

16 * Orthogonal to each unit's own `version` (stamped per unit in the `units`

17 * row). Bumped only on a breaking change to the table layout; any other

18 * stamped version rejects — this unreleased format has no migrations.

19 */

20export const STORAGE_SQLITE_SCHEMA_VERSION = 1

21

22/**

23 * Journal modes the backend will run under. `wal` is the default; the

24 * rollback-journal modes (`delete`/`truncate`/`persist`) exist for

25 * filesystems where WAL's shared-memory files do not work (network mounts).

26 * `memory`/`off` are excluded: dropping journal durability silently

27 * contradicts the durability clause of the KV backend contract.

28 */

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

30

31/* jscpd:ignore-start -- deliberately mirrors the session-query-sqlite open

32 sequence. Each package owns a distinct database identity and schema, so a

33 shared helper would couple otherwise independent storage providers (see the

34 domain KV storage Agent Note's reuse audit). */

35/**

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

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

38 * `DatabaseSync` reopens by path, so this does not protect confidentiality or

39 * integrity when another principal can replace the database entry in its

40 * parent directory.

41 */

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

43 try {

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

45 await handle.close()

46 } catch (error) {

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

48 }

49}

50

51/**

52 * Open the database and apply its schema and pragmas. Missing directories and

53 * database files are created owner-only (`:memory:` skips filesystem setup).

54 * A zero `user_version` is stamped with {@link STORAGE_SQLITE_SCHEMA_VERSION};

55 * every other non-current version rejects rather than being migrated in place.

56 * @param path - the SQLite database file to open, or `:memory:`.

57 * @param journalMode - validated journal pragma.

58 * @returns the open handle with pragmas applied and the unit metadata tables ensured.

59 */

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

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

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

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

64 await createDatabaseFile(actual)

65 }

66 const db = new DatabaseSync(actual)

67 try {

68 configureDatabase(db, actual, journalMode)

69 return db

70 } catch (error: unknown) {

71 db.close()

72 throw error

73 }

74}

75

76function configureDatabase(db: DatabaseSync, path: string, journalMode: JournalMode): void {

77 db.exec('PRAGMA foreign_keys = ON')

78 // The validated union is safe to interpolate into a non-bindable PRAGMA.

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

80 // `PRAGMA user_version` always returns exactly one row { user_version }.

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

82 if (onDisk !== 0 && onDisk !== STORAGE_SQLITE_SCHEMA_VERSION) {

83 throw new StorageError(

84 'version-mismatch',

85 `storage database at "${path}" has schema version ${onDisk}, incompatible with this build (${STORAGE_SQLITE_SCHEMA_VERSION})`,

86 )

87 }

88 /* jscpd:ignore-end */

89 db.exec(`

90 CREATE TABLE IF NOT EXISTS units (

91 name TEXT PRIMARY KEY,

92 version INTEGER NOT NULL

93 ) STRICT

94 `)

95 db.exec(`

96 CREATE TABLE IF NOT EXISTS unit_globals (

97 unit TEXT PRIMARY KEY REFERENCES units(name),

98 value TEXT NOT NULL

99 ) STRICT

100 `)

101 if (onDisk === 0) {

102 // Stamp fresh databases LAST: the stamp asserts the layout is complete,

103 // so a failure above must leave the medium unstamped (a re-open after

104 // the obstruction is cleared retries materialization from scratch).

105 db.exec(`PRAGMA user_version = ${STORAGE_SQLITE_SCHEMA_VERSION}`)

106 }

107}

108

109/**

110 * Physical table name for one unit table. Both segments are validated against

111 * `UNIT_NAME_RE` before reaching this, so the result is safe to interpolate

112 * into DDL and prepared-statement text.

113 * @param unit - Validated unit name.

114 * @param table - Validated table name.

115 * @returns the `u_<unit>_<table>` identifier.

116 */

117export function recordTableName(unit: string, table: string): string {

118 return `u_${unit}_${table}`

119}