Skip to content

armgit / alook

Public Imported from GitHub
0

Multi-Agent Collaboration for Team and Friends

21.3 KB
Raw Blame
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();
  });
});