Subject: Follow-up on Issue #66: Full List of Broken Migrations in v0.3.0 + Confirmed Fix on Raspberry Pi 5

[plazmabokor]

Hi,

Following up on issue #66 regarding the v0.3.0 fresh install failure. I wanted to share additional findings that may help you reproduce and fix the bug more quickly.

After extensive troubleshooting, I successfully got Circuit Breaker running on a Raspberry Pi 5 (aarch64, 4GB RAM, SSD) by manually patching the affected migration files. Here is a full breakdown of what we found.

---

Environment

• Hardware: Raspberry Pi 5, 4GB RAM, 30GB SSD
• OS: Raspberry Pi OS Lite 64-bit (Debian GNU/Linux 13 Bookworm)
• Install method: Native installer via --local-bundle flag
• Circuit Breaker version: v0.3.0

---

Root Cause

The v0.3.0 installer ships with a pre-populated database that already contains certain tables and columns. When the Alembic migration chain runs on a fresh install, several migrations attempt to CREATE tables or ADD columns that already exist, causing a crash loop. Because the failure happens inside a transaction, the alembic_version table is never committed, so every restart re-runs all migrations from scratch and hits the same errors.

---

Affected Migration Files

The following three migration files need to be patched to use idempotent SQL:

1. 0071_network_peer_connection_type.py

Replace the batch_alter_table block with:

def upgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("ALTER TABLE network_peers ADD COLUMN IF NOT EXISTS connection_type VARCHAR"))
    conn.execute(sa.text("ALTER TABLE network_peers ADD COLUMN IF NOT EXISTS bandwidth_mbps INTEGER"))

2. 0060_add_kb_oui_table.py

Replace op.create_table() with:

def upgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS kb_oui (
            prefix VARCHAR(6) PRIMARY KEY,
            vendor VARCHAR(128) NOT NULL,
            device_type VARCHAR(64),
            os_family VARCHAR(32),
            source VARCHAR(32) NOT NULL DEFAULT 'learned',
            seen_count INTEGER NOT NULL DEFAULT 1,
            first_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """))

3. 0078_device_roles_table.py

Replace op.create_table() with CREATE TABLE IF NOT EXISTS and add duplicate-safe bulk insert:

def upgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS device_roles (
            id SERIAL PRIMARY KEY,
            slug VARCHAR NOT NULL UNIQUE,
            label VARCHAR NOT NULL,
            rank INTEGER NOT NULL DEFAULT 5,
            icon_slug VARCHAR,
            is_builtin BOOLEAN NOT NULL DEFAULT false,
            device_type_hints JSONB NOT NULL DEFAULT '[]',
            hostname_patterns JSONB NOT NULL DEFAULT '[]',
            created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """))
    conn.execute(sa.text("ALTER TABLE app_settings ADD COLUMN IF NOT EXISTS roles_version INTEGER NOT NULL DEFAULT 0"))
    conn.execute(sa.text("ALTER TABLE scan_results ADD COLUMN IF NOT EXISTS role_suggestion VARCHAR"))
    # Only insert roles that do not already exist
    existing = conn.execute(sa.text("SELECT slug FROM device_roles")).fetchall()
    existing_slugs = {row[0] for row in existing}
    # ... bulk insert skipping existing_slugs

---

Additional Note

The alembic_version table uses VARCHAR(32) for version_num, but the revision ID '0071_network_peer_connection_type' is 33 characters long. This causes an additional error when trying to manually insert the revision. The column should be VARCHAR(64) or longer.

---

Result

After applying all three patches, Circuit Breaker v0.3.0 is now fully operational on Raspberry Pi 5. All services pass cb doctor with 'All systems operational.'

I hope these findings help you ship a clean v0.3.1 quickly. Happy to test a patched release if needed.

Best regards,
plazmabokor