`visual-search/sql/product_image_fingerprints.sql`

32 lines
-- 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.';