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, imports, values) with via, how the reference was tied to its target (exact, name or macro).
6. CodeGraphEdge: Directed inter-symbol dependency edges (calls, implements, instantiates, imports), with file_id, the file of the source symbol, so a file's outgoing edges are purged by one indexed column.
7. ArchitecturalDecision: Architectural Decision Records (ADRs) parsed from documentation.
8. ToolInvocationMetric: Usage telemetry for every MCP tool call: response tokens, truncation flag and latency (no content; ADR 0015).
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.
11. IndexStats: One row per project holding the last indexing run's health statistics as JSON (parse failures, syntax-error files, skipped files, unresolved references, Cargo dependency state); replaced by each run that changes the index and read by get_indexing_status and scryer doctor.
12. UnresolvedReference: A reference or call edge (kind = reference | edge) whose target named no stored symbol when it was indexed: the target as written, its bare name, role, byte range, line, via and, for an edge, the source symbol and type. A later run or edit that stores a symbol with that bare name links it (BatchIngestionActor::link_deferred) and removes the row; a file's rows go when the file is re-indexed or deleted. Projects only; the dependency project 0 keeps none. Its count (kind = 'reference') is the project's unresolved_references.

Schema Versioning

SchemaVersion rows record the applied schema version (SCHEMA_VERSION, currently 5: 1 = the baseline, 2 adds index_stats, 3 adds symbol_reference.via, 4 adds tool_invocation_metric.truncated, 5 adds unresolved_reference and code_graph_edge.file_id; the columns and the table come from idempotent statements (apply_column_migrations, TABLE_STATEMENTS) that run before the steps, and step 5 backfills file_id from each edge's source symbol). 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)

The embedded engine's planner answers WHERE col = ? from a single-column index but scans for the composites above, so the write path goes by one column: index_code_graph_edge_by_file_id, index_unresolved_reference_by_file_id and index_unresolved_reference_by_bare_name (the names the ORM gives #[index] columns, so a new and an upgraded database end up the same). See docs/learnings/indexing-freshness.md.

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.