Sqlite Schema Design

2. Require A Stable, Globally Unique Primary Key

For synced tables:

• always declare an explicit primary key
• prefer TEXT PRIMARY KEY NOT NULL
• generate ids with cloudsync_uuid()
• do not use auto-incrementing integer ids

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.

3. Make Inserts Merge-Safe With Real Defaults

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:

• every non-primary-key NOT NULL column should have a meaningful DEFAULT
• avoid required columns that only application code knows how to populate
• prefer simple scalar defaults over nullable columns when the field is logically always present

Good:

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.

4. Keep The Local And Cloud Schemas Identical

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:

• treat local and remote DDL as one contract
• do not introduce "client-only" columns on synced tables
• do not rely on drift being harmless
• ensure migrations are applied consistently before sync resumes

If a field is only needed locally, it likely belongs in a separate non-synced table.

5. Be Conservative With Foreign Keys

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:

• make child foreign key columns nullable when the relationship is optional
• if a foreign key column has a DEFAULT, that default must be NULL or reference an actually valid parent row
• avoid fake sentinel ids such as 'root' unless that parent row is guaranteed to exist everywhere
• index the child foreign key columns

Prefer ownership patterns that tolerate out-of-order arrival between related rows.

6. Avoid Triggers And Implicit Write Logic On Synced Tables

SQLite Sync best practices advise minimizing triggers because they make replicated writes harder to reason about.

For synced tables:

• avoid triggers that mutate synced columns
• avoid hidden side effects on insert or update
• prefer explicit application writes
• keep derived or bookkeeping writes in non-synced tables if possible

If a trigger is unavoidable, review it as part of the replication design, not as a local SQLite convenience.

7. Scope Uniqueness For RLS And Multi-Tenant Sync

The introduction docs emphasize row-level security and multi-tenant access patterns.

That changes uniqueness design:

• if the real rule is "unique per workspace/user/team", encode that as a composite constraint
• avoid globally unique business keys unless they truly span all tenants

Prefer:

UNIQUE (workspace_id, slug)