Veritabanı Tasarımı

🗄️ Bölüm 3

Veritabanı Tasarımı — Çekirdek ER Modeli

PostgreSQL üzerinde 22 tablo + 16 enum. SRS V5'teki tüm tablolar korunmuş; eksik kalan iş atama, sefer, bildirim, abonelik ve misafir ilan yapıları tamamlanmıştır. Çalıştırılabilir şema: db/schema.sql

🧭 İlişkisel verinin dışında: canlı araç konumu Redis'te (TTL'li anahtar, sadece SOS anı DB'ye yazılır), evrak/araç fotoğrafları S3'te (URL referansı DB'de) tutulur.

🗺️ ER Diyagramı

Kimlık & Profil İlan & Operasyon İletişim & Güvenlik Finans Reklam Misafir (Faz 2)

Tablo sözlüğü — modül modül

👤 Kimlık & Profil

users — tek giriş noktası; rol (FIRM/DRIVER/VEHICLE_OWNER/ADMIN), SMS+e-posta doğrulama bayrakları, ortalama puan.

firms — company_type ile otel/acente/tur operatörü ayrımı (kontrol #2).

drivers — TC No şifreli (tc_no_enc), dil bilgisi, D2 bayrağı.

vehicles — plaka, kapasite, VIP sınıfı; kamera & rehber koltuğu bayrakları (okul servisi alanları).

vehicle_job_types — aracın yapabileceği iş tipleri → eşleştirme filtreleri.

user_devices — FCM token'ları; push hedeflemesi.

📋 İlan & Operasyon

job_postings — pricing_type (SABIT_FIYAT / TEKLIF_TOPLA), dinamik alanlar JSONB, waypoints, scheduled_at + tekrar günleri.

job_applications — teklif modülü burada yaşar: offered_price teklif yarışını taşır.

job_assignments ★ — iş → şoför + araç bağlama, QR kod üretimi, onay durumu.

trips ★ — sefer başlangıç/bitiş, son bilinen konum (Redis yansıması).

documents — polimorfik (USER/VEHICLE); file_expires_at poliçe/psikoteknik bitiş takibi.

💬 İletişim & Güvenlik

chats / messages — iş başına tek sohbet; is_masked ile onay öncesi kimlik gizleme (SRS 10.3).

reviews — karşılıklı 1-5 puan; iş tamamlanmadan yorum açılmaz.

sos_alerts — yalnızca SOS anı kalıcılaşır; koordinat + çözüm durumu.

notifications ★ — app içi kutu + push geçmişi; okunmamış sayacı buradan.

💰 Finans

wallets — kullanıcı başına tek cüzdan, bakiye CHECK kısıtı ile negatife düşmez.

wallet_transactions — DEPOSIT/PAYMENT/REFUND…; psp_reference Iyzico/PayTR mutabakatı için.

pricing_config — admin panelinden ilan/öne çıkarma/reklam fiyatları dinamik ayarlanır (SRS 9.4).

subscriptions ★ — abonelik paketleri; ilan kotası postings_used ile takip edilir.

📢 Reklam

ad_zones — APP_HOME_BANNER, WEB_MARKET_SIDEBAR… günlük fiyatlı.

advertisements — tarih aralıklı yayın; admin onay statusü.

ad_metrics — gösterim/tıklama sayaçları (Redis'te artar, periyodik DB'ye yazılır).

🌐 Misafir İlan (Faz 2)

guest_postings ★ — üyeliksiz akışın kalbi: iletişim + rota + tarih + ödeme referansı.

Durum makinesi: PENDING_PAYMENT → PENDING_MODERATION → PUBLISHED (→ EXPIRED / REJECTED).

İlan sahibi üye olursa: converted_firm_id ile ilgili firmaya devredilir → CONVERted.

Firma panelindeki Fırsat Radarı bu tabloyu okur; firma kiracı olarak ilanı alıp kendi ilanına dönüştürür.

🔗 SRS V5 ↔ Şema Eşleştirmesi

Bölüm bazlı birebir kontrol

SRS Bölümü	Karşılanan Tablolar	Not
3. Rolleri & Evraklar	users, firms, drivers, vehicles, documents	Belge tipleri enum; bitiş tarihleri file_expires_at
4. İlan & Filtreleme	job_postings, vehicle_job_types	Dinamik alanlar dynamic_fields JSONB'de
5. Mobil Altyapı	user_devices, job_assignments(qr_code), trips	GPS konumu Redis'te; QR atamada
6. Çekirdek ER	users, firms, vehicles, documents, job_postings, job_applications, user_devices	SRS'in kendi tabloları aynen korundu
7. Reklam	ad_zones, advertisements, ad_metrics	Aynen korundu
8. Cüzdan	wallets, wallet_transactions, pricing_config	psp_reference ile sağlayıcı bağımsız
10.1 Puanlama	reviews (+ users.rating_avg)	Rozetler rating eşiğinden hesaplanır
10.2 Ters İhale	job_postings.pricing_type, job_applications.offered_price	ALTER TABLE yerine kalıcı kolon
10.3 Mesajlaşma	chats, messages (+is_masked)	Maskeleme kolonu eklendi
10.4 Takip & SOS	trips, sos_alerts	Canlı konum Redis, SOS DB
10.5 Rota	job_postings.route_waypoints, route_distance_km	Mesafe API'den hesaplanıp yazılır
1.1 Abonelik	subscriptions	SRS'te tablo yoktu → eklendi
Faz 2 · Üyeliksiz ilan	guest_postings	SRS dışı talep → tasarım yeni tabloyla karşılandı

📦 db/schema.sql — çalıştırılabilir, TOHUM verili 🐘 PostgreSQL 16 · 22 tablo · 16 enum 🔒 KVKK: tc_no_enc şifreli, audit log önerilir