DTX 1.0 → DTX 2.0 Migration Plan: Coverage & Insurance

Overview

Migration of insurance coverage data from DTX 1.0 to DTX 2.0. This is Phase 3 — depends on Accounts (Area 2) and Certificates (Area 4). This area also performs the Carrier A-F backfill on Certificate records deferred from Area 4 (Decision #16).

DTX 2.0 coverage models were designed with migration in mind — most fields map 1:1 with identical ACORD field names (lowercased). This is the most straightforward area since Area 2.

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

Source Tables (8)

Target Models (10)

Estimated Volume

Pre-Migration Actions Required

These must be resolved before running the migration.

Action 1: Fix llsamepot Typo in DTX 2.0 Model

Priority: HIGH — do this first

The LiquorLiability model has field llsamepot — should be llsamepol (same policy). This is a typo baked into the Django model. Fixing it after data migration requires a data migration too. Fix now via a Django schema migration while the table is empty.

# Current (wrong):
llsamepot = models.TextField(null=True, blank=True)
# Should be:
llsamepol = models.TextField(null=True, blank=True)

Action 2: Investigate DropdownOptions NULL Safety

Priority: HIGH — potential UI breakage

All coverage models have *_options FKs to DropdownOptions (e.g., general_aggregate_options, each_occurrence_options). Migration leaves all these NULL — there's no DTX 1.0 equivalent.

Risk: If DTX 2.0 frontend/API accesses coverage.general_aggregate_options.options without null-checking, every migrated coverage record will crash the UI.

Action: Review DTX 2.0 frontend code for null-safe access on all *_options FKs. If not null-safe, either: - Fix the frontend to handle NULL (preferred) - Create stub DropdownOptions records with empty option arrays

Action 3: Verify Evaluation Coverage Type Strings

Priority: HIGH — silent data disconnect

Area 4 created EvaluationCoverage records with coverage_type strings like "GeneralLiability", "AutoLiability", etc. (from certificate_status.field_name prefix mapping). DTX 2.0's evaluation engine presumably uses these strings to match coverage records to evaluations.

Action: Verify that DTX 2.0's evaluation engine expects exactly these strings. If it uses different casing, display names, or model-derived names, the evaluations created in Area 4 will disconnect from the coverage records created here.

Area 4 sets:     EvaluationCoverage.coverage_type = "GeneralLiability"
DTX 2.0 expects: ??? (must match)

Action 4: Confirm CarrierRating Model Intent

Priority: MEDIUM

Decision #5 creates CarrierRating records from certificate.AMBestRatingOK with NULL carrier info (name/naic/code). The model has per-carrier fields, suggesting it's designed for per-carrier rating tracking — not per-certificate flags.

Action: Confirm with DTX 2.0 team: - One CarrierRating per certificate (flag approach — current plan)? - Or one per unique carrier on the certificate, each with avii_or_above=True (per-carrier approach)?

Known Issues & Risks

Critical

High

Medium

Low

Decisions

Decision #1: Certificate FK Linking

Risk Level: Low

All coverage records use certificate FK (actual data). coi_risk_profile FK stays NULL (templates, Area 6). All *_options FKs stay NULL (UI config, not data — pending Pre-Migration Action #2).

For each coverage record:
  1. Look up certificate_id in id_mapping_certificates.csv
  2. If found → set certificate FK to mapped DTX 2.0 Certificate ID
  3. If not found → log to migration_issues.csv, skip record

Decision #2: Carrier A-F Backfill on Certificate

Risk Level: HIGH

Area 4 (Decision #16) left Certificate carrier_a_name through carrier_f_naic NULL. This area backfills them from coverage data.

CarrierCode availability from sample data

CarrierCode values "A" through "F" are ACORD insurer letter designations that directly map to Certificate slots.

Migration approach

For each migrated Certificate:
  1. Collect all coverage records linked to this certificate
     (across all 6 tables + user_defined_policy)

  2. Build NAIC → Slot mapping from records WITH CarrierCode:
     - LL record with CarrierCode="A", NAIC=14377 → slot A owns NAIC 14377
     - Other record with CarrierCode="C", NAIC=31194 → slot C owns NAIC 31194

  3. Assign carriers to slots:
     Phase A — Direct code assignment:
       Records with CarrierCode populated → assign to that slot
     Phase B — NAIC cross-reference:
       Records without CarrierCode but with NAIC matching a Phase A carrier
       → assign to the same slot
     Phase C — Remaining unassigned:
       Deduplicate by NAIC (when populated), then by normalized name
       → assign to next available slot A-F

  4. Normalize carrier names for dedup:
     - Strip leading/trailing whitespace
     - Collapse multiple spaces

  5. If >6 unique carriers → fill A-F, log overflow to migration_issues.csv

  6. Update Certificate record with populated carrier slots

Key insight: NAIC is more reliable than carrier name for dedup. Same carrier appears as "Continental Casualty Co." / "Continental Casualty Company" / "Continental Casualty Company " — all NAIC 20443.

Decision #3: other_coverage → OtherCoverage vs PropertyCoverage

Risk Level: Medium

DTX 1.0 has a single other_coverage table. DTX 2.0 splits into PropertyCoverage (with loss_payee, mortgagee) and OtherCoverage. The other_coverage table has no loss_payee/mortgagee fields, so routing is based on the Other1 type description.

Sample coverage types from Other1

Property-like:    "Extra Expense", "Equipment Floater", "Business Pers(onal Property)",
                  "Scheduled Equipment", "Contents", "Leased/Rented Equipment",
                  "Installation Floater", "Business Income", "Inland Marine"

Liability/Other:  "Professional Liability", "Professional-Claims Made",
                  "TECHNOLOGY E & O", "Crime Policy", "Crime", "EPLI",
                  "Fire Legal Liability", "Poll Legal Liab", "HNOA"

Pollution:        "Transportation Pollution-Occ", "Contractor Pollution-Occ",
                  "Professional/ Pollution Liab.", "Pollution-worksite"

Wrong-table:      "Commercial Umbrella" (should be umbrella_liability)

Data quality issues observed

• Carrier name truncated: "Business Pers " appears as both carrier name AND coverage type
• Placeholder NAICs: "99999" (e.g., "Grand Rapids" with NAIC 99999)
• Trailing spaces in names
• Mixed limit formats: "500000/1000000", "$2000,000 Limit Ded $2,500", "1000000 Limit Ded 2500"

Migration approach

1. Extract all unique Other1 values from full dataset
2. Normalize: strip whitespace, title case
3. Case-insensitive dedup (so "Equipment Floater" and "Equipment floater"
   become one type)
4. Classify each into "property" or "other" using keyword matching:
   Property keywords: property, equipment, floater, inland marine,
     business personal, building, contents, real property, business income,
     extra expense, boiler, machinery, scheduled, installation
   Everything else → OtherCoverage
5. Flag wrong-table types for post-migration review:
   Wrong-table keywords: umbrella, hired auto, non-owned auto, HNOA,
     workers comp, general liability
   These types are in other_coverage but logically belong in standard tables.
   DTX 2.0's evaluation engine may not find them.
6. Create PropertyCoverageType / OtherCoverageType (case-insensitive deduped)
7. Route each record to the appropriate model
8. Log all classifications to migration report for manual review

Fallback: Uncertain → OtherCoverage. Safer than misclassifying as PropertyCoverage.

Decision #4: user_defined_policy — Template vs Certificate Data

Risk Level: Medium

Dual-purpose table: - Template records: certificate_id=NULL, requirements_id set → Area 6 - Certificate records: certificate_id set → this area

Sample data observation

All 100 sample records are templates. Single customer (815), 3 UDPType values repeating: "Professional Liability", "Cyber Liability", "Pollution Liability". All have UDPLimits=-1, all other fields NULL. The certificate data path cannot be validated from sample data.

Migration approach

1. Split:
   a. Template records (certificate_id=NULL) → defer to Area 6, log count
   b. Certificate records (certificate_id set) → migrate here

2. For each certificate record:
   a. Route to target model:
      - If UDPLossPayeeCB or UDPMortgageeCB is truthy → PropertyCoverage
      - Else if UDPType matches property keywords (Decision #3 list) → PropertyCoverage
      - Otherwise → OtherCoverage
   b. Find or create type record (case-insensitive dedup)
   c. Create coverage record with certificate FK
   d. UDPLimits: -1 or NULL → set limit = NULL

3. Dry-run MUST log (since sample is 100% templates):
   - Count of template vs certificate records
   - Full value distribution for EVERY field in the certificate subset
   - All unique UDPType values with routing decisions

Decision #5: CarrierRating from certificate.AMBestRatingOK

Risk Level: Low (pending Pre-Migration Action #4)

Area 4 noted certificate.AMBestRatingOK maps here. Sample: cert 5307 has AMBestRatingOK="yes".

Open question: Is CarrierRating per-certificate or per-carrier? (See Pre-Migration Action #4)

Default approach (per-certificate, pending confirmation)

For each certificate where AMBestRatingOK is truthy ("yes"):
  1. Look up mapped Certificate ID
  2. Create CarrierRating:
     - certificate = mapped Certificate
     - avii_or_above = True
     - carrier_name/naic/code = NULL (certificate-level flag)
     - effective_date/expiration_date = NULL

Alternative approach (per-carrier, if confirmed)

For each certificate where AMBestRatingOK is truthy ("yes"):
  1. For each unique carrier from the Carrier A-F backfill data:
     Create CarrierRating:
       - certificate = mapped Certificate
       - avii_or_above = True
       - carrier_name = carrier's name
       - carrier_naic = carrier's NAIC

Decision #6: ReviewStatus / Reason Fields — Drop

Risk Level: Low

Every coverage table has *ReviewStatus and *Reason. These track DTX 1.0 review workflow results.

Drop all. Consistent with Area 4 Decision #11. Rationale: 1. Historical data — reviews are complete 2. DTX 2.0 uses a different evaluation system (Area 4's 3-tier pipeline) 3. certificate_status (migrated in Area 4) already captures compliance results 4. Redundant review state would conflict with new evaluation system

Sample values observed: NULL in most GL/Auto/WC/UMB samples. Liquor liability shows "COMPLIANT". Log all distributions in dry-run.

Decision #7: requirement_coverage Table — Don't Migrate

Risk Level: Low

Operational tracking table: "vendor X needs coverage Y, producer email is Z, from source W."

Sample: vendor 31739, email rstowe@farmersagent.com, type COI, name "All Risk – Business Personal Property (Contents)", source certificate 1100230.

Don't migrate. DTX 2.0 handles this via: - RequirementsGroup + COIRiskProfile → what's required - EvaluationCoverage + EvaluationDetail → what's met - NotificationSchedule → communication

Keep CSV for reference. The requirement_name values are useful for validating Area 6's requirements mapping.

Decision #8: DescriptionOfOperations — Defer to Area 6

Risk Level: Low

DTX 2.0's DescriptionOfOperations model tracks the requirement ("is ops description required?"), not the text itself. Area 4 already mapped certificate.OpsDesc → Certificate.ops_desc.

Defer. This is a requirement-level model (coi_risk_profile FK). Area 6 will create these when building COIRiskProfile templates.

Decision #9: WCExempt Cross-Reference

Risk Level: Low

• Area 3: vendor.WCExempt → Record.wc_exempt (vendor-level)
• Area 5: certificate.WCExempt → WorkersCompensation.any_excluded (coverage-level)

After migrating workers_comp records:
  For each certificate where WCExempt is truthy ("yes"):
    If WorkersCompensation record exists for this cert:
      → set any_excluded = True
    If no WC record exists:
      → no action (Record.wc_exempt already handles vendor-level exemption)

Decision #10: Coverage Limit Value Format

Risk Level: Low

DTX 1.0 limit formats observed:

Standard:     "1000000", "2000000", "500000"
Combined:     "500000/1000000"
Formatted:    "$2000,000 Limit Ded $2,500"
Special:      "-1" (user_defined_policy = no limit)
Suspicious:   "1000000000" (LL sample — $1B, likely data entry error)

Store raw values as-is. Don't transform or format. Map -1 → NULL.

Dry-run must flag suspicious values:

Limits exceeding thresholds:
  GL each_occurrence > $100M:  ???
  Auto single_limit > $50M:   ???
  WC disease limits > $10M:   ???
  LL each_occurrence > $100M:  ???
  UMB each_occurrence > $100M: ???

These are almost certainly data entry errors. Don't fix — flag for post-migration review.

Decision #11: Missing Target Fields

Risk Level: Low

Two DTX 1.0 fields have no direct DTX 2.0 target.

GLLimMisc (General Liability)

No gllimmisc field in DTX 2.0. NULL in all samples.

if GLLimMisc:
    gldescmisc = f"{GLDescMisc or ''} | Misc Limit: {GLLimMisc}".strip(' |')

Log all records where GLLimMisc is populated.

Other1Desc1 (Other Coverage)

No other1desc1 field in DTX 2.0. Contains labels like "Per Limit", "Each Claim/ Agg.", "Scheduled", "leased & rented".

# Store raw numeric value in both fields — do NOT concatenate description into limit field
limit = Other1Lim1      # primary limit value
other1lim1 = Other1Lim1 # raw backup

# Log Other1Desc1 for reference — do not store in a numeric field
# Dry-run: report all unique Other1Desc1 values

Why not concatenate: Storing "Per Limit: 5000000" in other1lim1 (a limit field) would break any downstream code that parses it as a number.

Decision #12: Timestamp Preservation

Risk Level: Medium

DTX 1.0 coverage tables have no create_time / update_time columns. Without intervention, all migrated coverage records get created = migration_date.

Migration approach

For each coverage record:
  1. Look up the parent Certificate's created timestamp
     (from id_mapping_certificates.csv or the Certificate object)
  2. Set coverage_record.created = certificate.created
  3. Set coverage_record.modified = certificate.modified
     (or migration_date if certificate.modified is NULL)

This preserves approximate historical timing. A coverage record created as part of certificate 5307 (created 2015-06-03) will show created = 2015-06-03, not created = 2026-XX-XX.

Field Mapping

Key Design Concepts

Dual FK Pattern

All coverage models have both coi_risk_profile and certificate FKs. For migration: certificate FK set, coi_risk_profile = NULL.

Coverage ↔ Evaluation Relationship

Coverage records (this area) and evaluations (Area 4) are complementary parallel structures linked to the same Certificate: - Coverage records: what was on the certificate (limits, carriers, dates) - Evaluations: whether it met requirements (pass/fail per field)

No direct FK between them. Linkage is by EvaluationCoverage.coverage_type string convention. (See Pre-Migration Action #3.)

Multiple Records Per Certificate

A single certificate typically has records in 3-5 different coverage tables. One table may also have multiple rows per certificate (e.g., cert 13957 has 3 auto policies). This is valid — one ACORD form can list multiple policies per coverage type.

Boolean Field Convention

General Liability: general_liability → GeneralLiability

Sample (row 40718): cert 7262, carrier QBE/General Casualty (NAIC 24414), policy CCX1207877, 2014-07-30 to 2015-07-30, limits: $1M occ / $100K rented / $1M person / $2M agg / $2M prod.

Automobile Liability: automobile_liability → AutoLiability

Sample (row 38978): cert 13957, carrier State Farm (NAIC 25178), scheduled auto, $1M single. Note: cert 13957 has 3 rows — multiple policies per cert is valid.

Workers Compensation: workers_comp → WorkersCompensation

Sample (row 34752): cert 11666, carrier State National (NAIC 12831), statutory: yes, $1M all limits.

Umbrella Liability: umbrella_liability → UmbrellaLiability

Sample (row 30139): cert 12843, Continental Casualty (NAIC 20443), umbrella/occurrence, $10M occ/$10M agg, retention $0.

Critical: UMBRetention holds dollar amounts ("0", "10000"), NOT booleans. UMBRetentionCB is the checkbox (NULL in all samples).

Liquor Liability: liquor_liability → LiquorLiability

Sample (row 12848): cert 333063, CarrierCode "A" → Selective Fire & Casualty (NAIC 14377), $1B limits (likely data entry error), ReviewStatus: COMPLIANT.

Data note: Only table with populated CarrierCode AND ReviewStatus in samples.

Other Coverage: other_coverage → OtherCoverage or PropertyCoverage

Routing per Decision #3.

How desc/limit pairs work (sample row 25187, type="Poll Legal Liab"):

Other1Desc1="Per Limit"    Other1Lim1="5000000"   → limit + other1lim1
Other1Desc2="Per aggregate" Other1Lim2="5000000"   → other1desc2 + other1lim2
Other1Desc3="SIR"           Other1Lim3="50000"     → other1desc3 + other1lim3

User Defined Policy: user_defined_policy → OtherCoverage or PropertyCoverage

Certificate records only. Routing per Decision #4.

Consolidated: Fields Dropped

Consolidated: DTX 2.0 Fields with No Source

Migration Execution

Migration Order (16 steps)

 1. Validate prerequisites:
    - id_mapping_certificates.csv exists and is complete
    - Pre-migration actions 1-4 resolved
 2. Dry-run validation report
 3. Create OtherCoverageType records (case-insensitive dedup from Other1 + UDPType)
 4. Create PropertyCoverageType records (property-classified types)
 5. Migrate general_liability → GeneralLiability (batched, 1000/batch)
 6. Migrate automobile_liability → AutoLiability (batched)
 7. Migrate workers_comp → WorkersCompensation (batched)
 8. Migrate umbrella_liability → UmbrellaLiability (batched)
 9. Migrate liquor_liability → LiquorLiability (batched)
10. Migrate other_coverage → OtherCoverage or PropertyCoverage (batched, classified)
11. Migrate user_defined_policy (certificate records only) → OtherCoverage or PropertyCoverage
12. Apply WCExempt flag to WorkersCompensation records (Decision #9)
13. Create CarrierRating records from certificate.AMBestRatingOK (Decision #5)
14. Backfill Certificate carrier_a through carrier_f (Decision #2)
15. Generate and export all ID mapping tables
16. Verify

Dry-Run Report

=== DTX 1.0 Coverage & Insurance Migration - Dry Run Report ===

PREREQUISITES:
  id_mapping_certificates.csv:    ??? records loaded
  Certificates skipped in Area 4: ???
  Coverage records orphaned by
    Area 4 skips:                 ??? (across ??? customers)

GENERAL LIABILITY:
  Total records:              ???
  Mapped to certificate:      ???
  Missing certificate_id:     ???
  CarrierCode populated:      ???
  Booleans: GLOccur=yes: ???, GLClaims=yes: ???, GLAddlInsd=yes: ???
  GLLimMisc populated:        ???
  Suspicious limits (>$100M): ???

AUTOMOBILE LIABILITY:
  Total records:              ???
  Multi-row certs (>1 row):   ???
  Auto types: Any: ???, Owned: ???, Hired: ???, NonOwned: ???, Sched: ???
  Suspicious limits (>$50M):  ???

WORKERS COMPENSATION:
  Total records:              ???
  WCStatutory=yes:            ???
  WCExec populated:           ???
  Suspicious limits (>$10M):  ???

UMBRELLA LIABILITY:
  Total records:              ???
  UMBLiability vs UMBExcess:  ???/???
  UMBRetention values:        min: ???, max: ???, avg: ???
  UMBRetentionCB populated:   ???
  Suspicious limits (>$100M): ???

LIQUOR LIABILITY:
  Total records:              ???
  LLNotApplicable populated:  ???
  CarrierCode distribution:   A: ???, B: ???, C: ???, ...
  ReviewStatus values:        [distribution]
  Suspicious limits (>$100M): ???

OTHER COVERAGE:
  Total records:              ???
  Unique Other1 types:        [list all with count]
  Classified as Property:     ??? types → ??? records
  Classified as Other:        ??? types → ??? records
  Wrong-table types flagged:  [list] (e.g., "Commercial Umbrella")
  Other1Desc1 unique values:  [list all]
  CarrierCode populated:      ???
  NAIC=99999 (placeholder):   ???
  Suspicious limits:          ???

USER DEFINED POLICY:
  Total records:              ???
  Template records (no cert): ???  → Area 6
  Certificate records:        ???  → this area
  Unique UDPType values:      [list all]
  With loss_payee:            ???
  With mortgagee:             ???
  [If cert records > 0: full field distribution for every field]

COVERAGE TYPE DEDUP:
  Raw unique names:           ???
  After normalization:        ??? (deduped by case/whitespace)
  OtherCoverageType to create: ???
  PropertyCoverageType to create: ???

CARRIER A-F BACKFILL:
  Certificates to update:     ???
  Avg carriers per cert:      ???
  Max carriers per cert:      ???
  Phase A (CarrierCode):      ??? assignments
  Phase B (NAIC cross-ref):   ??? assignments
  Phase C (remaining):        ??? assignments
  Certs with >6 carriers:     ??? (overflow)
  Carrier name variants
    (same NAIC, different name): [list]

CARRIER RATING:
  AMBestRatingOK=yes:         ???
  AMBestRatingOK=NULL/other:  ???

REQUIREMENT COVERAGE (reference only):
  Total records:              ???
  Unique requirement_names:   [list]

REVIEW STATUS (reference only, not migrated):
  GL:    [distribution]
  Auto:  [distribution]
  WC:    [distribution]
  UMB:   [distribution]
  LL:    [distribution]
  Other: [distribution]
  UDP:   [distribution]

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

Error Handling

Output Files

Verification (post-migration)

Dependencies

Difficulty Assessment

Overall: MEDIUM

Significantly easier than Areas 3 and 4. Most fields map 1:1 with identical names.

Decision Summary