tm-apps-db v4.3.18

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.

Database workings

Migrations

Read in MIGRATIONS.md

Credentials

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.

Seeding

There is a Chronos Seeding Job on Jenkins that is triggering the consumers based on DynamoDB data, but is not indicated to run on a large number of entries (more than 60k).

Go to Jenkins Chronos Reseeder job, fill in the the EscenicStateStore-<component>-<env>, fill in the lambda name and run the job. This will query the specified table and run the consumer for each entry which in the end will write down to the PosgresDB.

Links:

• prod: http://prod-build.tm-awx.com:8080/jenkins/job/Chronos-seeding/
• dev (bertha and stable): http://dev-build.tm-aws.com:8080/jenkins/job/Chronos-Seeding/

Deploy

NPM package

Apps APIs are using database models for queries and hence this repo is delivered as an NPM package.

A new version can be deployed by updating the version number from package.json file by following the semantic versioning syntax and tagging the commit with that version. When pushing the newly tag to git, a new package version will be published.

To bump up the package version, use npm version with --git-tag-version option - this will bump up the package version and it will automatically commit and tag the version change.

Eg:

npm version patch --git-tag-version

The above will increment the third digit of the version and it will tag the commit with that version.

Short description for semantic versioning syntax:

Stage  | Rule                                                            | Example
----------------------------------------------------------------------------------
major  | Increment the first digit and reset 2nd and 3rd digits to zero  | 4.0.0
minor  | Increment the middle digit and reset the 3rd digit to zero      | 3.4.0
patch  | Increment the 3rd digit                                         | 3.4.2

To trigger the drone pipeline, run:

git push origin <tag>

Note that git push origin --tags does not trigger the drone pipeline. It is however better to trigger it with only 1 tag referenced using git push origin <tag>.

Drone pipelines

The database itself is deployed as a Cloudformation stack through Drone pipelines by promote events to a specific environment:

drone build promote trinitymirror-ondemand/tm-apps-db 268 bertha

More information on promote on the drone documentation.

Note: You may need to configure drone locally before being able to run this command (to get the DRONE_TOKEN token, go to your drone's account):

export DRONE_SERVER=https://droneb.tm-dev-awx.com
export DRONE_TOKEN=<your_token_here>
drone info

CloudFormation Stacks

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

CF Stack Policy

The drone step add-stack-policy is adding a stack policy to the stack tm-apps-db-<env> that block any deletion or replacement on RDS instances.

More information on AWS documentation.

CF termination protection

The drone step add-stack-termination-protection is adding a termination protection to the stack tm-apps-db-<env>.

More information on AWS documentation.

DB Snapshot Identifier

If the database is created (or replaced during update), it can (or should) be based on a hardcoded DBSnapshotIdentifier specified in the Drone file to properly support restore situations. A snapshot can be created via the AWS console (either from tm-apps-db-stable for the dev pipeline or tm-apps-db-prod for the prod pipeline). Then, replace the respective DBSnapshotIdentifier field inside of the drone file. If the DB experienced some downtime during update, run the seeding job described further down in this file.

Note that once a DB instance is restored with a DBSnapshotIdentifier property, the same DBSnapshotIdentifier property must be specified for any future updates to the DB instance. This means that once the DB is created base on a snapshot, any further updates with the same snapshot identifier will not require a replacement of that DB.

Read more on AWS Guide about restoring a database from a snapshot using DBSnapshotIdentifier.

Notes

• 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.
• an ignore field was added to the lint-cf steps in drone.yml related to rule W3011 that require the UpdateReplacePolicy field for an RDS instance. Adding this field would require a replacement of the DB which is not wanted at that time (as it is a risky operation); this field could be added when a time arrives when we need to delete or replace the RDS instance.

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)

Recent failures

• CRITICAL : Copying a table won’t copy indexes, so when a query with JOIN operations is executed is not performing as expected: make sure to recreate all the indexes on a table (June 2017, observed high levels of CPU Usage, Follow Author Epic).

TODO

• update .npmignore with the right files / folders
• move CI files into a specific CI folder to clean up root
• find a way to integrate the creation of stacks to the drone step add-stack-policy, potentially by including stack policies into the trinitymirror/drone-cloudformation image.

Change Logs

version 4.2.0

• added is_live field to article table + created a migration file

version 4.1.1

• removed virtual setter on tag_id column from tag model. This was probably an error in the first place but was not damaging with previous versions of Sequelize. However, with v5, it nows breaks the code so it was needed to be taken out

version 4.1.0 - NOT WORKING

• correct model definition adding the right foreignKey to each relevant association

version 4.0.0 to 4.0.5 - NOT WORKING

• upgrade sequelize from v3.24.3 to v5.21.5

  • model.find alias was deprecated and replaced by model.findOne

  • removed classMethods and instanceMethods options from sequelize.define and replaced by

    // Class Method
    Model.associate = function (models) {
        ...associate the models
    };

    and

    // Instance Method
    Model.prototype.someMethod = function () {..}

• upgrade commander from v2.20.0 to v5.0.0

• upgrade pg from v6.1.0 to v7.18.2
• upgrade sequelize-cli from v5.5.0 to v5.5.1
• upgrade rambda from v to v0.27.0
• add foreignKey to relevant model associations

• add define property to configuration given new underscored rule of sequelize v5, as:

  config.define = {
      createdAt: 'created_at',
      updatedAt: 'updated_at',
      deletedAt: 'deleted_at'
  }

Note: version 4.0.0 to 4.0.5 are not working, first working version of v4 is v4.1.0

version 3.3.1

Last version (working) not tracked in this changelog.