Review request

Expense approval tool, Bolt modelled the state as two booleans

Solved · 12 views · asked 2 months ago by tomvibes · edited

Repo or live app

github.com/tomachterberg/expense-flow ↗

Unsure about: Structure

Agency of 20, expenses go from submitted to approved to paid. Bolt gave expenses an is_approved and an is_paid boolean. We now have rows that are paid but not approved, which should not be possible, and nobody can tell me when or by whom either flag flipped. Is this worth a migration or am I being precious?

Comment

2 answers

Marked as helpful by the asker

ines_data2 months ago · edited

Worth the migration, and it is a small one. Two booleans encode four states of which one is nonsense, and the database has no way to refuse it.

One column with a constraint instead:

alter table expenses add column status text not null default 'submitted'
  check (status in ('submitted','approved','paid','rejected'));

Backfill from the booleans, then drop them. Now 'paid but not approved' is unrepresentable rather than merely discouraged.

For the second half of your question, add an expense_events table with expense_id, from_status, to_status, actor_id, created_at and write a row on every transition. Approvals are exactly the kind of thing someone asks about a year later, and a status column alone cannot answer it.

Comment

olu_backend2 months ago · edited

If you want the transitions themselves enforced, a trigger keeps it in one place:

create function check_transition() returns trigger as $$
begin
  if (old.status, new.status) not in
     (('submitted','approved'),('submitted','rejected'),('approved','paid')) then
    raise exception 'illegal transition % -> %', old.status, new.status;
  end if;
  return new;
end $$ language plpgsql;

That way a future script or a stray dashboard edit cannot skip approval either.

Comment

Keyboard shortcuts

Expense approval tool, Bolt modelled the state as two booleans

Repo or live app

2 answers

Your answer

Expense approval tool, Bolt modelled the state as two booleans

Repo or live app

2 answers

Related

Your answer