Fuzzy Matching และการค้นหารายการธุรกรรม

1-- เปิด extension ครั้งเดียวต่อฐานข้อมูล
2CREATE EXTENSION IF NOT EXISTS pg_trgm;
3
4-- ตาราง merchant ที่เราจะจับคู่เข้าไป
5CREATE TABLE IF NOT EXISTS merchants (
6  id           BIGSERIAL PRIMARY KEY,
7  display_name TEXT NOT NULL,
8  ref_code     TEXT NOT NULL
9);
10
11-- GIN index บน trigram: ทำให้ % และ similarity() ใช้ index ได้
12-- gin_trgm_ops สำคัญมาก ถ้าไม่ใส่ index จะไม่ถูกใช้
13CREATE INDEX IF NOT EXISTS merchants_name_trgm
14  ON merchants USING GIN (display_name gin_trgm_ops);
15
16CREATE INDEX IF NOT EXISTS merchants_ref_trgm
17  ON merchants USING GIN (ref_code gin_trgm_ops);

1-- ตั้ง threshold ขั้นต่ำ (0.0 - 1.0) ของระดับ session
2SET pg_trgm.similarity_threshold = 0.3;
3
4SELECT
5  id,
6  display_name,
7  similarity(display_name, $1) AS score
8FROM merchants
9WHERE display_name % $1          -- ใช้ GIN index คัดผู้สมัครก่อน
10ORDER BY display_name <-> $1     -- เรียงจากใกล้สุด
11LIMIT 5;

1model Merchant {
2  id          BigInt  @id @default(autoincrement())
3  displayName String  @map("display_name")
4  refCode     String  @map("ref_code")
5
6  @@map("merchants")
7}

1import { bigserial, index, pgTable, text, sql } from "drizzle-orm/pg-core";
2
3export const merchants = pgTable(
4  "merchants",
5  {
6    id: bigserial("id", { mode: "bigint" }).primaryKey(),
7    displayName: text("display_name").notNull(),
8    refCode: text("ref_code").notNull(),
9  },
10  (t) => ({
11    // index แบบ GIN + gin_trgm_ops ผ่าน raw SQL
12    nameTrgm: index("merchants_name_trgm")
13      .using("gin", sql`${t.displayName} gin_trgm_ops`),
14  }),
15);

1import { Pool } from "pg";
2
3const pool = new Pool({ connectionString: process.env.DATABASE_URL });
4
5export type MatchResult = {
6  id: bigint;
7  displayName: string;
8  score: number;
9};
10
11// auto = มั่นใจพอจะจับคู่อัตโนมัติ, review = ต้องให้คนยืนยัน
12const AUTO_THRESHOLD = 0.55;
13const REVIEW_THRESHOLD = 0.3;
14
15export async function matchMerchant(query: string) {
16  const client = await pool.connect();
17  try {
18    await client.query("SET LOCAL pg_trgm.similarity_threshold = 0.3");
19    const { rows } = await client.query<MatchResult>(
20      `SELECT id, display_name AS "displayName",
21              similarity(display_name, $1) AS score
22         FROM merchants
23        WHERE display_name % $1
24        ORDER BY display_name <-> $1
25        LIMIT 5`,
26      [query.trim()],
27    );
28
29    const best = rows[0];
30    if (!best) return { decision: "no_match" as const, candidates: rows };
31    if (best.score >= AUTO_THRESHOLD)
32      return { decision: "auto" as const, match: best, candidates: rows };
33    if (best.score >= REVIEW_THRESHOLD)
34      return { decision: "review" as const, match: best, candidates: rows };
35    return { decision: "no_match" as const, candidates: rows };
36  } finally {
37    client.release();
38  }
39}

1CREATE EXTENSION IF NOT EXISTS vector;
2
3ALTER TABLE merchants
4  ADD COLUMN IF NOT EXISTS embedding vector(1536);
5
6-- HNSW: เร็วและแม่นกว่า ivfflat สำหรับงาน online
7-- vector_cosine_ops คู่กับ <=> (cosine distance)
8CREATE INDEX IF NOT EXISTS merchants_embedding_hnsw
9  ON merchants USING hnsw (embedding vector_cosine_ops);

1import os
2import asyncpg
3from openai import AsyncOpenAI
4
5client = AsyncOpenAI()
6
7async def embed(text: str) -> list[float]:
8    resp = await client.embeddings.create(
9        model="text-embedding-3-small",
10        input=text,
11    )
12    return resp.data[0].embedding
13
14async def match_by_meaning(pool: asyncpg.Pool, query: str, k: int = 5):
15    vec = await embed(query)
16    # ส่ง vector เป็น string literal ที่ pgvector เข้าใจ: '[0.1,0.2,...]'
17    literal = "[" + ",".join(f"{x:.6f}" for x in vec) + "]"
18    rows = await pool.fetch(
19        """
20        SELECT id, display_name,
21               1 - (embedding <=> $1::vector) AS score
22          FROM merchants
23         WHERE embedding IS NOT NULL
24         ORDER BY embedding <=> $1::vector
25         LIMIT $2
26        """,
27        literal, k,
28    )
29    return [dict(r) for r in rows]

1-- รวมอันดับจาก trigram และ vector ด้วย Reciprocal Rank Fusion
2WITH lexical AS (
3  SELECT id, row_number() OVER (ORDER BY display_name <-> $1) AS rnk
4  FROM merchants
5  WHERE display_name % $1
6  LIMIT 50
7),
8semantic AS (
9  SELECT id, row_number() OVER (ORDER BY embedding <=> $2::vector) AS rnk
10  FROM merchants
11  WHERE embedding IS NOT NULL
12  LIMIT 50
13)
14SELECT
15  m.id,
16  m.display_name,
17  COALESCE(1.0 / (60 + l.rnk), 0) + COALESCE(1.0 / (60 + s.rnk), 0) AS rrf
18FROM merchants m
19LEFT JOIN lexical  l ON l.id = m.id
20LEFT JOIN semantic s ON s.id = m.id
21WHERE l.id IS NOT NULL OR s.id IS NOT NULL
22ORDER BY rrf DESC
23LIMIT 10;