← บล็อก

หลายร้าน ฐานข้อมูลเดียว

ออกแบบ Postgres ให้แชทบอทร้านสัตว์เลี้ยงด้วย RLS และกฎที่ฐานข้อมูลบังคับเอง

Backendอ่าน 25 นาที

Petcare Assistant เป็นผู้ช่วย AI และ CRM บน LINE สำหรับร้านสัตว์เลี้ยง ทั้งคลินิก ร้านอาบน้ำตัดขน และที่รับฝากเลี้ยง ระบบเป็น multi-tenant คือทุกร้านใช้ Postgres ตัวเดียวกัน ในฐานข้อมูลมีทั้งประวัติแชท ข้อมูลเจ้าของ ข้อมูลน้อง ประวัติวัคซีน และนัดหมาย ถ้าร้านหนึ่งเห็นลูกค้าของอีกร้านได้แม้แต่แถวเดียว ระบบนี้ก็ขายไม่ได้

ผมทำโปรเจกต์นี้คนเดียว บทความนี้เล่าว่าฐานข้อมูลออกแบบไว้อย่างไร มีหลักอะไรบ้าง และหลักแต่ละข้อมีที่มาจากอะไร บางข้อตั้งไว้ตั้งแต่วันแรก บางข้อได้มาจากการที่ของพังหรือช้ากว่าที่คิด ตัวอย่างโค้ดทุกชิ้นตัดมาจาก repo จริง ชื่อลูกค้าในตัวอย่างเป็นข้อมูล seed ของร้านตัวอย่าง “Happy Paws”

ภาพรวม: 66 ตาราง แบ่งตามเรื่อง

schema เขียนด้วย Drizzle ORM แยกไฟล์ตามเรื่องไว้ใน packages/db/src/schema/ ตอนนี้มี 66 ตาราง ในจำนวนนี้ 59 ตารางเป็นข้อมูลของร้าน (tenant) ทุกตารางในกลุ่มนี้มีคอลัมน์ organization_id และ policy ของ row-level security (RLS) ส่วนอีก 7 ตารางอยู่นอก tenant ได้แก่ตารางของระบบ login (Better Auth), รายชื่อทีมของผมเอง และตารางเฝ้าระบบ

ตารางหลักแบ่งเป็นแปดกลุ่ม: ร้านและทีม, ลูกค้าและน้อง, แชท, จองคิว, ท่อ LINE, ข้อมูลร้าน, ติดตามและนับ และกลุ่มนอก tenant

ฐานข้อมูลทำงานหลายอย่างพร้อมกัน เป็นทั้ง CRM ของร้าน, คิวงานของ worker (ข้อความเข้าและข้อความออกถูกเก็บเป็นแถวก่อนจะประมวลผล), ระบบจองคิว และบันทึกว่าใครทำอะไร ผมเลือกเก็บทุกอย่างไว้ใน Postgres ตัวเดียว โดยมี Redis เป็นแค่คิวของ BullMQ และ embedding สำหรับค้นความรู้ของร้านก็ใช้ pgvector ในฐานข้อมูลเดียวกัน

หลักข้อ 1: แยกร้านด้วย RLS ไม่ใช่ด้วย where

วิธีที่คนส่วนใหญ่ใช้แยกข้อมูลแต่ละร้านคือเติม where organization_id = ? ลงในทุก query วิธีนี้ใช้ได้ตราบใดที่ไม่มีใครลืม แต่โค้ดที่มีเป็นร้อย query ยังไงก็ต้องมีสักที่ที่ลืม ผมเลยให้ Postgres เป็นคนกรองแทน

policy เดียว ใช้กับทุกตาราง

ทุกตารางของร้านเรียก tenantIsolation() ซึ่งสร้าง policy ชื่อเดียวกันหมด

export const currentOrganizationId = sql`nullif(current_setting('app.organization_id', true), '')::uuid`;

export function tenantIsolation(column = "organization_id") {
  const matchesTenant = sql`${sql.identifier(column)} = ${currentOrganizationId}`;
  return pgPolicy("tenant_isolation", {
    as: "permissive",
    for: "all",
    to: appRole,
    using: matchesTenant,
    withCheck: matchesTenant,
  });
}

using ทำให้มองไม่เห็นแถวของร้านอื่น ส่วน withCheck ทำให้ insert หรือ update แถวที่ระบุเป็นร้านอื่นไม่ผ่าน policy จะใช้กับ role petcare_app ซึ่งเป็น role ที่แอปใช้รันจริง ถ้ายังไม่ได้ตั้ง tenant ค่า current_setting จะเป็น NULL ผลคือไม่เห็นแถวไหนเลย ถ้าลืมตั้ง ระบบจึงล้มแบบปิด (fail closed) ไม่ได้รั่วออกไป

tenant ตั้งต่อ transaction

โค้ดทุกจุดที่แตะข้อมูลร้านต้องผ่าน withTenant()

return db.transaction(async (tx) => {
  await tx.execute(sql`select set_config('app.organization_id', ${organizationId}, true)`);
  return fn(tx);
});

อาร์กิวเมนต์ตัวที่สามของ set_config คือ true แปลว่าค่านี้อยู่แค่ใน transaction นี้ พอ connection ถูกคืนกลับเข้า pool ค่าก็หายไปด้วย คำขอถัดไปที่ได้ connection เดิมจึงไม่ได้ tenant ของคนก่อนติดมา ก่อนตั้งค่า ฟังก์ชันตรวจด้วยว่า id เป็น UUID ถ้าไม่ใช่ก็ throw ทันที

appDb() ผ่าน withTenant เห็นเฉพาะแถวของร้าน A, ไม่ตั้ง tenant ไม่เห็นอะไรเลย, systemDb() ข้าม RLS เห็นทุกแถว

สอง connection สอง role

packages/db/src/connections.ts มีให้เรียกแค่สองตัว

  • appDb() ต่อด้วย role petcare_app ซึ่งโดน RLS บังคับ ทั้ง web, api, worker และ services ใช้ตัวนี้
  • systemDb() ต่อด้วย role petcare ซึ่งเป็นเจ้าของตาราง จึงข้าม RLS ได้ ใช้ได้เฉพาะตอน login, ตอนหาว่าผู้ใช้คนหนึ่งเป็นสมาชิกของร้านไหนบ้าง, ในเครื่องมือของทีม, migration, seed และเทสต์ ถ้าจะใช้ที่อื่นต้องมีคอมเมนต์อธิบายว่าทำไม

ตัวอย่างที่จำเป็นต้องใช้ systemDb() จริงๆ คือ webhook ของ LINE ตอนที่ webhook เข้ามา ระบบยังไม่รู้ว่าเป็นของร้านไหน ต้องเอา channel id ไปหา credential เพื่อตรวจลายเซ็นก่อน getChannelCredentials จึงใช้ owner connection และมีคอมเมนต์เขียนกำกับไว้ว่า “Never expose it to a request handler that a shop user controls” พอรู้ร้านแล้ว งานที่เหลือก็กลับไปใช้ withTenant(appDb(), …) ตามปกติ

role petcare_app ถูกสร้างใน docker/postgres/init.sh ไม่ได้สร้างใน migration และได้สิทธิ์ผ่าน ALTER DEFAULT PRIVILEGES คือได้ SELECT, INSERT, UPDATE, DELETE บนตารางที่ owner สร้างขึ้นใหม่โดยอัตโนมัติ

ไม่เติม organization_id ใน where เอง

ในแผนแรก (PLAN ข้อ 15.7) ผมเขียนไว้ว่า “ทุก query กรอง organization_id” แล้วให้ RLS เป็นชั้นป้องกันที่สอง พอเขียนจริงผมกลับข้างกัน กฎที่จดไว้ใน docs/decisions.md คือ ไม่มี service ไหนเติม organization_id ลงใน where ให้ withTenant ตั้ง tenant แล้ว RLS เป็นคนกรอง เหตุผลคือถ้าเติมเองที่หนึ่งแต่ไม่เติมอีกที่หนึ่ง นั่นคือจุดที่ข้อมูลจะเริ่มรั่ว ถ้าทุกที่ใช้กติกาเดียวกันคือไม่เติมเลย ความผิดพลาดแบบนั้นก็ไม่มีโอกาสเกิด

เทสต์ที่ทำให้ลืมไม่ได้

กฎข้อนี้ใช้ได้ก็ต่อเมื่อตารางใหม่ทุกตารางมี policy ผมไม่ได้ฝากเรื่องนี้ไว้กับความจำ rls-coverage.test.ts จะถาม pg_class กับ pg_policies ว่ามีตารางไหนที่มีคอลัมน์ organization_id แต่ยังไม่เปิด RLS หรือยังไม่มี policy tenant_isolation บ้าง ถ้ามีแม้แต่ตารางเดียว build จะไม่ผ่าน

ส่วน rls.test.ts ทดสอบพฤติกรรมด้วย role ของแอปจริงกับสองร้าน

  • ถ้าไม่ตั้ง tenant จะไม่เห็นแถวไหนเลย
  • ถ้าตั้งเป็นร้าน A จะเห็นเฉพาะ organizations, branches และ members ของร้าน A
  • insert สาขาที่ระบุเป็นร้าน B จะได้ error violates row-level security policy
  • update หรือ delete สาขาของร้าน B จะได้ 0 แถว และข้อมูลจริงไม่เปลี่ยน
  • พอ transaction จบ ค่า tenant ไม่ติดค้างไปถึง query ถัดไป

รูที่ RLS ไม่ได้ปิดให้

ก่อนเปิดเดโมให้คนทั่วไปเข้ามาใช้ (milestone M13e) ผมตรวจโค้ดอีกรอบแล้วเจอสองเรื่องที่ RLS ไม่ได้ช่วยอะไร

  1. foreign key ไม่สนใจ RLS ตอนตั้งค่า LINE channel ร้านเลือกได้ว่า channel นี้ผูกกับสาขาไหน (defaultBranchId) ถ้ามีคนส่ง id ของสาขาร้านอื่นเข้ามา foreign key ก็ยังผ่าน เพราะตอนตรวจ foreign key Postgres ไม่ได้มองผ่าน policy ตอนนี้ saveLineChannel จึงโหลดสาขานั้นผ่าน withTenant ก่อน ถ้าหาไม่เจอ แปลว่าไม่ใช่สาขาของร้านนี้
  2. ตารางของ Better Auth ไม่มี RLS แต่ petcare_app ได้สิทธิ์บนตารางเหล่านี้มาตั้งแต่ตอนสร้างผ่าน default privileges ผลคือ query ใดก็ตามที่รันด้วย role ของแอปอ่าน session token, password hash และ reset token ของทุกร้านได้ migration 0030_revoke_auth_tables_from_app_role.sql จึง REVOKE ALL ทั้งสี่ตาราง แล้วคืนสิทธิ์ให้แค่ SELECT ("id", "email") ON users เพราะหน้ารายชื่อพนักงานยังต้อง join อีเมลอยู่ ฝั่ง login ใช้ owner connection อยู่แล้วจึงไม่กระทบ

ทั้งสองเรื่องสอนเหมือนกันว่า RLS กันได้เฉพาะการอ่านและเขียนแถวผ่าน policy เท่านั้น สิทธิ์ระดับตารางและ foreign key ยังต้องตรวจเองแยกต่างหาก

หลักข้อ 2: service ตรวจสิทธิ์ซ้ำอีกชั้น

RLS ตอบได้แค่ว่าแถวนี้เป็นของร้านไหน แต่ตอบไม่ได้ว่าพนักงานหน้าร้านสาขาอารีย์มีสิทธิ์แก้ราคาของสาขาบางนาหรือเปล่า คำถามนั้นเป็นหน้าที่ของ can(actor, permission, resource) ใน packages/permissions ซึ่งเป็นฟังก์ชันล้วน ไม่มี I/O และใช้ร่วมกันทั้งใน dashboard, API, ปุ่มในกลุ่ม LINE และ tool ของบอท

ทุก service เขียนตามรูปแบบเดียวกับ packages/services/src/branches.ts ซึ่งตั้งไว้เป็นตัวอย่างอ้างอิง

return withTenant(ctx.db, ctx.organizationId, async (tx) => {
  const [before] = await tx.select().from(branches).where(eq(branches.id, branchId));
  if (!before) throw new NotFoundError("Branch");

  authorize(ctx, "catalog.manage", { branchId });

  const [after] = await tx
    .update(branches)
    .set(parsed.data)
    .where(eq(branches.id, branchId))
    .returning();

  await recordAudit(tx, ctx, {
    action: "branch.updated",
    targetType: "branch",
    targetId: branchId,
    before,
    after,
  });
  return after!;
});

หนึ่ง service call: ตรวจ input ด้วย zod แล้วทำทุกขั้นใน transaction เดียว ตั้งแต่ตั้ง tenant, โหลดแถว, authorize, เปลี่ยนข้อมูล จนถึง recordAudit

ลำดับในโค้ดนี้มีเหตุผลทุกขั้น

  • ตรวจ input ด้วย zod .strict() ก่อนเปิด transaction ถ้ามี field ที่ไม่รู้จักติดมาก็ปฏิเสธทันที
  • โหลดแถวก่อนแล้วค่อย authorize เพราะสิทธิ์ขึ้นกับตัวแถว เช่นสาขาที่แถวนั้นสังกัด ถ้าแถวไม่มีอยู่ (หรือมีแต่เป็นของร้านอื่น ซึ่ง RLS ซ่อนไว้) ก็ได้ NotFoundError ถ้ามีแต่ไม่มีสิทธิ์ก็ได้ ForbiddenError
  • recordAudit เขียนใน transaction เดียวกับการเปลี่ยนข้อมูล ถ้าเขียน audit ไม่สำเร็จ การเปลี่ยนแปลงก็ rollback ไปด้วย จึงไม่มีทางที่ข้อมูลจะเปลี่ยนโดยไม่มีบันทึก audit_logs เก็บค่า before กับ after เป็น jsonb และเป็นตาราง append-only

ServiceContext ประกอบขึ้นจากผู้ใช้ที่ login แล้วเท่านั้น organizationId, สาขา และ contact ที่ใช้กำหนดขอบเขตไม่เคยมาจาก request ของ client หรือจากโมเดล กับบอทก็ใช้กฎเดียวกัน BotActor เก็บ contactId และ branchId ของบทสนทนาที่ระบบเป็นคนใส่ให้ โมเดลเรียก tool ได้ แต่เลือกไม่ได้ว่าจะดูข้อมูลของลูกค้าคนไหน

นอกจากนี้ยังมีกำแพงเล็กๆ อีกชั้น หน้าราคาสาธารณะใน LIFF ใช้ PublicActor ซึ่ง can() ให้แค่สิทธิ์อ่าน และ recordAudit จะ throw ทันทีถ้า actor เป็น public เพราะฉะนั้นต่อให้วันหนึ่งมีคนเผลอให้สิทธิ์เขียนกับ public actor การเขียนนั้นก็ยัง rollback เพราะ audit ไม่ผ่าน

หลักข้อ 3: ตารางเป็นรูปร่างของธุรกิจ

เจ้าของหนึ่งคน หลายบัญชี LINE

LINE ส่งมาแค่ userId ซึ่งแต่ละ provider ได้ค่าไม่เหมือนกัน ไม่มีเบอร์โทรมาด้วย และร้านสัตว์เลี้ยงก็มักมีพ่อกับแม่ที่ทักมาคนละบัญชีเพื่อถามเรื่องน้องตัวเดียวกัน ผมเลยแยกสองแนวคิดนี้ออกจากกัน

  • contacts คือเจ้าของหรือครอบครัว มีชื่อ เบอร์ โน้ตของพนักงาน และ mergedIntoId
  • channel_identities คือบัญชีหนึ่งบัญชีบนหนึ่ง channel มี unique (channel_id, external_user_id) และมี verificationStatus (unverified / pending / verified) พร้อมบอกว่ายืนยันด้วยวิธีไหน (QR, พนักงานผูกให้, เบอร์ตรงกัน ฯลฯ)

บอทจะเห็นข้อมูลน้องและประวัติของลูกค้าได้ก็ต่อเมื่อ identity นั้นถูก verify แล้วเท่านั้น ถ้าพบว่าคนเดียวมีสอง contact mergeContacts จะย้าย identity, บทสนทนา, น้อง และข้อมูลอื่นไปไว้ที่ contact ปลายทาง แถวเดิมไม่ถูกลบ แต่ตั้ง mergedIntoId ไว้เพื่อให้ตามย้อนได้ ขั้นตอนทั้งหมดนี้อยู่ใน transaction เดียว รวมถึงแถว audit contact.merged ที่เก็บทั้งสอง contact ก่อน merge และจำนวนแถวที่ถูกย้าย

น้องเป็น subjects และเก็บรายละเอียดเป็น jsonb

ตารางของน้องไม่ได้ชื่อ pets แต่ชื่อ subjects โดยมี kind default เป็น pet และเก็บรายละเอียดเฉพาะธุรกิจไว้ใน attributes แบบ jsonb (species, breed, weightKg, coat ฯลฯ) ซึ่งมี TypeScript type PetAttributes กำกับไว้ เหตุผลคือแผนตั้งแต่แรกแยกส่วน core ที่ไม่รู้จักธุรกิจออกจากส่วน industry pack ถ้าวันหนึ่งทำให้อู่รถ ตารางเดิมก็ใช้เก็บรถได้ (PLAN ข้อ 2)

แต่คอลัมน์ไหนที่ระบบต้องใช้ค้นหาหรือใช้ในเงื่อนไข ผมเก็บเป็นคอลัมน์ธรรมดา ไม่ยัดไว้ใน jsonb เหตุผลของข้อนี้อยู่ในหัวข้อ index ด้านล่าง

สิ่งที่บอท “จำ” ต้องรอคนยืนยัน

facts คือสิ่งที่ระบบจำเกี่ยวกับลูกค้าหรือน้อง เช่น แพ้ยา กัดคน หรือชอบให้ตัดขนแบบไหน ทุกแถวมี status เป็น suggested, active หรือ rejected และมี sourceMessageId กับ sourceQuote เก็บข้อความที่ลูกค้าพิมพ์มาจริงๆ เพื่อให้พนักงานตรวจได้ว่าเรื่องนี้มาจากไหน มีค่า confidence ของโมเดลด้วย หมวดที่สำคัญถูกแยกตามคนที่มีสิทธิ์ยืนยัน critical_medical ต้องให้สัตวแพทย์หรือผู้จัดการยืนยัน ส่วน critical_behavior ใครที่ดูแลน้องก็ยืนยันได้

บางตารางโมเดลไม่ได้เขียนเลย care_records (วัคซีนและการรักษา) เขียนได้แค่พนักงานหรือการ import และมีช่องสำหรับให้สัตวแพทย์ยืนยัน purchases มาจากระบบตอนการจองเสร็จ จากพนักงานตอนขายหน้าร้าน หรือจากการ import ส่วนตารางท่อ LINE ทั้งหมดมีคอมเมนต์หัวไฟล์ว่า “Nothing here is written by the LLM”

หลักข้อ 4: กฎที่สำคัญ ให้ฐานข้อมูลบังคับเอง

กฎที่ถ้าพลาดแล้วเสียหายจริง ผมไม่ได้เขียนไว้แค่ใน if ของโค้ด แต่ให้ Postgres เป็นคนปฏิเสธ

จองซ้อนไม่ได้ เพราะ exclusion constraint

เวลาที่พนักงานหรือห้องถูกจองไว้จะเก็บใน resource_allocations เป็น tstzrange แล้ว migration 0007_booking_constraints.sql เพิ่ม constraint นี้

ALTER TABLE "resource_allocations" ADD CONSTRAINT "resource_allocations_staff_no_overlap"
  EXCLUDE USING gist ("staff_id" WITH =, "during" WITH &&)
  WHERE ("active" AND "staff_id" IS NOT NULL);

ความหมายคือ ห้ามมีสองแถวที่ active ซึ่งเป็นพนักงานคนเดียวกันและช่วงเวลาทับกัน (extension btree_gist ทำให้ใช้ = บน uuid กับ && บน range ใน GiST index ตัวเดียวกันได้) ถ้าทับก็ได้ SQLSTATE 23P01 ซึ่งโค้ดแปลงเป็น BookingConflictError ในตารางเดียวกันยังมี CHECK ว่าทุกแถวต้องเป็นของ booking หรือ hold อย่างใดอย่างหนึ่งเท่านั้น และ bookings มี CHECK ว่า end_at > start_at

ระหว่างทำมีปัญหาเรื่องหนึ่ง constraint ตัวนี้ถูกต้อง แต่ทำให้ deadlock ได้ insert ที่รอแถวของเราซึ่งยังไม่ commit จะทิ้ง index entry ค้างไว้ พอเราไปแก้แถวของตัวเอง (ย้ายจาก hold ไปเป็น booking) การตรวจ constraint ก็ต้องรอ insert ตัวนั้นกลับ ทางแก้คือก่อนเขียนทุกครั้งให้จอง pg_advisory_xact_lock ตามชื่อพนักงานหรือห้อง โดยเรียงชื่อก่อนจอง คนที่มาทีหลังจะรออยู่ตรงนั้น แล้วได้ 23P01 แบบเรียบร้อย lock มีไว้แค่จัดลำดับ คนตัดสินจริงยังเป็น constraint

สอง transaction อ่านว่าคิวว่างทั้งคู่ A ได้ advisory lock ก่อนแล้ว commit ส่วน B รอ lock จากนั้น insert ทับแล้วได้ 23P01

เรื่องนี้มีเทสต์จริงใน bookings-concurrency.test.ts โดยเปิด connection pool สองชุด ปล่อยให้ทั้งสองฝั่งอ่านและตรวจว่าคิวว่างจนผ่านทั้งคู่ก่อน (ใช้ barrier ผ่าน engineHooks.beforeReserve) แล้วค่อยปล่อยให้เขียนพร้อมกัน เทสต์ผ่านเมื่อมีแค่ฝั่งเดียวที่ได้คิว

การจองที่นับเป็นจำนวน เช่นห้องพักต่อคืนหรือจำนวนตัวที่รับฝากต่อวัน ใช้ constraint แบบนี้ไม่ได้ เพราะไม่ใช่การจองช่วงเวลา capacity_reservations จึงนับใหม่ทุกครั้งภายใต้ pg_advisory_xact_lock ของคีย์ (สาขา, ประเภท, วันที่, key) ทำให้คนที่จองพร้อมกันต้องเข้าทีละคน

ข้อความซ้ำ: unique constraint เป็นคนกัน

LINE อาจส่ง webhook เดิมซ้ำมาอีกรอบ และ job ของ worker อาจถูก retry ทั้งสองกรณีกันด้วย unique constraint ไม่ได้ใช้การเช็กก่อนเขียน

  • inbound_events มี unique (channel_id, webhook_event_id) ตอน insert ใช้ onConflictDoNothing ถ้าชนก็แปลว่าเคยได้รับแล้ว ระบบจะไม่ตอบซ้ำ และเพราะเก็บ event ก่อนประมวลผล ถ้า worker ล่มกลางทางก็ยังเอามาประมวลผลใหม่ได้
  • outbound_messages มี unique (organization_id, idempotency_key) ซึ่ง key ถูกตั้งจากสิ่งที่ทำให้เกิดข้อความนั้น เช่น booking-event:<id>, followup:<id> หรือ postback:<webhookEventId> job ที่ถูก retry จะเจอแถวเดิมแล้วไม่ส่งซ้ำ ข้อความแบบ push ยังส่ง retryKey ไปเป็น header X-Line-Retry-Key เพื่อให้ฝั่ง LINE กันซ้ำให้อีกชั้น

outbox อยู่ในตารางเดียวกับข้อมูล

ทุกครั้งที่สถานะนัดเปลี่ยน จะมีแถวใหม่ใน booking_events ถ้าต้องแจ้งลูกค้า แถวนั้นจะมี notifyCustomer = true worker จะส่งข้อความ LINE แล้วตั้ง notifiedAt เพราะแถวนี้ถูกเขียนใน transaction เดียวกับการเปลี่ยนสถานะ จึงไม่มีทางที่นัดจะเปลี่ยนแล้วลืมแจ้ง หรือแจ้งไปแล้วแต่ตัวนัดกลับ rollback งานที่ค้างอยู่หาได้จาก partial index where notify_customer and notified_at is null โดยไม่ต้องอ่านประวัติทั้งหมด

ในตารางเดียวกันนี้ booking_items ยังเก็บ priceSnapshot เป็นราคาที่ quote ไว้ตอนจองด้วย ถ้าร้านแก้ราคาทีหลัง ลูกค้าที่จองไปแล้วก็ยังจ่ายราคาเดิม

หลักข้อ 5: index ต้องใช้ได้จริงภายใต้ RLS

เรื่องนี้ผมเรียนรู้จากการวัด ไม่ใช่จากการออกแบบ (เล่าละเอียดไว้ในบทความ load test) ตอนใส่ประวัติแชทย้อนหลังหกเดือนลงฐานข้อมูล การหา reply token ล่าสุดของลูกค้าช้าลงจาก 0.07 ms เป็น 81 ms ต่อการตอบหนึ่งครั้ง ผมเพิ่ม index บน raw->>'externalUserId' ตามที่ EXPLAIN แนะนำ แต่ไม่ได้ช่วยอะไรเลย

สาเหตุคือผมรัน EXPLAIN ด้วย owner role ซึ่งข้าม RLS ส่วนแอปรันด้วย petcare_app ภายใต้ RLS Postgres จะใช้เงื่อนไขเป็น index condition ได้ก็ต่อเมื่อ operator ทุกตัวในเงื่อนไขนั้นเป็น leakproof การเทียบค่าบน uuid, text, ตัวเลข และ timestamp เป็น leakproof แต่ ->> และ at time zone ไม่ใช่ index บน JSON จึงไม่เคยถูกแอปใช้เลย

กฎที่ได้จากเรื่องนี้ จดไว้ใน docs/decisions.md และเขียนเป็นคอมเมนต์ไว้ข้าง index ใน schema ด้วย

  • index สำหรับ query ที่รันภายใต้ RLS ต้องสร้างบนคอลัมน์ธรรมดา ถ้าเงื่อนไขที่คัดแถวได้ดีอยู่ใน JSON ให้เพิ่มเงื่อนไขบนคอลัมน์ธรรมดาเข้าไปด้วย findFreshReplyToken เพิ่ม outbound_messages.created_at > cutoff ซึ่งเป็นจริงเสมอกับ token ที่เพิ่งใช้ไป ผลลัพธ์จึงไม่เปลี่ยน แต่ index (organization_id, created_at) ใช้หาแถวได้ ผลคือ 81 ms → 0.17 ms
  • ค่าที่ต้องคำนวณ เช่น “วันแรกของเดือนนี้ตามเขตเวลาของร้าน” ให้คำนวณแยกไว้ก่อนแล้วส่งเข้า query เป็นค่าธรรมดา การรวมค่าใช้จ่าย AI ของเดือนลดจาก 48 ms → 12 ms ต่อ turn
  • partial index ที่มี where status = 'sent' ก็ใช้ไม่ได้เช่นกัน ถ้า query ส่ง 'sent' มาเป็น bind parameter
  • ถ้าจะ EXPLAIN ต้องรันด้วย role ของแอปและตั้ง tenant ไว้ก่อน

index ที่ใช้ได้จริงอยู่ใน migration 0029_reply_token_lookup_indexes ส่วน index ที่ลองแล้วไม่ได้ผลถูกลบทิ้งทั้งหมด

หลักข้อ 6: migration เป็นไฟล์ SQL ที่ commit ไว้ และต้องเพิ่มอย่างเดียว

ขั้นตอนตอนนี้เป็นแบบนี้

  1. แก้ schema ใน packages/db/src/schema แล้วรัน pnpm db:generate (คือ drizzle-kit generate) ขั้นนี้แค่ diff schema จึงไม่ต้องใช้ credential ของฐานข้อมูล
  2. commit ไฟล์ SQL ที่ได้ ตอนนี้มี 32 ไฟล์ ตั้งแต่ 0000 ถึง 0031 สิ่งที่ Drizzle เขียนเป็น schema ไม่ได้ เช่น exclusion constraint หรือ REVOKE ก็เขียนเป็น SQL ในไฟล์ migration พร้อมคอมเมนต์อธิบายเหตุผลไว้ในไฟล์
  3. ห้ามแก้ migration ที่ apply ไปแล้ว ถ้าจะเปลี่ยนต้องเขียน migration ใหม่เท่านั้น
  4. ตอน apply ใช้ owner role (src/scripts/migrate.ts)

บนเซิร์ฟเวอร์ scripts/deploy.sh จะ backup ไป Cloudflare R2 ก่อน migrate ทุกครั้ง ถ้า backup ไม่สำเร็จ ก็จะไม่ migrate และไม่รีสตาร์ตอะไรเลย migration รันตอนที่เวอร์ชันเก่ายังให้บริการอยู่ จึงต้องเป็นแบบเพิ่มอย่างเดียว (additive) คือเพิ่มคอลัมน์ได้แต่ห้ามเปลี่ยนชื่อ ถ้าต้อง rollback โค้ด คอลัมน์ใหม่ที่ค้างอยู่ก็ไม่ทำให้อะไรเสีย (รายละเอียดอยู่ในบทความ CI/CD)

หลักข้อ 7: เทสต์กับ Postgres จริง ไม่ใช้ mock

ความเสี่ยงส่วนใหญ่ของระบบนี้อยู่ใน SQL ทั้งการแยกร้าน, exclusion constraint, การนับความจุตอนมีคนจองพร้อมกัน และวันที่ที่ต้องถูกตามเขตเวลา ถ้า mock ฐานข้อมูล เทสต์ก็จะไม่ได้ทดสอบสิ่งเหล่านี้เลย integration test ทุกตัวจึงใช้ createTestDatabases() ซึ่งให้มาสอง connection คือ system (ข้าม RLS ใช้เตรียมข้อมูล) กับ app (โดน RLS ใช้ทดสอบจริง)

  • ทุกครั้งที่รันเทสต์ จะสร้างฐานข้อมูลใหม่จาก migration global-setup.ts จะ reset ฐานข้อมูลแล้วรัน migration ทั้งหมดตั้งแต่ไฟล์แรก ถ้า migration ไหนพัง เทสต์ก็พังด้วย ระหว่างเทสต์แต่ละตัวจะ truncate ทุกตาราง
  • แต่ละ package มีฐานข้อมูลของตัวเอง ตอนแรกทุก package ใช้ petcare_test ร่วมกัน พอ turbo รันเทสต์หลาย package พร้อมกัน แต่ละ package ก็ไปลบข้อมูลของกันและกันกลางทาง อาการคือเทสต์ booking พังตอนรันทั้งหมด แต่ผ่านตอนรันเดี่ยว และมีครั้งหนึ่งเจอ deadlock detected ใน truncateAllTables ตอนนี้ useIsolatedTestDatabase ต่อชื่อ package เข้าไปท้ายชื่อฐานข้อมูล (เช่น petcare_test_services) และสร้างให้เองถ้ายังไม่มี มีหกตัวที่ใช้แบบนี้ ได้แก่ db, services, pipeline, core, auth และ apps/api เวลารวมไม่ได้แย่ลง กลับเร็วขึ้นจาก 3m41s เป็น 3m15s
  • ใน CI เทสต์ราว 2,200 ตัวรันกับ Postgres จริงก่อนทุก deploy

สิ่งที่ยังไม่เรียบร้อย

  • service ยังรับ transaction จากข้างนอกไม่ได้ ตัว import ข้อมูลจาก Excel ต้องเป็นแบบทั้งหมดหรือไม่เอาเลย แต่ทุก service เปิด withTenant เป็นของตัวเอง ตอนนี้แก้ไปก่อนด้วย Proxy ที่ส่ง transaction ที่เปิดอยู่เข้าไปแทน และจะ throw ถ้ามีโค้ดไปแตะ property อื่นของ connection ทางแก้ที่ถูกจริงๆ คือให้ service รับ transaction เป็นตัวเลือกได้
  • sendDraft เขียนสองครั้งในสอง transaction ถ้าระบบล่มระหว่างสองครั้งนั้น จะได้ข้อความที่ส่งไปแล้วแต่ draft ยังค้างสถานะ pending
  • อีเมลของผู้ใช้ทุกคนยังอ่านได้จาก role ของแอป เพราะ GRANT SELECT ("id", "email") ที่คืนให้หน้ารายชื่อพนักงาน การย้าย join นั้นไปใช้ทางที่แคบกว่านี้ยังรอทำ
  • การรวมค่าใช้จ่ายของเดือนยังเป็นการบวกทุกแถว ใช้เวลา 12 ms กับข้อมูลทดสอบ ถ้าวันหนึ่งกลายเป็นปัญหา ทางที่จดไว้คือเก็บยอดสะสม ไม่ใช่เพิ่ม index อีกตัว
  • ยังไม่มี lease บน inbound event ที่ถูก claim ถ้า worker ล่มระหว่างจัดการ event ช่วงไม่กี่วินาทีนั้นอาจทำให้ event หายได้ เรื่องนี้ตัดสินใจไว้ว่าจะทำทีหลัง เพราะตอนนี้มี worker ตัวเดียว
  • load test รอบสองบนเซิร์ฟเวอร์จริงยังไม่ได้ทำ ตัวเลข index ข้างบนวัดบนโน้ตบุ๊ก บนเซิร์ฟเวอร์จริง disk ช้ากว่า และฐานข้อมูลอาจกลายเป็นคอขวดตัวแรกก็ได้

สรุปเป็นข้อ

  1. ให้ Postgres แยกข้อมูลร้านด้วย RLS อย่าฝากไว้กับ where ที่ต้องจำเติมเองทุก query
  2. ตั้ง tenant ต่อ transaction (set_config(…, true)) และออกแบบให้ถ้าลืมตั้งจะไม่เห็นอะไรเลย
  3. มี connection ที่ข้าม RLS ได้แค่ตัวเดียว และจำกัดว่าใช้ได้ที่ไหน
  4. ให้เทสต์ไล่ตรวจ catalog ของ Postgres ว่าทุกตารางของร้านมี policy
  5. RLS ไม่ได้ครอบ foreign key และสิทธิ์ระดับตาราง ต้องตรวจสองเรื่องนี้เอง
  6. ตรวจสิทธิ์ใน service อีกชั้น และเขียน audit ใน transaction เดียวกับการเปลี่ยนข้อมูล
  7. กฎที่ถ้าพลาดแล้วเสียหาย เช่นจองซ้อนหรือส่งข้อความซ้ำ ให้ constraint เป็นคนปฏิเสธ
  8. สร้าง index บนคอลัมน์ธรรมดา และ EXPLAIN ด้วย role ของแอป
  9. migration ต้องเพิ่มอย่างเดียว และ backup ก่อน migrate ทุกครั้ง
  10. เทสต์กับ Postgres จริง และให้แต่ละ package มีฐานข้อมูลเทสต์ของตัวเอง

ที่มา

  • packages/db/src/schema/_shared.ts (tenantIsolation, currentOrganizationId), packages/db/src/client.ts (withTenant), packages/db/src/connections.ts (appDb, systemDb)
  • packages/db/src/schema/conversations.ts, customers.ts, booking.ts, line.ts, audit.ts, tenancy.ts, ops.ts; จำนวนตารางและ policy นับจาก packages/db/drizzle/meta/0031_snapshot.json
  • packages/db/drizzle/0007_booking_constraints.sql, 0029_reply_token_lookup_indexes.sql, 0030_revoke_auth_tables_from_app_role.sql
  • packages/db/src/rls.test.ts, packages/db/src/rls-coverage.test.ts, packages/db/src/testing/, packages/db/src/maintenance.ts, docker/postgres/init.sh
  • packages/services/src/branches.ts, packages/services/src/context.ts, packages/services/src/channels.ts (saveLineChannel, getChannelCredentials), packages/services/src/customers/contacts.ts (mergeContacts), packages/services/src/booking/engine-writes.ts, packages/services/src/booking/bookings-concurrency.test.ts
  • packages/permissions/src/can.ts, packages/permissions/src/model.ts
  • packages/pipeline/src/events.ts (storeInboundEvent), packages/pipeline/src/outbound.ts (deliver)
  • docs/decisions.md: “RLS is the tenant filter, not a where clause”, “Under row-level security, only leakproof conditions reach an index”, “Every package’s integration tests get their own database”, “Writing through services inside one transaction”, “The web app never queries the database”
  • docs/PLAN.md: ข้อ 2, 3.3, 7, 14.3, 15.7, 16.2, 17.3
  • docs/specs/M13e.md: D8 (F3, F4, F6) และบันทึกของ T4
  • docs/load-test-results.md (S8) และ บทความ load test
  • docs/deploy.md (migration แบบ additive, backup ก่อน migrate), scripts/deploy.sh
  • docs/STATUS.md: “Known gaps, by area” หัวข้อ Inbox and follow-ups (sendDraft)