Honestly, you have several questions to unwrap and there is no perfect guidance. Database versioning is difficult at best. However, there is guidance that I have been successful with. In all cases, starting where you are as "version 0" is your best bet.
For your first statement, use one of the following methodologies (or a combination that works best):
EntityFramework Code First Migrations - Treat the current state of your schema as well as any reference tables as version 0.
DbUp and the DbUp Azure Pipelines tasks - This is a very simple, but effective way to get a "poor-man's" database migration methodology, if you follow some simple rules/conventions. There are also ways to integrate "running" DbUp migrations as part of the start-up of your application, too, but I've had success executing DbUp migrations via Azure Pipelines releases.
Red Gate Database Lifecycle Management (DLM) Automation Suite - This requires a bit more planning and comes with a cost, but Red Gate's tools integrate well and give you more of a complete DLM picture from integration of SSMS with Git version control, drift detection, Azure Pipelines tasks, and more.
As far as the question of should you couple versioning of the database with the application, you'll need to answer this question. Is the database shared between more than one application or is it dedicated to that application. If "dedicated" is your answer, then versioning of the database with the application is the right thing to do and should be done so within the same source code repository. If not, it will be difficult to decide which application "owns" the versioning of the database or if it needs to be versioned entirely independent of your apps. If it is versioned independent of your apps, then you better have strong unit testing in all of the apps.
Lastly, I will assume your "pitfalls of using such logic" refers to using your applications initialization/startup code to run an upgrade of the database to the intended version. If I've assumed correctly, there is no right/wrong way. What matters is that you understand where you need the automated process to fail to ensure your application does not incur a downtime because of a failed database migration. If you upgrade a DB during initialization/startup of an application, then your app better have a fallback mechanism where it can function with the previous version of the DB until which time it's fixed. This is where I'd recommend making failure of a DB upgrade part of the deployment process itself and not part of the application. That way, if the DB upgrade fails (and you're doing it correctly), reverting to the previous version of the app upon failure ensures that downtime is minimized.
And, when I refer to doing DB upgrades "the right way," I refer to using methods that ensure the state of the DB schema is only changed atomically and transactionally such that failures in the middle of a deployment can be completely rolled back.