跳转到内容

做一个团队投票卡

群里”晚上吃什么”、“功能优先级 1-5”、“开会时间” — 一行 /vote 命令秒出投票卡。比 mini-app/first-mini-app-vote 更工程化：含 DB 持久化 / 历史查询 / 防作弊。

用户故事

“现在群里要决策小事都靠 @everyone + 数表情包，效率低。想要：@VoteBot 午餐拉面披萨沙拉 一句话开投；卡片实时更新；用户已投不能改投；结束后历史可查；导出 CSV。“

与 mini-app 投票卡的差别

维度	mini-app/first-mini-app-vote	本教程
状态	in-memory Map	PG 持久化
多 replica	❌ 不支持	✓
历史查询	❌	✓ `/votes` 列上周所有
一人一票	in-memory Set	DB unique constraint
命令	`/vote 午餐拉面披萨`	同 + `/votes` 历史 + `/close <id>` 提前关

完整代码

import { HasheeAgent } from "@hasheeai/agent-sdk-ts";
import { Pool } from "pg";
import { ulid } from "ulid";

const db = new Pool({ connectionString: process.env.DATABASE_URL });

const agent = await HasheeAgent.init({
  agentId: process.env.HASHEE_AGENT_ID!,
  token: process.env.HASHEE_AGENT_TOKEN!,
  baseUrl: "https://api.hashee.ai",
  connectionMode: "websocket",
});

const MY_ID = process.env.HASHEE_AGENT_ID!;

agent.addMessageHandler(async (msg) => {
  if (msg.payload?.type !== "text") return;
  if (msg.conversation_type === "group" && !msg.mentions?.includes(MY_ID)) return;

  const text = stripMention(msg.payload.text, MY_ID);

  // /vote 标题 选项1 选项2 选项3
  let m = text.match(/^\/vote\s+([^\s]+)\s+(.+)$/);
  if (m) return startVote(msg, m[1], m[2].split(/\s+/));

  // /votes 列历史
  if (text === "/votes") return listVotes(msg);

  // /close <id>
  m = text.match(/^\/close\s+([A-Z0-9]+)$/);
  if (m) return closeVote(msg, m[1]);

  await agent.send(msg.conversation_id, {
    type: "text",
    text: "用法：\n`/vote 午餐 拉面 披萨 沙拉` — 发起投票\n`/votes` — 列上周投票\n`/close <id>` — 提前结束",
  });
});

// === startVote ===

async function startVote(msg: any, question: string, options: string[]) {
  if (options.length < 2 || options.length > 5) {
    await agent.send(msg.conversation_id, {
      type: "text",
      text: "投票需要 2-5 个选项。",
    });
    return;
  }
  const artifactId = ulid();
  await db.query("BEGIN");
  try {
    await db.query(
      `INSERT INTO votes (artifact_id, conv_id, question, options, created_by, ends_at)
       VALUES ($1, $2, $3, $4, $5, NOW() + INTERVAL '30 minutes')`,
      [artifactId, msg.conversation_id, question, JSON.stringify(options), msg.sender_id],
    );
    await db.query("COMMIT");
  } catch (e) {
    await db.query("ROLLBACK");
    throw e;
  }

  await agent.sendArtifact(msg.conversation_id, {
    artifact: {
      artifact_id: artifactId,
      subtype: "app",
      title: question,
      payload: await renderVote(artifactId),
      capability_flags: { allows_response: true },
      expires_at: new Date(Date.now() + 30 * 60 * 1000).toISOString(),
    },
  });
}

// === 投票回流 ===

agent.addEventHandler(async (event) => {
  if (event.type !== "artifact_response") return;
  const artifactId = event.payload.ref_artifact;
  if (event.payload.action !== "vote") return;

  const choice: string = event.payload.payload?.choice;
  const userId = event.sender_id;

  // DB UNIQUE(artifact_id, user_id) 防重复投票
  await db.query("BEGIN");
  try {
    const v = await db.query(
      "SELECT * FROM votes WHERE artifact_id = $1 AND ends_at > NOW() AND closed = FALSE",
      [artifactId],
    );
    if (v.rows.length === 0) {
      await db.query("ROLLBACK");
      return;
    }
    await db.query(
      "INSERT INTO vote_choices (artifact_id, user_id, choice) VALUES ($1, $2, $3) ON CONFLICT DO NOTHING",
      [artifactId, userId, choice],
    );
    await db.query("COMMIT");
  } catch (e) {
    await db.query("ROLLBACK");
    throw e;
  }

  await updateVote(artifactId);
});

async function updateVote(artifactId: string) {
  const v = await db.query("SELECT * FROM votes WHERE artifact_id = $1", [artifactId]);
  if (v.rows.length === 0) return;
  const row = v.rows[0];
  const revision = row.revision + 1;
  await db.query("UPDATE votes SET revision = $1 WHERE artifact_id = $2", [revision, artifactId]);
  try {
    await agent.updateArtifact(row.conv_id, {
      ref_artifact: artifactId,
      based_on_revision: revision - 1,
      artifact: { payload: await renderVote(artifactId) },
    });
  } catch (e: any) {
    if (e.code === "ARTIFACT_REVISION_CONFLICT") {
      // 并发冲突：拉最新 server revision 再重试一次
      console.warn("vote update conflict, retrying", artifactId);
    } else throw e;
  }
}

async function renderVote(artifactId: string) {
  const v = await db.query("SELECT * FROM votes WHERE artifact_id = $1", [artifactId]);
  const row = v.rows[0];
  const options = row.options as string[];
  const counts = await db.query(
    `SELECT choice, COUNT(*)::int AS n FROM vote_choices
     WHERE artifact_id = $1 GROUP BY choice`,
    [artifactId],
  );
  const totalVoters = counts.rows.reduce((a, r) => a + r.n, 0);
  const map: Record<string, number> = {};
  for (const r of counts.rows) map[r.choice] = r.n;

  return {
    ui: "vote_card",
    question: row.question,
    options: options.map((label, i) => ({
      id: String.fromCharCode(97 + i),
      label,
      votes: map[String.fromCharCode(97 + i)] ?? 0,
      pct: totalVoters ? Math.round(((map[String.fromCharCode(97 + i)] ?? 0) / totalVoters) * 100) : 0,
    })),
    total: totalVoters,
    closed: row.closed,
    ends_at: row.ends_at.toISOString(),
  };
}

// === closeVote ===

async function closeVote(msg: any, artifactId: string) {
  await db.query("UPDATE votes SET closed = TRUE WHERE artifact_id = $1", [artifactId]);
  await updateVote(artifactId);
  await agent.send(msg.conversation_id, { type: "text", text: `✓ 投票 ${artifactId} 已关闭` });
}

// === listVotes ===

async function listVotes(msg: any) {
  const r = await db.query(
    `SELECT artifact_id, question, created_at,
            (SELECT COUNT(*) FROM vote_choices WHERE artifact_id = v.artifact_id) AS votes
     FROM votes v
     WHERE conv_id = $1 AND created_at > NOW() - INTERVAL '7 days'
     ORDER BY created_at DESC
     LIMIT 20`,
    [msg.conversation_id],
  );
  if (r.rows.length === 0) {
    await agent.send(msg.conversation_id, { type: "text", text: "上周没有投票。" });
    return;
  }
  const body = r.rows.map((row) =>
    `**${row.question}** (${row.votes} 票) — ID: \`${row.artifact_id}\``,
  ).join("\n");
  await agent.sendArtifact(msg.conversation_id, {
    artifact: { subtype: "info", title: "本群上周投票", payload: { body } },
  });
}

// === 工具 ===

function stripMention(text: string, agentId: string): string {
  return text.replace(new RegExp(`@<U:${agentId}>\\s?`, "g"), "").trim();
}

agent.addStatusHandler((s) => console.log(`[status] ${s}`));
console.log("[vote-bot] up");

DB schema

CREATE TABLE votes (
  artifact_id TEXT PRIMARY KEY,
  conv_id TEXT NOT NULL,
  question TEXT NOT NULL,
  options JSONB NOT NULL,
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  ends_at TIMESTAMPTZ NOT NULL,
  closed BOOLEAN DEFAULT FALSE,
  revision INT DEFAULT 0
);

CREATE TABLE vote_choices (
  artifact_id TEXT REFERENCES votes(artifact_id),
  user_id TEXT NOT NULL,
  choice TEXT NOT NULL,
  ts TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(artifact_id, user_id)   -- 一人一票
);

CSV 导出（业务侧）

psql $DATABASE_URL -c "\copy (
  SELECT v.question, vc.choice, COUNT(*)
  FROM votes v JOIN vote_choices vc USING(artifact_id)
  WHERE v.conv_id = '<group_id>' AND v.created_at > NOW() - INTERVAL '30 days'
  GROUP BY v.question, vc.choice
  ORDER BY v.question
) TO 'votes.csv' WITH CSV HEADER"

相关页面

Artifact 高级 — 双向交互 — revision / 冲突处理
小程序入门 — 投票卡 — 内存版（简化）
群组消息处理 — @mention 路由