motr-enclave/internal/migrations/schema.sql

-- =============================================================================
-- NEBULA KEY ENCLAVE SCHEMA
-- Encrypted SQLite database for sensitive wallet data
-- UCAN v1.0.0-rc.1 compliant
-- =============================================================================

PRAGMA foreign_keys = ON;

-- =============================================================================
-- IDENTITY
-- =============================================================================

-- DID Documents: Local cache of Sonr DID state
CREATE TABLE IF NOT EXISTS did_documents (
    id              INTEGER PRIMARY KEY,
    did             TEXT NOT NULL UNIQUE,              -- did:sonr:abc123...
    controller      TEXT NOT NULL,                     -- Controller DID
    document        TEXT NOT NULL,                     -- Full DID Document (JSON)
    sequence        INTEGER NOT NULL DEFAULT 0,        -- On-chain sequence number
    last_synced     TEXT NOT NULL DEFAULT (datetime('now')),
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_did_documents_did ON did_documents(did);

-- Verification Methods: Keys associated with DID
CREATE TABLE IF NOT EXISTS verification_methods (
    id              INTEGER PRIMARY KEY,
    did_id          INTEGER NOT NULL REFERENCES did_documents(id) ON DELETE CASCADE,
    method_id       TEXT NOT NULL,                     -- did:sonr:abc#key-1
    method_type     TEXT NOT NULL,                     -- Ed25519VerificationKey2020, etc.
    controller      TEXT NOT NULL,
    public_key      TEXT NOT NULL,                     -- Base64 encoded public key
    purpose         TEXT NOT NULL DEFAULT 'authentication', -- authentication, assertion, keyAgreement, capabilityInvocation, capabilityDelegation
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(did_id, method_id)
);

CREATE INDEX idx_verification_methods_did_id ON verification_methods(did_id);

-- =============================================================================
-- WEBAUTHN CREDENTIALS
-- =============================================================================

-- Credentials: WebAuthn credential storage
CREATE TABLE IF NOT EXISTS credentials (
    id              INTEGER PRIMARY KEY,
    did_id          INTEGER NOT NULL REFERENCES did_documents(id) ON DELETE CASCADE,
    credential_id   TEXT NOT NULL UNIQUE,              -- WebAuthn credential ID (base64)
    public_key      TEXT NOT NULL,                     -- COSE public key (base64)
    public_key_alg  INTEGER NOT NULL,                  -- COSE algorithm (-7 = ES256, -257 = RS256)
    aaguid          TEXT,                              -- Authenticator AAGUID
    sign_count      INTEGER NOT NULL DEFAULT 0,        -- Signature counter
    transports      TEXT DEFAULT '[]',                 -- JSON array: ["internal", "usb", "nfc", "ble"]
    device_name     TEXT NOT NULL,                     -- User-assigned name
    device_type     TEXT NOT NULL DEFAULT 'platform',  -- platform, cross-platform
    authenticator   TEXT,                              -- Touch ID, Face ID, Windows Hello, YubiKey
    is_discoverable INTEGER NOT NULL DEFAULT 1,        -- Resident key / passkey
    backed_up       INTEGER NOT NULL DEFAULT 0,        -- Credential backed up (BE flag)
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    last_used       TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_credentials_did_id ON credentials(did_id);
CREATE INDEX idx_credentials_credential_id ON credentials(credential_id);

-- =============================================================================
-- MPC ENCLAVES
-- =============================================================================

CREATE TABLE IF NOT EXISTS mpc_enclaves (
    id              INTEGER PRIMARY KEY,
    did_id          INTEGER NOT NULL REFERENCES did_documents(id) ON DELETE CASCADE,
    enclave_id      TEXT NOT NULL UNIQUE,
    public_key_hex  TEXT NOT NULL,
    public_key      BLOB NOT NULL,
    val_share       BLOB NOT NULL,
    user_share      BLOB NOT NULL,
    nonce           BLOB NOT NULL,
    curve           TEXT NOT NULL DEFAULT 'secp256k1',
    status          TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'rotating', 'archived')),
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    rotated_at      TEXT,
    UNIQUE(did_id, enclave_id)
);

CREATE INDEX idx_mpc_enclaves_did_id ON mpc_enclaves(did_id);
CREATE INDEX idx_mpc_enclaves_public_key_hex ON mpc_enclaves(public_key_hex);

CREATE TABLE IF NOT EXISTS accounts (
    id              INTEGER PRIMARY KEY,
    did_id          INTEGER NOT NULL REFERENCES did_documents(id) ON DELETE CASCADE,
    enclave_id      INTEGER NOT NULL REFERENCES mpc_enclaves(id) ON DELETE CASCADE,
    address         TEXT NOT NULL,
    chain_id        TEXT NOT NULL,
    coin_type       INTEGER NOT NULL,
    account_index   INTEGER NOT NULL DEFAULT 0,
    address_index   INTEGER NOT NULL DEFAULT 0,
    label           TEXT DEFAULT '',
    is_default      INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(enclave_id, chain_id, account_index, address_index)
);

CREATE INDEX idx_accounts_did_id ON accounts(did_id);
CREATE INDEX idx_accounts_address ON accounts(address);
CREATE INDEX idx_accounts_chain_id ON accounts(chain_id);

-- =============================================================================
-- UCAN AUTHORIZATION (v1.0.0-rc.1)
-- =============================================================================

-- UCAN Delegations: v1.0.0-rc.1 delegation envelopes
-- Stores sealed DAG-CBOR envelopes with extracted fields for indexing
-- Uses JSONB for complex nested structures (policy, metadata)
CREATE TABLE IF NOT EXISTS ucan_delegations (
    id              INTEGER PRIMARY KEY,
    did_id          INTEGER NOT NULL REFERENCES did_documents(id) ON DELETE CASCADE,
    
    -- Content Identifier (immutable, unique)
    cid             TEXT NOT NULL UNIQUE,
    
    -- Sealed envelope (DAG-CBOR encoded)
    envelope        BLOB NOT NULL,
    
    -- Extracted fields for indexing/queries
    iss             TEXT NOT NULL,                     -- Issuer DID
    aud             TEXT NOT NULL,                     -- Audience DID  
    sub             TEXT,                              -- Subject DID (null = powerline)
    cmd             TEXT NOT NULL,                     -- Command (e.g., "/vault/read")
    
    -- Policy stored as JSONB for efficient querying
    pol             BLOB DEFAULT (jsonb('[]')),        -- Policy JSONB
    
    -- Optional metadata stored as JSONB
    meta            BLOB DEFAULT (jsonb('{}')),        -- Metadata JSONB
    
    -- Temporal fields
    nbf             TEXT,                              -- Not before (ISO8601)
    exp             TEXT,                              -- Expiration (ISO8601, null = never)
    
    -- Metadata
    is_root         INTEGER NOT NULL DEFAULT 0,        -- iss == sub
    is_powerline    INTEGER NOT NULL DEFAULT 0,        -- sub IS NULL
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_ucan_delegations_cid ON ucan_delegations(cid);
CREATE INDEX idx_ucan_delegations_did_id ON ucan_delegations(did_id);
CREATE INDEX idx_ucan_delegations_iss ON ucan_delegations(iss);
CREATE INDEX idx_ucan_delegations_aud ON ucan_delegations(aud);
CREATE INDEX idx_ucan_delegations_sub ON ucan_delegations(sub);
CREATE INDEX idx_ucan_delegations_cmd ON ucan_delegations(cmd);
CREATE INDEX idx_ucan_delegations_exp ON ucan_delegations(exp);

-- UCAN Invocations: v1.0.0-rc.1 invocation envelopes (audit log)
CREATE TABLE IF NOT EXISTS ucan_invocations (
    id              INTEGER PRIMARY KEY,
    did_id          INTEGER NOT NULL REFERENCES did_documents(id) ON DELETE CASCADE,
    
    -- Content Identifier
    cid             TEXT NOT NULL UNIQUE,
    
    -- Sealed envelope (DAG-CBOR encoded)
    envelope        BLOB NOT NULL,
    
    -- Extracted fields for indexing
    iss             TEXT NOT NULL,                     -- Invoker DID
    sub             TEXT NOT NULL,                     -- Subject DID
    aud             TEXT,                              -- Executor DID (if different from sub)
    cmd             TEXT NOT NULL,                     -- Command invoked
    
    -- Proof chain as JSONB (array of delegation CIDs)
    prf             BLOB NOT NULL DEFAULT (jsonb('[]')),
    
    -- Command arguments as JSONB
    args            BLOB DEFAULT (jsonb('{}')),
    
    -- Optional metadata as JSONB
    meta            BLOB DEFAULT (jsonb('{}')),
    
    -- Temporal
    exp             TEXT,                              -- Expiration
    iat             TEXT,                              -- Issued at
    
    -- Execution tracking
    executed_at     TEXT,                              -- When actually executed
    result_cid      TEXT,                              -- CID of receipt (if executed)
    
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_ucan_invocations_cid ON ucan_invocations(cid);
CREATE INDEX idx_ucan_invocations_did_id ON ucan_invocations(did_id);
CREATE INDEX idx_ucan_invocations_iss ON ucan_invocations(iss);
CREATE INDEX idx_ucan_invocations_sub ON ucan_invocations(sub);
CREATE INDEX idx_ucan_invocations_cmd ON ucan_invocations(cmd);

-- UCAN Revocations: Track revoked delegations
CREATE TABLE IF NOT EXISTS ucan_revocations (
    id              INTEGER PRIMARY KEY,
    delegation_cid  TEXT NOT NULL UNIQUE,              -- CID of revoked delegation
    revoked_by      TEXT NOT NULL,                     -- Revoker DID
    invocation_cid  TEXT,                              -- CID of revocation invocation
    reason          TEXT,
    revoked_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_ucan_revocations_delegation_cid ON ucan_revocations(delegation_cid);
CREATE INDEX idx_ucan_revocations_revoked_by ON ucan_revocations(revoked_by);

-- =============================================================================
-- DEVICE SESSIONS
-- =============================================================================

-- Sessions: Active device sessions
CREATE TABLE IF NOT EXISTS sessions (
    id              INTEGER PRIMARY KEY,
    did_id          INTEGER NOT NULL REFERENCES did_documents(id) ON DELETE CASCADE,
    credential_id   INTEGER NOT NULL REFERENCES credentials(id) ON DELETE CASCADE,
    session_id      TEXT NOT NULL UNIQUE,              -- Opaque session identifier
    device_info     TEXT DEFAULT '{}',                 -- JSON: {browser, os, ip, etc.}
    is_current      INTEGER NOT NULL DEFAULT 0,        -- Is this the current session
    last_activity   TEXT NOT NULL DEFAULT (datetime('now')),
    expires_at      TEXT NOT NULL,
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_sessions_did_id ON sessions(did_id);
CREATE INDEX idx_sessions_session_id ON sessions(session_id);
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);

-- =============================================================================
-- SERVICE CONNECTIONS
-- =============================================================================

-- Services: Connected third-party services/dApps
CREATE TABLE IF NOT EXISTS services (
    id              INTEGER PRIMARY KEY,
    origin          TEXT NOT NULL UNIQUE,              -- https://app.example.com
    name            TEXT NOT NULL,
    description     TEXT,
    logo_url        TEXT,
    did             TEXT,                              -- Service's DID (if known)
    is_verified     INTEGER NOT NULL DEFAULT 0,
    metadata        TEXT DEFAULT '{}',                 -- Additional service metadata
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_services_origin ON services(origin);

-- Grants: User grants to services (backed by UCAN delegations)
CREATE TABLE IF NOT EXISTS grants (
    id              INTEGER PRIMARY KEY,
    did_id          INTEGER NOT NULL REFERENCES did_documents(id) ON DELETE CASCADE,
    service_id      INTEGER NOT NULL REFERENCES services(id) ON DELETE CASCADE,
    delegation_cid  TEXT REFERENCES ucan_delegations(cid) ON DELETE SET NULL, -- v1.0.0-rc.1 delegation
    scopes          TEXT NOT NULL DEFAULT '[]',        -- JSON array of granted scopes
    accounts        TEXT NOT NULL DEFAULT '[]',        -- JSON array of account IDs exposed
    status          TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'revoked')),
    granted_at      TEXT NOT NULL DEFAULT (datetime('now')),
    last_used       TEXT,
    expires_at      TEXT,
    UNIQUE(did_id, service_id)
);

CREATE INDEX idx_grants_did_id ON grants(did_id);
CREATE INDEX idx_grants_service_id ON grants(service_id);
CREATE INDEX idx_grants_status ON grants(status);
CREATE INDEX idx_grants_delegation_cid ON grants(delegation_cid);

-- =============================================================================
-- SYNC STATE
-- =============================================================================

-- Sync Checkpoints: Track sync state with Sonr protocol
CREATE TABLE IF NOT EXISTS sync_checkpoints (
    id              INTEGER PRIMARY KEY,
    did_id          INTEGER NOT NULL REFERENCES did_documents(id) ON DELETE CASCADE,
    resource_type   TEXT NOT NULL,                     -- 'did', 'credentials', 'grants', etc.
    last_block      INTEGER NOT NULL DEFAULT 0,        -- Last synced block height
    last_tx_hash    TEXT,                              -- Last processed transaction
    last_synced     TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(did_id, resource_type)
);

CREATE INDEX idx_sync_checkpoints_did_id ON sync_checkpoints(did_id);

-- =============================================================================
-- TRIGGERS
-- =============================================================================

CREATE TRIGGER IF NOT EXISTS did_documents_updated_at 
    AFTER UPDATE ON did_documents
    BEGIN
        UPDATE did_documents SET updated_at = datetime('now') WHERE id = NEW.id;
    END;

-- =============================================================================
-- VIEWS (pre-computed JOINs for common queries)
-- =============================================================================

CREATE VIEW IF NOT EXISTS v_accounts AS
SELECT 
    a.id, a.did_id, a.enclave_id, a.address, a.chain_id, 
    a.coin_type, a.account_index, a.address_index, 
    a.label, a.is_default, a.created_at,
    e.public_key_hex, e.curve, e.enclave_id as enclave_ref
FROM accounts a
JOIN mpc_enclaves e ON a.enclave_id = e.id;

CREATE VIEW IF NOT EXISTS v_sessions AS
SELECT 
    s.id, s.did_id, s.credential_id, s.session_id, s.device_info,
    s.is_current, s.last_activity, s.expires_at, s.created_at,
    c.device_name, c.authenticator
FROM sessions s
JOIN credentials c ON s.credential_id = c.id
WHERE s.expires_at > datetime('now');

CREATE VIEW IF NOT EXISTS v_grants AS
SELECT 
    g.id, g.did_id, g.service_id, g.delegation_cid, g.scopes,
    g.accounts, g.status, g.granted_at, g.last_used, g.expires_at,
    s.name as service_name, s.origin as service_origin, s.logo_url as service_logo
FROM grants g
JOIN services s ON g.service_id = s.id;

CREATE VIEW IF NOT EXISTS v_active_delegations AS
SELECT * FROM ucan_delegations 
WHERE exp IS NULL OR exp > datetime('now');

CREATE VIEW IF NOT EXISTS v_active_enclaves AS
SELECT * FROM mpc_enclaves WHERE status = 'active';