supabase-setup.sql

-- Personal Hub Database Setup
-- Run this SQL in your Supabase Dashboard SQL Editor
-- https://supabase.com/dashboard/project/qcnunwpcvqcrbibqwfxb/sql

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

-- Main items table for Personal Hub
CREATE TABLE IF NOT EXISTS items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  type TEXT NOT NULL CHECK (type IN ('app', 'youtube', 'x_post', 'article', 'link', 'note')),
  title TEXT NOT NULL,
  description TEXT,
  url TEXT,
  icon_url TEXT,
  tags TEXT[] DEFAULT '{}',
  pinned BOOLEAN DEFAULT FALSE,
  use_count INTEGER DEFAULT 0,
  last_used TIMESTAMPTZ,
  date_saved TIMESTAMPTZ DEFAULT NOW(),
  embedding VECTOR(384),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for vector similarity search (HNSW)
CREATE INDEX IF NOT EXISTS items_embedding_idx ON items USING hnsw (embedding vector_cosine_ops);

-- Indexes for sorting and filtering
CREATE INDEX IF NOT EXISTS items_use_count_idx ON items (use_count DESC);
CREATE INDEX IF NOT EXISTS items_date_saved_idx ON items (date_saved DESC);
CREATE INDEX IF NOT EXISTS items_pinned_idx ON items (pinned) WHERE pinned = TRUE;

-- Semantic search function
CREATE OR REPLACE FUNCTION match_items(
  query_embedding VECTOR(384),
  match_threshold FLOAT DEFAULT 0.5,
  match_count INT DEFAULT 10
)
RETURNS TABLE (
  id UUID, title TEXT, description TEXT, url TEXT,
  icon_url TEXT, type TEXT, tags TEXT[],
  pinned BOOLEAN, use_count INT, similarity FLOAT
)
LANGUAGE SQL STABLE AS $$
  SELECT id, title, description, url, icon_url, type, tags,
         pinned, use_count,
         1 - (embedding <=> query_embedding) AS similarity
  FROM items
  WHERE 1 - (embedding <=> query_embedding) > match_threshold
  ORDER BY embedding <=> query_embedding
  LIMIT match_count;
$$;

-- Verify setup
SELECT 'Tables created successfully!' as status;
SELECT tablename FROM pg_tables WHERE schemaname = 'public';