What happens when the airline company scans your passport at the check-in counter?

An exact answer will depend on the airline, the country you are in, and the country/countries you are flying to/through, but as some examples of what will/might happen...

• The airline themselves will store the data for future reference.

• For flights from the US, the information will be transmitted to the US government via the APIS system. This will be used for multiple purposes, including exit immigration, no-fly lists, etc. Many countries have similar systems, especially those that don't generally have physical exit immigration (like the US).

• For flights to/through some countries, an electronic check will be done to the government of that country to check that they will allow the person to travel, and have the required documents (visa, travel approval, etc). This varies a lot from country to country, but as an example for travel to Australia this is done via the Advanced Passenger Processing system which will confirm that the passenger has a valid visa, eTA, or other means to be allowed to enter/transit the country.

• The airlines computer system may use the information to flag to staff to carry out a manual documentation check to make sure that you have a suitable visa in your passport for the destination you are travelling to, in addition to meeting any other conditions for travel (eg, return/onward ticket).

• The information may be updated in the flight booking, which may then be available to the travel agent that booked the ticket.

Depending on the exact situation it's possible the data will be shared with other parties.

In the situation you described it's possible that your exit from the US was not recorded correctly, as the US government would have recorded an "exit" by a French citizen, and specifically a French citizen who was potentially not legally in the US at the time (based on not having a corresponding entry record), rather than a US citizen. Odds are that this will not cause any issues in the future, but it would be worth at least remembering that this occurred in case it is raised at some stage in the future, and be ready to answer any questions about it when next returning to the US (where you should use your US passport for entry as is required by law).

Airline tickets are not generally bound to the passport, as you've discovered, but to the name. It is frequently possible to buy tickets without giving passport information, but airlines sometimes ask for it up front.

The purpose of the passport is twofold. First, it identifies you. That is, it proves that you are the person whose name is on the ticket. Second, it establishes that you meet the documentation requirements imposed by the country to which you are flying, perhaps in combination with a visa or other permit.

I routinely check in for flights on the same reservation using dissent passports because I am a dual citizen of the US and the EU, and I fly between the US and the EU anywhere from one to five times a year. I almost always have a round trip ticket, and I always use the EU passport when I check in for a flight to Europe and the US passport when I check in for the flight to the US.

Is the passport just checked to confirm it is valid?

No. It is checked for validity and for other things. In particular, the nationality shown on the passport is taken into account in determining whether the traveler needs a visa, and the name on the passport is checked against the name on the reservation.

and/or is the specific passport used tied to the individual and this information is kept in a database?

The passport is tied to the individual because that is in the nature of passports. Whether the passport is retained in the airlines' databases is mostly up to the airline. Some companies allow travelers to create a profile and save their passport data in the profile to ease online check in, for example. Passport data is usually sent to the destination country in the passenger manifest, where it will be stored in a database at least until the passenger clears immigration control and probably for decades thereafter. Some countries, notably the US and the UK, receive passenger manifests from departing flights so they can reconcile the departures against arrival records, because they do not have government agents inspecting the passports of departing passengers.

Moreover, would any government agency/organization be able to see, from the check-in scan, what passport was shown to the airline (or would that be taken from what was used to purchase the actual ticket)?

In general, if a government sees any passport data from the airline, it will be the passport that was scanned at check in. First, this is because passport data may not have been collected at the time of purchase, or it may have been entered incorrectly. Second, the traveler may have lost the original passport between the time of purchase and the day of travel, or indeed the traveler may be a dual citizen who is required to use different passports for different legs of the journey for legal reasons.