db-inspector

What problem does it solve?

The DB Inspector provides focused, server-side diagnostics and safe cleanup for AutomationOne PostgreSQL/SQLite deployments, helping engineers quickly identify schema issues, migration gaps, orphaned records, retention problems, backup/restore status, and index or performance bottlenecks.

Core Features & Use Cases

• Schema & Migration Checks: Verify Alembic migration state, missing columns, and unique/foreign key constraints that could break persistence.
• Data Integrity & Orphan Detection: Find orphaned sensor and actuator configs, stale mock devices, and cascading delete risks before destructive actions.
• Performance & Index Analysis: Inspect index usage, table bloat, sequential scans, and active connection patterns to guide tuning.
• Retention, Cleanup & Backups: Audit configured retention jobs, run safe dry-run cleanups, and create or restore compressed pg_dump backups with explicit confirmation.
• Operational Safety: Enforces SELECT-before-DELETE, dry-run defaults, and explicit user confirmation for destructive operations; avoids schema/code changes.
• Use Case Example: When sensor readings stop persisting for a device, run migration and FK checks, inspect related sensor_data and esp_heartbeat_logs, detect orphans, and produce a remediation report.

Quick Start

Run a full database inspection on the AutomationOne Postgres container to check migration status, key table sizes, index usage, orphaned records, retention job health, and backup availability, then summarize findings with recommended next steps.