rt/wrolpi
1
0
Fork 0
mirror of https://github.com/lrnselfreliance/wrolpi.git synced 2026-10-25 18:40:06 -04:00
Code Issues Projects Releases Packages Wiki Activity Actions 5
wrolpi/docker/README.md
Roland d9303a0ea8
Postgres -> SQLite hard cutover: the database moves into the media directory. 
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>
2026-07-12 14:09:37 -06:00

4.5 KiB
Raw Permalink Blame History

WROLPi Docker

Development and testing environment using Docker containers.

Quick Start

git clone https://github.com/lrnselfreliance/wrolpi.git
cd wrolpi
git submodule update --init
docker-compose build --parallel
docker volume create --name=openstreetmap-data
docker volume create --name=openstreetmap-rendered-tiles
docker-compose run --rm api db upgrade
docker-compose up

Browse to https://localhost:8443

Services

Service Port(s) Description
web 8443 (HTTPS), 8084-8086 Caddy reverse proxy
api 8081 Sanic API server
app (via web) React frontend
controller 8080 System management (FastAPI)
archive 8083 SingleFile/Readability
zim (via web:8085) Kiwix
help (via web:8086) MkDocs documentation
map (via web:8084) OpenStreetMap tile server

TLS Certificates

WROLPi uses a two-tier PKI for HTTPS. A root CA is generated once and persists in the media directory. A leaf certificate is regenerated on every container start to pick up hostname and IP changes.

How it works

  1. On first start, the web container generates a root CA at /opt/media/config/ssl/.
  2. A leaf cert signed by the root CA is created at /etc/ssl/caddy/ inside the container.
  3. Caddy serves HTTPS on ports 443, 8084, 8085, and 8086 using the leaf cert.
  4. The Controller serves on port 80 with a certificate trust banner when needed.

Trusting the certificate

  1. Browse to http://<host-ip> to see the Controller. If the HTTPS certificate is not yet trusted, a banner will appear with the download link and platform-specific trust instructions.
  2. Download and install the root CA on your device.
  3. All HTTPS ports will work without browser warnings.

The root CA only needs to be trusted once. Leaf certs can be regenerated (hostname change, container rebuild) without needing to re-trust.

Name Constraints

The root CA is restricted to local/private networks only via X.509 Name Constraints. Even if the CA key were compromised, it cannot sign certificates for public domains.

Permitted:

  • DNS: *.local, localhost
  • IPv4: 127.0.0.0/8, 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16
  • IPv6: ::1, fc00::/7 (unique local), fe80::/10 (link-local)

EXTRA_SANS

The Docker container cannot discover the host's real LAN IP. Set EXTRA_SANS in a .env file so the leaf certificate includes your host's IP and hostname:

# .env
EXTRA_SANS=IP:10.0.0.5,DNS:myhost.local

Each entry must have a type prefix (IP: or DNS:). Multiple entries are comma-separated. After changing EXTRA_SANS, restart the web container:

docker compose up -d --build web

Regenerating the root CA

If you need to regenerate the root CA (e.g. after updating the certificate generation script), delete the old CA files and rebuild:

rm test/config/ssl/ca.crt test/config/ssl/ca.key
docker compose up -d --build web

All devices will need to re-download and re-trust the new CA from http://<host-ip>/ca.crt.

Volumes

The test/ directory is mounted to /opt/media inside containers, serving as the media directory for development. Certificate files, configs, and downloaded content are stored here.

Two external volumes are required for the map service:

docker volume create --name=openstreetmap-data
docker volume create --name=openstreetmap-rendered-tiles

Environment Variables

Variable Default Description
MEDIA_DIRECTORY /opt/media Media directory inside containers
EXTRA_SANS (empty) Additional SANs for the TLS leaf certificate
WEB_HOST 0.0.0.0 Host bind address for the web service
WEB_HTTPS_PORT 8443 HTTPS port
REACT_APP_API 0.0.0.0:8081 API bind address
UID 1000 User ID for api/app containers
GID 1000 Group ID for api/app containers

Upgrading

See UPGRADE.md for upgrade instructions.