下载中心/建表设计稿

建表设计稿

下载原文件
-- 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();