Type and SubType data from seatool

[mdial89f]

Background

We will soon add type and subtype to the list of fields we send and receive from seatool. When a user submits a new package, type and subtype will be transmitted. The user will select subtype and/or type from a drop down list on their form.

There's a relationship between authority, type, and subtype that we need to honor. In seatool, a given authority has a list of allowable types, which is a subset of all types. And for a given type, there is a list of allowed subtypes. These relationships are important to populating our frontend drop down menus. We will know the authority by the time the user reaches the form, but the type and sub type may not be known. So we need to present the user with type options for that authority, in a drop down. Then we need to present subtype options based on the type selection.

This discussion is about how we can best access the type and subtype information, including the relationships between them and authority.

A key design point to remember: the type and subtype information is held in separate seatool tables, with each type and subtype having a unique id. These tables and their ids aren't identical across dev/val/prod.

Considerations:

• Each record/event as it’s emitted from seatool has a type id and sub type id. It does not have a type and sub type name.
• New submissions will soon require type and sub types. It’s our understanding that there will need to be drop down menus for users to select a type based on their authority and a subtype based on their authority and type.
• The relationship between type id and authority... and the relationship between subtype id, type id, and authority... are stored in individual type and sub type tables in seatool. Their contents are not identical across environments.

Options

Option 1

Make type and sub type tables, or the parts we care about, identical in all environments; keep current approach of static maps in our source

• Pros
  • Code’s done
• Cons
  • We rely on others.
  • We are vulnerable to any drift in the tables
  • This won’t help us when we get to CPOC, so it’s not a global solution
• Path to done:
  • Talk to Praveen and Hannah.
  • Identify what authorities, types, and subtypes we care about.
  • Analyze those selections across dev/val/prod for drift.
  • Correct any drift.
  • Export the new maps to source and use them.

Option 2

Create a sinkTypes function that builds a type and a subtype index in opensearch. Have sinkSeatool only put the ID of the type and subtype in main. When a user opens a record in our system, an API call trades the type/subtype ids for names. Or, we modify getItem to include this info in its return, just like we do with changelog data. New submission drop downs are populated by an API call to the type/subtype index.

• Pros
  • Table diffs across environments is OK
  • No race condition between sinkSeatool and sinkTypes… only the frontend will need to make use of the type/subtype index.
• Cons
  • Type and subtype aren’t directly in the main index… not sure how ‘con’ that is.
• Path to done:
  • Create sinkTypes and sinkSubTypes functions.
    • Set each to trigger off the debezium topics for the type and sub type table
    • They each populate type and subtype index in opensearch
  • Modify getItem to take the type and sub type ids, call opensearch, and retrieve the type and sub type names… include in return
  • The 4 fields would query opensearch via the api to populate its dropdown

Option 3

Same as Option 2, but we modify ksql to include type/subtype data in the ksql event, so type and sub type name can be put into main.

• Pros
  • Table diffs across environments is OK
  • No race condition between sinkSeatool and sinkTypes… only the frontend will need to make use of the type/subtype index.
  • Type and subtype names are directly main, fwiw…. No separate api call to trade an id for a name and/or no modification to getItem
• Cons
  • Ksql modification
• Path to done
  • Modify ksql to include the type and sub type name in the ksql output
  • Create sinkTypes and sinkSubTypes functions.
    • Set each to trigger off the debezium topics for the type and sub type table
    • They each populate type and subtype index in opensearch
  • The 4 fields would query opensearch via the api to populate its dropdown

Option 4

Modify sinkSeatool to trade type/subtype ids for names by directly calling seatool. Still have a type and sub type index for the frontend drop down population.

• Pros
  • Table diffs across environments is OK
  • No race condition between sinkSeatool and sinkTypes… only the frontend will need to make use of the type/subtype index.
  • Type and subtype names are directly main, fwiw…. No separate api call to trade an id for a name and/or no modification to getItem
  • No ksql modification
• Cons
  • Performance; how expensive is it to call the database at least once per lambda invocation
  • Architecture; the sink is a consumer of Kafka events… calling out to a legacy db would be an anti pattern for sure.
• Path to done
  • Modify sinkSeatool to call seatool directly. It trades the type and sub type id for names. It has to do this for each record. Maybe the sink would dump the type and subtype table once per invocation, and reuse that output for its batch of 1000 records.
  • Create sinkTypes and sinkSubTypes functions.
    • Set each to trigger off the debezium topics for the type and sub type table
    • They each populate type and subtype index in opensearch
  • The 4 fields would query opensearch via the api to populate its dropdown

Option 5

Create a sinkTypes function that builds a type and a subtype index in opensearch. Have sinkSeatool call those indices to trade the type/subtype id for names. The type/subtype name is indexed to master. New submission drop downs are populated by an API call to the type/subtype index.
Note: this is the same as option 2, but we're trading id for name in sinkSeatool

• Pros
  • Table diffs across environments is OK
• Cons
  • Need to ensure sinkTypes has finished building its index before sinkSeatool begins processing
• Path to done:
  • Create sinkTypes and sinkSubTypes functions.
    • Set each to trigger off the debezium topics for the type and sub type table
    • They each populate type and subtype index in opensearch
  • Modify the sinkSeatool to call the types indices to trade id for name, and then add the name to the package's main index doc.
  • Implement a means of ensuring sinkTypes finishes ahead of sinkSeatool
  • The 4 fields would query opensearch via the api to populate its dropdown

---

This space is reserved for the output or decision from this discussion.

Option 3 was implemented.

The ksql appliance was updated to roll up types and subtypes in the ksql aggregate. This allowed our sink to not require a type/subtype lookup index, and lets us set the type/subtype name in the main index.

The type and subtype indices are still built, and used by the frontend. This drives the forms, when users choose possible values. These indices are kept up to date with any changes in real time, so the user's choices will always be valid.

[mdial89f]

UPDATE:
We've had discussions with product and data. The type/subtype tables are different enough across environments to warrant building a type/subtype lookup index.
The frontrunner options are 2 and 5. 2 is simpler from an infrastructure point of view, but 5 is easier for the frontend to work with.

No final decision yet, but tenatively it looks like this:

1. we should build a technical solution so our type and sub type mapping are always 100% correct in each environment.
2. we should also utilize a whitelist and/or blacklist as determined by the business, to further refine those options programatically, so the state submitter will only see a subset of options if desired by cms.

[mdial89f]

UPDATE: Option 3 was implemented.

The ksql appliance was updated to roll up types and subtypes in the ksql aggregate. This allowed our sink to not require a type/subtype lookup index, and lets us set the type/subtype name in the main index.

The type and subtype indices are still built, and used by the frontend. This drives the forms, when users choose possible values. These indices are kept up to date with any changes in real time, so the user's choices will always be valid.