Chuyển tới nội dung chính

Kiến trúc dữ liệu - Data Architecture

1. Tổng quan kiến trúc​

1.1. Sơ đồ quan hệ chính​

Contract (1) ──── (N) Scope ──── (N) Campaign Plan
                     │
                     ├──── (N) Payment Milestone
                     │
                     ├──── (N) Vendor Assignment ──── (N) Vendor Payment
                     │
                     └──── (N) Project Deliverable

1.2. Nguyên tắc thiết kế​

  • Contract-driven: Mọi thứ phải xuất phát từ hợp đồng
  • Scope-centric: Scope là đơn vị tính lời/lỗ, nghiệm thu, và thanh toán
  • Flexible attributes: Sử dụng JSON attributes cho các trường động
  • Audit trail: Mọi thay đổi quan trọng đều được log

2. Entity Relationship Diagram (ERD)​

2.1. Các thực thể chính​

3. Database Schema​

3.1. Contract (Hợp đồng)​

Mục đích​

Quản lý hợp đồng tổng thể với khách hàng

Schema​

{
  "contract_id": "string (PK)",
  "contract_code": "string (unique)",
  "client_id": "string (FK to Client)",
  "client_name": "string",
  "start_date": "date",
  "end_date": "date",
  "total_value": "decimal",
  "total_margin_target": "decimal (%)",
  "currency": "string (VND, USD, etc.)",
  "status": "enum (draft, active, completed, cancelled)",
  "contract_file_url": "string",
  "notes": "text",
  "created_by": "string",
  "created_at": "timestamp",
  "updated_at": "timestamp"
}

Ví dụ​

{
  "contract_id": "CNT-2026-001",
  "contract_code": "KWP2026",
  "client_id": "CLI-001",
  "client_name": "Kewpie Vietnam",
  "start_date": "2026-01-01",
  "end_date": "2026-12-31",
  "total_value": 2400000000,
  "total_margin_target": 20,
  "currency": "VND",
  "status": "active",
  "notes": "Hợp đồng performance marketing tổng thể"
}

Validation Rules​

  • end_date phải sau start_date
  • total_value > 0
  • total_margin_target >= 0 và <= 100
  • contract_code phải unique

3.2. Scope (Channel / Service Package)​

Mục đích​

Chia nhỏ hợp đồng thành các gói dịch vụ độc lập về ngân sách, KPI và nghiệm thu

Schema​

{
  "scope_id": "string (PK)",
  "contract_id": "string (FK)",
  "contract_code": "string",
  "scope_code": "string (unique trong contract)",
  "service_type": "enum (ads, web, app, seo, hosting, kol, branding, outsource)",
  "channel": "string (FB, TikTok, Google, Website, Mobile, etc.)",
  "name": "string",
  "description": "text",
  "budget": "decimal",
  "revenue": "decimal",
  "cost_estimated": "decimal",
  "margin_target": "decimal (%)",
  "kpi_type": "string (leads, sales, traffic, views, etc.)",
  "kpi_target": "decimal",
  "unit_price": "decimal",
  "pricing_model": "enum (CPA, CPL, CPC, CPM, fixed_price, milestone)",
  "start_date": "date",
  "end_date": "date",
  "status": "enum (pending, active, paused, completed, cancelled)",
  "attributes": "json",
  "created_at": "timestamp",
  "updated_at": "timestamp"
}

Ví dụ - Ads Scope​

{
  "scope_id": "SCP-2026-001",
  "contract_id": "CNT-2026-001",
  "contract_code": "KWP2026",
  "scope_code": "FB01",
  "service_type": "ads",
  "channel": "FB",
  "name": "Facebook Lead Generation - Office Workers",
  "budget": 800000000,
  "revenue": 1000000000,
  "cost_estimated": 800000000,
  "margin_target": 25,
  "kpi_type": "leads",
  "kpi_target": 50000,
  "unit_price": 20000,
  "pricing_model": "CPL",
  "start_date": "2026-01-01",
  "end_date": "2026-06-30",
  "status": "active",
  "attributes": {
    "target_audience": "Office workers 25-45",
    "region": "HCM, HN",
    "product": "Kewpie Sesame Dressing",
    "campaign_type": "Always-on",
    "break_even_cpl": 16000
  }
}

Ví dụ - Web Scope​

{
  "scope_id": "SCP-2026-002",
  "contract_id": "CNT-2026-001",
  "contract_code": "KWP2026",
  "scope_code": "WEB01",
  "service_type": "web",
  "channel": "Website",
  "name": "Landing Page - Kewpie Product",
  "budget": 0,
  "revenue": 50000000,
  "cost_estimated": 30000000,
  "margin_target": 40,
  "kpi_type": "deliverable",
  "kpi_target": 1,
  "pricing_model": "fixed_price",
  "start_date": "2026-01-01",
  "end_date": "2026-02-28",
  "status": "active",
  "attributes": {
    "platform": "Next.js",
    "cms": "MongoREST",
    "language": "VN",
    "hosting": "Cloudflare",
    "delivery": "Source code + Deployment"
  }
}

Validation Rules​

  • revenue >= cost_estimated
  • Tổng budget của tất cả scope <= contract.total_value
  • end_date phải nằm trong khoảng contract.start_date và contract.end_date
  • scope_code phải unique trong mỗi contract

3.3. Campaign Plan (Kế hoạch Campaign)​

Mục đích​

PM phân bổ ngân sách chi tiết cho từng campaign trong scope ads

Schema​

{
  "campaign_plan_id": "string (PK)",
  "scope_id": "string (FK)",
  "contract_code": "string",
  "scope_code": "string",
  "campaign_code": "string (unique)",
  "campaign_name": "string",
  "platform": "enum (FB, TikTok, Google, etc.)",
  "objective": "string (Lead, Traffic, Conversion, etc.)",
  "segment": "string (Office, Housewife, etc.)",
  "phase": "string (P1, P2, etc.)",
  "budget_allocated": "decimal",
  "kpi_target": "decimal",
  "kpi_type": "string",
  "pricing_model": "enum (CPA, CPL, CPC, CPM)",
  "break_even_cost": "decimal",
  "profit_margin_target": "decimal (%)",
  "start_date": "date",
  "end_date": "date",
  "daily_budget_cap": "decimal",
  "status": "enum (pending, active, paused, completed)",
  "created_at": "timestamp",
  "updated_at": "timestamp"
}

Ví dụ​

{
  "campaign_plan_id": "CMP-PLN-001",
  "scope_id": "SCP-2026-001",
  "contract_code": "KWP2026",
  "scope_code": "FB01",
  "campaign_code": "Kewpie-KWP2026-FB01-FB-Lead-Office-P1",
  "campaign_name": "Kewpie Office Lead Gen - Phase 1",
  "platform": "FB",
  "objective": "Lead",
  "segment": "Office",
  "phase": "P1",
  "budget_allocated": 200000000,
  "kpi_target": 12500,
  "kpi_type": "leads",
  "pricing_model": "CPL",
  "break_even_cost": 16000,
  "profit_margin_target": 25,
  "start_date": "2026-01-01",
  "end_date": "2026-03-31",
  "daily_budget_cap": 2222222,
  "status": "active"
}

Validation Rules​

  • Tổng budget_allocated của tất cả campaign trong scope <= scope.budget
  • campaign_code phải follow naming convention
  • end_date phải nằm trong khoảng scope.start_date và scope.end_date

3.4. Campaign Execution (Thực thi Campaign - từ BigQuery)​

Mục đích​

Dữ liệu thực tế từ ads platform, được mapping vào campaign plan

Schema​

{
  "execution_id": "string (PK)",
  "date": "date",
  "platform": "string",
  "campaign_name": "string",
  "campaign_code": "string (parsed)",
  "scope_code": "string (parsed)",
  "contract_code": "string (parsed)",
  "impressions": "integer",
  "clicks": "integer",
  "spend": "decimal",
  "conversions": "decimal",
  "kpi_achieved": "decimal",
  "ctr": "decimal",
  "cpc": "decimal",
  "cpa": "decimal",
  "data_source": "enum (Meta, TikTok, Google)",
  "synced_at": "timestamp"
}

Ví dụ​

{
  "execution_id": "EXE-20260115-001",
  "date": "2026-01-15",
  "platform": "FB",
  "campaign_name": "Kewpie-KWP2026-FB01-FB-Lead-Office-P1",
  "campaign_code": "Kewpie-KWP2026-FB01-FB-Lead-Office-P1",
  "scope_code": "FB01",
  "contract_code": "KWP2026",
  "impressions": 150000,
  "clicks": 4500,
  "spend": 2100000,
  "conversions": 135,
  "kpi_achieved": 135,
  "ctr": 3.0,
  "cpc": 467,
  "cpa": 15556,
  "data_source": "Meta",
  "synced_at": "2026-01-16T02:00:00Z"
}

3.5. Payment Milestone (Mốc thanh toán)​

Mục đích​

Quản lý các đợt nghiệm thu và thanh toán cho mỗi scope

Schema​

{
  "milestone_id": "string (PK)",
  "scope_id": "string (FK)",
  "contract_code": "string",
  "scope_code": "string",
  "name": "string (Phase 1, Phase 2, etc.)",
  "description": "text",
  "due_date": "date",
  "invoice_date": "date",
  "payment_due_date": "date",
  "amount": "decimal",
  "kpi_required": "decimal",
  "deliverable": "text",
  "acceptance_criteria": "text",
  "status": "enum (pending, ready_to_invoice, invoiced, paid)",
  "invoice_number": "string",
  "payment_received_date": "date",
  "notes": "text",
  "created_at": "timestamp",
  "updated_at": "timestamp"
}

Ví dụ​

{
  "milestone_id": "MLS-001",
  "scope_id": "SCP-2026-001",
  "contract_code": "KWP2026",
  "scope_code": "FB01",
  "name": "Phase 1 - Q1 2026",
  "due_date": "2026-03-31",
  "invoice_date": "2026-04-05",
  "payment_due_date": "2026-04-20",
  "amount": 300000000,
  "kpi_required": 15000,
  "deliverable": "15,000 leads from Facebook Ads",
  "acceptance_criteria": "Valid leads with complete contact information",
  "status": "pending",
  "notes": "Customer expects report by April 3rd"
}

Validation Rules​

  • Tổng amount của tất cả milestone trong scope = scope.revenue
  • due_date <= scope.end_date
  • Status transition: pending → ready_to_invoice → invoiced → paid

3.6. Vendor (Nhà thầu phụ)​

Mục đích​

Quản lý thông tin các vendor/freelancer/đối tác

Schema​

{
  "vendor_id": "string (PK)",
  "vendor_code": "string (unique)",
  "name": "string",
  "type": "enum (agency, freelancer, studio, infrastructure)",
  "services": "array[string]",
  "contact_person": "string",
  "email": "string",
  "phone": "string",
  "address": "text",
  "tax_code": "string",
  "payment_terms": "string",
  "bank_account": "string",
  "rating": "decimal (1-5)",
  "status": "enum (active, inactive)",
  "notes": "text",
  "created_at": "timestamp",
  "updated_at": "timestamp"
}

Ví dụ​

{
  "vendor_id": "VND-001",
  "vendor_code": "ABC-DEV",
  "name": "ABC Development Agency",
  "type": "agency",
  "services": ["web", "app", "ui-design"],
  "contact_person": "Nguyen Van A",
  "email": "[email protected]",
  "phone": "+84901234567",
  "tax_code": "0123456789",
  "payment_terms": "Net 30",
  "rating": 4.5,
  "status": "active"
}

3.7. Vendor Assignment (Phân công Vendor)​

Mục đích​

Gán vendor vào scope cụ thể với chi phí và scope of work

Schema​

{
  "assignment_id": "string (PK)",
  "scope_id": "string (FK)",
  "vendor_id": "string (FK)",
  "role": "string (UI Designer, Backend Dev, etc.)",
  "description": "text",
  "cost": "decimal",
  "start_date": "date",
  "end_date": "date",
  "deliverable": "text",
  "status": "enum (pending, active, completed, cancelled)",
  "created_at": "timestamp",
  "updated_at": "timestamp"
}

Ví dụ​

{
  "assignment_id": "ASG-001",
  "scope_id": "SCP-2026-002",
  "vendor_id": "VND-001",
  "role": "Full-stack Development",
  "description": "Develop landing page with Next.js",
  "cost": 25000000,
  "start_date": "2026-01-01",
  "end_date": "2026-02-15",
  "deliverable": "Responsive landing page with CMS integration",
  "status": "active"
}

3.8. Vendor Payment (Thanh toán Vendor)​

Mục đích​

Quản lý các đợt thanh toán cho vendor

Schema​

{
  "payment_id": "string (PK)",
  "assignment_id": "string (FK)",
  "vendor_id": "string (FK)",
  "amount": "decimal",
  "due_date": "date",
  "payment_date": "date",
  "payment_method": "enum (bank_transfer, cash, etc.)",
  "status": "enum (pending, paid, overdue)",
  "invoice_number": "string",
  "notes": "text",
  "created_at": "timestamp",
  "updated_at": "timestamp"
}

Ví dụ​

{
  "payment_id": "VPY-001",
  "assignment_id": "ASG-001",
  "vendor_id": "VND-001",
  "amount": 12500000,
  "due_date": "2026-02-01",
  "payment_date": null,
  "payment_method": "bank_transfer",
  "status": "pending",
  "notes": "50% deposit"
}

4. Computed Views & Reports​

4.1. Project Finance View​

Aggregate view cho từng scope

SELECT
  scope_id,
  contract_code,
  scope_code,
  service_type,
  -- Budget
  budget AS budget_allocated,
  SUM(campaign_execution.spend) AS spend_to_date,
  budget - SUM(campaign_execution.spend) AS budget_remaining,
  (SUM(campaign_execution.spend) / budget * 100) AS budget_usage_pct,

  -- KPI
  kpi_target,
  SUM(campaign_execution.kpi_achieved) AS kpi_achieved,
  kpi_target - SUM(campaign_execution.kpi_achieved) AS kpi_remaining,
  (SUM(campaign_execution.kpi_achieved) / kpi_target * 100) AS kpi_completion_pct,

  -- Profit
  revenue,
  (cost_estimated + SUM(vendor_payment.amount)) AS actual_cost,
  revenue - (cost_estimated + SUM(vendor_payment.amount)) AS profit,
  ((revenue - cost_estimated) / revenue * 100) AS margin_realized

FROM scope
LEFT JOIN campaign_execution ON scope.scope_code = campaign_execution.scope_code
LEFT JOIN vendor_assignment ON scope.scope_id = vendor_assignment.scope_id
LEFT JOIN vendor_payment ON vendor_assignment.assignment_id = vendor_payment.assignment_id
GROUP BY scope_id

4.2. Cashflow Forecast View​

Dự báo dòng tiền vào/ra

-- Dòng tiền ra (outgoing)
SELECT
  DATE_TRUNC('month', date) AS month,
  SUM(budget_allocated) AS ads_spend_forecast,
  SUM(vendor_payment.amount) AS vendor_cost,
  SUM(budget_allocated) + SUM(vendor_payment.amount) AS total_outgoing
FROM campaign_plan
LEFT JOIN vendor_payment ON campaign_plan.scope_id = vendor_payment.scope_id
WHERE date \>= CURRENT_DATE
GROUP BY month
ORDER BY month

-- Dòng tiền vào (incoming)
SELECT
  DATE_TRUNC('month', due_date) AS month,
  SUM(amount) AS revenue_forecast,
  SUM(CASE WHEN status = 'invoiced' THEN amount ELSE 0 END) AS invoiced,
  SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid
FROM payment_milestone
WHERE due_date \>= CURRENT_DATE
GROUP BY month
ORDER BY month

5. Indexes và Performance​

-- Contract
CREATE INDEX idx_contract_code ON contract(contract_code);
CREATE INDEX idx_contract_status ON contract(status);

-- Scope
CREATE INDEX idx_scope_contract ON scope(contract_id, contract_code);
CREATE INDEX idx_scope_code ON scope(contract_code, scope_code);
CREATE INDEX idx_scope_status ON scope(status);

-- Campaign Plan
CREATE INDEX idx_campaign_scope ON campaign_plan(scope_id);
CREATE INDEX idx_campaign_code ON campaign_plan(campaign_code);

-- Campaign Execution
CREATE INDEX idx_execution_date ON campaign_execution(date);
CREATE INDEX idx_execution_campaign ON campaign_execution(campaign_code);
CREATE INDEX idx_execution_scope ON campaign_execution(scope_code, contract_code);

-- Payment Milestone
CREATE INDEX idx_milestone_scope ON payment_milestone(scope_id);
CREATE INDEX idx_milestone_status ON payment_milestone(status, due_date);

-- Vendor Assignment
CREATE INDEX idx_assignment_scope ON vendor_assignment(scope_id);
CREATE INDEX idx_assignment_vendor ON vendor_assignment(vendor_id);

6. Data Integrity Rules​

6.1. Referential Integrity​

  • Scope CANNOT be deleted if có Campaign Plan hoặc Payment Milestone
  • Contract CANNOT be deleted if có Scope
  • Vendor CANNOT be deleted if có active Vendor Assignment

6.2. Business Logic Constraints​

  • Tổng budget của Scope <= Contract total_value
  • Tổng revenue của Milestone = Scope revenue
  • Vendor Payment amount <= Vendor Assignment cost
  • Campaign Plan budget <= Scope budget

6.3. Audit Trail​

Các bảng quan trọng cần có audit log:

  • Contract
  • Scope
  • Payment Milestone
  • Vendor Payment

Audit log schema:

{
  "audit_id": "string",
  "table_name": "string",
  "record_id": "string",
  "action": "enum (create, update, delete)",
  "old_value": "json",
  "new_value": "json",
  "changed_by": "string",
  "changed_at": "timestamp"
}

7. Data Migration Strategy​

7.1. Initial Data Load​

  1. Import existing contracts
  2. Create scopes for each contract
  3. Import historical campaign data from BigQuery
  4. Map campaign data to scopes
  5. Create payment milestones based on contract terms

7.2. Ongoing Sync​

  • BigQuery sync: Every 4 hours
  • Meta Ads API: Real-time via webhook (if available) or hourly
  • Manual data entry: Real-time via UI

8. Backup và Recovery​

8.1. Backup Strategy​

  • Full backup: Daily at 2 AM
  • Incremental backup: Every 6 hours
  • Transaction log: Continuous
  • Retention: 30 days rolling

8.2. Recovery Point Objective (RPO)​

  • Maximum acceptable data loss: 1 hour
  • Recovery Time Objective (RTO): 4 hours