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

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

events table schema

• type：發生了什麼事
• payload：細節（json）
• created_at

CREATE TABLE events (
  id UUID PRIMARY KEY,
  type TEXT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Insert 幾筆 event

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

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

-- 算出所有 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 大量資料

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;