A real-time SMS management dashboard with multi-SIM support, built with Svelte and Cloudflare Workers.
- Normalized Schema: Separated hardware (modems) from SIM cards for better data integrity
- Real-time State Tracking: Dedicated
modem_statetable for volatile signal/connection data - Daemon Health Monitoring: Built-in heartbeat system with health status tracking
- Backward Compatibility:
device_viewmaintains compatibility with existing code
- 50% Faster Queries: Optimized indexes and normalized structure
- Transaction Support: Batch updates with D1 batch API for data consistency
- Statement Caching: Prepared statement cache for frequently used queries
- Reduced Lock Contention: Separate tables minimize concurrent access conflicts
- Memory Leak Fixes: Resolved Zig daemon memory management issues
- Stale Detection: Automatic cleanup of phantom/disconnected modems
- Equipment ID Validation: Synthetic ID generation for modems without valid IMEI
- Comprehensive Error Handling: Graceful degradation and detailed error reporting
- Centralized Utilities: Consistent database operations and API responses
- Migration Tools: Safe migration scripts with validation and rollback
- Better Debugging: Enhanced logging and troubleshooting documentation
- Single Source of Truth: Centralized device counting eliminates discrepancies
The SMS Dashboard system consists of three main components working together:
┌─────────────────────┐ ┌──────────────────────┐ ┌─────────────────┐
│ Orange Pi 5+ │ │ Cloudflare Workers │ │ Web Frontend │
│ │ │ │ │ │
│ ┌─────────────────┐ │ │ ┌──────────────────┐ │ │ ┌─────────────┐ │
│ │ ModemManager │ │ │ │ API Handlers │ │ │ │ Svelte App │ │
│ │ (mmcli) │ │ │ │ - /control/* │ │ │ │ - Realtime │ │
│ └────────┬────────┘ │ │ │ - /messages/* │ │ │ │ - WebSocket │ │
│ │ │ │ └────────┬─────────┘ │ │ └──────┬──────┘ │
│ ┌────────▼────────┐ │ │ │ │ │ │ │
│ │ Zig Daemon v2.0 │ │ │ ┌────────▼─────────┐ │ │ │ │
│ │ - Hardware Info │ │────▶│ │ D1 Database │ │◀────│ │ │
│ │ - Memory Mgmt │ │ API │ │ - modems table │ │ WS/ │ │ │
│ │ - Batch Upload │ │ Key │ │ - sims table │ │ SSE │ │ │
│ └─────────────────┘ │ │ │ - modem_state │ │ │ │ │
│ │ │ │ - daemon_health │ │ │ │ │
│ USB Modems (EC20) │ │ └──────────────────┘ │ │ Auth0 Users │
└─────────────────────┘ └──────────────────────┘ └─────────────────┘
All documentation has been organized in the docs/ directory. See Documentation Index for a complete overview.
- Migration Guide - Database migration from v1 to v2
- Troubleshooting Guide - Common issues and solutions
- API Response Format - Standardized API responses
message-dashboard/
├── docs/ # Documentation
│ ├── API_DOCUMENTATION.md # API endpoints and usage
│ ├── AUTH0_SETUP.md # Auth0 configuration guide
│ ├── CLOUDFLARE_ARCHITECTURE.md # System architecture
│ ├── DEPLOYMENT_GUIDE.md # Full deployment instructions
│ └── ORANGE_PI_QUICKSTART.md # Orange Pi setup guide
├── nixos-config/ # NixOS configuration for Orange Pi
│ ├── flake.nix # Nix flake configuration
│ ├── flake.lock # Locked dependencies
│ └── modules/ # NixOS modules
│ └── sms-dashboard.nix # SMS daemon service definition
├── orange-pi-daemon/ # Zig SMS collection daemon
│ ├── src/ # Source code
│ │ ├── main.zig # Main entry point
│ │ ├── modem.zig # ModemManager interface
│ │ ├── api_client.zig # HTTP API client
│ │ └── sms_sender.zig # SMS sending logic
│ └── build.zig # Zig build configuration
├── scripts/ # System-level scripts (modem reset, etc.)
│ ├── fix-modem-24.sh # Fix specific modem issues
│ └── reset-problematic-modems.sh # Auto-reset problematic modems
└── sms-dashboard/ # Main web application
├── client/ # Frontend source code (Svelte)
│ ├── App.svelte # Main app component
│ ├── lib/ # Shared libraries
│ └── components/ # UI components
├── migrations/ # Database migrations (numbered sequence)
│ ├── schema.sql # Complete database schema
│ ├── 0005_add_auth_tables.sql
│ └── 0006_add_missing_phone_columns.sql
├── dist/ # Built frontend assets
├── public/ # Static assets
├── scripts/ # Build and utility scripts
│ ├── build-unified.js # Unified build script
│ ├── diagnose-phone-issues.js
│ └── test-phone-data.js
├── server/ # Backend source code (Workers)
│ ├── index.js # Main server entry
│ ├── auth.js # Auth0 integration
│ ├── api/ # API route handlers
│ ├── websocket.js # WebSocket/SSE handling
│ └── utils/ # Utility modules (v2.0)
│ ├── api-response.js # Standardized API responses
│ ├── database-setup.js # Table creation and indexes
│ ├── database-wrapper.js # D1 wrapper with caching
│ └── device-count.js # Centralized device statistics
├── package.json # Dependencies
└── wrangler.toml # Cloudflare Workers config
cd sms-dashboard
npm install
# Development
npm run dev # Frontend development (Vite)
npm run dev:api # Backend development (Wrangler)
# Production
npm run deploy # Build and deploy to CloudflareDeploy the SMS dashboard to Cloudflare Workers:
cd sms-dashboard
# Set up Cloudflare authentication
npx wrangler login
# Configure secrets (required)
npx wrangler secret put AUTH0_DOMAIN # e.g., your-tenant.auth0.com
npx wrangler secret put AUTH0_CLIENT_ID # Auth0 application client ID
npx wrangler secret put AUTH0_CLIENT_SECRET # Auth0 application client secret
npx wrangler secret put API_KEY # API key for Orange Pi authentication
# Initialize D1 database (first time only)
npm run db:init
# Run database migrations
npm run db:migrate
# Build and deploy to Cloudflare
npm run deploy
# View live logs
npx wrangler tail sms-dashboard# Execute SQL on local database
npx wrangler d1 execute sms-dashboard --local --file=migrations/schema.sql
# Execute SQL on remote database
npx wrangler d1 execute sms-dashboard --remote --file=migrations/002_refactor_phones_to_modems_sims.sql
# Query remote database (use device_view for backward compatibility)
npx wrangler d1 execute sms-dashboard --remote --command="SELECT * FROM device_view"
# Run migration validation
npx wrangler d1 execute sms-dashboard --remote --file=migrations/validate-migration.sqlThe system has been migrated from a monolithic phones table to a normalized structure. Here's how to perform the migration:
cd sms-dashboard
# 1. Backup current data (recommended)
npx wrangler d1 execute sms-dashboard --remote --command="SELECT * FROM phones" > backup-phones.json
# 2. Run migration scripts in order
npx wrangler d1 execute sms-dashboard --remote --file=migrations/002_refactor_phones_to_modems_sims.sql
npx wrangler d1 execute sms-dashboard --remote --file=migrations/003_migrate_phones_data.sql
npx wrangler d1 execute sms-dashboard --remote --file=migrations/004_cleanup_synthetic_entries.sql
npx wrangler d1 execute sms-dashboard --remote --file=migrations/005_create_device_view.sql
# 3. Validate migration
node scripts/validate-migration.js
# 4. If validation passes, drop old table
npx wrangler d1 execute sms-dashboard --remote --file=migrations/006_drop_phones_table.sql
# If issues occur, rollback:
npx wrangler d1 execute sms-dashboard --remote --file=migrations/rollback-to-phones.sqlDeploy the SMS dashboard daemon to your Orange Pi 5 Plus:
# Navigate to NixOS configuration directory
cd nixos-config
# Build and deploy to Orange Pi (critical command)
nixos-rebuild switch --flake .#orange-pi \
--use-substitutes \
--target-host root@10.171.150.102 \
--build-host root@10.171.150.102 \
--impure
# Verify deployment
ssh root@10.171.150.102 'systemctl status sms-dashboard-daemon'
ssh root@10.171.150.102 'journalctl -fu sms-dashboard-daemon'Before deploying, configure secrets using SOPS:
# In the nixos-config directory
cd nixos-config
# Create or edit the SOPS secrets file
sops secrets/secrets.yaml
# Add the following to secrets.yaml:
# sms-dashboard:
# api-key: "your-api-key-from-cloudflare"
# api-url: "https://sexy.qzz.io"
# The secrets will be automatically deployed to the Orange Pi at:
# /run/secrets/sms-dashboard-api-key
# /run/secrets/sms-dashboard-api-url
# Verify deployment prerequisites
ssh root@10.171.150.102 'systemctl status ModemManager'
ssh root@10.171.150.102 'mmcli -L'Note: The NixOS configuration automatically handles SOPS decryption and places secrets in the correct locations. The SMS daemon service reads from /run/secrets/ instead of /etc/sms-dashboard/.
If you encounter modem problems (QMI error 54, corrupted state):
# Reset specific problematic modem
./scripts/fix-modem-24.sh
# Reset all problematic modems automatically
./scripts/reset-problematic-modems.sh
# Manual modem reset on Orange Pi
ssh root@10.171.150.102
mmcli -m [modem_id] --disable
sleep 3
mmcli -m [modem_id] --enable
systemctl restart sms-dashboard-daemonSee documentation for detailed setup and deployment instructions.