RDMS State Machine

State Machine definition

A system that transitions input from its current state into a desired state. The system is robust enough to handle failures at any phase of the transition including both automatic recovery and notifying appropriate stakeholders when manual intervention is required. Also, robust enough to survive a natural disaster, accidental destruction by its maintainer, and resistant to malicious actors.

Why RDMS

Very intuitive high-level tooling. We can build fast and be confident in our solutions.

TODO need better example once I implement this for real

Your Item you want to change

Status queue database pattern:

Get latest status SELECT ds.*, slot.env, slot.max_slot_id FROM deployment_status ds JOIN ( SELECT env, max(deployment_slot_id) AS max_slot_id FROM deployment_slot GROUP BY env ) slot ON ds.id = slot.??? WHERE ds.status = 'completed';

Get anchor status (completed) SELECT ds.*, slot.env, slot.max_slot_id FROM deployment_status ds JOIN ( SELECT env, max(deployment_slot_id) AS max_slot_id FROM deployment_slot GROUP BY env ) slot ON ds.id = slot.???;

Keep inserting statuses to change the status

INSERT INTO deployment_status (id, env, status, time_stamp, deployment_slot_id);

benefits of deployment_slot table would be 1 id instead of 3 (product_type, bom_type, channel, status, bom_id) second table deployment_status (id, env, status, time_stamp, deployment_slot_id);

Your item statuses that represent individual states

We could also solve this by keeping two tables, one for just recording history and the other for updating the actual state. You would have to ensure changes across both tables at once would be atomic with transactions but probably a worthy strategy.