Val Town Code SearchReturn to Val Town

30- [ ] add triggers to sidebar
31- [ ] add upload from SQL, CSV and JSON
32- [ ] add ability to connect to a non-val town Turso database 
33- [x] fix wonky sidebar separator height problem (thanks to @stevekrouse)
34- [x] make result tables scrollable

29        type: "text",
30        mapsTo: "incidentType",
31        placeholder: "e.g., Unplanned database downtime",
32      },
33      {

1import { sqlite } from "https://esm.town/v/stevekrouse/sqlite";
2import logger from '../utils/logger.ts';
3import { DatabaseError } from '../utils/error-handler.ts';
4
5// Create a logger for database operations
6const log = logger.createLogger('Database');
7
8// Table names - use versioning to allow for schema changes
12const EXAMPLES_TABLE = "abuse_analyzer_examples_v1";
13
14// Types for our database entities
15export interface Session {
16  id: string;
50
51/**
52 * Initialize database tables
53 */
54export async function initDatabase(): Promise<void> {
55  try {
56    log.info("Initializing database tables");
57    
58    // Create sessions table
109    `);
110    
111    log.info("Database tables initialized successfully");
112  } catch (error) {
113    log.error("Failed to initialize database tables:", error);
114    throw new DatabaseError("Failed to initialize database", error instanceof Error ? error : undefined);
115  }
116}
140  } catch (error) {
141    log.error(`Failed to create session "${title}":`, error);
142    throw new DatabaseError("Failed to create session", error instanceof Error ? error : undefined);
143  }
144}
163  } catch (error) {
164    log.error(`Failed to get session ${id}:`, error);
165    throw new DatabaseError("Failed to get session", error instanceof Error ? error : undefined);
166  }
167}
182  } catch (error) {
183    log.error(`Failed to update session timestamp ${id}:`, error);
184    throw new DatabaseError("Failed to update session timestamp", error instanceof Error ? error : undefined);
185  }
186}
199  } catch (error) {
200    log.error("Failed to get all sessions:", error);
201    throw new DatabaseError("Failed to get all sessions", error instanceof Error ? error : undefined);
202  }
203}
218  } catch (error) {
219    log.error(`Failed to update session title ${id}:`, error);
220    throw new DatabaseError("Failed to update session title", error instanceof Error ? error : undefined);
221  }
222}
259  } catch (error) {
260    log.error(`Failed to delete session ${id}:`, error);
261    throw new DatabaseError("Failed to delete session", error instanceof Error ? error : undefined);
262  }
263}
293  } catch (error) {
294    log.error(`Failed to add message to session ${session_id}:`, error);
295    throw new DatabaseError("Failed to add message", error instanceof Error ? error : undefined);
296  }
297}
311  } catch (error) {
312    log.error(`Failed to get messages for session ${session_id}:`, error);
313    throw new DatabaseError("Failed to get session messages", error instanceof Error ? error : undefined);
314  }
315}
355  } catch (error) {
356    log.error(`Failed to get threaded messages for session ${session_id}:`, error);
357    throw new DatabaseError("Failed to get threaded messages", error instanceof Error ? error : undefined);
358  }
359}
385  } catch (error) {
386    log.error(`Failed to add pattern to session ${session_id}:`, error);
387    throw new DatabaseError("Failed to add pattern", error instanceof Error ? error : undefined);
388  }
389}
403  } catch (error) {
404    log.error(`Failed to get patterns for session ${session_id}:`, error);
405    throw new DatabaseError("Failed to get session patterns", error instanceof Error ? error : undefined);
406  }
407}
426  } catch (error) {
427    log.error(`Failed to get pattern ${pattern_id}:`, error);
428    throw new DatabaseError("Failed to get pattern", error instanceof Error ? error : undefined);
429  }
430}
444  } catch (error) {
445    log.error(`Failed to get patterns by type for session ${session_id}:`, error);
446    throw new DatabaseError("Failed to get patterns by type", error instanceof Error ? error : undefined);
447  }
448}
479  } catch (error) {
480    log.error(`Failed to add example to pattern ${pattern_id}:`, error);
481    throw new DatabaseError("Failed to add example", error instanceof Error ? error : undefined);
482  }
483}
497  } catch (error) {
498    log.error(`Failed to get examples for pattern ${pattern_id}:`, error);
499    throw new DatabaseError("Failed to get pattern examples", error instanceof Error ? error : undefined);
500  }
501}
519  } catch (error) {
520    log.error(`Failed to get pattern with examples ${pattern_id}:`, error);
521    throw new DatabaseError("Failed to get pattern with examples", error instanceof Error ? error : undefined);
522  }
523}
544  } catch (error) {
545    log.error(`Failed to get patterns with examples for session ${session_id}:`, error);
546    throw new DatabaseError("Failed to get session patterns with examples", error instanceof Error ? error : undefined);
547  }
548}

16
17/**
18 * Error for database operations
19 */
20export class DatabaseError extends AppError {
21  public originalError?: Error;
22  
23  constructor(message: string, originalError?: Error) {
24    super(message);
25    this.name = 'DatabaseError';
26    this.originalError = originalError;
27  }
118export default {
119  AppError,
120  DatabaseError,
121  APIError,
122  AnalysisError,

10  - Tests for edge cases and error handling
11
12- **database.test.ts**: Tests for database operations
13  - Tests for CRUD operations on all tables
14  - Tests for recursive queries and threaded conversations

13  - `static-file-server.ts`: Static file server for the frontend
14
15- **database/**: Database operations
16  - `index.ts`: SQLite database operations for sessions, messages, patterns, and examples
17
18- **utils/**: Utility functions
32- `processAIResponse`: Processes the AI response into a structured format
33
34### Database
35
36The database module provides functions for storing and retrieving data from SQLite. It uses recursive common table expressions (CTEs) to handle threaded conversations.
37
38Key tables:

1import { assertEquals, assertExists } from "https://deno.land/std/testing/asserts.ts";
2import * as db from "../src/database/index.ts";
3
4/**
6 * 
7 * These tests verify the functionality of the chat API operations.
8 * They test the database operations that the API relies on.
9 */
10
14let testPatternId: number;
15
16// Setup: Initialize database
17Deno.test("ChatAPI - Setup", async () => {
18  await db.initDatabase();
19});
20

1import { assertEquals, assertExists, assertNotEquals } from "https://deno.land/std/testing/asserts.ts";
2import * as db from "../src/database/index.ts";
3
4/**
5 * Database module tests
6 * 
7 * These tests verify the functionality of the database operations.
8 * They should be run in sequence as they build on each other.
9 */
15let testExampleId: number;
16
17Deno.test("Database - Initialize database", async () => {
18  await db.initDatabase();
19  // If no error is thrown, the test passes
20});
21
22Deno.test("Database - Create session", async () => {
23  const session = await db.createSession("Test Session");
24  
31});
32
33Deno.test("Database - Get session", async () => {
34  const session = await db.getSession(testSessionId);
35  
39});
40
41Deno.test("Database - Update session title", async () => {
42  await db.updateSessionTitle(testSessionId, "Updated Test Session");
43  
46});
47
48Deno.test("Database - Add message", async () => {
49  const messageId = await db.addMessage(
50    testSessionId,
59});
60
61Deno.test("Database - Get session messages", async () => {
62  const messages = await db.getSessionMessages(testSessionId);
63  
68});
69
70Deno.test("Database - Add pattern", async () => {
71  const patternId = await db.addPattern(
72    testSessionId,
84});
85
86Deno.test("Database - Get pattern", async () => {
87  const pattern = await db.getPattern(testPatternId);
88  
94});
95
96Deno.test("Database - Add example", async () => {
97  const exampleId = await db.addExample(
98    testPatternId,
107});
108
109Deno.test("Database - Get pattern examples", async () => {
110  const examples = await db.getPatternExamples(testPatternId);
111  
116});
117
118Deno.test("Database - Get pattern with examples", async () => {
119  const pattern = await db.getPatternWithExamples(testPatternId);
120  
124});
125
126Deno.test("Database - Add threaded message", async () => {
127  const threadedMessageId = await db.addMessage(
128    testSessionId,
135});
136
137Deno.test("Database - Get threaded messages", async () => {
138  const messages = await db.getThreadedMessages(testSessionId, testMessageId);
139  
144});
145
146Deno.test("Database - Get session patterns with examples", async () => {
147  const patterns = await db.getSessionPatternsWithExamples(testSessionId);
148  
153});
154
155Deno.test("Database - Get all sessions", async () => {
156  const sessions = await db.getAllSessions();
157  
164});
165
166Deno.test("Database - Delete session", async () => {
167  await db.deleteSession(testSessionId);
168  

67### New Components
68
691. **Database Module** (`database.ts`):
70   - Manages SQLite operations
71   - Implements tables for sessions, messages, patterns, and examples
85   - Provides export functionality
86
87### Database Schema
88
89The application uses SQLite with the following tables:
175   - Tests for edge cases and error handling
176
1772. **`database.test.ts`**:
178   - Tests for database initialization
179   - Tests for CRUD operations on all tables
180   - Tests for recursive queries and threaded conversations

7const EXAMPLES_TABLE = "abuse_analyzer_examples_v1";
8
9// Initialize database tables
10export async function initDatabase() {
11  // Create sessions table
12  await sqlite.execute(`
63}
64
65// Types for our database entities
66export interface Session {
67  id: string;