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?