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

1 files changed, 45 insertions, 10 deletions

diff --git a/AGENTS.md b/AGENTS.md
index 3a1da81..8b997e4 100644
--- a/AGENTS.md
+++ b/AGENTS.md
@@ -23,6 +23,9 @@ table and deployment notes.
   (e.g. the `<noscript>` button in `posts.html`).
 - Server-rendered `html/template`; no SPA, no frontend toolchain.
 - Postgres holds **everything**, including images (`images.data bytea`).
+  **Each blog is its own database** (`blog_<sub>`, see Key mechanics) so a
+  blog is backed up and restored with plain `pg_dump`/`psql`; the control
+  database (`DATABASE_URL`) holds only `users` and the `blogs` registry.
 - Posts are Markdown (goldmark → bluemonday). No WYSIWYG. The editor's
   "Insert image" is the one scripted convenience (`partials/editor.html`);
   it must keep its no-JS fallback (file appended on save).
@@ -40,10 +43,14 @@ table and deployment notes.
 ## Layout
 
 ```
-cmd/blogspace/        main.go (serve|seed|migrate, superadmin + root blog bootstrap), seed.go
+cmd/blogspace/        main.go (serve|seed|migrate, superadmin + root blog bootstrap,
+                      migrates every blog database at start), seed.go
 internal/config/      env → Config; RootSubdomain = "www"
-internal/db/          pgxpool + goose; migrations/*.sql embedded
-internal/store/       models + hand-written SQL (users, blogs, pages, posts, images, sections, modules, menu)
+internal/db/          Cluster (control pool + lazy per-blog pools, CREATE/DROP DATABASE),
+                      goose providers; migrations/control/*.sql, migrations/blog/*.sql,
+                      split.go (control Go migration 00006: the one-off move to per-blog DBs)
+internal/store/       Store = control DB (users, blog registry, create/delete blog);
+                      BlogStore = one blog's DB (settings, pages, posts, images, sections, modules, menu)
 internal/auth/        bcrypt, JWT issue/parse, cookie, HMAC CSRF
 internal/markdown/    Render(md) → sanitized HTML
 internal/slug/        Make/Valid/WithSuffix (Greek transliteration included)
@@ -57,11 +64,29 @@ internal/web/         server.go (host router, middleware, render helpers)
 
 ## Key mechanics
 
+- **One database per blog**: the registry row (`blogs`: id, owner, subdomain,
+  `db_name`) lives in the control DB; everything else — a one-row `settings`
+  table (title, tagline, theme) plus pages, posts, images, sections, modules,
+  menu_items — lives in `blog_<sub>` (`db.DBName`: dashes → underscores, so
+  subdomains are capped at 58 chars). No table in a blog DB carries a
+  blog id; the database is the scope. `store.Store` (control) hands out a
+  `store.BlogStore` per blog via `Store.Open`, which also fills `Blog.Title/
+  Tagline/ThemeJSON` from `settings`. `db.Cluster` caches one small pool per
+  blog (4 conns, idle closed after 2 min). `Store.Open` resets the pool and
+  retries once so a `dropdb --force` + restore under a running app is seamless.
+  Creating a blog: registry insert in a control tx → `CreateBlogDB` (CREATE
+  DATABASE + blog migrations, outside the tx) → settings/home page/defaults →
+  commit; any failure rolls back and drops the DB. A leftover database with
+  the blog's name is `db.ErrDatabaseExists` (409 in `/admin/users/new`).
+  Deleting a user drops their blog database. Start-up (`migrateBlogs`)
+  migrates every registered blog DB; a missing one is logged and skipped.
 - **Host routing** (`web/server.go` `ServeHTTP`): base domain or `www.` →
   root mux with `ctxHostSub = "www"`; `<sub>.base` → subdomain mux; anything
   else 404. Public blog handlers get the blog via the `hostBlog` wrapper;
   management handlers get it via `withBlog` (`/b/{sub}/…`, owner or
-  superadmin). Both put it in `ctxBlog`; templates see it as `.Blog`.
+  superadmin). Both call `resolveBlog`, which puts the blog in `ctxBlog`
+  (templates see it as `.Blog`) and its `BlogStore` in `ctxBlogStore`
+  (`blogStore(r)` in handlers).
 - **Root blog** is a normal `blogs` row with subdomain `www`, owned by the
   first superadmin, created by `ensureRootBlog` on startup. Managed at
   `/b/www/`. `Config.BlogURL("www")` returns the root URL.
@@ -141,7 +166,12 @@ internal/web/         server.go (host router, middleware, render helpers)
   (`handleFavicon`) serves the png or redirects to the blog's image, so
   icon-probing browsers never hit the 404 page; `favicon.ico` is reserved.
 - **Images**: `/media/{uuid}` served on every host with immutable cache
-  headers. Uploads are content-sniffed (png/jpeg/gif/webp/ico). Deleting an
+  headers, from the host's blog database only (the root domain serves the
+  root blog's images). Dashboard previews for another blog on the root host
+  therefore use `GET /b/{sub}/media/{id}` (`withBlog`): `dashboard/images.html`
+  and the `imagepick` partial (`sub` arg). Markdown keeps the relative
+  `/media/…` form because post bodies render on the blog host.
+  Uploads are content-sniffed (png/jpeg/gif/webp/ico). Deleting an
   image clears theme references to it. `POST /b/{sub}/images/upload`
   answers JSON (`{id, filename, markdown}` / `{error}`) when the request
   has `Accept: application/json`; that is what the editor script calls.
@@ -172,7 +202,7 @@ internal/web/         server.go (host router, middleware, render helpers)
 make dev        # postgres in docker + app (DEV=1: templates/static reloaded from disk)
 make seed       # admin/admin (superadmin, owns root blog), alice/alicealice
 make test       # unit tests; no DB needed
-make db-reset   # wipe dev data
+make db-reset   # wipe dev data (control and all blog databases: the whole volume)
 ```
 Port 8080 may be taken on the owner's machine: `make dev ADDR=:8090 PUBLIC_PORT=8090`.
 Browsers resolve `*.blogspace.localhost` to loopback; with curl use
@@ -184,11 +214,16 @@ superadmin password to `admin`. Production refuses both.
 ## Conventions
 
 - Run `gofmt -w .`, `go vet ./...`, `go test ./...` before committing.
-- Migrations: add `internal/db/migrations/NNNNN_name.sql` with goose
+- Migrations: `internal/db/migrations/blog/NNNNN_name.sql` for blog content
+  (runs in every blog database — it must not reference `users`/`blogs`) and
+  `internal/db/migrations/control/` for users and the registry; goose
   `-- +goose Up/Down` sections; they run automatically at startup. Never
-  edit an applied migration.
-- New store methods take `ctx` first and scope queries by `blog_id` so a
-  blogger can never touch another blog's rows.
+  edit an applied migration. Control 00005–00007 are the one-off split (add
+  `db_name`, move content in `split.go`, drop the old tables) — a fresh
+  install still replays them.
+- Blog content goes through `BlogStore` methods, which take `ctx` first and
+  are bound to one blog's database, so a blogger can never touch another
+  blog's rows; users and the registry go through `Store`.
 - Keep handlers thin: validate → call store → `redirectOK`. Re-render the
   form with `"error"` in the data map on validation failure (4xx status).
 - Match the existing comment density; comments explain *why*, not *what*.