Tablo — payments

Proje: Okul Database · Hub: Okul Database — Tables

payments Tablosu

Taksit veya tek seferlik ödeme kayıtları. Transaction-level finansal veri.

---

Tam Kolon Listesi

Kolon	Tip	Nullable	Default	Açıklama
id	increments	NO	—	PK
customer_id	unsignedInt	NO	—	→ customers.id (INDEX)
customer_agreement_id	unsignedInt	YES	NULL	→ customer_agreements.id (INDEX)
product_price_id	unsignedInt	YES	NULL	→ product_prices.id
inflation_rate_id	unsignedInt	YES	NULL	→ inflation_rates.id
recurring_month	int	YES	NULL	Taksit sırası (1-based; NULL = tek seferlik)
amount	decimal(12,2)	NO	—	Ödeme tutarı (TL)
tax_rate	decimal	YES	NULL	KDV oranı (% cinsinden)
payment_method	varchar(50)	YES	NULL	Ödeme yöntemi
status	varchar(50)	NO	—	Durum — enum değerleri aşağıda
transaction_id	varchar(255)	YES	NULL	Ödeme sağlayıcı işlem ID
paid_at	timestamp	YES	NULL	Ödeme tarihi (NULL = henüz ödenmedi)
due_date	date	YES	NULL	Vade tarihi
notes	text	YES	NULL	Notlar
created_at	timestamp	NO	—
updated_at	timestamp	NO	—
deleted_at	timestamp	YES	NULL	Soft delete

---

status Enum Değerleri

Değer	TR Karşılığı	paid_at	Başarılı mı
pending	Beklemede	NULL	—
success	Başarılı	≠ NULL	✓
failed	Başarısız	NULL	✗
future_payment	Gelecek Ödeme	NULL	—
future_payment_failed	Gelecek Ödeme Başarısız	NULL	✗
payment_request	Ödeme Talebi	NULL	—
cancelled	İptal Edildi	NULL	✗
refund	İade	—	✗
in_progress	İşlemde	NULL	—
in_progress_failed	İşlemde Başarısız	NULL	✗

Tahsilat analizi için filtre: status = 'success' AND paid_at IS NOT NULL

---

payment_transactions — Detay Log Tablosu

Kolon	Tip	Açıklama
payment_id	FK	→ payments.id
transaction_id	varchar(255)	Ödeme sağlayıcı ID
status	varchar(50)	Girişim durumu
amount	decimal(12,2)	Denenen tutar
gateway_response	json	Ham API yanıtı (iyzico/banka)
created_at	timestamp

Bir ödeme birden fazla girişim içerebilir (retry). Finansal analiz için payments kullan; debug için payment_transactions.

---

İlişkiler

customer_agreements (1) ──< payments (N) ──< payment_transactions (N)

---

Indexes

Index	Tip	Kolonlar
PRIMARY	PK	id
FK	INDEX	customer_id
FK	INDEX	customer_agreement_id
payments_deleted_at_index	INDEX	deleted_at

---

Hazır Sorgu Kalıpları

-- Temel tahsilat filtresi
FROM payments
WHERE deleted_at IS NULL
  AND status = 'success'
  AND paid_at IS NOT NULL
 
-- Aylık gelir
SELECT DATE_FORMAT(paid_at, '%Y-%m') as ay,
       SUM(amount) as gelir,
       COUNT(*) as odeme_sayisi,
       COUNT(DISTINCT customer_agreement_id) as sozlesme_sayisi
FROM payments
WHERE deleted_at IS NULL AND status = 'success' AND paid_at IS NOT NULL
GROUP BY DATE_FORMAT(paid_at, '%Y-%m')
ORDER BY ay;
 
-- Başarısız ödeme oranı
SELECT status,
       COUNT(*) as sayi,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER(), 2) as pct
FROM payments
WHERE deleted_at IS NULL
GROUP BY status;
 
-- Gecikmiş / vadesi geçmiş ödemeler
SELECT p.*, c.name as musteri, c.firm_name
FROM payments p
JOIN customers c ON c.id = p.customer_id
WHERE p.deleted_at IS NULL
  AND p.status IN ('pending', 'future_payment')
  AND p.due_date < CURDATE()
ORDER BY p.due_date ASC;
 
-- Müşteri bazlı toplam tahsilat ve borç
SELECT p.customer_id,
       SUM(CASE WHEN p.status = 'success' THEN p.amount ELSE 0 END) as tahsilat,
       SUM(CASE WHEN p.status IN ('pending','future_payment') THEN p.amount ELSE 0 END) as bekleyen,
       COUNT(CASE WHEN p.status IN ('failed','future_payment_failed') THEN 1 END) as basarisiz_girisim
FROM payments p
WHERE p.deleted_at IS NULL
GROUP BY p.customer_id;

---

Related

• table-customer-agreements — Sözleşme bağlantısı
• ref-payment-statuses — Tüm status değerleri
• domain-payments — Payments domain
• query-revenue-analysis — Hazır gelir sorguları