point_histories テーブル定義

テーブル概要

ポイントの付与・使用・失効・調整の履歴を管理するテーブルです。ポイント残高はこのテーブルから動的に集計されます。

テーブル名: point_histories 説明: ポイント履歴用途: ポイントの付与・使用・失効・調整の履歴を記録し、ポイント残高を集計する

カラム定義

カラム名	型	NULL	デフォルト	キー	説明
id	BIGINT UNSIGNED	NOT NULL	AUTO_INCREMENT	PK	主キー
user_id	BIGINT UNSIGNED	NOT NULL	-	FK, INDEX	会員ID（users.id）
order_id	BIGINT UNSIGNED	NULL	NULL	FK, INDEX	注文ID（orders.id、購入時のポイント付与・使用の場合）
point_type	ENUM	NOT NULL	-	INDEX	ポイントタイプ（add, use, expire, adjust）
points	INT UNSIGNED	NOT NULL	-	-	ポイント数（常に正の値）
balance_after	INT	NOT NULL	-	INDEX	処理後残高
expires_at	TIMESTAMP	NULL	NULL	INDEX	有効期限
description	TEXT	NULL	NULL	-	説明
created_by	BIGINT UNSIGNED	NULL	NULL	FK	処理者ID（admins.id、調整時）
created_at	TIMESTAMP	NULL	NULL	INDEX	作成日時
updated_at	TIMESTAMP	NULL	NULL	-	更新日時

インデックス

種別	名称	対象カラム	説明
PRIMARY KEY	pk_point_histories	id	主キー
INDEX	idx_point_histories_user	user_id	会員別検索
INDEX	idx_point_histories_order	order_id	注文別検索
INDEX	idx_point_histories_type	point_type	ポイントタイプ別検索
INDEX	idx_point_histories_balance	balance_after	残高検索
INDEX	idx_point_histories_expires	expires_at	有効期限検索
INDEX	idx_point_histories_created	created_at	作成日時検索

外部キー制約

名称	対象カラム	参照先	ON DELETE	ON UPDATE	説明
fk_point_histories_user	user_id	users(id)	CASCADE	RESTRICT	会員削除時に履歴も削除
fk_point_histories_order	order_id	orders(id)	SET NULL	RESTRICT	注文削除時はNULL
fk_point_histories_created_by	created_by	admins(id)	SET NULL	RESTRICT	管理者削除時はNULL

リレーション

関連先テーブル	関連タイプ	外部キー	参照先	ON DELETE	ON UPDATE	説明
users	N:1	user_id	id	CASCADE	RESTRICT	ポイント履歴は1つの会員に属する
orders	N:1	order_id	id	SET NULL	RESTRICT	ポイント履歴は1つの注文に関連する（購入時のポイント付与・使用の場合）
admins	N:1	created_by	id	SET NULL	RESTRICT	ポイント履歴は1人の管理者によって処理される（調整時）

データ例

id	user_id	order_id	point_type	points	balance_after	expires_at	description	created_at
1	1	100	add	100	100	2026-12-31 23:59:59	購入ポイント付与	2025-11-11 10:00:00
2	1	101	add	50	150	2026-12-31 23:59:59	購入ポイント付与	2025-11-12 10:00:00
3	1	102	use	30	120	NULL	ポイント使用	2025-11-13 10:00:00
4	1	NULL	adjust	20	140	NULL	管理者による調整	2025-11-14 10:00:00
5	1	NULL	expire	50	90	2025-11-15 23:59:59	有効期限切れ	2025-11-15 23:59:59

備考・注意事項

ポイントタイプ（point_type）

値	名称	説明
add	付与	購入時、キャンペーン等でポイントを付与
use	使用	決済時のポイント利用
expire	失効	有効期限切れによるポイント失効
adjust	調整	管理者による手動調整

ポイント数の扱い

常に正の値

pointsカラムはINT UNSIGNEDで、常に正の値として記録
付与: point_type='add', points=100 → +100
使用: point_type='use', points=50 → -50
失効: point_type='expire', points=20 → -20

ポイント残高計算

SELECT
  SUM(CASE
    WHEN point_type = 'add' THEN points
    WHEN point_type IN ('use', 'expire') THEN -points
    ELSE 0
  END) as balance
FROM point_histories
WHERE user_id = ?
  AND (expires_at IS NULL OR expires_at > NOW())

有効期限管理

expires_at

ポイント付与時に有効期限を設定可能
NULLの場合は無期限
有効期限切れのポイントは残高計算から除外
失効処理はバッチジョブで実行

失効処理の例

-- 有効期限切れのポイントを失効として記録
INSERT INTO point_histories (user_id, point_type, points, balance_after, expires_at, description, created_at)
SELECT
  user_id,
  'expire',
  points,
  (SELECT COALESCE(SUM(CASE
    WHEN point_type = 'add' THEN points
    WHEN point_type IN ('use', 'expire') THEN -points
    ELSE 0
  END), 0) FROM point_histories WHERE user_id = ph.user_id AND id <= ph.id) - points as balance_after,
  expires_at,
  '有効期限切れ',
  NOW()
FROM point_histories ph
WHERE expires_at IS NOT NULL
  AND expires_at <= NOW()
  AND point_type = 'add'
  AND NOT EXISTS (
    SELECT 1 FROM point_histories
    WHERE user_id = ph.user_id
      AND point_type = 'expire'
      AND expires_at = ph.expires_at
  );

処理後残高（balance_after）

用途

各ポイント履歴の処理時点での残高を記録
監査や履歴確認に使用
データ整合性の検証に使用

計算方法

その時点までの全履歴を集計して算出
アプリケーション層で計算して保存

管理者による調整（adjust）

created_by

point_type='adjust'の場合、created_byに管理者IDを記録
手動調整の記録として使用
監査ログとして機能

Eloquentモデル例

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class PointHistory extends Model
{
    protected $fillable = [
        'user_id',
        'order_id',
        'point_type',
        'points',
        'balance_after',
        'expires_at',
        'description',
        'created_by',
    ];

    protected function casts(): array
    {
        return [
            'expires_at' => 'datetime',
        ];
    }

    // リレーション
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function order(): BelongsTo
    {
        return $this->belongsTo(Order::class);
    }

    public function createdBy(): BelongsTo
    {
        return $this->belongsTo(Admin::class, 'created_by');
    }

    // スコープ
    public function scopeValid($query)
    {
        return $query->where(function ($q) {
            $q->whereNull('expires_at')
              ->orWhere('expires_at', '>', now());
        });
    }

    public function scopeAdditions($query)
    {
        return $query->where('point_type', 'add');
    }

    public function scopeUsages($query)
    {
        return $query->where('point_type', 'use');
    }

    public function scopeExpired($query)
    {
        return $query->where('point_type', 'expire');
    }

    public function scopeAdjustments($query)
    {
        return $query->where('point_type', 'adjust');
    }

    // ポイント残高計算
    public static function calculateBalance(int $userId): int
    {
        return self::where('user_id', $userId)
            ->valid()
            ->get()
            ->sum(function ($history) {
                return match ($history->point_type) {
                    'add' => $history->points,
                    'use', 'expire' => -$history->points,
                    default => 0,
                };
            });
    }

    // ポイント付与
    public static function addPoints(
        int $userId,
        int $points,
        ?int $orderId = null,
        ?\DateTime $expiresAt = null,
        ?string $description = null
    ): self {
        $balanceAfter = self::calculateBalance($userId) + $points;

        return self::create([
            'user_id' => $userId,
            'order_id' => $orderId,
            'point_type' => 'add',
            'points' => $points,
            'balance_after' => $balanceAfter,
            'expires_at' => $expiresAt,
            'description' => $description ?? 'ポイント付与',
        ]);
    }

    // ポイント使用
    public static function usePoints(
        int $userId,
        int $points,
        ?int $orderId = null,
        ?string $description = null
    ): self {
        $balanceAfter = self::calculateBalance($userId) - $points;

        if ($balanceAfter < 0) {
            throw new \Exception('ポイント残高が不足しています');
        }

        return self::create([
            'user_id' => $userId,
            'order_id' => $orderId,
            'point_type' => 'use',
            'points' => $points,
            'balance_after' => $balanceAfter,
            'description' => $description ?? 'ポイント使用',
        ]);
    }
}