# Plan: Direct Snowflake Browser Connection with SSO

## Overview
Add direct Snowflake connection to the TikTok analytics UI using OAuth SSO authentication and the Snowflake SQL API. This enables fetching real historical trend data from the Chartmetric tables.

**Key Simplification:** Uses Snowflake's built-in `SNOWFLAKE$LOCAL_APPLICATION` integration - no admin setup required!

## Architecture

**Important:** The Snowflake SQL API does not support CORS from browser origins. All API calls must go through the Vite dev server proxy.

```
┌─────────────────────────────────────────────────────────────┐
│                     React/Vite Frontend                      │
├─────────────────────────────────────────────────────────────┤
│  1. User clicks "Connect to Snowflake"                      │
│  2. Browser opens Snowflake OAuth (popup or redirect)       │
│  3. User authenticates via existing SSO                     │
│  4. Snowflake redirects to http://127.0.0.1:5173/callback   │
│  5. App exchanges code for access token (via proxy)         │
│  6. App calls SQL API through Vite proxy                    │
└─────────────────────────────────────────────────────────────┘
         │
         ▼ (via /snowflake-api proxy)
┌─────────────────────────────────────────────────────────────┐
│                   Vite Dev Server Proxy                      │
│  /snowflake-api/* → https://<account>.snowflakecomputing.com │
│  (Adds changeOrigin + Origin header to bypass CORS)         │
└─────────────────────────────────────────────────────────────┘
         │
         ▼
┌─────────────────────────────────────────────────────────────┐
│              Snowflake SQL API (REST)                        │
│  POST /api/v2/statements                                     │
│  Authorization: Bearer <oauth_token>                         │
└─────────────────────────────────────────────────────────────┘
```

## Prerequisites

**None!** Uses Snowflake's built-in `SNOWFLAKE$LOCAL_APPLICATION` security integration:
- Exists in all accounts automatically
- No admin setup required
- Client ID: `LOCAL_APPLICATION`
- Client Secret: `LOCAL_APPLICATION` (or omit for public client)
- Redirect URI must be: `http://127.0.0.1:<port>/...`

Verify it exists:
```sql
SHOW SECURITY INTEGRATIONS LIKE 'SNOWFLAKE$LOCAL_APPLICATION';
```

## Implementation Steps

### Step 1: Create Snowflake API Module
**File:** `ui/src/api/snowflake.ts`

```typescript
// Key constants
const SNOWFLAKE_ACCOUNT = import.meta.env.VITE_SNOWFLAKE_ACCOUNT;
const REDIRECT_URI = 'http://127.0.0.1:5173/oauth/callback';
const CLIENT_ID = 'LOCAL_APPLICATION';

// OAuth endpoints (direct - auth doesn't have CORS issues)
const AUTH_URL = `https://${SNOWFLAKE_ACCOUNT}.snowflakecomputing.com/oauth/authorize`;

// Token + SQL API endpoints (proxied to avoid CORS)
const TOKEN_URL = '/snowflake-api/oauth/token-request';
const SQL_API_URL = '/snowflake-api/api/v2/statements';
```

**Functions:**
- `initiateOAuth()` - Open popup/redirect to Snowflake auth with PKCE
- `handleOAuthCallback(code, codeVerifier)` - Exchange auth code for access token
- `executeQuery(sql, params)` - Call SQL API with Bearer token (handles async polling)
- `fetchSoundTrendHistory(soundId)` - Query Chartmetric for historical data
- `isConnected()` - Check if valid token exists
- `disconnect()` - Clear stored tokens
- `refreshAccessToken()` - Use refresh token to get new access token

**Token Management:**
- Snowflake access tokens are short-lived (~10 minutes)
- Store both `access_token` and `refresh_token` in localStorage
- Store `expires_at` timestamp with token
- Implement request interceptor to:
  1. Check token expiration before requests
  2. On 401 response, attempt token refresh
  3. Retry original request with new token
  4. If refresh fails, prompt user to re-authenticate

**PKCE (Required for LOCAL_APPLICATION):**
```typescript
// Generate code verifier (43-128 chars, URL-safe)
const codeVerifier = generateRandomString(64);

// Generate code challenge (SHA256 hash, base64url encoded)
const codeChallenge = base64url(sha256(codeVerifier));

// Include in auth request:
// code_challenge_method=S256
// code_challenge=<codeChallenge>
// scope=refresh_token  <-- Important! Request refresh token explicitly
```

**OAuth URL construction:**
```typescript
const authUrl = new URL(AUTH_URL);
authUrl.searchParams.set('response_type', 'code');
authUrl.searchParams.set('client_id', CLIENT_ID);
authUrl.searchParams.set('redirect_uri', REDIRECT_URI);
authUrl.searchParams.set('code_challenge', codeChallenge);
authUrl.searchParams.set('code_challenge_method', 'S256');
authUrl.searchParams.set('scope', 'refresh_token'); // Explicit scope request
```

### Step 2: Create OAuth Callback Route
**File:** `ui/src/pages/OAuthCallback.tsx`

- Handle redirect from Snowflake after SSO
- Extract authorization code from URL params
- Exchange code for access token
- Store token and redirect to main app

### Step 3: Add Snowflake Connection UI
**File:** `ui/src/components/SnowflakeConnect.tsx`

- "Connect to Snowflake" button (shows when not connected)
- Connection status indicator (green when connected)
- Disconnect option

### Step 4: Update TrendAnalysis to Use Snowflake Data
**File:** `ui/src/pages/TrendAnalysis.tsx`

- Check if Snowflake connected
- If connected, fetch historical data from Chartmetric
- Replace sampled data disclaimer with real data
- Fallback to Apify if Snowflake not connected

### Step 5: Add Environment Variables
**File:** `ui/.env.local`

```
VITE_SNOWFLAKE_ACCOUNT=<your-account-identifier>
# Example: xy12345.us-east-1 or myorg-myaccount

VITE_SNOWFLAKE_WAREHOUSE=COMPUTE_WH
# Warehouse to use for queries (don't hardcode - may be suspended/renamed)

VITE_SNOWFLAKE_ROLE=SYSADMIN
# Role with access to DELPHI_EXPLORATION schema (user may have multiple roles)
```

Note: Client ID is always `LOCAL_APPLICATION` for the built-in integration.

### Step 6: Update Router
**File:** `ui/src/App.tsx`

- Add `/oauth/callback` route

### Step 7: Configure Vite for 127.0.0.1 + Snowflake Proxy
**File:** `ui/vite.config.ts`

**Critical:** The Snowflake SQL API does not support CORS. The Vite proxy is required, not optional.

Snowflake's LOCAL_APPLICATION requires redirect to `127.0.0.1` (not `localhost`):

```typescript
export default defineConfig({
  server: {
    host: '127.0.0.1',  // Required for Snowflake OAuth
    port: 5173,
    proxy: {
      '/snowflake-api': {
        target: `https://${process.env.VITE_SNOWFLAKE_ACCOUNT}.snowflakecomputing.com`,
        changeOrigin: true,
        secure: true,
        rewrite: (path) => path.replace(/^\/snowflake-api/, ''),
        // Essential for Snowflake to accept the proxied request
        configure: (proxy, _options) => {
          proxy.on('proxyReq', (proxyReq, req, _res) => {
            proxyReq.setHeader('Origin', `https://${process.env.VITE_SNOWFLAKE_ACCOUNT}.snowflakecomputing.com`);
          });
        },
      },
    },
  },
  // ...
})
```

Frontend code calls `/snowflake-api/api/v2/statements` instead of direct Snowflake URLs.

## Key Files to Modify/Create

| File | Action | Purpose |
|------|--------|---------|
| `ui/src/api/snowflake.ts` | Create | Snowflake OAuth + SQL API client |
| `ui/src/pages/OAuthCallback.tsx` | Create | Handle OAuth redirect |
| `ui/src/components/SnowflakeConnect.tsx` | Create | Connection button/status |
| `ui/src/pages/TrendAnalysis.tsx` | Modify | Use Snowflake data when available |
| `ui/src/App.tsx` | Modify | Add OAuth callback route |
| `ui/vite.config.ts` | Modify | Set host to 127.0.0.1 |
| `ui/.env.local` | Modify | Add Snowflake account |

## SQL Query for Historical Trend Data

```sql
SELECT
    t.TIKTOK_ID,
    t.TRACK,
    t.ARTIST,
    s.TIMESTP,
    s.POSTS,
    s.POSTS - LAG(s.POSTS) OVER (ORDER BY s.TIMESTP) as DAILY_NEW_POSTS
FROM DELPHI_EXPLORATION.CHARTMETRIC.TIKTOK_STAT s
JOIN DELPHI_EXPLORATION.CHARTMETRIC.TIKTOK t ON s.TIKTOK = t.ID
WHERE t.TIKTOK_ID = ?
ORDER BY s.TIMESTP DESC
LIMIT 90
```

## SQL API Async Handling

The Snowflake SQL API is asynchronous. For most small queries (like our `LIMIT 90`), results return immediately in `resultSet`. However, if a query takes longer than ~45 seconds, Snowflake returns a `202 Accepted` response with a `statementHandle` instead of data.

**Implementation in `executeQuery()`:**

```typescript
const WAREHOUSE = import.meta.env.VITE_SNOWFLAKE_WAREHOUSE;
const ROLE = import.meta.env.VITE_SNOWFLAKE_ROLE;

async function executeQuery(sql: string): Promise<QueryResult> {
  const response = await fetch(SQL_API_URL, {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${getAccessToken()}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({
      statement: sql,
      warehouse: WAREHOUSE,  // From env - don't hardcode
      role: ROLE,            // Ensures correct permissions
      database: 'DELPHI_EXPLORATION',
      schema: 'CHARTMETRIC',
    }),
  });

  // Handle 401 - token expired
  if (response.status === 401) {
    await refreshAccessToken();
    return executeQuery(sql); // Retry with new token
  }

  const data = await response.json();

  // Immediate result (most common for small queries)
  if (data.resultSetMetaData && data.data) {
    return parseResultSet(data);
  }

  // Async result - need to poll
  if (response.status === 202 && data.statementHandle) {
    return pollForResult(data.statementHandle);
  }

  throw new Error(data.message || 'Query failed');
}

async function pollForResult(handle: string, maxAttempts = 30): Promise<QueryResult> {
  for (let i = 0; i < maxAttempts; i++) {
    await sleep(2000); // Wait 2 seconds between polls

    const response = await fetch(`${SQL_API_URL}/${handle}`, {
      headers: { 'Authorization': `Bearer ${getAccessToken()}` },
    });

    const data = await response.json();

    if (data.data) {
      return parseResultSet(data);
    }

    if (data.status === 'FAILED') {
      throw new Error(data.message);
    }
    // Still running, continue polling
  }
  throw new Error('Query timeout - exceeded max polling attempts');
}
```

## Verification

1. Start dev server: `cd ui && npm run dev`
2. Open `http://127.0.0.1:5173` (not localhost!)
3. Click "Connect to Snowflake" button
4. Authenticate via SSO in browser popup/redirect
5. Verify connection status shows green/connected
6. Enter sound ID `7537176505709808415`
7. Verify chart shows real historical data (not "sampled" disclaimer)
8. Check daily creations graph shows actual growth (~100K new posts/day)

## Risks & Mitigations

| Risk | Mitigation |
|------|------------|
| Token expiration (~10 min) | Implement refresh token flow with automatic retry (see Token Management above) |
| Query timeout (>45s) | Poll for results using statementHandle (see SQL API Async Handling above) |
| LOCAL_APPLICATION not available | Fallback: create custom security integration (requires admin) |
| Rate limits | Add request throttling/caching in IndexedDB |
| Sound not in Chartmetric DB | Fall back to Apify sampled data |
| Production deployment | Proxy only works in dev; production needs backend service or Snowflake Connector |

## Production Considerations

The Vite proxy solution works for local development but won't work in production (static hosting). Options for production:

1. **Keep as internal tool** - Only run locally in dev mode
2. **Add backend API** - Simple Node/Express endpoint to proxy Snowflake calls
3. **Snowflake Connector for Node.js** - More robust, handles connection pooling
4. **Serverless function** - AWS Lambda / Vercel Function to proxy calls