台灣個資法下電商重新同意機制設計：舊名單合規補件實作

1CREATE OR REPLACE VIEW v_consent_tiers AS
2SELECT
3  c.customer_id,
4  c.email,
5  CASE
6    WHEN cr.consent_id IS NOT NULL
7         AND cr.status = 'granted'
8         AND cr.policy_version IS NOT NULL
9      THEN 'T1_documented'
10    WHEN o.order_count > 0
11      THEN 'T2_contractual_no_marketing'
12    WHEN c.source_channel IN ('third_party_list','offline_paper','unknown')
13      THEN 'T3_unverifiable'
14    ELSE 'T4_dormant'
15  END AS tier,
16  o.last_order_at,
17  e.last_engaged_at
18FROM customers c
19LEFT JOIN consent_records cr
20  ON cr.customer_id = c.customer_id
21 AND cr.purpose_code = '040'
22 AND cr.status = 'granted'
23LEFT JOIN (
24  SELECT customer_id, COUNT(*) AS order_count, MAX(created_at) AS last_order_at
25  FROM orders WHERE status NOT IN ('cancelled','fraud') GROUP BY customer_id
26) o ON o.customer_id = c.customer_id
27LEFT JOIN (
28  SELECT customer_id, MAX(event_at) AS last_engaged_at
29  FROM email_events WHERE event_type IN ('open','click') GROUP BY customer_id
30) e ON e.customer_id = c.customer_id;

1CREATE TABLE consent_records (
2  consent_id      BIGINT AUTO_INCREMENT PRIMARY KEY,
3  customer_id     BIGINT NOT NULL,
4  subject_email   VARCHAR(320) NOT NULL,
5  purpose_code    VARCHAR(8)  NOT NULL COMMENT '法務部特定目的代號，如 040/090/148',
6  purpose_label   VARCHAR(120) NOT NULL,
7  channel         ENUM('email','sms','line','push','postal') NOT NULL,
8  status          ENUM('granted','withdrawn','expired','pending') NOT NULL,
9  policy_version  VARCHAR(16) NOT NULL,
10  policy_url      VARCHAR(512) NOT NULL,
11  capture_method  ENUM('web_form','checkout','reconsent_email','csr_phone','offline') NOT NULL,
12  capture_url     VARCHAR(512),
13  source_ip       VARBINARY(16),
14  user_agent      VARCHAR(512),
15  cross_border    JSON COMMENT '跨境傳輸對象與地區，如 ["SG:CRM","US:ESP"]',
16  evidence_hash   CHAR(64) COMMENT '同意當下頁面快照的 SHA-256',
17  created_at      TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
18  expires_at      TIMESTAMP NULL,
19  INDEX idx_lookup (customer_id, purpose_code, channel, created_at DESC)
20) ENGINE=InnoDB;

1SELECT cr.*
2FROM consent_records cr
3JOIN (
4  SELECT customer_id, purpose_code, channel, MAX(created_at) AS latest
5  FROM consent_records
6  GROUP BY customer_id, purpose_code, channel
7) m
8  ON m.customer_id = cr.customer_id
9 AND m.purpose_code = cr.purpose_code
10 AND m.channel = cr.channel
11 AND m.latest = cr.created_at
12WHERE cr.status = 'granted'
13  AND (cr.expires_at IS NULL OR cr.expires_at > NOW());

1curl -X POST "https://api.sendgrid.com/v3/mail/send" \
2  -H "Authorization: Bearer $SENDGRID_API_KEY" \
3  -H "Content-Type: application/json" \
4  -d '{
5    "personalizations": [{
6      "to": [{"email": "[email protected]"}],
7      "custom_args": {"campaign_type": "reconsent", "policy_version": "v3.0"}
8    }],
9    "from": {"email": "[email protected]", "name": "品牌隱私權中心"},
10    "subject": "請確認您的個人資料使用偏好",
11    "content": [{"type": "text/html", "value": "..."}],
12    "mail_settings": {"bypass_list_management": {"enable": false}},
13    "asm": {"group_id": 12345}
14  }'

1<form id="reconsent" method="post" action="/api/consent">
2  <input type="hidden" name="token" value="{{signed_token}}">
3  <input type="hidden" name="policy_version" value="v3.0">
4
5  <label><input type="checkbox" name="purposes[]" value="040:email"> 電子郵件行銷</label>
6  <label><input type="checkbox" name="purposes[]" value="040:sms"> 簡訊／LINE 行銷</label>
7  <label><input type="checkbox" name="purposes[]" value="090:profiling"> 個人化推薦</label>
8
9  <button type="submit">儲存偏好</button>
10  <a href="/api/consent/withdraw-all?token={{signed_token}}">全部拒絕並停止聯繫</a>
11</form>

1app.post('/api/consent', async (req, res) => {
2  const claims = verifyJwt(req.body.token); // 失敗即 401
3  const selected = new Set(req.body['purposes[]'] || []);
4  const allOptions = ['040:email', '040:sms', '090:profiling'];
5
6  const rows = allOptions.map(opt => {
7    const [purpose, channel] = opt.split(':');
8    return {
9      customer_id: claims.sub,
10      purpose_code: purpose,
11      channel,
12      status: selected.has(opt) ? 'granted' : 'withdrawn',
13      policy_version: req.body.policy_version,
14      policy_url: `https://yourstore.tw/privacy/${req.body.policy_version}`,
15      capture_method: 'reconsent_email',
16      capture_url: req.get('referer'),
17      source_ip: ipToBinary(req.ip),
18      user_agent: req.get('user-agent'),
19      cross_border: JSON.stringify(['SG:crm', 'US:esp']),
20      evidence_hash: await snapshotHash(req.body.policy_version)
21    };
22  });
23
24  await db('consent_records').insert(rows); // 未勾選的也寫入 withdrawn
25  await syncToCdp(claims.sub, rows);
26  res.redirect('/privacy/preferences-saved');
27});

1curl -X POST "https://a.klaviyo.com/api/profile-import/" \
2  -H "Authorization: Klaviyo-API-Key $KLAVIYO_KEY" \
3  -H "revision: 2024-10-15" \
4  -H "Content-Type: application/json" \
5  -d '{
6    "data": {
7      "type": "profile",
8      "attributes": {
9        "email": "[email protected]",
10        "properties": {
11          "pipa_consent_040_email": "granted",
12          "pipa_consent_040_sms": "withdrawn",
13          "pipa_policy_version": "v3.0",
14          "pipa_consent_at": "2026-01-15T09:22:41Z"
15        }
16      }
17    }
18  }'

1SELECT s.customer_id, s.sent_at, s.campaign_id
2FROM marketing_sends s
3LEFT JOIN v_active_consent c
4  ON c.customer_id = s.customer_id
5 AND c.purpose_code = '040'
6 AND c.channel = s.channel
7WHERE c.customer_id IS NULL
8  AND s.sent_at >= DATE_SUB(NOW(), INTERVAL 90 DAY);