Hi r/selfhosted !

Like many people relying on 5G cellular for home broadband / WAN failover, I got tired of the terrible, locked-down stock router web interfaces. I wanted real-time RF signal metrics (SINR, RSRP, cell tower handover), remote SMS/OTP access, remote AT command execution, and true bandwidth ledgers without relying on sketchy vendor cloud services.

Over the past few years, I built a custom end-to-end telemetry and remote management suite running directly between my Qualcomm Snapdragon X55 (SDX55) 5G gateway and an Oracle Cloud (OCI) VPS.

Here’s a breakdown of the build, the architecture, and a fun technical lesson in outgrowing SQLite under high-frequency writes!

1. The Hardware & Modem Stack

• Hardware: Qualcomm SDX55-based 5G Gateway (`SG500M2-X`) running an embedded OpenWrt/Linux kernel.
• Network: SIM card offering truly unlimited 5G plans.
• Connection Mode: USB CDC-ECM Ethernet pass-through straight into my main network.
• On-Device Daemon:
• Custom shell & Perl scripts running natively on the modem baseband.
• Captures carrier state, RF telemetry (`AT+CPIN?`, `AT+QENG="servingcell"`, `AT+QRSRP`, thermals), system memory, and uptime every 2–3 seconds.
• Pushes updates securely to my remote VPS via HTTPS with token authentication.
• Listens for outbound command queues (AT commands, SMS sends, USSD codes, config snapshots).
• Hardware SIM PIN Security & Boot Auto-Unlock:
  • Wrote an automated SIM unlock daemon directly on the modem.
  • The Challenge: Qualcomm basebands take 15–25 seconds after boot to transition out of `NOT READY` / `SIM BUSY`. If you try to unlock too early, you get errors; if you loop blindly, you burn your 3 SIM PIN retries and lock yourself out. Implemented a 35s settling poll with single-attempt fail-safe lockouts, PUK guards, and encrypted local storage so the modem seamlessly boots and attaches to 5G SA without exposing PINs to the web UI.

1. In-Memory DNS & Edge Ad-Blocking

Instead of running a separate Pi-hole instance, the modem itself hosts an in-memory DNS caching proxy and sinkhole:

• Syncs with blocklists directly into RAM.
• Sub-millisecond local cache hits (`0.1ms – 0.2ms`).
• Streams DNS query metrics (top blocked domains, queries/min, block percentage) directly into the telemetry payload.

1. The Cloud VPS & The SQLite Bottleneck

On the cloud side, I host a lightweight dashboard and ingestion hub on an OCI Free Tier VPS (1 vCPU, 1 GB RAM):

• Pushes real-time telemetry straight to the browser (RSRP, SINR, latency, active tower eNB).
• Remote AT Terminal & SMS Manager: Send AT commands, run USSD queries, read incoming SMS, and auto-detect OTPs with Telegram alert integration.
• Bandwidth & Data Usage Ledger: Tracks daily download/upload, peak bitrates, and period totals.

Why SQLite Failed Here:

Originally, the VPS backend ran on SQLite. Within a few days:

1. The database grew to 1.2 GB.

2. Because the modem pushes telemetry every 2 seconds, while background tasks check heartbeats, workers run 15-minute summary digests, and WebUI clients query history, SQLite started throwing:

   sqlite3.OperationalError: database is locked POST /api/telemetry HTTP/1.1 400 – SQLite's database-level write locks caused incoming telemetry packets to be dropped under simultaneous dashboard reads.

3. VPS RAM spiked to ~256 MB just keeping SQLite page caches in memory.

4. The Move to Oracle Autonomous Database (JSON Database 26ai)

Since the VPS is already on Oracle Cloud Infrastructure, I migrated the backend to an Oracle Autonomous JSON Database (26ai):

• Connection Pooling: Built a connection pool (`min=2, max=16`) via `python3-oracledb` in Thin mode using an mTLS wallet.
• Full Multi-Version Concurrency (MVCC): Readers never block writers and writers never block readers. All `database is locked` errors vanished instantly.
• VPS Offloading: Memory footprint on the tiny VPS dropped from 256 MB down to ~35 MB, because indexing, sorting, and analytical queries run entirely on Oracle's Exadata cloud storage cells.
• Performance:
• Current database size: 312,000+ telemetry records.
• Multi-year ledger query (aggregating sums, peak speeds, and day counts across 621 days): 6.59 ms
• Single-row live dashboard lookup: 2.33 ms.
• Prometheus exporter (`/metrics`) runs continuously with zero impact on ingestion.

1. Results & UI Features

The dashboard now provides:

1. Live Modem Vitals: Real-time speedometers, RSRP/SINR indicators, thermal tracking (CPU & 5G module), and public IP rotation history.

2. Multi-Year Data Usage Ledger: Clean period breakdown (This Month, Last Month, This Year, Last Year) with peak bitrate tracking.

3. DNS Analytics: Live ad-block rate, query latency distributions, and searchable query logs.

4. Console & Control Suite: SIM PIN lock management, SMS inbox with OTP alerts, remote AT command queue with exit receipts, and compressed config backup downloads.

TL;DR

• The Setup: Qualcomm SDX55 5G gateway (Jio 5G SA) running custom on-device Linux daemons for real-time RF telemetry (RSRP/SINR/tower handover), edge in-memory DNS ad-blocking, and fail-safe SIM PIN boot auto-unlock, pushing metrics every 2–3s to a cloud dashboard.
• The Bottleneck: The backend initially used SQLite on an OCI Free Tier VPS (1GB RAM), which choked under high-frequency writes and concurrent dashboard reads (sqlite3.OperationalError: database is locked) once the DB passed 1.2 GB.
• The Fix: Migrated to Oracle Autonomous Database (JSON Database 26ai) using an mTLS connection pool.
• The Result: 100% elimination of database locking, VPS RAM usage dropped from ~256MB to ~35MB, and multi-year bandwidth ledger aggregations across 312,000+ rows now execute in 6.59 ms.

ai usage for this post and project :

• English is not my primary language, so this post was translated using ai.
• Not a UI developer, so the ui was written with help of ai. (All services were written by me).