Function resolveFromDatabase

server/src/db/domain-resolution-db.ts:144–207

* Database-only resolution: exact match, brand alias, sub-brand hierarchy.

(normalized: string)

 * Database-only resolution: exact match, brand alias, sub-brand hierarchy.
 */
async function resolveFromDatabase(normalized: string): Promise<DomainResolution | null> {
  const pool = getPool();

  // 1. Exact match: organization_domains or organizations.email_domain
  const exact = await pool.query<{ workos_organization_id: string; domain: string }>(
    `SELECT o.workos_organization_id, COALESCE(od.domain, o.email_domain) AS domain
     FROM organizations o
     LEFT JOIN organization_domains od
       ON o.workos_organization_id = od.workos_organization_id AND od.domain = $1
     WHERE o.email_domain = $1 OR od.domain = $1
     LIMIT 1`,
    [normalized]
  );

  if (exact.rows.length > 0) {
    return {
      orgId: exact.rows[0].workos_organization_id,
      matchedDomain: exact.rows[0].domain,
      method: 'exact',
    };
  }

  // 2. Brand alias: domain is a known alias → resolve to canonical brand → find org
  //    e.g. omc.com → omnicomgroup.com → Omnicom org
  const brandAlias = await pool.query<{ workos_organization_id: string; brand_domain: string }>(
    `SELECT o.workos_organization_id, bda.brand_domain
     FROM brand_domain_aliases bda
     JOIN organizations o ON o.email_domain = bda.brand_domain
     WHERE bda.alias_domain = $1
     LIMIT 1`,
    [normalized]
  );

  if (brandAlias.rows.length > 0) {
    return {
      orgId: brandAlias.rows[0].workos_organization_id,
      matchedDomain: brandAlias.rows[0].brand_domain,
      method: 'brand_alias',
    };
  }

  // 3. Sub-brand: domain is a discovered brand whose house_domain maps to an org
  //    e.g. instagram.com → house_domain=meta.com → Meta org
  const subBrand = await pool.query<{ workos_organization_id: string; house_domain: string }>(
    `SELECT o.workos_organization_id, db.house_domain
     FROM brands db
     JOIN organizations o ON o.email_domain = db.house_domain
     WHERE db.domain = $1
       AND db.house_domain IS NOT NULL
       AND db.house_domain != $1
     LIMIT 1`,
    [normalized]
  );

  if (subBrand.rows.length > 0) {
    return {
      orgId: subBrand.rows[0].workos_organization_id,
      matchedDomain: subBrand.rows[0].house_domain,