Alembic migration test fixtures using raw SQL (sa.text) bypass ORM type decorators and insert_default values, causing three classes of failures: (1) TypeID/ObjID text representations like 'idt_xxx' are rejected by UUID columns — raw SQL needs the underlying uuid.UUID value, not the ORM wrapper's string form; (2) columns with ORM-level insert_default=dict (e.g. kind_metadata, version_ctx JSONB columns) still have NOT NULL constraints at the DB level, so raw INSERTs must explicitly include '{}'::jsonb for each; (3) hand-written UUID hex strings with non-hex characters (e.g. 'ra' instead of valid hex 'a0') pass casual review but fail at uuid.UUID() parse time, crashing test collection before any test runs.
When writing Alembic migration test fixtures that seed data via sa.text() raw SQL: (1) Pass raw uuid.UUID objects for ObjIDColumn/TypeID columns, not the prefixed string form — the TypeDecorator that strips the prefix only runs through the ORM path. (2) Explicitly include ALL NOT NULL columns in INSERT statements, even those with ORM insert_default — raw SQL doesn't trigger SQLAlchemy defaults. (3) Validate hand-written UUID literals parse correctly: uuid.UUID('...') in Python before committing. A quick smoke test that imports the fixture module catches class (1) and (3) at collection time.