Database Schema & Migrations
Relevant source files
The following files were used as context for generating this wiki page:
Coral manages persistent state using either SQLite or Postgres. The system ensures data portability across these backends by maintaining a shared chain of migrations and performing read-only state format checks during application bootstrap.
Persistence Architecture
Coral isolates domain behavior from the physical storage layer by keeping database queries and row mapping inside specific repository modules. Domain code owns the transaction choreography, while repository facets join existing transactions to ensure atomicity.
Multi-Backend Support
Coral supports two primary database systems:
- SQLite: Used for local development and single-process deployments.
- Postgres: Used for shared environments and multi-worker scalability.
Sources: AGENTS.md:30-34, crates/coral-app/src/state/database_config.rs:10-14
State Validation Flow
Before Coral applies migrations, it validates the existing database to ensure it belongs to the current state format (coral-state-v1). This prevents data corruption from incompatible versions.
The system identifies the state format using a specific table named coral_state_format.
Sources: crates/coral-app/src/state/database_format.rs:12-40, crates/coral-app/src/state/database_format.rs:198-203
Migration System
Coral uses a single, shared chain of migrations. Developers must write portable SQL files that run on both SQLite and Postgres.
Portable Migrations
If a migration step requires different implementation for each backend, developers create separate files with identical names in backend-specific directories:
crates/coral-db/migrations/postgres/crates/coral-db/migrations/sqlite/
Sources: AGENTS.md:99-105
Migration Integrity
The application enforces strict rules to maintain database consistency:
- Immutability: Once a migration is on the
mainbranch, its file name and contents must never change. - Detection: The
scripts/check-migrations.shutility validates that existing migrations have not been altered. - Drift Prevention: Coral avoids using
IF NOT EXISTSin migrations to prevent hiding unexpected state.
Sources: AGENTS.md:106-111
Database Schema Components
The schema is divided into several functional domains, including search indexing, provider catalogs, and function persistence.
Search Indexing (Universal Search)
Universal Search maintains a text index beside search entities. On Postgres, it uses vector-weighted fields and splits camelCase names into separate words for improved ranking. SQLite uses FTS5 triggers to keep the text index synchronized with the search_entities table.
| Field | Description |
|---|---|
workspace_id | Identifies the workspace owning the document. |
doc_id | Primary identifier for the indexed document. |
kind | Type of entity (e.g., operation, saved_function). |
tsv | (Postgres) Tsvector for full-text search. |
title | The display title used for ranking. |
Sources: crates/coral-search/src/tests/storage.rs:18-50, crates/coral-search/src/tests/storage.rs:193-210, crates/coral-search/src/tests/storage.rs:356-378
Catalog Repository
The catalog stores materialization outcomes for providers, including OpenAPI documents and MCP discovery snapshots.
Configuration & Connection
The database connection is configured via TOML or environment variables.
Configuration Options
| Key | Type | Default | Description |
|---|---|---|---|
backend | String | sqlite | The database type (sqlite or postgres). |
path | Path | state/coral.db | (SQLite) Path to the database file. |
url_env | String | N/A | (Postgres) Name of the environment variable containing the URL. |
Sources: crates/coral-app/src/state/database_config.rs:17-40
Transaction Management
Coral enforces specific locking and visibility rules:
- Read-Only Operations: Opened with
Database::begin_read_snapshot. - Write Operations: Opened with
Database::begin, which waits for a SQLite write lock at the start of the transaction. - Row Locking: Uses
RowLock::Sharedfor decisions that read facts they do not write (e.g., Workspace or Policy rules).
Sources: AGENTS.md:43-60, crates/coral-db/tests/persistence_foundation.rs:136-160
Conclusion
The Coral database system is designed for high reliability and multi-backend portability. By combining a strict migration policy, read-only state format validation, and isolated repository modules, the architecture ensures that domain logic remains independent of the underlying storage engine while maintaining strict data consistency.