Transport Location ID Migration: Data-Preserving Fix

Status: ✅ COMPLETED (2025-12-07)

Goal: Fix the Transport database migration to convert bigint location columns to uuid while preserving existing data.

Problem:

• The Base migration was incorrectly modified to use uuid after it was already applied to the database with bigint
• The database has existing data that needs to be preserved
• BBU views depend on the transport.deliveries columns

Summary of Changes

Files Modified

Migration Strategy

1. BBU seeder creates views with location_mappings pattern (works with bigint)
2. Transport migration:
   • Drops ALL BBU views (analytics + expansion, 29 views total with CASCADE)
   • Adds new uuid columns
   • Migrates data using location_mappings lookup
   • Drops old bigint columns
   • Renames new columns
   • Sets NOT NULL constraints
   • Recreates analytics views with direct spatial.locations join
3. BBU seeder (on next run) recreates expansion views

Key Fixes During Implementation

1. Component Order Issue: BBU runs before Transport in db-manager order. Fixed by keeping SupersetAnalyticsViewsSql.cs with old pattern, letting Transport migration update views.

2. CASCADE Required: PostgreSQL requires CASCADE on DROP VIEW to drop dependent views (e.g., bbu_oblpn_status_daily depends on bbu_oblpn_shipments).

3. Expansion Views: Had to drop ALL BBU views, not just analytics views. The SupersetExpansionViewsSql.cs views (like daily_shipment_volume) also reference transport.deliveries.location_id.

Success Criteria

• Base migration reflects original bigint schema
• New migration successfully converts bigint → uuid
• Build succeeds
• All existing shipment/delivery data preserved
• location_id/source_location_id now uuid type
• BBU views work with direct spatial.locations join
• Transport API functional
• Application running as expected

Technical Details

Current Schema (after migration)

• transport.shipments.source_location_id → uuid (was bigint)
• transport.deliveries.location_id → uuid (was bigint)
• transport.location_mappings → obsolete, can be removed in future cleanup

View Join Pattern (after migration)

-- Direct join to spatial.locations (no more location_mappings indirection)
LEFT JOIN spatial.locations loc ON loc.id = d.location_id

Migration SQL Flow

-- Step 1: Drop ALL BBU views (29 views with CASCADE)
DROP VIEW IF EXISTS bbu.bbu_oblpn_location_backlog CASCADE;
-- ... (all analytics and expansion views)

-- Step 2: Add new uuid columns
ALTER TABLE transport.deliveries ADD COLUMN location_id_new uuid;
ALTER TABLE transport.shipments ADD COLUMN source_location_id_new uuid;

-- Step 3: Migrate data using location_mappings lookup
UPDATE transport.deliveries d
SET location_id_new = COALESCE(
    (SELECT lm.location_id FROM transport.location_mappings lm WHERE lm.id = d.location_id),
    '00000000-0000-0000-0000-000000000000'::uuid
);

-- Step 4-6: Drop old, rename, set NOT NULL
ALTER TABLE transport.deliveries DROP COLUMN location_id;
ALTER TABLE transport.deliveries RENAME COLUMN location_id_new TO location_id;
ALTER TABLE transport.deliveries ALTER COLUMN location_id SET NOT NULL;
-- (same for shipments.source_location_id)

-- Step 7: Recreate analytics views with direct join
CREATE OR REPLACE VIEW bbu.bbu_oblpn_shipments AS ...

Cleanup Completed (2025-12-07)

• Mark LocationMapping.cs entity as [Obsolete]
• Mark DbSet<LocationMapping> as [Obsolete] in TransportDb.cs
• Update SupersetAnalyticsViewsSql.cs to use direct join pattern (consistent with migration)
• Remove unused GetOrCreateLegacyLocationIdAsync method from OracleProcessor.cs
• Build verified with 0 errors

Remaining Future Cleanup (Optional)

• Create migration to drop transport.location_mappings table
• Remove LocationMapping.cs file entirely
• Remove DbSet<LocationMapping> from TransportDb.cs