SQL, пожалуйста

Интерактивный русскоязычный курс базового SQL на настоящем PostgreSQL. Внутри — 21 последовательный урок: от таблиц, SELECT и NULL до агрегаций, всех основных JOIN, изменения данных, индексов, подзапросов и транзакций.

Каждый урок устроен одинаково:

1. короткое объяснение понятия и цели;
2. примеры SQL с пояснениями;
3. практическое задание в редакторе;
4. пробный запуск и автоматическая проверка;
5. подсказки и сохранение прогресса в браузере.

Быстрый запуск

Нужны Docker и Docker Compose.

docker compose up --build

После готовности контейнеров откройте http://localhost:3000. PostgreSQL доступен для локальной разработки только на loopback-адресе 127.0.0.1:54329 и не публикуется во внешнюю сеть.

Доступ из локальной сети

Веб-приложение явно публикуется на 0.0.0.0:3000. С телефона или другого компьютера в той же сети откройте:

http://<локальный-IP-компьютера>:3000

На macOS адрес Wi-Fi обычно можно узнать так:

ipconfig getifaddr en0

Например, для адреса 192.168.1.50 ссылка будет http://192.168.1.50:3000. IP может меняться после переподключения к сети. Если страница не открывается с другого устройства, убедитесь, что устройства находятся в одной сети и системный firewall разрешает входящие подключения Docker Desktop.

Адрес привязки и внешний порт можно переопределить без изменения Compose:

APP_BIND_ADDRESS=0.0.0.0 APP_PORT=8080 docker compose up --build

PostgreSQL при этом остаётся привязан к 127.0.0.1 и по локальной сети не доступен. У приложения нет учётных записей, поэтому открывайте порт только в доверенной домашней или рабочей сети.

Остановить приложение:

docker compose down

Удалить учебный volume и создать базу заново:

docker compose down -v

Локальная разработка

PostgreSQL удобно поднять отдельно командой docker compose up -d database. Приложение ожидает строку подключения в DATABASE_URL; для запуска Node.js на хосте используйте:

postgres://sql_nastya:sql_nastya@localhost:54329/sql_nastya

Команды Node.js:

npm install
DATABASE_URL=postgres://sql_nastya:sql_nastya@localhost:54329/sql_nastya npm run dev
npm test

Интеграционный прогон после docker compose up -d --build:

npm run test:all

Печатная схема данных

В диалоге «Схема учебных данных» есть ссылка на public/schema.pdf — одностраничную схему A4 с таблицами, связями, легендой и типами столбцов. Файл собирается из того же dataset в src/lessons.js, что и диалог:

npm run build:schema-pdf

Скрипту не нужны зависимости: он сам встраивает подмножества системных шрифтов. Если стандартных шрифтов в системе нет, пути задаются переменными SCHEMA_PDF_FONT_DISPLAY, SCHEMA_PDF_FONT_SANS, SCHEMA_PDF_FONT_SANS_BOLD и SCHEMA_PDF_FONT_MONO. После изменения dataset схему нужно пересобрать — иначе npm test сообщит, что PDF устарел.

Смысловая проверка решения моделью

Программная проверка сравнивает данные: совпали столбцы и строки — задание принято. Такой ответ можно получить и обходным путём — выписать нужные строки литералами, перечислить их по id или подставить готовое число вместо вычисления. Поэтому после успешного сравнения решение дополнительно смотрит LLM: она получает формулировку задания, схему учебной базы, эталонное решение и запрос ученика, а возвращает один из трёх вердиктов.

Вердикт	Что означает	Что происходит
solves	запрос решает задачу	зачёт
warn	решает, но есть замечания	зачёт и заметка под результатом
cheats	результат подогнан под ответ	зачёт снимается, показывается объяснение

Правила проверки:

• модель вызывается только после успешного сравнения данных — неверный результат разбирать незачем;
• эталон не считается единственно верным: другой синтаксис, JOIN вместо подзапроса или CTE принимаются;
• в спорной ситуации модель выбирает warn, а не cheats, чтобы не наказывать за необычное, но верное решение;
• запрос ученика передаётся как данные: инструкции в комментариях не выполняются, попытка повлиять на оценку попадает в лог сервера;
• недоступная модель, таймаут или неразобранный ответ не мешают учиться — зачёт остаётся, а под результатом появляется пометка, что смысловую проверку выполнить не удалось.

Проверка выключена, пока не задан LLM_BASE_URL. Подходит любой сервис с OpenAI-совместимым маршрутом /responses:

LLM_BASE_URL=https://api.openai.com/v1 LLM_API_KEY=sk-... docker compose up --build

Если модель берётся из подписки Codex, рядом есть профиль llm с сервисом kotlm — прокси, который держит OAuth-сессию, поворачивает refresh-токен и считает расход по проектам. Положите secrets/codex_auth.json и secrets/clients.json, затем:

docker compose --profile llm up -d
# в .env: LLM_BASE_URL=http://kotlm:8080/v1, LLM_API_KEY=<ключ проекта>, LLM_MODEL=gpt-5.6

Переменные: LLM_BASE_URL, LLM_API_KEY, LLM_MODEL (по умолчанию gpt-5-mini), LLM_REASONING, LLM_TIMEOUT_MS, LLM_MAX_OUTPUT_TOKENS и LLM_REVIEW=off для временного отключения. Текущее состояние видно в GET /api/health в поле review и в логе при старте приложения.

Синхронизация прогресса между устройствами

Пройденные уроки и черновики запросов по умолчанию лежат в localStorage, то есть у каждого браузера свои. Если приложение опубликовано за Cloudflare Access, прогресс дополнительно хранится на сервере и общий для всех устройств одного ученика.

Ученика опознаёт сам Access: он проверяет вход и добавляет к каждому запросу заголовок Cf-Access-Jwt-Assertion. Приложение берёт из токена sub и почту. Подпись токена намеренно не проверяется — приложение доступно только через контейнер cloudflared внутри docker-сети, порт наружу не публикуется, а на кону лишь отметки о пройденных уроках. Если приложение когда-нибудь начнут публиковать напрямую, проверку подписи нужно вернуть: заголовок подделывается одной строкой curl.

Правила слияния:

• пройденные уроки объединяются, а не заменяются: устройство, которое давно не открывали, ничего не сотрёт;
• черновик подтягивается с сервера только для уроков, которых на этом устройстве ещё не касались: набранный здесь запрос не подменяется чужим прямо в открытом редакторе;
• на сервере черновик урока перетирает последняя отправка, поэтому два устройства, где писали один и тот же урок, так и останутся каждое при своём тексте;
• кнопка «Сбросить прогресс» удаляет строку на сервере целиком, иначе слияние вернуло бы отметки обратно;
• недоступный сервер курс не ломает: отметки остаются в браузере и уедут при следующем изменении.

Прогресс хранится под отдельной ролью sql_nastya_app в схеме app, к которой у роли ученика нет доступа, — иначе чужие отметки читались бы обычным SELECT прямо из урока. Синхронизация выключена, пока не задан PROGRESS_DATABASE_URL; PROGRESS_SYNC=off отключает её, не убирая адрес. Текущее состояние видно в GET /api/health в поле sync.

Схему прогресса создаёт docker/progress.sql. Файл идемпотентен: Compose выполняет его при создании пустой базы, и его же применяют к уже работающей:

psql "$DATABASE_URL" -f docker/progress.sql

Как устроена учебная песочница

• Для каждого запуска API берёт отдельное подключение и создаёт временные customers, orders, products и order_items.
• Для урока о схемах существует отдельная постоянная archive.customers; роль ученика может только читать её. Одноимённая временная customers остаётся независимой.
• Начальные данные заново загружаются перед каждым запросом.
• Обычные упражнения выполняются внутри транзакции, которая всегда откатывается.
• Урок о транзакциях использует отдельное одноразовое подключение, чтобы ученик мог сам выполнить BEGIN и ROLLBACK.
• Роль sql_nastya имеет CONNECT, TEMPORARY и точечный read-only доступ к archive; она не владеет базой и не создаёт постоянные объекты. Стандартные read-only представления information_schema доступны для изучения метаданных.
• Дополнительный фильтр разрешает только типы SQL-команд, нужные конкретному уроку, и ограничивает запрос 3,5 секундами.

Это надёжная локальная учебная изоляция, но не замена отдельным контейнерам или виртуальным машинам для публичного многопользовательского сервиса с недоверенными пользователями.

Структура

public/                 интерфейс без отдельной сборки
public/schema.pdf       печатная схема учебных данных на A4
scripts/                сборка печатной схемы (в образ не попадает)
src/lessons.js          содержание и критерии 21 урока
src/lesson-runner.js    запуск и проверка решений
src/llm-review.js       смысловая проверка запроса моделью
src/llm-client.js       клиент OpenAI-совместимого /responses
src/database.js         временная схема и тестовые данные
src/sql-safety.js       допустимые команды учебной среды
src/identity.js         ученик из токена Cloudflare Access
src/progress-store.js   прогресс в схеме app под отдельной ролью
src/server.js           HTTP API и статические файлы
docker/init.sql         непривилегированная роль и read-only схема archive
docker/progress.sql     схема app для синхронизации прогресса
test/                   модульные и интеграционные тесты

Основные API-маршруты:

• GET /api/catalog — публичное содержание курса без эталонных ответов;
• GET /api/health — готовность приложения, PostgreSQL, состояние смысловой проверки и синхронизации;
• GET /api/progress — прогресс ученика с сервера; PUT объединяет присланное с сохранённым, DELETE сбрасывает;
• POST /api/lessons/:id/run — пробный запуск;
• POST /api/lessons/:id/check — запуск, проверка результата или состояния базы и, если модель настроена, смысловая проверка запроса в поле review.