Node revision issue: langcode for all the revisions is default language

UPDATED SUMMARY.

When using content moderation with content translation it is very difficult as a developer to work with these two systems and develop features that work correctly and properly for all languages. A good example of this is the revisions tab which will only label a single revision as "current revision" - which shows on the last language which was saved. If you look at the attached image revisioning_1.png you will see that since the EN version was the last one saved, it is labelled "current revision" and the revisions on the FR side do not have a current revision even though it "kinda" is the one at the top.

Delving deeper into it, a develop discovers that the revisions shown on the revisions tab don't exactly match what you see in the database:

SELECT * FROM `node_revision` WHERE `nid` = '9932'

nid	vid	langcode	revision_uid	revision_timestamp	revision_log	revision_default
9932	91525	en	1	1744226538	NULL	1
9932	91526	en	1	1744226558	NULL	1
9932	91527	en	1	1744226702	NULL	1
9932	91528	en	1	1744226729	NULL	1

In the above table one can see that ALL revision are EN, which is quite a head scratcher for a non-core dev.

And, then when looking at content_moderation_state_field_data:

SELECT * FROM `content_moderation_state_field_data` WHERE `content_entity_id` = '9932' 

id	revision_id	langcode	uid	workflow	moderation_state	content_entity_type_id	content_entity_id	content_entity_revision_id	default_langcode	revision_translation_affected
9731	91148	en	1	editorial	draft	node	9932	91528	1	1
9731	91148	fr	1	editorial	draft	node	9932	91528	0	NULL

Here you have both language referencing the 'current version' 91528.

And, if you look at the content_moderation_state_field_revision:

SELECT * FROM `content_moderation_state_field_revision` WHERE `content_entity_id` = '9932'

id	revision_id	langcode	uid	workflow	moderation_state	content_entity_type_id	content_entity_id	content_entity_revision_id	default_langcode	revision_translation_affected
9731	91145	en	1	editorial	draft	node	9932	91525	1	1
9731	91146	en	1	editorial	draft	node	9932	91526	1	NULL
9731	91146	fr	1	editorial	draft	node	9932	91526	0	1
9731	91147	en	1	editorial	draft	node	9932	91527	1	NULL
9731	91147	fr	1	editorial	draft	node	9932	91527	0	1
9731	91148	en	1	editorial	draft	node	9932	91528	1	1
9731	91148	fr	1	editorial	draft	node	9932	91528	0	NULL
 

This is confusing because there lots of entries here. And, if you transition the node in both languages to published you get this:

9731	91149	en	1	editorial	published	node	9932	91529	1	1
9731	91149	fr	1	editorial	draft	node	9932	91529	0	NULL
9731	91150	en	1	editorial	published	node	9932	91530	1	NULL
9731	91150	fr	1	editorial	published	node	9932	91530	0	1

Just to add to the confusion, you get entries for both languages for each revision with different states!

As mentioned in the comments in code you can eventually figure out how to get things like the latest published English and the Latest published French versions using the methods developed around latest translation affected. I have found this on sites with a lot of revisions to not be very performant. I created the cm_revision_delete module to at least prune some revisions to reduce the DB size.

I am not sure what the fix is here, I really respect the awesomeness of the core devs that built this. As a developer that builds websites for the Canadian Government where the translations of all content must match by law, I have had some challenges working with content_moderation / content_translations to meet certain requirement such as "All language translations must be published at the same time".

I hope this information helps.