검색 API: 전문 검색 + 필터링

-- 마이그레이션
alter table generations
add column search_vec tsvector
generated always as (
  to_tsvector('simple',
    coalesce(prompt, '') || ' ' ||
    coalesce(result, '')
  )
) stored;

-- GIN 인덱스 (검색 빠름)
create index idx_gens_fts on generations using gin(search_vec);

-- to_tsquery (정확한 매치)
select * from generations
where search_vec @@ to_tsquery('simple', '카피');

-- websearch_to_tsquery (사용자 친화적)
select * from generations
where search_vec @@ websearch_to_tsquery('simple', 'AI 카피 OR 광고');

-- 부분 매치 (prefix)
select * from generations
where search_vec @@ to_tsquery('simple', '카피:*');

const { data } = await supabase
  .from('generations')
  .select('*')
  .textSearch('search_vec', `${query}:*`, { type: 'websearch' })
  .order('created_at', { ascending: false })
  .limit(20)

-- pg_trgm 확장 (PostgreSQL 부분 매치)
create extension if not exists pg_trgm;

-- 한국어는 형태소 분석기 부재 → trigram이 차선
create index idx_gens_prompt_trgm on generations using gin(prompt gin_trgm_ops);

-- 검색
select * from generations
where prompt % 'AI 카피'  -- 유사도 매치
order by similarity(prompt, 'AI 카피') desc
limit 20;

select
  id,
  ts_headline('simple', prompt, to_tsquery('simple', 'AI'),
    'StartSel=<mark>, StopSel=</mark>') as highlighted_prompt
from generations
where search_vec @@ to_tsquery('simple', 'AI');

// 클라이언트
<div dangerouslySetInnerHTML={{ __html: gen.highlighted_prompt }} />

// /api/generations/search
import { z } from 'zod'

const schema = z.object({
  q: z.string().optional(),
  category: z.enum(['blog', 'sns', 'email']).optional(),
  date_from: z.string().optional(),
  date_to: z.string().optional(),
  cursor: z.string().optional(),
})

export async function GET(request: Request) {
  const params = schema.parse(Object.fromEntries(new URL(request.url).searchParams))
  const supabase = await createClient()

  let q = supabase.from('generations').select('*')
  if (params.q) q = q.textSearch('search_vec', `${params.q}:*`)
  if (params.category) q = q.eq('category', params.category)
  if (params.date_from) q = q.gte('created_at', params.date_from)
  if (params.date_to) q = q.lte('created_at', params.date_to)
  if (params.cursor) q = q.lt('created_at', params.cursor)
  q = q.order('created_at', { ascending: false }).limit(20)

  const { data } = await q
  return Response.json({ data })
}

[Algolia]   — UI 컴포넌트·인스턴트 검색·$1/1K req
[MeiliSearch] — 오픈소스 self-host·자동 typo 보정
[Elasticsearch] — 강력하지만 운영 부담
[Typesense] — Algolia 오픈소스 대안

→ 1만+ 사용자 도달 전: PostgreSQL FTS로 충분

EXPLAIN ANALYZE
SELECT * FROM generations
WHERE search_vec @@ to_tsquery('simple', '카피:*')
ORDER BY created_at DESC
LIMIT 20;

-- "Bitmap Heap Scan" + "Bitmap Index Scan on idx_gens_fts" → 빠름
-- "Seq Scan" → 인덱스 안 탐 → 점검