Function checkAndAssignOrganizationByDomain

server/src/slack/sync.ts:600–706

(
  workosUserId: string
)

 * organization domain, adds them to that organization.
 */
export async function checkAndAssignOrganizationByDomain(
  workosUserId: string
): Promise<{
  assigned: boolean;
  organizationId?: string;
  organizationName?: string;
  previousOrgId?: string;
  previousOrgName?: string;
  error?: string;
} | null> {
  const pool = getPool();

  try {
    const membershipResult = await pool.query<{
      email: string;
      workos_organization_id: string;
      org_name: string;
      is_personal: boolean;
    }>(`
      SELECT om.email, om.workos_organization_id, o.name as org_name, o.is_personal
      FROM organization_memberships om
      JOIN organizations o ON o.workos_organization_id = om.workos_organization_id
      WHERE om.workos_user_id = $1
      LIMIT 1
    `, [workosUserId]);

    if (membershipResult.rows.length === 0) {
      return null;
    }

    const { email, workos_organization_id: currentOrgId, org_name: currentOrgName, is_personal: isPersonal } = membershipResult.rows[0];

    if (!isPersonal) {
      return null;
    }

    const domain = email.split('@')[1]?.toLowerCase();
    if (!domain || isFreeEmailDomain(domain)) {
      return null;
    }

    const domainResult = await pool.query<{
      workos_organization_id: string;
      org_name: string;
    }>(`
      SELECT od.workos_organization_id, o.name as org_name
      FROM organization_domains od
      JOIN organizations o ON o.workos_organization_id = od.workos_organization_id
      WHERE LOWER(od.domain) = $1
        AND o.is_personal = false
      LIMIT 1
    `, [domain]);

    if (domainResult.rows.length === 0) {
      return null;
    }

    const { workos_organization_id: targetOrgId, org_name: targetOrgName } = domainResult.rows[0];