For ctrl_orca, the files that are used are under $CAT_DIR/sql (setup_storeFunctions.sql, setup_perRunTable*.sql, lsstSchema4mysql*.sql). Those are set up in the lsst10 database, but I'm not sure how (or even if) the tables are being used by the current pipelines. I believe the intent at the time was to have orchestration set those up blindly, and cat would change as the pipelines saw fit. That way orchestration didn't have to be rev-ed every time that cat SQL files needed updating, or worse, that we'd have to have people download ctrl_orca and mess around with its innards to get at a couple of SQL files that they wanted to modify. All those SQL files could probably move to new package (cat_sql?), if we wanted to retain that functionality. I don't believe ctrl_orca uses anything else in that package.
As for ctrl_orca's use of ctrl_provenance... Way back, when the original .paf files used by orchestration went away, I removed the calls to methods in ctrl_orca that end up calling ctrl_provenance because a new provenance method was going to be on the way. All of the code remained, so I would be able to add that functionality back in at some point.
I believe that ctrl_provenance itself doesn't make direct references to the SQL files in cat, but they are logically bound. In other words, the provenance recorder expects that the "prv_Run" table be in place before the time the ProvenanceRecorder is called. My memory is a bit shaky about this because I didn't write that code. There might be direct dependencies that I'm not aware of. Again, ctrl_orca doesn't use ctrl_provenance at the moment, but retains the dependency for the time in the future when the new ctrl_provenance arrives.