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, 25 insertions, 26 deletions

diff --git a/internal/store/sections.go b/internal/store/sections.go
index caac251..2f742f7 100644
--- a/internal/store/sections.go
+++ b/internal/store/sections.go
@@ -9,7 +9,6 @@ import (
 // Section is an announcement shown on every page and post of a blog.
 type Section struct {
 	ID        int64
-	BlogID    int64
 	Title     string
 	BodyMD    string
 	BodyHTML  string
@@ -25,19 +24,19 @@ type Section struct {
 func (s Section) Column() string   { c, _, _ := strings.Cut(s.Placement, "-"); return c }
 func (s Section) Position() string { _, p, _ := strings.Cut(s.Placement, "-"); return p }
 
-const sectionCols = `id, blog_id, title, body_md, body_html, placement, style, enabled, sort_order, created_at, updated_at`
+const sectionCols = `id, title, body_md, body_html, placement, style, enabled, sort_order, created_at, updated_at`
 
 func scanSection(row interface{ Scan(...any) error }) (*Section, error) {
 	var s Section
-	err := row.Scan(&s.ID, &s.BlogID, &s.Title, &s.BodyMD, &s.BodyHTML, &s.Placement, &s.Style, &s.Enabled, &s.SortOrder, &s.CreatedAt, &s.UpdatedAt)
+	err := row.Scan(&s.ID, &s.Title, &s.BodyMD, &s.BodyHTML, &s.Placement, &s.Style, &s.Enabled, &s.SortOrder, &s.CreatedAt, &s.UpdatedAt)
 	if err != nil {
 		return nil, wrap(err)
 	}
 	return &s, nil
 }
 
-func (s *Store) collectSections(ctx context.Context, q string, args ...any) ([]Section, error) {
-	rows, err := s.db.Query(ctx, q, args...)
+func (bs *BlogStore) collectSections(ctx context.Context, q string, args ...any) ([]Section, error) {
+	rows, err := bs.db.Query(ctx, q, args...)
 	if err != nil {
 		return nil, err
 	}
@@ -53,44 +52,44 @@ func (s *Store) collectSections(ctx context.Context, q string, args ...any) ([]S
 	return out, rows.Err()
 }
 
-func (s *Store) ListSections(ctx context.Context, blogID int64) ([]Section, error) {
-	return s.collectSections(ctx, `SELECT `+sectionCols+` FROM sections WHERE blog_id=$1 ORDER BY sort_order, id`, blogID)
+func (bs *BlogStore) ListSections(ctx context.Context) ([]Section, error) {
+	return bs.collectSections(ctx, `SELECT `+sectionCols+` FROM sections ORDER BY sort_order, id`)
 }
 
 // EnabledSections is what the public blog renders.
-func (s *Store) EnabledSections(ctx context.Context, blogID int64) ([]Section, error) {
-	return s.collectSections(ctx, `SELECT `+sectionCols+` FROM sections WHERE blog_id=$1 AND enabled ORDER BY sort_order, id`, blogID)
+func (bs *BlogStore) EnabledSections(ctx context.Context) ([]Section, error) {
+	return bs.collectSections(ctx, `SELECT `+sectionCols+` FROM sections WHERE enabled ORDER BY sort_order, id`)
 }
 
-func (s *Store) SectionByID(ctx context.Context, blogID, id int64) (*Section, error) {
-	return scanSection(s.db.QueryRow(ctx, `SELECT `+sectionCols+` FROM sections WHERE blog_id=$1 AND id=$2`, blogID, id))
+func (bs *BlogStore) SectionByID(ctx context.Context, id int64) (*Section, error) {
+	return scanSection(bs.db.QueryRow(ctx, `SELECT `+sectionCols+` FROM sections WHERE id=$1`, id))
 }
 
-func (s *Store) CreateSection(ctx context.Context, sec *Section) (*Section, error) {
-	return scanSection(s.db.QueryRow(ctx, `INSERT INTO sections (blog_id, title, body_md, body_html, placement, style, enabled, sort_order)
-		VALUES ($1,$2,$3,$4,$5,$6,$7,(SELECT coalesce(max(sort_order),-1)+1 FROM sections WHERE blog_id=$1)) RETURNING `+sectionCols,
-		sec.BlogID, sec.Title, sec.BodyMD, sec.BodyHTML, sec.Placement, sec.Style, sec.Enabled))
+func (bs *BlogStore) CreateSection(ctx context.Context, sec *Section) (*Section, error) {
+	return scanSection(bs.db.QueryRow(ctx, `INSERT INTO sections (title, body_md, body_html, placement, style, enabled, sort_order)
+		VALUES ($1,$2,$3,$4,$5,$6,(SELECT coalesce(max(sort_order),-1)+1 FROM sections)) RETURNING `+sectionCols,
+		sec.Title, sec.BodyMD, sec.BodyHTML, sec.Placement, sec.Style, sec.Enabled))
 }
 
-func (s *Store) UpdateSection(ctx context.Context, sec *Section) error {
-	_, err := s.db.Exec(ctx, `UPDATE sections SET title=$3, body_md=$4, body_html=$5, placement=$6, style=$7, enabled=$8, updated_at=now() WHERE blog_id=$1 AND id=$2`,
-		sec.BlogID, sec.ID, sec.Title, sec.BodyMD, sec.BodyHTML, sec.Placement, sec.Style, sec.Enabled)
+func (bs *BlogStore) UpdateSection(ctx context.Context, sec *Section) error {
+	_, err := bs.db.Exec(ctx, `UPDATE sections SET title=$2, body_md=$3, body_html=$4, placement=$5, style=$6, enabled=$7, updated_at=now() WHERE id=$1`,
+		sec.ID, sec.Title, sec.BodyMD, sec.BodyHTML, sec.Placement, sec.Style, sec.Enabled)
 	return wrap(err)
 }
 
-func (s *Store) SetSectionEnabled(ctx context.Context, blogID, id int64, on bool) error {
-	_, err := s.db.Exec(ctx, `UPDATE sections SET enabled=$3, updated_at=now() WHERE blog_id=$1 AND id=$2`, blogID, id, on)
+func (bs *BlogStore) SetSectionEnabled(ctx context.Context, id int64, on bool) error {
+	_, err := bs.db.Exec(ctx, `UPDATE sections SET enabled=$2, updated_at=now() WHERE id=$1`, id, on)
 	return err
 }
 
-func (s *Store) DeleteSection(ctx context.Context, blogID, id int64) error {
-	_, err := s.db.Exec(ctx, `DELETE FROM sections WHERE blog_id=$1 AND id=$2`, blogID, id)
+func (bs *BlogStore) DeleteSection(ctx context.Context, id int64) error {
+	_, err := bs.db.Exec(ctx, `DELETE FROM sections WHERE id=$1`, id)
 	return err
 }
 
 // MoveSection swaps sort_order with the neighbouring section (dir = -1 up, +1 down).
-func (s *Store) MoveSection(ctx context.Context, blogID, id int64, dir int) error {
-	secs, err := s.ListSections(ctx, blogID)
+func (bs *BlogStore) MoveSection(ctx context.Context, id int64, dir int) error {
+	secs, err := bs.ListSections(ctx)
 	if err != nil {
 		return err
 	}
@@ -105,13 +104,13 @@ func (s *Store) MoveSection(ctx context.Context, blogID, id int64, dir int) erro
 		return nil
 	}
 	secs[idx], secs[j] = secs[j], secs[idx]
-	tx, err := s.db.Begin(ctx)
+	tx, err := bs.db.Begin(ctx)
 	if err != nil {
 		return err
 	}
 	defer tx.Rollback(ctx)
 	for i, sec := range secs { // renumber everything; keeps orders dense
-		if _, err := tx.Exec(ctx, `UPDATE sections SET sort_order=$3 WHERE blog_id=$1 AND id=$2`, blogID, sec.ID, i); err != nil {
+		if _, err := tx.Exec(ctx, `UPDATE sections SET sort_order=$2 WHERE id=$1`, sec.ID, i); err != nil {
 			return err
 		}
 	}