Database Migrations

This directory contains database migrations for MonoTask.

Migration System

MonoTask uses a simple migration tracking system:

• Migrations are numbered sequentially (e.g., 002-add-feature.ts, 003-update-schema.ts)
• Applied migrations are tracked in the schema_migrations table
• The baseline schema (001-baseline) was created on 2025-10-19 and represents the consolidation of all previous migrations

Creating a New Migration

1. Create a new file in this directory with the format: {version}-{description}.ts

   • Version should be the next sequential number (e.g., 002, 003)
   • Description should be kebab-case and descriptive

2. Use this template:

#!/usr/bin/env bun

import type { Migration } from '@monotask/shared/migration-types';
import type { Database } from 'bun:sqlite';

const migration: Migration = {
  version: '002-your-feature',
  name: 'Add your feature description',

  async up(db: Database) {
    // Apply migration
    db.run(`
      CREATE TABLE your_table (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
      )
    `);
  },

  async down(db: Database) {
    // Rollback migration (optional but recommended)
    db.run('DROP TABLE IF EXISTS your_table');
  }
};

export default migration;

3. Test your migration:

# Check current status
bun run db:migrate:status

# Run pending migrations
bun run db:migrate

# Verify the migration was applied
bun run db:migrate:status

Migration Best Practices

1. Atomic Changes: Each migration should represent one logical change
2. Idempotent: Use IF NOT EXISTS, IF EXISTS where possible
3. Reversible: Provide a down function when possible for rollbacks
4. Test First: Test migrations on a copy of your database first
5. No Data Loss: Be careful with DROP statements; backup data first
6. Sequential Numbering: Use the next available number in sequence

Available Commands

# Initialize a fresh database (with baseline schema)
bun run db:init

# Run all pending migrations
bun run db:migrate

# Check migration status
bun run db:migrate:status

# Reset database (WARNING: Deletes all data)
bun run db:reset

Migration History

• 001-baseline: Consolidated schema from all migrations up to 2025-10-19
  • See scripts/init-database-LEGACY.ts for previous migration history
  • 73 tables, full production schema

Troubleshooting

Migration failed partway through

• Check schema_migrations table to see what was applied
• Fix the issue in the migration file
• Re-run bun run db:migrate

Need to rollback a migration

• Migrations with down functions can be rolled back
• Currently, manual rollback is required (automatic rollback coming soon)

Migration already applied error

• Check bun run db:migrate:status to see applied migrations
• Ensure you're using a unique version number

See Also

• packages/shared/src/migration-types.ts - Migration type definitions
• packages/shared/src/migration-runner.ts - Migration runner utilities
• scripts/run-migrations.ts - Migration execution script
• scripts/migration-status.ts - Status checking script