Skip to content

Free, Open Source, Self-Hosted WhatsApp API Gateway

10.9 KB
Raw Blame
import { Client, ClientConfig } from 'pg';
import { DataSource, DataSourceOptions } from 'typeorm';
import { createLogger } from '../common/services/logger.service';
import { assertDataConnectionUtc, postgresUtcExtra } from './postgres-utc';
import { DeduplicateTerminalWebhookFailures1787200000000 } from './migrations/1787200000000-DeduplicateTerminalWebhookFailures';
import { repairSqliteMessageFts } from './sqlite-message-fts';

const logger = createLogger('PgBootMigrations');

// The postgres data connection runs its boot migrations while holding a session-scoped Postgres
// advisory lock, so replicas that boot at the same time serialize instead of racing DDL against
// the shared migrations ledger: the lock holder applies the chain while every other process waits
// inside pg_advisory_lock, then sees a filled ledger and applies nothing. This replaces TypeORM's
// built-in migrationsRun for that connection only (it has no cross-process serialization); the
// sqlite connection without synchronize keeps @nestjs/typeorm's default construction + initialize
// path; with synchronize (the dev and e2e setup) it is initialized here, the terminal-failure dedupe
// runs, and only then does the schema sync.
//
// Lock key in the two-int4 form — exact in JavaScript (a single bigint key would exceed
// Number.MAX_SAFE_INTEGER). The values are fixed so every process and version agrees:
//   0x4f5741 = "OWA" in ASCII bytes   0x626f6f74 = "boot" in ASCII bytes
export const POSTGRES_BOOT_MIGRATION_LOCK_KEYS: readonly [number, number] = [0x4f5741, 0x626f6f74];

// Just the pg client surface used here; doubles as the mock seam for the unit spec. Function
// properties (not method signatures) — these are callback holders, nothing binds `this`.
export interface AdvisoryLockClient {
  connect: () => Promise<unknown>;
  query: (text: string, values?: unknown[]) => Promise<unknown>;
  end: () => Promise<unknown>;
}

// Test seams over the two constructions this module performs.
export interface BootDataSourceDeps {
  createDataSource?: (options: DataSourceOptions) => DataSource;
  createLockClient?: (config: ClientConfig, onLost: (error: Error) => void) => AdvisoryLockClient;
}

type PostgresOptions = Extract<DataSourceOptions, { type: 'postgres' }>;

/**
 * dataSourceFactory for the 'data' connection. Postgres boot migrations execute here, under the
 * advisory lock, BEFORE the DataSource is handed to any provider — same ordering the built-in
 * migrationsRun gave (it finished inside DataSource.initialize()). SQLite with synchronize is
 * initialized here, deduplicates terminal webhook failures and then synchronizes; other non-postgres
 * options take @nestjs/typeorm's default path: construct only, let the wrapper initialize. The
 * wrapper also skips its own initialize() for the postgres branch because the DataSource comes
 * back already initialized, and keeps applying retryAttempts/retryDelay to this whole factory.
 *
 * It is also where the postgres data connection's UTC pin is applied and then verified, for the same
 * reason: this is the only place that connection is constructed at runtime.
 */
export async function createBootDataSource(
  options: DataSourceOptions | undefined,
  deps: BootDataSourceDeps = {},
): Promise<DataSource> {
  const createDataSource = deps.createDataSource ?? (opts => new DataSource(opts));
  const createLockClient = deps.createLockClient ?? createPgLockClient;

  if (options?.type === 'better-sqlite3' && options.synchronize) {
    const dataSource = createDataSource({ ...options, synchronize: false });
    try {
      await dataSource.initialize();
      const runner = dataSource.createQueryRunner();
      try {
        if (await runner.hasTable('webhook_delivery_failures')) {
          await new DeduplicateTerminalWebhookFailures1787200000000().up(runner);
        }
      } finally {
        await runner.release();
      }
      await dataSource.synchronize();
      // synchronize rebuilds `messages` without its rowids, so the index it kept can now name other messages.
      // Never fail boot over the index: if this repair fails, the search provider's own start-up check re-indexes
      // what it finds orphaned, and the next boot repairs the rest.
      await repairSqliteMessageFts(dataSource).catch((error: unknown) =>
        logger.error(`Failed to repair the SQLite message full-text index: ${String(error)}`),
      );
      return dataSource;
    } catch (error) {
      if (dataSource.isInitialized) await dataSource.destroy().catch(() => undefined);
      throw error;
    }
  }

  if (options?.type !== 'postgres') {
    // useFactory always resolves a full options object; the optional parameter is the library's
    // defensive typing, not a state this connection can actually boot in.
    return createDataSource(options as DataSourceOptions);
  }

  // This connection's migrations run HERE, under the lock — neutralize migrationsRun so the
  // DataSource itself never starts them unsynchronized inside initialize(). The UTC pin is merged in
  // at the same point, because this is the one place the runtime postgres data connection is built
  // (the migration CLI's own data source carries it directly).
  const build = (extra: ClientConfig | undefined): DataSource =>
    createDataSource({
      ...options,
      migrationsRun: false,
      extra: { ...extra, ...postgresUtcExtra(extra?.connectionTimeoutMillis) },
    });
  const runtimeExtra = options.extra as ClientConfig | undefined;

  // statement_timeout bounds live runtime queries, and pg sends it in the startup packet, so every
  // statement on a pool built with it inherits the limit. A backfill or index build over a large
  // table can legitimately run longer, so the chain runs on its own short-lived pool without it (as
  // the migration CLI does), and the runtime DataSource is built only once the chain is applied.
  const migrationExtra = { ...runtimeExtra };
  delete migrationExtra.statement_timeout;
  const migrator = build(migrationExtra);
  try {
    await migrator.initialize();
    // Before any migration writes a row: a connection whose UTC pin did not take stores timestamps in
    // one zone and reads them in another, which nothing downstream can detect (see postgres-utc.ts).
    await assertDataConnectionUtc(migrator);
    // A holder whose lock connection drops has lost the lock with it, so another replica can start
    // the same chain. Stop this one where it stands: tearing the migration pool down fails its
    // in-flight statement and rolls the current migration back, and the boot fails so the retry loop
    // reruns it under a new lock. A waiter needs nothing extra: its pending lock query rejects.
    let holding = false;
    let lockLost = false;
    const lockClient = createLockClient(lockClientConfig(options), () => {
      if (!holding) return;
      lockLost = true;
      void migrator.destroy().catch(() => undefined);
    });
    try {
      await lockClient.connect();
      // The holder sends nothing between lock and unlock, so a role- or database-level
      // idle_session_timeout (PostgreSQL 14+) would end the session, and the boot, mid-chain. Set
      // after connect rather than in the startup options: PostgreSQL 12 and 13 reject an unknown
      // setting there as FATAL, while here the error is harmless and swallowed.
      await lockClient.query('SET idle_session_timeout = 0').catch(() => undefined);
      await lockClient.query('SELECT pg_advisory_lock($1, $2)', [...POSTGRES_BOOT_MIGRATION_LOCK_KEYS]);
      holding = true;
      try {
        // Same transaction mode DataSource.initialize() passes for the built-in migrationsRun.
        await migrator.runMigrations({ transaction: options.migrationsTransactionMode });
        if (lockLost) throw new Error('Boot migration lock connection lost while migrating; retrying the boot');
      } finally {
        holding = false;
        // Session-scoped lock: even when the unlock call itself fails, end() below tears the
        // session — and with it the lock — down, so no crashed boot can leave it held.
        await lockClient
          .query('SELECT pg_advisory_unlock($1, $2)', [...POSTGRES_BOOT_MIGRATION_LOCK_KEYS])
          .catch(() => undefined);
      }
    } finally {
      await lockClient.end().catch(() => undefined);
    }
  } finally {
    // The migration pool never outlives this block: on success the runtime DataSource below replaces
    // it, and on failure a half-open one would stack pools across the boot retry loop. The failure
    // still fails boot via the factory's rejection; a teardown error never masks it.
    await migrator.destroy().catch(() => undefined);
  }

  const dataSource = build(runtimeExtra);
  try {
    await dataSource.initialize();
    await assertDataConnectionUtc(dataSource);
  } catch (error) {
    await dataSource.destroy().catch(() => undefined);
    throw error;
  }
  return dataSource;
}

// pg emits 'error' on the client when its socket drops while the client is not ending (a failover,
// pg_terminate_backend, an idle-timeout on the silent wait inside pg_advisory_lock). Unheard, that
// emit throws from the socket handler and exits the process. A waiter's pending lock query rejects on
// its own, so the factory's cleanup and Nest's retry loop take it from there; a holder is stopped by
// onLost, since it lost the lock with the socket.
function createPgLockClient(config: ClientConfig, onLost: (error: Error) => void): AdvisoryLockClient {
  const client = new Client(config);
  client.on('error', (error: Error) => {
    logger.warn(`Boot migration lock connection lost: ${error.message}`);
    onLost(error);
  });
  return client;
}

function lockClientConfig(options: PostgresOptions): ClientConfig {
  const extra = (options.extra ?? {}) as { connectionTimeoutMillis?: number };
  return {
    host: options.host,
    port: options.port,
    user: options.username,
    password: options.password,
    database: options.database,
    // Same ssl shape TypeORM's postgres driver forwards to pg; cast only because the two
    // packages type their TLS options independently.
    ssl: options.ssl as ClientConfig['ssl'],
    // Bound a stuck connect like the pool does (app.module's extra carries the same setting).
    connectionTimeoutMillis: extra.connectionTimeoutMillis ?? 10000,
    // No UTC pin here on purpose: this client only ever calls pg_advisory_lock/unlock, so it neither
    // binds nor reads a timestamp and its session zone cannot reach a column.
    // This client's only statements are pg_advisory_lock/unlock, and statement_timeout applies to
    // ANY command — including the wait inside pg_advisory_lock — so it must be OFF here. A config
    // `statement_timeout: 0` would NOT do it: pg drops falsy values from the startup packet, so
    // disable it via the startup `options` string instead, which also overrides any role- or
    // database-level default the server may carry. (lock_timeout never applies to advisory locks,
    // so it needs no override; idle_session_timeout is turned off by a SET after connect.)
    options: '-c statement_timeout=0',
  };
}