clavitor/consolidate-clavitor-db.sh

#!/bin/bash
# consolidate-clavitor-db.sh
# Consolidates clavitor.db by removing unused tables and migrating data
# Run locally first, then on Zurich server

set -e

DB="${1:-clavitor.db}"
BACKUP="${DB}.backup.$(date +%Y%m%d_%H%M%S)"

echo "=== Clavitor DB Consolidation ==="
echo "Target: $DB"
echo "Backup: $BACKUP"

# Backup first
cp "$DB" "$BACKUP"
echo "✓ Backup created"

# Check current state
echo ""
echo "=== Current Table Stats ==="
sqlite3 "$DB" <<'EOF'
SELECT '--- Row Counts ---';
SELECT 'accounts:          ' || COUNT(*) FROM accounts;
SELECT 'domain_scopes:     ' || COUNT(*) FROM domain_scopes;
SELECT 'incidents:         ' || COUNT(*) FROM incidents;
SELECT 'incident_updates:  ' || COUNT(*) FROM incident_updates;
SELECT 'login_codes:       ' || COUNT(*) FROM login_codes;
SELECT 'maintenance:       ' || COUNT(*) FROM maintenance;
SELECT 'outages:           ' || COUNT(*) FROM outages;
SELECT 'pops:              ' || COUNT(*) FROM pops;
SELECT 'sessions:          ' || COUNT(*) FROM sessions;
SELECT 'telemetry:         ' || COUNT(*) FROM telemetry;
SELECT 'uptime:            ' || COUNT(*) FROM uptime;
SELECT 'uptime_daily:      ' || COUNT(*) FROM uptime_daily;
SELECT 'uptime_spans:      ' || COUNT(*) FROM uptime_spans;
SELECT 'vaults:            ' || COUNT(*) FROM vaults;
EOF

echo ""
echo "=== Phase 1: Drop Unused Tables ==="

# Tables to drop and why:
# - accounts: moved to corporate.db (2 rows)
# - login_codes: ephemeral, not needed (0 rows)
# - sessions: ephemeral, not needed (3 rows)
# - vaults: hosted vaults obsolete (1 row)
# - domain_scopes: unused feature (0 rows)
# - uptime_daily: unused aggregation (0 rows)
# - uptime_spans: unused aggregation (0 rows)

sqlite3 "$DB" <<'EOF'
PRAGMA foreign_keys = OFF;

DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS login_codes;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS vaults;
DROP TABLE IF EXISTS domain_scopes;
DROP TABLE IF EXISTS uptime_daily;
DROP TABLE IF EXISTS uptime_spans;

PRAGMA foreign_keys = ON;
EOF

echo "✓ Dropped unused tables"

echo ""
echo "=== Phase 2: Consolidate Status Tables ==="

# Merge maintenance + outages into incidents
# incidents becomes the single status table

sqlite3 "$DB" <<'EOF'
PRAGMA foreign_keys = OFF;

-- Migrate maintenance windows to incidents (if any)
INSERT INTO incidents (title, status, date, node_ids, created_at)
SELECT 
    'Maintenance: ' || COALESCE(reason, 'Scheduled'),
    CASE WHEN end_at IS NULL THEN 'active' ELSE 'resolved' END,
    datetime(start_at, 'unixepoch'),
    '', -- no specific nodes
    COALESCE(start_at, strftime('%s', 'now'))
FROM maintenance
WHERE EXISTS (SELECT 1 FROM maintenance LIMIT 1);

-- Migrate outages to incidents
INSERT INTO incidents (title, status, date, node_ids, created_at)
SELECT 
    COALESCE(description, 'Service Outage'),
    CASE 
        WHEN status = 'resolved' THEN 'resolved'
        WHEN end_at = '' THEN 'investigating'
        ELSE 'monitoring'
    END,
    start_at,
    node_id,
    COALESCE(strftime('%s', start_at), strftime('%s', 'now'))
FROM outages
WHERE EXISTS (SELECT 1 FROM outages LIMIT 1);

DROP TABLE IF EXISTS maintenance;
DROP TABLE IF EXISTS outages;

PRAGMA foreign_keys = ON;
EOF

echo "✓ Merged maintenance/outages into incidents"

echo ""
echo "=== Phase 3: Optimize telemetry ==="

# Keep only last 7 days of telemetry, create view for aggregated data
sqlite3 "$DB" <<'EOF'
-- Delete old telemetry (keep 7 days)
DELETE FROM telemetry 
WHERE received_at < strftime('%s', 'now', '-7 days');

-- Check if telemetry is actually being used
SELECT CASE 
    WHEN COUNT(*) = 0 THEN 'WARNING: telemetry table is empty - POPs not reporting?'
    ELSE 'OK: ' || COUNT(*) || ' telemetry records (last 7 days)'
END
FROM telemetry;
EOF

echo "✓ Cleaned telemetry"

echo ""
echo "=== Phase 4: Vacuum and Analyze ==="

sqlite3 "$DB" <<'EOF'
VACUUM;
ANALYZE;
EOF

echo "✓ Database optimized"

echo ""
echo "=== Final State ==="
sqlite3 "$DB" ".tables"

echo ""
echo "=== Remaining Tables ==="
cat <<'TABLEDOCS'

CORE TABLES (kept):
- pops:          28 regional POPs (source of truth)
- telemetry:     POP metrics (last 7 days)
- uptime:        Daily status per POP (for status page)
- incidents:     Consolidated incidents + maintenance + outages
- incident_updates: Timeline for incidents

DROPPED:
- accounts, sessions, login_codes, vaults → moved to corporate.db
- domain_scopes → unused feature
- uptime_daily, uptime_spans → unused aggregations
- maintenance, outages → merged into incidents

TABLEDOCS

echo ""
echo "=== Done ==="
echo "Backup: $BACKUP"
echo ""
echo "To verify: sqlite3 $DB '.tables'"