# Storage

Where nikcli keeps persistent data: one SQLite database, the repository layer above it, migrations, and the files that stay on disk.

## Overview

All durable runtime state lives in a single SQLite database — `nikcli.db` under `Global.Path.data` — accessed through Drizzle. Projects, sessions, messages, parts, diffs, todos, goals, permissions, loops, missions, routines, workspaces, accounts, mobile tokens, and the sync journal are all tables in that one file.

The JSON object store and the per-domain databases are gone

Earlier releases wrote JSON files under `Global.Path.data/storage` and kept separate `users.db`, `accounts.db`, `mobile_auth.db`, and `workspaces.db` files. Both models were consolidated into `nikcli.db`; the `20260611020000_import_legacy_databases` and `20260611030000_import_json_storage` migrations import the old data on first run. There is no `Storage.Service` and no `src/storage/` directory any more.

Global.Path.data

### Data root

Holds `nikcli.db`, `log/`, `bin/`, `repos/`, and snapshots. Override with `NIKCLI_DATA_DIR`, or isolate in tests with `NIKCLI_TEST_HOME`.

src/database/database.ts

### One database

`bun:sqlite` in WAL mode behind a Drizzle client, opened once per filename and shared by every domain module. `NIKCLI_DB` overrides the filename.

\*.sql.ts + \*-repo.ts

### Repositories

Each domain declares its tables in a `.sql.ts` file and its queries in a repository namespace. Domain services never touch Drizzle directly.

src/database/migration/

### Migrations

Timestamp-named TypeScript migrations run inside a transaction and are recorded in a `migration` table, so each runs exactly once.

## Path roots

`@nikcli-ai/util/global` owns the global directory model. On startup, `initialize()` creates the data, cache, config, state, log, bin, and repos directories. macOS and Linux follow XDG roots; Windows uses the native AppData layout instead of POSIX dotfiles.

| Path | Default role | Contents |
| --- | --- | --- |
| `Global.Path.data` | Long-lived user data | `nikcli.db` (plus its `-wal`/`-shm` siblings), `auth.json`, `snapshot/`, `plans/`. |
| `Global.Path.log` | Logs | `data/log`. Where `--print-logs` output is also written. |
| `Global.Path.bin` | nikcli-managed binaries | `data/bin`: LSP downloads, language servers, support binaries. |
| `Global.Path.repos` | Hosted repositories | `data/repos`: imported or managed repositories. |
| `Global.Path.cache` | Disposable cache | Model lists, package cache, file search indexes. A cache-version bump clears the directory. |
| `Global.Path.config` | User config | `nikcli.json`, managed config, global themes, global skills. |
| `Global.Path.state` | Ephemeral state | Locks, prompt stash, plugin metadata, brain lock files. |

Terminal

bash 

```
NIKCLI_DATA_DIR=/srv/nikcli-data nikcli serve --hostname 0.0.0.0 --port 4096
NIKCLI_DB=:memory: bun test test/path/file.test.ts
NIKCLI_TEST_HOME=/tmp/nikcli-test bun test test/path/file.test.ts
```

$ shell · paste into your terminal

Path holders must re-resolve

`Global.Path.*` are getters, and the test suite swaps `NIKCLI_TEST_HOME` per file. A singleton that captures a path at module load will keep writing to the previous home — resolve paths on access, not at import time.

## The database

`Database.path()` resolves the filename: `NIKCLI_DB` when set (absolute, relative to the data root, or the literal `:memory:`), otherwise `<data>/nikcli.db`. Opening applies a fixed set of PRAGMAs, runs pending migrations, and checkpoints the WAL.

| PRAGMA | Value | Why |
| --- | --- | --- |
| `journal_mode` | `WAL` | Concurrent readers while a writer holds the file. |
| `synchronous` | `NORMAL` | Durability that matches WAL without an fsync per commit. |
| `busy_timeout` | `5000` | Wait rather than fail when the TUI, server, and CLI write at once. |
| `cache_size` | `-64000` (~64 MB) | Bounds the page cache explicitly. |
| `foreign_keys` | `ON` | Cascades and referential integrity are enforced, not advisory. |
| `mmap_size` | `0` | Keeps the process footprint from growing with the database file; the latency cost is dwarfed by model round-trips. |

The client is a per-filename singleton, so every module in a process shares one connection. A background loop checkpoints the WAL periodically; `Database.close()` and `closeAll()` tear it down.

| Entry point | Use |
| --- | --- |
| `Database.syncDb()` | The shared Drizzle client. Safe from synchronous code — what repositories call. |
| `Database.syncNative()` | The raw `bun:sqlite` handle for PRAGMAs and maintenance. |
| `Database.defaultLayer` | The Effect layer for code that resolves the service through a layer instead. |
| `Database.layerFromPath(file)` | Bind a specific database file — used by tests and tooling. |

## Tables and owners

`src/database/schema.ts` is the single re-export point: every table used by the Drizzle client is listed there, and each one is declared in its own domain module. That is the fastest way to see the current database surface.

| Table(s) | Declared in | Purpose |
| --- | --- | --- |
| `project` | `src/project/project.sql.ts` | Known projects, git roots, and per-project metadata. |
| `session_info` | `src/session/session.sql.ts` | Session records; indexed columns plus a JSON \`data\` blob for the rest. |
| `message_info, message_part` | `src/session/message.sql.ts` | Messages and their parts — text, reasoning, tool calls, files. |
| `session_entry` | `src/session/v2/entry.sql.ts` | The flat v2 event-sourced session log and its persisted projection. |
| `session_goal` | `src/session/goal.sql.ts` | Per-session goal state driven by the goal tools. |
| `session_diff` | `src/session/diff.sql.ts` | File diffs backing summaries, the changes view, and revert. |
| `session_pending` | `src/session/pending.sql.ts` | Queued prompts and steering messages for a busy session. |
| `todo_info` | `src/session/todo.sql.ts` | Todo list state written by the todo tools. |
| `instruction_blob, instruction_state` | `src/session/instruction.sql.ts` | Instruction files and their per-session enable/disable state. |
| `permission_ruleset` | `src/permission/permission.sql.ts` | Persisted always-approvals and per-agent permission rulesets. |
| `loop, loop_run` | `src/loop/loop.sql.ts` | Loop definitions and run history. See Loops. |
| `mission, mission_exec` | `src/mission/mission.sql.ts` | Mission definitions and executions. See Missions. |
| `routine` | `src/mobile/routine.sql.ts` | Scheduled and API-triggered routines. |
| `background_run` | `src/background/run.sql.ts` | Background delegation runs and their status. |
| `monitor` | `src/monitor/monitor.sql.ts` | Long-lived process monitors and their logs. |
| `artifact` | `src/artifact/artifact.sql.ts` | Session artifacts surfaced in the TUI and mobile viewers. |
| `session_share, local_share` | `src/share/share.sql.ts` | Share records for public session links. |
| `sync_event, sync_sequence, sync_snapshot, sync_outbox` | `src/sync/sync.sql.ts` | The sync event journal, sequences, snapshots, and idempotent outbox. |
| `workspace` | `src/workspace/workspace.sql.ts` | Workspace records, target config, and status. |
| `users, user_sessions, chat_contacts, chat_messages` | `src/user/users.sql.ts` | Local server accounts and the chat surface. |
| `account, config` | `src/account/account.sql.ts` | nikcli account records, tokens, and the active account. |
| `mobile_tokens` | `src/mobile/auth.sql.ts` | Hashed mobile bearer tokens with creation, last-used, and expiry. |

## Repository pattern

Domain modules do not write SQL inline. Each domain pairs a schema file with a repository namespace — `SessionRepo`, `MessageRepo`, `LoopRepo`, `PermissionRepo`, and so on — that owns every query against its tables.

1.  **Schema** (`*.sql.ts`) declares the Drizzle table, its columns, and its indexes.
2.  **Repository** (`repo.ts` / `*-repo.ts`) exposes typed synchronous CRUD over `Database.syncDb()`.
3.  **Service** (`index.ts`) adds business logic, publishes bus events, and is what routes and tools call.

Hot columns, cold JSON

Records with a large or evolving shape — sessions are the clearest case — promote the fields that get filtered or sorted into real columns and keep the rest in a JSON `data` column. When a field is later promoted, the column becomes the source of truth and the JSON blob may be stale for older rows; `session_info.last_model` is the worked example in `src/session/repo.ts`.

## Transactions

`Database.transaction(fn)` runs a unit of work against a transaction handle. Nested calls join the outer transaction rather than starting a new one — SQLite has no real nesting — so a projector invoked inside a caller's transaction stays in that transaction.

Repository writes accept an executor (`Database.TxOrDb`) precisely so a sync projector can append its event and update its projection atomically. Reads always use the shared client; they are never part of a projection.

Post-commit effects

`Database.effect(fn)` queues work to run after the current transaction commits — never on rollback. Outside a transaction it runs immediately, because the caller's write has already landed. Publish bus events from there, not from inside the transaction body.

## Migrations

Migrations are TypeScript modules under `src/database/migration/`, named `<timestamp>_<slug>.ts`, each exporting an `id` and an `up(database)` function. `migration.gen.ts` imports them in order.

`DatabaseMigration.apply()` creates a `migration` table, reads the applied ids, and runs each pending migration inside `BEGIN IMMEDIATE` — committing the migration and its bookkeeping row together, rolling both back on failure. Every open of the database runs this, so a fresh install and an upgrade converge on the same schema.

| Migration | What it does |
| --- | --- |
| `20260610211500_initial` | Creates the base schema. |
| `20260611020000_import_legacy_databases` | Imports `users.db`, `accounts.db`, `mobile_auth.db`, and `workspaces.db` into `nikcli.db`. |
| `20260611030000_import_json_storage` | Imports the old `data/storage` JSON object store — projects, sessions, messages, parts, diffs, todos. |
| `20260611040000_import_sync_json` | Imports the pre-SQL sync journal. |
| `20260805000000_session_entry` … | Introduces the flat v2 session entry log and its id ordering; a later migration drops the interim `session_v2_event` table. |
| `202608140*_*_sql` | The domain-by-domain move of loops, projects, goals, background runs, routines, diffs, workspaces, pending prompts, and instruction sync into SQL. |

Adding a migration changes the journal list

`test/database.test.ts` asserts the applied migration list. A new migration is expected to change it — update the test in the same commit, and back up the data root before replaying migration state by hand.

## Files outside the database

Some things are deliberately still files, because a file is the interface.

| Data | Location | Why not SQL |
| --- | --- | --- |
| Config | `nikcli.json` in the config dir and project roots | Users edit and version-control it. See [Configuration](/docs/configuration). |
| Provider auth | `auth.json` under the data root | Credentials with their own read/write path and permissions. |
| Plans | `.nikcli/plans` in the worktree, or `data/plans` for non-VCS sessions | Markdown meant to be read, diffed, and committed. |
| Snapshots | `data/snapshot` | Content-addressed file trees for revert. |
| Skills, commands, agents, themes | Config directories and `.nikcli/` | Authored as files by users and plugins. |
| Brain memory | `.github/instructions/memory.instruction.md` | An instruction file the model reads. See [Brain](/docs/brain). |
| Logs | `data/log` | Append-only and read with ordinary tooling. |

## Operational checks

Terminal

bash 

```
nikcli debug paths

DB="$HOME/.local/share/nikcli/nikcli.db"
sqlite3 "$DB" ".tables"
sqlite3 "$DB" "select id, time_completed from migration order by id;"
sqlite3 "$DB" "select count(*) from session_info;"
sqlite3 "$DB" "select id, name, expires_at from mobile_tokens;"
sqlite3 "$DB" "pragma integrity_check;"
```

$ shell · paste into your terminal

On macOS and Linux the default data root follows the XDG data location; on Windows it is the nikcli directory under Local AppData. If `NIKCLI_DATA_DIR` or `NIKCLI_DB` is set, inspect that path instead.

## Failure modes

| Symptom | Likely cause | Fix |
| --- | --- | --- |
| Session list is empty | Wrong data root, or sessions belong to a different project/worktree. | `nikcli debug paths`, then query `session_info` for the project id you expect. |
| Sessions vanished after upgrading | The JSON import migration did not run — usually a different `NIKCLI_DATA_DIR` than the old install used. | Point the data root at the old location and restart so the import migration sees `data/storage`. |
| Startup throws from a migration | A migration failed and rolled back; it will retry on every open. | Read the `database-migration` log lines, fix the source data or permissions, restart. |
| Writes wait or fail | Another process holds the write lock past `busy_timeout`, or the file is on a filesystem that does not do WAL cleanly. | Close competing processes; move the data root to a local filesystem. |
| Database is locked in tests | A stray `nikcli` binary is still running against the same file. | Kill leftover processes, remove `dist/`, re-run the file alone. |
| Mobile token works once then fails | Token expired or was revoked. | `nikcli mobile token list`, then issue a new token. |
| Manual SQL edits are not visible | The process holds an open connection with its own page cache. | Restart the process after editing the database out of band. |

## Source anchors

| File | What to inspect |
| --- | --- |
| `packages/nikcli/src/database/database.ts` | Filename resolution, PRAGMAs, singleton client, transactions, post-commit effects, WAL checkpoint loop. |
| `packages/nikcli/src/database/schema.ts` | The complete table surface, re-exported from every domain module. |
| `packages/nikcli/src/database/migration.ts` | Migration table, applied-id set, transactional apply loop. |
| `packages/nikcli/src/database/migration/` | The migrations themselves, including the legacy JSON and per-domain database imports. |
| `@nikcli-ai/util/global` | XDG/AppData path resolution, `NIKCLI_DATA_DIR`, `NIKCLI_TEST_HOME`, cache versioning. |
| `packages/nikcli/src/session/repo.ts`, `session.sql.ts` | The canonical repository + schema pair, including the hot-column/JSON-blob split. |
| `packages/nikcli/src/session/message-repo.ts`, `message.sql.ts` | Message and part persistence. |
| `packages/nikcli/src/session/v2/` | The event-sourced entry log and its persisted projection. See [Sessions](/docs/sessions). |
| `packages/nikcli/src/sync/` | Event journal, projectors, snapshots, and the idempotent outbox. See [Sync](/docs/sync). |
| `packages/nikcli/src/mobile/auth.ts`, `auth.sql.ts` | Mobile token hashing, caching, and bearer parsing. |
| `packages/nikcli/src/workspace/workspace.sql.ts` | Workspace records, targets, and status. |

---

Source: https://nikcli-ai.dev/docs/storage