# scryer-db
Relational persistence layer and database models for Scryer, powered by Turso and Tokio's Toasty ORM.
## Architecture & Multi-Tenancy
`scryer-db` implements logical multi-tenancy within a single Turso database. Data isolation is maintained via mandatory `project_id` foreign keys and compound indexes across all entities.
### Core Models
1. **`Project`**: Root tenant container representing a Git repository or workspace root.
2. **`SourceFile`**: Indexed file record with BLAKE3 content hash and language metadata.
3. **`Scope`**: Lexical AST scope boundaries (functions, closures, classes, blocks) with line/byte spans.
4. **`Symbol`**: Declarations (functions, structs, traits, enums, methods, constants) with signatures and visibility.
5. **`SymbolReference`**: Occurrences of symbol usages (calls, types, reads, writes, imports).
6. **`CodeGraphEdge`**: Directed inter-symbol dependency edges (`calls`, `implements`, `instantiates`, `imports`).
7. **`ArchitecturalDecision`**: Architectural Decision Records (ADRs) parsed from documentation.
8. **`ToolInvocationMetric`**: Telemetry accounting for every MCP tool call, recording tokens saved and latency.
9. **`DependencyPackage`**: Globally shared, immutable external package metadata (`(name, version, package_hash)`), storing crate root path and checksum.
10. **`ProjectDependency`**: Relational link associating a `Project` with a `DependencyPackage`, capturing direct vs. transitive status and enabled features.
### Schema Versioning
`SchemaVersion` rows record the applied schema version (`SCHEMA_VERSION`, currently 1 = the baseline). `push_schema` only runs for a brand-new database, so a later change to the schema must bump `SCHEMA_VERSION` and append a `(version, statements)` step to `MIGRATION_STEPS` in `migration.rs`; steps run once, in order, in a transaction. Opening a database stamped with a *newer* version than the binary knows is refused with an upgrade message rather than risking writes the build doesn't understand. `registry.remove` deletes every row a project owns (`PROJECT_OWNED_TABLES`) in the same transaction as the `project` row.
### Composite Indexes
Automated migrations in `migration.rs` ensure composite indexes exist for sub-millisecond query performance:
- `idx_symbol_proj_name` on `symbol(project_id, name)`
- `idx_symbol_proj_qualified` on `symbol(project_id, qualified_name)`
- `idx_files_proj_path` on `source_file(project_id, path)`
- `idx_scope_proj_file` on `scope(project_id, file_id, start_line, end_line)`
- `idx_ref_proj_sym` on `symbol_reference(project_id, symbol_id)`
- `idx_edge_proj_source` on `code_graph_edge(project_id, source_symbol_id)`
- `idx_edge_proj_target` on `code_graph_edge(project_id, target_symbol_id)`
- `idx_dep_pkg_name_ver_hash` on `dependency_package(name, version, package_hash)`
- `idx_proj_dep_proj_pkg` on `project_dependency(project_id, dependency_package_id)`
- `idx_files_dep_pkg` on `source_file(dependency_package_id)`
- `idx_symbol_dep_pkg` on `symbol(dependency_package_id)`
- `idx_symbol_dep_pkg_name` on `symbol(dependency_package_id, name)`
### In-Memory Project Registry
`ProjectRegistry` maintains a thread-safe, in-memory cache of registered projects:
- **Canonical Path Resolution**: Uses longest-prefix matching on canonical filesystem paths to resolve incoming tool file paths to their parent project.
- **Slug Lookups**: Provides O(1) lookup by project slug for workspace navigation.
- **Idempotent Registration**: Upserts database records while updating the in-memory cache.
### Storage Modes & Connectivity
`ScryerDb` supports three database operating modes:
- **In-Memory (`ScryerDb::new_in_memory()`)**: Fast ephemeral storage (`turso::memory:`) ideal for automated testing and isolated runs.
- **File-Backed (`ScryerDb::connect_file(path)`)**: Persistent on-disk SQLite/Turso database with automatic parent directory creation, default (non-MVCC) journal mode, and idempotent schema initialization. MVCC / `BEGIN CONCURRENT` is intentionally not enabled; see `docs/learnings/codebase-quirks.md` ยง3.
- **URL/Remote (`ScryerDb::connect(url)`)**: Standard Turso connection string support for remote clusters or custom connection configurations.
## Timestamps
`project.created_at` / `updated_at` and `tool_invocation_metric.created_at` are RFC 3339 UTC strings with millisecond precision (`2026-10-06T21:04:20.123Z`, from `scryer_db::time::now_rfc3339`), so they sort chronologically as text. Older databases stored `"<unix secs>.<millis>Z"`; `upgrade_legacy_timestamps` rewrites those rows when the database is opened.