DPL-046 Biosero - maintainance tasks to support new cherrytrack service

[harrietc52]

Describe the Housekeeping

Test existing endpoints

• /beckman/failure-types
• /beckman/robots
• /cherrypicked-plates/create
• /cherrypicked-plates/fail
• /plate-events/create
• /plates/new

Required for SAT

• Rename EVENT_DESTINATION_CREATED -> completed
• Remove all things EVENT_SOURCE_ALL_NEGATIVES
• update generation of testing data script to pull picked samples from mongo (@andrewsparkes )
• Update PostMan (Lighthouse endpoints)
• Add index samples collection in mongo for lh_sample_uuid
• check events message include user/robot? NOT NEEDED, REMOVED
• Remove robot and user id from event parameters
• Merge cherrytrack changes into develop and deploy to UAT
• Deploy lighthouse dev code branch into UAT
• Drop and recreate UAT database from cherrytrack
• Integration test run to check all working

Post SAT todo's/ Required for LIVE:

• Add lh_sample_uuid Index to PROD mongo samples_collection (add to Crawler) ready to deploy @andrewsparkes
• Make event names consistent - then email Rob (and requires URS update) - decided against this on 15/6
• Add robots as users to labwhere in PROD
• Add protection to reset script - DANGER to prod (PR here)
• Add the call to Labwhere to set source plates sent to output stacks for specific event types = source_completed and no_pickable_samples

Nice to Have:

• Security - BioSero -> Cherrytrack DB (check biosero DB permissions on stored proc or specific tables in DB - psd_admin has GRANT)
• Integration test - E2E via Docker (post SAT)

DONE

• Backups with DBA (Done: email David Harper re: backups / frequency and replication. Todo: Rich L to confirm about Tier 1) @andrewsparkes
• Security - Lighthouse -> Cherrytrack (GET only - for first release just use HTTPS, but no API key) @pjvv
• Security - BioSero -> Lighthouse (POST events HTTPS, including API key per env) add api key to lighthouse (flask default behavior)
• Deployment DPL-023-1 @pjvv
• Update PostMan (Cherrytrack endpoints)
• Self contained deliverables (docker-compose.yml in Cherrytrack) @pjvv
• Write access tests from BioSero to the DB

[andrewsparkes]

Current GRANT on database for Biosero account:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW ON psd\_cherrytrack\_uat.* TO biosero_rw@%

We could restrict them to just use stored procedures, or restrict writing to specific tables, or whatever we wish. The psd-admin account has GRANT permissions.
N.B. Note the backslash escape characters required in the database name before the underscores but NOT required in the account name. MySQL quirk David H warned me about. Plus single quotes around the names and % (see edit as markup interprets them).

[andrewsparkes]

Response from David H (DBA) about database backups:

For all production MySQL databases, including the cherrytrack database, we set up an on-site standby (replication slave) and an off-site standby. Tier 1 databases have two separate on-site standbys and two off-site standbys.

The on-site standbys run in a different quadrant of the data centre to the primary, using a separate enterprise storage system. The off-site standbys also run on separate hardware and storage, obviously. Thus there are always at least three copies of the database running on independent hardware and storage.

We make a backup from the on-site standby every night, and the files are sent to tape via the NetBackup system. NetBackup retains backup filesets for two months by default, but it is possible to retain backups for a longer period if required for auditing or other statutory legal purposes. These backups provide a consistent snapshot of the entire database instance.

We also retain the replication log files that are generated by the primary database, and these can be used with the entire-database snapshots to restore a copy of the database to any point in the past two months.

We don’t have any regular scheduled down time periods. Any planned down time will be arranged with several days notice, with the agreement of the team which owns the database, and it can be done out of hours if the application cannot tolerate down time during normal working hours.

In the event of irreparable hardware failure in the data centre quadrant housing the primary database, we can switch over to the standby database very quickly. Bear in mind, however, that the SLA for Tier 1 services stipulates a return-to-service target of 4 hours, and only during normal working hours.

[andrewsparkes]

API Endpoint versioning:
Biosero on board with the idea. They have a GBG method configuration file which contains a single location for the base path of the URL. Rob H said he'll send us a copy so we understand what's in the file. So easy enough to match a specific method instance to a specific API version.