hotsocket.fyi
This repository has no description
1import Database from "better-sqlite3"
2import fs from "fs"
3import path from "path"
4import * as sqliteVec from "sqlite-vec"
5import { HOME_DIR } from "./container/config"
6
7const DB_PATH = path.join(HOME_DIR, "niri.db")
8export const MEMORY_EMBEDDING_DIMENSIONS = 3072
9
10let db: Database.Database
11let vecAvailable = false
12
13function ensureWritableDirOrThrow(dirPath: string, purpose: string): void {
14 try {
15 fs.mkdirSync(dirPath, { recursive: true })
16 fs.accessSync(dirPath, fs.constants.W_OK)
17 } catch (err: any) {
18 let owner = "unknown"
19 try {
20 const st = fs.statSync(dirPath)
21 owner = `${st.uid}:${st.gid}`
22 } catch {
23 // ignore
24 }
25
26 const uid = typeof process.getuid === "function" ? process.getuid() : undefined
27 const gid = typeof process.getgid === "function" ? process.getgid() : undefined
28 const who = uid !== undefined && gid !== undefined ? `${uid}:${gid}` : "current user"
29
30 throw new Error(
31 [
32 `[db] cannot write ${purpose} under ${dirPath}`,
33 `- dir owner: ${owner}`,
34 `- process uid:gid: ${who}`,
35 "",
36 "Fix:",
37 "- If running locally: `sudo chown -R $(id -u):$(id -g) home`",
38 "- If running via docker-compose: set `AGENT_UID`/`AGENT_GID` in .env to match `id -u`/`id -g`, then recreate the container",
39 "",
40 `Original error: ${err?.message ?? String(err)}`,
41 ].join("\n"),
42 )
43 }
44}
45
46export function initDb(): void {
47 ensureWritableDirOrThrow(HOME_DIR, "niri.db")
48 db = new Database(DB_PATH)
49
50 db.pragma("journal_mode = WAL")
51 db.pragma("foreign_keys = ON")
52 try {
53 sqliteVec.load(db)
54 vecAvailable = true
55 } catch (err: any) {
56 vecAvailable = false
57 console.warn(`[db] sqlite-vec unavailable: ${err?.message ?? String(err)}`)
58 }
59
60 db.exec(`
61 create table if not exists conversations (
62 id integer primary key autoincrement,
63 startedAt text not null,
64 source text not null,
65 tokens integer not null default 0
66 );
67
68 create table if not exists messages (
69 id integer primary key autoincrement,
70 convId integer not null references conversations(id),
71 role text not null,
72 content text not null,
73 toolCalls text, -- json blob, null if none
74 toolCallId text, -- for role=tool responses
75 createdAt text not null default (datetime('now'))
76 );
77
78 create table if not exists discord_messages (
79 message_id text primary key,
80 channel_id text not null,
81 guild_id text,
82 channel_type integer,
83 author_id text,
84 author_username text,
85 content text not null default '',
86 created_at text not null,
87 is_dm integer not null default 0,
88 mentions_bot integer not null default 0,
89 is_from_bot integer not null default 0,
90 first_seen_at text not null,
91 last_seen_at text not null,
92 raw_json text not null
93 );
94
95 create index if not exists idx_discord_messages_channel
96 on discord_messages(channel_id, message_id desc);
97 create index if not exists idx_discord_messages_created
98 on discord_messages(created_at desc);
99
100 create table if not exists discord_items (
101 item_id text primary key,
102 message_id text not null references discord_messages(message_id) on delete cascade,
103 bucket text not null,
104 status text not null default 'pending',
105 action_taken text not null default 'none',
106 decision_note text,
107 first_seen_at text not null,
108 last_seen_at text not null,
109 last_decision_at text
110 );
111
112 create index if not exists idx_discord_items_status
113 on discord_items(status, last_seen_at desc);
114 create index if not exists idx_discord_items_message
115 on discord_items(message_id);
116
117 create table if not exists discord_channels (
118 channel_id text primary key,
119 guild_id text,
120 channel_type integer,
121 channel_name text,
122 guild_name text,
123 topic text,
124 is_dm integer not null default 0,
125 configured integer not null default 0,
126 note text,
127 last_note_at text,
128 first_seen_at text not null,
129 last_seen_at text not null,
130 raw_json text not null
131 );
132
133 create index if not exists idx_discord_channels_configured
134 on discord_channels(configured, guild_name, channel_name);
135 create index if not exists idx_discord_channels_last_seen
136 on discord_channels(last_seen_at desc);
137
138 create table if not exists discord_meta (
139 key text primary key,
140 value text not null,
141 updated_at text not null
142 );
143
144 create table if not exists memory_documents (
145 id integer primary key autoincrement,
146 path text not null unique,
147 kind text not null,
148 title text not null,
149 mtime_ms integer not null,
150 content_hash text not null,
151 updated_at text not null default (datetime('now'))
152 );
153
154 create index if not exists idx_memory_documents_kind
155 on memory_documents(kind, path);
156
157 create table if not exists memory_chunks (
158 id integer primary key autoincrement,
159 document_id integer not null references memory_documents(id) on delete cascade,
160 chunk_index integer not null,
161 title text not null,
162 heading_path text,
163 chunk_text text not null,
164 tags text,
165 created_at text not null default (datetime('now')),
166 unique(document_id, chunk_index)
167 );
168
169 create index if not exists idx_memory_chunks_document
170 on memory_chunks(document_id, chunk_index);
171
172 create virtual table if not exists memory_chunks_fts using fts5(
173 title,
174 heading_path,
175 chunk_text,
176 tags,
177 content='memory_chunks',
178 content_rowid='id',
179 tokenize='porter unicode61'
180 );
181
182 create trigger if not exists memory_chunks_ai after insert on memory_chunks begin
183 insert into memory_chunks_fts(rowid, title, heading_path, chunk_text, tags)
184 values (new.id, new.title, new.heading_path, new.chunk_text, new.tags);
185 end;
186
187 create trigger if not exists memory_chunks_ad after delete on memory_chunks begin
188 insert into memory_chunks_fts(memory_chunks_fts, rowid, title, heading_path, chunk_text, tags)
189 values ('delete', old.id, old.title, old.heading_path, old.chunk_text, old.tags);
190 end;
191
192 create trigger if not exists memory_chunks_au after update on memory_chunks begin
193 insert into memory_chunks_fts(memory_chunks_fts, rowid, title, heading_path, chunk_text, tags)
194 values ('delete', old.id, old.title, old.heading_path, old.chunk_text, old.tags);
195 insert into memory_chunks_fts(rowid, title, heading_path, chunk_text, tags)
196 values (new.id, new.title, new.heading_path, new.chunk_text, new.tags);
197 end;
198
199 create table if not exists memory_embedding_meta (
200 chunk_id integer primary key references memory_chunks(id) on delete cascade,
201 model text not null,
202 dimensions integer not null,
203 content_hash text not null,
204 updated_at text not null default (datetime('now'))
205 );
206
207 create index if not exists idx_memory_embedding_meta_model
208 on memory_embedding_meta(model, dimensions);
209
210 create table if not exists memory_embedding_prototypes (
211 id integer primary key,
212 name text not null unique,
213 category text not null,
214 model text not null,
215 dimensions integer not null,
216 content_hash text not null,
217 updated_at text not null default (datetime('now'))
218 );
219 `)
220
221 if (vecAvailable) {
222 db.exec(`
223 create virtual table if not exists memory_chunk_vec using vec0(
224 embedding float[${MEMORY_EMBEDDING_DIMENSIONS}] distance_metric=cosine
225 );
226
227 create virtual table if not exists memory_prototype_vec using vec0(
228 embedding float[${MEMORY_EMBEDDING_DIMENSIONS}] distance_metric=cosine
229 );
230 `)
231 }
232
233 console.log("[db] ready")
234}
235
236export function startConversation(source: string, startedAt: string): number {
237 const stmt = db.prepare("insert into conversations (startedAt, source) values (?, ?)")
238 const result = stmt.run(startedAt, source)
239 return result.lastInsertRowid as number
240}
241
242export function logMessage(
243 convId: number,
244 role: string,
245 content: string,
246 toolCalls?: unknown,
247 toolCallId?: string,
248): void {
249 const stmt = db.prepare(
250 "insert into messages (convId, role, content, toolCalls, toolCallId) values (?, ?, ?, ?, ?)",
251 )
252 stmt.run(
253 convId,
254 role,
255 content,
256 toolCalls ? JSON.stringify(toolCalls) : null,
257 toolCallId ?? null,
258 )
259}
260
261export function endConversation(id: number, tokens: number): void {
262 db.prepare("update conversations set tokens = ? where id = ?").run(tokens, id)
263}
264
265export function getDb(): Database.Database {
266 if (!db) throw new Error("Database not initialized")
267 return db
268}
269
270export function isVecAvailable(): boolean {
271 return vecAvailable
272}