Mentat

Based on the videocall discussion, the motivation behind this issue is to get rid of Alembic. Alembic is a database schema migration tool created by the same author as SQLAlchemy [1], mainly to support the database migrations for SQLAlchemy-based projects. Of course, Alembic can be used without SQLAlchemy. As it happens, both mentat_main, which is SQLAlchemy managed, and mentat_events, which is managed using SQL DDL descriptions, use Alembic for migrations, the scripts are source:/lib/hawat/migrations/versions@devel and source:/migrations-events/versions@devel respectively. Alembic does have limited support for generating migrations scripts from SQLAlchemy schema changes, those must always be reviewed, fixed and tested manually. As for the mentat_events database, the migration scripts there surpass the abilities of automatic script generation greatly, so a schema definition using SQLAlchemy would not have helped much.
To sum it up, discarding Alembic does not seem realistic (without replacing it with something roughly identical) and Alembic does not depend on SQLAlchemy and can be used separately.

Based on discussion in issue #4230, which this issue stemmed from, the motivation for this issue was to unify "database creation, management and cleanup tasks". It would be fairly trivial to replace the direct SQL DDL description in source:/lib/mentat/services/eventstorage.py@devel, namely the database_create() and index_create() methods, using SQLAlchemy definitions and DDL calls ([2] is a simple tutorial containing almost all the required information). Then, for the cleanup and management tasks, a significant portion of EventStorage would have to be reimplemented. Of course the actual data access (and even some management tasks, such as the enum tables manipulation) is too complex to implement using pure SQLAlchemy, but that is outside of the scope of this issue anyways.

[1] https://pypi.org/project/alembic/
[2] https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_core_creating_table.htm

CTEs (common table expressions) are in fact possible with SQLAlchemy [1], so enum table manipulation queries can be translated. However the syntax is quite lengthy and subjectively non-obvious, as illustrated in the example code in the manual.
Also, it is possible to define GIN indices in the model. [2]

[1] https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.cte
[2] https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#postgresql-indexes