DB, Web Forms, API Integration
Budget: $50 – $0 USD
Current Solution:
Client currently uses an Access database to store outage incidents. Initial outage information is input by field crews. The data is then manually scrubbed with the help other data and other internal sources. Once scrubbed, outages are manually input into a third party Website.
As the current Access database has been in use for 20+ years, HG&E is planning on refactoring the schema, moving to a SQL server, and providing a web application for access to the data.
Within the current process, there are several opportunities to reduce personnel workload
1. Reduce management workload by automating the transfer of outage data to third party website via API.
2. Reduce IT workload by performing the database transition to SQL and providing the web application/front end for data input.
3. Where possible, reduce input and scrubbing operations by utilizing data from other systems.
Automated Transmission of Outage Data to third party website:
• Write an application that polls the database looking for scrubbed/verified outage data
• Transmit new/updated outage data to third party website via the API
• Level of Effort:
Incident Database Migration and Web Access
• Database
o Work to refactor the existing Access schema
o Migrate the data to a newly created multi-user SQL database
While nearly any SQL database server would be sufficient, we recommend an open-source database like PostgresDB or MariaDB to remove licensing fees
o Level of Effort:
• Web Application
o Write an Incident Reporting System web application that provides access to the new SQL database and integration functionality
User Authentication (OAUTH2/OIDC) - Assumes an existing OAUTH provider (e.g. Okta, Azure Active Directory, Keycloak)
User Authorization (e.g. All roles can input data, but only certain roles can mark an incident as scrubbed/verified)
As a general rule-of-thumb, we expect each page of the application to correlate with a database table (exception: pre-populated/static reference tables)
Tech Stack
Kotlin or Java
Micronaut
Angular or React
While Linux is preferred, the application can also be run on Windows
o Level of Effort:
Client currently uses an Access database to store outage incidents. Initial outage information is input by field crews. The data is then manually scrubbed with the help other data and other internal sources. Once scrubbed, outages are manually input into a third party Website.
As the current Access database has been in use for 20+ years, HG&E is planning on refactoring the schema, moving to a SQL server, and providing a web application for access to the data.
Within the current process, there are several opportunities to reduce personnel workload
1. Reduce management workload by automating the transfer of outage data to third party website via API.
2. Reduce IT workload by performing the database transition to SQL and providing the web application/front end for data input.
3. Where possible, reduce input and scrubbing operations by utilizing data from other systems.
Automated Transmission of Outage Data to third party website:
• Write an application that polls the database looking for scrubbed/verified outage data
• Transmit new/updated outage data to third party website via the API
• Level of Effort:
Incident Database Migration and Web Access
• Database
o Work to refactor the existing Access schema
o Migrate the data to a newly created multi-user SQL database
While nearly any SQL database server would be sufficient, we recommend an open-source database like PostgresDB or MariaDB to remove licensing fees
o Level of Effort:
• Web Application
o Write an Incident Reporting System web application that provides access to the new SQL database and integration functionality
User Authentication (OAUTH2/OIDC) - Assumes an existing OAUTH provider (e.g. Okta, Azure Active Directory, Keycloak)
User Authorization (e.g. All roles can input data, but only certain roles can mark an incident as scrubbed/verified)
As a general rule-of-thumb, we expect each page of the application to correlate with a database table (exception: pre-populated/static reference tables)
Tech Stack
Kotlin or Java
Micronaut
Angular or React
While Linux is preferred, the application can also be run on Windows
o Level of Effort: