YeboNa Database Models

Complete schema documentation. YeboNa uses inline SQL with Neon Serverless Postgres.

Type Definitions

// Service categories
type ServiceCategory = 'exchange' | 'sourcing' | 'verification' | 'freight' | 'translation' | 'consulting';

// User types
type UserType = 'user' | 'provider' | 'both';

// Verification levels
type VerificationLevel = 'phone' | 'id' | 'business' | 'pro';

// Provider status
type ProviderStatus = 'pending' | 'active' | 'suspended';

// Request status
type RequestStatus = 'open' | 'quoted' | 'accepted' | 'completed' | 'cancelled';

// Quote status
type QuoteStatus = 'pending' | 'accepted' | 'declined' | 'expired';

// Transaction status
type TransactionStatus = 'pending_payment' | 'payment_received' | 'in_progress' | 'completed' | 'disputed' | 'refunded' | 'released';

// Review types
type ReviewType = 'user_to_provider' | 'provider_to_user';

// Message types
type MessageType = 'text' | 'image' | 'document' | 'voice' | 'system';

// Badge types
type BadgeType = 'verified' | 'top_rated' | 'pro' | 'fast_response';

// Price units
type PriceUnit = 'fixed' | 'hourly' | 'per_item' | 'percentage';

Users Table

CREATE TABLE users (
  id                 TEXT PRIMARY KEY,
  phone_number       TEXT UNIQUE NOT NULL,
  password_hash      TEXT NOT NULL,
  email              TEXT UNIQUE,
  name               TEXT,
  avatar_url         TEXT,
  country            TEXT,
  country_code       TEXT,
  user_type          TEXT DEFAULT 'user',
  verification_level TEXT DEFAULT 'phone',
  language           TEXT DEFAULT 'en',
  currency           TEXT DEFAULT 'USD',
  verified           BOOLEAN DEFAULT false,
  created_at         TIMESTAMP DEFAULT NOW(),
  updated_at         TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_users_phone ON users(phone_number);
CREATE INDEX idx_users_email ON users(email);

Providers Table

CREATE TABLE providers (
  id                   TEXT PRIMARY KEY,
  user_id              TEXT UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  business_name        TEXT,
  bio                  TEXT,
  city                 TEXT,
  country              TEXT,
  languages            TEXT[] DEFAULT '{}',
  response_time_mins   INTEGER DEFAULT 60,
  
  -- Ratings (calculated from reviews)
  rating               DECIMAL(2,1) DEFAULT 0,
  review_count         INTEGER DEFAULT 0,
  rating_communication DECIMAL(2,1) DEFAULT 0,
  rating_quality       DECIMAL(2,1) DEFAULT 0,
  rating_value         DECIMAL(2,1) DEFAULT 0,
  rating_timeliness    DECIMAL(2,1) DEFAULT 0,
  
  status               TEXT DEFAULT 'pending',
  verified_at          TIMESTAMP,
  created_at           TIMESTAMP DEFAULT NOW(),
  updated_at           TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_providers_user ON providers(user_id);
CREATE INDEX idx_providers_status ON providers(status);

Provider Services Table

CREATE TABLE provider_services (
  id             TEXT PRIMARY KEY,
  provider_id    TEXT NOT NULL REFERENCES providers(id) ON DELETE CASCADE,
  category       TEXT NOT NULL,
  description    TEXT NOT NULL,
  starting_price DECIMAL(12,2) NOT NULL,
  currency       TEXT DEFAULT 'USD',
  price_unit     TEXT DEFAULT 'fixed',
  created_at     TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_provider_services_provider ON provider_services(provider_id);
CREATE INDEX idx_provider_services_category ON provider_services(category);

Portfolio Items Table

CREATE TABLE portfolio_items (
  id          TEXT PRIMARY KEY,
  provider_id TEXT NOT NULL REFERENCES providers(id) ON DELETE CASCADE,
  title       TEXT NOT NULL,
  description TEXT,
  image_url   TEXT NOT NULL,
  created_at  TIMESTAMP DEFAULT NOW()
);

Badges Table

CREATE TABLE badges (
  id          TEXT PRIMARY KEY,
  provider_id TEXT NOT NULL REFERENCES providers(id) ON DELETE CASCADE,
  badge_type  TEXT NOT NULL,
  label       TEXT NOT NULL,
  earned_at   TIMESTAMP DEFAULT NOW()
);

Requests Table

CREATE TABLE requests (
  id               TEXT PRIMARY KEY,
  user_id          TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  category         TEXT NOT NULL,
  title            TEXT NOT NULL,
  description      TEXT NOT NULL,
  attachments      TEXT[] DEFAULT '{}',
  budget_min       DECIMAL(12,2),
  budget_max       DECIMAL(12,2),
  budget_currency  TEXT DEFAULT 'USD',
  timeline         TEXT,
  target_providers TEXT[] DEFAULT '{}',
  status           TEXT DEFAULT 'open',
  expires_at       TIMESTAMP,
  created_at       TIMESTAMP DEFAULT NOW(),
  updated_at       TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_requests_user ON requests(user_id);
CREATE INDEX idx_requests_status ON requests(status);
CREATE INDEX idx_requests_category ON requests(category);

Quotes Table

CREATE TABLE quotes (
  id           TEXT PRIMARY KEY,
  request_id   TEXT NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
  provider_id  TEXT NOT NULL REFERENCES providers(id) ON DELETE CASCADE,
  amount       DECIMAL(12,2) NOT NULL,
  currency     TEXT DEFAULT 'USD',
  description  TEXT NOT NULL,
  deliverables TEXT[] DEFAULT '{}',
  timeline     TEXT,
  valid_until  TIMESTAMP,
  status       TEXT DEFAULT 'pending',
  created_at   TIMESTAMP DEFAULT NOW(),
  updated_at   TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_quotes_request ON quotes(request_id);
CREATE INDEX idx_quotes_provider ON quotes(provider_id);

Transactions Table

CREATE TABLE transactions (
  id                   TEXT PRIMARY KEY,
  quote_id             TEXT NOT NULL REFERENCES quotes(id),
  request_id           TEXT NOT NULL REFERENCES requests(id),
  user_id              TEXT NOT NULL REFERENCES users(id),
  provider_id          TEXT NOT NULL REFERENCES providers(id),
  amount               DECIMAL(12,2) NOT NULL,
  currency             TEXT DEFAULT 'USD',
  fee                  DECIMAL(12,2) DEFAULT 0,
  provider_payout      DECIMAL(12,2) NOT NULL,
  status               TEXT DEFAULT 'pending_payment',
  payment_proof_url    TEXT,
  completion_proof_url TEXT,
  paid_at              TIMESTAMP,
  completed_at         TIMESTAMP,
  released_at          TIMESTAMP,
  created_at           TIMESTAMP DEFAULT NOW(),
  updated_at           TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_transactions_user ON transactions(user_id);
CREATE INDEX idx_transactions_provider ON transactions(provider_id);
CREATE INDEX idx_transactions_status ON transactions(status);

Reviews Table

CREATE TABLE reviews (
  id                   TEXT PRIMARY KEY,
  transaction_id       TEXT NOT NULL REFERENCES transactions(id),
  reviewer_id          TEXT NOT NULL REFERENCES users(id),
  reviewee_id          TEXT NOT NULL REFERENCES users(id),
  review_type          TEXT NOT NULL,
  rating               DECIMAL(2,1) NOT NULL,
  rating_communication DECIMAL(2,1),
  rating_quality       DECIMAL(2,1),
  rating_value         DECIMAL(2,1),
  rating_timeliness    DECIMAL(2,1),
  text                 TEXT NOT NULL,
  photos               TEXT[] DEFAULT '{}',
  response             TEXT,
  created_at           TIMESTAMP DEFAULT NOW(),
  UNIQUE(transaction_id, reviewer_id)
);

CREATE INDEX idx_reviews_transaction ON reviews(transaction_id);
CREATE INDEX idx_reviews_reviewee ON reviews(reviewee_id);

Conversations Table

CREATE TABLE conversations (
  id              TEXT PRIMARY KEY,
  participant_1_id TEXT NOT NULL REFERENCES users(id),
  participant_2_id TEXT NOT NULL REFERENCES users(id),
  request_id      TEXT REFERENCES requests(id),
  transaction_id  TEXT REFERENCES transactions(id),
  last_message_at TIMESTAMP,
  created_at      TIMESTAMP DEFAULT NOW(),
  updated_at      TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_conversations_participants ON conversations(participant_1_id, participant_2_id);

Messages Table

CREATE TABLE messages (
  id              TEXT PRIMARY KEY,
  conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  sender_id       TEXT NOT NULL REFERENCES users(id),
  message_type    TEXT DEFAULT 'text',
  content         TEXT NOT NULL,
  metadata        JSONB,
  read_at         TIMESTAMP,
  created_at      TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_messages_conversation ON messages(conversation_id);
CREATE INDEX idx_messages_created ON messages(created_at);

OTPs Table

CREATE TABLE otps (
  id           TEXT PRIMARY KEY,
  phone_number TEXT NOT NULL,
  code         TEXT NOT NULL,
  type         TEXT NOT NULL,
  expires_at   TIMESTAMP NOT NULL,
  verified     BOOLEAN DEFAULT false,
  created_at   TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_otps_phone_type ON otps(phone_number, type);

Refresh Tokens Table

CREATE TABLE refresh_tokens (
  token        TEXT PRIMARY KEY,
  user_id      TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  phone_number TEXT NOT NULL,
  expires_at   TIMESTAMP NOT NULL,
  created_at   TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_refresh_tokens_user ON refresh_tokens(user_id);

Waitlist Table

CREATE TABLE waitlist (
  id         TEXT PRIMARY KEY,
  name       TEXT NOT NULL,
  phone      TEXT NOT NULL,
  interest   TEXT,
  product    TEXT,
  source     TEXT,
  status     TEXT DEFAULT 'pending',
  notes      TEXT,
  country    TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Blog Posts Table

CREATE TABLE blog_posts (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title          VARCHAR(500) NOT NULL,
  slug           VARCHAR(500) NOT NULL UNIQUE,
  content        TEXT NOT NULL,
  excerpt        TEXT,
  category       VARCHAR(100),
  tags           TEXT[] DEFAULT ARRAY[]::TEXT[],
  status         VARCHAR(20) DEFAULT 'published',
  author         VARCHAR(100) DEFAULT 'AI',
  featured_image TEXT,
  published_at   TIMESTAMP DEFAULT NOW(),
  created_at     TIMESTAMP DEFAULT NOW(),
  updated_at     TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_blog_posts_slug ON blog_posts(slug);
CREATE INDEX idx_blog_posts_status ON blog_posts(status);

Relationships Diagram

User 1──* RefreshToken
 │
 ├──1 Provider 1──* ProviderService
 │      │
 │      ├──* Portfolio
 │      ├──* Badge
 │      └──* Quote
 │
 ├──* Request ──* Quote
 │      │
 │      └──1 Transaction ──* Review
 │
 └──* Conversation ──* Message