* Distrust compromised eMudhra intermediates in certifi and Deno
The private keys of eight eMudhra intermediate CAs can be recovered from
their certificates, so anyone can mint a trusted certificate for any host.
* Require certifi>=2026.10.19; yt-dlp, requests and curl-cffi use its bundle,
and 2026.10.19 removes the emSign ECC Root CA - G3 root they chain to.
* Set DENO_TLS_CA_STORE=system for the API (systemd + Docker) and the archive
container so Deno follows the apt-patched system store instead of roots
that only change with a Deno upgrade.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
* Keep Deno's bundled roots in the archive container
browserless/chrome:1.61.1 is Ubuntu 22.04 whose newest ca-certificates is
from 2023, older than Deno 2.8.3's bundled roots. The system store only
becomes the better choice once the archive base image is upgraded.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
---------
Co-authored-by: Claude Opus 5.5 <noreply@anthropic.com>
* perpetual: clear in-progress flags after a crash; downloads only in the perpetual process
Found by a kill -9 stress test on a device:
A SIGKILL inside `with flags.file_worker_counting` (or any of the file
worker's progress flags) left the shared flag set, so every later refresh
raised "flag is already set" until the whole API was restarted.
reconcile_after_predecessor now clears the perpetual process's in-progress
flags (file_worker_*, global_refresh_active) and reports it.
create_downloads started a dispatch in whatever process called it, so an
API worker executed downloads (and could take transcode_lock) despite the
single-process design. It now dispatches only inside the perpetual
process (or under pytest); the download loop picks new rows up within
seconds otherwise.
The shared restart cooldown drops from 60 s to 15 s: it only has to
outlast the manager acting on a request, and a replacement that dies
right away was left dead for the rest of the minute.
Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
* systemd: KillMode=mixed for the API so main shuts its own children down
With control-group kill, SIGTERM reaches the multiprocessing Manager at
the same moment as Sanic's main process; main's cleanup then writes
worker state to a dead Manager (broken pipe) and exits 1, so a plain
`systemctl stop` is recorded as a failure whenever a child takes a
moment to stop (a transcode being killed, for instance). Signal only
main; it stops the workers and the perpetual process itself.
Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
* perpetual: clear an in-progress flag only when its holder is dead
`with flag:` now records the holder pid in shared_ctx.flag_holders and
drops it on exit. The reconcile step clears the flags only the perpetual
process ever enters (file_worker_counting, file_worker_discovery,
global_refresh_active) whenever they are set, and the flags API workers
also enter (file_worker_modeling / indexing / cleanup via refresh_sync,
map_search_building via the map rebuild endpoints) only when the
recorded holder is dead. file_worker_busy is no longer touched: a
collection move in an API worker holds it as a mutex, and
process_queue drops a stale one itself.
Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
---------
Co-authored-by: Claude Fable 5.1 <noreply@anthropic.com>
The database now lives at <media>/config/wrolpi.db so a library (files,
configs, AND database) travels with the drive between WROLPi systems.
Core:
- wrolpi/db.py: lazy SQLite engine factory (WAL, busy_timeout=30s,
foreign_keys=ON); get_db_curs yields a plain sqlite3.Cursor; native
sqlite3 paramstyle everywhere (no compatibility shims); global sqlite3
adapters keep raw-SQL datetime formats identical to the ORM's.
- wrolpi/fts.py: FTS5 external-content search replacing tsvector/GIN.
Injection-proof websearch->FTS5 translator, bm25(10,4,2,1) reproducing
A/B/C/D weighting, fast(abc)/deep column filtering, snippet headlines.
Stemming contract pinned by dedicated tests (watch/watches/watched/
watching are interchangeable).
- wrolpi/schema_ddl.py: SQLite triggers shared verbatim by the alembic
baseline and test DBs; fixes the channel video_count DELETE/UPDATE
bugs and a latent total_size bug (it used the video id as channel id).
- wrolpi/db_bootstrap.py: startup auto-create/migrate; refuses databases
written by a newer WROLPi (db_version_mismatch); SQLite 3.40 floor;
network-filesystem refusal; never creates/migrates/trusts a database
beneath an unmounted production media directory (shadow-DB guard).
- Alembic: 85 Postgres-era migrations squashed into one baseline;
revision ids are dates (2026_07_10_1350) so filenames self-sort and
alembic_version says when a schema was created; render_as_batch=True.
- All raw Postgres SQL rewritten (ANY->json_each IN, array_agg @> ->
HAVING COUNT(DISTINCT), unnest->executemany/UPDATE..FROM,
generate_series histograms->strftime GROUP BY, DISTINCT ON->
row_number(), writable CTEs->two statements, jsonb_*->json_*, explicit
NULLS FIRST/LAST, LIMIT-before-OFFSET). Simple statements converted
to SQLAlchemy instead (flag upsert, download retry/summary/tag checks,
channel-by-tag estimate, Directory delete).
- Config imports count skipped entries; background dumps are blocked
after a lossy import so partial imports can never overwrite YAML
configs (they are the sole source of truth for rebuilds now).
- Search tolerates explicit JSON nulls for limit/offset (Postgres
treated OFFSET NULL as 0; SQLite errors).
- file_group.id/doc_section.id render as INTEGER PRIMARY KEY on SQLite
(BIGINT is not a rowid alias; FTS5 content_rowid requires it).
Ops teardown: docker-compose db service, CI postgres sidecars,
initialize_api_db.sh, controller postgresql service, psycopg2-binary all
removed. upgrade.sh stops/disables postgresql (data is NOT deleted;
`systemctl enable --now postgresql` restores it); systemd units no
longer Want postgresql.service.
Existing-user upgrade: first boot creates+migrates the DB, configs
import (tags recreate their FileGroups from disk), a refresh rebuilds
the library index. QA'd on the 10.0.0.8 RPi: rebuild matched the
Postgres baseline exactly; survived an uncontrolled hard reboot with
clean integrity checks.
Accepted behavior changes: FTS5 has no stopwords; bm25 length
normalization shifts some rank orders; fast-path d_headline is
un-highlighted; all-negative queries match nothing.
Deferred: Debian/trixie package lists (incl. sqlite3 CLI), Cypress
re-baseline, VACUUM INTO snapshots, exFAT/NTFS warning, auto-kicked
rebuild refresh with progress UX.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Storing video settings in session storage to help user start downloads consistently.
Applying channel tag name to new channels when download videos or channels.
Initializing map tiles when importing and resetting map.
Importing maps as wrolpi user, then changing ownership to _renderd.
Changing ownership of /opt/wrolpi as well.
Creating pgpass for wrolpi user in debian 11.
Install specific version of serve.
Not echoing the echo.
Updating cache directory in create_db.sh.
Use python binary to run db upgrade.
Install renderd.conf to /etc on RPi OS.
Fetching DC map from wrolpi.org to avoid broken daily PBFs.
Only get latest dc map if it doesn't exist.
carto switched again??
Changing mod_tile directory to /var/cache/renderd/tiles for RPi OS.
Copying renderd configs for RPi OS.
Installing postgres 13 for RPi OS map.
Changing title of feature matrix.
Moving download link up in readme.
Changing mod_tile to _renderd.
Using unix socket rather than host connection.
Getting openstreetmap-carto as _renderd user.
Assigning ownership of gis db to _renderd in reset_map.sh.
Creating _renderd db user.
Using _renderd to run renderd on debian 11.
Installing python modules that map requires globally.
Upgrading db using venv.
Installing nodejs which installs npm.
Install npm/node after other dependencies.
Updating apt so nodejs can be installed.
Upgrading PyYAML in new venv.
Installing pip via apt.
Including bash binary for portability.
Moving images link out of list.
Moving download up on Readme.
Removing map installation from readme.
Installing specific version of carto.
Creating venv for wrolpi project. Using venv to install requirements and run wrolpi's main.py.
Logging upgrade script.
Including upgrade script in Upgrade readme.
Moving upgrade logic into separate script. This is necessary so and repo update will take immediate effect on the upgrade.
Modifying and unifying Debian installation with RPi OS installation.
2023-02-24 16:16:13 -07:00
Renamed from etc/ubuntu20.04/wrolpi-api.service (Browse further)
