Database_Design

🗄️ Database Schema & ERD Design (การออกแบบฐานข้อมูล)

---

🇹🇭 ภาษาไทย (For User)

📊 1. แผนผังความสัมพันธ์ข้อมูล (Mermaid ERD)

ระบบ SplitDee ใช้ฐานข้อมูล PostgreSQL แบบเชิงสัมพันธ์ (Relational Database) โดยมีโครงสร้างความสัมพันธ์ดังนี้:

erDiagram
    users {
        uuid id PK
        varchar email UK
        varchar password_hash
        varchar display_name
        varchar phone_number
        varchar avatar_url
        boolean is_active
        timestamp created_at
    }

    groups {
        uuid id PK
        varchar name
        text description
        varchar invite_code UK
        timestamp created_at
    }

    group_members {
        uuid id PK
        uuid group_id FK
        uuid user_id FK
        varchar role
        timestamp joined_at
    }

    bills {
        uuid id PK
        uuid group_id FK
        uuid creator_id FK
        varchar title
        numeric total_amount
        varchar category
        timestamp created_at
    }

    expense_shares {
        uuid id PK
        uuid bill_id FK
        uuid user_id FK
        numeric share_amount
        varchar status
        timestamp paid_at
    }

    payments {
        uuid id PK
        uuid bill_id FK
        uuid payer_id FK
        numeric amount
        varchar slip_image_url
        varchar slip_trans_id UK
        varchar verification_status
        timestamp verified_at
        timestamp created_at
    }

    chats {
        uuid id PK
        uuid group_id FK
        uuid sender_id FK
        varchar message_type
        text message
        jsonb metadata
        timestamp created_at
    }

    group_pets {
        uuid id PK
        uuid group_id FK UK
        varchar pet_name
        integer level
        integer xp
        integer hp
        integer happiness
        timestamp updated_at
    }

    users ||--o{ group_members : "belongs to"
    groups ||--o{ group_members : "contains"
    groups ||--o{ bills : "records"
    groups ||--o{ chats : "has messages"
    groups ||--|| group_pets : "owns"
    bills ||--o{ expense_shares : "splits into"
    bills ||--o{ payments : "paid via"
    users ||--o{ expense_shares : "owes"
    users ||--o{ payments : "submits"
    users ||--o{ chats : "sends"

---

📝 2. รายละเอียดโครงสร้างตาราง (Table Definitions)

2.1 ตาราง users (ผู้ใช้งาน)

เก็บข้อมูลส่วนบุคคลและข้อมูลสำหรับการรับเงินผ่าน PromptPay

• id (UUID, Primary Key): รหัสผู้ใช้ระบบ
• email (VARCHAR(255), Unique, Indexed): อีเมล
• password_hash (VARCHAR(255)): รหัสผ่านเข้ารหัส
• display_name (VARCHAR(100)): ชื่อแสดงในแอป
• phone_number (VARCHAR(20)): เบอร์มือถือ (ใช้สร้าง PromptPay QR)
• avatar_url (VARCHAR(255)): ลิงก์รูปภาพโปรไฟล์
• is_active (BOOLEAN): สถานะบัญชี

2.2 ตาราง groups (กลุ่มกิจกรรม)

• id (UUID, Primary Key): รหัสกลุ่ม
• name (VARCHAR(100)): ชื่อกลุ่ม
• description (TEXT): คำอธิบายกลุ่ม
• invite_code (VARCHAR(10), Unique, Indexed): รหัสสำหรับเชิญเพื่อนร่วมห้อง/กลุ่มแชท

2.3 ตาราง group_members (สมาชิกในกลุ่ม)

• group_id (UUID, Foreign Key -> groups.id on Delete Cascade)
• user_id (UUID, Foreign Key -> users.id on Delete Cascade)
• role (VARCHAR(20)): สิทธิ์ผู้ใช้ (owner, member)
• joined_at (TIMESTAMP): วันเวลาที่เข้าร่วม

2.4 ตาราง bills (รายการบิลค่าใช้จ่าย)

• id (UUID, Primary Key)
• group_id (UUID, Foreign Key -> groups.id)
• creator_id (UUID, Foreign Key -> users.id): สมาชิกกลุ่มที่เป็นผู้สำรองจ่ายเงินไปก่อน
• title (VARCHAR(255)): ชื่องานหรือหมวดบิล (เช่น “ค่าหมูกระทะ”, “ค่าไฟรอบ พ.ค.”)
• total_amount (NUMERIC(10, 2)): ยอดเงินรวมของบิล
• category (VARCHAR(50)): หมวดหมู่ค่าใช้จ่าย
• created_at (TIMESTAMP): วันเวลาที่สร้างบิล

2.5 ตาราง expense_shares (ยอดส่วนแบ่งหนี้รายบุคคล)

เก็บข้อมูลว่าสมาชิกคนใดต้องร่วมแชร์ค่าใช้จ่ายเป็นจำนวนเงินเท่าใดในบิลนั้น

• id (UUID, Primary Key)
• bill_id (UUID, Foreign Key -> bills.id on Delete Cascade)
• user_id (UUID, Foreign Key -> users.id): ผู้รับผิดชอบส่วนแบ่ง
• share_amount (NUMERIC(10, 2)): จำนวนยอดเงินที่ต้องจ่าย
• status (VARCHAR(20)): สถานะการจ่าย (unpaid, paid, processing)
• paid_at (TIMESTAMP, Nullable): วันเวลาที่ยอดได้รับการเคลียร์เรียบร้อย

2.6 ตาราง payments (ประวัติการชำระเงินและตรวจสอบสลิป)

• id (UUID, Primary Key)
• bill_id (UUID, Foreign Key -> bills.id)
• payer_id (UUID, Foreign Key -> users.id): ผู้โอนเงิน
• amount (NUMERIC(10, 2)): จำนวนเงินโอนตามรูปสลิป
• slip_image_url (VARCHAR(255)): พาธรูปภาพสลิปที่อัปโหลด
• slip_trans_id (VARCHAR(100), Unique, Indexed): รหัสธุรกรรม (Transaction ID ของสลิปธนาคาร)
• verification_status (VARCHAR(20)): สถานะสแกนสลิป (pending, verified, failed)
• verified_at (TIMESTAMP): วันเวลาที่ตรวจสลิปเสร็จสมบูรณ์

2.7 ตาราง chats (ข้อมูลประวัติการแชทกลุ่ม)

• id (UUID, Primary Key)
• group_id (UUID, Foreign Key -> groups.id)
• sender_id (UUID, Foreign Key -> users.id): ผู้ส่ง (Null ได้ หากเป็น System Message)
• message_type (VARCHAR(20)): ประเภทข้อความ (text, system, bill)
• message (TEXT): เนื้อหาข้อความหรือคำอธิบายระบบ
• metadata (JSONB): ข้อมูลเสริมของบิลหรือสลิปการโอนเพื่อคลิกเข้าสู่การจัดการหนี้โดยตรง

2.8 ตาราง group_pets (สถานะสัตว์เลี้ยง Mochi ประจำกลุ่ม)

• id (UUID, Primary Key)
• group_id (UUID, Foreign Key -> groups.id Unique): หนึ่งกลุ่มมีสัตว์เลี้ยงได้ 1 ตัว
• pet_name (VARCHAR(50)): ชื่อสัตว์เลี้ยง
• level (INTEGER): เลเวล
• xp (INTEGER): ค่าประสบการณ์
• hp (INTEGER): พลังชีวิต (ลดลงเมื่อมีคนค้างจ่ายเงินเลยกำหนดเวลา)
• happiness (INTEGER): ค่าความสุขของสัตว์เลี้ยง

---

🇬🇧 English (For AI Agents)

🚀 Database Indexing Strategy

To optimize query performance for relational queries, the following indexes are implemented:

• idx_users_email: Unique index on users(email) for credential validation.
• idx_groups_invite_code: Unique index on groups(invite_code) for swift group onboarding.
• idx_group_members_composite: Composite index on group_members(group_id, user_id) for permission checks.
• idx_expense_shares_composite: Composite index on expense_shares(bill_id, user_id).
• idx_payments_trans_id: Unique index on payments(slip_trans_id) to prevent double-spending and duplicate slip usage.
• idx_chats_group_created: Composite index on chats(group_id, created_at DESC) for retrieving chat messages.

🎮 Gamification Trigger Rules (Event-Driven Updates)

1. On Payment Verified:
   • Add XP to group_pets based on time to settle.
   • If payment is settled before the deadline, add happiness.
   • Update expense_shares.status to 'paid'.
2. On Overdue Debt:
   • A cron job runs every night; if a bill’s due date is exceeded, subtract hp from the group’s group_pets for each day of delay.
   • Send notification to the group chat: “Mochi is getting sick because of unpaid bills!”