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

16 files changed, 125 insertions, 95 deletions

diff --git a/internal/web/handlers_admin.go b/internal/web/handlers_admin.go
index 4287411..e0b3991 100644
--- a/internal/web/handlers_admin.go
+++ b/internal/web/handlers_admin.go
@@ -9,11 +9,14 @@ import (
 
 	"github.com/gramanas/blogspace/internal/auth"
 	"github.com/gramanas/blogspace/internal/config"
+	"github.com/gramanas/blogspace/internal/db"
 	"github.com/gramanas/blogspace/internal/store"
 )
 
 var usernameRe = regexp.MustCompile(`^[a-zA-Z0-9_.-]{2,40}$`)
-var subdomainRe = regexp.MustCompile(`^[a-z0-9](-?[a-z0-9]){0,62}$`)
+
+// 58 chars at most: "blog_" + subdomain must fit a Postgres database name.
+var subdomainRe = regexp.MustCompile(`^[a-z0-9](-?[a-z0-9]){0,57}$`)
 
 func (s *Server) handleAdmin(w http.ResponseWriter, r *http.Request) {
 	users, err := s.st.ListUsers(r.Context())
@@ -70,6 +73,11 @@ func (s *Server) handleAdminNewUser(w http.ResponseWriter, r *http.Request) {
 			s.renderStatus(w, r, http.StatusConflict, "admin/new_user.html", f)
 			return
 		}
+		if errors.Is(err, db.ErrDatabaseExists) {
+			f["error"] = "A database " + db.DBName(sub) + " already exists in Postgres (a leftover of a deleted blog?). Drop it or pick another subdomain."
+			s.renderStatus(w, r, http.StatusConflict, "admin/new_user.html", f)
+			return
+		}
 		s.serverError(w, err)
 		return
 	}
@@ -151,7 +159,7 @@ func (s *Server) handleAdminDeleteUser(w http.ResponseWriter, r *http.Request) {
 		s.plainError(w, http.StatusBadRequest, "This user owns the root blog and cannot be deleted.")
 		return
 	}
-	if err := s.st.DeleteUser(r.Context(), u.ID); err != nil { // cascades to blog, pages, posts, images
+	if err := s.st.DeleteUser(r.Context(), u.ID); err != nil { // drops their blog database too
 		s.serverError(w, err)
 		return
 	}
diff --git a/internal/web/handlers_blog.go b/internal/web/handlers_blog.go
index 42ec45b..f2e7713 100644
--- a/internal/web/handlers_blog.go
+++ b/internal/web/handlers_blog.go
@@ -17,19 +17,19 @@ const postsPerPage = 10
 func (s *Server) blogView(r *http.Request) (map[string]any, error) {
 	blog := currentBlog(r)
 	ctx := r.Context()
-	pages, err := s.st.ListPages(ctx, blog.ID)
+	pages, err := blogStore(r).ListPages(ctx)
 	if err != nil {
 		return nil, err
 	}
-	menu, err := s.st.ListMenu(ctx, blog.ID)
+	menu, err := blogStore(r).ListMenu(ctx)
 	if err != nil {
 		return nil, err
 	}
-	mods, err := s.st.ListModules(ctx, blog.ID)
+	mods, err := blogStore(r).ListModules(ctx)
 	if err != nil {
 		return nil, err
 	}
-	sections, err := s.st.EnabledSections(ctx, blog.ID)
+	sections, err := blogStore(r).EnabledSections(ctx)
 	if err != nil {
 		return nil, err
 	}
@@ -38,14 +38,14 @@ func (s *Server) blogView(r *http.Request) (map[string]any, error) {
 	v := map[string]any{"theme": theme, "css": theme.CSS(), "layout": layout, "menu": menu, "pages": pages}
 	// Module data is only fetched when a visible module shows it.
 	if layout.NeedsArchive() {
-		index, err := s.st.PublishedPostIndex(ctx, blog.ID)
+		index, err := blogStore(r).PublishedPostIndex(ctx)
 		if err != nil {
 			return nil, err
 		}
 		v["archive"] = groupArchive(index)
 	}
 	if n := layout.MaxRecent(); n > 0 {
-		recent, err := s.st.RecentPublishedPosts(ctx, blog.ID, n)
+		recent, err := blogStore(r).RecentPublishedPosts(ctx, n)
 		if err != nil {
 			return nil, err
 		}
@@ -56,7 +56,7 @@ func (s *Server) blogView(r *http.Request) (map[string]any, error) {
 }
 
 func (s *Server) handleBlogHome(w http.ResponseWriter, r *http.Request) {
-	page, err := s.st.HomePage(r.Context(), currentBlog(r).ID)
+	page, err := blogStore(r).HomePage(r.Context())
 	if err != nil {
 		s.blogNotFound(w, r)
 		return
@@ -65,7 +65,7 @@ func (s *Server) handleBlogHome(w http.ResponseWriter, r *http.Request) {
 }
 
 func (s *Server) handleBlogPage(w http.ResponseWriter, r *http.Request) {
-	page, err := s.st.PageBySlug(r.Context(), currentBlog(r).ID, r.PathValue("page"))
+	page, err := blogStore(r).PageBySlug(r.Context(), r.PathValue("page"))
 	if err != nil {
 		s.blogNotFound(w, r)
 		return
@@ -87,7 +87,7 @@ func (s *Server) renderPage(w http.ResponseWriter, r *http.Request, page *store.
 	if n < 1 {
 		n = 1
 	}
-	posts, total, err := s.st.PublishedPosts(r.Context(), page.ID, postsPerPage, (n-1)*postsPerPage)
+	posts, total, err := blogStore(r).PublishedPosts(r.Context(), page.ID, postsPerPage, (n-1)*postsPerPage)
 	if err != nil {
 		s.serverError(w, err)
 		return
@@ -102,13 +102,12 @@ func (s *Server) renderPage(w http.ResponseWriter, r *http.Request, page *store.
 }
 
 func (s *Server) handleBlogPost(w http.ResponseWriter, r *http.Request) {
-	blog := currentBlog(r)
-	page, err := s.st.PageBySlug(r.Context(), blog.ID, r.PathValue("page"))
+	page, err := blogStore(r).PageBySlug(r.Context(), r.PathValue("page"))
 	if err != nil {
 		s.blogNotFound(w, r)
 		return
 	}
-	post, err := s.st.PublishedPostBySlug(r.Context(), page.ID, r.PathValue("post"))
+	post, err := blogStore(r).PublishedPostBySlug(r.Context(), page.ID, r.PathValue("post"))
 	if err != nil {
 		s.blogNotFound(w, r)
 		return
@@ -154,7 +153,7 @@ type rssItem struct {
 
 func (s *Server) handleBlogFeed(w http.ResponseWriter, r *http.Request) {
 	blog := currentBlog(r)
-	posts, err := s.st.RecentPublishedPosts(r.Context(), blog.ID, 30)
+	posts, err := blogStore(r).RecentPublishedPosts(r.Context(), 30)
 	if err != nil {
 		s.serverError(w, err)
 		return
diff --git a/internal/web/handlers_dashboard.go b/internal/web/handlers_dashboard.go
index f210891..a1e0d89 100644
--- a/internal/web/handlers_dashboard.go
+++ b/internal/web/handlers_dashboard.go
@@ -6,13 +6,12 @@ import (
 )
 
 func (s *Server) handleBlogOverview(w http.ResponseWriter, r *http.Request) {
-	blog := currentBlog(r)
-	pages, err := s.st.ListPages(r.Context(), blog.ID)
+	pages, err := blogStore(r).ListPages(r.Context())
 	if err != nil {
 		s.serverError(w, err)
 		return
 	}
-	posts, err := s.st.ListPosts(r.Context(), blog.ID, 0)
+	posts, err := blogStore(r).ListPosts(r.Context(), 0)
 	if err != nil {
 		s.serverError(w, err)
 		return
@@ -35,7 +34,7 @@ func (s *Server) handleSettings(w http.ResponseWriter, r *http.Request) {
 		s.renderStatus(w, r, http.StatusBadRequest, "dashboard/settings.html", map[string]any{"error": "Title is required (max 120 chars); tagline max 300."})
 		return
 	}
-	if err := s.st.UpdateBlogSettings(r.Context(), blog.ID, title, tagline); err != nil {
+	if err := blogStore(r).UpdateSettings(r.Context(), title, tagline); err != nil {
 		s.serverError(w, err)
 		return
 	}
diff --git a/internal/web/handlers_design.go b/internal/web/handlers_design.go
index 1e18001..9f98e9e 100644
--- a/internal/web/handlers_design.go
+++ b/internal/web/handlers_design.go
@@ -20,7 +20,7 @@ var allowedImageTypes = map[string]bool{"image/png": true, "image/jpeg": true, "
 
 func (s *Server) handleDesignForm(w http.ResponseWriter, r *http.Request) {
 	blog := currentBlog(r)
-	images, err := s.st.ListImages(r.Context(), blog.ID)
+	images, err := blogStore(r).ListImages(r.Context())
 	if err != nil {
 		s.serverError(w, err)
 		return
@@ -39,7 +39,7 @@ func (s *Server) handleDesign(w http.ResponseWriter, r *http.Request) {
 	for field, dst := range map[string]*string{"bg_image_file": &theme.BgImage, "header_image_file": &theme.HeaderImage, "logo_file": &theme.Logo, "favicon_file": &theme.Favicon} {
 		img, err := s.readUpload(r, field)
 		if err != nil {
-			images, _ := s.st.ListImages(r.Context(), blog.ID)
+			images, _ := blogStore(r).ListImages(r.Context())
 			s.renderStatus(w, r, http.StatusBadRequest, "dashboard/design.html", map[string]any{"theme": theme, "images": images, "presets": Presets(), "error": err.Error()})
 			return
 		}
@@ -47,7 +47,7 @@ func (s *Server) handleDesign(w http.ResponseWriter, r *http.Request) {
 			*dst = img.ID.String()
 		}
 	}
-	if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, theme.JSON()); err != nil {
+	if err := blogStore(r).UpdateTheme(r.Context(), theme.JSON()); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -64,7 +64,7 @@ func (s *Server) handleDesignPreset(w http.ResponseWriter, r *http.Request) {
 		return
 	}
 	theme := ParseTheme(blog.ThemeJSON).WithPreset(p)
-	if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, theme.JSON()); err != nil {
+	if err := blogStore(r).UpdateTheme(r.Context(), theme.JSON()); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -75,11 +75,11 @@ func (s *Server) handleDesignPreset(w http.ResponseWriter, r *http.Request) {
 // stay in the library and the menu is left alone.
 func (s *Server) handleDesignReset(w http.ResponseWriter, r *http.Request) {
 	blog := currentBlog(r)
-	if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, DefaultTheme().JSON()); err != nil {
+	if err := blogStore(r).UpdateTheme(r.Context(), DefaultTheme().JSON()); err != nil {
 		s.serverError(w, err)
 		return
 	}
-	if err := s.st.ResetModules(r.Context(), blog.ID); err != nil {
+	if err := blogStore(r).ResetModules(r.Context()); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -122,7 +122,7 @@ func (s *Server) storeUpload(r *http.Request, fh *multipart.FileHeader) (*store.
 	if name == "" || name == "." || len(name) > 120 {
 		name = "image"
 	}
-	return s.st.CreateImage(r.Context(), currentBlog(r).ID, name, ct, buf.Bytes())
+	return blogStore(r).CreateImage(r.Context(), name, ct, buf.Bytes())
 }
 
 // imageMarkdown is the line the editor inserts for an uploaded image.
@@ -153,7 +153,7 @@ func kbString(n int64) string {
 // ---- image library ---------------------------------------------------------
 
 func (s *Server) handleImages(w http.ResponseWriter, r *http.Request) {
-	images, err := s.st.ListImages(r.Context(), currentBlog(r).ID)
+	images, err := blogStore(r).ListImages(r.Context())
 	if err != nil {
 		s.serverError(w, err)
 		return
@@ -207,7 +207,7 @@ func (s *Server) handleImageDelete(w http.ResponseWriter, r *http.Request) {
 		http.NotFound(w, r)
 		return
 	}
-	if err := s.st.DeleteImage(r.Context(), blog.ID, id); err != nil {
+	if err := blogStore(r).DeleteImage(r.Context(), id); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -227,7 +227,7 @@ func (s *Server) handleImageDelete(w http.ResponseWriter, r *http.Request) {
 		theme.Logo, changed = "", true
 	}
 	if changed {
-		if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, theme.JSON()); err != nil {
+		if err := blogStore(r).UpdateTheme(r.Context(), theme.JSON()); err != nil {
 			s.serverError(w, err)
 			return
 		}
diff --git a/internal/web/handlers_layout.go b/internal/web/handlers_layout.go
index b22c1b5..1046a19 100644
--- a/internal/web/handlers_layout.go
+++ b/internal/web/handlers_layout.go
@@ -25,15 +25,15 @@ func (s *Server) layoutURL(r *http.Request) string {
 // layoutData is everything layout.html needs; it is rebuilt after a failed form too.
 func (s *Server) layoutData(r *http.Request) (map[string]any, error) {
 	blog := currentBlog(r)
-	mods, err := s.st.ListModules(r.Context(), blog.ID)
+	mods, err := blogStore(r).ListModules(r.Context())
 	if err != nil {
 		return nil, err
 	}
-	menu, err := s.st.ListMenu(r.Context(), blog.ID)
+	menu, err := blogStore(r).ListMenu(r.Context())
 	if err != nil {
 		return nil, err
 	}
-	pages, err := s.st.ListPages(r.Context(), blog.ID)
+	pages, err := blogStore(r).ListPages(r.Context())
 	if err != nil {
 		return nil, err
 	}
@@ -63,7 +63,7 @@ func (s *Server) handleLayout(w http.ResponseWriter, r *http.Request) {
 func (s *Server) handleLayoutWidths(w http.ResponseWriter, r *http.Request) {
 	blog := currentBlog(r)
 	theme := LayoutFromForm(ParseTheme(blog.ThemeJSON), r.Form)
-	if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, theme.JSON()); err != nil {
+	if err := blogStore(r).UpdateTheme(r.Context(), theme.JSON()); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -81,7 +81,7 @@ func (s *Server) handleAreaToggle(w http.ResponseWriter, r *http.Request) {
 	theme := ParseTheme(blog.ThemeJSON)
 	on := !theme.AreaOn(area)
 	theme.SetAreaOn(area, on)
-	if err := s.st.UpdateBlogTheme(r.Context(), blog.ID, theme.JSON()); err != nil {
+	if err := blogStore(r).UpdateTheme(r.Context(), theme.JSON()); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -93,14 +93,13 @@ func (s *Server) handleAreaToggle(w http.ResponseWriter, r *http.Request) {
 }
 
 func (s *Server) handleModuleAdd(w http.ResponseWriter, r *http.Request) {
-	blog := currentBlog(r)
 	area, kind := r.PathValue("area"), r.FormValue("kind")
 	if !allowedKind(area, kind) {
 		s.plainError(w, http.StatusBadRequest, "That module cannot go in that area.")
 		return
 	}
 	if singleModuleAreas[area] {
-		mods, err := s.st.ListModules(r.Context(), blog.ID)
+		mods, err := blogStore(r).ListModules(r.Context())
 		if err != nil {
 			s.serverError(w, err)
 			return
@@ -112,7 +111,7 @@ func (s *Server) handleModuleAdd(w http.ResponseWriter, r *http.Request) {
 			}
 		}
 	}
-	m := &store.Module{BlogID: blog.ID, Area: area, Kind: kind, Count: 5}
+	m := &store.Module{Area: area, Kind: kind, Count: 5}
 	switch kind { // sensible headings so the module reads well before it is edited
 	case "archive":
 		m.Title = "Archive"
@@ -121,7 +120,7 @@ func (s *Server) handleModuleAdd(w http.ResponseWriter, r *http.Request) {
 	case "sitemap":
 		m.Title = "Pages"
 	}
-	m, err := s.st.CreateModule(r.Context(), m)
+	m, err := blogStore(r).CreateModule(r.Context(), m)
 	if err != nil {
 		s.serverError(w, err)
 		return
@@ -143,7 +142,7 @@ func anchorFor(area string) string {
 
 func (s *Server) loadModule(w http.ResponseWriter, r *http.Request) *store.Module {
 	id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64)
-	m, err := s.st.ModuleByID(r.Context(), currentBlog(r).ID, id)
+	m, err := blogStore(r).ModuleByID(r.Context(), id)
 	if err != nil {
 		if errors.Is(err, store.ErrNotFound) {
 			http.NotFound(w, r)
@@ -196,7 +195,7 @@ func (s *Server) handleModuleSave(w http.ResponseWriter, r *http.Request) {
 		fail("Content is too long (20 KB max).")
 		return
 	}
-	if err := s.st.UpdateModule(r.Context(), m); err != nil {
+	if err := blogStore(r).UpdateModule(r.Context(), m); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -212,7 +211,7 @@ func (s *Server) handleModuleMove(w http.ResponseWriter, r *http.Request) {
 	if r.FormValue("dir") == "up" {
 		dir = -1
 	}
-	if err := s.st.MoveModule(r.Context(), m.BlogID, m.ID, dir); err != nil {
+	if err := blogStore(r).MoveModule(r.Context(), m.ID, dir); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -240,7 +239,7 @@ func (s *Server) handleModuleDelete(w http.ResponseWriter, r *http.Request) {
 }
 
 func (s *Server) deleteModule(w http.ResponseWriter, r *http.Request, m *store.Module) {
-	if err := s.st.DeleteModule(r.Context(), m.BlogID, m.ID); err != nil {
+	if err := blogStore(r).DeleteModule(r.Context(), m.ID); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -250,13 +249,12 @@ func (s *Server) deleteModule(w http.ResponseWriter, r *http.Request, m *store.M
 // ---- menu --------------------------------------------------------------------
 
 func (s *Server) handleMenuAddPage(w http.ResponseWriter, r *http.Request) {
-	blog := currentBlog(r)
 	pageID, _ := strconv.ParseInt(r.FormValue("page"), 10, 64)
 	if pageID == 0 {
 		redirectOK(w, r, s.layoutURL(r)+"#menu", "Pick a page first.")
 		return
 	}
-	if err := s.st.AddMenuPage(r.Context(), blog.ID, pageID); err != nil {
+	if err := blogStore(r).AddMenuPage(r.Context(), pageID); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -264,7 +262,6 @@ func (s *Server) handleMenuAddPage(w http.ResponseWriter, r *http.Request) {
 }
 
 func (s *Server) handleMenuAddLink(w http.ResponseWriter, r *http.Request) {
-	blog := currentBlog(r)
 	label, link := strings.TrimSpace(r.FormValue("label")), strings.TrimSpace(r.FormValue("url"))
 	if link != "" && !strings.HasPrefix(link, "/") && !strings.Contains(link, ":") {
 		link = "https://" + link // "example.org" is what people type
@@ -277,7 +274,7 @@ func (s *Server) handleMenuAddLink(w http.ResponseWriter, r *http.Request) {
 		redirectOK(w, r, s.layoutURL(r)+"#menu", "That address does not look right. Use a full web address like https://example.org or a path like /about.")
 		return
 	}
-	if err := s.st.AddMenuLink(r.Context(), blog.ID, label, link); err != nil {
+	if err := blogStore(r).AddMenuLink(r.Context(), label, link); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -290,7 +287,7 @@ func (s *Server) handleMenuMove(w http.ResponseWriter, r *http.Request) {
 	if r.FormValue("dir") == "up" {
 		dir = -1
 	}
-	if err := s.st.MoveMenuItem(r.Context(), currentBlog(r).ID, id, dir); err != nil {
+	if err := blogStore(r).MoveMenuItem(r.Context(), id, dir); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -299,7 +296,7 @@ func (s *Server) handleMenuMove(w http.ResponseWriter, r *http.Request) {
 
 func (s *Server) handleMenuDelete(w http.ResponseWriter, r *http.Request) {
 	id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64)
-	if err := s.st.DeleteMenuItem(r.Context(), currentBlog(r).ID, id); err != nil {
+	if err := blogStore(r).DeleteMenuItem(r.Context(), id); err != nil {
 		s.serverError(w, err)
 		return
 	}
diff --git a/internal/web/handlers_media.go b/internal/web/handlers_media.go
index 9d5a515..4e237fa 100644
--- a/internal/web/handlers_media.go
+++ b/internal/web/handlers_media.go
@@ -22,7 +22,7 @@ func (s *Server) handleMedia(w http.ResponseWriter, r *http.Request) {
 		w.WriteHeader(http.StatusNotModified)
 		return
 	}
-	img, err := s.st.ImageData(r.Context(), id)
+	img, err := blogStore(r).ImageData(r.Context(), id)
 	if err != nil {
 		if errors.Is(err, store.ErrNotFound) {
 			http.NotFound(w, r)
diff --git a/internal/web/handlers_pages.go b/internal/web/handlers_pages.go
index a226953..09257a3 100644
--- a/internal/web/handlers_pages.go
+++ b/internal/web/handlers_pages.go
@@ -16,7 +16,7 @@ var reservedPageSlugs = map[string]bool{"media": true, "static": true, "feed.xml
 	"webadmin": true, "logout": true, "dashboard": true, "account": true, "admin": true, "b": true, "healthz": true}
 
 func (s *Server) handlePages(w http.ResponseWriter, r *http.Request) {
-	pages, err := s.st.ListPages(r.Context(), currentBlog(r).ID)
+	pages, err := blogStore(r).ListPages(r.Context())
 	if err != nil {
 		s.serverError(w, err)
 		return
@@ -27,7 +27,7 @@ func (s *Server) handlePages(w http.ResponseWriter, r *http.Request) {
 // loadPage fetches the page named in the URL, or nil (having written the response) on failure.
 func (s *Server) loadPage(w http.ResponseWriter, r *http.Request) *store.Page {
 	id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64)
-	p, err := s.st.PageByID(r.Context(), currentBlog(r).ID, id)
+	p, err := blogStore(r).PageByID(r.Context(), id)
 	if err != nil {
 		if errors.Is(err, store.ErrNotFound) {
 			http.NotFound(w, r)
@@ -51,7 +51,7 @@ func (s *Server) handlePageForm(w http.ResponseWriter, r *http.Request) {
 
 func (s *Server) handlePageSave(w http.ResponseWriter, r *http.Request) {
 	blog := currentBlog(r)
-	p := &store.Page{BlogID: blog.ID}
+	p := &store.Page{}
 	if r.PathValue("id") != "" {
 		if p = s.loadPage(w, r); p == nil {
 			return
@@ -86,11 +86,11 @@ func (s *Server) handlePageSave(w http.ResponseWriter, r *http.Request) {
 		p.Slug = slug.WithSuffix(base, n)
 		if p.ID == 0 {
 			var created *store.Page
-			if created, err = s.st.CreatePage(r.Context(), p); err == nil {
+			if created, err = blogStore(r).CreatePage(r.Context(), p); err == nil {
 				p = created
 			}
 		} else {
-			err = s.st.UpdatePage(r.Context(), p)
+			err = blogStore(r).UpdatePage(r.Context(), p)
 		}
 		if !errors.Is(err, store.ErrConflict) || !autoSlug || n >= 50 {
 			break
@@ -129,7 +129,7 @@ func (s *Server) handlePageDelete(w http.ResponseWriter, r *http.Request) {
 		s.plainError(w, http.StatusBadRequest, "The home page cannot be deleted. Make another page the home page first.")
 		return
 	}
-	if err := s.st.DeletePage(r.Context(), p.BlogID, p.ID); err != nil {
+	if err := blogStore(r).DeletePage(r.Context(), p.ID); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -145,7 +145,7 @@ func (s *Server) handlePageMove(w http.ResponseWriter, r *http.Request) {
 	if r.FormValue("dir") == "up" {
 		dir = -1
 	}
-	if err := s.st.MovePage(r.Context(), p.BlogID, p.ID, dir); err != nil {
+	if err := blogStore(r).MovePage(r.Context(), p.ID, dir); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -157,7 +157,7 @@ func (s *Server) handlePageHome(w http.ResponseWriter, r *http.Request) {
 	if p == nil {
 		return
 	}
-	if err := s.st.SetHomePage(r.Context(), p.BlogID, p.ID); err != nil {
+	if err := blogStore(r).SetHomePage(r.Context(), p.ID); err != nil {
 		s.serverError(w, err)
 		return
 	}
diff --git a/internal/web/handlers_posts.go b/internal/web/handlers_posts.go
index 136b7fe..02a3fd7 100644
--- a/internal/web/handlers_posts.go
+++ b/internal/web/handlers_posts.go
@@ -12,14 +12,13 @@ import (
 )
 
 func (s *Server) handlePosts(w http.ResponseWriter, r *http.Request) {
-	blog := currentBlog(r)
 	pageID, _ := strconv.ParseInt(r.URL.Query().Get("page"), 10, 64)
-	pages, err := s.st.ListPages(r.Context(), blog.ID)
+	pages, err := blogStore(r).ListPages(r.Context())
 	if err != nil {
 		s.serverError(w, err)
 		return
 	}
-	posts, err := s.st.ListPosts(r.Context(), blog.ID, pageID)
+	posts, err := blogStore(r).ListPosts(r.Context(), pageID)
 	if err != nil {
 		s.serverError(w, err)
 		return
@@ -29,7 +28,7 @@ func (s *Server) handlePosts(w http.ResponseWriter, r *http.Request) {
 
 func (s *Server) loadPost(w http.ResponseWriter, r *http.Request) *store.Post {
 	id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64)
-	p, err := s.st.PostByID(r.Context(), currentBlog(r).ID, id)
+	p, err := blogStore(r).PostByID(r.Context(), id)
 	if err != nil {
 		if errors.Is(err, store.ErrNotFound) {
 			http.NotFound(w, r)
@@ -42,8 +41,7 @@ func (s *Server) loadPost(w http.ResponseWriter, r *http.Request) *store.Post {
 }
 
 func (s *Server) handlePostForm(w http.ResponseWriter, r *http.Request) {
-	blog := currentBlog(r)
-	pages, err := s.st.ListPages(r.Context(), blog.ID)
+	pages, err := blogStore(r).ListPages(r.Context())
 	if err != nil {
 		s.serverError(w, err)
 		return
@@ -55,7 +53,7 @@ func (s *Server) handlePostForm(w http.ResponseWriter, r *http.Request) {
 		}
 	} else if pid, _ := strconv.ParseInt(r.URL.Query().Get("page"), 10, 64); pid != 0 {
 		p.PageID = pid
-	} else if hp, err := s.st.HomePage(r.Context(), blog.ID); err == nil {
+	} else if hp, err := blogStore(r).HomePage(r.Context()); err == nil {
 		p.PageID = hp.ID
 	}
 	s.render(w, r, "dashboard/post_form.html", map[string]any{"post": p, "pages": pages})
@@ -63,7 +61,7 @@ func (s *Server) handlePostForm(w http.ResponseWriter, r *http.Request) {
 
 func (s *Server) handlePostSave(w http.ResponseWriter, r *http.Request) {
 	blog := currentBlog(r)
-	pages, err := s.st.ListPages(r.Context(), blog.ID)
+	pages, err := blogStore(r).ListPages(r.Context())
 	if err != nil {
 		s.serverError(w, err)
 		return
@@ -118,11 +116,11 @@ func (s *Server) handlePostSave(w http.ResponseWriter, r *http.Request) {
 		p.Slug = slug.WithSuffix(base, n)
 		if p.ID == 0 {
 			var created *store.Post
-			if created, err = s.st.CreatePost(r.Context(), p); err == nil {
+			if created, err = blogStore(r).CreatePost(r.Context(), p); err == nil {
 				p = created
 			}
 		} else {
-			err = s.st.UpdatePost(r.Context(), p)
+			err = blogStore(r).UpdatePost(r.Context(), p)
 		}
 		if !errors.Is(err, store.ErrConflict) || !autoSlug || n >= 50 {
 			break
@@ -156,7 +154,7 @@ func (s *Server) handlePostDelete(w http.ResponseWriter, r *http.Request) {
 	if p == nil {
 		return
 	}
-	if err := s.st.DeletePost(r.Context(), currentBlog(r).ID, p.ID); err != nil {
+	if err := blogStore(r).DeletePost(r.Context(), p.ID); err != nil {
 		s.serverError(w, err)
 		return
 	}
diff --git a/internal/web/handlers_sections.go b/internal/web/handlers_sections.go
index f5e2e59..1239545 100644
--- a/internal/web/handlers_sections.go
+++ b/internal/web/handlers_sections.go
@@ -19,7 +19,7 @@ func (s *Server) sectionsURL(r *http.Request) string {
 }
 
 func (s *Server) handleSections(w http.ResponseWriter, r *http.Request) {
-	secs, err := s.st.ListSections(r.Context(), currentBlog(r).ID)
+	secs, err := blogStore(r).ListSections(r.Context())
 	if err != nil {
 		s.serverError(w, err)
 		return
@@ -29,7 +29,7 @@ func (s *Server) handleSections(w http.ResponseWriter, r *http.Request) {
 
 func (s *Server) loadSection(w http.ResponseWriter, r *http.Request) *store.Section {
 	id, _ := strconv.ParseInt(r.PathValue("id"), 10, 64)
-	sec, err := s.st.SectionByID(r.Context(), currentBlog(r).ID, id)
+	sec, err := blogStore(r).SectionByID(r.Context(), id)
 	if err != nil {
 		if errors.Is(err, store.ErrNotFound) {
 			http.NotFound(w, r)
@@ -58,8 +58,7 @@ func normalizeSection(sec *store.Section) {
 }
 
 func (s *Server) handleSectionSave(w http.ResponseWriter, r *http.Request) {
-	blog := currentBlog(r)
-	sec := &store.Section{BlogID: blog.ID}
+	sec := &store.Section{}
 	if r.PathValue("id") != "" {
 		if sec = s.loadSection(w, r); sec == nil {
 			return
@@ -92,9 +91,9 @@ func (s *Server) handleSectionSave(w http.ResponseWriter, r *http.Request) {
 	sec.BodyMD = appendImageMD(sec.BodyMD, img)
 	sec.BodyHTML = markdown.Render(sec.BodyMD)
 	if sec.ID == 0 {
-		sec, err = s.st.CreateSection(r.Context(), sec)
+		sec, err = blogStore(r).CreateSection(r.Context(), sec)
 	} else {
-		err = s.st.UpdateSection(r.Context(), sec)
+		err = blogStore(r).UpdateSection(r.Context(), sec)
 	}
 	if err != nil {
 		s.serverError(w, err)
@@ -120,7 +119,7 @@ func (s *Server) handleSectionDelete(w http.ResponseWriter, r *http.Request) {
 	if sec == nil {
 		return
 	}
-	if err := s.st.DeleteSection(r.Context(), sec.BlogID, sec.ID); err != nil {
+	if err := blogStore(r).DeleteSection(r.Context(), sec.ID); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -136,7 +135,7 @@ func (s *Server) handleSectionMove(w http.ResponseWriter, r *http.Request) {
 	if r.FormValue("dir") == "up" {
 		dir = -1
 	}
-	if err := s.st.MoveSection(r.Context(), sec.BlogID, sec.ID, dir); err != nil {
+	if err := blogStore(r).MoveSection(r.Context(), sec.ID, dir); err != nil {
 		s.serverError(w, err)
 		return
 	}
@@ -149,7 +148,7 @@ func (s *Server) handleSectionToggle(w http.ResponseWriter, r *http.Request) {
 	if sec == nil {
 		return
 	}
-	if err := s.st.SetSectionEnabled(r.Context(), sec.BlogID, sec.ID, !sec.Enabled); err != nil {
+	if err := blogStore(r).SetSectionEnabled(r.Context(), sec.ID, !sec.Enabled); err != nil {
 		s.serverError(w, err)
 		return
 	}
diff --git a/internal/web/routes.go b/internal/web/routes.go
index 4b3d14f..36ae892 100644
--- a/internal/web/routes.go
+++ b/internal/web/routes.go
@@ -66,6 +66,8 @@ func (s *Server) rootRoutes() http.Handler {
 	m.HandleFunc("GET /b/{sub}/images", s.withBlog(s.handleImages))
 	m.HandleFunc("POST /b/{sub}/images/upload", s.withBlog(s.handleImageUpload))
 	m.HandleFunc("POST /b/{sub}/images/{id}/delete", s.withBlog(s.handleImageDelete))
+	// Images live in the blog's database, so dashboard previews on this host go through /b/.
+	m.HandleFunc("GET /b/{sub}/media/{id}", s.withBlog(s.handleMedia))
 
 	// superadmin
 	m.HandleFunc("GET /admin/{$}", s.requireAdmin(s.handleAdmin))
@@ -77,7 +79,7 @@ func (s *Server) rootRoutes() http.Handler {
 	m.HandleFunc("GET /admin/users/{id}/delete", s.requireAdmin(s.handleAdminDeleteUserConfirm))
 	m.HandleFunc("POST /admin/users/{id}/delete", s.requireAdmin(s.handleAdminDeleteUser))
 
-	m.HandleFunc("GET /media/{id}", s.handleMedia)
+	m.HandleFunc("GET /media/{id}", s.hostBlog(s.handleMedia))
 	m.Handle("GET /static/{file}", s.staticHandler())
 	m.HandleFunc("GET /favicon.ico", s.hostBlog(s.handleFavicon))
 	// The root domain also serves the superadmin's blog. Literal routes above win
@@ -98,7 +100,7 @@ func (s *Server) subdomainRoutes() http.Handler {
 	m := http.NewServeMux()
 	// Bloggers type /webadmin on their own blog; send them to the real login page.
 	m.HandleFunc("GET /webadmin", s.handleWebadminRedirect)
-	m.HandleFunc("GET /media/{id}", s.handleMedia)
+	m.HandleFunc("GET /media/{id}", s.hostBlog(s.handleMedia))
 	m.Handle("GET /static/{file}", s.staticHandler())
 	m.HandleFunc("GET /favicon.ico", s.hostBlog(s.handleFavicon))
 	s.blogRoutes(m, s.hostBlog)
diff --git a/internal/web/server.go b/internal/web/server.go
index 483663e..a7af06b 100644
--- a/internal/web/server.go
+++ b/internal/web/server.go
@@ -66,9 +66,10 @@ func hostname(h string) string {
 type ctxKey int
 
 const (
-	ctxUser    ctxKey = iota
-	ctxBlog           // the blog a handler operates on (public page, or /b/{sub}/ management)
-	ctxHostSub        // subdomain derived from the Host header
+	ctxUser      ctxKey = iota
+	ctxBlog             // the blog a handler operates on (public page, or /b/{sub}/ management)
+	ctxBlogStore        // that blog's store, bound to its own database
+	ctxHostSub          // subdomain derived from the Host header
 )
 
 func currentUser(r *http.Request) *store.User {
@@ -81,13 +82,33 @@ func currentBlog(r *http.Request) *store.Blog {
 	return b
 }
 
+// blogStore is the store of the blog in the context; only valid behind hostBlog or withBlog.
+func blogStore(r *http.Request) *store.BlogStore {
+	bs, _ := r.Context().Value(ctxBlogStore).(*store.BlogStore)
+	return bs
+}
+
 // ---- middleware ----------------------------------------------------------
 
+// resolveBlog looks a subdomain up in the registry and opens its database.
+func (s *Server) resolveBlog(r *http.Request, sub string) (*http.Request, error) {
+	blog, err := s.st.BlogBySubdomain(r.Context(), sub)
+	if err != nil {
+		return r, err
+	}
+	bs, err := s.st.Open(r.Context(), blog)
+	if err != nil {
+		return r, err
+	}
+	ctx := context.WithValue(r.Context(), ctxBlog, blog)
+	return r.WithContext(context.WithValue(ctx, ctxBlogStore, bs)), nil
+}
+
 // hostBlog resolves the blog named by the Host header into the context for public pages.
 func (s *Server) hostBlog(next http.HandlerFunc) http.HandlerFunc {
 	return func(w http.ResponseWriter, r *http.Request) {
 		sub, _ := r.Context().Value(ctxHostSub).(string)
-		blog, err := s.st.BlogBySubdomain(r.Context(), sub)
+		r, err := s.resolveBlog(r, sub)
 		if err != nil {
 			if errors.Is(err, store.ErrNotFound) {
 				s.plainError(w, http.StatusNotFound, "No blog here (yet).")
@@ -96,7 +117,7 @@ func (s *Server) hostBlog(next http.HandlerFunc) http.HandlerFunc {
 			s.serverError(w, err)
 			return
 		}
-		next(w, r.WithContext(context.WithValue(r.Context(), ctxBlog, blog)))
+		next(w, r)
 	}
 }
 
@@ -176,7 +197,7 @@ func (s *Server) requireAdmin(next http.HandlerFunc) http.HandlerFunc {
 func (s *Server) withBlog(next http.HandlerFunc) http.HandlerFunc {
 	return s.requireAuth(func(w http.ResponseWriter, r *http.Request) {
 		u := currentUser(r)
-		blog, err := s.st.BlogBySubdomain(r.Context(), r.PathValue("sub"))
+		r, err := s.resolveBlog(r, r.PathValue("sub"))
 		if err != nil {
 			if errors.Is(err, store.ErrNotFound) {
 				http.NotFound(w, r)
@@ -185,11 +206,11 @@ func (s *Server) withBlog(next http.HandlerFunc) http.HandlerFunc {
 			s.serverError(w, err)
 			return
 		}
-		if blog.OwnerID != u.ID && !u.IsSuperadmin() {
+		if blog := currentBlog(r); blog.OwnerID != u.ID && !u.IsSuperadmin() {
 			s.plainError(w, http.StatusForbidden, "This is not your blog.")
 			return
 		}
-		next(w, r.WithContext(context.WithValue(r.Context(), ctxBlog, blog)))
+		next(w, r)
 	})
 }
 
diff --git a/internal/web/templates/admin/index.html b/internal/web/templates/admin/index.html
index ecb0112..8293368 100644
--- a/internal/web/templates/admin/index.html
+++ b/internal/web/templates/admin/index.html
@@ -10,7 +10,7 @@
   {{range .Data.users}}<tr{{if .Disabled}} class="disabled"{{end}}>
     <td>{{.Username}}{{if .Disabled}} <span class="tag">disabled</span>{{end}}</td>
     <td>{{.Role}}</td>
-    <td>{{if .Subdomain}}<a href="/b/{{deref .Subdomain}}/">{{deref .BlogTitle}}</a> <a href="{{$.Data.cfg.BlogURL (deref .Subdomain)}}" target="_blank" class="muted">{{if eq (deref .Subdomain) "www"}}{{$.Data.cfg.BaseDomain}}{{else}}{{deref .Subdomain}}{{end}} &#8599;</a>{{else}}<span class="muted">—</span>{{end}}</td>
+    <td>{{if .Subdomain}}<a href="/b/{{deref .Subdomain}}/">{{if eq (deref .Subdomain) "www"}}{{$.Data.cfg.BaseDomain}}{{else}}{{deref .Subdomain}}{{end}}</a> <a href="{{$.Data.cfg.BlogURL (deref .Subdomain)}}" target="_blank" class="muted">view &#8599;</a>{{else}}<span class="muted">—</span>{{end}}</td>
     <td class="nowrap">{{date .CreatedAt}}</td>
     <td class="nowrap">
       {{if ne .ID $.User.ID}}
diff --git a/internal/web/templates/dashboard/design.html b/internal/web/templates/dashboard/design.html
index 4c86c79..9c2a12f 100644
--- a/internal/web/templates/dashboard/design.html
+++ b/internal/web/templates/dashboard/design.html
@@ -34,7 +34,7 @@
       <option value="tile"{{if eq $t.BgMode "tile"}} selected{{end}}>Repeat (tile)</option>
     </select></label>
   </div>
-  {{template "imagepick" (dict "name" "bg_image" "current" $t.BgImage "images" .Data.images "label" "Background image")}}
+  {{template "imagepick" (dict "name" "bg_image" "current" $t.BgImage "sub" .Blog.Subdomain "images" .Data.images "label" "Background image")}}
 </div>
 
 <div class="card" id="text">
@@ -102,7 +102,7 @@
     </select></label>
   </div>
   <p class="muted small">The title, logo and menu are header modules — add, remove and order them on the <a href="/b/{{.Blog.Subdomain}}/layout#header">Layout</a> tab. The banner below is shown across the top of the header whenever one is chosen.</p>
-  {{template "imagepick" (dict "name" "header_image" "current" $t.HeaderImage "images" .Data.images "label" "Header image (banner)")}}
+  {{template "imagepick" (dict "name" "header_image" "current" $t.HeaderImage "sub" .Blog.Subdomain "images" .Data.images "label" "Header image (banner)")}}
 </div>
 
 <div class="card" id="menu">
@@ -161,8 +161,8 @@
       <option value="large"{{if eq $t.LogoSize "large"}} selected{{end}}>Large (140 px)</option>
     </select></label>
   </div>
-  {{template "imagepick" (dict "name" "logo" "current" $t.Logo "images" .Data.images "label" "Logo" "hint" "Shown wherever you place a Logo module (Layout tab); it links to your front page.")}}
-  {{template "imagepick" (dict "name" "favicon" "current" $t.Favicon "images" .Data.images "label" "Favicon" "hint" "The small icon browsers show in the tab. A square PNG or ICO of 32–64 px works best; pick None for the Blogspace icon." "accept" "image/png,image/x-icon,image/gif,image/webp,image/jpeg,.ico")}}
+  {{template "imagepick" (dict "name" "logo" "current" $t.Logo "sub" .Blog.Subdomain "images" .Data.images "label" "Logo" "hint" "Shown wherever you place a Logo module (Layout tab); it links to your front page.")}}
+  {{template "imagepick" (dict "name" "favicon" "current" $t.Favicon "sub" .Blog.Subdomain "images" .Data.images "label" "Favicon" "hint" "The small icon browsers show in the tab. A square PNG or ICO of 32–64 px works best; pick None for the Blogspace icon." "accept" "image/png,image/x-icon,image/gif,image/webp,image/jpeg,.ico")}}
 </div>
 
 <div class="savebar">
diff --git a/internal/web/templates/dashboard/images.html b/internal/web/templates/dashboard/images.html
index 1c64391..2e39531 100644
--- a/internal/web/templates/dashboard/images.html
+++ b/internal/web/templates/dashboard/images.html
@@ -14,7 +14,7 @@
 <div class="gallery">
 {{range .Data.images}}
   <div class="card thumb">
-    <a href="/media/{{.ID}}" target="_blank"><img src="/media/{{.ID}}" alt="{{.Filename}}"></a>
+    <a href="/b/{{$.Blog.Subdomain}}/media/{{.ID}}" target="_blank"><img src="/b/{{$.Blog.Subdomain}}/media/{{.ID}}" alt="{{.Filename}}"></a>
     <div class="meta">{{.Filename}} <span class="muted">({{kb .Size}})</span></div>
     <input class="copy" readonly value="![{{.Filename}}](/media/{{.ID}})" onclick="this.select()">
     <form method="post" action="/b/{{$.Blog.Subdomain}}/images/{{.ID}}/delete" class="inline"><input type="hidden" name="_csrf" value="{{$.CSRF}}"><button class="mini danger">delete</button></form>
diff --git a/internal/web/templates/partials/imagepick.html b/internal/web/templates/partials/imagepick.html
index 0c6eced..4431d02 100644
--- a/internal/web/templates/partials/imagepick.html
+++ b/internal/web/templates/partials/imagepick.html
@@ -3,7 +3,7 @@
   {{if .hint}}<p class="muted small">{{.hint}}</p>{{end}}
   <div class="picks">
     <label class="pick none"><input type="radio" name="{{.name}}" value="none"{{if not .current}} checked{{end}}><span class="swatch">&times;</span><span class="name">None</span></label>
-    {{range .images}}<label class="pick"><input type="radio" name="{{$.name}}" value="{{.ID}}"{{if eq .ID.String $.current}} checked{{end}}><img src="/media/{{.ID}}" alt=""><span class="name">{{.Filename}}</span></label>{{end}}
+    {{range .images}}<label class="pick"><input type="radio" name="{{$.name}}" value="{{.ID}}"{{if eq .ID.String $.current}} checked{{end}}><img src="/b/{{$.sub}}/media/{{.ID}}" alt=""><span class="name">{{.Filename}}</span></label>{{end}}
   </div>
   <label>…or upload a new one<br><input type="file" name="{{.name}}_file" accept="{{if .accept}}{{.accept}}{{else}}image/*{{end}}"></label>
 </div>{{end}}
diff --git a/internal/web/web_test.go b/internal/web/web_test.go
index a988a23..5078c44 100644
--- a/internal/web/web_test.go
+++ b/internal/web/web_test.go
@@ -48,10 +48,17 @@ func TestHostRoutingWithoutDB(t *testing.T) {
 }
 
 // On the root domain the literal management routes must win over the blog's /{page} wildcards.
+// "blog_" + subdomain must fit a 63-char Postgres database name.
+func TestSubdomainLength(t *testing.T) {
+	if !subdomainRe.MatchString(strings.Repeat("a", 58)) || subdomainRe.MatchString(strings.Repeat("a", 59)) {
+		t.Error("subdomains must be at most 58 chars")
+	}
+}
+
 func TestRootRoutePrecedence(t *testing.T) {
 	cfg := &config.Config{BaseDomain: "example.com", JWTSecret: []byte("x"), MaxUploadBytes: 1 << 20}
 	s := NewServer(cfg, nil)
-	for path, want := range map[string]int{"/webadmin": 200, "/healthz": 200, "/dashboard": 303, "/admin/": 303, "/b/alice/posts": 303, "/b/alice/announcements": 303, "/b/alice/layout": 303, "/account/password": 303} {
+	for path, want := range map[string]int{"/webadmin": 200, "/healthz": 200, "/dashboard": 303, "/admin/": 303, "/b/alice/posts": 303, "/b/alice/announcements": 303, "/b/alice/layout": 303, "/b/alice/media/6ba7b810-9dad-11d1-80b4-00c04fd430c8": 303, "/account/password": 303} {
 		rec := httptest.NewRecorder()
 		req := httptest.NewRequest("GET", path, nil)
 		req.Host = "example.com"