catask/schema.sql

CREATE TABLE IF NOT EXISTS answers (
    id SERIAL PRIMARY KEY,
    question_id INTEGER NOT NULL,
    creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    content TEXT NOT NULL,
    cw VARCHAR(255) NOT NULL DEFAULT ''
);

CREATE TABLE IF NOT EXISTS questions (
    id SERIAL PRIMARY KEY,
    from_who VARCHAR(255) NOT NULL,
    creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    content TEXT NOT NULL,
    answered BOOLEAN NOT NULL DEFAULT FALSE,
    answer_id INTEGER,
    pinned BOOLEAN NOT NULL DEFAULT FALSE,
    cw VARCHAR(255) NOT NULL DEFAULT '',
    unread BOOLEAN NOT NULL DEFAULT TRUE
    -- private BOOLEAN NOT NULL DEFAULT FALSE, -- For later use
    -- user_ip BYTEA NOT NULL DEFAULT ''       -- For later use
);

ALTER TABLE questions
ADD CONSTRAINT fk_answer_id FOREIGN KEY (answer_id) REFERENCES answers(id) ON DELETE CASCADE;

ALTER TABLE answers
ADD CONSTRAINT fk_question_id FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE;