Database models for internal accounts
Database modeling and processes for internal, virtual and segregated accounts in Ebury.
Disclaimer: The level of detail in this RFC will probably go deeper than the levels allowed in the process, but making an exception for this it makes sense as complexity and implications on this developments are high.
Problem Description
Internal accounts were historically created in BOS, but as we want to split up BOS into several services to scale in performance and maintainability, we want to move the accounts out of BOS to ADS service, where the accounts domain belongs to.
More specific reasons for moving the accounts are explained in EUR Payment Execution via Swift Service (Barclays Frankfurt) RFC.
Background
Please read EUR Payment Execution via Swift Service (Barclays Frankfurt) RFC before going on here.
Naming glossary
- Payment: Outgoing money flow, debited to Ebury and credited to a beneficiary.
- Entry: Money flow, either credit or debit, for indicating money movements in accounts.
- Internal account: Accounts owned by Ebury and used for moving money, either payments or entries.
- Virtual account: Account proxying a parent (internally in banks, not in BOS db) account used for reconciliation purpose. All virtual accounts are link to a client, so all money movements are going to the client linked balances.
- Segregated account: Functionally is the same as a virtual account, but actually it is an internal account shared with one (and only one) client. This is transparent from client point of view.
- Customer account: Virtual account and segregated accounts.
- Settlement account (also known as collection account and pool account): Accounts to bind the entries to. They are a subtype of internal account.
- Funding account: Account where clients send funds to for fund in deals or payments.
Current implementations
There are two models in BOS for storing accounts:
BankAccount: Internals, segregated, beneficiary accounts, bank account approval..BankAccountMask: Virtual (automatic and manual) and segregated.
In BOS you will see filter(owner__internal=True, active=True) which actually
is the definition of internal account inside BankAccount model.
There are information stored inside BankAccount which is actually not account
information, it is information about how it is used, these are:
- Private.
- Main.
- Manual balance in client money.
- Sync account.
- Sync with fxsuite.
- Allow preload movements.
All this properties alongside with other logic and database data combined are the definition of "funding account", "source account", "settlement account", etc.
The models in ADS right now:
Note: There are missing tables about rules omitted to be focused on RFC target.
Some use cases of the accounts and potential queries in DB:
- All queries al filtering
active=True. - The most common operations over the accounts are actually list and get by id. This queries are performed for showing payments, entries in views, so it is queried as much as payments and other views in BOS/API/EBO are loaded.
- Funding account calculation: This is filtering against currency
filter(symbol='currency'). - Source account calculation: This is filtering against currency
filter(symbol='currency'). - List account with "Default bank entity":
filter(bank_entity__name=settings.DEFAULT_BANK_ENTITY). - Non segregated account: Looking for accounts that has not been already
segregated with
filter(bankaccountmask__isnull=True). Actually, not too relevant as this feature is not used too often. - Filters in list by account number or IBAN equal to some value
(Q(account_number=number.text) | Q(IBAN=number.text)). - For company transfers: Some ordering
order_by('currency', 'country'). - Entries export: Some ordering in accounts by account number for generate an xls.
Generally the most common operations but the CRUD basic operations are filtering by currency and by bank entity.
Segregated accounts
Right now in BOS, the flow for creating a segregated account is:
- Go to "Internal bank accounts" view.
- Create an internal bank account.
- Go to client page and add a "Assigned account".
- The form allows you to search a create a "assigned account" from a internal account.
Then internally BOS, create a BankAccountMask model link to the BankAccount (internal) with a relation. Then, by code logic the coherence in the data is manage, to keep both rows in different tables equal.
Then the BankAccount is used as a settlement account and the BankAccountMask is used for showing to the client the funding accounts.
For syncing BOS and ADS, there is a async Django signal on BankAccountMask save which is calling ADS to update/create the data.
This, when ADS project finish, the signal will disappear and the data will only exists in ADS.
Multi-currency
Because of the lack of validations on creating internal accounts, operations has started to create multi-currency accounts by duplicating the account but adding different currency for each one. Example:
ES12 1234 1234 1234 EUR
ES12 1234 1234 1234 GBP
ES12 1234 1234 1234 USD
Every field is exactly the same and there is not different data on account but the currency.
Then, when selecting accounts by currency in BOS (for selecting a funding account for example) the trick does the job and the account is used.
Solution
The v1 version mimic current behavior in BOS.
We create a new model EburyEntity just to unify what it is stored as bank entity information, so we help easily filling up accounts information.
This the most simple and straight forward solution as we don't have to redefine nor investigate anything.
Pros:
- We don’t fight BOS logic as we keep separately customer accounts and internal accounts definitions.
- We don’t fight BOS data inconsistency. Just a refactor in the BankEntity data.
- Scales better to changes, as when changing internal you will only affecting internal (smaller scopes).
Cons:
- We don’t fight BOS data inconsistency.
- There are more definition of "Account" in ADS when actually, in real world are the same. So, we will end up with data duplication and business logic to handle it.
- Adding new validations in accounts will need to double-implement it if affecting customer and internal accounts. For example: supporting multi-currency and potentially querying mixed data with customer and internal accounts.
Segregated accounts
Essentially we keep the same behavior we already have but it will be produced when sync data into BOS instead of in views on creation. No developments nor redefinitions need it.
Alternatives
v2 Inheritance modeling
Because of including the internal accounts we redefine the "Account" definition in ADS.
The properties that are store how accounts are used are not part of accounts domain, so it won't be move as part of model to ADS, they will be stored somewhere else.
Now customer accounts and internal accounts will be manage under the same entity "Account".
So, when you request /accounts API endpoint internal accounts will be listed too.
As creating a one level inheritance refactor requires extra developments and investigation, we build a modeling with two levels which then will probably (depending on investigation and changes during time) refactored to one level. Entity relation:
Note: don't pay too much attention to naming, this will be updating during development.
We will be removing SettlementAccount model to be replaced by InternalAccount.
Pros:
- Managing data consistency at database constraint layer.
- Handling different account types as the same one (querying or creating new logic with all them).
- Actually reflecting most of the real world that we can in the models.
Cons:
- Scope changes affecting customer and internal accounts.
- Errors/Incidents in the data layer will affect customer and internal accounts.
- Require extra developments and investigations to be able to do it.
- Too many inheritance levels (potential issues with libraries and to be able to change and extend models).
Segregated accounts refactors
Because of this change and unique in the IBAN we cannot longer store duplicated rows for internal and customer accounts. For solving it, we are going to just store one Account entity in ADS and it is going to be a SegregatedAccount.
Then in BOS, we are doing to keep same modeling, just mimic what BOS do right now but managing the accounts in the client page instead of the Bank account internal page. This will require creating new views for create and update the accounts.
When ADS project finish, the signal will disappear, so the sync model will looks like:
With future refactors internal and customer accounts will be removed from BOS. So, the segregated accounts will be selectable as a settlement accounts.
v3 One level inheritance
The v3 is actually the same as the v2 solution proposed but implying more developments.
This solution is the aim of the solution proposed but currently pretty conflicting to develop as there is a work in progress with the customer accounts in ADS and will need refactors in the code before being able to implement it.
Pros:
- Avoiding too many levels of inheritance is always good for maintainability.
Cons:
- Requires refactoring and unifying virtual, segregated and internal accounts and refactor current implementations.
- Scope changes affecting customer and internal accounts.
- Errors/Incidents in the data layer will affect customer and internal accounts.
Segregated accounts
Same as v2.
Caveats
Check "Cons" section in solution and alternatives.
Operation
NA
Security Impact
NA
Performance Impact
The table Account in ADS will be much longer, is will contain internal too.
This can cause issues when querying the table.
Currently there are ~1500 internal accounts and ~44K customer accounts. Not a big deal even merging them into one table.
Thinking about the future, customer accounts will be growth much more than internal. Right now we have ~50K clients. Let's say we grow x10 in internal accounts and we create 10 accounts for each client in Ebury: 15K internal account and 500K customer accounts. No critical numbers for ProstgreSQL.
The inclusion of account internal in Account table will not be a problem
for customer accounts but the opposite may be a problem in the future, as
customer accounts will be always much more than internal accounts, so we are
moving the potential issues in customer accounts to internal accounts.
Developer Impact
Dealing with customer accounts will mean dealing to internal accounts and vice-versa.
Data Consumer Impact
NA
Deployment
NA
Dependencies
NA