Observation import: add Azure blob rollback on transaction failure

[ClemRz]

Summary

The observation import pipeline uploads files to Azure Blob Storage inside a DB transaction, but if the transaction rolls back (e.g. a constraint violation after the upload), the blobs are orphaned — they remain in Azure with no corresponding DB record.

Current Behaviour

EntityBuilder.build() calls FileService.document.create(file, documentId, false, true, db) twice (raw data file + profile JSON). If a later step in the transaction fails, the DB rolls back but the Azure blobs persist.

Proposed Fix

Three small changes:

1. FileService.document.create must return the blob path

It already creates pathName internally. Change fetchResult to true (already supported) for the observation-import calls, or have create() always return { path } at minimum. The fetch() variant already returns the TFile record which includes path.

2. Add a deleteBlob helper on FileService

A ~5-line method that calls blockBlobClient.delete(). Doesn't exist yet but is trivial:

async deleteBlob(path) {
  const blobClient = containerClient.getBlockBlobClient(path);
  await blobClient.delete({ deleteSnapshots: 'include' });
}

3. Wrap the transaction in EntityBuilder.build() with cleanup on failure

Collect uploaded blob paths during the transaction. In a .catch() handler, call the new deleteBlob helper for each path and log orphan warnings if cleanup itself fails.

const uploadedBlobs = [];
try {
  await sails.getDatastore().transaction(async (db) => {
    // ... existing logic ...
    const file1 = await FileService.document.create(file, docId, true, true, db);
    uploadedBlobs.push(file1.path);
    // ...
  });
} catch (err) {
  // Attempt blob cleanup on transaction rollback
  for (const blobPath of uploadedBlobs) {
    try {
      await FileService.deleteBlob(blobPath);
    } catch (cleanupErr) {
      sails.log.warn(`Orphaned blob not cleaned up: ${blobPath}`, cleanupErr);
    }
  }
  throw err;
}

Complexity

Low-medium. The main change is flipping fetchResult to true for the two observation-import calls so that the blob path is captured, then adding the small deleteBlob helper and the catch block.

Priority

Medium — orphaned blobs waste storage but don't corrupt data. The import is atomic from the DB perspective; this makes it atomic from the storage perspective too.