lekss361/gendesign

Fork 0

feat(tradein): in-app scheduler — UI-managed schedule (replaces SSH crontab) #482

Merged

lekss361 merged 1 commit from feat/tradein-scheduler-backend into main

2026-05-23 14:54:25 +00:00

lekss361 commented

2026-05-23 14:51:34 +00:00

Owner

Summary

In-app scheduler: backend periodically (60s tick) проверяет scrape_schedules table → автоматически запускает city sweep если due (next_run_at ≤ NOW + enabled + no running). Recompute next_run_at для следующих суток в random время в окне. Заменяет SSH crontab setup на UI-managed config в БД.

LOC: +443 / -2 (6 files).

Changes

File	Type	Что
`data/sql/052_scrape_schedules.sql`	NEW	Table с config; seed `avito_city_sweep` window 2-5 UTC (05:00-08:00 МСК), enabled
`services/scheduler.py`	NEW	`compute_next_run_at()` (random hour в окне, cross-midnight OK) + `scheduler_loop()` async + `reap_zombies()` (runs > 6h без heartbeat → zombie) + `trigger_avito_city_sweep_run()`
`main.py`	MODIFY	FastAPI lifespan launches scheduler task, clean shutdown via cancel
`api/v1/admin.py`	MODIFY	2 endpoints: `GET /admin/scrape/schedules`, `PUT /admin/scrape/schedules/{source}`
`schemas/trade_in.py`	MODIFY	`ScheduleConfig` + `ScheduleConfigUpdate` Pydantic
`tests/test_scheduler.py`	NEW	6 offline tests (normal/cross-midnight/1h/future/tz-aware/zombie threshold)

Verify

✅ ruff clean (после 3 итераций — все nits resolved)
✅ pytest test_scheduler.py 6/6 pass
✅ pytest regression test_city_sweep.py + test_api_avito_stage4a.py 21/21 pass

Deviations

f-string INTERVAL '{N} hours' в SQL → CAST(:interval AS interval) параметр (no string interp)
task.add_done_callback для asyncio.create_task (RUF006)
assert row is not None в update_schedule (защита от None)
6 tests вместо 4 (added future-check + tz-aware)

Behavior

Tick every 60s
Initial sleep 30s (let FastAPI startup finish)
Per-tick: reap zombies → query due schedules → trigger if no running run → recompute next_run_at
Logs: scheduler: triggered city_sweep run_id=N next_run_at=ISO

Auth

Все endpoints без require_admin (PR #474 baseline). Caddy basic_auth — единственный gate.

Test plan

ruff + pytest offline
Deploy → migration 052 auto-apply → verify scrape_schedules row для 'avito_city_sweep' exists с window 2-5 UTC
Через 30 sec после deploy → check logs: scheduler: started + scheduler task spawned
PUT /admin/scrape/schedules/avito_city_sweep через UI → verify next_run_at recomputed
Wait до next_run_at → verify auto-triggered run появляется в /admin/scrape/avito-city-sweep/runs

Frontend

Parallel PR feat/tradein-frontend-scheduler добавляет UI секцию (toggle enabled, window МСК ввод, status preview).

Cron-script backward compat

deploy/avito-city-sweep.sh остаётся в репо как backup опция. Никаких изменений — opt-in для тех кто хочет cron вместо in-app scheduler. После deploy this PR — рекомендация удалить crontab entry чтобы избежать double-trigger.

## Summary In-app scheduler: backend periodically (60s tick) проверяет `scrape_schedules` table → автоматически запускает city sweep если due (next_run_at ≤ NOW + enabled + no running). Recompute next_run_at для следующих суток в random время в окне. **Заменяет SSH crontab setup на UI-managed config в БД.** LOC: +443 / -2 (6 files). ## Changes | File | Type | Что | |---|---|---| | `data/sql/052_scrape_schedules.sql` | NEW | Table с config; seed `avito_city_sweep` window 2-5 UTC (05:00-08:00 МСК), enabled | | `services/scheduler.py` | NEW | `compute_next_run_at()` (random hour в окне, cross-midnight OK) + `scheduler_loop()` async + `reap_zombies()` (runs > 6h без heartbeat → zombie) + `trigger_avito_city_sweep_run()` | | `main.py` | MODIFY | FastAPI lifespan launches scheduler task, clean shutdown via cancel | | `api/v1/admin.py` | MODIFY | 2 endpoints: `GET /admin/scrape/schedules`, `PUT /admin/scrape/schedules/{source}` | | `schemas/trade_in.py` | MODIFY | `ScheduleConfig` + `ScheduleConfigUpdate` Pydantic | | `tests/test_scheduler.py` | NEW | 6 offline tests (normal/cross-midnight/1h/future/tz-aware/zombie threshold) | ## Verify - ✅ ruff clean (после 3 итераций — все nits resolved) - ✅ pytest `test_scheduler.py` 6/6 pass - ✅ pytest regression `test_city_sweep.py` + `test_api_avito_stage4a.py` 21/21 pass ## Deviations - f-string `INTERVAL '{N} hours'` в SQL → `CAST(:interval AS interval)` параметр (no string interp) - `task.add_done_callback` для asyncio.create_task (RUF006) - `assert row is not None` в update_schedule (защита от None) - 6 tests вместо 4 (added future-check + tz-aware) ## Behavior - Tick every 60s - Initial sleep 30s (let FastAPI startup finish) - Per-tick: reap zombies → query due schedules → trigger if no running run → recompute next_run_at - Logs: `scheduler: triggered city_sweep run_id=N next_run_at=ISO` ## Auth Все endpoints без `require_admin` (PR #474 baseline). Caddy basic_auth — единственный gate. ## Test plan - [x] ruff + pytest offline - [ ] Deploy → migration 052 auto-apply → verify `scrape_schedules` row для 'avito_city_sweep' exists с window 2-5 UTC - [ ] Через 30 sec после deploy → check logs: `scheduler: started` + `scheduler task spawned` - [ ] PUT `/admin/scrape/schedules/avito_city_sweep` через UI → verify next_run_at recomputed - [ ] Wait до next_run_at → verify auto-triggered run появляется в `/admin/scrape/avito-city-sweep/runs` ## Frontend Parallel PR `feat/tradein-frontend-scheduler` добавляет UI секцию (toggle enabled, window МСК ввод, status preview). ## Cron-script backward compat `deploy/avito-city-sweep.sh` остаётся в репо как backup опция. Никаких изменений — opt-in для тех кто хочет cron вместо in-app scheduler. После deploy this PR — рекомендация удалить crontab entry чтобы избежать double-trigger.

lekss361 added 1 commit 2026-05-23 14:51:35 +00:00

feat(tradein): in-app scheduler — UI-managed schedule (replaces SSH crontab) 0c53108d7d

Background scheduler в FastAPI lifespan: each tick (60s) checks scrape_schedules
table, triggers city sweep run если due (next_run_at <= NOW() + enabled + no
running run), recomputes next_run_at для следующих суток в random время в окне.

- 052_scrape_schedules.sql: NEW table (source, enabled, window_start/end_hour UTC,
  default_params jsonb, last_run_id, last_run_at, next_run_at, updated_at).
  Seed: 'avito_city_sweep' enabled, window 2-5 UTC (05:00-08:00 МСК).
- scheduler.py NEW:
  * compute_next_run_at(start, end) — random datetime в окне для +1 day. Cross-midnight OK.
  * scheduler_loop() — tick каждые 60s
  * get_due_schedules / has_running_run / trigger_avito_city_sweep_run
  * reap_zombies() — runs running > 6h без heartbeat → status='zombie'
- main.py: FastAPI lifespan launches scheduler_task; clean shutdown через cancel
- admin.py: 2 endpoints
  * GET  /admin/scrape/schedules — list all (now single row, extensible)
  * PUT  /admin/scrape/schedules/{source} — update config (enabled, window, params)
    + auto-recompute next_run_at
- schemas/trade_in.py: ScheduleConfig + ScheduleConfigUpdate
- tests/test_scheduler.py: 6 offline tests (normal window, cross-midnight, 1h window,
  future check, timezone-aware check)

UI: frontend PR добавит секцию 6 на /scrapers/avito (parallel PR).
Cron-script deploy/avito-city-sweep.sh остаётся как backup, opt-in.

lekss361 merged commit 0202720096 into main

2026-05-23 14:54:25 +00:00

lekss361 referenced this pull request from a commit

2026-05-23 14:54:27 +00:00

feat(tradein): in-app scheduler — UI-managed schedule (replaces SSH crontab) (#482)

lekss361 commented

2026-05-23 14:55:08 +00:00

Author

Owner

Merged via deep-code-reviewer — verdict APPROVE (minor advisories).

Verification summary

Migration 052 idempotency: PASS

BEGIN; ... COMMIT; wrap correct
CREATE TABLE IF NOT EXISTS scrape_schedules + CREATE INDEX IF NOT EXISTS scrape_schedules_next_run_idx — re-runnable
INSERT ... ON CONFLICT (source) DO NOTHING — seed is safe on re-deploy
CHECK constraints on window_start_hour/window_end_hour (0..23) — enforced at DB level
FK last_run_id REFERENCES scrape_runs(id) valid (migration 015 created scrape_runs)
Ordering OK: 052 > 051_scrape_runs_extend

scheduler_loop reliability: PASS

try/except Exception → logger.exception обёртка tick — loop не падает на одной ошибке
await asyncio.sleep(SCHEDULER_TICK_SEC) всегда выполняется (после except)
Initial 30s sleep — даёт FastAPI startup завершиться
task.add_done_callback(...) — RUF006 compliant, task не GC'нут
Lifespan cancel + await scheduler_task с CancelledError swallow — clean shutdown
Per-tick новая SessionLocal() + db.close() в finally — нет утечки connections
Spawned _run() тоже использует свой SessionLocal() — не делит сессию с tick'ом

compute_next_run_at(): PASS

Normal window [2,5): random in randint(2*3600, 5*3600-1) = hours 2/3/4
Cross-midnight 22→3: total_seconds = (24-22)+3 = 5h → first_half=2h в [22,24), вторая часть в [0,3). Math correct
1-hour window [3,4): randint(10800, 14399) → строго hour=3
All branches return tzinfo=UTC (datetime.combine с tzinfo=UTC)
Tests 6/6 покрывают normal/cross-midnight/1h/future/tz-aware/zombie threshold

reap_zombies(): PASS

CAST(:interval AS interval) — psycopg v3 compliant, no SQL injection
heartbeat_at IS NULL OR heartbeat_at < NOW() - 6h — корректный predicate
Uses index scrape_runs_zombies_idx (status, heartbeat_at) из миграции 015
6h threshold reasonable (sweep обычно 3-12 min)

No-running-run guard: PASS

has_running_run() check inside trigger_avito_city_sweep_run() — atomic per session
Race condition при rapid ticks: 2-й tick найдёт status='running' от 1-го, skip → correct

Contract stability для PR #483 (frontend): PASS

GET /admin/scrape/schedules → list[ScheduleConfig]
PUT /admin/scrape/schedules/{source} → ScheduleConfig
ScheduleConfig поля: id, source, enabled, window_start_hour, window_end_hour, default_params, last_run_id, last_run_at (ISO), next_run_at (ISO), updated_at (ISO)
ScheduleConfigUpdate body: enabled (default true), window_start_hour (default 2), window_end_hour (default 5), default_params
Frontend codegen npm run codegen после deploy подтянет types

psycopg v3 compliance: PASS

Все params через :name binding (CAST(:interval AS interval), CAST(:params AS jsonb))
Нет f-string interpolation в SQL
import json + json.dumps(...) для jsonb param — корректно

Auth model: PASS

2 endpoints без require_admin — соответствует PR #474 baseline (Caddy basic_auth — единственный gate)

Minor advisories (post-merge, not blocking)

Crontab removal action required: после deploy надо удалить crontab entry avito-city-sweep.sh на VPS, иначе double-trigger (cron + in-app scheduler). Script остаётся в репо как backup — deploy/avito-city-sweep.sh. Команда: crontab -e → удалить line с avito-city-sweep.sh.
Single-replica assumption documented: если в будущем добавится 2-й backend replica → нужен advisory lock (pg_try_advisory_lock) в начале tick'а чтобы один schedulers не дублировал triggers. Текущий pilot — single replica, OK.
Vault entry: новый pattern (in-app scheduler заменяет cron) — желательно code/modules/tradein/InApp_Scheduler.md или decisions/2026-05-23-inapp-scheduler-replaces-cron.md. Не сделано в этом PR.
db.commit() после reap_zombies() в loop: можно объединить в одну транзакцию с get_due_schedules() + trigger_* но текущая разбивка читабельнее и race-condition-safe.

Post-merge verify checklist

deploy-tradein.yml triggers on push to main
scrape_schedules table + seed row exists на проде
Backend logs: scheduler: started (tick=60s) + FastAPI lifespan: scheduler task spawned через ~30s after start
GET /admin/scrape/schedules → 1 row для avito_city_sweep
PUT /admin/scrape/schedules/avito_city_sweep → next_run_at recomputed
Wait до next_run_at → check GET /admin/scrape/avito-city-sweep/runs — auto-triggered run появился

Merged via deep-code-reviewer — verdict APPROVE (minor advisories). ## Verification summary ### Migration 052 idempotency: PASS - `BEGIN; ... COMMIT;` wrap correct - `CREATE TABLE IF NOT EXISTS scrape_schedules` + `CREATE INDEX IF NOT EXISTS scrape_schedules_next_run_idx` — re-runnable - `INSERT ... ON CONFLICT (source) DO NOTHING` — seed is safe on re-deploy - `CHECK` constraints on `window_start_hour`/`window_end_hour` (0..23) — enforced at DB level - FK `last_run_id REFERENCES scrape_runs(id)` valid (migration 015 created `scrape_runs`) - Ordering OK: 052 > 051_scrape_runs_extend ### scheduler_loop reliability: PASS - `try/except Exception → logger.exception` обёртка tick — loop не падает на одной ошибке - `await asyncio.sleep(SCHEDULER_TICK_SEC)` всегда выполняется (после except) - Initial 30s sleep — даёт FastAPI startup завершиться - `task.add_done_callback(...)` — RUF006 compliant, task не GC'нут - Lifespan cancel + `await scheduler_task` с `CancelledError` swallow — clean shutdown - Per-tick новая `SessionLocal()` + `db.close()` в finally — нет утечки connections - Spawned `_run()` тоже использует свой `SessionLocal()` — не делит сессию с tick'ом ### compute_next_run_at(): PASS - Normal window `[2,5)`: random in `randint(2*3600, 5*3600-1)` = hours 2/3/4 - Cross-midnight `22→3`: total_seconds = (24-22)+3 = 5h → first_half=2h в [22,24), вторая часть в [0,3). Math correct - 1-hour window `[3,4)`: `randint(10800, 14399)` → строго hour=3 - All branches return `tzinfo=UTC` (datetime.combine с `tzinfo=UTC`) - Tests 6/6 покрывают normal/cross-midnight/1h/future/tz-aware/zombie threshold ### reap_zombies(): PASS - `CAST(:interval AS interval)` — psycopg v3 compliant, no SQL injection - `heartbeat_at IS NULL OR heartbeat_at < NOW() - 6h` — корректный predicate - Uses index `scrape_runs_zombies_idx (status, heartbeat_at)` из миграции 015 - 6h threshold reasonable (sweep обычно 3-12 min) ### No-running-run guard: PASS - `has_running_run()` check inside `trigger_avito_city_sweep_run()` — atomic per session - Race condition при rapid ticks: 2-й tick найдёт `status='running'` от 1-го, skip → correct ### Contract stability для PR #483 (frontend): PASS - `GET /admin/scrape/schedules` → `list[ScheduleConfig]` - `PUT /admin/scrape/schedules/{source}` → `ScheduleConfig` - `ScheduleConfig` поля: `id`, `source`, `enabled`, `window_start_hour`, `window_end_hour`, `default_params`, `last_run_id`, `last_run_at` (ISO), `next_run_at` (ISO), `updated_at` (ISO) - `ScheduleConfigUpdate` body: `enabled` (default true), `window_start_hour` (default 2), `window_end_hour` (default 5), `default_params` - Frontend codegen `npm run codegen` после deploy подтянет types ### psycopg v3 compliance: PASS - Все params через `:name` binding (`CAST(:interval AS interval)`, `CAST(:params AS jsonb)`) - Нет f-string interpolation в SQL - `import json` + `json.dumps(...)` для jsonb param — корректно ### Auth model: PASS - 2 endpoints без `require_admin` — соответствует PR #474 baseline (Caddy basic_auth — единственный gate) ## Minor advisories (post-merge, not blocking) 1. **Crontab removal action required**: после deploy надо удалить crontab entry `avito-city-sweep.sh` на VPS, иначе double-trigger (cron + in-app scheduler). Script остаётся в репо как backup — `deploy/avito-city-sweep.sh`. Команда: `crontab -e` → удалить line с `avito-city-sweep.sh`. 2. **Single-replica assumption documented**: если в будущем добавится 2-й backend replica → нужен advisory lock (`pg_try_advisory_lock`) в начале tick'а чтобы один schedulers не дублировал triggers. Текущий pilot — single replica, OK. 3. **Vault entry**: новый pattern (in-app scheduler заменяет cron) — желательно `code/modules/tradein/InApp_Scheduler.md` или `decisions/2026-05-23-inapp-scheduler-replaces-cron.md`. Не сделано в этом PR. 4. **`db.commit()` после `reap_zombies()` в loop**: можно объединить в одну транзакцию с `get_due_schedules()` + `trigger_*` но текущая разбивка читабельнее и race-condition-safe. ## Post-merge verify checklist 1. `deploy-tradein.yml` triggers on push to main 2. `scrape_schedules` table + seed row exists на проде 3. Backend logs: `scheduler: started (tick=60s)` + `FastAPI lifespan: scheduler task spawned` через ~30s after start 4. `GET /admin/scrape/schedules` → 1 row для `avito_city_sweep` 5. `PUT /admin/scrape/schedules/avito_city_sweep` → `next_run_at` recomputed 6. Wait до `next_run_at` → check `GET /admin/scrape/avito-city-sweep/runs` — auto-triggered run появился

lekss361 referenced this pull request

2026-05-23 14:55:52 +00:00

feat(tradein): /scrapers/avito — 6-я секция Расписание (in-app scheduler UI) #483