Merge pull request #2318 from akhilmhdh/feat/ui-patch-v1

feat: resolved ui issues related to permission based hiding

backend/package-lock.json

@@ -76,6 +76,7 @@
         "pino": "^8.16.2",
         "posthog-node": "^3.6.2",
         "probot": "^13.0.0",
+        "safe-regex": "^2.1.1",
         "smee-client": "^2.0.0",
         "tedious": "^18.2.1",
         "tweetnacl": "^1.0.3",
@@ -107,6 +108,7 @@
         "@types/picomatch": "^2.3.3",
         "@types/prompt-sync": "^4.2.3",
         "@types/resolve": "^1.20.6",
+        "@types/safe-regex": "^1.1.6",
         "@types/uuid": "^9.0.7",
         "@typescript-eslint/eslint-plugin": "^6.20.0",
         "@typescript-eslint/parser": "^6.20.0",
@@ -9801,6 +9803,12 @@
       "integrity": "sha512-A4STmOXPhMUtHH+S6ymgE2GiBSMqf4oTvcQZMcHzokuTLVYzXTB8ttjcgxOVaAp2lGwEdzZ0J+cRbbeevQj1UQ==",
       "dev": true
     },
+    "node_modules/@types/safe-regex": {
+      "version": "1.1.6",
+      "resolved": "https://registry.npmjs.org/@types/safe-regex/-/safe-regex-1.1.6.tgz",
+      "integrity": "sha512-CQ/uPB9fLOPKwDsrTeVbNIkwfUthTWOx0l6uIGwVFjZxv7e68pCW5gtTYFzdJi3EBJp8h8zYhJbTasAbX7gEMQ==",
+      "dev": true
+    },
     "node_modules/@types/semver": {
       "version": "7.5.6",
       "resolved": "https://registry.npmjs.org/@types/semver/-/semver-7.5.6.tgz",
@@ -17821,6 +17829,14 @@
         "@babel/runtime": "^7.8.4"
       }
     },
+    "node_modules/regexp-tree": {
+      "version": "0.1.27",
+      "resolved": "https://registry.npmjs.org/regexp-tree/-/regexp-tree-0.1.27.tgz",
+      "integrity": "sha512-iETxpjK6YoRWJG5o6hXLwvjYAoW+FEZn9os0PD/b6AP6xQwsa/Y7lCVgIixBbUPMfhu+i2LtdeAqVTgGlQarfA==",
+      "bin": {
+        "regexp-tree": "bin/regexp-tree"
+      }
+    },
     "node_modules/regexp.prototype.flags": {
       "version": "1.5.1",
       "resolved": "https://registry.npmjs.org/regexp.prototype.flags/-/regexp.prototype.flags-1.5.1.tgz",
@@ -18137,6 +18153,14 @@
         }
       ]
     },
+    "node_modules/safe-regex": {
+      "version": "2.1.1",
+      "resolved": "https://registry.npmjs.org/safe-regex/-/safe-regex-2.1.1.tgz",
+      "integrity": "sha512-rx+x8AMzKb5Q5lQ95Zoi6ZbJqwCLkqi3XuJXp5P3rT8OEc6sZCJG5AE5dU3lsgRr/F4Bs31jSlVN+j5KrsGu9A==",
+      "dependencies": {
+        "regexp-tree": "~0.1.1"
+      }
+    },
     "node_modules/safe-regex-test": {
       "version": "1.0.0",
       "resolved": "https://registry.npmjs.org/safe-regex-test/-/safe-regex-test-1.0.0.tgz",

backend/package.json

@@ -78,6 +78,7 @@
     "@types/picomatch": "^2.3.3",
     "@types/prompt-sync": "^4.2.3",
     "@types/resolve": "^1.20.6",
+    "@types/safe-regex": "^1.1.6",
     "@types/uuid": "^9.0.7",
     "@typescript-eslint/eslint-plugin": "^6.20.0",
     "@typescript-eslint/parser": "^6.20.0",
@@ -172,6 +173,7 @@
     "pino": "^8.16.2",
     "posthog-node": "^3.6.2",
     "probot": "^13.0.0",
+    "safe-regex": "^2.1.1",
     "smee-client": "^2.0.0",
     "tedious": "^18.2.1",
     "tweetnacl": "^1.0.3",

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

@@ -36,6 +36,7 @@ import { ActorAuthMethod, ActorType } from "@app/services/auth/auth-type";
 import { TAuthTokenServiceFactory } from "@app/services/auth-token/auth-token-service";
 import { TCertificateServiceFactory } from "@app/services/certificate/certificate-service";
 import { TCertificateAuthorityServiceFactory } from "@app/services/certificate-authority/certificate-authority-service";
+import { TCertificateTemplateServiceFactory } from "@app/services/certificate-template/certificate-template-service";
 import { TGroupProjectServiceFactory } from "@app/services/group-project/group-project-service";
 import { TIdentityServiceFactory } from "@app/services/identity/identity-service";
 import { TIdentityAccessTokenServiceFactory } from "@app/services/identity-access-token/identity-access-token-service";
@@ -52,6 +53,8 @@ import { TIntegrationAuthServiceFactory } from "@app/services/integration-auth/i
 import { TOrgRoleServiceFactory } from "@app/services/org/org-role-service";
 import { TOrgServiceFactory } from "@app/services/org/org-service";
 import { TOrgAdminServiceFactory } from "@app/services/org-admin/org-admin-service";
+import { TPkiAlertServiceFactory } from "@app/services/pki-alert/pki-alert-service";
+import { TPkiCollectionServiceFactory } from "@app/services/pki-collection/pki-collection-service";
 import { TProjectServiceFactory } from "@app/services/project/project-service";
 import { TProjectBotServiceFactory } from "@app/services/project-bot/project-bot-service";
 import { TProjectEnvServiceFactory } from "@app/services/project-env/project-env-service";
@@ -116,6 +119,7 @@ declare module "fastify" {
       group: TGroupServiceFactory;
       groupProject: TGroupProjectServiceFactory;
       apiKey: TApiKeyServiceFactory;
+      pkiAlert: TPkiAlertServiceFactory;
       project: TProjectServiceFactory;
       projectMembership: TProjectMembershipServiceFactory;
       projectEnv: TProjectEnvServiceFactory;
@@ -153,8 +157,10 @@ declare module "fastify" {
       auditLog: TAuditLogServiceFactory;
       auditLogStream: TAuditLogStreamServiceFactory;
       certificate: TCertificateServiceFactory;
+      certificateTemplate: TCertificateTemplateServiceFactory;
       certificateAuthority: TCertificateAuthorityServiceFactory;
       certificateAuthorityCrl: TCertificateAuthorityCrlServiceFactory;
+      pkiCollection: TPkiCollectionServiceFactory;
       secretScanning: TSecretScanningServiceFactory;
       license: TLicenseServiceFactory;
       trustedIp: TTrustedIpServiceFactory;

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

@@ -53,6 +53,9 @@ import {
   TCertificateSecretsUpdate,
   TCertificatesInsert,
   TCertificatesUpdate,
+  TCertificateTemplates,
+  TCertificateTemplatesInsert,
+  TCertificateTemplatesUpdate,
   TDynamicSecretLeases,
   TDynamicSecretLeasesInsert,
   TDynamicSecretLeasesUpdate,
@@ -161,6 +164,15 @@ import {
   TOrgRoles,
   TOrgRolesInsert,
   TOrgRolesUpdate,
+  TPkiAlerts,
+  TPkiAlertsInsert,
+  TPkiAlertsUpdate,
+  TPkiCollectionItems,
+  TPkiCollectionItemsInsert,
+  TPkiCollectionItemsUpdate,
+  TPkiCollections,
+  TPkiCollectionsInsert,
+  TPkiCollectionsUpdate,
   TProjectBots,
   TProjectBotsInsert,
   TProjectBotsUpdate,
@@ -355,6 +367,11 @@ declare module "knex/types/tables" {
       TCertificateAuthorityCrlUpdate
     >;
     [TableName.Certificate]: KnexOriginal.CompositeTableType<TCertificates, TCertificatesInsert, TCertificatesUpdate>;
+    [TableName.CertificateTemplate]: KnexOriginal.CompositeTableType<
+      TCertificateTemplates,
+      TCertificateTemplatesInsert,
+      TCertificateTemplatesUpdate
+    >;
     [TableName.CertificateBody]: KnexOriginal.CompositeTableType<
       TCertificateBodies,
       TCertificateBodiesInsert,
@@ -365,6 +382,17 @@ declare module "knex/types/tables" {
       TCertificateSecretsInsert,
       TCertificateSecretsUpdate
     >;
+    [TableName.PkiAlert]: KnexOriginal.CompositeTableType<TPkiAlerts, TPkiAlertsInsert, TPkiAlertsUpdate>;
+    [TableName.PkiCollection]: KnexOriginal.CompositeTableType<
+      TPkiCollections,
+      TPkiCollectionsInsert,
+      TPkiCollectionsUpdate
+    >;
+    [TableName.PkiCollectionItem]: KnexOriginal.CompositeTableType<
+      TPkiCollectionItems,
+      TPkiCollectionItemsInsert,
+      TPkiCollectionItemsUpdate
+    >;
     [TableName.UserGroupMembership]: KnexOriginal.CompositeTableType<
       TUserGroupMembership,
       TUserGroupMembershipInsert,

backend/src/db/migrations/20240724101056_access-request-groups.ts

@@ -0,0 +1,294 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  // ---------- ACCESS APPROVAL POLICY APPROVER ------------
+  const hasApproverUserId = await knex.schema.hasColumn(TableName.AccessApprovalPolicyApprover, "approverUserId");
+  const hasApproverId = await knex.schema.hasColumn(TableName.AccessApprovalPolicyApprover, "approverId");
+
+  if (!hasApproverUserId) {
+    // add the new fields
+    await knex.schema.alterTable(TableName.AccessApprovalPolicyApprover, (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.AccessApprovalPolicyApprover).update({
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      approverUserId: knex(TableName.ProjectMembership)
+        .select("userId")
+        .where("id", knex.raw("??", [`${TableName.AccessApprovalPolicyApprover}.approverId`]))
+    });
+    // drop the old field
+    await knex.schema.alterTable(TableName.AccessApprovalPolicyApprover, (tb) => {
+      if (hasApproverId) tb.dropColumn("approverId");
+      tb.uuid("approverUserId").notNullable().alter();
+    });
+  }
+
+  // ---------- ACCESS APPROVAL REQUEST ------------
+  const hasAccessApprovalRequestTable = await knex.schema.hasTable(TableName.AccessApprovalRequest);
+  const hasRequestedByUserId = await knex.schema.hasColumn(TableName.AccessApprovalRequest, "requestedByUserId");
+  const hasRequestedBy = await knex.schema.hasColumn(TableName.AccessApprovalRequest, "requestedBy");
+
+  if (hasAccessApprovalRequestTable) {
+    // new fields
+    await knex.schema.alterTable(TableName.AccessApprovalRequest, (tb) => {
+      if (!hasRequestedByUserId) {
+        tb.uuid("requestedByUserId");
+        tb.foreign("requestedByUserId").references("id").inTable(TableName.Users).onDelete("SET NULL");
+      }
+    });
+
+    // copy the assigned project membership => user id to new fields
+    await knex(TableName.AccessApprovalRequest).update({
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      requestedByUserId: knex(TableName.ProjectMembership)
+        .select("userId")
+        .where("id", knex.raw("??", [`${TableName.AccessApprovalRequest}.requestedBy`]))
+    });
+    // drop old fields
+    await knex.schema.alterTable(TableName.AccessApprovalRequest, (tb) => {
+      if (hasRequestedBy) {
+        // DROP AT A LATER TIME
+        // tb.dropColumn("requestedBy");
+
+        // ADD ALLOW NULLABLE FOR NOW
+        tb.uuid("requestedBy").nullable().alter();
+      }
+      tb.uuid("requestedByUserId").notNullable().alter();
+    });
+  }
+
+  // ---------- ACCESS APPROVAL REQUEST REVIEWER ------------
+  const hasMemberId = await knex.schema.hasColumn(TableName.AccessApprovalRequestReviewer, "member");
+  const hasReviewerUserId = await knex.schema.hasColumn(TableName.AccessApprovalRequestReviewer, "reviewerUserId");
+  if (!hasReviewerUserId) {
+    // new fields
+    await knex.schema.alterTable(TableName.AccessApprovalRequestReviewer, (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.AccessApprovalRequestReviewer).update({
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      reviewerUserId: knex(TableName.ProjectMembership)
+        .select("userId")
+        .where("id", knex.raw("??", [`${TableName.AccessApprovalRequestReviewer}.member`]))
+    });
+    // drop table
+    await knex.schema.alterTable(TableName.AccessApprovalRequestReviewer, (tb) => {
+      if (hasMemberId) {
+        // DROP AT A LATER TIME
+        // tb.dropColumn("member");
+
+        // ADD ALLOW NULLABLE FOR NOW
+        tb.uuid("member").nullable().alter();
+      }
+      tb.uuid("reviewerUserId").notNullable().alter();
+    });
+  }
+
+  // ---------- PROJECT USER ADDITIONAL PRIVILEGE ------------
+  const projectUserAdditionalPrivilegeHasProjectMembershipId = await knex.schema.hasColumn(
+    TableName.ProjectUserAdditionalPrivilege,
+    "projectMembershipId"
+  );
+
+  const projectUserAdditionalPrivilegeHasUserId = await knex.schema.hasColumn(
+    TableName.ProjectUserAdditionalPrivilege,
+    "userId"
+  );
+
+  if (!projectUserAdditionalPrivilegeHasUserId) {
+    await knex.schema.alterTable(TableName.ProjectUserAdditionalPrivilege, (tb) => {
+      tb.uuid("userId");
+      tb.foreign("userId").references("id").inTable(TableName.Users).onDelete("CASCADE");
+
+      tb.string("projectId");
+      tb.foreign("projectId").references("id").inTable(TableName.Project).onDelete("CASCADE");
+    });
+
+    await knex(TableName.ProjectUserAdditionalPrivilege)
+      .update({
+        // eslint-disable-next-line
+        // @ts-ignore because generate schema happens after this
+        userId: knex(TableName.ProjectMembership)
+          .select("userId")
+          .where("id", knex.raw("??", [`${TableName.ProjectUserAdditionalPrivilege}.projectMembershipId`])),
+
+        // eslint-disable-next-line
+        // @ts-ignore because generate schema happens after this
+        projectId: knex(TableName.ProjectMembership)
+          .select("projectId")
+          .where("id", knex.raw("??", [`${TableName.ProjectUserAdditionalPrivilege}.projectMembershipId`]))
+      })
+      .whereNotNull("projectMembershipId");
+
+    await knex.schema.alterTable(TableName.ProjectUserAdditionalPrivilege, (tb) => {
+      tb.uuid("userId").notNullable().alter();
+      tb.string("projectId").notNullable().alter();
+    });
+  }
+
+  if (projectUserAdditionalPrivilegeHasProjectMembershipId) {
+    await knex.schema.alterTable(TableName.ProjectUserAdditionalPrivilege, (tb) => {
+      // DROP AT A LATER TIME
+      // tb.dropColumn("projectMembershipId");
+
+      // ADD ALLOW NULLABLE FOR NOW
+      tb.uuid("projectMembershipId").nullable().alter();
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  // We remove project user additional privileges first, because it may delete records in the database where the project membership is not found.
+  // The project membership won't be found on records created by group members. In those cades we just delete the record and continue.
+  // When the additionl privilege record is deleted, it will cascade delete the access request created by the group member.
+
+  // ---------- PROJECT USER ADDITIONAL PRIVILEGE ------------
+  const hasUserId = await knex.schema.hasColumn(TableName.ProjectUserAdditionalPrivilege, "userId");
+  const hasProjectMembershipId = await knex.schema.hasColumn(
+    TableName.ProjectUserAdditionalPrivilege,
+    "projectMembershipId"
+  );
+
+  // If it doesn't have the userId field, then the up migration has not run
+  if (!hasUserId) {
+    return;
+  }
+
+  await knex.schema.alterTable(TableName.ProjectUserAdditionalPrivilege, (tb) => {
+    if (!hasProjectMembershipId) {
+      tb.uuid("projectMembershipId");
+      tb.foreign("projectMembershipId").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
+    }
+  });
+
+  if (!hasProjectMembershipId) {
+    // First, update records where a matching project membership exists
+    await knex(TableName.ProjectUserAdditionalPrivilege).update({
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      projectMembershipId: knex(TableName.ProjectMembership)
+        .select("id")
+        .where("userId", knex.raw("??", [`${TableName.ProjectUserAdditionalPrivilege}.userId`]))
+    });
+
+    await knex(TableName.AccessApprovalRequest).update({
+      // eslint-disable-next-line
+      // @ts-ignore because generate schema happens after this
+      projectMembershipId: knex(TableName.ProjectMembership)
+        .select("id")
+        .where("userId", knex.raw("??", [`${TableName.SecretApprovalRequest}.userId`]))
+    });
+
+    await knex.schema.alterTable(TableName.ProjectUserAdditionalPrivilege, (tb) => {
+      tb.dropColumn("userId");
+      tb.dropColumn("projectId");
+
+      tb.uuid("projectMembershipId").notNullable().alter();
+    });
+  }
+
+  // Then, delete records where no matching project membership was found
+  await knex(TableName.ProjectUserAdditionalPrivilege).whereNull("projectMembershipId").delete();
+  await knex(TableName.AccessApprovalRequest).whereNull("requestedBy").delete();
+
+  // ---------- ACCESS APPROVAL POLICY APPROVER ------------
+  const hasApproverUserId = await knex.schema.hasColumn(TableName.AccessApprovalPolicyApprover, "approverUserId");
+  const hasApproverId = await knex.schema.hasColumn(TableName.AccessApprovalPolicyApprover, "approverId");
+
+  if (hasApproverUserId) {
+    await knex.schema.alterTable(TableName.AccessApprovalPolicyApprover, (tb) => {
+      if (!hasApproverId) {
+        tb.uuid("approverId");
+        tb.foreign("approverId").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
+      }
+    });
+
+    if (!hasApproverId) {
+      await knex(TableName.AccessApprovalPolicyApprover).update({
+        // eslint-disable-next-line
+        // @ts-ignore because generate schema happens after this
+        approverId: knex(TableName.ProjectMembership)
+          .select("id")
+          .where("userId", knex.raw("??", [`${TableName.AccessApprovalPolicyApprover}.approverUserId`]))
+      });
+      await knex.schema.alterTable(TableName.AccessApprovalPolicyApprover, (tb) => {
+        tb.dropColumn("approverUserId");
+
+        tb.uuid("approverId").notNullable().alter();
+      });
+    }
+
+    // ---------- ACCESS APPROVAL REQUEST ------------
+    const hasAccessApprovalRequestTable = await knex.schema.hasTable(TableName.AccessApprovalRequest);
+    const hasRequestedByUserId = await knex.schema.hasColumn(TableName.AccessApprovalRequest, "requestedByUserId");
+    const hasRequestedBy = await knex.schema.hasColumn(TableName.AccessApprovalRequest, "requestedBy");
+
+    if (hasAccessApprovalRequestTable) {
+      await knex.schema.alterTable(TableName.AccessApprovalRequest, (tb) => {
+        if (!hasRequestedBy) {
+          tb.uuid("requestedBy");
+          tb.foreign("requestedBy").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
+        }
+      });
+
+      // Try to find a project membership based on the AccessApprovalRequest.requestedByUserId and AccessApprovalRequest.policyId(reference to AccessApprovalRequestPolicy).envId(reference to Environment).projectId(reference to Project)
+      // If a project membership is found, set the AccessApprovalRequest.requestedBy to the project membership id
+      // If a project membership is not found, remove the AccessApprovalRequest record
+
+      await knex(TableName.AccessApprovalRequest).update({
+        // eslint-disable-next-line
+        // @ts-ignore because generate schema happens after this
+        requestedBy: knex(TableName.ProjectMembership)
+          .select("id")
+          .where("userId", knex.raw("??", [`${TableName.AccessApprovalRequest}.requestedByUserId`]))
+      });
+
+      // Then, delete records where no matching project membership was found
+      await knex(TableName.AccessApprovalRequest).whereNull("requestedBy").delete();
+
+      await knex.schema.alterTable(TableName.AccessApprovalRequest, (tb) => {
+        if (hasRequestedByUserId) {
+          tb.dropColumn("requestedByUserId");
+        }
+        if (hasRequestedBy) tb.uuid("requestedBy").notNullable().alter();
+      });
+    }
+
+    // ---------- ACCESS APPROVAL REQUEST REVIEWER ------------
+    const hasMemberId = await knex.schema.hasColumn(TableName.AccessApprovalRequestReviewer, "member");
+    const hasReviewerUserId = await knex.schema.hasColumn(TableName.AccessApprovalRequestReviewer, "reviewerUserId");
+
+    if (hasReviewerUserId) {
+      if (!hasMemberId) {
+        await knex.schema.alterTable(TableName.AccessApprovalRequestReviewer, (tb) => {
+          tb.uuid("member");
+          tb.foreign("member").references("id").inTable(TableName.ProjectMembership).onDelete("CASCADE");
+        });
+      }
+      await knex(TableName.AccessApprovalRequestReviewer).update({
+        // eslint-disable-next-line
+        // @ts-ignore because generate schema happens after this
+        member: knex(TableName.ProjectMembership)
+          .select("id")
+          .where("userId", knex.raw("??", [`${TableName.AccessApprovalRequestReviewer}.reviewerUserId`]))
+      });
+      await knex.schema.alterTable(TableName.AccessApprovalRequestReviewer, (tb) => {
+        tb.dropColumn("reviewerUserId");
+
+        tb.uuid("member").notNullable().alter();
+      });
+    }
+  }
+}

backend/src/db/migrations/20240802181855_ca-cert-version.ts

@@ -0,0 +1,117 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+
+export async function up(knex: Knex): Promise<void> {
+  if (await knex.schema.hasTable(TableName.CertificateAuthority)) {
+    const hasActiveCaCertIdColumn = await knex.schema.hasColumn(TableName.CertificateAuthority, "activeCaCertId");
+    if (!hasActiveCaCertIdColumn) {
+      await knex.schema.alterTable(TableName.CertificateAuthority, (t) => {
+        t.uuid("activeCaCertId").nullable();
+        t.foreign("activeCaCertId").references("id").inTable(TableName.CertificateAuthorityCert);
+      });
+
+      await knex.raw(`
+        UPDATE "${TableName.CertificateAuthority}" ca
+        SET "activeCaCertId" = cac.id
+        FROM "${TableName.CertificateAuthorityCert}" cac
+        WHERE ca.id = cac."caId"
+      `);
+    }
+  }
+
+  if (await knex.schema.hasTable(TableName.CertificateAuthorityCert)) {
+    const hasVersionColumn = await knex.schema.hasColumn(TableName.CertificateAuthorityCert, "version");
+    if (!hasVersionColumn) {
+      await knex.schema.alterTable(TableName.CertificateAuthorityCert, (t) => {
+        t.integer("version").nullable();
+        t.dropUnique(["caId"]);
+      });
+
+      await knex(TableName.CertificateAuthorityCert).update({ version: 1 }).whereNull("version");
+
+      await knex.schema.alterTable(TableName.CertificateAuthorityCert, (t) => {
+        t.integer("version").notNullable().alter();
+      });
+    }
+
+    const hasCaSecretIdColumn = await knex.schema.hasColumn(TableName.CertificateAuthorityCert, "caSecretId");
+    if (!hasCaSecretIdColumn) {
+      await knex.schema.alterTable(TableName.CertificateAuthorityCert, (t) => {
+        t.uuid("caSecretId").nullable();
+        t.foreign("caSecretId").references("id").inTable(TableName.CertificateAuthoritySecret).onDelete("CASCADE");
+      });
+
+      await knex.raw(`
+        UPDATE "${TableName.CertificateAuthorityCert}" cert
+        SET "caSecretId" = (
+          SELECT sec.id
+          FROM "${TableName.CertificateAuthoritySecret}" sec
+          WHERE sec."caId" = cert."caId"
+        )
+      `);
+
+      await knex.schema.alterTable(TableName.CertificateAuthorityCert, (t) => {
+        t.uuid("caSecretId").notNullable().alter();
+      });
+    }
+  }
+
+  if (await knex.schema.hasTable(TableName.CertificateAuthoritySecret)) {
+    await knex.schema.alterTable(TableName.CertificateAuthoritySecret, (t) => {
+      t.dropUnique(["caId"]);
+    });
+  }
+
+  if (await knex.schema.hasTable(TableName.Certificate)) {
+    await knex.schema.alterTable(TableName.Certificate, (t) => {
+      t.uuid("caCertId").nullable();
+      t.foreign("caCertId").references("id").inTable(TableName.CertificateAuthorityCert);
+    });
+
+    await knex.raw(`
+        UPDATE "${TableName.Certificate}" cert
+        SET "caCertId" = (
+          SELECT caCert.id
+          FROM "${TableName.CertificateAuthorityCert}" caCert
+          WHERE caCert."caId" = cert."caId"
+        )
+      `);
+
+    await knex.schema.alterTable(TableName.Certificate, (t) => {
+      t.uuid("caCertId").notNullable().alter();
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  if (await knex.schema.hasTable(TableName.CertificateAuthority)) {
+    if (await knex.schema.hasColumn(TableName.CertificateAuthority, "activeCaCertId")) {
+      await knex.schema.alterTable(TableName.CertificateAuthority, (t) => {
+        t.dropColumn("activeCaCertId");
+      });
+    }
+  }
+
+  if (await knex.schema.hasTable(TableName.CertificateAuthorityCert)) {
+    if (await knex.schema.hasColumn(TableName.CertificateAuthorityCert, "version")) {
+      await knex.schema.alterTable(TableName.CertificateAuthorityCert, (t) => {
+        t.dropColumn("version");
+      });
+    }
+
+    if (await knex.schema.hasColumn(TableName.CertificateAuthorityCert, "caSecretId")) {
+      await knex.schema.alterTable(TableName.CertificateAuthorityCert, (t) => {
+        t.dropColumn("caSecretId");
+      });
+    }
+  }
+
+  if (await knex.schema.hasTable(TableName.Certificate)) {
+    if (await knex.schema.hasColumn(TableName.Certificate, "caCertId")) {
+      await knex.schema.alterTable(TableName.Certificate, (t) => {
+        t.dropColumn("caCertId");
+      });
+    }
+  }
+}

backend/src/db/migrations/20240818024923_cert-alerting.ts

@@ -0,0 +1,62 @@
+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.PkiCollection))) {
+    await knex.schema.createTable(TableName.PkiCollection, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.timestamps(true, true, true);
+      t.string("projectId").notNullable();
+      t.foreign("projectId").references("id").inTable(TableName.Project).onDelete("CASCADE");
+      t.string("name").notNullable();
+      t.string("description").notNullable();
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.PkiCollection);
+
+  if (!(await knex.schema.hasTable(TableName.PkiCollectionItem))) {
+    await knex.schema.createTable(TableName.PkiCollectionItem, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.timestamps(true, true, true);
+      t.uuid("pkiCollectionId").notNullable();
+      t.foreign("pkiCollectionId").references("id").inTable(TableName.PkiCollection).onDelete("CASCADE");
+      t.uuid("caId").nullable();
+      t.foreign("caId").references("id").inTable(TableName.CertificateAuthority).onDelete("CASCADE");
+      t.uuid("certId").nullable();
+      t.foreign("certId").references("id").inTable(TableName.Certificate).onDelete("CASCADE");
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.PkiCollectionItem);
+
+  if (!(await knex.schema.hasTable(TableName.PkiAlert))) {
+    await knex.schema.createTable(TableName.PkiAlert, (t) => {
+      t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      t.timestamps(true, true, true);
+      t.string("projectId").notNullable();
+      t.foreign("projectId").references("id").inTable(TableName.Project).onDelete("CASCADE");
+      t.uuid("pkiCollectionId").notNullable();
+      t.foreign("pkiCollectionId").references("id").inTable(TableName.PkiCollection).onDelete("CASCADE");
+      t.string("name").notNullable();
+      t.integer("alertBeforeDays").notNullable();
+      t.string("recipientEmails").notNullable();
+      t.unique(["name", "projectId"]);
+    });
+  }
+
+  await createOnUpdateTrigger(knex, TableName.PkiAlert);
+}
+
+export async function down(knex: Knex): Promise<void> {
+  await knex.schema.dropTableIfExists(TableName.PkiAlert);
+  await dropOnUpdateTrigger(knex, TableName.PkiAlert);
+
+  await knex.schema.dropTableIfExists(TableName.PkiCollectionItem);
+  await dropOnUpdateTrigger(knex, TableName.PkiCollectionItem);
+
+  await knex.schema.dropTableIfExists(TableName.PkiCollection);
+  await dropOnUpdateTrigger(knex, TableName.PkiCollection);
+}

backend/src/db/migrations/20240818184238_add-certificate-template.ts

@@ -0,0 +1,55 @@
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+import { createOnUpdateTrigger, dropOnUpdateTrigger } from "../utils";
+
+export async function up(knex: Knex): Promise<void> {
+  const hasCertificateTemplateTable = await knex.schema.hasTable(TableName.CertificateTemplate);
+  if (!hasCertificateTemplateTable) {
+    await knex.schema.createTable(TableName.CertificateTemplate, (tb) => {
+      tb.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+      tb.uuid("caId").notNullable();
+      tb.foreign("caId").references("id").inTable(TableName.CertificateAuthority).onDelete("CASCADE");
+      tb.uuid("pkiCollectionId");
+      tb.foreign("pkiCollectionId").references("id").inTable(TableName.PkiCollection).onDelete("SET NULL");
+      tb.string("name").notNullable();
+      tb.string("commonName").notNullable();
+      tb.string("subjectAlternativeName").notNullable();
+      tb.string("ttl").notNullable();
+      tb.timestamps(true, true, true);
+    });
+
+    await createOnUpdateTrigger(knex, TableName.CertificateTemplate);
+  }
+
+  const doesCertificateTableHaveTemplateId = await knex.schema.hasColumn(
+    TableName.Certificate,
+    "certificateTemplateId"
+  );
+
+  if (!doesCertificateTableHaveTemplateId) {
+    await knex.schema.alterTable(TableName.Certificate, (tb) => {
+      tb.uuid("certificateTemplateId");
+      tb.foreign("certificateTemplateId").references("id").inTable(TableName.CertificateTemplate).onDelete("SET NULL");
+    });
+  }
+}
+
+export async function down(knex: Knex): Promise<void> {
+  const doesCertificateTableHaveTemplateId = await knex.schema.hasColumn(
+    TableName.Certificate,
+    "certificateTemplateId"
+  );
+
+  if (doesCertificateTableHaveTemplateId) {
+    await knex.schema.alterTable(TableName.Certificate, (t) => {
+      t.dropColumn("certificateTemplateId");
+    });
+  }
+
+  const hasCertificateTemplateTable = await knex.schema.hasTable(TableName.CertificateTemplate);
+  if (hasCertificateTemplateTable) {
+    await knex.schema.dropTable(TableName.CertificateTemplate);
+    await dropOnUpdateTrigger(knex, TableName.CertificateTemplate);
+  }
+}

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

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

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

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

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

@@ -11,12 +11,12 @@ export const AccessApprovalRequestsSchema = z.object({
   id: z.string().uuid(),
   policyId: z.string().uuid(),
   privilegeId: z.string().uuid().nullable().optional(),
-  requestedBy: z.string().uuid(),
   isTemporary: z.boolean(),
   temporaryRange: z.string().nullable().optional(),
   permissions: z.unknown(),
   createdAt: z.date(),
-  updatedAt: z.date()
+  updatedAt: z.date(),
+  requestedByUserId: z.string().uuid()
 });
 
 export type TAccessApprovalRequests = z.infer<typeof AccessApprovalRequestsSchema>;

backend/src/db/schemas/certificate-authorities.ts

@@ -27,7 +27,8 @@ export const CertificateAuthoritiesSchema = z.object({
   maxPathLength: z.number().nullable().optional(),
   keyAlgorithm: z.string(),
   notBefore: z.date().nullable().optional(),
-  notAfter: z.date().nullable().optional()
+  notAfter: z.date().nullable().optional(),
+  activeCaCertId: z.string().uuid().nullable().optional()
 });
 
 export type TCertificateAuthorities = z.infer<typeof CertificateAuthoritiesSchema>;

backend/src/db/schemas/certificate-authority-certs.ts

@@ -15,7 +15,9 @@ export const CertificateAuthorityCertsSchema = z.object({
   updatedAt: z.date(),
   caId: z.string().uuid(),
   encryptedCertificate: zodBuffer,
-  encryptedCertificateChain: zodBuffer
+  encryptedCertificateChain: zodBuffer,
+  version: z.number(),
+  caSecretId: z.string().uuid()
 });
 
 export type TCertificateAuthorityCerts = z.infer<typeof CertificateAuthorityCertsSchema>;

backend/src/db/schemas/certificate-templates.ts

@@ -0,0 +1,24 @@
+// 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 CertificateTemplatesSchema = z.object({
+  id: z.string().uuid(),
+  caId: z.string().uuid(),
+  pkiCollectionId: z.string().uuid().nullable().optional(),
+  name: z.string(),
+  commonName: z.string(),
+  subjectAlternativeName: z.string(),
+  ttl: z.string(),
+  createdAt: z.date(),
+  updatedAt: z.date()
+});
+
+export type TCertificateTemplates = z.infer<typeof CertificateTemplatesSchema>;
+export type TCertificateTemplatesInsert = Omit<z.input<typeof CertificateTemplatesSchema>, TImmutableDBKeys>;
+export type TCertificateTemplatesUpdate = Partial<Omit<z.input<typeof CertificateTemplatesSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/certificates.ts

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

backend/src/db/schemas/index.ts

@@ -14,6 +14,7 @@ export * from "./certificate-authority-crl";
 export * from "./certificate-authority-secret";
 export * from "./certificate-bodies";
 export * from "./certificate-secrets";
+export * from "./certificate-templates";
 export * from "./certificates";
 export * from "./dynamic-secret-leases";
 export * from "./dynamic-secrets";
@@ -52,6 +53,9 @@ export * from "./org-bots";
 export * from "./org-memberships";
 export * from "./org-roles";
 export * from "./organizations";
+export * from "./pki-alerts";
+export * from "./pki-collection-items";
+export * from "./pki-collections";
 export * from "./project-bots";
 export * from "./project-environments";
 export * from "./project-keys";

backend/src/db/schemas/models.ts

@@ -9,6 +9,10 @@ export enum TableName {
   Certificate = "certificates",
   CertificateBody = "certificate_bodies",
   CertificateSecret = "certificate_secrets",
+  CertificateTemplate = "certificate_templates",
+  PkiAlert = "pki_alerts",
+  PkiCollection = "pki_collections",
+  PkiCollectionItem = "pki_collection_items",
   Groups = "groups",
   GroupProjectMembership = "group_project_memberships",
   GroupProjectMembershipRole = "group_project_membership_roles",

backend/src/db/schemas/pki-alerts.ts

@@ -0,0 +1,23 @@
+// 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 PkiAlertsSchema = z.object({
+  id: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  projectId: z.string(),
+  pkiCollectionId: z.string().uuid(),
+  name: z.string(),
+  alertBeforeDays: z.number(),
+  recipientEmails: z.string()
+});
+
+export type TPkiAlerts = z.infer<typeof PkiAlertsSchema>;
+export type TPkiAlertsInsert = Omit<z.input<typeof PkiAlertsSchema>, TImmutableDBKeys>;
+export type TPkiAlertsUpdate = Partial<Omit<z.input<typeof PkiAlertsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/pki-collection-items.ts

@@ -0,0 +1,21 @@
+// 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 PkiCollectionItemsSchema = z.object({
+  id: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  pkiCollectionId: z.string().uuid(),
+  caId: z.string().uuid().nullable().optional(),
+  certId: z.string().uuid().nullable().optional()
+});
+
+export type TPkiCollectionItems = z.infer<typeof PkiCollectionItemsSchema>;
+export type TPkiCollectionItemsInsert = Omit<z.input<typeof PkiCollectionItemsSchema>, TImmutableDBKeys>;
+export type TPkiCollectionItemsUpdate = Partial<Omit<z.input<typeof PkiCollectionItemsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/pki-collections.ts

@@ -0,0 +1,21 @@
+// 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 PkiCollectionsSchema = z.object({
+  id: z.string().uuid(),
+  createdAt: z.date(),
+  updatedAt: z.date(),
+  projectId: z.string(),
+  name: z.string(),
+  description: z.string()
+});
+
+export type TPkiCollections = z.infer<typeof PkiCollectionsSchema>;
+export type TPkiCollectionsInsert = Omit<z.input<typeof PkiCollectionsSchema>, TImmutableDBKeys>;
+export type TPkiCollectionsUpdate = Partial<Omit<z.input<typeof PkiCollectionsSchema>, TImmutableDBKeys>>;

backend/src/db/schemas/project-user-additional-privilege.ts

@@ -10,7 +10,6 @@ import { TImmutableDBKeys } from "./models";
 export const ProjectUserAdditionalPrivilegeSchema = z.object({
   id: z.string().uuid(),
   slug: z.string(),
-  projectMembershipId: z.string().uuid(),
   isTemporary: z.boolean().default(false),
   temporaryMode: z.string().nullable().optional(),
   temporaryRange: z.string().nullable().optional(),
@@ -18,7 +17,9 @@ export const ProjectUserAdditionalPrivilegeSchema = z.object({
   temporaryAccessEndTime: z.date().nullable().optional(),
   permissions: z.unknown(),
   createdAt: z.date(),
-  updatedAt: z.date()
+  updatedAt: z.date(),
+  userId: z.string().uuid(),
+  projectId: z.string()
 });
 
 export type TProjectUserAdditionalPrivilege = z.infer<typeof ProjectUserAdditionalPrivilegeSchema>;

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

@@ -17,11 +17,11 @@ export const registerAccessApprovalPolicyRouter = async (server: FastifyZodProvi
           name: z.string().optional(),
           secretPath: z.string().trim().default("/"),
           environment: z.string(),
-          approvers: z.string().array().min(1),
+          approverUserIds: z.string().array().min(1),
           approvals: z.number().min(1).default(1),
           enforcementLevel: z.nativeEnum(EnforcementLevel).default(EnforcementLevel.Hard)
         })
-        .refine((data) => data.approvals <= data.approvers.length, {
+        .refine((data) => data.approvals <= data.approverUserIds.length, {
           path: ["approvals"],
           message: "The number of approvals should be lower than the number of approvers."
         }),
@@ -56,7 +56,16 @@ export const registerAccessApprovalPolicyRouter = async (server: FastifyZodProvi
       }),
       response: {
         200: z.object({
-          approvals: sapPubSchema.extend({ approvers: z.string().array(), secretPath: z.string().optional() }).array()
+          approvals: sapPubSchema
+            .extend({
+              userApprovers: z
+                .object({
+                  userId: z.string()
+                })
+                .array(),
+              secretPath: z.string().optional().nullable()
+            })
+            .array()
         })
       }
     },
@@ -69,6 +78,7 @@ export const registerAccessApprovalPolicyRouter = async (server: FastifyZodProvi
         actorOrgId: req.permission.orgId,
         projectSlug: req.query.projectSlug
       });
+
       return { approvals };
     }
   });
@@ -117,11 +127,11 @@ export const registerAccessApprovalPolicyRouter = async (server: FastifyZodProvi
             .trim()
             .optional()
             .transform((val) => (val === "" ? "/" : val)),
-          approvers: z.string().array().min(1),
+          approverUserIds: z.string().array().min(1),
           approvals: z.number().min(1).default(1),
           enforcementLevel: z.nativeEnum(EnforcementLevel).default(EnforcementLevel.Hard)
         })
-        .refine((data) => data.approvals <= data.approvers.length, {
+        .refine((data) => data.approvals <= data.approverUserIds.length, {
           path: ["approvals"],
           message: "The number of approvals should be lower than the number of approvers."
         }),

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

@@ -1,10 +1,19 @@
 import { z } from "zod";
 
-import { AccessApprovalRequestsReviewersSchema, AccessApprovalRequestsSchema } from "@app/db/schemas";
+import { AccessApprovalRequestsReviewersSchema, AccessApprovalRequestsSchema, UsersSchema } from "@app/db/schemas";
 import { ApprovalStatus } from "@app/ee/services/access-approval-request/access-approval-request-types";
 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 registerAccessApprovalRequestRouter = async (server: FastifyZodProvider) => {
   server.route({
     url: "/",
@@ -104,10 +113,11 @@ export const registerAccessApprovalRequestRouter = async (server: FastifyZodProv
             }),
             reviewers: z
               .object({
-                member: z.string(),
+                userId: z.string(),
                 status: z.string()
               })
-              .array()
+              .array(),
+            requestedByUser: approvalRequestUser
           }).array()
         })
       }

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

@@ -1,9 +1,9 @@
 import { Knex } from "knex";
 
 import { TDbClient } from "@app/db";
-import { TableName, TAccessApprovalPolicies } from "@app/db/schemas";
+import { AccessApprovalPoliciesSchema, TableName, TAccessApprovalPolicies } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
-import { buildFindFilter, mergeOneToManyRelation, ormify, selectAllTableCols, TFindFilter } from "@app/lib/knex";
+import { buildFindFilter, ormify, selectAllTableCols, sqlNestRelationships, TFindFilter } from "@app/lib/knex";
 
 export type TAccessApprovalPolicyDALFactory = ReturnType<typeof accessApprovalPolicyDALFactory>;
 
@@ -15,12 +15,12 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
       // eslint-disable-next-line
       .where(buildFindFilter(filter))
       .join(TableName.Environment, `${TableName.AccessApprovalPolicy}.envId`, `${TableName.Environment}.id`)
-      .join(
+      .leftJoin(
         TableName.AccessApprovalPolicyApprover,
         `${TableName.AccessApprovalPolicy}.id`,
         `${TableName.AccessApprovalPolicyApprover}.policyId`
       )
-      .select(tx.ref("approverId").withSchema(TableName.AccessApprovalPolicyApprover))
+      .select(tx.ref("approverUserId").withSchema(TableName.AccessApprovalPolicyApprover))
       .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"))
@@ -35,18 +35,30 @@ export const accessApprovalPolicyDALFactory = (db: TDbClient) => {
       const doc = await accessApprovalPolicyFindQuery(tx || db.replicaNode(), {
         [`${TableName.AccessApprovalPolicy}.id` as "id"]: id
       });
-      const formatedDoc = mergeOneToManyRelation(
-        doc,
-        "id",
-        ({ approverId, envId, envName: name, envSlug: slug, ...el }) => ({
-          ...el,
-          envId,
-          environment: { id: envId, name, slug }
+      const formattedDoc = sqlNestRelationships({
+        data: doc,
+        key: "id",
+        parentMapper: (data) => ({
+          environment: {
+            id: data.envId,
+            name: data.envName,
+            slug: data.envSlug
+          },
+          projectId: data.projectId,
+          ...AccessApprovalPoliciesSchema.parse(data)
         }),
-        ({ approverId }) => approverId,
-        "approvers"
-      );
-      return formatedDoc?.[0];
+        childrenMapper: [
+          {
+            key: "approverUserId",
+            label: "userApprovers" as const,
+            mapper: ({ approverUserId }) => ({
+              userId: approverUserId
+            })
+          }
+        ]
+      });
+
+      return formattedDoc?.[0];
     } catch (error) {
       throw new DatabaseError({ error, name: "FindById" });
     }
@@ -55,18 +67,32 @@ 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 formatedDoc = mergeOneToManyRelation(
-        docs,
-        "id",
-        ({ approverId, envId, envName: name, envSlug: slug, ...el }) => ({
-          ...el,
-          envId,
-          environment: { id: envId, name, slug }
+
+      const formattedDocs = sqlNestRelationships({
+        data: docs,
+        key: "id",
+        parentMapper: (data) => ({
+          environment: {
+            id: data.envId,
+            name: data.envName,
+            slug: data.envSlug
+          },
+          projectId: data.projectId,
+          ...AccessApprovalPoliciesSchema.parse(data)
+          // secretPath: data.secretPath || undefined,
         }),
-        ({ approverId }) => approverId,
-        "approvers"
-      );
-      return formatedDoc.map((policy) => ({ ...policy, secretPath: policy.secretPath || undefined }));
+        childrenMapper: [
+          {
+            key: "approverUserId",
+            label: "userApprovers" as const,
+            mapper: ({ approverUserId }) => ({
+              userId: approverUserId
+            })
+          }
+        ]
+      });
+
+      return formattedDocs;
     } catch (error) {
       throw new DatabaseError({ error, name: "Find" });
     }

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

@@ -34,8 +34,7 @@ export const accessApprovalPolicyServiceFactory = ({
   accessApprovalPolicyApproverDAL,
   permissionService,
   projectEnvDAL,
-  projectDAL,
-  projectMembershipDAL
+  projectDAL
 }: TSecretApprovalPolicyServiceFactoryDep) => {
   const createAccessApprovalPolicy = async ({
     name,
@@ -45,7 +44,7 @@ export const accessApprovalPolicyServiceFactory = ({
     secretPath,
     actorAuthMethod,
     approvals,
-    approvers,
+    approverUserIds,
     projectSlug,
     environment,
     enforcementLevel
@@ -53,7 +52,7 @@ export const accessApprovalPolicyServiceFactory = ({
     const project = await projectDAL.findProjectBySlug(projectSlug, actorOrgId);
     if (!project) throw new BadRequestError({ message: "Project not found" });
 
-    if (approvals > approvers.length)
+    if (approvals > approverUserIds.length)
       throw new BadRequestError({ message: "Approvals cannot be greater than approvers" });
 
     const { permission } = await permissionService.getProjectPermission(
@@ -70,15 +69,6 @@ export const accessApprovalPolicyServiceFactory = ({
     const env = await projectEnvDAL.findOne({ slug: environment, projectId: project.id });
     if (!env) throw new BadRequestError({ message: "Environment not found" });
 
-    const secretApprovers = await projectMembershipDAL.find({
-      projectId: project.id,
-      $in: { id: approvers }
-    });
-
-    if (secretApprovers.length !== approvers.length) {
-      throw new BadRequestError({ message: "Approver not found in project" });
-    }
-
     await verifyApprovers({
       projectId: project.id,
       orgId: actorOrgId,
@@ -86,7 +76,7 @@ export const accessApprovalPolicyServiceFactory = ({
       secretPath,
       actorAuthMethod,
       permissionService,
-      userIds: secretApprovers.map((approver) => approver.userId)
+      userIds: approverUserIds
     });
 
     const accessApproval = await accessApprovalPolicyDAL.transaction(async (tx) => {
@@ -101,8 +91,8 @@ export const accessApprovalPolicyServiceFactory = ({
         tx
       );
       await accessApprovalPolicyApproverDAL.insertMany(
-        secretApprovers.map(({ id }) => ({
-          approverId: id,
+        approverUserIds.map((userId) => ({
+          approverUserId: userId,
           policyId: doc.id
         })),
         tx
@@ -138,7 +128,7 @@ export const accessApprovalPolicyServiceFactory = ({
 
   const updateAccessApprovalPolicy = async ({
     policyId,
-    approvers,
+    approverUserIds,
     secretPath,
     name,
     actorId,
@@ -171,16 +161,7 @@ export const accessApprovalPolicyServiceFactory = ({
         },
         tx
       );
-      if (approvers) {
-        // Find the workspace project memberships of the users passed in the approvers array
-        const secretApprovers = await projectMembershipDAL.find(
-          {
-            projectId: accessApprovalPolicy.projectId,
-            $in: { id: approvers }
-          },
-          { tx }
-        );
-
+      if (approverUserIds) {
         await verifyApprovers({
           projectId: accessApprovalPolicy.projectId,
           orgId: actorOrgId,
@@ -188,15 +169,13 @@ export const accessApprovalPolicyServiceFactory = ({
           secretPath: doc.secretPath!,
           actorAuthMethod,
           permissionService,
-          userIds: secretApprovers.map((approver) => approver.userId)
+          userIds: approverUserIds
         });
 
-        if (secretApprovers.length !== approvers.length)
-          throw new BadRequestError({ message: "Approvals cannot be greater than approvers" });
         await accessApprovalPolicyApproverDAL.delete({ policyId: doc.id }, tx);
         await accessApprovalPolicyApproverDAL.insertMany(
-          secretApprovers.map(({ id }) => ({
-            approverId: id,
+          approverUserIds.map((userId) => ({
+            approverUserId: userId,
             policyId: doc.id
           })),
           tx

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

@@ -17,7 +17,7 @@ export type TCreateAccessApprovalPolicy = {
   approvals: number;
   secretPath: string;
   environment: string;
-  approvers: string[];
+  approverUserIds: string[];
   projectSlug: string;
   name: string;
   enforcementLevel: EnforcementLevel;
@@ -26,7 +26,7 @@ export type TCreateAccessApprovalPolicy = {
 export type TUpdateAccessApprovalPolicy = {
   policyId: string;
   approvals?: number;
-  approvers?: string[];
+  approverUserIds?: string[];
   secretPath?: string;
   name?: string;
   enforcementLevel?: EnforcementLevel;

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

@@ -1,7 +1,7 @@
 import { Knex } from "knex";
 
 import { TDbClient } from "@app/db";
-import { AccessApprovalRequestsSchema, TableName, TAccessApprovalRequests } from "@app/db/schemas";
+import { AccessApprovalRequestsSchema, TableName, TAccessApprovalRequests, TUsers } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
 import { ormify, selectAllTableCols, sqlNestRelationships, TFindFilter } from "@app/lib/knex";
 
@@ -40,6 +40,12 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
           `${TableName.AccessApprovalPolicyApprover}.policyId`
         )
 
+        .join<TUsers>(
+          db(TableName.Users).as("requestedByUser"),
+          `${TableName.AccessApprovalRequest}.requestedByUserId`,
+          `requestedByUser.id`
+        )
+
         .leftJoin(TableName.Environment, `${TableName.AccessApprovalPolicy}.envId`, `${TableName.Environment}.id`)
 
         .select(selectAllTableCols(TableName.AccessApprovalRequest))
@@ -52,7 +58,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
           db.ref("envId").withSchema(TableName.AccessApprovalPolicy).as("policyEnvId")
         )
 
-        .select(db.ref("approverId").withSchema(TableName.AccessApprovalPolicyApprover))
+        .select(db.ref("approverUserId").withSchema(TableName.AccessApprovalPolicyApprover))
 
         .select(
           db.ref("projectId").withSchema(TableName.Environment),
@@ -61,15 +67,20 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         )
 
         .select(
-          db.ref("member").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerMemberId"),
+          db.ref("reviewerUserId").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerUserId"),
           db.ref("status").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerStatus")
         )
 
+        // TODO: ADD SUPPORT FOR GROUPS!!!!
         .select(
-          db
-            .ref("projectMembershipId")
-            .withSchema(TableName.ProjectUserAdditionalPrivilege)
-            .as("privilegeMembershipId"),
+          db.ref("email").withSchema("requestedByUser").as("requestedByUserEmail"),
+          db.ref("username").withSchema("requestedByUser").as("requestedByUserUsername"),
+          db.ref("firstName").withSchema("requestedByUser").as("requestedByUserFirstName"),
+          db.ref("lastName").withSchema("requestedByUser").as("requestedByUserLastName"),
+
+          db.ref("userId").withSchema(TableName.ProjectUserAdditionalPrivilege).as("privilegeUserId"),
+          db.ref("projectId").withSchema(TableName.ProjectUserAdditionalPrivilege).as("privilegeMembershipId"),
+
           db.ref("isTemporary").withSchema(TableName.ProjectUserAdditionalPrivilege).as("privilegeIsTemporary"),
           db.ref("temporaryMode").withSchema(TableName.ProjectUserAdditionalPrivilege).as("privilegeTemporaryMode"),
           db.ref("temporaryRange").withSchema(TableName.ProjectUserAdditionalPrivilege).as("privilegeTemporaryRange"),
@@ -102,9 +113,18 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
             enforcementLevel: doc.policyEnforcementLevel,
             envId: doc.policyEnvId
           },
+          requestedByUser: {
+            userId: doc.requestedByUserId,
+            email: doc.requestedByUserEmail,
+            firstName: doc.requestedByUserFirstName,
+            lastName: doc.requestedByUserLastName,
+            username: doc.requestedByUserUsername
+          },
           privilege: doc.privilegeId
             ? {
                 membershipId: doc.privilegeMembershipId,
+                userId: doc.privilegeUserId,
+                projectId: doc.projectId,
                 isTemporary: doc.privilegeIsTemporary,
                 temporaryMode: doc.privilegeTemporaryMode,
                 temporaryRange: doc.privilegeTemporaryRange,
@@ -118,11 +138,11 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         }),
         childrenMapper: [
           {
-            key: "reviewerMemberId",
+            key: "reviewerUserId",
             label: "reviewers" as const,
-            mapper: ({ reviewerMemberId: member, reviewerStatus: status }) => (member ? { member, status } : undefined)
+            mapper: ({ reviewerUserId: userId, reviewerStatus: status }) => (userId ? { userId, status } : undefined)
           },
-          { key: "approverId", label: "approvers" as const, mapper: ({ approverId }) => approverId }
+          { key: "approverUserId", label: "approvers" as const, mapper: ({ approverUserId }) => approverUserId }
         ]
       });
 
@@ -146,30 +166,65 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         `${TableName.AccessApprovalPolicy}.id`
       )
 
+      .join<TUsers>(
+        db(TableName.Users).as("requestedByUser"),
+        `${TableName.AccessApprovalRequest}.requestedByUserId`,
+        `requestedByUser.id`
+      )
+
       .join(
         TableName.AccessApprovalPolicyApprover,
         `${TableName.AccessApprovalPolicy}.id`,
         `${TableName.AccessApprovalPolicyApprover}.policyId`
       )
+
+      .join<TUsers>(
+        db(TableName.Users).as("accessApprovalPolicyApproverUser"),
+        `${TableName.AccessApprovalPolicyApprover}.approverUserId`,
+        "accessApprovalPolicyApproverUser.id"
+      )
+
       .leftJoin(
         TableName.AccessApprovalRequestReviewer,
         `${TableName.AccessApprovalRequest}.id`,
         `${TableName.AccessApprovalRequestReviewer}.requestId`
       )
 
+      .leftJoin<TUsers>(
+        db(TableName.Users).as("accessApprovalReviewerUser"),
+        `${TableName.AccessApprovalRequestReviewer}.reviewerUserId`,
+        `accessApprovalReviewerUser.id`
+      )
+
       .leftJoin(TableName.Environment, `${TableName.AccessApprovalPolicy}.envId`, `${TableName.Environment}.id`)
       .select(selectAllTableCols(TableName.AccessApprovalRequest))
       .select(
-        tx.ref("member").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerMemberId"),
+        tx.ref("approverUserId").withSchema(TableName.AccessApprovalPolicyApprover),
+        tx.ref("email").withSchema("accessApprovalPolicyApproverUser").as("approverEmail"),
+        tx.ref("username").withSchema("accessApprovalPolicyApproverUser").as("approverUsername"),
+        tx.ref("firstName").withSchema("accessApprovalPolicyApproverUser").as("approverFirstName"),
+        tx.ref("lastName").withSchema("accessApprovalPolicyApproverUser").as("approverLastName"),
+        tx.ref("email").withSchema("requestedByUser").as("requestedByUserEmail"),
+        tx.ref("username").withSchema("requestedByUser").as("requestedByUserUsername"),
+        tx.ref("firstName").withSchema("requestedByUser").as("requestedByUserFirstName"),
+        tx.ref("lastName").withSchema("requestedByUser").as("requestedByUserLastName"),
+
+        tx.ref("reviewerUserId").withSchema(TableName.AccessApprovalRequestReviewer),
+
         tx.ref("status").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerStatus"),
+
+        tx.ref("email").withSchema("accessApprovalReviewerUser").as("reviewerEmail"),
+        tx.ref("username").withSchema("accessApprovalReviewerUser").as("reviewerUsername"),
+        tx.ref("firstName").withSchema("accessApprovalReviewerUser").as("reviewerFirstName"),
+        tx.ref("lastName").withSchema("accessApprovalReviewerUser").as("reviewerLastName"),
+
         tx.ref("id").withSchema(TableName.AccessApprovalPolicy).as("policyId"),
         tx.ref("name").withSchema(TableName.AccessApprovalPolicy).as("policyName"),
         tx.ref("projectId").withSchema(TableName.Environment),
         tx.ref("slug").withSchema(TableName.Environment).as("environment"),
         tx.ref("secretPath").withSchema(TableName.AccessApprovalPolicy).as("policySecretPath"),
         tx.ref("enforcementLevel").withSchema(TableName.AccessApprovalPolicy).as("policyEnforcementLevel"),
-        tx.ref("approvals").withSchema(TableName.AccessApprovalPolicy).as("policyApprovals"),
-        tx.ref("approverId").withSchema(TableName.AccessApprovalPolicyApprover)
+        tx.ref("approvals").withSchema(TableName.AccessApprovalPolicy).as("policyApprovals")
       );
 
   const findById = async (id: string, tx?: Knex) => {
@@ -189,15 +244,45 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
             approvals: el.policyApprovals,
             secretPath: el.policySecretPath,
             enforcementLevel: el.policyEnforcementLevel
+          },
+          requestedByUser: {
+            userId: el.requestedByUserId,
+            email: el.requestedByUserEmail,
+            firstName: el.requestedByUserFirstName,
+            lastName: el.requestedByUserLastName,
+            username: el.requestedByUserUsername
           }
         }),
         childrenMapper: [
           {
-            key: "reviewerMemberId",
+            key: "reviewerUserId",
             label: "reviewers" as const,
-            mapper: ({ reviewerMemberId: member, reviewerStatus: status }) => (member ? { member, status } : undefined)
+            mapper: ({
+              reviewerUserId: userId,
+              reviewerStatus: status,
+              reviewerEmail: email,
+              reviewerLastName: lastName,
+              reviewerUsername: username,
+              reviewerFirstName: firstName
+            }) => (userId ? { userId, status, email, firstName, lastName, username } : undefined)
           },
-          { key: "approverId", label: "approvers" as const, mapper: ({ approverId }) => approverId }
+          {
+            key: "approverUserId",
+            label: "approvers" as const,
+            mapper: ({
+              approverUserId,
+              approverEmail: email,
+              approverUsername: username,
+              approverLastName: lastName,
+              approverFirstName: firstName
+            }) => ({
+              userId: approverUserId,
+              email,
+              firstName,
+              lastName,
+              username
+            })
+          }
         ]
       });
       if (!formatedDoc?.[0]) return;
@@ -235,7 +320,7 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         .where(`${TableName.Environment}.projectId`, projectId)
         .select(selectAllTableCols(TableName.AccessApprovalRequest))
         .select(db.ref("status").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerStatus"))
-        .select(db.ref("member").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerMemberId"));
+        .select(db.ref("reviewerUserId").withSchema(TableName.AccessApprovalRequestReviewer).as("reviewerUserId"));
 
       const formattedRequests = sqlNestRelationships({
         data: accessRequests,
@@ -245,9 +330,10 @@ export const accessApprovalRequestDALFactory = (db: TDbClient) => {
         }),
         childrenMapper: [
           {
-            key: "reviewerMemberId",
+            key: "reviewerUserId",
             label: "reviewers" as const,
-            mapper: ({ reviewerMemberId: member, reviewerStatus: status }) => (member ? { member, status } : undefined)
+            mapper: ({ reviewerUserId: reviewer, reviewerStatus: status }) =>
+              reviewer ? { reviewer, status } : undefined
           }
         ]
       });

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

@@ -52,7 +52,10 @@ type TSecretApprovalRequestServiceFactoryDep = {
   >;
   projectMembershipDAL: Pick<TProjectMembershipDALFactory, "findById">;
   smtpService: Pick<TSmtpService, "sendMail">;
-  userDAL: Pick<TUserDALFactory, "findUserByProjectMembershipId" | "findUsersByProjectMembershipIds">;
+  userDAL: Pick<
+    TUserDALFactory,
+    "findUserByProjectMembershipId" | "findUsersByProjectMembershipIds" | "find" | "findById"
+  >;
 };
 
 export type TAccessApprovalRequestServiceFactory = ReturnType<typeof accessApprovalRequestServiceFactory>;
@@ -94,7 +97,7 @@ export const accessApprovalRequestServiceFactory = ({
     );
     if (!membership) throw new UnauthorizedError({ message: "You are not a member of this project" });
 
-    const requestedByUser = await userDAL.findUserByProjectMembershipId(membership.id);
+    const requestedByUser = await userDAL.findById(actorId);
     if (!requestedByUser) throw new UnauthorizedError({ message: "User not found" });
 
     await projectDAL.checkProjectUpgradeStatus(project.id);
@@ -114,13 +117,15 @@ export const accessApprovalRequestServiceFactory = ({
       policyId: policy.id
     });
 
-    const approverUsers = await userDAL.findUsersByProjectMembershipIds(
-      approvers.map((approver) => approver.approverId)
-    );
+    const approverUsers = await userDAL.find({
+      $in: {
+        id: approvers.map((approver) => approver.approverUserId)
+      }
+    });
 
     const duplicateRequests = await accessApprovalRequestDAL.find({
       policyId: policy.id,
-      requestedBy: membership.id,
+      requestedByUserId: actorId,
       permissions: JSON.stringify(requestedPermissions),
       isTemporary
     });
@@ -153,7 +158,7 @@ export const accessApprovalRequestServiceFactory = ({
       const approvalRequest = await accessApprovalRequestDAL.create(
         {
           policyId: policy.id,
-          requestedBy: membership.id,
+          requestedByUserId: actorId,
           temporaryRange: temporaryRange || null,
           permissions: JSON.stringify(requestedPermissions),
           isTemporary
@@ -212,7 +217,7 @@ export const accessApprovalRequestServiceFactory = ({
     let requests = await accessApprovalRequestDAL.findRequestsWithPrivilegeByPolicyIds(policies.map((p) => p.id));
 
     if (authorProjectMembershipId) {
-      requests = requests.filter((request) => request.requestedBy === authorProjectMembershipId);
+      requests = requests.filter((request) => request.requestedByUserId === actorId);
     }
 
     if (envSlug) {
@@ -246,8 +251,8 @@ export const accessApprovalRequestServiceFactory = ({
 
     if (
       !hasRole(ProjectMembershipRole.Admin) &&
-      accessApprovalRequest.requestedBy !== membership.id && // The request wasn't made by the current user
-      !policy.approvers.find((approverId) => approverId === membership.id) // The request isn't performed by an assigned approver
+      accessApprovalRequest.requestedByUserId !== actorId && // The request wasn't made by the current user
+      !policy.approvers.find((approver) => approver.userId === actorId) // The request isn't performed by an assigned approver
     ) {
       throw new UnauthorizedError({ message: "You are not authorized to approve this request" });
     }
@@ -273,7 +278,7 @@ export const accessApprovalRequestServiceFactory = ({
       const review = await accessApprovalRequestReviewerDAL.findOne(
         {
           requestId: accessApprovalRequest.id,
-          member: membership.id
+          reviewerUserId: actorId
         },
         tx
       );
@@ -282,7 +287,7 @@ export const accessApprovalRequestServiceFactory = ({
           {
             status,
             requestId: accessApprovalRequest.id,
-            member: membership.id
+            reviewerUserId: actorId
           },
           tx
         );
@@ -303,7 +308,8 @@ export const accessApprovalRequestServiceFactory = ({
             // Permanent access
             const privilege = await additionalPrivilegeDAL.create(
               {
-                projectMembershipId: accessApprovalRequest.requestedBy,
+                userId: accessApprovalRequest.requestedByUserId,
+                projectId: accessApprovalRequest.projectId,
                 slug: `requested-privilege-${slugify(alphaNumericNanoId(12))}`,
                 permissions: JSON.stringify(accessApprovalRequest.permissions)
               },
@@ -317,7 +323,8 @@ export const accessApprovalRequestServiceFactory = ({
 
             const privilege = await additionalPrivilegeDAL.create(
               {
-                projectMembershipId: accessApprovalRequest.requestedBy,
+                userId: accessApprovalRequest.requestedByUserId,
+                projectId: accessApprovalRequest.projectId,
                 slug: `requested-privilege-${slugify(alphaNumericNanoId(12))}`,
                 permissions: JSON.stringify(accessApprovalRequest.permissions),
                 isTemporary: true,

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

@@ -1,5 +1,6 @@
 import { ForbiddenError } from "@casl/ability";
 
+import { getConfig } from "@app/lib/config/env";
 import { BadRequestError } from "@app/lib/errors";
 
 import { TPermissionServiceFactory } from "../permission/permission-service";
@@ -61,6 +62,10 @@ export const auditLogServiceFactory = ({
   };
 
   const createAuditLog = async (data: TCreateAuditLogDTO) => {
+    const appCfg = getConfig();
+    if (appCfg.DISABLE_AUDIT_LOG_GENERATION) {
+      return;
+    }
     // add all cases in which project id or org id cannot be added
     if (data.event.type !== EventType.LOGIN_IDENTITY_UNIVERSAL_AUTH) {
       if (!data.projectId && !data.orgId) throw new BadRequestError({ message: "Must either project id or org id" });

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

@@ -2,6 +2,7 @@ import { TProjectPermission } from "@app/lib/types";
 import { ActorType } from "@app/services/auth/auth-type";
 import { CaStatus } from "@app/services/certificate-authority/certificate-authority-types";
 import { TIdentityTrustedIp } from "@app/services/identity/identity-types";
+import { PkiItemType } from "@app/services/pki-collection/pki-collection-types";
 
 export type TListProjectAuditLogDTO = {
   auditLogActor?: string;
@@ -130,7 +131,9 @@ export enum EventType {
   GET_CA = "get-certificate-authority",
   UPDATE_CA = "update-certificate-authority",
   DELETE_CA = "delete-certificate-authority",
+  RENEW_CA = "renew-certificate-authority",
   GET_CA_CSR = "get-certificate-authority-csr",
+  GET_CA_CERTS = "get-certificate-authority-certs",
   GET_CA_CERT = "get-certificate-authority-cert",
   SIGN_INTERMEDIATE = "sign-intermediate",
   IMPORT_CA_CERT = "import-certificate-authority-cert",
@@ -141,6 +144,17 @@ export enum EventType {
   DELETE_CERT = "delete-cert",
   REVOKE_CERT = "revoke-cert",
   GET_CERT_BODY = "get-cert-body",
+  CREATE_PKI_ALERT = "create-pki-alert",
+  GET_PKI_ALERT = "get-pki-alert",
+  UPDATE_PKI_ALERT = "update-pki-alert",
+  DELETE_PKI_ALERT = "delete-pki-alert",
+  CREATE_PKI_COLLECTION = "create-pki-collection",
+  GET_PKI_COLLECTION = "get-pki-collection",
+  UPDATE_PKI_COLLECTION = "update-pki-collection",
+  DELETE_PKI_COLLECTION = "delete-pki-collection",
+  GET_PKI_COLLECTION_ITEMS = "get-pki-collection-items",
+  ADD_PKI_COLLECTION_ITEM = "add-pki-collection-item",
+  DELETE_PKI_COLLECTION_ITEM = "delete-pki-collection-item",
   CREATE_KMS = "create-kms",
   UPDATE_KMS = "update-kms",
   DELETE_KMS = "delete-kms",
@@ -148,7 +162,11 @@ export enum EventType {
   UPDATE_PROJECT_KMS = "update-project-kms",
   GET_PROJECT_KMS_BACKUP = "get-project-kms-backup",
   LOAD_PROJECT_KMS_BACKUP = "load-project-kms-backup",
-  ORG_ADMIN_ACCESS_PROJECT = "org-admin-accessed-project"
+  ORG_ADMIN_ACCESS_PROJECT = "org-admin-accessed-project",
+  CREATE_CERTIFICATE_TEMPLATE = "create-certificate-template",
+  UPDATE_CERTIFICATE_TEMPLATE = "update-certificate-template",
+  DELETE_CERTIFICATE_TEMPLATE = "delete-certificate-template",
+  GET_CERTIFICATE_TEMPLATE = "get-certificate-template"
 }
 
 interface UserActorMetadata {
@@ -1096,6 +1114,14 @@ interface DeleteCa {
   };
 }
 
+interface RenewCa {
+  type: EventType.RENEW_CA;
+  metadata: {
+    caId: string;
+    dn: string;
+  };
+}
+
 interface GetCaCsr {
   type: EventType.GET_CA_CSR;
   metadata: {
@@ -1104,6 +1130,14 @@ interface GetCaCsr {
   };
 }
 
+interface GetCaCerts {
+  type: EventType.GET_CA_CERTS;
+  metadata: {
+    caId: string;
+    dn: string;
+  };
+}
+
 interface GetCaCert {
   type: EventType.GET_CA_CERT;
   metadata: {
@@ -1191,6 +1225,95 @@ interface GetCertBody {
   };
 }
 
+interface CreatePkiAlert {
+  type: EventType.CREATE_PKI_ALERT;
+  metadata: {
+    pkiAlertId: string;
+    pkiCollectionId: string;
+    name: string;
+    alertBeforeDays: number;
+    recipientEmails: string;
+  };
+}
+interface GetPkiAlert {
+  type: EventType.GET_PKI_ALERT;
+  metadata: {
+    pkiAlertId: string;
+  };
+}
+
+interface UpdatePkiAlert {
+  type: EventType.UPDATE_PKI_ALERT;
+  metadata: {
+    pkiAlertId: string;
+    pkiCollectionId?: string;
+    name?: string;
+    alertBeforeDays?: number;
+    recipientEmails?: string;
+  };
+}
+interface DeletePkiAlert {
+  type: EventType.DELETE_PKI_ALERT;
+  metadata: {
+    pkiAlertId: string;
+  };
+}
+
+interface CreatePkiCollection {
+  type: EventType.CREATE_PKI_COLLECTION;
+  metadata: {
+    pkiCollectionId: string;
+    name: string;
+  };
+}
+
+interface GetPkiCollection {
+  type: EventType.GET_PKI_COLLECTION;
+  metadata: {
+    pkiCollectionId: string;
+  };
+}
+
+interface UpdatePkiCollection {
+  type: EventType.UPDATE_PKI_COLLECTION;
+  metadata: {
+    pkiCollectionId: string;
+    name?: string;
+  };
+}
+
+interface DeletePkiCollection {
+  type: EventType.DELETE_PKI_COLLECTION;
+  metadata: {
+    pkiCollectionId: string;
+  };
+}
+
+interface GetPkiCollectionItems {
+  type: EventType.GET_PKI_COLLECTION_ITEMS;
+  metadata: {
+    pkiCollectionId: string;
+  };
+}
+
+interface AddPkiCollectionItem {
+  type: EventType.ADD_PKI_COLLECTION_ITEM;
+  metadata: {
+    pkiCollectionItemId: string;
+    pkiCollectionId: string;
+    type: PkiItemType;
+    itemId: string;
+  };
+}
+
+interface DeletePkiCollectionItem {
+  type: EventType.DELETE_PKI_COLLECTION_ITEM;
+  metadata: {
+    pkiCollectionItemId: string;
+    pkiCollectionId: string;
+  };
+}
+
 interface CreateKmsEvent {
   type: EventType.CREATE_KMS;
   metadata: {
@@ -1247,6 +1370,46 @@ interface LoadProjectKmsBackupEvent {
   metadata: Record<string, string>; // no metadata yet
 }
 
+interface CreateCertificateTemplate {
+  type: EventType.CREATE_CERTIFICATE_TEMPLATE;
+  metadata: {
+    certificateTemplateId: string;
+    caId: string;
+    pkiCollectionId?: string;
+    name: string;
+    commonName: string;
+    subjectAlternativeName: string;
+    ttl: string;
+  };
+}
+
+interface GetCertificateTemplate {
+  type: EventType.GET_CERTIFICATE_TEMPLATE;
+  metadata: {
+    certificateTemplateId: string;
+  };
+}
+
+interface UpdateCertificateTemplate {
+  type: EventType.UPDATE_CERTIFICATE_TEMPLATE;
+  metadata: {
+    certificateTemplateId: string;
+    caId: string;
+    pkiCollectionId?: string;
+    name: string;
+    commonName: string;
+    subjectAlternativeName: string;
+    ttl: string;
+  };
+}
+
+interface DeleteCertificateTemplate {
+  type: EventType.DELETE_CERTIFICATE_TEMPLATE;
+  metadata: {
+    certificateTemplateId: string;
+  };
+}
+
 interface OrgAdminAccessProjectEvent {
   type: EventType.ORG_ADMIN_ACCESS_PROJECT;
   metadata: {
@@ -1349,7 +1512,9 @@ export type Event =
   | GetCa
   | UpdateCa
   | DeleteCa
+  | RenewCa
   | GetCaCsr
+  | GetCaCerts
   | GetCaCert
   | SignIntermediate
   | ImportCaCert
@@ -1360,6 +1525,17 @@ export type Event =
   | DeleteCert
   | RevokeCert
   | GetCertBody
+  | CreatePkiAlert
+  | GetPkiAlert
+  | UpdatePkiAlert
+  | DeletePkiAlert
+  | CreatePkiCollection
+  | GetPkiCollection
+  | UpdatePkiCollection
+  | DeletePkiCollection
+  | GetPkiCollectionItems
+  | AddPkiCollectionItem
+  | DeletePkiCollectionItem
   | CreateKmsEvent
   | UpdateKmsEvent
   | DeleteKmsEvent
@@ -1367,4 +1543,8 @@ export type Event =
   | UpdateProjectKmsEvent
   | GetProjectKmsBackupEvent
   | LoadProjectKmsBackupEvent
-  | OrgAdminAccessProjectEvent;
+  | OrgAdminAccessProjectEvent
+  | CreateCertificateTemplate
+  | UpdateCertificateTemplate
+  | GetCertificateTemplate
+  | DeleteCertificateTemplate;

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

@@ -66,6 +66,7 @@ export const permissionDALFactory = (db: TDbClient) => {
           `${TableName.GroupProjectMembershipRole}.projectMembershipId`,
           `${TableName.GroupProjectMembership}.id`
         )
+
         .leftJoin(
           TableName.ProjectRoles,
           `${TableName.GroupProjectMembershipRole}.customRoleId`,
@@ -73,6 +74,12 @@ export const permissionDALFactory = (db: TDbClient) => {
         )
         .join(TableName.Project, `${TableName.GroupProjectMembership}.projectId`, `${TableName.Project}.id`)
         .join(TableName.Organization, `${TableName.Project}.orgId`, `${TableName.Organization}.id`)
+
+        .leftJoin(
+          TableName.ProjectUserAdditionalPrivilege,
+          `${TableName.GroupProjectMembership}.projectId`,
+          `${TableName.Project}.id`
+        )
         .select(selectAllTableCols(TableName.GroupProjectMembershipRole))
         .select(
           db.ref("id").withSchema(TableName.GroupProjectMembership).as("membershipId"),
@@ -81,9 +88,30 @@ export const permissionDALFactory = (db: TDbClient) => {
           db.ref("projectId").withSchema(TableName.GroupProjectMembership),
           db.ref("authEnforced").withSchema(TableName.Organization).as("orgAuthEnforced"),
           db.ref("orgId").withSchema(TableName.Project),
-          db.ref("slug").withSchema(TableName.ProjectRoles).as("customRoleSlug")
-        )
-        .select("permissions");
+          db.ref("slug").withSchema(TableName.ProjectRoles).as("customRoleSlug"),
+
+          db.ref("permissions").withSchema(TableName.ProjectRoles).as("permissions"),
+          // db.ref("permissions").withSchema(TableName.ProjectUserAdditionalPrivilege).as("apPermissions")
+          // Additional Privileges
+          db.ref("id").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApId"),
+          db.ref("permissions").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApPermissions"),
+          db.ref("temporaryMode").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApTemporaryMode"),
+          db.ref("isTemporary").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApIsTemporary"),
+          db.ref("temporaryRange").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApTemporaryRange"),
+
+          db.ref("projectId").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApProjectId"),
+          db.ref("userId").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApUserId"),
+
+          db
+            .ref("temporaryAccessStartTime")
+            .withSchema(TableName.ProjectUserAdditionalPrivilege)
+            .as("userApTemporaryAccessStartTime"),
+          db
+            .ref("temporaryAccessEndTime")
+            .withSchema(TableName.ProjectUserAdditionalPrivilege)
+            .as("userApTemporaryAccessEndTime")
+        );
+      // .select(`${TableName.ProjectRoles}.permissions`);
 
       const docs = await db(TableName.ProjectMembership)
         .join(
@@ -98,12 +126,13 @@ export const permissionDALFactory = (db: TDbClient) => {
         )
         .leftJoin(
           TableName.ProjectUserAdditionalPrivilege,
-          `${TableName.ProjectUserAdditionalPrivilege}.projectMembershipId`,
-          `${TableName.ProjectMembership}.id`
+          `${TableName.ProjectUserAdditionalPrivilege}.projectId`,
+          `${TableName.ProjectMembership}.projectId`
         )
+
         .join(TableName.Project, `${TableName.ProjectMembership}.projectId`, `${TableName.Project}.id`)
         .join(TableName.Organization, `${TableName.Project}.orgId`, `${TableName.Organization}.id`)
-        .where("userId", userId)
+        .where(`${TableName.ProjectMembership}.userId`, userId)
         .where(`${TableName.ProjectMembership}.projectId`, projectId)
         .select(selectAllTableCols(TableName.ProjectUserMembershipRole))
         .select(
@@ -120,6 +149,10 @@ export const permissionDALFactory = (db: TDbClient) => {
           db.ref("temporaryMode").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApTemporaryMode"),
           db.ref("isTemporary").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApIsTemporary"),
           db.ref("temporaryRange").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApTemporaryRange"),
+
+          db.ref("projectId").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApProjectId"),
+          db.ref("userId").withSchema(TableName.ProjectUserAdditionalPrivilege).as("userApUserId"),
+
           db
             .ref("temporaryAccessStartTime")
             .withSchema(TableName.ProjectUserAdditionalPrivilege)
@@ -198,6 +231,31 @@ export const permissionDALFactory = (db: TDbClient) => {
                     permissions: z.unknown(),
                     customRoleSlug: z.string().optional().nullable()
                   }).parse(data)
+              },
+              {
+                key: "userApId",
+                label: "additionalPrivileges" as const,
+                mapper: ({
+                  userApId,
+                  userApProjectId,
+                  userApUserId,
+                  userApPermissions,
+                  userApIsTemporary,
+                  userApTemporaryMode,
+                  userApTemporaryRange,
+                  userApTemporaryAccessEndTime,
+                  userApTemporaryAccessStartTime
+                }) => ({
+                  id: userApId,
+                  userId: userApUserId,
+                  projectId: userApProjectId,
+                  permissions: userApPermissions,
+                  temporaryRange: userApTemporaryRange,
+                  temporaryMode: userApTemporaryMode,
+                  temporaryAccessEndTime: userApTemporaryAccessEndTime,
+                  temporaryAccessStartTime: userApTemporaryAccessStartTime,
+                  isTemporary: userApIsTemporary
+                })
               }
             ]
           })
@@ -218,15 +276,24 @@ export const permissionDALFactory = (db: TDbClient) => {
             !isTemporary || (isTemporary && temporaryAccessEndTime && new Date() < temporaryAccessEndTime)
         ) ?? [];
 
-      const activeAdditionalPrivileges = permission?.[0]?.additionalPrivileges?.filter(
-        ({ isTemporary, temporaryAccessEndTime }) =>
-          !isTemporary || (isTemporary && temporaryAccessEndTime && new Date() < temporaryAccessEndTime)
-      );
+      const activeAdditionalPrivileges =
+        permission?.[0]?.additionalPrivileges?.filter(
+          ({ isTemporary, temporaryAccessEndTime }) =>
+            !isTemporary || (isTemporary && temporaryAccessEndTime && new Date() < temporaryAccessEndTime)
+        ) ?? [];
+
+      const activeGroupAdditionalPrivileges =
+        groupPermission?.[0]?.additionalPrivileges?.filter(
+          ({ isTemporary, temporaryAccessEndTime, userId: apUserId, projectId: apProjectId }) =>
+            apProjectId === projectId &&
+            apUserId === userId &&
+            (!isTemporary || (isTemporary && temporaryAccessEndTime && new Date() < temporaryAccessEndTime))
+        ) ?? [];
 
       return {
         ...(permission[0] || groupPermission[0]),
         roles: [...activeRoles, ...activeGroupRoles],
-        additionalPrivileges: activeAdditionalPrivileges
+        additionalPrivileges: [...activeAdditionalPrivileges, ...activeGroupAdditionalPrivileges]
       };
     } catch (error) {
       throw new DatabaseError({ error, name: "GetProjectPermission" });

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

@@ -30,6 +30,9 @@ export enum ProjectPermissionSub {
   Identity = "identity",
   CertificateAuthorities = "certificate-authorities",
   Certificates = "certificates",
+  CertificateTemplates = "certificate-templates",
+  PkiAlerts = "pki-alerts",
+  PkiCollections = "pki-collections",
   Kms = "kms"
 }
 
@@ -63,6 +66,9 @@ export type ProjectPermissionSet =
   | [ProjectPermissionActions, ProjectPermissionSub.Identity]
   | [ProjectPermissionActions, ProjectPermissionSub.CertificateAuthorities]
   | [ProjectPermissionActions, ProjectPermissionSub.Certificates]
+  | [ProjectPermissionActions, ProjectPermissionSub.CertificateTemplates]
+  | [ProjectPermissionActions, ProjectPermissionSub.PkiAlerts]
+  | [ProjectPermissionActions, ProjectPermissionSub.PkiCollections]
   | [ProjectPermissionActions.Delete, ProjectPermissionSub.Project]
   | [ProjectPermissionActions.Edit, ProjectPermissionSub.Project]
   | [ProjectPermissionActions.Read, ProjectPermissionSub.SecretRollback]
@@ -161,6 +167,21 @@ const buildAdminPermissionRules = () => {
   can(ProjectPermissionActions.Edit, ProjectPermissionSub.Certificates);
   can(ProjectPermissionActions.Delete, ProjectPermissionSub.Certificates);
 
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.CertificateTemplates);
+  can(ProjectPermissionActions.Create, ProjectPermissionSub.CertificateTemplates);
+  can(ProjectPermissionActions.Edit, ProjectPermissionSub.CertificateTemplates);
+  can(ProjectPermissionActions.Delete, ProjectPermissionSub.CertificateTemplates);
+
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.PkiAlerts);
+  can(ProjectPermissionActions.Create, ProjectPermissionSub.PkiAlerts);
+  can(ProjectPermissionActions.Edit, ProjectPermissionSub.PkiAlerts);
+  can(ProjectPermissionActions.Delete, ProjectPermissionSub.PkiAlerts);
+
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.PkiCollections);
+  can(ProjectPermissionActions.Create, ProjectPermissionSub.PkiCollections);
+  can(ProjectPermissionActions.Edit, ProjectPermissionSub.PkiCollections);
+  can(ProjectPermissionActions.Delete, ProjectPermissionSub.PkiCollections);
+
   can(ProjectPermissionActions.Edit, ProjectPermissionSub.Project);
   can(ProjectPermissionActions.Delete, ProjectPermissionSub.Project);
 
@@ -237,6 +258,11 @@ const buildMemberPermissionRules = () => {
   can(ProjectPermissionActions.Edit, ProjectPermissionSub.Certificates);
   can(ProjectPermissionActions.Delete, ProjectPermissionSub.Certificates);
 
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.CertificateTemplates);
+
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.PkiAlerts);
+  can(ProjectPermissionActions.Read, ProjectPermissionSub.PkiCollections);
+
   return rules;
 };
 

backend/src/ee/services/project-user-additional-privilege/project-user-additional-privilege-service.ts

@@ -18,7 +18,7 @@ import {
 
 type TProjectUserAdditionalPrivilegeServiceFactoryDep = {
   projectUserAdditionalPrivilegeDAL: TProjectUserAdditionalPrivilegeDALFactory;
-  projectMembershipDAL: Pick<TProjectMembershipDALFactory, "findById">;
+  projectMembershipDAL: Pick<TProjectMembershipDALFactory, "findById" | "findOne">;
   permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
 };
 
@@ -53,12 +53,17 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     );
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.Member);
 
-    const existingSlug = await projectUserAdditionalPrivilegeDAL.findOne({ slug, projectMembershipId });
+    const existingSlug = await projectUserAdditionalPrivilegeDAL.findOne({
+      slug,
+      projectId: projectMembership.projectId,
+      userId: projectMembership.userId
+    });
     if (existingSlug) throw new BadRequestError({ message: "Additional privilege of provided slug exist" });
 
     if (!dto.isTemporary) {
       const additionalPrivilege = await projectUserAdditionalPrivilegeDAL.create({
-        projectMembershipId,
+        userId: projectMembership.userId,
+        projectId: projectMembership.projectId,
         slug,
         permissions: customPermission
       });
@@ -67,7 +72,8 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
 
     const relativeTempAllocatedTimeInMs = ms(dto.temporaryRange);
     const additionalPrivilege = await projectUserAdditionalPrivilegeDAL.create({
-      projectMembershipId,
+      projectId: projectMembership.projectId,
+      userId: projectMembership.userId,
       slug,
       permissions: customPermission,
       isTemporary: true,
@@ -90,7 +96,11 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     const userPrivilege = await projectUserAdditionalPrivilegeDAL.findById(privilegeId);
     if (!userPrivilege) throw new BadRequestError({ message: "User additional privilege not found" });
 
-    const projectMembership = await projectMembershipDAL.findById(userPrivilege.projectMembershipId);
+    const projectMembership = await projectMembershipDAL.findOne({
+      userId: userPrivilege.userId,
+      projectId: userPrivilege.projectId
+    });
+
     if (!projectMembership) throw new BadRequestError({ message: "Project membership not found" });
 
     const { permission } = await permissionService.getProjectPermission(
@@ -105,7 +115,8 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     if (dto?.slug) {
       const existingSlug = await projectUserAdditionalPrivilegeDAL.findOne({
         slug: dto.slug,
-        projectMembershipId: projectMembership.id
+        userId: projectMembership.id,
+        projectId: projectMembership.projectId
       });
       if (existingSlug && existingSlug.id !== userPrivilege.id)
         throw new BadRequestError({ message: "Additional privilege of provided slug exist" });
@@ -138,7 +149,10 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     const userPrivilege = await projectUserAdditionalPrivilegeDAL.findById(privilegeId);
     if (!userPrivilege) throw new BadRequestError({ message: "User additional privilege not found" });
 
-    const projectMembership = await projectMembershipDAL.findById(userPrivilege.projectMembershipId);
+    const projectMembership = await projectMembershipDAL.findOne({
+      userId: userPrivilege.userId,
+      projectId: userPrivilege.projectId
+    });
     if (!projectMembership) throw new BadRequestError({ message: "Project membership not found" });
 
     const { permission } = await permissionService.getProjectPermission(
@@ -164,7 +178,10 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     const userPrivilege = await projectUserAdditionalPrivilegeDAL.findById(privilegeId);
     if (!userPrivilege) throw new BadRequestError({ message: "User additional privilege not found" });
 
-    const projectMembership = await projectMembershipDAL.findById(userPrivilege.projectMembershipId);
+    const projectMembership = await projectMembershipDAL.findOne({
+      userId: userPrivilege.userId,
+      projectId: userPrivilege.projectId
+    });
     if (!projectMembership) throw new BadRequestError({ message: "Project membership not found" });
 
     const { permission } = await permissionService.getProjectPermission(
@@ -198,7 +215,10 @@ export const projectUserAdditionalPrivilegeServiceFactory = ({
     );
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.Member);
 
-    const userPrivileges = await projectUserAdditionalPrivilegeDAL.find({ projectMembershipId });
+    const userPrivileges = await projectUserAdditionalPrivilegeDAL.find({
+      userId: projectMembership.userId,
+      projectId: projectMembership.projectId
+    });
     return userPrivileges;
   };
 

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

@@ -31,6 +31,7 @@ import { UserAliasType } from "@app/services/user-alias/user-alias-types";
 import { TLicenseServiceFactory } from "../license/license-service";
 import { OrgPermissionActions, OrgPermissionSubjects } from "../permission/org-permission";
 import { TPermissionServiceFactory } from "../permission/permission-service";
+import { TProjectUserAdditionalPrivilegeDALFactory } from "../project-user-additional-privilege/project-user-additional-privilege-dal";
 import {
   buildScimGroup,
   buildScimGroupList,
@@ -93,6 +94,7 @@ type TScimServiceFactoryDep = {
   licenseService: Pick<TLicenseServiceFactory, "getPlan" | "updateSubscriptionOrgMemberCount">;
   permissionService: Pick<TPermissionServiceFactory, "getOrgPermission">;
   smtpService: Pick<TSmtpService, "sendMail">;
+  projectUserAdditionalPrivilegeDAL: Pick<TProjectUserAdditionalPrivilegeDALFactory, "delete">;
 };
 
 export type TScimServiceFactory = ReturnType<typeof scimServiceFactory>;
@@ -112,6 +114,7 @@ export const scimServiceFactory = ({
   projectKeyDAL,
   projectBotDAL,
   permissionService,
+  projectUserAdditionalPrivilegeDAL,
   smtpService
 }: TScimServiceFactoryDep) => {
   const createScimToken = async ({
@@ -558,6 +561,7 @@ export const scimServiceFactory = ({
       orgId: membership.orgId,
       orgDAL,
       projectMembershipDAL,
+      projectUserAdditionalPrivilegeDAL,
       projectKeyDAL,
       userAliasDAL,
       licenseService

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

@@ -8,6 +8,7 @@ import { removeTrailingSlash } from "@app/lib/fn";
 import { containsGlobPatterns } from "@app/lib/picomatch";
 import { TProjectEnvDALFactory } from "@app/services/project-env/project-env-dal";
 
+import { TLicenseServiceFactory } from "../license/license-service";
 import { TSecretApprovalPolicyApproverDALFactory } from "./secret-approval-policy-approver-dal";
 import { TSecretApprovalPolicyDALFactory } from "./secret-approval-policy-dal";
 import {
@@ -28,6 +29,7 @@ type TSecretApprovalPolicyServiceFactoryDep = {
   secretApprovalPolicyDAL: TSecretApprovalPolicyDALFactory;
   projectEnvDAL: Pick<TProjectEnvDALFactory, "findOne">;
   secretApprovalPolicyApproverDAL: TSecretApprovalPolicyApproverDALFactory;
+  licenseService: Pick<TLicenseServiceFactory, "getPlan">;
 };
 
 export type TSecretApprovalPolicyServiceFactory = ReturnType<typeof secretApprovalPolicyServiceFactory>;
@@ -36,7 +38,8 @@ export const secretApprovalPolicyServiceFactory = ({
   secretApprovalPolicyDAL,
   permissionService,
   secretApprovalPolicyApproverDAL,
-  projectEnvDAL
+  projectEnvDAL,
+  licenseService
 }: TSecretApprovalPolicyServiceFactoryDep) => {
   const createSecretApprovalPolicy = async ({
     name,
@@ -65,6 +68,15 @@ export const secretApprovalPolicyServiceFactory = ({
       ProjectPermissionActions.Create,
       ProjectPermissionSub.SecretApproval
     );
+
+    const plan = await licenseService.getPlan(actorOrgId);
+    if (!plan.secretApproval) {
+      throw new BadRequestError({
+        message:
+          "Failed to create secret approval policy due to plan restriction. Upgrade plan to create secret approval policy."
+      });
+    }
+
     const env = await projectEnvDAL.findOne({ slug: environment, projectId });
     if (!env) throw new BadRequestError({ message: "Environment not found" });
 
@@ -115,6 +127,14 @@ export const secretApprovalPolicyServiceFactory = ({
     );
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.SecretApproval);
 
+    const plan = await licenseService.getPlan(actorOrgId);
+    if (!plan.secretApproval) {
+      throw new BadRequestError({
+        message:
+          "Failed to update secret approval policy due to plan restriction. Upgrade plan to update secret approval policy."
+      });
+    }
+
     const updatedSap = await secretApprovalPolicyDAL.transaction(async (tx) => {
       const doc = await secretApprovalPolicyDAL.updateById(
         secretApprovalPolicy.id,
@@ -167,6 +187,14 @@ export const secretApprovalPolicyServiceFactory = ({
       ProjectPermissionSub.SecretApproval
     );
 
+    const plan = await licenseService.getPlan(actorOrgId);
+    if (!plan.secretApproval) {
+      throw new BadRequestError({
+        message:
+          "Failed to update secret approval policy due to plan restriction. Upgrade plan to update secret approval policy."
+      });
+    }
+
     await secretApprovalPolicyDAL.deleteById(secretPolicyId);
     return sapPolicy;
   };

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

@@ -50,6 +50,7 @@ import { TSecretVersionV2TagDALFactory } from "@app/services/secret-v2-bridge/se
 import { SmtpTemplates, TSmtpService } from "@app/services/smtp/smtp-service";
 import { TUserDALFactory } from "@app/services/user/user-dal";
 
+import { TLicenseServiceFactory } from "../license/license-service";
 import { TPermissionServiceFactory } from "../permission/permission-service";
 import { ProjectPermissionActions, ProjectPermissionSub } from "../permission/project-permission";
 import { TSecretSnapshotServiceFactory } from "../secret-snapshot/secret-snapshot-service";
@@ -97,6 +98,7 @@ type TSecretApprovalRequestServiceFactoryDep = {
   >;
   secretVersionV2BridgeDAL: Pick<TSecretVersionV2DALFactory, "insertMany" | "findLatestVersionMany">;
   secretVersionTagV2BridgeDAL: Pick<TSecretVersionV2TagDALFactory, "insertMany">;
+  licenseService: Pick<TLicenseServiceFactory, "getPlan">;
 };
 
 export type TSecretApprovalRequestServiceFactory = ReturnType<typeof secretApprovalRequestServiceFactory>;
@@ -122,7 +124,8 @@ export const secretApprovalRequestServiceFactory = ({
   kmsService,
   secretV2BridgeDAL,
   secretVersionV2BridgeDAL,
-  secretVersionTagV2BridgeDAL
+  secretVersionTagV2BridgeDAL,
+  licenseService
 }: TSecretApprovalRequestServiceFactoryDep) => {
   const requestCount = async ({ projectId, actor, actorId, actorOrgId, actorAuthMethod }: TApprovalRequestCountDTO) => {
     if (actor === ActorType.SERVICE) throw new BadRequestError({ message: "Cannot use service token" });
@@ -295,6 +298,14 @@ export const secretApprovalRequestServiceFactory = ({
     if (!secretApprovalRequest) throw new BadRequestError({ message: "Secret approval request not found" });
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
+    const plan = await licenseService.getPlan(actorOrgId);
+    if (!plan.secretApproval) {
+      throw new BadRequestError({
+        message:
+          "Failed to review secret approval request due to plan restriction. Upgrade plan to review secret approval request."
+      });
+    }
+
     const { policy } = secretApprovalRequest;
     const { hasRole } = await permissionService.getProjectPermission(
       ActorType.USER,
@@ -345,6 +356,14 @@ export const secretApprovalRequestServiceFactory = ({
     if (!secretApprovalRequest) throw new BadRequestError({ message: "Secret approval request not found" });
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
+    const plan = await licenseService.getPlan(actorOrgId);
+    if (!plan.secretApproval) {
+      throw new BadRequestError({
+        message:
+          "Failed to update secret approval request due to plan restriction. Upgrade plan to update secret approval request."
+      });
+    }
+
     const { policy } = secretApprovalRequest;
     const { hasRole } = await permissionService.getProjectPermission(
       ActorType.USER,
@@ -386,6 +405,14 @@ export const secretApprovalRequestServiceFactory = ({
     if (!secretApprovalRequest) throw new BadRequestError({ message: "Secret approval request not found" });
     if (actor !== ActorType.USER) throw new BadRequestError({ message: "Must be a user" });
 
+    const plan = await licenseService.getPlan(actorOrgId);
+    if (!plan.secretApproval) {
+      throw new BadRequestError({
+        message:
+          "Failed to merge secret approval request due to plan restriction. Upgrade plan to merge secret approval request."
+      });
+    }
+
     const { policy, folderId, projectId } = secretApprovalRequest;
     const { hasRole } = await permissionService.getProjectPermission(
       ActorType.USER,

backend/src/keystore/keystore.ts

@@ -5,17 +5,26 @@ import { Redlock, Settings } from "@app/lib/red-lock";
 export type TKeyStoreFactory = ReturnType<typeof keyStoreFactory>;
 
 // all the key prefixes used must be set here to avoid conflict
-export enum KeyStorePrefixes {
-  SecretReplication = "secret-replication-import-lock",
-  KmsProjectDataKeyCreation = "kms-project-data-key-creation-lock",
-  KmsProjectKeyCreation = "kms-project-key-creation-lock",
-  WaitUntilReadyKmsProjectDataKeyCreation = "wait-until-ready-kms-project-data-key-creation-",
-  WaitUntilReadyKmsProjectKeyCreation = "wait-until-ready-kms-project-key-creation-",
-  KmsOrgKeyCreation = "kms-org-key-creation-lock",
-  KmsOrgDataKeyCreation = "kms-org-data-key-creation-lock",
-  WaitUntilReadyKmsOrgKeyCreation = "wait-until-ready-kms-org-key-creation-",
-  WaitUntilReadyKmsOrgDataKeyCreation = "wait-until-ready-kms-org-data-key-creation-"
-}
+export const KeyStorePrefixes = {
+  SecretReplication: "secret-replication-import-lock",
+  KmsProjectDataKeyCreation: "kms-project-data-key-creation-lock",
+  KmsProjectKeyCreation: "kms-project-key-creation-lock",
+  WaitUntilReadyKmsProjectDataKeyCreation: "wait-until-ready-kms-project-data-key-creation-",
+  WaitUntilReadyKmsProjectKeyCreation: "wait-until-ready-kms-project-key-creation-",
+  KmsOrgKeyCreation: "kms-org-key-creation-lock",
+  KmsOrgDataKeyCreation: "kms-org-data-key-creation-lock",
+  WaitUntilReadyKmsOrgKeyCreation: "wait-until-ready-kms-org-key-creation-",
+  WaitUntilReadyKmsOrgDataKeyCreation: "wait-until-ready-kms-org-data-key-creation-",
+
+  SyncSecretIntegrationLock: (projectId: string, environmentSlug: string, secretPath: string) =>
+    `sync-integration-mutex-${projectId}-${environmentSlug}-${secretPath}` as const,
+  SyncSecretIntegrationLastRunTimestamp: (projectId: string, environmentSlug: string, secretPath: string) =>
+    `sync-integration-last-run-${projectId}-${environmentSlug}-${secretPath}` as const
+};
+
+export const KeyStoreTtls = {
+  SetSyncSecretIntegrationLastRunTimestampInSeconds: 10
+};
 
 type TWaitTillReady = {
   key: string;
@@ -37,10 +46,10 @@ export const keyStoreFactory = (redisUrl: string) => {
 
   const setItemWithExpiry = async (
     key: string,
-    exp: number | string,
+    expiryInSeconds: number | string,
     value: string | number | Buffer,
     prefix?: string
-  ) => redis.set(prefix ? `${prefix}:${key}` : key, value, "EX", exp);
+  ) => redis.set(prefix ? `${prefix}:${key}` : key, value, "EX", expiryInSeconds);
 
   const deleteItem = async (key: string) => redis.del(key);
 

backend/src/lib/api-docs/constants.ts

@@ -1049,12 +1049,27 @@ export const CERTIFICATE_AUTHORITIES = {
     caId: "The ID of the CA to generate CSR from",
     csr: "The generated CSR from the CA"
   },
+  RENEW_CA_CERT: {
+    caId: "The ID of the CA to renew the CA certificate for",
+    type: "The type of behavior to use for the renewal operation. Currently Infisical is only able to renew a CA certificate with the same key pair.",
+    notAfter: "The expiry date and time for the renewed CA certificate in YYYY-MM-DDTHH:mm:ss.sssZ format",
+    certificate: "The renewed CA certificate body",
+    certificateChain: "The certificate chain of the CA",
+    serialNumber: "The serial number of the renewed CA certificate"
+  },
   GET_CERT: {
     caId: "The ID of the CA to get the certificate body and certificate chain from",
     certificate: "The certificate body of the CA",
     certificateChain: "The certificate chain of the CA",
     serialNumber: "The serial number of the CA certificate"
   },
+  GET_CA_CERTS: {
+    caId: "The ID of the CA to get the CA certificates for",
+    certificate: "The certificate body of the CA certificate",
+    certificateChain: "The certificate chain of the CA certificate",
+    serialNumber: "The serial number of the CA certificate",
+    version: "The version of the CA certificate. The version is incremented for each CA renewal operation."
+  },
   SIGN_INTERMEDIATE: {
     caId: "The ID of the CA to sign the intermediate certificate with",
     csr: "The pem-encoded CSR to sign with the CA",
@@ -1074,6 +1089,8 @@ export const CERTIFICATE_AUTHORITIES = {
   },
   ISSUE_CERT: {
     caId: "The ID of the CA to issue the certificate from",
+    certificateTemplateId: "The ID of the certificate template to issue the certificate from",
+    pkiCollectionId: "The ID of the PKI collection to add the certificate to",
     friendlyName: "A friendly name for the certificate",
     commonName: "The common name (CN) for the certificate",
     altNames:
@@ -1089,6 +1106,7 @@ export const CERTIFICATE_AUTHORITIES = {
   },
   SIGN_CERT: {
     caId: "The ID of the CA to issue the certificate from",
+    pkiCollectionId: "The ID of the PKI collection to add the certificate to",
     csr: "The pem-encoded CSR to sign with the CA to be used for certificate issuance",
     friendlyName: "A friendly name for the certificate",
     commonName: "The common name (CN) for the certificate",
@@ -1130,6 +1148,91 @@ export const CERTIFICATES = {
   }
 };
 
+export const CERTIFICATE_TEMPLATES = {
+  CREATE: {
+    caId: "The ID of the certificate authority to associate the template with",
+    pkiCollectionId: "The ID of the PKI collection to bind to the template",
+    name: "The name of the template",
+    commonName: "The regular expression string to use for validating common names",
+    subjectAlternativeName: "The regular expression string to use for validating subject alternative names",
+    ttl: "The max TTL for the template"
+  },
+  GET: {
+    certificateTemplateId: "The ID of the certificate template to get"
+  },
+  UPDATE: {
+    certificateTemplateId: "The ID of the certificate template to update",
+    caId: "The ID of the certificate authority to update the association with the template",
+    pkiCollectionId: "The ID of the PKI collection to update the binding to the template",
+    name: "The updated name of the template",
+    commonName: "The updated regular expression string for validating common names",
+    subjectAlternativeName: "The updated regular expression string for validating subject alternative names",
+    ttl: "The updated max TTL for the template"
+  },
+  DELETE: {
+    certificateTemplateId: "The ID of the certificate template to delete"
+  }
+};
+
+export const ALERTS = {
+  CREATE: {
+    projectId: "The ID of the project to create the alert in",
+    pkiCollectionId: "The ID of the PKI collection to bind to the alert",
+    name: "The name of the alert",
+    alertBeforeDays: "The number of days before the certificate expires to trigger the alert",
+    emails: "The email addresses to send the alert email to"
+  },
+  GET: {
+    alertId: "The ID of the alert to get"
+  },
+  UPDATE: {
+    alertId: "The ID of the alert to update",
+    name: "The name of the alert to update to",
+    alertBeforeDays: "The number of days before the certificate expires to trigger the alert to update to",
+    pkiCollectionId: "The ID of the PKI collection to bind to the alert to update to",
+    emails: "The email addresses to send the alert email to update to"
+  },
+  DELETE: {
+    alertId: "The ID of the alert to delete"
+  }
+};
+
+export const PKI_COLLECTIONS = {
+  CREATE: {
+    projectId: "The ID of the project to create the PKI collection in",
+    name: "The name of the PKI collection",
+    description: "A description for the PKI collection"
+  },
+  GET: {
+    collectionId: "The ID of the PKI collection to get"
+  },
+  UPDATE: {
+    collectionId: "The ID of the PKI collection to update",
+    name: "The name of the PKI collection to update to",
+    description: "The description for the PKI collection to update to"
+  },
+  DELETE: {
+    collectionId: "The ID of the PKI collection to delete"
+  },
+  LIST_ITEMS: {
+    collectionId: "The ID of the PKI collection to list items from",
+    type: "The type of the PKI collection item to list",
+    offset: "The offset to start from",
+    limit: "The number of items to return"
+  },
+  ADD_ITEM: {
+    collectionId: "The ID of the PKI collection to add the item to",
+    type: "The type of the PKI collection item to add",
+    itemId: "The resource ID of the PKI collection item to add"
+  },
+  DELETE_ITEM: {
+    collectionId: "The ID of the PKI collection to delete the item from",
+    collectionItemId: "The ID of the PKI collection item to delete",
+    type: "The type of the deleted PKI collection item",
+    itemId: "The resource ID of the deleted PKI collection item"
+  }
+};
+
 export const PROJECT_ROLE = {
   CREATE: {
     projectSlug: "Slug of the project to create the role for.",

backend/src/lib/config/env.ts

@@ -140,7 +140,8 @@ const envSchema = z
     MAINTENANCE_MODE: zodStrBool.default("false"),
     CAPTCHA_SECRET: zpStr(z.string().optional()),
     PLAIN_API_KEY: zpStr(z.string().optional()),
-    PLAIN_WISH_LABEL_IDS: zpStr(z.string().optional())
+    PLAIN_WISH_LABEL_IDS: zpStr(z.string().optional()),
+    DISABLE_AUDIT_LOG_GENERATION: zodStrBool.default("false")
   })
   .transform((data) => ({
     ...data,

backend/src/lib/fn/dates.ts

@@ -1,2 +1,8 @@
 export const getLastMidnightDateISO = (last = 1) =>
   `${new Date(new Date().setDate(new Date().getDate() - last)).toISOString().slice(0, 10)}T00:00:00Z`;
+
+export const getTimeDifferenceInSeconds = (lhsTimestamp: string, rhsTimestamp: string) => {
+  const lhs = new Date(lhsTimestamp);
+  const rhs = new Date(rhsTimestamp);
+  return Math.floor((Number(lhs) - Number(rhs)) / 1000);
+};

backend/src/queue/queue-service.ts

@@ -16,6 +16,7 @@ export enum QueueName {
   // TODO(akhilmhdh): This will get removed later. For now this is kept to stop the repeatable queue
   AuditLogPrune = "audit-log-prune",
   DailyResourceCleanUp = "daily-resource-cleanup",
+  DailyExpiringPkiItemAlert = "daily-expiring-pki-item-alert",
   TelemetryInstanceStats = "telemtry-self-hosted-stats",
   IntegrationSync = "sync-integrations",
   SecretWebhook = "secret-webhook",
@@ -36,6 +37,7 @@ export enum QueueJobs {
   // TODO(akhilmhdh): This will get removed later. For now this is kept to stop the repeatable queue
   AuditLogPrune = "audit-log-prune-job",
   DailyResourceCleanUp = "daily-resource-cleanup-job",
+  DailyExpiringPkiItemAlert = "daily-expiring-pki-item-alert",
   SecWebhook = "secret-webhook-trigger",
   TelemetryInstanceStats = "telemetry-self-hosted-stats",
   IntegrationSync = "secret-integration-pull",
@@ -71,6 +73,10 @@ export type TQueueJobTypes = {
     name: QueueJobs.DailyResourceCleanUp;
     payload: undefined;
   };
+  [QueueName.DailyExpiringPkiItemAlert]: {
+    name: QueueJobs.DailyExpiringPkiItemAlert;
+    payload: undefined;
+  };
   [QueueName.AuditLogPrune]: {
     name: QueueJobs.AuditLogPrune;
     payload: undefined;

backend/src/server/routes/index.ts

@@ -89,6 +89,8 @@ import { certificateAuthorityDALFactory } from "@app/services/certificate-author
 import { certificateAuthorityQueueFactory } from "@app/services/certificate-authority/certificate-authority-queue";
 import { certificateAuthoritySecretDALFactory } from "@app/services/certificate-authority/certificate-authority-secret-dal";
 import { certificateAuthorityServiceFactory } from "@app/services/certificate-authority/certificate-authority-service";
+import { certificateTemplateDALFactory } from "@app/services/certificate-template/certificate-template-dal";
+import { certificateTemplateServiceFactory } from "@app/services/certificate-template/certificate-template-service";
 import { groupProjectDALFactory } from "@app/services/group-project/group-project-dal";
 import { groupProjectMembershipRoleDALFactory } from "@app/services/group-project/group-project-membership-role-dal";
 import { groupProjectServiceFactory } from "@app/services/group-project/group-project-service";
@@ -131,6 +133,12 @@ import { orgRoleServiceFactory } from "@app/services/org/org-role-service";
 import { orgServiceFactory } from "@app/services/org/org-service";
 import { orgAdminServiceFactory } from "@app/services/org-admin/org-admin-service";
 import { orgMembershipDALFactory } from "@app/services/org-membership/org-membership-dal";
+import { dailyExpiringPkiItemAlertQueueServiceFactory } from "@app/services/pki-alert/expiring-pki-item-alert-queue";
+import { pkiAlertDALFactory } from "@app/services/pki-alert/pki-alert-dal";
+import { pkiAlertServiceFactory } from "@app/services/pki-alert/pki-alert-service";
+import { pkiCollectionDALFactory } from "@app/services/pki-collection/pki-collection-dal";
+import { pkiCollectionItemDALFactory } from "@app/services/pki-collection/pki-collection-item-dal";
+import { pkiCollectionServiceFactory } from "@app/services/pki-collection/pki-collection-service";
 import { projectDALFactory } from "@app/services/project/project-dal";
 import { projectQueueFactory } from "@app/services/project/project-queue";
 import { projectServiceFactory } from "@app/services/project/project-service";
@@ -356,7 +364,8 @@ export const registerRoutes = async (
     projectEnvDAL,
     secretApprovalPolicyApproverDAL: sapApproverDAL,
     permissionService,
-    secretApprovalPolicyDAL
+    secretApprovalPolicyDAL,
+    licenseService
   });
   const tokenService = tokenServiceFactory({ tokenDAL: authTokenDAL, userDAL, orgMembershipDAL });
 
@@ -403,6 +412,7 @@ export const registerRoutes = async (
     orgDAL,
     orgMembershipDAL,
     projectDAL,
+    projectUserAdditionalPrivilegeDAL,
     projectMembershipDAL,
     groupDAL,
     groupProjectDAL,
@@ -468,6 +478,7 @@ export const registerRoutes = async (
     orgDAL,
     incidentContactDAL,
     tokenService,
+    projectUserAdditionalPrivilegeDAL,
     projectDAL,
     projectMembershipDAL,
     orgMembershipDAL,
@@ -540,10 +551,12 @@ export const registerRoutes = async (
     projectBotDAL,
     orgDAL,
     userDAL,
+    projectUserAdditionalPrivilegeDAL,
     userGroupMembershipDAL,
     smtpService,
     projectKeyDAL,
     projectRoleDAL,
+    groupProjectDAL,
     licenseService
   });
   const projectUserAdditionalPrivilegeService = projectUserAdditionalPrivilegeServiceFactory({
@@ -580,10 +593,15 @@ export const registerRoutes = async (
   const certificateAuthorityCertDAL = certificateAuthorityCertDALFactory(db);
   const certificateAuthoritySecretDAL = certificateAuthoritySecretDALFactory(db);
   const certificateAuthorityCrlDAL = certificateAuthorityCrlDALFactory(db);
+  const certificateTemplateDAL = certificateTemplateDALFactory(db);
 
   const certificateDAL = certificateDALFactory(db);
   const certificateBodyDAL = certificateBodyDALFactory(db);
 
+  const pkiAlertDAL = pkiAlertDALFactory(db);
+  const pkiCollectionDAL = pkiCollectionDALFactory(db);
+  const pkiCollectionItemDAL = pkiCollectionItemDALFactory(db);
+
   const certificateService = certificateServiceFactory({
     certificateDAL,
     certificateBodyDAL,
@@ -611,9 +629,12 @@ export const registerRoutes = async (
     certificateAuthorityCertDAL,
     certificateAuthoritySecretDAL,
     certificateAuthorityCrlDAL,
+    certificateTemplateDAL,
     certificateAuthorityQueue,
     certificateDAL,
     certificateBodyDAL,
+    pkiCollectionDAL,
+    pkiCollectionItemDAL,
     projectDAL,
     kmsService,
     permissionService
@@ -628,6 +649,27 @@ export const registerRoutes = async (
     licenseService
   });
 
+  const certificateTemplateService = certificateTemplateServiceFactory({
+    certificateTemplateDAL,
+    certificateAuthorityDAL,
+    permissionService
+  });
+
+  const pkiAlertService = pkiAlertServiceFactory({
+    pkiAlertDAL,
+    pkiCollectionDAL,
+    permissionService,
+    smtpService
+  });
+
+  const pkiCollectionService = pkiCollectionServiceFactory({
+    pkiCollectionDAL,
+    pkiCollectionItemDAL,
+    certificateAuthorityDAL,
+    certificateDAL,
+    permissionService
+  });
+
   const projectService = projectServiceFactory({
     permissionService,
     projectDAL,
@@ -644,11 +686,14 @@ export const registerRoutes = async (
     licenseService,
     certificateAuthorityDAL,
     certificateDAL,
+    pkiAlertDAL,
+    pkiCollectionDAL,
     projectUserMembershipRoleDAL,
     identityProjectMembershipRoleDAL,
     keyStore,
     kmsService,
-    projectBotDAL
+    projectBotDAL,
+    certificateTemplateDAL
   });
 
   const projectEnvService = projectEnvServiceFactory({
@@ -711,6 +756,7 @@ export const registerRoutes = async (
     kmsService
   });
   const secretQueueService = secretQueueFactory({
+    keyStore,
     queueService,
     secretDAL,
     folderDAL,
@@ -796,7 +842,8 @@ export const registerRoutes = async (
     secretVersionTagV2BridgeDAL,
     smtpService,
     projectEnvDAL,
-    userDAL
+    userDAL,
+    licenseService
   });
 
   const secretService = secretServiceFactory({
@@ -1041,6 +1088,11 @@ export const registerRoutes = async (
     identityUniversalAuthClientSecretDAL: identityUaClientSecretDAL
   });
 
+  const dailyExpiringPkiItemAlert = dailyExpiringPkiItemAlertQueueServiceFactory({
+    queueService,
+    pkiAlertService
+  });
+
   const oidcService = oidcConfigServiceFactory({
     orgDAL,
     orgMembershipDAL,
@@ -1065,6 +1117,7 @@ export const registerRoutes = async (
 
   await telemetryQueue.startTelemetryCheck();
   await dailyResourceCleanUp.startCleanUp();
+  await dailyExpiringPkiItemAlert.startSendingAlerts();
   await kmsService.startService();
 
   // inject all services
@@ -1122,7 +1175,10 @@ export const registerRoutes = async (
     auditLogStream: auditLogStreamService,
     certificate: certificateService,
     certificateAuthority: certificateAuthorityService,
+    certificateTemplate: certificateTemplateService,
     certificateAuthorityCrl: certificateAuthorityCrlService,
+    pkiAlert: pkiAlertService,
+    pkiCollection: pkiCollectionService,
     secretScanning: secretScanningService,
     license: licenseService,
     trustedIp: trustedIpService,

backend/src/server/routes/v1/certificate-authority-router.ts

@@ -8,7 +8,7 @@ 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";
 import { CertKeyAlgorithm } from "@app/services/certificate/certificate-types";
-import { CaStatus, CaType } from "@app/services/certificate-authority/certificate-authority-types";
+import { CaRenewalType, CaStatus, CaType } from "@app/services/certificate-authority/certificate-authority-types";
 import {
   validateAltNamesField,
   validateCaDateField
@@ -275,15 +275,118 @@ export const registerCaRouter = async (server: FastifyZodProvider) => {
     }
   });
 
+  server.route({
+    method: "POST",
+    url: "/:caId/renew",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Perform CA certificate renewal",
+      params: z.object({
+        caId: z.string().trim().describe(CERTIFICATE_AUTHORITIES.RENEW_CA_CERT.caId)
+      }),
+      body: z.object({
+        type: z.nativeEnum(CaRenewalType).describe(CERTIFICATE_AUTHORITIES.RENEW_CA_CERT.type),
+        notAfter: validateCaDateField.describe(CERTIFICATE_AUTHORITIES.RENEW_CA_CERT.notAfter)
+      }),
+      response: {
+        200: z.object({
+          certificate: z.string().trim().describe(CERTIFICATE_AUTHORITIES.RENEW_CA_CERT.certificate),
+          certificateChain: z.string().trim().describe(CERTIFICATE_AUTHORITIES.RENEW_CA_CERT.certificateChain),
+          serialNumber: z.string().trim().describe(CERTIFICATE_AUTHORITIES.RENEW_CA_CERT.serialNumber)
+        })
+      }
+    },
+    handler: async (req) => {
+      const { certificate, certificateChain, serialNumber, ca } =
+        await server.services.certificateAuthority.renewCaCert({
+          caId: req.params.caId,
+          actor: req.permission.type,
+          actorId: req.permission.id,
+          actorAuthMethod: req.permission.authMethod,
+          actorOrgId: req.permission.orgId,
+          ...req.body
+        });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: ca.projectId,
+        event: {
+          type: EventType.RENEW_CA,
+          metadata: {
+            caId: ca.id,
+            dn: ca.dn
+          }
+        }
+      });
+
+      return {
+        certificate,
+        certificateChain,
+        serialNumber
+      };
+    }
+  });
+
   server.route({
     method: "GET",
-    url: "/:caId/certificate",
+    url: "/:caId/ca-certificates",
     config: {
       rateLimit: readLimit
     },
     onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
     schema: {
-      description: "Get cert and cert chain of a CA",
+      description: "Get list of past and current CA certificates for a CA",
+      params: z.object({
+        caId: z.string().trim().describe(CERTIFICATE_AUTHORITIES.GET_CA_CERTS.caId)
+      }),
+      response: {
+        200: z.array(
+          z.object({
+            certificate: z.string().describe(CERTIFICATE_AUTHORITIES.GET_CA_CERTS.certificate),
+            certificateChain: z.string().describe(CERTIFICATE_AUTHORITIES.GET_CA_CERTS.certificateChain),
+            serialNumber: z.string().describe(CERTIFICATE_AUTHORITIES.GET_CA_CERTS.serialNumber),
+            version: z.number().describe(CERTIFICATE_AUTHORITIES.GET_CA_CERTS.version)
+          })
+        )
+      }
+    },
+    handler: async (req) => {
+      const { caCerts, ca } = await server.services.certificateAuthority.getCaCerts({
+        caId: req.params.caId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: ca.projectId,
+        event: {
+          type: EventType.GET_CA_CERTS,
+          metadata: {
+            caId: ca.id,
+            dn: ca.dn
+          }
+        }
+      });
+
+      return caCerts;
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/:caId/certificate", // TODO: consider updating endpoint structure considering CA certificates
+    config: {
+      rateLimit: readLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Get current CA cert and cert chain of a CA",
       params: z.object({
         caId: z.string().trim().describe(CERTIFICATE_AUTHORITIES.GET_CERT.caId)
       }),
@@ -453,6 +556,7 @@ export const registerCaRouter = async (server: FastifyZodProvider) => {
       }),
       body: z
         .object({
+          pkiCollectionId: z.string().trim().optional().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.pkiCollectionId),
           friendlyName: z.string().trim().optional().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.friendlyName),
           commonName: z.string().trim().min(1).describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.commonName),
           altNames: validateAltNamesField.describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.altNames),
@@ -532,6 +636,7 @@ export const registerCaRouter = async (server: FastifyZodProvider) => {
       body: z
         .object({
           csr: z.string().trim().min(1).describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.csr),
+          pkiCollectionId: z.string().trim().optional().describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.pkiCollectionId),
           friendlyName: z.string().trim().optional().describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.friendlyName),
           commonName: z.string().trim().min(1).optional().describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.commonName),
           altNames: validateAltNamesField.describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.altNames),

backend/src/server/routes/v1/certificate-router.ts

@@ -1,12 +1,17 @@
+import ms from "ms";
 import { z } from "zod";
 
 import { CertificatesSchema } from "@app/db/schemas";
 import { EventType } from "@app/ee/services/audit-log/audit-log-types";
-import { CERTIFICATES } from "@app/lib/api-docs";
+import { CERTIFICATE_AUTHORITIES, CERTIFICATES } from "@app/lib/api-docs";
 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";
 import { CrlReason } from "@app/services/certificate/certificate-types";
+import {
+  validateAltNamesField,
+  validateCaDateField
+} from "@app/services/certificate-authority/certificate-authority-validators";
 
 export const registerCertRouter = async (server: FastifyZodProvider) => {
   server.route({
@@ -55,6 +60,185 @@ export const registerCertRouter = async (server: FastifyZodProvider) => {
     }
   });
 
+  server.route({
+    method: "POST",
+    url: "/issue-certificate",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Issue certificate",
+      body: z
+        .object({
+          caId: z.string().trim().optional().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.caId),
+          certificateTemplateId: z
+            .string()
+            .trim()
+            .optional()
+            .describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.certificateTemplateId),
+          pkiCollectionId: z.string().trim().optional().describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.pkiCollectionId),
+          friendlyName: z.string().trim().optional().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.friendlyName),
+          commonName: z.string().trim().min(1).describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.commonName),
+          altNames: validateAltNamesField.describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.altNames),
+          ttl: z
+            .string()
+            .refine((val) => ms(val) > 0, "TTL must be a positive number")
+            .describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.ttl),
+          notBefore: validateCaDateField.optional().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.notBefore),
+          notAfter: validateCaDateField.optional().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.notAfter)
+        })
+        .refine(
+          (data) => {
+            const { ttl, notAfter } = data;
+            return (ttl !== undefined && notAfter === undefined) || (ttl === undefined && notAfter !== undefined);
+          },
+          {
+            message: "Either ttl or notAfter must be present, but not both",
+            path: ["ttl", "notAfter"]
+          }
+        )
+        .refine(
+          (data) =>
+            (data.caId !== undefined && data.certificateTemplateId === undefined) ||
+            (data.caId === undefined && data.certificateTemplateId !== undefined),
+          {
+            message: "Either CA ID or Certificate Template ID must be present, but not both",
+            path: ["caId", "certificateTemplateId"]
+          }
+        ),
+      response: {
+        200: z.object({
+          certificate: z.string().trim().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.certificate),
+          issuingCaCertificate: z.string().trim().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.issuingCaCertificate),
+          certificateChain: z.string().trim().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.certificateChain),
+          privateKey: z.string().trim().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.privateKey),
+          serialNumber: z.string().trim().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.serialNumber)
+        })
+      }
+    },
+    handler: async (req) => {
+      const { certificate, certificateChain, issuingCaCertificate, privateKey, serialNumber, ca } =
+        await server.services.certificateAuthority.issueCertFromCa({
+          actor: req.permission.type,
+          actorId: req.permission.id,
+          actorAuthMethod: req.permission.authMethod,
+          actorOrgId: req.permission.orgId,
+          ...req.body
+        });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: ca.projectId,
+        event: {
+          type: EventType.ISSUE_CERT,
+          metadata: {
+            caId: ca.id,
+            dn: ca.dn,
+            serialNumber
+          }
+        }
+      });
+
+      return {
+        certificate,
+        certificateChain,
+        issuingCaCertificate,
+        privateKey,
+        serialNumber
+      };
+    }
+  });
+
+  server.route({
+    method: "POST",
+    url: "/sign-certificate",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Sign certificate",
+      body: z
+        .object({
+          caId: z.string().trim().optional().describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.caId),
+          certificateTemplateId: z
+            .string()
+            .trim()
+            .optional()
+            .describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.certificateTemplateId),
+          pkiCollectionId: z.string().trim().optional().describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.pkiCollectionId),
+          csr: z.string().trim().min(1).describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.csr),
+          friendlyName: z.string().trim().optional().describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.friendlyName),
+          commonName: z.string().trim().min(1).optional().describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.commonName),
+          altNames: validateAltNamesField.describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.altNames),
+          ttl: z
+            .string()
+            .refine((val) => ms(val) > 0, "TTL must be a positive number")
+            .describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.ttl),
+          notBefore: validateCaDateField.optional().describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.notBefore),
+          notAfter: validateCaDateField.optional().describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.notAfter)
+        })
+        .refine(
+          (data) => {
+            const { ttl, notAfter } = data;
+            return (ttl !== undefined && notAfter === undefined) || (ttl === undefined && notAfter !== undefined);
+          },
+          {
+            message: "Either ttl or notAfter must be present, but not both",
+            path: ["ttl", "notAfter"]
+          }
+        )
+        .refine(
+          (data) =>
+            (data.caId !== undefined && data.certificateTemplateId === undefined) ||
+            (data.caId === undefined && data.certificateTemplateId !== undefined),
+          {
+            message: "Either CA ID or Certificate Template ID must be present, but not both",
+            path: ["caId", "certificateTemplateId"]
+          }
+        ),
+      response: {
+        200: z.object({
+          certificate: z.string().trim().describe(CERTIFICATE_AUTHORITIES.SIGN_CERT.certificate),
+          issuingCaCertificate: z.string().trim().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.issuingCaCertificate),
+          certificateChain: z.string().trim().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.certificateChain),
+          serialNumber: z.string().trim().describe(CERTIFICATE_AUTHORITIES.ISSUE_CERT.serialNumber)
+        })
+      }
+    },
+    handler: async (req) => {
+      const { certificate, certificateChain, issuingCaCertificate, serialNumber, ca } =
+        await server.services.certificateAuthority.signCertFromCa({
+          actor: req.permission.type,
+          actorId: req.permission.id,
+          actorAuthMethod: req.permission.authMethod,
+          actorOrgId: req.permission.orgId,
+          ...req.body
+        });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: ca.projectId,
+        event: {
+          type: EventType.SIGN_CERT,
+          metadata: {
+            caId: ca.id,
+            dn: ca.dn,
+            serialNumber
+          }
+        }
+      });
+
+      return {
+        certificate,
+        certificateChain,
+        issuingCaCertificate,
+        serialNumber
+      };
+    }
+  });
+
   server.route({
     method: "POST",
     url: "/:serialNumber/revoke",

backend/src/server/routes/v1/certificate-template-router.ts

@@ -0,0 +1,205 @@
+import ms from "ms";
+import { z } from "zod";
+
+import { EventType } from "@app/ee/services/audit-log/audit-log-types";
+import { CERTIFICATE_TEMPLATES } from "@app/lib/api-docs";
+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";
+import { sanitizedCertificateTemplate } from "@app/services/certificate-template/certificate-template-schema";
+import { validateTemplateRegexField } from "@app/services/certificate-template/certificate-template-validators";
+
+export const registerCertificateTemplateRouter = async (server: FastifyZodProvider) => {
+  server.route({
+    method: "GET",
+    url: "/:certificateTemplateId",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      params: z.object({
+        certificateTemplateId: z.string().describe(CERTIFICATE_TEMPLATES.GET.certificateTemplateId)
+      }),
+      response: {
+        200: sanitizedCertificateTemplate
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const certificateTemplate = await server.services.certificateTemplate.getCertTemplate({
+        id: req.params.certificateTemplateId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: certificateTemplate.projectId,
+        event: {
+          type: EventType.GET_CERTIFICATE_TEMPLATE,
+          metadata: {
+            certificateTemplateId: certificateTemplate.id
+          }
+        }
+      });
+
+      return certificateTemplate;
+    }
+  });
+
+  server.route({
+    method: "POST",
+    url: "/",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      body: z.object({
+        caId: z.string().describe(CERTIFICATE_TEMPLATES.CREATE.caId),
+        pkiCollectionId: z.string().optional().describe(CERTIFICATE_TEMPLATES.CREATE.pkiCollectionId),
+        name: z.string().min(1).describe(CERTIFICATE_TEMPLATES.CREATE.name),
+        commonName: validateTemplateRegexField.describe(CERTIFICATE_TEMPLATES.CREATE.commonName),
+        subjectAlternativeName: validateTemplateRegexField.describe(
+          CERTIFICATE_TEMPLATES.CREATE.subjectAlternativeName
+        ),
+        ttl: z
+          .string()
+          .refine((val) => ms(val) > 0, "TTL must be a positive number")
+          .describe(CERTIFICATE_TEMPLATES.CREATE.ttl)
+      }),
+      response: {
+        200: sanitizedCertificateTemplate
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const certificateTemplate = await server.services.certificateTemplate.createCertTemplate({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        ...req.body
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: certificateTemplate.projectId,
+        event: {
+          type: EventType.CREATE_CERTIFICATE_TEMPLATE,
+          metadata: {
+            certificateTemplateId: certificateTemplate.id,
+            caId: certificateTemplate.caId,
+            pkiCollectionId: certificateTemplate.pkiCollectionId as string,
+            name: certificateTemplate.name,
+            commonName: certificateTemplate.commonName,
+            subjectAlternativeName: certificateTemplate.subjectAlternativeName,
+            ttl: certificateTemplate.ttl
+          }
+        }
+      });
+
+      return certificateTemplate;
+    }
+  });
+
+  server.route({
+    method: "PATCH",
+    url: "/:certificateTemplateId",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      body: z.object({
+        caId: z.string().optional().describe(CERTIFICATE_TEMPLATES.UPDATE.caId),
+        pkiCollectionId: z.string().optional().describe(CERTIFICATE_TEMPLATES.UPDATE.pkiCollectionId),
+        name: z.string().min(1).optional().describe(CERTIFICATE_TEMPLATES.UPDATE.name),
+        commonName: validateTemplateRegexField.optional().describe(CERTIFICATE_TEMPLATES.UPDATE.commonName),
+        subjectAlternativeName: validateTemplateRegexField
+          .optional()
+          .describe(CERTIFICATE_TEMPLATES.UPDATE.subjectAlternativeName),
+        ttl: z
+          .string()
+          .refine((val) => ms(val) > 0, "TTL must be a positive number")
+          .optional()
+          .describe(CERTIFICATE_TEMPLATES.UPDATE.ttl)
+      }),
+      params: z.object({
+        certificateTemplateId: z.string().describe(CERTIFICATE_TEMPLATES.UPDATE.certificateTemplateId)
+      }),
+      response: {
+        200: sanitizedCertificateTemplate
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const certificateTemplate = await server.services.certificateTemplate.updateCertTemplate({
+        ...req.body,
+        id: req.params.certificateTemplateId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: certificateTemplate.projectId,
+        event: {
+          type: EventType.UPDATE_CERTIFICATE_TEMPLATE,
+          metadata: {
+            certificateTemplateId: certificateTemplate.id,
+            caId: certificateTemplate.caId,
+            pkiCollectionId: certificateTemplate.pkiCollectionId as string,
+            name: certificateTemplate.name,
+            commonName: certificateTemplate.commonName,
+            subjectAlternativeName: certificateTemplate.subjectAlternativeName,
+            ttl: certificateTemplate.ttl
+          }
+        }
+      });
+
+      return certificateTemplate;
+    }
+  });
+
+  server.route({
+    method: "DELETE",
+    url: "/:certificateTemplateId",
+    config: {
+      rateLimit: writeLimit
+    },
+    schema: {
+      params: z.object({
+        certificateTemplateId: z.string().describe(CERTIFICATE_TEMPLATES.DELETE.certificateTemplateId)
+      }),
+      response: {
+        200: sanitizedCertificateTemplate
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const certificateTemplate = await server.services.certificateTemplate.deleteCertTemplate({
+        id: req.params.certificateTemplateId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: certificateTemplate.projectId,
+        event: {
+          type: EventType.DELETE_CERTIFICATE_TEMPLATE,
+          metadata: {
+            certificateTemplateId: certificateTemplate.id
+          }
+        }
+      });
+
+      return certificateTemplate;
+    }
+  });
+};

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

@@ -3,6 +3,7 @@ import { registerAuthRoutes } from "./auth-router";
 import { registerProjectBotRouter } from "./bot-router";
 import { registerCaRouter } from "./certificate-authority-router";
 import { registerCertRouter } from "./certificate-router";
+import { registerCertificateTemplateRouter } from "./certificate-template-router";
 import { registerIdentityAccessTokenRouter } from "./identity-access-token-router";
 import { registerIdentityAwsAuthRouter } from "./identity-aws-iam-auth-router";
 import { registerIdentityAzureAuthRouter } from "./identity-azure-auth-router";
@@ -18,6 +19,8 @@ import { registerInviteOrgRouter } from "./invite-org-router";
 import { registerOrgAdminRouter } from "./org-admin-router";
 import { registerOrgRouter } from "./organization-router";
 import { registerPasswordRouter } from "./password-router";
+import { registerPkiAlertRouter } from "./pki-alert-router";
+import { registerPkiCollectionRouter } from "./pki-collection-router";
 import { registerProjectEnvRouter } from "./project-env-router";
 import { registerProjectKeyRouter } from "./project-key-router";
 import { registerProjectMembershipRouter } from "./project-membership-router";
@@ -74,6 +77,9 @@ export const registerV1Routes = async (server: FastifyZodProvider) => {
     async (pkiRouter) => {
       await pkiRouter.register(registerCaRouter, { prefix: "/ca" });
       await pkiRouter.register(registerCertRouter, { prefix: "/certificates" });
+      await pkiRouter.register(registerCertificateTemplateRouter, { prefix: "/certificate-templates" });
+      await pkiRouter.register(registerPkiAlertRouter, { prefix: "/alerts" });
+      await pkiRouter.register(registerPkiCollectionRouter, { prefix: "/collections" });
     },
     { prefix: "/pki" }
   );

backend/src/server/routes/v1/pki-alert-router.ts

@@ -0,0 +1,198 @@
+import { z } from "zod";
+
+import { PkiAlertsSchema } from "@app/db/schemas";
+import { EventType } from "@app/ee/services/audit-log/audit-log-types";
+import { ALERTS } from "@app/lib/api-docs";
+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";
+
+export const registerPkiAlertRouter = async (server: FastifyZodProvider) => {
+  server.route({
+    method: "POST",
+    url: "/",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Create PKI alert",
+      body: z.object({
+        projectId: z.string().trim().describe(ALERTS.CREATE.projectId),
+        pkiCollectionId: z.string().trim().describe(ALERTS.CREATE.pkiCollectionId),
+        name: z.string().trim().describe(ALERTS.CREATE.name),
+        alertBeforeDays: z.number().describe(ALERTS.CREATE.alertBeforeDays),
+        emails: z
+          .array(z.string().trim().email({ message: "Invalid email address" }))
+          .min(1, { message: "You must specify at least 1 email" })
+          .max(5, { message: "You can specify a maximum of 5 emails" })
+          .describe(ALERTS.CREATE.emails)
+      }),
+      response: {
+        200: PkiAlertsSchema
+      }
+    },
+    handler: async (req) => {
+      const alert = await server.services.pkiAlert.createPkiAlert({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        ...req.body
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: alert.projectId,
+        event: {
+          type: EventType.CREATE_PKI_ALERT,
+          metadata: {
+            pkiAlertId: alert.id,
+            pkiCollectionId: alert.pkiCollectionId,
+            name: alert.name,
+            alertBeforeDays: alert.alertBeforeDays,
+            recipientEmails: alert.recipientEmails
+          }
+        }
+      });
+
+      return alert;
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/:alertId",
+    config: {
+      rateLimit: readLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Get PKI alert",
+      params: z.object({
+        alertId: z.string().trim().describe(ALERTS.GET.alertId)
+      }),
+      response: {
+        200: PkiAlertsSchema
+      }
+    },
+    handler: async (req) => {
+      const alert = await server.services.pkiAlert.getPkiAlertById({
+        alertId: req.params.alertId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: alert.projectId,
+        event: {
+          type: EventType.GET_PKI_ALERT,
+          metadata: {
+            pkiAlertId: alert.id
+          }
+        }
+      });
+
+      return alert;
+    }
+  });
+
+  server.route({
+    method: "PATCH",
+    url: "/:alertId",
+    config: {
+      rateLimit: readLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Update PKI alert",
+      params: z.object({
+        alertId: z.string().trim().describe(ALERTS.UPDATE.alertId)
+      }),
+      body: z.object({
+        name: z.string().trim().optional().describe(ALERTS.UPDATE.name),
+        alertBeforeDays: z.number().optional().describe(ALERTS.UPDATE.alertBeforeDays),
+        pkiCollectionId: z.string().trim().optional().describe(ALERTS.UPDATE.pkiCollectionId),
+        emails: z
+          .array(z.string().trim().email({ message: "Invalid email address" }))
+          .min(1, { message: "You must specify at least 1 email" })
+          .max(5, { message: "You can specify a maximum of 5 emails" })
+          .optional()
+          .describe(ALERTS.UPDATE.emails)
+      }),
+      response: {
+        200: PkiAlertsSchema
+      }
+    },
+    handler: async (req) => {
+      const alert = await server.services.pkiAlert.updatePkiAlert({
+        alertId: req.params.alertId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        ...req.body
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: alert.projectId,
+        event: {
+          type: EventType.UPDATE_PKI_ALERT,
+          metadata: {
+            pkiAlertId: alert.id,
+            pkiCollectionId: alert.pkiCollectionId,
+            name: alert.name,
+            alertBeforeDays: alert.alertBeforeDays,
+            recipientEmails: alert.recipientEmails
+          }
+        }
+      });
+
+      return alert;
+    }
+  });
+
+  server.route({
+    method: "DELETE",
+    url: "/:alertId",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Delete PKI alert",
+      params: z.object({
+        alertId: z.string().trim().describe(ALERTS.DELETE.alertId)
+      }),
+      response: {
+        200: PkiAlertsSchema
+      }
+    },
+    handler: async (req) => {
+      const alert = await server.services.pkiAlert.deletePkiAlert({
+        alertId: req.params.alertId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: alert.projectId,
+        event: {
+          type: EventType.DELETE_PKI_ALERT,
+          metadata: {
+            pkiAlertId: alert.id
+          }
+        }
+      });
+
+      return alert;
+    }
+  });
+};

backend/src/server/routes/v1/pki-collection-router.ts

@@ -0,0 +1,338 @@
+import { z } from "zod";
+
+import { PkiCollectionItemsSchema, PkiCollectionsSchema } from "@app/db/schemas";
+import { EventType } from "@app/ee/services/audit-log/audit-log-types";
+import { PKI_COLLECTIONS } from "@app/lib/api-docs";
+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";
+import { PkiItemType } from "@app/services/pki-collection/pki-collection-types";
+
+export const registerPkiCollectionRouter = async (server: FastifyZodProvider) => {
+  server.route({
+    method: "POST",
+    url: "/",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Create PKI collection",
+      body: z.object({
+        projectId: z.string().trim().describe(PKI_COLLECTIONS.CREATE.projectId),
+        name: z.string().trim().describe(PKI_COLLECTIONS.CREATE.name),
+        description: z.string().trim().default("").describe(PKI_COLLECTIONS.CREATE.description)
+      }),
+      response: {
+        200: PkiCollectionsSchema
+      }
+    },
+    handler: async (req) => {
+      const pkiCollection = await server.services.pkiCollection.createPkiCollection({
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        ...req.body
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: pkiCollection.projectId,
+        event: {
+          type: EventType.CREATE_PKI_COLLECTION,
+          metadata: {
+            pkiCollectionId: pkiCollection.id,
+            name: pkiCollection.name
+          }
+        }
+      });
+
+      return pkiCollection;
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/:collectionId",
+    config: {
+      rateLimit: readLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Get PKI collection",
+      params: z.object({
+        collectionId: z.string().trim().describe(PKI_COLLECTIONS.GET.collectionId)
+      }),
+      response: {
+        200: PkiCollectionsSchema
+      }
+    },
+    handler: async (req) => {
+      const pkiCollection = await server.services.pkiCollection.getPkiCollectionById({
+        collectionId: req.params.collectionId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: pkiCollection.projectId,
+        event: {
+          type: EventType.GET_PKI_COLLECTION,
+          metadata: {
+            pkiCollectionId: pkiCollection.id
+          }
+        }
+      });
+
+      return pkiCollection;
+    }
+  });
+
+  server.route({
+    method: "PATCH",
+    url: "/:collectionId",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Update PKI collection",
+      params: z.object({
+        collectionId: z.string().trim().describe(PKI_COLLECTIONS.UPDATE.collectionId)
+      }),
+      body: z.object({
+        name: z.string().trim().optional().describe(PKI_COLLECTIONS.UPDATE.name),
+        description: z.string().trim().optional().describe(PKI_COLLECTIONS.UPDATE.description)
+      }),
+      response: {
+        200: PkiCollectionsSchema
+      }
+    },
+    handler: async (req) => {
+      const pkiCollection = await server.services.pkiCollection.updatePkiCollection({
+        collectionId: req.params.collectionId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        ...req.body
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: pkiCollection.projectId,
+        event: {
+          type: EventType.UPDATE_PKI_COLLECTION,
+          metadata: {
+            pkiCollectionId: pkiCollection.id,
+            name: pkiCollection.name
+          }
+        }
+      });
+
+      return pkiCollection;
+    }
+  });
+
+  server.route({
+    method: "DELETE",
+    url: "/:collectionId",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Delete PKI collection",
+      params: z.object({
+        collectionId: z.string().trim().describe(PKI_COLLECTIONS.DELETE.collectionId)
+      }),
+      response: {
+        200: PkiCollectionsSchema
+      }
+    },
+    handler: async (req) => {
+      const pkiCollection = await server.services.pkiCollection.deletePkiCollection({
+        collectionId: req.params.collectionId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: pkiCollection.projectId,
+        event: {
+          type: EventType.DELETE_PKI_COLLECTION,
+          metadata: {
+            pkiCollectionId: pkiCollection.id
+          }
+        }
+      });
+
+      return pkiCollection;
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/:collectionId/items",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Get items in PKI collection",
+      params: z.object({
+        collectionId: z.string().trim().describe(PKI_COLLECTIONS.LIST_ITEMS.collectionId)
+      }),
+      querystring: z.object({
+        type: z.nativeEnum(PkiItemType).optional().describe(PKI_COLLECTIONS.LIST_ITEMS.type),
+        offset: z.coerce.number().min(0).max(100).default(0).describe(PKI_COLLECTIONS.LIST_ITEMS.offset),
+        limit: z.coerce.number().min(1).max(100).default(25).describe(PKI_COLLECTIONS.LIST_ITEMS.limit)
+      }),
+      response: {
+        200: z.object({
+          collectionItems: z.array(
+            PkiCollectionItemsSchema.omit({ caId: true, certId: true }).extend({
+              type: z.nativeEnum(PkiItemType),
+              itemId: z.string().trim(),
+              notBefore: z.date(),
+              notAfter: z.date(),
+              friendlyName: z.string().trim()
+            })
+          ),
+          totalCount: z.number()
+        })
+      }
+    },
+    handler: async (req) => {
+      const { pkiCollection, pkiCollectionItems, totalCount } =
+        await server.services.pkiCollection.getPkiCollectionItems({
+          collectionId: req.params.collectionId,
+          actor: req.permission.type,
+          actorId: req.permission.id,
+          actorAuthMethod: req.permission.authMethod,
+          actorOrgId: req.permission.orgId,
+          ...req.query
+        });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: pkiCollection.projectId,
+        event: {
+          type: EventType.GET_PKI_COLLECTION_ITEMS,
+          metadata: {
+            pkiCollectionId: pkiCollection.id
+          }
+        }
+      });
+
+      return {
+        collectionItems: pkiCollectionItems,
+        totalCount
+      };
+    }
+  });
+
+  server.route({
+    method: "POST",
+    url: "/:collectionId/items",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Add item to PKI collection",
+      params: z.object({
+        collectionId: z.string().trim().describe(PKI_COLLECTIONS.ADD_ITEM.collectionId)
+      }),
+      body: z.object({
+        type: z.nativeEnum(PkiItemType).describe(PKI_COLLECTIONS.ADD_ITEM.type),
+        itemId: z.string().trim().describe(PKI_COLLECTIONS.ADD_ITEM.itemId)
+      }),
+      response: {
+        200: PkiCollectionItemsSchema.omit({ caId: true, certId: true }).extend({
+          type: z.nativeEnum(PkiItemType).describe(PKI_COLLECTIONS.ADD_ITEM.type),
+          itemId: z.string().trim().describe(PKI_COLLECTIONS.ADD_ITEM.itemId)
+        })
+      }
+    },
+    handler: async (req) => {
+      const { pkiCollection, pkiCollectionItem } = await server.services.pkiCollection.addItemToPkiCollection({
+        collectionId: req.params.collectionId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId,
+        ...req.body
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: pkiCollection.projectId,
+        event: {
+          type: EventType.ADD_PKI_COLLECTION_ITEM,
+          metadata: {
+            pkiCollectionId: pkiCollection.id,
+            pkiCollectionItemId: pkiCollectionItem.id,
+            type: pkiCollectionItem.type,
+            itemId: pkiCollectionItem.itemId
+          }
+        }
+      });
+
+      return pkiCollectionItem;
+    }
+  });
+
+  server.route({
+    method: "DELETE",
+    url: "/:collectionId/items/:collectionItemId",
+    config: {
+      rateLimit: writeLimit
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    schema: {
+      description: "Remove item from PKI collection",
+      params: z.object({
+        collectionId: z.string().trim().describe(PKI_COLLECTIONS.DELETE_ITEM.collectionId),
+        collectionItemId: z.string().trim().describe(PKI_COLLECTIONS.DELETE_ITEM.collectionItemId)
+      }),
+      response: {
+        200: PkiCollectionItemsSchema.omit({ caId: true, certId: true }).extend({
+          type: z.nativeEnum(PkiItemType).describe(PKI_COLLECTIONS.DELETE_ITEM.type),
+          itemId: z.string().trim().describe(PKI_COLLECTIONS.DELETE_ITEM.itemId)
+        })
+      }
+    },
+    handler: async (req) => {
+      const { pkiCollection, pkiCollectionItem } = await server.services.pkiCollection.removeItemFromPkiCollection({
+        collectionId: req.params.collectionId,
+        itemId: req.params.collectionItemId,
+        actor: req.permission.type,
+        actorId: req.permission.id,
+        actorAuthMethod: req.permission.authMethod,
+        actorOrgId: req.permission.orgId
+      });
+
+      await server.services.auditLog.createAuditLog({
+        ...req.auditLogInfo,
+        projectId: pkiCollection.projectId,
+        event: {
+          type: EventType.DELETE_PKI_COLLECTION_ITEM,
+          metadata: {
+            pkiCollectionId: pkiCollection.id,
+            pkiCollectionItemId: pkiCollectionItem.id
+          }
+        }
+      });
+
+      return pkiCollectionItem;
+    }
+  });
+};

backend/src/server/routes/v1/project-router.ts

@@ -59,12 +59,19 @@ export const registerProjectRouter = async (server: FastifyZodProvider) => {
       rateLimit: readLimit
     },
     schema: {
+      querystring: z.object({
+        includeGroupMembers: z
+          .enum(["true", "false"])
+          .default("false")
+          .transform((value) => value === "true")
+      }),
       params: z.object({
         workspaceId: z.string().trim()
       }),
       response: {
         200: z.object({
           users: ProjectMembershipsSchema.extend({
+            isGroupMember: z.boolean(),
             user: UsersSchema.pick({
               email: true,
               username: true,
@@ -99,9 +106,11 @@ export const registerProjectRouter = async (server: FastifyZodProvider) => {
         actorId: req.permission.id,
         actor: req.permission.type,
         actorAuthMethod: req.permission.authMethod,
+        includeGroupMembers: req.query.includeGroupMembers,
         projectId: req.params.workspaceId,
         actorOrgId: req.permission.orgId
       });
+
       return { users };
     }
   });

backend/src/server/routes/v2/project-router.ts

@@ -1,7 +1,13 @@
 import slugify from "@sindresorhus/slugify";
 import { z } from "zod";
 
-import { CertificateAuthoritiesSchema, CertificatesSchema, ProjectKeysSchema } from "@app/db/schemas";
+import {
+  CertificateAuthoritiesSchema,
+  CertificatesSchema,
+  PkiAlertsSchema,
+  PkiCollectionsSchema,
+  ProjectKeysSchema
+} from "@app/db/schemas";
 import { EventType } from "@app/ee/services/audit-log/audit-log-types";
 import { PROJECTS } from "@app/lib/api-docs";
 import { readLimit, writeLimit } from "@app/server/config/rateLimiter";
@@ -9,6 +15,7 @@ import { getTelemetryDistinctId } from "@app/server/lib/telemetry";
 import { verifyAuth } from "@app/server/plugins/auth/verify-auth";
 import { AuthMode } from "@app/services/auth/auth-type";
 import { CaStatus } from "@app/services/certificate-authority/certificate-authority-types";
+import { sanitizedCertificateTemplate } from "@app/services/certificate-template/certificate-template-schema";
 import { ProjectFilterType } from "@app/services/project/project-types";
 import { PostHogEventTypes } from "@app/services/telemetry/telemetry-types";
 
@@ -392,4 +399,94 @@ export const registerProjectRouter = async (server: FastifyZodProvider) => {
       return { certificates, totalCount };
     }
   });
+
+  server.route({
+    method: "GET",
+    url: "/:projectId/pki-alerts",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      params: z.object({
+        projectId: z.string().trim()
+      }),
+      response: {
+        200: z.object({
+          alerts: z.array(PkiAlertsSchema)
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const { alerts } = await server.services.project.listProjectAlerts({
+        projectId: req.params.projectId,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actorAuthMethod: req.permission.authMethod,
+        actor: req.permission.type
+      });
+
+      return { alerts };
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/:projectId/pki-collections",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      params: z.object({
+        projectId: z.string().trim()
+      }),
+      response: {
+        200: z.object({
+          collections: z.array(PkiCollectionsSchema)
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const { pkiCollections } = await server.services.project.listProjectPkiCollections({
+        projectId: req.params.projectId,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actorAuthMethod: req.permission.authMethod,
+        actor: req.permission.type
+      });
+
+      return { collections: pkiCollections };
+    }
+  });
+
+  server.route({
+    method: "GET",
+    url: "/:projectId/certificate-templates",
+    config: {
+      rateLimit: readLimit
+    },
+    schema: {
+      params: z.object({
+        projectId: z.string().trim()
+      }),
+      response: {
+        200: z.object({
+          certificateTemplates: sanitizedCertificateTemplate.array()
+        })
+      }
+    },
+    onRequest: verifyAuth([AuthMode.JWT, AuthMode.IDENTITY_ACCESS_TOKEN]),
+    handler: async (req) => {
+      const { certificateTemplates } = await server.services.project.listProjectCertificateTemplates({
+        projectId: req.params.projectId,
+        actorId: req.permission.id,
+        actorOrgId: req.permission.orgId,
+        actorAuthMethod: req.permission.authMethod,
+        actor: req.permission.type
+      });
+
+      return { certificateTemplates };
+    }
+  });
 };

backend/src/services/certificate-authority/certificate-authority-fns.ts

@@ -1,11 +1,17 @@
 import * as x509 from "@peculiar/x509";
 import crypto from "crypto";
 
-import { BadRequestError } from "@app/lib/errors";
+import { NotFoundError } from "@app/lib/errors";
 import { getProjectKmsCertificateKeyId } from "@app/services/project/project-fns";
 
 import { CertKeyAlgorithm, CertStatus } from "../certificate/certificate-types";
-import { TDNParts, TGetCaCertChainDTO, TGetCaCredentialsDTO, TRebuildCaCrlDTO } from "./certificate-authority-types";
+import {
+  TDNParts,
+  TGetCaCertChainDTO,
+  TGetCaCertChainsDTO,
+  TGetCaCredentialsDTO,
+  TRebuildCaCrlDTO
+} from "./certificate-authority-types";
 
 export const createDistinguishedName = (parts: TDNParts) => {
   const dnParts = [];
@@ -89,6 +95,8 @@ export const keyAlgorithmToAlgCfg = (keyAlgorithm: CertKeyAlgorithm) => {
  * Return the public and private key of CA with id [caId]
  * Note: credentials are returned as crypto.webcrypto.CryptoKey
  * suitable for use with @peculiar/x509 module
+ *
+ * TODO: Update to get latest CA Secret once support for CA renewal with new key pair is added
  */
 export const getCaCredentials = async ({
   caId,
@@ -98,10 +106,10 @@ export const getCaCredentials = async ({
   kmsService
 }: TGetCaCredentialsDTO) => {
   const ca = await certificateAuthorityDAL.findById(caId);
-  if (!ca) throw new BadRequestError({ message: "CA not found" });
+  if (!ca) throw new NotFoundError({ message: "CA not found" });
 
   const caSecret = await certificateAuthoritySecretDAL.findOne({ caId });
-  if (!caSecret) throw new BadRequestError({ message: "CA secret not found" });
+  if (!caSecret) throw new NotFoundError({ message: "CA secret not found" });
 
   const keyId = await getProjectKmsCertificateKeyId({
     projectId: ca.projectId,
@@ -132,26 +140,73 @@ export const getCaCredentials = async ({
   ]);
 
   return {
+    caSecret,
     caPrivateKey,
     caPublicKey
   };
 };
 
 /**
- * Return the decrypted pem-encoded certificate and certificate chain
+ * Return the list of decrypted pem-encoded certificates and certificate chains
  * for CA with id [caId].
  */
-export const getCaCertChain = async ({
+export const getCaCertChains = async ({
   caId,
   certificateAuthorityDAL,
   certificateAuthorityCertDAL,
   projectDAL,
   kmsService
-}: TGetCaCertChainDTO) => {
+}: TGetCaCertChainsDTO) => {
   const ca = await certificateAuthorityDAL.findById(caId);
-  if (!ca) throw new BadRequestError({ message: "CA not found" });
+  if (!ca) throw new NotFoundError({ message: "CA not found" });
 
-  const caCert = await certificateAuthorityCertDAL.findOne({ caId: ca.id });
+  const keyId = await getProjectKmsCertificateKeyId({
+    projectId: ca.projectId,
+    projectDAL,
+    kmsService
+  });
+
+  const kmsDecryptor = await kmsService.decryptWithKmsKey({
+    kmsId: keyId
+  });
+
+  const caCerts = await certificateAuthorityCertDAL.find({ caId: ca.id }, { sort: [["version", "asc"]] });
+
+  const decryptedChains = await Promise.all(
+    caCerts.map(async (caCert) => {
+      const decryptedCaCert = await kmsDecryptor({
+        cipherTextBlob: caCert.encryptedCertificate
+      });
+      const caCertObj = new x509.X509Certificate(decryptedCaCert);
+      const decryptedChain = await kmsDecryptor({
+        cipherTextBlob: caCert.encryptedCertificateChain
+      });
+      return {
+        certificate: caCertObj.toString("pem"),
+        certificateChain: decryptedChain.toString("utf-8"),
+        serialNumber: caCertObj.serialNumber,
+        version: caCert.version
+      };
+    })
+  );
+
+  return decryptedChains;
+};
+
+/**
+ * Return the decrypted pem-encoded certificate and certificate chain
+ * corresponding to CA certificate with id [caCertId].
+ */
+export const getCaCertChain = async ({
+  caCertId,
+  certificateAuthorityDAL,
+  certificateAuthorityCertDAL,
+  projectDAL,
+  kmsService
+}: TGetCaCertChainDTO) => {
+  const caCert = await certificateAuthorityCertDAL.findById(caCertId);
+  if (!caCert) throw new NotFoundError({ message: "CA certificate not found" });
+  const ca = await certificateAuthorityDAL.findById(caCert.caId);
 
   const keyId = await getProjectKmsCertificateKeyId({
     projectId: ca.projectId,
@@ -194,7 +249,7 @@ export const rebuildCaCrl = async ({
   kmsService
 }: TRebuildCaCrlDTO) => {
   const ca = await certificateAuthorityDAL.findById(caId);
-  if (!ca) throw new BadRequestError({ message: "CA not found" });
+  if (!ca) throw new NotFoundError({ message: "CA not found" });
 
   const caSecret = await certificateAuthoritySecretDAL.findOne({ caId: ca.id });
 

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

@@ -5,22 +5,28 @@ import crypto, { KeyObject } from "crypto";
 import ms from "ms";
 import { z } from "zod";
 
+import { TCertificateAuthorities, TCertificateTemplates } from "@app/db/schemas";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
 import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
-import { BadRequestError } from "@app/lib/errors";
+import { BadRequestError, NotFoundError } from "@app/lib/errors";
 import { TCertificateBodyDALFactory } from "@app/services/certificate/certificate-body-dal";
 import { TCertificateDALFactory } from "@app/services/certificate/certificate-dal";
 import { TKmsServiceFactory } from "@app/services/kms/kms-service";
+import { TPkiCollectionDALFactory } from "@app/services/pki-collection/pki-collection-dal";
+import { TPkiCollectionItemDALFactory } from "@app/services/pki-collection/pki-collection-item-dal";
 import { TProjectDALFactory } from "@app/services/project/project-dal";
 import { getProjectKmsCertificateKeyId } from "@app/services/project/project-fns";
 
 import { TCertificateAuthorityCrlDALFactory } from "../../ee/services/certificate-authority-crl/certificate-authority-crl-dal";
 import { CertKeyAlgorithm, CertStatus } from "../certificate/certificate-types";
+import { TCertificateTemplateDALFactory } from "../certificate-template/certificate-template-dal";
+import { validateCertificateDetailsAgainstTemplate } from "../certificate-template/certificate-template-fns";
 import { TCertificateAuthorityCertDALFactory } from "./certificate-authority-cert-dal";
 import { TCertificateAuthorityDALFactory } from "./certificate-authority-dal";
 import {
   createDistinguishedName,
-  getCaCertChain,
+  getCaCertChain, // TODO: consider rename
+  getCaCertChains,
   getCaCredentials,
   keyAlgorithmToAlgCfg,
   parseDistinguishedName
@@ -33,10 +39,12 @@ import {
   TCreateCaDTO,
   TDeleteCaDTO,
   TGetCaCertDTO,
+  TGetCaCertsDTO,
   TGetCaCsrDTO,
   TGetCaDTO,
   TImportCertToCaDTO,
   TIssueCertFromCaDTO,
+  TRenewCaCertDTO,
   TSignCertFromCaDTO,
   TSignIntermediateDTO,
   TUpdateCaDTO
@@ -48,12 +56,18 @@ type TCertificateAuthorityServiceFactoryDep = {
     TCertificateAuthorityDALFactory,
     "transaction" | "create" | "findById" | "updateById" | "deleteById" | "findOne"
   >;
-  certificateAuthorityCertDAL: Pick<TCertificateAuthorityCertDALFactory, "create" | "findOne" | "transaction">;
+  certificateAuthorityCertDAL: Pick<
+    TCertificateAuthorityCertDALFactory,
+    "create" | "findOne" | "transaction" | "find" | "findById"
+  >;
   certificateAuthoritySecretDAL: Pick<TCertificateAuthoritySecretDALFactory, "create" | "findOne">;
   certificateAuthorityCrlDAL: Pick<TCertificateAuthorityCrlDALFactory, "create" | "findOne" | "update">;
+  certificateTemplateDAL: Pick<TCertificateTemplateDALFactory, "getById">;
   certificateAuthorityQueue: TCertificateAuthorityQueueFactory; // TODO: Pick
   certificateDAL: Pick<TCertificateDALFactory, "transaction" | "create" | "find">;
   certificateBodyDAL: Pick<TCertificateBodyDALFactory, "create">;
+  pkiCollectionDAL: Pick<TPkiCollectionDALFactory, "findById">;
+  pkiCollectionItemDAL: Pick<TPkiCollectionItemDALFactory, "create">;
   projectDAL: Pick<TProjectDALFactory, "findProjectBySlug" | "findOne" | "updateById" | "findById" | "transaction">;
   kmsService: Pick<TKmsServiceFactory, "generateKmsKey" | "encryptWithKmsKey" | "decryptWithKmsKey">;
   permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
@@ -66,8 +80,11 @@ export const certificateAuthorityServiceFactory = ({
   certificateAuthorityCertDAL,
   certificateAuthoritySecretDAL,
   certificateAuthorityCrlDAL,
+  certificateTemplateDAL,
   certificateDAL,
   certificateBodyDAL,
+  pkiCollectionDAL,
+  pkiCollectionItemDAL,
   projectDAL,
   kmsService,
   permissionService
@@ -165,6 +182,24 @@ export const certificateAuthorityServiceFactory = ({
         kmsId: certificateManagerKmsId
       });
 
+      // https://nodejs.org/api/crypto.html#static-method-keyobjectfromkey
+      const skObj = KeyObject.from(keys.privateKey);
+
+      const { cipherTextBlob: encryptedPrivateKey } = await kmsEncryptor({
+        plainText: skObj.export({
+          type: "pkcs8",
+          format: "der"
+        })
+      });
+
+      const caSecret = await certificateAuthoritySecretDAL.create(
+        {
+          caId: ca.id,
+          encryptedPrivateKey
+        },
+        tx
+      );
+
       if (type === CaType.ROOT) {
         // note: create self-signed cert only applicable for root CA
         const cert = await x509.X509CertificateGenerator.createSelfSigned({
@@ -191,11 +226,21 @@ export const certificateAuthorityServiceFactory = ({
           plainText: Buffer.alloc(0)
         });
 
-        await certificateAuthorityCertDAL.create(
+        const caCert = await certificateAuthorityCertDAL.create(
           {
             caId: ca.id,
             encryptedCertificate,
-            encryptedCertificateChain
+            encryptedCertificateChain,
+            version: 1,
+            caSecretId: caSecret.id
+          },
+          tx
+        );
+
+        await certificateAuthorityDAL.updateById(
+          ca.id,
+          {
+            activeCaCertId: caCert.id
           },
           tx
         );
@@ -223,24 +268,6 @@ export const certificateAuthorityServiceFactory = ({
         tx
       );
 
-      // https://nodejs.org/api/crypto.html#static-method-keyobjectfromkey
-      const skObj = KeyObject.from(keys.privateKey);
-
-      const { cipherTextBlob: encryptedPrivateKey } = await kmsEncryptor({
-        plainText: skObj.export({
-          type: "pkcs8",
-          format: "der"
-        })
-      });
-
-      await certificateAuthoritySecretDAL.create(
-        {
-          caId: ca.id,
-          encryptedPrivateKey
-        },
-        tx
-      );
-
       return ca;
     });
 
@@ -341,9 +368,7 @@ export const certificateAuthorityServiceFactory = ({
     );
 
     if (ca.type === CaType.ROOT) throw new BadRequestError({ message: "Root CA cannot generate CSR" });
-
-    const caCert = await certificateAuthorityCertDAL.findOne({ caId: ca.id });
-    if (caCert) throw new BadRequestError({ message: "CA already has a certificate installed" });
+    if (ca.activeCaCertId) throw new BadRequestError({ message: "CA already has a certificate installed" });
 
     const { caPrivateKey, caPublicKey } = await getCaCredentials({
       caId,
@@ -381,9 +406,283 @@ export const certificateAuthorityServiceFactory = ({
   };
 
   /**
-   * Return certificate and certificate chain for CA
+   * Renew certificate for CA with id [caId]
+   * Note: Currently implements CA renewal with same key-pair only
    */
-  const getCaCert = async ({ caId, actorId, actorAuthMethod, actor, actorOrgId }: TGetCaCertDTO) => {
+  const renewCaCert = async ({ caId, notAfter, actorId, actorAuthMethod, actor, actorOrgId }: TRenewCaCertDTO) => {
+    const ca = await certificateAuthorityDAL.findById(caId);
+    if (!ca) throw new BadRequestError({ message: "CA not found" });
+
+    if (!ca.activeCaCertId) throw new BadRequestError({ message: "CA does not have a certificate installed" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      ca.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Create,
+      ProjectPermissionSub.CertificateAuthorities
+    );
+
+    if (ca.status === CaStatus.DISABLED) throw new BadRequestError({ message: "CA is disabled" });
+
+    // get latest CA certificate
+    const caCert = await certificateAuthorityCertDAL.findById(ca.activeCaCertId);
+
+    const serialNumber = crypto.randomBytes(32).toString("hex");
+
+    const certificateManagerKmsId = await getProjectKmsCertificateKeyId({
+      projectId: ca.projectId,
+      projectDAL,
+      kmsService
+    });
+
+    const kmsEncryptor = await kmsService.encryptWithKmsKey({
+      kmsId: certificateManagerKmsId
+    });
+
+    const { caPrivateKey, caPublicKey, caSecret } = await getCaCredentials({
+      caId: ca.id,
+      certificateAuthorityDAL,
+      certificateAuthoritySecretDAL,
+      projectDAL,
+      kmsService
+    });
+
+    const alg = keyAlgorithmToAlgCfg(ca.keyAlgorithm as CertKeyAlgorithm);
+
+    const kmsDecryptor = await kmsService.decryptWithKmsKey({
+      kmsId: certificateManagerKmsId
+    });
+    const decryptedCaCert = await kmsDecryptor({
+      cipherTextBlob: caCert.encryptedCertificate
+    });
+
+    const caCertObj = new x509.X509Certificate(decryptedCaCert);
+
+    let certificate = "";
+    let certificateChain = "";
+
+    switch (ca.type) {
+      case CaType.ROOT: {
+        if (new Date(notAfter) <= new Date(caCertObj.notAfter)) {
+          throw new BadRequestError({
+            message:
+              "New Root CA certificate must have notAfter date that is greater than the current certificate notAfter date"
+          });
+        }
+
+        const notBeforeDate = new Date();
+        const cert = await x509.X509CertificateGenerator.createSelfSigned({
+          name: ca.dn,
+          serialNumber,
+          notBefore: notBeforeDate,
+          notAfter: new Date(notAfter),
+          signingAlgorithm: alg,
+          keys: {
+            privateKey: caPrivateKey,
+            publicKey: caPublicKey
+          },
+          extensions: [
+            new x509.BasicConstraintsExtension(
+              true,
+              ca.maxPathLength === -1 || !ca.maxPathLength ? undefined : ca.maxPathLength,
+              true
+            ),
+            new x509.ExtendedKeyUsageExtension(["1.2.3.4.5.6.7", "2.3.4.5.6.7.8"], true),
+            // eslint-disable-next-line no-bitwise
+            new x509.KeyUsagesExtension(x509.KeyUsageFlags.keyCertSign | x509.KeyUsageFlags.cRLSign, true),
+            await x509.SubjectKeyIdentifierExtension.create(caPublicKey)
+          ]
+        });
+
+        const { cipherTextBlob: encryptedCertificate } = await kmsEncryptor({
+          plainText: Buffer.from(new Uint8Array(cert.rawData))
+        });
+
+        const { cipherTextBlob: encryptedCertificateChain } = await kmsEncryptor({
+          plainText: Buffer.alloc(0)
+        });
+
+        await certificateAuthorityDAL.transaction(async (tx) => {
+          const newCaCert = await certificateAuthorityCertDAL.create(
+            {
+              caId: ca.id,
+              encryptedCertificate,
+              encryptedCertificateChain,
+              version: caCert.version + 1,
+              caSecretId: caSecret.id
+            },
+            tx
+          );
+
+          await certificateAuthorityDAL.updateById(
+            ca.id,
+            {
+              activeCaCertId: newCaCert.id,
+              notBefore: notBeforeDate,
+              notAfter: new Date(notAfter)
+            },
+            tx
+          );
+        });
+
+        certificate = cert.toString("pem");
+        break;
+      }
+      case CaType.INTERMEDIATE: {
+        if (!ca.parentCaId) {
+          // TODO: look into optimal way to support renewal of intermediate CA with external parent CA
+          throw new BadRequestError({
+            message: "Failed to renew intermediate CA certificate with external parent CA"
+          });
+        }
+
+        const parentCa = await certificateAuthorityDAL.findById(ca.parentCaId);
+        const { caPrivateKey: parentCaPrivateKey } = await getCaCredentials({
+          caId: parentCa.id,
+          certificateAuthorityDAL,
+          certificateAuthoritySecretDAL,
+          projectDAL,
+          kmsService
+        });
+
+        // get latest parent CA certificate
+        if (!parentCa.activeCaCertId)
+          throw new BadRequestError({ message: "Parent CA does not have a certificate installed" });
+        const parentCaCert = await certificateAuthorityCertDAL.findById(parentCa.activeCaCertId);
+
+        const decryptedParentCaCert = await kmsDecryptor({
+          cipherTextBlob: parentCaCert.encryptedCertificate
+        });
+
+        const parentCaCertObj = new x509.X509Certificate(decryptedParentCaCert);
+
+        if (new Date(notAfter) <= new Date(caCertObj.notAfter)) {
+          throw new BadRequestError({
+            message:
+              "New Intermediate CA certificate must have notAfter date that is greater than the current certificate notAfter date"
+          });
+        }
+
+        if (new Date(notAfter) > new Date(parentCaCertObj.notAfter)) {
+          throw new BadRequestError({
+            message:
+              "New Intermediate CA certificate must have notAfter date that is equal to or smaller than the notAfter date of the parent CA certificate current certificate notAfter date"
+          });
+        }
+
+        const csrObj = await x509.Pkcs10CertificateRequestGenerator.create({
+          name: ca.dn,
+          keys: {
+            privateKey: caPrivateKey,
+            publicKey: caPublicKey
+          },
+          signingAlgorithm: alg,
+          extensions: [
+            // eslint-disable-next-line no-bitwise
+            new x509.KeyUsagesExtension(
+              x509.KeyUsageFlags.keyCertSign |
+                x509.KeyUsageFlags.cRLSign |
+                x509.KeyUsageFlags.digitalSignature |
+                x509.KeyUsageFlags.keyEncipherment
+            )
+          ],
+          attributes: [new x509.ChallengePasswordAttribute("password")]
+        });
+
+        const notBeforeDate = new Date();
+        const intermediateCert = await x509.X509CertificateGenerator.create({
+          serialNumber,
+          subject: csrObj.subject,
+          issuer: parentCaCertObj.subject,
+          notBefore: notBeforeDate,
+          notAfter: new Date(notAfter),
+          signingKey: parentCaPrivateKey,
+          publicKey: csrObj.publicKey,
+          signingAlgorithm: alg,
+          extensions: [
+            new x509.KeyUsagesExtension(
+              x509.KeyUsageFlags.keyCertSign |
+                x509.KeyUsageFlags.cRLSign |
+                x509.KeyUsageFlags.digitalSignature |
+                x509.KeyUsageFlags.keyEncipherment,
+              true
+            ),
+            new x509.BasicConstraintsExtension(
+              true,
+              ca.maxPathLength === -1 || !ca.maxPathLength ? undefined : ca.maxPathLength,
+              true
+            ),
+            await x509.AuthorityKeyIdentifierExtension.create(parentCaCertObj, false),
+            await x509.SubjectKeyIdentifierExtension.create(csrObj.publicKey)
+          ]
+        });
+
+        const { cipherTextBlob: encryptedCertificate } = await kmsEncryptor({
+          plainText: Buffer.from(new Uint8Array(intermediateCert.rawData))
+        });
+
+        const { caCert: parentCaCertificate, caCertChain: parentCaCertChain } = await getCaCertChain({
+          caCertId: parentCa.activeCaCertId,
+          certificateAuthorityDAL,
+          certificateAuthorityCertDAL,
+          projectDAL,
+          kmsService
+        });
+
+        certificateChain = `${parentCaCertificate}\n${parentCaCertChain}`.trim();
+
+        const { cipherTextBlob: encryptedCertificateChain } = await kmsEncryptor({
+          plainText: Buffer.from(certificateChain)
+        });
+
+        await certificateAuthorityDAL.transaction(async (tx) => {
+          const newCaCert = await certificateAuthorityCertDAL.create(
+            {
+              caId: ca.id,
+              encryptedCertificate,
+              encryptedCertificateChain,
+              version: caCert.version + 1,
+              caSecretId: caSecret.id
+            },
+            tx
+          );
+
+          await certificateAuthorityDAL.updateById(
+            ca.id,
+            {
+              activeCaCertId: newCaCert.id,
+              notBefore: notBeforeDate,
+              notAfter: new Date(notAfter)
+            },
+            tx
+          );
+        });
+
+        certificate = intermediateCert.toString("pem");
+        break;
+      }
+      default: {
+        throw new BadRequestError({
+          message: "Unrecognized CA type"
+        });
+      }
+    }
+
+    return {
+      certificate,
+      certificateChain,
+      serialNumber,
+      ca
+    };
+  };
+
+  const getCaCerts = async ({ caId, actorId, actorAuthMethod, actor, actorOrgId }: TGetCaCertsDTO) => {
     const ca = await certificateAuthorityDAL.findById(caId);
     if (!ca) throw new BadRequestError({ message: "CA not found" });
 
@@ -400,7 +699,7 @@ export const certificateAuthorityServiceFactory = ({
       ProjectPermissionSub.CertificateAuthorities
     );
 
-    const { caCert, caCertChain, serialNumber } = await getCaCertChain({
+    const caCertChains = await getCaCertChains({
       caId,
       certificateAuthorityDAL,
       certificateAuthorityCertDAL,
@@ -408,6 +707,41 @@ export const certificateAuthorityServiceFactory = ({
       kmsService
     });
 
+    return {
+      ca,
+      caCerts: caCertChains
+    };
+  };
+
+  /**
+   * Return current certificate and certificate chain for CA
+   */
+  const getCaCert = async ({ caId, actorId, actorAuthMethod, actor, actorOrgId }: TGetCaCertDTO) => {
+    const ca = await certificateAuthorityDAL.findById(caId);
+    if (!ca) throw new BadRequestError({ message: "CA not found" });
+    if (!ca.activeCaCertId) throw new BadRequestError({ message: "CA does not have a certificate installed" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      ca.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Read,
+      ProjectPermissionSub.CertificateAuthorities
+    );
+
+    const { caCert, caCertChain, serialNumber } = await getCaCertChain({
+      caCertId: ca.activeCaCertId,
+      certificateAuthorityDAL,
+      certificateAuthorityCertDAL,
+      projectDAL,
+      kmsService
+    });
+
     return {
       certificate: caCert,
       certificateChain: caCertChain,
@@ -447,6 +781,13 @@ export const certificateAuthorityServiceFactory = ({
     );
 
     if (ca.status === CaStatus.DISABLED) throw new BadRequestError({ message: "CA is disabled" });
+    if (!ca.activeCaCertId) throw new BadRequestError({ message: "CA does not have a certificate installed" });
+
+    const caCert = await certificateAuthorityCertDAL.findById(ca.activeCaCertId);
+
+    if (ca.notAfter && new Date() > new Date(ca.notAfter)) {
+      throw new BadRequestError({ message: "CA is expired" });
+    }
 
     const alg = keyAlgorithmToAlgCfg(ca.keyAlgorithm as CertKeyAlgorithm);
 
@@ -459,7 +800,6 @@ export const certificateAuthorityServiceFactory = ({
       kmsId: certificateManagerKmsId
     });
 
-    const caCert = await certificateAuthorityCertDAL.findOne({ caId: ca.id });
     const decryptedCaCert = await kmsDecryptor({
       cipherTextBlob: caCert.encryptedCertificate
     });
@@ -531,7 +871,7 @@ export const certificateAuthorityServiceFactory = ({
     });
 
     const { caCert: issuingCaCertificate, caCertChain } = await getCaCertChain({
-      caId,
+      caCertId: ca.activeCaCertId,
       certificateAuthorityDAL,
       certificateAuthorityCertDAL,
       projectDAL,
@@ -577,8 +917,7 @@ export const certificateAuthorityServiceFactory = ({
       ProjectPermissionSub.CertificateAuthorities
     );
 
-    const caCert = await certificateAuthorityCertDAL.findOne({ caId: ca.id });
-    if (caCert) throw new BadRequestError({ message: "CA has already imported a certificate" });
+    if (ca.activeCaCertId) throw new BadRequestError({ message: "CA has already imported a certificate" });
 
     const certObj = new x509.X509Certificate(certificate);
     const maxPathLength = certObj.getExtension(x509.BasicConstraintsExtension)?.pathLength;
@@ -625,12 +964,32 @@ export const certificateAuthorityServiceFactory = ({
       plainText: Buffer.from(certificateChain)
     });
 
+    // TODO: validate that latest key-pair of CA is used to sign the certificate
+    // once renewal with new key pair is supported
+    const { caSecret, caPublicKey } = await getCaCredentials({
+      caId: ca.id,
+      certificateAuthorityDAL,
+      certificateAuthoritySecretDAL,
+      projectDAL,
+      kmsService
+    });
+
+    const isCaAndCertPublicKeySame = Buffer.from(await crypto.subtle.exportKey("spki", caPublicKey)).equals(
+      Buffer.from(certObj.publicKey.rawData)
+    );
+
+    if (!isCaAndCertPublicKeySame) {
+      throw new BadRequestError({ message: "CA and certificate public key do not match" });
+    }
+
     await certificateAuthorityCertDAL.transaction(async (tx) => {
-      await certificateAuthorityCertDAL.create(
+      const newCaCert = await certificateAuthorityCertDAL.create(
         {
           caId: ca.id,
           encryptedCertificate,
-          encryptedCertificateChain
+          encryptedCertificateChain,
+          version: 1,
+          caSecretId: caSecret.id
         },
         tx
       );
@@ -643,7 +1002,8 @@ export const certificateAuthorityServiceFactory = ({
           notBefore: new Date(certObj.notBefore),
           notAfter: new Date(certObj.notAfter),
           serialNumber: certObj.serialNumber,
-          parentCaId: parentCa?.id
+          parentCaId: parentCa?.id,
+          activeCaCertId: newCaCert.id
         },
         tx
       );
@@ -658,6 +1018,8 @@ export const certificateAuthorityServiceFactory = ({
    */
   const issueCertFromCa = async ({
     caId,
+    certificateTemplateId,
+    pkiCollectionId,
     friendlyName,
     commonName,
     altNames,
@@ -669,8 +1031,27 @@ export const certificateAuthorityServiceFactory = ({
     actor,
     actorOrgId
   }: TIssueCertFromCaDTO) => {
-    const ca = await certificateAuthorityDAL.findById(caId);
-    if (!ca) throw new BadRequestError({ message: "CA not found" });
+    let ca: TCertificateAuthorities | undefined;
+    let certificateTemplate: TCertificateTemplates | undefined;
+    let collectionId = pkiCollectionId;
+
+    if (caId) {
+      ca = await certificateAuthorityDAL.findById(caId);
+    } else if (certificateTemplateId) {
+      certificateTemplate = await certificateTemplateDAL.getById(certificateTemplateId);
+      if (!certificateTemplate) {
+        throw new NotFoundError({
+          message: "Certificate template not found"
+        });
+      }
+
+      collectionId = certificateTemplate.pkiCollectionId as string;
+      ca = await certificateAuthorityDAL.findById(certificateTemplate.caId);
+    }
+
+    if (!ca) {
+      throw new BadRequestError({ message: "CA not found" });
+    }
 
     const { permission } = await permissionService.getProjectPermission(
       actor,
@@ -683,9 +1064,19 @@ export const certificateAuthorityServiceFactory = ({
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Create, ProjectPermissionSub.Certificates);
 
     if (ca.status === CaStatus.DISABLED) throw new BadRequestError({ message: "CA is disabled" });
+    if (!ca.activeCaCertId) throw new BadRequestError({ message: "CA does not have a certificate installed" });
+    const caCert = await certificateAuthorityCertDAL.findById(ca.activeCaCertId);
 
-    const caCert = await certificateAuthorityCertDAL.findOne({ caId: ca.id });
-    if (!caCert) throw new BadRequestError({ message: "CA does not have a certificate installed" });
+    if (ca.notAfter && new Date() > new Date(ca.notAfter)) {
+      throw new BadRequestError({ message: "CA is expired" });
+    }
+
+    // check PKI collection
+    if (collectionId) {
+      const pkiCollection = await pkiCollectionDAL.findById(collectionId);
+      if (!pkiCollection) throw new NotFoundError({ message: "PKI collection not found" });
+      if (pkiCollection.projectId !== ca.projectId) throw new BadRequestError({ message: "Invalid PKI collection" });
+    }
 
     const certificateManagerKmsId = await getProjectKmsCertificateKeyId({
       projectId: ca.projectId,
@@ -755,11 +1146,13 @@ export const certificateAuthorityServiceFactory = ({
       await x509.SubjectKeyIdentifierExtension.create(csrObj.publicKey)
     ];
 
+    let altNamesArray: {
+      type: "email" | "dns";
+      value: string;
+    }[] = [];
+
     if (altNames) {
-      const altNamesArray: {
-        type: "email" | "dns";
-        value: string;
-      }[] = altNames
+      altNamesArray = altNames
         .split(",")
         .map((name) => name.trim())
         .map((altName) => {
@@ -787,6 +1180,18 @@ export const certificateAuthorityServiceFactory = ({
       extensions.push(altNamesExtension);
     }
 
+    if (certificateTemplate) {
+      validateCertificateDetailsAgainstTemplate(
+        {
+          commonName,
+          notBeforeDate,
+          notAfterDate,
+          altNames: altNamesArray.map((entry) => entry.value)
+        },
+        certificateTemplate
+      );
+    }
+
     const serialNumber = crypto.randomBytes(32).toString("hex");
     const leafCert = await x509.X509CertificateGenerator.create({
       serialNumber,
@@ -813,7 +1218,9 @@ export const certificateAuthorityServiceFactory = ({
     await certificateDAL.transaction(async (tx) => {
       const cert = await certificateDAL.create(
         {
-          caId: ca.id,
+          caId: (ca as TCertificateAuthorities).id,
+          caCertId: caCert.id,
+          certificateTemplateId: certificateTemplate?.id,
           status: CertStatus.ACTIVE,
           friendlyName: friendlyName || commonName,
           commonName,
@@ -833,11 +1240,21 @@ export const certificateAuthorityServiceFactory = ({
         tx
       );
 
+      if (collectionId) {
+        await pkiCollectionItemDAL.create(
+          {
+            pkiCollectionId: collectionId,
+            certId: cert.id
+          },
+          tx
+        );
+      }
+
       return cert;
     });
 
     const { caCert: issuingCaCertificate, caCertChain } = await getCaCertChain({
-      caId: ca.id,
+      caCertId: caCert.id,
       certificateAuthorityDAL,
       certificateAuthorityCertDAL,
       projectDAL,
@@ -860,7 +1277,9 @@ export const certificateAuthorityServiceFactory = ({
    */
   const signCertFromCa = async ({
     caId,
+    certificateTemplateId,
     csr,
+    pkiCollectionId,
     friendlyName,
     commonName,
     altNames,
@@ -872,8 +1291,27 @@ export const certificateAuthorityServiceFactory = ({
     actor,
     actorOrgId
   }: TSignCertFromCaDTO) => {
-    const ca = await certificateAuthorityDAL.findById(caId);
-    if (!ca) throw new BadRequestError({ message: "CA not found" });
+    let ca: TCertificateAuthorities | undefined;
+    let certificateTemplate: TCertificateTemplates | undefined;
+    let collectionId = pkiCollectionId;
+
+    if (caId) {
+      ca = await certificateAuthorityDAL.findById(caId);
+    } else if (certificateTemplateId) {
+      certificateTemplate = await certificateTemplateDAL.getById(certificateTemplateId);
+      if (!certificateTemplate) {
+        throw new NotFoundError({
+          message: "Certificate template not found"
+        });
+      }
+
+      collectionId = certificateTemplate.pkiCollectionId as string;
+      ca = await certificateAuthorityDAL.findById(certificateTemplate.caId);
+    }
+
+    if (!ca) {
+      throw new BadRequestError({ message: "CA not found" });
+    }
 
     const { permission } = await permissionService.getProjectPermission(
       actor,
@@ -886,9 +1324,20 @@ export const certificateAuthorityServiceFactory = ({
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Create, ProjectPermissionSub.Certificates);
 
     if (ca.status === CaStatus.DISABLED) throw new BadRequestError({ message: "CA is disabled" });
+    if (!ca.activeCaCertId) throw new BadRequestError({ message: "CA does not have a certificate installed" });
 
-    const caCert = await certificateAuthorityCertDAL.findOne({ caId: ca.id });
-    if (!caCert) throw new BadRequestError({ message: "CA does not have a certificate installed" });
+    const caCert = await certificateAuthorityCertDAL.findById(ca.activeCaCertId);
+
+    if (ca.notAfter && new Date() > new Date(ca.notAfter)) {
+      throw new BadRequestError({ message: "CA is expired" });
+    }
+
+    // check PKI collection
+    if (pkiCollectionId) {
+      const pkiCollection = await pkiCollectionDAL.findById(pkiCollectionId);
+      if (!pkiCollection) throw new NotFoundError({ message: "PKI collection not found" });
+      if (pkiCollection.projectId !== ca.projectId) throw new BadRequestError({ message: "Invalid PKI collection" });
+    }
 
     const certificateManagerKmsId = await getProjectKmsCertificateKeyId({
       projectId: ca.projectId,
@@ -957,11 +1406,12 @@ export const certificateAuthorityServiceFactory = ({
       await x509.SubjectKeyIdentifierExtension.create(csrObj.publicKey)
     ];
 
+    let altNamesArray: {
+      type: "email" | "dns";
+      value: string;
+    }[] = [];
     if (altNames) {
-      const altNamesArray: {
-        type: "email" | "dns";
-        value: string;
-      }[] = altNames
+      altNamesArray = altNames
         .split(",")
         .map((name) => name.trim())
         .map((altName) => {
@@ -989,6 +1439,18 @@ export const certificateAuthorityServiceFactory = ({
       extensions.push(altNamesExtension);
     }
 
+    if (certificateTemplate) {
+      validateCertificateDetailsAgainstTemplate(
+        {
+          commonName: cn,
+          notBeforeDate,
+          notAfterDate,
+          altNames: altNamesArray.map((entry) => entry.value)
+        },
+        certificateTemplate
+      );
+    }
+
     const serialNumber = crypto.randomBytes(32).toString("hex");
     const leafCert = await x509.X509CertificateGenerator.create({
       serialNumber,
@@ -1012,7 +1474,9 @@ export const certificateAuthorityServiceFactory = ({
     await certificateDAL.transaction(async (tx) => {
       const cert = await certificateDAL.create(
         {
-          caId: ca.id,
+          caId: (ca as TCertificateAuthorities).id,
+          caCertId: caCert.id,
+          certificateTemplateId: certificateTemplate?.id,
           status: CertStatus.ACTIVE,
           friendlyName: friendlyName || csrObj.subject,
           commonName: cn,
@@ -1032,11 +1496,21 @@ export const certificateAuthorityServiceFactory = ({
         tx
       );
 
+      if (collectionId) {
+        await pkiCollectionItemDAL.create(
+          {
+            pkiCollectionId: collectionId,
+            certId: cert.id
+          },
+          tx
+        );
+      }
+
       return cert;
     });
 
     const { caCert: issuingCaCertificate, caCertChain } = await getCaCertChain({
-      caId: ca.id,
+      caCertId: ca.activeCaCertId,
       certificateAuthorityDAL,
       certificateAuthorityCertDAL,
       projectDAL,
@@ -1058,6 +1532,8 @@ export const certificateAuthorityServiceFactory = ({
     updateCaById,
     deleteCaById,
     getCaCsr,
+    renewCaCert,
+    getCaCerts,
     getCaCert,
     signIntermediate,
     importCertToCa,

backend/src/services/certificate-authority/certificate-authority-types.ts

@@ -20,6 +20,10 @@ export enum CaStatus {
   PENDING_CERTIFICATE = "pending-certificate"
 }
 
+export enum CaRenewalType {
+  EXISTING = "existing"
+}
+
 export type TCreateCaDTO = {
   projectSlug: string;
   type: CaType;
@@ -53,6 +57,16 @@ export type TGetCaCsrDTO = {
   caId: string;
 } & Omit<TProjectPermission, "projectId">;
 
+export type TRenewCaCertDTO = {
+  caId: string;
+  notAfter: string;
+  type: CaRenewalType;
+} & Omit<TProjectPermission, "projectId">;
+
+export type TGetCaCertsDTO = {
+  caId: string;
+} & Omit<TProjectPermission, "projectId">;
+
 export type TGetCaCertDTO = {
   caId: string;
 } & Omit<TProjectPermission, "projectId">;
@@ -72,7 +86,9 @@ export type TImportCertToCaDTO = {
 } & Omit<TProjectPermission, "projectId">;
 
 export type TIssueCertFromCaDTO = {
-  caId: string;
+  caId?: string;
+  certificateTemplateId?: string;
+  pkiCollectionId?: string;
   friendlyName?: string;
   commonName: string;
   altNames: string;
@@ -82,8 +98,10 @@ export type TIssueCertFromCaDTO = {
 } & Omit<TProjectPermission, "projectId">;
 
 export type TSignCertFromCaDTO = {
-  caId: string;
+  caId?: string;
   csr: string;
+  certificateTemplateId?: string;
+  pkiCollectionId?: string;
   friendlyName?: string;
   commonName?: string;
   altNames: string;
@@ -109,10 +127,18 @@ export type TGetCaCredentialsDTO = {
   kmsService: Pick<TKmsServiceFactory, "decryptWithKmsKey" | "generateKmsKey">;
 };
 
-export type TGetCaCertChainDTO = {
+export type TGetCaCertChainsDTO = {
   caId: string;
   certificateAuthorityDAL: Pick<TCertificateAuthorityDALFactory, "findById">;
-  certificateAuthorityCertDAL: Pick<TCertificateAuthorityCertDALFactory, "findOne">;
+  certificateAuthorityCertDAL: Pick<TCertificateAuthorityCertDALFactory, "find">;
+  projectDAL: Pick<TProjectDALFactory, "findOne" | "updateById" | "transaction">;
+  kmsService: Pick<TKmsServiceFactory, "decryptWithKmsKey" | "generateKmsKey">;
+};
+
+export type TGetCaCertChainDTO = {
+  caCertId: string;
+  certificateAuthorityDAL: Pick<TCertificateAuthorityDALFactory, "findById">;
+  certificateAuthorityCertDAL: Pick<TCertificateAuthorityCertDALFactory, "findById">;
   projectDAL: Pick<TProjectDALFactory, "findOne" | "updateById" | "transaction">;
   kmsService: Pick<TKmsServiceFactory, "decryptWithKmsKey" | "generateKmsKey">;
 };

backend/src/services/certificate-template/certificate-template-dal.ts

@@ -0,0 +1,57 @@
+import { TDbClient } from "@app/db";
+import { TableName } from "@app/db/schemas";
+import { DatabaseError } from "@app/lib/errors";
+import { ormify, selectAllTableCols } from "@app/lib/knex";
+
+export type TCertificateTemplateDALFactory = ReturnType<typeof certificateTemplateDALFactory>;
+
+export const certificateTemplateDALFactory = (db: TDbClient) => {
+  const certificateTemplateOrm = ormify(db, TableName.CertificateTemplate);
+
+  const getCertTemplatesByProjectId = async (projectId: string) => {
+    try {
+      const certTemplates = await db
+        .replicaNode()(TableName.CertificateTemplate)
+        .join(
+          TableName.CertificateAuthority,
+          `${TableName.CertificateAuthority}.id`,
+          `${TableName.CertificateTemplate}.caId`
+        )
+        .where(`${TableName.CertificateAuthority}.projectId`, "=", projectId)
+        .select(selectAllTableCols(TableName.CertificateTemplate))
+        .select(
+          db.ref("friendlyName").as("caName").withSchema(TableName.CertificateAuthority),
+          db.ref("projectId").withSchema(TableName.CertificateAuthority)
+        );
+
+      return certTemplates;
+    } catch (error) {
+      throw new DatabaseError({ error, name: "Get certificate templates by project ID" });
+    }
+  };
+
+  const getById = async (id: string) => {
+    try {
+      const certTemplate = await db
+        .replicaNode()(TableName.CertificateTemplate)
+        .join(
+          TableName.CertificateAuthority,
+          `${TableName.CertificateAuthority}.id`,
+          `${TableName.CertificateTemplate}.caId`
+        )
+        .where(`${TableName.CertificateTemplate}.id`, "=", id)
+        .select(selectAllTableCols(TableName.CertificateTemplate))
+        .select(
+          db.ref("projectId").withSchema(TableName.CertificateAuthority),
+          db.ref("friendlyName").as("caName").withSchema(TableName.CertificateAuthority)
+        )
+        .first();
+
+      return certTemplate;
+    } catch (error) {
+      throw new DatabaseError({ error, name: "Get certificate template by ID" });
+    }
+  };
+
+  return { ...certificateTemplateOrm, getCertTemplatesByProjectId, getById };
+};

backend/src/services/certificate-template/certificate-template-fns.ts

@@ -0,0 +1,36 @@
+import ms from "ms";
+
+import { TCertificateTemplates } from "@app/db/schemas";
+import { BadRequestError } from "@app/lib/errors";
+
+export const validateCertificateDetailsAgainstTemplate = (
+  cert: {
+    commonName: string;
+    notBeforeDate: Date;
+    notAfterDate: Date;
+    altNames: string[];
+  },
+  template: TCertificateTemplates
+) => {
+  const commonNameRegex = new RegExp(template.commonName);
+  if (!commonNameRegex.test(cert.commonName)) {
+    throw new BadRequestError({
+      message: "Invalid common name based on template policy"
+    });
+  }
+
+  if (cert.notAfterDate.getTime() - cert.notBeforeDate.getTime() > ms(template.ttl)) {
+    throw new BadRequestError({
+      message: "Invalid validity date based on template policy"
+    });
+  }
+
+  const subjectAlternativeNameRegex = new RegExp(template.subjectAlternativeName);
+  cert.altNames.forEach((altName) => {
+    if (!subjectAlternativeNameRegex.test(altName)) {
+      throw new BadRequestError({
+        message: "Invalid subject alternative name based on template policy"
+      });
+    }
+  });
+};

backend/src/services/certificate-template/certificate-template-schema.ts

@@ -0,0 +1,18 @@
+import z from "zod";
+
+import { CertificateTemplatesSchema } from "@app/db/schemas";
+
+export const sanitizedCertificateTemplate = CertificateTemplatesSchema.pick({
+  id: true,
+  caId: true,
+  name: true,
+  commonName: true,
+  subjectAlternativeName: true,
+  pkiCollectionId: true,
+  ttl: true
+}).merge(
+  z.object({
+    projectId: z.string(),
+    caName: z.string()
+  })
+);

backend/src/services/certificate-template/certificate-template-service.ts

@@ -0,0 +1,196 @@
+import { ForbiddenError } from "@casl/ability";
+
+import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
+import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
+import { BadRequestError, NotFoundError } from "@app/lib/errors";
+
+import { TCertificateAuthorityDALFactory } from "../certificate-authority/certificate-authority-dal";
+import { TCertificateTemplateDALFactory } from "./certificate-template-dal";
+import {
+  TCreateCertTemplateDTO,
+  TDeleteCertTemplateDTO,
+  TGetCertTemplateDTO,
+  TUpdateCertTemplateDTO
+} from "./certificate-template-types";
+
+type TCertificateTemplateServiceFactoryDep = {
+  certificateTemplateDAL: TCertificateTemplateDALFactory;
+  certificateAuthorityDAL: Pick<TCertificateAuthorityDALFactory, "findById">;
+  permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
+};
+
+export type TCertificateTemplateServiceFactory = ReturnType<typeof certificateTemplateServiceFactory>;
+
+export const certificateTemplateServiceFactory = ({
+  certificateTemplateDAL,
+  certificateAuthorityDAL,
+  permissionService
+}: TCertificateTemplateServiceFactoryDep) => {
+  const createCertTemplate = async ({
+    caId,
+    pkiCollectionId,
+    name,
+    commonName,
+    subjectAlternativeName,
+    ttl,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId
+  }: TCreateCertTemplateDTO) => {
+    const ca = await certificateAuthorityDAL.findById(caId);
+    if (!ca) {
+      throw new NotFoundError({
+        message: "CA not found"
+      });
+    }
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      ca.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Create,
+      ProjectPermissionSub.CertificateTemplates
+    );
+
+    const { id } = await certificateTemplateDAL.create({
+      caId,
+      pkiCollectionId,
+      name,
+      commonName,
+      subjectAlternativeName,
+      ttl
+    });
+
+    const certificateTemplate = await certificateTemplateDAL.getById(id);
+    if (!certificateTemplate) {
+      throw new NotFoundError({
+        message: "Certificate template not found"
+      });
+    }
+
+    return certificateTemplate;
+  };
+
+  const updateCertTemplate = async ({
+    id,
+    caId,
+    pkiCollectionId,
+    name,
+    commonName,
+    subjectAlternativeName,
+    ttl,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId
+  }: TUpdateCertTemplateDTO) => {
+    const certTemplate = await certificateTemplateDAL.getById(id);
+    if (!certTemplate) {
+      throw new NotFoundError({
+        message: "Certificate template not found."
+      });
+    }
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      certTemplate.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Edit,
+      ProjectPermissionSub.CertificateTemplates
+    );
+
+    if (caId) {
+      const ca = await certificateAuthorityDAL.findById(caId);
+      if (!ca || ca.projectId !== certTemplate.projectId) {
+        throw new BadRequestError({
+          message: "Invalid CA"
+        });
+      }
+    }
+
+    await certificateTemplateDAL.updateById(certTemplate.id, {
+      caId,
+      pkiCollectionId,
+      commonName,
+      subjectAlternativeName,
+      name,
+      ttl
+    });
+
+    const updatedTemplate = await certificateTemplateDAL.getById(id);
+    if (!updatedTemplate) {
+      throw new NotFoundError({
+        message: "Certificate template not found"
+      });
+    }
+
+    return updatedTemplate;
+  };
+
+  const deleteCertTemplate = async ({ id, actorId, actorAuthMethod, actor, actorOrgId }: TDeleteCertTemplateDTO) => {
+    const certTemplate = await certificateTemplateDAL.getById(id);
+    if (!certTemplate) {
+      throw new NotFoundError({
+        message: "Certificate template not found."
+      });
+    }
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      certTemplate.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Delete,
+      ProjectPermissionSub.CertificateTemplates
+    );
+
+    await certificateTemplateDAL.deleteById(certTemplate.id);
+
+    return certTemplate;
+  };
+
+  const getCertTemplate = async ({ id, actorId, actorAuthMethod, actor, actorOrgId }: TGetCertTemplateDTO) => {
+    const certTemplate = await certificateTemplateDAL.getById(id);
+    if (!certTemplate) {
+      throw new NotFoundError({
+        message: "Certificate template not found."
+      });
+    }
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      certTemplate.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Read,
+      ProjectPermissionSub.CertificateTemplates
+    );
+
+    return certTemplate;
+  };
+
+  return {
+    createCertTemplate,
+    getCertTemplate,
+    deleteCertTemplate,
+    updateCertTemplate
+  };
+};

backend/src/services/certificate-template/certificate-template-types.ts

@@ -0,0 +1,28 @@
+import { TProjectPermission } from "@app/lib/types";
+
+export type TCreateCertTemplateDTO = {
+  caId: string;
+  pkiCollectionId?: string;
+  name: string;
+  commonName: string;
+  subjectAlternativeName: string;
+  ttl: string;
+} & Omit<TProjectPermission, "projectId">;
+
+export type TUpdateCertTemplateDTO = {
+  id: string;
+  caId?: string;
+  pkiCollectionId?: string;
+  name?: string;
+  commonName?: string;
+  subjectAlternativeName?: string;
+  ttl?: string;
+} & Omit<TProjectPermission, "projectId">;
+
+export type TGetCertTemplateDTO = {
+  id: string;
+} & Omit<TProjectPermission, "projectId">;
+
+export type TDeleteCertTemplateDTO = {
+  id: string;
+} & Omit<TProjectPermission, "projectId">;

backend/src/services/certificate-template/certificate-template-validators.ts

@@ -0,0 +1,14 @@
+import safe from "safe-regex";
+import z from "zod";
+
+export const validateTemplateRegexField = z
+  .string()
+  .min(1)
+  .max(100)
+  .regex(/^[a-zA-Z0-9 *@\-\\.\\]+$/, {
+    message: "Invalid pattern: only alphanumeric characters, spaces, *, ., @, -, and \\ are allowed."
+  })
+  // we ensure that the inputted pattern is computationally safe by limiting star height to 1
+  .refine((v) => safe(v), {
+    message: "Unsafe REGEX pattern"
+  });

backend/src/services/certificate/certificate-service.ts

@@ -21,7 +21,7 @@ type TCertificateServiceFactoryDep = {
   certificateDAL: Pick<TCertificateDALFactory, "findOne" | "deleteById" | "update" | "find">;
   certificateBodyDAL: Pick<TCertificateBodyDALFactory, "findOne">;
   certificateAuthorityDAL: Pick<TCertificateAuthorityDALFactory, "findById">;
-  certificateAuthorityCertDAL: Pick<TCertificateAuthorityCertDALFactory, "findOne">;
+  certificateAuthorityCertDAL: Pick<TCertificateAuthorityCertDALFactory, "findById">;
   certificateAuthorityCrlDAL: Pick<TCertificateAuthorityCrlDALFactory, "update">;
   certificateAuthoritySecretDAL: Pick<TCertificateAuthoritySecretDALFactory, "findOne">;
   projectDAL: Pick<TProjectDALFactory, "findOne" | "updateById" | "findById" | "transaction">;
@@ -180,7 +180,7 @@ export const certificateServiceFactory = ({
     const certObj = new x509.X509Certificate(decryptedCert);
 
     const { caCert, caCertChain } = await getCaCertChain({
-      caId: ca.id,
+      caCertId: cert.caCertId,
       certificateAuthorityDAL,
       certificateAuthorityCertDAL,
       projectDAL,

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

@@ -1,7 +1,7 @@
 import { Knex } from "knex";
 
 import { TDbClient } from "@app/db";
-import { TableName } from "@app/db/schemas";
+import { TableName, TUserEncryptionKeys } from "@app/db/schemas";
 import { DatabaseError } from "@app/lib/errors";
 import { ormify, sqlNestRelationships } from "@app/lib/knex";
 
@@ -95,5 +95,107 @@ export const groupProjectDALFactory = (db: TDbClient) => {
     }
   };
 
-  return { ...groupProjectOrm, findByProjectId };
+  // The GroupProjectMembership table has a reference to the project (projectId) AND the group (groupId).
+  // We need to join the GroupProjectMembership table with the Groups table to get the group name and slug.
+  // We also need to join the GroupProjectMembershipRole table to get the role of the group in the project.
+  const findAllProjectGroupMembers = async (projectId: string) => {
+    const docs = await db(TableName.UserGroupMembership)
+      // Join the GroupProjectMembership table with the Groups table to get the group name and slug.
+      .join(
+        TableName.GroupProjectMembership,
+        `${TableName.UserGroupMembership}.groupId`,
+        `${TableName.GroupProjectMembership}.groupId` // this gives us access to the project id in the group membership
+      )
+
+      .join(TableName.Project, `${TableName.GroupProjectMembership}.projectId`, `${TableName.Project}.id`)
+
+      .where(`${TableName.GroupProjectMembership}.projectId`, projectId)
+
+      .join(TableName.Users, `${TableName.UserGroupMembership}.userId`, `${TableName.Users}.id`)
+      .join<TUserEncryptionKeys>(
+        TableName.UserEncryptionKey,
+        `${TableName.UserEncryptionKey}.userId`,
+        `${TableName.Users}.id`
+      )
+      .join(
+        TableName.GroupProjectMembershipRole,
+        `${TableName.GroupProjectMembershipRole}.projectMembershipId`,
+        `${TableName.GroupProjectMembership}.id`
+      )
+      .leftJoin(
+        TableName.ProjectRoles,
+        `${TableName.GroupProjectMembershipRole}.customRoleId`,
+        `${TableName.ProjectRoles}.id`
+      )
+      .select(
+        db.ref("id").withSchema(TableName.GroupProjectMembership),
+        db.ref("isGhost").withSchema(TableName.Users),
+        db.ref("username").withSchema(TableName.Users),
+        db.ref("email").withSchema(TableName.Users),
+        db.ref("publicKey").withSchema(TableName.UserEncryptionKey),
+        db.ref("firstName").withSchema(TableName.Users),
+        db.ref("lastName").withSchema(TableName.Users),
+        db.ref("id").withSchema(TableName.Users).as("userId"),
+        db.ref("role").withSchema(TableName.GroupProjectMembershipRole),
+        db.ref("id").withSchema(TableName.GroupProjectMembershipRole).as("membershipRoleId"),
+        db.ref("customRoleId").withSchema(TableName.GroupProjectMembershipRole),
+        db.ref("name").withSchema(TableName.ProjectRoles).as("customRoleName"),
+        db.ref("slug").withSchema(TableName.ProjectRoles).as("customRoleSlug"),
+        db.ref("temporaryMode").withSchema(TableName.GroupProjectMembershipRole),
+        db.ref("isTemporary").withSchema(TableName.GroupProjectMembershipRole),
+        db.ref("temporaryRange").withSchema(TableName.GroupProjectMembershipRole),
+        db.ref("temporaryAccessStartTime").withSchema(TableName.GroupProjectMembershipRole),
+        db.ref("temporaryAccessEndTime").withSchema(TableName.GroupProjectMembershipRole),
+        db.ref("name").as("projectName").withSchema(TableName.Project)
+      )
+      .where({ isGhost: false });
+
+    const members = sqlNestRelationships({
+      data: docs,
+      parentMapper: ({ email, firstName, username, lastName, publicKey, isGhost, id, userId, projectName }) => ({
+        isGroupMember: true,
+        id,
+        userId,
+        projectId,
+        project: {
+          id: projectId,
+          name: projectName
+        },
+        user: { email, username, firstName, lastName, id: userId, publicKey, isGhost }
+      }),
+      key: "id",
+      childrenMapper: [
+        {
+          label: "roles" as const,
+          key: "membershipRoleId",
+          mapper: ({
+            role,
+            customRoleId,
+            customRoleName,
+            customRoleSlug,
+            membershipRoleId,
+            temporaryRange,
+            temporaryMode,
+            temporaryAccessEndTime,
+            temporaryAccessStartTime,
+            isTemporary
+          }) => ({
+            id: membershipRoleId,
+            role,
+            customRoleId,
+            customRoleName,
+            customRoleSlug,
+            temporaryRange,
+            temporaryMode,
+            temporaryAccessEndTime,
+            temporaryAccessStartTime,
+            isTemporary
+          })
+        }
+      ]
+    });
+    return members;
+  };
+
+  return { ...groupProjectOrm, findByProjectId, findAllProjectGroupMembers };
 };

backend/src/services/org/org-fns.ts

@@ -1,4 +1,5 @@
 import { TLicenseServiceFactory } from "@app/ee/services/license/license-service";
+import { TProjectUserAdditionalPrivilegeDALFactory } from "@app/ee/services/project-user-additional-privilege/project-user-additional-privilege-dal";
 import { TOrgDALFactory } from "@app/services/org/org-dal";
 import { TProjectKeyDALFactory } from "@app/services/project-key/project-key-dal";
 import { TProjectMembershipDALFactory } from "@app/services/project-membership/project-membership-dal";
@@ -12,6 +13,7 @@ type TDeleteOrgMembership = {
   projectKeyDAL: Pick<TProjectKeyDALFactory, "find" | "delete">;
   userAliasDAL: Pick<TUserAliasDALFactory, "delete">;
   licenseService: Pick<TLicenseServiceFactory, "updateSubscriptionOrgMemberCount">;
+  projectUserAdditionalPrivilegeDAL: Pick<TProjectUserAdditionalPrivilegeDALFactory, "delete">;
 };
 
 export const deleteOrgMembershipFn = async ({
@@ -19,6 +21,7 @@ export const deleteOrgMembershipFn = async ({
   orgId,
   orgDAL,
   projectMembershipDAL,
+  projectUserAdditionalPrivilegeDAL,
   projectKeyDAL,
   userAliasDAL,
   licenseService
@@ -39,6 +42,13 @@ export const deleteOrgMembershipFn = async ({
       tx
     );
 
+    await projectUserAdditionalPrivilegeDAL.delete(
+      {
+        userId: orgMembership.userId
+      },
+      tx
+    );
+
     // Get all the project memberships of the user in the organization
     const projectMemberships = await projectMembershipDAL.findProjectMembershipsByUserId(orgId, orgMembership.userId);
 

backend/src/services/org/org-service.ts

@@ -10,6 +10,7 @@ import { TGroupDALFactory } from "@app/ee/services/group/group-dal";
 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 { TProjectUserAdditionalPrivilegeDALFactory } from "@app/ee/services/project-user-additional-privilege/project-user-additional-privilege-dal";
 import { TSamlConfigDALFactory } from "@app/ee/services/saml-config/saml-config-dal";
 import { getConfig } from "@app/lib/config/env";
 import { generateAsymmetricKeyPair } from "@app/lib/crypto";
@@ -67,6 +68,7 @@ type TOrgServiceFactoryDep = {
     TLicenseServiceFactory,
     "getPlan" | "updateSubscriptionOrgMemberCount" | "generateOrgCustomerId" | "removeOrgCustomer"
   >;
+  projectUserAdditionalPrivilegeDAL: Pick<TProjectUserAdditionalPrivilegeDALFactory, "delete">;
 };
 
 export type TOrgServiceFactory = ReturnType<typeof orgServiceFactory>;
@@ -84,6 +86,7 @@ export const orgServiceFactory = ({
   projectMembershipDAL,
   projectKeyDAL,
   orgMembershipDAL,
+  projectUserAdditionalPrivilegeDAL,
   tokenService,
   orgBotDAL,
   licenseService,
@@ -632,6 +635,7 @@ export const orgServiceFactory = ({
       orgId,
       orgDAL,
       projectMembershipDAL,
+      projectUserAdditionalPrivilegeDAL,
       projectKeyDAL,
       userAliasDAL,
       licenseService

backend/src/services/pki-alert/expiring-pki-item-alert-queue.ts

@@ -0,0 +1,48 @@
+import { logger } from "@app/lib/logger";
+import { QueueJobs, QueueName, TQueueServiceFactory } from "@app/queue";
+import { TPkiAlertServiceFactory } from "@app/services/pki-alert/pki-alert-service";
+
+type TDailyExpiringPkiItemAlertQueueServiceFactoryDep = {
+  queueService: TQueueServiceFactory;
+  pkiAlertService: Pick<TPkiAlertServiceFactory, "sendPkiItemExpiryNotices">;
+};
+
+export type TDailyExpiringPkiItemAlertQueueServiceFactory = ReturnType<
+  typeof dailyExpiringPkiItemAlertQueueServiceFactory
+>;
+
+export const dailyExpiringPkiItemAlertQueueServiceFactory = ({
+  queueService,
+  pkiAlertService
+}: TDailyExpiringPkiItemAlertQueueServiceFactoryDep) => {
+  queueService.start(QueueName.DailyExpiringPkiItemAlert, async () => {
+    logger.info(`${QueueName.DailyExpiringPkiItemAlert}: queue task started`);
+    await pkiAlertService.sendPkiItemExpiryNotices();
+    logger.info(`${QueueName.DailyExpiringPkiItemAlert}: queue task completed`);
+  });
+
+  // we do a repeat cron job in utc timezone at 12 Midnight each day
+  const startSendingAlerts = async () => {
+    // clear previous job
+    await queueService.stopRepeatableJob(
+      QueueName.DailyExpiringPkiItemAlert,
+      QueueJobs.DailyExpiringPkiItemAlert,
+      { pattern: "0 0 * * *", utc: true },
+      QueueName.DailyExpiringPkiItemAlert // just a job id
+    );
+
+    await queueService.queue(QueueName.DailyExpiringPkiItemAlert, QueueJobs.DailyExpiringPkiItemAlert, undefined, {
+      delay: 5000,
+      jobId: QueueName.DailyExpiringPkiItemAlert,
+      repeat: { pattern: "0 0 * * *", utc: true }
+    });
+  };
+
+  queueService.listen(QueueName.DailyExpiringPkiItemAlert, "failed", (_, err) => {
+    logger.error(err, `${QueueName.DailyExpiringPkiItemAlert}: Expiring PKI item alert failed`);
+  });
+
+  return {
+    startSendingAlerts
+  };
+};

backend/src/services/pki-alert/pki-alert-dal.ts

@@ -0,0 +1,84 @@
+import { TDbClient } from "@app/db";
+import { TableName } from "@app/db/schemas";
+import { DatabaseError } from "@app/lib/errors";
+import { ormify } from "@app/lib/knex";
+
+import { PkiItemType } from "../pki-collection/pki-collection-types";
+
+export type TPkiAlertDALFactory = ReturnType<typeof pkiAlertDALFactory>;
+
+export const pkiAlertDALFactory = (db: TDbClient) => {
+  const pkiAlertOrm = ormify(db, TableName.PkiAlert);
+
+  const getExpiringPkiCollectionItemsForAlerting = async () => {
+    try {
+      type AlertItem = {
+        type: PkiItemType;
+        id: string; // id of the CA or certificate
+        expiryDate: Date;
+        serialNumber: string;
+        friendlyName: string;
+        pkiCollectionId: string;
+        alertId: string;
+        alertName: string;
+        alertBeforeDays: number;
+        recipientEmails: string;
+      };
+
+      // gets CAs and certificates as part of PKI collection items
+      const combinedQuery = db
+        .replicaNode()
+        .select(
+          db.raw("? as type", [PkiItemType.CA]),
+          `${PkiItemType.CA}.id`,
+          `${PkiItemType.CA}.notAfter as expiryDate`,
+          `${PkiItemType.CA}.serialNumber`,
+          `${PkiItemType.CA}.friendlyName`,
+          "pci.pkiCollectionId"
+        )
+        .from(`${TableName.CertificateAuthority} as ${PkiItemType.CA}`)
+        .join(`${TableName.PkiCollectionItem} as pci`, `${PkiItemType.CA}.id`, "pci.caId")
+        .unionAll((qb) => {
+          void qb
+            .select(
+              db.raw("? as type", [PkiItemType.CERTIFICATE]),
+              `${PkiItemType.CERTIFICATE}.id`,
+              `${PkiItemType.CERTIFICATE}.notAfter as expiryDate`,
+              `${PkiItemType.CERTIFICATE}.serialNumber`,
+              `${PkiItemType.CERTIFICATE}.friendlyName`,
+              "pci.pkiCollectionId"
+            )
+            .from(`${TableName.Certificate} as ${PkiItemType.CERTIFICATE}`)
+            .join(`${TableName.PkiCollectionItem} as pci`, `${PkiItemType.CERTIFICATE}.id`, "pci.certId");
+        });
+
+      /**
+       * Gets alerts to send based on alertBeforeDays on PKI alerts connected to PKI collection items
+       * Note: Results are clamped to 1-day window to avoid sending multiple alerts for the same item
+       */
+      const alertQuery = db
+        .replicaNode()
+        .select("combined.*", "pa.id as alertId", "pa.name as alertName", "pa.alertBeforeDays", "pa.recipientEmails")
+        .from(db.raw("(?) as combined", [combinedQuery]))
+        .join(`${TableName.PkiAlert} as pa`, "combined.pkiCollectionId", "pa.pkiCollectionId")
+        .whereRaw(
+          `
+          combined."expiryDate" <= CURRENT_TIMESTAMP + (pa."alertBeforeDays" * INTERVAL '1 day')
+          AND combined."expiryDate" > CURRENT_TIMESTAMP + ((pa."alertBeforeDays" - 1) * INTERVAL '1 day')
+        `
+        )
+        .orderBy("combined.expiryDate");
+
+      const results = (await alertQuery) as AlertItem[];
+
+      return results;
+    } catch (error) {
+      throw new DatabaseError({ error, name: "Get expiring PKI collection items for alerting" });
+    }
+  };
+
+  return {
+    getExpiringPkiCollectionItemsForAlerting,
+    ...pkiAlertOrm
+  };
+};

backend/src/services/pki-alert/pki-alert-service.ts

@@ -0,0 +1,182 @@
+import { ForbiddenError } from "@casl/ability";
+
+import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
+import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
+import { NotFoundError, UnauthorizedError } from "@app/lib/errors";
+import { groupBy } from "@app/lib/fn";
+import { TPkiCollectionDALFactory } from "@app/services/pki-collection/pki-collection-dal";
+import { pkiItemTypeToNameMap } from "@app/services/pki-collection/pki-collection-types";
+import { SmtpTemplates, TSmtpService } from "@app/services/smtp/smtp-service";
+
+import { TPkiAlertDALFactory } from "./pki-alert-dal";
+import { TCreateAlertDTO, TDeleteAlertDTO, TGetAlertByIdDTO, TUpdateAlertDTO } from "./pki-alert-types";
+
+type TPkiAlertServiceFactoryDep = {
+  pkiAlertDAL: Pick<
+    TPkiAlertDALFactory,
+    "create" | "findById" | "updateById" | "deleteById" | "getExpiringPkiCollectionItemsForAlerting"
+  >;
+  pkiCollectionDAL: Pick<TPkiCollectionDALFactory, "findById">;
+  permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
+  smtpService: Pick<TSmtpService, "sendMail">;
+};
+
+export type TPkiAlertServiceFactory = ReturnType<typeof pkiAlertServiceFactory>;
+
+export const pkiAlertServiceFactory = ({
+  pkiAlertDAL,
+  pkiCollectionDAL,
+  permissionService,
+  smtpService
+}: TPkiAlertServiceFactoryDep) => {
+  const sendPkiItemExpiryNotices = async () => {
+    const allAlertItems = await pkiAlertDAL.getExpiringPkiCollectionItemsForAlerting();
+
+    const flattenedResults = allAlertItems.flatMap(({ recipientEmails, ...item }) =>
+      recipientEmails.split(",").map((email) => ({
+        ...item,
+        recipientEmail: email.trim()
+      }))
+    );
+
+    const groupedByEmail = groupBy(flattenedResults, (item) => item.recipientEmail);
+
+    for await (const [email, items] of Object.entries(groupedByEmail)) {
+      const groupedByAlert = groupBy(items, (item) => item.alertId);
+      for await (const [, alertItems] of Object.entries(groupedByAlert)) {
+        await smtpService.sendMail({
+          recipients: [email],
+          subjectLine: `Infisical CA/Certificate expiration notice: ${alertItems[0].alertName}`,
+          substitutions: {
+            alertName: alertItems[0].alertName,
+            alertBeforeDays: items[0].alertBeforeDays,
+            items: alertItems.map((alertItem) => ({
+              ...alertItem,
+              type: pkiItemTypeToNameMap[alertItem.type],
+              expiryDate: new Date(alertItem.expiryDate).toString()
+            }))
+          },
+          template: SmtpTemplates.PkiExpirationAlert
+        });
+      }
+    }
+  };
+
+  const createPkiAlert = async ({
+    projectId,
+    name,
+    pkiCollectionId,
+    alertBeforeDays,
+    emails,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId
+  }: TCreateAlertDTO) => {
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Create, ProjectPermissionSub.PkiAlerts);
+
+    const pkiCollection = await pkiCollectionDAL.findById(pkiCollectionId);
+    if (!pkiCollection) throw new NotFoundError({ message: "PKI collection not found" });
+    if (pkiCollection.projectId !== projectId)
+      throw new UnauthorizedError({ message: "PKI collection not found in project" });
+
+    const alert = await pkiAlertDAL.create({
+      projectId,
+      pkiCollectionId,
+      name,
+      alertBeforeDays,
+      recipientEmails: emails.join(",")
+    });
+    return alert;
+  };
+
+  const getPkiAlertById = async ({ alertId, actorId, actorAuthMethod, actor, actorOrgId }: TGetAlertByIdDTO) => {
+    const alert = await pkiAlertDAL.findById(alertId);
+    if (!alert) throw new NotFoundError({ message: "Alert not found" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      alert.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.PkiAlerts);
+    return alert;
+  };
+
+  const updatePkiAlert = async ({
+    alertId,
+    name,
+    pkiCollectionId,
+    alertBeforeDays,
+    emails,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId
+  }: TUpdateAlertDTO) => {
+    let alert = await pkiAlertDAL.findById(alertId);
+    if (!alert) throw new NotFoundError({ message: "Alert not found" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      alert.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.PkiAlerts);
+
+    if (pkiCollectionId) {
+      const pkiCollection = await pkiCollectionDAL.findById(pkiCollectionId);
+      if (!pkiCollection) throw new NotFoundError({ message: "PKI collection not found" });
+      if (pkiCollection.projectId !== alert.projectId)
+        throw new UnauthorizedError({ message: "PKI collection not found in project" });
+    }
+
+    alert = await pkiAlertDAL.updateById(alertId, {
+      name,
+      alertBeforeDays,
+      ...(pkiCollectionId && { pkiCollectionId }),
+      ...(emails && { recipientEmails: emails.join(",") })
+    });
+
+    return alert;
+  };
+
+  const deletePkiAlert = async ({ alertId, actorId, actorAuthMethod, actor, actorOrgId }: TDeleteAlertDTO) => {
+    let alert = await pkiAlertDAL.findById(alertId);
+    if (!alert) throw new NotFoundError({ message: "Alert not found" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      alert.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Delete, ProjectPermissionSub.PkiAlerts);
+    alert = await pkiAlertDAL.deleteById(alertId);
+    return alert;
+  };
+
+  return {
+    sendPkiItemExpiryNotices,
+    createPkiAlert,
+    getPkiAlertById,
+    updatePkiAlert,
+    deletePkiAlert
+  };
+};

backend/src/services/pki-alert/pki-alert-types.ts

@@ -0,0 +1,24 @@
+import { TProjectPermission } from "@app/lib/types";
+
+export type TCreateAlertDTO = {
+  name: string;
+  pkiCollectionId: string;
+  alertBeforeDays: number;
+  emails: string[];
+} & TProjectPermission;
+
+export type TGetAlertByIdDTO = {
+  alertId: string;
+} & Omit<TProjectPermission, "projectId">;
+
+export type TUpdateAlertDTO = {
+  alertId: string;
+  name?: string;
+  pkiCollectionId?: string;
+  alertBeforeDays?: number;
+  emails?: string[];
+} & Omit<TProjectPermission, "projectId">;
+
+export type TDeleteAlertDTO = {
+  alertId: string;
+} & Omit<TProjectPermission, "projectId">;

backend/src/services/pki-collection/pki-collection-dal.ts

@@ -0,0 +1,13 @@
+import { TDbClient } from "@app/db";
+import { TableName } from "@app/db/schemas";
+import { ormify } from "@app/lib/knex";
+
+export type TPkiCollectionDALFactory = ReturnType<typeof pkiCollectionDALFactory>;
+
+export const pkiCollectionDALFactory = (db: TDbClient) => {
+  const pkiCollectionOrm = ormify(db, TableName.PkiCollection);
+
+  return {
+    ...pkiCollectionOrm
+  };
+};

backend/src/services/pki-collection/pki-collection-fns.ts

@@ -0,0 +1,30 @@
+import { TPkiCollectionItems } from "@app/db/schemas";
+
+import { PkiItemType } from "./pki-collection-types";
+
+/**
+ * Transforms a PKI Collection Item from the database to the expected API response format
+ */
+export const transformPkiCollectionItem = (pkiCollectionItem: TPkiCollectionItems) => {
+  let type: PkiItemType;
+  let itemId: string;
+
+  if (pkiCollectionItem.caId) {
+    type = PkiItemType.CA;
+    itemId = pkiCollectionItem.caId;
+  } else if (pkiCollectionItem.certId) {
+    type = PkiItemType.CERTIFICATE;
+    itemId = pkiCollectionItem.certId;
+  } else {
+    throw new Error("Invalid PKI Collection Item: must have either caId or certId");
+  }
+
+  return {
+    id: pkiCollectionItem.id,
+    pkiCollectionId: pkiCollectionItem.pkiCollectionId,
+    type,
+    itemId,
+    createdAt: pkiCollectionItem.createdAt,
+    updatedAt: pkiCollectionItem.updatedAt
+  };
+};

backend/src/services/pki-collection/pki-collection-item-dal.ts

@@ -0,0 +1,93 @@
+import { TDbClient } from "@app/db";
+import { TableName, TPkiCollectionItems } from "@app/db/schemas";
+import { DatabaseError } from "@app/lib/errors";
+import { ormify } from "@app/lib/knex";
+
+import { PkiItemType } from "./pki-collection-types";
+
+export type TPkiCollectionItemDALFactory = ReturnType<typeof pkiCollectionItemDALFactory>;
+
+export const pkiCollectionItemDALFactory = (db: TDbClient) => {
+  const pkiCollectionItemOrm = ormify(db, TableName.PkiCollectionItem);
+
+  const findPkiCollectionItems = async ({
+    collectionId,
+    type,
+    offset,
+    limit
+  }: {
+    collectionId: string;
+    type?: PkiItemType;
+    offset?: number;
+    limit?: number;
+  }) => {
+    try {
+      const query = db
+        .replicaNode()(TableName.PkiCollectionItem)
+        .select(
+          "pki_collection_items.*",
+          db.raw(
+            `COALESCE("${TableName.CertificateAuthority}"."notBefore", "${TableName.Certificate}"."notBefore") as "notBefore"`
+          ),
+          db.raw(
+            `COALESCE("${TableName.CertificateAuthority}"."notAfter", "${TableName.Certificate}"."notAfter") as "notAfter"`
+          ),
+          db.raw(
+            `COALESCE("${TableName.CertificateAuthority}"."friendlyName", "${TableName.Certificate}"."friendlyName") as "friendlyName"`
+          )
+        )
+        .leftJoin(
+          TableName.CertificateAuthority,
+          `${TableName.PkiCollectionItem}.caId`,
+          `${TableName.CertificateAuthority}.id`
+        )
+        .leftJoin(TableName.Certificate, `${TableName.PkiCollectionItem}.certId`, `${TableName.Certificate}.id`)
+        .where((builder) => {
+          void builder.where(`${TableName.PkiCollectionItem}.pkiCollectionId`, collectionId);
+          if (type === PkiItemType.CA) {
+            void builder.whereNull(`${TableName.PkiCollectionItem}.certId`);
+          } else if (type === PkiItemType.CERTIFICATE) {
+            void builder.whereNull(`${TableName.PkiCollectionItem}.caId`);
+          }
+        });
+
+      if (offset) {
+        void query.offset(offset);
+      }
+      if (limit) {
+        void query.limit(limit);
+      }
+
+      void query.orderBy(`${TableName.PkiCollectionItem}.createdAt`, "desc");
+
+      const result = await query;
+      return result as (TPkiCollectionItems & { notAfter: Date; notBefore: Date; friendlyName: string })[];
+    } catch (error) {
+      throw new DatabaseError({ error, name: "Find all PKI collection items" });
+    }
+  };
+
+  const countItemsInPkiCollection = async (collectionId: string) => {
+    try {
+      interface CountResult {
+        count: string;
+      }
+
+      const query = db
+        .replicaNode()(TableName.PkiCollectionItem)
+        .where(`${TableName.PkiCollectionItem}.pkiCollectionId`, collectionId);
+
+      const count = await query.count("*").first();
+
+      return parseInt((count as unknown as CountResult).count || "0", 10);
+    } catch (error) {
+      throw new DatabaseError({ error, name: "Count all PKI collection items" });
+    }
+  };
+
+  return {
+    ...pkiCollectionItemOrm,
+    findPkiCollectionItems,
+    countItemsInPkiCollection
+  };
+};

backend/src/services/pki-collection/pki-collection-service.ts

@@ -0,0 +1,331 @@
+import { ForbiddenError } from "@casl/ability";
+
+import { TPkiCollectionItems } from "@app/db/schemas";
+import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
+import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
+import { BadRequestError, NotFoundError } from "@app/lib/errors";
+import { TCertificateDALFactory } from "@app/services/certificate/certificate-dal";
+import { TCertificateAuthorityDALFactory } from "@app/services/certificate-authority/certificate-authority-dal";
+
+import { TPkiCollectionDALFactory } from "./pki-collection-dal";
+import { transformPkiCollectionItem } from "./pki-collection-fns";
+import { TPkiCollectionItemDALFactory } from "./pki-collection-item-dal";
+import {
+  PkiItemType,
+  TAddItemToPkiCollectionDTO,
+  TCreatePkiCollectionDTO,
+  TDeletePkiCollectionDTO,
+  TGetPkiCollectionByIdDTO,
+  TGetPkiCollectionItems,
+  TRemoveItemFromPkiCollectionDTO,
+  TUpdatePkiCollectionDTO
+} from "./pki-collection-types";
+
+type TPkiCollectionServiceFactoryDep = {
+  pkiCollectionDAL: Pick<TPkiCollectionDALFactory, "create" | "findById" | "updateById" | "deleteById">;
+  pkiCollectionItemDAL: Pick<
+    TPkiCollectionItemDALFactory,
+    "findOne" | "create" | "deleteById" | "findPkiCollectionItems" | "countItemsInPkiCollection"
+  >;
+  certificateAuthorityDAL: Pick<TCertificateAuthorityDALFactory, "find" | "findOne">;
+  certificateDAL: Pick<TCertificateDALFactory, "find">;
+  permissionService: Pick<TPermissionServiceFactory, "getProjectPermission">;
+};
+
+export type TPkiCollectionServiceFactory = ReturnType<typeof pkiCollectionServiceFactory>;
+
+export const pkiCollectionServiceFactory = ({
+  pkiCollectionDAL,
+  pkiCollectionItemDAL,
+  certificateAuthorityDAL,
+  certificateDAL,
+  permissionService
+}: TPkiCollectionServiceFactoryDep) => {
+  const createPkiCollection = async ({
+    name,
+    description,
+    projectId,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId
+  }: TCreatePkiCollectionDTO) => {
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Create,
+      ProjectPermissionSub.PkiCollections
+    );
+
+    const pkiCollection = await pkiCollectionDAL.create({
+      projectId,
+      name,
+      description
+    });
+
+    return pkiCollection;
+  };
+
+  const getPkiCollectionById = async ({
+    collectionId,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId
+  }: TGetPkiCollectionByIdDTO) => {
+    const pkiCollection = await pkiCollectionDAL.findById(collectionId);
+    if (!pkiCollection) throw new NotFoundError({ message: "PKI collection not found" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      pkiCollection.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.PkiCollections);
+    return pkiCollection;
+  };
+
+  const updatePkiCollection = async ({
+    collectionId,
+    name,
+    description,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId
+  }: TUpdatePkiCollectionDTO) => {
+    let pkiCollection = await pkiCollectionDAL.findById(collectionId);
+    if (!pkiCollection) throw new NotFoundError({ message: "PKI collection not found" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      pkiCollection.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Edit, ProjectPermissionSub.PkiCollections);
+    pkiCollection = await pkiCollectionDAL.updateById(collectionId, {
+      name,
+      description
+    });
+
+    return pkiCollection;
+  };
+
+  const deletePkiCollection = async ({
+    collectionId,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId
+  }: TDeletePkiCollectionDTO) => {
+    let pkiCollection = await pkiCollectionDAL.findById(collectionId);
+    if (!pkiCollection) throw new NotFoundError({ message: "PKI collection not found" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      pkiCollection.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Delete,
+      ProjectPermissionSub.PkiCollections
+    );
+    pkiCollection = await pkiCollectionDAL.deleteById(collectionId);
+    return pkiCollection;
+  };
+
+  const getPkiCollectionItems = async ({
+    collectionId,
+    type,
+    offset = 0,
+    limit = 25,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId
+  }: TGetPkiCollectionItems) => {
+    const pkiCollection = await pkiCollectionDAL.findById(collectionId);
+    if (!pkiCollection) throw new NotFoundError({ message: "PKI collection not found" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      pkiCollection.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.PkiCollections);
+
+    const pkiCollectionItems = await pkiCollectionItemDAL.findPkiCollectionItems({
+      collectionId,
+      type,
+      offset,
+      limit
+    });
+
+    const count = await pkiCollectionItemDAL.countItemsInPkiCollection(collectionId);
+
+    return {
+      pkiCollection,
+      pkiCollectionItems: pkiCollectionItems.map((p) => ({
+        ...transformPkiCollectionItem(p),
+        notBefore: p.notBefore,
+        notAfter: p.notAfter,
+        friendlyName: p.friendlyName
+      })),
+      totalCount: count
+    };
+  };
+
+  const addItemToPkiCollection = async ({
+    collectionId,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId,
+    type,
+    itemId
+  }: TAddItemToPkiCollectionDTO) => {
+    const pkiCollection = await pkiCollectionDAL.findById(collectionId);
+    if (!pkiCollection) throw new NotFoundError({ message: "PKI collection not found" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      pkiCollection.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Create,
+      ProjectPermissionSub.PkiCollections
+    );
+
+    let pkiCollectionItem: TPkiCollectionItems;
+    switch (type) {
+      case PkiItemType.CA: {
+        // validate that CA has not already been added to PKI collection
+        const isCaAdded = await pkiCollectionItemDAL.findOne({
+          pkiCollectionId: collectionId,
+          caId: itemId
+        });
+
+        if (isCaAdded) throw new BadRequestError({ message: "CA is already part of the PKI collection" });
+
+        // validate that there exists a CA in same project as PKI collection
+        const ca = await certificateAuthorityDAL.findOne({
+          id: itemId,
+          projectId: pkiCollection.projectId
+        });
+
+        if (!ca) throw new NotFoundError({ message: "CA not found" });
+
+        pkiCollectionItem = await pkiCollectionItemDAL.create({
+          pkiCollectionId: collectionId,
+          caId: itemId
+        });
+        break;
+      }
+      case PkiItemType.CERTIFICATE: {
+        // validate that certificate has not already been added to PKI collection
+        const isCertAdded = await pkiCollectionItemDAL.findOne({
+          pkiCollectionId: collectionId,
+          certId: itemId
+        });
+        if (isCertAdded) throw new BadRequestError({ message: "Certificate already part of the PKI collection" });
+
+        // validate that there exists a certificate in same project as PKI collection
+        const cas = await certificateAuthorityDAL.find({ projectId: pkiCollection.projectId });
+
+        // TODO: consider making this more efficient
+        const [certificate] = await certificateDAL.find({
+          $in: {
+            caId: cas.map((ca) => ca.id)
+          },
+          id: itemId
+        });
+        if (!certificate) throw new NotFoundError({ message: "Certificate not found" });
+
+        pkiCollectionItem = await pkiCollectionItemDAL.create({
+          pkiCollectionId: collectionId,
+          certId: itemId
+        });
+        break;
+      }
+      default: {
+        throw new BadRequestError({ message: "Invalid PKI item type" });
+      }
+    }
+
+    return {
+      pkiCollection,
+      pkiCollectionItem: transformPkiCollectionItem(pkiCollectionItem)
+    };
+  };
+
+  const removeItemFromPkiCollection = async ({
+    collectionId,
+    actorId,
+    actorAuthMethod,
+    actor,
+    actorOrgId,
+    itemId
+  }: TRemoveItemFromPkiCollectionDTO) => {
+    const pkiCollection = await pkiCollectionDAL.findById(collectionId);
+    if (!pkiCollection) throw new NotFoundError({ message: "PKI collection not found" });
+
+    let pkiCollectionItem = await pkiCollectionItemDAL.findOne({
+      pkiCollectionId: collectionId,
+      id: itemId
+    });
+
+    if (!pkiCollectionItem) throw new NotFoundError({ message: "PKI collection item not found" });
+
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      pkiCollection.projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Delete,
+      ProjectPermissionSub.PkiCollections
+    );
+
+    pkiCollectionItem = await pkiCollectionItemDAL.deleteById(itemId);
+
+    return {
+      pkiCollection,
+      pkiCollectionItem: transformPkiCollectionItem(pkiCollectionItem)
+    };
+  };
+
+  return {
+    createPkiCollection,
+    getPkiCollectionById,
+    updatePkiCollection,
+    deletePkiCollection,
+    getPkiCollectionItems,
+    addItemToPkiCollection,
+    removeItemFromPkiCollection
+  };
+};

backend/src/services/pki-collection/pki-collection-types.ts

@@ -0,0 +1,48 @@
+import { TProjectPermission } from "@app/lib/types";
+
+export type TCreatePkiCollectionDTO = {
+  name: string;
+  description: string;
+} & TProjectPermission;
+
+export type TGetPkiCollectionByIdDTO = {
+  collectionId: string;
+} & Omit<TProjectPermission, "projectId">;
+
+export type TUpdatePkiCollectionDTO = {
+  collectionId: string;
+  name?: string;
+  description?: string;
+} & Omit<TProjectPermission, "projectId">;
+
+export type TDeletePkiCollectionDTO = {
+  collectionId: string;
+} & Omit<TProjectPermission, "projectId">;
+
+export enum PkiItemType {
+  CERTIFICATE = "certificate",
+  CA = "ca"
+}
+
+export const pkiItemTypeToNameMap: { [K in PkiItemType]: string } = {
+  [PkiItemType.CA]: "CA",
+  [PkiItemType.CERTIFICATE]: "Certificate"
+};
+
+export type TGetPkiCollectionItems = {
+  collectionId: string;
+  type?: PkiItemType;
+  offset: number;
+  limit: number;
+} & Omit<TProjectPermission, "projectId">;
+
+export type TAddItemToPkiCollectionDTO = {
+  collectionId: string;
+  type: PkiItemType;
+  itemId: string;
+} & Omit<TProjectPermission, "projectId">;
+
+export type TRemoveItemFromPkiCollectionDTO = {
+  collectionId: string;
+  itemId: string;
+} & Omit<TProjectPermission, "projectId">;

backend/src/services/project-membership/project-membership-service.ts

@@ -12,6 +12,7 @@ import {
 import { TLicenseServiceFactory } from "@app/ee/services/license/license-service";
 import { TPermissionServiceFactory } from "@app/ee/services/permission/permission-service";
 import { ProjectPermissionActions, ProjectPermissionSub } from "@app/ee/services/permission/project-permission";
+import { TProjectUserAdditionalPrivilegeDALFactory } from "@app/ee/services/project-user-additional-privilege/project-user-additional-privilege-dal";
 import { getConfig } from "@app/lib/config/env";
 import { infisicalSymmetricDecrypt } from "@app/lib/crypto/encryption";
 import { BadRequestError } from "@app/lib/errors";
@@ -19,6 +20,7 @@ import { groupBy } from "@app/lib/fn";
 
 import { TUserGroupMembershipDALFactory } from "../../ee/services/group/user-group-membership-dal";
 import { ActorType } from "../auth/auth-type";
+import { TGroupProjectDALFactory } from "../group-project/group-project-dal";
 import { TOrgDALFactory } from "../org/org-dal";
 import { TProjectDALFactory } from "../project/project-dal";
 import { assignWorkspaceKeysToMembers } from "../project/project-fns";
@@ -54,6 +56,8 @@ type TProjectMembershipServiceFactoryDep = {
   projectDAL: Pick<TProjectDALFactory, "findById" | "findProjectGhostUser" | "transaction">;
   projectKeyDAL: Pick<TProjectKeyDALFactory, "findLatestProjectKey" | "delete" | "insertMany">;
   licenseService: Pick<TLicenseServiceFactory, "getPlan">;
+  projectUserAdditionalPrivilegeDAL: Pick<TProjectUserAdditionalPrivilegeDALFactory, "delete">;
+  groupProjectDAL: TGroupProjectDALFactory;
 };
 
 export type TProjectMembershipServiceFactory = ReturnType<typeof projectMembershipServiceFactory>;
@@ -66,8 +70,10 @@ export const projectMembershipServiceFactory = ({
   projectRoleDAL,
   projectBotDAL,
   orgDAL,
+  projectUserAdditionalPrivilegeDAL,
   userDAL,
   userGroupMembershipDAL,
+  groupProjectDAL,
   projectDAL,
   projectKeyDAL,
   licenseService
@@ -77,6 +83,7 @@ export const projectMembershipServiceFactory = ({
     actor,
     actorOrgId,
     actorAuthMethod,
+    includeGroupMembers,
     projectId
   }: TGetProjectMembershipDTO) => {
     const { permission } = await permissionService.getProjectPermission(
@@ -88,7 +95,25 @@ export const projectMembershipServiceFactory = ({
     );
     ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.Member);
 
-    return projectMembershipDAL.findAllProjectMembers(projectId);
+    const projectMembers = await projectMembershipDAL.findAllProjectMembers(projectId);
+
+    // projectMembers[0].project
+    if (includeGroupMembers) {
+      const groupMembers = await groupProjectDAL.findAllProjectGroupMembers(projectId);
+
+      const allMembers = [
+        ...projectMembers.map((m) => ({ ...m, isGroupMember: false })),
+        ...groupMembers.map((m) => ({ ...m, isGroupMember: true }))
+      ];
+
+      // Ensure the userId is unique
+      const membersIds = new Set(allMembers.map((entity) => entity.user.id));
+      const uniqueMembers = allMembers.filter((entity) => membersIds.has(entity.user.id));
+
+      return uniqueMembers;
+    }
+
+    return projectMembers.map((m) => ({ ...m, isGroupMember: false }));
   };
 
   const getProjectMembershipByUsername = async ({
@@ -502,6 +527,16 @@ export const projectMembershipServiceFactory = ({
     );
 
     const memberships = await projectMembershipDAL.transaction(async (tx) => {
+      await projectUserAdditionalPrivilegeDAL.delete(
+        {
+          projectId,
+          $in: {
+            userId: projectMembers.map((membership) => membership.user.id)
+          }
+        },
+        tx
+      );
+
       const deletedMemberships = await projectMembershipDAL.delete(
         {
           projectId,
@@ -564,12 +599,25 @@ export const projectMembershipServiceFactory = ({
       });
     }
 
-    const deletedMembership = (
-      await projectMembershipDAL.delete({
-        projectId: project.id,
-        userId: actorId
-      })
-    )?.[0];
+    const deletedMembership = await projectMembershipDAL.transaction(async (tx) => {
+      await projectUserAdditionalPrivilegeDAL.delete(
+        {
+          projectId: project.id,
+          userId: actorId
+        },
+        tx
+      );
+      const membership = (
+        await projectMembershipDAL.delete(
+          {
+            projectId: project.id,
+            userId: actorId
+          },
+          tx
+        )
+      )?.[0];
+      return membership;
+    });
 
     if (!deletedMembership) {
       throw new BadRequestError({ message: "Failed to leave project" });

backend/src/services/project-membership/project-membership-types.ts

@@ -1,6 +1,6 @@
 import { TProjectPermission } from "@app/lib/types";
 
-export type TGetProjectMembershipDTO = TProjectPermission;
+export type TGetProjectMembershipDTO = { includeGroupMembers?: boolean } & TProjectPermission;
 export type TLeaveProjectDTO = Omit<TProjectPermission, "actorOrgId" | "actorAuthMethod">;
 export enum ProjectUserMembershipTemporaryMode {
   Relative = "relative"

backend/src/services/project/project-service.ts

@@ -16,12 +16,15 @@ import { TProjectPermission } from "@app/lib/types";
 import { ActorType } from "../auth/auth-type";
 import { TCertificateDALFactory } from "../certificate/certificate-dal";
 import { TCertificateAuthorityDALFactory } from "../certificate-authority/certificate-authority-dal";
+import { TCertificateTemplateDALFactory } from "../certificate-template/certificate-template-dal";
 import { TIdentityOrgDALFactory } from "../identity/identity-org-dal";
 import { TIdentityProjectDALFactory } from "../identity-project/identity-project-dal";
 import { TIdentityProjectMembershipRoleDALFactory } from "../identity-project/identity-project-membership-role-dal";
 import { TKmsServiceFactory } from "../kms/kms-service";
 import { TOrgDALFactory } from "../org/org-dal";
 import { TOrgServiceFactory } from "../org/org-service";
+import { TPkiAlertDALFactory } from "../pki-alert/pki-alert-dal";
+import { TPkiCollectionDALFactory } from "../pki-collection/pki-collection-dal";
 import { TProjectBotDALFactory } from "../project-bot/project-bot-dal";
 import { TProjectEnvDALFactory } from "../project-env/project-env-dal";
 import { TProjectKeyDALFactory } from "../project-key/project-key-dal";
@@ -37,7 +40,9 @@ import {
   TDeleteProjectDTO,
   TGetProjectDTO,
   TGetProjectKmsKey,
+  TListProjectAlertsDTO,
   TListProjectCasDTO,
+  TListProjectCertificateTemplatesDTO,
   TListProjectCertsDTO,
   TLoadProjectKmsBackupDTO,
   TToggleProjectAutoCapitalizationDTO,
@@ -70,6 +75,9 @@ type TProjectServiceFactoryDep = {
   projectUserMembershipRoleDAL: Pick<TProjectUserMembershipRoleDALFactory, "create">;
   certificateAuthorityDAL: Pick<TCertificateAuthorityDALFactory, "find">;
   certificateDAL: Pick<TCertificateDALFactory, "find" | "countCertificatesInProject">;
+  certificateTemplateDAL: Pick<TCertificateTemplateDALFactory, "getCertTemplatesByProjectId">;
+  pkiAlertDAL: Pick<TPkiAlertDALFactory, "find">;
+  pkiCollectionDAL: Pick<TPkiCollectionDALFactory, "find">;
   permissionService: TPermissionServiceFactory;
   orgService: Pick<TOrgServiceFactory, "addGhostUser">;
   licenseService: Pick<TLicenseServiceFactory, "getPlan">;
@@ -107,6 +115,9 @@ export const projectServiceFactory = ({
   identityProjectMembershipRoleDAL,
   certificateAuthorityDAL,
   certificateDAL,
+  certificateTemplateDAL,
+  pkiCollectionDAL,
+  pkiAlertDAL,
   keyStore,
   kmsService,
   projectBotDAL
@@ -676,6 +687,90 @@ export const projectServiceFactory = ({
     };
   };
 
+  /**
+   * Return list of (PKI) alerts configured for project
+   */
+  const listProjectAlerts = async ({
+    projectId,
+    actor,
+    actorId,
+    actorAuthMethod,
+    actorOrgId
+  }: TListProjectAlertsDTO) => {
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.PkiAlerts);
+
+    const alerts = await pkiAlertDAL.find({ projectId });
+
+    return {
+      alerts
+    };
+  };
+
+  /**
+   * Return list of PKI collections for project
+   */
+  const listProjectPkiCollections = async ({
+    projectId,
+    actor,
+    actorId,
+    actorAuthMethod,
+    actorOrgId
+  }: TListProjectAlertsDTO) => {
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(ProjectPermissionActions.Read, ProjectPermissionSub.PkiCollections);
+
+    const pkiCollections = await pkiCollectionDAL.find({ projectId });
+
+    return {
+      pkiCollections
+    };
+  };
+
+  /**
+   * Return list of certificate templates for project
+   */
+  const listProjectCertificateTemplates = async ({
+    projectId,
+    actorId,
+    actorOrgId,
+    actorAuthMethod,
+    actor
+  }: TListProjectCertificateTemplatesDTO) => {
+    const { permission } = await permissionService.getProjectPermission(
+      actor,
+      actorId,
+      projectId,
+      actorAuthMethod,
+      actorOrgId
+    );
+
+    ForbiddenError.from(permission).throwUnlessCan(
+      ProjectPermissionActions.Read,
+      ProjectPermissionSub.CertificateTemplates
+    );
+
+    const certificateTemplates = await certificateTemplateDAL.getCertTemplatesByProjectId(projectId);
+
+    return {
+      certificateTemplates
+    };
+  };
+
   const updateProjectKmsKey = async ({
     projectId,
     kms,
@@ -794,6 +889,9 @@ export const projectServiceFactory = ({
     upgradeProject,
     listProjectCas,
     listProjectCertificates,
+    listProjectAlerts,
+    listProjectPkiCollections,
+    listProjectCertificateTemplates,
     updateVersionLimit,
     updateAuditLogsRetention,
     updateProjectKmsKey,

backend/src/services/project/project-types.ts

@@ -106,6 +106,8 @@ export type TListProjectCertsDTO = {
   commonName?: string;
 } & Omit<TProjectPermission, "projectId">;
 
+export type TListProjectAlertsDTO = TProjectPermission;
+
 export type TUpdateProjectKmsDTO = {
   kms: { type: KmsType.Internal } | { type: KmsType.External; kmsId: string };
 } & TProjectPermission;
@@ -115,3 +117,5 @@ export type TLoadProjectKmsBackupDTO = {
 } & TProjectPermission;
 
 export type TGetProjectKmsKey = TProjectPermission;
+
+export type TListProjectCertificateTemplatesDTO = TProjectPermission;

backend/src/services/secret/secret-queue.ts

@@ -6,11 +6,12 @@ import { TSecretApprovalRequestDALFactory } from "@app/ee/services/secret-approv
 import { TSecretRotationDALFactory } from "@app/ee/services/secret-rotation/secret-rotation-dal";
 import { TSnapshotDALFactory } from "@app/ee/services/secret-snapshot/snapshot-dal";
 import { TSnapshotSecretV2DALFactory } from "@app/ee/services/secret-snapshot/snapshot-secret-v2-dal";
+import { KeyStorePrefixes, KeyStoreTtls, TKeyStoreFactory } from "@app/keystore/keystore";
 import { getConfig } from "@app/lib/config/env";
 import { decryptSymmetric128BitHexKeyUTF8 } from "@app/lib/crypto";
 import { daysToMillisecond, secondsToMillis } from "@app/lib/dates";
 import { BadRequestError } from "@app/lib/errors";
-import { groupBy, isSamePath, unique } from "@app/lib/fn";
+import { getTimeDifferenceInSeconds, groupBy, isSamePath, unique } from "@app/lib/fn";
 import { logger } from "@app/lib/logger";
 import { QueueJobs, QueueName, TQueueServiceFactory } from "@app/queue";
 import { TProjectBotDALFactory } from "@app/services/project-bot/project-bot-dal";
@@ -79,6 +80,7 @@ type TSecretQueueFactoryDep = {
   secretApprovalRequestDAL: Pick<TSecretApprovalRequestDALFactory, "deleteByProjectId">;
   snapshotDAL: Pick<TSnapshotDALFactory, "findNSecretV1SnapshotByFolderId" | "deleteSnapshotsAboveLimit">;
   snapshotSecretV2BridgeDAL: Pick<TSnapshotSecretV2DALFactory, "insertMany" | "batchInsert">;
+  keyStore: Pick<TKeyStoreFactory, "acquireLock" | "setItemWithExpiry" | "getItem">;
 };
 
 export type TGetSecrets = {
@@ -122,7 +124,8 @@ export const secretQueueFactory = ({
   secretRotationDAL,
   snapshotDAL,
   snapshotSecretV2BridgeDAL,
-  secretApprovalRequestDAL
+  secretApprovalRequestDAL,
+  keyStore
 }: TSecretQueueFactoryDep) => {
   const removeSecretReminder = async (dto: TRemoveSecretReminderDTO) => {
     const appCfg = getConfig();
@@ -576,7 +579,6 @@ export const secretQueueFactory = ({
           )
       );
     }
-
     const { shouldUseSecretV2Bridge, botKey } = await projectBotService.getBotKey(projectId);
     const { decryptor: secretManagerDecryptor } = await kmsService.createCipherPairWithDataKey({
       type: KmsDataKey.SecretManager,
@@ -641,111 +643,157 @@ export const secretQueueFactory = ({
       `getIntegrationSecrets: secret integration sync started [jobId=${job.id}] [jobId=${job.id}] [projectId=${job.data.projectId}] [environment=${job.data.environment}]  [secretPath=${job.data.secretPath}] [depth=${job.data.depth}]`
     );
 
-    const secrets = shouldUseSecretV2Bridge
-      ? await getIntegrationSecretsV2({
-          environment,
-          projectId,
-          folderId: folder.id,
-          depth: 1,
-          decryptor: (value) => (value ? secretManagerDecryptor({ cipherTextBlob: value }).toString() : "")
-        })
-      : await getIntegrationSecrets({
-          environment,
-          projectId,
-          folderId: folder.id,
-          key: botKey as string,
-          depth: 1
-        });
-
-    for (const integration of toBeSyncedIntegrations) {
-      const integrationAuth = {
-        ...integration.integrationAuth,
-        createdAt: new Date(),
-        updatedAt: new Date(),
-        projectId: integration.projectId
-      };
-
-      const { accessToken, accessId } = await integrationAuthService.getIntegrationAccessToken(
-        integrationAuth,
-        shouldUseSecretV2Bridge,
-        botKey
-      );
-      let awsAssumeRoleArn = null;
-      if (shouldUseSecretV2Bridge) {
-        if (integrationAuth.encryptedAwsAssumeIamRoleArn) {
-          awsAssumeRoleArn = secretManagerDecryptor({
-            cipherTextBlob: Buffer.from(integrationAuth.encryptedAwsAssumeIamRoleArn)
-          }).toString();
-        }
-      } else if (
-        integrationAuth.awsAssumeIamRoleArnTag &&
-        integrationAuth.awsAssumeIamRoleArnIV &&
-        integrationAuth.awsAssumeIamRoleArnCipherText
-      ) {
-        awsAssumeRoleArn = decryptSymmetric128BitHexKeyUTF8({
-          ciphertext: integrationAuth.awsAssumeIamRoleArnCipherText,
-          iv: integrationAuth.awsAssumeIamRoleArnIV,
-          tag: integrationAuth.awsAssumeIamRoleArnTag,
-          key: botKey as string
-        });
+    const lock = await keyStore.acquireLock(
+      [KeyStorePrefixes.SyncSecretIntegrationLock(projectId, environment, secretPath)],
+      10000,
+      {
+        retryCount: 3,
+        retryDelay: 2000
       }
+    );
+    const lockAcquiredTime = new Date();
 
-      const suffixedSecrets: typeof secrets = {};
-      const metadata = integration.metadata as Record<string, string>;
-      if (metadata) {
-        Object.keys(secrets).forEach((key) => {
-          const prefix = metadata?.secretPrefix || "";
-          const suffix = metadata?.secretSuffix || "";
-          const newKey = prefix + key + suffix;
-          suffixedSecrets[newKey] = secrets[key];
-        });
-      }
+    const lastRunSyncIntegrationTimestamp = await keyStore.getItem(
+      KeyStorePrefixes.SyncSecretIntegrationLastRunTimestamp(projectId, environment, secretPath)
+    );
 
-      try {
-        // akhilmhdh: this needs to changed later to be more easier to use
-        // at present this is not at all extendable like to add a new parameter for just one integration need to modify multiple places
-        const response = await syncIntegrationSecrets({
-          createManySecretsRawFn,
-          updateManySecretsRawFn,
-          integrationDAL,
-          integration,
-          integrationAuth,
-          secrets: Object.keys(suffixedSecrets).length !== 0 ? suffixedSecrets : secrets,
-          accessId: accessId as string,
-          awsAssumeRoleArn,
-          accessToken,
-          projectId,
-          appendices: {
-            prefix: metadata?.secretPrefix || "",
-            suffix: metadata?.secretSuffix || ""
-          }
-        });
-
-        await integrationDAL.updateById(integration.id, {
-          lastSyncJobId: job.id,
-          lastUsed: new Date(),
-          syncMessage: response?.syncMessage ?? "",
-          isSynced: response?.isSynced ?? true
-        });
-      } catch (err) {
-        logger.error(
-          err,
-          `Secret integration sync error [projectId=${job.data.projectId}] [environment=${job.data.environment}]  [secretPath=${job.data.secretPath}]`
+    // check whether the integration should wait or not
+    if (lastRunSyncIntegrationTimestamp) {
+      const INTEGRATION_INTERVAL = 2000;
+      const isStaleSyncIntegration = new Date(job.timestamp) < new Date(lastRunSyncIntegrationTimestamp);
+      if (isStaleSyncIntegration) {
+        logger.info(
+          `getIntegrationSecrets: secret integration sync stale [jobId=${job.id}] [jobId=${job.id}] [projectId=${job.data.projectId}] [environment=${job.data.environment}]  [secretPath=${job.data.secretPath}] [depth=${job.data.depth}]`
         );
-
-        const message =
-          (err instanceof AxiosError ? JSON.stringify(err?.response?.data) : (err as Error)?.message) ||
-          "Unknown error occurred.";
-
-        await integrationDAL.updateById(integration.id, {
-          lastSyncJobId: job.id,
-          lastUsed: new Date(),
-          syncMessage: message,
-          isSynced: false
-        });
+        return;
       }
+
+      const timeDifferenceWithLastIntegration = getTimeDifferenceInSeconds(
+        lockAcquiredTime.toISOString(),
+        lastRunSyncIntegrationTimestamp
+      );
+      if (timeDifferenceWithLastIntegration < INTEGRATION_INTERVAL && timeDifferenceWithLastIntegration > 0)
+        await new Promise((resolve) => {
+          setTimeout(resolve, 2000 - timeDifferenceWithLastIntegration * 1000);
+        });
     }
 
+    // akhilmhdh: this try catch is for lock release
+    try {
+      const secrets = shouldUseSecretV2Bridge
+        ? await getIntegrationSecretsV2({
+            environment,
+            projectId,
+            folderId: folder.id,
+            depth: 1,
+            decryptor: (value) => (value ? secretManagerDecryptor({ cipherTextBlob: value }).toString() : "")
+          })
+        : await getIntegrationSecrets({
+            environment,
+            projectId,
+            folderId: folder.id,
+            key: botKey as string,
+            depth: 1
+          });
+
+      for (const integration of toBeSyncedIntegrations) {
+        const integrationAuth = {
+          ...integration.integrationAuth,
+          createdAt: new Date(),
+          updatedAt: new Date(),
+          projectId: integration.projectId
+        };
+
+        const { accessToken, accessId } = await integrationAuthService.getIntegrationAccessToken(
+          integrationAuth,
+          shouldUseSecretV2Bridge,
+          botKey
+        );
+        let awsAssumeRoleArn = null;
+        if (shouldUseSecretV2Bridge) {
+          if (integrationAuth.encryptedAwsAssumeIamRoleArn) {
+            awsAssumeRoleArn = secretManagerDecryptor({
+              cipherTextBlob: Buffer.from(integrationAuth.encryptedAwsAssumeIamRoleArn)
+            }).toString();
+          }
+        } else if (
+          integrationAuth.awsAssumeIamRoleArnTag &&
+          integrationAuth.awsAssumeIamRoleArnIV &&
+          integrationAuth.awsAssumeIamRoleArnCipherText
+        ) {
+          awsAssumeRoleArn = decryptSymmetric128BitHexKeyUTF8({
+            ciphertext: integrationAuth.awsAssumeIamRoleArnCipherText,
+            iv: integrationAuth.awsAssumeIamRoleArnIV,
+            tag: integrationAuth.awsAssumeIamRoleArnTag,
+            key: botKey as string
+          });
+        }
+
+        const suffixedSecrets: typeof secrets = {};
+        const metadata = integration.metadata as Record<string, string>;
+        if (metadata) {
+          Object.keys(secrets).forEach((key) => {
+            const prefix = metadata?.secretPrefix || "";
+            const suffix = metadata?.secretSuffix || "";
+            const newKey = prefix + key + suffix;
+            suffixedSecrets[newKey] = secrets[key];
+          });
+        }
+
+        // akhilmhdh: this try catch is for catching integration error and saving it in db
+        try {
+          // akhilmhdh: this needs to changed later to be more easier to use
+          // at present this is not at all extendable like to add a new parameter for just one integration need to modify multiple places
+          const response = await syncIntegrationSecrets({
+            createManySecretsRawFn,
+            updateManySecretsRawFn,
+            integrationDAL,
+            integration,
+            integrationAuth,
+            secrets: Object.keys(suffixedSecrets).length !== 0 ? suffixedSecrets : secrets,
+            accessId: accessId as string,
+            awsAssumeRoleArn,
+            accessToken,
+            projectId,
+            appendices: {
+              prefix: metadata?.secretPrefix || "",
+              suffix: metadata?.secretSuffix || ""
+            }
+          });
+
+          await integrationDAL.updateById(integration.id, {
+            lastSyncJobId: job.id,
+            lastUsed: new Date(),
+            syncMessage: response?.syncMessage ?? "",
+            isSynced: response?.isSynced ?? true
+          });
+        } catch (err) {
+          logger.error(
+            err,
+            `Secret integration sync error [projectId=${job.data.projectId}] [environment=${job.data.environment}]  [secretPath=${job.data.secretPath}]`
+          );
+
+          const message =
+            (err instanceof AxiosError ? JSON.stringify(err?.response?.data) : (err as Error)?.message) ||
+            "Unknown error occurred.";
+
+          await integrationDAL.updateById(integration.id, {
+            lastSyncJobId: job.id,
+            lastUsed: new Date(),
+            syncMessage: message,
+            isSynced: false
+          });
+        }
+      }
+    } finally {
+      await lock.release();
+    }
+
+    await keyStore.setItemWithExpiry(
+      KeyStorePrefixes.SyncSecretIntegrationLastRunTimestamp(projectId, environment, secretPath),
+      KeyStoreTtls.SetSyncSecretIntegrationLastRunTimestampInSeconds,
+      lockAcquiredTime.toISOString()
+    );
     logger.info("Secret integration sync ended: %s", job.id);
   });
 

backend/src/services/smtp/smtp-service.ts

@@ -31,7 +31,8 @@ export enum SmtpTemplates {
   ResetPassword = "passwordReset.handlebars",
   SecretLeakIncident = "secretLeakIncident.handlebars",
   WorkspaceInvite = "workspaceInvitation.handlebars",
-  ScimUserProvisioned = "scimUserProvisioned.handlebars"
+  ScimUserProvisioned = "scimUserProvisioned.handlebars",
+  PkiExpirationAlert = "pkiExpirationAlert.handlebars"
 }
 
 export enum SmtpHost {

backend/src/services/smtp/templates/pkiExpirationAlert.handlebars

@@ -0,0 +1,31 @@
+<html>
+  <head>
+    <meta charset="utf-8" />
+    <meta http-equiv="x-ua-compatible" content="ie=edge" />
+    <title>Infisical CA/Certificate expiration notice</title>
+  </head>
+  <body>
+    <p>Hello,</p>
+    <p>This is an automated alert for "{{alertName}}" triggered for CAs/Certificates expiring in
+      {{alertBeforeDays}}
+      days.</p>
+
+    <p>Expiring Items:</p>
+    <ul>
+      {{#each items}}
+        <li>
+          {{type}}:
+          <strong>{{friendlyName}}</strong>
+          <br />Serial Number:
+          {{serialNumber}}
+          <br />Expires On:
+          {{expiryDate}}
+        </li>
+      {{/each}}
+    </ul>
+
+    <p>Please take necessary actions to renew these items before they expire.</p>
+
+    <p>For more details, please log in to your Infisical account and check your PKI management section.</p>
+  </body>
+</html>

company/handbook/talking-to-customers.mdx

@@ -0,0 +1,20 @@
+---
+title: "Talking to Customers"
+sidebarTitle: "Talking to Customers"
+description: "The guide to talking to customers at Infisical."
+---
+
+Everyone at Infisical talks to customers directly. We do this for a few reasons: 
+1. This helps us understand the needs of our customers and build the product they want.
+2. This speeds up our iteration cycles (time from customer feedback to product improvements). 
+3. Our customers (developers) are able to talk directly to the best experts in Infisical (us) – which improves their satisfaction and success.
+
+## Customer Communication Etiquette
+
+1. When talking to customers (no matter whether it's on Slack, email, or any other channel), it is very important to use proper grammar (e.g., no typos, no missed question marks) and minimal colloquial language (no "yeap", "yah", etc.). 
+2. At the time of a crisis (e.g., customer-reported bug), it is very important to communicate often. Even if there is no update yet, it is good to reach out to the customer and let them know that we are still working on resolving a certain issue.
+
+## Community Slack
+
+Unfortunately, we are not able to help everyone in the community Slack. It is OK to politely decline questions about infrastructure management that are not directly related to the product itself.
+

company/mint.json

@@ -60,7 +60,8 @@
         "handbook/spending-money",
         "handbook/time-off",
         "handbook/hiring",
-        "handbook/meetings"
+        "handbook/meetings",
+        "handbook/talking-to-customers"
       ]
     }
   ],

docs/api-reference/endpoints/certificate-authorities/list-ca-certs.mdx

@@ -0,0 +1,4 @@
+---
+title: "List CA certificates"
+openapi: "GET /api/v1/pki/ca/{caId}/ca-certificates"
+---

docs/api-reference/endpoints/certificate-authorities/renew.mdx

@@ -0,0 +1,4 @@
+---
+title: "Renew"
+openapi: "POST /api/v1/pki/ca/{caId}/renew"
+---

docs/api-reference/endpoints/certificate-templates/create.mdx

@@ -0,0 +1,4 @@
+---
+title: "Create"
+openapi: "POST /api/v1/pki/certificate-templates"
+---

docs/api-reference/endpoints/certificate-templates/delete.mdx

@@ -0,0 +1,4 @@
+---
+title: "Delete"
+openapi: "DELETE /api/v1/pki/certificate-templates/{certificateTemplateId}"
+---

docs/api-reference/endpoints/certificate-templates/get-by-id.mdx

@@ -0,0 +1,4 @@
+---
+title: "Get by ID"
+openapi: "GET /api/v1/pki/certificate-templates/{certificateTemplateId}"
+---

docs/api-reference/endpoints/certificate-templates/update.mdx

@@ -0,0 +1,4 @@
+---
+title: "Update"
+openapi: "PATCH /api/v1/pki/certificate-templates/{certificateTemplateId}"
+---

docs/api-reference/endpoints/certificates/issue-certificate.mdx

@@ -0,0 +1,4 @@
+---
+title: "Issue Certificate"
+openapi: "POST /api/v1/pki/certificates/issue-certificate"
+---

docs/api-reference/endpoints/certificates/sign-certificate.mdx

@@ -0,0 +1,4 @@
+---
+title: "Sign Certificate"
+openapi: "POST /api/v1/pki/certificates/sign-certificate"
+---

docs/api-reference/endpoints/pki-alerts/create.mdx

@@ -0,0 +1,4 @@
+---
+title: "Create"
+openapi: "POST /api/v1/pki/alerts"
+---

docs/api-reference/endpoints/pki-alerts/delete.mdx

@@ -0,0 +1,4 @@
+---
+title: "Delete"
+openapi: "DELETE /api/v1/pki/alerts/{alertId}"
+---

docs/api-reference/endpoints/pki-alerts/read.mdx

@@ -0,0 +1,4 @@
+---
+title: "Retrieve"
+openapi: "GET /api/v1/pki/alerts/{alertId}"
+---

docs/api-reference/endpoints/pki-alerts/update.mdx

@@ -0,0 +1,4 @@
+---
+title: "Update"
+openapi: "PATCH /api/v1/pki/alerts/{alertId}"
+---

docs/api-reference/endpoints/pki-collections/add-item.mdx

@@ -0,0 +1,4 @@
+---
+title: "Add Collection Item"
+openapi: "POST /api/v1/pki/collections/{collectionId}/items"
+---

docs/api-reference/endpoints/pki-collections/create.mdx

@@ -0,0 +1,4 @@
+---
+title: "Create"
+openapi: "POST /api/v1/pki/collections"
+---

docs/api-reference/endpoints/pki-collections/delete-item.mdx

@@ -0,0 +1,4 @@
+---
+title: "Delete Collection Item"
+openapi: "DELETE /api/v1/pki/collections/{collectionId}/items/{collectionItemId}"
+---

docs/api-reference/endpoints/pki-collections/delete.mdx

@@ -0,0 +1,4 @@
+---
+title: "Delete"
+openapi: "DELETE /api/v1/pki/collections/{collectionId}"
+---

docs/api-reference/endpoints/pki-collections/list-items.mdx

@@ -0,0 +1,4 @@
+---
+title: "Retrieve"
+openapi: "GET /api/v1/pki/collections/{collectionId}/items"
+---

docs/api-reference/endpoints/pki-collections/read.mdx

@@ -0,0 +1,4 @@
+---
+title: "Retrieve"
+openapi: "GET /api/v1/pki/collections/{collectionId}"
+---

docs/api-reference/endpoints/pki-collections/update.mdx

@@ -0,0 +1,4 @@
+---
+title: "Update"
+openapi: "PATCH /api/v1/pki/collections/{collectionId}"
+---