Skip to content

DATABASE_AND_API_CONTEXT.md

Latest commit

 

History

History
260 lines (221 loc) · 8.56 KB

DATABASE_AND_API_CONTEXT.md

File metadata and controls

260 lines (221 loc) · 8.56 KB

E-RAKSHA Database & API Context for Agent Migration

Current Database Configuration

Supabase Database Details

  • Database URL: https://rzgplzaytxronhcakemi.supabase.co
  • Project ID: rzgplzaytxronhcakemi
  • Anon Key: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InJ6Z3BsemF5dHhyb25oY2FrZW1pIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NjY4NzMxODAsImV4cCI6MjA4MjQ0OTE4MH0.6wxeRoi8ZnUmX4Vu86HKx4wWaiOJm_rPhXGrFKyEvyg

Environment Variables

VITE_SUPABASE_URL=https://rzgplzaytxronhcakemi.supabase.co
VITE_SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InJ6Z3BsemF5dHhyb25oY2FrZW1pIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NjY4NzMxODAsImV4cCI6MjA4MjQ0OTE4MH0.6wxeRoi8ZnUmX4Vu86HKx4wWaiOJm_rPhXGrFKyEvyg

Database Schema

1. Primary Table: video_analyses

CREATE TABLE video_analyses (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    filename TEXT NOT NULL,
    file_size BIGINT NOT NULL,
    prediction TEXT NOT NULL CHECK (prediction IN ('real', 'fake')),
    confidence DECIMAL(5,4) NOT NULL CHECK (confidence >= 0 AND confidence <= 1),
    models_used TEXT[] NOT NULL DEFAULT '{}',
    processing_time DECIMAL(6,2) NOT NULL DEFAULT 0,
    analysis_result JSONB,
    user_ip TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Indexes:

  • idx_video_analyses_created_at on created_at DESC
  • idx_video_analyses_prediction on prediction
  • idx_video_analyses_confidence on confidence

2. Security Schema Tables (Extended)

Users Table

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(255),
  role VARCHAR(50) DEFAULT 'analyst',
  organization VARCHAR(255),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_login TIMESTAMP
);

Video Evidence Table (Encrypted)

CREATE TABLE video_evidence (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_id VARCHAR(255) NOT NULL UNIQUE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  user_email VARCHAR(255),
  filename VARCHAR(255) NOT NULL,
  file_size BIGINT NOT NULL,
  file_hash VARCHAR(64) NOT NULL,
  mime_type VARCHAR(100),
  encrypted_link TEXT NOT NULL, -- AES-256-GCM encrypted Cloudinary links
  cloudinary_public_id VARCHAR(255),
  cloudinary_version INTEGER,
  status VARCHAR(50) DEFAULT 'uploaded',
  analysis_status VARCHAR(50) DEFAULT 'pending',
  analysis_result JSONB,
  prediction VARCHAR(50),
  confidence DECIMAL(5, 4),
  metadata JSONB DEFAULT '{}',
  upload_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  analysis_timestamp TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Forensic Analysis Table

CREATE TABLE forensic_analysis (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_id VARCHAR(255) NOT NULL UNIQUE REFERENCES video_evidence(case_id),
  analysis_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  analyst_id UUID REFERENCES users(id),
  prediction VARCHAR(50) NOT NULL,
  confidence DECIMAL(5, 4) NOT NULL,
  preprocessing_results JSONB,
  core_model_results JSONB,
  postprocessing_results JSONB,
  forensic_certificate TEXT,
  chain_of_custody JSONB DEFAULT '{}',
  legal_admissibility VARCHAR(50),
  expert_recommendation TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Access Logs Table (Audit Trail)

CREATE TABLE video_access_logs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  case_id VARCHAR(255) NOT NULL,
  user_id UUID REFERENCES users(id) ON DELETE SET NULL,
  user_email VARCHAR(255),
  action VARCHAR(100) NOT NULL,
  ip_address VARCHAR(45),
  user_agent TEXT,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

TypeScript Interfaces

VideoAnalysis Interface

export interface VideoAnalysis {
  id?: string
  filename: string
  file_size: number
  prediction: 'real' | 'fake'
  confidence: number
  models_used: string[]
  processing_time: number
  analysis_result: any
  created_at?: string
  user_ip?: string
}

Database Functions

Core Functions Available:

  1. checkDuplicateFile(filename, fileSize) - Check if file was previously analyzed
  2. saveAnalysis(analysis) - Save new analysis result
  3. getAnalyticsStats() - Get overall statistics
  4. getRecentAnalyses(limit, offset, filter) - Get paginated recent analyses
  5. getAnalysisById(id) - Get single analysis by ID
  6. formatRelativeTime(dateString) - Format timestamps

Security Functions:

  1. log_video_access() - Log video access for audit trail
  2. update_video_analysis() - Update analysis status and results

Security Features

Row Level Security (RLS)

  • Enabled on all sensitive tables
  • Users can only access their own data
  • Admin role has broader access

Encryption

  • Video Links: AES-256-GCM encryption for Cloudinary URLs
  • Passwords: Bcrypt hashing (one-way)
  • File Integrity: SHA-256 hashing for verification

Audit Trail

  • All video access logged with timestamps
  • IP addresses and user agents tracked
  • Actions logged: 'video_retrieved', 'video_downloaded', 'analysis_viewed'

API Integration Points

Current API Endpoints:

  • /api/predict - Basic deepfake detection
  • /api/predict-with-agents - Agent-enhanced analysis
  • /api/predict-large-video - Large file processing
  • /api/ondemand-webhook - OnDemand agent integration
  • /api/upload-secure - Secure file upload with encryption
  • /api/video-retrieve - Encrypted video retrieval

OnDemand Integration:

  • Webhook URL: https://gateway.on-demand.io/automation/public/v1/webhook/workflow/696aed8727b1bb913e89a2a9/execute
  • 6-Agent Workflow: 3 pre-processing + 3 post-processing agents
  • Payload Format: {"payload": data}

Deployment Configuration

Vercel Settings:

  • Project: interceptor-4x4
  • Node Version: 20.x
  • Build Command: vite build
  • Output Directory: dist

Environment Variables Required:

VITE_SUPABASE_URL=https://rzgplzaytxronhcakemi.supabase.co
VITE_SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InJ6Z3BsemF5dHhyb25oY2FrZW1pIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NjY4NzMxODAsImV4cCI6MjA4MjQ0OTE4MH0.6wxeRoi8ZnUmX4Vu86HKx4wWaiOJm_rPhXGrFKyEvyg

# Optional Security Variables:
ENCRYPTION_KEY=your_32_byte_hex_key
CLOUDINARY_CLOUD_NAME=your_cloud_name
CLOUDINARY_API_KEY=your_api_key
CLOUDINARY_API_SECRET=your_api_secret
SUPABASE_SERVICE_ROLE_KEY=your_service_role_key
JWT_SECRET=your_jwt_secret

Data Migration Considerations

Current Data Structure:

  • Primary Storage: Supabase PostgreSQL
  • File Storage: Cloudinary (with encrypted links)
  • Analytics: Real-time via Supabase functions
  • Audit Trail: Complete access logging

Migration Requirements:

  1. Database Schema: Preserve all table structures and relationships
  2. Encryption Keys: Maintain encryption compatibility for existing data
  3. API Compatibility: Ensure all existing endpoints continue working
  4. Security Policies: Maintain RLS and access controls
  5. Audit Trail: Preserve all historical access logs

Critical Data to Preserve:

  • All video analysis results in video_analyses table
  • Encrypted video links in video_evidence table
  • Complete audit trail in video_access_logs table
  • User accounts and permissions in users table
  • Forensic certificates in forensic_analysis table

Performance Considerations

Current Optimizations:

  • Indexed queries on frequently accessed columns
  • JSONB storage for flexible analysis results
  • Pagination for large result sets
  • Caching for duplicate file detection

Scaling Notes:

  • Database can handle high concurrent reads
  • Write operations are optimized for analysis results
  • File storage is handled by Cloudinary CDN
  • API endpoints are serverless (Vercel functions)

Security Compliance

Features Implemented:

  • End-to-end encryption for sensitive data
  • Complete audit trail for legal compliance
  • Row-level security for data isolation
  • Secure file upload and retrieval
  • Password hashing with bcrypt
  • JWT-based authentication

Legal Compliance:

  • Chain of custody tracking
  • Forensic certificate generation
  • Legal admissibility assessment
  • Expert recommendation documentation
  • Audit log retention policies

Note: This context provides complete information for migrating the E-RAKSHA system while maintaining all security, functionality, and data integrity requirements.