ReconOne: Data Reconciliation Tool

An all-in-one reconciliation tool to compare files and databases, generating comprehensive reports in HTML or Excel. ReconOne is an open-source (GPLv3) solution designed for automated testing of financial systems like Calypso or Murex.

📋 Table of Contents

• About
• Key Features
• Use Cases
• Prerequisites
• Installation
• Quick Start
• Build from Source
• Usage
• Development
• Development Environment Setup
• Running Tests
• Project Structure
• Reconciliation Types
• Contributing
• Support
• Security
• Changelog
• Built With
• License
• Contact
• Advanced Topics
• Vision and Design Principles
• Architecture Overview
• Performance and Scaling
• Production Hardening
• Troubleshooting

📖 About

ReconOne is a powerful data reconciliation tool that compares data from various sources like files and databases. It produces detailed reports in HTML and Excel, making it an ideal module for automated testing in financial systems, including Treasury Solutions such as Calypso or Murex.

Key Features

• Multi-Source Comparison: Compare data across databases (PostgreSQL, Oracle, MySQL, etc.), CSV files, and Excel spreadsheets.
• Comprehensive Reporting: Generate detailed difference reports in HTML and Excel formats.
• Centralized Dashboard: A web interface to view reconciliation results and test history.
• Custom SQL Queries: Execute custom queries for flexible data extraction and comparison.
• Advanced Column Mapping: Support for ignoring, renaming, and masking columns.
• Multi-Key Reconciliation: Handle complex scenarios with multiple key columns.
• CI/CD Integration: A RESTful API for seamless integration into DevOps pipelines.
• High-Performance Engine: Efficiently process large datasets in real-time.

Use Cases

• Financial Data Validation: Ensure data consistency across trading and financial systems.
• Automated Testing: Validate system outputs against expected results in test environments.
• Data Migration: Verify data integrity during system migrations and upgrades.
• Regulatory Compliance: Generate audit trails and reports for regulatory needs.
• Quality Assurance: Continuously monitor data quality in production.

⚙️ Prerequisites

System Requirements

• Java Development Kit (JDK): Version 21 or higher.
• Node.js: Version 18.19.1+, 20.11.1+, or 22.0.0+ with npm.
• Database: PostgreSQL, Oracle, MySQL, H2, SQLite, or Sybase.

Hardware Requirements

• Memory: 4GB RAM (8GB recommended).
• Storage: 2GB free disk space.

🚀 Installation

Quick Start

Get up and running with a pre-built release.

1. Download: Get the latest release from the GitLab releases page.
2. Database: Initialize your database using a script from util/database_init_scripts/ or use the pre-configured H2 database at util/h2/standalone.mv.db.
3. Configure: Copy server.properties and executor.properties from the util/config folder and customize them.
4. Run: Execute startReconOne.bat (or the equivalent shell script) from util/start_script/ to launch the Server and Executor.
5. Access: Open your browser to http://localhost:8080.
6. Login: Use standalone/standalone and change the password immediately.

Build from Source

For developers who want to build the project from the source code.

1. Clone Repository bash git clone https://gitlab.com/devone4/recon_one.git cd recon_one

2. Build Application bash ./gradlew build This creates JAR files in projects/server/build/libs/ and projects/executor/build/libs/.

3. Database Setup Choose and run the appropriate initialization script from util/database_init_scripts/.

4. Application Configuration Copy and configure server.properties and executor.properties from util/config/.

5. Start Services ```bash # Start Server java -Xms512m -Xmx4g -jar /path/to/recon-one-server.jar --spring.config.additional-location=file:/path/to/server.properties

   Start Executor

   java -Xms1g -Xmx8g -jar /path/to/recon-one-executor.jar --spring.config.additional-location=file:/path/to/executor.properties ```

💻 Usage

Once running, access the web interface at http://localhost:8080. The main features include:

• Dashboard: An overview of reconciliation results and test execution history.
• Database Reconciliation: Compare tables or results from custom SQL queries.
• File Reconciliation: Compare structured files like CSV and Excel.
• Context Reconciliation: Perform reconciliations within specific business contexts.

🛠️ Development

Development Environment Setup

1. Clone the Repository bash git clone https://gitlab.com/devone4/recon_one.git cd recon_one

2. Configure Database

   • Run a database initialization script from util/database_init_scripts/.
   • Configure the database connection in config/server.properties and config/executor.properties.

3. Build the Project bash ./gradlew build -Pversion=dev

4. Start Backend Services ```bash # Start Executor java -jar projects/executor/build/libs/recon-one-executor-dev.jar --spring.config.additional-location=file:/path/to/executor.properties

   Start Server

   java -jar projects/server/build/libs/recon-one-server-dev.jar --spring.config.additional-location=file:/path/to/server.properties ```

5. Start Frontend Development Server bash cd client npm install npm start

6. Access Development Environment

   • Frontend: http://localhost:4200
   • Backend API: http://localhost:8080/api/v1
   • Swagger UI: http://localhost:8080/api/v1/swagger-ui.html

Generate Typed Angular API Client

To keep the frontend aligned with the backend API, you can generate a TypeScript Angular client from the OpenAPI specification.

1. Start the backend server.
2. Run the generation script: bash cd client npm run api:generate This generates a typed client in client/src/app/api.

Running Tests

Execute the test suites for all modules.

# Run all tests
./gradlew test

# Run tests for a specific module
./gradlew :projects:server:test

# Generate test coverage reports
./gradlew jacocoTestReport

Coverage reports are available in build/reports/jacoco/test/html/ for each module.

🤝 Contributing

We welcome community contributions!

1. Fork & Clone: Fork the repository and clone it locally.
2. Create Branch: git checkout -b feature/your-new-feature.
3. Develop: Make your changes, adhering to the project's coding standards.
4. Test: Add and run tests to verify your changes.
5. Commit & Push: Commit your changes and push to your fork.
6. Create Merge Request: Open a merge request with a clear description of your work.

Reporting Issues

• Search First: Check if a similar issue already exists.
• Be Detailed: Provide steps to reproduce, expected vs. actual behavior, and environment details.
• Report Here: GitLab Issues

📁 Project Structure

ReconOne is a multi-module project:

• projects/client/: Angular frontend application.
• projects/server/: Spring Boot REST API server for business logic and orchestration.
• projects/executor/: The engine that processes reconciliation jobs.
• projects/common/: Shared utilities.
• projects/dataaccess/: Data access layer for database operations.
• projects/recon/: Core reconciliation algorithms.
• projects/templates/: Report templates.
• config/: Application configuration files.
• util/: Utility scripts (database init, startup).

🔄 Reconciliation Types

• Database Reconciliation: Compare tables or custom query results, even across different database systems.
• File Reconciliation: Compare CSV, Excel, and fixed-width files.
• Context Reconciliation: Apply business-specific rules and conditions.
• Advanced Reconciliation: Use column mapping, data transformations, and tolerance-based comparisons.

🆘 Support

• Documentation: Check the doc/ directory.
• Issue Tracker: GitLab Issues.
• Commercial Support: Contact us at contact@devone.es for enterprise support.

🔒 Security

• Reporting Vulnerabilities: Please report security issues privately to security@devone.es. Do not create public issues.
• Best Practices: Change default credentials, use strong passwords, and keep dependencies updated.

📋 Changelog

Version history is available on the GitLab Releases page. The project follows Semantic Versioning.

🏗️ Built With

• Frontend: Angular, TypeScript, Bootstrap
• Backend: Spring Boot, Spring Security, Spring Data JPA, Hibernate
• Build: Gradle, Node.js/npm
• Databases: PostgreSQL, Oracle, MySQL, H2, SQLite, Sybase

📄 License

This project is licensed under the GNU General Public License v3.0 (GPL-3.0). See LICENSE.md for details.

📞 Contact

• Email: contact@devone.es
• Website: https://devone.es
• GitLab: https://gitlab.com/devone4/recon_one

🧠 Advanced Topics

This section covers the architectural principles, performance considerations, and other advanced details for developers and system administrators.

Vision and Design Principles

• Correctness: Deterministic comparison with explicit rules and audit trails.
• Explainability: Human-friendly reports that show why a record mismatched.
• Extensibility: Pluggable connectors and mappers for custom systems.
• Performance: Streaming I/O, batching, and pushdown operations.
• Automation: CI/CD-friendly APIs for pipeline integration.
• Security: Least-privilege access, encryption, and secrets management.

Architecture Overview

• Client (Angular): UI for configuring runs and viewing results.
• Server (Spring Boot): API gateway, orchestration, and scheduling.
• Executor (Spring Boot): Job execution, comparison engine, and data adapters.
• Data Sources: RDBMS and files (CSV, Excel).

Performance and Scaling

• JVM Sizing: Adjust heap sizes for Server (-Xmx4g) and Executor (-Xmx8g) based on load.
• Database Indexing: Index join keys and filtered columns.
• Query Optimization: Use custom SQL with filters to reduce data transfer.
• Batch Processing: Control batch sizes to balance memory and throughput.

Production Hardening

• Secrets Management: Use environment variables or a secrets manager (e.g., Vault) for credentials.
• Secure Transport: Enforce TLS for all external traffic.
• Monitoring: Set up structured logging and metrics (CPU, heap, GC, latency).
• Backup Strategy: Regularly back up metadata and reports.

Troubleshooting

• Port Conflicts: Change server.port in server.properties if port 8080 is in use.
• Login Failures: Verify database initialization and credentials.
• OutOfMemoryError: Increase JVM heap size or reduce batch sizes for large datasets.
• Slow Queries: Analyze query plans (EXPLAIN) and add indexes.