StratoFusion
Documentation / OMNI EXPERIENCE Omni Experience Implementation
The Stratofusion application includes an omni experience that persists user
service accounts and tokens across devices, browsers, and sessions in the
environment's PostgreSQL database. Production uses Compose PostgreSQL; the
local development uses local PostgreSQL.
Overview
The omni experience ensures that:
Service accounts persist across all user sessions.
OAuth tokens are automatically refreshed.
Users can access their connected services from any device.
Data is securely stored and user-scoped.
Architecture
Database Schema
The implementation uses four main tables:
user_service_accountsuser_service_tokensuser_preferencestoken_refresh_log
Key Components
Database Service Layer (src/lib/database/service.ts) - Clean interface for database operations.Token Refresh Manager (src/lib/database/token-refresh.ts) - Automatic token refresh.Updated API Routes - Database-backed authentication endpoints.Enhanced ServiceManagerContext - Loads persisted data on mount.
Setup Instructions
1. Database Configuration
Add the target environment's PostgreSQL database URL to its protected runtime variables:
# .env.local
DATABASE_URL = postgresql://username:password@hostname/database?sslmode = require
2. Database Migration Generate and run the database migrations:
# Generate migration files
pnpm db:generate
# Push schema to database
pnpm db:push
3. Verify Setup The application will automatically:
Load persisted service accounts when users sign in.
Initialize token refresh schedules.
Store new service connections in the database.
Features
Cross-Device Persistence
Connect services on one device and access them on another.
Switch between browsers without losing connections.
Resume work seamlessly across sessions.
Automatic Token Refresh The system provides comprehensive automatic token refresh for all connected accounts:
Auto-Connected Accounts (Clerk OAuth)
Monitors token expiration dates every 5 minutes.
Automatically refreshes tokens 5-10 minutes before expiry.
Uses Clerk's OAuth integration for seamless refresh.
Zero user intervention required.
Manually Connected Accounts (Service-Specific OAuth)
NEW : Now supports automatic refresh for manual accounts.Uses service-specific refresh token logic.
Supports Google Drive, OneDrive, and Dropbox.
Falls back to re-authentication if refresh fails.
Refresh Process
Background monitoring every 5 minutes.
Proactive refresh before token expiry.
Service-specific refresh methods for each provider.
Comprehensive error handling and logging.
Secure Data Storage
User-scoped with Clerk user IDs.
Stored securely in the target environment's PostgreSQL database.
Encrypted in transit and at rest.
Automatically cleaned up when users disconnect.
API Endpoints
Service Accounts
GET /api/auth/service-accounts - Load user's service accounts.DELETE /api/auth/service-accounts - Disconnect service accounts.
Token Management
GET /api/auth/refresh-tokens - Initialize token refresh schedules.POST /api/auth/refresh-tokens - Manually refresh tokens.
Authentication Status
GET /api/auth/status - Get current authentication status (updated for database).
Usage Examples
Loading User Data import { loadUserOmniData } from "~/lib/database/service" ;
const omniData = await loadUserOmniData ( userId ) ;
console . log ( "Service accounts:" , omniData . serviceAccounts ) ;
console . log ( "Active services:" , omniData . activeServices ) ;
Persisting New Service Account import { initializeUserOmniExperience } from "~/lib/database/service" ;
await initializeUserOmniExperience (
userId ,
"google" ,
{ id : "account123" , email : "user@gmail.com" , name : "User" } ,
tokens ,
false // isAutoConnected
) ;
Token Refresh import { ensureValidTokens } from "~/lib/database/token-refresh" ;
const isValid = await ensureValidTokens ( userId , "google" , "account123" ) ;
if ( ! isValid ) {
// Handle token refresh failure
}
Migration from Session Storage The implementation maintains backward compatibility with the existing session-based storage by:
Preserving API interfaces - All existing functions work the same wayGradual migration - New connections use database, existing ones continue to workFallback handling - Graceful degradation if database is unavailable
Monitoring and Debugging
Token Refresh Logs Monitor token refresh attempts:
SELECT * FROM token_refresh_log
WHERE user_id = 'user_123'
ORDER BY refreshed_at DESC ;
User Service Accounts View user's connected accounts:
SELECT * FROM user_service_accounts
WHERE user_id = 'user_123' AND is_active = true ;
Database Studio Use Drizzle Studio to inspect the database:
Testing Run the omni experience tests:
pnpm test src/lib/database/__tests__/omni-experience.test.ts
Security Considerations
All database queries are user-scoped with Clerk user IDs.
Tokens are stored securely and never exposed in logs.
Automatic cleanup prevents data accumulation.
Regular token refresh maintains security.
The implementation is optimized for:
Fast loading of user data on sign-in.
Efficient token refresh scheduling.
Minimal database queries through caching.
Automatic cleanup of expired data.
Troubleshooting
Common Issues
Database connection errors - Verify DATABASE_URL is correctToken refresh failures - Check Clerk OAuth configurationMissing service accounts - Ensure user is properly authenticated
Debug Logging Enable debug logging by setting:
Future Enhancements Planned improvements include:
Real-time sync across devices using WebSockets.
Advanced token refresh strategies.
User preference synchronization.
Enhanced monitoring and analytics.