YeboID Database Schema

Neon PostgreSQL — the source of truth for identity.

---

Overview

YeboID uses Neon PostgreSQL for:

• User accounts & profiles
• Authentication (PINs, sessions)
• Handles & reservations
• OTP codes
• Audit logs

---

Tables

users

The core identity table. Every YeboID user has exactly one row here.

CREATE TABLE users (
  -- Identity
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  phone VARCHAR(20) UNIQUE NOT NULL,           -- E.164 format: +26878422613
  phone_verified BOOLEAN DEFAULT FALSE,
  handle VARCHAR(30) UNIQUE,                    -- lowercase: laslie
  
  -- Authentication
  pin_hash VARCHAR(255),                        -- bcrypt hash
  pin_attempts INTEGER DEFAULT 0,               -- failed attempts counter
  pin_locked_until TIMESTAMP,                   -- lockout expiry
  
  -- Profile
  name VARCHAR(100),
  avatar_url TEXT,
  bio VARCHAR(160),
  country CHAR(2),                              -- ISO 3166-1: SZ, ZA, KE
  language VARCHAR(10) DEFAULT 'en',            -- ISO 639-1
  
  -- Verification
  kyc_status VARCHAR(20) DEFAULT 'none',        -- none, pending, verified, rejected
  kyc_verified_at TIMESTAMP,
  kyc_provider_id VARCHAR(100),                 -- YeboVerify reference
  
  -- Metadata
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  last_login_at TIMESTAMP,
  
  -- Constraints
  CONSTRAINT handle_format CHECK (handle ~ '^[a-z0-9_]{3,30}$'),
  CONSTRAINT handle_no_edges CHECK (handle !~ '^_' AND handle !~ '_$')
);

-- Indexes
CREATE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_handle ON users(handle);
CREATE INDEX idx_users_kyc_status ON users(kyc_status);
CREATE INDEX idx_users_created_at ON users(created_at);

otp_codes

Temporary codes for phone verification and PIN reset.

CREATE TABLE otp_codes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  phone VARCHAR(20) NOT NULL,
  code VARCHAR(6) NOT NULL,
  purpose VARCHAR(20) NOT NULL,                 -- signup, pin_reset
  attempts INTEGER DEFAULT 0,                   -- verification attempts
  max_attempts INTEGER DEFAULT 5,
  expires_at TIMESTAMP NOT NULL,
  verified_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  
  CONSTRAINT valid_purpose CHECK (purpose IN ('signup', 'pin_reset'))
);

-- Indexes
CREATE INDEX idx_otp_phone_purpose ON otp_codes(phone, purpose);
CREATE INDEX idx_otp_expires ON otp_codes(expires_at);

-- Auto-cleanup: delete expired codes after 24 hours
-- (handled by cron job or pg_cron)

sessions

Active user sessions (refresh tokens).

CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  -- Token
  refresh_token_hash VARCHAR(255) UNIQUE NOT NULL,  -- SHA-256 hash
  
  -- Device info
  device_id VARCHAR(100),                       -- unique device identifier
  device_name VARCHAR(100),                     -- "iPhone 15 Pro"
  platform VARCHAR(50),                         -- ios, android, web
  ip_address INET,
  user_agent TEXT,
  
  -- Lifecycle
  last_used_at TIMESTAMP DEFAULT NOW(),
  expires_at TIMESTAMP NOT NULL,
  revoked_at TIMESTAMP,
  revoke_reason VARCHAR(50),                    -- logout, security, expired
  
  created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_sessions_user ON sessions(user_id);
CREATE INDEX idx_sessions_token ON sessions(refresh_token_hash);
CREATE INDEX idx_sessions_expires ON sessions(expires_at);
CREATE INDEX idx_sessions_active ON sessions(user_id) WHERE revoked_at IS NULL;

reserved_handles

Handles that cannot be registered.

CREATE TABLE reserved_handles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  handle VARCHAR(30) UNIQUE NOT NULL,
  reason VARCHAR(50) NOT NULL,                  -- brand, inappropriate, system
  reserved_by VARCHAR(100),                     -- admin email or system
  reserved_at TIMESTAMP DEFAULT NOW(),
  
  CONSTRAINT valid_reason CHECK (reason IN ('brand', 'inappropriate', 'system', 'premium'))
);

-- Pre-populate with system handles
INSERT INTO reserved_handles (handle, reason, reserved_by) VALUES
  ('admin', 'system', 'system'),
  ('support', 'system', 'system'),
  ('help', 'system', 'system'),
  ('yebo', 'brand', 'system'),
  ('yeboid', 'brand', 'system'),
  ('yeboshops', 'brand', 'system'),
  ('yebojobs', 'brand', 'system'),
  ('yebolearn', 'brand', 'system'),
  ('yebolink', 'brand', 'system'),
  ('yebona', 'brand', 'system'),
  ('yebosafe', 'brand', 'system'),
  ('yebocars', 'brand', 'system'),
  ('eneza', 'brand', 'system'),
  ('omevision', 'brand', 'system'),
  ('official', 'system', 'system'),
  ('api', 'system', 'system'),
  ('www', 'system', 'system'),
  ('app', 'system', 'system'),
  ('security', 'system', 'system'),
  ('billing', 'system', 'system'),
  ('status', 'system', 'system');

handle_changes

History of handle changes (for cooldown enforcement).

CREATE TABLE handle_changes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  old_handle VARCHAR(30) NOT NULL,
  new_handle VARCHAR(30) NOT NULL,
  changed_at TIMESTAMP DEFAULT NOW(),
  
  -- Old handle release
  old_handle_released_at TIMESTAMP,             -- 90 days after change
  
  CONSTRAINT different_handles CHECK (old_handle != new_handle)
);

-- Indexes
CREATE INDEX idx_handle_changes_user ON handle_changes(user_id);
CREATE INDEX idx_handle_changes_old ON handle_changes(old_handle);
CREATE INDEX idx_handle_changes_date ON handle_changes(changed_at);

audit_logs

Security-relevant events.

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Actor
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  ip_address INET,
  user_agent TEXT,
  
  -- Event
  event_type VARCHAR(50) NOT NULL,              -- See event types below
  event_data JSONB,                             -- Additional context
  
  -- Result
  success BOOLEAN NOT NULL,
  failure_reason VARCHAR(100),
  
  created_at TIMESTAMP DEFAULT NOW()
);

-- Event types:
-- auth.signup, auth.signin, auth.signout
-- auth.pin_reset, auth.pin_failed, auth.pin_locked
-- auth.otp_sent, auth.otp_verified, auth.otp_failed
-- profile.updated, profile.deleted
-- handle.changed
-- session.revoked, session.revoked_all
-- kyc.initiated, kyc.completed, kyc.failed

-- Indexes
CREATE INDEX idx_audit_user ON audit_logs(user_id);
CREATE INDEX idx_audit_type ON audit_logs(event_type);
CREATE INDEX idx_audit_date ON audit_logs(created_at);

-- Partitioning by month for scale
-- (implement when needed)

rate_limits

Track rate limiting per phone/IP.

CREATE TABLE rate_limits (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Identifier
  key VARCHAR(100) NOT NULL,                    -- phone:+268... or ip:1.2.3.4
  action VARCHAR(50) NOT NULL,                  -- otp_send, signin, etc.
  
  -- Counts
  count INTEGER DEFAULT 1,
  window_start TIMESTAMP DEFAULT NOW(),
  window_minutes INTEGER NOT NULL,              -- window size
  max_count INTEGER NOT NULL,                   -- limit
  
  UNIQUE(key, action)
);

-- Indexes
CREATE INDEX idx_rate_limits_key ON rate_limits(key, action);
CREATE INDEX idx_rate_limits_window ON rate_limits(window_start);

---

Relationships

users
  │
  ├── 1:N ── sessions (active login sessions)
  │
  ├── 1:N ── otp_codes (temporary codes)
  │
  ├── 1:N ── handle_changes (history)
  │
  └── 1:N ── audit_logs (security events)

reserved_handles (standalone)
rate_limits (standalone)

---

Data Types & Constraints

Phone Numbers

• Format: E.164 (+26878422613)
• Validated on input
• Unique constraint

Handles

• 3-30 characters
• Lowercase only
• Letters, numbers, underscores
• No leading/trailing underscores
• Unique constraint
• Checked against reserved_handles

PINs

• 4-6 digits
• Stored as bcrypt hash (cost 12)
• Never logged

Timestamps

• All in UTC
• created_at auto-set
• updated_at via trigger

---

Triggers

Updated At Trigger

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

Audit Trigger (Optional)

CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_logs (user_id, event_type, event_data, success)
  VALUES (
    NEW.id,
    'profile.updated',
    jsonb_build_object(
      'old', to_jsonb(OLD),
      'new', to_jsonb(NEW)
    ),
    TRUE
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

---

Migrations

Naming Convention

001_create_users.sql
002_create_otp_codes.sql
003_create_sessions.sql
004_create_reserved_handles.sql
005_create_handle_changes.sql
006_create_audit_logs.sql
007_create_rate_limits.sql

Running Migrations

# Via Prisma
npx prisma migrate deploy

# Via raw SQL
psql $DATABASE_URL -f migrations/001_create_users.sql

---

Maintenance

Cleanup Jobs

-- Delete expired OTP codes (run hourly)
DELETE FROM otp_codes 
WHERE expires_at < NOW() - INTERVAL '24 hours';

-- Delete expired sessions (run daily)
DELETE FROM sessions 
WHERE expires_at < NOW() - INTERVAL '7 days';

-- Archive old audit logs (run monthly)
-- Move to cold storage after 90 days

Vacuum Schedule

-- Auto-vacuum should handle most cases
-- For high-traffic tables, consider more aggressive settings:
ALTER TABLE otp_codes SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE sessions SET (autovacuum_vacuum_scale_factor = 0.05);

---

Backup Strategy

• Neon: Automatic point-in-time recovery
• Retention: 7 days for dev, 30 days for prod
• Export: Daily pg_dump to Cloud Storage (optional)

---

Performance Notes

Expected Scale (Year 1)

• Users: 50,000-100,000
• Sessions: 200,000-500,000
• OTP codes: 10,000/day
• Audit logs: 50,000/day

Indexes

All foreign keys and frequently queried columns are indexed. Monitor slow queries and add indexes as needed.

Connection Pooling

Neon handles pooling. For Cloud Run:

• Min connections: 1
• Max connections: 10 per instance

---

Last updated: March 18, 2026