ShopDB Flask presents a HIGH overall risk profile driven by three critical factors: air-gapped deployment topology that makes incident response 10x slower, authentication design with fleet-wide credential exposure, and unbounded data growth that will cause production failure within 6-12 months without intervention.
The system currently requires 936 annual maintenance hours (0.45 FTE), but this understates reality. Ad-hoc incident response (troubleshooting collector failures, configuration drift, migration issues) adds an estimated 104 hours/year not captured in scheduled tasks. The maintenance profile is developer-heavy (55% of effort requires code/architecture skills), making the system expensive to operate in a manufacturing environment that typically staffs operator-level skills.
936
Annual Maintenance Hours
8
Critical Risks
111
Breakpoints Identified
53
Drift Vectors
Critical Risks 8 Items
These risks pose immediate threats to production availability, security, or data integrity. Failure to address within the next quarter will result in production outages or security incidents.
Shared Collector API Key - 700 endpoints share one credential. Single compromised machine exposes fleet-wide credentials. Timing attack enables key extraction in ~22k requests. Impact: Complete fleet impersonation, malicious command execution across all endpoints.
Unbounded Audit Log Growth - No retention policy. Database fills in 6 months at current rate (7M audit entries/year at 200 endpoints). Impact: Disk exhaustion, database write failures, application outage.
No Automated Backups - Production sites vulnerable to unrecoverable data loss from migration failures, disk corruption, or ransomware. Impact: Complete site data loss, 2-3 day recovery time minimum.
Python 3.14 Alpha Runtime - Docker uses unreleased Python version (alpha/beta as of Jan 2025). Dev machine on 3.13.15 creates version drift. Impact: Production instability, undefined runtime behavior, difficult-to-diagnose bugs.
Application Inventory Write Amplification - 1.92M database writes/day (99.9% waste). Full DELETE+INSERT cycle every 15 minutes regardless of changes. Impact: Database I/O exhaustion, backup size explosion, query performance degradation.
Air-Gap Deployment Prevents Rapid Response - 10x upgrade cost, three-gap bundle workflow, 2-3 day recovery time for critical fixes. Impact: Cannot rapidly patch zero-day vulnerabilities, extended outage windows.
JWT Refresh Tokens Valid 30 Days After User Deactivation - No server-side revocation. Compromised accounts retain access indefinitely until token expiry. Impact: Terminated employees retain access, privilege escalation persists after role change.
Loss of SECRET_KEY or JWT_SECRET_KEY - Invalidates all tokens fleet-wide. Requires manual re-keying of 700 endpoints with no recovery path. Impact: Complete authentication system failure, days of downtime to re-provision fleet.
High Risks 10 Items
MySQL 8.4 Innovation Track - Not LTS. LTS is 8.0.x supported until April 2026. Creates upgrade pressure and compatibility risk for air-gapped sites.
Endpoint Events Accumulate Indefinitely - 3.6M events/year with no retention policy. Critical for forensics but unmanageable without pruning.
Node 20 LTS End-of-Life April 2026 - Major version upgrade needed within 4 months with full regression testing.
TLS Certificate Expiration Monitoring Missing - Self-signed certs have no automated renewal. Expiration breaks production HTTPS.
Database Migration Failures Leave Partial Schema - MySQL DDL auto-commit defeats transaction safety. No automated rollback.
Guest Role Privilege Escalation Risk - Accidental grant of sensitive permissions enables anonymous writes and deletes.
Plugin Dependency Graph Not Enforced During Migrations - Arbitrary dict ordering can run migrations out of dependency order causing FK violations.
Schema Drift Detection Gap - Manual ALTER outside migration chain goes undetected until runtime query failures.
CORS_ORIGINS and TRUSTED_PROXY_COUNT Misconfiguration - Only 4 of 46 env vars validated. Wrong proxy count enables IP spoofing.
Docker Container Logs Accumulate Unbounded - 50-100MB/day with 700 agents. Will fill host disk without log rotation configured.
Quick Wins (22-35 Hours Total Effort)
These 7 actions address 80% of the critical risk surface and are achievable within one quarter with developer + admin collaboration.
1. Configure Audit Log Retention
Effort: 4 hoursImpact: Prevents disk exhaustion in 6 months
Major version upgrades, DR drills, security audits, capacity planning
Critical Competencies Required
Air-gap bundle workflow expertise - Create git bundles, transfer across three gaps (dev PC ↔ SMB share ↔ work PC ↔ GitHub), verify SHA-256 checksums, coordinate multi-site deployments
Multi-modal sweep of the codebase to map technical architecture, dependencies, and deployment topology. Each dimension analyzed by independent Sonnet agent.
Dependencies & Version Management
Python Runtime (3.14 Docker, 3.13.15 dev)
Docker image uses python:3.14-slim (aggressive bleeding-edge version, not yet released as stable). Dev machine runs 3.13.15. Python 3.14 is alpha/beta as of Jan 2025 and represents significant version drift risk. No .python-version file to enforce consistency.
Relevance: CRITICAL for air-gapped deployment. If 3.14 introduces breaking changes or is not available when bundles are built, deployment will fail. Should pin to stable LTS version (3.11 or 3.12) for air-gapped manufacturing environments.
Universal + Hashed Requirements (uv pip compile)
requirements.txt generated with --universal --generate-hashes flags. Provides hash verification (prevents supply chain attacks) and cross-platform resolution (single lockfile for Linux Docker + Windows dev). 40 Python packages, all hashes verified at install time.
Relevance: EXCELLENT security posture for air-gapped deployment. Hash checking prevents tampered packages. Zero CVEs in shipped Python dependencies per OSV scan.
Node.js 20 LTS and npm Dependencies
Docker uses node:20-slim for frontend build. 307 npm packages (153 dev-only). Vue 3.4.0, Vite 6.4.1. 16 CVEs found, ALL in build-only tools (vitest, vite, rollup) - NONE in shipped runtime.
Relevance: HIGH maintenance visibility needed. Build-only CVEs do not gate releases per documented policy, but Vite 6.4.1 has 3 HIGH severity advisories. Node 20 is LTS until April 2026.
MySQL 8.4 and Database Drivers
Docker image uses mysql:8.4 (latest major version). Python drivers: mysql-connector-python 9.7.0, pymysql 1.1.3. No PostgreSQL support. utf8mb4 enforced server-wide.
Relevance: MODERATE upgrade risk. MySQL 8.4 is latest Innovation track (not LTS). LTS track is 8.0.x (supported until April 2026). Should consider downgrading to mysql:8.0 LTS for production stability.
No enforcement of inter-plugin dependency order during migration runs. If plugin B's tables have foreign keys to plugin A's tables, and both have pending migrations, arbitrary dict ordering could run plugin B before A causing FK constraint violations.
Relevance: CRITICAL for multi-plugin deployments. Failed FK during migration leaves DB inconsistent with no automated rollback. Requires manual intervention and potential data loss.
MySQL Auto-Commit Defeats Transaction Safety
MySQL DDL (CREATE TABLE, ALTER TABLE) auto-commits. If a migration creates a table then fails on subsequent DDL, the table exists but alembic_version_plugin is not stamped. Re-running hits 'table already exists' error.
Relevance: HIGH maintenance burden. Every migration failure requires manual inspection to determine DB state, manual cleanup of partial changes, and re-run. No automated recovery path.
No Pre-Migration Backup Automation
Migration runner has no backup step. Failed migration in production requires manual restore from separate backup schedule. Cutover model means 20+ plugin chains run on every deploy, each a potential failure point.
Relevance: CRITICAL for production safety. Without automated backup, failed migration on air-gapped prod site requires data loss assessment and manual recovery with no guaranteed rollback path.
Plugin Architecture & Coupling
PLUGIN_TABLE_OWNERS Dependency Graph
Central ownership map in shopdb/plugins/alembic_template.py declaring which tables each of 20+ plugins owns. Drives per-plugin migration chain filtering and upgrade order. Out of sync with actual model declarations causes migration failures.
Relevance: CRITICAL for maintenance. Adding plugin tables requires updating this dict. Migration chain breaks if ownership map drifts from models. Test coverage exists but relies on manual sync discipline.
shopdb.api Contract Surface
Stable import surface (~50 exports) that plugins use instead of internal paths. Contract versioned 0.32.0 per ADR-001/ADR-002. Breaking changes require major bump and coordinated plugin updates across air-gapped sites.
Relevance: HIGH impact on upgrades. Contract changes require testing every plugin. Pre-1.0 means contract still settling, requiring tight version coordination across sister sites.
Hard Plugin Dependencies (manifest.json)
Explicit dependency chains via manifest dependencies field. Key relationships: endpoints→computers (hostname resolution), computers has no deps (foundational). Loader does topological sort and refuses to enable plugin if dependency disabled.
Relevance: HIGH for staged rollouts. Cannot enable endpoints without computers. Disabling computers blocked if endpoints enabled. Upgrade order matters.
Deployment Configuration & Infrastructure
Full deployment configuration analysis including Docker Compose services, health checks, volume management, environment variables, certificate management, and air-gap deployment differences. Click to expand for detailed findings.
Air-Gap Bundle Mechanism & Release Process
Analysis of three-gap workflow (dev PC ↔ work PC ↔ GitHub), bundle creation, integrity verification, version tracking, and rollback capabilities. Click to expand for detailed findings.
Configuration Surface & Settings Management
Settings table structure, environment variable validation, site-specific data storage (ADR-015 compliance), secret management, and configuration validation. Click to expand for detailed findings.
Authentication & Authorization Systems
Collector API key model, JWT token management, personal API tokens (PAT), permission system (RBAC), account security controls, device token authentication. Click to expand for detailed findings.
Technical failure modes identified across 6 categories: startup, runtime, upgrade, database, integration, and security. Each breakpoint includes trigger conditions, impact assessment, likelihood, detection difficulty, and recovery complexity.
Startup Sequence Failures (18 breakpoints)
Database Health Check Failure - MySQL service never reaches healthy state, blocking migrate and api service start. Triggers: wrong credentials, port already bound, volume corruption. Detection: Easy. Recovery: Moderate.
Environment Variable Validation Failure - ProductionConfig.validate() raises ConfigError at migrate service startup. Triggers: SECRET_KEY/JWT_SECRET_KEY using dev defaults, DATABASE_URL not set. Detection: Easy. Recovery: Simple.
Core Migration Chain Failure - flask db upgrade fails with Alembic error or constraint violation. MySQL DDL auto-commit creates table then fails on subsequent DDL. Detection: Moderate. Recovery: Complex.
15 additional startup breakpoints documented in full report...
Runtime Operational Failures (21 breakpoints)
Database Connection Pool Exhaustion - 200 endpoint agents polling every 15 minutes + UI users exceed default pool size (15 total). Each gevent worker has its own pool. Likelihood: Medium. Detection: Moderate.
Disk Space Exhaustion (Database Volume) - Unbounded audit log + endpoint events + application inventory churn fill db_data volume. No retention policies configured. Likelihood: Very High. Detection: Easy (if monitored).
19 additional runtime breakpoints documented in full report...
Upgrade & Migration Failures (19 breakpoints)
Database-Specific Failures (17 breakpoints)
Integration Breakpoints (20 breakpoints)
Security Vulnerabilities (16 breakpoints)
Total: 111 breakpoints identified across all categories. Full detailed analysis with triggers, impacts, likelihood, and recovery procedures available in project documentation.
Phase 3: User Drift Analysis (53 Drift Vectors)
User-induced failure modes from settings modifications, data integrity violations, permission misconfigurations, plugin management errors, and operational neglect. Each drift vector includes user action, resulting state, symptoms, severity, reversibility, detection method, and prevention strategy.
Settings Modifications Gone Wrong (12 vectors)
Invalid site_timezone Value - Admin sets invalid IANA timezone name. Timezone parsing fails silently, falls back to America/New_York default. Severity: Moderate. Reversible: Yes.
Wrong TRUSTED_PROXY_COUNT - Setting to 1 when no proxy allows X-Forwarded-For spoofing. Setting to 0 when behind proxy locks out entire facility. Severity: Major. Reversible: Yes (requires admin access).
Data Integrity Violations (15 vectors)
Permission Misconfigurations (9 vectors)
Plugin Management Errors (8 vectors)
Operational Neglect (9 vectors)
Total: 53 drift vectors identified across all categories. Prevention strategies focus on input validation, automated checks, and operator training.
Monitoring Checklist
Daily Checks (5 minutes)
Endpoint health dashboard - verify 95%+ of 700 agents reported within last 24 hours
Backup completion - check last restic snapshot timestamp within 24 hours
Docker service health - verify db, api containers healthy
Database disk space - db_data <85%, host disk <85%