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 { describe, it, expect, vi } from "vitest";
import * as serverQueries from "../../src/db/queries/community/server";
import {
communityServer,
communityCategory,
communityChannel,
communityChannelMember,
communityServerMember,
communityMention,
communityMessage,
communityReadState,
} from "../../src/db/community-schema";
// Mocks a Drizzle DB where each insert()/select() chain resolves to a
// caller-supplied row set (or `undefined` for terminal insert-without-returning).
// The mock records every call so we can assert the side-effects.
type InsertCall = {
table: unknown;
values: Record<string, unknown>;
returningArg: unknown;
};
type SelectCall = {
fields: unknown;
from: unknown;
};
function createDbMock(opts: {
insertReturns: unknown[][]; // per-insert `.returning()` payload, in call order
selectReturns: unknown[][]; // per-select `.where()` payload, in call order
}) {
const insertCalls: InsertCall[] = [];
const selectCalls: SelectCall[] = [];
let insertIdx = 0;
let selectIdx = 0;
const db: any = {
batch: vi.fn(async (queries: PromiseLike<unknown>[]) => Promise.all(queries)),
insert(table: unknown) {
const call: InsertCall = { table, values: {}, returningArg: undefined };
insertCalls.push(call);
const rowsForThisInsert = opts.insertReturns[insertIdx] ?? [];
insertIdx += 1;
const chain: any = {
values(v: Record<string, unknown>) {
call.values = v;
// Terminal insert without returning is awaited directly — make the
// chain itself thenable so `await db.insert(...).values(...)` works.
const thenable = {
returning(arg?: unknown) {
call.returningArg = arg;
return thenable;
},
then(resolve: (v: unknown) => void) {
resolve(rowsForThisInsert);
},
};
return thenable;
},
};
return chain;
},
select(fields: unknown) {
const call: SelectCall = { fields, from: undefined };
selectCalls.push(call);
const rowsForThisSelect = opts.selectReturns[selectIdx] ?? [];
selectIdx += 1;
const chain: any = {
from(t: unknown) {
call.from = t;
return chain;
},
where() {
return Promise.resolve(rowsForThisSelect);
},
};
return chain;
},
};
return { db, insertCalls, selectCalls };
}
describe("updateServerIconIfCurrent", () => {
function updateDb(rows: unknown[]) {
const chain: any = {};
chain.set = vi.fn(() => chain);
chain.where = vi.fn(() => chain);
chain.returning = vi.fn(async () => rows);
return { db: { update: vi.fn(() => chain) }, chain };
}
it.each([null, "server-icon/s1/old"])("returns the winner row for expected icon %s", async (expectedIcon) => {
const winner = { id: "s1", icon: "server-icon/s1/new" };
const { db, chain } = updateDb([winner]);
await expect(serverQueries.updateServerIconIfCurrent(db as any, {
serverId: "s1",
expectedIcon,
nextIcon: "server-icon/s1/new",
})).resolves.toEqual(winner);
expect(chain.set).toHaveBeenCalledWith({ icon: "server-icon/s1/new" });
expect(chain.where).toHaveBeenCalledTimes(1);
});
it("returns null when the old-value predicate loses", async () => {
const { db } = updateDb([]);
await expect(serverQueries.updateServerIconIfCurrent(db as any, {
serverId: "s1",
expectedIcon: "server-icon/s1/old",
nextIcon: "server-icon/s1/new",
})).resolves.toBeNull();
});
});
describe("createServer", () => {
const ownerId = "u_owner";
const serverRow = { id: "srv_1", name: "My Server", ownerId };
const publicCategoryRow = { id: "cat_public" };
const privateCategoryRow = { id: "cat_private" };
const privateChannelRow = { id: "ch_room" };
const memberRow = {
id: "mem_1",
userId: ownerId,
joinedAt: "2026-07-02T00:00:00.000Z",
};
it("returns { server, ownerMember } with fields sourced from the seeded rows and user join", async () => {
const { db } = createDbMock({
insertReturns: [
[serverRow], // insert communityServer
[publicCategoryRow], // insert public communityCategory
[], // insert public communityChannel (no returning)
[privateCategoryRow], // insert private communityCategory
[privateChannelRow], // insert private communityChannel
[], // insert private communityChannelMember
[memberRow], // insert communityServerMember w/ returning
],
selectReturns: [
[{ name: "Alice", image: "https://avatars/alice.png", discriminator: "0042" }], // select user
],
});
const result = await serverQueries.createServer(db, {
name: "My Server",
description: "hi",
ownerId,
});
expect(result.server).toEqual(serverRow);
expect(result.ownerMember).toEqual({
id: memberRow.id,
userId: ownerId,
joinedAt: memberRow.joinedAt,
userName: "Alice",
userImage: "https://avatars/alice.png",
userAvatarVersion: 0,
userDiscriminator: "0042",
});
});
it("ownerMember.userName falls back to '' + userImage to null if the joined user row is missing", async () => {
// The Better-Auth create.before hook + createUser/updateUser guards keep
// user.name non-empty, but the select can still miss (e.g. race between
// ownerId insert and this select) — return "" rather than null so the
// caller doesn't have to null-check a field that's typed non-null.
const { db } = createDbMock({
insertReturns: [[serverRow], [publicCategoryRow], [], [privateCategoryRow], [privateChannelRow], [], [memberRow]],
selectReturns: [[]],
});
const result = await serverQueries.createServer(db, {
name: "My Server",
ownerId,
});
expect(result.ownerMember.userName).toBe("");
expect(result.ownerMember.userImage).toBeNull();
});
it("seeds Public/all and private Private/room with owner access", async () => {
const { db, insertCalls } = createDbMock({
insertReturns: [[serverRow], [publicCategoryRow], [], [privateCategoryRow], [privateChannelRow], [], [memberRow]],
selectReturns: [[{ name: "Alice" }]],
});
await serverQueries.createServer(db, {
name: "My Server",
description: "hi",
ownerId,
});
expect(insertCalls).toHaveLength(7);
expect(db.batch).toHaveBeenCalledTimes(1);
expect(db.batch.mock.calls[0][0]).toHaveLength(7);
const serverId = insertCalls[0].values.id;
const publicCategoryId = insertCalls[1].values.id;
const privateCategoryId = insertCalls[3].values.id;
const privateChannelId = insertCalls[4].values.id;
// 1) communityServer
expect(insertCalls[0].table).toBe(communityServer);
expect(insertCalls[0].values).toMatchObject({
name: "My Server",
description: "hi",
ownerId,
});
// 2) public communityCategory
expect(insertCalls[1].table).toBe(communityCategory);
expect(insertCalls[1].values).toMatchObject({
serverId,
name: "Public",
position: 0,
private: 0,
});
// 3) public communityChannel — /all
expect(insertCalls[2].table).toBe(communityChannel);
expect(insertCalls[2].values).toMatchObject({
serverId,
categoryId: publicCategoryId,
name: "all",
type: "text",
position: 0,
});
// 4) private communityCategory
expect(insertCalls[3].table).toBe(communityCategory);
expect(insertCalls[3].values).toMatchObject({
serverId,
name: "Private",
position: 1,
private: 1,
creatorId: ownerId,
});
// 5) private communityChannel — /room
expect(insertCalls[4].table).toBe(communityChannel);
expect(insertCalls[4].values).toMatchObject({
serverId,
categoryId: privateCategoryId,
name: "room",
type: "text",
position: 0,
creatorId: ownerId,
});
// 6) private channel roster includes the owner
expect(insertCalls[5].table).toBe(communityChannelMember);
expect(insertCalls[5].values).toMatchObject({
channelId: privateChannelId,
userId: ownerId,
relation: "access",
source: "added",
addedBy: ownerId,
});
// 7) communityServerMember — exactly one owner row, no rail order
expect(insertCalls[6].table).toBe(communityServerMember);
expect(insertCalls[6].values).toMatchObject({
serverId,
userId: ownerId,
role: "owner",
});
// Member insert uses .returning({ id, userId, joinedAt })
expect(insertCalls[6].returningArg).toBeDefined();
});
it("description defaults to empty string when omitted", async () => {
const { db, insertCalls } = createDbMock({
insertReturns: [[serverRow], [publicCategoryRow], [], [privateCategoryRow], [privateChannelRow], [], [memberRow]],
selectReturns: [[{ name: "Alice" }]],
});
await serverQueries.createServer(db, { name: "My Server", ownerId });
expect(insertCalls[0].values).toMatchObject({ description: "" });
});
it("writes a 4-digit discriminator + an up-front id on the server insert (name#disc handle)", async () => {
const { db, insertCalls } = createDbMock({
insertReturns: [[serverRow], [publicCategoryRow], [], [privateCategoryRow], [privateChannelRow], [], [memberRow]],
selectReturns: [[{ name: "Alice" }]],
});
await serverQueries.createServer(db, { name: "My Server", ownerId });
// The server insert now carries an id (minted up-front so the discriminator
// can hash on it, like createUser) and a computed 4-digit decimal disc. The
// (name, discriminator) uniqueness + salt-retry come from
// withUniqueDiscriminator (unit-tested in user.test.ts) + the real index
// (community-server-discriminator-unique.test.ts).
expect(insertCalls[0].table).toBe(communityServer);
expect(insertCalls[0].values.id).toEqual(expect.any(String));
expect(insertCalls[0].values.discriminator).toMatch(/^\d{4}$/);
});
});
// ────────────────────────────────────────────────────────────────────────────
// listUserServers — unread-mention aggregate → rail badge
// ────────────────────────────────────────────────────────────────────────────
//
// The query now has two select chains:
// 1. Inner subquery over community_mention → community_message →
// community_channel, aliased as `mention_counts`.
// 2. Outer select over community_server INNER JOIN member LEFT JOIN
// mention_counts, projected with a COALESCE(mentions, 0) column.
//
// We can't execute SQL against D1 in unit tests, so these mocks capture the
// second call's `.from(...) → .innerJoin(...) → .leftJoin(...) → .orderBy(...)`
// chain and pin:
// (a) the LEFT JOIN against the subquery is present (badge would silently
// be 0 forever without it),
// (b) the WHERE inside the subquery pins `read = 0` and `kind = 'mention'`
// (regressions here silently over-count or over-drop),
// (c) the final projection includes `mentions`, and callers see the
// aggregated number.
//
// SQL semantics (correct grouping, DM exclusion) are covered by the shape of
// the query itself — the joins force server_id via message → channel, which
// naturally excludes DM message rows (channelId IS NULL).
function createListServersMock(finalRows: unknown[]) {
// Track calls per select chain so tests can assert what tables/joins were
// touched. Two `.select(...)` calls occur: the subquery builder first, then
// the outer select.
const selectChains: Array<{
fields: unknown;
from?: unknown;
innerJoins: unknown[];
leftJoins: unknown[];
where?: unknown;
groupBy?: unknown;
orderBy?: unknown;
aliased?: string | null;
}> = [];
const build = (isSubquery: boolean) => {
const call = selectChains[selectChains.length - 1]!;
const chain: any = {};
chain.from = (t: unknown) => {
call.from = t;
return chain;
};
chain.innerJoin = (table: unknown, cond: unknown) => {
call.innerJoins.push({ table, cond });
return chain;
};
chain.leftJoin = (table: unknown, cond: unknown) => {
call.leftJoins.push({ table, cond });
return chain;
};
chain.where = (cond: unknown) => {
call.where = cond;
return chain;
};
chain.groupBy = (col: unknown) => {
call.groupBy = col;
return chain;
};
chain.as = (name: string) => {
call.aliased = name;
// Subquery result — returned to the outer builder as a "table-like"
// reference. Include a `.serverId` / `.mentions` marker so the outer
// leftJoin condition builder can access it symbolically.
return { __subquery: name, serverId: {}, mentions: {} };
};
chain.orderBy = (col: unknown) => {
call.orderBy = col;
// Terminal — this is the outer builder; return the fixture rows.
return Promise.resolve(finalRows);
};
return chain;
};
const db: any = {
batch: async () => [finalRows.map((row: any) => ({ serverId: row.id })), [], [], []],
select(fields: unknown) {
selectChains.push({
fields,
innerJoins: [],
leftJoins: [],
aliased: null,
});
// The first select is the subquery — it terminates in `.as(...)`, not
// `.orderBy(...)`. The second is the outer builder.
const isSubquery = selectChains.length === 1;
return build(isSubquery);
},
};
return { db, selectChains };
}
describe("listUserServers — mention aggregate for the rail badge", () => {
it("subquery filters on read=0 AND kind='mention' AND matches the viewer", async () => {
const { db, selectChains } = createListServersMock([]);
await serverQueries.listUserServers(db, "u_1");
// First `.select(...)` is the subquery.
const sub = selectChains[0];
expect(sub.from).toBe(communityMention);
// Inner joins from mention → message → channel drive the server_id
// projection AND drop DM mentions (channelId IS NULL). Pin both hops.
expect(sub.innerJoins).toHaveLength(2);
const joinedTables = sub.innerJoins.map((j: any) => j.table);
expect(joinedTables).toContain(communityMessage);
expect(joinedTables).toContain(communityChannel);
expect(sub.leftJoins).toHaveLength(1);
expect(sub.leftJoins[0].table).toBe(communityReadState);
// groupBy on channel.serverId so the row shape is (serverId, count).
expect(sub.groupBy).toBe(communityChannel.serverId);
expect(sub.aliased).toBe("mention_counts");
});
it("outer select LEFT JOINs the mention_counts subquery", async () => {
const { db, selectChains } = createListServersMock([]);
await serverQueries.listUserServers(db, "u_1");
const outer = selectChains[1];
expect(outer.from).toBe(communityServer);
expect(outer.fields.official).toBe(communityServer.official);
// Inner join to member (the "am I in this server" pin) MUST stay — the
// mention aggregate is a badge on the rail, not a public feed.
expect(outer.innerJoins).toHaveLength(1);
expect(outer.innerJoins[0]).toMatchObject({ table: communityServerMember });
// Left join to the subquery is the actual fix — without it `mentions` is
// always undefined and the badge silently reads 0.
expect(outer.leftJoins).toHaveLength(1);
expect(outer.leftJoins[0].table).toMatchObject({ __subquery: "mention_counts" });
});
it("orders newly joined rows deterministically before canonical projection", async () => {
const { db, selectChains } = createListServersMock([]);
await serverQueries.listUserServers(db, "u_1");
const outer = selectChains[1];
expect(outer.orderBy).toBeDefined();
});
it("returns rows with mentions passed through from the resolved LEFT JOIN", async () => {
// Simulates: viewer has 2 unread mentions in srv_A, 1 in srv_B, none in
// srv_C (LEFT JOIN + COALESCE → 0).
const rows = [
{ id: "srv_A", name: "A", mentions: 2 },
{ id: "srv_B", name: "B", mentions: 1 },
{ id: "srv_C", name: "C", mentions: 0 },
];
const { db } = createListServersMock(rows);
const result = await serverQueries.listUserServers(db, "u_1");
expect(result).toHaveLength(3);
// Pin each server's count — including the zero case, which is the
// COALESCE(NULL, 0) path.
expect(result.map((r: any) => [r.id, r.mentions])).toEqual([
["srv_A", 2],
["srv_B", 1],
["srv_C", 0],
]);
});
it("returns mentions: 0 for every server when the viewer has no unread mentions", async () => {
// With zero unread mentions, the subquery yields no rows and every
// LEFT JOIN produces NULL → COALESCE → 0.
const rows = [
{ id: "srv_A", name: "A", mentions: 0 },
{ id: "srv_B", name: "B", mentions: 0 },
];
const { db } = createListServersMock(rows);
const result = await serverQueries.listUserServers(db, "u_1");
expect(result.every((r: any) => r.mentions === 0)).toBe(true);
});
});
describe("listUnreadMentionSources", () => {
it("returns grouped per-channel evidence through the policy joins", async () => {
const rows = [{ serverId: "srv_A", channelId: "ch_1", count: 2, lastSeq: 7 }];
const call: {
from?: unknown;
innerJoins: unknown[];
leftJoins: unknown[];
grouped?: unknown[];
} = { innerJoins: [], leftJoins: [] };
const chain: any = {};
chain.from = (table: unknown) => { call.from = table; return chain; };
chain.innerJoin = (table: unknown, condition: unknown) => {
call.innerJoins.push({ table, condition });
return chain;
};
chain.leftJoin = (table: unknown, condition: unknown) => {
call.leftJoins.push({ table, condition });
return chain;
};
chain.where = () => chain;
chain.groupBy = (...columns: unknown[]) => {
call.grouped = columns;
return Promise.resolve(rows);
};
const db = { select: vi.fn(() => chain) };
await expect(serverQueries.listUnreadMentionSources(db as any, "u_1"))
.resolves.toEqual(rows);
expect(call.from).toBe(communityMention);
expect(call.innerJoins.map((join: any) => join.table)).toEqual([
communityMessage,
communityChannel,
]);
expect(call.leftJoins).toHaveLength(1);
expect((call.leftJoins[0] as any).table).toBe(communityReadState);
expect(call.grouped).toEqual([communityChannel.serverId, communityChannel.id]);
});
});
// The reply/DM/read exclusion semantics live in the subquery's WHERE clause.
// We can't execute a WHERE against a mock, but we CAN pin that the subquery
// is built with the right filter shape — a raw `sql` cast or a missing
// eq(mention.kind, "mention") clause would be a bug we'd catch by reading
// the subquery source. As a low-cost regression signal, assert the source
// text of `listUserServers` includes the three critical predicates.
describe("listUserServers — filter predicates (source-level pin)", () => {
const src = serverQueries.listUserServers.toString();
it("subquery filters mention rows to the viewer (userId equality)", () => {
// The `eq(communityMention.userId, userId)` clause is what pins the
// aggregate to the caller's mentions — never anyone else's.
expect(src).toMatch(/communityMention\.userId/);
});
it("subquery excludes already-read mentions (read = 0)", () => {
// Guards against dropping the `eq(communityMention.read, 0)` predicate,
// which would keep the badge permanently red.
expect(src).toMatch(/communityMention\.read/);
expect(src).toMatch(/,\s*0\s*\)/);
});
it("subquery counts only kind='mention' (not replies)", () => {
// `kind = "reply"` events live in For You but do not warrant a red badge
// on the server icon — the plan pins this explicitly. The literal is now
// sourced from the shared MENTION_KIND constant (#5 consolidation), so the
// filter reads `communityMention.kind, MENTION_KIND.MENTION` rather than a
// bare "mention" string.
expect(src).toMatch(/communityMention\.kind/);
expect(src).toMatch(/MENTION_KIND\.MENTION/);
});
it("gates the rail count by current policy and the aligned read cursor", () => {
expect(src).toMatch(/notificationEligibleSql/);
expect(src).toMatch(/channelReadableSql/);
expect(src).toMatch(/communityMessage\.seq/);
expect(src).toMatch(/communityReadState\.lastReadSeq/);
});
it("joins go through community_message → community_channel so DM mentions never land in the aggregate", () => {
// DM messages have channelId=NULL and would be excluded by INNER JOIN on
// `community_channel.id = message.channelId`. Pin the join topology.
expect(src).toMatch(/communityMessage/);
expect(src).toMatch(/communityChannel/);
});
});
function createResolveServerMock(selectReturns: unknown[][]) {
const selectCalls: Array<{ fields: Record<string, unknown>; where?: unknown }> = [];
let index = 0;
const db: any = {
select(fields: Record<string, unknown>) {
const call: { fields: Record<string, unknown>; where?: unknown } = { fields };
selectCalls.push(call);
const rows = selectReturns[index++] ?? [];
const chain: any = {
from: () => chain,
innerJoin: () => chain,
where(condition: unknown) {
call.where = condition;
return Promise.resolve(rows);
},
};
return chain;
},
};
return { db, selectCalls };
}
describe("resolveServerByNameForMember — handle-only entry", () => {
it("rejects a raw server id without querying", async () => {
const { db, selectCalls } = createResolveServerMock([]);
await expect(serverQueries.resolveServerByNameForMember(db, "u_1", "srv_1")).resolves.toEqual([]);
expect(selectCalls).toHaveLength(0);
});
it("recognizes four-digit and expanded name#discriminator handles", async () => {
for (const handle of ["Studio#0042", "Studio#12345"]) {
const row = { id: "srv_1", name: "Studio", discriminator: handle.split("#")[1] };
const { db, selectCalls } = createResolveServerMock([[row]]);
await expect(serverQueries.resolveServerByNameForMember(db, "u_1", handle)).resolves.toEqual([row]);
expect(selectCalls).toHaveLength(1);
}
});
it("rejects a bare name without probing a name fallback", async () => {
const { db, selectCalls } = createResolveServerMock([]);
await expect(serverQueries.resolveServerByNameForMember(db, "u_1", "Studio")).resolves.toEqual([]);
expect(selectCalls).toHaveLength(0);
});
it("pins member scoping, discriminator parsing, and index-aligned NOCASE lookup in the query source", () => {
const source = serverQueries.resolveServerByNameForMember.toString();
expect(source).toMatch(/communityServerMember\.userId/);
expect(source).toMatch(/parseNameAndTag/);
expect(source).toMatch(/COLLATE NOCASE/);
expect(source).toMatch(/communityServer\.discriminator/);
});
});