← back to Lifestyle Asset Intel
yolo tick #4: image_assets + image_embeddings (prep for v0.3 CLIP intake)
660aa14a1cc574d76a6f2d9cf249402426f5b9b4 · 2026-05-09 23:56:34 -0700 · Steve Abrams
Lay in the schema for the image-intake pipeline BLUEPRINT.md calls for
in v0.3 (image-first valuation flow):
image_assets:
Provenance-rich row per image. Tracks canonical_asset linkage,
origin (upload/auction_lot/marketplace/crawl/reference), source_url,
storage_url, sha256 for content-addressed dedupe, mime/width/height,
and an attribution string per Steve's standing public-domain-only
rule. UNIQUE (sha256) lets the connector idempotently store images
without re-uploading the same bytes.
image_embeddings:
One row per (image, model) pair so we can run multiple embedders
side-by-side (CLIP ViT-B/32 baseline + SigLIP fine re-rank, etc.).
Stored as real[] with explicit dim column — PG-native, no extension
required. When pgvector lands locally the migration to vector(dim) +
HNSW is a 2-line ALTER.
No data seeded; v0.3 connector lands in a future tick.
29/29 tests still green. 3/3 migrations recorded.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Files touched
A db/migrations/0003_image_intake.sqlM db/schema.sql
Diff
commit 660aa14a1cc574d76a6f2d9cf249402426f5b9b4
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sat May 9 23:56:34 2026 -0700
yolo tick #4: image_assets + image_embeddings (prep for v0.3 CLIP intake)
Lay in the schema for the image-intake pipeline BLUEPRINT.md calls for
in v0.3 (image-first valuation flow):
image_assets:
Provenance-rich row per image. Tracks canonical_asset linkage,
origin (upload/auction_lot/marketplace/crawl/reference), source_url,
storage_url, sha256 for content-addressed dedupe, mime/width/height,
and an attribution string per Steve's standing public-domain-only
rule. UNIQUE (sha256) lets the connector idempotently store images
without re-uploading the same bytes.
image_embeddings:
One row per (image, model) pair so we can run multiple embedders
side-by-side (CLIP ViT-B/32 baseline + SigLIP fine re-rank, etc.).
Stored as real[] with explicit dim column — PG-native, no extension
required. When pgvector lands locally the migration to vector(dim) +
HNSW is a 2-line ALTER.
No data seeded; v0.3 connector lands in a future tick.
29/29 tests still green. 3/3 migrations recorded.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
---
db/migrations/0003_image_intake.sql | 66 +++++++++++++++++++++++++++++++++++++
db/schema.sql | 1 +
2 files changed, 67 insertions(+)
diff --git a/db/migrations/0003_image_intake.sql b/db/migrations/0003_image_intake.sql
new file mode 100644
index 0000000..1446c71
--- /dev/null
+++ b/db/migrations/0003_image_intake.sql
@@ -0,0 +1,66 @@
+-- 0003_image_intake.sql — prep for v0.3 image-intake pipeline.
+--
+-- BLUEPRINT.md calls for CLIP-style multimodal embeddings as the launch
+-- choice for image-based candidate retrieval. This migration lays in:
+--
+-- image_assets — provenance + raw metadata for each image we hold
+-- image_embeddings — vector representations + provenance (model + dim)
+--
+-- Embeddings stored as real[] (PG native) for v0. When pgvector lands,
+-- migrate to `vector(dim)` and add an HNSW index. The ORM/pg client
+-- doesn't need to change — real[] reads back as a JS Number[].
+
+CREATE EXTENSION IF NOT EXISTS pgcrypto;
+
+-- ---------------------------------------------------------------------------
+-- image_assets
+-- ---------------------------------------------------------------------------
+CREATE TABLE IF NOT EXISTS image_assets (
+ id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+ canonical_asset_id uuid REFERENCES canonical_assets(id) ON DELETE SET NULL,
+ raw_observation_id uuid REFERENCES raw_observations(id) ON DELETE SET NULL,
+ -- 'upload' = direct user/console upload; 'auction_lot' / 'marketplace' /
+ -- 'crawl' = auto-captured from a connector. 'reference' = curated set
+ -- (used as a similarity anchor for new uploads).
+ origin text NOT NULL CHECK (origin IN ('upload','auction_lot','marketplace','crawl','reference')),
+ source_url text,
+ storage_url text, -- e.g. s3://… or local file://…
+ sha256 text NOT NULL, -- content-addressed dedupe
+ mime text,
+ width int,
+ height int,
+ attribution text, -- credit string per BLUEPRINT.md
+ captured_at timestamptz NOT NULL DEFAULT now(),
+ created_at timestamptz NOT NULL DEFAULT now(),
+ UNIQUE (sha256)
+);
+CREATE INDEX IF NOT EXISTS image_assets_canonical_idx
+ ON image_assets(canonical_asset_id);
+CREATE INDEX IF NOT EXISTS image_assets_origin_idx
+ ON image_assets(origin);
+
+-- ---------------------------------------------------------------------------
+-- image_embeddings
+-- ---------------------------------------------------------------------------
+-- One image can have multiple embeddings if we run more than one model
+-- (e.g., CLIP ViT-B/32 baseline + a finer SigLIP model for re-ranking).
+-- Vector dimensionality varies by model: 512 for CLIP ViT-B/32, 768 for
+-- ViT-L/14, 768 for SigLIP base, 1024 for ViT-H/14. Store as real[] so PG
+-- doesn't care; enforce dim consistency in app code.
+CREATE TABLE IF NOT EXISTS image_embeddings (
+ id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+ image_asset_id uuid NOT NULL REFERENCES image_assets(id) ON DELETE CASCADE,
+ model text NOT NULL, -- e.g. 'clip-vit-base-patch32'
+ dim int NOT NULL CHECK (dim BETWEEN 64 AND 4096),
+ vector real[] NOT NULL,
+ created_at timestamptz NOT NULL DEFAULT now(),
+ UNIQUE (image_asset_id, model) -- one embedding per (image, model) pair
+);
+CREATE INDEX IF NOT EXISTS image_embeddings_model_idx
+ ON image_embeddings(model);
+
+-- ---------------------------------------------------------------------------
+-- migrations bookkeeping
+-- ---------------------------------------------------------------------------
+INSERT INTO schema_migrations(filename) VALUES ('0003_image_intake.sql')
+ ON CONFLICT (filename) DO NOTHING;
diff --git a/db/schema.sql b/db/schema.sql
index aeccd96..e51fa0b 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -1,3 +1,4 @@
-- schema.sql — applies all migrations in order. Re-run safe (every migration uses IF NOT EXISTS).
\i db/migrations/0001_canonical_schema.sql
\i db/migrations/0002_transactions_unique.sql
+\i db/migrations/0003_image_intake.sql
← 9b63e84 yolo tick #3: live liquidity_score + expected_dts (no more c
·
back to Lifestyle Asset Intel
·
yolo tick #5: METHODOLOGY.md + /methodology route 63e0563 →