はじめに
今回は、前回DynamoDB用に作成したテーブル設計を、Amazon Aurora PostgreSQL（Serverless v2） で設計し直してみました。
リレーショナルデータベースに設計し直すことで、将来のイベント種別追加・スタンプ追加・管理画面実装などに柔軟に対応できるようになりました。
概要
- DB エンジン: Amazon Aurora PostgreSQL（Serverless v2）
- 接続方式: RDS Data API（HTTPS / IAM 認可）→Lambdaを非VPCのまま接続する想定
- 設計方針: 第3正規形を基本としつつ、共通コア + イベント種別ごとの拡張テーブルに分割
- ORM/クライアント: AWS SDK @aws-sdk/client-rds-data、またはData API対応の
Drizzle ORMを想定
セキュリティ
-
line_user_idはLIFF ID Tokenをサーバー側で検証した結果のsubクレームからのみ取得する（クライアントから受け取った値をそのまま使わない） - 保存時暗号化: AuroraはAWS KMS Customer Managed Key (CMK) で暗号化する
- 認証情報: DB認証情報はSecrets Manager（CMK 暗号化 + 自動ローテーション）で管理し、Data API には
Secret ARNを渡す - IAM: アプリ実行ロールはData API（
rds-data:ExecuteStatement等）+ 対象SecretのGetSecretValueのみ。
要件サマリ
| 要件 | 設計反映 | |
|---|---|---|
| 1 | LIFFのスタンプラリー機能 |
events(type=stamp_rally) + stamps + user_stamps
|
| 2 | LineUserId を保持し後から一覧取得 |
line_users テーブル（line_user_id に UNIQUE） |
| 3 | スタンプ（今後追加あり想定） |
stamps に行を追加するだけ（スキーマ変更不要） |
| 4 | 各スタンプの獲得状態を保持 |
user_stamps（参加者 × スタンプ の獲得レコード） |
| 5 | スタンプ獲得時の Flex Message 内容を保持 |
stamps.flex_message_content（jsonb） |
| 6 | 獲得時間 / Flex 送信完了状態を保持 |
user_stamps.acquired_at / flex_message_sent / ..._sent_at
|
| 7 | 新イベント種別への対応（仕様は未確定） |
events.type で種別分岐 + 種別別拡張テーブルを後付け可能 |
| 8 | 管理画面の実装に対応（現状は不要） | 正規化 + 集計ビュー 管理画面の書き込み機能をリリースする前に admin identity/audit log/change history(監査・トラブル対応用)を必須化する（後付けでは過去の変更履歴を復元できないため） |
ER図
erDiagram
events ||--o{ event_participants : "has"
events ||--o{ stamps : "defines"
line_users ||--o{ event_participants : "joins"
event_participants ||--o{ user_stamps : "earns"
stamps ||--o{ user_stamps : "is earned as"
event_participants ||--o{ stamp_rally_event_completion_claims : "completes (per generation)"
events {
bigint id PK
text code UK
text type
text name
timestamptz starts_at
timestamptz ends_at
text status
timestamptz created_at
timestamptz updated_at
}
line_users {
bigint id PK
text line_user_id UK
timestamptz created_at
timestamptz updated_at
}
event_participants {
bigint id PK
bigint event_id FK
bigint line_user_id FK
timestamptz registered_at
timestamptz created_at
timestamptz updated_at
}
stamps {
bigint id PK
bigint event_id FK
text code
text name
int display_order
boolean is_active
jsonb flex_message_content
timestamptz created_at
timestamptz updated_at
}
user_stamps {
bigint id PK
bigint event_id FK
bigint event_participant_id FK
bigint stamp_id FK
timestamptz acquired_at
boolean flex_message_sent
timestamptz flex_message_sent_at
timestamptz created_at
timestamptz updated_at
}
stamp_rally_event_completion_claims {
bigint id PK
bigint event_participant_id FK
text stamp_set_signature
int required_count
timestamptz claimed_at
}
テーブル定義DDL（Data Definition Language）
0: updated_at自動更新トリガー
1: events（イベントマスタ）
2: line_users（全イベントで一意のLINEユーザー）
3: event_participants（各イベント参加者＝LINEユーザー x 特定イベントの関連づけ）
4: stamps（スタンプマスタ）
5: user_stamps（スタンプ獲得状態＝各イベント参加者 x 特定スタンプの関連づけ）
6: stamp_rally_event_completion_claims（スタンプ全コンプリートの一意確定）
※スタンプラリーイベント参加者専用テーブル
0: updated_at自動更新トリガー
行更新時にupdated_atを自動的に現在時刻へ書き換える共通関数です。
updated_atをアプリケーションのコードで毎回書く運用も可能ですが、
- UPDATE文を書く箇所すべてで指定しないと更新が漏れる
- 複数のサービス・言語・経路（管理画面、バッチ、手動SQL等）から更新され
る場合、全部で揃えるのは現実的でない - （特に）手動でpsqlから直接UPDATEしたときに更新されない
といった問題が起こり得ます。
DB側のトリガーに寄せておけば、どんな経路でUPDATEされても必ずupdated_atが正しく入ることが保証されます。
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
1: events（イベントマスタ）
開催するイベント（現状はスタンプラリーのみだが、将来は別種別も追加可能）を1行＝1イベントで管理する親マスタテーブルです。
CREATE TABLE events (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code TEXT NOT NULL, -- 外部公開用の安定したコード (例: 'stamp-rally-202606')
type TEXT NOT NULL, -- イベント種別 (下記のCHECK参照)
name TEXT NOT NULL, -- 管理画面などで表示用の名称
starts_at TIMESTAMPTZ, -- 公開開始日時 (任意)
ends_at TIMESTAMPTZ, -- 公開終了日時 (任意)
status TEXT NOT NULL DEFAULT 'draft', -- draft / active / ended / archived
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT events_code_uk UNIQUE (code),
-- 種別は CHECK で表現 (新種別の追加はCHECKへ値を足すだけ。ネイティブENUMより柔軟)
-- 現状はスタンプラリーのみ。新イベント種別が決まったら値を追加する
CONSTRAINT events_type_chk CHECK (type IN ('stamp_rally')),
CONSTRAINT events_status_chk CHECK (status IN ('draft', 'active', 'ended', 'archived')),
-- 開始・終了が両方ある場合は開始 < 終了を強制 (管理画面の入力ミスをDBで弾く)
CONSTRAINT events_period_chk CHECK (starts_at IS NULL OR ends_at IS NULL OR starts_at < ends_at)
);
CREATE TRIGGER trg_events_updated_at
BEFORE UPDATE ON events
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CONSTRAINTは「データの整合性ルールに名前を付ける仕組み」です。
名前を付けることでエラー原因が分かりやすくなります。
また、これらの整合性ルールはDBが不正なデータの登録・更新を自動で防いでくれます。
bigintとintの違いは
PostgreSQLにおけるintとbigintの違いは、主に格納できる数値の範囲（とそれに伴うバイト数） です。
どちらも整数で、計算や比較の使い方は同じです。
bigintはintに比べ、桁あふれ（オー バーフロー）するリスクがない分、バイト数が多くなります。
| 型 | バイト数 | 範囲 | 別名 |
|---|---|---|---|
| smallint | 2 | 約-3.2万〜+3.2万 | int2 |
| int(integer) | 4 | 約-21億〜+21億（±2,147,483,647） | int4 |
| bigint | 8 | 約-922京〜+922京（±9.2×10¹⁸） | int8 |
後からint→bigintに広げる移行は全関連カラムを巻き込んで非常に重いため、将来の拡張が予測できない場合は最初からbigintにしておくのが推奨されています。
なぜstringではなくtext?
PostgreSQLにはstringという型は存在しません。文字列型は次の3つです。
| 型 | 説明 |
|---|---|
| text | 長さ上限なしの可変長文字列 |
| varchar(n) | 最大 n 文字までの可変長文字列 |
| char(n) | 固定長 n 文字（足りない分は空白で埋める） |
PostgreSQLではtextとvarcharは内部的に全く同じ実装で、格納方法も処理速度も同一です。
性能差が無いため、「長さ制限が業務要件として必要」な列のみvarchar(n)、それ以外はtextにしておくのが推奨されています。
※MySQL（Aurora MySQL）に切り替える場合はTEXTは扱いが重いため注意が必要です。
PK, FK, UKの違い
すべて「制約（constraint）」であり、必須というわけではありませんが、実務では多くの場合使用されています。
PK = Primary Key（主キー）
「その1行を指す代表の鍵」です。1つのテーブルに1つだけ設定でき、自動的に「重複
不可 + NULL不可」になります。
FK = Foreign Key（外部キー）
「別テーブルの行を指すポインタ」です。参照先に存在しない値は入れられないようにDBが守ってくれます。
（例：「参加者テーブル」内に「イベントIDを指すFK」がある場合、存在しないイベントの参加者を作れない）
また、ON DELETE CASCADEを付ければ、親(イベントID)を消すと子も自動で消すことが可能になります。
UK = Unique Key（一意キー）
「この列（または列の組み合わせ）の値は重複してはいけない」というルールで
す。PKと違い、1テーブルに複数設定でき、NULLも基本許容されます。
「line_user_id」や「event_id」等に使用されます。
| Key | 役割 | 個数 | 重複 | NULL |
|---|---|---|---|---|
| PK | 行を一意に特定する代表ID | テーブルに1つ | 不可 | 不可 |
| UK | 特定の値の重複を禁止 | 複数OK | 不可 | 基本OK |
| FK | 他テーブルの行を参照 | 複数OK | 可（多対一なので） | 可（任意参照なら） |
2: line_users（全イベントで一意のLINEユーザー）
LINEユーザーを全イベント横断で1人＝1行に集約する、ユーザーIDの一元管理テーブルです。
CREATE TABLE line_users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
line_user_id TEXT NOT NULL, -- 検証済みID Tokenのsubクレーム由来 (PII)
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT line_users_line_user_id_uk UNIQUE (line_user_id)
);
CREATE TRIGGER trg_line_users_updated_at
BEFORE UPDATE ON line_users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
なぜline_usersテーブルが別で必要なのか？
1. ユーザーは「イベント横断で1人1行」だから
line_usersはline_user_idにUNIQUEが張ってあり、同じ人は何イベントに参加してもDB全体で1行です。一方event_participantsは「誰がどのイベントに参加したか」の橋渡しテーブルであり、[ユーザー×イベントの組み合わせ]ごとに1行増えます。
もしline_usersを作らず参加レコードにline_user_id(PII)を直接持たせると、同じ人がイベントに参加するたびに同じline_user_id文字列が何行も複製されます。テーブルを分けることでPIIの重複保存をなくし、ユーザーという実体を1か所に集約できます。
2. PIIの一元管理 / GDPR削除点になるから
line_user_idは「個人を継続識別できるID=PII」として扱う方針です。これを1テーブルに集約しておくと、
- 削除が1行で済む：
DELETE FROM line_users WHERE line_user_id = $1;
だけでON DELETE CASCADEにより配下のevent_participants → user_stamps → event_completion_claimsまで一括削除されます。（event_participantsテーブル参照）
もしPIIが各参加レコードに散らばっていると、削除漏れのリスクが上がります。 - また、暗号化・マスキングの対象として管理しやすくなります
3: event_participants（各イベント参加者＝LINEユーザー x 特定イベントの関連づけ）
「どのユーザーが・どのイベントに参加したか」を1行で表す、line_usersとeventsを多対多で結ぶ中間（橋渡し）テーブルです。
CREATE TABLE event_participants (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
event_id BIGINT NOT NULL REFERENCES events(id) ON DELETE RESTRICT, -- イベント終了は status='archived' で表現。物理削除での証跡消失を防ぐ
line_user_id BIGINT NOT NULL REFERENCES line_users(id) ON DELETE CASCADE, -- GDPR 削除はline_users起点。RESTRICTでないためevent_idをRESTRICTにしても削除フローは壊れない
registered_at TIMESTAMPTZ NOT NULL DEFAULT now(), -- そのイベントへの初回登録日時
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- 同一ユーザーが同一イベントに二重登録されないことを保証 (冪等登録の要)
CONSTRAINT event_participants_uk UNIQUE (event_id, line_user_id),
-- user_stampsからの複合外部キー (id, event_id) の参照先。
-- idは単独でPKだが、複合FKの参照先には対応するUNIQUE制約が必要なため明示する
CONSTRAINT event_participants_id_event_uk UNIQUE (id, event_id)
);
-- 管理画面「イベント別 参加者一覧（登録日時順）」用（現状は不要）
-- 管理画面を実装する際は、深いページの OFFSET 非効率と同時刻登録の順序不安定を避けるため、
-- このINDEXを (event_id, registered_at, id) に拡張し、keyset paginationを採用する。
-- 現時点では管理画面が無いため索引は最小構成のままとする。
-- CREATE INDEX idx_event_participants_event_registered
-- ON event_participants (event_id, registered_at);
CREATE TRIGGER trg_event_participants_updated_at
BEFORE UPDATE ON event_participants
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
複合外部キーは、複数の列の組み合わせが正しいことをDBで保証するために使います。
今回のケースでは、「イベント参加者」と「特定のイベント」の組み合わせが矛盾しないことを保証しています。
CREATE INDEX
PostgreSQLがevent_participantsテーブルとは別に、(event_id, registered_at) の順に並んだ検索用のデータ構造（B-tree）を裏で維持します。これにより、特定のクエリが高速になります（INSERTするたびに、テーブル本体だけでなくこの索引にもエントリを追加・整列する必要があるため、書き込みが少し遅くなるなどのデメリットもあります）
なぜわざわざUNIQUE制約を追加しているか
CONSTRAINT event_participants_id_event_uk UNIQUE (id, event_id)
PostgreSQLでは、外部キーの参照先カラムにはUNIQUE（またはPK）制約が必須というルールがあります。
idは単独でPKなので、それ自体は一意なのですが、 (id, event_id)というペアの組み合わせを参照先にするには、「そのペアに対するUNIQUE制約」が形式上必要になります。
4: stamps（スタンプマスタ）
各イベントで集められるスタンプ1種類＝1行を定義するマスタテーブルです。
CREATE TABLE stamps (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
event_id BIGINT NOT NULL REFERENCES events(id) ON DELETE RESTRICT, -- イベント終了は status='archived' で表現。物理削除での証跡消失を防ぐ
code TEXT NOT NULL, -- イベント内で安定したコード (例: '001')
name TEXT NOT NULL, -- 表示名 (例: '第1スタンプ')
display_order INT NOT NULL DEFAULT 0, -- 表示順 ("order" は予約語のため display_order)
is_active BOOLEAN NOT NULL DEFAULT true, -- 非公開化したい場合にfalse
flex_message_content JSONB, -- 獲得時に送るFlex Message JSONをそのまま格納
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- 同一イベント内でコード重複を禁止
CONSTRAINT stamps_event_code_uk UNIQUE (event_id, code),
-- user_stamps からの複合外部キー (id, event_id) の参照先 (上記event_participantsと同様)
CONSTRAINT stamps_id_event_uk UNIQUE (id, event_id)
);
-- スタンプ一覧 (表示順) 取得用。display_orderは重複可(DEFAULT 0)のためidをタイブレークに含め安定ソートを担保
-- CREATE INDEX idx_stamps_event_order ON stamps (event_id, display_order, id);
CREATE TRIGGER trg_stamps_updated_at
BEFORE UPDATE ON stamps
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
5: user_stamps（スタンプ獲得状態＝各イベント参加者 x 特定スタンプの関連づけ）
user_stampsは「参加者の所属イベント」と「スタンプの所属イベント」が一致することをDBレベルで強制します。
CREATE TABLE user_stamps (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
event_id BIGINT NOT NULL, -- 参加者・スタンプ双方の所属イベント (整合性担保用に非正規化)
event_participant_id BIGINT NOT NULL,
stamp_id BIGINT NOT NULL,
acquired_at TIMESTAMPTZ NOT NULL DEFAULT now(), -- 獲得時刻
flex_message_sent BOOLEAN NOT NULL DEFAULT false, -- Flex Message送信完了フラグ
flex_message_sent_at TIMESTAMPTZ, -- 送信完了時刻
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- 二重獲得防止: 同じ参加者が同じスタンプを2行持てない
CONSTRAINT user_stamps_uk UNIQUE (event_participant_id, stamp_id),
-- 複合外部キーで「参加者の所属イベント」と「スタンプの所属イベント」の一致をDBレベルで強制する。
-- event_idを介して両FKを張ることで、別イベントの参加者×スタンプの組み合わせは挿入できない
CONSTRAINT user_stamps_participant_event_fk
FOREIGN KEY (event_participant_id, event_id)
REFERENCES event_participants (id, event_id) ON DELETE CASCADE,
-- stamp側はRESTRICT。スタンプの物理削除で獲得履歴(証跡)が巻き添え消失するのを防ぐ
-- (非公開化はis_active=falseで表現する)
CONSTRAINT user_stamps_stamp_event_fk
FOREIGN KEY (stamp_id, event_id)
REFERENCES stamps (id, event_id) ON DELETE RESTRICT,
-- 送信フラグと送信時刻の整合を強制 (未送信なら時刻NULL / 送信済みなら時刻必須)
CONSTRAINT user_stamps_flex_sent_chk CHECK (
(flex_message_sent = false AND flex_message_sent_at IS NULL)
OR
(flex_message_sent = true AND flex_message_sent_at IS NOT NULL)
)
);
-- 「このスタンプを獲得した参加者」逆引き (管理集計用)
-- CREATE INDEX idx_user_stamps_stamp ON user_stamps (stamp_id);
CREATE TRIGGER trg_user_stamps_updated_at
BEFORE UPDATE ON user_stamps
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
6: stamp_rally_event_completion_claims（スタンプ全コンプリートの一意確定）
このテーブルはスタンプラリー(type='stamp_rally')の完了確定だけに使用します。
他種別のイベントは、その種別固有の<種別>_*拡張テーブルを別途新設する想定です。
参加者が、「その時点でactiveなスタンプ集合を全獲得した」という事実を、完了世代（= activeスタンプ集合）ごとに1行で確定させます。
また、完了通知(Flex/プッシュ)を「その世代の初回完了時に一度だけ」発火させるよう制御する役割もあります。
スタンプは後から追加や入れ替えされることが想定され、その都度全スタンプ獲得の達成条件は変わるため必要になります。
CREATE TABLE stamp_rally_event_completion_claims (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
event_participant_id BIGINT NOT NULL REFERENCES event_participants(id) ON DELETE CASCADE,
-- 完了確定時点のactiveスタンプid集合（昇順連結）。例: "12,15" / 入れ替え後 "15,18"
-- 集合が変われば別世代になり、件数が同じ「入れ替え」でも衝突せず別世代として扱える
stamp_set_signature TEXT NOT NULL,
required_count INT NOT NULL, -- その世代のactiveスタンプ総数（監査・表示用。signatureのid個数と一致する冗長値）
claimed_at TIMESTAMPTZ NOT NULL DEFAULT now(), -- その世代の完了確定時刻
-- 「同一参加者 × 同一完了世代」は1行のみ（競合・リトライでも世代あたり通知1回に限定）
CONSTRAINT stamp_rally_event_completion_claims_uk UNIQUE (event_participant_id, stamp_set_signature),
-- 完了世代は必ず1件以上のactiveスタンプを伴う。管理SQL/移行/バッチからの不正値(<=0)をDBで弾く
CONSTRAINT stamp_rally_event_completion_claims_required_count_chk CHECK (required_count > 0)
);
アクセスパターンの注意点
スタンプマスタ一覧取得時は、
SELECT
id,
code,
name,
display_order,
is_active
FROM
stamps
WHERE
event_id = $1
AND is_active
ORDER BY
display_order,
id;
などのようにし、
・データが大きなflex_message_contentは一覧で返さない
・display_order重複時はidで安定ソートさせる
などといった工夫が必要になります。
※Aurora PostgreSQLのData APIは1MB（結果セット）、1レコードあたり64KBの制限があるため
今回は以上になります！