The thing that clicked today: rules are confident and wrong all the time, and looking at the actual pictures is what keeps you honest. Now that every file's metadata is in a database, the next phase is generating candidates for deletion, never deleting anything, just surfacing "you probably don't want these" by rule. The photo library itself stays the source of truth; this phase only writes CSV lists and a summary. That read-only discipline turned out to matter, because several of the rules were wrong in instructive ways.
Built / shipped
A set of documented candidate rules, split into a high-confidence tier and a human-review tier: exact duplicates, orphaned short clips, screenshots, burst extras, blurry shots, and obvious junk. Each rule is a database query that writes out a CSV; nothing changes the database except one approved write (below). A sampler pulls a reproducible random 40 rows per rule and renders them as a grid of thumbnails in an HTML page, so I could actually look at what each rule was flagging instead of trusting the count.
Problems & fixes
Exact-duplicate detection found zero duplicates. The content hash is real (I checked it against the system's own hashing tool on an actual file), but there are no byte-identical files in this library. The foo (1).jpg twins everyone has are re-saved copies, not exact copies, so they hash differently. Exact-hash dedup is useless here; the real follow-up is matching on what the image looks like.
A rule that looked empty was actually blind. The video-duration column was empty for every row, and I had not known that in SQL a comparison against an empty value is neither true nor false, so every video silently dropped out of any duration-based rule. The rule didn't error. It just returned nothing, which reads as "no matches" when it really means "no data." I ran a backfill of duration for all ~35,000 videos by reading each file's header (resumable, batched, one column updated; three corrupt files refused and always will). I now check that a column is actually populated before I trust a rule's count.
The blur detector flags the wrong things. The score it uses measures how much fine detail is in the frame, so a clean shot of a flat sign or a gray sky scores as "blurry" because there is nothing sharp to measure. A single global threshold also collides with precious old soft-focus photos. It needs a texture guard and per-era tuning, not one magic number, so the threshold is now chosen from the actual distribution of scores rather than hardcoded.
The single biggest flagged bucket (over 5,000 items, hundreds of gigabytes) came through as "shared album." I only caught what it really was by rendering the thumbnails and looking: it's all my own footage from a wearable camera, tagged with a naming pattern my rule had misread. It must never be a delete candidate. The "shared album" flag didn't mean what I assumed it meant; it had been set entirely by that one import.
Decisions
- This phase generates candidates only. Deletion is a separate, much later, deliberate step. The only database write all day was the video-duration backfill.
- Long videos shot at either of two home locations are treated as work product and excluded from culling entirely: protected, not candidates.
- Tagging stays in CSVs until the decision schema is designed properly; no half-baked "to_delete" column bolted onto the main table.
Learned
- A content hash finds byte-identical files. It does nothing for near-duplicates; that needs a hash of what the picture looks like, not of its bytes.
- An empty column makes a rule look empty. A rule can return nothing because it is starved of data, not because nothing matches. I check population first now.
- A blur score cannot tell "out of focus" from "not much texture," and a global threshold punishes rare, precious soft shots. Look at the pixels.
Still open / next
The perceptual near-dup pass, a texture-aware blur guard, smarter burst handling that scales the keep-count to the cluster size, and designing the decision schema before any tagging touches the database.