Area 10: Reporting

Overview

Migration of reporting system from DTX 1.0 to DTX 2.0. This is Phase 5 — depends on all previous areas.

DTX 1.0 has a full-featured reporting subsystem: a dynamic report builder using SQL fragment templates (report_field_definition), composable report definitions (report, report_definition), a 93-column denormalized materialized view (report_data), and scheduled report delivery with email distribution (report_master_schedule).

DTX 2.0 has no reporting models. There are no Report, ReportDefinition, ReportCategory, ReportData, ReportSchedule, or Dashboard models anywhere in the codebase. The only export functionality that exists is: - Manual Excel export for Records and Accounts (docutrax_hierarchy/export_utils.py) - Payment history CSV/PDF export (docutrax_billing/views.py) - Real-time compliance status calculations (no materialized views)

This means all 8 tables in this area have no direct DTX 2.0 target model. The team must decide the reporting strategy for DTX 2.0 before any data migration can occur.

Source Tables (8)

Existing DTX 2.0 Export Capabilities

Dependencies

DTX 1.0 Reporting Architecture

The reporting system has three distinct layers:

Layer 1: Configuration (what reports exist)

report_category (9 rows, 3 columns) — Simple lookup:

report (100 rows, 15 columns) — Report templates: - Two tiers: system reports (customer_id=0, internal_name like "COI-Expired", "ACCT-Master") and custom reports (customer-specific, no internal_name). - Prefix convention: ACCT-*, COI-*, DOC-*, MAN-*, SRS-* (scheduled). - Format flags: csv_format (always 1), pdf_format (rarely 1). - industry_sector: Only "Construction" appears; most are NULL.

report_field_definition (100+ rows, 14 columns) — Data dictionary with SQL fragments: - Defines every available report field with: category, name, label, description, display_format, and raw SQL expressions (select_expression, table_name, table_join). - SQL uses template placeholders: __CUSTOMER_SELECTOR_FROM__, __CUSTOMER_SELECTOR_WHERE__, __NUM_DAYS_COMPLIANT__, __BETWEEN_INCLUSION__. - Field categories: Account Profile, Account Data, Account Data - Ping, Broker Profile, Carrier, Client Profile. - These SQL fragments reference DTX 1.0 table and column names. They cannot be used directly in DTX 2.0.

report_definition (100+ rows, 4 columns) — Junction table mapping report_id × report_field_id with ordering. Defines which fields appear in each report and in what column order.

Layer 2: Data (what reports query)

report_data (100+ rows, 93 columns) — Denormalized materialized view: - Flattens the full hierarchy: SO (Service Org) → CH (Client/Certificate Holder) → PH (Policyholder/Vendor) - Contains every compliance status, alert date, custom field, boolean flag, and count pre-computed for fast reporting. - Key column groups: - SO fields (7): ID, Name, Status, Industry, Account Manager - CH fields (6): ID, Name, Status, Create/Inactivated times - PH fields (60+): ID, Name, all status timestamps, COI compliance, DOC compliance, alert dates, boolean flags, custom fields 1–15, counts - Sentinel values: PH_DOC_RiskProfile_ID = -1 (no profile assigned) - Mixed boolean encoding: "yes"/"no" strings AND "1"/"0" integers in different columns

Layer 3: Scheduling & Audit (when reports run)

report_master_schedule (100 rows, 16 columns) — Scheduled report delivery: - recurrence: monthly_recurrence, daily_recurrence, weekly_recurrence, does_not_repeat_recurrence, or empty - tod: Time of day (hour, 0–13) - dow_bitmap: Day of week bitmask (same encoding as notification dow_bitmap) - dom: Day of month (for monthly) - email: PHP serialized email recipient arrays - do_not_send_email: PHP serialized exclusion lists - start_date: Many rows have 0000-00-00 (MySQL zero-date)

report_master_log (100 rows, 8 columns) — Manual execution audit: - Tracks every report run: user_id, customer_id, ip_address, timestamp - scheduled flag (always 0 in sample — all manual runs)

report_master_schedule_log (100 rows, 5 columns) — Scheduled execution audit: - Tracks scheduled report deliveries: schedule_id, create_time, sent (0/1), sent_time - Early entries show failures (sent=0), later entries show success — suggests system startup issues

Decisions

Decision #1: Reporting system architecture — Team decision required

DTX 2.0 has no reporting models today. The team must decide the reporting strategy before any migration can occur. The options are:

This decision blocks all other decisions in this area.

Decision #2: report + report_category → Report templates

The report table (15 columns) contains report templates — both system-wide templates (categories 1–4, 7) and customer-specific custom reports (category 5).

If Option A (build reporting system): - Migrate system reports (customer_id=0) as seed data for the new system. - Custom reports (customer_id≠0) need customer_id → Account remapping. - report_category (9 rows) can serve as seed data for report categorization. - report_internal_name prefix convention (ACCT-/COI-/DOC-/MAN-/SRS-) provides a natural taxonomy.

If Option B/C/D: - Archive for reference. Report definitions would be recreated natively in the chosen tool/framework.

Decision #3: report_field_definition + report_definition → Report builder configuration

The report_field_definition table (14 columns) is the data dictionary powering the dynamic report builder. Each row defines a reportable field with its SQL expression, display format, and category. The report_definition junction table maps which fields appear in each report.

Critical limitation: The SQL fragments in select_expression, table_name, and table_join reference DTX 1.0 table names (rd.PH_Name, v.address_1, __CUSTOMER_SELECTOR_FROM__). These are not portable to DTX 2.0's Django ORM schema.

If Option A (build reporting system): - The field metadata (name, label, description, display_format, category) is valuable and should inform the new report field definitions. - The SQL fragments must be completely rewritten against DTX 2.0's schema. - The report_definition junction data shows which fields users actually used in their reports — useful for prioritizing which fields to implement first.

If Option B (BI tool): - Use field metadata as a requirements spec for BI tool configuration. - SQL fragments are irrelevant (BI tool generates its own queries).

If Option C/D: - Archive for reference.

Decision #4: report_data → Don't migrate (materialized view)

The report_data table (93 columns) is a denormalized materialized view — a pre-computed snapshot of the entire hierarchy with all compliance statuses, alert dates, boolean flags, and custom fields flattened into a single wide table. It is refreshed periodically (batch create_time timestamps visible in sample).

Don't migrate regardless of architecture decision. This is a computed cache, not source data. All the underlying data is being migrated through Areas 2–9. DTX 2.0 should either: - Query source tables directly (with proper indexes) - Build its own materialized views from DTX 2.0 models - Use BI tool caching/aggregation

The 93-column schema is useful as a requirements document — it shows exactly what data points the reporting system needs access to.

Decision #5: report_master_schedule → Scheduled report delivery

The report_master_schedule table (16 columns) defines scheduled report delivery: recurrence pattern, time of day, day of week/month, and email recipients (PHP serialized).

If Option A (build reporting system): - Migrate schedule configurations with: - recurrence → new schedule model recurrence field - tod / dow_bitmap / dom → timing fields - email → deserialize PHP arrays to JSON email lists - report_id → remap to new report IDs - customer_id → remap to Account - start_date → handle 0000-00-00 zero-dates (map to NULL or migration date) - do_not_send_email → deserialize or drop (exclusion lists)

If Option B/C/D: - Archive. Scheduled delivery would be configured natively in the BI tool or deferred.

Data quality notes: - PHP serialized email and do_not_send_email columns need deserialization. - Many start_date values are 0000-00-00 (MySQL zero-date sentinel). - Some rows have empty recurrence fields (likely one-time or manual triggers). - Bulk update timestamps (2025-07-09, 2023-06-29) suggest batch migrations touched many schedules.

Decision #6: report_master_log + report_master_schedule_log → Don't migrate

report_master_log (8 columns) — Audit trail of manual report executions (who ran which report, when, from where).

report_master_schedule_log (5 columns) — Execution log of scheduled report deliveries (was it sent successfully, when).

Don't migrate. These are historical operational logs with no value in a new reporting system. They document DTX 1.0 usage patterns, not data that needs to be preserved.

Keep CSVs as archive. The log data could be useful for understanding which reports were most used (to prioritize DTX 2.0 report implementation).

Usage insights from the logs: - Most popular reports: report_id 1 (Account Master), 55 (Link Customer Compliance), 127 (DOC-Not in Compliance), 135 (COI-Not in Compliance) - Execution pattern: Heavy manual usage during US business hours; overnight scheduled runs - Scheduled report failures in early Jan 2023 suggest system startup issues (resolved within 2 days)

Decision #7: Client-specific report rights from Area 1

Area 1 (Users & Auth) identified and dropped several client-specific report rights:

Confirmed: All client-specific report rights were correctly dropped in Area 1. These were per-client customizations baked into the rights system. DTX 2.0's permission model (Permission + UserPermission) handles access control at a more granular level.

If DTX 2.0 builds a reporting system, report access should be controlled via the standard permission system (resource='reports', action='read'/'manage') rather than per-client right IDs.

Decision #8: Deferred references from other areas

Known Issues

Pre-Migration Actions

Migration Order

Step 0: Team decides reporting architecture (Decision #1) — BLOCKING
         ↓
    Option A: Build DTX 2.0 reporting
         Step 1: Create new Report/ReportSchedule models
         Step 2: Migrate report + report_category as seed data
         Step 3: Rewrite report_field_definition against DTX 2.0 schema
         Step 4: Migrate report_definition (field ordering)
         Step 5: Migrate report_master_schedule (with PHP deserialize)
         Step 6: Skip report_data, report_master_log, report_master_schedule_log

    Option B: External BI tool
         Step 1: Configure BI tool against DTX 2.0 PostgreSQL
         Step 2: Recreate report definitions natively
         Step 3: Archive all 8 tables

    Option C/D: Defer or ad-hoc export
         Step 1: Archive all 8 tables
         Step 2: Use field inventory as future requirements

Cross-Area References

Decision Summary