2 files changed, 111 insertions, 0 deletions
diff --git a/internal/db/db.go b/internal/db/db.go
new file mode 100644
index 0000000..44d489b
--- /dev/null
+++ b/internal/db/db.go
@@ -0,0 +1,43 @@
+// Package db opens the Postgres pool and applies embedded migrations.
+package db
+
+import (
+	"context"
+	"database/sql"
+	"embed"
+	"fmt"
+
+	"github.com/jackc/pgx/v5/pgxpool"
+	_ "github.com/jackc/pgx/v5/stdlib"
+	"github.com/pressly/goose/v3"
+)
+
+//go:embed migrations/*.sql
+var migrations embed.FS
+
+func Open(ctx context.Context, url string) (*pgxpool.Pool, error) {
+	pool, err := pgxpool.New(ctx, url)
+	if err != nil {
+		return nil, fmt.Errorf("connect: %w", err)
+	}
+	if err := pool.Ping(ctx); err != nil {
+		pool.Close()
+		return nil, fmt.Errorf("ping: %w", err)
+	}
+	return pool, nil
+}
+
+// Migrate applies all pending migrations using goose over database/sql.
+func Migrate(ctx context.Context, url string) error {
+	sqldb, err := sql.Open("pgx", url)
+	if err != nil {
+		return err
+	}
+	defer sqldb.Close()
+	goose.SetBaseFS(migrations)
+	goose.SetLogger(goose.NopLogger())
+	if err := goose.SetDialect("postgres"); err != nil {
+		return err
+	}
+	return goose.UpContext(ctx, sqldb, "migrations")
+}
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;