# SQLite Storage (https://portunus.bybee.dev/en/docs/features/sqlite-storage)



Available since v0.8.0. Collapses every server-side persistent JSON
file (`tokens.json`, `identity.json`, `rules.json`) and the in-memory
audit ring buffer into one embedded SQLite database at
`<data-dir>/state.db`.

## Why SQLite [#why-sqlite]

* **Single-binary deployment unchanged** — `rusqlite` with the
  `bundled` feature statically links a pinned SQLite. No system
  libsqlite3 dependency.
* **Crash-safe** — WAL mode + `BEGIN IMMEDIATE` write transactions.
* **Migrations** — managed by `refinery`; schema lives at
  `crates/portunus-server/src/store/migrations/`.

## Tables [#tables]

Representative tables (schema evolves via migration; see
`crates/portunus-server/src/store/migrations/`):

| Table                                                | Holds                                                     |
| ---------------------------------------------------- | --------------------------------------------------------- |
| `users`                                              | RBAC users (id, role, display name, disabled flag)        |
| `credentials`                                        | Bearer-token hashes (blake3) + status                     |
| `grants`                                             | Per-user `(client, listen-port range, protocols)` triples |
| `rules`                                              | Pushed rules (with v0.11 nullable cap columns)            |
| `rule_targets`                                       | Multi-target rule entries (v0.7+)                         |
| `audit`                                              | Durable audit log (replaces the v0.6 in-memory ring)      |
| `rate_limit_owner`                                   | Per-owner cap envelopes (v0.11+)                          |
| `traffic_quotas`, `traffic_samples_*`                | Per-`(user, client)` monthly usage + history (v0.13+)     |
| `web_sessions`, `login_attempts`, `onboarding_setup` | Local password auth + first-run setup token (v1.1+)       |
| `client_enrollments`                                 | One-time client enrollment codes                          |

## Path layout [#path-layout]

XDG-aligned default data-dir:

```
$XDG_STATE_HOME/portunus/    (or ~/.local/state/portunus/)
  server.toml          (optional — overrides built-in defaults)
  server.crt
  server.key
  state.db
  state.db-shm         (present while server running)
  state.db-wal         (present iff WAL frames pending)
```

Override with `--data-dir`. Production deployments typically use
`/var/lib/portunus/`.

## Filesystem checks [#filesystem-checks]

The server refuses to start with `event=startup.unsupported_filesystem`
if `--data-dir` points at NFS, tmpfs, or ramfs. Move the data-dir to a
**local writable filesystem**.

It also refuses two `portunus-server serve` processes against the same
data-dir (`event=startup.store_in_use`, exit 75) — clustering is out of
scope.

## Backup [#backup]

Online backup via `rusqlite::backup::Backup` — WAL-aware, no quiescing
needed:

```sh
portunus-server backup --out ~/portunus-backup-$(date +%F).db
```

Refuses to overwrite an existing destination.

## Restore [#restore]

```sh
portunus-server reset --confirm     # wipe local state first
portunus-server restore --in ~/portunus-backup-2026-05-08.db
portunus-server serve               # schema migrations run automatically
```

`restore` validates the SQLite header magic and refuses to clobber a
non-empty data dir without `--force`. Backups whose schema is **newer**
than the binary's target version exit `78` with
`startup.schema_version_too_new` — restore on the matching binary
version, or upgrade.

## Reset [#reset]

```sh
portunus-server reset --confirm
# wipes state.db + state.db-wal + state.db-shm (+ state.db.lock)
# refuses any path whose first 16 bytes don't match the SQLite header
```

The header check protects against a typo'd `--data-dir`. `reset` leaves
`server.crt` / `server.key` / `server.toml` in the same directory
untouched. Without `--confirm` it is a dry run that prints the path it
would remove.

## Audit prune [#audit-prune]

The audit table is durable, not ring-buffered. Bound its size with the
prune CLI:

```sh
# Dry run — count without deleting
portunus-server audit prune --before 2026-05-01T00:00:00Z --dry-run

# Real prune — deletes under BEGIN IMMEDIATE, then PRAGMA incremental_vacuum
portunus-server audit prune --before 2026-05-01T00:00:00Z
```

Run it from cron (daily / weekly) to keep the database bounded.

## Audit envelope mode [#audit-envelope-mode]

`GET /v1/audit?since=<RFC3339>&until=<RFC3339>&cursor=<base64>&limit=N`
returns `{ entries, next_cursor?, count }` instead of the v0.7 array
root, enabling cursor-based historic scroll-back.

v0.7 callers (no `since` / `until` / `cursor`) keep getting the array
root unchanged — byte-stable.

## `portunus-client --bundle` resolution [#portunus-client---bundle-resolution]

When `--bundle` is omitted the client searches in order:

1. `$PORTUNUS_CLIENT_BUNDLE`
2. `$XDG_CONFIG_HOME/portunus/client.bundle.json`
3. `$HOME/.config/portunus/client.bundle.json`
4. `./client.bundle.json`

Exits `1` listing every attempted path when none resolve.