Skip to content

Infisical is the open-source platform for securing secrets, certificates, access, and AI Agents.

61.0 KB
Raw Blame
import { Knex } from "knex";

import { TDbClient } from "@app/db";
import {
  AccessScope,
  OrganizationsSchema,
  OrgMembershipRole,
  OrgMembershipStatus,
  ProjectType,
  ProjectVersion,
  RESOURCE_SCOPE,
  TableName,
  TMemberships,
  TMembershipsInsert,
  TMembershipsUpdate,
  TOrganizations,
  TOrganizationsInsert,
  TUserEncryptionKeys
} from "@app/db/schemas";
import { PgSqlLock } from "@app/keystore/keystore";
import { DatabaseError } from "@app/lib/errors";
import { groupBy, sanitizeSqlLikeString, unique } from "@app/lib/fn";
import {
  buildFindFilter,
  ormify,
  selectAllTableCols,
  sqlNestRelationships,
  TFindFilter,
  TFindOpt,
  withTransaction
} from "@app/lib/knex";
import { applyScimComparison, generateKnexQueryFromScim, TScimAttributeResolver } from "@app/lib/knex/scim";
import { OrderByDirection } from "@app/lib/types";

import { ActorType } from "../auth/auth-type";
import { OrgAuthMethod, TOrgWithSubOrgs } from "./org-types";

export type TOrgDALFactory = ReturnType<typeof orgDALFactory>;

interface CountResult {
  count: string;
}

const SCIM_USER_ALIAS = "scim_user_alias";

type TOrgAccessActors = {
  userIds: Set<string>;
  identityIds: Set<string>;
};

type TOrphanedProjectActor = {
  actorUserId: string | null;
  actorIdentityId: string | null;
  scopeOrgId: string;
};

export const ORG_ACCESS_LOCK_ESCALATION_THRESHOLD = 500;

export const orgDALFactory = (db: TDbClient) => {
  const orgOrm = ormify(db, TableName.Organization);

  const findOrganizationsByFilter = async ({
    limit,
    offset,
    searchTerm,
    sortBy
  }: {
    limit: number;
    offset: number;
    searchTerm: string;
    sortBy?: keyof TOrganizations;
  }) => {
    try {
      // Build the subquery for limited organization IDs
      const orgSubquery = db.replicaNode().select("id").from(TableName.Organization);

      if (searchTerm) {
        void orgSubquery.where((qb) => {
          void qb.whereILike(`${TableName.Organization}.name`, `%${sanitizeSqlLikeString(searchTerm)}%`);
        });
      }

      const countQuery = orgSubquery.clone();

      if (sortBy) {
        void orgSubquery.orderBy(sortBy);
      }
      void orgSubquery.limit(limit).offset(offset);

      const buildBaseQuery = (orgIdSubquery: Knex.QueryBuilder) => {
        return db
          .replicaNode()(TableName.Organization)
          .whereNull(`${TableName.Organization}.rootOrgId`)
          .whereIn(`${TableName.Organization}.id`, orgIdSubquery)
          .leftJoin(TableName.Project, `${TableName.Organization}.id`, `${TableName.Project}.orgId`)
          .leftJoin(TableName.Membership, `${TableName.Organization}.id`, `${TableName.Membership}.scopeOrgId`)
          .where(`${TableName.Membership}.scope`, AccessScope.Organization)
          .whereNotNull(`${TableName.Membership}.actorUserId`)
          .leftJoin(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
          .leftJoin(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
          .leftJoin(TableName.Role, `${TableName.MembershipRole}.customRoleId`, `${TableName.Role}.id`)
          .where((qb) => {
            void qb.where(`${TableName.Users}.isGhost`, false).orWhereNull(`${TableName.Users}.id`);
          });
      };

      const [docs, totalResult] = await Promise.all([
        buildBaseQuery(orgSubquery)
          .select(selectAllTableCols(TableName.Organization))
          .select(db.ref("name").withSchema(TableName.Project).as("projectName"))
          .select(db.ref("id").withSchema(TableName.Project).as("projectId"))
          .select(db.ref("slug").withSchema(TableName.Project).as("projectSlug"))
          .select(db.ref("createdAt").withSchema(TableName.Project).as("projectCreatedAt"))
          .select(db.ref("email").withSchema(TableName.Users).as("userEmail"))
          .select(db.ref("username").withSchema(TableName.Users).as("username"))
          .select(db.ref("firstName").withSchema(TableName.Users).as("firstName"))
          .select(db.ref("lastName").withSchema(TableName.Users).as("lastName"))
          .select(db.ref("id").withSchema(TableName.Users).as("userId"))
          .select(db.ref("id").withSchema(TableName.Membership).as("orgMembershipId"))
          .select(db.ref("status").withSchema(TableName.Membership).as("orgMembershipStatus"))
          .select(db.ref("role").withSchema(TableName.MembershipRole).as("orgMembershipRole"))
          .select(db.ref("customRoleId").withSchema(TableName.MembershipRole).as("orgMembershipRoleId"))
          .select(db.ref("name").withSchema(TableName.Role).as("orgMembershipRoleName")),
        buildBaseQuery(countQuery).countDistinct(`${TableName.Organization}.id`, { as: "count" }).first()
      ]);

      const formattedDocs = sqlNestRelationships({
        data: docs,
        key: "id",
        parentMapper: (data) => OrganizationsSchema.parse(data),
        childrenMapper: [
          {
            key: "projectId",
            label: "projects" as const,
            mapper: ({ projectId, projectName, projectSlug, projectCreatedAt }) => ({
              id: projectId,
              name: projectName,
              slug: projectSlug,
              createdAt: projectCreatedAt
            })
          },
          {
            key: "userId",
            label: "members" as const,
            mapper: ({
              userId,
              userEmail,
              username,
              firstName,
              lastName,
              orgMembershipId,
              orgMembershipRole,
              orgMembershipRoleName,
              orgMembershipRoleId,
              orgMembershipStatus
            }) => ({
              user: {
                id: userId,
                email: userEmail,
                username,
                firstName,
                lastName
              },
              status: orgMembershipStatus,
              membershipId: orgMembershipId,
              role: orgMembershipRoleName || orgMembershipRole, // custom role name or pre-defined role name
              roleId: orgMembershipRoleId
            })
          }
        ]
      });

      const total = Number(totalResult?.count || 0);

      return {
        organizations: formattedDocs,
        total
      };
    } catch (error) {
      throw new DatabaseError({ error, name: "Find organizations by filter" });
    }
  };

  const listSubOrganizations = async (dto: {
    actorId: string;
    actorType: ActorType;
    orgId: string;
    isAccessible?: boolean;
    search?: string;
    orderBy?: string;
    orderDirection?: OrderByDirection;
    limit?: number;
    offset?: number;
  }) => {
    try {
      const conn = db.replicaNode();
      const orderBy = dto.orderBy ?? "name";
      const orderDirection = dto.orderDirection ?? "asc";

      const buildBaseQuery = () => {
        if (dto.isAccessible) {
          const subOrgIdsSubquery = conn(TableName.Organization)
            .where(`${TableName.Organization}.rootOrgId`, dto.orgId)
            .select(db.ref("id").withSchema(TableName.Organization));

          const userGroupIdsSubquery = conn(TableName.Groups)
            .join(TableName.UserGroupMembership, `${TableName.UserGroupMembership}.groupId`, `${TableName.Groups}.id`)
            .where(`${TableName.UserGroupMembership}.userId`, dto.actorId)
            .select(db.ref("id").withSchema(TableName.Groups));

          const identityGroupIdsSubquery = conn(TableName.Groups)
            .join(
              TableName.IdentityGroupMembership,
              `${TableName.IdentityGroupMembership}.groupId`,
              `${TableName.Groups}.id`
            )
            .where(`${TableName.IdentityGroupMembership}.identityId`, dto.actorId)
            .select(db.ref("id").withSchema(TableName.Groups));

          const accessibleSubOrgIdsSubquery = conn(TableName.Membership)
            .where(`${TableName.Membership}.scope`, AccessScope.Organization)
            .whereIn(`${TableName.Membership}.scopeOrgId`, subOrgIdsSubquery)
            .andWhere((qb) => {
              if (dto.actorType === ActorType.USER) {
                void qb
                  .where(`${TableName.Membership}.actorUserId`, dto.actorId)
                  .orWhereIn(`${TableName.Membership}.actorGroupId`, userGroupIdsSubquery);
              } else {
                void qb
                  .where(`${TableName.Membership}.actorIdentityId`, dto.actorId)
                  .orWhereIn(`${TableName.Membership}.actorGroupId`, identityGroupIdsSubquery);
              }
            })
            .select(db.ref("scopeOrgId").withSchema(TableName.Membership));

          return conn(TableName.Organization)
            .where(`${TableName.Organization}.rootOrgId`, dto.orgId)
            .whereIn(`${TableName.Organization}.id`, accessibleSubOrgIdsSubquery);
        }

        return conn(TableName.Organization).where(`${TableName.Organization}.rootOrgId`, dto.orgId);
      };

      const baseQuery = buildBaseQuery();
      if (dto.search)
        void baseQuery.whereILike(`${TableName.Organization}.name`, `%${sanitizeSqlLikeString(dto.search)}%`);

      const [totalResult, orgs] = await Promise.all([
        baseQuery.clone().count({ count: "*" }).first(),
        baseQuery
          .clone()
          .select(selectAllTableCols(TableName.Organization))
          .orderBy(`${TableName.Organization}.${orderBy}`, orderDirection)
          .limit(dto.limit ?? 25)
          .offset(dto.offset ?? 0)
      ]);

      return { orgs, totalCount: Number(totalResult?.count ?? 0) };
    } catch (error) {
      throw new DatabaseError({ error, name: "List sub organization" });
    }
  };

  /**
   * Returns all root orgs the actor can see (has membership, direct or via group),
   * each with its sub-organizations. Basic org fields and suborg id/name/slug only.
   */
  const listOrganizationsWithSubOrgs = async (dto: { actorId: string }): Promise<TOrgWithSubOrgs[]> => {
    try {
      const conn = db.replicaNode();

      // Query 1: Get root orgs the user has direct membership in (full org info)
      const rootOrgs = (await conn(TableName.Organization)
        .join(TableName.Membership, (qb) => {
          void qb
            .on(`${TableName.Membership}.scopeOrgId`, "=", db.ref("id").withSchema(TableName.Organization))
            .andOn(`${TableName.Membership}.scope`, db.raw("?", [AccessScope.Organization]))
            .andOn(`${TableName.Membership}.actorUserId`, db.raw("?", [dto.actorId]));
        })
        .whereNull(`${TableName.Organization}.rootOrgId`)
        .leftJoin(TableName.SamlConfig, (qb) => {
          qb.on(`${TableName.SamlConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
            `${TableName.SamlConfig}.isActive`,
            "=",
            db.raw("true")
          );
        })
        .leftJoin(TableName.OidcConfig, (qb) => {
          qb.on(`${TableName.OidcConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
            `${TableName.OidcConfig}.isActive`,
            "=",
            db.raw("true")
          );
        })
        .select(
          selectAllTableCols(TableName.Organization),
          db.ref("createdAt").withSchema(TableName.Membership).as("userJoinedAt"),
          db.raw(`
            CASE
              WHEN ${TableName.SamlConfig}."orgId" IS NOT NULL THEN 'saml'
              WHEN ${TableName.OidcConfig}."orgId" IS NOT NULL THEN 'oidc'
              ELSE ''
            END as "orgAuthMethod"
        `)
        )) as (TOrganizations & { orgAuthMethod: string; userJoinedAt: Date | null })[];

      if (rootOrgs.length === 0) return [];

      const rootOrgIds = rootOrgs.map((o) => o.id);

      // Query 2: Get sub-orgs under those root orgs that the user can access (direct or via group)
      const userGroupIdsSubquery = conn(TableName.Groups)
        .join(TableName.UserGroupMembership, `${TableName.UserGroupMembership}.groupId`, `${TableName.Groups}.id`)
        .where(`${TableName.UserGroupMembership}.userId`, dto.actorId)
        .select(db.ref("id").withSchema(TableName.Groups));

      const subOrgs = await conn(TableName.Organization)
        .join(TableName.Membership, (qb) => {
          void qb
            .on(`${TableName.Membership}.scopeOrgId`, "=", db.ref("id").withSchema(TableName.Organization))
            .andOn(`${TableName.Membership}.scope`, db.raw("?", [AccessScope.Organization]));
        })
        .whereIn(`${TableName.Organization}.rootOrgId`, rootOrgIds)
        .andWhere((qb) => {
          void qb
            .where(`${TableName.Membership}.actorUserId`, dto.actorId)
            .orWhereIn(`${TableName.Membership}.actorGroupId`, userGroupIdsSubquery);
        })
        .select(
          db.ref("id").withSchema(TableName.Organization),
          db.ref("name").withSchema(TableName.Organization),
          db.ref("slug").withSchema(TableName.Organization),
          db.ref("rootOrgId").withSchema(TableName.Organization),
          db.ref("createdAt").withSchema(TableName.Membership).as("userJoinedAt")
        );

      const uniqueSubOrgs = unique(subOrgs, (s) => s.id);
      const subOrgsByRootId = groupBy(uniqueSubOrgs, (s) => s.rootOrgId as string);

      return rootOrgs.map((org) => ({
        ...org,
        userJoinedAt: org.userJoinedAt ?? null,
        subOrganizations: (subOrgsByRootId[org.id] ?? []).map((s) => ({
          id: s.id,
          name: s.name,
          slug: s.slug,
          userJoinedAt: s.userJoinedAt ?? null
        }))
      }));
    } catch (error) {
      throw new DatabaseError({ error, name: "List organizations with sub orgs" });
    }
  };

  const findOrgById = async (orgId: string) => {
    try {
      const org = (await db
        .replicaNode()(TableName.Organization)
        .where({ [`${TableName.Organization}.id` as "id"]: orgId })
        .whereNull(`${TableName.Organization}.rootOrgId`)
        .leftJoin(TableName.SamlConfig, (qb) => {
          qb.on(`${TableName.SamlConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
            `${TableName.SamlConfig}.isActive`,
            "=",
            db.raw("true")
          );
        })
        .leftJoin(TableName.OidcConfig, (qb) => {
          qb.on(`${TableName.OidcConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
            `${TableName.OidcConfig}.isActive`,
            "=",
            db.raw("true")
          );
        })
        .select(selectAllTableCols(TableName.Organization))
        .select(
          db.raw(`
            CASE
              WHEN ${TableName.SamlConfig}."orgId" IS NOT NULL THEN '${OrgAuthMethod.SAML}'
              WHEN ${TableName.OidcConfig}."orgId" IS NOT NULL THEN '${OrgAuthMethod.OIDC}'
              ELSE ''
            END as "orgAuthMethod"
        `)
        )
        .first()) as TOrganizations & { orgAuthMethod?: string };

      return org;
    } catch (error) {
      throw new DatabaseError({ error, name: "Find org by id" });
    }
  };

  const findOrgBySlug = async (orgSlug: string) => {
    try {
      const org = (await db
        .replicaNode()(TableName.Organization)
        .whereNull(`${TableName.Organization}.rootOrgId`)
        .where({ [`${TableName.Organization}.slug` as "slug"]: orgSlug })
        .leftJoin(TableName.SamlConfig, (qb) => {
          qb.on(`${TableName.SamlConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
            `${TableName.SamlConfig}.isActive`,
            "=",
            db.raw("true")
          );
        })
        .leftJoin(TableName.OidcConfig, (qb) => {
          qb.on(`${TableName.OidcConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
            `${TableName.OidcConfig}.isActive`,
            "=",
            db.raw("true")
          );
        })
        .select(selectAllTableCols(TableName.Organization))
        .select(
          db.raw(`
            CASE
              WHEN ${TableName.SamlConfig}."orgId" IS NOT NULL THEN '${OrgAuthMethod.SAML}'
              WHEN ${TableName.OidcConfig}."orgId" IS NOT NULL THEN '${OrgAuthMethod.OIDC}'
              ELSE ''
            END as "orgAuthMethod"
        `)
        )
        .first()) as TOrganizations & { orgAuthMethod?: string };

      return org;
    } catch (error) {
      throw new DatabaseError({ error, name: "Find org by slug" });
    }
  };

  // special query
  const findAllOrgsByUserId = async (
    userId: string
  ): Promise<
    (TOrganizations & { orgAuthMethod: string; userRole: string; userStatus: string; userJoinedAt: Date })[]
  > => {
    try {
      const org = (await db
        .replicaNode()(TableName.Membership)
        .where(`${TableName.Membership}.actorUserId`, userId)
        .where(`${TableName.Membership}.scope`, AccessScope.Organization)
        .whereNotNull(`${TableName.Membership}.actorUserId`)
        .join(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
        .join(TableName.Organization, `${TableName.Membership}.scopeOrgId`, `${TableName.Organization}.id`)
        .whereNull(`${TableName.Organization}.rootOrgId`)
        .leftJoin(TableName.SamlConfig, (qb) => {
          qb.on(`${TableName.SamlConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
            `${TableName.SamlConfig}.isActive`,
            "=",
            db.raw("true")
          );
        })
        .leftJoin(TableName.OidcConfig, (qb) => {
          qb.on(`${TableName.OidcConfig}.orgId`, "=", `${TableName.Organization}.id`).andOn(
            `${TableName.OidcConfig}.isActive`,
            "=",
            db.raw("true")
          );
        })
        .select(selectAllTableCols(TableName.Organization))
        .select(db.ref("role").withSchema(TableName.MembershipRole).as("userRole"))
        .select(db.ref("status").withSchema(TableName.Membership).as("userStatus"))
        .select(db.ref("createdAt").withSchema(TableName.Membership).as("userJoinedAt"))
        .select(
          db.raw(`
            CASE
              WHEN ${TableName.SamlConfig}."orgId" IS NOT NULL THEN 'saml'
              WHEN ${TableName.OidcConfig}."orgId" IS NOT NULL THEN 'oidc'
              ELSE ''
            END as "orgAuthMethod"
        `)
        )) as (TOrganizations & {
        orgAuthMethod: string;
        userRole: string;
        userStatus: string;
        userJoinedAt: Date;
      })[];

      return org;
    } catch (error) {
      throw new DatabaseError({ error, name: "Find all org by user id" });
    }
  };

  // Membership is required so a removed or deactivated creator is not locked out everywhere. A second
  // account with admin can therefore free the slot; accepted, since that costs an invite cycle per org.
  // Takes the transaction, not an optional one, so the read cannot hit a replica and defeat the lock.
  const countJoinedRootOrgsCreatedByUserId = async (userId: string, tx: Knex) => {
    try {
      const count = await tx(TableName.Organization)
        .where(`${TableName.Organization}.createdByUserId`, userId)
        .whereNull(`${TableName.Organization}.rootOrgId`)
        .whereExists((qb) => {
          void qb
            .select(tx.raw("1"))
            .from(TableName.Membership)
            .where(`${TableName.Membership}.actorUserId`, userId)
            .where(`${TableName.Membership}.scope`, AccessScope.Organization)
            .where(`${TableName.Membership}.status`, OrgMembershipStatus.Accepted)
            .where(`${TableName.Membership}.isActive`, true)
            .whereRaw(`"${TableName.Membership}"."scopeOrgId" = "${TableName.Organization}"."id"`);
        })
        .count("*", { as: "count" })
        .first();

      return Number((count as unknown as CountResult)?.count ?? 0);
    } catch (error) {
      throw new DatabaseError({ error, name: "Count joined root orgs created by user id" });
    }
  };

  const findOrgByProjectId = async (projectId: string): Promise<TOrganizations> => {
    try {
      const [org] = await db
        .replicaNode()(TableName.Project)
        .where({ [`${TableName.Project}.id` as "id"]: projectId })
        .join(TableName.Organization, `${TableName.Project}.orgId`, `${TableName.Organization}.id`)
        .select(selectAllTableCols(TableName.Organization));

      return org;
    } catch (error) {
      throw new DatabaseError({ error, name: "Find org by project id" });
    }
  };

  // special query
  const findAllOrgMembers = async (orgId: string) => {
    try {
      const members = await db
        .replicaNode()(TableName.Membership)
        .where(`${TableName.Membership}.scopeOrgId`, orgId)
        .where(`${TableName.Membership}.scope`, AccessScope.Organization)
        .whereNotNull(`${TableName.Membership}.actorUserId`)
        .join(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
        .join(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
        .leftJoin(TableName.Role, `${TableName.MembershipRole}.customRoleId`, `${TableName.Role}.id`)
        .leftJoin<TUserEncryptionKeys>(
          TableName.UserEncryptionKey,
          `${TableName.UserEncryptionKey}.userId`,
          `${TableName.Users}.id`
        )
        .select(
          db.ref("id").withSchema(TableName.Membership),
          db.ref("inviteEmail").withSchema(TableName.Membership),
          db.ref("scopeOrgId").withSchema(TableName.Membership).as("orgId"),
          db.ref("role").withSchema(TableName.MembershipRole),
          db.ref("customRoleId").withSchema(TableName.MembershipRole).as("roleId"),
          db.ref("status").withSchema(TableName.Membership),
          db.ref("isActive").withSchema(TableName.Membership),
          db.ref("lastLoginAuthMethod").withSchema(TableName.Membership),
          db.ref("lastLoginTime").withSchema(TableName.Membership),
          db.ref("email").withSchema(TableName.Users),
          db.ref("isEmailVerified").withSchema(TableName.Users),
          db.ref("username").withSchema(TableName.Users),
          db.ref("firstName").withSchema(TableName.Users),
          db.ref("lastName").withSchema(TableName.Users),
          db.ref("id").withSchema(TableName.Users).as("userId"),
          db.ref("superAdmin").withSchema(TableName.Users),
          db.ref("publicKey").withSchema(TableName.UserEncryptionKey)
        )
        .where({ isGhost: false }) // MAKE SURE USER IS NOT A GHOST USER
        .orderBy("firstName")
        .orderBy("lastName");

      return members.map(
        ({ email, isEmailVerified, username, firstName, lastName, userId, publicKey, superAdmin, ...data }) => ({
          ...data,
          user: { email, isEmailVerified, username, firstName, lastName, id: userId, publicKey, superAdmin }
        })
      );
    } catch (error) {
      throw new DatabaseError({ error, name: "Find all org members" });
    }
  };

  // Users with no direct org row never show up in findAllOrgMembers, so this is the only place they come
  // from. Expired temporary roles are skipped to match isActiveRole. Roles come back permanent first so
  // the one a client shows when it has room for only one is never the one about to expire. The group id
  // breaks name ties: a sub-org can hold a local group and a linked parent group with the same name, and
  // sqlNestRelationships nests each role under the group it last appended, so a group's rows must stay
  // contiguous.
  const findAllOrgGroupUserAccess = async (orgId: string) => {
    try {
      const rows = await db
        .replicaNode()(TableName.Membership)
        .where(`${TableName.Membership}.scopeOrgId`, orgId)
        .where(`${TableName.Membership}.scope`, AccessScope.Organization)
        .whereNotNull(`${TableName.Membership}.actorGroupId`)
        .join(TableName.Groups, `${TableName.Groups}.id`, `${TableName.Membership}.actorGroupId`)
        .join(TableName.UserGroupMembership, `${TableName.UserGroupMembership}.groupId`, `${TableName.Groups}.id`)
        .join(TableName.Users, `${TableName.Users}.id`, `${TableName.UserGroupMembership}.userId`)
        .join(TableName.MembershipRole, `${TableName.MembershipRole}.membershipId`, `${TableName.Membership}.id`)
        .where(`${TableName.Users}.isGhost`, false)
        .where((qb) => {
          void qb
            .where(`${TableName.MembershipRole}.isTemporary`, false)
            .orWhere(`${TableName.MembershipRole}.temporaryAccessEndTime`, ">", new Date());
        })
        .select(
          db.ref("id").withSchema(TableName.Users).as("userId"),
          db.ref("email").withSchema(TableName.Users),
          db.ref("username").withSchema(TableName.Users),
          db.ref("firstName").withSchema(TableName.Users),
          db.ref("lastName").withSchema(TableName.Users),
          db.ref("superAdmin").withSchema(TableName.Users),
          db.ref("id").withSchema(TableName.Groups).as("groupId"),
          db.ref("name").withSchema(TableName.Groups).as("groupName"),
          db.ref("slug").withSchema(TableName.Groups).as("groupSlug"),
          db.ref("id").withSchema(TableName.MembershipRole).as("membershipRoleId"),
          db.ref("role").withSchema(TableName.MembershipRole),
          db.ref("customRoleId").withSchema(TableName.MembershipRole).as("roleId")
        )
        .orderBy([
          { column: `${TableName.Groups}.name` },
          { column: `${TableName.Groups}.id` },
          { column: `${TableName.MembershipRole}.isTemporary` },
          { column: `${TableName.MembershipRole}.temporaryAccessEndTime` },
          { column: `${TableName.MembershipRole}.role` }
        ]);

      return sqlNestRelationships({
        data: rows,
        key: "userId",
        parentMapper: ({ userId, email, username, firstName, lastName, superAdmin }) => ({
          user: { id: userId, email, username, firstName, lastName, superAdmin }
        }),
        childrenMapper: [
          {
            key: "groupId",
            label: "groups" as const,
            mapper: ({ groupId, groupName, groupSlug }) => ({ id: groupId, name: groupName, slug: groupSlug }),
            childrenMapper: [
              {
                key: "membershipRoleId",
                label: "roles" as const,
                mapper: ({ role, roleId }) => ({ role, roleId: roleId ?? null })
              }
            ]
          }
        ]
      });
    } catch (error) {
      throw new DatabaseError({ error, name: "Find all org group user access" });
    }
  };

  // TODO(sub-org): updated this logic later
  const countAllOrgMembers = async (orgId: string) => {
    try {
      const count = await db
        .replicaNode()(TableName.Membership)
        .where(`${TableName.Membership}.scopeOrgId`, orgId)
        .where(`${TableName.Membership}.scope`, AccessScope.Organization)
        .where(`${TableName.Membership}.status`, OrgMembershipStatus.Accepted)
        .whereNotNull(`${TableName.Membership}.actorUserId`)
        .count("*")
        .join(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
        .where({ isGhost: false, [`${TableName.Membership}.isActive` as "isActive"]: true })
        .first();

      return parseInt((count as unknown as CountResult).count || "0", 10);
    } catch (error) {
      throw new DatabaseError({ error, name: "Count all org members" });
    }
  };

  const countSecretManagerProjectMembers = async (orgId: string) => {
    try {
      const projectUsers = db
        .replicaNode()(TableName.Membership)
        .where(`${TableName.Membership}.scope`, AccessScope.Project)
        .where(`${TableName.Membership}.isActive`, true)
        .join(TableName.Project, `${TableName.Membership}.scopeProjectId`, `${TableName.Project}.id`)
        .where(`${TableName.Project}.orgId`, orgId)
        .where(`${TableName.Project}.type`, ProjectType.SecretManager)
        .where(`${TableName.Project}.version`, ProjectVersion.V3)
        .whereNull(`${TableName.Project}.deleteAfter`)
        .leftJoin(
          TableName.UserGroupMembership,
          `${TableName.UserGroupMembership}.groupId`,
          `${TableName.Membership}.actorGroupId`
        )
        .select(
          db.raw(`coalesce(??, ??) as "userId"`, [
            `${TableName.Membership}.actorUserId`,
            `${TableName.UserGroupMembership}.userId`
          ])
        );

      const count = await db
        .replicaNode()(TableName.Users)
        .where(`${TableName.Users}.isGhost`, false)
        .whereIn(`${TableName.Users}.id`, projectUsers)
        .countDistinct<{ count: string }[]>(`${TableName.Users}.id as count`)
        .first();

      return Number(count?.count ?? 0);
    } catch (error) {
      throw new DatabaseError({ error, name: "Count secret manager project members" });
    }
  };

  const findOrgMembersByUsername = async (orgId: string, usernames: string[], tx?: Knex) => {
    try {
      const conn = tx || db.replicaNode();
      const members = await conn(TableName.Membership)
        .where(`${TableName.Membership}.scopeOrgId`, orgId)
        .where(`${TableName.Membership}.scope`, AccessScope.Organization)
        .whereNotNull(`${TableName.Membership}.actorUserId`)
        .join(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
        .join(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
        .leftJoin(TableName.Role, `${TableName.MembershipRole}.customRoleId`, `${TableName.Role}.id`)
        .leftJoin<TUserEncryptionKeys>(
          TableName.UserEncryptionKey,
          `${TableName.UserEncryptionKey}.userId`,
          `${TableName.Users}.id`
        )
        .select(
          conn.ref("id").withSchema(TableName.Membership),
          conn.ref("inviteEmail").withSchema(TableName.Membership),
          conn.ref("scopeOrgId").withSchema(TableName.Membership).as("orgId"),
          db.ref("role").withSchema(TableName.MembershipRole),
          db.ref("customRoleId").withSchema(TableName.MembershipRole).as("roleId"),
          conn.ref("status").withSchema(TableName.Membership),
          conn.ref("username").withSchema(TableName.Users),
          conn.ref("email").withSchema(TableName.Users),
          conn.ref("firstName").withSchema(TableName.Users),
          conn.ref("lastName").withSchema(TableName.Users),
          conn.ref("id").withSchema(TableName.Users).as("userId"),
          conn.ref("publicKey").withSchema(TableName.UserEncryptionKey)
        )
        .where({ isGhost: false })
        .whereIn("username", usernames);
      return members.map(({ email, firstName, lastName, userId, publicKey, ...data }) => ({
        ...data,
        user: { email, firstName, lastName, id: userId, publicKey }
      }));
    } catch (error) {
      throw new DatabaseError({ error, name: "Find all org members" });
    }
  };

  const findOrgMembersByRole = async (orgId: string, role: OrgMembershipRole, tx?: Knex) => {
    try {
      const conn = tx || db.replicaNode();
      const members = await conn(TableName.Membership)
        .where(`${TableName.Membership}.scopeOrgId`, orgId)
        .where(`${TableName.Membership}.scope`, AccessScope.Organization)
        .whereNotNull(`${TableName.Membership}.actorUserId`)
        .join(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
        .where(`${TableName.MembershipRole}.role`, role)
        .join(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
        .leftJoin<TUserEncryptionKeys>(
          TableName.UserEncryptionKey,
          `${TableName.UserEncryptionKey}.userId`,
          `${TableName.Users}.id`
        )
        .select(
          conn.ref("id").withSchema(TableName.Membership),
          conn.ref("inviteEmail").withSchema(TableName.Membership),
          conn.ref("scopeOrgId").withSchema(TableName.Membership).as("orgId"),
          conn.ref("role").withSchema(TableName.MembershipRole),
          conn.ref("customRoleId").withSchema(TableName.MembershipRole).as("roleId"),
          conn.ref("status").withSchema(TableName.Membership),
          conn.ref("username").withSchema(TableName.Users),
          conn.ref("email").withSchema(TableName.Users),
          conn.ref("firstName").withSchema(TableName.Users),
          conn.ref("lastName").withSchema(TableName.Users),
          conn.ref("id").withSchema(TableName.Users).as("userId"),
          conn.ref("publicKey").withSchema(TableName.UserEncryptionKey),
          conn.ref("isActive").withSchema(TableName.Membership)
        )
        .where({ isGhost: false });

      return members.map(({ username, email, firstName, lastName, userId, publicKey, isActive, ...data }) => ({
        ...data,
        user: { username, email, firstName, lastName, id: userId, publicKey, isActive }
      }));
    } catch (error) {
      throw new DatabaseError({ error, name: "Find org members by role" });
    }
  };

  const create = async (dto: TOrganizationsInsert, tx?: Knex) => {
    try {
      // An org created by this code has carried the org-scoped secret value digest on every secret
      // it will ever write, so it is complete by construction and needs no backfill. The column
      // defaults to false so that an org created by an older replica mid-deploy, whose writes carry
      // no digest, is not wrongly marked complete.
      const [organization] = await (tx || db)(TableName.Organization)
        .insert({ orgWideSecretValueTrackingEnabled: true, ...dto })
        .returning("*");
      return organization;
    } catch (error) {
      throw new DatabaseError({ error, name: "Create organization" });
    }
  };

  const deleteById = async (orgId: string, tx?: Knex) => {
    try {
      const [org] = await (tx || db)(TableName.Organization).where({ id: orgId }).delete().returning("*");
      return org;
    } catch (error) {
      throw new DatabaseError({ error, name: "Update organization" });
    }
  };

  const updateById = async (orgId: string, data: Partial<TOrganizations>, tx?: Knex) => {
    try {
      const [org] = await (tx || db)(TableName.Organization)
        .where({ id: orgId })
        .update({ ...data })
        .returning("*");
      return org;
    } catch (error) {
      throw new DatabaseError({ error, name: "Update organization" });
    }
  };

  // MEMBERSHIP OPERATIONS
  // --------------------

  const createMembership = async (data: TMembershipsInsert, tx?: Knex) => {
    try {
      const [membership] = await (tx || db)(TableName.Membership).insert(data).returning("*");
      return membership;
    } catch (error) {
      throw new DatabaseError({ error, name: "Create org membership" });
    }
  };

  const bulkCreateMemberships = async (data: TMembershipsInsert[], tx?: Knex) => {
    try {
      const memberships = await (tx || db)(TableName.Membership).insert(data).returning("*");
      return memberships;
    } catch (error) {
      throw new DatabaseError({ error, name: "Create org memberships" });
    }
  };

  const updateMembershipById = async (id: string, data: TMembershipsUpdate, tx?: Knex) => {
    try {
      const [membership] = await (tx || db)(TableName.Membership).where({ id }).update(data).returning("*");
      return membership;
    } catch (error) {
      throw new DatabaseError({ error, name: "Update org membership" });
    }
  };

  const updateMembership = async (filter: Partial<TMemberships>, data: TMembershipsUpdate, tx?: Knex) => {
    try {
      const membership = await (tx || db)(TableName.Membership).where(filter).update(data).returning("*");
      return membership;
    } catch (error) {
      throw new DatabaseError({ error, name: "Update org memberships" });
    }
  };

  const deleteMembershipById = async (id: string, orgId: string, tx?: Knex) => {
    try {
      const [membership] = await (tx || db)(TableName.Membership)
        .where({ id, scopeOrgId: orgId, scope: AccessScope.Organization })
        .delete()
        .returning("*");
      return membership;
    } catch (error) {
      throw new DatabaseError({ error, name: "Delete org membership" });
    }
  };

  const deleteMembershipsById = async (ids: string[], orgId: string, tx?: Knex) => {
    try {
      const memberships = await (tx || db)(TableName.Membership)
        .where({
          scopeOrgId: orgId,
          scope: AccessScope.Organization
        })
        .whereIn("id", ids)
        .delete()
        .returning("*");
      return memberships;
    } catch (error) {
      throw new DatabaseError({ error, name: "Delete org memberships" });
    }
  };

  const findMembership = async (
    filter: TFindFilter<TMemberships>,
    { offset, limit, sort, tx }: TFindOpt<TMemberships> = {}
  ) => {
    try {
      const query = (tx || db.replicaNode())(TableName.Membership)
        // eslint-disable-next-line
        .where(buildFindFilter(filter))
        .where("scope", AccessScope.Organization)
        .join(TableName.Users, `${TableName.Users}.id`, `${TableName.Membership}.actorUserId`)
        .join(TableName.Organization, `${TableName.Organization}.id`, `${TableName.Membership}.scopeOrgId`)
        .leftJoin(TableName.UserAliases, function joinUserAlias() {
          this.on(`${TableName.UserAliases}.userId`, "=", `${TableName.Membership}.actorUserId`)
            .andOn(`${TableName.UserAliases}.orgId`, "=", `${TableName.Membership}.scopeOrgId`)
            .andOn(`${TableName.UserAliases}.aliasType`, "=", (tx || db).raw("?", ["saml"]));
        })
        .select(
          selectAllTableCols(TableName.Membership),
          db.ref("email").withSchema(TableName.Users),
          db.ref("isEmailVerified").withSchema(TableName.Users),
          db.ref("username").withSchema(TableName.Users),
          db.ref("firstName").withSchema(TableName.Users),
          db.ref("lastName").withSchema(TableName.Users),
          db.ref("scimEnabled").withSchema(TableName.Organization),
          db.ref("externalId").withSchema(TableName.UserAliases)
        )
        .where({ isGhost: false });

      if (limit) void query.limit(limit);
      if (offset) void query.offset(offset);
      if (sort) {
        void query.orderBy(sort.map(([column, order, nulls]) => ({ column: column as string, order, nulls })));
      }
      const res = await query;
      return res;
    } catch (error) {
      throw new DatabaseError({ error, name: "Find one" });
    }
  };

  /**
   * Returns all effective org memberships for an actor (user or identity) in the org: direct
   * membership and any memberships via groups the actor belongs to. Single query
   */
  const findEffectiveOrgMemberships = async (dto: {
    actorType: ActorType;
    actorId: string;
    orgId: string;
    status?: OrgMembershipStatus;
    /** When true, do not filter by status (e.g. to find Invited and update to Accepted) */
    acceptAnyStatus?: boolean;
    tx?: Knex;
  }): Promise<TMemberships[]> => {
    try {
      const conn = dto.tx ?? db.replicaNode();
      const status = dto.status ?? OrgMembershipStatus.Accepted;
      const anyStatus = dto.acceptAnyStatus === true;

      const userGroupIdsSubquery = conn(TableName.Groups)
        .join(TableName.UserGroupMembership, `${TableName.UserGroupMembership}.groupId`, `${TableName.Groups}.id`)
        .where(`${TableName.UserGroupMembership}.userId`, dto.actorId)
        .select(db.ref("id").withSchema(TableName.Groups));

      const identityGroupIdsSubquery = conn(TableName.Groups)
        .join(
          TableName.IdentityGroupMembership,
          `${TableName.IdentityGroupMembership}.groupId`,
          `${TableName.Groups}.id`
        )
        .where(`${TableName.IdentityGroupMembership}.identityId`, dto.actorId)
        .select(db.ref("id").withSchema(TableName.Groups));

      const query = conn(TableName.Membership)
        .where(`${TableName.Membership}.scope`, AccessScope.Organization)
        .where(`${TableName.Membership}.scopeOrgId`, dto.orgId)
        .andWhere((qb) => {
          if (dto.actorType === ActorType.USER) {
            void qb
              .where(`${TableName.Membership}.actorUserId`, dto.actorId)
              .orWhereIn(`${TableName.Membership}.actorGroupId`, userGroupIdsSubquery);
          } else {
            void qb
              .where(`${TableName.Membership}.actorIdentityId`, dto.actorId)
              .orWhereIn(`${TableName.Membership}.actorGroupId`, identityGroupIdsSubquery);
          }
        });

      if (!anyStatus) {
        void query.where((qb) => {
          void qb.where(`${TableName.Membership}.status`, status).orWhereNull(`${TableName.Membership}.status`);
        });
      }

      const rows = await query.select(selectAllTableCols(TableName.Membership));
      return rows as TMemberships[];
    } catch (error) {
      throw new DatabaseError({ error, name: "Find effective org memberships" });
    }
  };

  const findActiveEffectiveOrgMembershipsByUserId = async (userId: string, tx?: Knex): Promise<TMemberships[]> => {
    try {
      const conn = tx ?? db.replicaNode();

      const userGroupIdsSubquery = conn(TableName.UserGroupMembership)
        .where(`${TableName.UserGroupMembership}.userId`, userId)
        .select(db.ref("groupId").withSchema(TableName.UserGroupMembership));

      const rows = await conn(TableName.Membership)
        .where(`${TableName.Membership}.scope`, AccessScope.Organization)
        .where(`${TableName.Membership}.isActive`, true)
        .where((qb) => {
          void qb
            .where(`${TableName.Membership}.actorUserId`, userId)
            .orWhereIn(`${TableName.Membership}.actorGroupId`, userGroupIdsSubquery);
        })
        .where((qb) => {
          void qb
            .where(`${TableName.Membership}.status`, OrgMembershipStatus.Accepted)
            .orWhereNull(`${TableName.Membership}.status`);
        })
        .select(selectAllTableCols(TableName.Membership));
      return rows as TMemberships[];
    } catch (error) {
      throw new DatabaseError({ error, name: "Find active effective org memberships by user id" });
    }
  };

  /**
   * Returns the first effective org membership for an actor (user or identity): direct or via group.
   * Use for access checks and to get a single membership id/role. For all memberships use findEffectiveOrgMemberships.
   */
  const findEffectiveOrgMembership = async (dto: {
    actorType: ActorType;
    actorId: string;
    orgId: string;
    status?: OrgMembershipStatus;
    /** When true, do not filter by status (e.g. to find Invited and update to Accepted) */
    acceptAnyStatus?: boolean;
    tx?: Knex;
  }): Promise<TMemberships | null> => {
    try {
      const conn = dto.tx ?? db.replicaNode();
      const status = dto.status ?? OrgMembershipStatus.Accepted;
      const anyStatus = dto.acceptAnyStatus === true;
      const actorColumn =
        dto.actorType === ActorType.USER
          ? (`${TableName.Membership}.actorUserId` as const)
          : (`${TableName.Membership}.actorIdentityId` as const);

      const directQuery = conn(TableName.Membership)
        .where(`${TableName.Membership}.scope`, AccessScope.Organization)
        .where(`${TableName.Membership}.scopeOrgId`, dto.orgId)
        .where(actorColumn, dto.actorId)
        .select(selectAllTableCols(TableName.Membership))
        .first();

      if (!anyStatus) {
        void directQuery.where((qb) => {
          void qb.where(`${TableName.Membership}.status`, status).orWhereNull(`${TableName.Membership}.status`);
        });
      }

      const direct = (await directQuery) as TMemberships | undefined;
      if (direct) {
        return direct;
      }

      // Direct miss: fall back to group membership.
      const list = await findEffectiveOrgMemberships(dto);
      return list[0] ?? null;
    } catch (error) {
      throw new DatabaseError({ error, name: "Find effective org membership" });
    }
  };

  const $buildScimMembershipQuery = (
    orgId: string,
    scimFilter: string | undefined,
    orgAuthMethod: string | undefined,
    { tx, membershipId }: { tx?: Knex; membershipId?: string } = {}
  ) => {
    const conn = tx || db.replicaNode();
    // Determine alias type from org auth method (default to saml for backwards compatibility)
    const aliasType = orgAuthMethod === OrgAuthMethod.OIDC ? OrgAuthMethod.OIDC : OrgAuthMethod.SAML;

    const resolveColumn = (attrPath: string) => {
      switch (attrPath) {
        case "id":
          return `${TableName.Membership}.id`;
        case "active":
          return `${TableName.Membership}.isActive`;
        case "name.givenName":
          return `${TableName.Users}.firstName`;
        case "name.familyName":
          return `${TableName.Users}.lastName`;
        case "email.value":
          return `${TableName.Users}.email`;
        default:
          return null;
      }
    };

    const resolveFilterAttribute: TScimAttributeResolver = (attrPath) => {
      if (attrPath !== "userName") return resolveColumn(attrPath);
      return (qb, op, value) => {
        const matchingAliases = conn(TableName.UserAliases)
          .select(conn.raw("1"))
          .whereRaw("?? = ??", [`${TableName.UserAliases}.userId`, `${TableName.Membership}.actorUserId`])
          .where(`${TableName.UserAliases}.orgId`, orgId)
          .where(`${TableName.UserAliases}.aliasType`, aliasType);
        if (op === "ne") {
          applyScimComparison(matchingAliases, `${TableName.UserAliases}.externalId`, "eq", value);
          void qb.whereNotExists(matchingAliases);
        } else {
          applyScimComparison(matchingAliases, `${TableName.UserAliases}.externalId`, op, value);
          void qb.whereExists(matchingAliases);
        }
      };
    };

    const resolveDisplayAttribute: TScimAttributeResolver = (attrPath) =>
      attrPath === "userName" ? `${SCIM_USER_ALIAS}.externalId` : resolveColumn(attrPath);

    const query = conn(TableName.Membership)
      .where(`${TableName.Membership}.scopeOrgId`, orgId)
      .where(`${TableName.Membership}.scope`, AccessScope.Organization)
      .whereNotNull(`${TableName.Membership}.actorUserId`)
      .where((qb) => {
        if (membershipId) {
          void qb.where(`${TableName.Membership}.id`, membershipId);
        }
        if (scimFilter) {
          void generateKnexQueryFromScim(qb, scimFilter, resolveFilterAttribute);
        }
      })
      .join(TableName.Users, `${TableName.Users}.id`, `${TableName.Membership}.actorUserId`)
      .join(TableName.Organization, `${TableName.Organization}.id`, `${TableName.Membership}.scopeOrgId`)
      .whereNull(`${TableName.Organization}.rootOrgId`)
      .leftJoin(`${TableName.UserAliases} as ${SCIM_USER_ALIAS}`, function joinScimUserAlias() {
        this.on(`${SCIM_USER_ALIAS}.userId`, "=", `${TableName.Membership}.actorUserId`)
          .andOn(`${SCIM_USER_ALIAS}.orgId`, "=", conn.raw("?", [orgId]))
          .andOn(`${SCIM_USER_ALIAS}.aliasType`, "=", conn.raw("?", [aliasType]));
      })
      .where({ isGhost: false });

    const aliasRowMatchesFilter = scimFilter
      ? conn
          .queryBuilder()
          .select(conn.raw("1"))
          .where((qb) => {
            void generateKnexQueryFromScim(qb, scimFilter, resolveDisplayAttribute);
          })
      : undefined;

    return { query, aliasRowMatchesFilter };
  };

  const findMembershipWithScimFilter = async (
    orgId: string,
    scimFilter: string | undefined,
    orgAuthMethod: string | undefined,
    { offset, limit, sort, tx, membershipId }: TFindOpt<TMemberships> & { membershipId?: string } = {}
  ): Promise<
    (TMemberships & {
      email: string | null;
      isEmailVerified: boolean | null;
      username: string;
      firstName: string | null;
      lastName: string | null;
      scimEnabled: boolean | null;
      defaultMembershipRole: string;
      externalId: string | null;
    })[]
  > => {
    try {
      const conn = tx || db.replicaNode();
      const { query: memberships, aliasRowMatchesFilter } = $buildScimMembershipQuery(
        orgId,
        scimFilter,
        orgAuthMethod,
        {
          tx,
          membershipId
        }
      );

      const dedupedMemberships = memberships
        .distinctOn(`${TableName.Membership}.id`)
        .select(
          selectAllTableCols(TableName.Membership),
          db.ref("email").withSchema(TableName.Users),
          db.ref("isEmailVerified").withSchema(TableName.Users),
          db.ref("username").withSchema(TableName.Users),
          db.ref("firstName").withSchema(TableName.Users),
          db.ref("lastName").withSchema(TableName.Users),
          db.ref("scimEnabled").withSchema(TableName.Organization),
          db.ref("defaultMembershipRole").withSchema(TableName.Organization),
          db.ref("externalId").withSchema(SCIM_USER_ALIAS)
        )
        .orderBy(`${TableName.Membership}.id`);

      if (aliasRowMatchesFilter) {
        void dedupedMemberships.orderByRaw("CASE WHEN EXISTS (?) THEN 0 ELSE 1 END", [aliasRowMatchesFilter]);
      }

      void dedupedMemberships.orderBy(`${SCIM_USER_ALIAS}.createdAt`, "desc", "last").as("scim_membership");

      const query = conn.select("*").from(dedupedMemberships);

      if (sort) {
        void query.orderBy(sort.map(([column, order, nulls]) => ({ column: column as string, order, nulls })));
      } else {
        void query.orderBy("id");
      }
      if (limit) void query.limit(limit);
      if (offset) void query.offset(offset);

      const res = await query;

      // eslint-disable-next-line @typescript-eslint/no-unsafe-return
      return res;
    } catch (error) {
      throw new DatabaseError({ error, name: "Find membership with scim filter" });
    }
  };

  const countMembershipWithScimFilter = async (
    orgId: string,
    scimFilter: string | undefined,
    orgAuthMethod: string | undefined,
    tx?: Knex
  ): Promise<number> => {
    try {
      const doc = await $buildScimMembershipQuery(orgId, scimFilter, orgAuthMethod, { tx })
        .query.countDistinct(`${TableName.Membership}.id as count`)
        .first();

      return Number(doc?.count ?? 0);
    } catch (error) {
      throw new DatabaseError({ error, name: "Count membership with scim filter" });
    }
  };

  const findIdentityOrganization = async (
    identityId: string
  ): Promise<{ id: string; name: string; slug: string; role: string }> => {
    try {
      const org = await db
        .replicaNode()(TableName.Membership)
        .where({ actorIdentityId: identityId })
        .where(`${TableName.Membership}.scope`, AccessScope.Organization)
        .whereNull(`${TableName.Organization}.rootOrgId`)
        .whereNotNull(`${TableName.Membership}.actorIdentityId`)
        .join(TableName.MembershipRole, `${TableName.Membership}.id`, `${TableName.MembershipRole}.membershipId`)
        .join(TableName.Organization, `${TableName.Membership}.scopeOrgId`, `${TableName.Organization}.id`)
        .select(db.ref("id").withSchema(TableName.Organization).as("id"))
        .select(db.ref("name").withSchema(TableName.Organization).as("name"))
        .select(db.ref("slug").withSchema(TableName.Organization).as("slug"))
        .select(db.ref("role").withSchema(TableName.MembershipRole).as("role"));

      return org?.[0];
    } catch (error) {
      throw new DatabaseError({ error, name: "Find identity organization" });
    }
  };

  const findRootOrgDetails = async (orgId: string, tx?: Knex): Promise<TOrganizations | undefined> => {
    try {
      const org = await (tx ?? db.replicaNode())(TableName.Organization)
        .select(selectAllTableCols(TableName.Organization))
        .where(
          "id",
          db(TableName.Organization)
            .select(db.raw(`CASE WHEN "rootOrgId" IS NULL THEN id ELSE "rootOrgId" END`))
            .where("id", orgId)
        )
        .first();

      return org;
    } catch (error) {
      throw new DatabaseError({ error, name: "FindRootOrgDetails" });
    }
  };

  // Actors that still reach the org: a direct org row (any status) or a group holding one. Ignores isActive
  // on purpose, it's for the reconcile consumer, and deactivating someone mustn't reap their projects.
  const findActorsWithOrgAccess = async (
    { orgId, userIds, identityIds }: { orgId: string; userIds: string[]; identityIds: string[] },
    tx?: Knex
  ): Promise<TOrgAccessActors> => {
    try {
      const conn = tx || db.replicaNode();
      const orgRows = () =>
        conn(TableName.Membership)
          .where(`${TableName.Membership}.scope`, AccessScope.Organization)
          .where(`${TableName.Membership}.scopeOrgId`, orgId);

      const reaching = async (
        ids: string[],
        actorColumn: "actorUserId" | "actorIdentityId",
        groupTable: TableName.UserGroupMembership | TableName.IdentityGroupMembership,
        groupActorColumn: "userId" | "identityId"
      ) => {
        if (!ids.length) return new Set<string>();
        const direct = (await orgRows()
          .whereIn(`${TableName.Membership}.${actorColumn}`, ids)
          .select(db.ref(actorColumn).withSchema(TableName.Membership).as("actorId"))) as { actorId: string }[];
        const viaGroup = (await orgRows()
          .join(groupTable, `${groupTable}.groupId`, `${TableName.Membership}.actorGroupId`)
          .whereIn(`${groupTable}.${groupActorColumn}`, ids)
          .select(db.ref(groupActorColumn).withSchema(groupTable).as("actorId"))) as { actorId: string }[];
        return new Set([...direct, ...viaGroup].map((row) => row.actorId));
      };

      return {
        userIds: await reaching(userIds, "actorUserId", TableName.UserGroupMembership, "userId"),
        identityIds: await reaching(identityIds, "actorIdentityId", TableName.IdentityGroupMembership, "identityId")
      };
    } catch (error) {
      throw new DatabaseError({ error, name: "FindActorsWithOrgAccess" });
    }
  };

  // Actors that can be granted access right now, used by every add path: an active direct org row, or no
  // direct row and an active group holding one. A deactivated direct member stays out even with a group
  // path, matching findEffectiveOrgMembership, which resolves the direct row first.
  const findActorsWithActiveOrgAccess = async (
    { orgId, userIds, identityIds }: { orgId: string; userIds: string[]; identityIds: string[] },
    tx?: Knex
  ): Promise<TOrgAccessActors> => {
    try {
      const conn = tx || db.replicaNode();
      const orgRows = () =>
        conn(TableName.Membership)
          .where(`${TableName.Membership}.scope`, AccessScope.Organization)
          .where(`${TableName.Membership}.scopeOrgId`, orgId);

      const active = async (
        ids: string[],
        actorColumn: "actorUserId" | "actorIdentityId",
        groupTable: TableName.UserGroupMembership | TableName.IdentityGroupMembership,
        groupActorColumn: "userId" | "identityId"
      ) => {
        if (!ids.length) return new Set<string>();
        const direct = (await orgRows()
          .whereIn(`${TableName.Membership}.${actorColumn}`, ids)
          .select(
            db.ref(actorColumn).withSchema(TableName.Membership).as("actorId"),
            db.ref("isActive").withSchema(TableName.Membership)
          )) as { actorId: string; isActive: boolean }[];
        const withDirectRow = new Set(direct.map((row) => row.actorId));
        const viaGroup = (await orgRows()
          .join(groupTable, `${groupTable}.groupId`, `${TableName.Membership}.actorGroupId`)
          .whereIn(`${groupTable}.${groupActorColumn}`, ids)
          .where(`${TableName.Membership}.isActive`, true)
          .select(db.ref(groupActorColumn).withSchema(groupTable).as("actorId"))) as { actorId: string }[];

        return new Set([
          ...direct.filter((row) => row.isActive).map((row) => row.actorId),
          ...viaGroup.map((row) => row.actorId).filter((actorId) => !withDirectRow.has(actorId))
        ]);
      };

      return {
        userIds: await active(userIds, "actorUserId", TableName.UserGroupMembership, "userId"),
        identityIds: await active(identityIds, "actorIdentityId", TableName.IdentityGroupMembership, "identityId")
      };
    } catch (error) {
      throw new DatabaseError({ error, name: "FindActorsWithActiveOrgAccess" });
    }
  };

  // (actor, org) pairs with a project or resource row in an org they no longer reach. Resource rows count on
  // their own (a group-derived PAM admin's folder row has no project row). Skips ghost users, they never
  // have an org row. One streaming pass against a hash of org rows; collapsing to pairs first was measured
  // and just moved the cost into an aggregate. Runs in its own replica tx so `statementTimeoutMs` can
  // bound it: an overrunning backstop should fail, not keep the replica busy.
  const findOrphanedProjectActors = async ({
    limit,
    statementTimeoutMs
  }: {
    limit: number;
    statementTimeoutMs?: number;
  }) => {
    const m = TableName.Membership;

    const query = async (conn: Knex): Promise<TOrphanedProjectActor[]> => {
      const noDirectOrgRow = (actorColumn: "actorUserId" | "actorIdentityId") =>
        conn
          .select(conn.raw("1"))
          .from(`${m} as direct`)
          .where("direct.scope", AccessScope.Organization)
          .whereRaw("?? = ??", [`direct.${actorColumn}`, `${m}.${actorColumn}`])
          .whereRaw("?? = ??", ["direct.scopeOrgId", `${m}.scopeOrgId`]);

      const noGroupOrgRow = (
        groupTable: TableName.UserGroupMembership | TableName.IdentityGroupMembership,
        groupActorColumn: "userId" | "identityId",
        actorColumn: "actorUserId" | "actorIdentityId"
      ) =>
        conn
          .select(conn.raw("1"))
          .from(groupTable)
          .join(`${m} as viaGroup`, "viaGroup.actorGroupId", `${groupTable}.groupId`)
          .where("viaGroup.scope", AccessScope.Organization)
          .whereRaw("?? = ??", [`${groupTable}.${groupActorColumn}`, `${m}.${actorColumn}`])
          .whereRaw("?? = ??", ["viaGroup.scopeOrgId", `${m}.scopeOrgId`]);

      const userRows = (await conn(m)
        .join(TableName.Users, `${TableName.Users}.id`, `${m}.actorUserId`)
        .whereIn(`${m}.scope`, [AccessScope.Project, RESOURCE_SCOPE])
        .where(`${TableName.Users}.isGhost`, false)
        .whereNotExists(noDirectOrgRow("actorUserId"))
        .whereNotExists(noGroupOrgRow(TableName.UserGroupMembership, "userId", "actorUserId"))
        .distinct(db.ref("actorUserId").withSchema(m), db.ref("scopeOrgId").withSchema(m))
        .limit(limit)) as { actorUserId: string; scopeOrgId: string }[];

      const identityRows = (await conn(m)
        .whereIn(`${m}.scope`, [AccessScope.Project, RESOURCE_SCOPE])
        .whereNotNull(`${m}.actorIdentityId`)
        .whereNotExists(noDirectOrgRow("actorIdentityId"))
        .whereNotExists(noGroupOrgRow(TableName.IdentityGroupMembership, "identityId", "actorIdentityId"))
        .distinct(db.ref("actorIdentityId").withSchema(m), db.ref("scopeOrgId").withSchema(m))
        .limit(limit)) as { actorIdentityId: string; scopeOrgId: string }[];

      return [
        ...userRows.map((row) => ({ actorUserId: row.actorUserId, actorIdentityId: null, scopeOrgId: row.scopeOrgId })),
        ...identityRows.map((row) => ({
          actorUserId: null,
          actorIdentityId: row.actorIdentityId,
          scopeOrgId: row.scopeOrgId
        }))
      ];
    };

    try {
      return await db.replicaNode().transaction(async (trx) => {
        if (statementTimeoutMs) await trx.raw(`SET LOCAL statement_timeout = ${Math.floor(statementTimeoutMs)}`);
        return query(trx);
      });
    } catch (error) {
      throw new DatabaseError({ error, name: "FindOrphanedProjectActors" });
    }
  };

  // Serializes grants with the reconcile consumer. Two levels, like an engine escalating row locks to a table
  // lock: a small set takes the tenant shared plus one exclusive key per actor, a set past the threshold takes
  // the tenant exclusive and nothing per actor. Every key is a slot in Postgres's shared lock table (about
  // 6,400 on default settings), so a SCIM sync adding thousands would otherwise fill it and fail unrelated
  // transactions instance-wide; escalated, it only stalls grants in its own tenant. The tenant is the root org
  // because access never crosses one: a root-org group reaches its sub-orgs, nothing reaches another tenant.
  // Tenant first, then sorted actors in one statement, so two callers can't deadlock.
  const lockActorsForOrgAccess = async ({ orgId, actorIds }: { orgId: string; actorIds: string[] }, tx: Knex) => {
    const actorKeys = [...new Set(actorIds)].sort().map((actorId) => PgSqlLock.OrgAccessReconcile(actorId));
    if (!actorKeys.length) return;
    try {
      const org = await tx(TableName.Organization).where({ id: orgId }).select("rootOrgId").first();
      const scopeKey = PgSqlLock.OrgAccessReconcileScope(org?.rootOrgId ?? orgId);
      if (actorKeys.length > ORG_ACCESS_LOCK_ESCALATION_THRESHOLD) {
        await tx.raw("SELECT pg_advisory_xact_lock(?)", [scopeKey]);
        return;
      }
      await tx.raw("SELECT pg_advisory_xact_lock_shared(?)", [scopeKey]);
      await tx.raw("SELECT pg_advisory_xact_lock(k) FROM unnest(?::int[]) AS k", [actorKeys]);
    } catch (error) {
      throw new DatabaseError({ error, name: "LockActorsForOrgAccess" });
    }
  };

  return withTransaction(db, {
    ...orgOrm,
    findOrgByProjectId,
    findAllOrgMembers,
    findAllOrgGroupUserAccess,
    countAllOrgMembers,
    countSecretManagerProjectMembers,
    listSubOrganizations,
    listOrganizationsWithSubOrgs,
    findOrgById,
    findOrgBySlug,
    findAllOrgsByUserId,
    countJoinedRootOrgsCreatedByUserId,
    findOrganizationsByFilter,
    findOrgMembersByUsername,
    findOrgMembersByRole,
    create,
    updateById,
    deleteById,
    findMembership,
    findEffectiveOrgMembership,
    findEffectiveOrgMemberships,
    findActiveEffectiveOrgMembershipsByUserId,
    findActorsWithOrgAccess,
    findActorsWithActiveOrgAccess,
    findOrphanedProjectActors,
    lockActorsForOrgAccess,
    findMembershipWithScimFilter,
    countMembershipWithScimFilter,
    createMembership,
    bulkCreateMemberships,
    updateMembershipById,
    deleteMembershipById,
    deleteMembershipsById,
    updateMembership,
    findIdentityOrganization,
    findRootOrgDetails,
    primaryNode: () => db.primaryNode()
  });
};