287 lines
14 KiB
SQL
287 lines
14 KiB
SQL
-- ============================================================
|
|
-- AEGIS MANIFEST (SQLite) - UPDATED: datastore label visible, inner requires PIN
|
|
-- ============================================================
|
|
|
|
-- ----------------------------
|
|
-- PRAGMAS (aplicar na abertura da conexão)
|
|
-- ----------------------------
|
|
PRAGMA foreign_keys = ON;
|
|
PRAGMA journal_mode = WAL;
|
|
PRAGMA synchronous = NORMAL;
|
|
PRAGMA temp_store = MEMORY;
|
|
|
|
-- ============================================================
|
|
-- USERS (OIDC identities)
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
user_id TEXT PRIMARY KEY, -- GUID/ULID (string)
|
|
created_at INTEGER NOT NULL, -- epoch seconds
|
|
disabled_at INTEGER, -- null = active
|
|
|
|
-- OIDC identity (stable pairing)
|
|
subject TEXT NOT NULL, -- "sub"
|
|
issuer TEXT NOT NULL, -- "iss"
|
|
|
|
display_name TEXT,
|
|
email TEXT,
|
|
|
|
UNIQUE(subject, issuer)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- DATASTORES
|
|
-- ============================================================
|
|
-- IMPORTANT:
|
|
-- - datastore name is decryptable WITHOUT PIN (using Label Key mechanism)
|
|
-- - datastore inner content requires PIN (Master Key unwrapped with PIN-derived KEK)
|
|
|
|
CREATE TABLE IF NOT EXISTS datastores (
|
|
datastore_id TEXT PRIMARY KEY, -- GUID/ULID
|
|
created_at INTEGER NOT NULL,
|
|
deleted_at INTEGER,
|
|
|
|
-- --------------------------
|
|
-- LABEL (visible without PIN)
|
|
-- --------------------------
|
|
-- name_enc is encrypted with a "Label Key" (LK) that does NOT require datastore PIN.
|
|
-- LK can be derived from a server secret, KMS, or any non-PIN mechanism.
|
|
name_enc BLOB NOT NULL,
|
|
name_nonce BLOB NOT NULL,
|
|
|
|
-- Identifies which LK mechanism/version was used (for rotation/migration).
|
|
-- Examples: "lk:server:v1", "lk:kms:key/123", "lk:yubikey:piv:9c"
|
|
lk_kid TEXT NOT NULL,
|
|
lk_version INTEGER NOT NULL DEFAULT 1,
|
|
|
|
-- --------------------------
|
|
-- MASTER KEY (PIN protected)
|
|
-- --------------------------
|
|
-- mk_wrapped is the datastore Master Key (MK) encrypted ("wrapped") with a KEK derived from the datastore PIN.
|
|
mk_wrapped BLOB NOT NULL,
|
|
mk_nonce BLOB NOT NULL,
|
|
mk_version INTEGER NOT NULL DEFAULT 1,
|
|
|
|
-- PIN KDF parameters (so you can change KDF settings per datastore over time)
|
|
pin_kdf TEXT NOT NULL DEFAULT 'argon2id', -- 'argon2id' recommended
|
|
pin_salt BLOB NOT NULL,
|
|
-- Store params as JSON text (portable and easy to evolve)
|
|
-- Example: {"memory_kib":65536,"iterations":3,"parallelism":1,"output_len":32}
|
|
pin_kdf_params TEXT NOT NULL,
|
|
|
|
-- Optional: quick verifier to reduce unwrap attempts (can be null).
|
|
-- If used, store an AEAD-encrypted constant (e.g., "AEGIS_PIN_OK") with KEK_pin and a nonce.
|
|
-- Otherwise you simply "try unwrap MK" and treat failure as "wrong PIN".
|
|
pin_verifier_enc BLOB,
|
|
pin_verifier_nonce BLOB,
|
|
|
|
-- Root node of the datastore tree
|
|
root_node_id TEXT NOT NULL,
|
|
|
|
FOREIGN KEY(root_node_id) REFERENCES nodes(node_id)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- DATASTORE MEMBERS (authorization)
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS datastore_members (
|
|
datastore_id TEXT NOT NULL,
|
|
user_id TEXT NOT NULL,
|
|
role TEXT NOT NULL, -- 'OWNER'|'ADMIN'|'EDITOR'|'VIEWER'
|
|
created_at INTEGER NOT NULL,
|
|
|
|
PRIMARY KEY(datastore_id, user_id),
|
|
FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id),
|
|
FOREIGN KEY(user_id) REFERENCES users(user_id)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- NODES (logical tree) - INTERNAL: requires PIN
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS nodes (
|
|
node_id TEXT PRIMARY KEY, -- GUID/ULID
|
|
datastore_id TEXT NOT NULL,
|
|
parent_id TEXT, -- null only for root
|
|
kind INTEGER NOT NULL, -- 1=DIR, 2=FILE
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
deleted_at INTEGER, -- soft delete/trash
|
|
|
|
-- Encrypted node name (requires MK -> PIN)
|
|
name_enc BLOB NOT NULL,
|
|
name_nonce BLOB NOT NULL,
|
|
|
|
-- File-only fields (kind=FILE)
|
|
main_object_id TEXT, -- FK objects.object_id
|
|
size_plain INTEGER, -- optional (can leak info; keep or encrypt later)
|
|
mime_enc BLOB, -- optional: encrypt mime to reduce metadata leak
|
|
mime_nonce BLOB,
|
|
|
|
version INTEGER NOT NULL DEFAULT 1, -- optimistic lock
|
|
|
|
FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id),
|
|
FOREIGN KEY(parent_id) REFERENCES nodes(node_id),
|
|
FOREIGN KEY(main_object_id) REFERENCES objects(object_id)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- OBJECTS (encrypted blobs) - INTERNAL: requires PIN
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS objects (
|
|
object_id TEXT PRIMARY KEY, -- GUID/ULID
|
|
datastore_id TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
|
|
-- AEAD algorithm info
|
|
cipher TEXT NOT NULL, -- e.g. 'AES-256-GCM'
|
|
nonce BLOB NOT NULL, -- base nonce / object nonce (format depends on your file container)
|
|
|
|
-- DEK wrapped by datastore MK (requires PIN)
|
|
dek_wrapped BLOB NOT NULL,
|
|
dek_nonce BLOB NOT NULL,
|
|
|
|
-- Reference to blob location (also encrypted to avoid path leaks)
|
|
blob_ref_enc BLOB NOT NULL,
|
|
blob_ref_nonce BLOB NOT NULL,
|
|
|
|
size_cipher INTEGER NOT NULL,
|
|
hash_plain BLOB, -- optional (SHA-256) for integrity/dedupe v2
|
|
|
|
FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- DERIVATIVES (thumbs/previews/posters/etc.) - INTERNAL: requires PIN
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS derivatives (
|
|
derivative_id TEXT PRIMARY KEY, -- GUID/ULID
|
|
datastore_id TEXT NOT NULL,
|
|
node_id TEXT NOT NULL, -- owning file node
|
|
created_at INTEGER NOT NULL,
|
|
updated_at INTEGER NOT NULL,
|
|
|
|
kind TEXT NOT NULL, -- 'THUMB'|'POSTER'|'PREVIEW'|'WAVEFORM'|'SPRITE'...
|
|
profile TEXT NOT NULL, -- '256w','512w','page1','t=3s','wave:1024',...
|
|
|
|
object_id TEXT, -- object containing the derivative blob (when READY)
|
|
status INTEGER NOT NULL, -- 0=PENDING,1=READY,2=FAILED
|
|
|
|
-- store error details encrypted (may contain sensitive paths/info)
|
|
error_enc BLOB,
|
|
error_nonce BLOB,
|
|
|
|
FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id),
|
|
FOREIGN KEY(node_id) REFERENCES nodes(node_id),
|
|
FOREIGN KEY(object_id) REFERENCES objects(object_id),
|
|
|
|
UNIQUE(datastore_id, node_id, kind, profile)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- JOBS (SQLite queue) - does NOT require PIN itself
|
|
-- Payload should be encrypted with MK if it contains sensitive info (recommended).
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS jobs (
|
|
job_id TEXT PRIMARY KEY, -- GUID/ULID
|
|
datastore_id TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
available_at INTEGER NOT NULL, -- for backoff scheduling
|
|
picked_at INTEGER,
|
|
finished_at INTEGER,
|
|
|
|
kind TEXT NOT NULL, -- 'GEN_DERIVATIVES'|'REWRAP_KEYS' etc.
|
|
|
|
payload_enc BLOB NOT NULL,
|
|
payload_nonce BLOB NOT NULL,
|
|
|
|
status INTEGER NOT NULL, -- 0=QUEUED,1=RUNNING,2=DONE,3=FAILED
|
|
attempts INTEGER NOT NULL DEFAULT 0,
|
|
|
|
FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- AUDIT LOG (encrypted details) - INTERNAL: may require PIN depending on what you put in details
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
audit_id TEXT PRIMARY KEY, -- GUID/ULID
|
|
datastore_id TEXT NOT NULL,
|
|
created_at INTEGER NOT NULL,
|
|
actor_user_id TEXT, -- null for system/worker
|
|
action TEXT NOT NULL, -- 'UPLOAD','RENAME','MOVE','DELETE','DOWNLOAD','UNLOCK','LOCK'...
|
|
|
|
target_node_id TEXT,
|
|
details_enc BLOB NOT NULL,
|
|
details_nonce BLOB NOT NULL,
|
|
|
|
FOREIGN KEY(datastore_id) REFERENCES datastores(datastore_id),
|
|
FOREIGN KEY(actor_user_id) REFERENCES users(user_id),
|
|
FOREIGN KEY(target_node_id) REFERENCES nodes(node_id)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- INDEXES (hot paths)
|
|
-- ============================================================
|
|
|
|
-- list directory: by datastore + parent, excluding deleted
|
|
CREATE INDEX IF NOT EXISTS idx_nodes_parent
|
|
ON nodes(datastore_id, parent_id, deleted_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_nodes_parent_kind
|
|
ON nodes(datastore_id, parent_id, kind, deleted_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_nodes_main_object
|
|
ON nodes(main_object_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_objects_datastore_created
|
|
ON objects(datastore_id, created_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_derivatives_node
|
|
ON derivatives(datastore_id, node_id, kind, status);
|
|
|
|
-- jobs queue
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_queue
|
|
ON jobs(status, available_at);
|
|
|
|
-- members lookup
|
|
CREATE INDEX IF NOT EXISTS idx_members_user
|
|
ON datastore_members(user_id);
|
|
|
|
-- datastore listing for a user typically joins members -> datastores
|
|
CREATE INDEX IF NOT EXISTS idx_members_datastore
|
|
ON datastore_members(datastore_id);
|
|
|
|
-- audit time queries
|
|
CREATE INDEX IF NOT EXISTS idx_audit_datastore_time
|
|
ON audit_log(datastore_id, created_at);
|
|
|
|
-- ============================================================
|
|
-- TRIGGERS (touch updated_at / version)
|
|
-- NOTE: SQLite default PRAGMA recursive_triggers is OFF, so these won't recurse infinitely.
|
|
-- ============================================================
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_nodes_touch
|
|
AFTER UPDATE ON nodes
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE nodes
|
|
SET updated_at = strftime('%s','now'),
|
|
version = version + 1
|
|
WHERE node_id = NEW.node_id;
|
|
END;
|
|
|
|
CREATE TRIGGER IF NOT EXISTS trg_derivatives_touch
|
|
AFTER UPDATE ON derivatives
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE derivatives
|
|
SET updated_at = strftime('%s','now')
|
|
WHERE derivative_id = NEW.derivative_id;
|
|
END;
|