Database Schema

This document provides a comprehensive overview of the Rahat database schema, organized by modules and their relationships.

Overview

The Rahat database uses PostgreSQL as the primary database with Prisma as the ORM. The schema is organized into several modules:

• User Management - Authentication, roles, and user profiles
• Beneficiary Management - Beneficiary data and grouping
• Project Management - Project configuration and relationships
• Transaction Management - Vendor and transaction tracking
• Grievance System - Issue tracking and resolution
• Settings & Configuration - System-wide settings
• Offramp Services - Crypto-to-fiat conversion

Database Configuration

-- Database: PostgreSQL
-- ORM: Prisma
-- Binary Targets: native, linux-musl-openssl-3.0.x

Module: User Management

Core User Tables

tbl_users (User)

The central user table containing basic user information.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
name	String?	User's full name
gender	Gender enum	User's gender (MALE, FEMALE, OTHER, UNKNOWN)
email	String?	Email address
phone	String?	Phone number
wallet	String?	Wallet address
extras	JsonB?	Additional user data
notes	Text?	User notes
sessionId	String?	Current session identifier
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp
createdBy	String?	Creator reference
updatedBy	String?	Last updater reference

tbl_auth_roles (Role)

Defines system roles and permissions.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
name	String (Unique)	Role name
isSystem	Boolean	Whether it's a system role
expiry	DateTime?	Role expiration date
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
createdBy	Int?	Creator reference
updatedBy	Int?	Last updater reference

tbl_auth_permissions (Permission)

Defines specific permissions for roles.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
roleId	Int (FK)	Reference to Role
action	String	Permission action
subject	String	Permission subject
inverted	Boolean	Whether permission is inverted
conditions	JsonB?	Permission conditions
reason	Text?	Permission reason
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp

tbl_users_roles (UserRole)

Many-to-many relationship between users and roles.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
userId	Int (FK)	Reference to User
roleId	Int (FK)	Reference to Role
expiry	DateTime?	Role assignment expiration
createdAt	DateTime	Record creation timestamp
createdBy	Int?	Creator reference

Constraints:

• Unique constraint on [userId, roleId]

Authentication Tables

tbl_auth (Auth)

Stores authentication methods for users.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
userId	Int (FK)	Reference to User
service	Service enum	Authentication service type
serviceId	String	Service-specific identifier
details	JsonB?	Service-specific details
challenge	String?	Authentication challenge
falseAttempts	Int	Failed login attempts
isLocked	Boolean	Whether account is locked
lockedOnAt	DateTime?	Lock timestamp
lastLoginAt	DateTime?	Last successful login
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp

Constraints:

• Unique constraint on [service, serviceId]

tbl_auth_sessions (AuthSession)

Tracks user sessions.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
clientId	String	Client identifier
sessionId	String (UUID)	Unique session identifier
authId	Int (FK)	Reference to Auth
ip	String?	Client IP address
details	JsonB?	Session details
userAgent	String?	Client user agent
createdAt	DateTime	Record creation timestamp

Signup Management

tbl_users_signups (Signup)

Manages user registration requests.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
userIdentifier	String?	User identifier
data	JsonB?	Registration data
status	SignupStatus	Registration status
rejectedReason	String?	Rejection reason
approvedBy	Int (FK)?	Approver reference
approvedAt	DateTime?	Approval timestamp
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp

Enums

Gender

enum Gender {
  MALE
  FEMALE
  OTHER
  UNKNOWN
}

Service

enum Service {
  EMAIL
  PHONE
  WALLET
  GOOGLE
  APPLE
  FACEBOOK
  TWITTER
  GITHUB
  LINKEDIN
}

SignupStatus

enum SignupStatus {
  PENDING
  APPROVED
  FAILED
  REJECTED
}

Module: Beneficiary Management

Core Beneficiary Tables

tbl_beneficiaries (Beneficiary)

Central table for beneficiary information.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
gender	Gender	Beneficiary gender
walletAddress	String (Unique)	Blockchain wallet address
birthDate	DateTime?	Date of birth
age	Int?	Calculated age
location	String?	Geographic location
latitude	Float?	GPS latitude
longitude	Float?	GPS longitude
extras	JsonB?	Additional beneficiary data
notes	String?	Beneficiary notes
bankedStatus	BankedStatus	Banking status
internetStatus	InternetStatus	Internet access status
phoneStatus	PhoneStatus	Phone ownership status
isVerified	Boolean	Verification status
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

tbl_beneficiaries_pii (BeneficiaryPii)

Stores personally identifiable information for beneficiaries.

Field	Type	Description
beneficiaryId	Int (FK, Unique)	Reference to Beneficiary
name	String?	Full name
phone	String (Unique)	Phone number
email	String?	Email address
extras	JsonB?	Additional PII data

Beneficiary Grouping

tbl_beneficiaries_group (BeneficiaryGroup)

Defines beneficiary groups for organizational purposes.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
name	String (Unique)	Group name
groupPurpose	GroupPurpose?	Purpose of the group
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

tbl_grouped_beneficiaries (GroupedBeneficiaries)

Many-to-many relationship between beneficiaries and groups.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
beneficiaryGroupId	String (UUID, FK)	Reference to BeneficiaryGroup
beneficiaryId	String (UUID, FK)	Reference to Beneficiary
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

Constraints:

• Unique constraint on [beneficiaryGroupId, beneficiaryId]

Project Relationships

tbl_beneficiaries_projects (BeneficiaryProject)

Links beneficiaries to projects.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
projectId	String (UUID, FK)	Reference to Project
beneficiaryId	String (UUID, FK)	Reference to Beneficiary
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

Constraints:

• Unique constraint on [projectId, beneficiaryId]

tbl_beneficiaries_gorup_projects (BeneficiaryGroupProject)

Links beneficiary groups to projects.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
projectId	String (UUID, FK)	Reference to Project
beneficiaryGroupId	String (UUID, FK)	Reference to BeneficiaryGroup
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

Constraints:

• Unique constraint on [projectId, beneficiaryGroupId]

Enums

BankedStatus

enum BankedStatus {
  UNKNOWN
  UNBANKED
  BANKED
  UNDER_BANKED
}

InternetStatus

enum InternetStatus {
  UNKNOWN
  NO_INTERNET
  HOME_INTERNET
  MOBILE_INTERNET
}

PhoneStatus

enum PhoneStatus {
  UNKNOWN
  NO_PHONE
  FEATURE_PHONE
  SMART_PHONE
}

GroupPurpose

enum GroupPurpose {
  BANK_TRANSFER
  MOBILE_MONEY
  COMMUNICATION
}

Module: Project Management

Core Project Table

tbl_projects (Project)

Defines projects in the Rahat ecosystem.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
name	String	Project name
description	String?	Project description
status	ProjectStatus	Project status
type	String	Project type
contractAddress	String?	Smart contract address
extras	JsonB?	Additional project data
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

Enums

ProjectStatus

enum ProjectStatus {
  NOT_READY
  ACTIVE
  CLOSED
}

Module: Transaction Management

Vendor Management

tbl_vendors (Vendors)

Stores vendor information.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
name	String	Vendor name
email	String?	Vendor email
phone	String?	Vendor phone
location	String?	Vendor location
wallet	String?	Vendor wallet address
extras	JsonB?	Additional vendor data
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

tbl_projects_vendors (ProjectVendors)

Links vendors to projects.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
projectId	String (UUID, FK)	Reference to Project
vendorId	String (FK)	Reference to User (vendor)
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp
extras	JsonB?	Additional relationship data

Constraints:

• Unique constraint on [projectId, vendorId]

Transaction Tracking

tbl_transactions (Transactions)

Tracks financial transactions.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
projectId	String (UUID)	Reference to Project
beneficiaryId	String (UUID)	Reference to Beneficiary
vendorId	String (UUID)	Reference to Vendor
amount	Float	Transaction amount
currency	String	Currency code
status	TransactionStatus	Transaction status
extras	JsonB?	Additional transaction data
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

Enums

TransactionStatus

enum TransactionStatus {
  PENDING
  APPROVED
  REJECTED
  FAILED
}

Module: Grievance System

Grievance Management

tbl_grievances (Grievance)

Tracks user grievances and support requests.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
reportedBy	String	Reporter name
reporterUserId	Int (FK)	Reference to User
reporterContact	String	Reporter contact information
title	String	Grievance title
type	GrievanceType	Type of grievance
projectId	String (UUID, FK)	Reference to Project
description	String	Detailed description
status	GrievanceStatus	Current status
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

Enums

GrievanceStatus

enum GrievanceStatus {
  NEW
  UNDER_REVIEW
  RESOLVED
  CLOSED
}

GrievanceType

enum GrievanceType {
  TECHNICAL
  NON_TECHNICAL
  OTHER
}

Module: Settings & Configuration

System Settings

tbl_settings (Setting)

Stores system-wide configuration settings.

Field	Type	Description
name	String (PK)	Setting name
value	Json	Setting value
dataType	SettingDataType	Data type of the value
requiredFields	String[]	Required field names
isReadOnly	Boolean	Whether setting is read-only
isPrivate	Boolean	Whether setting is private

Authentication Apps

tbl_auth_apps (AuthApp)

Stores authentication application configurations.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
address	String (Unique)	App address
name	String	App name
description	String?	App description
nonceMessage	String?	Nonce message for authentication
createdBy	String?	Creator reference
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

Enums

SettingDataType

enum SettingDataType {
  STRING
  NUMBER
  BOOLEAN
  OBJECT
}

TokenDataType

enum TokenDataType {
  IMPORTED
  CREATED
}

Module: Statistics

Statistics Storage

tbl_stats (Stats)

Stores system statistics and metrics.

Field	Type	Description
name	String (PK)	Statistic name
data	Json	Statistic data
group	String?	Statistic group
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp

Module: Token Management

Token Configuration

tbl_tokens (Token)

Stores token configurations for different blockchains.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
name	String	Token name
symbol	String (Unique)	Token symbol
description	String?	Token description
decimals	Int	Token decimal places
initialSupply	Int	Initial token supply
fromBlock	Int (Unique)	Starting block number
contractAddress	String (Unique)	Smart contract address
transactionHash	String (Unique)?	Deployment transaction hash
type	TokenDataType	Token type
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
createdBy	Int?	Creator reference
updatedBy	Int?	Last updater reference

Module: Offramp Services

Offramp Provider Management

tbl_offramp_providers (OfframpProvider)

Stores offramp service provider configurations.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
name	String (Unique)	Provider name
description	String?	Provider description
config	JsonB	Provider configuration
extras	JsonB?	Additional provider data
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp
createdBy	Int?	Creator reference
updatedBy	Int?	Last updater reference

Offramp Request Management

tbl_offramps (OfframpRequest)

Tracks offramp conversion requests.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
chain	String	Blockchain network
token	String	Token symbol
senderAddress	String	Sender wallet address
amount	Float	Conversion amount
requestId	String (Unique)?	External request ID
escrowAddress	String?	Escrow contract address
status	OfframpStatus	Request status
extras	JsonB?	Additional request data
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp
createdBy	Int?	Creator reference
updatedBy	Int?	Last updater reference

tbl_offramp_transactions (OfframpTransaction)

Tracks offramp transaction details.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
requestId	String (Unique, FK)	Reference to OfframpRequest
txHash	String	Transaction hash
walletId	String?	Wallet identifier
customerKey	String?	Customer key
chain	String?	Blockchain network
token	String?	Token symbol
referenceId	String?	External reference ID
status	OfframpStatus	Transaction status
extras	JsonB?	Additional transaction data
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp
createdBy	Int?	Creator reference
updatedBy	Int?	Last updater reference

Enums

OfframpStatus

enum OfframpStatus {
  PENDING
  PROCESSING
  COMPLETED
  FAILED
}

Temporary Storage Tables

Temporary Beneficiary Management

tbl_temp_beneficiaries (TempBeneficiary)

Temporary storage for beneficiary data during import processes.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
firstName	String	First name
lastName	String	Last name
govtIDNumber	String?	Government ID number
gender	Gender	Gender
birthDate	DateTime?	Date of birth
walletAddress	String	Wallet address
phone	String?	Phone number
email	String?	Email address
archived	Boolean	Archive status
location	String?	Location
latitude	Float?	GPS latitude
longitude	Float?	GPS longitude
notes	String?	Notes
bankedStatus	BankedStatus	Banking status
internetStatus	InternetStatus	Internet status
phoneStatus	PhoneStatus	Phone status
extras	JsonB?	Additional data
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

tbl_temp_group (TempGroup)

Temporary storage for beneficiary groups.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
name	String (Unique)	Group name
createdAt	DateTime	Record creation timestamp

tbl_temp_beneficiary_group (TempBeneficiaryGroup)

Temporary many-to-many relationship between temp beneficiaries and groups.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
tempGroupUID	String (UUID, FK)	Reference to TempGroup
tempBenefUID	String (UUID, FK)	Reference to TempBeneficiary
createdAt	DateTime	Record creation timestamp

Constraints:

• Unique constraint on [tempGroupUID, tempBenefUID]

Kobo Integration

tbl_kobo_beneficiaries (KoboBeneficiary)

Stores beneficiary data from Kobo form submissions.

Field	Type	Description
id	Int (PK)	Auto-incrementing primary key
uuid	String (UUID)	Unique identifier
name	String	Beneficiary name
gender	Gender	Gender
type	KoboBeneficiaryType	Beneficiary type
status	KoboBeneficiaryStatus	Processing status
phone	String	Phone number
age	Int?	Age
email	String?	Email address
wallet	String?	Wallet address
leadInterests	String[]	Lead interests
extras	JsonB?	Additional data
createdAt	DateTime	Record creation timestamp
updatedAt	DateTime?	Last update timestamp
deletedAt	DateTime?	Soft delete timestamp

Enums

KoboBeneficiaryType

enum KoboBeneficiaryType {
  SALE
  LEAD
  HOME_VISIT
}

KoboBeneficiaryStatus

enum KoboBeneficiaryStatus {
  PENDING
  FAILED
  SUCCESS
}

Database Relationships

Key Relationships

1. User Management

   • User ↔ Auth (One-to-Many)
   • User ↔ UserRole (One-to-Many)
   • Role ↔ Permission (One-to-Many)
   • User ↔ Signup (One-to-Many)

2. Beneficiary Management

   • Beneficiary ↔ BeneficiaryPii (One-to-One)
   • Beneficiary ↔ BeneficiaryProject (One-to-Many)
   • BeneficiaryGroup ↔ GroupedBeneficiaries (One-to-Many)
   • Project ↔ BeneficiaryProject (One-to-Many)

3. Project Management

   • Project ↔ ProjectVendors (One-to-Many)
   • Project ↔ BeneficiaryGroupProject (One-to-Many)

4. Transaction Management

   • Project ↔ Transactions (One-to-Many)
   • Vendors ↔ ProjectVendors (One-to-Many)

5. Grievance System

   • User ↔ Grievance (One-to-Many)
   • Project ↔ Grievance (One-to-Many)

6. Offramp Services

   • OfframpRequest ↔ OfframpTransaction (One-to-One)

Indexes

The database includes several indexes for performance optimization:

• Soft Delete Indexes: Most tables have indexes on deletedAt for efficient soft delete queries
• Foreign Key Indexes: All foreign key relationships are indexed
• Unique Constraints: Enforced through database constraints
• Composite Indexes: For many-to-many relationships

Data Types

PostgreSQL Specific Types

• JsonB: Used for flexible JSON data storage
• UUID: For unique identifiers
• Text: For large text fields
• VarChar: For string fields with length limits

Prisma Types

• Int: Integer fields
• String: String fields
• Float: Decimal numbers
• Boolean: Boolean values
• DateTime: Timestamp fields
• Json: JSON data (stored as JsonB in PostgreSQL)

Best Practices

1. Soft Deletes: Most tables implement soft delete functionality using deletedAt timestamps
2. Audit Trails: Tables include createdAt, updatedAt, createdBy, and updatedBy fields
3. UUID Usage: Primary identifiers use UUIDs for security and scalability
4. JSON Flexibility: Extras fields use JsonB for flexible data storage
5. Enum Constraints: Database enums ensure data integrity
6. Indexing Strategy: Strategic indexing for query performance

Migration Notes

When working with this schema:

1. Prisma Migrations: Use prisma migrate commands for schema changes
2. Data Seeding: Use Prisma seed scripts for initial data
3. Environment Variables: Ensure DATABASE_URL is properly configured
4. Binary Targets: Schema supports multiple deployment targets
5. Backup Strategy: Regular backups recommended for production deployments