> ## Documentation Index > Fetch the complete documentation index at: https://kiro-learn.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Database > Local SQLite store with full-text search. Where raw events and memory records live on your machine. The database is the persistence layer for kiro-learn. Every event the [Collector](/architecture/collector) ingests and every memory record [Extraction](/architecture/extraction) produces ends up here. It is a single [SQLite](https://www.sqlite.org/) file on disk, accessed through a small, well-defined interface. Nothing ever leaves your machine from this layer. The database file lives at: ```text theme={null} ~/.kiro-learn/kiro-learn.db ``` No server, no sidecar process, no network port. SQLite runs in-process inside the collector daemon and writes to a single file (plus its WAL and SHM siblings). ## What it stores Two things, in two tables: | Table | What it stores | Written by | | ---------------- | ------------------------------------------------------------ | ------------------ | | `events` | Raw events exactly as the shims posted them (after cleaning) | Ingestion pipeline | | `memory_records` | Structured memories extracted from batches of events | Extraction worker | Plus two internal tables that support them: | Table | What it stores | | -------------------- | -------------------------------------------------------------------------------------- | | `memory_records_fts` | Full-text search index over memory record titles, summaries, and facts | | `_migrations` | One row per applied schema migration — the bookkeeping the runner uses to detect drift | ### The `events` table Every incoming event lands here as one row. The schema mirrors the wire format — event ID, session ID, actor ID, namespace, kind, body, timestamps — with the body and source block stored as JSON blobs. The primary key is `event_id` (a ULID), so duplicate POSTs from a retrying shim are a no-op instead of an error. The table is indexed for the two common access patterns: listing events for a project in reverse chronological order, and looking up an event by its session or parent. Two timestamp columns track different notions of "when": * **`valid_time`** — when the event actually happened (set by the shim). * **`transaction_time`** — when the database recorded it (stamped on insert). These almost always agree, but the split exists so future versions can support [bi-temporal](https://martinfowler.com/articles/bitemporal-history.html) queries — "what did memory look like as of last Tuesday?" — without a schema change. ### The `memory_records` table Each row is one memory record: a title, a summary, a list of facts, a list of concepts, a list of files touched, an observation type, and the IDs of the source events that produced it. The `source_event_ids` field links every memory back to the raw events it came from, so you can always trace a memory to its provenance. Alongside that content, each record carries an optional `embedding` column — a dense vector of the record, stored as a `BLOB`. It is `NULL` on fresh rows until the embedder fills it in, and is never exposed on the wire. The [Embeddings](#embeddings) section below covers the encoding. The primary key is `record_id` (`mr_` + ULID). Unlike events, memory records are never supposed to collide — a duplicate record ID is an upstream bug, so the database rejects the insert loudly instead of silently ignoring it. ### `STRICT` tables Both user-facing tables are declared `STRICT`. This is SQLite's opt-in type-safety mode. In a non-strict table, inserting a string into an `INTEGER` column silently succeeds and stores the string. In a strict table, the same insert fails loudly. For a persistence layer that serializes wire data validated by Zod on the way in, silent type coercion is a liability, not a feature. `STRICT` closes that gap — if a code change accidentally passes a number where a string is expected, the database catches it at insert time instead of letting a malformed row drift into storage. ## Full-text search Memory records need to be searchable by content. [Retrieval](/architecture/retrieval) pulls relevant memories into the agent's prompt, and the match quality directly shapes how useful the feature is. kiro-learn uses SQLite's built-in [FTS5](https://www.sqlite.org/fts5.html) full-text search extension. When a memory record is inserted into `memory_records`, a companion row is also inserted into the `memory_records_fts` virtual table. Both inserts happen inside a single transaction — either both rows land or neither does. ```mermaid theme={null} flowchart LR Worker["Extraction worker"] -->|putMemoryRecord| Tx["Transaction"] Tx --> MR[("memory_records")] Tx --> FTS[("memory_records_fts")] Search["Retrieval"] -->|MATCH| FTS FTS -->|record_id join| MR MR -->|rows| Search ``` The FTS row indexes three fields: * `title` * `summary` * `facts_text` — the record's facts joined into a single searchable blob The `record_id` and `namespace` are carried as `UNINDEXED` columns so the search path can join back to the primary table and filter results by project without a separate lookup. ### Tokenizer The FTS5 index uses a three-stage tokenizer: `porter unicode61 remove_diacritics 2`. Each stage does one thing: | Stage | What it does | | --------------------- | ----------------------------------------------------------------------------------------------- | | `porter` | Stems tokens to their root form — "migrating", "migrates", "migration" all collapse to "migrat" | | `unicode61` | Unicode-aware word segmentation and case folding | | `remove_diacritics 2` | Strips diacritical marks so "café" matches "cafe" | The combination means a search for "migrations" hits memories that mention "migrate", "migrating", or "migration" — behavior closer to what a developer types into a code search box than strict substring match. ### Availability over ranking [Retrieval](/architecture/retrieval#fallback-path-substring-search) builds a sanitized FTS5 query by tokenizing the prompt and emitting an `OR` of quoted phrases. If FTS5 rejects the query anyway — its grammar has corners that are hard to fully account for — the storage layer catches the error and falls back to a `LIKE '%query%'` search against the original string. The LIKE path is slower and unranked (results come back by creation date), but it always works. The explicit tradeoff is **availability over ranking quality**: a worse-ordered result set is better than an error. ## Embeddings FTS5 catches records that share the user's words. [Hybrid retrieval](/architecture/retrieval#hybrid-search) also needs to catch records that share the user's *meaning* — different words, same idea. For that, every memory record gets a dense semantic vector stored alongside its content. ### The `embedding` column The vector lives directly on the memory record. Migration `0005_memory_record_embedding` adds a single column: ```sql theme={null} ALTER TABLE memory_records ADD COLUMN embedding BLOB DEFAULT NULL; ``` That is the whole schema change. No new tables, no new indexes, no FTS5 changes. The column is nullable and defaults to `NULL`, which is the truthful state for a record whose vector has not yet been computed — either because it predates this migration or because the embedder was busy when it was written. Readers that need the vector must handle `NULL` explicitly; hybrid search treats it as "no vector contribution" and falls through to the lexical ranking for that record. Storing the vector inline on the row (rather than in a sidecar table) means the record and its embedding live or die together. A record delete takes the vector with it. There is no second table to keep consistent. ### BLOB encoding The vector is a 384-dimensional `Float32Array` produced by the [all-MiniLM-L6-v2](https://huggingface.co/sentence-transformers/all-MiniLM-L6-v2) sentence-transformer model. It is encoded as exactly **1,536 bytes** — 384 float32 values × 4 bytes each — written in **little-endian IEEE-754** order. ```text theme={null} byte 0–3 : f32 component 0 (little-endian) byte 4–7 : f32 component 1 ... byte 1532–1535 : f32 component 383 ``` The encoder and decoder go through `DataView.setFloat32(offset, value, /* littleEndian */ true)` and `DataView.getFloat32(offset, true)` explicitly rather than relying on the host's byte order. Node runs little-endian on every supported platform today, but making it explicit in code means the format survives a platform we have not tested on, and it makes the invariant unambiguous: the BLOB is portable bytes, not a host-dependent memory dump. The round-trip preserves every IEEE-754 bit pattern — NaN, ±Infinity, ±0, subnormals all survive — which a property test verifies by comparing the underlying `Uint32Array` views after a re-decode. The decoder also validates length: a BLOB that is not exactly 1,536 bytes raises an error annotated with the offending `record_id`, so a corrupted row is visible at the record level rather than silently returning bogus similarity scores. No `sqlite-vec`, no virtual table, no ANN index. Cosine similarity is computed in JavaScript over the in-memory per-namespace vector index; SQLite's job is to durably store the bytes, nothing more. See the [retrieval page](/architecture/retrieval#the-two-rankings) for how the math runs at query time. ### Migration is additive `ALTER TABLE … ADD COLUMN` on SQLite is an O(1) metadata operation when the column is nullable with a `NULL` default. Migration `0005_memory_record_embedding` does not rewrite a single existing row — every pre-existing record ends up with `embedding = NULL` and keeps its `rowid`, its content, and every other column byte-identical. The FTS5 companion table is not touched. Populating the embeddings on existing records is a separate, asynchronous concern handled by the backfill worker, which scans for `embedding IS NULL` and fills rows in batches between ingestions. The migration does not wait for that to complete — the daemon is usable the moment the schema is current, and hybrid search falls back to lexical-only for any record whose vector has not arrived yet. ## Migrations The schema evolves over time. New features need new columns, new indexes, or new constraints. Rather than handwriting upgrade scripts, kiro-learn uses a small migration runner that applies code-embedded DDL in order and tracks what has been applied. ```mermaid theme={null} flowchart TD Open["openSqliteStorage"] --> Runner["Migration runner"] Runner --> Bootstrap["Create _migrations table
if it does not exist"] Bootstrap --> Read["Read applied versions"] Read --> Drift{"DB history matches
code migration list?"} Drift -->|No| Error["Throw MigrationDriftError"] Drift -->|Yes| Apply["Apply pending migrations
in version order"] Apply --> Tx["Each migration runs
in its own transaction"] Tx --> Record["Insert row into _migrations"] Record --> Done["Schema is current"] ``` Rules the runner enforces: 1. **Append-only.** New migrations are added at the end with the next integer version. Released migrations are never reordered, renamed, or edited in place. 2. **Transactional.** Each migration runs inside a `BEGIN`/`COMMIT`. If the DDL throws, the transaction rolls back and `_migrations` is left untouched — the next run tries again from the same point. 3. **Idempotent.** Running the runner twice with the same list is a no-op. After every migration is applied, subsequent opens of the database do no DDL work. 4. **Drift-fatal.** If `_migrations` records a version whose name disagrees with the code, the runner throws `MigrationDriftError` and refuses to proceed. This catches the case where migrations were renamed or reordered after being applied. 5. **Forward-only.** There are no down-migrations. A broken migration is rolled back at the transaction level by SQLite itself; the developer fixes the DDL and re-runs. ### The migration history The schema has evolved through five migrations so far: | Version | Name | What it does | | ------- | ------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- | | 0001 | `init` | Creates `events`, `memory_records`, `memory_records_fts`, and `_migrations`, plus the initial indexes | | 0002 | `xml_extraction_fields` | Adds `concepts_json`, `files_touched_json`, and `observation_type` columns to `memory_records` for the XML extraction pipeline | | 0003 | `project_path` | Adds a nullable `project_path` column to `events` plus a compound index on `(namespace, project_path)` for project listing | | 0004 | `session_summary_type` | Widens the `observation_type` CHECK constraint to include `'session_summary'` (needed because SQLite has no `ALTER CONSTRAINT`, so the table is rebuilt) | | 0005 | `memory_record_embedding` | Adds a nullable `embedding BLOB` column to `memory_records` for local semantic vectors. Additive — no existing row is rewritten. | New migrations appear at the end of the list. The five existing ones are frozen. ## Privacy Everything in the database is local. There is no remote replica, no cloud sync, no background upload. The only path out of your machine is [extraction](/architecture/extraction), where batches of events are sent to [Amazon Bedrock](https://docs.aws.amazon.com/bedrock/latest/userguide/what-is-bedrock.html) via `kiro-cli` for LLM processing — and even that traffic goes out of your AWS account, using your credentials. ### `` is stripped before it reaches the database Users can wrap sensitive content in `...` tags, and those spans are replaced with `[REDACTED]` before storage. **The redaction happens in the collector's cleaning pipeline, not in the database.** By the time an event arrives at `putEvent`, the private spans are already gone. This is a deliberate layering choice. Storage is a sink: if scrubbing lived here, a caller that forgot to scrub would still land scrubbed data in the database but would never see the private spans again and could build mistaken upstream assumptions ("storage protects us"). Centralizing the scrub in the pipeline puts the guarantee in one place and makes it visible. A guard test enforces that the storage layer never references `` at all. ### File-system permissions The installer creates `~/.kiro-learn/` with mode `0700` — owner-read/write/execute only. Other users on a shared machine cannot read your memory database. The storage layer does not widen these permissions; it inherits whatever the parent directory grants. ## The storage interface Callers do not import from the SQLite module directly. The collector's pipeline code, retrieval code, and read API all see a single pluggable interface: ```ts theme={null} interface StorageBackend { putEvent(event: KiroMemEvent): Promise; getEventById(eventId: string): Promise; putMemoryRecord(record: MemoryRecord): Promise; deleteMemoryRecord(recordIds: readonly string[]): Promise; withTransaction(fn: (tx: StorageTransaction) => Promise | T): Promise; searchMemoryRecords(params: SearchParams): Promise; close(): Promise; // Read API for the viewer UI getStats(namespace?: string): Promise; listProjects(): Promise; listMemoryRecords(params: { namespace?; limit; offset }): Promise<{ items; total }>; listEvents(params: { namespace?; limit }): Promise<{ items; total }>; } interface StorageTransaction { putMemoryRecord(record: MemoryRecord): Promise; putEmbedding(recordId: string, embedding: Float32Array): Promise; deleteMemoryRecord(recordIds: readonly string[]): Promise; } ``` The SQLite implementation is one backend behind that interface. Only the collector's bootstrap code knows it exists — everything else (pipeline stages, retrieval assembler, read API handlers) receives a `StorageBackend` as a dependency and never asks which backend it is. A guard test enforces this: any file outside `src/collector/storage/sqlite/` that tries to import from that directory fails CI. The point of this split is future-proofing without disruption. Later versions will ship alternative backends (Postgres with `pgvector`, [Bedrock AgentCore Memory](https://docs.aws.amazon.com/bedrock-agentcore/latest/devguide/memory.html)) and swapping them in becomes a matter of choosing a different opener at startup. The pipeline, retrieval, and UI code changes not at all. ### Method contracts The interface is small because the contracts are strict. A few worth calling out: * **`putEvent` is idempotent.** The insert uses `INSERT OR IGNORE` on the event ID primary key, so a retry from a confused shim is a silent no-op. The original `transaction_time` is preserved on collision — a second insert does not overwrite it. * **`putMemoryRecord` rejects collisions.** Memory record IDs are generated fresh on every extraction. A collision is an upstream bug, so the database surfaces it as an error instead of silently dropping the write. * **`deleteMemoryRecord` cascades and is idempotent.** See [Merge deletion is destructive](#merge-deletion-is-destructive) for the full cascade semantics — in short, one call drops the row from `memory_records`, its embedding from `embeddings`, and its FTS5 entry from `memory_records_fts`, all atomically. Unknown `record_id`s are silent no-ops so retries after a partial crash do not raise. * **`withTransaction` runs one atomic unit of work.** The callback receives a restricted `StorageTransaction` handle exposing only `putMemoryRecord`, `putEmbedding`, and `deleteMemoryRecord`. All three calls commit together or roll back together. Nested transactions are not supported. This is the seam the [extraction worker's push phase](/architecture/extraction#push) uses to commit a summary record and delete its merged originals in one go. * **`getEventById` returns `null` for misses.** Not an exception. The caller decides whether a missing event is a problem. * **`searchMemoryRecords` is availability-biased.** When FTS5 rejects a query, it falls back to LIKE instead of failing. The cost is worse ranking on the fallback path; the benefit is that retrieval never errors out on a weird prompt. * **`close` is idempotent.** A second close is a silent no-op, not an error. Every method is `async` even though [`better-sqlite3`](https://github.com/WiseLibs/better-sqlite3) is synchronous. The async surface keeps the interface compatible with future backends that genuinely are async (remote databases, HTTP APIs) without forcing callers to change shape. ### Prepared statements, parameterized queries Every SQL statement is prepared once when the backend opens and reused for the life of the handle. Every user-controlled value — event body, FTS5 query string, namespace prefix — is bound positionally with `?` placeholders. SQL and data are never concatenated. This is the primary defense against SQL injection. It also happens to be faster: preparing a statement once and binding values on every call avoids re-parsing and re-planning the query on each insert or search. ## Merge deletion is destructive The [extraction worker](/architecture/extraction) is the only caller that deletes memory records. When the judge model rules that a cluster of candidates and some subset of existing neighbors describe the same thing, the worker commits a new summary record **and deletes the merged originals** in the same transaction. There is no soft delete, no tombstone, no `deleted_at` column. The merged rows are gone. The cascade is explicit. `deleteMemoryRecord(recordIds)` touches three things for every id, all inside one `db.transaction(...)` block: ```text theme={null} for each record_id in recordIds: DELETE FROM memory_records_fts WHERE record_id = ? UPDATE memory_records SET embedding = NULL WHERE record_id = ? DELETE FROM memory_records WHERE record_id = ? ``` The embedding is a `BLOB` column on `memory_records` itself (no separate `embeddings` table in the v1 schema), so "deleting the embedding" is an `UPDATE` that nulls the column before the row itself is dropped. Ordering it this way keeps the semantics "child state before parent" — the NULL transition is observable inside the transaction, and if a future schema splits embeddings into a separate table the same ordering still holds. All three statements are explicit prepared statements. SQLite's FTS5 here is a plain virtual table (no content-linked `INSERT … INTO … (content)` rowid coupling), and there are no `ON DELETE` triggers in any migration — the FTS5 companion row is managed in application code the same way it is written in `putMemoryRecord`. A missing cascade statement would leak a ghost FTS row; the property tests catch that by asserting the FTS index is empty for every deleted id. Because `DELETE` (and `UPDATE`) on a nonexistent row affects zero rows and does not raise, `deleteMemoryRecord` is idempotent. An id that was deleted in an earlier attempt is a silent no-op on retry, which is what lets a partially-crashed reconciliation run safely retry the same merge on the next ingestion trigger. **Merge commits have no undo.** A `` decision deletes the original rows outright. Nothing in the database remembers that `record_id mr_abc123` used to exist, and nothing in the retrieval path can surface a merged-away record. The [extraction worker](/architecture/extraction#dedupe) tunes its intra-batch and neighbor similarity thresholds conservatively by default — a more lenient configuration paired with a lenient judge can silently collapse rows that should have stayed distinct, and the only recovery path is to re-learn from fresh events. ### Reconciliation has no schema footprint Reconciliation has no schema footprint — no columns, no indexes, no tables, no triggers. It operates on the `memory_records` schema as it stands. The storage surface exposes two methods that reconciliation relies on: * `deleteMemoryRecord(recordIds)` on the `StorageBackend` interface (and its SQLite implementation). * `withTransaction(fn)` — the restricted handle the reconciler uses to commit a summary-plus-deletes atomically. Reconciliation only touches a row when it comes up as a neighbor of a new candidate cluster and the judge decides to merge it away — no proactive rewrites or migrations of historical data. ## Key design decisions **SQLite first.** SQLite is the simplest thing that covers the v1 requirements: durable local storage, full-text search, multi-process safety (WAL mode), and zero operational overhead. No server to run, no credentials to manage, no port to expose. The trade-off is that it does not scale to multiple machines — which is fine, because kiro-learn is explicitly a local tool until a future version decides otherwise. **One file.** Everything lives in `~/.kiro-learn/kiro-learn.db`. Backing up your memory is `cp`. Starting fresh is `rm`. There is no migration tool, no schema dump format, no cross-file consistency to worry about. **`STRICT` tables everywhere.** SQLite's default laxity about types is convenient for scripting and hostile for a persistence layer. Opting in to `STRICT` catches type errors at the boundary instead of letting them propagate. **FTS5 over building lexical search by hand.** FTS5 is production-tested, maintained by the SQLite project, and handles tokenization, stemming, and ranking in one extension. Rebuilding any of that would be wasted effort. Semantic search is layered on top as an independent ranking ([hybrid retrieval](/architecture/retrieval#hybrid-search)) rather than replacing FTS5 — [Bedrock Knowledge Bases](https://docs.aws.amazon.com/bedrock/latest/userguide/knowledge-base.html) uses the same hybrid shape, so the algorithm is portable. **Migrations are code, not files.** Each migration's DDL is a string constant inside a TypeScript module, compiled into the package. There is nothing to read from disk at runtime and nothing extra to ship. The compiled `dist/` directory is self-contained. **Interface-first.** The `StorageBackend` interface is the contract every backend satisfies. Writing the SQLite backend against the interface (rather than exposing it directly) forces the contract to stay small and backend-agnostic. Every capability that is hard to express in the interface is a capability a future backend would struggle to provide. **Privacy is a pipeline property, not a storage property.** Storage stores what it is given. The pipeline is responsible for redaction. Putting the scrub in the right layer keeps the guarantee auditable and prevents a "defense in depth" that silently swallows bugs. ## Code pointers * `src/collector/storage/index.ts` — the `StorageBackend` interface and method contracts * `src/collector/storage/sqlite/index.ts` — the SQLite backend implementation * `src/collector/storage/sqlite/statements.ts` — every prepared SQL statement in one file, including the three `DELETE` statements that implement the merge cascade * `src/collector/storage/sqlite/fts5.ts` — FTS5 query sanitization and LIKE escaping * `src/collector/storage/sqlite/migrations/runner.ts` — migration runner with drift detection * `src/collector/storage/sqlite/migrations/0001_init.ts` — initial schema * `src/collector/storage/sqlite/migrations/0002_xml_extraction_fields.ts` — extraction fields * `src/collector/storage/sqlite/migrations/0003_project_path.ts` — project path column * `src/collector/storage/sqlite/migrations/0004_session_summary_type.ts` — widened observation type constraint * `src/collector/storage/sqlite/migrations/0005_memory_record_embedding.ts` — embedding BLOB column * `src/collector/embedding/blob.ts` — Float32Array ↔ 1,536-byte BLOB encode/decode ## Related pages The dashboard that reads from the database via the read API How memory records are searched via hybrid FTS5 + vector The worker that drives `putMemoryRecord` and `deleteMemoryRecord` What happens when buffers grow faster than extraction drains them The daemon that writes events to the database