内容资讯类小程序：内容审核、栏目体系与订阅消息推送的工程实现

CREATE TABLE channel (
 id          BIGINT PRIMARY KEY AUTO_INCREMENT,
 parent_id   BIGINT NOT NULL DEFAULT 0,
 name        VARCHAR(40) NOT NULL,
 slug        VARCHAR(40) NOT NULL COMMENT 'URL 友好标识',
 sort        INT NOT NULL DEFAULT 0,
 status      TINYINT NOT NULL DEFAULT 1,
 UNIQUE KEY uk_slug (slug),
 KEY idx_parent (parent_id)
);

CREATE TABLE article_channel (
 article_id BIGINT NOT NULL,
 channel_id BIGINT NOT NULL,
 is_primary TINYINT NOT NULL DEFAULT 0 COMMENT '主栏目，用于面包屑',
 PRIMARY KEY (article_id, channel_id),
 KEY idx_channel (channel_id)
);

// 云函数 checkContent
const cloud = require('wx-server-sdk');
cloud.init({ env: cloud.DYNAMIC_CURRENT_ENV });

exports.main = async (event) => {
 const { content, images = [] } = event;
 const results = { text: null, images: [] };

 if (content && content.trim()) {
 try {
 const r = await cloud.openapi.security.msgSecCheck({
 content: content.slice(0, 2500),
 version: 2,
 scene: 2,          // 2 表示评论/文章
 openid: event.openid,
 });
 results.text = r.result && r.result.suggest;  // pass / review / risky
 } catch (e) {
 // 接口异常时按 review 处理，不放行也不拒绝
 results.text = 'review';
 }
 }

 for (const url of images.slice(0, 9)) {
 try {
 const r = await cloud.openapi.security.imgSecCheck({ media: { contentType: 'image/png', value: Buffer.from(await fetchBuffer(url)) } });
 results.images.push(r.errCode === 0 ? 'pass' : 'review');
 } catch (e) {
 results.images.push('review');
 }
 }
 return results;
};

CREATE TABLE content_audit (
 id          BIGINT PRIMARY KEY AUTO_INCREMENT,
 target_type TINYINT NOT NULL COMMENT '1文章 2评论',
 target_id   BIGINT NOT NULL,
 stage       TINYINT NOT NULL COMMENT '1机器 2人工 3举报',
 result      TINYINT NOT NULL COMMENT '1通过 2拒绝 3待处理',
 reason      VARCHAR(200) NULL,
 operator    VARCHAR(40) NULL,
 created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_target (target_type, target_id)
);

// 前端：用户点击"订阅XX栏目更新"时
async function subscribeChannel(channelId) {
 const res = await wx.requestSubscribeMessage({
 tmplIds: [TMPL_CHANNEL_UPDATE],
 });
 if (res[TMPL_CHANNEL_UPDATE] === 'accept') {
 await wx.cloud.callFunction({
 name: 'saveSubscribe',
 data: { channelId, tmplId: TMPL_CHANNEL_UPDATE },
 });
 wx.showToast({ title: '已订阅' });
 }
}

CREATE TABLE subscribe_quota (
 id         BIGINT PRIMARY KEY AUTO_INCREMENT,
 openid     VARCHAR(64) NOT NULL,
 tmpl_id    VARCHAR(64) NOT NULL,
 remain     INT NOT NULL DEFAULT 0 COMMENT '剩余可发次数',
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uk_openid_tmpl (openid, tmpl_id)
);

async function sendOnce(openid, tmplId, page, data, bizKey) {
 // bizKey 保证同一业务事件只发一次，比如 article:12345
 const key = `msg:${openid}:${bizKey}`;
 const ok = await redis.set(key, '1', 'NX', 'EX', 604800);
 if (!ok) return { skipped: true };

 const quota = await getQuota(openid, tmplId);
 if (quota <= 0) return { skipped: true, reason: 'no_quota' };

 try {
 await cloud.openapi.subscribeMessage.send({
 touser: openid, templateId: tmplId, page, data,
 miniprogramState: 'formal',
 });
 await decQuota(openid, tmplId);
 return { ok: true };
 } catch (e) {
 await redis.del(key);   // 发送失败释放幂等键，允许重试
 return { ok: false, err: e.errCode };
 }
}

async function getFeed(channelId, cursor, limit = 20) {
  const cacheKey = `feed:${channelId}:${cursor || 0}:${limit}`;
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);

  const rows = await db.query(
    `SELECT a.id, a.title, a.cover, a.summary, a.published_at
       FROM article a
       JOIN article_channel ac ON ac.article_id = a.id
      WHERE ac.channel_id = ?
        AND a.status = 1
        AND a.published_at <= NOW()
        AND (? IS NULL OR a.id < ?)
      ORDER BY a.id DESC
      LIMIT ?`,
    [channelId, cursor, cursor, limit]
  );
  await redis.set(cacheKey, JSON.stringify(rows), 'EX', 60);
  return rows;
}