Database currentDB and repo: POS Card related table 1. pos_settlement (currently this table is not there need to create with settlement_id, settlement_date,created_date,updated_date,other fields from pos_transaction table) 2. pos_failed_transaction 3. pos_transaction_reversal QR transaction 1.table transactions( for all success,failed, settlement) Merchant and rate related tables are from different database:shukria_mms 1.table merchants for other merchant related informations mysql> desc mcc_mdr_rates; +-----------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-----------------+------+-----+---------+----------------+ | id | bigint unsigned | NO | PRI | NULL | auto_increment | | mcc_id | bigint unsigned | NO | MUL | NULL | | | card_type_id | bigint unsigned | NO | MUL | NULL | | | percentage_rate | decimal(8,4) | NO | | 0.0000 | | | fixed_fee | decimal(10,2) | NO | | 0.00 | | | minimum_fee | decimal(10,2) | YES | | NULL | | | maximum_fee | decimal(10,2) | YES | | NULL | | | effective_from | date | YES | MUL | NULL | | | effective_to | date | YES | | NULL | | | status | tinyint(1) | NO | MUL | 1 | | | notes | text | YES | | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +-----------------+-----------------+------+-----+---------+----------------+ 13 rows in set (0.00 sec) mysql> select * from mcc_mdr_rates limit 3; +----+--------+--------------+-----------------+-----------+-------------+-------------+----------------+--------------+--------+--------------------------------------+---------------------+---------------------+ | id | mcc_id | card_type_id | percentage_rate | fixed_fee | minimum_fee | maximum_fee | effective_from | effective_to | status | notes | created_at | updated_at | +----+--------+--------------+-----------------+-----------+-------------+-------------+----------------+--------------+--------+--------------------------------------+---------------------+---------------------+ | 1 | 20 | 45 | 1.6500 | 0.00 | NULL | NULL | NULL | NULL | 1 | Auto-imported from CSV - Automobiles | 2025-09-08 12:50:34 | 2025-09-08 12:50:34 | | 2 | 20 | 46 | 1.6500 | 0.00 | NULL | NULL | NULL | NULL | 1 | Auto-imported from CSV - Automobiles | 2025-09-08 12:50:34 | 2025-09-08 12:50:34 | | 3 | 20 | 47 | 1.6500 | 0.00 | NULL | NULL | NULL | NULL | 1 | Auto-imported from CSV - Automobiles | 2025-09-08 12:50:34 | 2025-09-08 12:50:34 | +----+--------+--------------+-----------------+-----------+-------------+-------------+----------------+--------------+--------+--------------------------------------+---------------------+---------------------+ 3 rows in set (0.01 sec) mysql> desc mdr_rates; +-----------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-----------------+------+-----+---------+----------------+ | id | bigint unsigned | NO | PRI | NULL | auto_increment | | mdr_template_id | bigint unsigned | NO | MUL | NULL | | | card_type_id | bigint unsigned | NO | MUL | NULL | | | percentage_rate | decimal(8,4) | NO | | 0.0000 | | | fixed_fee | decimal(10,2) | NO | | 0.00 | | | minimum_fee | decimal(10,2) | YES | | NULL | | | maximum_fee | decimal(10,2) | YES | | NULL | | | effective_from | date | YES | MUL | NULL | | | effective_to | date | YES | | NULL | | | status | tinyint(1) | NO | MUL | 1 | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +-----------------+-----------------+------+-----+---------+----------------+ 12 rows in set (0.01 sec) mysql> select * from mdr_rates limit 2; +----+-----------------+--------------+-----------------+-----------+-------------+-------------+----------------+--------------+--------+---------------------+---------------------+ | id | mdr_template_id | card_type_id | percentage_rate | fixed_fee | minimum_fee | maximum_fee | effective_from | effective_to | status | created_at | updated_at | +----+-----------------+--------------+-----------------+-----------+-------------+-------------+----------------+--------------+--------+---------------------+---------------------+ | 2 | 2 | 1 | 0.0006 | 0.03 | 0.07 | 0.14 | 2025-08-22 | 2025-08-29 | 1 | 2025-08-22 06:09:11 | 2025-08-22 06:09:11 | | 24 | 14 | 15 | 0.0002 | 0.03 | 0.03 | 0.03 | 2025-09-02 | 2025-10-01 | 1 | 2025-09-02 11:19:34 | 2025-09-02 11:19:34 | +----+-----------------+--------------+-----------------+-----------+-------------+-------------+----------------+--------------+--------+---------------------+---------------------+ 2 rows in set (0.00 sec) mysql> desc mdr_templates; +----------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-----------------+------+-----+---------+----------------+ | id | bigint unsigned | NO | PRI | NULL | auto_increment | | merchant_id | bigint unsigned | YES | | NULL | | | name | varchar(191) | NO | | NULL | | | description | text | YES | | NULL | | | is_default | tinyint(1) | NO | MUL | 0 | | | is_custom | tinyint(1) | NO | | 0 | | | is_modified | tinyint(1) | NO | | 0 | | | mcc_id | bigint unsigned | YES | MUL | NULL | | | sub_mcc | varchar(191) | YES | | NULL | | | effective_from | date | YES | MUL | NULL | | | effective_to | date | YES | | NULL | | | status | tinyint(1) | NO | | 1 | | | created_by | bigint unsigned | NO | MUL | NULL | | | updated_by | bigint unsigned | YES | MUL | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +----------------+-----------------+------+-----+---------+----------------+ 16 rows in set (0.00 sec) mysql> select * from mdr_templates limit 2; +----+-------------+------------+-------------+------------+-----------+-------------+--------+---------+----------------+--------------+--------+------------+------------+---------------------+---------------------+ | id | merchant_id | name | description | is_default | is_custom | is_modified | mcc_id | sub_mcc | effective_from | effective_to | status | created_by | updated_by | created_at | updated_at | +----+-------------+------------+-------------+------------+-----------+-------------+--------+---------+----------------+--------------+--------+------------+------------+---------------------+---------------------+ | 2 | 430 | Electonics | NULL | 0 | 0 | 0 | 12 | Retail | 2025-08-22 | 2025-08-29 | 1 | 1 | NULL | 2025-08-22 06:09:11 | 2025-08-22 06:09:11 | | 10 | 434 | Medical | NULL | 0 | 0 | 0 | 8 | Drug | 2025-08-26 | 2025-08-30 | 1 | 1 | NULL | 2025-08-26 07:10:00 | 2025-09-03 06:18:44 | +----+-------------+------------+-------------+------------+-----------+-------------+--------+---------+----------------+--------------+--------+------------+------------+---------------------+---------------------+ 2 rows in set (0.00 sec) mysql> select * from users limit 2; +----+--------------------+--------------------------------------------------------------+--------------+---------------------+---------------------+------+------------+-----------+------+-------+------+-----------+ | id | email | hashed_password | confirmed_at | inserted_at | updated_at | role | first_name | last_name | name | image | data | data_name | +----+--------------------+--------------------------------------------------------------+--------------+---------------------+---------------------+------+------------+-----------+------+-------+------+-----------+ | 2 | sonu@dataaegis.com | $2b$12$QrPkfBa85.f73TL25s1bReCCCYuCkeiNnEcoklPZFOfUEfoWywQy2 | NULL | 2025-05-12 07:36:10 | 2025-05-12 07:36:10 | user | NULL | NULL | NULL | NULL | NULL | NULL | | 3 | demo@mercury.com | $2b$12$II1lBX/hENlxnRtxTSHzpuXceYKymURt152zTv2Isyj0scqDw3zGu | NULL | 2025-05-12 07:36:10 | 2025-05-12 07:36:10 | user | NULL | NULL | NULL | NULL | NULL | NULL | +----+--------------------+--------------------------------------------------------------+--------------+---------------------+---------------------+------+------------+-----------+------+-------+------+-----------+ 2 rows in set (0.01 sec) desc card_types; +-------------+--------------------------------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------------------------------------------------+------+-----+---------+----------------+ | id | bigint unsigned | NO | PRI | NULL | auto_increment | | code | varchar(50) | NO | UNI | NULL | | | name | varchar(191) | NO | | NULL | | | category | enum('DEBIT','CREDIT','PREPAID','MANUAL','QR_DIGITAL') | NO | MUL | NULL | | | brand | varchar(191) | YES | | NULL | | | sub_type | varchar(191) | YES | | NULL | | | description | text | YES | | NULL | | | status | tinyint(1) | NO | MUL | 1 | | | sort_order | int | NO | | 0 | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +-------------+--------------------------------------------------------+------+-----+---------+----------------+ 11 rows in set (0.00 sec) mysql> select * from card_types limit 2; +----+-----------------+---------------------+----------+-------+----------+----------------------------------------------------+--------+------------+---------------------+---------------------+ | id | code | name | category | brand | sub_type | description | status | sort_order | created_at | updated_at | +----+-----------------+---------------------+----------+-------+----------+----------------------------------------------------+--------+------------+---------------------+---------------------+ | 1 | VISA_DEBIT_STD | Visa Debit Standard | DEBIT | VISA | Standard | Standard Visa debit card for everyday transactions | 1 | 1 | 2025-08-21 06:13:42 | 2025-09-04 11:02:39 | | 2 | VISA_DEBIT_PREM | Visa Debit Premium | DEBIT | VISA | Premium | Premium Visa debit card with enhanced benefits | 1 | 2 | 2025-08-21 06:13:42 | 2025-09-04 11:02:39 | +----+-----------------+---------------------+----------+-------+----------+----------------------------------------------------+--------+------------+---------------------+---------------------+ For mysql> select * from merchant_metadata where merchant_refrence_number=Mercury_F1CE8D7; +-----+---------+-------------+---------+--------------------------+---------------+----------------------+--------------+------------+-----------+-------------+---------------------+---------------------+---------------------+---------------------+ | id | user_id | merchant_id | mc_code | merchant_refrence_number | currency_code | transaction_currency | country_code | group_code | bank_code | bankUser_id | date_added | date_updated | created_at | updated_at | +-----+---------+-------------+---------+--------------------------+---------------+----------------------+--------------+------------+-----------+-------------+---------------------+---------------------+---------------------+---------------------+ | 240 | 436 | 430 | NULL | Mercury_F1CE8D7 | NULL | NULL | 971 | NULL | NULL | NULL | 2026-03-03 05:53:56 | 2026-03-03 05:53:56 | 2026-03-03 05:53:56 | 2026-03-03 05:53:56 | +-----+---------+-------------+---------+--------------------------+---------------+----------------------+--------------+------------+-----------+-------------+---------------------+---------------------+---------------------+---------------------+ Table info added: 06-03-2026 mysql> desc merchant_stores; +---------------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-----------------+------+-----+---------+----------------+ | id | bigint unsigned | NO | PRI | NULL | auto_increment | | user_id | bigint unsigned | NO | MUL | NULL | | | merchant_name | varchar(191) | NO | | NULL | | | dba_name | varchar(191) | YES | | NULL | | | complete_address | text | NO | | NULL | | | location | varchar(191) | NO | | NULL | | | product_type | varchar(191) | NO | | NULL | | | quantity | int | NO | | 1 | | | contact_person_name | varchar(191) | NO | | NULL | | | contact_number | varchar(191) | NO | | NULL | | | is_primary | tinyint(1) | NO | | 0 | | | is_ro_address | tinyint(1) | NO | | 0 | | | status | varchar(191) | NO | | pending | | | metadata | json | YES | | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +---------------------+-----------------+------+-----+---------+----------------+ 16 rows in set (0.00 sec) desc users; +-------------------------------+-------------------------------------------+------+-----+-------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------------------+-------------------------------------------+------+-----+-------------+----------------+ | id | bigint unsigned | NO | PRI | NULL | auto_increment | | name | varchar(191) | YES | | NULL | | | avatar | varchar(191) | YES | | NULL | | | username | varchar(191) | NO | UNI | NULL | | | phone | varchar(191) | YES | | NULL | | | email | varchar(191) | NO | UNI | NULL | | | currency_id | bigint unsigned | YES | MUL | NULL | | | mcc_id | bigint unsigned | YES | MUL | NULL | | | role | varchar(191) | NO | | user | | | bank_id | bigint unsigned | YES | MUL | NULL | | | created_by_user_id | bigint unsigned | YES | MUL | NULL | | | created_by_role_id | bigint unsigned | YES | MUL | NULL | | | wallet | double | NO | | 0 | | | password | varchar(191) | YES | | NULL | | | status | int | NO | | 1 | | | kyc_status | enum('in_progress','approved','rejected') | NO | | in_progress | | | meta | json | YES | | NULL | | | public_key | varchar(191) | NO | UNI | NULL | | | secret_key | varchar(191) | NO | UNI | NULL | | | qr | varchar(191) | NO | UNI | NULL | | | ip_address | varchar(45) | YES | | NULL | | | last_login_at | timestamp | YES | | NULL | | | locked_until | timestamp | YES | | NULL | | | failed_login_attempts | int | NO | | 0 | | | password_changed_at | timestamp | YES | | NULL | | | mfa_enabled | tinyint(1) | NO | | 0 | | | mfa_secret | varchar(191) | YES | | NULL | | | kyc_verified_at | timestamp | YES | | NULL | | | onboarding_step | tinyint unsigned | NO | | 1 | | | admin_onboarding_step | tinyint unsigned | NO | | 0 | | | shareholders | json | YES | | NULL | | | products | json | YES | | NULL | | | merchant_onboarding_step | tinyint unsigned | NO | | 0 | | | city | varchar(191) | YES | | NULL | | | merchant_stage | tinyint | NO | | 0 | | | merchant_status | varchar(191) | NO | | not_started | | | remember_token | varchar(100) | YES | | NULL | | | email_verified_at | timestamp | YES | | NULL | | | is_first_login | tinyint(1) | NO | | 0 | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | | contract_generated_at | timestamp | YES | | NULL | | | contract_sent_at | timestamp | YES | | NULL | | | contract_initiated_at | timestamp | YES | | NULL | | | contract_reference_id | varchar(191) | YES | | NULL | | | contract_status | varchar(191) | YES | | NULL | | | contract_approval_status | varchar(50) | YES | | NULL | | | contract_approved_by | bigint unsigned | YES | | NULL | | | contract_approved_at | timestamp | YES | | NULL | | | contract_rejection_reason | text | YES | | NULL | | | contract_approval_notes | text | YES | | NULL | | | contract_data | json | YES | | NULL | | | contract_callback_data | json | YES | | NULL | | | contract_callback_received_at | timestamp | YES | | NULL | | | contract_completed_at | timestamp | YES | | NULL | | | final_signed_contract_url | varchar(191) | YES | | NULL | | | final_signed_contract_hash | varchar(191) | YES | | NULL | | | audit_certificate_url | varchar(191) | YES | | NULL | | | signer_callback_data | json | YES | | NULL | | | last_signer_status | varchar(191) | YES | | NULL | | | last_signer_callback_at | timestamp | YES | | NULL | | | contract_sent_to | varchar(191) | YES | | NULL | | | contract_updated_at | timestamp | YES | | NULL | | | contract_notes | text | YES | | NULL | | | expected_annual_turnover | varchar(191) | YES | | NULL | | | fee_structure | json | YES | | NULL | | | channel_partner_id | bigint unsigned | YES | MUL | NULL | | +-------------------------------+-------------------------------------------+------+-----+-------------+----------------+ 67 rows in set (0.01 sec) mysql> desc merchant_stores; +---------------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-----------------+------+-----+---------+----------------+ | id | bigint unsigned | NO | PRI | NULL | auto_increment | | user_id | bigint unsigned | NO | MUL | NULL | | | merchant_name | varchar(191) | NO | | NULL | | | dba_name | varchar(191) | YES | | NULL | | | complete_address | text | NO | | NULL | | | location | varchar(191) | NO | | NULL | | | product_type | varchar(191) | NO | | NULL | | | quantity | int | NO | | 1 | | | contact_person_name | varchar(191) | NO | | NULL | | | contact_number | varchar(191) | NO | | NULL | | | is_primary | tinyint(1) | NO | | 0 | | | is_ro_address | tinyint(1) | NO | | 0 | | | status | varchar(191) | NO | | pending | | | metadata | json | YES | | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +---------------------+-----------------+------+-----+---------+----------------+ 16 rows in set (0.00 sec mysql> desc user_banks; +------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------------+------+-----+---------+----------------+ | id | bigint unsigned | NO | PRI | NULL | auto_increment | | user_id | bigint unsigned | YES | MUL | NULL | | | bank_id | bigint unsigned | YES | MUL | NULL | | | data | json | YES | | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +------------+-----------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) select * from user_banks limit 1; +----+---------+---------+----------------------------------------------------------------------------------------------------------+---------------------+---------------------+ | id | user_id | bank_id | data | created_at | updated_at | +----+---------+---------+----------------------------------------------------------------------------------------------------------+---------------------+---------------------+ | 1 | 3 | 8 | {"account_name": "Robb Sanford", "account_type": "business", "account_number": 16, "routing_number": 10} | 2025-02-03 05:03:16 | 2025-02-03 05:03:16 | +----+---------+---------+----------------------------------------------------------------------------------------------------------+---------------------+---------------------+ 1 row in set (0.00 sec)