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.