PyroRadius — Database Architecture

Version: 1.0 (Documentation) Last Updated: 2026-07-13 Database: PostgreSQL 16 Application Root: /var/www/pyroradius/ Migrations Path: database/migrations/

---

Table of Contents

1. Overview
2. Table Groups
3. Core Tables
4. Billing Tables
5. RADIUS Tables
6. Network Tables
7. Communication Tables
8. System Tables
9. Geographic Tables
10. Support Tables
11. Key Relationships (ER Concept)
12. Indexes Strategy
13. PostgreSQL-Specific Features
14. Performance Notes

---

Overview

PyroRadius uses a single PostgreSQL 16 database (pyroradius) that serves two roles simultaneously:

1. Application database — all Laravel/PyroRadius application tables
2. FreeRADIUS database — the rad* tables read directly by FreeRADIUS for PPPoE authentication and accounting

This shared-database approach means RADIUS authentication happens without any API layer; FreeRADIUS reads credentials and group assignments written by Laravel in real time.

All tables use Laravel migration conventions (snake_case, plural names, id primary key, created_at/updated_at timestamps). The RADIUS tables (radcheck, radreply, radgroupreply, radusergroup, radacct, radpostauth) follow the FreeRADIUS PostgreSQL schema conventions which predate this application.

---

Table Groups

Group	Tables	Purpose
Core	users, customers, packages, connection_types, customer_types, customer_statuses, service_types	Users, customers, and package definitions
Billing	invoices, payments, ledger_entries, invoice_batches, billing_archives	All financial records
RADIUS	radcheck, radreply, radgroupreply, radusergroup, radacct, radpostauth	FreeRADIUS auth and accounting
Network	nas, olts, ont_cache, signal_readings, customer_traffic_readings, nas_health_records, nas_ports	NAS devices, OLTs, ONTs, traffic data
Communication	whatsapp_templates, whatsapp_logs, notifications, device_tokens, campaigns, campaign_recipients	WhatsApp and push notifications
System	audit_logs, cron_jobs, settings, company_profiles, dealer_package, failed_jobs, jobs	Application system tables
Geographic	areas, sub_areas, cities, countries	Location hierarchy
Support	tickets, ticket_categories	Customer support tickets
TR-069	customer_acs_devices	GenieACS CPE device records
Engineer	engineer_teams	Field engineer team management
Graphs	graph_targets	Custom interface traffic tracking

---

Core Tables

users

The primary user table. Covers all human actors in the system: super_admin, admin, dealer, sub_dealer, and customer portal users.

Column	Type	Description
id	bigint PK	Auto-increment primary key
name	varchar(255)	Display name
email	varchar(255) UNIQUE	Login email address
password	varchar(255)	Bcrypt hash
remember_token	varchar(100)	Laravel remember-me token
role	varchar(50)	One of: super_admin, admin, dealer, sub_dealer
permissions	jsonb	Explicit permission keys (see Authorization docs)
dealer_id	bigint FK→users	NULL for admins; set for dealers (parent admin); for sub_dealers points to their dealer
sub_dealer_id	bigint FK→users	NULL unless this IS a sub_dealer referencing their own row (self-referential for sub-dealer's own dealer)
phone	varchar(50)	Contact phone
address	text	Address
is_active	boolean	Whether user account is enabled
email_verified_at	timestamp	Email verification timestamp (Sanctum)
last_login_at	timestamp	Last successful login timestamp
created_at	timestamp
updated_at	timestamp

Key relationships: - One user (dealer) → many customers (via customers.dealer_id) - One user (dealer) → many users (sub_dealers) via users.dealer_id - permissions JSONB structure: {"customers.create": true, "billing.payments": true, ...}

customers

The central entity of the entire system. Every internet subscriber is a customer record.

Column	Type	Description
id	bigint PK
name	varchar(255)	Customer full name
email	varchar(255)	Customer email (optional)
phone	varchar(50)	Primary contact phone (used for WhatsApp)
cnic	varchar(20)	CNIC (national ID, Pakistan)
address	text	Installation address
dealer_id	bigint FK→users	Owning dealer
sub_dealer_id	bigint FK→users	Owning sub-dealer (nullable)
package_id	bigint FK→packages	Current active package
connection_type_id	bigint FK→connection_types	PPPoE(1), Static IP(2), CIR(3)
customer_type_id	bigint FK→customer_types	Residential, business, etc.
customer_status_id	bigint FK→customer_statuses	Active, suspended, expired, terminated
area_id	bigint FK→areas	Installation area
sub_area_id	bigint FK→sub_areas	Installation sub-area
pppoe_username	varchar(255) UNIQUE	PPPoE login username; also used in RADIUS tables
pppoe_password	varchar(255)	PPPoE password (plain text, RADIUS requirement)
static_ip	varchar(45)	Static IP address (for connection_type_id=2)
mac_address	varchar(17)	Device MAC address
device_mac	varchar(17)	CPE/router MAC address
ont_serial	varchar(100)	ONT serial number (FTTH)
ont_port	varchar(50)	OLT port assignment
rx_power	decimal(8,2)	Last measured optical signal (dBm)
expiry_date	date	Account expiry date (billing cycle end)
installation_date	date	Date of service activation
monthly_price	decimal(10,2)	Current monthly charge (denormalized from package)
balance	decimal(10,2)	Running ledger balance (positive = credit)
nas_id	bigint FK→nas	Assigned NAS device
created_at	timestamp
updated_at	timestamp

Key relationships: - customer → package (current package) - customer → user (dealer) - customer → nas (network device) - customer → many invoices - customer → many payments - customer → many ledger_entries - customer → many radcheck rows (via pppoe_username) - customer → one customer_acs_device (TR-069 CPE)

Security note: pppoe_password is stored as plain text. This is a hard requirement of the RADIUS/PPPoE authentication protocol. FreeRADIUS performs a direct Cleartext-Password comparison. The column is excluded from AuditLogger exports.

packages

Internet package definitions. Each package maps to a RADIUS group.

Column	Type	Description
id	bigint PK
name	varchar(255)	Package display name (e.g., "10 Mbps Home")
radius_group	varchar(100)	RADIUS group name in radgroupreply (e.g., pkg_10mb)
download_speed	integer	Download speed in Kbps
upload_speed	integer	Upload speed in Kbps
burst_download	integer	Burst download speed in Kbps
burst_upload	integer	Burst upload speed in Kbps
burst_threshold_down	integer	Threshold for burst download
burst_threshold_up	integer	Threshold for burst upload
burst_time	integer	Burst time in seconds
price	decimal(10,2)	Standard monthly price
connection_type_id	bigint FK→connection_types	Package applies to this connection type
is_active	boolean	Whether package is available
description	text	Optional package description
created_at	timestamp
updated_at	timestamp

Key relationships: - package → many customers - package → many dealer_package (pivot with per-dealer pricing) - package → radius group (via radius_group column → radgroupreply.groupname)

---

Billing Tables

invoices

One invoice per billing period per customer.

Column	Type	Description
id	bigint PK
invoice_number	varchar(50) UNIQUE	Human-readable invoice number (sequence-based)
customer_id	bigint FK→customers
dealer_id	bigint FK→users	Denormalized for scoping queries
invoice_batch_id	bigint FK→invoice_batches	Batch that generated this invoice (nullable for manual)
amount	decimal(10,2)	Total invoice amount
paid_amount	decimal(10,2)	Amount paid so far
status	varchar(20)	unpaid, partial, paid, cancelled
due_date	date	Payment due date
billing_period_start	date	Start of billing period
billing_period_end	date	End of billing period
notes	text	Optional notes
pdf_path	varchar(500)	Storage path to generated PDF
created_at	timestamp
updated_at	timestamp

payments

Individual payment transactions applied against invoices.

Column	Type	Description
id	bigint PK
receipt_number	varchar(50) UNIQUE	Human-readable receipt number (sequence-based)
customer_id	bigint FK→customers
dealer_id	bigint FK→users	Denormalized for scoping
invoice_id	bigint FK→invoices	Primary invoice this payment is applied to (nullable for advance)
payment_method_id	bigint FK→payment_methods	Cash, bank transfer, online, etc.
amount	decimal(10,2)	Payment amount
collected_by	bigint FK→users	User who recorded this payment
notes	text	Notes (reference number, etc.)
payment_date	date	Date of payment (may differ from created_at)
created_at	timestamp
updated_at	timestamp

ledger_entries

Double-entry style ledger for complete financial trail per customer.

Column	Type	Description
id	bigint PK
customer_id	bigint FK→customers
dealer_id	bigint FK→users	Denormalized
entry_type	varchar(20)	debit (charge) or credit (payment)
amount	decimal(10,2)	Entry amount
balance_after	decimal(10,2)	Running balance after this entry
description	text	Human-readable description
reference_type	varchar(50)	invoice, payment, adjustment
reference_id	bigint	ID of the related invoice or payment
created_at	timestamp

invoice_batches

Groups invoices generated together in a monthly run.

Column	Type	Description
id	bigint PK
name	varchar(255)	Batch name (e.g., "June 2026 Monthly")
billing_month	date	First day of the billing month
total_invoices	integer	Count of invoices in this batch
total_amount	decimal(12,2)	Sum of all invoice amounts
status	varchar(20)	processing, complete, failed
generated_by	bigint FK→users	User or NULL for scheduled
created_at	timestamp
updated_at	timestamp

billing_archives

Historical billing records moved from active tables for performance.

Mirrors structure of invoices and payments, with an additional archived_at timestamp. Queried via BillingArchiveController and BillingArchiveService.

---

RADIUS Tables

These tables are shared with FreeRADIUS. PyroRadius owns the write operations; FreeRADIUS performs reads at authentication time.

radcheck

Per-user authentication attributes checked during RADIUS Access-Request.

Column	Type	Description
id	integer PK
username	varchar(64)	PPPoE username (matches customers.pppoe_username)
attribute	varchar(64)	Typically Cleartext-Password
op	varchar(2)	RADIUS operator (typically := for password)
value	varchar(253)	Attribute value (plain text password for PPPoE)

radreply

Per-user reply attributes sent in Access-Accept response.

Column	Type	Description
id	integer PK
username	varchar(64)	PPPoE username
attribute	varchar(64)	e.g., Framed-IP-Address for static IP customers
op	varchar(2)	RADIUS operator (typically =)
value	varchar(253)	Attribute value

radgroupreply

Group-level reply attributes. PyroRadius writes one row per speed attribute per package.

Column	Type	Description
id	integer PK
groupname	varchar(64)	Matches packages.radius_group
attribute	varchar(64)	e.g., Mikrotik-Rate-Limit
op	varchar(2)	RADIUS operator
value	varchar(253)	e.g., 10240k/5120k (download/upload in Kbps)

radusergroup

Maps each PPPoE username to their RADIUS group (package group).

Column	Type	Description
id	integer PK
username	varchar(64)	PPPoE username
groupname	varchar(64)	RADIUS group (package's radius_group)
priority	integer	Group priority (default 1)

radacct

Session accounting records written by FreeRADIUS. Read-only from PyroRadius's perspective.

Column	Type	Description
radacctid	bigint PK
acctsessionid	varchar(64)	Unique session ID from NAS
acctuniqueid	varchar(32) UNIQUE	FreeRADIUS unique session ID
username	varchar(64)	PPPoE username
nasipaddress	varchar(15)	NAS IP address
nasportid	varchar(15)	NAS port identifier
acctstarttime	timestamp	Session start
acctstoptime	timestamp	Session stop (NULL if still active)
acctinputoctets	bigint	Bytes downloaded by user
acctoutputoctets	bigint	Bytes uploaded by user
acctsessiontime	integer	Session duration in seconds
acctterminatecause	varchar(32)	Why session ended
framedipaddress	varchar(15)	Assigned IP address
callingstationid	varchar(50)	MAC address of CPE

Usage in PyroRadius: - Online session count (customers currently connected) - Traffic usage per customer per period - Session history for customer detail page - CloseGhostSessions command cleans orphaned active records

---

Network Tables

nas

MikroTik NAS (Network Access Server) device inventory.

Column	Type	Description
id	bigint PK
name	varchar(255)	Human-readable NAS name
server	varchar(100)	NAS IP address or hostname
secret	varchar(255)	RADIUS shared secret
type	varchar(50)	NAS type (e.g., mikrotik)
api_username	varchar(100)	RouterOS API username
api_password	varchar(255)	RouterOS API password (encrypted cast in Model)
api_port	integer	RouterOS API port (default 8728)
snmp_community	varchar(100)	SNMP community string (for fallback)
description	text	Notes
is_active	boolean	Whether this NAS is polled
created_at	timestamp
updated_at	timestamp

olts

GPON OLT (Optical Line Terminal) inventory.

Column	Type	Description
id	bigint PK
name	varchar(255)	OLT name/location
ip_address	varchar(45)	Management IP
telnet_port	integer	Telnet/SSH port for management
username	varchar(100)	Login username
password	varchar(255)	Login password
model	varchar(100)	OLT model/brand
is_active	boolean	Whether this OLT is polled
created_at	timestamp
updated_at	timestamp

ont_cache

Discovered ONTs from OLT polling. Refreshed by PollOltOnts command.

Column	Type	Description
id	bigint PK
olt_id	bigint FK→olts	Which OLT this ONT is on
serial	varchar(100)	ONT serial number
port	varchar(50)	OLT port/slot/pon identifier
description	varchar(255)	ONT description from OLT
status	varchar(50)	ONT operational status
rx_power	decimal(8,2)	Current receive signal level (dBm)
last_seen_at	timestamp	Last time this ONT was observed
created_at	timestamp
updated_at	timestamp

signal_readings

Historical signal strength readings per customer ONT.

Column	Type	Description
id	bigint PK
customer_id	bigint FK→customers
rx_power	decimal(8,2)	Signal reading (dBm)
read_at	timestamp	When reading was taken

Pruned periodically by PruneTrafficReadings (or similar command) to manage table growth.

customer_traffic_readings

Traffic statistics per customer per polling cycle.

Column	Type	Description
id	bigint PK
customer_id	bigint FK→customers
nas_id	bigint FK→nas	Source NAS
bytes_in	bigint	Download bytes in this sample
bytes_out	bigint	Upload bytes in this sample
read_at	timestamp	Polling timestamp

Pruned periodically by PruneTrafficReadings.

---

Communication Tables

whatsapp_templates

Parameterized message templates for all WhatsApp notification types.

Column	Type	Description
id	bigint PK
name	varchar(100) UNIQUE	Template key (e.g., invoice_generated, payment_receipt, expiry_reminder)
body	text	Template body with {{variable}} placeholders
is_active	boolean	Whether this template is in use
created_at	timestamp
updated_at	timestamp

whatsapp_logs

Record of every WhatsApp message attempted (success or failure).

Column	Type	Description
id	bigint PK
customer_id	bigint FK→customers	Recipient customer
phone	varchar(50)	Destination phone number
template_name	varchar(100)	Which template was used
message	text	Final rendered message body
status	varchar(20)	sent, failed, queued
error	text	Error message if failed
driver	varchar(20)	openwa or log
sent_at	timestamp	When message was sent
created_at	timestamp

campaigns

Bulk WhatsApp broadcast campaigns.

Column	Type	Description
id	bigint PK
name	varchar(255)	Campaign name
template_id	bigint FK→whatsapp_templates	Template to use
filter_criteria	jsonb	Customer filter (area, package, status)
total_recipients	integer	Count of targeted customers
sent_count	integer	Sent so far
failed_count	integer	Failed so far
status	varchar(20)	draft, running, complete, failed
scheduled_at	timestamp	When to run (nullable = run immediately)
created_by	bigint FK→users
created_at	timestamp
updated_at	timestamp

campaign_recipients

Per-customer record within a campaign.

Column	Type	Description
id	bigint PK
campaign_id	bigint FK→campaigns
customer_id	bigint FK→customers
status	varchar(20)	pending, sent, failed
sent_at	timestamp
error	text	Error if failed

---

System Tables

audit_logs

Comprehensive action log for all create/update/delete operations.

Column	Type	Description
id	bigint PK
user_id	bigint FK→users	User who performed the action
action	varchar(100)	Action key (e.g., customer.created, payment.recorded)
auditable_type	varchar(255)	Eloquent model class name
auditable_id	bigint	Model primary key
old_values	jsonb	State before change (for updates)
new_values	jsonb	State after change
ip_address	varchar(45)	Request IP address
user_agent	varchar(500)	Browser user agent
created_at	timestamp

Pruned periodically by PruneAuditLogs command (configurable retention period).

cron_jobs

DB-driven scheduler configuration. The Laravel scheduler reads this table to determine which commands to run.

Column	Type	Description
id	bigint PK
command	varchar(255)	Artisan command name (e.g., radius:sync-all)
expression	varchar(100)	Cron expression (e.g., 0 2 * * *)
description	text	Human-readable description
is_active	boolean	Whether this job is scheduled
last_run_at	timestamp	Last execution time
last_result	varchar(20)	success, failed
last_output	text	Command output from last run
created_at	timestamp
updated_at	timestamp

settings

Key-value store for application-wide configuration.

Column	Type	Description
id	bigint PK
key	varchar(255) UNIQUE	Setting key (e.g., expiry_reminder_days, invoice_prefix)
value	text	Setting value
group	varchar(100)	Logical grouping (e.g., billing, notifications)
created_at	timestamp
updated_at	timestamp

company_profiles

Company branding information used in PDF invoices and the UI.

Column	Type	Description
id	bigint PK
name	varchar(255)	Company name (e.g., "Indus Broadband")
address	text	Registered address
phone	varchar(50)	Contact phone
email	varchar(255)	Contact email
website	varchar(255)	Website URL
logo_path	varchar(500)	Path to company logo image
ntn	varchar(50)	National Tax Number
bank_details	text	Bank account details for invoice footer
invoice_footer_note	text	Custom footer text on invoices
created_at	timestamp
updated_at	timestamp

dealer_package

Pivot table linking dealers to packages with per-dealer pricing and commercial terms.

Column	Type	Description
id	bigint PK
dealer_id	bigint FK→users	The dealer
package_id	bigint FK→packages	The package
price	decimal(10,2)	Dealer-specific price for this package
is_active	boolean	Whether dealer can sell this package
created_at	timestamp
updated_at	timestamp

---

Geographic Tables

countries

Column	Type
id	bigint PK
name	varchar(100)

cities

Column	Type
id	bigint PK
name	varchar(100)
country_id	bigint FK→countries

areas

Column	Type
id	bigint PK
name	varchar(100)
city_id	bigint FK→cities

sub_areas

Column	Type
id	bigint PK
name	varchar(100)
area_id	bigint FK→areas

Geographic hierarchy: Country → City → Area → Sub-Area → Customer

---

Support Tables

tickets

Customer support tickets. Can be created by customers (via portal) or staff.

Column	Type	Description
id	bigint PK
ticket_number	varchar(50) UNIQUE	Human-readable ticket ID
customer_id	bigint FK→customers
dealer_id	bigint FK→users	Denormalized for scoping
category_id	bigint FK→ticket_categories
subject	varchar(255)	Ticket subject
description	text	Issue description
status	varchar(20)	open, in_progress, resolved, closed
priority	varchar(20)	low, medium, high, critical
assigned_to	bigint FK→users	Assigned staff member
resolved_at	timestamp	When ticket was resolved
created_at	timestamp
updated_at	timestamp

ticket_categories

Column	Type
id	bigint PK
name	varchar(100)
description	text

---

Key Relationships (ER Concept)

countries ──< cities ──< areas ──< sub_areas
                                         │
                               customers ┤
          ┌──────────────────────────────┤
          │                             │
        users (dealers)          packages ──────────── dealer_package
          │                        │         │
          │                    radgroupreply (FreeRADIUS)
          │
    customers ──────────────────────────────────────────┐
          │                                              │
          ├──> invoices ──< invoice_batches              │
          │                                              │
          ├──> payments                                  │
          │                                              │
          ├──> ledger_entries                            │
          │                                              │
          ├──> radcheck (pppoe_username FK by value)     │
          ├──> radreply (pppoe_username FK by value)     │
          ├──> radusergroup (pppoe_username FK by value) │
          ├──> radacct (username FK by value)            │
          │                                              │
          ├──> whatsapp_logs                             │
          ├──> campaign_recipients                       │
          ├──> signal_readings                           │
          ├──> customer_traffic_readings                 │
          ├──> customer_acs_devices (TR-069)             │
          └──> tickets                                   │
                                                         │
    nas ─────────────────────────────────────────────────┘
    olts ──< ont_cache ──────────────────────────> customers (by ont_serial)

Note: RADIUS tables (radcheck, radreply, radusergroup, radacct) do not use foreign keys to customers. The relationship is maintained by value (username = customers.pppoe_username). This is a FreeRADIUS schema constraint that PyroRadius respects.

---

Indexes Strategy

Critical Indexes

-- Customer lookups (most frequent queries)
CREATE INDEX idx_customers_dealer_id ON customers(dealer_id);
CREATE INDEX idx_customers_pppoe_username ON customers(pppoe_username);
CREATE INDEX idx_customers_expiry_date ON customers(expiry_date);
CREATE INDEX idx_customers_status ON customers(customer_status_id);
CREATE INDEX idx_customers_dealer_status ON customers(dealer_id, customer_status_id);

-- RADIUS table indexes (FreeRADIUS performance critical)
CREATE INDEX idx_radcheck_username ON radcheck(username);
CREATE INDEX idx_radreply_username ON radreply(username);
CREATE INDEX idx_radusergroup_username ON radusergroup(username);
CREATE INDEX idx_radacct_username ON radacct(username);
CREATE INDEX idx_radacct_acctstoptime ON radacct(acctstoptime);  -- online session queries
CREATE INDEX idx_radacct_nasipaddress ON radacct(nasipaddress);

-- Billing lookups
CREATE INDEX idx_invoices_customer_id ON invoices(customer_id);
CREATE INDEX idx_invoices_dealer_id ON invoices(dealer_id);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_payments_customer_id ON payments(customer_id);
CREATE INDEX idx_ledger_entries_customer_id ON ledger_entries(customer_id);

-- Audit log pruning
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);
CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);

-- JSONB permission index (for permission checks)
CREATE INDEX idx_users_permissions ON users USING GIN(permissions);

---

PostgreSQL-Specific Features

JSONB for Permissions

The users.permissions column uses PostgreSQL JSONB (Binary JSON). This allows: - Efficient key-existence checks: permissions ? 'customers.create' - GIN index for fast permission lookups - Easy permission grant/revoke via JSON manipulation without schema changes

Example:

SELECT id, name FROM users
WHERE permissions ? 'billing.payments'
AND role = 'dealer';

Sequences for Invoice/Receipt Numbers

Invoice numbers and receipt numbers are generated from PostgreSQL sequences (not auto-increment) to produce human-readable, predictable formats:

-- Invoice number: INV-2026-000001
CREATE SEQUENCE invoice_number_seq START 1;
SELECT LPAD(nextval('invoice_number_seq')::text, 6, '0');

-- Receipt number: RCP-2026-000001
CREATE SEQUENCE receipt_number_seq START 1;

This approach guarantees uniqueness across concurrent requests without gaps, and allows custom prefix formatting.

Lock Timeout

On critical billing transactions (recording payments, generating invoices), a session-level lock timeout is applied to prevent deadlock hangs:

SET lock_timeout = '30s';

If a lock cannot be acquired within 30 seconds, PostgreSQL raises an exception instead of waiting indefinitely. Laravel catches this and returns a user-friendly error.

Partial Indexes

Performance optimization for the most common filtered queries:

-- Index only active customers (most dashboard queries filter by active status)
CREATE INDEX idx_customers_active ON customers(dealer_id, expiry_date)
WHERE customer_status_id = 1;  -- 1 = active

-- Index only open radacct sessions (online count query)
CREATE INDEX idx_radacct_open ON radacct(username, nasipaddress)
WHERE acctstoptime IS NULL;

---

Performance Notes

1. RADIUS table size — radacct grows continuously. PruneRadpostauth prunes radpostauth. radacct is retained longer for usage reports. Monitor table size and plan a retention policy (e.g., archive sessions older than 12 months).

2. Audit log growth — audit_logs is written on every model change. With hundreds of daily transactions, this can reach millions of rows within a year. PruneAuditLogs is scheduled to delete records older than the configured retention period (default: 90 days).

3. Traffic readings — customer_traffic_readings and signal_readings are time-series tables written frequently by polling commands. PruneTrafficReadings and PruneNasPorts manage their size. Consider PostgreSQL table partitioning by month for these tables if they grow beyond 50M rows.

4. PostgreSQL autovacuum — ensure autovacuum is tuned for the workload. High-write tables (radacct, audit_logs, customer_traffic_readings) benefit from more aggressive autovacuum settings:

   ALTER TABLE radacct SET (autovacuum_vacuum_scale_factor = 0.01);
   ALTER TABLE audit_logs SET (autovacuum_vacuum_scale_factor = 0.01);
   

5. Connection pooling — at scale, consider PgBouncer between Laravel/FreeRADIUS and PostgreSQL to reduce connection overhead. FreeRADIUS can generate many short-lived connections during peak authentication load.

6. Billing queries — the most expensive queries are full dealer-scoped customer lists with balance calculations. Ensure the dealer_id index is used (check with EXPLAIN ANALYZE).

---

For installation, see 02_Installation_Guide.md. For authorization details, see 05_Authorization_Model.md.