Rated Messages & Message Detail Records (MDRs) Module Documentation

Table of Contents

1. Module Overview (Technical)
2. Module Overview (Commercial & Business Value)
3. 🎯 User Roles & Key Capabilities
4. Visual Interface & Form Structure
5. Architectural Flow & SMS/MMS Rating Engine
6. Common Scenarios & Operational Playbooks
7. Troubleshooting & Diagnostic Commands
8. Model Context Protocol (MCP) AI Integration
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)

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.

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.

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

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

{
  "name": "query_rated_mdrs",
  "arguments": {
    "customerId": 1,
    "limit": 5
  }
}

Response Payload

[
  {
    "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.