Async Import Architecture

1. Overview

The Async Import architecture provides a framework for processing large spreadsheet imports asynchronously, with support for interactive column and cell mapping when automatic resolution fails. This enables imports that would otherwise timeout to complete successfully with user assistance.

2. Implementation Status (2026-04)

The framework was implemented in two layers that both ship in release 2.4:

  1. Generic interactive framework — ImportResource + state machine + mapping services. Designed to support UX-driven imports where the operator walks through column and cell mapping interactively. Fully wired end-to-end; used today by any future import that needs interactive resolution.

  2. Per-entity facade endpoints (primary surface) — thin controllers on each domain’s existing REST resource (ResultSetResourceEx, EventParticipantResourceEx, MembershipResourceEx) that accept a one-shot upload, orchestrate the generic framework internally, and return HTTP 202 with an ImportJobDTO. The caller polls a per-entity GET /import/{jobId} that deserialises the per-type response DTO (BulkResultImportResponseDTO, EventParticipantImportResultDTO, MembershipImportResultDTO). This is the contract you almost certainly want — domain-typed Swagger, tenant-scoped security, shape parity with the prior synchronous response.

Two processor modes feed the generic framework:

  • Whole-file (RESULT, EP) — the processor sets supportsWholeFile()=true and consumes the uploaded file in a single call via processWholeFile(ImportJob, InputStream, ImportContext). Used when cross-row logic (number-change detection, category grouping, upsert-by-seq for RESULT; summary aggregation for EP) cannot be expressed row-at-a-time. The job skips COLUMN_MAPPING and CELL_MAPPING (skipMappingPhases=true) and transitions straight from UPLOADED to PROCESSING.

  • Row-by-row (MEMBERSHIP) — the processor overrides processRow(ImportJob, SpreadsheetRow, rowNumber, ImportContext). The framework iterates the spreadsheet itself, persisting an ImportRowResult per row. The per-entity GET endpoint aggregates those rows into a MembershipImportResultDTO on demand.

See Result Import Design, Event Participant Import Design and Import Operations Runbook for the per-entity surfaces and operator workflows. The sections below describe the generic framework in full.

3. Problem Statement

3.1. Current Limitations

The synchronous import approach has several limitations:

Limitation Impact

HTTP timeout

Large files fail before processing completes

No progress visibility

Users don’t know import status

All-or-nothing mapping

Any unmapped column/cell fails the entire import

No resume capability

Failed imports must restart from scratch

Resource consumption

Long-running requests tie up server threads

3.2. Requirements

The async import system must:

  • Accept large files with immediate acknowledgment

  • Process rows asynchronously in the background

  • Pause for user input when mappings cannot be resolved

  • Allow resume after user provides missing mappings

  • Track progress and provide status updates

  • Clean up completed/abandoned imports automatically

4. Architecture Overview

async-import-architecture

5. State Machine

5.1. States

import-states

The UPLOADED → PROCESSING fast-path is taken when the processor reports supportsWholeFile()=true (RESULT, EP) or when the caller uses ImportJobService.createWholeFileImportJob(…​). skipMappingPhases=true is set on the job so the transition is auditable. The UPLOADED → COLUMN_MAPPING path remains for interactive imports that need manual resolution (MEMBERSHIP one-shot uploads also traverse this path but auto-confirm matched columns and auto-ignore unmatched ones via createAndAutoStartImportJob(…​) — the client still gets one-shot semantics but the state machine goes through every step).

5.2. State Descriptions

State Description Next States

UPLOADED

File received and stored as BLOB. Awaiting column detection.

COLUMN_MAPPING

COLUMN_MAPPING

Headers analyzed. Required fields not all matched. Waiting for user to provide mappings.

CELL_MAPPING, CANCELLED

CELL_MAPPING

Columns mapped. Foreign key values cannot be resolved. Waiting for user to select valid values.

PROCESSING, CANCELLED

PROCESSING

Actively processing rows. Progress tracked.

CELL_MAPPING (new value), COMPLETED, FAILED, CANCELLED

COMPLETED

All rows processed. Results available.

(terminal)

FAILED

Fatal error occurred during processing.

(terminal)

CANCELLED

User cancelled the import.

(terminal)

6. BLOB Storage

6.1. Attachment Entity

Import files are stored using the existing Attachment entity:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Attachment {
    @Id @GeneratedValue
    private Long id;

    @Column(unique = true, nullable = false)
    private String uuid;

    @Lob @Basic(fetch = FetchType.EAGER)
    private byte[] data;

    @ManyToOne(optional = false)
    private Organisation organisation;

    @NotNull
    private String mediaType;

    private Instant expiryDate;

    @NotNull
    private String name;
}

6.2. File Storage Flow

  1. Client uploads file via multipart form

  2. Service validates file type and size

  3. File bytes stored in Attachment.data

  4. Attachment.uuid returned to client as reference

  5. ImportJob links to Attachment for processing

6.3. Supported Media Types

private static final Set<String> ALLOWED_TYPES = Set.of(
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",  // xlsx
    "application/vnd.ms-excel",                                           // xls
    "text/csv",
    "text/plain"  // csv with wrong mime type
);

7. Database Schema

7.1. Entity Relationship Diagram

import-erd

7.2. Enumerations

public enum ImportType {
    EVENT_PARTICIPANT,
    MEMBERSHIP,
    RESULT
}

public enum ImportJobStatus {
    UPLOADED,
    COLUMN_MAPPING,
    CELL_MAPPING,
    PROCESSING,
    COMPLETED,
    FAILED,
    CANCELLED
}

public enum MappingStatus {
    AUTO_MATCHED,     // System matched with high confidence
    MANUAL_MATCHED,   // User confirmed or corrected
    UNMATCHED,        // Cannot match, needs user input
    IGNORED           // User chose to skip
}

public enum ImportOutcome {
    CREATED,
    UPDATED,
    SKIPPED,
    ERROR
}

8. API Design

8.1. Per-Entity Facade Endpoints (primary surface)

These are the endpoints operators and integrations should use by default. Each accepts a one-shot upload, orchestrates the generic framework internally, and returns HTTP 202 + ImportJobDTO immediately; the caller polls the paired GET /import/{jobId} for the domain-typed response DTO once the job reaches COMPLETED or FAILED.

Method Endpoint Description

PUT

/api/result-sets/import-bulk

Upload a combined CSV for a bulk result import. Query params: eventId (required), participantIdMode (epid/regid/pid), applyNumberChanges, pointsCalculator. Returns 202 + ImportJobDTO. Whole-file delegation to ResultImportXLS.processBulkCsv.

GET

/api/result-sets/import/{jobId}

Returns BulkResultImportResponseDTO (summary, categories, number changes, unmatched categories, reconciliation counters) once COMPLETED/FAILED. 409 while still processing; 404 if missing or wrong import type.

PUT

/api/event-participants/import

Upload an EP roster. Query params: eventId (required), orgId, sheetIndex, createCustom1/2/3. Returns 202 + ImportJobDTO. Whole-file delegation to EventParticipantImportXLS.process.

GET

/api/event-participants/import/{jobId}

Returns EventParticipantImportResultDTO (summary counts + focused issues list) once COMPLETED/FAILED.

PUT

/api/memberships/import

Upload a membership roster. Query params: periodId (required), orgId, sheetIndex. Returns 202 + ImportJobDTO. Row-by-row via MembershipRowProcessor → MembershipImportService.addMember, with auto-confirm of matched columns and auto-ignore of unresolved columns via createAndAutoStartImportJob.

GET

/api/memberships/import/{jobId}

Returns MembershipImportResultDTO (totalRows, created, skipped, errors, per-row issues) once COMPLETED/FAILED. Built on demand from persisted ImportRowResult entries (no bulk importer exists for Membership).

8.2. Generic Framework Endpoints (interactive / cross-cutting)

These endpoints expose the full state machine for interactive imports (where the operator needs to walk through column and cell mapping) and for cross-cutting operations that don’t belong to any one domain.

Method Endpoint Description

POST

/api/imports

Upload file and create import job (interactive flow). Returns 201 + ImportJobDTO with analyzed column mappings.

GET

/api/imports

List import jobs (paginated, filterable by status and organisation).

GET

/api/imports/{uuid}

Get import job status (generic — no domain DTO). Useful during polling when the caller wants status, totalRows, processedRows, counters without the full result payload.

DELETE

/api/imports/{uuid}

Cancel import job.

GET

/api/imports/{uuid}/column-mappings

Get column mappings (interactive).

PUT

/api/imports/{uuid}/column-mappings

Update column mappings (interactive).

POST

/api/imports/{uuid}/column-mappings/confirm

Confirm all auto-matched column mappings and proceed to cell mapping (interactive).

GET

/api/imports/{uuid}/cell-mappings

Get cell mappings (interactive).

GET

/api/imports/{uuid}/cell-mappings/candidates

Get candidate entities for FK resolution (interactive dropdown population).

PUT

/api/imports/{uuid}/cell-mappings

Update cell mappings (interactive).

POST

/api/imports/{uuid}/cell-mappings/confirm

Confirm all cell mappings and start processing (interactive).

POST

/api/imports/{uuid}/start

Skip cell mapping and start processing (from COLUMN_MAPPING). Equivalent to skipCellMappingsAndStartProcessing.

GET

/api/imports/{uuid}/results

Get per-row results (paginated, filterable by outcome). Generic shape; per-entity endpoints give the domain DTO.

GET

/api/imports/{uuid}/results/summary

Get results summary by outcome (generic counts).

8.3. Endpoint Details

8.3.1. Create Import Job

POST /api/imports
Content-Type: multipart/form-data

Parameters:
- file: MultipartFile (required) - The spreadsheet file
- importType: String (required) - EVENT_PARTICIPANT, MEMBERSHIP, RESULT
- contextId: Long (optional) - Event ID, MembershipPeriod ID, or Race ID
- organisationId: Long (optional) - Organisation ID (defaults to current user's org)

Response: 201 Created
Location: /api/imports/{uuid}

{
    "identifier": "abc-123-def-456",
    "importType": "EVENT_PARTICIPANT",
    "status": "COLUMN_MAPPING",
    "originalFilename": "registrations.xlsx",
    "totalRows": 150,
    "processedRows": 0,
    "successCount": 0,
    "errorCount": 0,
    "createdAt": "2026-01-03T10:00:00Z",
    "columnMappings": [
        {"id": 1, "columnIndex": 0, "sourceHeader": "Name", "targetField": "FIRST_NAME", "status": "AUTO_MATCHED", "confidenceScore": 1.0},
        {"id": 2, "columnIndex": 1, "sourceHeader": "Unknown Col", "targetField": null, "status": "UNRESOLVED", "confidenceScore": 0.0}
    ]
}

8.3.2. Get Import Job Status

GET /api/imports/{uuid}?includeMappings=true

Response: 200 OK
{
    "identifier": "abc-123-def-456",
    "importType": "EVENT_PARTICIPANT",
    "status": "PROCESSING",
    "totalRows": 150,
    "processedRows": 75,
    "successCount": 72,
    "errorCount": 3,
    "progressPercent": 50,
    "createdAt": "2026-01-03T10:00:00Z"
}

8.3.3. List Import Jobs

GET /api/imports?organisationId=1&status=PROCESSING&page=0&size=20

Response: 200 OK
X-Total-Count: 5

[
    {"identifier": "abc-123", "status": "PROCESSING", "progressPercent": 50, ...},
    {"identifier": "def-456", "status": "COMPLETED", "progressPercent": 100, ...}
]

8.3.4. Update Column Mappings

PUT /api/imports/{uuid}/column-mappings
Content-Type: application/json

[
    {"id": 1, "confirm": true},
    {"id": 2, "targetField": "EVENT_CATEGORY_NAME"},
    {"id": 3, "ignore": true}
]

Response: 202 Accepted (all required mapped)
Response: 406 Not Acceptable (required fields still unresolved)

8.3.5. Confirm Column Mappings

POST /api/imports/{uuid}/column-mappings/confirm

Response: 202 Accepted
{
    "identifier": "abc-123-def-456",
    "status": "CELL_MAPPING",
    "cellMappings": [
        {"id": 1, "targetField": "EVENT_CATEGORY_NAME", "sourceValue": "Junior", "status": "AUTO_MATCHED", "targetEntityId": 42},
        {"id": 2, "targetField": "EVENT_CATEGORY_NAME", "sourceValue": "Unknown Cat", "status": "UNRESOLVED", "targetEntityId": null}
    ]
}

8.3.6. Get Cell Mapping Candidates

GET /api/imports/{uuid}/cell-mappings/candidates?targetField=EVENT_CATEGORY_NAME

Response: 200 OK
[
    {"id": 42, "displayName": "Junior (U18)"},
    {"id": 43, "displayName": "Senior (18+)"},
    {"id": 44, "displayName": "Masters (40+)"}
]

8.3.7. Update Cell Mappings

PUT /api/imports/{uuid}/cell-mappings
Content-Type: application/json

[
    {"id": 1, "confirm": true},
    {"id": 2, "targetEntityId": 43},
    {"id": 3, "ignore": true}
]

Response: 202 Accepted (all resolved)
Response: 406 Not Acceptable (unresolved mappings remain)

8.3.8. Start Processing

POST /api/imports/{uuid}/start

Description: Confirms all auto-matched mappings and starts processing.
             Can be called from COLUMN_MAPPING (skips cell mapping) or CELL_MAPPING status.

Response: 202 Accepted
{
    "identifier": "abc-123-def-456",
    "status": "PROCESSING",
    "totalRows": 150,
    "processedRows": 0
}

8.3.9. Get Row Results

GET /api/imports/{uuid}/results?outcome=ERROR&page=0&size=50

Response: 200 OK
X-Total-Count: 5

[
    {"id": 1, "rowNumber": 15, "outcome": "ERROR", "message": "Duplicate email: [email protected]"},
    {"id": 2, "rowNumber": 42, "outcome": "ERROR", "message": "Invalid date format"}
]

8.3.10. Get Results Summary

GET /api/imports/{uuid}/results/summary

Response: 200 OK
{
    "created": 120,
    "updated": 25,
    "skipped": 2,
    "error": 3,
    "validationError": 0
}

8.3.11. Cancel Import

DELETE /api/imports/{uuid}

Response: 204 No Content (cancelled successfully)
Response: 404 Not Found (job doesn't exist)
Response: 409 Conflict (already completed/failed/cancelled)

9. Column Mapping

9.1. Auto-Matching Process

  1. Extract headers from first row

  2. Normalize each header (remove spaces, special chars, uppercase)

  3. Look up in header dictionary

  4. Calculate confidence score for fuzzy matches

  5. Mark required fields that couldn’t match

9.2. Confidence Scoring

Match Type Example Confidence

Exact match

"FirstName" → FIRST_NAME

1.0

Alias match

"FN" → FIRST_NAME

1.0

Normalized match

"First Name" → FIRST_NAME

1.0

Fuzzy match (>80%)

"First_Nm" → FIRST_NAME

0.8-0.99

No match

"Unknown" → null

0.0

9.3. User Resolution

When unmatched columns exist:

  1. Return list of unmatched columns with isRequired flag

  2. Return list of available (unmapped) target fields

  3. User selects target for each or marks as IGNORED

  4. System validates all required fields are mapped

  5. Return 202 if valid, 406 if still missing

10. Cell Mapping

10.1. Foreign Key Resolution

For fields that reference other entities, values must be resolved to IDs:

Field Resolution

ID_COUNTRY

Lookup Country by code or name

EVENT_CATEGORY_NAME

Lookup EventCategory by name within event

CUSTOM_LIST_*

Lookup CustomListValue by value within list

MEMBERSHIP_TYPE_ID

Lookup MembershipType by ID or name

10.2. Resolution Strategy

public interface CellValueResolver {
    /**
     * Attempt to resolve a source value to an entity ID.
     */
    CellResolutionResult resolve(String sourceValue, ImportContext context);

    /**
     * Get all valid target values for UI dropdown.
     */
    List<TargetOptionDTO> getAvailableTargets(ImportContext context);
}

10.3. User Resolution Options

When a value cannot be resolved:

  1. Map to existing: Select from dropdown of valid entities

  2. Create new: Create new entity (if permitted for this type)

  3. Ignore: Skip rows with this value

  4. Use default: Apply a default for all unmatched

11. Whole-File Bridge

Some bulk importers cannot be expressed as "row in → row out". RESULT, for example, needs two passes over the CSV (category grouping + per-category upsert), cross-row number-change detection, and a reconciliation summary that counts blank/header/malformed rows. Modelling these as a loop over processRow(…​) would throw away the synchronous importer’s battle-tested logic.

The bridge is a default-method extension on ImportRowProcessor:

public interface ImportRowProcessor {
    ImportType getImportType();

    default boolean supportsWholeFile() { return false; }

    default Object processWholeFile(ImportJob job, InputStream stream, ImportContext ctx) {
        throw new UnsupportedOperationException();
    }

    default ImportRowResult processRow(ImportJob job, SpreadsheetRow row, int rowNumber, ImportContext ctx) {
        throw new UnsupportedOperationException();
    }

    default void beforeProcessing(ImportJob job, ImportContext ctx) {}
    default void afterProcessing(ImportJob job, ImportContext ctx) {}
}

11.1. Dispatch

ImportProcessingService.processRows(…​) branches on processor.supportsWholeFile():

  • true → processWholeFileInternal re-opens the uploaded attachment as an InputStream, hands the whole stream to processor.processWholeFile(…​), JSON-serialises the returned DTO to ImportJob.resultPayloadJson, and returns. Counters the processor wrote directly onto the managed ImportJob (e.g. fileLines, successCount, blankLines) are persisted.

  • false → processRowByRowInternal iterates the spreadsheet via SpreadsheetReader and calls rowProcessingService.processRowInTransaction(jobId, processor, row, rowNumber, ctx) per row. Each row commits in its own REQUIRES_NEW transaction so a rollback on one row doesn’t affect subsequent rows.

11.2. Contract for whole-file processors

The processor is expected to:

  1. Parse ImportJob.getConfigJson() to recover per-type parameters (e.g. participantIdMode, pointsCalculator, applyNumberChanges for RESULT).

  2. Do its work — typically by delegating to the synchronous bulk importer (ResultImportXLS.processBulkCsv, EventParticipantImportXLS.process).

  3. Write observability counters onto the managed ImportJob passed to beforeProcessing: fileLines, totalRows, processedRows, successCount, errorCount, blankLines, headerRows, issueCount, numberChangeCount. These feed the generic status endpoint and dashboards.

  4. Return the response DTO — shape should match the synchronous importer’s so the per-entity GET endpoint can deserialise and return it verbatim.

11.3. Configuration JSON

ImportJob.configJson is a CLOB holding a per-type options object. Shape is intentionally open so new options can be added without schema migration. Current keys:

Type Key Meaning

RESULT

participantIdMode

epid (default) | regid | pid

RESULT

pointsCalculator

Short code (e.g. wpca-road-league, sa-school) or FQCN

RESULT

applyNumberChanges

true to actually apply SIMPLE number changes; false (default) to detect and report only

EVENT_PARTICIPANT

sheetIndex

XLSX sheet index (default 0, ignored for CSV)

EVENT_PARTICIPANT

createCustom1 / createCustom2 / createCustom3

Auto-create missing custom-list values (default true)

MEMBERSHIP

sheetIndex

XLSX sheet index

12. Row-Counter Invariant

After processing completes, ImportProcessingService.completeJob(…​) asserts:

fileLines == successCount + blankLines + headerRows + errorCount

Violation is logged at WARN (with the expected and actual values) but does not fail the job — the invariant is a defensive check, not a correctness constraint; individual failures would already have surfaced during processing.

The whole-file processors populate every term from the sync DTO’s reconciliation summary; row-by-row imports (MEMBERSHIP) currently leave fileLines null and skip the assertion.

13. Observability

  • import.processWholeFile (Micrometer Timer, tagged importType) — latency of the async whole-file path per import type. Hooks into the existing Spring Boot Actuator / OTLP stack.

  • import.status.transitions (Micrometer Counter, tagged importType, from, to) — incremented every time a job reaches a terminal status via completeJob or markJobFailed. Enables alerting on unusual FAILED rates per type.

  • Structured logs on terminal transitions include the job identifier, import type, from/to status, success/error counts, and fileLines — tailable from ELK/Loki without needing the full metrics pipeline.

14. Async Processing

14.1. Spring Async Configuration

@Configuration
@EnableAsync
public class AsyncConfig {

    @Bean(name = "importTaskExecutor")
    public Executor importTaskExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(2);
        executor.setMaxPoolSize(5);
        executor.setQueueCapacity(25);
        executor.setThreadNamePrefix("import-");
        executor.initialize();
        return executor;
    }
}

14.2. Processing Service

@Service
public class ImportProcessingService {

    @Async("importTaskExecutor")
    public CompletableFuture<Void> processImport(UUID jobId) {
        ImportJob job = findByUuid(jobId);

        try {
            job.setStatus(PROCESSING);
            save(job);

            SpreadsheetReader reader = createReader(job);
            int rowIndex = 1;  // Skip header

            while (rowIndex < reader.getRowCount()) {
                // Check cancellation
                if (isCancelled(jobId)) return complete();

                try {
                    processRow(reader.getRow(rowIndex), job);
                    job.incrementSuccess();
                } catch (UnmappedCellException e) {
                    // Pause for user input
                    savePendingCellMapping(job, e);
                    job.setStatus(CELL_MAPPING);
                    save(job);
                    return complete();
                } catch (Exception e) {
                    saveError(job, rowIndex, e);
                    job.incrementError();
                }

                job.setProcessedRows(rowIndex);
                if (rowIndex % 100 == 0) save(job);
                rowIndex++;
            }

            job.setStatus(COMPLETED);
            job.setCompletedAt(Instant.now());
            save(job);

        } catch (Exception e) {
            job.setStatus(FAILED);
            save(job);
        }

        return complete();
    }
}

15. Purging Strategy

15.1. Retention Rules

Status Retention Rationale

COMPLETED

7 days

Allow review and re-download

FAILED

7 days

Allow debugging

CANCELLED

7 days

Same as completed

UPLOADED, COLUMN_MAPPING, CELL_MAPPING

90 days

User may return to complete

PROCESSING

No auto-purge

Requires manual intervention

15.2. Scheduled Cleanup

@Service
public class ImportCleanupService {

    @Scheduled(cron = "0 0 2 * * ?")  // Daily at 2 AM
    @Transactional
    public void cleanupExpiredImports() {
        Instant now = Instant.now();

        // Completed/Failed/Cancelled: 7 days
        deleteByStatusesOlderThan(
            Set.of(COMPLETED, FAILED, CANCELLED),
            now.minus(7, ChronoUnit.DAYS)
        );

        // Abandoned: 90 days
        deleteByStatusesOlderThan(
            Set.of(UPLOADED, COLUMN_MAPPING, CELL_MAPPING),
            now.minus(90, ChronoUnit.DAYS)
        );
    }

    private void deleteByStatusesOlderThan(Set<Status> statuses, Instant cutoff) {
        List<ImportJob> jobs = repository.findByStatusInAndCreatedAtBefore(statuses, cutoff);
        for (ImportJob job : jobs) {
            attachmentRepository.delete(job.getSourceFile());
            repository.delete(job);
        }
    }
}

16. UI Workflow

16.1. Upload Flow

upload-flow

16.2. Polling Flow

polling-flow

17. Related Documentation