有効期限付きポイントシステムの要求定義と設計

アクター#

• ユーザ
• 経理
• システム管理者

機能要求#

• 基本機能

  • ユーザに対してポイントを加算、減算できるシステム。
  • 加算に対して、任意の有効期限をつける。
  • 有効期限が近いポイントから消費していく。（獲得順の FIFO ではなく、期限が近い順 = FEFO: First-Expire-First-Out である点に注意）

• 運用

  • 減算を取り消すときには、減算前のポイントの有効期限を復元する。
  • ある時点の残高を算出できる。
  • ポイントの整合性をチェックできる。

• ユーザ向け機能

  • 有効期限ごとに残高を表示できる。
  • 現在の残高を取得できる。

• 経理

  • できるだけオンラインで処理できるようにする
  • 特定時点のBS（バランスシート）の算出
  • 特定時点のPL（プロフィット/ロス）の算出
  • BSとPLの一致

• システム

  • 長期間運用しても遅くならないクエリーで取り出せる。
  • 減算、加算の整合性を検算できる。（＝BSとPLを作っておいて、整合性を検証）

ユースケースの例#

1. 2022年6月まで有効の100ポイントを加算
2. 2022年7月まで有効の100ポイントを加算
3. 150ポイントを消費。（1から100ポイント、2から50ポイントを引く）

1. トランザクションモデル#

方向性は、入金と出金の履歴は最低限取らなければいけないのでそれぞれの履歴をエンティティとして表現します。

Userエンティティは、ユーザ情報のマスタです。

DepositHistoryは入金の履歴を表現するエンティティです。amountにはポイントの額を入れます。used_amountはその入金に対して利用済みの金額を保存するためのカラムです。デフォルトは0になります。 expiration_dateはその入金の有効期限の日付を入力します。

WithdrawHistoryは出金の履歴を表現するエンティティです。

classDiagram
User "1" --> "0..*" DepositHistory
User "1" --> "0..*" WithdrawHistory
class User
class DepositHistory {
-int amount
-int used_amount
-datetime expiration_date
-datetime created_at
}
class WithdrawHistory {
-int amount
-datetime created_at
}

入金と出金を1つのエンティティで表現しても良かったのですが、used_amountとexpiration_dateの2つのアトリビュートが出金には非従属なので正規化してエンティティで分けました。

このアプローチの良い点は、テーブルが2つで済むのでシンプルです。悪い点は、出金と入金の紐付けが行えないので出金の取り消しができません。

2. 会計的アプローチ#

PLとBS (有効期限ごと)を分離して保存する。ユーザや管理者が見たいのは主にPLかBLのどちらかです。なので、ユーザ側の要求に近い状態でデータ自体も保存してしまえば集計も楽というアプローチです。

classDiagram
User "1" --> "0..*" Balance
User "1" --> "0..*" History
class User
class Balance {
-int amount
-int expiration_date
}
class History {
-int amount
-int type
-datetime created_at
}

良い点は集計が容易です。悪い点は、1つ目と同じように入金と出金の紐付けがないので出金の取り消しが行なえません。

3. オブジェクト指向#

入金、出金を個別に扱います。入金に対しての出金を厳密に関連付けることで取り消しを行えるようにします。

データとしてはPLを持ちます。BSはこのPLのデータを使って集計して出すか、参照頻度が高いのであれば別途BS用のテーブルを用意しても良いと思います。

BS用のテーブルを用意すると、集計は行いやすいですがその反面、PLの更新があるたびにBSのテーブルの更新も行う必要が出てきます。システムのユースケース頻度やトラフィックの多さによって使い分けるのが良いと思います。

classDiagram
User "1" --> "0..*" Deposit
User "1" --> "0..*" Withdraw
Deposit "1" --> "0..*" DepositWithdraw
Withdraw "1" --> "0..*" DepositWithdraw
class User
class Deposit {
-int amount
-date expiration_date
-datetime created_at
}
class DepositWithdraw {
-int amount
}
class Withdraw {
-int amount
-datetime created_at
}

ユースケース例（150pt 消費）で FEFO を適用すると、DepositWithdraw は以下のように作られる。

flowchart TD
D1["Deposit A\n+100pt / 期限: 2022-06-30"]
D2["Deposit B\n+100pt / 期限: 2022-07-31"]
W["Withdraw\n-150pt"]
DW1["DepositWithdraw\namount=100\n（Deposit A を全消費）"]
DW2["DepositWithdraw\namount=50\n（Deposit B から一部消費）"]

D1 -->|"1対多"| DW1
D2 -->|"1対多"| DW2
W -->|"1対多"| DW1
W -->|"1対多"| DW2

この手法の良い点は、出金の取り消しも容易に行なえます。悪い点はテーブルが3つになるのでデータの管理が煩雑です。出金時のクエリーを複数回発行する必要があり、計算量が収束しないので処理が遅くなる可能性があります。

メリット・デメリット#

Matrixで整理しておきます。

• n: とあるユーザに対する関連するテーブルのレコード数
• D: 入金（Deposit）数、W: 出金（Withdraw）数
• C: DepositWithdraw のリンク数（C ≥ W。1回の出金が複数の入金をまたぐほど増加）

採用する手法#

3番を採用します。

加算#

• Depositにレコードを入れるだけ。
• expiration_date の意味（「最終有効日」か「失効日」か）を最初に決めて全クエリで統一しておく。この記事では「最終有効日（その日いっぱいは使える）」とし、有効判定は expiration_date >= CURRENT_DATE、失効判定は expiration_date < CURRENT_DATE とする。取り違えると1日ズレるので注意。

残高の表示#

• Depositから有効期限が有効なレコード（expiration_date >= CURRENT_DATE）を抽出し、DepositWithdraw.amountのsum()を除算

残高 = Σ( Deposit.amount − Σ DepositWithdraw.amount )
       d ∈ Deposit
       d.expiration_date >= CURRENT_DATE

減算#

• トランザクション
  • Depositを有効期限に近い順にソートし、影響のあるDepositを排他ロック（FOR UPDATE）
  • ロックした行から残高を計算してチェックする。残高チェックを先（ロック前）に行うと、同一ユーザへの同時リクエストが両方ともチェックを通過し、二重に消費して残高がマイナスになるTOCTOU競合が起きるため、必ずロックの後にチェックする。
  • Withdrawへレコードの追加
  • DepositWithdrawへレコードの追加

sequenceDiagram
participant App
participant DB

App->>DB: BEGIN
App->>DB: SELECT * FROM Deposit<br/>WHERE user_id=? AND expiration_date >= today<br/>ORDER BY expiration_date, id FOR UPDATE
DB-->>App: ロック済み Deposit 一覧
App->>App: 残高チェック（ロック後に実施）
App->>DB: INSERT INTO Withdraw (user_id, amount, ...)
App->>DB: INSERT INTO DepositWithdraw<br/>(deposit_id, withdraw_id, amount) × 必要分
App->>DB: COMMIT

減算取り消し#

• トランザクション
  • 関連するDepositWithdrawを排他ロック
  • Withdrawの削除
  • 関連するDepositWithdrawの削除
• ただし物理削除（DELETE）は「監査」「過去のとある時点の残高再現」が不要な場合のみにする。過去に存在した出金を後から削除すると、その時点の残高を再構築するクエリーから出金が消え、歴史的な残高を誤って算出してしまう。監査・締め処理がある場合は、cancelled_at での論理削除や打ち消しレコードの追加（逆仕訳）で対応し、レコードは不変（immutable）に保つ。

一覧#

加算と減算を別のテーブルで管理しているので加算と減算の一覧を時系列に出す処理が重くなってしまいます。計算量、メモリ使用量がO(n)です。

このシステムの設計における一番大きなデメリットがこの点。

• DepositとWithdrawをUNION ALLして時間でソートする。
  • 上記の処理は重いのでユースケースレベルで加算と減算それぞれのページを用意する事によってパフォーマンスは担保できる。

ユーザの削除#

• PLを簡単に出すために、退会したユーザに対しては減算処理をして残高を0にしておく必要がある。そうでないと、BSを出すときに退会ユーザを除外するのが難しくなる。現時点でのBSを出すのは簡単だが、過去のとある時点のBSを出す際にはクエリーが不可能ではないが困難になる。
  • この減算処理は通常の減算とまったく同じで、Withdrawだけでなく対応するDepositWithdraw（どのDepositから引いたか）も必ず作成する。これを忘れるとDeposit単位の残高が0にならず、検算（後述）が崩れる。

ポイントの失効#

• BSを計算する際に、expiration_dateを元に算出することになる。有効期限が切れたポイントに関連するPLを算出するのはクエリーが複雑になる。
• 考察
  • PLを集計しやすいように、ポイントが失効したら減算するレコードを入れるバッチを作っても良いが、実行タイミングは失効と同時に行う必要があるので実行が困難。
  • そもそも失効レコードを持たなくても、失効額はクエリーで導出できる。失効したDepositからは消費できない（減算が expiration_date >= CURRENT_DATE で弾く）ので、あるDepositに紐づくDepositWithdrawの合計は有効期限までに必ず確定する。したがって 失効額 = Deposit.amount − Σ(そのDepositのDepositWithdraw)、認識タイミングは expiration_date として、expiration_date < CURRENT_DATE のDepositを集計すれば失効益（breakage）が出せる。
  • 注意：有効期限切れ後に出金を取り消すと、過去（締め済みかもしれない期間）の失効額が遡って変わる。監査・締め処理がある場合は、失効後の取り消しは現在期間の打ち消しイベントとして記録し、締め済み期間のレコードは触らない運用にする。

検算#

BSのスナップショットは持っていないので、PLの整合性を検証するのみになります。PLを複数テーブルで管理しているのでその整合性を確認します。

• 検証1：出金（Withdraw）ごとに Withdraw.amount == sum(関連するDepositWithdraw.amount) であること。
  • deposit_withdraw をそのままJOINして全体のsum同士を比べてはいけない。1回の出金が複数のDepositWithdrawに分割されると、JOINで Withdraw.amount が分割数だけ重複加算され、正常データでも不一致になる。必ず出金単位（GROUP BY withdraw.id）で集計してから比較する。DepositWithdrawが1件も無い出金（割当漏れ）も検出できる。
• 検証2：Depositごとに Deposit.amount >= sum(関連するDepositWithdraw.amount)（使いすぎていないこと）。

数式で表すと：

検証1: ∀w ∈ Withdraw:
  w.amount = Σ dw.amount
             dw ∈ DepositWithdraw
             dw.withdraw_id = w.id

検証2: ∀d ∈ Deposit:
  d.amount ≥ Σ dw.amount
             dw ∈ DepositWithdraw
             dw.deposit_id = d.id

さらに、失効額もクエリーで導出できる（前述）ので、BSとPLの一致 も次の会計恒等式で検算できる。

失効額 = Σ ( d.amount − Σ dw.amount )
         d ∈ Deposit            dw ∈ DepositWithdraw
         d.expiration_date       dw.deposit_id = d.id
         < CURRENT_DATE

付与 − 消費 − 失効額 − 現在残高 = 0

  付与   = Σ Deposit.amount       （全 Deposit の合計）
  消費   = Σ Withdraw.amount      （全 Withdraw の合計）
  残高   = 上記「残高の表示」の式

失効レコードを物理的に持たなくても、3テーブル（Deposit / Withdraw / DepositWithdraw）だけから付与・消費・失効・残高のすべてを導出でき、BSとPLの一致まで検証できる。

実装が正しく動いているか確認するための保険としてdailyで上記のバッチを実行しておくのが良いです。

データ容量試算#

以下の前提で5年後のレコード数とデータ容量を試算する。

前提条件

5年後のレコード数

Deposit テーブル         U × D × T = 100,000 × 5 × 60  = 3,000万レコード
Withdraw テーブル        U × W × T = 100,000 × 2 × 60  = 1,200万レコード
DepositWithdraw テーブル U × W × L × T = 100,000 × 2 × 2 × 60 = 2,400万レコード
─────────────────────────────────────────────────────────────────
合計                                                     6,600万レコード

行サイズの見積もり（インデックス込み）

データ容量（5年後、U=10万人）

ユーザ数に比例してスケールするため、U=1万人なら約0.7 GB、U=100万人なら約70 GB となる。

DepositWithdraw のリンク数 L の影響

DepositWithdraw の行数は L（1回の出金が何件の入金をまたぐか）に強く依存する。入金額が小さく出金額が大きいサービスほど L が増加しやすい。

DepositWithdraw レコード数 = U × W × L × T

L=2  → 2,400万レコード  （約 2.9 GB）
L=5  → 6,000万レコード  （約 7.2 GB）
L=10 → 1.2億レコード    （約14.4 GB）

L が大きいと書き込みの計算量も増加するため（前述のメリット・デメリット表参照）、入金単位を細かくしすぎないよう設計上の注意が必要となる。

2026-06-01#

設計とロジックを見直し、以下を修正・追記しました。

• 検算（検証1）の誤りを修正：deposit_withdraw を素直にJOINして合計同士を比較していたため、出金が複数レコードに分割されると Withdraw.amount が重複加算され、正常データでも不一致になっていた。出金単位で集計してから比較する形に修正し、割当漏れの出金も検出できるようにした。
• 消費順を FEFO（First-Expire-First-Out）と明記（獲得順の FIFO とは異なる）。
• 有効期限の境界条件を統一：expiration_date を「最終有効日」と定義し、有効=>= CURRENT_DATE / 失効=< CURRENT_DATE に統一（1日ズレの予防）。
• 減算処理のTOCTOU競合を修正：残高チェックを排他ロックの後に行う順序へ変更。
• 退会処理の注意を追記：Withdrawだけでなく対応するDepositWithdrawも作成する必要がある旨を明記。
• 取り消しの物理削除に関する注意を追記：監査・過去残高が要件の場合は論理削除や逆仕訳で対応する。
• 失効益（breakage）の導出方法と、BS=PL の検算（付与 − 消費 − 失効 − 残高 = 0）を追記。
• デッドロック対策を具体化：テーブル順の固定に加え、同一テーブル内は id 昇順でロックする。

モデルベース要件定義テクニック

🛒 Amazonで購入