# Database Layer — `src/db.rs`

## Overview

Theseus uses **rusqlite** (SQLite) as its sole persistence layer. All data lives in a
single `.db` file opened via `Database::open(path)`. The schema is created eagerly
on every open (`CREATE TABLE IF NOT EXISTS`), so no migration tooling is required.

## Connection & Mutex Pattern

```rust
pub struct Database {
    conn: Mutex<Connection>,       // std::sync::Mutex, not tokio
}
```

- The entire `rusqlite::Connection` is wrapped in a **single `std::sync::Mutex`**.
- Every method acquires the lock via the private helper `fn c(&self) -> MutexGuard<Connection>`.
- Poisoned locks are recovered with `unwrap_or_else(|e| e.into_inner())` — the DB
  is never abandoned after a panic.
- This is a **coarse-grained lock**: only one thread can touch SQLite at a time.
  Adequate because the workload is low-concurrency (single-user desktop app / agent).
- `PRAGMA journal_mode=WAL` is set at open for better concurrent read performance
  and crash safety.
- `PRAGMA foreign_keys=ON` is set at open (SQLite has FK enforcement off by default).

> **Implication:** All `Database` methods are synchronous and blocking. The Axum
> routes call them inside `web::block` or `spawn_blocking` to avoid stalling the
> async runtime.

## Schema — 6 Tables

### `projects`

| Column       | Type | Constraints |
|-------------|------|-------------|
| `id`         | TEXT | PRIMARY KEY (UUID v4) |
| `name`       | TEXT | NOT NULL |
| `created_at` | TEXT | NOT NULL (RFC 3339) |

Root entity. All other tables reference `project_id` with `ON DELETE CASCADE`.

### `nodes`

| Column       | Type | Constraints |
|-------------|------|-------------|
| `id`         | TEXT | PRIMARY KEY |
| `project_id` | TEXT | NOT NULL → FK `projects(id)` CASCADE |
| `label`      | TEXT | NOT NULL |
| `node_type`  | TEXT | NOT NULL — one of: `host`, `service`, `vulnerability`, `privilege_escalation`, `credential`, `finding`, `note`, `entry_point`, `attack_step`, `impact`, `objective`, or any custom string |
| `x`          | REAL | nullable (canvas position) |
| `y`          | REAL | nullable (canvas position) |
| `properties` | TEXT | NOT NULL DEFAULT `'{}'` — JSON blob; holds `content`, `verdict`, `evidence`, and arbitrary keys |
| `created_at` | TEXT | NOT NULL (RFC 3339) |

**Indexes:** `idx_nodes_pid ON nodes(project_id)`

### `edges`

| Column           | Type | Constraints |
|-----------------|------|-------------|
| `id`             | TEXT | PRIMARY KEY |
| `project_id`     | TEXT | NOT NULL → FK `projects(id)` CASCADE |
| `source_node_id` | TEXT | NOT NULL |
| `target_node_id` | TEXT | NOT NULL |
| `label`          | TEXT | NOT NULL DEFAULT `''` |
| `created_at`     | TEXT | NOT NULL |

**Indexes:** `idx_edges_pid ON edges(project_id)`

> Deleting a node also cascades deletes to related edges **at the application level**
> in `delete_node()` (not via DB FK — `source_node_id`/`target_node_id` have no FK).

### `credentials`

| Column           | Type | Constraints |
|-----------------|------|-------------|
| `id`             | TEXT | PRIMARY KEY |
| `project_id`     | TEXT | NOT NULL → FK `projects(id)` CASCADE |
| `label`          | TEXT | NOT NULL |
| `credential_type`| TEXT | NOT NULL — enum: `password`, `hash`, `key`, `token`, `cookie`, `other` |
| `username`       | TEXT | nullable |
| `secret`         | TEXT | NOT NULL |
| `source_node_id` | TEXT | nullable → FK `nodes(id)` SET NULL |
| `status`         | TEXT | NOT NULL DEFAULT `'unverified'` — enum: `unverified`, `valid`, `invalid`, `expired` |
| `notes`          | TEXT | nullable |
| `created_at`     | TEXT | NOT NULL |

**Indexes:** `idx_credentials_pid ON credentials(project_id)`

### `evidence_files`

| Column         | Type | Constraints |
|---------------|------|-------------|
| `id`           | TEXT | PRIMARY KEY |
| `project_id`   | TEXT | NOT NULL → FK `projects(id)` CASCADE |
| `node_id`      | TEXT | NOT NULL → FK `nodes(id)` CASCADE |
| `original_name`| TEXT | NOT NULL |
| `stored_name`  | TEXT | NOT NULL |
| `file_size`    | INTEGER | NOT NULL DEFAULT 0 |
| `mime_type`    | TEXT | nullable |
| `created_at`   | TEXT | NOT NULL |

**Indexes:** `idx_evidence_pid_nid ON evidence_files(project_id, node_id)`

### `agent_memory`

| Column       | Type | Constraints |
|-------------|------|-------------|
| `id`         | TEXT | PRIMARY KEY |
| `project_id` | TEXT | NOT NULL → FK `projects(id)` CASCADE |
| `role`       | TEXT | NOT NULL — e.g. `user`, `assistant`, `system` |
| `content`    | TEXT | NOT NULL |
| `tool_calls` | TEXT | nullable — JSON string |
| `run_id`     | TEXT | NOT NULL — groups messages from a single agent run |
| `created_at` | TEXT | NOT NULL |

**Indexes:** `idx_agent_memory_pid_time ON agent_memory(project_id, created_at DESC)`

## CRUD Methods by Table

| Table        | Methods |
|-------------|---------|
| **projects** | `create_project`, `list_projects`, `get_project`, `delete_project`, `find_project_by_name`, `count_nodes_for_project`, `count_edges_for_project` |
| **nodes**    | `insert_node`, `node_exists`, `list_nodes`, `get_node`, `update_node`, `delete_node`, `count_edges_for_node`, `search_nodes`, `nodes_by_date`, `find_node_by_label`, `find_nodes_by_label_fuzzy` |
| **edges**    | `insert_edge`, `list_edges`, `update_edge`, `delete_edge`, `edge_exists` |
| **credentials** | `create_credential`, `list_credentials`, `get_credential`, `update_credential`, `delete_credential` |
| **evidence_files** | `insert_evidence_file`, `list_evidence_files`, `get_evidence_file`, `delete_evidence_file` |
| **agent_memory** | `save_memory`, `load_recent_memory`, `prune_memory` |

### Node update semantics

`update_node` performs a **read-modify-write**: it fetches the current row, applies
field-level patches (including property merging), then writes back. Special fields
`content`, `append_content`, `set_verdict`, `set_evidence` map into the `properties`
JSON blob rather than top-level columns.

### Agent memory lifecycle

- `save_memory` appends a message (role + content + optional tool_calls).
- `load_recent_memory(N)` returns the last N messages in chronological order
  (queries DESC, then reverses in Rust).
- `prune_memory(N)` deletes all but the most recent N rows per project — used
  to keep context windows bounded.

## Analysis & Query Functions

| Function | Description |
|---------|-------------|
| `project_timeline(pid)` | Builds a chronologically sorted list of `TimelineItem`s with incoming/outgoing edge info |
| `global_search(query)` | LIKE search across `nodes.label` + `nodes.properties`, joined with `projects.name`, limit 50 |
| `check(project_id)` | Graph health check: validates provenance chains, unverified hypotheses, unused vulns, dangling exploits |
| `analyze(project_id)` | Full attack-path analysis: BFS from entry points, identifies unexploited vulns, dead ends, next steps, cross-connections, isolated nodes |

> `analyze` and `check` work entirely in memory after loading all nodes + edges
> for a project — no recursive CTEs.

## Row Deserializers

Five private functions convert `rusqlite::Row` → domain structs:
`row_project`, `row_node`, `row_edge`, `row_credential`, `row_evidence_file`.
UUIDs are stored as TEXT and parsed on read; timestamps are RFC 3339 strings.

## Key Source Locations

| What | Location |
|-----|----------|
| Struct + open/schema | `src/db.rs:12-85` |
| Row deserializers | `src/db.rs:90-181` |
| Projects CRUD | `src/db.rs:185-244` |
| Nodes CRUD | `src/db.rs:248-474` |
| Edges CRUD | `src/db.rs:368-419` |
| Credentials CRUD | `src/db.rs:478-567` |
| Evidence files CRUD | `src/db.rs:571-610` |
| Timeline | `src/db.rs:614-686` |
| Global search | `src/db.rs:690-720` |
| Analysis types | `src/db.rs:724-800` |
| Check + Analyze | `src/db.rs:802-1266` |
| Agent memory | `src/db.rs:1269-1339` |
| Tests | `src/db.rs:1342-1992` |
| Domain types | `src/types.rs` |
