Area 8: Notifications & Letters

Overview

Migration of notifications and letters from DTX 1.0 to DTX 2.0. This is Phase 4 — depends on Areas 2, 3 (Customers/Accounts, Vendors/Records).

DTX 1.0's notification system was built around a settings + template + sent-history architecture: notification stored per-customer timing schedules, notification_webhook mapped events to email recipient lists (PHP serialized), letter stored email templates with __PLACEHOLDER__ macros, messagequeue stored sent emails with full HTML bodies, and envelope tracked broker responses.

DTX 2.0 has a fully rebuilt notification system in the docutrax_utils app with a queue-based architecture: NotificationSchedule (account-scoped, per-type/per-sequence rows with cumulative timing), NotificationQueue (pending items processed by Celery tasks), EmailLog (delivery tracking), and EmailTemplate (reusable templates with {PLACEHOLDER} variables). Additionally, InAppNotification provides bell-icon notifications — a feature with no DTX 1.0 equivalent.

All target models exist. No missing models (unlike Area 9).

Source Tables (7)

Target Models

Supporting Infrastructure

Dependencies

Decisions

Decision #1: notification + notification_webhook → NotificationSchedule

This is the most complex migration in this area. DTX 1.0 stores notification settings as wide rows (one row per customer/type with ~20 timing columns). DTX 2.0 uses narrow rows (one row per account/type/sequence with a single days_after column).

DTX 1.0 structure: Each customer has 2 notification rows (type=COI and type=DOCUMENT). Each row contains columns for multiple reminder stages: - CR* = Certificate Request settings (first remind, second remind, non-response alert, restart) - CE* = Certificate Expiration settings (start-to-expire, first remind, expired, second remind, alert, restart) with Before/After timing - CNC* = Non-Compliance settings (first, second, alert, restart) - DR* = Document Request settings (first remind, second remind, non-response alert)

DTX 2.0 structure: Each account has multiple NotificationSchedule rows, one per (type, sequence) combination: - type: coi_request, coi_non_compliance, coi_expiration, document_request, document_non_compliance, document_expiration, coi_review, document_review - sequence: initial, reminder, final, alert, restart_notices, soon_to_expire, expired - days_after: cumulative days from T0 - email_recipients: JSON array (populated from notification_webhook)

Column mapping — COI notification row:

Column mapping — DOCUMENT notification row:

Email recipients from notification_webhook:

For each (customer_id, type, selector) in notification_webhook: 1. Deserialize PHP serialized webhook field → extract email array 2. Map selector to (type, sequence):

1. Store as JSON array in NotificationSchedule.email_recipients

Yield: Each DTX 1.0 customer produces up to ~30 NotificationSchedule rows (COI: 5 request + 6 expiration + 5 non-compliance = 16; DOCUMENT: 4 request + similar = ~14).

Additional fields: - notification.send_hold → NotificationSchedule.is_active (SEND=True, HOLD=False) - notification.user_id → store in ownership fields if needed - notification.create_time → use as creation timestamp - notification.customer_id → remap to NotificationSchedule.account FK via Area 2 mapping

Decision #2: notification_lookup → Drop

The notification_lookup table (12 columns) is a denormalized join table mapping customers to their notification configurations via coi_notification_id and doc_notification_id FKs, plus a broker_id reference.

Drop this table. DTX 2.0 links NotificationSchedule directly to Account via FK. The customer→notification relationship is implicit. The broker_id reference is irrelevant (Area 9 found no Broker model in DTX 2.0).

Data preserved: The coi_send_hold / doc_send_hold overrides and coi_dow_bitmap / doc_dow_bitmap day-of-week settings from this table are partially captured by Decision #1 (send_hold → is_active). The dow_bitmap values have no DTX 2.0 equivalent (see Decision #7).

Decision #3: notification_audit → Don't migrate

The notification_audit table (25 columns) records BEFORE/AFTER snapshots of notification setting changes. It has 3 fewer columns than the live notification table (missing CRRestartSetting, CERestartSetting, CNCRestartSetting), indicating schema drift — columns were added to notification without updating the audit table.

Don't migrate. This is historical audit data for a settings table that is being completely restructured (Decision #1). The old BEFORE/AFTER snapshots of DTX 1.0-format settings have no value in DTX 2.0's new architecture. DTX 2.0 tracks notification activity via EmailLog and NotificationQueue rather than settings-change auditing.

Keep CSV as archive for reference.

Decision #4: letter → EmailTemplate

The letter table (9 columns) stores customer-specific email templates with HTML bodies containing __PLACEHOLDER__ macros (e.g., __DATE__, __SO_NAME__, __PH_NAME__, __EXPIRING_POLICIES__).

Column mapping:

Placeholder macro remapping:

Approach: - Regex-replace all __([A-Z0-9_]+)__ patterns with {$1} in both subject and body_html. - Detect unique placeholders from body_html and populate the variables JSON list. - Set name = "{type} {selector}" (e.g., "COI EXPIRE_REMIND_2"). - Some templates contain Spanish text — set language='es' for those, or create bilingual variants.

Pre-migration action: Build a complete list of all __PLACEHOLDER__ macros used across all letter rows and verify each maps to a DTX 2.0 template variable supported by EmailNotificationService.get_record_context().

Decision #5: messagequeue → Don't migrate (archive)

The messagequeue table (15 columns) stores complete sent email records including full HTML bodies (letter_body), recipient addresses, send/read timestamps, and GUID-based tracking pixels.

Don't migrate. Rationale: - Structural mismatch: DTX 2.0's EmailLog tracks delivery metadata (schedule FK, delivery_status, context_data) — it does not store full HTML email bodies. The monolithic letter_body field (averaging ~57 lines of HTML per row) has no target column. - Historical data: These are records of emails already sent. They have no operational value in the new system. - Tracking infrastructure change: DTX 1.0 used GUID-based tracking pixels (risktoolbox.com/letters/messageread/{GUID}) and read_time tracking. DTX 2.0 uses delivery_status from the email provider (Mailgun). - Volume concern: The full messagequeue table likely contains thousands of rows, each with multi-KB HTML bodies.

Keep CSV as archive. If historical email audit is needed, it can be queried from the archived data.

Decision #6: envelope → No target model

The envelope table (9 columns) tracks broker/vendor responses to notification emails. When a vendor receives a compliance request email with a portal link, the envelope records their interaction: whether they accessed it, how many clicks, and the outcome (Completed, Declined, Voided). The vendor_data column contains PHP serialized contact information (doubly-serialized — the email field within is itself a PHP serialized array).

DTX 2.0 does not have an Envelope model. The DTX 2.0 system uses magic links (cryptographically signed URLs) for vendor document upload, tracked through the standard Document upload flow rather than a dedicated response-tracking table.

The team must decide: 1. Archive and discard — The envelope tracking was tightly coupled to the old notification system. DTX 2.0's magic link + Document upload flow replaces this functionality differently. 2. Create a ResponseTracking model — If historical response rates (Completed vs Declined vs Voided) are valuable for analytics or to benchmark the new system's vendor engagement rates. 3. Extract aggregate metrics only — Summarize response rates per customer/vendor and store as reference data without migrating individual envelope records.

Notable data in envelope: - event distribution: Voided (60%), Completed (36%), Declined (3%), NULL (1%) — shows most compliance requests expire without vendor action. - vendor_data: Contains vendor contact snapshots (name, address, email, phone). These are point-in-time copies, not canonical records.

Decision #7: send_hold and dow_bitmap capability gap

DTX 1.0 has two notification control settings that map imperfectly to DTX 2.0:

1. send_hold (SEND/HOLD): - notification.send_hold: Global toggle per customer/type - notification_lookup.coi_send_hold / doc_send_hold: Per-customer overrides - DTX 2.0 mapping: NotificationSchedule.is_active (True/False). Partial mapping — DTX 2.0 is per-schedule-row, not per-customer-type. A customer with send_hold=HOLD for COI would set is_active=False on all their COI-type schedules.

2. dow_bitmap (day-of-week): - notification.dow_bitmap: Bitmask for which days notifications can be sent (e.g., 32 = Friday only, 62 = Mon-Fri, 127 = every day) - DTX 2.0 has no equivalent. The Celery task process_notification_queue runs daily at 8 AM regardless of day-of-week. - Impact: Customers who restricted notifications to specific days will now receive them any day. - Options: - A. Accept the gap — process notifications daily (simplification) - B. Add a dow_bitmap or send_days field to NotificationSchedule to preserve per-customer day restrictions - C. Store in metadata/notes for manual configuration later

Decision #8: Deferred references from other areas

Known Issues

Pre-Migration Actions

Migration Order

Step 1: Migrate letter → EmailTemplate (templates needed before schedules reference them)
Step 2: Migrate notification + notification_webhook → NotificationSchedule (config with email recipients)
Step 3: Drop notification_lookup (no migration needed)
Step 4: Drop notification_audit (no migration needed)
Step 5: Archive messagequeue (no migration, keep CSV)
Step 6: Resolve envelope per team decision
Step 7: Bootstrap NotificationQueue and EmailLog (empty — populated by DTX 2.0 operations going forward)

Cross-Area References

Decision Summary