7 files changed, 112 insertions, 0 deletions
diff --git a/internal/db/migrations/blog/00001_init.sql b/internal/db/migrations/blog/00001_init.sql
new file mode 100644
index 0000000..4649935
--- /dev/null
+++ b/internal/db/migrations/blog/00001_init.sql
@@ -0,0 +1,91 @@
+-- +goose Up
+-- One database per blog: nothing here carries a blog id, the database is the scope.
+CREATE TABLE settings (
+    id          boolean PRIMARY KEY DEFAULT true CHECK (id), -- exactly one row
+    title       text NOT NULL,
+    tagline     text NOT NULL DEFAULT '',
+    theme       jsonb NOT NULL DEFAULT '{}'::jsonb,
+    created_at  timestamptz NOT NULL DEFAULT now(),
+    updated_at  timestamptz NOT NULL DEFAULT now()
+);
+
+CREATE TABLE pages (
+    id           bigserial PRIMARY KEY,
+    slug         text NOT NULL UNIQUE,
+    title        text NOT NULL,
+    intro_md     text NOT NULL DEFAULT '',
+    intro_html   text NOT NULL DEFAULT '',
+    nav_order    integer NOT NULL DEFAULT 0,
+    is_home      boolean NOT NULL DEFAULT false,
+    created_at   timestamptz NOT NULL DEFAULT now()
+);
+CREATE UNIQUE INDEX pages_one_home ON pages ((true)) WHERE is_home;
+
+CREATE TABLE posts (
+    id          bigserial PRIMARY KEY,
+    page_id     bigint NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
+    slug        text NOT NULL,
+    title       text NOT NULL,
+    body_md     text NOT NULL DEFAULT '',
+    body_html   text NOT NULL DEFAULT '',
+    published   boolean NOT NULL DEFAULT true,
+    created_at  timestamptz NOT NULL DEFAULT now(),
+    updated_at  timestamptz NOT NULL DEFAULT now(),
+    UNIQUE (page_id, slug)
+);
+CREATE INDEX posts_page_created ON posts (page_id, created_at DESC);
+
+CREATE TABLE images (
+    id            uuid PRIMARY KEY,
+    filename      text NOT NULL,
+    content_type  text NOT NULL,
+    size          integer NOT NULL,
+    data          bytea NOT NULL,
+    created_at    timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX images_created ON images (created_at DESC);
+
+-- Announcements: blog-wide notices shown on every page and post.
+CREATE TABLE sections (
+    id          bigserial PRIMARY KEY,
+    title       text NOT NULL DEFAULT '',
+    body_md     text NOT NULL DEFAULT '',
+    body_html   text NOT NULL DEFAULT '',
+    placement   text NOT NULL DEFAULT 'main-top'
+                CHECK (placement IN ('left-top', 'left-bottom', 'main-top', 'main-bottom', 'right-top', 'right-bottom')),
+    style       text NOT NULL DEFAULT 'note' CHECK (style IN ('plain', 'note', 'warning')),
+    enabled     boolean NOT NULL DEFAULT true,
+    sort_order  integer NOT NULL DEFAULT 0,
+    created_at  timestamptz NOT NULL DEFAULT now(),
+    updated_at  timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX sections_order ON sections (sort_order, id);
+
+-- Layout modules: what each area of a blog (header, columns, footer) shows.
+CREATE TABLE modules (
+    id          bigserial PRIMARY KEY,
+    area        text NOT NULL CHECK (area IN ('header', 'left', 'right', 'above', 'below', 'footer')),
+    kind        text NOT NULL CHECK (kind IN ('title', 'logo', 'menu', 'archive', 'recent', 'html', 'rss', 'text', 'sitemap')),
+    title       text NOT NULL DEFAULT '',
+    body        text NOT NULL DEFAULT '',
+    count       integer NOT NULL DEFAULT 5,
+    sort_order  integer NOT NULL DEFAULT 0,
+    created_at  timestamptz NOT NULL DEFAULT now(),
+    updated_at  timestamptz NOT NULL DEFAULT now()
+);
+CREATE INDEX modules_area ON modules (area, sort_order, id);
+
+-- The menu: blog pages and custom links in one ordered list.
+CREATE TABLE menu_items (
+    id          bigserial PRIMARY KEY,
+    page_id     bigint REFERENCES pages(id) ON DELETE CASCADE,
+    label       text NOT NULL DEFAULT '',
+    url         text NOT NULL DEFAULT '',
+    sort_order  integer NOT NULL DEFAULT 0,
+    CHECK (page_id IS NOT NULL OR url <> '')
+);
+CREATE UNIQUE INDEX menu_items_page ON menu_items (page_id) WHERE page_id IS NOT NULL;
+CREATE INDEX menu_items_order ON menu_items (sort_order, id);
+
+-- +goose Down
+DROP TABLE menu_items, modules, sections, images, posts, pages, settings;
diff --git a/internal/db/migrations/00001_init.sql b/internal/db/migrations/control/00001_init.sql
index e27dad4..e27dad4 100644
--- a/internal/db/migrations/00001_init.sql
+++ b/internal/db/migrations/control/00001_init.sql
diff --git a/internal/db/migrations/00002_sections.sql b/internal/db/migrations/control/00002_sections.sql
index 388cb17..388cb17 100644
--- a/internal/db/migrations/00002_sections.sql
+++ b/internal/db/migrations/control/00002_sections.sql
diff --git a/internal/db/migrations/00003_layout.sql b/internal/db/migrations/control/00003_layout.sql
index e8fff23..e8fff23 100644
--- a/internal/db/migrations/00003_layout.sql
+++ b/internal/db/migrations/control/00003_layout.sql
diff --git a/internal/db/migrations/00004_section_placement.sql b/internal/db/migrations/control/00004_section_placement.sql
index c36787e..c36787e 100644
--- a/internal/db/migrations/00004_section_placement.sql
+++ b/internal/db/migrations/control/00004_section_placement.sql
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;
diff --git a/internal/db/migrations/control/00007_drop_content.sql b/internal/db/migrations/control/00007_drop_content.sql
new file mode 100644
index 0000000..9daa1ac
--- /dev/null
+++ b/internal/db/migrations/control/00007_drop_content.sql
@@ -0,0 +1,9 @@
+-- +goose Up
+-- The content now lives in the per-blog databases (see 00006 in split.go);
+-- the control database keeps only users and the blog registry.
+DROP TABLE menu_items, modules, sections, images, posts, pages;
+ALTER TABLE blogs DROP COLUMN title, DROP COLUMN tagline, DROP COLUMN theme, DROP COLUMN updated_at;
+ALTER TABLE blogs ALTER COLUMN db_name SET NOT NULL;
+
+-- +goose Down
+-- Not reversible: the content is gone from this database. Restore from a backup instead.