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, 12 insertions, 13 deletions

diff --git a/internal/store/images.go b/internal/store/images.go
index 3699e75..b222468 100644
--- a/internal/store/images.go
+++ b/internal/store/images.go
@@ -9,7 +9,6 @@ import (
 
 type Image struct {
 	ID          uuid.UUID
-	BlogID      int64
 	Filename    string
 	ContentType string
 	Size        int
@@ -17,15 +16,15 @@ type Image struct {
 	CreatedAt   time.Time
 }
 
-func (s *Store) CreateImage(ctx context.Context, blogID int64, filename, contentType string, data []byte) (*Image, error) {
-	img := &Image{ID: uuid.New(), BlogID: blogID, Filename: filename, ContentType: contentType, Size: len(data)}
-	err := s.db.QueryRow(ctx, `INSERT INTO images (id, blog_id, filename, content_type, size, data) VALUES ($1,$2,$3,$4,$5,$6) RETURNING created_at`,
-		img.ID, blogID, filename, contentType, len(data), data).Scan(&img.CreatedAt)
+func (bs *BlogStore) CreateImage(ctx context.Context, filename, contentType string, data []byte) (*Image, error) {
+	img := &Image{ID: uuid.New(), Filename: filename, ContentType: contentType, Size: len(data)}
+	err := bs.db.QueryRow(ctx, `INSERT INTO images (id, filename, content_type, size, data) VALUES ($1,$2,$3,$4,$5) RETURNING created_at`,
+		img.ID, filename, contentType, len(data), data).Scan(&img.CreatedAt)
 	return img, err
 }
 
-func (s *Store) ListImages(ctx context.Context, blogID int64) ([]Image, error) {
-	rows, err := s.db.Query(ctx, `SELECT id, blog_id, filename, content_type, size, created_at FROM images WHERE blog_id=$1 ORDER BY created_at DESC`, blogID)
+func (bs *BlogStore) ListImages(ctx context.Context) ([]Image, error) {
+	rows, err := bs.db.Query(ctx, `SELECT id, filename, content_type, size, created_at FROM images ORDER BY created_at DESC`)
 	if err != nil {
 		return nil, err
 	}
@@ -33,7 +32,7 @@ func (s *Store) ListImages(ctx context.Context, blogID int64) ([]Image, error) {
 	var out []Image
 	for rows.Next() {
 		var i Image
-		if err := rows.Scan(&i.ID, &i.BlogID, &i.Filename, &i.ContentType, &i.Size, &i.CreatedAt); err != nil {
+		if err := rows.Scan(&i.ID, &i.Filename, &i.ContentType, &i.Size, &i.CreatedAt); err != nil {
 			return nil, err
 		}
 		out = append(out, i)
@@ -42,17 +41,17 @@ func (s *Store) ListImages(ctx context.Context, blogID int64) ([]Image, error) {
 }
 
 // ImageData loads an image including its bytes.
-func (s *Store) ImageData(ctx context.Context, id uuid.UUID) (*Image, error) {
+func (bs *BlogStore) ImageData(ctx context.Context, id uuid.UUID) (*Image, error) {
 	var i Image
-	err := s.db.QueryRow(ctx, `SELECT id, blog_id, filename, content_type, size, data, created_at FROM images WHERE id=$1`, id).
-		Scan(&i.ID, &i.BlogID, &i.Filename, &i.ContentType, &i.Size, &i.Data, &i.CreatedAt)
+	err := bs.db.QueryRow(ctx, `SELECT id, filename, content_type, size, data, created_at FROM images WHERE id=$1`, id).
+		Scan(&i.ID, &i.Filename, &i.ContentType, &i.Size, &i.Data, &i.CreatedAt)
 	if err != nil {
 		return nil, wrap(err)
 	}
 	return &i, nil
 }
 
-func (s *Store) DeleteImage(ctx context.Context, blogID int64, id uuid.UUID) error {
-	_, err := s.db.Exec(ctx, `DELETE FROM images WHERE blog_id=$1 AND id=$2`, blogID, id)
+func (bs *BlogStore) DeleteImage(ctx context.Context, id uuid.UUID) error {
+	_, err := bs.db.Exec(ctx, `DELETE FROM images WHERE id=$1`, id)
 	return err
 }