Skip to content

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

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

import { TDbClient } from "@app/db";
import {
  AccessScope,
  ProjectsSchema,
  ProjectType,
  ProjectUpgradeStatus,
  ProjectVersion,
  SortDirection,
  TableName,
  TMemberships,
  TProjects,
  TProjectsUpdate
} from "@app/db/schemas";
import { BadRequestError, DatabaseError, NotFoundError, UnauthorizedError } from "@app/lib/errors";
import { sanitizeSqlLikeString } from "@app/lib/fn";
import {
  buildFindFilter,
  ormify,
  selectAllTableCols,
  sqlNestRelationships,
  TFindFilter,
  TFindOpt
} from "@app/lib/knex";

import { ActorType } from "../auth/auth-type";
import {
  hardDeleteApprovalSecretLinksInBatches,
  hardDeleteSecretReferencesInBatches,
  hardDeleteSecretsInBatches,
  hardDeleteSecretVersionsInBatches,
  projectFolderIdsSubquery
} from "../secret-v2-bridge/secret-tree-hard-delete-fns";
import { Filter, ProjectFilterType, SearchProjectSortBy } from "./project-types";

type TDeletedByRow = {
  deletedByUserId: string | null;
  deletedByIdentityId: string | null;
  deletedByUserEmail: string | null;
  deletedByUserUsername: string | null;
  deletedByUserFirstName: string | null;
  deletedByUserLastName: string | null;
  deletedByIdentityName: string | null;
};

const toDeletedBy = (row: TDeletedByRow) => {
  if (row.deletedByUserId) {
    return {
      type: "user" as const,
      id: row.deletedByUserId,
      email: row.deletedByUserEmail,
      username: row.deletedByUserUsername,
      firstName: row.deletedByUserFirstName,
      lastName: row.deletedByUserLastName
    };
  }
  if (row.deletedByIdentityId) {
    return { type: "identity" as const, id: row.deletedByIdentityId, name: row.deletedByIdentityName ?? "" };
  }
  return null;
};

export type TProjectDALFactory = ReturnType<typeof projectDALFactory>;

export const projectDALFactory = (db: TDbClient) => {
  const projectOrm = ormify(db, TableName.Project);

  // Soft-deleted projects (deleteAfter set) must be hidden from every read path. We override the
  // ormify base reads so indirect callers can't accidentally surface a pending-deletion project,
  // and expose explicit *IncludingExpired escape hatches for the restore + hard-delete worker.
  const findById: typeof projectOrm.findById = async (id, tx) => {
    try {
      const result = await (tx || db.replicaNode())(TableName.Project)
        .where({ id })
        .whereNull("deleteAfter")
        .first("*");
      return result;
    } catch (error) {
      throw new DatabaseError({ error, name: "Find by id" });
    }
  };

  const findOne: typeof projectOrm.findOne = async (filter, tx) => {
    try {
      const res = await (tx || db.replicaNode())(TableName.Project).where(filter).whereNull("deleteAfter").first("*");
      return res;
    } catch (error) {
      throw new DatabaseError({ error, name: "Find one" });
    }
  };

  const find = (async (
    filter: TFindFilter<TProjects>,
    { offset, limit, sort, count, tx, countDistinct }: TFindOpt<TProjects, boolean, keyof TProjects | undefined> = {}
  ) => {
    try {
      const query = (tx || db.replicaNode())(TableName.Project)
        // eslint-disable-next-line @typescript-eslint/no-misused-promises
        .where(buildFindFilter(filter))
        .whereNull("deleteAfter");
      if (countDistinct) {
        void query.countDistinct(countDistinct);
      } else if (count) {
        void query.select(db.raw("COUNT(*) OVER() AS count"));
        void query.select("*");
      }
      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 as TProjects[];
    } catch (error) {
      throw new DatabaseError({ error, name: "Find" });
    }
  }) as typeof projectOrm.find;

  // Bypasses the soft-delete read filter — hard-delete worker only. Primary-backed by default
  // (NOT the replica): staleness here is dangerous (a just-restored/already-reaped project could
  // look wrong), so we read the primary unless the caller explicitly threads a transaction.
  const findByIdIncludingExpired = async (id: string, tx?: Knex) => {
    try {
      const result = await (tx || db)(TableName.Project).where({ id }).first("*");
      return result as TProjects | undefined;
    } catch (error) {
      throw new DatabaseError({ error, name: "Find by id including expired" });
    }
  };

  // Raw find that includes soft-deleted rows, primary-backed by default (same staleness concern as
  // above). Used by the hard-delete worker's KMS shared-key check so a *second* pending-deletion
  // project sharing a key still counts as a referencer.
  const findIncludingExpired: typeof projectOrm.find = ((filter, opts) =>
    projectOrm.find(filter, { ...(opts ?? {}), tx: opts?.tx ?? db })) as typeof projectOrm.find;

  const softDeleteById = async (
    id: string,
    update: {
      deleteAfter: Date;
      softDeletedAt: Date;
      deletedByActorType: string | null;
      deletedByActorId: string | null;
      slug: string;
    },
    tx?: Knex
  ) => {
    try {
      const [doc] = await (tx || db)(TableName.Project)
        .where({ id })
        .whereNull("deleteAfter")
        .update(update)
        .returning("*");
      return doc as TProjects | undefined;
    } catch (error) {
      throw new DatabaseError({ error, name: "Soft delete project" });
    }
  };

  const expireSoftDeletedById = async (id: string, slug: string, tx?: Knex) => {
    try {
      const [doc] = await (tx || db)(TableName.Project)
        .where({ id })
        .whereNotNull("deleteAfter")
        .update({ deleteAfter: new Date(), slug })
        .returning("*");
      return doc as TProjects | undefined;
    } catch (error) {
      throw new DatabaseError({ error, name: "Expire soft-deleted project" });
    }
  };

  const restoreById = async (id: string, tx?: Knex) => {
    try {
      const [doc] = await (tx || db)(TableName.Project)
        .where({ id })
        .whereNotNull("deleteAfter")
        .andWhere("deleteAfter", ">", new Date())
        .update({
          deleteAfter: null,
          softDeletedAt: null,
          deletedByActorType: null,
          deletedByActorId: null
        })
        .returning("*");
      return doc as TProjects | undefined;
    } catch (error) {
      throw new DatabaseError({ error, name: "Restore project" });
    }
  };

  const findExpiredForHardDelete = async (limit: number, tx?: Knex) => {
    try {
      const rows = await (tx || db.replicaNode())(TableName.Project)
        .whereNotNull("deleteAfter")
        .andWhere("deleteAfter", "<=", new Date())
        .orderBy("deleteAfter", "asc")
        .limit(limit)
        .select("*");
      return rows as TProjects[];
    } catch (error) {
      throw new DatabaseError({ error, name: "Find expired projects for hard delete" });
    }
  };

  const pruneOpts = (
    batchSize: number,
    statementTimeoutMs: number,
    interBatchSleepMs: number,
    onBatchCommitted?: (deleted: number) => void
  ) => ({
    batchSize,
    statementTimeoutMs,
    interBatchSleepMs,
    onBatchCommitted
  });

  const hardDeleteProjectSecretVersionsInBatches = (
    projectId: string,
    batchSize: number,
    statementTimeoutMs: number,
    interBatchSleepMs: number,
    onBatchCommitted?: (deleted: number) => void
  ) =>
    hardDeleteSecretVersionsInBatches(
      db,
      projectFolderIdsSubquery(projectId),
      pruneOpts(batchSize, statementTimeoutMs, interBatchSleepMs, onBatchCommitted)
    );

  const hardDeleteProjectSecretReferencesInBatches = (
    projectId: string,
    batchSize: number,
    statementTimeoutMs: number,
    interBatchSleepMs: number,
    onBatchCommitted?: (deleted: number) => void
  ) =>
    hardDeleteSecretReferencesInBatches(
      db,
      projectFolderIdsSubquery(projectId),
      pruneOpts(batchSize, statementTimeoutMs, interBatchSleepMs, onBatchCommitted)
    );

  const hardDeleteProjectApprovalSecretLinksInBatches = (
    projectId: string,
    batchSize: number,
    statementTimeoutMs: number,
    interBatchSleepMs: number,
    onBatchCommitted?: (deleted: number) => void
  ) =>
    hardDeleteApprovalSecretLinksInBatches(
      db,
      projectFolderIdsSubquery(projectId),
      pruneOpts(batchSize, statementTimeoutMs, interBatchSleepMs, onBatchCommitted)
    );

  const hardDeleteProjectSecretsInBatches = (
    projectId: string,
    batchSize: number,
    statementTimeoutMs: number,
    interBatchSleepMs: number,
    onBatchCommitted?: (deleted: number) => void
  ) =>
    hardDeleteSecretsInBatches(
      db,
      projectFolderIdsSubquery(projectId),
      pruneOpts(batchSize, statementTimeoutMs, interBatchSleepMs, onBatchCommitted)
    );

  // Hands a project's envs to the paced env hard-delete worker: marks them deleteAfter = now,
  // collapsing any restore grace. Returns rows marked.
  const softDeleteProjectEnvironments = async (projectId: string, tx?: Knex) => {
    try {
      const now = new Date();
      const marked = await (tx || db)(TableName.Environment)
        .where({ projectId })
        .andWhere((qb) => void qb.whereNull("deleteAfter").orWhere("deleteAfter", ">", now))
        .update({
          deleteAfter: now,
          softDeletedAt: db.raw(`COALESCE("softDeletedAt", ?)`, [now]) as unknown as Date
        });
      return marked;
    } catch (error) {
      throw new DatabaseError({ error, name: "Soft delete project environments" });
    }
  };

  // Counts ALL env rows (any soft-delete state). Primary-backed so a stale replica cannot end the drain early.
  const countProjectEnvironments = async (projectId: string, tx?: Knex) => {
    try {
      const doc = await (tx || db)(TableName.Environment).where({ projectId }).count().first();
      return Number(doc?.count ?? 0);
    } catch (error) {
      throw new DatabaseError({ error, name: "Count project environments" });
    }
  };

  const findIdentityProjects = async (identityId: string, orgId: string, projectType?: ProjectType) => {
    try {
      const identityGroupSubquery = db
        .replicaNode()(TableName.Groups)
        .leftJoin(
          TableName.IdentityGroupMembership,
          `${TableName.IdentityGroupMembership}.groupId`,
          `${TableName.Groups}.id`
        )
        .where(`${TableName.IdentityGroupMembership}.identityId`, identityId)
        .select(db.ref("id").withSchema(TableName.Groups));

      const workspaces = await db
        .replicaNode()(TableName.Membership)
        .where(`${TableName.Membership}.scope`, AccessScope.Project)
        .join(TableName.Project, `${TableName.Membership}.scopeProjectId`, `${TableName.Project}.id`)
        .where(`${TableName.Project}.orgId`, orgId)
        .whereNull(`${TableName.Project}.deleteAfter`)
        .andWhere((qb) => {
          void qb
            .where(`${TableName.Membership}.actorIdentityId`, identityId)
            .orWhereIn(`${TableName.Membership}.actorGroupId`, identityGroupSubquery);
        })
        .andWhere((qb) => {
          if (projectType) {
            void qb.where(`${TableName.Project}.type`, projectType);
          }
        })
        .leftJoin(TableName.Environment, function joinActiveEnvByProject() {
          this.on(`${TableName.Environment}.projectId`, `${TableName.Project}.id`).andOnNull(
            `${TableName.Environment}.deleteAfter`
          );
        })
        .select(
          selectAllTableCols(TableName.Project),
          db.ref("id").withSchema(TableName.Project).as("_id"),
          db.ref("id").withSchema(TableName.Environment).as("envId"),
          db.ref("slug").withSchema(TableName.Environment).as("envSlug"),
          db.ref("name").withSchema(TableName.Environment).as("envName")
        )
        .orderBy([
          { column: `${TableName.Project}.name`, order: "asc" },
          { column: `${TableName.Environment}.position`, order: "asc" }
        ]);

      const nestedWorkspaces = sqlNestRelationships({
        data: workspaces,
        key: "id",
        parentMapper: ({ _id, ...el }) => ({ _id, ...ProjectsSchema.parse(el) }),
        childrenMapper: [
          {
            key: "envId",
            label: "environments" as const,
            mapper: ({ envId: id, envSlug: slug, envName: name }) => ({
              id,
              slug,
              name
            })
          }
        ]
      });

      return nestedWorkspaces.map((workspace) => ({
        ...workspace,
        organization: workspace.orgId
      }));
    } catch (error) {
      throw new DatabaseError({ error, name: "Find identity projects" });
    }
  };

  const findUserProjects = async (userId: string, orgId: string | string[], projectType?: ProjectType) => {
    try {
      const userGroupSubquery = db
        .replicaNode()(TableName.Groups)
        .leftJoin(TableName.UserGroupMembership, `${TableName.UserGroupMembership}.groupId`, `${TableName.Groups}.id`)
        .where(`${TableName.UserGroupMembership}.userId`, userId)
        .select(db.ref("id").withSchema(TableName.Groups));

      const projects = await db
        .replicaNode()(TableName.Membership)
        .where(`${TableName.Membership}.scope`, AccessScope.Project)
        .join(TableName.Project, `${TableName.Membership}.scopeProjectId`, `${TableName.Project}.id`)
        .whereIn(`${TableName.Project}.orgId`, Array.isArray(orgId) ? orgId : [orgId])
        .whereNull(`${TableName.Project}.deleteAfter`)
        .andWhere((qb) => {
          void qb
            .where(`${TableName.Membership}.actorUserId`, userId)
            .orWhereIn(`${TableName.Membership}.actorGroupId`, userGroupSubquery);
        })
        .andWhere((qb) => {
          if (projectType) {
            void qb.where(`${TableName.Project}.type`, projectType);
          }
        })
        .leftJoin(TableName.Environment, function joinActiveEnvByProject() {
          this.on(`${TableName.Environment}.projectId`, `${TableName.Project}.id`).andOnNull(
            `${TableName.Environment}.deleteAfter`
          );
        })
        .select(
          selectAllTableCols(TableName.Project),
          db.ref("id").withSchema(TableName.Project).as("_id"),
          db.ref("id").withSchema(TableName.Environment).as("envId"),
          db.ref("slug").withSchema(TableName.Environment).as("envSlug"),
          db.ref("name").withSchema(TableName.Environment).as("envName")
        )
        .orderBy([
          { column: `${TableName.Project}.name`, order: "asc" },
          { column: `${TableName.Environment}.position`, order: "asc" }
        ]);

      const formattedProjects = sqlNestRelationships({
        data: projects,
        key: "id",
        parentMapper: ({ _id, ...el }) => ({ _id, ...ProjectsSchema.parse(el) }),
        childrenMapper: [
          {
            key: "envId",
            label: "environments" as const,
            mapper: ({ envId: id, envSlug: slug, envName: name }) => ({
              id,
              slug,
              name
            })
          }
        ]
      });

      return formattedProjects.map((workspace) => ({
        ...workspace,
        organization: workspace.orgId
      }));
    } catch (error) {
      throw new DatabaseError({ error, name: "Find all projects" });
    }
  };

  // Lightweight variant of findUserProjects/findIdentityProjects that returns only the
  // project IDs the actor has direct or group-based project membership on. Skips the
  // Project/Environment joins + sqlNestRelationships hydration used by the full variants,
  // for hot paths that need permission scoping but don't need the project rows themselves.
  const findActorAccessibleProjectIds = async (
    actorId: string,
    actorType: ActorType,
    orgId: string,
    tx?: Knex
  ): Promise<string[]> => {
    try {
      if (actorType !== ActorType.USER && actorType !== ActorType.IDENTITY) {
        return [];
      }
      const isUser = actorType === ActorType.USER;
      const groupMembershipTable = isUser ? TableName.UserGroupMembership : TableName.IdentityGroupMembership;
      const groupMembershipActorColumn = isUser ? "userId" : "identityId";
      const actorColumn = isUser ? "actorUserId" : "actorIdentityId";

      const groupSubquery = (tx || db.replicaNode())(TableName.Groups)
        .leftJoin(groupMembershipTable, `${groupMembershipTable}.groupId`, `${TableName.Groups}.id`)
        .where(`${groupMembershipTable}.${groupMembershipActorColumn}`, actorId)
        .select(db.ref("id").withSchema(TableName.Groups));

      const rows = await (tx || db.replicaNode())(TableName.Membership)
        .where(`${TableName.Membership}.scope`, AccessScope.Project)
        .where(`${TableName.Membership}.scopeOrgId`, orgId)
        .whereNotNull(`${TableName.Membership}.scopeProjectId`)
        .join(TableName.Project, `${TableName.Membership}.scopeProjectId`, `${TableName.Project}.id`)
        .whereNull(`${TableName.Project}.deleteAfter`)
        .andWhere((qb) => {
          void qb
            .where(`${TableName.Membership}.${actorColumn}`, actorId)
            .orWhereIn(`${TableName.Membership}.actorGroupId`, groupSubquery);
        })
        .distinct<{ scopeProjectId: string }[]>(`${TableName.Membership}.scopeProjectId`);

      return rows.map((r) => r.scopeProjectId);
    } catch (error) {
      throw new DatabaseError({ error, name: "Find actor accessible project ids" });
    }
  };

  /**
   * Returns all effective project memberships for an actor (user or identity) in a project: direct
   * membership and any memberships via groups the actor belongs to. Single query.
   */
  const findEffectiveProjectMemberships = async (dto: {
    actorType: ActorType;
    actorId: string;
    orgId: string;
    projectId: string;
    tx?: Knex;
  }): Promise<TMemberships[]> => {
    try {
      if (dto.actorType !== ActorType.USER && dto.actorType !== ActorType.IDENTITY) {
        return [];
      }

      const conn = dto.tx ?? db.replicaNode();
      const isUser = dto.actorType === ActorType.USER;
      const groupMembershipTable = isUser ? TableName.UserGroupMembership : TableName.IdentityGroupMembership;
      const groupMembershipActorColumn = isUser ? "userId" : "identityId";
      const actorColumn = isUser ? "actorUserId" : "actorIdentityId";

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

      const rows = await conn(TableName.Membership)
        .where(`${TableName.Membership}.scope`, AccessScope.Project)
        .where(`${TableName.Membership}.scopeOrgId`, dto.orgId)
        .where(`${TableName.Membership}.scopeProjectId`, dto.projectId)
        .join(TableName.Project, `${TableName.Membership}.scopeProjectId`, `${TableName.Project}.id`)
        .whereNull(`${TableName.Project}.deleteAfter`)
        .andWhere((qb) => {
          void qb
            .where(`${TableName.Membership}.${actorColumn}`, dto.actorId)
            .orWhereIn(`${TableName.Membership}.actorGroupId`, groupIdsSubquery);
        })
        .select(selectAllTableCols(TableName.Membership));

      return rows as TMemberships[];
    } catch (error) {
      throw new DatabaseError({ error, name: "Find effective project memberships" });
    }
  };

  /**
   * Returns the first effective project 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 findEffectiveProjectMemberships.
   */
  const findEffectiveProjectMembership = async (dto: {
    actorType: ActorType;
    actorId: string;
    orgId: string;
    projectId: string;
    tx?: Knex;
  }): Promise<TMemberships | null> => {
    const list = await findEffectiveProjectMemberships(dto);
    const directMembership = list.find((membership) =>
      dto.actorType === ActorType.USER
        ? membership.actorUserId === dto.actorId
        : membership.actorIdentityId === dto.actorId
    );

    return directMembership ?? list[0] ?? null;
  };

  /**
   * Resolves effective project membership for many users/groups in one lookup entrypoint.
   * Users are considered members if they are directly added or inherit access via a project group membership.
   */
  const findEffectiveProjectSubjectsMembership = async ({
    orgId,
    projectId,
    userIds,
    groupIds,
    tx
  }: {
    orgId: string;
    projectId: string;
    userIds: string[];
    groupIds: string[];
    tx?: Knex;
  }) => {
    try {
      const uniqueUserIds = [...new Set(userIds)];
      const uniqueGroupIds = [...new Set(groupIds)];
      if (uniqueUserIds.length === 0 && uniqueGroupIds.length === 0) {
        return {
          effectiveUserIds: [] as string[],
          effectiveGroupIds: [] as string[]
        };
      }

      const conn = tx || db.replicaNode();
      const rows = await conn(TableName.Membership)
        .join(TableName.Project, `${TableName.Membership}.scopeProjectId`, `${TableName.Project}.id`)
        .leftJoin(
          TableName.UserGroupMembership,
          `${TableName.Membership}.actorGroupId`,
          `${TableName.UserGroupMembership}.groupId`
        )
        .where(`${TableName.Membership}.scope`, AccessScope.Project)
        .where(`${TableName.Membership}.scopeOrgId`, orgId)
        .where(`${TableName.Membership}.scopeProjectId`, projectId)
        .where(`${TableName.Project}.orgId`, orgId)
        .whereNull(`${TableName.Project}.deleteAfter`)
        .andWhere((qb) => {
          let hasCondition = false;
          if (uniqueUserIds.length > 0) {
            hasCondition = true;
            void qb.where((sqb) => {
              void sqb
                .whereIn(`${TableName.Membership}.actorUserId`, uniqueUserIds)
                .orWhereIn(`${TableName.UserGroupMembership}.userId`, uniqueUserIds);
            });
          }
          if (uniqueGroupIds.length > 0) {
            if (hasCondition) {
              void qb.orWhereIn(`${TableName.Membership}.actorGroupId`, uniqueGroupIds);
            } else {
              void qb.whereIn(`${TableName.Membership}.actorGroupId`, uniqueGroupIds);
            }
          }
        })
        .select<
          {
            directUserId: string | null;
            groupUserId: string | null;
            directGroupId: string | null;
          }[]
        >(
          db.ref("actorUserId").withSchema(TableName.Membership).as("directUserId"),
          db.ref("userId").withSchema(TableName.UserGroupMembership).as("groupUserId"),
          db.ref("actorGroupId").withSchema(TableName.Membership).as("directGroupId")
        );

      const effectiveUserIds = new Set<string>();
      const effectiveGroupIds = new Set<string>();
      const requestedUserIds = new Set(uniqueUserIds);
      const requestedGroupIds = new Set(uniqueGroupIds);

      for (const row of rows) {
        if (row.directUserId && requestedUserIds.has(row.directUserId)) {
          effectiveUserIds.add(row.directUserId);
        }
        if (row.groupUserId && requestedUserIds.has(row.groupUserId)) {
          effectiveUserIds.add(row.groupUserId);
        }
        if (row.directGroupId && requestedGroupIds.has(row.directGroupId)) {
          effectiveGroupIds.add(row.directGroupId);
        }
      }

      return {
        effectiveUserIds: [...effectiveUserIds],
        effectiveGroupIds: [...effectiveGroupIds]
      };
    } catch (error) {
      throw new DatabaseError({ error, name: "Find effective project subjects membership" });
    }
  };
  const findProjectTypesByIds = async (ids: string[], tx?: Knex): Promise<{ id: string; type: string }[]> => {
    try {
      if (ids.length === 0) return [];
      const rows = await (tx || db.replicaNode())(TableName.Project).whereIn("id", ids).select("id", "type");
      return rows as { id: string; type: string }[];
    } catch (error) {
      throw new DatabaseError({ error, name: "Find project types by ids" });
    }
  };

  // Lightweight all-projects-in-org lookup that returns only the IDs.
  const findOrgProjectIds = async (orgId: string, tx?: Knex): Promise<string[]> => {
    try {
      const rows = await (tx || db.replicaNode())(TableName.Project)
        .where({ orgId })
        .whereNull("deleteAfter")
        .select("id");
      return rows.map((r) => r.id);
    } catch (error) {
      throw new DatabaseError({ error, name: "Find org project ids" });
    }
  };

  const findProjectGhostUser = async (projectId: string, tx?: Knex) => {
    try {
      const ghostUser = await (tx || db.replicaNode())(TableName.Membership)
        .where(`${TableName.Membership}.scope`, AccessScope.Project)
        .where(`${TableName.Membership}.scopeProjectId`, projectId)
        .join(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
        .select(selectAllTableCols(TableName.Users))
        .where({ isGhost: true })
        .first();
      return ghostUser;
    } catch (error) {
      throw new DatabaseError({ error, name: "Find project top-level user" });
    }
  };

  const setProjectUpgradeStatus = async (projectId: string, status: ProjectUpgradeStatus | null, tx?: Knex) => {
    try {
      const data: TProjectsUpdate = {
        upgradeStatus: status
      } as const;

      await (tx || db)(TableName.Project).where({ id: projectId }).update(data);
    } catch (error) {
      throw new DatabaseError({ error, name: "Set project upgrade status" });
    }
  };

  const findProjectById = async (id: string) => {
    try {
      const workspaces = await db
        .replicaNode()(TableName.Project)
        .where(`${TableName.Project}.id`, id)
        .whereNull(`${TableName.Project}.deleteAfter`)
        .leftJoin(TableName.Environment, function joinActiveEnvByProject() {
          this.on(`${TableName.Environment}.projectId`, `${TableName.Project}.id`).andOnNull(
            `${TableName.Environment}.deleteAfter`
          );
        })
        .select(
          selectAllTableCols(TableName.Project),
          db.ref("id").withSchema(TableName.Environment).as("envId"),
          db.ref("slug").withSchema(TableName.Environment).as("envSlug"),
          db.ref("name").withSchema(TableName.Environment).as("envName")
        )
        .orderBy([
          { column: `${TableName.Project}.name`, order: "asc" },
          { column: `${TableName.Environment}.position`, order: "asc" }
        ]);

      const project = sqlNestRelationships({
        data: workspaces,
        key: "id",
        parentMapper: ({ ...el }) => ({ _id: el.id, ...ProjectsSchema.parse(el) }),
        childrenMapper: [
          {
            key: "envId",
            label: "environments" as const,
            mapper: ({ envId, envSlug: slug, envName: name }) => ({
              id: envId,
              slug,
              name
            })
          }
        ]
      })?.[0];

      if (!project) {
        throw new NotFoundError({ message: `Project with ID '${id}' not found` });
      }

      return project;
    } catch (error) {
      if (error instanceof NotFoundError) {
        throw error;
      }

      throw new DatabaseError({ error, name: "Find all projects" });
    }
  };

  const findProjectBySlug = async (slug: string, orgId: string | undefined) => {
    try {
      if (!orgId) {
        throw new UnauthorizedError({ message: "Organization ID is required when querying with slugs" });
      }

      const projects = await db
        .replicaNode()(TableName.Project)
        .where(`${TableName.Project}.slug`, slug)
        .where(`${TableName.Project}.orgId`, orgId)
        .whereNull(`${TableName.Project}.deleteAfter`)
        .leftJoin(TableName.Environment, function joinActiveEnvByProject() {
          this.on(`${TableName.Environment}.projectId`, `${TableName.Project}.id`).andOnNull(
            `${TableName.Environment}.deleteAfter`
          );
        })
        .select(
          selectAllTableCols(TableName.Project),
          db.ref("id").withSchema(TableName.Environment).as("envId"),
          db.ref("slug").withSchema(TableName.Environment).as("envSlug"),
          db.ref("name").withSchema(TableName.Environment).as("envName")
        )
        .orderBy([
          { column: `${TableName.Project}.name`, order: "asc" },
          { column: `${TableName.Environment}.position`, order: "asc" }
        ]);

      const project = sqlNestRelationships({
        data: projects,
        key: "id",
        parentMapper: ({ ...el }) => ({ _id: el.id, ...ProjectsSchema.parse(el) }),
        childrenMapper: [
          {
            key: "envId",
            label: "environments" as const,
            mapper: ({ envId, envSlug, envName: name }) => ({
              id: envId,
              slug: envSlug,
              name
            })
          }
        ]
      })?.[0];

      if (!project) {
        throw new NotFoundError({ message: `Project with slug '${slug}' not found` });
      }

      return project;
    } catch (error) {
      if (error instanceof NotFoundError || error instanceof UnauthorizedError) {
        throw error;
      }

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

  const findProjectByFilter = async (filter: Filter) => {
    try {
      if (filter.type === ProjectFilterType.ID) {
        return await findProjectById(filter.projectId);
      }
      if (filter.type === ProjectFilterType.SLUG) {
        if (!filter.orgId) {
          throw new UnauthorizedError({
            message: "Organization ID is required when querying with slugs"
          });
        }

        return await findProjectBySlug(filter.slug, filter.orgId);
      }
      throw new BadRequestError({ message: "Invalid filter type" });
    } catch (error) {
      if (error instanceof BadRequestError || error instanceof NotFoundError || error instanceof UnauthorizedError) {
        throw error;
      }
      throw new DatabaseError({ error, name: `Failed to find project by ${filter.type}` });
    }
  };

  const checkProjectUpgradeStatus = async (projectId: string) => {
    const project = await findById(projectId);
    if (!project) {
      throw new NotFoundError({ message: `Project with ID '${projectId}' not found` });
    }
    const upgradeInProgress =
      project.upgradeStatus === ProjectUpgradeStatus.InProgress && project.version === ProjectVersion.V1;

    if (upgradeInProgress) {
      throw new BadRequestError({
        message: "Project is currently being upgraded, and secrets cannot be written. Please try again"
      });
    }
  };

  const findProjectWithOrg = async (projectId: string, tx?: Knex) => {
    // we just need the project, and we need to include a new .organization field that includes the org from the orgId reference

    const project = await (tx || db)(TableName.Project)
      .where({ [`${TableName.Project}.id` as "id"]: projectId })
      .whereNull(`${TableName.Project}.deleteAfter`)
      .join(TableName.Organization, `${TableName.Organization}.id`, `${TableName.Project}.orgId`)

      .select(
        db.ref("id").withSchema(TableName.Organization).as("organizationId"),
        db.ref("name").withSchema(TableName.Organization).as("organizationName")
      )
      .select(selectAllTableCols(TableName.Project))
      .first();

    if (!project) {
      throw new NotFoundError({ message: `Project with ID '${projectId}' not found` });
    }

    return {
      ...ProjectsSchema.parse(project),
      organization: {
        id: project.organizationId,
        name: project.organizationName
      }
    };
  };

  const searchProjects = async (dto: {
    orgId: string;
    actor: ActorType;
    actorId: string;
    type?: ProjectType;
    limit?: number;
    offset?: number;
    name?: string;
    sortBy?: SearchProjectSortBy;
    sortDir?: SortDirection;
    projectIds?: string[];
  }) => {
    const { limit = 20, offset = 0, sortBy = SearchProjectSortBy.NAME, sortDir = SortDirection.ASC } = dto;
    const groupMembershipSubquery = db(TableName.Groups)
      .leftJoin(TableName.UserGroupMembership, `${TableName.UserGroupMembership}.groupId`, `${TableName.Groups}.id`)
      .where(`${TableName.UserGroupMembership}.userId`, dto.actorId)
      .select(db.ref("id").withSchema(TableName.Groups));

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

    const membershipSubQuery = db(TableName.Membership)
      .where(`${TableName.Membership}.scope`, AccessScope.Project)
      .where((qb) => {
        if (dto.actor === ActorType.IDENTITY) {
          void qb
            .where(`${TableName.Membership}.actorIdentityId`, dto.actorId)
            .orWhereIn(`${TableName.Membership}.actorGroupId`, identityGroupMembershipSubquery);
        } else {
          void qb
            .where(`${TableName.Membership}.actorUserId`, dto.actorId)
            .orWhereIn(`${TableName.Membership}.actorGroupId`, groupMembershipSubquery);
        }
      })
      .select("scopeProjectId");

    const directMembershipSubQuery = db(TableName.Membership)
      .where(`${TableName.Membership}.scope`, AccessScope.Project)
      .where(
        dto.actor === ActorType.IDENTITY
          ? `${TableName.Membership}.actorIdentityId`
          : `${TableName.Membership}.actorUserId`,
        dto.actorId
      )
      .select("scopeProjectId");

    // Get the SQL strings for the subqueries
    const membershipSQL = membershipSubQuery.toQuery();
    const directMembershipSQL = directMembershipSubQuery.toQuery();

    const query = db
      .replicaNode()(TableName.Project)
      .where(`${TableName.Project}.orgId`, dto.orgId)
      .whereNull(`${TableName.Project}.deleteAfter`)
      .select(selectAllTableCols(TableName.Project))
      .select(db.raw("COUNT(*) OVER() AS count"))
      .select<(TProjects & { isMember: boolean; isDirectMember: boolean; count: number })[]>(
        db.raw(
          `
                  CASE
                    WHEN ${TableName.Project}.id IN (?) THEN TRUE
                    ELSE FALSE
                  END as "isMember"
                `,
          [db.raw(membershipSQL)]
        ),
        db.raw(
          `
                  CASE
                    WHEN ${TableName.Project}.id IN (?) THEN TRUE
                    ELSE FALSE
                  END as "isDirectMember"
                `,
          [db.raw(directMembershipSQL)]
        )
      )
      .limit(limit)
      .offset(offset);
    const sortColumn = {
      [SearchProjectSortBy.NAME]: `${TableName.Project}.name`,
      [SearchProjectSortBy.DESCRIPTION]: `${TableName.Project}.description`,
      [SearchProjectSortBy.CREATED_AT]: `${TableName.Project}.createdAt`
    }[sortBy];

    void query.orderBy(sortColumn, sortDir, "last");
    if (sortBy !== SearchProjectSortBy.NAME) {
      void query.orderBy(`${TableName.Project}.name`, SortDirection.ASC);
    }
    void query.orderBy(`${TableName.Project}.id`, SortDirection.ASC);

    if (dto.type) {
      void query.where(`${TableName.Project}.type`, dto.type);
    }
    if (dto.name) {
      void query.whereILike(`${TableName.Project}.name`, `%${sanitizeSqlLikeString(dto.name)}%`);
    }

    if (dto.projectIds?.length) {
      void query.whereIn(`${TableName.Project}.id`, dto.projectIds);
    }

    const docs = await query;

    return { docs, totalCount: Number(docs?.[0]?.count ?? 0) };
  };

  const findProjectByEnvId = async (envId: string, tx?: Knex) => {
    const project = await (tx || db.replicaNode())(TableName.Project)
      .leftJoin(TableName.Environment, function joinActiveEnvByProject() {
        this.on(`${TableName.Environment}.projectId`, `${TableName.Project}.id`).andOnNull(
          `${TableName.Environment}.deleteAfter`
        );
      })
      // eslint-disable-next-line @typescript-eslint/no-misused-promises
      .where(buildFindFilter({ id: envId }, TableName.Environment))
      .whereNull(`${TableName.Project}.deleteAfter`)
      .select(selectAllTableCols(TableName.Project))
      .first();
    return project;
  };

  const findProjectDeletedEnvironments = async (projectId: string, tx?: Knex) => {
    try {
      type DeletedEnvironmentRow = TDeletedByRow & {
        id: string;
        slug: string;
        name: string;
        deleteAfter: Date;
        softDeletedAt: Date;
      };

      const rows = (await (tx || db.replicaNode())(TableName.Environment)
        .leftJoin(TableName.Users, `${TableName.Environment}.deletedByUserId`, `${TableName.Users}.id`)
        .leftJoin(TableName.Identity, `${TableName.Environment}.deletedByIdentityId`, `${TableName.Identity}.id`)
        .where(`${TableName.Environment}.projectId`, projectId)
        .whereNotNull(`${TableName.Environment}.deleteAfter`)
        .whereNotNull(`${TableName.Environment}.softDeletedAt`)
        .select(
          `${TableName.Environment}.id`,
          `${TableName.Environment}.slug`,
          `${TableName.Environment}.name`,
          `${TableName.Environment}.deleteAfter`,
          `${TableName.Environment}.softDeletedAt`,
          `${TableName.Environment}.deletedByUserId`,
          `${TableName.Environment}.deletedByIdentityId`,
          db.ref("email").withSchema(TableName.Users).as("deletedByUserEmail"),
          db.ref("username").withSchema(TableName.Users).as("deletedByUserUsername"),
          db.ref("firstName").withSchema(TableName.Users).as("deletedByUserFirstName"),
          db.ref("lastName").withSchema(TableName.Users).as("deletedByUserLastName"),
          db.ref("name").withSchema(TableName.Identity).as("deletedByIdentityName")
        )
        .orderBy(`${TableName.Environment}.position`, "asc")) as DeletedEnvironmentRow[];

      return rows.map((row) => ({
        id: row.id,
        slug: row.slug,
        name: row.name,
        deleteAfter: row.deleteAfter,
        softDeletedAt: row.softDeletedAt,
        deletedBy: toDeletedBy(row)
      }));
    } catch (error) {
      throw new DatabaseError({ error, name: "Find project deleted environments" });
    }
  };

  const findDeletedProjects = async (orgId: string, type?: ProjectType, tx?: Knex) => {
    try {
      type DeletedProjectRow = TDeletedByRow & {
        id: string;
        name: string;
        type: string;
        deleteAfter: Date;
        softDeletedAt: Date;
      };

      const query = (tx || db.replicaNode())(TableName.Project)
        .leftJoin(TableName.Users, `${TableName.Project}.deletedByActorId`, `${TableName.Users}.id`)
        .leftJoin(TableName.Identity, `${TableName.Project}.deletedByActorId`, `${TableName.Identity}.id`)
        .where(`${TableName.Project}.orgId`, orgId)
        .where(`${TableName.Project}.deleteAfter`, ">", new Date())
        .whereNotNull(`${TableName.Project}.softDeletedAt`)
        .select(
          `${TableName.Project}.id`,
          `${TableName.Project}.name`,
          `${TableName.Project}.type`,
          `${TableName.Project}.deleteAfter`,
          `${TableName.Project}.softDeletedAt`,
          db.ref("id").withSchema(TableName.Users).as("deletedByUserId"),
          db.ref("id").withSchema(TableName.Identity).as("deletedByIdentityId"),
          db.ref("email").withSchema(TableName.Users).as("deletedByUserEmail"),
          db.ref("username").withSchema(TableName.Users).as("deletedByUserUsername"),
          db.ref("firstName").withSchema(TableName.Users).as("deletedByUserFirstName"),
          db.ref("lastName").withSchema(TableName.Users).as("deletedByUserLastName"),
          db.ref("name").withSchema(TableName.Identity).as("deletedByIdentityName")
        )
        .orderBy(`${TableName.Project}.deleteAfter`, "asc");
      if (type) void query.where(`${TableName.Project}.type`, type);

      const rows = (await query) as DeletedProjectRow[];

      return rows.map((row) => ({
        id: row.id,
        name: row.name,
        type: row.type,
        deleteAfter: row.deleteAfter,
        softDeletedAt: row.softDeletedAt,
        deletedBy: toDeletedBy(row)
      }));
    } catch (error) {
      throw new DatabaseError({ error, name: "Find deleted projects" });
    }
  };

  // Other live projects this project shares folders of live environments with. A deleted environment's grants
  // are inactive and can't be revoked. Reads the primary so a grant made just before a delete is seen.
  const findProjectsSharedWith = async (projectId: string, tx?: Knex) => {
    try {
      const docs = await (tx || db)(TableName.ProjectFolderGrant)
        .join(TableName.SecretFolder, `${TableName.ProjectFolderGrant}.sourceFolderId`, `${TableName.SecretFolder}.id`)
        .join(TableName.Environment, `${TableName.SecretFolder}.envId`, `${TableName.Environment}.id`)
        .join(TableName.Project, `${TableName.ProjectFolderGrant}.targetProjectId`, `${TableName.Project}.id`)
        .where(`${TableName.ProjectFolderGrant}.sourceProjectId`, projectId)
        .whereNull(`${TableName.Environment}.deleteAfter`)
        .whereNull(`${TableName.Project}.deleteAfter`)
        .distinct(db.ref("id").withSchema(TableName.Project), db.ref("name").withSchema(TableName.Project));
      return docs as { id: string; name: string }[];
    } catch (error) {
      throw new DatabaseError({ error, name: "Find projects shared with" });
    }
  };

  const countOfBillableOrgProjects = async (orgId: string | null, tx?: Knex) => {
    try {
      const subOrgProjects = db.replicaNode()(TableName.Organization).where({ rootOrgId: orgId }).select("id");

      const doc = await (tx || db.replicaNode())(TableName.Project)
        .whereNotIn("type", [ProjectType.CertificateManager])
        .whereNull("deleteAfter")
        .whereNotIn("type", [
          ProjectType.CertificateManager,
          ProjectType.SecretScanning,
          ProjectType.PAM,
          ProjectType.AgentVault
        ])
        // Project rows of the removed SSH / Agent Sentinel products are left in
        // place (see migration 20260729150000) but must not consume workspace quota.
        .whereNotIn("type", ["ssh", "ai"])
        .andWhere((bd) => {
          if (orgId) {
            void bd.where({ orgId }).orWhereIn("orgId", subOrgProjects);
          }
        })
        .count();
      return Number(doc?.[0]?.count ?? 0);
    } catch (error) {
      throw new DatabaseError({ error, name: "Count of Org Projects" });
    }
  };

  return {
    ...projectOrm,
    findById,
    findOne,
    find,
    findByIdIncludingExpired,
    findIncludingExpired,
    softDeleteById,
    expireSoftDeletedById,
    restoreById,
    findExpiredForHardDelete,
    hardDeleteProjectSecretVersionsInBatches,
    hardDeleteProjectSecretReferencesInBatches,
    hardDeleteProjectApprovalSecretLinksInBatches,
    hardDeleteProjectSecretsInBatches,
    softDeleteProjectEnvironments,
    countProjectEnvironments,
    findUserProjects,
    findIdentityProjects,
    findActorAccessibleProjectIds,
    findEffectiveProjectMemberships,
    findEffectiveProjectMembership,
    findEffectiveProjectSubjectsMembership,
    findProjectTypesByIds,
    findOrgProjectIds,
    setProjectUpgradeStatus,
    findProjectGhostUser,
    findProjectById,
    findProjectByFilter,
    findProjectBySlug,
    findProjectWithOrg,
    checkProjectUpgradeStatus,
    searchProjects,
    findProjectByEnvId,
    findProjectDeletedEnvironments,
    findDeletedProjects,
    findProjectsSharedWith,
    countOfBillableOrgProjects
  };
};