Area 11: File Repository & Misc

Overview

Migration of file repository and miscellaneous data from DTX 1.0 to DTX 2.0. This is Phase 5 — depends on Areas 2, 3 (Customers/Accounts, Vendors/Records).

This area covers 14 tables that span four functional domains: file repository management, billing/revenue tracking, custom field configuration, and client-specific integrations. Most tables in this area are either denormalized reporting snapshots, historical audit trails, or client-specific customizations with limited or no direct DTX 2.0 equivalents.

The most operationally significant item is the repo_file table — the file storage layer connecting DTX 1.0's on-disk EFS files to certificate and document records. Area 4 (Certificates & Documents) already defined the file migration strategy (Decision #5: "File Migration — EFS to S3"), including the 258 GB file repo partition. This area resolves the repo_file metadata and remaining file repo tables.

DTX 2.0 has fundamentally replaced several subsystems: billing is now Stripe-based (docutrax_billing app), activity tracking uses UserActivityLog, and exemptions/overrides use ManualOverride + WaiverRequest + is_exempted flags.

Source Tables (14)

Relevant Target Models

Dependencies

Decisions

Decision #1: repo_file → Shared file migration utility (Area 4 coordination)

The repo_file table (27 columns) is the file storage layer connecting on-disk files to certificate and document records. Area 4 already defined the complete file migration strategy (Decision #5: "File Migration — EFS to S3"), including:

• 258 GB of files in /efsdata/filerepo/ (the repo_file partition)
• 657.5 GB certificates + 1.3 TB documents + 200 MB logos = 2.2 TB total
• Content hashing (SHA-256), file_uid generation, batch processing
• A shared utility for both Area 4 and Area 11

Area 11's responsibility: Migrate remaining repo_file records that are NOT already linked to certificates or documents via Area 4's migration. These are "orphaned" or "unlinked" repo files — files uploaded but never processed into a certificate or document record.

Column mapping (for repo_file metadata not covered by Area 4):

Approach: 1. During Area 4 file migration, mark which repo_file_id values have been processed. 2. After Area 4 completes, identify remaining repo_file records (those with no certificate_id AND no document_id, or with status=DELETED). 3. For deleted files: do not migrate (they were intentionally removed). 4. For unlinked but non-deleted files: upload to S3, create a Document record with status='new' and flag for manual review.

Decision #2: repo_file_audit → Don't migrate

The repo_file_audit table (29 columns) records BEFORE/AFTER snapshots of repo_file state changes. It is a historical audit trail for file processing operations in DTX 1.0.

Don't migrate. DTX 2.0 tracks document/certificate changes through AuditHistory (evaluation snapshots) and UserActivityLog (HTTP request tracking). The old file-level audit trail has no operational value in the new system.

Keep CSV as archive.

Decision #3: repo_throughput → Don't migrate

The repo_throughput table (19 columns) is a denormalized reporting snapshot — a single batch-generated snapshot of file processing metrics per service owner. Column names contain special characters (#, %, spaces). No primary key.

Don't migrate. This is a computed cache, not source data. DTX 2.0 will calculate throughput metrics dynamically from actual Document, Certificate, and Evaluation data.

Keep CSV as archive for validation comparison.

Decision #4: imap_upload → No target model

The imap_upload table (10 columns) tracks files received via email-based IMAP ingestion — vendors/brokers emailing certificates to a monitored inbox. Each row records the sender email, filename, filesize, and links to a repo_file_id.

DTX 2.0 does not have an IMAP email ingestion system. Documents are uploaded via the web portal (drag-and-drop or magic links).

The team must decide: 1. Archive and discard — Email ingestion is a deprecated workflow. Vendors now upload via the web portal. 2. Build IMAP ingestion in DTX 2.0 — If email-based upload is a desired feature, create an IMapUpload model and processing pipeline. The DTX 1.0 data provides a useful reference for the schema. 3. Migrate as reference data — Store imap_upload records as metadata on the corresponding Document records (via repo_file_id → Document mapping) to preserve upload provenance.

Decision #5: form_field → No target model (partial mapping)

The form_field table (17 columns) stores custom form field definitions per customer/vendor. In the sample, all rows are for form_name=vendor_view, model_name=Vendorhistory, property_name=custom_field_0, with per-vendor value entries.

DTX 2.0 handles custom fields differently: - Record.internal_id and Record.internal_id1 — two custom text fields per Record - AccountMeta — key-value metadata per Account (flexible but unstructured) - Coverage models have hardcoded fields (not dynamic form fields)

The team must decide: 1. Map custom_field_0 values to Record.internal_id — If the custom field data is still needed per vendor, migrate form_field.value where property_name='custom_field_0' to Record.internal_id. 2. Store in AccountMeta — Migrate customer-level form field configurations as AccountMeta key-value pairs. 3. Archive and discard — If the custom form field system is replaced by DTX 2.0's fixed schema.

Note: The sample only shows one property_name. The full dataset likely has custom_field_1 through custom_field_4 (matching the keyword table's CUSTOM_FIELD_1 through CUSTOM_FIELD_4 labels). The full scope is unclear from sample data alone.

Pre-migration action: Query full DTX 1.0 for distinct form_name, model_name, property_name combinations to understand the full scope of custom fields.

Decision #6: keyword → No target model

The keyword table (3 columns) is a UI label customization system. Customers can rename standard labels — e.g., TRACKED_ITEM_NAME → "Tenants", GROUP_NAME → "Centers", CUSTOM_FIELD_1 → "Lease #".

DTX 2.0 does not have a keyword/label customization model. The UI uses fixed labels.

The team must decide: 1. Store in AccountMeta — Migrate keyword mappings as AccountMeta entries (key=keyword, value=replacement) so the DTX 2.0 UI can eventually support label customization. 2. Store in Directory.settings — Use the JSONField on Directory for per-tenant label overrides. 3. Archive and discard — If DTX 2.0 will use fixed labels for all customers.

Data size: Small — likely hundreds of rows total (6 keywords × number of customers who customized).

Decision #7: exemption → ManualOverride + DocRequirement.is_exempted

The exemption table (11 columns) records vendor-level exemptions from specific coverage or document requirements — e.g., "Sole proprietor" (WC exempt), "WC NOT MANDATORY IN TX", "waived". Each row has a vendor_id, item_type (COI/DOCUMENT), item_name (coverage line or document type), expiry date, and reason.

Column mapping:

item_name → coverage_type mapping (COI exemptions):

Sentinel date handling: - 3000-01-01 → expires_at = NULL (permanent/never expires) - 2050-* dates → expires_at = NULL (permanent exemption) - Normal dates → direct mapping

For DOCUMENT exemptions (item_type='DOCUMENT'): - Find the matching DocRequirement for the vendor's RequirementsGroup + document type - Set DocRequirement.is_exempted = True - Store reason in a note or metadata field

Pre-migration action: Build complete item_name → coverage_type mapping from full DTX 1.0 data. The sample shows ~10 unique values; the full dataset may have more.

Decision #8: reviewer_address → Verify against Area 2 AccountEmail

The reviewer_address table (6 columns) stores reviewer email addresses per customer with PHP serialized email_string fields. Area 2 already mapped customer.reviewer_email → AccountEmail(email_type='reviewer').

Verify and supplement: Compare reviewer_address data against the AccountEmail records created by Area 2. - If reviewer_address emails are already captured by customer.reviewer_email, no additional migration needed. - If reviewer_address contains different or additional email addresses, create additional AccountEmail(email_type='reviewer') records. - Deserialize PHP serialized email_string to extract email arrays.

Data quality note: type column is 100% empty (not NULL, just blank strings). level is always 0. These columns are effectively dead.

Decision #9: Billing tables (4) → Don't migrate

Four tables relate to DTX 1.0's billing/revenue system:

Don't migrate. DTX 2.0 has a completely different billing architecture: - Stripe integration: BillingProfile, Invoice, Payment, BillingEvent models in docutrax_billing app - Payment methods: stripe, wire, check, ach_direct, invoice, complimentary, sponsored - Plan tiers: starter, professional, enterprise - prepay_so_list → BillingProfile.payment_method (no need for a separate flag table)

The DTX 1.0 billing tables are denormalized snapshots of a legacy billing system that bears no structural resemblance to DTX 2.0's Stripe-based architecture. Historical billing data would need to be archived separately if needed for accounting/audit purposes.

Keep CSVs as archive for financial reference.

Decision #10: work_log → Don't migrate

The work_log table (5 columns) tracks user sessions per customer: user_id, session_id (SHA-1 hash), customer_id, start_time, end_time. No primary key. ~50% of rows have NULL end_time.

Don't migrate. DTX 2.0 has UserActivityLog (docutrax_utils/models.py) which tracks HTTP requests with user, URL, IP, user agent, and payload. This is a fundamentally different (and more detailed) activity tracking approach.

The old session-per-customer logs are historical operational data with no value in the new system.

Keep CSV as archive.

Decision #11: vornado_feed → No target model

The vornado_feed table (41 columns) is a client-specific data import for Vornado Realty Trust — importing tenant/lease data from their ABSINSQRY export. It includes property codes, tenant IDs, lease dates, insurance requirement flags, and processing status.

DTX 2.0 does not have a vornado_feed model or any client-specific data import tables. Area 1 also noted Vornado-specific rights (IDs 1000, 1009) that were dropped as "per-client hacks."

The team must decide: 1. Archive and discard — This was a custom integration for one client. If Vornado is still a customer in DTX 2.0, a new import mechanism should be designed. 2. Build a generic data import framework — Create a reusable CSV/data import system in DTX 2.0 that could handle Vornado-style feeds as well as other client integrations. 3. Migrate as seed data — If Vornado's tenant data needs to be in DTX 2.0, extract the tenant → Record mappings (via PH_ID → vendor mapping) and create Records with the relevant metadata.

Data quality note: Sentinel dates (12/31/2050 for perpetual leases), PH_ID = -1 for rejected records, typos ("reokacnebt" for "replacement", "Noncontributary" for "Noncontributory"), and 100% NULL contact fields.

Known Issues

Pre-Migration Actions

Migration Order

Step 1: Coordinate with Area 4 file migration utility (repo_file files → S3)
Step 2: Migrate exemption → ManualOverride + DocRequirement.is_exempted
Step 3: Verify reviewer_address against Area 2 AccountEmail (supplement if needed)
Step 4: Resolve form_field and keyword per team decisions
Step 5: Archive billing tables, work_log, repo_file_audit, repo_throughput (no migration)
Step 6: Resolve imap_upload and vornado_feed per team decisions

Cross-Area References

Decision Summary