Social network

CREATE TABLE profiles (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    handle text NOT NULL UNIQUE,
    display_name text NOT NULL,
    bio text,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE follows (
    follower_id uuid NOT NULL,
    followee_id uuid NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL,
    PRIMARY KEY (follower_id, followee_id),
    CHECK (follower_id <> followee_id)
);

CREATE TABLE posts (
    id bigserial PRIMARY KEY,
    author_id uuid NOT NULL,
    reply_to_id bigint,
    body text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL,
    CHECK (char_length(body) <= 500)
);
CREATE INDEX posts_author_idx ON posts (author_id, created_at DESC);

CREATE TABLE likes (
    post_id bigint NOT NULL,
    profile_id uuid NOT NULL,
    PRIMARY KEY (post_id, profile_id)
);

CREATE TABLE conversations (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE conversation_members (
    conversation_id uuid NOT NULL,
    profile_id uuid NOT NULL,
    PRIMARY KEY (conversation_id, profile_id)
);

CREATE TABLE messages (
    id bigserial PRIMARY KEY,
    conversation_id uuid NOT NULL,
    sender_id uuid NOT NULL,
    body text NOT NULL,
    sent_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX messages_conversation_idx ON messages (conversation_id, sent_at);

CREATE TABLE notifications (
    id bigserial PRIMARY KEY,
    profile_id uuid NOT NULL,
    kind text NOT NULL,
    payload jsonb DEFAULT '{}'::jsonb NOT NULL,
    read_at timestamptz
);
CREATE INDEX notifications_unread_idx ON notifications (profile_id) WHERE read_at is null;

ALTER TABLE follows ADD FOREIGN KEY (follower_id) REFERENCES profiles (id) ON DELETE CASCADE;

ALTER TABLE follows ADD FOREIGN KEY (followee_id) REFERENCES profiles (id) ON DELETE CASCADE;

ALTER TABLE posts ADD FOREIGN KEY (author_id) REFERENCES profiles (id) ON DELETE CASCADE;

ALTER TABLE posts ADD FOREIGN KEY (reply_to_id) REFERENCES posts (id) ON DELETE SET NULL;

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

ALTER TABLE likes ADD FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE;

ALTER TABLE conversation_members ADD FOREIGN KEY (conversation_id) REFERENCES conversations (id) ON DELETE CASCADE;

ALTER TABLE conversation_members ADD FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE;

ALTER TABLE messages ADD FOREIGN KEY (conversation_id) REFERENCES conversations (id) ON DELETE CASCADE;

ALTER TABLE messages ADD FOREIGN KEY (sender_id) REFERENCES profiles (id) ON DELETE CASCADE;

ALTER TABLE notifications ADD FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE;