Migration Strategy

This document outlines the migration strategies for database schema changes, including both planned migrations and completed ones.

Completed Migrations

HD Wallet Path Registry (July 2025)

Migration: Added path_registry and manufacturer_registry tables to PostgreSQL

Strategy:

  1. Created new tables with full constraints and triggers
  2. Pre-populated with Plings manufacturer registration
  3. Added base path allocations for generic tags
  4. No data migration needed (new feature)

Files:

  • /migrations/create_hd_wallet_path_registry.sql
  • See HD Wallet Schema for details

Planned Migrations

PlingsIdentifier Path Enhancement

Current State: PlingsIdentifier nodes in Neo4j without path information

Target State: Include path data for all identifiers

Migration Strategy:

  1. Phase 1: Add path property to new PlingsIdentifier nodes
  2. Phase 2: Backfill paths for existing identifiers based on creation order
  3. Phase 3: Make path required for all new identifiers
// Phase 2: Backfill example
MATCH (pi:PlingsIdentifier)
WHERE pi.path IS NULL
WITH pi
ORDER BY pi.created_at
WITH collect(pi) as identifiers
UNWIND range(0, size(identifiers)-1) as idx
WITH identifiers[idx] as pi, idx
SET pi.path = '1.1.' + toString(idx + 1)
RETURN count(pi) as updated

Organization-Specific Paths

Future Enhancement: Allow organizations to have dedicated path ranges

Migration Plan:

  1. Update path_registry to support organization-specific allocations
  2. Add RLS policies for organization isolation
  3. Migrate existing organization identifiers to new paths

Migration Best Practices

For PostgreSQL

  1. Always use transactions for schema changes
  2. Create indexes CONCURRENTLY in production
  3. Test migrations on a copy of production data
  4. Include rollback scripts with every migration

For Neo4j

  1. Use APOC procedures for bulk operations
  2. Create indexes before bulk imports
  3. Monitor query performance during migration
  4. Backup before major schema changes

Version Control

All migrations should be:

  • Named with timestamp prefix (e.g., 2025_07_11_add_path_registry.sql)
  • Include UP and DOWN migrations
  • Document the business reason
  • Reference related issues/PRs

Status: Updated July 2025 with HD wallet migration