Bug: V1 onboarding uses overrideBaseURL for DRS prompt generation, fails brand lookup

## Problem Statement

**ALL sites in Paramount org fail DRS prompt generation with identical error:**

```
Error: No brand matching site {siteId} in org {orgId}
```

This is not a one-off bug—it's an **organization-wide failure** indicating a systemic issue with the Paramount org's migration from V1 to V2 mode.

## Critical Findings

### 1. Org-Wide Failure Pattern

All three sites in Paramount org (`296fefc8-1e54-46dd-aee4-a0f94621deaf`) fail with 100% failure rate:

| Job ID | Site | Submitted | Status |
|--------|------|-----------|--------|
| **b374161e** | paramountliquor.com.au | 2026-05-08 14:20 | ❌ FAILED |
| **474fa9f1** | bottle-stop.com.au | 2026-05-08 12:24 | ❌ FAILED |
| **462d6811** | radissonhotels.com | 2026-05-08 13:29 | ❌ FAILED |

### 2. Onboarding Mode: Auto-Detected by Org Creation Date

`resolveLlmoOnboardingMode()` determines V1 vs V2 per organization:
- **Before 2026-04-01:** V1 mode (legacy)
- **After 2026-04-01:** V2 mode (new)
- **Feature flag override:** `brandalf` flag can force V2 for old orgs

**Paramount org was created 2025-02-24 (before cutoff) → default V1, but was manually upgraded to V2 via feature flag.**

### 3. Control Case: McCain Org (Fresh V2)

McCain org created 2026-05-05 (after cutoff):
- Fresh V2 org from inception
- Job **1b7f531d** submitted 2026-05-08
- Status: ✅ **SUCCEEDED**
- Reason: Site and brand created together in consistent state
- Brand has **non-empty siteIds** → strict resolver succeeds

### 4. Bypass Case: CPKCR Org (V1)

CPKCR org created 2025-10-08 (before cutoff):
- V1 mode by default
- Job **6862eb34** submitted 2026-05-06
- Status: ✅ **SUCCEEDED**
- Reason: No brand created (V1 path skips brand creation)
- DRS skips v2 sync entirely

## Root Cause: Organization State Inconsistency

**Paramount org migration from V1 → V2 created a database state mismatch:**

### The Migration Problem

1. **Org created 2025-02-24:** V1 mode
- Sites created without brand requirements
- No feature flags set

2. **Later (unknown date):** `brandalf` flag manually enabled
- Organization marked as V2
- Sites table unchanged
- Brands table expected to exist and link properly

3. **When onboarding runs:**
- `resolveLlmoOnboardingMode()` finds `brandalf` flag enabled → V2
- Calls `upsertBrand()` → creates brand in v2 catalog
- Calls `syncBrandSites()` → queries sites table to link brand
- **Query returns empty** (sites not found)
- No brand_sites rows created
- Brand.siteIds stays empty

4. **When DRS job runs:**
- Gets brands from SpaceCat v2 catalog
- Finds brand with **empty siteIds**
- Strict resolver (`find_brand_for_site_strict()`) fails
- Job fails with `BrandResolverDriftError`

### Why syncBrandSites Fails for Paramount

The query in `syncBrandSites()` (brands-storage.js:218-222):

```javascript
const { data: sites } = await postgrestClient
.from('sites')
.select('id, base_url')
.eq('organization_id', organizationId)
.in('base_url', [...pathsByBase.keys()]);

if (!sites || sites.length === 0) {
return; // Silent failure
}
```

Returns empty for Paramount but succeeds for McCain because:

**Hypothesis:** When Paramount org was migrated, one of these occurred:
1. **Sites table not properly migrated:** Sites exist but organization_id column has wrong value
2. **base_url format mismatch:** Sites stored with different URL format than brand URL (e.g., www vs no-www)
3. **Query condition broken:** Organization state prevents exact-match query from working
4. **Feature flag timing:** Feature flag enabled before sites were properly migrated

For McCain (fresh V2), none of these issues exist—site and brand created together.

## Code Path Analysis

### resolveLlmoOnboardingMode() (llmo-onboarding-mode.js)

Determines V1 vs V2 mode:

```javascript
// Line 28: Cutoff = 2026-04-01
const LLMO_BRANDALF_GA_CUTOFF_MS_DEFAULT = Date.UTC(2026, 3, 1);

// If org created after cutoff → V2
// If org has brandalf flag enabled → V2 (override)
```

- **Paramount:** Created before cutoff, but flag enabled → forced to V2
- **McCain:** Created after cutoff → naturally V2
- **CPKCR:** Created before cutoff, no flag → stays V1

### Execution Flow for Paramount

1. `resolveLlmoOnboardingMode()` detects V2 (flag enabled)
2. Takes V2 path (lines 1343-1413 in llmo-onboarding.js)
3. Calls `upsertBrand()` (line 1373)
4. `upsertBrand()` calls `syncBrandSites()` (line 558 in brands-storage.js)
5. syncBrandSites queries sites table, gets empty result
6. Returns silently, brand.siteIds empty
7. Later, DRS job finds brand but no sites in siteIds
8. Strict resolver fails → job fails

### Execution Flow for McCain

1. `resolveLlmoOnboardingMode()` detects V2 (org after cutoff)
2. Takes V2 path (lines 1343-1413 in llmo-onboarding.js)
3. Calls `upsertBrand()` (line 1373)
4. `upsertBrand()` calls `syncBrandSites()` (line 558 in brands-storage.js)
5. syncBrandSites queries sites table, **gets site match**
6. Creates brand_sites rows, brand.siteIds populated
7. Later, DRS job finds brand with sites in siteIds
8. Strict resolver succeeds → job succeeds

## Investigation Required

### 1. Paramount's Feature Flag History

```sql
SELECT * FROM feature_flags
WHERE organization_id = '296fefc8-1e54-46dd-aee4-a0f94621deaf'
AND product = 'LLMO'
ORDER BY updated_at DESC
```

- When was `brandalf` flag created?
- Has it always been true, or was it updated?
- Are there any error logs from when it was enabled?

### 2. Paramount's Sites in PostgreSQL

```sql
SELECT id, base_url, organization_id, created_at
FROM sites
WHERE organization_id = '296fefc8-1e54-46dd-aee4-a0f94621deaf'
ORDER BY created_at
```

Expected for all 3 sites:
- Do all 3 sites exist?
- Do organization_ids match exactly: `296fefc8-1e54-46dd-aee4-a0f94621deaf`?
- Are base_urls formatted correctly (no-www canonical)?

### 3. Paramount's Brand-Sites Linkage

```sql
SELECT b.id, b.name, b.organization_id, COUNT(bs.site_id) as linked_sites
FROM brands b
LEFT JOIN brand_sites bs ON b.id = bs.brand_id
WHERE b.organization_id = '296fefc8-1e54-46dd-aee4-a0f94621deaf'
GROUP BY b.id, b.name, b.organization_id
```

Expected result: 0 rows or all with `linked_sites = 0`
- No brand_sites rows exist
- Every brand has empty siteIds

### 4. McCain's (Control) Brand-Sites Linkage

```sql
SELECT b.id, b.name, b.organization_id, COUNT(bs.site_id) as linked_sites
FROM brands b
LEFT JOIN brand_sites bs ON b.id = bs.brand_id
WHERE b.organization_id = 'd9aae1ba-2443-4a30-a8c9-5b1a58390966'
GROUP BY b.id, b.name, b.organization_id
```

Expected result: Every brand has `linked_sites > 0`
- brand_sites rows exist
- Sites are properly linked

## Solutions

### Part 1: Short-term (Fixes Paramount)

Once root cause identified via PostgreSQL queries:

**If sites have wrong organization_id:**
```sql
UPDATE sites
SET organization_id = '296fefc8-1e54-46dd-aee4-a0f94621deaf'
WHERE id IN ('738d9ce9-3879-4640-a345-25b5ae8bb92a',
'69847518-1efb-42a0-945a-d9ec37832620',
'74ebfcef-70e0-4537-afdc-6e61176c362b')
```

**If base_url format is inconsistent:**
- Normalize base_urls in sites table to match brand URLs

**Then re-run syncBrandSites:**
- Manually call syncBrandSites for all Paramount brands
- OR re-trigger onboarding for affected sites

### Part 2: Medium-term (PR #2374)

- **Commit 1:** Use canonical baseURL for DRS jobs (metadata consistency)
- **Commit 2:** Add diagnostics to syncBrandSites (log when sites not found)

This helps prevent similar issues and provides visibility.

### Part 3: Long-term (Prevent future migrations)

Once root cause known:
- Add validation in org migration path
- Verify sites table is consistent before enabling V2 mode
- Add tests for V1→V2 org migrations
- Consider automated remediation on feature flag enable

## Key Metrics

| Org | Created | Mode | Reason | Status |
|-----|---------|------|--------|--------|
| **McCain** | 2026-05-05 | V2 (natural) | After GA cutoff | ✅ Works |
| **CPKCR** | 2025-10-08 | V1 (natural) | Before cutoff | ✅ Works |
| **Paramount** | 2025-02-24 | V2 (migrated) | Flag enabled | ❌ Broken |

## References

- PR: #2374 (symptom + diagnostics)
- Paramount org UUID: `296fefc8-1e54-46dd-aee4-a0f94621deaf`
- Paramount IMS org: `38931D6666E3ECDA0A495E80@AdobeOrg`
- Failed jobs: b374161e, 474fa9f1, 462d6811
- Working control: McCain (1b7f531d, job ID from today)
- Code: resolveLlmoOnboardingMode (llmo-onboarding-mode.js), syncBrandSites (brands-storage.js)
- Related: LLMO-4534, LLMO-4650, LLMO-4129, LLMO-4723

Start with llmo-onboarding-mode.js and brands-storage.js, especially syncBrandSites and the cited query. Run the provided PostgreSQL checks for Paramount and McCain to compare organization IDs, base URLs, and brand_sites links. Done means the migration failure is explained, affected sites can be linked reliably, and diagnostics or tests cover the inconsistent state.

Written by the indexing model from the issue text.