Notes

First American Trust
Wire Advice Ingestion – Development Assistance Document
Version: v1.1
 
Contents

Introduction 3
Referenced Documents 3
Scope & Assumptions 4
Guidelines 4
Shadow Core: WireAdviceHandler Azure Function 6
Common Utilities to be developed 8
Appendix 9



 
Introduction

This document covers the development approach for Wire Advice Ingestion into Shadow Core database. Shadow Core is like transaction data store and PostgreSQL database is used. Shadow Core will follow serverless architecture.
Azure functions will be used for business logic and Azure managed PostgreSQL instance will be used.
Note: This document is only for reference and inputs for development of wire advice ingestion into shadow core database and not the business requirement document. Acceptance Criteria defined in user stories should be followed for outcome.
Referenced Documents

# Document Name Description Reference folder
1 Wire Advice Ingestion - Reviewed - Approved Design.pdf The detailed design for Wire Advice ingestion from MIADS to Shadow Core.

2 Sample BAI Wire Advice.zip Sample Wire Advice messages in BAI2 format.

3 FA Trust - Digital Banking Shadow Core Database Design -V0.4.xlsx* Digital Banking Shadow Core Database Design

4 BAI2 Mapping from FA Source systems.xlsx DBIQ Current Day BAI2 Mapping from FA source systems (sheet: CD_wires)

5 <TBD> BAI Layout Specification from GFX <TBD>
*Always refer to the latest version of the document from folder <TBD>. 
Scope & Assumptions
• Time triggered Azure Function GetWireAdvicesFromGFX in MIDAS reads the wire advice in BAI2 format from GFX queue, encrypts it and puts it into “incoming_wc_advices” Service Bus Queue.
• Service Bus trigger function GetIncomingWireAdvices in MIDAS reads the message from service bus queue and send encrypted message to service bus Topic: “Pending_Wire_Advices_Topic”. GetIncomingWireAdvices function in MIDAS should always send the message header TO property as ALL for all messages to be delivered to both the subscriptions.
• Pending_Wire_Advices_Topic will have two subscription one each for WireCore and WireAdviceHandler.The subscription level SQL topic filters should look for message header level TO property to accept messages with (ALL or WIRECORE) or (ALL or SHADOWCORE) for WireCore and WireAdviceHandler respectively.
• Function ProcessPendingWireCoreAdvices in MIDAS and WireAdviceHandler in Shadow Core will subscribe to the same topic.
• Function ProcessPendingWireCoreAdvices in MIDAS will read wire advice from service bus topic and send to WireCore. This will be considered as separate story since changes required to enhance this function as per the agreed to-be state is not in scope of this document.
• Messages published/consumed from Topic are encrypted/decrypted using Python’s cryptography (Fernet) library. Encryption key will be available in Azure Key Vault.
• Retry logic – TBD -
• Data fields containing PII (Personally Identifiable Information) information should be encrypted before inserting into database. Data should be encrypted using AES-128 bit CBC algorithm. Column-L of each sheet in FA Trust - Digital Banking Shadow Core Database Design -V0.4.xlsx defines whether encryption is needed for each column or not. <Pending Confirmation from Andrea>
• WireAdviceHandler in Shadow Core should ensure that persisting the current Wire Advice as transaction should not result in duplicate entries.
• Creation of all Azure Resources will be through Terraform, follow the guidelines to implement the set up in Terraform.
Guidelines
• Naming Conventions:
 Name of the function/class should be meaningful and self-explanatory.
 Do not use underscores, space to differentiate words
 Capitalize the first letter of each word (Camel Case)
 Follow consistent naming conventions for class, method, folder, variables etc.
• Indentation:
 There should be no space between function name and parenthesis.
 Indentation should be consistent at opening and closing conditional statements.
• Terraform:
 Configured all API management endpoints set up in the api_management.tf
 Configured Service bus topic, subscription settings such as Message Lock Duration, Max Delivery Count, etc. in the service_bus.tf.
• All secrets, URL, connection string for database, EventHub, etc. or any sensitive data should be added in Azure key vault.
• Create a constants class file to define variable which holds all the constants values. Instead of hardcoding the values use variables defined in constants class file.
• Constants defined specifically for parameters to be fetched from data stores such as Key Vault, json files, etc. must be defined in a separate constant class. Example “Configurations.”
• Add local.settings.json file in Main project folder to test code locally.
• All Date fields in database should be in “YYYY-MM-DD” format.
• Amount fields should be signed, with 2 places of decimal or as-is.
• Any logs generated from application should be routed to Splunk using Azure Event Hub. Leverage existing “EventHubLogger” class for logging.
 
Shadow Core: WireAdviceHandler Azure Function

Azure Function Type: Service Bus Topic Trigger Function
Name: WireAdviceHandler
Message: Encrypted message
Message Receive Mode: Peek Lock
Message Format (after decryption): Wire Advice in BAI2 format. Multiline string for BAI2 format will be received from MIDAS.

A. Parse BAI Message
1. Create a subscription (through Terraform) for WireCoreHandler with SQL filter on message header’s “TO” property to accept messages with (ALL or SHADOWCORE) only.
2. The service bus topic trigger function WireAdviceHandler is triggered on arrival of each message in Service Bus Topic. Encrypted message will be delivered to function as input argument.
3. Received messages should be decrypted using the algorithm specified in “Scope and Assumption” section of this document.
4. Decrypted message will be the Wire Advice in multiline string BAI2 format.
5. Wire Advice should be parsed to get the required data fields to be inserted into database columns. BAI2 Wire Advice sample is provided in Referenced Documents section of this document. Interim BAI2 Wire Advice layout is provided in Appendix.
6. 88 type record follows standard key value pair delimited by “:”. Certain data element may continue in multiple rows. They should be concatenated and stored in the single column.
7. Wire Advice must contain below fields mandatorily, if any of these are missing then message should be rejected:
 Customer Account Number
 AMOUNT
 REFNUM
 WIRE_TYPE
8. Wire Advice for certain types of wires such as non-monetary, etc. are not required to be ingested into Shadow Core, those should be skipped. Filtration logic should be built on TYPE field as a part of 88 record of BAI2 format as per below logic:
i. If TYPE is having odd value or (1090, 1060) then Wire Advice should be skipped.
ii. For skipped messages, appropriate logging should be included, mark the message as complete and return success response.
9. Appropriate logging for traceability and support should be included. Business specific information such as REFNUM, AMOUNT, ACCOUNT etc. should be logged in Splunk.
10. Any run time error, exception, failures, etc. should be logged in Splunk with proper logging level like Warning, Critical, Error. It helps to identify which log would trigger what alert.
B. Data Ingestion into Database
1. Credential to connect to database such as host, user, password, etc. must be retrieved from Key Vault. Key literal for each parameter (or secrets) stored in key vault must be defined as a constant as per standards.
2. Before adding new records into table below record level validations should be followed as specified in subsequent sections.
3. Wire Advice (wire_advice_details) Record Creation
iii. Refer Appendix section of this document– BAI2 message mapping with WireAdviceDetails table for mapping and transformation logic to insert wire advice record in table
iv. Generated unique advice reference number must be logged for audit and tracing purpose.
v. Insert record into wire_advice_details table
vi. If duplicate error is reported due to unique key (bank_reference, tran_date) violation, then log warning indicating wire advice already exists in wire_advice_details table and proceed with creation/validation of transaction information.
vii. In case of any other failure while adding data into table then rollback the changes and return bad request with appropriate error logging. Do not mark message as complete.
viii. If no errors are encountered, proceed with creation of Transaction Information record as specified below.
4. Transaction Information (cd_transaction_info) Record Creation
i. Refer appendix section of this document - BAI2 message mapping with CD_TransactionInfo table for mapping and transformation rules for each column with BAI2 message to form the transaction record.
ii. Generated unique transaction identifier (tran_id) must be logged for audit and tracing purpose.
iii. Insert the record into cd_transaction_info table.
iv. If duplicate error is reported due to unique key (bank_id, account_no, post_date, amount, bank_reference) violation, it indicates transaction record for the wire advise already exists. Log warning message indicating entry already exists. Commit database, return success response, and mark the message as completed.
5. If no error is encountered, then commit all the changes in database, mark the message as complete and return response as Success.

 
Common Utilities to be developed

• BaseEntity Model Class

To define common audit columns of a table, create BaseEntity as model class for below fields.
- CreatedDate
- UpdateDate
- CreatedBy
- UpdateBy

• Generate Unique Key Reference Number

 New utility generates 16 digit long unique key sequence number as per the format specified
 The format of sequence number will be <YYYYDDDnnnnnnnnn> where:

Format Description Sample
YYYY Current Year in YYYY format. 2022
DDD Julian date for current date 352
nnnnnnnnn Running database sequence number 000001234

Sample of unique reference number:
2022352000001234
 Create class GenerateUniqueReferenceNumber and define a method which accepts an argument as type of sequence to be generate.
 All possible input parameters must define as constant as per below

Type of Sequence Database Sequence Name
CurrentDayTransactionID cd_txn_sequence
PriorDayTransactionID pd_txn_sequence
WireAdviceReferenceId wire_adv_ref_sequence
ACHAdviceReferenceId ach_adv_ref_sequence
BAIFileId bai_file_id_sequence
TransactionExceptionID txn_expn_id_sequence

 This utility should fetch the next sequence number from database sequence and suffix it to Current Year and Julian date to form the 16-digit unique reference number and return it to calling function.
 Appropriate error and exception handling should be in place within this utility.

Appendix

• GFX Wire Advice BAI2 layout for needed fields
Note: Below definition of wire advice BAI2 covers only the fields needed for Wire Ingestion to ShadowCore database. Complete wire advice BAI2 specification will be updated once we receive it from GFX.

BAI2 is a “,” delimited file. First column of each record is the “Record Code” which is used to identify the type of record as explained below.
Record Code Record Name Description
01 File Header Begins File
02 Group Header Begins Group
03 Account Identifier Begins Account
16 Wire Transaction Detail Within Account
88 Continuation Record Applicable for 03 and 16 type
49 Account Trailer Ends Account
98 Group Trailer Ends Group
99 File Header Ends File

Record Type – 03 Account Identifier
Column Index Column Name of Table Remarks
1 Record Code 03
2 Account Number

Record Type – 16 Wire Transaction Detail
Column Index Column Name of Table Remarks
1 Record Code 16
2 BAI Type Code
3 Transaction Amount With 2 decimal values without the decimal point
Ex: 10023  refers to $ 100.23
5 Wire Reference Trace Number if concatenated with account number with a “.” Separator.
Ex: 123456789.7777888
Trace Number = 123456789
7 Description

Record Type – 88 Continuation Record Detail
Column Index Column Name of Table Remarks
1 Record Code 88
2 Additional Record Key: Value
Example:
TYPE:1000





• BAI2 message mapping with WireAdviceDetails table
Column Name of Table BAI2 Field
Name BAI2 Record Code Transformation Logic
wire_advice_reference_id Invoke method defined in section of this document: Common Utilities to be developed - Generate Unique Key Reference Number with parameter as WireAdviceReferenceId to generate unique reference number

bank_reference Wire Reference 16 Wire Reference in 16 type contains both Trace Number and Account number. Both are concatenated with “.” Delimiter.
Only the Trace Number part should be mapped.
tran_date Current date in format YYYY-MM-DD
description Description 16
wire_type BAI Type Code 16
pay_meth PayMeth 88 BAI2 Field name for 88 type actually refers to the KEYs of continuation record.






















in_out IN/OUT 88
msg_id MSGID 88
Type TYPE 88
template_id TemplateID 88
Imad IMAD 88
Omad OMAD 88
sender_bank_aba SNDBKABA 88
sender_bank_name SNDBKNAME 88
receiver_bank_aba RCVBKABA 88
receiver_bank_name RCVBKNAME 88
org_id ORGID 88
Org ORG 88
Ogb OGB 88
bbk_id BBKID 88
bbk_nm BBKNM 88
Bnf BNF 88
Obi OBI 88
receiver_bank_info RCVBKINFO 88
ibk_info IBKINFO 88
bbk_info BBKINFO 88
bnf_info BNFINFO 88
Bbi BBI 88
Rfb RFB 88
create_operator CREATE OPERATOR 88
verify_operator VERIFY OPERATOR 88
pnrm_time PNRM TIME 88
created_date Current date and time – When record inserted into table
Format – Default Postgres timestamp
Date and time until microsecond (no time zone)
Ex: YYYY-MM-DDTHH:MM:SS.ssssss
update_date Current date and time – When record inserted into table
Format – Default Postgres timestamp
Date and time until microsecond (no time zone)
Ex: YYYY-MM-DDTHH:MM:SS.ssssss
created_by Default to ‘ShadowCore’
update_by Default to ‘ShadowCore’

• BAI2 message mapping with CD_TransactionInfo table
Column Name of Table BAI2 Field
Name BAI2 Record Code Transformation Logic
tran_id Invoke method defined in section of this document: Common Utilities to be developed - Generate Unique Key Reference Number with parameter as CurrentDayTransactionID to generate unique reference number
bank_code Default to '4125'
account_no Account Number 03
tran_date Current date and time – When record inserted into table
Format – YYYY-MM-DD
post_date Default to NULL
value_date Default to NULL
Amount Transaction Amount 16 If transaction type is credit, then amount should be positive value else negative value with two decimal places.
Sample values: 1234.44 or -708.9
debit_credit_indicator If transaction amount is zero or +ve, then value should be “CR” else “DR”.
currency_code Default to ‘USD’
bank_reference Wire Reference 16 Wire Reference in 16 type contains both Trace Number and Account number. Both are concatenated with “.” Delimiter.
Only the Trace Number part should be mapped.
transaction_detail Description 16
bai_type_code BAI Type Code 16
Timestamp Default to NULL
transaction_code Default to NULL
check_number Default to NULL
Source Default to ‘GFX’
dna_ref_number Default to NULL
wire_advice_reference_id Unique key reference number generated for table wire_advice_details
ach_advice_reference_id Default to NULL
record_status Default to “READY”
bai_file_id Default to NULL
created_date Current date and time – When record inserted into table
Format – Default Postgres timestamp
Date and time until microsecond (no time zone)
Ex: YYYY-MM-DDTHH:MM:SS.ssssss
update_date Current date and time – When record inserted into table
Format – Default Postgres timestamp
Date and time until microsecond (no time zone)
Ex: YYYY-MM-DDTHH:MM:SS.ssssss
created_by Default to ‘ShadowCore’
update_by Default to ‘ShadowCore’