Data domains
Information stored in LDS has been classified into the following 6 domains:
User data
LDS stores only externalUserId- identifier of the user assigned at TPP frontend and passed to PSD2Hub. PSD2Hub does not store any additional personal details of the user.
LDS tables:
aspnetusers - user
usercompany - relation user- company
company- company
ASPSP accounts and transactions
The following information is stored in LDS in the scope of AIS flow
Accounts
LDS tables:
bankaccount - account details, name and balance
apibankaccount- additional technical bank parameters
stage_bankaccount
Transactions
LDS tables:
transaction - transaction details
stage_transaction
Payments
Initiated payments with the latest status (PIS)
LDS tables:
payment
Sessions
LDS tables:
apisession - AIS sessions
apisessionapibankaccount - a link between the session and bank account
Consents
LDS tables:
psd2context
LDS data model
he ERD (Entity Relational Diagram) has been depicted below. It contains the core main entities related to financial data aggregation stored in LDS (Local Data Storage). Technical tables and dictionary table are not presented here.
The structure is designed for both, retail and business Customers data. In case of retail Customers there is an additional direct relation between BankAccount and User.
User
Column | Format | Mandatory | Description |
Id | nvarchar(450) | NOT_NULL | Unique user ID (technical, internal) |
AccessFailedCount | int | NOT_NULL | This is used when Customer can login to banqware directly |
AuthenticationMode | int | NOT_NULL | This is used when Customer can login to banqware directly |
ConcurrencyStamp | nvarchar(max) | NULL | This is used when Customer can login to banqware directly |
DeviceToken | nvarchar(max) | NULL | This is used when Customer can login to banqware directly |
nvarchar(256) | NULL | ||
EmailConfirmed | bit | NOT_NULL | This is used when Customer can login to banqware directly |
LanguageId | int | NULL | Default language |
LockoutEnabled | bit | NOT_NULL | This is used when Customer can login to banqware directly |
LockoutEnd | datetimeoffset(7) | NULL | This is used when Customer can login to banqware directly |
LastName | nvarchar(max) | NULL | Last Name |
NormalizedEmail | nvarchar(256) | NULL | This is used when Customer can login to banqware directly |
NormalizedUserName | nvarchar(256) | NULL | This is used when Customer can login to banqware directly |
PasswordHash | nvarchar(max) | NULL | This is used when Customer can login to banqware directly |
PhoneNumber | nvarchar(max) | NULL | Mobile phone number |
PhoneNumberConfirmed | bit | NOT_NULL | This is used when Customer can login to banqware directly |
SecurityStamp | nvarchar(max) | NULL | This is used when Customer can login to banqware directly |
Status | int | NOT_NULL | Status |
TwoFactorEnabled | bit | NOT_NULL | This is used when Customer can login to banqware directly |
UserName | nvarchar(256) | NULL | User name (login) |
RegistrationDate | datetime2(7) | NOT_NULL | Registration date |
ContactId | int | NULL | N/a |
CountryId | int | NOT_NULL | Country |
SalutationId | int | NULL | Salutation/Title (1- Mr, 2- Mrs) |
FirstName | nvarchar(max) | NULL | First Name |
IsActivated | bit | NOT_NULL | Active Flag |
LastLoginDate | datetime2(7) | NULL | Last login timestamp |
Externald | nvarchar(256) | NULL | External user ID |
Transaction
Column | Format | Mandatory | Description |
Id | int | NOT_NULL | Unique transaction ID (technical, internal) |
AccountingDate | datetime2(7) | NOT_NULL | AccountingDate |
Address | nvarchar(255) | NULL | Address |
BalanceAfterTransaction | decimal(18,2) | NULL | BalanceAfterTransaction |
BalanceBeforeTransaction | decimal(18,2) | NULL | BalanceBeforeTransaction |
BankAccountId | int | NOT_NULL | Link to BankAccount |
BankBranch | nvarchar(100) | NULL |
|
BankName | nvarchar(100) | NULL |
|
CurrencyDate | datetime2(7) | NOT_NULL |
|
CurrencyId | int | NULL | Currency (internal dictionary(. (1 – PLN, 2 – EUR, 3 – USD, 4 – GBP, 5 – AUD) |
DestinationAccount | nvarchar(50) | NULL | DestinationAccount |
OperationAmount | decimal(18,2) | NOT_NULL | OperationAmount |
Owner | nvarchar(255) | NULL | Owner |
Recipient | nvarchar(255) | NULL | Recipient |
ReferenceNumber | nvarchar(100) | NULL | ReferenceNumber |
Sender | nvarchar(255) | NULL | Sender |
SenderAddress | nvarchar(255) | NULL | SenderAddress |
SourceAccount | nvarchar(50) | NULL | SourceAccount |
Title | nvarchar(255) | NULL | Title |
TransactionTypeId | int | NOT_NULL | TransactionTypeId |
ContactId | int | NULL | N/a |
ConfirmationCode | nvarchar(20) | NULL | N/a |
TransactionStatusId | int | NOT_NULL | Status (1 – Unauthorized, 2 – Confirmed, 3 – Added, 4 - Booked) |
TransactionKindId | int | NULL | N/a |
EffectiveDate | datetime2(7) | NULL |
|
BankAccount
Column | Format | Mandatory | Description |
Id | int | NOT_NULL | Unique account ID (technical, internal) |
AccountName | nvarchar(max) | NULL | Name of the account (alias) |
AccountNumber  | nvarchar(max) | NULL | DEPRECATED- see below |
AvailableFunds | decimal(18,2) | NOT_NULL |
|
Balance | decimal(18,2) | NOT_NULL | Last known balance |
BankAccountTypeId | int | NOT_NULL |
|
BlockedAmount | decimal(18,2) | NOT_NULL |
|
CompanyId | int | NOT_NULL |
|
CreateDate | datetime2(7) | NOT_NULL | If account open on banqware (non external) |
CurrencyId | int | NOT_NULL | Currency (internal dictionary(. (1 – PLN, 2 – EUR, 3 – USD, 4 – GBP, 5 – AUD) |
InterestCapitalizationDate | datetime2(7) | NULL |
|
InterestRateNegBal | decimal(18,2) | NOT_NULL |
|
InterestRatePosBal | decimal(18,2) | NOT_NULL |
|
Overdraft | decimal(18,2) | NOT_NULL |
|
IncludeInCashflowChart | bit | NOT_NULL DEFAULT((0)) | Technical |
BankId | int | NULL | Link to Bank |
LastImport | datetime2(7) | NULL | Last import timestamp |
Based on that we have extended our BankAccount model with additional tables BankAccountIdentifier and bank BankAccountIdentifierSchema
BankAccountIdentifier has 3 columns:
[BankAccountId] - id of account
[BankAccountIdentifierSchemeId]- type of identifier (IBAN, BBAN etc.) - dictionary from BankAccountIdentifierSchema
[Value]- identifier
BankAccountIdentifierSchema has 3 business columns:
Id
IsPrimary- if this is used as the main key (for deduplication)
Name- name of an identifier (e.g. IBAN=0, BBAN=1_
The column AccountNumber in BankAccount table has been deprecated and will be removed over time from LDS model.
psd2context stores concent data
[Key] | [nvarchar](450) | NOT NULL | unique technical identifier |
[AccessToken] | [nvarchar](max) | NULL | access token |
[AccountNumber] | [nvarchar](max) | NULL | Related account, empty if consent covers >1 account |
[Consent] | [nvarchar](max) | NULL | oAuth code value as received form ASPSP |
[CreateDate] | [datetime2](7) | NOT NULL | timestamp when oAuth code has been received |
[ExpirationDate] | [datetime2](7) | NULL | expiration date for access token as received form ASPSP (can be empty if ASPSP does not provide this value) |
[IsLocked] | [bit] | NOT NULL | locked Y/N (internally not on ASPSP side) |
[LastModification] | [datetime2](7) | NOT NULL | timestamp fo last refresh of access token |
[RefreshToken] | [nvarchar](max) | NULL | refresh token |
[UserId] | [nvarchar](450) | NULL | external user id |
[AccountExternalId] | [nvarchar](max) | NULL | id of the account |
[BankId] | [int] | NOT NULL | id of the bank that issued oAuth |
[AplicationScope] | [int] | NULL | scope of the consent (ais/pis) other values possible. |
[UserScope] | [int] | NULL |
|
payment stores initiated payments
[Id] | unique identifier of payment, assigned by LDS |
[Value] | amount of payment |
[BankAccountId] | Link to account id (if aggregated with AIS) |
[CurrencyId] | Currency code (based in LDS dictionary) |
[Date] | Date (timestamp) when payment has been initiated in PSD2Hub |
[ExternalUserId] | External user ID |
[PurposeCode] | optional parameter (if required by AFI PIS API) |
[RecipientName] | Name of the payment recipient |
[RecipientAddress] | Address of the payment recipient |
[RecipientIBAN] | Destination account IBAN (creditor IBAN) |
[SenderIBAN] | Source account IBAN (debtor IBAN) |
[SenderName] | Nem of debtor |
[PaymentStatusId] | Status of payment (refer to PISP payment status mapping) |
[Title] | Title of payment (as provided in the frontend) |
[TransactionId] | Identifier of transaction assigned by AFI |
[TransactionSubmissionId] | Identifier of transaction submission (after SCA) assigned by AFI |
[ExternalId] | External identifier of the transaction (assigned by AFI) |
[RecipientIdentifier] | Alternative recipient identifier (e.g. DIP) |
[RecipientType] | Alternative recipient identifier type - can be an email address, phone number, tax id etc. |
[BankId] | The id of the bank (debtor account) |
