# Data Models and Schema

## Storage Backend

- Database engine: SQLite.
- Default file path: `./data/dashboard.db`.
- Effective path is controlled by `DB_PATH`.

Related references:

- Configuration values: `doc/environment.md`
- Persistence implementation: `app/db.py`

## Tables

### `node_settings`

Single-row settings table used as the source of truth for RPC and SSH connectivity.

Schema fields:

- `id INTEGER PRIMARY KEY CHECK (id = 1)`
- `rpc_url TEXT NOT NULL`
- `rpc_username TEXT NOT NULL`
- `rpc_password TEXT NOT NULL`
- `rpc_wallet TEXT NOT NULL`
- `config_path TEXT NOT NULL`
- `ssh_host TEXT NOT NULL`
- `ssh_port INTEGER NOT NULL`
- `ssh_username TEXT NOT NULL`
- `ssh_password TEXT NOT NULL`
- `ssh_key_path TEXT NOT NULL`
- `bitcoin_binary TEXT NOT NULL`
- `updated_at TEXT NOT NULL` (UTC ISO 8601 string)

Invariants:

- Exactly one logical settings record exists with `id = 1`.
- `save_settings` performs an upsert.
- Missing settings row is represented by in-code defaults.

### `metrics_history`

Time-series table for chart data and historical dashboard views.

Schema fields:

- `ts INTEGER PRIMARY KEY` (Unix timestamp, seconds)
- `blocks INTEGER NOT NULL`
- `headers INTEGER NOT NULL`
- `mempool_bytes INTEGER NOT NULL`
- `peers INTEGER NOT NULL`

Index:

- `idx_metrics_history_ts` on `ts`

Invariants:

- A timestamp has at most one row.
- Inserts on duplicate timestamp overwrite existing point.
- Returned history is chronological for UI consumption.

## Retention and Limits

Retention behavior is enforced during each metric insert:

- Time retention: rows older than 30 days are deleted.
- Count retention: rows beyond the newest 20000 samples are deleted.
- Query limit input is clamped to `[1, 20000]`.

## Metric Sampling Sources

A metric point is composed from RPC responses:

- `blocks` and `headers` from `getblockchaininfo`
- `mempool_bytes` from `getmempoolinfo.bytes`
- `peers` from `getnetworkinfo.connections`

Points are written:

- On each `/api/dashboard/summary` request.
- In a background sampler loop at configured interval.

## Settings Defaults

Default values used when no persisted row exists:

- `rpc_url`: `http://127.0.0.1:8332`
- `rpc_username`: empty string
- `rpc_password`: empty string
- `rpc_wallet`: empty string
- `config_path`: empty string
- `ssh_host`: empty string
- `ssh_port`: `22`
- `ssh_username`: empty string
- `ssh_password`: empty string
- `ssh_key_path`: empty string
- `bitcoin_binary`: `bitcoind`
- `updated_at`: empty string

## Data Consumers

- `/api/settings` and `/api/settings` (PUT) read and write `node_settings`.
- `/api/dashboard/history*` reads `metrics_history`.
- `/api/dashboard/summary` and sampler thread write `metrics_history`.

See `doc/api.md` for endpoint-level contracts.