Sqlite Schema Design

Goal

Design tables that behave correctly under SQLite Sync's CRDT replication model.

This skill is specifically for CloudSync-backed schemas:

• tables are initialized through cloudsync_init(...)
• sync is enabled with cloudsync_enable(...)
• identifiers should be generated with cloudsync_uuid()
• schema changes must go through cloudsync_begin_alter(...) and cloudsync_commit_alter(...)
• local and cloud databases must keep the same schema

Do not treat this as ordinary SQLite schema design. SQLite Sync imposes extra rules around keys, defaults, foreign keys, and schema evolution.

Workflow

1. Decide Whether The Table Is Synced

Before proposing DDL, classify the table:

• synced application data: must satisfy SQLite Sync constraints
• local-only cache or ephemeral state: should usually stay out of CloudSync

Only apply this skill to synced tables or to tables that may become synced soon.

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.