Database Module

This module handles all database operations for the OffX application using Val Town's SQLite module.

The database schema follows a normalized design (up to 3NF) with the following tables:

1. topics_v1

   • Stores user-defined topics for categorizing and summarizing posts
   • Fields: id, title, description, created_at, updated_at

2. posts_v1

   • Stores posts from various platforms (Twitter/X, etc.)
   • Fields: id, external_id, content, author, author_username, platform, created_at, fetched_at, url

3. post_media_v1

   • Stores media attachments for posts
   • Fields: id, post_id, media_url, media_type, alt_text, created_at
   • Foreign key: post_id references posts_v1(id)

4. topic_posts_v1

   • Junction table for many-to-many relationship between topics and posts
   • Fields: topic_id, post_id, added_at
   • Foreign keys: topic_id references topics_v1(id), post_id references posts_v1(id)

5. summarizations_v1

   • Stores AI-generated summaries of posts for a topic
   • Fields: id, topic_id, content, prompt, post_count, created_at, time_period_start, time_period_end
   • Foreign key: topic_id references topics_v1(id)

The following indexes are created to optimize query performance:

1. Posts table indexes

   • idx_posts_external_id: For fast lookups by external platform ID
   • idx_posts_author: For filtering/searching posts by author
   • idx_posts_created_at: For time-based queries and sorting
   • idx_posts_platform: For filtering posts by platform

2. Topic-Posts junction table indexes

   • idx_topic_posts_topic_id: For finding all posts related to a specific topic
   • idx_topic_posts_post_id: For finding all topics a post belongs to

3. Summarizations table indexes

   • idx_summarizations_topic_id: For retrieving summaries for a specific topic
   • idx_summarizations_created_at: For time-based sorting of summaries
   • idx_summarizations_time_period: Compound index for efficient time range queries

Val Town's SQLite implementation has limited support for ALTER TABLE operations. Instead of traditional migration frameworks, we use a table versioning approach for schema changes.

1. Version Suffix in Table Names

   • All tables include a version suffix (e.g., topics_v1)
   • When schema changes are needed, create a new table with an incremented version (e.g., topics_v2)

2. Data Migration Process

   • Create the new table with the updated schema
   • Copy data from the old table to the new table, mapping columns as needed
   • Update the TABLES constant in migrations.ts to point to the new table versions
   • Deploy the updated code

3. Utility Functions

   • tableExists(tableName): Check if a table exists in the database
   • getTableColumns(tableName): Get column information for a table
   • migrateTable(oldTableName, newTableName, createTableSql, columnMappings): Migrate data between tables

To add new columns to the topics table:

// 1. Create a migration function
async function upgradeTopicsToV2() {
  const oldTable = "topics_v1";
  const newTable = "topics_v2";
  
  // Define the new table schema with additional columns
  const createTableSql = `
    CREATE TABLE IF NOT EXISTS ${newTable} (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      description TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      is_active BOOLEAN DEFAULT 1,  -- New column added in v2
      color TEXT DEFAULT '#3498db'  -- New column added in v2
    )
  `;
  
  // Migrate the table
  await migrateTable(oldTable, newTable, createTableSql);
}

// 2. Update the TABLES constant after migration
export const TABLES = {
  TOPICS: "topics_v2", // Updated to v2
  POSTS: "posts_v1",
  // ...other tables
};

When renaming columns, provide a mapping in the migration function:

// Column mappings for renamed columns
const columnMappings = {
  "author": "author_name",
  "platform": "source",
  "created_at": "published_at"
};

// Use the mappings in the migration function
await migrateTable(oldTable, newTable, createTableSql, columnMappings);

1. Always increment version numbers - Never reuse a table name for a different schema
2. Test migrations thoroughly - Verify data integrity after migration
3. Deploy schema changes separately from application logic changes when possible
4. Document schema changes in code comments and commit messages
5. Update all affected queries when changing table or column names
6. Create appropriate indexes for new tables and columns

The database module is automatically initialized when imported. For production deployments, you may want to comment out the auto-initialization for better performance in serverless environments:

// Comment this out in production if you want to initialize manually
// for better performance in serverless environments
initializeDatabase().catch(console.error);