Flask SQLAlchemy Patterns

What this prompt does

This prompt implements advanced SQLAlchemy patterns for a [app_type] Flask application on [database_engine] with roughly [model_count] models. It produces reusable base mixins — a TimestampMixin with auto-updating created_at/updated_at, a SoftDeleteMixin with a deleted_at column and a query filter override, and a UUIDMixin using [uuid_strategy] — so you stop rewriting the same columns on every model.

It then wires [relationship_type] relationships across [model_examples] with cascade rules, back_populates, and lazy-loading strategies (selectin for collections, joined for single objects), adds hybrid properties for [hybrid_examples] that work in both Python and SQL, and registers event listeners for [event_use_cases] such as before_insert slug generation, after_update cache invalidation, and after_delete cleanup. A custom query class exposes reusable filters including active() for soft-delete scoping, full-text search() over [search_columns], and a paginate_json() returning [pagination_format]. Connection pooling is sized with pool_size=[pool_size] and max_overflow=[max_overflow] plus pool_timeout and pool_recycle for [deployment_context], and Alembic patterns separate schema from data migrations while seeding [seed_requirements]. Together these patterns strip a large amount of repetitive boilerplate out of every model you write.

When to use it

• Your Flask app has real domain complexity and you're tired of duplicating timestamp/UUID columns
• You need soft deletes that are transparently filtered from normal queries
• You want computed values like is_overdue usable in both Python and SQL filters
• You need event-driven side effects (slugging, audit logging, file cleanup) on model lifecycle
• You're seeing dropped connections in production and need correct pool sizing per worker
• You want Alembic migrations that cleanly separate schema changes from data migrations
• You want a reusable query class so soft-delete filtering and pagination aren't reinvented per endpoint

Example output

The AI returns mixin classes, relationship definitions with back_populates and lazy strategies, @hybrid_property plus @hybrid_expression pairs, event-listener functions decorated for insert/update/delete, a custom Query subclass exposing active() and search(), a connection-pool configuration block, and Alembic migration examples that split schema from data changes. Expect code grouped by the seven numbered concerns rather than one large module.

Pro tips

• Size [pool_size] and [max_overflow] against [deployment_context] — each Gunicorn worker needs its own pool, so multiply, don't share
• Pick [uuid_strategy] deliberately; sortable UUIDs (like uuid7) index far better than random v4 for time-ordered data
• Define [hybrid_examples] so each has both a Python and a SQL expression, or you'll lose the ability to filter on them in queries
• Keep event listeners in [event_use_cases] lightweight; pushing heavy work into after_update can stall the request
• Make [search_columns] explicit so the full-text search builds the right tsvector index
• Set pool_recycle below your database's idle timeout so stale connections are recycled before the server drops them
• If pooling causes timeouts, re-prompt with your exact worker count to recompute [pool_size]