Create a separate test database
Install the CLI from official SQLite page and run sqlite3 rewards-test.db in the new folder. Do not open the base of the working plugin for experience. In Windows PowerShell, use ./sqlite3.exe rewards-test.db if the exe is nearby.
Save the following diagram in 001_rewards.sql and apply sqlite3 rewards-test.db ".read 001_rewards.sql". The composite PRIMARY KEY specifies uniqueness, CHECK limits the amount, and the foreign key connects delivery with unlocking. CREATE TABLE rules apply to the actual SQLite engine.
PRAGMA foreign_keys = ON;
CREATE TABLE unlocks (
player_uuid TEXT NOT NULL,
rule_id TEXT NOT NULL,
period_key TEXT NOT NULL,
PRIMARY KEY (player_uuid, rule_id, period_key)
);
CREATE TABLE reward_outbox (
player_uuid TEXT NOT NULL,
rule_id TEXT NOT NULL,
period_key TEXT NOT NULL,
amount INTEGER NOT NULL CHECK (amount > 0),
status TEXT NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'paying', 'paid', 'review')),
PRIMARY KEY (player_uuid, rule_id, period_key),
FOREIGN KEY (player_uuid, rule_id, period_key)
REFERENCES unlocks(player_uuid, rule_id, period_key)
);
Repeat one operation without a second reward
Save the code in unlock.sql and run sqlite3 rewards-test.db ".read unlock.sql" twice. Both times, two SELECTs will return 1. .bail on stops the CLI on error; When the connection is closed, the pending transaction is rolled back.
ON CONFLICT DO NOTHING handles repetition of a unique key here. Don't replace it with a generic INSERT OR IGNORE: it's easy to hide a required field error along with the expected conflict.
.bail on
PRAGMA foreign_keys = ON;
BEGIN IMMEDIATE;
INSERT INTO unlocks VALUES
('00000000-0000-4000-8000-000000000001', 'first_cave', 'permanent')
ON CONFLICT DO NOTHING;
INSERT INTO reward_outbox(player_uuid, rule_id, period_key, amount) VALUES
('00000000-0000-4000-8000-000000000001', 'first_cave', 'permanent', 50)
ON CONFLICT DO NOTHING;
COMMIT;
SELECT count(*) FROM unlocks;
SELECT count(*) FROM reward_outbox;Check the rollback and the immutability of the amount
In the new test database, change the amount of the first charge to -1 and execute the file. CHECK should reject the outbox entry; count unlocks after closing the process should remain 0. In the original database, repeating with the same key and a different amount will not replace the previous 50.
It is better for the API to return the stored amount and detect a modified payload of the same key as a conflict. New period_key creates a new periodic reward consciously; accidentally renaming rule_id will also create a new key.
Don't promise atomicity across database boundaries
In Java, use PreparedStatement and parameters, explicit commit/rollback, and a bounded queue outside the game thread. Outbox allows delivery to continue after a restart, but the Vault call is not involved in the SQLite transaction.
If the provider does not accept a stable operation ID, a fall after payment but before the paid entry leaves an unknown outcome. This operation is transferred to review and verified against the provider’s log; blind replay can give out money a second time. Testing on a real server should include exactly this gap.
The runId is needed in memory, and after the restart the same stable result key is needed. Close round re-finish.
The database file and log must correspond to the same recoverable state. Test SQLite recovery with WAL.