psQL (payment support query language)

Inspiration

Handling manual support cases in the payments domain can often lead to quite complex scenarios where we need to execute SQL queries that sometimes look like being obfuscated for security reasons.

Such queries can be hard to understand and are error prone as well. They are executed manually via a database client, thus no triggers are fired (e.g. for accounting, outstanding amount changed etc).

It is also not possible for non devs to provide such solutions, so we block a lot of resources here as well.

What it does

The goal is to find a more descriptive and semantic way of providing necessary instructions to fix common payment issues. This instructions will then be executed on the target system where all relevant triggers and events will be fired.

ToDos

determine atomic actions that are sufficient / necessary to cover common cases
determine required parameters
explore options how we might represent those actions (DQL-style, json, excel-sheet)
does the solution work for a single case and multiple (id IN (x,y,z))?
outline how a parser could work
is it easily extendable with new actions?
Bonus: Could be build a guided form for that?

Examples

UNMAP amount=2.99 FROM obligation=12, bank_transaction_part=678;
MAP amount=1.50 FROM bank_transaction_part=678 TO obligation=13;
REIMBURSE TO DEBTOR  amount=1.49 FROM bank_transaction_part=678;

or maybe as json for easier parsing

{
  "actions": [
      {
        "action": "unmap",
        "amount": 2.99,
        "obligation": 12,
        "bank_transaction_part": 678
      },
      {
        "action": "map",
        "amount": 1.50,
        "obligation": 13,
        "bank_transaction_part": 678
      },
      {
        "action": "reimburse to debtor",
        "amount": 1.49,
        "bank_transaction_part": 678
      }  
  ]
}