ERD render ediliyor...
erDiagram
contacts {
uuid id PK
varchar phone UK "WhatsApp number"
varchar bsuid UK "WA BSUID"
varchar contact_type "identity category"
varchar display_name
varchar onboarding_status "state machine"
bool consent_transfer "KVKK Md9"
bool consent_training "opt-in"
bool consent_marketing "ETK"
}
agent_profiles {
uuid contact_id PK
varchar role "agent broker admin owner"
varchar specialty
varchar territory
int target_monthly
bool active
}
portfolios {
varchar id PK "2026-001 format"
uuid agent_id FK
varchar type "daire villa dukkan arsa"
varchar listing_type "sale rent"
varchar district
varchar rooms
bigint listed_price
varchar currency
varchar status "active inactive sold"
tsvector search_vector "full-text"
}
portfolio_confidential {
varchar portfolio_id PK
varchar owner_name
varchar owner_phone
bigint min_price "dip fiyat"
numeric commission_rate
text private_notes
}
portfolio_media {
uuid id PK
varchar portfolio_id FK
varchar media_type "photo or video"
text storage_path
int sort_order
uuid uploaded_by FK
}
portfolio_history {
uuid id PK
varchar portfolio_id FK
varchar event_type "create update deactivate"
jsonb old_value
jsonb new_value
uuid changed_by FK
}
requests {
uuid id PK
uuid customer_id FK
uuid assigned_agent FK "NULL by default"
varchar request_type "buy or rent"
jsonb criteria "district ptype price"
varchar status "new assigned closed"
uuid source_session_id FK
}
sessions {
uuid id PK
uuid contact_id FK
text status "active closed extracted"
timestamptz last_message_at
text intent
text district
text property_type
text rooms
numeric max_price
text language
numeric qualifying_completeness
}
market_signals {
uuid id PK
varchar source_type
uuid source_id FK
text query_raw
jsonb parsed_criteria
int match_count "0 means signal"
}
portfolio_request_matches {
uuid id PK
varchar portfolio_id FK
uuid request_id FK
smallint match_score "0 to 5"
varchar trigger_type "portfolio or request"
bool notified
uuid notified_agent_id FK
}
showings {
uuid id PK
uuid request_id FK
varchar portfolio_id FK
uuid agent_id FK "selling"
uuid customer_id FK
uuid buying_agent_id FK "cross-agent"
uuid deal_id FK
timestamptz scheduled_at
varchar status "scheduled shown cancelled"
}
deals {
uuid id PK
varchar portfolio_id FK
uuid customer_id FK
uuid listing_agent_id FK
uuid buying_agent_id FK "cross-agent"
bool is_cross_agent
varchar status "matched to closed"
bigint offer_price
bigint closing_price
bigint commission_total
timestamptz last_activity_at "stale check"
}
deal_confirmations {
uuid id PK
uuid deal_id FK
uuid agent_id FK
varchar role "listing or buying"
bool confirmed
bool reminder_sent
}
deal_negotiations {
uuid id PK
uuid deal_id FK
uuid from_contact_id FK
uuid to_contact_id FK
bigint amount
varchar status "pending countered accepted"
}
platform_commissions {
uuid id PK
uuid deal_id FK
uuid contact_id FK
varchar role "listing buying platform"
numeric rate
bigint amount
varchar status "pending invoiced paid"
}
agent_visibility {
uuid id PK
varchar portfolio_id FK
uuid agent_id FK
varchar visibility_level "anonymous partial full"
uuid deal_id FK "unlock context"
uuid granted_by FK
}
message_log {
uuid id PK
uuid from_contact FK
uuid to_contact FK
varchar direction "inbound outbound"
varchar message_type "text image button etc"
text content
varchar wa_message_id UK
uuid session_id FK
timestamptz processed_at "CLAIM column"
jsonb interactive_payload
}
interaction_tracker {
uuid contact_id PK
timestamptz last_inbound
timestamptz last_outbound
timestamptz phone_expires_at "WA 30-day"
timestamptz nudge_sent_at "atomic dedupe"
}
pending_notifications {
uuid id PK
uuid contact_id FK
varchar kind "match or proactive"
text body "rich freeform"
jsonb buttons
timestamptz delivered_at
}
tasks {
uuid id PK
uuid agent_id FK
varchar task_type "followup reminder etc"
text title
uuid related_contact FK
varchar related_portfolio FK
timestamptz due_at
varchar status "pending done cancelled"
}
documents {
uuid id PK
varchar type "contract or certificate"
uuid contact_id FK
varchar portfolio_id FK
uuid agent_id FK
uuid showing_id FK
uuid deal_id FK
jsonb template_data
text file_url
}
contacts ||--o| agent_profiles : "1:1 authority"
contacts ||--|| interaction_tracker : "24h window"
contacts ||--o{ portfolios : "agent_id"
portfolios ||--o| portfolio_confidential : "broker-only"
portfolios ||--o{ portfolio_media : "photos"
portfolios ||--o{ portfolio_history : "audit"
contacts ||--o{ portfolio_media : "uploaded_by"
contacts ||--o{ portfolio_history : "changed_by"
contacts ||--o{ requests : "customer_id"
contacts ||--o{ sessions : "contact_id"
sessions ||--o{ message_log : "session_id"
sessions }o--o| requests : "source_session_id"
contacts ||--o{ market_signals : "source_id"
portfolios ||--o{ portfolio_request_matches : "matcher"
requests ||--o{ portfolio_request_matches : "matcher"
contacts ||--o{ portfolio_request_matches : "notified_agent"
portfolio_request_matches ||--o{ deals : "match_id"
portfolios ||--o{ deals : "portfolio_id"
requests ||--o{ deals : "request_id"
contacts ||--o{ deals : "listing+buying+created_by"
portfolios ||--o{ showings : "portfolio_id"
requests ||--o{ showings : "request_id"
contacts ||--o{ showings : "agent+customer+buying"
deals ||--o{ showings : "deal_id"
deals ||--o{ deal_confirmations : "dual confirm"
contacts ||--o{ deal_confirmations : "agent_id"
deals ||--o{ deal_negotiations : "ping-pong"
contacts ||--o{ deal_negotiations : "from+to"
deals ||--o{ platform_commissions : "split"
contacts ||--o{ platform_commissions : "contact_id"
deals ||--o{ agent_visibility : "unlock"
portfolios ||--o{ agent_visibility : "portfolio_id"
contacts ||--o{ agent_visibility : "agent+granted_by"
contacts ||--o{ message_log : "from+to"
contacts ||--o{ pending_notifications : "contact_id"
contacts ||--o{ tasks : "agent+related"
portfolios ||--o{ tasks : "related_portfolio"
contacts ||--o{ documents : "contact+agent"
portfolios ||--o{ documents : "portfolio_id"
showings ||--o{ documents : "certificate"
deals ||--o{ documents : "deal_id"