tm-apps-db

tm-apps-db

Apps backend database using the Sequalize library. See /models and /migrations.

• index.js: provides access to the DB using Sequalize APIs
• sync.js: command-line tool to init/synchronize the DB models.

Docs

In order to view or generate an entity diagram of the database, go to ./db/docs/ and follow the instructions.

Migrations

Information on using the Sequelize CLI to perform database migrations can be viewed here: http://docs.sequelizejs.com/en/latest/docs/migrations/

To perform migrations then run

$ yarn run migrate

Migrations can be reversed by running

$ yarn run migrate:undo

Read more in MIGRATIONS.md

Deploy

Apps backend database is deployed as a Cloudformation stack through Drone pipelines by promote events to a specific environment. There are 2 Cloudformation stacks that gets deployed:

• tm-apps-db-env containing a RDS instance (postgres), a security and a subnet group, with alarms set up for CPU and Storage space
• tm-apps-db-dns-env containing a Record Set for the RDS Instance address

The database creation is based on a hardcoded DBSnapshotIdentifier specified in Drone file to properly support restore situations.

The following database credentials are created according to their use:

• master used for deployment pipeline
• consumer used by lambdas with read/write permissions
• api used by APIs with read permissions

The above credentials are injected in a migration file (sql\V001__add_roles.sql) through prebuild.sh script and is part of a migration step in the deployment pipeline using the Flyway tool. This migration file is granting the right permissions on the database to the consumers and apis.

The credentials are stored in AWS Parameter Store in the format: /tm-apps-db/<DB_ROLE>/<username|password>. Example: /tm-apps-db/api/username.

Note:

• Read more on AWS Guide about restoring a database from a snapshot using DBSnapshotIdentifier
• When a new migration for adding a new table is performed against the database, ensure that you update sql\V001__add_roles.sql or create a new migration file for granting the right permissions to consumers and apis which will run in the deployment pipeline with flyway. If a new migration file is added, then consider to update the prebuild.sh script accordingly.

How to recover in case of a Database Outage

1. Create or use an existing snapshot identifier
2. Update DBSnapshotIdentifier value in .drone.yml file
3. Deploy the stack using the specified snapshot
4. Recovering missing data:
   • all consumers that are populating the database have TRIM_HORIZON as their starting position, meaning that we can rely on the state store database streams to repopulate the missing data from the last 24h
   • if the missing data is older than 24h, use the Chronos Reseeder (Jenkins job)
5. There is no need to update the consumers and the apis to point on the new database since they are pointing to the database DNS which gets updated with the new database address (default TTL is set to 60 seconds)