fix(V2): account for staged deletes in reference guards

TheLastCicada · TheLastCicada · commit 9492108ca2ca · 2026-05-19T13:40:16.000-07:00
diff --git a/src/utils/v2-reference-guards.js b/src/utils/v2-reference-guards.js
@@ -25,6 +25,7 @@ import {
 } from '../models/v2/index.js';
 import { Op } from 'sequelize';
 import { toSnakeCase } from './v2-camel-to-snake.js';
+import { getV2PrimaryKeyField } from './v2-primary-key-utils.js';
 
 const REFERENCE_ERROR_CODE = 'Referenced records must be removed before deletion';
 
@@ -172,6 +173,34 @@ const countStagedReferences = async (table, snakeFkField, recordId, matchPredica
   return count;
 };
 
+const findPendingStagedDeleteIds = async (table, primaryKeyField) => {
+  const stagedRows = await StagingV2.findAll({
+    where: {
+      table,
+      action: 'DELETE',
+      committed: false,
+      failed_commit: false,
+    },
+    raw: true,
+  });
+
+  const deletedIds = new Set();
+  for (const stagedRow of stagedRows) {
+    try {
+      const parsedData = JSON.parse(stagedRow.data);
+      const records = Array.isArray(parsedData) ? parsedData : [parsedData];
+      for (const record of records) {
+        if (record?.[primaryKeyField]) {
+          deletedIds.add(record[primaryKeyField]);
+        }
+      }
+    } catch {
+      continue;
+    }
+  }
+  return deletedIds;
+};
+
 /**
  * Check whether any local records reference the given record (committed + staged).
  *
@@ -191,7 +220,14 @@ export const checkReferences = async (table, recordId) => {
   for (const def of definitions) {
     const { model, fkField, table: refTable, label, buildWhere, stagedMatch } = def;
     const where = buildWhere ? buildWhere(recordId) : { [fkField]: recordId };
-    const mainCount = await model.count({ where });
+    const mainRecords = await model.findAll({ where, raw: true });
+    const primaryKeyAttr = model.primaryKeyAttribute;
+    const primaryKeyField = getV2PrimaryKeyField(refTable) || toSnakeCase(primaryKeyAttr);
+    const pendingDeleteIds = await findPendingStagedDeleteIds(refTable, primaryKeyField);
+    const mainCount = mainRecords.filter((row) => {
+      const rowId = row?.[primaryKeyAttr] ?? row?.[primaryKeyField];
+      return !pendingDeleteIds.has(rowId);
+    }).length;
 
     const snakeFk = toSnakeCase(fkField);
     const stagedCount = stagedMatch
diff --git a/tests/v2/integration/methodology-v2.spec.js b/tests/v2/integration/methodology-v2.spec.js
@@ -476,6 +476,59 @@ describe('V2 Methodology API - Basic CRUD Tests', function () {
       expect(response.body.references[0].count).to.equal(1);
     });
 
+    it('should allow delete when committed references are already staged for deletion', async function () {
+      const methodology = await MethodologyV2.create({
+        cadTrustMethodologyId: uuidv4(),
+        methodologyCode: 'STAGED-DELETE-METHOD-001',
+        methodologyName: 'Methodology With Deleted Reference',
+      });
+
+      const program = await ProgramV2.create({
+        programName: 'Staged Delete Program',
+        programRegistry: 'Test Registry',
+        programRegistryActivityId: 'STAGED-DELETE-001',
+      });
+
+      const project = await ProjectV2.create({
+        cadTrustProjectId: uuidv4(),
+        orgUid: 'test-home-org-v2',
+        projectRegistryName: 'Test Registry',
+        projectId: 'STAGED-DELETE-PROJ-001',
+        projectName: 'Staged Delete Project',
+        cadTrustProgramId: program.cadTrustProgramId,
+      });
+
+      const projectMethodology = await ProjectMethodologyV2.create({
+        cadTrustProjectMethodologyId: uuidv4(),
+        cadTrustProjectId: project.cadTrustProjectId,
+        cadTrustMethodologyId: methodology.cadTrustMethodologyId,
+      });
+
+      await StagingV2.create({
+        uuid: uuidv4(),
+        table: 'project_methodology',
+        action: 'DELETE',
+        data: JSON.stringify([{
+          cad_trust_project_methodology_id: projectMethodology.cadTrustProjectMethodologyId,
+        }]),
+        committed: false,
+        failed_commit: false,
+        is_transfer: false,
+      });
+
+      const response = await supertest(app)
+        .delete(`/v2/methodology/${methodology.cadTrustMethodologyId}`)
+        .expect(200);
+
+      expect(response.body.success).to.be.true;
+      expect(response.body.message).to.equal('Methodology delete staged successfully');
+
+      const stagingRecord = await StagingV2.findOne({
+        where: { table: 'methodology', action: 'DELETE' },
+      });
+      expect(stagingRecord).to.exist;
+    });
+
     it('should return 409 with ?force=true when references still exist', async function () {
       const methodology = await MethodologyV2.create({
         cadTrustMethodologyId: uuidv4(),
