zhenyi
716f952bb6
- Add database migrations for message base table with indexes for efficient querying - Implement rich content support with attachment, embed, and poll tables - Create social features including reactions, bookmarks, mentions, and read states - Add thread management with participant tracking and resolution capabilities - Include article posts with title, cover image, tags, and engagement metrics - Support scheduled messages, stickers, forwards, and interactive components - Fix UUID defaults and ensure proper uniqueness constraints for drafts - Add gRPC health server for imks and health check client for appks connectivity - Replace non-connectable 0.0.0.0 addresses with localhost in service discovery - Normalize addresses during RPC configuration to handle bind address issues
77 lines
3.0 KiB
PL/PgSQL
77 lines
3.0 KiB
PL/PgSQL
-- ============================================================
|
|
-- Migration: 002_message_social.sql
|
|
-- Tables: message_pin, message_read_state, message_draft, message_edit
|
|
-- ============================================================
|
|
-- Extends the message subsystem with pinned messages, read receipts,
|
|
-- drafts, and edit history.
|
|
|
|
BEGIN;
|
|
|
|
-- models/message_pin.rs → message_pin
|
|
CREATE TABLE IF NOT EXISTS message_pin (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
channel_id UUID NOT NULL,
|
|
message_id UUID NOT NULL REFERENCES message(id) ON DELETE CASCADE,
|
|
pinned_by UUID NOT NULL,
|
|
position INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT uq_message_pin_channel_message UNIQUE (channel_id, message_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_pin_channel_id
|
|
ON message_pin (channel_id);
|
|
|
|
-- models/message_read_state.rs → message_read_state
|
|
CREATE TABLE IF NOT EXISTS message_read_state (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
channel_id UUID NOT NULL,
|
|
user_id UUID NOT NULL,
|
|
last_read_message_id UUID NULL REFERENCES message(id) ON DELETE SET NULL,
|
|
last_read_at TIMESTAMPTZ NULL,
|
|
unread_count BIGINT NOT NULL DEFAULT 0,
|
|
unread_mentions BIGINT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT uq_message_read_state_channel_user UNIQUE (channel_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_read_state_user_id
|
|
ON message_read_state (user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_message_read_state_channel_id
|
|
ON message_read_state (channel_id);
|
|
|
|
-- models/message_draft.rs → message_draft
|
|
CREATE TABLE IF NOT EXISTS message_draft (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
channel_id UUID NOT NULL,
|
|
user_id UUID NOT NULL,
|
|
thread_id UUID NULL REFERENCES message_thread(id) ON DELETE CASCADE,
|
|
reply_to_message_id UUID NULL REFERENCES message(id) ON DELETE SET NULL,
|
|
body TEXT NOT NULL DEFAULT '',
|
|
metadata JSONB NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT uq_message_draft_channel_user_thread
|
|
UNIQUE (channel_id, user_id, thread_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_draft_user_id
|
|
ON message_draft (user_id);
|
|
|
|
-- models/message_edit.rs → message_edit
|
|
CREATE TABLE IF NOT EXISTS message_edit (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
message_id UUID NOT NULL REFERENCES message(id) ON DELETE CASCADE,
|
|
edited_by UUID NOT NULL,
|
|
old_body TEXT NOT NULL,
|
|
new_body TEXT NOT NULL,
|
|
edited_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_edit_message_id
|
|
ON message_edit (message_id);
|
|
CREATE INDEX IF NOT EXISTS idx_message_edit_edited_at
|
|
ON message_edit (edited_at);
|
|
|
|
COMMIT;
|