﻿# Glicko DB

Documentation: [Español](README.md) | English | [Português](README.pt.md)

Glicko DB is a Flask and SQLite application for managing a Go community's players, ratings, matches, and tournaments. It provides public rankings and statistics, along with protected administrative screens for imports, rating configuration, backups, and tournament operations.

## Table of contents

- [Features](#features)
- [User help](#user-help)
- [Requirements](#requirements)
- [Local run](#local-run)
- [Configuration](#configuration)
- [Project roadmap](#project-roadmap)
- [Development](#development)
  - [Code organization](#code-organization)
  - [Linux hosting installation](#linux-hosting-installation)
  - [Scheduled backups and restore drills](#scheduled-backups-and-restore-drills)
  - [Authenticated API rollout](#authenticated-api-rollout)
- [License and attribution](#license-and-attribution)

## Features

- Public rankings, player search, profiles, match history, and rating charts
- Configurable Glicko-2 ratings and an interface in Spanish, English, and Portuguese
- Excel, CSV, and OpenGotha imports; OpenGotha tournament uploads are reviewed and approved before changes are applied
- Swiss, category Swiss, accelerated Swiss, and McMahon tournament systems
- Member registration, moderated requests to link unclaimed player profiles, and result submissions for administrative approval
- Filterable reports with CSV/PDF export, news, and a public SGF library
- Backups, protected restores, and SQLite migrations
- Country, regional, age, and female-player rankings; the default web/API ranking region is configurable (initially Latin America)
- Three access levels: Global (Administrators), Regional, and Country; Organizers have Tournament Operations (including players), and Managers also have Data Management
- Handicap games, BYEs, and absences
- Drafts stay hidden and canceled tournaments are omitted from public lists; canceled individual details remain accessible by direct link. Completed unrated tournaments show their games without affecting ratings
- PairGo tournaments with two players per team and no gender restrictions; they are always unrated and do not change individual statistics or head-to-head records
- Keyboard-accessible navigation, reduced-motion support, and rating filters with native fallbacks

## User help

Deployment procedures and observations are summarized in the [operations notes](docs/operations.md); the [roadmap](FUTURE_FEATURES.md) tracks pending work. See the [permission matrix](docs/user_interface.en.md#administrator-panel) for roles and their operations.

The interface guide is the reference for daily tasks. It is also available from the `?` button in the top navigation or at `/help?lang=en`.
On supported mobile browsers, you can add Glicko DB to your home screen as an app.

- [User interface help](docs/user_interface.en.md)
- [Routes and integration notes](docs/api_endpoints.en.md)

## Requirements

- Python 3.10, 3.11, 3.12, or 3.13 (versions included in the CI matrix)
- `pip`
- Python dependencies: [requirements.txt](requirements.txt)
- Development and test dependencies: [requirements-test.txt](requirements-test.txt)

## Local run

Windows PowerShell:

```powershell
python -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt
$env:APP_SECRET_KEY = "replace-with-a-long-random-value"
$env:ADMIN_PASSWORD = "choose-a-strong-password"
python app.py
```

macOS or Linux:

```bash
python3 -m venv .venv
source .venv/bin/activate
python -m pip install -r requirements.txt
export APP_SECRET_KEY="replace-with-a-long-random-value"
export ADMIN_PASSWORD="choose-a-strong-password"
python app.py
```

Open `http://127.0.0.1:5000` in the browser. The application creates the SQLite database at `data/acg_ratings.db` on first start.

Only for local sample data, define `LOAD_SAMPLE_DATA=1` before startup. Do not use sample data in a production database.

## Configuration

Set required environment variables as needed; most defaults are in `config.py`.

- `APP_SECRET_KEY` and `ADMIN_PASSWORD`: session key and one-time password for creating the initial administrator account. Use secure values in production.
- `APP_PUBLIC_ORIGIN`: public HTTPS origin for password-recovery links.
- `DB_PATH` in `config.py`: SQLite database path; it is not an environment variable.
- Analytics uses a separate file beside `DB_PATH`, with the `.analytics.db` suffix (default `data/acg_ratings.analytics.db`); application backups and restores exclude it. Startup imports missing historical rows from the legacy table without changing it.
- `SQLITE_JOURNAL_MODE=DELETE` (default) or `WAL`: primary database journal mode, applied at startup. WAL requires a compatible local filesystem and staging validation; all application connections wait up to 5000 ms for locks. See the [WAL procedure](docs/operations.md#sqlite-journals-analytics-and-restore-cutover).
- `GEOIP_COUNTRY_DB`: path to the local GeoLite2 Country database, defaulting to `data/GeoLite2-Country.mmdb`. It is used for profile display and aggregate site analytics. It is not included; obtain it under its [license](https://www.maxmind.com/en/geolite/eula).
- `MAIL_SERVER`, `MAIL_PORT`, `MAIL_USERNAME`, `MAIL_PASSWORD`, `MAIL_USE_TLS`, and `MAIL_FROM`: SMTP settings for recovery and alerts.
- `RECAPTCHA_SITE_KEY` and `RECAPTCHA_SECRET_KEY`: reCAPTCHA v3 keys for member registration.
- `LOAD_SAMPLE_DATA=1`: loads local sample data; do not use in production.
- `AUDIT_RETENTION_DAYS` and `SCHEDULED_BACKUP_KEEP`: audit and scheduled-backup retention.
- `BACKUP_FRESHNESS_ENABLED` (default `false`) and `BACKUP_FRESHNESS_MAX_AGE_HOURS` (default `768`): enable and configure the independent backup checker.
- `API_RATE_LIMIT_READS_PER_TOKEN=60`, `API_RATE_LIMIT_WRITES_PER_TOKEN=20`, and `API_RATE_LIMIT_REQUESTS_PER_IP=120`: API limits per fixed 60-second window.
- `API_MAX_REQUEST_BYTES=1048576`: maximum API request-body size (1 MiB).
- `API_REQUIRE_HTTPS=true`: requires HTTPS for bearer requests and token creation. Set `API_REQUIRE_HTTPS=false` only for local development without HTTPS.
- `API_ENABLED_SLICES` and `API_STAFF_PILOT_SLICES`: enabled API slices and slices limited to staff during a pilot. Both are empty by default, so the API returns `503`; see [Authenticated API rollout](#authenticated-api-rollout).

The default timezone is UTC-5. Use HTTPS in production and keep secrets out of source control. If TLS terminates at a proxy, configure the WSGI server to report the HTTPS scheme only from trusted proxies; do not trust arbitrary forwarded headers.

## Project roadmap

See the [project roadmap](FUTURE_FEATURES.md) for pending proposals and [API deployment notes](docs/operations.md#api-rollout-and-rollback) for the implemented A0-A6 milestones. Each API slice is enabled through configuration. Existing public web pages and exports remain available.

## Development

Protect the shared host: prefer local tests; allow at most three approved production GETs, sequentially, five seconds apart, with small pages. Stop on slow responses, timeouts, 429, or 5xx; do not retry automatically. Do not run benchmarks, rating replay, or load tests on the live server: a disposable database does not isolate CPU or I/O. Follow the [low-impact validation rules](docs/operations.md#low-impact-production-validation). Never store tokens or passwords in notes or documentation.

Help pages are rendered from Markdown. To export static HTML copies:

```powershell
python scripts/build_help_html.py
```

Run tests from the project root:

```powershell
python -m pip install -r requirements-test.txt
python -m pytest -q
```

CI runs the full suite on Python 3.10–3.13, including OpenAPI contract and documentation checks. Chromium navigation tests are optional and are not part of CI.

Optional Chromium navigation tests require Playwright:

```powershell
python -m pip install playwright
python -m playwright install chromium
python -m pytest tests\test_navigation_browser.py -q
```

### Code organization

Application routes are in `routes/`, shared logic in `services/`, and tests in `tests/`. Test fixtures are versioned with the project.

### Linux hosting installation

Use a version included in the CI matrix (Python 3.10–3.13), create a virtual environment, and install dependencies:

```bash
python3 -m venv .venv
.venv/bin/python -m pip install --upgrade pip
.venv/bin/python -m pip install --only-binary=Pillow -r requirements.txt
```

The production application runs under Passenger. If Pillow has no compatible wheel, check that the host's Python version and platform are supported. For common deployment issues, check server logs, the Python environment, dependencies, WSGI entry point, permissions, and routing. Optional diagnostic tools are in `scripts/dev_only/`; do not include them in normal deployments.

Under Passenger, schema migrations and cleanup should run during controlled startup, not in read-only requests such as reports. For high-CPU investigation and historical SQLite failures, see the [operational notes](docs/operations.md#passenger-resource-investigation-and-report-read-regression).

### Scheduled backups and restore drills

Schedule `scripts/scheduled_backup.py` with the same Python runtime, configuration, and environment used by the application. The database path is defined in `config.py`; `SCHEDULED_BACKUP_KEEP` controls retention. The job verifies the backup and a restore into a temporary database before applying retention; it exits with status 1 on failure. In Windows Task Scheduler, configure:

- **Program**: `C:\path\to\glicko-db\.venv\Scripts\python.exe`
- **Arguments**: `scripts\scheduled_backup.py`
- **Start in**: `C:\path\to\glicko-db`

On Linux, a daily cron entry can be:

```cron
0 2 * * * cd /srv/glicko-db && .venv/bin/python scripts/scheduled_backup.py >> /var/log/glicko-db-backup.log 2>&1
```

Set the same production environment variables in the scheduler; cron and Task Scheduler may not inherit the web server's environment. As an administrator, review `/admin/backups` for rating freshness, the latest backup and restore check, and operational failures. Practice restoration on a disposable instance. Before replacing the active database, schedule a maintenance window, block new writes, and let in-flight requests finish during the final swap. SQLite backups do not include `uploads/sgf`; protect and restore that directory separately.

With WAL, also stop readers and new connections during final cutover. Restore checks and drains WAL before replacing the database; blocking connections cause it to abort without replacing the data. Never manually delete `-wal` or `-shm`. The restored database uses DELETE mode; the next controlled startup reapplies `SQLITE_JOURNAL_MODE`.

To detect a job that never runs, schedule `scripts/check_backup_freshness.py` separately with the same interpreter, database and environment, including SMTP and `BACKUP_FRESHNESS_ENABLED=true`. Choose the checker frequency independently of the backup; no schedule is created automatically. Set `BACKUP_FRESHNESS_MAX_AGE_HOURS` to a positive integer: 768 hours (32 days) allows a 31-day monthly interval and one additional day. Adjust the margin to the actual schedule.

For example, after configuring the production and SMTP environment for cron, this checks hourly (adjust the path, frequency and log):

```cron
0 * * * * cd /srv/glicko-db && BACKUP_FRESHNESS_ENABLED=true .venv/bin/python scripts/check_backup_freshness.py >> /var/log/glicko-db-freshness.log 2>&1
```

The checker uses the latest recorded UTC scheduled-backup success. If none exists, it allows the full threshold from the first enabled check; an already-stale success alerts immediately. It emails active administrators with valid addresses when overdue, reminds them at most every 24 hours when the checker runs, and sends a recovery email when fresh again. Delivery failures are logged and retried at the next check. Exit status: 0 fresh, in grace or disabled; 1 stale; 2 configuration, database or delivery error. Monitor these errors too. To disable and restart the grace period before re-enabling, run the checker once with `BACKUP_FRESHNESS_ENABLED=false`.

The operational journal has bounded retention; a pruned success and legacy timestamps without UTC do not count. The journal is evidence of a run, not proof that the file still exists or can be restored, and does not include SGFs. Monitor state lives in the application database and can roll back on restore. A checker on the same host cannot detect its own failure or a host outage; use external monitoring if that coverage is needed. Verify SMTP and recipients before relying on alerts.

### Authenticated API rollout

The `/api/v1` API is enabled in independent slices and is disabled on a new installation. A disabled slice returns `503` (`api_slice_disabled`) without affecting web pages, sessions, or exports.

- `API_ENABLED_SLICES`: comma-separated enabled slices (`docs`, `reads`, `submissions`, `moderation`, `tournament_setup`, `tournament_participants`, `tournament_rounds`), or `all`.
- `API_STAFF_PILOT_SLICES`: enabled slices that only Organizers, Managers, and Administrators may use; other accounts receive `503` (`api_pilot_restricted`).

An unknown name, or `all` combined with other names, prevents the application from starting. Restart the Passenger application after changing these variables.

1. Confirm a verified backup, HTTPS, and the trusted-proxy configuration. Record the slices being enabled in the release notes.
2. Enable `docs,reads`, list the same slices in `API_STAFF_PILOT_SLICES`, restart, and run the smoke client with a staff pilot token. The client uses only the standard library, reads the token from an environment variable, and never prints it:

   ```powershell
   $env:GLICKO_API_TOKEN = "<pilot token>"
   python scripts/api_smoke_test.py --base-url https://example.org/glicko --expect-enabled docs,reads --expect-disabled submissions,moderation,tournament_setup,tournament_participants,tournament_rounds
   ```

3. Remove `docs,reads` from `API_STAFF_PILOT_SLICES` to open them to members. Then enable `submissions`, `moderation`, `tournament_setup`, `tournament_participants`, and `tournament_rounds` one at a time, each first as a staff pilot. Verify each step with the smoke client and the `api.request.completed` logs, which include the slice, status, and duration.

To roll back, remove the affected slices from `API_ENABLED_SLICES`, restart, and run the smoke client with `--expect-disabled`. If needed, owners revoke pilot tokens from their profiles. Disabling a slice does not undo committed writes: correct data through the normal web workflows, and do not restore an older database to undo an API release. Measured budgets and proposed thresholds are in the [operations notes](docs/operations.md#api-benchmark-and-proposed-budgets).

#### Production verification (2026-11-06)

Operator-reported output and direct read checks are consolidated in [production observations](docs/operations.md#production-api-observations). These historical snapshots do not verify successful production writes or rollback. The activity defect and outstanding checks are in the [roadmap](FUTURE_FEATURES.md#production-verification-and-operations).

The smoke client's `--write-draft-tournament` option also creates one draft tournament; use it only against disposable data. To benchmark the API in a new disposable database, which the script refuses to overwrite:

```powershell
python scripts/benchmark_api.py --output $env:TEMP\glicko-api-benchmark.db
```

## License and attribution

Glicko DB was originally developed for the Go community in Colombia by Juan Felipe Samper in 2026.

The Glicko-2 system was published by Mark Glickman in 2022 into the public domain. The Python implementation is © 2009 Ryan Kirkman, and BesoGo is © 2015-2018 Ye Wang. Both are distributed under the [MIT license](./static/vendor/besogo/LICENSE).

This product uses GeoLite data created by [MaxMind](https://www.maxmind.com/) and data from [GeoNames](https://www.geonames.org/). See the [GeoLite license](https://www.maxmind.com/en/geolite/eula), [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/), and [CC BY-SA 4.0](https://creativecommons.org/licenses/by-sa/4.0/).
