PSA/docs/migrations/reference_data_import_migration.md

Reference Data Import System Migration Guide

Overview

This guide covers the migration process for existing installations to enable the new Reference Data Import System introduced in migration 20250630140000_create_standard_reference_tables.cjs.

What's New

The Reference Data Import System adds:

1. Standard reference tables with pre-defined data
2. Import functionality in the UI for all reference types
3. Conflict resolution for handling duplicates
4. Enhanced category management with parent-child relationships
5. Display ordering for all reference data types

Migration Steps

1. Database Migration

Run the migration to create standard tables and update existing tables:

npm run db:migrate:latest

This migration will:

• Create standard_* tables for all reference types
• Add display_order column to existing tables if missing
• Add description column to channels table
• Populate standard tables with pre-defined data
• Update existing records with sequential display orders

2. Update Seed Files

If you use seed files, update them to include the new fields:

Channels

Add description and display_order:

{
  channel_name: 'Technical Issues',
  description: 'System failures, hardware issues, and technical problems',
  display_order: 1
}

Categories

Add display_order and ensure channel_id is set:

{
  category_name: 'Hardware',
  channel_id: '<channel_uuid>',
  display_order: 1,
  created_by: '<user_uuid>'
}

Service Categories

Add display_order:

{
  category_name: 'Managed Services',
  description: 'Ongoing managed service offerings',
  display_order: 1
}

3. UI Updates

The UI components are automatically updated with the migration. No manual changes needed.

4. Verify Installation

After migration:

1. Navigate to Settings → General → Ticketing Settings
2. Check each tab has the "Import from Standard Types" button
3. Verify existing data has proper display orders
4. Test importing a few standard items

Breaking Changes

Required Fields

The following fields are now required:

1. Categories

   • channel_id - Every category must belong to a channel
   • created_by - User who created the category

2. All Reference Types

   • display_order - Defaults to 0 if not specified

API Changes

If you have custom API integrations:

1. Category Creation - Must include channel_id
2. Import Endpoints - New endpoints available:
   • POST /api/reference-data/available
   • POST /api/reference-data/check-conflicts
   • POST /api/reference-data/import

Rollback Procedure

If you need to rollback:

npm run db:migrate:rollback

This will:

• Drop all standard_* tables
• Remove display_order columns
• Remove description from channels

Warning: Rollback will lose any imported standard data.

Post-Migration Tasks

1. Review Display Orders

The migration assigns sequential orders to existing data. Review and adjust if needed:

1. Go to each reference data settings page
2. Review the order column
3. Edit items to adjust ordering

2. Import Standard Data

Take advantage of pre-defined configurations:

1. Click "Import from Standard Types"
2. Select relevant items for your organization
3. Resolve any conflicts
4. Customize as needed

3. Update Documentation

Update your internal documentation to reflect:

• New import functionality
• Category channel requirements
• Display order management

Troubleshooting

Migration Fails

Error: "column already exists"

• The migration may have partially completed
• Check which tables were created
• Manually drop partial tables and re-run

Error: "null value in column 'created_by'"

• Ensure all categories have a valid created_by user
• Update existing records before migration

Import Issues

"No standard items available"

• Verify migration completed successfully
• Check standard tables have data:

  SELECT COUNT(*) FROM standard_channels;
  SELECT COUNT(*) FROM standard_categories;
  

Import fails with constraint errors

• Check tenant context is set
• Verify user has appropriate permissions
• Review server logs for detailed errors

Performance Concerns

The standard tables are relatively small and indexed appropriately. If you experience performance issues:

1. Check indexes are created:

   \d standard_categories
   

2. Analyze query performance:

   EXPLAIN ANALYZE SELECT * FROM standard_categories WHERE ...
   

Best Practices

For New Installations

1. Run migrations before seeding
2. Use import functionality instead of manual entry
3. Customize after importing

For Existing Installations

1. Backup database before migration
2. Test in staging environment first
3. Communicate changes to users
4. Plan for training on new features

Ongoing Maintenance

1. Review standard data with each upgrade
2. Keep display orders logical
3. Document any customizations
4. Use consistent naming conventions

Support

For issues or questions:

1. Check the Reference Data Import System documentation
2. Review the Settings Reference Data Guide
3. Check server logs for detailed error messages
4. Contact support with migration version and error details