Supabase Architecture

Overview

Solatis is built on Supabase, an open-source Backend-as-a-Service (BaaS) providing PostgreSQL database, authentication, real-time subscriptions, and Edge Functions.

Architecture Components

Database Layer

PostgreSQL 15:

Primary data store
ACID compliance
Full SQL support
Advanced indexing
JSON/JSONB support

pgvector Extension:

Vector similarity search
Semantic embeddings storage
1536-dimensional vectors
Cosine similarity queries
HNSW indexing

PostgREST API:

Auto-generated REST API
Direct database queries
Row Level Security (RLS) enforced
Automatic OpenAPI documentation

Schema Design

Core Tables:

sql

-- Users & Authentication
users (
  id uuid PRIMARY KEY,
  email text UNIQUE,
  full_name text,
  avatar_url text,
  created_at timestamp,
  updated_at timestamp
)

-- Organizations
organizations (
  id uuid PRIMARY KEY,
  name text,
  slug text UNIQUE,
  owner_id uuid REFERENCES users(id),
  created_at timestamp
)

-- Workspaces
workspaces (
  id uuid PRIMARY KEY,
  organization_id uuid REFERENCES organizations(id),
  name text,
  description text,
  created_at timestamp
)

-- Documents
documents (
  id uuid PRIMARY KEY,
  workspace_id uuid REFERENCES workspaces(id),
  user_id uuid REFERENCES users(id),
  title text,
  content text,
  file_url text,
  file_type text,
  file_size bigint,
  processing_status text,
  metadata jsonb,
  created_at timestamp,
  updated_at timestamp
)

-- Document Embeddings
document_embeddings (
  id uuid PRIMARY KEY,
  document_id uuid REFERENCES documents(id),
  chunk_index integer,
  content text,
  embedding vector(1536),
  metadata jsonb,
  created_at timestamp
)

-- Meetings
meetings (
  id uuid PRIMARY KEY,
  workspace_id uuid REFERENCES workspaces(id),
  title text,
  start_time timestamp,
  end_time timestamp,
  calendar_event_id text,
  recording_url text,
  transcript_url text,
  summary text,
  participants jsonb,
  created_at timestamp
)

-- Conversations
conversations (
  id uuid PRIMARY KEY,
  workspace_id uuid REFERENCES workspaces(id),
  user_id uuid REFERENCES users(id),
  title text,
  context_documents uuid[],
  messages jsonb,
  created_at timestamp,
  updated_at timestamp
)

-- Integrations
integrations (
  id uuid PRIMARY KEY,
  organization_id uuid REFERENCES organizations(id),
  type text, -- 'google', 'slack', 'jira', etc.
  credentials jsonb, -- encrypted
  settings jsonb,
  status text,
  last_sync timestamp,
  created_at timestamp
)

Row Level Security (RLS)

Security Model: Every table has RLS policies that enforce data isolation.

Example Policies:

sql

-- Users can only see their own documents or shared ones
CREATE POLICY "Users can view own documents"
ON documents FOR SELECT
USING (
  auth.uid() = user_id
  OR workspace_id IN (
    SELECT workspace_id FROM workspace_members
    WHERE user_id = auth.uid()
  )
);

-- Users can only insert documents in workspaces they belong to
CREATE POLICY "Users can insert documents"
ON documents FOR INSERT
WITH CHECK (
  workspace_id IN (
    SELECT workspace_id FROM workspace_members
    WHERE user_id = auth.uid()
    AND permission IN ('editor', 'admin')
  )
);

-- Users can only update their own documents
CREATE POLICY "Users can update own documents"
ON documents FOR UPDATE
USING (auth.uid() = user_id);

-- Users can only delete their own documents
CREATE POLICY "Users can delete own documents"
ON documents FOR DELETE
USING (auth.uid() = user_id);

Benefits:

Database-enforced security
No application-level bugs
Automatic filtering
Centralized permission logic

Vector Search Implementation

Semantic Search Query:

sql

-- Find similar documents using pgvector
CREATE OR REPLACE FUNCTION search_documents(
  query_embedding vector(1536),
  match_threshold float,
  match_count int,
  filter_workspace_id uuid DEFAULT NULL
)
RETURNS TABLE (
  id uuid,
  document_id uuid,
  content text,
  similarity float
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    document_embeddings.id,
    document_embeddings.document_id,
    document_embeddings.content,
    1 - (document_embeddings.embedding <=> query_embedding) as similarity
  FROM document_embeddings
  JOIN documents ON documents.id = document_embeddings.document_id
  WHERE 
    (filter_workspace_id IS NULL OR documents.workspace_id = filter_workspace_id)
    AND (1 - (document_embeddings.embedding <=> query_embedding)) > match_threshold
  ORDER BY similarity DESC
  LIMIT match_count;
END;
$$;

-- Usage
SELECT * FROM search_documents(
  '[0.1, 0.2, ...]'::vector(1536),
  0.7, -- threshold
  20, -- limit
  'workspace-uuid' -- filter
);

Indexing:

sql

-- Create HNSW index for fast similarity search
CREATE INDEX document_embeddings_embedding_idx 
ON document_embeddings 
USING hnsw (embedding vector_cosine_ops);

-- GiST index for filtering
CREATE INDEX documents_workspace_idx
ON documents (workspace_id);

Authentication & Authorization

Supabase Auth

Supported Methods:

Email/Password
Magic Links
OAuth providers (Google, Microsoft, GitHub)
SSO/SAML (Enterprise)
Two-Factor Authentication

JWT Token Structure:

json

{
  "aud": "authenticated",
  "exp": 1699123456,
  "sub": "user-uuid",
  "email": "user@example.com",
  "role": "authenticated",
  "app_metadata": {
    "provider": "google",
    "organizations": ["org-uuid"]
  },
  "user_metadata": {
    "full_name": "John Smith",
    "avatar_url": "https://..."
  }
}

Token Lifecycle:

1. User logs in
2. Access token issued (1 hour TTL)
3. Refresh token issued (30 days TTL)
4. Access token sent with requests
5. Auto-refresh before expiration
6. Re-authenticate after 30 days

Permission Levels

Hierarchy:

Organization
├─ Owner (full control)
├─ Admin (manage users, settings)
└─ Member (access assigned workspaces)

Workspace
├─ Admin (manage workspace)
├─ Editor (create/edit content)
├─ Commenter (view and comment)
└─ Viewer (read-only)

Document
├─ Owner (full control)
├─ Editor (can edit)
└─ Viewer (read-only)

Real-Time Features

Supabase Realtime

WebSocket Subscriptions:

typescript

// Subscribe to document changes
const subscription = supabase
  .channel('document-changes')
  .on(
    'postgres_changes',
    {
      event: '*', // INSERT, UPDATE, DELETE
      schema: 'public',
      table: 'documents',
      filter: `workspace_id=eq.${workspaceId}`
    },
    (payload) => {
      console.log('Document changed:', payload);
      // Update UI
    }
  )
  .subscribe();

Presence System:

typescript

// Track who's viewing a document
const channel = supabase.channel(`document:${documentId}`);

// Send presence
channel.track({
  user_id: userId,
  username: userName,
  viewing_at: new Date().toISOString()
});

// Receive presence updates
channel.on('presence', { event: 'sync' }, () => {
  const state = channel.presenceState();
  // Update UI with active viewers
});

Edge Functions

Serverless Functions

Use Cases:

AI API calls (OpenAI, Anthropic)
Webhook handling
Background processing
External API integrations
Custom business logic

Example Function:

typescript

// supabase/functions/process-document/index.ts
import { serve } from "https://deno.land/std@0.168.0/http/server.ts"
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

serve(async (req) => {
  const { documentId } = await req.json();
  
  // Create Supabase client with service role key
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
  );
  
  // Get document
  const { data: document } = await supabase
    .from('documents')
    .select('*')
    .eq('id', documentId)
    .single();
  
  // Process with AI
  const summary = await generateSummary(document.content);
  const embedding = await generateEmbedding(document.content);
  
  // Update document
  await supabase
    .from('documents')
    .update({ 
      summary,
      processing_status: 'complete'
    })
    .eq('id', documentId);
  
  // Store embedding
  await supabase
    .from('document_embeddings')
    .insert({
      document_id: documentId,
      embedding,
      content: document.content
    });
  
  return new Response(
    JSON.stringify({ success: true }),
    { headers: { "Content-Type": "application/json" } }
  );
});

Storage Architecture

Supabase Storage

Buckets:

documents/
├─ {organization_id}/
│  └─ {document_id}/
│     ├─ original.pdf
│     ├─ processed.txt
│     └─ thumbnail.png

recordings/
├─ {organization_id}/
│  └─ {meeting_id}/
│     ├─ audio.mp3
│     └─ video.mp4

avatars/
└─ {user_id}/
   └─ avatar.jpg

Storage Policies:

sql

-- Users can upload to their organization's folder
CREATE POLICY "Users can upload organization files"
ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'documents'
  AND (storage.foldername(name))[1] IN (
    SELECT id::text FROM organizations
    WHERE id IN (
      SELECT organization_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  )
);

File Upload Flow

1. Client requests signed URL
   ↓
2. Edge Function validates request
   ↓
3. Generate signed URL (1 hour TTL)
   ↓
4. Client uploads directly to storage
   ↓
5. Webhook triggers processing
   ↓
6. Edge Function processes file
   ↓
7. Store metadata in database
   ↓
8. Update UI via real-time subscription

Performance Optimization

Database Optimization

Indexes:

sql

-- Frequently queried columns
CREATE INDEX documents_workspace_user_idx 
ON documents (workspace_id, user_id);

-- Full-text search
CREATE INDEX documents_content_fts_idx
ON documents USING gin(to_tsvector('english', content));

-- JSONB queries
CREATE INDEX documents_metadata_idx
ON documents USING gin(metadata jsonb_path_ops);

Query Optimization:

sql

-- Use EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM documents
WHERE workspace_id = 'uuid'
AND created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 20;

-- Optimize with covering index
CREATE INDEX documents_workspace_created_idx
ON documents (workspace_id, created_at DESC)
INCLUDE (id, title, file_type);

Caching Strategy

Client-Side:

React Query for data caching
5-minute stale time
Background revalidation
Optimistic updates

CDN:

Static assets cached
Document files cached (1 hour)
User avatars cached (24 hours)

Monitoring & Observability

Database Metrics

Monitor:

Query performance
Connection pool usage
Index usage
Table sizes
Slow queries

Supabase Dashboard:

Real-time metrics
Query logs
API usage
Storage consumption

Logging

Edge Function Logs:

typescript

console.log('Processing document:', documentId);
console.error('Failed to process:', error);

// Structured logging
console.log(JSON.stringify({
  level: 'info',
  message: 'Document processed',
  documentId,
  duration: 1234,
  timestamp: new Date().toISOString()
}));

Security Best Practices

Checklist:

✅ RLS enabled on all tables
✅ Service role key stored securely
✅ HTTPS only
✅ JWT validation
✅ Input sanitization
✅ Rate limiting
✅ Audit logging
✅ Regular security audits

Disaster Recovery

Backup Strategy

Automated Backups:

Daily full backups
Point-in-time recovery (7 days)
Encrypted backups
Geo-redundant storage

Manual Backups:

sql

-- Export specific table
COPY documents TO '/tmp/documents.csv' DELIMITER ',' CSV HEADER;

-- Full database dump
pg_dump -h db.supabase.co -U postgres > backup.sql

Next Steps

pgvector Semantic Search - Vector search deep dive
API Authentication - API usage guide
Security & Compliance - Security practices

Need help? Check Developer Forum or contact support.

Last Updated: October 11, 2025

Supabase Architecture ​

Overview ​

Architecture Components ​

Database Layer ​

Schema Design ​

Row Level Security (RLS) ​

Vector Search Implementation ​

Authentication & Authorization ​

Supabase Auth ​

Permission Levels ​

Real-Time Features ​

Supabase Realtime ​

Edge Functions ​

Serverless Functions ​

Storage Architecture ​

Supabase Storage ​

File Upload Flow ​

Performance Optimization ​

Database Optimization ​

Caching Strategy ​

Monitoring & Observability ​

Database Metrics ​

Logging ​

Security Best Practices ​

Disaster Recovery ​

Backup Strategy ​

Next Steps ​

Supabase Architecture

Overview

Architecture Components

Database Layer

Schema Design

Row Level Security (RLS)

Vector Search Implementation

Authentication & Authorization

Supabase Auth

Permission Levels

Real-Time Features

Supabase Realtime

Edge Functions

Serverless Functions

Storage Architecture

Supabase Storage

File Upload Flow

Performance Optimization

Database Optimization

Caching Strategy

Monitoring & Observability

Database Metrics

Logging

Security Best Practices

Disaster Recovery

Backup Strategy

Next Steps