Skip to content

A local-first personal finance app

8.0 KB
Raw Blame
// @ts-strict-ignore
// We have to bundle in JS migrations manually to avoid having to `eval`
// them which doesn't play well with CSP. There isn't great, and eventually
// we can remove this migration.
import type { Database } from '@jlongster/sql.js';

import m1632571489012 from '#migrations/1632571489012_remove_cache';
import m1722717601000 from '#migrations/1722717601000_reports_move_selected_categories';
import m1722804019000 from '#migrations/1722804019000_create_dashboard_table';
import m1723665565000 from '#migrations/1723665565000_prefs';
import m1765518577215 from '#migrations/1765518577215_multiple_dashboards';
import m1788468782000 from '#migrations/1788468782000_add_messages_pending';
import * as fs from '#platform/server/fs';
import { logger } from '#platform/server/log';
import * as sqlite from '#platform/server/sqlite';
import * as prefs from '#server/prefs';

let MIGRATIONS_DIR = fs.migrationsPath;

const javascriptMigrations = {
  1632571489012: m1632571489012,
  1722717601000: m1722717601000,
  1722804019000: m1722804019000,
  1723665565000: m1723665565000,
  1765518577215: m1765518577215,
  1788468782000: m1788468782000,
};

export async function withMigrationsDir(
  dir: string,
  func: () => Promise<void>,
): Promise<void> {
  const oldDir = MIGRATIONS_DIR;
  MIGRATIONS_DIR = dir;
  await func();
  MIGRATIONS_DIR = oldDir;
}

export function getMigrationsDir(): string {
  return MIGRATIONS_DIR;
}

export function getMigrationId(name: string): number {
  return parseInt(name.match(/^(\d)+/)[0]);
}

export function getUpMigration(id, names) {
  for (const m of names) {
    if (getMigrationId(m) === id) {
      return m;
    }
  }
}

async function patchBadMigrations(db: Database) {
  const badFiltersMigration = 1685375406832;
  const newFiltersMigration = 1688749527273;
  const appliedIds = await getAppliedMigrations(db);
  if (appliedIds.includes(badFiltersMigration)) {
    sqlite.runQuery(db, 'DELETE FROM __migrations__ WHERE id = ?', [
      badFiltersMigration,
    ]);
    sqlite.runQuery(db, 'INSERT INTO __migrations__ (id) VALUES (?)', [
      newFiltersMigration,
    ]);
  }
}

export async function getAppliedMigrations(db: Database): Promise<number[]> {
  const rows = sqlite.runQuery<{ id: number }>(
    db,
    'SELECT * FROM __migrations__ ORDER BY id ASC',
    [],
    true,
  );
  return rows.map(row => row.id);
}

export async function getMigrationList(
  migrationsDir: string,
): Promise<string[]> {
  const files = await fs.listDir(migrationsDir);
  return files
    .filter(name => name.match(/(\.sql|\.js)$/))
    .sort((m1, m2) => {
      const id1 = getMigrationId(m1);
      const id2 = getMigrationId(m2);
      if (id1 < id2) {
        return -1;
      } else if (id1 > id2) {
        return 1;
      }
      return 0;
    });
}

export function getPending(appliedIds: number[], all: string[]): string[] {
  return all.filter(name => {
    const id = getMigrationId(name);
    return appliedIds.indexOf(id) === -1;
  });
}

async function applyJavaScript(db, id) {
  const dbInterface = {
    runQuery: (query, params, fetchAll) =>
      sqlite.runQuery(db, query, params, fetchAll),
    execQuery: query => sqlite.execQuery(db, query),
    transaction: func => sqlite.transaction(db, func),
  };

  if (javascriptMigrations[id] == null) {
    throw new Error('Could not find JS migration code to run for ' + id);
  }

  const run = javascriptMigrations[id];
  return run(dbInterface, {
    fs,
    fileId: prefs.getPrefs()?.id,
  });
}

async function applySql(db, sql) {
  try {
    sqlite.execQuery(db, sql);
  } catch (e) {
    logger.log('Error applying sql:', sql);
    throw e;
  }
}

export async function applyMigration(
  db: Database,
  name: string,
  migrationsDir: string,
): Promise<void> {
  const code = await fs.readFile(fs.join(migrationsDir, name));
  if (name.match(/\.js$/)) {
    await applyJavaScript(db, getMigrationId(name));
  } else {
    await applySql(db, code);
  }
  sqlite.runQuery(db, 'INSERT INTO __migrations__ (id) VALUES (?)', [
    getMigrationId(name),
  ]);
}

// Migrations with ids after this point are additive-only, enforced by
// additive-migrations.test.ts. A database containing unknown applied
// ids from that era was touched by a newer release — including one
// whose migration id happens to sort below ids this version already
// knows (authored earlier, merged later) — and is still safe to open.
// Unknown ids from before this point indicate a corrupt or
// incompatible database.
export const ADDITIVE_ONLY_CUTOFF = 1780606215001;

function checkDatabaseValidity(
  appliedIds: number[],
  available: string[],
): void {
  // A migrated database with no migrations on disk means the install is
  // broken — without this guard every applied id would count as
  // "unknown but tolerable" below and the checks would pass vacuously
  if (available.length === 0 && appliedIds.length > 0) {
    logger.error('No migrations found on disk for a migrated database:', {
      appliedIds,
    });
    throw new Error('out-of-sync-migrations');
  }

  const allAvailableIds = available.map(getMigrationId);
  const availableIds = new Set(allAvailableIds);
  const unknownIds = appliedIds.filter(id => !availableIds.has(id));

  if (unknownIds.some(id => id <= ADDITIVE_ONLY_CUTOFF)) {
    logger.error(
      'Database is out of sync with migrations (unknown migration from before the additive-only era):',
      {
        appliedIds,
        available,
      },
    );
    throw new Error('out-of-sync-migrations');
  }

  const knownAppliedIds = appliedIds.filter(id => availableIds.has(id));

  // A database touched by a newer version must already contain every
  // migration this app knows (append-only migrations guarantee the newer
  // version knew them all). A known migration missing next to an
  // unknown one means the database is corrupt — running it now, after
  // later migrations already ran, would be unsafe.
  if (unknownIds.length > 0 && knownAppliedIds.length !== available.length) {
    logger.error(
      'Database is out of sync with migrations (missing known migration next to an unknown one):',
      {
        appliedIds,
        available,
      },
    );
    throw new Error('out-of-sync-migrations');
  }

  // Pre-cutoff migrations shipped strictly append-only, so the applied
  // ones must form an ordered prefix of the available list — a gap
  // there means the database is corrupt. Post-cutoff, a gap is just a
  // pending interleaved-id migration that `migrate` applies next.
  const preCutoffAvailableIds = allAvailableIds.filter(
    id => id <= ADDITIVE_ONLY_CUTOFF,
  );
  const preCutoffAppliedIds = knownAppliedIds.filter(
    id => id <= ADDITIVE_ONLY_CUTOFF,
  );

  // A post-cutoff migration only ever runs after the entire pre-cutoff
  // chain, so one applied next to a missing pre-cutoff migration means
  // the database is corrupt
  if (
    appliedIds.some(id => id > ADDITIVE_ONLY_CUTOFF) &&
    preCutoffAppliedIds.length !== preCutoffAvailableIds.length
  ) {
    logger.error(
      'Database is out of sync with migrations (missing pre-cutoff migration next to an applied post-cutoff one):',
      {
        appliedIds,
        available,
      },
    );
    throw new Error('out-of-sync-migrations');
  }
  for (let i = 0; i < preCutoffAppliedIds.length; i++) {
    if (preCutoffAppliedIds[i] !== preCutoffAvailableIds[i]) {
      logger.error(
        'Database is out of sync with migrations (migration id mismatch):',
        {
          appliedIds,
          available,
          missing: available.filter(
            m => !appliedIds.includes(getMigrationId(m)),
          ),
        },
      );
      throw new Error('out-of-sync-migrations');
    }
  }
}

export async function migrate(db: Database): Promise<string[]> {
  await patchBadMigrations(db);
  const appliedIds = await getAppliedMigrations(db);
  const available = await getMigrationList(MIGRATIONS_DIR);

  checkDatabaseValidity(appliedIds, available);

  const pending = getPending(appliedIds, available);

  for (const migration of pending) {
    await applyMigration(db, migration, MIGRATIONS_DIR);
  }

  return pending;
}