PostgreSQL Best Practices (psycopg, no ORM)

Core rules for every piece of database code in this project:

• No ORM — use psycopg directly.
• Inline SQL — trivial queries of 4 lines or fewer may be written inline in Python. Anything with JOINs, subqueries, CTEs, aggregations, or multiple conditions must live in its own .sql file.
• Named parameters — always %(name)s style, never positional %s.
• SQL formatting — all .sql files are formatted with shandy-sqlfmt.
• Dynamic SQL — check pyproject.toml for the Python version; use psycopg t-string templates on 3.14+, otherwise psycopg.sql.
• Result mapping — every query result maps to a Pydantic model defined in a {topic}_models.py file.

Project layout

app/
├── db/
│   ├── connection.py          # pool factory + get_pg_connection()