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
46 lines
2.0 KiB
PL/PgSQL
46 lines
2.0 KiB
PL/PgSQL
-- ============================================================
|
|
-- Migration: 003_message_article.sql
|
|
-- Tables: message_article
|
|
-- ============================================================
|
|
-- Extends the message subsystem with forum-style article posts.
|
|
-- Articles extend regular messages with title, cover image, tags,
|
|
-- and view/like stats. Rendered as waterfall cards in forum channels.
|
|
|
|
BEGIN;
|
|
|
|
-- models/message_article.rs → message_article
|
|
CREATE TABLE IF NOT EXISTS message_article (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
message_id UUID NOT NULL REFERENCES message(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
summary TEXT NULL,
|
|
cover_url TEXT NULL,
|
|
cover_width INTEGER NULL,
|
|
cover_height INTEGER NULL,
|
|
cover_color TEXT NULL,
|
|
tags JSONB NULL,
|
|
view_count BIGINT NOT NULL DEFAULT 0,
|
|
like_count BIGINT NOT NULL DEFAULT 0,
|
|
bookmark_count BIGINT NOT NULL DEFAULT 0,
|
|
reply_count BIGINT NOT NULL DEFAULT 0,
|
|
last_reply_message_id UUID NULL REFERENCES message(id) ON DELETE SET NULL,
|
|
last_reply_at TIMESTAMPTZ NULL,
|
|
last_reply_user_id UUID NULL,
|
|
is_pinned_to_top BOOLEAN NOT NULL DEFAULT FALSE,
|
|
is_answered BOOLEAN NOT NULL DEFAULT FALSE,
|
|
answered_by UUID NULL,
|
|
answered_at TIMESTAMPTZ NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT uq_message_article_message UNIQUE (message_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_article_last_reply_at
|
|
ON message_article (last_reply_at DESC NULLS LAST);
|
|
CREATE INDEX IF NOT EXISTS idx_message_article_is_pinned_to_top
|
|
ON message_article (is_pinned_to_top DESC, last_reply_at DESC NULLS LAST);
|
|
CREATE INDEX IF NOT EXISTS idx_message_article_view_count
|
|
ON message_article (view_count DESC);
|
|
|
|
COMMIT;
|