Hello,
I'm new to Kamailio and considering its use for our inbound/outbound proxy, call rating, and billing system. I'd like to get your feedback on my current approach and understand the best practices for this scenario.
Currently, I'm using SQL queries within my Kamailio configuration to handle authorization and rating. Here's a simplified example:

# Call authorization
route[DISPATCH] {
        if (is_method("INVITE") && isbflagset(FLB_SRC_CARRIER)) {

                if ($au == $null) {
                        $var(acc_query) = $_s(SELECT ct.tag as tag, ct.customer_id as customer_id, cast(cb.nobal_amt as integer) as nobal_amount, cast(cb.cash as integer) as cash, ct.digits, cast(ct.retail_rate as varchar) as retail_rate, cast(ct.wholesale_rate as varchar) as wholesale_rate, ct.dispatcher_group, cast(ct.cps as integer) as cps from customer_ip ip INNER JOIN customer_balance cb ON (ip.customer_id = cb.customer_id) INNER JOIN customer_tariff ct ON (ip.customer_id = ct.customer_id) INNER JOIN customers cust ON (ip.customer_id = cust.customer_id)  WHERE ip_address = '$si' AND '$rU' LIKE ct.digits || '%' order by ct.digits desc limit 1;);
                } else {
                        $var(acc_query) = $_s(SELECT ct.tag as tag, ct.customer_id as customer_id, cast(cb.nobal_amt as integer) as nobal_amount,cast(cb.cash as integer) as cash, ct.digits, cast(ct.retail_rate as varchar) as retail_rate, cast(ct.wholesale_rate as varchar) as wholesale_rate, ct.dispatcher_group, cast(ct.cps as integer) as cps from customer_auth auth INNER JOIN customer_balance cb ON (auth.customer_id = cb.customer_id) INNER JOIN customer_tariff ct ON (auth.customer_id = ct.customer_id) INNER JOIN customers cust ON (auth.customer_id = cust.customer_id) WHERE auth.subcriber = '$au' AND '$rU' LIKE ct.digits || '%' order by ct.digits desc limit 1;);

                        $dlg_var(auth) = "1";

                }
                xalert("query is $var(acc_query)");
                if (sql_xquery("dbh", "$var(acc_query)", "row") == 1) {
                        $dlg_var(account) = $xavp(row[0]=>customer_id);
                        $dlg_var(dispatcher_group) = $xavp(row[0]=>dispatcher_group);
                        $dlg_var(retail_rate) = $xavp(row[0]=>retail_rate);
                        $dlg_var(wholesale_rate) = $xavp(row[0]=>wholesale_rate);
                        $dlg_var(cps) = $xavp(row[0]=>cps);
                        $dlg_var(service_plan) = $xavp(row[0]=>tag);
                        # $var(cash) = $xavp(row[0]=>cash);
                        xalert("account: $xavp(row[0]=>customer_id) cash: $xavp(row[0]=>cash) retail_rate: $xavp(row[0]=>retail_rate) wholesale rate: $xavp(row[0]=>wholesale_rate) group: $xavp(row[0]=>dispatcher_group) cps: $xavp(row[0]=>cps) tag: $xavp(row[0]=>tag) \n");
                        if ($xavp(row[0]=>cash) <= $xavp(row[0]=>nobal_amount)) {
                                sl_send_reply("403", "Insufficient balance");
                                exit;
                        }

                        append_hf("X-Account: $dlg_var(account),$dlg_var(retail_rate),$dlg_var(wholesale_rate)\r\n");

                } else {
                        sl_send_reply("500", "Server Error!");
                        exit;
                }


#!ifdef WITH_CPSLIMIT
                # perform pipe match for INVITE
                if (is_method("INVITE")) {
                        $var(invlimit) = $(dlg_var(cps){s.int});
                        if ($var(invlimit) > 0) {
                                if (!pl_check("$si-$dlg_var(dispatcher_group)", "TAILDROP", "$var(invlimit)")) {
                                        pl_drop("5");
                                        exit;
                                }
                        }
                }
#!endif

                # round robin dispatching on gateways group '1'
                if(!ds_select_dst("$dlg_var(dispatcher_group)", "4")) {
                        send_reply("404", "No destination");
                        exit;
                }

                t_on_failure("RTF_DISPATCH");
                route(RELAY);
                exit;
        }
}


……
…….
……

# Billing and Rating using acc_cdrs and a customer_balance table
event_route[dialog:end] {
                $var(call_duration) = $DLG_lifetime;  # Fetch call duration
                $avp(originator) = $dlg_var(originator);
                $avp(accountcode) = $dlg_var(account);
                $avp(caller) = $dlg_var(caller);
                $avp(callee) = $dlg_var(callee);
                $avp(pdd) = $dlg_var(pdd);
                $avp(service_plan) = $dlg_var(service_plan);
                $avp(hangup_source) = 'CALLEE';

                $avp(hangup_code) = '200';
                $avp(hangup_reason) = 'Normal Clearing';

                $avp(elapsed) = ( $TV(s) - $dlg_var(init_timestamp) );

                if ($dlg_var(auth) != $null) {
                        setbflag(FLB_SRC_CARRIER);
                }


                if (isbflagset(FLB_SRC_CARRIER) && $dlg_var(originator) == "CUSTOMER") {
                        $avp(hangup_source) = 'CALLER';
                }
                if (isbflagset(FLB_SRC_PBX) && $dlg_var(originator) == "SYSTEM") {
                        $avp(hangup_source) = 'CALLER';
                }


                # Calculate billed amount using postgres query
                $var(bill_query) = $_s(select cast(round($dlg_var(retail_rate)/60::numeric * $var(call_duration), 4) as varchar) as bill;);

                if (sql_xquery("dbh", "$var(bill_query)", "row") == 1) {
                        $avp(billed) = $xavp(row[0]=>bill);
                }

                if (isbflagset(FLB_SRC_CARRIER)) {
                        $var(query) = "UPDATE customer_balance set cash = cash - (" + $dlg_var(retail_rate) + " / 60 * " + $var(call_duration) + ") WHERE customer_id = " + $dlg_var(account) + ";";
                        sql_xquery("dbh", "$var(query)", "ra");
                }
                # xalert("~~~~~ Call ended (duration: $var(call_duration)) total_billed: $avp(billed) ~ $avp(hangup_source) ~ $T(reply_code) ~ $T(reply_reason) ~ $T_reply_code ~  \n");

}


My questions are:
    • Is this a sustainable approach for a Kamailio billing/rating setup? Are there performance or scalability concerns?
    • Would it be better to handle rating and billing directly on our FreeSWITCH B2BUA? What are the advantages and disadvantages of each approach?
    • Are there best practices or alternative solutions I should consider?
Thank you in advance for sharing your experience and insights!