Move billing version migration to front

.env.example

@@ -67,6 +67,3 @@ CLIENT_SECRET_GITLAB_LOGIN=
 CAPTCHA_SECRET=
 
 NEXT_PUBLIC_CAPTCHA_SITE_KEY=
-
-PLAIN_API_KEY=
-PLAIN_WISH_LABEL_IDS=

.github/workflows/build-binaries.yml

@@ -1,99 +0,0 @@
-name: Build Binaries and Deploy
-
-on:
-    workflow_dispatch:
-        inputs:
-            version:
-                description: "Version number"
-                required: true
-                type: string
-
-defaults:
-    run:
-        working-directory: ./backend
-
-jobs:
-    build-and-deploy:
-        runs-on: ubuntu-20.04
-        strategy:
-            matrix:
-                arch: [x64, arm64]
-                os: [linux, win]
-                include:
-                    - os: linux
-                      target: node20-linux
-                    - os: win
-                      target: node20-win
-
-        steps:
-            - name: Checkout code
-              uses: actions/checkout@v3
-
-            - name: Set up Node.js
-              uses: actions/setup-node@v3
-              with:
-                  node-version: 20
-
-            - name: Install pkg
-              run: npm install -g @yao-pkg/pkg
-
-            - name: Install dependencies (backend)
-              run: npm install
-
-            - name: Install dependencies (frontend)
-              run: npm install --prefix ../frontend
-
-            - name: Prerequisites for pkg
-              run: npm run binary:build
-
-            - name: Package into node binary
-              run: |
-                  if [ "${{ matrix.os }}" != "linux" ]; then
-                    pkg --no-bytecode --public-packages "*" --public --compress Brotli --target ${{ matrix.target }}-${{ matrix.arch }} --output ./binary/infisical-core-${{ matrix.os }}-${{ matrix.arch }} .
-                  else
-                    pkg --no-bytecode --public-packages "*" --public --compress Brotli --target ${{ matrix.target }}-${{ matrix.arch }} --output ./binary/infisical-core .
-                  fi
-
-            # Set up .deb package structure (Debian/Ubuntu only)
-            - name: Set up .deb package structure
-              if: matrix.os == 'linux'
-              run: |
-                  mkdir -p infisical-core/DEBIAN
-                  mkdir -p infisical-core/usr/local/bin
-                  cp ./binary/infisical-core infisical-core/usr/local/bin/
-                  chmod +x infisical-core/usr/local/bin/infisical-core
-
-            - name: Create control file
-              if: matrix.os == 'linux'
-              run: |
-                  cat <<EOF > infisical-core/DEBIAN/control
-                  Package: infisical-core
-                  Version: ${{ github.event.inputs.version }}
-                  Section: base
-                  Priority: optional
-                  Architecture: ${{ matrix.arch == 'x64' && 'amd64' || matrix.arch }}
-                  Maintainer: Infisical <daniel@infisical.com>
-                  Description: Infisical Core standalone executable (app.infisical.com)
-                  EOF
-
-            # Build .deb file (Debian/Ubunutu only)
-            - name: Build .deb package
-              if: matrix.os == 'linux'
-              run: |
-                  dpkg-deb --build infisical-core
-                  mv infisical-core.deb ./binary/infisical-core-${{matrix.arch}}.deb
-
-            - uses: actions/setup-python@v4
-            - run: pip install --upgrade cloudsmith-cli
-
-            # Publish .deb file to Cloudsmith (Debian/Ubuntu only)
-            - name: Publish to Cloudsmith (Debian/Ubuntu)
-              if: matrix.os == 'linux'
-              working-directory: ./backend
-              run: cloudsmith push deb --republish --no-wait-for-sync --api-key=${{ secrets.CLOUDSMITH_API_KEY }} infisical/infisical-core/any-distro/any-version ./binary/infisical-core-${{ matrix.arch }}.deb
-
-            # Publish .exe file to Cloudsmith (Windows only)
-            - name: Publish to Cloudsmith (Windows)
-              if: matrix.os == 'win'
-              working-directory: ./backend
-              run: cloudsmith push raw infisical/infisical-core ./binary/infisical-core-${{ matrix.os }}-${{ matrix.arch }}.exe --republish --no-wait-for-sync --version ${{ github.event.inputs.version }} --api-key ${{ secrets.CLOUDSMITH_API_KEY }}

.github/workflows/build-staging-and-deploy-aws.yml

@@ -50,13 +50,6 @@ jobs:
     environment:
       name: Gamma
     steps:
-      - uses: twingate/github-action@v1
-        with:
-          # The Twingate Service Key used to connect Twingate to the proper service
-          # Learn more about [Twingate Services](https://docs.twingate.com/docs/services)
-          #
-          # Required
-          service-key: ${{ secrets.TWINGATE_SERVICE_KEY }}
       - name: Checkout code
         uses: actions/checkout@v2
       - name: Setup Node.js environment
@@ -81,21 +74,21 @@ jobs:
         uses: pr-mpt/actions-commit-hash@v2
       - name: Download task definition
         run: |
-          aws ecs describe-task-definition --task-definition infisical-core-gamma-stage --query taskDefinition > task-definition.json
+          aws ecs describe-task-definition --task-definition infisical-core-platform --query taskDefinition > task-definition.json
       - name: Render Amazon ECS task definition
         id: render-web-container
         uses: aws-actions/amazon-ecs-render-task-definition@v1
         with:
           task-definition: task-definition.json
-          container-name: infisical-core
+          container-name: infisical-core-platform
           image: infisical/staging_infisical:${{ steps.commit.outputs.short }}
           environment-variables: "LOG_LEVEL=info"
       - name: Deploy to Amazon ECS service
         uses: aws-actions/amazon-ecs-deploy-task-definition@v1
         with:
           task-definition: ${{ steps.render-web-container.outputs.task-definition }}
-          service: infisical-core-gamma-stage
-          cluster: infisical-gamma-stage
+          service: infisical-core-platform
+          cluster: infisical-core-platform
           wait-for-service-stability: true
 
   production-postgres-deployment:
@@ -105,13 +98,6 @@ jobs:
     environment:
       name: Production
     steps:
-      - uses: twingate/github-action@v1
-        with:
-        # The Twingate Service Key used to connect Twingate to the proper service
-        # Learn more about [Twingate Services](https://docs.twingate.com/docs/services)
-        #
-        # Required
-          service-key: ${{ secrets.TWINGATE_SERVICE_KEY }}
       - name: Checkout code
         uses: actions/checkout@v2
       - name: Setup Node.js environment

.github/workflows/check-migration-file-edited.yml

@@ -1,25 +0,0 @@
-name: Check migration file edited
-
-on:
-  pull_request:
-    types: [opened, synchronize]
-    paths:
-      - 'backend/src/db/migrations/**'
-
-jobs:
-  rename:
-    runs-on: ubuntu-latest
-    steps:
-      - name: Check out repository
-        uses: actions/checkout@v4
-        with:
-          fetch-depth: 0
-
-      - name: Check any migration files are modified, renamed or duplicated.
-        run: |
-          git diff --name-status HEAD^ HEAD backend/src/db/migrations | grep '^M\|^R\|^C' || true | cut -f2 | xargs -r -n1 basename > edited_files.txt
-          if [ -s edited_files.txt ]; then
-            echo "Exiting migration files cannot be modified."
-            cat edited_files.txt
-            exit 1
-          fi

.github/workflows/update-be-new-migration-latest-timestamp.yml

@@ -0,0 +1,59 @@
+name: Rename Migrations
+
+on:
+  pull_request:
+    types: [closed]
+    paths:
+      - 'backend/src/db/migrations/**'
+
+jobs:
+  rename:
+    runs-on: ubuntu-latest
+    if: github.event.pull_request.merged == true
+
+    steps:
+      - name: Check out repository
+        uses: actions/checkout@v4
+        with:
+          fetch-depth: 0
+
+      - name: Get list of newly added files in migration folder
+        run: |
+          git diff --name-status HEAD^ HEAD backend/src/db/migrations | grep '^A' | cut -f2 | xargs -n1 basename > added_files.txt
+          if [ ! -s added_files.txt ]; then
+            echo "No new files added. Skipping"
+            echo "SKIP_RENAME=true" >> $GITHUB_ENV
+          fi
+
+      - name: Script to rename migrations
+        if: env.SKIP_RENAME != 'true'
+        run: python .github/resources/rename_migration_files.py
+
+      - name: Commit and push changes
+        if: env.SKIP_RENAME != 'true'
+        run: |
+          git config user.name github-actions
+          git config user.email github-actions@github.com
+          git add ./backend/src/db/migrations
+          rm added_files.txt
+          git commit -m "chore: renamed new migration files to latest timestamp (gh-action)"
+
+      - name: Get PR details
+        id: pr_details
+        run: |
+          PR_NUMBER=${{ github.event.pull_request.number }}
+          PR_MERGER=$(curl -s "https://api.github.com/repos/${{ github.repository }}/pulls/$PR_NUMBER" | jq -r '.merged_by.login')
+          
+          echo "PR Number: $PR_NUMBER"
+          echo "PR Merger: $PR_MERGER"
+          echo "pr_merger=$PR_MERGER" >> $GITHUB_OUTPUT
+
+      - name: Create Pull Request
+        if: env.SKIP_RENAME != 'true'
+        uses: peter-evans/create-pull-request@v6
+        with:
+          token: ${{ secrets.GITHUB_TOKEN }}
+          commit-message: 'chore: renamed new migration files to latest UTC (gh-action)'
+          title: 'GH Action: rename new migration file timestamp'
+          branch-suffix: timestamp
+          reviewers: ${{ steps.pr_details.outputs.pr_merger }}

.gitignore

@@ -69,4 +69,3 @@ frontend-build
 *.tgz
 cli/infisical-merge
 cli/test/infisical-merge
-/backend/binary

.infisicalignore

@@ -5,4 +5,3 @@ frontend/src/views/Project/MembersPage/components/MemberListTab/MemberRoleForm/M
 frontend/src/views/Project/MembersPage/components/MemberListTab/MemberRoleForm/SpecificPrivilegeSection.tsx:generic-api-key:292
 docs/self-hosting/configuration/envars.mdx:generic-api-key:106
 frontend/src/views/Project/MembersPage/components/MemberListTab/MemberRoleForm/SpecificPrivilegeSection.tsx:generic-api-key:451
-docs/mint.json:generic-api-key:651

backend/babel.config.json

@@ -1,4 +0,0 @@
-{
-  "presets": ["@babel/preset-env", "@babel/preset-react"],
-  "plugins": ["@babel/plugin-syntax-import-attributes", "babel-plugin-transform-import-meta"]
-}

backend/e2e-test/vitest-environment-knex.ts

@@ -3,6 +3,7 @@ import "ts-node/register";
 
 import dotenv from "dotenv";
 import jwt from "jsonwebtoken";
+import knex from "knex";
 import path from "path";
 
 import { seedData1 } from "@app/db/seed-data";
@@ -14,7 +15,6 @@ import { AuthMethod, AuthTokenType } from "@app/services/auth/auth-type";
 import { mockQueue } from "./mocks/queue";
 import { mockSmtpServer } from "./mocks/smtp";
 import { mockKeyStore } from "./mocks/keystore";
-import { initDbConnection } from "@app/db";
 
 dotenv.config({ path: path.join(__dirname, "../../.env.test"), debug: true });
 export default {
@@ -23,21 +23,23 @@ export default {
   async setup() {
     const logger = await initLogger();
     const cfg = initEnvConfig(logger);
-    const db = initDbConnection({
-      dbConnectionUri: cfg.DB_CONNECTION_URI,
-      dbRootCert: cfg.DB_ROOT_CERT
-    });
-
-    try {
-      await db.migrate.latest({
+    const db = knex({
+      client: "pg",
+      connection: cfg.DB_CONNECTION_URI,
+      migrations: {
         directory: path.join(__dirname, "../src/db/migrations"),
         extension: "ts",
         tableName: "infisical_migrations"
-      });
-      await db.seed.run({
+      },
+      seeds: {
         directory: path.join(__dirname, "../src/db/seeds"),
         extension: "ts"
-      });
+      }
+    });
+
+    try {
+      await db.migrate.latest();
+      await db.seed.run();
       const smtp = mockSmtpServer();
       const queue = mockQueue();
       const keyStore = mockKeyStore();
@@ -72,14 +74,7 @@ export default {
         // @ts-expect-error type
         delete globalThis.jwtToken;
         // called after all tests with this env have been run
-        await db.migrate.rollback(
-          {
-            directory: path.join(__dirname, "../src/db/migrations"),
-            extension: "ts",
-            tableName: "infisical_migrations"
-          },
-          true
-        );
+        await db.migrate.rollback({}, true);
         await db.destroy();
       }
     };

backend/package.json

@@ -3,39 +3,11 @@
   "version": "1.0.0",
   "description": "",
   "main": "./dist/main.mjs",
-  "bin": "dist/main.js",
-  "pkg": {
-    "scripts": [
-      "dist/**/*.js",
-      "../frontend/node_modules/next/**/*.js",
-      "../frontend/.next/*/**/*.js",
-      "../frontend/node_modules/next/dist/server/**/*.js",
-      "../frontend/node_modules/@fortawesome/fontawesome-svg-core/**/*.js"
-    ],
-    "assets": [
-      "dist/**",
-      "!dist/**/*.js",
-      "node_modules/**",
-      "../frontend/node_modules/**",
-      "../frontend/.next/**",
-      "!../frontend/node_modules/next/dist/server/**/*.js",
-      "../frontend/node_modules/@fortawesome/fontawesome-svg-core/**/*",
-      "../frontend/public/**"
-    ],
-    "outputPath": "binary"
-  },
   "scripts": {
-    "binary:build": "npm run binary:clean && npm run build:frontend && npm run build && npm run binary:babel-frontend && npm run binary:babel-backend && npm run binary:rename-imports",
-    "binary:package": "pkg --no-bytecode --public-packages \"*\" --public --target host .",
-    "binary:babel-backend": " babel ./dist -d ./dist",
-    "binary:babel-frontend": "babel --copy-files ../frontend/.next/server -d ../frontend/.next/server",
-    "binary:clean": "rm -rf ./dist && rm -rf ./binary",
-    "binary:rename-imports": "ts-node ./scripts/rename-mjs.ts",
     "test": "echo \"Error: no test specified\" && exit 1",
     "dev": "tsx watch --clear-screen=false ./src/main.ts  | pino-pretty --colorize --colorizeObjects --singleLine",
     "dev:docker": "nodemon",
     "build": "tsup",
-    "build:frontend": "npm run build --prefix ../frontend",
     "start": "node dist/main.mjs",
     "type:check": "tsc --noEmit",
     "lint:fix": "eslint --fix --ext js,ts ./src",
@@ -59,11 +31,6 @@
   "author": "",
   "license": "ISC",
   "devDependencies": {
-    "@babel/cli": "^7.18.10",
-    "@babel/core": "^7.18.10",
-    "@babel/plugin-syntax-import-attributes": "^7.24.7",
-    "@babel/preset-env": "^7.18.10",
-    "@babel/preset-react": "^7.24.7",
     "@types/bcrypt": "^5.0.2",
     "@types/jmespath": "^0.15.2",
     "@types/jsonwebtoken": "^9.0.5",
@@ -81,8 +48,6 @@
     "@types/uuid": "^9.0.7",
     "@typescript-eslint/eslint-plugin": "^6.20.0",
     "@typescript-eslint/parser": "^6.20.0",
-    "@yao-pkg/pkg": "^5.12.0",
-    "babel-plugin-transform-import-meta": "^2.2.1",
     "eslint": "^8.56.0",
     "eslint-config-airbnb-base": "^15.0.0",
     "eslint-config-airbnb-typescript": "^17.1.0",
@@ -95,7 +60,7 @@
     "pino-pretty": "^10.2.3",
     "prompt-sync": "^4.2.0",
     "rimraf": "^5.0.5",
-    "ts-node": "^10.9.2",
+    "ts-node": "^10.9.1",
     "tsc-alias": "^1.8.8",
     "tsconfig-paths": "^4.2.0",
     "tsup": "^8.0.1",
@@ -106,9 +71,7 @@
   },
   "dependencies": {
     "@aws-sdk/client-iam": "^3.525.0",
-    "@aws-sdk/client-kms": "^3.609.0",
     "@aws-sdk/client-secrets-manager": "^3.504.0",
-    "@aws-sdk/client-sts": "^3.600.0",
     "@casl/ability": "^6.5.0",
     "@fastify/cookie": "^9.3.1",
     "@fastify/cors": "^8.5.0",
@@ -126,8 +89,7 @@
     "@peculiar/asn1-schema": "^2.3.8",
     "@peculiar/x509": "^1.10.0",
     "@serdnam/pino-cloudwatch-transport": "^1.0.4",
-    "@sindresorhus/slugify": "1.1.0",
-    "@team-plain/typescript-sdk": "^4.6.1",
+    "@sindresorhus/slugify": "^2.2.1",
     "@ucast/mongo2js": "^1.3.4",
     "ajv": "^8.12.0",
     "argon2": "^0.31.2",
@@ -137,7 +99,6 @@
     "bcrypt": "^5.1.1",
     "bullmq": "^5.4.2",
     "cassandra-driver": "^4.7.2",
-    "connect-redis": "^7.1.1",
     "cron": "^3.1.7",
     "dotenv": "^16.4.1",
     "fastify": "^4.26.0",
@@ -149,16 +110,14 @@
     "jmespath": "^0.16.0",
     "jsonwebtoken": "^9.0.2",
     "jsrp": "^0.2.4",
-    "jwks-rsa": "^3.1.0",
     "knex": "^3.0.1",
     "ldapjs": "^3.0.7",
     "libsodium-wrappers": "^0.7.13",
     "lodash.isequal": "^4.5.0",
     "ms": "^2.1.3",
     "mysql2": "^3.9.8",
-    "nanoid": "^3.3.4",
+    "nanoid": "^5.0.4",
     "nodemailer": "^6.9.9",
-    "openid-client": "^5.6.5",
     "ora": "^7.0.1",
     "oracledb": "^6.4.0",
     "passport-github": "^1.1.0",
@@ -172,7 +131,6 @@
     "posthog-node": "^3.6.2",
     "probot": "^13.0.0",
     "smee-client": "^2.0.0",
-    "tedious": "^18.2.1",
     "tweetnacl": "^1.0.3",
     "tweetnacl-util": "^0.15.1",
     "uuid": "^9.0.1",

backend/scripts/create-migration.ts

@@ -2,14 +2,13 @@
 import { execSync } from "child_process";
 import path from "path";
 import promptSync from "prompt-sync";
-import slugify from "@sindresorhus/slugify"
 
 const prompt = promptSync({ sigint: true });
 
 const migrationName = prompt("Enter name for migration: ");
 
 // Remove spaces from migration name and replace with hyphens
-const formattedMigrationName = slugify(migrationName);
+const formattedMigrationName = migrationName.replace(/\s+/g, "-");
 
 execSync(
   `npx knex migrate:make --knexfile ${path.join(__dirname, "../src/db/knexfile.ts")} -x ts ${formattedMigrationName}`,

backend/scripts/rename-mjs.ts

@@ -1,27 +0,0 @@
-/* eslint-disable @typescript-eslint/no-shadow */
-import fs from "node:fs";
-import path from "node:path";
-
-function replaceMjsOccurrences(directory: string) {
-  fs.readdir(directory, (err, files) => {
-    if (err) throw err;
-    files.forEach((file) => {
-      const filePath = path.join(directory, file);
-      if (fs.statSync(filePath).isDirectory()) {
-        replaceMjsOccurrences(filePath);
-      } else {
-        fs.readFile(filePath, "utf8", (err, data) => {
-          if (err) throw err;
-          const result = data.replace(/\.mjs/g, ".js");
-          fs.writeFile(filePath, result, "utf8", (err) => {
-            if (err) throw err;
-            // eslint-disable-next-line no-console
-            console.log(`Updated: ${filePath}`);
-          });
-        });
-      }
-    });
-  });
-}
-
-replaceMjsOccurrences("dist");

backend/src/@types/fastify.d.ts

@@ -9,12 +9,10 @@ import { TAuditLogStreamServiceFactory } from "@app/ee/services/audit-log-stream
 import { TCertificateAuthorityCrlServiceFactory } from "@app/ee/services/certificate-authority-crl/certificate-authority-crl-service";
 import { TDynamicSecretServiceFactory } from "@app/ee/services/dynamic-secret/dynamic-secret-service";
 import { TDynamicSecretLeaseServiceFactory } from "@app/ee/services/dynamic-secret-lease/dynamic-secret-lease-service";
-import { TExternalKmsServiceFactory } from "@app/ee/services/external-kms/external-kms-service";
 import { TGroupServiceFactory } from "@app/ee/services/group/group-service";
 import { TIdentityProjectAdditionalPrivilegeServiceFactory } from "@app/ee/services/identity-project-additional-privilege/identity-project-additional-privilege-service";
 import { TLdapConfigServiceFactory } from "@app/ee/services/ldap-config/ldap-config-service";
 import { TLicenseServiceFactory } from "@app/ee/services/license/license-service";
-import { TOidcConfigServiceFactory } from "@app/ee/services/oidc/oidc-config-service";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
 import { TProjectUserAdditionalPrivilegeServiceFactory } from "@app/ee/services/project-user-additional-privilege/project-user-additional-privilege-service";
 import { TRateLimitServiceFactory } from "@app/ee/services/rate-limit/rate-limit-service";
@@ -42,9 +40,7 @@ import { TIdentityAwsAuthServiceFactory } from "@app/services/identity-aws-auth/
 import { TIdentityAzureAuthServiceFactory } from "@app/services/identity-azure-auth/identity-azure-auth-service";
 import { TIdentityGcpAuthServiceFactory } from "@app/services/identity-gcp-auth/identity-gcp-auth-service";
 import { TIdentityKubernetesAuthServiceFactory } from "@app/services/identity-kubernetes-auth/identity-kubernetes-auth-service";
-import { TIdentityOidcAuthServiceFactory } from "@app/services/identity-oidc-auth/identity-oidc-auth-service";
 import { TIdentityProjectServiceFactory } from "@app/services/identity-project/identity-project-service";
-import { TIdentityTokenAuthServiceFactory } from "@app/services/identity-token-auth/identity-token-auth-service";
 import { TIdentityUaServiceFactory } from "@app/services/identity-ua/identity-ua-service";
 import { TIntegrationServiceFactory } from "@app/services/integration/integration-service";
 import { TIntegrationAuthServiceFactory } from "@app/services/integration-auth/integration-auth-service";
@@ -68,7 +64,6 @@ import { TSuperAdminServiceFactory } from "@app/services/super-admin/super-admin
 import { TTelemetryServiceFactory } from "@app/services/telemetry/telemetry-service";
 import { TUserDALFactory } from "@app/services/user/user-dal";
 import { TUserServiceFactory } from "@app/services/user/user-service";
-import { TUserEngagementServiceFactory } from "@app/services/user-engagement/user-engagement-service";
 import { TWebhookServiceFactory } from "@app/services/webhook/webhook-service";
 
 declare module "fastify" {
@@ -107,7 +102,6 @@ declare module "fastify" {
       permission: TPermissionServiceFactory;
       org: TOrgServiceFactory;
       orgRole: TOrgRoleServiceFactory;
-      oidc: TOidcConfigServiceFactory;
       superAdmin: TSuperAdminServiceFactory;
       user: TUserServiceFactory;
       group: TGroupServiceFactory;
@@ -131,13 +125,11 @@ declare module "fastify" {
       identity: TIdentityServiceFactory;
       identityAccessToken: TIdentityAccessTokenServiceFactory;
       identityProject: TIdentityProjectServiceFactory;
-      identityTokenAuth: TIdentityTokenAuthServiceFactory;
       identityUa: TIdentityUaServiceFactory;
       identityKubernetesAuth: TIdentityKubernetesAuthServiceFactory;
       identityGcpAuth: TIdentityGcpAuthServiceFactory;
       identityAwsAuth: TIdentityAwsAuthServiceFactory;
       identityAzureAuth: TIdentityAzureAuthServiceFactory;
-      identityOidcAuth: TIdentityOidcAuthServiceFactory;
       accessApprovalPolicy: TAccessApprovalPolicyServiceFactory;
       accessApprovalRequest: TAccessApprovalRequestServiceFactory;
       secretApprovalPolicy: TSecretApprovalPolicyServiceFactory;
@@ -163,8 +155,6 @@ declare module "fastify" {
       identityProjectAdditionalPrivilege: TIdentityProjectAdditionalPrivilegeServiceFactory;
       secretSharing: TSecretSharingServiceFactory;
       rateLimit: TRateLimitServiceFactory;
-      userEngagement: TUserEngagementServiceFactory;
-      externalKms: TExternalKmsServiceFactory;
     };
     // this is exclusive use for middlewares in which we need to inject data
     // everywhere else access using service layer

backend/src/@types/knex.d.ts

@@ -1,4 +1,4 @@
-import { Knex as KnexOriginal } from "knex";
+import { Knex } from "knex";
 
 import {
   TableName,
@@ -59,9 +59,6 @@ import {
   TDynamicSecrets,
   TDynamicSecretsInsert,
   TDynamicSecretsUpdate,
-  TExternalKms,
-  TExternalKmsInsert,
-  TExternalKmsUpdate,
   TGitAppInstallSessions,
   TGitAppInstallSessionsInsert,
   TGitAppInstallSessionsUpdate,
@@ -95,9 +92,6 @@ import {
   TIdentityKubernetesAuths,
   TIdentityKubernetesAuthsInsert,
   TIdentityKubernetesAuthsUpdate,
-  TIdentityOidcAuths,
-  TIdentityOidcAuthsInsert,
-  TIdentityOidcAuthsUpdate,
   TIdentityOrgMemberships,
   TIdentityOrgMembershipsInsert,
   TIdentityOrgMembershipsUpdate,
@@ -110,9 +104,6 @@ import {
   TIdentityProjectMemberships,
   TIdentityProjectMembershipsInsert,
   TIdentityProjectMembershipsUpdate,
-  TIdentityTokenAuths,
-  TIdentityTokenAuthsInsert,
-  TIdentityTokenAuthsUpdate,
   TIdentityUaClientSecrets,
   TIdentityUaClientSecretsInsert,
   TIdentityUaClientSecretsUpdate,
@@ -128,9 +119,6 @@ import {
   TIntegrations,
   TIntegrationsInsert,
   TIntegrationsUpdate,
-  TInternalKms,
-  TInternalKmsInsert,
-  TInternalKmsUpdate,
   TKmsKeys,
   TKmsKeysInsert,
   TKmsKeysUpdate,
@@ -146,9 +134,6 @@ import {
   TLdapGroupMaps,
   TLdapGroupMapsInsert,
   TLdapGroupMapsUpdate,
-  TOidcConfigs,
-  TOidcConfigsInsert,
-  TOidcConfigsUpdate,
   TOrganizations,
   TOrganizationsInsert,
   TOrganizationsUpdate,
@@ -292,383 +277,317 @@ import {
   TWebhooksUpdate
 } from "@app/db/schemas";
 
-declare module "knex" {
-  namespace Knex {
-    interface QueryInterface {
-      primaryNode(): KnexOriginal;
-      replicaNode(): KnexOriginal;
-    }
-  }
-}
-
 declare module "knex/types/tables" {
   interface Tables {
-    [TableName.Users]: KnexOriginal.CompositeTableType<TUsers, TUsersInsert, TUsersUpdate>;
-    [TableName.Groups]: KnexOriginal.CompositeTableType<TGroups, TGroupsInsert, TGroupsUpdate>;
-    [TableName.CertificateAuthority]: KnexOriginal.CompositeTableType<
+    [TableName.Users]: Knex.CompositeTableType<TUsers, TUsersInsert, TUsersUpdate>;
+    [TableName.Groups]: Knex.CompositeTableType<TGroups, TGroupsInsert, TGroupsUpdate>;
+    [TableName.CertificateAuthority]: Knex.CompositeTableType<
       TCertificateAuthorities,
       TCertificateAuthoritiesInsert,
       TCertificateAuthoritiesUpdate
     >;
-    [TableName.CertificateAuthorityCert]: KnexOriginal.CompositeTableType<
+    [TableName.CertificateAuthorityCert]: Knex.CompositeTableType<
       TCertificateAuthorityCerts,
       TCertificateAuthorityCertsInsert,
       TCertificateAuthorityCertsUpdate
     >;
-    [TableName.CertificateAuthoritySecret]: KnexOriginal.CompositeTableType<
+    [TableName.CertificateAuthoritySecret]: Knex.CompositeTableType<
       TCertificateAuthoritySecret,
       TCertificateAuthoritySecretInsert,
       TCertificateAuthoritySecretUpdate
     >;
-    [TableName.CertificateAuthorityCrl]: KnexOriginal.CompositeTableType<
+    [TableName.CertificateAuthorityCrl]: Knex.CompositeTableType<
       TCertificateAuthorityCrl,
       TCertificateAuthorityCrlInsert,
       TCertificateAuthorityCrlUpdate
     >;
-    [TableName.Certificate]: KnexOriginal.CompositeTableType<TCertificates, TCertificatesInsert, TCertificatesUpdate>;
-    [TableName.CertificateBody]: KnexOriginal.CompositeTableType<
+    [TableName.Certificate]: Knex.CompositeTableType<TCertificates, TCertificatesInsert, TCertificatesUpdate>;
+    [TableName.CertificateBody]: Knex.CompositeTableType<
       TCertificateBodies,
       TCertificateBodiesInsert,
       TCertificateBodiesUpdate
     >;
-    [TableName.CertificateSecret]: KnexOriginal.CompositeTableType<
+    [TableName.CertificateSecret]: Knex.CompositeTableType<
       TCertificateSecrets,
       TCertificateSecretsInsert,
       TCertificateSecretsUpdate
     >;
-    [TableName.UserGroupMembership]: KnexOriginal.CompositeTableType<
+    [TableName.UserGroupMembership]: Knex.CompositeTableType<
       TUserGroupMembership,
       TUserGroupMembershipInsert,
       TUserGroupMembershipUpdate
     >;
-    [TableName.GroupProjectMembership]: KnexOriginal.CompositeTableType<
+    [TableName.GroupProjectMembership]: Knex.CompositeTableType<
       TGroupProjectMemberships,
       TGroupProjectMembershipsInsert,
       TGroupProjectMembershipsUpdate
     >;
-    [TableName.GroupProjectMembershipRole]: KnexOriginal.CompositeTableType<
+    [TableName.GroupProjectMembershipRole]: Knex.CompositeTableType<
       TGroupProjectMembershipRoles,
       TGroupProjectMembershipRolesInsert,
       TGroupProjectMembershipRolesUpdate
     >;
-    [TableName.UserAliases]: KnexOriginal.CompositeTableType<TUserAliases, TUserAliasesInsert, TUserAliasesUpdate>;
-    [TableName.UserEncryptionKey]: KnexOriginal.CompositeTableType<
+    [TableName.UserAliases]: Knex.CompositeTableType<TUserAliases, TUserAliasesInsert, TUserAliasesUpdate>;
+    [TableName.UserEncryptionKey]: Knex.CompositeTableType<
       TUserEncryptionKeys,
       TUserEncryptionKeysInsert,
       TUserEncryptionKeysUpdate
     >;
-    [TableName.AuthTokens]: KnexOriginal.CompositeTableType<TAuthTokens, TAuthTokensInsert, TAuthTokensUpdate>;
-    [TableName.AuthTokenSession]: KnexOriginal.CompositeTableType<
+    [TableName.AuthTokens]: Knex.CompositeTableType<TAuthTokens, TAuthTokensInsert, TAuthTokensUpdate>;
+    [TableName.AuthTokenSession]: Knex.CompositeTableType<
       TAuthTokenSessions,
       TAuthTokenSessionsInsert,
       TAuthTokenSessionsUpdate
     >;
-    [TableName.BackupPrivateKey]: KnexOriginal.CompositeTableType<
+    [TableName.BackupPrivateKey]: Knex.CompositeTableType<
       TBackupPrivateKey,
       TBackupPrivateKeyInsert,
       TBackupPrivateKeyUpdate
     >;
-    [TableName.Organization]: KnexOriginal.CompositeTableType<
-      TOrganizations,
-      TOrganizationsInsert,
-      TOrganizationsUpdate
-    >;
-    [TableName.OrgMembership]: KnexOriginal.CompositeTableType<
-      TOrgMemberships,
-      TOrgMembershipsInsert,
-      TOrgMembershipsUpdate
-    >;
-    [TableName.OrgRoles]: KnexOriginal.CompositeTableType<TOrgRoles, TOrgRolesInsert, TOrgRolesUpdate>;
-    [TableName.IncidentContact]: KnexOriginal.CompositeTableType<
+    [TableName.Organization]: Knex.CompositeTableType<TOrganizations, TOrganizationsInsert, TOrganizationsUpdate>;
+    [TableName.OrgMembership]: Knex.CompositeTableType<TOrgMemberships, TOrgMembershipsInsert, TOrgMembershipsUpdate>;
+    [TableName.OrgRoles]: Knex.CompositeTableType<TOrgRoles, TOrgRolesInsert, TOrgRolesUpdate>;
+    [TableName.IncidentContact]: Knex.CompositeTableType<
       TIncidentContacts,
       TIncidentContactsInsert,
       TIncidentContactsUpdate
     >;
-    [TableName.UserAction]: KnexOriginal.CompositeTableType<TUserActions, TUserActionsInsert, TUserActionsUpdate>;
-    [TableName.SuperAdmin]: KnexOriginal.CompositeTableType<TSuperAdmin, TSuperAdminInsert, TSuperAdminUpdate>;
-    [TableName.ApiKey]: KnexOriginal.CompositeTableType<TApiKeys, TApiKeysInsert, TApiKeysUpdate>;
-    [TableName.Project]: KnexOriginal.CompositeTableType<TProjects, TProjectsInsert, TProjectsUpdate>;
-    [TableName.ProjectMembership]: KnexOriginal.CompositeTableType<
+    [TableName.UserAction]: Knex.CompositeTableType<TUserActions, TUserActionsInsert, TUserActionsUpdate>;
+    [TableName.SuperAdmin]: Knex.CompositeTableType<TSuperAdmin, TSuperAdminInsert, TSuperAdminUpdate>;
+    [TableName.ApiKey]: Knex.CompositeTableType<TApiKeys, TApiKeysInsert, TApiKeysUpdate>;
+    [TableName.Project]: Knex.CompositeTableType<TProjects, TProjectsInsert, TProjectsUpdate>;
+    [TableName.ProjectMembership]: Knex.CompositeTableType<
       TProjectMemberships,
       TProjectMembershipsInsert,
       TProjectMembershipsUpdate
     >;
-    [TableName.Environment]: KnexOriginal.CompositeTableType<
+    [TableName.Environment]: Knex.CompositeTableType<
       TProjectEnvironments,
       TProjectEnvironmentsInsert,
       TProjectEnvironmentsUpdate
     >;
-    [TableName.ProjectBot]: KnexOriginal.CompositeTableType<TProjectBots, TProjectBotsInsert, TProjectBotsUpdate>;
-    [TableName.ProjectUserMembershipRole]: KnexOriginal.CompositeTableType<
+    [TableName.ProjectBot]: Knex.CompositeTableType<TProjectBots, TProjectBotsInsert, TProjectBotsUpdate>;
+    [TableName.ProjectUserMembershipRole]: Knex.CompositeTableType<
       TProjectUserMembershipRoles,
       TProjectUserMembershipRolesInsert,
       TProjectUserMembershipRolesUpdate
     >;
-    [TableName.ProjectRoles]: KnexOriginal.CompositeTableType<TProjectRoles, TProjectRolesInsert, TProjectRolesUpdate>;
-    [TableName.ProjectUserAdditionalPrivilege]: KnexOriginal.CompositeTableType<
+    [TableName.ProjectRoles]: Knex.CompositeTableType<TProjectRoles, TProjectRolesInsert, TProjectRolesUpdate>;
+    [TableName.ProjectUserAdditionalPrivilege]: Knex.CompositeTableType<
       TProjectUserAdditionalPrivilege,
       TProjectUserAdditionalPrivilegeInsert,
       TProjectUserAdditionalPrivilegeUpdate
     >;
-    [TableName.ProjectKeys]: KnexOriginal.CompositeTableType<TProjectKeys, TProjectKeysInsert, TProjectKeysUpdate>;
-    [TableName.Secret]: KnexOriginal.CompositeTableType<TSecrets, TSecretsInsert, TSecretsUpdate>;
-    [TableName.SecretReference]: KnexOriginal.CompositeTableType<
+    [TableName.ProjectKeys]: Knex.CompositeTableType<TProjectKeys, TProjectKeysInsert, TProjectKeysUpdate>;
+    [TableName.Secret]: Knex.CompositeTableType<TSecrets, TSecretsInsert, TSecretsUpdate>;
+    [TableName.SecretReference]: Knex.CompositeTableType<
       TSecretReferences,
       TSecretReferencesInsert,
       TSecretReferencesUpdate
     >;
-    [TableName.SecretBlindIndex]: KnexOriginal.CompositeTableType<
+    [TableName.SecretBlindIndex]: Knex.CompositeTableType<
       TSecretBlindIndexes,
       TSecretBlindIndexesInsert,
       TSecretBlindIndexesUpdate
     >;
-    [TableName.SecretVersion]: KnexOriginal.CompositeTableType<
-      TSecretVersions,
-      TSecretVersionsInsert,
-      TSecretVersionsUpdate
-    >;
-    [TableName.SecretFolder]: KnexOriginal.CompositeTableType<
-      TSecretFolders,
-      TSecretFoldersInsert,
-      TSecretFoldersUpdate
-    >;
-    [TableName.SecretFolderVersion]: KnexOriginal.CompositeTableType<
+    [TableName.SecretVersion]: Knex.CompositeTableType<TSecretVersions, TSecretVersionsInsert, TSecretVersionsUpdate>;
+    [TableName.SecretFolder]: Knex.CompositeTableType<TSecretFolders, TSecretFoldersInsert, TSecretFoldersUpdate>;
+    [TableName.SecretFolderVersion]: Knex.CompositeTableType<
       TSecretFolderVersions,
       TSecretFolderVersionsInsert,
       TSecretFolderVersionsUpdate
     >;
-    [TableName.SecretSharing]: KnexOriginal.CompositeTableType<
-      TSecretSharing,
-      TSecretSharingInsert,
-      TSecretSharingUpdate
-    >;
-    [TableName.RateLimit]: KnexOriginal.CompositeTableType<TRateLimit, TRateLimitInsert, TRateLimitUpdate>;
-    [TableName.SecretTag]: KnexOriginal.CompositeTableType<TSecretTags, TSecretTagsInsert, TSecretTagsUpdate>;
-    [TableName.SecretImport]: KnexOriginal.CompositeTableType<
-      TSecretImports,
-      TSecretImportsInsert,
-      TSecretImportsUpdate
-    >;
-    [TableName.Integration]: KnexOriginal.CompositeTableType<TIntegrations, TIntegrationsInsert, TIntegrationsUpdate>;
-    [TableName.Webhook]: KnexOriginal.CompositeTableType<TWebhooks, TWebhooksInsert, TWebhooksUpdate>;
-    [TableName.ServiceToken]: KnexOriginal.CompositeTableType<
-      TServiceTokens,
-      TServiceTokensInsert,
-      TServiceTokensUpdate
-    >;
-    [TableName.IntegrationAuth]: KnexOriginal.CompositeTableType<
+    [TableName.SecretSharing]: Knex.CompositeTableType<TSecretSharing, TSecretSharingInsert, TSecretSharingUpdate>;
+    [TableName.RateLimit]: Knex.CompositeTableType<TRateLimit, TRateLimitInsert, TRateLimitUpdate>;
+    [TableName.SecretTag]: Knex.CompositeTableType<TSecretTags, TSecretTagsInsert, TSecretTagsUpdate>;
+    [TableName.SecretImport]: Knex.CompositeTableType<TSecretImports, TSecretImportsInsert, TSecretImportsUpdate>;
+    [TableName.Integration]: Knex.CompositeTableType<TIntegrations, TIntegrationsInsert, TIntegrationsUpdate>;
+    [TableName.Webhook]: Knex.CompositeTableType<TWebhooks, TWebhooksInsert, TWebhooksUpdate>;
+    [TableName.ServiceToken]: Knex.CompositeTableType<TServiceTokens, TServiceTokensInsert, TServiceTokensUpdate>;
+    [TableName.IntegrationAuth]: Knex.CompositeTableType<
       TIntegrationAuths,
       TIntegrationAuthsInsert,
       TIntegrationAuthsUpdate
     >;
-    [TableName.Identity]: KnexOriginal.CompositeTableType<TIdentities, TIdentitiesInsert, TIdentitiesUpdate>;
-    [TableName.IdentityTokenAuth]: KnexOriginal.CompositeTableType<
-      TIdentityTokenAuths,
-      TIdentityTokenAuthsInsert,
-      TIdentityTokenAuthsUpdate
-    >;
-    [TableName.IdentityUniversalAuth]: KnexOriginal.CompositeTableType<
+    [TableName.Identity]: Knex.CompositeTableType<TIdentities, TIdentitiesInsert, TIdentitiesUpdate>;
+    [TableName.IdentityUniversalAuth]: Knex.CompositeTableType<
       TIdentityUniversalAuths,
       TIdentityUniversalAuthsInsert,
       TIdentityUniversalAuthsUpdate
     >;
-    [TableName.IdentityKubernetesAuth]: KnexOriginal.CompositeTableType<
+    [TableName.IdentityKubernetesAuth]: Knex.CompositeTableType<
       TIdentityKubernetesAuths,
       TIdentityKubernetesAuthsInsert,
       TIdentityKubernetesAuthsUpdate
     >;
-    [TableName.IdentityGcpAuth]: KnexOriginal.CompositeTableType<
+    [TableName.IdentityGcpAuth]: Knex.CompositeTableType<
       TIdentityGcpAuths,
       TIdentityGcpAuthsInsert,
       TIdentityGcpAuthsUpdate
     >;
-    [TableName.IdentityAwsAuth]: KnexOriginal.CompositeTableType<
+    [TableName.IdentityAwsAuth]: Knex.CompositeTableType<
       TIdentityAwsAuths,
       TIdentityAwsAuthsInsert,
       TIdentityAwsAuthsUpdate
     >;
-    [TableName.IdentityAzureAuth]: KnexOriginal.CompositeTableType<
+    [TableName.IdentityAzureAuth]: Knex.CompositeTableType<
       TIdentityAzureAuths,
       TIdentityAzureAuthsInsert,
       TIdentityAzureAuthsUpdate
     >;
-    [TableName.IdentityOidcAuth]: KnexOriginal.CompositeTableType<
-      TIdentityOidcAuths,
-      TIdentityOidcAuthsInsert,
-      TIdentityOidcAuthsUpdate
-    >;
-    [TableName.IdentityUaClientSecret]: KnexOriginal.CompositeTableType<
+    [TableName.IdentityUaClientSecret]: Knex.CompositeTableType<
       TIdentityUaClientSecrets,
       TIdentityUaClientSecretsInsert,
       TIdentityUaClientSecretsUpdate
     >;
-    [TableName.IdentityAccessToken]: KnexOriginal.CompositeTableType<
+    [TableName.IdentityAccessToken]: Knex.CompositeTableType<
       TIdentityAccessTokens,
       TIdentityAccessTokensInsert,
       TIdentityAccessTokensUpdate
     >;
-    [TableName.IdentityOrgMembership]: KnexOriginal.CompositeTableType<
+    [TableName.IdentityOrgMembership]: Knex.CompositeTableType<
       TIdentityOrgMemberships,
       TIdentityOrgMembershipsInsert,
       TIdentityOrgMembershipsUpdate
     >;
-    [TableName.IdentityProjectMembership]: KnexOriginal.CompositeTableType<
+    [TableName.IdentityProjectMembership]: Knex.CompositeTableType<
       TIdentityProjectMemberships,
       TIdentityProjectMembershipsInsert,
       TIdentityProjectMembershipsUpdate
     >;
-    [TableName.IdentityProjectMembershipRole]: KnexOriginal.CompositeTableType<
+    [TableName.IdentityProjectMembershipRole]: Knex.CompositeTableType<
       TIdentityProjectMembershipRole,
       TIdentityProjectMembershipRoleInsert,
       TIdentityProjectMembershipRoleUpdate
     >;
-    [TableName.IdentityProjectAdditionalPrivilege]: KnexOriginal.CompositeTableType<
+    [TableName.IdentityProjectAdditionalPrivilege]: Knex.CompositeTableType<
       TIdentityProjectAdditionalPrivilege,
       TIdentityProjectAdditionalPrivilegeInsert,
       TIdentityProjectAdditionalPrivilegeUpdate
     >;
 
-    [TableName.AccessApprovalPolicy]: KnexOriginal.CompositeTableType<
+    [TableName.AccessApprovalPolicy]: Knex.CompositeTableType<
       TAccessApprovalPolicies,
       TAccessApprovalPoliciesInsert,
       TAccessApprovalPoliciesUpdate
     >;
 
-    [TableName.AccessApprovalPolicyApprover]: KnexOriginal.CompositeTableType<
+    [TableName.AccessApprovalPolicyApprover]: Knex.CompositeTableType<
       TAccessApprovalPoliciesApprovers,
       TAccessApprovalPoliciesApproversInsert,
       TAccessApprovalPoliciesApproversUpdate
     >;
 
-    [TableName.AccessApprovalRequest]: KnexOriginal.CompositeTableType<
+    [TableName.AccessApprovalRequest]: Knex.CompositeTableType<
       TAccessApprovalRequests,
       TAccessApprovalRequestsInsert,
       TAccessApprovalRequestsUpdate
     >;
 
-    [TableName.AccessApprovalRequestReviewer]: KnexOriginal.CompositeTableType<
+    [TableName.AccessApprovalRequestReviewer]: Knex.CompositeTableType<
       TAccessApprovalRequestsReviewers,
       TAccessApprovalRequestsReviewersInsert,
       TAccessApprovalRequestsReviewersUpdate
     >;
 
-    [TableName.ScimToken]: KnexOriginal.CompositeTableType<TScimTokens, TScimTokensInsert, TScimTokensUpdate>;
-    [TableName.SecretApprovalPolicy]: KnexOriginal.CompositeTableType<
+    [TableName.ScimToken]: Knex.CompositeTableType<TScimTokens, TScimTokensInsert, TScimTokensUpdate>;
+    [TableName.SecretApprovalPolicy]: Knex.CompositeTableType<
       TSecretApprovalPolicies,
       TSecretApprovalPoliciesInsert,
       TSecretApprovalPoliciesUpdate
     >;
-    [TableName.SecretApprovalPolicyApprover]: KnexOriginal.CompositeTableType<
+    [TableName.SecretApprovalPolicyApprover]: Knex.CompositeTableType<
       TSecretApprovalPoliciesApprovers,
       TSecretApprovalPoliciesApproversInsert,
       TSecretApprovalPoliciesApproversUpdate
     >;
-    [TableName.SecretApprovalRequest]: KnexOriginal.CompositeTableType<
+    [TableName.SecretApprovalRequest]: Knex.CompositeTableType<
       TSecretApprovalRequests,
       TSecretApprovalRequestsInsert,
       TSecretApprovalRequestsUpdate
     >;
-    [TableName.SecretApprovalRequestReviewer]: KnexOriginal.CompositeTableType<
+    [TableName.SecretApprovalRequestReviewer]: Knex.CompositeTableType<
       TSecretApprovalRequestsReviewers,
       TSecretApprovalRequestsReviewersInsert,
       TSecretApprovalRequestsReviewersUpdate
     >;
-    [TableName.SecretApprovalRequestSecret]: KnexOriginal.CompositeTableType<
+    [TableName.SecretApprovalRequestSecret]: Knex.CompositeTableType<
       TSecretApprovalRequestsSecrets,
       TSecretApprovalRequestsSecretsInsert,
       TSecretApprovalRequestsSecretsUpdate
     >;
-    [TableName.SecretApprovalRequestSecretTag]: KnexOriginal.CompositeTableType<
+    [TableName.SecretApprovalRequestSecretTag]: Knex.CompositeTableType<
       TSecretApprovalRequestSecretTags,
       TSecretApprovalRequestSecretTagsInsert,
       TSecretApprovalRequestSecretTagsUpdate
     >;
-    [TableName.SecretRotation]: KnexOriginal.CompositeTableType<
+    [TableName.SecretRotation]: Knex.CompositeTableType<
       TSecretRotations,
       TSecretRotationsInsert,
       TSecretRotationsUpdate
     >;
-    [TableName.SecretRotationOutput]: KnexOriginal.CompositeTableType<
+    [TableName.SecretRotationOutput]: Knex.CompositeTableType<
       TSecretRotationOutputs,
       TSecretRotationOutputsInsert,
       TSecretRotationOutputsUpdate
     >;
-    [TableName.Snapshot]: KnexOriginal.CompositeTableType<
-      TSecretSnapshots,
-      TSecretSnapshotsInsert,
-      TSecretSnapshotsUpdate
-    >;
-    [TableName.SnapshotSecret]: KnexOriginal.CompositeTableType<
+    [TableName.Snapshot]: Knex.CompositeTableType<TSecretSnapshots, TSecretSnapshotsInsert, TSecretSnapshotsUpdate>;
+    [TableName.SnapshotSecret]: Knex.CompositeTableType<
       TSecretSnapshotSecrets,
       TSecretSnapshotSecretsInsert,
       TSecretSnapshotSecretsUpdate
     >;
-    [TableName.SnapshotFolder]: KnexOriginal.CompositeTableType<
+    [TableName.SnapshotFolder]: Knex.CompositeTableType<
       TSecretSnapshotFolders,
       TSecretSnapshotFoldersInsert,
       TSecretSnapshotFoldersUpdate
     >;
-    [TableName.DynamicSecret]: KnexOriginal.CompositeTableType<
-      TDynamicSecrets,
-      TDynamicSecretsInsert,
-      TDynamicSecretsUpdate
-    >;
-    [TableName.DynamicSecretLease]: KnexOriginal.CompositeTableType<
+    [TableName.DynamicSecret]: Knex.CompositeTableType<TDynamicSecrets, TDynamicSecretsInsert, TDynamicSecretsUpdate>;
+    [TableName.DynamicSecretLease]: Knex.CompositeTableType<
       TDynamicSecretLeases,
       TDynamicSecretLeasesInsert,
       TDynamicSecretLeasesUpdate
     >;
-    [TableName.SamlConfig]: KnexOriginal.CompositeTableType<TSamlConfigs, TSamlConfigsInsert, TSamlConfigsUpdate>;
-    [TableName.OidcConfig]: KnexOriginal.CompositeTableType<TOidcConfigs, TOidcConfigsInsert, TOidcConfigsUpdate>;
-    [TableName.LdapConfig]: KnexOriginal.CompositeTableType<TLdapConfigs, TLdapConfigsInsert, TLdapConfigsUpdate>;
-    [TableName.LdapGroupMap]: KnexOriginal.CompositeTableType<
-      TLdapGroupMaps,
-      TLdapGroupMapsInsert,
-      TLdapGroupMapsUpdate
-    >;
-    [TableName.OrgBot]: KnexOriginal.CompositeTableType<TOrgBots, TOrgBotsInsert, TOrgBotsUpdate>;
-    [TableName.AuditLog]: KnexOriginal.CompositeTableType<TAuditLogs, TAuditLogsInsert, TAuditLogsUpdate>;
-    [TableName.AuditLogStream]: KnexOriginal.CompositeTableType<
+    [TableName.SamlConfig]: Knex.CompositeTableType<TSamlConfigs, TSamlConfigsInsert, TSamlConfigsUpdate>;
+    [TableName.LdapConfig]: Knex.CompositeTableType<TLdapConfigs, TLdapConfigsInsert, TLdapConfigsUpdate>;
+    [TableName.LdapGroupMap]: Knex.CompositeTableType<TLdapGroupMaps, TLdapGroupMapsInsert, TLdapGroupMapsUpdate>;
+    [TableName.OrgBot]: Knex.CompositeTableType<TOrgBots, TOrgBotsInsert, TOrgBotsUpdate>;
+    [TableName.AuditLog]: Knex.CompositeTableType<TAuditLogs, TAuditLogsInsert, TAuditLogsUpdate>;
+    [TableName.AuditLogStream]: Knex.CompositeTableType<
       TAuditLogStreams,
       TAuditLogStreamsInsert,
       TAuditLogStreamsUpdate
     >;
-    [TableName.GitAppInstallSession]: KnexOriginal.CompositeTableType<
+    [TableName.GitAppInstallSession]: Knex.CompositeTableType<
       TGitAppInstallSessions,
       TGitAppInstallSessionsInsert,
       TGitAppInstallSessionsUpdate
     >;
-    [TableName.GitAppOrg]: KnexOriginal.CompositeTableType<TGitAppOrg, TGitAppOrgInsert, TGitAppOrgUpdate>;
-    [TableName.SecretScanningGitRisk]: KnexOriginal.CompositeTableType<
+    [TableName.GitAppOrg]: Knex.CompositeTableType<TGitAppOrg, TGitAppOrgInsert, TGitAppOrgUpdate>;
+    [TableName.SecretScanningGitRisk]: Knex.CompositeTableType<
       TSecretScanningGitRisks,
       TSecretScanningGitRisksInsert,
       TSecretScanningGitRisksUpdate
     >;
-    [TableName.TrustedIps]: KnexOriginal.CompositeTableType<TTrustedIps, TTrustedIpsInsert, TTrustedIpsUpdate>;
+    [TableName.TrustedIps]: Knex.CompositeTableType<TTrustedIps, TTrustedIpsInsert, TTrustedIpsUpdate>;
     // Junction tables
-    [TableName.JnSecretTag]: KnexOriginal.CompositeTableType<
+    [TableName.JnSecretTag]: Knex.CompositeTableType<
       TSecretTagJunction,
       TSecretTagJunctionInsert,
       TSecretTagJunctionUpdate
     >;
-    [TableName.SecretVersionTag]: KnexOriginal.CompositeTableType<
+    [TableName.SecretVersionTag]: Knex.CompositeTableType<
       TSecretVersionTagJunction,
       TSecretVersionTagJunctionInsert,
       TSecretVersionTagJunctionUpdate
     >;
     // KMS service
-    [TableName.KmsServerRootConfig]: KnexOriginal.CompositeTableType<
+    [TableName.KmsServerRootConfig]: Knex.CompositeTableType<
       TKmsRootConfig,
       TKmsRootConfigInsert,
       TKmsRootConfigUpdate
     >;
-    [TableName.InternalKms]: KnexOriginal.CompositeTableType<TInternalKms, TInternalKmsInsert, TInternalKmsUpdate>;
-    [TableName.ExternalKms]: KnexOriginal.CompositeTableType<TExternalKms, TExternalKmsInsert, TExternalKmsUpdate>;
-    [TableName.KmsKey]: KnexOriginal.CompositeTableType<TKmsKeys, TKmsKeysInsert, TKmsKeysUpdate>;
-    [TableName.KmsKeyVersion]: KnexOriginal.CompositeTableType<
-      TKmsKeyVersions,
-      TKmsKeyVersionsInsert,
-      TKmsKeyVersionsUpdate
-    >;
+    [TableName.KmsKey]: Knex.CompositeTableType<TKmsKeys, TKmsKeysInsert, TKmsKeysUpdate>;
+    [TableName.KmsKeyVersion]: Knex.CompositeTableType<TKmsKeyVersions, TKmsKeyVersionsInsert, TKmsKeyVersionsUpdate>;
   }
 }

backend/src/db/instance.ts

@@ -1,38 +1,8 @@
-import knex, { Knex } from "knex";
+import knex from "knex";
 
 export type TDbClient = ReturnType<typeof initDbConnection>;
-export const initDbConnection = ({
-  dbConnectionUri,
-  dbRootCert,
-  readReplicas = []
-}: {
-  dbConnectionUri: string;
-  dbRootCert?: string;
-  readReplicas?: {
-    dbConnectionUri: string;
-    dbRootCert?: string;
-  }[];
-}) => {
-  // akhilmhdh: the default Knex is knex.Knex<any, any[]>. but when assigned with knex({<config>}) the value is knex.Knex<any, unknown[]>
-  // this was causing issue with files like `snapshot-dal` `findRecursivelySnapshots` this i am explicitly putting the any and unknown[]
-  // eslint-disable-next-line
-  let db: Knex<any, unknown[]>;
-  // eslint-disable-next-line
-  let readReplicaDbs: Knex<any, unknown[]>[];
-  // @ts-expect-error the querybuilder type is expected but our intension is to return  a knex instance
-  knex.QueryBuilder.extend("primaryNode", () => {
-    return db;
-  });
-
-  // @ts-expect-error the querybuilder type is expected but our intension is to return  a knex instance
-  knex.QueryBuilder.extend("replicaNode", () => {
-    if (!readReplicaDbs.length) return db;
-
-    const selectedReplica = readReplicaDbs[Math.floor(Math.random() * readReplicaDbs.length)];
-    return selectedReplica;
-  });
-
-  db = knex({
+export const initDbConnection = ({ dbConnectionUri, dbRootCert }: { dbConnectionUri: string; dbRootCert?: string }) => {
+  const db = knex({
     client: "pg",
     connection: {
       connectionString: dbConnectionUri,
@@ -52,21 +22,5 @@ export const initDbConnection = ({
     }
   });
 
-  readReplicaDbs = readReplicas.map((el) => {
-    const replicaDbCertificate = el.dbRootCert || dbRootCert;
-    return knex({
-      client: "pg",
-      connection: {
-        connectionString: el.dbConnectionUri,
-        ssl: replicaDbCertificate
-          ? {
-              rejectUnauthorized: true,
-              ca: Buffer.from(replicaDbCertificate, "base64").toString("ascii")
-            }
-          : false
-      }
-    });
-  });
-
   return db;
 };

backend/src/db/migrations/20240609133400_private-key-handoff.ts

@@ -1,61 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  const doesPasswordFieldExist = await knex.schema.hasColumn(TableName.UserEncryptionKey, "hashedPassword");
-  const doesPrivateKeyFieldExist = await knex.schema.hasColumn(
-    TableName.UserEncryptionKey,
-    "serverEncryptedPrivateKey"
-  );
-  const doesPrivateKeyIVFieldExist = await knex.schema.hasColumn(
-    TableName.UserEncryptionKey,
-    "serverEncryptedPrivateKeyIV"
-  );
-  const doesPrivateKeyTagFieldExist = await knex.schema.hasColumn(
-    TableName.UserEncryptionKey,
-    "serverEncryptedPrivateKeyTag"
-  );
-  const doesPrivateKeyEncodingFieldExist = await knex.schema.hasColumn(
-    TableName.UserEncryptionKey,
-    "serverEncryptedPrivateKeyEncoding"
-  );
-  if (await knex.schema.hasTable(TableName.UserEncryptionKey)) {
-    await knex.schema.alterTable(TableName.UserEncryptionKey, (t) => {
-      if (!doesPasswordFieldExist) t.string("hashedPassword");
-      if (!doesPrivateKeyFieldExist) t.text("serverEncryptedPrivateKey");
-      if (!doesPrivateKeyIVFieldExist) t.text("serverEncryptedPrivateKeyIV");
-      if (!doesPrivateKeyTagFieldExist) t.text("serverEncryptedPrivateKeyTag");
-      if (!doesPrivateKeyEncodingFieldExist) t.text("serverEncryptedPrivateKeyEncoding");
-    });
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  const doesPasswordFieldExist = await knex.schema.hasColumn(TableName.UserEncryptionKey, "hashedPassword");
-  const doesPrivateKeyFieldExist = await knex.schema.hasColumn(
-    TableName.UserEncryptionKey,
-    "serverEncryptedPrivateKey"
-  );
-  const doesPrivateKeyIVFieldExist = await knex.schema.hasColumn(
-    TableName.UserEncryptionKey,
-    "serverEncryptedPrivateKeyIV"
-  );
-  const doesPrivateKeyTagFieldExist = await knex.schema.hasColumn(
-    TableName.UserEncryptionKey,
-    "serverEncryptedPrivateKeyTag"
-  );
-  const doesPrivateKeyEncodingFieldExist = await knex.schema.hasColumn(
-    TableName.UserEncryptionKey,
-    "serverEncryptedPrivateKeyEncoding"
-  );
-  if (await knex.schema.hasTable(TableName.UserEncryptionKey)) {
-    await knex.schema.alterTable(TableName.UserEncryptionKey, (t) => {
-      if (doesPasswordFieldExist) t.dropColumn("hashedPassword");
-      if (doesPrivateKeyFieldExist) t.dropColumn("serverEncryptedPrivateKey");
-      if (doesPrivateKeyIVFieldExist) t.dropColumn("serverEncryptedPrivateKeyIV");
-      if (doesPrivateKeyTagFieldExist) t.dropColumn("serverEncryptedPrivateKeyTag");
-      if (doesPrivateKeyEncodingFieldExist) t.dropColumn("serverEncryptedPrivateKeyEncoding");
-    });
-  }
-}

backend/src/db/migrations/20240618205808_org-billing-version.ts

@@ -0,0 +1,15 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  await knex.schema.alterTable(TableName.Organization, (t) => {
+    t.string("billingVersion").defaultTo("v1");
+  });
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.alterTable(TableName.Organization, (t) => {
+    t.dropColumn("billingVersion");
+  });
+}

backend/src/db/migrations/20240624161942_add-oidc-auth.ts

@@ -1,49 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  if (!(await knex.schema.hasTable(TableName.OidcConfig))) {
-    await knex.schema.createTable(TableName.OidcConfig, (tb) => {
-      tb.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
-      tb.string("discoveryURL");
-      tb.string("issuer");
-      tb.string("authorizationEndpoint");
-      tb.string("jwksUri");
-      tb.string("tokenEndpoint");
-      tb.string("userinfoEndpoint");
-      tb.text("encryptedClientId").notNullable();
-      tb.string("configurationType").notNullable();
-      tb.string("clientIdIV").notNullable();
-      tb.string("clientIdTag").notNullable();
-      tb.text("encryptedClientSecret").notNullable();
-      tb.string("clientSecretIV").notNullable();
-      tb.string("clientSecretTag").notNullable();
-      tb.string("allowedEmailDomains").nullable();
-      tb.boolean("isActive").notNullable();
-      tb.timestamps(true, true, true);
-      tb.uuid("orgId").notNullable().unique();
-      tb.foreign("orgId").references("id").inTable(TableName.Organization);
-    });
-  }
-
-  if (await knex.schema.hasTable(TableName.SuperAdmin)) {
-    if (!(await knex.schema.hasColumn(TableName.SuperAdmin, "trustOidcEmails"))) {
-      await knex.schema.alterTable(TableName.SuperAdmin, (tb) => {
-        tb.boolean("trustOidcEmails").defaultTo(false);
-      });
-    }
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  await knex.schema.dropTableIfExists(TableName.OidcConfig);
-
-  if (await knex.schema.hasTable(TableName.SuperAdmin)) {
-    if (await knex.schema.hasColumn(TableName.SuperAdmin, "trustOidcEmails")) {
-      await knex.schema.alterTable(TableName.SuperAdmin, (t) => {
-        t.dropColumn("trustOidcEmails");
-      });
-    }
-  }
-}

backend/src/db/migrations/20240624172027_default-saml-ldap-org.ts

@@ -1,27 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-const DEFAULT_AUTH_ORG_ID_FIELD = "defaultAuthOrgId";
-
-export async function up(knex: Knex): Promise<void> {
-  const hasDefaultOrgColumn = await knex.schema.hasColumn(TableName.SuperAdmin, DEFAULT_AUTH_ORG_ID_FIELD);
-
-  await knex.schema.alterTable(TableName.SuperAdmin, (t) => {
-    if (!hasDefaultOrgColumn) {
-      t.uuid(DEFAULT_AUTH_ORG_ID_FIELD).nullable();
-      t.foreign(DEFAULT_AUTH_ORG_ID_FIELD).references("id").inTable(TableName.Organization).onDelete("SET NULL");
-    }
-  });
-}
-
-export async function down(knex: Knex): Promise<void> {
-  const hasDefaultOrgColumn = await knex.schema.hasColumn(TableName.SuperAdmin, DEFAULT_AUTH_ORG_ID_FIELD);
-
-  await knex.schema.alterTable(TableName.SuperAdmin, (t) => {
-    if (hasDefaultOrgColumn) {
-      t.dropForeign([DEFAULT_AUTH_ORG_ID_FIELD]);
-      t.dropColumn(DEFAULT_AUTH_ORG_ID_FIELD);
-    }
-  });
-}

backend/src/db/migrations/20240624221840_certificate-alt-names.ts

@@ -1,24 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  if (await knex.schema.hasTable(TableName.Certificate)) {
-    const hasAltNamesColumn = await knex.schema.hasColumn(TableName.Certificate, "altNames");
-    if (!hasAltNamesColumn) {
-      await knex.schema.alterTable(TableName.Certificate, (t) => {
-        t.string("altNames").defaultTo("");
-      });
-    }
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  if (await knex.schema.hasTable(TableName.Certificate)) {
-    if (await knex.schema.hasColumn(TableName.Certificate, "altNames")) {
-      await knex.schema.alterTable(TableName.Certificate, (t) => {
-        t.dropColumn("altNames");
-      });
-    }
-  }
-}

backend/src/db/migrations/20240626111536_integration-auth-aws-assume-role.ts

@@ -1,35 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  const hasAwsAssumeRoleCipherText = await knex.schema.hasColumn(
-    TableName.IntegrationAuth,
-    "awsAssumeIamRoleArnCipherText"
-  );
-  const hasAwsAssumeRoleIV = await knex.schema.hasColumn(TableName.IntegrationAuth, "awsAssumeIamRoleArnIV");
-  const hasAwsAssumeRoleTag = await knex.schema.hasColumn(TableName.IntegrationAuth, "awsAssumeIamRoleArnTag");
-  if (await knex.schema.hasTable(TableName.IntegrationAuth)) {
-    await knex.schema.alterTable(TableName.IntegrationAuth, (t) => {
-      if (!hasAwsAssumeRoleCipherText) t.text("awsAssumeIamRoleArnCipherText");
-      if (!hasAwsAssumeRoleIV) t.text("awsAssumeIamRoleArnIV");
-      if (!hasAwsAssumeRoleTag) t.text("awsAssumeIamRoleArnTag");
-    });
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  const hasAwsAssumeRoleCipherText = await knex.schema.hasColumn(
-    TableName.IntegrationAuth,
-    "awsAssumeIamRoleArnCipherText"
-  );
-  const hasAwsAssumeRoleIV = await knex.schema.hasColumn(TableName.IntegrationAuth, "awsAssumeIamRoleArnIV");
-  const hasAwsAssumeRoleTag = await knex.schema.hasColumn(TableName.IntegrationAuth, "awsAssumeIamRoleArnTag");
-  if (await knex.schema.hasTable(TableName.IntegrationAuth)) {
-    await knex.schema.alterTable(TableName.IntegrationAuth, (t) => {
-      if (hasAwsAssumeRoleCipherText) t.dropColumn("awsAssumeIamRoleArnCipherText");
-      if (hasAwsAssumeRoleIV) t.dropColumn("awsAssumeIamRoleArnIV");
-      if (hasAwsAssumeRoleTag) t.dropColumn("awsAssumeIamRoleArnTag");
-    });
-  }
-}

backend/src/db/migrations/20240626115035_admin-login-method-config.ts

@@ -1,19 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  if (!(await knex.schema.hasColumn(TableName.SuperAdmin, "enabledLoginMethods"))) {
-    await knex.schema.alterTable(TableName.SuperAdmin, (tb) => {
-      tb.specificType("enabledLoginMethods", "text[]");
-    });
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  if (await knex.schema.hasColumn(TableName.SuperAdmin, "enabledLoginMethods")) {
-    await knex.schema.alterTable(TableName.SuperAdmin, (t) => {
-      t.dropColumn("enabledLoginMethods");
-    });
-  }
-}

backend/src/db/migrations/20240626171758_add-ldap-unique-user-attribute.ts

@@ -1,19 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  if (!(await knex.schema.hasColumn(TableName.LdapConfig, "uniqueUserAttribute"))) {
-    await knex.schema.alterTable(TableName.LdapConfig, (tb) => {
-      tb.string("uniqueUserAttribute").notNullable().defaultTo("");
-    });
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  if (await knex.schema.hasColumn(TableName.LdapConfig, "uniqueUserAttribute")) {
-    await knex.schema.alterTable(TableName.LdapConfig, (t) => {
-      t.dropColumn("uniqueUserAttribute");
-    });
-  }
-}

backend/src/db/migrations/20240626171943_configurable-audit-log-retention.ts

@@ -1,19 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  if (!(await knex.schema.hasColumn(TableName.Project, "auditLogsRetentionDays"))) {
-    await knex.schema.alterTable(TableName.Project, (tb) => {
-      tb.integer("auditLogsRetentionDays").nullable();
-    });
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  if (await knex.schema.hasColumn(TableName.Project, "auditLogsRetentionDays")) {
-    await knex.schema.alterTable(TableName.Project, (t) => {
-      t.dropColumn("auditLogsRetentionDays");
-    });
-  }
-}

backend/src/db/migrations/20240627173239_add-oidc-updated-at-trigger.ts

@@ -1,12 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-import { createOnUpdateTrigger, dropOnUpdateTrigger } from "../utils";
-
-export async function up(knex: Knex): Promise<void> {
-  await createOnUpdateTrigger(knex, TableName.OidcConfig);
-}
-
-export async function down(knex: Knex): Promise<void> {
-  await dropOnUpdateTrigger(knex, TableName.OidcConfig);
-}

backend/src/db/migrations/20240701143900_member-project-favorite.ts

@@ -1,19 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  if (!(await knex.schema.hasColumn(TableName.OrgMembership, "projectFavorites"))) {
-    await knex.schema.alterTable(TableName.OrgMembership, (tb) => {
-      tb.specificType("projectFavorites", "text[]");
-    });
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  if (await knex.schema.hasColumn(TableName.OrgMembership, "projectFavorites")) {
-    await knex.schema.alterTable(TableName.OrgMembership, (t) => {
-      t.dropColumn("projectFavorites");
-    });
-  }
-}

backend/src/db/migrations/20240702055253_add-encrypted-webhook-url.ts

@@ -1,53 +0,0 @@
-import { Knex } from "knex";
-
-import { WebhookType } from "@app/services/webhook/webhook-types";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  const hasUrlCipherText = await knex.schema.hasColumn(TableName.Webhook, "urlCipherText");
-  const hasUrlIV = await knex.schema.hasColumn(TableName.Webhook, "urlIV");
-  const hasUrlTag = await knex.schema.hasColumn(TableName.Webhook, "urlTag");
-  const hasType = await knex.schema.hasColumn(TableName.Webhook, "type");
-
-  if (await knex.schema.hasTable(TableName.Webhook)) {
-    await knex.schema.alterTable(TableName.Webhook, (tb) => {
-      if (!hasUrlCipherText) {
-        tb.text("urlCipherText");
-      }
-      if (!hasUrlIV) {
-        tb.string("urlIV");
-      }
-      if (!hasUrlTag) {
-        tb.string("urlTag");
-      }
-      if (!hasType) {
-        tb.string("type").defaultTo(WebhookType.GENERAL);
-      }
-    });
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  const hasUrlCipherText = await knex.schema.hasColumn(TableName.Webhook, "urlCipherText");
-  const hasUrlIV = await knex.schema.hasColumn(TableName.Webhook, "urlIV");
-  const hasUrlTag = await knex.schema.hasColumn(TableName.Webhook, "urlTag");
-  const hasType = await knex.schema.hasColumn(TableName.Webhook, "type");
-
-  if (await knex.schema.hasTable(TableName.Webhook)) {
-    await knex.schema.alterTable(TableName.Webhook, (t) => {
-      if (hasUrlCipherText) {
-        t.dropColumn("urlCipherText");
-      }
-      if (hasUrlIV) {
-        t.dropColumn("urlIV");
-      }
-      if (hasUrlTag) {
-        t.dropColumn("urlTag");
-      }
-      if (hasType) {
-        t.dropColumn("type");
-      }
-    });
-  }
-}

backend/src/db/migrations/20240702131735_secret-approval-groups.ts

@@ -1,188 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  // migrate secret approval policy approvers to user id
-  const hasApproverUserId = await knex.schema.hasColumn(TableName.SecretApprovalPolicyApprover, "approverUserId");
-  const hasApproverId = await knex.schema.hasColumn(TableName.SecretApprovalPolicyApprover, "approverId");
-  if (!hasApproverUserId) {
-    // add the new fields
-    await knex.schema.alterTable(TableName.SecretApprovalPolicyApprover, (tb) => {
-      // if (hasApproverId) tb.setNullable("approverId");
-      tb.uuid("approverUserId");
-      tb.foreign("approverUserId").references("id").inTable(TableName.Users).onDelete("CASCADE");
-    });
-
-    // convert project membership id => user id
-    await knex(TableName.SecretApprovalPolicyApprover).update({
-      // eslint-disable-next-line
-      // @ts-ignore because generate schema happens after this
-      approverUserId: knex(TableName.ProjectMembership)
-        .select("userId")
-        .where("id", knex.raw("??", [`${TableName.SecretApprovalPolicyApprover}.approverId`]))
-    });
-    // drop the old field
-    await knex.schema.alterTable(TableName.SecretApprovalPolicyApprover, (tb) => {
-      if (hasApproverId) tb.dropColumn("approverId");
-      tb.uuid("approverUserId").notNullable().alter();
-    });
-  }
-
-  // migrate secret approval request committer and statusChangeBy to user id
-  const hasSecretApprovalRequestTable = await knex.schema.hasTable(TableName.SecretApprovalRequest);
-  const hasCommitterUserId = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "committerUserId");
-  const hasCommitterId = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "committerId");
-  const hasStatusChangeBy = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "statusChangeBy");
-  const hasStatusChangedByUserId = await knex.schema.hasColumn(
-    TableName.SecretApprovalRequest,
-    "statusChangedByUserId"
-  );
-  if (hasSecretApprovalRequestTable) {
-    // new fields
-    await knex.schema.alterTable(TableName.SecretApprovalRequest, (tb) => {
-      // if (hasCommitterId) tb.setNullable("committerId");
-      if (!hasCommitterUserId) {
-        tb.uuid("committerUserId");
-        tb.foreign("committerUserId").references("id").inTable(TableName.Users).onDelete("SET NULL");
-      }
-      if (!hasStatusChangedByUserId) {
-        tb.uuid("statusChangedByUserId");
-        tb.foreign("statusChangedByUserId").references("id").inTable(TableName.Users).onDelete("SET NULL");
-      }
-    });
-
-    // copy the assigned project membership => user id to new fields
-    await knex(TableName.SecretApprovalRequest).update({
-      // eslint-disable-next-line
-      // @ts-ignore because generate schema happens after this
-      committerUserId: knex(TableName.ProjectMembership)
-        .select("userId")
-        .where("id", knex.raw("??", [`${TableName.SecretApprovalRequest}.committerId`])),
-      // eslint-disable-next-line
-      // @ts-ignore because generate schema happens after this
-      statusChangedByUserId: knex(TableName.ProjectMembership)
-        .select("userId")
-        .where("id", knex.raw("??", [`${TableName.SecretApprovalRequest}.statusChangeBy`]))
-    });
-    // drop old fields
-    await knex.schema.alterTable(TableName.SecretApprovalRequest, (tb) => {
-      if (hasStatusChangeBy) tb.dropColumn("statusChangeBy");
-      if (hasCommitterId) tb.dropColumn("committerId");
-      tb.uuid("committerUserId").notNullable().alter();
-    });
-  }
-
-  // migrate secret approval request reviewer to user id
-  const hasMemberId = await knex.schema.hasColumn(TableName.SecretApprovalRequestReviewer, "member");
-  const hasReviewerUserId = await knex.schema.hasColumn(TableName.SecretApprovalRequestReviewer, "reviewerUserId");
-  if (!hasReviewerUserId) {
-    // new fields
-    await knex.schema.alterTable(TableName.SecretApprovalRequestReviewer, (tb) => {
-      // if (hasMemberId) tb.setNullable("member");
-      tb.uuid("reviewerUserId");
-      tb.foreign("reviewerUserId").references("id").inTable(TableName.Users).onDelete("SET NULL");
-    });
-    // copy project membership => user id to new fields
-    await knex(TableName.SecretApprovalRequestReviewer).update({
-      // eslint-disable-next-line
-      // @ts-ignore because generate schema happens after this
-      reviewerUserId: knex(TableName.ProjectMembership)
-        .select("userId")
-        .where("id", knex.raw("??", [`${TableName.SecretApprovalRequestReviewer}.member`]))
-    });
-    // drop table
-    await knex.schema.alterTable(TableName.SecretApprovalRequestReviewer, (tb) => {
-      if (hasMemberId) tb.dropColumn("member");
-      tb.uuid("reviewerUserId").notNullable().alter();
-    });
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  const hasApproverUserId = await knex.schema.hasColumn(TableName.SecretApprovalPolicyApprover, "approverUserId");
-  const hasApproverId = await knex.schema.hasColumn(TableName.SecretApprovalPolicyApprover, "approverId");
-  if (hasApproverUserId) {
-    await knex.schema.alterTable(TableName.SecretApprovalPolicyApprover, (tb) => {
-      if (!hasApproverId) {
-        tb.uuid("approverId");
-        tb.foreign("approverId").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
-      }
-    });
-
-    if (!hasApproverId) {
-      await knex(TableName.SecretApprovalPolicyApprover).update({
-        // eslint-disable-next-line
-        // @ts-ignore because generate schema happens after this
-        approverId: knex(TableName.ProjectMembership)
-          .select("id")
-          .where("userId", knex.raw("??", [`${TableName.SecretApprovalPolicyApprover}.approverUserId`]))
-      });
-      await knex.schema.alterTable(TableName.SecretApprovalPolicyApprover, (tb) => {
-        tb.dropColumn("approverUserId");
-        tb.uuid("approverId").notNullable().alter();
-      });
-    }
-  }
-
-  const hasSecretApprovalRequestTable = await knex.schema.hasTable(TableName.SecretApprovalRequest);
-  const hasCommitterUserId = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "committerUserId");
-  const hasCommitterId = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "committerId");
-  const hasStatusChangeBy = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "statusChangeBy");
-  const hasStatusChangedByUser = await knex.schema.hasColumn(TableName.SecretApprovalRequest, "statusChangedByUserId");
-  if (hasSecretApprovalRequestTable) {
-    await knex.schema.alterTable(TableName.SecretApprovalRequest, (tb) => {
-      // if (hasCommitterId) tb.uuid("committerId").notNullable().alter();
-      if (!hasCommitterId) {
-        tb.uuid("committerId");
-        tb.foreign("committerId").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
-      }
-      if (!hasStatusChangeBy) {
-        tb.uuid("statusChangeBy");
-        tb.foreign("statusChangeBy").references("id").inTable(TableName.ProjectMembership).onDelete("SET NULL");
-      }
-    });
-
-    await knex(TableName.SecretApprovalRequest).update({
-      // eslint-disable-next-line
-      // @ts-ignore because generate schema happens after this
-      committerId: knex(TableName.ProjectMembership)
-        .select("id")
-        .where("userId", knex.raw("??", [`${TableName.SecretApprovalRequest}.committerUserId`])),
-      // eslint-disable-next-line
-      // @ts-ignore because generate schema happens after this
-      statusChangeBy: knex(TableName.ProjectMembership)
-        .select("id")
-        .where("userId", knex.raw("??", [`${TableName.SecretApprovalRequest}.statusChangedByUserId`]))
-    });
-
-    await knex.schema.alterTable(TableName.SecretApprovalRequest, (tb) => {
-      if (hasCommitterUserId) tb.dropColumn("committerUserId");
-      if (hasStatusChangedByUser) tb.dropColumn("statusChangedByUserId");
-      if (hasCommitterId) tb.uuid("committerId").notNullable().alter();
-    });
-  }
-
-  const hasMemberId = await knex.schema.hasColumn(TableName.SecretApprovalRequestReviewer, "member");
-  const hasReviewerUserId = await knex.schema.hasColumn(TableName.SecretApprovalRequestReviewer, "reviewerUserId");
-  if (hasReviewerUserId) {
-    if (!hasMemberId) {
-      await knex.schema.alterTable(TableName.SecretApprovalRequestReviewer, (tb) => {
-        // if (hasMemberId) tb.uuid("member").notNullable().alter();
-        tb.uuid("member");
-        tb.foreign("member").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
-      });
-    }
-    await knex(TableName.SecretApprovalRequestReviewer).update({
-      // eslint-disable-next-line
-      // @ts-ignore because generate schema happens after this
-      member: knex(TableName.ProjectMembership)
-        .select("id")
-        .where("userId", knex.raw("??", [`${TableName.SecretApprovalRequestReviewer}.reviewerUserId`]))
-    });
-    await knex.schema.alterTable(TableName.SecretApprovalRequestReviewer, (tb) => {
-      tb.uuid("member").notNullable().alter();
-      tb.dropColumn("reviewerUserId");
-    });
-  }
-}

backend/src/db/migrations/20240702175124_identity-token-auth.ts

@@ -1,24 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-import { createOnUpdateTrigger, dropOnUpdateTrigger } from "../utils";
-
-export async function up(knex: Knex): Promise<void> {
-  await knex.schema.createTable(TableName.IdentityTokenAuth, (t) => {
-    t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
-    t.bigInteger("accessTokenTTL").defaultTo(7200).notNullable();
-    t.bigInteger("accessTokenMaxTTL").defaultTo(7200).notNullable();
-    t.bigInteger("accessTokenNumUsesLimit").defaultTo(0).notNullable();
-    t.jsonb("accessTokenTrustedIps").notNullable();
-    t.timestamps(true, true, true);
-    t.uuid("identityId").notNullable().unique();
-    t.foreign("identityId").references("id").inTable(TableName.Identity).onDelete("CASCADE");
-  });
-
-  await createOnUpdateTrigger(knex, TableName.IdentityTokenAuth);
-}
-
-export async function down(knex: Knex): Promise<void> {
-  await knex.schema.dropTableIfExists(TableName.IdentityTokenAuth);
-  await dropOnUpdateTrigger(knex, TableName.IdentityTokenAuth);
-}

backend/src/db/migrations/20240704161322_identity-access-token-name.ts

@@ -1,24 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-
-export async function up(knex: Knex): Promise<void> {
-  if (await knex.schema.hasTable(TableName.IdentityAccessToken)) {
-    const hasNameColumn = await knex.schema.hasColumn(TableName.IdentityAccessToken, "name");
-    if (!hasNameColumn) {
-      await knex.schema.alterTable(TableName.IdentityAccessToken, (t) => {
-        t.string("name").nullable();
-      });
-    }
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  if (await knex.schema.hasTable(TableName.IdentityAccessToken)) {
-    if (await knex.schema.hasColumn(TableName.IdentityAccessToken, "name")) {
-      await knex.schema.alterTable(TableName.IdentityAccessToken, (t) => {
-        t.dropColumn("name");
-      });
-    }
-  }
-}

backend/src/db/migrations/20240708100026_external-kms.ts

@@ -1,256 +0,0 @@
-import slugify from "@sindresorhus/slugify";
-import { Knex } from "knex";
-
-import { alphaNumericNanoId } from "@app/lib/nanoid";
-
-import { TableName } from "../schemas";
-
-const createInternalKmsTableAndBackfillData = async (knex: Knex) => {
-  const doesOldKmsKeyTableExist = await knex.schema.hasTable(TableName.KmsKey);
-  const doesInternalKmsTableExist = await knex.schema.hasTable(TableName.InternalKms);
-
-  // building the internal kms table by filling from old kms table
-  if (doesOldKmsKeyTableExist && !doesInternalKmsTableExist) {
-    await knex.schema.createTable(TableName.InternalKms, (tb) => {
-      tb.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
-      tb.binary("encryptedKey").notNullable();
-      tb.string("encryptionAlgorithm").notNullable();
-      tb.integer("version").defaultTo(1).notNullable();
-      tb.uuid("kmsKeyId").unique().notNullable();
-      tb.foreign("kmsKeyId").references("id").inTable(TableName.KmsKey).onDelete("CASCADE");
-    });
-
-    // copy the old kms and backfill
-    const oldKmsKey = await knex(TableName.KmsKey).select("version", "encryptedKey", "encryptionAlgorithm", "id");
-    if (oldKmsKey.length) {
-      await knex(TableName.InternalKms).insert(
-        oldKmsKey.map((el) => ({
-          encryptionAlgorithm: el.encryptionAlgorithm,
-          encryptedKey: el.encryptedKey,
-          kmsKeyId: el.id,
-          version: el.version
-        }))
-      );
-    }
-  }
-};
-
-const renameKmsKeyVersionTableAsInternalKmsKeyVersion = async (knex: Knex) => {
-  const doesOldKmsKeyVersionTableExist = await knex.schema.hasTable(TableName.KmsKeyVersion);
-  const doesNewKmsKeyVersionTableExist = await knex.schema.hasTable(TableName.InternalKmsKeyVersion);
-
-  if (doesOldKmsKeyVersionTableExist && !doesNewKmsKeyVersionTableExist) {
-    // because we haven't started using versioning for kms thus no data exist
-    await knex.schema.renameTable(TableName.KmsKeyVersion, TableName.InternalKmsKeyVersion);
-    const hasKmsKeyIdColumn = await knex.schema.hasColumn(TableName.InternalKmsKeyVersion, "kmsKeyId");
-    const hasInternalKmsIdColumn = await knex.schema.hasColumn(TableName.InternalKmsKeyVersion, "internalKmsId");
-
-    await knex.schema.alterTable(TableName.InternalKmsKeyVersion, (tb) => {
-      if (hasKmsKeyIdColumn) tb.dropColumn("kmsKeyId");
-      if (!hasInternalKmsIdColumn) {
-        tb.uuid("internalKmsId").notNullable();
-        tb.foreign("internalKmsId").references("id").inTable(TableName.InternalKms).onDelete("CASCADE");
-      }
-    });
-  }
-};
-
-const createExternalKmsKeyTable = async (knex: Knex) => {
-  const doesExternalKmsServiceExist = await knex.schema.hasTable(TableName.ExternalKms);
-  if (!doesExternalKmsServiceExist) {
-    await knex.schema.createTable(TableName.ExternalKms, (tb) => {
-      tb.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
-      tb.string("provider").notNullable();
-      tb.binary("encryptedProviderInputs").notNullable();
-      tb.string("status");
-      tb.string("statusDetails");
-      tb.uuid("kmsKeyId").unique().notNullable();
-      tb.foreign("kmsKeyId").references("id").inTable(TableName.KmsKey).onDelete("CASCADE");
-    });
-  }
-};
-
-const removeNonRequiredFieldsFromKmsKeyTableAndBackfillRequiredData = async (knex: Knex) => {
-  const doesOldKmsKeyTableExist = await knex.schema.hasTable(TableName.KmsKey);
-
-  // building the internal kms table by filling from old kms table
-  if (doesOldKmsKeyTableExist) {
-    const hasSlugColumn = await knex.schema.hasColumn(TableName.KmsKey, "slug");
-    const hasEncryptedKeyColumn = await knex.schema.hasColumn(TableName.KmsKey, "encryptedKey");
-    const hasEncryptionAlgorithmColumn = await knex.schema.hasColumn(TableName.KmsKey, "encryptionAlgorithm");
-    const hasVersionColumn = await knex.schema.hasColumn(TableName.KmsKey, "version");
-    const hasTimestamps = await knex.schema.hasColumn(TableName.KmsKey, "createdAt");
-    const hasProjectId = await knex.schema.hasColumn(TableName.KmsKey, "projectId");
-    const hasOrgId = await knex.schema.hasColumn(TableName.KmsKey, "orgId");
-
-    await knex.schema.alterTable(TableName.KmsKey, (tb) => {
-      if (!hasSlugColumn) tb.string("slug", 32);
-      if (hasEncryptedKeyColumn) tb.dropColumn("encryptedKey");
-      if (hasEncryptionAlgorithmColumn) tb.dropColumn("encryptionAlgorithm");
-      if (hasVersionColumn) tb.dropColumn("version");
-      if (!hasTimestamps) tb.timestamps(true, true, true);
-    });
-
-    // backfill all org id in kms key because its gonna be changed to non nullable
-    if (hasProjectId && hasOrgId) {
-      await knex(TableName.KmsKey)
-        .whereNull("orgId")
-        .update({
-          // eslint-disable-next-line
-          // @ts-ignore because generate schema happens after this
-          orgId: knex(TableName.Project)
-            .select("orgId")
-            .where("id", knex.raw("??", [`${TableName.KmsKey}.projectId`]))
-        });
-    }
-
-    // backfill slugs in kms
-    const missingSlugs = await knex(TableName.KmsKey).whereNull("slug").select("id");
-    if (missingSlugs.length) {
-      await knex(TableName.KmsKey)
-        // eslint-disable-next-line
-        // @ts-ignore because generate schema happens after this
-        .insert(missingSlugs.map(({ id }) => ({ id, slug: slugify(alphaNumericNanoId(8).toLowerCase()) })))
-        .onConflict("id")
-        .merge();
-    }
-
-    await knex.schema.alterTable(TableName.KmsKey, (tb) => {
-      if (hasOrgId) tb.uuid("orgId").notNullable().alter();
-      tb.string("slug", 32).notNullable().alter();
-      if (hasProjectId) tb.dropColumn("projectId");
-      if (hasOrgId) tb.unique(["orgId", "slug"]);
-    });
-  }
-};
-
-/*
- * The goal for this migration is split the existing kms key into three table
- * the kms-key table would be a container table that contains
- * the internal kms key table and external kms table
- */
-export async function up(knex: Knex): Promise<void> {
-  await createInternalKmsTableAndBackfillData(knex);
-  await renameKmsKeyVersionTableAsInternalKmsKeyVersion(knex);
-  await removeNonRequiredFieldsFromKmsKeyTableAndBackfillRequiredData(knex);
-  await createExternalKmsKeyTable(knex);
-
-  const doesOrgKmsKeyExist = await knex.schema.hasColumn(TableName.Organization, "kmsDefaultKeyId");
-  if (!doesOrgKmsKeyExist) {
-    await knex.schema.alterTable(TableName.Organization, (tb) => {
-      tb.uuid("kmsDefaultKeyId").nullable();
-      tb.foreign("kmsDefaultKeyId").references("id").inTable(TableName.KmsKey);
-    });
-  }
-
-  const doesProjectKmsSecretManagerKeyExist = await knex.schema.hasColumn(TableName.Project, "kmsSecretManagerKeyId");
-  if (!doesProjectKmsSecretManagerKeyExist) {
-    await knex.schema.alterTable(TableName.Project, (tb) => {
-      tb.uuid("kmsSecretManagerKeyId").nullable();
-      tb.foreign("kmsSecretManagerKeyId").references("id").inTable(TableName.KmsKey);
-    });
-  }
-}
-
-const renameInternalKmsKeyVersionBackToKmsKeyVersion = async (knex: Knex) => {
-  const doesInternalKmsKeyVersionTableExist = await knex.schema.hasTable(TableName.InternalKmsKeyVersion);
-  const doesKmsKeyVersionTableExist = await knex.schema.hasTable(TableName.KmsKeyVersion);
-  if (doesInternalKmsKeyVersionTableExist && !doesKmsKeyVersionTableExist) {
-    // because we haven't started using versioning for kms thus no data exist
-    await knex.schema.renameTable(TableName.InternalKmsKeyVersion, TableName.KmsKeyVersion);
-    const hasInternalKmsIdColumn = await knex.schema.hasColumn(TableName.KmsKeyVersion, "internalKmsId");
-    const hasKmsKeyIdColumn = await knex.schema.hasColumn(TableName.KmsKeyVersion, "kmsKeyId");
-
-    await knex.schema.alterTable(TableName.KmsKeyVersion, (tb) => {
-      if (hasInternalKmsIdColumn) tb.dropColumn("internalKmsId");
-      if (!hasKmsKeyIdColumn) {
-        tb.uuid("kmsKeyId").notNullable();
-        tb.foreign("kmsKeyId").references("id").inTable(TableName.KmsKey).onDelete("CASCADE");
-      }
-    });
-  }
-};
-
-const bringBackKmsKeyFields = async (knex: Knex) => {
-  const doesOldKmsKeyTableExist = await knex.schema.hasTable(TableName.KmsKey);
-  const doesInternalKmsTableExist = await knex.schema.hasTable(TableName.InternalKms);
-  if (doesOldKmsKeyTableExist && doesInternalKmsTableExist) {
-    const hasSlug = await knex.schema.hasColumn(TableName.KmsKey, "slug");
-    const hasEncryptedKeyColumn = await knex.schema.hasColumn(TableName.KmsKey, "encryptedKey");
-    const hasEncryptionAlgorithmColumn = await knex.schema.hasColumn(TableName.KmsKey, "encryptionAlgorithm");
-    const hasVersionColumn = await knex.schema.hasColumn(TableName.KmsKey, "version");
-    const hasNullableOrgId = await knex.schema.hasColumn(TableName.KmsKey, "orgId");
-    const hasProjectIdColumn = await knex.schema.hasColumn(TableName.KmsKey, "projectId");
-
-    await knex.schema.alterTable(TableName.KmsKey, (tb) => {
-      if (!hasEncryptedKeyColumn) tb.binary("encryptedKey");
-      if (!hasEncryptionAlgorithmColumn) tb.string("encryptionAlgorithm");
-      if (!hasVersionColumn) tb.integer("version").defaultTo(1);
-      if (hasNullableOrgId) tb.uuid("orgId").nullable().alter();
-      if (!hasProjectIdColumn) {
-        tb.string("projectId");
-        tb.foreign("projectId").references("id").inTable(TableName.Project).onDelete("CASCADE");
-      }
-      if (hasSlug) tb.dropColumn("slug");
-    });
-  }
-};
-
-const backfillKmsKeyFromInternalKmsTable = async (knex: Knex) => {
-  const doesOldKmsKeyTableExist = await knex.schema.hasTable(TableName.KmsKey);
-  const doesInternalKmsTableExist = await knex.schema.hasTable(TableName.InternalKms);
-  if (doesInternalKmsTableExist && doesOldKmsKeyTableExist) {
-    // backfill kms key with internal kms data
-    await knex(TableName.KmsKey).update({
-      // eslint-disable-next-line
-      // @ts-ignore because generate schema happens after this
-      encryptedKey: knex(TableName.InternalKms)
-        .select("encryptedKey")
-        .where("kmsKeyId", knex.raw("??", [`${TableName.KmsKey}.id`])),
-      // eslint-disable-next-line
-      // @ts-ignore because generate schema happens after this
-      encryptionAlgorithm: knex(TableName.InternalKms)
-        .select("encryptionAlgorithm")
-        .where("kmsKeyId", knex.raw("??", [`${TableName.KmsKey}.id`])),
-      // eslint-disable-next-line
-      // @ts-ignore because generate schema happens after this
-      projectId: knex(TableName.Project)
-        .select("id")
-        .where("kmsCertificateKeyId", knex.raw("??", [`${TableName.KmsKey}.id`]))
-    });
-  }
-};
-
-export async function down(knex: Knex): Promise<void> {
-  const doesOrgKmsKeyExist = await knex.schema.hasColumn(TableName.Organization, "kmsDefaultKeyId");
-  if (doesOrgKmsKeyExist) {
-    await knex.schema.alterTable(TableName.Organization, (tb) => {
-      tb.dropColumn("kmsDefaultKeyId");
-    });
-  }
-
-  const doesProjectKmsSecretManagerKeyExist = await knex.schema.hasColumn(TableName.Project, "kmsSecretManagerKeyId");
-  if (doesProjectKmsSecretManagerKeyExist) {
-    await knex.schema.alterTable(TableName.Project, (tb) => {
-      tb.dropColumn("kmsSecretManagerKeyId");
-    });
-  }
-
-  await renameInternalKmsKeyVersionBackToKmsKeyVersion(knex);
-  await bringBackKmsKeyFields(knex);
-  await backfillKmsKeyFromInternalKmsTable(knex);
-
-  const doesOldKmsKeyTableExist = await knex.schema.hasTable(TableName.KmsKey);
-  if (doesOldKmsKeyTableExist) {
-    await knex.schema.alterTable(TableName.KmsKey, (tb) => {
-      tb.binary("encryptedKey").notNullable().alter();
-      tb.string("encryptionAlgorithm").notNullable().alter();
-    });
-  }
-
-  const doesInternalKmsTableExist = await knex.schema.hasTable(TableName.InternalKms);
-  if (doesInternalKmsTableExist) await knex.schema.dropTable(TableName.InternalKms);
-
-  const doesExternalKmsServiceExist = await knex.schema.hasTable(TableName.ExternalKms);
-  if (doesExternalKmsServiceExist) await knex.schema.dropTable(TableName.ExternalKms);
-}

backend/src/db/migrations/20240710045107_identity-oidc-auth.ts

@@ -1,34 +0,0 @@
-import { Knex } from "knex";
-
-import { TableName } from "../schemas";
-import { createOnUpdateTrigger, dropOnUpdateTrigger } from "../utils";
-
-export async function up(knex: Knex): Promise<void> {
-  if (!(await knex.schema.hasTable(TableName.IdentityOidcAuth))) {
-    await knex.schema.createTable(TableName.IdentityOidcAuth, (t) => {
-      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
-      t.bigInteger("accessTokenTTL").defaultTo(7200).notNullable();
-      t.bigInteger("accessTokenMaxTTL").defaultTo(7200).notNullable();
-      t.bigInteger("accessTokenNumUsesLimit").defaultTo(0).notNullable();
-      t.jsonb("accessTokenTrustedIps").notNullable();
-      t.uuid("identityId").notNullable().unique();
-      t.foreign("identityId").references("id").inTable(TableName.Identity).onDelete("CASCADE");
-      t.string("oidcDiscoveryUrl").notNullable();
-      t.text("encryptedCaCert").notNullable();
-      t.string("caCertIV").notNullable();
-      t.string("caCertTag").notNullable();
-      t.string("boundIssuer").notNullable();
-      t.string("boundAudiences").notNullable();
-      t.jsonb("boundClaims").notNullable();
-      t.string("boundSubject");
-      t.timestamps(true, true, true);
-    });
-
-    await createOnUpdateTrigger(knex, TableName.IdentityOidcAuth);
-  }
-}
-
-export async function down(knex: Knex): Promise<void> {
-  await knex.schema.dropTableIfExists(TableName.IdentityOidcAuth);
-  await dropOnUpdateTrigger(knex, TableName.IdentityOidcAuth);
-}

backend/src/db/schemas/certificates.ts

@@ -19,8 +19,7 @@ export const CertificatesSchema = z.object({
   notBefore: z.date(),
   notAfter: z.date(),
   revokedAt: z.date().nullable().optional(),
-  revocationReason: z.number().nullable().optional(),
-  altNames: z.string().default("").nullable().optional()
+  revocationReason: z.number().nullable().optional()
 });
 
 export type TCertificates = z.infer<typeof CertificatesSchema>;

backend/src/db/schemas/external-kms.ts

@@ -1,23 +0,0 @@
-// Code generated by automation script, DO NOT EDIT.
-// Automated by pulling database and generating zod schema
-// To update. Just run npm run generate:schema
-// Written by akhilmhdh.
-
-import { z } from "zod";
-
-import { zodBuffer } from "@app/lib/zod";
-
-import { TImmutableDBKeys } from "./models";
-
-export const ExternalKmsSchema = z.object({
-  id: z.string().uuid(),
-  provider: z.string(),
-  encryptedProviderInputs: zodBuffer,
-  status: z.string().nullable().optional(),
-  statusDetails: z.string().nullable().optional(),
-  kmsKeyId: z.string().uuid()
-});
-
-export type TExternalKms = z.infer<typeof ExternalKmsSchema>;
-export type TExternalKmsInsert = Omit<z.input<typeof ExternalKmsSchema>, TImmutableDBKeys>;
-export type TExternalKmsUpdate = Partial<Omit<z.input<typeof ExternalKmsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/identity-access-tokens.ts

@@ -19,8 +19,7 @@ export const IdentityAccessTokensSchema = z.object({
   identityUAClientSecretId: z.string().nullable().optional(),
   identityId: z.string().uuid(),
   createdAt: z.date(),
-  updatedAt: z.date(),
-  name: z.string().nullable().optional()
+  updatedAt: z.date()
 });
 
 export type TIdentityAccessTokens = z.infer<typeof IdentityAccessTokensSchema>;

backend/src/db/schemas/identity-oidc-auths.ts

@@ -1,31 +0,0 @@
-// Code generated by automation script, DO NOT EDIT.
-// Automated by pulling database and generating zod schema
-// To update. Just run npm run generate:schema
-// Written by akhilmhdh.
-
-import { z } from "zod";
-
-import { TImmutableDBKeys } from "./models";
-
-export const IdentityOidcAuthsSchema = z.object({
-  id: z.string().uuid(),
-  accessTokenTTL: z.coerce.number().default(7200),
-  accessTokenMaxTTL: z.coerce.number().default(7200),
-  accessTokenNumUsesLimit: z.coerce.number().default(0),
-  accessTokenTrustedIps: z.unknown(),
-  identityId: z.string().uuid(),
-  oidcDiscoveryUrl: z.string(),
-  encryptedCaCert: z.string(),
-  caCertIV: z.string(),
-  caCertTag: z.string(),
-  boundIssuer: z.string(),
-  boundAudiences: z.string(),
-  boundClaims: z.unknown(),
-  boundSubject: z.string().nullable().optional(),
-  createdAt: z.date(),
-  updatedAt: z.date()
-});
-
-export type TIdentityOidcAuths = z.infer<typeof IdentityOidcAuthsSchema>;
-export type TIdentityOidcAuthsInsert = Omit<z.input<typeof IdentityOidcAuthsSchema>, TImmutableDBKeys>;
-export type TIdentityOidcAuthsUpdate = Partial<Omit<z.input<typeof IdentityOidcAuthsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/identity-token-auths.ts

@@ -1,23 +0,0 @@
-// Code generated by automation script, DO NOT EDIT.
-// Automated by pulling database and generating zod schema
-// To update. Just run npm run generate:schema
-// Written by akhilmhdh.
-
-import { z } from "zod";
-
-import { TImmutableDBKeys } from "./models";
-
-export const IdentityTokenAuthsSchema = z.object({
-  id: z.string().uuid(),
-  accessTokenTTL: z.coerce.number().default(7200),
-  accessTokenMaxTTL: z.coerce.number().default(7200),
-  accessTokenNumUsesLimit: z.coerce.number().default(0),
-  accessTokenTrustedIps: z.unknown(),
-  createdAt: z.date(),
-  updatedAt: z.date(),
-  identityId: z.string().uuid()
-});
-
-export type TIdentityTokenAuths = z.infer<typeof IdentityTokenAuthsSchema>;
-export type TIdentityTokenAuthsInsert = Omit<z.input<typeof IdentityTokenAuthsSchema>, TImmutableDBKeys>;
-export type TIdentityTokenAuthsUpdate = Partial<Omit<z.input<typeof IdentityTokenAuthsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/index.ts

@@ -17,7 +17,6 @@ export * from "./certificate-secrets";
 export * from "./certificates";
 export * from "./dynamic-secret-leases";
 export * from "./dynamic-secrets";
-export * from "./external-kms";
 export * from "./git-app-install-sessions";
 export * from "./git-app-org";
 export * from "./group-project-membership-roles";
@@ -29,25 +28,21 @@ export * from "./identity-aws-auths";
 export * from "./identity-azure-auths";
 export * from "./identity-gcp-auths";
 export * from "./identity-kubernetes-auths";
-export * from "./identity-oidc-auths";
 export * from "./identity-org-memberships";
 export * from "./identity-project-additional-privilege";
 export * from "./identity-project-membership-role";
 export * from "./identity-project-memberships";
-export * from "./identity-token-auths";
 export * from "./identity-ua-client-secrets";
 export * from "./identity-universal-auths";
 export * from "./incident-contacts";
 export * from "./integration-auths";
 export * from "./integrations";
-export * from "./internal-kms";
 export * from "./kms-key-versions";
 export * from "./kms-keys";
 export * from "./kms-root-config";
 export * from "./ldap-configs";
 export * from "./ldap-group-maps";
 export * from "./models";
-export * from "./oidc-configs";
 export * from "./org-bots";
 export * from "./org-memberships";
 export * from "./org-roles";

backend/src/db/schemas/integration-auths.ts

@@ -29,10 +29,7 @@ export const IntegrationAuthsSchema = z.object({
   keyEncoding: z.string(),
   projectId: z.string(),
   createdAt: z.date(),
-  updatedAt: z.date(),
-  awsAssumeIamRoleArnCipherText: z.string().nullable().optional(),
-  awsAssumeIamRoleArnIV: z.string().nullable().optional(),
-  awsAssumeIamRoleArnTag: z.string().nullable().optional()
+  updatedAt: z.date()
 });
 
 export type TIntegrationAuths = z.infer<typeof IntegrationAuthsSchema>;

backend/src/db/schemas/internal-kms-key-version.ts

@@ -1,21 +0,0 @@
-// Code generated by automation script, DO NOT EDIT.
-// Automated by pulling database and generating zod schema
-// To update. Just run npm run generate:schema
-// Written by akhilmhdh.
-
-import { z } from "zod";
-
-import { zodBuffer } from "@app/lib/zod";
-
-import { TImmutableDBKeys } from "./models";
-
-export const InternalKmsKeyVersionSchema = z.object({
-  id: z.string().uuid(),
-  encryptedKey: zodBuffer,
-  version: z.number(),
-  internalKmsId: z.string().uuid()
-});
-
-export type TInternalKmsKeyVersion = z.infer<typeof InternalKmsKeyVersionSchema>;
-export type TInternalKmsKeyVersionInsert = Omit<z.input<typeof InternalKmsKeyVersionSchema>, TImmutableDBKeys>;
-export type TInternalKmsKeyVersionUpdate = Partial<Omit<z.input<typeof InternalKmsKeyVersionSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/internal-kms.ts

@@ -1,22 +0,0 @@
-// Code generated by automation script, DO NOT EDIT.
-// Automated by pulling database and generating zod schema
-// To update. Just run npm run generate:schema
-// Written by akhilmhdh.
-
-import { z } from "zod";
-
-import { zodBuffer } from "@app/lib/zod";
-
-import { TImmutableDBKeys } from "./models";
-
-export const InternalKmsSchema = z.object({
-  id: z.string().uuid(),
-  encryptedKey: zodBuffer,
-  encryptionAlgorithm: z.string(),
-  version: z.number().default(1),
-  kmsKeyId: z.string().uuid()
-});
-
-export type TInternalKms = z.infer<typeof InternalKmsSchema>;
-export type TInternalKmsInsert = Omit<z.input<typeof InternalKmsSchema>, TImmutableDBKeys>;
-export type TInternalKmsUpdate = Partial<Omit<z.input<typeof InternalKmsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/kms-keys.ts

@@ -5,17 +5,20 @@
 
 import { z } from "zod";
 
+import { zodBuffer } from "@app/lib/zod";
+
 import { TImmutableDBKeys } from "./models";
 
 export const KmsKeysSchema = z.object({
   id: z.string().uuid(),
+  encryptedKey: zodBuffer,
+  encryptionAlgorithm: z.string(),
+  version: z.number().default(1),
   description: z.string().nullable().optional(),
   isDisabled: z.boolean().default(false).nullable().optional(),
   isReserved: z.boolean().default(true).nullable().optional(),
-  orgId: z.string().uuid(),
-  createdAt: z.date(),
-  updatedAt: z.date(),
-  slug: z.string()
+  projectId: z.string().nullable().optional(),
+  orgId: z.string().uuid().nullable().optional()
 });
 
 export type TKmsKeys = z.infer<typeof KmsKeysSchema>;

backend/src/db/schemas/ldap-configs.ts

@@ -26,8 +26,7 @@ export const LdapConfigsSchema = z.object({
   updatedAt: z.date(),
   groupSearchBase: z.string().default(""),
   groupSearchFilter: z.string().default(""),
-  searchFilter: z.string().default(""),
-  uniqueUserAttribute: z.string().default("")
+  searchFilter: z.string().default("")
 });
 
 export type TLdapConfigs = z.infer<typeof LdapConfigsSchema>;

backend/src/db/schemas/models.ts

@@ -53,14 +53,12 @@ export enum TableName {
   Webhook = "webhooks",
   Identity = "identities",
   IdentityAccessToken = "identity_access_tokens",
-  IdentityTokenAuth = "identity_token_auths",
   IdentityUniversalAuth = "identity_universal_auths",
   IdentityKubernetesAuth = "identity_kubernetes_auths",
   IdentityGcpAuth = "identity_gcp_auths",
   IdentityAzureAuth = "identity_azure_auths",
   IdentityUaClientSecret = "identity_ua_client_secrets",
   IdentityAwsAuth = "identity_aws_auths",
-  IdentityOidcAuth = "identity_oidc_auths",
   IdentityOrgMembership = "identity_org_memberships",
   IdentityProjectMembership = "identity_project_memberships",
   IdentityProjectMembershipRole = "identity_project_membership_role",
@@ -80,7 +78,6 @@ export enum TableName {
   SecretRotationOutput = "secret_rotation_outputs",
   SamlConfig = "saml_configs",
   LdapConfig = "ldap_configs",
-  OidcConfig = "oidc_configs",
   LdapGroupMap = "ldap_group_maps",
   AuditLog = "audit_logs",
   AuditLogStream = "audit_log_streams",
@@ -96,10 +93,6 @@ export enum TableName {
   // KMS Service
   KmsServerRootConfig = "kms_root_config",
   KmsKey = "kms_keys",
-  ExternalKms = "external_kms",
-  InternalKms = "internal_kms",
-  InternalKmsKeyVersion = "internal_kms_key_version",
-  // @depreciated
   KmsKeyVersion = "kms_key_versions"
 }
 
@@ -167,11 +160,9 @@ export enum ProjectUpgradeStatus {
 }
 
 export enum IdentityAuthMethod {
-  TOKEN_AUTH = "token-auth",
   Univeral = "universal-auth",
   KUBERNETES_AUTH = "kubernetes-auth",
   GCP_AUTH = "gcp-auth",
   AWS_AUTH = "aws-auth",
-  AZURE_AUTH = "azure-auth",
-  OIDC_AUTH = "oidc-auth"
+  AZURE_AUTH = "azure-auth"
 }

backend/src/db/schemas/oidc-configs.ts

@@ -1,34 +0,0 @@
-// Code generated by automation script, DO NOT EDIT.
-// Automated by pulling database and generating zod schema
-// To update. Just run npm run generate:schema
-// Written by akhilmhdh.
-
-import { z } from "zod";
-
-import { TImmutableDBKeys } from "./models";
-
-export const OidcConfigsSchema = z.object({
-  id: z.string().uuid(),
-  discoveryURL: z.string().nullable().optional(),
-  issuer: z.string().nullable().optional(),
-  authorizationEndpoint: z.string().nullable().optional(),
-  jwksUri: z.string().nullable().optional(),
-  tokenEndpoint: z.string().nullable().optional(),
-  userinfoEndpoint: z.string().nullable().optional(),
-  encryptedClientId: z.string(),
-  configurationType: z.string(),
-  clientIdIV: z.string(),
-  clientIdTag: z.string(),
-  encryptedClientSecret: z.string(),
-  clientSecretIV: z.string(),
-  clientSecretTag: z.string(),
-  allowedEmailDomains: z.string().nullable().optional(),
-  isActive: z.boolean(),
-  createdAt: z.date(),
-  updatedAt: z.date(),
-  orgId: z.string().uuid()
-});
-
-export type TOidcConfigs = z.infer<typeof OidcConfigsSchema>;
-export type TOidcConfigsInsert = Omit<z.input<typeof OidcConfigsSchema>, TImmutableDBKeys>;
-export type TOidcConfigsUpdate = Partial<Omit<z.input<typeof OidcConfigsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/org-memberships.ts

@@ -16,8 +16,7 @@ export const OrgMembershipsSchema = z.object({
   updatedAt: z.date(),
   userId: z.string().uuid().nullable().optional(),
   orgId: z.string().uuid(),
-  roleId: z.string().uuid().nullable().optional(),
-  projectFavorites: z.string().array().nullable().optional()
+  roleId: z.string().uuid().nullable().optional()
 });
 
 export type TOrgMemberships = z.infer<typeof OrgMembershipsSchema>;

backend/src/db/schemas/organizations.ts

@@ -16,7 +16,7 @@ export const OrganizationsSchema = z.object({
   updatedAt: z.date(),
   authEnforced: z.boolean().default(false).nullable().optional(),
   scimEnabled: z.boolean().default(false).nullable().optional(),
-  kmsDefaultKeyId: z.string().uuid().nullable().optional()
+  billingVersion: z.string().default("v1").nullable().optional() // v0 -> User-only billing, v1 -> Universal Identity billing
 });
 
 export type TOrganizations = z.infer<typeof OrganizationsSchema>;

backend/src/db/schemas/projects.ts

@@ -17,10 +17,8 @@ export const ProjectsSchema = z.object({
   updatedAt: z.date(),
   version: z.number().default(1),
   upgradeStatus: z.string().nullable().optional(),
-  pitVersionLimit: z.number().default(10),
   kmsCertificateKeyId: z.string().uuid().nullable().optional(),
-  auditLogsRetentionDays: z.number().nullable().optional(),
-  kmsSecretManagerKeyId: z.string().uuid().nullable().optional()
+  pitVersionLimit: z.number().default(10)
 });
 
 export type TProjects = z.infer<typeof ProjectsSchema>;

backend/src/db/schemas/secret-approval-policies-approvers.ts

@@ -9,10 +9,10 @@ import { TImmutableDBKeys } from "./models";
 
 export const SecretApprovalPoliciesApproversSchema = z.object({
   id: z.string().uuid(),
+  approverId: z.string().uuid(),
   policyId: z.string().uuid(),
   createdAt: z.date(),
-  updatedAt: z.date(),
-  approverUserId: z.string().uuid()
+  updatedAt: z.date()
 });
 
 export type TSecretApprovalPoliciesApprovers = z.infer<typeof SecretApprovalPoliciesApproversSchema>;

backend/src/db/schemas/secret-approval-requests-reviewers.ts

@@ -9,11 +9,11 @@ import { TImmutableDBKeys } from "./models";
 
 export const SecretApprovalRequestsReviewersSchema = z.object({
   id: z.string().uuid(),
+  member: z.string().uuid(),
   status: z.string(),
   requestId: z.string().uuid(),
   createdAt: z.date(),
-  updatedAt: z.date(),
-  reviewerUserId: z.string().uuid()
+  updatedAt: z.date()
 });
 
 export type TSecretApprovalRequestsReviewers = z.infer<typeof SecretApprovalRequestsReviewersSchema>;

backend/src/db/schemas/secret-approval-requests.ts

@@ -15,11 +15,11 @@ export const SecretApprovalRequestsSchema = z.object({
   conflicts: z.unknown().nullable().optional(),
   slug: z.string(),
   folderId: z.string().uuid(),
+  statusChangeBy: z.string().uuid().nullable().optional(),
+  committerId: z.string().uuid(),
   createdAt: z.date(),
   updatedAt: z.date(),
-  isReplicated: z.boolean().nullable().optional(),
-  committerUserId: z.string().uuid(),
-  statusChangedByUserId: z.string().uuid().nullable().optional()
+  isReplicated: z.boolean().nullable().optional()
 });
 
 export type TSecretApprovalRequests = z.infer<typeof SecretApprovalRequestsSchema>;

backend/src/db/schemas/super-admin.ts

@@ -16,10 +16,7 @@ export const SuperAdminSchema = z.object({
   allowedSignUpDomain: z.string().nullable().optional(),
   instanceId: z.string().uuid().default("00000000-0000-0000-0000-000000000000"),
   trustSamlEmails: z.boolean().default(false).nullable().optional(),
-  trustLdapEmails: z.boolean().default(false).nullable().optional(),
-  trustOidcEmails: z.boolean().default(false).nullable().optional(),
-  defaultAuthOrgId: z.string().uuid().nullable().optional(),
-  enabledLoginMethods: z.string().array().nullable().optional()
+  trustLdapEmails: z.boolean().default(false).nullable().optional()
 });
 
 export type TSuperAdmin = z.infer<typeof SuperAdminSchema>;

backend/src/db/schemas/user-encryption-keys.ts

@@ -21,12 +21,7 @@ export const UserEncryptionKeysSchema = z.object({
   tag: z.string(),
   salt: z.string(),
   verifier: z.string(),
-  userId: z.string().uuid(),
-  hashedPassword: z.string().nullable().optional(),
-  serverEncryptedPrivateKey: z.string().nullable().optional(),
-  serverEncryptedPrivateKeyIV: z.string().nullable().optional(),
-  serverEncryptedPrivateKeyTag: z.string().nullable().optional(),
-  serverEncryptedPrivateKeyEncoding: z.string().nullable().optional()
+  userId: z.string().uuid()
 });
 
 export type TUserEncryptionKeys = z.infer<typeof UserEncryptionKeysSchema>;

backend/src/db/schemas/webhooks.ts

@@ -21,11 +21,7 @@ export const WebhooksSchema = z.object({
   keyEncoding: z.string().nullable().optional(),
   createdAt: z.date(),
   updatedAt: z.date(),
-  envId: z.string().uuid(),
-  urlCipherText: z.string().nullable().optional(),
-  urlIV: z.string().nullable().optional(),
-  urlTag: z.string().nullable().optional(),
-  type: z.string().default("general").nullable().optional()
+  envId: z.string().uuid()
 });
 
 export type TWebhooks = z.infer<typeof WebhooksSchema>;

backend/src/ee/routes/v1/external-kms-router.ts

@@ -1,190 +0,0 @@
-import { z } from "zod";
-
-import { ExternalKmsSchema, KmsKeysSchema } from "@app/db/schemas";
-import {
-  ExternalKmsAwsSchema,
-  ExternalKmsInputSchema,
-  ExternalKmsInputUpdateSchema
-} from "@app/ee/services/external-kms/providers/model";
-import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
-import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
-import { AuthMode } from "@app/services/auth/auth-type";
-
-const sanitizedExternalSchema = KmsKeysSchema.extend({
-  external: ExternalKmsSchema.pick({
-    id: true,
-    status: true,
-    statusDetails: true,
-    provider: true
-  })
-});
-
-const sanitizedExternalSchemaForGetById = KmsKeysSchema.extend({
-  external: ExternalKmsSchema.pick({
-    id: true,
-    status: true,
-    statusDetails: true,
-    provider: true
-  }).extend({
-    providerInput: ExternalKmsAwsSchema
-  })
-});
-
-export const registerExternalKmsRouter = async (server: FastifyZodProvider) => {
-  server.route({
-    method: "POST",
-    url: "/",
-    config: {
-      rateLimit: writeLimit
-    },
-    schema: {
-      body: z.object({
-        slug: z.string().min(1).trim().optional(),
-        description: z.string().min(1).trim().optional(),
-        provider: ExternalKmsInputSchema
-      }),
-      response: {
-        200: z.object({
-          externalKms: sanitizedExternalSchema
-        })
-      }
-    },
-    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
-    handler: async (req) => {
-      const externalKms = await server.services.externalKms.create({
-        actor: req.permission.type,
-        actorId: req.permission.id,
-        actorAuthMethod: req.permission.authMethod,
-        actorOrgId: req.permission.orgId,
-        slug: req.body.slug,
-        provider: req.body.provider,
-        description: req.body.description
-      });
-      return { externalKms };
-    }
-  });
-
-  server.route({
-    method: "PATCH",
-    url: "/:id",
-    config: {
-      rateLimit: writeLimit
-    },
-    schema: {
-      params: z.object({
-        id: z.string().trim().min(1)
-      }),
-      body: z.object({
-        slug: z.string().min(1).trim().optional(),
-        description: z.string().min(1).trim().optional(),
-        provider: ExternalKmsInputUpdateSchema
-      }),
-      response: {
-        200: z.object({
-          externalKms: sanitizedExternalSchema
-        })
-      }
-    },
-    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
-    handler: async (req) => {
-      const externalKms = await server.services.externalKms.updateById({
-        actor: req.permission.type,
-        actorId: req.permission.id,
-        actorAuthMethod: req.permission.authMethod,
-        actorOrgId: req.permission.orgId,
-        slug: req.body.slug,
-        provider: req.body.provider,
-        description: req.body.description,
-        id: req.params.id
-      });
-      return { externalKms };
-    }
-  });
-
-  server.route({
-    method: "DELETE",
-    url: "/:id",
-    config: {
-      rateLimit: writeLimit
-    },
-    schema: {
-      params: z.object({
-        id: z.string().trim().min(1)
-      }),
-      response: {
-        200: z.object({
-          externalKms: sanitizedExternalSchema
-        })
-      }
-    },
-    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
-    handler: async (req) => {
-      const externalKms = await server.services.externalKms.deleteById({
-        actor: req.permission.type,
-        actorId: req.permission.id,
-        actorAuthMethod: req.permission.authMethod,
-        actorOrgId: req.permission.orgId,
-        id: req.params.id
-      });
-      return { externalKms };
-    }
-  });
-
-  server.route({
-    method: "GET",
-    url: "/:id",
-    config: {
-      rateLimit: readLimit
-    },
-    schema: {
-      params: z.object({
-        id: z.string().trim().min(1)
-      }),
-      response: {
-        200: z.object({
-          externalKms: sanitizedExternalSchemaForGetById
-        })
-      }
-    },
-    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
-    handler: async (req) => {
-      const externalKms = await server.services.externalKms.findById({
-        actor: req.permission.type,
-        actorId: req.permission.id,
-        actorAuthMethod: req.permission.authMethod,
-        actorOrgId: req.permission.orgId,
-        id: req.params.id
-      });
-      return { externalKms };
-    }
-  });
-
-  server.route({
-    method: "GET",
-    url: "/slug/:slug",
-    config: {
-      rateLimit: readLimit
-    },
-    schema: {
-      params: z.object({
-        slug: z.string().trim().min(1)
-      }),
-      response: {
-        200: z.object({
-          externalKms: sanitizedExternalSchemaForGetById
-        })
-      }
-    },
-    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
-    handler: async (req) => {
-      const externalKms = await server.services.externalKms.findBySlug({
-        actor: req.permission.type,
-        actorId: req.permission.id,
-        actorAuthMethod: req.permission.authMethod,
-        actorOrgId: req.permission.orgId,
-        slug: req.params.slug
-      });
-      return { externalKms };
-    }
-  });
-};

backend/src/ee/routes/v1/index.ts

@@ -8,7 +8,6 @@ import { registerGroupRouter } from "./group-router";
 import { registerIdentityProjectAdditionalPrivilegeRouter } from "./identity-project-additional-privilege-router";
 import { registerLdapRouter } from "./ldap-router";
 import { registerLicenseRouter } from "./license-router";
-import { registerOidcRouter } from "./oidc-router";
 import { registerOrgRoleRouter } from "./org-role-router";
 import { registerProjectRoleRouter } from "./project-role-router";
 import { registerProjectRouter } from "./project-router";
@@ -65,14 +64,7 @@ export const registerV1EERoutes = async (server: FastifyZodProvider) => {
     { prefix: "/pki" }
   );
 
-  await server.register(
-    async (ssoRouter) => {
-      await ssoRouter.register(registerSamlRouter);
-      await ssoRouter.register(registerOidcRouter, { prefix: "/oidc" });
-    },
-    { prefix: "/sso" }
-  );
-
+  await server.register(registerSamlRouter, { prefix: "/sso" });
   await server.register(registerScimRouter, { prefix: "/scim" });
   await server.register(registerLdapRouter, { prefix: "/ldap" });
   await server.register(registerSecretScanningRouter, { prefix: "/secret-scanning" });

backend/src/ee/routes/v1/ldap-router.ts

@@ -53,7 +53,7 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
       // eslint-disable-next-line
       async (req: IncomingMessage, user, cb) => {
         try {
-          if (!user.mail) throw new BadRequestError({ message: "Invalid request. Missing mail attribute on user." });
+          if (!user.email) throw new BadRequestError({ message: "Invalid request. Missing email." });
           const ldapConfig = (req as unknown as FastifyRequest).ldapConfig as TLDAPConfig;
 
           let groups: { dn: string; cn: string }[] | undefined;
@@ -70,13 +70,10 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
             groups = await searchGroups(ldapConfig, groupSearchFilter, ldapConfig.groupSearchBase);
           }
 
-          const externalId = ldapConfig.uniqueUserAttribute ? user[ldapConfig.uniqueUserAttribute] : user.uidNumber;
-          const username = ldapConfig.uniqueUserAttribute ? externalId : user.uid;
-
           const { isUserCompleted, providerAuthToken } = await server.services.ldap.ldapLogin({
-            externalId,
-            username,
             ldapConfigId: ldapConfig.id,
+            externalId: user.uidNumber,
+            username: user.uid,
             firstName: user.givenName ?? user.cn ?? "",
             lastName: user.sn ?? "",
             email: user.mail,
@@ -141,7 +138,6 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
           url: z.string(),
           bindDN: z.string(),
           bindPass: z.string(),
-          uniqueUserAttribute: z.string(),
           searchBase: z.string(),
           searchFilter: z.string(),
           groupSearchBase: z.string(),
@@ -176,7 +172,6 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
         url: z.string().trim(),
         bindDN: z.string().trim(),
         bindPass: z.string().trim(),
-        uniqueUserAttribute: z.string().trim().default("uidNumber"),
         searchBase: z.string().trim(),
         searchFilter: z.string().trim().default("(uid={{username}})"),
         groupSearchBase: z.string().trim(),
@@ -218,7 +213,6 @@ export const registerLdapRouter = async (server: FastifyZodProvider) => {
           url: z.string().trim(),
           bindDN: z.string().trim(),
           bindPass: z.string().trim(),
-          uniqueUserAttribute: z.string().trim(),
           searchBase: z.string().trim(),
           searchFilter: z.string().trim(),
           groupSearchBase: z.string().trim(),

backend/src/ee/routes/v1/oidc-router.ts

@@ -1,355 +0,0 @@
-/* eslint-disable @typescript-eslint/no-explicit-any */
-/* eslint-disable @typescript-eslint/no-unsafe-return */
-/* eslint-disable @typescript-eslint/no-unsafe-member-access */
-/* eslint-disable @typescript-eslint/no-unsafe-assignment */
-/* eslint-disable @typescript-eslint/no-unsafe-call */
-/* eslint-disable @typescript-eslint/no-unsafe-argument */
-// All the any rules are disabled because passport typesense with fastify is really poor
-
-import { Authenticator, Strategy } from "@fastify/passport";
-import fastifySession from "@fastify/session";
-import RedisStore from "connect-redis";
-import { Redis } from "ioredis";
-import { z } from "zod";
-
-import { OidcConfigsSchema } from "@app/db/schemas/oidc-configs";
-import { OIDCConfigurationType } from "@app/ee/services/oidc/oidc-config-types";
-import { getConfig } from "@app/lib/config/env";
-import { authRateLimit, readLimit, writeLimit } from "@app/server/config/rateLimiter";
-import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
-import { AuthMode } from "@app/services/auth/auth-type";
-
-export const registerOidcRouter = async (server: FastifyZodProvider) => {
-  const appCfg = getConfig();
-  const redis = new Redis(appCfg.REDIS_URL);
-  const passport = new Authenticator({ key: "oidc", userProperty: "passportUser" });
-
-  /*
-  - OIDC protocol cannot work without sessions: https://github.com/panva/node-openid-client/issues/190
-  - Current redis usage is not ideal and will eventually have to be refactored to use a better structure
-  - Fastify session <> Redis structure is based on the ff: https://github.com/fastify/session/blob/master/examples/redis.js
-  */
-  const redisStore = new RedisStore({
-    client: redis,
-    prefix: "oidc-session:",
-    ttl: 600 // 10 minutes
-  });
-
-  await server.register(fastifySession, {
-    secret: appCfg.COOKIE_SECRET_SIGN_KEY,
-    store: redisStore,
-    cookie: {
-      secure: appCfg.HTTPS_ENABLED,
-      sameSite: "lax" // we want cookies to be sent to Infisical in redirects originating from IDP server
-    }
-  });
-
-  await server.register(passport.initialize());
-  await server.register(passport.secureSession());
-
-  // redirect to IDP for login
-  server.route({
-    url: "/login",
-    method: "GET",
-    config: {
-      rateLimit: authRateLimit
-    },
-    schema: {
-      querystring: z.object({
-        orgSlug: z.string().trim(),
-        callbackPort: z.string().trim().optional()
-      })
-    },
-    preValidation: [
-      async (req, res) => {
-        const { orgSlug, callbackPort } = req.query;
-
-        // ensure fresh session state per login attempt
-        await req.session.regenerate();
-
-        req.session.set<any>("oidcOrgSlug", orgSlug);
-
-        if (callbackPort) {
-          req.session.set<any>("callbackPort", callbackPort);
-        }
-
-        const oidcStrategy = await server.services.oidc.getOrgAuthStrategy(orgSlug, callbackPort);
-        return (
-          passport.authenticate(oidcStrategy as Strategy, {
-            scope: "profile email openid"
-          }) as any
-        )(req, res);
-      }
-    ],
-    handler: () => {}
-  });
-
-  // callback route after login from IDP
-  server.route({
-    url: "/callback",
-    method: "GET",
-    preValidation: [
-      async (req, res) => {
-        const oidcOrgSlug = req.session.get<any>("oidcOrgSlug");
-        const callbackPort = req.session.get<any>("callbackPort");
-        const oidcStrategy = await server.services.oidc.getOrgAuthStrategy(oidcOrgSlug, callbackPort);
-
-        return (
-          passport.authenticate(oidcStrategy as Strategy, {
-            failureRedirect: "/api/v1/sso/oidc/login/error",
-            session: false,
-            failureMessage: true
-          }) as any
-        )(req, res);
-      }
-    ],
-    handler: async (req, res) => {
-      await req.session.destroy();
-
-      if (req.passportUser.isUserCompleted) {
-        return res.redirect(
-          `${appCfg.SITE_URL}/login/sso?token=${encodeURIComponent(req.passportUser.providerAuthToken)}`
-        );
-      }
-
-      // signup
-      return res.redirect(
-        `${appCfg.SITE_URL}/signup/sso?token=${encodeURIComponent(req.passportUser.providerAuthToken)}`
-      );
-    }
-  });
-
-  server.route({
-    url: "/login/error",
-    method: "GET",
-    handler: async (req, res) => {
-      await req.session.destroy();
-
-      return res.status(500).send({
-        error: "Authentication error",
-        details: req.query
-      });
-    }
-  });
-
-  server.route({
-    url: "/config",
-    method: "GET",
-    config: {
-      rateLimit: readLimit
-    },
-    onRequest: verifyAuth([AuthMode.JWT]),
-    schema: {
-      querystring: z.object({
-        orgSlug: z.string().trim()
-      }),
-      response: {
-        200: OidcConfigsSchema.pick({
-          id: true,
-          issuer: true,
-          authorizationEndpoint: true,
-          jwksUri: true,
-          tokenEndpoint: true,
-          userinfoEndpoint: true,
-          configurationType: true,
-          discoveryURL: true,
-          isActive: true,
-          orgId: true,
-          allowedEmailDomains: true
-        }).extend({
-          clientId: z.string(),
-          clientSecret: z.string()
-        })
-      }
-    },
-    handler: async (req) => {
-      const { orgSlug } = req.query;
-      const oidc = await server.services.oidc.getOidc({
-        orgSlug,
-        type: "external",
-        actor: req.permission.type,
-        actorId: req.permission.id,
-        actorOrgId: req.permission.orgId,
-        actorAuthMethod: req.permission.authMethod
-      });
-
-      return oidc;
-    }
-  });
-
-  server.route({
-    method: "PATCH",
-    url: "/config",
-    config: {
-      rateLimit: writeLimit
-    },
-    onRequest: verifyAuth([AuthMode.JWT]),
-    schema: {
-      body: z
-        .object({
-          allowedEmailDomains: z
-            .string()
-            .trim()
-            .optional()
-            .default("")
-            .transform((data) => {
-              if (data === "") return "";
-              // Trim each ID and join with ', ' to ensure formatting
-              return data
-                .split(",")
-                .map((id) => id.trim())
-                .join(", ");
-            }),
-          discoveryURL: z.string().trim(),
-          configurationType: z.nativeEnum(OIDCConfigurationType),
-          issuer: z.string().trim(),
-          authorizationEndpoint: z.string().trim(),
-          jwksUri: z.string().trim(),
-          tokenEndpoint: z.string().trim(),
-          userinfoEndpoint: z.string().trim(),
-          clientId: z.string().trim(),
-          clientSecret: z.string().trim(),
-          isActive: z.boolean()
-        })
-        .partial()
-        .merge(z.object({ orgSlug: z.string() })),
-      response: {
-        200: OidcConfigsSchema.pick({
-          id: true,
-          issuer: true,
-          authorizationEndpoint: true,
-          configurationType: true,
-          discoveryURL: true,
-          jwksUri: true,
-          tokenEndpoint: true,
-          userinfoEndpoint: true,
-          orgId: true,
-          allowedEmailDomains: true,
-          isActive: true
-        })
-      }
-    },
-    handler: async (req) => {
-      const oidc = await server.services.oidc.updateOidcCfg({
-        actor: req.permission.type,
-        actorId: req.permission.id,
-        actorAuthMethod: req.permission.authMethod,
-        actorOrgId: req.permission.orgId,
-        ...req.body
-      });
-      return oidc;
-    }
-  });
-
-  server.route({
-    method: "POST",
-    url: "/config",
-    config: {
-      rateLimit: writeLimit
-    },
-    onRequest: verifyAuth([AuthMode.JWT]),
-    schema: {
-      body: z
-        .object({
-          allowedEmailDomains: z
-            .string()
-            .trim()
-            .optional()
-            .default("")
-            .transform((data) => {
-              if (data === "") return "";
-              // Trim each ID and join with ', ' to ensure formatting
-              return data
-                .split(",")
-                .map((id) => id.trim())
-                .join(", ");
-            }),
-          configurationType: z.nativeEnum(OIDCConfigurationType),
-          issuer: z.string().trim().optional().default(""),
-          discoveryURL: z.string().trim().optional().default(""),
-          authorizationEndpoint: z.string().trim().optional().default(""),
-          jwksUri: z.string().trim().optional().default(""),
-          tokenEndpoint: z.string().trim().optional().default(""),
-          userinfoEndpoint: z.string().trim().optional().default(""),
-          clientId: z.string().trim(),
-          clientSecret: z.string().trim(),
-          isActive: z.boolean(),
-          orgSlug: z.string().trim()
-        })
-        .superRefine((data, ctx) => {
-          if (data.configurationType === OIDCConfigurationType.CUSTOM) {
-            if (!data.issuer) {
-              ctx.addIssue({
-                path: ["issuer"],
-                message: "Issuer is required",
-                code: z.ZodIssueCode.custom
-              });
-            }
-            if (!data.authorizationEndpoint) {
-              ctx.addIssue({
-                path: ["authorizationEndpoint"],
-                message: "Authorization endpoint is required",
-                code: z.ZodIssueCode.custom
-              });
-            }
-            if (!data.jwksUri) {
-              ctx.addIssue({
-                path: ["jwksUri"],
-                message: "JWKS URI is required",
-                code: z.ZodIssueCode.custom
-              });
-            }
-            if (!data.tokenEndpoint) {
-              ctx.addIssue({
-                path: ["tokenEndpoint"],
-                message: "Token endpoint is required",
-                code: z.ZodIssueCode.custom
-              });
-            }
-            if (!data.userinfoEndpoint) {
-              ctx.addIssue({
-                path: ["userinfoEndpoint"],
-                message: "Userinfo endpoint is required",
-                code: z.ZodIssueCode.custom
-              });
-            }
-          } else {
-            // eslint-disable-next-line no-lonely-if
-            if (!data.discoveryURL) {
-              ctx.addIssue({
-                path: ["discoveryURL"],
-                message: "Discovery URL is required",
-                code: z.ZodIssueCode.custom
-              });
-            }
-          }
-        }),
-      response: {
-        200: OidcConfigsSchema.pick({
-          id: true,
-          issuer: true,
-          authorizationEndpoint: true,
-          configurationType: true,
-          discoveryURL: true,
-          jwksUri: true,
-          tokenEndpoint: true,
-          userinfoEndpoint: true,
-          orgId: true,
-          isActive: true,
-          allowedEmailDomains: true
-        })
-      }
-    },
-
-    handler: async (req) => {
-      const oidc = await server.services.oidc.createOidcCfg({
-        actor: req.permission.type,
-        actorId: req.permission.id,
-        actorAuthMethod: req.permission.authMethod,
-        actorOrgId: req.permission.orgId,
-        ...req.body
-      });
-      return oidc;
-    }
-  });
-};

backend/src/ee/routes/v1/scim-router.ts

@@ -475,13 +475,10 @@ export const registerScimRouter = async (server: FastifyZodProvider) => {
             }),
             z.object({
               op: z.literal("add"),
-              path: z.string().trim(),
-              value: z.array(
-                z.object({
-                  value: z.string().trim(),
-                  display: z.string().trim().optional()
-                })
-              )
+              value: z.object({
+                value: z.string().trim(),
+                display: z.string().trim().optional()
+              })
             })
           ])
         )

backend/src/ee/routes/v1/secret-approval-policy-router.ts

@@ -25,10 +25,10 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
             .optional()
             .nullable()
             .transform((val) => (val ? removeTrailingSlash(val) : val)),
-          approverUserIds: z.string().array().min(1),
+          approvers: z.string().array().min(1),
           approvals: z.number().min(1).default(1)
         })
-        .refine((data) => data.approvals <= data.approverUserIds.length, {
+        .refine((data) => data.approvals <= data.approvers.length, {
           path: ["approvals"],
           message: "The number of approvals should be lower than the number of approvers."
         }),
@@ -66,7 +66,7 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
       body: z
         .object({
           name: z.string().optional(),
-          approverUserIds: z.string().array().min(1),
+          approvers: z.string().array().min(1),
           approvals: z.number().min(1).default(1),
           secretPath: z
             .string()
@@ -74,7 +74,7 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
             .nullable()
             .transform((val) => (val ? removeTrailingSlash(val) : val))
         })
-        .refine((data) => data.approvals <= data.approverUserIds.length, {
+        .refine((data) => data.approvals <= data.approvers.length, {
           path: ["approvals"],
           message: "The number of approvals should be lower than the number of approvers."
         }),
@@ -139,15 +139,7 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
       }),
       response: {
         200: z.object({
-          approvals: sapPubSchema
-            .extend({
-              userApprovers: z
-                .object({
-                  userId: z.string()
-                })
-                .array()
-            })
-            .array()
+          approvals: sapPubSchema.merge(z.object({ approvers: z.string().array() })).array()
         })
       }
     },
@@ -178,11 +170,7 @@ export const registerSecretApprovalPolicyRouter = async (server: FastifyZodProvi
       }),
       response: {
         200: z.object({
-          policy: sapPubSchema
-            .extend({
-              userApprovers: z.object({ userId: z.string() }).array()
-            })
-            .optional()
+          policy: sapPubSchema.merge(z.object({ approvers: z.string().array() })).optional()
         })
       }
     },

backend/src/ee/routes/v1/secret-approval-request-router.ts

@@ -6,8 +6,7 @@ import {
   SecretApprovalRequestsSecretsSchema,
   SecretsSchema,
   SecretTagsSchema,
-  SecretVersionsSchema,
-  UsersSchema
+  SecretVersionsSchema
 } from "@app/db/schemas";
 import { EventType } from "@app/ee/services/audit-log/audit-log-types";
 import { ApprovalStatus, RequestState } from "@app/ee/services/secret-approval-request/secret-approval-request-types";
@@ -15,15 +14,6 @@ import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
 import { AuthMode } from "@app/services/auth/auth-type";
 
-const approvalRequestUser = z.object({ userId: z.string() }).merge(
-  UsersSchema.pick({
-    email: true,
-    firstName: true,
-    lastName: true,
-    username: true
-  })
-);
-
 export const registerSecretApprovalRequestRouter = async (server: FastifyZodProvider) => {
   server.route({
     method: "GET",
@@ -51,10 +41,9 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
               approvers: z.string().array(),
               secretPath: z.string().optional().nullable()
             }),
-            committerUser: approvalRequestUser,
             commits: z.object({ op: z.string(), secretId: z.string().nullable().optional() }).array(),
             environment: z.string(),
-            reviewers: z.object({ userId: z.string(), status: z.string() }).array(),
+            reviewers: z.object({ member: z.string(), status: z.string() }).array(),
             approvers: z.string().array()
           }).array()
         })
@@ -206,7 +195,7 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
           type: isClosing ? EventType.SECRET_APPROVAL_CLOSED : EventType.SECRET_APPROVAL_REOPENED,
           // eslint-disable-next-line
           metadata: {
-            [isClosing ? ("closedBy" as const) : ("reopenedBy" as const)]: approval.statusChangedByUserId as string,
+            [isClosing ? ("closedBy" as const) : ("reopenedBy" as const)]: approval.statusChangeBy as string,
             secretApprovalRequestId: approval.id,
             secretApprovalRequestSlug: approval.slug
             // eslint-disable-next-line
@@ -227,7 +216,6 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
   })
     .array()
     .optional();
-
   server.route({
     method: "GET",
     url: "/:id",
@@ -247,13 +235,12 @@ export const registerSecretApprovalRequestRouter = async (server: FastifyZodProv
                 id: z.string(),
                 name: z.string(),
                 approvals: z.number(),
-                approvers: approvalRequestUser.array(),
+                approvers: z.string().array(),
                 secretPath: z.string().optional().nullable()
               }),
               environment: z.string(),
-              statusChangedByUser: approvalRequestUser.optional(),
-              committerUser: approvalRequestUser,
-              reviewers: approvalRequestUser.extend({ status: z.string() }).array(),
+              reviewers: z.object({ member: z.string(), status: z.string() }).array(),
+              approvers: z.string().array(),
               secretPath: z.string(),
               commits: SecretApprovalRequestsSecretsSchema.omit({ secretBlindIndex: true })
                 .merge(

backend/src/ee/services/access-approval-policy/access-approval-policy-dal.ts

@@ -32,7 +32,7 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
 
   const findById = async (id: string, tx?: Knex) => {
     try {
-      const doc = await accessApprovalPolicyFindQuery(tx || db.replicaNode(), {
+      const doc = await accessApprovalPolicyFindQuery(tx || db, {
         [`${TableName.AccessApprovalPolicy}.id` as "id"]: id
       });
       const formatedDoc = mergeOneToManyRelation(
@@ -54,7 +54,7 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
 
   const find = async (filter: TFindFilter<TAccessApprovalPolicies & { projectId: string }>, tx?: Knex) => {
     try {
-      const docs = await accessApprovalPolicyFindQuery(tx || db.replicaNode(), filter);
+      const docs = await accessApprovalPolicyFindQuery(tx || db, filter);
       const formatedDoc = mergeOneToManyRelation(
         docs,
         "id",

backend/src/ee/services/access-approval-request/access-approval-request-dal.ts

@@ -14,8 +14,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
 
   const findRequestsWithPrivilegeByPolicyIds = async (policyIds: string[]) => {
     try {
-      const docs = await db
-        .replicaNode()(TableName.AccessApprovalRequest)
+      const docs = await db(TableName.AccessApprovalRequest)
         .whereIn(`${TableName.AccessApprovalRequest}.policyId`, policyIds)
 
         .leftJoin(
@@ -171,7 +170,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
 
   const findById = async (id: string, tx?: Knex) => {
     try {
-      const sql = findQuery({ [`${TableName.AccessApprovalRequest}.id` as "id"]: id }, tx || db.replicaNode());
+      const sql = findQuery({ [`${TableName.AccessApprovalRequest}.id` as "id"]: id }, tx || db);
       const docs = await sql;
       const formatedDoc = sqlNestRelationships({
         data: docs,
@@ -208,8 +207,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
 
   const getCount = async ({ projectId }: { projectId: string }) => {
     try {
-      const accessRequests = await db
-        .replicaNode()(TableName.AccessApprovalRequest)
+      const accessRequests = await db(TableName.AccessApprovalRequest)
         .leftJoin(
           TableName.AccessApprovalPolicy,
           `${TableName.AccessApprovalRequest}.policyId`,

backend/src/ee/services/audit-log/audit-log-dal.ts

@@ -4,7 +4,6 @@ import { TDbClient } from "@app/db";
 import { TableName } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
 import { ormify, stripUndefinedInWhere } from "@app/lib/knex";
-import { logger } from "@app/lib/logger";
 
 export type TAuditLogDALFactory = ReturnType<typeof auditLogDALFactory>;
 
@@ -28,7 +27,7 @@ export const auditLogDALFactory = (db: TDbClient) => {
     tx?: Knex
   ) => {
     try {
-      const sqlQuery = (tx || db.replicaNode())(TableName.AuditLog)
+      const sqlQuery = (tx || db)(TableName.AuditLog)
         .where(
           stripUndefinedInWhere({
             projectId,
@@ -56,34 +55,13 @@ export const auditLogDALFactory = (db: TDbClient) => {
 
   // delete all audit log that have expired
   const pruneAuditLog = async (tx?: Knex) => {
-    const AUDIT_LOG_PRUNE_BATCH_SIZE = 10000;
-    const MAX_RETRY_ON_FAILURE = 3;
-
-    const today = new Date();
-    let deletedAuditLogIds: { id: string }[] = [];
-    let numberOfRetryOnFailure = 0;
-
-    do {
-      try {
-        const findExpiredLogSubQuery = (tx || db)(TableName.AuditLog)
-          .where("expiresAt", "<", today)
-          .select("id")
-          .limit(AUDIT_LOG_PRUNE_BATCH_SIZE);
-        // eslint-disable-next-line no-await-in-loop
-        deletedAuditLogIds = await (tx || db)(TableName.AuditLog)
-          .whereIn("id", findExpiredLogSubQuery)
-          .del()
-          .returning("id");
-        numberOfRetryOnFailure = 0; // reset
-        // eslint-disable-next-line no-await-in-loop
-        await new Promise((resolve) => {
-          setTimeout(resolve, 100); // time to breathe for db
-        });
-      } catch (error) {
-        numberOfRetryOnFailure += 1;
-        logger.error(error, "Failed to delete audit log on pruning");
-      }
-    } while (deletedAuditLogIds.length > 0 && numberOfRetryOnFailure < MAX_RETRY_ON_FAILURE);
+    try {
+      const today = new Date();
+      const docs = await (tx || db)(TableName.AuditLog).where("expiresAt", "<", today).del();
+      return docs;
+    } catch (error) {
+      throw new DatabaseError({ error, name: "PruneAuditLog" });
+    }
   };
 
   return { ...auditLogOrm, pruneAuditLog, find };

backend/src/ee/services/audit-log/audit-log-queue.ts

@@ -45,29 +45,18 @@ export const auditLogQueueServiceFactory = ({
     const { actor, event, ipAddress, projectId, userAgent, userAgentType } = job.data;
     let { orgId } = job.data;
     const MS_IN_DAY = 24 * 60 * 60 * 1000;
-    let project;
 
     if (!orgId) {
       // it will never be undefined for both org and project id
       // TODO(akhilmhdh): use caching here in dal to avoid db calls
-      project = await projectDAL.findById(projectId as string);
+      const project = await projectDAL.findById(projectId as string);
       orgId = project.orgId;
     }
 
     const plan = await licenseService.getPlan(orgId);
-    if (plan.auditLogsRetentionDays === 0) {
-      // skip inserting if audit log retention is 0 meaning its not supported
-      return;
-    }
-
-    // For project actions, set TTL to project-level audit log retention config
-    // This condition ensures that the plan's audit log retention days cannot be bypassed
-    const ttlInDays =
-      project?.auditLogsRetentionDays && project.auditLogsRetentionDays < plan.auditLogsRetentionDays
-        ? project.auditLogsRetentionDays
-        : plan.auditLogsRetentionDays;
-
-    const ttl = ttlInDays * MS_IN_DAY;
+    const ttl = plan.auditLogsRetentionDays * MS_IN_DAY;
+    // skip inserting if audit log retention is 0 meaning its not supported
+    if (ttl === 0) return;
 
     const auditLog = await auditLogDAL.create({
       actor: actor.type,

backend/src/ee/services/audit-log/audit-log-types.ts

@@ -45,7 +45,6 @@ export enum EventType {
   CREATE_SECRETS = "create-secrets",
   UPDATE_SECRET = "update-secret",
   UPDATE_SECRETS = "update-secrets",
-  MOVE_SECRETS = "move-secrets",
   DELETE_SECRET = "delete-secret",
   DELETE_SECRETS = "delete-secrets",
   GET_WORKSPACE_KEY = "get-workspace-key",
@@ -66,43 +65,25 @@ export enum EventType {
   ADD_IDENTITY_UNIVERSAL_AUTH = "add-identity-universal-auth",
   UPDATE_IDENTITY_UNIVERSAL_AUTH = "update-identity-universal-auth",
   GET_IDENTITY_UNIVERSAL_AUTH = "get-identity-universal-auth",
-  REVOKE_IDENTITY_UNIVERSAL_AUTH = "revoke-identity-universal-auth",
-  CREATE_TOKEN_IDENTITY_TOKEN_AUTH = "create-token-identity-token-auth",
-  UPDATE_TOKEN_IDENTITY_TOKEN_AUTH = "update-token-identity-token-auth",
-  GET_TOKENS_IDENTITY_TOKEN_AUTH = "get-tokens-identity-token-auth",
-  ADD_IDENTITY_TOKEN_AUTH = "add-identity-token-auth",
-  UPDATE_IDENTITY_TOKEN_AUTH = "update-identity-token-auth",
-  GET_IDENTITY_TOKEN_AUTH = "get-identity-token-auth",
-  REVOKE_IDENTITY_TOKEN_AUTH = "revoke-identity-token-auth",
   LOGIN_IDENTITY_KUBERNETES_AUTH = "login-identity-kubernetes-auth",
   ADD_IDENTITY_KUBERNETES_AUTH = "add-identity-kubernetes-auth",
   UPDATE_IDENTITY_KUBENETES_AUTH = "update-identity-kubernetes-auth",
   GET_IDENTITY_KUBERNETES_AUTH = "get-identity-kubernetes-auth",
-  REVOKE_IDENTITY_KUBERNETES_AUTH = "revoke-identity-kubernetes-auth",
-  LOGIN_IDENTITY_OIDC_AUTH = "login-identity-oidc-auth",
-  ADD_IDENTITY_OIDC_AUTH = "add-identity-oidc-auth",
-  UPDATE_IDENTITY_OIDC_AUTH = "update-identity-oidc-auth",
-  GET_IDENTITY_OIDC_AUTH = "get-identity-oidc-auth",
-  REVOKE_IDENTITY_OIDC_AUTH = "revoke-identity-oidc-auth",
   CREATE_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET = "create-identity-universal-auth-client-secret",
   REVOKE_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET = "revoke-identity-universal-auth-client-secret",
   GET_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRETS = "get-identity-universal-auth-client-secret",
-  GET_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET_BY_ID = "get-identity-universal-auth-client-secret-by-id",
   LOGIN_IDENTITY_GCP_AUTH = "login-identity-gcp-auth",
   ADD_IDENTITY_GCP_AUTH = "add-identity-gcp-auth",
   UPDATE_IDENTITY_GCP_AUTH = "update-identity-gcp-auth",
-  REVOKE_IDENTITY_GCP_AUTH = "revoke-identity-gcp-auth",
   GET_IDENTITY_GCP_AUTH = "get-identity-gcp-auth",
   LOGIN_IDENTITY_AWS_AUTH = "login-identity-aws-auth",
   ADD_IDENTITY_AWS_AUTH = "add-identity-aws-auth",
   UPDATE_IDENTITY_AWS_AUTH = "update-identity-aws-auth",
-  REVOKE_IDENTITY_AWS_AUTH = "revoke-identity-aws-auth",
   GET_IDENTITY_AWS_AUTH = "get-identity-aws-auth",
   LOGIN_IDENTITY_AZURE_AUTH = "login-identity-azure-auth",
   ADD_IDENTITY_AZURE_AUTH = "add-identity-azure-auth",
   UPDATE_IDENTITY_AZURE_AUTH = "update-identity-azure-auth",
   GET_IDENTITY_AZURE_AUTH = "get-identity-azure-auth",
-  REVOKE_IDENTITY_AZURE_AUTH = "revoke-identity-azure-auth",
   CREATE_ENVIRONMENT = "create-environment",
   UPDATE_ENVIRONMENT = "update-environment",
   DELETE_ENVIRONMENT = "delete-environment",
@@ -241,17 +222,6 @@ interface UpdateSecretBatchEvent {
   };
 }
 
-interface MoveSecretsEvent {
-  type: EventType.MOVE_SECRETS;
-  metadata: {
-    sourceEnvironment: string;
-    sourceSecretPath: string;
-    destinationEnvironment: string;
-    destinationSecretPath: string;
-    secretIds: string[];
-  };
-}
-
 interface DeleteSecretEvent {
   type: EventType.DELETE_SECRET;
   metadata: {
@@ -464,73 +434,6 @@ interface GetIdentityUniversalAuthEvent {
   };
 }
 
-interface DeleteIdentityUniversalAuthEvent {
-  type: EventType.REVOKE_IDENTITY_UNIVERSAL_AUTH;
-  metadata: {
-    identityId: string;
-  };
-}
-
-interface CreateTokenIdentityTokenAuthEvent {
-  type: EventType.CREATE_TOKEN_IDENTITY_TOKEN_AUTH;
-  metadata: {
-    identityId: string;
-    identityAccessTokenId: string;
-  };
-}
-
-interface UpdateTokenIdentityTokenAuthEvent {
-  type: EventType.UPDATE_TOKEN_IDENTITY_TOKEN_AUTH;
-  metadata: {
-    identityId: string;
-    tokenId: string;
-    name?: string;
-  };
-}
-
-interface GetTokensIdentityTokenAuthEvent {
-  type: EventType.GET_TOKENS_IDENTITY_TOKEN_AUTH;
-  metadata: {
-    identityId: string;
-  };
-}
-
-interface AddIdentityTokenAuthEvent {
-  type: EventType.ADD_IDENTITY_TOKEN_AUTH;
-  metadata: {
-    identityId: string;
-    accessTokenTTL: number;
-    accessTokenMaxTTL: number;
-    accessTokenNumUsesLimit: number;
-    accessTokenTrustedIps: Array<TIdentityTrustedIp>;
-  };
-}
-
-interface UpdateIdentityTokenAuthEvent {
-  type: EventType.UPDATE_IDENTITY_TOKEN_AUTH;
-  metadata: {
-    identityId: string;
-    accessTokenTTL?: number;
-    accessTokenMaxTTL?: number;
-    accessTokenNumUsesLimit?: number;
-    accessTokenTrustedIps?: Array<TIdentityTrustedIp>;
-  };
-}
-
-interface GetIdentityTokenAuthEvent {
-  type: EventType.GET_IDENTITY_TOKEN_AUTH;
-  metadata: {
-    identityId: string;
-  };
-}
-
-interface DeleteIdentityTokenAuthEvent {
-  type: EventType.REVOKE_IDENTITY_TOKEN_AUTH;
-  metadata: {
-    identityId: string;
-  };
-}
-
 interface LoginIdentityKubernetesAuthEvent {
   type: EventType.LOGIN_IDENTITY_KUBERNETES_AUTH;
   metadata: {
@@ -554,13 +457,6 @@ interface AddIdentityKubernetesAuthEvent {
   };
 }
 
-interface DeleteIdentityKubernetesAuthEvent {
-  type: EventType.REVOKE_IDENTITY_KUBERNETES_AUTH;
-  metadata: {
-    identityId: string;
-  };
-}
-
 interface UpdateIdentityKubernetesAuthEvent {
   type: EventType.UPDATE_IDENTITY_KUBENETES_AUTH;
   metadata: {
@@ -597,14 +493,6 @@ interface GetIdentityUniversalAuthClientSecretsEvent {
   };
 }
 
-interface GetIdentityUniversalAuthClientSecretByIdEvent {
-  type: EventType.GET_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET_BY_ID;
-  metadata: {
-    identityId: string;
-    clientSecretId: string;
-  };
-}
-
 interface RevokeIdentityUniversalAuthClientSecretEvent {
   type: EventType.REVOKE_IDENTITY_UNIVERSAL_AUTH_CLIENT_SECRET;
   metadata: {
@@ -637,13 +525,6 @@ interface AddIdentityGcpAuthEvent {
   };
 }
 
-interface DeleteIdentityGcpAuthEvent {
-  type: EventType.REVOKE_IDENTITY_GCP_AUTH;
-  metadata: {
-    identityId: string;
-  };
-}
-
 interface UpdateIdentityGcpAuthEvent {
   type: EventType.UPDATE_IDENTITY_GCP_AUTH;
   metadata: {
@@ -689,13 +570,6 @@ interface AddIdentityAwsAuthEvent {
   };
 }
 
-interface DeleteIdentityAwsAuthEvent {
-  type: EventType.REVOKE_IDENTITY_AWS_AUTH;
-  metadata: {
-    identityId: string;
-  };
-}
-
 interface UpdateIdentityAwsAuthEvent {
   type: EventType.UPDATE_IDENTITY_AWS_AUTH;
   metadata: {
@@ -739,13 +613,6 @@ interface AddIdentityAzureAuthEvent {
   };
 }
 
-interface DeleteIdentityAzureAuthEvent {
-  type: EventType.REVOKE_IDENTITY_AZURE_AUTH;
-  metadata: {
-    identityId: string;
-  };
-}
-
 interface UpdateIdentityAzureAuthEvent {
   type: EventType.UPDATE_IDENTITY_AZURE_AUTH;
   metadata: {
@@ -766,63 +633,6 @@ interface GetIdentityAzureAuthEvent {
   };
 }
 
-interface LoginIdentityOidcAuthEvent {
-  type: EventType.LOGIN_IDENTITY_OIDC_AUTH;
-  metadata: {
-    identityId: string;
-    identityOidcAuthId: string;
-    identityAccessTokenId: string;
-  };
-}
-
-interface AddIdentityOidcAuthEvent {
-  type: EventType.ADD_IDENTITY_OIDC_AUTH;
-  metadata: {
-    identityId: string;
-    oidcDiscoveryUrl: string;
-    caCert: string;
-    boundIssuer: string;
-    boundAudiences: string;
-    boundClaims: Record<string, string>;
-    boundSubject: string;
-    accessTokenTTL: number;
-    accessTokenMaxTTL: number;
-    accessTokenNumUsesLimit: number;
-    accessTokenTrustedIps: Array<TIdentityTrustedIp>;
-  };
-}
-
-interface DeleteIdentityOidcAuthEvent {
-  type: EventType.REVOKE_IDENTITY_OIDC_AUTH;
-  metadata: {
-    identityId: string;
-  };
-}
-
-interface UpdateIdentityOidcAuthEvent {
-  type: EventType.UPDATE_IDENTITY_OIDC_AUTH;
-  metadata: {
-    identityId: string;
-    oidcDiscoveryUrl?: string;
-    caCert?: string;
-    boundIssuer?: string;
-    boundAudiences?: string;
-    boundClaims?: Record<string, string>;
-    boundSubject?: string;
-    accessTokenTTL?: number;
-    accessTokenMaxTTL?: number;
-    accessTokenNumUsesLimit?: number;
-    accessTokenTrustedIps?: Array<TIdentityTrustedIp>;
-  };
-}
-
-interface GetIdentityOidcAuthEvent {
-  type: EventType.GET_IDENTITY_OIDC_AUTH;
-  metadata: {
-    identityId: string;
-  };
-}
-
 interface CreateEnvironmentEvent {
   type: EventType.CREATE_ENVIRONMENT;
   metadata: {
@@ -912,6 +722,7 @@ interface CreateWebhookEvent {
     webhookId: string;
     environment: string;
     secretPath: string;
+    webhookUrl: string;
     isDisabled: boolean;
   };
 }
@@ -922,6 +733,7 @@ interface UpdateWebhookStatusEvent {
     webhookId: string;
     environment: string;
     secretPath: string;
+    webhookUrl: string;
     isDisabled: boolean;
   };
 }
@@ -932,6 +744,7 @@ interface DeleteWebhookEvent {
     webhookId: string;
     environment: string;
     secretPath: string;
+    webhookUrl: string;
     isDisabled: boolean;
   };
 }
@@ -1171,7 +984,6 @@ export type Event =
   | CreateSecretBatchEvent
   | UpdateSecretEvent
   | UpdateSecretBatchEvent
-  | MoveSecretsEvent
   | DeleteSecretEvent
   | DeleteSecretBatchEvent
   | GetWorkspaceKeyEvent
@@ -1191,44 +1003,26 @@ export type Event =
   | LoginIdentityUniversalAuthEvent
   | AddIdentityUniversalAuthEvent
   | UpdateIdentityUniversalAuthEvent
-  | DeleteIdentityUniversalAuthEvent
   | GetIdentityUniversalAuthEvent
-  | CreateTokenIdentityTokenAuthEvent
-  | UpdateTokenIdentityTokenAuthEvent
-  | GetTokensIdentityTokenAuthEvent
-  | AddIdentityTokenAuthEvent
-  | UpdateIdentityTokenAuthEvent
-  | GetIdentityTokenAuthEvent
-  | DeleteIdentityTokenAuthEvent
   | LoginIdentityKubernetesAuthEvent
-  | DeleteIdentityKubernetesAuthEvent
   | AddIdentityKubernetesAuthEvent
   | UpdateIdentityKubernetesAuthEvent
   | GetIdentityKubernetesAuthEvent
   | CreateIdentityUniversalAuthClientSecretEvent
   | GetIdentityUniversalAuthClientSecretsEvent
-  | GetIdentityUniversalAuthClientSecretByIdEvent
   | RevokeIdentityUniversalAuthClientSecretEvent
   | LoginIdentityGcpAuthEvent
   | AddIdentityGcpAuthEvent
-  | DeleteIdentityGcpAuthEvent
   | UpdateIdentityGcpAuthEvent
   | GetIdentityGcpAuthEvent
   | LoginIdentityAwsAuthEvent
   | AddIdentityAwsAuthEvent
   | UpdateIdentityAwsAuthEvent
   | GetIdentityAwsAuthEvent
-  | DeleteIdentityAwsAuthEvent
   | LoginIdentityAzureAuthEvent
   | AddIdentityAzureAuthEvent
-  | DeleteIdentityAzureAuthEvent
   | UpdateIdentityAzureAuthEvent
   | GetIdentityAzureAuthEvent
-  | LoginIdentityOidcAuthEvent
-  | AddIdentityOidcAuthEvent
-  | DeleteIdentityOidcAuthEvent
-  | UpdateIdentityOidcAuthEvent
-  | GetIdentityOidcAuthEvent
   | CreateEnvironmentEvent
   | UpdateEnvironmentEvent
   | DeleteEnvironmentEvent

backend/src/ee/services/certificate-authority-crl/certificate-authority-crl-service.ts

@@ -17,7 +17,7 @@ type TCertificateAuthorityCrlServiceFactoryDep = {
   certificateAuthorityDAL: Pick<TCertificateAuthorityDALFactory, "findById">;
   certificateAuthorityCrlDAL: Pick<TCertificateAuthorityCrlDALFactory, "findOne">;
   projectDAL: Pick<TProjectDALFactory, "findOne" | "updateById" | "transaction">;
-  kmsService: Pick<TKmsServiceFactory, "decryptWithKmsKey" | "generateKmsKey">;
+  kmsService: Pick<TKmsServiceFactory, "decrypt" | "generateKmsKey">;
   permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
   licenseService: Pick<TLicenseServiceFactory, "getPlan">;
 };
@@ -68,11 +68,11 @@ export const certificateAuthorityCrlServiceFactory = ({
       kmsService
     });
 
-    const kmsDecryptor = await kmsService.decryptWithKmsKey({
-      kmsId: keyId
+    const decryptedCrl = await kmsService.decrypt({
+      kmsId: keyId,
+      cipherTextBlob: caCrl.encryptedCrl
     });
 
-    const decryptedCrl = kmsDecryptor({ cipherTextBlob: caCrl.encryptedCrl });
     const crl = new x509.X509Crl(decryptedCrl);
 
     const base64crl = crl.toString("base64");

backend/src/ee/services/dynamic-secret-lease/dynamic-secret-lease-dal.ts

@@ -12,10 +12,7 @@ export const dynamicSecretLeaseDALFactory = (db: TDbClient) => {
 
   const countLeasesForDynamicSecret = async (dynamicSecretId: string, tx?: Knex) => {
     try {
-      const doc = await (tx || db.replicaNode())(TableName.DynamicSecretLease)
-        .count("*")
-        .where({ dynamicSecretId })
-        .first();
+      const doc = await (tx || db)(TableName.DynamicSecretLease).count("*").where({ dynamicSecretId }).first();
       return parseInt(doc || "0", 10);
     } catch (error) {
       throw new DatabaseError({ error, name: "DynamicSecretCountLeases" });
@@ -24,7 +21,7 @@ export const dynamicSecretLeaseDALFactory = (db: TDbClient) => {
 
   const findById = async (id: string, tx?: Knex) => {
     try {
-      const doc = await (tx || db.replicaNode())(TableName.DynamicSecretLease)
+      const doc = await (tx || db)(TableName.DynamicSecretLease)
         .where({ [`${TableName.DynamicSecretLease}.id` as "id"]: id })
         .first()
         .join(

backend/src/ee/services/dynamic-secret/providers/models.ts

@@ -3,8 +3,7 @@ import { z } from "zod";
 export enum SqlProviders {
   Postgres = "postgres",
   MySQL = "mysql2",
-  Oracle = "oracledb",
-  MsSQL = "mssql"
+  Oracle = "oracledb"
 }
 
 export const DynamicSecretSqlDBSchema = z.object({

backend/src/ee/services/external-kms/external-kms-dal.ts

@@ -1,47 +0,0 @@
-import { Knex } from "knex";
-
-import { TDbClient } from "@app/db";
-import { TableName, TKmsKeys } from "@app/db/schemas";
-import { DatabaseError } from "@app/lib/errors";
-import { ormify, selectAllTableCols } from "@app/lib/knex";
-
-export type TExternalKmsDALFactory = ReturnType<typeof externalKmsDALFactory>;
-
-export const externalKmsDALFactory = (db: TDbClient) => {
-  const externalKmsOrm = ormify(db, TableName.ExternalKms);
-
-  const find = async (filter: Partial<TKmsKeys>, tx?: Knex) => {
-    try {
-      const result = await (tx || db.replicaNode())(TableName.ExternalKms)
-        .join(TableName.KmsKey, `${TableName.KmsKey}.id`, `${TableName.ExternalKms}.kmsKeyId`)
-        .where(filter)
-        .select(selectAllTableCols(TableName.KmsKey))
-        .select(
-          db.ref("id").withSchema(TableName.ExternalKms).as("externalKmsId"),
-          db.ref("provider").withSchema(TableName.ExternalKms).as("externalKmsProvider"),
-          db.ref("encryptedProviderInputs").withSchema(TableName.ExternalKms).as("externalKmsEncryptedProviderInput"),
-          db.ref("status").withSchema(TableName.ExternalKms).as("externalKmsStatus"),
-          db.ref("statusDetails").withSchema(TableName.ExternalKms).as("externalKmsStatusDetails")
-        );
-
-      return result.map((el) => ({
-        id: el.id,
-        description: el.description,
-        isDisabled: el.isDisabled,
-        isReserved: el.isReserved,
-        orgId: el.orgId,
-        slug: el.slug,
-        externalKms: {
-          id: el.externalKmsId,
-          provider: el.externalKmsProvider,
-          status: el.externalKmsStatus,
-          statusDetails: el.externalKmsStatusDetails
-        }
-      }));
-    } catch (error) {
-      throw new DatabaseError({ error, name: "Find" });
-    }
-  };
-
-  return { ...externalKmsOrm, find };
-};

backend/src/ee/services/external-kms/external-kms-service.ts

@@ -1,309 +0,0 @@
-import { ForbiddenError } from "@casl/ability";
-import slugify from "@sindresorhus/slugify";
-
-import { BadRequestError } from "@app/lib/errors";
-import { alphaNumericNanoId } from "@app/lib/nanoid";
-import { TKmsKeyDALFactory } from "@app/services/kms/kms-key-dal";
-import { TKmsServiceFactory } from "@app/services/kms/kms-service";
-
-import { OrgPermissionActions, OrgPermissionSubjects } from "../permission/org-permission";
-import { TPermissionServiceFactory } from "../permission/permission-service";
-import { TExternalKmsDALFactory } from "./external-kms-dal";
-import {
-  TCreateExternalKmsDTO,
-  TDeleteExternalKmsDTO,
-  TGetExternalKmsByIdDTO,
-  TGetExternalKmsBySlugDTO,
-  TListExternalKmsDTO,
-  TUpdateExternalKmsDTO
-} from "./external-kms-types";
-import { AwsKmsProviderFactory } from "./providers/aws-kms";
-import { ExternalKmsAwsSchema, KmsProviders } from "./providers/model";
-
-type TExternalKmsServiceFactoryDep = {
-  externalKmsDAL: TExternalKmsDALFactory;
-  kmsService: Pick<TKmsServiceFactory, "getOrgKmsKeyId" | "encryptWithKmsKey" | "decryptWithKmsKey">;
-  kmsDAL: Pick<TKmsKeyDALFactory, "create" | "updateById" | "findById" | "deleteById" | "findOne">;
-  permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
-};
-
-export type TExternalKmsServiceFactory = ReturnType<typeof externalKmsServiceFactory>;
-
-export const externalKmsServiceFactory = ({
-  externalKmsDAL,
-  permissionService,
-  kmsService,
-  kmsDAL
-}: TExternalKmsServiceFactoryDep) => {
-  const create = async ({
-    provider,
-    description,
-    actor,
-    slug,
-    actorId,
-    actorOrgId,
-    actorAuthMethod
-  }: TCreateExternalKmsDTO) => {
-    const { permission } = await permissionService.getOrgPermission(
-      actor,
-      actorId,
-      actorOrgId,
-      actorAuthMethod,
-      actorOrgId
-    );
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Settings);
-    const kmsSlug = slug ? slugify(slug) : slugify(alphaNumericNanoId(32));
-
-    let sanitizedProviderInput = "";
-    switch (provider.type) {
-      case KmsProviders.Aws:
-        {
-          const externalKms = await AwsKmsProviderFactory({ inputs: provider.inputs });
-          await externalKms.validateConnection();
-          // if missing kms key this generate a new kms key id and returns new provider input
-          const newProviderInput = await externalKms.generateInputKmsKey();
-          sanitizedProviderInput = JSON.stringify(newProviderInput);
-        }
-        break;
-      default:
-        throw new BadRequestError({ message: "external kms provided is invalid" });
-    }
-
-    const orgKmsKeyId = await kmsService.getOrgKmsKeyId(actorOrgId);
-    const kmsEncryptor = await kmsService.encryptWithKmsKey({
-      kmsId: orgKmsKeyId
-    });
-    const { cipherTextBlob: encryptedProviderInputs } = kmsEncryptor({
-      plainText: Buffer.from(sanitizedProviderInput, "utf8")
-    });
-
-    const externalKms = await externalKmsDAL.transaction(async (tx) => {
-      const kms = await kmsDAL.create(
-        {
-          isReserved: false,
-          description,
-          slug: kmsSlug,
-          orgId: actorOrgId
-        },
-        tx
-      );
-      const externalKmsCfg = await externalKmsDAL.create(
-        {
-          provider: provider.type,
-          encryptedProviderInputs,
-          kmsKeyId: kms.id
-        },
-        tx
-      );
-      return { ...kms, external: externalKmsCfg };
-    });
-
-    return externalKms;
-  };
-
-  const updateById = async ({
-    provider,
-    description,
-    actor,
-    id: kmsId,
-    slug,
-    actorId,
-    actorOrgId,
-    actorAuthMethod
-  }: TUpdateExternalKmsDTO) => {
-    const kmsDoc = await kmsDAL.findById(kmsId);
-    const { permission } = await permissionService.getOrgPermission(
-      actor,
-      actorId,
-      kmsDoc.orgId,
-      actorAuthMethod,
-      actorOrgId
-    );
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Settings);
-    const kmsSlug = slug ? slugify(slug) : undefined;
-
-    const externalKmsDoc = await externalKmsDAL.findOne({ kmsKeyId: kmsDoc.id });
-    if (!externalKmsDoc) throw new BadRequestError({ message: "External kms not found" });
-
-    const orgDefaultKmsId = await kmsService.getOrgKmsKeyId(kmsDoc.orgId);
-    let sanitizedProviderInput = "";
-    if (provider) {
-      const kmsDecryptor = await kmsService.decryptWithKmsKey({
-        kmsId: orgDefaultKmsId
-      });
-      const decryptedProviderInputBlob = kmsDecryptor({
-        cipherTextBlob: externalKmsDoc.encryptedProviderInputs
-      });
-
-      switch (provider.type) {
-        case KmsProviders.Aws:
-          {
-            const decryptedProviderInput = await ExternalKmsAwsSchema.parseAsync(
-              JSON.parse(decryptedProviderInputBlob.toString("utf8"))
-            );
-            const updatedProviderInput = { ...decryptedProviderInput, ...provider.inputs };
-            const externalKms = await AwsKmsProviderFactory({ inputs: updatedProviderInput });
-            await externalKms.validateConnection();
-            sanitizedProviderInput = JSON.stringify(updatedProviderInput);
-          }
-          break;
-        default:
-          throw new BadRequestError({ message: "external kms provided is invalid" });
-      }
-    }
-
-    let encryptedProviderInputs: Buffer | undefined;
-    if (sanitizedProviderInput) {
-      const kmsEncryptor = await kmsService.encryptWithKmsKey({
-        kmsId: orgDefaultKmsId
-      });
-      const { cipherTextBlob } = kmsEncryptor({
-        plainText: Buffer.from(sanitizedProviderInput, "utf8")
-      });
-      encryptedProviderInputs = cipherTextBlob;
-    }
-
-    const externalKms = await externalKmsDAL.transaction(async (tx) => {
-      const kms = await kmsDAL.updateById(
-        kmsDoc.id,
-        {
-          description,
-          slug: kmsSlug
-        },
-        tx
-      );
-      if (encryptedProviderInputs) {
-        const externalKmsCfg = await externalKmsDAL.updateById(
-          externalKmsDoc.id,
-          {
-            encryptedProviderInputs
-          },
-          tx
-        );
-        return { ...kms, external: externalKmsCfg };
-      }
-      return { ...kms, external: externalKmsDoc };
-    });
-
-    return externalKms;
-  };
-
-  const deleteById = async ({ actor, id: kmsId, actorId, actorOrgId, actorAuthMethod }: TDeleteExternalKmsDTO) => {
-    const kmsDoc = await kmsDAL.findById(kmsId);
-    const { permission } = await permissionService.getOrgPermission(
-      actor,
-      actorId,
-      kmsDoc.orgId,
-      actorAuthMethod,
-      actorOrgId
-    );
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Settings);
-
-    const externalKmsDoc = await externalKmsDAL.findOne({ kmsKeyId: kmsDoc.id });
-    if (!externalKmsDoc) throw new BadRequestError({ message: "External kms not found" });
-
-    const externalKms = await externalKmsDAL.transaction(async (tx) => {
-      const kms = await kmsDAL.deleteById(kmsDoc.id, tx);
-      return { ...kms, external: externalKmsDoc };
-    });
-
-    return externalKms;
-  };
-
-  const list = async ({ actor, actorId, actorOrgId, actorAuthMethod }: TListExternalKmsDTO) => {
-    const { permission } = await permissionService.getOrgPermission(
-      actor,
-      actorId,
-      actorOrgId,
-      actorAuthMethod,
-      actorOrgId
-    );
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Settings);
-
-    const externalKmsDocs = await externalKmsDAL.find({ orgId: actorOrgId });
-
-    return externalKmsDocs;
-  };
-
-  const findById = async ({ actor, actorId, actorOrgId, actorAuthMethod, id: kmsId }: TGetExternalKmsByIdDTO) => {
-    const kmsDoc = await kmsDAL.findById(kmsId);
-    const { permission } = await permissionService.getOrgPermission(
-      actor,
-      actorId,
-      kmsDoc.orgId,
-      actorAuthMethod,
-      actorOrgId
-    );
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Settings);
-
-    const externalKmsDoc = await externalKmsDAL.findOne({ kmsKeyId: kmsDoc.id });
-    if (!externalKmsDoc) throw new BadRequestError({ message: "External kms not found" });
-
-    const orgDefaultKmsId = await kmsService.getOrgKmsKeyId(kmsDoc.orgId);
-    const kmsDecryptor = await kmsService.decryptWithKmsKey({
-      kmsId: orgDefaultKmsId
-    });
-    const decryptedProviderInputBlob = kmsDecryptor({
-      cipherTextBlob: externalKmsDoc.encryptedProviderInputs
-    });
-    switch (externalKmsDoc.provider) {
-      case KmsProviders.Aws: {
-        const decryptedProviderInput = await ExternalKmsAwsSchema.parseAsync(
-          JSON.parse(decryptedProviderInputBlob.toString("utf8"))
-        );
-        return { ...kmsDoc, external: { ...externalKmsDoc, providerInput: decryptedProviderInput } };
-      }
-      default:
-        throw new BadRequestError({ message: "external kms provided is invalid" });
-    }
-  };
-
-  const findBySlug = async ({
-    actor,
-    actorId,
-    actorOrgId,
-    actorAuthMethod,
-    slug: kmsSlug
-  }: TGetExternalKmsBySlugDTO) => {
-    const kmsDoc = await kmsDAL.findOne({ slug: kmsSlug, orgId: actorOrgId });
-    const { permission } = await permissionService.getOrgPermission(
-      actor,
-      actorId,
-      kmsDoc.orgId,
-      actorAuthMethod,
-      actorOrgId
-    );
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Settings);
-
-    const externalKmsDoc = await externalKmsDAL.findOne({ kmsKeyId: kmsDoc.id });
-    if (!externalKmsDoc) throw new BadRequestError({ message: "External kms not found" });
-
-    const orgDefaultKmsId = await kmsService.getOrgKmsKeyId(kmsDoc.orgId);
-    const kmsDecryptor = await kmsService.decryptWithKmsKey({
-      kmsId: orgDefaultKmsId
-    });
-    const decryptedProviderInputBlob = kmsDecryptor({
-      cipherTextBlob: externalKmsDoc.encryptedProviderInputs
-    });
-
-    switch (externalKmsDoc.provider) {
-      case KmsProviders.Aws: {
-        const decryptedProviderInput = await ExternalKmsAwsSchema.parseAsync(
-          JSON.parse(decryptedProviderInputBlob.toString("utf8"))
-        );
-        return { ...kmsDoc, external: { ...externalKmsDoc, providerInput: decryptedProviderInput } };
-      }
-      default:
-        throw new BadRequestError({ message: "external kms provided is invalid" });
-    }
-  };
-
-  return {
-    create,
-    updateById,
-    deleteById,
-    list,
-    findById,
-    findBySlug
-  };
-};

backend/src/ee/services/external-kms/external-kms-types.ts

@@ -1,30 +0,0 @@
-import { TOrgPermission } from "@app/lib/types";
-
-import { TExternalKmsInputSchema, TExternalKmsInputUpdateSchema } from "./providers/model";
-
-export type TCreateExternalKmsDTO = {
-  slug?: string;
-  description?: string;
-  provider: TExternalKmsInputSchema;
-} & Omit<TOrgPermission, "orgId">;
-
-export type TUpdateExternalKmsDTO = {
-  id: string;
-  slug?: string;
-  description?: string;
-  provider?: TExternalKmsInputUpdateSchema;
-} & Omit<TOrgPermission, "orgId">;
-
-export type TDeleteExternalKmsDTO = {
-  id: string;
-} & Omit<TOrgPermission, "orgId">;
-
-export type TListExternalKmsDTO = Omit<TOrgPermission, "orgId">;
-
-export type TGetExternalKmsByIdDTO = {
-  id: string;
-} & Omit<TOrgPermission, "orgId">;
-
-export type TGetExternalKmsBySlugDTO = {
-  slug: string;
-} & Omit<TOrgPermission, "orgId">;

backend/src/ee/services/external-kms/providers/aws-kms.ts

@@ -1,102 +0,0 @@
-import { CreateKeyCommand, DecryptCommand, DescribeKeyCommand, EncryptCommand, KMSClient } from "@aws-sdk/client-kms";
-import { AssumeRoleCommand, STSClient } from "@aws-sdk/client-sts";
-import { randomUUID } from "crypto";
-
-import { ExternalKmsAwsSchema, KmsAwsCredentialType, TExternalKmsAwsSchema, TExternalKmsProviderFns } from "./model";
-
-const getAwsKmsClient = async (providerInputs: TExternalKmsAwsSchema) => {
-  if (providerInputs.credential.type === KmsAwsCredentialType.AssumeRole) {
-    const awsCredential = providerInputs.credential.data;
-    const stsClient = new STSClient({
-      region: providerInputs.awsRegion
-    });
-    const command = new AssumeRoleCommand({
-      RoleArn: awsCredential.assumeRoleArn,
-      RoleSessionName: `infisical-kms-${randomUUID()}`,
-      DurationSeconds: 900, // 15mins
-      ExternalId: awsCredential.externalId
-    });
-    const response = await stsClient.send(command);
-    if (!response.Credentials?.AccessKeyId || !response.Credentials?.SecretAccessKey)
-      throw new Error("Failed to assume role");
-
-    const kmsClient = new KMSClient({
-      region: providerInputs.awsRegion,
-      credentials: {
-        accessKeyId: response.Credentials.AccessKeyId,
-        secretAccessKey: response.Credentials.SecretAccessKey,
-        sessionToken: response.Credentials.SessionToken,
-        expiration: response.Credentials.Expiration
-      }
-    });
-    return kmsClient;
-  }
-  const awsCredential = providerInputs.credential.data;
-  const kmsClient = new KMSClient({
-    region: providerInputs.awsRegion,
-    credentials: {
-      accessKeyId: awsCredential.accessKey,
-      secretAccessKey: awsCredential.secretKey
-    }
-  });
-  return kmsClient;
-};
-
-type AwsKmsProviderArgs = {
-  inputs: unknown;
-};
-type TAwsKmsProviderFactoryReturn = TExternalKmsProviderFns & {
-  generateInputKmsKey: () => Promise<TExternalKmsAwsSchema>;
-};
-
-export const AwsKmsProviderFactory = async ({ inputs }: AwsKmsProviderArgs): Promise<TAwsKmsProviderFactoryReturn> => {
-  const providerInputs = await ExternalKmsAwsSchema.parseAsync(inputs);
-  const awsClient = await getAwsKmsClient(providerInputs);
-
-  const generateInputKmsKey = async () => {
-    if (providerInputs.kmsKeyId) return providerInputs;
-
-    const command = new CreateKeyCommand({ Tags: [{ TagKey: "author", TagValue: "infisical" }] });
-    const kmsKey = await awsClient.send(command);
-    if (!kmsKey.KeyMetadata?.KeyId) throw new Error("Failed to generate kms key");
-
-    return { ...providerInputs, kmsKeyId: kmsKey.KeyMetadata?.KeyId };
-  };
-
-  const validateConnection = async () => {
-    const command = new DescribeKeyCommand({
-      KeyId: providerInputs.kmsKeyId
-    });
-    const isConnected = await awsClient.send(command).then(() => true);
-    return isConnected;
-  };
-
-  const encrypt = async (data: Buffer) => {
-    const command = new EncryptCommand({
-      KeyId: providerInputs.kmsKeyId,
-      Plaintext: data
-    });
-    const encryptionCommand = await awsClient.send(command);
-    if (!encryptionCommand.CiphertextBlob) throw new Error("encryption failed");
-
-    return { encryptedBlob: Buffer.from(encryptionCommand.CiphertextBlob) };
-  };
-
-  const decrypt = async (encryptedBlob: Buffer) => {
-    const command = new DecryptCommand({
-      KeyId: providerInputs.kmsKeyId,
-      CiphertextBlob: encryptedBlob
-    });
-    const decryptionCommand = await awsClient.send(command);
-    if (!decryptionCommand.Plaintext) throw new Error("decryption failed");
-
-    return { data: Buffer.from(decryptionCommand.Plaintext) };
-  };
-
-  return {
-    generateInputKmsKey,
-    validateConnection,
-    encrypt,
-    decrypt
-  };
-};

backend/src/ee/services/external-kms/providers/model.ts

@@ -1,61 +0,0 @@
-import { z } from "zod";
-
-export enum KmsProviders {
-  Aws = "aws"
-}
-
-export enum KmsAwsCredentialType {
-  AssumeRole = "assume-role",
-  AccessKey = "access-key"
-}
-
-export const ExternalKmsAwsSchema = z.object({
-  credential: z
-    .discriminatedUnion("type", [
-      z.object({
-        type: z.literal(KmsAwsCredentialType.AccessKey),
-        data: z.object({
-          accessKey: z.string().trim().min(1).describe("AWS user account access key"),
-          secretKey: z.string().trim().min(1).describe("AWS user account secret key")
-        })
-      }),
-      z.object({
-        type: z.literal(KmsAwsCredentialType.AssumeRole),
-        data: z.object({
-          assumeRoleArn: z.string().trim().min(1).describe("AWS user role to be assumed by infisical"),
-          externalId: z
-            .string()
-            .trim()
-            .min(1)
-            .optional()
-            .describe("AWS assume role external id for furthur security in authentication")
-        })
-      })
-    ])
-    .describe("AWS credential information to connect"),
-  awsRegion: z.string().min(1).trim().describe("AWS region to connect"),
-  kmsKeyId: z
-    .string()
-    .trim()
-    .optional()
-    .describe("A pre existing AWS KMS key id to be used for encryption. If not provided a kms key will be generated.")
-});
-export type TExternalKmsAwsSchema = z.infer<typeof ExternalKmsAwsSchema>;
-
-// The root schema of the JSON
-export const ExternalKmsInputSchema = z.discriminatedUnion("type", [
-  z.object({ type: z.literal(KmsProviders.Aws), inputs: ExternalKmsAwsSchema })
-]);
-export type TExternalKmsInputSchema = z.infer<typeof ExternalKmsInputSchema>;
-
-export const ExternalKmsInputUpdateSchema = z.discriminatedUnion("type", [
-  z.object({ type: z.literal(KmsProviders.Aws), inputs: ExternalKmsAwsSchema.partial() })
-]);
-export type TExternalKmsInputUpdateSchema = z.infer<typeof ExternalKmsInputUpdateSchema>;
-
-// generic function shared by all provider
-export type TExternalKmsProviderFns = {
-  validateConnection: () => Promise<boolean>;
-  encrypt: (data: Buffer) => Promise<{ encryptedBlob: Buffer }>;
-  decrypt: (encryptedBlob: Buffer) => Promise<{ data: Buffer }>;
-};

backend/src/ee/services/group/group-dal.ts

@@ -12,7 +12,7 @@ export const groupDALFactory = (db: TDbClient) => {
 
   const findGroups = async (filter: TFindFilter<TGroups>, { offset, limit, sort, tx }: TFindOpt<TGroups> = {}) => {
     try {
-      const query = (tx || db.replicaNode())(TableName.Groups)
+      const query = (tx || db)(TableName.Groups)
         // eslint-disable-next-line
         .where(buildFindFilter(filter))
         .select(selectAllTableCols(TableName.Groups));
@@ -32,7 +32,7 @@ export const groupDALFactory = (db: TDbClient) => {
 
   const findByOrgId = async (orgId: string, tx?: Knex) => {
     try {
-      const docs = await (tx || db.replicaNode())(TableName.Groups)
+      const docs = await (tx || db)(TableName.Groups)
         .where(`${TableName.Groups}.orgId`, orgId)
         .leftJoin(TableName.OrgRoles, `${TableName.Groups}.roleId`, `${TableName.OrgRoles}.id`)
         .select(selectAllTableCols(TableName.Groups))
@@ -74,12 +74,11 @@ export const groupDALFactory = (db: TDbClient) => {
     username?: string;
   }) => {
     try {
-      let query = db
-        .replicaNode()(TableName.OrgMembership)
+      let query = db(TableName.OrgMembership)
         .where(`${TableName.OrgMembership}.orgId`, orgId)
         .join(TableName.Users, `${TableName.OrgMembership}.userId`, `${TableName.Users}.id`)
-        .leftJoin(TableName.UserGroupMembership, (bd) => {
-          bd.on(`${TableName.UserGroupMembership}.userId`, "=", `${TableName.Users}.id`).andOn(
+        .leftJoin(TableName.UserGroupMembership, function () {
+          this.on(`${TableName.UserGroupMembership}.userId`, "=", `${TableName.Users}.id`).andOn(
             `${TableName.UserGroupMembership}.groupId`,
             "=",
             db.raw("?", [groupId])

backend/src/ee/services/group/user-group-membership-dal.ts

@@ -18,7 +18,7 @@ export const userGroupMembershipDALFactory = (db: TDbClient) => {
    */
   const filterProjectsByUserMembership = async (userId: string, groupId: string, projectIds: string[], tx?: Knex) => {
     try {
-      const userProjectMemberships: string[] = await (tx || db.replicaNode())(TableName.ProjectMembership)
+      const userProjectMemberships: string[] = await (tx || db)(TableName.ProjectMembership)
         .where(`${TableName.ProjectMembership}.userId`, userId)
         .whereIn(`${TableName.ProjectMembership}.projectId`, projectIds)
         .pluck(`${TableName.ProjectMembership}.projectId`);
@@ -43,8 +43,7 @@ export const userGroupMembershipDALFactory = (db: TDbClient) => {
   // special query
   const findUserGroupMembershipsInProject = async (usernames: string[], projectId: string) => {
     try {
-      const usernameDocs: string[] = await db
-        .replicaNode()(TableName.UserGroupMembership)
+      const usernameDocs: string[] = await db(TableName.UserGroupMembership)
         .join(
           TableName.GroupProjectMembership,
           `${TableName.UserGroupMembership}.groupId`,
@@ -74,7 +73,7 @@ export const userGroupMembershipDALFactory = (db: TDbClient) => {
     try {
       // get list of groups in the project with id [projectId]
       // that that are not the group with id [groupId]
-      const groups: string[] = await (tx || db.replicaNode())(TableName.GroupProjectMembership)
+      const groups: string[] = await (tx || db)(TableName.GroupProjectMembership)
         .where(`${TableName.GroupProjectMembership}.projectId`, projectId)
         .whereNot(`${TableName.GroupProjectMembership}.groupId`, groupId)
         .pluck(`${TableName.GroupProjectMembership}.groupId`);
@@ -84,8 +83,8 @@ export const userGroupMembershipDALFactory = (db: TDbClient) => {
         .where(`${TableName.UserGroupMembership}.groupId`, groupId)
         .where(`${TableName.UserGroupMembership}.isPending`, false)
         .join(TableName.Users, `${TableName.UserGroupMembership}.userId`, `${TableName.Users}.id`)
-        .leftJoin(TableName.ProjectMembership, (bd) => {
-          bd.on(`${TableName.Users}.id`, "=", `${TableName.ProjectMembership}.userId`).andOn(
+        .leftJoin(TableName.ProjectMembership, function () {
+          this.on(`${TableName.Users}.id`, "=", `${TableName.ProjectMembership}.userId`).andOn(
             `${TableName.ProjectMembership}.projectId`,
             "=",
             db.raw("?", [projectId])
@@ -108,9 +107,9 @@ export const userGroupMembershipDALFactory = (db: TDbClient) => {
           db.ref("publicKey").withSchema(TableName.UserEncryptionKey)
         )
         .where({ isGhost: false }) // MAKE SURE USER IS NOT A GHOST USER
-        .whereNotIn(`${TableName.UserGroupMembership}.userId`, (bd) => {
+        .whereNotIn(`${TableName.UserGroupMembership}.userId`, function () {
           // eslint-disable-next-line @typescript-eslint/no-floating-promises
-          bd.select(`${TableName.UserGroupMembership}.userId`)
+          this.select(`${TableName.UserGroupMembership}.userId`)
             .from(TableName.UserGroupMembership)
             .whereIn(`${TableName.UserGroupMembership}.groupId`, groups);
         });

backend/src/ee/services/ldap-config/ldap-config-service.ts

@@ -23,8 +23,6 @@ import {
 } from "@app/lib/crypto/encryption";
 import { BadRequestError } from "@app/lib/errors";
 import { AuthMethod, AuthTokenType } from "@app/services/auth/auth-type";
-import { TAuthTokenServiceFactory } from "@app/services/auth-token/auth-token-service";
-import { TokenType } from "@app/services/auth-token/auth-token-types";
 import { TGroupProjectDALFactory } from "@app/services/group-project/group-project-dal";
 import { TOrgBotDALFactory } from "@app/services/org/org-bot-dal";
 import { TOrgDALFactory } from "@app/services/org/org-dal";
@@ -32,9 +30,7 @@ import { TOrgMembershipDALFactory } from "@app/services/org-membership/org-membe
 import { TProjectDALFactory } from "@app/services/project/project-dal";
 import { TProjectBotDALFactory } from "@app/services/project-bot/project-bot-dal";
 import { TProjectKeyDALFactory } from "@app/services/project-key/project-key-dal";
-import { SmtpTemplates, TSmtpService } from "@app/services/smtp/smtp-service";
 import { getServerCfg } from "@app/services/super-admin/super-admin-service";
-import { LoginMethod } from "@app/services/super-admin/super-admin-types";
 import { TUserDALFactory } from "@app/services/user/user-dal";
 import { normalizeUsername } from "@app/services/user/user-fns";
 import { TUserAliasDALFactory } from "@app/services/user-alias/user-alias-dal";
@@ -54,7 +50,7 @@ import {
   TTestLdapConnectionDTO,
   TUpdateLdapCfgDTO
 } from "./ldap-config-types";
-import { searchGroups, testLDAPConfig } from "./ldap-fns";
+import { testLDAPConfig } from "./ldap-fns";
 import { TLdapGroupMapDALFactory } from "./ldap-group-map-dal";
 
 type TLdapConfigServiceFactoryDep = {
@@ -77,19 +73,11 @@ type TLdapConfigServiceFactoryDep = {
   >;
   userDAL: Pick<
     TUserDALFactory,
-    | "create"
-    | "findOne"
-    | "transaction"
-    | "updateById"
-    | "findUserEncKeyByUserIdsBatch"
-    | "find"
-    | "findUserEncKeyByUserId"
+    "create" | "findOne" | "transaction" | "updateById" | "findUserEncKeyByUserIdsBatch" | "find"
   >;
   userAliasDAL: Pick<TUserAliasDALFactory, "create" | "findOne">;
   permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
   licenseService: Pick<TLicenseServiceFactory, "getPlan" | "updateSubscriptionOrgMemberCount">;
-  tokenService: Pick<TAuthTokenServiceFactory, "createTokenForUser">;
-  smtpService: Pick<TSmtpService, "sendMail">;
 };
 
 export type TLdapConfigServiceFactory = ReturnType<typeof ldapConfigServiceFactory>;
@@ -109,9 +97,7 @@ export const ldapConfigServiceFactory = ({
   userDAL,
   userAliasDAL,
   permissionService,
-  licenseService,
-  tokenService,
-  smtpService
+  licenseService
 }: TLdapConfigServiceFactoryDep) => {
   const createLdapCfg = async ({
     actor,
@@ -123,7 +109,6 @@ export const ldapConfigServiceFactory = ({
     url,
     bindDN,
     bindPass,
-    uniqueUserAttribute,
     searchBase,
     searchFilter,
     groupSearchBase,
@@ -202,7 +187,6 @@ export const ldapConfigServiceFactory = ({
       encryptedBindPass,
       bindPassIV,
       bindPassTag,
-      uniqueUserAttribute,
       searchBase,
       searchFilter,
       groupSearchBase,
@@ -225,7 +209,6 @@ export const ldapConfigServiceFactory = ({
     url,
     bindDN,
     bindPass,
-    uniqueUserAttribute,
     searchBase,
     searchFilter,
     groupSearchBase,
@@ -248,8 +231,7 @@ export const ldapConfigServiceFactory = ({
       searchBase,
       searchFilter,
       groupSearchBase,
-      groupSearchFilter,
-      uniqueUserAttribute
+      groupSearchFilter
     };
 
     const orgBot = await orgBotDAL.findOne({ orgId });
@@ -287,7 +269,7 @@ export const ldapConfigServiceFactory = ({
     return ldapConfig;
   };
 
-  const getLdapCfg = async (filter: { orgId: string; isActive?: boolean; id?: string }) => {
+  const getLdapCfg = async (filter: { orgId: string; isActive?: boolean }) => {
     const ldapConfig = await ldapConfigDAL.findOne(filter);
     if (!ldapConfig) throw new BadRequestError({ message: "Failed to find organization LDAP data" });
 
@@ -350,7 +332,6 @@ export const ldapConfigServiceFactory = ({
       url: ldapConfig.url,
       bindDN,
       bindPass,
-      uniqueUserAttribute: ldapConfig.uniqueUserAttribute,
       searchBase: ldapConfig.searchBase,
       searchFilter: ldapConfig.searchFilter,
       groupSearchBase: ldapConfig.groupSearchBase,
@@ -387,7 +368,6 @@ export const ldapConfigServiceFactory = ({
         url: ldapConfig.url,
         bindDN: ldapConfig.bindDN,
         bindCredentials: ldapConfig.bindPass,
-        uniqueUserAttribute: ldapConfig.uniqueUserAttribute,
         searchBase: ldapConfig.searchBase,
         searchFilter: ldapConfig.searchFilter || "(uid={{username}})",
         // searchAttributes: ["uid", "uidNumber", "givenName", "sn", "mail"],
@@ -418,13 +398,6 @@ export const ldapConfigServiceFactory = ({
   }: TLdapLoginDTO) => {
     const appCfg = getConfig();
     const serverCfg = await getServerCfg();
-
-    if (serverCfg.enabledLoginMethods && !serverCfg.enabledLoginMethods.includes(LoginMethod.LDAP)) {
-      throw new BadRequestError({
-        message: "Login with LDAP is disabled by administrator."
-      });
-    }
-
     let userAlias = await userAliasDAL.findOne({
       externalId,
       orgId,
@@ -464,24 +437,9 @@ export const ldapConfigServiceFactory = ({
         }
       });
     } else {
-      const plan = await licenseService.getPlan(orgId);
-      if (plan?.memberLimit && plan.membersUsed >= plan.memberLimit) {
-        // limit imposed on number of members allowed / number of members used exceeds the number of members allowed
-        throw new BadRequestError({
-          message: "Failed to create new member via LDAP due to member limit reached. Upgrade plan to add more members."
-        });
-      }
-
-      if (plan?.identityLimit && plan.identitiesUsed >= plan.identityLimit) {
-        // limit imposed on number of identities allowed / number of identities used exceeds the number of identities allowed
-        throw new BadRequestError({
-          message: "Failed to create new member via LDAP due to member limit reached. Upgrade plan to add more members."
-        });
-      }
-
       userAlias = await userDAL.transaction(async (tx) => {
         let newUser: TUsers | undefined;
-        if (serverCfg.trustLdapEmails) {
+        if (serverCfg.trustSamlEmails) {
           newUser = await userDAL.findOne(
             {
               email,
@@ -530,7 +488,7 @@ export const ldapConfigServiceFactory = ({
         if (!orgMembership) {
           await orgMembershipDAL.create(
             {
-              userId: newUser.id,
+              userId: userAlias.userId,
               inviteEmail: email,
               orgId,
               role: OrgMembershipRole.Member,
@@ -634,14 +592,12 @@ export const ldapConfigServiceFactory = ({
     });
 
     const isUserCompleted = Boolean(user.isAccepted);
-    const userEnc = await userDAL.findUserEncKeyByUserId(user.id);
 
     const providerAuthToken = jwt.sign(
       {
         authTokenType: AuthTokenType.PROVIDER_TOKEN,
         userId: user.id,
         username: user.username,
-        hasExchangedPrivateKey: Boolean(userEnc?.serverEncryptedPrivateKey),
         ...(user.email && { email: user.email, isEmailVerified: user.isEmailVerified }),
         firstName,
         lastName,
@@ -663,22 +619,6 @@ export const ldapConfigServiceFactory = ({
       }
     );
 
-    if (user.email && !user.isEmailVerified) {
-      const token = await tokenService.createTokenForUser({
-        type: TokenType.TOKEN_EMAIL_VERIFICATION,
-        userId: user.id
-      });
-
-      await smtpService.sendMail({
-        template: SmtpTemplates.EmailVerification,
-        subjectLine: "Infisical confirmation code",
-        recipients: [user.email],
-        substitutions: {
-          code: token
-        }
-      });
-    }
-
     return { isUserCompleted, providerAuthToken };
   };
 
@@ -724,25 +664,11 @@ export const ldapConfigServiceFactory = ({
         message: "Failed to create LDAP group map due to plan restriction. Upgrade plan to create LDAP group map."
       });
 
-    const ldapConfig = await getLdapCfg({
-      orgId,
-      id: ldapConfigId
+    const ldapConfig = await ldapConfigDAL.findOne({
+      id: ldapConfigId,
+      orgId
     });
-
-    if (!ldapConfig.groupSearchBase) {
-      throw new BadRequestError({
-        message: "Configure a group search base in your LDAP configuration in order to proceed."
-      });
-    }
-
-    const groupSearchFilter = `(cn=${ldapGroupCN})`;
-    const groups = await searchGroups(ldapConfig, groupSearchFilter, ldapConfig.groupSearchBase);
-
-    if (!groups.some((g) => g.cn === ldapGroupCN)) {
-      throw new BadRequestError({
-        message: "Failed to find LDAP Group CN"
-      });
-    }
+    if (!ldapConfig) throw new BadRequestError({ message: "Failed to find organization LDAP data" });
 
     const group = await groupDAL.findOne({ slug: groupSlug, orgId });
     if (!group) throw new BadRequestError({ message: "Failed to find group" });

backend/src/ee/services/ldap-config/ldap-config-types.ts

@@ -7,7 +7,6 @@ export type TLDAPConfig = {
   url: string;
   bindDN: string;
   bindPass: string;
-  uniqueUserAttribute: string;
   searchBase: string;
   groupSearchBase: string;
   groupSearchFilter: string;
@@ -20,7 +19,6 @@ export type TCreateLdapCfgDTO = {
   url: string;
   bindDN: string;
   bindPass: string;
-  uniqueUserAttribute: string;
   searchBase: string;
   searchFilter: string;
   groupSearchBase: string;
@@ -35,7 +33,6 @@ export type TUpdateLdapCfgDTO = {
   url: string;
   bindDN: string;
   bindPass: string;
-  uniqueUserAttribute: string;
   searchBase: string;
   searchFilter: string;
   groupSearchBase: string;

backend/src/ee/services/ldap-config/ldap-group-map-dal.ts

@@ -10,8 +10,7 @@ export const ldapGroupMapDALFactory = (db: TDbClient) => {
 
   const findLdapGroupMapsByLdapConfigId = async (ldapConfigId: string) => {
     try {
-      const docs = await db
-        .replicaNode()(TableName.LdapGroupMap)
+      const docs = await db(TableName.LdapGroupMap)
         .where(`${TableName.LdapGroupMap}.ldapConfigId`, ldapConfigId)
         .join(TableName.Groups, `${TableName.LdapGroupMap}.groupId`, `${TableName.Groups}.id`)
         .select(selectAllTableCols(TableName.LdapGroupMap))

backend/src/ee/services/license/__mocks__/licence-fns.ts

@@ -7,8 +7,6 @@ export const getDefaultOnPremFeatures = () => {
     workspacesUsed: 0,
     memberLimit: null,
     membersUsed: 0,
-    identityLimit: null,
-    identitiesUsed: 0,
     environmentLimit: null,
     environmentsUsed: 0,
     secretVersioning: true,

backend/src/ee/services/license/licence-fns.ts

@@ -15,8 +15,6 @@ export const getDefaultOnPremFeatures = (): TFeatureSet => ({
   membersUsed: 0,
   environmentLimit: null,
   environmentsUsed: 0,
-  identityLimit: null,
-  identitiesUsed: 0,
   dynamicSecret: false,
   secretVersioning: true,
   pitRecovery: false,
@@ -29,7 +27,6 @@ export const getDefaultOnPremFeatures = (): TFeatureSet => ({
   auditLogStreams: false,
   auditLogStreamLimit: 3,
   samlSSO: false,
-  oidcSSO: false,
   scim: false,
   ldap: false,
   groups: false,
@@ -38,8 +35,7 @@ export const getDefaultOnPremFeatures = (): TFeatureSet => ({
   has_used_trial: true,
   secretApproval: false,
   secretRotation: true,
-  caCrl: false,
-  instanceUserManagement: false
+  caCrl: false
 });
 
 export const setupLicenceRequestWithStore = (baseURL: string, refreshUrl: string, licenseKey: string) => {

backend/src/ee/services/license/license-dal.ts

@@ -7,9 +7,9 @@ import { DatabaseError } from "@app/lib/errors";
 export type TLicenseDALFactory = ReturnType<typeof licenseDALFactory>;
 
 export const licenseDALFactory = (db: TDbClient) => {
-  const countOfOrgMembers = async (orgId: string | null, tx?: Knex) => {
+  const countOfOrgIdentities = async (orgId: string | null, orgBillingVersion?: string | null, tx?: Knex) => {
     try {
-      const doc = await (tx || db.replicaNode())(TableName.OrgMembership)
+      const userIdentities = await (tx || db)(TableName.OrgMembership)
         .where({ status: OrgMembershipStatus.Accepted })
         .andWhere((bd) => {
           if (orgId) {
@@ -19,44 +19,34 @@ export const licenseDALFactory = (db: TDbClient) => {
         .join(TableName.Users, `${TableName.OrgMembership}.userId`, `${TableName.Users}.id`)
         .where(`${TableName.Users}.isGhost`, false)
         .count();
-      return Number(doc?.[0].count);
+
+      const userIdentitiesCount =
+        typeof userIdentities?.[0].count === "string"
+          ? parseInt(userIdentities?.[0].count, 10)
+          : userIdentities?.[0].count;
+      if (orgBillingVersion === "v0") {
+        return userIdentitiesCount;
+      }
+
+      const machineIdentities = await (tx || db)(TableName.IdentityOrgMembership)
+        .where((bd) => {
+          if (orgId) {
+            void bd.where(`${TableName.IdentityOrgMembership}.orgId`, orgId);
+          }
+        })
+        .join(TableName.Identity, `${TableName.IdentityOrgMembership}.identityId`, `${TableName.Identity}.id`)
+        .count();
+
+      const machineIdentitiesCount =
+        typeof machineIdentities?.[0].count === "string"
+          ? parseInt(machineIdentities?.[0].count, 10)
+          : machineIdentities?.[0].count;
+
+      return userIdentitiesCount + machineIdentitiesCount;
     } catch (error) {
       throw new DatabaseError({ error, name: "Count of Org Members" });
     }
   };
 
-  const countOrgUsersAndIdentities = async (orgId: string | null, tx?: Knex) => {
-    try {
-      // count org users
-      const userDoc = await (tx || db)(TableName.OrgMembership)
-        .where({ status: OrgMembershipStatus.Accepted })
-        .andWhere((bd) => {
-          if (orgId) {
-            void bd.where({ orgId });
-          }
-        })
-        .join(TableName.Users, `${TableName.OrgMembership}.userId`, `${TableName.Users}.id`)
-        .where(`${TableName.Users}.isGhost`, false)
-        .count();
-
-      const userCount = Number(userDoc?.[0].count);
-
-      // count org identities
-      const identityDoc = await (tx || db)(TableName.IdentityOrgMembership)
-        .where((bd) => {
-          if (orgId) {
-            void bd.where({ orgId });
-          }
-        })
-        .count();
-
-      const identityCount = Number(identityDoc?.[0].count);
-
-      return userCount + identityCount;
-    } catch (error) {
-      throw new DatabaseError({ error, name: "Count of Org Users + Identities" });
-    }
-  };
-
-  return { countOfOrgMembers, countOrgUsersAndIdentities };
+  return { countOfOrgIdentities };
 };

backend/src/ee/services/license/license-service.ts

@@ -5,7 +5,6 @@
 // TODO(akhilmhdh): With tony find out the api structure and fill it here
 
 import { ForbiddenError } from "@casl/ability";
-import { Knex } from "knex";
 
 import { TKeyStoreFactory } from "@app/keystore/keystore";
 import { getConfig } from "@app/lib/config/env";
@@ -156,7 +155,6 @@ export const licenseServiceFactory = ({
           LICENSE_SERVER_CLOUD_PLAN_TTL,
           JSON.stringify(currentPlan)
         );
-
         return currentPlan;
       }
     } catch (error) {
@@ -201,29 +199,21 @@ export const licenseServiceFactory = ({
     await licenseServerCloudApi.request.delete(`/api/license-server/v1/customers/${customerId}`);
   };
 
-  const updateSubscriptionOrgMemberCount = async (orgId: string, tx?: Knex) => {
+  const updateSubscriptionOrgIdentitiesCount = async (orgId: string): Promise<void> => {
     if (instanceType === InstanceType.Cloud) {
       const org = await orgDAL.findOrgById(orgId);
       if (!org) throw new BadRequestError({ message: "Org not found" });
 
-      const quantity = await licenseDAL.countOfOrgMembers(orgId, tx);
-      const quantityIdentities = await licenseDAL.countOrgUsersAndIdentities(orgId, tx);
+      const count = await licenseDAL.countOfOrgIdentities(orgId, org.billingVersion);
       if (org?.customerId) {
         await licenseServerCloudApi.request.patch(`/api/license-server/v1/customers/${org.customerId}/cloud-plan`, {
-          quantity,
-          quantityIdentities
+          quantity: count
         });
       }
       await keyStore.deleteItem(FEATURE_CACHE_KEY(orgId));
     } else if (instanceType === InstanceType.EnterpriseOnPrem) {
-      const usedSeats = await licenseDAL.countOfOrgMembers(null, tx);
-      const usedIdentitySeats = await licenseDAL.countOrgUsersAndIdentities(null, tx);
-      onPremFeatures.membersUsed = usedSeats;
-      onPremFeatures.identitiesUsed = usedIdentitySeats;
-      await licenseServerOnPremApi.request.patch(`/api/license/v1/license`, {
-        usedSeats,
-        usedIdentitySeats
-      });
+      const usedSeats = await licenseDAL.countOfOrgIdentities(null, null);
+      await licenseServerOnPremApi.request.patch(`/api/license/v1/license`, { usedSeats });
     }
     await refreshPlan(orgId);
   };
@@ -589,7 +579,7 @@ export const licenseServiceFactory = ({
       return onPremFeatures;
     },
     getPlan,
-    updateSubscriptionOrgMemberCount,
+    updateSubscriptionOrgIdentitiesCount,
     refreshPlan,
     getOrgPlan,
     getOrgPlansTableByBillCycle,

backend/src/ee/services/license/license-types.ts

@@ -30,9 +30,7 @@ export type TFeatureSet = {
   workspacesUsed: 0;
   dynamicSecret: false;
   memberLimit: null;
-  membersUsed: number;
-  identityLimit: null;
-  identitiesUsed: number;
+  membersUsed: 0;
   environmentLimit: null;
   environmentsUsed: 0;
   secretVersioning: true;
@@ -46,7 +44,6 @@ export type TFeatureSet = {
   auditLogStreams: false;
   auditLogStreamLimit: 3;
   samlSSO: false;
-  oidcSSO: false;
   scim: false;
   ldap: false;
   groups: false;
@@ -56,7 +53,6 @@ export type TFeatureSet = {
   secretApproval: false;
   secretRotation: true;
   caCrl: false;
-  instanceUserManagement: false;
 };
 
 export type TOrgPlansTableDTO = {

backend/src/ee/services/oidc/oidc-config-dal.ts

@@ -1,11 +0,0 @@
-import { TDbClient } from "@app/db";
-import { TableName } from "@app/db/schemas";
-import { ormify } from "@app/lib/knex";
-
-export type TOidcConfigDALFactory = ReturnType<typeof oidcConfigDALFactory>;
-
-export const oidcConfigDALFactory = (db: TDbClient) => {
-  const oidcCfgOrm = ormify(db, TableName.OidcConfig);
-
-  return { ...oidcCfgOrm };
-};

backend/src/ee/services/oidc/oidc-config-service.ts

@@ -1,645 +0,0 @@
-/* eslint-disable @typescript-eslint/no-unsafe-call */
-import { ForbiddenError } from "@casl/ability";
-import jwt from "jsonwebtoken";
-import { Issuer, Issuer as OpenIdIssuer, Strategy as OpenIdStrategy, TokenSet } from "openid-client";
-
-import { OrgMembershipRole, OrgMembershipStatus, SecretKeyEncoding, TableName, TUsers } from "@app/db/schemas";
-import { TOidcConfigsUpdate } from "@app/db/schemas/oidc-configs";
-import { TLicenseServiceFactory } from "@app/ee/services/license/license-service";
-import { OrgPermissionActions, OrgPermissionSubjects } from "@app/ee/services/permission/org-permission";
-import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
-import { getConfig } from "@app/lib/config/env";
-import {
-  decryptSymmetric,
-  encryptSymmetric,
-  generateAsymmetricKeyPair,
-  generateSymmetricKey,
-  infisicalSymmetricDecrypt,
-  infisicalSymmetricEncypt
-} from "@app/lib/crypto/encryption";
-import { BadRequestError } from "@app/lib/errors";
-import { AuthMethod, AuthTokenType } from "@app/services/auth/auth-type";
-import { TAuthTokenServiceFactory } from "@app/services/auth-token/auth-token-service";
-import { TokenType } from "@app/services/auth-token/auth-token-types";
-import { TOrgBotDALFactory } from "@app/services/org/org-bot-dal";
-import { TOrgDALFactory } from "@app/services/org/org-dal";
-import { TOrgMembershipDALFactory } from "@app/services/org-membership/org-membership-dal";
-import { SmtpTemplates, TSmtpService } from "@app/services/smtp/smtp-service";
-import { getServerCfg } from "@app/services/super-admin/super-admin-service";
-import { LoginMethod } from "@app/services/super-admin/super-admin-types";
-import { TUserDALFactory } from "@app/services/user/user-dal";
-import { normalizeUsername } from "@app/services/user/user-fns";
-import { TUserAliasDALFactory } from "@app/services/user-alias/user-alias-dal";
-import { UserAliasType } from "@app/services/user-alias/user-alias-types";
-
-import { TOidcConfigDALFactory } from "./oidc-config-dal";
-import {
-  OIDCConfigurationType,
-  TCreateOidcCfgDTO,
-  TGetOidcCfgDTO,
-  TOidcLoginDTO,
-  TUpdateOidcCfgDTO
-} from "./oidc-config-types";
-
-type TOidcConfigServiceFactoryDep = {
-  userDAL: Pick<
-    TUserDALFactory,
-    "create" | "findOne" | "transaction" | "updateById" | "findById" | "findUserEncKeyByUserId"
-  >;
-  userAliasDAL: Pick<TUserAliasDALFactory, "create" | "findOne">;
-  orgDAL: Pick<
-    TOrgDALFactory,
-    "createMembership" | "updateMembershipById" | "findMembership" | "findOrgById" | "findOne" | "updateById"
-  >;
-  orgMembershipDAL: Pick<TOrgMembershipDALFactory, "create">;
-  orgBotDAL: Pick<TOrgBotDALFactory, "findOne" | "create" | "transaction">;
-  licenseService: Pick<TLicenseServiceFactory, "getPlan" | "updateSubscriptionOrgMemberCount">;
-  tokenService: Pick<TAuthTokenServiceFactory, "createTokenForUser">;
-  smtpService: Pick<TSmtpService, "sendMail">;
-  permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
-  oidcConfigDAL: Pick<TOidcConfigDALFactory, "findOne" | "update" | "create">;
-};
-
-export type TOidcConfigServiceFactory = ReturnType<typeof oidcConfigServiceFactory>;
-
-export const oidcConfigServiceFactory = ({
-  orgDAL,
-  orgMembershipDAL,
-  userDAL,
-  userAliasDAL,
-  licenseService,
-  permissionService,
-  tokenService,
-  orgBotDAL,
-  smtpService,
-  oidcConfigDAL
-}: TOidcConfigServiceFactoryDep) => {
-  const getOidc = async (dto: TGetOidcCfgDTO) => {
-    const org = await orgDAL.findOne({ slug: dto.orgSlug });
-    if (!org) {
-      throw new BadRequestError({
-        message: "Organization not found",
-        name: "OrgNotFound"
-      });
-    }
-    if (dto.type === "external") {
-      const { permission } = await permissionService.getOrgPermission(
-        dto.actor,
-        dto.actorId,
-        org.id,
-        dto.actorAuthMethod,
-        dto.actorOrgId
-      );
-      ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Read, OrgPermissionSubjects.Sso);
-    }
-
-    const oidcCfg = await oidcConfigDAL.findOne({
-      orgId: org.id
-    });
-
-    if (!oidcCfg) {
-      throw new BadRequestError({
-        message: "Failed to find organization OIDC configuration"
-      });
-    }
-
-    // decrypt and return cfg
-    const orgBot = await orgBotDAL.findOne({ orgId: oidcCfg.orgId });
-    if (!orgBot) {
-      throw new BadRequestError({ message: "Org bot not found", name: "OrgBotNotFound" });
-    }
-
-    const key = infisicalSymmetricDecrypt({
-      ciphertext: orgBot.encryptedSymmetricKey,
-      iv: orgBot.symmetricKeyIV,
-      tag: orgBot.symmetricKeyTag,
-      keyEncoding: orgBot.symmetricKeyKeyEncoding as SecretKeyEncoding
-    });
-
-    const { encryptedClientId, clientIdIV, clientIdTag, encryptedClientSecret, clientSecretIV, clientSecretTag } =
-      oidcCfg;
-
-    let clientId = "";
-    if (encryptedClientId && clientIdIV && clientIdTag) {
-      clientId = decryptSymmetric({
-        ciphertext: encryptedClientId,
-        key,
-        tag: clientIdTag,
-        iv: clientIdIV
-      });
-    }
-
-    let clientSecret = "";
-    if (encryptedClientSecret && clientSecretIV && clientSecretTag) {
-      clientSecret = decryptSymmetric({
-        key,
-        tag: clientSecretTag,
-        iv: clientSecretIV,
-        ciphertext: encryptedClientSecret
-      });
-    }
-
-    return {
-      id: oidcCfg.id,
-      issuer: oidcCfg.issuer,
-      authorizationEndpoint: oidcCfg.authorizationEndpoint,
-      configurationType: oidcCfg.configurationType,
-      discoveryURL: oidcCfg.discoveryURL,
-      jwksUri: oidcCfg.jwksUri,
-      tokenEndpoint: oidcCfg.tokenEndpoint,
-      userinfoEndpoint: oidcCfg.userinfoEndpoint,
-      orgId: oidcCfg.orgId,
-      isActive: oidcCfg.isActive,
-      allowedEmailDomains: oidcCfg.allowedEmailDomains,
-      clientId,
-      clientSecret
-    };
-  };
-
-  const oidcLogin = async ({ externalId, email, firstName, lastName, orgId, callbackPort }: TOidcLoginDTO) => {
-    const serverCfg = await getServerCfg();
-
-    if (serverCfg.enabledLoginMethods && !serverCfg.enabledLoginMethods.includes(LoginMethod.OIDC)) {
-      throw new BadRequestError({
-        message: "Login with OIDC is disabled by administrator."
-      });
-    }
-
-    const appCfg = getConfig();
-    const userAlias = await userAliasDAL.findOne({
-      externalId,
-      orgId,
-      aliasType: UserAliasType.OIDC
-    });
-
-    const organization = await orgDAL.findOrgById(orgId);
-    if (!organization) throw new BadRequestError({ message: "Org not found" });
-
-    let user: TUsers;
-    if (userAlias) {
-      user = await userDAL.transaction(async (tx) => {
-        const foundUser = await userDAL.findById(userAlias.userId, tx);
-        const [orgMembership] = await orgDAL.findMembership(
-          {
-            [`${TableName.OrgMembership}.userId` as "userId"]: foundUser.id,
-            [`${TableName.OrgMembership}.orgId` as "id"]: orgId
-          },
-          { tx }
-        );
-        if (!orgMembership) {
-          await orgMembershipDAL.create(
-            {
-              userId: userAlias.userId,
-              inviteEmail: email,
-              orgId,
-              role: OrgMembershipRole.Member,
-              status: foundUser.isAccepted ? OrgMembershipStatus.Accepted : OrgMembershipStatus.Invited // if user is fully completed, then set status to accepted, otherwise set it to invited so we can update it later
-            },
-            tx
-          );
-          // Only update the membership to Accepted if the user account is already completed.
-        } else if (orgMembership.status === OrgMembershipStatus.Invited && foundUser.isAccepted) {
-          await orgDAL.updateMembershipById(
-            orgMembership.id,
-            {
-              status: OrgMembershipStatus.Accepted
-            },
-            tx
-          );
-        }
-
-        return foundUser;
-      });
-    } else {
-      user = await userDAL.transaction(async (tx) => {
-        let newUser: TUsers | undefined;
-
-        if (serverCfg.trustOidcEmails) {
-          newUser = await userDAL.findOne(
-            {
-              email,
-              isEmailVerified: true
-            },
-            tx
-          );
-        }
-
-        if (!newUser) {
-          const uniqueUsername = await normalizeUsername(externalId, userDAL);
-          newUser = await userDAL.create(
-            {
-              email,
-              firstName,
-              isEmailVerified: serverCfg.trustOidcEmails,
-              username: serverCfg.trustOidcEmails ? email : uniqueUsername,
-              lastName,
-              authMethods: [],
-              isGhost: false
-            },
-            tx
-          );
-        }
-
-        await userAliasDAL.create(
-          {
-            userId: newUser.id,
-            aliasType: UserAliasType.OIDC,
-            externalId,
-            emails: email ? [email] : [],
-            orgId
-          },
-          tx
-        );
-
-        const [orgMembership] = await orgDAL.findMembership(
-          {
-            [`${TableName.OrgMembership}.userId` as "userId"]: newUser.id,
-            [`${TableName.OrgMembership}.orgId` as "id"]: orgId
-          },
-          { tx }
-        );
-
-        if (!orgMembership) {
-          await orgMembershipDAL.create(
-            {
-              userId: newUser.id,
-              inviteEmail: email,
-              orgId,
-              role: OrgMembershipRole.Member,
-              status: newUser.isAccepted ? OrgMembershipStatus.Accepted : OrgMembershipStatus.Invited // if user is fully completed, then set status to accepted, otherwise set it to invited so we can update it later
-            },
-            tx
-          );
-          // Only update the membership to Accepted if the user account is already completed.
-        } else if (orgMembership.status === OrgMembershipStatus.Invited && newUser.isAccepted) {
-          await orgDAL.updateMembershipById(
-            orgMembership.id,
-            {
-              status: OrgMembershipStatus.Accepted
-            },
-            tx
-          );
-        }
-
-        return newUser;
-      });
-    }
-
-    await licenseService.updateSubscriptionOrgMemberCount(organization.id);
-
-    const userEnc = await userDAL.findUserEncKeyByUserId(user.id);
-    const isUserCompleted = Boolean(user.isAccepted);
-    const providerAuthToken = jwt.sign(
-      {
-        authTokenType: AuthTokenType.PROVIDER_TOKEN,
-        userId: user.id,
-        username: user.username,
-        ...(user.email && { email: user.email, isEmailVerified: user.isEmailVerified }),
-        firstName,
-        lastName,
-        organizationName: organization.name,
-        organizationId: organization.id,
-        organizationSlug: organization.slug,
-        hasExchangedPrivateKey: Boolean(userEnc?.serverEncryptedPrivateKey),
-        authMethod: AuthMethod.OIDC,
-        authType: UserAliasType.OIDC,
-        isUserCompleted,
-        ...(callbackPort && { callbackPort })
-      },
-      appCfg.AUTH_SECRET,
-      {
-        expiresIn: appCfg.JWT_PROVIDER_AUTH_LIFETIME
-      }
-    );
-
-    if (user.email && !user.isEmailVerified) {
-      const token = await tokenService.createTokenForUser({
-        type: TokenType.TOKEN_EMAIL_VERIFICATION,
-        userId: user.id
-      });
-
-      await smtpService.sendMail({
-        template: SmtpTemplates.EmailVerification,
-        subjectLine: "Infisical confirmation code",
-        recipients: [user.email],
-        substitutions: {
-          code: token
-        }
-      });
-    }
-
-    return { isUserCompleted, providerAuthToken };
-  };
-
-  const updateOidcCfg = async ({
-    orgSlug,
-    allowedEmailDomains,
-    configurationType,
-    discoveryURL,
-    actor,
-    actorOrgId,
-    actorAuthMethod,
-    actorId,
-    issuer,
-    isActive,
-    authorizationEndpoint,
-    jwksUri,
-    tokenEndpoint,
-    userinfoEndpoint,
-    clientId,
-    clientSecret
-  }: TUpdateOidcCfgDTO) => {
-    const org = await orgDAL.findOne({
-      slug: orgSlug
-    });
-
-    if (!org) {
-      throw new BadRequestError({
-        message: "Organization not found"
-      });
-    }
-
-    const plan = await licenseService.getPlan(org.id);
-    if (!plan.oidcSSO)
-      throw new BadRequestError({
-        message:
-          "Failed to update OIDC SSO configuration due to plan restriction. Upgrade plan to update SSO configuration."
-      });
-
-    const { permission } = await permissionService.getOrgPermission(
-      actor,
-      actorId,
-      org.id,
-      actorAuthMethod,
-      actorOrgId
-    );
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Edit, OrgPermissionSubjects.Sso);
-
-    const orgBot = await orgBotDAL.findOne({ orgId: org.id });
-    if (!orgBot) throw new BadRequestError({ message: "Org bot not found", name: "OrgBotNotFound" });
-    const key = infisicalSymmetricDecrypt({
-      ciphertext: orgBot.encryptedSymmetricKey,
-      iv: orgBot.symmetricKeyIV,
-      tag: orgBot.symmetricKeyTag,
-      keyEncoding: orgBot.symmetricKeyKeyEncoding as SecretKeyEncoding
-    });
-
-    const updateQuery: TOidcConfigsUpdate = {
-      allowedEmailDomains,
-      configurationType,
-      discoveryURL,
-      issuer,
-      authorizationEndpoint,
-      tokenEndpoint,
-      userinfoEndpoint,
-      jwksUri,
-      isActive
-    };
-
-    if (clientId !== undefined) {
-      const { ciphertext: encryptedClientId, iv: clientIdIV, tag: clientIdTag } = encryptSymmetric(clientId, key);
-      updateQuery.encryptedClientId = encryptedClientId;
-      updateQuery.clientIdIV = clientIdIV;
-      updateQuery.clientIdTag = clientIdTag;
-    }
-
-    if (clientSecret !== undefined) {
-      const {
-        ciphertext: encryptedClientSecret,
-        iv: clientSecretIV,
-        tag: clientSecretTag
-      } = encryptSymmetric(clientSecret, key);
-
-      updateQuery.encryptedClientSecret = encryptedClientSecret;
-      updateQuery.clientSecretIV = clientSecretIV;
-      updateQuery.clientSecretTag = clientSecretTag;
-    }
-
-    const [ssoConfig] = await oidcConfigDAL.update({ orgId: org.id }, updateQuery);
-    return ssoConfig;
-  };
-
-  const createOidcCfg = async ({
-    orgSlug,
-    allowedEmailDomains,
-    configurationType,
-    discoveryURL,
-    actor,
-    actorOrgId,
-    actorAuthMethod,
-    actorId,
-    issuer,
-    isActive,
-    authorizationEndpoint,
-    jwksUri,
-    tokenEndpoint,
-    userinfoEndpoint,
-    clientId,
-    clientSecret
-  }: TCreateOidcCfgDTO) => {
-    const org = await orgDAL.findOne({
-      slug: orgSlug
-    });
-    if (!org) {
-      throw new BadRequestError({
-        message: "Organization not found"
-      });
-    }
-
-    const plan = await licenseService.getPlan(org.id);
-    if (!plan.oidcSSO)
-      throw new BadRequestError({
-        message:
-          "Failed to create OIDC SSO configuration due to plan restriction. Upgrade plan to update SSO configuration."
-      });
-
-    const { permission } = await permissionService.getOrgPermission(
-      actor,
-      actorId,
-      org.id,
-      actorAuthMethod,
-      actorOrgId
-    );
-    ForbiddenError.from(permission).throwUnlessCan(OrgPermissionActions.Create, OrgPermissionSubjects.Sso);
-
-    const orgBot = await orgBotDAL.transaction(async (tx) => {
-      const doc = await orgBotDAL.findOne({ orgId: org.id }, tx);
-      if (doc) return doc;
-
-      const { privateKey, publicKey } = generateAsymmetricKeyPair();
-      const key = generateSymmetricKey();
-      const {
-        ciphertext: encryptedPrivateKey,
-        iv: privateKeyIV,
-        tag: privateKeyTag,
-        encoding: privateKeyKeyEncoding,
-        algorithm: privateKeyAlgorithm
-      } = infisicalSymmetricEncypt(privateKey);
-      const {
-        ciphertext: encryptedSymmetricKey,
-        iv: symmetricKeyIV,
-        tag: symmetricKeyTag,
-        encoding: symmetricKeyKeyEncoding,
-        algorithm: symmetricKeyAlgorithm
-      } = infisicalSymmetricEncypt(key);
-
-      return orgBotDAL.create(
-        {
-          name: "Infisical org bot",
-          publicKey,
-          privateKeyIV,
-          encryptedPrivateKey,
-          symmetricKeyIV,
-          symmetricKeyTag,
-          encryptedSymmetricKey,
-          symmetricKeyAlgorithm,
-          orgId: org.id,
-          privateKeyTag,
-          privateKeyAlgorithm,
-          privateKeyKeyEncoding,
-          symmetricKeyKeyEncoding
-        },
-        tx
-      );
-    });
-
-    const key = infisicalSymmetricDecrypt({
-      ciphertext: orgBot.encryptedSymmetricKey,
-      iv: orgBot.symmetricKeyIV,
-      tag: orgBot.symmetricKeyTag,
-      keyEncoding: orgBot.symmetricKeyKeyEncoding as SecretKeyEncoding
-    });
-
-    const { ciphertext: encryptedClientId, iv: clientIdIV, tag: clientIdTag } = encryptSymmetric(clientId, key);
-    const {
-      ciphertext: encryptedClientSecret,
-      iv: clientSecretIV,
-      tag: clientSecretTag
-    } = encryptSymmetric(clientSecret, key);
-
-    const oidcCfg = await oidcConfigDAL.create({
-      issuer,
-      isActive,
-      configurationType,
-      discoveryURL,
-      authorizationEndpoint,
-      allowedEmailDomains,
-      jwksUri,
-      tokenEndpoint,
-      userinfoEndpoint,
-      orgId: org.id,
-      encryptedClientId,
-      clientIdIV,
-      clientIdTag,
-      encryptedClientSecret,
-      clientSecretIV,
-      clientSecretTag
-    });
-
-    return oidcCfg;
-  };
-
-  const getOrgAuthStrategy = async (orgSlug: string, callbackPort?: string) => {
-    const appCfg = getConfig();
-
-    const org = await orgDAL.findOne({
-      slug: orgSlug
-    });
-
-    if (!org) {
-      throw new BadRequestError({
-        message: "Organization not found."
-      });
-    }
-
-    const oidcCfg = await getOidc({
-      type: "internal",
-      orgSlug
-    });
-
-    if (!oidcCfg || !oidcCfg.isActive) {
-      throw new BadRequestError({
-        message: "Failed to authenticate with OIDC SSO"
-      });
-    }
-
-    let issuer: Issuer;
-    if (oidcCfg.configurationType === OIDCConfigurationType.DISCOVERY_URL) {
-      if (!oidcCfg.discoveryURL) {
-        throw new BadRequestError({
-          message: "OIDC not configured correctly"
-        });
-      }
-      issuer = await Issuer.discover(oidcCfg.discoveryURL);
-    } else {
-      if (
-        !oidcCfg.issuer ||
-        !oidcCfg.authorizationEndpoint ||
-        !oidcCfg.jwksUri ||
-        !oidcCfg.tokenEndpoint ||
-        !oidcCfg.userinfoEndpoint
-      ) {
-        throw new BadRequestError({
-          message: "OIDC not configured correctly"
-        });
-      }
-      issuer = new OpenIdIssuer({
-        issuer: oidcCfg.issuer,
-        authorization_endpoint: oidcCfg.authorizationEndpoint,
-        jwks_uri: oidcCfg.jwksUri,
-        token_endpoint: oidcCfg.tokenEndpoint,
-        userinfo_endpoint: oidcCfg.userinfoEndpoint
-      });
-    }
-
-    const client = new issuer.Client({
-      client_id: oidcCfg.clientId,
-      client_secret: oidcCfg.clientSecret,
-      redirect_uris: [`${appCfg.SITE_URL}/api/v1/sso/oidc/callback`]
-    });
-
-    const strategy = new OpenIdStrategy(
-      {
-        client,
-        passReqToCallback: true
-      },
-      // eslint-disable-next-line @typescript-eslint/no-explicit-any
-      (_req: any, tokenSet: TokenSet, cb: any) => {
-        const claims = tokenSet.claims();
-        if (!claims.email || !claims.given_name) {
-          throw new BadRequestError({
-            message: "Invalid request. Missing email or first name"
-          });
-        }
-
-        if (oidcCfg.allowedEmailDomains) {
-          const allowedDomains = oidcCfg.allowedEmailDomains.split(", ");
-          if (!allowedDomains.includes(claims.email.split("@")[1])) {
-            throw new BadRequestError({
-              message: "Email not allowed."
-            });
-          }
-        }
-
-        oidcLogin({
-          email: claims.email,
-          externalId: claims.sub,
-          firstName: claims.given_name ?? "",
-          lastName: claims.family_name ?? "",
-          orgId: org.id,
-          callbackPort
-        })
-          .then(({ isUserCompleted, providerAuthToken }) => {
-            cb(null, { isUserCompleted, providerAuthToken });
-          })
-          .catch((error) => {
-            cb(error);
-          });
-      }
-    );
-
-    return strategy;
-  };
-
-  return { oidcLogin, getOrgAuthStrategy, getOidc, updateOidcCfg, createOidcCfg };
-};

backend/src/ee/services/oidc/oidc-config-types.ts

@@ -1,56 +0,0 @@
-import { TGenericPermission } from "@app/lib/types";
-
-export enum OIDCConfigurationType {
-  CUSTOM = "custom",
-  DISCOVERY_URL = "discoveryURL"
-}
-
-export type TOidcLoginDTO = {
-  externalId: string;
-  email: string;
-  firstName: string;
-  lastName?: string;
-  orgId: string;
-  callbackPort?: string;
-};
-
-export type TGetOidcCfgDTO =
-  | ({
-      type: "external";
-      orgSlug: string;
-    } & TGenericPermission)
-  | {
-      type: "internal";
-      orgSlug: string;
-    };
-
-export type TCreateOidcCfgDTO = {
-  issuer?: string;
-  authorizationEndpoint?: string;
-  discoveryURL?: string;
-  configurationType: OIDCConfigurationType;
-  allowedEmailDomains?: string;
-  jwksUri?: string;
-  tokenEndpoint?: string;
-  userinfoEndpoint?: string;
-  clientId: string;
-  clientSecret: string;
-  isActive: boolean;
-  orgSlug: string;
-} & TGenericPermission;
-
-export type TUpdateOidcCfgDTO = Partial<{
-  issuer: string;
-  authorizationEndpoint: string;
-  allowedEmailDomains: string;
-  discoveryURL: string;
-  jwksUri: string;
-  configurationType: OIDCConfigurationType;
-  tokenEndpoint: string;
-  userinfoEndpoint: string;
-  clientId: string;
-  clientSecret: string;
-  isActive: boolean;
-  orgSlug: string;
-}> &
-  TGenericPermission;

backend/src/ee/services/permission/org-permission.ts

@@ -116,6 +116,7 @@ const buildMemberPermission = () => {
   can(OrgPermissionActions.Read, OrgPermissionSubjects.Role);
   can(OrgPermissionActions.Read, OrgPermissionSubjects.Settings);
   can(OrgPermissionActions.Read, OrgPermissionSubjects.Billing);
+  can(OrgPermissionActions.Read, OrgPermissionSubjects.Sso);
   can(OrgPermissionActions.Read, OrgPermissionSubjects.IncidentAccount);
 
   can(OrgPermissionActions.Read, OrgPermissionSubjects.SecretScanning);

backend/src/ee/services/permission/permission-dal.ts

@@ -10,8 +10,7 @@ export type TPermissionDALFactory = ReturnType<typeof permissionDALFactory>;
 export const permissionDALFactory = (db: TDbClient) => {
   const getOrgPermission = async (userId: string, orgId: string) => {
     try {
-      const membership = await db
-        .replicaNode()(TableName.OrgMembership)
+      const membership = await db(TableName.OrgMembership)
         .leftJoin(TableName.OrgRoles, `${TableName.OrgMembership}.roleId`, `${TableName.OrgRoles}.id`)
         .join(TableName.Organization, `${TableName.OrgMembership}.orgId`, `${TableName.Organization}.id`)
         .where("userId", userId)
@@ -29,8 +28,7 @@ export const permissionDALFactory = (db: TDbClient) => {
 
   const getOrgIdentityPermission = async (identityId: string, orgId: string) => {
     try {
-      const membership = await db
-        .replicaNode()(TableName.IdentityOrgMembership)
+      const membership = await db(TableName.IdentityOrgMembership)
         .leftJoin(TableName.OrgRoles, `${TableName.IdentityOrgMembership}.roleId`, `${TableName.OrgRoles}.id`)
         .join(TableName.Organization, `${TableName.IdentityOrgMembership}.orgId`, `${TableName.Organization}.id`)
         .where("identityId", identityId)
@@ -47,13 +45,11 @@ export const permissionDALFactory = (db: TDbClient) => {
 
   const getProjectPermission = async (userId: string, projectId: string) => {
     try {
-      const groups: string[] = await db
-        .replicaNode()(TableName.GroupProjectMembership)
+      const groups: string[] = await db(TableName.GroupProjectMembership)
         .where(`${TableName.GroupProjectMembership}.projectId`, projectId)
         .pluck(`${TableName.GroupProjectMembership}.groupId`);
 
-      const groupDocs = await db
-        .replicaNode()(TableName.UserGroupMembership)
+      const groupDocs = await db(TableName.UserGroupMembership)
         .where(`${TableName.UserGroupMembership}.userId`, userId)
         .whereIn(`${TableName.UserGroupMembership}.groupId`, groups)
         .join(
@@ -235,8 +231,7 @@ export const permissionDALFactory = (db: TDbClient) => {
 
   const getProjectIdentityPermission = async (identityId: string, projectId: string) => {
     try {
-      const docs = await db
-        .replicaNode()(TableName.IdentityProjectMembership)
+      const docs = await db(TableName.IdentityProjectMembership)
         .join(
           TableName.IdentityProjectMembershipRole,
           `${TableName.IdentityProjectMembershipRole}.projectMembershipId`,

backend/src/ee/services/permission/permission-service.ts

@@ -109,9 +109,6 @@ export const permissionServiceFactory = ({
     authMethod: ActorAuthMethod,
     userOrgId?: string
   ) => {
-    // when token is scoped, ensure the passed org id is same as user org id
-    if (userOrgId && userOrgId !== orgId)
-      throw new BadRequestError({ message: "Invalid user token. Scoped to different organization." });
     const membership = await permissionDAL.getOrgPermission(userId, orgId);
     if (!membership) throw new UnauthorizedError({ name: "User not in org" });
     if (membership.role === OrgMembershipRole.Custom && !membership.permissions) {

backend/src/ee/services/saml-config/saml-config-dal.ts

@@ -10,8 +10,7 @@ export const samlConfigDALFactory = (db: TDbClient) => {
 
   const findEnforceableSamlCfg = async (orgId: string) => {
     try {
-      const samlCfg = await db
-        .replicaNode()(TableName.SamlConfig)
+      const samlCfg = await db(TableName.SamlConfig)
         .where({
           orgId,
           isActive: true

backend/src/ee/services/saml-config/saml-config-service.ts

@@ -28,7 +28,6 @@ import { TOrgDALFactory } from "@app/services/org/org-dal";
 import { TOrgMembershipDALFactory } from "@app/services/org-membership/org-membership-dal";
 import { SmtpTemplates, TSmtpService } from "@app/services/smtp/smtp-service";
 import { getServerCfg } from "@app/services/super-admin/super-admin-service";
-import { LoginMethod } from "@app/services/super-admin/super-admin-types";
 import { TUserDALFactory } from "@app/services/user/user-dal";
 import { normalizeUsername } from "@app/services/user/user-fns";
 import { TUserAliasDALFactory } from "@app/services/user-alias/user-alias-dal";
@@ -42,10 +41,7 @@ import { TCreateSamlCfgDTO, TGetSamlCfgDTO, TSamlLoginDTO, TUpdateSamlCfgDTO } f
 
 type TSamlConfigServiceFactoryDep = {
   samlConfigDAL: Pick<TSamlConfigDALFactory, "create" | "findOne" | "update" | "findById">;
-  userDAL: Pick<
-    TUserDALFactory,
-    "create" | "findOne" | "transaction" | "updateById" | "findById" | "findUserEncKeyByUserId"
-  >;
+  userDAL: Pick<TUserDALFactory, "create" | "findOne" | "transaction" | "updateById" | "findById">;
   userAliasDAL: Pick<TUserAliasDALFactory, "create" | "findOne">;
   orgDAL: Pick<
     TOrgDALFactory,
@@ -336,13 +332,6 @@ export const samlConfigServiceFactory = ({
   }: TSamlLoginDTO) => {
     const appCfg = getConfig();
     const serverCfg = await getServerCfg();
-
-    if (serverCfg.enabledLoginMethods && !serverCfg.enabledLoginMethods.includes(LoginMethod.SAML)) {
-      throw new BadRequestError({
-        message: "Login with SAML is disabled by administrator."
-      });
-    }
-
     const userAlias = await userAliasDAL.findOne({
       externalId,
       orgId,
@@ -388,21 +377,6 @@ export const samlConfigServiceFactory = ({
         return foundUser;
       });
     } else {
-      const plan = await licenseService.getPlan(orgId);
-      if (plan?.memberLimit && plan.membersUsed >= plan.memberLimit) {
-        // limit imposed on number of members allowed / number of members used exceeds the number of members allowed
-        throw new BadRequestError({
-          message: "Failed to create new member via SAML due to member limit reached. Upgrade plan to add more members."
-        });
-      }
-
-      if (plan?.identityLimit && plan.identitiesUsed >= plan.identityLimit) {
-        // limit imposed on number of identities allowed / number of identities used exceeds the number of identities allowed
-        throw new BadRequestError({
-          message: "Failed to create new member via SAML due to member limit reached. Upgrade plan to add more members."
-        });
-      }
-
       user = await userDAL.transaction(async (tx) => {
         let newUser: TUsers | undefined;
         if (serverCfg.trustSamlEmails) {
@@ -478,7 +452,6 @@ export const samlConfigServiceFactory = ({
     await licenseService.updateSubscriptionOrgMemberCount(organization.id);
 
     const isUserCompleted = Boolean(user.isAccepted);
-    const userEnc = await userDAL.findUserEncKeyByUserId(user.id);
     const providerAuthToken = jwt.sign(
       {
         authTokenType: AuthTokenType.PROVIDER_TOKEN,
@@ -491,7 +464,6 @@ export const samlConfigServiceFactory = ({
         organizationId: organization.id,
         organizationSlug: organization.slug,
         authMethod: authProvider,
-        hasExchangedPrivateKey: Boolean(userEnc?.serverEncryptedPrivateKey),
         authType: UserAliasType.SAML,
         isUserCompleted,
         ...(relayState

backend/src/ee/services/scim/scim-service.ts

@@ -2,7 +2,7 @@ import { ForbiddenError } from "@casl/ability";
 import slugify from "@sindresorhus/slugify";
 import jwt from "jsonwebtoken";
 
-import { OrgMembershipRole, OrgMembershipStatus, TableName, TOrgMemberships, TUsers } from "@app/db/schemas";
+import { OrgMembershipRole, OrgMembershipStatus, TableName, TGroups, TOrgMemberships, TUsers } from "@app/db/schemas";
 import { TGroupDALFactory } from "@app/ee/services/group/group-dal";
 import { addUsersToGroupByUserIds, removeUsersFromGroupByUserIds } from "@app/ee/services/group/group-fns";
 import { TUserGroupMembershipDALFactory } from "@app/ee/services/group/user-group-membership-dal";
@@ -66,7 +66,7 @@ type TScimServiceFactoryDep = {
   projectMembershipDAL: Pick<TProjectMembershipDALFactory, "find" | "delete" | "findProjectMembershipsByUserId">;
   groupDAL: Pick<
     TGroupDALFactory,
-    "create" | "findOne" | "findAllGroupMembers" | "delete" | "findGroups" | "transaction" | "updateById" | "update"
+    "create" | "findOne" | "findAllGroupMembers" | "update" | "delete" | "findGroups" | "transaction"
   >;
   groupProjectDAL: Pick<TGroupProjectDALFactory, "find">;
   userGroupMembershipDAL: Pick<
@@ -75,7 +75,7 @@ type TScimServiceFactoryDep = {
   >;
   projectKeyDAL: Pick<TProjectKeyDALFactory, "find" | "findLatestProjectKey" | "insertMany" | "delete">;
   projectBotDAL: Pick<TProjectBotDALFactory, "findOne">;
-  licenseService: Pick<TLicenseServiceFactory, "getPlan" | "updateSubscriptionOrgMemberCount">;
+  licenseService: Pick<TLicenseServiceFactory, "getPlan" | "updateSubscriptionOrgIdentitiesCount">;
   permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
   smtpService: Pick<TSmtpService, "sendMail">;
 };
@@ -817,6 +817,7 @@ export const scimServiceFactory = ({
     });
   };
 
+  // TODO: add support for add/remove op
   const updateScimGroupNamePatch = async ({ groupId, orgId, operations }: TUpdateScimGroupNamePatchDTO) => {
     const plan = await licenseService.getPlan(orgId);
     if (!plan.groups)
@@ -839,45 +840,23 @@ export const scimServiceFactory = ({
         status: 403
       });
 
-    let group = await groupDAL.findOne({
-      id: groupId,
-      orgId
-    });
-
-    if (!group) {
-      throw new ScimRequestError({
-        detail: "Group Not Found",
-        status: 404
-      });
-    }
-
+    let group: TGroups | undefined;
     for await (const operation of operations) {
       switch (operation.op) {
         case "replace": {
-          group = await groupDAL.updateById(group.id, {
-            name: operation.value.displayName
-          });
+          await groupDAL.update(
+            {
+              id: groupId,
+              orgId
+            },
+            {
+              name: operation.value.displayName
+            }
+          );
           break;
         }
         case "add": {
-          const orgMemberships = await orgMembershipDAL.find({
-            $in: {
-              id: operation.value.map((member) => member.value)
-            }
-          });
-
-          await addUsersToGroupByUserIds({
-            group,
-            userIds: orgMemberships.map((membership) => membership.userId as string),
-            userDAL,
-            userGroupMembershipDAL,
-            orgDAL,
-            groupProjectDAL,
-            projectKeyDAL,
-            projectDAL,
-            projectBotDAL
-          });
-
+          // TODO
           break;
         }
         case "remove": {
@@ -893,6 +872,13 @@ export const scimServiceFactory = ({
       }
     }
 
+    if (!group) {
+      throw new ScimRequestError({
+        detail: "Group Not Found",
+        status: 404
+      });
+    }
+
     return buildScimGroup({
       groupId: group.id,
       name: group.name,

backend/src/ee/services/scim/scim-types.ts

@@ -125,11 +125,10 @@ type TRemoveOp = {
 
 type TAddOp = {
   op: "add";
-  path: string;
   value: {
     value: string;
     display?: string;
-  }[];
+  };
 };
 
 export type TDeleteScimGroupDTO = {

backend/src/ee/services/secret-approval-policy/secret-approval-policy-dal.ts

@@ -1,59 +1,49 @@
 import { Knex } from "knex";
 
 import { TDbClient } from "@app/db";
-import { SecretApprovalPoliciesSchema, TableName, TSecretApprovalPolicies } from "@app/db/schemas";
+import { TableName, TSecretApprovalPolicies } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
-import { buildFindFilter, ormify, selectAllTableCols, sqlNestRelationships, TFindFilter } from "@app/lib/knex";
+import { buildFindFilter, mergeOneToManyRelation, ormify, selectAllTableCols, TFindFilter } from "@app/lib/knex";
 
 export type TSecretApprovalPolicyDALFactory = ReturnType<typeof secretApprovalPolicyDALFactory>;
 
 export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
   const secretApprovalPolicyOrm = ormify(db, TableName.SecretApprovalPolicy);
 
-  const secretApprovalPolicyFindQuery = (tx: Knex, filter: TFindFilter<TSecretApprovalPolicies>) =>
+  const sapFindQuery = (tx: Knex, filter: TFindFilter<TSecretApprovalPolicies>) =>
     tx(TableName.SecretApprovalPolicy)
       // eslint-disable-next-line
       .where(buildFindFilter(filter))
       .join(TableName.Environment, `${TableName.SecretApprovalPolicy}.envId`, `${TableName.Environment}.id`)
-      .leftJoin(
+      .join(
         TableName.SecretApprovalPolicyApprover,
         `${TableName.SecretApprovalPolicy}.id`,
         `${TableName.SecretApprovalPolicyApprover}.policyId`
       )
-      .select(tx.ref("approverUserId").withSchema(TableName.SecretApprovalPolicyApprover))
-      .select(
-        tx.ref("name").withSchema(TableName.Environment).as("envName"),
-        tx.ref("slug").withSchema(TableName.Environment).as("envSlug"),
-        tx.ref("id").withSchema(TableName.Environment).as("envId"),
-        tx.ref("projectId").withSchema(TableName.Environment)
-      )
+      .select(tx.ref("approverId").withSchema(TableName.SecretApprovalPolicyApprover))
+      .select(tx.ref("name").withSchema(TableName.Environment).as("envName"))
+      .select(tx.ref("slug").withSchema(TableName.Environment).as("envSlug"))
+      .select(tx.ref("id").withSchema(TableName.Environment).as("envId"))
+      .select(tx.ref("projectId").withSchema(TableName.Environment))
       .select(selectAllTableCols(TableName.SecretApprovalPolicy))
       .orderBy("createdAt", "asc");
 
   const findById = async (id: string, tx?: Knex) => {
     try {
-      const doc = await secretApprovalPolicyFindQuery(tx || db.replicaNode(), {
+      const doc = await sapFindQuery(tx || db, {
         [`${TableName.SecretApprovalPolicy}.id` as "id"]: id
       });
-      const formatedDoc = sqlNestRelationships({
-        data: doc,
-        key: "id",
-        parentMapper: (data) => ({
-          environment: { id: data.envId, name: data.envName, slug: data.envSlug },
-          projectId: data.projectId,
-          ...SecretApprovalPoliciesSchema.parse(data)
+      const formatedDoc = mergeOneToManyRelation(
+        doc,
+        "id",
+        ({ approverId, envId, envName: name, envSlug: slug, ...el }) => ({
+          ...el,
+          envId,
+          environment: { id: envId, name, slug }
         }),
-        childrenMapper: [
-          {
-            key: "approverUserId",
-            label: "userApprovers" as const,
-            mapper: ({ approverUserId }) => ({
-              userId: approverUserId
-            })
-          }
-        ]
-      });
-
+        ({ approverId }) => approverId,
+        "approvers"
+      );
       return formatedDoc?.[0];
     } catch (error) {
       throw new DatabaseError({ error, name: "FindById" });
@@ -62,25 +52,18 @@ export const secretApprovalPolicyDALFactory = (db: TDbClient) => {
 
   const find = async (filter: TFindFilter<TSecretApprovalPolicies & { projectId: string }>, tx?: Knex) => {
     try {
-      const docs = await secretApprovalPolicyFindQuery(tx || db.replicaNode(), filter);
-      const formatedDoc = sqlNestRelationships({
-        data: docs,
-        key: "id",
-        parentMapper: (data) => ({
-          environment: { id: data.envId, name: data.envName, slug: data.envSlug },
-          projectId: data.projectId,
-          ...SecretApprovalPoliciesSchema.parse(data)
+      const docs = await sapFindQuery(tx || db, filter);
+      const formatedDoc = mergeOneToManyRelation(
+        docs,
+        "id",
+        ({ approverId, envId, envName: name, envSlug: slug, ...el }) => ({
+          ...el,
+          envId,
+          environment: { id: envId, name, slug }
         }),
-        childrenMapper: [
-          {
-            key: "approverUserId",
-            label: "userApprovers" as const,
-            mapper: ({ approverUserId }) => ({
-              userId: approverUserId
-            })
-          }
-        ]
-      });
+        ({ approverId }) => approverId,
+        "approvers"
+      );
       return formatedDoc;
     } catch (error) {
       throw new DatabaseError({ error, name: "Find" });

backend/src/ee/services/secret-approval-policy/secret-approval-policy-service.ts

@@ -7,6 +7,7 @@ import { BadRequestError } from "@app/lib/errors";
 import { removeTrailingSlash } from "@app/lib/fn";
 import { containsGlobPatterns } from "@app/lib/picomatch";
 import { TProjectEnvDALFactory } from "@app/services/project-env/project-env-dal";
+import { TProjectMembershipDALFactory } from "@app/services/project-membership/project-membership-dal";
 
 import { TSecretApprovalPolicyApproverDALFactory } from "./secret-approval-policy-approver-dal";
 import { TSecretApprovalPolicyDALFactory } from "./secret-approval-policy-dal";
@@ -28,6 +29,7 @@ type TSecretApprovalPolicyServiceFactoryDep = {
   secretApprovalPolicyDAL: TSecretApprovalPolicyDALFactory;
   projectEnvDAL: Pick<TProjectEnvDALFactory, "findOne">;
   secretApprovalPolicyApproverDAL: TSecretApprovalPolicyApproverDALFactory;
+  projectMembershipDAL: Pick<TProjectMembershipDALFactory, "find">;
 };
 
 export type TSecretApprovalPolicyServiceFactory = ReturnType<typeof secretApprovalPolicyServiceFactory>;
@@ -36,7 +38,8 @@ export const secretApprovalPolicyServiceFactory = ({
   secretApprovalPolicyDAL,
   permissionService,
   secretApprovalPolicyApproverDAL,
-  projectEnvDAL
+  projectEnvDAL,
+  projectMembershipDAL
 }: TSecretApprovalPolicyServiceFactoryDep) => {
   const createSecretApprovalPolicy = async ({
     name,
@@ -45,12 +48,12 @@ export const secretApprovalPolicyServiceFactory = ({
     actorOrgId,
     actorAuthMethod,
     approvals,
-    approverUserIds,
+    approvers,
     projectId,
     secretPath,
     environment
   }: TCreateSapDTO) => {
-    if (approvals > approverUserIds.length)
+    if (approvals > approvers.length)
       throw new BadRequestError({ message: "Approvals cannot be greater than approvers" });
 
     const { permission } = await permissionService.getProjectPermission(
@@ -67,6 +70,13 @@ export const secretApprovalPolicyServiceFactory = ({
     const env = await projectEnvDAL.findOne({ slug: environment, projectId });
     if (!env) throw new BadRequestError({ message: "Environment not found" });
 
+    const secretApprovers = await projectMembershipDAL.find({
+      projectId,
+      $in: { id: approvers }
+    });
+    if (secretApprovers.length !== approvers.length)
+      throw new BadRequestError({ message: "Approver not found in project" });
+
     const secretApproval = await secretApprovalPolicyDAL.transaction(async (tx) => {
       const doc = await secretApprovalPolicyDAL.create(
         {
@@ -78,8 +88,8 @@ export const secretApprovalPolicyServiceFactory = ({
         tx
       );
       await secretApprovalPolicyApproverDAL.insertMany(
-        approverUserIds.map((approverUserId) => ({
-          approverUserId,
+        secretApprovers.map(({ id }) => ({
+          approverId: id,
           policyId: doc.id
         })),
         tx
@@ -90,7 +100,7 @@ export const secretApprovalPolicyServiceFactory = ({
   };
 
   const updateSecretApprovalPolicy = async ({
-    approverUserIds,
+    approvers,
     secretPath,
     name,
     actorId,
@@ -122,11 +132,22 @@ export const secretApprovalPolicyServiceFactory = ({
         },
         tx
       );
-      if (approverUserIds) {
+      if (approvers) {
+        const secretApprovers = await projectMembershipDAL.find(
+          {
+            projectId: secretApprovalPolicy.projectId,
+            $in: { id: approvers }
+          },
+          { tx }
+        );
+        if (secretApprovers.length !== approvers.length)
+          throw new BadRequestError({ message: "Approver not found in project" });
+        if (doc.approvals > secretApprovers.length)
+          throw new BadRequestError({ message: "Approvals cannot be greater than approvers" });
         await secretApprovalPolicyApproverDAL.delete({ policyId: doc.id }, tx);
         await secretApprovalPolicyApproverDAL.insertMany(
-          approverUserIds.map((approverUserId) => ({
-            approverUserId,
+          secretApprovers.map(({ id }) => ({
+            approverId: id,
             policyId: doc.id
           })),
           tx

backend/src/ee/services/secret-approval-policy/secret-approval-policy-types.ts

@@ -4,7 +4,7 @@ export type TCreateSapDTO = {
   approvals: number;
   secretPath?: string | null;
   environment: string;
-  approverUserIds: string[];
+  approvers: string[];
   projectId: string;
   name: string;
 } & Omit<TProjectPermission, "projectId">;
@@ -13,7 +13,7 @@ export type TUpdateSapDTO = {
   secretPolicyId: string;
   approvals?: number;
   secretPath?: string | null;
-  approverUserIds: string[];
+  approvers: string[];
   name?: string;
 } & Omit<TProjectPermission, "projectId">;
 

backend/src/ee/services/secret-approval-request/secret-approval-request-dal.ts

@@ -5,8 +5,7 @@ import {
   SecretApprovalRequestsSchema,
   TableName,
   TSecretApprovalRequests,
-  TSecretApprovalRequestsSecrets,
-  TUsers
+  TSecretApprovalRequestsSecrets
 } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
 import { ormify, selectAllTableCols, sqlNestRelationships, stripUndefinedInWhere, TFindFilter } from "@app/lib/knex";
@@ -17,7 +16,7 @@ export type TSecretApprovalRequestDALFactory = ReturnType<typeof secretApprovalR
 
 type TFindQueryFilter = {
   projectId: string;
-  userId: string;
+  membershipId: string;
   status?: RequestState;
   environment?: string;
   committer?: string;
@@ -38,68 +37,32 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
         `${TableName.SecretApprovalRequest}.policyId`,
         `${TableName.SecretApprovalPolicy}.id`
       )
-      .leftJoin<TUsers>(
-        db(TableName.Users).as("statusChangedByUser"),
-        `${TableName.SecretApprovalRequest}.statusChangedByUserId`,
-        `statusChangedByUser.id`
-      )
-      .join<TUsers>(
-        db(TableName.Users).as("committerUser"),
-        `${TableName.SecretApprovalRequest}.committerUserId`,
-        `committerUser.id`
-      )
       .join(
         TableName.SecretApprovalPolicyApprover,
         `${TableName.SecretApprovalPolicy}.id`,
         `${TableName.SecretApprovalPolicyApprover}.policyId`
       )
-      .join<TUsers>(
-        db(TableName.Users).as("secretApprovalPolicyApproverUser"),
-        `${TableName.SecretApprovalPolicyApprover}.approverUserId`,
-        "secretApprovalPolicyApproverUser.id"
-      )
       .leftJoin(
         TableName.SecretApprovalRequestReviewer,
         `${TableName.SecretApprovalRequest}.id`,
         `${TableName.SecretApprovalRequestReviewer}.requestId`
       )
-      .leftJoin<TUsers>(
-        db(TableName.Users).as("secretApprovalReviewerUser"),
-        `${TableName.SecretApprovalRequestReviewer}.reviewerUserId`,
-        `secretApprovalReviewerUser.id`
-      )
       .select(selectAllTableCols(TableName.SecretApprovalRequest))
       .select(
-        tx.ref("approverUserId").withSchema(TableName.SecretApprovalPolicyApprover),
-        tx.ref("email").withSchema("secretApprovalPolicyApproverUser").as("approverEmail"),
-        tx.ref("username").withSchema("secretApprovalPolicyApproverUser").as("approverUsername"),
-        tx.ref("firstName").withSchema("secretApprovalPolicyApproverUser").as("approverFirstName"),
-        tx.ref("lastName").withSchema("secretApprovalPolicyApproverUser").as("approverLastName"),
-        tx.ref("email").withSchema("statusChangedByUser").as("statusChangedByUserEmail"),
-        tx.ref("username").withSchema("statusChangedByUser").as("statusChangedByUserUsername"),
-        tx.ref("firstName").withSchema("statusChangedByUser").as("statusChangedByUserFirstName"),
-        tx.ref("lastName").withSchema("statusChangedByUser").as("statusChangedByUserLastName"),
-        tx.ref("email").withSchema("committerUser").as("committerUserEmail"),
-        tx.ref("username").withSchema("committerUser").as("committerUserUsername"),
-        tx.ref("firstName").withSchema("committerUser").as("committerUserFirstName"),
-        tx.ref("lastName").withSchema("committerUser").as("committerUserLastName"),
-        tx.ref("reviewerUserId").withSchema(TableName.SecretApprovalRequestReviewer),
+        tx.ref("member").withSchema(TableName.SecretApprovalRequestReviewer).as("reviewerMemberId"),
         tx.ref("status").withSchema(TableName.SecretApprovalRequestReviewer).as("reviewerStatus"),
-        tx.ref("email").withSchema("secretApprovalReviewerUser").as("reviewerEmail"),
-        tx.ref("username").withSchema("secretApprovalReviewerUser").as("reviewerUsername"),
-        tx.ref("firstName").withSchema("secretApprovalReviewerUser").as("reviewerFirstName"),
-        tx.ref("lastName").withSchema("secretApprovalReviewerUser").as("reviewerLastName"),
         tx.ref("id").withSchema(TableName.SecretApprovalPolicy).as("policyId"),
         tx.ref("name").withSchema(TableName.SecretApprovalPolicy).as("policyName"),
         tx.ref("projectId").withSchema(TableName.Environment),
         tx.ref("slug").withSchema(TableName.Environment).as("environment"),
         tx.ref("secretPath").withSchema(TableName.SecretApprovalPolicy).as("policySecretPath"),
-        tx.ref("approvals").withSchema(TableName.SecretApprovalPolicy).as("policyApprovals")
+        tx.ref("approvals").withSchema(TableName.SecretApprovalPolicy).as("policyApprovals"),
+        tx.ref("approverId").withSchema(TableName.SecretApprovalPolicyApprover)
       );
 
   const findById = async (id: string, tx?: Knex) => {
     try {
-      const sql = findQuery({ [`${TableName.SecretApprovalRequest}.id` as "id"]: id }, tx || db.replicaNode());
+      const sql = findQuery({ [`${TableName.SecretApprovalRequest}.id` as "id"]: id }, tx || db);
       const docs = await sql;
       const formatedDoc = sqlNestRelationships({
         data: docs,
@@ -108,22 +71,6 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
           ...SecretApprovalRequestsSchema.parse(el),
           projectId: el.projectId,
           environment: el.environment,
-          statusChangedByUser: el.statusChangedByUserId
-            ? {
-                userId: el.statusChangedByUserId,
-                email: el.statusChangedByUserEmail,
-                firstName: el.statusChangedByUserFirstName,
-                lastName: el.statusChangedByUserLastName,
-                username: el.statusChangedByUserUsername
-              }
-            : undefined,
-          committerUser: {
-            userId: el.committerUserId,
-            email: el.committerUserEmail,
-            firstName: el.committerUserFirstName,
-            lastName: el.committerUserLastName,
-            username: el.committerUserUsername
-          },
           policy: {
             id: el.policyId,
             name: el.policyName,
@@ -133,34 +80,11 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
         }),
         childrenMapper: [
           {
-            key: "reviewerUserId",
+            key: "reviewerMemberId",
             label: "reviewers" as const,
-            mapper: ({
-              reviewerUserId: userId,
-              reviewerStatus: status,
-              reviewerEmail: email,
-              reviewerLastName: lastName,
-              reviewerUsername: username,
-              reviewerFirstName: firstName
-            }) => (userId ? { userId, status, email, firstName, lastName, username } : undefined)
+            mapper: ({ reviewerMemberId: member, reviewerStatus: status }) => (member ? { member, status } : undefined)
           },
-          {
-            key: "approverUserId",
-            label: "approvers" as const,
-            mapper: ({
-              approverUserId,
-              approverEmail: email,
-              approverUsername: username,
-              approverLastName: lastName,
-              approverFirstName: firstName
-            }) => ({
-              userId: approverUserId,
-              email,
-              firstName,
-              lastName,
-              username
-            })
-          }
+          { key: "approverId", label: "approvers" as const, mapper: ({ approverId }) => approverId }
         ]
       });
       if (!formatedDoc?.[0]) return;
@@ -173,12 +97,12 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
     }
   };
 
-  const findProjectRequestCount = async (projectId: string, userId: string, tx?: Knex) => {
+  const findProjectRequestCount = async (projectId: string, membershipId: string, tx?: Knex) => {
     try {
       const docs = await (tx || db)
         .with(
           "temp",
-          (tx || db.replicaNode())(TableName.SecretApprovalRequest)
+          (tx || db)(TableName.SecretApprovalRequest)
             .join(TableName.SecretFolder, `${TableName.SecretApprovalRequest}.folderId`, `${TableName.SecretFolder}.id`)
             .join(TableName.Environment, `${TableName.SecretFolder}.envId`, `${TableName.Environment}.id`)
             .join(
@@ -190,8 +114,8 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
             .andWhere(
               (bd) =>
                 void bd
-                  .where(`${TableName.SecretApprovalPolicyApprover}.approverUserId`, userId)
-                  .orWhere(`${TableName.SecretApprovalRequest}.committerUserId`, userId)
+                  .where(`${TableName.SecretApprovalPolicyApprover}.approverId`, membershipId)
+                  .orWhere(`${TableName.SecretApprovalRequest}.committerId`, membershipId)
             )
             .select("status", `${TableName.SecretApprovalRequest}.id`)
             .groupBy(`${TableName.SecretApprovalRequest}.id`, "status")
@@ -218,13 +142,13 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
   };
 
   const findByProjectId = async (
-    { status, limit = 20, offset = 0, projectId, committer, environment, userId }: TFindQueryFilter,
+    { status, limit = 20, offset = 0, projectId, committer, environment, membershipId }: TFindQueryFilter,
     tx?: Knex
   ) => {
     try {
       // akhilmhdh: If ever u wanted a 1 to so many relationship connected with pagination
       // this is the place u wanna look at.
-      const query = (tx || db.replicaNode())(TableName.SecretApprovalRequest)
+      const query = (tx || db)(TableName.SecretApprovalRequest)
         .join(TableName.SecretFolder, `${TableName.SecretApprovalRequest}.folderId`, `${TableName.SecretFolder}.id`)
         .join(TableName.Environment, `${TableName.SecretFolder}.envId`, `${TableName.Environment}.id`)
         .join(
@@ -237,11 +161,6 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
           `${TableName.SecretApprovalPolicy}.id`,
           `${TableName.SecretApprovalPolicyApprover}.policyId`
         )
-        .join<TUsers>(
-          db(TableName.Users).as("committerUser"),
-          `${TableName.SecretApprovalRequest}.committerUserId`,
-          `committerUser.id`
-        )
         .leftJoin(
           TableName.SecretApprovalRequestReviewer,
           `${TableName.SecretApprovalRequest}.id`,
@@ -257,21 +176,20 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
             projectId,
             [`${TableName.Environment}.slug` as "slug"]: environment,
             [`${TableName.SecretApprovalRequest}.status`]: status,
-            committerUserId: committer
+            committerId: committer
           })
         )
         .andWhere(
           (bd) =>
             void bd
-              .where(`${TableName.SecretApprovalPolicyApprover}.approverUserId`, userId)
-              .orWhere(`${TableName.SecretApprovalRequest}.committerUserId`, userId)
+              .where(`${TableName.SecretApprovalPolicyApprover}.approverId`, membershipId)
+              .orWhere(`${TableName.SecretApprovalRequest}.committerId`, membershipId)
         )
         .select(selectAllTableCols(TableName.SecretApprovalRequest))
         .select(
           db.ref("projectId").withSchema(TableName.Environment),
           db.ref("slug").withSchema(TableName.Environment).as("environment"),
-          db.ref("id").withSchema(TableName.SecretApprovalRequestReviewer).as("reviewerId"),
-          db.ref("reviewerUserId").withSchema(TableName.SecretApprovalRequestReviewer),
+          db.ref("id").withSchema(TableName.SecretApprovalRequestReviewer).as("reviewerMemberId"),
           db.ref("status").withSchema(TableName.SecretApprovalRequestReviewer).as("reviewerStatus"),
           db.ref("id").withSchema(TableName.SecretApprovalPolicy).as("policyId"),
           db.ref("name").withSchema(TableName.SecretApprovalPolicy).as("policyName"),
@@ -283,11 +201,7 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
           ),
           db.ref("secretPath").withSchema(TableName.SecretApprovalPolicy).as("policySecretPath"),
           db.ref("approvals").withSchema(TableName.SecretApprovalPolicy).as("policyApprovals"),
-          db.ref("approverUserId").withSchema(TableName.SecretApprovalPolicyApprover),
-          db.ref("email").withSchema("committerUser").as("committerUserEmail"),
-          db.ref("username").withSchema("committerUser").as("committerUserUsername"),
-          db.ref("firstName").withSchema("committerUser").as("committerUserFirstName"),
-          db.ref("lastName").withSchema("committerUser").as("committerUserLastName")
+          db.ref("approverId").withSchema(TableName.SecretApprovalPolicyApprover)
         )
         .orderBy("createdAt", "desc");
 
@@ -309,26 +223,18 @@ export const secretApprovalRequestDALFactory = (db: TDbClient) => {
             name: el.policyName,
             approvals: el.policyApprovals,
             secretPath: el.policySecretPath
-          },
-          committerUser: {
-            userId: el.committerUserId,
-            email: el.committerUserEmail,
-            firstName: el.committerUserFirstName,
-            lastName: el.committerUserLastName,
-            username: el.committerUserUsername
           }
         }),
         childrenMapper: [
           {
-            key: "reviewerId",
+            key: "reviewerMemberId",
             label: "reviewers" as const,
-            mapper: ({ reviewerUserId, reviewerStatus: s }) =>
-              reviewerUserId ? { userId: reviewerUserId, status: s } : undefined
+            mapper: ({ reviewerMemberId: member, reviewerStatus: s }) => (member ? { member, status: s } : undefined)
           },
           {
-            key: "approverUserId",
+            key: "approverId",
             label: "approvers" as const,
-            mapper: ({ approverUserId }) => approverUserId
+            mapper: ({ approverId }) => approverId
           },
           {
             key: "commitId",

backend/src/ee/services/secret-approval-request/secret-approval-request-secret-dal.ts

@@ -47,7 +47,7 @@ export const secretApprovalRequestSecretDALFactory = (db: TDbClient) => {
 
   const findByRequestId = async (requestId: string, tx?: Knex) => {
     try {
-      const doc = await (tx || db.replicaNode())({
+      const doc = await (tx || db)({
         secVerTag: TableName.SecretTag
       })
         .from(TableName.SecretApprovalRequestSecret)