Rated CDRs & Profit Margin Analytics 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 & Real-Time Rating Pipeline
6. Common Scenarios & Operational Playbooks
7. Troubleshooting & Diagnostic Commands
8. Model Context Protocol (MCP) AI Integration
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)

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.

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

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

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

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

Response Payload

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