Storage model #

piclaw stores state in SQLite at /workspace/.piclaw/store/messages.db. The database is the source of truth for chat history, media, tasks, and token usage.

Never delete this file. Only repair or migrate it.

Key tables #

Attachments, Adaptive Card blocks, submission receipts, and link previews are stored on the message record (content_blocks, link_previews). In practice, content_blocks can now contain persisted adaptive_card and adaptive_card_submission payloads in addition to other structured message content. Token usage rows include model, provider, and api fields to support per-model tracking.

Browser-local UI memory that should not become shared backend truth — for example local dismissal/seen state for the context compaction affordance — is intentionally kept out of SQLite. The backend remains authoritative for whether the underlying context condition is currently warranted; the browser only stores lightweight local UI memory.

Entity map #

erDiagram
  CHATS ||--o{ MESSAGES : contains
  CHATS ||--o{ TOKEN_USAGE : tracks
  CHATS ||--|| CHAT_CURSORS : tracks
  MESSAGES ||--o{ MESSAGE_MEDIA : has
  MEDIA ||--o{ MESSAGE_MEDIA : linked
  SCHEDULED_TASKS ||--o{ TASK_RUN_LOGS : logs
  MESSAGES ||--|| MESSAGES_FTS : indexes
  TOOL_OUTPUTS ||--|| TOOL_OUTPUTS_FTS : indexes
  WORKSPACE_FILES ||--|| WORKSPACE_FTS : indexes

  CHATS {
    text jid
    text name
    text last_message_time
  }
  MESSAGES {
    text id
    text chat_jid
    text sender
    text sender_name
    text content
    text content_blocks
    text link_previews
    text timestamp
    int is_from_me
    int is_bot_message
  }
  MESSAGES_FTS {
    text content
    text chat_jid
    text sender
    text sender_name
    text timestamp
    int is_bot_message
  }
  MESSAGE_MEDIA {
    int message_rowid
    int media_id
  }
  MEDIA {
    int id
    text filename
    text content_type
    blob data
    blob thumbnail
    text metadata
    text created_at
  }
  SCHEDULED_TASKS {
    text id
    text chat_jid
    text prompt
    text model
    text schedule_type
    text schedule_value
    text next_run
    text last_run
    text last_result
    text status
    text created_at
  }
  TASK_RUN_LOGS {
    int id
    text task_id
    text run_at
    int duration_ms
    text status
    text result
    text error
  }
  TOKEN_USAGE {
    int id
    text chat_jid
    text run_at
    int input_tokens
    int output_tokens
    int cache_read_tokens
    int cache_write_tokens
    int total_tokens
    real cost_input
    real cost_output
    real cost_cache_read
    real cost_cache_write
    real cost_total
    text model
    text provider
    text api
    int turns
  }
  CHAT_CURSORS {
    text chat_jid
    text cursor_ts
    text inflight_prev_ts
    text inflight_message_id
    text inflight_started_at
    text failed_prev_ts
    text failed_ts
    text failed_message_id
    int failed_thread_root
    text failed_created_at
    text queued_followups_json
  }
  TOOL_OUTPUTS {
    text id
    text created_at
    text source
    int size_bytes
    int line_count
    text summary
    text path
  }
  TOOL_OUTPUTS_FTS {
    text content
    text output_id
  }
  WORKSPACE_FILES {
    text path
    int mtime_ms
    int size_bytes
    text indexed_at
  }
  WORKSPACE_FTS {
    text content
    text path
    int mtime_ms
    int size_bytes
  }
  ROUTER_STATE {
    text key
    text value
  }
  KEYCHAIN_ENTRIES {
    int id
    text name
    text type
    blob ciphertext
    blob nonce
    blob salt
    text kdf
    int kdf_iterations
    text created_at
    text updated_at
  }
  WEBAUTHN_CREDENTIALS {
    int id
    text user_id
    text rp_id
    text credential_id
    text public_key
    int sign_count
    text transports
    text created_at
    text last_used_at
  }
  WEBAUTHN_ENROLLMENTS {
    text token
    text user_id
    text created_at
    text expires_at
  }
  WEB_SESSIONS {
    text token
    text user_id
    text auth_method
    text created_at
    text expires_at
  }

Token usage #

token_usage stores per-assistant-message usage and cost tracking:

• run_at is the timestamp for the tool run (ISO 8601).
• Token counts are stored in input_tokens, output_tokens, cache_read_tokens, cache_write_tokens, and total_tokens.
• Costs are tracked in cost_input, cost_output, cost_cache_read, cost_cache_write, and cost_total.
• model, provider, and api help with attribution.

Indexes #

• messages(timestamp) for chronological queries
• messages(chat_jid) for timeline paging
• messages(chat_jid, timestamp) for per-chat time windows
• messages(chat_jid, is_bot_message, timestamp) for pollers and ingestion
• token_usage(chat_jid), token_usage(run_at), and token_usage(chat_jid, run_at) for usage summaries
• chats(last_message_time) for recent chat ordering
• scheduled_tasks(next_run), scheduled_tasks(status), scheduled_tasks(created_at), and scheduled_tasks(last_run) for the scheduler
• task_run_logs(task_id, run_at) for audit history
• tool_outputs(created_at) for recent tool output
• media(created_at) for attachment timelines
• message_media(message_rowid) and message_media(media_id) for joins
• messages_fts, tool_outputs_fts, and workspace_fts for full-text search
• keychain_entries(type) for keychain lookups
• webauthn_credentials(user_id) and webauthn_credentials(rp_id) for passkey queries
• webauthn_enrollments(expires_at) for enrolment cleanup
• web_sessions(expires_at) for session cleanup

Data paths #

• /workspace/.piclaw/store/messages.db — SQLite database
• /workspace/.piclaw/data/sessions/ — pi session JSONL history
• /workspace/.piclaw/data/ipc/ — IPC messages and scheduled task files
• /workspace/.piclaw/data/chats.json — Known chat JIDs

Backups #

Restic snapshots are stored in the configured repository. The backup script lives at:

/workspace/.piclaw/restic/backup.sh

The container image ships a pinned official restic binary with Azure backend support. The pinned version is tracked in the repo root at:

RESTIC_VERSION