🗄️ 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:
DATA_DIR/db.sqlite: The primary operational database, encrypted whole-DB with SQLCipher (Layer 3) and per-row metadata encryption (Layer 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.
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).
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).
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).
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.
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).
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 viaGET /api/attachments/:id/file,413on 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.
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'sattachmentsID array — never inflate folder badges. The server'sGET /api/healthcounts are instance-health totals, not pod tallies. (Receipt:tests/unit/keyGen.test.tspod-decoupling assertions, Phase 18.)
8. Preferences & System Settings
Stores non-secret user preferences and instance configuration.
-- 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.
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
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.
{
"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"
}
]
}