TL;DR
Audit trail data adalah tabel log yang mencatat siapa mengubah baris apa, dari nilai berapa jadi berapa, dan kapan. Cara paling andal membuatnya adalah trigger di level database, karena trigger tetap jalan meskipun perubahan datang dari aplikasi lain atau dari orang yang mengetik UPDATE langsung. Kolom minimal yang perlu dicatat: nama tabel, kunci baris, nama kolom, nilai lama, nilai baru, pelaku, waktu, dan jenis aksi. Batasi juga siapa yang boleh menghapus isi tabel log itu.
Audit trail data adalah tabel log yang mencatat siapa mengubah baris apa, dari nilai berapa jadi berapa, dan kapan persisnya.
Kamu butuh ini saat angka laporan bulan lalu tiba-tiba beda dari yang kamu kirim. Tanpa audit trail, yang tersisa cuma saling tanya di grup chat.
Aku bakal tunjukin struktur tabel log yang praktis, trigger PostgreSQL yang bisa langsung dipakai, query buat melacak perubahan, dan beberapa kesalahan yang bikin log kelihatan lengkap tapi gak berguna.
Audit trail nyimpen jejak tiap perubahan data, bukan cuma kondisi akhirnya. Bedanya dari backup: backup bikin kamu bisa balik ke kondisi kemarin, audit trail bikin kamu tahu satu angka berubah kapan, oleh siapa, dan dari berapa jadi berapa. Dua-duanya perlu, dan gak saling menggantikan.
Empat situasi yang bikin audit trail langsung kepakai:
Kalau kamu pernah kena satu saja dari empat itu, kamu udah tahu betapa mahalnya menebak.
Struktur yang aku pakai punya sembilan kolom. Gak lebih, karena tabel log yang kegemukan malah bikin orang malas bacanya.
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
nama_tabel TEXT NOT NULL,
kunci_baris TEXT NOT NULL,
nama_kolom TEXT,
nilai_lama TEXT,
nilai_baru TEXT,
aksi TEXT NOT NULL,
pelaku TEXT NOT NULL DEFAULT CURRENT_USER,
waktu TIMESTAMPTZ NOT NULL DEFAULT NOW(),
alasan TEXT
);
CREATE INDEX idx_audit_tabel_baris ON audit_log (nama_tabel, kunci_baris);
CREATE INDEX idx_audit_waktu ON audit_log (waktu DESC);Dua kolom yang sering dilupakan orang tapi paling berguna.
nilai_lama disimpan sebagai TEXT supaya satu tabel log bisa melayani semua jenis kolom, angka maupun tanggal.
alasan diisi dari aplikasi kalau perubahannya manual. Ini yang bikin kamu tahu bedanya koreksi salah input dan penyesuaian stok setelah opname.
Dua index itu wajib. Tanpa index, query pelacakan di tabel log yang udah jutaan baris bakal makan menit, bukan detik.
Trigger adalah fungsi yang otomatis jalan tiap ada perubahan di tabel tertentu. Ini yang bikin catatan kamu gak bisa dilewati.
CREATE OR REPLACE FUNCTION catat_perubahan_produk()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'UPDATE' AND NEW.harga IS DISTINCT FROM OLD.harga THEN
INSERT INTO audit_log (nama_tabel, kunci_baris, nama_kolom,
nilai_lama, nilai_baru, aksi)
VALUES ('produk', OLD.id_produk::TEXT, 'harga',
OLD.harga::TEXT, NEW.harga::TEXT, 'UPDATE');
END IF;
IF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (nama_tabel, kunci_baris, aksi, nilai_lama)
VALUES ('produk', OLD.id_produk::TEXT, 'DELETE', row_to_json(OLD)::TEXT);
RETURN OLD;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_audit_produk
AFTER UPDATE OR DELETE ON produk
FOR EACH ROW EXECUTE FUNCTION catat_perubahan_produk();Perhatikan IS DISTINCT FROM. Itu bukan sama dengan tanda tidak sama biasa.
Kalau kamu pakai <>, perubahan dari NULL ke sebuah nilai gak bakal terdeteksi, karena perbandingan apa pun dengan NULL menghasilkan NULL. IS DISTINCT FROM nangani itu dengan benar.
Untuk DELETE, aku simpan seluruh baris dalam bentuk JSON. Alasannya sederhana: kalau barisnya udah hilang, kamu gak bisa lagi nyusun ulang isinya dari kolom per kolom.
Sintaks lengkap dan pilihan lain untuk trigger ada di dokumentasi resmi PostgreSQL.
Ini masalah yang paling sering bikin audit trail jadi setengah berguna.
Kalau semua koneksi aplikasi pakai satu akun database, CURRENT_USER cuma bakal nulis nama akun itu. Kamu tahu perubahannya dari aplikasi, tapi gak tahu kasir mana.
Solusinya kirim identitas pengguna lewat variabel sesi sebelum menjalankan query.
-- Dijalankan aplikasi di awal tiap transaksi
SET LOCAL app.pelaku = 'kasir_rina';
-- Di dalam trigger, ganti DEFAULT CURRENT_USER jadi:
COALESCE(current_setting('app.pelaku', TRUE), CURRENT_USER)Kalau aplikasi lupa mengisi variabel itu, COALESCE bakal jatuh balik ke nama akun database. Jadi lognya tetap terisi, gak pernah kosong.
Tabel log gak berguna kalau kamu gak punya query siap pakai. Ini tiga yang paling sering aku jalankan.
Riwayat satu baris tertentu:
SELECT waktu, pelaku, nama_kolom, nilai_lama, nilai_baru, alasan
FROM audit_log
WHERE nama_tabel = 'produk' AND kunci_baris = '1042'
ORDER BY waktu DESC;Perubahan di luar jam kerja, yang biasanya layak dilihat lebih dekat:
SELECT pelaku, COUNT(*) AS jumlah_perubahan
FROM audit_log
WHERE waktu >= NOW() - INTERVAL '30 days'
AND (EXTRACT(HOUR FROM waktu) < 7 OR EXTRACT(HOUR FROM waktu) > 20)
GROUP BY pelaku
ORDER BY jumlah_perubahan DESC;Perubahan harga yang selisihnya besar:
SELECT waktu, pelaku, kunci_baris,
nilai_lama::NUMERIC AS harga_lama,
nilai_baru::NUMERIC AS harga_baru,
ROUND((nilai_baru::NUMERIC - nilai_lama::NUMERIC)
/ NULLIF(nilai_lama::NUMERIC, 0) * 100, 1) AS persen_ubah
FROM audit_log
WHERE nama_kolom = 'harga'
AND ABS(nilai_baru::NUMERIC - nilai_lama::NUMERIC)
> nilai_lama::NUMERIC * 0.2
ORDER BY waktu DESC;NULLIF di situ mencegah pembagian dengan nol kalau ada produk yang harga lamanya nol.
Bulan Oktober, laporan margin toko_berkah nunjukin kategori minyak goreng untung 22%. Bulan November, kategori yang sama tercatat untung 8%, padahal harga jualnya gak berubah.
Tanpa audit trail, tebakan pertama biasanya "pasti harga beli naik". Itu tebakan yang masuk akal dan salah.
Setelah trigger dipasang, query pelacakan nunjukin 47 perubahan pada kolom harga_beli dalam 30 hari, dan 31 di antaranya terjadi dalam rentang 4 menit di tanggal 3 November pukul 23.14.
Semuanya dari satu akun, dengan pola perubahan yang sama: harga beli naik persis 15%.
Ternyata itu impor data massal dari file pemasok yang formatnya berubah. Kolom harga sudah termasuk PPN di file baru, sementara sistem menganggapnya belum.
Angka yang bikin ini gampang ketemu: 31 perubahan dalam 4 menit. Manusia gak mungkin ngetik secepat itu.
Perbaikannya butuh 20 menit setelah penyebabnya ketemu. Nyari penyebabnya yang tadinya bisa makan berhari-hari.
Ini juga alasan kenapa kolom waktu perlu presisi sampai detik. Pola waktu sering lebih informatif dari isi perubahannya.
Nyatet dari aplikasi, bukan dari database. Pencatatan di aplikasi cuma menangkap perubahan yang lewat aplikasi itu. Impor massal, perbaikan manual, dan aplikasi kedua bakal lolos tanpa jejak.
Nyatet semua kolom di semua tabel. Tabel log bakal membengkak dan queri jadi lambat. Pilih kolom yang berdampak pada uang atau keputusan.
Ngasih izin DELETE ke tabel log. Log yang bisa dihapus pelakunya bukan log. Cabut izin hapus dari semua akun aplikasi.
Lupa index. Tabel log tumbuh cepat. Tanpa index, pelacakan satu baris di tabel 5 juta record bisa makan lebih dari satu menit.
Gak nyimpen nilai lama. Log yang cuma bilang "harga diubah" tanpa nilai sebelumnya gak menjawab pertanyaan apa pun.
Pakai tanda tidak sama biasa untuk membandingkan. Perubahan dari NULL bakal terlewat. Pakai IS DISTINCT FROM.
Bikin log tapi gak pernah dibaca. Jadwalkan satu query ringkasan mingguan yang dikirim ke chat tim. Log yang gak pernah dilihat cuma jadi beban penyimpanan.
Gak punya rencana pembersihan. Tabel log ikut aturan kebijakan retensi data juga, dan kalau gak diurus dia bakal jadi tabel terbesar di database kamu.
Backup nyimpen kondisi seluruh database di satu titik waktu, jadi kamu bisa memulihkan semuanya sekaligus. Audit trail nyimpen riwayat tiap perubahan beserta pelakunya, jadi kamu bisa tahu satu angka berubah kapan dan oleh siapa. Backup jawab pertanyaan gimana caranya balik ke kondisi kemarin. Audit trail jawab pertanyaan siapa yang mengubah angka ini.
Ada biayanya, tapi biasanya kecil kalau kamu selektif. Mencatat semua kolom di semua tabel jelas berat. Catat cuma kolom yang berdampak pada uang atau keputusan, misalnya harga, diskon, stok, dan status pesanan. Dari pengujianku, trigger yang cuma memantau 4 kolom nambah sekitar 8% waktu eksekusi UPDATE.
Karena pencatatan dari aplikasi cuma menangkap perubahan yang lewat aplikasi itu. Begitu ada orang yang mengetik UPDATE langsung di database, impor data massal, atau aplikasi kedua yang menulis ke tabel sama, catatannya hilang. Trigger jalan di level database, jadi apa pun jalur masuknya tetap tercatat.
Ikuti kebijakan retensi data perusahaan kamu. Untuk perubahan yang menyangkut dokumen keuangan, biasanya jauh lebih lama dari data operasional biasa. Yang perlu diingat: tabel log tumbuh lebih cepat dari yang kamu kira. Rencanakan arsip sejak awal, dan jangan hapus log tanpa mencatat penghapusannya sendiri.
Kalau semua koneksi pakai user yang sama, CURRENT_USER cuma memberi nama akun aplikasi. Solusinya, kirim identitas pengguna dari aplikasi lewat variabel sesi sebelum menjalankan query, lalu baca variabel itu di dalam trigger. Cara lain yang lebih sederhana: tambahkan kolom diubah_oleh di tabel utama dan pastikan aplikasi selalu mengisinya.
Tiga hal yang perlu dibawa.
Satu, pasang trigger di level database, bukan di aplikasi. Ini bedanya catatan yang lengkap dan catatan yang bolong di saat paling dibutuhkan.
Dua, simpan nilai lama dan nilai baru. Log tanpa nilai lama gak menjawab pertanyaan apa pun.
Tiga, cabut izin hapus dari tabel log, dan jadwalkan satu query ringkasan mingguan supaya lognya beneran dibaca.
Minggu ini coba pasang audit trail di satu kolom saja, kolom harga di tabel produk. Biarkan jalan seminggu, lalu jalankan query perubahan di luar jam kerja. Hasilnya biasanya nunjukin sesuatu yang kamu gak duga.
Kalau tabel lognya udah mulai gemuk, lanjut baca kebijakan retensi data supaya kamu tahu bagian mana yang boleh diarsipkan. Buat memahami mutu data secara umum, cek juga data quality.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Kolom waktu isinya angka gede kayak 1704067200 dan bikin bingung? Itu Unix timestamp. Ini cara ngubahnya jadi tanggal beneran di SQL, plus balik lagi.
Data transaksi tersimpan UTC, tapi laporan harus jam WIB. Kalau salah konversi, angka penjualan tengah malam bisa kecatat di tanggal yang salah. Ini cara handle timezone di SQL dengan benar.
Nambah 30 hari ke tanggal invoice, ngurangin sebulan buat cari periode lalu, ngitung selisih hari antar order. Semua itu aritmetika tanggal, dan SQL punya operator INTERVAL buat ngerjainnya.