# Audit DB schema — changelog

The audit log lives at `~/.kvendra/audit.db` (SQLite, WAL). Every row is
HMAC-chained: each row's `hmac_hex` commits to the canonical input plus
the previous row's HMAC. Verification is `kvendra audit --verify`.

Schema evolution is tracked in a dedicated `schema_migrations` table.
Migrations are idempotent and applied automatically on every process
startup via [`crate::audit::schema::init`].

---

## v1 — baseline (`0.1.0`)

Columns:

```
id              INTEGER PRIMARY KEY AUTOINCREMENT
ts_unix_ms      INTEGER NOT NULL
profile_id      TEXT NOT NULL
primitive       TEXT NOT NULL
action          TEXT NOT NULL
args_hash_hex   TEXT NOT NULL          -- sha256 of canonical args JSON
status          TEXT NOT NULL          -- started | ok | error
severity        TEXT NOT NULL          -- info | warn | error
flags           TEXT NOT NULL DEFAULT ''
prev_hmac_hex   TEXT NOT NULL DEFAULT ''
hmac_hex        TEXT NOT NULL
```

HMAC canonical input (v1 layout):

```
id (BE i64) | ts (BE i64) | profile_id | primitive | action |
args_hash | status | severity | flags | prev_hmac
```

(Each `|` is a literal one-byte separator.)

Indices: `idx_audit_ts`, `idx_audit_profile`.

---

## v2 — workspace correlation (`0.3.0-alpha.1`)

Released: 2026-05-13 (REQ-KVD-CLI-010, M1 Sprint 4).

### Columns added

```
remote_audit_id  TEXT NULL              -- ULID returned by `tokens:issue`
hmac_version     INTEGER NOT NULL DEFAULT 1
```

`remote_audit_id` is populated only when the row was generated by the
`RemoteBrokerResolver`. Local rows leave it `NULL`.

`hmac_version` records the HMAC layout under which the row was signed.
Pre-migration rows default to `1`; new rows post-migration write `2`.

### Index added

```
idx_audit_remote_id ON audit_events(remote_audit_id)
```

### HMAC canonical input (v2 layout)

Identical to v1 with one trailing block appended:

```
… | prev_hmac | remote_audit_id?
```

`remote_audit_id` is canonicalised to the empty string when `NULL`. This
means a v2 row with `remote_audit_id = NULL` does NOT match the v1 hash
of the same byte-content row — the trailing `|` + empty string adds one
byte to the HMAC input. `verify_chain` selects v1 vs v2 per row using
the `hmac_version` column.

### Ledger row

```
INSERT INTO schema_migrations (version, applied_at) VALUES (2, <RFC3339 now>);
```

### Why HMAC versioning (not chain rewrite)

The alternative — re-signing all historical rows under v2 — would
invalidate every prior cryptographic verification, breaking the
"tampering should fail" invariant. HMAC versioning is the
industry-standard pattern (HashiCorp Vault, GitHub audit log) for
schema upgrades of immutable HMAC chains.

### Baseline detection

A DB created by an older binary may not have a `schema_migrations` row
even though the v2 columns exist (e.g. an external tool ran the ALTER
manually). In that case `apply_pending` records `v1` + `v2` directly
without re-running the ALTER (which would fail on column collision).

---

## Caveats

- **SQLite `ALTER TABLE` limitations.** `ADD COLUMN` with `DEFAULT NULL`
  is safe and atomic; `DROP COLUMN` would require a full table rebuild
  and is intentionally not supported here.
- **Row-level integrity.** A `--verify` failure pinpoints the broken
  row id so forensic tooling can correlate with the surrounding rows.
- **Status updates** (`Started → Ok|Error`) re-hash the affected row.
  The dispatch path picks v1 or v2 based on the row's `hmac_version`
  column, so updates do not promote a v1 row to v2.

---

## Reading the log

```bash
# Tail human-readable.
kvendra audit --json | jq '.[-20:]'

# Workspace-mode rows (with broker correlation).
sqlite3 ~/.kvendra/audit.db \
  "SELECT id, ts_unix_ms, action, remote_audit_id
   FROM audit_events
   WHERE remote_audit_id IS NOT NULL
   ORDER BY id DESC LIMIT 10"

# Migration history.
sqlite3 ~/.kvendra/audit.db \
  "SELECT version, applied_at FROM schema_migrations ORDER BY version"
```
