Tobserver/docs/bitcoin-pulse-postgresql.md

# Bitcoin Pulse PostgreSQL operations

## Declared production layout

`modules/bitcoin-pulse.nix` declares PostgreSQL 18 as the rootless
`bitcoin-pulse-postgres` Podman container. The corresponding system service is
`podman-bitcoin-pulse-postgres.service`.

- The official PostgreSQL 18.4 Bookworm image is pinned by immutable digest.
- Podman runs under the dedicated `bitcoin-pulse-postgres` system account.
- The `bitcoin-pulse-db` named volume is mounted at `/var/lib/postgresql`, the
  persistent volume location required by PostgreSQL 18 images.
- Host port 5432 is published only as `127.0.0.1:5432` and is not opened in the
  NixOS firewall.
- Podman reports the service ready only after `pg_isready` succeeds.
- PostgreSQL receives `SIGINT` for a clean fast shutdown, with 60 seconds for
  Podman and 90 seconds for the enclosing systemd service.
- Automatic image updates are disabled. Update the digest only in a reviewed
  change after testing backups, startup, and application compatibility.

The named volume belongs to the rootless Podman account and survives container
removal, service recreation, and NixOS generation changes. Backup portability
is provided by the logical Restic backup described below; do not copy Podman's
private storage directly.

## Human-managed secrets

Before deploying this module, use `sops` from an authorized workstation to add
three independently generated passwords to `secrets/tobserver.yaml`:

```yaml
bitcoin-pulse:
  postgres:
    admin-password: <administrator password>
    migration-password: <migration role password>
    app-password: <application role password>
```

Commit only the resulting encrypted SOPS payload. Never place a password in a
Nix expression, command argument, shell history, log, or pull-request text. The
agent change intentionally declares only the secret paths; it does not modify
or decrypt `secrets/tobserver.yaml`.

## Logical off-server backups

`modules/bitcoin-pulse.nix` declares the daily
`restic-backups-bitcoin-pulse.service` and its systemd timer. The job:

1. Requires the PostgreSQL container service to be available.
2. Creates a consistent custom-format `pg_dump` through the loopback database
   port using the administrator password as a systemd credential.
3. Uploads the dump to the separate Restic repository
   `b2:tobcloud-backup:bitcoin-pulse`.
4. Removes the local staging dump whether the upload succeeds or fails.
5. Retains 7 daily, 4 weekly, and 12 monthly snapshots.

The timer starts the job daily between 03:00 and 03:30 Europe/Berlin time. It is
persistent, so systemd starts a missed job after the next boot. A failed dump,
upload, or prune operation makes the unit fail and leaves details in the system
journal. A run that exceeds six hours is stopped and marked failed so it cannot
block future timer runs indefinitely. Alert delivery and automated restore
testing are intentionally outside this deployment scope.

### Backup credentials

The existing `tobcloud-backup` bucket and its Backblaze application key are
shared with the Nextcloud backup. Before deploying, confirm that the key is not
restricted only to the `nextcloud/` prefix and can also access
`bitcoin-pulse/`. For the native B2 backend, it needs `listBuckets`,
`listFiles`, `readFiles`, `writeFiles`, and `deleteFiles` because Restic must
find the bucket and retention must remove expired repository files.

Generate a separate, strong Restic repository password and retain it in the
approved password manager. Losing this password makes every backup in the
repository unusable.

Use `sops secrets/tobserver.yaml` from an authorized workstation to add:

```yaml
restic:
  bitcoin-pulse-password: <new Restic repository password>
backblaze:
  tobcloud-backup-env: |
    B2_ACCOUNT_ID=<shared application key ID>
    B2_ACCOUNT_KEY=<shared application key>
```

Rename the existing `backblaze/nextcloud-env` SOPS entry to
`backblaze/tobcloud-backup-env`; do not keep a second plaintext copy of the same
credential. Commit only the encrypted result. The NixOS configuration will not
activate successfully until the renamed Backblaze key and the new Restic
password exist.

### Initial backup verification

After adding the credentials and deploying the reviewed NixOS generation, run
the first backup manually rather than waiting for the timer:

```console
sudo systemctl start restic-backups-bitcoin-pulse.service
sudo systemctl status restic-backups-bitcoin-pulse.service
sudo journalctl -u restic-backups-bitcoin-pulse.service -b --no-pager
sudo restic-bitcoin-pulse snapshots
systemctl list-timers restic-backups-bitcoin-pulse.timer
```

The service must exit successfully, `snapshots` must show the new off-server
snapshot, and `/var/backup/bitcoin-pulse` must not retain a dump after the job.
The journal must not contain any credential value.

For PostgreSQL, `sops-nix` materializes each credential as a restricted runtime
file. Before container startup, the service stages private copies owned by
PostgreSQL's uid
inside the rootless user namespace. The copies are mounted read-only and are
removed from the host runtime directory after PostgreSQL reports healthy and
again when the unit stops. The application and migration SOPS files remain
available only through the dedicated groups of their respective host services.

## Empty-database initialization and privileges

On the first start of an empty `bitcoin-pulse-db` volume, the official image
creates:

- `bitcoin_pulse_admin`, the bootstrap PostgreSQL superuser;
- the `bitcoin_pulse` database;
- `bitcoin_pulse_migration`, a non-superuser login that owns the database;
- `bitcoin_pulse_app`, a non-superuser login with `CONNECT` only initially.

The role bootstrap SQL comes from the pinned BitcoinPulse flake input.
Migratus' database-foundation migration then grants the application role schema
usage and default DML privileges for application objects. Later migrations can
further restrict individual tables; the application role cannot create schema
objects, roles, or databases and must never run migrations.

Initialization scripts run only for an empty volume. Changing a SOPS value does
not change a role password in an existing PostgreSQL cluster.

## Migration-gated backend startup

`bitcoin-pulse.service` requires `bitcoin-pulse-migrate.service`, and the
migration service requires the health-gated PostgreSQL container service. The
resulting startup order is:

```text
PostgreSQL healthy -> bounded readiness check -> Migratus -> backend
```

The migration service waits up to 60 seconds for `pg_isready`, then runs the
same packaged backend version's `bitcoin-pulse-migrate migrate` command as the
unprivileged `bitcoin-pulse-migration` host user. It receives only the migration
role and its SOPS password-file path. The backend separately receives the
restricted `bitcoin_pulse_app` role and application password-file path.

The migration unit is a one-shot service without `RemainAfterExit`, so every
new start transaction that pulls it in runs Migratus again. Migratus records
completed versions in `schema_migrations`; already-applied migrations therefore
make repeated starts harmless. A readiness timeout, invalid credential, or
migration error fails the one-shot unit. Because the backend is ordered after
and requires that unit, the backend process is not started after such a failure.
Inspect `journalctl -u bitcoin-pulse-migrate.service` for the bounded readiness
message, Migratus error, and systemd exit status.

## Deployment and verification

Use `bitcoin-pulse-operations.md` for the standard build, switch, and complete
post-deployment procedure. The checks below cover the database-specific
acceptance paths.

After deployment, verify the service and loopback binding:

```console
systemctl status podman-bitcoin-pulse-postgres.service
ss -ltn '( sport = :5432 )'
journalctl -u podman-bitcoin-pulse-postgres.service --since today
```

`ss` must show only `127.0.0.1:5432`. No credential should appear in the unit's
command line or journal.

Connect with `psql -h 127.0.0.1 -U <role> -d bitcoin_pulse`; allow `psql` to
prompt for the password. As the administrator, inspect role attributes with
`\du` and verify:

```sql
SELECT has_database_privilege(
  'bitcoin_pulse_app', 'bitcoin_pulse', 'CONNECT');
SELECT has_database_privilege(
  'bitcoin_pulse_app', 'bitcoin_pulse', 'CREATE');
SELECT has_schema_privilege(
  'bitcoin_pulse_app', 'public', 'CREATE');
```

The expected results are `true`, `false`, and `false`. Verify the application
can perform only the DML allowed by the migrated schema and that only the
migration role can apply DDL.

To test recreation and persistence, create a temporary probe table as the
administrator or migration role, restart the unit, confirm the row remains, and
then drop the probe table:

```sql
CREATE TABLE bit29_recreation_probe (value integer PRIMARY KEY);
INSERT INTO bit29_recreation_probe VALUES (1);
```

```console
systemctl restart podman-bitcoin-pulse-postgres.service
```

```sql
TABLE bit29_recreation_probe;
DROP TABLE bit29_recreation_probe;
```

A normal `systemctl stop` must complete within 90 seconds. Review the journal to
confirm a clean fast PostgreSQL shutdown and no forced kill. Start the unit again
and confirm it becomes healthy. Perform these production checks during a
reviewed maintenance window.

After deploying the migration gate, verify the one-shot and backend:

```console
systemctl is-active podman-bitcoin-pulse-postgres.service
systemctl is-active bitcoin-pulse.service
systemctl show bitcoin-pulse-migrate.service \
  --property=Result --property=ExecMainStatus
journalctl -u bitcoin-pulse-migrate.service -b --no-pager
ss -ltn '( sport = :3000 )'
```

A successful migration unit becomes inactive after it exits; `Result=success`
and `ExecMainStatus=0` are expected. The backend must be active and port 3000
must be bound only to `127.0.0.1`. Restart `bitcoin-pulse.service` once and
confirm the migration unit runs successfully again without reapplying completed
migrations.

Successful and repeated paths may be verified during deployment. Exercise the
database-unavailable and invalid-migration-credential paths only on a disposable
NixOS test host: both must make `bitcoin-pulse-migrate.service` fail, leave
`bitcoin-pulse.service` stopped, finish within the configured timeout, and emit
an actionable journal message. Never inject invalid credentials into production.

## Application compatibility constraints

A full-host NixOS generation rollback is not part of the Bitcoin Pulse
production procedure because it can affect unrelated Tobserver services and it
does not reverse PostgreSQL migrations. Do not use `bitcoin-pulse-migrate
rollback` as an automatic deployment action. Every schema change must remain
compatible with the previously deployed application while that version remains
a possible recovery version. Use an expand/migrate/contract sequence for
breaking changes.

## Credential rotation for an existing database

Rotate one role at a time during a maintenance window. Keep all plaintext in an
approved password manager and interactive prompts.

1. Stop the backend and migration units that use the role being rotated. Leave
   PostgreSQL running.
2. Connect over loopback as `bitcoin_pulse_admin`; enter the current admin
   password at the `psql` prompt.
3. Run `\password bitcoin_pulse_app`, `\password bitcoin_pulse_migration`, or
   `\password bitcoin_pulse_admin` as appropriate. Enter the newly generated
   value only at the hidden prompts.
4. Update the matching value in `secrets/tobserver.yaml` with `sops`, commit only
   the encrypted result, and deploy that reviewed NixOS generation.
5. Restart the affected client unit and verify it connects. For administrator
   rotation, restart PostgreSQL once to verify the synchronized bootstrap file
   still permits normal startup of the existing cluster.
6. Revoke the old value in the password manager and record the rotation date.

If client verification fails, keep the client stopped, reconnect as the
administrator, and correct either the database role or encrypted runtime value.
Do not reset or remove `bitcoin-pulse-db`: first-start initialization is not a
credential-rotation mechanism.