Database Layer

Architecture Overview

┌─────────────────────────────────────────────────────────────────────────┐
│                         Database Layer (8 tables)                        │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                          │
│  ┌────────────────────────────────────────────────────────────────────┐ │
│  │                      Core Tables (Pointer Architecture)            │ │
│  │  ┌──────────────┐  ┌──────────────┐  ┌───────────────────────┐   │ │
│  │  │   sessions   │  │ session_tags │  │   session_contexts    │   │ │
│  │  │ (metadata)   │──│  (full-text) │  │  (folder bindings)    │   │ │
│  │  └──────────────┘  └──────────────┘  └───────────────────────┘   │ │
│  │         │                                                         │ │
│  │         │ SDK JSONL files are source of truth for messages        │ │
│  │         ▼ ({vault}/.claude/projects/{encoded-cwd}/)               │ │
│  └────────────────────────────────────────────────────────────────────┘ │
│                                                                          │
│  ┌────────────────────────────────────────────────────────────────────┐ │
│  │                      RAG Index Tables                              │ │
│  │  ┌──────────────┐  ┌────────────────┐  ┌────────────────────┐    │ │
│  │  │    chunks    │  │ index_manifest │  │      metadata      │    │ │
│  │  │ (text+embed) │  │ (indexed docs) │  │   (key-value)      │    │ │
│  │  └──────────────┘  └────────────────┘  └────────────────────┘    │ │
│  └────────────────────────────────────────────────────────────────────┘ │
│                                                                          │
│  ┌────────────────────────────────────────────────────────────────────┐ │
│  │                      Bot Connector Tables                          │ │
│  │  ┌──────────────────────────────────────────────────────────────┐ │ │
│  │  │ pairing_requests (user approval flow for Telegram/Discord)  │ │ │
│  │  └──────────────────────────────────────────────────────────────┘ │ │
│  └────────────────────────────────────────────────────────────────────┘ │
│                                                                          │
│  ┌────────────────────────────────────────────────────────────────────┐ │
│  │  schema_version (v10+) - Migration tracking                        │ │
│  └────────────────────────────────────────────────────────────────────┘ │
│                                                                          │
└─────────────────────────────────────────────────────────────────────────┘

Key Files

Sessions Table

Primary session metadata storage. The pointer architecture means this table stores metadata only; actual messages live in SDK JSONL files.

CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY,                    -- SDK session ID (UUID)
    title TEXT,                             -- Session title (auto-generated or user-set)
    module TEXT NOT NULL DEFAULT 'chat',    -- Module: 'chat', 'daily', 'build'
    source TEXT NOT NULL DEFAULT 'parachute', -- Source: parachute, claude-code, claude, chatgpt
    working_directory TEXT,                 -- Working directory for file operations
    vault_root TEXT,                        -- Original vault path (cross-machine portability)
    model TEXT,                             -- Model used (e.g., 'claude-opus-4')
    message_count INTEGER DEFAULT 0,        -- Total message count
    archived INTEGER DEFAULT 0,             -- Boolean: 1=archived, 0=active
    created_at TEXT NOT NULL,               -- ISO 8601 timestamp
    last_accessed TEXT NOT NULL,            -- ISO 8601 timestamp (auto-updated)
    continued_from TEXT,                    -- Parent session ID if continuation
    agent_type TEXT,                        -- Agent type (vault-agent, orchestrator, etc.)
    metadata TEXT                           -- JSON: permissions, custom metadata
);

-- Performance indexes
CREATE INDEX IF NOT EXISTS idx_sessions_module ON sessions(module);
CREATE INDEX IF NOT EXISTS idx_sessions_archived ON sessions(archived);
CREATE INDEX IF NOT EXISTS idx_sessions_source ON sessions(source);
CREATE INDEX IF NOT EXISTS idx_sessions_last_accessed ON sessions(last_accessed DESC);
CREATE INDEX IF NOT EXISTS idx_sessions_agent_type ON sessions(agent_type);

Key Design Decisions

• Pointer Architecture: Session table contains metadata only; SDK JSONL files are source of truth for messages
• Soft Deletes: archived flag instead of hard delete for audit trail
• Auto-update: last_accessed updated on every session access
• Permissions in Metadata: JSON blob contains SessionPermissions object

Session Tags Table

Enables fast filtering of sessions by tags without expensive text search.

CREATE TABLE IF NOT EXISTS session_tags (
    session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    tag TEXT NOT NULL,
    created_at TEXT NOT NULL,
    PRIMARY KEY (session_id, tag)
);

CREATE INDEX IF NOT EXISTS idx_session_tags_tag ON session_tags(tag);

Tag Operations

RAG Index Tables

Support for Retrieval-Augmented Generation with chunked content and optional embeddings.

Chunks Table

CREATE TABLE IF NOT EXISTS chunks (
    id TEXT PRIMARY KEY,              -- Format: "chunk:{content_id}:{field}:{chunk_index}"
    content_id TEXT NOT NULL,         -- Foreign key: session_id or document_id
    content_type TEXT NOT NULL,       -- Type: 'session', 'journal', 'document'
    field TEXT NOT NULL DEFAULT 'content',  -- Field name (for multi-field indexing)
    chunk_index INTEGER NOT NULL,     -- Sequential index of this chunk
    chunk_text TEXT NOT NULL,         -- The actual text content
    embedding BLOB,                   -- Optional vector embedding (for similarity search)
    created_at TEXT NOT NULL,
    UNIQUE(content_id, field, chunk_index)
);

CREATE INDEX IF NOT EXISTS idx_chunks_content_id ON chunks(content_id);
CREATE INDEX IF NOT EXISTS idx_chunks_content_type ON chunks(content_type);

Index Manifest Table

CREATE TABLE IF NOT EXISTS index_manifest (
    content_id TEXT PRIMARY KEY,      -- ID of indexed content
    content_type TEXT NOT NULL,       -- Type: 'session', 'journal', etc.
    content_hash TEXT,                -- Hash of original content (change detection)
    title TEXT,                       -- Title/name of content
    indexed_at TEXT NOT NULL,         -- ISO 8601 timestamp
    chunk_count INTEGER NOT NULL,     -- How many chunks were created
    source_path TEXT,                 -- Path to original file (if applicable)
    metadata TEXT                     -- JSON: additional indexing metadata
);

Chunk Operations

Pairing Requests Table

Tracks user approval flow for bot connectors (Telegram, Discord). When an unknown user messages the bot, a pairing request is created for the admin to approve or deny.

CREATE TABLE IF NOT EXISTS pairing_requests (
    id TEXT PRIMARY KEY,                               -- UUID
    platform TEXT NOT NULL,                            -- 'telegram' or 'discord'
    platform_user_id TEXT NOT NULL,                    -- Platform-specific user ID
    platform_user_display TEXT,                        -- Display name at time of request
    platform_chat_id TEXT NOT NULL,                    -- Chat/channel ID
    status TEXT NOT NULL DEFAULT 'pending',            -- pending → approved/denied
    approved_trust_level TEXT,                         -- Trust level if approved
    created_at TEXT NOT NULL,                          -- ISO 8601
    resolved_at TEXT,                                  -- When approved/denied
    resolved_by TEXT,                                  -- Who resolved (admin)
    UNIQUE(platform, platform_user_id, status)         -- One pending per user per platform
);

CREATE INDEX IF NOT EXISTS idx_pairing_requests_status ON pairing_requests(status);
CREATE INDEX IF NOT EXISTS idx_pairing_requests_platform ON pairing_requests(platform, platform_user_id);

Pairing Request Operations

Session Contexts Table

Links sessions to context folders. The system loads all AGENTS.md files from the folder hierarchy to provide context.

CREATE TABLE IF NOT EXISTS session_contexts (
    session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    folder_path TEXT NOT NULL,         -- Relative path to vault (e.g., "Projects/parachute")
    added_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (session_id, folder_path)
);

CREATE INDEX IF NOT EXISTS idx_session_contexts_session ON session_contexts(session_id);
CREATE INDEX IF NOT EXISTS idx_session_contexts_folder ON session_contexts(folder_path);

Context Operations

Metadata Table

Key-value store for system settings, version info, and global metadata.

CREATE TABLE IF NOT EXISTS metadata (
    key TEXT PRIMARY KEY,    -- Config key
    value TEXT,              -- JSON or string value
    updated_at TEXT NOT NULL
);

Operations

Pydantic Models

Data validation models for sessions and permissions.

Session Model

class Session(BaseModel):
    id: str                              # SDK session ID (UUID)
    title: Optional[str]                 # Session title
    module: str = 'chat'                 # 'chat' | 'daily' | 'build'
    source: SessionSource                # parachute, claude-code, claude, chatgpt
    working_directory: Optional[str]     # Absolute or relative path
    vault_root: Optional[str]            # Original vault path
    model: Optional[str]                 # Model name
    message_count: int = 0               # Total messages
    archived: bool = False               # Soft delete flag
    created_at: datetime                 # With timezone
    last_accessed: datetime              # With timezone, auto-updated
    continued_from: Optional[str]        # Parent session ID
    agent_type: Optional[str]            # Agent type string
    metadata: Optional[dict]             # Permissions + custom data

    @property
    def permissions(self) -> SessionPermissions:
        """Extract permissions from metadata"""
        ...

    def with_permissions(self, perms: SessionPermissions) -> Session:
        """Returns copy with updated permissions"""
        ...

SessionPermissions Model

class SessionPermissions(BaseModel):
    read: List[str] = []                 # Glob patterns for allowed read paths
    write: List[str] = ["Chat/artifacts/*"]  # Glob patterns for write paths
    bash: Union[List[str], bool] = False # Commands list, or bool (all/none)
    trust_mode: bool = True              # Skip prompts when True

    def can_read(self, path: str) -> bool:
        """Check if path is allowed for reading"""
        ...

    def can_write(self, path: str) -> bool:
        """Check if path is allowed for writing"""
        ...

    def can_bash(self, command: str) -> bool:
        """Check if command is allowed"""
        ...

Additional Models

Session CRUD Operations

Schema Migrations

Automatic migrations run on Database.connect() in _run_migrations().

Schema Version Table

CREATE TABLE IF NOT EXISTS schema_version (
    version INTEGER PRIMARY KEY,
    applied_at TEXT NOT NULL
);

-- Initialized to version 10
INSERT OR IGNORE INTO schema_version (version, applied_at)
VALUES (10, datetime('now'));

Migration History

Connection Management

class Database:
    def __init__(self, db_path: Path):
        self.db_path = db_path
        self._connection: Optional[aiosqlite.Connection] = None

    async def connect(self) -> None:
        # Creates parent directories if needed
        # Opens SQLite connection with aiosqlite
        # Initializes schema
        # Runs migrations
        # Sets row_factory to aiosqlite.Row (dict-like access)

    async def close(self) -> None:
        # Gracefully close connection

    @property
    def connection(self) -> aiosqlite.Connection:
        # Getter that raises if not connected

Global Instance

_database: Optional[Database] = None

async def init_database(db_path: Path) -> Database:
    """Initialize global instance"""

async def get_database() -> Database:
    """Get global instance"""

async def close_database() -> None:
    """Close global instance"""

SSE Event Models

Streaming events sent from server to client (server-sent events).

Query Examples

List Recent Chat Sessions

sessions = await db.list_sessions(
    module='chat',
    archived=False,
    limit=20,
    offset=0
)

Find Sessions by Tag

tagged = await db.get_sessions_by_tag('daily-standup', limit=10)

Update Session After Message

await db.increment_message_count(session_id)
await db.touch_session(session_id)

Archive Old Sessions

deleted = await db.cleanup_old_sessions(days=30)

Check Session Permissions

session = await db.get_session(session_id)
perms = session.permissions
if perms.can_read('Projects/parachute'):
    # Allow operation

Key Design Decisions

• Metadata as JSON: Permissions, agent config, custom data stored as JSON in metadata column
• Async-first: All operations are async using aiosqlite for non-blocking I/O
• Row factory: aiosqlite.Row allows dict-like access (row["column_name"])
• Timestamps: ISO 8601 format (timezone-aware UTC strings)
• Boolean storage: Integers (0/1) for SQLite compatibility
• Soft deletes: Archive flag instead of hard delete for audit trail
• Cascading deletes: Foreign key constraints clean up related records
• Pointer architecture: Session DB contains metadata only; SDK stores actual messages in JSONL files
• Bot pairing isolation: Dedicated table for pairing requests separate from session metadata