`python-matcher/scripts/setup_db.sql`

85 lines
-- Setup PostgreSQL database for handbag vector matching
-- Run this once to initialize the database

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Drop existing table if recreating
-- DROP TABLE IF EXISTS handbags CASCADE;

-- Create handbags table with vector embeddings
CREATE TABLE IF NOT EXISTS handbags (
  id              bigserial PRIMARY KEY,
  sku             text UNIQUE NOT NULL,
  brand           text NOT NULL,
  model_name      text NOT NULL,
  pattern_name    text,
  colorway        text,
  material        text,
  hardware        text,
  size            text,
  condition       text,
  year            integer,
  price_usd       numeric(10, 2),
  price_jpy       numeric(10, 2),
  image_url       text NOT NULL,
  source          text,
  external_id     text,
  created_at      timestamp DEFAULT CURRENT_TIMESTAMP,
  updated_at      timestamp DEFAULT CURRENT_TIMESTAMP,

  -- 768-dimensional vector for CLIP embeddings
  embedding       vector(768),

  -- Metadata
  is_active       boolean DEFAULT true,
  verified        boolean DEFAULT false
);

-- Create indexes for fast search
CREATE INDEX IF NOT EXISTS idx_handbags_brand ON handbags(brand);
CREATE INDEX IF NOT EXISTS idx_handbags_model ON handbags(model_name);
CREATE INDEX IF NOT EXISTS idx_handbags_sku ON handbags(sku);
CREATE INDEX IF NOT EXISTS idx_handbags_active ON handbags(is_active) WHERE is_active = true;

-- Create IVFFlat index for vector similarity search
-- lists = 100 is good for ~10K-100K vectors
-- Adjust based on your catalog size
CREATE INDEX IF NOT EXISTS idx_handbags_embedding
ON handbags
USING ivfflat (embedding vector_l2_ops)
WITH (lists = 100);

-- Optional: Create HNSW index (better accuracy, more memory)
-- CREATE INDEX idx_handbags_embedding_hnsw
-- ON handbags
-- USING hnsw (embedding vector_l2_ops);

-- Create function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create trigger to auto-update updated_at
DROP TRIGGER IF EXISTS update_handbags_updated_at ON handbags;
CREATE TRIGGER update_handbags_updated_at
BEFORE UPDATE ON handbags
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- Sample query to test vector search
-- SELECT id, sku, brand, model_name,
--        1 - (embedding <-> '[0.1,0.2,...]'::vector) AS similarity
-- FROM handbags
-- WHERE embedding IS NOT NULL
-- ORDER BY embedding <-> '[0.1,0.2,...]'::vector
-- LIMIT 10;

COMMENT ON TABLE handbags IS 'Handbag catalog with vector embeddings for visual similarity search';
COMMENT ON COLUMN handbags.embedding IS '768-dimensional CLIP vector for image similarity';
COMMENT ON INDEX idx_handbags_embedding IS 'IVFFlat index for fast approximate nearest neighbor search';