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, 38 insertions, 7 deletions

diff --git a/README.md b/README.md
index d8c11f2..fbf7412 100644
--- a/README.md
+++ b/README.md
@@ -19,7 +19,9 @@ Server-rendered HTML, no JavaScript required, works on old browsers and phones.
 - Changes are live immediately: save, then refresh the blog tab.
 - A **superadmin** creates bloggers, resets passwords, disables or deletes accounts.
 - The **root domain is itself a blog**, owned by the superadmin and managed like any other.
-- Images are stored in Postgres; everything is in one database.
+- **Each blog is its own Postgres database** (`blog_<name>`), images included, so one
+  `pg_dump` is a complete backup of a blog and one `psql` restores it. A small control
+  database holds the users and the list of blogs.
 
 ## Local development
 
@@ -56,13 +58,13 @@ Go changes need a restart.
 | `BASE_DOMAIN` | `blogspace.localhost` | Root domain; blogs are `<name>.BASE_DOMAIN` |
 | `ADDR` | `:8080` | Listen address |
 | `PUBLIC_PORT` | — | Appended to generated blog links (dev only; unset behind a proxy on :80/:443) |
-| `DATABASE_URL` | local dev DSN | Postgres connection string |
+| `DATABASE_URL` | local dev DSN | Connection string of the **control** database; blog databases are created next to it by the same role |
 | `JWT_SECRET` | — | **Required** outside dev; long random string (`openssl rand -hex 32`) |
 | `SUPERADMIN_USERNAME` / `SUPERADMIN_PASSWORD` | `admin` / — | Created on first start if no superadmin exists |
 | `MAX_UPLOAD_MB` | `5` | Image upload limit |
 | `DEV` | `false` | Hot-reload templates, allow missing secrets |
 
-Migrations run automatically at startup.
+Migrations run automatically at startup, for the control database and for every blog database.
 
 ## Deployment (Docker)
 
@@ -95,16 +97,45 @@ server {
 Caddy: `example.com, *.example.com { reverse_proxy 127.0.0.1:8080 }` (wildcard
 certificates need the DNS challenge).
 
-Backups: dump the Postgres volume (`docker compose exec db pg_dump -U blogspace blogspace > backup.sql`).
-Images live in the database, so that one dump is everything.
+### Backups and restores
+
+Every blog lives in its own database, `blog_<name>` (dashes become underscores:
+`my-blog` → `blog_my_blog`). The control database (`blogspace`) holds the users
+and the blog registry. Images are in the blog database, so one dump is the whole blog.
+
+```sh
+# one blog
+docker compose exec db pg_dump -U blogspace blog_alice > alice.sql
+# users and the blog list
+docker compose exec db pg_dump -U blogspace blogspace > control.sql
+# everything at once
+docker compose exec db pg_dumpall -U blogspace > all.sql
+```
+
+Restoring a blog, with the app running:
+
+```sh
+docker compose exec db dropdb -U blogspace --force blog_alice
+docker compose exec db createdb -U blogspace blog_alice
+docker compose exec -T db psql -U blogspace -q blog_alice < alice.sql
+```
+
+The registry row must exist: on a fresh install first create the user with that
+subdomain in `/admin/` (which makes an empty `blog_alice`), then overwrite it as
+above. A dump taken with an older version of Blogspace is upgraded at the next
+start (or with `blogspace migrate`).
+
+Deleting a user in `/admin/` drops their blog database — take a dump first if
+you may want it back. Blog pools are small (4 connections each, closed when
+idle); with many blogs busy at once, raise `max_connections` on the `db` service.
 
 ## Layout
 
 ```
 cmd/blogspace/        main (serve | seed | migrate)
 internal/config/      environment → Config
-internal/db/          pgx pool + goose migrations (embedded SQL)
-internal/store/       models and queries (users, blogs, pages, posts, images, sections)
+internal/db/          control + per-blog pools, goose migrations (control/ and blog/)
+internal/store/       Store (users, blog registry) and BlogStore (one blog's content)
 internal/auth/        bcrypt, JWT cookie sessions, CSRF tokens
 internal/markdown/    goldmark + bluemonday
 internal/slug/        title → slug