Database schema: transaction-based points ledger

[avandenberghe]

Summary

Design and implement the new transaction-based point storage, replacing the old fixed-column approach from EQdkp/bbDKP.

This is exactly how accounting systems are built. transactions are write only.

Per #1, the old memberdkp table with separate columns per point type (member_earned, member_time_bonus, etc.) should be replaced with a transaction ledger.

New schema concept

bb_dkp_transactions

Each point movement is a row:

Column	Type	Description
transaction_id	INT PK AUTO	Unique ID
player_id	INT	FK to bb_players
pool_id	INT	FK to bb_dkp_pools
account	VARCHAR	Account type: earned, spent, time_bonus, raid_bonus, adjustment, decay
amount	DECIMAL(11,2)	Signed value
raid_id	INT NULL	FK to bb_dkp_raids (NULL for manual adjustments)
item_id	INT NULL	FK to bb_dkp_items (for spent transactions)
reason	VARCHAR(255)	Description
created_by	INT	User who created
created_at	INT	Unix timestamp

Benefits

• Rollback: Deleting a raid = delete its transactions. No column recalculation needed.
• Flexible accounts: New point types added as account values, not schema changes.
• Audit trail: Full history of every point movement.
• EP/GP ratio: SUM(earned) / SUM(spent) per pool per player.
• Accounting model: General ledger with analytical dimension per DKP pool.

Supporting tables

• bb_dkp_pools — DKP pool definitions (replaces old bb_multidkp)
• bb_dkp_pool_events — Maps events to pools (M:N)

Reference

• Old bbDKP: ext/avathar/bbdkp/root/includes/bbdkp/controller/points/
• EQdkp Plus: admin/manage_multidkp.php, core/auto_point_adjustments.class.php
• Issue Implement new points system based on transactions.  #1: Transaction-based design rationale