Database Schema

ProxPanel uses PostgreSQL 16 as its single source of truth. The full DDL lives in backend/internal/models/schema.sql and is applied at startup (idempotent — CREATE TABLE IF NOT EXISTS everywhere). This page summarises the most important tables, their key columns, indexes, and soft-delete status.

Conventions used below:

• PK — primary key.
• FK → table — foreign key.
• soft-delete — table has a deleted_at TIMESTAMP column; rows are not removed, only marked.
• All tables include created_at / updated_at unless noted.
• Columns marked [ENC] are encrypted at the application layer (AES-256-GCM with license-derived key).

Subscribers and identity

subscribers

The central object — one PPPoE / IPoE end customer.

Column	Type	Notes
id	SERIAL	PK
username	VARCHAR(100)	PPPoE login — unique via partial index WHERE deleted_at IS NULL
password	VARCHAR(255)	bcrypt hash for portal login
password_plain	VARCHAR(255)	[ENC] PPPoE password (encrypted)
full_name, email, phone, address, region, building, nationality, country, note	—	Customer-info fields
service_id	INTEGER	FK → services
status	INTEGER	1=active, 0=inactive
expiry_date, due_date	TIMESTAMP	Lifecycle dates
price, override_price	DECIMAL / BOOL	Per-subscriber price override
auto_renew, auto_recharge, auto_recharge_days	—	Auto-renewal config
daily_quota_used, monthly_quota_used	BIGINT	Bytes; reset by daily / monthly resetters
daily_download_used, daily_upload_used, monthly_download_used, monthly_upload_used	BIGINT	Direction split
fup_level, monthly_fup_level	INTEGER	0=normal, 1..6=tiers
last_daily_reset, last_monthly_reset, last_quota_reset, last_quota_sync	TIMESTAMP	Reset bookkeeping
last_session_download, last_session_upload	BIGINT	QuotaSync delta baseline
last_bypass_cdn_bytes	BIGINT	CDN bypass counter
mac_address	VARCHAR(50)	MAC binding
ip_address	VARCHAR(50)	Currently-online IP
static_ip	VARCHAR(50)	Reserved IP if any
save_mac	BOOL	Lock on first connect
nas_id, switch_id	INTEGER	FK → nas_devices / switches
reseller_id	INTEGER	FK → resellers — required
collector_id	INTEGER	FK → users (optional)
latitude, longitude	DECIMAL	Map coords
is_online, last_seen, session_id, simultaneous_sessions	—	Session state
whatsapp_notifications, wan_check_status, port_open	—	Comms / WAN
deleted_by_id, deleted_by_name	—	Audit trail for archived
deleted_at	TIMESTAMP	Soft-delete

Indexes: (mac_address), (is_online), (reseller_id), (service_id), (nas_id), (status), (username), (expiry_date), (deleted_at), plus (last_seen) WHERE is_online=true (v1.0.388).

users

Operator accounts — admins, resellers, support, collectors, read-only.

Column	Type	Notes
id	SERIAL	PK
username	VARCHAR(100)	Unique
password	VARCHAR(255)	bcrypt
password_plain	VARCHAR(255)	[ENC] — visible to admins only
email, phone, full_name	—	Contact
user_type	INTEGER	1=admin, 2=reseller, 3=support, 4=collector, 5=read-only
is_active	BOOL	Login enabled
force_password_change	BOOL	Prompt on next login
two_factor_enabled, two_factor_secret	—	TOTP
reseller_id	INTEGER	FK → resellers (NULL for admins)
permission_group	INTEGER	FK → permission_groups
last_login	TIMESTAMP	Audit
deleted_at	TIMESTAMP	Soft-delete

resellers

The hierarchy node above subscribers.

Column	Type	Notes
id	SERIAL	PK
user_id	INTEGER	FK → users — 1:1
name	VARCHAR(255)	Display name
balance	DECIMAL(15,2)	Available funds
credit	DECIMAL(15,2)	Credit limit (allows balance to go negative up to this)
address	VARCHAR(500)	—
parent_id	INTEGER	FK → resellers — sub-reseller tree
permission_group	INTEGER	FK → permission_groups
bandwidth_rule_id	INTEGER	Optional reseller-level bandwidth rule
is_active	BOOL	Disable reseller
deleted_at	TIMESTAMP	Soft-delete

permission_groups

Named bundles of permissions.

Column	Type	Notes
id	SERIAL	PK
name	VARCHAR(100)	Unique
description	VARCHAR(255)	—

permissions

The 220 canonical permission rows.

Column	Type	Notes
id	SERIAL	PK
name	VARCHAR(100)	Unique — category.action
description	VARCHAR(255)	Human-readable

permission_group_permissions

M:N join.

Column	Type	Notes
permission_group_id	INTEGER	FK ON DELETE CASCADE
permission_id	INTEGER	FK ON DELETE CASCADE
PK	composite	(group, permission)

Services and plans

services

The plan catalogue. ~50+ columns — only the load-bearing ones shown.

Column	Type	Notes
id	SERIAL	PK
name, commercial_name, description	—	Identity
download_speed, upload_speed	BIGINT	kbps
download_speed_str, upload_speed_str	VARCHAR	e.g. 2000k, fed to MikroTik
burst_download, burst_upload, burst_threshold, burst_time	—	RouterOS burst config
soft_fup_enabled	BOOL	Whether FUP shapes vs cuts
daily_quota, monthly_quota	BIGINT	Bytes — 0 = unlimited
time_quota	INTEGER	Time-based quota seconds
fup1_threshold .. fup6_threshold	BIGINT	Daily FUP step bytes
fup1_download_speed .. fup6_upload_speed	BIGINT	Speeds for each step
monthly_fup1_* .. monthly_fup6_*	—	Same for monthly
price, day_price, reset_price	DECIMAL	Pricing
expiry_value, expiry_unit, entire_month, monthly_account	—	Renewal period
time_from_hour, time_from_minute, time_to_hour, time_to_minute	—	Free-hours window
time_download_ratio, time_upload_ratio	INTEGER	% of usage discounted during window
nas_id	INTEGER	FK → nas_devices (preferred NAS)
pool_name	VARCHAR(100)	Framed-Pool to send
address_list_in, address_list_out	VARCHAR	MikroTik address-list names
queue_type	VARCHAR(50)	simple, pcq
time_based_speed_enabled	BOOL	Toggle the free-hours feature
is_active, sort_order	—	List ordering
deleted_at	TIMESTAMP	Soft-delete

reseller_services

Per-reseller price override per service.

Column	Type	Notes
id	SERIAL	PK
reseller_id, service_id	INTEGER	Unique pair
price, day_price	DECIMAL	Override values
is_enabled	BOOL	Hide the service from this reseller if false

reseller_service_limits

Per-service subscriber caps.

Column	Type	Notes
reseller_id, service_id	INTEGER	Unique pair
max_subscribers	INTEGER	Cap

NAS / Routers

nas_devices

BNGs / routers ProxPanel talks to.

Column	Type	Notes
id	SERIAL	PK
name, short_name, description	—	Identity
ip_address	VARCHAR(50)	Unique
type	VARCHAR(50)	mikrotik (default) / cisco / juniper / huawei
secret	VARCHAR(100)	RADIUS shared secret — hidden from API responses
auth_port, acct_port, coa_port	INTEGER	RADIUS ports
api_username, api_password	—	MikroTik API creds (api_password [ENC])
api_port, api_ssl_port, use_ssl, ftp_port	—	API endpoint
subscriber_pools, allowed_realms	VARCHAR(500)	Comma-separated whitelists
is_active, is_online, last_seen, version, active_sessions, total_users	—	Health / telemetry
deleted_at	TIMESTAMP	Soft-delete

switches

Hierarchy device above a NAS (for the topology diagram).

Column	Type	Notes
id	SERIAL	PK
name, location	—
parent_id	INTEGER	FK → switches (self-ref tree)
deleted_at	TIMESTAMP	Soft-delete

RADIUS tables

These follow the FreeRADIUS schema convention (lowercase, no underscores in column names) so they remain interchangeable with the upstream tool ecosystem.

radcheck

Per-username check attributes (cleartext password, expiry).

Column	Type	Notes
id	SERIAL	PK
username	VARCHAR(64)	Indexed
attribute	VARCHAR(64)	e.g. Cleartext-Password
op	CHAR(2)	:=
value	VARCHAR(253)	—

radreply

Per-username reply attributes (Framed-IP-Address, Mikrotik-Rate-Limit).

Column	Type	Notes
id	SERIAL	PK
username	VARCHAR(64)	Indexed
attribute	VARCHAR(64)	e.g. Framed-IP-Address, Mikrotik-Rate-Limit
op	CHAR(2)	:=
value	VARCHAR(253)	—
Composite index	—	(username, attribute)

radgroupcheck / radgroupreply / radusergroup

Group-based attributes. Rarely used by ProxPanel — kept for compatibility.

radacct

Accounting — one row per session.

Column	Type	Notes
radacctid	BIGSERIAL	PK
acctsessionid	VARCHAR(64)	Per-session ID from BNG
acctuniqueid	VARCHAR(32)	Unique across all NAS
username, realm	—	Subscriber identity
nasipaddress, nasportid, nasporttype	—	NAS metadata
acctstarttime, acctupdatetime, acctstoptime	TIMESTAMP	Session lifecycle
acctinterval, acctsessiontime	INTEGER	Duration accounting
acctauthentic	VARCHAR(32)	RADIUS/Local
connectinfo_start, connectinfo_stop	—	Free-form
acctinputoctets, acctoutputoctets	BIGINT	Bytes
calledstationid, callingstationid	VARCHAR(50)	NAS / MAC
acctterminatecause	VARCHAR(32)	Why disconnected
servicetype, framedprotocol	—	PPP / IPoE
framedipaddress, framedipv6address, framedipv6prefix, framedinterfaceid, delegatedipv6prefix	—	Assigned addresses

Indexes: (username), (acctstarttime), (acctstoptime), (acctsessionid, username), (nasipaddress), (framedipaddress).

When the table exceeds ~10M rows, run the radacct partitioning runbook.

radpostauth

Post-auth audit (failed authentications, password attempts).

Column	Type	Notes
id	SERIAL	PK
username	VARCHAR(64)	Indexed
pass	VARCHAR(64)	What the user tried
reply	VARCHAR(32)	Access-Accept / Access-Reject
callingstationid	VARCHAR(50)	MAC
authdate	TIMESTAMP	Indexed
class	VARCHAR(64)	—

Billing

transactions

Money movements. See Transaction Types.

Column	Type	Notes
id	SERIAL	PK
type	VARCHAR(50)	Indexed — see Transaction Types reference
amount	DECIMAL(15,2)	Signed
balance_before, balance_after	DECIMAL(15,2)	Reseller balance snapshots
description	VARCHAR(500)	—
old_service_name, new_service_name, service_name	—	Plan-change provenance
reseller_id	INTEGER	FK — indexed
subscriber_id	INTEGER	FK (NULL on transfer) — indexed
target_reseller_id	INTEGER	For transfer type
ip_address, user_agent	—	Audit metadata
created_by	INTEGER	FK → users
created_at	TIMESTAMP	Indexed

Not soft-deleted — transactions are immutable history.

invoices

Professional invoice records.

Column	Type	Notes
id	SERIAL	PK
invoice_number	VARCHAR(50)	Unique
subscriber_id, reseller_id	INTEGER	FK — indexed
sub_total, discount, tax, total, amount_paid	DECIMAL(15,2)	Money
status	VARCHAR(20)	pending, completed, failed, refunded, rolled_forward — indexed
due_date, paid_date	TIMESTAMP	Lifecycle
public_token	VARCHAR(64)	Random token used by /api/invoices/public/:id so QR codes are not enumerable
billing_period_start, billing_period_end	TIMESTAMP	—
auto_generated	BOOL	From a scheduler vs created manually
notification_log	JSONB	Timeline of notifications sent
rolled_forward_to_invoice_id	INTEGER	FK → invoices (if unpaid balance was rolled into a new invoice)
deleted_at	TIMESTAMP	Soft-delete

invoice_items

Line items on an invoice.

Column	Type	Notes
id	SERIAL	PK
invoice_id	INTEGER	FK
description	VARCHAR(255)	—
quantity, unit_price, total	—	Money

payments

Operator-recorded payments (often collector cash entries).

Column	Type	Notes
id	SERIAL	PK
invoice_id, subscriber_id, reseller_id, collector_id	INTEGER	FKs
amount	DECIMAL(15,2)	—
method	VARCHAR(50)	cash, card, online
reference, notes	—	Free-form
status	VARCHAR(20)	Defaults completed

prepaid_cards

Voucher cards.

Column	Type	Notes
id	SERIAL	PK
code	VARCHAR(50)	Unique
pin	VARCHAR(20)	Optional
service_id, reseller_id	INTEGER	FK
value, days, quota_refill	—	Card contents
is_used, used_by, used_at	—	Redemption state
is_active, expiry_date	—	Cancellable / expirable
batch_id, batch_number	—	Print batch tracking

static_ip_prices / static_ip_rentals

Pricing catalogue and per-subscriber rentals for static IPs.

Column	Type	Notes
id	SERIAL	PK
name, price, days, is_active	—	Catalogue entry
(rental) subscriber_id, ip_address, price_id, start_date, expiry_date, is_active, deleted_at	—	Per-subscriber row

collection_assignments

Assignments of subscribers to collectors for cash collection runs.

Column	Type	Notes
collector_id, subscriber_id, reseller_id, invoice_id	—	FKs
status	VARCHAR(20)	pending, collected, failed, cancelled
auto_renew, send_notification, amount, notes, collected_at, payment_id, created_by	—	—

Audit, logs, backups

audit_logs

Append-only operator-action history.

Column	Type	Notes
id	SERIAL	PK
actor_id	INTEGER	FK → users (the operator)
actor_name	VARCHAR(100)	Denormalised for performance
action	VARCHAR(100)	e.g. subscriber.update, reseller.add_money
entity_type	VARCHAR(50)	subscriber, reseller, …
entity_id	INTEGER	Target
entity_name	VARCHAR(255)	Denormalised
description	TEXT	Human-readable summary
changes	JSONB	Field-level diff (where applicable)
ip_address, user_agent	—	Request metadata
created_at	TIMESTAMP	Indexed

backup_schedules

Scheduled backup definitions.

Column	Type	Notes
id	SERIAL	PK
name, backup_type, frequency, retention	—	Identity + retention
day_of_week, day_of_month, time_of_day	—	When to run
storage_type, local_path	—	local / cloud / ftp
ftp_enabled, ftp_host, ftp_port, ftp_username, ftp_password, ftp_path, ftp_passive, ftp_tls	—	FTP target
last_status, last_error, last_backup_file, last_run_at, next_run_at	—	Last run state
deleted_at	TIMESTAMP	Soft-delete

backup_logs

Per-backup execution record.

Column	Type	Notes
id	SERIAL	PK
schedule_id, schedule_name	—	Origin
filename, storage_path, storage_type	—	Where it landed
backup_type, duration, created_by_id, created_by_name	—	Metadata

Rules and detection

bandwidth_rules

Time-window-based speed adjustments.

Column	Type	Notes
id	SERIAL	PK
name	—	Identity
service_id	INTEGER	Indexed — scope of the rule
time_from_hour, time_from_minute, time_to_hour, time_to_minute	—	Window
download_percent, upload_percent	INTEGER	Multiplier
is_active	BOOL	Toggle — indexed
days_of_week	VARCHAR	Comma-separated 0-6

subscriber_bandwidth_rules

Per-subscriber bandwidth override.

Column	Type	Notes
id	SERIAL	PK
subscriber_id	INTEGER	Indexed
rule_type	VARCHAR(20)	internet, cdn
enabled	BOOL	Toggle
download_speed, upload_speed	INTEGER	Absolute kbps override
cdn_id, cdn_name	—	For cdn rules
duration, expires_at	—	Temporary rules
priority	INTEGER	Conflict resolution

cdn

CDN entries — named groups of IP subnets.

Column	Type	Notes
id	SERIAL	PK
name, description	—	Identity
subnets	TEXT	Comma-separated CIDRs
is_active	BOOL	—

cdn_port_rules

Port-based PCQ speed control.

Column	Type	Notes
id	SERIAL	PK
name, description	—	Identity
port, protocol	—	Match
direction	VARCHAR	src, dst, both, dscp
dscp_value	INTEGER	When direction=dscp
download_speed, upload_speed	INTEGER	kbps
nas_id	INTEGER	Optional NAS target

service_cdns

Per-service CDN attachments (with FUP overrides).

Column	Type	Notes
service_id	INTEGER	Indexed
cdn_id	INTEGER	FK
time_based_speed_enabled	BOOL	—
pcq_* columns	—	PCQ settings on this attachment

communication_rules

Automated WhatsApp / SMS / email triggers.

Column	Type	Notes
id	SERIAL	PK
name	—	Identity
trigger_event	VARCHAR(50)	Indexed — expiry_warning, fup_applied, etc.
is_active	BOOL	Indexed
channels	VARCHAR	Comma-sep whatsapp,sms,email
template, subject	TEXT	Content
filters	JSONB	Service / FUP-level / reseller filters

sharing_detection_results

TTL-analysis output from the nightly scan.

Column	Type	Notes
id	SERIAL	PK
subscriber_id	INTEGER	FK
detected_at	TIMESTAMP	When found
ttl_anomaly, device_count_estimate	—	Findings
auto_disconnected	BOOL	Whether the user was disconnected

CPE / TR-069

cpe_devices

Registered TR-069 routers.

Column	Type	Notes
id	SERIAL	PK
serial_number	VARCHAR	Unique
manufacturer, model, oui, product_class, firmware_version	—	Identity
ip_address, mac_address	—	Network
subscriber_id	INTEGER	FK — link to ProxPanel subscriber
wifi_ssid, wifi_password, wifi_channel, wifi_enabled	—	Current WiFi state
connected_clients	INTEGER	Hosts behind the CPE
connected_hosts	JSONB	Detailed list of hosts
last_inform, last_connection_request	TIMESTAMP	TR-069 lifecycle

cpe_tasks

Pending / completed TR-069 task queue.

Column	Type	Notes
id	SERIAL	PK
device_id	INTEGER	FK → cpe_devices
task_type	VARCHAR	get_parameter_values, get_hosts, set_wifi, reboot
payload	JSONB	Parameters
status	VARCHAR	pending, running, done, failed
result	JSONB	—

IP pool management

ip_pool_assignments

Mirrors MikroTik pool ranges. Prevents duplicate IP allocation across radreply + pool.

Column	Type	Notes
id	SERIAL	PK
ip_address	VARCHAR(15)	Unique
pool_name	VARCHAR(64)	Which pool the IP belongs to
status	VARCHAR(20)	available, in_use
username, subscriber_id, nas_id	—	Allocation metadata
assigned_at	TIMESTAMP	—

Cluster / HA

cluster_config

Local cluster role + secret for this node.

Column	Type	Notes
role	VARCHAR	standalone, main, secondary
cluster_id, cluster_secret	VARCHAR	Identity
main_server_ip	VARCHAR	When secondary

cluster_nodes

Registered cluster members (only present on main).

Column	Type	Notes
id	SERIAL	PK
cluster_id, node_id, role, ip_address, hostname	—	Identity
last_heartbeat, status, cpu_usage, memory_usage, disk_usage, replication_lag	—	Telemetry

cluster_events

Audit log of cluster events.

Column	Type	Notes
event_type	VARCHAR	node_joined, node_left, failover, manual_failover
node_id, details, created_at	—	—

Auth / sessions

refresh_tokens (v1.0.540+)

HttpOnly-cookie refresh tokens.

Column	Type	Notes
id	SERIAL	PK
user_id	INTEGER	FK → users
token_hash	VARCHAR	sha256 — only the hash stored
family_id	UUID	Token-family for rotation
issued_at, expires_at, revoked_at	TIMESTAMP	Lifecycle
user_agent, ip_address	—	Audit

token_blacklist

Logout invalidation.

Column	Type	Notes
token_hash	VARCHAR	sha256 of the JWT
expires_at	TIMESTAMP	Auto-cleaned after expiry

System

system_preferences

Key / value store for runtime config (timezone, reset times, feature flags).

Column	Type	Notes
key	VARCHAR(100)	Unique
value	TEXT	—
updated_at	TIMESTAMP	—

tenant_config (SaaS only)

Per-tenant configuration.

Column	Type	Notes
tenant_id	VARCHAR	Unique
schema_name	VARCHAR	tenant_<id>
domain, branding, feature_flags	—	—

Soft-delete summary

Tables with deleted_at:

• subscribers
• services
• nas_devices
• switches
• resellers
• users
• invoices
• static_ip_rentals
• backup_schedules

All others are hard-delete (history tables like transactions, audit_logs, radacct are never deleted in normal operation; radacct is archived to radacct_archive).

Migration philosophy

ProxPanel uses schema.sql with idempotent statements rather than GORM AutoMigrate. Reasons:

1. Predictable on upgrades — no auto-add-column surprises.
2. Strict, predictable schema control.
3. Easier rollback — diff the SQL files.

For column additions across released versions, the file uses ALTER TABLE ... ADD COLUMN IF NOT EXISTS after the relevant CREATE TABLE.

Related pages

• Permission List — populates permissions and permission_groups.
• Transaction Types — populates transactions.type.
• RADIUS — how radcheck, radreply, radacct are filled and read.
• Subscribers — UI on top of the subscribers table.
• Backups — backup_schedules and backup_logs.