PSA/ee/server/migrations/20251031130000_create_install_config_tables.cjs

/** EE-only migration: tenant_extension_install config + secrets */

/** @param {import('knex').Knex} knex */
exports.up = async function up(knex) {
  // Use IF NOT EXISTS to avoid aborting the surrounding migration transaction when the table already exists.
  await knex.raw(`
    CREATE TABLE IF NOT EXISTS tenant_extension_install_config (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      install_id uuid NOT NULL REFERENCES tenant_extension_install(id) ON DELETE CASCADE,
      tenant_id varchar(255) NOT NULL,
      config jsonb NOT NULL DEFAULT '{}'::jsonb,
      providers jsonb NOT NULL DEFAULT '[]'::jsonb,
      version varchar(255) NOT NULL DEFAULT gen_random_uuid()::text,
      created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `);

  // Best-effort: ensure expected columns exist for environments where the table was created manually or partially.
  const ensureConfigColumn = async (name, addColumn) => {
    const has = await knex.schema.hasColumn('tenant_extension_install_config', name);
    if (!has) {
      await knex.schema.alterTable('tenant_extension_install_config', addColumn);
    }
  };

  await ensureConfigColumn('id', (t) => t.uuid('id').defaultTo(knex.raw('gen_random_uuid()')));
  await ensureConfigColumn('install_id', (t) => t.uuid('install_id'));
  await ensureConfigColumn('tenant_id', (t) => t.string('tenant_id'));
  await ensureConfigColumn('config', (t) => t.jsonb('config').notNullable().defaultTo('{}'));
  await ensureConfigColumn('providers', (t) => t.jsonb('providers').notNullable().defaultTo('[]'));
  await ensureConfigColumn('version', (t) => t.string('version').notNullable().defaultTo(knex.raw("gen_random_uuid()::text")));
  await ensureConfigColumn('created_at', (t) => t.timestamp('created_at').notNullable().defaultTo(knex.fn.now()));
  await ensureConfigColumn('updated_at', (t) => t.timestamp('updated_at').notNullable().defaultTo(knex.fn.now()));

  // Indexes/constraints (safe if they already exist).
  await knex.raw('CREATE UNIQUE INDEX IF NOT EXISTS tenant_extension_install_config_install_id_unique ON tenant_extension_install_config (install_id)');
  await knex.raw('CREATE INDEX IF NOT EXISTS tenant_extension_install_config_tenant_id_idx ON tenant_extension_install_config (tenant_id)');
  await knex.raw('CREATE INDEX IF NOT EXISTS tenant_extension_install_config_install_id_version_idx ON tenant_extension_install_config (install_id, version)');

  await knex.raw(`
    CREATE TABLE IF NOT EXISTS tenant_extension_install_secrets (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      install_id uuid NOT NULL REFERENCES tenant_extension_install(id) ON DELETE CASCADE,
      tenant_id varchar(255) NOT NULL,
      ciphertext text NOT NULL,
      algorithm varchar(255) NOT NULL DEFAULT 'inline/base64',
      transit_key varchar(255) NULL,
      transit_mount varchar(255) NULL,
      version varchar(255) NULL,
      expires_at timestamptz NULL,
      created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `);

  const ensureSecretsColumn = async (name, addColumn) => {
    const has = await knex.schema.hasColumn('tenant_extension_install_secrets', name);
    if (!has) {
      await knex.schema.alterTable('tenant_extension_install_secrets', addColumn);
    }
  };

  await ensureSecretsColumn('id', (t) => t.uuid('id').defaultTo(knex.raw('gen_random_uuid()')));
  await ensureSecretsColumn('install_id', (t) => t.uuid('install_id'));
  await ensureSecretsColumn('tenant_id', (t) => t.string('tenant_id'));
  await ensureSecretsColumn('ciphertext', (t) => t.text('ciphertext'));
  await ensureSecretsColumn('algorithm', (t) => t.string('algorithm').notNullable().defaultTo('inline/base64'));
  await ensureSecretsColumn('transit_key', (t) => t.string('transit_key').nullable());
  await ensureSecretsColumn('transit_mount', (t) => t.string('transit_mount').nullable());
  await ensureSecretsColumn('version', (t) => t.string('version').nullable());
  await ensureSecretsColumn('expires_at', (t) => t.timestamp('expires_at').nullable());
  await ensureSecretsColumn('created_at', (t) => t.timestamp('created_at').notNullable().defaultTo(knex.fn.now()));
  await ensureSecretsColumn('updated_at', (t) => t.timestamp('updated_at').notNullable().defaultTo(knex.fn.now()));

  await knex.raw('CREATE UNIQUE INDEX IF NOT EXISTS tenant_extension_install_secrets_install_id_unique ON tenant_extension_install_secrets (install_id)');
  await knex.raw('CREATE INDEX IF NOT EXISTS tenant_extension_install_secrets_tenant_id_idx ON tenant_extension_install_secrets (tenant_id)');
  await knex.raw('CREATE INDEX IF NOT EXISTS tenant_extension_install_secrets_install_id_idx ON tenant_extension_install_secrets (install_id)');
};

/** @param {import('knex').Knex} knex */
exports.down = async function down(knex) {
  await knex.schema.dropTableIfExists('tenant_extension_install_secrets');
  await knex.schema.dropTableIfExists('tenant_extension_install_config');
};