- 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
## Summary
Fixes the missing `dashboard_public` check security vulnerability
(GHSA-9mjc-6fp2-hm9v).
### Root cause
The `user_authenticated_or_public_dashboard` dependency in `auth.py`
only verified that the tournament existed in the database, but never
checked whether `dashboard_public = True`. This allowed unauthenticated
users to access sensitive tournament data on the following endpoints
even when the tournament was not publicly shared:
- `GET /tournaments/{tournament_id}` (partially protected by an explicit
post-dependency check)
- `GET /tournaments/{tournament_id}/courts`
- `GET /tournaments/{tournament_id}/teams`
- `GET /tournaments/{tournament_id}/rankings`
- `GET /tournaments/{tournament_id}/stages`
### Changes
- **`backend/bracket/routes/auth.py`**: Added `not
tournaments_fetched[0].dashboard_public` to the check in
`user_authenticated_or_public_dashboard`. Unauthenticated requests to a
tournament with `dashboard_public=False` now receive a 401 response.
- **`backend/bracket/routes/tournaments.py`**: Removed the now-redundant
explicit `dashboard_public` check in `get_tournament` (the dependency
handles it now).
- **`backend/tests/integration_tests/api/tournaments_test.py`**: Added
`test_non_public_tournament_endpoints_blocked_for_unauthenticated_users`
to assert that all affected endpoints return 401 for unauthenticated
requests when `dashboard_public=False`.
Note: `user_authenticated_or_public_dashboard_by_endpoint_name` (used
for the `GET /tournaments?endpoint_name=` route) was not affected — it
delegates to `sql_get_tournament_by_endpoint_name` which already
includes `AND dashboard_public IS TRUE` in its SQL query.
Co-authored-by: copilot-swe-agent[bot] <198982749+Copilot@users.noreply.github.com>
Co-authored-by: evroon <11857441+evroon@users.noreply.github.com>
