# example-bank-models
This is a library project - that contains all the sqlalchemy models and pydantic schemas - that are required to provide the required features of a Sponsor Bank.
## Persistent Objects - Design
### **account**
Table used - to maintain accounts for benefit programs. This table will also maintain the book balance, available balance and funds blocked for these program accounts.
| Attribute | Description |
| --------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| account\_number | Unique Index - will hold the Account that funds the benefit program
In the Sponsor Bank, this is the account number that will be debited for every disbursement
The corresponding credit will be either to the beneficiary\_account (if the beneficiary account is serviced by the sponsor bank itself)
OR
The corresponding credit will be to the clearing account Nostro (mirror of the sponsor bank's account with the clearing house)
|
| account\_holder\_name | Should identify the Benefit Program that uses this account |
| account\_currency | The currency in which funds are held in the account |
| book\_balance | This is the ledger balance in the account |
| available\_balance | This is the available balance (funds available for use for disbursements)
available\_balance = book\_balance - blocked\_amount
|
| blocked\_amount | This is the total amount that has been reserved (earmarked) by the program for specific purposes.
The G2P Bridge - blocks the entire envelope amount - for every disbursement\_envelope.
The sponsor bank - creates an Amount Block for every block request. These amount blocks are available in the table - fund\_blocks. The sum total of all records in fund\_blocks equals this blocked\_amount
|
### **fund\_blocks**
Table used - to maintain funds blocks for program accounts. Every successful funds block request will be identified by a "block\_reference\_no" and will have exactly one record in this table.
| Attribute | Description |
| -------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| block\_reference\_no | **Unique Index - for every block transaction.** Every request for an amount block - generates this unique Id. |
| account\_number | The account funding the benefit program |
| currency | The currency in which the account operates |
| amount | The amount that has been blocked under this block\_reference\_no.
The g2p-bridge subsystem requests an amount block for the total envelope amount.
This amount reflects that total envelope amount in this scenario
|
| amount\_released | The amount released - (partial) against this amount\_block
In the g2p-bridge scenario, every disbursement diminishes (releases) the amount block partially
Once all the disbursements are effected, the amount released will equal to the amount blocked. When this happens, the column - blocked\_amount in the account table - will become zero.
|
### initiate\_payment\_batch\_requests
When the g2p-bridge initiates a payment request for a bunch of disbursements, all the disbursements in a single request are treated as a single batch. A batch\_id is allocated to this group of disbursements. A batch\_id will have a single record in this table. Further downstream processing in terms of generating book keeping entries will be processed together for a batch\_id.
| Attribute | Description |
| ------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| batch\_id | Unique Index.
The G2P Bridge will initiate payments in batches.
Each API invocation (initiate\_payment) will result in a creation of a unique batch\_id.
All individual disbursements (payments) in that request will be grouped under this batch\_id.
|
| accounting\_log\_attempts | The actual payment itself (generating book keeping entries) is handled asynchronously. A celery beat picks up all PENDING batches and delegates the batch\_id to a task.
This attribute - keeps a count of these attempts.
|
| accounting\_status | PENDING, PROCESSED
A celery beat picks all PENDING records and delegates the batch\_id to a celery worker.
The worker picks up the individual payment records for this batch\_id and effects book keeping entries for the "remitting\_account"
|
### initiate\_payment\_requests
The payment requests initiated from g2p-bridge are stored in this table. A Celery beat producer later picks up all PENDING batch\_ids from the "initiate\_payment\_batch\_requests" table and delegates the actual book keeping task to a Celery worker.
This worker then translates all the payment\_requests for that batch\_id into book\_keeping entries in accounting\_log. Since this is only a simulator, only the Debit entries (debiting the program account) are generated.
| Attribute | Description |
|---|
| batch_id | Index. Not unique
A single batch_id will have many disbursements. Disbursements initiated from g2p-bridge in a single API invocation will be grouped together under a single batch_id. |
| payment_reference_number | Unique Index Every disbursement is treated as an individual payment request.
Every payment request is uniquely identified by a payment_reference_number.
The g2p-bridge populates "disbursement_id" as the payment_reference_number |
| remitting_account | This is the account that is debited for this payment request.
In the g2p-bridge scenario, this account will be the program's funding account. |
| remitting_account_currency | This is the currency in which the remitting_account operates. This will typically be the local currency of the nation, in which this benefit program runs. |
| payment_amount | The payment amount, the amount that will be transferred to the beneficiary's account. In the g2p-bridge scenario, this amount will be the disbursement amount. |
| payment_date | The date on which the payment needs to be effected.
g2p-bridge will send this date as the disbursement_date. |
| funds_blocked_reference_number | This is the block_reference_number, under which the funds have been earmarked for the disbursement_envelope.
For every payment actually effected, the funds blocked will be progressively released to eventually bring the blocked_amount to 0. |
| beneficiary_name | |
| beneficiary_account | |
| beneficiary_account_currency | |
| beneficiary_account_type | |
| beneficiary_bank_code | |
| beneficiary_branch_code | |
| beneficiary_mobile_wallet_provider | |
| beneficiary_phone_no | |
| beneficiary_email | |
| beneficiary_email_wallet_provider | |
| narrative_1 | There is a provision for 6 lines of narrative that can accompany a payment transaction.
It's upto the implementation to populate these 6 lines with useful information so that g2p-bridge can reconcile the payments against the individual disbursements.
The "disbursement_id" is the unique reference that is populated as "payment_reference_no".
Apart from that - the g2p-bridge can use the following attributes to populate the narrative lines.
benefit_program_mnemonic
cycle_code_mnemonic |
| narrative_2 | |
| narrative_3 | |
| narrative_4 | |
| narrative_5 | |
| narrative_6 | |
### accounting\_logs
Book keeping entries for the Debit leg. These are generated by the Celery worker - process\_payments\_worker
| Attribute | Description |
| ----------------------------------- | ----------- |
| reference\_no | |
| corresponding\_block\_reference\_no | |
| customer\_reference\_no | |
| debit\_credit | |
| account\_number | |
| transaction\_amount | |
| transaction\_date | |
| transaction\_currency | |
| transaction\_code | |
| narrative\_1 | |
| narrative\_2 | |
| narrative\_3 | |
| narrative\_4 | |
| narrative\_5 | |
| narrative\_6 | |
### account\_statements
Table used to store the requests coming in for generating account statements. There is a REST API to facilitate requests for generating account statements. The REST API persists the requests in this table with a unique ID. \ The API does not immediately generate the statement. It instead delegates this "ID" to a Celery worker - account\_statement\_generator.
The account\_statement\_generator worker generates the MT940 statement and persists the statement as TEXT in the account\_statement\_lob column.
| Attribute | Description |
| ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------- |
| id | Unique ID generated for every statement request |
| account\_number | The benefit program account number - for which the request is being placed |
| account\_statement\_lob | The actual account statement - TEXT - in MT940 format.
Asynchronously generated by Celery worker - account\_statement\_generator.
|
| account\_statement\_date | The date for which the MT940 needs to be generated. |