---

# Voice Billing System Design
**Created at** 2025-07-11
**Created by** Manoj Vala

---

## **Overview:**
---
Each **workspace** can have multiple **packages**, which define call minutes and pricing logic. Calls consume these packages, and billing includes:

* Minimum call duration,
* Overdue time penalties,
* Concurrency penalties,
* Notification for usage/expiry,
* A running balance (even negative).

---

## 🧱 1. DATABASE SCHEMA DESIGN

### 🏢 `Workspace`==`Tenant` New column:

allowed_overdue_seconds (Integer DEFAULT 7200) -- 120 mins
remaining_billable_seconds (Integer DEFAULT 0)
remaining_billable_reason_note (Text DEFAULT "")

### 📦 `KalimeraPackage`

- We have some Predefined Packages which can be assigned to any workspace and also can create new custom packages.:

id (UUID, PK)
tenant_id (FK to Tenant)
name (String)
package_type (String)
total_storage (Integer)
total_seconds (Integer)               -- Total package seconds
minimum_charge_seconds (Integer)          -- e.g., 30
overdue_seconds (Integer)             -- charge block, e.g., 60 seconds
overdue_charge_seconds (Integer)      -- how many seconds to add per block
number_of_concurent_requests (Integer)
concurrency_penalty_seconds (Integer) -- penalty per concurrency
is_active (Boolean)
created_by (FK to Account)
created_at, updated_at

### 📊 `KalimeraBilling`

id (UUID, PK)
tenant_id (FK to Tenant)
package_id (FK to Package)
total_seconds (Integer)
total_consumed_seconds (Integer)
total_remaining_seconds (Integer)
total_storage (Integer)
is_expired = (Boolean, server_default=text('false'))
expired_at = (TimeStamp)
is_refunded = (Boolean, server_default=text('false'))
created_at, updated_at

### 📞 `KalimeraBillingLog` (Per call/transaction)

id (UUID, PK)
tenant_id (FK to Tenant)
agent_id (UUID)
conversation_id (UUID)
actual_call_seconds (Integer)
min_billed_seconds (Integer)
overdue_billed_seconds (Integer)
concurrency_billed_seconds (Integer)
total_billed_seconds (Integer)
total_remaining_seconds (Integer)
seconds_billed_from (Array(package_1_id: seconds, package_2_id: seconds))
created_at, updated_at

### 📉 `NegativeUsage`

- This will be managed in workspace.
- Because package can be many and when all are expired we track NegativeUsage.
- So, after deactivated or expired packages we are not addressing it again for performance purposes.

### 📈 `KalimeraWorkspaceUsageStats` (Aggregated data)

id (UUID, PK)
workspace_id (FK)
total_seconds (Integer)
remaining_seconds (Integer)
consumed_seconds (Integer)
overdue_seconds (Integer)
concurrency_seconds (Integer)
storage_billed_seconds (Integer)
created_at, updated_at

erDiagram
    WORKSPACE ||--o{ PACKAGE : "1:N"
    WORKSPACE ||--o{ WORKSPACE_USAGE_STATS : "1:1"
    WORKSPACE ||--o{ CALL : "1:N"
    
    PACKAGE ||--o{ PACKAGE_USAGE : "1:N"
    PACKAGE ||--o{ BILLING_LOG : "1:N"
    
    CALL ||--o{ BILLING_LOG : "1:N"

    WORKSPACE {
        string id PK
        string name
        int overdue_time
        int allowed_overdue_seconds
        bool is_active
        datetime created_at
        datetime updated_at
    }

    PACKAGE {
        string id PK
        string workspace_id FK
        string name
        int total_seconds
        int min_charge_seconds
        int overdue_charge_unit
        int concurrent_threshold
        int concurrent_penalty_seconds
        datetime start_date
        datetime end_date
        string status
    }

    PACKAGE_USAGE {
        string package_id PK,FK
        int total_seconds
        int consumed_seconds
        int billed_seconds
        int remaining_seconds
        datetime last_used_at
        json notification_flags
    }

    WORKSPACE_USAGE_STATS {
        string workspace_id PK,FK
        int total_available_seconds
        int total_consumed_seconds
        int total_billed_seconds
        int billed_negative_seconds
        int max_concurrent_calls
        datetime last_recalculated_at
        int remaining_negative_seconds
        string reason_for_negative_seconds
    }

    CALL {
        string id PK
        string workspace_id FK
        datetime start_time
        datetime end_time
        int actual_seconds
        string from_number
        string to_number
        string call_type
        string status
    }

    BILLING_LOG {
        string id PK
        string workspace_id FK
        string package_id FK
        string call_id FK
        string billing_type
        int actual_seconds
        int billed_seconds
        int min_charge_seconds
        int overdue_seconds
        int concurrency_seconds
        datetime created_at
    }

---

## 🔁 2. CRON JOBS / BACKGROUND TASKS

### ⏱ Every X Minutes: `Recalculate Available Packages`

* For each workspace:

* Find all **non-expired** packages with remaining seconds.
* Identify **package\_1** and **package\_2** (first two).
* Update cache or usage stats.
* Concurrent penelty will be added in the last log of the day.

### Yearly deduct seconds for storage:

* From `upload_files` table we will get `used storage`
* Storage in MB and we deducts 400 seconds per 50MB storage.
* Calculation for `KalimeraWorkspaceUsageStats.storage_billed_seconds`

 50 / 50 = 1 * 400(sec) = 400
150 / 50 = 3 * 400(sec) = 1200

### 🧮 On Conversation End: `Billing Event Handler`

* Triggered by event queue (e.g., Celery).
* Calculate:

* `actual_call_seconds`
* If `actual < min`: use `min`
* If `actual > threshold`: calculate `overdue_billed_seconds`
* Assign call usage to `package_1` and `package_2` accordingly
* Deduct from `PackageUsage`

### 📆 Daily Cron: `Concurrency Penalty Handler`

* Check concurrent calls.
* Apply `concurrency_penalty_seconds` per policy.
* If workspace has no available units:

* Add record in `NegativeUsage`
* Update `WorkspaceUsageStats`

### 📤 Notification Cron

Send alerts:

* 80%, 90%, 95%, 100% usage

consumedseconds = 70
totalseconds = 100
totalseconds * 80 / 100 == result
duration : 12
consumedseconds >= result && result <= (duration + consumedseconds)
70 >= 80 && 80 <= 82

* Package expiration: 20, 10, 5, 0 days
* No active packages available
* Deducted Negative seconds

- Negative usage beyond allowed `overdue_seconds` → disable account

---

## 🧮 3. BILLING CALCULATION LOGIC

### When Call Ends:

1. **Select Package**:

* Use `package_1` → if exceeded, spill to `package_2`

2. **Billed Time Calculation**:

* `min_billed_seconds = max(actual_call_seconds, min_seconds_per_call)`
* `overdue_billed_seconds = floor((actual - overdue_threshold) / unit) * unit`
* `total_billed_seconds = min_billed + overdue + concurrency`
* `actual_seconds = consumed_seconds - (overdue_seconds + concurrency_seconds)`
* `billed_seconds = consumed_seconds`
* `consumption_percentage = (consumed_seconds * 100) / total_seconds`

3. **Deduct from Package(s)**:

* Use remaining seconds from `package_1`
* Then from `package_2`
* If both insufficient → push remaining to `NegativeUsage`

---

## 🎨 4. FRONTEND & VISUALIZATION

* **Billing Overview Dashboard**:

* Usage meter (used, available)
* Upcoming expirations
* Negative balance warning
* Package history

* **Admin Dashboard**:

* Manage packages
* Assign to workspace
* Set `allowed_overdue_seconds`
* View invoice numbers

---

## 🧾 5. PAYMENT FLOW

* Initially handled **manually**:

* Create package
* Assign invoice
* Set package as `enabled`

* Later: **Stripe Integration**

* Generate link
* Mark as paid when Stripe confirms
* Auto-activate package

---

## 📌 KEY NOTES FROM CONVERSATION

* Each call transaction must log:

* Actual seconds
* Minimum charged seconds
* Overdue charged seconds
* Package(s) used
* Overdue calculation is **in seconds**
* Concurrency penalty is run **once per day**
* If workspace has no units, allow up to **200 minutes overuse**, then block
* Maintain **negative balance table**
* Send regular usage & expiry notifications

---

## Conversation log:

"id", "campaign_id", "dialplan_id", "customer_id", "voice_id", "tenant_id", "voice_name", "voice_type", "from_number", "to_number", "sip_number", "sip_id", "call_type", "calltime", "day", "month", "year", "dialog_start_time", "dialog_end_time", "dialog_duration", "status", "auditor_comments", "comments", "agent_id", "version_number", "charge_minutes", "charge_timestamp", "charge_packet_id", "voice_input_only_url", "voice_output_only_url", "voice_dialog_url", "language", "conversation_metadata", "customer_data", "updated_at", "call_unique_id", "created_at", "status_description", "is_sorry_message", "is_delay_message", "is_active_call_type"
"dbf54f9b-c0e8-47a2-af3a-8acffc8435fe", "e76b0dac-2e1a-43a3-9109-9ca7f31867ae", "b6332de5-3d40-4578-943b-86801024a59d", "el-GR", "0035799674861", "[phone]", "e76b0dac-2e1a-43a3-9109-9ca7f31867ae", "inbound", "2025-07-09 12:15:53", "2025-07-09 09:16:04", "2025-07-09 09:16:48", 44, "Answer", "05c7456e-c67d-4e30-94d6-f6a9e7841df7", "el-GR", """kalimera/b6332de5-3d40-4578-943b-86801024a59d/voice/dbf54f9b-c0e8-47a2-af3a-8acffc8435fe.json""", "{""summary"": ""The conversation is a phone call from a representative named Theodoros from a store called Alpha regarding school needs for the new year. The caller is in communication with an automated assistant and asks if it's a good time to talk. The mother expresses gratitude for the time and confirms she is the parent or guardian responsible for the children's school needs. The representative apologizes twice for not hearing the response. The conversation seems to be a polite and standard interaction regarding school-related matters, with the representative trying to confirm the caller's role and offer assistance."", ""sentiment"": {""overall"": {""label"": ""neutral"", ""neutral"": 1.0, ""compound"": 0.0, ""negative"": 0.0, ""positive"": 0.0}}, ""agent_name"": ""Back-To-School Sales"", ""analyzed_at"": ""2025-07-09 09:16:51.045603"", ""customer_name"": ""Unknown"", ""summarization_time"": ""1.63 seconds"", ""customer_message_count"": 1, ""sentiment_analysis_time"": ""0.00 seconds""}", "2025-07-09 09:16:51.128408", "AI20-1752052553.3762", "2025-07-09 09:16:03.383993", false, false, false

---

## Calculation: packages, billing and tenant.

A workspace has overdue time and in packages we have charges for overdue time. and a workspace has multiple package. We have a join table of workspace and packages called KalimeraBilling.
IN that table we have package id, total seconds, billed seconds, remaining seconds, start and end date of package, total storage, consumed storage. and others for statistics if needed.

A package have following fields:
- package name, total seconds, minimum charges per call(like 10, then if call duration is below 10 seconds then we will deduct for 10 seconds otherwise not), overdue charge(from workspace overdue time, like it's 60 seconds then call duration is 730 seconds then 730/60 = 12.166666667 (12 will be used)).

From this above configurations we need to calculate the billing.

---

### Billing calculations per call end:

graph TD
  A["Call Ends → Billing Triggered"] --> B["Extract Call Duration"]
  B --> C["Fetch Workspace + KalimeraBilling"]
  C --> D["Get Package Config: MinChargeSeconds, OverdueUnit, etc."]
  D --> E{"Apply Minimum Charge: max(duration, min)"}
  E --> F{"Duration > Workspace Overdue Time?"}
  F -- Yes --> G["Calculate Overdue Blocks: floor(duration / overdue_time)"]
  G --> H["Overdue = blocks × overdue_unit"]
  F -- No --> I["Overdue = 0"]
  H --> J["Total Billed = Billed Time + Overdue"]
  I --> J
  J --> K["Deduct from KalimeraBilling Packages"]
  K --> L{"Remaining > 0?"}
  L -- Yes --> M["Log Negative Usage"]
  L -- No --> N["Continue"]
  M --> O["Create BillingLog Entry"]
  N --> O
  O --> P["Done"]

---
**billing logic and Python-style pseudocode** that works with:

* `Workspace.overdue_time`
* `Package.min_charge_seconds`
* `Package.overdue_charge_unit`
* `KalimeraBilling` join table (for available time/storage)

---

## 🧠 **Examples Billing Calculation Logic**

### 🧾 Sample Parameters

Let’s assume:

* `workspace.overdue_time = 60s`
* `package.min_charge_seconds = 10s`
* `package.overdue_charge_unit = 15s`

### 🧮 Step-by-Step Calculation

1. **Actual call duration**: `duration = dialog_end_time - dialog_start_time`

2. **Apply minimum billing rule**:

   billed_time = max(actual_time, min_charge_seconds)

3. **Calculate overdue time**:
If `actual_time > workspace.overdue_time`, then:

   overdue_units = floor(actual_time / workspace.overdue_time)
   overdue_billed_time = overdue_units * package.overdue_charge_unit

> Note: This is **not added** to billed time but charged additionally.

4. **Total chargeable seconds**:

   total_billed = billed_time + overdue_billed_time

---

## 🧾 Example

If a call lasted **730 seconds**, `workspace.overdue_time = 60`, `package.overdue_charge_unit = 15`:

overdue_units = floor(730 / 60) = 12
overdue_billed_time = 12 * 15 = 180s

If `min_charge_seconds = 10`, and actual = 730 → billed\_time = 730

So `total_billed = 730 + 180 = 910s`

---

## ⚙️ Allocation from Packages

### From `KalimeraBilling`:

* Get active records for the workspace where:

  start_date <= now AND end_date >= now AND remaining_seconds > 0
  ORDER BY start_date ASC

Distribute `total_billed` across packages sequentially:

remaining_to_bill = total_billed
for billing_record in kalimera_packages:
    available = billing_record.remaining_seconds
    if available >= remaining_to_bill:
        billing_record.billed_seconds += remaining_to_bill
        billing_record.remaining_seconds -= remaining_to_bill
        log.append((billing_record.package_id, remaining_to_bill))
        break
    else:
        billing_record.billed_seconds += available
        billing_record.remaining_seconds = 0
        log.append((billing_record.package_id, available))
        remaining_to_bill -= available

If `remaining_to_bill > 0` → log negative usage entry.

---

## ✅ Final Billing Log Schema (Example)

| Field | Value |
| ---------------------- | -------------------------- |
| agent\_id | UUID from log |
| workspace\_id | from `tenant_id` |
| actual\_duration | 730 |
| min\_charge\_seconds | 10 |
| billed\_time | 730 |
| overdue\_time | 180 |
| total\_billed\_seconds | 910 |
| package\_distribution | List of (pkg\_id, seconds) |
| negative\_usage | e.g., 90s (if any) |

---

### Concurrent penelty cron job:
it runs once per day. and cron job will find the concurrency seconds used and charge billing for that and make log.

for calculating that we have some fields in packages.
- concurrent request(e.g. 10) and concurrent penelty seconds(5 seconds).
So, when I calls are happening concurrently more than 10 then multiply 5 seconds with 2(over the limit) so it's 10 seconds will be deducted from the KalimeraBilling table and make logs.

Now we have the below example to calculate efficiently:
1. starts: 12:30:00 --> ends: 12:31:00
2. starts: 12:30:10 --> ends: 12:31:10

then concurrent seconds are 20 seconds. we will multiply it with penelty seconds and deduct from billing and make logs.

# Example of concurrent calls: 9, 14, 24, 36, 51
# concurrent_threshold = 10 calls
# concurrent_charges   = 50 seconds
9  - 10 == -1:              NO
14 - 10 == 04/10 > 1 * 50 = 50
24 - 10 == 14/10 > 2 * 50 = 100
36 - 10 == 26/10 > 3 * 50 = 150
51 - 10 == 41/10 > 5 * 50 = 250

#### Sweep Line Algorithm to find concurrent users:

from datetime import datetime

# Input table rows as (start_time, end_time)
raw_call_data = [
    ("2025-07-10 06:45:04", "2025-07-10 06:46:12"),
    ("2025-07-10 06:45:04", "2025-07-10 06:45:51"),
    ("2025-07-10 06:45:03", "2025-07-10 06:45:26"),
    ("2025-07-10 06:45:02", "2025-07-10 06:45:25"),
    ("2025-07-10 06:45:01", "2025-07-10 06:45:48"),
    ("2025-07-10 06:45:00", "2025-07-10 06:45:20"),
    ("2025-07-10 06:39:33", "2025-07-10 06:39:50"),
    ("2025-07-10 06:39:25", "2025-07-10 06:39:53"),
    ("2025-07-10 06:09:36", "2025-07-10 06:12:46"),
    ("2025-07-10 06:09:18", "2025-07-10 06:09:32"),
]

# Convert to datetime and create event list
events = []
for start_str, end_str in raw_call_data:
    start = datetime.strptime(start_str, "%Y-%m-%d %H:%M:%S")
    end = datetime.strptime(end_str, "%Y-%m-%d %H:%M:%S")
    events.append((start, +1))
    events.append((end, -1))

# Sort events
events.sort()

# Sweep to find max concurrency
concurrent = 0
max_concurrent = 0

for timestamp, delta in events:
    concurrent += delta
    if concurrent > max_concurrent:
        max_concurrent = concurrent

max_concurrent

---

graph TD
  A["Start Cron Job<br>(Daily)"] --> B["Fetch Today's Calls"]
  B --> C["Group Calls by Workspace"]
  C --> D["For each Workspace:<br>Group by Phone Number"]
  D --> E["For each Number:<br>Build (start, +1)<br>and (end, -1) Events"]
  E --> F["Sort Events<br>Sweep to Count<br>Concurrent Calls"]
  F --> G{"Concurrent > Limit?"}
  G -- Yes --> H["Calculate<br>Over-Concurrent Seconds"]
  G -- No --> I["Continue"] --> Q
  H --> J["Sum All<br>Over-Concurrent Seconds"]
  J --> K["Calculate Penalty:<br>OverSeconds × PenaltyUnit"]
  K --> L["Deduct from<br>KalimeraBilling"]
  L --> M{"Penalty Remaining?"}
  M -- Yes --> N["Log Negative Usage"]
  M -- No --> O["Proceed"]
  O --> P["Create BillingLog Entry"]
  P --> Q["Next Workspace ?"]
  Q --> R["End Cron Job"]

---