[FEATURE] Plugin Development - Using Alembic with Custom Database Tables

Currently, on the subject of adding database tables to CTFd through a plugin, the documentation only states that you can define tables with SQLAlchemy syntax in your plugin (ref). While this is certainly true, it does not cover the most important part (imho): deployment. Alembic migrations are executed prior to starting CTFd, which is great, but there's no easy way to ensure that plugin tables are created without forking CTFd and adding new database revisions to migrations/versions/.

My proposition is to formally link all the plugins with the main Alembic configuration. Specifically, you can use the migrations.config decorator provided by Flask-Migrate to modify the Alembic configuration prior to loading, and add a directory under each plugin to the Alembic version_locations list. In order to make this work, you would need to:

• Add a migrations.config method to iterate over plugins, and add {plugin_path}/migrations to version_locations.
• Change calls to flask_migrate.upgrade() to flask_migrate.upgrade(revision="heads"). This ensures that upgrades happen toward all available heads.
• Change calls to stamp() to stamp(revision="heads") for similar reasons.

After doing this, CTFd will continue running normally. The difference is that plugin developers can now create new branch versions and store the Alembic scripts under their own plugin directory. CTFd will then be able to apply custom plugin migrations at startup time without any hacky code or external DB orchestration.

I would be happy to put this together into a PR, but I wanted to gauge interest before doing the work. Technically, I already have this working, but only as a proof of concept and it needs some clean up. If the CTFd maintainers are interested in this, I will gladly clean it up, and submit a formal PR. Conversely, if you have any issues or suggestions, I'm happy to reconsider aspects of the plan and work on the best solution.

Thanks for reading/considering! :)

I just realized I forgot to include a link to the relevant Alembic documentation on using a setup like this. It can be found here.

Sounds interesting but CTFd plugins can already have their own migrations. See https://github.com/CTFd/CTFd/blob/8873417e0d6172796b48a54db8bac54ace4a8c15/CTFd/plugins/migrations.py#L26.

This sounds like it might improve the reliability of deploying plugin migrations but I would need to see what the code looks like.

Ah, I didn't know about that! Is the intention for plugins to run upgrade(plugin_name="myself") in their load() method?