Legacy System Migration Guide

This comprehensive guide provides step-by-step instructions for migrating from an existing SQL-based inventory system to the new LUStores University Inventory Management System.

Overview

The LUStores system replaces legacy inventory systems with a modern, web-based solution. This migration guide covers:

  • Pre-migration assessment and planning

  • Data extraction from legacy systems

  • Schema mapping and transformation

  • Data import procedures

  • Post-migration validation and testing

  • Rollback procedures

Migration Planning

Pre-Migration Assessment

Before starting the migration, assess your legacy system:

System Information Gathering:

Database Platform: _______________
Database Version: ________________
Schema Name/Database: ____________
Primary Tables of Interest:
- Items/Inventory: _______________
- Categories: ____________________
- Users: _________________________
- Stock Movements: _______________
- Sales/Transactions: ____________

Data Volume Assessment:

-- Run these queries in your legacy system to assess data volume
SELECT COUNT(*) as item_count FROM [your_items_table];
SELECT COUNT(*) as category_count FROM [your_categories_table];
SELECT COUNT(*) as user_count FROM [your_users_table];
SELECT COUNT(*) as movement_count FROM [your_movements_table];

Critical Data Identification:

  1. Essential Data (Must migrate): - Current inventory items and stock levels - Active categories and their structure - User accounts and role assignments - Critical stock movement history (last 12 months)

  2. Important Data (Should migrate): - Complete stock movement history - Sales transaction records - Supplier/vendor information - Asset tags and serial numbers

  3. Optional Data (Nice to migrate): - Historical reports and analytics - Archived or deleted items - System logs and audit trails

Migration Timeline

Recommended Timeline (4-6 weeks):

Week 1: Assessment and Planning
- Legacy system analysis
- Schema mapping
- Test environment setup

Week 2-3: Data Extraction and Transformation
- Export legacy data
- Transform to LUStores format
- Test imports on staging

Week 4: Production Migration
- Final data export
- Production import
- User acceptance testing

Week 5-6: Post-Migration
- User training
- System optimization
- Documentation updates

Legacy System Analysis

Common Legacy Database Schemas

Typical Legacy Inventory Tables:

-- Common legacy table structures
CREATE TABLE inventory_items (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(255),
    item_code VARCHAR(50),
    description TEXT,
    category_id INT,
    unit_price DECIMAL(10,2),
    quantity_on_hand INT,
    reorder_level INT,
    created_date DATETIME,
    last_updated DATETIME
);

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100),
    description TEXT,
    parent_category_id INT
);

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(255),
    full_name VARCHAR(255),
    user_role VARCHAR(20),
    is_active BOOLEAN,
    created_date DATETIME
);

Schema Discovery Queries:

Use these queries to understand your legacy database structure:

-- Discover all tables
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'your_database_name';

-- Discover table columns
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'your_table_name';

-- Discover relationships
SELECT constraint_name, table_name, column_name,
       referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL;

Data Extraction

SQL Export Scripts

Complete Data Export:

-- Categories Export
SELECT
    category_id as legacy_id,
    category_name as name,
    description,
    COALESCE(parent_category_id, 0) as parent_id,
    'fas fa-box' as icon,
    'blue' as color,
    created_date
FROM categories
ORDER BY category_id;

-- Items Export
SELECT
    item_id as legacy_id,
    item_name as name,
    item_code as sku,
    description,
    category_id as legacy_category_id,
    unit_price as price,
    quantity_on_hand as current_stock,
    reorder_level as minimum_stock,
    CASE WHEN is_active = 1 THEN true ELSE false END as is_active,
    created_date,
    last_updated
FROM inventory_items
ORDER BY item_id;

-- Users Export
SELECT
    user_id as legacy_id,
    username,
    email,
    full_name,
    CASE
        WHEN user_role = 'admin' THEN 'admin'
        WHEN user_role = 'manager' THEN 'manager'
        ELSE 'user'
    END as role,
    is_active,
    created_date
FROM users
ORDER BY user_id;

Stock Movements Export:

-- Stock movements (if available)
SELECT
    movement_id as legacy_id,
    item_id as legacy_item_id,
    movement_type as type,
    quantity,
    previous_quantity as previous_stock,
    new_quantity as new_stock,
    movement_reason as reason,
    performed_by_user_id as legacy_user_id,
    movement_date as created_at
FROM stock_movements
WHERE movement_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
ORDER BY movement_date;

CSV Export Commands:

# MySQL/MariaDB export
mysql -h hostname -u username -p database_name \
  -e "SELECT ... FROM categories" \
  > categories_export.csv

# PostgreSQL export
psql -h hostname -U username -d database_name \
  -c "\COPY (SELECT ... FROM categories) TO 'categories_export.csv' CSV HEADER"

# SQL Server export
sqlcmd -S servername -d database_name \
  -Q "SELECT ... FROM categories" \
  -o "categories_export.csv" -h-1 -s"," -w 999

Schema Mapping

LUStores Target Schema

Target table structures in LUStores:

-- LUStores categories table
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    icon VARCHAR(50) NOT NULL DEFAULT 'fas fa-box',
    color VARCHAR(50) NOT NULL DEFAULT 'blue',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- LUStores items table
CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    sku VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    category_id INTEGER NOT NULL REFERENCES categories(id),
    price DECIMAL(10,2) NOT NULL,
    vat_rate DECIMAL(5,4) NOT NULL DEFAULT 0.2000,
    vat_included BOOLEAN NOT NULL DEFAULT true,
    current_stock INTEGER NOT NULL DEFAULT 0,
    minimum_stock INTEGER NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_by VARCHAR NOT NULL REFERENCES users(id),
    updated_by VARCHAR REFERENCES users(id),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- LUStores users table
CREATE TABLE users (
    id VARCHAR PRIMARY KEY,
    email VARCHAR NOT NULL UNIQUE,
    password_hash VARCHAR,
    first_name VARCHAR,
    last_name VARCHAR,
    role VARCHAR NOT NULL DEFAULT 'user',
    is_active BOOLEAN NOT NULL DEFAULT true,
    must_change_password BOOLEAN NOT NULL DEFAULT false,
    last_login TIMESTAMP,
    profile_image_url VARCHAR,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

Field Mapping Guide

Categories Mapping:

Legacy Field          → LUStores Field      Notes
===============================================
category_id           → (reference only)    Keep for item mapping
category_name         → name               Direct mapping
description           → description        Direct mapping
parent_category_id    → (not supported)    Flatten hierarchy
created_date          → created_at         Convert to timestamp
(none)                → icon               Default: 'fas fa-box'
(none)                → color              Default: 'blue'

Items Mapping:

Legacy Field          → LUStores Field      Notes
===============================================
item_id               → (reference only)    Keep for movement mapping
item_name             → name               Direct mapping
item_code/sku         → sku                Must be unique
description           → description        Direct mapping
category_id           → category_id        Map via categories
unit_price            → price              Direct mapping
quantity_on_hand      → current_stock      Direct mapping
reorder_level         → minimum_stock      Direct mapping
is_active             → is_active          Convert boolean
created_date          → created_at         Convert timestamp
(none)                → vat_rate           Default: 0.2000 (20%)
(none)                → vat_included       Default: true
(none)                → created_by         Set to migration user
(none)                → updated_by         Set to migration user

Users Mapping:

Legacy Field          → LUStores Field      Notes
===============================================
user_id               → (reference only)    Keep for movements
username              → id                 Use as primary key
email                 → email              Direct mapping
full_name             → first_name +       Split name
                        last_name
user_role             → role               Map values: admin/manager/user
is_active             → is_active          Direct mapping
(none)                → password_hash      Generate temp password
(none)                → must_change_password  Set to true

Data Transformation

SQL Transformation Scripts

Categories transformation:

-- Transform categories data
WITH transformed_categories AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY category_id) as new_id,
    category_id as legacy_id,
    category_name as name,
    COALESCE(description, '') as description,
    'fas fa-box' as icon,
    CASE
      WHEN category_id % 6 = 0 THEN 'blue'
      WHEN category_id % 6 = 1 THEN 'green'
      WHEN category_id % 6 = 2 THEN 'purple'
      WHEN category_id % 6 = 3 THEN 'orange'
      WHEN category_id % 6 = 4 THEN 'red'
      ELSE 'gray'
    END as color,
    NOW() as created_at,
    NOW() as updated_at
  FROM legacy_categories
  WHERE category_name IS NOT NULL
)
SELECT * FROM transformed_categories;

Items transformation:

-- Transform items data
WITH transformed_items AS (
  SELECT
    item_name as name,
    COALESCE(item_code, CONCAT('ITEM-', LPAD(item_id, 6, '0'))) as sku,
    COALESCE(description, '') as description,
    cat_map.new_category_id as category_id,
    COALESCE(unit_price, 0.00) as price,
    0.2000 as vat_rate,
    true as vat_included,
    COALESCE(quantity_on_hand, 0) as current_stock,
    COALESCE(reorder_level, 0) as minimum_stock,
    COALESCE(is_active, true) as is_active,
    'migration_admin' as created_by,
    'migration_admin' as updated_by,
    COALESCE(created_date, NOW()) as created_at,
    COALESCE(last_updated, NOW()) as updated_at
  FROM legacy_items li
  JOIN category_mapping cat_map ON li.category_id = cat_map.legacy_category_id
  WHERE item_name IS NOT NULL
    AND item_name != ''
)
SELECT * FROM transformed_items;

Users transformation:

-- Transform users data
WITH transformed_users AS (
  SELECT
    COALESCE(username, CONCAT('user_', user_id)) as id,
    email,
    '$2b$10$defaulthash...' as password_hash,
    TRIM(SUBSTRING_INDEX(full_name, ' ', 1)) as first_name,
    TRIM(SUBSTRING(full_name, LOCATE(' ', full_name) + 1)) as last_name,
    CASE
      WHEN user_role IN ('admin', 'administrator') THEN 'admin'
      WHEN user_role IN ('manager', 'supervisor') THEN 'manager'
      ELSE 'user'
    END as role,
    COALESCE(is_active, true) as is_active,
    true as must_change_password,
    null as last_login,
    null as profile_image_url,
    COALESCE(created_date, NOW()) as created_at,
    NOW() as updated_at
  FROM legacy_users
  WHERE email IS NOT NULL
    AND email != ''
)
SELECT * FROM transformed_users;

Migration Scripts

Pre-Migration Setup

1. Create Migration User:

-- Create temporary migration admin user
INSERT INTO users (
  id, email, password_hash, first_name, last_name,
  role, is_active, must_change_password, created_at, updated_at
) VALUES (
  'migration_admin',
  'migration@university.edu',
  '$2b$10$8K1p/a0dqailSurF.ONj2uyaLiI6Hqh4LAks.5wvW.3BlxtZjO96O',
  'Migration',
  'Administrator',
  'admin',
  true,
  false,
  NOW(),
  NOW()
);

2. Disable Constraints (Temporarily):

-- Disable foreign key checks for migration
SET foreign_key_checks = 0;  -- MySQL
-- OR
SET session_replication_role = replica;  -- PostgreSQL

Migration Import Scripts

Categories Import:

-- Step 1: Import categories
INSERT INTO categories (name, description, icon, color, created_at, updated_at)
SELECT DISTINCT
  category_name,
  COALESCE(description, ''),
  'fas fa-box',
  'blue',
  NOW(),
  NOW()
FROM staging_categories
WHERE category_name IS NOT NULL
  AND category_name NOT IN (SELECT name FROM categories);

Items Import:

-- Step 2: Import items (after categories)
INSERT INTO items (
  name, sku, description, category_id, price, vat_rate, vat_included,
  current_stock, minimum_stock, is_active, created_by, updated_by,
  created_at, updated_at
)
SELECT
  si.item_name,
  si.item_code,
  COALESCE(si.description, ''),
  c.id,
  COALESCE(si.unit_price, 0.00),
  0.2000,
  true,
  COALESCE(si.quantity_on_hand, 0),
  COALESCE(si.reorder_level, 0),
  COALESCE(si.is_active, true),
  'migration_admin',
  'migration_admin',
  COALESCE(si.created_date, NOW()),
  NOW()
FROM staging_items si
JOIN categories c ON c.name = si.category_name
WHERE si.item_name IS NOT NULL
  AND si.item_code NOT IN (SELECT sku FROM items);

Stock Movements Import:

-- Step 3: Import stock movements (if available)
INSERT INTO stock_movements (
  item_id, type, quantity, previous_stock, new_stock,
  reason, performed_by, created_at
)
SELECT
  i.id,
  CASE
    WHEN sm.movement_type = 'IN' THEN 'in'
    WHEN sm.movement_type = 'OUT' THEN 'out'
    ELSE 'adjustment'
  END,
  sm.quantity,
  sm.previous_quantity,
  sm.new_quantity,
  COALESCE(sm.movement_reason, 'Legacy data migration'),
  'migration_admin',
  sm.movement_date
FROM staging_movements sm
JOIN items i ON i.sku = sm.item_code
WHERE sm.movement_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH);

Post-Migration Tasks

Data Validation

Validation Queries:

-- Verify category migration
SELECT COUNT(*) as migrated_categories FROM categories;
SELECT COUNT(*) as source_categories FROM staging_categories;

-- Verify item migration
SELECT COUNT(*) as migrated_items FROM items;
SELECT COUNT(*) as source_items FROM staging_items;

-- Check for missing SKUs
SELECT COUNT(*) as duplicate_skus
FROM items
GROUP BY sku
HAVING COUNT(*) > 1;

-- Verify stock levels
SELECT
  SUM(current_stock) as total_migrated_stock,
  COUNT(*) as items_with_stock
FROM items
WHERE current_stock > 0;

-- Check category assignments
SELECT c.name, COUNT(i.id) as item_count
FROM categories c
LEFT JOIN items i ON c.id = i.category_id
GROUP BY c.id, c.name
ORDER BY item_count DESC;

Data Quality Checks:

-- Find items without categories
SELECT name, sku FROM items WHERE category_id IS NULL;

-- Find duplicate SKUs
SELECT sku, COUNT(*) as count
FROM items
GROUP BY sku
HAVING COUNT(*) > 1;

-- Find items with invalid prices
SELECT name, sku, price
FROM items
WHERE price < 0 OR price IS NULL;

-- Find users without email
SELECT id, first_name, last_name
FROM users
WHERE email IS NULL OR email = '';

Cleanup Tasks

Remove Migration Artifacts:

-- Re-enable constraints
SET foreign_key_checks = 1;  -- MySQL
-- OR
SET session_replication_role = DEFAULT;  -- PostgreSQL

-- Remove staging tables
DROP TABLE IF EXISTS staging_categories;
DROP TABLE IF EXISTS staging_items;
DROP TABLE IF EXISTS staging_movements;

-- Update statistics
ANALYZE TABLE categories;
ANALYZE TABLE items;
ANALYZE TABLE users;
ANALYZE TABLE stock_movements;

Configuration Updates

Update System Settings:

# Update environment variables
echo "MIGRATION_COMPLETED=$(date -Iseconds)" >> .env
echo "LEGACY_SYSTEM_NAME=YourLegacySystem" >> .env

# Initialize default admin password
npm run admin:reset-password admin@university.edu

Create Initial Backup:

# Create post-migration backup
pg_dump $DATABASE_URL > "post_migration_backup_$(date +%Y%m%d_%H%M%S).sql"

Troubleshooting

Common Migration Issues

Duplicate SKU Conflicts:

-- Find and resolve duplicate SKUs
UPDATE items
SET sku = CONCAT(sku, '-DUP-', id)
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY sku ORDER BY id) as rn
    FROM items
  ) t WHERE rn > 1
);

Category Mapping Issues:

-- Create default category for unmapped items
INSERT INTO categories (name, description, icon, color)
VALUES ('Uncategorized', 'Items from legacy system without category mapping', 'fas fa-question', 'gray');

-- Assign unmapped items to default category
UPDATE items
SET category_id = (SELECT id FROM categories WHERE name = 'Uncategorized')
WHERE category_id IS NULL;

Character Encoding Problems:

# Convert file encoding before import
iconv -f ISO-8859-1 -t UTF-8 legacy_export.csv > utf8_export.csv

Large Data Volume Issues:

-- Import in batches
INSERT INTO items (...)
SELECT ...
FROM staging_items
WHERE id BETWEEN 1 AND 1000;

-- Repeat for each batch

Rollback Procedures

Emergency Rollback

If migration fails and you need to rollback:

# Stop the application
docker-compose down

# Restore pre-migration backup
psql $DATABASE_URL < pre_migration_backup.sql

# Restart application
docker-compose up -d

Partial Rollback (Data Only):

-- Remove migrated data while preserving schema
DELETE FROM stock_movements WHERE performed_by = 'migration_admin';
DELETE FROM items WHERE created_by = 'migration_admin';
DELETE FROM categories WHERE name NOT IN ('IT Equipment', 'Office Supplies', 'Laboratory Equipment', 'Medical Supplies');
DELETE FROM users WHERE id LIKE 'user_%' OR id = 'migration_admin';

Success Criteria

Migration Success Checklist

□ All critical data migrated successfully
□ Data validation queries pass
□ No duplicate SKUs or constraint violations
□ User authentication works
□ Stock levels are accurate
□ Category structure is logical
□ System performance is acceptable
□ Backup created and verified
□ User training completed
□ Legacy system properly archived

Key Metrics to Verify:

-- Final validation report
SELECT
  'Categories' as entity,
  COUNT(*) as migrated_count,
  (SELECT COUNT(*) FROM staging_categories) as source_count
FROM categories

UNION ALL

SELECT
  'Items',
  COUNT(*),
  (SELECT COUNT(*) FROM staging_items)
FROM items

UNION ALL

SELECT
  'Users',
  COUNT(*),
  (SELECT COUNT(*) FROM staging_users)
FROM users
WHERE id != 'migration_admin';

This migration guide provides a comprehensive framework for moving from any legacy SQL-based inventory system to LUStores. Adjust the specific SQL queries and procedures based on your legacy system’s unique schema and requirements.