📱 Home Assistant & UniFi Device Management Standard#

1. Executive Summary & Architecture#

This document defines the Single Source of Truth (SSOT) Device Management Architecture for the Homelab environment.

To optimize speed, usability, and data integrity:

1. Google Sheets serves as the Primary User Entry Interface. Adding or editing devices in the spreadsheet is fast, accessible on mobile/desktop, and requires zero complex IPAM UI navigation.
2. NetBox (v4.2) serves as the Automated IPAM & DCIM System of Record. It is hosted on 10.1.2.202:8082 and reconciled directly from Google Sheets via terminal alias.

flowchart LR
    A["Google Sheets (User Entry)"] -->|Mac Studio Alias: netbox| B["sync_gsheets_to_netbox.py"]
    B -->|Non-Destructive Delta Sync| C["NetBox v4.2 (10.1.2.202:8082)"]
    C -->|API & Interoperability| D["UniFi UDM / Home Assistant / Pi-hole"]

2. Standard Operating Procedure (SOP) for Device Management#

A. Adding or Editing Devices#

1. Open the Google Sheet inventory (Devices tab).
2. Add or edit any row (Location, Name, Serial, MAC, IP, Type, Brand).
3. Open your Mac Studio terminal and type:

   netbox

4. Outcome: The script fetches your spreadsheet, securely sends the inventory to NetBox on 10.1.2.202:8082, and updates your network records in seconds.

B. Retiring or Deleting a Device#

• Option 1 (Archive / Keep Record): Change the Location column in Google Sheets to Z-Inactive.
  • Behavior: The sync script automatically skips all Z-Inactive rows during import, removing the device from active NetBox IPAM while preserving historical records in your spreadsheet.
• Option 2 (Permanent Delete): Delete the row from the Google Sheet and run netbox.

3. Work Completed#

During the system implementation, the following key engineering milestones were achieved:

1. Terminal Alias netbox Implemented on Mac Studio#

• Added the shell alias alias netbox="python3 /Users/marc/Scripts/Linux/Netbox/Netbox_sync_gsheets_to_netbox.py" to ~/.zshrc.
• Zero credentials or configuration files exist on disk anywhere (no .env, .env.template, or credentials.json on Mac Studio, NAS, server, or Git). All keys are read dynamically in-memory from your 1Password vault at runtime.

2. NetBox v4.2 Primary MAC Address Binding Fix#

• In NetBox v4.2, creating a MACAddress object assigned to an interface is required, but setting interface.primary_mac_address = mac_obj is mandatory for MAC addresses to render in the Addressing -> MAC address interface view.
• The Django import handler was updated to explicitly bind primary_mac_address across all imported records.

3. Non-Destructive Delta Sync (Smart Reconciliation)#

• Replaced the destructive SQL TRUNCATE CASCADE approach with a smart reconciliation engine.
• The script matches existing NetBox records (first by MAC address, then by Name) and performs updates only on fields that changed, preserving database IDs, creation dates, and full change logging (Journal).
• Reduces database sync time from 83 seconds to under 10 seconds.

4. 1Password Secrets Integration#

• Converted all hardcoded plaintext passwords in /home/marc/docker-compose/netbox/docker-compose.yaml to 1Password URI references.
• Vault Item: op://Homelab/netbox

4. Key Scripts & File Map#