# Sqlite Schema Design

Design or review schemas for `crates/cloudsync` using SQLite Sync constraints, not...

- Repository: https://github.com/fastrepl/anarlog (9738 stars)
- Category: Design
- License: MIT
- Last updated: 2026-11-01
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add fastrepl/anarlog --skill sqlite-schema-design -g -a claude-code -y`
Codex: `npx skills add fastrepl/anarlog --skill sqlite-schema-design -g -a codex -y`
Cursor: `npx skills add fastrepl/anarlog --skill sqlite-schema-design -g -a cursor -y`
Gemini CLI: `npx skills add fastrepl/anarlog --skill sqlite-schema-design -g -a gemini-cli -y`
Claude Code plugin marketplace: `/plugin marketplace add fastrepl/anarlog`

## 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.

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

## 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.

```sql
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

The rest: https://github.com/fastrepl/anarlog/blob/HEAD/.agents/skills/sqlite-schema-design/SKILL.md