fix(V2): enforce response contract and required-field validation for CSV batch

TheLastCicada · TheLastCicada · commit 1e82c901a06d · 2026-04-15T16:46:51.000-07:00
Return stagedCount/errorCount from batchUpload models. Controllers now
branch response: full success (200), partial success (200 with errors),
or total failure (400 with success:false) when zero rows are staged.

Add required-field validation for INSERT rows — checks allowNull:false
fields from the Sequelize model definition, excluding auto-managed
fields (PK, orgUid, timestamps) and derivable fields (unitSerialId).
UPDATE rows skip this check since missing fields merge from the DB.

Addresses review findings:
- FINDING 1: success:true when 0 rows staged (misleading contract)
- FINDING 2: missing required-field validation for CSV INSERT rows
diff --git a/docs/cadt_rpc_api_v2.md b/docs/cadt_rpc_api_v2.md
@@ -2422,7 +2422,7 @@ CSV headers may use either camelCase attribute names (e.g., `cadTrustProjectId`)
 
 **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.
 
-**Validation**: Rows are validated for foreign key existence (`cadTrustProgramId` must reference an existing program or a staged program record). Rows that fail validation are skipped and their errors are returned in the response. 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.
+**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`.
 
 **Ownership**: UPDATE rows are verified to belong to the home organization. Attempting to update a project owned by another organization will produce an error for that row.
 
@@ -2444,20 +2444,38 @@ Request
 curl --location --request POST 'http://localhost:31310/v2/project/batch' --form 'csv=@"./createProject.csv"'
 ```
 
-Response (success, no row-level errors)
+Response (full success — all rows staged)
 ```json
 {
   "message": "CSV processing complete, your records have been added to the staging table.",
-  "success": true
+  "success": true,
+  "stagedCount": 3,
+  "errorCount": 0
 }
 ```
 
-Response (success with row-level errors — valid rows are still staged)
+Response (partial success — some rows staged, some failed)
 ```json
 {
-  "message": "CSV processing complete, your records have been added to the staging table.",
+  "message": "CSV processing complete. 2 row(s) staged, 1 row(s) skipped due to errors.",
   "success": true,
+  "stagedCount": 2,
+  "errorCount": 1,
+  "errors": [
+    { "row": 3, "error": "cadTrustProgramId 'invalid-id' does not exist" }
+  ]
+}
+```
+
+Response (failure — no rows staged, HTTP 400)
+```json
+{
+  "message": "No rows were staged. All rows failed validation.",
+  "success": false,
+  "stagedCount": 0,
+  "errorCount": 2,
   "errors": [
+    { "row": 2, "error": "Missing required field(s): projectName" },
     { "row": 3, "error": "cadTrustProgramId 'invalid-id' does not exist" }
   ]
 }
@@ -3718,7 +3736,7 @@ CSV headers may use either camelCase attribute names (e.g., `cadTrustUnitId`) or
 
 **Serial ID derivation**: If `unitSerialId` is not provided but `unitStartBlock` and `unitEndBlock` are, the serial ID is automatically derived as `<unitStartBlock>-<unitEndBlock>`.
 
-**Validation**: Rows are validated for foreign key existence (`cadTrustIssuanceId` must reference an existing issuance or a staged issuance record). Rows that fail validation are skipped and their errors are returned in the response.
+**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`.
 
 **Ownership**: UPDATE rows are verified to belong to the home organization. Attempting to update a unit owned by another organization will produce an error for that row.
 
@@ -3727,25 +3745,42 @@ Request
 curl --location --request POST 'http://localhost:31310/v2/unit/batch' --form 'csv=@"./createUnit.csv"'
 ```
 
-Response (success, no row-level errors)
+Response (full success — all rows staged)
 ```json
 {
   "message": "CSV processing complete, your records have been added to the staging table.",
-  "success": true
+  "success": true,
+  "stagedCount": 3,
+  "errorCount": 0
 }
 ```
 
-Response (success with row-level errors — valid rows are still staged)
+Response (partial success — some rows staged, some failed)
 ```json
 {
-  "message": "CSV processing complete, your records have been added to the staging table.",
+  "message": "CSV processing complete. 2 row(s) staged, 1 row(s) skipped due to errors.",
   "success": true,
+  "stagedCount": 2,
+  "errorCount": 1,
   "errors": [
     { "row": 4, "error": "cadTrustIssuanceId 'invalid-id' does not exist" }
   ]
 }
 ```
 
+Response (failure — no rows staged, HTTP 400)
+```json
+{
+  "message": "No rows were staged. All rows failed validation.",
+  "success": false,
+  "stagedCount": 0,
+  "errorCount": 1,
+  "errors": [
+    { "row": 2, "error": "Missing required field(s): unitStartBlock, unitEndBlock" }
+  ]
+}
+```
+
 ---
 
 <a id="unit-put-examples"></a>
diff --git a/src/controllers/v2/project-v2.controller.js b/src/controllers/v2/project-v2.controller.js
@@ -961,15 +961,27 @@ export const batchUpload = async (req, res) => {
     const result = await ProjectV2.batchUpload({ data: req.file.buffer });
 
     const response = {
-      message:
-        'CSV processing complete, your records have been added to the staging table.',
-      success: true,
+      stagedCount: result.stagedCount,
+      errorCount: result.errorCount,
     };
 
-    if (result.errors && result.errors.length > 0) {
+    if (result.errorCount > 0) {
       response.errors = result.errors;
     }
 
+    if (result.stagedCount === 0) {
+      response.success = false;
+      response.message = 'No rows were staged. All rows failed validation.';
+      return res.status(400).json(response);
+    }
+
+    response.success = true;
+    if (result.errorCount > 0) {
+      response.message = `CSV processing complete. ${result.stagedCount} row(s) staged, ${result.errorCount} row(s) skipped due to errors.`;
+    } else {
+      response.message = 'CSV processing complete, your records have been added to the staging table.';
+    }
+
     res.json(response);
   } catch (error) {
     loggerV2.error('[v2]: Batch Upload Failed.', error);
diff --git a/src/controllers/v2/unit-v2.controller.js b/src/controllers/v2/unit-v2.controller.js
@@ -941,15 +941,27 @@ export const batchUpload = async (req, res) => {
     const result = await UnitV2.batchUpload({ data: req.file.buffer });
 
     const response = {
-      message:
-        'CSV processing complete, your records have been added to the staging table.',
-      success: true,
+      stagedCount: result.stagedCount,
+      errorCount: result.errorCount,
     };
 
-    if (result.errors && result.errors.length > 0) {
+    if (result.errorCount > 0) {
       response.errors = result.errors;
     }
 
+    if (result.stagedCount === 0) {
+      response.success = false;
+      response.message = 'No rows were staged. All rows failed validation.';
+      return res.status(400).json(response);
+    }
+
+    response.success = true;
+    if (result.errorCount > 0) {
+      response.message = `CSV processing complete. ${result.stagedCount} row(s) staged, ${result.errorCount} row(s) skipped due to errors.`;
+    } else {
+      response.message = 'CSV processing complete, your records have been added to the staging table.';
+    }
+
     res.json(response);
   } catch (error) {
     loggerV2.error('[v2]: Batch Upload Failed.', error);
diff --git a/src/models/v2/project-v2.model.js b/src/models/v2/project-v2.model.js
@@ -16,6 +16,7 @@ import {
   normalizeCsvHeaders,
   toDbFieldNames,
   stripUnknownDbFields,
+  validateRequiredFields,
 } from '../../utils/v2-xls.js';
 import { assertRecordExistanceOrStaged } from '../../utils/v2-data-assertions.js';
 import { ProgramV2 } from './program-v2.model.js';
@@ -373,7 +374,7 @@ class ProjectV2 extends Model {
    *   6. Upsert into StagingV2
    *
    * @param {Object} csvFile - CSV file object with data buffer
-   * @returns {Promise<Object>} Result with errors array (may be empty)
+   * @returns {Promise<Object>} { stagedCount, errorCount, errors }
    */
   static async batchUpload(csvFile) {
     const buffer = csvFile.data;
@@ -402,6 +403,7 @@ class ProjectV2 extends Model {
     const orgUid = homeOrg.org_uid;
 
     const errors = [];
+    let stagedCount = 0;
 
     await sequelizeV2.transaction(async (transaction) => {
       for (let i = 0; i < rawRows.length; i++) {
@@ -433,6 +435,15 @@ class ProjectV2 extends Model {
             mergedRecord = { ...row };
           }
 
+          // Required-field validation for INSERT rows
+          if (action === 'INSERT') {
+            const missing = validateRequiredFields(mergedRecord, ProjectV2);
+            if (missing.length > 0) {
+              errors.push({ row: rowNum, error: `Missing required field(s): ${missing.join(', ')}` });
+              continue;
+            }
+          }
+
           // FK existence check for cadTrustProgramId
           if (mergedRecord.cadTrustProgramId) {
             try {
@@ -467,13 +478,14 @@ class ProjectV2 extends Model {
             },
             { transaction },
           );
+          stagedCount++;
         } catch (err) {
           errors.push({ row: rowNum, error: err.message });
         }
       }
     });
 
-    return { errors };
+    return { stagedCount, errorCount: errors.length, errors };
   }
 
   /**
diff --git a/src/models/v2/unit-v2.model.js b/src/models/v2/unit-v2.model.js
@@ -20,6 +20,7 @@ import {
   normalizeCsvHeaders,
   toDbFieldNames,
   stripUnknownDbFields,
+  validateRequiredFields,
 } from '../../utils/v2-xls.js';
 import { assertRecordExistanceOrStaged } from '../../utils/v2-data-assertions.js';
 import { IssuanceV2 } from './issuance-v2.model.js';
@@ -434,7 +435,7 @@ class UnitV2 extends Model {
    *   6. Upsert into StagingV2
    *
    * @param {Object} csvFile - CSV file object with data buffer
-   * @returns {Promise<Object>} Result with errors array (may be empty)
+   * @returns {Promise<Object>} { stagedCount, errorCount, errors }
    */
   static async batchUpload(csvFile) {
     const buffer = csvFile.data;
@@ -463,6 +464,10 @@ class UnitV2 extends Model {
     const orgUid = homeOrg.org_uid;
 
     const errors = [];
+    let stagedCount = 0;
+
+    // unitSerialId is derivable from blocks — don't require it if blocks are present
+    const unitSkipFields = new Set(['unitSerialId']);
 
     await sequelizeV2.transaction(async (transaction) => {
       for (let i = 0; i < rawRows.length; i++) {
@@ -495,6 +500,15 @@ class UnitV2 extends Model {
             mergedRecord = { ...row };
           }
 
+          // Required-field validation for INSERT rows
+          if (action === 'INSERT') {
+            const missing = validateRequiredFields(mergedRecord, UnitV2, unitSkipFields);
+            if (missing.length > 0) {
+              errors.push({ row: rowNum, error: `Missing required field(s): ${missing.join(', ')}` });
+              continue;
+            }
+          }
+
           // FK existence check for cadTrustIssuanceId
           if (mergedRecord.cadTrustIssuanceId) {
             try {
@@ -529,13 +543,14 @@ class UnitV2 extends Model {
             },
             { transaction },
           );
+          stagedCount++;
         } catch (err) {
           errors.push({ row: rowNum, error: err.message });
         }
       }
     });
 
-    return { errors };
+    return { stagedCount, errorCount: errors.length, errors };
   }
 
 
diff --git a/src/utils/v2-xls.js b/src/utils/v2-xls.js
@@ -256,6 +256,42 @@ export function stripUnknownDbFields(dbRow, modelClass, logger = null) {
   return cleaned;
 }
 
+/**
+ * Validate that a camelCase row destined for INSERT contains all fields
+ * marked `allowNull: false` in the model definition.  Skips fields that
+ * are auto-managed (primary key, orgUid, timestamps) and any fields listed
+ * in the optional `skipFields` set (e.g. `unitSerialId` when it can be
+ * derived from other fields).
+ *
+ * @param {Object} row - camelCase row to validate
+ * @param {import('sequelize').Model} modelClass
+ * @param {Set<string>} [skipFields] - attribute names to skip
+ * @returns {string[]} array of missing field names (empty if valid)
+ */
+export function validateRequiredFields(row, modelClass, skipFields = new Set()) {
+  const autoManaged = new Set([
+    modelClass.primaryKeyAttribute,
+    'orgUid',
+    'createdAt',
+    'updatedAt',
+    // Models with underscored:true and custom timestamp mappings use
+    // snake_case attribute names in rawAttributes
+    'created_at',
+    'updated_at',
+  ]);
+
+  const missing = [];
+  for (const [attrName, meta] of Object.entries(modelClass.rawAttributes)) {
+    if (meta.allowNull === false && !autoManaged.has(attrName) && !skipFields.has(attrName)) {
+      const val = row[attrName];
+      if (val === undefined || val === null || val === '') {
+        missing.push(attrName);
+      }
+    }
+  }
+  return missing;
+}
+
 /**
  * Create StagingV2 records from parsed XLSX data.
  * Parent rows and child rows each get their own staging entry, matching the
diff --git a/tests/v2/integration/project-v2.spec.js b/tests/v2/integration/project-v2.spec.js
diff --git a/tests/v2/integration/unit-v2.spec.js b/tests/v2/integration/unit-v2.spec.js
