auto-data-snapshot: 2026-09-23T14:48:42 (1 data files) — visual-search/sql/product_image_fingerprints.sql

b1c94e394e7e0762aeb96c98d2dd06e184dc7d99 · 2026-09-23 14:49:06 -0700 · auto-commit-fleet

Files touched

A visual-search/sql/product_image_fingerprints.sql

Diff

commit b1c94e394e7e0762aeb96c98d2dd06e184dc7d99
Author: auto-commit-fleet <steve@designerwallcoverings.com>
Date:   Wed Sep 23 14:49:06 2026 -0700

    auto-data-snapshot: 2026-09-23T14:48:42 (1 data files) — visual-search/sql/product_image_fingerprints.sql
---
 visual-search/sql/product_image_fingerprints.sql | 31 ++++++++++++++++++++++++
 1 file changed, 31 insertions(+)

diff --git a/visual-search/sql/product_image_fingerprints.sql b/visual-search/sql/product_image_fingerprints.sql
new file mode 100644
index 0000000..29cea97
--- /dev/null
+++ b/visual-search/sql/product_image_fingerprints.sql
@@ -0,0 +1,31 @@
+-- product_image_fingerprints — CLIP fingerprints stored IN Postgres with product linkage + model
+-- versioning + SQL-filterable columns (design port of handbag-auth-nextjs/python-matcher's
+-- `handbags` table, TK-12090 Lane P). LOCAL Mac2 dw_unified mirror ONLY.
+--
+-- Vector type: bytea (float32 little-endian, L2-normalized, `dim` floats) + in-process cosine.
+-- pgvector is NOT installed on this mirror (pg_available_extensions has no 'vector'), so the
+-- handbag design's `vector(768)` + ivfflat index is replaced by: SQL pre-filter on the indexed
+-- columns below, then numpy cosine over the filtered rows. Byte-identical to image_embeddings.
+--
+-- UNDO: DROP TABLE IF EXISTS product_image_fingerprints;
+CREATE TABLE IF NOT EXISTS product_image_fingerprints (
+  id                  bigserial PRIMARY KEY,
+  source_vc_id        bigint      NOT NULL,          -- image_embeddings.vc_id it was copied from
+  shopify_product_id  bigint,                        -- numeric id from shopify_products.shopify_id gid
+  dw_sku              text,
+  mfr_sku             text,
+  vendor_code         text,
+  pattern_name        text,
+  image_url           text,
+  product_status      text,                          -- shopify_products.status at load time (ACTIVE/DRAFT/ARCHIVED)
+  model_tag           text        NOT NULL,          -- which CLIP weights produced `embedding`
+  dim                 integer     NOT NULL,
+  embedding           bytea       NOT NULL,
+  created_at          timestamptz NOT NULL DEFAULT now(),
+  UNIQUE (source_vc_id, model_tag)
+);
+CREATE INDEX IF NOT EXISTS pif_dw_sku_idx     ON product_image_fingerprints (dw_sku);
+CREATE INDEX IF NOT EXISTS pif_vendor_idx     ON product_image_fingerprints (vendor_code);
+CREATE INDEX IF NOT EXISTS pif_active_idx     ON product_image_fingerprints (product_status) WHERE product_status = 'ACTIVE';
+CREATE INDEX IF NOT EXISTS pif_shopify_pid_idx ON product_image_fingerprints (shopify_product_id);
+COMMENT ON TABLE product_image_fingerprints IS 'TK-12090 Lane P: CLIP fingerprints in PG (bytea+in-process cosine; no pgvector). Loaded from image_embeddings by visual-search/load_pg_fingerprints.py. Undo: DROP TABLE.';

← d5f6c4a visual-search nightly embed: env-first DB user (PGUSER or ru · back to Dw Photo Capture · visual-search: product_image_fingerprints PG table + disk-gu 6a07319 →