Data Storage

Data Storage

SnipVault stores everything in a single local SQLite database. There is no cloud, no account, and no telemetry of your snippets.

Location

Runtime	Path
Web app	./data/snippets.db (relative to the project working directory)
Desktop (default)	see per-OS defaults below — configurable

On the desktop, the database location is chosen on first launch and can be changed later in Settings (see User Guide). The active path is saved in a config.json next to the default location. This lets you keep the database wherever you like — including a cloud-synced folder. The default location, used when you pick "create a new database", is:

OS	Default path
Windows	C:\Users\<you>\AppData\Local\snipvault\snippets.db
macOS	~/Library/Application Support/snipvault/snippets.db
Linux	~/.local/share/snipvault/snippets.db

The default comes from the OS local data directory (dirs::data_local_dir()) plus a snipvault/ folder. Both runtimes enable WAL mode, so you may also see snippets.db-wal and snippets.db-shm files alongside the main database.

On the web app / self-hosted server, the path defaults to ./data/snippets.db but can be relocated by setting the SNIPVAULT_DB_PATH environment variable to a full file path (added in v2.4.0) — handy for pointing the Docker container or a bare-Node server at a specific volume (the parent directory is created if missing).

Since v3.0.0, a multi-user server (set via SNIPVAULT_TOKENS) stores each user's library in its own file at data/users/<user>/snippets.db — the same schema, one file per user, so isolation is structural. The default single-user file (./data/snippets.db / SNIPVAULT_DB_PATH) is unchanged. See Syncing → Multiple users.

Backups

In the desktop app, use Settings → Back up database to write a timestamped copy anywhere you choose. Backups use SQLite's online backup API, so they're consistent even while the app is running. To restore, either point the app at the backup file (Settings → Change database) or copy it over your active snippets.db while the app is closed.

Since v2.6.0 there's also a managed Backups folder (<app_dir>/backups/) holding rotating, timestamped snapshots (snipvault-YYYYMMDD-HHMMSS.db), with Back up now, Open folder, a validated one-click Restore…, and an opt-in back-up-on-launch (at most once a day). See the User Guide. On the server, back up the file at SNIPVAULT_DB_PATH (or the Docker volume) with a consistent .backup / VACUUM INTO — see docs/self-hosting.md.

Manual copy

Aside from the in-app backup above, you can copy the snippets.db file (and, to be safe, the -wal/-shm siblings) while the app is closed. Restoring is just putting the file back at the same path.

Schema

The database has a single table, snippets:

CREATE TABLE IF NOT EXISTS snippets (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    title       TEXT NOT NULL,
    description TEXT DEFAULT '',
    code        TEXT NOT NULL,
    language    TEXT NOT NULL,
    tags         TEXT DEFAULT '[]',            -- JSON array of strings
    favorite     INTEGER NOT NULL DEFAULT 0,   -- 0/1 pin flag (added in v1.5.0)
    model        TEXT NOT NULL DEFAULT '',     -- model/target metadata (added in v2.0.0)
    kind         TEXT NOT NULL DEFAULT 'prompt', -- 'prompt' | 'code' entry kind (added in v2.3.0)
    color        TEXT NOT NULL DEFAULT '',     -- per-prompt color tag, '' = none (added in v3.5.x)
    template     INTEGER NOT NULL DEFAULT 0,   -- 0/1 reusable-template flag (added in v3.5.x)
    last_device  TEXT NOT NULL DEFAULT '',     -- name of device that last wrote the row (added in v3.5.x)
    copy_count   INTEGER NOT NULL DEFAULT 0,   -- times copied (added in v2.0.0)
    last_used_at TEXT,                         -- last copied, UTC; NULL if never (added in v2.0.0)
    uuid         TEXT,                          -- stable cross-machine id for sync (added in v2.2.0)
    deleted      INTEGER NOT NULL DEFAULT 0,   -- 0/1 soft-delete tombstone (added in v2.2.0)
    created_at   TEXT DEFAULT (datetime('now')),
    updated_at   TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_snippets_language   ON snippets(language);
CREATE INDEX IF NOT EXISTS idx_snippets_created_at ON snippets(created_at);
CREATE UNIQUE INDEX IF NOT EXISTS idx_snippets_uuid ON snippets(uuid); -- (added in v2.2.0)

The snippet_revisions table (v2.7.0)

Prompt history is an append-only table, separate from snippets:

CREATE TABLE IF NOT EXISTS snippet_revisions (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    snippet_uuid TEXT NOT NULL,   -- links to snippets.uuid
    title TEXT, description TEXT, code TEXT, language TEXT,
    tags TEXT, model TEXT, kind TEXT,
    saved_at     TEXT             -- the updated_at this version carried while live
);
CREATE INDEX IF NOT EXISTS idx_revisions_uuid ON snippet_revisions(snippet_uuid, id);

Each content-changing update_snippet first writes the pre-edit row here (no-op saves are skipped), and the newest ~50 per snippet_uuid are kept (older ones pruned in the same transaction). Restoring a version writes it back through the normal update path — which itself captures the then-current state — so nothing is lost. This table is local to each database and is not part of sync (no SyncRecord shape, no protocol change), keeping the newest-wins model untouched.

Field notes

• tags is stored as a JSON-encoded array of strings (e.g. ["react","hooks"]), not a separate table. Both backends parse it to/from a real array. Tag filtering uses a LIKE '%"tag"%' match against this JSON text.
• favorite is an integer 0/1 pin flag exposed to the frontend as a boolean. Pinned snippets sort to the top (ORDER BY favorite DESC, created_at DESC).
• model is free-text model/target metadata (empty string if unset); it's included in All Fields search.
• kind (v2.3.0) is the entry type — 'prompt' (default) or 'code'. It governs whether the UI shows the ~token estimate. Any value other than 'code' is normalized to 'prompt' on write, and rows created before v2.3.0 default to 'prompt'.
• color (v3.5.x) is a per-prompt color tag from a small fixed palette (red, orange, amber, green, teal, blue, violet, pink), or '' for none. Validated to the palette on every write path (anything else becomes ''). Purely a visual aid — changing it doesn't add a history revision.
• template (v3.5.x) is a 0/1 flag marking a reusable template (offered as a starting point in the New menu). Metadata like favorite; not part of history.
• last_device (v3.5.x) is the friendly name of the device that last wrote the row ('' = unknown). The desktop app stamps it on create/update/restore from its configured device name; the field is length-capped (64 chars) and travels through sync, where a receiver preserves the remote stamp rather than overwriting it. The web app has no device identity, so web writes leave it ''.
• copy_count / last_used_at track usage: copying a prompt increments the count and stamps the time. They're set by the copy endpoint/command, not by create/update.
• created_at / updated_at are TEXT timestamps in YYYY-MM-DD HH:MM:SS (UTC). updated_at is refreshed on every update, and it's the clock sync uses for "newest edit wins".
• uuid (v2.2.0) is a stable identifier assigned on create, so the same snippet is recognised across machines during Syncing. It's unique; rows created before v2.2.0 are backfilled with one on upgrade.
• deleted (v2.2.0) is a 0/1 soft-delete tombstone. Deleting a snippet sets deleted = 1 (and bumps updated_at) instead of removing the row — so the deletion can propagate on the next sync. Tombstoned rows are hidden from every read (WHERE deleted = 0) and don't appear in the app. Undo-after-delete simply clears the flag.
• Tags are normalized to lowercase, trimmed, de-duplicated, and capped at 20 per snippet.
• title is limited to 255 characters; language must be one of the recognized values (see API and Commands).

Migrations

The schema is created idempotently (CREATE TABLE IF NOT EXISTS …) on startup by both backends. Columns added after the initial schema are applied on startup with a guarded ALTER TABLE … ADD COLUMN that is ignored if the column already exists — this is how the favorite column (v1.5.0), the model, copy_count, and last_used_at columns (v2.0.0), the uuid / deleted columns (v2.2.0), and the kind column (v2.3.0) are added to databases created by earlier versions, so existing snippets.db files upgrade automatically with no data loss. The kind column's DEFAULT 'prompt' fills existing rows automatically, so no backfill pass is needed (unlike uuid).

The same guarded-ALTER mechanism adds the color, template, and last_device columns (v3.5.x), each with a DEFAULT that backfills existing rows (no separate pass). They're sync-safe — appended last, carried in the SyncRecord shape, and merged newest-wins like every other field — so no protocol bump was needed.

The snippet_revisions table (v2.7.0) is likewise created idempotently on startup by both backends. Its columns are unchanged — color/template/last_device are metadata, not content, so they're deliberately not captured in history.

The v2.2.0 migration also backfills a uuid for every existing row and then creates the unique index (SQLite can't add a UNIQUE column with a single ALTER, so it's added nullable, filled, then indexed). This upgrade is one-way: once a database has been opened by v2.2.0 it shouldn't be reopened by an older build.

The scripts/ folder also contains the historical SQL migrations for reference:

• scripts/001-create-snippets-table.sql
• scripts/002-add-tags-column.sql

The schema is intentionally identical between the web and desktop runtimes, so a snippets.db is portable between them.