-- Vaultik Database Schema -- Note: This database does not support migrations. If the schema changes, -- delete the local database and perform a full backup to recreate it. -- Files table: stores metadata about files in the filesystem CREATE TABLE IF NOT EXISTS files ( id TEXT PRIMARY KEY, -- UUID path TEXT NOT NULL UNIQUE, mtime INTEGER NOT NULL, ctime INTEGER NOT NULL, size INTEGER NOT NULL, mode INTEGER NOT NULL, uid INTEGER NOT NULL, gid INTEGER NOT NULL, link_target TEXT ); -- Create index on path for efficient lookups CREATE INDEX IF NOT EXISTS idx_files_path ON files(path); -- File chunks table: maps files to their constituent chunks CREATE TABLE IF NOT EXISTS file_chunks ( file_id TEXT NOT NULL, idx INTEGER NOT NULL, chunk_hash TEXT NOT NULL, PRIMARY KEY (file_id, idx), FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE, FOREIGN KEY (chunk_hash) REFERENCES chunks(chunk_hash) ); -- Chunks table: stores unique content-defined chunks CREATE TABLE IF NOT EXISTS chunks ( chunk_hash TEXT PRIMARY KEY, size INTEGER NOT NULL ); -- Blobs table: stores packed, compressed, and encrypted blob information CREATE TABLE IF NOT EXISTS blobs ( id TEXT PRIMARY KEY, blob_hash TEXT UNIQUE, created_ts INTEGER NOT NULL, finished_ts INTEGER, uncompressed_size INTEGER NOT NULL DEFAULT 0, compressed_size INTEGER NOT NULL DEFAULT 0, uploaded_ts INTEGER ); -- Blob chunks table: maps chunks to the blobs that contain them CREATE TABLE IF NOT EXISTS blob_chunks ( blob_id TEXT NOT NULL, chunk_hash TEXT NOT NULL, offset INTEGER NOT NULL, length INTEGER NOT NULL, PRIMARY KEY (blob_id, chunk_hash), FOREIGN KEY (blob_id) REFERENCES blobs(id) ON DELETE CASCADE, FOREIGN KEY (chunk_hash) REFERENCES chunks(chunk_hash) ); -- Chunk files table: reverse mapping of chunks to files CREATE TABLE IF NOT EXISTS chunk_files ( chunk_hash TEXT NOT NULL, file_id TEXT NOT NULL, file_offset INTEGER NOT NULL, length INTEGER NOT NULL, PRIMARY KEY (chunk_hash, file_id), FOREIGN KEY (chunk_hash) REFERENCES chunks(chunk_hash), FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE ); -- Snapshots table: tracks backup snapshots CREATE TABLE IF NOT EXISTS snapshots ( id TEXT PRIMARY KEY, hostname TEXT NOT NULL, vaultik_version TEXT NOT NULL, vaultik_git_revision TEXT NOT NULL, started_at INTEGER NOT NULL, completed_at INTEGER, file_count INTEGER NOT NULL DEFAULT 0, chunk_count INTEGER NOT NULL DEFAULT 0, blob_count INTEGER NOT NULL DEFAULT 0, total_size INTEGER NOT NULL DEFAULT 0, blob_size INTEGER NOT NULL DEFAULT 0, blob_uncompressed_size INTEGER NOT NULL DEFAULT 0, compression_ratio REAL NOT NULL DEFAULT 1.0, compression_level INTEGER NOT NULL DEFAULT 3, upload_bytes INTEGER NOT NULL DEFAULT 0, upload_duration_ms INTEGER NOT NULL DEFAULT 0 ); -- Snapshot files table: maps snapshots to files CREATE TABLE IF NOT EXISTS snapshot_files ( snapshot_id TEXT NOT NULL, file_id TEXT NOT NULL, PRIMARY KEY (snapshot_id, file_id), FOREIGN KEY (snapshot_id) REFERENCES snapshots(id) ON DELETE CASCADE, FOREIGN KEY (file_id) REFERENCES files(id) ); -- Snapshot blobs table: maps snapshots to blobs CREATE TABLE IF NOT EXISTS snapshot_blobs ( snapshot_id TEXT NOT NULL, blob_id TEXT NOT NULL, blob_hash TEXT NOT NULL, PRIMARY KEY (snapshot_id, blob_id), FOREIGN KEY (snapshot_id) REFERENCES snapshots(id) ON DELETE CASCADE, FOREIGN KEY (blob_id) REFERENCES blobs(id) ); -- Uploads table: tracks blob upload metrics CREATE TABLE IF NOT EXISTS uploads ( blob_hash TEXT PRIMARY KEY, snapshot_id TEXT NOT NULL, uploaded_at INTEGER NOT NULL, size INTEGER NOT NULL, duration_ms INTEGER NOT NULL, FOREIGN KEY (blob_hash) REFERENCES blobs(blob_hash), FOREIGN KEY (snapshot_id) REFERENCES snapshots(id) );