messages.db Schema

Don't Guess Column Names

The shared SQLite at /workspace/store/messages.db is accessed via python3 -c 'import sqlite3; conn = sqlite3.connect("/workspace/store/messages.db"); ...' — the standalone sqlite3 CLI is not installed in the container image. The tables below have schemas confirmed by PRAGMA table_info(<table>). Don't guess these column names:

• trigger_word — the actual column is trigger_pattern
• chat_jid on registered_groups — it's not a column there; only on messages
• trusted as a column — lives inside container_config JSON, not its own column

Tables

• registered_groups (per-group config): jid, name, folder, trigger_pattern, added_at, container_config, requires_trigger, is_main. The trigger_pattern column is what callers sometimes guess as trigger_word or trigger. The trusted flag is NOT a column — it lives inside container_config JSON.
• chats: jid, name, last_message_time, channel, is_group.
• messages: id, chat_jid, sender, sender_name, content, timestamp, is_from_me, is_bot_message, reply_to_message_id, reply_to_message_content, reply_to_sender_name, telegram_message_id. Composite PK (id, chat_jid).
• scheduled_tasks: id, group_folder, chat_jid, prompt, schedule_type, schedule_value, next_run, last_run, last_result, status, created_at, context_mode, script, created_by_role, schedule_timezone, continuation_cycle_id, session_id, source.
• tz_state (singleton, id = 1): id, current_tz, home_tz, scheduler_tz, schema_version.
• follow_me_tasks: name (PK), local_time, schedule_value, last_run_date, pending_run_at, schema_version, updated_at.
• phase_completions: phase (PK), last_completed, metadata, updated_at, schema_version.

Prefer MCP Tools for Mutations

Where an MCP host tool already exposes the field (chat_status, inspect_gate_decisions, get_scheduled_tasks, etc.), prefer it over raw SQL — the tool also handles the host-side concurrency contract (BEGIN IMMEDIATE for follow_me_tasks writes per the nanoclaw-admin: follow-me-two-phase-lock rule). Direct SQL is for read-mostly inspection; mutations should go through the tool path unless concurrency is provably safe.

Schema Drift

If a PRAGMA table_info result diverges from the columns above, the host has shipped a state-migration not yet reflected in this rule. Update the rule in lock-step rather than guessing the new shape.