Why secret values live in the keychain and metadata in SQLite

envsec splits the job. Secret values go into the OS credential store (macOS Keychain, the Secret Service on Linux, Windows Credential Manager). Everything else, from key names to expiry dates, goes into a small SQLite database. This post explains why, what is in that database, and what happens when one of the two writes fails and the other doesn't.

What keychains are bad at

All three stores answer one question well: "give me the item with this exact name". Beyond that, each one is different:

• macOS. security find-generic-password returns one item, the first that matches. To enumerate items you would run security dump-keychain and parse the attributes of every item in the keychain, envsec's or not.
• Linux. secret-tool search matches attributes exactly, which gets closer. But its output includes the secret values of the matches, and exact matching cannot express "every key in contexts that start with myapp".
• Windows. cmdkey /list prints the stored targets, one block each. There is nothing in it to sort or filter by date.

You could store an expiry date in Secret Service attributes or in a Windows credential's comment field, but each OS would need its own scheme, and answering "what expires this week" would still mean reading every item. In envsec, each keychain operation is also a child process (on Windows, a PowerShell process that compiles a small C# class), so a listing built on the keychain gets slower with every secret you add.

So the OS-specific interface in envsec has three methods, set, get and remove, all by exact name. How those map to each OS is in One CLI, three keychains. Everything that needs listing, searching or sorting reads SQLite instead. envsec list, search, audit and shell completion never touch the keychain at all.

What goes in the database

The schema lives in packages/core, created on first open. Here it is reformatted for reading:

CREATE TABLE IF NOT EXISTS secrets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  env TEXT NOT NULL,              -- the context
  key TEXT NOT NULL,
  type TEXT NOT NULL DEFAULT 'string',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  expires_at TEXT DEFAULT NULL,   -- added later with ALTER TABLE
  UNIQUE(env, key)
);

CREATE TABLE IF NOT EXISTS commands (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  command TEXT NOT NULL,
  context TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS env_exports (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  context TEXT NOT NULL,
  path TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_env_exports_path
  ON env_exports(path);

• secrets has one row per context and key: timestamps and an optional expiry. The column is still called env because contexts were called environments in the first days of the project.
• commands holds saved command templates for envsec cmd, with {key} placeholders that are resolved when the command runs.
• env_exports records every file written by envsec env-file, so envsec audit can remind you that a plaintext copy exists, and drop the record once the file is gone.

Timestamps are UTC text in YYYY-MM-DD HH:MM:SS form, so "expires before X" is a plain string comparison. Every query uses ? placeholders with bound parameters, and every row is decoded with an Effect Schema instead of being cast to a type:

// envsec list -c myapp.dev
queryAll(
  SecretListRow,
  "SELECT key, updated_at, expires_at FROM secrets WHERE env = ? ORDER BY key",
  [env]
);

// envsec audit: everything that expires before the cutoff
queryAll(
  ExpiringSecretRow,
  "SELECT env, key, created_at, updated_at, expires_at FROM secrets " +
    "WHERE expires_at IS NOT NULL AND expires_at <= ? ORDER BY expires_at",
  [cutoff]
);

Where it lives, and who can read it

The default path is ~/.envsec/store.sqlite. You can change it with --db or the ENVSEC_DB environment variable; when both are set, the flag wins. The flag is read straight from argv before the CLI parser runs, because the database layer has to exist before any command does.

$# default
$envsec -c myapp.dev list
$# one-off, the flag wins over the variable
$envsec --db ~/scratch/envsec/store.sqlite -c myapp.dev list
$# for a whole shell session or a CI job
$export ENVSEC_DB=/tmp/ci-envsec/store.sqlite

Every time the store opens, it creates the file with 0600 before SQLite touches it, and sets the directory to 0700 if envsec created it or it is the default ~/.envsec:

// Only directories envsec creates (or owns, like ~/.envsec) are locked
// down: a custom --db can live in the current directory or a shared
// folder whose permissions are not ours to change.
const created = mkdirSync(dbDir, { mode: DIR_PERMISSIONS, recursive: true }); // 0o700
if (created !== undefined || dbDir === DEFAULT_DB_DIR) {
  chmodSync(dbDir, DIR_PERMISSIONS);
}
// Create the file ourselves so it never exists with the default umask
// permissions; SQLite gives its journal files the same mode.
closeSync(openSync(dbPath, "a", FILE_PERMISSIONS)); // 0o600
chmodSync(dbPath, FILE_PERMISSIONS);

const { DatabaseSync } = await loadSqlite();
const db = new DatabaseSync(dbPath);
db.exec(`PRAGMA busy_timeout = ${BUSY_TIMEOUT_MS}`); // 5000

Up to 1.1.2 the directory was set to 0700 on every run, whatever it was, so a --db in a project root or a shared folder locked that folder down. Since 1.1.3 an existing directory you point --db at keeps its permissions; only the database file is forced to 0600.

Next to the database sits completions.cache, a JSON file with your context names, key names and saved command names, written with 0600. It lets tab completion answer without opening SQLite.

What the metadata gives away

Values never go into SQLite. Everything else about your secrets does:

• context and key names, such as stripe.live-key;
• when each secret was created and last updated, and when it expires;
• saved command templates;
• the paths of .env files you exported.

The database is not encrypted. File permissions keep other users out, but any process running as you can read it and learn which secrets you have, and for which services. I think that trade-off is acceptable because names are rarely the secret part: the same names already sit in your source code (process.env.STRIPE_SECRET_KEY), in .env.example files and in CI configuration. In exchange you get listing, search, expiry and completion without unlocking the keychain.

Two things are worth doing anyway. Keep secrets out of saved command templates: a literal token typed into envsec cmd instead of a {key} placeholder ends up in SQLite as plaintext. And use full-disk encryption, which covers this file along with everything else in your home directory. The wider picture is in What envsec does not protect you from.

From sql.js to node:sqlite

Until October 2026 the metadata store used sql.js, SQLite compiled to WebAssembly. sql.js keeps the database in memory, so envsec read the whole file at startup and, after every write, exported the whole database and wrote it back:

const persist = (db: Database, dbPath: string) => {
  writeFileSync(dbPath, Buffer.from(db.export()), { mode: FILE_PERMISSIONS });
};

That design had three problems:

• Every write rewrote the file. Changing one timestamp meant serialising and writing every page. Batches only reduced how often that happened.
• No locking between processes. Each envsec process worked on its own in-memory copy. If two processes overlapped, whichever wrote last replaced the file with its copy and dropped the other's changes.
• Startup cost. As I measured in From 417 to 32 milliseconds, sql.js was a 23 MB package with a WASM module to instantiate on every start, and its WASM file could not be located at run time inside a compiled Bun binary.

The store now uses DatabaseSync from node:sqlite, which works without a flag from Node 22.13 and is also available in Bun. Writes go to the file through SQLite itself, with its own journal and file locks. A second envsec process that finds the database locked waits up to 5 seconds (busy_timeout) instead of failing. Batched commands (move, copy, rename, delete --all and load --batch) run inside BEGIN IMMEDIATE and COMMIT. On Node 22, loading the module prints an experimental warning; envsec filters out that one warning so it does not land on stderr on every run.

I won't put a number on this change alone. In the performance post it shared a step with dropping @effect/platform-node, and the numbers there are for both changes together.

Two stores, no shared transaction

There is no transaction that covers a keychain write and a SQLite write. The keychains have no transactions to join, and I did not try to build two-phase commit on top of security, secret-tool and PowerShell. What envsec does instead is pick an order for each operation and compensate where it can.

Writes: value first, then metadata

SecretStore.set writes the keychain first. If the metadata write then fails, it puts the keychain back the way it was: the previous value for an update, or no item for a new secret.

// When overwriting, keep the previous value so a failed metadata write
// can restore it instead of deleting the user's existing secret.
const exists = yield* metadata.get(context, key).pipe(
  Effect.as(true),
  Effect.catchTag("SecretNotFoundError", () => Effect.succeed(false))
);
const previous = exists
  ? yield* keychain.get(parsed.service, parsed.account) /* … */
  : Option.none<string>();
yield* keychain.set(parsed.service, parsed.account, encodeValue(value));
yield* metadata.upsert(context, key, expiresAt).pipe(
  Effect.catch((metadataError) =>
    Option.match(previous, {
      onNone: () => keychain.remove(parsed.service, parsed.account),
      onSome: (raw) => keychain.set(parsed.service, parsed.account, raw),
    }).pipe(Effect.ignore, Effect.andThen(Effect.fail(metadataError)))
  )
);

The previous value matters. An earlier version always removed the keychain item when the metadata write failed, which for an update deleted the value you already had. The compensation is best effort: if it fails too, its error is ignored and you see the original metadata error.

Reads: metadata first

SecretStore.get checks for the metadata row before it touches the keychain. A secret with no row does not exist as far as envsec is concerned, even if the keychain item is there. A row with no item gets its own message, because that state has a clear fix:

$# remove the value behind envsec's back
$$ security delete-generic-password -s envsec.blogdemo.dev.api -a token
$password has been deleted.
$$ envsec -c blogdemo.dev get api.token
$▲ Secret "api.token" has metadata but is missing from the OS keychain.
$● To clean up stale metadata, run: envsec delete -c blogdemo.dev api.token
$✖ Secret "api.token" has metadata in context "blogdemo.dev" but is missing from the OS keychain. Run: envsec delete -c blogdemo.dev api.token
$$ envsec -c blogdemo.dev delete api.token --yes
$× Secret "api.token" removed from context "blogdemo.dev"

That output is from my machine, with a throwaway context. delete works here because it ignores keychain errors on removal and then deletes the row. envsec doctor also counts rows whose keychain item cannot be read.

Batches commit what they did

Batched commands write metadata inside one SQLite transaction while the keychain work happens one secret at a time. If the batch fails halfway, envsec commits the transaction instead of rolling it back. That sounds backwards, but the keychain changes for the finished secrets have already happened, and rolling back the rows would hide them.

What is still not covered

The weak spot is the "keychain item with no row" case. It happens if envsec is killed between the two writes of an add, or if a keychain removal fails during delete. The value is still in your keychain, but list does not show it, get refuses it, and doctor does not look for it: its orphan check only goes from rows to items. Running the same envsec add again stores the value and recreates the row. There is no command that rebuilds the database from the keychain, and given how differently the three stores enumerate items, I don't plan to pretend there could be a reliable one.

The same applies if you lose the database file: your values are still in the keychain, but envsec no longer knows their names. Back up ~/.envsec along with the rest of your home directory.

In short

• The keychain is good at protecting one value by exact name. envsec asks it for nothing else.
• Names, timestamps, expiry, saved commands and .env export records live in SQLite at ~/.envsec/store.sqlite (or --db, or ENVSEC_DB), with 0700 and 0600 permissions and bound parameters everywhere.
• That database reveals which secrets you have, not their values. It is not encrypted.
• node:sqlite replaced sql.js: real file writes, locking between processes, and no WASM to load.
• There is no transaction across both stores. Writes go value first and roll back on a metadata failure; a crash at the wrong moment can still leave a value in the keychain that envsec does not list.