Local data
A coven store is one SQLite database on the device, plus the files its rows carry. The app opens it through one handle, declares which tables sync, and runs ordinary SQL. This page is about the store on one device: opening it, declaring tables, keys, and writing and reading.
Opening a store
Each store lives in its own directory under the app's StoreLayout. The directory holds the database, coven's copies of files, the cache, and the store's settings: its id and name, this device's id, and its storage location. The app never picks a device id; creating, restoring or joining a store does.
let layout = StoreLayout::new(app_dir);
// First launch: make the store. Its member keys go to custody before anything else.
let handle = Coven::builder(layout.clone())
.synced_tables(tables())
.migrations(migrations())
.create_store("Household")
.await?;
// Later launches: open it again.
let store = layout.stores().await?[0].id;
let handle = Coven::builder(layout.clone())
.synced_tables(tables())
.migrations(migrations())
.open(store, |progress| show_progress(progress))
.await?;Opening migrates coven's own tables, then runs the app's pending migrations (Schema evolution), then checks the schema against the declared tables. A large migration, or rebuilding the indexes after a new version of SQLite or of the app's own orders and functions, takes a while, so open calls the app back with each step and how much of it is done, a Progress of OpenStep, for a progress bar. Most opens have no such step and call it never. It needs no network and doesn't start syncing: the sync state of a store without storage set up is not synced, and one with storage keeps how its last pass ended, with a queued file waiting for sync to start (Sync).
One writable handle can have a store open at a time; a second open, in this process or another, fails with StoreLockError::AlreadyOpen. Another process, such as a widget, can read alongside the app with open_read_only, which returns a CovenReadHandle. It runs no migration and refuses a database whose schema needs migrating or is newer than its own; while it is open, the store can't be deleted.
handle.close() stops syncing, closes every connection and releases the lock. handle.delete_store(IfUnsent::Refuse) removes the store from this device: it refuses while writes or files wait to upload, publishes this device's removal, then deletes this device's own keychain entries and its directory. The restore code and S3 key in iCloud Keychain stay for the person's other devices (Keys). IfUnsent::Discard drops the unsent work instead. For a store that no longer opens, Coven::discard_store(&store_dir) deletes it without opening it. The copy in storage is never touched.
SQLite settings
App SQL can't run a PRAGMA, so the app chooses SQLite's settings on the builder, as a SqliteSettings. Coven applies them to every connection a handle opens to the store's database, the writer and the readers alike, as each one opens. A setting left None keeps SQLite's default, except checkpoint_fullfsync, which coven turns on for macOS and iOS, and journal_size_limit, 4 MiB. On macOS and iOS each checkpoint flushes the drive's own cache, so a power loss can't damage the database file; commits keep their plain fsync, so a power loss can still take the latest few commits.
Coven checkpoints the WAL itself, never inside a commit: once the WAL holds 1,000 pages not yet copied, or wal_autocheckpoint, a thread of the store's own copies it on a connection of its own and lets the next commit start the WAL over. A live query rerunning after every commit no longer makes each commit wait for a checkpoint it can't finish. WalAutocheckpoint::Off leaves checkpoints to the app.
let handle = Coven::builder(layout.clone())
.synced_tables(tables())
.migrations(migrations())
.sqlite_settings(SqliteSettings {
cache_size: Some(CacheSize::Kibibytes(64 * 1024)),
wal_autocheckpoint: Some(WalAutocheckpoint::Off),
..SqliteSettings::default()
})
.open(store, |progress| show_progress(progress))
.await?;
// After a large import, let the next writes reuse the WAL from its start.
let checkpoint = handle.checkpoint(CheckpointMode::Restart).await?;The app can set the busy timeout, cache and memory-map sizes, where temporary tables live, sorting threads, how large the WAL file stays and when coven checkpoints it, secure delete, full fsync on Apple platforms, and the query planner's automatic indexes and ANALYZE limit. The page size and auto-vacuum mode apply only when the store's database is created; an existing store keeps its own. handle.sqlite_settings() reads back what SQLite has in effect, and checkpoint returns what SQLite reports: whether it was blocked, how many frames the WAL holds, and how many it copied.
Coven gathers the query planner's statistics itself, with PRAGMA optimize, as a store opens, after migrating, and as it closes. A run analyzes the tables with no statistics or whose size changed tenfold, reading at most 2,000 rows of each index, so it costs a few milliseconds the first time and almost nothing after. While the store stays open, coven checks each time its writes have changed 1,000 rows, and runs again once a table reaches 100 rows or ten times the size its statistics count, so a table that fills in one session doesn't plan blind until the next open. An app write can't run ANALYZE; a migration can.
The settings coven depends on have no field, so the app can't change them: WAL mode, synchronous = FULL, normal locking, foreign keys, recursive triggers and an untrusted schema. The API reference lists every SQLite setting and why each one is the app's or coven's.
Collations
SQLite orders text by its own three collations, BINARY, NOCASE and RTRIM. For any other order, such as listing "Note 9" before "Note 10", the app registers a collation on the builder: a name, a version, and a function that compares two strings. Its SQL then names it with COLLATE, in a query or in the schema.
let handle = Coven::builder(layout.clone())
.synced_tables(tables())
.migrations(migrations())
.collation("natural_order", 1, natural_order)
.open(store, |progress| show_progress(progress))
.await?;
// A migration can declare an index in that order:
// CREATE INDEX notes_by_title ON notes(title COLLATE natural_order)
let titles = handle
.read(|sql| {
Ok::<_, CovenError>(sql.query(
"SELECT title FROM notes ORDER BY title COLLATE natural_order",
[],
|row| row.get::<_, String>(0),
)?)
})
.await?;Coven registers each collation on every connection it opens to the store's database, before any app SQL runs there: the writer, which also migrates and applies other devices' writes, the readers, and every connection of a read-only handle. Every builder that opens the store, a widget's read-only one included, registers the same collations.
An order is part of the store's schema. At one version, the function must be deterministic, comparing the same two strings the same way on every device and in every app version, and a total order. An index keeps its entries in the order the function gave when they were written, so every change to the order takes a new version. When a writable handle opens with a version the store's indexes weren't built with, coven rebuilds every index in that order, before the app's migrations, and records the version; an open at the same versions rebuilds nothing. A read-only handle can't rebuild, and fails with DbError::VersionNotRebuilt until a writable one has. Key and unique columns of synced tables still compare by bytes.
A version can't change while a CHECK or generated column, or a unique index of a synced table, uses the order: those decide which rows every device keeps, or keep what the old order computed, and a rebuild can't make devices at different versions agree. The open fails with DbError::VersionChangeRefused and changes nothing. To change such an order, register it under a new name, migrate those declarations to it, and keep registering the old name at its version.
A name that is one of SQLite's own fails the open with DbError::ReservedCollation, and a name given twice, ignoring case, with DbError::DuplicateCollation. A name that is an SQL keyword, such as natural, has to be quoted: COLLATE "natural". A panic in the function ends the process, because SQLite has no way for a comparison to fail.
Functions
SQLite's lower, LIKE and NOCASE fold only ASCII letters, so a search for "école" misses a note titled "École". The app can give SQLite a function of its own instead: a name, how many arguments it takes, whether it is deterministic and at which version, and the Rust function that computes it. Its SQL then calls it by name, in a query or in the schema.
let handle = Coven::builder(layout.clone())
.synced_tables(tables())
.migrations(migrations())
.function("fold", 1, Determinism::Deterministic { version: 1 }, |arguments| {
Ok(arguments.get::<String>(0)?.to_lowercase())
})
.open(store, |progress| show_progress(progress))
.await?;
// A migration can declare an index on it:
// CREATE INDEX notes_folded ON notes(fold(title))
let matches = handle
.read(|sql| {
Ok::<_, CovenError>(sql.query(
"SELECT title FROM notes WHERE fold(title) = fold(?1)",
["école"],
|row| row.get::<_, String>(0),
)?)
})
.await?;Coven registers each function on the same connections as the collations, and every builder that opens the store has to register the same ones. The function returns any value that converts into an SQLite value, such as a String, an i64 or an Option of one, or an error that fails the statement calling it. Coven marks every app function safe for triggers and views, which only the app's migrations declare.
Only a deterministic function can appear in an index, a CHECK or a generated column. One the schema uses is part of the store's schema, like a collation: at its version it must give the same result for the same arguments on every device and in every app version. Unicode lowercasing changes when a newer Unicode version gives a letter a lowercase form, so register the function at a new version then: a writable open rebuilds exactly the indexes that call it, and a CHECK, generated column or synced unique index that calls it refuses the new version, as for an order. A function that refuses some value with an error also stops this device from applying another device's write that holds it, so a function the schema uses should return a value for every argument.
A name SQLite or coven already has, such as lower or coven_applying, or one starting with coven_ or _coven_, fails the open with DbError::ReservedFunction, and a name given twice, ignoring case, with DbError::DuplicateFunction. A deterministic function takes at least one argument; one that takes none is a constant, better written as a literal, and fails the open with DbError::ConstantFunction. regexp is the app's to define: SQLite has none, and title REGEXP 'ban' calls regexp('ban', title). A panic in the function ends the process.
Declaring synced tables
The app tells coven which tables sync with a SyncedTable for each one. Every other table stays on the device: window positions, a search index, per-device settings.
fn tables() -> Vec<SyncedTable> {
vec![
SyncedTable::new("notes", RowIdentity::IndependentUuid),
SyncedTable::new("tags", RowIdentity::SharedKey),
SyncedTable::new("note_tags", RowIdentity::SharedKey)
.key_columns(["note_id", "tag_id"]),
]
}A declaration can also say how the table's rows get their audience (audience_column, audience_from), which of its columns reference a file (file_column), that only each row's creator may change it (owner_only), and which of its triggers are shared (shared_trigger). The schema itself is the app's: coven adds no columns to synced tables.
Keys
Every synced row is named by its table and its primary key, on every device. Two devices that insert the same key insert the same row. So each synced table declares what kind of key it has:
- Independent (
RowIdentity::IndependentUuid): each new row gets a random UUID, so rows made on different devices never collide. Ana and Ben each create a note offline; they get two notes. - Shared (
RowIdentity::SharedKey): the app derives the key from what makes the row unique, so the same thing made on two devices is one row. Ana and Ben, both offline, each add the tag "urgent". Both derive the keyurgent, their inserts are one row, and their cells merge.
Either kind can span several columns with key_columns; without it, the key is the one column id. An independent key over several columns needs a UUID in one of them.
Bind independent keys with UuidText, which writes a UUID as canonical lowercase text. Inserting anything else, including an uppercase UUID, fails with DbError::KeyNotUuid. A bare uuid::Uuid would bind as a blob.
let note = UuidText(Uuid::now_v7());
handle.write(move |sql| {
sql.execute("INSERT INTO notes (id, title) VALUES (?1, ?2)", (note, "Paint colors"))?;
sql.execute("INSERT INTO tags (id) VALUES ('urgent') ON CONFLICT DO NOTHING", [])?;
sql.execute("INSERT INTO note_tags (note_id, tag_id) VALUES (?1, 'urgent')", (note,))?;
Ok::<_, CovenError>(())
}).await?;Changing a row's primary key is a delete of the old row and an insert of the new one, on every device (Merge).
Coven refuses, when the database opens and after every migration, keys that can't mean the same thing on every device. The error names the table:
| Refused | Why | Error |
|---|---|---|
| An integer rowid key, or any key SQLite picks | two devices would pick the same number for different rows | SchemaError::GeneratedPrimaryKey |
| A synced table without a primary key | coven can't tell which row a change belongs to | SchemaError::NoPrimaryKey |
| A key column that allows NULL, in a synced or a device-local table | a key with a NULL in it names no row, and live queries learn which local rows a write changed by their keys | SchemaError::NullableKey |
| An independent key with no text column for its UUID | SchemaError::IndependentKeyNotUuid | |
A collation other than BINARY on a key, unique or foreign-key column | under COLLATE NOCASE, SQLite would hold "Urgent" and "urgent" as one row that coven treats as two | SchemaError::KeyCollation |
| Declared key columns that don't match the table's primary key | SchemaError::KeyColumns |
The rules for foreign keys, unique constraints, CHECKs and triggers are on the Merge page; Constraints lists what can't be expressed at all.
Writing
A write is one SQLite transaction through handle.write. Its closure gets a CovenWrite with execute, execute_batch, query_row and query, and coven's own steps, such as retrying an operation, which commit or roll back with the app's statements. If the closure returns an error, or a statement or a step fails, the whole transaction rolls back, even when the closure drops the error and returns success (DbError::StatementFailed, DbError::StepFailed). A conflict the app expects belongs in its SQL, with ON CONFLICT or INSERT OR IGNORE.
When a write commits, coven records in the same transaction:
- the rows as they now stand;
- a write record for upload: each row change with the columns it touched and their old and new values, the write's device and number, its timestamp, the schema version it was made with, and how far this device had read the store's sequence (
read_through); - the facts later owner checks need.
Here is the write record for a write that fixes a note's title and deletes a tag:
ana-phone, write 3, 2026-10-02 12:00:00.000 #0
read_through: slot 20
notes row 42 update title: "Grocry list" → "Grocery list"
tags "errands" deleteWriting needs no key and no storage. The record waits on the device unencrypted and unsigned; it is encrypted and signed when it is sent (Sync). Ana can use the app before she has set storage up at all.
A commit never waits for the network, but it can be refused for local reasons: a SQL constraint or trigger, a write into a circle the member isn't in, a change to another member's owned row, or an audience in the middle of a reload. The DbError names each. Coven also refuses SQL that would get around it: transaction control, PRAGMA, ATTACH or loading an extension (DbError::StatementForbidden), and anything that reads or changes coven's own objects (DbError::InternalTable). App tables, indexes and triggers may not start with _coven_ or coven_.
Rowids. In a table whose rowid isn't its primary key, the app can read the rowid but can't change it once the row exists. The rowid is SQLite's private address for the row: coven doesn't sync it, and VACUUM can renumber it.
A rollback that fails. If a transaction fails and rolling it back fails too, a connection may still be inside that transaction. Every later call on that handle, read-only or not, then fails with DbError::RollbackFailed until the store is reopened.
Reading
handle.read runs a closure on one of several read-only connections, in one transaction, so every statement in it sees the same committed state. Reads run alongside each other and alongside the writer, and a read after an awaited write sees that write. SQLite itself refuses writes on these connections.
let titles: Vec<String> = handle
.read(|sql| Ok::<_, CovenError>(sql.query("SELECT title FROM notes", [], |row| row.get(0))?))
.process(|mut titles| {
titles.sort();
Ok(titles)
})
.await?;process runs on a separate worker after the read has released its connection, so expensive work doesn't hold one. Fetch every database input in the read; process gets owned values and no SQL.
Live queries
handle.subscribe runs a query once, then again whenever a write commits that changes rows it read, whether the write was local or arrived from another device. Coven records the tables, columns and keys the query reads, so an unrelated write doesn't rerun it. The first next() returns the current result; an error is a result too and doesn't end the query, except the one saying the store closed or its handle failed: after it, next() returns None, so a loop over it ends. Dropping the LiveQuery ends it.
let mut notes: LiveQuery<_, CovenError> = handle.subscribe(|sql| {
Ok(sql.query("SELECT id, title FROM notes ORDER BY title", [], |row| {
Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))
})?)
});
while let Some(Ok(rows)) = notes.next().await {
show_notes(rows);
}handle.subscribe_reconfigurable(request, query) takes a request, such as a page number or a search term, that the app can replace through requests().set(..) without starting a new subscription. Each result says which request and revision it answers, and whether a new request, a write, or both caused it.
Coven's own state has ready-made live queries with the same results as their one-shot reads: subscribe_members, subscribe_lost_values, subscribe_pin_progress and others. They all work offline. The upload queue and pending records are live queries over coven's views instead (below).
Coven's state in SQL
A list often depends on coven's own state: albums still uploading come first, a filter keeps the rows this device has pinned, a person's row shows whether they are still a member. The app's SQL reads that state through views and tables like these, so the list stays one query over the database:
| View | One row per | Columns |
|---|---|---|
coven_files (a table) | file this device has a record of | id, audience, size, hash, coordinates, namespace |
coven_uploads | file waiting in this device's upload queue | file, position, paused, metered, retry_at, pending, bytes_confirmed, bytes_total, sending, move |
coven_sending | queued file this process is sending now | file, position |
coven_upload_pause | store, always exactly one row | paused |
coven_network | store, always exactly one row | usable, metered, unmetered_only |
coven_upload_queue (a table) | file waiting in this device's upload queue | file, position |
coven_pins (a table) | file this device keeps pinned | file |
coven_pin_requests (a table) | file a switch to the cloud asked this device to keep whole, not yet pinned | file |
coven_moves (a table) | file of a move to the cloud still moving or failed | move, state, file |
coven_pending | pending record | record, file, position, cause |
coven_invites | invite this device made that hasn't ended | invite, operation |
coven_members | member, former ones included | member, role, removed, is_self |
coven_devices | device a member registered | device, member, name, state, is_self |
The file views and tables name a file by its id, the id the app's file reference columns hold, so a join reads u.file = p.file:
let uploading_first: LiveQuery<_, CovenError> = handle.subscribe(|sql| {
Ok(sql.query(
"SELECT a.title FROM albums AS a
ORDER BY EXISTS(SELECT 1 FROM photos AS p JOIN coven_uploads AS u
ON u.file = p.file
WHERE p.album_id=a.id) DESC, a.title",
[],
|row| row.get::<_, String>(0),
)?)
});Each view is a query over coven's own tables, so it is never out of date, and a live query that reads one reruns when coven changes what it read: an upload finishing, a pin, a member removed. The views are read-only, and coven's tables themselves stay out of reach. pending and record read as a PendingRecord, member as a MemberId, device as a DeviceId, and file and id as a FileId. The API reference lists every column.
coven_pins is a table rather than a view so the app can follow it: a local trigger declared AFTER INSERT or AFTER DELETE ON coven_pins keeps a count of each album's pinned tracks in a device-local table, and "how many albums aren't kept offline" becomes an indexed read instead of a probe per track. The trigger may write only local tables, since a pin is this device's alone, and it reads only what the app's SQL may.
coven_upload_queue is a table for the same reason. Local triggers on it keep each album's queued count, first place and bytes in the app's own table, which an outbox reads by index however long the queue is; grouping coven_uploads by album reads every queued file, with what each waits for.
coven_pin_requests and coven_moves are tables for the same reason. A trigger on coven_pin_requests follows each file still to be kept whole after its switch to the cloud. One on coven_moves follows each file of a move as the move starts, as it fails, which is when the app can offer to cancel it, and as a cancel or a switch ends it. move reads as a MoveId, the id cancel_move takes.
Local triggers can follow coven_files too, the records themselves. When another device finishes uploading a track, its write switches the track's record to the cloud, changing only coordinates and hash; no row of the app's own tables changes. A trigger declared AFTER UPDATE OF coordinates ON coven_files runs as that write applies here, as well as on the uploader, so an album list can keep each album's count of stored tracks in a local table. Triggers on its INSERT and DELETE follow each record made and deleted, here and on other devices. Adding or dropping one changes nothing that syncs, so its migration is an addition.
Whether a file is being sent right now lasts only as long as the process, so nothing stores it: coven_uploads.sending and the coven_sending view read it from the upload worker's memory. A live query that reads either runs again each time an upload starts or ends, and a reopened store shows every file waiting. coven_sending holds one row per upload in flight, so counting an album's files being sent takes a lookup per file, however long the queue:
let sending: LiveQuery<_, CovenError> = handle.subscribe(move |sql| {
Ok(sql.query_row(
"SELECT count(*) FROM coven_sending AS s JOIN photos AS p ON s.file = p.file
WHERE p.album_id=?1",
[&album],
|row| row.get::<_, i64>(0),
)?)
});The bytes the provider has confirmed do last, so coven_uploads gives them as bytes_confirmed and bytes_total, NULL for a file sent in one request: an album's upload bar is SUM(bytes_confirmed) over SUM(bytes_total) for its photos. coven_pending gives each pending record's error as cause, the text coven writes with the record; a retry that fails the same way with a different error changes it, and a live query runs again for it. Opening clears every cause, so a record from an earlier run shows none until its failure happens again.