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, 23 insertions, 31 deletions

diff --git a/internal/store/menu.go b/internal/store/menu.go
index 2d3eabd..cedb7ad 100644
--- a/internal/store/menu.go
+++ b/internal/store/menu.go
@@ -2,14 +2,11 @@ package store
 
 import (
 	"context"
-
-	"github.com/jackc/pgx/v5/pgconn"
 )
 
 // MenuItem is one entry of a blog's menu: either a page of the blog or a custom link.
 type MenuItem struct {
 	ID        int64
-	BlogID    int64
 	PageID    *int64 // nil for custom links
 	Label     string // custom links only; pages use their title
 	URL       string // custom links only
@@ -39,11 +36,11 @@ func (m MenuItem) Href() string {
 	return "/" + m.PageSlug
 }
 
-const menuCols = `m.id, m.blog_id, m.page_id, m.label, m.url, m.sort_order, coalesce(p.title,''), coalesce(p.slug,''), coalesce(p.is_home,false)`
+const menuCols = `m.id, m.page_id, m.label, m.url, m.sort_order, coalesce(p.title,''), coalesce(p.slug,''), coalesce(p.is_home,false)`
 
-func (s *Store) ListMenu(ctx context.Context, blogID int64) ([]MenuItem, error) {
-	rows, err := s.db.Query(ctx, `SELECT `+menuCols+` FROM menu_items m LEFT JOIN pages p ON p.id=m.page_id
-		WHERE m.blog_id=$1 ORDER BY m.sort_order, m.id`, blogID)
+func (bs *BlogStore) ListMenu(ctx context.Context) ([]MenuItem, error) {
+	rows, err := bs.db.Query(ctx, `SELECT `+menuCols+` FROM menu_items m LEFT JOIN pages p ON p.id=m.page_id
+		ORDER BY m.sort_order, m.id`)
 	if err != nil {
 		return nil, err
 	}
@@ -51,7 +48,7 @@ func (s *Store) ListMenu(ctx context.Context, blogID int64) ([]MenuItem, error)
 	var out []MenuItem
 	for rows.Next() {
 		var m MenuItem
-		if err := rows.Scan(&m.ID, &m.BlogID, &m.PageID, &m.Label, &m.URL, &m.SortOrder, &m.PageTitle, &m.PageSlug, &m.IsHome); err != nil {
+		if err := rows.Scan(&m.ID, &m.PageID, &m.Label, &m.URL, &m.SortOrder, &m.PageTitle, &m.PageSlug, &m.IsHome); err != nil {
 			return nil, err
 		}
 		out = append(out, m)
@@ -60,41 +57,41 @@ func (s *Store) ListMenu(ctx context.Context, blogID int64) ([]MenuItem, error)
 }
 
 // AddMenuPage appends a page to the menu; a page already in the menu is left where it is.
-func (s *Store) AddMenuPage(ctx context.Context, blogID, pageID int64) error {
-	return addMenuPage(ctx, s.db, blogID, pageID)
+func (bs *BlogStore) AddMenuPage(ctx context.Context, pageID int64) error {
+	return addMenuPage(ctx, bs.db, pageID)
 }
 
-func addMenuPage(ctx context.Context, db querier, blogID, pageID int64) error {
-	_, err := db.Exec(ctx, `INSERT INTO menu_items (blog_id, page_id, sort_order)
-		SELECT $1, id, (SELECT coalesce(max(sort_order),-1)+1 FROM menu_items WHERE blog_id=$1)
-		FROM pages WHERE blog_id=$1 AND id=$2 ON CONFLICT (page_id) WHERE page_id IS NOT NULL DO NOTHING`, blogID, pageID)
+func addMenuPage(ctx context.Context, db querier, pageID int64) error {
+	_, err := db.Exec(ctx, `INSERT INTO menu_items (page_id, sort_order)
+		SELECT id, (SELECT coalesce(max(sort_order),-1)+1 FROM menu_items)
+		FROM pages WHERE id=$1 ON CONFLICT (page_id) WHERE page_id IS NOT NULL DO NOTHING`, pageID)
 	return err
 }
 
 // SetPageInMenu adds or removes a page's menu entry.
-func (s *Store) SetPageInMenu(ctx context.Context, blogID, pageID int64, on bool) error {
+func (bs *BlogStore) SetPageInMenu(ctx context.Context, pageID int64, on bool) error {
 	if on {
-		return s.AddMenuPage(ctx, blogID, pageID)
+		return bs.AddMenuPage(ctx, pageID)
 	}
-	_, err := s.db.Exec(ctx, `DELETE FROM menu_items WHERE blog_id=$1 AND page_id=$2`, blogID, pageID)
+	_, err := bs.db.Exec(ctx, `DELETE FROM menu_items WHERE page_id=$1`, pageID)
 	return err
 }
 
 // AddMenuLink appends a custom link to the menu.
-func (s *Store) AddMenuLink(ctx context.Context, blogID int64, label, url string) error {
-	_, err := s.db.Exec(ctx, `INSERT INTO menu_items (blog_id, label, url, sort_order)
-		VALUES ($1,$2,$3,(SELECT coalesce(max(sort_order),-1)+1 FROM menu_items WHERE blog_id=$1))`, blogID, label, url)
+func (bs *BlogStore) AddMenuLink(ctx context.Context, label, url string) error {
+	_, err := bs.db.Exec(ctx, `INSERT INTO menu_items (label, url, sort_order)
+		VALUES ($1,$2,(SELECT coalesce(max(sort_order),-1)+1 FROM menu_items))`, label, url)
 	return err
 }
 
-func (s *Store) DeleteMenuItem(ctx context.Context, blogID, id int64) error {
-	_, err := s.db.Exec(ctx, `DELETE FROM menu_items WHERE blog_id=$1 AND id=$2`, blogID, id)
+func (bs *BlogStore) DeleteMenuItem(ctx context.Context, id int64) error {
+	_, err := bs.db.Exec(ctx, `DELETE FROM menu_items WHERE id=$1`, id)
 	return err
 }
 
 // MoveMenuItem swaps sort_order with the neighbouring entry (dir = -1 up, +1 down).
-func (s *Store) MoveMenuItem(ctx context.Context, blogID, id int64, dir int) error {
-	items, err := s.ListMenu(ctx, blogID)
+func (bs *BlogStore) MoveMenuItem(ctx context.Context, id int64, dir int) error {
+	items, err := bs.ListMenu(ctx)
 	if err != nil {
 		return err
 	}
@@ -109,20 +106,15 @@ func (s *Store) MoveMenuItem(ctx context.Context, blogID, id int64, dir int) err
 		return nil
 	}
 	items[idx], items[j] = items[j], items[idx]
-	tx, err := s.db.Begin(ctx)
+	tx, err := bs.db.Begin(ctx)
 	if err != nil {
 		return err
 	}
 	defer tx.Rollback(ctx)
 	for i, m := range items { // renumber everything; keeps orders dense
-		if _, err := tx.Exec(ctx, `UPDATE menu_items SET sort_order=$3 WHERE blog_id=$1 AND id=$2`, blogID, m.ID, i); err != nil {
+		if _, err := tx.Exec(ctx, `UPDATE menu_items SET sort_order=$2 WHERE id=$1`, m.ID, i); err != nil {
 			return err
 		}
 	}
 	return tx.Commit(ctx)
 }
-
-// querier is what the helpers shared with page transactions need: the pool or a pgx.Tx.
-type querier interface {
-	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
-}