6 examples
Orphaned record
Record disconnected from related database entries.
[ FAQ1 ]
What is an orphaned record?
An orphaned record occurs when a child record remains in a database after its corresponding parent record has been removed, leaving a reference to a non-existent entity. Typically, databases use foreign keys to enforce referential integrity, ensuring related data remains consistent. However, if constraints like foreign keys or cascade delete operations aren't properly defined or used, deleting parent records can unintentionally leave behind orphaned entries. These orphaned records complicate database management, impair data integrity, and may lead to unexpected application behavior or logic errors.
[ FAQ2 ]
How to fix orphaned records
To fix orphaned records, first identify them using SQL queries or database integrity-checking tools that pinpoint entries violating foreign key constraints. After identifying orphaned records, you can delete them manually or script automated cleanup routines. Establish robust foreign key constraints and configure cascade delete operations appropriately in your database schema to automatically remove dependent records upon deletion of a parent. Regularly audit your database to identify and rectify potential orphaned records proactively, ensuring continuous data integrity and application reliability.
diff block
+import { MigrationInterface, QueryRunner } from 'typeorm';
+
+export class DropForeignKeyOnCustomerEntitlement1739356522949
+ implements MigrationInterface
+{
+ name = 'DropForeignKeyOnCustomerEntitlement1739356522949';
+
+ public async up(queryRunner: QueryRunner): Promise<void> {
+ await queryRunner.query(
+ `ALTER TABLE "core"."billingEntitlement" DROP CONSTRAINT "FK_766a1918aa3dbe0d67d3df62356"`,
+ );
greptile
logic: Dropping this FK constraint removes database-level validation that entitlements reference valid customers. Consider adding application-level validation to prevent orphaned records.
diff block
+import { Knex } from "knex";
+
+import { TableName } from "../schemas";
+import { createOnUpdateTrigger, dropOnUpdateTrigger } from "../utils";
+
+// TODO: can attach default SSH login mappings to a host group
+// TODO: can attach default user SSH CA and host SSH CA (convert existing project level ones to a group)
+
+export async function up(knex: Knex): Promise<void> {
+ if (!(await knex.schema.hasTable(TableName.SshHostGroup))) {
+ await knex.schema.createTable(TableName.SshHostGroup, (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.unique(["projectId", "name"]);
+ });
+ await createOnUpdateTrigger(knex, TableName.SshHostGroup);
+ }
+
+ if (!(await knex.schema.hasTable(TableName.SshHostGroupMembership))) {
+ await knex.schema.createTable(TableName.SshHostGroupMembership, (t) => {
+ t.uuid("id", { primaryKey: true }).defaultTo(knex.fn.uuid());
+ t.timestamps(true, true, true);
+ t.uuid("sshHostGroupId").notNullable();
+ t.foreign("sshHostGroupId").references("id").inTable(TableName.SshHostGroup).onDelete("CASCADE");
+ t.uuid("sshHostId").notNullable();
+ t.foreign("sshHostId").references("id").inTable(TableName.SshHost).onDelete("CASCADE");
+ t.unique(["sshHostGroupId", "sshHostId"]);
+ });
+ await createOnUpdateTrigger(knex, TableName.SshHostGroupMembership);
+ }
+
+ const hasGroupColumn = await knex.schema.hasColumn(TableName.SshHostLoginUser, "sshHostGroupId");
+ if (!hasGroupColumn) {
+ await knex.schema.alterTable(TableName.SshHostLoginUser, (t) => {
+ t.uuid("sshHostGroupId").nullable();
+ t.uuid("sshHostId").nullable().alter();
+ });
greptile
logic: Missing foreign key constraint for sshHostGroupId in SshHostLoginUser table. This could lead to orphaned records.
suggested fix
+ t.uuid("sshHostGroupId").nullable().references("id").inTable(TableName.SshHostGroup).onDelete("CASCADE");
t.uuid("sshHostId").nullable().alter();
});
diff block
+package com.egardia.sunrise.controller;
+
+import com.egardia.dto.account.AccountInformation;
+import com.egardia.sunrise.entity.SunriseBaseEntity.DeviceDetails;
+import com.egardia.sunrise.entity.SunriseLogin;
+import com.egardia.sunrise.entity.SunriseOrder;
+import com.egardia.sunrise.entity.SunriseOrderResponse;
+import com.egardia.sunrise.entity.SunriseOrderResponse.Status;
+import com.egardia.sunrise.entity.SunriseOrderTypes.ActivationOrder;
+import com.egardia.sunrise.entity.SunriseOrderTypes.CeaseOrder;
+import com.egardia.sunrise.entity.SunriseOrderTypes.ModifyOrder;
+import com.egardia.sunrise.entity.SunriseOrderTypes.SuspendOrder;
+import com.egardia.sunrise.entity.SunriseSite;
+import com.egardia.sunrise.entity.SunriseSite.AccountState;
+import com.egardia.sunrise.exception.InvalidOrderException;
+import com.egardia.sunrise.repository.SunriseLoginRepository;
+import com.egardia.sunrise.repository.SunriseOrderRepository;
+import com.egardia.sunrise.repository.SunriseSiteRepository;
+import com.egardia.sunrise.service.EgardiaAccountService;
+import com.egardia.sunrise.service.EgardiaAccountService.HomeUserRel;
+import com.fasterxml.jackson.core.JsonProcessingException;
+import com.fasterxml.jackson.databind.ObjectMapper;
+import jakarta.transaction.Transactional;
+import jakarta.validation.ConstraintViolation;
+import jakarta.validation.Validator;
+import java.time.OffsetDateTime;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Set;
+import java.util.function.Consumer;
+import java.util.stream.Collectors;
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.http.HttpStatus;
+import org.springframework.http.ResponseEntity;
+import org.springframework.web.bind.annotation.GetMapping;
+import org.springframework.web.bind.annotation.PostMapping;
+import org.springframework.web.bind.annotation.RequestBody;
+import org.springframework.web.bind.annotation.RequestMapping;
+import org.springframework.web.bind.annotation.RestController;
+import org.springframework.web.client.HttpClientErrorException;
+
+@Slf4j
+@Transactional
+@RestController
+@RequestMapping("/v1/order")
+public class SunriseOrderController {
+
+ private static final String EMAIL_IN_USE = "Email already in use";
+
+ @Autowired
+ private SunriseOrderRepository sunriseOrderRepository;
+ @Autowired
+ private SunriseSiteRepository sunriseSiteRepository;
+ @Autowired
+ private SunriseLoginRepository sunriseLoginRepository;
+ @Autowired
+ private ObjectMapper objectMapper;
+ @Autowired
+ private Validator validator;
+
+ @Autowired
+ private EgardiaAccountService egardiaAccountService;
+
+ @GetMapping()
+ public String test() {
+ System.out.println("test endpoint");
+ return "orders endpoint ok";
+ }
+
+ @Transactional
+ @PostMapping()
+ public ResponseEntity<SunriseOrderResponse> newOrder(@RequestBody SunriseOrder order)
+ throws JsonProcessingException {
+ SunriseOrder.OrderType orderType =
+ order.getOrderDetails() != null ? order.getOrderDetails().getOrderType() : null;
+
+ Set<ConstraintViolation<SunriseOrder>> violations;
+ switch (orderType) {
+ case ACTIVATION -> violations = validator.validate(order, ActivationOrder.class);
+ case MODIFY, DOWNGRADE -> violations = validator.validate(order, ModifyOrder.class);
+ case SUSPEND -> violations = validator.validate(order, SuspendOrder.class);
+ case CEASE -> violations = validator.validate(order, CeaseOrder.class);
+ case null, default -> {
+ throw new InvalidOrderException(order, Status.ERROR_INVALID_ORDER_TYPE,
+ "Validation failed: Invalid value for field orderDetails.orderType. Allowed values are: ACTIVATION, MODIFY, SUSPEND, CEASE");
+ }
+ }
+
+ if (!violations.isEmpty()) {
+ String errorMessage = violations.stream()
+ .map(violation -> violation.getPropertyPath() + ": " + violation.getMessage())
+ .collect(Collectors.joining(", "));
+
+ throw new InvalidOrderException(order, Status.ERROR_VALIDATION_FAILED,
+ "Validation failed: [" + errorMessage + "]");
+ }
+
+ log.info("Received order: " + order);
+ SunriseOrderResponse res = new SunriseOrderResponse(order);
+
+ SunriseOrder existingOrder = sunriseOrderRepository.findByOrderIdForWrite(
+ order.getOrderId()).orElse(null);
+ if (existingOrder != null) {
+ throw new InvalidOrderException(order, Status.ERROR_DUPLICATE_ORDER,
+ "An order with this ID has already been handled");
+ }
+
+ SunriseSite site = sunriseSiteRepository.findBySiteIdForWrite(order.getSiteId())
+ .orElse(null);
+
+ boolean isActiveSite =
+ site != null && site.getAccountState() == SunriseSite.AccountState.ACTIVE;
+ boolean isActivatable =
+ site == null
+ || site.getAccountState() == SunriseSite.AccountState.CEASED
+ || site.getAccountState() == SunriseSite.AccountState.SUSPENDED;
+
+ if (!isActiveSite && orderType != SunriseOrder.OrderType.ACTIVATION) {
+ throw new InvalidOrderException(order, Status.ERROR_MISSING_SITE,
+ "No active site found for the siteId provided in this order.");
+ } else if (!isActivatable && orderType == SunriseOrder.OrderType.ACTIVATION) {
+ throw new InvalidOrderException(order, Status.ERROR_ALREADY_PROVISIONED,
+ "A site with this siteId is already provisioned.");
+ }
+
+ return switch (orderType) {
+ case ACTIVATION -> handleActivationOrder(order, res);
+ case MODIFY, DOWNGRADE -> handleModificationOrder(order, site, res);
+ case SUSPEND -> handleSuspendOrder(order, site, res);
+ case CEASE -> handleCeaseOrder(order, site, res);
+ };
+ }
+
+ private RegistrationInfo getRegistrationInfo(SunriseOrder order) {
+ AccountInformation accountInformation;
+ String gatewayCommonName;
+ try {
+ accountInformation = order.getAccountInformation();
+ gatewayCommonName = order.getGatewayCommonName();
+ } catch (IllegalArgumentException e) {
+ Status status = Status.valueOf(e.getMessage());
+
+ switch (status) {
+ case ERROR_MISSING_GATEWAY:
+ throw new InvalidOrderException(order, status,
+ "No accepted gateway found among the devices supplied in the order");
+ case ERROR_MULTIPLE_GATEWAYS:
+ throw new InvalidOrderException(order, status,
+ "Multiple gateways found among the devices supplied in the order");
+ default:
+ throw e;
+ }
+ } catch (Exception e) {
+ throw new InvalidOrderException(order, Status.ERROR,
+ "Error occurred while processing the order");
+ }
+ return new RegistrationInfo(accountInformation, gatewayCommonName);
+ }
+
+ private void checkExistingDevices(SunriseOrder order) {
+ checkExistingDevices(order, null);
+ }
+
+ private void checkExistingDevices(SunriseOrder order, SunriseSite site) {
+ List<String> deviceSerialNumbers = order.getDeviceDetails().stream()
+ .map(DeviceDetails::getDeviceSerialNumber)
+ .toList();
+
+ List<SunriseSite> sitesWithExistingDevice = sunriseSiteRepository.findByDeviceSerialNumbers(
+ deviceSerialNumbers);
+ // Filter out the site that is being modified
+ if (site != null) {
+ sitesWithExistingDevice = sitesWithExistingDevice.stream()
+ .filter(existingSite -> !existingSite.getSiteId().equals(site.getSiteId()))
+ .toList();
+ }
+ // Get the first site that has a matching device
+ SunriseSite siteWithExistingDevice = sitesWithExistingDevice.stream()
+ .findFirst()
+ .orElse(null);
+ // Find device that matches the serial number
+ if (siteWithExistingDevice != null) {
+ List<String> existingDeviceSerialNumbers = siteWithExistingDevice.getDeviceDetails()
+ .stream()
+ .map(DeviceDetails::getDeviceSerialNumber)
+ .toList();
+ String matchingSerial = deviceSerialNumbers.stream()
+ .filter(existingDeviceSerialNumbers::contains)
+ .findFirst()
+ .orElseThrow();
+ throw new InvalidOrderException(order, Status.ERROR_DEVICE_ALREADY_IN_USE,
+ "Device with serial number " + matchingSerial + " is already in use");
+ }
+ }
+
+ /**
+ * Handles activation order Checks the following things:
+ * - If the device serial numbers are already in use
+ * - If the gateway is missing or multiple gateways are found
+ *
+ * @param order The order to handle
+ * @param res The response object to update
+ * @return ResponseEntity<SunriseOrderResponse> The response object
+ */
+ private ResponseEntity<SunriseOrderResponse> handleActivationOrder(SunriseOrder order,
+ SunriseOrderResponse res) {
+
+ checkExistingDevices(order);
+
+ RegistrationInfo registrationInfo = getRegistrationInfo(order);
+ AccountInformation accountInformation = registrationInfo.accountInformation;
+ String gatewayCommonName = registrationInfo.gatewayCommonName;
+
+ // Store the randomly generated password (encrypted) so we can use the same password to get the token later
+ SunriseLogin login = new SunriseLogin(order.getSiteId(), accountInformation.getPassword());
+
+ // Once we know everything is in working order, we can save the order and login
+ sunriseOrderRepository.save(order);
+ sunriseLoginRepository.save(login);
greptile
logic: saving order and login should be moved after successful account creation to prevent orphaned records
diff block
+import { MigrationInterface, QueryRunner } from 'typeorm';
+
+export class AddIntegrationsMigrationBranchEntities1739563469632
+ implements MigrationInterface
+{
+ name = 'AddIntegrationsMigrationBranchEntities1739563469632';
+
+ public async up(queryRunner: QueryRunner): Promise<void> {
+ await queryRunner.query(
+ `CREATE TABLE "core"."telephony" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "memberId" character varying NOT NULL DEFAULT '', "numberExtension" character varying NOT NULL DEFAULT '', "createdAt" TIMESTAMP NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP NOT NULL DEFAULT now(), "type" character varying DEFAULT '', "extensionName" character varying DEFAULT '', "extensionGroup" character varying DEFAULT '', "dialingPlan" character varying DEFAULT '', "areaCode" character varying DEFAULT '', "SIPPassword" character varying DEFAULT '', "callerExternalID" character varying DEFAULT '', "pullCalls" character varying DEFAULT '', "listenToCalls" boolean DEFAULT false, "recordCalls" boolean DEFAULT false, "blockExtension" boolean DEFAULT false, "enableMailbox" boolean DEFAULT false, "emailForMailbox" character varying DEFAULT '', "fowardAllCalls" character varying DEFAULT '', "fowardBusyNotAvailable" character varying DEFAULT '', "fowardOfflineWithoutService" character varying DEFAULT '', "extensionAllCallsOrOffline" character varying DEFAULT '', "externalNumberAllCallsOrOffline" character varying DEFAULT '', "destinyMailboxAllCallsOrOffline" character varying DEFAULT '', "extensionBusy" character varying DEFAULT '', "externalNumberBusy" character varying DEFAULT '', "destinyMailboxBusy" character varying DEFAULT '', "ramal_id" character varying DEFAULT '', "advancedFowarding1" character varying DEFAULT '', "advancedFowarding2" character varying DEFAULT '', "advancedFowarding3" character varying DEFAULT '', "advancedFowarding4" character varying DEFAULT '', "advancedFowarding5" character varying DEFAULT '', "advancedFowarding1Value" character varying DEFAULT '', "advancedFowarding2Value" character varying DEFAULT '', "advancedFowarding3Value" character varying DEFAULT '', "advancedFowarding4Value" character varying DEFAULT '', "advancedFowarding5Value" character varying DEFAULT '', "workspaceId" uuid, CONSTRAINT "UQ_7d63a6aaa99acb4fb9c4773d0a5" UNIQUE ("memberId"), CONSTRAINT "UQ_dd1f243a489880fcddf0b592c08" UNIQUE ("numberExtension"), CONSTRAINT "PK_f380a6d1e8ff45b6562fdaa17c8" PRIMARY KEY ("id"))`,
+ );
+ await queryRunner.query(
+ `CREATE TABLE "core"."whatsappIntegration" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "label" character varying NOT NULL, "phoneId" character varying NOT NULL, "businessAccountId" character varying NOT NULL, "appId" character varying NOT NULL, "appKey" character varying NOT NULL, "accessToken" character varying NOT NULL, "verifyToken" character varying NOT NULL, "disabled" boolean NOT NULL DEFAULT false, "sla" integer NOT NULL DEFAULT '30', "workspaceId" uuid, CONSTRAINT "PK_39f3dbc97ba512ae7733c5e313d" PRIMARY KEY ("id"))`,
+ );
+ await queryRunner.query(
+ `CREATE TYPE "core"."inbox_integrationtype_enum" AS ENUM('whatsapp', 'messenger')`,
+ );
+ await queryRunner.query(
+ `CREATE TABLE "core"."inbox" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "integrationType" "core"."inbox_integrationtype_enum" NOT NULL DEFAULT 'whatsapp', "whatsappIntegrationId" uuid, "workspaceId" uuid, CONSTRAINT "PK_ab7abc299fab4bb4f965549c819" PRIMARY KEY ("id"))`,
+ );
+ await queryRunner.query(
+ `CREATE TABLE "core"."agent" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "memberId" character varying NOT NULL, "isAdmin" boolean NOT NULL DEFAULT false, "isActive" boolean NOT NULL DEFAULT true, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), "workspaceId" uuid, CONSTRAINT "PK_1000e989398c5d4ed585cf9a46f" PRIMARY KEY ("id"))`,
+ );
+ await queryRunner.query(
+ `CREATE TABLE "core"."sector" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "icon" character varying NOT NULL DEFAULT '', "name" character varying NOT NULL, "topics" jsonb, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), "workspaceId" uuid, CONSTRAINT "PK_668b2ea8a2f534425407732f3ab" PRIMARY KEY ("id"))`,
+ );
+ await queryRunner.query(
+ `CREATE TABLE "core"."agentSectors" ("agentId" uuid NOT NULL, "sectorId" uuid NOT NULL, CONSTRAINT "PK_0c18c8b27732410238951935921" PRIMARY KEY ("agentId", "sectorId"))`,
+ );
+ await queryRunner.query(
+ `CREATE INDEX "IDX_1881105c19d856bd8a6584927d" ON "core"."agentSectors" ("agentId") `,
+ );
+ await queryRunner.query(
+ `CREATE INDEX "IDX_38087e38dbfd5622ef7c25b6da" ON "core"."agentSectors" ("sectorId") `,
+ );
+ await queryRunner.query(
+ `CREATE TABLE "core"."agentInboxes" ("agentId" uuid NOT NULL, "inboxId" uuid NOT NULL, CONSTRAINT "PK_5dfa966345cd5bdff1a46baa3eb" PRIMARY KEY ("agentId", "inboxId"))`,
+ );
+ await queryRunner.query(
+ `CREATE INDEX "IDX_026ec88fe86e63746eff660903" ON "core"."agentInboxes" ("agentId") `,
+ );
+ await queryRunner.query(
+ `CREATE INDEX "IDX_8f880c98aefa6e4d00b22312df" ON "core"."agentInboxes" ("inboxId") `,
+ );
+ await queryRunner.query(
+ `ALTER TABLE "core"."telephony" ADD CONSTRAINT "FK_37d14bf27d627391ae02a396238" FOREIGN KEY ("workspaceId") REFERENCES "core"."workspace"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`,
greptile
style: ON DELETE NO ACTION on telephony.workspaceId foreign key could cause orphaned records if workspace is deleted
diff block
);
}
+ async batchCleanOnboardingWorkspaces(
+ workspaceIds: string[],
+ dryRun = false,
+ ): Promise<void> {
+ this.logger.log(
+ `${dryRun ? 'DRY RUN - ' : ''}batchCleanOnboardingWorkspaces running...`,
+ );
+
+ const workspaces = await this.workspaceRepository.find({
+ where: {
+ id: In(workspaceIds),
+ activationStatus: In([
+ WorkspaceActivationStatus.PENDING_CREATION,
+ WorkspaceActivationStatus.ONGOING_CREATION,
+ ]),
+ },
+ withDeleted: true,
+ });
+
+ if (workspaces.length !== 0) {
+ if (!dryRun) {
+ await this.workspaceRepository.delete(
+ workspaces.map((workspace) => workspace.id),
+ );
greptile
logic: Direct deletion without cleanup of related data could leave orphaned records. Consider using workspaceService.deleteWorkspace() instead of repository.delete()
diff block
});
};
+const deleteAllDowloadSources = async () => {
+ await db.transaction("rw", repacksTable, downloadSourcesTable, async () => {
+ await repacksTable.clear();
+ await downloadSourcesTable.clear();
+ });
greptile
logic: clearing tables in this order could leave orphaned records if the second clear fails - consider reversing order
Want to avoid this bug in your codebase? Try Greptile.
Avoid this bug!