# 🗄️ Database Schema — GOFLOW Enterprise AI OS Portal

**Database Schema Document (ER Diagram)**
เวอร์ชัน 1.0 · Confidential – GOFLOW Enterprise · อัปเดตล่าสุด: 2026-08-07
DBMS: PostgreSQL 16+ · ORM: Prisma

---

## 1. ER Diagram (ภาพรวม)

```mermaid
erDiagram
    USERS ||--o{ ORGANIZATIONS : "member_of"
    ORGANIZATIONS ||--o{ ORGANIZATION_MEMBERS : has
    USERS ||--o{ ORGANIZATION_MEMBERS : has
    ORGANIZATIONS ||--o{ BOOKINGS : receives
    USERS ||--o{ BOOKINGS : requests
    ORGANIZATIONS ||--o{ PARTNERS : applies
    POSITIONS ||--o{ APPLICATIONS : receives
    USERS ||--o{ APPLICATIONS : submits
    APPLICATIONS ||--o{ RESUME_FILES : has
    USERS ||--o{ CONVERSATIONS : owns
    CONVERSATIONS ||--o{ MESSAGES : contains
    USERS ||--o{ API_KEYS : owns
    ORGANIZATIONS ||--o{ AI_AGENTS : deploys
    AI_AGENTS ||--o{ AGENT_TASKS : executes
    USERS ||--o{ KNOWLEDGE_DOCUMENTS : uploads
    ORGANIZATIONS ||--o{ KNOWLEDGE_DOCUMENTS : owns
    PRODUCTS ||--o{ ORDERS : in
    ORDERS ||--o{ ORDER_ITEMS : contains
    ORDERS ||--o{ PAYMENTS : paid_by
    USERS ||--o{ WALLETS : has
    WALLETS ||--o{ TRANSACTIONS : records
    USERS ||--o{ AUDIT_LOGS : generates
```

---

## 2. ตารางหลัก (Core Tables)

### `users`
| คอลัมน์ | ชนิด | หมายเหตุ |
|---|---|---|
| id | UUID | PK |
| email | VARCHAR(255) | UNIQUE, NOT NULL |
| password_hash | VARCHAR(255) | bcrypt/argon2 |
| full_name | VARCHAR(150) | NOT NULL |
| phone | VARCHAR(30) | nullable |
| role | ENUM | `ADMIN, DEVELOPER, CUSTOMER, SELLER, RIDER, INVESTOR` |
| status | ENUM | `ACTIVE, PENDING, SUSPENDED` |
| created_at / updated_at | TIMESTAMPTZ | auto |

### `organizations`
| คอลัมน์ | ชนิด | หมายเหตุ |
|---|---|---|
| id | UUID | PK |
| name | VARCHAR(200) | NOT NULL |
| type | ENUM | `SME, ENTERPRISE, GOV, COOP, LOGISTICS, AGRI` |
| size | VARCHAR(20) | เช่น `1-50, 51-200, 201-1000, 1000+` |
| plan | ENUM | `FREE, STARTER, BUSINESS, ENTERPRISE` |
| status | ENUM | `PENDING_REVIEW, ACTIVE, TRIAL, SUSPENDED` |
| contact_email | VARCHAR(255) | |
| created_at / updated_at | TIMESTAMPTZ | |

### `organization_members`
| คอลัมน์ | ชนิด | หมายเหตุ |
|---|---|---|
| id | UUID | PK |
| organization_id | UUID | FK → organizations |
| user_id | UUID | FK → users |
| role_in_org | ENUM | `OWNER, ADMIN, MANAGER, MEMBER, VIEWER` |
| UNIQUE(organization_id, user_id) | | |

---

## 3. AI และ Knowledge

### `ai_agents`
| คอลัมน์ | ชนิด | หมายเหตุ |
|---|---|---|
| id | UUID | PK |
| organization_id | UUID | FK |
| name | VARCHAR(100) | เช่น `CEO AI` |
| agent_type | ENUM | `CEO, CTO, CFO, COO, SPECIALIST, CUSTOM` |
| level | SMALLINT | 1–5 (AI Level Framework) |
| skills | JSONB | ชุดทักษะ |
| permissions | JSONB | Permission Model |
| status | ENUM | `ACTIVE, PAUSED, ARCHIVED` |

### `agent_tasks`
| คอลัมน์ | ชนิด | หมายเหตุ |
|---|---|---|
| id | UUID | PK |
| agent_id | UUID | FK → ai_agents |
| title | VARCHAR(255) | |
| status | ENUM | `OPEN, IN_PROGRESS, DONE, BLOCKED` |
| priority | ENUM | `LOW, MEDIUM, HIGH, CRITICAL` |
| due_at | TIMESTAMPTZ | nullable |
| result | JSONB | ผลลัพธ์/รายงาน |

### `conversations` / `messages`
- conversations: `id, user_id, org_id, session_id, created_at`
- messages: `id, conversation_id, role (USER|AI), content TEXT, reasoning_mode, sources JSONB, created_at`

### `knowledge_documents`
| คอลัมน์ | ชนิด | หมายเหตุ |
|---|---|---|
| id | UUID | PK |
| organization_id | UUID | FK |
| uploaded_by | UUID | FK → users |
| title | VARCHAR(255) | |
| file_id | VARCHAR(100) | อ้างอิง MinIO object |
| vector_ids | JSONB | chunk IDs ใน Qdrant |
| status | ENUM | `PROCESSING, READY, FAILED` |
| created_at | TIMESTAMPTZ | |

---

## 4. Booking / Recruitment / Partner

### `bookings`
`id, type (ENTERPRISE_DEMO|AI_CONSULT|PARTNER_MEETING), user_id, organization_id, name, email, phone, topic, preferred_date DATE, preferred_time TIME, status (PENDING_CONFIRMATION|CONFIRMED|COMPLETED|CANCELLED), notes, created_at`

### `positions`
`id, title, team (FRONTEND|BACKEND|AI|MOBILE|DEVOPS|QA|DESIGN|DOCS), headcount SMALLINT, status (OPEN|CLOSED), description TEXT, requirements JSONB, tags JSONB, created_at`

### `applications`
`id, position_id, user_id (nullable), name, email, phone, portfolio_url, resume_file_id, message TEXT, status (RECEIVED|REVIEWING|INTERVIEW|OFFER|REJECTED), interview_at TIMESTAMPTZ, notes, created_at`

### `partners`
`id, company, type (TECHNOLOGY|INTEGRATION|CHANNEL), contact_name, contact_email, status (PENDING|ACTIVE|SUSPENDED), tier, created_at`

---

## 5. Marketplace / FinTech (v1.1)

### `products`
`id, seller_id (FK users), name, description, category, price NUMERIC(12,2), currency, stock INT, status (DRAFT|ACTIVE|ARCHIVED), metadata JSONB, created_at`

### `orders` / `order_items`
- orders: `id, buyer_id, status (PENDING|PAID|SHIPPING|DELIVERED|CANCELLED), total NUMERIC(12,2), currency, created_at`
- order_items: `id, order_id, product_id, qty, unit_price, subtotal`

### `wallets` / `transactions`
- wallets: `id, user_id, balance NUMERIC(12,2), currency, version INT (optimistic lock)`
- transactions: `id, wallet_id, type (TOPUP|PAYMENT|PAYOUT|REFUND), amount, reference_type, reference_id, status (PENDING|SUCCESS|FAILED), created_at`

### `subscriptions`
`id, organization_id, plan, period (MONTHLY|YEARLY), status (ACTIVE|CANCELED|PAST_DUE), current_period_start, current_period_end, created_at`

---

## 6. Security / Infrastructure

### `api_keys`
`id, user_id, organization_id, name, key_prefix (เช่น gflw_live_xxxx), key_hash (SHA-256 เฉพาะ hash), scopes JSONB, expires_at, last_used_at, status (ACTIVE|REVOKED)`

### `audit_logs`
`id, actor_type (USER|AI_AGENT|SYSTEM), actor_id, action, resource_type, resource_id, ip, user_agent, metadata JSONB, created_at` — **เขียนอย่างเดียว (append-only)**

---

## 7. Indexes และข้อควรปฏิบัติ

| ตาราง | Index ที่แนะนำ |
|---|---|
| messages | `(conversation_id, created_at)` |
| applications | `(position_id, status)` |
| bookings | `(organization_id, preferred_date)` |
| transactions | `(wallet_id, created_at DESC)` |
| audit_logs | `(actor_id, created_at DESC)` |

- ทุกตารางมี `created_at` / `updated_at` (Prisma `@updatedAt`)
- ใช้ **UUID v7** (เรียงตามเวลา) หรือ ULID เป็น PK
- Soft delete (`deleted_at`) สำหรับข้อมูลสำคัญ (organizations, products, applications)
- ตัวเงินใช้ `NUMERIC` (ไม่ใช้ float)
- ข้อมูลขนาดใหญ่ (resume, เอกสาร RAG) เก็บใน MinIO — DB เก็บ `file_id` เท่านั้น

---

*เอกสารนี้เป็นส่วนหนึ่งของ GOFLOW Developer Kit — จัดทำเป็น `schema.prisma` ตามตารางนี้ใน Sprint 2*