sqlite-inspector

MikoPBX SQLite Database Inspecting

Quick database verification for MikoPBX after REST API operations to ensure data consistency and referential integrity.

What This Skill Does

• ✅ Verifies REST API results are correctly persisted in database
• ✅ Validates foreign key relationships and referential integrity
• ✅ Checks data consistency against model schemas
• ✅ Inspects CDR (Call Detail Records) for call routing verification
• ✅ Debugs data issues at the lowest database level

When to Use

Use this skill when you need to:

• After API operations - Verify create/update/delete operations modified database correctly
• Debugging data issues - Investigate inconsistencies between API responses and database state
• Before integration tests - Ensure database is in expected state
• Validating foreign keys - Check relationships between tables are correct
• Inspecting CDR records - Query call history for testing routing and recording

How It Works

All queries execute inside the MikoPBX Docker container using docker exec with sqlite3:

docker exec <container_id> sqlite3 /cf/conf/mikopbx.db "SELECT * FROM m_Extensions LIMIT 5"

Available Databases

Main Database: /cf/conf/mikopbx.db

Primary configuration database containing:

• Extensions (SIP/IAX, queues, IVR, conferences)
• Users and authentication
• Routing rules (incoming/outgoing)
• Providers (SIP/IAX trunks)
• Security settings (firewall, fail2ban, network filters)

CDR Database: Location varies

Call detail records database:

• cdr_general - Historical call records
• cdr - Active calls

---

Quick Start

1. Get Container ID

# List MikoPBX containers
docker ps | grep mikopbx

# Or use auto-detection script
./scripts/db_query.sh "SELECT 1"

2. Execute Simple Query

# Using docker exec directly
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT * FROM m_Extensions LIMIT 5" -header -column

# Using helper script
./scripts/db_query.sh "SELECT * FROM m_Extensions LIMIT 5"

3. Common Output Formats

# Column format (default, readable)
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT * FROM m_Extensions LIMIT 5" -header -column

# JSON format (for scripts)
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT * FROM m_Extensions LIMIT 5" -json

# CSV export
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT * FROM m_Extensions" -csv -header > extensions.csv

---

Top 5 Common Verification Patterns

1. Verify Extension Creation

After creating extension via API:

# Check extension exists
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT number, type, callerid, userid FROM m_Extensions WHERE number='100'" \
  -header -column

# Check SIP account created (for SIP extensions)
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT extension, secret, transport FROM m_Sip WHERE extension='100'" \
  -header -column

# Verify complete profile with foreign keys
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT e.number, e.type, e.callerid, u.username, u.email, s.secret
   FROM m_Extensions e
   LEFT JOIN m_Users u ON e.userid = u.id
   LEFT JOIN m_Sip s ON e.number = s.extension
   WHERE e.number='100'" \
  -header -column

Expected: All fields populated, foreign keys valid (no NULLs for required relationships)

---

2. Verify Provider Configuration

After creating/updating provider:

# Check provider record
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT uniqid, type, description, host, disabled FROM m_Providers
   WHERE uniqid='PROVIDER_ID'" -header -column

# Verify routing rules exist
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT
     (SELECT COUNT(*) FROM m_IncomingRoutingTable WHERE provider='PROVIDER_ID') as incoming,
     (SELECT COUNT(*) FROM m_OutgoingRoutingTable WHERE providerid='PROVIDER_ID') as outbound"

Expected: Provider exists, has at least one routing rule

---

3. Verify Queue Configuration

After creating/modifying queue:

# Check queue with members
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT cq.name, cq.extension, cq.strategy,
          e.number as member_ext, e.callerid as member_name,
          cqm.priority
   FROM m_CallQueues cq
   LEFT JOIN m_CallQueueMembers cqm ON cq.uniqid = cqm.queue
   LEFT JOIN m_Extensions e ON cqm.extension = e.number
   WHERE cq.uniqid='QUEUE_ID'
   ORDER BY cqm.priority" -header -column

Expected: Queue exists, all members have valid extensions, priorities are correct

---

4. Check Data Consistency (Find Orphans)

Find broken foreign key relationships:

# Orphaned SIP accounts (no matching extension)
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT s.extension FROM m_Sip s
   LEFT JOIN m_Extensions e ON s.extension = e.number
   WHERE e.number IS NULL"

# SIP extensions without accounts
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT e.number FROM m_Extensions e
   LEFT JOIN m_Sip s ON e.number = s.extension
   WHERE e.type='SIP' AND s.extension IS NULL"

# Routing rules pointing to non-existent extensions
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT ir.number, ir.extension FROM m_IncomingRoutingTable ir
   LEFT JOIN m_Extensions e ON ir.extension = e.number
   WHERE ir.action='extension' AND e.number IS NULL"

Expected: No results (empty) - indicates data integrity is maintained

---

5. Query CDR Records

Verify call routing and recording:

# Calls for specific extension (last 20)
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT start, src_num, dst_num, duration, billsec, disposition
   FROM cdr_general
   WHERE src_num = '100' OR dst_num = '100'
   ORDER BY start DESC
   LIMIT 20" -header -column

# Answered calls today
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT COUNT(*) as total_calls, SUM(billsec) as total_duration
   FROM cdr_general
   WHERE DATE(start) = DATE('now')
     AND disposition = 'ANSWERED'"

# Active calls right now
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT src_num, dst_num, start, duration FROM cdr
   WHERE endtime IS NULL OR endtime = ''" -header -column

Expected: Call records match expected call flow

---

Key Tables Reference

Core Tables

• m_Extensions - All phone numbers (SIP, queues, IVR, conferences)
• m_Sip - SIP account configurations
• m_Users - User accounts and profiles
• m_Providers - SIP/IAX trunks

Routing

• m_IncomingRoutingTable - DID/incoming routes
• m_OutgoingRoutingTable - Outbound routes

Call Features

• m_CallQueues + m_CallQueueMembers - Call queues
• m_ConferenceRooms - Conference rooms
• m_IvrMenu + m_IvrMenuActions - IVR menus

Security

• m_NetworkFilters - IP-based restrictions
• m_FirewallRules - Firewall rules
• m_Fail2BanRules - Intrusion prevention

CDR

• cdr_general - Historical call records
• cdr - Active calls

For complete schema with all columns and relationships, see Schema Reference

---

Helper Script Usage

The scripts/db_query.sh helper script simplifies queries:

# Auto-detect container
./scripts/db_query.sh "SELECT * FROM m_Extensions LIMIT 5"

# Specify container
./scripts/db_query.sh -c abc123 "SELECT * FROM m_Users"

# JSON output
./scripts/db_query.sh -f json "SELECT * FROM m_Extensions"

# CSV export
./scripts/db_query.sh -f csv "SELECT * FROM m_Extensions" > extensions.csv

# Different database (CDR)
./scripts/db_query.sh -d /storage/usbdisk1/mikopbx/astlogs/asterisk_cdr/master.db \
  "SELECT * FROM cdr LIMIT 10"

# Show help
./scripts/db_query.sh --help

---

Troubleshooting

Database Locked Error

# Check processes using database
docker exec <container_id> lsof /cf/conf/mikopbx.db

# Use read-only mode
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db \
  "SELECT * FROM m_Extensions" -readonly

Permission Denied

# Run as root
docker exec -u root <container_id> sqlite3 /cf/conf/mikopbx.db "SELECT 1"

Database Not Found

# Verify database exists
docker exec <container_id> ls -l /cf/conf/mikopbx.db

# Find CDR database location
docker exec <container_id> find /storage -name "*.db"

---

Additional Resources

Complete Documentation

• Schema Reference - Complete table definitions, columns, foreign keys, enum values
• Common Queries - SQL query patterns library with 50+ examples
• Verification Scenarios - Step-by-step verification workflows for all entity types

Related Resources

• Model Documentation - /Users/nb/PhpstormProjects/mikopbx/Core/src/Common/Models/CLAUDE.md
• REST API Documentation - /Users/nb/PhpstormProjects/mikopbx/Core/src/PBXCoreREST/CLAUDE.md

---

Tips & Best Practices

1. Always use -header -column for readable output during development
2. Use -json for scripting and automation
3. Check both directions of relationships (e.g., extension→user AND user→extensions)
4. Use LEFT JOIN to detect missing foreign key relationships
5. Export to CSV for complex analysis in spreadsheets
6. Quote queries properly - use double quotes for entire query
7. Limit large queries - Use LIMIT for tables like CDR
8. Verify enum values - Check DISTINCT values match expected enums

---

Quick Commands Cheat Sheet

# List all tables
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db ".tables"

# Show table schema
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db ".schema m_Extensions"

# Count records
docker exec <container_id> sqlite3 /cf/conf/mikopbx.db "SELECT COUNT(*) FROM 

---

*Content truncated.*