Consolidate moves and move task orders into one database table
Currently, a moves
record is distinct from a move_task_orders
(MTO) record, and there
is no direct association between the two. Data entered by customers during move onboarding
must flow into the MTO correctly. If we don't get ahead of this now, the teams will
potentially implement different versions of the database, resulting in rework and
confusion later. Having a consistent and uniform data model will improve
collaboration and productivity, and will enable us to demonstrate end-to-end
capability.
Considered Alternativesβ
- Keep
moves
andmove_task_orders
in separate database tables - Consolidate
moves
andmove_task_orders
into a single table
Decision Outcomeβ
- Chosen Alternative: Consolidate
moves
andmove_task_orders
into a single table - Keep only the
moves
table and add fields to it to enable accurate representation of an MTO. - Below is the final state of the
moves
table. The "new" label means it was ported over from themove_task_orders
table. These new fields will need to be nullable for backwards-compatibility with the current production process.id
created_at
updated_at
orders_id
selected_move_type
status
locator
cancel_reason
show
contractor_id
(new)available_to_prime_at
(new)ppm_type
(new)ppm_estimated_weight
(new)reference_id
(new)
Definitions of fieldsβ
id
: A UUID primary keycreated_at
,updated_at
: The usual Pop timestampsorders_id
: A foreign key to the associatedorders
recordselected_move_type
: Allowed values are HHG, PPM, UB, POV, NTS, HHG_PPM (but only HHG and PPM appear to be used currently).status
: Allowed values are DRAFT, SUBMITTED, APPROVED, CANCELEDlocator
: This is a 6-digit alphanumeric value that is a sharable, human-readable identifier for a move (so it could be disclosed to support staff, for instance). The MTO'sreference_id
is similar in nature but is in adddd-dddd
format. Thereference_id
does serve currently as the prefix for payment request numbers. We likely donβt need bothlocator
andreference_id
if these tables merge. See Slack discussion.cancel_reason
: A string to explain why a move was canceled.show
: A boolean that allows admin users to prevent a move from showing up in the TxO queue. This came out of a HackerOne engagement where hundreds of fake moves were created. This defaults totrue
.contractor_id
: This was added to represent the prime contractor who will handle the move. This makes it easy to point the move to a different contractor in case it changes.available_to_prime_at
: a date and time field that indicates when the move is available for the Prime to handle. The presence of this field can be used to determine whether or not to display the move to the Prime.ppm_type
: currently used values areFULL
andPARTIAL
. This appears to be different from having theselected_move_type
in themoves
table bePPM
vsHHG_PPM
. See Slack discussion.ppm_estimated_weight
: this is being set by the Prime currently so we are keeping it for now.reference_id
: A unique identifier for an MTO (which also serves as the prefix for payment request numbers) indddd-dddd
format. There is still an ongoing discussion as to whether or not we need thisreference_id
in addition to the uniquelocator
identifier, so we are keepingreference_id
for now.
There will be future work to reconcile the ppm_type
and ppm_estimated_weight
fields when we reenter conversations with the prime.
Fields that we are not moving from move_task_orders to movesβ
is_canceled
: used to determine if an MTO was canceled or not. The moves table already has astatus
field with aCANCELED
option, so we can get rid ofis_canceled
and usestatus
instead.
Pros and Cons of the Alternativesβ
Keep moves and move_task_orders separateβ
+
Allows for the possibility of multiple MTOs for a single move.-
Increase the risk of code complexity and data duplication.-
Makes it more difficult to represent the move from the point of view of all parties: service member, TOO, Prime. For example, to find a move related to an MTO, you have to find theorder_id
that the MTO points to, then the move that points to that sameorders_id
(unless we add a foreign key to amoves
record from amove_task_orders
record)
Consolidate moves and move_task_ordersβ
+
It keeps things simple in the codebase because an MTO is essentially a move that is available to the Prime. All moves will require an MTO except in one specific scenario: when the service member chooses to handle the move on their own (PPM) AND they receive counseling from services and not from the Prime.+
A move can only have one MTO, and the information the MTO refers to also applies to a move, so it makes sense to only have one DB table.-
Because of the differences between both tables, the consolidation will require more effort and might cause breaking API changes.-
By not having a separate DB table for MTOs, there is a risk we might not be representing an MTO accurately. An MTO is a legal construct with specific requirements.
Definitionsβ
TOO - Task Ordering Officer. They are responsible for generating Task Orders and "ordering" shipments and service items such as crating, shuttle service and SIT. They are also a check on lines of accounting to make sure the correct ones are on the MTO so the Prime is paid from the appropriate bucket of money.
PPM - Personally Procured Move. When a service member chooses to handle the move on their own.
MTO - Move Task Order. Is similar to an order for goods from a contractor. In the case of MilMove, the TOO is ordering services from the Prime Contractor. When the Prime Contractor completes those services, they can request payment for those services. Every service the Prime undertakes must be "ordered." The government does this via a Move Task Order. It is the record of everything that is ordered (approved) for the Prime to do. The Move Task Order contains all the information about shipments, including approved service items, estimated weights, actuals, requested and scheduled move dates etc.