The limitations are relative to what you want to do, as for many apps there are no limitations, for others there could be many, so its not generalized exactly. Other than delegation rules versus dataverse etc it can be amazing, other times not so much.
For me and
@WarrenBelz can chime in, I find that as long as I optimize my lists, my delegation, linkage via ID's versus lookups (in most cases), I prefer lists over choices (in many cases, especially multi-select) etc but some is just my personal preference.
Unlike many who may not "like" or leverage SharePoint much I use it significantly at customers huge and small and for myself as well.
As for your other question
1. If you want, you can use a flow to read and write all the data into SharePoint. The downside is that it will be written (created by, modified by etc) that account. But you can do this versus letting a person have direct access
2. In real-time you can also share items and change permissions to allow it to be written by a person and then revoked, it's possible but a bother (IMHO).
If there was one gap I wish was filled better its actual Row / List etc security against SharePoint but its not really a database so its not intended to be a giant relational database even though many of us try to use it that way.
@WarrenBelz builds on it a lot and I am certain can provide some other thoughts as well
If these suggestions help resolve your issue, Please consider Marking the answer as such and also maybe a like.
Thank you!
Sincerely, Michael Gernaey
