Documentation

fsds/dplx-database-gap-analysis.md

Azure vs Local Database Gap Analysis & Remediation Plan

Executive Summary

Compared Azure backup (psql-bbudev2-backup-20251212-142811.dump) with local backup (local-backup-20251212-144945.dump).

Key Findings

Category Azure Local Gap
TOC Entries 1393 1315 +78 in Azure
Tables 166 166 ✅ Identical
Functions 8 8 ✅ Identical
Views 43 40 3 missing from local/code

Critical Gaps Identified

Views in Azure NOT in Code/Local (Will be lost on deployment!)

View Name Schema Purpose Risk
shipment_detail bbu Detailed shipment view with basket counts, return percentages, auto-close detection HIGH - Potentially used in Superset
rfid_reader_daily_activity bbu Daily aggregations per RFID reader (reads, unique tags, RSSI) MEDIUM - Analytics view
rfid_reader_summary bbu Overall summary statistics per RFID reader with location MEDIUM - Analytics view

These views were created ad-hoc directly in Azure and are NOT defined in:

  • SupersetAnalyticsViewsSql.cs
  • SupersetExpansionViewsSql.cs
  • Any EF Core migration

NOT referenced in Superset exports - may be used by dashboards that weren't exported.

View Definitions Extracted from Azure

-- bbu.shipment_detail
CREATE VIEW bbu.shipment_detail AS
WITH basket_counts AS (
    SELECT sia.shipment_id,
        count(*) FILTER (WHERE sia.expect_return = true) AS total_baskets,
        count(*) FILTER (WHERE sia.expect_return = true AND sia.status = 'IN_TRANSIT') AS baskets_in_transit,
        count(*) FILTER (WHERE sia.expect_return = true AND sia.status = 'RECEIVED') AS baskets_received,
        count(*) FILTER (WHERE sia.expect_return = true AND sia.status = 'MISSING') AS baskets_missing,
        count(*) FILTER (WHERE sia.expect_return = false) AS non_returnable_items
    FROM transport.shipment_item_allocations sia
    GROUP BY sia.shipment_id
)
SELECT
    s.tenant_id,
    s.id AS shipment_id,
    s.tracking_number,
    s.reference_number,
    s.shipment_type,
    s.status AS shipment_status,
    s.item_quantity AS declared_item_count,
    s.comments,
    to_timestamp((((('x' || lpad(replace(s.id::text, '-', ''), 16, '0'))::bit(64))::bigint >> 16)::numeric / 1000.0)::double precision) AS created_at_utc,
    s.status_updated_at_utc,
    date(to_timestamp(...)) AS created_date,
    date(s.status_updated_at_utc) AS status_updated_date,
    COALESCE(bc.total_baskets, 0) AS total_baskets,
    COALESCE(bc.baskets_in_transit, 0) AS baskets_in_transit,
    COALESCE(bc.baskets_received, 0) AS baskets_received,
    COALESCE(bc.baskets_missing, 0) AS baskets_missing,
    COALESCE(bc.non_returnable_items, 0) AS non_returnable_items,
    CASE WHEN COALESCE(bc.total_baskets, 0) > 0
         THEN round(100.0 * bc.baskets_received / bc.total_baskets, 1)
         ELSE NULL END AS return_percentage,
    (COALESCE(bc.total_baskets, 0) > 0) AS has_trackable_baskets,
    (s.comments LIKE '%Auto-closed%') AS was_auto_closed
FROM transport.shipments s
LEFT JOIN basket_counts bc ON s.id = bc.shipment_id;

-- bbu.rfid_reader_daily_activity
CREATE VIEW bbu.rfid_reader_daily_activity AS
SELECT
    d.id AS device_id,
    d.name AS reader_name,
    d.serial_number,
    d.device_type,
    date_trunc('day', tr.event_timestamp)::date AS activity_date,
    count(*) AS total_reads,
    count(DISTINCT tr.epc) AS unique_tags,
    min(tr.event_timestamp) AS first_read_at,
    max(tr.event_timestamp) AS last_read_at,
    round(avg(tr.peak_rssi)::numeric, 1) AS avg_rssi,
    count(DISTINCT tr.antenna) AS antennas_used,
    count(DISTINCT tr.mode_type) AS mode_types_used
FROM iot.zb_tag_reads tr
JOIN iot.devices d ON d.serial_number = tr.device_id::text
GROUP BY d.id, d.name, d.serial_number, d.device_type, date_trunc('day', tr.event_timestamp)::date;

-- bbu.rfid_reader_summary
CREATE VIEW bbu.rfid_reader_summary AS
SELECT
    d.id AS device_id,
    d.name AS reader_name,
    d.serial_number,
    d.device_type,
    l.name AS location_name,
    count(*) AS total_reads,
    count(DISTINCT tr.epc) AS unique_tags_read,
    count(DISTINCT date_trunc('day', tr.event_timestamp)) AS active_days,
    min(tr.event_timestamp) AS first_read_ever,
    max(tr.event_timestamp) AS last_read_at,
    round(avg(tr.peak_rssi)::numeric, 1) AS avg_rssi,
    round(count(*)::numeric / NULLIF(count(DISTINCT date_trunc('day', tr.event_timestamp)), 0)::numeric, 0) AS avg_reads_per_day
FROM iot.zb_tag_reads tr
JOIN iot.devices d ON d.serial_number = tr.device_id::text
LEFT JOIN spatial.locations l ON l.id = d.location_id
GROUP BY d.id, d.name, d.serial_number, d.device_type, l.name;

What's Identical (No Action Needed)

  • ✅ All 166 tables across all schemas
  • ✅ All 8 prism functions (journal_writer, temporal *_at functions)
  • ✅ All sequences
  • ✅ All Superset tables (public schema)
  • ✅ Core analytics views already in SupersetExpansionViewsSql.cs

Remediation Plan

Stage 1: Add Missing Views to Codebase

File to modify: engines/bbu/src/Acsis.Dynaplex.Engines.Bbu.Database/Analytics/SupersetExpansionViewsSql.cs

Add three new view definitions following the existing pattern:

  1. CreateShipmentDetail - Comprehensive shipment view
  2. CreateRfidReaderDailyActivity - Daily reader metrics
  3. CreateRfidReaderSummary - Overall reader summary

Add to CreateScripts list:

// RFID Reader Analytics Views
CreateShipmentDetail,
CreateRfidReaderDailyActivity,
CreateRfidReaderSummary,

Add to DropExpansionViews:

DROP VIEW IF EXISTS bbu.rfid_reader_summary;
DROP VIEW IF EXISTS bbu.rfid_reader_daily_activity;
DROP VIEW IF EXISTS bbu.shipment_detail;

Stage 2: Create Migration

Create new migration to apply the views:

/generate-migration AddRfidReaderAnalyticsViews --component bbu

The migration will call SupersetExpansionViewsSql.CreateScripts to ensure all views are applied.

Stage 3: Verify Local Database

Run migration locally to confirm views are created correctly:

dotnet build
# Run AppHost to apply migrations

Stage 4: Export Updated Superset Config (if needed)

If these views are used in Superset dashboards:

  1. Check if datasets exist for these views in Azure Superset
  2. Export updated dashboard configurations
  3. Add to projects/bbu-rfid/resources/superset/exports/

Files to Modify

File Action
engines/bbu/src/Acsis.Dynaplex.Engines.Bbu.Database/Analytics/SupersetExpansionViewsSql.cs Add 3 view definitions + update CreateScripts + update DropExpansionViews
engines/bbu/src/Acsis.Dynaplex.Engines.Bbu.Database/Migrations/ New migration file (auto-generated)

Success Criteria

  • All 3 missing views are defined in SupersetExpansionViewsSql.cs
  • Migration applies successfully to local database
  • pg_restore --list of local backup shows all 43 views (matching Azure)
  • No deployment will accidentally drop these views

Risk Assessment

LOW RISK - These are read-only analytics views:

  • No data loss if dropped (can be recreated)
  • No foreign key dependencies
  • Used only for Superset reporting

MITIGATION: The views are now backed up in the Azure dump file at projects/bbu-rfid/resources/backups/psql-bbudev2-backup-20251212-142811.dump should we need to recover the exact SQL.