Blog & CMS

CREATE TYPE post_status AS ENUM ('draft', 'scheduled', 'published', 'archived');

CREATE TABLE authors (
    id serial PRIMARY KEY,
    email text NOT NULL UNIQUE,
    display_name text NOT NULL,
    bio text
);

CREATE TABLE posts (
    id bigserial PRIMARY KEY,
    author_id integer NOT NULL,
    slug text NOT NULL UNIQUE,
    title text NOT NULL,
    body text DEFAULT '' NOT NULL,
    status post_status DEFAULT 'draft' NOT NULL,
    published_at timestamptz,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX posts_status_published_idx ON posts (status, published_at DESC);

CREATE TABLE post_revisions (
    id bigserial PRIMARY KEY,
    post_id bigint NOT NULL,
    body text NOT NULL,
    edited_by integer,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE tags (
    id serial PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id bigint NOT NULL,
    tag_id integer NOT NULL,
    PRIMARY KEY (post_id, tag_id)
);

CREATE TABLE comments (
    id bigserial PRIMARY KEY,
    post_id bigint NOT NULL,
    parent_id bigint,
    author_name text NOT NULL,
    body text NOT NULL,
    approved boolean DEFAULT false NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX comments_post_idx ON comments (post_id);

ALTER TABLE posts ADD FOREIGN KEY (author_id) REFERENCES authors (id);

ALTER TABLE post_revisions ADD FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE;

ALTER TABLE post_revisions ADD FOREIGN KEY (edited_by) REFERENCES authors (id) ON DELETE SET NULL;

ALTER TABLE post_tags ADD FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE;

ALTER TABLE post_tags ADD FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE;

ALTER TABLE comments ADD FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE;

ALTER TABLE comments ADD FOREIGN KEY (parent_id) REFERENCES comments (id) ON DELETE CASCADE;