Storing bank account data using Event Sourcing

Question

Trying to change payment related stuff in current project, but still couldn't decide on architecture of small part. I hope you can share your feedbacks.

Flow and state: We have payment account using which we send money (paypal, swift or iban accounts), each time user wants to (1) add/update his/her payment account, we store data to be updated in confirm_this_data_table.JSONB field (as it was in request), then we (2) send an email for confirmation, after (3) confirming this data we fetch it from JSON store and add it to payment_accounts table. User might have multiple payment_accounts.

Current state and its problem Lets say we have A,B and C payment_accounts. User edits account "A" and changes swift number. In our current solution this data will be replaced with new data, which means we lost historical data for audit, we probably send payment to old account, but we cannot prove it, because we are referencing account which is edited

Thinking about this Table:

• id (sequence)
• user_id
• account (lets say in jsonb, probably it doesnt matter in this context)
• confirmation_token (random string)
• uuid (uuid4 for uniquely identifying this payment_account)
• start_date
• deleted_at

Flow:

• user adds payment_account (new row created and returned token for confirmation, lets say uuid=abcd-efg)
• user confirms token (start_date will be updated with current_date which means account is now active)
• user updates this payment (again new row created, this time with old uuid=abcd-efg, because we are updating, token returned)
• user confirms token, now start_date set to current_date
• when user deletes this account with uuid=abcd-efg, we set deleted_at=current_date

Ideally I would like to have a solution with event sourcing (do not want to issue UPDATE query)

Do you see any problems with this solution?

Can you recommend solution which only INSERTs data, but at the same time doesn't require re-playing all events or requires sophisticated queries to retrieve data?

Answer 1

Can you recommend solution which only INSERTs data, but at the same time doesn't require re-playing all events or requires sophisticated queries to retrieve data?

It looks to me as though you have the right idea; but to spell it out: in many domains, there is a domain concept for "effective date". Not one property that is true forever and always, but rather one that has a limited lifetime (although the end point may not be known yet).

So you make that idea explicit in your model, which now includes an append only history; when you make your edits to the model, they are non-destructive, and you can recover the account history.

The approach isn't without its challenges: it's often the case that most of your use cases don't need all of the historical data. So there's some temptation to optimize the load of the data from the persistence store. That may be hard or easy, depending on what other constraints are involved (aka "it depends").