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, 34 insertions, 35 deletions

diff --git a/internal/store/pages.go b/internal/store/pages.go
index cd0e652..55cad33 100644
--- a/internal/store/pages.go
+++ b/internal/store/pages.go
@@ -7,7 +7,6 @@ import (
 
 type Page struct {
 	ID        int64
-	BlogID    int64
 	Slug      string
 	Title     string
 	IntroMD   string
@@ -19,20 +18,20 @@ type Page struct {
 	PostCount int // filled by ListPages only
 }
 
-const pageCols = `id, blog_id, slug, title, intro_md, intro_html, nav_order, EXISTS (SELECT 1 FROM menu_items m WHERE m.page_id=pages.id), is_home, created_at`
+const pageCols = `id, slug, title, intro_md, intro_html, nav_order, EXISTS (SELECT 1 FROM menu_items m WHERE m.page_id=pages.id), is_home, created_at`
 
 func scanPage(row interface{ Scan(...any) error }) (*Page, error) {
 	var p Page
-	err := row.Scan(&p.ID, &p.BlogID, &p.Slug, &p.Title, &p.IntroMD, &p.IntroHTML, &p.NavOrder, &p.ShowInNav, &p.IsHome, &p.CreatedAt)
+	err := row.Scan(&p.ID, &p.Slug, &p.Title, &p.IntroMD, &p.IntroHTML, &p.NavOrder, &p.ShowInNav, &p.IsHome, &p.CreatedAt)
 	if err != nil {
 		return nil, wrap(err)
 	}
 	return &p, nil
 }
 
-func (s *Store) ListPages(ctx context.Context, blogID int64) ([]Page, error) {
-	rows, err := s.db.Query(ctx, `SELECT `+pageCols+`, (SELECT count(*) FROM posts WHERE page_id=pages.id)
-		FROM pages WHERE blog_id=$1 ORDER BY nav_order, id`, blogID)
+func (bs *BlogStore) ListPages(ctx context.Context) ([]Page, error) {
+	rows, err := bs.db.Query(ctx, `SELECT `+pageCols+`, (SELECT count(*) FROM posts WHERE page_id=pages.id)
+		FROM pages ORDER BY nav_order, id`)
 	if err != nil {
 		return nil, err
 	}
@@ -40,7 +39,7 @@ func (s *Store) ListPages(ctx context.Context, blogID int64) ([]Page, error) {
 	var out []Page
 	for rows.Next() {
 		var p Page
-		if err := rows.Scan(&p.ID, &p.BlogID, &p.Slug, &p.Title, &p.IntroMD, &p.IntroHTML, &p.NavOrder, &p.ShowInNav, &p.IsHome, &p.CreatedAt, &p.PostCount); err != nil {
+		if err := rows.Scan(&p.ID, &p.Slug, &p.Title, &p.IntroMD, &p.IntroHTML, &p.NavOrder, &p.ShowInNav, &p.IsHome, &p.CreatedAt, &p.PostCount); err != nil {
 			return nil, err
 		}
 		out = append(out, p)
@@ -48,34 +47,34 @@ func (s *Store) ListPages(ctx context.Context, blogID int64) ([]Page, error) {
 	return out, rows.Err()
 }
 
-func (s *Store) PageByID(ctx context.Context, blogID, id int64) (*Page, error) {
-	return scanPage(s.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE blog_id=$1 AND id=$2`, blogID, id))
+func (bs *BlogStore) PageByID(ctx context.Context, id int64) (*Page, error) {
+	return scanPage(bs.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE id=$1`, id))
 }
 
-func (s *Store) PageBySlug(ctx context.Context, blogID int64, slug string) (*Page, error) {
-	return scanPage(s.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE blog_id=$1 AND slug=$2`, blogID, slug))
+func (bs *BlogStore) PageBySlug(ctx context.Context, slug string) (*Page, error) {
+	return scanPage(bs.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE slug=$1`, slug))
 }
 
-func (s *Store) HomePage(ctx context.Context, blogID int64) (*Page, error) {
-	return scanPage(s.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages WHERE blog_id=$1 ORDER BY is_home DESC, nav_order, id LIMIT 1`, blogID))
+func (bs *BlogStore) HomePage(ctx context.Context) (*Page, error) {
+	return scanPage(bs.db.QueryRow(ctx, `SELECT `+pageCols+` FROM pages ORDER BY is_home DESC, nav_order, id LIMIT 1`))
 }
 
 // CreatePage adds a page at the end of the page list and, when ShowInNav is
 // set, to the end of the menu.
-func (s *Store) CreatePage(ctx context.Context, p *Page) (*Page, error) {
-	tx, err := s.db.Begin(ctx)
+func (bs *BlogStore) CreatePage(ctx context.Context, p *Page) (*Page, error) {
+	tx, err := bs.db.Begin(ctx)
 	if err != nil {
 		return nil, err
 	}
 	defer tx.Rollback(ctx)
-	created, err := scanPage(tx.QueryRow(ctx, `INSERT INTO pages (blog_id, slug, title, intro_md, intro_html, nav_order)
-		VALUES ($1,$2,$3,$4,$5,(SELECT coalesce(max(nav_order),-1)+1 FROM pages WHERE blog_id=$1)) RETURNING `+pageCols,
-		p.BlogID, p.Slug, p.Title, p.IntroMD, p.IntroHTML))
+	created, err := scanPage(tx.QueryRow(ctx, `INSERT INTO pages (slug, title, intro_md, intro_html, nav_order)
+		VALUES ($1,$2,$3,$4,(SELECT coalesce(max(nav_order),-1)+1 FROM pages)) RETURNING `+pageCols,
+		p.Slug, p.Title, p.IntroMD, p.IntroHTML))
 	if err != nil {
 		return nil, err
 	}
 	if p.ShowInNav {
-		if err := addMenuPage(ctx, tx, p.BlogID, created.ID); err != nil {
+		if err := addMenuPage(ctx, tx, created.ID); err != nil {
 			return nil, err
 		}
 		created.ShowInNav = true
@@ -84,20 +83,20 @@ func (s *Store) CreatePage(ctx context.Context, p *Page) (*Page, error) {
 }
 
 // UpdatePage saves the page and keeps its menu entry in step with ShowInNav.
-func (s *Store) UpdatePage(ctx context.Context, p *Page) error {
-	tx, err := s.db.Begin(ctx)
+func (bs *BlogStore) UpdatePage(ctx context.Context, p *Page) error {
+	tx, err := bs.db.Begin(ctx)
 	if err != nil {
 		return err
 	}
 	defer tx.Rollback(ctx)
-	if _, err := tx.Exec(ctx, `UPDATE pages SET slug=$3, title=$4, intro_md=$5, intro_html=$6 WHERE blog_id=$1 AND id=$2`,
-		p.BlogID, p.ID, p.Slug, p.Title, p.IntroMD, p.IntroHTML); err != nil {
+	if _, err := tx.Exec(ctx, `UPDATE pages SET slug=$2, title=$3, intro_md=$4, intro_html=$5 WHERE id=$1`,
+		p.ID, p.Slug, p.Title, p.IntroMD, p.IntroHTML); err != nil {
 		return wrap(err)
 	}
 	if p.ShowInNav {
-		err = addMenuPage(ctx, tx, p.BlogID, p.ID)
+		err = addMenuPage(ctx, tx, p.ID)
 	} else {
-		_, err = tx.Exec(ctx, `DELETE FROM menu_items WHERE blog_id=$1 AND page_id=$2`, p.BlogID, p.ID)
+		_, err = tx.Exec(ctx, `DELETE FROM menu_items WHERE page_id=$1`, p.ID)
 	}
 	if err != nil {
 		return err
@@ -105,30 +104,30 @@ func (s *Store) UpdatePage(ctx context.Context, p *Page) error {
 	return tx.Commit(ctx)
 }
 
-func (s *Store) DeletePage(ctx context.Context, blogID, id int64) error {
-	_, err := s.db.Exec(ctx, `DELETE FROM pages WHERE blog_id=$1 AND id=$2 AND NOT is_home`, blogID, id)
+func (bs *BlogStore) DeletePage(ctx context.Context, id int64) error {
+	_, err := bs.db.Exec(ctx, `DELETE FROM pages WHERE id=$1 AND NOT is_home`, id)
 	return err
 }
 
 // SetHomePage moves the home flag to the given page.
-func (s *Store) SetHomePage(ctx context.Context, blogID, id int64) error {
-	tx, err := s.db.Begin(ctx)
+func (bs *BlogStore) SetHomePage(ctx context.Context, id int64) error {
+	tx, err := bs.db.Begin(ctx)
 	if err != nil {
 		return err
 	}
 	defer tx.Rollback(ctx)
-	if _, err := tx.Exec(ctx, `UPDATE pages SET is_home=false WHERE blog_id=$1 AND is_home`, blogID); err != nil {
+	if _, err := tx.Exec(ctx, `UPDATE pages SET is_home=false WHERE is_home`); err != nil {
 		return err
 	}
-	if _, err := tx.Exec(ctx, `UPDATE pages SET is_home=true WHERE blog_id=$1 AND id=$2`, blogID, id); err != nil {
+	if _, err := tx.Exec(ctx, `UPDATE pages SET is_home=true WHERE id=$1`, id); err != nil {
 		return err
 	}
 	return tx.Commit(ctx)
 }
 
 // MovePage swaps nav_order with the neighbouring page (dir = -1 up, +1 down).
-func (s *Store) MovePage(ctx context.Context, blogID, id int64, dir int) error {
-	pages, err := s.ListPages(ctx, blogID)
+func (bs *BlogStore) MovePage(ctx context.Context, id int64, dir int) error {
+	pages, err := bs.ListPages(ctx)
 	if err != nil {
 		return err
 	}
@@ -143,13 +142,13 @@ func (s *Store) MovePage(ctx context.Context, blogID, id int64, dir int) error {
 		return nil
 	}
 	pages[idx], pages[j] = pages[j], pages[idx]
-	tx, err := s.db.Begin(ctx)
+	tx, err := bs.db.Begin(ctx)
 	if err != nil {
 		return err
 	}
 	defer tx.Rollback(ctx)
 	for i, p := range pages { // renumber everything; keeps orders dense
-		if _, err := tx.Exec(ctx, `UPDATE pages SET nav_order=$3 WHERE blog_id=$1 AND id=$2`, blogID, p.ID, i); err != nil {
+		if _, err := tx.Exec(ctx, `UPDATE pages SET nav_order=$2 WHERE id=$1`, p.ID, i); err != nil {
 			return err
 		}
 	}