Data Dictionary
  • 12 Oct 2023
  • 195 Minutes To Read
  • Dark
    Light
  • PDF

Data Dictionary

  • Dark
    Light
  • PDF

Article summary

Description

This document describes the database structure and fields used in Mambu for the purposes of supporting the Mambu APIs, Business Intelligence Reporting, and enabling data migration procedures. You may also perform a database backup which will give a backup of all your tables. For more information, see Database Backups in our User Guide or Database Backup in our API v2 Reference

Overview

Mambu is built on a relational database system, but is often de-normalized and puts the dependency on the application to maintain certain level of integrity. For instance, a loan account has a foreign key to its holder which may be a client or a group.

Here we indicate which fields are also logically required or currently unused (or reserved for future use). This document focuses on tables which are importable or accessible via APIs.

Common Fields

All tables have a primary key called encodedKey. This key is a Universally unique identifier (UUID) generated by the application at the time of creating or storing the object.

Many tables will also have fields called creationDate and lastModifiedDate indicating when the object was created and last modified.

Time Stamps

Most time stamps in Mambu are stored in UTC.

Some variation to this are pure dates which have meaning for the organization itself. For instance, if a repayment is due on June 16th, 2010 it will be stored as “June 16, 2010 00:00:00” in the database. The application logic then takes care of ensuring that this repayment is set to in arrears as appropriate for the organizations’ time zone.

In each entity’s table presented in this document, for its date fields extra info will be found about what value is stored in the database (UTC or Organization Time).

Undocumented Tables

Some tables in this data dictionary may be undocumented, this can be for a number of reasons. In some cases tables will be created to support upcoming features and documentation added once the feature has been released, in other cases tables are not intended to be modified/used by customers as they contain only Mambu system or UI data such as dashboard view preferences or custom views and menus.


accountarrearssettings

entity holding the required information for the arrears settings of an account.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
monthlytolerancedayint(11)Represents the monthly arrears tolerance day value.
tolerancepercentageofoutstandingprincipaldecimal(50,20)Indicates the amount by which an account will be allowed to go into arrears as a percentage of the outstanding principal.
toleranceperiodint(11)The allowed period for loan to be in arrears.



accountarrearssettingsmapping

Describes a link between a loan account and arrears settings when the terms change with a transaction of type TERMS_CHANGED
Column NameData TypeDescription
arrearssettingskeyvarchar(35)The encoded key of the entry in teh arrearssettings table holding the arrears settings.
loanaccountkeyvarchar(32)The encoded key of the loan account.
loantransactionkeyvarchar(32)The encoded key of the tranasction which records the change of terms.



accountinginterestaccrualbreakdown

Stores information regarding interest accrual
Column NameData TypeDescription
accountencodedkeyvarchar(32)Reference to the account (loan/savings) encodedkey for which this entry is logged.
accountidvarchar(32)Reference to the account (loan/savings) ID for which this entry is logged.
accrualtypevarchar(32)The type of interest being accrued. For regular interest accrued, that isn’t paid already INTEREST_ACCRUED. For interest accrued that is already paid after a prepayment PREPAID_INTEREST_ACCRUED.
amountdecimal(50,10)The amount accrued.
bookingdatedatetimeBooking Date of the referenced GL Journal Entry
branchencodedkeyvarchar(32)Reference to the branch encoded key for which this entry is logged.
creationdatedatetimeDate and time, in UTC, at which this entry was created.
entryidbigint(20)Reference to GL Journal Entry entry id for which this entry is logged.
entrytypevarchar(32)Accounting entry type, for example, DEBIT or CREDIT.
glaccountencodedkeyvarchar(32)Reference to the GL Account for which this entry is logged
glaccounttypevarchar(32)Type of GL Account for which this entry is logged, for example ASSETor INCOME.
idbigint(64)Accounting interest accrual breakdown id
processedtinyint(1)Flag for marking if this entry has been successfully processed and can be removed from this table.
productencodedkeyvarchar(32)Reference to the product encoded key for which this entry is logged
producttypevarchar(32)Reference to the product type for which this entry is logged
senttinyint(1)Flag for marking is this entry was sent to the internal message broker
transactionidvarchar(32)Reference to GL Journal Entry transaction id for which this entry is logged.



accountingloaninterestaccrualsnapshot

stores interest accrual data to generate the aggregated and breakdown amounts on the same data, saved in a snapshot at a specific point in time.
Column NameData TypeDescription
accountencodedkeyvarchar(32)The unique identifier of the account.
accountidvarchar(32)The ID of the account.
assignedbranchkeyvarchar(32)The unique key of the branch to which this account is assinged.
interestamountdecimal(50,10)The amount of interest.
productinterestaccrualcalculationvarchar(256)The method used to calculate interest accrual. Can be one of NONE, BREAKDOWN_PER_ACCOUNT, or AGGREGATED_AMOUNT.
producttypekeyvarchar(32)The unique key of the product used to create this account.



accountingnotificationmessage

Column NameData TypeDescription
bodymediumtext
creationdatedatetime
destinationvarchar(1024)
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
eventvarchar(256)
failurecausevarchar(256)
failurereasonvarchar(255)
gljournalentrykeyvarchar(32)
idvarchar(256)
numretriesint(11)
senddatedatetime
statevarchar(256)
templatekeyvarchar(32)
typevarchar(256)



accountingnotificationmessagequeue

Column NameData TypeDescription
accountingnotificationmessage_encodedkey_oidvarchar(32)
creationdatedatetime
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
statevarchar(256)



accountingrate

table used to store internal currency conversion rates for accounts in different currencies within the same bank. this table is for an upcoming feature.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique keyu for this row.
enddatedatetimeThe last date on which this rate will be valid.
fromcurrencycodevarchar(3)Foreign key pointing to a currency defined in the currency table which is to be converted from.
ratedecimal(50,20)The currency conversion rate.
startdatedatetimeThe first date on which this rate is valid.
tocurrencycodevarchar(3)Foreign key pointing to a currency defined in the currency table that is the output currency of the conversion rate.
userkeyvarchar(32)Unique key of a user who entered this exchange rate. Foreign key to the user table.



represents a link created between a loan account and a deposit account. these links are used to be able to pay the loan account due amounts in the due date of a repayment, automatically, via transfers from the linked deposit account.
Column NameData TypeDescription
creationdatedatetimeThe date when the link was created - Stored as UTC
encodedkeyvarchar(32)A unique ID used as a key for this table.
lastmodifieddatedatetimeThe date when the link was changed - Stored as UTC
loanaccountkeyvarchar(32)The key of loan account with which the deposit account is linked. Required
savingsaccountkeyvarchar(32)The key of deposit account linked to the loan account.Required



accountpaymentholidaysdetails

this table contains details about payment holidays which have been granted for a given loan account and includes the interest amount accrued during the payment holiday period. read our documentation on payment holidays for more information on this feature.
Column NameData TypeDescription
accountkeyvarchar(32)The encoded key for the loan account which has been granted a payment holiday.
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
interestaccrueddecimal(50,10)The interest accrued during the payment holiday.



activity

each action that takes place in the application is followed by an activity that is logged and posted on the dashboard and on the activity feed.
Column NameData TypeDescription
activitychanges_integer_idxint(11)For single changes this will always be -1. When a single activity contains multiple changes, this field will indicate the position in the list of changes, starting from 0. For example if multiple holidays can be added for an organization and will be applied when the save button is clicked, there will be one activity of the type HOLIDAY_SETTINGS_CHANGED and multiple activities of the type ENTITY_ADDED. The parent_key will point to the main activity in the set.
assignedcentrekeyvarchar(32)The key of the centre involved in this activity
assigneduserkeyvarchar(32)Set to a user who is assigned to be notified about this activity (such being the owner of the clients) also, is used as the assigned user key of the entity for whom the activity is placed (the credit officer of the client, for example)
branchkeyvarchar(32)Set to branch encoded key if the activity is associated with a particular branch
centrekeyvarchar(32)Set to centre encoded key if the activity is associated with a particular centre
clientkeyvarchar(32)Set to client encoded key if the activity is associated with a client
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables, in this case, the fieldchangeitem table.
entitykeyvarchar(32)Used for generic activities and specifies the key of the linked entity (CLIENT, GROUP, etc)
entitytypevarchar(30)Field used for generic activities and specifies entity type (Eg. CLIENT, GROUP, BRANCH)
fieldchangenamevarchar(256)The field which corresponds to this activity in the case when it is a sub-activity
glaccountkeyvarchar(32)Set to gl account encoded key if the activity is associated with a particular gl account
glaccountsclosurekeyvarchar(32)Set to GlAccountClosure encoded key if the activity is associated with a particular accounting closure
groupkeyvarchar(32)Set to group encoded key if the activity is associated with a group
lineofcreditkeyvarchar(32)The key of the line of credit involved in this activity
loanaccountkeyvarchar(32)Set to loan account encoded key if the activity is associated with a particular loan account
loanproductkeyvarchar(32)Set to loan product encoded key if the activity is associated with a particular product (eg. account activity)
notesvarchar(256)The notes logged within the activity.
parent_keyvarchar(32)Specifies the parent activity if any. The parent is another entry in this table.
savingsaccountkeyvarchar(32)Set to loan account encoded key if the activity is associated with a particular loan account
savingsproductkeyvarchar(32)Set to loan product encoded key if the activity is associated with a particular product (eg. account activity)
taskkeyvarchar(32)The key of the task involved in this activity
timestampdatetimeThe time when the activity was logged.
transactionidbigint(20)The id of the transaction contained in the activity.
typevarchar(256)The type of the activity. See our API v1 reference listing available Activity types.
userkeyvarchar(32)The user key of the user (activity actor) who was logged in and performed the activity



address

captures address information about clients, groups, etc. foreign key (parentkey) relies on primary keys being uuids. parents may have any number of addresses. for instance, the address field may have a parentkey = “abc” which refer to the client.encodedkey = “abc”. as such, addresses are to be retrieved via their parents.
Column NameData TypeDescription
addresstypevarchar(256)Type of address. Unused
cityvarchar(256)City of the address
countryvarchar(256)Country of the address
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
indexinlistint(11)Order of the address if the parent holder has multiple addresses for display/formatting purposes. That is, 0 is displayed before 1, etc.
latitudedecimal(9,6)The latitude of the address point.
line1varchar(256)First line of the address
line2varchar(256)Second line of the address
longitudedecimal(9,6)The longitude of the address point.
parentkeyvarchar(32)Foreign key as to who this address belongs to. For instance may refer to a client or a group, etc. Required
postcodevarchar(256)Postal code of the address
regionvarchar(256)Sub-region of the address. Unused.



amortizationamount

an amount that was amortized from a bigger one. when organizations hold an asset, an income or an expense, they want to amortize it over the time. this class is used to amortize a part of the initial amount.
Column NameData TypeDescription
amortizedamounts_encodedkey_ownvarchar(32)Encoded key of an entry in the predefinedfeeamount table.
amortizedamounts_integer_idxint(11)In the case this there are more than one amortized amount relating to the same entry in the predefinedfeeamount table, this field shows the index in that list for the current amount.
amountdecimal(50,10)The amount amortized by this instance. Required
branchkeyvarchar(32)Encoded key of the branch.
centrekeyvarchar(32)Encoded key of the centre.
creationdatedatetimeThe system date when this entry was logged (as UTC). Required
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
entrydatedatetimeThe date when this amount was recognized as amortized (as Organization Time). Required
reversalamountkeyvarchar(32)In the case when this amount is reversed the reversalAmountKey represents the key of an entry in this table containing the amount which reversed this current one
taxamountdecimal(50,10)The amount of taxes amortized by this instance.
typevarchar(32)The type of the amortization(regular amortization, reversal, etc.)
- AMORTIZATION,
- AMORTIZATION_ADJUSTMENT



applicationrootencryptionkey

A table holding keys used to encrypt files and data being stored in your Mmabu system.
Column NameData TypeDescription
contentvarchar(256)A key used in encrypting data.
creationdatedatetime(6)The date and time at which this key was created, in UTC.
encodedkeyvarchar(32)A unique key for this row.
ivvarchar(256)An initialisation vector used in encrypting data.
lastmodifieddatedatetime(6)The date and time, in UTC, on which the key was last modified or replaced.



authorizationhold

The model used for keeping authorization requests.
Column NameData TypeDescription
accountkeyvarchar(32)The key of the account linked with the authorization hold.
amountdecimal(50,10)The amount to hold. Required
cardacceptorkeyvarchar(32)Link to the entity used for keeping card acceptor provided details like, the state, country, etc from which the request was made
cardreferencetokenvarchar(72)The card reference token used to reference the user card.
creationdatedatetime(6)As UTC. Required
creditdebitindicatorvarchar(256)Indicates whether the hold is positive or negative. DBIT is a normal positive hold for card payments, CRDT indicates this hold will add money to the card-holder’s account, for example, for refunds &c..
currencycodevarchar(3)The ISO currency code, in which the request was made
encodedkeyvarchar(32)The encoded key for this database entry. This value is auto-generated and should not be changed.
exchangeratedecimal(50,10)The exchange rate used at the time of the transaction to convert between the original and account currency.
externalreferenceidvarchar(256)The external reference Id to be used to reference this request in the subsequent requests
isadvicebit(1)Whenever the given request should be accepted without any validations. Required
lastmodifieddatedatetime(6)The date on which this row was last modified. As UTC.
originalamountdecimal(50,10)The amount of the transaction in the original currency in the case that this transaction was made in a currency different to that of the account.
originalcurrencyvarchar(32)The currency of the transaction in the case that it was anything other than the currency of the account.
referencedateforexpirationdatetime(6)The date to consider as start date when calculating the number of days passed until expiration (stored as UTC).
sourcevarchar(32)Indicates the source of the authorization hold. Can be:
- CARD: The authorization hold has been created using the Cards functionality.
- ACCOUNT: The authorization hold has been created directly in the account.
statevarchar(256)The current Authorization Hold state. Can be:
- CANCELED: The previously registered was canceled and the balances updated,
- PENDING : The request was registered, the available amount was updated, but the transaction was not applied yet,
- SETTLED: The request was registered and the specific transaction was applied in Mambu.
Required
usertransactiontimevarchar(256)The moment of time at which the transaction occurred. The format is caller dependant and it not restricted in anyway. Could be dates, timestamps, epoch millis etc.



backgroundprocess

the execution of a process started by a user, that is done in the background.
Column NameData TypeDescription
creationdatedatetime(3)When this process was created. Stored as Organization Time
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
enddatedatetimeWhen this process was ended. Stored as Organization Time.
lastheartbeatdatedatetime(3)The date and time at which it was last checked that this process is still running.
retryattemptsint(11)The number of times the process was retried in the case of failure.
simpleexceptionvarchar(3000)A simple exception information summary for failed processes
startdatedatetimeWhen this process was started. Stored as Organization Time.
statevarchar(256)The current status of this process:
- IN_PROGRESS
- COMPLETE
- NOT_FOUND
- CANCEL
- ERROR
- OVERRIDDEN
Required
typevarchar(256)The type of the action:
- ACCOUNTING_BALANCE_SHEET_REPORT
- ACCOUNTING_PRODUCT_LEDGER
- ACCOUNTING_TRIAL_BALANCE_REPORT
- ACCOUNTING_PROFIT_AND_LOSS_REPORT
- STORE_HOLIDAYS
- STORE_HOLIDAYS_FOR_BRANCH
- CRON_JOBS
- GENERATE_GL_ACCOUNTS_CLOSURE
Required
userkeyvarchar(32)The key of the user that started this process.



backgroundprocessprogress

entity which correspond to the progress information of a background process.
Column NameData TypeDescription
currentprogressdecimal(50,20)The process current progress
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables, in this case, the backgroundtask table.
progresstypevarchar(256)How this progress is measured
totalprogressdecimal(50,20)The process total progress that need to be executed (includes the progress that was executed and the one that needs to be executed)



backgroundtask

represents a task which is submitted for background processing
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry. This field is autogenerated and should not be changed.
entitykeyvarchar(32)The entity key which for which this task was created.
entitytypevarchar(256)The type of the entity for which this task was created
inputmediumtextInput of the task, JSON serialized version
processkeyvarchar(32)Foreign key to the backgroundProcess table. The associated background process to this task, it contains information about process state and progress.
progresskeyvarchar(32)Foreign key to the backgroundProcessProgress table. The associated background process progress to this task.
resultmediumtextResult of the task, JSON serialized version
taskidbigint(20)Incremented id used for ordering. (UNIQUE INDEX ‘TASKID_UNIQUE’)



basearrearssettings

base class for all the entities grouping settings related to arrears settings.
Column NameData TypeDescription
datecalculationmethodvarchar(256)How arrears dates are calculated. Can be one of
- ACCOUNT_FIRST_WENT_TO_ARREARS
- LAST_LATE_REPAYMENT
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables, in this case, the accountarrearssettings and productarrearssettings tables.
nonworkingdaysmethodvarchar(256)Whether the non working days are taken in consideration or not when applying penalties/late fees or when setting an account into arrears. Either INCLUDED or EXCLUDED.
tolerancecalculationmethodvarchar(256)The method used to compute arrears day. Must be one of:
- ARREARS_TOLERANCE_PERIOD
- MONTHLY_ARREARS_TOLERANCE_DAY
Required"
toleranceflooramountdecimal(50,20)The minimum threshold for marking an account as being in arrears. For example, if the floor is set as 15 euros and a loan account has a tolerance of 10% with 100 euros outstanding principal, it will not be marked as being in arrears as the amount is only 10 euros and so less than the 15 euro floor.



batchmigration

holds information about batch migration scripts.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
entitykeyvarchar(32)Maintains the key of the migrated entity. Required
exceptionmediumtextException of the failed update process.
successfulbit(1)Maintains the state of the migrated entity.
typevarchar(32)Holds the type of migration that contains the row information.



batchmigrationarchived

this table is an archive of the batch migration table and was created as part of the v9.47 release to avoid losing existing data.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
entitykeyvarchar(32)Maintains the key of the migrated entity. Required
exceptionmediumtextException of the failed update process
successfulbit(1)Maintains the state of the migrated entity.
typevarchar(32)Holds the type of migration that contains the row information.



blockfund

a table describing blocks or seizures of funds in client accounts which are generally made due to a governmental or judicial request, for example garnishment of wages, debt collection or seizure of funds which were generated through the proceeds of criminal activity. blocked funds will not be available to clients.
Column NameData TypeDescription
accountkeyvarchar(32)The encoded of the account for which a certain amount will be blocked.
amountdecimal(50,10)The amount of the account owner’s funds which are blocked.
creationdatedatetimeThe date on which this seizure was created.
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
externalreferenceidvarchar(512)A reference ID to refer to the blocked funds which is used when unblocking or withdrawing all or part of these funds.
lastmodifieddatedatetimeThe date on which this seizure was last modified.
notesvarchar(256)Notes recorded by the user when initiating the seizure.
seizedamountdecimal(50,10)The amount which has been blocked for this seizure.
statevarchar(256)The state of this block; PENDING, REMOVED or SEIZED.



blockfundtransactionsource

represents a link between a transaction and a portion of an account holder’s balance which has been blocked.
Column NameData TypeDescription
blockfundkeyvarchar(32)The encodedkey of the blocked funds. This links to the blockfund table
blockfundreferenceidvarchar(512)The reference ID for the blocked funds. This referes to the externalreferenceid column of the blockfund table.
savingstransactionkeyvarchar(32)The encoded key of the savings/deposit account transaction. Links to the savingstransaction table.



branch

a branch is the main division criteria of the organization.
Column NameData TypeDescription
creationdatedatetimeThe date on which the branch was fdirst created. Stores in the organization’s local time.
emailaddressvarchar(256)The email address defined for a branch
encodedkeyvarchar(32)The encoded key for this database entry. This ID can be used to refer to this entity when using our API.
idvarchar(32)A unique user defined ID. Required
lastmodifieddatedatetimeThe date on which the entry was last modified. Stored in the organization’s local time.
namevarchar(256)The name of the branch.
notesmediumtextNotes for the branch, usually stored as HTML
phonenumbervarchar(256)The phone number defined for a branch
statevarchar(255)Indicates whether the branch is ACTIVE or INACTIVE.



branchcustomvalue

Holds values for custom information for branches. Please note: This table is for an upcoming feature and may not contain any data for your organization.
Column NameData TypeDescription
definitionidsmediumtextHolds the list of the custom field encodedkeys generated from the JSON held in the values column.
linkedentitykeysmediumtextHolds the list of linkedentitykeys generated from the JSON held in the values column. These will be the entities to which this custom field is linked for custom fields of type CLIENT_LINK, GROUP_LINK or USER_LINK.
parentkeyvarchar(32)The encodedkey of the entity holding the custom values within the values JSON column. This will be the encodedkey of the entity with which these custom field values are associated.
valuesjsonHolds all the custom field data in a JSON structure, including keys, IDs, values and indexes.



bulkprocessingprocesseditems

Column NameData TypeDescription
bulkitemtemporarilyuuidvarchar(32)
bulkkeyvarchar(32)
creationdatedatetime
encodedkeyvarchar(32)
persistedentitykeyvarchar(32)



cardacceptor

used for keeping card acceptor details linked to hold and financial transaction requests.
Column NameData TypeDescription
cityvarchar(256)The city of the acceptor.
countryvarchar(256)The country of the acceptor.
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables, in this case, the authorizationhold table.
mccint(11)The business code of the acceptor, the code can be used for authorization holds expiration. For example for some MCC values, the hold could expire faster.
namevarchar(256)The name of the acceptor.
statevarchar(256)The state of the acceptor.
streetvarchar(256)The street and house number of the acceptor.
zipvarchar(256)The zip of the acceptor.



cardreference

entity used for keeping the card assignments for an account. an account can have more cards assigned, but a card can be assigned only to one account.
Column NameData TypeDescription
accountkeyvarchar(32)Keeps the encoded key of the account for which the cart was referenced. Required
cardreferencetokenvarchar(72)Keeps the card id reference token. Required
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables, in this case, the authorizationhold table.
typevarchar(32)The type of card, ie. debit or credit.



cardtransactionreversal

used for the context that caused a card transaction reversal.
Column NameData TypeDescription
amountdecimal(50,10)The amount to be debited. Required
cardreferencetokenvarchar(72)The card reference token used to reference the user card.
cardtransactionexternalreferenceidvarchar(256)The reference ID of the corresponding card transaction external reference id
creationdatedatetime(6)Keeps the encoded key of the savings account for which the cart was referenced. Required
currencycodevarchar(3)The ISO currency code, in which the request was made.
encodedkeyvarchar(32)The encoded key for this database entry.
externalreferenceidvarchar(256)The external reference ID to be used to reference this request in the subsequent requests.
originaltransactionkeyvarchar(32)A reference to the original transaction that is being reversed here.
transactionchannelidvarchar(32)Foreign key for the transactionChannel table which points to the transaction channel used for this card transaction.
transactionkeyvarchar(32)The ID for this transaction.



cardtransactionsource

used for the context that caused a card transaction.
Column NameData TypeDescription
amountdecimal(50,10)The amount to be debited. Required
cardacceptorkeyvarchar(32)The encodedkey of a row in the cardacceptor table containing details of the acceptor for this transaction.
cardreferencetokenvarchar(72)The card reference token used to reference the user card.
creationdatedatetime(6)Keeps the encoded key of the savings account for which the cart was referenced. Required
currencycodevarchar(3)The ISO currency code, in which the request was made.
encodedkeyvarchar(32)The encoded key for this database entry.
externalauthorizationreferenceidvarchar(256)The external authorization hold reference ID, which relates this card transaction to a previous authorization hold.
externalreferenceidvarchar(256)The external reference ID to be used to reference the card transaction in subsequent requests..
isadvicebit(1)Whenever the given request should be accepted without any validations(i.e. advice). Required
lastmodifieddatedatetime(6)The date on which this row was last modified. As UTC.
linkedtransactionkeyvarchar(32)The encodedKey of the linked financial transaction.
linkedtransactiontypevarchar(32)The type of the linked transaction (DEPOSIT / LOAN).
transactionchannelidvarchar(32)The ID of the channel through which the payment is done.
usertransactiontimevarchar(256)The date&time string at which the transaction was created by the merchant.



centre

a centre is a common meeting area where credit officers, the individuals and group clients go to. each centre is assigned to a branch (a branch can have multiple centres) and might have a specific meeting day and location.
Column NameData TypeDescription
assignedbranchkeyvarchar(32)Foreign key to a Branch. It defines the branch to whom this centre belongs to. Required
creationdatedatetimeThe date on which the centre was created. Stored in the organization’s local time.
encodedkeyvarchar(32)The encoded key for this database entry. This ID can be used with our API to get details on a specific Centre.
idvarchar(32)An unique user defined ID. Required
lastmodifieddatedatetimeThe date on which the entry was last modified. Stored in the organization’s local time.
meetingdayvarchar(256)The day of week when the meeting is scheduled. The clients/groups that are associated to a centre, that have a meeting day, can have the repayments due date in the specified meeting day.
The meeting day can be:
- MONDAY
- TUESDAY
- WEDNESDAY
- THURSDAY
- FRIDAY
- SATURDAY
- SUNDAY
migrationeventkeyvarchar(32)Foreign key to a specific Migration Event. A centre might be imported using the Data Import feature and all the data imported from a file will be a part of a specific migration event (when this event will be reverted, all the data associated with it will be removed from the system)
namevarchar(256)The name of the centre. Required
notesmediumtextOptional notes that can be entered when the centre is created/edited.
statevarchar(255)State of the Centre: ACTIVE/INACTIVE.



centrecustomvalue

Holds values for custom information for centres. Please note: This table is for an upcoming feature and may not contain any data for your organization.
Column NameData TypeDescription
definitionidsmediumtextHolds the list of the custom field encodedkeys generated from the JSON held in the values column.
linkedentitykeysmediumtextHolds the list of linkedentitykeys generated from the JSON held in the values column. These will be the entities to which this custom field is linked for custom fields of type CLIENT_LINK, GROUP_LINK or USER_LINK.
parentkeyvarchar(32)The encodedkey of the entity holding the custom values within the values JSON column. This will be the encodedkey of the entity with which these custom field values are associated.
valuesjsonHolds all the custom field data in a JSON structure, including keys, IDs, values and indexes.



changeduedateevent

Tracks changes made to the due date for loans with fixed day of month payment schedule. This operation will be made via api, read more about how to carry out this operation as well as the product constraints in our repayments schedule editing support article.
Column NameData TypeDescription
dayofmonthtinyint(4)The new day of the month on which installments are due.
transactionkeyvarchar(32)Foreign key which links to an entry in the loantransaction table of the type DUE_DATE_CHANGED.



client

captures information about individual clients of the mfi. clients may have their own accounts or belong to groups. clients are also assigned to users and branches. custom fields, identification documents & addresses are stored in separate tables
Column NameData TypeDescription
activationdatedatetimeThe date when the account was set into ACTIVE state (when an active account was created for him) (UTC)
approveddatedatetimeThe date when the client was set into APPROVED state (UTC)
assignedbranchkeyvarchar(32)Foreign key to the Branch table indicating which branch the client belongs to
assignedcentrekeyvarchar(32)Foreign key to the Centre table indicating to which centre the client belongs to.
assigneduserkeyvarchar(32)Foreign key to the Users table indicating who the the user assigned to the client is (ie: which credit officer is responsible for them)
birthdatedatetimeDate of when the client was born (Organization Time)
clientrolekeyvarchar(32)The key of the the client role this client belongs to
closeddatedatetimeThe date when the client was Exited or Blacklisted (UTC)
creationdatedatetimeThe date on which this client was first entered into the system.
emailaddressvarchar(256)Email address of the client
encodedkeyvarchar(32)The encoded key for this database entry. This ID can be used with our API to get details on a specific Client and is also used as a foreign key to link with other tables, such as the groupmember, grouprole, passwordresetrequest, identificationdocument tables, among others.
firstnamevarchar(256)The first name(s) of the client. Required.
gendervarchar(256)Gender of the client. Must be one of: MALE or FEMALE.
grouploancycleint(11)The client’s current group loan cycle. That is, how many successful loans they’ve been part of as a group. Auto-incremented on successful account closure.
homephonevarchar(256)The home phone number of the client
idvarchar(32)A unique (for Clients) human-readable identifier for the the client id. Generated automatically when storing a client through the application but can be set to anything during import. Required
lastmodifieddatedatetimeThe date on which some aspect of this client entry was last modified.
lastnamevarchar(256)The last name(s) of the client. Required
loancycleint(11)The client’s current individual loan cycles. Auto-increment on successful account closure.
middlenamevarchar(256)The middle name(s) of the client.
migrationeventkeyvarchar(32)Foreign key to a specific Migration Event. A client might be imported using the Data Import feature and all the data imported from a file will be a part of a specific migration event (when this event will be reverted, all the data associated with it will be removed from the system)
mobilephone1varchar(256)Mobile phone number of the client
mobilephone2varchar(256)Mobile phone number of the client (secondary)
notesmediumtextHTML rich-text detailed notes about the client
portalpreferenceskeyvarchar(32)Foreign key to the client’s portal preferences object - if preferences have been defined for this client. These is created when the portal is first activated for the client
preferredlanguagevarchar(32)The language preference for this user. Must be one of ENGLISH, PORTUGUESE, RUSSIAN, SPANISH, FRENCH, CHINESE, GEORGIAN, INDONESIAN, ROMANIAN, BURMESE, GERMAN.
profilepicturekeyvarchar(32)Foreign key to the Images table containing the image of the client’s profile picture
profilesignaturekeyvarchar(32)Foreign key to the Images table containing the signature image for this client
statevarchar(256)Like the accounts, the clients might go to an approval process, by the MFIs.
A client can be:
- PENDING_APPROVAL: is waiting for approval
- INACTIVE: has only inactive accounts
- ACTIVE: has at least one active account
- EXITED: was closed normally
- BLACKLISTED: was closed and blacklisted



clientcustomvalue

Holds values for custom information for clients. Please note: This table is for an upcoming feature and may not contain any data for your organization.
Column NameData TypeDescription
definitionidsmediumtextHolds the list of the custom field encodedkeys generated from the JSON held in the values column.
linkedentitykeysmediumtextHolds the list of linkedentitykeys generated from the JSON held in the values column. These will be the entities to which this custom field is linked for custom fields of type CLIENT_LINK, GROUP_LINK or USER_LINK.
parentkeyvarchar(32)The encodedkey of the entity holding the custom values within the values JSON column. This will be the encodedkey of the entity with which these custom field values are associated.
valuesjsonHolds all the custom field data in a JSON structure, including keys, IDs, values and indexes.



clientrole

a role which describes the intended use of a client or group in the system
Column NameData TypeDescription
canguaranteebit(1)Whether this role can guarantee for other clients/groups
canopenaccountsbit(1)Whether this role can open loan/savings accounts.
clienttypevarchar(255)The category addressed by this role:
- CLIENT
- GROUP
createdbyuserkeyvarchar(32)The key of the user who created the role
creationdatedatetimeThe date when the role was created (as UTC).
descriptionvarchar(256)Description text for client roles
encodedkeyvarchar(32)The encoded key for this database entry.
idvarchar(255)The id of the client role
idpatternvarchar(32)The patterm used to generate IDs when new clients are created.
indexint(11)The index giving the order of the roles
namevarchar(255)The name of the client role
requireidbit(1)Whether it is mandatory for the client to have an ID
usedefaultaddressbit(1)Field to indicate if this the default address should be used



columnconfiguration

stores a configuration for loans/savings/clients/groups according to the specified list type. it keeps track of the column order, for the given list, can be shared with other users or can include the computed totals (for the number columns).
Column NameData TypeDescription
customconfigurationinfo_encodedkey_oidvarchar(32)Foreign key to customConfiguration table with reference to the entity holding common information for the custom configuration enitites
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID.
includetimestampbit(1)Whether to include timestamp or not.
includetotalsbit(1)Specifies whether to include total values for the numeric and money columns
sortingcolumn_encodedkey_oidvarchar(32)Foreign key to the fieldColumn table pointing the column used for sorting.
sortingordervarchar(256)Order of sorting, can be:
- ASCENDING
- DESCENDING



comment

for each entity available in the application (client, group, account, user, branch etc.) there can be posted comments, by the users.
Column NameData TypeDescription
creationdatedatetimeThe date when the comment has been created.
encodedkeyvarchar(32)The globally unique encoded key for this comment.
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
parentkeyvarchar(32)The parent of the comment is the object it belong to (eg, a client)
textmediumtextThe comment text.
userkeyvarchar(32)The user who left the comment.



contract

table used to model the concept of contract. further a contract can have multiple accounts mapped to it.
Column NameData TypeDescription
creationdatedatetime(6)Date when the contract was created. UTC
encodedkeyvarchar(32)Primary key of the contract table.
lastmodifieddatedatetime(6)Date when the contract was last time modified. UTC
oldcontractkeyvarchar(32)Encoded key of the old contract entity (i.e SavingsAccount).
productkeyvarchar(32)Encoded key of the product used by the contract.
userkeyvarchar(32)Encoded key of the user which triggered the action to create the contract.



contractaccount

table used to hold the mapping between a contract entity and associated accounts.
Column NameData TypeDescription
accountkeyvarchar(32)Identifier of the account which is mapped to the contract.
accountmnemovarchar(256)Indicate the type of the account which is mapped to the contract (i.e MAIN, OVERDRAFT, etc)
closeddatedatetime(6)Date when the account was closed, null if the the account is not closed yet. In Organization Time Zone
contractkeyvarchar(32)Encoded key of the contract to which the account belongs.
creationdatedatetime(6)Date when the account mapping was created. UTC
encodedkeyvarchar(32)Primary key for this table.
lastmodifieddatedatetime(6)Date when the account mapping was last time modified. UTC
userkeyvarchar(32)Encoded key of the user which determined the account mapping to be created.



currency

holds details about the currency being used by the mfi. not user configurable.
Column NameData TypeDescription
codevarchar(3)Official ISO 4217 code, for example: CAD, USD or EUR. For more information, see ISO 4217 on Wikipedia.
creationdatedatetimeUTC date of creation
currencysymbolpositionvarchar(256)Possible values:
- BEFORE_NUMBER
- AFTER_NUMBER
digitsafterdecimalint(11)Number of digits which the currency has after the decimal places for display
isbasecurrencybit(1)Whether the currency is the base one used by the organization
lastmodifieddatedatetimeDate of last modification. As UTC.
namevarchar(256)Name of the currency, for example “Canadian dollar”
symbolvarchar(256)Short symbol like ‘$’



currencymapping

holds a list of currencies associated to this product. an account for the product can use only the currencies associated to this product.
Column NameData TypeDescription
currencycodevarchar(32)The currency code associated to this product. Required
indexint(11)Column used to sort currencies inside list. Required
parentkeyvarchar(32)The encoded key of the parent, foreign key to the currency table.



customconfigurationinfo

entity holding common information used in the custom configuration entities customview, customfilter or columnconfiguration.
Column NameData TypeDescription
creationdatedatetimeUTC date of creation
dataviewtypevarchar(256)View type for this configuration:
- LOANS
- SAVINGS
- etc.
encodedkeyvarchar(32)The encoded key for this configuration, this is an autogenerated and globally unique ID.
indexinlistint(11)Specifies the position in an outside collection of custom configurations.
lastmodifieddatedatetimeUTC last modified date
namevarchar(256)The name of the custom configuration
sharedbit(1)Specfiies whether this configuration is shared with all users.
userkeyvarchar(32)The key of the user who created this configuration



customfield

a custom field is a user-defined field which is applicable for any other type of object such as a client or group. the customfield define a type (such as education) and the customfieldvalue defined the individual stored value for any given client (such as bachelors)
Column NameData TypeDescription
amountsmediumblob With our custom fields, it’s easy to assign value amounts to certain field selections. This is really beneficial for social performance monitoring. It assigns values to custom fields selections and allow performing reporting and interface analysis on them.
builtincustomfieldidvarchar(255)The field that is part of the builtIn custom fields (custom fields whose values are store in entity table for example Client.firstName).
creationdatedatetimeThe date when the custom field was created - Stored as UTC
customfieldset_encodedkey_oidvarchar(32)Links this entry to a custom field set definded in the customfieldset table.
datatypevarchar(256)Type of value which is to be stored (refers to the representation of CustomFieldValue.value). Must be one of:
- STRING
- SELECTION
Required
descriptionvarchar(256)A short description of the specific custom field.
editusagerightskeyvarchar(32)The usage rights that describes the edit access to the Custom Field. Foreign key to the usageRights table.
encodedkeyvarchar(32)The encoded key of this custom field. Please note: this is an automgenerated ID and not the same as the user defined custom field ID.
idvarchar(32)Unique, user-defined ID for the custom field object
indexinlistint(11)Index of the custom field in the list of all custom fields with the same type; -1 means that this custom field was never ordered by the application
isdefaultbit(1)Whether the field is to be displayed as a default field when creating the client/group/etc. Required
isrequiredbit(1)Whether the field is required when creating the client/group/etc. Required
lastmodifieddatedatetimeThe last date when the custom field was changed - Stored as UTC
namevarchar(256)The name of the custom field. Such as ‘Education’Required
statevarchar(256)Custom field state
- NORMAL - The default state for a custom field
- DEACTIVATED - Used to mark the custom field as deactivated
temporaryidvarchar(32)Temporary field, valid form of the id
typevarchar(256)Type of custom field. Defines for whom this field is applicable. Some fields are for clients, whereas other are for groups, etc.
Must be one of:
- CLIENT_INFO
- GROUP_INFO
- BRANCH_INFO
- CREDIT_OFFICER_INFO
Required.
uniquebit(1)Indicates that the values for this custom field needs to be unique. It can be used only for text type custom fields
validationpatternvarchar(256)If validation has been set for the custom field, for example, the value should only be digits or should be in the format 123-456, the validation pattern will be held in this field.
valuelengthvarchar(256)Whether the custom field is SHORT or LONG, this mostly has an impact on how the data from this custom field is displayed in the Mambu UI, generally all custom field values have a maximum length of 2048 characters.
valuesmediumblob Used to store the predefined values for the dataType.SELECTION customFields. For example: - name = ‘Occupation’; - values = ‘Teacher’, ‘Student’;
viewusagerightskeyvarchar(32)The usage rights that describes the view access to the Custom Field. Foreign key to the usageRights table.



information about the availability of a custom field for another entity (product, client role)
Column NameData TypeDescription
customfieldlinks_encodedkey_ownvarchar(32)The key to the custom field. Required
encodedkeyvarchar(32)The encoded key for this link, this is an autogenerated and globally unique ID.
entitylinkedkeyvarchar(32)The key to the loan, savings product, or client type, this custom field might be assigned to. When the key is set, the custom field can be used for the accounts made after that product. Required
isdefaultbit(1)Whether the linked custom field is displayed by default when creating a new entity. Required
isrequiredbit(1)Whether the linked custom field is displayed by default for the linked entity. Required
linktypevarchar(32)Specifies the link type, like product or a client role. Required



customfieldselection

entity holding a selection value for a custom field of type customfield.datatype.selection. the entity will also keep the score for that given value and if the parent custom field has a dependency on another custom field the dependency rule will be kept in the customfilterconstraint.
Column NameData TypeDescription
constraintkeyvarchar(32)The key of the constraint that keeps the dependency on the parent custom field. Can be null if no parent is assigned.
customfieldkeyvarchar(32)The key of the custom field associated with this selectible value
encodedkeyvarchar(32)The encoded key for this selectible value, this is an autogenerated and globally unique value.
idvarchar(32)An automatically generated ID for the selectible value.
scoredecimal(19,0)The score for the selectible value (credit scoring feature).
selectionindexint(11)The index in list for this selectible value.
valuevarchar(255)Value that appears in the dropdown and can be selected by the user.



customfieldset

defines a set of custom fields for grouping them on the interface
Column NameData TypeDescription
builtintypevarchar(255)Represents the special sets which contains the configurations for fields that are part of the entities
createddatedatetimeThe date when this set was created (as UTC).
encodedkeyvarchar(32)The encoded key for this set of custom fields, this is an autogenerated and globally unique ID.
idvarchar(32)The custom field set identifier.
indexinlistint(11)Index of the set in the list of all sets with the same type.
lastmodifieddatedatetimeThe date when this set was last modified (as UTC).
namevarchar(256)The name of the custom field set. Required
notesmediumtextA short description of the specific custom field set.
temporaryidvarchar(32)Temporary field, valid form of the id
typevarchar(256)Type of custom field set. Defines for whom this set is applicable to. Some fields are for clients, whereas other are for groups, etc.
Must be one of:
- CLIENT_INFO
- GROUP_INFO
- BRANCH_INFO
- CREDIT_OFFICER_INFO
Required.
usagevarchar(32)Custom field set usage. Enum used for deciding how the Custom field set will be used in the UI and how the custom field values will be stored.
- SINGLE - Default behavior, when the custom field set is displayed in the UI and can be used only once on one entity. The custom fields can be add/removed from the custom field set.
- GROUPED - Custom field set is allowed multiple times for the same entity. The entity can have multiple custom field values for the same custom field.



customfieldvalue

to store the value for a custom field for a client or group. for instance, if the customfield is education then the value may store the string bachelor's degree
Column NameData TypeDescription
amountdecimal(50,10)With our custom fields, it’s easy to assign value amounts to certain field selections. This is really beneficial for social performance monitoring. It assigns values to custom fields selections and allow performing reporting and interface analysis on them.
customfieldkeyvarchar(32)Foreign key to the CustomField. Required
customfieldsetgroupindexint(11)Field used for deciding which is the order of the custom field sets for {@link CustomFieldSet.Usage}.GROUPED. Where a custom field set can be duplicated and used multiple times for the same entity
encodedkeyvarchar(32)The encoded key for the value of this custom field, this is an autogenerated and globally unique ID.
indexinlistint(11)Order of the address if the parent holder has multiple addresses (for display/formatting purposes. That is, 0 is displayed before 1, etc.
linkedentitykeyvaluevarchar(32)Key of the linked entity stored as value for the custom field
parentkeyvarchar(32)Foreign key to the holder of this custom field. That is, may refer to the Client.encodedKey or Group.encodedKey, etc. Required
valuevarchar(2048)Value of the field (such as Bachelors if the CustomField was Education)



customfilter

represents a filter saved by a user for a certain list displayed in the application.
Column NameData TypeDescription
customconfigurationinfo_encodedkey_oidvarchar(32)Foreign key to the entry in the customConfigurationInfo table containing a configuration information for the current filter
encodedkeyvarchar(32)The encoded key for this filter, this is an autogenerated and globally unique ID.



customfilterconstraint

represents a filtering condition as example ‘client age between 15 and 100’.
Column NameData TypeDescription
customfieldkeyvarchar(32)The custom field key after which the filtering is done. A reference to the customfield table.
datafieldtypevarchar(256)Field type:
- NATIVE
- CUSTOM
datafieldvaluevarchar(256)The name of the data field. For example the constraint “Loan Purpose EQUALS Agriculture Loan” will have as data field value Loan Purpose.
dataitemtypevarchar(256)Item type:
- LOANS
- SAVINGS
- etc.
datatypevarchar(256)Data type:
- BIG_DECIMAL
- DATE
- LONG
- MONEY
- etc.
encodedkeyvarchar(32)The encoded key for this row, used as the primary key for this table.
filterconstraints_encodedkey_ownvarchar(32)A reference to the encodedkey field of the entry in the customfilter table to which this constraint relates.
filterconstraints_integer_idxint(11)If one or more filter constraints are set for the same custom filter, this field shows their position in the list. The first constraint having index 0, the second 1 and so on.
filterelementvarchar(256)Filter element:
- EQUALS
- MORE_THAN
- LESS_THAN
- STARTS_WITH
- BETWEEN
- ON
- AFTER
- BEFORE
- TODAY
- THIS_WEEK
- THIS_MONTH
- THIS_YEAR
- LAST_DAYS
groupnumberint(11)Specifies the group expression number for which this constraints is part of.
linkingoperatorvarchar(32)The operator on which this constraint is linking to the previous expression. Eg. AND loan.id='B' it is the AND operator part
- AND
- OR
secondvaluevarchar(2048)The first filtering value of the filter. For example the constraint “Loan Purpose EQUALS Agriculture Loan And Science” has as second value “Science”.
valuevarchar(2048)The first filtering value of the filter. For example the constraint “Loan Purpose EQUALS Agriculture Loan And Science” has as (first) value “AgricultureLoan”.



custommenuitem

entity class for holding information about a custom menu item. it offers the possibility to customize what menus are to be displayed in the navigation bar along with their name and other defining properties.
Column NameData TypeDescription
creationdatedatetimeThe date when the custom menu item was created - Stored as UTC
encodedkeyvarchar(32)The encoded key for this menu item, this is an autogenerated and globally unique ID.
includecollectionsbit(1)Whether to include collections item in custom transactions menus
lastmodifieddatedatetimeThe last date when the custom menu item was changed - Stored as UTC
namevarchar(255)The name of the custom menu item
statevarchar(255)Holds the state which defines the accessibility for the current custom menu item
typevarchar(255)The type of the custom menu item (LOANS, SAVINGS etc)
userkeyvarchar(32)The key of the user who created this menu item.
viewusagerightskeyvarchar(32)Foreign key to the usageRights table entry containing the usage rights that describes the view access to the Custom Menu Item.



custommenuitempostion

model used for maintaining the index of a custom menu item in a parent. list.
Column NameData TypeDescription
custommenuitempositions_encodedkey_ownvarchar(32)Foreign key linking to an entry in the userpreferences table.
encodedkeyvarchar(32)A unique key for this row.
indexint(11)The index of this row.
menuitemkeyvarchar(32)The ID of the menu item. Foreign key to the custommenuitem table.



custompaymentamount

model capable of holding the custom payment amount introduced by a client for a specific payment amount type.
Column NameData TypeDescription
amountdecimal(50,10)The custom payment amount introduced by the client.
custompaymentamounts_encodedkey_ownvarchar(32)The key of the entry in the loantransaction table recording this repayment.
custompaymentamounts_integer_idxint(11)Index in a list when the total sum of this custom payment is allocated to more than one type of charge (eg, interest and princpal or late payment fees and pentalties).
custompaymentamounttypevarchar(32)Indicates to which part of the loan this portion of the custom payment relates (UPFRONT_DISBURSEMENT_FEE, INTEREST, PRINCIPAL, MANUAL_FEE, LATE_REPAYMENT_FEE, PAYMENT_DUE_FEE, PENALTY, etc).
encodedkeyvarchar(32)A unique key for this row.
taxonamountdecimal(50,10)the tax over the custom payment amount introduced by the client.



custompredefinedfee

entity class which "customize" a specific predefined fee. when a custom predefined fee is used the details of the predefined fee can be obtained directly from the referenced predefined fee entity or some of the settings as the amount can be customized and obtained directly from this custom predefined fee entity.
Column NameData TypeDescription
amountdecimal(50,10)Custom amount for the fee
encodedkeyvarchar(32)The encoded key for this fee, this is an autogenerated and globally unique ID.
predefinedfeekeyvarchar(32)Foreign key to the entry int the predefinedFee table containing the predefined fee to be customized.



custompredefinedfeemapping

list of fees that should be applied at the disbursement time.
Column NameData TypeDescription
custompredefinedfeekeyvarchar(32)The encoded of the predefined fee to be applied. Foreign key to custompredefinedfeekey table.
indexint(11)
parentkeyvarchar(32)Link to the entity that caused the fee to be charged, for example, if the fee is incurred on disbursement, this will link to an entry in the disbursementdetails table.



custompreference

model used to maintain custom preferences for a user.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this set of preferences, this is an autogenerated and globally unique ID.
indexinlistint(11)Index of the user custom preference in the list of all custom preferences with the same type; -1 means that this custom preference was never ordered by the application
preferencetypevarchar(32)Containing values for the opening columns in Trial Balance report.
preferenceviewtypevarchar(32)Containing view that offer column preferences functionalities
usercustompreferences_encodedkey_ownvarchar(32)Foriegn key to link this entry to one in the userpreferences table.
valuebit(1)If the column is displayed or not.



customrepaymentsettings

holds a link between a repayment and a type of settings which were customized by the user for the repayment.
Column NameData TypeDescription
customsettings_encodedkey_ownvarchar(32)
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID.
loantransactionkeyvarchar(32)The key of the loan transaction which caused this custom settings
sourcevarchar(32)The source of the settings (how the custom settings were created).
- USER_INPUT,
- INSTALLMENT_PAID
Required
typevarchar(32)The type of the settings which were customized by the user for the repayment to which this entity is linked.
- CUSTOM_DUE_DATE
- CUSTOM_PRINCIPAL
Required



customrepaymenttransactionsettings

records settings for a custom repayment type transaction.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key.
installmentkeyvarchar(32)The key of the installment.
prepaymentrecalculationmethodvarchar(256)one of:
- NO_RECALCULATION,
- RESCHEDULE_REMAINING_REPAYMENTS,
- RECALCULATE_SCHEDULE_KEEP_SAME_NUMBER_OF_TERMS,
- RECALCULATE_SCHEDULE_KEEP_SAME_PRINCIPAL_AMOUNT,
- RECALCULATE_SCHEDULE_KEEP_SAME_TOTAL_REPAYMENT_AMOUNT,
- REDUCE_AMOUNT_PER_INSTALLMENT,
- REDUCE_NUMBER_OF_INSTALLMENTS,
- REDUCE_NUMBER_OF_INSTALLMENTS_NEW.
repaymenttransactionkeyvarchar(32)Foreign key linking to an entry in the loantransaction table



customreport

stores a custom report created by the user including all the indicators which are in the report, summary, etc. these reports can be created for branches, centres, officers, loan products or savings products.

read more about creating custom reports for our ui on our support page.
Column NameData TypeDescription
creationdatedatetimeThe date on which this report was created. As UTC.
descriptionmediumtextA description of this report. This will show up to users when they view the report in the Mambu UI.
encodedkeyvarchar(32)The encoded key for this report, this is an autogenerated and globally unique ID.
filtervarchar(256)Indicates the entity covered by this report. Can be one of BRANCH, SAVINGS_PRODUCT, LOAN_PRODUCT, CENTRE, OFFICER.
indicatorsmediumblob An array of indicators used in the report.
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
namevarchar(256)The name of the report.



customview

entity holding information about a custom view. a custom view represents a composition between a customfilte, a columnconfiguration and a datafield used for sorting purposes. it is used for displaying data represented by data items by offering information for filtering, sorting, arranging and paginating this data.
Column NameData TypeDescription
columnconfiguration_encodedkey_oidvarchar(32)The columns used by the view
customconfigurationinfo_encodedkey_oidvarchar(32)Holds view data like name, view type
encodedkeyvarchar(32)The encoded key for this view, this is an autogenerated and globally unique ID. This ID is used to call up this view in our UI.
filter_encodedkey_oidvarchar(32)The custom filter used by the view
parentmenuitemkeyvarchar(32)The key of the custom menu item under which this custom view can be found. Required
viewmodevarchar(255)The mode in which the custom view is shown:
- DETAIL
- LIST
Required
viewusagerightskeyvarchar(32)The usage rights that describes the view access to the Custom View. Required



customviewposition

maintains the index of a custom view.
Column NameData TypeDescription
customviewspositions_encodedkey_ownvarchar(32)The encoded key of an entry in the userpreferences table to which these position settings belong.
encodedkeyvarchar(32)The ID of this entry.
favoritecustomviewspositions_encodedkey_ownvarchar(32)
indexint(11)Index in list for the corresponding view.
parentkeyvarchar(32)The key of the parent entity relative to which the custom views ordering takes place. Currently, it represents the key of the parent CustomMenuItem or Dashboard for favorited custom views displayed in the dashboard.
viewkeyvarchar(32)The encoded key of the view from the customview table.



customviewpreferences

holds specific user preferences related to a custom view.
Column NameData TypeDescription
customviewkeyvarchar(32)Foreign key pointing to an entry in the customview table which defines this view.
encodedkeyvarchar(32)A unique key for this row.
isfavoritebit(1)Whether or not this view has been marked as a favourite by the user.
userkeyvarchar(32)The encoded key of the user who has set these preferences.



dashboard

stores the user dashboard setting preferences.
Column NameData TypeDescription
activitytypesmediumblob An array of activity types displayed on this dashboard.
createdbyuserkeyvarchar(32)Unique key of the user who created this dashboard. Foreign key pointing to an entry in the user table.
creationdatedatetimeThe date on which this dashboard was created. UTC
encodedkeyvarchar(32)A unique key for this row.
filterloggedinuseractivitiesbit(1)Whether the dashboard activity widget diplays all activities or has been filtered.
filterloggedinuserfavoriteviewsdatabit(1)Whether there has been a filter applied to the favourite views widget on this dashboard.
filterloggedinuserindicatorsbit(1)Whether there is a filter applied to the indicators displayed on this dashboard.
indicatorsmediumblob An array of indicators displayed on this dashboard, for example, the number of active clients or gross loan portfolio.
lastmodifieddatedatetimeThe date on which this dashboard was last edited. UTC



dashboardconfiguration

Column NameData TypeDescription
creationdatedatetime(6)
dashboardconfigurations_encodedkey_ownvarchar(32)
encodedkeyvarchar(32)
namevarchar(256)



databasechangelog

this table acts as an audit log of changes made to the database schema. for more detailed information on changes check our release notes which are published in advance of any changes being made.
Column NameData TypeDescription
authorvarchar(255)The database user who created the change.
commentsvarchar(255)Any comments relating to the changes made. In most cases our release notes will contain more information.
contextsvarchar(255)Whether the changes are pre_migration or
dateexecuteddatetimeThe date and time at which the change was made.
descriptionvarchar(255)Indicates what kind of change was made.
exectypevarchar(10)Jobs are run pre_migration and post_migration. Post migration jobs are generally to check consistency of data once everything has been migrated to a new schema.
filenamevarchar(255)The file containing the script to execute and details of the changes.
idvarchar(255)An id for reference.
labelsvarchar(255)This column is not used.
liquibasevarchar(20)The liquibase version used to make the changes.
md5sumvarchar(35)A hash used as a checksum.
orderexecutedint(11)An index starting at 1 and increasing by 1 for each time the database schema was changed.
tagvarchar(255)Tags are sometimes used to provide additional



databasechangeloglock

Column NameData TypeDescription
idint(11)
lockedbit(1)Whether or not the DB is locked.
lockedbyvarchar(255)
lockgranteddatetime



datamigrationevent

captures a data migration event (import/export) performed by a user. includes information about the details of the event as well as (for import) the state of the event if it was accepted or reverted.
Column NameData TypeDescription
creationdatedatetimeThe date on which this data import oeration was executed.
encodedkeyvarchar(32)The encoded key of this data migration event.
numcentresimportedint(11)The number of centres imported as part of this operation.
numclientsimportedint(11)The number of clients who were imported as part of this migration.
numglaccountsimportedint(11)The number of general ledger accounts which were imported as part of this job.
numgroupsimportedint(11)The number of groups which were imported in this operation.
numloanrepaymentsimportedint(11)The number of loan reapayments which were imported as part of this job.
numloansimportedint(11)The number of loan accounts which were imported as part of this operation.
numloantransactionsimportedint(11)The number of loan transactions which were imported as part of this operation.
numsavingsimportedint(11)The number of savings and deposit accounts which were imported during this job.
statevarchar(256)The state of the data migration, indicates whether it is pending, has been successfully completed or failed. Can be DRAFT, APPROVED, or REVERTED.
typevarchar(256)Whether this is an IMPORT or EXPORT data migration.



dbschemaupgradeprogress

this table tracks jobs relating to upgrading the mambu database. this generally happens when new versions of mambu are released.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for this step
endsteptimedatetime(3)The time at which the steo completed. UTC
failurereasonvarchar(3000)The reason in the case that the upgrade process failed.
releaseversionvarchar(32)The mambu release version.
stepstarttimedatetime(3)The time at which this step started. UTC
stepstatusvarchar(256)The current status of this step. SUCCESS indicates the was step completed.
upgradestarttimedatetime(3)The start time of the upgrade job that this step belongs to.
upgradestepvarchar(256)Which stage of the upgrade this step was; PRE_MIGRATION, MIGRATION, POST_MIGRATION.



decimalintervalconstraints

holds for keeping decimal min/max/default constraints.
Column NameData TypeDescription
defaultvaluedecimal(50,20)The constraint default value
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID.
maxvaluedecimal(50,20)The constraint maximum value
minvaluedecimal(50,20)The constraint minimum value



disbursementdetails

entity class which holds the informations related to the disbursement details as disbursement date, first repayment date, disbursement fees.
Column NameData TypeDescription
disbursementdatedatetimeThe activation date, the date when the disbursement actually took place. Stored as Organization Time.
encodedkeyvarchar(32)The encoded key for this disbursement, this is an autogenerated and globally unique ID.
expecteddisbursementdatedatetimeThe expected disbursement date of the account. Stored as Organization Time.
firstrepaymentdatedatetimeThe date of the first repayment. Stored as Organization Time.
transactiondetailskeyvarchar(32)Foreign key to the transactionDetails table containing the details for the disbursement transaction.



document

represents a document stored in mambu. the actual document data is not stored here in the database but externally in a different file stored, refernced to by the location. the location path is not multi-tenant aware. so even though document may be stored as tenantid/folder/file.jpeg the location will refer to folder/file.jpg and it is up to the remote file retrieval system to retrieve the document
Column NameData TypeDescription
createdbyuserkeyvarchar(32)The key of the user who created the document
creationdatedatetimeUTC date represents creation date for the document
descriptionmediumtextAdditional notes about the document
documentholderkeyvarchar(32)Who is the holder of this document, if null then no holder for the document
documentholdertypevarchar(256)Type of the holder. Valid values:
- CLIENT
- GROUP
- LOAN_PRODUCT
- SAVINGS_PRODUCT
- CENTRE
- BRANCH
- USER
- LOAN_ACCOUNT
- DEPOSIT_ACCOUNT
encodedkeyvarchar(32)The encoded key for this document. This is an autogenerated and globally unique ID.
filesizebigint(20)Size of the file in bytes
idbigint(20)The id of the document
lastmodifieddatedatetimeUTC date represents last modification date for the document
locationvarchar(256)Location of the document where it can be found /a/b/cc.jpg
namevarchar(256)Document name (provided)
originalfilenamevarchar(256)Specifies the filename
typevarchar(256)Extension type of the document



documenttemplate

representing a document template which can be associated to an entity (ex: templates for savings or lending products).
Column NameData TypeDescription
contentmediumtextThe actual template content as HTML.
creationdatedatetimeThe date on which this template was first created.
encodedkeyvarchar(32)The encoded key for this document template. This
lastmodifieddatedatetimeThe date on which this template was last modified. As UTC.
namevarchar(255)The name of this template.
typevarchar(32)Indicates what this template will be available for; TRANSACTION or ACCOUNT.



documenttemplatemapping

a list with templates associated to this product.
Column NameData TypeDescription
indexint(11)The index where multiple templates are associated to the same product.
parentkeyvarchar(32)The encoded key of a product which has assocated document templates.
templatekeyvarchar(32)The ID of a template.



duplicatefieldconstraint

stores a duplicate constraint which will be applied when saving entities.
Column NameData TypeDescription
activebit(1)Whether or not this constraint is active.
datafieldvarchar(255)Indicates the field for which these constraints will be valid. For example for a client constraint, the field may be EMAIL_ADDRESS, MOBILE_PHONE_NUMBER
dataitemtypevarchar(255)Indicates for which kind of entity these constraints are used, eg, CLIENT, IDENTIFICATION_DOCUMENT etc.
duplicateclientchecks_encodedkey_ownvarchar(32)Unique key of an entry in the generalsettings table for which these field constraints are used.
encodedkeyvarchar(32)A unique key for this row.
groupindexint(11)Indicates whether checks are related, for example the combination of a first name and last name or a last name and a birthday.
indexinlistint(11)Index for this row.



emailnotificationsettings

containing the credentials and settings for the email notifications.
Column NameData TypeDescription
emailauthentificationmethodvarchar(255)The authentication method used for this connection. AUTH_LOGIN for normal username and password authentication.
emailcredentialsprovidervarchar(255)CUSTOM indicates that your instance will use their own custom email settings.
emailtransportencryptionmethodvarchar(255)Indicates which method is used to encrypt communication between mambu and your email service provider. One of STARTTLS or SSL/TLS.
encodedkeyvarchar(32)The encoded key for these settings.
fromemailvarchar(255)The email address which will appear as the sender for emails sent from your organization.
fromnamevarchar(255)The name which as appear as the sender for emails sent by your organization.
lastmodifieddatedatetimeThe date on which these settings were last modified.
orgemailusedasfromaddressbit(1)If set to true then the email address set for your organization in the generalSettings table is to be used as the sender email address for emails sent by your organization.
passwordvarchar(255)The password required to access the email service provider.
replaytoemailvarchar(255)The email address which will appear in the reply to field for emails sent by your organization.
smtphostvarchar(255)The url used to connect to the email service provider using smtp.
smtpportint(11)The post used to connect to the email service provider using smtp
usernamevarchar(255)The username used to log in to the email service provider.



entityfeaturetoggle

Some features will be released to a small number of customers as part of a beta testing phase. This table holds information on such features and whether they have been enabled for a particular mambu instance and entity. If there is an entry in this table for a given entity, the feature toggle is on for that entity.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for this row.
entitykeyvarchar(32)The encoded key of the entity for which the toggle is put in place.
entitytypevarchar(50)The type of entity for which the feature is available, for example LOAN_ACCOUNT, CLIENT or GROUP.
featurevarchar(256)The feature in question.



exchangerate

entity class for holding information about an exchange rate entry. the exchange rate is defined between two currencies (currently between the base currency and another available currency) and offers the rates at which the target currency is sold or bought at a given time by the organization.
Column NameData TypeDescription
buyratedecimal(50,20)The rate at which the organization accepts money (e.g. incoming repayments, deposits). Required
encodedkeyvarchar(32)The encoded key for this entry, this is an autogenerated and globally unique ID.
enddatedatetimeThe date when the rate ended to be valid (as Organization Time).
fromcurrencycodevarchar(3)The base currency used in the exchange rate. Required
sellratedecimal(50,20)The rate at which the organization gives money (e.g. disbursals, withdrawals). Required
startdatedatetimethe date when the rate starts to be used (as Organization Time). Required
tocurrencycodevarchar(3)The target currency used in the exchange rate. Required
userkeyvarchar(32)The user who added the exchange rate.



failedattempt

holds login failed attempts
Column NameData TypeDescription
countint(11)Consecutive failed attempts count
datesmediumblob Maintained in UTC. Stores dates of failed attempts.
encodedkeyvarchar(32)The encoded key for this failed attempt, this is an autogenerated and globally unique ID used to index this table.
typevarchar(256)Indicates whether the login attempt was via USERNAME or an API Consumer key, which will have the value IP.
valuevarchar(255)Value to help identify the actor behind the failed attempt eg. the username if the login form was used



federatedauthenticationsettings

holds organization settings about the federation of user authentication. holds configuration, as example the url of the saml 2.0 identity provider along with a certificated in order to externalize the user authentication. check our support page for more information oin using federated authentication with mambu.
Column NameData TypeDescription
acsurlvarchar(2048)An optional field for added ability to use proxies when using IdP with a dedicated environemnt. This will correlate the Response with the original AuthRequest.
certificatevarchar(4096)The certificate from your identify provider.
certificateexpirydatedatetime(6)The date on which the certificate is set to expire.
creationdatedatetime(6)The date on which these settings were first created.
enablesinglelogoutbit(1)Whether Single Log Out is enabled, meaning that when a user logs out of one service, they are logged out of all services.
encodedkeyvarchar(32)The encoded key for these settings. A unique ID
federationstatevarchar(256)Whether federated authentiation is ACTIVE or INACTIVE for this Mambu instance.
federationusagevarchar(256)Identifies the type of federation (for REGULAR or support users).
idpissueridvarchar(2048)The issuer ID from your identity provider.
idplogouturlvarchar(2048)The URL for intitiating a single logout request. This field should not be changed.
lastmodifieddatedatetime(6)The date on which this row was last modified. As UTC.
namevarchar(256)The name for this connection to an identity provider.
urlvarchar(2048)The Single Sign-on Endpoint from your identity provider.
privateIdPtinyint(1)Allows you to use and configure a private IdP in the Mambu Federated Authentication module. Private IdPs are not visible or pingable on the Internet.
isAccessibletinyint(1)This field marks if the IdP settings are accessible or not. This is used for Mambu delivery users as the settings should expire after a certain time period.
deactivateDatedatetime(6)This field marks the date when the IdP settings should automatically expire. This is used for Mambu delivery users as the settings should expire after a certain time period.



fieldchangeitem

a field change log which is associated with an activity. it stores the name of the changed field, the original and the new value of the field.
Column NameData TypeDescription
fieldchangenamevarchar(256)
fieldchanges_encodedkey_ownvarchar(32)The encodedkey for a row in the activities table where this change was recorded.
fieldchanges_integer_idxint(11)
fielddetailkeyvarchar(32)
fielddetailnamevarchar(256)
idbigint(20)
newvaluemediumtextThe new value of the field.
originalvaluemediumtextThe original value of the field.



fieldcolumn

entity for storing a datafield column into the database.
Column NameData TypeDescription
customfieldkeyvarchar(32)If this row is relating to a custom field then this will hold the unique key of that custom field in the customfield table.
datafieldvarchar(255)Field containing the information for this custom field. For example, for a group custom field this could be NUMBER_OF_MEMBERS or CREDIT_OFFER_NAME.
dataitemtypevarchar(255)Indicates the type of entity the configuration relates to, for example CLIENT, GROUP, LOANS, SAVINGS, TRANSACTION, …
encodedkeyvarchar(32)A unique key for this row.
fieldcolumns_encodedkey_ownvarchar(32)Points to an entry in the columnconfiguration table containing some settings for this column.
fieldcolumns_integer_idxint(11)Index if there is more than one row in this column relating to the same entry in the columnconfiguration table.



generalsettings

common settings for a mambu instance which are configured under the mambu admin. in here we can specify the default transaction channel key used in the organization, or the end of day processing method used.
Column NameData TypeDescription
accountingcutofftimevarchar(256)The cutoff time for daily accounting if one has been configured in Administration > Financial Setup > EOD Processing
approvaldisbursaltwomanruleenabledbit(1)If there are required separate users for approvals and disbursals
arrearsdaysbeforewriteooffint(11)Number of days that are required before an account can be written off.
assignmentconstraintsmediumblob List of required assignments for Clients and Groups
automatedaccountingclosuresintervalint(11)The interval (number of days) between the execution of automated accounting closures. If this number is 0, no automated closure is performed. Required.
clientidformatvarchar(256)Pattern for generating client ids (uses letter & digit symbols as defined in IDGenerator)
dateformatsmediumblob The possible values the type of a date format can take (DATE_FORMAT or DATE_TIME_FORMAT) together with the format of the date (eg. “dd-MM-yyyy”)
decimalseperatorvarchar(256)Whether numbers are interpreted such as "$10.20" or "$10,20" to mean 10 dollars and 20 cents.
- COMMA
- DECIMAL
defaultclientrolekeyvarchar(32)Specifies the organization default client role of the current tenant.
defaultclientstatevarchar(256)The state that a client is set when first created
- PENDING_APPROVAL
- INACTIVE
- ACTIVE
- EXITED
- BLACKLISTED
- REJECTED
defaultgrouprolekeyvarchar(32)Specifies the organization default group role of the current tenant.
defaultlineofcreditstatevarchar(256)The state that a line of credit is set when it’s first created
- PENDING_APPROVAL
- APPROVED
- ACTIVE
- CLOSED
- WITHDRAWN
- REJECTED
defaulttransactionchannelkeyvarchar(32)Specifies the default transaction channel of the current tenant
duplicateclientconstraintactionvarchar(255)Action to be taken when the duplicate client validation fails
- NONE
- WARNING
- ERROR
enabledcomponentsmediumblob The list of all the enabled components for the current tenant
- LOANS
- DEPOSITS
- BRANCHES
- CENTRES
- CLIENTS
- GROUPS
- ACCOUNTING
- CREDIT_OFFICERS
encodedkeyvarchar(32)A unique key for this row.
eodprocessingmethodvarchar(256)Specifies EOD processing settings whether is automatic, runs every midnight or manual, runs when the client initiates the action from the interface.
- AUTOMATIC
- MANUAL
exposureamountdecimal(50,10)How much (number value) a client can have in outstanding loans with the organization at any time.
exposuretypevarchar(256)How much a client can have in outstanding loans with the organization at any time.
- UNLIMITED
- SUM_OF_LOANS
- SUM_OF_LOANS_MINUS_SAVINGS
groupidformatvarchar(256)Pattern for generating group ids (uses letter & digit symbols as defined in IDGenerator)
groupsizelimittypevarchar(256)Group size limitation type
interbranchtransferglaccountkeyvarchar(32)The key of the GL Account which will be used for inter-branch transfers.
lineofcreditidformatvarchar(32)Pattern for generating line of credit ids (uses letter & digit symbols as defined in IDGenerator)
maxallowediddocumentattachmentsint(11)The maximum number of identification documents which can be attached to an entity.
maxallowedjournalentrydocumentattachmentsint(11)The maximum number of attachments for a journal entry. Required.
maxallowedundoclosureperiodint(11)Maximum of days we allow users to undo of close obligations met for a loan account. Required.
maxgroupsizelimitint(11)Maximum group size allowed; null values causes ignoring of the limit.
mingroupsizelimitint(11)Minimum group size allowed; null values causes ignoring of the limit.
multiplegroupmembershipsvarchar(256)Constraint on whether clients can belong to more than one group or not.
- UNLIMITED
- ONE_GROUP
multipleloansvarchar(256)Shows if multiple loans are allowed or not:
- UNLIMITED
- ONE_LOAN
otheriddocumentsenabledbit(1)Whether the other id documents are enabled or not
overdraftinteresteodbalancedatedatetimeThe format for displaying dates and times.
tillidformatvarchar(256)Pattern for generating till ids (uses letter & digit symbols as defined in IDGenerator)



glaccount

for organizations with accounting enabling, this represents the general ledger account
Column NameData TypeDescription
activatedbit(1)Whether the account is activated and may be used. Required
allowmanualjournalentriesbit(1)Whether the gl account accepts journal entries logged manually. The default value is true.
creationdatedatetimeThe date on which the GL account was created. UTC
currencycodevarchar(3)Foreign key to a the Currency table.
descriptionmediumtextDetailed (text) description of the gl account
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables, in this case, the generalsettings, glaccountingrule, gljournalentriessummary, gljournalentry and interbranchtransferrule tables.
glcodevarchar(32)Unique general ledger code for this account. Required
lastmodifieddatedatetimeThe date on which the GL was last modified. UTC
migrationeventkeyvarchar(32)Foreign key to a specific Migration Event. A gl account might be imported using the Data Import feature and all the data imported from a file will be a part of a specific migration event (when this event will be reverted, all the data associated with it will be removed from the system)
namevarchar(256)The name of the GL account. Required
striptrailingzerosbit(1)Whether the trailing zeros should be stripped or not when computing accounting reports for Header GL Accounts.
typevarchar(256)Type of GL Account. Must be one of:
- ASSET
- LIABILITY
- EQUITY
- INCOME
- EXPENSE
Required
usagevarchar(256)What the account is used for. Either a detailed account which is actually usable for deposits or as a header account which is just for display/organizational purposes. Must be one of:
- DETAIL
- HEADER
Required



glaccountingrule

holds the accounting definitions for the loan and savings product. a rule associates an accounting resource with a glaccount and also a transactionchannel with a glaccount.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry.
financialresourcevarchar(256)The financial resource associated with this rule.
glaccountkeyvarchar(32)The account that is mapped to the financialResource.
indexint(11)Used for ordering of the product rules when they make part from a list.
predefinedfeekeyvarchar(32)The key of the predefined fee that uses this rule. If this field is null, this rule is not used by a predefined fee.
productkeyvarchar(32)Product key associated with this product rule.
producttypevarchar(256)Product type (eg: loan or savings) that is being referred to by the product key:
- LOAN
- SAVINGS
transactionchannelkeyvarchar(32)The key of the transaction rule that uses this rule.



glaccountsclosure

an accounts closure is used to limit backdating of accounting operations. it simulates the accounting action of "book closing".
Column NameData TypeDescription
branchkeyvarchar(32)The branch for which the accounts were closed.
closuredatedatetimeThe date on which the closure was performed. UTC
createdbyuserkeyvarchar(32)The ID of the user who initiated this closure.
creationdatedatetimeThe date on which this closure was first performed. UTC
encodedkeyvarchar(32)The encoded key for this database entry.
lastmodifieddatedatetimeThe date on which this closure was last modified. Generally the notes can only be modified after a closure has been performed.
notesvarchar(256)Notes entered about the closure.
triggervarchar(256)Indicates whether the closure was MANUAL or AUTOMATIC.



gljournalentriessummary

keeps the last states of a bucket of gljournalentry unique identified by a branch, glaccount and a product.
Column NameData TypeDescription
balancedecimal(50,10)The balance of this journal entries summary
branchkeyvarchar(32)The key of the assigned branch for this journal entries summary
creationdatedatetimeUTC date for the creation time
encodedkeyvarchar(32)The encoded key for this database entry.
entrydatedatetimeThe date for which the summary was generated (as Organization Time)
glaccountkeyvarchar(32)The key of the assigned gl account for this journal entries summary
typevarchar(256)The type of the snapshot CREDIT or DEBIT



gljournalentry

a journal entry is an accounting’s version of a transaction. it records an event being written to the general ledger. every entry is a credit or a debit and as part of a set of a journalentries called a transaction. within such a transaction, the debits must match the credits to ensure the balance sheets balance.
Column NameData TypeDescription
accountkeyvarchar(32)The key to the account (loan or savings) associated with this journal action. May be null if it’s just a manual journal entry. Set only for On The Fly Closures
amountdecimal(50,10)Amount which was debited or credited. Required
assignedbranchkeyvarchar(32)Foreign key of the branch where this journal entry was logged for.
creationdatedatetimeDate stamp of when the transaction occured. For instance this may be when a loan repayment was actually made by the client and the entryDate would be when this was recorded in Mambu (the exact time) (UTC)Required
encodedkeyvarchar(32)The encoded key for this GL journal entry.
bookingDatedatetimeThe date and time when an entry is posted to an account on the account servicer's accounting books. As Organization Time. Required
entryidbigint(20)A unique auto-increment id for the gl journal entry. Required
glaccount_encodedkey_oidvarchar(32)Foreign key to the GLAccount which was debited or credit as part of this transaction. Required.
notesvarchar(256)Optional notes entered by the user when they logged the entry
productkeyvarchar(32)The Product associated with this journal entry.
producttypevarchar(256)The product/account type which the accountKey is referring to. Must be one of:
- LOAN
- SAVINGS
reversalentrykeyvarchar(32)Foreign key (to self) to the entry where the reversal was made. If it’s null the entry wasn’t reversed, else it contains the key of the reversal entry.
transactionidvarchar(32)An id for the transaction. Note that this ‘id’ is not unique for any given journal entry. Multiple journal entries may have the same transaction ID which is used for grouping them together. For instance a repayment results in multiple journal entries but will all have the same transaction ID.
typevarchar(256)The entry type of the Journal Entry, DEBIT or CREDIT.
userkeyvarchar(32)Foreign key to the User who performed the journal entry (or the transaction which lead to the automatic journal entry).



gljournalentryforeignamount

stores foreign currency amount for gl journal entries.
Column NameData TypeDescription
accountingratekeyvarchar(32)The encoded key of the exchange rate between this foreign currency and the organization base currency.
amountdecimal(50,10)The amount in the currency specified in the currencyCode field.
currencycodevarchar(3)the ISO currency code of this journal entry.
encodedkeyvarchar(32)The encoded key for this database entry.
gljournalentrykeyvarchar(32)The GL journal entry that this currency conversion relates to.



gljournalentryinterestaccruallog

entity directly linked to a gljournalentry used to keep track of some journal entries.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry.
interestaccruedaccountingmethodvarchar(255)Indicates whether CASH or ACCRUAL accounting was used.
lastexecutiondatedatetimeThe date on which interest was last accrued. UTC
producttypevarchar(255)The product type that this accrual relates to. Can be either SAVINGS or LOAN.
transactionidvarchar(255)The ID of the transaction.



gljournalentryreconciliation

A technical table which may be used when discrepancies are noticed in general ledger journal entries and need to be manually reconciled.
Column NameData TypeDescription
accountingratekeyvarchar(32)Encoded key of an entry in the accountingrate table containing details of the exchange rate if the journal entry is in a different currency to the base currency.
accountkeyvarchar(32)The key to the account (loan or savings) associated with this journal action. May be null if it’s just a manual journal entry. Set only for On The Fly Closures
amountdecimal(50,10)Amount which was debited or credited. Required
assignedbranchkeyvarchar(32)Foreign key of the branch where this journal entry was logged for.
creationdatedatetimeDate stamp of when the transaction occured. For instance this may be when a loan repayment was actually made by the client and the entryDate would be when this was recorded in Mambu (the exact time) (UTC)Required
encodedkeyvarchar(32)The encoded key for this GL journal entry.
entrydatedatetimeDate/time stamp when the entry was recorded. As Organization Time. Required
entryidbigint(20)A unique auto-increment id for the gl journal entry. Required
foreignamountdecimal(50,10)The amount if the currency is different to the base currency.
foreigncurrencycodevarchar(3)The currency code if the amount is in a currency other than the base currency.
glaccount_encodedkey_oidvarchar(32)Foreign key to the GLAccount which was debited or credit as part of this transaction. Required.
idbigint(20)
notesvarchar(256)Optional notes entered by the user when they logged the entry
originalencodedkeyvarchar(32)The unique key of the original journal entry.
processedflagint(1)Whether the current journal entry has been processed.
productkeyvarchar(32)The Product associated with this journal entry.
producttypevarchar(256)The product/account type which the accountKey is referring to. Must be one of:
- LOAN
- SAVINGS
reconciliationnotesvarchar(256)Any relevant notes added during the reconciliation process.
reversalentrykeyvarchar(32)Foreign key (to self) to the entry where the reversal was made. If it’s null the entry wasn’t reversed, else it contains the key of the reversal entry.
transactionidvarchar(32)An id for the transaction. Note that this ‘id’ is not unique for any given journal entry. Multiple journal entries may have the same transaction ID which is used for grouping them together. For instance a repayment results in multiple journal entries but will all have the same transaction ID.
typevarchar(256)The entry type of the Journal Entry, DEBIT or CREDIT.
userkeyvarchar(32)Foreign key to the User who performed the journal entry (or the transaction which lead to the automatic journal entry).



group

stores details about a group of client. a group itself contains a new and a few fields similar to those of clients. then, the relationships to the groups (between clients and groups) are described in groupmember and grouprole tables
Column NameData TypeDescription
assignedbranchkeyvarchar(32)Foreign key to the Branch table indicating which branch the client belongs to
assignedcentrekeyvarchar(32)Foreign key to the Centre table indicating to which centre the group belongs to.
assigneduserkeyvarchar(32)Foreign key to the Users table indicating who the the user assigned to the client is (ie: which credit officer is responsible for them)
clientrolekeyvarchar(32)The key of the role this group belongs to
creationdatedatetimeThe date on which this group was first created.
emailaddressvarchar(256)The email address of the group.
encodedkeyvarchar(32)The encoded key for this database entry. This ID can be used with our API to get details on or update a specific group.
groupnamevarchar(256)Name of the group. Required
homephonevarchar(256)The home phone number of the group.
idvarchar(32)Unique id for the group. Automatically generated by Mambu when groups are stored. Required
lastmodifieddatedatetimeThe date on which this data relating to this group was last modified.
loancycleint(11)For group which receive loans, this is the current cycle of the group loan
migrationeventkeyvarchar(32)Foreign key to a specific Migration Event. A group might be imported using the Data Import feature and all the data imported from a file will be a part of a specific migration event (when this event will be reverted, all the data associated with it will be removed from the system)
mobilephone1varchar(256)The mobile phone number of the group.
notesmediumtextHTML-formatted notes about the group
preferredlanguagevarchar(32)The language preference for this group. Must be one of ENGLISH, PORTUGUESE, RUSSIAN, SPANISH, FRENCH, CHINESE, GEORGIAN, INDONESIAN, ROMANIAN, BURMESE, GERMAN.



groupcustomvalue

Holds values for custom information for groups. Please note: This table is for an upcoming feature and may not contain any data for your organization.
Column NameData TypeDescription
definitionidsmediumtextHolds the list of the custom field encodedkeys generated from the JSON held in the values column.
linkedentitykeysmediumtextHolds the list of linkedentitykeys generated from the JSON held in the values column. These will be the entities to which this custom field is linked for custom fields of type CLIENT_LINK, GROUP_LINK or USER_LINK.
parentkeyvarchar(32)The encodedkey of the entity holding the custom values within the values JSON column. This will be the encodedkey of the entity with which these custom field values are associated.
valuesjsonHolds all the custom field data in a JSON structure, including keys, IDs, values and indexes.



groupmember

represents a join table between a group and it’s members. connects the clients & groups tables togethers
Column NameData TypeDescription
clientkeyvarchar(32)Foreign key to the Client which this relationship describes. Required
creationdatedatetimeThe date on which the client was first set as a member of this group.
encodedkeyvarchar(32)The encoded key for this database entry.
groupkeyvarchar(32)Foreign key to the Group which this relationship describes. Required
indexinlistint(11)Order of the client in the group clients list. That is, 0 is displayed before 1, etc.



grouprole

represents a relationship of a client in a group as to what their role is. for instance a client may be a president of a group.
Column NameData TypeDescription
clientkeyvarchar(32)Foreign key to the Client which this relationship describes. Required
encodedkeyvarchar(32)The encoded key for this database entry.
groupkeyvarchar(32)Foreign key to the Group which this relationship describes. Required
grouprolenamekeyvarchar(32)Foreign key to the GroupRoleName which this relationship describes. Required
indexinlistint(11)Indicates which position in the list of group roles this Role will occupy.



grouprolename

entity holding role names for a group. example: secretary, president, etc. referred to by the grouprole table.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry.
namevarchar(256)The name of the group role



guaranty

entity holding information about a client guaranty entry. it can be defined based on another client which guarantees (including or not a savings account whether it is a client of the organization using mambu or not) or based on a value the client holds (an asset/collateral)
Column NameData TypeDescription
amountdecimal(50,10)The amount used by the client for the guaranty
assetnamevarchar(256)The name of a value the client guarantees with (populated when the guaranty type is ASSET)
discriminatorvarchar(32)
encodedkeyvarchar(32)A unique key for this row.
funds_encodedkey_ownvarchar(32)The unique key of an entry in the loanaccount table for which this guaranty has been provided when this guaranty is used as a funding source for the account.
funds_integer_idxint(11)Index if there is more than one guaranty for the same loan account.
guarantees_encodedkey_ownvarchar(32)The unique key of an entry in the loanaccount table for which this guaranty has been provided.
guarantees_integer_idxint(11)Index if there is more than one guaranty for the same loan account.
guarantorkeyvarchar(32)The key of the client used as the guarantor (populated when the guaranty type is GUARANTOR)
guarantortypevarchar(255)The type of the guarantor (CLIENT or GROUP)
idvarchar(32)Investor fund identifier. All versions of an investor fund will have same id.
interestcommissiondecimal(50,20)The Interest commission that is used for funds.
investmentpercentagedecimal(50,20)The investment percentage to be considered for collection of amounts upon repayment for this investor fund
savingsaccountkeyvarchar(32)The key of the savings account used by the guarantor (populated when the guaranty type is GUARANTOR). It can be null
statevarchar(255)Starting with P2P fractionalization, this entity is also used to hold the history of an investment fund. Having
multiple versions, the state is one of the fields that can differentiate or give information regarding the
investment fund. Must be one of:
- PENDING - Default state for the investor fund.
- ACTIVE - State for the case when the loan amount has been funded.
- PARTIALLY_BOUGHT - Partially bought state for an investor fund for which a newer version exists due to buying amount
- PARTIALLY_SOLD - Partially sold state for an investor fund for which a newer version exists due to selling amount
- CLOSED - State denoting the fact that the fraction itself is closed (the loan is closed)
- REVERTED - State for the case when this specific Investor entry should be ignored as it was reverted.
- TOTALLY_SOLD - State for the case when the Investor sells the loan fraction entirely.
typevarchar(256)Guaranty type
- GUARANTOR - Value used when the guaranty is created using a client (guarantor)
- ASSET - Value used when the guaranty is created using an asset
validfromdatetimeThe date when the investor started funding the loan account (Organization time)
validuntildatetimeThe date when the investor stopped funding the loan account (Organization time)



guarantycustomvalue

Holds values for custom information for loan guarantees. Please note: This table is for an upcoming feature and may not contain any data for your organization.
Column NameData TypeDescription
definitionidsmediumtextHolds the list of the custom field encodedkeys generated from the JSON held in the values column.
linkedentitykeysmediumtextHolds the list of linkedentitykeys generated from the JSON held in the values column. These will be the entities to which this custom field is linked for custom fields of type CLIENT_LINK, GROUP_LINK or USER_LINK.
parentkeyvarchar(32)The encodedkey of the entity holding the custom values within the values JSON column. This will be the encodedkey of the entity with which these custom field values are associated.
valuesjsonHolds all the custom field data in a JSON structure, including keys, IDs, values and indexes.



holiday

stores details about user-defined holidays which affect things like repayment scheduling.
Column NameData TypeDescription
branchholidays_encodedkey_ownvarchar(32)If this holiday applies to one branch only this field will contain that branch’s encoded key.
branchholidays_integer_idxint(11)The number in the list of holidays applying only to a single branch. The first will be 0, the second 1 and so on. If a holiday is general and does not only apply to one specific branch this index will be -1.
creationdatedatetimeThe time at which this holiday was added to the system.
dayofmonthint(11)The day of the month on which this holiday falls.
encodedkeyvarchar(32)The encoded key for this holiday.
generalholidays_encodedkey_ownvarchar(32)If this holiday applies to all branches this is the encoded key of the general settings for the organization.
generalholidays_integer_idxint(11)The index in the list of general holidays. The first will have index 0, the second 1 and so on. If a holiday is not general and instead only applies to a specific branch, its index will be -1.
isannualyrecurringbit(1)Whether or not this is a yearly repeating holiday.
keyidbigint(20)An internal ID stored for portability reasons.
monthofyearint(11)The month on which this holiday falls as integer, ie. 1 is January, 7 is July.
namevarchar(256)The name of this holiday.
yearint(11)The year on which this holiday falls.



identificationdocument

stores identification documents associated with individual clients. a client may have an number of identification documents
Column NameData TypeDescription
clientkeyvarchar(32)Foreign key to Client to which this identification document belongs to. Required.
documentidvarchar(256)The document id such as the Passport # (“ABC123”)
documenttypevarchar(256)Type of document: eg: Passport
encodedkeyvarchar(32)The encoded key for this document, this is an autogenerated and globally unique ID.
identificationdocumenttemplatekeyvarchar(32)Points to the entry in the identificationdocumenttemplate table that corresponds to this ID; ie. if this document is a passport, it will point to the template for passports.
indexinlistint(11)Order of the ID document if the parent holder has multiple documents (for display/formatting purposes). That is, 0 is displayed before 1, etc.
issuingauthorityvarchar(256)Who issued this document (Such as “Government”)
validuntildatetimeExpiry date of the document (Organization Time)



identificationdocumenttemplate

represents a template for identification documents, which defines default document type, issuing authority and adds a constraint to the identification document id.
Column NameData TypeDescription
allowattachmentsbit(1)Whether this template allow files to be attached or not.
documentidtemplatevarchar(256)The document id template constraint, that contains letters (@) and digits (#) symbols used to validate the id and can also contain any other static character.
documenttypevarchar(256)Type of document: eg: Passport.
encodedkeyvarchar(32)The encoded key for this template, this is an autogenerated and globally unique ID.
issuingauthorityvarchar(256)Who issued this document (Such as “Government”).
mandatoryforclientsbit(1)Whether this template it’s mandatory for all the clients or not.



image

stores an image which can be used in mambu in various places such as displaying client profile photos. images are stored in multiple sizes depending on the display needs.
Column NameData TypeDescription
creationdatedatetimeUTC creation date
descriptionmediumtextDescription of the image contents
encodedkeyvarchar(32)The encoded key for this image, this is an autogenerated and globally unique ID.
largeimagemediumblob Image data up to 750px in the longest dimension. Required
largeimagelocationvarchar(256)The location of large images, which may be held in a service such as Amazon Web Services S3 or Google Cloud Platform equivalent if the file size is particularly large.
lastmodifieddatedatetimeLast modified date. As UTC
mediumimagemediumblob Image data up to 300px in the longest dimensionRequired
mediumimagelocationvarchar(256)The location of medium sized images, which may be held in a service such as Amazon Web Services S3 or Google Cloud Platform equivalent if the file size is particularly large.
smallthumbnailmediumblob Image data exactly 50px by 50px in sizeRequired
smallthumbnaillocationvarchar(256)The location of image thumbnails, which may be held in a service such as Amazon Web Services S3 or Google Cloud Platform equivalent if the file size is particularly large.
tinythumbnailmediumblob Image data exactly 32px by 32px in size. Required
tinythumbnaillocationvarchar(256)The location of tiny thumbnail images, which may be held in a service such as Amazon Web Services S3 or Google Cloud Platform equivalent if the file size is particularly large.
titlevarchar(256)Name of the image
typevarchar(256)Type of the image. May be one of: ‘png’, ‘jpeg’, ‘gif’, etc. Required



importedreport

more complex reports can be created via jaspersoft studio and imported into mambu. this table contains information on such reports.
Column NameData TypeDescription
accessiblebyallusersbit(1)Indicates whether all users can access this report.
createdbyuserkeyvarchar(32)Encoded key of the user who imported this report.
creationdatedatetimeDate on which this report was first imported.
dataitemtypevarchar(256)The type of Mambu entity that is reported on, for example LOAN_ACCOUNT, SAVINGS_TRANSACTION or CLIENT.
descriptionmediumtextA description of the report that has been provided as HTML.
encodedkeyvarchar(32)The encoded key of this imported report used as the primary key for this table.
indexint(11)
lastmodifieddatedatetimeThe date on which this report was last modified. As UTC.
namevarchar(256)The name of the report.
productkeyvarchar(32)If the report relates to a specific loan or deposit product the encoded key of that product will be linked here.
reportfilemediumblob The actual jrxml file containing this Jaspersoft Studio report.
rolekeysmediumblob The list of roles who have been granted permission to view this report.
typevarchar(256)The type of report, eg. JASPER for a report created with Jaspersoft Studio.



importedreportcode

Column NameData TypeDescription
codeexpressionsmediumtext
encodedkeyvarchar(32)A unique key.
importedreportkeyvarchar(32)
jasperlanguagesvarchar(256)
namevarchar(256)
sqlstatementsmediumtext



indexrate

index value used as a base interest rate in some organizations for the calculation of the loan interest rate as relative to this value. the amount they give it out for is fixed, but they or an external source such as the government, etc, may change the base interest rate and this means that the loans need to be updated so that the interest rate value should be changed as well.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this index rate, this is an autogenerated and globally unique ID.
indexinterestratesource_encodedkey_oidvarchar(32)The encodedkey of the entry in the indexratesource table which serves as teh soruce for this index rate.
notesmediumtextComments
ratedecimal(50,10)The value of the index interest rate
startdatedatetimeThe date from when this index should be used (Organization Time)
userkeyvarchar(32)The key of the user who added this index interest rate



indexratesource

the set of possible sources for an index interest rate that can come from (ex: one source may be libor) or for a tax rate.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this source, this is an autogenerated and globally unique ID and can be used with our API to get details on a specific Index Rate Source. This ID is also used as a foreign key in a number of other tables including the loanproduct, savingsaccount, interesrate and interestproductsettings tables.
namevarchar(256)Name of the rate source
notesmediumtextComments
typevarchar(256)The types of the Index rate source currently in use by both Loan and Savings products and accounts.
Values
- INTEREST_RATE - Percentage applied on top of the interest
- TAX_RATE - Rate percentage applied for calculating the taxes
- WITHHOLDING_TAX_RATE - Rate percentage applied for calculating the taxes for a Savings Product (the Withholding ones)



interbranchtransferrule

class for associating inter branch gl accounts. the relation between the branches is bidirectional, depending on the actual accounting action.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID.
glaccountkeyvarchar(32)The general ledger account
leftbranchkeyvarchar(32)Returns the encoded key of the left branch. A value of null means ‘Unassigned’.
rightbranchkeyvarchar(32)Returns the encoded key of the right branch. A value of null means ‘Unassigned’.



interestaccountsettings

class for all the entities grouping settings related to how is the interest accrued and applied
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this set of settings, this is an autogenerated and globally unique ID.
interestratedecimal(50,20)The rate based on which the interest is accrued and applied for accounts with fixed interest rate
interestspreaddecimal(50,20)The rate based on which the interest is accrued and applied for accounts with index interest rate



interestbasesettings

base class for all the entities grouping settings related to how is the interest accrued and applied
Column NameData TypeDescription
accrueinterestaftermaturitybit(1)If the product support this option, specify if the interest should be accrued after the account maturity date.
encodedkeyvarchar(32)The encoded key for this set of settings, this is an autogenerated and globally unique ID.
interestchargefrequencyvarchar(255)The interval used for determining how often is interest charged (e.g. x [weeks])
interestchargefrequencycountint(11)The count of units to apply over the interval (e.g. [x] weeks)
interestratereviewcountint(11)Interest rate review frequency unit count
interestratereviewunitvarchar(255)Interest rate review frequency measurement unit
interestratesourcevarchar(255)Interest calculation method: fixed or (interest spread + active organization index interest rate)
interestratetermsvarchar(256)The option for how is the interest rate determined when being accrued for an account. Required



interestproductsettings

base class for all the entities grouping settings related to how is the interest accrued and applied
Column NameData TypeDescription
allownegativeinterestratetinyint(1)Whether the interest rate for this product is allowed to go into negative.
compoundingfrequencyvarchar(32)How often interest compounds for this product.
defaultinterestratedecimal(50,20)Default interest rate(for fixed interest rate)/spread(for index interest rate) used by the product
encodedkeyvarchar(32)The encoded key for this set of settings, this is an autogenerated and globally unique ID.
indexsourcekeyvarchar(32)Index rate source key for the product
interestrateceilingvaluedecimal(50,20)Interest spread + index interest rate can’t be more than this amount (valid only for index interest rate products)
interestratefloorvaluedecimal(50,20)Interest spread + index interest rate can’t be less than this amount (valid only for index interest rate products)
maxinterestratedecimal(50,20)Maximum interest rate(for fixed interest rate)/spread(for index interest rate) used by the product
mininterestratedecimal(50,20)Minimum interest rate(for fixed interest rate)/spread(for index interest rate) used by the product



interestratechanges

table which stores interest rate changes on deposit accounts with fixed rate terms.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for this row.
endingdayint(11)The day on which this interest rate stopped applying.
interestaccountsettingskeyvarchar(32)The encodedkey of an entry in the interestaccountsettings table containing the settings for the deposit account whose interest rate changed.
interestratedecimal(50,20)The value of the interest rate.



interestratetier

useful for tiered interest rates, holds the values to define how is the interest computed for one of this steps.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this interest rate tier.
endingbalancedecimal(50,10)The top-limit value for the account balance in order to determine if this tier is used or not
endingdayint(11)The top-limit value for the account period since activation in order to determine if this tier is used or not
indexint(11)The index of the interest rate tier.
interestratedecimal(50,20)The rate used for computing the interest for an account which has the balance less than the ending balance
interestratetiers_encodedkey_ownvarchar(32)Foreign key to an entry in the interestbasesettings table which used this tier.
interestratetiers_integer_idxint(11)The index for this tier, ie. the first tier will be 0, the second 1 and so on.



lineofcredit

a maximum loan amount that can be approved or disbursed for a client or a group. it is a limit also for the period under which the clients/groups are allowed to make loans or overdrafts.
Column NameData TypeDescription
amountdecimal(50,10)The amount that client can be exposed to.
approveddatedatetimeThe date when the line of credit was approved if the LOC wasn’t approved yet. Stored as Organization Time. Nullable
clientkeyvarchar(32)The key of the client associated with the line of credit
closeddatedatetimeThe date when the line of credit was closed (Organization Time)
creationdatedatetimeCreation date UTC
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID.
expiredatedatetimeExpire date of this line of credit; after this date no other loans/overdrafts (Organization Time)
exposurelimittypevarchar(256)The calculation method for exposure limit:
- APPROVED_AMOUNT
- OUTSTANDING_AMOUNT
groupkeyvarchar(32)The key of the group associated with the line of credit
idvarchar(32)Auto generated unique ID based on pattern defined in GeneralSettings for the line of credit that can be used for identifying the Line Of Credit or for fetching lines of credit from API calls
lastmodifieddatedatetimeThe date when the line of credit was modified - UTC
notesmediumtextComments
startdatedatetimeThe line of credit start date(UTC) - it must not be null. Represents the starting date from which the line of credit becomes active
statevarchar(256)The state of the line of credit:
- PENDING_APPROVAL
- APPROVED
- ACTIVE
- CLOSED
substatevarchar(256)The sub state of the Line of Credit
- WITHDRAWN
- REJECTED



lineofcreditcustomvalue

Holds values for custom information for lines of credit, also referred to as credit arrangements. Please note: This table is for an upcoming feature and may not contain any data for your organization.
Column NameData TypeDescription
definitionidsmediumtextHolds the list of the custom field encodedkeys generated from the JSON held in the values column.
linkedentitykeysmediumtextHolds the list of linkedentitykeys generated from the JSON held in the values column. These will be the entities to which this custom field is linked for custom fields of type CLIENT_LINK, GROUP_LINK or USER_LINK.
parentkeyvarchar(32)The encodedkey of the entity holding the custom values within the values JSON column. This will be the encodedkey of the entity with which these custom field values are associated.
valuesjsonHolds all the custom field data in a JSON structure, including keys, IDs, values and indexes.



livemigrationprocess

An internal table to track the status of various jobs which will be carried out as part of the release process for new features, for example, when new fields are added to entities, new methods of interest calculation are added or a feature was enabled for your Mambu instance.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for this row.
enddatedatetimeThe time at which the process was completed in UTC.
iscleanupdonebit(1)Whether the cleanup process was completed successfully after the migration was completed.
numfailedentitiesbigint(20)The number of entities for which the migration process failed for any reason.
numsuccessfulentitiesbigint(20)The number of entities which were successfully processed as part of this migration.
startdatedatetimeThe date and time, in UTC, at which this process was started. Generally, this will be the rollout date and time of the version containing the feature mentioned in the type field.
typevarchar(128)A human readable name for this process.



loanaccount

stores a loan account or a loan application (which is just a loan account which is not yet active). a loan account always belongs to a client or group and must be of a certain loan product type. loan accounts store the detailed breakdown of the amounts due & paid. this is redundant and must match with the amounts for all the repayments for the accounts. like clients and groups, loan accounts are also assigned to users (credit officers) and branches.
Column NameData TypeDescription
accountarrearssettingskeyvarchar(32)Arrears settings available for the current account.
accountholderkeyvarchar(32)Foreign key reference to the Client or Group which is holding on this account. Required
accountholdertypevarchar(256)The type of account holder this group has. Must be one of the following:
- CLIENT
- GROUP
and direct to the key referred to by accountHolderKey
Required
accounts_integer_idxint(11)
accountstatevarchar(256)The current state of the loan account. Must be one of the following:
- PARTIAL_APPLICATION - account has not yet been approved and is pending more information (in draft form)
- PENDING_APPROVAL - account is ready to be approved by users with permission to do so
- APPROVED - account has been approved and is now ready to be disbursed to the client
- ACTIVE - account has been been disbursed and is now active and in good standing
- ACTIVE_IN_ARREARS - the account has been disbursed and is active but is in arrears (e.g.: has repayments which are late)
- CLOSED - the account is no longer active (see accountSubState field for sub-states)
- CLOSED_WRITTEN_OFF - Account has been closed and any remaining balance due has been written off
- CLOSED_REJECTED - Account has gone through the application process and has been rejected
Required
accountsubstatevarchar(256)This field holds a second state for the account. Must be one of the following:
- PARTIALLY_DISBURSED - Related to ACTIVE state; the account is only partially disbursed.
- REFINANCED - Related to CLOSED state; the account was closed and refinanced.
- RESCHEDULED - Related to CLOSED state; the account was closed and further rescheduled.
- WITHDRAWN - Related to CLOSED state; the account was closed before activation.
- REPAID - Related to the CLOSED state; the account was closed after the account was fully repaid.
- LOCKED - Related to ACTIVE or ACTIVE_IN_ARREARS states; the account is locked and may not be used further unless unlocked, in this state; no amount is automatically applied to the account, ie. no automatic interest is applied, fees, penalties or transfer transactions logged; interest is accrued; rate change transactions are logged.
- LOCKED_CAPPING - Related to ACTIVE_IN_ARREARS state; an account will be set to this substate when it is in arrears and the total owed for interest, fees and penalties has exceeded the threshold allowed by your organization’s risk settings. Please see our article on internal controls for more information on managing thresholds and what is and is not possible when an account is in this substate.
accruedinterestdecimal(50,10)How much interest has accrued to the account but is not yet posted
accruedpenaltydecimal(50,10)Specifies the amount of penalty that has been accrued in the account
accrueinterestaftermaturitybit(1)If the product support this option, specify if the interest should be accrued after the account maturity date
accruelateinterestbit(1)Indicates whether the option to continue to accrue interest after the repayment date for late payments. See our support article for more information on this subject.
activationtransactionkeyvarchar(32)The key of the transaction that activated this account
allowoffsetbit(1)Specify if the account is allowing offset links
applyinterestonprepaymentmethodvarchar(256)Apply interest on prepayment method copied from loan product on which this account is based.
approveddatedatetimeThe date when the loan account has been approved. Stored as Organization Time.
arrearstoleranceperiodint(11)The tolerance period, in days, before an account will be marked as being arrears.
assignedbranchkeyvarchar(32)Foreign key to the Branch that this account is assigned to
assignedcentrekeyvarchar(32)Foreign key to the Centre table indicating to which centre the loan account belongs to.
assigneduserkeyvarchar(32)Foreign key to the User (Credit Officer) who is assigned to his account
closeddatedatetimeDate when the account was closed or null if never closed (OrganizationTime)
creationdatedatetimeThe date when the account was created. Stored as UTC
defaultfirstrepaymentduedateoffsetdecimal(50,20)The amount of days which will be automatically added to the first repayment date. So if, for example, this number is 5 and repayments should be made on the 1st of the month, the first repayment will instead be scheduled for the 5th.
disbursementdetailskeyvarchar(32)The key of an entry in the disbursementdetails table containing information about the disbursement of this loan account.
elementsrecalculationmethodvarchar(256)the method by which individual elements will be recalculated
encodedkeyvarchar(32)The encoded key for this database entry. This ID can be used with our API to get details for or update a specific Loan Account. This field should not be changed as it is used as a foreign key to link with other tables.
feesbalancedecimal(50,10)How much fees are still due on the account (relevant for fixed accounts only)
feesduedecimal(50,10)The total fees due for this loan account. Required
feespaiddecimal(50,10)The total fees paid for this loan account. Required
fixeddaysofmonthmediumblob Specifies the days of the month when the repayment due dates should be. Only available if the Repayment methodology is FIXED_DAYS_OF_MONTH
futurepaymentsacceptancevarchar(256)Whether or not a customer can pay in advance. Will be one of ACCEPT_OVERPAYMENTS or NO_FUTURE_PAYMENTS.
graceperiodint(11)Grace period for the loan account in the number of installments. Ignored if grace period is none or null.
graceperiodtypevarchar(256)
Type of Grace period or null if no grace period. Must be one of:
- NONE
- PAY_INTEREST_ONLY - interest is charged and paid for the repayments (but capital repayment is 0)
- INTEREST_FORGIVENESS - interest is neither charged nor paid. a pure grace period
hascustomschedulebit(1)Flag used when the repayments schedule for the current account was determined by the user, by editing the due dates or the principal due
holdbalancedecimal(50,10)The amount currently being held for pending card transactions. See our support page for more information.
idvarchar(32)Unique ID of the loan account. Required
interestapplicationmethodvarchar(256)The method used by the loans defining how the interest gets applied
- ON_DISBURSEMENT - All the interest amount gets applied only once, on disbursement time. There is no way of applying interest manually, through the cron jobs, or when performing repayments
- ON_REPAYMENT - The interest gets applied on each repayment and there are multiple ways of performing this. There is an accrued interest, which gets accumulated every day, this determining how much interest needs to be applied on the repayments. The interest can be applied by the cron jobs in the due date of a repayment. The interest can also be applied manually: behind the scenes, when performing a repayment or explicitly by using the UI function available for this.
interestbalancedecimal(50,10)The total interest currently owed and outstanding for the client (total interest accrued for account - interest paid)
interestbalancecalculationmethodvarchar(32)Option which determines the way the balance for the account’s interest is computed.
interestcalculationmethodvarchar(256)The method used for calculating the interest on this loan account. Must be one of:
- FLAT
- DECLINING_BALANCE
- DECLINING_BALANCE_DISCOUNTED - also known as the french declining balance method
Required
interestchargefrequencyvarchar(256)Defines how the interest is charge on this loan account. For instance if the interest rate is 5% and the charge frequency is EVERY_DAY then 5% is charged every day to this account when determining the repayment schedule. Must be one of:
- ANNUALIZED - annual repayment schedule (1/yr)
- EVERY_MONTH - monthly interest (12/yr)
- EVERY_FOUR_WEEKS - interest calculated every 4 weeks (13/yrs)
- EVERY_DAY - interest calculate every day (365/yr)
Required
interestcommissiondecimal(50,20)The value of the interest booked by the organization from the accounts funded by investors. Null if the funds are not enabled.
interestduedecimal(50,10)How much interest it’s due for the account at this moment. Required
interestfromarrearsaccrueddecimal(50,10)The amount of interest from arrears that has been accrued in the account
interestfromarrearsbalancedecimal(50,10)the total interest from arrears which is currently owed and outstanding for this account, (total interest from arrears due - interest from arrears paid)
interestfromarrearsduedecimal(50,10)how much interest from arrears is due for the account at this moment
interestfromarrearspaiddecimal(50,10)total interest from arrears paid into the account
interestpaiddecimal(50,10)The total interest paid for this loan account. Required
interestratedecimal(50,20)The interest rate for the loan account. See the charge frequency for how it is used. Required
interestratereviewcountint(11)Indicates how often the index rate for this account should be reviewed in the units specified in the interestratereviewunit column.
interestratereviewunitvarchar(256)The unit (DAYS, WEEKS, MONTHS`) indicating how often the index rate should be checked.
interestratesourcevarchar(256)Whether the account uses a default FIXED_INTEREST_RATE or is linked to an INDEX_INTEREST_RATE
interestroundingversionvarchar(256)Holds the possible values for the version of the algorithm used for applying rounding on the loan accounts interest values:
- VERSION_1
- VERSION_2
- VERSION_3
interestspreaddecimal(50,10)Interest to be added to active organization index interest rate in order to find out actual interest rate
interesttypevarchar(255)The loan account interest type. Must be one of the following:
- SIMPLE_INTEREST - the interest is applied on the schedule when it is applied in the account
- CAPITALIZED_INTEREST - the interest is capitalized on the schedule. It will be converted into principal when applied
lastaccountappraisaldatedatetimeWhen/if the account had last been evaluated for interest, principal, fees and penalties calculations (UTC)
lastinterestapplieddatedatetimeLast date when interest was applied (posted) to the account (Organization Time)
lastinterestreviewdatedatetimeThe date on which the last review was carried out for accounts using an index interest rate.
lastlockeddatedatetimeDate when the account was set for the last time in the LOCKED sub-state. If null, the account is not locked anymore or it was never locked (OrganizationTime).
lastmodifieddatedatetimeThe date when the account was modified the last time. Stored as UTC.
lastsettoarrearsdatedatetimeDate when the account was last set to In Arrears standing or null ifnever set (Organization Time)
lasttaxratereviewdatedatetimeWhen/if the account had last tax rate checked (as Organization Time)
latepaymentsrecalculationmethodvarchar(256)Method used by loan accounts to have the schedule recalculated when late payments are posted on declining balance equal installments accounts: LAST_INSTALLMENT_INCREASE - this option will only recalculate the interest due for the next installment (the one where the extra interest caused by the late payment, will be added) and OVERDUE_INSTALLMENTS_INCREASE - this option will recalculate all the next installments, in order to keep the same total expected on the installments.
lineofcreditkeyvarchar(32)The key to the line of credit where this account is registered
loanamountdecimal(50,10)The original loan amount given out to the client. Required
loangroup_encodedkey_oidvarchar(32)The loan group this account belongs to (if part of a hybrid loan account or null otherwise).
loannamevarchar(256)Display name of the loan account. Often just the same as the product name. Required.
loanpenaltycalculationmethodvarchar(256)Specifies on what amount are the penalties calculated (Eg. OVERDUE_BALANCE, OVERDUE_BALANCE_AND_INTEREST)
lockedoperationsmediumblob A list with operations which are locked when the account is in LOCKED sub-state:
- APPLY_INTEREST
- APPLY_FEES
- APPLY_PENALTIES
migrationeventkeyvarchar(32)Foreign key to a specific Migration Event. A loan account might be imported using the Data Import feature and all the data imported from a file will be a part of a specific migration event (when this event will be reverted, all the data associated with it will be removed from the system)
notesmediumtextHTML notes and details about this loan account/application
paymentmethodvarchar(256)The method used by the loans defining how the payments get performed
- HORIZONTAL - The payment is done horizontally, on the repayments, following the repayment allocation elements order.
- VERTICAL - The payment is done vertically, into the account, following the repayment allocation elements order.
penaltybalancedecimal(50,10)How much fees are still due on the account (relevant for fixed accounts only)
penaltyduedecimal(50,10)The total penalty due for this loan account. Required
penaltypaiddecimal(50,10)The total penalty paid for this loan account. Required
penaltyratedecimal(50,20)Specifies the rate (in percent) which is charged as a penalty
periodicpaymentdecimal(50,10)The periodic payment amount for the accounts which have balloon payments
prepaymentacceptancevarchar(256)Whether the pre-payments are allowed or not for this account
Must be one of the following:
- ACCEPT_PREPAYMENTS - The pre-payments can be posted
- NO_PREPAYMENTS - No pre-payments are accepted (no repayments posting before due)
prepaymentrecalculationmethodvarchar(255)repayment recalculation method copied from the loan product on which this account is based. Holds the possible options for how are the pre-payments affecting the number of installments and the amount allocated per installment for a repayments schedule belonging to a DYNAMIC loan account
- NO_RECALCULATION
- RESCHEDULE_REMAINING_REPAYMENTS
- RECALCULATE_SCHEDULE_KEEP_SAME_NUMBER_OF_TERMS
- RECALCULATE_SCHEDULE_KEEP_SAME_PRINCIPAL_AMOUNT
- RECALCULATE_SCHEDULE_KEEP_SAME_TOTAL_REPAYMENT_AMOUNT
- REDUCE_AMOUNT_PER_INSTALLMENT
- REDUCE_NUMBER_OF_INSTALLMENTS
principalbalancedecimal(50,10)The total principal currently owed and outstanding for the client for this account (principal disbursed - principal paid)
principalduedecimal(50,10)How much principal is currently due for this account. Required
principalpaiddecimal(50,10)Total principal paid into the account
principalpaidinstallmentstatusvarchar(255)Defines the installment status after the principal was paid off as part of an over-payment.
- PARTIALLY_PAID
- PAID
- ORIGINAL_TOTAL_EXPECTED_PAID
principalpaymentsettingskeyvarchar(32)Points to an entry in the principalPaymentSettings table containing further settings for this account.
principalrepaymentintervalint(11)Once at how many repayments has the principal to be paid.
producttypekeyvarchar(32)Foreign Key to the LoanProduct with which this account was created. Required
redrawbalancedecimal(50,10)The total redraw amount available to the client
repaymentinstallmentsint(11)How many installments are required to pay back the loan. Must be same number as number of repayments when loan is initially disbursed. Required
repaymentperiodcountint(11)How often the loan is to be repaid. for instance “1” with the unit being “Days” means every day. Determines the repayment schedule. Required
repaymentperiodunitvarchar(256)Unit in which the repaymentPeriodCount is being represented. Must be one of:
- DAYS - repaid on a daily basis
- WEEKS - repaid every x weeks
- MONTHS - repaid every x months
- YEARS - repaid every x years
Required
repaymentschedulemethodvarchar(256)The method used by the loans to compute the repayment schedule
- FIXED - The repayment schedule is fixed and doesn’t change all over the loan account’s lifecycle. More detailed, the principal and interest due which get computed for each repayment doesn’t ever change, even if a repayment is pre-paid or paid later. Penalties and fees can be applied only on repayments. Still, reduction operations can be performed over the principal, interest, fees and penalties amounts (only with special settings enabled)
- DYNAMIC - The repayment schedule is dynamic and will change over the loan account’s lifecycle e.g. when entering repayments, on holidays changes or interest rates changes. For example, if paying an amount grater that what is due at a given time will cause the interest balance to be recomputed and will be lower that the amount initially computed. Penalties and fees can be applied only straight into the account
rescheduledaccountkeyvarchar(32)Foreign key to another LoanAccount if this account has been closed with state CLOSED_RESCHEDULED. Or null if not rescheduled
scheduleduedatesmethodvarchar(256)The methodology used by this product to compute the due dates of the repayments
- INTERVAL - the repayments will be made on a specified interval (e.g. Every 2 Months)
- FIXED_DAYS_OF_MONTH - The repayments will be made each month on some given dates (for example, each month on 10th and 20th means that each month there will be two installments, one made on 10 of that month and one on 20)
shortmonthhandlingmethodvarchar(256)Determines how to handle the short months, if they select a fixed day of month > 28. Will be null if no such date is selected and also for the Interval methodology. Only available if the Repayment Methodology is FIXED_DAYS_OF_MONTH
taxratedecimal(50,10)The current tax rate of the account



loanaccount_billingcycledays

Holds the day on which the billing cycle rolls over for revolving credit type loans.
Column NameData TypeDescription
accountkeyvarchar(32)The encoded key of the revolving credit account.
daytinyint(2)The day on which the billing cycle rolls over each month.



loanaccount_repaymentdays

Holds days on which repayments will become due for loan accounts.
Column NameData TypeDescription
accountkeyvarchar(32)The encoded key of a loan account.
daytinyint(2)The day of the month on which loan repayments become due.



loanaccountchangedevent

Column NameData TypeDescription
encodedkeyvarchar(32)The key for this database entry



loanaccountcustomvalue

Holds values for custom information for loan accounts. Please note: This table is for an upcoming feature and may not contain any data for your organization.
Column NameData TypeDescription
definitionidsmediumtextHolds the list of the custom field encodedkeys generated from the JSON held in the values column.
linkedentitykeysmediumtextHolds the list of linkedentitykeys generated from the JSON held in the values column. These will be the entities to which this custom field is linked for custom fields of type CLIENT_LINK, GROUP_LINK or USER_LINK.
parentkeyvarchar(32)The encodedkey of the entity holding the custom values within the values JSON column. This will be the encodedkey of the entity with which these custom field values are associated.
valuesjsonHolds all the custom field data in a JSON structure, including keys, IDs, values and indexes.



loanaccountnumericid

Column NameData TypeDescription
numericiddecimal(32,0)The numeric ID of a given loan account.



loanaccountredrawsettings

this table relates an upcoming feature and will be documented closer to release.
Column NameData TypeDescription
accountkeyvarchar(32)Unique key of the entry in the loanaccount table for which these redraw settings are valid.
encodedkeyvarchar(32)A unique key for this row.
restrictnextduewithdrawaltinyint(1)Indicates whether withdrawing amounts that reduce the next due instalment repayment is restricted or not.



loangroup

for the hybrid group product type, this is the model which captures the relationship between a group any any number of loan accounts. a loan group is simply a logical grouping of loan accounts. in the hybrid model, all loan accounts must have the same properties, repayment schedules, etc except that they may have different amounts.
Column NameData TypeDescription
creationdatedatetimeThe date on which this loan group was created. UTC
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID.
group_encodedkey_oidvarchar(32)Foreign key of the Group that this loan group belongs to Required.
lastmodifieddatedatetimeThe date on which this loan group was last modified. UTC
namevarchar(256)Name of the loan group. Required
notesmediumtextHTML Notes about the loan group.



loanproduct

stores templates that specify some predefined information and constraints that are then applied to the loan accounts, associated with the loan products. each loan account has to use one product. products can be defined for individuals, pure groups or hybrid groups, can specify a default amount, interest rate, number of installments and also can constrain these fields to some minimum and maximum values. it also defines the interest details and the repayments frequency.
Column NameData TypeDescription
accountingmethodvarchar(256)The current accounting state for this product
- NONE - accounting is deactivated
- CASH - uses cash accounting
- ACCRUAL - uses accrual accounting
accountinitialstatevarchar(32)Specifies the initial states for the accounts that will be created using this product.
Available states:
- PENDING_APPROVAL
- PARTIAL_APPLICATION
- etc.
accountlinkingenabledbit(1)Whether this product can be linked to others
accruelateinterestbit(1)Indicates whether this product continues to accrue intersest on late payments, see here for more details.
activatedbit(1)Whether this loan product is activated or not (can be used or not).
allowarbitraryfeesbit(1)Only if true users will be able to apply fees, for current object, of type ‘Other’; these fees can have any amount
allowcustomrepaymentallocationbit(1)Indicates whether a customer will be allowed to freely allocate repayments or parts of repayments towards specific costs. See our support article for more information.
amortizationmethodvarchar(32)Method used by loan accounts for repayments schedule generation. It indicates whether the user wants to define a periodic payment, and round the remaining principal into the last installment, or they want to allocate the whole principal on the schedule
- STANDARD_PAYMENTS - This option will allow spreading the whole principal amount over the repayments schedule
- BALLOON_PAYMENTS - This option will allow the user defining a periodic payment amount, which will be used on all the installments, except the last one where the remaining principal amount will be posted
applyinterestonprepaymentmethodvarchar(256)Whether the interest on prepayment is applied manual or automatic.
arrearssettingskeyvarchar(32)Loan product arrears settings
autocreatelinkedaccountsbit(1)Whether account links should be automatically created
autolinkaccountsbit(1)Whether accounts should be automatically linked if possible on creation
cappingapplyaccruedchargesbeforelockingbit(1)Specifies if the accrued charges should be applied before locking (capping)
cappingconstrainttypevarchar(255)Specifies constraint types for capping charges. Must be one of the following:
- SOFT_CAP - Interest, fees, penalty are applied. Account is locked in Locked(Capping) state
- HARD_CAP - Interest, fees, penalty are not applied. Account is locked in Locked(Capping) state.
cappingmethodvarchar(255)Specifies how principal will be used when calculating capping charges. Must be one of the following:
- OUTSTANDING_PRINCIPAL_PERCENTAGE - As percentage from the outstanding principal
- ORIGINAL_PRINCIPAL_PERCENTAGE - As percentage from the original principal
cappingpercentagedecimal(50,20)Specifies the percentage of principal that cannot be exceeded by the sum of interest, fees and penalty balances
categoryvarchar(256)The category of this loan product. This helps organise products into business areas. Can be PERSONAL_LENDING, PURCHASE_FINANCING, RETAIL_MORTGAGES, SME_LENDING, COMMERCIAL, UNCATEGORIZED.
creationdatedatetimeThe date when the loan product was created. Stored as UTC
currencycodevarchar(3)The currency which will be compatible with this product. Only relevant for customers offering multicurrency.
daysinyearvarchar(256)Days in a year methodology used for loan interest calculations for this product
- ACTUAL_365_FIXED
- ACTUAL_364
- ACTUAL_360
- E30_360
defaultfirstrepaymentduedateoffsetdecimal(50,20)How many days the first repayment due date should be extended (all other due dates from the schedule are relative to first repayment due date - they will also be affected by the offset)
defaultgraceperiodint(11)The default grace period that will be defined for the loan accounts that will use this product (if the grace period type is not NONE).
defaultloanamountdecimal(50,10)A default amount for the product (most of the loan accounts are using this default amount).
defaultnuminstallmentsint(11)The default number of the repayments.
defaultpenaltyratedecimal(50,20)Rate (in percent) which is set as default for new accounts.
defaultprincipalrepaymentintervalint(11)Frequency at which repayments should be paid on this loan product
defaultrepaymentperiodcountint(11)The repayments frequency. Example: the repayment is due once at 10 days (repayment period units). Required
dormancyperioddaysint(11)Specifies the number of days for an account to be fully paid in order to auto close it
elementsrecalculationmethodvarchar(256)Determines how the schedule elements are recalculated:
- FIXED_PRINCIPAL_EXPECTED - the principal expected is kept the same as before, when a prepayment is posted
- FIXED_TOTAL_EXPECTED - The total expected (principal + interest) is kept the same, when a prepayment is posted, interest is recalculated based on the new principal balance and principal is adjusted to match the PMT
encodedkeyvarchar(32)The encoded key for this loan product. This ID can be used with our ID to work with this specific Loan Product.
fixeddaysofmonthmediumblob Specifies the days of the month when the repayment due dates should be. Only available if the Repayment methodology is FIXED_DAYS_OF_MONTH
forallbranchesbit(1)Field to indicate if this product is available for all branches
forhybridgroupsbit(1)Field to indicate if this product is available for hybrid groups, true if available, false otherwise, never null
forindividualsbit(1)Field to indicate if this product is available for individuals, true if available, false otherwise, never null
forpuregroupsbit(1)Field to indicate if this product is available for pure groups, true if available, false otherwise, never null
futurepaymentsacceptancevarchar(256)Whether future payments are accepted or not by the accounts created for a given loan product
- ACCEPT_FUTURE_PAYMENTS - Future payments can be posted
- NO_FUTURE_PAYMENTS - Whether the future payments are accepted
graceperiodtypevarchar(256)The type of grace period which is possible for a loan account:
- NONE - no grace period
- PAY_INTEREST_ONLY - A grace period during which interest is charged and paid (i.e. an interest only loan for a period, still generates a full loan repayment schedule but during the grace period the capital repayment is $0)
- INTEREST_FORGIVENESS - interest is neither charged nor paid: a pure grace period
idvarchar(32)Unique ID of the loan product (specified by the user). Required
idgeneratortypevarchar(256)The type of the ids that will be generated:
- RANDOM_PATTERN - uses a given pattern to generate IDs
- INCREMENTAL_NUMBER - increments a given number to generate IDs
idpatternvarchar(256)The pattern, containing ‘@’ for letters and ‘#’ for digits, for the RANDOM_PATTERN or the starting number for the INCREMENTAL_NUMBER.
interestaccrualcalculationvarchar(256)The accounting interest calculation option selected for the product. One of BREAKDOWN_PER_ACCOUNT, AGGREGATED_AMOUNT, NONE.
interestaccruedaccountingmethodvarchar(32)Method being used for maintaining the interest accrued method for the loan product (NONE, DAILY, MONTHLY).
interestapplicationmethodvarchar(256)The method used by the loans defining how the interest gets applied
- ON_DISBURSEMENT - All the interest amount gets applied only once, on disbursement time. There is no way of applying interest manually, through the cron jobs, or when performing repayments
- ON_REPAYMENT - The interest gets applied on each repayment and there are multiple ways of performing this. There is an accrued interest, which gets accumulated every day, this determining how much interest needs to be applied on the repayments. The interest can be applied by the cron jobs in the due date of a repayment. The interest can also be applied manually: behind the scenes, when performing a repayment or explicitly by using the UI function available for this.
interestbalancecalculationmethodvarchar(32)Option which determines the way the balance for the account’s interest is computed.
interestcalculationmethodvarchar(256)The method that is used to compute the interest:
- FLAT - interest is calculated only on the original balance and remains unchanged through out the loan
- DECLINING_BALANCE - interest is paid on the remaining balance
- DECLINING_BALANCE_DISCOUNTED - interest is paid on the remaining balance and the repayments schedule is balanced to create equal repayments
interestratesettingskeyvarchar(32)Points to a row in the interestproductsettings table containing interest rate settings for this product.
interesttypevarchar(255)Specifies interest type for the loan product. Must be one of the following:
- SIMPLE_INTEREST - the interest is applied on the schedule when it is applied in the account
- CAPITALIZED_INTEREST - the interest is capitalized on the schedule. It will be converted into principal when applied
lastmodifieddatedatetimeThe date when the loan product was modified last time. Stored as UTC.
latepaymentsrecalculationmethodvarchar(256)Method used by loan accounts to have the schedule recalculated when late payments are posted on declining balance equal installments accounts: - INCREASE_LAST_INSTALLMENT - this option will only recalculate the interest due for the next installment (the one where the extra interest caused by the late payment, will be added) - INCREASE_OVERDUE_INSTALLMENTS - this option will recalculate all the next installments, in order to keep the same total expected on the installments.
lineofcreditrequirementvarchar(255)Specifies whether accounts created after this product can/should be part of a line of credit
Possible values:
- OPTIONAL - account can be part of a line of credit
- REQUIRED - account should be part of a line of credit
- NOT_REQUIRED - account should not be part of a line of credit
linkablesavingsproductkeyvarchar(32)Which savings product this account is linked to
loanpenaltycalculationmethodvarchar(256)Method used for calculating the loan penalty on this product.
- NONE
- OVERDUE_BALANCE
- OVERDUE_BALANCE_AND_INTEREST
loanpenaltygraceperiodint(11)Number of days to wait before applying the loan penalty amounts
loanproducttypevarchar(255)Specifies the type of the loan product
lockperioddaysint(11)Specifies the number of days for in which the account will be locked if it stays in arrears
maxfirstrepaymentduedateoffsetdecimal(50,20)Maximum number of days the first repayment due date should be extended (all other due dates from the schedule are relative to first repayment due date - they will also be affected by the offset)
maxgraceperiodint(11)The maximum grace period that has to be defined for the loan accounts that will use this product (if the grace period type is not NONE).
maxloanamountdecimal(50,10)The maximum loan amount for the loan account, to be able to use this product.
maxnumberofdisbursementtranchesint(11)Maximum number of disbursement tranches a loan account account made after this product can have
maxnuminstallmentsint(11)The maximum number of repayments for the loan account, to be able to use this product
maxpenaltyratedecimal(50,20)Maximum penalty rate which can be set for accounts.
minfirstrepaymentduedateoffsetdecimal(50,20)Minimum number of days the first repayment due date should be extended (all other due dates from the schedule are relative to first repayment due date
mingraceperiodint(11)The minimum grace period that has to be defined for the loan accounts that will use this product (if the grace period type is not NONE).
minloanamountdecimal(50,10)The minimum loan amount for the loan account, to be able to use this product.
minnuminstallmentsint(11)The minimum number of repayments for the loan account, to be able to use this product.
minpenaltyratedecimal(50,20)Minimum penalty rate which can be set for accounts.
offsetpercentagedecimal(50,20)Stores the percentage to be used as offset for the loan account schedule calculation.
paymentmethodvarchar(256)The method used by the loans defining how the payments get performed
- HORIZONTAL - The payment is done horizontally, on the repayments, following the repayment allocation elements order.
- VERTICAL - The payment is done vertically, into the account, following the repayment allocation elements order.
prepaymentacceptancevarchar(256)Whether the pre-payments are allowed or not for this product (if there can be posted repayments before the due date of that repayment)
prepaymentrecalculationmethodvarchar(255)Holds the possible options for how are the pre-payments affecting the number of installments and the amount allocated per installment for a repayments schedule belonging to a DYNAMIC loan account
- NO_RECALCULATION
- RESCHEDULE_REMAINING_REPAYMENTS
- RECALCULATE_SCHEDULE_KEEP_SAME_NUMBER_OF_TERMS
- RECALCULATE_SCHEDULE_KEEP_SAME_PRINCIPAL_AMOUNT
- RECALCULATE_SCHEDULE_KEEP_SAME_TOTAL_REPAYMENT_AMOUNT
- REDUCE_AMOUNT_PER_INSTALLMENT
- REDUCE_NUMBER_OF_INSTALLMENTS
principalpaidinstallmentstatusvarchar(255)Defines the installment status after the principal was paid off as part of an over-payment.
- PARTIALLY_PAID
- PAID
- ORIGINAL_TOTAL_EXPECTED_PAID
principalpaymentsettingskeyvarchar(32)Foreign key to the principalPaymentSettings table containing options for the current product
productdescriptionmediumtextA short description of the product (why is it recommended, who can use it etc.)
productnamevarchar(256)The name of the product.
productsecuritysettingskeyvarchar(32)Foreign key to the productSecuritySettings table containing the security settings (guarantors, collateral, investor funds) available for the current product.
redrawsettingskeyvarchar(32)Foreign key to the productRedrawSettings table
repaymentallocationordermediumblob An array list of the order of which to allocate repayments including principal, interest, fees and penalty
repaymentcurrencyroundingvarchar(256)Specifies if the repayment schedule should be rounded to the nearest whole unit
- NO_ROUNDING
- ROUND_TO_NEAREST_WHOLE_UNIT
repaymentelementsroundingmethodvarchar(256)Determines how the repayment currency rounding is handled on each element from the schedule:
- NO_ROUNDING
- ROUND_ALL
- PAYMENT_DUE
repaymentperiodunitvarchar(256)The frequency of loan repayment:
- DAYS
- WEEKS
- MONTHS
- YEARS
repaymentreschedulingmethodvarchar(256)The repayment rescheduling method used in calculations
repaymentscheduleeditoptionsmediumblob Which rights do users have when editing the schedule of this product (relevant for fixed products only)
repaymentschedulemethodvarchar(256)The repayment schedule method. Represents the method that determines whether the schedule will be fixed all over the loan account’s life cycle or will be dynamically recomputed when required.
roundingrepaymentschedulemethodvarchar(256)Specifies if the repayment schedule should be rounded to the nearest whole unit
- NO_ROUNDING
- ROUND_TO_NEAREST_WHOLE_UNIT
scheduleduedatesmethodvarchar(256)Method used by the loan accounts to determine the due dates of the repayments:
- INTERVAL - the repayments will be made on a specified interval (e.g. Every 2 Months)
- FIXED_DAYS_OF_MONTH - the repayments will be made each month on some given dates (for example, each month on 10th and 20th means that each month there will be two installments, one made on 10 of that month and one on 20)
scheduleinterestdayscountmethodvarchar(256)Methods that determine how the number of interest days for a repayment are computed (currently only used by FIXED methods)
- USING_REPAYMENT_PERIODICITY - The number of days in the repayment are ignored .Instead, the number of days is computed by considering that there’s no inconsistency between the first repayment length and the repayment frequency
- USING_ACTUAL_DAYS_COUNT - the actual number of days between the first repayment due date and the disbursement date are considered when computing the interest
settlementoptionsvarchar(32)Specifies how the nightly job will transfer from the due amounts from the linked savings account to the loan account
Available options:
- FULL_DUE_AMOUNTS
- PARTIAL_DUE_AMOUNTS
shortmonthhandlingmethodvarchar(256)Determines how to handle the short months, if they select a fixed day of month > 28. Will be null if no such date is selected and also for the Interval methodology. Only available if the Repayment Methodology is FIXED_DAYS_OF_MONTH
taxcalculationmethodvarchar(256)The method used by the loans to compute the taxes for the revenues:
- INCLUSIVE - The tax amount is included in the original computed amount.
- EXCLUSIVE - The tax amount is not included on the computed amount.
taxesonfeesenabledbit(1)Whether taxes on fees are enabled for this product or not
taxesoninterestenabledbit(1)Whether taxes on interest are enabled for this product or not
taxesonpenaltyenabledbit(1)Whether taxes on penalties are enabled for this product or not.
taxsourcekeyvarchar(32)The tax source from where the loan account taxes will be updated



loanproduct_billingcycledays

Holds information on the billing cycle for revolving credit type products.
Column NameData TypeDescription
daytinyint(2)The day on which the billing cycle rolls over.
productkeyvarchar(32)The encoded key of the product for which this setting is valid.



loanproductbranch

stores the association between a loan product and the branches where it is available.
Column NameData TypeDescription
branchkeyvarchar(32)The key of the branch that is associated with a loan product
encodedkeyvarchar(32)The encoded key for this association, this is an autogenerated and globally unique ID.
productkeyvarchar(32)The loan product that is associated with a branch



loanproductstartids

the id settings allows you to customize the ids generated for any loan accounts which will later be created under this product.

you can either choose a random pattern or incremental numbers for your account ids.
Column NameData TypeDescription
idpatternvarchar(256)The template for IDs for this loan product.
numericiddecimal(32,0)If IDs are numeric, newly created accounts for this product started at this mumber.
productencodedkeyvarchar(32)The encoded key of the product which uses this configuration.



loanrisklevel

capture the a loan account risk level band. a band is considered a range of days in arrears for an account (eg: from 5 to 10 days) and a risk level should also have a defined provisioning percentage. this percentage represents the amount that should be provisioned at this loan risk level"
Column NameData TypeDescription
arrearsfromint(11)The starting day for this band.
arrearstoint(11)The ending day for this band.
encodedkeyvarchar(32)They encoded key for this row.
namevarchar(256)The name of this rule.
provisioningpercentdecimal(50,10)The percentage of capital which should be provisioned for loans in this risk level.



loantranche

in some cases organizations may approve loans but not disburse the full amount initially. they would like to spread the disbursement (and risk) over time.

likewise for the client, they may not need the full loan amount up front. they may want to have a loan to buy some equipment for their business but will make one purchase today and another purchase in a few months. in these cases, they don’t need the full amount and wouldn’t want to pay interest on cash they don’t need yet.
a solution for this matter is the usage of disbursement in tranches. this class holds the information required for one of this tranche.
Column NameData TypeDescription
amountdecimal(50,10)The amount this tranche has available for disburse. Required
disbursementtransactionkeyvarchar(32)A link to the entry in the loantransaction table pointing to the transaction disbursing this tranche.
encodedkeyvarchar(32)A unique ID for this loan tranche.
expecteddisbursementdatedatetimeThe date when this tranche is supposed to be disbursed (as Organization Time)
indexint(11)The index which gives the order of tranches
tranches_encodedkey_ownvarchar(32)The encoded key of the loan account for this tranche.
tranches_integer_idxint(11)The index which gives the order of tranches



loantransaction

keeps track of all transactions which occur with loan accounts such as state changes, repayments, fees, etc.
Column NameData TypeDescription
advancepositiondecimal(50,10)Captures the advance (prepaid) amount
amountdecimal(50,10)The amount of the transaction. Compare the previous balance to the current balance to see if the amount increases or decreases the balance. The amount may be null for certain transactions - such as state changes.
arrearspositiondecimal(50,10)Captures the arrears position amount for the account in arrears
balancedecimal(50,10)The balance of the loan account after the transaction
branchkeyvarchar(32)Foreign key to the branch where this transaction was performed.
centrekeyvarchar(32)Foreign key to the centre where this transaction was performed.
commentvarchar(256)The comment which could be provided for a loan transaction.
creationdatedatetimeDate when the transaction occurred (UTC). Required
deferredinterestamountdecimal(50,10)How much interest pre-paid was added/removed in account, within this transaction (including taxes)
deferredtaxoninterestamountdecimal(50,10)How much taxes on the interest that was pre-paid were added/removed in account, within this transaction. If there is any deferred tax on interest amount set in this transaction, that amount should be included in this field.
details_encodedkey_oidvarchar(32)Details about transaction.
encodedkeyvarchar(32)The encoded key for this transaction.
entrydatedatetimeDate of the entry (eg date of repayment or disbursal, etc.). As Organization Time.
expectedprincipalredrawdecimal(50,10)Captures the difference between principal balance and redraw balance after each transaction performed on the loan account
externalidvarchar(36)The ID added by the customers that accepts alpha-numeric characters, underscore and dash. Can be null
feesamountdecimal(50,10)How much fees was added/removed in account, within this transaction.
fundersinterestamountdecimal(50,20)Amount of interest that goes to the funders (only for P2P accounts with split methodology)
indexinterestrate_encodedkey_oidvarchar(32)Foreign key to indexRate table: Index value used for the calculation of the loan interest rate.
interestamountdecimal(50,10)How much interest was added/removed in account, within this transaction.
interestfromarrearsamountdecimal(50,10)How much interest from arrears was applied/paid in account, within this transaction (including taxes).
interestratedecimal(50,20)The new interest rate for a loan account
loantransactiontermskeyvarchar(32)Foreign key to loansTransactionTerms table: Reference to entity which holds specific information related to loan transactions.
migrationeventkeyvarchar(32)Points to an entry in the datamigrationevent table if this transaction was imported rather than having been generated in teh Mmabu system itself.
organizationcommissionamountdecimal(50,20)Amount of interest that goes to the organization (only for P2P accounts with split methodology)
originalamountdecimal(50,10)the amount that was posted in a foreign currency. This amount was converted using the exchange rate available at entry date and set into the amount field
originalcurrencycodevarchar(3)the currency in which this transaction was posted. The amounts are stored in the base currency, but the user could have enter it in a foreign currency
parentaccountkeyvarchar(32)Foreign key to the loan account this transaction refers to. Required
parentloantransactionkeyvarchar(32)The key of the parent loan transaction. Right now, we link DEFERRED_INTEREST_APPLIED with REPAYMENT transactions and DEFERRED_INTEREST_PAID with INTEREST_APPLIED transactions, because this transaction comes as a result of logging the parent transaction. Usually, when the parent is reversed, the child should be reversed as well.
penaltyamountdecimal(50,10)How much penalty was added/removed in account, within this transaction.
principalamountdecimal(50,10)How much principal was added/removed in account, within this transaction.
principalbalancedecimal(50,10)The total principal owed by the client, at the current time (principal disbursed - principal paid)
producttypekeyvarchar(32)Store the key of the loan product to which the account owning this transaction belongs.
redrawbalancedecimal(50,10)The total redraw amount available to the client, at the current time
reversaltransactionkeyvarchar(32)Foreign key to another loan transaction (to self - LoanTransaction.encodedKey) where the reversal of the current transaction was made. It’s null if the transaction wasn’t reversed. Example: This transaction represents a penalty applied transaction. If this transaction will be reversed, another transaction will be logged and this transaction will remember the key of that one.
taxonfeesamountdecimal(50,10)How much taxes on the fees that were paid in this transaction were added/removed in account, within this transaction
taxoninterestamountdecimal(50,10)How much taxes on the interest that was paid in this transaction were added/removed in account, within this transaction
taxoninterestfromarrearsamountdecimal(50,10)The amount of taxes on the interest from arrears that were applied/paid in account, within this transaction.
taxonpenaltyamountdecimal(50,10)how much taxes on the penalties that were paid in this transaction were added/removed in account, within this transaction
taxrate_encodedkey_oidvarchar(32)Foreign key to indexRate table: Tax rate that was set or changed in this transaction.
tillkeyvarchar(32)The till key associated with this transaction
transactionidbigint(20)Auto-increment unique ID of the loan transaction. Required
typevarchar(256)Type of the transaction. Must be one of:
- CREATION - the loan account was created
- EDIT - the loan account was modified
- DISBURSEMENT - the loan account was disbursed
- STATE_CHANGE - the loan account state changed (eg for an Approval)
- REPAYMENT - a repayment was applied to the account
- FEE - a fee was applied to the account
- PENALTY_APPLIED - a penalty was applied to the account
- PAYMENT_RESCHEDULE - a repayment balance was rescheduled
- REPAYMENT_ADJUSTMENT - a previously entered repayment amount was corrected
- FEE_ADJUSTMENT - a previously applied fee was adjusted
- PENALTY_ADJUSTMENT - a penalty amount was adjusted
- BRANCH_CHANGED - marks the moment when the parent account is assigned to a different branch
- DEFERRED_INTEREST_APPLIED - before interest that’s not applied in the account yet gets pre-paid, this transaction will be logged in the same time with the repayment transaction, to justify the pre-paid interest amount in account’s balance
- DEFERRED_INTEREST_APPLIED_ADJUSTMENT - reversal for DEFERRED_INTEREST_APPLIED transaction
- DEFERRED_INTEREST_PAID - after interest that was pre-paid gets applied in the account, this transaction will be logged with the interest that was pre-paid and now applied, to justify why that applied interest is not reflected in account’s balance.
- DEFERRED_INTEREST_PAID_ADJUSTMENT - reversal for DEFERRED_INTEREST_PAID transaction
- DISBURSEMENT_ADJUSTMENT - reversal of disbursement
- FEE_CHARGED - a fee being charged to the loan account;
- FEE_LOCKED - when fees on the account are set to locked
- FEE_REDUCTION_ADJUSTMENT - reversal for the FEES_DUE_REDUCED transaction
- FEE_UNLOCKED - when the fees on the account are set to unlocked
- FEES_DUE_REDUCED - a decrease over the fees due
- IMPORT - an account being imported
- INTEREST_APPLIED - transaction logged when the accrued interest it’s applied to the account interest balance
- INTEREST_APPLIED_ADJUSTMENT - reversal for the INTEREST_APPLIED transaction
- INTEREST_DUE_REDUCED - a decrease over the interest due
- INTEREST_LOCKED - when the interest on account is set to locked
- INTEREST_RATE_CHANGED - the interest for an account has been recomputed
- INTEREST_REDUCTION_ADJUSTMENT - reversal for the INTEREST_DUE_REDUCED transaction
- INTEREST_UNLOCKED - when the interest on account is set to unlocked
- PENALTIES_DUE_REDUCED - a decrease over the penalties due
- PENALTY_LOCKED - when the penalties on the account are set to locked
- PENALTY_REDUCTION_ADJUSTMENT - reversal for the - PENALTIES_DUE_REDUCED transaction
- PENALTY_UNLOCKED - when the penalties on the account
- TAX_RATE_CHANGED - the tax for an account has been changed
- TERMS_CHANGED - marks the moment when some loan terms have changed for a loan account
- TRANSFER - when principal gets transferred from a loan to another loan
- TRANSFER_ADJUSTMENT - a transfer being adjusted (reversed)
- WRITE_OFF - loan account being closed off
- WRITE_OFF_ADJUSTMENT - the loan account closure being reversed
Required
userkeyvarchar(32)Foreign key to the User who performed this transaction. If null, it means this was a system-performed transactions (such as an automatic penalty)



loantransactioncustomvalue

Holds values for custom information for loan transactions. Please note: This table is for an upcoming feature and may not contain any data for your organization.
Column NameData TypeDescription
definitionidsmediumtextHolds the list of the custom field encodedkeys generated from the JSON held in the values column.
linkedentitykeysmediumtextHolds the list of linkedentitykeys generated from the JSON held in the values column. These will be the entities to which this custom field is linked for custom fields of type CLIENT_LINK, GROUP_LINK or USER_LINK.
parentkeyvarchar(32)The encodedkey of the entity holding the custom values within the values JSON column. This will be the encodedkey of the entity with which these custom field values are associated.
valuesjsonHolds all the custom field data in a JSON structure, including keys, IDs, values and indexes.



loantransactionexternalhistory

Table which tracks loan transactions that were made asynchronously by microservices.
Column NameData TypeDescription
creationdatedatetime(6)Date and time, in UTC, when the transaction occurred.
errorsvarchar(512)Will contain a log of the errors if the transaction has a status of FAILED.
externalrequestidvarchar(32)A unique ID generated by the service
loantransactionencodedkeyvarchar(32)The encoded key of the loan transaction.
statusvarchar(16)Status of the current transaction. Will be one of FAILED or SUCCEEDED



loantransactionpenaltydata

Stores information about penalties as a JSON representation.
Column NameData TypeDescription
contentjsonA JSON representation of a loan penalty.
encodedkeyvarchar(32)A unique key for this row.
parenttransactionkeyvarchar(32)The encoded key of the parent loan transaction.



loantransactionpenaltydetails

stores penalty settings at the loan transaction level.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for an entry in the loantransaction table.
penaltyratedecimal(50,20)The penalty rate in effect at the time of this transaction.



loantransactionterms

entity class which holds specific informations related to loan transaction terms which may change over the lifetime of a loan, e.g. principal payment value (amount or percentage) for credit card accounts or revolving credit or balloon payment type loans.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this row, this is an autogenerated and globally unique ID. This will be linked to from an entry in the loanaccount table.
periodicpaymentdecimal(50,10)Tracks changes in loans with associated payment plans.
principalpaymentamountdecimal(50,10)The principal payment flat amount logged when change it for a revolving credit loan
principalpaymentpercentagedecimal(50,20)The principal payment percentage value logged when change it for a revolving credit loan



mambuapp

represents an app in mambu - an external application added and provided by a 3rd party developer. check our user guide pages for more information on creating and installing apps.
Column NameData TypeDescription
apiuser_encodedkey_oidvarchar(32)The encoded key of a user in the user table which this app uses to authenticate with Mambu.
appkeyvarchar(32)The app key used to sign requests between the app and Mambu.
creationdatedatetimeThe date when the app was created. Stored as UTC
encodedkeyvarchar(32)A unique key for this app.
extensionpointsmediumblob Extension points indicate where the app will appear in the Mambu user interface. See this page for a list of extension points.
idvarchar(256)The unique id of the application. This is used as the App ID for authenticating API calls.
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
namevarchar(256)The display name of the app.
propertiesmediumblob The properties of the app.
statevarchar(256)Whether the app is ENABLED or DISABLED.



mambufeatureentity

contains information about mambu features.
Column NameData TypeDescription
creationdatedatetime(6)The date when the mambu feature was created (stored as UTC). Required.
encodedkeyvarchar(32)The encoded key for this feature, this is an autogenerated and globally unique ID.
lastmodifieddatedatetime(6)The date when the mambu feature was last modified (stored as UTC).
namevarchar(256)The name of the mambu feature. Required.
statusvarchar(32)The status of the mambu feature.
usagevarchar(32)Indicates if mambu feature is used.



mambuservices

services which are enabled/disabled for different editions/tiers of mambu and different subscription plans and clients
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for this row.
mambueditionvarchar(256)The product teir of this mambu instance.
maxclientsint(11)The maximum number of clients that can be supported by this instance.
maxusersint(11)The maximum number of users for this instance.
trialexpirydatedatetimeThe date on which the free trail will expire



mccexpiration

contains information on how much an authorizationhold can be maintained in the system before expiring it, based on the mcc (merchant category code).
Column NameData TypeDescription
daystoexpirationint(11)The number of days to wait before expiring an authorization hold with this entity’s MCC
descriptionvarchar(256)The description of the MCC expiration. Unique.
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID.
mccint(11)The Merchant Category Code to hold the expiration information for



messagetemplate

a template for a notification which can be sent by mambu. also used by task templating functionality.
Column NameData TypeDescription
activatedbit(1)If the messate template is activated or not
authorizationvarchar(255)Specifies authorization type (basic or no authorization).
contenttypevarchar(255)The header to be used when posting the webhook notification.
creationdatedatetimeThe creation date of the message template. Stored as UTC.
customfilter_keyvarchar(32)A reference to the custom filter based on which the notification message is created of not
encodedkeyvarchar(32)The encoded key for this template. Can be used with our API to generate populated templates programatically, see here for more information.
eventvarchar(256)Event associated with this notification. Required
lastmodifieddatedatetimeThe date on which this row was last modified. Stored as UTC.
namevarchar(255)Name of this message template. Required
optionvarchar(256)Subscription option for this notification - whether clients opt in or opt out of it.
passwordvarchar(255)Password to be used in authentication token when web hook notification is posted.
recipientkeyvarchar(32)The entity that is going to receive the message. May be a client, group, credit officer or a linked custom field.
requesttypevarchar(32)The HTTP method to be used when sending the webhook notification.
subjectvarchar(256)The subject of the message template.
targettypevarchar(32)Indicates the type of entity which will trigger this notification, eg. CLIENT, GROUP, SAVINGS, LOANS
templatemediumtextThe content of the template.
topicvarchar(256)The topic to which a client can subscribe in order to receive the events
triggervarchar(255)The trigger of the message template.
triggerdaysint(11)Number of days before/after the trigger when the notification will be sent.
typevarchar(256)The type of notification, EMAIL, SMS or TASK.
urlvarchar(512)The URL of the message template.
usernamevarchar(255)User name to be used in authentication token when web hook notification is posted.



messagetemplaterecipient

model that maintains the relation between a message template and the recipient type that is going to receive the message.
Column NameData TypeDescription
customfieldkeyvarchar(32)Which user link related to the target (client/group) should receive notifications of events.
encodedkeyvarchar(32)The encoded key for this relationship, this is an autogenerated and globally unique ID.
grouprolenamekeyvarchar(32)Which group role (user defined, as president/secretary/etc.) related to the target (client/group) should receive notifications of events.
recipienttypevarchar(256)Describe the type of the recipient that is going to receive the message. May be a client, group, credit officer etc.



messagetemplaterequestheader

A table holding custom header keys and values for automated webhook notifications.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for this row.
headerkeyvarchar(256)The key of the custome header, for example, Accept or X-Mambu-Custom-Header.
headervaluevarchar(1024)The value this header will take.
messagetemplatekeyvarchar(32)The key of an entry in the messagetemplate table to which this custom header will be added when sent.



messaginglog

Column NameData TypeDescription
creationdatedatetime(3)The time at which this message was logged.
encodedkeyvarchar(32)The encoded key for this database entry.
messageidvarchar(36)The ID for this message.



nonworkingday

represents a day from the week which is non-working for the organization
Column NameData TypeDescription
creationdatedatetimeThe date when the non working day was created (as UTC)
dayofweekvarchar(256)The day of the week which is non-working for the organization, eg MONDAY, SATURDAY.
encodedkeyvarchar(32)The encoded key for this entry in this table.
nonworkingdays_encodedkey_ownvarchar(32)Encoded key of the entry in the generalsettings table which uses this set of non-working days. Essentially your mambu instance.
nonworkingdays_integer_idxint(11)Index for this non working day, ie. the first non working day will have index 0, the second 1 and so on.



notificationbrokerqueue

Column NameData TypeDescription
creationdatedatetimeThe date when the row was created. Stored as UTC
encodedkeyvarchar(32)
failurecausevarchar(2048)
failurereasonvarchar(256)
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
notificationmessagekeyvarchar(32)
statevarchar(256)



notificationeventdetails

Column NameData TypeDescription
creationdatedatetime(3)The date when the event was created. Stored as UTC
encodedkeyvarchar(32)
isreadtinyint(1)Whether or not this message has been marked as read.
lastmodifieddatetime(3)
messagemediumtext
subscriberidvarchar(32)
taskidvarchar(32)
typevarchar(32)



notificationeventitem

this entity holds the information necessary to display notification events for clients.
Column NameData TypeDescription
creationdatedatetime(3)The date when the notification event was created (stored as UTC). Required.
encodedkeyvarchar(32)The encoded key for this notification, this is an autogenerated and globally unique ID.
isreadbit(1)Flag to determine whether the notification was read by the user. Required.
lastmodifieddatetime(3)The date when the notification event was last modified (stored as UTC). Required.
statusvarchar(32)The state of the Event.
subscriberidvarchar(32)The userKey of the user that owns the job. Required.
taskidvarchar(32)The taskKey of the job. Required.
typevarchar(32)The type of the Event.



notificationmessage

a log of sent notification messages of different types, including the state of the notification, to whom it was sent.
Column NameData TypeDescription
bodymediumtextThe actual contents (body) of the message.
clientkeyvarchar(32)Whom the message was sent to.
creationdatedatetimeWhen the message was created either for immediate sending of queued (as UTC).
destinationvarchar(1024)The destination (phone number or email address) this notification was sent to.
encodedkeyvarchar(32)The encoded key for this database entry. This ID can be used with our API to receive details on a specific message.
eventvarchar(256)The event this message was sent for, if any.
failurecausevarchar(256)A failure code if the message failed to send.
failurereasonvarchar(255)Maintains the reason of the notification message failure.
groupkeyvarchar(32)What group the message was sent to.
idvarchar(256)The id of the notification message.
loanaccountkeyvarchar(32)If the notification was about a loan account this is set to that account.
numretriesint(11)Number of retries to send the message
repaymentkeyvarchar(32)If the notification was about a repayment -then this is set to that account.
savingsaccountkeyvarchar(32)If the notification was about a savings account - then this is set to that account.
senddatedatetimeWhen the message was actually sent (as UTC).
senderkeyvarchar(32)Who sent the message. Or null if done automatically by Mambu.
statevarchar(256)The state of the message. Required.
subjectvarchar(256)The subject of the message.
templatekeyvarchar(32)Key of the associated MessageTemplate.
typevarchar(256)What type of notification is. Required.
userkeyvarchar(32)What user the message was sent to.



notificationmessagegljournalentry

Column NameData TypeDescription
encodedkeyvarchar(32)
gljournalentrykeyvarchar(32)
notificationmessagekeyvarchar(32)



notificationmessagehistory

Column NameData TypeDescription
bodymediumtext
creationdatedatetimeThe date when the entry was created. Stored as UTC
destinationvarchar(1024)
encodedkeyvarchar(32)
eventvarchar(256)
gljournalentrykeyvarchar(32)
idvarchar(256)
numretriesint(11)
senddatedatetime
templatekeyvarchar(32)
typevarchar(256)



notificationmessagequeue

this entity holds a notification message that is queued in the fact that it is ready to be sent by the jobs responsible for actual message sending (email, sms, web hook services). the corresponding table is scanned periodically by jobs that will send out the notifications.
Column NameData TypeDescription
creationdatedatetimeThe date when the message was created. Stored as UTC
encodedkeyvarchar(32)The encoded key for this message in the queue.
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
notificationmessage_encodedkey_oidvarchar(32)
statevarchar(256)The state of the message.



notificationmessagestreaming

Column NameData TypeDescription
encodedkeyvarchar(32)
idbigint(20)
messageidvarchar(36)
notificationmessage_encodedkey_oidvarchar(32)
partitionkeyvarchar(256)



notificationmessagestreamingqueue

Column NameData TypeDescription
creationdatedatetimeThe date when the notification was created. Stored as UTC
encodedkeyvarchar(32)
idbigint(20)
lastmodifieddatedatetimeThe last time at which this notification was modified.
notificationmessage_encodedkey_oidvarchar(32)
statevarchar(256)



notificationrequest

used for request for notifications. requests may be tied to clients or group and are associated with a specific notification template.
Column NameData TypeDescription
encodedkeyvarchar(32)the id for this request
ownerkeyvarchar(32)
ownertypevarchar(256)
template_encodedkey_oidvarchar(32)



notificationsdrdata

Column NameData TypeDescription
encodedkeyvarchar(32)
notificationmessagekeyvarchar(32)



objectlabel

captures the settings of custom object labels, for example ‘clients’ being referred to as ‘members’.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry.
languagevarchar(256)The language in which the object label is displayed. Required.
pluralvaluevarchar(256)The plural form of the type which is displayed. Required.
singularvaluevarchar(256)The singular form of the type which is displayed. Required.
typevarchar(256)The type of the object label which can be user-customised:
- CLIENT
- GROUP
- BRANCH
- CENTRE
- CREDIT_OFFICER
Required.



organization

stores details about the organization itself such as it’s name, address, time zone, etc.
Column NameData TypeDescription
creationdatedatetimeThe date when the organization was created (as Organization Time).
emailaddressvarchar(256)The email address of the organization
encodedkeyvarchar(32)The encoded key for this database entry.
lastmodifieddatedatetimeThe date of the last modify performed over the organization (as Organization Time).
namevarchar(256)The name of the organization
phonenovarchar(256)The phone number of the organization
timezoneidvarchar(256)Canonical time zone ID of the organization. See http://en.wikipedia.org/wiki/List_of_tz_database_time_zones for valid formats. Required.



organizationbranding

an organization can store it’s own logo (stored as 300x50 px) and also a small icon, stored as (16x16 px), that will replace the mambu logos from the website.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry.
iconimagemediumblob The image that will replace the Mambu logos from the website
logoimagemediumblob The logo of the organization. Stores a logo of 300x50.



organizationsnapshot

holds a snapshot of all organization indicators at a given point in time. used in the historical analysis graphs
Column NameData TypeDescription
creationdatedatetimeThe date on which this snapshot was taken.
encodedkeyvarchar(32)The encoded key for this database entry.
indicatorsmediumblob A hashmap of key performance indicators and their values at the time of the snapshot.
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.



passwordresetrequest

stores the password reset requests.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry.
identifiervarchar(255)Identifier for the password request - used instead of the encodedKey.
requestdatedatetimeThe date on which this password reset request was made.
statevarchar(256)Contains the state of the request. Before the change has been made, the state will be PENDING. Once the user has successfully changed their password this field should contain COMPLETED. If the account owner did not change their password within the allotted timeframe, the state will be EXPIRED.
targetclientkeyvarchar(32)If the request relates to a client and their password for the portal, this field will contain the encoded key for that client.
targetuserkeyvarchar(32)If the request relates to a user of the system then this field will contain the encoded key of that user.
userkeyvarchar(32)The encoded key of the user who intitated the password reset request.



paymentdetails

Holds details of payments made via the Mambu payments gateway including SEPA Direct Debit and Credit Transfers
Column NameData TypeDescription
creditoraccountcurrencyvarchar(3)The currency code of the creditor account
creditoraccountibanvarchar(34)The IBAN of the account sending funds as part of the transaction.
creditoraccountotheridentificationvarchar(34)Any other ID provided to identify the sender.
creditoraccountotherschemevarchar(35)The type of ID if creditoraccountotheridentification has been provided.
creditoragentbicvarchar(35)The bank identifier code of the agent used by the sender.
creditornamevarchar(140)The name of the holder of the account sending funds in this transaction.
debtoraccountcurrencyvarchar(3)The currency of the receiving account.
debtoraccountibanvarchar(34)The IBAN of the receiver of the transcation.
debtoraccountotheridentificationvarchar(34)Any other ID provided to identify the receiver.
debtoraccountotherschemevarchar(35)The type of ID if debtoraccountotherscheme has been provided.
debtoragentbicvarchar(35)The bank identifier code of the agent used by the receiver.
debtornamevarchar(140)The name of the holder of the account receiving the transaction.
encodedkeyvarchar(32)A unique key used to identify this payment.
endtoendidentificationvarchar(35)Identifier assigned by the initiating party to the transaction.
instructionidentificationvarchar(35)Identifier of a payment instruction.
remittanceinformationstructuredreferencevarchar(35)The reference information of the creditor’s underlying documents.
remittanceinformationstructuredreferenceissuervarchar(35)The entity that assigns the reference type.
remittanceinformationstructuredreferencetypevarchar(35)The type of creditor reference.
remittanceinformationunstructuredvarchar(140)Information supplied to match the items of the payment in an unstructured form.
savingstransactionkeyvarchar(32)The matching transaction in a Mambu deposit account.
servicelevelcodevarchar(35)The code for a pre-agreed service or level of service between the parties.
transactionidentificationvarchar(35)Identifier unique for a period assigned by the first initiating party to the transaction.



periodicpayment

entity defining a line from the payment plan, holding the pmt value used to compute the principal and interest for a specific defined number of installments when the schedule is generated. See our payment plans support article for more information.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry.
endinginstallmentpositionint(11)The last installment up to which this band of the payment plan will be used.
indexint(11)The index of the payment amount in the plan, for example, the payment amount for payments 1-5 will be index 0, for payments 6-10 will be index 1 and so on.
paymentplan_encodedkey_ownvarchar(32)The encoded key of the loan account to which this payment plan is linked.
paymentplan_integer_idxint(11)The index of the payment amount in the plan, for example, the payment amount for payments 1-5 will be index 0, for payments 6-10 will be index 1 and so on.
pmtdecimal(50,10)The actual payment amount used for installments in this band up to the endinginstallmentposition.



periodintervalsettings

holds settings for defining period intervals.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry.
frequencyvarchar(256)Frequency settings of the fee amortization. Required.
intervalcountint(11)Total number of intervals
intervaltypevarchar(256)Defines the options for an interval. Can be:
- FULL_TERM - The number of intervals is determined programmatically considering a loan account’s maturity date
- PREDEFINED_INTERVALS - The number of intervals is provided by the user
periodcountint(11)Period count used in conjunction with periodUnit to determine the next date of the interval
periodunitvarchar(256)Amortization unit to determine the interval between amortizations



permission

A table containing available permissions for users of the Mambu system.
Column NameData TypeDescription
creationdatedatetime(6)The date on which this permission was added, in UTC.
encodedkeyvarchar(32)A unique key for this permission
lastmodifieddatedatetime(6)The last date and time at which this permission was modified, in UTC.
permissionvarchar(256)The name of the permission, for example, CREATE_SAVINGS_ACCOUNT or APPROVE_LOANS



permissions

stores permissions associated with a user of mambu.
Column NameData TypeDescription
canmanageallbranchesbit(1)Whether or not this user is allowed to manage clients and services from all branches.
canmanageentitiesassignedtootherofficersbit(1)Whether or not the user can edit clients, accounts and other entities which are assigned to other Mambu users. Will be 1 for Administrators.
encodedkeyvarchar(32)The encoded key for this set of permissions. Used as a foreign key in the user table to link a user to a set of permissions.
permissionsmediumblob A list of permissions granted to the user via our UI such as VIEW_COMMENTS, EDIT_BRANCH and so on as a JAVA array.
permissionvaluestextA list of permissions granted to the user via our UI such as VIEW_COMMENTS, EDIT_BRANCH and so on as text.



permissionsassociation

Maps permissions to permission sets that include them.
Column NameData TypeDescription
permissionencodedkeyvarchar(32)The encoded key of a permission.
permissionsencodedkeyvarchar(32)The encoded key of a permission set in the permissions table that includes the permmission indicated by permissionencodedkey.



portalgeneralsettings

general settings for the portal module including the portal state, what to show for clients and organization stylesheet.
Column NameData TypeDescription
cnameurlvarchar(256)The custom domain configured for the client portal.
encodedkeyvarchar(32)Encoded key for these settings.
iconimagemediumblob The image which will be used as the favicon for the portal.
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
logoimagemediumblob The logo which should appear on the login screen.
maxclientaccountsint(11)The maximum number of client accounts supported by the portal.
portalenabledbit(1)Whether the portal is enabled or not. Please note: even if enabled there may be further steps to complete before the portal is usable, including setting up password recovery email templates and communication flows. See this page for more details.
shownaccountstatesmediumblob An array of the activities which will be displayed on the portal (for example PENDING_APPROVAL, ACTIVE etc.).
shownactivitiesmediumblob An array of the activities which will be displayed on the portal (for example LOAN_ACCOUNT_CREATED, CLIENT_EMAIL_SENT etc.).
stylesheetmediumblob Map containing link to general CSS template, custom colour scheme and background which have been configured for the portal.



portalpreferences

a user’s individual portal setting preferences including language, password (encrypted) and whether the portal is actually enabled.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for this row.
lastloggedindatedatetimeThe date on which this user last logged in to the portal.
passwordvarchar(256)The encrypted password of the user.
portalstatevarchar(256)Whether the portal is ENABLED or DISABLED.



predefinedfee

fee with a defined name and a fixed value
Column NameData TypeDescription
activebit(1)If the fee is active or not
amortizationintervalsettingskeyvarchar(32)Interest Rate Settings holds information about interest rate applied to the product
amortizationprofilevarchar(256)The type of amortization profile used for fee
- NONE
- SUM_OF_YEARS_DIGITS
- STRAIGHT_LINE
- EFFECTIVE_INTEREST_RATE
amountdecimal(50,10)The amount of the fee
amountcalculationmethodvarchar(256)The amount from which the fee is calculated using percentage amount:
- FLAT - a fix value independent from account to which it is applied (used both for loans and savings)
- LOAN_AMOUNT_PERCENTAGE - a percentage from the loan amount of the loan account to which it is applied (used only for loans)
- REPAYMENT_PRINCIPAL_AMOUNT_PERCENTAGE-a percentage from the repayment amount of the loan account to which it is applied(used only for loans)
applydatemethodvarchar(256)When should a fee be applied; to be used with monthly deposit fees:
- MONTHLY_FROM_ACTIVATION
- FIRST_OF_EVERY_MONTH
creationdatedatetimeThe date when the fee was created (as UTC).
encodedkeyvarchar(32)The encoded key for this predefined fee.
feeamortizationuponrescheduleoptionvarchar(256)Indicates if fee amortization should be continued or finished at account reschedule/refinance. Will be one of END_AMORTIZATION_ON_THE_ORIGINAL_ACCOUNT or CONTINUE_AMORTIZATION_ON_THE_RESCHEDULED_REFINANCED_ACCOUNT depending on the option which has been selected.
feeapplicationvarchar(256)The type of fee application when disbursement is applied:
- REQUIRED - fee will be automatically applied into account at disbursement
- OPTIONAL - fee can be applied into account at disbursement. User decide this.
Required.
idvarchar(256)The ID given to this fee.
lastmodifieddatedatetime(3)The date on which this row was last modified. As UTC.
loanfees_encodedkey_ownvarchar(32)If this fee is for a loan account, this field will contain the encoded key of the product to which it applies.
loanfees_integer_idxint(11)Shows the index for this fee if there are more than one fee defined for a given loan product.
namevarchar(256)The name of the fee
nontaxablefeebit(1)Indicates whether the fee is exempt from tax.
percentageamountdecimal(50,20)The amount of the fee in percents applied to percent source
savingsfees_encodedkey_ownvarchar(32)If this fee is for a savings/deposit account, this field will contain the encoded key of the product to which it applies.
savingsfees_integer_idxint(11)Shows the index for this fee if there are more than one fee defined for a given savings/deposit product.
taxratesourcekeyvarchar(32)If tax must be applied to this fee, this field will include the encoded key of the tax.
triggervarchar(256)The event that will trigger a fee:
- MANUAL - Not automated, initiated by an user action
- DISBURSEMENT - Applied at loan disbursement. Disbursement fees are subtracted from loan amount at disbursement
- CAPITALIZED_DISBURSEMENT - Applied at loan disbursement. Capitalized fees are not subtracted from loan amount at disbursement
- LATE_REPAYMENT - Applied once for a repayment when it’s due date expired and that repayment was not paid off
- MONTHLY_FEE - Applied every month per account depending on the apply date method
- PAYMENT_DUE - Applied every time a repayment becomes due
- ARBITRARY - Used for the displaying logic of the transactions with arbitrary fees



predefinedfeeamount

an amount of predefined fee that was applied or paid on an account:

- when a fee is applied, the transaction will have a single predefinedfeeamount entry created, that will point to the applied predefined fee

- when a fee is paid/reduced, the transaction might have multiple predefinedfeeamount entries created, one for each fee that was paid/reduced
Column NameData TypeDescription
amountdecimal(50,10)The amount of the fee that was applied/paid in the transaction for the given predefined fee
encodedkeyvarchar(32)The encoded key for this predefined fee amount.
fee_encodedkey_oidvarchar(32)The predefined fee for which the amount was applied/paid. Foreign key to the predefinedKey table.
loanpredefinedfeeamounts_encodedkey_ownvarchar(32)If the fee was applied to a loan account this field will contain the encoded key of the related transaction.
loanpredefinedfeeamounts_integer_idxint(11)If this is one of a number of applied applied for the same transaction then this field will show the index of this particular fee amount, ie. the first amount will have index 0, the second will have index 1 and so on.
savingspredefinedfeeamounts_encodedkey_ownvarchar(32)If the fee was applied to a savings/deposit account this field will contain the encoded key of the related transaction.
savingspredefinedfeeamounts_integer_idxint(11)If this is one of a number of applied applied for the same savings/deposit account transaction then this field will show the index of this particular fee amount, ie. the first fee will have index 0, the second will have index 1 and so on.
taxamountdecimal(50,10)The amount of the taxes on fee that was applied/paid in the transaction.
transactionidbigint(20)Used for capturing the transaction Id of the transaction originally generating this predefined fee amount.



principalpaymentaccountsettings

entity holding the required information for the principal payment process of an account
Column NameData TypeDescription
amountdecimal(50,10)Fixed amount for being used for the repayments principal due
encodedkeyvarchar(32)The encoded key for this set of settings, this is an autogenerated and globally unique ID.
percentagedecimal(50,20)Percentage of principal amount used for the repayments principal due



principalpaymentbasesettings

base class for all the entities grouping settings related to the principal payment process
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID and is used as foreign key to link this row to the principalpaymentaccountsettings and principalpaymentproductsettings tables.
includefeesinflooramountbit(1)If true, the fee will be included along with the principal in the repayment floor amount, for a revolving credit account.
includeinterestinflooramountbit(1)If true, the interest will be included along with the principal in the repayment floor amount, for a revolving credit account
principalceilingvaluedecimal(50,10)The maximum principal due amount a repayment made with this settings can have
principalfloorvaluedecimal(50,10)The minimum principal due amount a repayment made with this settings can have
principalpaymentmethodvarchar(255)The method of principal payment for revolving credit



principalpaymentproductsettings

entity holding the required information for the principal payment settings stored on a product
Column NameData TypeDescription
defaultamountdecimal(50,10)The default principal payment amount for the accounts made after the product using this settings
defaultpercentagedecimal(50,20)The default principal payment percentage for the accounts made after the product using this settings
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID. This ID is also used as a foreign key to link to the loanproduct table.
maxamountdecimal(50,10)The maximum principal payment amount for the accounts made after the product using this settings
maxpercentagedecimal(50,20)The maximum principal payment percentage for the accounts made after the product using this settings
minamountdecimal(50,10)The minimum principal payment amount for the accounts made after the product using this settings
minpercentagedecimal(50,20)The minimum principal payment percentage for the accounts made after the product using this settings



productaccountsettings

this table is for an upcoming feature and more documentation will be provided as the release progresses.
Column NameData TypeDescription
accountserviceenabledbit(1)Whether the account service is enabled. This is an upcoming feature.
encodedkeyvarchar(32)A unique key.
productkeyvarchar(32)The unique key of the product.
producttypevarchar(128)The type of product.



productarrearssettings

table used for holding the arrears settings for a product.
Column NameData TypeDescription
defaulttolerancepercentageofoutstandingprincipaldecimal(50,20)The default tolerance allowed as a percentage of outstanding principal which has been configured for this product and will be suggested for all newly created accounts.
defaulttoleranceperiodint(11)Default tolerance period
encodedkeyvarchar(32)The encoded key for these settings.
maxtolerancepercentageofoutstandingprincipaldecimal(50,20)The mximum tolerance allowed as a percentage of outstanding principal which has been configured for this product.
maxtoleranceperiodint(11)Maximum tolerance period
mintolerancepercentageofoutstandingprincipaldecimal(50,20)The minimum tolerance allowed as a percentage of outstanding principal which has been configured for this product.
mintoleranceperiodint(11)Minimum tolerance period
monthlytolerancedayint(11)Represents the monthly arrears tolerance day value..



productoffsetsettings

stores loan product offset settings
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key.
isoffsetenabledtinyint(1)Whether the offset feature is enabled for this product.
loanproductkeyvarchar(32)The encoded key of the loan product.



productpaymentholidayssettings

DEPRECATED
Column NameData TypeDescription
allowinterestaccrualtinyint(1)If set to true then accounts will continue to accrue interest during the payment holiday period.
encodedkeyvarchar(32)A unique key for these settings.
productkeyvarchar(32)The encoded key of the loan product.



productredrawsettings

table used for storing the redraw settings available for a product.
Column NameData TypeDescription
allowredrawbit(1)Flag which indicates if the product has the redraw functionality enabled
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID, used as the primary key for this table.



productsecuritysettings

the security settings (guarantors, collateral, investor funds) available for an entity.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID.
funderinterestcommissionallocationtypevarchar(255)Define how the Interest is allocated to the investors(if the investors can define their own percentages for their own contribution to the loan, or if all of them are using the same percentage)
funderinterestcommissionkeyvarchar(32)Constraints for funder interest commission. Foreign key to the decimalIntervalConstraints table.
iscollateralenabledbit(1)Whether collateral (assets or other goods) are accepted in order to reach required securities percentage from loan amount, as defined in this product
isguarantorsenabledbit(1)Whether guarantors (other clients) are accepted in order to reach the required securities percentage from loan amount, as defined in this product
isinvestorfundsenabledbit(1)Whether investor funds are accepted in order to allow external funding for an account
lockfundsatapprovalbit(1)Whether investor funds are locked or not at the loan account’s approval
organizationinterestcommissionkeyvarchar(32)Constraints for organization interest commission. Foreign key to the decimalIntervalConstraints table.
requiredguarantiesdecimal(50,20)The securities percentage from loan amount that is needed in order for this account to be approved.
requiredinvestorfundsdecimal(50,20)The required investor funds percentage, for opening an account with external funding



repayment

captures the details about repayments which are both due and have been paid off. all repayments belong to a loan account but are also themselves assigned to creditofficer & branches (for performance look-up reasons). a repayment captures the schedule and state of repayment, while the actual transactions are captured in the loantransaction model.
Column NameData TypeDescription
assignedbranchkeyvarchar(32)Foreign key to the Branch that this account is assigned to
assignedcentrekeyvarchar(32)Foreign key to the Centre table indicating to which centre the repayment belongs to.
assigneduserkeyvarchar(32)Foreign key to the User (Credit Officer) who is assigned to his account
duedatedatetimeDate when this repayment is due (ex: ‘2011-09-07 00:00:00’) (Organization Time). Required
encodedkeyvarchar(32)The unique key for this repayment.
feesduedecimal(50,10)How much fees were originally due on this repayment (for fixed accounts only). This is equivalent to the Fees Expected column in the Mambu UI, it will not change with partial payments and always reflect the amount originally due.
feespaiddecimal(50,10)How much fees are have been paid on this repayment (for fixed accounts only)
fundersinterestduedecimal(50,10)P2P accounts only - the amount of interest allocated to funders
interestduedecimal(50,10)The amount of interest that was due for this repayment. Required.
interestpaiddecimal(50,10)The amount of interest paid for this repayment. Required.
lastpaiddatedatetimeDate when the newest repayment has been entered for this repayment (eg, if multiple partial payments - then the latest of those. Null if not paid yet (Organization Time)
lastpenaltyapplieddatedatetimeSet to the newest date whenever a penalty is applied to this repayment. Or null if no penalty applied (Organization Time)
notesvarchar(256)Notes about this repayment. Unused.
organizationcommissionduedecimal(50,10)P2P accounts only - the amount of interest originally due and allocated to organization as commission
parentaccountkeyvarchar(32)Foreign key to the loan account this repayment belongs to. Required
penaltyduedecimal(50,10)How much penalty were originally due on this repayment (for fixed accounts only). This is equivalent to the Penaltiy Expected column in the Mambu UI, it will not change with partial payments and always reflect the amount originally due.
penaltypaiddecimal(50,10)How much penalty has been paid on this repayment (for fixed accounts only)
principalduedecimal(50,10)The amount of principal originally due for this repayment. Required. This is equivalent to the Principal Expected column in the Mambu UI, it will not change with partial payments and always reflect the amount originally due.
principalpaiddecimal(50,10)The amount of principal paid for this repayment. Required.
repaiddatedatetimeDate when this repayment has been fully repaid. Null if not fully repaid yet (Organization Time)
statevarchar(256)State of the repayment. Must be one of:

- PENDING - the payment is upcoming and is awaiting to be paid back on the dueDate
- LATE - the repayment is now late (past its due date)
- PAID - the repayment has been paid in full
- PARTIALLY_PAID - the repayment has been partially paid, but not in full
- RESCHEDULED - the repayment balances have been rescheduled into other repayments
- GRACE - this repayment is part of a grace period or it has been reduced through the Reduce Number of Installments (RNI) prepayment recalculation method
taxfeesduedecimal(50,10)The amount originally due as tax for fees. This is included in the Taxes Expected column in the Mambu UI, it will not change with partial payments and always reflect the amount originally due.
taxfeespaiddecimal(50,10)The amount of taxes paid relating to fees charged on the account.
taxinterestduedecimal(50,10)The amount of taxes that are due at a specified moment in time relating to interest payments. This is included in the Taxes Expected column in the Mambu UI, it will not change with partial payments and always reflect the amount originally due.
taxinterestpaiddecimal(50,10)The amount of taxes that were paid by the user relating to an interest payment
taxpenaltyduedecimal(50,10)The amount of tax due relating to a penalty payment. This is included in the Taxes Expected column in the Mambu UI, it will not change with partial payments and always reflect the amount originally due.
taxpenaltypaiddecimal(50,10)The amount of tax paid relating to a penalty payment.



repaymentdays

stores a loan account’s fixed days of month settings.
Column NameData TypeDescription
accountkeyvarchar(32)The encoded key of the loan account. Links to the loanaccounts table.
daytinyint(2)The day of the month on which the repayment should fall.



repaymentfeedetails

a model used to keep fee details (fee due, fee paid and taxes per predefinedfee type) for a specific repayment.
Column NameData TypeDescription
encodedkeyvarchar(32)The unique ID for these repayment fee details.
feeduedecimal(50,10)The total fee due for the linked PredefinedFee on this repayment.
feepaiddecimal(50,10)The total fee paid for the linked PredefinedFee on this repayment.
feereduceddecimal(50,10)The amount of fees when they have been reduced, for example, during a grace period.
loantransactionkeyvarchar(32)The key of the loan transaction of LoanTransactionType FEE type which contains the predefined fee (in PredefinedFeeAmount) for which the amount was applied/paid for the linked repayment
repaymentfeedetails_encodedkey_ownvarchar(32)The encoded key of a repayment in the repayments table to which these details relate.
repaymentfeedetails_integer_idxint(11)If there are more than one fee for the same repayment then this will show this fee’s index in that list.
taxonfeeduedecimal(50,10)The amount of tax on fee due for the linked PredefinedFee on this repayment.
taxonfeepaiddecimal(50,10)The amount of tax on fee paid for the linked PredefinedFee on this repayment.
taxonfeereduceddecimal(50,10)The amount of tax due on fees when they have been reduced, for example, during a grace period.



repaymentsscheduleversioning

Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for this row.
idbigint(20)
loanaccountchangedeventkeyvarchar(32)Key of an entry in the loanaccountchangedeventkey table.
loantransactionkeyvarchar(32)The ID of the loan transaction. Foreign key, an entry in the loantransaction table.
parentaccountkeyvarchar(32)The encoded key of an account in the loanaccount table.
versioningcontentjson



repaymentunappliedfeedetails

a model used to keep fee details for unapplied fee (fee due and taxes per predefined fee type) for a specific loan repayment.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key.
feeduedecimal(50,10)The total fee due for the linked predefinedfee of this repayment
indexinlistint(11)An index for cases where there is more than one unapplied fee for a single repayment.
predefinedfeekeyvarchar(32)The key of the entry in the predefinedfee containing the type of fee which contains the predefined fee amount (in the predefinedfeeamount table) that was not applied for the linked repayment.
repaymentkeyvarchar(32)The key of the entry in the repayment table to which this entity belongs.
taxonfeeduedecimal(50,10)The amount of tax due for this repayment.



revolvingproductsettings

Holds revolving loan products specific settings.
Column NameData TypeDescription
billingcycleenabledtinyint(1)Indicates whether a periodic billing cycle is enabled for this loan product.
productkeyvarchar(32)The encoded key of the product.



role

holding information about roles for users.
Column NameData TypeDescription
accessrightsmediumblobAn encoded representation of the permissions assigned to this role.
creationdatedatetimeThe date when the role was created. Stored as UTC
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables, in this case, the user and usagerightsroleassignment tables.
idvarchar(256)The manually entered ID for this role.
isadministratorbit(1)Whether the role is for a user who should have administrator privileges.
iscreditofficerbit(1)Whether the role is for a Credit Officer type user.
isdeliverybit(1)Whether the role is for members of the Mambu delivery team who may assist in setting up your Mambu system.
issupportbit(1)Whether the role is for a support user.
istellerbit(1)Whether or not this user has the ‘teller’ role.
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
namevarchar(256)The name of the role.
notesmediumtextNotes about the role which have been entered via the Mambu UI.
permissions_encodedkey_oidvarchar(32)Links to the an entry in the permissions table containing more permissions and whether they are enabled for this role.



savingsaccount

a savings account representing also a daily account, a term-deposit or a checking account is essentially the current account of the client where deposits and withdrawals can be made.
Column NameData TypeDescription
accountholderkeyvarchar(32)Foreign key reference to the Client or Group which is holding on this account. Required
accountholdertypevarchar(256)The type of account holder this group has. Must be one of the following and direct to the key referred to by accountHolderKey
- CLIENT
- GROUP
Required
accountstatevarchar(256)Current state of the account. Must be on one of:
- PENDING_APPROVAL - created but not active and is pending approval
- APPROVED - approved but not yet active (ie, no transaction have yet occurred)
- ACTIVE - account is active and is collecting interest, deposits and withdrawals may be made
- ACTIVE_IN_ARREARS - Account is active but has outstanding balance
- MATURED - only for fixed deposits or savings plan: the account has matured and the money may be withdrawn
- LOCKED - the account is locked and may not be used further unless unlocked
- DORMANT - Savings account state used for accounts that had been inactive for a number of days (Defined in savings product)
- CLOSED - the account was full emptied and closed because it was no longer being used
- CLOSED_WRITTEN_OFF - Account has been closed and any remaining balance due has been written off
- WITHDRAWN - if the client withdrawn the original application for the account
- CLOSED_REJECTED - Account has gone through the application process and has been rejected
Required
accounttypevarchar(256)
Type of savings account. This must be one of:
- REGULAR_SAVINGS - a current account
- FIXED_DEPOSIT - a deposit is made for a certain time period until it reaches maturity
- SAVINGS_PLAN - deposits are made over a time until a target or time period is reached
Required
accruedinterestdecimal(50,10)How much interest has been accrued into the account.
activationdatedatetimeDate when this saving account was activated (Organization Time)
allowoverdraftbit(1)Whether this account may be overdrawn
approveddatedatetimeThe date on which the account was approved.
assignedbranchkeyvarchar(32)Foreign key to the Branch that this account is assigned to
assignedcentrekeyvarchar(32)Foreign key to the Centre table indicating to which centre the savings account belongs to.
assigneduserkeyvarchar(32)Foreign key to the User (Credit Officer) who is assigned to his account
balancedecimal(50,10)The current balance of the account. Required.
blockedbalancedecimal(50,10)The amount of this account’s funds which are unavailable for use because they are blocked.
closeddatedatetimeSet to when the account was closed (or null if never) (Organization Time)
creationdatedatetimeThe date when the savings account was created.Stored as UTC.
currencycodevarchar(32)The currency code associated to this product.Required.
encodedkeyvarchar(32)The encoded key for this database entry. This ID can be used with our API to get details on or update a specific Savings Account.
feesduedecimal(50,10)How much fees is due to be paid on this account
forwardavailablebalancedecimal(50,10)Stores the positive hold balance for a savings account.
holdbalancedecimal(50,10)Hold balance of the account (it is included in balance). Required
idvarchar(32)Unique ID of the savings account. Required
interestduedecimal(50,10)How much interest is due to be paid on this account
interestpaymentdatesmediumblob List of all dates on which the interest is payed into savings account
interestpaymentpointvarchar(256)Specifies when the interest should be paid to the account (Eg. FIRST_DAY_OF_MONTH, EVERY_3_MONTHS, etc)
interestsettingskeyvarchar(32)Foreign key to the interestaccountsettings table where the configuration for interest for this account is stored.
lastaccountappraisaldatedatetimeDate when the account has last been evaluated for interest calculation/maturity. Null if never (UTC)
lastinterestcalculationdatedatetimeDate when/if this account has the interest last calculated. Null if never (Organization Time)
lastintereststoreddatedatetimeDate when the account had last the interest applied (that is, stored from accrued to the balance). Null if never (Organization Time)
lastmodifieddatedatetimeThe date when the savings account was modified last time. Stored as UTC.
lastoverdraftinterestreviewdatedatetimeWhen the overdraft interest was last time reviewed (as Organization Time)
lastsettoarrearsdatedatetimeThe last time this account went into arrears.
lineofcreditkeyvarchar(32)The key to the line of credit where this account is registered
lockedbalancedecimal(50,10)Locked balance of the account(it is included in balance). No operation can modify the balance of the account and get it lower than this locked balance
lockeddatedatetimeThe date when the account was locked(null if not closed). Saved as Organization Time.
maturitydatedatetimeFor a fixed or compulsory savings plan, this is when the account matures (Organization Time)
maxdepositbalancedecimal(50,10)The maximum depisit balance, if set.
maxwidthdrawlamountdecimal(50,10)The max amount that can be withdrawn at any time (or null if no limit)
migrationeventkeyvarchar(32)Foreign key to the dataMigrationEvent table: references the particular operation if this account was created as part of a data import.
namevarchar(256)The name of the loan account. Often same as the savings product name. Required.
negativeinterestaccrueddecimal(50,10)The amount of interest accrued when the interest rate is negative.
notesmediumtextHTML notes about this savings account.
overdraftamountdecimal(50,10)How much money has been taken out in overdraft
overdraftexpirydatedatetimeThe date after which the account is considered in arrears (as Organization Time)
overdraftinterestaccrueddecimal(50,10)The amount of overdraft interest that has been accrued in the account
overdraftinterestsettingskeyvarchar(32)References the entry in the interestaccountsettings table where settings concerning overdrafts are configured for this account.
overdraftlimitdecimal(50,10)how much may be taken out as overdraft.
producttypekeyvarchar(32)Foreign key to the SavingsProduct which this account is based on. Required
recommendeddepositamountdecimal(50,10)For account which have a recommended deposit amount
targetamountdecimal(50,10)For savings plans, this is the savings target amount
technicalinterestduedecimal(50,10)How much interest is due to be paid on this account due to technical overdraft
technicaloverdraftamountdecimal(50,10)How much money has been taken from unplanned overdraft. This balance is usually used when doing advice cards operation(offline cards transactions)
technicaloverdraftinterestaccrueddecimal(50,10)The amount of technical overdraft interest that has been accrued in the account
withholdingtaxsourcekeyvarchar(32)The tax source from where the account withholding taxes will be updated. Can be null, in which case the account will not have withholding taxes



savingsaccountcustomvalue

Holds values for custom information for savings accounts. Please note: This table is for an upcoming feature and may not contain any data for your organization.
Column NameData TypeDescription
definitionidsmediumtextHolds the list of the custom field encodedkeys generated from the JSON held in the values column.
linkedentitykeysmediumtextHolds the list of linkedentitykeys generated from the JSON held in the values column. These will be the entities to which this custom field is linked for custom fields of type CLIENT_LINK, GROUP_LINK or USER_LINK.
parentkeyvarchar(32)The encodedkey of the entity holding the custom values within the values JSON column. This will be the encodedkey of the entity with which these custom field values are associated.
valuesjsonHolds all the custom field data in a JSON structure, including keys, IDs, values and indexes.



savingsaccountdailyaccruedinterest

stores daily interest accrual for savings accounts.
Column NameData TypeDescription
accountkeyvarchar(32)The unique key of the account.
accruedinterestdecimal(60,20)The amount of accrued interest.
idbigint(20)Primary key for rows in this table.
lastinterestcalculationdatedatetimeThe date and time at which the accrued interest was last calculated.
lastmodifieddatedatetimeThe last time this row was modified. UTC
negativeinterestaccrueddecimal(60,20)The amount of interest accrued when the interest rate is negative.
overdraftinterestaccrueddecimal(60,20)The amount of interest accrued due to the account being overdrawn.
technicaloverdraftinterestaccrueddecimal(60,20)The amount of interest accrued due to technical overdraft of the account.



savingsproduct

stores templates that specify some predefined information and constraints that are then applied to the savings accounts, associated with a specific savings product. products can be defined for individuals and groups and has a specified interest rate and the interest calculation frequency.
Column NameData TypeDescription
accountingmethodvarchar(256)The current accounting state for this product
- NONE - accounting is deactivated
- CASH - uses cash accounting
- ACCRUAL - uses accrual accounting
activatedbit(1)Whether this product can be used or not.
allowarbitraryfeesbit(1)Only if true users will be able to apply fees, for current object, of type ‘Other’; these fees can have any amount
allowoffsetbit(1)Specify if the product allow to create accounts which can be used as offset for loans
allowoverdraftbit(1)Whether the accounts for this product may be overdrawn
allowtechnicaloverdraftbit(1)Indicates whether these accounts are allowed to go into technical overdraft, which can happen, for example, in accounts with credit or debit cards.
categoryvarchar(256)The category of this savings product. This helps organise products into business areas. Can be UNCATEGORIZED, PERSONAL_DEPOSIT, BUSINESS_DEPOSIT, DAILY_BANKING_ACCOUNTS, BUSINESS_BANKING_ACCOUNTS, STORED_VALUE_ACCOUNTS.
collectinterestwhenlockedbit(1)Whether locked accounts still collect Interest or not (default is true)
creationdatedatetimeThe date when the savings product was created. Stored as UTC
defaultmaturityperiodint(11)How long a fixed deposit or a savings plan can have a maturity period (the default period)
defaultopeningbalancedecimal(50,10)The constraint for the default opening balance for a saving account using this product
descriptionmediumtextThe savings product description
dormancyperioddaysint(11)Specifies the number of days for an account to change the state to Dormant
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables, in this case the predefinedfee, savingsproductbranch, savingsaccount and savingstransaction tables.
forallbranchesbit(1)Field to indicate if this product is available for all branches
forgroupsbit(1)If the product is available for groups
forindividualsbit(1)If the product is available for individual entities
idvarchar(32)Unique ID of the savings product (specified by the user). Required
idgeneratortypevarchar(256)The type of the ids that will be generated:
- RANDOM_PATTERN - uses a given pattern to generate IDs
- INCREMENTAL_NUMBER - increments a given number to generate IDs
idpatternvarchar(256)The pattern, containing ‘@’ for letters and ‘#’ for digits, for the RANDOM_PATTERN or the starting number for the INCREMENTAL_NUMBER
interestaccruedaccountingmethodvarchar(32)Method being used for maintaining the interest accrued method for the loan product
interestcalculationbalancevarchar(256)The balance which is used for the Interest calculation
- MINIMUM - the minimum balance during that time period
- AVERAGE - the average balance during that time period
interestdaysinyearvarchar(256)How many days in a year should be used for interest calculations
interestpaidintoaccountbit(1)Whether the accounts for this product have interest paid into account
interestpaymentdatesmediumblob List of all dates on which the interest is applied into savings account
interestpaymentpointvarchar(256)Specifies when the interest should be paid to the account:
- FIRST_DAY_OF_MONTH - interest is paid on day 1 of each month
- EVERY_WEEK - for every week, interest should be paid out each 14 days, first time is after 14 days since the account went active
- EVERY_OTHER_WEEK - for every 2 weeks, interest should be paid out each 14 days, first time is after 14 days since the account went active
- EVERY_MONTH - interest should be paid out after a month since activation. e.g. May 12th went active, so post on June 12th, July 12th
- EVERY_3_MONTHS - interest should be paid out after 3 months (quarterly) since activation. e.g. May 12th went active, so post on August 12th
interestratesettingskeyvarchar(32)Foreign key to the interestProductSettings table: Settings for the account interest rate.
lastmodifieddatedatetimeThe date when the savings product was modified last time. Stored as UTC.
lineofcreditrequirementvarchar(255)Specifies whether accounts created after this product can/should be part of a line of credit
Possible values:
- OPTIONAL (account can be part of a line of credit)
- REQUIRED (account should be part of a line of credit)
- NOT_REQUIRED (account should not be part of a line of credit)
maturityperiodunitvarchar(256)How long a fixed deposit or a savings plan can have a maturity period:
- DAYS
- WEEKS
- MONTHS
maximumbalancedecimal(50,10)The maximum balance this account can hold
maxmaturityperiodint(11)How long a fixed deposit or a savings plan can have a maturity period (the maximum period)
maxopeningbalancedecimal(50,10)The constraint for the maximum opening balance for a saving account using this product
maxoverdraftinterestratedecimal(50,10)The maximum overdraft interest account which can be set for accounts created using this product.
maxoverdraftlimitdecimal(50,10)How much money may be taken out for the account to go negative
maxwidthdrawlamountdecimal(50,10)Maximum amount per withdrawal
minmaturityperiodint(11)How long a fixed deposit or a savings plan can have a maturity period (the minimum period)
minopeningbalancedecimal(50,10)The constraint for the minimum opening balance for a saving account using this product
minoverdraftinterestratedecimal(50,10)The minimum overdraft interest account which can be set for accounts created using this product.
namevarchar(256)The name of the product.
overdraftdaysinyearvarchar(256)Number of days in year for which to accrue interest for overdraft account
Days in a year methodology used for interest calculations for this product
- ACTUAL_365_FIXED
- ACTUAL_364
- ACTUAL_360
- E30_360
overdraftinterestcalculationbalancevarchar(256)Which calculation will be used to calculate overdraft interest: MINIMUM - the lowest balance the account had that day, END_OF_DAY - the balance recorded after completion of all end of day jobs.
overdraftinterestratesettingskeyvarchar(32)Foreign key to the interestProductSettings table: Settings for the overdraft interest rate.
producttypevarchar(256)The type of savings product/account. This influences the behavior and possible parameters of the account.
The savings type can be:
- CURRENT_ACCOUNT - a current which fully allows withdrawals/deposits and overdrafts
- REGULAR_SAVINGS - a standard savings which fully allows withdrawals/deposits
- FIXED_DEPOSIT - a fixed deposit where one deposit is made for a certain time period until it reaches maturity
- SAVINGS_PLAN - a savings plan where savings deposits are made over a certain time period usually with the goal of reaching some savings target. once the time period has expired the account is ‘matured’ and withdrawals can be made


recommendeddepositamountdecimal(50,10)Recommended amount for a deposit
withholdingtaxenabledbit(1)Whether withholding taxes are enabled for this product or not



savingsproductbranch

stores the association between a savings product and the branches where it is available.
Column NameData TypeDescription
branchkeyvarchar(32)Foreign key to branch table: The key of the branch that is associated with a savings product.
encodedkeyvarchar(32)The encoded key for this database entry.
productkeyvarchar(32)Foreign key to `savingsProduct table: The savings product that is associated with a branch.



savingstransaction

keeps track of all transactions which occur with savings accounts such as state changes, repayments, fees, etc.
Column NameData TypeDescription
amountdecimal(50,10)Amount of the transaction. For example, this may be the amount of repayment or for certain transactions may be null (for state changes for instance.) The amount is expressed relative to how it affects the balance. If a repayment is adjusted (reduced) the amount will be a negative balance
balancedecimal(50,10)Running balance for the savings account including this current transaction.
branchkeyvarchar(32)Foreign key to the branch where this transaction was performed.
centrekeyvarchar(32)Foreign key to the centre where this transaction was performed.
commentvarchar(256)Comment for the savings transaction.
creationdatedatetimeWhen the transaction occurred (as UTC)
currencycodevarchar(32)Currency code for current transaction.
details_encodedkey_oidvarchar(32)Details about the current savings transaction
encodedkeyvarchar(32)The encoded key for this database entry.
entrydatedatetimeDate of the entry (eg date of repayment or disbursal, etc.). As Organization Time
externalidvarchar(36)The ID set by the customers that accepts alpha-numeric characters, underscore and dash. Can be null
feesamountdecimal(50,10)Amount of fees involved in a transaction that affects an account with positive balance
fractionamountdecimal(50,10)In a case of an Loan Fraction Bought transactions, this represent the fraction amount which was bought from another investor.
fundsamountdecimal(50,10)Balance change amount involved in a transaction that affects an accountwith positive balance
interestamountdecimal(50,10)Amount of interest involved in a transaction that affects an account with positive balance
interestratedecimal(50,20)The interest rate that was set or changed in this transaction. Used on product interest rate changes or interest tier switches.
linkedloantransactionkeyvarchar(32)Foreign key to the LoanTransaction which is associated with this transaction (for example a transfer which causes a repayment)
linkedsavingstransactionkeyvarchar(32)Foreign key to the SavingsTransaction which is associated with this transaction (for example a transfer which causes a deposit)
migrationeventkeyvarchar(32)Foreign key to the dataMigrationEvent table: If this transaction was created during import, track which migrationevent they came from.
overdraft_indexrate_keyvarchar(32)Foreign key to the indexRate table: The index overdraft interest rate that was set or changed in this transaction.
overdraftamountdecimal(50,10)Balance change amount involved in a transaction that affects an overdraft
overdraftfeesamountdecimal(50,10)Fees amount involved in a transaction that affects an overdraft
overdraftinterestamountdecimal(50,10)Interest amount involved in a transaction that affects an overdraft
overdraftinterestratedecimal(50,20)The overdraft interest rate that was set or changed in this transaction. Used on product interest rate changes or interest tier switches.
overdraftlimitdecimal(50,10)The overdraft limit that was set or changed in this transaction
parentaccountkeyvarchar(32)Foreign key to the savings account this transaction refers to. Required
paymentorderidvarchar(36)The payment order ID for transactions which were created via the Mambu Payments Gateway, for example, a SEPA Direct Debit or Credit Transfer.
preciseinterestamountdecimal(50,20)Interest amount without rounding(for now populated only for P2P).
producttypekeyvarchar(32)Link to the product to which the account owning this transaction belongs to.
reversaltransactionkeyvarchar(32)Foreign key to another savings transaction (to self - SavingsTransaction.encodedKey) where the reversal of the current transaction was made. It’s null if the transaction wasn’t reversed.Example: This transaction represents a fee applied transaction. If this transaction will be reversed, another transaction will be logged and this transaction will remember the key of the one where the reversal was made.
taxrate_encodedkey_oidvarchar(32)Foreign key to the indexRate table: The tax rate that was set or changed in this transaction.
technicaloverdraftamountdecimal(50,10)Balance change amount involved in a transaction that affects an technical overdraft
technicaloverdraftinterestamountdecimal(50,10)Interest amount involved in a transaction that affects an technical overdraft
tillkeyvarchar(32)The till key associated with this transaction
transactionidbigint(20)Auto-increment unique ID of the savings transaction. Required
transactionperformerkeyvarchar(32)
typevarchar(256)Type the transaction. Must be one of:
- CREATION - the account was created (DEPRECATED)
- EDIT - the account was modified (DEPRECATED)
- STATE_CHANGE - the account’s state changed, eg for an Approval (DEPRECATED)
- DEPOSIT - a deposit into the account
- WITHDRAWAL - a withdrawal from the account
- ADJUSTMENT - an adjustment on a deposit
- INTEREST_APPLIED - accrued interest has been applied to the account
- FEE_APPLIED - a fee was applied to the account
- FEE_ADJUSTED - a previously applied fee was adjusted
- WRITE_OFF - an account written off
- WITHDRAWAL_ADJUSTMENT - an adjustment to a withdrawal
- ADJUSTMENT - reversal of a deposit
- BEGIN_MATURITY_PERIOD - the start of a maturity period for an account (DEPRECATED)
- BRANCH_CHANGED - marks the moment when the parent account is assigned to a different branch
- FEE_REDUCTION_ADJUSTMENT - reversal for FEES_DUE_REDUCED
- FEES_DUE_REDUCED - a fee being decreased
- IMPORT - an account being imported
- INTEREST_APPLIED_ADJUSTMENT - reversal for the INTEREST_APPLIED transaction
- INTEREST_RATE_CHANGED - there was a change to the interest rate for this account
- LOAN_FUNDED - investor funds amount being transferred to the linked loan account
- LOAN_FUNDED_ADJUSTMENT - reversal for the LOAN_ACCOUNT_FUNDED transaction
- LOAN_REPAID - investor funds amount being collected from the linked loan account
- LOAN_REPAID_ADJUSTMENT - reversal for the LOAN_REPAID transaction
- OVERDRAFT_INTEREST_RATE_CHANGED -the overdraft interest rate has changed
- TRANSFER - a transfer being made
- TRANSFER_ADJUSTMENT - a transfer being adjusted (reversed)
- UNDO_BEGIN_MATURITY_PERIOD - reversing the start of the maturity period for the account (DEPRECATED)
- WITHDRAWAL - a withdrawal being made
- WITHDRAWAL_ADJUSTMENT - a withdrawal being adjusted
- WITHHOLDING_TAX - tax being applied over an interest amount (the interest which the clients earn, not the one from the overdrafts)
- WITHHOLDING_TAX_ADJUSTMENT - reversal for the WITHHOLDING_TAX transaction
- WRITE_OFF_ADJUSTMENT - the overdraft write off being adjusted
Required
userkeyvarchar(32)Foreign key to the User who performed this transaction. If null, it means this was a system-performed transactions (such as an automatic penalty)



savingstransactioncustomvalue

Holds values for custom information for savings transactions. Please note: This table is for an upcoming feature and may not contain any data for your organization.
Column NameData TypeDescription
definitionidsmediumtextHolds the list of the custom field encodedkeys generated from the JSON held in the values column.
linkedentitykeysmediumtextHolds the list of linkedentitykeys generated from the JSON held in the values column. These will be the entities to which this custom field is linked for custom fields of type CLIENT_LINK, GROUP_LINK or USER_LINK.
parentkeyvarchar(32)The encodedkey of the entity holding the custom values within the values JSON column. This will be the encodedkey of the entity with which these custom field values are associated.
valuesjsonHolds all the custom field data in a JSON structure, including keys, IDs, values and indexes.



savingstransactionguarantymapping

saves mapping information between investorfund and savingstransaction
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry.
newguarantykeyvarchar(32)Foreign key to the guaranty table: The ID of the new guaranty settings after a change.
oldguarantykeyvarchar(32)Foreign key to the guaranty table: The ID of the original guaranty settings.
savingstransactionkeyvarchar(32)Foreign key to the savingsTransaction table: The ID of the transaction to which these guaranty settings relate.



savingstransactiontoexternal

This is an internal table which may be used to record when transactions are simultaneously transmitted to other services, such as when new features are being tested and you have elected to be part of a pre-release testing programme.
Column NameData TypeDescription
creationdatedatetime(6)When the transaction occurred (as UTC).
errorsvarchar(512)Whether any errors ocurred during the process of syncing this transaction to an ancialliary service.
externalencodedkeyvarchar(255)
externaltypevarchar(16)Details about the external system to whom is the request sent, for example MBU_LENDING for the Mambu lending module.
savingstransactionencodedkeyvarchar(32)The external key of the savings transaction.
statusvarchar(16)Status of the current transaction, CREATED, SUCCEEDED or FAILED.
updatedatedatetime(6)When the transaction was updated (as UTC).



scheduledjobsummary

this table holds details on scheduled jobs including those run as eod processes.
Column NameData TypeDescription
backgroundprocesskeyvarchar(32)Foreign key to an entry in the backgroundprocess table. Many jobs in this table can be related to one background process.
creationdatedatetime(3)The date when this job was created. Stored as UTC
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
enddatedatetime(3)The date and time at which the job was completed. UTC
noentitiesbigint(32)The number of entities which were affected by this scheduled job.
processingtimebigint(32)The time taken to complete the job, in seconds.
scheduledjobcategoryvarchar(32)Whether the job is a general ORGANIZATION task, relates to ACCOUNTS, LOANS, SAVINGS, or OTHER.
scheduledjobtypevarchar(128)The type of job which has been scheduled.
startdatedatetime(3)The date and time at which the job started. UTC



scheduledprocess

model for a scheduled process (one time run, at midnight) like the job of updating all account settings from a savingsproduct. this kind of jobs are currently initiated by changes in the products that need to be mirrored also in the accounts.
Column NameData TypeDescription
creationdatedatetimeThe date when this row was created. Stored as UTC
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
enddatedatetimeThe time at which the scheduled process ended.
entitykeyvarchar(32)Link to the entity, for example, the savings product, where settings are held.
startdatedatetimeThe date and time at which the scheduled process started.
statusvarchar(255)The status of this process, for example started, completed, etc..
typevarchar(255)The process type, the job type used to correlate the process with the actual jobs that needs to be executed. For example UPDATE_SAVINGS_ACCOUNTS_SETTING_FROM_PRODUCT, UPDATE_LOAN_ACCOUNTS_SETTINGS_FROM_PRODUCT, UPDATE_JOURNAL_ENTRIES_FOR_INTEREST_ACCRUAL.



securitysettings

stores the custom organization security settings (session timeout, password complexity, ip address restrictions, maximum number of consecutive failed logins allowed, failed login wait time, password reset link expiration time, whether to re-authenticate on critical actions, the password expiration days etc.).
Column NameData TypeDescription
creationdatedatetimeThe date when these settings were created. Stored as UTC
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables.
failedloginwaittimeint(11)The time a user will have to wait before attempting to log in after a failed attempt.
ipaddressrestrictionsmediumblob If the option to restrict access by IP has been enabled, this field will hold the list of allowed IP addresses.
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
lockuserafterfailedloginattemptsint(32)Indicates the number of failed login attempts a user is allowed before their account is locked. Once locked it can only be unlocked by an administrator.
lockuserafterfailedloginattemptswithinminutesint(32)Indicated the number of minutes over which failed login attempts will be counted. For example, if set to 10 minutes, the count of failed attempts will reset to 0 10 minutes after the last attempt.
maxfailedloginscountbeforecaptchaint(11)The number of failed login attempts a user will be allowed before they will need to prove they are human by completing captcha.
minpasswordlengthint(11)The minimum length of a password created for a user account.
passwordexpirationactivationdatedatetimeThe first day from which the password expiration countdown started.
passwordexpirationdaysint(32)The number of days before a user will be prompted to change their password.
passwordresetlinkexpiretimehoursint(11)The amount of time before the link to reset a password included in an email will expire.
reauthenticateoncriticalactionsbit(1)Indicates whether users will be asked to enter a password when performing certain actions via our UI.
restrictedsecuritygroupsmediumblob If IP address whitelisting has been enabled, this contains an array of the user types (admins, users and API users) to which this restriction will be applied.
restrictuseraccessipbit(1)Whether access to the UI, API or Admin functions should be restricted only to certain IP addresses.
sessiontimeoutint(11)The number of minutes before a session expires. This value only applies to the Mambu UI.



sequence_table

This is a technical table which tracks the next available number for entities or processes which are assigned sequential IDs.
Column NameData TypeDescription
next_valbigint(20)The next value available to be used for sequential numbering.
sequence_namevarchar(255)The name of the process with sequential numbering, for example, tasks, documents or activites.



smsnotificationsettings

containing the credentials and settings for the sms notifications.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry.
fromnumbervarchar(255)The telephone number which will appear as the sender on the recipient’s device.
gatewayvarchar(255)Indicates which of the supported SMS gateway providers, Twilio or Infobip, you are using to send SMS.
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
passwordvarchar(255)The password to your account at the SMS gateway provider.
usernamevarchar(255)The username used to log in to your SMS gateway provider.



task

represents a human task that can be assigned by a user to another one. it can be related to a client or a group and also a due date can be specified for it.
Column NameData TypeDescription
assigneduserkeyvarchar(32)User assigned to the task. Foreign key to the user table.
completiondatedatetimeOrganization time states the date the task was completed
createdbyuserkeyvarchar(32)The key of the user who created the task
creationdatedatetimeUTC date of creation of the task
descriptionlongtext,Description, notes of the task
duedatedatetimeOrganization time states the due date of the task
encodedkeyvarchar(32)The encoded key for this task. This field should not be changed as it may be used as a foreign key to link with other tables as well as act as the index for this table.
idbigint(20)The id of the task
lastmodifieddatedatetimeUTC date of last modification
statusvarchar(256)ask status with valid values:
- OPEN
- COMPLETED
tasklinkkeyvarchar(32)Specifies who is the link to this task. If null, means nobody is linked to this task
tasklinktypevarchar(256)Type of the link. Valid values:
- CLIENT
- GROUP
- LOAN_PRODUCT
- SAVINGS_PRODUCT
- CENTRE
- BRANCH
- USER
- LOAN_ACCOUNT
- DEPOSIT_ACCOUNT
titlevarchar(256)Title, summary of the task



tenant

Column NameData TypeDescription
connectionpasswordvarchar(64)
connectionurlvarchar(512)
connectionusernamevarchar(64)
creationdatedatetimeThe date when this tenant was created. Stored as UTC
idvarchar(64)
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
namevarchar(128)The tenant name.



till

a till represents a concept introduced by the tellering feature. it can be a physical cash box a teller uses, or just a concept such as a when a manager hands cash to a credit officer who leaves for the day (he is then a ‘virtual’ till)
the typical process is that at the beginning of the work day a manager takes out cash from the vault at a branch and distributes it to various tills. they need to keep track how much is in each till. the tills are then assigned to staff (tellers) who perform transactions during the day with the cash.
at the end of the day, they close the till by counting up all the cash, checking it against the transactions that occurred and ensuring there’s no discrepancy. the teller may be held responsible for any shortage of cash. the manager then may choose to transfer the cash back to the vault (or it might stay in the till overnight).
Column NameData TypeDescription
balancedecimal(50,20)The amount of money existing in the till (expected cash in till)
balanceconstraintstypevarchar(32)One of:
- HARD - if a new transaction posted brings the balance in the Till beyond the specified thresholds, the Teller will get an error message and it will not be possible to post the transaction,
- SOFT - if a new transaction posted brings the balance in the Till beyond the specified thresholds, then the Teller will get a warning message, but the transaction will still be posted,
- NONE- no limits on the balance.
balancedifferencedecimal(50,10)The difference between the closing amount of the till and the expected amount
closeddatedatetimeThe date when the till was closed (as Organization Time).
closingbalancedecimal(50,10)The amount entered by the user as the available amount in till at its closing time
creationdatedatetimeThe date when this till was created. Stored as UTC
encodedkeyvarchar(32)The encoded key for this database entry, this is an autogenerated and globally unique ID and is not the same as any internal ID you may have given your till. This field should not be changed as it may be used as a foreign key to link with other tables.
idvarchar(32)The id of the till
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
maxbalancedecimal(50,10)The fields specifies the maximum balance. When tellers reach the maximum allowed balance, they should transfer to vault.
minbalancedecimal(50,10)This field specifies the minimum till balance. Tellers shouldn’t be able to post a withdrawal if they don’t have enough cash balance => minimum balance should be zero.
openeddatedatetimeThe date when the till was opened (as Organization Time).
originaltillkeyvarchar(32)Used for when reopening a till. When the till is reopened this field is populated with the encoded key of the closed till
statevarchar(256)Till state:
- OPEN - Value used after a till was created. When in this state a till can have money added or removed from the opening amount and its balance can be affected by transactions logged by the assigned teller
- CLOSED - Value used to mark the till as not-required. In this state, the till cannot have its opening amount or balance changed anymore.
tellerkeyvarchar(32)The key of the teller for which this till is assigned to
userkeyvarchar(32)The key of the user who created this till
vaultamountdecimal(50,10)The amount placed in the till. It can be changed by adding or removing money from it, but the user doing this requires special permissions



transactionchannel

stores the definition of payment types. organizations often need to collect payments (and make deposits and withdrawals and disbursements) from different payment forms. for instance different bank sources, various payment gateways or agents or simple cash and cheques ( (eg. cash, cheque, bank or receipt). these payment types specify, on a product level, which sources are allowed for that product.
Column NameData TypeDescription
activatedbit(1)States whether this transaction channel is active and can be used when entering repayments
createdbyuserkeyvarchar(32)The key of the user who created the channel
creationdatedatetimeDate of creation
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables, for example, this ID will be referenced in entries in the transactiondetails table.
idvarchar(32)32 character String hold the ID of the transaction channel
indexint(11)Transaction channel position in the list of transaction channels displayed in administration
loan_custom_filter_constraint_keyvarchar(32)Foriegn key to the customFilter table. Maintains the custom constraints, if limited usage selected, the transaction channel on loan transactions.
loanconstraintsusagevarchar(255)States the limited/unlimited usage of the transaction channel for loan transactions. Enumeration with the types of constraints available for Transaction Channels.
- UNCONSTRAINED_USAGE
- LIMITED_USAGE
namevarchar(255)Name of this transaction channel, will be used in display forms when entering payments
savings_custom_filter_constraint_keyvarchar(32)Foreign key to the customFilter table. Maintains the custom constraints, if limited usage selected, the transaction channel on savings transactions.
savingsconstraintsusagevarchar(255)States the limited/unlimited usage of the transaction channel for savings transactions. Enumeration with the types of constraints available for Transaction Channels.
- UNCONSTRAINED_USAGE
- LIMITED_USAGE
usagerightskeyvarchar(32)The usage rights that describes the transaction channel.



transactiondetails

stores the common details about any financial transaction such as what type of transaction it was, receipt numbers, etc. referred to by loantransaction and savingstransaction models. fields are optional and are used for tracking and auditing purposes only.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables, in this case, the loantransaction, savingstransaction and discbursementdetails tables.
internaltransferbit(1)Indicates whether the transaction was transferred between loans and savings accounts owned by the same customer.
targetsavingsaccountkeyvarchar(32)Foreign key to the savingsAccount table: In case of a transaction to a savings account this represent the savings account key for which the transaction was made.
transactionchannelkeyvarchar(32)Foreign key to the transactionChannel table: Associated payment type for the transaction.



transactionpaymentholidaysdetails

holds details about the payment holiday’s impact over the parent loan transaction.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for this row.
interestamountdecimal(50,10)The amount of interest payable.
taxoninterestamountdecimal(50,10)The amount ofd tax payable on the amount.
transactionkeyvarchar(32)Foreign key. An entry in the loantransaction table.



unusedloanidinterval

table containing unused numeric loan account ids. any creation of a loan account with a numeric id will be reflected in this table.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for this row.
endidbigint(32)The last ID in an unused range.
startidbigint(32)The first ID in the sequence which should be skipped.
versionbigint(32)



unusedloanidintervalupdate

details for unusedloanidinterval update operation. contains data about update input and update result.
Column NameData TypeDescription
encodedkeyvarchar(32)A unique key for this row.
splitresultendidbigint(32)Split interval result end id. Can be null. If splitResultStartId also null. This means that after updating the interval the interval was not split, otherwise is considered an infinite value.
splitresultstartidbigint(32)Split interval result start id. Can be null, this means that after update the interval the interval was not split.
updatedendidbigint(32)Updated interval end id. Can be null for the last unused interval. In this case is considered an infinite value.
updatedstartidbigint(32)Updated interval start id. Can’t be null.
updatedwithendidbigint(32)End id to update interval. Can be null if the update operation is not triggered by live migration and the interval is updated with only one value. In this case should consider having same value as updatedWithStartId.
updatedwithstartidbigint(32)Start id to update interval. Can’t be null.
updateresultendidbigint(32)Updated interval end id. Can be null for the last unused interval. In this case is considered an infinite value
updateresultstartidbigint(32)Updated interval result start id. Can be null, this means that after update the interval was deleted.



usagerights

entity used to maintain the relation between a usage-rights enabled entity and the roles that can edit it.
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry. This field should not be changed as it may be used as a foreign key to link with other tables
isaccessiblebyallusersbit(1)Whether or not all users can access this entity.



usagerightsroleassignment

Column NameData TypeDescription
idxint(11)
rolekeyvarchar(32)The role ID.
usagerightskeyvarchar(32)



user

a user is the entity that can access the mambu application. it can be a person: the manager of the organization, a credit officer or another stuff memeber, or it can be another application (that access the api). each usert may have some predefined permissions, to be able to access specific sections of the application, or it can be an administrator. it can have an assigned branch (for the credit officers is mandatory to be a part of a branch).
Column NameData TypeDescription
accessrightsmediumblob The access rights an user can have:
- MAMBU
- MOBILE
- APIS
aliasvarchar(32)Any alias provided for this user.
apiconsumertypevarchar(256)If using the API consumers feature this will indicate the type. Can be one of AUDIT, MAMBU_API, PAYMENTS, STREAMING_API. For more information check our support page on API consumers here
assignedbranchkeyvarchar(32)Foreign key to the Branch table indicating which branch the user belongs to
creationdatedatetimeThe date when the user was created. Stored as UTC
emailvarchar(256)The email of the user.
encodedkeyvarchar(32)The encoded key for this database entry, it can be used in our API to get data on a specific user of the system. This field is globally unique and should not be changed.
failedloginscountint(11)Stores the number of consecutive failed logins
failedloginsdatesmediumblob Stores the dates of failed logins. Maintained in UTC.
firstnamevarchar(256)User’s first name
homephonevarchar(256)The home phone number of the user
idbigint(20)The id of the user (it’s auto-incremented after each created user).
isadministratorbit(1)Whether this user is an administrator
iscreditofficerbit(1)Whether this user is a credit officer
isdeliverybit(1)Whether this user is a member of the Mambu delivery team who may assist in initially setting up your Mambu system.
issupportbit(1)Flag indicating the user is in charge with the Mambu technical support
istellerbit(1)Flag indicating if the user is a teller
languagevarchar(256)The language used for the interface. It can be:
- ENGLISH (default)
- PORTUGUESE
- SPANISH
- RUSSIAN
- FRENCH
lastloggedindatedatetimeThe date when the user last logged in the application (UTC)
lastmodifieddatedatetimeThe date on which this row was last modified. As UTC.
lastnamevarchar(256)User’s last name
lastpasswordresetdatedatetimeThe date on which this user’s password was last reset.
mobilephone1varchar(256)The mobile phone number of the user
notesmediumtextA short description of the user.
passwordvarchar(256)The encrypted password.
permissions_encodedkey_oidvarchar(32)The permissions of this user. Foreign key the permissions table.
provisionedthroughfederationbit(1)If set to true, when editing, the editor must edit the password of the user. It will only be true after a user was provisioned from federated authentication.
role_encodedkey_oidvarchar(32)References the set of roles this user has which is held in the roles table.
titlevarchar(256)The title of the user, for example, MR, MRS &c.
transactionlimitsmediumblob Larger organizations may want to be able to restrict different users to different transaction limits, such as some users can’t approve accounts over a certain amount or make large withdrawals. An organization can have transactions limits for each user, for approving and disbursing loans, entering repayments, making deposits and withdrawls or for applying fees.
twofactorauthenticationbit(1)For any users, a user may set, whether they require to be authenticated with both their phone number as well as an SMS code which they receive
usernamevarchar(254)The username (Unique)
userpreferenceskeyvarchar(32)References the user’s preferences which is held in the userpreferences table.
userstatevarchar(256)Whether a user can have access to his Mambu account. Possible states are:
- ACTIVE
- INACTIVE



usercustomvalue

Holds values for custom information for users. Please note: This table is for an upcoming feature and may not contain any data for your organization.
Column NameData TypeDescription
definitionidsmediumtextHolds the list of the custom field encodedkeys generated from the JSON held in the values column.
linkedentitykeysmediumtextHolds the list of linkedentitykeys generated from the JSON held in the values column. These will be the entities to which this custom field is linked for custom fields of type CLIENT_LINK, GROUP_LINK or USER_LINK.
parentkeyvarchar(32)The encodedkey of the entity holding the custom values within the values JSON column. This will be the encodedkey of the entity with which these custom field values are associated.
valuesjsonHolds all the custom field data in a JSON structure, including keys, IDs, values and indexes.



usermanagedbranch

entity class for holding information about user’s managed branch. it keeps a relation of 1 to 1 => 1 user 1 branch. the user can manage multiple branches so it will have a collection of usermanagedbranch entities.
Column NameData TypeDescription
branchkeyvarchar(32)The key of the managed branch
encodedkeyvarchar(32)The encoded key for this database entry. This field is autogenerated and should not be changed.
indexinlistint(11)Index of the branch in the list of all branches with the same type; -1 means that this branch was never ordered by the application
managedbranches_encodedkey_ownvarchar(32)The key of the user



userpreferences

the preferences for a specific user, regarding the list column configurations (for clients, groups, loans, savings and transactions lists) and other configurable components
Column NameData TypeDescription
defaultactivitieslookupcolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing activites in the UI.
defaultclientcolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing clients in the UI.
defaultdashboardkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing the dashboard in the UI.
defaultdepositscollectioncolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing the deposits collection screen, availble under Deposit Transactions in the UI.
defaultgroupcolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing groups in the UI.
defaultinterestaccrualbreakdowncolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing interest accrual breakdowns in the UI.
defaultjournalentriescolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing general ledger journal entries in the UI.
defaultlineofcreditcolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing lines of credit in the UI.
defaultloancolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing loan accounts in the UI.
defaultloanrepaymentscollectioncolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing the loan repayments collection screen, available under Loan Transactions in the UI.
defaultnotificationmessagecolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing notifications in the UI.
defaultorganizationbranchesconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing the list of branches in the UI.
defaultorganizationcentresconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing the list of centres in the UI.
defaultorganizationusersconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing the list of users in the UI.
defaultrepaymentcolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing loan repayment schedules in the UI.
defaultrepaymentscollectioncolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing the repayments collection screen in the UI.
defaultsavingscolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing savings accounts in the UI.
defaultsavingstransactionslookupcolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing savings account transactions in the UI.
defaulttaskscolumnconfigurationvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing tasks in the UI.
defaulttransactionslookupcolumnconfigurationkeyvarchar(32)The key of an entry in the columnconfiguration table holding this user’s default settings for viewing transactions in the UI.
encodedkeyvarchar(32)A unique key for this set of defaults.
helpenabledbit(1)Whether or not help is enabled for this user.



webhooknotificationsettings

containing settings for web hook notifications
Column NameData TypeDescription
encodedkeyvarchar(32)The encoded key for this database entry. This field can be used with our API to get details on an individual notification.
lastmodifieddatedatetimeThe date on which the settings were last modified.
notificationstatevarchar(255)Whether the Webhook feature is ENABLED or NONE.



Was this article helpful?

ESC

Eddy, a generative AI, facilitating knowledge discovery through conversational intelligence