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

[aenascut]

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

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:

// 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

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

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

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

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:

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: fix(llmo): use canonical baseURL for V1 DRS prompt generation #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