Skip to content

🗄️ Database Schema Ground Truth ​

The database tables are initialized via versioned SQLite migrations located in migrations/ and executed at server boot by MigrationRunner. Ground truth as of v0.0.2.8 (migrations 0001-0010). Note that migration 0004_key_ledger performs its SHA-256 key backfill in code (src/server/database/keyLedger.ts), migration 0005_attachment_blobs migrates legacy attachment payloads to native SQLite BLOB storage (src/server/database/attachmentBlobs.ts), migration 0006_vault_tags adds tags JSON columns and owner indices, migration 0007_composite_item_features adds secondary login URIs (uris) and password generation revision history (password_history) to vault_pearls, migration 0008_note_attachments adds child binary attachment support (attachments TEXT DEFAULT '[]') to vault_secure_notes, migration 0009_attachment_integrity enforces Bitwarden-style item integrity (adding attachments to vault_ssh_keys and parent linkage/quarantine columns to vault_secure_attachments), and migration 0010_import_lifecycle adds ephemeral import tracking columns (imported_at, import_batch_id) and batch indices across vault_pearls, vault_secure_notes, and vault_ssh_keys.

ShellGuard maintains two separate SQLite database files:

  1. DATA_DIR/db.sqlite: The primary operational database, encrypted whole-DB with SQLCipher (Layer 3) and per-row metadata encryption (Layer 2).
  2. DATA_DIR/audit.sqlite: The forensic audit log database, append-only and strictly isolated from data backups and restores.

🐚 Primary Database (db.sqlite) ​

1. lobsters (Human Identities) ​

Stores registered users. The key_hash is a constant-time SHA-256 hash of the sovereign hu- identity key.

sql
CREATE TABLE IF NOT EXISTS lobsters (
  uuid         TEXT PRIMARY KEY,
  username     TEXT NOT NULL UNIQUE,
  display_name TEXT,
  key_hash     TEXT NOT NULL UNIQUE,
  created_at   TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_lobsters_key_hash ON lobsters(key_hash);

2. api_tokens (Session Bearer Tokens) ​

Tracks active bearer sessions issued during user authentication. owner_uuid holds the lobster uuid for human sessions - or, for agent sessions, the agent row id (re-pointed from raw lb- keys in migration 0004).

sql
CREATE TABLE IF NOT EXISTS api_tokens (
  key        TEXT PRIMARY KEY,
  owner_uuid TEXT NOT NULL,
  owner_type TEXT NOT NULL,
  created_at TEXT NOT NULL,
  expires_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_api_tokens_expires_at ON api_tokens(expires_at);
CREATE INDEX IF NOT EXISTS idx_api_tokens_owner ON api_tokens(owner_uuid, owner_type);

3. vault_pearls (Logins & Credentials) ​

Stores login credentials. secret, totp_secret, attachments, custom_fields, and password_history contain client-side ShellCryption ciphertext envelopes. uris is sealed under Layer 2 metadata encryption. imported_at and import_batch_id remain unencrypted relational columns for indexed batch queries (Migration 0010).

sql
CREATE TABLE IF NOT EXISTS vault_pearls (
  id               TEXT PRIMARY KEY,
  owner_uuid       TEXT NOT NULL,
  title            TEXT NOT NULL,
  secret           TEXT NOT NULL,
  username         TEXT DEFAULT '',
  url              TEXT DEFAULT '',
  type             TEXT DEFAULT 'password',
  category         TEXT DEFAULT '',
  notes            TEXT DEFAULT '',
  totp_secret      TEXT DEFAULT '',
  attachments      TEXT DEFAULT '[]',
  custom_fields    TEXT DEFAULT '',
  tags             TEXT DEFAULT '[]',
  uris             TEXT DEFAULT '[]',
  password_history TEXT DEFAULT '[]',
  created_at       TEXT NOT NULL,
  imported_at      TEXT DEFAULT NULL,
  import_batch_id  TEXT DEFAULT NULL,
  FOREIGN KEY (owner_uuid) REFERENCES lobsters(uuid)
);

CREATE INDEX IF NOT EXISTS idx_vault_pearls_owner_created ON vault_pearls(owner_uuid, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_pearls_import_batch ON vault_pearls(owner_uuid, import_batch_id);

4. vault_secure_notes (Markdown Notes) ​

Encrypted free-form text and markdown documentation. Linked child binary attachments stored in attachments JSON array (Migration 0008).

sql
CREATE TABLE IF NOT EXISTS vault_secure_notes (
  id              TEXT PRIMARY KEY,
  owner_uuid      TEXT NOT NULL,
  title           TEXT NOT NULL,
  content         TEXT NOT NULL,
  category        TEXT DEFAULT '',
  custom_fields   TEXT DEFAULT '',
  tags            TEXT DEFAULT '[]',
  attachments     TEXT DEFAULT '[]',
  created_at      TEXT NOT NULL,
  imported_at     TEXT DEFAULT NULL,
  import_batch_id TEXT DEFAULT NULL,
  FOREIGN KEY (owner_uuid) REFERENCES lobsters(uuid)
);

CREATE INDEX IF NOT EXISTS idx_vault_secure_notes_owner_created ON vault_secure_notes(owner_uuid, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_notes_import_batch ON vault_secure_notes(owner_uuid, import_batch_id);

5. vault_ssh_keys (SSH Private Keys) ​

Encrypted SSH keys and certificates.

sql
CREATE TABLE IF NOT EXISTS vault_ssh_keys (
  id              TEXT PRIMARY KEY,
  owner_uuid      TEXT NOT NULL,
  title           TEXT NOT NULL,
  key_value       TEXT NOT NULL,
  username        TEXT DEFAULT '',
  category        TEXT DEFAULT '',
  custom_fields   TEXT DEFAULT '',
  tags            TEXT DEFAULT '[]',
  attachments     TEXT DEFAULT '[]',
  created_at      TEXT NOT NULL,
  imported_at     TEXT DEFAULT NULL,
  import_batch_id TEXT DEFAULT NULL,
  FOREIGN KEY (owner_uuid) REFERENCES lobsters(uuid)
);

CREATE INDEX IF NOT EXISTS idx_vault_ssh_keys_owner_created ON vault_ssh_keys(owner_uuid, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_keys_import_batch ON vault_ssh_keys(owner_uuid, import_batch_id);

6. vault_secure_attachments (File Attachments) ​

Stores binary file attachments under the Reference Model. Linked from vault_pearls.attachments and vault_secure_notes.attachments JSON arrays. Phase 19 (v0.0.2.1) & Phase 20: file_data is a native BLOB holding raw ShellCryption envelope bytes (legacy TEXT rows re-encoded in code by attachmentBlobs.ts), with size_bytes powering the 1000MB (1GB) grotto quota (500MB per-file limit).

sql
CREATE TABLE IF NOT EXISTS vault_secure_attachments (
  id             TEXT PRIMARY KEY,
  owner_uuid     TEXT NOT NULL,
  title          TEXT NOT NULL,
  file_data      BLOB NOT NULL,
  size_bytes     INTEGER NOT NULL DEFAULT 0,
  file_name      TEXT DEFAULT '',
  mime_type      TEXT DEFAULT '',
  category       TEXT DEFAULT '',
  parent_id      TEXT,
  parent_type    TEXT,
  is_quarantined INTEGER DEFAULT 0,
  quarantined_at TEXT,
  created_at     TEXT NOT NULL,
  FOREIGN KEY (owner_uuid) REFERENCES lobsters(uuid)
);

CREATE INDEX IF NOT EXISTS idx_vault_secure_attachments_owner_created ON vault_secure_attachments(owner_uuid, created_at DESC);

Phase 19 & 20 wire contract: multipart streaming uploads (Busboy, 500MB per-file ceiling ATTACHMENT_MAX_MB), metadata-only list responses, chunked 1MB BLOB downloads via GET /api/attachments/:id/file, 413 on 1000MB (GROTTO_QUOTA_MB) quota breach.

7. agent_keys (LobsterKeys / AI Agent Delegation) ​

Issued API keys (lb- prefix) allowing scoped, programmatic access to autonomous agents.

sql
CREATE TABLE IF NOT EXISTS agent_keys (
  id              TEXT PRIMARY KEY,
  name            TEXT NOT NULL,
  description     TEXT,
  key_hash        TEXT NOT NULL UNIQUE,
  key_fingerprint TEXT,
  permissions     TEXT NOT NULL,
  expiration_type TEXT NOT NULL,
  expiration_date TEXT,
  rate_limit      INTEGER,
  is_active       INTEGER DEFAULT 1,
  owner_uuid      TEXT NOT NULL DEFAULT '',
  revoked_at      TEXT,
  revoked_by      TEXT,
  revoke_reason   TEXT,
  created_at      TEXT NOT NULL,
  last_used       TEXT
);

CREATE INDEX IF NOT EXISTS idx_agent_keys_key_hash ON agent_keys(key_hash);
CREATE INDEX IF NOT EXISTS idx_agent_keys_active ON agent_keys(is_active);
CREATE INDEX IF NOT EXISTS idx_agent_keys_owner ON agent_keys(owner_uuid);

IMPORTANT

Hash-only ledger (v0.0.1.9). The plaintext lb- column was retired in migration 0004_key_ledger: legacy keys were SHA-256 hashed in place (live keys keep authenticating), and newly minted LobsterKeys are returned exactly once in the mint response - only key_hash and key_fingerprint (first 12 hash chars, what key cards display) ever touch the database. All lookups are constant-time comparisons against the stored hash (WHERE key_hash = ?).

Pod purity. Migration 0004 also rebuilt the four vault tables to drop the hardcoded DEFAULT 'Personal' from every category column - the default is '' (uncategorized), matching the client's normalizePod() semantics. Zero hardcoded pods, all the way down.

Pod tallies = primary items only. Pod/folder counts are computed client-side (buildPodTree, over the already-decrypted in-memory corpus) and filter to the primary types (password / note / key). Attachment rows — children linked via the parent's attachments ID array — never inflate folder badges. The server's GET /api/health counts are instance-health totals, not pod tallies. (Receipt: tests/unit/keyGen.test.ts pod-decoupling assertions, Phase 18.)

8. Preferences & System Settings ​

Stores non-secret user preferences and instance configuration.

sql
-- Per-owner UI preferences
CREATE TABLE IF NOT EXISTS settings (
  owner_uuid TEXT NOT NULL DEFAULT '',
  key        TEXT NOT NULL,
  value      TEXT NOT NULL,
  PRIMARY KEY (owner_uuid, key)
);

-- SuperLobster instance settings (backup schedule, retention policies)
CREATE TABLE IF NOT EXISTS system_settings (
  key        TEXT PRIMARY KEY,
  value      TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

9. schema_migrations ​

Tracks applied schema migrations.

sql
CREATE TABLE IF NOT EXISTS schema_migrations (
  version    INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  applied_at TEXT NOT NULL
);

🩺 Forensic Audit Database (audit.sqlite) ​

Stored as an independent, append-only SQLite database initialized by src/server/database/schema.ts.

audit_logs ​

sql
CREATE TABLE IF NOT EXISTS audit_logs (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  timestamp   TEXT NOT NULL,
  event_type  TEXT NOT NULL,
  actor       TEXT,
  actor_type  TEXT,
  resource    TEXT,
  action      TEXT NOT NULL,
  outcome     TEXT NOT NULL,
  ip_address  TEXT,
  user_agent  TEXT,
  details     TEXT
);

CREATE INDEX IF NOT EXISTS idx_audit_timestamp ON audit_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_event_type ON audit_logs(event_type);
CREATE INDEX IF NOT EXISTS idx_audit_actor ON audit_logs(actor);
CREATE INDEX IF NOT EXISTS idx_audit_outcome ON audit_logs(outcome);

Habitat Manifest Schema (shellguard_vault_export.json) ​

Inside Habitat Full-Archive Mobility ZIPs, ShellGuard emits shellguard_vault_export.json.

json
{
  "items": [
    {
      "id": "uuid",
      "title": "GitHub",
      "type": "password",
      "attachments": "[\"att-uuid-1\"]"
    }
  ],
  "attachment_manifest": [
    {
      "id": "att-uuid-1",
      "file_name": "document.pdf",
      "parent_item_id": "uuid",
      "parent_type": "password",
      "size_bytes": 1048576,
      "sha256_checksum": "deadbeef1234567890abcdef...",
      "relative_path": "attachments/Work/GitHub__[a1b2c3d4]/document.pdf"
    }
  ]
}

Sovereign Zero-Knowledge Secrets Vault. Released under AGPL-3.0. • Privacy Policy