example-bank-models

openg2p-g2p-bridge-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.

AttributeDescription

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.

AttributeDescription

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.

AttributeDescription

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.

AttributeDescription

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

AttributeDescription

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.

AttributeDescription

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.

Last updated

Logo

Copyright © OpenG2P. This work is licensed under Creative Common Attribution (CC-BY-4.0) International license unless otherwise noted.