Flow Meter Asset ID Migration

Date: 2026-01-15
Migration: 20260115000000_add_asset_id_to_flow_meters.sql

Overview

This migration adds asset_id foreign key columns to flow meter related tables, replacing the string-based flow_meter identifier with a proper foreign key relationship to the data_assets table.

Changes

Database Schema

Three tables were modified to add asset_id UUID foreign key:

1. data_flow_meters

   • Added: asset_id UUID (nullable)
   • Foreign Key: References data_assets(asset_id) ON DELETE SET NULL
   • Index: idx_data_flow_meters_asset_id
   • Deprecation: flow_meter column marked as deprecated (kept for backward compatibility)

2. ops_dustloc_refills

   • Added: asset_id UUID (nullable)
   • Foreign Key: References data_assets(asset_id) ON DELETE SET NULL
   • Index: idx_ops_dustloc_refills_asset_id
   • Deprecation: flow_meter column marked as deprecated (kept for backward compatibility)

3. ops_flow_calibrations

   • Fixed: Changed asset_id from TEXT to UUID
   • Foreign Key: References data_assets(asset_id) ON DELETE SET NULL
   • Index: idx_ops_flow_calibrations_asset_id
   • Deprecation: flow_meter column marked as deprecated (kept for backward compatibility)

TypeScript Types

Updated src/lib/supabaseTypes.ts to include asset_id: string | null in:

• data_flow_meters (Row, Insert, Update)
• ops_dustloc_refills (Row, Insert, Update)
• ops_flow_calibrations (already had it, type corrected from TEXT to UUID)

Data Migration

A helper function migrate_flow_meter_to_asset_id() was created to automatically map existing flow_meter string values to asset_id by matching against data_assets.display_id.

Migration Results

SELECT * FROM migrate_flow_meter_to_asset_id();

Table Name	Records Updated
data_flow_meters	971
ops_dustloc_refills	21
ops_flow_calibrations	0

Coverage:

• data_flow_meters: 971/971 (100%)
• ops_dustloc_refills: 21/22 (95.5%)
• ops_flow_calibrations: 0/1 (0%)

Unmapped Records

Records that couldn't be automatically mapped (1 in ops_dustloc_refills, 1 in ops_flow_calibrations) will need manual review. These records either:

• Have flow_meter values that don't match any display_id in data_assets
• Have NULL flow_meter values
• Reference flow meters that don't exist in the assets database

Backward Compatibility

The original flow_meter string columns are retained to maintain backward compatibility:

• Existing code will continue to work without modification
• New code should prefer using asset_id for better data integrity
• The flow_meter columns are marked as deprecated in database comments

Benefits

1. Data Integrity: Foreign key constraints ensure flow meter references are valid
2. Rich Metadata: Access to full asset information (type, status, location, etc.)
3. Cascading Updates: Changes to asset identifiers propagate automatically
4. Better Performance: UUID indexes are more efficient than text string comparisons
5. Unified Asset Management: Consistent approach across all asset types

Future Work

Phase 2 (Optional)

Once all applications are updated to use asset_id:

1. Remove flow_meter string columns
2. Update all queries to use asset_id joins
3. Simplify data import scripts

Recommended Code Updates

While not required (backward compatible), consider updating:

1. Flow Meter Service (src/features/flow-meter/services/databaseService.ts)

   • Use asset_id for insertions where possible
   • Join with data_assets to get display names

2. Refills Management

   • Update forms to select assets via dropdown (from data_assets)
   • Store asset_id instead of string identifier

3. Calibration Management

   • Already has asset_id field in forms
   • Ensure proper UUID type handling

Verification Queries

-- Check coverage
SELECT COUNT(*) as total, COUNT(asset_id) as with_asset_id 
FROM data_flow_meters;

-- Find unmapped records
SELECT DISTINCT fm.flow_meter 
FROM data_flow_meters fm 
LEFT JOIN data_assets da ON da.display_id = fm.flow_meter 
WHERE fm.asset_id IS NULL 
  AND fm.flow_meter IS NOT NULL 
  AND da.asset_id IS NULL;

-- Verify foreign key constraints
SELECT conname, contype, conrelid::regclass, confrelid::regclass
FROM pg_constraint 
WHERE conname LIKE '%flow%asset%';

Rollback

If needed, the migration can be rolled back:

-- Remove foreign keys
ALTER TABLE data_flow_meters DROP CONSTRAINT IF EXISTS fk_data_flow_meters_asset_id;
ALTER TABLE ops_dustloc_refills DROP CONSTRAINT IF EXISTS fk_ops_dustloc_refills_asset_id;
ALTER TABLE ops_flow_calibrations DROP CONSTRAINT IF EXISTS fk_ops_flow_calibrations_asset_id;

-- Drop columns
ALTER TABLE data_flow_meters DROP COLUMN IF EXISTS asset_id;
ALTER TABLE ops_dustloc_refills DROP COLUMN IF EXISTS asset_id;

-- Revert ops_flow_calibrations.asset_id to TEXT if needed
ALTER TABLE ops_flow_calibrations ALTER COLUMN asset_id TYPE TEXT;

-- Drop helper function
DROP FUNCTION IF EXISTS migrate_flow_meter_to_asset_id();

Testing

• [x] Migration applied successfully
• [x] TypeScript types updated
• [x] Build passes without errors
• [x] Data migration function executed
• [x] 971 flow meter records mapped
• [x] Foreign key constraints working

Related Documents

• Migration Script
• Assets Specification
• Database Schema