Menurunkan Saldo Loyalty Wallet dengan Database Trigger

Masalah dengan saldo yang ditulis oleh aplikasi

Sebuah loyalty wallet biasanya membutuhkan dua tampilan atas keadaan finansial yang sama:

• Ledger append-only yang memuat setiap kredit dan debit.
• Saldo saat ini yang dapat dibaca cepat untuk checkout, reward, atau halaman akun.

Implementasi yang terlihat mudah adalah memasukkan baris ledger dari aplikasi lalu memperbarui wallet.balance dalam query kedua. Kami menghindari pola ini ketika saldo sepenuhnya diturunkan dari pergerakan ledger.

Pendekatan dari aplikasi memiliki beberapa kegagalan yang mungkin terjadi:

• Insert ledger berhasil, tetapi proses berhenti sebelum saldo diperbarui.
• Retry mengulangi sebagian alur dan membuat double credit.
• Dua request membaca saldo yang sama lalu keduanya menulis nilai pengganti yang dihitung masing-masing.
• Layanan yang berbeda menerapkan aturan saldo secara berbeda seiring waktu.

Kami lebih memilih database bertanggung jawab memelihara saldo terdenormalisasi karena database sudah memiliki transaksi yang memuat entri ledger tersebut.

Model kami: ledger lebih dahulu, saldo diturunkan

Kami memperlakukan wallet_ledger sebagai catatan otoritatif. Setiap baris mewakili pergerakan bertanda:

• Nilai amount positif adalah kredit.
• Nilai amount negatif adalah debit.
• Saldo wallet adalah jumlah semua pergerakan ledger yang telah diposting.

Kolom wallets.balance dengan demikian merupakan nilai termaterialisasi. Kolom ini ada untuk kinerja pembacaan, bukan sebagai sumber kebenaran yang dapat diedit secara terpisah.

Skema PostgreSQL yang disederhanakan terlihat seperti ini:

create table wallets (
  id uuid primary key,
  balance bigint not null default 0,
  updated_at timestamptz not null default now()
);

create table wallet_ledger (
  id uuid primary key,
  wallet_id uuid not null references wallets(id),
  amount bigint not null check (amount <> 0),
  status text not null check (status in ('posted', 'voided')),
  idempotency_key text not null,
  created_at timestamptz not null default now(),
  unique (wallet_id, idempotency_key)
);

Kami memakai satuan integer terkecil, bukan nilai floating-point. Untuk loyalty wallet berbasis poin, satuannya dapat langsung berupa poin. Untuk nilai yang menyerupai uang, satuannya harus berupa denominasi terkecil yang didukung.

Memperbarui saldo dalam database trigger

Trigger berjalan dalam transaksi yang sama dengan insert ledger. Jika pembaruan saldo gagal, insert ledger juga gagal. Jika transaksi di-rollback, kedua perubahan tidak tersimpan.

create function apply_wallet_ledger_entry()
returns trigger
language plpgsql
as $$
begin
  if new.status = 'posted' then
    update wallets
    set balance = balance + new.amount,
        updated_at = now()
    where id = new.wallet_id;

    if not found then
      raise exception 'wallet % does not exist', new.wallet_id;
    end if;
  end if;

  return new;
end;
$$;

create trigger wallet_ledger_after_insert
after insert on wallet_ledger
for each row
execute function apply_wallet_ledger_entry();

Kami memakai balance = balance + new.amount, bukan saldo yang dihitung oleh aplikasi. Ini penting untuk concurrency. Update tersebut mengambil row lock pada baris wallet, dan PostgreSQL menerapkan setiap penambahan terhadap versi terbaru yang sudah di-commit dari baris itu.

Jika dua kredit berstatus posted datang ke wallet yang sama dalam waktu hampir bersamaan, saldo akhir mencakup kedua pergerakan tersebut. Kami tidak membiarkan dua proses aplikasi saling menimpa dengan saldo hasil perhitungan lama.

Trigger sendiri tidak mencegah double credit

Database trigger membuat pembaruan dari ledger ke saldo menjadi atomik. Trigger tidak menentukan apakah sebuah entri ledger seharusnya dimasukkan satu kali atau dua kali.

Sebagai contoh, penyedia pembayaran dapat me-retry webhook setelah timeout. Jika aplikasi kami memasukkan dua entri kredit yang berbeda, trigger akan menerapkan keduanya dengan benar. Dari sudut pandang bisnis, itu tetap merupakan double credit.

Kami menangani hal ini dengan idempotency key dan unique constraint. Key tersebut harus mengidentifikasi event eksternal atau operasi bisnis yang hanya boleh membuat satu pergerakan.

insert into wallet_ledger (
  id,
  wallet_id,
  amount,
  status,
  idempotency_key
)
values (
  gen_random_uuid(),
  $1,
  $2,
  'posted',
  $3
)
on conflict (wallet_id, idempotency_key) do nothing;

Aplikasi harus memeriksa apakah insert benar-benar membuat baris. Kami tidak menganggap retry berhasil hanya karena pernyataan SQL selesai.

Kami juga menetapkan cakupan idempotensi dengan sengaja. ID event dari penyedia sering lebih baik daripada ID request HTTP karena beberapa request HTTP dapat mewakili event penyedia yang sama.

Debit dan saldo tidak mencukupi

Untuk loyalty wallet yang tidak boleh memiliki saldo negatif, trigger dapat menegakkan aturan itu ketika trigger memegang row lock wallet.

create function apply_wallet_ledger_entry()
returns trigger
language plpgsql
as $$
declare
  resulting_balance bigint;
begin
  if new.status = 'posted' then
    update wallets
    set balance = balance + new.amount,
        updated_at = now()
    where id = new.wallet_id
    returning balance into resulting_balance;

    if not found then
      raise exception 'wallet % does not exist', new.wallet_id;
    end if;

    if resulting_balance < 0 then
      raise exception 'insufficient wallet balance';
    end if;
  end if;

  return new;
end;
$$;

Exception tersebut me-rollback insert ledger dan pembaruan saldo secara bersamaan. Ini lebih aman daripada membaca saldo terlebih dahulu di aplikasi, memeriksanya, lalu menulis debit di kemudian waktu. Alur yang terpisah itu dapat menyetujui dua debit bersamaan atas poin tersedia yang sama.

Apakah saldo negatif dilarang merupakan aturan produk. Beberapa wallet mengizinkannya untuk penyesuaian atau batas kredit. Kami tidak memasukkan aturan universal ke trigger tanpa menyatakan kebijakan tersebut secara eksplisit.

Menangani pembalikan alih-alih mengubah riwayat

Kami menghindari perubahan jumlah pada baris ledger posted setelah baris tersebut memengaruhi saldo. Mengubah atau menghapus catatan posted membuat audit trail dan rekonsiliasi lebih sulit.

Sebagai gantinya, kami membuat entri ledger kompensasi:

• Untuk membalik kredit 100, masukkan debit posted sebesar -100.
• Untuk membalik debit -100, masukkan kredit posted sebesar 100.
• Hubungkan pembalikan dengan entri asli menggunakan kolom referensi jika keterlacakan diperlukan.

Pendekatan ini menjaga ledger tetap append-only dan memungkinkan kami membangun ulang saldo jika kami mencurigai adanya kesalahan operasional.

Jika sistem membutuhkan status pending, kami menentukan apakah baris pending memengaruhi saldo yang tersedia. Pada trigger contoh, hanya baris posted yang memengaruhi saldo. Mengubah baris dari pending menjadi posted memerlukan trigger AFTER UPDATE atau prosedur posting terpisah. Kami tidak menambahkan perilaku itu secara tidak sengaja, karena transisi status memiliki aturan idempotensi dan otorisasi sendiri.

Rekonsiliasi tetap diperlukan

Database trigger mengurangi selisih data, tetapi tidak menghilangkan kebutuhan untuk memverifikasi data turunan. Bug migrasi, perubahan database manual, trigger yang dinonaktifkan, dan impor data historis masih dapat menghasilkan ketidaksesuaian.

Kami menyimpan query rekonsiliasi yang membandingkan saldo tersimpan dengan jumlah ledger:

select
  w.id,
  w.balance as stored_balance,
  coalesce(sum(l.amount) filter (where l.status = 'posted'), 0) as ledger_balance
from wallets w
left join wallet_ledger l on l.wallet_id = w.id
group by w.id, w.balance
having w.balance <> coalesce(sum(l.amount) filter (where l.status = 'posted'), 0);

Untuk perbaikan, kami lebih memilih memahami penyebabnya terlebih dahulu. Jika kami dengan sengaja membangun ulang saldo, kami melakukannya dalam operasi pemeliharaan yang terkendali dan menyimpan bukti selisih awal.

Trade-off yang kami terima

Database trigger menempatkan perilaku bisnis penting di bawah lapisan aplikasi. Ini memudahkan setiap penulis data menjaga invariant, termasuk skrip dan layanan baru di masa depan. Namun, perilaku tersebut juga menjadi kurang terlihat bagi pengembang yang hanya memeriksa kode aplikasi.

Kami menerima trade-off ini ketika kondisi berikut terpenuhi:

• Saldo benar-benar diturunkan dari ledger.
• Banyak jalur kode dapat membuat entri ledger.
• Ketepatan pada kondisi concurrency lebih penting daripada menyimpan semua logika dalam satu layanan.
• Tim mendokumentasikan, menguji, dan memantau trigger sebagai kode produksi.

Kami menjaga trigger tetap sempit. Trigger menerapkan pergerakan ledger yang tervalidasi ke satu saldo wallet. Idempotensi, otorisasi, kelayakan reward, dan efek samping eksternal tetap menjadi perhatian eksplisit pada aplikasi atau layanan.

Trigger melindungi konsistensi antara baris ledger dan saldo turunannya. Trigger tidak menggantikan alur kerja ledger yang dirancang dengan baik.

Batasan ini memberi kami loyalty wallet yang dapat dibaca secara efisien sambil mempertahankan sumber kebenaran yang dapat diaudit dan jawaban transaksional untuk concurrency.