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
47 lines
1.6 KiB
PL/PgSQL
47 lines
1.6 KiB
PL/PgSQL
-- ============================================================
|
|
-- Migration: 000_message_base.sql
|
|
-- Tables: message
|
|
-- ============================================================
|
|
-- Core message table managed by imks. All companion tables
|
|
-- (thread, attachment, embed, reaction, ...) reference message(id).
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS message (
|
|
id UUID PRIMARY KEY,
|
|
channel_id UUID NOT NULL,
|
|
author_id UUID NOT NULL,
|
|
thread_id UUID NULL,
|
|
reply_to_message_id UUID NULL REFERENCES message(id) ON DELETE SET NULL,
|
|
message_type TEXT NOT NULL DEFAULT 'text',
|
|
body TEXT NOT NULL,
|
|
metadata JSONB NULL,
|
|
pinned BOOLEAN NOT NULL DEFAULT FALSE,
|
|
system BOOLEAN NOT NULL DEFAULT FALSE,
|
|
edited_at TIMESTAMPTZ NULL,
|
|
deleted_at TIMESTAMPTZ NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Cursor pagination: WHERE id < $cursor ORDER BY id DESC
|
|
CREATE INDEX IF NOT EXISTS idx_message_channel_created
|
|
ON message (channel_id, created_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_author
|
|
ON message (author_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_thread
|
|
ON message (thread_id)
|
|
WHERE thread_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_reply_to
|
|
ON message (reply_to_message_id)
|
|
WHERE reply_to_message_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_deleted
|
|
ON message (deleted_at)
|
|
WHERE deleted_at IS NOT NULL;
|
|
|
|
COMMIT;
|