DTX 1.0 → DTX 2.0 Migration Plan: Customers & Accounts

Overview

Migration of customer/account data from DTX 1.0 to DTX 2.0. This is Phase 1 — the foundation that everything else depends on.

Important: Full production data is not available. Migration scripts must be built defensively with self-validation, dry-run mode, and graceful error handling. All assumptions are based on a 100-row sample dataset.

DTX 1.0 Source Tables

DTX 2.0 Target Models

Records Created Per Customer

Each DTX 1.0 customer row becomes 3-5 DTX 2.0 records:

1. Entity (name, proper_ch_name, entity_type='account')
2. Account (all config fields, FK to Entity)
3. Node (ltree path, FK to Entity)
4. Directory (only for root-level customers, FK to root Account)
5. Address (if address fields populated)
6. Contact + ContactEmail (if contact fields populated)
7. EntityContact (links Contact to Entity)
8. AccountEmail (for each parsed email — COI, override, reviewer)

Field Mapping

Direct Matches → Account

Direct Matches → Entity

Contact Fields → Contact + EntityContact

Address Fields → Address

Email Fields → AccountEmail (PHP serialized)

Fields to Drop

Fields Requiring Downstream Migration

Decisions

Decision #1: parent_id → ltree Hierarchy

Risk Level: HIGH

DTX 1.0 uses parent_id for a simple FK tree. DTX 2.0 uses PostgreSQL ltree materialized paths via the Node model.

Sample data observation: All 100 sample records have parent_id=NULL (all roots). The full dataset may have children — we cannot assume flat structure.

Migration approach

1. Load all customers into memory
2. Build adjacency list from parent_id
3. Validate tree integrity:
   - Detect orphans (parent_id → non-existent customer_id)
   - Detect circular references
   - Report tree depth statistics
4. Topological sort (parents before children)
5. For each customer in sorted order:
   a. Create Entity
   b. Create Account (FK to Entity)
   c. If root (parent_id=NULL):
      - Create Directory
      - Create root Node with ltree path = generated_label
      - Set Directory.root_account = Account
   d. If child (parent_id set):
      - Look up parent's Node path
      - Create child Node with path = parent_path.child_label
   e. Create Address, Contact, AccountEmail records
   f. Record old_customer_id → new_account_id in mapping table

ltree label generation

Labels must be valid ltree labels (alphanumeric + underscore, no dots). Strategy: - Use account_{id} as the label (e.g., account_4094) - Path example: account_4094.account_5001.account_5002

Decision #2: PHP Serialized Email Parsing

Risk Level: Medium

DTX 1.0 email fields use PHP serialize() format:

// Simple
a:1:{i:0;s:21:"ar@teboproperties.com";}

// Nested
a:1:{i:0;a:1:{i:0;s:18:"mamon@docutrax.com";}}

// Empty
a:1:{i:0;s:0:"";}

Migration approach

# Use phpserialize library or write a simple parser
# For each email field:
#   1. Try to parse PHP serialized format
#   2. Extract all email strings
#   3. Validate each with email regex
#   4. Create AccountEmail/ContactEmail records
#   5. First valid email = is_primary=True
#   6. Log failures to migration_issues.csv

Fallback for unparseable values

• Try treating the raw string as a plain email address
• If still invalid, log to issues report and skip

Decision #3: name vs name1

Risk Level: Medium

Sample data shows:

name:  "12225 East 39th Ave."     ← property/location name
name1: "Tebo Properties"          ← company name

Approach: - name1 → Entity.name (this is the company/account name displayed in UI) - name → store as metadata or Entity.description if different from name1 - If name1 is NULL/empty, fall back to name - Flag records where name and name1 are both populated and different

Cannot fully validate without full dataset. The migration script will log all records where name != name1 for review.

Decision #4: Status Mapping

The active field ("yes" / NULL) appears redundant with status. Use status as the primary source. If status=ACTIVE but active != "yes", flag for review.

Decision #5: Directory Creation

Each root-level customer (parent_id=NULL) needs a Directory.

If division='docutrax', set directory_type='docutrax_managed'. Otherwise, may need mapping logic based on other fields.

Decision #6: customer_audit

Approach: Don't migrate

The customer_audit table contains BEFORE/AFTER snapshots of customer record changes. This is historical audit data from the old system.

DTX 2.0 uses Django's built-in audit mechanisms. Migrating old audit trails would require recreating the old schema context, which provides no value.

Resolution: Drop. Keep CSV as archive for compliance if needed.

Decision #7: currency Table

Approach: Use code directly, seed reference data separately

DTX 1.0 has a currency reference table with 100+ rows. DTX 2.0 stores currency_code as a CharField on Account (default 'USD').

Resolution: - Don't migrate the currency table as a model - Map each customer's currency setting to Account.currency_code - If DTX 2.0 needs a currency dropdown, seed a reference table independently (ISO 4217 standard data)

Decision #8: Logo/Image File Migration

Approach: Migrate files to S3, update Account.logo

DTX 1.0 stores logo_image as a file path (e.g., "schnippers.jpg"). These files need to be: 1. Located in the old file storage 2. Uploaded to DTX 2.0's S3 bucket using org_prefixed_upload_path('account_logos') 3. Account.logo field updated with the new S3 path

Dependency: Requires access to DTX 1.0 file storage. Logos are stored at /efsdata/logos/ (200 MB total). The S3 upload will use the shared file migration utility built in Area 4 (Decision #5). This area maps the Account.logo field; Area 4 handles the actual S3 upload.

Note: The so_property table contains key-value metadata (Account Source, Industry Sector, Account Manager) that maps to Account.account_source, Account.industry_sector, Account.account_manager. These fields will be populated during the Area 3 (Vendors) migration since so_property is listed under that area's table inventory.

Defensive Migration Strategy

Since full production data is not available, the migration script must handle unknowns gracefully.

Dry-Run Mode

Before any data is written, the script runs in dry-run mode and produces a validation report:

=== DTX 1.0 Customer Migration - Dry Run Report ===

Total records:           ???
Root customers:          ???
Child customers:         ???
Max tree depth:          ???

Status distribution:     ACTIVE: ???, INACTIVE: ???, other: ???
Type distribution:       R: ???, other: ???

PHP email parse results:
  - email field:         ??? success, ??? failed
  - override_notice:     ??? success, ??? failed
  - reviewer_email:      ??? success, ??? failed

Name analysis:
  - name == name1:       ???
  - name != name1:       ???
  - name1 is NULL:       ???

Tree integrity:
  - Orphan records:      ??? (parent_id → missing customer_id)
  - Circular references: ???
  - Max depth:           ???

Issues flagged:          ???
Ready to migrate:        ???

Error Handling

Output Files

Verification Steps (post-migration)

1. Count check: DTX 1.0 customer count == DTX 2.0 Account count (minus skipped)
2. ID mapping completeness: every customer_id used in other tables exists in mapping
3. Tree integrity: all Node paths are valid ltree, no orphans
4. Email check: spot-check parsed emails against originals
5. Name check: spot-check Entity.name values against original name1/name

Migration Order (within this area)

1. Validate and report (dry-run)
2. Create Address records (no dependencies)
3. Create Contact + ContactEmail records (no dependencies)
4. Create Entity records (links to Address, Contact via EntityContact)
5. Create Account records (FK to Entity)
6. Create Node records with ltree paths (FK to Entity, requires parent ordering)
7. Create Directory records (FK to root Account, only for roots)
8. Create AccountEmail records (FK to Account, from parsed PHP emails)
9. Generate and export ID mapping table
10. Verify

Dependencies

Decision Summary