Database Schema

Overview

NetPOS uses Room Persistence Library to manage local SQLite database operations. The database stores transaction history, MQTT events, and payment tracking data.

Database Configuration

database/AppDatabase.kt:19

@Database(
    entities = [
        TransactionResponse::class,
        MqttEventsLocal::class,
        TransactionResponseXForTracking::class,
        GetZenithPayByTransferUserTransactionsModel::class
    ],
    version = 18,
    exportSchema = false
)
@TypeConverters(RoomTypeConverters::class)
abstract class AppDatabase : RoomDatabase() {
    abstract fun transactionTrackingTableDao(): TransactionTrackingTableDao
    abstract fun transactionResponseDao(): TransactionResponseDao
    abstract fun mqttLocalDao(): MqttLocalDao
    abstract fun getZenithPayByTransferDao(): ZenithPayByTransferUserTransactionsDao
}

Current database version is 18, using destructive migration strategy for schema updates.

Database Instantiation

database/AppDatabase.kt:38

companion object {
    @Volatile
    private var INSTANCE: AppDatabase? = null

    fun getDatabaseInstance(context: Context) = INSTANCE ?: synchronized(this) {
        INSTANCE ?: buildDatabase(context).also { INSTANCE = it }
    }

    private fun buildDatabase(context: Context) =
        Room.databaseBuilder(context, AppDatabase::class.java, APP_DB_NAME)
            .fallbackToDestructiveMigration()
            .build()
}

Database name is defined in AppConstants.APP_DB_NAME as “netpos-db”

Entities

1. TransactionResponse

Stores all card transaction records from NIBSS EPMS library.

TransactionResponse Fields

@Entity(tableName = "transactionresponse")
data class TransactionResponse(
    @PrimaryKey(autoGenerate = true) val id: Long,
    val terminalId: String,
    val amount: Long,
    val transactionType: TransactionType,
    val responseCode: String,
    val responseMessage: String,
    val maskedPan: String?,
    val cardExpiry: String?,
    val cardHolder: String?,
    val cardLabel: String?,
    val stan: String?,
    val rrn: String?,
    val authCode: String?,
    val transactionTimeInMillis: Long,
    val localDate_13: String,
    val accountType: IsoAccountType
)

Key Fields:

terminalId: Unique terminal identifier
transactionType: PURCHASE, REFUND, PRE_AUTHORIZATION, etc.
responseCode: ISO 8583 response code (“00” = approved)
transactionTimeInMillis: Unix timestamp
rrn: Retrieval Reference Number
stan: System Trace Audit Number

2. MqttEventsLocal

Stores failed MQTT event publish attempts for retry.

model/MqttEvent.kt:44

@Entity(tableName = "mqttEvents")
data class MqttEventsLocal(
    val topic: String,
    val data: String,
    val cause: String? = null
) {
    @PrimaryKey(autoGenerate = true) var id: Int = 0
}

MQTT Event Topics

enum class MqttTopics(val topic: String) {
    AUTHENTICATION("mqtt.pos.authentication.event"),
    TERMINAL_CONFIGURATION("mqtt.pos.terminal_config.event"),
    TRANSACTIONS("mqtt.pos.transaction.event"),
    PRINTING_RECEIPT("mqtt.pos.device.event"),
    NIP_PULL("mqtt.pos.bank_transfer.event"),
    NIP_NEW("mqtt.pos.generate_session_code.event"),
    NIP_SEARCH("mqtt.pos.verify_session_code.event"),
    CARD_READER_EVENTS("mqtt.pos.device.event"),
    POWER_EVENTS("mqtt.pos.device.event"),
    BATTERY_EVENTS("mqtt.pos.device.event"),
    SMS_EVENTS("mqtt.pos.sms.event")
}

3. TransactionResponseXForTracking

Extended transaction tracking entity with additional metadata.

4. GetZenithPayByTransferUserTransactionsModel

Stores Zenith Bank pay-by-transfer transaction history.

Data Access Objects (DAOs)

TransactionResponseDao

Manages all card transaction database operations.

@Dao
interface TransactionResponseDao {
    @Insert(onConflict = IGNORE)
    fun insertNewTransaction(
        transactionResponse: TransactionResponse
    ): Single<Long>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertNewTransaction(
        transactionResponses: List<TransactionResponse>
    ): Single<List<Long>>
}

The nukeAllTransactions() method permanently deletes all transaction records. Use with extreme caution.

MqttLocalDao

Manages MQTT event persistence for retry mechanism.

database/dao/MqttLocalDao.kt:11

@Dao
interface MqttLocalDao {
    @Insert
    fun insertNewTransaction(mqttEventsLocal: MqttEventsLocal): Single<Long>

    @Query("DELETE FROM mqttEvents")
    fun deleteAllEvents(): Completable

    @Query("SELECT * FROM mqttEvents")
    fun getLocalEvents(): Single<List<MqttEventsLocal>>

    @Query("SELECT COUNT(*) from mqttEvents")
    fun getNumberOfFailedTransactions(): Single<Long>
}

ZenithPayByTransferUserTransactionsDao

Manages Zenith pay-by-transfer transactions.

Type Converters

Room requires type converters for complex data types:

util/RoomTypeConverters.kt:9

class RoomTypeConverters {
    companion object {
        @TypeConverter
        fun convertIsoAccountEnumToString(isoAccountType: IsoAccountType) = 
            isoAccountType.name

        @TypeConverter
        fun convertIsoAccountStringToEnum(value: String) = 
            IsoAccountType.valueOf(value)

        @TypeConverter
        fun convertTransactionResponseEnumToString(transactionType: TransactionType) =
            transactionType.name

        @TypeConverter
        fun convertTransactionResponseToEnum(value: String) = 
            TransactionType.valueOf(value)

        @TypeConverter
        fun convertTransactionResponseXtoString(transRespX: TransactionResponseX) =
            Gson().toJson(transRespX)

        @TypeConverter
        fun convertStringToTransactionResponseX(tranRespXInJson: String) =
            Gson().fromJson(tranRespXInJson, TransactionResponseX::class.java)
    }
}

Type converters handle:

Enum serialization (TransactionType, IsoAccountType)
Complex object JSON serialization (TransactionResponseX)

Database Usage Examples

Insert Transaction

viewmodels/TransactionsViewModel.kt:131

fun insertIntoDatabase(transactionResponse: List<TransactionResponse>) {
    compositeDisposable.add(
        appDatabase.transactionResponseDao()
            .insertNewTransaction(transactionResponse)
            .subscribeOn(Schedulers.io())
            .observeOn(AndroidSchedulers.mainThread())
            .subscribe { rowIds, error ->
                rowIds?.let {
                    Timber.d("Inserted ${it.size} transactions")
                }
                error?.let {
                    Timber.e(it.localizedMessage)
                }
            }
    )
}

Query with Paging

viewmodels/TransactionsViewModel.kt:88

val pagedTransaction: LiveData<PagedList<TransactionResponse>>

init {
    val config = PagedList.Config.Builder()
        .setPageSize(20)
        .setEnablePlaceholders(false)
        .build()
    
    pagedTransaction = LivePagedListBuilder(
        appDatabase.transactionResponseDao()
            .getTransactions(NetPosTerminalConfig.getTerminalId()),
        config
    ).build()
}

End-of-Day Query

val eodTransactions = appDatabase.transactionResponseDao()
    .getEndOfDayTransaction(
        beginningOfDay = startOfDayMillis,
        endOfDay = endOfDayMillis,
        terminalId = NetPosTerminalConfig.getTerminalId()
    )

Transaction Types

enum class TransactionType {
    PURCHASE,
    REFUND,
    REVERSAL,
    PRE_AUTHORIZATION,
    PRE_AUTHORIZATION_COMPLETION,
    BALANCE_INQUIRY,
    CASH_ADVANCE
}

Account Types

enum class IsoAccountType {
    DEFAULT_UNSPECIFIED,
    SAVINGS,
    CURRENT,
    CREDIT,
    UNIVERSAL,
    INVESTMENT
}

Database Migration Strategy

NetPOS uses fallbackToDestructiveMigration() which means:

All data is lost on schema version changes
Suitable for development but risky for production
Consider implementing proper migration paths for production releases

Best Practices

Always use background threads

All database operations run on Schedulers.io() to avoid blocking UI

Observe LiveData

Use LiveData for automatic UI updates when data changes

Dispose subscriptions

Dispose RxJava subscriptions in onCleared() to prevent leaks

Use transactions for bulk operations

Wrap multiple inserts in @Transaction for atomicity

Query Performance Tips

Index frequently queried columns

Add @Index annotation on terminalId and transactionTimeInMillis

Use paging for large datasets

Implement DataSource.Factory for memory-efficient scrolling

Limit result sets

Use SQL LIMIT clause for preview queries

Avoid N+1 queries

Use JOIN queries or @Relation for related data

Data Models

Explore all data model definitions

ViewModels

See how ViewModels use DAOs

Architecture

Overall application architecture

Architecture

API

Database Schema

Overview

Database Configuration

Database Instantiation

Entities

1. TransactionResponse

2. MqttEventsLocal

3. TransactionResponseXForTracking

4. GetZenithPayByTransferUserTransactionsModel

Data Access Objects (DAOs)

TransactionResponseDao

MqttLocalDao

ZenithPayByTransferUserTransactionsDao

Type Converters

Database Usage Examples

Insert Transaction

Query with Paging

End-of-Day Query

Transaction Types

Account Types

Database Migration Strategy

Best Practices

Always use background threads

Observe LiveData

Dispose subscriptions

Use transactions for bulk operations

Query Performance Tips

Data Models

ViewModels

Architecture

Build docs developers (and LLMs) love

Architecture

API

​Overview

​Database Configuration

​Database Instantiation

​Entities

​1. TransactionResponse

​2. MqttEventsLocal

​3. TransactionResponseXForTracking

​4. GetZenithPayByTransferUserTransactionsModel

​Data Access Objects (DAOs)

​TransactionResponseDao

​MqttLocalDao

​ZenithPayByTransferUserTransactionsDao

​Type Converters

​Database Usage Examples

​Insert Transaction

​Query with Paging

​End-of-Day Query

​Transaction Types

​Account Types

​Database Migration Strategy

​Best Practices

Always use background threads

Observe LiveData

Dispose subscriptions

Use transactions for bulk operations

​Query Performance Tips

​Related Resources

Data Models

ViewModels

Architecture

Build docs developers (and LLMs) love

Overview

Database Configuration

Database Instantiation

Entities

1. TransactionResponse

2. MqttEventsLocal

3. TransactionResponseXForTracking

4. GetZenithPayByTransferUserTransactionsModel

Data Access Objects (DAOs)

TransactionResponseDao

MqttLocalDao

ZenithPayByTransferUserTransactionsDao

Type Converters

Database Usage Examples

Insert Transaction

Query with Paging

End-of-Day Query

Transaction Types

Account Types

Database Migration Strategy

Best Practices

Query Performance Tips

Related Resources