Migrate from Upstash Redis to Val Town's built-in SQLite for better performance and zero external dependencies.

• No external service dependency - SQLite is built into Val Town
• No rate limits or quotas - Unlimited reads/writes
• Better for relational data - Events and answers have clear relationships
• SQL queries - More powerful querying capabilities
• Automatic stats - Use SQL aggregations instead of manual calculations

-- Events table
CREATE TABLE events (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  options TEXT NOT NULL, -- JSON array
  secret TEXT NOT NULL,
  creator_email TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Answers table  
CREATE TABLE answers (
  event_id TEXT NOT NULL,
  person_name TEXT NOT NULL,
  option TEXT NOT NULL,
  secret TEXT NOT NULL,
  email TEXT,
  answered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (event_id, person_name),
  FOREIGN KEY (event_id) REFERENCES events(id)
);

-- Indexes for performance
CREATE INDEX idx_answers_event_id ON answers(event_id);

1. Import SQLite module

   import { sqlite } from "https://esm.town/v/stevekrouse/sqlite";

2. Create initialization function

   async function initDB() {
     await sqlite.execute(`CREATE TABLE IF NOT EXISTS events_v1 ...`);
     await sqlite.execute(`CREATE TABLE IF NOT EXISTS answers_v1 ...`);
   }

// Before (Redis)
await redis.set(`event:${id}`, JSON.stringify(event));

// After (SQLite)
await sqlite.execute(
  `INSERT INTO events (id, name, options, secret, creator_email) 
   VALUES (?, ?, ?, ?, ?)`,
  [id, name, JSON.stringify(options), secret, creator_email]
);

// Single SQL query replaces multiple Redis operations
const result = await sqlite.execute(`
  SELECT 
    e.*,
    a.option,
    COUNT(a.person_name) as count,
    GROUP_CONCAT(a.person_name) as participants
  FROM events e
  LEFT JOIN answers a ON e.id = a.event_id
  WHERE e.id = ?
  GROUP BY e.id, a.option
`, [id]);

// Upsert pattern for SQLite
await sqlite.execute(`
  INSERT INTO answers (event_id, person_name, option, secret, email)
  VALUES (?, ?, ?, ?, ?)
  ON CONFLICT(event_id, person_name) 
  DO UPDATE SET option = ?, answered_at = CURRENT_TIMESTAMP, email = ?
`, [id, person_name, option, secret, email, option, email]);

1. Deploy new SQLite version to new Val
2. Users start fresh with new events
3. Keep old Redis version running for existing events

1. Update API to write to both Redis and SQLite
2. Gradually migrate read operations
3. Once stable, remove Redis code

1. Native Aggregations

   -- Get event summary in one query
   SELECT 
     e.name,
     COUNT(DISTINCT a.person_name) as total_participants,
     a.option,
     COUNT(a.person_name) as votes
   FROM events e
   LEFT JOIN answers a ON e.id = a.event_id
   WHERE e.id = ?
   GROUP BY a.option

2. Better Querying

   • List all events by creator email
   • Find most popular options across events
   • Track participation over time

3. No More Cache Management

   • SQLite handles query optimization
   • No need for manual stats caching
   • No Redis key expiration to manage

• Multiple round trips (keys, mget, parsing)
• Manual stats calculation in JavaScript
• ~50-100ms for event with many answers

• Single optimized SQL query
• Database handles aggregation
• ~5-20ms for same operation

1. Week 1: Create new SQLite-based API file
2. Week 2: Test all endpoints thoroughly
3. Week 3: Deploy and monitor performance
4. Week 4: Migrate existing events (if needed)

upstash/
├── doodle_api.http.ts       # Current Redis version
├── doodle_api_v2.http.ts    # New SQLite version
├── admin.http.ts            # No changes needed
├── answer.http.ts           # No changes needed
└── doodle.http.ts           # Update import to v2

• Risk: SQLite file size limits
  • Mitigation: Val Town handles this automatically
• Risk: Concurrent write conflicts
  • Mitigation: SQLite handles with WAL mode
• Risk: Migration complexity
  • Mitigation: Start fresh, import old data later if needed

SQLite is the better choice for this application:

• Eliminates 1.8k/hour Redis reads entirely
• Simpler code with SQL queries
• Better performance for stats
• No external dependencies
• Free and unlimited usage