// ---------------------------------------------------------------------------// libSQL boot migration: rewrite `bigint` columns that an earlier build left in// SQLite's INTEGER storage class (issue #1771).//// A `bigint` column is stored on SQLite as a BLOB holding the value's decimal// digits — that is what drizzle's `blob({ mode: "bigint" })` writes, and its// row mapper reads the blob back with `Buffer.from(...)`. Before these columns// carried the `bigint` flag they were plain numbers, which SQLite kept in the// INTEGER storage class. SQLite does not rewrite existing rows when a column's// declared type changes, so an install written by that build still holds// integers today.//// Reading one back now reaches `Buffer.from(<number>)`, which throws// `ERR_INVALID_ARG_TYPE`. The throw is in the ROW mapper, so it fails the whole// `findMany` rather than one field: on a database whose `connection` rows// carried an OAuth `expires_at` from that era, EVERY catalog read threw and the// MCP gateway served an empty tool list even though the integrations were all// still saved.//// The rewrite is deliberately narrow. It touches only the columns the schema// declares `bigint`, and within them only values whose storage class is// `integer` or `real` — the shapes the mapper cannot read. Values already// stored as BLOB or TEXT (TEXT is what the v1→v2 migration writes, and the// mapper reads it fine) are left exactly as they are. `CAST(x AS BLOB)`// converts through TEXT, so the stored bytes end up identical to what the ORM// writes today. Idempotent: after a run no column is in a numeric storage// class, so a second run updates nothing.// --------------------------------------------------------------------------- import { Effect } from "effect"; import { coreSchema } from "./core-schema";import {  DataMigrationError,  type SqliteDataMigration,  type SqliteDataMigrationClient,} from "./sqlite-data-migrations"; const MIGRATION_NAME = "2026-08-28-bigint-storage-class"; export interface BigintStorageClassColumn {  /** SQL table name. */  readonly table: string;  /** SQL column name. */  readonly column: string;} /** * Every `bigint` column in the core schema, by SQL name. * * Derived from the schema rather than hand-listed so a column added later can * never be silently missed. Scanning a column that never held an integer is a * no-op, so over-coverage costs one indexed-free table scan at first boot. */export const LEGACY_BIGINT_STORAGE_CLASS_COLUMNS: readonly BigintStorageClassColumn[] =  Object.values(coreSchema).flatMap((table) =>    Object.values(table.columns)      .filter((column) => column.type === "bigint")      .map((column) => ({ table: table.names.sql, column: column.names.sql })),  ); const execute = (  client: SqliteDataMigrationClient,  stmt: string | { readonly sql: string; readonly args: readonly unknown[] },) =>  Effect.tryPromise({    try: () => client.execute(stmt),    catch: (cause) => new DataMigrationError({ migration: MIGRATION_NAME, cause }),  }); /** SQLite identifiers are quoted, not parameterized. Every name here comes from *  the compiled-in schema, so this always matches; anything else is refused *  rather than interpolated. */const quoteIdentifier = (name: string): Effect.Effect<string, DataMigrationError> =>  /^[A-Za-z_][A-Za-z0-9_]*$/.test(name)    ? Effect.succeed(`"${name}"`)    : Effect.fail(        new DataMigrationError({          migration: MIGRATION_NAME,          cause: `Refusing to interpolate SQL identifier: ${name}`,        }),      ); const hasColumn = (  client: SqliteDataMigrationClient,  table: string,  quotedTable: string,  column: string,): Effect.Effect<boolean, DataMigrationError> =>  execute(client, {    sql: "SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?",    args: [table],  }).pipe(    Effect.flatMap((tables) =>      tables.rows.length === 0        ? Effect.succeed(false)        : execute(client, `PRAGMA table_info(${quotedTable})`).pipe(            Effect.map((info) => info.rows.some((row) => row["name"] === column)),          ),    ),  ); /** * Convert legacy INTEGER/REAL values in the schema's `bigint` columns to the * blob representation the ORM reads. * * Returns the number of rows rewritten. Wrapped in BEGIN…COMMIT so a mid-run * failure leaves the database untouched and the (unstamped) migration re-runs * cleanly on the next boot. */export const runSqliteBigintStorageClassMigration = (  client: SqliteDataMigrationClient,): Effect.Effect<number, DataMigrationError> =>  Effect.gen(function* () {    const pending: { readonly sql: string; readonly count: number }[] = [];     for (const target of LEGACY_BIGINT_STORAGE_CLASS_COLUMNS) {      const table = yield* quoteIdentifier(target.table);      const column = yield* quoteIdentifier(target.column);      if (!(yield* hasColumn(client, target.table, table, target.column))) continue;       // `typeof()` reports the STORAGE class, which is what the mapper trips      // over — the declared column type is irrelevant to SQLite here.      const predicate = `typeof(${column}) IN ('integer', 'real')`;       const counted = yield* execute(        client,        `SELECT COUNT(*) AS n FROM ${table} WHERE ${predicate}`,      );      const count = Number(counted.rows[0]?.["n"] ?? 0);      if (count === 0) continue;       pending.push({        sql: `UPDATE ${table} SET ${column} = CAST(CAST(${column} AS INTEGER) AS BLOB) WHERE ${predicate}`,        count,      });    }     if (pending.length === 0) return 0;     const applyAll = Effect.gen(function* () {      let converted = 0;      for (const statement of pending) {        yield* execute(client, statement.sql);        converted += statement.count;      }      yield* execute(client, "COMMIT");      return converted;    });     yield* execute(client, "BEGIN");    return yield* applyAll.pipe(      Effect.tapError(() => execute(client, "ROLLBACK").pipe(Effect.ignore)),      // `tapError` only fires on a typed failure, not on fiber interruption      // (e.g. the boot sequence timing out mid-migration) — so without this an      // interrupted run can leave the transaction open. Never fires on success:      // COMMIT has already run by then.      Effect.onInterrupt(() => execute(client, "ROLLBACK").pipe(Effect.ignore)),    );  }); /** Registry entry for the SQLite hosts' boot-time data-migration ledger. */export const bigintStorageClassSqliteMigration: SqliteDataMigration = {  name: MIGRATION_NAME,  run: (client) => runSqliteBigintStorageClassMigration(client).pipe(Effect.asVoid),};