Review enum-related behavior

[tcompa]

As of #934 (comment), we may be fixing the CI with sqlmodel >0.0.8, but we still need to make sure that the following test works.

• Add a migration that changes job.status type into an actual enum, as it would be autogenerated with sqlmodel 0.0.9
• Run fractal-server with postgres (and gunicorn, if possible)
• Look for unexpected behavior, especially by running jobs (because it's their status that could be broken)
• In case of errors, try disabling asyncpg cache (https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#prepared-statement-cache)

[tcompa]

Note: due to the bug in sqlmodel<=0.0.8, that was representing enums as autostrings, we have no enums in our migrations.
Therefore this specific error should be impossible until we update sqlmodel to >=0.0.9.

[tcompa]

Ref tiangolo/sqlmodel#164

[mfranzon]

As long as SQLITE does not support ENUM type, do we want to make an if branch to store enum in postgres and string in sqlite?

The other possibility is to remove Enum from JobStatusType model, and make it a string.

[tcompa]

Where would you place the if? In the migration scripts?

[mfranzon]

Where would you place the if? In the migration scripts?

Yes, I am thinking the same but honestly I don't like too much this approach

[tcompa]

We re-discussed this with @mfranzon, and had a look at known issues like:

• Autogenerate doesn't correctly handle postgresql enums sqlalchemy/alembic#278

• We think it's not worth it to have actual enums in the db, for the moment. We'll keep them for all serializer (that is, Pydantic models), but not for the actual tables (that is, SQLModel models).
• Let's add a Pydantic validator to the SQLModel objects, that checks e.g. that ApplyWorkflow.status complies with JobStatusType.

In this way we don't risk breaking changes with due to the recent sqlmodel update.

We should also keep it in mind when adding new columns in the future.