1 files changed, 68 insertions, 0 deletions
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;