Capstone — thiết kế data system cho một sản phẩm SaaS

TL;DR: Đây là bài capstone tổng hợp toàn khoá sql-database. Bạn nhận một bài toán SaaS quản lý task đa tổ chức với hai nhu cầu: phục vụ người dùng realtime (OLTP) và báo cáo phân tích cho khách hàng doanh nghiệp (OLAP). Nhiệm vụ: dẫn qua từng quyết định thiết kế — mô hình dữ liệu, schema, index, isolation, query plan, kiến trúc phân tích, vòng đời — mỗi quyết định nối về bài đã học trong khoá. Không có một "đáp án đúng duy nhất" — điều quan trọng là lập luận biện minh, không phải chọn đúng tên.

🎯 Đề bài — TaskFlow SaaS mở rộng

Startup của bạn xây TaskFlow SaaS — phần mềm quản lý task theo dự án, phục vụ nhiều tổ chức (multi-tenant). Sau 18 tháng, hệ thống đạt:

• 500 tổ chức (org), mỗi org từ 10 đến 2 000 người dùng.
• 12 triệu task trong DB, tăng thêm ~80 000 task/ngày.
• Peak concurrent users: 3 000, tạo/cập nhật task liên tục.
• Yêu cầu mới từ khách hàng doanh nghiệp: dashboard phân tích — tổng giờ hoàn thành task theo phòng ban/tháng, task quá hạn theo người dùng, burndown chart theo sprint. Query này cần quét toàn bộ lịch sử của org (vài triệu dòng mỗi org lớn).

Ràng buộc:

• Thao tác giao dịch (tạo task, cập nhật trạng thái, comment) phải dưới 50ms p95.
• Dashboard phân tích chấp nhận chờ tối đa 10 giây, nhưng không được làm chậm thao tác giao dịch.
• Schema phải cho phép org thêm custom field vào task mà không cần migration toàn bảng.
• Dữ liệu task đã hoàn thành quá 2 năm có thể archive (cold storage) nhưng vẫn truy vấn được khi cần.

🔍 I-P-O — Input / Process / Output

📦 Concept mapping — nối về khoá học

▶️ Phân rã thiết kế — 7 quyết định

Quyết định 1 — Mô hình dữ liệu: Relational hay gì khác?

Câu hỏi: Task có quan hệ với user, project, org, comment, tag — nên dùng mô hình nào?

TaskFlow có dữ liệu có cấu trúc rõ, quan hệ nhiều chiều: task thuộc project, project thuộc org, user thuộc org, task có nhiều assignee, comment, label. Truy vấn thường JOIN nhiều bảng và lọc theo nhiều điều kiện.

Biện minh chọn relational: mô hình quan hệ (xem M1 — relational model) cho phép JOIN linh hoạt mà không cần đoán trước mọi access pattern, đảm bảo integrity qua foreign key và constraint, và SQL chuẩn ANSI chuyển giao được giữa engine. Document model (MongoDB-style) hợp hơn khi dữ liệu dạng blob lồng nhau, ít JOIN — không phải trường hợp này.

Khi nào cân nhắc thêm: custom field của org (yêu cầu ràng buộc trên) là dữ liệu bán cấu trúc — có thể lưu trong cột JSONB/JSON thay vì thêm cột mới mỗi lần. Đây là pattern "hybrid": schema quan hệ cứng cho dữ liệu lõi, JSON column cho extension. Một số engine hỗ trợ index trên JSON path nên truy vấn custom field vẫn hiệu quả.

Quyết định 2 — Schema, constraint, normalization

Lược đồ lõi đề xuất:

-- Multi-tenant: moi bang co org_id de isolate du lieu
CREATE TABLE orgs (
  id          BIGINT PRIMARY KEY,
  slug        VARCHAR(100) NOT NULL UNIQUE,
  name        VARCHAR(255) NOT NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
  id         BIGINT PRIMARY KEY,
  org_id     BIGINT       NOT NULL REFERENCES orgs(id),
  email      VARCHAR(255) NOT NULL,
  name       VARCHAR(255) NOT NULL,
  dept       VARCHAR(100),
  UNIQUE (org_id, email)
);

CREATE TABLE projects (
  id         BIGINT PRIMARY KEY,
  org_id     BIGINT       NOT NULL REFERENCES orgs(id),
  name       VARCHAR(255) NOT NULL,
  archived   BOOLEAN      NOT NULL DEFAULT FALSE
);

CREATE TABLE tasks (
  id              BIGINT PRIMARY KEY,
  org_id          BIGINT       NOT NULL REFERENCES orgs(id),
  project_id      BIGINT       NOT NULL REFERENCES projects(id),
  title           VARCHAR(500) NOT NULL,
  status          VARCHAR(50)  NOT NULL DEFAULT 'todo',
  priority        SMALLINT     NOT NULL DEFAULT 2,
  assignee_id     BIGINT       REFERENCES users(id),
  effort_hours    NUMERIC(6,2),
  due_at          TIMESTAMP,
  completed_at    TIMESTAMP,
  custom_fields   JSONB,
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_archived     BOOLEAN      NOT NULL DEFAULT FALSE,
  CHECK (status IN ('todo','in_progress','review','done','cancelled'))
);

Biện minh các lựa chọn (nối M5):

• org_id trên mọi bảng: multi-tenant isolation — mọi query lọc theo org_id trước, tránh data leak giữa tenant.
• NUMERIC(6,2) cho effort_hours: tránh floating-point error khi tính tổng báo cáo — xem M5 data types.
• TIMESTAMP không timezone: lưu UTC, convert ở tầng application — nhất quán giữa mọi engine.
• custom_fields JSONB: đáp ứng yêu cầu custom field mà không migration — tradeoff: mất type safety cho field trong JSON, nhưng linh hoạt hơn EAV pattern (entity-attribute-value).
• Normalization: tasks ở 3NF — không lưu dept_name lặp lại trong tasks, chỉ lưu assignee_id rồi JOIN sang users khi cần. Xem M5 normalization.

Khi nào denormalize: nếu query "task kèm tên dept" chạy hàng nghìn lần/giây và JOIN làm chậm, có thể denormalize thêm cột dept_snapshot vào tasks — đánh đổi: nhanh hơn khi đọc, phải sync khi dept đổi tên. Xem M5 denormalization.

Quyết định 3 — Index cho query pattern

Các query pattern quan trọng:

-- Pattern A: load task cua 1 project (OLTP, ~100ms)
SELECT id, title, status, assignee_id
FROM tasks
WHERE org_id = ? AND project_id = ? AND is_archived = FALSE
ORDER BY created_at DESC;

-- Pattern B: task cua assignee trong 1 org (OLTP)
SELECT id, title, due_at
FROM tasks
WHERE org_id = ? AND assignee_id = ? AND status != 'done'
ORDER BY due_at ASC;

-- Pattern C: dem task qua han trong 1 org (OLTP light analytics)
SELECT COUNT(*) FROM tasks
WHERE org_id = ? AND due_at < CURRENT_TIMESTAMP AND status != 'done';

Index đề xuất (nối M6):

-- Index composite cho Pattern A: (org_id, project_id) la prefix chon loc chinh
CREATE INDEX idx_tasks_org_project ON tasks (org_id, project_id, is_archived, created_at DESC);

-- Index cho Pattern B: assignee + trang thai
CREATE INDEX idx_tasks_assignee ON tasks (org_id, assignee_id, status);

-- Index cho Pattern C: org + due_at
CREATE INDEX idx_tasks_overdue ON tasks (org_id, due_at) WHERE status != 'done';

Biện minh:

• Composite index (org_id, project_id): org_id luôn là điều kiện lọc đầu tiên (multi-tenant) nên phải là cột đầu trong index — cardinality cao, prune nhiều nhất.
• Partial index WHERE status != 'done': task đã xong chiếm phần lớn bảng theo thời gian, nhưng query overdue chỉ quan tâm task chưa xong — partial index nhỏ hơn, hiệu quả hơn.
• Không index tất cả: mỗi index tiêu thụ không gian đĩa và làm chậm mỗi INSERT/UPDATE (B-tree phải rebalance). Index custom_fields JSONB chỉ thêm khi có query pattern rõ ràng — không index phòng thủ.

Quyết định 4 — Isolation/transaction cho thao tác đồng thời

Tình huống 1 — Gán task: hai manager cùng gán cùng một task cho hai người khác nhau đồng thời.

-- Transaction gan task, can isolation de tranh race condition
BEGIN;
  SELECT id, assignee_id FROM tasks WHERE id = ? FOR UPDATE;
  UPDATE tasks SET assignee_id = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?;
COMMIT;

SELECT ... FOR UPDATE lấy exclusive lock trên dòng đó — manager thứ hai phải chờ transaction đầu hoàn thành. Đây là pessimistic locking, hợp khi contention cao trên một resource (xem M7 — ACID).

Tình huống 2 — Comment đồng thời: nhiều user comment cùng lúc trên một task. Mỗi INSERT vào bảng comments là độc lập — không cần lock đặc biệt. READ COMMITTED (default của hầu hết engine) là đủ: mỗi transaction thấy commit mới nhất khi đọc, tránh dirty read.

Tình huống 3 — Báo cáo nền: nếu cần chạy query tổng hợp trên DB OLTP (chưa có warehouse), dùng isolation REPEATABLE READ hoặc snapshot read để nhận consistent view mà không block write — đổi lại tăng áp lực MVCC. Đây là lý do cần tách OLAP (quyết định 6).

Nguyên tắc chọn isolation (nối M7):

Quyết định 5 — Query plan và tránh bad plan

Query có thể bị bad plan:

-- Query xem task sap het han, loc theo custom_fields (JSON)
SELECT id, title, due_at
FROM tasks
WHERE org_id = 42
  AND custom_fields->>'priority_tag' = 'urgent'
  AND due_at < CURRENT_TIMESTAMP + INTERVAL '7 days';

Nếu planner không có index trên custom_fields->>'priority_tag', nó sẽ full scan toàn bộ task của org 42 — có thể vài triệu dòng.

Cách đọc và cải thiện (nối M8):

Kiểm tra plan qua EXPLAIN (cú pháp chuẩn, mọi engine hỗ trợ). Dấu hiệu bad plan: Seq Scan trên bảng lớn, rows estimated lệch xa rows actual, nested loop join với bảng lớn ở outer side.

Fix options:

1. Tạo index trên expression JSON nếu query này chạy thường xuyên.
2. Nếu priority_tag chỉ có vài giá trị (cardinality thấp), filter due_at trước rồi filter JSON sau — sắp xếp điều kiện theo selectivity giảm dần.
3. Cập nhật statistics định kỳ (ANALYZE hoặc tương đương) để planner có ước lượng cardinality chính xác.

Nguyên tắc: planner tối ưu dựa trên cost model và statistics — nếu statistics cũ hoặc thiếu index, planner chọn sai plan dù query đúng. Bảo trì statistics là trách nhiệm vận hành, không chỉ thiết kế.

Quyết định 6 — Tách OLTP/OLAP: khi nào và như thế nào

Vấn đề: dashboard phân tích "tổng giờ theo phòng ban/tháng" cần quét toàn bộ lịch sử task của org. Với 12 triệu dòng và tăng 80k/ngày, query này chạy thẳng trên DB OLTP sẽ chiếm I/O và cache, làm chậm mọi thao tác giao dịch — đúng như kịch bản đầu bài M10.

Kiến trúc đề xuất:

flowchart LR
  subgraph OLTP["He thong giao dich (TaskFlow app)"]
    APP["TaskFlow<br/>App Server"] --> DB[("PostgreSQL<br/>OLTP")]
  end
  DB -->|"ETL/ELT dinh ky<br/>(moi 15 phut - 1 gio)"| WH[("Data Warehouse<br/>column-oriented")]
  subgraph OLAP["He thong phan tich"]
    WH --> BI["Dashboard<br/>Analytics API"]
    BI --> CUSTOMER["Khach hang<br/>doanh nghiep"]
  end

Giai đoạn triển khai theo quy mô:

TaskFlow hiện ở 12M task → giai đoạn 3: cần warehouse riêng.

ETL đơn giản (khái niệm):

• Mỗi 15 phút đến 1 giờ: extract task updated trong khoảng, transform (denormalize org/dept/user vào 1 bảng flat), load vào warehouse.
• Dashboard đọc warehouse → không đụng OLTP DB.
• Đổi lại: dữ liệu phân tích trễ 15 phút đến 1 giờ so với thực tế — chấp nhận được với báo cáo kinh doanh.

Quyết định 7 — Vòng đời dữ liệu và schema evolution

Archive dữ liệu cũ (nối M9):

Task hoàn thành quá 2 năm có thể chuyển sang "cold tier":

• Partition bảng tasks theo năm: tasks_2023, tasks_2024, tasks_2025. Bảng cold ít truy vấn hơn → ít cần caching hot.
• Hoặc flag is_archived = TRUE + index partial loại trừ archived khỏi query thường ngày (đã làm ở Quyết định 3).

Schema evolution không downtime (nối M9):

Khi muốn thêm cột sprint_id vào tasks:

1. Thêm cột nullable (ALTER TABLE tasks ADD COLUMN sprint_id BIGINT): backward compatible, row cũ có sprint_id = NULL, code cũ không quan tâm cột này.
2. Deploy code mới đọc/ghi sprint_id.
3. Backfill dần (không một lần toàn bảng → tránh lock lâu).
4. Sau khi backfill xong và code cũ retire, có thể thêm NOT NULL constraint hoặc đặt default.

Pattern expand-contract (thêm rộng, thu dần sau) giữ system up trong suốt quá trình migration. Không bao giờ DROP COLUMN hoặc đổi tên cột trực tiếp khi có code đang đọc cột đó.

✅ Lời giải tham khảo

Thiết kế trên không phải đáp án duy nhất. Dưới đây là tóm tắt biện minh cho mỗi quyết định và những điểm người khác có thể chọn khác:

Mô hình dữ liệu: Relational hợp với TaskFlow vì quan hệ rõ, JOIN cần thiết, và cần integrity đảm bảo bởi DB. Người khác có thể chọn document DB nếu custom field là use case trung tâm và ít JOIN — đều có lý do chính đáng, miễn là biện minh rõ.

Schema: NUMERIC cho tiền/giờ, TIMESTAMP UTC, org_id mọi bảng, JSONB cho extension — đây là pattern phổ biến cho SaaS multi-tenant. Tradeoff: JSONB phức tạp hơn để query so với cột riêng, nhưng linh hoạt hơn cho custom field.

Index: Composite với org_id đầu tiên, partial index cho "active only" — tránh index tất cả mọi cột. Index có chi phí write; chỉ thêm khi có query pattern cụ thể chứng minh.

Isolation: READ COMMITTED mặc định là đúng cho phần lớn thao tác. FOR UPDATE cho thao tác tranh chấp resource. Tránh SERIALIZABLE toàn diện nếu không cần — overhead cao.

Query plan: đọc plan là kỹ năng vận hành, không chỉ thiết kế. Statistics cũ gây bad plan — ANALYZE định kỳ là best practice.

OLTP/OLAP: tách là bắt buộc khi query phân tích làm chậm giao dịch. Warehouse với ETL 15 phút có độ trễ — chấp nhận được cho báo cáo kinh doanh. Nếu cần near-realtime analytics, có thể dùng streaming ETL (CDC) — thuộc track nâng cao.

Vòng đời: partial index + archive flag đủ cho giai đoạn hiện tại. Partition theo thời gian là bước tiếp theo khi bảng vượt vài trăm triệu dòng.

🎓 Điều bạn vừa làm được

Sau capstone này, bạn đã thực hành:

• Đọc yêu cầu sản phẩm và phân rã thành 7 quyết định thiết kế dữ liệu độc lập.
• Chọn mô hình dữ liệu có lý do, không phải theo thói quen.
• Thiết kế schema đúng chuẩn ANSI SQL với constraint, normalization, và extension point.
• Chọn index theo query pattern thực tế, không index phòng thủ.
• Chọn isolation level phù hợp với mức độ contention của từng thao tác.
• Đọc tín hiệu bad plan và biết cách cải thiện.
• Phân biệt khi nào OLTP cần tách OLAP và vì sao.
• Áp dụng expand-contract để schema evolution không downtime.

🚀 Hướng mở rộng

Các chủ đề nâng cao nằm ngoài khoá này, nhưng là bước tự nhiên tiếp theo:

Horizontal sharding: khi 1 DB node không đủ — chia data theo org_id hash sang nhiều node. Phức tạp hơn nhiều: cross-shard JOIN, distributed transaction, re-sharding. Thuộc track "Database Internals nâng cao".

Real-time analytics (streaming): thay ETL batch bằng CDC (change data capture) — mỗi write trên OLTP được stream sang warehouse ngay lập tức, độ trễ dưới 1 giây. Cần Kafka/Debezium hoặc tương đương. Thuộc track "Data Engineering".

Tiered storage / cold archive: chuyển task cũ sang object storage (S3-compatible) + query engine đọc từ đó (Parquet format). Chi phí lưu trữ giảm nhiều lần. Thuộc track "Data Engineering".

📚 Deep Dive

🔗 Liên hệ các bài khác trong khoá

• M1 — Relational model: nền tảng tư duy mô hình quan hệ mà mọi quyết định thiết kế schema đều dựa vào.
• M5 — Data types & constraints: chọn kiểu dữ liệu đúng (NUMERIC vs FLOAT, TIMESTAMP), normalization, denormalization tradeoff.
• M6 — Vì sao cần index: chi phí index, B-tree, khi nào KHÔNG nên index — nền tảng cho Quyết định 3.
• M7 — ACID deep: bốn tính chất ACID và ý nghĩa thực tế — nền tảng cho Quyết định 4.
• M8 — Từ SQL đến kế hoạch thực thi: parse/plan/execute pipeline, đọc EXPLAIN — nền tảng cho Quyết định 5.
• M9 — Vòng đời dữ liệu: hot/cold tiers, schema evolution expand-contract — nền tảng cho Quyết định 7.
• M10 — OLTP vs OLAP: vì sao query phân tích làm chậm DB giao dịch và cách tách — nền tảng cho Quyết định 6.

Tự kiểm tra (reflective)

Bài tiếp theo: Bạn đã hoàn thành khoá SQL & Database — Tư tưởng & Nguyên lý. Quay lại dashboard khoá học để xem lại các module hoặc tiếp tục track nâng cao.