How to Configure Database Schema Migrations in TREK and What Happens During Updates

TREK automates SQLite schema evolution through a version-tracked migration array in server/src/db/migrations.ts that executes sequentially on every server startup, wrapping DDL statements in error-handling logic to ensure idempotent updates.

TREK (TRE‑K) stores all application data in a SQLite database and manages schema changes through a built-in migration system. Understanding how to configure database schema migrations in TREK is essential for maintaining data integrity across deployments. The migration engine resides in server/src/db/migrations.ts and integrates with the server startup sequence in server/src/app.ts to apply changes automatically.

How TREK's Migration System Works

The migration system uses a version-tracking table and an ordered array of migration functions to evolve the database schema safely.

Schema Version Tracking

At the core of the system is a schema_version table that persists the current migration state. According to the source code in server/src/db/migrations.ts:70-73, the system ensures this table exists with:

CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL)

On startup, the runMigrations() function reads the single row from this table. If no row exists, the system initializes the version to 0.

The Migrations Array

All schema changes are defined in a global migrations array located in server/src/db/migrations.ts. Each entry represents one schema version and contains either a function or an object with a raw method. The index of each entry implicitly defines its version number (0-based).

The system loops from the current stored version up to the last array index, executing each migration in sequence. After each successful execution, the schema_version table is updated to reflect the new version number.

Idempotent Execution Pattern

To ensure migrations are safe to run multiple times (such as when a user restarts the server), each migration wraps DDL statements in a try-catch block that ignores "duplicate column name" errors. As seen in migration 33 (which adds the day_title column) at lines 93-98, the pattern is:

() => {
  try {
    db.exec('ALTER TABLE places ADD COLUMN day_title TEXT');
  } catch (err: any) {
    if (!err.message?.includes('duplicate column name')) throw err;
  }
},

This idempotent approach prevents failures on databases that already contain the schema changes.

Configuring Database Schema Migrations in TREK

Adding a new migration requires appending to the existing array and following specific safety patterns.

Step 1: Create the Migration Function

Write a function that receives the db instance (a better-sqlite3 Database object) and performs the required schema change. For example, to add a column to the users table:

() => {
  try {
    db.exec('ALTER TABLE users ADD COLUMN favorite_color TEXT');
  } catch (err: any) {
    if (!err.message?.includes('duplicate column name')) throw err;
  }
},

Step 2: Register in the Migrations Array

Append your new function to the end of the migrations array in server/src/db/migrations.ts. Its position in the array implicitly becomes the migration number. The runMigrations function will automatically detect and execute it when the server starts.

Step 3: Handle Data Transformations

For migrations requiring data migration or normalization, include additional logic after the DDL statements. The file includes helpers like trimUserWhitespace for common tasks. For example, migration 22 (lines 158-180) moves reservation data from places to day_assignments:

() => {
  try {
    db.exec('ALTER TABLE day_assignments ADD COLUMN reservation_id INTEGER');
  } catch (err: any) {
    if (!err.message?.includes('duplicate column name')) throw err;
  }
  
  // Data migration wrapped in try/catch
  try {
    db.exec(`
      UPDATE day_assignments 
      SET reservation_id = (
        SELECT id FROM places 
        WHERE places.day_id = day_assignments.day_id
      )
    `);
  } catch (err) {
    console.log('Data migration skipped or already applied');
  }
},

Step 4: Verify with Tests

TREK includes a migration-hygiene test in server/tests/unit/services/migration.test.ts that verifies each migration can be applied to a fresh database. Adding a new migration triggers this test automatically, ensuring your changes are safe and idempotent.

Important: Never edit an existing migration after it has been released. Once a migration is deployed, it must remain unchanged to prevent divergent states across installations. Always add new schema changes as additional entries at the end of the array.

What Happens During Updates

When a new version of TREK is deployed, the database updates automatically without manual intervention.

Automatic Execution on Startup

The server startup sequence in server/src/app.ts initializes the SQLite connection using better-sqlite3 and immediately invokes runMigrations(db). This process:

1. Reads current version from the schema_version table
2. Executes pending migrations from the migrations array (all entries with index > current version)
3. Updates version after each successful migration
4. Logs progress to the console (e.g., [DB] Migrated reservation data from places to day_assignments)

Transaction Safety and Error Handling

While runMigrations does not wrap the entire sequence in a single transaction, individual migrations can use explicit transactions if needed. The idempotent error handling ensures that if a server restarts mid-migration, subsequent starts will skip already-applied changes and continue from where they left off, preventing partial schema states.

Summary

• TREK uses SQLite with a version-tracked migration system in server/src/db/migrations.ts
• The migrations array contains ordered functions that execute sequentially based on the schema_version table
• Configuration involves appending new functions to the array, wrapping DDL in try-catch blocks for idempotency, and optionally adding data transformation logic
• Updates run automatically on server startup via runMigrations(db) called from server/src/app.ts
• Never modify existing migrations; always append new changes to ensure consistent upgrades across installations

Frequently Asked Questions

How do I run migrations manually for debugging?

You can execute migrations manually using a Node REPL. This is useful for testing changes against a specific database file:

node -e "
  const Database = require('better-sqlite3');
  const db = new Database('data/trek.db');
  const { runMigrations } = require('./server/src/db/migrations');
  runMigrations(db);
"

This bypasses the full server startup and runs only the migration logic against the specified database file.

What happens if a migration fails?

If a migration throws an error other than "duplicate column name", the process stops and the error propagates up. The schema_version table will not be updated for the failed migration, causing the server startup to halt. This prevents the application from running against a partially migrated schema. You must fix the migration code or the database state before the server can start successfully.

Can I use SQL transactions in my migrations?

Yes. While the runMigrations function does not automatically wrap the entire sequence in a transaction, you can use db.exec('BEGIN') and db.exec('COMMIT') within your individual migration function. However, due to SQLite's limitations with ALTER TABLE statements inside transactions, you may need to handle transaction logic carefully or rely on the built-in idempotent error handling for safety.

Where are the initial table definitions created?

Before migrations run, initial tables are created by server/src/db/schema.ts. This file defines the base schema that exists before version 1. The migration system then takes over for all subsequent schema changes. This separation ensures new installations start with a complete base schema while existing installations upgrade through the migration chain.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}