Infisical is the open-source platform for securing secrets, certificates, access, and AI Agents.
TypeScript acmeagentsai-agentscertificate-managementdevsecopsenvironment-variableskubernetesopen-sourcepampkipostgresprivate-caprivileged-access-managementsecret-managementsecret-managersecretssecrets-managementsecuritysecurity-toolsvault
import { Knex } from "knex";
import { TDbClient } from "@app/db";
import { TableName, TIntegrations } from "@app/db/schemas";
import { DatabaseError } from "@app/lib/errors";
import { ormify, selectAllTableCols } from "@app/lib/knex";
export type TIntegrationDALFactory = ReturnType<typeof integrationDALFactory>;
export const integrationDALFactory = (db: TDbClient) => {
const integrationOrm = ormify(db, TableName.Integration);
const integrationFindQuery = (tx: Knex, filter: Partial<TIntegrations>) =>
tx(TableName.Integration)
.where(filter)
.join(TableName.Environment, `${TableName.Integration}.envId`, `${TableName.Environment}.id`)
.whereNull(`${TableName.Environment}.deleteAfter`)
.select(tx.ref("name").withSchema(TableName.Environment).as("envName"))
.select(tx.ref("slug").withSchema(TableName.Environment).as("envSlug"))
.select(tx.ref("id").withSchema(TableName.Environment).as("envId"))
.select(tx.ref("projectId").withSchema(TableName.Environment))
.select(selectAllTableCols(TableName.Integration));
const find = async (filter: Partial<TIntegrations>, tx?: Knex) => {
try {
const docs = await integrationFindQuery(tx || db.replicaNode(), filter);
return docs.map(({ envId, envSlug, envName, ...el }) => ({
...el,
environment: {
id: envId,
slug: envSlug,
name: envName
}
}));
} catch (error) {
throw new DatabaseError({ error, name: "Find by id integrations" });
}
};
const findOne = async (filter: Partial<TIntegrations>, tx?: Knex) => {
try {
const doc = await integrationFindQuery(tx || db.replicaNode(), filter).first();
if (!doc) return;
const { envName: name, envSlug: slug, envId: id, ...el } = doc;
return { ...el, environment: { id, name, slug } };
} catch (error) {
throw new DatabaseError({ error, name: "Find one integrations" });
}
};
const findById = async (id: string, tx?: Knex) => {
try {
const doc = await integrationFindQuery(tx || db.replicaNode(), {
[`${TableName.Integration}.id` as "id"]: id
}).first();
if (!doc) return;
const { envName: name, envSlug: slug, envId, ...el } = doc;
return { ...el, environment: { id: envId, name, slug } };
} catch (error) {
throw new DatabaseError({ error, name: "Find by id integrations" });
}
};
const findByProjectId = async (projectId: string, tx?: Knex) => {
try {
const integrations = await (tx || db.replicaNode())(TableName.Integration)
.where(`${TableName.Environment}.projectId`, projectId)
.join(TableName.Environment, `${TableName.Integration}.envId`, `${TableName.Environment}.id`)
.whereNull(`${TableName.Environment}.deleteAfter`)
.select(db.ref("name").withSchema(TableName.Environment).as("envName"))
.select(db.ref("slug").withSchema(TableName.Environment).as("envSlug"))
.select(db.ref("id").withSchema(TableName.Environment).as("envId"))
.select(db.ref("projectId").withSchema(TableName.Environment))
.select(selectAllTableCols(TableName.Integration));
return integrations.map(({ envId, envSlug, envName, ...el }) => ({
...el,
envId,
environment: {
id: envId,
slug: envSlug,
name: envName
}
}));
} catch (error) {
throw new DatabaseError({ error, name: "FindByProjectId" });
}
};
// used for syncing secrets
// this will populate integration auth also
const findByProjectIdV2 = async (projectId: string, environment: string, tx?: Knex) => {
const docs = await (tx || db.replicaNode())(TableName.Integration)
.where(`${TableName.Environment}.projectId`, projectId)
.where("isActive", true)
.where(`${TableName.Environment}.slug`, environment)
.join(TableName.Environment, `${TableName.Integration}.envId`, `${TableName.Environment}.id`)
.whereNull(`${TableName.Environment}.deleteAfter`)
.join(TableName.IntegrationAuth, `${TableName.IntegrationAuth}.id`, `${TableName.Integration}.integrationAuthId`)
.select(db.ref("name").withSchema(TableName.Environment).as("envName"))
.select(db.ref("slug").withSchema(TableName.Environment).as("envSlug"))
.select(db.ref("id").withSchema(TableName.Environment).as("envId"))
.select(db.ref("projectId").withSchema(TableName.Environment))
.select(selectAllTableCols(TableName.Integration))
.select(
db.ref("id").withSchema(TableName.IntegrationAuth).as("idAu"),
db.ref("integration").withSchema(TableName.IntegrationAuth).as("integrationAu"),
db.ref("teamId").withSchema(TableName.IntegrationAuth).as("teamIdAu"),
db.ref("url").withSchema(TableName.IntegrationAuth).as("urlAu"),
db.ref("namespace").withSchema(TableName.IntegrationAuth).as("namespaceAu"),
db.ref("accountId").withSchema(TableName.IntegrationAuth).as("accountIdAu"),
db.ref("refreshCiphertext").withSchema(TableName.IntegrationAuth).as("refreshCiphertextAu"),
db.ref("refreshIV").withSchema(TableName.IntegrationAuth).as("refreshIVAu"),
db.ref("refreshTag").withSchema(TableName.IntegrationAuth).as("refreshTagAu"),
db.ref("accessIdCiphertext").withSchema(TableName.IntegrationAuth).as("accessIdCiphertextAu"),
db.ref("accessIdIV").withSchema(TableName.IntegrationAuth).as("accessIdIVAu"),
db.ref("accessIdTag").withSchema(TableName.IntegrationAuth).as("accessIdTagAu"),
db.ref("accessIV").withSchema(TableName.IntegrationAuth).as("accessIVAu"),
db.ref("accessTag").withSchema(TableName.IntegrationAuth).as("accessTagAu"),
db.ref("accessCiphertext").withSchema(TableName.IntegrationAuth).as("accessCiphertextAu"),
db.ref("accessExpiresAt").withSchema(TableName.IntegrationAuth).as("accessExpiresAtAu"),
db.ref("metadata").withSchema(TableName.IntegrationAuth).as("metadataAu"),
db.ref("algorithm").withSchema(TableName.IntegrationAuth).as("algorithmAu"),
db.ref("keyEncoding").withSchema(TableName.IntegrationAuth).as("keyEncodingAu"),
db.ref("awsAssumeIamRoleArnCipherText").withSchema(TableName.IntegrationAuth),
db.ref("awsAssumeIamRoleArnIV").withSchema(TableName.IntegrationAuth),
db.ref("awsAssumeIamRoleArnTag").withSchema(TableName.IntegrationAuth),
db.ref("encryptedRefresh").withSchema(TableName.IntegrationAuth),
db.ref("encryptedAccess").withSchema(TableName.IntegrationAuth),
db.ref("encryptedAccessId").withSchema(TableName.IntegrationAuth),
db.ref("encryptedAwsAssumeIamRoleArn").withSchema(TableName.IntegrationAuth)
);
return docs.map(
({
envId,
envName,
envSlug,
idAu: id,
integrationAu: integration,
teamIdAu: teamId,
urlAu: url,
namespaceAu: namespace,
accountIdAu: accountId,
refreshIVAu: refreshIV,
refreshCiphertextAu: refreshCiphertext,
refreshTagAu: refreshTag,
accessIVAu: accessIV,
accessCiphertextAu: accessCiphertext,
accessTagAu: accessTag,
accessIdIVAu: accessIdIV,
accessIdTagAu: accessIdTag,
accessIdCiphertextAu: accessIdCiphertext,
metadataAu: metadata,
algorithmAu: algorithm,
keyEncodingAu: keyEncoding,
accessExpiresAtAu: accessExpiresAt,
awsAssumeIamRoleArnIV,
awsAssumeIamRoleArnCipherText,
awsAssumeIamRoleArnTag,
encryptedAccess,
encryptedRefresh,
encryptedAccessId,
encryptedAwsAssumeIamRoleArn,
...el
}) => ({
...el,
envId,
environment: {
id: envId,
name: envName,
slug: envSlug
},
integrationAuth: {
id,
integration,
teamId,
url,
namespace,
accountId,
refreshTag,
refreshIV,
refreshCiphertext,
accessIdCiphertext,
accessIdIV,
accessIdTag,
accessIV,
accessCiphertext,
accessTag,
metadata,
algorithm,
keyEncoding,
accessExpiresAt,
awsAssumeIamRoleArnIV,
awsAssumeIamRoleArnCipherText,
awsAssumeIamRoleArnTag,
encryptedAccess,
encryptedRefresh,
encryptedAccessId,
encryptedAwsAssumeIamRoleArn
}
})
);
};
// native integrations are deprecated in favour of secret syncs — the three helpers below back the monthly
// deprecation notice, which enumerates every org still holding one.
const liveOrgIntegrationQuery = (conn: Knex) =>
conn(TableName.Integration)
.join(TableName.Environment, `${TableName.Integration}.envId`, `${TableName.Environment}.id`)
.join(TableName.Project, `${TableName.Environment}.projectId`, `${TableName.Project}.id`)
.whereNull(`${TableName.Environment}.deleteAfter`)
.whereNull(`${TableName.Project}.deleteAfter`);
const findOrgIdsWithIntegrations = async (tx?: Knex) => {
try {
const orgIds = await liveOrgIntegrationQuery(tx || db.replicaNode())
.distinct(db.ref("orgId").withSchema(TableName.Project))
.pluck<string[]>(`${TableName.Project}.orgId`);
return orgIds;
} catch (error) {
throw new DatabaseError({ error, name: "FindOrgIdsWithIntegrations" });
}
};
const findProjectIntegrationsByOrgId = async (orgId: string, tx?: Knex) => {
try {
const docs = await liveOrgIntegrationQuery(tx || db.replicaNode())
.where(`${TableName.Project}.orgId`, orgId)
.select(
db.ref("id").withSchema(TableName.Project).as("projectId"),
db.ref("name").withSchema(TableName.Project).as("projectName"),
db.ref("integration").withSchema(TableName.Integration)
)
.orderBy(`${TableName.Project}.name` as "name");
return docs;
} catch (error) {
throw new DatabaseError({ error, name: "FindProjectIntegrationsByOrgId" });
}
};
const hasIntegrationsByOrgId = async (orgId: string, tx?: Knex): Promise<boolean> => {
try {
// primary, not the replica: this check exists to catch an integration deleted moments ago, so reading a
// possibly stale replica would defeat its purpose
const doc = await liveOrgIntegrationQuery(tx || db)
.where(`${TableName.Project}.orgId`, orgId)
.select(db.ref("id").withSchema(TableName.Integration))
.first();
return Boolean(doc);
} catch (error) {
throw new DatabaseError({ error, name: "HasIntegrationsByOrgId" });
}
};
return {
...integrationOrm,
find,
findOne,
findById,
findByProjectId,
findByProjectIdV2,
findOrgIdsWithIntegrations,
findProjectIntegrationsByOrgId,
hasIntegrationsByOrgId
};
};