Database Schema

The UBU Digital Finance Solution uses PostgreSQL as its primary database. The schema is designed to support all the system's functionality while maintaining data integrity and performance.

Entity Relationship Diagram

Below is a simplified entity relationship diagram showing the main tables and their relationships:

┌─────────────────┐       ┌─────────────────┐       ┌─────────────────┐
│     Users       │       │     Roles       │       │   Permissions   │
├─────────────────┤       ├─────────────────┤       ├─────────────────┤
│ user_id         │       │ role_id         │       │ permission_id   │
│ user_code       │       │ role_name       │       │ permision_key   │
│ user_fullname   │       │ role_description│       │ permission_name │
│ user_email      │       │ role_is_active  │       │ permission_desc │
│ user_phonenumber│       └────────┬────────┘       └────────┬────────┘
│ user_password   │                │                         │
│ is_active       │                │                         │
│ is_two_factor_  │                │                         │
│ auth            │                │                         │
│ otp             │                │                         │
│ otp_expiry      │                │                         │
└────────┬────────┘                │                         │
         │                         │                         │
         │                ┌────────┴────────┐                │
         │                │   User_Roles    │                │
         └────────────────┤                 │                │
                          │ user_id         │                │
                          │ role_id         │                │
                          │ role_is_active  │                │
                          └────────┬────────┘                │
                                   │                         │
                                   │                         │
                                   │      ┌─────────────────┐│
                                   └──────┤ Role_Permissions││
                                          │                 ├┘
                                          │ role_id         │
                                          │ permission_id   │
                                          └─────────────────┘

┌─────────────────┐       ┌─────────────────┐
│ Organizational  │       │ User_Org_Units  │
│     Units       │       │                 │
├─────────────────┤       ├─────────────────┤
│ unit_id         │       │ user_id         │
│ unit_name       │       │ unit_id         │
│ unit_description│       │ is_active       │
│ parent_unit_id  │       └─────────────────┘
│ is_active       │                
└────────┬────────┘                
         │                         
         └────────────────┐        
                          │        
                          │        
                          │        
                          │        

Table Descriptions

User Management Tables

Users

Stores user account information.

Column	Type	Description
user_id	UUID	Primary key
user_code	VARCHAR	Unique user code for identification
user_fullname	VARCHAR	User's full name
user_email	VARCHAR	User's email address (unique)
user_phonenumber	VARCHAR	User's phone number (unique)
user_password	VARCHAR	Hashed password
is_active	BOOLEAN	Whether the user account is active
is_two_factor_auth	BOOLEAN	Whether two-factor authentication is enabled
otp	VARCHAR	One-time password for 2FA
otp_expiry	TIMESTAMP	Expiration time for OTP

Roles

Defines roles that can be assigned to users.

Column	Type	Description
role_id	UUID	Primary key
role_name	VARCHAR	Name of the role
role_description	VARCHAR	Description of the role
role_is_active	BOOLEAN	Whether the role is active

Permissions

Defines permissions that can be assigned to roles.

Column	Type	Description
permission_id	UUID	Primary key
permision_key	VARCHAR	Unique key for the permission
permission_name	VARCHAR	Name of the permission
permission_desc	VARCHAR	Description of the permission

User_Roles

Maps users to roles (many-to-many relationship).

Column	Type	Description
user_id	UUID	Foreign key to Users table
role_id	UUID	Foreign key to Roles table
role_is_active	BOOLEAN	Whether the role is active for this user

Role_Permissions

Maps roles to permissions (many-to-many relationship).

Column	Type	Description
role_id	UUID	Foreign key to Roles table
permission_id	UUID	Foreign key to Permissions table

Organizational Structure Tables

Organizational_Units

Defines the organizational structure.

Column	Type	Description
unit_id	UUID	Primary key
unit_name	VARCHAR	Name of the organizational unit
unit_description	VARCHAR	Description of the unit
parent_unit_id	UUID	Foreign key to parent unit (self-referencing)
is_active	BOOLEAN	Whether the unit is active

User_Organizational_Units

Maps users to organizational units.

Column	Type	Description
user_id	UUID	Foreign key to Users table
unit_id	UUID	Foreign key to Organizational_Units table
is_active	BOOLEAN	Whether the assignment is active

Session Management Tables

Access_Sessions

Tracks user login sessions.

Column	Type	Description
session_id	UUID	Primary key
user_id	UUID	Foreign key to Users table
refresh_token	VARCHAR	Refresh token for the session
ip_address	VARCHAR	IP address of the client
user_agent	VARCHAR	User agent of the client
created_at	TIMESTAMP	When the session was created
expires_at	TIMESTAMP	When the session expires
is_active	BOOLEAN	Whether the session is active

Planned Tables (To Be Implemented)

Transaction Management Tables

Accounts

Will store financial account information.

Column	Type	Description
account_id	UUID	Primary key
account_number	VARCHAR	Unique account number
account_type	VARCHAR	Type of account
customer_id	UUID	Foreign key to Users table
balance	DECIMAL	Current account balance
currency	VARCHAR	Account currency
status	VARCHAR	Account status
created_at	TIMESTAMP	When the account was created
updated_at	TIMESTAMP	When the account was last updated

Transactions

Will store financial transaction information.

Column	Type	Description
transaction_id	UUID	Primary key
source_account_id	UUID	Source account (foreign key)
destination_account_id	UUID	Destination account (foreign key)
amount	DECIMAL	Transaction amount
currency	VARCHAR	Transaction currency
transaction_type	VARCHAR	Type of transaction
status	VARCHAR	Transaction status
reference	VARCHAR	Transaction reference
description	VARCHAR	Transaction description
created_at	TIMESTAMP	When the transaction was created
updated_at	TIMESTAMP	When the transaction was last updated

Loan Management Tables

Loans

Will store loan information.

Column	Type	Description
loan_id	UUID	Primary key
customer_id	UUID	Foreign key to Users table
amount	DECIMAL	Loan amount
interest_rate	DECIMAL	Interest rate
term_months	INTEGER	Loan term in months
status	VARCHAR	Loan status
created_at	TIMESTAMP	When the loan was created
approved_at	TIMESTAMP	When the loan was approved
disbursed_at	TIMESTAMP	When the loan was disbursed

Loan_Payments

Will store loan payment information.

Column	Type	Description
payment_id	UUID	Primary key
loan_id	UUID	Foreign key to Loans table
amount	DECIMAL	Payment amount
payment_date	TIMESTAMP	When the payment was made
status	VARCHAR	Payment status

Payment Management Tables

Payments

Will store payment information.

Column	Type	Description
payment_id	UUID	Primary key
payer_id	UUID	Foreign key to Users table
payee_id	UUID	Foreign key to Users table
amount	DECIMAL	Payment amount
currency	VARCHAR	Payment currency
payment_method	VARCHAR	Method of payment
status	VARCHAR	Payment status
reference	VARCHAR	Payment reference
created_at	TIMESTAMP	When the payment was created
processed_at	TIMESTAMP	When the payment was processed