Backend Lab 1-1 Event-based System 基本概念

  • 用 event log 的方式記錄「發生了什麼事」,例如發生了 order_createdpayment_confirmedorder_completed ,就能計算出現在 order 的狀態是 completed
  • event log 只會寫,不會改。

events table schema

  • type:發生了什麼事
  • payload:細節(json)
  • created_at
1
2
3
4
5
6
CREATE TABLE events (
id UUID PRIMARY KEY,
type TEXT NOT NULL,
payload JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Insert 幾筆 event

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
INSERT INTO events (id, type, payload)
VALUES (
gen_random_uuid(),
'order_created',
jsonb_build_object(
'order_id', 'order-123',
'amount', 100,
'currency', 'TWD'
)
);

INSERT INTO events (id, type, payload)
VALUES (
gen_random_uuid(),
'payment_confirmed',
jsonb_build_object(
'order_id', 'order-123',
'payment_type', 'credit_card',
'amount', 100,
'currency', 'TWD'
)
);

INSERT INTO events (id, type, payload)
VALUES (
gen_random_uuid(),
'order_created',
jsonb_build_object(
'order_id', 'order-456',
'amount', 150,
'currency', 'TWD'
)
);

Replay

用計算的方式算出想要的東西,例如訂單狀態。

1
2
3
4
5
6
7
8
-- 算出所有 order 是否已付款
-- bool_or(...) 是 aggregate function, 只要有一筆資料是 true, 結果就是 true
-- 因為 bool_or(...) 是 aggregate function, 其他要的欄位要出現在 GROUP BY 裡或者也是 aggregate
SELECT
payload->>'order_id' AS order_id,
bool_or(type = 'payment_confirmed') AS is_paid
FROM events
GROUP BY payload->>'order_id';

Insert 大量資料

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
INSERT INTO events (id, type, payload, created_at)
SELECT
gen_random_uuid(),
'order_created',
jsonb_build_object(
'order_id', 'order-' || gs,
'total_amount', (random() * 1000)::int
),
now() - (gs || ' seconds')::interval
FROM generate_series(1, 10000) AS gs;

INSERT INTO events (id, type, payload)
SELECT
gen_random_uuid(),
'payment_confirmed',
jsonb_build_object(
'order_id', payload->>'order_id'
)
FROM events
WHERE type = 'order_created'
AND random() < 0.5;