Desktop SQLite

Desktop apps need three decisions before schema design: where data lives, how migrations run, and how users recover data.

Data Paths

Stack	Default shape
Tauri	app data/config path plus SQL plugin migrations and explicit permissions
Electron	`app.getPath('userData')` with app-owned subdirectories
Native Rust/Go/.NET	OS app data directory with documented backup/export path

Do not store durable user data in the current working directory, temp directories, or cache directories.

Schema

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

CREATE TABLE app_meta (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

CREATE TABLE documents (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

INSERT INTO app_meta (key, value) VALUES ('schema_version', '1');

Backup Command Shape

type BackupResult = {
  path: string;
  rowCount: number;
  integrity: "ok";
  createdAt: string;
};

The UI should show where the backup went and whether validation passed.

Key Storage

Use platform key storage for tokens and encryption keys. For Tauri, Stronghold is a candidate. For Electron, use OS keychain libraries rather than putting secrets in JSON next to the DB.

Gotcha: userData can accumulate unrelated Chromium/session files in Electron apps. Keep app-owned DBs and backups in deliberate subdirectories.