Infisical is the open-source platform for securing secrets, certificates, access, and AI Agents.
TypeScript acmeagentsai-agentscertificate-managementdevsecopsenvironment-variableskubernetesopen-sourcepampkipostgresprivate-caprivileged-access-managementsecret-managementsecret-managersecretssecrets-managementsecuritysecurity-toolsvault
import crypto from "node:crypto";
import os from "node:os";
import path from "node:path";
import { type ClickHouseClient } from "@clickhouse/client";
import dotenv from "dotenv";
import { Knex } from "knex";
import { Logger } from "pino";
import { getConfig } from "@app/lib/config/env";
import { applyJitter, delay } from "@app/lib/delay";
import { getMigrationBootState } from "./auto-start-migrations-fns";
import { ensureClickHouseSchema } from "./db/clickhouse-migration-runner";
import {
acquireSanitizedSchemaLock,
createSanitizedSchema,
dropSanitizedSchema,
grantSanitizedSchemaAccess
} from "./db/sanitized-schema";
import { PgSqlLock } from "./keystore/keystore";
const SANITIZED_SCHEMA_ERROR = "SANITIZED_SCHEMA_ERROR";
dotenv.config();
type TArgs = {
auditLogDb?: Knex;
applicationDb: Knex;
clickhouseClient?: ClickHouseClient | null;
logger: Logger;
};
const isProduction = process.env.NODE_ENV === "production";
const migrationConfig = {
directory: path.join(__dirname, "./db/migrations"),
loadExtensions: [".mjs", ".ts"],
tableName: "infisical_migrations"
};
const getImageVersionTag = (): string => {
const version = getConfig().INFISICAL_PLATFORM_VERSION;
if (!version) {
return "development";
}
return version.startsWith("v") ? version : `v${version}`;
};
const getMigrationDateRange = (migrationNames: string[]): { earliest: string; latest: string } | null => {
if (!migrationNames.length) {
return null;
}
const timestamps = migrationNames
.map((name) => name.match(/^(\d{8})/)?.[1])
.filter((timestamp): timestamp is string => Boolean(timestamp))
.sort();
if (!timestamps.length) {
return null;
}
const formatDate = (timestamp: string) =>
`${timestamp.slice(0, 4)}-${timestamp.slice(4, 6)}-${timestamp.slice(6, 8)}`;
return {
earliest: formatDate(timestamps[0]),
latest: formatDate(timestamps[timestamps.length - 1])
};
};
const logUnknownAppliedMigrations = ({
databaseName,
logger,
unknownAppliedMigrationNames
}: {
databaseName: string;
logger: Logger;
unknownAppliedMigrationNames: string[];
}) => {
const imageVersion = getImageVersionTag();
const dateRange = getMigrationDateRange(unknownAppliedMigrationNames);
const sections = [
`Database has applied migrations that this image does not bundle [database=${databaseName}] [imageVersion=${imageVersion}].`,
`This image has nothing pending to apply, so startup migrations are skipped.`,
`This is expected during rolling deployments or rollbacks when a peer instance on a different image has already applied them.`
];
if (dateRange) {
sections.push(
`The database contains migrations dated up to ${dateRange.latest} that this image does not bundle. If this is not a rolling deployment, verify the intended image version is deployed — an Infisical image released on or after ${dateRange.latest} should include them.`
);
} else {
sections.push(`If this is not a rolling deployment, verify that the intended image version is deployed.`);
}
logger.warn(
{
databaseName,
imageVersion,
unknownAppliedMigrationCount: unknownAppliedMigrationNames.length,
unknownAppliedMigrationDateRange: dateRange,
unknownAppliedMigrationNames: unknownAppliedMigrationNames.slice(-5)
},
sections.join(" ")
);
};
const throwInvalidMigrationHistory = ({
databaseName,
pendingMigrationNames,
unknownAppliedMigrationNames
}: {
databaseName: string;
pendingMigrationNames: string[];
unknownAppliedMigrationNames: string[];
}): never => {
const imageVersion = getImageVersionTag();
const unknownAppliedList = unknownAppliedMigrationNames.join(", ") || "none";
const pendingList = pendingMigrationNames.join(", ") || "none";
const unknownDateRange = getMigrationDateRange(unknownAppliedMigrationNames);
const pendingDateRange = getMigrationDateRange(pendingMigrationNames);
const sections = [
`Database migration history does not match this image [database=${databaseName}] [imageVersion=${imageVersion}].`,
`This usually means the database was migrated by a different (typically newer) Infisical image than the one currently running — for example after a rollback or a mixed-version rollout.`,
`Unknown applied migrations (in DB but not in this image, count=${unknownAppliedMigrationNames.length}): ${unknownAppliedList}.`,
`Pending bundled migrations (in this image but not yet applied, count=${pendingMigrationNames.length}): ${pendingList}.`
];
if (unknownDateRange && pendingDateRange) {
sections.push(
`Timeline: this image's pending migrations are dated ${pendingDateRange.earliest} to ${pendingDateRange.latest}; the unknown applied migrations in the database are dated ${unknownDateRange.earliest} to ${unknownDateRange.latest}.`
);
} else if (unknownDateRange) {
sections.push(
`Timeline: the unknown applied migrations in the database are dated ${unknownDateRange.earliest} to ${unknownDateRange.latest}.`
);
}
if (unknownDateRange) {
sections.push(
`To resolve: deploy an Infisical image released on or after ${unknownDateRange.latest} so its bundled migrations include the unknown ones above, or restore the database to a state matching this image's migrations.`
);
} else {
sections.push(
`To resolve: deploy a newer Infisical image whose bundled migrations include the unknown ones above, or restore the database to a state matching this image's migrations.`
);
}
throw new Error(sections.join(" "));
};
const getLockTableName = (tableName: string): string => {
return `${tableName}_lock`;
};
const getStartupLockTableName = (tableName: string): string => {
return `${tableName}_startup_lock`;
};
const isMigrationInitialized = async (db: Knex, logger: Logger): Promise<boolean> => {
const { tableName } = migrationConfig;
const lockTableName = getLockTableName(tableName);
const startupLockTableName = getStartupLockTableName(tableName);
const lockTableExists = await db.schema.hasTable(lockTableName);
const startupLockTableExists = await db.schema.hasTable(startupLockTableName);
if (!lockTableExists) {
logger.debug("Migration tables not initialized");
return false;
}
if (!startupLockTableExists) {
logger.debug("Startup lock table not initialized");
return false;
}
const lockRowCount = await db(lockTableName).count().first();
const startupLockRowCount = await db(startupLockTableName).count().first();
const isInitialized = (lockRowCount?.count as number) > 0 && (startupLockRowCount?.count as number) > 0;
logger.debug(
`Migration tables initialized, lock table: ${lockRowCount?.count}, startup lock table: ${startupLockRowCount?.count}`
);
return isInitialized;
};
const createStartupLockTable = async (tableName: string, db: Knex): Promise<void> => {
await db.schema.createTable(tableName, (t) => {
t.increments("index").primary();
t.integer("is_locked");
t.string("session_id").nullable();
t.string("node").nullable();
t.timestamp("heartbeat_updated_at").nullable();
});
};
const insertStartupLockRowIfNeeded = async (tableName: string, db: Knex): Promise<void> => {
const data: unknown[] = await db.select("*").from(tableName);
if (!data.length) {
await db.from(tableName).insert({ is_locked: 0 });
}
};
const ensureStartupLockTable = async (db: Knex, logger: Logger): Promise<void> => {
const { tableName } = migrationConfig;
const startupLockTableName = getStartupLockTableName(tableName);
const tableExists = await db.schema.hasTable(startupLockTableName);
if (!tableExists) {
await createStartupLockTable(startupLockTableName, db);
logger.info(`Startup lock table created: ${startupLockTableName}`);
}
await insertStartupLockRowIfNeeded(startupLockTableName, db);
};
const ensureMigrationTables = async (db: Knex, logger: Logger): Promise<void> => {
const isInitialized = await isMigrationInitialized(db, logger);
if (isInitialized) {
// Even if migration tables are initialized, ensure the startup lock table exists
await ensureStartupLockTable(db, logger);
return;
}
await db.transaction(async (tx) => {
// This is to prevent multiple instances from initializing the migration table at the same time.
// While the Knex migration system comes with its own lock mechanism, but it doesn't handle the
// case where multiple instances are trying to initialize the migration table at the same time.
// Therefore, we need to use a separate lock to prevent multiple instances from initializing the migration table at the same time.
await tx.raw("SELECT pg_advisory_xact_lock(?)", [PgSqlLock.BootUpMigration]);
if (await isMigrationInitialized(tx, logger)) {
// After we acquire the lock, check again and see if the migration table is still not initialized.
// It's possible multiple instances sees the missing migration table and try to initialize it at the same time.
// But only the one instance which acquired the lock will be able to initialize the migration table.
logger.info("Migration tables already initialized by another instance, skipping initialization.");
return;
}
// The currentVersion will call ensureTable, end up creating the migration table, lock table,
// and insert the first row into the lock table.
await tx.migrate.currentVersion(migrationConfig);
logger.info("Migration tables initialized");
// Ensure the startup lock table is created and initialized
await ensureStartupLockTable(tx, logger);
logger.info("Startup lock table initialized");
});
};
/**
* The reason we need to use a startup lock is to ensure that only one instance runs migrations at a time.
* In our production environment or customer's self-hosted environment, there could be multiple instances of the application running.
* When a new deployment is rolling out, only one could acquire the Knex migration lock to run the migrations.
* With the other instances, they will see db already locked error. This is not great because it creates a lot of noise for the On-call team.
*
* Why not just use `pg_advisory_xact_lock` as we did previously?
* Because `pg_advisory_xact_lock` will block the transaction until the lock is acquired.
* We have new migration files that uses CREATE INDEX CONCURRENTLY, it cannot be run in a transaction.
* Also, it will wait untill all the current transactions are committed.
* If there are instances holding `pg_advisory_xact_lock` for migration (it will be in a transaction that's different from the migration transaction)
* and CREATE INDEX CONCURRENTLY (cannot be run in a transaction) runs in side the migration, we will end up with a deadlock.
*
* Here's how it works:
* 1. We acquire the lock by updating the startup lock table (similar to how knex's migration lock works).
* 2. We start a heartbeat interval to update the `heartbeat_updated_at` column.
* 3. We run the migrations.
* 4. We release the lock by updating the startup lock table.
* 5. We stop the heartbeat interval.
*
* If the instance crashes, the heartbeat will stop and the lock will be released after the timeout.
* If the instance is still running, the heartbeat will keep updating and the lock will not be released.
*/
const withStartupLock = async (db: Knex, logger: Logger, doMigrations: () => Promise<void>): Promise<void> => {
const { tableName } = migrationConfig;
const startupLockTableName = getStartupLockTableName(tableName);
const sessionId = crypto.randomUUID();
const node = `${os.hostname()}-${process.pid}`;
const ACQUIRE_LOCK_TIMEOUT_MS = 5 * 60 * 1000; // 5 minutes timeout to acquire lock
const HEARTBEAT_INTERVAL_MS = 5000; // Update heartbeat every 5 seconds
const BASE_RETRY_DELAY_MS = 2000; // Base delay of 2 seconds between retries
// Consider lock stale if heartbeat is older than 3x the heartbeat interval (15 seconds)
const STALE_HEARTBEAT_THRESHOLD_MS = HEARTBEAT_INTERVAL_MS * 3;
let lockAcquired = false;
let heartbeatInterval: NodeJS.Timeout | null = null;
const acquireLock = async (): Promise<boolean> => {
const result = await db(startupLockTableName)
// 1. is_locked = 0 (lock is free), OR
.where({ is_locked: 0 })
// 2. is_locked = 1 AND heartbeat is stale (previous instance crashed)
.orWhere((qb) => {
void qb.where({ is_locked: 1 }).andWhere((subQb) => {
void subQb
.whereNull("heartbeat_updated_at")
.orWhere(
"heartbeat_updated_at",
"<",
db.raw("NOW() - (? || ' milliseconds')::INTERVAL", [STALE_HEARTBEAT_THRESHOLD_MS])
);
});
})
.update({
is_locked: 1,
session_id: sessionId,
node,
heartbeat_updated_at: db.fn.now()
});
return result > 0;
};
const startHeartbeat = () => {
heartbeatInterval = setInterval(() => {
db(startupLockTableName)
.where({ session_id: sessionId, node, is_locked: 1 })
.update({
heartbeat_updated_at: db.fn.now()
})
.catch((err) => {
logger.error(err, "Failed to update heartbeat");
});
}, HEARTBEAT_INTERVAL_MS);
};
const stopHeartbeat = () => {
if (heartbeatInterval) {
clearInterval(heartbeatInterval);
heartbeatInterval = null;
}
};
const releaseLock = async () => {
try {
await db(startupLockTableName).where({ session_id: sessionId, node, is_locked: 1 }).update({
is_locked: 0,
session_id: null,
node: null,
heartbeat_updated_at: null
});
logger.info(`Startup lock released [sessionId=${sessionId}] [node=${node}]`);
} catch (err) {
logger.error(err, "Failed to release startup lock");
}
};
// Try to acquire the lock with timeout-based retries
const acquireLockStartTime = Date.now();
let attemptCount = 0;
while (Date.now() - acquireLockStartTime < ACQUIRE_LOCK_TIMEOUT_MS) {
attemptCount += 1;
// eslint-disable-next-line no-await-in-loop -- Intentional retry loop with timeout
lockAcquired = await acquireLock();
if (lockAcquired) {
const elapsedTime = Date.now() - acquireLockStartTime;
logger.info(
`Startup lock acquired [sessionId=${sessionId}] [node=${node}] [attempts=${attemptCount}] [elapsedTime=${elapsedTime}ms]`
);
break;
}
// Check if we still have time before timeout
const elapsedTime = Date.now() - acquireLockStartTime;
const remainingTime = ACQUIRE_LOCK_TIMEOUT_MS - elapsedTime;
if (remainingTime <= 0) {
break;
}
// Lock not acquired, wait with random jitter before retrying
// Don't wait longer than the remaining timeout
const retryDelay = Math.min(Math.floor(applyJitter(BASE_RETRY_DELAY_MS)), remainingTime);
logger.debug(
`Startup lock not available, retrying in ${retryDelay}ms [attempt=${attemptCount}] [elapsedTime=${elapsedTime}ms] [remainingTime=${remainingTime}ms] [node=${node}]`
);
// eslint-disable-next-line no-await-in-loop -- Intentional retry delay
await delay(retryDelay);
}
if (!lockAcquired) {
const elapsedTime = Date.now() - acquireLockStartTime;
throw new Error(
`Failed to acquire startup lock within ${ACQUIRE_LOCK_TIMEOUT_MS}ms timeout [attempts=${attemptCount}] [elapsedTime=${elapsedTime}ms]. Another instance may be running migrations.`
);
}
startHeartbeat();
try {
// Run migrations while heartbeat is updating
await doMigrations();
logger.info("Migrations completed successfully");
} finally {
// Always stop heartbeat and release lock
stopHeartbeat();
await releaseLock();
}
};
export const runMigrations = async ({ applicationDb, auditLogDb, clickhouseClient, logger }: TArgs) => {
const generateSanitizedSchema = process.env.GENERATE_SANITIZED_SCHEMA === "true";
const failOnSanitizedSchemaError = process.env.FAIL_ON_SANITIZED_SCHEMA_ERROR === "true";
// Only API should run migrations. Workers don't run them to prevent
// API nodes to fall behind the schema and start failing while they are not updated.
if (!getConfig().isApiRunModeEnabled) {
const bootState = await getMigrationBootState({ db: applicationDb, migrationConfig });
if (bootState.direction === "invalid") {
throwInvalidMigrationHistory({
databaseName: "application",
pendingMigrationNames: bootState.pendingMigrationNames,
unknownAppliedMigrationNames: bootState.unknownAppliedMigrationNames
});
}
if (bootState.direction === "behind") {
logger.warn(
`Skipping migrations: not an api run mode [direction=behind] [pendingCount=${bootState.pendingMigrationNames.length}]. Waiting on an api pod to apply them.`
);
} else {
logger.info(`Skipping migrations: not an api run mode [direction=${bootState.direction}]`);
}
return;
}
try {
// akhilmhdh(Feb 10 2025): 2 years from now remove this
if (isProduction) {
const migrationTable = migrationConfig.tableName;
const hasMigrationTable = await applicationDb.schema.hasTable(migrationTable);
if (hasMigrationTable) {
const firstFile = (await applicationDb(migrationTable).where({}).first()) as { name: string };
if (firstFile?.name?.includes(".ts")) {
await applicationDb(migrationTable).update({
name: applicationDb.raw("REPLACE(name, '.ts', '.mjs')")
});
}
}
if (auditLogDb) {
const hasMigrationTableInAuditLog = await auditLogDb.schema.hasTable(migrationTable);
if (hasMigrationTableInAuditLog) {
const firstFile = (await auditLogDb(migrationTable).where({}).first()) as { name: string };
if (firstFile?.name?.includes(".ts")) {
await auditLogDb(migrationTable).update({
name: auditLogDb.raw("REPLACE(name, '.ts', '.mjs')")
});
}
}
}
}
// Ensure ClickHouse audit_logs table exists if configured
if (clickhouseClient) {
logger.info("Ensuring ClickHouse audit_logs table exists ...");
try {
const cfg = getConfig();
await ensureClickHouseSchema({
client: clickhouseClient,
tableName: cfg.CLICKHOUSE_AUDIT_LOG_TABLE_NAME,
engine: cfg.CLICKHOUSE_AUDIT_LOG_ENGINE,
logger
});
} catch (clickhouseErr) {
logger.error(clickhouseErr, "ClickHouse schema setup failed");
process.exit(1);
}
logger.info("Finished ensuring ClickHouse audit_logs table exists.");
} else {
logger.info("No ClickHouse client configured: Skipping ClickHouse audit_logs table creation.");
}
const applicationMigrationBootState = await getMigrationBootState({ db: applicationDb, migrationConfig });
if (applicationMigrationBootState.direction === "ahead") {
logUnknownAppliedMigrations({
databaseName: "application",
logger,
unknownAppliedMigrationNames: applicationMigrationBootState.unknownAppliedMigrationNames
});
return;
}
if (applicationMigrationBootState.direction === "current") {
logger.info("No migrations pending: Skipping migration process.");
if (generateSanitizedSchema) {
const sanitizedSchemaRole = process.env.SANITIZED_SCHEMA_ROLE;
try {
await applicationDb.transaction(async (tx) => {
const isLocked = await acquireSanitizedSchemaLock({ db: tx, logger });
if (isLocked) {
await dropSanitizedSchema({ db: tx, logger });
await createSanitizedSchema({ db: tx, logger });
if (sanitizedSchemaRole) {
await grantSanitizedSchemaAccess({ db: tx, logger, role: sanitizedSchemaRole });
}
logger.info("Finished sanitized schema generation.");
}
});
} catch (err) {
logger.error(
{ err, errorId: SANITIZED_SCHEMA_ERROR, phase: "recreate" },
`${SANITIZED_SCHEMA_ERROR}: Failed to recreate sanitized schema`
);
if (failOnSanitizedSchemaError) {
throw err;
}
}
}
return;
}
if (applicationMigrationBootState.direction === "invalid") {
throwInvalidMigrationHistory({
databaseName: "application",
pendingMigrationNames: applicationMigrationBootState.pendingMigrationNames,
unknownAppliedMigrationNames: applicationMigrationBootState.unknownAppliedMigrationNames
});
}
if (auditLogDb) {
const auditLogMigrationBootState = await getMigrationBootState({ db: auditLogDb, migrationConfig });
if (auditLogMigrationBootState.direction === "ahead") {
logUnknownAppliedMigrations({
databaseName: "audit-log",
logger,
unknownAppliedMigrationNames: auditLogMigrationBootState.unknownAppliedMigrationNames
});
} else if (auditLogMigrationBootState.direction === "current") {
logger.info("No audit log migrations pending: Skipping migration process.");
} else if (auditLogMigrationBootState.direction === "invalid") {
throwInvalidMigrationHistory({
databaseName: "audit-log",
pendingMigrationNames: auditLogMigrationBootState.pendingMigrationNames,
unknownAppliedMigrationNames: auditLogMigrationBootState.unknownAppliedMigrationNames
});
} else {
await ensureMigrationTables(auditLogDb, logger);
logger.info("Running audit log migrations.");
// Use startup lock to ensure only one instance runs migrations at a time
await withStartupLock(auditLogDb, logger, async () => {
const lockedAuditLogMigrationBootState = await getMigrationBootState({ db: auditLogDb, migrationConfig });
if (lockedAuditLogMigrationBootState.direction === "ahead") {
logUnknownAppliedMigrations({
databaseName: "audit-log",
logger,
unknownAppliedMigrationNames: lockedAuditLogMigrationBootState.unknownAppliedMigrationNames
});
return;
}
if (lockedAuditLogMigrationBootState.direction === "current") {
logger.info("No audit log migrations pending: Applied by previous instance. Skipping migration process.");
return;
}
if (lockedAuditLogMigrationBootState.direction === "invalid") {
throwInvalidMigrationHistory({
databaseName: "audit-log",
pendingMigrationNames: lockedAuditLogMigrationBootState.pendingMigrationNames,
unknownAppliedMigrationNames: lockedAuditLogMigrationBootState.unknownAppliedMigrationNames
});
}
await auditLogDb.migrate.latest(migrationConfig);
});
logger.info("Finished audit log migrations.");
}
}
await ensureMigrationTables(applicationDb, logger);
logger.info("Running application migrations.");
// Use startup lock to ensure only one instance runs migrations at a time
await withStartupLock(applicationDb, logger, async () => {
const lockedApplicationMigrationBootState = await getMigrationBootState({ db: applicationDb, migrationConfig });
if (lockedApplicationMigrationBootState.direction === "ahead") {
logUnknownAppliedMigrations({
databaseName: "application",
logger,
unknownAppliedMigrationNames: lockedApplicationMigrationBootState.unknownAppliedMigrationNames
});
return;
}
if (lockedApplicationMigrationBootState.direction === "current") {
logger.info("No application migrations pending: Applied by previous instance. Skipping migration process.");
return;
}
if (lockedApplicationMigrationBootState.direction === "invalid") {
throwInvalidMigrationHistory({
databaseName: "application",
pendingMigrationNames: lockedApplicationMigrationBootState.pendingMigrationNames,
unknownAppliedMigrationNames: lockedApplicationMigrationBootState.unknownAppliedMigrationNames
});
}
if (generateSanitizedSchema) await dropSanitizedSchema({ db: applicationDb, logger });
await applicationDb.migrate.latest(migrationConfig);
});
logger.info("Finished application migrations.");
// we create the sanitized schema after migrations to avoid conflicts with the migrations
if (generateSanitizedSchema) {
const sanitizedSchemaRole = process.env.SANITIZED_SCHEMA_ROLE;
try {
await createSanitizedSchema({ db: applicationDb, logger });
if (sanitizedSchemaRole) {
await grantSanitizedSchemaAccess({ db: applicationDb, logger, role: sanitizedSchemaRole });
}
logger.info("Finished sanitized schema generation.");
} catch (err) {
logger.error(
{ err, errorId: SANITIZED_SCHEMA_ERROR, phase: "create" },
`${SANITIZED_SCHEMA_ERROR}: Failed to create sanitized schema after migrations`
);
if (failOnSanitizedSchemaError) {
throw err;
}
}
}
} catch (err) {
logger.error(err, "Boot up migration failed");
process.exit(1);
}
};