# 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:

1. **`user_service_accounts`** - Stores connected service account information
2. **`user_service_tokens`** - Stores OAuth tokens with automatic refresh
3. **`user_preferences`** - Stores user preferences and settings
4. **`token_refresh_log`** - Tracks token refresh attempts for monitoring

### 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:

```bash
# .env.local
DATABASE_URL=postgresql://username:password@hostname/database?sslmode=require
```

### 2. Database Migration

Generate and run the database migrations:

```bash
# 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

Users can:
- 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

All data is:
- 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

```typescript
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

```typescript
import { initializeUserOmniExperience } from "~/lib/database/service";

await initializeUserOmniExperience(
  userId,
  "google",
  { id: "account123", email: "user@gmail.com", name: "User" },
  tokens,
  false // isAutoConnected
);
```

### Token Refresh

```typescript
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:

1. **Preserving API interfaces** - All existing functions work the same way
2. **Gradual migration** - New connections use database, existing ones continue to work
3. **Fallback handling** - Graceful degradation if database is unavailable

## Monitoring and Debugging

### Token Refresh Logs

Monitor token refresh attempts:

```sql
SELECT * FROM token_refresh_log
WHERE user_id = 'user_123'
ORDER BY refreshed_at DESC;
```

### User Service Accounts

View user's connected accounts:

```sql
SELECT * FROM user_service_accounts
WHERE user_id = 'user_123' AND is_active = true;
```

### Database Studio

Use Drizzle Studio to inspect the database:

```bash
pnpm db:studio
```

## Testing

Run the omni experience tests:

```bash
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.

## Performance

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

1. **Database connection errors** - Verify `DATABASE_URL` is correct
2. **Token refresh failures** - Check Clerk OAuth configuration
3. **Missing service accounts** - Ensure user is properly authenticated

### Debug Logging

Enable debug logging by setting:

```bash
DEBUG=stratofusion:omni
```

## Future Enhancements

Planned improvements include:
- Real-time sync across devices using WebSockets.
- Advanced token refresh strategies.
- User preference synchronization.
- Enhanced monitoring and analytics.