Contribuições para a melhoria da integridade dos dados no herbário HCF-Web: deduplicação, correção de inconsistências e transição tecnológica para PostgreSQL

Abstract

The continuous evolution of the HCF-Web system, developed for managing the collection of the Herbário do Centro Federal (HCF) at the Universidade Tecnológica Federal do Paraná, Campus Campo Mourão (UTFPR-CM), highlighted the need to deepen the processes of data cleansing, validation, and modernization of the existing database. After the system entered production using a MySQL database, inconsistencies in the migrated data began to be identified through real-world use of the application, requiring structural corrections and direct adjustments in the production environment. This work aims to document and implement a set of technical interventions focused on improving the quality, consistency, and scalability of the data in HCF-Web. Among the activities carried out are the identification and correction of defective records originating from the Firebird database migration process, the standardization and normalization of fundamental entities such as collection sites, collectors, and taxonomic identifiers, as well as the addition and population of important semantic fields, such as the classification of exsiccatae as unique or duplicate. Additionally, a data cleansing process was conducted in the production database, with the removal of duplicates and the reinforcement of referential integrity among tables, ensuring greater reliability of the stored information. In parallel, functional improvements were made to the system, including adjustments to the display of exsiccatae images and updates to both the frontend and backend to support new data standards. As a final stage, the work included the technological migration of the database from MySQL to PostgreSQL, using the pgloader tool with carefully defined conversion rules (casts) to ensure data fidelity. This migration was quantitatively validated and accompanied by the adaptation of the application layers, enabling the creation of a functional testing environment. The adoption of PostgreSQL enables the use of the PostGIS extension, opening possibilities for future features based on geospatial data, such as the analysis of collection coordinates and automatic identification of locations. The achieved results consolidate a more robust, consistent, and scalable database, significantly contributing to the technological sustainability and further evolution of the HCF-Web system.