docs/adr/0022-backup-and-operations.md
Rama master · réplica de lectura del repositorio original.
# ADR-0022: Database Backup Strategy and Production Operations
## Metadata
| Field | Value |
|-------|-------|
| **Status** | Accepted |
| **Date** | 2026-06-13 |
| **Authors** | AlessioBrillo |
| **Deciders** | AlessioBrillo |
| **Supersedes** | N/A |
| **Relates to** | ADR-0001, ADR-0005 |
| **Project** | DOMINUS |
## Context
DOMINUS stores all persistent state in a single SQLite file (`dominus.db`). As
the portfolio grows and the backtest history accumulates, this database
becomes the single most critical asset in the system:
- All pipeline runs, scoring history, and candidate data
- Portfolio entries, renewal dates, acquisition costs
- Trademark cache and provider cache (rate-limited API responses)
- Backtest signals and weight snapshots (the calibration loop)
Despite this criticality, v0.2.0 had **zero backup automation**:
1. No scheduled backup job existed in the scheduler.
2. No automated pre-migration snapshot was taken before schema changes.
3. No integrity verification was run periodically.
4. The operator had to manually `cp` the database file, which produces an
inconsistent copy when WAL mode is active (the copy may miss uncheckpointed
WAL pages or include partially-written transactions).
Concurrently, the GitHub Actions CI minutes were exhausted, making the remote
quality gate unavailable. The project needed a local fallback that runs the
same checks (typecheck, lint, test, build) before every push, enforced by a
pre-push git hook that was already present but under-documented.
## Decision Drivers
1. **Consistency** — Backups must be transaction-consistent. A file-level copy
of a SQLite database in WAL mode is NOT consistent. `VACUUM INTO` is the
only safe mechanism for hot backups in single-user SQLite.
2. **Automation** — Backups must run on a schedule without operator
intervention. The scheduler (ADR-0021) is the natural home for this.
3. **Retention discipline** — Unlimited backups consume disk space silently.
A retention policy with automatic pruning is mandatory.
4. **Local CI gate** — Without GitHub Actions, the pre-push hook must run the
full quality suite (typecheck, build, lint, format, test) and block the
push on failure.
5. **Observability** — The operator must be able to list, inspect, and
manually trigger backups and vacuum operations from the CLI.
## Considered Options
### Option A: `VACUUM INTO` + retention pruning + scheduler job (CHOSEN)
A dedicated `BackupService` class that:
- Calls `PRAGMA wal_checkpoint(TRUNCATE)` to flush the WAL
- Runs `VACUUM INTO '<path>'` to create a transaction-consistent snapshot
- Prunes backups older than `BACKUP_RETENTION_DAYS` (default: 30)
- Is wired into the scheduler as a daily job (cron: `0 4 * * *`)
- Is available from the CLI via `dominus maintenance backup` and
`dominus maintenance vacuum`
**Advantages:**
- `VACUUM INTO` produces a fully consistent, compacted copy — even if the
source database has accumulated free pages from deletions, the backup
contains only live pages
- The backup is a standalone SQLite file; no external tools required
- No downtime — `VACUUM INTO` is atomic in single-user mode
- Scheduler integration means the operator never has to remember
- Retention pruning prevents unbounded disk growth
**Disadvantages:**
- `VACUUM INTO` requires free disk space equal to the source database size
(transient; the source is unmodified)
- Daily backup of a mostly-static database is wasteful (accept: small scale)
**Risk Assessment:** Low. `VACUUM INTO` is a mature SQLite feature.
Single-user mode guarantees no concurrent writers during the backup.
### Option B: `.backup` command via `sqlite3` CLI
Use the SQLite `.backup` command or the `backup_api` C API.
**Advantages:**
- Incremental backup support via `sqlite3_backup_step()`
- Online backup without blocking readers
**Disadvantages:**
- Requires shelling out to the `sqlite3` binary
- The `better-sqlite3` Node.js bindings expose `backup()` but it uses
callbacks and is more complex to integrate into the scheduler
- `VACUUM INTO` produces a smaller output (no free pages)
**Risk Assessment:** Low, but more complex to wire into Node.js async flow.
### Option C: File-level copy with WAL checkpoint
Simply copy the database file after `PRAGMA wal_checkpoint(TRUNCATE)`.
**Advantages:**
- Simple. One copy command.
**Disadvantages:**
- NOT consistent if a write transaction commits between the checkpoint and
the copy (though single-user mode mitigates this)
- The copy contains free pages; no compaction
- Does not detect corruption in the source file
**Risk Assessment:** Medium. In single-user mode the window is narrow, but
the copy is provably unsafe if any async write completes between the
checkpoint and the copy.
## Decision
**Chosen option: Option A — `VACUUM INTO` + retention pruning + scheduler job**
We accept the transient disk space cost in exchange for absolute consistency
guarantees and zero operational overhead. The `BackupService` is a single
class (~100 lines) that integrates cleanly with the existing scheduler
(ADR-0021) and CLI infrastructure.
The local CI gate is implemented as a `.husky/pre-push` hook that runs
`npm run prepush`, which executes `ci:backend` (typecheck → build → lint →
format → test with coverage). This was already partially present but not
enforced or documented as the primary quality gate.
## Consequences
### Positive
- **Consistent backups**: Every backup is a transaction-consistent snapshot
via `VACUUM INTO`. Safe for restore at any point.
- **Zero-touch operations**: Daily backup + prune is fully automated via the
scheduler. The operator never has to remember.
- **CLI visibility**: `dominus maintenance backup --list`, `dominus
maintenance vacuum`, and `dominus maintenance prune` give the operator
full control.
- **Local CI**: The pre-push hook catches type/lint/test failures before
they reach the remote, compensating for the exhausted GitHub Actions
minutes.
- **Integrity verification**: `dominus maintenance vacuum` runs
`PRAGMA integrity_check` before vacuuming, catching corruption early.
### Negative
- **Disk space**: Each backup is a full copy of the database. At current
scale (MBs), this is irrelevant. If the database grows to GBs, consider
switching to incremental backup or reducing retention.
- **WAL checkpoint side-effect**: Running `PRAGMA wal_checkpoint(TRUNCATE)`
during the backup job forces a WAL flush, which may cause a brief
write stall. In single-user mode this is imperceptible.
### Compliance and Security Implications
- Backup files may contain domain names, portfolio entries, and trademark
data. The .gitignore already excludes `data/` — no backup file can be
committed. The operator is responsible for securing the backup directory.
- No credentials or API keys are stored in the database; all secrets live
in environment variables or file-based config.
### Migration Plan
- **No schema migration required.** The backup is purely a file-system
operation. The `BackupService` creates `./data/backup/` on first run.
- A new migration (`0017`) is NOT required — the scheduler job is registered
dynamically based on `BackupService` availability, not from the
`scheduler_jobs` table. Adding a static migration would be beneficial for
persistence of the job metadata but is deferred (low priority).
## Validation
1. **Unit tests**: `src/scheduler/__tests__/backup-service.test.ts` covers
backup creation, pruning, listing, and edge cases (empty dir, missing dir,
corrupted file names).
2. **Integration**: Run `dominus maintenance backup` with a test database,
verify the output file opens with `PRAGMA integrity_check`.
3. **Scheduler**: Enable `SCHEDULER_ENABLED=true`, wait for the backup cron
(or use `dominus scheduler run-once backup`), verify backup appears in
`./data/backup/`.
4. **CI gate**: Make a deliberate type error, commit, try to push — verify
the pre-push hook rejects the push.
---
*This ADR was created following the MADR 4.0.0 standard. All DOMINUS ADRs
should be consistent with the ADR series starting at `docs/adr/0001-project-architecture.md`.*