HM_project_Viewer_Board/docs/DOCKER_SQLITE_MIGRATION.md

Docker and SQLite Migration

Goal

Move the application to a reproducible Docker runtime with SQLite 3.53.1, while keeping the live database safe and preparing to separate rebuildable comparison caches from durable business data.

Current Constraints

• The live database is large and active. Do not copy data.db while assuming the WAL file can be ignored.
• WEHAGO browser automation currently depends on the Windows Chrome debugging session. It remains outside the app container during the first migration.
• SQLite WAL databases must remain on the WSL Linux filesystem. Do not place the runtime DB on /mnt/c, /mnt/d, a network share, or a synchronized folder.
• Only one app container may write the SQLite database in the initial Docker rollout.

Phase 1: Safe Runtime Foundation

Implemented in the application:

• Runtime paths can be supplied through environment variables.
• Docker requires SQLite >= 3.51.3 and builds SQLite 3.53.1.
• Heavy WEHAGO query-cache generation is blocked when WAL is at least 512MB.
• Docker starts with HM_APP_MAINTENANCE_ENABLED=0 so the legacy single-transaction cache cleanup does not lock new snapshot work; run cleanup only in a maintenance window until cache tables are separated.
• scripts/sqlite_runtime_admin.py provides read-only status, progress-reporting backup with selectable verification, and explicitly acknowledged checkpoint commands.

Check the existing database without changing it:

.venv/bin/python scripts/sqlite_runtime_admin.py status --include-counts

Phase 2: Install Docker Desktop

On Windows, install Docker Desktop and enable:

1. Use the WSL 2 based engine.
2. WSL integration for the Ubuntu distribution containing this repository.

After installation, inside WSL confirm:

docker version
docker compose version

Phase 3: Create an Isolated Trial Runtime

Do not connect the first container run to the live DB.

mkdir -p /home/b17301/intranet-runtime/{db,cache,backups,exports}
.venv/bin/python scripts/sqlite_runtime_admin.py backup \
  --output /home/b17301/intranet-runtime/db/data.db \
  --verify smoke
cp .env.docker.example .env
# APP_UID/APP_GID는 WSL에서 `id -u` / `id -g` 결과와 맞춰야 합니다.
docker compose build intranet-app
docker compose run --rm intranet-app python -c \
  "import sqlite3; print(sqlite3.sqlite_version); assert sqlite3.sqlite_version_info >= (3, 51, 3)"
docker compose up -d intranet-app

Verify the trial application on http://127.0.0.1:8010 only after stopping the existing local server or publishing the container on a different port. smoke verifies that the copied DB opens and its schema is readable; schedule --verify quick or --verify full separately because this cache-heavy DB makes even quick_check a long maintenance operation.

Trial validation checklist:

curl -sS http://127.0.0.1:8010/health
docker compose exec intranet-app python scripts/sqlite_runtime_admin.py status --include-counts
docker compose exec intranet-app python scripts/sqlite_runtime_admin.py cache-retention-report

The status output should show SQLite 3.53.1, no WAL warning, and the expected runtime DB path under /runtime/db. The cache retention report shows whether old export-row cache signatures are still occupying the DB. It is diagnostic by default.

The application loads .env itself through python-dotenv, so local Python commands and VS Code terminal sessions do not need VS Code terminal environment injection to be enabled. .env is ignored by Git and excluded from Docker image build context; keep secrets and machine-specific paths there, not in committed files.

Phase 4: Maintenance Cutover

Schedule a maintenance window before operating on the live database:

1. Stop the existing app and all background cache jobs.
2. Create a final SQLite backup.
3. Run a checkpoint only while the app is stopped.
4. Validate the copied DB under Docker.
5. Start only the Docker app service.
6. Preserve the old live database for rollback.

Example commands after the server is stopped:

.venv/bin/python scripts/sqlite_runtime_admin.py backup \
  --output /home/b17301/intranet-runtime/db/data.db \
  --verify full
.venv/bin/python scripts/sqlite_runtime_admin.py checkpoint \
  --mode truncate --ack-maintenance-window
docker compose up -d intranet-app

Phase 5: Cache Database Separation

This is the next application migration after Docker trial validation.

Planned storage layout:

/runtime/db/core.db
  Durable project data, review decisions, manual pairings, settings

/runtime/db/source.db
  Imported WEHAGO/Hanmac source rows and source metadata

/runtime/cache/compare/<year>/<logic_signature>/projection.db
  Rebuildable query projection and export rows

/runtime/cache/candidate/<year>/<logic_signature>/candidate.db
  Rebuildable raw ERP match candidates

The application must switch projection files only after a complete build and verification. Old logic signatures must never be served as current results. Cache files can then be removed by retention policy instead of deleting millions of rows from the durable DB.

The first separation target should be the query projection/export-row cache, because it is rebuilt by year/range and already has a logic signature. The second target should be raw ERP trace candidates. Durable review decisions, manual matches, source imports, and configuration should stay in the core DB.

Until the cache DB split is complete, use the retention report first and delete only rebuildable orphan export-row cache in a maintenance window:

docker compose exec intranet-app python scripts/sqlite_runtime_admin.py prune-orphan-export-cache
docker compose exec intranet-app python scripts/sqlite_runtime_admin.py prune-orphan-export-cache \
  --execute --ack-delete-rebuildable-cache

The first command is a dry run. The second command deletes only export-row cache whose (year, snapshot_signature) no longer matches a ready snapshot. Follow it with a checkpoint only while the app is stopped if the WAL/database file needs to be compacted.

Query projections are also rebuildable and can become the largest cache tables. Keep the newest projection signatures per range and prune older ones during a maintenance window:

docker compose exec intranet-app python scripts/sqlite_runtime_admin.py query-retention-report --keep 2
docker compose exec intranet-app python scripts/sqlite_runtime_admin.py prune-old-query-projections --keep 2
docker compose exec intranet-app python scripts/sqlite_runtime_admin.py prune-old-query-projections \
  --keep 2 --execute --ack-delete-rebuildable-cache

Daily Startup

Docker Desktop must be running for the containerized app. Visual Studio Code is only an editor; opening VS Code alone does not start the Docker engine or the app container.

Recommended daily workflow after Docker cutover:

1. Start Docker Desktop, or configure Docker Desktop to start on Windows login.
2. Open VS Code for editing.
3. Confirm the app container is running:

docker compose ps
curl -sS http://127.0.0.1:8010/health

Because compose.yaml uses restart: unless-stopped, the app container normally starts again when Docker Desktop starts. If you explicitly stop the container, run docker compose up -d intranet-app before using the app.