Struct SqlStore 

pub struct SqlStore { /* private fields */ }

Implementations

impl SqlStore

pub async fn new(connection_string: &str) -> Result<Self, StorageError>

Create a new SQL store with startup-mode retry (fails fast if config is wrong).

pub async fn with_registry( connection_string: &str, registry: Arc<SchemaRegistry>, ) -> Result<Self, StorageError>

Create a new SQL store with a shared schema registry.

Use this when you want the SyncEngine to share the registry for dynamic schema registration.

pub fn pool(&self) -> AnyPool

Get a clone of the connection pool for sharing with other stores.

pub fn registry(&self) -> &Arc<SchemaRegistry>

Get the schema registry for this store.

pub async fn ensure_table(&self, table_name: &str) -> Result<(), StorageError>

Create a schema-specific table with the same structure as sync_items.

Used for horizontal partitioning: each schema (e.g., “users”, “orders”) gets its own table with identical DDL. This enables:

• Better query performance (smaller tables, focused indices)
• Easier maintenance (vacuum, backup per-schema)
• Future sharding potential

For SQLite, this is a no-op (returns Ok) since partitioning doesn’t help.

Arguments

• table_name - Name of the table to create (e.g., “users_items”)

Example

store.ensure_table("users_items").await.unwrap();
store.ensure_table("orders_items").await.unwrap();

pub async fn table_exists(&self, table_name: &str) -> Result<bool, StorageError>

Check if a table exists.

pub fn is_sqlite(&self) -> bool

Check if this is a SQLite database.

impl SqlStore

pub async fn search_in_table( &self, table: &str, where_clause: &str, params: &[SqlParam], limit: usize, ) -> Result<Vec<SyncItem>, StorageError>

Search in a specific table using a WHERE clause.

pub async fn count_where_in_table( &self, table: &str, where_clause: &str, params: &[SqlParam], ) -> Result<u64, StorageError>

Count items in a specific table matching a WHERE clause.

pub async fn scan_batch( &self, limit: usize, ) -> Result<Vec<SyncItem>, StorageError>

Scan a batch of items (for WAL drain).

pub async fn delete_batch(&self, ids: &[String]) -> Result<usize, StorageError>

Delete multiple items by ID in a single query.

pub async fn get_dirty_merkle_ids( &self, limit: usize, ) -> Result<Vec<String>, StorageError>

Get IDs of items with merkle_dirty = 1 (need merkle recalculation).

Used by background merkle processor to batch recalculate affected trees.

pub async fn count_dirty_merkle(&self) -> Result<u64, StorageError>

Count items with merkle_dirty = 1.

pub async fn mark_merkle_clean( &self, ids: &[String], ) -> Result<usize, StorageError>

Mark items as merkle-clean after recalculation.

Updates all schema-partitioned tables based on the item ID’s routing.

pub async fn has_dirty_merkle(&self) -> Result<bool, StorageError>

Check if there are any dirty merkle items.

pub async fn branch_dirty_count( &self, prefix: &str, ) -> Result<u64, StorageError>

Count dirty merkle items within a specific branch prefix.

Used for branch-level hygiene checks. Returns 0 if the branch is “clean” (all merkle hashes up-to-date), allowing safe comparison with peers.

Arguments

• prefix - Branch prefix (e.g., “uk.nhs” matches “uk.nhs.patient.123”)

pub async fn get_dirty_prefixes(&self) -> Result<Vec<String>, StorageError>

Get distinct top-level prefixes that have dirty items.

Returns prefixes like [“uk”, “us”, “de”] that have pending merkle recalcs. Branches NOT in this list are “clean” and safe to compare with peers.

pub async fn get_dirty_merkle_items( &self, limit: usize, ) -> Result<Vec<SyncItem>, StorageError>

Get full SyncItems with merkle_dirty = 1 (need merkle recalculation).

Queries all schema-partitioned tables plus the default table. Returns the items themselves so merkle can be calculated. Use mark_merkle_clean() after processing to clear the flag.

pub async fn get_by_state( &self, state: &str, limit: usize, ) -> Result<Vec<SyncItem>, StorageError>

Get items by state (e.g., “delta”, “base”, “pending”).

Uses indexed query for fast retrieval.

pub async fn count_by_state(&self, state: &str) -> Result<u64, StorageError>

Count items in a given state.

pub async fn list_state_ids( &self, state: &str, limit: usize, ) -> Result<Vec<String>, StorageError>

Get just the IDs of items in a given state (lightweight query).

pub async fn set_state( &self, id: &str, new_state: &str, ) -> Result<bool, StorageError>

Update the state of an item by ID.

pub async fn delete_by_state(&self, state: &str) -> Result<u64, StorageError>

Delete all items in a given state.

Returns the number of deleted items.

pub async fn scan_prefix( &self, prefix: &str, limit: usize, ) -> Result<Vec<SyncItem>, StorageError>

Scan items by ID prefix.

Efficiently retrieves all items whose ID starts with the given prefix. Uses SQL LIKE 'prefix%' which leverages the primary key index.

Example

// Get all deltas for object user.123
let deltas = store.scan_prefix("delta:user.123:", 1000).await?;

pub async fn count_prefix(&self, prefix: &str) -> Result<u64, StorageError>

Count items matching an ID prefix.

pub async fn delete_prefix(&self, prefix: &str) -> Result<u64, StorageError>

Delete all items matching an ID prefix.

Returns the number of deleted items.

Trait Implementations

impl ArchiveStore for SqlStore

fn put_batch<'life0, 'life1, 'async_trait>( &'life0 self, items: &'life1 mut [SyncItem], ) -> Pin<Box<dyn Future<Output = Result<BatchWriteResult, StorageError>> + Send + 'async_trait>>

where Self: 'async_trait, 'life0: 'async_trait, 'life1: 'async_trait,

Write a batch of items in a single multi-row INSERT with verification.

Items are grouped by target table (based on schema registry) and written in separate batches per table.

fn get<'life0, 'life1, 'async_trait>( &'life0 self, id: &'life1 str, ) -> Pin<Box<dyn Future<Output = Result<Option<SyncItem>, StorageError>> + Send + 'async_trait>>

where Self: 'async_trait, 'life0: 'async_trait, 'life1: 'async_trait,

fn put<'life0, 'life1, 'async_trait>( &'life0 self, item: &'life1 SyncItem, ) -> Pin<Box<dyn Future<Output = Result<(), StorageError>> + Send + 'async_trait>>

where Self: 'async_trait, 'life0: 'async_trait, 'life1: 'async_trait,

fn delete<'life0, 'life1, 'async_trait>( &'life0 self, id: &'life1 str, ) -> Pin<Box<dyn Future<Output = Result<(), StorageError>> + Send + 'async_trait>>

where Self: 'async_trait, 'life0: 'async_trait, 'life1: 'async_trait,

fn exists<'life0, 'life1, 'async_trait>( &'life0 self, id: &'life1 str, ) -> Pin<Box<dyn Future<Output = Result<bool, StorageError>> + Send + 'async_trait>>

where Self: 'async_trait, 'life0: 'async_trait, 'life1: 'async_trait,

Check if an item exists (SQL EXISTS query - fast, no data transfer).

fn scan_keys<'life0, 'async_trait>( &'life0 self, offset: u64, limit: usize, ) -> Pin<Box<dyn Future<Output = Result<Vec<String>, StorageError>> + Send + 'async_trait>>

where Self: 'async_trait, 'life0: 'async_trait,

Scan keys for cuckoo filter warmup (paginated). Returns empty vec when offset exceeds total count.

fn count_all<'life0, 'async_trait>( &'life0 self, ) -> Pin<Box<dyn Future<Output = Result<u64, StorageError>> + Send + 'async_trait>>

where Self: 'async_trait, 'life0: 'async_trait,

Count total items in store.

fn search<'life0, 'life1, 'life2, 'async_trait>( &'life0 self, where_clause: &'life1 str, params: &'life2 [SqlParam], limit: usize, ) -> Pin<Box<dyn Future<Output = Result<Vec<SyncItem>, StorageError>> + Send + 'async_trait>>

where Self: 'async_trait, 'life0: 'async_trait, 'life1: 'async_trait, 'life2: 'async_trait,

Search using SQL WHERE clause with JSON_EXTRACT. Returns matching items.

fn count_where<'life0, 'life1, 'life2, 'async_trait>( &'life0 self, where_clause: &'life1 str, params: &'life2 [SqlParam], ) -> Pin<Box<dyn Future<Output = Result<u64, StorageError>> + Send + 'async_trait>>

where Self: 'async_trait, 'life0: 'async_trait, 'life1: 'async_trait, 'life2: 'async_trait,

Count items matching a WHERE clause (fast COUNT(*) query). Use for exhaustiveness checks without fetching data.