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.