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, 94 insertions, 24 deletions

diff --git a/internal/store/blogs.go b/internal/store/blogs.go
index b7edee0..a2e88a8 100644
--- a/internal/store/blogs.go
+++ b/internal/store/blogs.go
@@ -3,34 +3,52 @@ package store
 import (
 	"context"
 	"encoding/json"
+	"errors"
+	"fmt"
 	"time"
 
+	"github.com/gramanas/blogspace/internal/db"
 	"github.com/jackc/pgx/v5"
 )
 
+// Blog is a registry row (control database) plus the settings row of the
+// blog's own database, which Store.Open fills in.
 type Blog struct {
 	ID        int64
 	OwnerID   int64
 	Subdomain string
+	DBName    string
+	CreatedAt time.Time
+	// from the blog database
 	Title     string
 	Tagline   string
 	ThemeJSON json.RawMessage
-	CreatedAt time.Time
 	UpdatedAt time.Time
 }
 
-const blogCols = `id, owner_id, subdomain, title, tagline, theme, created_at, updated_at`
+const blogCols = `id, owner_id, subdomain, db_name, created_at`
 
 func scanBlog(row interface{ Scan(...any) error }) (*Blog, error) {
 	var b Blog
-	err := row.Scan(&b.ID, &b.OwnerID, &b.Subdomain, &b.Title, &b.Tagline, &b.ThemeJSON, &b.CreatedAt, &b.UpdatedAt)
+	err := row.Scan(&b.ID, &b.OwnerID, &b.Subdomain, &b.DBName, &b.CreatedAt)
 	if err != nil {
 		return nil, wrap(err)
 	}
 	return &b, nil
 }
 
-// CreateBlogger creates a user, their blog and a default home page in one transaction.
+func (bs *BlogStore) loadSettings(ctx context.Context, b *Blog) error {
+	err := bs.db.QueryRow(ctx, `SELECT title, tagline, theme, updated_at FROM settings`).Scan(&b.Title, &b.Tagline, &b.ThemeJSON, &b.UpdatedAt)
+	if errors.Is(err, pgx.ErrNoRows) { // registered, but the database is empty: not a 404
+		return fmt.Errorf("database %s of blog %q has no settings row (empty or wrong dump?)", b.DBName, b.Subdomain)
+	}
+	if err != nil {
+		return fmt.Errorf("settings of %s: %w", b.DBName, err)
+	}
+	return nil
+}
+
+// CreateBlogger creates a user, their blog and a default home page.
 func (s *Store) CreateBlogger(ctx context.Context, username, passwordHash, subdomain, title string) (*User, *Blog, error) {
 	tx, err := s.db.Begin(ctx)
 	if err != nil {
@@ -43,13 +61,10 @@ func (s *Store) CreateBlogger(ctx context.Context, username, passwordHash, subdo
 	if err != nil {
 		return nil, nil, err
 	}
-	b, err := createBlog(ctx, tx, u.ID, subdomain, title)
+	b, err := s.createBlog(ctx, tx, u.ID, subdomain, title)
 	if err != nil {
 		return nil, nil, err
 	}
-	if err := tx.Commit(ctx); err != nil {
-		return nil, nil, err
-	}
 	return u, b, nil
 }
 
@@ -60,30 +75,64 @@ func (s *Store) CreateBlog(ctx context.Context, ownerID int64, subdomain, title
 		return nil, err
 	}
 	defer tx.Rollback(ctx)
-	b, err := createBlog(ctx, tx, ownerID, subdomain, title)
+	return s.createBlog(ctx, tx, ownerID, subdomain, title)
+}
+
+// createBlog registers the blog in tx, then creates and fills its database and
+// commits. The registry insert goes first so a taken subdomain fails before
+// any DDL; if anything after that fails the transaction rolls back and the new
+// database is dropped again.
+func (s *Store) createBlog(ctx context.Context, tx pgx.Tx, ownerID int64, subdomain, title string) (*Blog, error) {
+	name := db.DBName(subdomain)
+	b, err := scanBlog(tx.QueryRow(ctx, `INSERT INTO blogs (owner_id, subdomain, db_name) VALUES ($1,$2,$3) RETURNING `+blogCols,
+		ownerID, subdomain, name))
+	if err != nil {
+		return nil, err
+	}
+	if err := s.cluster.CreateBlogDB(ctx, name); err != nil {
+		return nil, err
+	}
+	bs, err := s.fillNewBlog(ctx, b, title)
+	if err == nil {
+		err = tx.Commit(ctx)
+	}
 	if err != nil {
+		s.cluster.DropBlogDB(ctx, name)
 		return nil, err
 	}
-	return b, tx.Commit(ctx)
+	if err := bs.loadSettings(ctx, b); err != nil {
+		return nil, err
+	}
+	return b, nil
 }
 
-func createBlog(ctx context.Context, tx pgx.Tx, ownerID int64, subdomain, title string) (*Blog, error) {
-	b, err := scanBlog(tx.QueryRow(ctx, `INSERT INTO blogs (owner_id, subdomain, title) VALUES ($1,$2,$3) RETURNING `+blogCols,
-		ownerID, subdomain, title))
+// fillNewBlog writes what every new blog starts with: its settings, a home
+// page in the menu and the default layout.
+func (s *Store) fillNewBlog(ctx context.Context, b *Blog, title string) (*BlogStore, error) {
+	pool, err := s.cluster.Blog(ctx, b.DBName)
 	if err != nil {
 		return nil, err
 	}
+	bs := &BlogStore{db: pool}
+	btx, err := pool.Begin(ctx)
+	if err != nil {
+		return nil, err
+	}
+	defer btx.Rollback(ctx)
+	if _, err := btx.Exec(ctx, `INSERT INTO settings (title, created_at, updated_at) VALUES ($1,$2,$2)`, title, b.CreatedAt); err != nil {
+		return nil, err
+	}
 	var homeID int64
-	if err := tx.QueryRow(ctx, `INSERT INTO pages (blog_id, slug, title, nav_order, is_home) VALUES ($1,'home','Home',0,true) RETURNING id`, b.ID).Scan(&homeID); err != nil {
+	if err := btx.QueryRow(ctx, `INSERT INTO pages (slug, title, nav_order, is_home) VALUES ('home','Home',0,true) RETURNING id`).Scan(&homeID); err != nil {
 		return nil, wrap(err)
 	}
-	if err := addMenuPage(ctx, tx, b.ID, homeID); err != nil {
+	if err := addMenuPage(ctx, btx, homeID); err != nil {
 		return nil, err
 	}
-	if err := insertDefaultModules(ctx, tx, b.ID); err != nil {
+	if err := insertDefaultModules(ctx, btx); err != nil {
 		return nil, err
 	}
-	return b, nil
+	return bs, btx.Commit(ctx)
 }
 
 func (s *Store) BlogByID(ctx context.Context, id int64) (*Blog, error) {
@@ -98,17 +147,38 @@ func (s *Store) BlogByOwner(ctx context.Context, ownerID int64) (*Blog, error) {
 	return scanBlog(s.db.QueryRow(ctx, `SELECT `+blogCols+` FROM blogs WHERE owner_id=$1`, ownerID))
 }
 
-func (s *Store) UpdateBlogSettings(ctx context.Context, id int64, title, tagline string) error {
-	_, err := s.db.Exec(ctx, `UPDATE blogs SET title=$2, tagline=$3, updated_at=now() WHERE id=$1`, id, title, tagline)
-	return err
+// ListBlogs returns every registered blog, oldest first.
+func (s *Store) ListBlogs(ctx context.Context) ([]Blog, error) {
+	rows, err := s.db.Query(ctx, `SELECT `+blogCols+` FROM blogs ORDER BY id`)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var out []Blog
+	for rows.Next() {
+		b, err := scanBlog(rows)
+		if err != nil {
+			return nil, err
+		}
+		out = append(out, *b)
+	}
+	return out, rows.Err()
+}
+
+// DeleteBlog unregisters the blog and drops its database.
+func (s *Store) DeleteBlog(ctx context.Context, b *Blog) error {
+	if _, err := s.db.Exec(ctx, `DELETE FROM blogs WHERE id=$1`, b.ID); err != nil {
+		return err
+	}
+	return s.cluster.DropBlogDB(ctx, b.DBName)
 }
 
-func (s *Store) UpdateBlogTheme(ctx context.Context, id int64, theme json.RawMessage) error {
-	_, err := s.db.Exec(ctx, `UPDATE blogs SET theme=$2, updated_at=now() WHERE id=$1`, id, theme)
+func (bs *BlogStore) UpdateSettings(ctx context.Context, title, tagline string) error {
+	_, err := bs.db.Exec(ctx, `UPDATE settings SET title=$1, tagline=$2, updated_at=now()`, title, tagline)
 	return err
 }
 
-func (s *Store) DeleteBlog(ctx context.Context, id int64) error {
-	_, err := s.db.Exec(ctx, `DELETE FROM blogs WHERE id=$1`, id)
+func (bs *BlogStore) UpdateTheme(ctx context.Context, theme json.RawMessage) error {
+	_, err := bs.db.Exec(ctx, `UPDATE settings SET theme=$1, updated_at=now()`, theme)
 	return err
 }