I like the sound of per library settings, that sounds rather more flexible. And standardising on some common field names like "pages" shouldn't be an issue. I think there would need to be the flexibility to allow a plugin to have some additional field(s) that are bespoke to it without them being centrally defined in the Calibre code, so defined as part of a plugin's API sounds a plan.
Charles you raised an interesting point about the specification including the data types a custom column must conform to for matching - I've been able to get away with hard-coding this when it has been self-contained in the plugin.
I also hadn't seriously considered series type fields which are probably going to be a pain to deal with. The only use I could think of such a field would be for an "alternate series", but then you need to find a metadata source that could populate it (none of the B&N, Goodreads, Fantastic Fiction, Webscription plugins could populate this). I guess you don't like shortcuts
As for where this mapping translation takes place, I wonder whether we can get away with waiting until set_metadata. The situation that comes to mind is the edit metadata dialog, which of course has the ability to display custom column values. Maybe it is a special case. I would have thought you would have to get the metadata object updated into custom columns by when the Download Metadata button has finished so it can refresh the dialog contents (which are not yet persisted)?