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, 28 insertions, 30 deletions

diff --git a/internal/store/posts.go b/internal/store/posts.go
index 9f3c4f9..94bc856 100644
--- a/internal/store/posts.go
+++ b/internal/store/posts.go
@@ -31,8 +31,8 @@ func scanPost(row interface{ Scan(...any) error }) (*Post, error) {
 	return &p, nil
 }
 
-func (s *Store) collectPosts(ctx context.Context, q string, args ...any) ([]Post, error) {
-	rows, err := s.db.Query(ctx, q, args...)
+func (bs *BlogStore) collectPosts(ctx context.Context, q string, args ...any) ([]Post, error) {
+	rows, err := bs.db.Query(ctx, q, args...)
 	if err != nil {
 		return nil, err
 	}
@@ -48,21 +48,21 @@ func (s *Store) collectPosts(ctx context.Context, q string, args ...any) ([]Post
 	return out, rows.Err()
 }
 
-// ListPosts returns all posts of a blog for the dashboard, optionally filtered by page.
-func (s *Store) ListPosts(ctx context.Context, blogID int64, pageID int64) ([]Post, error) {
-	return s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
-		WHERE g.blog_id=$1 AND ($2=0 OR p.page_id=$2) ORDER BY p.created_at DESC, p.id DESC`, blogID, pageID)
+// ListPosts returns all posts of the blog for the dashboard, optionally filtered by page.
+func (bs *BlogStore) ListPosts(ctx context.Context, pageID int64) ([]Post, error) {
+	return bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
+		WHERE ($1=0 OR p.page_id=$1) ORDER BY p.created_at DESC, p.id DESC`, pageID)
 }
 
 // PublishedPosts returns a page of published posts for the public site.
-func (s *Store) PublishedPosts(ctx context.Context, pageID int64, limit, offset int) ([]Post, int, error) {
-	posts, err := s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
+func (bs *BlogStore) PublishedPosts(ctx context.Context, pageID int64, limit, offset int) ([]Post, int, error) {
+	posts, err := bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
 		WHERE p.page_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $2 OFFSET $3`, pageID, limit, offset)
 	if err != nil {
 		return nil, 0, err
 	}
 	var total int
-	err = s.db.QueryRow(ctx, `SELECT count(*) FROM posts WHERE page_id=$1 AND published`, pageID).Scan(&total)
+	err = bs.db.QueryRow(ctx, `SELECT count(*) FROM posts WHERE page_id=$1 AND published`, pageID).Scan(&total)
 	return posts, total, err
 }
 
@@ -74,10 +74,10 @@ type PostRef struct {
 	CreatedAt time.Time
 }
 
-// PublishedPostIndex lists every published post of a blog, newest first, without bodies.
-func (s *Store) PublishedPostIndex(ctx context.Context, blogID int64) ([]PostRef, error) {
-	rows, err := s.db.Query(ctx, `SELECT p.title, p.slug, g.slug, p.created_at FROM posts p JOIN pages g ON g.id=p.page_id
-		WHERE g.blog_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC`, blogID)
+// PublishedPostIndex lists every published post of the blog, newest first, without bodies.
+func (bs *BlogStore) PublishedPostIndex(ctx context.Context) ([]PostRef, error) {
+	rows, err := bs.db.Query(ctx, `SELECT p.title, p.slug, g.slug, p.created_at FROM posts p JOIN pages g ON g.id=p.page_id
+		WHERE p.published ORDER BY p.created_at DESC, p.id DESC`)
 	if err != nil {
 		return nil, err
 	}
@@ -93,40 +93,38 @@ func (s *Store) PublishedPostIndex(ctx context.Context, blogID int64) ([]PostRef
 	return out, rows.Err()
 }
 
-// RecentPublishedPosts returns the newest published posts across a whole blog (for feeds).
-func (s *Store) RecentPublishedPosts(ctx context.Context, blogID int64, limit int) ([]Post, error) {
-	return s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
-		WHERE g.blog_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $2`, blogID, limit)
+// RecentPublishedPosts returns the newest published posts across the whole blog (for feeds).
+func (bs *BlogStore) RecentPublishedPosts(ctx context.Context, limit int) ([]Post, error) {
+	return bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
+		WHERE p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $1`, limit)
 }
 
-func (s *Store) PostByID(ctx context.Context, blogID, id int64) (*Post, error) {
-	return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
-		WHERE g.blog_id=$1 AND p.id=$2`, blogID, id))
+func (bs *BlogStore) PostByID(ctx context.Context, id int64) (*Post, error) {
+	return scanPost(bs.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.id=$1`, id))
 }
 
-func (s *Store) PublishedPostBySlug(ctx context.Context, pageID int64, slug string) (*Post, error) {
-	return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
+func (bs *BlogStore) PublishedPostBySlug(ctx context.Context, pageID int64, slug string) (*Post, error) {
+	return scanPost(bs.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id
 		WHERE p.page_id=$1 AND p.slug=$2 AND p.published`, pageID, slug))
 }
 
-func (s *Store) CreatePost(ctx context.Context, p *Post) (*Post, error) {
+func (bs *BlogStore) CreatePost(ctx context.Context, p *Post) (*Post, error) {
 	var id int64
-	err := s.db.QueryRow(ctx, `INSERT INTO posts (page_id, slug, title, body_md, body_html, published) VALUES ($1,$2,$3,$4,$5,$6) RETURNING id`,
+	err := bs.db.QueryRow(ctx, `INSERT INTO posts (page_id, slug, title, body_md, body_html, published) VALUES ($1,$2,$3,$4,$5,$6) RETURNING id`,
 		p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, p.Published).Scan(&id)
 	if err != nil {
 		return nil, wrap(err)
 	}
-	return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.id=$1`, id))
+	return bs.PostByID(ctx, id)
 }
 
-// UpdatePost updates a post; the page must belong to the same blog (checked by handler).
-func (s *Store) UpdatePost(ctx context.Context, p *Post) error {
-	_, err := s.db.Exec(ctx, `UPDATE posts SET page_id=$2, slug=$3, title=$4, body_md=$5, body_html=$6, published=$7, updated_at=now() WHERE id=$1`,
+func (bs *BlogStore) UpdatePost(ctx context.Context, p *Post) error {
+	_, err := bs.db.Exec(ctx, `UPDATE posts SET page_id=$2, slug=$3, title=$4, body_md=$5, body_html=$6, published=$7, updated_at=now() WHERE id=$1`,
 		p.ID, p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, p.Published)
 	return wrap(err)
 }
 
-func (s *Store) DeletePost(ctx context.Context, blogID, id int64) error {
-	_, err := s.db.Exec(ctx, `DELETE FROM posts p USING pages g WHERE g.id=p.page_id AND g.blog_id=$1 AND p.id=$2`, blogID, id)
+func (bs *BlogStore) DeletePost(ctx context.Context, id int64) error {
+	_, err := bs.db.Exec(ctx, `DELETE FROM posts WHERE id=$1`, id)
 	return err
 }