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

Code before:
import type { MigrationContext, ReversibleMigration } from '../migration-types';

const secretsProviderConnectionTable = 'secrets_provider_connection';
const projectSecretsProviderAccessTable = 'project_secrets_provider_access';

export class CreateSecretsProviderConnectionTables1769433700000 implements ReversibleMigration {
	async up({ schemaBuilder: { createTable, column, createIndex } }: MigrationContext) {
		// Create secrets_provider_connection table first (parent table)
		await createTable(secretsProviderConnectionTable).withColumns(
			column('id').varchar().primary.notNull,
			column('providerKey').varchar(128).notNull,
			column('type')
				.varchar(36)
				.notNull.comment(
					'Type of secrets provider. Possible values: awsSecretsManager, gcpSecretsManager, vault, azureKeyVault, infisical',
				),
			column('encryptedSettings').text.notNull,
			column('isEnabled').bool.default(false).notNull,
		).withTimestamps;

		await createIndex(secretsProviderConnectionTable, ['providerKey'], true);

		// Create project_secrets_provider_access table (join table with FKs)
		await createTable(projectSecretsProviderAccessTable)
			.withColumns(
				column('secretsProviderConnectionId').varchar().primary.notNull,
				column('projectId').varchar(36).primary.notNull,
			)
			.withTimestamps.withForeignKey('secretsProviderConnectionId', {
				tableName: secretsProviderConnectionTable,
				columnName: 'id',
				onDelete: 'CASCADE',
			})
			.withForeignKey('projectId', {
				tableName: 'project',

Review comment (suggestion):
Let's use `autoGenerate2` so it gets autogenerated and is a simple and fast numeric type

Code after:
import type { MigrationContext, ReversibleMigration } from '../migration-types';

const secretsProviderConnectionTable = 'secrets_provider_connection';
const projectSecretsProviderAccessTable = 'project_secrets_provider_access';

export class CreateSecretsProviderConnectionTables1769433700000 implements ReversibleMigration {
	async up({ schemaBuilder: { createTable, column, createIndex } }: MigrationContext) {
		// Create secrets_provider_connection table first (parent table)
		await createTable(secretsProviderConnectionTable).withColumns(
			column('id').int.primary.autoGenerate2,
			column('providerKey').varchar(128).notNull,
			column('type')
				.varchar(36)
				.notNull.comment(
					'Type of secrets provider. Possible values: awsSecretsManager, gcpSecretsManager, vault, azureKeyVault, infisical',
				),
			column('encryptedSettings').text.notNull,
			column('isEnabled').bool.default(false).notNull,
		).withTimestamps;

		await createIndex(secretsProviderConnectionTable, ['providerKey'], true);

		// Create project_secrets_provider_access table (join table with FKs)
		await createTable(projectSecretsProviderAccessTable)
			.withColumns(
				column('secretsProviderConnectionId').int.primary.notNull,
				column('projectId').varchar(36).primary.notNull,
			)
			.withTimestamps.withForeignKey('secretsProviderConnectionId', {
				tableName: secretsProviderConnectionTable,
				columnName: 'id',
				onDelete: 'CASCADE',
			})
			.withForeignKey('projectId', {
				tableName: 'project',