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