Skip to content

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

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

import { TDbClient } from "@app/db";
import {
  AccessScope,
  TableName,
  TUserActionsInsert,
  TUserActionsUpdate,
  TUserEncryptionKeys,
  TUserEncryptionKeysInsert,
  TUserEncryptionKeysUpdate,
  TUsers,
  UsersSchema
} from "@app/db/schemas";
import { DatabaseError } from "@app/lib/errors";
import { sanitizeSqlLikeString } from "@app/lib/fn";
import { ormify, selectAllTableCols, sqlNestRelationships } from "@app/lib/knex";

export type TUserDALFactory = ReturnType<typeof userDALFactory>;

export const userDALFactory = (db: TDbClient) => {
  const userOrm = ormify(db, TableName.Users);
  const getUsersByFilter = async ({
    limit,
    offset,
    searchTerm,
    sortBy,
    adminsOnly
  }: {
    limit: number;
    offset: number;
    searchTerm: string;
    sortBy?: keyof TUsers;
    adminsOnly: boolean;
  }) => {
    try {
      let query = db.replicaNode()(TableName.Users).where("isGhost", "=", false);

      if (searchTerm) {
        query = query.where((qb) => {
          void qb
            .whereILike("email", `%${sanitizeSqlLikeString(searchTerm)}%`)
            .orWhereILike("firstName", `%${sanitizeSqlLikeString(searchTerm)}%`)
            .orWhereILike("lastName", `%${sanitizeSqlLikeString(searchTerm)}%`)
            .orWhereRaw('lower("username") like ?', `%${sanitizeSqlLikeString(searchTerm)}%`);
        });
      }

      if (adminsOnly) {
        query = query.where("superAdmin", true);
      }

      const countQuery = query.clone();

      if (sortBy) {
        query = query.orderBy(sortBy);
      }

      const [users, totalResult] = await Promise.all([
        query.limit(limit).offset(offset).select(selectAllTableCols(TableName.Users)),
        countQuery.count("*", { as: "count" }).first()
      ]);

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

      return { users, total };
    } catch (error) {
      throw new DatabaseError({ error, name: "Get users by filter" });
    }
  };

  // USER ENCRYPTION FUNCTIONS
  // -------------------------
  const findUserEncKeyByUsername = async ({ username }: { username: string }) => {
    try {
      return await db
        .replicaNode()(TableName.Users)
        .whereRaw('lower("username") = :username', { username: username.toLowerCase() })
        .where({
          isGhost: false
        })
        .join(TableName.UserEncryptionKey, `${TableName.Users}.id`, `${TableName.UserEncryptionKey}.userId`);
    } catch (error) {
      throw new DatabaseError({ error, name: "Find user enc by email" });
    }
  };

  const findUserEncKeyByUserIdsBatch = async ({ userIds }: { userIds: string[] }, tx?: Knex) => {
    try {
      return await (tx || db.replicaNode())(TableName.Users)
        .where({
          isGhost: false
        })
        .whereIn(`${TableName.Users}.id`, userIds)
        .leftJoin(TableName.UserEncryptionKey, `${TableName.Users}.id`, `${TableName.UserEncryptionKey}.userId`)
        .select(selectAllTableCols(TableName.Users))
        .select(db.ref("publicKey").withSchema(TableName.UserEncryptionKey));
    } catch (error) {
      throw new DatabaseError({ error, name: "Find user enc by user ids batch" });
    }
  };

  const findUserEncKeyByUserId = async (userId: string, tx?: Knex) => {
    try {
      const user = await (tx || db.replicaNode())(TableName.Users)
        .leftJoin(TableName.UserEncryptionKey, `${TableName.Users}.id`, `${TableName.UserEncryptionKey}.userId`)
        .where(`${TableName.Users}.id`, userId)
        .select(selectAllTableCols(TableName.Users))
        .select(
          db.ref("publicKey").withSchema(TableName.UserEncryptionKey),
          db.ref("iv").withSchema(TableName.UserEncryptionKey),
          db.ref("salt").withSchema(TableName.UserEncryptionKey),
          db.ref("tag").withSchema(TableName.UserEncryptionKey),
          db.ref("protectedKey").withSchema(TableName.UserEncryptionKey),
          db.ref("protectedKeyIV").withSchema(TableName.UserEncryptionKey),
          db.ref("protectedKeyTag").withSchema(TableName.UserEncryptionKey),
          db.ref("encryptedPrivateKey").withSchema(TableName.UserEncryptionKey)
        )
        .first();

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

  const findUserByProjectMembershipId = async (projectMembershipId: string) => {
    try {
      return await db
        .replicaNode()(TableName.Membership)
        .where({
          [`${TableName.Membership}.id` as "id"]: projectMembershipId,
          [`${TableName.Membership}.scope` as "scope"]: AccessScope.Project
        })
        .whereNotNull(`${TableName.Membership}.actorUserId`)
        .join(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
        .first();
    } catch (error) {
      throw new DatabaseError({ error, name: "Find user by project membership id" });
    }
  };

  const findUsersByProjectMembershipIds = async (projectMembershipIds: string[]) => {
    try {
      return await db
        .replicaNode()(TableName.Membership)
        .whereIn(`${TableName.Membership}.id`, projectMembershipIds)
        .where(`${TableName.Membership}.scope`, AccessScope.Project)
        .whereNotNull(`${TableName.Membership}.actorUserId`)
        .join(TableName.Users, `${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
        .select("*");
    } catch (error) {
      throw new DatabaseError({ error, name: "Find users by project membership ids" });
    }
  };

  const createUserEncryption = async (data: TUserEncryptionKeysInsert, tx?: Knex) => {
    try {
      const [userEnc] = await (tx || db)(TableName.UserEncryptionKey).insert(data).returning("*");
      return userEnc;
    } catch (error) {
      throw new DatabaseError({ error, name: "Create user encryption" });
    }
  };

  const updateUserEncryptionByUserId = async (userId: string, data: TUserEncryptionKeysUpdate, tx?: Knex) => {
    try {
      const [userEnc] = await (tx || db)(TableName.UserEncryptionKey)
        .where({ userId })
        .update({ ...data })
        .returning("*");
      return userEnc;
    } catch (error) {
      throw new DatabaseError({ error, name: "Update user enc by user id" });
    }
  };

  const upsertUserEncryptionKey = async (
    userId: string,
    data: Omit<TUserEncryptionKeysUpdate, "userId">,
    tx?: Knex
  ) => {
    try {
      const [userEnc] = await (tx ? tx(TableName.UserEncryptionKey) : db(TableName.UserEncryptionKey))
        // if user insert make sure to pass all required data
        .insert({ userId, ...data } as TUserEncryptionKeys)
        .onConflict("userId")
        .merge()
        .returning("*");
      return userEnc;
    } catch (error) {
      throw new DatabaseError({ error, name: "Upsert user enc key" });
    }
  };

  const findAllMyAccounts = async (email: string) => {
    try {
      const doc = await db(TableName.Users)
        .where({ email })
        .leftJoin(TableName.Membership, (qb) => {
          void qb
            .on(`${TableName.Membership}.actorUserId`, `${TableName.Users}.id`)
            .andOn(`${TableName.Membership}.scope`, db.raw("?", [AccessScope.Organization]));
        })
        .leftJoin(TableName.Organization, `${TableName.Organization}.id`, `${TableName.Membership}.scopeOrgId`)
        .select(selectAllTableCols(TableName.Users))
        .select(
          db.ref("name").withSchema(TableName.Organization).as("orgName"),
          db.ref("slug").withSchema(TableName.Organization).as("orgSlug")
        );
      const formattedDoc = sqlNestRelationships({
        data: doc,
        key: "id",
        parentMapper: (el) => UsersSchema.parse(el),
        childrenMapper: [
          {
            key: "orgSlug",
            label: "organizations" as const,
            mapper: ({ orgSlug, orgName }) => ({
              slug: orgSlug,
              name: orgName
            })
          }
        ]
      });
      return formattedDoc;
    } catch (error) {
      throw new DatabaseError({ error, name: "Upsert user enc key" });
    }
  };

  // USER ACTION FUNCTIONS
  // ---------------------
  const findOneUserAction = (filter: TUserActionsUpdate, tx?: Knex) => {
    try {
      return (tx || db.replicaNode())(TableName.UserAction).where(filter).first("*");
    } catch (error) {
      throw new DatabaseError({ error, name: "Find one user action" });
    }
  };

  const createUserAction = async (data: TUserActionsInsert, tx?: Knex) => {
    try {
      const [userAction] = await (tx || db)(TableName.UserAction).insert(data).returning("*");
      return userAction;
    } catch (error) {
      throw new DatabaseError({ error, name: "Create user action" });
    }
  };

  return {
    ...userOrm,
    findUserEncKeyByUsername,
    findUserEncKeyByUserIdsBatch,
    findUserEncKeyByUserId,
    updateUserEncryptionByUserId,
    findUserByProjectMembershipId,
    findUsersByProjectMembershipIds,
    upsertUserEncryptionKey,
    createUserEncryption,
    findOneUserAction,
    createUserAction,
    getUsersByFilter,
    findAllMyAccounts
  };
};