Database Architecture

Database Overview

AuthentiVoice uses Supabase as its database platform, which provides PostgreSQL with additional features like real-time subscriptions, authentication, and Row Level Security (RLS).

Schema Design

Core Tables

Table Definitions

profiles

CREATE TABLE public.profiles (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    email TEXT UNIQUE NOT NULL,
    full_name TEXT,
    avatar_url TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- RLS Policies
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their own profile"
    ON public.profiles FOR SELECT
    USING (auth.uid() = id);

CREATE POLICY "Users can update their own profile"
    ON public.profiles FOR UPDATE
    USING (auth.uid() = id);

organizations

CREATE TABLE public.organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    domain TEXT,
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_organizations_domain ON public.organizations(domain);

-- RLS Policies
ALTER TABLE public.organizations ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Organization members can view"
    ON public.organizations FOR SELECT
    USING (
        EXISTS (
            SELECT 1 FROM public.organization_members
            WHERE organization_id = organizations.id
            AND user_id = auth.uid()
        )
    );

call_analyses

CREATE TABLE public.call_analyses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES public.organizations(id),
    file_id TEXT,
    file_hash TEXT,
    file_name TEXT NOT NULL,
    stored_paths JSONB DEFAULT '{}',
    result JSONB DEFAULT '{}',
    metadata JSONB DEFAULT '{}',
    status TEXT NOT NULL DEFAULT 'pending' 
        CHECK (status IN ('pending', 'processing', 'completed', 'error', 'needs_review', 'in_review', 'cleared')),
    priority TEXT DEFAULT 'medium'
        CHECK (priority IN ('high', 'medium', 'low')),
    assigned_to UUID REFERENCES public.profiles(id),
    assigned_by UUID REFERENCES public.profiles(id),
    assigned_at TIMESTAMP WITH TIME ZONE,
    assigned_load INTEGER DEFAULT 0,
    completed_at TIMESTAMP WITH TIME ZONE,
    reviewed_by UUID REFERENCES public.profiles(id),
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_call_analyses_org_id ON public.call_analyses(organization_id);
CREATE INDEX idx_call_analyses_status ON public.call_analyses(status);
CREATE INDEX idx_call_analyses_created_at ON public.call_analyses(created_at DESC);
CREATE INDEX idx_call_analyses_fraud_score ON public.call_analyses((result->>'fraud_score')::FLOAT);

Database Functions

get_user_organization_role

CREATE OR REPLACE FUNCTION public.get_user_organization_role(p_user_id UUID)
RETURNS TABLE(organization_id UUID, role TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        om.organization_id,
        om.role
    FROM public.organization_members om
    WHERE om.user_id = p_user_id
    LIMIT 1;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

assign_user_to_organization_by_domain

CREATE OR REPLACE FUNCTION public.assign_user_to_organization_by_domain(
    p_user_id UUID,
    p_email TEXT,
    p_role TEXT DEFAULT 'member'
)
RETURNS BOOLEAN AS $$
DECLARE
    v_domain TEXT;
    v_org_id UUID;
BEGIN
    -- Extract domain from email
    v_domain := LOWER(SUBSTRING(p_email FROM '@(.*)$'));
    
    -- Find organization by domain
    SELECT id INTO v_org_id
    FROM public.organizations
    WHERE domain = v_domain
    LIMIT 1;
    
    -- If organization found, create membership
    IF v_org_id IS NOT NULL THEN
        INSERT INTO public.organization_members (user_id, organization_id, role)
        VALUES (p_user_id, v_org_id, p_role)
        ON CONFLICT (user_id, organization_id) DO UPDATE
        SET role = EXCLUDED.role;
        
        RETURN TRUE;
    END IF;
    
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Row Level Security (RLS)

Organization-based Isolation

-- Call analyses RLS
CREATE POLICY "Organization members can view analyses"
    ON public.call_analyses FOR SELECT
    USING (
        organization_id IN (
            SELECT organization_id 
            FROM public.organization_members 
            WHERE user_id = auth.uid()
        )
    );

CREATE POLICY "Organization members can create analyses"
    ON public.call_analyses FOR INSERT
    WITH CHECK (
        organization_id IN (
            SELECT organization_id 
            FROM public.organization_members 
            WHERE user_id = auth.uid()
        )
    );

-- Role-based permissions
CREATE POLICY "Only admins can delete analyses"
    ON public.call_analyses FOR DELETE
    USING (
        organization_id IN (
            SELECT organization_id 
            FROM public.organization_members 
            WHERE user_id = auth.uid() 
            AND role = 'admin'
        )
    );

Audit Logging

-- Audit log table
CREATE TABLE public.audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID REFERENCES public.organizations(id),
    user_id UUID REFERENCES public.profiles(id),
    action TEXT NOT NULL,
    resource_type TEXT NOT NULL,
    resource_id UUID,
    details JSONB DEFAULT '{}',
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Automatic audit logging trigger
CREATE OR REPLACE FUNCTION public.create_audit_log()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.audit_logs (
        organization_id,
        user_id,
        action,
        resource_type,
        resource_id,
        details
    ) VALUES (
        NEW.organization_id,
        auth.uid(),
        TG_OP,
        TG_TABLE_NAME,
        NEW.id,
        jsonb_build_object(
            'old', to_jsonb(OLD),
            'new', to_jsonb(NEW)
        )
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply trigger to important tables
CREATE TRIGGER audit_call_analyses
    AFTER INSERT OR UPDATE OR DELETE ON public.call_analyses
    FOR EACH ROW EXECUTE FUNCTION public.create_audit_log();

Real-time Subscriptions

Enabling Real-time

-- Enable real-time for call analyses
ALTER PUBLICATION supabase_realtime ADD TABLE public.call_analyses;

-- Enable real-time for specific columns only
ALTER TABLE public.call_analyses 
REPLICA IDENTITY USING INDEX call_analyses_pkey;

Client-side Subscription

// Subscribe to call analysis updates
const subscription = supabase
  .channel('call-analyses')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'call_analyses',
      filter: `organization_id=eq.${orgId}`
    },
    (payload) => {
      console.log('Change received!', payload);
      // Update local state
    }
  )
  .subscribe();

Performance Optimization

Indexes

-- Composite indexes for common queries
CREATE INDEX idx_call_analyses_org_status_created 
    ON public.call_analyses(organization_id, status, created_at DESC);

-- Partial indexes for filtered queries
CREATE INDEX idx_call_analyses_pending 
    ON public.call_analyses(organization_id, created_at DESC) 
    WHERE status = 'pending';

-- JSON indexes for JSONB queries
CREATE INDEX idx_call_analyses_fraud_score_gin 
    ON public.call_analyses USING GIN ((result->'fraud_score'));

Materialized Views

-- Analytics summary view
CREATE MATERIALIZED VIEW public.analytics_summary AS
SELECT 
    organization_id,
    DATE_TRUNC('day', created_at) as date,
    COUNT(*) as total_calls,
    COUNT(*) FILTER (WHERE (result->>'fraud_score')::FLOAT > 0.7) as high_risk_calls,
    AVG((result->>'fraud_score')::FLOAT) as avg_fraud_score,
    COUNT(DISTINCT assigned_to) as active_reviewers
FROM public.call_analyses
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY organization_id, date;

-- Refresh materialized view periodically
CREATE OR REPLACE FUNCTION public.refresh_analytics_summary()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY public.analytics_summary;
END;
$$ LANGUAGE plpgsql;

Backup and Recovery

Backup Strategy

-- Point-in-time recovery enabled by default in Supabase
-- Additional backup strategies:

-- Export critical data
CREATE OR REPLACE FUNCTION public.export_organization_data(p_org_id UUID)
RETURNS TABLE(
    table_name TEXT,
    data JSONB
) AS $$
BEGIN
    -- Export call analyses
    RETURN QUERY
    SELECT 
        'call_analyses'::TEXT,
        jsonb_agg(ca.*)
    FROM public.call_analyses ca
    WHERE ca.organization_id = p_org_id;
    
    -- Export reviews
    RETURN QUERY
    SELECT 
        'call_reviews'::TEXT,
        jsonb_agg(cr.*)
    FROM public.call_reviews cr
    JOIN public.call_analyses ca ON cr.call_analysis_id = ca.id
    WHERE ca.organization_id = p_org_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Migration Management

Migration Structure

-- Migration naming convention: YYYYMMDD_HHMMSS_description.sql

-- Example migration
-- 20250110_120000_add_review_metrics.sql
BEGIN;

-- Add new tables
CREATE TABLE IF NOT EXISTS public.review_metrics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    call_analysis_id UUID UNIQUE NOT NULL REFERENCES public.call_analyses(id),
    audio_played_percentage DECIMAL(5,2) DEFAULT 0,
    -- ... other columns
);

-- Update existing data if needed
UPDATE public.call_analyses 
SET status = 'needs_review' 
WHERE status = 'completed' 
AND (result->>'fraud_score')::FLOAT > 0.5;

COMMIT;

Best Practices

Schema Design

Use UUIDs for primary keys
Implement proper foreign key constraints
Add appropriate indexes for query patterns
Use JSONB for flexible, schema-less data

Security

Always enable RLS on tables
Use SECURITY DEFINER functions carefully
Implement least-privilege access
Audit sensitive operations

Performance

Create indexes for frequent queries
Use materialized views for complex aggregations
Implement connection pooling
Monitor query performance

Maintenance

Regular VACUUM and ANALYZE
Monitor table bloat
Review slow query logs
Plan for data archival

Get Started

User Guide

Features

Architecture

Deployment

Database Overview

Schema Design

Core Tables

Table Definitions

profiles

organizations

call_analyses

Database Functions

get_user_organization_role

assign_user_to_organization_by_domain

Row Level Security (RLS)

Organization-based Isolation

Audit Logging

Real-time Subscriptions

Enabling Real-time

Client-side Subscription

Performance Optimization

Indexes

Materialized Views

Backup and Recovery

Backup Strategy

Migration Management

Migration Structure

Best Practices

Get Started

User Guide

Features

Architecture

Deployment

​Database Overview

​Schema Design

​Core Tables

​Table Definitions

​profiles

​organizations

​call_analyses

​Database Functions

​get_user_organization_role

​assign_user_to_organization_by_domain

​Row Level Security (RLS)

​Organization-based Isolation

​Audit Logging

​Real-time Subscriptions

​Enabling Real-time

​Client-side Subscription

​Performance Optimization

​Indexes

​Materialized Views

​Backup and Recovery

​Backup Strategy

​Migration Management

​Migration Structure

​Best Practices

Database Overview

Schema Design

Core Tables

Table Definitions

profiles

organizations

call_analyses

Database Functions

get_user_organization_role

assign_user_to_organization_by_domain

Row Level Security (RLS)

Organization-based Isolation

Audit Logging

Real-time Subscriptions

Enabling Real-time

Client-side Subscription

Performance Optimization

Indexes

Materialized Views

Backup and Recovery

Backup Strategy

Migration Management

Migration Structure

Best Practices