--- title: "Rated Messages & Message Detail Records (MDRs) Module Documentation" description: "Documentation for Rated Messages (MDRs)" --- ## Table of Contents 1. [Module Overview (Technical)](#1-module-overview-technical) 2. [Module Overview (Commercial & Business Value)](#2-module-overview-commercial--business-value) 3. [🎯 User Roles & Key Capabilities](#3--user-roles--key-capabilities) 4. [Visual Interface & Form Structure](#4-visual-interface--form-structure) 5. [Architectural Flow & SMS/MMS Rating Engine](#5-architectural-flow--smsmms-rating-engine) 6. [Common Scenarios & Operational Playbooks](#6-common-scenarios--operational-playbooks) 7. [Troubleshooting & Diagnostic Commands](#7-troubleshooting--diagnostic-commands) 8. [Model Context Protocol (MCP) AI Integration](#8-model-context-protocol-mcp-ai-integration) 9. [Glossary](#9-glossary) --- ## 1. Module Overview (Technical) The **Rated Messages & Message Detail Records (MDRs)** module (`public.mdrs_rated`) provides comprehensive financial auditing, carrier reconciliation, and delivery tracking for Short Message Service (SMS) and Multimedia Messaging Service (MMS) traffic across **Ring2All Billing**. ### Data Model & Entity Schema (`public.mdrs_rated`) ```sql CREATE TABLE public.mdrs_rated ( id BIGSERIAL PRIMARY KEY, uuid UUID NOT NULL DEFAULT gen_random_uuid(), message_uuid VARCHAR(64) NOT NULL UNIQUE, carrier_message_id VARCHAR(100), customer_id BIGINT REFERENCES customers(id) ON DELETE SET NULL, did_provider_id BIGINT REFERENCES did_providers(id) ON DELETE SET NULL, did_id BIGINT REFERENCES dids(id) ON DELETE SET NULL, direction VARCHAR(10) NOT NULL DEFAULT 'outbound', -- 'inbound' | 'outbound' message_type VARCHAR(10) NOT NULL DEFAULT 'sms', -- 'sms' | 'mms' source_number VARCHAR(50) NOT NULL, destination_number VARCHAR(50) NOT NULL, prefix VARCHAR(32), destination_name VARCHAR(255), segments_count INTEGER NOT NULL DEFAULT 1, media_count INTEGER NOT NULL DEFAULT 0, rate_per_unit NUMERIC(10,6) NOT NULL DEFAULT 0.005000, cost NUMERIC(10,4) NOT NULL DEFAULT 0.0000, vendor_rate NUMERIC(10,6) NOT NULL DEFAULT 0.000000, vendor_cost NUMERIC(10,4) NOT NULL DEFAULT 0.0000, margin NUMERIC(10,4) NOT NULL DEFAULT 0.0000, status VARCHAR(20) NOT NULL DEFAULT 'delivered', error_code VARCHAR(50), error_message TEXT, message_text TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); ``` ### Segmentation & Encoding Rules * **GSM-7 Character Encoding:** Standard 7-bit alphabet supporting up to 160 characters per segment. Concat messages above 160 characters use 153 characters per segment due to UDH (User Data Header) overhead. * **UCS-2 (Unicode) Encoding:** Triggered whenever emojis or accented non-GSM characters are present. Segment limits drop to 70 characters (or 67 characters per segment for multi-part messages). * **MMS Multimedia Rating:** Billed at higher flat rates per transaction plus carrier media data pass-through fees. --- ## 2. Module Overview (Commercial & Business Value) * **Monetization of Enterprise Messaging:** Allows service providers to package A2P (Application-to-Person) and P2P (Person-to-Person) messaging bundles into commercial customer subscriptions with automated overage pricing. * **Granular Margin Transparency:** Provides side-by-side visibility of retail billed cost versus upstream wholesale carrier expense (Telnyx, Twilio, Bandwidth) down to 6 decimal places. * **Prepaid Wallet Protection:** Real-time segment calculation ensures prepaid customer wallets are verified and debited before outbound message dispatch to the carrier network. * **Regulatory Compliance & Delivery Audit:** Immutable audit trail logging carrier message IDs, delivery receipts (DLRs), timestamps, and error codes for compliance verification. --- ## 3. 🎯 User Roles & Key Capabilities | User Role | Key Permissions | Core Responsibilities & Workflows | | :--- | :--- | :--- | | **Super Administrator** | Full Control & Tariff Mapping | Defines retail SMS/MMS pricing tiers, assigns messaging profiles, and investigates carrier wholesale price revisions. | | **Billing Specialist** | Read, Filter, Export | Analyzes message volume trends, audits monthly messaging spend per customer, and reconciles carrier DLR invoices. | | **Messaging Operations / NOC** | Delivery Audit & DLR Tracking | Investigates delivery failures, carrier error codes, and handset receipt delays. | | **Customer (Self-Care Portal)** | Self-Service MDR Inspection | Monitors corporate messaging campaign volume, sent vs received counts, and monthly billed spend. | --- ## 4. Visual Interface & Form Structure ### 4.1 Rated Messages Management (List View) The **Rated Messages** interface displays an institutional telemetry dashboard featuring total message counts, gross billed spend, gross margin profit, and delivery success rate, backed by an interactive DataGrid. ![Rated Messages List](/screenshots/billing/reports/financial/mdrs/mdrs-list.png) ### 4.2 Message Detail & Audit Modal Clicking on any message record opens the full **Message Detail Modal**, revealing full routing metadata, segment counts, carrier delivery receipts, and applied retail/vendor tariffs. ![Message Detail Modal](/screenshots/billing/reports/financial/mdrs/mdrs-details-modal.png) ### 4.3 MDR DataGrid Column Reference | Column Header | Data Field | Display Format | Technical & Financial Significance | | :--- | :--- | :--- | :--- | | **Type & Direction** | `direction`, `message_type` | Visual Badges | Indicates `Inbound` (MO) or `Outbound` (MT) traffic and payload classification (`SMS` or `MMS`). | | **Source Number** | `source_number` | E.164 String | Originating sender DID or Alphanumeric Sender ID. | | **Destination Number** | `destination_number` | E.164 String | Target recipient phone number. | | **Segments & Media** | `segments_count`, `media_count` | Numeric Badges | Number of billable 160/70 character units and attached multimedia assets. | | **Customer Account** | `customer_id` | Text / Code | Enterprise client associated with the originating or receiving number. | | **Carrier Provider** | `did_provider_id` | Text | Wholesale carrier gateway that routed the message. | | **Billed Cost** | `cost` / `rate_per_unit` | `$0.0000` | Net customer charge and unit price per segment applied. | | **Delivery Status** | `status` | Status Badge | Real-time carrier DLR status: `delivered`, `sent`, `received`, `undelivered`, `failed`. | --- ## 5. Architectural Flow & SMS/MMS Rating Engine ``` ┌────────────────────────────────────────────────────────────────────────┐ │ Inbound or Outbound SMS/MMS Event on Message Gateway │ └───────────────────────────────────┬────────────────────────────────────┘ │ ▼ ┌────────────────────────────────────────────────────────────────────────┐ │ Step 1: Payload Analysis & Segment Computation │ │ • Detect character set: GSM-7 (160 char) vs UCS-2 Unicode (70 char) │ │ • Calculate total billable segments = `ceil(length / segment_size)` │ │ • Detect media attachments (images, audio, PDF) for MMS rating │ └───────────────────────────────────┬────────────────────────────────────┘ │ ▼ ┌────────────────────────────────────────────────────────────────────────┐ │ Step 2: Dual Financial Rating Calculation │ │ • `Customer Cost = segments_count * customer_rate_per_unit` │ │ • `Carrier Cost = segments_count * vendor_rate_per_unit` │ │ • `Gross Margin = Customer Cost - Carrier Cost` │ └───────────────────────────────────┬────────────────────────────────────┘ │ ▼ ┌────────────────────────────────────────────────────────────────────────┐ │ Step 3: Atomic Debit & MDR Ledger Insertion │ │ • Deduct `cost` from customer wallet balance │ │ • Insert complete audit record into `public.mdrs_rated` │ │ • Emit real-time telemetry event to live monitoring dashboard │ └────────────────────────────────────────────────────────────────────────┘ ``` --- ## 6. Common Scenarios & Operational Playbooks ### Scenario A: Investigating High-Cost Multi-Part Message Spikes 1. Filter the MDRs list by **Type: SMS** and **Direction: Outbound**. 2. Inspect the **Segments** column to locate records with 5+ segments. 3. Open the **Message Detail Modal** to verify if unexpected Unicode emojis or special quotation marks caused the message to switch from GSM-7 (160 chars) to UCS-2 (70 chars). 4. Advise the customer's CRM integration team on sanitizing character encodings before dispatching bulk notifications. ### Scenario B: Auditing Carrier Delivery Failure Rates 1. In the top telemetry cards, observe the **Delivery Success Rate** percentage. 2. Filter the DataGrid by status **Failed / Undelivered**. 3. Inspect the `error_code` (e.g. `CARRIER_UNREACHABLE`, `INVALID_DESTINATION`, `SPAM_FILTER_REJECTED`) to identify terminating network outages. --- ## 7. Troubleshooting & Diagnostic Commands ### Query Daily Messaging Margin by Customer ```sql SELECT c.name as customer_name, count(*) as total_messages, sum(m.segments_count) as total_segments, sum(m.cost) as billed_revenue, sum(m.vendor_cost) as carrier_cogs, sum(m.margin) as gross_margin FROM mdrs_rated m JOIN customers c ON c.id = m.customer_id WHERE m.created_at >= NOW() - INTERVAL '30 days' GROUP BY c.name ORDER BY billed_revenue DESC; ``` --- ## 8. Model Context Protocol (MCP) AI Integration The **Rated Messages & Message Detail Records (MDRs)** module connects directly to the **Ring2All BSS MCP Server**, allowing AI assistants, SMS routing engineers, and financial auditors to inspect messaging volume, examine delivery statuses, and verify carrier margins. ### Available MCP Tools | Tool Name | Access Role | Description & Primary Function | Example Arguments | | :--- | :--- | :--- | :--- | | `query_rated_mdrs` | `Billing Operations` / `Admin` | Searches and analyzes rated Message Detail Records (MDRs/SMS) with carrier, segments, direction, and margins. | `{"customerId": 1, "status": "delivered", "limit": 10}` | ### Sample MCP Tool Execution: `query_rated_mdrs` #### Request Payload ```json { "name": "query_rated_mdrs", "arguments": { "customerId": 1, "limit": 5 } } ``` #### Response Payload ```json [ { "id": 9104, "fromNumber": "+13055550199", "toNumber": "+17865550100", "direction": "outbound", "segmentsCount": 1, "status": "delivered", "customerCost": 0.0150, "vendorCost": 0.0055, "grossMargin": 0.0095, "customer": { "id": 1, "name": "Rodrigo Cuadra" }, "createdAt": "2026-09-09T04:50:00Z" } ] ``` ### Conversational AI Prompts for Copilot * *"Show recent SMS delivery errors or undelivered messages for customer 1."* * *"What is the total outbound SMS segment count and spend for this month?"* * *"List the top carriers by message volume and gross margin."* --- ## 9. Glossary * **DLR (Delivery Receipt):** Asynchronous carrier status message confirming that an SMS reached the recipient handset or failed. * **GSM-7:** Default character set for SMS supporting Latin alphanumeric characters up to 160 characters per segment. * **MDR (Message Detail Record):** Immutable record capturing all technical and financial parameters of a single SMS or MMS transaction. * **MO (Mobile Originated):** Inbound message originating from an end-user handset toward the platform. * **MT (Mobile Terminated):** Outbound message dispatched from the platform toward a recipient handset. * **UCS-2:** 16-bit character encoding required for emojis and non-Latin alphabets, reducing segment capacity to 70 characters. * **Model Context Protocol (MCP):** Open protocol standard that enables secure, controlled integration between Large Language Models and external tools, databases, and telecom rating engines.