The data layer

One SQLite file, one connection per process, and a schema version tracked by PRAGMA user_version. Everything durable that is not a document lives here; everything that is a document — her long-term memory, her files, her reports — lives on disk as files you can open.

Where the file is

kotoba/paths.py resolves it, and it distinguishes a clone from a wheel:

• From a clone (<repo>/api/src/kotoba with a soul/ beside api/), a database already sitting at api/kotoba.db wins. One beside the package always wins, and that direction is the whole point: preferring the home directory whenever a file happened to be there was measured switching a live install onto a two-turn stray while 561 conversations and two saved keys went invisible — and doctor called it ok. Moving to the home is something you do on purpose, by moving the file.
• Otherwise — a wheel, or a clone with no database beside the package — it is ~/.kotoba/ (or $KOTOBA_HOME).

DATABASE_URL overrides it, and a relative URL like the shipped sqlite:///./kotoba.db resolves against db_dir(), never against the current directory. The terminal starts in the user's own project folder, so without that rule it would open — and create — a different, empty database, with no memory, no saved keys, no history and nothing on screen to say why. A driver-qualified URL (sqlite+aiosqlite:///…) is the same URL and is stripped the same way.

Five modules used to count Path(__file__).parents[N] by hand. That is the one failure a package move makes silent: a wrong depth still names a directory that exists, so the database is empty and the skills are missing with nothing raised. They all read paths.py now.

The connection

db/database.py::Database — one aiosqlite connection per process, published as Engine.db and, in the web server, as app.state.db. One shared connection is enough inside a process because SQLite serialises writes anyway.

What is not internal is the second process. The terminal runs beside the server on the same file, and three things exist for that:

• PRAGMA busy_timeout=5000 is set first, before anything that can take a lock. Converting a fresh rollback-journal database to WAL takes an exclusive lock and returns SQLITE_BUSY rather than waiting, so two processes booting the same fresh install crashed one of them.
• _enable_wal retries the one-time conversion.
• The cron claim is a compare-and-swap, not a read-then-write.

The file is forced to mode 0600 on every connect, not just at creation — SQLite creates it at 0644 minus umask, so every turn she had ever been told sat readable by any local account while the keystore, holding less, was 0600. The -wal and -shm sidecars are restricted too: on POSIX SQLite copies the database's mode onto them, but Windows has no mode to copy.

One more thing that looks like paranoia and is not: _close_open_databases runs on threading._register_atexit. aiosqlite's worker thread is not a daemon, and CPython joins non-daemon threads before atexit. A Database that outlives its loop with no close() parks that worker forever and the process can never exit — no traceback, no timeout, and to a parent reading through a pipe, no output at all, since the pipe never reaches EOF.

The tables

Three indexes cover the hot paths: turns(session_id, created_at) for the history read on every turn, cronjobs(active, fired_at, due_at) for the due-job scan, and audit_log(created_at) for the recent-rows view.

saved_keys is envelope-encrypted

core/keystore.py wraps every value in AES-256-GCM before it is persisted, as v1:<base64(nonce(12) + ciphertext)>, and decrypts only in memory at use. The key-encryption key comes from KOTOBA_MASTER_KEY if set, otherwise from an auto-generated per-install file at ~/.kotoba/.keystore_key at mode 0600 — so encryption works out of the box with no configuration.

get_key distinguishes "never saved" from "ours by shape but not by key": a row that no longer decrypts logs an error naming the key, because a silent None reads as "never saved" and the Settings panel would keep insisting the key was there while every call failed for want of one.

audit_log_read — a view, not a step

Views carry no data, so they live in _VIEW_STATEMENTS and are rebuilt on every boot rather than being a migration. audit_log_read adds two derived columns to every audit row:

• regime — whether that row's approved/approver can be trusted at all. detail IS NULL means the row predates the convention where approved meant consent.
• authority — a sentence naming who permitted this, or that nobody did: user, auto-safe, allowlist, saved-family, saved-exact-command, none — the loop ran it, nobody was asked, nobody — the card was shown and expired unanswered, denied, and so on.

The rebuild is deliberately not executescript: that runs each statement in autocommit, so two processes booting at once interleave into DROP, DROP, CREATE, CREATE and the second CREATE dies unhandled. It runs inside BEGIN IMMEDIATE, and the except re-raises only if the view is genuinely absent afterwards — a racer that got in first rebuilt the same view from the same source.

Migrations

db/migrations.py. Each entry in _STEPS is an idempotent SQL script; PRAGMA user_version records how many have been applied; every step at or past that index runs, bumping the version after each. Databases predating versioning read 0, and step 0 is IF NOT EXISTS throughout, so it no-ops for them. A fresh boot today ends at user_version = 4.

Append, never edit. Indexes once appended to step 0 never reached databases already past it, and the hot queries silently full-scanned. A table rebuild must run inside BEGIN IMMEDIATE, or executescript's commit between statements leaves a window where the table does not exist. And the version bump is not atomic with the step — a crash in between re-runs it — which is why every step has to stay idempotent.

Dead on purpose

Two things in this schema are honestly dead, and documented as dead rather than removed.

turns.emotion and turns.tools_used are NULL in every row. All eight insert_turn call sites across the four surfaces pass session_id, role and content only, and nothing queries either column. The face is driven live by the event channel, never replayed from history. Dropping them would be a new migration step over live user data in exchange for no behaviour at all, so the comment sits beside the columns in db/migrations.py instead. Note this if you are reading history and wondering why she never seems to remember which tools she used.

memory_facts has no writer. Her long-term memory moved to structured Markdown under ~/.kotoba/memory — an index (USER.md) plus one file per topic. The table is read exactly once, by engine._migrate_memory at startup, to fold any pre-existing rows into that store; the migration is idempotent and no-ops once topic files exist. The table stays so an old install does not lose anything on upgrade.

What is deliberately not in SQLite

• Long-term memory — Markdown in ~/.kotoba/memory, readable and editable by hand.
• Her files — ~/.kotoba/files, which is also the default jailed workdir, so the Files panel mirrors exactly what is on disk with no per-session copy or sync.
• Settings — ~/.kotoba/settings.yaml, written atomically, with a cross-process lock.
• MCP server configuration and the plugin folder — also under the home directory.
• Reports being viewed, and pending attachments — in memory only, and gone when the process restarts. The report's own HTML file is saved into the workspace and stays.