Database Schema

Every table below is accessible via the Supabase client. All queries respect row-level security — you only see data from organizations where you’re a team member.

organizations

Top-level billing entity. Every user belongs to at least one.

Column	Type	Default	Description
id	uuid	auto	Primary key
name	text	required	Organization name
slug	text	unique, required	URL-safe identifier
plan	text	'free'	free, growth, or pro
monthly_message_limit	integer	50	AI replies allowed per month
messages_used_this_month	integer	0	Current usage count
stripe_customer_id	text	null	Stripe billing link
created_at	timestamptz	now()

const { data: org } = await sendhub
  .from('organizations')
  .select('id, name, plan, messages_used_this_month, monthly_message_limit')
  .single()

---

businesses

Workspaces within an organization. Each has its own AI config, channels, and contacts.

Column	Type	Default	Description
id	uuid	auto	Primary key
org_id	uuid	required	Parent organization
name	text	required	Workspace name
slug	text	required	Unique within org
ai_enabled	boolean	true	AI auto-reply on/off
ai_system_prompt	text	null	Default AI prompt for this workspace
ai_model	text	'gemini-2.5-flash'	AI model to use
ai_temperature	real	0.7	AI creativity (0–1)
created_at	timestamptz	now()

const { data: workspaces } = await sendhub
  .from('businesses')
  .select('id, name, ai_enabled, ai_system_prompt')
  .eq('org_id', orgId)

---

channels

Connected WhatsApp numbers — business API or personal bridge.

Column	Type	Default	Description
id	uuid	auto	Primary key
business_id	uuid	required	Parent workspace
org_id	uuid	required	Parent organization
platform	text	'whatsapp'	Always whatsapp
channel_type	text		'business' or 'personal'
phone_number	text	null	E.164 phone number
phone_number_id	text	unique	Meta API phone number ID
waba_id	text	null	WhatsApp Business Account ID
access_token	text	null	Meta API access token
label	text	'Main'	Display name
active	boolean	true	Channel enabled
ai_prompt_override	text	null	Per-channel AI prompt
ai_delay_seconds	integer	10	Seconds to wait before AI replies
ai_confidence_threshold	integer	80	Below this → draft, above → auto-send
ai_auto_send	boolean	false	When false, all AI replies are saved as drafts for human review
matrix_user_id	uuid	null	Device link user (personal channels)
created_at	timestamptz	now()

const { data: channels } = await sendhub
  .from('channels')
  .select('id, label, phone_number, channel_type, active, ai_delay_seconds, ai_confidence_threshold, ai_auto_send')
  .eq('business_id', workspaceId)
  .eq('active', true)

---

contacts

WhatsApp users who have messaged your channels. Created automatically on first message.

Column	Type	Default	Description
id	uuid	auto	Primary key
business_id	uuid	required	Parent workspace
org_id	uuid	required	Parent organization
platform	text	'whatsapp'
platform_id	text	required	WhatsApp phone number
name	text	null	Display name
phone	text	null	Formatted phone number
avatar_url	text	null	Profile picture URL
tags	text[]	'{}'	Array of tag labels
notes	text	null	Free-text notes
created_at	timestamptz	now()

// Search contacts by name or phone
const { data: contacts } = await sendhub
  .from('contacts')
  .select('id, name, phone, tags, avatar_url')
  .eq('business_id', workspaceId)
  .or(`name.ilike.%${query}%,phone.ilike.%${query}%`)
  .order('name')
  .limit(50)

// Update tags
await sendhub
  .from('contacts')
  .update({ tags: ['vip', 'returning'] })
  .eq('id', contactId)

---

conversations

A thread between a contact and your team/AI on a specific channel.

Column	Type	Default	Description
id	uuid	auto	Primary key
business_id	uuid	required	Parent workspace
org_id	uuid	required	Parent organization
channel_id	uuid	required	WhatsApp channel
contact_id	uuid	required	The contact
external_id	text	null	Bridge room ID (personal channels)
status	text	'ai_handling'	ai_handling, assigned, resolved
assigned_to	uuid	null	Team member ID
last_message_at	timestamptz	now()
created_at	timestamptz	now()

// List conversations with related data
const { data } = await sendhub
  .from('conversations')
  .select(`
    id, status, assigned_to, last_message_at,
    contacts(id, name, phone, tags, avatar_url),
    channels(label, phone_number, channel_type)
  `)
  .eq('business_id', workspaceId)
  .eq('status', 'ai_handling')
  .order('last_message_at', { ascending: false })

// Assign to a team member
await sendhub
  .from('conversations')
  .update({ assigned_to: memberId, status: 'assigned' })
  .eq('id', convoId)

// Resolve
await sendhub
  .from('conversations')
  .update({ status: 'resolved' })
  .eq('id', convoId)

---

messages

Individual messages within a conversation.

Column	Type	Default	Description
id	uuid	auto	Primary key
conversation_id	uuid	required	Parent conversation
business_id	uuid	required	Parent workspace
org_id	uuid	required	Parent organization
direction	text	required	'inbound' or 'outbound'
sender_type	text	required	'contact', 'ai', or 'agent'
sender_id	uuid	null	Team member ID (for agent messages)
content	text	required	Message text
media_url	text	null	Attachment URL
media_type	text	null	MIME type of attachment
wa_message_id	text	null	WhatsApp message ID (deduplication)
delivery_status	text	null	queued → sending → sent → delivered → read / failed
is_draft	boolean	false	AI draft awaiting human review
confidence	integer	null	AI confidence score (0–100)
read	boolean	false	Read by agent
created_at	timestamptz	now()

// List messages in a conversation
const { data: messages } = await sendhub
  .from('messages')
  .select('id, direction, sender_type, content, delivery_status, confidence, is_draft, created_at')
  .eq('conversation_id', convoId)
  .order('created_at', { ascending: true })

// Send a message
const { data } = await sendhub.from('messages').insert({
  conversation_id: convoId,
  business_id: workspaceId,
  org_id: orgId,
  direction: 'outbound',
  sender_type: 'agent',
  content: 'Your order has shipped!'
}).select('id').single()

// Search messages
const { data: results } = await sendhub
  .from('messages')
  .select('id, conversation_id, content, sender_type, created_at')
  .ilike('content', `%${searchQuery}%`)
  .order('created_at', { ascending: false })
  .limit(50)

---

team_members

Users with access to an organization.

Column	Type	Default	Description
id	uuid	auto	Primary key
org_id	uuid	required	Parent organization
user_id	uuid	required	Supabase auth user
name	text	required	Display name
email	text	required	Email address
org_role	text	'agent'	owner, admin, or agent
active	boolean	true
deleted_at	timestamptz	null	Soft delete
created_at	timestamptz	now()

const { data: team } = await sendhub
  .from('team_members')
  .select('id, name, email, org_role, active')
  .eq('org_id', orgId)
  .is('deleted_at', null)

---

team_assignments

Maps team members to specific workspaces with a role.

Column	Type	Default	Description
id	uuid	auto	Primary key
team_member_id	uuid	required
business_id	uuid	required
role	text	'agent'	Role within this workspace
created_at	timestamptz	now()

---

api_keys

Programmatic access keys scoped to an organization.

Column	Type	Default	Description
id	uuid	auto	Primary key
org_id	uuid	required
business_id	uuid	null	Optional workspace scope
key_hash	text	unique	Hashed API secret key
key_prefix	text	required	First 8 chars for identification
label	text	'Default'
scopes	text[]	'{read,write}'	Permission scopes
last_used_at	timestamptz	null
created_at	timestamptz	now()

---

Real-time Subscriptions

Subscribe to changes on any table:

// New messages across all conversations
sendhub
  .channel('all-messages')
  .on('postgres_changes', {
    event: 'INSERT',
    schema: 'public',
    table: 'messages',
    filter: `business_id=eq.${workspaceId}`,
  }, (payload) => {
    console.log('New message:', payload.new)
  })
  .subscribe()

// Conversation status changes
sendhub
  .channel('convo-updates')
  .on('postgres_changes', {
    event: 'UPDATE',
    schema: 'public',
    table: 'conversations',
  }, (payload) => {
    console.log('Status changed:', payload.new.status)
  })
  .subscribe()

// Delivery status tracking
sendhub
  .channel('delivery')
  .on('postgres_changes', {
    event: 'UPDATE',
    schema: 'public',
    table: 'messages',
    filter: `conversation_id=eq.${convoId}`,
  }, (payload) => {
    console.log('Delivery:', payload.new.delivery_status)
  })
  .subscribe()