DTX 1.0 → DTX 2.0 Migration Plan: Requirements & Compliance

Overview

Migration of requirements and compliance review data from DTX 1.0 to DTX 2.0. This is Phase 2 — depends on Accounts (Area 2). This area performs the COIRiskProfile backfill on RequirementsGroup records created in Area 4, and creates coverage template records that define what insurance is required.

The requirements table is the central piece: each row defines a complete set of coverage requirements (GL, Auto, WC, UMB, LL, attachment, AM Best) that gets mapped to a COIRiskProfile template with child coverage template records.

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 (5)

Target Models

Estimated Volume

Pre-Migration Actions Required

These must be resolved before running the migration.

Action 1: Map ATT Fields to DTX 2.0 DocumentType Records

Priority: HIGH — potential data loss

The requirements table has 8 attachment requirement fields (ATT_W9, ATT_COI, ATT_CG2015, ATT_CG2033, ATT_CG2037, ATT_Primary, ATT_HoldHarmless, ATT_SubrWvd) defining which documents a vendor must provide. These map to DocRequirement records in DTX 2.0, but need DocumentType FK targets.

Action: Verify that DTX 2.0 has DocumentType records for each of these:

If DocumentType records don't exist, create them as a seed data step. If the names differ, provide a mapping table.

Action 2: Confirm -1 Limit Semantics with DTX 2.0 Team

Priority: HIGH — affects all template coverage records

DTX 1.0 requirements use -1 extensively for limit fields. From sample data: - GLLimEaOcc="1000000" → "must have at least $1M each occurrence" - GLLimEaOcc="-1" → "coverage required, no minimum limit threshold" - GLLimEaOcc=NULL → "not checked / not applicable"

Plan assumes: -1 → store as NULL in template limit field (coverage type IS required because the template record exists, but no specific minimum). Actual dollar values stored as-is.

Action: Confirm with DTX 2.0 evaluation engine: 1. Does a NULL limit on a template coverage record mean "don't check this field"? 2. Or does it mean "any value passes"? 3. Is there a different sentinel value the engine expects?

Action 3: Verify Area 4 RequirementsGroup Mapping File

Priority: HIGH — backfill depends on this

Area 4 created RequirementsGroups from unique (customer_id, requirements_id, document_set_id) combinations. This area needs the mapping file to: 1. Look up which RequirementsGroups reference each requirements_id 2. Backfill RequirementsGroup.coi_risk_profile FK with the newly created COIRiskProfile

Action: Verify id_mapping_requirements_groups.csv exists and contains requirements_id → requirements_group_id associations.

Action 4: Resolve Area 5 user_defined_policy Template Records

Priority: MEDIUM

Area 5 deferred user_defined_policy template records (where certificate_id=NULL) to this area. These have requirements_id and customer_id and define custom coverage types required for a vendor.

Action: Confirm that Area 5's migration output includes a file listing deferred template records, or that the raw CSV is available for this area to process directly.

Known Issues & Risks

Critical

High

Medium

Low

Decisions

Decision #1: COIRiskProfile Creation from requirements

Risk Level: HIGH — central to this area

Each requirements record becomes one COIRiskProfile (is_template=True).

Migration approach

For each requirements record:
  1. Create COIRiskProfile:
     - account = customer_id → Account (via id_mapping_accounts.csv)
     - is_template = True
     - name = requirements.name
     - description = requirements.description (strip placeholder values)
     - created = requirements.create_time (preserve timestamp)
     - modified = requirements.update_time (if valid, else create_time)
  2. Record mapping: requirements_id → coi_risk_profile_id
  3. If customer_id not in Account mapping → skip, log to issues CSV

Deduplication

One COIRiskProfile per unique requirements_id. Multiple RequirementsGroups that reference the same requirements_id will share the same COIRiskProfile.

0000-00-00 00:00:00 handling

If update_time == "0000-00-00 00:00:00":
  modified = create_time  (fall back to creation time)
If create_time == "0000-00-00 00:00:00":
  created = migration_date  (should not happen — log if it does)

Decision #2: Coverage Template Records from requirements

Risk Level: HIGH — defines evaluation requirements

For each COIRiskProfile, create child coverage template records from the requirements fields. Only create a template record if the coverage section has at least one populated field.

Detection: "Is this coverage type required?"

GL required if: ANY of GLClaims, GLOccur, GLAddlInsd, ..., GLLimEaOcc, GLLimAgg, GLLimProd
                is non-NULL
Auto required if: ANY of AutoAddlInsd, AutoSubrWvd, ..., AutoSingle, AutoBodilyPerson
                  is non-NULL
WC required if: ANY of WCStatutory, WCSubrWvd, ..., WCEachAcc, WCDiseaseEachEmpl
                is non-NULL
UMB required if: ANY of UMBAddlInsd, UMBSubrWvd, ..., UMBLimEaOcc, UMBLimAgg
                 is non-NULL
LL required if: ANY of LLAddlInsd, LLSubrWvd, LLLimEaOcc, LLLimAgg, LLAddlInsdForms
                is non-NULL

Value mapping rules

Note: The difference between -1 → NULL (required, no minimum) and NULL → NULL (not checked) is that the TEMPLATE RECORD EXISTS for the first case but not the second.

Sample data patterns

Row 2295 (customer 630, "Cogent Communications"):
  GL: GLOccur=yes, GLAddlInsd=yes, GLSubrWvd=yes, GLLimEaOcc=1000000, GLLimAgg=2000000
  → Create GL template: occurrence=True, additional_insured=True, subrogation_waived=True,
    each_occurrence="1000000", general_aggregate="2000000", all others=NULL
  Auto: AutoSingle=2000000 (only populated field)
  → Create Auto template: single_limit_per_accident="2000000", all others=NULL/False
  WC: WCStatutory=yes, WCEachAcc=1000000
  → Create WC template: statutory_limits=True, disease_accident="1000000"
  UMB: UMBCombined=5000000
  → Create UMB template: umbcombined="5000000"

Row 2355 (customer 525, "Stewart") — richest record:
  GL: GLOccur=yes, GLAddlInsd=yes, GLSubrWvd=yes, GLLocation=yes, GLAddlInsdForms=yes,
      GLLimEaOcc=1000000, GLLimRented=300000, GLLimEaMed=5000, GLLimPerson=1000000,
      GLLimAgg=2000000, GLLimProd=2000000, GLPrimary=yes
  Auto: AutoSingle=-, but no booleans
  WC: WCStatutory=yes, WCEachAcc=1000000, WCDiseaseEachEmpl=1000000, WCDiseasePolicyLimit=1000000
  UMB: UMBSubrWvd=yes, UMBOccur=yes, UMBLimEaOcc=-, UMBCombined=1000000, UMBLimAgg=1000000
  LL: (all -1 or NULL)

Decision #3: Backfill RequirementsGroup.coi_risk_profile

Risk Level: HIGH — links templates to vendor assignments

Area 4 created RequirementsGroups with coi_risk_profile=NULL. This area fills them in.

For each RequirementsGroup in id_mapping_requirements_groups.csv:
  1. Look up the requirements_id associated with this group
  2. Look up the COIRiskProfile created from that requirements_id (Decision #1)
  3. Update RequirementsGroup.coi_risk_profile = found COIRiskProfile
  4. If requirements_id is NULL or has no matching COIRiskProfile → leave NULL, log

Important: This is a Django ORM UPDATE, not a CREATE. The RequirementsGroup already exists.

Decision #4: ATT Fields → DocRequirement Records

Risk Level: MEDIUM (pending Action #1)

The requirements table has 8 ATT_* fields defining document requirements. These map to DocRequirement records linking RequirementsGroups to DocumentTypes.

For each requirements record where ANY ATT field is truthy ("yes"):
  For each truthy ATT field:
    1. Look up the corresponding DocumentType (via Action #1 mapping)
    2. For EACH RequirementsGroup that references this requirements_id:
       Create DocRequirement:
         - requirements_group = the RequirementsGroup
         - document_type = mapped DocumentType
         - status = "In Review" (default — no historical status)
         - is_exempted = False

Sample data

Row 2355 ("Stewart") has: ATT_W9=yes, ATT_COI=yes, ATT_CG2015=yes (3 of 8 populated). Most other sample rows: all ATT fields NULL.

Decision #5: DescriptionOfOperations Templates

Risk Level: Low

Requirements OpsDesc field → DescriptionOfOperations template record.

For each requirements record where OpsDesc is truthy ("yes"):
  Create DescriptionOfOperations:
    - coi_risk_profile = mapped COIRiskProfile
    - certificate = NULL (template)
    - description_required = True
    - All other fields = NULL

Sample: OpsDesc appears to be NULL in most sample rows. The OpsDescReviewStatus field is dropped (review workflow not migrated, consistent with Area 5 Decision #6).

Decision #6: CarrierRating Templates from AMBestRatingOK

Risk Level: Low

Requirements AMBestRatingOK field → CarrierRating template record on the COIRiskProfile.

For each requirements record where AMBestRatingOK is truthy ("yes"):
  Create CarrierRating:
    - coi_risk_profile = mapped COIRiskProfile
    - certificate = NULL (template)
    - avii_or_above = True
    - All carrier fields = NULL (requirement-level, not carrier-specific)

Sample: Most rows have AMBestRatingOK=NULL. Some have truthy values. Consistent with Area 5 Decision #5 (certificate-level CarrierRating).

Decision #7: user_defined_policy Templates → Coverage Templates

Risk Level: MEDIUM

Area 5 deferred template records from user_defined_policy (where certificate_id=NULL). These define custom coverage types required by a customer.

For each user_defined_policy template record:
  1. Find or create the COIRiskProfile for this requirements_id
     (should already exist from Decision #1)
  2. Classify UDPType → PropertyCoverage or OtherCoverage
     (same keyword matching as Area 5 Decision #3)
  3. Find or create PropertyCoverageType / OtherCoverageType
     (case-insensitive dedup, reuse types created in Area 5)
  4. Create coverage template record:
     - coi_risk_profile = matched COIRiskProfile
     - certificate = NULL
     - type = found/created type
     - required = UDPRequired ("yes" → True)
     - loss_payee = UDPLossPayeeCB (PropertyCoverage only)
     - mortgagee = UDPMortgageeCB (PropertyCoverage only)
     - limit = UDPLimits (-1 → NULL, else direct)

Sample data

All 100 sample records are templates (certificate_id=NULL). Single customer 815. Three repeating UDPType values: "Professional Liability", "Cyber Liability", "Pollution Liability". All UDPLimits=-1.

Decision #8: requirements_audit — Don't Migrate

Risk Level: Low

BEFORE/AFTER snapshot audit of requirement changes. 83 columns per row.

Don't migrate. Rationale: 1. DTX 2.0 uses Django's built-in audit trail (django-simple-history or similar) for change tracking 2. Missing 6 columns vs live requirements table — audit is incomplete 3. Historical audit data (2014-2016 in samples) has limited operational value 4. Massive table (82 fields × N rows × 2 for BEFORE/AFTER) for minimal benefit

Keep CSV for reference. Log total record count and date range in migration report.

Decision #9: review_result — Don't Migrate

Risk Level: Low

Simple document review results: document_id → review_status (COMPLIANT/NON-COMPLIANT).

Don't migrate. Rationale: 1. Links to document_id — Area 4 already migrated documents with status 2. vendor_id is NULL in all samples — no vendor context 3. DTX 2.0 uses DocRequirement.status for document compliance tracking 4. Historical review snapshots (2017 samples) — reviews are complete 5. Comments are minimal ("coi" or NULL)

Keep CSV for reference. Cross-reference unique document_ids against Area 4's mapping if validation is needed.

Decision #10: coi_review_result — Don't Migrate (Parse for Waivers Only)

Risk Level: MEDIUM

COI review results with serialized PHP form_data. Contains per-coverage evaluation results that OVERLAP with Area 4's evaluation pipeline (built from certificate_status).

Don't migrate as primary data. Rationale: 1. Area 4 already created Evaluation → EvaluationCoverage → EvaluationDetail from certificate_status 2. Serialized PHP parsing is fragile and error-prone 3. Contains navigation state (coi_prev_page, coi_next_page) not needed in DTX 2.0 4. IP addresses are a privacy concern

However: form_data contains WAIVED statuses with reasons (e.g., ReviewStatus="WAIVED", Reason="gl ok"). This information is NOT in certificate_status.

Waiver extraction (optional post-migration enrichment):
  1. Parse form_data for each coi_review_result record
  2. For coverage sections with ReviewStatus="WAIVED":
     a. Log: certificate_id, coverage_type, Reason, user_id, create_time
     b. Output to waiver_candidates.csv
  3. DTX 2.0 team can review and create WaiverRequest records if needed

PHP serialized data structure observed

form_data = {
  "coi_prev_page": "Acord_25",
  "page_name": "Acord_25",
  "coi_next_page": "last_page",
  "Cert": {
    "starting_page": "Acord_25",
    "old_broker_id": "114147",
    "old_requirements_id": "8666",      ← links back to requirements table
    "old_document_set_id": "1219"       ← links to document_set
  },
  "GL": {
    "GL": {
      "DefinedType": "GL",
      "DeclaredType": "254240",          ← certificate_id
      "EvalStatus": "NON-COMPLIANT",
      "ReviewStatus": "WAIVED",          ← WAIVER INFORMATION
      "Reason": "gl ok"                  ← WAIVER REASON
    }
  },
  "Auto": { ... },
  "WC": { ... },
  "Other": {                             ← property/other coverage types
    "All Risk": {
      "DefinedType": "All Risk",
      "EvalStatus": "COMPLIANT",
      "PolicyLink": "(serialized carrier + policy info)"
    },
    "Business Income": { ... },
    "Business Personal Property (Content)": { ... }
  },
  "OpsDescReason": "",
  "HolderReason": "",
  "notice_action": "send_notice",
  "override_reason": ""
}

Decision #11: so_review — Don't Migrate

Risk Level: Low

Aggregated broker/SO compliance summary with counts (total, compliant, non_compliant, expired, etc.).

Don't migrate. Rationale: 1. This is a reporting/summary table, not transactional data 2. DTX 2.0 computes compliance metrics on-the-fly via ComplianceStatus model 3. Data inconsistency: some rows have total < compliant + non_compliant + expired 4. broker_id links to Area 9 (Brokers) — not yet migrated

Keep CSV for reference. Useful for validating post-migration compliance metrics.

Decision #12: ComplianceStatus and ComplianceThresholds — Create Empty

Risk Level: Low

These are DTX 2.0-native models with no DTX 1.0 equivalent.

ComplianceStatus:
  - Created per-RequirementsGroup AFTER all migration areas complete
  - Initial status = "no_requirements" (default)
  - Will be recalculated by DTX 2.0's evaluation engine on first run

ComplianceThresholds:
  - Created per-Account with defaults AFTER all migration areas complete
  - Uses DTX 2.0 default values (warning_days=30, stale_days=7, etc.)

Note: Do NOT create these during Area 6 migration. Create them in a post-migration "bootstrap" step after all areas are complete, or let DTX 2.0 create them lazily on first access.

Decision #13: requirements.comments — Store as COIRiskProfile.description Supplement

Risk Level: Low

The comments field contains operational notes like "No WC requirement in Temp License Agmt" and "NO EMPLOYEES!!!!". These are distinct from description.

If requirements.comments is non-NULL and non-whitespace:
  Append to COIRiskProfile.description:
    description = f"{clean_description}\n\nMigration note: {comments}".strip()

This preserves operational context without creating a new field.

Field Mapping

Key Design Concepts

Template vs Certificate Coverage Records

All coverage models have both coi_risk_profile and certificate FKs: - Template records (this area): coi_risk_profile set, certificate = NULL. Define REQUIREMENTS. - Certificate records (Area 5): certificate set, coi_risk_profile = NULL. Store ACTUAL VALUES.

The evaluation engine compares template records against certificate records.

-1 Semantics (Pending Action #2)

DTX 1.0 limit = "-1"   → DTX 2.0 template limit = NULL
  Meaning: "This coverage type is required (template record exists),
            but no specific dollar minimum."

DTX 1.0 limit = "1000000" → DTX 2.0 template limit = "1000000"
  Meaning: "Must have at least $1M."

DTX 1.0 limit = NULL    → No template record created for this field/type
  Meaning: "Not checked."

requirements → COIRiskProfile

requirements GL fields → GeneralLiability template

Create if ANY GL field is non-NULL. All *_options FKs = NULL. certificate = NULL.

requirements Auto fields → AutoLiability template

Create if ANY Auto field is non-NULL.

requirements WC fields → WorkersCompensation template

Create if ANY WC field is non-NULL.

requirements UMB fields → UmbrellaLiability template

Create if ANY UMB field is non-NULL.

requirements LL fields → LiquorLiability template

Create if ANY LL field is non-NULL.

Consolidated: Fields Dropped

Consolidated: DTX 2.0 Fields with No Source

Migration Execution

Migration Order (14 steps)

 1. Validate prerequisites:
    - id_mapping_accounts.csv exists and is complete
    - id_mapping_requirements_groups.csv exists with requirements_id associations
    - Pre-migration actions 1-4 resolved
    - Area 5's user_defined_policy template records available
 2. Dry-run validation report
 3. Create COIRiskProfiles from requirements (Decision #1)
 4. Create GL templates (Decision #2)
 5. Create Auto templates (Decision #2)
 6. Create WC templates (Decision #2)
 7. Create UMB templates (Decision #2)
 8. Create LL templates (Decision #2)
 9. Create user_defined_policy templates → PropertyCoverage/OtherCoverage (Decision #7)
10. Create CarrierRating templates (Decision #6)
11. Create DescriptionOfOperations templates (Decision #5)
12. Create DocRequirement records from ATT fields (Decision #4)
13. Backfill RequirementsGroup.coi_risk_profile (Decision #3)
14. Generate and export all ID mapping tables
15. Extract waiver candidates from coi_review_result (Decision #10, optional)
16. Verify

Dry-Run Report

=== DTX 1.0 Requirements & Compliance Migration - Dry Run Report ===

PREREQUISITES:
  id_mapping_accounts.csv:              ??? records loaded
  id_mapping_requirements_groups.csv:   ??? records loaded
  DocumentType records for ATT fields:  ??? of 8 present

REQUIREMENTS:
  Total records:                 ???
  Mapped to account:             ???
  Missing customer_id in mapping:???
  Unique customer_ids:           ???

  Timestamp issues:
    update_time = 0000-00-00:    ???
    create_time = 0000-00-00:    ???

  Description cleanup:
    NULL descriptions:           ???
    Placeholder (" ", "."):      ???
    With comments:               ???

COI RISK PROFILES TO CREATE:      ???

COVERAGE TEMPLATES:
  GL templates:                  ???
    GLLimEaOcc populated:        ???  (non-NULL, non-"-1")
    GLLimEaOcc = "-1":           ???  (required, no minimum)
    GLAddlInsd = "yes":          ???
    GLSubrWvd = "yes":           ???
    GLPrimary = "yes":           ???
    GLContLiab = "yes":          ???

  Auto templates:                ???
    AutoSingle populated:        ???
    AutoAny = "yes":             ???
    AutoHired = "yes":           ???
    AutoOther3LimitDesc populated: ???  (should be 0)

  WC templates:                  ???
    WCStatutory = "yes":         ???
    WCSubrWvd = "yes":           ???
    WCExec = "yes":              ???

  UMB templates:                 ???
    UMBLimEaOcc populated:       ???
    UMBMandatory populated:      ???  (should be 0)

  LL templates:                  ???
    LLLimEaOcc populated:        ???

  Total coverage templates:      ???

USER DEFINED POLICY TEMPLATES:
  Total deferred records:        ???
  Unique UDPType values:         [list all]
  Property-classified:           ???
  Other-classified:              ???

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

DESCRIPTION OF OPERATIONS:
  OpsDesc truthy:                ???
  OpsDescReviewStatus values:    [distribution]

ATTACHMENT REQUIREMENTS:
  ATT_W9:                        ???
  ATT_COI:                       ???
  ATT_CG2015:                    ???
  ATT_CG2033:                    ???
  ATT_CG2037:                    ???
  ATT_Primary:                   ???
  ATT_HoldHarmless:              ???
  ATT_SubrWvd:                   ???
  Total DocRequirement records:  ???

REQUIREMENTS GROUP BACKFILL:
  Groups with requirements_id:   ???
  Groups without requirements_id:???
  Unique requirements_id values: ???
  Groups that will get COIRiskProfile: ???

TABLES NOT MIGRATED (reference counts):
  requirements_audit:            ??? records, date range: ??? to ???
  review_result:                 ??? records, date range: ??? to ???
  coi_review_result:             ??? records, date range: ??? to ???
    WAIVED statuses found:       ???
  so_review:                     ??? records

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

Error Handling

Output Files

Verification (post-migration)

Dependencies

Difficulty Assessment

Overall: MEDIUM-HIGH

More complex than Area 5 due to the structural transformation (flat 89-column requirements → multiple models), cross-area backfill, and ATT → DocumentType mapping.

Decision Summary