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:
topics_v1
posts_v1
post_media_v1
topic_posts_v1
summarizations_v1
The following indexes are created to optimize query performance:
Posts table indexes
Topic-Posts junction table indexes
Summarizations table indexes
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.
Version Suffix in Table Names
topics_v1
)topics_v2
)Data Migration Process
TABLES
constant in migrations.ts
to point to the new table versionsUtility Functions
tableExists(tableName)
: Check if a table exists in the databasegetTableColumns(tableName)
: Get column information for a tablemigrateTable(oldTableName, newTableName, createTableSql, columnMappings)
: Migrate data between tablesTo 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);
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);