#768 steps 1+2: normalized image_prediction table (read cutover) #92

Merged

bvandeusen merged 4 commits from dev into main 2026-06-10 20:15:26 -04:00

Conversation 0 Commits 4 Files Changed 12

+337 -50

bvandeusen commented 2026-06-10 20:12:50 -04:00

Owner

Copy Link

Copy Source

Normalize tagger predictions out of the image_record.tagger_predictions JSON blob into a queryable image_prediction table (#768), through the read cutover. Verify-before-drop: this is steps 1+2 only — the tagger_predictions column is still present (vestigial, dual-written); the column DROP + dual-write removal + prune retirement is a held step-3 follow-up.

• image_prediction table (image_record_id, raw_name, category, score), migration 0045 creates it + backfills from the JSON via a single set-based INSERT…SELECT json_each, filtered to >= ml_settings.tagger_store_floor (0.70) so it's self-sufficient — does NOT need the #764 prune (which is superseded; it was too slow rewriting 100 GB in place and failing at its soft limit).
• Reads all moved to the table: SuggestionService.for_image/for_selection, apply_allowlist_tags, importer re-import reset. Parity by construction — each loads the same {name:{category,confidence}} shape, so downstream logic is byte-identical. Integration suite confirms.
• tag_and_embed dual-writes the table (transitional).

Deploy needs NO ml-worker quiesce (0045 takes no ACCESS EXCLUSIVE on image_record). DB stays ~100 GB until step 3 (column drop + VACUUM FULL).

CI green on dev 75eab18 (run 845).

🤖 Generated with Claude Code

Normalize tagger predictions out of the `image_record.tagger_predictions` JSON blob into a queryable `image_prediction` table (#768), through the read cutover. Verify-before-drop: this is steps 1+2 only — the `tagger_predictions` column is still present (vestigial, dual-written); the column DROP + dual-write removal + prune retirement is a held step-3 follow-up. - **`image_prediction`** table (`image_record_id, raw_name, category, score`), migration 0045 creates it + backfills from the JSON via a single set-based `INSERT…SELECT json_each`, filtered to `>= ml_settings.tagger_store_floor` (0.70) so it's self-sufficient — does NOT need the #764 prune (which is superseded; it was too slow rewriting 100 GB in place and failing at its soft limit). - **Reads** all moved to the table: `SuggestionService.for_image`/`for_selection`, `apply_allowlist_tags`, importer re-import reset. Parity by construction — each loads the same `{name:{category,confidence}}` shape, so downstream logic is byte-identical. Integration suite confirms. - `tag_and_embed` dual-writes the table (transitional). Deploy needs NO ml-worker quiesce (0045 takes no `ACCESS EXCLUSIVE` on image_record). DB stays ~100 GB until step 3 (column drop + VACUUM FULL). CI green on dev `75eab18` (run 845). 🤖 Generated with [Claude Code](https://claude.com/claude-code)

bvandeusen added 4 commits 2026-06-10 20:12:50 -04:00

feat(ml): image_prediction table + backfill + dual-write (#768 step 1) ... 79089b50b0

Normalize tagger predictions out of the image_record.tagger_predictions JSON
blob into a queryable per-prediction table. Step 1 of the cutover (expand):
additive + low-risk — reads still use the JSON, this just adds the table and
keeps it populated.

- ImagePrediction(image_record_id, raw_name, category, score) — stores the
  RAW tagger vocab name (not tag_id) so read-time alias→canonical resolution
  is unchanged. Indexed for per-image reads + by (raw_name, score).
- Migration 0045: create table + set-based backfill from the JSON via
  json_each (fast post-#764-prune). The old column stays (vestigial) and is
  dropped in a later follow-up — DROP needs an ACCESS EXCLUSIVE lock on the
  hot image_record table, so it waits for a quiesced-worker window.
- tag_and_embed dual-writes the rows (delete-then-insert, idempotent);
  tagger_store_floor already applied in infer().

Next: switch suggestion + allowlist reads to the table, then drop the JSON
write. Plan-task #768.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

feat(ml): read suggestions + allowlist from image_prediction (#768 step 2) ... 22cdf0f334

Switch every prediction READER off the JSON column onto the normalized
image_prediction table. Parity by construction: each reader loads the same
{raw_name: {category, confidence}} dict it consumed before (via small
_load_predictions helpers), so all downstream threshold/alias/merge/consensus
logic is byte-identical — only the data source changed.

- suggestions.SuggestionService.for_image (and for_selection via it)
- ml.apply_allowlist_tags (iterates images that have prediction rows)
- importer re-import reset deletes the image's prediction rows
The tagger_predictions JSON column is still dual-written (step 1) so it stays
valid during transition; the backfill task's NULL check still works. Removing
the JSON write + DROP column + retiring the #764 prune is the cleanup
follow-up (needs a quiesced-worker window for the DROP lock).

Tests: shared tests/_prediction_helpers.seed_predictions seeds the table;
read-path tests (suggestions, bulk consensus, allowlist apply, API) seed there
instead of ImageRecord.tagger_predictions.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

style: group tests._prediction_helpers import with backend (ruff I001) ... 0319812b45

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

fix(migration): 0045 backfill filters to >= store floor (supersedes #764 prune) ... 75eab188c8

The #764 in-place prune (rewrite tagger_predictions to >=0.70) is too slow on
100 GB of TOAST and fails at its soft limit (interrupts a query mid-flight ->
'another command is already in progress'). #768 supersedes it: extract only
the >=floor predictions into image_prediction via this set-based backfill,
then drop the column (step 3) — reading 100 GB once + writing ~840k small rows
beats rewriting 100 GB in place.

So this backfill no longer assumes the prune ran: it filters by
ml_settings.tagger_store_floor (default 0.70) itself, handling the full or
partially-pruned JSON identically.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

bvandeusen merged commit e75427b19a into main 2026-06-10 20:15:26 -04:00

Sign in to join this conversation.

Reviewers

No Reviewers

Labels

No items

No Label

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

bvandeusen

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: bvandeusen/FabledCurator#92