// Design or review schemas for `crates/cloudsync` using SQLite Sync constraints, not generic SQLite advice. Use when adding synced tables, changing synced columns, or planning CloudSync-safe migrations.
| name | sqlite-schema-design |
| description | Design or review schemas for `crates/cloudsync` using SQLite Sync constraints, not generic SQLite advice. Use when adding synced tables, changing synced columns, or planning CloudSync-safe migrations. |
Design tables that behave correctly under SQLite Sync's CRDT replication model.
This skill is specifically for CloudSync-backed schemas:
cloudsync_init(...)cloudsync_enable(...)cloudsync_uuid()cloudsync_begin_alter(...) and cloudsync_commit_alter(...)Do not treat this as ordinary SQLite schema design. SQLite Sync imposes extra rules around keys, defaults, foreign keys, and schema evolution.
Before proposing DDL, classify the table:
Only apply this skill to synced tables or to tables that may become synced soon.
For synced tables:
TEXT PRIMARY KEY NOT NULLcloudsync_uuid()SQLite Sync docs explicitly recommend UUIDv7-style globally unique ids for CRDT workloads. Integer autoincrement ids are a bad fit because multiple devices can create rows independently.
CREATE TABLE document (
id TEXT PRIMARY KEY NOT NULL DEFAULT (cloudsync_uuid()),
workspace_id TEXT NOT NULL,
title TEXT NOT NULL DEFAULT '',
body TEXT NOT NULL DEFAULT '',
archived INTEGER NOT NULL DEFAULT 0,
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
updated_at INTEGER NOT NULL DEFAULT (unixepoch())
) STRICT;
If the environment cannot use a function call in DEFAULT, generate the id in application code, but still use cloudsync_uuid() as the canonical id strategy.
SQLite Sync best practices call out a non-obvious CRDT constraint: merges can happen column-by-column, so missing values are much more dangerous than in a single-node SQLite app.
For synced tables:
NOT NULL column should have a meaningful DEFAULTGood:
title TEXT NOT NULL DEFAULT ''
archived INTEGER NOT NULL DEFAULT 0
sort_order INTEGER NOT NULL DEFAULT 0
Bad:
title TEXT NOT NULL
archived INTEGER NOT NULL
without defaults on a synced table.
The getting-started docs require the local synced database and the SQLite Cloud database to share the same schema.
When designing or reviewing a schema:
If a field is only needed locally, it likely belongs in a separate non-synced table.
SQLite Sync best practices explicitly warn that foreign keys can interact poorly with CRDT replication.
Use foreign keys on synced tables only when the integrity guarantee is worth the operational cost.
If you keep them:
DEFAULT, that default must be NULL or reference an actually valid parent row'root' unless that parent row is guaranteed to exist everywherePrefer ownership patterns that tolerate out-of-order arrival between related rows.
SQLite Sync best practices advise minimizing triggers because they make replicated writes harder to reason about.
For synced tables:
If a trigger is unavoidable, review it as part of the replication design, not as a local SQLite convenience.
The introduction docs emphasize row-level security and multi-tenant access patterns.
That changes uniqueness design:
Prefer:
UNIQUE (workspace_id, slug)
over:
slug TEXT UNIQUE
when data is tenant-scoped.
Schema changes for synced databases are not ordinary ALTER TABLE work. Use:
cloudsync_begin_alter('table_name')cloudsync_commit_alter('table_name')Design implications:
When reviewing a migration plan, reject any synced-table schema change that skips the CloudSync alter flow.
SQLite Sync already exposes its own metadata and helpers:
cloudsync_siteid()cloudsync_db_version()cloudsync_version()cloudsync_is_enabled()Do not duplicate these concepts as app-managed columns on synced tables unless there is a very specific product requirement.
The API set includes network setup and sync transport functions such as:
cloudsync_network_init(...)cloudsync_network_set_token(...)cloudsync_network_set_apikey(...)cloudsync_network_sync(...)cloudsync_network_has_unsent_changes()These matter operationally, but they are not substitutes for sound schema design.
Do not design tables that assume:
Assume offline creation, delayed delivery, retries, and independent merges.
Unless the user explicitly asks otherwise:
TEXT PRIMARY KEY NOT NULLcloudsync_uuid()STRICT tablesDEFAULT on every non-key NOT NULL columnWhen reviewing a CloudSync schema, ask:
cloudsync_begin_alter / cloudsync_commit_alter?Create the next desktop changelog entry when asked to add a changelog file or prepare the next release note under `packages/changelog/content`. Use this when the task is specifically about determining the next version and creating the markdown entry.
Migrate a TinyBase table to SQLite. Use when asked to move a data domain (e.g. templates, vocabs) from the TinyBase store to the app SQLite database.
Build SQLite-backed reactive UI in `apps/desktop` using stable patterns for reads, selection, forms, writes, and loading states. Use when implementing or reviewing screens built on `useDrizzleLiveQuery` and SQLite mutations.
Choose between in-process `tower::ServiceExt::oneshot` tests and real `tokio::net::TcpListener` server tests for Axum services in this repository.
Scaffold a new Tauri plugin in this repository when asked to add or create a plugin under `plugins/`. Use this for plugin generation and repository integration work, not for editing an existing plugin unless the request is specifically about bringing a freshly generated plugin in line with project conventions.
Live transcription, recording, and audio tools CLI. Use when the user needs to transcribe audio, record, play audio files, or manage speech-to-text models.