建表设计稿
下载原文件-- P27 消息中心建表设计稿(PostgreSQL 口径)
-- 仅作设计参考,不直接执行;统一 is_deleted / created_at / updated_at 与本地触发器。
CREATE TABLE msg_template (
id bigserial PRIMARY KEY,
code text NOT NULL UNIQUE,
msg_type text NOT NULL CHECK (msg_type IN ('business','alert','operation')),
title_tpl text NOT NULL,
body_tpl text NOT NULL,
link_tpl text,
variables jsonb NOT NULL DEFAULT '[]'::jsonb,
channels jsonb NOT NULL DEFAULT '["inbox"]'::jsonb,
version integer NOT NULL DEFAULT 1,
enabled boolean NOT NULL DEFAULT true,
is_deleted boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE msg_template IS '消息模板(含变量与渠道配置)';
CREATE TABLE msg_notification (
id bigserial PRIMARY KEY,
receiver_id text NOT NULL,
receiver_type text NOT NULL CHECK (receiver_type IN ('customer','supplier','platform')),
msg_type text NOT NULL,
template_code text,
title text NOT NULL,
content text,
link_url text,
level text NOT NULL DEFAULT 'normal' CHECK (level IN ('normal','important','urgent')),
read_status boolean NOT NULL DEFAULT false,
read_at timestamptz,
expire_at timestamptz,
is_deleted boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE msg_notification IS '站内消息与告警';
COMMENT ON COLUMN msg_notification.level IS 'normal 普通 / important 重要 / urgent 紧急';
CREATE INDEX idx_msg_notification_receiver ON msg_notification (receiver_id, read_status);
CREATE TABLE msg_subscription (
id bigserial PRIMARY KEY,
user_id text NOT NULL,
domain text NOT NULL CHECK (domain IN ('order','finance','quality','supply','operation')),
channels jsonb NOT NULL DEFAULT '["inbox"]'::jsonb,
dnd_start time,
dnd_end time,
is_deleted boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (user_id, domain)
);
COMMENT ON TABLE msg_subscription IS '账号消息订阅与免打扰设置';
CREATE TABLE msg_send_record (
id bigserial PRIMARY KEY,
notification_id bigint,
channel text NOT NULL CHECK (channel IN ('inbox','im','wecom','email','sms')),
send_status text NOT NULL DEFAULT 'pending' CHECK (send_status IN ('pending','sent','failed')),
retry_count integer NOT NULL DEFAULT 0,
fail_reason text,
sent_at timestamptz,
is_deleted boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
COMMENT ON TABLE msg_send_record IS '各渠道发送记录与重试';
CREATE INDEX idx_msg_send_record_status ON msg_send_record (send_status);
CREATE OR REPLACE FUNCTION set_msg_updated_at()
RETURNS trigger AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_msg_template_updated BEFORE UPDATE ON msg_template FOR EACH ROW EXECUTE FUNCTION set_msg_updated_at();
CREATE TRIGGER trg_msg_notification_updated BEFORE UPDATE ON msg_notification FOR EACH ROW EXECUTE FUNCTION set_msg_updated_at();
CREATE TRIGGER trg_msg_subscription_updated BEFORE UPDATE ON msg_subscription FOR EACH ROW EXECUTE FUNCTION set_msg_updated_at();
CREATE TRIGGER trg_msg_send_record_updated BEFORE UPDATE ON msg_send_record FOR EACH ROW EXECUTE FUNCTION set_msg_updated_at();
