schema.sql

-- Conduit (RealWorld) schema for the multicore server challenge. Fixed: don't add or change anything (rule 3).
-- seed/make-seed.sh loads it into `conduit_seed` (or `conduit_dev`); every test and benchmark run starts from a
-- fresh `CREATE DATABASE ... TEMPLATE conduit_seed`.
--
-- Indexes: the obvious ones a competent developer adds on day one for the queries in SPEC.md.
-- No denormalized counters, no materialized views.

CREATE TABLE users (
    id            bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    username      text NOT NULL UNIQUE,
    email         text NOT NULL UNIQUE,
    password_hash text NOT NULL,
    bio           text,
    image         text,
    created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE follows (
    follower_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    followee_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    created_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (follower_id, followee_id)
);
CREATE INDEX follows_followee_idx ON follows (followee_id);

CREATE TABLE articles (
    id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    slug        text NOT NULL UNIQUE,
    author_id   bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    title       text NOT NULL,
    description text NOT NULL,
    body        text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);
-- Lists page newest-first by id (ids are assigned in insertion order), so the primary key serves the global list.
CREATE INDEX articles_author_idx ON articles (author_id, id DESC);

CREATE TABLE tags (
    id   int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE article_tags (
    article_id bigint NOT NULL REFERENCES articles (id) ON DELETE CASCADE,
    tag_id     int    NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, tag_id)
);
CREATE INDEX article_tags_tag_idx ON article_tags (tag_id, article_id);

CREATE TABLE favorites (
    user_id    bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    article_id bigint NOT NULL REFERENCES articles (id) ON DELETE CASCADE,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, article_id)
);
CREATE INDEX favorites_article_idx ON favorites (article_id);

CREATE TABLE comments (
    id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    article_id bigint NOT NULL REFERENCES articles (id) ON DELETE CASCADE,
    author_id  bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    body       text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX comments_article_idx ON comments (article_id, created_at DESC, id DESC);
CREATE INDEX comments_author_idx ON comments (author_id);