Nothing surfaces a back scan whose front never arrived #143

New issue

Closed

opened 2026-08-05 16:37:44 +00:00 by claude-bot · 1 comment

claude-bot commented

2026-08-05 16:37:44 +00:00

Severity: MEDIUM — a small, permanently invisible pile in a large archive

Found while building #105.

#105 introduces the orphan back: a _b scan whose front is not in the archive is
ingested and kept rather than rejected, because the bytes of an irreplaceable original
should not be discarded over a filename. It is a photo row with storage_key_front
NULL and storage_key_back set, and PhotoOut.has_front reports the state per row.

But there is no way to ask for the list. list_photos has no filter for it, so
"which backs never found a front" is answerable only by opening SQL against the
database. On a 5,000-scan archive assembled over months that is exactly the list
somebody needs — each entry is either a front that was never scanned, or a filename
that does not follow the convention closely enough to pair. Both are worth acting on,
and neither is visible.

The fold in #105 means an orphan back resolves itself the moment its front turns up in
any later batch, so this list is self-clearing for the ordinary case. What is left in it
after an import is the genuine exceptions — which is precisely what makes it worth
showing.

Scope

A filter on GET /api/photos for it. Note the shape it should take: the state is
derived (storage_key_front IS NULL), not a column, so this needs the same care as
every other listing predicate here — see below.
The browser's filter control, alongside the undated / rescan-requested / missing-page
flags it sits naturally with.
A count, so the reviewer knows whether the pile is four photographs or four hundred.

The index trap, stated up front so it is not rediscovered

This repo has hit "an index whose leading terms match a filter can be catastrophically
mis-chosen" four times (#88, #94, #106, #141). Migration 014 measured a non-partial
(collection_id, deleted_at, id) sending every listing from 0.06 ms to ~250 ms —
3,800x — with the answers still correct, so nothing above the query plan would report
it. If this filter gets an index it must be partial on the orphan-back predicate,
the way ix_photo_collection_deleted is, so a live listing cannot choose it. Measure
before and after and put the numbers in the migration, as 012, 014 and 015 do.

Also worth checking rather than assuming: orphan backs are rare, so the filter may not
need an index at all — the excluded view's numbers in 014 suggest a predicate matching
a few percent of rows is cheap to evaluate over an index that already carries the order.

References

#105 — where the orphan back is introduced
backend/app/repositories/photos.py — list_photos
backend/alembic/versions/014_photo_exclusion.py — the measured index trap

## Severity: MEDIUM — a small, permanently invisible pile in a large archive Found while building #105. #105 introduces the **orphan back**: a `_b` scan whose front is not in the archive is ingested and kept rather than rejected, because the bytes of an irreplaceable original should not be discarded over a filename. It is a `photo` row with `storage_key_front` NULL and `storage_key_back` set, and `PhotoOut.has_front` reports the state per row. **But there is no way to ask for the list.** `list_photos` has no filter for it, so "which backs never found a front" is answerable only by opening SQL against the database. On a 5,000-scan archive assembled over months that is exactly the list somebody needs — each entry is either a front that was never scanned, or a filename that does not follow the convention closely enough to pair. Both are worth acting on, and neither is visible. The fold in #105 means an orphan back resolves itself the moment its front turns up in any later batch, so this list is self-clearing for the ordinary case. What is left in it after an import is the genuine exceptions — which is precisely what makes it worth showing. ## Scope - A filter on `GET /api/photos` for it. Note the shape it should take: the state is derived (`storage_key_front IS NULL`), not a column, so this needs the same care as every other listing predicate here — see below. - The browser's filter control, alongside the undated / rescan-requested / missing-page flags it sits naturally with. - A count, so the reviewer knows whether the pile is four photographs or four hundred. ## The index trap, stated up front so it is not rediscovered This repo has hit "an index whose leading terms match a filter can be catastrophically mis-chosen" four times (#88, #94, #106, #141). Migration 014 measured a non-partial `(collection_id, deleted_at, id)` sending **every** listing from 0.06 ms to ~250 ms — 3,800x — with the answers still correct, so nothing above the query plan would report it. If this filter gets an index it must be **partial** on the orphan-back predicate, the way `ix_photo_collection_deleted` is, so a live listing cannot choose it. Measure before and after and put the numbers in the migration, as 012, 014 and 015 do. Also worth checking rather than assuming: orphan backs are rare, so the filter may not need an index at all — the excluded view's numbers in 014 suggest a predicate matching a few percent of rows is cheap to evaluate over an index that already carries the order. ## References - #105 — where the orphan back is introduced - `backend/app/repositories/photos.py` — `list_photos` - `backend/alembic/versions/014_photo_exclusion.py` — the measured index trap

claude-bot referenced this issue

2026-08-05 17:26:50 +00:00

Bulk ingest, front/back pairing, and album linkage #105

rbrooks referenced this issue from a commit

2026-08-05 21:57:25 +00:00

Let the people doing the scanning add photographs themselves

rbrooks closed this issue

2026-08-05 21:57:25 +00:00

claude-bot commented

2026-08-05 21:58:01 +00:00

Author

Done in 50c3eb6.

back_only on GET /api/photos, and a "Back only" checkbox in the browser's flag filters — the same phrase the tile badge and the workspace have used since #105, because a filter for those rows under another name would read as a second concept.

The predicate asks both storage keys, not just storage_key_front IS NULL. A row with neither key is not a print waiting for its front, and this list exists to be worked through — every entry should be a front to re-scan or a filename to correct.

The index: I was wrong in this issue, and the measurement says so

This issue told whoever picked it up that an index "may not be needed at all — the excluded view's numbers in 014 suggest a predicate matching a few percent of rows is cheap to evaluate over an index that already carries the order."

That reasoning does not hold, and it fails in the opposite direction to the one I assumed. 014's analogy works for deleted_at because that column sits inside every partial index's WHERE clause. storage_key_front is in no index at all, so each candidate row costs a table fetch, and a page of 51 is only reached after walking far enough to find 51 matches. Rarity therefore makes the filter more expensive, not less — the cost peaks when the answer is empty, which is the steady state of a healthy archive.

Measured at 50,000 rows in one collection, 014's seeding proportions, best of 18:

shape	none seeded	0.18%	1.88%
back only, page 1	11.261 ms	6.325 ms	0.151 ms
back only, deep cursor page	6.321 ms	6.201 ms	0.202 ms
back only, date order	134.370 ms	77.020 ms	6.817 ms
back only, count	133.602 ms	124.864 ms	130.870 ms

The 014 catastrophe does not reproduce here, including with a plain index — worth recording, because this issue's warning implied it would. 014 needed the live listing to carry a term on the indexed column, so SQLite read deleted_at IS NULL as an equality constraint and preferred that index over the one carrying the sort. No listing carries a term on storage_key_front, so a plain index on it is simply never chosen.

Still no index, for a better reason. The other flag filters cost the same and have shipped since #94:

shape	none seeded	0.18%	1.88%
`rescan_requested`, page 1	12.062 ms	4.577 ms	0.244 ms
`missing_page_gap_after`, page 1	12.711 ms	11.625 ms	12.050 ms
`has_duplicate`, page 1	11.804 ms	11.075 ms	11.145 ms

Same plan, same cost, same absence of an index. Indexing only the newest member buys an eighth index on photo, paid on every ingest, against a cost the whole family already pays — and 11 ms for a view opened after an import sits inside what this repo already accepts (014 left the excluded queue in date order at 5.5 ms; 015 left an album read in album order at 15.4 ms). If the flag-filter family is ever worth indexing, that is one measured piece of work covering all four, not this issue.

The counterfactual is recorded rather than discarded: a partial index on the orphan-back predicate takes page one to 0.024 ms for 4–68 KB, and is pinned with its numbers in test_query_plans.py::TestTheBackOnlyFilterIsDeliberatelyUnindexed, following 015's precedent so a later reader does not mistake the absence for an oversight.

The count: left out, on the stop-condition

130.9 ms. There is no LIMIT to stop at, so it fetches every live row in the collection to read a column no index carries — on a dashboard that loads on every visit. Folding it into count_by_status is worse: that query is index-only at 6.3 ms today, and a SUM(CASE …) forces a row fetch and takes the whole thing to ~131 ms.

Two secondary reasons it is also the wrong shape: excluded is in PhotoStatsOut because excluded photographs leave the status counts, so the number explains a discrepancy — an orphan back is still counted under its status, so a back_only count explains nothing. And the only consumer would be the dashboard. The one-line path if it is ever wanted is the partial index above (0.027 ms) plus a count_back_only.

A hole in the safety net, found on the way

This issue's spec said the contract tests would notice a new query parameter. They did not — both passed unchanged, because nothing in either asserted that a browser filter reaches the published document at all. A parameter added to the repository and forgotten on the route would have passed both suites silently.

That is now TestTheListFiltersAreInTheContract, parametrized over the whole flag family, asserting each is declared, optional, and boolean | null — the tri-state being what a generated client gets wrong — with a companion pinning excluded as the deliberate two-state exception.

Thirteen mutations, all caught. One composition test survived the first run — its album held only the orphan back, so the album filter alone gave the right answer — and was rebuilt so neither half of the request can answer alone.

Done in 50c3eb6. `back_only` on `GET /api/photos`, and a "Back only" checkbox in the browser's flag filters — the same phrase the tile badge and the workspace have used since #105, because a filter for those rows under another name would read as a second concept. The predicate asks both storage keys, not just `storage_key_front IS NULL`. A row with neither key is not a print waiting for its front, and this list exists to be worked through — every entry should be a front to re-scan or a filename to correct. ## The index: I was wrong in this issue, and the measurement says so This issue told whoever picked it up that an index "may not be needed at all — the excluded view's numbers in 014 suggest a predicate matching a few percent of rows is cheap to evaluate over an index that already carries the order." **That reasoning does not hold, and it fails in the opposite direction to the one I assumed.** 014's analogy works for `deleted_at` because that column sits *inside* every partial index's `WHERE` clause. `storage_key_front` is in no index at all, so each candidate row costs a table fetch, and a page of 51 is only reached after walking far enough to find 51 matches. **Rarity therefore makes the filter more expensive, not less** — the cost peaks when the answer is empty, which is the steady state of a healthy archive. Measured at 50,000 rows in one collection, 014's seeding proportions, best of 18: | shape | none seeded | 0.18% | 1.88% | |---|---|---|---| | back only, page 1 | 11.261 ms | 6.325 ms | 0.151 ms | | back only, deep cursor page | 6.321 ms | 6.201 ms | 0.202 ms | | back only, date order | 134.370 ms | 77.020 ms | 6.817 ms | | back only, count | 133.602 ms | 124.864 ms | 130.870 ms | **The 014 catastrophe does not reproduce here, including with a plain index** — worth recording, because this issue's warning implied it would. 014 needed the *live* listing to carry a term on the indexed column, so SQLite read `deleted_at IS NULL` as an equality constraint and preferred that index over the one carrying the sort. No listing carries a term on `storage_key_front`, so a plain index on it is simply never chosen. **Still no index, for a better reason.** The other flag filters cost the same and have shipped since #94: | shape | none seeded | 0.18% | 1.88% | |---|---|---|---| | `rescan_requested`, page 1 | 12.062 ms | 4.577 ms | 0.244 ms | | `missing_page_gap_after`, page 1 | 12.711 ms | 11.625 ms | 12.050 ms | | `has_duplicate`, page 1 | 11.804 ms | 11.075 ms | 11.145 ms | Same plan, same cost, same absence of an index. Indexing only the newest member buys an eighth index on `photo`, paid on every ingest, against a cost the whole family already pays — and 11 ms for a view opened after an import sits inside what this repo already accepts (014 left the excluded queue in date order at 5.5 ms; 015 left an album read in album order at 15.4 ms). **If the flag-filter family is ever worth indexing, that is one measured piece of work covering all four, not this issue.** The counterfactual is recorded rather than discarded: a partial index on the orphan-back predicate takes page one to 0.024 ms for 4–68 KB, and is pinned with its numbers in `test_query_plans.py::TestTheBackOnlyFilterIsDeliberatelyUnindexed`, following 015's precedent so a later reader does not mistake the absence for an oversight. ## The count: left out, on the stop-condition **130.9 ms.** There is no `LIMIT` to stop at, so it fetches every live row in the collection to read a column no index carries — on a dashboard that loads on every visit. Folding it into `count_by_status` is worse: that query is index-only at 6.3 ms today, and a `SUM(CASE …)` forces a row fetch and takes the whole thing to ~131 ms. Two secondary reasons it is also the wrong shape: `excluded` is in `PhotoStatsOut` because excluded photographs *leave* the status counts, so the number explains a discrepancy — an orphan back is still counted under its status, so a `back_only` count explains nothing. And the only consumer would be the dashboard. The one-line path if it is ever wanted is the partial index above (0.027 ms) plus a `count_back_only`. ## A hole in the safety net, found on the way This issue's spec said the contract tests would notice a new query parameter. **They did not** — both passed unchanged, because nothing in either asserted that a browser filter reaches the published document at all. A parameter added to the repository and forgotten on the route would have passed both suites silently. That is now `TestTheListFiltersAreInTheContract`, parametrized over the whole flag family, asserting each is declared, optional, and `boolean | null` — the tri-state being what a generated client gets wrong — with a companion pinning `excluded` as the deliberate two-state exception. Thirteen mutations, all caught. One composition test survived the first run — its album held only the orphan back, so the album filter alone gave the right answer — and was rebuilt so neither half of the request can answer alone.