Give every blog its own Postgres database

A blog is now a database of its own (blog_<sub>) on the same server: one
pg_dump is a complete backup of a blog, one psql restores it, and nothing a
blog's queries do can reach another blog's rows. The control database
(DATABASE_URL) keeps only users and the blog registry (id, owner, subdomain,
db_name); title, tagline and theme move into a one-row settings table next
to the content so the dump really is everything.

db.Cluster holds the control pool plus small, lazily opened per-blog pools.
store.Store (control) hands out a store.BlogStore per blog; every blog_id
parameter and column is gone, the database is the scope. Handlers reach it
through blogStore(r), which resolveBlog puts in the context next to the blog.

Existing data is moved in place by control migration 00006, a Go migration
that runs inside the control transaction: it creates and migrates each blog
database, copies the rows preserving ids, and marks the registry; 00007 then
drops the old tables. Either every blog is moved or the control database is
untouched.

/media/{id} now serves the host's blog only, so dashboard previews on the
root domain use /b/{sub}/media/{id}. Subdomains are capped at 58 chars so
"blog_" + name fits a Postgres identifier. Deleting a user drops their
database. Store.Open resets a blog's pool and retries once so a database
restored under a running app (dropdb --force, createdb, psql) just works.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Sd8UPWrvyYCLj97JexNw3A

6 files changed, 177 insertions, 0 deletions

diff --git a/internal/db/migrations/control/00001_init.sql b/internal/db/migrations/control/00001_init.sql
new file mode 100644
index 0000000..e27dad4
--- /dev/null
+++ b/internal/db/migrations/control/00001_init.sql
@@ -0,0 +1,68 @@
+-- +goose Up
+CREATE TABLE users (
+    id             bigserial PRIMARY KEY,
+    username       text NOT NULL UNIQUE,
+    password_hash  text NOT NULL,
+    role           text NOT NULL CHECK (role IN ('superadmin', 'blogger')),
+    disabled       boolean NOT NULL DEFAULT false,
+    token_version  integer NOT NULL DEFAULT 0,
+    created_at     timestamptz NOT NULL DEFAULT now()
+);
+
+CREATE TABLE blogs (
+    id          bigserial PRIMARY KEY,
+    owner_id    bigint NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
+    subdomain   text NOT NULL UNIQUE CHECK (subdomain ~ '^[a-z0-9](-?[a-z0-9]){0,62}$'),
+    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,
+    blog_id      bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE,
+    slug         text NOT NULL,
+    title        text NOT NULL,
+    intro_md     text NOT NULL DEFAULT '',
+    intro_html   text NOT NULL DEFAULT '',
+    nav_order    integer NOT NULL DEFAULT 0,
+    show_in_nav  boolean NOT NULL DEFAULT true,
+    is_home      boolean NOT NULL DEFAULT false,
+    created_at   timestamptz NOT NULL DEFAULT now(),
+    UNIQUE (blog_id, slug)
+);
+CREATE UNIQUE INDEX pages_one_home_per_blog ON pages (blog_id) 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,
+    blog_id       bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE,
+    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_blog ON images (blog_id, created_at DESC);
+
+-- +goose Down
+DROP TABLE images;
+DROP TABLE posts;
+DROP TABLE pages;
+DROP TABLE blogs;
+DROP TABLE users;
diff --git a/internal/db/migrations/control/00002_sections.sql b/internal/db/migrations/control/00002_sections.sql
new file mode 100644
index 0000000..388cb17
--- /dev/null
+++ b/internal/db/migrations/control/00002_sections.sql
@@ -0,0 +1,19 @@
+-- +goose Up
+-- Announcements: blog-wide notices shown on every page and post.
+CREATE TABLE sections (
+    id          bigserial PRIMARY KEY,
+    blog_id     bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE,
+    title       text NOT NULL DEFAULT '',
+    body_md     text NOT NULL DEFAULT '',
+    body_html   text NOT NULL DEFAULT '',
+    placement   text NOT NULL DEFAULT 'above' CHECK (placement IN ('above', 'below', 'sidebar')),
+    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_blog ON sections (blog_id, sort_order, id);
+
+-- +goose Down
+DROP TABLE sections;
diff --git a/internal/db/migrations/control/00003_layout.sql b/internal/db/migrations/control/00003_layout.sql
new file mode 100644
index 0000000..e8fff23
--- /dev/null
+++ b/internal/db/migrations/control/00003_layout.sql
@@ -0,0 +1,56 @@
+-- +goose Up
+-- Layout modules: what each area of a blog (header, columns, footer) shows.
+CREATE TABLE modules (
+    id          bigserial PRIMARY KEY,
+    blog_id     bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE,
+    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_blog ON modules (blog_id, area, sort_order, id);
+
+-- The menu: blog pages and custom links in one ordered list.
+CREATE TABLE menu_items (
+    id          bigserial PRIMARY KEY,
+    blog_id     bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE,
+    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_blog ON menu_items (blog_id, sort_order, id);
+
+-- Existing blogs keep the menu they had: pages marked "show in menu", in menu
+-- order, with the home page only if the theme included it.
+INSERT INTO menu_items (blog_id, page_id, sort_order)
+SELECT p.blog_id, p.id, row_number() OVER (PARTITION BY p.blog_id ORDER BY p.nav_order, p.id) - 1
+FROM pages p JOIN blogs b ON b.id = p.blog_id
+WHERE p.show_in_nav AND (NOT p.is_home OR coalesce((b.theme->>'nav_show_home')::boolean, true));
+ALTER TABLE pages DROP COLUMN show_in_nav;
+
+-- ...and the layout their theme described.
+INSERT INTO modules (blog_id, area, kind, sort_order)
+SELECT id, 'header', 'title', CASE WHEN theme->>'nav_position' = 'top-bar' THEN 1 ELSE 0 END
+FROM blogs WHERE coalesce((theme->>'header_show_title')::boolean, true);
+INSERT INTO modules (blog_id, area, kind, sort_order)
+SELECT id, 'header', 'menu', CASE WHEN theme->>'nav_position' = 'top-bar' THEN 0 ELSE 1 END
+FROM blogs WHERE coalesce(theme->>'nav_position', 'below-header') <> 'left-sidebar';
+INSERT INTO modules (blog_id, area, kind, sort_order)
+SELECT id, 'left', 'menu', 0 FROM blogs WHERE theme->>'nav_position' = 'left-sidebar';
+INSERT INTO modules (blog_id, area, kind, body, sort_order)
+SELECT id, 'footer', 'text', theme->>'footer_text', 0 FROM blogs WHERE coalesce(theme->>'footer_text', '') <> '';
+INSERT INTO modules (blog_id, area, kind, sort_order)
+SELECT id, 'footer', 'rss', 1 FROM blogs WHERE coalesce((theme->>'footer_show_rss')::boolean, true);
+
+-- +goose Down
+ALTER TABLE pages ADD COLUMN show_in_nav boolean NOT NULL DEFAULT true;
+UPDATE pages SET show_in_nav = EXISTS (SELECT 1 FROM menu_items m WHERE m.page_id = pages.id);
+DROP TABLE menu_items;
+DROP TABLE modules;
diff --git a/internal/db/migrations/control/00004_section_placement.sql b/internal/db/migrations/control/00004_section_placement.sql
new file mode 100644
index 0000000..c36787e
--- /dev/null
+++ b/internal/db/migrations/control/00004_section_placement.sql
@@ -0,0 +1,13 @@
+-- +goose Up
+-- Announcements are placed in a column (left, main, right) at its top or bottom.
+ALTER TABLE sections DROP CONSTRAINT sections_placement_check;
+UPDATE sections SET placement = CASE placement WHEN 'above' THEN 'main-top' WHEN 'below' THEN 'main-bottom' ELSE 'left-top' END;
+ALTER TABLE sections ALTER COLUMN placement SET DEFAULT 'main-top';
+ALTER TABLE sections ADD CONSTRAINT sections_placement_check
+    CHECK (placement IN ('left-top', 'left-bottom', 'main-top', 'main-bottom', 'right-top', 'right-bottom'));
+
+-- +goose Down
+ALTER TABLE sections DROP CONSTRAINT sections_placement_check;
+UPDATE sections SET placement = CASE placement WHEN 'main-top' THEN 'above' WHEN 'main-bottom' THEN 'below' ELSE 'sidebar' END;
+ALTER TABLE sections ALTER COLUMN placement SET DEFAULT 'above';
+ALTER TABLE sections ADD CONSTRAINT sections_placement_check CHECK (placement IN ('above', 'below', 'sidebar'));
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.