This is not a question but rather a share, in case someone else here faces a similar challenge to me. No answer is expected.

I have an SQL database with 800+ rows that can be filtered using 12 different criteria.

I needed to dynamically assign a row number consistently starting with #1 for any given search result.

Long story short, here is how to do it:

A) First create an empty column number in first position in your database

B) Insert the following JS on the page where you datatable is displayed (I do this using hooks)

<script type="text/javascript">
jQuery(window).on('load',function(){
    var drawIndex = false;
   wpDataTables.table_1.api().on( 'order.dt search.dt', function ( e, dt, type, indexes ) {
        wpDataTables.table_1.api().column(0, {search:'applied', order:'applied'}).nodes().each( function (cell, i) {
            if (!drawIndex){
                if (cell.innerHTML.includes('responsiveExpander')){
console.log(i+1);
                    cell.innerHTML =  cell.innerHTML + (i + 1);
                }else if (cell.innerHTML =='') {
                    cell.innerHTML = i+1;
console.log(i+1);
                }
            }
cell.innerHTML = i+1;
        } );
        drawIndex = true;
    } ).draw();
 });
</script>