Data Reconciliation
Relevant source files
The following files were used as context for generating this wiki page:
The Data Reconciliation system provides administrators with a centralized dashboard to manage all Data Logger (DL-#) sources in the database. It transforms the previous "Combine Duplicate Systems" feature into a comprehensive tool for auditing data health, identifying and "adopting" orphaned data streams into new systems, and merging duplicate system entries. This ensures data integrity and provides a complete picture of all data sources.
This system is a key part of maintaining data quality, alongside related features like the Autonomous Self-Correction and Duplicate Detection mechanisms.
Sources: DATA_RECONCILIATION_PR_SUMMARY.md, DATA_RECONCILIATION_IMPLEMENTATION.md
Architecture Overview
The system consists of a backend Netlify function that performs a data integrity audit and a frontend React component that presents the audit results and provides management workflows.
The following diagram illustrates the high-level architecture of the Data Reconciliation feature.
Backend: Data Integrity Audit
The core of the backend is a serverless function that audits all DL-# records and categorizes them.
Sources: DATA_RECONCILIATION_IMPLEMENTATION.md:1. Backend: Data Integrity Endpoint, DATA_RECONCILIATION_PR_SUMMARY.md:Backend (MongoDB + Netlify Functions)
API Endpoint: admin-data-integrity
- File:
netlify/functions/admin-data-integrity.cjs - Endpoint:
GET /.netlify/functions/admin-data-integrity - Purpose: To perform a comprehensive audit of all
analysisrecords, group them bydlNumber(DL-#), and cross-reference them with registeredsystemsto identify their status.
The function uses an efficient MongoDB aggregation pipeline to perform the audit. The pipeline groups records, counts them, tracks timestamps, and categorizes each DL-# source as either MATCHED (linked to a system) or ORPHAN (data exists without a corresponding system).
Sources: DATA_RECONCILIATION_IMPLEMENTATION.md:1. Backend: Data Integrity Endpoint, DATA_RECONCILIATION_PR_SUMMARY.md:Backend (MongoDB + Netlify Functions)
API Response Structure
The endpoint returns a structured JSON object containing a summary and a detailed list of all data sources.
| Field | Type | Description |
|---|---|---|
summary | Object | High-level statistics. |
summary.total_dl_sources | Number | Count of unique DL-# identifiers found. |
summary.matched | Number | Count of DL-# sources linked to a system. |
summary.orphaned | Number | Count of DL-# sources not linked to any system. |
summary.orphaned_records | Number | Total number of analysis records belonging to orphaned sources. |
data | Array | An array of data source objects. |
data[].dl_id | String | The Data Logger ID (e.g., "DL-240"). |
data[].record_count | Number | The number of analysis records for this DL-ID. |
data[].status | String | "MATCHED" or "ORPHAN". |
data[].system_id | String | null | The ID of the associated system, if matched. |
data[].system_name | String | null | The name of the associated system, if matched. |
timestamp | String | The ISO 8601 timestamp of when the audit was run. |
Sources: DATA_RECONCILIATION_IMPLEMENTATION.md:Response Format
Frontend: Reconciliation Dashboard
The user-facing component for data reconciliation is the ReconciliationDashboard, which provides a split-view UI for monitoring data health and performing management tasks.
Sources: DATA_RECONCILIATION_IMPLEMENTATION.md:2. Frontend: Reconciliation Dashboard Component, DATA_RECONCILIATION_PR_SUMMARY.md:Frontend (React + TypeScript)
UI Components
The dashboard is composed of several key visual elements:
| Component | Description |
|---|---|
| Summary Cards | Four cards at the top displaying key metrics: Total DL Sources, Matched, Orphaned, and Orphaned Records. |
| Orphaned Data Sources Table | A table with a yellow warning style that lists all orphaned DL-# sources. It provides an "Adopt System" action for each orphan. |
| System Status & Management Table | A table listing all registered systems, their linked DL-#s, and total record counts. It includes checkboxes for selecting systems to merge. |
| Merge Systems Controls | A set of controls that appear when two or more systems are selected, allowing the admin to choose a primary system and execute the merge. |
Sources: DATA_RECONCILIATION_IMPLEMENTATION.md:Key Features, DATA_RECONCILIATION_PR_SUMMARY.md:Split-view UI
User Workflows
The dashboard enables two primary administrative workflows: adopting orphaned data and merging duplicate systems.
1. Adopt Orphaned DL-#
This workflow allows an admin to create a new system for an unlinked data source.
The following diagram shows the user flow for adopting an orphaned DL-#.
2. Merge Duplicate Systems
This workflow allows an admin to combine multiple system entries into a single primary system.
The following diagram shows the user flow for merging duplicate systems.
Supporting Components & Services
EditSystemModal.tsx
This modal component was enhanced to support both editing existing systems and creating new ones. For reconciliation, its "Create Mode" is used.
- Create Mode: The modal can be opened with a
nullsystem prop to render a form for creating a new system. - Geolocation: It includes an
enableGeolocationprop and a "Use Current Location" button that uses the browser'snavigator.geolocationAPI to auto-detect and fill in GPS coordinates. - Initial Data: A new
initialDataprop allows pre-filling the form, which is used to automatically populate the DL-# when adopting an orphan.
Sources: DATA_RECONCILIATION_IMPLEMENTATION.md:3. Enhanced EditSystemModal, DATA_RECONCILIATION_PR_SUMMARY.md:Enhanced File: components/EditSystemModal.tsx
clientService.ts
The client-side API service was updated to support the new reconciliation features.
getDataIntegrity(): A new function that makes an authenticatedGETrequest to the/.netlify/functions/admin-data-integrityendpoint to fetch the audit report.registerBmsSystem(): This existing function was updated to accept anassociatedDLsarray in its payload, allowing a new system to be created and linked to one or more DL-#s in a single operation. It maintains backward compatibility by defaulting the array to empty.
Sources: DATA_RECONCILIATION_IMPLEMENTATION.md:4. Updated Client Service, DATA_RECONCILIATION_PR_SUMMARY.md:Updated File: services/clientService.ts
Related Feature: Duplicate Detection
While the reconciliation dashboard focuses on linking data to systems, a related data quality feature is the detection of duplicate and semantically similar items, such as AI-generated feedback. This is handled by a separate two-tier system.
- Tier 1: Exact Match: Uses a SHA-256 content hash for an O(1) lookup to find exact duplicates.
- Tier 2: Semantic Similarity: If no exact match is found, it calculates the Jaccard similarity on word sets from item fields (e.g., title, description) to find near-duplicates. A configurable threshold (default 70%) is used to determine similarity.
Sources: SPRINT_COMPLETE_SUMMARY.md:4. Enhanced Duplicate Detection
The following diagram shows the logic for the two-tier duplicate detection system.
Related Feature: Autonomous Self-Correction
To improve data quality at the source, the system also employs a validation-driven retry loop during the initial data extraction from BMS screenshots. This is part of the broader data reconciliation and quality effort.
- Physics-Based Validation: Extracted data is checked against physical laws (e.g.,
Power = Current × Voltage,Overall Voltage ≈ Sum of Cell Voltages). - Quality Scoring: Each extraction attempt is given a quality score from 0-100.
- Actionable Feedback: If validation fails, specific feedback is generated and sent back to the AI for a retry attempt.
- Smart Duplicate Handling: If a duplicate image is uploaded and the existing record has a low quality score (
< 80), the system re-analyzes it to attempt an upgrade.
Sources: AUTONOMOUS_SELF_CORRECTION.md, SANITY_CHECK_REPORT.md
Conclusion
The Data Reconciliation Dashboard provides a powerful and necessary tool for administrators to maintain a clean and accurate dataset. By offering clear visibility into data sources and providing simple workflows for adopting orphans and merging duplicates, it ensures that all incoming data is properly categorized and linked to a system. This enhances data integrity, improves the reliability of analytics, and saves significant manual investigation time.