Is it possible to do programmatic automation with Final Cut Pro? I have a large library of videos for a video instruction product. I have mostly been automating my workflows with FFmpeg and Ruby. I pull information such as titles, instructors' names, etc. from a database, and do some simple intro animations. 

Is it possible to programmatically manipulate video with Final Cut Pro (I haven't bought it yet)? For example could I add join an “intro” animation, overlay animated text with all my 3000 videos for example?

How is automation done on large file sets done in the Video Production world? I'm a software engineer by trade, and I have only basic video production knowledge (but I want to learn!).
Davinci Resolve and Fusion would be a better choice than FCPX/Motion if you're looking for something that supports standard scripting languages (lua and python 2/3).  From there, you can install whatever modules you need to get hooks into your data.  Adobe has their own ExtendScript for AfterEffects, which is based on JavaScript, but I can't really comment on it.  FCPX and Motion are probably the least script friendly of the major NLEs.  I'd guess that most larger studios probably use Nuke, which also uses Python, but if you're just starting out, Resolve/Fusion is a cheap entry point which scales well towards Nuke.

Automation of large file sets typically falls under the purview of a production's Technical Director (TD), who would be great people to befriend if this type of work interests you.