[central-ledger] Enforce 1 to 1 relation between participantCurrency and participantPosition database schema

[kleyow]

Summary: A partcipant account(i.e participantCurrency) should only have one partcipantPosition for that account. This is not enforced in the database schema, so duplicate participantPostion's may be associated with an account wether through error or intentional entry.

This needs to be updated in our relationship diagrams https://docs.mojaloop.io/assets/img/central-ledger-schema.58c20f57.png

Severity: (Low, Medium, High)

Priority: (Critical, Medium, Low)

Expected Behavior Inserting a participantPosition for a participantCurrency with a participantPosition already made should throw a error.

Steps to Reproduce

1. Deploy mojaloop and run GP collection
2. Attach a mysql viewer to central ledger database and insert a participantPosition entry on any account.

Specifications

• Component (if known): Central Ledger
• Version: v14.1.0 Mojaloop
• Platform: Test enviorment
• Subsystem: Database
• Type of testing:
• Bug found/raised by:

Notes: Example of accounts with multiple positions.

GET /participants/payerfsp/accounts
[
{
id: 13,
ledgerAccountType: "POSITION",
currency: "INR",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:25.000Z"
},
{
id: 13,
ledgerAccountType: "POSITION",
currency: "INR",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:25.000Z"
},
{
id: 14,
ledgerAccountType: "SETTLEMENT",
currency: "INR",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:25.000Z"
},
{
id: 14,
ledgerAccountType: "SETTLEMENT",
currency: "INR",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:25.000Z"
},
{
id: 15,
ledgerAccountType: "INTERCHANGE_FEE",
currency: "INR",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:25.000Z"
},
{
id: 16,
ledgerAccountType: "INTERCHANGE_FEE_SETTLEMENT",
currency: "INR",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:25.000Z"
},
{
id: 11,
ledgerAccountType: "POSITION",
currency: "TZS",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:24.000Z"
},
{
id: 12,
ledgerAccountType: "SETTLEMENT",
currency: "TZS",
isActive: 1,
value: -10000,
reservedValue: 0,
changedDate: "2023-02-07T13:40:51.000Z"
},
{
id: 7,
ledgerAccountType: "POSITION",
currency: "USD",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:22.000Z"
},
{
id: 7,
ledgerAccountType: "POSITION",
currency: "USD",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:22.000Z"
},
{
id: 8,
ledgerAccountType: "SETTLEMENT",
currency: "USD",
isActive: 1,
value: -10000,
reservedValue: 0,
changedDate: "2023-02-07T13:40:49.000Z"
},
{
id: 8,
ledgerAccountType: "SETTLEMENT",
currency: "USD",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:22.000Z"
},
{
id: 9,
ledgerAccountType: "POSITION_REMITTANCE",
currency: "USD",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:22.000Z"
},
{
id: 10,
ledgerAccountType: "SETTLEMENT_REMITTANCE",
currency: "USD",
isActive: 1,
value: 0,
reservedValue: 0,
changedDate: "2023-02-07T13:35:22.000Z"
}
]

• Severity when opened:
• Priority when opened:

[shashi165]

I think this scenario will occur only when someone inserts data into participantPosition table manually.

We already have an index on participantPosition.participantCurrencyId but it is non-unique. we need to convert it to an unique index. https://github.com/mojaloop/central-ledger/blob/main/migrations/310301_participantPosition-indexes.js