Copy-paste Claude Code prompts for SQLAlchemy 2.0 async — declarative models with Mapped types, Alembic migrations, and selectinload N+1 fixes.
SQLAlchemy 2.0 dropped the implicit Query API in favour of select(). Claude handles the new syntax well but tends to drift toward joinedload — fine for single records, disastrous for collections. Lock the loading-strategy rule and the queries Claude produces stay fast.
Add a model 'Invoice' in app/models/invoice.py.
Fields:
- id: Mapped[UUID], primary_key=True, default=uuid4
- account_id: Mapped[UUID], ForeignKey('account.id', ondelete='CASCADE'), indexed
- amount_cents: Mapped[int]
- currency: Mapped[str], String(3)
- status: Mapped[InvoiceStatus] enum, default DRAFT, indexed
- issued_at: Mapped[Optional[datetime]]
- created_at, updated_at: from TimeStampedMixin (already exists — read it first)
Relationships:
- account: Mapped['Account'] = relationship(back_populates='invoices', lazy='raise')
- lines: Mapped[list['InvoiceLine']] = relationship(
back_populates='invoice', cascade='all, delete-orphan', lazy='raise'
)
Constraints:
- lazy='raise' on all relationships in this project — never lazy='select'.
Forces explicit loading at the query site.
- Composite index on (account_id, status).
- Add 'invoices' back_populates on Account.
- Generate an Alembic revision file via alembic revision --autogenerate
--message 'add invoice'. Output the file content. Do NOT run upgrade.Add async def get_account_dashboard(session, account_id) at
app/services/dashboard.py that returns:
- The Account (with id, name, plan)
- Its last 10 invoices (newest first) — id, amount_cents, status, issued_at
- For each invoice: the sum of its line totals (denormalized into the response)
Constraints:
- One round trip if possible. If two, justify.
- Use selectinload(Account.invoices) and then within that, selectinload(
Invoice.lines) — collections are selectinload, not joinedload.
- Use func.sum on InvoiceLine.total_cents in a subquery aliased onto Invoice
so the result is one row per invoice with the sum included. (Pattern:
with_only_columns + group_by, then join into the main select.)
- Return a Pydantic v2 model AccountDashboard from app/schemas/dashboard.py
— define it if missing.
- Add a test in tests/services/test_dashboard.py: fixture an account with 12
invoices, assert only 10 are returned, ordered correctly, with the sums.Rename the column 'amt' on table 'invoice' to 'amount_cents'. Production has
~8M rows.
Produce three Alembic migrations:
1. Add column amount_cents (nullable) + Python op.execute backfill in batches of
5000 + add NOT NULL. Reversible (downgrade drops the new column).
2. Update the SQLAlchemy model to map amount_cents (separate migration to make
the deploy step a no-op DB change, but a Python change). Actually — model
changes aren't migrations. Put step 2 as a code commit, document it in the
header comment of migration 3.
3. Drop column 'amt'. Downgrade re-adds the column — but downgrade does NOT
restore data, so warn in the header that this migration is destructive.
Each migration has a downgrade() function. Header docstring explains the deploy
sequence.## SQLAlchemy 2.0 policy
- Async only. AsyncSession via app/db/session.py.
- All models inherit from Base (declarative) + TimeStampedMixin.
- All relationships lazy='raise'. Eager load at query site with
selectinload (collections) / joinedload (single).
- Alembic migrations always reversible. Destructive migrations flagged
in the docstring.
- Never query in models. Repos/services own queries.Related: FastAPI + Claude.