Repository: n8n-io/n8n
File: packages/@n8n/db/src/migrations/common/1771500000000-MigrateExternalSecretsToEntityStorage.ts (TypeScript)

Code before:
/**
 * Migrates external secrets provider settings from a single encrypted JSON blob
 * in the `settings` table to individual entity rows in the `secrets_provider_connection` table.
 *
 * Existing providers are migrated as global providers (no project access rows).
 * The old settings row is preserved for backward compatibility.
 */
export class MigrateExternalSecretsToEntityStorage1771500000000 implements IrreversibleMigration {
	private readonly cipher = Container.get(Cipher);

	async up({ escape, runQuery, logger, migrationName }: MigrationContext) {
		const settingsTable = escape.tableName('settings');
		const keyCol = escape.columnName('key');
		const valueCol = escape.columnName('value');

		// Step 1: Read the encrypted settings blob
		const rows: Array<{ value: string }> = await runQuery(
			`SELECT ${valueCol} FROM ${settingsTable} WHERE ${keyCol} = '${EXTERNAL_SECRETS_DB_KEY}';`,
		);

		if (rows.length === 0) {
			logger.info(`[${migrationName}] No external secrets settings found, skipping`);
			return;
		}

		const [row] = rows;

		// Step 2: Decrypt the blob
		let allSettings: ExternalSecretsSettings;
		try {
			const decrypted = this.cipher.decrypt(row.value);
			allSettings = jsonParse<ExternalSecretsSettings>(decrypted);
		} catch (e) {
			logger.error(
				`[${migrationName}] Failed to decrypt external secrets settings, skipping migration`,
			);
			return;
		}

		const providerNames = Object.keys(allSettings);
		if (providerNames.length === 0) {
			logger.info(`[${migrationName}] External secrets settings blob is empty, skipping`);
			return;
		}

		// Step 3: Insert each provider into secrets_provider_connection
		const connectionTable = escape.tableName('secrets_provider_connection');
		const providerKeyCol = escape.columnName('providerKey');
		const typeCol = escape.columnName('type');
		const encryptedSettingsCol = escape.columnName('encryptedSettings');


Review comment (security):
For my own understanding, how did we previously store external secrets in the settings table?
Asking because I assumed a row per external secret provider but the code implies a single row for all external secrets? 

Code after:
/**
 * Migrates external secrets provider settings from a single encrypted JSON blob
 * in the `settings` table to individual entity rows in the `secrets_provider_connection` table.
 *
 * Existing providers are migrated as global providers (no project access rows).
 * The old settings row is preserved for backward compatibility.
 */
export class MigrateExternalSecretsToEntityStorage1771500000000 implements IrreversibleMigration {
	private readonly cipher = Container.get(Cipher);

	async up(context: MigrationContext) {
		const allSettings = await this.readSettings(context);
		if (!allSettings) return;

		const providerNames = Object.keys(allSettings);
		if (providerNames.length === 0) {
			context.logger.info(
				`[${context.migrationName}] External secrets settings blob is empty, skipping`,
			);
			return;
		}

		for (const providerName of providerNames) {
			await this.migrateProvider(context, providerName, allSettings[providerName]);
		}

		context.logger.info(
			`[${context.migrationName}] Migration complete. Old settings row preserved in settings table.`,
		);
	}

	private async readSettings({
		escape,
		runQuery,
		logger,
		migrationName,
	}: MigrationContext): Promise<ExternalSecretsSettings | undefined> {
		const settingsTable = escape.tableName('settings');
		const keyCol = escape.columnName('key');
		const valueCol = escape.columnName('value');

		const rows: Array<{ value: string }> = await runQuery(
			`SELECT ${valueCol} FROM ${settingsTable} WHERE ${keyCol} = '${EXTERNAL_SECRETS_DB_KEY}';`,
		);

		if (rows.length === 0) {
			logger.info(`[${migrationName}] No external secrets settings found, skipping`);
			return undefined;
		}

		try {