phinternal-api

phinternal-api

Internal API used by phin-web, phin-service-network, and phin-api. This project owns the main phin Postgresql database and its migrations.

Getting Started

Initial Workstation Setup

1. Install Homebrew [http://brew.sh/]

2. Install Postgres 13.x with postgis 3.0.3

   • Use PostGresApp on OSX.

3. Ensure that createdb is on your $PATH: http://postgresapp.com/documentation/cli-tools.html.

• Note: For Mac users, you may need to create .bash_profile in your home directory

1. Install Version 12.x node.js: [https://nodejs.org/en/download/(https://nodejs.org/en/download/)
2. Install RabbitMQ - see section below.
3. Install the heroku toolbelt [https://s3.amazonaws.com/assets.heroku.com/heroku-toolbelt/heroku-toolbelt.pkg]
4. Install Paw from the Paw [https://paw.cloud/]
5. Install Xcode (Surprise Kuldip!)
6. Install redis (https://redis.io/)

Project Setup

1. Run npm install
2. Run git submodule init
3. Run git submodule update
4. Create the database user npm run create:pguser for migrations
5. Create the initial phinternal database inside Postgres by running npm run create:db.
6. Follow the 'Running Database Migrations' instructions below to run the database up to latest version.
7. Install the pHin Heroku CLI plugin and follow the 'Creating up a pHin user' instructions below to create a local user.
8. Follow the 'Loading static data' instructions below to pre-populate the database.

Rules for Editing Phinternal

1. Use proper security. Secure routes must include the security middleware.
2. Ensure proper user ownership. If you edit a resource, ensure the calling user owns that resource.
3. Add unit test for unit testable functions like validations.
4. For any API routes changes, perform the following:
   1. Optionally update the file phinternal-swagger.yml with API route changes.
   2. Add API tests in tests\integration for all routes. Tests should verify database modifications and check validations.
   3. Update the phinternal client library in phin-common-js/lib/phinternal.
5. For any new database tables or table changes, perform the following:
   1. Create a migration file in migrations which performs the change up and down.
   2. Create a Sequelize model or update the existing model in models for the new table or change.
6. Determine if a change audit is necessary and implement.

Installing the pHin Heroku CLI plugin

1. Install it's dependencies by running: cd ./tools/heroku-phin && npm install.
2. Link it to your Heroku cli by running heroku plugins:link ./tools/heroku-phin.
3. Test it by running heroku and verifying that phin shows up in the command list. You can also run heroku phin --help for a list of options.

Exporting a user account to a JSON file

You can export a user from any environment to a JSON file that can be imported to another database. You can view usage instructions by running heroku phin:export-user --help.

Examples:

Export from a Heroku database: heroku phin:export-user --app=[heroku app name] --userId=456

Export from a local database: heroku phin:export-user --userId=456

Importing a user account from a JSON file

After exporting a user to a JSON file you can import that file to a local or remote database. You can view usage instructions by running heroku phin:import-user --help.

Examples:

Importing into your local database: heroku phin:import-user [exported account file].json

Importing into a Heroku database: heroku phin:import-user --app=[heroku app name] [exported account file].json

Creating up a pHin user

To create an admin user in a local database:

heroku phin:create-user --email=[e-mail] --password=muscularMichelle96 --firstName=Barack --lastName=Obama --isAdmin

To create a standard user just leave out the admin flag:

heroku phin:create-user --email=[e-mail] --password=muscularMichelle96 --firstName=Barack --lastName=Obama

To create a user in a Heroku database just provide the --app paramater and choose the correct database.

Changing a pHin user's password

To change a pHin user's password in your local database run the following command:

heroku phin:change-password --email=[email] --password=[new password]

Inserting fake user accounts

To insert some fake user accounts ( the same accounts we use in tests ) run the following command:

heroku phin:insert-fake-accounts

To change a user's password in a Heroku database just provide the --app paramater and choose the correct database.

Running a node script with Heroku Postgres DB Credentials

Run heroku phin:run --app=[heroku app name] script.js [args]. This command will query heroku and prompt you to select a database connected to the app. It will then execute your script with the following environemnt variables:

• DATABASE_URL - full connection url for the selected database
• DATABASE_SSL = true or false based on the chosen database

This allows you to build scripts that connect to our heroku postgres databases which accept the env variables above to keep us from committing database credentials or having to load ./src/config.

RabbitMQ

1. Install RabbitMQ using homebrew: brew install rabbitmq.
2. Add the RabbitMQ installation directory to your ~/.bash_profile -> export PATH="/usr/local/sbin:$PATH".
3. Run the server rabbitmq-server.
4. Visit the RabbitMQ admin dashboard at http://localhost:15672/. Login using 'guest' and 'guest'.

Redis

1. Download version 4.0
2. Follow the installation instructions on the download page

PostgreSQL

Install version 12.3.x with postgis 3.0.x

• you can install on ubuntu by using this command. sudo apt install postgresql-9.6 postgresql-9.6-postgis-2.3 postgresql-9.6-postgis-2.3-scripts postgresql-contrib-9.6

Getting DB Started

• npm run drop:db # Reset your local environment by dropping and creating from scratch
• npm run create:pguser # Create postgres role if you are running it for first time
• npm run create:db
• npm run migrate:up # Run pending migrations up to latest version.

Running Database Migrations Locally

• npm run drop:db # Reset your local environment by dropping and creating from scratch
• npm run create:pguser # Create postgres role if you are running it for first time
• npm run create:db
• npm run migrate:up # Run pending migrations up to latest version.
• npm run migrate:down # Revert the last migration run.

Loading static data

To load static DB data in your local database, run npm run load:static-data. To load the static data on Heroku, run heroku run bash then npm run load:static-data.

Creating an empty database migration

When it's time to add a new migration so that you can make changes to the database schema, run npm run migration:create [name]. An empty migration file will be created in the migrations folder. Change the name of the file to reflect your changes, add both up and down code, and test both up and down.

Database Migrations on Heroku ( Stage or Production )

Login to the Heroku instance using npm run bash:prod or npm run bash:stage and run npm run migrate:heroku:up.

Dumping and Importing

// DUMP LOCAL DATABASE
pg_dump --host=localhost --dbname=phinternal -a -b -f dump.sql

// IMPORT LOCAL
psql --host=localhost --dbname=phinternal -f dump.sql

// IMPORT TO HEROKU
psql --host=ec2-54-227-247-40.compute-1.amazonaws.com --dbname=d7eb3pqu74oa4f --port=6702 --username=ufnehfa9ur7gev -f dump.sql

You can also download DB backups from postgres and import them locally:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -d phinternal [filename of downloaded backup]

Reseting serial (autoincrement) sequences

EXAMPLES

SELECT setval('"UserAudits_user_audit_id_seq"', max("user_audit_id")) FROM "UserAudits";
SELECT setval('"Users_userId_seq"', max("userId")) FROM "Users";

Testing

This project includes npm scripts for testing, which create their own database for testing before they are run and drop them after the test completes (doesn't matter if its successful or not)

The complete test suite can be executed by using the command npm test

• Integration: tests which requires the full REST api to be up and running. The testing scripts go on the folder: tests/integration/ and are run with the command: npm run test:integration
• Database: tests which do not use the REST api but use the postgres database. This include any integration test that calls the DB and Model tests which are testing to the DB. The testing scripts go on the folder: tests/database/ and are run with the command: npm run test:database
• Unit: Tests which do not require the DB or the REST api to be running. Tests for model instance and class functions which leverage mocks. The testing scripts go on the folder: tests/unit/ and are run with the command: npm run test:unit

Important Note: Chai and Assert (chai.assert) are loaded as globals before the tests are instantiated, so they are no longer needed to be required on every test file

Running A Chemical Engine Worker

You can test the chem engine by inserting test data for a vessel and enqueing a chem-check for that vessel.

1. Prepare the user and vessel data
2. Ensure that RabbitMQ is setup and running a. rabbitmq-server, see RabbitMQ above b. run phinternal-api at least once to create queues and exchanges
3. Start a chemical engine worker
4. Enqueue a job using the admin tool

Heroku Stuff

If you run into an error building a deployment try the following:

Install the Heroku Repo plugin if you haven't already

heroku plugins:install https://github.com/heroku/heroku-repo.git

Run

heroku repo:purge-cache -a appname

If your heroku command line tools will not exit after completing. Delete the following folders: ~/.local/share/heroku ~/Library/Caches/heroku

Managing Addons

/// List addons
heroku:addons

// Attach an addon
heroku addons:attach postgresql-tetrahedral-6520 --as SERVICENETWORK_DATABASE_URL

// Detach an addon
heroku addons:detach HEROKU_POSTGRESQL_GREEN

Papertrail

1. Add the log drain to the heroku app: heroku drains:add syslog+tls://logs4.papertrailapp.com:31567 --app [appname]
2. List the log drains to get the drain id: heroku drains --app [appname] - the value in parenthesis at the end of the drain, leave out the d. at the front
3. Go to papertrail and click the link to see details for all systems. The UI will show a pie chart and a list of event systems - not log messages.
4. take the drain id from above ( without the d. at the front ) and paste it into the filter box.
5. Click settings for filtered system that matches your drain id.
6. Update the name and description of the drain to match your app.

Heroku Procfile Settings

From here I learned that you can control some of the V8 garbage collection behavior. Heroku recommends these Procfile settings for 512Mb instances:

node --optimize_for_size --max_old_space_size=460 --gc_interval=100

This should trigger GC before the memory warning kicks in. Here is the article for reference: Heroku Node Habits.

Dumping all samples from production follower

Run heroku pg:psql HEROKU_POSTGRESQL_BRONZE --app=phinternal

Then run \copy (SELECT hardware_id, sample_index, temp_c, ph, orp_mv, battery_mv, sample_date FROM "PhinDeviceSamples" ORDER BY hardware_id ASC, sample_date DESC ) TO dump.csv CSV DELIMITER ','

Errors

> psql

dyld: Library not loaded: /usr/local/opt/readline/lib/libreadline.6.dylib
  Referenced from: /usr/local/bin/bash
  Reason: image not found

Resolved With:

ln -s /usr/local/opt/readline/lib/libreadline.7.dylib /usr/local/opt/readline/lib/libreadline.6.dylib

Primary Key Validation Errors on Test Runs

Having the testing blues? Has your shiny new table started throwing validation errors the next time you run your tests? I have good news for you, this is an easy fix! You forgot to update the 'testDb/tables' file. Go add your table in the appropriate referential point (e.g. if it depends on Vessel it needs to be below Vessel in the list)

Exporting Data from Postgres

\copy (SELECT * FROM "PhinDeviceSamples" WHERE EXTRACT(YEAR FROM sample_date) = 2017 AND EXTRACT(MONTH FROM sample_date) = 1 ORDER BY sample_date ASC ) TO samples-2017-01.csv CSV DELIMITER ',';

Data has been stashed in the phin-data-exports AWS S3 bucket. The CSV files are zipped before uploading to save space.

Shopify

If you're testing stuff using the Shopify API you should know there are 2 stores: the production store found at chemicals.phin.co and the test store at /phintest.myshopify.com. In stage, all API calls go against phintest.myshopify.com. You'll need access to the production or test stores to see if things are working.

Automatic Subscription Renewal Testing

You should use the subscription editor to drive states. You can find it at https://github.com/PhinCo/sandbox/tree/master/subscription_editor along with the test cases.

Chemical choosing

Chemicals are chosen in the following scenarios.

Scanning a chemical

• Determine if a chemical matches the intended chemical purpose/action type

Rendering skus for a protocol

• Determine which skus match a protocol based on chemical purpose (Should this include vessel type?)

Find best fitting chemical using chemical purposes and vessel type

• Vessel protocol changes
• Building a recommendation for an action type
• Validating and healing chemical lists
• Returning chemicals for a vessel as part of an API response
• Dosing recommendation app

Find chemical from inventory

• List ordered products for a protocol (This does not account for vessel type which matches expectations of the tests. Should it?)
• Allocate inventory based on chemical purpose and vessel type
• Determine the quantity of chemicals for a given action type in the inventory

Find all retail chemicals with matching chemical purpose for a given vessel type and manufacturer