Skip to content

A local-first personal finance app

8.8 KB
Raw Blame
// Test-only: imported solely by additive-migrations.test.ts, which
// enforces the additive-only migration policy in CI. Nothing enforces
// that policy at runtime — see `checkDatabaseValidity` for what the app
// itself tolerates.
import type { Database } from '@jlongster/sql.js';

import * as sqlite from '#platform/server/sqlite';

// `required` = cannot insert a row without providing this column.
// `notNull` is tracked separately: NOT NULL with a DEFAULT still
// rejects explicit NULL writes. `defaultValue` is null when absent or
// an explicit NULL (they behave the same).
type ColumnFlags = {
  required: boolean;
  notNull: boolean;
  pk: boolean;
  type: string;
  defaultValue: string | null;
};

export type TableSnapshot = {
  columns: Map<string, ColumnFlags>;
  // Column lists of UNIQUE constraints/indexes, e.g. "a" or "a,b"
  uniques: Set<string>;
  // Normalized CHECK constraint expressions, sorted. PRAGMAs don't
  // expose CHECKs, so they are extracted from the canonical CREATE
  // TABLE text SQLite stores
  checks: string[];
};

// Collapses whitespace so formatting differences in SQL text don't
// register as changes
function normalizeSql(sql: string): string {
  return sql.replace(/\s+/g, ' ').trim();
}

// Quoted literals/identifiers (doubled quotes are escapes) and comments.
// The input is well-formed SQL from sqlite_master, so unterminated
// quotes/comments can't occur.
const NON_CODE =
  /'(?:[^']|'')*'|"(?:[^"]|"")*"|`(?:[^`]|``)*`|\[[^\]]*\]|--[^\n]*|\/\*[\s\S]*?\*\//g;

// Lowercases SQL tokens outside quoted literals/comments so formatting
// and keyword case differences don't register as CHECK changes, while
// preserving string literal contents (SQLite treats 'ARCHIVED' and
// 'archived' as different values).
function normalizeCheckExpression(expr: string): string {
  const normalized = normalizeSql(expr);
  let result = '';
  let lastIndex = 0;
  for (const match of normalized.matchAll(NON_CODE)) {
    const index = match.index!;
    result += normalized.slice(lastIndex, index).toLowerCase();
    result += match[0];
    lastIndex = index + match[0].length;
  }
  result += normalized.slice(lastIndex).toLowerCase();
  return result;
}

// Extracts each CHECK(...) expression by scanning to the balanced close
// paren, normalized (whitespace collapsed, SQL tokens lowercased outside
// quoted literals) so formatting differences don't register as changes.
// The scan runs on a masked copy with literals and comments blanked to
// spaces (length-preserving, so indexes line up) — a CHECK or paren
// inside one can't start or end a clause. Clause text is sliced from
// the original so literal contents survive intact.
function checkClauses(sql: string): string[] {
  const masked = sql.replace(NON_CODE, match => ' '.repeat(match.length));
  const clauses: string[] = [];
  const starts = /\bCHECK\s*\(/gi;
  let match;
  while ((match = starts.exec(masked))) {
    const start = match.index + match[0].length;
    let i = start;
    for (let depth = 1; i < masked.length && depth > 0; i++) {
      if (masked[i] === '(') {
        depth++;
      } else if (masked[i] === ')') {
        depth--;
      }
    }
    clauses.push(normalizeCheckExpression(sql.slice(start, i - 1)));
  }
  return clauses.sort();
}

// table -> its columns and constraints, from schema introspection
export type SchemaSnapshot = Map<string, TableSnapshot>;

export function snapshotSchema(db: Database): SchemaSnapshot {
  const tables = sqlite.runQuery<{ name: string; sql: string }>(
    db,
    "SELECT name, sql FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'",
    [],
    true,
  );
  return new Map(
    tables.map(({ name: table, sql }): [string, TableSnapshot] => {
      const columns = new Map(
        sqlite
          .runQuery<{
            name: string;
            type: string;
            notnull: number;
            dflt_value: string | null;
            pk: number;
          }>(db, `PRAGMA table_info("${table}")`, [], true)
          .map((column): [string, ColumnFlags] => {
            // PRAGMA reports the DEFAULT as verbatim SQL text; an
            // explicit `DEFAULT NULL` comes back as the string "NULL",
            // which is not a usable default for a NOT NULL column
            const rawDefault =
              column.dflt_value == null
                ? null
                : normalizeSql(column.dflt_value);
            const defaultValue =
              rawDefault?.toUpperCase() === 'NULL' ? null : rawDefault;
            return [
              column.name,
              {
                required:
                  column.notnull !== 0 &&
                  defaultValue == null &&
                  column.pk === 0,
                notNull: column.notnull !== 0,
                pk: column.pk !== 0,
                type: normalizeSql(column.type).toLowerCase(),
                defaultValue,
              },
            ];
          }),
      );
      const uniques = new Set(
        sqlite
          .runQuery<{ name: string; unique: number; origin: string }>(
            db,
            `PRAGMA index_list("${table}")`,
            [],
            true,
          )
          .filter(index => index.unique !== 0 && index.origin !== 'pk')
          .map(index =>
            sqlite
              .runQuery<{ name: string }>(
                db,
                `PRAGMA index_info("${index.name}")`,
                [],
                true,
              )
              .map(column => column.name)
              .sort()
              .join(','),
          ),
      );
      return [table, { columns, uniques, checks: checkClauses(sql) }];
    }),
  );
}

// A schema change is additive-only when every column that existed
// before still exists after, unchanged, and no constraint changes in
// either direction — either direction, because tightening rejects data
// older clients hold while loosening lets newer clients write data
// older schemas reject on sync.
export function findAdditiveViolations(
  before: SchemaSnapshot,
  after: SchemaSnapshot,
  nonSyncedTables: Set<string>,
): string[] {
  const violations: string[] = [];

  for (const [table, { columns: beforeColumns }] of before) {
    const afterColumns = after.get(table)?.columns;
    for (const name of beforeColumns.keys()) {
      if (!afterColumns?.has(name)) {
        violations.push(
          `column "${table}.${name}" (or its table) was removed or renamed`,
        );
      }
    }
  }

  for (const [table, { columns: afterColumns, uniques, checks }] of after) {
    const beforeTable = before.get(table);

    // The internal-table exemption applies only at creation; once a
    // table exists, older app versions write it with the column lists
    // they were built with, so changes are validated regardless of
    // syncedness
    if (!beforeTable && nonSyncedTables.has(table)) {
      continue;
    }

    for (const [name, column] of afterColumns) {
      const prev = beforeTable?.columns.get(name);
      if (!prev) {
        if (column.required) {
          violations.push(
            `new column "${table}.${name}" is NOT NULL without a DEFAULT`,
          );
        }
        continue;
      }
      // A table rebuild can change an existing column in place; any
      // attribute change breaks one side of the version skew
      const changed = (
        ['notNull', 'pk', 'type', 'defaultValue'] as const
      ).filter(key => column[key] !== prev[key]);
      if (changed.length > 0) {
        violations.push(
          `existing column "${table}.${name}" changed: ${changed.join(', ')}`,
        );
      }
    }

    // Constraint changes only matter on existing tables — a brand-new
    // table gets the same constraints on every client that runs its
    // migration, so there is no version skew to protect against
    if (beforeTable) {
      for (const unique of uniques) {
        if (!beforeTable.uniques.has(unique)) {
          violations.push(
            `table "${table}" gained a UNIQUE constraint on (${unique})`,
          );
        }
      }
      for (const unique of beforeTable.uniques) {
        if (!uniques.has(unique)) {
          violations.push(
            `table "${table}" lost a UNIQUE constraint on (${unique})`,
          );
        }
      }

      if (beforeTable.checks.join(';') !== checks.join(';')) {
        violations.push(`table "${table}" changed its CHECK constraints`);
      }
    }

    if (!beforeTable) {
      // Sync addresses rows by their `id` column (see `apply` in
      // #server/sync), so a new synced table needs exactly that as its
      // primary key — no other name, no composite keys
      const pks = [...afterColumns]
        .filter(([, c]) => c.pk)
        .map(([name]) => name);
      if (pks.length !== 1 || pks[0] !== 'id') {
        violations.push(
          `new table "${table}" must have a single primary key named "id"`,
        );
      }
    }
  }

  return violations;
}