blog_schema.sql

CREATE TABLE IF NOT EXISTS blog_posts (
    id              SERIAL PRIMARY KEY,
    title           TEXT NOT NULL,
    slug            TEXT NOT NULL UNIQUE,
    content_md      TEXT NOT NULL,
    content_html    TEXT NOT NULL,
    excerpt         TEXT DEFAULT '',
    is_published    BOOLEAN NOT NULL DEFAULT FALSE,
    published_at    TIMESTAMP,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_blog_posts_published
ON blog_posts (is_published, published_at DESC);

CREATE INDEX IF NOT EXISTS idx_blog_posts_slug
ON blog_posts (slug);

CREATE TABLE IF NOT EXISTS blog_likes (
    id          SERIAL PRIMARY KEY,
    post_id     INTEGER NOT NULL REFERENCES blog_posts(id) ON DELETE CASCADE,
    anon_hash   TEXT NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE (post_id, anon_hash)
);

CREATE INDEX IF NOT EXISTS idx_blog_likes_post
ON blog_likes (post_id);

CREATE TABLE IF NOT EXISTS blog_comments (
    id          SERIAL PRIMARY KEY,
    post_id     INTEGER NOT NULL REFERENCES blog_posts(id) ON DELETE CASCADE,
    anon_hash   TEXT NOT NULL,
    name        TEXT DEFAULT 'Anonymous',
    content     TEXT NOT NULL,
    is_hidden   BOOLEAN NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_blog_comments_post
ON blog_comments (post_id, created_at DESC);

-- =========================
-- Full-text search (optional but recommended)
-- =========================

ALTER TABLE blog_posts
ADD COLUMN IF NOT EXISTS search_vector tsvector;

CREATE INDEX IF NOT EXISTS idx_blog_posts_search
ON blog_posts USING GIN (search_vector);

CREATE OR REPLACE FUNCTION blog_posts_search_trigger() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        to_tsvector('english',
            COALESCE(NEW.title,'') || ' ' ||
            COALESCE(NEW.excerpt,'') || ' ' ||
            COALESCE(NEW.content_md,'')
        );
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tsvectorupdate ON blog_posts;

CREATE TRIGGER tsvectorupdate
BEFORE INSERT OR UPDATE ON blog_posts
FOR EACH ROW EXECUTE FUNCTION blog_posts_search_trigger();

-- =========================
-- updated_at auto-update
-- =========================

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS blog_posts_updated_at ON blog_posts;

CREATE TRIGGER blog_posts_updated_at
BEFORE UPDATE ON blog_posts
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();