ข้ามไปที่เนื้อหา

TLX Database Schema (Logical Design)

เอกสารนี้สรุปโครงสร้างฐานข้อมูลเชิงตรรกะจาก requirements.txt เพื่อให้ทีมพัฒนาประเมิน effort และตีราคาได้ง่าย

1) Scope ของข้อมูลที่ต้องรองรับ

ระบบต้องรองรับ 4 กลุ่มหลัก: - Master Data: ผู้เข้าร่วมระบบ, คู่ค้า, สินค้า, รหัสมาตรฐาน - Transaction Documents: วงจรเอกสาร Quote -> PO -> Confirm -> Delivery -> Receipt -> Invoice -> Billing -> Payment - Infrastructure Data: endpoint, public key, routing, validation result - Back-office & Governance: KYC/KYB, RBAC, Audit log, monitoring, notification

2) Proposed Logical ER Overview

erDiagram
    ORGANIZATION ||--o{ USER_ACCOUNT : has
    ORGANIZATION ||--o{ PARTICIPANT : maps_to
    PARTICIPANT ||--o{ PARTICIPANT_ENDPOINT : owns
    PARTICIPANT ||--o{ PARTICIPANT_PUBLIC_KEY : publishes
    PARTICIPANT ||--o{ PARTNER_MATCH_REQUEST : requests
    PARTICIPANT ||--o{ PARTNER_MATCH_REQUEST : receives
    PARTICIPANT ||--o{ PRODUCT : owns
    PARTICIPANT ||--o{ DOCUMENT_HEADER : sends
    PARTICIPANT ||--o{ DOCUMENT_HEADER : receives

    USER_ACCOUNT ||--o{ USER_ROLE : has
    ROLE ||--o{ USER_ROLE : maps
    ROLE ||--o{ ROLE_PERMISSION : grants
    PERMISSION ||--o{ ROLE_PERMISSION : maps

    DOCUMENT_TYPE ||--o{ DOCUMENT_HEADER : classifies
    DOCUMENT_HEADER ||--|{ DOCUMENT_LINE : has
    DOCUMENT_HEADER ||--o{ DOCUMENT_REFERENCE : references
    DOCUMENT_HEADER ||--o{ TRANSACTION_EVENT : emits
    DOCUMENT_HEADER ||--o{ VALIDATION_RESULT : validates
    DOCUMENT_HEADER ||--o{ DELIVERY_SHIPMENT : ships
    DOCUMENT_HEADER ||--o{ GOODS_RECEIPT : receives
    DOCUMENT_HEADER ||--o{ BILLING_BATCH_ITEM : includes

    DOCUMENT_TYPE ||--o{ DOCUMENT_REGISTRY : configures
    PARTICIPANT ||--o{ DOCUMENT_REGISTRY : subscribes
    PARTICIPANT ||--o{ NOTIFICATION_PREFERENCE : sets
    NOTIFICATION_PREFERENCE ||--o{ NOTIFICATION_LOG : delivers

    TRANSACTION_EVENT ||--o{ AUDIT_LOG : audits

3) Core Tables (แนะนำ)

3.1 Identity, Participant, Access Control

  1. organization
  2. id (PK)
  3. legal_name, tax_id (unique), organization_type (seller|buyer|both)
  4. kyc_status (pending|approved|rejected)

  5. created_at, updated_at

  6. participant

  7. id (PK, internal uuid)
  8. participant_id (unique business id from registry)
  9. organization_id (FK -> organization.id)
  10. status (active|inactive|suspended)

  11. default_endpoint_id (nullable FK -> participant_endpoint.id)

  12. participant_endpoint

  13. id (PK)
  14. participant_id (FK)
  15. channel_type (api|web_portal|edi|other)
  16. endpoint_url

  17. is_active

  18. participant_public_key

  19. id (PK)
  20. participant_id (FK)
  21. key_id, public_key_pem, algorithm

  22. valid_from, valid_to, is_current

  23. user_account

  24. id (PK)
  25. organization_id (FK)
  26. email (unique), full_name, status

  27. last_login_at

  28. role, permission, user_role, role_permission

  29. ใช้ RBAC สำหรับ admin|support|user และ permission รายฟังก์ชัน

  30. partner_match_request

  31. id (PK)
  32. requester_participant_id (FK)
  33. target_participant_id (FK)
  34. status (pending|accepted|rejected|cancelled)
  35. requested_at, responded_at, response_reason

3.2 Product Catalog Registry

  1. product
  2. id (PK)
  3. seller_participant_id (FK)
  4. seller_sku (indexed)
  5. gtin (indexed)
  6. name, description, brand
  7. unspsc_code, hs_code
  8. unit_of_measure_code, currency_code
  9. price_amount
  10. package_type, package_size, weight, dimension_json
  11. status (active|inactive)

  12. created_at, updated_at

  13. product_media

  14. id (PK)
  15. product_id (FK)

  16. media_type, media_url, metadata_json

  17. code_list

  18. id (PK)
  19. code_type (uom|currency|document_type|status|custom)
  20. code_value, code_label, is_active

3.3 Document & Transaction Lifecycle

  1. document_type
  2. id (PK)
  3. code (unique) เช่น QUOTATION, PURCHASE_ORDER, ORDER_CONFIRMATION, DELIVERY_NOTE, GOODS_RECEIPT, INVOICE, TAX_INVOICE, BILLING, PAYMENT_CONFIRMATION, CLAIM

  4. description

  5. document_header

  6. id (PK)
  7. message_id (unique, ใช้ trace และ audit)
  8. doc_no
  9. document_type_id (FK)
  10. sender_participant_id (FK)
  11. receiver_participant_id (FK)
  12. issue_date, due_date, expected_delivery_date
  13. status (สถานะตามประเภทเอกสาร)
  14. currency_code, total_amount, tax_amount, net_amount
  15. payload_json (raw/normalized)

  16. created_at, updated_at

  17. document_line

  18. id (PK)
  19. document_id (FK)
  20. line_no
  21. product_id (nullable FK)
  22. seller_sku, gtin, description
  23. ordered_qty, confirmed_qty, delivered_qty, received_qty

  24. unit_price, line_amount

  25. document_reference

  26. id (PK)
  27. document_id (FK เอกสารปลายทาง)
  28. ref_document_id (FK เอกสารต้นทาง)

  29. ref_type (from_quotation|from_po|from_invoice|from_delivery_note|other)

  30. delivery_shipment

  31. id (PK)
  32. delivery_note_document_id (FK)
  33. invoice_document_id (FK)
  34. carrier_name, truck_no
  35. package_count

  36. eta_datetime, shipment_status

  37. goods_receipt

  38. id (PK)
  39. goods_receipt_document_id (FK)
  40. delivery_note_document_id (FK)
  41. receipt_status (complete|partial|rejected)
  42. claim_required (boolean)

  43. claim_reason

  44. billing_batch

  45. id (PK)
  46. buyer_participant_id (FK)
  47. seller_participant_id (FK)
  48. billing_date, payment_due_date

  49. status (draft|sent|confirmed|paid|cancelled)

  50. billing_batch_item

  51. id (PK)
  52. billing_batch_id (FK)

  53. invoice_document_id (FK)

  54. payment_confirmation

  55. id (PK)
  56. billing_batch_id (FK)
  57. payer_participant_id (FK)
  58. payment_date, payment_ref_no, amount
  59. status

3.4 Directory, Validation, Routing, Notification, Audit

  1. document_registry
  2. id (PK)
  3. participant_id (FK)
  4. document_type_id (FK)
  5. direction (send|receive|both)
  6. endpoint_id (FK)

  7. is_active

  8. transaction_event

  9. id (PK)
  10. document_id (FK)
  11. event_type (received|validated|routed|delivered|failed|acknowledged)
  12. event_status (success|failed)
  13. error_code, error_message

  14. occurred_at

  15. validation_result

  16. id (PK)
  17. document_id (FK)
  18. validation_stage (structural|semantic)
  19. is_valid

  20. rule_code, rule_message

  21. notification_preference

  22. id (PK)
  23. user_id (FK)
  24. channel (email|webhook|sms|other)

  25. is_enabled

  26. notification_log

  27. id (PK)
  28. preference_id (FK)
  29. document_id (nullable FK)

  30. send_status, sent_at, provider_response

  31. audit_log

  32. id (PK)
  33. user_id (nullable FK)
  34. participant_id (nullable FK)
  35. event_id (nullable FK -> transaction_event.id)
  36. action, module, resource_type, resource_id
  37. ip_address, user_agent

  38. created_at

  39. message_schema_template

  40. id (PK)
  41. document_type_id (FK)
  42. version
  43. schema_format (json_schema|xsd|custom)
  44. schema_body
  45. is_active

4) Document Status Models (ค่าเริ่มต้น)

  • Quotation: draft -> sent -> accepted|rejected|expired
  • Purchase Order: draft -> sent -> confirmed|partially_confirmed|rejected
  • Delivery Note: created -> in_transit -> delivered|failed
  • Goods Receipt: pending -> complete|partial|rejected
  • Invoice/Tax Invoice: issued -> submitted -> acknowledged -> disputed|approved
  • Billing: draft -> submitted -> confirmed -> paid|cancelled
  • Payment Confirmation: issued -> accepted|rejected

5) Key Constraints สำหรับความถูกต้อง

  • document_header.message_id ต้อง unique ทั้งระบบ
  • ผู้ส่ง/ผู้รับใน document_header ต้องมีอยู่ใน participant และ active
  • ประเภทเอกสารที่ส่งได้ต้องผ่าน document_registry
  • delivery_note ต้องอ้างอิง invoice/tax_invoice
  • goods_receipt ต้องอ้างอิง delivery_note
  • purchase_order ควรอ้างอิง quotation เพื่อคุมราคา
  • organization(tax_id)
  • participant(participant_id, status)
  • product(seller_participant_id, gtin)
  • product(name) + Full-text index ที่ name, description
  • document_header(message_id)
  • document_header(document_type_id, status, issue_date)
  • document_header(sender_participant_id, receiver_participant_id, created_at)
  • document_reference(ref_document_id)
  • transaction_event(document_id, occurred_at desc)
  • audit_log(created_at, user_id, module)

7) Assumptions / Out of Scope (เพื่อ vendor estimate)

Assumptions: - ใช้ RDBMS เดียวเป็นหลัก (เช่น PostgreSQL) - มี field payload_json เพื่อรองรับ schema เอกสารที่ evolve - การเข้ารหัส JWE จัดการที่ service layer แต่เก็บ metadata ใน DB

Out of Scope (ในเอกสารนี้): - physical database tuning รายละเอียดระดับ infra - data migration จากระบบเดิม - BI data warehouse model