# Shared Eve task storage

The live board and document catalog use PostgreSQL 16 on `46.62.222.247`,
cluster `eve`, port `5433`, database `eve`. The `eve_board` and `eve_documents`
schemas hold ordinary relational tables. PostgreSQL owns current task state;
Markdown trackers and generated board views in Git are historical projections.
SQLite is retained only for disposable search caches, tests, and reading
historical Git versions.

## Worker setup

For agents on another server, use the [authenticated worker API](WORKER-API.md).
It preserves `./eve` commands over HTTPS with one revocable key per worker and
keeps PostgreSQL credentials on this host. The direct connection instructions
below remain for authorized operators and server-side maintenance.

Run `pnpm --filter @oshun/eve-task-board install --frozen-lockfile`. This
checkout selects PostgreSQL through `.eve-storage.json`; it refuses missing
credentials or an unavailable database instead of silently creating another
board.

Credentials are read from `EVE_DATABASE_URL` or
`~/.config/oshun/eve-postgres.json` (mode `0600`), whose `connectionString`
property is a PostgreSQL URL. `EVE_DATABASE_CONFIG` overrides that filename.
Obtain this file through the existing authorized SSH access; never put it in
Git, paste its content into a task, or print it in CI logs.

PostgreSQL listens on loopback. Authorized operators can use an SSH tunnel for
maintenance; remote coding agents use the worker API:

```sh
ssh -N -o ExitOnForwardFailure=yes -o ServerAliveInterval=30 \
  -L 5433:127.0.0.1:5433 root@46.62.222.247
```

Use a different local port in both the tunnel and credential file if `5433` is
already occupied. Then use the existing `./eve next`, `claim`, `show`, `done`,
`park`, `docs`, and `board` commands. Task claims are serialized on a PostgreSQL
advisory lock, including the reads that validate a claim. Transaction rollback
preserves the journal and task rows together. Expired or superseded fencing
tokens remain invalid across workers.

After task-authority cutover, task edits and completion update PostgreSQL and
its journal directly. Do not edit tracker checkboxes, regenerate a Markdown
board, or import historical tracker state back into Eve. `./eve board`,
`./eve files`, and `./eve check` report current database state. Task definitions
use the authoring API with revision checks, and leased changes require the
worker's fencing token. Claims and closures work from implementation branches.

The BFF reads through `OSHUN_EVE_BOARD_DATABASE_URL`. Its board tools use
asynchronous queries in a PostgreSQL transaction explicitly marked read-only.
They must receive a database role with SELECT access, not the CLI writer role.

## Tested UAT content snapshots

Dev is the authoring workspace. With publication deployment enabled, UAT uses an
explicitly promoted, tested publication and reads the single canonical task
board. The [migration ledger](content-migration.json) records activation and the
first live snapshot separately from implementation verification.

Promotion stages a fresh `eve_content_uat_<publication-id>` database. Its
documents, original sources, history and relationships come from the pinned
revisions; newer Dev drafts, users and sessions are excluded. UAT has a separate
operator identity and private database credentials. PostgreSQL grants content
reads, with writes restricted to authentication and preferences tables. Payload
uses a complete auth-row upsert to persist login sessions; account creation and
role changes still fail through the UAT API. Provisioning also checks effective
database grants, including inherited permissions, before activating the service.
The dashboard and native, human and worker APIs all refuse content changes.

Publication media is copied to `eve/content/uat/media/` in the established
bucket. Both primary and recovery objects must pass byte/checksum verification.
A failed transfer keeps the database and private evidence for a verified retry;
it never marks the snapshot ready or overwrites an immutable object.

CI retains the tested CMS runtime and a checksum manifest under
`ci/cms-builds/<commit>`, independently of disposable source checkouts. UAT
installs that retained build and verifies the exact publication identity,
document revision, media and read-only task permissions through HTTPS before
recording success. Its systemd unit references immutable credentials so a
rollback restores the previous database together with its code and publication.
The service starts at boot and restarts on failure.

`provision-content-snapshot.py` prepares snapshots under the deployment lock;
`provision-content.py --environment uat` requires both a verified snapshot
`--config` and retained `--build`. Normal operation goes through the platform
promotion controller, which preserves the previous deployment on failure.

## Provisioning and backups

```sh
apt-get install --no-install-recommends postgresql-16 pgbackrest
python3 infra/hetzner/eve/bootstrap-postgres.py \
  --secrets-file /root/Desktop/workspace/drawer/env-master.env
node tools/eve-task-board/migrate-postgres.mjs --root /path/to/pre-cutover-checkout
python3 infra/hetzner/eve/provision-reader.py
systemctl start eve-postgres-full.service
python3 infra/hetzner/eve/restore-drill.py
```

The migration checks every row against the source SQLite databases using SHA-256
hashes, preserving keys, leases, fencing tokens, dependencies, and journals. A
populated target is verified by default, never overwritten. Before cutover,
`--replace-matching-source /path/to/preserved-original` can incorporate a newer
SQLite source only if every PostgreSQL row still matches that preserved
original. Any intervening PostgreSQL write refuses the refresh. This option is
disabled once the checkout has selected PostgreSQL. The legacy logical schema
versions remain 3 and 1; PostgreSQL storage migrations have their own
`storage_schema` version.

pgBackRest writes to `s3://app-dev-server/eve/postgresql/` through the HTTPS
endpoint `hel1.your-objectstorage.com`. Existing bucket prefixes are untouched.

- WAL archiving runs continuously, with a 60-second archive timeout. Recovery
  exposure is approximately that interval plus upload time; this is not
  synchronous replication or a zero-loss promise.
- Full backups run Sundays at 02:00 UTC; differential backups run the other
  days. Timers catch up after downtime. Failed backups retry every fifteen
  minutes until they succeed, including after temporary object-storage errors.
  Retention is four full backups and fourteen differential backups, with their
  required WAL managed by pgBackRest.
- An archive check runs every five minutes. Failed checks and backups are
  visible in the journal. Inspect backup service status for pending automatic
  retries; failed archive checks also appear in `systemctl --failed`.
- PostgreSQL starts at boot and restarts on failure. Its unit waits for the
  volume containing `/mnt/volume300/eve/postgresql/16`.
- The restore drill downloads a backup into a temporary cluster, waits for
  recovery and promotion, verifies task/catalog data, and removes only its own
  temporary directory. It never replaces or stops the live cluster. Content
  restore checks include every imported source checksum, current and archived
  revisions, task-source bindings, media aliases and publication body pins.
  Historical pins without a serialized-body checksum are reported separately.
  Add `--all-media --media-report /private/new-media-report.json` to verify
  every registered media import and its recovery objects, including later
  imports. The drill requires every restored object and alias to belong to a
  verified import; uncovered uploads are reported as a failure, never silently
  skipped. The aggregate report counts shared objects once and retains
  individual import proofs beside it. `--media-manifest-hash` still supports a
  single import check.

Useful checks:

```sh
systemctl is-enabled postgresql@16-eve.service
systemctl list-timers 'eve-postgres-*'
runuser -u postgres -- pgbackrest --config=/etc/pgbackrest/eve.conf --stanza=eve info
journalctl -u eve-postgres-full.service -u eve-postgres-diff.service
```

The pgBackRest configuration contains bucket credentials and is root-owned,
group-readable only by PostgreSQL (`0640`). The original drawer file remains
`0600`. Recovery on another host requires the bucket credentials independently
of the failed server. Backups protect data; a single host still has downtime
when it is rebooting or unavailable.
