schema.sql

-- federation-node/schema.sql
-- The Vault: Phase 2 Persistent Storage for C-FED-NODE-001

-- Tracks known entities and their trust tier mappings
CREATE TABLE IF NOT EXISTS trust_registry (
    seal TEXT PRIMARY KEY,
    alias TEXT,
    tier TEXT NOT NULL DEFAULT 'QUARANTINE',
    first_seen_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_seen_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- The physical envelope store
CREATE TABLE IF NOT EXISTS envelopes (
    envelope_id TEXT PRIMARY KEY,
    protocol_version TEXT NOT NULL,
    message_class TEXT NOT NULL,
    sender_seal TEXT NOT NULL,
    recipient_seal TEXT NOT NULL,
    payload_body TEXT,
    status TEXT NOT NULL DEFAULT 'RECEIVED',
    received_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME,
    priority TEXT DEFAULT 'normal'
);

-- Immutable audit log of envelope state transitions
CREATE TABLE IF NOT EXISTS status_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    envelope_id TEXT NOT NULL,
    status TEXT NOT NULL,
    transitioned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    reason TEXT,
    FOREIGN KEY(envelope_id) REFERENCES envelopes(envelope_id)
);