DTX 1.0 → DTX 2.0 Migration Plan: Certificates & Documents

Overview

Migration of certificate and document data from DTX 1.0 to DTX 2.0. This is Phase 3 — depends on Accounts (Area 2) and Vendors/Records (Area 3), and feeds into Coverage/Insurance (Area 5) and Requirements (Area 6).

The DTX 1.0 certificate table is a massive denormalized table (69+ fields) containing the full ACORD certificate of insurance data — producer, insured, holder contacts, review workflow, and coverage references all in one row. DTX 2.0 splits this into a cleaner Certificate model with related coverage models and a 3-tier evaluation system.

Important: Full production data is not available. Migration scripts must be built defensively with dry-run mode, self-validation, and graceful error handling.

DTX 1.0 Source Tables

DTX 2.0 Target Models

Records Created Per Certificate

Each DTX 1.0 certificate row becomes 2-5 DTX 2.0 records:

1. Certificate (core fields, agent/insured/holder, carrier info)
2. Evaluation (from certificate_status rows → pass/fail result)
3. EvaluationCoverage (one per coverage type found in certificate_status)
4. EvaluationDetail (one per field_name in certificate_status)
5. AuditHistory (optional — snapshot for compliance)

Records Created Per Document

Each DTX 1.0 document row becomes 1-3 DTX 2.0 records:

1. Document (core fields, file, status)
2. DocumentRequirementFulfillment (links document to its DocRequirement)
3. AuditHistory (optional — snapshot for compliance)

Field Mapping

Certificate: Direct Matches

Certificate: Producer/Agent Fields

Certificate: Insured Fields

Certificate: Holder Fields

Certificate: FK References

Certificate: Fields to Drop

Document: Field Mapping

Decisions

Decision #1: RequirementsGroup Creation (Cross-Cutting)

Risk Level: HIGH

DTX 2.0 introduces RequirementsGroup as a central organizing concept that ties together: - COIRiskProfile (what insurance is required) - DocumentRiskProfile (what documents are required) - NamedEntity (who should be on certificates)

In DTX 1.0, these are separate FK references on different tables: - certificate.requirements_id → requirements table (Area 6 → COIRiskProfile) - certificate.document_set_id → document_set table (this area → DocumentRiskProfile) - No explicit named entity concept

Migration approach

1. Build a lookup of all unique (customer_id, requirements_id, document_set_id)
   combinations found in BOTH the certificate table AND the vendor table
   (vendors may have requirements without certificates)
2. For each unique combination:
   a. Look up the Account (from Area 2 mapping)
   b. Look up the COIRiskProfile (from Area 6 mapping — may be NULL initially)
   c. Look up the DocumentRiskProfile (from this area's document_set mapping)
   d. Create RequirementsGroup with these links
3. Record mapping: (customer_id, requirements_id, document_set_id) → requirements_group_id
4. When migrating certificates:
   - Look up the RequirementsGroup for each certificate's combination
   - Set Certificate.requirements_group = found group
5. When migrating vendor Assignments:
   - Create RequirementsGroupMember linking Assignment to RequirementsGroup

Note: RequirementsGroups that reference requirements_id will have coi_risk_profile=NULL until Area 6 migration fills them in. The migration script must support this phased linking.

Cross-area linkage: After creating RequirementsGroups, this step also creates RequirementsGroupMember records linking each vendor's Assignment (created in Area 3) to its RequirementsGroup. The lookup key is: vendor's (customer_id, requirements_id, document_set_id) → RequirementsGroup → RequirementsGroupMember(assignment=vendor's Assignment).

Decision #2: Certificate Status Mapping

Risk Level: Medium

DTX 1.0 has two status fields: - certificate_status (e.g., "FINISHED") — processing status - review_status — review workflow status

DTX 2.0 has:

class CertificateStatus(TextChoices):
    READY = 'ready'            # Parsing succeeded
    MANUAL_REVIEW = 'manual_review'  # Parsing failed, needs manual entry
    EVALUATED = 'evaluated'    # Has been evaluated

Mapping

All migrated certificates are historical data, so evaluated is the most appropriate status for any certificate that was fully processed in DTX 1.0. Use manual_review as fallback.

Cannot fully validate without full dataset. The migration script will log the distribution of certificate_status values.

Decision #3: Contact Field Consolidation

Risk Level: Low

DTX 1.0 has separate fields for each contact's phone, fax, email, extension. DTX 2.0 has fewer, wider text fields.

Producer/Agent

DTX 2.0 has only 4 agent fields (agent_name, agent_title, agent_contact, agent_address).

# agent_address = concatenated address
agent_address = f"{ProdAddr1}\n{ProdAddr2}\n{ProdCity}, {ProdState} {ProdZip}".strip()

# agent_contact = concatenated contact info
parts = []
if ProdContact: parts.append(f"Contact: {ProdContact}")
if ProdPhone:
    phone = ProdPhone
    if ProdPhoneExtension: phone += f" x{ProdPhoneExtension}"
    parts.append(f"Phone: {phone}")
if ProdFax: parts.append(f"Fax: {ProdFax}")
if ProdEmail: parts.append(f"Email: {ProdEmail}")
agent_contact = " | ".join(parts)

Insured

DTX 2.0 has separate address fields (insured_address1, city, state, zip) — direct mapping. Phone/fax/email packed into insured_contact:

parts = []
if InsContact: parts.append(f"Contact: {InsContact}")
if InsPhone:
    phone = InsPhone
    if InsPhoneExtension: phone += f" x{InsPhoneExtension}"
    parts.append(f"Phone: {phone}")
if InsFax: parts.append(f"Fax: {InsFax}")
if InsMobilePhone: parts.append(f"Mobile: {InsMobilePhone}")
if InsEmail: parts.append(f"Email: {InsEmail}")
insured_contact = " | ".join(parts)

Holder

DTX 2.0 has separate address fields — direct mapping. Holder contact info (phone, fax, email) has no direct target field. Store in Certificate.notes with a prefix:

holder_extras = []
if HolderContact: holder_extras.append(f"Contact: {HolderContact}")
if HolderTitle: holder_extras.append(f"Title: {HolderTitle}")
if HolderPhone: holder_extras.append(f"Phone: {HolderPhone}")
if HolderFax: holder_extras.append(f"Fax: {HolderFax}")
if HolderEmail: holder_extras.append(f"Email: {HolderEmail}")
if holder_extras:
    notes = f"[Migrated Holder Contact] {' | '.join(holder_extras)}"

Decision #4: certificate_status → Evaluation Pipeline

Risk Level: HIGH

DTX 1.0's certificate_status table stores per-field compliance checks:

certificate_id | status_code | field_name       | minimum    | actual
5215          | COMPLIANT   | UMBPolExpDate    | 2018-04-01 | 2019-04-04
5215          | COMPLIANT   | WCPolExpDate     | 2018-04-01 | 2019-06-28
5215          | COMPLIANT   | AutoPolExpDate   | 2018-04-01 | 2019-04-04
5215          | COMPLIANT   | GLPolExpDate     | 2018-04-01 | 2019-02-06

DTX 2.0 has a 3-tier evaluation structure:

Evaluation (overall: pass/fail/override/pending)
  └── EvaluationCoverage (per coverage type: GeneralLiability, Auto, etc.)
       └── EvaluationDetail (per requirement: limit amounts, dates, endorsements)

Migration approach

1. Group certificate_status rows by certificate_id
2. For each certificate's status rows:
   a. Create Evaluation:
      - certificate = migrated Certificate
      - requirements_group = Certificate's RequirementsGroup
      - status = 'pass' if ALL rows are COMPLIANT, else 'fail'
      - evaluation_date = min(create_time) of status rows
   b. Map field_name to coverage_type:
      - GLPolExpDate → "GeneralLiability"
      - AutoPolExpDate → "AutoLiability"
      - WCPolExpDate → "WorkersCompensation"
      - UMBPolExpDate → "UmbrellaLiability"
      - Other field_names → log and map based on prefix
   c. Create EvaluationCoverage per coverage_type:
      - coverage_type = mapped type
      - status = 'pass' if all rows for this type are COMPLIANT, else 'fail'
   d. Create EvaluationDetail per field_name:
      - evaluation_coverage = parent coverage
      - requirement_name = field_name (human-readable)
      - status = 'pass' if COMPLIANT, else 'fail'
      - required_value = minimum
      - provided_value = actual
      - field_key = field_name (raw)
   e. Set Certificate.current_evaluation = created Evaluation

field_name → coverage_type mapping

Cannot fully validate mapping without full dataset. The migration script will log all unique field_name values and their frequency.

Decision #5: File Migration — EFS to S3

Risk Level: CRITICAL

This is the single largest operational task in the entire migration. 2.5 TB of files need to move from DTX 1.0's EFS to DTX 2.0's S3.

Source: EFS Storage Architecture (from Docutrax EFS paths.docx and DTX 1.0 Data Sources and File Size & Count.xlsx)

Total: ~2.22 TB (excluding CloudWatch/SFTP)

Path Evolution (three formats — script must handle all three)

Files may exist under any of these path prefixes depending on when they were created:

Generation 1: /home/wwwriskt/public_html/attachments/...
Generation 2: /home/wwwriskt/public_html/www1/attachments/...
Generation 3: /efsdata/attachments/...

The repo_file table has the actual repo_filepath which tells us which generation each file uses. For attachment records without a full path, construct from the EFS pattern.

File Lookup Chain

repo_file.repo_filepath → actual file path (most reliable)
     ↓
repo_file.attachment_id → attachment table
     ↓
attachment.certificate_id OR attachment.document_id → target record

For files with a repo_file record, use repo_filepath directly. For files with only an attachment record, construct the path: - Certificate: /efsdata/attachments/certificate_management/<certificate_id>/<attachment.name> - Document: /efsdata/attachments/document_management/<customer_id>/<document_id>/<attachment.name>

Target: DTX 2.0 S3 Storage

DTX 2.0 uses org_prefixed_upload_path() with SHA-256 content hashing and file_uid (compound unique ID: {content_hash}_{timestamp}).

Migration approach

Phase 1: Inventory (dry-run)
  1. Scan all repo_file records — extract repo_filepath, verify file exists
  2. Scan all attachment records — construct path, verify file exists
  3. Report:
     - Total files found vs expected
     - Missing files (path exists in DB but file not on disk)
     - Orphan files (file on disk but no DB record)
     - Size distribution and total
     - Duplicate files (same content_hash)

Phase 2: Upload (batched, resumable)
  1. Process files in batches of 1000
  2. For each file:
     a. Read file from EFS
     b. Compute SHA-256 content_hash
     c. Generate file_uid: {content_hash}_{YYYYMMDDHHmmssffffff}
     d. Upload to S3 using org_prefixed_upload_path
     e. Record: old_path → S3_key → content_hash → file_uid
  3. Checkpoint after each batch (save progress to resume on failure)

Phase 3: Link
  1. For certificate files:
     - Set Certificate.file = S3 key
     - Set Certificate.original_filename = attachment.name
     - Set Certificate.content_hash = computed hash
     - Set Certificate.file_uid = generated UID
  2. For document files:
     - Set Document.file = S3 key
     - Set Document.original_filename = attachment.name
     - Set Document.content_hash = computed hash
     - Set Document.file_uid = generated UID
  3. For repo files (Area 11):
     - Store S3 key in mapping for Area 11 migration
  4. For logos (200 MB — `/efsdata/logos/`):
     - Upload to S3, set Account.logo = S3 key (from Area 2 mapping)
     - Note: Area 2 maps the field; this step does the actual S3 upload

Operational considerations

The repo_file table (cross-reference with Area 11)

The repo_file table is in Area 11 (File Repository & Misc) but is central to file migration. Key fields:

Decision: File migration (the S3 upload) should be a shared utility used by both Area 4 and Area 11. Area 4 migrates certificate and document files. Area 11 migrates remaining repo files. Both use the same upload/hash/UID logic.

Dependency: Requires read access to DTX 1.0 EFS mount. If EFS is not mountable, an alternative is aws s3 sync from the EFS volume's EC2 instance, then process from a staging S3 bucket.

Decision #6: Document Status Mapping

Risk Level: Medium

DTX 1.0 document.document_status appears to use numeric codes (sample shows 0). The document_status table uses string statuses like NOT_PROVIDED.

DTX 2.0 uses a detailed DocumentStatus enum with 13 values representing a document workflow.

Mapping

Cannot fully validate without full dataset. The migration script will log all unique document_status values (both from document.document_status and document_status.document_status) and their frequency.

DocRequirementStatus mapping

The document_status table's per-vendor status needs mapping:

Decision #7: document_library → DocumentType

Risk Level: Medium

DTX 1.0 document_library is per-customer (has customer_id). DTX 2.0 DocumentType is global (unique name across all accounts).

Challenge: Different customers may have document libraries with the same name but different purposes.

Migration approach

1. Extract all document_library entries
2. Deduplicate by name:
   - If name is unique across customers → create one DocumentType
   - If name is shared across customers with same meaning → create one DocumentType
   - If name is shared with different meanings → append customer identifier
     e.g., "W-9 Form" (shared) vs "Custom Policy Doc [Customer ABC]" (unique)
3. Record mapping: old document_library_id → new DocumentType.id
4. Use DocumentType to create DocRequirement entries

Dedup strategy: Start by assuming same name = same type (since document libraries represent generic document types like "W-9", "Certificate of Good Standing", etc.). Flag duplicates for post-migration review.

Decision #8: document_set → DocumentRiskProfile + DocRequirement

Risk Level: HIGH

DTX 1.0 uses: - document_set — named requirement sets per customer (e.g., "Standard - Policy and Endorsement (w/ UMB) - Construction") - document_requirements — junction linking sets to specific documents

DTX 2.0 uses: - DocumentRiskProfile — named collection of required document types per account - DocRequirement — links RequirementsGroup to DocumentType with a status

Structural difference: DTX 1.0 links sets to specific document instances. DTX 2.0 links profiles to document types (categories).

Migration approach

1. For each document_set:
   a. Create DocumentRiskProfile:
      - account = mapped Account (from customer_id)
      - name = document_set.name
      - description = document_set.description
   b. Find all documents linked via document_requirements junction
   c. For each linked document, get its document_library_id
   d. Map document_library_id → DocumentType (from Decision #7)
   e. Create DocumentRiskProfileDocumentType entries for each unique DocumentType
   f. Record mapping: old document_set_id → new DocumentRiskProfile.id
2. For each RequirementsGroup (from Decision #1):
   a. Link to the mapped DocumentRiskProfile
   b. For each DocumentType in the profile:
      - Create DocRequirement(requirements_group, document_type)
      - Set status based on document_status table data for this vendor/customer
3. For each document linked to a set:
   a. Find or create the DocRequirement for (RequirementsGroup, DocumentType)
   b. Create DocumentRequirementFulfillment(document, doc_requirement)

Decision #9: Document Hierarchy (parent_document_id)

Risk Level: Low

DTX 1.0 document has a parent_document_id self-reference for hierarchical grouping. DTX 2.0's Document model is flat — no parent reference.

Sample data observation: All 9 sample document records have parent_document_id=NULL. The full dataset may have children.

Migration approach

• Flatten the hierarchy. Migrate all documents as independent records.
• If parent_document_id is set, store the parent reference in Document.supervisor_notes as metadata: [Migrated] Parent document: {parent_document_name} (old_id: {parent_document_id})
• Log all parent-child relationships to migration_issues.csv for post-migration review.

Decision #10: Replacement Certificates (replacement_id)

Risk Level: Low

DTX 1.0 certificate has a replacement_id FK to another certificate, tracking when one certificate replaces another. DTX 2.0 has no explicit replacement chain — certificates belong to RequirementsGroups, and current_evaluation tracks the latest evaluation.

Migration approach

• Don't migrate the replacement chain. Migrate all certificates independently.
• The most recent certificate (by create_time) within a RequirementsGroup naturally becomes the current one.
• Set Certificate.current_evaluation only on the most recent evaluated certificate per RequirementsGroup.
• Log all replacement chains to the migration report for reference.

Decision #11: Review Workflow Fields

Risk Level: Low

DTX 1.0 has detailed review workflow tracking: - review_status, review_time, reviewer_id — general review state - OpsDescReviewStatus, OpsDescReason, OpsDescCorrectedBy — operations description review - HolderReviewStatus, HolderReason, HolderCorrectedBy — holder review - override_reason — override justification

DTX 2.0 has a completely different review workflow (DocumentStatus enum with 13 states, ManualOverride model, AuditHistory snapshots).

Migration approach

• Don't migrate review workflow state. All migrated certificates are historical — their review is complete.
• The override_reason field: if the certificate was overridden, create a ManualOverride record: python if certificate.override_reason: ManualOverride.objects.create( requirements_group=cert.requirements_group, coi_risk_profile=cert.requirements_group.coi_risk_profile, coverage_type='General', # may need refinement reason=certificate.override_reason, approved_at=certificate.review_time, )
• Otherwise, drop review workflow fields.

Decision #12: DocuSign Template References

Risk Level: Low

DTX 1.0 document has a docusign_template_id field. DTX 2.0 has no DocuSign integration visible in the models.

Migration approach

• Drop. The DocuSign integration is not carried forward to DTX 2.0.
• Log all documents with docusign_template_id set to migration report for reference.
• If DTX 2.0 adds DocuSign support later, the mapping can be rebuilt from the original CSV data.

Decision #13: missing_documents Table

Risk Level: Low

The missing_documents table is a denormalized reporting/cache table tracking missing document counts per vendor. DTX 2.0 calculates compliance status dynamically from DocRequirement statuses.

Migration approach

• Don't migrate. This is a cache table that DTX 2.0 will recalculate from actual DocRequirement and Document data.
• Keep CSV as reference for validation (compare DTX 2.0's calculated missing counts against DTX 1.0's cached values).

Decision #14: certificate_queue Table

Risk Level: Low

The certificate_queue table is a processing queue with GUIDs for distributed processing. It represents work-in-progress, not historical data.

Migration approach

• Don't migrate. Processing queues are transient. Any unprocessed items would need to be reprocessed through DTX 2.0's pipeline anyway.
• Log queue contents to migration report for reference (in case any stale items need manual attention).

Decision #15: InsName vs InsName1

Risk Level: Low

DTX 1.0 certificate has both InsName (col 37) and InsName1 (col 38). On the ACORD form, InsName is typically the primary insured name and InsName1 is a DBA or second named insured line.

Migration approach

if InsName and InsName1 and InsName != InsName1:
    insured_name = f"{InsName} / {InsName1}"  # Combine both
elif InsName:
    insured_name = InsName
elif InsName1:
    insured_name = InsName1
else:
    insured_name = None  # Flag for review

Log all records where both are populated and different.

Decision #16: Carrier A-F Backfill (Area 5 Dependency)

Risk Level: Medium

DTX 2.0 Certificate has carrier_a_name through carrier_f_naic (12 fields) for the ACORD form's INSURER A-F section. However, DTX 1.0's certificate table has NO carrier columns — carrier/insurer information lives in the coverage tables (general_liability, automobile_liability, workers_comp, etc.) which are Area 5.

Migration approach

• Leave carrier A-F fields NULL during this area's migration.
• After Area 5 (Coverage) migration:
• For each Certificate, collect all coverage records linked to it
• Extract unique (carrier_name, carrier_naic) pairs from those coverages
• Assign them to carrier_a through carrier_f in order
• Update the Certificate record
• This is a backfill step that runs at the end of Area 5.

Coverage model → Certificate linkage

Each DTX 2.0 coverage model (GeneralLiability, AutoLiability, etc.) has certificate = ForeignKey('Certificate'). During Area 5 migration, the coverage tables' data needs to link to Certificate records created here. Area 5 will consume the id_mapping_certificates.csv to establish these links.

Difficulty Assessment

Overall: HARD (hardest area so far)

This area has more structural transformation than any previous area. Areas 1-3 were mostly field-level mappings with occasional data parsing. Area 4 involves architectural changes — flat tables becoming hierarchical structures, instance-based references becoming type-based, and a new cross-cutting RequirementsGroup concept.

What makes it hard

What makes it doable

1. DTX 2.0 models are well-designed — the target schema is clean and consistent
2. Most certificate fields map directly — agent, insured, holder fields have clear targets
3. The 5 "don't migrate" decisions reduce scope significantly (certificate_queue, missing_documents, review workflow, DocuSign, replacement chains)
4. Defensive strategy handles unknowns — dry-run mode catches problems before they become data corruption
5. Phased linking works — NULL FKs during migration, backfill later, is a proven pattern

Estimated volume (actual storage sizes from DTX 1.0 Data Sources and File Size & Count.xlsx)

Risk summary

Defensive Migration Strategy

Dry-Run Mode

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

=== DTX 1.0 Certificates & Documents Migration - Dry Run Report ===

Certificates:
  Total records:              ???
  Status distribution:        FINISHED: ???, PENDING: ???, other: ???
  With attachment:            ???
  Without attachment:         ???
  With replacement_id:        ???
  With requirements_id:       ???
  With document_set_id:       ???

Certificate Status (compliance checks):
  Total rows:                 ???
  Unique certificates:        ???
  Unique field_names:         [list all]
  COMPLIANT count:            ???
  Non-COMPLIANT count:        ???

Documents:
  Total records:              ???
  Status distribution:        0: ???, other: ???
  With attachment:            ???
  With parent_document_id:    ???
  With docusign_template_id:  ???

Document Libraries:
  Total entries:              ???
  Unique names:               ???
  Duplicate names:            ???

Document Sets:
  Total entries:              ???
  Requirements per set:       min: ???, max: ???, avg: ???

Attachments:
  Total files:                ???
  With certificate_id:        ???
  With document_id:           ???
  Total file size:            ??? GB
  File types:                 [distribution]

RequirementsGroups to create:
  Unique (customer, requirements, doc_set) combos: ???

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

Error Handling

Output Files

Verification Steps (post-migration)

1. Count check: DTX 1.0 certificate count == DTX 2.0 Certificate count (minus skipped)
2. Count check: DTX 1.0 document count == DTX 2.0 Document count (minus skipped)
3. Evaluation check: every certificate with certificate_status rows has an Evaluation
4. File check: every Certificate/Document with an attachment has a file in S3
5. RequirementsGroup check: every Certificate has a RequirementsGroup
6. DocRequirement check: every Document has a DocRequirement or is flagged as orphaned
7. ID mapping completeness: every certificate_id and document_id used in other tables exists in mapping
8. Spot-check: compare 10 random certificates' field values against originals

Migration Order (within this area)

1. Validate and report (dry-run)
2. Create DocumentType records (from document_library — no dependencies)
3. Create DocumentRiskProfile + DocumentRiskProfileDocumentType records (from document_set)
4. Create RequirementsGroup records (from unique certificate combinations)
5. Migrate attachment files to S3 (parallel, batch)
6. Create Certificate records (with file references, agent/insured/holder mapping)
7. Create Evaluation + EvaluationCoverage + EvaluationDetail records (from certificate_status)
8. Set Certificate.current_evaluation on latest certificates
9. Create Document records (with file references)
10. Create DocRequirement records (from document_requirements + DocumentType mapping)
11. Create DocumentRequirementFulfillment records (link documents to requirements)
12. Create AuditHistory snapshots (optional — for compliance trail)
13. Generate and export all ID mapping tables
14. Verify

Dependencies

Decision Summary