Skip to content

armgit / alook

Public Imported from GitHub
0

Multi-Agent Collaboration for Team and Friends

25.5 KB
Raw Blame
import { afterEach, describe, it, expect, vi } from "vitest";
import Sqlite from "better-sqlite3";
import { drizzle } from "drizzle-orm/d1";
import {
  communityMention,
  communityReadState,
  communityChannelMember,
  communityServerFolderItem,
} from "../../src/db/community-schema";
import * as mentionQueries from "../../src/db/queries/community/mention";
import * as attachmentQueries from "../../src/db/queries/community/attachment";
import * as inboxQueries from "../../src/db/queries/community/inbox";
import * as agentInboxQueries from "../../src/db/queries/community/agent-inbox";
import * as readStateQueries from "../../src/db/queries/community/read-state";
import * as channelQueries from "../../src/db/queries/community/channel";
import * as categoryQueries from "../../src/db/queries/community/category";
import * as memberQueries from "../../src/db/queries/community/member";
import * as reactionQueries from "../../src/db/queries/community/reaction";
import * as dmQueries from "../../src/db/queries/community/dm";
import * as taskQueries from "../../src/db/queries/task";
import * as agentQueries from "../../src/db/queries/agent";
import * as conversationQueries from "../../src/db/queries/conversation";
import * as meetingQueries from "../../src/db/queries/meeting-session";
import * as userQueries from "../../src/db/queries/user";
import * as userProfileQueries from "../../src/db/queries/community/user-profile";
import { D1_MAX_BIND_PARAMS } from "../../src/db/queries/_chunk";

const fakeDb = drizzle({} as never);

// The REAL number of bind params a multi-row insert emits per row. Drizzle binds
// every non-disabled column of every row (including a $defaultFn primary key and
// literal-default columns), so we count params on the actually-built statement
// rather than trusting the "logical" column count. This locks the caps in
// _chunk usage against schema drift — add a column, this test forces a re-cap.
function paramsPerRow(table: Parameters<typeof fakeDb.insert>[0], row: Record<string, unknown>): number {
  const two = fakeDb
    .insert(table)
    .values([row, row])
    .toSQL();
  return two.params.length / 2;
}

describe("real emitted params per row (pins the insert caps)", () => {
  it("communityMention = 5", () => {
    const p = paramsPerRow(communityMention, {
      messageId: "m1",
      userId: "u1",
      kind: "mention",
    });
    expect(p).toBe(5);
  });

  it("communityReadState = 6", () => {
    const p = paramsPerRow(communityReadState, {
      userId: "u1",
      channelId: "c1",
      lastReadAt: "2026-01-01T00:00:00.000Z",
      lastReadMessageId: "m1",
    });
    expect(p).toBe(6);
  });

  it("communityChannelMember = 6", () => {
    const p = paramsPerRow(communityChannelMember, {
      channelId: "c1",
      userId: "u1",
      relation: "notify",
      source: "mention",
    });
    expect(p).toBe(6);
  });

  it("communityServerFolderItem = 3", () => {
    const p = paramsPerRow(communityServerFolderItem, {
      folderId: "f1",
      serverId: "s1",
      position: 0,
    });
    expect(p).toBe(3);
  });
});

// Capturing db that records each insert's row count and resolves .returning().
function makeInsertCapture() {
  const inserts: number[] = [];
  const db: any = {
    insert: vi.fn(() => {
      const builder: any = {};
      builder.values = vi.fn((rows: unknown[]) => {
        inserts.push(rows.length);
        const chainable: any = {
          returning: vi.fn(() =>
            Promise.resolve((rows as Record<string, unknown>[]).map((r, i) => ({ ...r, id: `row-${inserts.length}-${i}` })))
          ),
          onConflictDoNothing: vi.fn(() => Promise.resolve()),
        };
        return chainable;
      });
      return builder;
    }),
  };
  return { db, inserts };
}

describe("createMentions chunking", () => {
  it("100 userIds → 5 insert statements of ≤20 rows, returns all 100", async () => {
    const { db, inserts } = makeInsertCapture();
    const userIds = Array.from({ length: 100 }, (_, i) => `u${i}`);
    const rows = await mentionQueries.createMentions(db, {
      messageId: "m1",
      userIds,
    });
    // 5 params/row → cap 20 → 100/20 = 5 statements.
    expect(inserts).toEqual([20, 20, 20, 20, 20]);
    for (const n of inserts) expect(n * 5).toBeLessThanOrEqual(D1_MAX_BIND_PARAMS);
    expect(rows).toHaveLength(100);
  });

  it("empty userIds → no insert", async () => {
    const { db, inserts } = makeInsertCapture();
    const rows = await mentionQueries.createMentions(db, { messageId: "m1", userIds: [] });
    expect(inserts).toEqual([]);
    expect(rows).toEqual([]);
  });

  it("21 userIds → 2 statements (20 + 1)", async () => {
    const { db, inserts } = makeInsertCapture();
    const userIds = Array.from({ length: 21 }, (_, i) => `u${i}`);
    await mentionQueries.createMentions(db, { messageId: "m1", userIds });
    expect(inserts).toEqual([20, 1]);
  });
});

// Capturing db for a chunked SELECT that ends in .orderBy(). Returns rows by
// call index (chunk order is deterministic: first chunk = ids[0..89], etc.).
function makeSelectCapture(rowsPerCall: unknown[][]) {
  let call = 0;
  const db: any = {
    select: vi.fn(() => {
      const chain: any = {};
      chain.from = vi.fn(() => chain);
      chain.innerJoin = vi.fn(() => chain);
      chain.where = vi.fn(() => chain);
      chain.orderBy = vi.fn(() => Promise.resolve(rowsPerCall[call++] ?? []));
      return chain;
    }),
  };
  return { db, callCount: () => call };
}

describe("listByMessageIds chunking + global re-sort", () => {
  it("150 ids → 2 chunks, rows re-sorted globally by (position, createdAt)", async () => {
    // First chunk returns position 2; second chunk returns position 1. A correct
    // global re-sort must put position 1 first even though its chunk ran second
    // (per-chunk order alone would be wrong).
    const { db, callCount } = makeSelectCapture([
      [{ messageId: "m0", position: 2, createdAt: "2026-01-01T00:00:02.000Z" }],
      [{ messageId: "m100", position: 1, createdAt: "2026-01-01T00:00:01.000Z" }],
    ]);
    const ids = Array.from({ length: 150 }, (_, i) => `m${i}`);
    const rows = await attachmentQueries.listByMessageIds(db, ids);
    expect(callCount()).toBe(2); // 150 ids → 2 chunks (90 + 60)
    expect(rows.map((r: any) => r.position)).toEqual([1, 2]);
  });
});

function makeD1SelectCapture() {
  const statements: Array<{ sql: string; params: unknown[] }> = [];
  const client = {
    prepare(sql: string) {
      return {
        bind(...params: unknown[]) {
          statements.push({ sql, params });
          return { raw: async () => [] };
        },
      };
    },
  };
  return { db: drizzle(client as never), statements };
}

function makeD1Capture(rawResponses: unknown[][][] = []) {
  const statements: Array<{ sql: string; params: unknown[] }> = [];
  const responses = [...rawResponses];
  const client = {
    prepare(sql: string) {
      return {
        bind(...params: unknown[]) {
          statements.push({ sql, params });
          return {
            raw: async () => responses.shift() ?? [],
            all: async () => ({ results: [] }),
            run: async () => ({ success: true, meta: { changes: 0 } }),
          };
        },
      };
    },
  };
  return { db: drizzle(client as never), statements };
}

describe("getUsersByIds bound parameters and result set", () => {
  function rawUser(id: string) {
    return [id, id, `${id}@example.test`, 0, null, 0,
      "2026-01-01T00:00:00.000Z", "2026-01-01T00:00:00.000Z", "1234"];
  }

  function userReadDb(ids: string[]) {
    const sqlite = new Sqlite(":memory:");
    try {
      sqlite.exec(`CREATE TABLE user (
        id TEXT PRIMARY KEY, name TEXT, email TEXT, emailVerified INTEGER,
        image TEXT, avatarVersion INTEGER, createdAt TEXT, updatedAt TEXT, discriminator TEXT
      )`);
      const insert = sqlite.prepare("INSERT INTO user VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
      for (const id of [...ids, "outside-user"]) insert.run(...rawUser(id));
      const statements: Array<{ sql: string; params: unknown[] }> = [];
      const client = {
        prepare(sql: string) {
          return {
            bind(...params: unknown[]) {
              statements.push({ sql, params });
              return { raw: async () => sqlite.prepare(sql).raw(true).all(...params) };
            },
          };
        },
      };
      return { sqlite, db: drizzle(client as never), statements };
    } catch (error) {
      sqlite.close();
      throw error;
    }
  }

  function expectedUsers(ids: string[]) {
    return ids.map(id => ({
      id, name: id, email: `${id}@example.test`, emailVerified: false,
      image: null, avatarVersion: 0, createdAt: "2026-01-01T00:00:00.000Z",
      updatedAt: "2026-01-01T00:00:00.000Z", discriminator: "1234",
    }));
  }

  it.each([
    { count: 100 },
    { count: 101 },
    { count: 201 },
  ])("returns all $count users within each statement's bind budget", async ({ count }) => {
    const ids = Array.from({ length: count }, (_, index) => `user_${index}`);
    const originalIds = [...ids];
    const { sqlite, db, statements } = userReadDb(ids);
    try {
      const rows = await userQueries.getUsersByIds(db as never, ids);
      expect([...rows].sort((a, b) => a.id.localeCompare(b.id)))
        .toEqual(expectedUsers(ids).sort((a, b) => a.id.localeCompare(b.id)));
      expect(statements.flatMap(({ params }) => params).sort()).toEqual([...ids].sort());
      for (const { params } of statements) expect(params.length).toBeLessThanOrEqual(100);
      expect(ids).toEqual(originalIds);
    } finally {
      sqlite.close();
    }
  });

  it("deduplicates IDs across the original batch boundary without mutating input", async () => {
    const uniqueIds = Array.from({ length: 101 }, (_, index) => `user_${index}`);
    const ids = [...uniqueIds.slice(0, 100), uniqueIds[0]!, uniqueIds[100]!, uniqueIds[50]!];
    const originalIds = [...ids];
    const { sqlite, db, statements } = userReadDb(uniqueIds);
    try {
      const rows = await userQueries.getUsersByIds(db as never, ids);
      expect([...rows].sort((a, b) => a.id.localeCompare(b.id)))
        .toEqual(expectedUsers(uniqueIds).sort((a, b) => a.id.localeCompare(b.id)));
      const parameters = statements.flatMap(({ params }) => params);
      expect([...parameters].sort()).toEqual([...uniqueIds].sort());
      expect(new Set(parameters).size).toBe(parameters.length);
      for (const { params } of statements) expect(params.length).toBeLessThanOrEqual(100);
      expect(new Set(rows.map(({ id }) => id)).size).toBe(rows.length);
      expect(ids).toEqual(originalIds);
    } finally {
      sqlite.close();
    }
  });

  it("omits missing IDs across batches and keeps the historical public projection", async () => {
    const ids = Array.from({ length: 101 }, (_, index) => `user_${index}`);
    const { db } = makeD1Capture([
      [rawUser(ids[0]!)], [rawUser(ids[100]!)],
    ]);

    const rows = await userQueries.getUsersByIds(db as never, ids);

    expect(rows.map(({ id }) => id)).toEqual([ids[0], ids[100]]);
    expect(Object.keys(rows[0]!).sort()).toEqual([
      "id", "name", "email", "emailVerified", "image", "avatarVersion",
      "createdAt", "updatedAt", "discriminator",
    ].sort());
  });
});

describe("listEligibleUnreadChannels bound parameters", () => {
  it("95 visible ids use 80-id chunks and keep every statement within D1's limit", async () => {
    const { db, statements } = makeD1SelectCapture();
    const visibleChannelIds = Array.from({ length: 95 }, (_, index) => `channel_${index}`);

    await expect(
      inboxQueries.listEligibleUnreadChannels(db as never, "user_1", visibleChannelIds),
    ).resolves.toEqual([]);

    expect(statements).toHaveLength(2);
    expect(statements.map(({ params }) => params.filter(
      (value) => typeof value === "string" && value.startsWith("channel_"),
    ).length)).toEqual([80, 15]);
    expect(statements.map(({ params }) => params.length)).toEqual([92, 27]);
    for (const { params } of statements) {
      expect(params.length).toBeLessThanOrEqual(D1_MAX_BIND_PARAMS);
    }
  });
});

describe("listUnreadMessagesForAgent bound parameters", () => {
  it("90 allowed channels split at the query's 13-fixed-bind budget", async () => {
    const channelIds = Array.from({ length: 90 }, (_, index) => `channel_${index}`);
    const typeRows = channelIds.map((id) => [id, "text"]);
    const { db, statements } = makeD1Capture([typeRows, [], []]);

    await expect(
      agentInboxQueries.listUnreadMessagesForAgent(db as never, "user_1", {
        max: 2,
        visibleChannelIds: channelIds,
      }),
    ).resolves.toEqual([]);

    expect(statements.map(({ params }) => params.filter(
      (value) => typeof value === "string" && value.startsWith("channel_"),
    ).length)).toEqual([90, 87, 3]);
    expect(statements.map(({ params }) => params.length)).toEqual([90, 100, 16]);
    for (const { params } of statements) {
      expect(params.length).toBeLessThanOrEqual(D1_MAX_BIND_PARAMS);
    }
  });
});

describe("getLatestUnreadMessageForAgent bound parameters", () => {
  it("87 allowed channels fit exactly at the query's 13-fixed-bind budget", async () => {
    const channelIds = Array.from({ length: 87 }, (_, index) => `channel_${index}`);
    const typeRows = channelIds.map((id) => [id, "text"]);
    const { db, statements } = makeD1Capture([typeRows, []]);

    await expect(
      agentInboxQueries.getLatestUnreadMessageForAgent(db as never, "user_1", {
        accessVisibleChannelIds: channelIds,
      }),
    ).resolves.toBeNull();

    expect(statements.map(({ params }) => params.filter(
      (value) => typeof value === "string" && value.startsWith("channel_"),
    ).length)).toEqual([87, 87]);
    expect(statements.map(({ params }) => params.length)).toEqual([87, 100]);
  });

  it("88 allowed channels split into 87 + 1 instead of emitting 101 binds", async () => {
    const channelIds = Array.from({ length: 88 }, (_, index) => `channel_${index}`);
    const typeRows = channelIds.map((id) => [id, "text"]);
    const { db, statements } = makeD1Capture([
      typeRows,
      [["message_older", "2026-09-06T01:00:00.000Z"]],
      [["message_newer", "2026-09-06T02:00:00.000Z"]],
    ]);

    await expect(
      agentInboxQueries.getLatestUnreadMessageForAgent(db as never, "user_1", {
        accessVisibleChannelIds: channelIds,
      }),
    ).resolves.toEqual({ messageId: "message_newer" });

    expect(statements.map(({ params }) => params.filter(
      (value) => typeof value === "string" && value.startsWith("channel_"),
    ).length)).toEqual([88, 87, 1]);
    expect(statements.map(({ params }) => params.length)).toEqual([88, 100, 14]);
    for (const { params } of statements) {
      expect(params.length).toBeLessThanOrEqual(D1_MAX_BIND_PARAMS);
    }
  });
});

describe("mark-all-read revision guard bound parameters", () => {
  function buildGuard(targetCount: number) {
    const targets = Array.from({ length: targetCount }, (_, index) => ({
      channelId: `channel_${index}`,
      targetSeq: index + 1,
    }));
    const condition = readStateQueries.readStateAdvancesAnyTargetCondition(
      fakeDb as never,
      "user_1",
      targets,
    );
    return readStateQueries
      .advanceReadStateRevisionWhenBuilder(fakeDb as never, "user_1", condition)
      .toSQL();
  }

  it("33 and 95 targets keep one constant-size JSON guard below D1's limit", () => {
    const thirtyThree = buildGuard(33);
    const ninetyFive = buildGuard(95);

    expect(thirtyThree.params).toHaveLength(4);
    expect(ninetyFive.params).toHaveLength(4);
    expect(ninetyFive.params.length).toBeLessThanOrEqual(D1_MAX_BIND_PARAMS);
    expect(ninetyFive.sql).toContain("json_each");
    expect(ninetyFive.sql).toMatch(
      /cast\(json_extract\(read_target\.value, '\$\[1\]'\) as integer\)/i,
    );
    const encodedTargets = ninetyFive.params.find(
      (param) => typeof param === "string" && param.startsWith('[["channel_0",'),
    );
    expect(JSON.parse(encodedTargets as string)).toHaveLength(95);
  });
});

describe("high-cardinality query bind matrix", () => {
  const sizes = [33, 90, 100, 125];

  it.each(sizes)("keeps JSON/subquery reads constant at %i ids", async (size) => {
    const ids = Array.from({ length: size }, (_, index) => `id_${index}`);
    const calls: Array<(db: ReturnType<typeof drizzle>) => Promise<unknown>> = [
      (db) => memberQueries.getMemberships(db as never, "user_1", ids),
      (db) => categoryQueries.getCategoriesByIds(db as never, ids),
      (db) => agentQueries.getAgentsByIds(db as never, ids, "workspace_1"),
      (db) => conversationQueries.getConversationsByIds(db as never, ids, "workspace_1"),
      (db) => taskQueries.listPendingTasksByRuntimes(db as never, ids, "workspace_1"),
      (db) => taskQueries.listActiveTaskCountsByWorkspace(db as never, "workspace_1", ids, "user_1"),
      (db) => taskQueries.listActiveTasksByWorkspace(db as never, "workspace_1", ids, "user_1"),
      (db) => taskQueries.getTraceAgentsByTaskIds(db as never, ids, "workspace_1"),
      (db) => meetingQueries.claimMeetingSessions(db as never, ids, "workspace_1", "2026-01-01T00:00:00.000Z"),
      (db) => dmQueries.listDMs(db as never, "user_1"),
      (db) => dmQueries.getDMBetween(db as never, "user_1", "user_2"),
    ];

    for (const call of calls) {
      const { db, statements } = makeD1Capture();
      await call(db);
      expect(statements.length).toBeGreaterThan(0);
      for (const statement of statements) {
        expect(statement.params.length).toBeLessThanOrEqual(D1_MAX_BIND_PARAMS);
      }
      expect(statements.some(({ sql }) => sql.includes("json_each")) || call === calls.at(-2) || call === calls.at(-1)).toBe(true);
    }
  });

  it("uses exact 99-id member chunks and 100-id reaction chunks", async () => {
    const ids = Array.from({ length: 125 }, (_, index) => `id_${index}`);
    const memberCapture = makeD1Capture();
    await memberQueries.getMembersByUserIds(memberCapture.db as never, "server_1", ids);
    expect(memberCapture.statements.map(({ params }) => params.length)).toEqual([100, 27]);

    const reactionCapture = makeD1Capture();
    await reactionQueries.listReactionsByMessageIds(reactionCapture.db as never, ids, "user_1");
    expect(reactionCapture.statements.map(({ params }) => params.length)).toEqual([100, 25]);
  });

  it("uses 100/98 server chunks before the visibility merge", async () => {
    const memberships = Array.from({ length: 125 }, (_, index) => [`server_${index}`]);
    const capture = makeD1Capture([memberships, [], [], [], []]);

    await channelQueries.listVisibleChannelIdsForUser(capture.db as never, "user_1");

    expect(capture.statements.map(({ params }) => params.length)).toEqual([1, 100, 25, 100, 29]);
    for (const { params } of capture.statements) {
      expect(params.length).toBeLessThanOrEqual(D1_MAX_BIND_PARAMS);
    }
  });
});


describe("resource reads through the D1 adapter and local SQLite", () => {
  const connections: Sqlite.Database[] = [];
  const cases = [
    { count: 0, sizes: [] },
    { count: 90, sizes: [90] },
    { count: 91, sizes: [90, 1] },
    { count: 181, sizes: [90, 90, 1] },
  ];

  afterEach(() => {
    for (const sqlite of connections) sqlite.close();
    connections.length = 0;
  });

  function resourceDb() {
    const sqlite = new Sqlite(":memory:");
    connections.push(sqlite);
    sqlite.exec(`
      CREATE TABLE community_channel_member (
        channel_id TEXT, user_id TEXT, relation TEXT, source TEXT,
        PRIMARY KEY (channel_id, user_id, relation)
      );
      CREATE TABLE community_user_profile (
        user_id TEXT PRIMARY KEY, about_me TEXT, banner_color TEXT,
        status_emoji TEXT, status_text TEXT
      );
    `);
    const statements: Array<{ sql: string; params: unknown[] }> = [];
    const client = {
      prepare(sql: string) {
        return {
          bind(...params: unknown[]) {
            statements.push({ sql, params });
            expect(params.length).toBeLessThanOrEqual(D1_MAX_BIND_PARAMS);
            return { raw: async () => sqlite.prepare(sql).raw(true).all(...params) };
          },
        };
      },
    };
    return { sqlite, db: drizzle(client as never), statements };
  }

  it.each(cases)("reads all $count requested channels for only the original viewer", async ({ count, sizes }) => {
    const { sqlite, db, statements } = resourceDb();
    const ids = Array.from({ length: count }, (_, index) => `channel_${index}`);
    const insert = sqlite.prepare("INSERT INTO community_channel_member VALUES (?, ?, ?, ?)");
    for (const id of ids) {
      insert.run(id, "viewer-a", "access", "added");
      insert.run(id, "viewer-b", "access", "added");
    }
    insert.run("outside-channel", "viewer-a", "access", "added");
    if (ids.length) insert.run(ids[0], "viewer-a", "notify", "mention");

    const rows = await channelQueries.listChannelMembershipsForUserInChannels(db as never, "viewer-a", ids);
    const expected = ids.map(channelId => ({ channelId, userId: "viewer-a", relation: "access", source: "added" }));
    if (ids.length) expected.push({ channelId: ids[0]!, userId: "viewer-a", relation: "notify", source: "mention" });
    const ordered = (values: typeof rows) => [...values].sort((a, b) => `${a.channelId}:${a.relation}`.localeCompare(`${b.channelId}:${b.relation}`));

    expect(ordered(rows)).toEqual(ordered(expected));
    expect(statements.map(({ params }) => params.length)).toEqual(sizes.map(size => size + 1));
    expect(statements.flatMap(({ params }) => params.slice(1))).toEqual(ids);
    for (const statement of statements) {
      expect(statement.sql).toContain('"community_channel_member"."user_id" = ?');
      expect(statement.sql).toContain('"community_channel_member"."channel_id" in');
      expect(statement.params[0]).toBe("viewer-a");
    }
    console.info(JSON.stringify({ case: "resource-memberships-local-sqlite", ids: count, binds: statements.map(row => row.params.length), rows: rows.length }));
  });

  it.each(cases)("reads all $count requested public profiles without unrequested IDs", async ({ count }) => {
    const { sqlite, db, statements } = resourceDb();
    const ids = Array.from({ length: count }, (_, index) => `profile_${index}`);
    const originalIds = [...ids];
    const insert = sqlite.prepare("INSERT INTO community_user_profile VALUES (?, ?, ?, ?, ?)");
    for (const id of ids) insert.run(id, `about-${id}`, null, null, `status-${id}`);
    insert.run("outside-profile", "outside", null, null, "outside");

    const rows = await userProfileQueries.getProfilesByUserIds(db as never, ids);

    expect([...rows].sort((a, b) => a.userId.localeCompare(b.userId))).toEqual(ids.map(userId => ({ userId, aboutMe: `about-${userId}`, bannerColor: null, statusEmoji: null, statusText: `status-${userId}` })).sort((a, b) => a.userId.localeCompare(b.userId)));
    if (count === 0) expect(statements).toHaveLength(0);
    expect(statements.flatMap(({ params }) => params).sort()).toEqual([...ids].sort());
    for (const { params } of statements) expect(params.length).toBeLessThanOrEqual(100);
    expect(ids).toEqual(originalIds);
    console.info(JSON.stringify({ case: "resource-profiles-local-sqlite", ids: count, binds: statements.map(row => row.params.length), rows: rows.length }));
  });

  it("deduplicates membership IDs across slices and preserves input and both relations", async () => {
    const { sqlite, db, statements } = resourceDb();
    const ids = Array.from({ length: 91 }, (_, index) => `channel_${index}`);
    const input = [...ids.slice(0, 90), ids[0]!, ids[90]!, ids[50]!];
    const original = [...input];
    const insert = sqlite.prepare("INSERT INTO community_channel_member VALUES (?, ?, ?, ?)");
    for (const id of ids) insert.run(id, "viewer-a", "access", "added");
    insert.run(ids[0], "viewer-a", "notify", "mention");
    insert.run(ids[0], "viewer-b", "access", "added");
    insert.run("outside-channel", "viewer-a", "access", "added");

    const rows = await channelQueries.listChannelMembershipsForUserInChannels(db as never, "viewer-a", input);

    expect(rows).toHaveLength(92);
    expect(new Set(rows.map(row => `${row.channelId}:${row.userId}:${row.relation}`)).size).toBe(92);
    expect(rows.every(row => row.userId === "viewer-a" && ids.includes(row.channelId))).toBe(true);
    expect(rows.filter(row => row.channelId === ids[0]).map(row => row.relation).sort()).toEqual(["access", "notify"]);
    expect(statements.map(({ params }) => params.length)).toEqual([91, 2]);
    expect(statements.flatMap(({ params }) => params.slice(1))).toEqual(ids);
    expect(input).toEqual(original);
  });

  it("deduplicates profile IDs across slices without changing input or nullable facts", async () => {
    const { sqlite, db, statements } = resourceDb();
    const ids = Array.from({ length: 91 }, (_, index) => `profile_${index}`);
    const input = [...ids.slice(0, 90), ids[0]!, ids[90]!, ids[50]!];
    const original = [...input];
    const insert = sqlite.prepare("INSERT INTO community_user_profile VALUES (?, ?, ?, ?, ?)");
    for (const id of ids) insert.run(id, null, null, null, "");
    insert.run("outside-profile", "outside", null, null, "outside");

    const rows = await userProfileQueries.getProfilesByUserIds(db as never, input);

    expect(rows.map(row => row.userId).sort()).toEqual([...ids].sort());
    expect(new Set(rows.map(row => row.userId)).size).toBe(91);
    expect(rows.every(row => row.aboutMe === null && row.bannerColor === null && row.statusEmoji === null && row.statusText === "")).toBe(true);
    const parameters = statements.flatMap(({ params }) => params);
    expect([...parameters].sort()).toEqual([...ids].sort());
    expect(new Set(parameters).size).toBe(parameters.length);
    for (const { params } of statements) expect(params.length).toBeLessThanOrEqual(100);
    expect(input).toEqual(original);
  });
});