CSV Import/Export Operations

Description

The CSV Import/Export system provides comprehensive bulk data management capabilities for temporary wishlists, enabling users to efficiently upload large datasets, track import progress, and download detailed error reports. This system supports sophisticated error handling, complete audit trails, and real-time progress tracking to ensure data integrity and provide comprehensive user feedback throughout the import lifecycle.

Key features include:

• Advanced Bulk CSV Import: Upload product data with comprehensive validation, error collection, and progress tracking
• Complete Import History Tracking: Full audit trail of all import operations with detailed status monitoring and metadata preservation
• Sophisticated Error Management: Detailed error collection, categorization, and downloadable error reports with specific validation feedback
• Comprehensive File Validation: Multi-layer CSV format validation with size limits, security checks, and encoding support
• Asynchronous Processing: Background processing for large files with real-time progress tracking and status updates
• Transaction Safety: All import operations wrapped in database transactions with comprehensive rollback strategies
• User Experience Optimization: Real-time feedback, progress indicators, and detailed error reporting for optimal user experience

Activity Diagram

---
config:
  theme: base
  layout: dagre
  flowchart:
    curve: linear
    htmlLabels: true
  themeVariables:
    edgeLabelBackground: "transparent"
---
flowchart TD
    Start([User Initiates CSV Operation])
    
    Start --> Step1[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #4CAF50 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>1</span>
            <p style='margin-top: 8px'>Upload & Validate CSV File</p>
        </div>
    ]
    
    Step1 --> Step2[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #2196F3 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>2</span>
            <p style='margin-top: 8px'>Process CSV Data with Validation</p>
        </div>
    ]
    
    Step2 --> Step3[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #FF9800 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>3</span>
            <p style='margin-top: 8px'>Create Import History Record</p>
        </div>
    ]
    
    Step3 --> Decision{Processing Result}
    Decision -->|Success| Step4[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #4CAF50 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>4</span>
            <p style='margin-top: 8px'>Create Temporary Wishlist</p>
        </div>
    ]
    Decision -->|Errors| Step5[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #F44336 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>5</span>
            <p style='margin-top: 8px'>Store Error Details</p>
        </div>
    ]
    
    Step4 --> Step6[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #9C27B0 !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>6</span>
            <p style='margin-top: 8px'>Update Import Status</p>
        </div>
    ]
    
    Step5 --> Step7[
        <div style='text-align: center'>
            <span style='display: inline-block; background-color: #607D8B !important; color:white; width: 28px; height: 28px; line-height: 28px; border-radius: 50%; font-weight: bold'>7</span>
            <p style='margin-top: 8px'>Generate Error CSV</p>
        </div>
    ]
    
    Step6 --> End([Import Complete])
    Step7 --> End
    
    %% Styling
    style Start fill:#e8f5e8,stroke:#4caf50,stroke-width:2px
    style End fill:#ffe8e8,stroke:#f44336,stroke-width:2px
    style Decision fill:#fff3e0,stroke:#ff9800,stroke-width:2px
    style Step1 fill:transparent,stroke:transparent,stroke-width:1px
    style Step2 fill:transparent,stroke:transparent,stroke-width:1px
    style Step3 fill:transparent,stroke:transparent,stroke-width:1px
    style Step4 fill:transparent,stroke:transparent,stroke-width:1px
    style Step5 fill:transparent,stroke:transparent,stroke-width:1px
    style Step6 fill:transparent,stroke:transparent,stroke-width:1px
    style Step7 fill:transparent,stroke:transparent,stroke-width:1px

Detail Dataflow Dependency

Step 1: File Upload & Multi-Layer Validation

• Description: Comprehensive validation of uploaded CSV files with security checks, format validation, and size limitations
• Action: Execute multi-tier file validation including MIME type checking, file extension validation, size limit enforcement (1MB), encoding detection (UTF-8), and basic CSV structure validation
• Input: Uploaded CSV file, user session, group context
• Output: Validated file object, file metadata (name, size, encoding), validation confirmation
• Dependencies: File system access, MIME type detection, security validation services
• External Services: None (internal validation only)

Step 2: CSV Data Processing with Row-Level Validation

• Description: Parse CSV content and perform comprehensive row-by-row validation with detailed error collection and progress tracking
• Action:
  • Parse CSV file with proper encoding handling
  • Validate each row against business rules (product input format, mall compatibility, required fields)
  • Collect detailed validation errors with row numbers and field-specific messages
  • Track processing progress for large files
• Input: Validated CSV file, validation rules, mall configuration
• Output: Parsed data rows, validation results, detailed error collection, processing statistics
• Dependencies: CSV parsing libraries, validation rule engine, mall configuration service
• External Services: None (internal data processing)

Step 3: Import History Creation & Tracking

• Description: Create comprehensive import history record for complete audit trail and progress monitoring
• Action: Generate import history record with file metadata, user context, initial status (Pending), timestamp information, and unique tracking identifier
• Input: File metadata, user information, group context, processing initiation data
• Output: Import history record with unique ID, tracking information, audit trail entry
• Dependencies: Import history repository, user session management, audit logging system
• External Services: None (internal record creation)

Step 4: Temporary Wishlist Creation with Transaction Safety

• Description: Create temporary wishlist and associated product records using validated CSV data with full transaction safety
• Action:
  • Begin database transaction for atomic operations
  • Create temporary wishlist record with CSV import status
  • Bulk insert validated product records with proper relationships
  • Generate automatic slug and assign appropriate status
  • Commit transaction or rollback on any error
• Input: Validated CSV data, import history context, user/group information
• Output: Created temporary wishlist with all products, updated import history with success status
• Dependencies: TempWishlistToGroupService, database transaction management, slug generation service
• External Services: None (internal data creation)

Step 5: Error Collection & Detailed Reporting

• Description: Comprehensive collection and storage of validation errors with detailed context for user feedback and debugging
• Action:
  • Categorize validation errors by type (format, business rule, data integrity)
  • Store detailed error information including row numbers, field names, original values, and specific error messages
  • Create structured error data for CSV generation
  • Update import history with error status and summary
• Input: Validation error collection, row data context, import history record
• Output: Stored error records, updated import history with error status, error summary statistics
• Dependencies: Import error repository, error categorization service, import history management
• External Services: None (internal error management)

Step 6: Error CSV Generation & Download Preparation

• Description: Generate downloadable CSV file containing original data with detailed error descriptions for user review and correction
• Action:
  • Retrieve stored error records with original row data
  • Format error information for user-friendly CSV output
  • Generate CSV content with error descriptions and correction guidance
  • Prepare streamed download response for efficient file delivery
• Input: Import history ID, stored error records, original CSV data
• Output: Generated error CSV file, download stream, user-friendly error report
• Dependencies: CSV generation service, error formatting utilities, file streaming capabilities
• External Services: None (internal file generation)

Database Related Tables & Fields

Database: gb_console

erDiagram
    users ||--o{ import_histories : "creates"
    import_histories ||--o{ import_errors : "has"
    import_histories ||--o{ temp_wishlist_to_groups : "creates"

    users {
        bigint id PK
        string name
    }

    import_histories {
        bigint id PK
        string file_name "The name of the file"
        bigint created_by FK
        string temp_wishlist_id "The ID of the temp wishlist"
        string temp_wishlist_name "The name of the temp wishlist"
        string error "The error message"
        tinyint status "0: Pending, 1: Success, 2: Error"
        timestamp created_at
        timestamp updated_at
    }

    import_errors {
        bigint id PK
        bigint import_history_id FK
        string header
        string value
        json error_messages
        timestamp created_at
        timestamp updated_at
    }

    temp_wishlist_to_groups {
        bigint id PK
        bigint user_id FK
        bigint group_id FK
        string name "Name of the wishlist"
        string slug "Slug of the wishlist"
        tinyint status "1: Temporary Save, 2: CSV Import Draft"
        timestamp created_at
        timestamp updated_at
    }

Case Documentation

Case 1: CSV File Import with Comprehensive Validation

API: Import CSV File

Steps

Step 1: File Upload & Multi-Layer Validation

• Description: Comprehensive validation of uploaded CSV files with security and format checks
• Validation: MIME type (text/csv, text/plain), file extension (.csv, .txt), size limit (1MB), encoding (UTF-8)
• Security: File type validation to prevent malicious uploads, content scanning for security threats

Step 2: CSV Data Processing with Row-Level Validation

• Description: Parse CSV content and perform detailed row-by-row validation with error collection
• Processing: Header validation, row-by-row data validation, business rule enforcement, error categorization
• Rules: Product input validation (format, length), mall compatibility checking, required field validation

Step 3: Import History Creation & Result Handling

• Description: Create import history record and handle processing results (success or error)
• Success Path: Create temporary wishlist with all valid products, update history status to Success
• Error Path: Store detailed error information, update history status to Error, prepare error CSV

Sequence Diagram

sequenceDiagram
    participant Client
    participant TempWishlistController
    participant ImportService
    participant ImportHistoryRepository
    participant ImportErrorRepository
    participant TempWishlistService
    participant Database
    participant FileSystem

    rect rgb(255, 255, 200)
    Note over Client,FileSystem: Authentication & File Validation
    Client->>TempWishlistController: POST /api/v1/temp-wishlist-to-group/import
    TempWishlistController->>TempWishlistController: Check user authentication
    TempWishlistController->>TempWishlistController: Validate group membership (isMemberOfGroup)
    TempWishlistController->>TempWishlistController: Validate CSV file (MIME, extension, size, encoding)
    end

    rect rgb(200, 230, 255)
    Note over TempWishlistController,ImportService: Import Processing with History Tracking
    TempWishlistController->>ImportService: handle(request)
    ImportService->>ImportHistoryRepository: Create import history record
    ImportHistoryRepository->>Database: INSERT import_histories (status=Pending, file metadata)
    Database-->>ImportHistoryRepository: Created import history with ID
    ImportService->>FileSystem: Read and parse CSV file with encoding detection
    ImportService->>ImportService: Validate CSV structure and headers
    ImportService->>ImportService: Process rows with detailed validation
    end

    alt All Rows Valid
        rect rgb(200, 255, 200)
        Note over ImportService,Database: Happy Case - Successful Import
        ImportService->>ImportService: All validation checks passed
        ImportService->>TempWishlistService: Create temporary wishlist from CSV data
        TempWishlistService->>Database: BEGIN TRANSACTION
        TempWishlistService->>Database: INSERT temp_wishlist_to_groups (status=CSV Import Draft)
        TempWishlistService->>Database: INSERT temp_wishlist_products (bulk insert)
        TempWishlistService->>Database: COMMIT TRANSACTION
        ImportService->>ImportHistoryRepository: Update status to Success with wishlist details
        ImportHistoryRepository->>Database: UPDATE import_histories (status=Success, temp_wishlist_id)
        ImportService-->>TempWishlistController: Success result with wishlist data
        TempWishlistController-->>Client: 200 OK with success message and wishlist info
        end
    else Validation Errors Found
        rect rgb(255, 200, 200)
        Note over ImportService,Database: Error Case - Validation Failures
        ImportService->>ImportService: Collect detailed validation errors by row and field
        ImportService->>ImportErrorRepository: Store comprehensive error details
        ImportErrorRepository->>Database: INSERT import_errors (multiple rows with detailed context)
        Database-->>ImportErrorRepository: Stored error records
        ImportService->>ImportHistoryRepository: Update status to Error with error summary
        ImportHistoryRepository->>Database: UPDATE import_histories (status=Error, error summary)
        ImportService-->>TempWishlistController: Error result with detailed error information
        TempWishlistController-->>Client: 400 Bad Request with error details and download link
        end
    else File Processing Error
        rect rgb(255, 200, 200)
        Note over ImportService,Database: Error Case - Processing Failure
        ImportService->>ImportHistoryRepository: Update with general processing error
        ImportHistoryRepository->>Database: UPDATE import_histories (error message, status=Error)
        ImportService-->>TempWishlistController: Processing error with details
        TempWishlistController-->>Client: 400 Bad Request with processing error message
        end
    end

Error Handling

Case 2: Import History Retrieval with Pagination

API: Get Import Histories

Steps

Step 1: Authentication & Parameter Processing

• Description: Verify user authentication and process query parameters for filtering and pagination
• Parameters: page, per_page, optional filtering by status or date range
• Authorization: Ensure user can access their own import history records

Step 2: History Retrieval with User Filtering

• Description: Fetch user's import history with pagination and proper ordering
• Filters: User ownership (created_by = user_id), chronological order (newest first)
• Response: Paginated list with import status, file metadata, and error summaries

Sequence Diagram

sequenceDiagram
    participant Client
    participant TempWishlistController
    participant ImportHistoryRepository
    participant Database

    rect rgb(255, 255, 200)
    Note over Client,Database: Authentication & Query Parameters
    Client->>TempWishlistController: GET /api/v1/temp-wishlist-to-group/import-history/list?page=1&per_page=10
    TempWishlistController->>TempWishlistController: Check user authentication
    TempWishlistController->>TempWishlistController: Parse pagination and filter parameters
    end

    rect rgb(200, 255, 200)
    Note over TempWishlistController,Database: Happy Case - Retrieve History
    TempWishlistController->>ImportHistoryRepository: serverPaginationFilteringFor(request->all())
    ImportHistoryRepository->>Database: SELECT import_histories WHERE created_by = user_id ORDER BY created_at DESC
    Database-->>ImportHistoryRepository: Paginated import history records
    ImportHistoryRepository-->>TempWishlistController: LengthAwarePaginator with histories
    TempWishlistController->>TempWishlistController: Transform to ImportHistoryResource::collection
    TempWishlistController-->>Client: 200 OK with paginated import histories
    end

    rect rgb(255, 200, 200)
    Note over Client,Database: Error Handling
    alt Database Error
        Database-->>ImportHistoryRepository: Query error
        ImportHistoryRepository-->>TempWishlistController: Exception
        TempWishlistController-->>Client: 400 Bad Request
    end
    end

Case 3: Error CSV Download with Detailed Error Information

API: Download Error CSV

Steps

Step 1: Import History Validation & Authorization

• Description: Verify import history exists, belongs to user, and has associated errors
• Security: Ensure user owns the import history record and has permission to download errors
• Validation: Check history ID exists, status is Error, and error records are available

Step 2: Error Data Retrieval & CSV Generation

• Description: Fetch detailed error information and generate user-friendly CSV file
• Data: Original row data with specific error messages, field-level validation failures, correction guidance
• Format: CSV with original headers plus error description columns for easy review and correction

Step 3: Streamed CSV Download

• Description: Generate and stream downloadable CSV file with comprehensive error information
• Content: Original data with detailed error descriptions, validation failure explanations, correction suggestions
• Response: Streamed CSV file download with appropriate headers for browser handling

Sequence Diagram

sequenceDiagram
    participant Client
    participant TempWishlistController
    participant ImportHistoryRepository
    participant ExportService
    participant ImportErrorRepository
    participant Database

    rect rgb(255, 255, 200)
    Note over Client,TempWishlistController: Authentication & Authorization
    Client->>TempWishlistController: GET /api/v1/temp-wishlist-to-group/import-history/{id}/download-errors
    TempWishlistController->>TempWishlistController: Check user authentication
    TempWishlistController->>TempWishlistController: Validate import history ID format
    end

    rect rgb(200, 230, 255)
    Note over TempWishlistController,Database: Validation & Permission Check
    TempWishlistController->>ImportHistoryRepository: findByIdAndCreatedBy(historyId, userId)
    ImportHistoryRepository->>Database: SELECT import_histories WHERE id = ? AND created_by = ?
    Database-->>ImportHistoryRepository: Import history record or null
    ImportHistoryRepository-->>TempWishlistController: History data with ownership validation
    TempWishlistController->>TempWishlistController: Check if history exists and has errors
    TempWishlistController->>TempWishlistController: Validate error status and error count
    end

    rect rgb(200, 255, 200)
    Note over TempWishlistController,Database: Happy Case - Generate & Download Error CSV
    TempWishlistController->>ExportService: generateErrorCSV(history)
    ExportService->>ImportErrorRepository: getAllByHistoryId(historyId)
    ImportErrorRepository->>Database: SELECT import_errors WHERE import_history_id = ? ORDER BY id
    Database-->>ImportErrorRepository: Detailed error records with row context
    ImportErrorRepository-->>ExportService: Error data with original values and messages
    ExportService->>ExportService: Generate CSV content with headers and error descriptions
    ExportService->>ExportService: Create StreamedResponse with proper headers
    ExportService-->>TempWishlistController: CSV download stream
    TempWishlistController-->>Client: CSV file download (Content-Type: text/csv)
    end

    rect rgb(255, 200, 200)
    Note over Client,Database: Error Handling
    alt History Not Found or Access Denied
        ImportHistoryRepository-->>TempWishlistController: null
        TempWishlistController-->>Client: 404 Not Found
    else No Errors Available
        TempWishlistController->>TempWishlistController: History status != Error or no error records
        TempWishlistController-->>Client: 400 Bad Request ("No errors available for download")
    else CSV Generation Error
        ExportService-->>TempWishlistController: Exception during CSV generation
        TempWishlistController-->>Client: 400 Bad Request ("Error generating CSV file")
    end
    end

Business Logic Details

Advanced File Validation

• Multi-Layer Security: MIME type validation, file extension checking, content scanning for malicious patterns
• Format Support: CSV and TXT files with UTF-8 encoding, automatic encoding detection and conversion
• Size Management: 1MB maximum file size with progress tracking for large files
• Structure Validation: CSV header validation, column count verification, data type checking

Sophisticated Import Processing

• Asynchronous Processing: Large files processed in background with real-time progress updates
• Row-Level Validation: Each CSV row validated independently with detailed error collection
• Business Rule Enforcement: Product input format validation, mall compatibility checking, required field validation
• Transaction Safety: All database operations wrapped in transactions with comprehensive rollback strategies

Comprehensive Status Management

• Pending (0): Import initiated, file uploaded, processing in progress with progress tracking
• Success (1): All rows processed successfully, temporary wishlist created, import completed
• Error (2): Validation errors found, detailed error report available, partial processing possible

Advanced Error Handling & Reporting

• Detailed Error Collection: Field-level validation errors with specific messages and correction guidance
• Error Categorization: Errors classified by type (format, business rule, data integrity) for better user understanding
• Comprehensive Error CSV: Downloadable CSV with original data plus detailed error descriptions and correction suggestions
• Error Persistence: All errors stored with full context for audit, debugging, and user support

Complete Import History Tracking

• Full Audit Trail: All import attempts recorded with comprehensive metadata and processing details
• File Metadata Preservation: Original filename, file size, encoding, upload timestamp, processing duration
• User Association: Import history linked to authenticated user with proper access controls
• Status Monitoring: Real-time status updates with progress indicators and completion notifications

User Experience Optimization

• Progress Tracking: Real-time progress updates for large file processing with estimated completion times
• Detailed Feedback: Comprehensive error messages with specific field-level validation failures
• Download Management: Efficient streaming downloads for error CSV files with proper browser handling
• Localization: User-friendly error messages in Japanese with technical details for debugging

Integration Points

Related APIs

• Temporary Wishlist Management: Created wishlists seamlessly integrate with main wishlist system for further processing
• File Storage: CSV files temporarily stored with proper cleanup and security management
• Error Reporting: Integration with notification system for import completion and error alerts
• User Management: Integration with user authentication and group membership validation

External Services

• File System: Secure temporary file storage with automatic cleanup and access controls
• Background Jobs: Queue system for large file processing with priority management and retry mechanisms
• Notification System: Real-time user alerts for import completion, errors, and progress updates
• Monitoring System: Performance tracking, error monitoring, and system health checks

Data Flow

• CSV → Validation → Temporary Wishlist: Successful imports create temporary wishlists ready for conversion to official wishlists
• Errors → Error CSV → User Feedback: Failed validations generate comprehensive error reports for user review and correction
• History → Audit → Compliance: All operations tracked for compliance, debugging, and user support with complete audit trails