RWWS Backend

Backend API for a Russian whitewater-rafting routes content site. Built with FastAPI, SQLAlchemy 2.0, and PostGIS. Consumed by the Astro (SSR) frontend and Leaflet maps.

Requirements

• Python 3.x (for local development; the Docker path needs just Docker)
• Docker with Compose v2 (for the PostGIS database and/or the whole stack)

Setup

# 1. Start PostGIS
docker compose up -d db

# 2. Create and activate a virtualenv, install dependencies
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

# 3. (Optional) Set env vars to override values from `.env`
export DATABASE_URL=postgresql://rwws:mysecretpassword@localhost:5432/rwws

# 4. Apply migrations (creates schema "rwws" + tables + GIST indexes)
alembic upgrade head

`alembic` migrates the database from `DATABASE_URL` (falling back to the URL in
`alembic.ini`); the migration connection pins `search_path=rwws,public` and keeps
`alembic_version` in `public` (see ARCHITECTURE.md). The chain applies cleanly from a
fresh DB (`docker compose down -v && docker compose up -d && alembic upgrade head`) and
is covered by `tests/test_migrations.py`.

# 5. Run the API
uvicorn app.main:app --reload

Interactive API docs: http://localhost:8000/docs

Docker

The API ships as a Docker image (rwws-backend, see Dockerfile); the same docker-compose.yml runs PostGIS and the API. Build the image with one command:

docker compose build          # equivalently: docker build -t rwws-backend .

Then start the whole stack (db + api):

docker compose up -d          # builds the image automatically on first run

On startup the container waits for PostgreSQL, applies alembic upgrade head, and starts uvicorn on port 8000 (docs at http://localhost:8000/docs). Both services carry Docker healthchecks; docker compose ps shows healthy once the API answers GET / with 200.

Quick smoke test:

docker compose up -d
curl -fsS http://localhost:8000/            # {"status":"ok",...}
curl -fsS http://localhost:8000/api/v1/rivers
docker compose exec api alembic current     # applied migration head

To run the stack on other ports — e.g. while the dev database/API are already up — use a separate compose project (isolated network and volumes):

POSTGRES_HOST_PORT=55432 API_HOST_PORT=18000 POSTGRES_CONTAINER_NAME=rwws-smoke-postgis \
docker compose -p rwws-smoke up -d

Docker environment variables

The PostgreSQL URL inside the container always comes from the environment and is not taken from the host .env DATABASE_URL (the container reaches the database via the compose network, so a localhost URL would be wrong there):

Variable	Default	Description
API_DATABASE_URL	postgresql://rwws:mysecretpassword@db:5432/rwws	Full DATABASE_URL handed to the container (wins over everything else)
POSTGRES_USER / POSTGRES_PASSWORD / POSTGRES_DB	rwws / mysecretpassword / rwws	Credentials for the db service and the default API_DATABASE_URL
POSTGRES_HOST_PORT	5432	Host port for PostGIS (bound to 127.0.0.1 only)
API_HOST_PORT	8000	Host port for the API
API_IMAGE	rwws-backend	Name of the built image
POSTGRES_CONTAINER_NAME	rwws-postgis	Container name of the db service
RUN_MIGRATIONS	1	0 disables the automatic alembic upgrade head at container start

POSTGRES_USER/POSTGRES_PASSWORD/POSTGRES_DB are interpolated into the default API_DATABASE_URL, so the default URL only works with URL-safe credential characters. A password containing reserved URI characters (@, :, /, ?, #, %) must not be percent-encoded into POSTGRES_PASSWORD (the db service would then store the encoded literal) — instead keep POSTGRES_PASSWORD raw and set the complete API_DATABASE_URL yourself with the password percent-encoded in the URL.

The remaining app settings (ALLOWED_ORIGINS, JWT_SECRET_KEY, OAuth credentials, ...) are passed from the host .env into the container via env_file (optional — the stack starts without one). To apply migrations explicitly instead of at container start:

RUN_MIGRATIONS=0 docker compose up -d api
RUN_MIGRATIONS=0 docker compose run --rm api alembic upgrade head

Development Tooling

Linter and formatter: ruff. Type checks: mypy. Both are configured in pyproject.toml and listed in requirements-dev.txt (installs requirements.txt too):

pip install -r requirements-dev.txt

ruff check .          # lint
ruff format .         # format (config in pyproject.toml)
mypy app              # type check
python -m pytest      # run the test suite

The OpenAPI specification is exported deterministically from app.main.app.openapi() into the committed openapi.json at the repository root, in human-readable, diff-friendly form: two-space indentation, sorted object keys, literal non-ASCII UTF-8 (no \uXXXX/apostrophe escaping — JSON-required escapes, e.g. control characters, may still occur), and a trailing newline:

python -m utils.openapi                # regenerate openapi.json at the repository root
python -m utils.openapi --check        # verify openapi.json is up to date (exit 0 = ok, 1 = stale/missing)
python -m utils.openapi --output PATH  # write the spec to a custom path

The spec also documents the cache headers (Cache-Control/ETag/Last-Modified), the 304 Not Modified response, and the conditional-request parameters (If-None-Match/If-Modified-Since) of the CachingMiddleware on public GET /api/v1/* endpoints (see the Caching section in ARCHITECTURE.md).

Regenerate and commit openapi.json whenever the API schema changes. The committed file is the single source of truth for the API contract: the Astro frontend (rwws-frontend) generates its TypeScript types from it (openapi-typescript → src/lib/generated/openapi.ts) with npm run api:generate (refresh) and npm run api:check (verification). After a backend schema change: regenerate the spec, commit it, then regenerate the frontend types.

Tests

Test suite lives in tests/ and covers the CRUD layer (rivers, sections, geometry round-trip), HTTP API tests (tests/test_api_rivers.py, tests/test_api_sections.py via httpx2.AsyncClient over ASGI with the get_db dependency overridden), auth, and shared cache headers (tests/test_caching.py). The markdown sanitizer is covered at four levels — unit/policy (tests/test_markdown_sanitizer.py), hardening/boundedness (tests/test_markdown_sanitize_hardening.py), service boundary (tests/test_services_markdown_sanitize.py) and HTTP integration (tests/test_api_markdown_sanitize.py, tests/test_api_markdown_limits.py) — and the global request-body cap by tests/test_body_limit.py (pure-ASGI 413, no PostGIS needed). The backfill utility is covered by tests/test_utils_backfill_markdown.py: unit tests against SQLite fake models (monkeypatched TABLES/sync_engine) plus integration tests against the testcontainers PostGIS verifying the SHARE lock blocks concurrent writers and the post-audit leaves every row sanitized. DB and API tests are async (@pytest.mark.asyncio). Tests run in an isolated PostGIS container started by testcontainers (see tests/conftest.py); the dev rwws database is never touched. Docker is required, but the running docker compose PostGIS container is not:

python -m pytest

The test image is imresamu/postgis:17-3.6-bookworm (override with POSTGIS_TEST_IMAGE).

Configuration

Variable	Default	Description
DATABASE_URL	postgresql://rwws:mysecretpassword@localhost:5432/rwws	SQLAlchemy connection URL
ALLOWED_ORIGINS	http://localhost,http://127.0.0.1	Comma-separated CORS allow-list (used with allow_credentials=True, so * is not allowed)
OVERPASS_API_URL	https://maps.mail.ru/osm/tools/overpass/api/interpreter	OverPass API interpreter endpoint
OVERPASS_TIMEOUT	60	HTTP timeout (seconds) for OverPass requests
OVERPASS_WATERWAY_CLASSES	river,stream,canal	Comma-separated exact waterway classes included in OverPass queries; excludes ditches/drains and polygon classes such as riverbank/dock
OAUTH_TIMEOUT	30	HTTP timeout (seconds) for OAuth provider calls (Yandex/VK ID)
WATERWAY_MAX_HOPS	6	Max connectivity-expansion steps when collecting river segments
LOG_LEVEL	INFO	Root logger level (DEBUG, INFO, WARNING, ...)
LOG_FORMAT	json	Log record format: json (structured, default) or text (human-readable for dev)
PROBLEM_BASE_URL	https://rwws.example/problems	Base URL for RFC 9457 problem type URIs
PUBLIC_CACHE_MAX_AGE	300	Seconds public reads are cached (Cache-Control: public, max-age); also drives ETag/Last-Modified/304
MAX_REQUEST_BODY_BYTES	1048576	Global cap on HTTP request bodies (bytes), enforced by middleware before full buffering/JSON decoding — oversized requests get an RFC 9457 413 (see Content Policy below)

.env is loaded automatically (via python-dotenv in app/core/config.py); variables already set in the environment take precedence over .env values.

API Overview

Method	Path	Purpose
GET	/	Health check
POST	/api/auth/social	Social login (Yandex/VK ID): {provider, code} → {token, role}. Rate limited: 10 req/min per IP
POST	/api/auth/login	Password login: {username, password} → {token, role} (401 on bad credentials). Rate limited: 10 req/min per IP
POST	/api/v1/rivers	Create a river (409 if the slug is already in use)
GET	/api/v1/rivers	List rivers (skip, limit)
GET	/api/v1/rivers/{slug}	Get a river by slug (incl. sections brief list: slug, name, grade)
PUT	/api/v1/rivers/{slug}	Update a river by slug (partial; sent fields only; 409 if the new slug is already in use)
DELETE	/api/v1/rivers/{slug}	Delete a river (and its sections) by slug
POST	/api/v1/rivers/{slug}/sections	Create a section for a river (409 if its slug is already used in that river; optional waterway GeoJSON LineString; start/end points are managed via the features API)
GET	/api/v1/rivers/{river_slug}/sections/{section_slug}	Get a section by slug (incl. embedded river object, computed length_km, and a brief features list with each feature's location GeoJSON point — so the detail page needs no separate features request)
PUT	/api/v1/rivers/{river_slug}/sections/{section_slug}	Update a section by slug (partial; 409 if the new slug is already used in that river; incl. waterway GeoJSON LineString)
DELETE	/api/v1/rivers/{river_slug}/sections/{section_slug}	Delete a section by slug
GET	/api/v1/rivers/{river_slug}/sections/{section_slug}/features	List a section's on-river features (rapids, hazards, portages, access points); location as GeoJSON Point
POST	/api/v1/rivers/{river_slug}/sections/{section_slug}/features	Create a feature. Body is a discriminated union on type: one schema per type (RapidFeatureCreate, HazardFeatureCreate, ...) documenting that type's mandatory fields (access_kind, hazard_kind, rapid_grade; name optional for all types). Auth: editor/admin
GET	/api/v1/rivers/{river_slug}/sections/{section_slug}/features/{feature_id}	Get a single feature
PUT	/api/v1/rivers/{river_slug}/sections/{section_slug}/features/{feature_id}	Update a feature (partial). type is required (discriminator); send the current type. Auth: editor/admin
DELETE	/api/v1/rivers/{river_slug}/sections/{section_slug}/features/{feature_id}	Delete a feature. Auth: admin only
GET	/api/v1/gis/river/{slug}/sections/features	GeoJSON FeatureCollection (waterway LineString path when present + rapids); path properties include length_km and nullable descent_m_per_km; rapids with rapid_length_m also get a LineString feature id="{feature_id}-stretch" — the waterway stretch covered by the rapid (skipped when the section has no waterway)
GET	/api/v1/gis/rivers/{river_slug}/sections/{section_slug}/features	GeoJSON FeatureCollection for a single section (waterway path + river features); path properties include length_km and nullable descent_m_per_km; rapids with rapid_length_m also get a LineString feature id="{feature_id}-stretch" — the waterway stretch covered by the rapid (skipped when the section has no waterway); 404 if the river is missing or the section slug is not in that river
POST	/api/v1/gis/waterway	Build a GeoJSON LineString between two points (put_in, take_out). radius (1–500 m, default 100) = OverPass search radius. Real waterway geometry from OverPass; returns 422 if no waterway is found, 500 if the OverPass API fails. Auth: editor/admin

Rate Limiting

Public auth endpoints are rate limited per client IP to slow down password/code brute force:

Endpoint	Limit (per IP)
POST /api/auth/login	10 requests / 60 s
POST /api/auth/social	10 requests / 60 s

• Sliding-window limiter, in-process (app/core/ratelimit.py, stdlib only): no external dependencies and no shared store. With multiple uvicorn workers each worker counts independently (effective limit = 10 × workers).
• Each endpoint has its own window; requests rejected with 429 do not extend it.
• Client IP is the first address of X-Forwarded-For (deployment behind a reverse proxy) with a fallback to the connection address. The proxy must strip or overwrite this header: a directly exposed API lets clients rotate the header to mint fresh windows (limit bypass) or fill another IP's bucket (bucket poisoning).
• Exceeding the limit returns an RFC 9457 problem document with status 429, type {PROBLEM_BASE_URL}/rate-limit, and a Retry-After header (seconds).
• Limits are code constants (AUTH_RATE_LIMIT_* in app/api/routes/auth.py), not env-configurable.

Content Policy (Markdown Descriptions)

description_markdown on rivers, sections, and river features is sanitized once per write, before persistence:

• Length limit: at most 50,000 characters — anything longer is rejected with 422 before parsing/persistence.
• Where: sanitization runs once per write at the API boundary, in a worker thread (anyio.to_thread, off the event loop), before any CRUD/DB call. Pydantic only performs cheap max_length checks.
• Allowed: safe CommonMark — paragraphs, headings, emphasis/strong, lists, blockquotes, inline/block code, links, thematic breaks, line breaks. Stored output is canonicalized.
• Stripped / neutralized: raw HTML (block and inline) and images are removed (image alt text is kept as plain text); links whose destination is outside http(s)://, protocol-relative //host, or a relative path/query/fragment are reduced to their label text. Inline/block code content is never touched.
• Bounded output: sanitization is idempotent and never raises — pathological depth, non-convergence, or output expansion beyond the limit safely becomes an empty description.
• null semantics: river/section update allows omitting description_markdown (stored value untouched) but rejects an explicit null with 422; feature descriptions are nullable (null clears them).
• Legacy data: rows stored before this feature are not sanitized in the DB and may still contain unsanitized content. python -m utils.backfill_markdown can audit and backfill them against the configured DATABASE_URL database. That database was backfilled on 2026-09-16 (171 rows scanned, 31 changed: rivers 13/5, sections 34/14, river_features 124/12) and independently verified at zero drift. Other or newly created databases need their own run.

Sanitization reduces the risk of unsafe stored content, but it is not a substitute for safe rendering: the frontend must still render descriptions safely (escape text / sanitize at render time). The sanitizer runs on the mistune CommonMark AST parser; Marko is a dev-only test renderer.

Seeding Data

With the API running, populate the DB with sample rivers and sections (data lives in utils/seed_data/, one YAML file per river):

python -m utils.seed            # create rivers and sections; update entities that already exist
python -m utils.seed --dry-run  # preview without creating anything

RWWS_API_URL overrides the API base URL (default http://localhost:8000).

Writing requires an editor/admin token. If none is supplied, utils/seed.py mints one itself: it connects to the same database as the API, ensures a seed-bot editor user exists, and issues a JWT with the shared JWT_SECRET_KEY. To use a specific token instead, pass --token JWT or set RWWS_TOKEN (overrides auto-minting).

Dumping Data

The inverse of seeding: fetch every river/section from the running API and write them back to seed_data/*.yaml (one file per river, same format utils/seed.py reads). River/section metadata comes from the CRUD endpoints; the waterway line comes from the river-level GIS features endpoint (GET /api/v1/gis/river/{slug}/sections/features) in a single request per river. Dump paths are matched to sections via the numeric section id (the slug is not used — section slugs are unique only within a river). Start/end points are not written (managed via the features API). Sections without a stored waterway are written without a waterway field. A river is skipped if its sections request or its features request fails — there is no partial export.

python -m utils.dump                 # overwrite seed_data/*.yaml with current DB contents
python -m utils.dump --dry-run       # preview rivers/section counts without writing
python -m utils.dump --out-dir DIR   # write to a different directory

Backfilling Markdown Descriptions

Rows written before the sanitizer existed may still contain raw HTML, images, or dangerous links. python -m utils.backfill_markdown audits and (optionally) backfills description_markdown across rivers, sections, and river_features by running every stored value through sanitize_markdown and reporting per-table counts (scanned / changed). Feature NULL descriptions stay NULL.

python -m utils.backfill_markdown                       # read-only audit: no writes, no locks
python -m utils.backfill_markdown --apply               # backfill in one transaction, post-audit before commit
python -m utils.backfill_markdown --preview-chars 100   # opt-in content preview (default: off)
python -m utils.backfill_markdown --max-previews 50     # cap preview count (default 20)
python -m utils.backfill_markdown --batch-size 200      # rows per read/update batch (default 200)

Behavior:

• Default is a read-only audit — nothing is written and no table locks are taken.
• --apply runs a single transactional pass: it acquires PostgreSQL LOCK TABLE rwws.<table> IN SHARE MODE on all three tables — the weakest mode that blocks concurrent INSERT/UPDATE/DELETE without blocking ordinary reads — applies the sanitized values in batches (keyset pagination), flushes, and re-audits in the same transaction while the locks are still held. It commits only after a clean post-audit; any drift or error rolls everything back and exits non-zero. Concurrent writes block during apply; API reads stay available. A run is safe to repeat (it simply finishes the rest).
• Privacy-safe previews by default — content previews are disabled (--preview-chars 0): only counters and lengths are printed. Enable them explicitly with --preview-chars N (N > 0); output stays bounded by --max-previews (default 20) and per-entry truncation to N characters.
• Uses the same DB as the API (DATABASE_URL) via the sync engine; no migration required.

Operational status: the configured target database was backfilled with python -m utils.backfill_markdown --apply on 2026-09-16 (171 rows scanned, 31 changed) and independently verified: a post-commit dry-run re-scanned the same 171 rows and found 0 changed rows (zero drift). For other or newly created databases, repeat the dry-run audit, --apply, and a final zero-drift audit as described above.

User Management

Manage users directly in the same DB the API uses (passwords are hashed with the same PBKDF2 functions as the API):

python -m utils.users create <name> [--role editor] [--password P]  # create a user (prompts for password on a terminal)
python -m utils.users set-password <name> [--password P]            # set/change a user's password
python -m utils.users set-role <name> <user|editor|admin>           # change a user's role
python -m utils.users list                                          # list users (id, username, role)

Roles: user (read-only), editor (create/edit), admin (also delete). A user created without a password can only log in via social OAuth.

Errors

Every error response is an RFC 9457 problem document served as application/problem+json:

{"type": "about:blank", "title": "Not Found", "status": 404, "detail": "River not found", "instance": "/api/v1/rivers/nonexistent-river"}

• type — URI identifying the problem class; about:blank for plain 4xx/5xx, otherwise {PROBLEM_BASE_URL}/validation-error | /auth-error | /rate-limit | /internal-error.
• title — short summary; the HTTP status reason phrase by default.
• status — the HTTP status code.
• detail — human-readable explanation (always English).
• instance — the request path that produced the error.
• errors — extension present only on 422: [{"field": "put_in.coordinates", "message": "Field required"}, ...].

Examples: HTTPException (400/401/403/404/...) uses the status phrase as title; validation (422) adds structured errors plus a flattened readable detail; unhandled exceptions (500) return title "Internal Server Error", detail "Internal server error" (traceback logged server-side).

Project Layout

app/            FastAPI application (core/ infra incl. body-limit middleware, models, schemas, crud package, services incl. markdown sanitizer, api/routes)
migrations/     Alembic migrations (schema "rwws")
utils/          utility scripts (seed.py, dump.py, users.py, openapi.py, backfill_markdown.py + seed_data/*.yaml; seed populates the DB via the CRUD API, dump exports it back to YAML, users manages accounts directly in the DB, openapi exports the committed openapi.json, backfill_markdown audits/backfills description_markdown through the sanitizer)
openapi.json    committed OpenAPI spec — deterministic export of app.openapi()
Dockerfile      multi-stage image build (builder wheels + slim runtime, non-root, healthcheck, migrations entrypoint)
docker/         docker assets (entrypoint.sh: wait for db → alembic upgrade head → exec CMD)
docker-compose.yml  PostGIS database service + API service (env-configurable ports/URL)
.dockerignore   build-context filter (no .env, caches, tests, docs in the image)

See ARCHITECTURE.md for details and TODO.md for known bugs and planned work.