traxxx/migrations/20241103060644_socials.js

exports.up = async (knex) => {
	await knex.schema.alterTable('actors_social', (table) => {
		table.dropUnique(['url', 'actor_id', 'profile_id']);
		table.dropColumn('profile_id');

		table.string('handle');

		table.boolean('is_broken')
			.notNullable()
			.defaultTo(false);

		table.datetime('pinged_at');
		table.datetime('verified_at');

		table.unique(['actor_id', 'platform', 'handle']);
		table.unique(['actor_id', 'url']);
	});

	await knex.raw('ALTER TABLE actors_social ADD CONSTRAINT socials_url_or_handle CHECK (num_nulls(handle, url) = 1);');
	await knex.raw('ALTER TABLE actors_social ADD CONSTRAINT socials_handle_and_platform CHECK (num_nulls(platform, handle) = 2 or num_nulls(platform, handle) = 0);');

	await knex.schema.renameTable('actors_social', 'actors_socials');
};

exports.down = async (knex) => {
	await knex.raw('ALTER TABLE actors_socials DROP CONSTRAINT socials_url_or_handle;');
	await knex.raw('ALTER TABLE actors_socials DROP CONSTRAINT socials_handle_and_platform;');

	await knex.schema.renameTable('actors_socials', 'actors_social');

	await knex.schema.alterTable('actors_social', (table) => {
		table.dropUnique(['actor_id', 'platform', 'handle']);
		table.dropUnique(['actor_id', 'url']);

		table.integer('profile_id')
			.references('id')
			.inTable('actors_profiles');

		table.dropColumn('handle');
		table.dropColumn('verified_at');
		table.dropColumn('pinged_at');
		table.dropColumn('is_broken');

		table.unique(['url', 'actor_id', 'profile_id']);
	});
};