repo: overhaul for multitenancy

4 files changed, 75 insertions, 0 deletions

diff --git a/migrations/2024-08-16-111659_multitenant/down.sql b/migrations/2024-08-16-111659_multitenant/down.sql
new file mode 100644
index 0000000..f0cac09
--- /dev/null
+++ b/migrations/2024-08-16-111659_multitenant/down.sql
@@ -0,0 +1,12 @@
+ALTER TABLE memes
+    DROP CONSTRAINT IF EXISTS memes_guild_title_key,
+    DROP CONSTRAINT IF EXISTS memes_guild_content_image_id_audio_id_key,
+    DROP CONSTRAINT IF EXISTS memes_guild_metadata_id_key;
+
+DROP INDEX IF EXISTS memes_guild_title_key,
+    memes_guild_content_image_id_audio_id_key,
+    memes_guild_metadata_id_key;
+
+CREATE UNIQUE INDEX IF NOT EXISTS text_memes_title_key ON memes (title);
+CREATE UNIQUE INDEX IF NOT EXISTS text_memes_content_image_id_audio_id_key ON memes (content, image_id, audio_id);
+CREATE UNIQUE INDEX IF NOT EXISTS text_memes_metadata_id_key ON memes (metadata_id);
diff --git a/migrations/2024-08-16-111659_multitenant/up.sql b/migrations/2024-08-16-111659_multitenant/up.sql
new file mode 100644
index 0000000..8bda619
--- /dev/null
+++ b/migrations/2024-08-16-111659_multitenant/up.sql
@@ -0,0 +1,35 @@
+ALTER TABLE memes
+    DROP CONSTRAINT IF EXISTS text_memes_title_key,
+    DROP CONSTRAINT IF EXISTS text_memes_content_image_id_audio_id_key,
+    DROP CONSTRAINT IF EXISTS text_memes_metadata_id_key,
+    DROP CONSTRAINT IF EXISTS memes_guild_title_key,
+    DROP CONSTRAINT IF EXISTS memes_guild_content_image_id_audio_id_key,
+    DROP CONSTRAINT IF EXISTS memes_guild_metadata_id_key
+;
+
+DROP INDEX IF EXISTS text_memes_title_key, text_memes_content_image_id_audio_id_key, text_memes_metadata_id_key;
+
+ALTER TABLE memes
+    ADD COLUMN IF NOT EXISTS guild BIGINT;
+
+ALTER TABLE MEMES
+    ALTER COLUMN guild TYPE BIGINT,
+    ADD CONSTRAINT memes_guild_title_key UNIQUE (guild, title),
+    ADD CONSTRAINT memes_guild_content_image_id_audio_id_key UNIQUE (guild, content, image_id, audio_id),
+    ADD CONSTRAINT memes_guild_metadata_id_key UNIQUE (guild, metadata_id);
+
+CREATE UNIQUE INDEX IF NOT EXISTS memes_guild_title_key ON memes (guild, title);
+CREATE UNIQUE INDEX IF NOT EXISTS memes_guild_content_image_id_audio_id_key ON memes (guild, content, image_id, audio_id);
+CREATE UNIQUE INDEX IF NOT EXISTS memes_guild_metadata_id_key ON memes (guild, metadata_id);
+
+ALTER TABLE tombstones
+    ADD COLUMN IF NOT EXISTS guild BIGINT;
+
+ALTER TABLE tombstones
+    ALTER COLUMN guild TYPE BIGINT;
+
+ALTER TABLE invocation_records
+    ADD COLUMN IF NOT EXISTS guild BIGINT;
+
+ALTER TABLE invocation_records
+    ALTER COLUMN guild TYPE BIGINT;
diff --git a/migrations/2024-08-16-230507_require_guild/down.sql b/migrations/2024-08-16-230507_require_guild/down.sql
new file mode 100644
index 0000000..e7191c8
--- /dev/null
+++ b/migrations/2024-08-16-230507_require_guild/down.sql
@@ -0,0 +1,6 @@
+ALTER TABLE memes
+    ALTER COLUMN guild DROP NOT NULL;
+ALTER TABLE invocation_records
+    ALTER COLUMN guild DROP NOT NULL;
+ALTER TABLE tombstones
+    ALTER COLUMN guild DROP NOT NULL;
diff --git a/migrations/2024-08-16-230507_require_guild/up.sql b/migrations/2024-08-16-230507_require_guild/up.sql
new file mode 100644
index 0000000..06e5cd3
--- /dev/null
+++ b/migrations/2024-08-16-230507_require_guild/up.sql
@@ -0,0 +1,22 @@
+-- This migration will fail with any records in the db. If you are working with a normal server, you
+-- can run:
+--
+-- $ convert_null_guilds --guild $PREVIOUS_SINGLETENANT_GUILD
+--
+-- to set all existing db records to the specified guild. A mixed / fucked up db sceneario will
+-- require you to manually edit the db and ensure `guild` is set in `memes`, `invocation_records`,
+-- and `tombstones`.
+
+DO LANGUAGE plpgsql
+$$
+    BEGIN
+        RAISE NOTICE 'if this migration fails, consider running "convert_null_guilds"';
+    END
+$$;
+
+ALTER TABLE memes
+    ALTER COLUMN guild SET NOT NULL;
+ALTER TABLE invocation_records
+    ALTER COLUMN guild SET NOT NULL;
+ALTER TABLE tombstones
+    ALTER COLUMN guild SET NOT NULL;