experiments in a post-browser web
burrito.space
1/**
2 * better-sqlite3 Storage Adapter
3 *
4 * Implements the StorageAdapter interface using a better-sqlite3 Database instance.
5 * Works with the canonical camelCase schema.
6 *
7 * Usage:
8 * import Database from 'better-sqlite3';
9 * import { createBetterSqliteAdapter } from './adapters/better-sqlite3.js';
10 *
11 * const db = new Database(':memory:');
12 * const adapter = createBetterSqliteAdapter(db);
13 * await adapter.open();
14 */
15
16/**
17 * Create a better-sqlite3 adapter wrapping an existing Database instance.
18 * The caller owns the database lifecycle (opening, closing the file).
19 *
20 * @param {import('better-sqlite3').Database} db
21 * @returns {import('./interface.js').StorageAdapter}
22 */
23export function createBetterSqliteAdapter(db) {
24 let stmts = {};
25
26 function ensureSchema() {
27 db.exec(`
28 CREATE TABLE IF NOT EXISTS items (
29 id TEXT PRIMARY KEY,
30 type TEXT NOT NULL CHECK(type IN ('url', 'text', 'tagset', 'image')),
31 content TEXT,
32 metadata TEXT,
33 syncId TEXT DEFAULT '',
34 syncSource TEXT DEFAULT '',
35 syncedAt INTEGER DEFAULT 0,
36 createdAt INTEGER NOT NULL,
37 updatedAt INTEGER NOT NULL,
38 deletedAt INTEGER DEFAULT 0
39 );
40 CREATE INDEX IF NOT EXISTS idx_items_type ON items(type);
41 CREATE INDEX IF NOT EXISTS idx_items_syncId ON items(syncId);
42 CREATE INDEX IF NOT EXISTS idx_items_deletedAt ON items(deletedAt);
43
44 CREATE TABLE IF NOT EXISTS tags (
45 id TEXT PRIMARY KEY,
46 name TEXT NOT NULL UNIQUE,
47 frequency INTEGER DEFAULT 1,
48 lastUsed INTEGER NOT NULL,
49 frecencyScore REAL DEFAULT 0.0,
50 createdAt INTEGER NOT NULL,
51 updatedAt INTEGER NOT NULL
52 );
53 CREATE INDEX IF NOT EXISTS idx_tags_name ON tags(name);
54 CREATE INDEX IF NOT EXISTS idx_tags_frecency ON tags(frecencyScore DESC);
55
56 CREATE TABLE IF NOT EXISTS item_tags (
57 itemId TEXT NOT NULL,
58 tagId TEXT NOT NULL,
59 createdAt INTEGER NOT NULL,
60 PRIMARY KEY (itemId, tagId)
61 );
62 CREATE INDEX IF NOT EXISTS idx_item_tags_itemId ON item_tags(itemId);
63 CREATE INDEX IF NOT EXISTS idx_item_tags_tagId ON item_tags(tagId);
64
65 CREATE TABLE IF NOT EXISTS settings (
66 key TEXT PRIMARY KEY,
67 value TEXT
68 );
69 `);
70
71 // Add missing columns to tags if they exist from a prior schema
72 const tagCols = db.prepare('PRAGMA table_info(tags)').all();
73 const tagColNames = new Set(tagCols.map(c => c.name));
74 if (!tagColNames.has('frequency')) {
75 db.exec('ALTER TABLE tags ADD COLUMN frequency INTEGER DEFAULT 1');
76 }
77 if (!tagColNames.has('lastUsed')) {
78 db.exec('ALTER TABLE tags ADD COLUMN lastUsed INTEGER DEFAULT 0');
79 }
80 if (!tagColNames.has('frecencyScore')) {
81 db.exec('ALTER TABLE tags ADD COLUMN frecencyScore REAL DEFAULT 0.0');
82 }
83 if (!tagColNames.has('updatedAt')) {
84 db.exec('ALTER TABLE tags ADD COLUMN updatedAt INTEGER DEFAULT 0');
85 }
86
87 // Add missing sync columns to items if they exist from a prior schema
88 const itemCols = db.prepare('PRAGMA table_info(items)').all();
89 const itemColNames = new Set(itemCols.map(c => c.name));
90 if (!itemColNames.has('syncId')) {
91 db.exec("ALTER TABLE items ADD COLUMN syncId TEXT DEFAULT ''");
92 }
93 if (!itemColNames.has('syncSource')) {
94 db.exec("ALTER TABLE items ADD COLUMN syncSource TEXT DEFAULT ''");
95 }
96 if (!itemColNames.has('syncedAt')) {
97 db.exec('ALTER TABLE items ADD COLUMN syncedAt INTEGER DEFAULT 0');
98 }
99 }
100
101 function prepareStatements() {
102 stmts = {
103 getItem: db.prepare('SELECT * FROM items WHERE id = ? AND deletedAt = 0'),
104 getItemIncludeDeleted: db.prepare('SELECT * FROM items WHERE id = ?'),
105 insertItem: db.prepare(`
106 INSERT INTO items (id, type, content, metadata, syncId, syncSource, syncedAt, createdAt, updatedAt, deletedAt)
107 VALUES (@id, @type, @content, @metadata, @syncId, @syncSource, @syncedAt, @createdAt, @updatedAt, @deletedAt)
108 `),
109 deleteItemSoft: db.prepare('UPDATE items SET deletedAt = @deletedAt, updatedAt = @updatedAt WHERE id = @id AND deletedAt = 0'),
110 hardDeleteItem: db.prepare('DELETE FROM items WHERE id = ?'),
111 hardDeleteItemTags: db.prepare('DELETE FROM item_tags WHERE itemId = ?'),
112
113 getTagById: db.prepare('SELECT * FROM tags WHERE id = ?'),
114 getTagByName: db.prepare('SELECT * FROM tags WHERE LOWER(name) = LOWER(?)'),
115 insertTag: db.prepare(`
116 INSERT INTO tags (id, name, frequency, lastUsed, frecencyScore, createdAt, updatedAt)
117 VALUES (@id, @name, @frequency, @lastUsed, @frecencyScore, @createdAt, @updatedAt)
118 `),
119
120 getItemTags: db.prepare(`
121 SELECT t.* FROM tags t
122 JOIN item_tags it ON t.id = it.tagId
123 WHERE it.itemId = ?
124 `),
125 getItemsByTag: db.prepare(`
126 SELECT i.* FROM items i
127 JOIN item_tags it ON i.id = it.itemId
128 WHERE it.tagId = ? AND i.deletedAt = 0
129 `),
130 tagItem: db.prepare('INSERT OR IGNORE INTO item_tags (itemId, tagId, createdAt) VALUES (?, ?, ?)'),
131 untagItem: db.prepare('DELETE FROM item_tags WHERE itemId = ? AND tagId = ?'),
132 clearItemTags: db.prepare('DELETE FROM item_tags WHERE itemId = ?'),
133
134 getSetting: db.prepare('SELECT value FROM settings WHERE key = ?'),
135 setSetting: db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)'),
136
137 findBySyncId: db.prepare('SELECT * FROM items WHERE id = ?'),
138 findBySyncIdField: db.prepare('SELECT * FROM items WHERE syncId = ?'),
139
140 getAllTags: db.prepare('SELECT * FROM tags'),
141 };
142 }
143
144 return {
145 // ==================== Lifecycle ====================
146
147 async open() {
148 ensureSchema();
149 prepareStatements();
150 },
151
152 async close() {
153 // Caller owns the db — we just clear prepared statements
154 stmts = {};
155 },
156
157 // ==================== Items ====================
158
159 async getItem(id) {
160 return stmts.getItem.get(id) || null;
161 },
162
163 async getItems(filter = {}) {
164 let sql = 'SELECT * FROM items WHERE 1=1';
165 const params = [];
166
167 if (!filter.includeDeleted) {
168 sql += ' AND deletedAt = 0';
169 }
170 if (filter.type) {
171 sql += ' AND type = ?';
172 params.push(filter.type);
173 }
174 if (filter.since) {
175 sql += ' AND updatedAt > ?';
176 params.push(filter.since);
177 }
178
179 sql += ' ORDER BY createdAt DESC';
180 return db.prepare(sql).all(...params);
181 },
182
183 async insertItem(item) {
184 stmts.insertItem.run(item);
185 },
186
187 async updateItem(id, fields) {
188 const sets = [];
189 const values = [];
190 for (const [key, value] of Object.entries(fields)) {
191 if (value !== undefined) {
192 sets.push(`${key} = ?`);
193 values.push(value);
194 }
195 }
196 if (sets.length === 0) return;
197 values.push(id);
198 db.prepare(`UPDATE items SET ${sets.join(', ')} WHERE id = ?`).run(...values);
199 },
200
201 async deleteItem(id) {
202 const timestamp = Date.now();
203 stmts.deleteItemSoft.run({ id, deletedAt: timestamp, updatedAt: timestamp });
204 },
205
206 async hardDeleteItem(id) {
207 stmts.hardDeleteItemTags.run(id);
208 stmts.hardDeleteItem.run(id);
209 },
210
211 // ==================== Tags ====================
212
213 async getTag(id) {
214 return stmts.getTagById.get(id) || null;
215 },
216
217 async getTagByName(name) {
218 return stmts.getTagByName.get(name) || null;
219 },
220
221 async insertTag(tag) {
222 stmts.insertTag.run(tag);
223 },
224
225 async updateTag(id, fields) {
226 const sets = [];
227 const values = [];
228 for (const [key, value] of Object.entries(fields)) {
229 if (value !== undefined) {
230 sets.push(`${key} = ?`);
231 values.push(value);
232 }
233 }
234 if (sets.length === 0) return;
235 values.push(id);
236 db.prepare(`UPDATE tags SET ${sets.join(', ')} WHERE id = ?`).run(...values);
237 },
238
239 // ==================== Item-Tags ====================
240
241 async getItemTags(itemId) {
242 return stmts.getItemTags.all(itemId);
243 },
244
245 async getItemsByTag(tagId) {
246 return stmts.getItemsByTag.all(tagId);
247 },
248
249 async tagItem(itemId, tagId) {
250 stmts.tagItem.run(itemId, tagId, Date.now());
251 },
252
253 async untagItem(itemId, tagId) {
254 stmts.untagItem.run(itemId, tagId);
255 },
256
257 async clearItemTags(itemId) {
258 stmts.clearItemTags.run(itemId);
259 },
260
261 // ==================== Settings ====================
262
263 async getSetting(key) {
264 const row = stmts.getSetting.get(key);
265 return row ? row.value : null;
266 },
267
268 async setSetting(key, value) {
269 stmts.setSetting.run(key, value);
270 },
271
272 // ==================== Query Helpers ====================
273
274 async findItemBySyncId(syncId) {
275 // Check by direct ID first
276 const byId = stmts.findBySyncId.get(syncId);
277 if (byId) return byId;
278
279 // Check by syncId field
280 const bySyncField = stmts.findBySyncIdField.get(syncId);
281 return bySyncField || null;
282 },
283
284 async getAllTags() {
285 return stmts.getAllTags.all();
286 },
287 };
288}