# Cairn — Database

## Engine

| | |
|---|---|
| Engine | SQLite (bundled with Python) |
| Mode | WAL (Write-Ahead Logging) |
| Foreign keys | `PRAGMA foreign_keys=ON` |
| Default path | `~/.local/share/cairn/cairn.db` |
| Connection | `sqlite3.connect()`, `row_factory = sqlite3.Row` |
| Concurrency | Single-writer (context manager per request, auto-commit/rollback) |

## Schema

### settings
Global timeout configuration. Single row (rowid=1).

| Column | Type | Default |
|--------|------|---------|
| intent_timeout | INTEGER | 15 |
| reason_timeout | INTEGER | 15 |

### projects
Top-level container for a problem-solving session.

| Column | Type | Default |
|--------|------|---------|
| id | TEXT PK | — |
| title | TEXT NOT NULL | — |
| status | TEXT | `'active'` (active/stopped/completed) |
| bootstrap_enabled | INTEGER | 1 |
| created_at | TEXT | — |
| reason_worker | TEXT | NULL |
| reason_trigger | TEXT | NULL |
| reason_started_at | TEXT | NULL |
| reason_last_heartbeat_at | TEXT | NULL |

### facts
Confirmed findings. Composite PK: `(id, project_id)`.

| Column | Type | Notes |
|--------|------|-------|
| id | TEXT | e.g., `"origin"`, `"goal"`, `"fact-1"` |
| project_id | TEXT FK→projects | CASCADE |
| description | TEXT | — |

### intents
Exploration directions. Composite PK: `(id, project_id)`.

| Column | Type | Notes |
|--------|------|-------|
| id | TEXT | e.g., `"intent-1"` |
| project_id | TEXT FK→projects | CASCADE |
| to_fact_id | TEXT | NULL = open, "goal" = completion |
| description | TEXT | — |
| creator | TEXT | Worker or human name |
| worker | TEXT | Currently claiming worker (NULL = unclaimed) |
| last_heartbeat_at | TEXT | Lease expiry check |
| created_at | TEXT | — |
| concluded_at | TEXT | NULL = still open |

### intent_sources
Many-to-many: which facts are the "from" of an intent.

| Column | Type |
|--------|------|
| intent_id | TEXT |
| project_id | TEXT |
| fact_id | TEXT |
| **PK** | `(intent_id, project_id, fact_id)` |
| **FK** | `(intent_id, project_id)` → intents(id, project_id) CASCADE |

### hints
Human-injected guidance. Composite PK: `(id, project_id)`.

| Column | Type |
|--------|------|
| id | TEXT |
| project_id | TEXT FK→projects CASCADE |
| content | TEXT |
| creator | TEXT |
| created_at | TEXT |

### counters
Global ID counters.

| Column | Type |
|--------|------|
| name | TEXT PK |
| value | INTEGER DEFAULT 0 |

### scoped_counters
Per-project ID counters (fact-N, intent-N, hint-N).

| Column | Type |
|--------|------|
| project_id | TEXT FK→projects CASCADE |
| kind | TEXT |
| value | INTEGER DEFAULT 0 |
| **PK** | `(project_id, kind)` |

## Entity Relationships

```
projects 1──N facts
projects 1──N intents
projects 1──N hints
intents 1──N intent_sources (via intent_id, project_id)
facts 1──N intent_sources (via fact_id)
projects 1──N scoped_counters
```

## Key Patterns

- **ID generation** — `next_project_id()` increments `counters.project`; `next_fact_id(conn, pid)` increments `scoped_counters` for `(pid, "fact")`
- **Lease expiry** — `expire_workers()` clears stale `worker` fields on intents where `last_heartbeat_at` is older than `intent_timeout`; `expire_reason_leases()` does the same for `reason_worker` on projects
- **Schema migration** — `_ensure_project_columns()` uses `PRAGMA table_info` to detect missing columns and `ALTER TABLE ADD COLUMN`
- **Transaction per request** — `get_conn()` context manager: commit on success, rollback on exception, close in finally
