`db/migrations/0003_image_intake.sql`

67 lines
-- 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;