MVC pattern and Alembic

[norton120]

Maybe these should be three different discussions, but they feel pretty tightly coupled.
I'm working on this PR #1357 and I'm thinking that 3 things are going to get pretty sticky pretty fast. One is the MetadataStore pattern, another is the migrations via metasdatastore init, and the last is the missing referential integrity.

With the MetadataStore pattern is seems like the db models are pretty much dumb table definition containers, and then all the models' methods live in this one big mega-class. Especially since the db sessions are all sync (so there's not the added pain of asyncio and awaitable_attrs etc), I think a more traditional MVC "M" pattern might de-clutter and simplify the inheritance chain.

If we move methods into their respective db objects (ideally inherit from an extended base object that handles things like crud for 99% of cases) and then pass the db session and configs down as dependency injection (pretty standard FastAPI pattern), it would create nice test hooks - and it would also mean that for a "personas" route you are only importing a "PersonaModel" object, instead of big constructor objects that obscure it.

Tied up with that is we are going to need migrations. The data model has to be able to evolve without blowing away existing database instances out there, and alembic is the best way to do that when SQLalchemy is the ORM.

Sqlalchemy ORM is a very battle-hardened and robust MVC and there's a ton of work that it can just handle (like related item lookups, natural partitioning of objects by a team or user, complex defaults) if we set up all the RDBMS stuff - fkeys, relationships etc. SQLAlchemy and ActiveRecord were the rockstars in the fat model, skinny controller days, so leaning into that could make things much more enjoyable when coding over in the view layer (ie the routers).

How do people feel about this? It is work I'm happy to dig into (in small chunks), but also pretty fundamental changes in the design so worth a conversation or three I think