(n.b.: This is an alternative Part 3/Part 4/Part 6 for Scythe’s original GeoDrops → Home Assistant guide. Same overall pipeline, same BigQuery dataset — but it runs the sync as a proper AppDaemon add-on instead of a hand-built venv reached via docker exec, and it fixes a few BigQuery field names that have drifted since the original script was written. Parts 1 and 2 are unchanged from the original; everything else below replaces the corresponding original section.)
(AI Note: Just as Claude was used for the original, this updated version is created as the final result of a Claude Sonnet 5 working session I completed and tested. Use at your own discretion.)
(Terminology note: as of Home Assistant 2026.2, Settings → Add-ons was renamed Settings → Apps — same underlying Supervisor-managed Docker containers, just relabeled. This guide uses the current “Apps” terminology below. Unfortunately AppDaemon has its own, unrelated notion of “apps” — the Python scripts you write in its apps/ folder — so where both meanings could be confused, this guide spells out “AppDaemon app” for the script and just “App” for the Settings → Apps install.)
Why a different Part 3
The original approach (docker exec -it homeassistant /bin/bash, build a venv under /config, wire it up with shell_command: + a time_pattern automation) works, but it has a couple of rough edges worth avoiding if you’re setting this up fresh:
docker exec into the Core container requires the SSH App with “Protection mode” turned off — real shell access to the host, not just your HA config. If you don’t already have that App installed for other reasons, this guide doesn’t require it at all.- Pip-installed packages living inside
/config survive Core updates in practice, but it’s not a guarantee — the App’s own troubleshooting section already flags “Module not found” after updates as a known failure mode.
shell_command has a default execution timeout and blocks HA’s executor while the script runs.
Running this as an AppDaemon app sidesteps all three: dependencies are declared once in the App’s config and reinstalled automatically on startup, AppDaemon is a separate container so a Core update can’t touch it, and scheduling is a native in-process callback (self.run_every) instead of a shell-out.
Updated Architecture
GeoDrops Sensors → GeoDrops AI → BigQuery (public dataset)
↓
AppDaemon app (self.run_every, every 15 min)
↓
MQTT Broker (Mosquitto)
↓
Home Assistant (auto-discovered sensors)
Data freshness, staleness thresholds, and MQTT expire_after behavior are all unchanged from the original guide — see that post for the full explanation. Short version: sensor readings are typically 3-6 hours old by the time you see them, this polls BigQuery every 15 minutes, and entities go unavailable if nothing’s been published in 45 minutes.
Part 3: Install AppDaemon
-
Install AppDaemon: Settings → Apps → Install app (bottom right) → scroll to the “Home Assistant Community Apps” section (this is built in — you don’t need to add any repository URL manually) → AppDaemon → Install, then start it once. This creates its config folder at addon_configs/a0d7b954_appdaemon/ (the slug may differ slightly depending on your setup — check Settings → Apps → AppDaemon for the exact slug).
-
Declare the Python dependencies: Settings → Apps → AppDaemon → Configuration tab, add under python_packages:
python_packages:
- google-cloud-bigquery
- paho-mqtt
-
Restart the AppDaemon App. Watch its log — it reinstalls these packages fresh on every startup (takes 30-60 seconds, this is normal), and you should see Successfully installed ... google-cloud-bigquery ... before AppDaemon itself starts.
You’ll likely also see a pip dependency-resolver warning like appdaemon 4.5.13 requires requests<2.33.0 ... but you have requests 2.34.2 which is incompatible. This is a benign version-pin conflict, not an install failure — AppDaemon starts fine regardless. Worth knowing about so it doesn’t look alarming.
-
Recommended App settings: Start on Boot = on, Watchdog = on (safe here — a crash-triggered restart just re-runs initialize(), there’s no state to lose), Auto-update = off. Auto-update is worth turning off specifically for this one: a new AppDaemon base image could change the Python version or how python_packages gets installed without warning, which defeats the whole point of moving off the fragile venv approach.
Getting file access to AppDaemon’s config folder
AppDaemon’s own /config (where appdaemon.yaml and the apps/ folder live) is a separate directory from Home Assistant’s /config — it’s mounted at addon_configs/<appdaemon-slug>/ on the host. The built-in “File editor” App only browses HA’s root config, so you’ll need something else to get files in there.
Easiest option: install the Studio Code Server App (same repository as AppDaemon). After installing:
-
Settings → Apps → Studio Code Server → Configuration tab, set:
config_path: /addon_configs/a0d7b954_appdaemon
(substitute your actual AppDaemon slug if different)
-
Restart Studio Code Server. Open it (sidebar icon, or Settings → Apps → Studio Code Server → Open Web UI) and it should load directly into AppDaemon’s folder. If it still shows the old location, do a hard refresh of the tab, or use the ☰ menu → File → Open Folder and type the path manually.
(A Samba share App works too if you’d rather drag-and-drop from your desktop — just make sure it’s configured to expose addon_configs, not just config.)
Part 4: MQTT — create a dedicated login
The original guide has you dig up the existing homeassistant MQTT password from Settings → Devices & Services → MQTT → Configure, or fall back to grepping .storage/core.config_entries. In practice that password often isn’t visible in the UI at all (it’s masked), and Home Assistant’s config API doesn’t expose it either. Skip the archaeology — create a fresh, dedicated login instead:
- Settings → Apps → Mosquitto broker → Configuration tab.
- Under Logins, add an entry: username
geodrops, password — anything you like, it doesn’t need to be memorable.
- Save, then restart the Mosquitto broker App for the new login to take effect (a plain “Save” alone didn’t pick it up in testing — an explicit restart was needed).
Always wrap the password in quotes in apps.yaml, since an unquoted #, :, or a few other characters can get misparsed as YAML syntax rather than part of the string:
mqtt_password: "your-password-here"
Part 5: Install the script
- In Studio Code Server, create
apps/geodrops_sync.py with the contents below. Also create/edit apps/apps.yaml with the config block that follows.
- Copy your
geodrops-service-account.json (from Part 1) into the same apps/ folder.
A note on BigQuery schema drift: GeoDrops has changed their public dataset’s column names/availability at least twice since the original script was written. The version below is current as of August 2026 — it no longer selects moisturePctDepthSmooth1/2/3 (renamed to moisturePctDepth1/2/3), and it drops weatherPrecipMmHr, dateLastWatered, miscIsBattPoorQuality, and total7dSunExposureHour (all either removed from the schema or unused in the output anyway — the “7-Day Sun Exposure” sensor from v2 of the original script is gone as a result). If BigQuery changes things again, the AppDaemon log will show a 400 Unrecognized name: <field> error naming the exact broken column — sometimes even suggesting the corrected name — so treat that as your signal to edit the SELECT list, not a sign anything is fundamentally wrong.
A note on “Dominant Moisture”: the schema also has a moisturePct column (and a moistureRawPct counterpart) that’s separate from the three per-depth values — this is GeoDrops’ “Dominant Real Moisture,” a single whole-profile value from their AI Soil Physics Model, explicitly not just an average of the three depths (see this thread for GeoDrops staff explaining the distinction). It’s null until your sensor finishes its initial calibration/training period, same as the per-depth QCN flags — the script below only publishes a state for it once a real value shows up, confirmed working via a live sensor that had just come out of Training.
apps/geodrops_sync.py:
"""
GeoDrops BigQuery -> Home Assistant MQTT integration, running as a native
AppDaemon app (installed via Settings > Apps > AppDaemon). See the setup
guide for install steps.
"""
import json
import traceback
import appdaemon.plugins.hass.hassapi as hass
from google.cloud import bigquery
from google.oauth2 import service_account
import paho.mqtt.publish as publish
EXPIRE_AFTER_SECONDS = 45 * 60 # mark unavailable if no update in 45 min
QCN_STATE_MAP = {2: "Good", 1: "Poor", 0: "Bad", -1: "Training"}
QCN_ICON_MAP = {
2: "mdi:check-circle",
1: "mdi:alert",
0: "mdi:close-circle",
-1: "mdi:school",
}
class GeodropsSync(hass.Hass):
def initialize(self):
self.project_id = self.args["project_id"]
self.service_account_file = self.args["service_account_file"]
self.mqtt_broker = self.args.get("mqtt_broker", "core-mosquitto")
self.mqtt_port = self.args.get("mqtt_port", 1883)
self.mqtt_username = self.args.get("mqtt_username", "homeassistant")
self.mqtt_password = self.args["mqtt_password"]
self.device_mapping = {
int(k): v for k, v in self.args["device_mapping"].items()
}
interval = self.args.get("interval_seconds", 900)
credentials = service_account.Credentials.from_service_account_file(
self.service_account_file
)
self.client = bigquery.Client(credentials=credentials, project=self.project_id)
# Run once shortly after startup, then on a fixed interval.
self.run_in(self.sync, 5)
self.run_every(self.sync, "now+15", interval)
def sync(self, kwargs):
try:
self._sync()
except Exception:
self.error(traceback.format_exc())
def _sync(self):
device_ids = tuple(self.device_mapping.keys())
device_ids_str = ", ".join(str(i) for i in device_ids)
query = f"""
SELECT
deviceId, mfgSn, date,
moisturePct,
moisturePctDepth1, moisturePctDepth2, moisturePctDepth3,
temperatureCSurface, temperatureCDepth1, temperatureCDepth2, temperatureCDepth3,
miscSensorSyncDelayHour,
miscBattPercent,
qcnDepth1, qcnDepth2, qcnDepth3
FROM `geodrops-prod.db_public.p_sensor_unified`
WHERE deviceId IN ({device_ids_str})
AND createdAtOrigin > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 12 HOUR)
QUALIFY ROW_NUMBER() OVER (PARTITION BY deviceId ORDER BY date DESC) = 1
"""
self.log("Executing BigQuery query...")
rows = list(self.client.query(query))
self.log(f"Query returned {len(rows)} results")
if not rows:
self.log(
"No results in the last 12h window - check device IDs / sensor reporting",
level="WARNING",
)
return
messages = []
published_configs = 0
published_states = 0
for row in rows:
info = self.device_mapping.get(row.deviceId)
if not info:
self.log(f"Unknown deviceId {row.deviceId}, skipping", level="WARNING")
continue
name = info["name"]
mfg_sn = info["mfg_sn"]
sync_delay = row.miscSensorSyncDelayHour or 0
if sync_delay > 12:
self.log(
f"{name} ({mfg_sn}) stale (sync delay {sync_delay}h) - SKIPPING",
level="WARNING",
)
continue
elif sync_delay > 6:
self.log(
f"{name} ({mfg_sn}) stale (sync delay {sync_delay}h) - publishing anyway",
level="WARNING",
)
else:
self.log(
f"{name} ({mfg_sn}) last reading {row.date} (sync delay {sync_delay}h)"
)
device_info = {
"identifiers": [mfg_sn],
"name": f"{name.replace('_', ' ').title()} Moisture Sensor",
"manufacturer": "GeoDrops",
"model": "Soil Moisture Sensor",
"sw_version": "A2.02r1",
}
qcn = {
1: row.qcnDepth1 if getattr(row, "qcnDepth1", None) is not None else -1,
2: row.qcnDepth2 if getattr(row, "qcnDepth2", None) is not None else -1,
3: row.qcnDepth3 if getattr(row, "qcnDepth3", None) is not None else -1,
}
# Whole-profile calibrated moisture ("Dominant Real Moisture" in
# GeoDrops' terminology) - a single value distinct from just
# averaging the three depths. It's null until the sensor
# finishes its initial calibration/training period, so we only
# publish a state for it when a real value is present.
dominant_moisture = getattr(row, "moisturePct", None)
def sensor_config(key, friendly, unit=None, device_class=None, icon=None):
payload = {
"name": friendly,
"state_topic": f"geodrops/{name}/{key}",
"unique_id": f"{mfg_sn}_{key}",
"device": device_info,
"expire_after": EXPIRE_AFTER_SECONDS,
}
if icon:
payload["icon"] = icon
else:
payload["state_class"] = "measurement"
if unit:
payload["unit_of_measurement"] = unit
if device_class:
payload["device_class"] = device_class
return {
"topic": f"homeassistant/sensor/{name}_{key}/config",
"payload": json.dumps(payload),
"retain": True,
"qos": 1,
}
discovery = [
sensor_config("moisture_d1", "Moisture Depth 1", "%", "moisture"),
sensor_config("moisture_d2", "Moisture Depth 2", "%", "moisture"),
sensor_config("moisture_d3", "Moisture Depth 3", "%", "moisture"),
sensor_config("moisture_dominant", "Dominant Moisture", "%", "moisture"),
sensor_config("qcn_d1", "Quality Depth 1", icon=QCN_ICON_MAP.get(qcn[1], "mdi:help-circle")),
sensor_config("qcn_d2", "Quality Depth 2", icon=QCN_ICON_MAP.get(qcn[2], "mdi:help-circle")),
sensor_config("qcn_d3", "Quality Depth 3", icon=QCN_ICON_MAP.get(qcn[3], "mdi:help-circle")),
sensor_config("battery", "Battery", "%", "battery"),
sensor_config("sync_delay", "Sync Delay", "h"),
sensor_config("temp_surface", "Surface Temperature", "°C", "temperature"),
sensor_config("temp_d1", "Temperature Depth 1", "°C", "temperature"),
sensor_config("temp_d2", "Temperature Depth 2", "°C", "temperature"),
sensor_config("temp_d3", "Temperature Depth 3", "°C", "temperature"),
]
states = [
{"topic": f"geodrops/{name}/moisture_d1", "payload": str(row.moisturePctDepth1 or 0), "retain": True, "qos": 0},
{"topic": f"geodrops/{name}/moisture_d2", "payload": str(row.moisturePctDepth2 or 0), "retain": True, "qos": 0},
{"topic": f"geodrops/{name}/moisture_d3", "payload": str(row.moisturePctDepth3 or 0), "retain": True, "qos": 0},
{"topic": f"geodrops/{name}/qcn_d1", "payload": QCN_STATE_MAP.get(qcn[1], "Unknown"), "retain": True, "qos": 0},
{"topic": f"geodrops/{name}/qcn_d2", "payload": QCN_STATE_MAP.get(qcn[2], "Unknown"), "retain": True, "qos": 0},
{"topic": f"geodrops/{name}/qcn_d3", "payload": QCN_STATE_MAP.get(qcn[3], "Unknown"), "retain": True, "qos": 0},
{"topic": f"geodrops/{name}/battery", "payload": str(int(round(row.miscBattPercent or 0))), "retain": True, "qos": 0},
{"topic": f"geodrops/{name}/sync_delay", "payload": str(sync_delay), "retain": True, "qos": 0},
{"topic": f"geodrops/{name}/temp_surface", "payload": str(row.temperatureCSurface or 0), "retain": True, "qos": 0},
{"topic": f"geodrops/{name}/temp_d1", "payload": str(row.temperatureCDepth1 or 0), "retain": True, "qos": 0},
{"topic": f"geodrops/{name}/temp_d2", "payload": str(row.temperatureCDepth2 or 0), "retain": True, "qos": 0},
{"topic": f"geodrops/{name}/temp_d3", "payload": str(row.temperatureCDepth3 or 0), "retain": True, "qos": 0},
]
if dominant_moisture is not None:
states.append({
"topic": f"geodrops/{name}/moisture_dominant",
"payload": str(dominant_moisture),
"retain": True,
"qos": 0,
})
messages.extend(discovery + states)
published_configs += len(discovery)
published_states += len(states)
if messages:
auth = {"username": self.mqtt_username, "password": self.mqtt_password}
publish.multiple(messages, hostname=self.mqtt_broker, port=self.mqtt_port, auth=auth)
self.log(f"Published {published_configs} discovery configs and {published_states} states")
else:
self.log("No messages to publish (all sensors stale)", level="WARNING")
apps/apps.yaml:
Every line you need to personalize is flagged # EDIT ME: — after pasting this in, search the file for that exact text (Ctrl+F / Cmd+F) to make sure you’ve caught all of them. Don’t rely on the line numbers below; they’ll shift as soon as you start editing.
geodrops_sync:
module: geodrops_sync
class: GeodropsSync
# EDIT ME: your Google Cloud project ID from Part 1
project_id: your-google-cloud-project-id
# Path to the service account key AS SEEN FROM INSIDE THE APPDAEMON
# CONTAINER. If you put the JSON file in the apps/ folder next to
# geodrops_sync.py (as instructed above), this is the correct value -
# note it's /config/apps/..., not the host-side /addon_configs/... path
# you used to get the file there via Studio Code Server.
service_account_file: /config/apps/geodrops-service-account.json
# MQTT connection - use the dedicated login created in Part 4.
# Quote the password - see the note above about special characters.
mqtt_broker: core-mosquitto
mqtt_port: 1883
# EDIT ME: match the login username you created in Part 4
mqtt_username: geodrops
# EDIT ME: match the login password you created in Part 4
mqtt_password: "your-mqtt-password-here"
# How often to poll BigQuery, in seconds (900 = 15 minutes)
interval_seconds: 900
# EDIT ME: your sensor(s), from the Part 2 BigQuery lookup. "name"
# becomes part of the MQTT topic and the HA entity/device name - call
# it whatever you like (a bed name, a yard zone, etc.)
device_mapping:
YOUR_DEVICE_ID: {mfg_sn: "YOUR_SERIAL_NUMBER", name: "soil_sensor"}
# Add more devices here as needed, one line per sensor:
# deviceId: {mfg_sn: "SERIAL", name: "friendly_name"}
- Save both files. AppDaemon watches its
apps/ folder and hot-reloads automatically — no restart needed after edits (though restarting is a reliable way to force an immediate run if you don’t want to wait for the next cycle).
Part 6: Nothing to do here
Unlike the original guide, there’s no shell_command: or automation to add to configuration.yaml. The AppDaemon app schedules itself (self.run_in fires 5 seconds after every reload, then self.run_every repeats on your configured interval). This whole part of the original guide is simply not needed with this approach.
Part 7: Verify sensors appear
-
Settings → Apps → AppDaemon → Log. On success you’ll see a line like:
Published 13 discovery configs and 12 states
That’s one run, one sensor: 13 discovery configs (the 12 always-on sensors plus Dominant Moisture) but only 12 states, because Dominant Moisture doesn’t publish a state until it has a real value (see the Training note below). Once your sensor is calibrated, expect 13 discovery configs and 13 states instead. If you have N sensors, multiply accordingly.
-
Settings → Devices & Services → MQTT → you should see your GeoDrops device(s) listed (e.g., “Soil Sensor Moisture Sensor”).
-
Developer Tools → States → search your sensor name to confirm real values are populating.
One thing that’s normal, not a bug: a freshly added probe will likely show moisture readings of 0 with a quality flag of Training on all three depths for a while, and the Dominant Moisture sensor won’t appear as an entity with a state at all (its discovery config is published, but no state message is sent). That’s GeoDrops’ own model telling you it hasn’t built enough history on that specific sensor yet — not something wrong with this integration. Confirmed on a live sensor here: QCN flipped from Training to Good on all three depths, and Dominant Moisture started reporting a real percentage in the very next sync cycle after that.
Troubleshooting (AppDaemon-specific)
paho.mqtt.MQTTException: Not authorized — either the login doesn’t exist on the Mosquitto broker yet (add it under Configuration → Logins and restart the App, not just Save), or the password has an unquoted special character that YAML is mangling (see Part 4).
google.api_core.exceptions.BadRequest: 400 Unrecognized name: <field> — GeoDrops changed their BigQuery schema. Remove or rename the offending field in the SELECT list (and in the corresponding output line further down if it’s actually used). BigQuery’s error sometimes suggests the correct replacement name directly.
SyntaxError: unterminated string literal in the AppDaemon log after an edit — almost always a broken triple-quoted docstring at the top of the file. AppDaemon reports the exact line number; check that the """...""" block is still properly closed.
Script keeps reinstalling packages on every App restart — this is expected behavior for this App’s python_packages option, not a bug; it takes 30-60 seconds each time.
Can’t find addon_configs in your file browser — see the “Getting file access” section above; the built-in File Editor App won’t show it, and Studio Code Server needs its config_path option pointed there explicitly.
Entities show “Unknown” — same as the original guide: wait for the next 15-minute cycle, or restart the AppDaemon App to force an immediate run.
Everything else
Cost/usage, the free-tier math, and the long-term future-enhancements ideas (30-day trend data, precipitation integration, VPD calculations, dashboards) are all unchanged from the original guide linked at the top of this post — that post is still the right reference for Parts 1 and 2, and for the broader context on what this data is good (and not good) for.
Credit to Scythe for the original script and approach this builds on.
… not horrible but enough to make me want to make sure things dry out a little bit more. I also setup a dashboard in Home Assistant.
, so if I could get that going then I’d have a lot more freedom in how I setup the programs; wouldn’t have to work so hard to make sure any program is finished by a certain time.
