Multi-Agent Collaboration for Team and Friends
TypeScript agent-automationagent-orchestrationagent-workflowai-agentsai-collaborationai-memoryai-workforceautonomous-agentsclaude-codeclaude-code-skillscodexcoding-agentmulti-agentmultiple-agentone-person-businessone-person-companyone-person-teamopencodesolopreneur
import Sqlite from "better-sqlite3";
import { drizzle } from "drizzle-orm/better-sqlite3";
import { drizzle as drizzleProxy } from "drizzle-orm/sqlite-proxy";
import { afterEach, beforeEach, describe, it, expect, vi } from "vitest";
import * as threadQueries from "../../src/db/queries/community/thread";
import { D1_MAX_BIND_PARAMS } from "../../src/db/queries/_chunk";
import { FORUM_ARCHIVE_TAG } from "../../src/constants/community";
describe("community/thread exports", () => {
it("exports the participant CRUD", () => {
expect(typeof threadQueries.listThreadParticipants).toBe("function");
expect(typeof threadQueries.removeThreadParticipant).toBe("function");
});
});
describe("listForumThreadsByCreatedAt against real SQLite", () => {
let sqlite: Sqlite.Database;
let db: ReturnType<typeof drizzle>;
beforeEach(() => {
sqlite = new Sqlite(":memory:");
sqlite.exec(`
CREATE TABLE community_channel (
id TEXT PRIMARY KEY,
server_id TEXT,
category_id TEXT,
name TEXT,
type TEXT NOT NULL DEFAULT 'text',
topic TEXT DEFAULT '',
position INTEGER DEFAULT 0,
parent_channel_id TEXT,
creator_id TEXT,
message_count INTEGER DEFAULT 0,
archived INTEGER DEFAULT 0,
parent_message_id TEXT,
last_message_at TEXT,
created_at TEXT NOT NULL
);
CREATE TABLE community_message_tag (
id TEXT PRIMARY KEY,
message_id TEXT NOT NULL,
tag TEXT NOT NULL,
UNIQUE(message_id, tag)
);
CREATE INDEX idx_channel_forum_created
ON community_channel(parent_channel_id, created_at DESC, id DESC)
WHERE type = 'thread' AND archived = 0 AND parent_message_id IS NOT NULL;
CREATE TABLE user (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
image TEXT
,avatarVersion INTEGER NOT NULL DEFAULT 0
,avatarObjectKey TEXT
,discriminator TEXT
);
CREATE TABLE community_channel_member (
id TEXT PRIMARY KEY,
channel_id TEXT NOT NULL,
user_id TEXT NOT NULL,
relation TEXT NOT NULL,
source TEXT NOT NULL,
added_by TEXT,
added_at TEXT NOT NULL
);
`);
const insertChannel = sqlite.prepare(`
INSERT INTO community_channel
(id, name, type, parent_channel_id, parent_message_id, archived, last_message_at, created_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
`);
insertChannel.run("t_new", "new activity", "thread", "forum_1", "m_new", 0, "2026-08-08T05:00:00.000Z", "2026-01-01T00:00:00.000Z");
insertChannel.run("t_tie_b", "tie b", "thread", "forum_1", "m_tie_b", 0, "2026-08-08T04:00:00.000Z", "2026-01-02T00:00:00.000Z");
insertChannel.run("t_tie_a", "tie a", "thread", "forum_1", "m_tie_a", 0, "2026-08-08T04:00:00.000Z", "2026-01-02T00:00:00.000Z");
insertChannel.run("t_created", "created fallback", "thread", "forum_1", "m_created", 0, null, "2026-08-08T02:00:00.000Z");
insertChannel.run("t_tag_archived", "tag archived", "thread", "forum_1", "m_tag_archived", 0, null, "2026-08-09T02:00:00.000Z");
insertChannel.run("t_archived", "archived", "thread", "forum_1", "m_archived", 1, "2026-08-09T00:00:00.000Z", "2026-01-01T00:00:00.000Z");
insertChannel.run("t_rootless", "rootless", "thread", "forum_1", null, 0, "2026-08-09T00:00:00.000Z", "2026-01-01T00:00:00.000Z");
insertChannel.run("not_thread", "text child", "text", "forum_1", "m_text", 0, "2026-08-09T00:00:00.000Z", "2026-01-01T00:00:00.000Z");
insertChannel.run("foreign", "foreign", "thread", "forum_2", "m_foreign", 0, "2026-08-10T00:00:00.000Z", "2026-01-01T00:00:00.000Z");
const insertTag = sqlite.prepare("INSERT INTO community_message_tag (id, message_id, tag) VALUES (?, ?, ?)");
insertTag.run("tag_tie", "m_tie_a", "bug");
insertTag.run("tag_created", "m_created", "bug");
insertTag.run("tag_archive", "m_tag_archived", FORUM_ARCHIVE_TAG);
insertTag.run("tag_archive_bug", "m_tag_archived", "bug");
insertTag.run("tag_foreign", "m_foreign", "bug");
db = drizzle(sqlite);
});
afterEach(() => sqlite.close());
it("keeps creation order when an older post has newer activity and pages ties without overlap", async () => {
const first = await threadQueries.listForumThreadsByCreatedAt(db as never, {
parentChannelId: "forum_1",
limit: 2,
});
const second = await threadQueries.listForumThreadsByCreatedAt(db as never, {
parentChannelId: "forum_1",
cursor: { createdAt: first[1]!.createdAt, id: first[1]!.id },
limit: 3,
});
expect(first.map((row) => row.id)).toEqual(["t_created", "t_tie_b"]);
expect(second.map((row) => row.id)).toEqual(["t_tie_a", "t_new"]);
expect(second[1]!.activityAt).toBe("2026-08-08T05:00:00.000Z");
expect(new Set([...first, ...second].map((row) => row.id)).size).toBe(4);
});
it("applies the tag and forum scope before the creation limit", async () => {
const rows = await threadQueries.listForumThreadsByCreatedAt(db as never, {
parentChannelId: "forum_1",
tag: "bug",
limit: 5,
});
expect(rows.map((row) => row.id)).toEqual(["t_created", "t_tie_a"]);
});
it("treats the archive tag as an exclusive feed before pagination", async () => {
const all = await threadQueries.listForumThreadsByCreatedAt(db as never, {
parentChannelId: "forum_1",
limit: 1,
});
const ordinary = await threadQueries.listForumThreadsByCreatedAt(db as never, {
parentChannelId: "forum_1",
tag: "bug",
limit: 5,
});
const archived = await threadQueries.listForumThreadsByCreatedAt(db as never, {
parentChannelId: "forum_1",
tag: FORUM_ARCHIVE_TAG,
limit: 5,
});
expect(all.map((row) => row.id)).toEqual(["t_created"]);
expect(ordinary.map((row) => row.id)).toEqual(["t_created", "t_tie_a"]);
expect(archived.map((row) => row.id)).toEqual(["t_tag_archived"]);
});
it("uses the forum creation index without a temp B-tree for the untagged feed", () => {
const plan = sqlite.prepare(`
EXPLAIN QUERY PLAN
SELECT id
FROM community_channel
WHERE parent_channel_id = ?
AND type = 'thread'
AND archived = 0
AND parent_message_id IS NOT NULL
ORDER BY created_at DESC, id DESC
LIMIT ?
`).all("forum_1", 50) as Array<{ detail: string }>;
const details = plan.map((row) => row.detail);
expect(details.some((detail) => detail.includes("USING INDEX idx_channel_forum_created"))).toBe(true);
expect(details.some((detail) => detail.includes("USE TEMP B-TREE"))).toBe(false);
});
it("preserves SQLite BINARY participant tie ordering for case-sensitive ids", async () => {
sqlite.prepare("INSERT INTO user (id, name) VALUES (?, ?)").run("A", "upper");
sqlite.prepare("INSERT INTO user (id, name) VALUES (?, ?)").run("a", "lower");
const insertMember = sqlite.prepare(`
INSERT INTO community_channel_member
(id, channel_id, user_id, relation, source, added_at)
VALUES (?, ?, ?, 'notify', 'added', ?)
`);
const tiedAt = "2026-08-08T06:00:00.000Z";
insertMember.run("member_a", "t_new", "a", tiedAt);
insertMember.run("member_A", "t_new", "A", tiedAt);
const rows = await threadQueries.listParticipantsForChannels(
db as never,
["t_new"],
5,
);
expect(rows.map((row) => row.userId)).toEqual(["A", "a"]);
});
it.each([undefined, 1])("retains real participant numbers across chunks and duplicate channel ids (limit %s)", async (limit) => {
sqlite.prepare("INSERT INTO user (id, name, discriminator) VALUES (?, ?, ?)").run("numbered", "Numbered", "123456");
sqlite.prepare("INSERT INTO user (id, name, discriminator) VALUES (?, ?, ?)").run("later", "Later", "0042");
const insert = sqlite.prepare("INSERT INTO community_channel_member (id, channel_id, user_id, relation, source, added_at) VALUES (?, ?, ?, 'notify', 'added', ?)");
const channelIds = Array.from({ length: 100 }, (_, index) => `numbered_thread_${index}`);
for (const channelId of channelIds) {
insert.run(`${channelId}:first`, channelId, "numbered", "2026-10-11T01:00:00Z");
insert.run(`${channelId}:second`, channelId, "later", "2026-10-11T02:00:00Z");
}
const rows = await threadQueries.listParticipantsForChannels(db as never, [...channelIds, ...channelIds], limit);
expect(rows).toHaveLength(limit === undefined ? 200 : 100);
expect(rows.filter((row) => row.userId === "numbered")).toHaveLength(100);
expect(rows.filter((row) => row.userId === "numbered").every((row) => row.discriminator === "123456")).toBe(true);
expect(new Set(rows.map((row) => `${row.channelId}:${row.userId}`)).size).toBe(rows.length);
if (limit !== undefined) expect(rows.every((row) => "participantCount" in row && row.participantCount === 2)).toBe(true);
else expect(rows.filter((row) => row.userId === "later").every((row) => row.discriminator === "0042")).toBe(true);
});
it("keeps retained disposition explicit when the participating query is disabled", async () => {
for (const params of [
{ parentChannelIds: [] as string[], limitPerParent: 5 },
{ parentChannelIds: ["forum_1"], limitPerParent: 0 },
]) {
const result = await threadQueries.listParticipatingForumThreads(db as never, {
...params,
userId: "viewer",
activeAfter: "2026-08-08T03:00:00.000Z",
retainId: "t_created",
});
expect(result).toEqual({
canonical: [],
retained: null,
retainedDisposition: "genuine-negative",
});
}
});
it("returns per-forum recent notify rows and retains an older open post", async () => {
sqlite.prepare("INSERT INTO user (id, name) VALUES (?, ?)").run("viewer", "Viewer");
const insertMember = sqlite.prepare(`
INSERT INTO community_channel_member
(id, channel_id, user_id, relation, source, added_at)
VALUES (?, ?, 'viewer', 'notify', 'spoke', '2026-08-08T00:00:00.000Z')
`);
for (const id of ["t_new", "t_tie_b", "t_tie_a", "t_created", "t_tag_archived", "t_archived", "foreign"]) {
insertMember.run(`member_${id}`, id);
}
sqlite.prepare(
"INSERT INTO community_message_tag (id, message_id, tag) VALUES (?, ?, ?)",
).run("tag_channel_archive", "m_archived", FORUM_ARCHIVE_TAG);
const recent = await threadQueries.listParticipatingForumThreads(db as never, {
parentChannelIds: ["forum_1", "forum_2"],
userId: "viewer",
activeAfter: "2026-08-08T03:00:00.000Z",
limitPerParent: 2,
});
expect(recent.canonical.map((row) => row.id)).toEqual(["t_new", "t_tie_b", "foreign"]);
expect(recent.retained).toBeNull();
expect(recent.retainedDisposition).toBeNull();
const retained = await threadQueries.listParticipatingForumThreads(db as never, {
parentChannelIds: ["forum_1", "forum_2"],
userId: "viewer",
activeAfter: "2026-08-08T03:00:00.000Z",
limitPerParent: 2,
retainId: "t_created",
});
expect(retained.canonical.map((row) => row.id)).toEqual(["t_new", "t_tie_b", "foreign"]);
expect(retained.retained?.id).toBe("t_created");
expect(retained.retainedDisposition).toBe("eligible");
const openerArchived = await threadQueries.listParticipatingForumThreads(db as never, {
parentChannelIds: ["forum_1"],
userId: "viewer",
activeAfter: "2026-08-08T03:00:00.000Z",
limitPerParent: 2,
retainId: "t_tag_archived",
});
expect(openerArchived.retained).toBeNull();
expect(openerArchived.retainedDisposition).toBe("opener-archived");
const channelArchived = await threadQueries.listParticipatingForumThreads(db as never, {
parentChannelIds: ["forum_1"],
userId: "viewer",
activeAfter: "2026-08-08T03:00:00.000Z",
limitPerParent: 2,
retainId: "t_archived",
});
expect(channelArchived.retained).toBeNull();
expect(channelArchived.retainedDisposition).toBe("genuine-negative");
sqlite.prepare("INSERT INTO user (id, name) VALUES (?, ?)").run("outsider", "Outsider");
const nonparticipantArchived = await threadQueries.listParticipatingForumThreads(db as never, {
parentChannelIds: ["forum_1"],
userId: "outsider",
activeAfter: "2026-08-08T03:00:00.000Z",
limitPerParent: 2,
retainId: "t_tag_archived",
});
expect(nonparticipantArchived.retained).toBeNull();
expect(nonparticipantArchived.retainedDisposition).toBe("genuine-negative");
const outOfScopeRetained = await threadQueries.listParticipatingForumThreads(db as never, {
parentChannelIds: ["forum_1"],
userId: "viewer",
activeAfter: "2026-08-08T03:00:00.000Z",
limitPerParent: 2,
retainId: "foreign",
});
expect(outOfScopeRetained.canonical.map((row) => row.id)).toEqual(["t_new", "t_tie_b"]);
expect(outOfScopeRetained.retained).toBeNull();
expect(outOfScopeRetained.retainedDisposition).toBe("genuine-negative");
});
it("filters archived openers before top-five ranking and refills from the sixth active post", async () => {
sqlite.prepare("INSERT INTO user (id, name) VALUES (?, ?)").run("viewer", "Viewer");
const insertChannel = sqlite.prepare(`
INSERT INTO community_channel
(id, name, type, parent_channel_id, parent_message_id, archived, last_message_at, created_at)
VALUES (?, ?, 'thread', 'forum_1', ?, 0, ?, '2026-01-01T00:00:00.000Z')
`);
insertChannel.run("t_fifth", "fifth", "m_fifth", "2026-08-08T03:30:00.000Z");
insertChannel.run("t_sixth", "sixth", "m_sixth", "2026-08-08T03:00:00.000Z");
const insertMember = sqlite.prepare(`
INSERT INTO community_channel_member
(id, channel_id, user_id, relation, source, added_at)
VALUES (?, ?, 'viewer', 'notify', 'spoke', '2026-08-08T00:00:00.000Z')
`);
for (const id of [
"t_tag_archived",
"t_new",
"t_tie_b",
"t_tie_a",
"t_fifth",
"t_sixth",
"t_created",
]) {
insertMember.run(`member_${id}`, id);
}
sqlite.prepare("DELETE FROM community_message_tag WHERE id = 'tag_archive'").run();
const before = await threadQueries.listParticipatingForumThreads(db as never, {
parentChannelIds: ["forum_1"],
userId: "viewer",
activeAfter: "2026-08-08T00:00:00.000Z",
limitPerParent: 5,
});
expect(before.canonical.map((row) => row.id)).toEqual([
"t_tag_archived",
"t_new",
"t_tie_b",
"t_tie_a",
"t_fifth",
]);
sqlite.prepare(
"INSERT INTO community_message_tag (id, message_id, tag) VALUES (?, ?, ?)",
).run("tag_archive_again", "m_tag_archived", FORUM_ARCHIVE_TAG);
const archived = await threadQueries.listParticipatingForumThreads(db as never, {
parentChannelIds: ["forum_1"],
userId: "viewer",
activeAfter: "2026-08-08T00:00:00.000Z",
limitPerParent: 5,
});
expect(archived.canonical.map((row) => row.id)).toEqual([
"t_new",
"t_tie_b",
"t_tie_a",
"t_fifth",
"t_sixth",
]);
sqlite.prepare("DELETE FROM community_message_tag WHERE id = 'tag_archive_again'").run();
const unarchived = await threadQueries.listParticipatingForumThreads(db as never, {
parentChannelIds: ["forum_1"],
userId: "viewer",
activeAfter: "2026-08-08T00:00:00.000Z",
limitPerParent: 5,
});
expect(unarchived.canonical.map((row) => row.id)).toEqual(before.canonical.map((row) => row.id));
});
});
describe("addThreadParticipant", () => {
function insertMock(returned: any[]) {
const chain: any = {};
chain.insert = vi.fn(() => chain);
chain.values = vi.fn(() => chain);
chain.onConflictDoNothing = vi.fn(() => chain);
chain.returning = vi.fn(() => Promise.resolve(returned));
return chain;
}
it("returns the inserted row for a new participant", async () => {
const db = insertMock([{ id: "tp1", threadChannelId: "t1", userId: "u1" }]);
const res = await threadQueries.addThreadParticipant(db, {
threadChannelId: "t1",
userId: "u1",
source: "spoke",
});
expect(res).toMatchObject({ id: "tp1" });
});
it("returns null on conflict (already a participant)", async () => {
const db = insertMock([]);
const res = await threadQueries.addThreadParticipant(db, {
threadChannelId: "t1",
userId: "u1",
source: "mention",
});
expect(res).toBeNull();
});
});
describe("addThreadParticipants — bulk", () => {
it("skips the query entirely for an empty rows list", async () => {
const chain: any = { insert: vi.fn() };
await threadQueries.addThreadParticipants(chain, "t1", []);
expect(chain.insert).not.toHaveBeenCalled();
});
it("inserts one row per (userId, source) pair with onConflictDoNothing", async () => {
const returning = vi.fn(() => Promise.resolve([{ userId: "u1" }, { userId: "u2" }]));
const values = vi.fn(() => ({
onConflictDoNothing: vi.fn(() => ({ returning })),
}));
const chain: any = { insert: vi.fn(() => ({ values })) };
await expect(threadQueries.addThreadParticipants(chain, "t1", [
{ userId: "u1", source: "spoke" },
{ userId: "u2", source: "mention" },
])).resolves.toEqual(["u1", "u2"]);
expect(chain.insert).toHaveBeenCalledTimes(1); // single bulk insert, not N
expect(values).toHaveBeenCalledWith([
{ channelId: "t1", userId: "u1", relation: "notify", source: "spoke" },
{ channelId: "t1", userId: "u2", relation: "notify", source: "mention" },
]);
expect(returning).toHaveBeenCalledTimes(1);
});
});
describe("listThreadParticipantUserIds — notify set", () => {
function whereMock(rows: any[]) {
const chain: any = {};
chain.select = vi.fn(() => chain);
chain.from = vi.fn(() => chain);
chain.where = vi.fn(() => Promise.resolve(rows));
return chain;
}
it("returns every participant's userId", async () => {
const db = whereMock([{ userId: "u1" }, { userId: "u2" }]);
const res = await threadQueries.listThreadParticipantUserIds(db, "t1");
expect(res).toEqual(["u1", "u2"]);
});
});
describe("listParticipantsForChannels — D1 bind cap", () => {
it("chunks 100 channel ids so every ranked statement stays within 100 binds", async () => {
const paramCounts: number[] = [];
const db = drizzleProxy(async (_sql, params) => {
paramCounts.push(params.length);
return { rows: [] };
});
await threadQueries.listParticipantsForChannels(
db as never,
Array.from({ length: 100 }, (_, i) => `thread_${i}`),
5,
);
expect(paramCounts).toHaveLength(2);
expect(Math.max(...paramCounts)).toBeLessThanOrEqual(D1_MAX_BIND_PARAMS);
});
});
describe("listParticipatingForumThreads — D1 bind cap", () => {
it("chunks 100 visible forum ids so ranked and retained statements stay within 100 binds", async () => {
const paramCounts: number[] = [];
const db = drizzleProxy(async (_sql, params) => {
paramCounts.push(params.length);
return { rows: [] };
});
await threadQueries.listParticipatingForumThreads(db as never, {
parentChannelIds: Array.from({ length: 100 }, (_, i) => `forum_${i}`),
userId: "viewer_1",
activeAfter: "2026-08-05T00:00:00.000Z",
limitPerParent: 5,
retainId: "thread_1",
});
expect(paramCounts).toHaveLength(4);
expect(Math.max(...paramCounts)).toBeLessThanOrEqual(D1_MAX_BIND_PARAMS);
});
});
describe("listParticipatingThreadIds", () => {
function whereMock(rows: any[]) {
const chain: any = {};
chain.select = vi.fn(() => chain);
chain.from = vi.fn(() => chain);
chain.where = vi.fn(() => Promise.resolve(rows));
return chain;
}
it("returns [] without querying for an empty id list", async () => {
const db = whereMock([{ channelId: "should_not_be_used" }]);
const res = await threadQueries.listParticipatingThreadIds(db, [], "u1");
expect(res).toEqual([]);
expect(db.select).not.toHaveBeenCalled();
});
it("returns the participating thread ids", async () => {
const db = whereMock([{ channelId: "t1" }, { channelId: "t3" }]);
const res = await threadQueries.listParticipatingThreadIds(db, ["t1", "t2", "t3"], "u1");
expect(res).toEqual(["t1", "t3"]);
});
});
describe("removeParticipantFromChildChannels — member-remove notify cascade", () => {
function makeDb(postRows: any[], deletedRows: any[]) {
const selectChain: any = {};
selectChain.select = vi.fn(() => selectChain);
selectChain.from = vi.fn(() => selectChain);
selectChain.where = vi.fn(() => Promise.resolve(postRows));
const deleteChain: any = {};
deleteChain.delete = vi.fn(() => deleteChain);
deleteChain.where = vi.fn(() => deleteChain);
deleteChain.returning = vi.fn(() => Promise.resolve(deletedRows));
return {
select: selectChain.select,
from: selectChain.from,
where: selectChain.where,
delete: deleteChain.delete,
} as any;
}
it("skips the delete when the parent has no children", async () => {
const db = makeDb([], []);
const n = await threadQueries.removeParticipantFromChildChannels(db, "forum_1", "u_removed");
expect(n).toBe(0);
expect(db.delete).not.toHaveBeenCalled();
});
it("deletes the user's participant rows across the parent's children", async () => {
const db = makeDb([{ id: "p1" }, { id: "p2" }], [{ id: "tp1" }, { id: "tp2" }]);
const n = await threadQueries.removeParticipantFromChildChannels(db, "forum_1", "u_removed");
expect(n).toBe(2);
expect(db.delete).toHaveBeenCalled();
});
});