IPTD-93
Rutgers Beach System Database
Description
WM&S is working with Rutgers University to revamp it's beaches data management system. The current system only handles coastal beaches related to the Cooperative Coastal Monitoring Program (CCMP). The new beaches data management system will now handle coastal beaches, freshwater lake beaches, and environmental beaches not associated with the CCMP Program. DOIT currently assists WM&S in sending Beach related data to EPA via XML submissions to satisfy WM&S's EPA Beach Grant requirement. To send the XML submissions, DOIT pulls directly from a DOIT maintained beaches database. The problem is that since Rutgers is completely redesigning their system to handle all three beach programs, DOIT will need to establish a completely new Beaches Database structure and redesign all the XML file submissions to pull from the appropriate columns and tables.
The new beach system is scheduled to launch for beach season 2024. Therefore, data submission procedures to satisfy EPA grant funding will need to be working by the Fall of 2024. All data flow of beach data to DEP, along with new XML file generation should be tested and confirmed working prior to the Summer of 2024.
Project Justification
(1) Establish an identical database structure within the DEP:
(2) Create continuous data flow from Rutgers to DEP.
(3) Rework the XML file data pull based on the new Beaches database structure.
Rutgers will work with DEP on an Oracle-compatible Database Dump – This approach will utilize code packages to convert a dump file from either PostgreSQL or MariaDB SQL into an Oracle-compatible dump file that can be re-instated by DOIT on a schedule that meets their data needs (e.g. – nightly, weekly, etc.). This method would provide a complete replication of the database. However due to the potential size and processing time needed to generate the dump files, this would not be accessible through a REST-based API service and would instead be accessible through secure file transfer protocol that could be scripted.
It looks like I had originally intended that both the redesign of the new beach system by Rutgers and the XML piece be handled by DEP. In reality, this project should have been split up into two. That is why I renamed this project back to strictly just the Rutger effort. Will create a new project to represent DOIT/Enotech’s effort.
We have started the process of figuring out a way to migrate data from Rutgers to DEP. Currently looking at best strategies for getting mirror image postgreSQL beaches database structure from Rutgers here at DEP. Then we’ll look at best way to send the data on a yet to be determined schedule. Working with DOIT DBAs to get this done.
Rutgers is running way behind schedule for this project. Since we are less then 2 months away from the start of beach season, and all pieces of the system are not yet complete or thoroughly tested, it looks like we’ll postpone the launch of this new system till beach season 2025.
RU is running a bit behind on this project. RU is developing the beaches and environmental beach systems in tandem and integrated with one another since they are basically identical in the way they operate. In contrast, RU is developing the beach lake program as a "stand-alone" system with its own database structure, permissions system, etc., but will then integrate it into the overall beaches platform so it seems seamless between all three beach programs. This strategy will provide more flexibility to make changes/adjustments to the lake program as it rolls out without having a cascading effect on the other two established beach programs. This effort is setting RU back a bit as they had to redo some things that were already developed, but it should allow RU to move more quickly going forward (instead of fighting to make everything integrate properly). Because of this, we’re running behind on the work that RU and DOIT will do to have the data pushed from RU to DOIT.
Mike K working with Aleem to get 2022 data in which will take a couple weeks. Once complete will work on new system.
There is an underlying need to upgrade the existing SQL database to AWS, which might be better done first in order to smooth the Rutgers connection to this. The Node connection to AWS is still uncertain.
Rutgers work is underway, this project represents the Beaches database & Node changes to match the Rutgers work.