1 files changed, 12 insertions, 0 deletions
diff --git a/internal/db/migrations/control/00005_per_blog_databases.sql b/internal/db/migrations/control/00005_per_blog_databases.sql
new file mode 100644
index 0000000..92d3fa2
--- /dev/null
+++ b/internal/db/migrations/control/00005_per_blog_databases.sql
@@ -0,0 +1,12 @@
+-- +goose Up
+-- Each blog gets its own database; the registry remembers which one.
+-- db_name stays nullable until the Go migration 00006 has moved the content.
+ALTER TABLE blogs ADD COLUMN db_name text UNIQUE;
+-- "blog_" + subdomain must fit in a 63-char Postgres identifier.
+ALTER TABLE blogs DROP CONSTRAINT blogs_subdomain_check;
+ALTER TABLE blogs ADD CONSTRAINT blogs_subdomain_check CHECK (subdomain ~ '^[a-z0-9](-?[a-z0-9]){0,57}$');
+
+-- +goose Down
+ALTER TABLE blogs DROP CONSTRAINT blogs_subdomain_check;
+ALTER TABLE blogs ADD CONSTRAINT blogs_subdomain_check CHECK (subdomain ~ '^[a-z0-9](-?[a-z0-9]){0,62}$');
+ALTER TABLE blogs DROP COLUMN db_name;