IPv6 accounting RADIUS SQL schema?

[Michael Ducharme]

On 8/19/2018 2:47 AM, Nathan Ward wrote:
> I would query why that data is in your billing system, but, I’m sure you have your reasons.
> Generally I prefer to expose data to “external” systems through APIs, rather than give them direct database access and tightly couple two applications together though a DB schema which may need to change for one side or the other.

It is unfortunately extremely common for billing systems to integrate 
with FreeRADIUS/MySQL via direct database access. Ours uses the 
accounting data to determine the IP allocation history for the customer 
or the previous history for an IP, and staff can view the history 
records through the billing software itself. The billing system also 
continually monitors the radacct table for new records -- instead of 
sending an access-reject to a customer who is behind on billing, they 
are sent an access-accept. A few seconds later, after the billing system 
has found a new record in the radacct table that shows the customer is 
online on that IP, the billing system logs into the NAS via its API and 
adds that IP to an ACL which blocks internet access and forces the 
customer into a walled garden to make a payment. If the customer 
disconnects, the billing system logs back into the NAS and removes the 
IP from the ACL. It is complex but generally works pretty decently, 
except the odd time when an accounting-stop packet is dropped between 
NAS and RADIUS server. The biggest issue that we had was very recent, 
the result of a missing UNIQUE on the index for acctuniqueid, resulting 
in duplicate records in the radacct table that confused the billing 
system so much that it stopped consistently updating the ACL, resulting 
in a small number of customers who were up to date getting the walled 
garden, and a few customers who were behind on billing were getting free 
service.