BrokerDeal Ebury Company Entity Branch Reporting
Problem Description
Ebury 'Company Entities' are legal instances having a well-defined geographical location and specific Terms and Conditions (T&C) each. Every single Ebury Client belongs to a Company Entity defined at on-boarding time, signing the Entity's T&C. On top of that Company Entities can have "sub-entities", called Branches.
A completer definition of Ebury company entities and branches can be found on the Ebury Company Entity Branches RFC
Up to this point Ebury has not been reporting the entity and branches for broker deals correctly, we are using the hardcoded value “EBU LP” as the entity for the report and there is no branch being reported at the moment.
Background
Ebury uses Quantum as its Treasury Management System, the input for this system are plaintext files in the QXT format.
Right now our reporting to Quantum is done in batches every 30 minutes.
In the case for BrokerDeals the process goes like:
- BOS gets a list of all the broker deals (either created, modified or deleted).
- BOS serializes the broker deal needed information for reporting into JSON.
- BOS calls FXSuite in order to perform the JSON to QXT conversion.
- FXSuite converts the JSON to QXT and uploads the QXT files to the Quantum FTP server.
The core logic for reporting (serializers on Step 2) lives inside the tms app on BOS.
Solution
The BrokerDeal Entity Branch project has been divided into five phases, the first phase is a tactical solution which will provide business with the ability to start reporting correctly the entity and branch for BrokerDeals as soon as possible. Subsequent phases will remove the tactical solution with a solution that fits the new Ebury 2.0 architecture.
This RFC will only focus on taking the Treasury Reporting functionality for BrokerDeals out of BOS, we also report on payments, trades, transactions, etc, those are not going to be covered by this RFC
Logic for broker deal entity and branch assignment
As long as a broker deal is not associated with a trade the entity and branch should be:
- Entity: EPUK
- Branch: EPUK-UK
After the broker deal is associated with a trade we have three cases:
A broker deal is associated with a single trade
This is the normal scenario and most common scenario.
- Entity: EPU LP (keep entity as it is right now)
- Branch: same as trade
A broker deal is associated to multiple trades
This scenario happens when we want to sell multiple trades of a customer and we simply group them together. This broker deal is then associated with multiple trades, each trade, even if for the same client, could have different entities and branches.
- Entity: EPU LP (we keep it as is right now)
- Branch: the same as the last trade associated to the broker deal
Mirrored broker deal for Ebury Broker deal
When a Ebury broker deal is created, an opposite/mirror broker deal is created and assigned to the dealset to expose our internal position (currently set to the entity "Trading Spot Book").
- Entity: EPUK
- Branch: EPUK-UK
Phase 1: Tactical solution
In this phase we will determine dynamically the entity and branch for a BrokerDeal during the BrokerDeal serialization on TMS within BOS itself. With the first phase completed we'll be able to start reporting to Quantum the entity and branch for BrokerDeals.
Phase 2: Introduce the Treasury Reporting Service
We will introduce a new service, the Treasury Reporting Service. The Treasury Reporting Service will be in charge of determining all the needed information for reporting broker deals to Quantum, including both the entity and branch for a broker deal.
Although, the real source of truth for entities and branches is SalesForce, for now there is no Gateway that would allows us to take that information directly into Kafka, so BOS is considered to be the source of truth in the meantime. Because of that the service will consume the events published from BOS via Kafka Connect on trades, broker deals, and client topics and store some of the event’s payload data in a local database.
As stated before, the entity and branch for a BrokerDeal depends on the entity and branch of the trades associated with that BrokerDeal. On top of that, the entity and branch for a trade depends on the entity and branch of the client of that trade at a given point in time.
After determining the entity and branch for a BrokerDeal the service will store the entity and branch on its local database and also emit a BrokerDealEburyEntity domain event which will contain all the information neeeded for reporting the BrokerDeal to Quantum, including it's entity and branch
The service will have a local database with at least these 4 tables for determining the entity and branch for the broker deals:
- Client entity history (intermediate table)
- Trades-Client (intermediate table)
- BrokerDeal-Trade(intermediate table)
- BrokerDeal entity branch(SQL View)
Other tables might be needed as well in order to be able recreate the functiontionality of the BrokerDeal TMS serializer on BOS.
Client entity history table
This table will hold the historical client entity and branch information for all clients in BOS. We will get this data from the Data team and import it into the database as a starting point and we can keep filling it with client’s entity and branch changes coming from BOS via Kafka Connect
We need the historical data to get the entity and branch for some type of trades, e.g drawdowns in the cases in which the client was migrated from one entity to another during the lifetime of the forward, since all drawdowns of the forward should have the same entity as the forward.
Trade Client table
This table will hold some of the trade information and client id for all the trades in BOS, we need the client id because the entity and branch for a trade is the client’s entity and branch on a given point in time.
This table will be populated exclusively with the information coming from the different type of trades topics via Kafka Connect:
- SpotDeal
- ForwardDeal
- Drawdown
- FXSettlement
- NDFSettlement
BrokerDeal-Trade tables
This table will hold the relationship between a broker deal and the last trade associated with that broker deal. We need this information because the entity and branch for a broker deal associated with multiple trades is the entity and branch for the last trade associated with the broker deal.
This table will be populated exclusively with the information coming from the BrokerDealAllocation table in BOS
BrokerDeal entity branch view
This view will hold the broker deal id, entity name and branch name for all broker deals in BOS
This view will be built by using the Trades-Client, BrokerDeal-Trade and Client entity history tables following the logic on Logic for broker deal entity and branch assignment
Phase 3: Introduce the Quantum Gateway
We will create a new Quantum gateway with the same functionality that the existing one on FXSuite.
The gateway will consume the BrokerDealEburyEntity event from the Treasury Reporting Service, and just log the QXT messages generated which will allow us to check that the gateway’s functionality is working as expected, the gateway will not contact Quantum in any way during this phase.
FXSuite will still be in charge of uploading the QXT file to Quantum.
Minor changes will be needed in FXSuite so that it starts logging the generated QXT messages which will allow us to compare the messages generated from the Gateway vs the messages generated from FXSuite.
Phase 4: Enable the Quantum Gateway
We will stop using FXSuite to convert JSON to QXT and replace it with the new gateway, the gateway will start uploading QXT files to the Quantum FTP server just like FXSuite used to do.
We will clean the entity and branch generation code from Phase 1 from BOS, remove the TMS code for serializing BrokerDeals and will also clean up the no longer used FXSuite code at the end of this phase.
Phase 5: Introduce the Quantum API
We want to add support for the Quantum API in the Quantum gateway.
Ebury’s Quantum instance is currently in the process of being upgraded to a more recent version. Once this is complete we will be able fully evaluate the capabilities of the Quantum API. The intention is to have both channels (SFTP & API) as options with the gateway determining which channel to use based on its logic around the type of data being sent and availability of the channel itself but the preference for future Quantum data will be via the API.
Alternatives
ksqlDB
For Phase 2, instead of storing the information on a local database to determine the entity and branch for a broker deal we could make use of the stream processing capabilities of ksqlDB and:
- Derive a table from the client’s topic in order to create the Client entity history table
- Derive a table from trade’s topics in order to create the Trades-Client table
- Derive a table from the broker deal’s topics in order to create the BrokerDeal-Trade tables
- Derive a table from the previous tables in order to create the BrokerDeal entity branch table
This solution would have the additional cost of setting up ksqlDB which is something we are not using right now.
Caveats
By creating a new service in charge for handling reporting of BrokerDeals, we are going to start repoting the BrokerDeals outside of the main 30 minutes batch generated by BOS. This could cause reporting issues on Quantum since it could be possible for the report for a BrokerDeal to be sent before the report for the Deal associated with that BrokerDeal.
In order to avoid this kind of issues some type of syncronization mechanism will have to be implemented, or the migration of trades to the Treasury Reporting Service and subsequently the API before enabling API requests for Broker Deals.
Security Impact
N/A
Performance Impact
N/A
Developer Impact
N/A
Data Consumer Impact
The Data team will be asked for the history of entities for all clients in BOS.
Deployment
N/A
Dependencies
N/A
References
N/A