Database Schema

Common Fields

Nearly every table in the schema shares a set of standard fields. These are documented once here and omitted from individual table descriptions to avoid repetition.

Audit Fields (present on all tables)

Multi-Tenant Isolation

Maker-Checker Fields (on workflow-enabled entities)

Entities that go through an approval workflow carry these additional fields:

The workflow follows: CAPTURED -> PENDING_AUTH_L3/L2/L1 (depending on required approval levels) -> AUTHORIZED, or REJECTED / DENIED.

---

A. System Infrastructure

CustomFieldDefinition

Table: remoteEaze_custom_field_definitions Purpose: Defines dynamic (tenant-configurable) columns that can be attached to any entity type. Each definition specifies the field key, label, data type, validation rules, default value strategy, and UI rendering config.

Unique constraint: [tenantId, entityType, fieldKey]

ActivityLog

Table: remoteEaze_activity_logs Purpose: Immutable audit trail capturing the "Who, What, Where, When, and Why" of every significant action. Uses loose references (no FKs) so audit history survives entity deletion.

Indexes: Optimized for tenant+date range, action filtering, entity history, user timeline, trace correlation, status analysis, and tag/custom field queries (GIN).

Sequence

Table: remoteEaze_sequences Purpose: Atomic auto-incrementing sequence generator used to produce business keys (customer numbers, staff numbers, agent numbers, account numbers). Supports configurable prefix, zero-padding, year inclusion, and yearly reset.

Unique constraint: [tenantId, name]

---

B. Tenant & Organization

License

Table: remoteEaze_licenses Purpose: The root tenant/organization record. Each License represents one organization using the system. Contains company information, contract details, financial configuration, password policies, and session policies.

Branch

Table: remoteEaze_branches Purpose: Organizational branches with parent-child hierarchy. Each branch has an auto-generated code and belongs to a nation and currency. On authorization, ProductBalance rows are created for all applicable products.

Unique constraint: [tenantId, code]

Maker-Checker: Yes.

Department

Table: remoteEaze_departments Purpose: Organizational departments/units with hierarchy. Used for access control (deptUnitRestrict on Product) and user assignment.

Unique constraint: [tenantId, code]

CostCentre

Table: remoteEaze_cost_centres Purpose: Cost allocation centres for financial reporting and user assignment.

Unique constraint: [tenantId, code]

TenantCurrency

Table: remoteEaze_tenant_currencies Purpose: Currencies enabled per tenant with foreign exchange rates. One currency per tenant is marked as the base currency.

Unique constraint: [tenantId, currencyCode]

---

C. Authentication & Access Control

RemoteEaze_userProfile

Table: remoteEaze_userProfile Purpose: User accounts. Integrates with Better-Auth for authentication while carrying extensive business fields for the financial system. Users can be INTERNAL (staff), EXTERNAL (field agents), or SYSTEM (super admins).

Unique constraints: [email], [tenantId, staffNumber]

RemoteEaze_userSession

Table: remoteEaze_userSession Purpose: Active user sessions managed by Better-Auth. Stores session tokens, expiry, and client context (IP, user agent).

RemoteEaze_authAccount

Table: remoteEaze_authAccount Purpose: Authentication provider accounts (Better-Auth). Links users to credential or OAuth providers with access/refresh tokens.

RemoteEaze_accountVerification

Table: remoteEaze_accountVerification Purpose: Email/phone verification tokens with expiry. Used during account verification flows.

Role

Table: remoteEaze_roles Purpose: Named roles per tenant used for RBAC. Each role has associated permissions, transaction approval thresholds, and approval rules.

Unique constraints: [tenantId, slug], [tenantId, name]

Maker-Checker: Yes.

Relations: users (RemoteEaze_userProfile[]), permissions (PermissionMatrix[]), thresholds (TransactionApproval[]), permissionRules (TransactionApprovalRule[]).

PermissionMatrix

Table: remoteEaze_permission_matrix Purpose: Maps a Role to an entity type (e.g. "transfer", "customer"), grouping the granular permission actions for that combination.

Unique constraint: [tenantId, roleId, entityType]

Relations: actions (PermissionAction[]).

PermissionAction

Table: remoteEaze_permission_actions Purpose: Individual permission flags within a PermissionMatrix. Each action (read, write, approve_l1, etc.) can be allowed or denied, optionally with a required approval level.

Unique constraint: [permissionMatrixId, action]

TransactionApproval

Table: remoteEaze_transaction_approvals Purpose: Amount-based approval thresholds per role. Defines what a role can create or approve based on transaction amount ranges and currency.

TransactionApprovalRule

Table: remoteEaze_transaction_approval_rules Purpose: Conditional rules that override or extend base approval thresholds. Two types: PERMISSION (role-specific conditional overrides) and VALIDATION (global blocking rules that apply to everyone).

Relations: conditions (TransactionApprovalCondition[]).

TransactionApprovalCondition

Table: remoteEaze_transaction_approval_conditions Purpose: Individual conditions within an approval rule. Uses field-level operators to evaluate transaction data.

---

D. Global Reference Data

These tables have no tenantId. They are system-wide, read-only for tenants, and typically seeded from ISO standards.

Nation

Table: remoteEaze_nations Purpose: ISO 3166 country reference. Used for branch location, customer nationality/residence, and identity type scoping.

Relations: branches, sectors, identityTypes.

Language

Table: remoteEaze_languages Purpose: ISO 639 language reference. Used for user language preferences.

Currency

Table: remoteEaze_currencies Purpose: ISO 4217 currency reference. Critical for banking math -- the decimals field determines rounding precision across the system.

Relations: tenantConfigs, branches, accountConditions, commissionTypes, commissionBands, accounts, facilities, facilityCollaterals, collateralValuations.

Sector

Table: remoteEaze_sectors Purpose: Economic sectors per country, defined by regulators. Used for customer and account classification (regulatory reporting).

Unique constraint: [countryCode, code]

Relations: industries, customers, accounts.

Industry

Table: remoteEaze_industries Purpose: Industries within sectors. Finer-grained classification for regulatory reporting.

Unique constraint: [sectorId, code]

Relations: customers, accounts.

---

E. Tenant Reference Data (Lookup Tables)

These are simple code + description lookup tables scoped by tenantId. They power dropdown menus across the UI and enforce consistent categorization. All follow the same pattern:

Unique constraint (all): [tenantId, code]

Complex Configuration Tables

Simple Lookup Tables

---

F. Entity Onboarding (CRM)

Customer

Table: remoteEaze_customers Purpose: Core customer entity supporting both Individual and Corporate customer types. Carries demographics, contact info, KYC data, compliance fields, and complex JSON fields for identities, directors, shareholders, and documents.

Individual-specific fields: firstName, surname, titleCode, genderCode, maritalStatusCode, dateOfBirth, placeOfBirth, countryOfBirth.

Corporate-specific fields: corporateName, dateOfIncorporation, placeOfIncorporation, countryOfIncorporation.

Creation rules:

• At least one entry in identities is always required on creation (both Individual and Corporate).
• For Corporate: at least one Director, one Shareholder, and one Contact Person are required.
• Total shareholder percentage across all shareholders cannot exceed 100%.
• Identity ref is validated against the formatRegex defined in the matching IdentityType record.
• customerTypeId cannot be changed after creation.

Contact: email, phone, addressPhysical, addressHome, addressPostal.

Classification: nationality, residence, sectorId, industryId, customerRatingCode.

Relationships: relationOfficerId (FK to User), agentId (FK to Agent), branchId (FK to Branch).

Complex JSON fields:

• identities -- Array: [{ typeCode, ref, issueDate, expiryDate }]
• directors -- Array (corporate): [{ name, address, email, phone, idTypeCode, idRef, memo }]
• shareholders -- Array (corporate): [{ name, address, email, phone, idTypeCode, idRef, sharePercentage }]
• contacts -- Array (corporate): [{ name, email, phone, role }]
• nextOfKin -- Array: [{ name, address, relationship, idTypeCode, idRef, idIssueDate, idExpiryDate }]
• documents -- Array: [{ typeCode, ref, imageUrl, uploadDate, expiryDate }]

Credit: totalCreditLimit (Decimal) -- Max total facility exposure allowed for this customer.

Dual status:

• recordStatus (RecordStatus) -- Maker-checker workflow state.
• customerStatus (CustomerStatus) -- Operational state: ACTIVE, PENDING_KYC, BLOCKED, DECEASED.
• statusMemo / statusDate -- Reason and timestamp for last customerStatus change.

Relations: accounts, mandates (as signatory), facilities.

Maker-Checker: Yes.

Agent

Table: remoteEaze_agents Purpose: Field agents who operate on behalf of the organization, typically for mobile banking or outreach. Each agent is linked to exactly one user account and one branch. Personal information (name, email, phone) comes from the linked User record (single source of truth).

Dual status:

• recordStatus (RecordStatus) — Maker-checker workflow state.
• agentStatus (AgentStatus) — Operational state: ACTIVE, SUSPENDED, TERMINATED.

Working config updates: An AUTHORIZED agent's working hours and geo-fencing can be updated directly (no maker-checker cycle) via the updateConfig action.

Relations: customers (managed), managedAccounts.

Maker-Checker: Yes.

---

G. Financial Engine (General Ledger)

Product

Table: remoteEaze_products Purpose: Chart of Accounts (GL codes). Each product represents a general ledger account category. Products are either customer-facing contracts (savings, loans) or internal GL accounts (expense, income tracking).

Relations: balances (ProductBalance[]), accounts, facilityClassProducts, activityRules, and various GL account link relations from AccountCondition and CommissionType.

Maker-Checker: Yes.

ProductBalance

Table: remoteEaze_product_balances Purpose: System-managed ledger balances per product per branch. Tracks Year-to-Date, Month-to-Date, and Day-level open balances and debit/credit movements. Created automatically when a Product is authorized or a new Branch is created.

Unique constraint: [tenantId, productId, branchId]

Not user-editable. Read-only via API. Updated by the transaction posting engine and batch jobs.

TransCode

Table: remoteEaze_trans_codes Purpose: Transaction codes that define posting behavior. Referenced by CommissionType to link fee types to their transaction posting rules.

Unique constraint: [tenantId, code]

Maker-Checker: Yes.

---

H. Product Rules Engine

AccountCondition (Interest Engine)

Table: remoteEaze_account_conditions Purpose: Interest calculation configuration with temporal versioning. Defines how debit interest (customer pays) and credit interest (customer earns) are calculated, including tiered rates, day-count conventions, frequency settings, GL account links, and tax configuration.

Unique constraint: [tenantId, code, effectiveFrom] (allows temporal versioning — multiple versions of the same code can coexist with different effective dates).

Supersede operation: Creates a new CAPTURED version of an existing open-ended AUTHORIZED condition. The new version's effectiveFrom must be at least tomorrow. The current version's effectiveTo is automatically set to the day before the new effectiveFrom. All product links from the current version are copied to the new version atomically.

Interest preview: AUTHORIZED conditions support a preview endpoint that calculates interest for a given balance and number of days, with optional tax calculation.

Maker-Checker: Yes. A condition code cannot be reused (same code + effectiveFrom combination). Use supersede instead of creating a new record with the same code.

ProductAccountCondition

Table: remoteEaze_product_account_conditions Purpose: Junction table linking Products to AccountConditions. Only CONTRACT products (customer-facing) can be linked. Unlinking is prevented if active AUTHORIZED accounts exist using that combination.

Unique constraint: [productId, accountConditionId]

CommissionType (Fee Engine)

Table: remoteEaze_commission_types Purpose: Fee and commission definitions. Each commission type has GL account links, a transaction code, tax configuration, and calculation settings. Supports FLAT, PERCENTAGE, and FORMULA calculation bases.

Maker-Checker: Yes. Must have at least one band before submission.

CommissionBand

Table: remoteEaze_commission_bands Purpose: Tiered fee brackets within a CommissionType. Defines amount ranges with either flat fees or percentage-based charges.

Unique constraint: [commissionTypeId, currencyCode, minAmount]

Bands are validated as a set: contiguous ranges, no gaps, no overlaps.

FacilityClass

Table: remoteEaze_facility_classes Purpose: Credit facility templates. Defines whether facilities are revolving (overdraft) or term (fixed loan), and what percentage of the limit is available for utilization.

Maker-Checker: Yes.

Relations: eligibleProducts (FacilityClassProduct[]), facilities (Facility[]).

FacilityClassProduct

Table: remoteEaze_facility_class_products Purpose: Junction table linking FacilityClasses to eligible Products. Only CONTRACT products can be linked. Unlinking prevented if active facilities exist.

Unique constraint: [facilityClassId, productId]

ActivityStatusRule

Table: remoteEaze_activity_status_rules Purpose: Lifecycle automation rules for account dormancy management. Defines conditions that trigger automatic status changes on accounts (e.g. mark as DORMANT after 90 days of inactivity). Includes cycle detection to prevent circular status transitions.

Maker-Checker: Yes.

WorkDayYear

Table: remoteEaze_work_day_years Purpose: Business calendar year definition. Tracks the current, last, and next working days. Used for interest calculations, business date determination, and transaction scheduling.

Unique constraint: [tenantId, year]

Maker-Checker: Yes.

WorkDayMonth

Table: remoteEaze_work_day_months Purpose: Monthly breakdown within a WorkDayYear. Defines which days are weekends and which are public holidays for each month.

Unique constraint: [workDayYearId, month]

Months can only be modified when the parent year is in CAPTURED or REJECTED status.

---

I. Account & Credit Management

Account

Table: remoteEaze_accounts Purpose: The core financial account entity. Can be customer-facing (savings, loans, fixed deposits) or internal GL accounts. Carries six balance fields, interest state, schedule state, activity tracking, and services/notifications configuration.

Contract product rules:

• isContractProduct = true on the product → either customerId or isAgentAccount = true is required (not both).
• isContractProduct = false (internal GL) → customerId and isAgentAccount must both be absent/false.

Operational status update: An AUTHORIZED account's accountStatusId can be changed directly (no maker-checker). Restrictive statuses (BLOCKED, CLOSED, DORMANT, FROZEN) require a statusMemo (min 5 characters).

Balance fields:

Interest state fields: lastDrInterestAccrDate, lastDrInterestCapDate, lastCrInterestAccrDate, lastCrInterestCapDate, lastDrInterestCapAmt, lastCrInterestCapAmt, currAccrDrInterAmt, currAccrCrInterAmt, plus YTD and grand-total interest/charge amounts.

Schedule state: scheduleDefined, scheduleFrequency, nextScheduleDate, nextScheduleAmount, lastScheduleDate, lastScheduleAmount. scheduleFrequency is required when scheduleDefined = true.

Activity tracking: lastTxnDate, lastCreditDate, lastDebitDate (used by ActivityStatusRule evaluation).

Services: subscribedServices (DEBITCARD, CREDITCARD, POS, INTERNET), notificationServices (EMAIL, SMS).

Maker-Checker: Yes.

AccountMandate

Table: remoteEaze_account_mandates Purpose: Signing authority records per account. Defines who is authorized to sign for transactions, their transaction limits, and validity period. SigningRule (how mandates must be collectively fulfilled) lives on the Account, not the mandate.

Rules:

• Can only be created on AUTHORIZED accounts.
• If signatoryCustomerId is provided, the customer must exist in the same tenant.
• The signingRule for collective authorization is set on the Account (Account.signingRule), not here.

Auto-creation: Two paths create a PRIMARY_HOLDER mandate automatically:

1. Individual customer account reaches AUTHORIZED — mandate is created with the customer's displayName, contact details, and first identity document, atomically in the same transaction.
2. Agent's account is linked via linkAccount — mandate is created using the agent's User record (user.name, user.email, user.userPhoneOfficial). No signatoryCustomerId is set for agent mandates.

AccountStatement

Table: remoteEaze_account_statements Purpose: Statement delivery configuration per account. Defines how often statements are generated and how they are delivered. Tracks the last generation date and balance.

Rules: Can only be created for AUTHORIZED accounts. An account can have one statement configuration.

Facility

Table: remoteEaze_facilities Purpose: Credit facilities (overdraft lines or term loans) granted to customers. Each facility is based on a FacilityClass template, has an approved amount, and tracks expiry/review dates.

Dual status: recordStatus (workflow) + facilityStatus (ACTIVE, EXPIRED, SUSPENDED, CANCELLED).

Maker-Checker: Yes. dateApproved set automatically on AUTHORIZED.

FacilityCollateral

Table: remoteEaze_facility_collaterals Purpose: Collateral pledged against a facility. Carries current valuation and links to a CollateralType.

CollateralValuation

Table: remoteEaze_collateral_valuations Purpose: Immutable valuation history for collateral. Each record represents one professional valuation. On creation, the parent FacilityCollateral is updated with the latest values atomically.

Immutable: No updates. Corrections via soft delete + new record.

FacilityAccount

Table: remoteEaze_facility_accounts Purpose: Junction table tracking which accounts draw from which facilities and how much. Enforces allocation limits: total allocation across all linked accounts cannot exceed the facility's amountAdvised.

Unique constraint: [facilityId, accountId]

---

J. Third-Party Delivery

ThirdPartySystem

Table: remoteEaze_third_party_systems Purpose: Registry of external systems (core banking, ERPs, payment gateways) that the platform can deliver data to. Stores connection details, authentication credentials (encrypted at rest), and default retry/timeout behaviour. Uses maker-checker workflow.

Unique constraint: [tenantId, code]

ThirdPartyMapping

Table: remoteEaze_third_party_mappings Purpose: Junction table mapping internal entity records to their identifiers in external systems. For example, a customer cust_abc123 might be CUS001 in T24. Simple CRUD — no maker-checker workflow.

Unique constraint: [tenantId, entityType, entityId, systemId, externalKey]

DeliveryHookConfig

Table: remoteEaze_delivery_hook_configs Purpose: Rules defining when and how to deliver data to external systems. Each config ties an entity type + event to a system with field mappings that transform the payload. Uses maker-checker workflow. Only AUTHORIZED + active configs trigger deliveries.

Unique constraint: [tenantId, systemId, entityType, event]

DeliveryLog

Table: remoteEaze_delivery_logs Purpose: Immutable audit trail of every delivery attempt. Written exclusively by the queue processor — never directly via API. Each retry creates a new log entry, so you can trace the full retry history.

---

Enums Reference

Workflow & Status

User & Access

Financial

Frequency

Branch & Organization

Third-Party Delivery

Custom Fields

Rules & Conditions

Account & Facility