Sql & Veritabanı İle Uygulamalar İçin Veri Kayıt Geçmişi İzleme Nasıl Yapılır?

Gereksinimler ve Ön Hazırlık

Bu rehberdeki uygulamaları gerçekleştirmek için temel düzeyde SQL bilgisine sahip olmanız gerekmektedir. Çalışmalarımızı PostgreSQL 16+ veya MySQL 8.4+ sürümleri üzerinde gerçekleştirebilirsiniz. Bu sürümler, JSONB (PostgreSQL) veya JSON (MySQL) veri tiplerini desteklediği için geçmiş verilerini saklamada büyük esneklik sağlar.

• Veritabanı: PostgreSQL veya MySQL kurulu bir sunucu.
• Araç: DBeaver veya pgAdmin gibi bir veritabanı yönetim arayüzü.
• Yetkilendirme: Veritabanı üzerinde tablo oluşturma ve tetikleyici (trigger) tanımlama yetkisine sahip bir kullanıcı.

Adım 1: Denetim Tablosu Tasarımı

Veri kayıt geçmişini izlemek için en yaygın yöntem, ana tablodaki her değişikliği ayrı bir "log" veya "audit" tablosuna kaydetmektir. Bu tablo, değişikliğin yapıldığı tablo adını, etkilenen satırın ID'sini, yapılan işlemi (INSERT, UPDATE, DELETE) ve değişen veriyi içermelidir.

Aşağıdaki SQL kodu, tüm sistemdeki değişiklikleri merkezi olarak izleyebileceğiniz genel bir denetim tablosu yapısını göstermektedir.

CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id INT NOT NULL,
    action_type VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE
    old_data JSONB, -- Değişim öncesi veri
    new_data JSONB, -- Değişim sonrası veri
    changed_by VARCHAR(100), -- İşlemi yapan kullanıcı
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Bu tablo yapısında JSONB kullanımı, farklı tablolardan gelen farklı veri yapılarını tek bir sütunda esnek bir şekilde saklamamıza olanak tanır. old_data ve new_data sütunları, değişikliğin analiz edilmesi için kritik öneme sahiptir.

Adım 2: Tetikleyiciler (Triggers) ile Otomatik İzleme

Manuel olarak her uygulama katmanında loglama yapmak yerine, veritabanı seviyesinde tetikleyiciler kullanmak, veri bütünlüğünü garanti altına alır. Uygulama kodunuzda bir hata olsa bile, veritabanı seviyesindeki tetikleyici her değişikliği yakalar.

Aşağıdaki örnekte, users tablosundaki bir güncelleme işlemini otomatik olarak audit_logs tablosuna yazan bir fonksiyon ve tetikleyici tanımlanmıştır.

CREATE OR REPLACE FUNCTION log_user_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_logs (table_name, record_id, action_type, old_data, new_data)
        VALUES ('users', OLD.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_users
AFTER UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION log_user_changes();

Bu tetikleyici, users tablosunda bir satır güncellendiğinde devreye girer. OLD anahtar kelimesi güncelleme öncesi veriyi, NEW ise güncelleme sonrası veriyi temsil eder. Bu yöntem, veritabanı seviyesinde %100 kapsama sağlar.

Adım 3: Performans ve Depolama Stratejileri

Veri geçmişi izleme sistemleri, zamanla milyonlarca satıra ulaşabilir ve bu durum veritabanı performansını olumsuz etkileyebilir. Bu sorunu aşmak için "bölümleme" (partitioning) yöntemini kullanmalısınız.

Tablolarınızı zamana dayalı olarak (örneğin aylık) bölümlere ayırmak, eski verilere erişimi hızlandırır ve arşivlemeyi kolaylaştırır. Aşağıdaki tablo, farklı izleme stratejilerinin karşılaştırmasını sunar.

Adım 4: Güvenli Veri Erişimi ve Yetkilendirme

Denetim logları, sistemin en hassas verilerini içerir. Bu verilere herkesin erişmemesi gerekir. Sadece yetkili yöneticilerin bu tablolara erişebilmesi için veritabanı üzerinde "Role-Based Access Control" (RBAC) uygulamalısınız.

Aşağıdaki örnek, sadece admin rolüne sahip kullanıcıların logları görmesini sağlayan yetkilendirme komutudur.

REVOKE ALL ON audit_logs FROM PUBLIC;
GRANT SELECT ON audit_logs TO admin_role;

Güvenlik Uyarısı: Denetim logları içerisinde asla şifre (password), kredi kartı numarası veya kişisel sağlık verisi gibi hassas bilgileri açık metin olarak saklamayın. Veritabanı tetikleyicilerinde bu alanları filtreleyerek (maskeleyerek) log tablosuna aktarın.

Adım 5: Uygulama Katmanında İzleme (ORM Entegrasyonu)

Eğer veritabanı tetikleyicileri yerine uygulama katmanında (Laravel, Entity Framework, Hibernate vb.) loglama yapıyorsanız, "Observer" veya "Event" desenlerini kullanmalısınız. Örneğin, bir model kaydedilmeden önce tetiklenen bir olay ile loglama işlemini gerçekleştirebilirsiniz.

Bu yöntem, "kimin" işlemi yaptığı bilgisini (oturum açan kullanıcı ID'si) loglara eklemek için daha uygundur. Veritabanı tetikleyicileri genellikle oturum bilgilerine erişemez.

// Örnek: Bir ORM üzerinde model kaydedilmeden önce loglama
public function saveUser(User $user) {
    $oldData = $user->getOriginal();
    $user->save();
    $newData = $user->toArray();
    
    AuditLog::create([
        'table_name' => 'users',
        'record_id' => $user->id,
        'old_data' => json_encode($oldData),
        'new_data' => json_encode($newData),
        'user_id' => Auth::id()
    ]);
}

Sıkça Sorulan Sorular

Log tablolarının boyutu çok artarsa ne yapmalıyım?

Log tablolarını belirli bir süreden (örneğin 6 ay) eski verileri başka bir soğuk depolama alanına (S3, arşiv veritabanı) taşıyarak temizlemelisiniz (data archiving).

Tetikleyiciler veritabanını yavaşlatır mı?

Evet, yoğun yazma işlemi olan tablolarda tetikleyiciler bir miktar gecikmeye neden olabilir. Bu durumda asenkron loglama yöntemlerini veya veritabanı loglarını (binlog/wal) okuyan CDC araçlarını tercih etmelisiniz.

Hangi verilerin izleneceğine nasıl karar verilir?

Sadece "kritik" tablolarda (kullanıcılar, ödemeler, siparişler) izleme yapın. Tüm tabloları izlemek, depolama maliyetini gereksiz artırır.

Logları nasıl geri yükleyebilirim?

Loglar genellikle bir "geri alma" (undo) mekanizması için kullanılır. old_data sütunundaki veriyi kullanarak ilgili satırı orijinal değerlerine döndüren bir SQL scripti yazabilirsiniz.

SQL Injection riskine karşı ne yapmalı?

Dinamik SQL kullanıyorsanız mutlaka parametreli sorgular (prepared statements) kullanın. Tetikleyicilerde genellikle statik SQL kullanıldığı için bu risk düşüktür ancak yine de dikkatli olunmalıdır.

İleri Seviye Veri Analitiği: Log Verilerinden Anomali Tespiti

Veri kayıt geçmişi izleme sistemini kurduktan sonra, bu logların sadece "kim, ne zaman, neyi değiştirdi" sorusuna yanıt vermesi yeterli değildir. Büyük ölçekli sistemlerde, bu verileri proaktif bir güvenlik aracına dönüştürmek mümkündür. Özellikle kritik tablolarda gerçekleşen sıra dışı veri hareketlerini tespit etmek için SQL üzerinde basit bir anomali tespiti mantığı kurgulayabilirsiniz.

Örneğin, bir kullanıcı hesabının çok kısa sürede birden fazla kez güncellenmesi veya mesai saatleri dışında veri silinmesi gibi durumlar, bir güvenlik ihlali işareti olabilir. Aşağıdaki SQL sorgusu, son 1 saat içinde aynı kullanıcı tarafından yapılan "DELETE" işlemlerini gruplayarak şüpheli aktiviteleri listeler:

SELECT 
    kullanici_id, 
    COUNT(*) as islem_sayisi, 
    MAX(islem_tarihi) as son_islem
FROM denetim_tablosu
WHERE islem_tipi = 'DELETE' 
  AND islem_tarihi > DATEADD(hour, -1, GETDATE())
GROUP BY kullanici_id
HAVING COUNT(*) > 5;

Log Verilerinin Arşivlenmesi ve Partitioning Stratejisi

Log tabloları, veritabanı boyutunun hızla büyümesine neden olan en büyük etkenlerden biridir. Performansı korumak için "Table Partitioning" (Tablo Bölümleme) yöntemini kullanmak, sorgu hızını doğrudan etkiler. Verileri aylık veya yıllık bazda bölümlere ayırarak, eski verilere erişimi optimize edebilirsiniz.

Partitioning Uygulama Adımları

1. Partition Function Oluşturma: Verilerin hangi kriterlere göre bölüneceğini belirleyin (örneğin tarih aralığı).
2. Partition Scheme Tanımlama: Bölünen verilerin hangi dosya gruplarında tutulacağını atayın.
3. Tabloyu Bölümleme: Mevcut log tablosunu bu şemaya göre yeniden yapılandırın.

Aşağıdaki örnek, SQL Server üzerinde tarih bazlı bir bölümleme fonksiyonunun temel mantığını göstermektedir:

CREATE PARTITION FUNCTION LogTarihAraligi (datetime)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01', '2025-01-01');

CREATE PARTITION SCHEME LogDosyaSemasi
AS PARTITION LogTarihAraligi
ALL TO ([PRIMARY]);

-- Tabloyu oluştururken şemayı kullanın
CREATE TABLE denetim_tablosu (
    id INT IDENTITY(1,1),
    islem_tarihi DATETIME,
    -- diğer kolonlar...
) ON LogDosyaSemasi(islem_tarihi);

Hata Ayıklama ve Log Bütünlüğü Kontrolü

Loglama sisteminizin çalışıp çalışmadığını doğrulamak, sistemin kendisi kadar kritiktir. Bazen tetikleyiciler (triggers) devre dışı bırakılabilir veya uygulama katmanındaki hata yönetimi log yazımını engelleyebilir. Bu durumları izlemek için bir "Heartbeat" (Nabız) kontrolü mekanizması kurmalısınız.

İleri İpucu: Her gün belirli bir saatte çalışan bir "Job" (Zamanlanmış Görev) ile log tablosuna sahte bir kayıt ekleyip, ardından bu kaydın başarıyla yazılıp yazılmadığını kontrol eden bir doğrulama scripti çalıştırın. Eğer loglama mekanizması durmuşsa, sistem yöneticisine otomatik bir uyarı e-postası gönderilmesini sağlayın.

Dikkat: Log tablolarınızın üzerine yazma (overwrite) yapılmadığından emin olun. Log verileri "Append-only" (sadece eklenebilir) prensibiyle çalışmalıdır. Veritabanı kullanıcısının log tablosuna UPDATE veya DELETE yetkisi olmaması, veri bütünlüğünü korumak için en temel güvenlik önlemidir.

Sonuç

Veri kayıt geçmişi izleme, sadece bir güvenlik önlemi değil, aynı zamanda sistemin izlenebilirliği için temel bir gereksinimdir. SQL tetikleyicileri ile veritabanı seviyesinde veya ORM olayları ile uygulama seviyesinde bu yapıyı kurarak, sisteminizdeki her değişikliği kayıt altına alabilirsiniz. Unutmayın, iyi bir loglama sistemi, olası bir veri kaybı veya güvenlik ihlali anında hayat kurtarıcıdır.

Yasal Sorumluluk Reddi: Bu rehberdeki kod örnekleri eğitim amaçlıdır. Üretim ortamında (production) uygulamadan önce mutlaka yedek almalı ve performans testlerini gerçekleştirmelisiniz. Güvenlik yapılandırmaları sisteminizin özel ihtiyaçlarına göre değişiklik gösterebilir.

Bir sonraki adım olarak, log verilerini görselleştiren bir dashboard (Grafana veya özel bir admin paneli) geliştirerek, değişiklikleri daha anlamlı bir şekilde raporlamayı deneyebilirsiniz.