# ๐Ÿฆ Complete ReqPay Transaction Storage Implementation ## Overview This implementation provides a comprehensive solution for storing all ReqPay transaction details in your UPI PSP platform. The system captures every aspect of the ReqPay lifecycle with full audit trails, event logging, and analytics capabilities. ## ๐Ÿ—ƒ๏ธ Database Schema ### Core Tables #### 1. `req_pays` Table **Purpose**: Primary storage for normalized ReqPay transaction data **Key Fields**: ```sql -- Core UPI Fields msg_id VARCHAR(35) NOT NULL UNIQUE -- NPCI Message ID org_id VARCHAR(10) NOT NULL -- Organization ID txn_id VARCHAR(50) -- Transaction ID ref_id VARCHAR(35) -- Reference ID ref_url VARCHAR(255) -- Reference URL -- Party Information payer_addr VARCHAR(255) -- Payer UPI ID payee_addr VARCHAR(255) -- Payee UPI ID payer_name VARCHAR(100) -- Payer Name payee_name VARCHAR(100) -- Payee Name -- Transaction Details amount DECIMAL(18,2) NOT NULL -- Transaction Amount currency VARCHAR(3) NOT NULL -- Currency Code payment_type VARCHAR(20) -- QR/INTENT/COLLECT payment_purpose VARCHAR(100) -- Purpose of Payment payment_status VARCHAR(20) -- SUCCESS/FAILURE/PENDING settlement_status VARCHAR(20) -- Settlement Status -- International Payment Fields corridor VARCHAR(20) -- SINGAPORE/UAE/USA fx_rate DECIMAL(18,8) -- Exchange Rate base_amount DECIMAL(18,6) -- Amount in Base Currency base_currency VARCHAR(3) -- Merchant's Currency -- QR Specific Fields qr_string TEXT -- QR Code String qr_medium VARCHAR(20) -- DYNAMIC/STATIC merchant_category_code VARCHAR(10) -- MCC Code merchant_vpa VARCHAR(255) -- Merchant UPI ID -- Status & Tracking status VARCHAR(20) NOT NULL DEFAULT 'PENDING' -- PENDING/PROCESSED/FAILED validation_type VARCHAR(20) NOT NULL -- DOMESTIC/INTERNATIONAL error_code VARCHAR(10) -- Error Code if Failed error_message VARCHAR(255) -- Error Description -- Timestamps & Performance npci_request_received_at DATETIME -- Request Receipt Time npci_response_sent_at DATETIME -- Response Send Time processing_duration_ms INT -- Processing Time paid_at DATETIME -- Payment Completion Time -- XML Storage (Hashed) req_xml_hash BLOB -- SHA256 of Request XML resp_xml_hash BLOB -- SHA256 of Response XML -- Relationships transaction_id BIGINT -- Link to main transaction partner_id BINARY(16) -- Link to partner merchant_id BIGINT -- Link to merchant ``` #### 2. `req_pay_events` Table **Purpose**: Event log with hash chain integrity ```sql req_pay_id BIGINT NOT NULL -- Link to req_pays transaction_id BIGINT NOT NULL -- Link to transaction seq INT NOT NULL -- Event Sequence Number event_type VARCHAR(50) NOT NULL -- Event Type payload JSON -- Event Data prev_hash BLOB -- Previous Event Hash hash BLOB NOT NULL -- This Event Hash req_xml_hash BLOB -- Request XML Hash resp_xml_hash BLOB -- Response XML Hash ``` ## ๐Ÿ”ง Implementation Architecture ### 1. ReqPayService Module **Location**: `lib/da_product_app/transactions/req_pay_service.ex` **Key Features**: - Complete CRUD operations for ReqPay data - Event logging with cryptographic hash chains - Status transition validation - XML hash storage for audit trails - Analytics and reporting functions - International payment support **Core Functions**: ```elixir # Creation & Storage ReqPayService.create_req_pay(attrs, req_xml, parsed_data) ReqPayService.update_req_pay_with_response(req_pay, response_attrs, resp_xml) # Status Management ReqPayService.update_status(req_pay, "PROCESSED", metadata) ReqPayService.mark_as_processed(req_pay, partner_response) ReqPayService.mark_as_failed(req_pay, error_code, error_message) # Event Logging ReqPayService.append_event(req_pay, "validation_started", payload, xml_content) # Analytics ReqPayService.get_statistics(date_from: yesterday, date_to: today) ReqPayService.get_trends(interval: "day") ``` ### 2. Updated UPI Controller **Location**: `lib/da_product_app_web/controllers/api/v1/upi_controller.ex` **Enhanced Flow**: 1. Parse incoming ReqPay XML from NPCI 2. **Store ReqPay details immediately** using ReqPayService 3. Send ACK response to NPCI 4. Process payment asynchronously 5. Log events at each processing stage 6. Update ReqPay with final status and response ### 3. Event Chain Management **Location**: `lib/da_product_app/transaction_event_chain_service.ex` **Features**: - Hash chain integrity for audit trails - Cross-reference between ReqPay and Transaction events - Cryptographic verification of event ordering ## ๐Ÿ“Š Data Capture Points ### 1. Initial Request Processing ```elixir # Captured when ReqPay received from NPCI %{ msg_id: "MSG123456789", org_id: "MERCURY001", txn_id: "TXN987654321", amount: Decimal.new("100.00"), currency: "INR", payer_addr: "user@paytm", payee_addr: "merchant@mercury", # ... all other fields from XML npci_request_received_at: DateTime.utc_now(), req_xml_hash: :crypto.hash(:sha256, req_xml) } ``` ### 2. Event Logging Throughout Process ```elixir # Events logged during processing [ %{type: "req_pay_received", seq: 1, payload: %{...}}, %{type: "processing_started", seq: 2, payload: %{...}}, %{type: "validation_success", seq: 3, payload: %{...}}, %{type: "partner_processing", seq: 4, payload: %{...}}, %{type: "payment_processed", seq: 5, payload: %{...}}, %{type: "response_sent", seq: 6, payload: %{...}}, %{type: "status_changed", seq: 7, payload: %{...}} ] ``` ### 3. Response Data Storage ```elixir # Updated when sending RespPay to NPCI %{ response_code: "00", response_message: "SUCCESS", payment_status: "SUCCESS", rrn: "RRN123456789", npci_response_sent_at: DateTime.utc_now(), processing_duration_ms: 1250, resp_xml_hash: :crypto.hash(:sha256, resp_xml) } ``` ## ๐ŸŒ International Payment Support ### FX Data Capture ```elixir %{ validation_type: "INTERNATIONAL", corridor: "SINGAPORE", fx_rate: Decimal.new("0.012"), # SGD to INR rate base_amount: Decimal.new("1.00"), # Original SGD amount base_currency: "SGD", amount: Decimal.new("83.25") # Converted INR amount } ``` ### Corridor-Specific Analytics - Volume by corridor (Singapore, UAE, USA) - FX rate tracking and trends - Settlement amount comparisons - Cross-border transaction success rates ## ๐Ÿ” Security & Audit Features ### 1. XML Hash Storage - **Purpose**: Immutable audit trail of original XML - **Method**: SHA-256 hashing of req/resp XML - **Benefits**: Tamper detection, compliance verification ### 2. Event Hash Chains - **Purpose**: Cryptographic event ordering - **Method**: Each event hash includes previous event hash - **Benefits**: Prevents event manipulation, ensures timeline integrity ### 3. Status Transition Validation ```elixir # Valid transitions only "PENDING" โ†’ "PROCESSED" โœ… "PENDING" โ†’ "FAILED" โœ… "PROCESSED" โ†’ "FAILED" โœ… (reversal scenario) "FAILED" โ†’ "PROCESSED" โŒ (invalid) ``` ## ๐Ÿ“ˆ Analytics & Reporting ### 1. Transaction Statistics ```elixir %{ total_count: 1247, status_breakdown: %{ "PROCESSED" => 1089, "FAILED" => 158, "PENDING" => 0 }, validation_type_breakdown: %{ "DOMESTIC" => 987, "INTERNATIONAL" => 260 }, corridor_breakdown: %{ "SINGAPORE" => 145, "UAE" => 89, "USA" => 26 }, amount_statistics: %{ total: Decimal.new("234567.89"), average: Decimal.new("188.23"), min: Decimal.new("1.00"), max: Decimal.new("5000.00") } } ``` ### 2. Performance Metrics - Average processing time per transaction - Success rates by payment type (QR vs INTENT) - Error code distribution - Peak processing times ### 3. Trend Analysis - Daily/hourly transaction volumes - Success rate trends over time - International vs domestic ratios - Corridor performance comparisons ## ๐Ÿš€ Usage Examples ### Creating ReqPay from Controller ```elixir def process_credit_payment(conn, _params) do with {:ok, xml_body} <- read_request_body(conn), {:ok, parsed_data} <- UpiXmlSchema.parse_req_pay(xml_body) do # Build comprehensive attributes req_pay_attrs = build_req_pay_attrs(parsed_data) # Store with XML and parsed data case ReqPayService.create_req_pay(req_pay_attrs, xml_body, parsed_data) do {:ok, req_pay} -> # Send ACK and process asynchronously send_ack_and_process_async(conn, req_pay, parsed_data) {:error, changeset} -> send_error_response(conn, "96", "System malfunction") end end end ``` ### Event Logging During Processing ```elixir # Log validation events ReqPayService.append_event(req_pay, "validation_started", %{ validator: "merchant_check", merchant_id: merchant.id }) # Log partner communication ReqPayService.append_event(req_pay, "partner_request_sent", %{ partner_id: partner.id, request_payload: partner_payload }, partner_request_xml) # Log final status ReqPayService.mark_as_processed(req_pay, %{ partner_response: partner_response, settlement_amount: final_amount }) ``` ### Analytics Queries ```elixir # Get today's statistics today_stats = ReqPayService.get_statistics( date_from: Date.utc_today() |> DateTime.new!(~T[00:00:00]), date_to: DateTime.utc_now() ) # Get Singapore corridor trends singapore_trends = ReqPayService.list_req_pays( corridor: "SINGAPORE", limit: 100, sort_by: :amount ) # Get failed transaction analysis failed_analysis = ReqPayService.list_req_pays( status: "FAILED", date_from: last_week ) ``` ## โœ… Benefits of This Implementation ### 1. **Complete Data Capture** - Every field from NPCI ReqPay XML stored - Full transaction lifecycle documented - Rich metadata for analysis ### 2. **Audit Compliance** - Immutable event logs with cryptographic integrity - XML hash storage for compliance verification - Complete audit trail from request to response ### 3. **Performance Monitoring** - Processing time tracking - Bottleneck identification - Success rate monitoring ### 4. **Business Intelligence** - Transaction volume trends - Revenue analytics by corridor - Partner performance metrics ### 5. **Operational Excellence** - Real-time status tracking - Error pattern analysis - Automated reconciliation support ## ๐Ÿงช Testing Coverage The implementation includes comprehensive tests covering: - ReqPay creation and validation - Event logging and hash chain integrity - Status transitions and error handling - International payment scenarios - Analytics and reporting functions - Edge cases and error conditions **Test File**: `test/da_product_app/transactions/req_pay_service_test.exs` ## ๐Ÿ”„ Integration Points ### 1. **UPI Controller** - Automatic ReqPay creation on NPCI requests - Event logging throughout processing - Status updates on completion ### 2. **Transaction Management** - Cross-reference with main transaction table - Dual event logging (ReqPay + Transaction) - Unified transaction timeline ### 3. **Partner Integrations** - Partner response capture - Settlement data recording - Error handling and logging ### 4. **Analytics Dashboards** - Real-time statistics display - Trend visualization - Performance monitoring This implementation provides a robust, scalable, and compliant solution for capturing all ReqPay transaction details in your UPI PSP platform. The system ensures data integrity, provides comprehensive audit trails, and enables powerful analytics for business intelligence and regulatory compliance.