> ## Documentation Index
> Fetch the complete documentation index at: https://docs.findmydata.io/llms.txt
> Use this file to discover all available pages before exploring further.

# ADR 0003 persistence single sqlite behind role interfaces

# ADR-0003: One SQLite database behind role-specific store interfaces

**Status:** Accepted · 2026-07-14

## Decision

The vertical slice uses a single SQLite database file (WAL mode) as the physical
store, but code accesses it only through **logical role interfaces** mirroring the
reference architecture's polyglot model:

| Logical role                   | Interface               | v0 implementation                                                                                                                  | Documented evolution                                              |
| ------------------------------ | ----------------------- | ---------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------- |
| Transactional system of record | repositories per module | SQLite tables + constraints                                                                                                        | Azure Database for PostgreSQL                                     |
| Object/evidence artifacts      | `ObjectStore`           | filesystem `data/artifacts/` + metadata table                                                                                      | Azure Blob + lifecycle policies                                   |
| Search projection              | `SearchIndex`           | SQLite FTS5                                                                                                                        | Azure AI Search / OpenSearch                                      |
| Graph projection               | `GraphRepository`       | typed `graph_nodes`/`graph_edges` tables; bounded iterative traversal in the read layer (depth- and node-capped, no recursive CTE) | dedicated graph or Cosmos Gremlin, only if traversal perf demands |
| Event/work substrate           | `WorkQueue` + outbox    | SQLite job/outbox tables                                                                                                           | Azure Service Bus / Storage Queues                                |
| Vector/fingerprint candidates  | `FingerprintIndex`      | SQLite tables (hash buckets, SimHash bands)                                                                                        | pgvector / dedicated ANN when embeddings go live                  |

Every row carries a non-null `tenant_id`; repository helpers refuse queries without
tenant scope.

## Context

Local development and tests must be credential-free (product invariant). SQLite via
`bun:sqlite` is synchronous, fast, transactional, and supports FTS5 and recursive
CTEs — enough to implement *real* semantics (durable jobs, projections, bounded
traversals) rather than in-memory fakes. What matters for the slice is that the
**contracts** are the polyglot ones, so store substitution is additive.

## Consequences

* Projections (search/graph) are rebuildable from the system of record via explicit
  rebuild jobs and carry watermarks — enforced by the interfaces, not convention.
* Single-writer SQLite limits concurrency; acceptable at slice scale. The capacity
  model (docs/capacity-model.md) covers when each role must move out.
* Graph queries are bounded (depth/edge-type/limit parameters are mandatory).