Data Model

Complete database schema for Talbino MVP with corrected structure based on requirements.

Core Principles

1. No role enum: Use is_admin boolean instead
2. Seller capability: Determined by seller_profiles table existence + status
3. One conversation per offer: Enforced by unique constraint
4. Atomic offer acceptance: Transaction ensures consistency
5. UUIDs: All primary keys use UUID for security and distribution

Entity Relationship Diagram

users (1) ──────< (0..1) seller_profiles
  │                         │
  │ (buyer)                 │ (seller)
  │                         │
  ├─< buy_requests          │
  │      │                  │
  │      └──< seller_offers <┘
  │             │
  │             ├─< chat_conversations
  │             │      │
  │             │      └──< chat_messages
  │             │
  │             └──< deals
  │                    │
  │                    ├──< ratings
  │                    └──< reports
  │
  ├─< auth_otps
  ├─< device_tokens
  └─< notifications (optional)

Tables

1. users

Core user table. Every registered user is a buyer by default.

CREATE TABLE users (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    phone_number        VARCHAR(20) UNIQUE NOT NULL,
    phone_verified_at   TIMESTAMP,
    is_admin            BOOLEAN DEFAULT FALSE,
    name                VARCHAR(255),
    avatar_url          VARCHAR(500),
    language            VARCHAR(2) DEFAULT 'ar',
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_users_phone ON users(phone_number);
CREATE INDEX idx_users_admin ON users(is_admin) WHERE is_admin = TRUE;

Fields:

• id: UUID primary key
• phone_number: Unique phone (E.164 format recommended)
• phone_verified_at: Timestamp when phone verified via OTP
• is_admin: Boolean flag for admin access
• name: Display name
• avatar_url: S3 URL for profile photo
• language: Preferred language (ar/en)

Business Rules:

• All users are buyers by default
• Sellers must create seller_profiles entry
• Admins have is_admin = TRUE

---

2. auth_otps

OTP codes for phone verification and login.

CREATE TABLE auth_otps (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    phone_number    VARCHAR(20) NOT NULL,
    code_hash       VARCHAR(255) NOT NULL,
    expires_at      TIMESTAMP NOT NULL,
    attempts        INTEGER DEFAULT 0,
    consumed_at     TIMESTAMP,
    created_at      TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_otps_phone_expires ON auth_otps(phone_number, expires_at);

Fields:

• phone_number: Phone number requesting OTP
• code_hash: Bcrypt hash of 6-digit code
• expires_at: Expiry time (10 minutes from creation)
• attempts: Failed verification attempts (max 3)
• consumed_at: When OTP was successfully used

Business Rules:

• Max 3 verification attempts
• 10-minute expiry
• Rate limit: Max 3 OTP requests per phone per 10 minutes
• OTP marked consumed after successful verification

---

3. device_tokens

Push notification tokens for mobile devices.

CREATE TABLE device_tokens (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    platform        VARCHAR(20) NOT NULL, -- 'ios', 'android', 'flutter_web'
    push_token      TEXT NOT NULL,
    last_seen_at    TIMESTAMP DEFAULT NOW(),
    created_at      TIMESTAMP DEFAULT NOW(),
    UNIQUE(user_id, platform, push_token)
);

CREATE INDEX idx_device_tokens_user ON device_tokens(user_id);

Fields:

• user_id: User owning this device
• platform: Device platform (ios/android/flutter_web)
• push_token: FCM registration token
• last_seen_at: Last time app was active

Business Rules:

• User can have multiple devices
• Tokens refreshed on app launch
• Stale tokens (>90 days) cleaned up periodically

---

4. seller_profiles

Seller-specific profile data. Existence + approved status = seller capability.

CREATE TABLE seller_profiles (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id             UUID UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status              VARCHAR(20) DEFAULT 'pending', -- 'pending', 'approved', 'rejected', 'suspended'
    business_name       VARCHAR(255),
    location_district   VARCHAR(100),
    location_city       VARCHAR(100) DEFAULT 'Cairo',
    bio                 TEXT,
    rating_avg          DECIMAL(2,1) DEFAULT 0.0,
    rating_count        INTEGER DEFAULT 0,
    approved_at         TIMESTAMP,
    approved_by         UUID REFERENCES users(id),
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_seller_profiles_status ON seller_profiles(status);
CREATE INDEX idx_seller_profiles_user ON seller_profiles(user_id);

Fields:

• user_id: One-to-one with users table
• status: Approval status (pending/approved/rejected/suspended)
• business_name: Optional business name
• location_district: Seller's district
• location_city: Seller's city (default Cairo)
• bio: Seller description
• rating_avg: Average rating (0.0-5.0)
• rating_count: Total number of ratings
• approved_at: When admin approved
• approved_by: Admin who approved

Business Rules:

• Only approved sellers can make offers
• Suspended sellers cannot make new offers
• Rating aggregates updated on new rating

---

5. buy_requests

Buyer-posted requests for products.

CREATE TABLE buy_requests (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    buyer_id            UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    category            VARCHAR(50) NOT NULL, -- 'phone', 'accessory'
    product_brand       VARCHAR(100),
    product_model       VARCHAR(100),
    condition_pref      VARCHAR(20), -- 'new', 'like_new', 'good', 'acceptable', 'any'
    budget_min          DECIMAL(10,2),
    budget_max          DECIMAL(10,2),
    location_district   VARCHAR(100),
    location_city       VARCHAR(100) DEFAULT 'Cairo',
    notes               TEXT,
    status              VARCHAR(20) DEFAULT 'active', -- 'active', 'closed', 'cancelled'
    offer_count         INTEGER DEFAULT 0,
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW(),
    closed_at           TIMESTAMP
);

CREATE INDEX idx_requests_status_created ON buy_requests(status, created_at DESC);
CREATE INDEX idx_requests_buyer ON buy_requests(buyer_id);
CREATE INDEX idx_requests_category_location ON buy_requests(category, location_city, status);

Fields:

• buyer_id: User who created request
• category: Product category (phone/accessory)
• product_brand: Brand name
• product_model: Model name
• condition_pref: Desired condition
• budget_min/max: Price range
• location_district/city: Buyer location
• notes: Additional details
• status: Request status (active/closed/cancelled)
• offer_count: Number of offers received
• closed_at: When request closed

Business Rules:

• Only active requests can receive offers
• Auto-closed when offer accepted
• Can be manually closed by buyer

---

6. seller_offers

Seller-submitted offers on buy requests.

CREATE TABLE seller_offers (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    request_id          UUID NOT NULL REFERENCES buy_requests(id) ON DELETE CASCADE,
    seller_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    price               DECIMAL(10,2) NOT NULL,
    availability        VARCHAR(100),
    condition_notes     TEXT,
    warranty_notes      TEXT,
    status              VARCHAR(20) DEFAULT 'pending', -- 'pending', 'accepted', 'rejected', 'withdrawn'
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_offers_request_status ON seller_offers(request_id, status);
CREATE INDEX idx_offers_seller_created ON seller_offers(seller_id, created_at DESC);

Fields:

• request_id: Buy request being offered on
• seller_id: Seller making offer
• price: Offered price
• availability: Availability description
• condition_notes: Product condition details
• warranty_notes: Warranty information
• status: Offer status (pending/accepted/rejected/withdrawn)

Business Rules:

• Can only create if request.status = 'active'
• Can only create if seller_profiles.status = 'approved'
• Price should be within budget range (validated in API)
• When accepted: status → 'accepted', others → 'rejected'

---

7. chat_conversations

1:1 conversations between buyer and seller per offer.

CREATE TABLE chat_conversations (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    request_id          UUID NOT NULL REFERENCES buy_requests(id) ON DELETE CASCADE,
    offer_id            UUID UNIQUE NOT NULL REFERENCES seller_offers(id) ON DELETE CASCADE,
    buyer_id            UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    seller_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    last_message_at     TIMESTAMP,
    created_at          TIMESTAMP DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_conversations_offer ON chat_conversations(offer_id);
CREATE INDEX idx_conversations_buyer ON chat_conversations(buyer_id);
CREATE INDEX idx_conversations_seller ON chat_conversations(seller_id);

Fields:

• request_id: Related buy request
• offer_id: Related offer (UNIQUE - one conversation per offer)
• buyer_id: Buyer participant
• seller_id: Seller participant
• last_message_at: Last message timestamp

Business Rules:

• One conversation per offer (enforced by unique constraint)
• Created on first message
• Persists after deal completion

---

8. chat_messages

Messages within conversations.

CREATE TABLE chat_messages (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id     UUID NOT NULL REFERENCES chat_conversations(id) ON DELETE CASCADE,
    sender_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    message_text        TEXT NOT NULL,
    read_at             TIMESTAMP,
    created_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_messages_conversation_created ON chat_messages(conversation_id, created_at);
CREATE INDEX idx_messages_sender ON chat_messages(sender_id);

Fields:

• conversation_id: Parent conversation
• sender_id: User who sent message
• message_text: Message content (text only in MVP)
• read_at: When recipient read message
• created_at: Message timestamp

Business Rules:

• Text only in MVP (no media)
• Real-time delivery via WebSocket
• Push notification if recipient offline

---

9. deals

Created when buyer accepts an offer.

CREATE TABLE deals (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    request_id          UUID NOT NULL REFERENCES buy_requests(id) ON DELETE CASCADE,
    offer_id            UUID UNIQUE NOT NULL REFERENCES seller_offers(id) ON DELETE CASCADE,
    buyer_id            UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    seller_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status              VARCHAR(20) DEFAULT 'accepted', -- 'accepted', 'completed', 'cancelled'
    completed_at        TIMESTAMP,
    cancelled_at        TIMESTAMP,
    cancellation_reason TEXT,
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_deals_buyer ON deals(buyer_id);
CREATE INDEX idx_deals_seller ON deals(seller_id);
CREATE INDEX idx_deals_status ON deals(status);

Fields:

• request_id: Original buy request
• offer_id: Accepted offer (one-to-one)
• buyer_id: Buyer in deal
• seller_id: Seller in deal
• status: Deal status (accepted/completed/cancelled)
• completed_at: When marked completed
• cancelled_at: When cancelled
• cancellation_reason: Why cancelled

Business Rules:

• Created atomically with offer acceptance
• Only buyer can mark completed/cancelled
• Completed deals trigger rating prompt

---

10. ratings

Buyer ratings of sellers after deal completion.

CREATE TABLE ratings (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    deal_id             UUID UNIQUE NOT NULL REFERENCES deals(id) ON DELETE CASCADE,
    buyer_id            UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    seller_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    rating              INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    review_text         TEXT,
    created_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_ratings_seller ON ratings(seller_id);
CREATE INDEX idx_ratings_deal ON ratings(deal_id);

Fields:

• deal_id: Deal being rated (one rating per deal)
• buyer_id: Buyer who rated
• seller_id: Seller being rated
• rating: Star rating (1-5)
• review_text: Optional written review

Business Rules:

• One rating per deal (enforced by unique constraint)
• Only after deal marked completed
• Updates seller_profiles.rating_avg and rating_count

---

11. reports

Buyer reports of problematic sellers.

CREATE TABLE reports (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reporter_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    reported_user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    deal_id             UUID REFERENCES deals(id) ON DELETE SET NULL,
    reason              VARCHAR(50) NOT NULL, -- 'fake_offer', 'no_show', 'rude', 'scam', 'other'
    description         TEXT,
    status              VARCHAR(20) DEFAULT 'pending', -- 'pending', 'reviewed', 'resolved'
    reviewed_by         UUID REFERENCES users(id),
    reviewed_at         TIMESTAMP,
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_reports_status ON reports(status);
CREATE INDEX idx_reports_reported_user ON reports(reported_user_id);

Fields:

• reporter_id: User filing report
• reported_user_id: User being reported
• deal_id: Related deal (optional)
• reason: Report reason
• description: Additional details
• status: Review status (pending/reviewed/resolved)
• reviewed_by: Admin who reviewed
• reviewed_at: When reviewed

Business Rules:

• Can report without deal (general complaint)
• Admin reviews and takes action
• May result in seller suspension

---

12. notifications (Optional - Audit Only)

Optional table for push notification audit trail. Not used as user inbox in MVP.

CREATE TABLE notifications (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type            VARCHAR(50) NOT NULL,
    title           VARCHAR(255) NOT NULL,
    body            TEXT NOT NULL,
    data            JSONB,
    sent_at         TIMESTAMP,
    created_at      TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_notifications_user_created ON notifications(user_id, created_at DESC);

Purpose: Audit log of sent push notifications for debugging. Not displayed in app.

---

Key Relationships Summary

Parent	Child	Relationship	Constraint
users	seller_profiles	1:0..1	One seller profile per user
users	buy_requests	1:N	User creates many requests
users	seller_offers	1:N	Seller makes many offers
buy_requests	seller_offers	1:N	Request receives many offers
seller_offers	chat_conversations	1:1	One conversation per offer
chat_conversations	chat_messages	1:N	Conversation has many messages
seller_offers	deals	1:0..1	Accepted offer becomes deal
deals	ratings	1:0..1	Deal can have one rating
deals	reports	1:N	Deal can have multiple reports

Marketplace Business Rules (Enforced)

Offer Creation Rules

-- Can only offer on active requests
SELECT * FROM buy_requests WHERE id = ? AND status = 'active';

-- Seller must be approved
SELECT * FROM seller_profiles WHERE user_id = ? AND status = 'approved';

-- Price should be in budget range (API validation)
price >= budget_min AND price <= budget_max

Offer Acceptance Transaction

BEGIN;
  -- Create deal
  INSERT INTO deals (request_id, offer_id, buyer_id, seller_id, status)
  VALUES (?, ?, ?, ?, 'accepted');
  
  -- Update accepted offer
  UPDATE seller_offers SET status = 'accepted' WHERE id = ?;
  
  -- Reject other offers
  UPDATE seller_offers SET status = 'rejected' 
  WHERE request_id = ? AND id != ?;
  
  -- Close request
  UPDATE buy_requests SET status = 'closed', closed_at = NOW() 
  WHERE id = ?;
COMMIT;

Indexes for Performance

All critical indexes are included in table definitions above. Summary:

• users: phone_number, is_admin
• buy_requests: (status, created_at), buyer_id, (category, location_city, status)
• seller_offers: (request_id, status), (seller_id, created_at)
• chat_messages: (conversation_id, created_at)
• device_tokens: user_id
• seller_profiles: status, user_id
• deals: buyer_id, seller_id, status
• ratings: seller_id, deal_id
• reports: status, reported_user_id

Migration Strategy

1. Create tables in dependency order (users first, then dependent tables)
2. Add indexes after table creation
3. Seed initial data:
   • Admin user
   • Cairo/Giza districts
   • Phone/accessory categories
4. Run in transaction for rollback safety