[ADR]: Persist Data in Prod databases

[SammySteiner]

Description

For beta.grants.gov to reach feature parity with grants.gov we will need to write to the database. Several approaches to that will need to be considered. The goal is to eventually deprecate the oracle database and migrate to a postgres database with an improved schema to facilitate more robust opportunity querying and opportunity lifecycle tracking. There will be a time before that third database is created that both applications will need to write data that can be read by both applications.

Approvers

• 1 engineer not on the ticket
• @widal001 and/or @lucasmbrown-usds
• 1 representative of MicroHealth

Options

• Each application writes to its own database and DMS replicates the changes in two directions instead of just one.
  • Will require enhanced permissions for the DMS instance
• Beta api sends post requests to the grants.gov database directly and have DMS replicate those changes to the beta database.
  • The beta api will require db access for grants.gov db
• Beta api writes to both databases
  • DMS is updated to not replicate data introduced by the beta service

Decision Criteria

• Technical Debt: The chosen solution will not require significant application changes when replacing grants.gov with beta.grants.gov
• Latency and performance: Consider the production databases' ability to respond quickly to any request
• Data Security: Consider to security features including the ability to encrypt in transit and at rest
• Network Security: Consider the impact of exposing network traffic, aws resources, and api endpoints to the public internet
• Data parity: Ensure that queries to beta or grants, made at the same time, returns the same data
• Scalability: Ensure each application can meet all requirements at scale
• Monitoring and Alerting: Consider the ability of each system to fail gracefully in the case of a db, dms, application, or network outage
• Cost: Consider the operation costs of each approach

Definition of Done

• [ ] The approvers for this decision have been identified (ideally before work on the ADR starts)
• [ ] The ADR is created and stored in documentation/decisions/adr with status "Accepted"
• [ ] The ADR has been reviewed and approved by the approvers listed above
• [ ] The ADR satisfies requirements that are outlined in the ADR template
• [ ] Any follow-up tickets have been created (if necessary)

[widal001]

Yay! Thank you for creating an ADR to track this decision. @SammySteiner

One other decision criteria I might add is "technical debt" or something to indicate that we want to pick an option that won't require us to reimplement a bunch of code when we promote beta.grants.gov out of beta and it becomes the primary system.

A third option could be that our web service writes to both beta.grants.gov and grants.gov databases and then we configure DMS to use merge replication on beta.grants.gov tables that support writing (if that's supported)