Wildlife Monitor

Status: working  ·  hardware-software  ·  View on GitHub →

#wildlife #ai #computer-vision #nas #raspberry-pi

What It Does

Automated wildlife detection pipeline for security camera and camera trap footage. Videos are pulled from a network-attached storage (NAS), run through AI detection (MegaDetector) and species identification (SpeciesNet), and browsed through a local web dashboard. Footage with detections gets archived in an organised structure; blank footage is purged on a configurable schedule.

Supports dual-lens cameras (fixed wide + telephoto) with synchronised playback and automatic lens pairing.

Hardware

Component Notes
Linux machine (Ubuntu 20.04+) Runs the detection pipeline and dashboard
Network-attached storage (NAS) Accessible via NFS or SMB — stores raw and archived footage
Security cameras / camera traps Any that write video to the NAS
GPU (optional) NVIDIA CUDA 11/12 — ~10x faster than CPU

How It Works

  1. nas_sync.sh pulls recent video from the NAS to local staging
  2. MegaDetector V6 scans each frame for animals, people, and vehicles
  3. SpeciesNet identifies species (2,000+ species, 65M training images) with state/province-level geo-filtering to cut impossible IDs
  4. Each animal crop is scored for image quality (sharpness, brightness, contrast)
  5. Kept footage is archived back to the NAS under camera/year/month/day/
  6. Blank footage is purged on a configurable retention schedule
  7. Web dashboard (FastAPI) lets you browse species, gallery crops, videos, and activity trends

Runs automatically at 6 AM daily via systemd. Dashboard starts on boot.

Installation

See GitHub repo for full instructions.

Quick start:

chmod +x setup.sh nas_connect.sh nas_sync.sh
./setup.sh
./nas_connect.sh   # interactive NAS setup wizard
./nas_sync.sh --then-process --country US --admin1-region US-UT

Build Log


2026-10-09 — Phase 18: the irreversible half, and the one row nobody could re-apply

Phase 17 made the code stop depending on three columns of species and the old video_corrections table. Phase 18 is the part that cannot be reverted with git revert: removing them from the production database. The whole phase is shaped by one question, which is how to make a one-way write boring. The answer was a standalone script, scripts/drop_legacy_corrections.py, that does nothing unless you pass it two flags.

It is not a line in init_db(), on purpose. init_db() runs unreviewed on every start of the web app, the processor and three snippets in nas_sync.sh, so a drop living there would fire on the next restart without anyone looking at it, and on SQLite older than 3.35 it would have crashed startup, because DROP COLUMN does not exist there. The script defaults to a dry run, requires --apply --confirm-irreversible, takes an absolute database path, refuses below SQLite 3.35, and takes a Connection.backup() snapshot that it integrity-checks before it touches anything.

ALTER TABLE ... DROP COLUMN was enough, with no twelve-step table rebuild. None of the three columns was a primary key, unique, indexed, in a CHECK, in a foreign key, or in a trigger or view (production has no triggers and no views), and nothing references video_corrections, so the table and its two indexes just go. Reading the SQLite rules is not the same as knowing, so the first thing I did was run exactly that sequence on a byte-copy on the box.

That copy is also where I found the trap. Python’s sqlite3 does not open a transaction before DDL. I ran ALTER TABLE species DROP COLUMN user_common_name, called rollback(), and the column stayed gone. A plain try/except around four DDL statements would have left the schema half-dropped and said it had rolled back. So the script opens BEGIN IMMEDIATE first, and everything that could fail runs inside that transaction before the commit: the foreign-key check, the integrity check, the column list, the per-table counts and content digests of every surviving table. Any exception rolls the whole set back through get_conn(). The harness has an injected failure at the fourth statement, after three columns were already dropped, and another in the in-transaction checks. Both exit 1 with ROLLED BACK, and the copy comes out byte-for-byte the same schema, counts and digests. Removing the BEGIN IMMEDIATE turns the matching case red, which is how I know the case can fail.

Two things about the box surprised me. The sudoers file lets the twostar user restart the dashboard unit but not stop it, so the privileged stop had to be the operator’s own, typed in their own session at a blocking checkpoint, with me confirming on the box that the unit was inactive, its main PID 0 and port 8080 refusing connections, before writing anything. And “is a run in progress” has two holes. A oneshot service reports activating, never active, while it runs, and a dashboard-triggered run lives inside the web service rather than the analysis unit. The mutual exclusion that actually covers both is the nas_sync.lock flock, so the script holds it for the whole apply and treats activating, deactivating and reloading as running too. The rehearsal proved bash flock -n and Python fcntl contend on the same file: with a real flock holder the real CLI refused and wrote nothing.

The rehearsal ran at production volume, on two backup copies of the real 94,523,392-byte file, SQLite 3.45.1 and Python 3.12.3. The backups took about 2 seconds each. The real CLI on a copy took 3.12 s for the snapshot, 7.70 s for the drop and 2.29 s for the in-transaction checks. Integrity was ok, the foreign-key check returned nothing, every surviving count and digest matched the source, and every GET route answered below 500 with the same output on the dropped copy and the undropped one. A dry-run on the live file, read through mode=ro, printed all six objects present and the 11 legacy rows. The operator said go at 11:46 on 2026-10-08.

The apply was at 12:05, after the operator stopped the dashboard, with the nightly timer 64,443 seconds away and no sync process or lock holder. It exited 0 in a few seconds: snapshot 2.87 s, drop 4.68 s, checks 2.30 s. The snapshot is data/migrations/legacy-drop-snapshot-20261008T120558830050.db, 94,523,392 bytes. Row counts were identical before and after (blacklist 18, crops 22,407, detections 57,872, runs 79, species 23,443, species_corrections 20, videos 55,261) and only video_corrections went from 11 rows to gone. The file did not shrink: it ended with 23,077 pages of which 6,786 are free, about 29 percent. I did not VACUUM, which needs roughly twice the file size free and exclusive access and would buy nothing but a smaller file. The dashboard came back up on a new process about 8 seconds after the restart at 12:06:49, init_db() re-added nothing, the journal held a startup banner and nothing else, the GET-only live suite passed 5 of 5 with an empty diff against its pre-drop baseline, and the operator walked the tabs and approved.

The orphan row. One of the 11 legacy rows was a real puzzle. Row id 5 is for video 31680, original label dce8d520-a3f6-4ed7-a434-bfe98f81a03d;mammalia;carnivora;hyaenidae;crocuta;crocuta;spotted hyaena, with corrected_label, corrected_common and corrected_scientific all NULL, corrected_at 2026-06-15T09:36:35.907087 and an empty note. It matched no detection and has no row in species_corrections. Ten of the eleven legacy rows already had a counterpart in the new table, and ids 3, 4 and 6 are also NULL-valued but are carried over, so only id 5 stood alone. I checked the premise on the live database: video 31680’s three detections today are a blank and two “Unknown species”, with no correction on any of them. There is no corrected value to re-apply, so there was nothing to decide and nothing worth an export file. The row went with the table and is preserved only inside the snapshot, which I keep indefinitely and do not prune. This entry is the only human-readable copy. That closes the loose end Phase 14 left in D-07.

The first nightly on the dropped database. The 06:00 timer fired at 06:00:17 on 2026-10-09 and exited at 06:09:20 with Result=success and status 0. It is run 80: success, 11 videos, 24 detections, from 06:01:34 to 06:08:47. So this was a real night rather than an empty one, and init_db(), the flagged-reprocess step and the cleanup all ran against a database without the legacy objects. The 301 cron.log lines since the restart held no traceback, no OperationalError and no missing table or column, and the totals moved by exactly what the run added (55,261 to 55,272 videos, 57,872 to 57,896 detections). A dry-run of the drop script on the live file afterwards printed NOTHING TO DROP - none of the six legacy objects exist, integrity and foreign keys were clean, the live suite passed 5 of 5 again, and the snapshot still passed its own integrity check and still holds row 5. That is the Phase 17 SCHEMA/init_db() decoupling holding in production, after a real run.

What I did not get to see. The other open question from Phase 14 is whether a reprocessed video’s detections start uncorrected, since reprocessing deletes their species_corrections rows by design. I did not wait for it and I did not manufacture it. No video was reprocessed in this window and the reprocess queue is empty. The one flagged video, 31703, has a stale flag and no file on disk, so it will never be picked up. Setting a flag or requeueing a species to force a reprocess on live data just to get a log line would be a production write with no purpose other than the log line, so the observation is carried as a standing item with no deadline, the same as the zero-detection alert.

The script stays. The Phase 17 one-shots were retired, because nothing could usefully run them again. This one is different, so scripts/drop_legacy_corrections.py stays in the repo as a documented, idempotent one-shot. On a database that is already dropped its dry-run is a read-only “still dropped” probe, and the code is the exact record of what was removed. Its audit file, legacy-drop-audit.jsonl, holds counts and timings only, never row contents. With the code decoupled in Phase 17 and the data dropped in Phase 18, the legacy correction machinery is gone from both.

2026-10-07 — Phase 17: pulling the app off a schema it was about to lose

Phase 14 froze three columns on species and the old video-player correction table, and Phase 16 finished moving every reader onto species_corrections. Nothing reads the frozen data any more, but the application still names it in a lot of places, and Phase 18 wants to drop it from production. Dropping a schema object under code that still mentions it is how you get a 500 at 06:00, so the removal is split in two on purpose. This phase is the reversible half: the code stops depending on the legacy objects, and production is not touched at all. The irreversible drop is Phase 18, and it may only start once this code has served production and survived a real nightly run (D-04). If the decoupling is wrong, the cost is a git revert; if the drop is wrong, the cost is the data.

The scary number was 279 grep hits. Reading them collapsed it to three runtime paths. Video detail (get_video_by_id(), and through it /media/video/{id}, which is easy to forget) read the old table to build a corrections list. init_db() re-created the columns, the table and both indexes on every start through SCHEMA and a migration branch. And the reprocess UPDATE in wildlife_processor.py nulled the three columns by name. Everything else was dead SQL constants, comments describing a state that no longer exists, the live API field user_common_name, and one-shot scripts. A blanket grep-and-delete would have been wrong in both directions: user_common_name is still the request-body field of the Gallery correction, and corrected_at is also a column of species_corrections.

The path I was most afraid of was init_db(). It runs on every start of the web app, the processor and three inline snippets in nas_sync.sh, so a Phase 18 drop without this phase would have been quietly undone at the next restart, columns and table back, empty. Worse, an orphaned CREATE INDEX ... ON video_corrections left in SCHEMA would have stopped the app from starting at all on a database without the table, so the table and its two indexes had to leave SCHEMA in a single edit. I reproduced both failures against a fixture before changing anything: get_video_by_id() died with no such table, and a second init_db() put all four objects back.

The reprocess loop was the sneakiest. It sits under if __name__ == "__main__": behind import cv2 and the model imports, so no harness had ever executed it; the only test of it read its source as text. That is exactly the kind of code that breaks the first night a video is flagged after the drop. Moving its two statements into a database.rewrite_species_for_reprocess() helper made the real SQL runnable against a database that physically lacks the columns. The decoupled suite also runs the real wildlife_processor.py --reprocess-flagged in a child process with the ML modules stubbed (and cv2 stubbed where it is absent), on both database shapes, and checks the species rows, the cleared corrections and the cleared flag.

The proof design is one seed and two shapes. The legacy-free shape is a fresh init_db() of the decoupled code, which is what Phase 18 leaves behind. The production-like shape is a Connection.backup() copy of it plus the legacy DDL with frozen values seeded, so “ignored” is proven instead of assumed: every reader and the HTTP layer must return identical output on both, and no frozen name may show up anywhere. Reasoning from a grep can only say the code does not mention the columns; building the absent shape says it works without them. On top of that sits a static guard, with an allowlist for the three regions that legitimately carry the live user_common_name field and a negative control that feeds the scanner every forbidden form and the look-alikes, because a guard that cannot fail proves nothing. It scans comments on purpose, which is what forced the stale-comment cleanup. The endpoint suite maps every GET route in the app to a URL and fails when a route exists that is not mapped, so a route added later cannot escape the proof. Run through the test client on both shapes, 27 GET routes answered with no 5xx and identical database-backed output (the 28th, the update check, would call PyPI and is skipped with a reason). The live field was exercised end to end over HTTP: a POST /api/species/correct lands in species_corrections, the Gallery shows the corrected name, the species row is untouched, and the video-player create, list and delete round-trip works.

The part I trust most is the rehearsal on the real file. On the production box the harness opens data/wildlife.db with a mode=ro URI, copies it twice with the backup API into a temp directory, and only ever writes to the copies. The source is 94,523,392 bytes, with 11 rows in the old table, 4 species rows carrying a non-blank frozen name and 20 species_corrections rows. On the first copy, which keeps the legacy objects, init_db() run twice changed nothing: no object added or dropped, identical legacy row counts. On the second copy three ALTER TABLE species DROP COLUMN statements and a DROP TABLE took about 8.6 to 8.9 seconds on SQLite 3.45.1 and Python 3.12.3, after which integrity and foreign-key checks were clean and init_db() re-added nothing. All the GET routes answered below 500 on both copies with identical JSON (the slowest was /api/stats at 1.8 s), and the read and write exerciser ran clean on both: gallery correction, video-player fan-out, delete, and the reprocess helper, with the legacy row counts on the undropped copy unchanged afterwards. A last case re-read the source’s schema and every table’s row count and found them identical, which is the D-03 promise that this phase writes nothing to production. The schema, decoupled, guard and endpoint suites also ran green on the box’s interpreter, along with the two older harnesses that need fastapi and httpx.

Retiring the two one-shot backfills was easy to decide and fiddly to execute. The Phase 9 dedup backfill and the Phase 14 species-corrections backfill did their jobs, and git history keeps them (D-01). Their harnesses went with them, and the Phase 10 audit suite went too, since it counted a frozen historical backlog from tables that will not exist. The harness cases that were not pure tests of those scripts were rewritten onto species_corrections, in the commit that would otherwise have broken them: the case-sensitivity check now goes through the live write path, the “gallery write leaves the species row alone” check compares the whole row before and after, and the stale-path snapshot looks at a table that exists on both shapes. The rule from the earlier phases held: rewrite, do not weaken.

One thing changes for anyone outside the dashboard. GET /api/videos/{id} no longer carries a corrections key, and get_video_corrections() and apply_corrections_to_species() are gone with it. The key served rows from the frozen table, 11 rows in the whole database, and nothing in the page, the processor, the notifier or the scripts reads it. An ad-hoc script of yours is the only thing that could notice. I chose to delete it rather than leave an empty list that looks documented but means nothing.

What has not happened yet is everything that touches production. The code is committed locally and nothing is pushed or deployed, and the running service is still the old code: pointed at it, the GET-only live suite fails exactly one case, the one that checks the corrections key is gone, which is the point of having it. Still ahead: shipping the code, the restart behind an operator approval stop, the live suite going green and a read-only smoke of every tab, and then the real gate, one nightly pipeline run completing cleanly on the decoupled code. Only after that can Phase 18 start. A nightly run may not even flag a video for reprocess, so the reprocess path is covered by the child-process run and the copy exerciser rather than by waiting for one.

Production soak (2026-10-08). The decoupled code went live on 2026-10-07 at 15:33:56 local, after a fast-forward pull and a rehearsal against a read-only byte-copy of the real database, and only once the operator answered “restart” at the approval stop D-03 requires. The GET-only smoke afterwards passed 5 of 5: every route answered, the counts were identical to the pre-restart snapshot (the empty diff is the “behaves as before” proof), the journal held no traceback or missing-table error, and video detail came back without the old corrections key. The one case that had failed against the old process, the one asserting that key is gone, turned green. The operator then walked every dashboard tab and approved it. The gate itself was the next scheduled nightly, which started at 06:00:14 on 2026-10-08 and exited 06:09:11 with Result=success and status 0. It is run 79: success, 9 videos, 24 detections, and the log lines written since the restart held no traceback, no OperationalError and no missing table or column. It was a light night rather than an empty one, so init_db(), the flagged-reprocess step and the cleanup all ran on the decoupled code with real work between them; no video was flagged for reprocess, so the reprocess path is still covered by the child-process run rather than by production. The totals moved by exactly what the run added (55,252 to 55,261 videos, 57,848 to 57,872 detections, matching the 24 the run reported), the live suite passed 5 of 5 again afterwards, and a read-only probe found all six legacy objects still in production with the same row counts as before the phase: 11 in the old table, 4 species rows with a frozen name, 20 corrections in species_corrections. Nothing dropped them, nothing re-added them, nothing wrote to them. That is the precondition Phase 18 needed, so it may now drop the columns and the table. This supersedes the “not yet” paragraph above.

2026-10-07 — Phase 16: one name, one bucket, and a pseudo-label that wanted to be a species

Phase 15 left a deliberate wart: a corrected “Northern Raccoon” bucket and a native SpeciesNet raccoon bucket were two buckets with the same display name, because a bucket was keyed on the raw taxonomy label for an uncorrected detection and on the corrected name for a corrected one. This phase makes them one. A species now appears exactly once on the Species tab, in both dropdowns, and in every chart series, whether SpeciesNet or the operator identified it. The three video readers that still matched the raw label learned about corrections too, and old bookmarks to a raw-label gallery filter keep working.

The first decision was the shape of the key, and it was really a choice between two designs. Design A would map each correction onto a taxonomy label so both halves of a bucket share a label. That needs a name-to-label table, and a correction carries no label at all: the Gallery popover only collects a common name, so the mapping would have been invented. Design B keys everything on the normalised common name instead: the corrected name for a corrected detection, the native common name for an uncorrected one, falling back to the raw label when the native name is blank so a nameless native stays its own bucket and a correction can never merge into it. The rule is “same displayed name means same bucket”, even if two different taxa ever share a name. That was a conscious choice to merge and report rather than to build a scientific-name component into the key.

Because the key shape flows into every dropdown value, filter predicate, drilldown path and chart series, the change was gated behind a read-only audit of a byte-copy of the production database, run before a line of the key changed. It reported 51 current keys becoming 47 through exactly four merges, each a native bucket meeting a correction-only bucket: Domestic Dog (187 detections on 173 videos), Mule Deer (606 on 502), Northern Raccoon (704 on 676) and Western Gray Squirrel (5 on 5). No collisions between native labels, no blank or odd names, no case variants. The video counts are distinct unions, not sums: 671 plus 8 raccoon videos became 676, not 679, because a video holding both a native and a corrected raccoon was counted once in each old bucket, so the harness asserts that the readers agree with each other rather than asserting arithmetic. The audit carries a predicted copy of the new key, and a harness case pins it equal to the one that shipped, so the audit cannot quietly drift from the code it is auditing. The slowest of eleven timed readers was 0.757 s against a 5 s budget on SQLite 3.45.1 and Python 3.12.3.

The trap in all of this was the “Unknown species” pseudo-label. SpeciesNet writes the label itself into the common name column for it, so a naive name key turns the pseudo-label into an ordinary lowercase bucket named “unknown species”, and the two stats queries that exclude the literal stop excluding it. Unknown would have leaked into the donut and the seven-day chart. The Phase 15 fixture hid this completely because it seeded the Unknown row with a NULL name, which is not the shape production writes. The guard lives inside the native key itself, so the key stays the literal and every existing exclusion keeps working unchanged. It also maps any native row whose name normalises to “unknown species” onto the same literal, which a label-based predicate would have missed: a non-Unknown label whose name part happens to be blank gets the pseudo-name too. The audit counted those on production and found none, so the broader guard costs nothing today and the fixture now seeds both stored shapes plus a non-Unknown label carrying the name.

Naming a merged bucket was the second place a shortcut would have bitten. Taking the MAX of the common name over a merged bucket is a lottery: it can pick a corrected member’s wrong AI name, so a Mule Deer bucket could be titled “Wild Boar” because the correction came from a boar detection. Reusing Phase 15’s most-recent-correction name would have been just as wrong in the other direction, re-titling a 600-detection native bucket in whatever lowercase the last correction was typed in and dropping its scientific name. So the display name is native-first: the most frequent native common name in the bucket, ties broken by name for determinism, with the scientific name taken from the same source and never mixed. A bucket with no native members, a correction to a species never detected, keeps the old most-recent-correction name. One documented approximation: the frequency counts every native row with the key, including ones hidden by the blacklist or corrected away. It only decides which casing variant wins a tie, and it is deterministic.

Old links were solved once, at the boundary, instead of per endpoint. A resolver turns an old raw label or any spelling of a name into the current key, and it runs at the three places a species value enters: the drilldown, the gallery filter and the video filter. Typing it into the URL is the only way to get a raw label there now, so it is a bookmark problem, and the resolver also rescued two Phase 7 cases that pass a raw label with no edit at all. On the browser side the drilldown already returned the resolved key as its label field, so the page repairs a stale #gallery?species= link with one request, selects the right dropdown option and shows the short display name in the chip, with no API change. It tries each value at most once and says nothing when a value cannot be resolved, so a stale link keeps its old behaviour instead of growing a modal. A smaller fix rode along: a video holding a native “Domestic Dog” and a corrected “domestic dog” came back as two spellings in one concatenated string and drew two chips for one bucket, so the chip builders now drop case-insensitive duplicates.

The three video readers are converted. A video counts as having a species only when it has a detection with a known effective species, so a video whose only detection was corrected away from Unknown now counts, and one whose only detections are suppressed, blacklisted, Unknown or named “Unknown species” does not. The two video searches match the effective name, so a corrected detection is found by its corrected name and no longer by its old AI name. Taxonomy search survives only for uncorrected rows: “canidae” still finds the uncorrected dog videos and the dog species bucket, and a suppressed detection is not found by its raw taxonomy either, which closes a leak the first version of the predicate would have had, since suppressed rows count as uncorrected. Some totals change by design and should not be reported as regressions: has-species video counts, the dashboard Recent detections list and search hits all drop videos that only ever held hidden or Unknown detections.

The harness discipline carried over from Phase 15. Every case that pinned the old separate buckets was rewritten in the commit that broke it, never deleted or weakened: ED4 flipped from two raccoon rows to one, LS2 now expects the merged bucket’s three detections, B6 and P10 in the Phase 12 harness moved to the merged counts, and the raw label constants were split from the new key constants so a test can no longer confuse a label with a key. A last unrelated one: the K6 case in the ops harness had been red since the v1.5 requirements rewrite removed the row it was reading, which was found by running every harness before starting rather than assuming a clean baseline, and it now reads the archived milestone file.

What has not happened yet: the post-deploy production run is with the operator. That means a fresh byte-copy audit in merged mode expecting 47 keys and no shared display names, both live suites against the restarted service, and the browser checks: one card per species with the corrected badge, one dropdown entry per bucket, one chart series per merged species, an old raw-label link landing on the merged bucket, corrected-name and “canidae” searches, and the usability of the roughly 47-entry list in the dropdowns and charts. Everything above was verified against fixtures, a local server and the harnesses, which prove the numbers and the source contracts but not what the page looks like.

2026-10-06 — Phase 15: the species list finally believes the corrections

Phase 12 made a corrected name visible everywhere it was drawn, and Phase 14 collapsed the two correction tables into one. Neither touched what the readers grouped on. The Species tab, the Stats tile, the Timeline and the species dropdowns still bucketed by the original SpeciesNet label, so a raccoon that a human had already fixed kept counting toward “Domestic Cat”, and a species that was never anywhere near these cameras stayed in the list until every last detection of it was gone. The Species tab staleness was accepted on purpose at the end of Phase 14. This phase closes it.

The first decision was what a bucket is keyed on. A taxonomy label was the obvious candidate, but a Gallery correction never carries one; the popover only collects a common name. So the key is the normalised corrected common name (trimmed, lower-cased) for a corrected detection, and the raw SpeciesNet label for an uncorrected one. It is defined once as a SQL constant and interpolated verbatim into every GROUP BY and WHERE, with no view, no materialised column and no merging in Python. The by-design consequence is that a corrected “Northern Raccoon” bucket and a native SpeciesNet raccoon bucket are two buckets with the same display name. Merging them would need a name-to-label mapping that does not exist yet, so it is recorded as a deferred idea rather than guessed at. The audit on production data found four display names shared by two or more keys.

The API field label stayed label, and now carries the key. That kept the dropdown value, the ?species= filter and the drilldown path in lockstep without changing the frontend contract at all: the option value was already the thing sent back to the server. The cost is that a gallery or video-player item still carries its raw label, because the correction popover and the write-time fan-out need it; only the aggregate readers repurpose the field.

Naming a bucket was the part that needed care. A bucket can hold detections corrected at different times, so the display name has to come from one deterministic place, the latest non-suppressed correction for the key. SQLite will happily return a bare column from an arbitrary member of a group, which is exactly the kind of non-determinism the original todo warned about. The tempting alternative, taking the MAX of a timestamp concatenated with the name, depends on the timestamp text sorting chronologically, and stamps written in more than one textual form do not. A window-ranked CTE joined once per key avoids both traps. A second trap sat in the same queries: a SELECT alias that shadows a column name changes what GROUP BY means, so the key expression is repeated rather than aliased.

One bug had been hiding in plain sight. The drilldown route has always returned 404 for an unknown species, but the underlying query was an aggregate, and an aggregate without GROUP BY always returns a row, so the 404 could never fire. A stale key opened a modal that spun forever. The reader now returns an empty info object for a bucket with no visible member, the existing 404 finally triggers, and the frontend shows “Species not found” with inert View-all buttons. That is also the cleanest production proof the new code is serving: the live service answers 404 for a key that no longer exists, where the old code answered 200.

Suppression is now global, which deliberately reverses something Phase 14 signed off: the Gallery used to keep showing a crop the video player hid. The reason is arithmetic. A filtered gallery total has to equal the Species-tab count for the same bucket, and a hidden-in-one-place crop makes that impossible. The blacklist got the matching rule: a correction with a real name overrides it, while a blank-name or scientific-only correction does not, so correcting something does not accidentally un-hide a blacklisted species. Only the species half of search() moved to the key. Three readers stay on the raw label by operator decision and are tracked in a todo: the has-species video filter, the Videos-tab text search, and the videos half of the global search. Their symptom is narrow: a video whose only detection was corrected away from Unknown still counts as “no species”, and text search still finds a corrected detection by its old AI name.

Two small things worth stating. Chart series are named by display name, so two same-named buckets share one timeline series; that is a display merge and no data is lost. And the species route is declared after a literal /api/species/search route, so a bucket whose key is literally “search” would be shadowed. A correction to the name “search” is the only way to get there, and that is accepted.

Because the change flips behaviour that older harnesses pinned, each legacy case was revised in the commit whose change broke it, never deleted, so every commit stayed green. The new harness asserts that every reader agrees with every other, and the same invariants run against real data.

The production run, before the service was restarted, was against a byte-copy of the live database taken with the SQLite backup API; the copy was deleted afterwards. All five harness families passed on the box’s own interpreter first. The audit found 51 effective keys, all hard cross-reader invariants held, and the slowest of eight timed readers was the stats call at 0.405 s against a 5 s budget. Diagnostics: no known-species rows on videos that are not kept, 226 detections whose crop count is not exactly one, five suppressed corrections, and one corrected detection whose raw label is blacklisted. No warnings. After the restart a live HTTP suite passed 5 of 5 (unique species equal to the list length, the stale key returning 404, the largest bucket’s detection and video counts matching its drilldown and video filter, and the served page carrying the new frontend). The Species tab now lists 51 buckets and the Domestic Cat bucket holds 22 detections.

What has not happened yet: the rendered browser checks are pending with the operator. The donut slice click, dropdown order and the filter chip, the Species-not-found modal, whether lowercase corrected headings are acceptable, the acknowledgement that suppressed crops are gone from the Gallery, the optional correction round-trip and the blacklist-override display are all unconfirmed by a person looking at the page. What was verified instead is backend and HTTP level: the live suite, the byte-copy audit, and a separate set of live API checks (counts, reader lockstep, timeline sums, suppression of one known crop and the stale-key 404). Those prove the numbers; they do not prove what the page looks like. A follow-up note will record the operator’s answers once they have looked.

2026-10-06 — Phase 14: two correction tables, one source of truth, and a migration nobody wrote down

For months a species correction could live in one of two places. The Gallery popover wrote to the species row itself (user_common_name), while the video player wrote a group-scoped row to video_corrections. The two were reconciled at read time, with a fixed rule that the video-player value always won. That worked until Phase 12 made the corrected name visible everywhere and it became obvious that every reader had to know about both mechanisms and agree on precedence.

The roadmap had a fork: a materialized effective_label column reconciled after the fact, or a real unification. The operator chose unification, a single species_corrections table keyed UNIQUE(detection_id) with an UPSERT. That moved precedence from read time (video always wins) to write time (most recent write wins). Because the old rule and the new one disagree exactly when a detection carries both kinds of correction, the new rule is pinned by tests in both write orders. Pinning only one order would have passed while hiding half the behaviour.

Two decisions are worth stating as tradeoffs. First, a video-level correction is now a snapshot fanned out over the detections that match when it is saved; a detection added later by a reprocess starts uncorrected. That is accepted, and the first real reprocess is the place to observe it. Second, the suppress sentinel got its own suppressed column. The proposed encoding, corrected_label IS NULL, would have meant two different things depending on whether a row came from the Gallery or the video player.

One number deserves a second look: the roadmap quoted “~17,298” as the size of the job. That is the total crops count, not the corrected count. The real production dry-run planned 15 rows: 4 from the Gallery path and 12 from fanning 11 video-level rows out over their detections, with one detection reachable from both sources. Nothing about the migration was large; the care went into making a 15-row write provably lossless, with a snapshot, a JSONL audit log, and a digest of the original-label columns compared before and after.

The production write ran on 2026-08-22: 15 rows written, a dashboard restart onto the cutover code, then a second pass that wrote zero. Both audit digests were identical before and after, so the original AI labels are untouched. The honest part of this entry is that the write was not documented at the time. This record was reconstructed on 2026-10-06 from the audit log, the snapshot files and journalctl, then re-checked against the live database: zero foreign-key violations, zero orphans, zero duplicates, and 17 rows, the 15 migrated plus 2 real corrections made through the new path on 2026-09-30.

The code review on the finished phase caught something the harnesses could not: --reprocess-flagged reuses each detection’s id and only cleared the old legacy correction columns, so a requeued video would have kept its corrections forever, and a suppressed crop would have stayed hidden even after SpeciesNet gave it a new label. Before the cutover a reprocess wiped a Gallery correction; the unified table had quietly made it permanent. The plan’s own comment claimed reprocessed detections start uncorrected, and nobody had read the reprocess loop to check. It now deletes the detection’s correction in the same loop, pinned by a harness case. The same review found that DELETE /api/corrections/{id} had moved to the new table while GET still listed the old one, so the two no longer shared an id space; nothing in the dashboard calls it, but it is fixed too.

Three loose ends came out of that re-check. One legacy row, a spotted hyaena correction on video 31680, matched no detections and was reported rather than dropped, most likely because Phase 9’s dedup consolidated that video. A test fixture dated 2026-08-16 failed once it aged past get_timeline()‘s 30-day default window, which is a reminder that fixed dates in fixtures are a clock. And until Phase 15 ships, the Species tab, the Stats tile and the Timeline still group by the original label, so a corrected name there lags on purpose.

2026-08-20 — Phase 13: a preview feature nobody could reach, and a text harness that already knew

Two small, unrelated fixes to the raw-cleanup subsystem this phase, both closing gaps left over from Phase 6. The interesting part wasn’t either fix — it was what got found while making them.

The first bug had a shape worth naming on its own: a preview feature that technically existed but was functionally unreachable. nas_sync.sh --dry-run looked, from the flag name, like exactly what an operator would want before trusting an automated deletion sweep — a look-before-you-leap check. But all of the raw-cleanup preview logic was nested inside the --then-process branch, so --dry-run used alone just synced videos and exited, having previewed nothing. The only way to actually see what a cleanup sweep would touch was to hand-set an undocumented environment variable, WM_RAW_CLEANUP_DRY_RUN, inside a full --then-process invocation — something no operator would discover without reading the source. A safety feature that only its author knows how to reach isn’t really a safety feature; it’s a checkbox that happens to compile. The fix pulls the preview into a standalone branch reachable from --dry-run alone, with no --then-process and no environment variable required. That’s also a deliberate behavior change, not a strict superset of the old behavior: --dry-run by itself used to sync-then-exit, and now it previews-then-exits instead. Preserving the old sync-then-exit behavior alongside the new preview was considered and rejected — a preview-only invocation gains nothing from copying gigabytes of video to local staging first, and keeping that dead weight around would have just meant two ways to do a no-op.

The more interesting find, though, was archaeological. Planning for this phase assumed nas_sync.sh had no automated coverage — it’s a bash script, and this project’s test story has always been Python-only stdlib harnesses. That assumption was wrong. scripts/verify_raw_cleanup.py has been parsing nas_sync.sh as text since Phase 6, encoding structural invariants across roughly twenty cases — not by running the script, but by asserting things about its literal source: that certain sentinel comments occur exactly once, that a code block’s extent runs from one divider string to another, that an exit guard is keyed on finding a particular variable name. Every one of those techniques has the same failure mode once you add a second, near-identical block of code to the file: silent mistargeting, not a loud error. A first-occurrence scan for a marker comment doesn’t know which occurrence it’s supposed to mean; it just returns the first one, confidently, whether or not that’s the block you actually inserted. Inserting a second preview-flavored copy of the raw-cleanup verification logic without accounting for this would have meant existing test cases quietly started asserting things about the wrong block — passing while checking nothing meaningful. The fix was to give the new branch its own distinct sentinel comments, so every text-scan in the harness has an unambiguous target, and to add one more case on top: a drift guard (PRV8) that diffs the duplicated verification functions against the canonical copy and fails if they ever diverge, since the two copies exist for placement reasons, not because they’re supposed to evolve independently. The general lesson: text-assertion harnesses are cheap, durable, and genuinely useful, but they fail by going quiet, not by getting loud — worth remembering the next time a “just add a similar block” change looks free.

One smaller trap, set -euo pipefail flavored: NAS_ARCHIVE_ROOT and NAS_BLANK_ROOT are only assigned inside the existing --then-process block, further down the script than where the new standalone preview branch needed to sit. The obvious insertion point — right after the mount check, before the --then-process block runs — would have hit an unbound-variable abort the moment the preview tried to read either path. The new branch derives both locally instead of depending on assignments from a block it may never reach.

The second fix, CLEANUP-05, was smaller in every sense: the Settings page’s raw-retention warning previously only checked raw retention against blank-video retention, and said nothing if raw retention outlived kept-video retention instead — a real gap, since a kept archive copy purged before its raw source was ever cross-verified would strand data with no warning at all. The design call worth recording is that this shipped as a second, independent warning stacked alongside the original one, rather than folding both conditions into a single merged message. Each message names the specific archive type actually at risk, and — more importantly — the original blank-retention warning survives byte-for-byte unchanged, which the harness pins directly as a no-regression guarantee rather than trusting a code review to notice a wording drift.

Deployed and verified live on production hardware against the real NAS and a five-figure raw_recordings directory: the preview correctly reported real skip reasons (no_raw, ok) with nothing copied and nothing deleted — the only file-count movement during the test window came from live camera motion events landing mid-check, confirmed by timestamp, not from the preview doing anything. Both retention warnings behaved exactly as designed across all five tested configurations, stacked correctly, and never blocked a save.

2026-08-16 — Phase 12: two species-correction write paths, one that never left the room

Closed out the last of the three open decision items from the v1.3 milestone’s “observability, UX, and monitoring” bucket, and the one that turned out to be a bigger job than the roadmap line implied.

The starting question was small: a gallery tile for a human-corrected species still shows the AI’s original confidence percentage, which is misleading once a human has overridden the label. Fixing the badge meant finding every place a corrected species gets displayed — and that’s where the actual gap surfaced. This codebase has had two independent species-correction write paths since Phase 3: the Gallery popover’s correct_species(), which writes species.user_common_name directly onto the detection row, and the video player’s per-crop editor, save_video_correction(), which writes a separate video_corrections row keyed by (video_id, original_label). Only one of them was ever readable outside the view that wrote it. The Gallery popover’s corrections propagate everywhere, because the shared DISPLAY_COMMON SQL expression reads user_common_name directly. The video player’s corrections were visible only inside that one video’s own detail page — forever, by construction, since nothing else queried video_corrections at all. Nobody noticed for months, because the two correction UIs look and behave identically from the operator’s chair. It took a Phase 10 UAT session, re-verifying an unrelated fix on a video that happened to carry an old video-player correction, for the mismatch to become visible.

The fix followed a pattern this project had already used once: Phase 10’s NOT_EFFECTIVELY_UNKNOWN made the unknown-species suppression filter correction-aware with a correlated subquery instead of a per-caller Python overlay. Display correctness got the same treatment here — a SQL expression that composes into every existing interpolation site — rather than bolting apply_corrections_to_species()-style post-processing onto six separate reader functions individually.

There was a boundary drawn on purpose, though, and it’s worth explaining why. Making the displayed name correction-aware is safe. Making the grouping correction-aware is not — not without a decision this project hasn’t made yet. If a species drilldown groups on the raw AI label but selects a corrected display name within that group, SQLite hands back an arbitrary member row’s value whenever a group is only partially corrected, which means a species tile could render a different name on every page load. That’s a worse failure than the stale-but-stable behavior it would replace. Fixing it for real means changing what a species drilldown, a filter dropdown, and a chart series are keyed on — not just what they display — and that’s a decision that needed to go to the operator rather than get smuggled into a display-layer bug fix.

The badge itself came down to a small but deliberate ordering choice: a stale AI confidence score attached to a label a human has since overridden is worse than no score at all, so the corrected state replaces the percentage rather than annotating it, and the corrected-check runs before the null-confidence guard — so a corrected crop that was never scored by SpeciesNet in the first place still shows the “corrected” signal instead of falling through to a blank badge.

The third item on this phase’s list, NOTIFY-01, closed out as an honest dead end rather than a fix. Five different failure-injection strategies were tried against a copy of the pipeline to simulate a corrupted-video partial-run failure, and OpenCV/ffmpeg absorbed every one of them as a benign “0 frames extracted” rather than the hard error the alert path is built to catch. The alert code stays in production, live and armed — it’s just permanently unverifiable by any means available, which is now written down as an accepted limitation instead of an item that looks perpetually half-done.

Production deployment surfaced nothing new: full harness suite green on the real runtime, timing deltas all within a few percent of baseline, and the operator’s browser walkthrough confirmed the badge, the propagation fix, and the one v1.2-era view that’s supposed to stay unchanged (the video detail page itself) all behaved as designed. The operator also flagged the grouping gap as worth a follow-up — and, digging into why two correction mechanisms exist in the first place, asked whether they should really be two mechanisms at all. Both questions are now filed as their own todo rather than re-discovered the next time a corrected species shows up somewhere it shouldn’t.

2026-08-16 — FIX-02: 14,354 stale file-path references repaired in production

Closed out a data-quality gap that had been sitting quietly in the database since before this project started tracking its own history: about 22% of crops.crop_path and videos.thumbnail_path references — 14,354 rows out of roughly 65,000 — pointed at a /home/nash/... directory prefix that hadn’t existed on disk in years. The box’s home directory was renamed to /home/twostar/ at some point during this project’s early life, and whatever did that rename never touched the database rows that stored full paths under the old name. Nobody had noticed, because nothing had gone looking. It surfaced by accident, mid-rehearsal, during Phase 9’s dedup backfill audit — a completely unrelated operation that happened to read every crop and thumbnail path on its way through. Confirmed unrelated to and unaffected by that backfill before either was allowed near production.

The actual fix is a one-line idea: replace a stale string prefix with the current one, on two columns, no row deletion, no schema change. That idea got four plans and its own phase anyway, because this project’s standing rule is that every production write earns a harness first, full stop — dry-run-first, snapshot-before-write, an operator go/no-go checkpoint, and post-write reconciliation against a baseline captured before the transaction opened. The cost of that discipline on a change this small was low: one script, a 37-case fixture harness, a rehearsal against a live copy of the data. The cost of skipping it once, on the day the “obvious” one-liner turns out not to be so obvious, is unbounded. That asymmetry is the whole argument.

And it did turn out not to be quite so obvious. The first-instinct implementation — a SQL UPDATE ... WHERE crop_path LIKE '/home/nash/%' doing a REPLACE on the matched rows — is subtly wrong. The LIKE clause controls which rows get touched, but SQL’s substring replace doesn’t know or care where in the string the substitution lands. A path containing /home/nash/ a second time later in its own value — plausible if a filename or a nested directory ever echoed the old username — would get corrupted in the tail, not just repaired at the head. The fix that avoids the failure mode entirely: compute the new value in Python as a leading-prefix slice, so only the first, known-position occurrence is ever touched, structurally. The fixture harness carries a dedicated case asserting exactly this — a path with the stale string appearing twice, confirmed rewritten only at the front.

The roadmap’s Success Criterion 3 — “a sample of previously-broken image URLs resolves correctly in the dashboard after migration” — assumed the stale paths meant broken images in the browser. Reading web_app.py‘s serving code before writing anything showed that assumption was shaky: the dashboard’s media routes reduce every stored path to its basename before serving the file, so the directory prefix never actually reached them. The pre-migration baseline measurement confirmed it empirically — all five sampled dashboard URLs (3 crops, 2 thumbnails) already returned HTTP 200 before the migration ran. The dashboard was never broken by this bug. The real damage was elsewhere: the full-path consumers, wildlife_processor.py and backfill_dedup_videos.py, which do use the complete stored path and would have failed to find any of these 14,354 files on disk. Reporting the criterion as “passed, but for a boring reason — no regression, not a visible repair” felt more honest than quietly checking the box.

The production write itself, once rehearsed, was exactly as uneventful as the plan wanted it to be: 14,354 rows rewritten (4,274 crops, 10,080 thumbnails) in a single invocation, no service stopped — this is a two-column string UPDATE, not a DELETE-based consolidation, so there was never a window where a row briefly didn’t exist. Post-write, every figure reconciled exactly against the pre-write baseline: row counts unchanged (17,298 crops, 48,464 videos), both non-path-column fingerprints byte-identical before and after, zero foreign-key violations, and all 14,354 rewritten paths individually confirmed to resolve against the live filesystem — not sampled, checked. A snapshot backup was taken before the transaction opened regardless, cheap insurance on a 94MB database, matching this project’s practice for every prior production write.

2026-08-10 — Historical dedup backfill: 19,289 duplicate video rows consolidated in production

Closed out the last and highest-risk phase of the v1.2 milestone: a one-time data-surgery script to clean up ~19,291 duplicate videos rows left behind by an old archive-collision bug (fixed at the source back in Phase 5, but the historical mess was explicitly deferred until now). Deliberately sequenced last — irreversible production writes only happen after everything else has stabilized.

The plan itself was built in layers on purpose: a read-only audit against real production data first (before any delete logic existed), then a tracer proving one full consolidation path end-to-end on a fixture, then widening that tracer to handle every real shape the audit found — operator corrections, empty winners, a hazard where crops silently migrate to the wrong sibling via INSERT OR REPLACE, dual-lens pairings. Two hazards surfaced just from reading the existing code before any of this ran: nearly every duplicate group (98.7%, it turned out) shares one identical thumbnail file with no uniquifier, so a naive delete would blank the surviving row’s gallery tile; and crops could end up orphaned on the wrong sibling entirely. Both got dedicated reference-guarded handling instead of being discovered the hard way in production.

The fixture harness — 53 cases across 10 suites, all synthetic — passed clean the whole way through. It proved the logic was right. It said nothing about whether the logic was fast enough, and that gap showed up hard during the full-scale rehearsal against a real production copy: a first dry-run attempt was killed after 33+ minutes of runaway CPU, root-caused to an N+1 query pattern invisible at fixture scale (six rows) but catastrophic at real scale (19,291 groups, ~64,000 rows). Fixed with a bulk-prefetch cache. Then the same shape of bug reappeared in the actual write path — an unindexed DELETE ... WHERE detection_id IN (subquery) — fixed by deleting on primary key instead. And then a third layer: even with the query-level fixes, SQLite’s own foreign-key enforcement was still doing full-table scans on every delete, because three FK columns (two on crops/species, one a self-referencing paired_video_id on videos) had never been indexed. That one required stepping outside the phase’s own files into the shared database.py schema — flagged for explicit sign-off rather than treated as a same-file auto-fix, given the blast radius. Three schema-level fixes later, the full 19,291-group pass went from a projected ~2.3 hours down to 42 seconds.

The rehearsal earned its keep: none of those three bugs were reachable from a fixture database small enough to write by hand. Real data volume is a different animal, and “the tests pass” turned out to be a necessary but insufficient claim for anything touching the live database.

The actual production run was almost anticlimactic by comparison — 41 seconds, services stopped for a few minutes total, every post-run check clean (zero FK violations, zero broken pairings, deltas reconciling exactly). 19,289 of 19,291 groups consolidated; the remaining 2 were left alone on purpose, reported by name rather than force-resolved, because both plausible automatic answers would have either destroyed a video’s only crops or duplicated its detections. Quarantine directory ended up empty — turns out almost nothing was actually safe to delete once the shared-thumbnail guard was accounted for, which says something about how deep that particular bug’s fingerprints ran through the data.

2026-05-07 — Project initialized and milestone planned

Started using a proper planning workflow for the first time on this project, which has been running and evolving organically for a while now. The codebase reached a point where it works well enough to trust the output but has enough rough edges that fixing one thing breaks another — the classic “it works on my machine, in this exact order” problem.

The planning pass surfaced six silent failure modes that have been lurking in the code. The most insidious: a NameError: log in database.py that crashes any startup against a database that needs a filepath migration. It never showed up during normal operation — only when something changed in the environment. Similar story with SETTINGS_FILE using a global path instead of honoring --data-dir, meaning settings saved in the dashboard silently went to the wrong place when the service was running with a custom data directory.

The dual-lens sync situation turned out to be three separate bugs stacked on each other: a SQL LIKE wildcard matching _ as any character (so camera_01 could pair with camera_X01), a JavaScript isSyncing guard that resets synchronously and immediately lets the next timeupdate event fire (feedback loop), and no database migration to repair the pairs that got wrongly matched by the SQL bug. All three need to be fixed in the right order.

Planning broke into four phases: fix the six blocking bugs first (Phase 1), then run monitoring + email alerts (Phase 2), scheduling UI + gallery UX improvements (Phase 3), and the dual-lens overhaul (Phase 4). Phases 2–4 can run in parallel once Phase 1 clears. The constraint on dependencies is worth noting: the dual-lens database fix must land before the JavaScript player rewrite, and Phase 1 must land before anything else, because until those six bugs are fixed the codebase isn’t safe to extend.

Key architectural constraint I locked in early: no new pip dependencies. The project already has a heavy ML stack (MegaDetector + SpeciesNet), and adding smtplib-style packages that are already in stdlib, or <dialog> elements that are now baseline browser-native, keeps the installation story clean. Everything new goes into Python stdlib or browser APIs.

Total scope: 26 requirements across the four phases.

Scroll to Top