Skip to content

armgit / qm

Public Imported from GitHub
0

Multiplayer agent harness for work.

29.2 KB
Raw Blame
import { likeEscape } from "../persistence/like.ts";
import { orgId as configOrgId } from "../config.ts";
import { createHash } from "node:crypto";
import { createPgPool, type PoolClient, withPgTransaction } from "../persistence/pg-pool.ts";
import type { PrincipalType } from "../types.ts";
import { foldPrincipalId, rosterIds } from "./person.ts";
import {
  MAX_CANDIDATES,
  groupParticipantsKey,
  normDirectoryQuery,
  type ChannelMembership,
  type DirectoryChannel,
  type DirectoryMember,
  type DirectoryStore,
} from "./directory-store.ts";

const INITIAL_SCHEMA = [
  `CREATE TABLE IF NOT EXISTS directory_members(
    org_id          TEXT NOT NULL,
    principal_id    TEXT NOT NULL,
    display_name    TEXT NOT NULL,
    display_name_lc TEXT NOT NULL,
    type            TEXT NOT NULL,
    slack_id        TEXT,
    PRIMARY KEY (org_id, principal_id)
  )`,
  `CREATE INDEX IF NOT EXISTS directory_members_name
    ON directory_members (org_id, display_name_lc)`,
  `CREATE INDEX IF NOT EXISTS directory_members_lower_pid
    ON directory_members (org_id, lower(principal_id))`,
  `DO $$
  BEGIN
    IF NOT EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_name = 'directory_members' AND column_name = 'slack_id'
    ) THEN
      ALTER TABLE directory_members ADD COLUMN slack_id TEXT;
    END IF;
  END $$`,
  `CREATE TABLE IF NOT EXISTS directory_channels(
    org_id     TEXT NOT NULL,
    channel_id TEXT NOT NULL,
    name       TEXT NOT NULL,
    name_lc    TEXT NOT NULL,
    is_private BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (org_id, channel_id)
  )`,
  `CREATE INDEX IF NOT EXISTS directory_channels_name
    ON directory_channels (org_id, name_lc)`,
  `CREATE INDEX IF NOT EXISTS directory_channels_lower_cid
    ON directory_channels (org_id, lower(channel_id))`,
  `CREATE TABLE IF NOT EXISTS directory_channel_members(
    org_id       TEXT NOT NULL,
    channel_id   TEXT NOT NULL,
    principal_id TEXT NOT NULL,
    PRIMARY KEY (org_id, channel_id, principal_id)
  )`,
  `CREATE TABLE IF NOT EXISTS directory_group_members(
    org_id       TEXT NOT NULL,
    group_id     TEXT NOT NULL,
    principal_id TEXT NOT NULL,
    PRIMARY KEY (org_id, group_id, principal_id)
  )`,
  `CREATE INDEX IF NOT EXISTS directory_group_members_principal
    ON directory_group_members (org_id, principal_id, group_id)`,
  `CREATE TABLE IF NOT EXISTS directory_sync(
    org_id        TEXT PRIMARY KEY,
    members_hash  TEXT,
    channels_hash TEXT,
    groups_hash   TEXT,
    channel_members_synced BOOLEAN NOT NULL DEFAULT FALSE,
    updated_at    BIGINT NOT NULL
  )`,
  `DO $$
  BEGIN
    IF NOT EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_name = 'directory_sync' AND column_name = 'groups_hash'
    ) THEN
      ALTER TABLE directory_sync ADD COLUMN groups_hash TEXT;
    END IF;
    IF NOT EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_name = 'directory_sync' AND column_name = 'channel_members_synced'
    ) THEN
      ALTER TABLE directory_sync ADD COLUMN channel_members_synced BOOLEAN NOT NULL DEFAULT FALSE;
    END IF;
  END $$`,
  `CREATE TABLE IF NOT EXISTS directory_meta(
    org_id        TEXT PRIMARY KEY,
    workspace_url TEXT,
    updated_at    BIGINT NOT NULL
  )`,
];

const EXTERNAL_ROSTER_SCHEMA = [
  `ALTER TABLE directory_channels ADD COLUMN IF NOT EXISTS is_external BOOLEAN NOT NULL DEFAULT FALSE`,
  `DO $$
  BEGIN
    IF NOT EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_name = 'directory_channels' AND column_name = 'roster_known'
    ) THEN
      ALTER TABLE directory_channels ADD COLUMN roster_known BOOLEAN NOT NULL DEFAULT FALSE;
      UPDATE directory_channels c SET roster_known = TRUE
      FROM directory_sync s WHERE c.org_id = s.org_id AND s.channel_members_synced = TRUE;
    END IF;
  END $$`,
  `CREATE TABLE IF NOT EXISTS directory_groups(
    org_id       TEXT NOT NULL,
    group_id     TEXT NOT NULL,
    roster_known BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (org_id, group_id)
  )`,
  `INSERT INTO directory_groups (org_id, group_id, roster_known)
    SELECT DISTINCT org_id, group_id, TRUE FROM directory_group_members
    ON CONFLICT (org_id, group_id) DO NOTHING`,
];

const SYNC_STAMP_SCHEMA = [
  `ALTER TABLE directory_sync ADD COLUMN IF NOT EXISTS members_synced_at BIGINT`,
  `ALTER TABLE directory_sync ADD COLUMN IF NOT EXISTS channels_synced_at BIGINT`,
  `ALTER TABLE directory_sync ADD COLUMN IF NOT EXISTS groups_synced_at BIGINT`,
];

function memberRow(r: Record<string, unknown>): DirectoryMember {
  return {
    principalId: r.principal_id as string,
    displayName: r.display_name as string,
    type: r.type as PrincipalType,
    ...(r.slack_id ? { slackId: r.slack_id as string } : {}),
    ...(r.avatar_url ? { avatarUrl: r.avatar_url as string } : {}),
  };
}
const MEMBER_COLS = "principal_id, display_name, type, slack_id, avatar_url";
function channelRow(r: Record<string, unknown>): DirectoryChannel {
  return {
    channelId: r.channel_id as string,
    name: r.name as string,
    isPrivate: r.is_private as boolean,
    ...(r.is_external ? { isExternal: true } : {}),
  };
}

function hashRoster(rows: string[]): string {
  return createHash("sha256").update(rows.slice().sort().join("\n")).digest("hex");
}

function dedupPairs<T>(rows: T[], idOf: (r: T) => string, pidOf: (r: T) => string): T[] {
  const seen = new Map<string, Set<string>>();
  const out: T[] = [];
  for (const r of rows) {
    const id = idOf(r);
    const pid = pidOf(r);
    if (!id || !pid) continue;
    let principals = seen.get(id);
    if (!principals) seen.set(id, (principals = new Set()));
    if (!principals.has(pid)) {
      principals.add(pid);
      out.push(r);
    }
  }
  return out;
}
function dedupMemberships(rows: ChannelMembership[]): ChannelMembership[] {
  return dedupPairs(
    rows,
    (m) => m.channelId,
    (m) => m.principalId,
  );
}

export const DIRECTORY_MIGRATIONS = [
  {
    id: "directory/store/0001",
    expectedChecksum: "c47a45848f9d225ff601c9df13dc272349c7df7525e7e2417f33d68be7672cc1",
    statements: INITIAL_SCHEMA,
  },
  { id: "directory/store/0002", statements: SYNC_STAMP_SCHEMA },
  { id: "directory/store/0003", statements: EXTERNAL_ROSTER_SCHEMA },
  {
    id: "directory/store/0004",
    statements: ["ALTER TABLE directory_channels ADD COLUMN IF NOT EXISTS observed_at BIGINT"],
  },
  {
    id: "directory/store/0005",
    statements: ["ALTER TABLE directory_members ADD COLUMN IF NOT EXISTS avatar_url TEXT"],
  },
];

export function createPostgresDirectoryStore(connectionString: string): DirectoryStore {
  const orgId = configOrgId();
  const { q, pool } = createPgPool(connectionString, DIRECTORY_MIGRATIONS);

  async function pick<T>(
    query: string,
    table: string,
    idCol: string,
    labelLcCol: string,
    cols: string,
    map: (r: Record<string, unknown>) => T,
  ): Promise<{ kind: "one"; item: T } | { kind: "ambiguous"; items: T[] } | { kind: "none" }> {
    const qn = normDirectoryQuery(query);
    if (!qn) return { kind: "none" };
    const from = `FROM ${table} WHERE org_id = $1`;

    const byId = await q(`SELECT ${cols} ${from} AND lower(${idCol}) = $2 LIMIT 1`, [orgId, qn]);
    if (byId.length) return { kind: "one", item: map(byId[0]!) };

    const exact = await q(
      `SELECT ${cols} ${from} AND ${labelLcCol} = $2 ORDER BY ${idCol} LIMIT ${MAX_CANDIDATES + 1}`,
      [orgId, qn],
    );
    if (exact.length === 1) return { kind: "one", item: map(exact[0]!) };
    if (exact.length > 1) return { kind: "ambiguous", items: exact.slice(0, MAX_CANDIDATES).map(map) };

    const esc = likeEscape(qn);
    let pool2 = await q(
      `SELECT ${cols} ${from} AND ${labelLcCol} LIKE $2 ESCAPE '\\' ORDER BY ${labelLcCol}, ${idCol} LIMIT ${MAX_CANDIDATES + 1}`,
      [orgId, `${esc}%`],
    );
    if (!pool2.length) {
      pool2 = await q(
        `SELECT ${cols} ${from} AND ${labelLcCol} LIKE $2 ESCAPE '\\' ORDER BY ${labelLcCol}, ${idCol} LIMIT ${MAX_CANDIDATES + 1}`,
        [orgId, `%${esc}%`],
      );
    }
    if (!pool2.length) return { kind: "none" };
    if (pool2.length === 1) return { kind: "one", item: map(pool2[0]!) };
    return { kind: "ambiguous", items: pool2.slice(0, MAX_CANDIDATES).map(map) };
  }

  async function swapIfChanged(
    hashCol: string,
    hash: string,
    syncedAt: number | undefined,
    write: (client: PoolClient) => Promise<boolean | void>,
    partial = false,
  ): Promise<boolean> {
    const syncedAtCol = hashCol.replace(/_hash$/, "_synced_at");
    return withPgTransaction(await pool(), async (client) => {
      await client.query("SELECT pg_advisory_xact_lock(hashtext('directory'), hashtext($1))", [orgId]);
      const sync = await client.query(`SELECT ${hashCol}, ${syncedAtCol} FROM directory_sync WHERE org_id = $1`, [
        orgId,
      ]);
      const row = sync.rows[0];
      if (
        syncedAt !== undefined &&
        row?.[syncedAtCol] != null &&
        (partial ? Number(row[syncedAtCol]) >= syncedAt : Number(row[syncedAtCol]) > syncedAt)
      ) {
        console.warn(
          `[directory] refused stale ${hashCol.replace(/_hash$/, "")} swap for ${orgId}: stamped ${Number(row[syncedAtCol]) - syncedAt}ms behind`,
        );
        return false;
      }
      if (!partial && row?.[hashCol] === hash) {
        if (syncedAt !== undefined) {
          await client.query(
            `UPDATE directory_sync SET ${syncedAtCol} = GREATEST(COALESCE(${syncedAtCol}, 0), $2) WHERE org_id = $1`,
            [orgId, syncedAt],
          );
        }
        return true;
      }
      const complete = await write(client);
      if (partial && complete === false) return false;
      await client.query(
        `INSERT INTO directory_sync (org_id, ${hashCol}, ${syncedAtCol}, updated_at) VALUES ($1, $2, $3, $4)
         ON CONFLICT (org_id) DO UPDATE SET ${hashCol} = EXCLUDED.${hashCol},
           ${syncedAtCol} = COALESCE(EXCLUDED.${syncedAtCol}, directory_sync.${syncedAtCol}),
           updated_at = EXCLUDED.updated_at`,
        [orgId, partial || complete === false ? null : hash, partial ? null : (syncedAt ?? null), Date.now()],
      );
      return true;
    });
  }

  return {
    async replace(members, syncedAt) {
      const byId = new Map<string, DirectoryMember>();
      for (const m of members) if (m.principalId && m.type === "internal") byId.set(m.principalId, m);
      const internal = [...byId.values()];

      const hash = hashRoster(
        internal.map((m) => `${m.principalId}|${m.displayName}|${m.type}|${m.slackId ?? ""}|${m.avatarUrl ?? ""}`),
      );

      return swapIfChanged("members_hash", hash, syncedAt, async (client) => {
        await client.query("DELETE FROM directory_members WHERE org_id = $1", [orgId]);
        if (internal.length) {
          await client.query(
            `INSERT INTO directory_members (org_id, principal_id, display_name, display_name_lc, type, slack_id, avatar_url)
             SELECT $1, * FROM unnest($2::text[], $3::text[], $4::text[], $5::text[], $6::text[], $7::text[])`,
            [
              orgId,
              internal.map((m) => m.principalId),
              internal.map((m) => m.displayName),
              internal.map((m) => normDirectoryQuery(m.displayName)),
              internal.map((m) => m.type),
              internal.map((m) => m.slackId ?? null),
              internal.map((m) => m.avatarUrl ?? null),
            ],
          );
        }
      });
    },

    async replaceChannels(channels, channelMembers, syncedAt, channelRosterIds, revocations = [], partial = false) {
      if (partial && (syncedAt === undefined || !Number.isFinite(syncedAt) || channelMembers === undefined))
        return false;
      const byId = new Map<string, DirectoryChannel>();
      for (const c of channels) if (c.channelId && c.name) byId.set(c.channelId, c);
      let list = [...byId.values()];
      const listedIds = new Set(list.map((channel) => channel.channelId));

      let rosterIds =
        channelMembers === undefined
          ? undefined
          : new Set((channelRosterIds ?? [...listedIds]).filter((channelId) => listedIds.has(channelId)));
      let membershipRows =
        channelMembers === undefined
          ? undefined
          : dedupMemberships(channelMembers).filter((member) => rosterIds!.has(member.channelId));
      let revokedRows = dedupMemberships(revocations).filter((member) => listedIds.has(member.channelId));
      const channelsPart = list.map((c) => `${c.channelId}|${c.name}|${c.isPrivate ? 1 : 0}|${c.isExternal ? 1 : 0}`);
      const membersPart =
        membershipRows === undefined
          ? []
          : [
              ...[...rosterIds!].map((channelId) => `r:${channelId}`),
              ...membershipRows.map((m) => `m:${m.channelId}|${m.principalId}`),
              ...revokedRows.map((m) => `x:${m.channelId}|${m.principalId}`),
            ];
      const hash = hashRoster([...channelsPart, ...membersPart]);

      const applied = await swapIfChanged(
        "channels_hash",
        hash,
        syncedAt,
        async (client) => {
          const protectedRows = await client.query(
            "SELECT channel_id FROM directory_channels WHERE org_id = $1 AND (observed_at > $2 OR ($3 AND observed_at = $2))",
            [orgId, syncedAt ?? null, partial],
          );
          const protectedIds = new Set(protectedRows.rows.map((row) => row.channel_id as string));
          if (partial && list.some((channel) => protectedIds.has(channel.channelId))) return false;
          list = list.filter((channel) => !protectedIds.has(channel.channelId));
          rosterIds = rosterIds && new Set([...rosterIds].filter((id) => !protectedIds.has(id)));
          membershipRows = membershipRows?.filter((member) => !protectedIds.has(member.channelId));
          revokedRows = revokedRows.filter((member) => !protectedIds.has(member.channelId));
          const retainedIds = [...listedIds, ...protectedIds];
          const channelIds = list.map((channel) => channel.channelId);
          if (!partial) {
            await client.query(
              "DELETE FROM directory_channels WHERE org_id = $1 AND NOT (channel_id = ANY($2::text[]))",
              [orgId, retainedIds],
            );
            await client.query(
              "DELETE FROM directory_channel_members WHERE org_id = $1 AND NOT (channel_id = ANY($2::text[]))",
              [orgId, retainedIds],
            );
          }
          if (list.length) {
            await client.query(
              `INSERT INTO directory_channels (org_id, channel_id, name, name_lc, is_private, is_external, roster_known, observed_at)
             SELECT $1, rows.*, $8::bigint FROM unnest($2::text[], $3::text[], $4::text[], $5::boolean[], $6::boolean[], $7::boolean[]) AS rows
             ON CONFLICT (org_id, channel_id) DO UPDATE SET
               name = EXCLUDED.name,
               name_lc = EXCLUDED.name_lc,
               is_private = EXCLUDED.is_private,
               is_external = EXCLUDED.is_external,
               roster_known = directory_channels.roster_known OR EXCLUDED.roster_known,
               observed_at = EXCLUDED.observed_at`,
              [
                orgId,
                channelIds,
                list.map((c) => c.name),
                list.map((c) => normDirectoryQuery(c.name)),
                list.map((c) => !!c.isPrivate),
                list.map((c) => !!c.isExternal),
                list.map((c) => rosterIds?.has(c.channelId) ?? false),
                partial ? syncedAt : null,
              ],
            );
          }
          if (membershipRows !== undefined) {
            await client.query(
              "DELETE FROM directory_channel_members WHERE org_id = $1 AND channel_id = ANY($2::text[])",
              [orgId, [...rosterIds!]],
            );
            if (membershipRows.length) {
              await client.query(
                `INSERT INTO directory_channel_members (org_id, channel_id, principal_id)
               SELECT $1, * FROM unnest($2::text[], $3::text[])`,
                [orgId, membershipRows.map((m) => m.channelId), membershipRows.map((m) => m.principalId)],
              );
            }
          }
          if (revokedRows.length) {
            await client.query(
              `DELETE FROM directory_channel_members member
             USING unnest($2::text[], $3::text[]) AS revoked(channel_id, principal_id)
             WHERE member.org_id = $1 AND member.channel_id = revoked.channel_id
               AND member.principal_id = revoked.principal_id`,
              [orgId, revokedRows.map((m) => m.channelId), revokedRows.map((m) => m.principalId)],
            );
          }
          return partial || protectedIds.size === 0;
        },
        partial,
      );
      if (applied && membershipRows !== undefined) {
        await q("UPDATE directory_sync SET channel_members_synced = TRUE WHERE org_id = $1", [orgId]);
      }
      return applied;
    },

    async setWorkspaceUrl(url) {
      await q(
        `INSERT INTO directory_meta (org_id, workspace_url, updated_at) VALUES ($1, $2, $3)
         ON CONFLICT (org_id) DO UPDATE SET workspace_url = EXCLUDED.workspace_url, updated_at = EXCLUDED.updated_at`,
        [orgId, url, Date.now()],
      );
    },

    async meta() {
      const rows = await q("SELECT workspace_url FROM directory_meta WHERE org_id = $1", [orgId]);
      return { workspaceUrl: (rows[0]?.workspace_url as string | null) ?? null };
    },

    async channelMember(channelId, principalId) {
      const rows = await q(
        `SELECT 1 FROM directory_channel_members member
         JOIN directory_channels channel USING (org_id, channel_id)
         WHERE member.org_id = $1 AND member.channel_id = $2 AND member.principal_id = ANY($3::text[])
           AND NOT (channel.is_private AND channel.is_external) LIMIT 1`,
        [orgId, channelId, rosterIds(principalId)],
      );
      return rows.length > 0;
    },

    async channelMemberIds(channelId) {
      const known = await q("SELECT roster_known FROM directory_channels WHERE org_id = $1 AND channel_id = $2", [
        orgId,
        channelId,
      ]);
      if (known[0]?.roster_known !== true) return undefined;
      const rows = await q(
        "SELECT principal_id FROM directory_channel_members WHERE org_id = $1 AND channel_id = $2 ORDER BY principal_id",
        [orgId, channelId],
      );
      return rows.map((row) => row.principal_id as string);
    },

    async channelMembership(channelId, principalId) {
      const rows = await q(
        `SELECT EXISTS (
           SELECT 1 FROM directory_channel_members
           WHERE org_id = $1 AND channel_id = $2 AND principal_id = ANY($3::text[])
         ) AS member,
         (SELECT is_private FROM directory_channels WHERE org_id = $1 AND channel_id = $2) AS is_private,
         (SELECT roster_known FROM directory_channels WHERE org_id = $1 AND channel_id = $2) AS roster_known`,
        [orgId, channelId, rosterIds(principalId)],
      );
      const member = rows[0]?.member === true;
      if (rows[0]?.roster_known !== true) return undefined;
      return member || rows[0]?.is_private === true ? member : undefined;
    },

    async channelPrivacy(channelId) {
      const rows = await q("SELECT is_private FROM directory_channels WHERE org_id = $1 AND channel_id = $2 LIMIT 1", [
        orgId,
        channelId,
      ]);
      return rows.length > 0 ? (rows[0]!.is_private as boolean) : undefined;
    },

    async replaceGroups(groupMembers, syncedAt, groupIds, groupRosterIds) {
      const allRows = dedupPairs(
        groupMembers,
        (m) => m.groupId,
        (m) => m.principalId,
      );
      const listedIds = [...new Set((groupIds ?? allRows.map((member) => member.groupId)).filter(Boolean))];
      const listed = new Set(listedIds);
      const rosterIds = [...new Set((groupRosterIds ?? listedIds).filter((groupId) => listed.has(groupId)))];
      const rostered = new Set(rosterIds);
      const rows = allRows.filter((member) => rostered.has(member.groupId));
      const hash = hashRoster([
        ...listedIds.map((groupId) => `g:${groupId}`),
        ...rosterIds.map((groupId) => `r:${groupId}`),
        ...rows.map((member) => `m:${member.groupId}|${member.principalId}`),
      ]);

      return swapIfChanged("groups_hash", hash, syncedAt, async (client) => {
        await client.query(
          "DELETE FROM directory_group_members WHERE org_id = $1 AND NOT (group_id = ANY($2::text[]))",
          [orgId, listedIds],
        );
        await client.query("DELETE FROM directory_groups WHERE org_id = $1 AND NOT (group_id = ANY($2::text[]))", [
          orgId,
          listedIds,
        ]);
        if (listedIds.length) {
          await client.query(
            `INSERT INTO directory_groups (org_id, group_id, roster_known)
             SELECT $1, * FROM unnest($2::text[], $3::boolean[])
             ON CONFLICT (org_id, group_id) DO UPDATE SET
               roster_known = directory_groups.roster_known OR EXCLUDED.roster_known`,
            [orgId, listedIds, listedIds.map((groupId) => rostered.has(groupId))],
          );
        }
        await client.query("DELETE FROM directory_group_members WHERE org_id = $1 AND group_id = ANY($2::text[])", [
          orgId,
          rosterIds,
        ]);
        if (rows.length) {
          await client.query(
            `INSERT INTO directory_group_members (org_id, group_id, principal_id)
             SELECT $1, * FROM unnest($2::text[], $3::text[])`,
            [orgId, rows.map((m) => m.groupId), rows.map((m) => m.principalId)],
          );
        }
      });
    },

    async upsertGroup(groupId, principalIds) {
      const ids = [...new Set(principalIds.filter(Boolean))];
      if (!groupId || !ids.length) return;
      await withPgTransaction(await pool(), async (client) => {
        await client.query("SELECT pg_advisory_xact_lock(hashtext('directory'), hashtext($1))", [orgId]);
        await client.query(
          `INSERT INTO directory_groups (org_id, group_id, roster_known) VALUES ($1, $2, TRUE)
           ON CONFLICT (org_id, group_id) DO UPDATE SET roster_known = TRUE`,
          [orgId, groupId],
        );
        await client.query("DELETE FROM directory_group_members WHERE org_id = $1 AND group_id = $2", [orgId, groupId]);
        await client.query(
          `INSERT INTO directory_group_members (org_id, group_id, principal_id)
           SELECT $1, $2, * FROM unnest($3::text[])`,
          [orgId, groupId, ids],
        );
        const all = await client.query("SELECT group_id, principal_id FROM directory_group_members WHERE org_id = $1", [
          orgId,
        ]);
        await client.query(
          `INSERT INTO directory_sync (org_id, groups_hash, groups_synced_at, updated_at) VALUES ($1, $2, $3, $3)
           ON CONFLICT (org_id) DO UPDATE SET groups_hash = EXCLUDED.groups_hash,
             groups_synced_at = GREATEST(COALESCE(directory_sync.groups_synced_at, 0), EXCLUDED.groups_synced_at),
             updated_at = EXCLUDED.updated_at`,
          [orgId, hashRoster(all.rows.map((r) => `${r.group_id}|${r.principal_id}`)), Date.now()],
        );
      });
    },

    async resolveGroupByParticipants(participants) {
      const key = groupParticipantsKey(participants);
      if (!key) return { kind: "none" };
      const rows = await q(
        `SELECT group_id FROM directory_group_members
         WHERE org_id = $1
         GROUP BY group_id
         HAVING string_agg(DISTINCT principal_id, ',' ORDER BY principal_id) = $2
         LIMIT 1`,
        [orgId, key],
      );
      return rows.length ? { kind: "one", groupId: rows[0]!.group_id as string } : { kind: "none" };
    },

    async groupMember(groupId, principalId) {
      const rows = await q(
        "SELECT 1 FROM directory_group_members WHERE org_id = $1 AND group_id = $2 AND principal_id = ANY($3::text[]) LIMIT 1",
        [orgId, groupId, rosterIds(principalId)],
      );
      return rows.length > 0;
    },

    async groupMembership(groupId, principalId) {
      const rows = await q(
        `SELECT EXISTS (
           SELECT 1 FROM directory_group_members
           WHERE org_id = $1 AND group_id = $2 AND principal_id = ANY($3::text[])
         ) AS member,
         EXISTS (
           SELECT 1 FROM directory_groups WHERE org_id = $1 AND group_id = $2
         ) AS listed,
         COALESCE((
           SELECT roster_known FROM directory_groups WHERE org_id = $1 AND group_id = $2
         ), FALSE) AS roster_known,
         EXISTS (
           SELECT 1 FROM directory_sync WHERE org_id = $1 AND groups_hash IS NOT NULL
         ) AS synced`,
        [orgId, groupId, rosterIds(principalId)],
      );
      if (rows[0]?.member === true) return true;
      if (rows[0]?.listed !== true) return rows[0]?.synced === true ? false : undefined;
      return rows[0]?.roster_known === true ? rows[0]?.member === true : undefined;
    },

    async listGroupsFor(principalId) {
      const rows = await q(
        "SELECT DISTINCT group_id FROM directory_group_members WHERE org_id = $1 AND principal_id = ANY($2::text[]) ORDER BY group_id",
        [orgId, rosterIds(principalId)],
      );
      return rows.map((row) => row.group_id as string);
    },

    async conversationMembers(kind, id) {
      const rows =
        kind === "channel"
          ? await q(
              `SELECT channel.roster_known, channel.is_external, roster.principal_id AS roster_principal_id,
                      member.principal_id, member.display_name, member.type, member.slack_id
               FROM directory_channels channel
               LEFT JOIN directory_channel_members roster
                 ON roster.org_id = channel.org_id AND roster.channel_id = channel.channel_id
               LEFT JOIN directory_members member
                 ON member.org_id = roster.org_id AND member.principal_id = roster.principal_id
               WHERE channel.org_id = $1 AND channel.channel_id = $2
               ORDER BY roster.principal_id`,
              [orgId, id],
            )
          : await q(
              `SELECT group_row.roster_known, FALSE AS is_external, roster.principal_id AS roster_principal_id,
                      member.principal_id, member.display_name, member.type, member.slack_id
               FROM directory_groups group_row
               LEFT JOIN directory_group_members roster
                 ON roster.org_id = group_row.org_id AND roster.group_id = group_row.group_id
               LEFT JOIN directory_members member
                 ON member.org_id = roster.org_id AND member.principal_id = roster.principal_id
               WHERE group_row.org_id = $1 AND group_row.group_id = $2
               ORDER BY roster.principal_id`,
              [orgId, id],
            );
      if (!rows.length || rows[0]?.roster_known !== true || rows[0]?.is_external === true) return undefined;
      if (rows.some((row) => !row.roster_principal_id || !row.principal_id)) return undefined;
      return rows.map(memberRow);
    },

    async listChannelsFor(principalId) {
      const rows = await q(
        `SELECT channel_id, name, is_private, is_external FROM directory_channels c
         WHERE org_id = $1 AND (is_private = FALSE OR (is_external = FALSE AND EXISTS (
           SELECT 1 FROM directory_channel_members m
           WHERE m.org_id = c.org_id AND m.channel_id = c.channel_id AND m.principal_id = ANY($2::text[]))))
         ORDER BY name_lc`,
        [orgId, rosterIds(principalId)],
      );
      return rows.map(channelRow);
    },

    async list() {
      const rows = await q(`SELECT ${MEMBER_COLS} FROM directory_members WHERE org_id = $1`, [orgId]);
      return rows.map(memberRow);
    },

    async listChannels() {
      const rows = await q(
        "SELECT channel_id, name, is_private, is_external FROM directory_channels WHERE org_id = $1",
        [orgId],
      );
      return rows.map(channelRow);
    },

    async get(principalId) {
      const key = foldPrincipalId(principalId);
      const rows = await q(
        `SELECT ${MEMBER_COLS} FROM directory_members
         WHERE org_id = $1 AND (principal_id = $2 OR ($3 AND lower(principal_id) = $4))
         ORDER BY (principal_id = $2) DESC LIMIT 1`,
        [orgId, principalId, key.includes("@"), key],
      );
      return rows.length ? memberRow(rows[0]!) : null;
    },

    async getBySlackId(slackId) {
      const rows = await q(
        `SELECT ${MEMBER_COLS} FROM directory_members
         WHERE org_id = $1 AND (slack_id = $2 OR (slack_id IS NULL AND principal_id = $2)) LIMIT 1`,
        [orgId, slackId],
      );
      return rows.length ? memberRow(rows[0]!) : null;
    },

    async resolve(query) {
      const bySlackId = await q(
        `SELECT ${MEMBER_COLS} FROM directory_members WHERE org_id = $1 AND slack_id = $2 LIMIT 1`,
        [orgId, query.trim()],
      );
      if (bySlackId.length) return { kind: "one", member: memberRow(bySlackId[0]!) };
      const m = await pick(query, "directory_members", "principal_id", "display_name_lc", MEMBER_COLS, memberRow);
      if (m.kind === "one") return { kind: "one", member: m.item };
      if (m.kind === "ambiguous") return { kind: "ambiguous", candidates: m.items };
      return { kind: "none" };
    },

    async resolveChannel(query) {
      const m = await pick(
        query,
        "directory_channels",
        "channel_id",
        "name_lc",
        "channel_id, name, is_private, is_external",
        channelRow,
      );
      if (m.kind === "one") return { kind: "one", channel: m.item };
      if (m.kind === "ambiguous") return { kind: "ambiguous", candidates: m.items };
      return { kind: "none" };
    },
  };
}