Database

Bloqd uses SQLite for data storage. This guide covers database management, optimization, and troubleshooting.

Database Overview

Location

Deployment	Database Path
Docker	./data/bloqd.db (mounted volume)
Manual	Configured in .env

Database Engine

• Engine: SQLite 3
• Mode: WAL (Write-Ahead Logging)
• File Size: Grows with ban history

Schema Overview

Core Tables

servers          - Managed server information
bans             - Ban history and records
whitelist        - Whitelisted IPs/CIDRs
templates        - Jail templates
users            - User accounts
api_keys         - API key credentials
settings         - System configuration

Relationships

servers
  ├── bans (one-to-many)
  ├── server_templates (many-to-many with templates)
  └── server_whitelist (many-to-many with whitelist)

users
  ├── api_keys (one-to-many)
  ├── user_servers (many-to-many with servers)
  └── audit_log (one-to-many)

Database Maintenance

Check Database Size

# File size
ls -lh ./data/bloqd.db

# Table sizes
sqlite3 ./data/bloqd.db "
SELECT
  name,
  (SELECT COUNT(*) FROM pragma_table_info(name)) as columns,
  (SELECT COUNT(*) FROM \"\" || name || \"\") as rows
FROM sqlite_master
WHERE type='table'
ORDER BY name;
"

Vacuum Database

Reclaim unused space:

# Stop Bloqd first (recommended)
docker compose stop bloqd

# Vacuum database
sqlite3 ./data/bloqd.db "VACUUM;"

# Restart
docker compose start bloqd

Analyze Tables

Update query statistics:

sqlite3 ./data/bloqd.db "ANALYZE;"

Integrity Check

sqlite3 ./data/bloqd.db "PRAGMA integrity_check;"
# Should return: ok

Data Retention

Configure Ban Retention

Bloqd automatically prunes old ban records based on configuration:

# In .env
BAN_RETENTION_DAYS=90

Manual Cleanup

# Remove bans older than 90 days
sqlite3 ./data/bloqd.db "
DELETE FROM bans
WHERE created_at < datetime('now', '-90 days');
"

# Remove orphaned records
sqlite3 ./data/bloqd.db "
DELETE FROM server_templates
WHERE server_id NOT IN (SELECT id FROM servers);
"

# Vacuum after cleanup
sqlite3 ./data/bloqd.db "VACUUM;"

Audit Log Retention

# Remove audit logs older than 180 days
sqlite3 ./data/bloqd.db "
DELETE FROM audit_log
WHERE timestamp < datetime('now', '-180 days');
"

Querying the Database

Connect to Database

# Direct connection
sqlite3 ./data/bloqd.db

# From Docker
docker compose exec bloqd sqlite3 /app/data/bloqd.db

Common Queries

Server Statistics:

SELECT
  s.hostname,
  s.status,
  COUNT(b.id) as total_bans,
  MAX(b.created_at) as last_ban
FROM servers s
LEFT JOIN bans b ON s.id = b.server_id
GROUP BY s.id
ORDER BY total_bans DESC;

Top Banned IPs:

SELECT
  ip,
  COUNT(*) as ban_count,
  GROUP_CONCAT(DISTINCT jail) as jails
FROM bans
WHERE created_at > datetime('now', '-7 days')
GROUP BY ip
ORDER BY ban_count DESC
LIMIT 20;

Bans by Country:

SELECT
  country,
  COUNT(*) as bans,
  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM bans), 2) as percentage
FROM bans
WHERE country IS NOT NULL
GROUP BY country
ORDER BY bans DESC
LIMIT 10;

Active Sessions:

SELECT
  u.username,
  s.created_at,
  s.last_active,
  s.ip_address
FROM sessions s
JOIN users u ON s.user_id = u.id
WHERE s.expires_at > datetime('now')
ORDER BY s.last_active DESC;

Performance Optimization

Enable WAL Mode

WAL mode is enabled by default but can be verified:

sqlite3 ./data/bloqd.db "PRAGMA journal_mode;"
# Should return: wal

Create Indexes

Indexes are created automatically, but you can add custom ones:

-- Example: Index for IP lookups
CREATE INDEX IF NOT EXISTS idx_bans_ip_date
ON bans(ip, created_at DESC);

-- Example: Index for server status queries
CREATE INDEX IF NOT EXISTS idx_servers_status
ON servers(status, last_heartbeat);

Query Performance

Check slow queries:

-- Enable query timing
.timer on

-- Run your query
SELECT * FROM bans WHERE ip = '192.168.1.100';

-- Check query plan
EXPLAIN QUERY PLAN
SELECT * FROM bans WHERE ip = '192.168.1.100';

Database Migration

Migration System

Bloqd uses automatic migrations on startup:

# Check migration files
docker compose exec bloqd ls -la /app/src/db/migrations/

# Migration status (in application logs)
docker compose logs bloqd | grep -i migration

Manual Migration

# Run pending migrations
docker compose exec bloqd npm run migrate

# Rollback last migration (if supported)
docker compose exec bloqd npm run migrate:rollback

Troubleshooting

Database Locked

Symptom: SQLITE_BUSY: database is locked

Solutions:

# Check for processes using the database
fuser ./data/bloqd.db

# Restart the application
docker compose restart bloqd

# If persistent, check for zombie connections
docker compose down && docker compose up -d

Corrupt Database

Symptom: SQLITE_CORRUPT: database disk image is malformed

Solutions:

1. Try to recover:

   # Dump what we can
   sqlite3 ./data/bloqd.db ".dump" > dump.sql
   
   # Create new database
   sqlite3 ./data/bloqd-new.db < dump.sql
   
   # Replace old database
   mv ./data/bloqd.db ./data/bloqd-corrupt.db
   mv ./data/bloqd-new.db ./data/bloqd.db

2. Restore from backup:

   # Stop Bloqd
   docker compose stop bloqd
   
   # Restore backup
   cp ./backups/bloqd-latest.db ./data/bloqd.db
   
   # Start Bloqd
   docker compose start bloqd

Database Too Large

Symptom: Database file growing excessively

Solutions:

1. Check what's taking space:

   SELECT
     name,
     (SELECT COUNT(*) FROM pragma_table_info(name)) as cols,
     (SELECT COUNT(*) FROM sqlite_master WHERE type='index' AND tbl_name=name) as indexes
   FROM sqlite_master
   WHERE type='table';

2. Reduce ban retention:

   BAN_RETENTION_DAYS=30

3. Vacuum database:

   sqlite3 ./data/bloqd.db "VACUUM;"

WAL File Large

The .db-wal file can grow large during heavy writes:

# Force checkpoint
sqlite3 ./data/bloqd.db "PRAGMA wal_checkpoint(TRUNCATE);"

Export and Import

Export to JSON

# Export servers
sqlite3 ./data/bloqd.db -json "SELECT * FROM servers;" > servers.json

# Export bans (recent)
sqlite3 ./data/bloqd.db -json "
SELECT * FROM bans
WHERE created_at > datetime('now', '-7 days');
" > recent_bans.json

Export to CSV

sqlite3 ./data/bloqd.db -header -csv "
SELECT ip, jail, country, created_at
FROM bans
ORDER BY created_at DESC
LIMIT 1000;
" > bans.csv

Import Data

# Import from SQL dump
sqlite3 ./data/bloqd.db < import.sql

# Import CSV (requires .mode csv)
sqlite3 ./data/bloqd.db "
.mode csv
.import whitelist.csv whitelist
"

Database Security

File Permissions

# Recommended permissions
chmod 640 ./data/bloqd.db
chown bloqd:bloqd ./data/bloqd.db

Encryption at Rest

SQLite doesn't natively support encryption. Options:

1. Full-disk encryption - Encrypt the volume containing the database
2. SQLCipher - Not currently supported by Bloqd

Sensitive Data

The database contains sensitive information:

• User password hashes
• API keys (hashed)
• Server connection details

warning

Never share database files without sanitizing sensitive data first.