Schema support for db-migrate

• We decided to assign search_path to each microservice user (service and migration users) in terraform to resolve this need.

Developer Productivity

• Codeowners file and agreements

• Document observability tools and access

  • ELK: `kubernetes.container.image :"greenstand/treetracker-api:1.4.0"

    • deployed image version can be found by visiting the root path of any deployed api

    • (such as https://dev-k8s.treetracker.org/messaging/)

    • kubernetes.container.name: "treetracker-api"

    • u: admin p:xRrE6vZDUcRph%1P%0H

  • grafana (Performance monitoring)

• Document about how deployment to dev environment works.

• Figure out if dicpher can work with node16 or if there is a replacement (npm run decrypt)

  • Password for dipher is pinned

Improve Documentation

• https://www.conventionalcommits.org/en/v1.0.0/

• Sealed secrets: treetracker-infrastructure/sealed-secrets/scripts/create-database-secret.sh

Improve Microservices Template

• resolve all lint warnings

  • Update lint settings to our likely - are there rules we want to enable?

• remove co-mocha?

  • YES, remove from the template

• require lint errors to be removed to commit.

  • Reject on warnings in CI, but not in husky

  • Reject on errors in husky

  • Quaid adding his settings to microservice template

• Resolve all vulnerabilities

  • Check for vulnerabilities when a new PR comes in.

• Resolve dependencies errors for test files

  Copy

    import/no-extraneous-dependencies: [error, { devDependencies: true }]

  * https://eslint.org/docs/user-guide/configuring/rules

  • "import/no-extraneous-dependencies": [ "error", { "devDependencies": ["/*.test.js", "/*.spec.js"] } ]

Node 16 update & test

CI/CD for dev and test environment, node affinity

Fix linting errors if present, fix linting warnings

Review state of testing

Review state of open issues

Review state of open PRs

Fix security audit reports

• npm audit --production - list vulnerable packages while ignoring dev dependencies

• npm ls <package-name> - trace vulnerable packages to the base level packages that are using them

Typescript - review appropriateness of typescript for this service if the dev knows typescript

Microservices implementation structural review

Disable CORS for working on a microservice API with localhost

• Disable CORS for development by importing "cors" and then use it to disable cors for 'development' in each microservice

  if (process.env.NODE_ENV === 'development') { log.info('disable cors'); app.use(cors()); }

  EXAMPLE w/ cors:

  EXAMPLE w/ header options:

• Chrome extension that will let you disable CORS when you want without having to shutdown

  • Simple option: or - You can click the red button to make it active when you want to disable CORS.

  • Lots of settings, but you have to create an account:

• Try different browsers or the proxy setting for CRA.

• Create your own proxy server:

Helpful reading!

Debugging: Explain settings: CORS module settings:

treetracker.grower_account.wallet

• is the same as messaging.author.handle

• is the same as (public.planter.phone || public.planter.email)

messaging.author.handle

• does not necessarily have a corresponding treetracker.grower_account.wallet

• does not have a matching (public.planter.phone || public.planter.email) if it does not have a treetracker.grower_account.wallet

Handler

• HTTP Domain

• All error and success codes

• API payload validation

• Calls a service or a model function

Service

• Orchestration between services and the domain model

  • Database session

  • External APIs

  • Cloud Services (such as RabbitMQ or S3)

Model

• Domain logic

• Accesses repositories

• Allowances

  • We allow HTTP 404 errors to be thrown from within the model as a convenience for now

Repository

• Accesses the database, performs CRUD operations

• One repository for each database table in RDMS

1. We will control available authors manually at first, by populating the authors table

2. We will control access to admins using our normal process

3. For announce and survey messages, we look up associated Grower Accounts in the treetracker API, and then match those against enabled authors

   1. We need to extend this approach to respect the organizational hierarchy, which may mean a query service for grower accounts.

   2. A query service for grower accounts would also more easily respect the region filters

1. Update to 'verified' status requested to treetracker API

2. Start transaction against treetracker schema to insert a new capture record

   1. If the record already exists, that's OK, just accept.

3. Call legacy API and update capture (trees table record) to approved

4. Commit transaction on treetracker schema

5. Call field data API and update raw_capture record to approved

if (3) fails, we rollback the treetracker schema transaction, therefore the raw_capture record remains unprocessed, and will be reprocessed by an operator in the future, should bring data into consistency.

if (4) fails, same as if (3) fails, we no update. Will be reprocessed

if (5) fails, the record will be reprocessed

When we POST to captures/, send the UUID of the raw_capture to become the UUID of the approved capture record - these should always be unique.

• Allows for easy reprocessing when there are outages

• Allows for multiple concurrent POST messages to be collapsed and only create 1 capture record

Diagram for contract tables:

https://lucid.app/lucidchart/4beb07f7-0ef0-4bf0-b88d-5548c2cda03b/edit?page=0_0#

Unit Tests

Integration Tests

• Only access the API, do not perform database check

  • First write seeded tests for GET endpoints

  • Then write tests for create and update that use GET endpoint to validate functionality

• APIs should return created objects, and get queryable via these object Ids

• Seeding should be done using knex, and use the same seeding scenarios provided to the frontend developers

• Best Practices

  • Do specific seeding for each integration test, don't use a generic seed that isn't for the specific test

  • Objects posted to an API should be explicitly expressed within the integration test file (?)

  • Use async syntax

    • it('should do something', async function() { ...

  • Log request errors in test to facilitate debugging

    • if(res.error ) { console.log(res.error); }

  • Stub functions on a per-test basis

• Running integration tests

  • Use the test-integration-working command to specific a specific test file rather than running all

  • Use .only to specify a single test

  • Don't leave .only in place after getting the test to pass

• TODO

  • Need a solution to print response errors when expect fails on an error code

  • What tests are required?

    • Endpoint behaviors

    • How much validation?

      • Payload structure vs 404 responses

      • JOI tests could be unit tests

Field data schem

Ingestion Process by Entity Type

wallet_registration

Wallet registrations need to be stored in the field data schema in the wallet_registrations table, but they also need to be used to upsert (create or update) grower account records in the treetracker API. A wallet registration also needs the grower account id field populated.

Suggested workflow:

1. Bulk pack transformer calls PUT on grower_account in treetracker API for grower account with identifier that matches wallet

   1. If account exists, update

      1. Only update the name, phone, and email for now

   2. Else, insert

      1. Insert identifier, name, phone, email, and photo

2. POST to /planter path on bulk-pack-transformer-1 to populate legacy schema

3. Populate grower account id in wallet registration data, and POST into field data api

   1. If wallet registration id is already present, field data api will do nothing (return 200)

4. Return 200

device_configuration

device configuration records are simply inserted in v2, but in v1 they were upserted.

Suggested Workflow

1. POST device configuration payload to bulk-pack-transformer-1

   1. bulk-pack-transformer-1 returns 200 if already present

2. POST device configuration payload to field data API

   1. field data API returns 200 if already present

3. Return 200

session

session does not exist in the v1 bulk-pack-transformer.

Suggested Workflow

1. POST to session path on field data API

   1. field data API returns 200 if already present

2. Return 200

capture

1. POST capture data to bulk-pack-transformer-1

   1. If data exists this API returns 200, else a new record is inserted

   2. This API also propagates the record to field data API, where a queue message is emitted

2. POST capture data to field data API

   1. Since step 1 already propagated to this API, a 200 should be returned based on uuid

   2. Since 200 is returned, queue message should not re-emitted

Orchestration Diagram

Bulk Pack Format v2 :

Improved orchestration architecture

Deprecated proposal for orchestration

Nullable Columns for Field Data Tables

Session

• track_url

• organization

Wallet Registration

• phone or email is nullable, but not both

Raw Capture

• session_id is nullable for v1 endpoints only

• reference_id

• note

• extra_attributes

• rejection_reason

Device Configuration

• No nullable fields

Workflow Notes

Insert 'tree' as 'capture' will fail until corresponding device and registration records have been inserted, because there's no session Once device and registration records are inserted, then it is possible to insert a session id using SESSION UUID = device_identifier + planter_identifier In v2 bulk pack endpoint /v1/tree is responsible for ensuring that the session record exists for a v1 tree. This session can then be used as the session for inserting the v1 'tree' record as a v2 'capture' record

1. process sends tree record to /v1/tree in v2

2. v2 calculates SESSION UUID = device_identifier + planter_identifier using uuid-string

3. v2 checks for a session that matches this

4. If no session exists, it looks up device_configuration_id and wallet_registration_id using device_identifier and planter_identifier, and if they exist it creates a session record. If they don't exist yet, it fails until next time.

5. Assuming a session exists or was inserted in (4), we can now insert the capture record using his session uuid.

Release

Old versions:

• greenstand/bulk-pack-processor:1.2.7

• greenstand/bulk-pack-transformer:1.6.2

Goal: Admin panel to not read trees table directly. Build new treetracker service with API to create capture records.

Build a new service to be the source of truth of all approved captures.

This service is to own all the capture records along with tagging and other details created during the approval in the admin panel.

• Service is already created, known as treetracker-api.

• Some verbs (GET/POST/PATCH) may be missing but capture, tree, and planter resources have been defined.

• We may need to add additional search parameters as needed by other services or for domain goals.

On creation of a capture record, emit events indicating the creation of the capture record to a Topic. This event is meant for web-map to build a view of all approved captures.

• This appears to be implemented, but has not been tested or operated

The scope of this capture service is yet to be determined if it can own more than just capture records. We could call this as captures service or treetracker-capture-records.

• We added planter (ground_user) and tree to this service already. That will be the scope of the service. The service is called treetracker serivce, and is stored in the treetracker-api repository

Change Admin panel

Change Admin panel to use field data API instead of directly reading trees table for tree capture verification.

• Data populated in the admin panel verify tool, should ONLY be raw captures from the field data service that has not been verified (status=unprocessed)

• This will require the admin panel frontend team to split the current verify tool into a 'verification' tool and a 'capture edit' tool.

• Only verified captures can be 'edited' through the 'capture edit' tool, and only very limited fields can be edited. mostly this is tagging.

• Later, there will also be some tools for cleaning field data, but that's out of scope for now.

Admin panel to invoke capture service API to record the approved capture, update the trees table as usual and then update field data service to mark the data as approved/rejected.

• (1) POST to treetracker/capture

• (2) PATCH to the legacy admin panel API

• (3) PATCH back to the field service to mark as approved/rejected in field data schema.

• TODO: Think through what happens if one of these calls fails.

  • I think it's ok, it just causes some work to be repeated.

  • For instance, if POST to (1) succeeds but PATCH to (2) fails,

    • Then raw capture will need to be reverified (because (3) did not execute)

    • So the data will just be reprocessed by the operator in this case

  • We will probably resolve this by using an orchestration layer

    • The treetracker service itself can handle the orchestration, and also keep track of failed requests to (2) and (3) to retry.

    • This could also use a queue, or implement a separate service to handle the orchestration.

    • To some extent, this decision is up to the engineer implementing this step of the domain migration.

• Remove the feature that emitted event for approved captures from Change 1 earlier (in the legacy API). The reason we will write to trees table from admin panel (or orchestration layer) is for back-ward compatability with web-map.

Add a consumer for the capture creation events in the web-map layer and build a view for all captures in web-map specific db.

• This appears to already be complete. Need to be tested and operated.

Migrate data from trees table in main db to captures table in the new captures service of all approved tree entries.

• ETL job. Can be implemented as one-off airflow job.

Update token generation process and refer to ids from captures instead of trees as reference association.

• This refers to the token minting scripts, now being run in airflow

• capture_id for each token needs to reference the id of treetracker.capture rather than public.trees. This also requires updating the HATEOAS link in the token payload (wallet API) to point to the new capture service.

• Update all existing tokens to use treetracker.capture.id as capture_id

Wallet API service to emit events when transfer of tokens occur to be consumed by web-map layer for Impact owner view.

• The events consumed in web-map layer will trigger api calls to get the tokens impacted and update the capture view in the web-layer to assign the updated wallet owner.

• Requires endpoint to look up all tokens in any transfer by transfer id.

Trigger discussion about impact manager map (the view in web-map for planter orgs) in the web-map layer and its needs to shape and update milestone 3.

• Discuss how organizational hierarchy is captured in the webmap schema (JSON blob or table structure)

Address domain naming issues (variable and class names)

• Breaking changes will trigger a new major revision, and a new deployment mount point

  • Frontend engineers will be responsible for migrating to the new mount point

  • If database changes are necessary, if the service is not in production it's ok to make a breaking change. Otherwise check with engineering leadership.

• planter, grower, and fielduser should all become 'ground_user'

• planteridentifier should become 'ground_username'

• planter class, table, and API resource should become 'ground_user'

Capture Matching Release Project

Capture Match road map 2021: https://matthew-fullstackgis.gitbook.io/capture-matching/

Webmap query service

https://github.com/orgs/Greenstand/projects/36

1. Release treetracker API into all envs

2. Web map query schema & consumer into all env

   1. Release

   2. Validate data flow for raw captures

   3. Validate data flow for captures

   4. Validate or implement cluster generation

   5. Validate or implement

3. Implement web map for raw captures in field data schema

4. Finalize and Release Capture Matching Tool

5. Finalize and Release Earnings Tool

6. Finalize and Release Stakeholder Tool

7. Migrate admin panel users to keycloak

8. Migrate web map to captures table in treetracker schema

‘Ground user’ and ‘individual’ are synonyms. Also ‘person’

A mobile app user registers a wallet when using the mobile phone. Wallet registrations are uploaded along with their corresponding captures. A wallet is created for the wallet registration if it does not already exist. A grower account is created for each wallet, to track the identity of the person until they can be verified. Once a person is verified, a person record is created for them in the stakeholder system. Person records can receive payments for treetracker services.

Domain Services

Treetracker API

Source Code: https://github.com/Greenstand/treetracker-api

Mounted Endpoint: https://{env}-k8s.treetracker.org/treetracker/

Internal Endpoint: http://treetracker-api.treetracker-api

Field Data API

Source Code: https://github.com/Greenstand/treetracker-field-data

Mounted Endpoint: https://{env}-k8s.treetracker.org/field-data/

Internal Endpoint: http://treetracker-field-data.field-data-api/

Stakeholder API

Source Code: https://github.com/Greenstand/treetracker-stakeholder-api

Mounted Endpoint: https://{env}-k8s.treetracker.org/stakeholder/

Internal Endpoint: http://treetracker-stakeholder-api.stakeholder-api/

Earnings API

Source Code: https://github.com/Greenstand/treetracker-earnings-api

Mounted Endpoint: https://{env}-k8s.treetracker.org/earnings/

Internal Endpoint: http://treetracker-earnings-api.earnings-api/

Regions API

Source Code: https://github.com/Greenstand/treetracker-regions-api

Mounted Endpoint: https://{env}-k8s.treetracker.org/regions/

Internal Endpoint: http://treetracker-regions-api.regions-api/

Messaging API

Source Code: https://github.com/Greenstand/treetracker-messaging-api

Mounted Endpoint: https://{env}-k8s.treetracker.org/messaging/

Internal Endpoint: http://treetracker-messaging-api.messaging-api/

Map Tile Service

Source Code: https://github.com/Greenstand/node-mapnik-1

CDN Endpoint: https://tiles1.treetracker.org/, also tiles2, tiles3, tiles4

Mounted Endpoint: https://{env}-k8s.treetracker.org/tiles/

Grower Account Query Service

Source Code: https://github.com/Greenstand/treetracker-grower-account-query

Mounted Endpoint: https://{env}-k8s.treetracker.org/query/

Internal Endpoint: http://treetracker-grower-account-query.query/

Web Map Query Service Consumer

Source Code: https://github.com/Greenstand/webmap-query-service-consumer

This queue message consumer exposes no API

Bulk Pack Services

Bulk Pack Consumer

Source Code: https://github.com/Greenstand/bulk-pack-consumer

This queue message consumer exposes no API

Bulk Pack Processor

Source Code: https://github.com/Greenstand/bulk-pack-processor

This scheduled job exposes no API

Bulk Pack Transformer V1

Source Code: https://github.com/Greenstand/bulk-pack-transformer

Mounted Endpoint: not mounted

Internal Endpoint: http://bulk-pack-transformer.bulk-pack-services/

Bulk Pack Transformer V2

Source Code: https://github.com/Greenstand/bulk-pack-transformer-v2

Mounted Endpoint: not mounted

Internal Endpoint: http://bulk-pack-transformer-v2.bulk-pack-services/

Legacy Services

Admin API

Source Code: https://github.com/Greenstand/treetracker-admin-api

Mounted Endpoint: https://{env}-k8s.treetracker.org/api/admin/

Internal Endpoint: http://treetracker-admin-api.admin-api/

Web Map API

Source Code: https://github.com/Greenstand/treetracker-web-map-api

Mounted Endpoint: https://{env}-k8s.treetracker.org/webmap/

Internal Endpoint: http://treetracker-webmap-api-ambassador-svc.webmap/

Cloud Services

Airflow API

CKAN API

Migration Sequence

1. Migrate all legacy 'tree' table data into the fielddata.raw_capture table

   1. Includes creation session and device records for these captures

2. Admin panel application: split capture verification tool from capture search tools.

3. Go through the migration process for capture verification

   1. Documentation: https://github.com/Greenstand/system-design-docs/blob/master/domain-migration/project_plan.md

   2. Steps

      1. Milestone 1: Get rabbitmq verification messages functioning all the way through into production

         1. Get it working on dev infra

         2. Release and test on test infra

         3. Release and test on beta and prod infra

      2. Milestone 2: Update admin panel to move approved data from field_data.raw_capture to treetracker.capture in the verification process (using the treetracker API), but for capture search still pulls from legacy database (using the legacy API)

         1. Get it working on dev infra

         2. Release and test on test infra

         3. Release and test on beta and prod infra

4. Migrate all remaining approved legacy 'tree' table data into the treetracker.capture table

   1. Apply to dev

   2. Apply to test

   3. Apply to prod

5. Consolidation step to fix all planting organization assignments

   1. Priority between planting org per planter, per tree, and reported in field data to be determined

   2. Clean up all capture records to have valid organization assignments in the stakeholder tool.

6. Update admin panel to using treetracker API for capture search tools.

7. *Now, ready to get the capture matching tool working in deployment*

8. GIS task: Identify very small geographic area in Freetown as a pilot for capture matching, and supply this as a spatial shape file (.geojson or .shp)

9. GIS task: Identify all the capture records in the small geographic area that are the initial captures of the tree.

10. Extract production data from small geographic area and add that data to dev and test database.

11. Data task: insert treetracker.tree records for each initial capture. Now we have trees to capture match against.

    1. Perform in dev, test, and prod

12. Implement capture matching frontend feature to allow filtering trees and captures by named geographic area. We will filter for initial use by the small geographic area defined above, which should be loaded into the regions API.

13. Now we can deploy the capture matching tool to the beta infrastructure and try it out.

Notes:

1. Some legacy planter registrations reported to not have device identifier.

   1. This is inconvenient but not fixable with the v2 bulk pack format

**Capture**

**Tree**

**Tag**

**Capture_Tag**

**Tree_Tag**

**Grower_Account**

**Status Enumeration** active deleted Session - Field Data Schema Device Configuration - Field Data Schema