42 lines
1.9 KiB
SQL
42 lines
1.9 KiB
SQL
-- migrations/0009_discussion.sql
|
|
-- Phase 12: Native Tablo Chat
|
|
|
|
-- +goose Up
|
|
|
|
CREATE TABLE discussion_messages (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tablo_id uuid NOT NULL REFERENCES tablos(id) ON DELETE CASCADE,
|
|
author_user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
body text NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
edited_at timestamptz,
|
|
edited_by_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
|
|
deleted_at timestamptz,
|
|
deleted_by_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
|
|
CONSTRAINT discussion_messages_body_not_blank CHECK (length(trim(body)) > 0),
|
|
CONSTRAINT discussion_messages_body_max_length CHECK (char_length(body) <= 10000)
|
|
);
|
|
|
|
CREATE INDEX discussion_messages_tablo_created_idx ON discussion_messages(tablo_id, created_at, id);
|
|
CREATE INDEX discussion_messages_author_idx ON discussion_messages(author_user_id);
|
|
|
|
CREATE TABLE discussion_read_states (
|
|
tablo_id uuid NOT NULL REFERENCES tablos(id) ON DELETE CASCADE,
|
|
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
last_read_message_id uuid REFERENCES discussion_messages(id) ON DELETE SET NULL,
|
|
last_read_at timestamptz NOT NULL DEFAULT now(),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (tablo_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX discussion_read_states_user_idx ON discussion_read_states(user_id, tablo_id);
|
|
|
|
-- +goose Down
|
|
|
|
DROP INDEX IF EXISTS discussion_read_states_user_idx;
|
|
DROP TABLE IF EXISTS discussion_read_states;
|
|
DROP INDEX IF EXISTS discussion_messages_author_idx;
|
|
DROP INDEX IF EXISTS discussion_messages_tablo_created_idx;
|
|
DROP TABLE IF EXISTS discussion_messages;
|