Lược đồ Cơ sở dữ liệu (Database Schema)

Thông tin nhanh

• Engine: Neon (Serverless PostgreSQL)
• Mô hình: Multi-Tenant Single-DB — tables gắn tenant_id hoặc org_id
• Migrations: 30+ files trong schema/ (001-030)
• Connector: @neondatabase/serverless (HTTP/WebSocket)

Xem thêm: Kiến trúc · Affiliate System · Catalog System

Sơ đồ ERD — Core CRM

Sơ đồ ERD — Affiliate System

Sơ đồ ERD — Catalog System

Danh sách Migrations (30 files)

CRM Core (001-009)

File	Mô tả
001_init.sql	admin_users, tenants, customers, services, bookings, reviews
002_payments.sql	payments, bank_accounts
003_minicrm.sql	leads, activities, UTM tracking
004_queue_upgrade.sql	queue tickets, room config
005_unified_flow.sql	Unified booking-lead flow
006_spa_tenant.sql	Multi-tenant RBAC
007_lead_integration.sql	API keys, lead tracking fields
008_call_system.sql	call_logs, call_scripts
009_booking_enhancements.sql	Booking status enhancements

Seed Data (010-018)

File	Ngành
010_seed_call_scripts.sql	Kịch bản gọi điện mẫu
011-015_seed_*.sql	dental, nail, hair, beauty, gym
016_seed_pet.sql	pet shop
017_seed_clinic_data.sql	clinic
018_seed_spa_data.sql	spa

Auth & Platform (016-025)

File	Mô tả
016_multi_org_auth.sql	organizations, users, multi-org
017_advanced_auth.sql	Google OAuth, OTP, invitations
019_public_pages.sql	Public pages
019_seed_saleagent_org.sql	SaleHay org seed
020_agent_integration.sql	agent_tokens, webhook_subscriptions, delivery_log
021_billing.sql	Billing & subscription tables
022_einvoice.sql	E-invoice (hoá đơn điện tử)
023_tax_reports.sql	Tax reporting tables
024_audit_trail.sql	Audit trail system
025_platform_admin.sql	Platform admin tables

Affiliate (026-029)

File	Mô tả
026_affiliate.sql	affiliates, aff_campaigns, aff_links, aff_clicks, aff_referrals, aff_commissions, aff_payouts
027_affiliate_profile.sql	Affiliate profile enhancements (CCCD, DOB, address)
028_affiliate_attribution.sql	Last-touch attribution model
029_org_affiliates.sql	ORG-level affiliates, payout batches, payout items

Catalog (030)

File	Mô tả
030_catalog_system.sql	service_categories, products, service_packages, package_items, price_history + backfill

TIP

Chạy migrations: node run-migration.mjs — script đọc và execute tuần tự mỗi file .sql trong schema/.

Multi-Tenant Model

Hệ thống dùng Single-DB Multi-Tenant — mọi query filter theo tenant_id:

Level	Scope	Tables
Platform	Toàn hệ thống	users, organizations, affiliates
Organization	Mỗi doanh nghiệp	org settings, billing, subscriptions
Tenant	Mỗi chi nhánh	customers, leads, bookings, services, products