Skip to content

Open-source manufacturing ERP, MES and QMS. Quoting, MRP, inventory, shop floor, quality and lot/serial traceability on one Postgres schema, with a REST API and MCP server. Self-host or use Carbon Cloud.

12.4 KB
Raw Blame
// SPDX-License-Identifier: LicenseRef-Carbon-Commercial
// Carbon Enterprise file, licensed only under the Carbon Commercial License
// (packages/ee/LICENSE). Not AGPL. Running, modifying, or copying it beyond those terms requires a commercial license.

import type { Kysely, KyselyDatabase, KyselyTx } from "@carbon/database/client";

export interface ExternalIntegrationMapping {
  id: string;
  entityType: string;
  entityId: string;
  integration: string;
  externalId: string;
  allowDuplicateExternalId: boolean;
  companyId: string;
  metadata: Record<string, unknown> | null;
  lastSyncedAt: string | null;
  remoteUpdatedAt: string | null;
  createdAt: string;
  updatedAt: string;
  createdBy: string | null;
}

export interface LinkOptions {
  metadata?: Record<string, unknown>;
  remoteUpdatedAt?: Date | string;
  lastSyncedAt?: Date | string;
  createdBy?: string;
  /**
   * When true, allows multiple Carbon entities to map to the same external ID.
   * Use this for many-to-one (Carbon to External) relationships.
   * Default: false (enforces unique external IDs per integration per company)
   */
  allowDuplicateExternalId?: boolean;
}

/**
 * Service for managing external integration mappings.
 * Provides a clean interface for linking Carbon entities to external system entities.
 */
export class ExternalIntegrationMappingService {
  constructor(
    private db: Kysely<KyselyDatabase> | KyselyTx,
    private companyId: string
  ) {}

  /**
   * Link a Carbon entity to an external system entity.
   * Uses upsert to handle both create and update cases.
   * If remoteUpdatedAt is not provided, it defaults to the current timestamp.
   */
  async link(
    entityType: string,
    entityId: string,
    integration: string,
    externalId: string,
    options?: LinkOptions
  ): Promise<void> {
    const now = new Date().toISOString();
    // Default to current timestamp if remoteUpdatedAt is not provided
    const remoteUpdatedAt =
      options?.remoteUpdatedAt instanceof Date
        ? options.remoteUpdatedAt.toISOString()
        : (options?.remoteUpdatedAt ?? now);
    const lastSyncedAt =
      options?.lastSyncedAt instanceof Date
        ? options.lastSyncedAt.toISOString()
        : (options?.lastSyncedAt ?? now);
    const allowDuplicateExternalId = options?.allowDuplicateExternalId ?? false;

    await this.db
      .insertInto("externalIntegrationMapping")
      .values({
        entityType,
        entityId,
        integration,
        externalId,
        allowDuplicateExternalId,
        companyId: this.companyId,
        metadata: options?.metadata ?? null,
        lastSyncedAt,
        remoteUpdatedAt,
        createdBy: options?.createdBy ?? null,
        createdAt: now,
        updatedAt: now
      } as any)
      .onConflict((oc) =>
        oc
          .columns(["entityType", "entityId", "integration", "companyId"])
          .doUpdateSet({
            externalId,
            allowDuplicateExternalId,
            metadata: (options?.metadata ?? null) as any,
            lastSyncedAt,
            remoteUpdatedAt,
            updatedAt: now
          })
      )
      .execute();
  }

  /**
   * Unlink a Carbon entity from an external system.
   */
  async unlink(
    entityType: string,
    entityId: string,
    integration: string
  ): Promise<void> {
    await this.db
      .deleteFrom("externalIntegrationMapping")
      .where("entityType", "=", entityType)
      .where("entityId", "=", entityId)
      .where("integration", "=", integration)
      .where("companyId", "=", this.companyId)
      .execute();
  }

  /**
   * Get the external ID for a Carbon entity.
   */
  async getExternalId(
    entityType: string,
    entityId: string,
    integration: string
  ): Promise<string | null> {
    const mapping = await this.db
      .selectFrom("externalIntegrationMapping")
      .select("externalId")
      .where("entityType", "=", entityType)
      .where("entityId", "=", entityId)
      .where("integration", "=", integration)
      .where("companyId", "=", this.companyId)
      .executeTakeFirst();

    return mapping?.externalId ?? null;
  }

  /**
   * Get the Carbon entity ID for an external ID.
   */
  async getEntityId(
    integration: string,
    externalId: string,
    entityType?: string
  ): Promise<string | null> {
    let query = this.db
      .selectFrom("externalIntegrationMapping")
      .select("entityId")
      .where("integration", "=", integration)
      .where("externalId", "=", externalId)
      .where("companyId", "=", this.companyId);

    if (entityType) {
      query = query.where("entityType", "=", entityType);
    }

    const mapping = await query.executeTakeFirst();
    return mapping?.entityId ?? null;
  }

  /**
   * Get the full mapping for a Carbon entity.
   */
  async getByEntity(
    entityType: string,
    entityId: string,
    integration: string
  ): Promise<ExternalIntegrationMapping | null> {
    const mapping = await this.db
      .selectFrom("externalIntegrationMapping")
      .selectAll()
      .where("entityType", "=", entityType)
      .where("entityId", "=", entityId)
      .where("integration", "=", integration)
      .where("companyId", "=", this.companyId)
      .executeTakeFirst();

    return (mapping as ExternalIntegrationMapping) ?? null;
  }

  /** Tenant/provider-scoped mappings for one outbound entity batch. */
  async getByEntities(
    entityType: string,
    entityIds: string[],
    integration: string
  ): Promise<Map<string, ExternalIntegrationMapping>> {
    if (entityIds.length === 0) return new Map();
    const rows = await this.db
      .selectFrom("externalIntegrationMapping")
      .selectAll()
      .where("entityType", "=", entityType)
      .where("entityId", "in", entityIds)
      .where("integration", "=", integration)
      .where("companyId", "=", this.companyId)
      .execute();
    return new Map(
      rows.map((row) => [row.entityId, row as ExternalIntegrationMapping])
    );
  }

  /**
   * Get the full mapping for an external ID.
   */
  async getByExternalId(
    integration: string,
    externalId: string,
    entityType?: string
  ): Promise<ExternalIntegrationMapping | null> {
    let query = this.db
      .selectFrom("externalIntegrationMapping")
      .selectAll()
      .where("integration", "=", integration)
      .where("externalId", "=", externalId)
      .where("companyId", "=", this.companyId);

    if (entityType) {
      query = query.where("entityType", "=", entityType);
    }

    const mapping = await query.executeTakeFirst();
    return (mapping as ExternalIntegrationMapping) ?? null;
  }

  /**
   * Get every mapping for an external ID — for the many-to-one relationships
   * `link()`'s `allowDuplicateExternalId` option enables. `getByExternalId`
   * (singular) uses `executeTakeFirst()` and can only ever surface one of them.
   */
  async getAllByExternalId(
    integration: string,
    externalId: string,
    entityType?: string
  ): Promise<ExternalIntegrationMapping[]> {
    let query = this.db
      .selectFrom("externalIntegrationMapping")
      .selectAll()
      .where("integration", "=", integration)
      .where("externalId", "=", externalId)
      .where("companyId", "=", this.companyId);

    if (entityType) {
      query = query.where("entityType", "=", entityType);
    }

    const mappings = await query.execute();
    return mappings as ExternalIntegrationMapping[];
  }

  /**
   * Get all mappings for a Carbon entity (across all integrations).
   */
  async getAllByEntity(
    entityType: string,
    entityId: string
  ): Promise<ExternalIntegrationMapping[]> {
    const mappings = await this.db
      .selectFrom("externalIntegrationMapping")
      .selectAll()
      .where("entityType", "=", entityType)
      .where("entityId", "=", entityId)
      .where("companyId", "=", this.companyId)
      .execute();

    return mappings as ExternalIntegrationMapping[];
  }

  /**
   * Get all mappings for an integration.
   */
  async getAllByIntegration(
    integration: string,
    entityType?: string
  ): Promise<ExternalIntegrationMapping[]> {
    let query = this.db
      .selectFrom("externalIntegrationMapping")
      .selectAll()
      .where("integration", "=", integration)
      .where("companyId", "=", this.companyId);

    if (entityType) {
      query = query.where("entityType", "=", entityType);
    }

    const mappings = await query.execute();
    return mappings as ExternalIntegrationMapping[];
  }

  /**
   * Check if a mapping already exists and is up to date.
   * Returns true if the mapping exists and remoteUpdatedAt >= the provided timestamp.
   */
  async isUpToDate(
    integration: string,
    externalId: string,
    remoteUpdatedAt: Date
  ): Promise<boolean> {
    const mapping = await this.getByExternalId(integration, externalId);

    if (!mapping?.remoteUpdatedAt) {
      return false;
    }

    return new Date(mapping.remoteUpdatedAt) >= remoteUpdatedAt;
  }

  /**
   * Get entity IDs that don't have a mapping for a specific integration.
   * Useful for finding entities that need to be synced.
   */
  async getUnsyncedEntityIds(
    entityType: string,
    tableName: string,
    integration: string,
    limit: number
  ): Promise<string[]> {
    const result = await (this.db as any)
      .selectFrom(tableName)
      .leftJoin("externalIntegrationMapping as m", (join: any) =>
        join
          .onRef("m.entityId", "=", `${tableName}.id`)
          .on("m.entityType", "=", entityType)
          .on("m.integration", "=", integration)
          .on("m.companyId", "=", this.companyId)
      )
      .select([`${tableName}.id`])
      .where(`${tableName}.companyId`, "=", this.companyId)
      .where("m.id", "is", null)
      .limit(limit)
      .execute();

    return (result as Array<{ id: string }>).map((r) => r.id);
  }

  /**
   * Update only the lastSyncedAt timestamp for a mapping.
   */
  async touchLastSyncedAt(
    entityType: string,
    entityId: string,
    integration: string,
    remoteUpdatedAt?: Date
  ): Promise<void> {
    const now = new Date().toISOString();
    await this.db
      .updateTable("externalIntegrationMapping")
      .set({
        lastSyncedAt: now,
        updatedAt: now,
        ...(remoteUpdatedAt
          ? { remoteUpdatedAt: remoteUpdatedAt.toISOString() }
          : {})
      })
      .where("entityType", "=", entityType)
      .where("entityId", "=", entityId)
      .where("integration", "=", integration)
      .where("companyId", "=", this.companyId)
      .execute();
  }

  /**
   * Batch link multiple entities to external IDs.
   * If remoteUpdatedAt is not provided for a mapping, it defaults to the current timestamp.
   */
  async linkBatch(
    mappings: Array<{
      entityType: string;
      entityId: string;
      integration: string;
      externalId: string;
      options?: LinkOptions;
    }>
  ): Promise<void> {
    if (mappings.length === 0) return;

    const now = new Date().toISOString();

    const values = mappings.map((m) => ({
      entityType: m.entityType,
      entityId: m.entityId,
      integration: m.integration,
      externalId: m.externalId,
      allowDuplicateExternalId: m.options?.allowDuplicateExternalId ?? false,
      companyId: this.companyId,
      metadata: m.options?.metadata ?? null,
      lastSyncedAt: now,
      // Default to current timestamp if remoteUpdatedAt is not provided
      remoteUpdatedAt:
        m.options?.remoteUpdatedAt instanceof Date
          ? m.options.remoteUpdatedAt.toISOString()
          : (m.options?.remoteUpdatedAt ?? now),
      createdBy: m.options?.createdBy ?? null,
      createdAt: now,
      updatedAt: now
    }));

    await this.db
      .insertInto("externalIntegrationMapping")
      .values(values as any)
      .onConflict((oc) =>
        oc
          .columns(["entityType", "entityId", "integration", "companyId"])
          .doUpdateSet((eb) => ({
            externalId: eb.ref("excluded.externalId"),
            allowDuplicateExternalId: eb.ref(
              "excluded.allowDuplicateExternalId"
            ),
            metadata: eb.ref("excluded.metadata"),
            lastSyncedAt: eb.ref("excluded.lastSyncedAt"),
            remoteUpdatedAt: eb.ref("excluded.remoteUpdatedAt"),
            updatedAt: eb.ref("excluded.updatedAt")
          }))
      )
      .execute();
  }
}

/**
 * Create a new ExternalIntegrationMappingService instance.
 */
export function createMappingService(
  db: Kysely<KyselyDatabase> | KyselyTx,
  companyId: string
): ExternalIntegrationMappingService {
  return new ExternalIntegrationMappingService(db, companyId);
}