Skip to content

Commit 446f268

Browse files
TheLastCicadaTheLastCicada
committed
fix(V2): reconcile staged CSV batch updates safely
Merge CSV batch updates with pending staged rows instead of rebuilding from only the committed DB state, and reject complex pending unit updates such as splits before they can be clobbered. Also tighten docs and regression coverage around staged merge behavior, serial recomputation, and add a standalone live CSV batch test plus safer live helper fallback lookup.
1 parent 1e82c90 commit 446f268

8 files changed

Lines changed: 1160 additions & 32 deletions

File tree

‎docs/cadt_rpc_api_v2.md‎

Lines changed: 13 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2420,7 +2420,7 @@ CSV batch upload is for **flat parent records only** — each row represents one
24202420

24212421
CSV headers may use either camelCase attribute names (e.g., `cadTrustProjectId`) or snake_case DB column names (e.g., `cad_trust_project_id`).
24222422

2423-
**Update behavior**: When a row includes a `cadTrustProjectId` that matches an existing project, the CSV row is **merged** with the existing DB record — fields present in the CSV overwrite the existing values; fields absent from the CSV retain their current values. This differs from the REST `PUT` endpoint, which requires all fields.
2423+
**Update behavior**: When a row includes a `cadTrustProjectId` that matches an existing project, the CSV row is **merged** with the latest pending state for that project — existing DB values plus any already-staged uncommitted edits for the same project. Fields present in the CSV overwrite that merged state; fields absent from the CSV retain their current values. This differs from the REST `PUT` endpoint, which requires all fields.
24242424

24252425
**Validation**: INSERT rows are validated for required fields (`projectRegistryName`, `projectId`, `projectName`) and foreign key existence (`cadTrustProgramId` must reference an existing or staged program). UPDATE rows skip required-field checks since missing fields are merged from the existing record. Rows that fail validation are skipped and their errors are returned in the response with row numbers. The CSV validation is intentionally more lenient than the REST API — fields like `projectLink` and `projectStatusDate` that are required in the REST schema are optional in CSV batch upload. If **all** rows fail validation, the response returns HTTP 400 with `success: false`.
24262426

@@ -2481,6 +2481,10 @@ Response (failure — no rows staged, HTTP 400)
24812481
}
24822482
```
24832483

2484+
Other 400 responses:
2485+
- Missing file upload: `{ "message": "Cannot find the required csv file in request", "success": false }`
2486+
- Parser/runtime failure: `{ "message": "Batch Upload Failed.", "error": "<details>", "success": false }`
2487+
24842488
---
24852489

24862490
<a id="project-put-examples"></a>
@@ -2592,7 +2596,7 @@ curl --location -g --request DELETE 'http://localhost:31310/v2/project/693d37f6-
25922596
Response
25932597
```json
25942598
{
2595-
"message": "Project deletion staged successfully",
2599+
"message": "Project delete staged successfully",
25962600
"success": true
25972601
}
25982602
```
@@ -3732,9 +3736,9 @@ CSV batch upload is for **flat parent records only** — each row represents one
37323736

37333737
CSV headers may use either camelCase attribute names (e.g., `cadTrustUnitId`) or snake_case DB column names (e.g., `cad_trust_unit_id`).
37343738

3735-
**Update behavior**: When a row includes a `cadTrustUnitId` that matches an existing unit, the CSV row is **merged** with the existing DB record — fields present in the CSV overwrite the existing values; fields absent from the CSV retain their current values. This differs from the REST `PUT` endpoint, which requires all fields.
3739+
**Update behavior**: When a row includes a `cadTrustUnitId` that matches an existing unit, the CSV row is **merged** with the latest pending state for that unit — existing DB values plus any already-staged uncommitted edits for the same unit. Fields present in the CSV overwrite that merged state; fields absent from the CSV retain their current values. This differs from the REST `PUT` endpoint, which requires all fields.
37363740

3737-
**Serial ID derivation**: If `unitSerialId` is not provided but `unitStartBlock` and `unitEndBlock` are, the serial ID is automatically derived as `<unitStartBlock>-<unitEndBlock>`.
3741+
**Serial ID derivation**: If `unitSerialId` is not provided but `unitStartBlock` and `unitEndBlock` are, the serial ID is automatically derived as `<unitStartBlock>-<unitEndBlock>`. This also applies to UPDATE rows after merge: if you change one block boundary and omit `unitSerialId`, the serial ID is recomputed from the merged start/end block values.
37383742

37393743
**Validation**: INSERT rows are validated for required fields (`unitStartBlock`, `unitEndBlock`, `unitVintageYear`, `cadTrustIssuanceId`) and foreign key existence (`cadTrustIssuanceId` must reference an existing or staged issuance). `unitSerialId` is not required if `unitStartBlock` and `unitEndBlock` are provided (it is derived automatically). UPDATE rows skip required-field checks since missing fields are merged from the existing record. Rows that fail validation are skipped and their errors are returned in the response with row numbers. If **all** rows fail validation, the response returns HTTP 400 with `success: false`.
37403744

@@ -3781,6 +3785,10 @@ Response (failure — no rows staged, HTTP 400)
37813785
}
37823786
```
37833787

3788+
Other 400 responses:
3789+
- Missing file upload: `{ "message": "Cannot find the required csv file", "success": false }`
3790+
- Parser/runtime failure: `{ "message": "Batch Upload Failed.", "error": "<details>", "success": false }`
3791+
37843792
---
37853793

37863794
<a id="unit-put-examples"></a>
@@ -3870,7 +3878,7 @@ curl --location -g --request DELETE 'localhost:31310/v2/unit/104b082c-b112-4c39-
38703878
Response
38713879
```json
38723880
{
3873-
"message": "Unit deletion staged successfully",
3881+
"message": "Unit delete staged successfully",
38743882
"success": true
38753883
}
38763884
```

‎src/models/v2/project-v2.model.js‎

Lines changed: 34 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,8 @@ import {
1717
toDbFieldNames,
1818
stripUnknownDbFields,
1919
validateRequiredFields,
20+
buildPendingCsvMergeBase,
21+
stageConsolidatedCsvRecord,
2022
} from '../../utils/v2-xls.js';
2123
import { assertRecordExistanceOrStaged } from '../../utils/v2-data-assertions.js';
2224
import { ProgramV2 } from './program-v2.model.js';
@@ -419,16 +421,38 @@ class ProjectV2 extends Model {
419421

420422
if (projectId) {
421423
const existing = await ProjectV2.findByPk(projectId);
422-
if (!existing) {
424+
const {
425+
mergedBase,
426+
hasPendingDelete,
427+
hasMultiRecordPendingRow,
428+
} = await buildPendingCsvMergeBase(ProjectV2, projectId, existing, {
429+
transaction,
430+
});
431+
432+
if (hasPendingDelete) {
433+
errors.push({
434+
row: rowNum,
435+
error: `Cannot update project ${projectId}: it already has a pending staged delete`,
436+
});
437+
continue;
438+
}
439+
if (hasMultiRecordPendingRow) {
440+
errors.push({
441+
row: rowNum,
442+
error: `Cannot update project ${projectId}: it already has a complex pending staged update`,
443+
});
444+
continue;
445+
}
446+
if (!existing && Object.keys(mergedBase).length === 0) {
423447
errors.push({ row: rowNum, error: `Project with cadTrustProjectId ${projectId} does not exist` });
424448
continue;
425449
}
426-
if (existing.orgUid !== orgUid) {
450+
if (mergedBase.orgUid !== orgUid) {
427451
errors.push({ row: rowNum, error: `Cannot update project ${projectId}: belongs to a different organization` });
428452
continue;
429453
}
430-
action = 'UPDATE';
431-
mergedRecord = { ...existing.toJSON(), ...row };
454+
action = existing ? 'UPDATE' : 'INSERT';
455+
mergedRecord = { ...mergedBase, ...row };
432456
} else {
433457
row.cadTrustProjectId = uuidv4();
434458
action = 'INSERT';
@@ -469,14 +493,12 @@ class ProjectV2 extends Model {
469493

470494
cleaned.org_uid = orgUid;
471495

472-
await StagingV2.upsert(
473-
{
474-
uuid: mergedRecord.cadTrustProjectId,
475-
action,
476-
table: 'project',
477-
data: JSON.stringify([cleaned]),
478-
},
479-
{ transaction },
496+
await stageConsolidatedCsvRecord(
497+
ProjectV2,
498+
mergedRecord.cadTrustProjectId,
499+
action,
500+
cleaned,
501+
transaction,
480502
);
481503
stagedCount++;
482504
} catch (err) {

‎src/models/v2/unit-v2.model.js‎

Lines changed: 42 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,8 @@ import {
2121
toDbFieldNames,
2222
stripUnknownDbFields,
2323
validateRequiredFields,
24+
buildPendingCsvMergeBase,
25+
stageConsolidatedCsvRecord,
2426
} from '../../utils/v2-xls.js';
2527
import { assertRecordExistanceOrStaged } from '../../utils/v2-data-assertions.js';
2628
import { IssuanceV2 } from './issuance-v2.model.js';
@@ -484,16 +486,46 @@ class UnitV2 extends Model {
484486

485487
if (unitId) {
486488
const existing = await UnitV2.findByPk(unitId);
487-
if (!existing) {
489+
const {
490+
mergedBase,
491+
hasPendingDelete,
492+
hasMultiRecordPendingRow,
493+
} = await buildPendingCsvMergeBase(UnitV2, unitId, existing, {
494+
transaction,
495+
});
496+
497+
if (hasPendingDelete) {
498+
errors.push({
499+
row: rowNum,
500+
error: `Cannot update unit ${unitId}: it already has a pending staged delete`,
501+
});
502+
continue;
503+
}
504+
if (hasMultiRecordPendingRow) {
505+
errors.push({
506+
row: rowNum,
507+
error: `Cannot update unit ${unitId}: it already has a complex pending staged update`,
508+
});
509+
continue;
510+
}
511+
if (!existing && Object.keys(mergedBase).length === 0) {
488512
errors.push({ row: rowNum, error: `Unit with cadTrustUnitId ${unitId} does not exist` });
489513
continue;
490514
}
491-
if (existing.orgUid !== orgUid) {
515+
if (mergedBase.orgUid !== orgUid) {
492516
errors.push({ row: rowNum, error: `Cannot update unit ${unitId}: belongs to a different organization` });
493517
continue;
494518
}
495-
action = 'UPDATE';
496-
mergedRecord = { ...existing.toJSON(), ...row };
519+
action = existing ? 'UPDATE' : 'INSERT';
520+
mergedRecord = { ...mergedBase, ...row };
521+
522+
const changedBlockRange =
523+
!row.unitSerialId &&
524+
(row.unitStartBlock !== undefined || row.unitEndBlock !== undefined);
525+
if (changedBlockRange) {
526+
delete mergedRecord.unitSerialId;
527+
UnitV2.prepareXlsRow(mergedRecord);
528+
}
497529
} else {
498530
row.cadTrustUnitId = uuidv4();
499531
action = 'INSERT';
@@ -534,14 +566,12 @@ class UnitV2 extends Model {
534566

535567
cleaned.org_uid = orgUid;
536568

537-
await StagingV2.upsert(
538-
{
539-
uuid: mergedRecord.cadTrustUnitId,
540-
action,
541-
table: 'unit',
542-
data: JSON.stringify([cleaned]),
543-
},
544-
{ transaction },
569+
await stageConsolidatedCsvRecord(
570+
UnitV2,
571+
mergedRecord.cadTrustUnitId,
572+
action,
573+
cleaned,
574+
transaction,
545575
);
546576
stagedCount++;
547577
} catch (err) {

0 commit comments

Comments
 (0)