From aeb19df4222269c585de55be5568326222df879d Mon Sep 17 00:00:00 2001 From: grm Date: Sun, 13 Sep 2026 23:52:41 +0300 Subject: Give every blog its own Postgres database A blog is now a database of its own (blog_) 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 Claude-Session: https://claude.ai/code/session_01Sd8UPWrvyYCLj97JexNw3A --- internal/db/migrations/blog/00001_init.sql | 91 ++++++++++++++++++++++++++++++ 1 file changed, 91 insertions(+) create mode 100644 internal/db/migrations/blog/00001_init.sql (limited to 'internal/db/migrations/blog/00001_init.sql') diff --git a/internal/db/migrations/blog/00001_init.sql b/internal/db/migrations/blog/00001_init.sql new file mode 100644 index 0000000..4649935 --- /dev/null +++ b/internal/db/migrations/blog/00001_init.sql @@ -0,0 +1,91 @@ +-- +goose Up +-- One database per blog: nothing here carries a blog id, the database is the scope. +CREATE TABLE settings ( + id boolean PRIMARY KEY DEFAULT true CHECK (id), -- exactly one row + title text NOT NULL, + tagline text NOT NULL DEFAULT '', + theme jsonb NOT NULL DEFAULT '{}'::jsonb, + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz NOT NULL DEFAULT now() +); + +CREATE TABLE pages ( + id bigserial PRIMARY KEY, + slug text NOT NULL UNIQUE, + title text NOT NULL, + intro_md text NOT NULL DEFAULT '', + intro_html text NOT NULL DEFAULT '', + nav_order integer NOT NULL DEFAULT 0, + is_home boolean NOT NULL DEFAULT false, + created_at timestamptz NOT NULL DEFAULT now() +); +CREATE UNIQUE INDEX pages_one_home ON pages ((true)) WHERE is_home; + +CREATE TABLE posts ( + id bigserial PRIMARY KEY, + page_id bigint NOT NULL REFERENCES pages(id) ON DELETE CASCADE, + slug text NOT NULL, + title text NOT NULL, + body_md text NOT NULL DEFAULT '', + body_html text NOT NULL DEFAULT '', + published boolean NOT NULL DEFAULT true, + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz NOT NULL DEFAULT now(), + UNIQUE (page_id, slug) +); +CREATE INDEX posts_page_created ON posts (page_id, created_at DESC); + +CREATE TABLE images ( + id uuid PRIMARY KEY, + filename text NOT NULL, + content_type text NOT NULL, + size integer NOT NULL, + data bytea NOT NULL, + created_at timestamptz NOT NULL DEFAULT now() +); +CREATE INDEX images_created ON images (created_at DESC); + +-- Announcements: blog-wide notices shown on every page and post. +CREATE TABLE sections ( + id bigserial PRIMARY KEY, + title text NOT NULL DEFAULT '', + body_md text NOT NULL DEFAULT '', + body_html text NOT NULL DEFAULT '', + placement text NOT NULL DEFAULT 'main-top' + CHECK (placement IN ('left-top', 'left-bottom', 'main-top', 'main-bottom', 'right-top', 'right-bottom')), + style text NOT NULL DEFAULT 'note' CHECK (style IN ('plain', 'note', 'warning')), + enabled boolean NOT NULL DEFAULT true, + sort_order integer NOT NULL DEFAULT 0, + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz NOT NULL DEFAULT now() +); +CREATE INDEX sections_order ON sections (sort_order, id); + +-- Layout modules: what each area of a blog (header, columns, footer) shows. +CREATE TABLE modules ( + id bigserial PRIMARY KEY, + area text NOT NULL CHECK (area IN ('header', 'left', 'right', 'above', 'below', 'footer')), + kind text NOT NULL CHECK (kind IN ('title', 'logo', 'menu', 'archive', 'recent', 'html', 'rss', 'text', 'sitemap')), + title text NOT NULL DEFAULT '', + body text NOT NULL DEFAULT '', + count integer NOT NULL DEFAULT 5, + sort_order integer NOT NULL DEFAULT 0, + created_at timestamptz NOT NULL DEFAULT now(), + updated_at timestamptz NOT NULL DEFAULT now() +); +CREATE INDEX modules_area ON modules (area, sort_order, id); + +-- The menu: blog pages and custom links in one ordered list. +CREATE TABLE menu_items ( + id bigserial PRIMARY KEY, + page_id bigint REFERENCES pages(id) ON DELETE CASCADE, + label text NOT NULL DEFAULT '', + url text NOT NULL DEFAULT '', + sort_order integer NOT NULL DEFAULT 0, + CHECK (page_id IS NOT NULL OR url <> '') +); +CREATE UNIQUE INDEX menu_items_page ON menu_items (page_id) WHERE page_id IS NOT NULL; +CREATE INDEX menu_items_order ON menu_items (sort_order, id); + +-- +goose Down +DROP TABLE menu_items, modules, sections, images, posts, pages, settings; -- cgit v1.2.3