AI開発のデータベース選定ガイド

-- pgvector 拡張を有効化
CREATE EXTENSION IF NOT EXISTS vector;

-- エンベディングを保存するテーブルを作成
CREATE TABLE documents (
  id          bigserial PRIMARY KEY,
  user_id     uuid REFERENCES auth.users(id),
  content     text NOT NULL,
  metadata    jsonb DEFAULT '{}',
  embedding   vector(1536),  -- OpenAI text-embedding-3-small の次元数
  created_at  timestamptz DEFAULT now()
);

-- HNSW インデックスを作成（IVFFlatより高速・高精度）
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- ベクトル類似検索用のRPC関数を作成
CREATE OR REPLACE FUNCTION match_documents(
  query_embedding vector(1536),
  match_threshold float DEFAULT 0.7,
  match_count     int DEFAULT 5
)
RETURNS TABLE (
  id         bigint,
  content    text,
  metadata   jsonb,
  similarity float
)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT
    documents.id,
    documents.content,
    documents.metadata,
    1 - (documents.embedding <=> query_embedding) AS similarity
  FROM documents
  WHERE 1 - (documents.embedding <=> query_embedding) > match_threshold
  ORDER BY documents.embedding <=> query_embedding
  LIMIT match_count;
END;
$$;

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_ANON_KEY
);

// ドキュメントをエンベディングに変換して保存
async function storeDocument(content: string, metadata: object) {
  const embeddingRes = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: content,
  });

  const { data, error } = await supabase
    .from('documents')
    .insert({
      content,
      metadata,
      embedding: embeddingRes.data[0].embedding,
    });
  return { data, error };
}

// ベクトル類似検索を実行
async function searchDocuments(query: string) {
  const embeddingRes = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: query,
  });

  const { data } = await supabase.rpc('match_documents', {
    query_embedding: embeddingRes.data[0].embedding,
    match_threshold: 0.7,
    match_count: 5,
  });
  return data;
}

-- 全文検索用のカラムを追加
ALTER TABLE documents
  ADD COLUMN fts tsvector
  GENERATED ALWAYS AS (to_tsvector('japanese', content)) STORED;

CREATE INDEX ON documents USING gin(fts);

-- ハイブリッド検索関数（キーワード30% + セマンティック70%）
CREATE OR REPLACE FUNCTION hybrid_search(
  search_query     text,
  query_embedding  vector(1536),
  match_count      int DEFAULT 5,
  keyword_weight   float DEFAULT 0.3,
  semantic_weight  float DEFAULT 0.7
)
RETURNS TABLE (
  id bigint, content text, metadata jsonb, score float
)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT
    d.id, d.content, d.metadata,
    (keyword_weight * ts_rank(d.fts, websearch_to_tsquery(search_query))) +
    (semantic_weight * (1 - (d.embedding <=> query_embedding))) AS score
  FROM documents d
  ORDER BY score DESC
  LIMIT match_count;
END;
$$;

-- RLSを有効化（必須！これなしではデータが全公開になります）
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- ユーザーは自分のドキュメントのみ閲覧可能
CREATE POLICY "Users can view own documents"
  ON documents FOR SELECT
  USING (auth.uid() = user_id);

-- ユーザーは自分のドキュメントのみ作成可能
CREATE POLICY "Users can insert own documents"
  ON documents FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- ユーザーは自分のドキュメントのみ更新可能
CREATE POLICY "Users can update own documents"
  ON documents FOR UPDATE
  USING (auth.uid() = user_id);

-- ユーザーは自分のドキュメントのみ削除可能
CREATE POLICY "Users can delete own documents"
  ON documents FOR DELETE
  USING (auth.uid() = user_id);

import { serve } from 'https://deno.land/std/http/server.ts';
import { createClient } from 'https://esm.sh/@supabase/supabase-js';

serve(async (req) => {
  const { query } = await req.json();

  // 1. クエリをエンベディングに変換
  const embeddingRes = await fetch('https://api.openai.com/v1/embeddings', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${Deno.env.get('OPENAI_API_KEY')}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({
      model: 'text-embedding-3-small',
      input: query,
    }),
  });
  const { data } = await embeddingRes.json();

  // 2. ベクトル検索で関連ドキュメントを取得
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL'),
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')
  );
  const { data: docs } = await supabase.rpc('match_documents', {
    query_embedding: data[0].embedding,
    match_count: 5,
  });

  // 3. Claude APIで回答を生成
  const context = docs.map(d => d.content).join('\n---\n');
  const claudeRes = await fetch('https://api.anthropic.com/v1/messages', {
    method: 'POST',
    headers: {
      'x-api-key': Deno.env.get('ANTHROPIC_API_KEY'),
      'content-type': 'application/json',
      'anthropic-version': '2023-06-01',
    },
    body: JSON.stringify({
      model: 'claude-sonnet-4-20250514',
      max_tokens: 1024,
      messages: [{
        role: 'user',
        content: `以下のコンテキストを参考に回答してください。\n\nContext:\n${context}\n\nQuestion: ${query}`,
      }],
    }),
  });

  const answer = await claudeRes.json();
  return new Response(JSON.stringify({
    answer: answer.content[0].text,
    sources: docs,
  }));
});