DTX 1.0 → DTX 2.0 Migration Plan: Vendors & Records

Overview

Migration of vendor/record data from DTX 1.0 to DTX 2.0. This is Phase 3 — depends on Area 2 (Customers/Accounts). This is the largest and most complex single table migration — the DTX 1.0 vendor table has 65 fields and is a denormalized mega-table combining entity data, relationships, compliance status, and notification state.

Important: Full production data is not available. The so_summary table shows ~103,991 total vendor records in production. Migration scripts must handle this volume and be built defensively.

DTX 1.0 Source Tables

DTX 2.0 Target Models

Records Created Per Vendor

Each DTX 1.0 vendor row becomes 4-7 DTX 2.0 records:

1. Entity (name, proper_ph_name, entity_type='record')
2. Record (FK to Entity, tax_id, wc_exempt, compliance fields)
3. Node (ltree path under the vendor's customer/Account tree)
4. Assignment (Entity → Node, with contact/address overrides)
5. Address (if address fields populated)
6. Contact + ContactEmail (if contact fields populated)
7. EntityContact (links Contact to Entity)

Key Decision: Vendor Deduplication Strategy

Approach: Migrate 1:1, merge later. Do NOT deduplicate during migration.

Why deduplication during migration is a trap

In DTX 1.0, the same real-world vendor can appear as multiple rows — one per customer they work for. For example, "ABC Plumbing" working for 5 customers = 5 vendor rows.

DTX 2.0's Entity model is designed to be a single representation of a real-world thing. However, deduplicating during migration is too risky:

1. Fuzzy matching is error-prone — matching "ABC Plumbing" vs "ABC Plumbing Inc" vs "A.B.C. Plumbing LLC" risks merging different companies (irreversible data loss) or missing duplicates.

2. Per-customer data differs — even for the "same" vendor, each row has different contacts, addresses, requirements, compliance status, and notification state. All of this per-customer data is meaningful.

3. Migration complexity — the migration is already complex enough. Adding fuzzy matching multiplies risk for zero immediate benefit.

4. DTX 2.0 is designed for this — the Entity model docs explicitly state: "When Non-Docutrax end-users create new Entities, we will need a way to merge them with existing ones if they represent the same 'real world' thing." Merge is a planned future feature, not a migration concern.

Migration approach

Each vendor row becomes its own Entity + Record + Assignment:

DTX 1.0:  vendor row (vendor_id=6254, customer_id=713)
    ↓
DTX 2.0:
    Entity (name="Partners Advantage Insurance Services", entity_type='record')
    Record (FK to Entity, tax_id, wc_exempt, etc.)
    Node (under Account 713's hierarchy tree)
    Assignment (Entity → Node, with contact/address)

Same vendor appearing under 3 customers = 3 Entities, 3 Records, 3 Assignments under 3 different Account trees.

Post-migration deduplication (separate project)

Once data is safely migrated and verified, run a dedup pass using:

This can be done incrementally, account by account, with no migration risk.

Field Mapping

Vendor Identity → Entity

Vendor-Specific → Record

Contact Fields → Contact + EntityContact

Address Fields → Address

Compliance Status Fields

DTX 1.0 stores 12+ compliance status/date columns directly on the vendor row:

Decision: Store as metadata or compute from source data.

DTX 2.0 computes compliance status dynamically from certificates and documents rather than storing it as static fields on the vendor. Options:

Resolution: Option B — store as a JSON snapshot on Record for reference, but DTX 2.0 will recalculate compliance status from actual certificate/document data. This preserves history while ensuring 2.0 compliance is based on real data.

Notification/Ping Fields

Relationship Fields

Fields to Drop

Related Tables

vendor_status — Per-field compliance checks

Decision: Store as reference data. DTX 2.0 recalculates compliance from actual certificates/documents. This table is a historical record of past compliance checks. Store as a JSON snapshot or reference table for audit purposes.

vendor_coi_cache — Cached COI compliance data

Massive PHP-serialized blobs containing: - validation_data — all COI field values (policy numbers, limits, dates, carrier info) - waived_policies — policies with waived requirements - errors — compliance errors by status - policy_set — which certificates satisfy which policy types - compliant_policy_set — subset of compliant policies

Decision: Don't migrate. This is a computed cache derived from certificates and compliance rules. DTX 2.0 will rebuild this from migrated certificates + requirements. Migrating serialized PHP cache data into a different system's cache format provides no value.

vendor_display_cache — Cached display data

Similar to vendor_coi_cache but formatted for UI rendering. Contains PHP-serialized display objects.

Decision: Don't migrate. Same reasoning — computed cache, will be regenerated.

vendor_audit — Change audit trail

BEFORE/AFTER snapshots of vendor records.

Decision: Don't migrate. Historical audit data from old system. DTX 2.0 uses its own audit mechanisms. Keep CSV as archive.

vendor_create_time_audit — Creation timestamp audit

Simple audit of vendor_id, user_id, create_time, change_time.

Decision: Don't migrate. The create_time is preserved in Entity.created / Record timestamps. Keep CSV as archive.

so_summary — Global aggregate stats

Single row with global counts: total=103,991 vendors, compliant=53,781, non_compliant=50,210, etc.

Decision: Don't migrate. This is a dashboard cache that DTX 2.0 will compute dynamically. Useful only for validating migration counts post-migration.

Useful for: Verification — after migration, compare DTX 2.0 record counts against these numbers.

so_property — Customer key-value metadata

Decision: Migrate directly. These map to existing fields on the DTX 2.0 Account model. This is actually part of the Customer/Account migration (Area 2) but listed here since it's in the so_property table.

Migration: For each so_property row, look up the Account via customer_id mapping and set the corresponding field.

PHP Serialized Fields (same strategy as Area 2)

The following vendor fields use PHP serialize() format:

Parser handles: simple arrays, nested arrays, empty arrays, and malformed strings (log + skip).

Defensive Migration Strategy

Volume

Production has ~103,991 vendor records (per so_summary). At 4-7 DTX 2.0 records per vendor, this means ~400,000-700,000 records to create. Migration must be: - Batched — process in chunks of 1,000 to avoid memory issues - Transaction-wrapped — each batch in a transaction for rollback safety - Resumable — track progress so a failed run can resume from last successful batch

Dry-Run Report

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

Total vendors:           ???
Unique customer_ids:     ???
Customer IDs not in mapping: ??? (CRITICAL — these vendors can't be placed)

Status distribution:     COMPLIANT: ???, NON-COMPLIANT: ???, ALERT: ???, etc.
Active distribution:     yes: ???, no: ???

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

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

Requirements/doc set references:
  - Unique requirements_ids: ???
  - Unique document_set_ids: ???

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

Error Handling

Output Files

Migration Steps

1. Validate prerequisites:
   - Customer → Account ID mapping must exist and be verified
   - Account hierarchy (Nodes) must exist

2. Dry-run validation report

3. For each vendor (batched, 1000 at a time):
   a. Look up Account via customer_id mapping
   b. Create Entity (name=name1, entity_type='record')
   c. Create Record (FK to Entity, vendor-specific fields)
   d. Create Node under Account's hierarchy tree
   e. Create Assignment (Entity → Node)
   f. Create Address (if address fields populated)
   g. Create Contact + ContactEmail (if contact fields, parse PHP email)
   h. Create EntityContact (link Contact → Entity)
   i. Store compliance status as JSON snapshot on Record
   j. Record vendor_id → new IDs in mapping table

4. Migrate so_property → Account fields (Account Source, Industry Sector, Account Manager)

5. Verify:
   - Count check against so_summary totals
   - ID mapping completeness
   - Spot-check vendor data against originals

Dependencies

Decision Summary

Post-Migration: Deduplication Project

After migration is verified, run vendor deduplication as a separate project:

Merge operation: Keep one Entity, reassign all Assignments/Nodes to the surviving Entity, archive the duplicates.