Supabase Integration

Overview

Highway uses Supabase as its backend database and real-time data platform. All verification records, call logs, and customer data are stored in Supabase PostgreSQL tables with automatic persistence and querying capabilities.

Database Architecture

Highway’s data model consists of two primary tables with a one-to-many relationship:

Database Schema

Verifications Table

The verifications table stores customer information and the data points that need to be verified.

Schema
Example Data
Queries

Column	Type	Constraints	Description
`id`	`integer`	PRIMARY KEY, AUTO INCREMENT	Unique verification identifier
`name`	`text`	NOT NULL	Customer’s full name
`phone`	`text`	NOT NULL	10-digit phone number
`type`	`text`	NOT NULL	Background/context for verification
`data`	`jsonb`	NOT NULL	Verification questions as JSON
`created_at`	`timestamp with time zone`	DEFAULT now()	When verification was created

{
  "id": 42,
  "name": "John Smith",
  "phone": "5551234567",
  "type": "Customer applied for business loan",
  "data": {
    "date_of_birth": "1985-03-15",
    "ssn_last_4": "1234",
    "address": "123 Main St, Springfield, IL 62701",
    "employer": "Acme Corporation"
  },
  "created_at": "2024-10-15T14:30:00Z"
}

-- Create new verification
INSERT INTO verifications (name, phone, type, data)
VALUES (
  'Jane Doe',
  '5559876543',
  'Account activation',
  '{"dob": "1990-01-01", "address": "456 Oak Ave"}'
);

-- Get all verifications (newest first)
SELECT * FROM verifications
ORDER BY created_at DESC;

-- Get specific verification
SELECT * FROM verifications
WHERE id = 42;

-- Search by name
SELECT * FROM verifications
WHERE name ILIKE '%smith%';

JSONB Data Field

The data column uses PostgreSQL’s jsonb type for flexible, queryable JSON storage:

Advantages of JSONB

Flexible Schema: Each verification can have different data fields
Queryable: Can search within JSON using PostgreSQL operators
Indexed: Supports GIN indexes for fast JSON queries
Typed: Validates JSON structure on insert

Example JSON Queries

-- Find verifications with specific DOB
SELECT * FROM verifications
WHERE data->>'date_of_birth' = '1990-01-01';

-- Find verifications containing address field
SELECT * FROM verifications
WHERE data ? 'address';

-- Find verifications with SSN field
SELECT id, name, data->'ssn_last_4' as ssn
FROM verifications
WHERE data ? 'ssn_last_4';

Best Practices

Use consistent field naming (snake_case recommended)
Keep JSON structure flat when possible
Don’t store overly nested objects
Use descriptive key names
Validate JSON on the frontend before insert

Calls Table

The calls table tracks individual phone verification attempts and their outcomes.

Schema
Example Data
Queries

Column	Type	Constraints	Description
`id`	`integer`	PRIMARY KEY, AUTO INCREMENT	Unique call identifier
`verification`	`integer`	FOREIGN KEY → verifications(id)	Associated verification record
`status`	`text`	NOT NULL	Current call status
`created_at`	`timestamp with time zone`	DEFAULT now()	When call was initiated

{
  "id": 123,
  "verification": 42,
  "status": "successful_call",
  "created_at": "2024-10-15T15:45:00Z"
}

-- Create new call
INSERT INTO calls (verification, status)
VALUES (42, 'in_progress');

-- Get all calls with verification details
SELECT c.*, v.name, v.phone, v.data
FROM calls c
JOIN verifications v ON c.verification = v.id
ORDER BY c.created_at DESC;

-- Get calls for specific verification
SELECT * FROM calls
WHERE verification = 42
ORDER BY created_at DESC;

-- Update call status
UPDATE calls
SET status = 'successful_call'
WHERE id = 123;

Status Values

The status field uses predefined string values:

type CallStatus = 
  | "in_progress"
  | "successful_call"
  | "unsuccessful_call"
  | "user_hung_up"
  | "system_error";

These status values match the enum in the call_reflection_data function (highway-backend/conversationConfig.js:36-44).

Data Persistence

Highway persists data at several key points in the verification workflow:

1. Creating Verifications

From highway-frontend/src/app/page.tsx:111-129:

const handleSubmit = async (values: typeof form.values) => {
  const supabase = createClient();
  const { data, error } = await supabase.from("verifications").insert({
    name: values.name,
    phone: values.phoneNumber,
    data: JSON.parse(values.userData),
    type: values.type,
  });
  
  if (error) {
    console.error("Error adding verification:", error);
  } else {
    fetchCustomers(); // Refresh list
    closeAddUserModal();
    form.reset();
  }
};

When: User clicks “Add verification” button What’s stored: Customer name, phone, background, and verification data

2. Initiating Calls

From highway-backend/routes.js:35-44:

router.post("/call-customer", async (req, res) => {
  const { to, verification } = req.body;
  
  const { data } = await supabase
    .from("calls")
    .insert([{ verification: verification, status: "in_progress" }])
    .select();
    
  // Call ID is used in Twilio stream URL
  const call = await client.calls.create({
    to: to,
    from: TWILIO_PHONE_NUMBER,
    twiml: `<Stream url="wss://${req.headers.host}/media-stream/${verification}/${data[0].id}" />`
  });
});

When: User clicks “Initiate call” button What’s stored: New call record with in_progress status and verification foreign key

3. Loading Verification Data

From highway-backend/websocket.js:63-76:

openAiWs.on("open", async () => {
  const { data, error } = await supabase
    .from("verifications")
    .select("*")
    .eq("id", streamId);
    
  if (data) {
    bigdata = JSON.stringify(data[0]);
    sendSessionUpdate();
  }
});

When: WebSocket connection to OpenAI is established What’s retrieved: Complete verification record for AI context

4. Updating Call Status

From highway-backend/websocket.js:86-92:

if (response.type === "response.function_call_arguments.done") {
  supabase
    .from("calls")
    .update({ status: response.arguments.status })
    .eq("id", callId);
}

When: AI calls call_reflection_data function What’s updated: Call status changed to final outcome

Supabase Client Setup

Frontend Client

From highway-frontend/src/utils/supabase/client.ts:1-9:

import { createBrowserClient } from "@supabase/ssr";

export const createClient = () =>
  createBrowserClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );

Usage:

const supabase = createClient();
const { data } = await supabase.from("verifications").select("*");

Backend Client

From highway-backend/websocket.js:5-9:

const { createClient } = require("@supabase/supabase-js");
const supabase = createClient(
  "https://umbkzjfffeoykaxsghly.supabase.co",
  "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
);

The backend uses hardcoded credentials in the source code. For production deployments, move these to environment variables:

const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_ANON_KEY
);

Common Query Patterns

Fetching Verifications

From highway-frontend/src/app/page.tsx:57-69:

const fetchCustomers = async () => {
  const supabase = createClient();
  const { data, error } = await supabase
    .from("verifications")
    .select("*")
    .order("created_at", { ascending: false });
    
  if (error) {
    console.error("Error fetching customers:", error);
  } else {
    setCustomers(data || []);
  }
};

Fetching Calls with Verifications

From highway-frontend/src/app/calls/page.tsx:40-80:

const fetchData = async () => {
  const supabase = createClient();
  
  // 1. Fetch all calls
  const { data: callsData } = await supabase
    .from("calls")
    .select("*")
    .order("created_at", { ascending: false });
    
  // 2. Get unique verification IDs from calls
  const verificationIds = [
    ...new Set(callsData?.map((call) => call.verification)),
  ];
  
  // 3. Fetch all related verifications
  const { data: verificationsData } = await supabase
    .from("verifications")
    .select("*")
    .in("id", verificationIds);
    
  // 4. Create lookup map
  const verificationsMap = verificationsData?.reduce(
    (acc, verification) => {
      acc[verification.id] = verification;
      return acc;
    },
    {}
  );
};

This pattern avoids N+1 queries by fetching all verifications in a single query using .in().

Inserting with Return Data

const { data, error } = await supabase
  .from("verifications")
  .insert({ name: "John Doe", phone: "5551234567", ... })
  .select();
  
// data[0] contains the inserted record with auto-generated id

Updating Records

const { error } = await supabase
  .from("calls")
  .update({ status: "successful_call" })
  .eq("id", callId);

Authentication and Security

Row Level Security (RLS)

The current implementation uses the anon key which provides public access. For production:

Enable Row Level Security on both tables
Create policies to restrict access
Implement authentication
Use authenticated user context in queries

Recommended RLS Policies

-- Enable RLS
ALTER TABLE verifications ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users to read their own verifications
CREATE POLICY "Users can view own verifications"
ON verifications FOR SELECT
USING (auth.uid() = user_id);

-- Allow authenticated users to create verifications
CREATE POLICY "Users can create verifications"
ON verifications FOR INSERT
WITH CHECK (auth.uid() = user_id);

API Key Security

Current approach:

Frontend uses public NEXT_PUBLIC_SUPABASE_ANON_KEY
Backend uses same anon key hardcoded

Production recommendations:

Frontend: Keep using anon key with RLS policies
Backend: Use service role key for privileged operations
Environment variables: Never commit keys to version control

.env.local

# Frontend
NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJhbGci...

# Backend
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJhbGci...  # More privileges

Real-Time Subscriptions

Supabase supports real-time subscriptions for live updates. While not currently implemented in Highway, you could add:

// Subscribe to new calls
const subscription = supabase
  .channel('calls')
  .on(
    'postgres_changes',
    { 
      event: 'INSERT', 
      schema: 'public', 
      table: 'calls' 
    },
    (payload) => {
      console.log('New call:', payload.new);
      // Update UI with new call
    }
  )
  .subscribe();

// Subscribe to call status updates
const statusSub = supabase
  .channel('call-status')
  .on(
    'postgres_changes',
    { 
      event: 'UPDATE', 
      schema: 'public', 
      table: 'calls' 
    },
    (payload) => {
      console.log('Call status updated:', payload.new.status);
      // Update call badge in real-time
    }
  )
  .subscribe();

Use Cases for Real-Time

Live call status updates: See status change from “in_progress” to “successful_call” without refreshing
New verification notifications: Alert when team members add verifications
Dashboard sync: Keep multiple browser tabs in sync
Monitoring dashboards: Real-time call volume tracking

Data Backup and Migration

Exporting Data

-- Export verifications to CSV
COPY verifications TO '/tmp/verifications.csv' CSV HEADER;

-- Export calls to CSV
COPY calls TO '/tmp/calls.csv' CSV HEADER;

-- Export with JOIN
COPY (
  SELECT c.*, v.name, v.phone 
  FROM calls c 
  JOIN verifications v ON c.verification = v.id
) TO '/tmp/call_report.csv' CSV HEADER;

Database Migrations

For schema changes, use Supabase migrations:

-- migrations/20241015_add_notes_column.sql
ALTER TABLE verifications
ADD COLUMN notes TEXT;

-- migrations/20241015_add_duration_column.sql
ALTER TABLE calls
ADD COLUMN duration_seconds INTEGER;

Best Practices

Query Optimization

Use .select() to specify only needed columns
Add indexes on frequently queried fields
Use .limit() for pagination
Avoid N+1 queries with .in() operator
Use .explain() to analyze query performance

Data Integrity

Always validate JSON before inserting into data field
Use database constraints (NOT NULL, FOREIGN KEY)
Handle errors gracefully in application code
Consider adding CHECK constraints for status values
Use transactions for multi-step operations

Error Handling

const { data, error } = await supabase
  .from("verifications")
  .insert(newVerification);
  
if (error) {
  console.error("Database error:", error);
  // Show user-friendly error message
  // Log to error tracking service
  // Don't expose database details to users
}

Performance

Create indexes on foreign keys
Use connection pooling
Cache frequently accessed data
Paginate large result sets
Monitor query performance in Supabase dashboard

Next Steps

Verification Management

Learn how to create and manage verification records

Call Monitoring

Query and analyze call data

Configuration

Set up Supabase environment variables

API Reference

Explore the complete API

Getting Started

Core Features

Setup & Configuration

Integration Guides

Supabase Integration

Overview

Database Architecture

Database Schema

Verifications Table

JSONB Data Field

Calls Table

Status Values

Data Persistence

1. Creating Verifications

2. Initiating Calls

3. Loading Verification Data

4. Updating Call Status

Supabase Client Setup

Frontend Client

Backend Client

Common Query Patterns

Fetching Verifications

Fetching Calls with Verifications

Inserting with Return Data

Updating Records

Authentication and Security

Row Level Security (RLS)

Recommended RLS Policies

API Key Security

Real-Time Subscriptions

Data Backup and Migration

Exporting Data

Database Migrations

Best Practices

Next Steps

Verification Management

Call Monitoring

Configuration

API Reference

Build docs developers (and LLMs) love

Getting Started

Core Features

Setup & Configuration

Integration Guides

​Overview

​Database Architecture

​Database Schema

​Verifications Table

​JSONB Data Field

​Calls Table

​Status Values

​Data Persistence

​1. Creating Verifications

​2. Initiating Calls

​3. Loading Verification Data

​4. Updating Call Status

​Supabase Client Setup

​Frontend Client

​Backend Client

​Common Query Patterns

​Fetching Verifications

​Fetching Calls with Verifications

​Inserting with Return Data

​Updating Records

​Authentication and Security

​Row Level Security (RLS)

​Recommended RLS Policies

​API Key Security

​Real-Time Subscriptions

​Data Backup and Migration

​Exporting Data

​Database Migrations

​Best Practices

​Next Steps

Verification Management

Call Monitoring

Configuration

API Reference

Build docs developers (and LLMs) love

Overview

Database Architecture

Database Schema

Verifications Table

JSONB Data Field

Calls Table

Status Values

Data Persistence

1. Creating Verifications

2. Initiating Calls

3. Loading Verification Data

4. Updating Call Status

Supabase Client Setup

Frontend Client

Backend Client

Common Query Patterns

Fetching Verifications

Fetching Calls with Verifications

Inserting with Return Data

Updating Records

Authentication and Security

Row Level Security (RLS)

Recommended RLS Policies

API Key Security

Real-Time Subscriptions

Data Backup and Migration

Exporting Data

Database Migrations

Best Practices

Next Steps