From 3eb04b1a2bdf9e53231fe862cfd76327371a9741 Mon Sep 17 00:00:00 2001 From: gramanas Date: Sat, 12 Sep 2026 11:24:17 +0300 Subject: Initial multi-tenant blog host Go + Postgres application serving a management dashboard on the base domain and one public blog per subdomain. Markdown posts organised in pages, form-based theme customisation, image uploads stored in Postgres, JWT cookie sessions with CSRF, superadmin user management, RSS feeds. Docker/compose deployment and a Makefile-driven dev environment with seed data. Co-Authored-By: Claude Opus 5 Claude-Session: https://claude.ai/code/session_01Sd8UPWrvyYCLj97JexNw3A --- internal/db/migrations/00001_init.sql | 68 +++++++++++++++++++++++++++++++++++ 1 file changed, 68 insertions(+) create mode 100644 internal/db/migrations/00001_init.sql (limited to 'internal/db/migrations/00001_init.sql') diff --git a/internal/db/migrations/00001_init.sql b/internal/db/migrations/00001_init.sql new file mode 100644 index 0000000..e27dad4 --- /dev/null +++ b/internal/db/migrations/00001_init.sql @@ -0,0 +1,68 @@ +-- +goose Up +CREATE TABLE users ( + id bigserial PRIMARY KEY, + username text NOT NULL UNIQUE, + password_hash text NOT NULL, + role text NOT NULL CHECK (role IN ('superadmin', 'blogger')), + disabled boolean NOT NULL DEFAULT false, + token_version integer NOT NULL DEFAULT 0, + created_at timestamptz NOT NULL DEFAULT now() +); + +CREATE TABLE blogs ( + id bigserial PRIMARY KEY, + owner_id bigint NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE, + subdomain text NOT NULL UNIQUE CHECK (subdomain ~ '^[a-z0-9](-?[a-z0-9]){0,62}$'), + 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, + blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, + slug text NOT NULL, + title text NOT NULL, + intro_md text NOT NULL DEFAULT '', + intro_html text NOT NULL DEFAULT '', + nav_order integer NOT NULL DEFAULT 0, + show_in_nav boolean NOT NULL DEFAULT true, + is_home boolean NOT NULL DEFAULT false, + created_at timestamptz NOT NULL DEFAULT now(), + UNIQUE (blog_id, slug) +); +CREATE UNIQUE INDEX pages_one_home_per_blog ON pages (blog_id) 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, + blog_id bigint NOT NULL REFERENCES blogs(id) ON DELETE CASCADE, + 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_blog ON images (blog_id, created_at DESC); + +-- +goose Down +DROP TABLE images; +DROP TABLE posts; +DROP TABLE pages; +DROP TABLE blogs; +DROP TABLE users; -- cgit v1.2.3