- Rewrite database.py with asyncpg pool-based DatabasePool class
- Support named params (:param) → positional ($N) conversion
- Transaction context manager using contextvars for connection reuse
- Replace all SQLAlchemy query building with raw SQL in routes and sql modules
- Update utils/db.py to use raw SQL strings instead of SQLAlchemy Select
- Update utils/db_init.py to use local engine creation for DDL only
- Update all test files to use string table names and raw SQL
- Remove databases and sqlalchemy-stubs from dependencies
- Keep sqlalchemy only for schema.py definitions and alembic migrations
Python's `and` operator is unsafe with SQLAlchemy clause elements: in
SQLAlchemy 2.0 it raises `TypeError` (routes return 500); in older
versions it short-circuits to the right-hand operand, silently dropping
the `id = X` filter and enabling cross-tournament entity access.
## Changes
**`routes/util.py`**
- `team_dependency`: replace `and` with `&`
- `round_dependency` / `match_dependency`: `rounds` and `matches` have
no direct `tournament_id`; replace the broken `and` chain with proper
JOINs through `stage_items → stages` to enforce tournament scoping
**`routes/courts.py`**
- `create_court` post-insert fetch: replace `and` with `&`
**`tests/.../teams_test.py`**
- Add `test_cross_tournament_team_access_denied`: asserts that a `PUT`
on tournament A's URL using a `team_id` belonging to tournament B
returns 404
```python
# Before (BROKEN — evaluates to just the right-hand side)
teams.select().where(teams.c.id == team_id and teams.c.tournament_id == tournament_id)
# After (CORRECT)
teams.select().where((teams.c.id == team_id) & (teams.c.tournament_id == tournament_id))
# round_dependency — no tournament_id on rounds table, use JOIN
rounds.select()
.join(stage_items, rounds.c.stage_item_id == stage_items.c.id)
.join(stages, stage_items.c.stage_id == stages.c.id)
.where((rounds.c.id == round_id) & (stages.c.tournament_id == tournament_id))
```
---------
Co-authored-by: copilot-swe-agent[bot] <198982749+Copilot@users.noreply.github.com>
