--- title: "Rated CDRs & Profit Margin Analytics Module Documentation" description: "Documentation for Rated CDRs" --- ## 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 & Real-Time Rating Pipeline](#5-architectural-flow--real-time-rating-pipeline) 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 CDRs & Profit Margin Analytics** module (`public.cdrs_rated`) acts as the high-throughput financial auditing ledger for all voice traffic traversing **Ring2All Billing**. Whenever a call session completes on **Ring2All SBC** or **Ring2All PBX**, the rating engine computes two parallel financial calculations: 1. **Retail Customer Cost:** Calculated against the customer's assigned retail Rate Card, factoring in initial/increment billing intervals (e.g., 30/6, 60/60, 1/1) and connect fees. 2. **Wholesale Vendor Cost:** Calculated against the terminating carrier provider's wholesale Rate Card. 3. **Gross Profit Margin:** Derived in real time (`margin = customer_cost - vendor_cost`, `margin_percent = (margin / customer_cost) * 100`). ### PostgreSQL Schema Architecture (`public.cdrs_rated`) ```sql CREATE TABLE public.cdrs_rated ( id BIGSERIAL PRIMARY KEY, uuid UUID NOT NULL DEFAULT gen_random_uuid(), call_uuid VARCHAR(64) NOT NULL UNIQUE, customer_id BIGINT REFERENCES customers(id) ON DELETE SET NULL, did_provider_id BIGINT REFERENCES did_providers(id) ON DELETE SET NULL, caller_number VARCHAR(50) NOT NULL, destination_number VARCHAR(50) NOT NULL, destination_name VARCHAR(100), prefix VARCHAR(32), duration_seconds INTEGER NOT NULL, billed_seconds INTEGER NOT NULL, rate_per_minute NUMERIC(10,4) NOT NULL, cost NUMERIC(10,4) NOT NULL, vendor_rate_per_minute NUMERIC(10,4) NOT NULL, vendor_cost NUMERIC(10,4) NOT NULL, margin NUMERIC(10,4) NOT NULL, hangup_cause VARCHAR(64), status VARCHAR(20) NOT NULL DEFAULT 'completed', invoice_id BIGINT REFERENCES invoices(id) ON DELETE SET NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); ``` --- ## 2. Module Overview (Commercial & Business Value) * **Instant Financial Visibility:** Eliminates waiting for end-of-month carrier statements. Billing managers can monitor gross revenue, net carrier COGS (Cost of Goods Sold), and profit margins by second, minute, and day. * **Negative Margin Detection:** Alerts operators when misconfigured retail rate cards or carrier price changes result in loss-making traffic, enabling immediate LCR routing adjustments on Ring2All SBC. * **Carrier Dispute Reconciliation:** Detailed records capturing caller CLI, dialed destination, connect timestamp, duration, and vendor rate provide indisputable evidence during wholesale carrier billing disputes. * **Enterprise CSV Data Feeds:** One-click streaming export generates comprehensive CSV summaries ready for integration with external business intelligence dashboards and ERP databases. --- ## 3. 🎯 User Roles & Key Capabilities | User Role | Key Permissions | Core Responsibilities & Workflows | | :--- | :--- | :--- | | **Super Administrator** | Full Control & Re-rating Triggers | Oversees macro financial margins, triggers batch re-rating upon retroactive rate changes, and sets system-wide margin alert thresholds. | | **Billing & Financial Analyst** | Read, Filter, Export CSV | Audits traffic profitability by destination and customer, investigates zero-duration or failed call anomalies, and generates customer CDR reports. | | **Carrier Relations Manager** | Vendor Cost Audit | Compares vendor cost against upstream carrier invoices, validates carrier billing increments, and negotiates wholesale volume discounts. | | **Enterprise Customer** | Self-Care Read-Only CDRs | Inspects company-wide call usage, analyzes department calling spend, and downloads monthly call detail spreadsheets. | --- ## 4. Visual Interface & Form Structure ### 4.1 Rated CDRs Management (List View) The **Rated CDRs Explorer** provides a high-density, real-time DataGrid with KPI metric ribbons (Total Billed Calls, Total Retail Revenue, Total Vendor Cost, and Net Profit Margin), customer/carrier dropdown filters, date range selectors, and instant CSV export. ![Rated CDRs List](/screenshots/billing/reports/financial/cdrs/cdrs-list.png) ### 4.2 DataGrid Field Reference | Column Header | Data Source | Format | Description & Financial Significance | | :--- | :--- | :--- | :--- | | **Call UUID & Time** | `call_uuid`, `created_at` | Monospace String / Timestamp | Unique telecom session identifier and UTC start time. | | **Caller (CLI)** | `caller_number` | E.164 String | Originating phone number presented to the network. | | **Destination Number** | `destination_number` | E.164 String | Dialed target number with detected national/international country code. | | **Destination Name** | `destination_name` | String | Geographical routing tag (e.g. `USA California San Francisco`, `United Kingdom Mobile`). | | **Customer Account** | `customer_id` | String / Account Code | Name and account ID of the enterprise customer charged for the call. | | **Carrier Provider** | `did_provider_id` | String | Terminating carrier used for outbound completion (e.g. `Telnyx`, `Twilio`). | | **Duration (Sec)** | `duration_seconds` / `billed_seconds` | Integer (s) | Raw call duration alongside billed billable duration after applying increment rounding. | | **Customer Cost** | `cost` | `$0.0000` | Net monetary amount debited from the customer wallet or billed on invoice. | | **Vendor Cost** | `vendor_cost` | `$0.0000` | Wholesale cost charged by the terminating carrier provider. | | **Gross Margin** | `margin` | `$0.0000` (`%`) | Gross dollar profit and percentage margin generated by the call. | | **Hangup Cause** | `hangup_cause` | Monospace String | Q.850 / SIP release code (e.g. `NORMAL_CLEARING`, `USER_BUSY`, `NO_ANSWER`). | --- ## 5. Architectural Flow & Real-Time Rating Pipeline ``` ┌────────────────────────────────────────────────────────────────────────┐ │ Call Termination on Ring2All SBC / Telephony Core │ └───────────────────────────────────┬────────────────────────────────────┘ │ (JSON CDR Event / Socket Hook) ▼ ┌────────────────────────────────────────────────────────────────────────┐ │ Step 1: Prefix Matching & Rate Resolution │ │ • Longest-prefix match against Customer Retail Rate Card │ │ • Longest-prefix match against Carrier Wholesale Rate Card │ └───────────────────────────────────┬────────────────────────────────────┘ │ ▼ ┌────────────────────────────────────────────────────────────────────────┐ │ Step 2: Interval Rounding & Rating Calculations │ │ • Apply initial/increment rule (e.g., 30s minimum + 6s steps) │ │ • Compute: `Customer Cost = Connect Fee + (Billed Min * Retail Rate)` │ │ • Compute: `Carrier Cost = (Billed Min * Wholesale Rate)` │ │ • Compute: `Margin = Customer Cost - Carrier Cost` │ └───────────────────────────────────┬────────────────────────────────────┘ │ ▼ ┌────────────────────────────────────────────────────────────────────────┐ │ Step 3: Atomic Ingestion & Balance Deduction │ │ • Insert rated record into `public.cdrs_rated` │ │ • Deduct `cost` from Customer wallet balance if Prepaid │ │ • Update real-time KPI metrics cache in Redis │ └────────────────────────────────────────────────────────────────────────┘ ``` --- ## 6. Common Scenarios & Operational Playbooks ### Scenario A: Identifying and Mitigating Negative Margin Routes 1. On the **Rated CDRs** page, filter by Date Range: **This Month**. 2. Click the **Gross Margin** column header to sort ascending. 3. If any calls display negative margin values (colored red), inspect the destination prefix and terminating carrier. 4. Update the customer's Rate Card to increase retail rates, or adjust LCR routing on **Ring2All SBC** to route the destination via a lower-cost carrier. ### Scenario B: Generating an Audited CSV Export for Carrier Billing Reconciliation 1. Filter the DataGrid by the target wholesale carrier (e.g. `Telnyx Wholesale Carrier`) and target date range. 2. Click **Export CSV** in the toolbar. 3. The browser downloads an institutional CSV file `ring2all-billing-cdrs-YYYY-MM-DD.csv` containing raw timestamps, prefixes, exact billed seconds, and vendor costs for line-by-line comparison with the carrier's invoice. --- ## 7. Troubleshooting & Diagnostic Commands ### Query High-Volume Low-Margin Destinations ```sql SELECT destination_name, prefix, count(*) as total_calls, sum(duration_seconds) / 60 as total_minutes, sum(cost) as total_revenue, sum(vendor_cost) as total_cogs, sum(margin) as gross_profit, round((sum(margin) / NULLIF(sum(cost), 0)) * 100, 2) as margin_percent FROM cdrs_rated WHERE created_at >= NOW() - INTERVAL '7 days' GROUP BY destination_name, prefix ORDER BY total_calls DESC LIMIT 15; ``` ### Check Zero-Duration or Unbilled Rating Discrepancies ```sql SELECT count(*) as unbilled_calls, sum(cost) as unbilled_total FROM cdrs_rated WHERE invoice_id IS NULL; ``` --- ## 8. Model Context Protocol (MCP) AI Integration The **Rated CDRs & Profit Margin Analytics** module connects directly to the **Ring2All BSS MCP Server**, empowering AI data analysts and finance copilots to query call detail records, compute destination margins, and detect low-margin traffic. ### Available MCP Tools | Tool Name | Access Role | Description & Primary Function | Example Arguments | | :--- | :--- | :--- | :--- | | `query_rated_cdrs` | `Billing Operations` / `Admin` | Searches and analyzes rated Call Detail Records (CDRs) with margins, duration, destination, and customer details. | `{"customerId": 1, "destination": "+1305", "limit": 10}` | | `diagnose_unrated_cdrs` | `Billing Operations` / `Admin` | Scans CDRs for unbilled or zero-cost calls ($0.00 with duration > 0s), aggregates unbilled minutes, potential revenue loss, and identifies unrated destination prefixes. | `{"timeframeHours": 24, "limit": 10}` | | `diagnose_margin_leakage` | `Billing Operations` / `Admin` | Audits carrier route margins and vendor wholesale costs to detect negative margins, financial deficits, and unprofitable termination routes. | `{"minMarginPercent": 10, "timeframeHours": 24}` | | `diagnose_ocs_realtime_pipeline` | `Telecom Engineer` / `Admin` | Runs deep latency and health check on the Online Charging System (OCS): database latency, balance locks, and registered voice node connectivity (PBX and SBC). | `{}` | ### Sample MCP Tool Execution: `query_rated_cdrs` #### Request Payload ```json { "name": "query_rated_cdrs", "arguments": { "customerId": 1, "limit": 5 } } ``` #### Response Payload ```json [ { "id": 48201, "callerNumber": "+13055550199", "destinationNumber": "+442079460991", "destinationName": "United Kingdom - London", "durationSeconds": 184, "customerCost": 0.0368, "vendorCost": 0.0150, "grossMargin": 0.0218, "hangupCause": "NORMAL_CLEARING", "customer": { "id": 1, "name": "Rodrigo Cuadra" }, "createdAt": "2026-09-09T04:45:10Z" } ] ``` ### Conversational AI Prompts for Copilot * *"Diagnose if there are unrated CDRs or unbilled calls in the last 24 hours."* * *"Scan for margin leakage and negative profit routes with margins below 15%."* * *"Run a health check on the real-time OCS charging pipeline and telecom nodes."* * *"Show the last 10 rated calls placed by customer ID 1."* * *"Find all calls to international destinations with gross margins below 10%."* * *"What is our total call volume and revenue generated over the last 24 hours?"* --- ## 9. Glossary * **Billed Seconds:** The billable call duration resulting from applying contractual interval rules (e.g. 30s minimum followed by 6s increments). * **COGS (Cost of Goods Sold):** The direct wholesale expenses incurred from upstream carrier providers for terminating voice minutes. * **Gross Margin:** The monetary difference between what the customer was billed and what the carrier charged. * **Q.850 Hangup Cause:** International telecom signaling standard defining how a call was disconnected (e.g., Code 16 `NORMAL_CLEARING`). * **Model Context Protocol (MCP):** Open protocol standard that enables secure, controlled integration between Large Language Models and external tools, databases, and telecom rating engines.