Unified ID Migration - Complete TODO List

Current Status: Documentation updated, PostgreSQL and Neo4j still using dual-UUID system Objective: Migrate from dual-UUID system (PostgreSQL id + neo4j_id) to unified single-UUID system (same id in both databases)

✅ COMPLETED: Documentation Structure Updates

📚 Documentation Successfully Reorganized

• ✅ NEW: frontend/docs/database/neo4j-core-schema.md (replaces old graph-schema.md)
• ✅ NEW: frontend/docs/database/supabase-core-schema.md (PostgreSQL schema)
• ✅ UPDATED: frontend/docs/database/README.md (points to new schema files)
• ✅ DEPRECATED: frontend/docs/database/graph-schema.md (redirects to neo4j-core-schema.md)
• ✅ UPDATED: Backend README.md with new documentation synchronization approach
• ✅ UPDATED: Frontend overview documentation structure

🚨 PENDING: Unified ID Implementation

CRITICAL: The dual-UUID system is still active in production. The migration to unified IDs needs to be implemented.

🎯 PHASE 1: Pre-Migration Validation & Planning

Current System Analysis

• AUDIT: Complete analysis of current dual-UUID usage in codebase
  • Count all PostgreSQL queries using neo4j_id
  • Identify all Neo4j queries using separate UUID mapping
  • Document all resolver patterns that translate between UUIDs
• VALIDATE: Current data consistency
  • Verify all PostgreSQL objects have corresponding Neo4j nodes
  • Check for orphaned records in either database
  • Validate relationship integrity
• BACKUP: Full database backups before migration
  • PostgreSQL full backup with schema and data
  • Neo4j database backup with all nodes and relationships
  • Test backup restoration procedures

Migration Strategy Documentation

• CREATE: architecture/unified-id-migration-plan.md
  • Detailed step-by-step migration procedures
  • Rollback procedures for each step
  • Data validation scripts
  • Performance impact analysis
  • Downtime estimation and scheduling

🛠️ PHASE 2: Database Schema Migration

PostgreSQL Schema Changes

• CREATE: Migration script migrations/unified_id_migration.sql

  -- Step 1: Add temporary columns for tracking migration status
  ALTER TABLE object_instances ADD COLUMN migration_status TEXT DEFAULT 'pending';
  ALTER TABLE object_instances ADD COLUMN old_neo4j_id TEXT;
    
  -- Step 2: Backup current neo4j_id values
  UPDATE object_instances SET old_neo4j_id = neo4j_id;
    
  -- Step 3: Create new indexes for performance
  CREATE INDEX CONCURRENTLY idx_object_instances_migration_status 
  ON object_instances(migration_status);
  

• UPDATE: Supabase schema files
  • frontend/docs/database/supabase-core-schema.md
  • Remove neo4j_id column from documentation
  • Update all table references

Neo4j Schema Migration

• CREATE: Neo4j migration script

  // Step 1: Add temporary property for tracking migration
  MATCH (o:ObjectInstance) 
  SET o.old_id = o.id, o.migration_status = 'pending';
    
  // Step 2: Update node IDs to match PostgreSQL primary keys
  // (This will be done in batches via application code)
  

• VALIDATE: Neo4j relationship integrity after ID changes
  • Check all relationships still point to correct nodes
  • Validate spatial and functional relationship consistency

🧩 PHASE 3: Application Code Migration

Backend Resolver Updates

• UPDATE: app/object_creation_resolvers.py

  # OLD CODE (current):
  object_id = str(uuid.uuid4())        # PostgreSQL PK
  neo4j_id = str(uuid.uuid4())         # Neo4j node ID
    
  # NEW CODE:
  object_id = str(uuid.uuid4())        # Single UUID for both systems
    
  # PostgreSQL insert (remove neo4j_id)
  INSERT INTO object_instances (id, name, description, ...)
  VALUES (object_id, name, description, ...)
    
  # Neo4j node creation (use same UUID)
  CREATE (o:ObjectInstance {id: object_id, name: name, ...})
  

• UPDATE: app/resolvers.py

  # Remove neo4j_id to PostgreSQL id mapping logic
  # OLD:
  result = await conn.execute(
      text("SELECT neo4j_id, id FROM object_instances WHERE neo4j_id = ANY(:neo4j_ids)"),
      {"neo4j_ids": neo4j_ids}
  )
    
  # NEW: Direct ID matching
  result = await conn.execute(
      text("SELECT id FROM object_instances WHERE id = ANY(:object_ids)"),
      {"object_ids": object_ids}
  )
  

• UPDATE: app/image_resolvers.py
  • Remove UUID translation logic
  • Simplify object lookups with direct ID matching
  • Update all database queries

GraphQL Schema Updates

• UPDATE: GraphQL type definitions
  • Ensure consistent ID handling across all types
  • Remove any neo4j_id exposure in schema
  • Update mutation and query examples

Database Connection & Query Updates

• FIND & REPLACE: All queries that reference neo4j_id

  # Search for all neo4j_id usage
  grep -r "neo4j_id" app/
  grep -r "neo4j_id" frontend/docs/
  

• UPDATE: Data synchronization scripts
• UPDATE: Backup and restore procedures

🧪 PHASE 4: Migration Execution

Migration Process (Production)

• STEP 1: Deploy migration-ready code (feature flagged)
• STEP 2: Run PostgreSQL schema migration (add tracking columns)
• STEP 3: Execute Neo4j ID updates in batches

  # Process objects in batches of 100
  for batch in get_objects_batch(100):
      # Update Neo4j node IDs to match PostgreSQL IDs
      update_neo4j_node_ids(batch)
      # Mark as migrated in PostgreSQL
      mark_migration_complete(batch)
  

• STEP 4: Validate data consistency after each batch
• STEP 5: Switch to unified ID code (remove feature flag)
• STEP 6: Remove temporary migration columns

Validation & Testing

• CREATE: Comprehensive validation scripts

  -- Verify all objects have same ID in both databases
  SELECT COUNT(*) as consistent_objects FROM object_instances oi
  WHERE EXISTS (
    SELECT 1 FROM neo4j_objects_via_api no 
    WHERE no.id = oi.id
  );
  

• TEST: All GraphQL operations with unified IDs
• TEST: Object creation, updates, deletions
• TEST: Relationship creation and traversal
• BENCHMARK: Performance comparison before/after

📝 PHASE 5: Documentation Updates

Schema Documentation Updates

• UPDATE: frontend/docs/database/SCHEMA-VERIFICATION.md
  • Remove all references to neo4j_id column
  • Update verification commands
  • Document unified ID verification process
• UPDATE: frontend/docs/database/neo4j-core-schema.md
  • Update ObjectInstance node properties
  • Remove references to separate PostgreSQL ID mapping
  • Update all Cypher examples
• UPDATE: frontend/docs/database/supabase-core-schema.md
  • Remove neo4j_id column from table definitions
  • Update constraints and indexes
  • Add unified ID usage notes

API Documentation Updates

• UPDATE: frontend/docs/api/data-models.md
  • Update GraphQL type examples
  • Remove dual-UUID references
  • Update mutation and query examples
• UPDATE: frontend/docs/api/api-endpoints.md
  • Update all GraphQL examples with unified IDs
  • Remove object ID translation examples
  • Update error handling documentation

Use Case Documentation Updates

• UPDATE: Use case documents that reference object creation
  • Update data flow diagrams
  • Update database schema impact sections
  • Update example mutations and queries

Troubleshooting Updates

• UPDATE: frontend/docs/troubleshooting/database-troubleshooting.md
  • Remove dual-UUID troubleshooting sections
  • Add unified ID consistency checks
  • Update migration-related troubleshooting

🔧 PHASE 6: Cleanup & Finalization

Database Cleanup

• REMOVE: Temporary migration columns from PostgreSQL

  ALTER TABLE object_instances DROP COLUMN migration_status;
  ALTER TABLE object_instances DROP COLUMN old_neo4j_id;
  

• REMOVE: Temporary properties from Neo4j nodes

  MATCH (o:ObjectInstance) 
  REMOVE o.old_id, o.migration_status;
  

Code Cleanup

• REMOVE: All commented-out dual-UUID code
• REMOVE: Migration feature flags and temporary code
• REMOVE: UUID translation helper functions
• UPDATE: Code comments to reflect unified ID system

Documentation Cleanup

• CREATE: architecture/unified-id-migration-retrospective.md
  • Document migration process and lessons learned
  • Performance impact analysis
  • Recommendations for future migrations
• ARCHIVE: Old migration guides and dual-UUID documentation
• UPDATE: All README files with current architecture

⚠️ Critical Migration Considerations

Data Safety

• NEVER delete old data until migration is 100% validated
• Maintain rollback capability at every step
• Test all procedures in staging environment first
• Have 24/7 monitoring during migration

Performance Impact

• Migration may require brief read-only periods
• Plan for increased database load during ID updates
• Monitor query performance before and after
• Have rollback plan if performance degrades

Application Compatibility

• Ensure frontend can handle unified ID format
• Validate all GraphQL operations work correctly
• Test mobile app compatibility if applicable
• Verify third-party integrations still work

📊 Success Criteria

Technical Validation

• Zero data loss during migration
• All objects have matching IDs in PostgreSQL and Neo4j
• No orphaned records in either database
• All relationships intact and functional
• Query performance maintained or improved

Functional Validation

• Object creation works with unified IDs
• All GraphQL operations function correctly
• Image management works properly
• User authentication and authorization unchanged
• Mobile app functionality unaffected

Documentation Validation

• All documentation accurately reflects unified ID system
• Schema verification process updated and working
• Troubleshooting guides current and accurate
• API examples all functional

---

🚨 CURRENT ACTION REQUIRED

Next Steps (in order):

1. Complete audit of current dual-UUID usage in codebase
2. Create detailed migration plan with specific implementation steps
3. Set up staging environment for migration testing
4. Develop and test migration scripts
5. Schedule migration execution during low-traffic period

The unified ID system will significantly simplify the architecture, but requires careful planning and execution to avoid data loss or system downtime.