To answer my own question, the code responsible for updating the vid field in the node table is in Drupal\Core\Entity\Sql\SqlContentEntityStorage::doSaveFieldItems.

In my particular case, it's line 937.

To fix my issue, I actually simply needed to add the following line before saving the moderated revision (after $node->set('moderation_state', 'published')):

$node->isDefaultRevision(TRUE);

This instructs the code in Drupal\Core\Entity\Sql\SqlContentEntityStorage::doSaveFieldItems to update the node table as well as the node_field_data table.

To answer my own question, the code responsible for updating the vid field in the node table is in Drupal\Core\Entity\Sql\SqlContentEntityStorage::doSaveFieldItems.

In my particular case, it's line 937.

To fix my issue, I actually simply needed to add the following line before saving the moderated revision (after $node->set('moderation_state', 'published')):

$node->isDefaultRevision(TRUE);

Although the field revision_default was already set to 1 in the node_revision table, the isDefaultRevision flag needs to be set to TRUE in order for the code in Drupal\Core\Entity\Sql\SqlContentEntityStorage::doSaveFieldItems to update the node and node_field_data tables.