Database Schema

Supabase PostgreSQL — 10 tables, RLS-enforced

HostelHack uses Supabase (managed Postgres) with Row-Level Security on every table. The schema supports the full lifecycle: user profiles, email connectivity, hostel discovery cache, proposal management, and AI enrichments.

Entity Relationship Overview

┌─────────────┐       ┌─────────────────────────┐
│   users     │       │ connected_mail_accounts  │
│─────────────│  1:N  │─────────────────────────│
│ id (PK)     │◄──────│ user_id (FK)            │
│ clerk_id    │       │ provider                │
│ email       │       │ access_token            │
│ role        │       │ sync_cursor             │
└──────┬──────┘       └───────────┬─────────────┘
       │                          │ 1:N
       │ 1:N              ┌──────┴──────┐
       │                  │ mail_threads │
┌──────┴──────┐           │─────────────│
│  proposals  │           │ subject     │
│─────────────│           │ snippet     │
│ user_id     │           │ is_unread   │
│ hostel_name │           └──────┬──────┘
│ status      │                  │ 1:N
│ terms (JSON)│           ┌──────┴───────┐
└──────┬──────┘           │ mail_messages│
       │                  └──────┬───────┘
       │                         │ 1:N
  ┌────┴──────────┐    ┌────────┴─────────┐
  │mail_thread    │    │ mail_attachments  │
  │  _links       │    └──────────────────┘
  └───────────────┘
           ┌─────────────────┐  ┌──────────────┐
           │mail_ai_enrichm. │  │ search_cache  │
           └─────────────────┘  └──────────────┘

Table Schemas

Click any table to expand its complete column definition.

usersCore user profiles linked to Clerk auth14 cols

Column	Type	Notes
id	uuid	Primary key, default gen_random_uuid()
clerk_id	text	Unique, Clerk external ID
email	text	Unique
full_name	text	—
display_name	text	—
headline	text	Professional tagline
bio	text	—
skills	text[]	Array of skill names
avatar_url	text	—
role	text	nomad \| hostel \| admin
location	text	—
is_onboarded	boolean	default false
created_at	timestamptz	—
updated_at	timestamptz	—

connected_mail_accountsOAuth-linked email accounts for unified inbox14 cols

Column	Type	Notes
id	uuid	Primary key
user_id	uuid	FK → users.id
provider	text	google \| microsoft
email_address	text	Account email
access_token	text	Encrypted OAuth token
refresh_token	text	Encrypted refresh token
token_expires_at	timestamptz	—
sync_cursor	text	pageToken / deltaLink
history_id	text	Gmail history ID
subscription_id	text	Webhook subscription ID
subscription_expires_at	timestamptz	—
is_default	boolean	Default send account
last_synced_at	timestamptz	—
created_at	timestamptz	—

mail_threadsEmail conversation threads aggregated from messages14 cols

Column	Type	Notes
id	uuid	Primary key
account_id	uuid	FK → connected_mail_accounts.id
provider_thread_id	text	Gmail/Outlook thread ID
subject	text	—
snippet	text	Preview text
from_address	text	—
to_addresses	text[]	—
is_unread	boolean	—
is_starred	boolean	—
is_archived	boolean	—
label_ids	text[]	Gmail labels / Outlook categories
message_count	integer	—
last_message_at	timestamptz	—
created_at	timestamptz	—

mail_messagesIndividual email messages within threads13 cols

Column	Type	Notes
id	uuid	Primary key
thread_id	uuid	FK → mail_threads.id
account_id	uuid	FK → connected_mail_accounts.id
provider_message_id	text	—
from_address	text	—
to_addresses	text[]	—
cc_addresses	text[]	—
subject	text	—
body_text	text	—
body_html	text	—
sent_at	timestamptz	—
is_outbound	boolean	—
created_at	timestamptz	—

mail_attachmentsFile attachments linked to email messages8 cols

Column	Type	Notes
id	uuid	Primary key
message_id	uuid	FK → mail_messages.id
filename	text	—
mime_type	text	—
size_bytes	integer	—
provider_attachment_id	text	—
storage_path	text	—
created_at	timestamptz	—

mail_thread_linksLinks threads to proposals for deal tracking5 cols

Column	Type	Notes
id	uuid	Primary key
thread_id	uuid	FK → mail_threads.id
proposal_id	uuid	FK → proposals.id
link_type	text	outbound \| inbound \| auto
created_at	timestamptz	—

mail_ai_enrichmentsAI-generated metadata for intelligent inbox features8 cols

Column	Type	Notes
id	uuid	Primary key
thread_id	uuid	FK → mail_threads.id
sentiment	text	positive \| neutral \| negative
intent	text	inquiry \| acceptance \| rejection \| negotiation
summary	text	AI-generated thread summary
suggested_reply	text	—
priority_score	integer	0-100
created_at	timestamptz	—

mail_sync_eventsAudit log for sync operations8 cols

Column	Type	Notes
id	uuid	Primary key
account_id	uuid	FK → connected_mail_accounts.id
event_type	text	full \| delta \| webhook
status	text	started \| completed \| failed
messages_synced	integer	—
error_message	text	—
started_at	timestamptz	—
completed_at	timestamptz	—

proposalsSkill-exchange proposals between nomads and hostels13 cols

Column	Type	Notes
id	uuid	Primary key
user_id	text	Clerk user ID
hostel_name	text	—
hostel_id	text	—
status	text	draft \| pending \| accepted \| rejected \| negotiating
terms	jsonb	Duration, deliverables, accommodation
initial_message	text	—
recipient_email	text	—
email_sent	boolean	—
email_sent_at	timestamptz	—
email_message_id	text	For reply tracking
created_at	timestamptz	—
updated_at	timestamptz	—

search_cacheCached AI search results to reduce API calls7 cols

Column	Type	Notes
id	uuid	Primary key
query_hash	text	SHA-256 of search params
location	text	—
skills	text[]	—
results	jsonb	Cached hostel results
expires_at	timestamptz	TTL for cache invalidation
created_at	timestamptz	—

Row-Level Security

RLS is enabled on every table. Policies ensure users can only access their own data.

Table	Policy	Rule
users	Users can read/update own row	auth.uid() = clerk_id
connected_mail_accounts	Users can CRUD own accounts	user_id = auth.uid()
mail_threads	Users can read own threads via account	account_id IN (SELECT id FROM connected_mail_accounts WHERE user_id = auth.uid())
mail_messages	Users can read own messages	thread_id IN (user's thread IDs)
proposals	Users can CRUD own proposals	user_id = auth.uid()
search_cache	Read-only for authenticated users	auth.role() = 'authenticated'

Migrations

11 migrations applied — all versioned in supabase/migrations/

20260325173000

20260325173000_unified_inbox.sql

Core inbox schema — accounts, threads, messages, attachments, thread links, AI enrichments, sync events

20260325180000

20260325180000_profile_photos.sql

Add avatar storage bucket + policies

20260326120000

20260326120000_profile_updates.sql

Additional profile fields — headline, bio, skills

20260326130000

20260326130000_storage_buckets.sql

Supabase storage bucket configuration

20260326140000

20260326140000_users_rls_fix.sql

Fix RLS policies for user table

20260326150000

20260326150000_users_missing_columns.sql

Add missing user columns (display_name, location, etc.)

20260326160000

20260326160000_oauth_tokens.sql

OAuth token storage columns for mail accounts

20260326170000

20260326170000_search_cache.sql

Search cache table with TTL

20260326180001

20260326180001_proposals.sql

Proposals table with JSONB terms

20260326190000

20260326190000_proposals_email_tracking.sql

Add email tracking columns to proposals

20260326200000

20260326200000_proposals_drop_fk.sql

Drop strict FK constraint for flexibility

Realtime Subscriptions

Inbox Threads

Subscribe to mail_threads INSERT/UPDATE events. Powers real-time unread counts and new thread notifications.

supabase
  .channel('inbox')
  .on('postgres_changes', {
    event: '*',
    schema: 'public',
    table: 'mail_threads',
    filter: `account_id=eq.${accountId}`
  }, handleChange)
  .subscribe()

Proposal Updates

Subscribe to proposals status changes. Instant notification when a hostel accepts/rejects.

supabase
  .channel('proposals')
  .on('postgres_changes', {
    event: 'UPDATE',
    schema: 'public',
    table: 'proposals',
    filter: `user_id=eq.${userId}`
  }, handleUpdate)
  .subscribe()