chatapp/backend/migrations/20260405234237_v0.4.0.sql

-- Add migration script here
-- Add migration script here
CREATE TYPE user_role AS ENUM ('user', 'admin');
CREATE TYPE totp_status AS ENUM ('disabled', 'pending', 'enabled');

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    role user_role NOT NULL DEFAULT 'user',

    -- profile
    nickname VARCHAR(255),

    -- basic auth
    username VARCHAR(255) UNIQUE NOT NULL,
    passhash VARCHAR(255) NOT NULL,

    -- email
    email VARCHAR(255),
    email_verified BOOLEAN DEFAULT FALSE,

    -- 2fa
    totp_secret VARCHAR(255),
    totp_status totp_status NOT NULL DEFAULT 'disabled',

    -- update tracking
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

CREATE TABLE access_tokens (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    creator_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    code VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,

    uses INTEGER NOT NULL DEFAULT 0,
    max_uses INTEGER NOT NULL DEFAULT 1,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '24 hours',
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE refresh_tokens (
    id          BIGSERIAL PRIMARY KEY NOT NULL,
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  VARCHAR(255) NOT NULL,

    revoked     BOOLEAN NOT NULL DEFAULT FALSE,

    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at  TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '7 days'
);


CREATE TABLE spaces (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,

    owner_id BIGINT NOT NULL REFERENCES users(id),

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE channels (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,

    space_id BIGINT NOT NULL REFERENCES spaces(id) ON DELETE CASCADE,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);


CREATE TABLE space_members (
    space_id BIGINT NOT NULL REFERENCES spaces(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    role user_role DEFAULT 'user',

    PRIMARY KEY (space_id, user_id)
);

CREATE TABLE messages (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    channel_id BIGINT NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    is_edited BOOLEAN DEFAULT FALSE
);

CREATE TABLE attachments (
    id           BIGSERIAL    PRIMARY KEY NOT NULL,
    message_id   BIGINT       NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    filename     VARCHAR(255) NOT NULL,
    content_type VARCHAR(100) NOT NULL,  -- mime type e.g. image/png, video/mp4
    size_bytes   BIGINT       NOT NULL,
    url          TEXT         NOT NULL,   -- path to file on your CDN/storage
    width        INTEGER,                 -- null for non-image/video
    height       INTEGER,                 -- null for non-image/video
    duration_ms  INTEGER,                 -- null for non-audio/video
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TYPE relationship_status AS ENUM ('pending', 'accepted', 'blocked');

CREATE TABLE relationships (
    id          BIGSERIAL PRIMARY KEY NOT NULL,

    from_user   BIGINT NOT NULL REFERENCES users(id),
    to_user     BIGINT NOT NULL REFERENCES users(id),
    status      relationship_status NOT NULL DEFAULT 'pending',

    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT no_self_relationship CHECK (from_user != to_user),
    CONSTRAINT unique_relationship  UNIQUE (from_user, to_user)
);

CREATE INDEX idx_messages_channel_id   ON messages(channel_id, created_at DESC);
CREATE INDEX idx_messages_user_id      ON messages(user_id);
CREATE INDEX idx_attachments_message   ON attachments(message_id);
CREATE INDEX idx_channels_space_id     ON channels(space_id);
CREATE INDEX idx_space_members_user    ON space_members(user_id);
CREATE INDEX idx_refresh_tokens_hash   ON refresh_tokens(token_hash);
CREATE INDEX idx_relationships_from    ON relationships(from_user, to_user);
CREATE INDEX idx_relationships_to      ON relationships(to_user);
CREATE INDEX idx_access_tokens_code    ON access_tokens(code);

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

CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER spaces_updated_at
    BEFORE UPDATE ON spaces
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER channels_updated_at
    BEFORE UPDATE ON channels
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER messages_updated_at
    BEFORE UPDATE ON messages
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER relationships_updated_at
    BEFORE UPDATE ON relationships
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER access_tokens_updated_at
    BEFORE UPDATE ON access_tokens
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE OR REPLACE FUNCTION add_owner_to_space()
    RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO space_members (space_id, user_id, role, joined_at)
    VALUES (NEW.id, NEW.owner_id, 'admin', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER space_owner_becomes_member
    AFTER INSERT ON spaces
    FOR EACH ROW EXECUTE FUNCTION add_owner_to_space();