Smart Payment Execution Date
Implementation of a REST API to guess the transfer execution date according to the business rules and holidays.
Payments can be instructed for the date payment is Executed (execution date) or received by Beneficiary (delivery date). Where Delivery Date is defined, the Platform will calculate the correct Execution Date so the Beneficiary receives funds on time.
Problem Description
A customer need to transfer an amount of money to another customer, placed in a certain countries. They want the money to be in the destination bank at a specific date. This service will predict when the transfer needs to be sent in order to have the money at that specific date in the destination. The service will use country specific rules to predict the time that the transfer will take, and also the holidays between the start and the end dates.
Background
Many manual processes are in use in Mass Payments to work around current functionality. These should be eliminated so that all payments and processes can be handled using the global operations model for the Ebury Group with appropriate extensions and enhancements.
Different MP processes to be reconciled include: * Manipulating Value Date so payments can be pre-processed ahead of execution date. * Manipulating payment files to correct value date and other details for payment providers.
Solution
The service will implement a REST API service. The framework proposed is FastAPI in python. The company stack includes python as the main programming language, so there are some available frameworks.
- FastAPI: high performance and light framework, with API autodocumentation, used for microservices.
- Flask: micro-framework like FastAPI, but it does not generate autodocumentation.
- Django: mostly used when a service depends on a database, needs a simple admin interface or a web GUI.
According to this, FastAPI is lighter, generates autodocumentation and is very easy to use for implementing just a few endpoints as we need.
Data Store
The data store could be a standard relational database or a in-memory key-value store.
Redis is the recommended store, based on the level of performance needed by this service and to reduce complexity in the required infrastructure.
Here are some notes about each option:
* Relational database
Holidays and Cutoff times could be stored in relational database tables, like MySQL or PostgreSQL servers. There will be only a few number of tables in the system, and most of the time, these tables will be cached in the database server, so the performance will not be affected significantly. On the other hand, this option need extra configuration in the production environment, and we will not use all the features of the database server, like user management for accessing the database.
Choosing this option, we need to add tables for storing holidays and cutoff times, like this:
Holidays
--------------------------------------------------------------
| id | country_code | date | name | type |
--------------------------------------------------------------
| 1 | ES | 2020-12-25 | Navidad | H |
--------------------------------------------------------------
| 2 | ES | 2021-1-1 | Año nuevo | H |
--------------------------------------------------------------
| 3 | UK | 2020-12-25 | Christmas | H |
--------------------------------------------------------------
| 4 | US | 2020-7-4 | Independence day | H |
--------------------------------------------------------------
| 5 | ES | 2020-11-28 | Saturday | W |
--------------------------------------------------------------
Cuttoffs
-----------------------------------------------------------
| id | currency_code | time | days | corridor |
-----------------------------------------------------------
| 1 | EUR | 15:00 | 0 | Barclays UK |
-----------------------------------------------------------
| 2 | USD | 19:00 | 1 | Citibank |
-----------------------------------------------------------
| 3 | USD | 19:00 | 1 | Barclays |
-----------------------------------------------------------
CuttoffCountries
-------------------------------------
| id | cutoff_id | country_code |
-------------------------------------
| 1 | 1 | es |
-------------------------------------
| 2 | 1 | uk |
-------------------------------------
| 3 | 2 | us |
-------------------------------------
* Redis server
Using a in-memory store like redis will add complexity to the implementation, but will add better performance. The access will be faster, but we need more code to do the needed queries. A persistence strategy must be configured in the redis server, to keep data when restarting the service, and that will affect the server performance.
https://redis.io/topics/persistence
Redis can be used to store relational data in sorted sets in memory:
For holidays, we can use sorted sets using the date as score, to have the list of holidays sorted by date. We can keep there the hash index of the full holiday object:
'holiday:es': (ordered set as index)
[
{ value: 'holiday:es:2020-01-01', score: 20200101 },
{ value: 'holiday:es:2020-01-06', score: 20200106 },
]
'holiday:es:2020-01-01' : (individual holiday object)
{
date : '2020-1-1',
name : 'New Year',
country_code: 'es'
type: 'H'
...
]
'holiday:es:2020-01-06' : (individual holiday object)
{
date : '2020-1-6',
name : 'Epiphany',
country_code: 'es'
type: 'H'
...
]
--------------------- holidays:es -------------------------
| holiday:es:2020-01-01 | holiday:es:2020-01-06 | ... |
-----------------------------------------------------------
| |
--------------------- ---------------------
| date: 2020-01-01 | | date: 2020-01-06 |
| name: New Year | | name: Epiphany |
| country_code: es | | country_code: es |
| type: H | | type: H |
| ... | | ... |
--------------------- ---------------------
We can get all the sorted holidays by country code, and then access each individual object from the index. There will be a sorted set for each country code.
For cutoffs, we can store each object by currency_code:
'cutoff:eur':
[
time: '15:00',
days: 0,
corridor: 'Barclays UK',
countries: ['es', 'uk']
]
'cutoff:usd':
[
time: '19:00',
days: 1,
corridor: 'Citibank',
countries: []
]
GET /api/v1/holidays
200 response:
[
{
"date": "2019-12-25",
"name": "Christmas",
"country_code": "US",
"id": 7
}
]
Errors:
422 - Validation error
- Country code needed as a filter parameter.
POST /api/v1/holidays
Request payload:
{
"date": "2019-07-4",
"name": "Independence day",
"country_code": "US"
}
200 response:
{
"date": "2019-7-4",
"name": "Independence day",
"country_code": "US",
"id": 8
}
Errors:
422 - Validation error:
- valid country code
- valid date
- do not allow repeated records (country_code and date)
PUT /api/v1/holidays/{id}
Request payload:
{
"date": "2019-08-24",
"name": "modified",
"country_code": "US"
}
200 response:
{
"date": "2019-08-24",
"name": "modified",
"country_code": "US",
"id": 8
}
Errors:
422 - Validation error:
- valid id
- valid country code
- valid date
- do not allow repeated records (country_code and date)
DELETE /api/v1/holidays/{id}
200 response:
{
"date": "2019-1-6",
"name": "Epiphany",
"country_code": "UK",
"id": 9
}
Errors:
422 - Validation error
- valid id
- record exists
The service will provide CRUD operations to add/modify cutoff times.
GET /api/v1/cutoffs
200 response:
[
{
"currency_code": "US",
"time": "13:30",
"days": 0,
"corridor": "Source account"
"id": 1
}
]
Errors:
422 - Validation error
- Currency code needed as a filter parameter.
POST /api/v1/cutoffs
Request payload:
{
"currency_code": "FR",
"time": "15:00",
"days": 0,
"corridor": "Source account"
}
200 response:
{
"currency_code": "FR",
"time": "15:00",
"days": 0,
"corridor": "Source account",
"id": 2
}
Errors:
422 - Validation error
- valid currency code
- valid time
- valid corridor
- do not allow repeated records (currency code and corridor)
PUT /api/v1/cutoffs/{id}
Request payload:
{
"currency_code": "UK",
"time": "16:00",
"days": 0,
"corridor": "Source account"
}
200 response:
{
"currency_code": "FR",
"time": "15:00",
"days": 0,
"id": 2
}
Errors:
422 - Validation error
- valid id
- valid currency code
- valid time
- valid corridor
- do not allow repeated records (currency code and corridor)
DELETE /api/v1/cutoffs/{id}
200 response:
{
"currency_code": "FR",
"time": "15:00",
"days": 0,
"id": 2
}
Errors:
422 - Validation error
- valid id
- record exists
Holidays will be initially populated from third party API's. Look at the Caveats section
A main endpoint will be provided that will return the calculated date to do the transfer. This endpoint will need some input parameters, like country origin and destination, amount, desired end date, etc. There will be also a bulk version of this endpoint, that receives a list of selected operations:
GET /api/v1/smart_date
Request payload:
{
"currency_code": "USD",
"amount": "1500",
"corridor": "Barclays UK",
"delivery_date": "2020-11-30"
}
200 response:
{
"currency_code": "USD",
"amount": "1500",
"corridor": "Barclays UK",
"delivery_date": "2020-11-30",
"execution_date": "2020-11-25"
}
Errors:
422 - Validation error
- invalid currency code
- invalid delivery date
GET /api/v1/bulk_smart_date
Request body:
[
{
"currency_code": "USD",
"amount": "1500",
"corridor": "Barclays UK",
"delivery_date": "2020-11-30"
},
{
"currency_code": "EUR",
"amount": "12300",
"corridor": "Citybank",
"delivery_date": "2020-10-15"
}
]
200 response:
[
{
"currency_code": "USD",
"amount": "1500",
"corridor": "Barclays UK",
"delivery_date": "2020-11-30",
"execution_date": "2020-11-25"
},
{
"currency_code": "EUR",
"amount": "12300",
"corridor": "Citybank",
"delivery_date": "2020-10-15",
"execution_date": "2020-10-13"
}
]
Errors:
422 - Validation error
- invalid currency code
- invalid delivery date
The service can be distributed via docker container. For high availability, we can instantiate as many docker containers as we want, and all of them will access the same redis instance. The data store will be centralized.
Execution date calculation
Calculation of the release date should take into consideration holidays and cutoff times. From those entities we can get the cutoff time, holidays and value date that indicate how long it takes for the money to arrive at the destination if the cutoff time is met. With this information we will be able to determine which date the payment has to be released so it arrives on the date client's established.
Note: the cutoff times are based in UK time
Example:
Sell= USD / Buy = INR
Cutoff time payment INR = 14.30 UK time
Client indicates delivery date = 30th October
If we release the payment today 28/10/2020 (Wednesday, not weekend, not bank holiday) at 15:00 Spanish time (14.00 UK time, before cutoff). Then, the estimated delivery date will be 30/10/2020 as per client requested, if it's not a bank holiday in India. If payment was release yesterday (not weekend, not bank holiday) at 20:00 Spanish time (19.00 UK time, after cutoff). The estimated delivery date will be also 30/10/2020. If it's not a bank holiday in India.
However if payment is released today at 19.00 Spanish time, estimated delivery date will be 2/11/2020 Monday if it's not a bank holiday in India, not being able to comply with the clients expectation.
The process would be like this:
- GET /api/v1/smart_date with the params:
{
"sender_country_code": "US",
"receiver_country_code": "IN",
"amount": "1500",
"delivery_date": "2020-10-30"
}
Example 1: - The service takes the current date, for example, Wednesday, 28/10/2020, at 12:00 UK time - Check that 12:00 UK time is before cutoff (14:00) for that sender country. - Take the cutoff value for that country, in this case: 2 days. - Check that 30/10/2020 is not holiday/weekend in receiver country. - We substract 2 days from the delivery_date, so the execution date will be 28/10/2020, before 14:00 UK.
Example 2: - The service takes the current date, for example, Tuesday, 27/10/2020, at 19:00 UK time - 19:00 UK time is after cutoff for that sender country (14:00), so we have to add 1 day to cutoff days value. - The cutoff for this country is 2 days, so we have to substract 2+1 days to the final result - Check that 30/10/2020 is not holiday/weekend in receiver country. - We substract 2+1 days from the delivery_date, so the execution date will be 26/10/2020, before 14:00 UK
Example 3: - The service takes the current date, for example, Tuesday, 28/10/2020, at 18:00 UK time - 18:00 UK time is after cutoff for that sender country (14:00), so we have to add 1 day to cutoff days value. - The cutoff for this country is 2 days, so we have to substract 2+1 days to the final result - Check that 30/10/2020 is not holiday/weekend in receiver country. - We substract 2+1 days from the delivery_date, so the execution date will be 26/10/2020, before 14:00 UK - The result is in the past, so the execution could not be performed on time
Note: There could be different countries involved in the same transaction. So we need to store the relations between senders and receivers, to know which countries are involved in the operation, to take care of the holidays/weekends for those countries.
Alternatives
Following the ebury's stack, the service could be implemented using Flask or Django frameworks, that also work with python. Both of these frameworks are too heavy for the scope of this project.
Caveats
To initially populate the database, we need a list of holidays for each country.
This list can be retrieved with third party API's. These are some of them:
holidayapi and calendarific have a very simple python library to access the API.
Although calendarific does not support python 3, the library is so simple that we could easily adapt it to support it. The price and the supported countries should be taken into account to choose one of them.
There’s no country list for this scope. In the future, we could use an external service to access the list of available countries and/or currencies.
In this service, we will allow creation of holiday records without country validation. We can delegate the validation of country codes to a python library (https://pypi.org/project/pycountry/) or just allow to create holidays only for countries that already exist in the database. Same for currencies.
If we need to add a new country or currency in out local database, an admin will access the instance and will create the records manually.
This service will only take in account the payment days for cutoff times. In the future could be extended to use also trade cutoff times.
Integration of this service within other Ebury services and business is still on definition phase, this definition will be used to a first phase of development where logic will be tested. The definition process will include how to integrate trades creation for setting desired date on payments and how to set up BOS platforms (aka Ebury's BOS and Frontierpay's BOS) .
Operation
N/A
Security Impact
This service will not use authentication. The security will be handled from
infrastructure.
Performance Impact
The new service will support increasing global reach for payments and Mass Payments business growth to 1,000,000 per month being able to calculate payment release dates accurate and fast
Developer Impact
N/A
Data Consumer Impact
Data can be stored in a relational database or in a key-value store like redis. If redis is the desired store, we need to configure in order to persist data.
Deployment
As far as we know, this service could be deployed as a single docker container.
Dependencies
Business rules must be defined in order to implement this service.
Data stores should be chosen before coding this service.
Third party API libraries for holidays should be chosen before starting coding.
References
[fastapi](https://fastapi.tiangolo.com/