Data Import & Migration Hub

Version: v4.2.0
Status: Production Ready

The Data Import & Migration Hub provides a comprehensive solution for importing and migrating data from various formats (CSV, XLSX, XML, JSON) into Lager Guru, with automatic mapping, validation, deduplication, and rollback capabilities.

Overview

The Import Hub enables administrators to:

• Import large datasets from external systems (SAP, ERP, spreadsheets)
• Automatically map source columns to Lager Guru fields
• Validate data before import
• Detect and handle duplicates
• Rollback imports if needed
• Track import history and statistics

Features

Supported Formats

• CSV - Comma-separated values with header detection
• XLSX/XLS - Excel files with multi-sheet support
• XML - XML files with SAP/ERP format support
• JSON - JSON arrays or objects

Import Types

1. SKU / Items - Import inventory items with SKU, name, barcode, etc.
2. Stock Levels - Import current stock quantities by zone
3. Zones - Import warehouse zones with coordinates
4. Equipment - Import equipment records
5. Drivers - Import driver information
6. Safety Data - Import safety-related data
7. Multi-Format - Flexible import for mixed data

Core Capabilities

1. Auto-Mapping Engine

• Automatically matches source columns to target fields
• Uses pattern matching and confidence scoring
• Supports manual override
• Handles common field name variations (e.g., "SKU_NUMBER" → "sku", "LAGERPLATZ" → "zone")

2. AI Deduplication

• Detects duplicate or similar records
• Uses fuzzy string matching
• Prioritizes barcode matches
• Shows duplicate warnings in UI

3. Validation Engine

• Validates required fields
• Checks data types (string, number, date, UUID, email)
• Validates field lengths and ranges
• Checks zone references
• Validates SKU uniqueness
• Shows validation errors per record

4. Rollback System

• Creates snapshots before import
• Allows complete rollback of imports
• Restores previous data state
• Admin-only access for safety

Database Schema

import_jobs

Tracks import job metadata and status.

Fields:

• id - Unique identifier
• tenant_id - Tenant isolation
• created_by - User who created the job
• status - pending, processing, completed, failed, rollback
• type - Import type (sku, stock, zones, etc.)
• file_name - Original file name
• file_ref - Supabase Storage reference
• mapping - Column mapping configuration (JSONB)
• stats - Import statistics (JSONB)
• error_log - Error messages if failed

import_records

Individual records from import files.

Fields:

• id - Unique identifier
• job_id - Reference to import job
• row_number - Original row number
• raw_data - Original row data (JSONB)
• normalized_data - Mapped data (JSONB)
• validation_errors - Array of validation errors (JSONB)
• status - valid, invalid, skipped, imported

import_rollback

Rollback snapshots for imported records.

Fields:

• id - Unique identifier
• job_id - Reference to import job
• table_name - Target table name
• record_id - Record ID in target table
• previous_data - Snapshot before import (JSONB)

Edge Functions

process-import-job

Processes import job and parses file data.

Input:

{
  "job_id": "uuid"
}

Output:

{
  "success": true,
  "job_id": "uuid",
  "file_type": "csv",
  "stats": {
    "total_rows": 100,
    "valid_rows": 95,
    "invalid_rows": 5
  }
}

apply-import-job

Applies validated records to target tables.

Input:

{
  "job_id": "uuid"
}

Output:

{
  "success": true,
  "imported_count": 95,
  "message": "Successfully imported 95 records"
}

rollback-import-job

Rolls back an import job, restoring previous state.

Input:

{
  "job_id": "uuid"
}

Output:

{
  "success": true,
  "rolled_back": 95,
  "message": "Rolled back 95 records"
}

UI Workflow

1. Upload File

• Drag & drop or click to select file
• Choose import type
• Preview first 5 rows
• Upload to Supabase Storage

2. Field Mapping

• Auto-mapping with confidence scores
• Manual override for unmapped columns
• Visual column-to-field mapping
• Save mapping configuration

3. Validation Review

• View validation results
• See errors per record
• Duplicate detection warnings
• Proceed if valid records exist

4. Import Execution

• Execute import for valid records
• Progress tracking
• Success/failure notification
• Statistics display

5. Rollback (if needed)

• Admin-only rollback option
• Confirmation dialog
• Complete data restoration

6. History & Details

• View all import jobs
• Filter by status/type
• View job details and statistics
• Access individual record data

Integration Points

Safety Intelligence

• Imported zones with coordinates automatically link to Hazard Map
• Zone risk scores can be recalculated after import

Inventory Vision

• Imported SKUs with barcodes can be immediately scanned
• Vision recognition works instantly for new items

Inventory Module

• SKU imports create/update inventory items
• Stock imports create stock movements
• Zone imports create/update zones

Security & Permissions

• RLS Policies: All tables protected with Row-Level Security
• Tenant Isolation: Complete multi-tenant support
• Role-Based Access:
  • Admin: Full access (including rollback)
  • Worker: Read/write access
  • Driver: No access
  • Safety Officer: No access

Performance

• Batch Processing: Records processed in batches
• Streaming: Large files handled efficiently
• Low Memory: Optimized for large datasets
• Retry-Safe: Idempotent operations

Usage Examples

Import SKUs from CSV

1. Upload CSV file with columns: SKU_NUMBER, ITEM_NAME, BARCODE
2. Auto-mapping detects: SKU_NUMBER → sku, ITEM_NAME → name, BARCODE → barcode
3. Validation checks: SKU uniqueness, required fields
4. Execute import
5. New items appear in inventory

Import Stock Levels from Excel

1. Upload XLSX file with stock data
2. Map columns: SKU → sku, ZONE → zone, QUANTITY → quantity
3. Validation ensures zones exist
4. Execute creates stock movements (inbound)
5. Stock levels updated

Rollback Import

1. Navigate to import history
2. Select completed job
3. Click "Rollback"
4. Confirm action
5. All imported data restored to previous state

Limitations

• File Size: Recommended max 10MB per file
• Row Limit: No hard limit, but performance degrades with 100k+ rows
• Real-time: Import is not real-time (batch processing)
• Concurrent Imports: One import per tenant at a time recommended

Future Enhancements

• Scheduled imports (cron-based)
• API-based imports
• Incremental imports (delta updates)
• Import templates (save mapping configurations)
• Email notifications on completion
• Export functionality (reverse import)
• Advanced transformation rules
• Multi-file batch imports

Related Documentation

• Inventory Module
• Zone Management
• Safety Intelligence