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.