1// SPDX-FileCopyrightText: Amolith <amolith@secluded.site>
2//
3// SPDX-License-Identifier: AGPL-3.0-or-later
4
5import { serve } from "https://deno.land/std@0.208.0/http/server.ts";
6import { Database } from "https://deno.land/x/sqlite3@0.11.1/mod.ts";
7
8const db = new Database("lists.db");
9
10// Enable foreign key constraints
11db.exec("PRAGMA foreign_keys = ON;");
12
13// Check schema version and migrate if needed
14const currentVersion = (db.prepare('PRAGMA user_version').get() as { user_version: number }).user_version;
15
16if (currentVersion < 1) {
17 console.log('🔄 Migrating database schema to version 1...');
18
19 try {
20 db.exec('BEGIN');
21
22 // Temporarily disable foreign keys for migration
23 db.exec('PRAGMA foreign_keys = OFF');
24
25 // Create new tables with constraints
26 db.exec(`
27 CREATE TABLE rooms_new (
28 code TEXT PRIMARY KEY NOT NULL,
29 created_at INTEGER DEFAULT (unixepoch())
30 );
31
32 CREATE TABLE items_new (
33 id TEXT PRIMARY KEY NOT NULL,
34 room_code TEXT NOT NULL,
35 text TEXT NOT NULL CHECK(LENGTH(text) BETWEEN 1 AND 200),
36 created_at INTEGER DEFAULT (unixepoch()),
37 FOREIGN KEY (room_code) REFERENCES rooms_new(code) ON DELETE CASCADE
38 );
39
40 CREATE TABLE votes_new (
41 item_id TEXT NOT NULL,
42 user_id TEXT NOT NULL,
43 vote_type TEXT NOT NULL CHECK(vote_type IN ('up', 'down', 'veto')),
44 created_at INTEGER DEFAULT (unixepoch()),
45 PRIMARY KEY (item_id, user_id),
46 FOREIGN KEY (item_id) REFERENCES items_new(id) ON DELETE CASCADE
47 );
48 `);
49
50 // Copy data if old tables exist, filtering/truncating as needed
51 const tableExists = (name: string) => {
52 const result = db.prepare(`
53 SELECT name FROM sqlite_master
54 WHERE type='table' AND name=?
55 `).get(name);
56 return result !== undefined;
57 };
58
59 if (tableExists('rooms')) {
60 db.exec(`
61 INSERT INTO rooms_new (code, created_at)
62 SELECT code, created_at
63 FROM rooms
64 `);
65 }
66
67 if (tableExists('items')) {
68 db.exec(`
69 INSERT INTO items_new (id, room_code, text, created_at)
70 SELECT i.id, i.room_code,
71 SUBSTR(TRIM(REPLACE(REPLACE(i.text, char(13)||char(10), char(10)), char(13), char(10))), 1, 200) as text,
72 i.created_at
73 FROM items i
74 WHERE i.room_code IN (SELECT code FROM rooms_new)
75 AND LENGTH(TRIM(i.text)) > 0
76 `);
77 }
78
79 if (tableExists('votes')) {
80 db.exec(`
81 INSERT INTO votes_new (item_id, user_id, vote_type, created_at)
82 SELECT v.item_id, v.user_id, v.vote_type, v.created_at
83 FROM votes v
84 WHERE v.item_id IN (SELECT id FROM items_new)
85 AND v.vote_type IN ('up', 'down', 'veto')
86 `);
87 }
88
89 // Drop old tables if they exist
90 if (tableExists('votes')) db.exec('DROP TABLE votes');
91 if (tableExists('items')) db.exec('DROP TABLE items');
92 if (tableExists('rooms')) db.exec('DROP TABLE rooms');
93
94 // Rename new tables
95 db.exec('ALTER TABLE rooms_new RENAME TO rooms');
96 db.exec('ALTER TABLE items_new RENAME TO items');
97 db.exec('ALTER TABLE votes_new RENAME TO votes');
98
99 // Create indexes
100 db.exec(`
101 CREATE INDEX idx_items_room_code ON items(room_code);
102 CREATE INDEX idx_votes_item_id ON votes(item_id);
103 CREATE INDEX idx_votes_user_id ON votes(user_id);
104 `);
105
106 // Re-enable foreign keys
107 db.exec('PRAGMA foreign_keys = ON');
108
109 // Set schema version
110 db.exec('PRAGMA user_version = 2');
111
112 db.exec('COMMIT');
113 console.log('✅ Migration complete');
114 } catch (err) {
115 db.exec('ROLLBACK');
116 console.error('❌ Migration failed:', err);
117 throw err;
118 }
119} else if (currentVersion === 1) {
120 console.log('🔄 Migrating database schema from version 1 to 2...');
121
122 try {
123 db.exec('BEGIN');
124 db.exec('PRAGMA foreign_keys = OFF');
125
126 // Create new rooms table without last_activity and length constraint
127 db.exec(`
128 CREATE TABLE rooms_new (
129 code TEXT PRIMARY KEY NOT NULL,
130 created_at INTEGER DEFAULT (unixepoch())
131 );
132 `);
133
134 // Copy existing rooms
135 db.exec(`
136 INSERT INTO rooms_new (code, created_at)
137 SELECT code, created_at
138 FROM rooms
139 `);
140
141 // Drop old and rename
142 db.exec('DROP TABLE rooms');
143 db.exec('ALTER TABLE rooms_new RENAME TO rooms');
144
145 db.exec('PRAGMA foreign_keys = ON');
146 db.exec('PRAGMA user_version = 2');
147 db.exec('COMMIT');
148
149 console.log('✅ Migration from v1 to v2 complete');
150 } catch (err) {
151 db.exec('ROLLBACK');
152 console.error('❌ Migration failed:', err);
153 throw err;
154 }
155} else if (currentVersion === 2) {
156 console.log('🔄 Migrating database schema from version 2 to 3...');
157
158 try {
159 db.exec('BEGIN');
160
161 // Add title column to rooms table
162 db.exec('ALTER TABLE rooms ADD COLUMN title TEXT');
163
164 db.exec('PRAGMA user_version = 3');
165 db.exec('COMMIT');
166
167 console.log('✅ Migration from v2 to v3 complete');
168 } catch (err) {
169 db.exec('ROLLBACK');
170 console.error('❌ Migration failed:', err);
171 throw err;
172 }
173} else {
174 // Plant the schema if soil is fresh (for new databases)
175 db.exec(`
176 CREATE TABLE IF NOT EXISTS rooms (
177 code TEXT PRIMARY KEY NOT NULL,
178 created_at INTEGER DEFAULT (unixepoch()),
179 title TEXT
180 );
181
182 CREATE TABLE IF NOT EXISTS items (
183 id TEXT PRIMARY KEY NOT NULL,
184 room_code TEXT NOT NULL,
185 text TEXT NOT NULL CHECK(LENGTH(text) BETWEEN 1 AND 200),
186 created_at INTEGER DEFAULT (unixepoch()),
187 FOREIGN KEY (room_code) REFERENCES rooms(code) ON DELETE CASCADE
188 );
189
190 CREATE TABLE IF NOT EXISTS votes (
191 item_id TEXT NOT NULL,
192 user_id TEXT NOT NULL,
193 vote_type TEXT NOT NULL CHECK(vote_type IN ('up', 'down', 'veto')),
194 created_at INTEGER DEFAULT (unixepoch()),
195 PRIMARY KEY (item_id, user_id),
196 FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
197 );
198 `);
199}
200
201const clients = new Map<string, Set<WebSocket>>();
202
203function generateRoomId(): string {
204 return crypto.randomUUID();
205}
206
207function broadcast(roomCode: string, message: unknown, except?: WebSocket) {
208 const room = clients.get(roomCode);
209 if (!room) return;
210
211 const payload = JSON.stringify(message);
212 for (const client of room) {
213 if (client !== except && client.readyState === WebSocket.OPEN) {
214 client.send(payload);
215 }
216 }
217}
218
219// Validation constants
220const MAX_ITEM_TEXT_LEN = 200;
221const MAX_BULK_ITEMS = 100;
222const MAX_WS_MESSAGE_BYTES = 32768;
223
224// Validation helpers
225function safeParseMessage(ws: WebSocket, raw: string): unknown | null {
226 if (raw.length > MAX_WS_MESSAGE_BYTES) {
227 console.warn(`Message too large: ${raw.length} bytes`);
228 ws.close(1009, 'Message too large');
229 return null;
230 }
231
232 try {
233 return JSON.parse(raw);
234 } catch (err) {
235 console.warn('Invalid JSON:', err);
236 return null;
237 }
238}
239
240function sanitizeItemText(s: string): string | null {
241 // Trim and collapse internal CRLF to \n
242 const cleaned = s.trim().replace(/\r\n/g, '\n').replace(/\r/g, '\n');
243
244 if (cleaned.length < 1 || cleaned.length > MAX_ITEM_TEXT_LEN) {
245 return null;
246 }
247
248 return cleaned;
249}
250
251function isValidVoteType(x: unknown): x is 'up' | 'down' | 'veto' {
252 return x === 'up' || x === 'down' || x === 'veto';
253}
254
255function itemBelongsToRoom(itemId: string, roomCode: string): boolean {
256 const result = db.prepare('SELECT 1 FROM items WHERE id = ? AND room_code = ?').get(itemId, roomCode);
257 return result !== undefined;
258}
259
260function getState(roomCode: string) {
261 const room = db.prepare('SELECT title FROM rooms WHERE code = ?').get(roomCode) as { title: string | null } | undefined;
262 const roomTitle = room?.title || null;
263
264 const items = db.prepare(`
265 SELECT i.id, i.text, i.created_at,
266 GROUP_CONCAT(v.user_id || ':' || v.vote_type) as votes
267 FROM items i
268 LEFT JOIN votes v ON i.id = v.item_id
269 WHERE i.room_code = ?
270 GROUP BY i.id
271 ORDER BY i.created_at
272 `).all(roomCode) as Array<{id: string, text: string, created_at: number, votes: string | null}>;
273
274 return {
275 roomTitle,
276 items: items.map(item => ({
277 id: item.id,
278 text: item.text,
279 votes: item.votes ? Object.fromEntries(
280 item.votes.split(',').map(v => {
281 const [userId, voteType] = v.split(':');
282 return [userId, voteType];
283 })
284 ) : {}
285 }))
286 };
287}
288
289function handleWebSocket(ws: WebSocket, roomCode: string, userId: string) {
290 // Add to room
291 if (!clients.has(roomCode)) {
292 clients.set(roomCode, new Set());
293 }
294 clients.get(roomCode)!.add(ws);
295
296 ws.onopen = () => {
297 // Send current state once connection is open
298 const state = getState(roomCode);
299 ws.send(JSON.stringify({
300 type: 'state',
301 items: state.items,
302 roomTitle: state.roomTitle,
303 userId
304 }));
305 };
306
307 ws.onmessage = (event) => {
308 const msg = safeParseMessage(ws, event.data);
309 if (!msg || typeof msg !== 'object') return;
310
311 try {
312 switch ((msg as any).type) {
313 case 'add_items': {
314 const rawItems = (msg as any).items;
315 if (!Array.isArray(rawItems)) {
316 console.warn('add_items: items is not an array');
317 return;
318 }
319
320 if (rawItems.length < 1 || rawItems.length > MAX_BULK_ITEMS) {
321 console.warn(`add_items: invalid count ${rawItems.length}`);
322 return;
323 }
324
325 const items = rawItems
326 .map((text: unknown) => {
327 if (typeof text !== 'string') return null;
328 const sanitized = sanitizeItemText(text);
329 return sanitized ? { id: crypto.randomUUID(), text: sanitized } : null;
330 })
331 .filter((item): item is { id: string; text: string } => item !== null);
332
333 if (items.length === 0) {
334 console.warn('add_items: no valid items after sanitization');
335 return;
336 }
337
338 try {
339 db.exec('BEGIN IMMEDIATE');
340 const stmt = db.prepare('INSERT INTO items (id, room_code, text) VALUES (?, ?, ?)');
341 for (const item of items) {
342 stmt.run(item.id, roomCode, item.text);
343 }
344 db.exec('COMMIT');
345 } catch (err) {
346 db.exec('ROLLBACK');
347 console.error('add_items transaction failed:', err);
348 return;
349 }
350
351 broadcast(roomCode, {
352 type: 'items_added',
353 items: items.map(i => ({ ...i, votes: {} }))
354 });
355 break;
356 }
357
358 case 'vote': {
359 const { itemId, voteType } = msg as any;
360
361 if (typeof itemId !== 'string') {
362 console.warn('vote: itemId is not a string');
363 return;
364 }
365
366 if (!isValidVoteType(voteType)) {
367 console.warn(`vote: invalid voteType ${voteType}`);
368 return;
369 }
370
371 if (!itemBelongsToRoom(itemId, roomCode)) {
372 console.warn(`vote: item ${itemId} does not belong to room ${roomCode}`);
373 return;
374 }
375
376 db.prepare(`
377 INSERT INTO votes (item_id, user_id, vote_type)
378 VALUES (?, ?, ?)
379 ON CONFLICT (item_id, user_id)
380 DO UPDATE SET vote_type = excluded.vote_type
381 `).run(itemId, userId, voteType);
382
383 broadcast(roomCode, {
384 type: 'vote_changed',
385 itemId,
386 userId,
387 voteType
388 });
389 break;
390 }
391
392 case 'unvote': {
393 const { itemId } = msg as any;
394
395 if (typeof itemId !== 'string') {
396 console.warn('unvote: itemId is not a string');
397 return;
398 }
399
400 if (!itemBelongsToRoom(itemId, roomCode)) {
401 console.warn(`unvote: item ${itemId} does not belong to room ${roomCode}`);
402 return;
403 }
404
405 db.prepare('DELETE FROM votes WHERE item_id = ? AND user_id = ?')
406 .run(itemId, userId);
407
408 broadcast(roomCode, {
409 type: 'vote_removed',
410 itemId,
411 userId
412 });
413 break;
414 }
415
416 case 'edit_item': {
417 const { itemId, text } = msg as any;
418
419 if (typeof itemId !== 'string') {
420 console.warn('edit_item: itemId is not a string');
421 return;
422 }
423
424 if (typeof text !== 'string') {
425 console.warn('edit_item: text is not a string');
426 return;
427 }
428
429 const sanitized = sanitizeItemText(text);
430 if (!sanitized) {
431 console.warn('edit_item: text failed sanitization');
432 return;
433 }
434
435 if (!itemBelongsToRoom(itemId, roomCode)) {
436 console.warn(`edit_item: item ${itemId} does not belong to room ${roomCode}`);
437 return;
438 }
439
440 db.prepare('UPDATE items SET text = ? WHERE id = ?')
441 .run(sanitized, itemId);
442
443 broadcast(roomCode, {
444 type: 'item_edited',
445 itemId,
446 text: sanitized
447 });
448 break;
449 }
450
451 case 'delete_item': {
452 const { itemId } = msg as any;
453
454 if (typeof itemId !== 'string') {
455 console.warn('delete_item: itemId is not a string');
456 return;
457 }
458
459 if (!itemBelongsToRoom(itemId, roomCode)) {
460 console.warn(`delete_item: item ${itemId} does not belong to room ${roomCode}`);
461 return;
462 }
463
464 // Delete votes first (foreign key constraint)
465 db.prepare('DELETE FROM votes WHERE item_id = ?').run(itemId);
466 // Delete the item
467 db.prepare('DELETE FROM items WHERE id = ?').run(itemId);
468
469 broadcast(roomCode, {
470 type: 'item_deleted',
471 itemId
472 });
473 break;
474 }
475
476 case 'reset_votes': {
477 // Delete all votes for all items in this room
478 db.prepare(`
479 DELETE FROM votes
480 WHERE item_id IN (
481 SELECT id FROM items WHERE room_code = ?
482 )
483 `).run(roomCode);
484
485 broadcast(roomCode, {
486 type: 'votes_reset'
487 });
488 break;
489 }
490
491 case 'set_title': {
492 const { title } = msg as any;
493
494 let sanitized: string | null = null;
495 if (title !== null && title !== undefined) {
496 if (typeof title !== 'string') {
497 console.warn('set_title: title is not a string');
498 return;
499 }
500 sanitized = sanitizeItemText(title);
501 if (!sanitized) {
502 console.warn('set_title: title failed sanitization');
503 return;
504 }
505 }
506
507 db.prepare('UPDATE rooms SET title = ? WHERE code = ?')
508 .run(sanitized, roomCode);
509
510 broadcast(roomCode, {
511 type: 'title_changed',
512 title: sanitized
513 });
514 break;
515 }
516 }
517 } catch (err) {
518 console.error('Message handling error:', err);
519 }
520 };
521
522 ws.onclose = () => {
523 const room = clients.get(roomCode);
524 if (room) {
525 room.delete(ws);
526 if (room.size === 0) {
527 clients.delete(roomCode);
528 }
529 }
530 };
531}
532
533serve(async (req) => {
534 const url = new URL(req.url);
535
536 // Serve static files
537 if (url.pathname === "/" || url.pathname === "/index.html") {
538 const html = await Deno.readTextFile("./static/index.html");
539 return new Response(html, { headers: { "content-type": "text/html" } });
540 }
541 if (url.pathname === "/style.css") {
542 const css = await Deno.readTextFile("./static/style.css");
543 return new Response(css, { headers: { "content-type": "text/css" } });
544 }
545 if (url.pathname === "/palette.css") {
546 const css = await Deno.readTextFile("./static/palette.css");
547 return new Response(css, { headers: { "content-type": "text/css" } });
548 }
549 if (url.pathname === "/app.js") {
550 const js = await Deno.readTextFile("./static/app.js");
551 return new Response(js, { headers: { "content-type": "application/javascript" } });
552 }
553 if (url.pathname.startsWith("/icons/") && url.pathname.endsWith(".svg")) {
554 const iconName = url.pathname.slice(7); // Remove "/icons/"
555 try {
556 const svg = await Deno.readTextFile(`./static/icons/${iconName}`);
557 return new Response(svg, { headers: { "content-type": "image/svg+xml" } });
558 } catch {
559 return new Response("Not found", { status: 404 });
560 }
561 }
562
563 // Create new room
564 if (url.pathname === "/api/create") {
565 const code = generateRoomId();
566 db.prepare('INSERT INTO rooms (code) VALUES (?)').run(code);
567 return Response.json({ code });
568 }
569
570 // WebSocket connection
571 if (url.pathname === "/ws") {
572 const roomCode = url.searchParams.get("room");
573 const userId = url.searchParams.get("user") || crypto.randomUUID();
574
575 if (!roomCode) {
576 return new Response("Missing room code", { status: 400 });
577 }
578
579 // Verify room exists
580 const roomExists = db.prepare('SELECT code FROM rooms WHERE code = ?').get(roomCode);
581 if (!roomExists) {
582 return new Response("Room not found", { status: 404 });
583 }
584
585 const upgrade = req.headers.get("upgrade") || "";
586 if (upgrade.toLowerCase() !== "websocket") {
587 return new Response("Expected websocket", { status: 426 });
588 }
589
590 const { socket, response } = Deno.upgradeWebSocket(req);
591 handleWebSocket(socket, roomCode, userId);
592 return response;
593 }
594
595 return new Response("Not found", { status: 404 });
596}, { port: 8294 });
597
598console.log("🌿 Sift ready at http://localhost:8294");