YeboLink Database Models Deep Dive

YeboLink uses PostgreSQL (hosted on Neon) with 12 tables. Models are located in src/models/ and use raw SQL via the pg pool.

Database Architecture

┌──────────────────────────────────────────────────────────────────────┐
│                           WORKSPACES                                 │
│  (Central entity - all other tables reference workspace_id)          │
├──────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐ │
│  │  api_keys   │  │  contacts   │  │  webhooks   │  │transactions │ │
│  └─────────────┘  └─────────────┘  └─────────────┘  └─────────────┘ │
│         │                │                │                │        │
│         │                │                │                │        │
│         └────────────────┼────────────────┼────────────────┘        │
│                          │                │                          │
│                    ┌─────┴─────┐    ┌─────┴──────┐                   │
│                    │  messages │    │  webhook   │                   │
│                    │           │    │ deliveries │                   │
│                    └───────────┘    └────────────┘                   │
│                                                                      │
│  ┌──────────────────┐  ┌──────────────────┐  ┌────────────────────┐ │
│  │ refresh_tokens   │  │ verification     │  │    blog_posts      │ │
│  │                  │  │ _tokens          │  │   (standalone)     │ │
│  └──────────────────┘  └──────────────────┘  └────────────────────┘ │
└──────────────────────────────────────────────────────────────────────┘

Table: `workspaces`

Central entity representing customer accounts.

Schema

Column	Type	Constraints	Default	Notes
`id`	UUID	PK	`gen_random_uuid()`
`name`	VARCHAR(255)	NOT NULL		Company/workspace name
`email`	VARCHAR(255)	UNIQUE, NOT NULL		Login email
`password_hash`	VARCHAR(255)	NOT NULL		bcrypt hash
`phone`	VARCHAR(50)			E.164 format
`country`	VARCHAR(2)		`'SZ'`	ISO 3166-1 alpha-2
`credits_balance`	DECIMAL(12,2)	NOT NULL	`0.00`	Current balance
`email_verified`	BOOLEAN		`FALSE`
`is_active`	BOOLEAN		`TRUE`
`company_name`	VARCHAR(255)
`industry`	VARCHAR(100)
`use_cases`	TEXT[]
`monthly_volume`	VARCHAR(50)
`onboarding_completed`	BOOLEAN		`FALSE`
`sms_sender_name`	VARCHAR(11)			Alphanumeric sender ID
`created_at`	TIMESTAMP		`NOW()`
`updated_at`	TIMESTAMP		`NOW()`

Model: `WorkspaceModel`

typescript

// Create workspace
static async create(data: {
  name: string;
  email: string;
  password_hash: string;
  phone?: string;
  country?: string;
}): Promise<Workspace>

// Find by email (login)
static async findByEmail(email: string): Promise<Workspace | null>

// Find by ID
static async findById(id: string): Promise<Workspace | null>

// Verify email
static async verifyEmail(id: string): Promise<void>

// Update password
static async updatePassword(id: string, password_hash: string): Promise<void>

// Update profile
static async update(id: string, data: {
  name?: string;
  phone?: string;
  sms_sender_name?: string | null;
  company_name?: string;
  industry?: string;
  // ...
}): Promise<Workspace>

// Get balance
static async getBalance(id: string): Promise<number>

// Deduct credits (atomic, via stored procedure)
static async deductCredits(
  workspaceId: string,
  amount: number,
  messageId: string,
  description: string
): Promise<boolean>

// Add credits (atomic, via stored procedure)
static async addCredits(
  workspaceId: string,
  amount: number,
  stripePaymentId: string,
  description: string
): Promise<number>

Table: `api_keys`

API keys for programmatic access.

Schema

Column	Type	Constraints	Default	Notes
`id`	UUID	PK	`gen_random_uuid()`
`workspace_id`	UUID	FK → workspaces
`name`	VARCHAR(100)	NOT NULL		User-friendly name
`key_hash`	VARCHAR(255)	UNIQUE, NOT NULL		HMAC-SHA256 hash
`key_prefix`	VARCHAR(15)	NOT NULL		`ybk_xxx...` for display
`scopes`	TEXT[]		`['send_messages', 'read_messages']`
`is_active`	BOOLEAN		`TRUE`
`last_used_at`	TIMESTAMP
`created_at`	TIMESTAMP		`NOW()`

Model: `ApiKeyModel`

typescript

// Create API key
static async create(data: {
  workspace_id: string;
  name: string;
  key_hash: string;
  key_prefix: string;
  scopes?: string[];
}): Promise<ApiKey>

// Find by hash (authentication)
static async findByHash(keyHash: string): Promise<ApiKey | null>

// List workspace keys
static async findByWorkspace(workspaceId: string): Promise<ApiKey[]>

// Update last used timestamp
static async updateLastUsed(id: string): Promise<void>

// Deactivate key
static async deactivate(id: string, workspaceId: string): Promise<void>

Table: `messages`

All sent/received messages.

Schema

Column	Type	Constraints	Default	Notes
`id`	UUID	PK	`gen_random_uuid()`
`workspace_id`	UUID	FK → workspaces
`conversation_id`	UUID	FK → conversations		Optional
`api_key_id`	UUID	FK → api_keys		If sent via API key
`channel`	VARCHAR(20)	NOT NULL		`sms`, `whatsapp`, `email`, etc.
`direction`	VARCHAR(10)	NOT NULL		`outbound`, `inbound`
`sender`	VARCHAR(255)	NOT NULL
`recipient`	VARCHAR(255)	NOT NULL
`content_type`	VARCHAR(20)	NOT NULL		`text`, `media`, `template`
`content`	JSONB	NOT NULL		Message content
`provider`	VARCHAR(50)	NOT NULL		`twilio`, `resend`, etc.
`provider_message_id`	VARCHAR(255)			Provider's message ID
`status`	VARCHAR(20)		`'queued'`
`error_message`	TEXT
`credits_used`	DECIMAL(10,2)		`0`
`metadata`	JSONB		`'{}'`
`parent_message_id`	UUID			For replies
`created_at`	TIMESTAMP		`NOW()`
`sent_at`	TIMESTAMP
`delivered_at`	TIMESTAMP
`read_at`	TIMESTAMP
`failed_at`	TIMESTAMP

Indexes

workspace_id (foreign key)
provider_message_id (lookup by provider callback)
created_at (descending, for recent messages)
(workspace_id, channel, status) (filtered queries)

Model: `MessageModel`

typescript

// Create message
static async create(data: {
  workspace_id: string;
  conversation_id?: string;
  api_key_id?: string;
  channel: string;
  direction: 'outbound' | 'inbound';
  sender: string;
  recipient: string;
  content_type: string;
  content: Record<string, any>;
  provider: string;
  credits_used: number;
  metadata?: Record<string, any>;
}): Promise<Message>

// Find by ID
static async findById(id: string, workspaceId: string): Promise<Message | null>

// List with filters
static async findByWorkspace(
  workspaceId: string,
  filters: {
    channel?: string;
    status?: string;
    startDate?: Date;
    endDate?: Date;
    limit?: number;
    offset?: number;
  }
): Promise<{ messages: Message[]; total: number }>

// Update status
static async updateStatus(
  id: string,
  status: 'sent' | 'delivered' | 'failed' | 'read',
  additionalData?: {
    provider_message_id?: string;
    error_message?: string;
  }
): Promise<void>

// Find by provider ID (for status callbacks)
static async findByProviderMessageId(providerMessageId: string): Promise<Message | null>

// Usage statistics
static async getUsageStats(
  workspaceId: string,
  startDate: Date,
  endDate: Date
): Promise<{
  totalMessages: number;
  totalCredits: number;
  byChannel: Array<{ channel: string; count: number; credits: number }>;
  byStatus: Array<{ status: string; count: number }>;
}>

Table: `contacts`

Contact database for messaging.

Schema

Column	Type	Constraints	Default	Notes
`id`	UUID	PK	`gen_random_uuid()`
`workspace_id`	UUID	FK → workspaces
`phone`	VARCHAR(50)			E.164 format
`email`	VARCHAR(255)
`name`	VARCHAR(255)
`custom_fields`	JSONB		`'{}'`	Arbitrary metadata
`channel_preferences`	JSONB			Preferred channel, opt-outs
`tags`	TEXT[]		`'{}'`	For segmentation
`created_at`	TIMESTAMP		`NOW()`
`updated_at`	TIMESTAMP		`NOW()`

Unique Constraint

UNIQUE(workspace_id, phone) WHERE phone IS NOT NULL
UNIQUE(workspace_id, email) WHERE email IS NOT NULL

Model: `ContactModel`

typescript

// Create contact
static async create(data: {
  workspace_id: string;
  phone?: string;
  email?: string;
  name?: string;
  custom_fields?: Record<string, any>;
  tags?: string[];
}): Promise<Contact>

// Find or create (for message sending)
static async findOrCreate(data: {
  workspace_id: string;
  phone?: string;
  email?: string;
  name?: string;
}): Promise<Contact>

// Search with filters
static async findByWorkspace(
  workspaceId: string,
  filters: {
    search?: string;  // ILIKE on name, phone, email
    tags?: string[];  // Array overlap
    limit?: number;
    offset?: number;
  }
): Promise<{ contacts: Contact[]; total: number }>

// Tag management
static async addTags(id: string, workspaceId: string, tags: string[]): Promise<void>
static async removeTags(id: string, workspaceId: string, tags: string[]): Promise<void>

Table: `transactions`

Credit transaction ledger.

Schema

Column	Type	Constraints	Default	Notes
`id`	UUID	PK	`gen_random_uuid()`
`workspace_id`	UUID	FK → workspaces
`type`	VARCHAR(20)	NOT NULL		`purchase`, `usage`, `refund`, `adjustment`
`amount`	DECIMAL(12,2)	NOT NULL		Positive = credit, negative = debit
`balance_after`	DECIMAL(12,2)	NOT NULL		Running balance
`description`	TEXT
`message_id`	UUID			For usage transactions
`stripe_payment_id`	VARCHAR(255)			Stripe payment intent
`stripe_checkout_session_id`	VARCHAR(255)			Stripe session
`metadata`	JSONB		`'{}'`
`created_at`	TIMESTAMP		`NOW()`

Model: `TransactionModel`

typescript

// Query transactions
static async findByWorkspace(
  workspaceId: string,
  filters: {
    type?: string;
    startDate?: Date;
    endDate?: Date;
    limit?: number;
    offset?: number;
  }
): Promise<{ transactions: Transaction[]; total: number }>

// Check for duplicate Stripe session
static async findByStripeCheckoutSession(sessionId: string): Promise<Transaction | null>

// Statistics
static async getStats(
  workspaceId: string,
  startDate: Date,
  endDate: Date
): Promise<{
  totalPurchased: number;
  totalUsed: number;
  totalRefunded: number;
  byType: Array<{ type: string; total: number; count: number }>;
}>

Table: `webhooks`

Customer webhook endpoints.

Schema

Column	Type	Constraints	Default	Notes
`id`	UUID	PK	`gen_random_uuid()`
`workspace_id`	UUID	FK → workspaces
`url`	VARCHAR(500)	NOT NULL		HTTPS endpoint
`secret`	VARCHAR(255)	NOT NULL		SHA256 hash of signing secret
`events`	TEXT[]	NOT NULL		Subscribed events
`is_active`	BOOLEAN		`TRUE`
`last_triggered_at`	TIMESTAMP
`failure_count`	INTEGER		`0`	Consecutive failures
`created_at`	TIMESTAMP		`NOW()`

Model: `WebhookModel`

typescript

// Create webhook
static async create(data: {
  workspace_id: string;
  url: string;
  secret: string;  // Stored as hash
  events: string[];
}): Promise<Webhook>

// Find active webhooks for event
static async findActiveByEvent(workspaceId: string, event: string): Promise<Webhook[]>

// Update failure tracking
static async incrementFailureCount(id: string): Promise<number>
static async resetFailureCount(id: string): Promise<void>

Table: `webhook_deliveries`

Webhook delivery attempts (for debugging).

Schema

Column	Type	Constraints	Default	Notes
`id`	UUID	PK	`gen_random_uuid()`
`webhook_id`	UUID	FK → webhooks
`event_type`	VARCHAR(50)	NOT NULL
`payload`	JSONB	NOT NULL		Sent payload
`response_status`	INTEGER			HTTP status
`response_body`	TEXT			Response (truncated)
`error`	TEXT			Error message
`delivered_at`	TIMESTAMP		`NOW()`

Table: `refresh_tokens`

JWT refresh tokens.

Schema

Column	Type	Constraints	Default	Notes
`id`	UUID	PK	`gen_random_uuid()`
`workspace_id`	UUID	FK → workspaces
`token`	VARCHAR(255)	UNIQUE, NOT NULL		128-char hex
`expires_at`	TIMESTAMP	NOT NULL		30 days from creation
`created_at`	TIMESTAMP		`NOW()`
`revoked_at`	TIMESTAMP

Model: `RefreshTokenModel`

typescript

static async create(workspaceId: string, token: string, expiresAt: Date): Promise<RefreshToken>
static async findByToken(token: string): Promise<RefreshToken | null>  // Excludes revoked/expired
static async revoke(token: string): Promise<void>
static async revokeAllForWorkspace(workspaceId: string): Promise<void>
static async cleanup(): Promise<void>  // Delete old tokens

Table: `verification_tokens`

Email verification & password reset tokens.

Schema

Column	Type	Constraints	Default	Notes
`id`	UUID	PK	`gen_random_uuid()`
`workspace_id`	UUID	FK → workspaces
`token`	VARCHAR(255)	NOT NULL		64-char hex
`type`	VARCHAR(50)	NOT NULL		`email_verification`, `password_reset`
`expires_at`	TIMESTAMP	NOT NULL
`used_at`	TIMESTAMP
`created_at`	TIMESTAMP		`NOW()`

Table: `blog_posts`

Blog content for autoblogger.

Schema

Column	Type	Constraints	Default	Notes
`id`	UUID	PK	`gen_random_uuid()`
`title`	VARCHAR(500)	NOT NULL
`slug`	VARCHAR(500)	UNIQUE, NOT NULL		URL-safe identifier
`content`	TEXT	NOT NULL		Full HTML/markdown
`excerpt`	TEXT			Short summary
`category`	VARCHAR(100)
`tags`	TEXT[]		`'{}'`
`status`	VARCHAR(20)		`'published'`	`draft`, `published`, `archived`
`author`	VARCHAR(100)		`'AI'`
`featured_image`	TEXT			Image URL
`published_at`	TIMESTAMP		`NOW()`
`created_at`	TIMESTAMP		`NOW()`
`updated_at`	TIMESTAMP		`NOW()`

Stored Procedures

`deduct_credits(workspace_id, amount, message_id, description)`

Atomic credit deduction with transaction logging.

sql

CREATE OR REPLACE FUNCTION deduct_credits(
  p_workspace_id UUID,
  p_amount DECIMAL,
  p_message_id UUID,
  p_description TEXT
) RETURNS BOOLEAN AS $$
DECLARE
  v_balance DECIMAL;
BEGIN
  -- Lock row for update
  SELECT credits_balance INTO v_balance
  FROM workspaces WHERE id = p_workspace_id
  FOR UPDATE;
  
  IF v_balance < p_amount THEN
    RETURN FALSE;
  END IF;
  
  -- Deduct credits
  UPDATE workspaces
  SET credits_balance = credits_balance - p_amount
  WHERE id = p_workspace_id;
  
  -- Log transaction
  INSERT INTO transactions (workspace_id, type, amount, balance_after, message_id, description)
  VALUES (p_workspace_id, 'usage', -p_amount, v_balance - p_amount, p_message_id, p_description);
  
  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

`add_credits(workspace_id, amount, stripe_payment_id, description)`

Atomic credit addition with transaction logging.

sql

CREATE OR REPLACE FUNCTION add_credits(
  p_workspace_id UUID,
  p_amount DECIMAL,
  p_stripe_payment_id TEXT,
  p_description TEXT
) RETURNS DECIMAL AS $$
DECLARE
  v_new_balance DECIMAL;
BEGIN
  UPDATE workspaces
  SET credits_balance = credits_balance + p_amount
  WHERE id = p_workspace_id
  RETURNING credits_balance INTO v_new_balance;
  
  INSERT INTO transactions (workspace_id, type, amount, balance_after, stripe_payment_id, description)
  VALUES (p_workspace_id, 'purchase', p_amount, v_new_balance, p_stripe_payment_id, p_description);
  
  RETURN v_new_balance;
END;
$$ LANGUAGE plpgsql;

API

Backend API

Okia AI Service

Frontend

Student Dashboard

Teacher Dashboard

Parent Dashboard

School Admin Dashboard

YeboLink Database Models Deep Dive ​

Database Architecture ​

Table: workspaces ​

Schema ​

Model: WorkspaceModel ​

Table: api_keys ​

Schema ​

Model: ApiKeyModel ​

Table: messages ​

Schema ​

Indexes ​

Model: MessageModel ​

Table: contacts ​

Schema ​

Unique Constraint ​

Model: ContactModel ​

Table: transactions ​

Schema ​

Model: TransactionModel ​

Table: webhooks ​

Schema ​

Model: WebhookModel ​

Table: webhook_deliveries ​

Schema ​

Table: refresh_tokens ​

Schema ​

Model: RefreshTokenModel ​

Table: verification_tokens ​

Schema ​

Table: blog_posts ​

Schema ​

Stored Procedures ​

deduct_credits(workspace_id, amount, message_id, description) ​

add_credits(workspace_id, amount, stripe_payment_id, description) ​

YeboLink Database Models Deep Dive

Database Architecture

Table: `workspaces`

Schema

Model: `WorkspaceModel`

Table: `api_keys`

Schema

Model: `ApiKeyModel`

Table: `messages`

Schema

Indexes

Model: `MessageModel`

Table: `contacts`

Schema

Unique Constraint

Model: `ContactModel`

Table: `transactions`

Schema

Model: `TransactionModel`

Table: `webhooks`

Schema

Model: `WebhookModel`

Table: `webhook_deliveries`

Schema

Table: `refresh_tokens`

Schema

Model: `RefreshTokenModel`

Table: `verification_tokens`

Schema

Table: `blog_posts`

Schema

Stored Procedures

`deduct_credits(workspace_id, amount, message_id, description)`

`add_credits(workspace_id, amount, stripe_payment_id, description)`