Kindly, I agree - it would be much preferable to have independent storage for plugins and this would be easy to do if we were using another type of storage already. As it stands, however, our storage mechanism is SQL. I think we should use it for what it is as much as possible and do the weird, vertical stuff (k,v tables, swapping to redis) only if we really need it. For everything else: lets use SQL as it was intended.

Examples:

We want to develop an apps catalogue as a CKAN plugin. While we could certainly put this in Redis, there is no reason why we can't have the following table: application (id, name, title, description, author, project_url, site_url, code_url, image).

A watchlist plugin could essentially work on UUIDs alone. What you'd end up with is something like this: watch (id, user, scope_id).

Re migrations you're right, but my first intention would be to handle that seperatly for each plugin (i.e. they need to have their own migration repositories that they keep track of, e.g. via an apps_migrate_version table)

I do not think we need to 'extend the model' if you intend to make the migrations separate. If the schema is decoupled, then there are no problems. So each plugin can have its own model and use sqlalchemy independently i.e have their own metadata, classes and mappers. They do not have to even use sqlalchemy.

What I mean is that there is no need to do anything apart from.

Agree on a naming convention of the plugin tables (including their own migrate table each)

Agree to the rule that no plugin can add a column to an existing table.

Agree that no table can have a (database level) foreign key constraint between the core tables and itself in either direction. They *can* have implied sqlalchemy level joins.

Maybe have a hook that on db upgrade all plugins are upgraded.

Each plugin will have to redefine the tables, classes and mappers they need to join onto the core tables themselves. reusing/extending the core model will not be worth the trouble.

This seems to cover your use cases and this way everything is nicely decoupled. Best of all there is very little work to do...