10 — Billing System

System: PyroRadius ISP Billing & RADIUS Management
Stack: Laravel 13 · PostgreSQL 16 · Redis
Last Updated: 2026-07-13

---

Overview

The billing system handles the complete financial lifecycle of ISP customers: invoice generation, payment recording, ledger double-entry, expiry management, and month-end archival. It is designed to be idempotent (safe to retry), resilient to PostgreSQL deadlocks, and consistent through database transactions.

The core logic lives in two service classes: - App\Services\BillingService — invoice generation, payment recording, renewal - App\Services\BillingArchiveService — month-end archival process

---

Invoice Lifecycle

CREATED (unpaid)
     │
     │  partial payment received
     ▼
  PARTIAL
     │
     │  remaining balance paid
     ▼
    PAID
     │
     │  month-end archive run
     ▼
  ARCHIVED

Additionally: - An invoice can go directly from unpaid → paid if the full amount is paid in one transaction. - An invoice in paid state can be reversed (creates a reversal payment, restores invoice to unpaid, removes RADIUS if expiry is rolled back). - Invoices in any state can be voided by a super admin (marks as voided, no ledger reversal — used for erroneous invoices only).

Invoice Fields

Field	Type	Description
id	bigint	Primary key
invoice_no	varchar	Sequential number, e.g. INV202506-00001
customer_id	bigint	FK to customers
package_id	bigint	Package at time of invoice (snapshot, not FK-enforced)
amount	numeric(10,2)	Total amount due
paid	numeric(10,2)	Total amount paid so far
status	varchar	unpaid, partial, paid, voided, archived
period_start	date	Billing period start
period_end	date	Billing period end
due_date	date	Payment due date
notes	text	Optional admin notes
is_archived	boolean	True after month-end archive
archived_at	timestamptz	When archived
created_by	bigint	FK to users
created_at	timestamptz
updated_at	timestamptz

---

BillingService::generateInvoice()

Generates an invoice for a customer. This method is idempotent: if an invoice already exists for the same customer and period, it returns the existing invoice without creating a duplicate.

public function generateInvoice(
    Customer $customer,
    Carbon   $periodStart,
    Carbon   $periodEnd,
    ?float   $amount = null,      // defaults to package price
    ?string  $notes = null,
    bool     $processPayment = false,  // if true, also records a payment immediately
    ?float   $paymentAmount = null,
    ?string  $paymentMethod = null,
): Invoice

Idempotency Check

Before creating an invoice, generateInvoice() queries:

SELECT id FROM invoices
WHERE customer_id = ?
  AND period_start = ?
  AND period_end = ?
  AND status != 'voided'
LIMIT 1;

If a row is found, it is returned immediately. This prevents double-invoicing when a command runs twice (e.g., if the scheduler fires the GenerateMonthlyInvoices command twice due to a server restart).

Invoice Number Generation

$seq = DB::selectOne("SELECT nextval('seq_invoice_no') AS n")->n;
$invoiceNo = 'INV' . now()->format('Ym') . '-' . str_pad($seq, 5, '0', STR_PAD_LEFT);
// INV202506-00001, INV202506-00002, ...

The PostgreSQL sequence guarantees uniqueness without table-level locks. The sequence increments monotonically and never resets, even across months — the month prefix in the invoice number provides the visual grouping.

---

BillingService::recordPayment()

The core payment recording method. Creates a Payment record, two LedgerEntry records (one for the customer account, one for the dealer if applicable), applies the payment to outstanding invoice(s), calls renew() if fully paid, syncs RADIUS, and sends a WhatsApp message.

public function recordPayment(
    Customer $customer,
    float    $amount,
    string   $method,
    ?string  $notes,
    ?int     $invoiceId,       // specific invoice to apply to (or null for auto-apply)
    User     $recordedBy,
): Payment

Payment Recording Flow

POST /customers/{id}/recharge
         │
         ▼
CustomerController::recharge()
    ├── RechargeRequest::validate()
    └── BillingService::recordPayment()
              │
              ▼
         DB::transaction() {
              │
              ├── SET LOCAL lock_timeout = '3s'
              │
              ├── Create Payment record
              │     invoice_no ← nextval('seq_receipt_no')
              │     receipt_no: RCP202506-00001
              │
              ├── Create LedgerEntry (customer) — CREDIT
              │     customer_id, amount, type='credit',
              │     description='Payment received — Cash',
              │     reference=receipt_no
              │
              ├── [if customer has dealer]
              │   Create LedgerEntry (dealer account) — CREDIT
              │     dealer gets commission entry
              │
              ├── Apply to invoice(s):
              │     if invoiceId provided → apply to that invoice only
              │     else → apply to oldest unpaid invoices first (FIFO)
              │
              ├── Update invoice.paid += amount_applied
              │   Update invoice.status:
              │     paid == amount  → 'paid'
              │     paid < amount   → 'partial'
              │
              ├── if invoice fully paid:
              │   BillingService::renew($customer, $invoice)
              │       ├── Calculate new expiry_date
              │       │     (current expiry + package duration, or today + duration
              │       │      if currently expired)
              │       ├── UPDATE customers SET expiry_date = ?, status = 'active'
              │       └── RadiusService::syncCustomer($customer)
              │             ├── Write radcheck (username/password)
              │             ├── Write radusergroup (package group)
              │             └── Write radgroupreply (speed attributes)
              │
              └── } end transaction
         │
         ├── WhatsAppService::sendToCustomer($customer, 'payment_received', [...])
         │     (dispatches SendWhatsAppMessage job to queue)
         │
         ├── AuditLogger::log('customer.recharged', $customer, [...])
         │
         └── event(new CustomerRecharged($customer, $payment))
                   └── SendPaymentWhatsApp listener (queued)

Receipt Number Generation

Same pattern as invoice numbers, using a separate sequence:

$seq = DB::selectOne("SELECT nextval('seq_receipt_no') AS n")->n;
$receiptNo = 'RCP' . now()->format('Ym') . '-' . str_pad($seq, 5, '0', STR_PAD_LEFT);

---

Ledger Double-Entry System

Every financial movement creates two ledger entries — one on the customer's account and one on the dealer's account (if the customer has a dealer). This provides an auditable trail and allows per-dealer revenue reports.

LedgerEntry Fields

Field	Type	Description
id	bigint	Primary key
account_type	varchar	customer or dealer
account_id	bigint	customer_id or dealer user_id
type	varchar	debit (amount owed) or credit (amount paid)
amount	numeric(10,2)	Absolute value
description	varchar	Human-readable description
reference	varchar	Invoice no or receipt no
payment_id	bigint	FK to payments (nullable, for credits)
invoice_id	bigint	FK to invoices (nullable, for debits)
created_at	timestamptz

Entry Creation on Invoice Generation

When an invoice is generated for a customer: - DEBIT entry on customer account: amount = invoice.amount, description = "Invoice INV202506-00038"

When the customer has a dealer: - DEBIT entry on dealer account: amount = invoice.amount, description = "Customer invoice INV202506-00038 (Ahmad Khan)"

Entry Creation on Payment Recording

When a payment is recorded: - CREDIT entry on customer account: amount = payment.amount, description = "Payment received — Cash"

When the customer has a dealer: - CREDIT entry on dealer account: amount = dealer_commission_amount, description = "Commission on RCP202506-00043"

---

Expiry Date Management

How Expiry Works

Each customer has an expiry_date column. The CheckExpiry artisan command runs every hour and:

1. Finds all customers where status = 'active' AND expiry_date < NOW().
2. For each: calls RadiusService::deleteCustomerRadius() to remove their RADIUS entries.
3. Updates status = 'expired'.
4. Fires CustomerExpired event (sends WhatsApp if configured).

Renewal Logic in renew()

private function renew(Customer $customer, Invoice $invoice): void
{
    $duration = $customer->package->duration_days;

    $newExpiry = $customer->expiry_date && $customer->expiry_date->isFuture()
        ? $customer->expiry_date->addDays($duration)   // extend from current expiry
        : now()->addDays($duration);                   // start fresh from today

    $customer->update([
        'expiry_date' => $newExpiry,
        'status'      => 'active',
    ]);
}

If the customer's expiry date is in the future (they paid early), the new expiry extends from their current expiry — they don't lose days by paying early. If the customer is already expired (paying late), the new expiry starts from today.

temp_extended Status (Grace Period)

Some customers are given a grace period after their expiry via the UI (manually setting status to temp_extended). The CheckExpiry command skips customers with this status. A separate UI on the customer show page allows admins to set/clear this status. When they pay, renew() clears it back to active.

---

Payment Methods

Method Key	Description
cash	Cash collected by agent/dealer
bank_transfer	Direct bank transfer
easypaisa	Easypaisa mobile wallet
jazzcash	JazzCash mobile wallet
card	Debit/credit card

The payment method is stored on the Payment record and shown in the receipt PDF.

---

Monthly Invoice Generation (GenerateMonthlyInvoices Command)

Runs on the 1st of each month at 02:00 via the scheduler. For each active customer whose package has billing_cycle = 'monthly':

1. Calls BillingService::generateInvoice() (idempotent — skips if already exists).
2. Dispatches GeneratePdfInvoice job to generate the PDF in the background.
3. Optionally sends an invoice notification WhatsApp if notify_on_invoice = true in settings.

The command processes customers in batches of 100 using SKIP LOCKED to support concurrent execution across multiple queue workers.

// app/Console/Commands/GenerateMonthlyInvoices.php

Customer::active()
    ->whereHas('package', fn($q) => $q->where('billing_cycle', 'monthly'))
    ->chunkById(100, function ($customers) {
        foreach ($customers as $customer) {
            $this->billing->generateInvoice(
                customer:    $customer,
                periodStart: now()->startOfMonth(),
                periodEnd:   now()->endOfMonth(),
                amount:      $customer->package->price,
            );
        }
    });

---

Overpayment Handling

If a customer pays more than their outstanding balance, the excess is tracked in customer.credit_balance. When the next invoice is generated, applyExcessToUnpaid() is called:

private function applyExcessToUnpaid(Customer $customer): void
{
    if ($customer->credit_balance <= 0) return;

    $unpaidInvoices = $customer->invoices()
        ->whereIn('status', ['unpaid', 'partial'])
        ->orderBy('created_at')
        ->get();

    foreach ($unpaidInvoices as $invoice) {
        if ($customer->credit_balance <= 0) break;

        $apply = min($customer->credit_balance, $invoice->amount - $invoice->paid);
        $invoice->increment('paid', $apply);
        $invoice->update(['status' => $invoice->paid >= $invoice->amount ? 'paid' : 'partial']);
        $customer->decrement('credit_balance', $apply);

        if ($invoice->status === 'paid') {
            $this->renew($customer, $invoice);
        }
    }
}

---

Deadlock Retry Logic

recordPayment() wraps the transaction in a retry loop. PostgreSQL deadlocks (error code 40P01) can occur when two concurrent payments attempt to lock the same customer/invoice rows in different orders.

public function recordPayment(...): Payment
{
    $attempts = 0;
    $maxAttempts = 3;

    while (true) {
        try {
            return DB::transaction(function () use (...) {
                DB::statement("SET LOCAL lock_timeout = '3s'");
                // ... payment logic
            });
        } catch (\Illuminate\Database\QueryException $e) {
            $pgCode = $e->getCode();
            $isDeadlock     = $pgCode === '40P01';
            $isLockTimeout  = $pgCode === '55P03';

            if (($isDeadlock || $isLockTimeout) && $attempts < $maxAttempts) {
                $attempts++;
                usleep(random_int(50000, 200000));  // 50–200ms random backoff
                continue;
            }
            throw $e;  // rethrow after max attempts or for other errors
        }
    }
}

---

Billing Archive System (BillingArchiveService)

At the end of each month (last day, 23:30), the archive command runs:

// app/Services/BillingArchiveService.php

public function archiveMonth(int $year, int $month): BillingArchive
{
    return DB::transaction(function () use ($year, $month) {
        $periodStart = Carbon::create($year, $month, 1)->startOfMonth();
        $periodEnd   = $periodStart->copy()->endOfMonth();

        // 1. Collect all paid invoices for the month
        $invoices = Invoice::whereBetween('period_start', [$periodStart, $periodEnd])
            ->where('status', 'paid')
            ->get();

        // 2. Create an archive summary record
        $archive = BillingArchive::create([
            'year'            => $year,
            'month'           => $month,
            'total_invoices'  => $invoices->count(),
            'total_revenue'   => $invoices->sum('amount'),
            'total_collected' => $invoices->sum('paid'),
        ]);

        // 3. Mark invoices as archived
        Invoice::whereIn('id', $invoices->pluck('id'))
            ->update(['is_archived' => true, 'archived_at' => now(), 'status' => 'archived']);

        // 4. Store a JSON snapshot of the archive for audit/reporting
        Storage::put(
            "archives/{$year}-{$month:02d}.json",
            $invoices->toJson()
        );

        return $archive;
    });
}

Purpose of the Archive

• Keeps the invoices table lean — archived invoices are still queryable but marked separately.
• The Billing/Archive UI provides a month-by-month revenue summary.
• The JSON snapshot in storage provides an immutable record even if database rows are later modified.

---

Invoice Batch System

The invoice batch system allows generating invoices for a specific subset of customers at once (e.g., "generate invoices for all DHA customers on the Premium package"). This is separate from the monthly automatic generation.

Billing/Batches/Create UI
    ↓
POST /billing/batches
    ↓
InvoiceBatchController::store()
    ↓
InvoiceBatch::create([
    'filters'    => $validated['filters'],   // JSON: area, package_id, nas_id, etc.
    'status'     => 'queued',
    'created_by' => auth()->id(),
])
    ↓
GenerateInvoiceBatchJob::dispatch($batch)
    ↓
[Queue Worker]
    ├── Resolve customers matching filters
    ├── For each: BillingService::generateInvoice()
    ├── Update batch.progress incrementally
    └── Set batch.status = 'completed' | 'failed'

The Billing/Batches/Show page polls /api/batches/{id}/progress every 5 seconds to show a live progress bar.

---

HealOverpaidInvoices Command

Occasionally, floating-point rounding across multiple partial payments can result in invoice.paid > invoice.amount by a fraction (e.g., Rs. 0.01). This leaves invoices in partial status when they should be paid. The HealOverpaidInvoices command runs daily at 04:00:

-- Find invoices where paid >= amount but status is not 'paid'
SELECT id, amount, paid
FROM invoices
WHERE paid >= amount
  AND status IN ('unpaid', 'partial')
  AND is_archived = FALSE;

For each found invoice: 1. Sets paid = amount (clips to exact amount). 2. Sets status = 'paid'. 3. Calls BillingService::renew() if the customer is not already active with a valid expiry. 4. Logs the correction to the audit log.

---

Payment Reversal

An admin with the reverse_payments permission can reverse a payment:

POST /billing/payments/{payment}/reverse
     │
     ├── Validate: reason required
     ├── Check: payment not already reversed
     └── BillingService::reversePayment($payment, $reason)
              │
              └── DB::transaction() {
                       ├── Create reversal Payment record (negative amount)
                       ├── Create DEBIT LedgerEntry (reversal)
                       ├── Reduce invoice.paid by reversed amount
                       ├── Update invoice.status accordingly
                       ├── If customer expiry was extended by this payment:
                       │     Roll back expiry_date
                       │     If now expired: RadiusService::deleteCustomerRadius()
                       ├── Mark original payment as reversed
                       └── AuditLogger::log('payment.reversed', ...)
              }

---

Key Database Tables

Table	Purpose
invoices	All invoice records
payments	All payment records (including reversals)
ledger_entries	Double-entry ledger for customer and dealer accounts
billing_archives	Month-end archive summary records
invoice_batches	Batch invoice generation jobs
cron_jobs	DB-driven scheduler configuration

Sequences

Sequence	Format	Example
seq_invoice_no	INV{YYYYMM}-{00001}	INV202506-00001
seq_receipt_no	RCP{YYYYMM}-{00001}	RCP202506-00043