self-awareness

# Self-awareness — Claude Science's own database and SDK `host.query(sql, params=[], limit=None, df=False)` runs read-only SQLite against Claude Science's own metadata DB. It is only available via the **`repl` tool** (not `python`/`r`). Results are automatically scoped to the current project, so `SELECT * FROM frames` returns only frames in this project. The `repl` tool is stdlib-only — `df=True` returns the raw dict there (use `json.dump(..., open("handoff/q.json","w"))` and load in a `python` cell if you want pandas). ## Dialect and limits - **SQLite.** Epoch-milliseconds for all timestamps (`created_at > strftime('%s','now','-1 day')*1000`). Booleans are `0`/`1`. JSON columns are TEXT — use `json_extract(col, '$.key')`. Recursive CTEs OK. - `SELECT` / `WITH` / `PRAGMA` / `EXPLAIN` only; one statement per call; `?` placeholders with `params=[...]`. - **Scoping.** Most tables are transparently filtered to the current project (and `memories` to the current user) via CTEs that shadow the real tables — `session_claims`, `verification_checks`, and `poller_lease` are unscoped. You therefore **cannot** use `main.table` / `temp.table` — schema-qualified names are rejected. - **Caps.** Default 200 rows (max `limit=1000`); cells >2000 chars are clipped in place with a `…[+N chars]` marker; total serialized output capped at ~100k chars (`truncated=True`, `truncation_reason="total_size_cap"` — narrow your columns). 5-second timeout. - Schema introspection: `host.query("PRAGMA table_info(frames)")` or `host.query("SELECT name, sql FROM sqlite_master WHERE type='table'")`. ## Queryable tables ### Session / conversation **`frames`** — one row per agent frame (a root conversation or a delegated sub-agent). The frame you are running in now is one of these rows. Key columns: `id`, `parent_frame_id`, `root_frame_id`, `agent_name`, `delegate_name`, `status` (`processing`/`completed`/`failed`/`cancelled`/ `awaiting_user_response`/`awaiting_plan_approval`), `model`, `effort`, `input_tokens`, `output_tokens`, `cache_read_tokens`, `cache_write_tokens`, `total_cost`, `task_summary`, `status_description`, `conversation_type`, `name`, `project_id`, `created_at`, `updated_at`, `completed_at`, `last_user_message_at`, `is_hidden`. JSON columns: `input_data` (what started the frame), `output_data` (`json_extract(output_data,'$.response')` is the final response text), `context_data` (the full serialized runner state — see below), `mentioned_artifact_ids`, `specialists_used`. `context_data` is large. It holds the entire runner state under underscore-prefixed keys — notably `$._messages` (the full conversation array), `$._input_tokens` / `$._output_tokens` / `$._total_cost` (same values as the top-level columns), `$._running_children`, `$._plan_json`, `$._compaction_count`, `$._tool_id_to_frame_id`. Selecting it raw will hit the cell cap; use `json_extract`/`json_array_length` to read specific keys. For the messages themselves, prefer `host.frames(frame_id=...)` which paginates — `_messages` via SQL will truncate on any non-trivial session. **`compaction_archives`** — pre-compaction message snapshots. `frame_id`, `compaction_index`, `message_count`, `token_count`, `summary`, `messages` (JSON array), `created_at`. When a frame's `_compaction_count > 0`, the original messages that were summarized live here. **`notifications`** — parent↔child messages. `sender_frame_id`, `recipient_frame_id`, `root_frame_id`, `notification_type`, `payload` (JSON), `read_at`, `created_at`. **`projects`** — `id` (`proj_*`, not a UUID), `name`, `description`, `context`, `user_id`, `uploads_frame_id`, `memory_enabled`, `created_at`, `updated_at`. **`notes`** — user annotations. `project_id`, `target_type`, `target_frame_id`, `target_message_index`, `target_artifact_id`, `content`. ### Artifacts **`artifacts`** — one row per file. `id`, `project_id`, `root_frame_id`, `frame_id`, `filename`, `latest_version_id`, `is_user_upload`, `is_ephemeral`, `folder_id`, `sort_order`, `priority`, `created_at`. **`artifact_versions`** — one row per saved revision. `id`, `artifact_id`, `version_number`, `frame_id`, `content_type`, `size_bytes`, `checksum`, `storage_path`, `extracted_code`, `code_description`, `language`, `agent_name`, `is_intermediate`, `is_checkpoint`, `parent_version_id`, `producing_cell_id` (→ `execution_log.id`), `created_at`. JSON: `lineage_messages`, `dependency_mappings`, `environment_snapshot`, `annotations`, `cell_sources`. Join `artifacts.latest_version_id = artifact_versions.id` for size/type. **`artifact_dependencies`** — DAG edges. `artifact_version_id`, `depends_on_version_id`, `reference_name`. **`artifact_folders`** — `id`, `project_id`, `parent_id`, `name`, `root_frame_id`, `is_conversation_folder`, `is_user_uploads_folder`, `sort_order`. **`content_snapshots`** — content-addressed dedup store. `hash`, `content`, `size_bytes`. Referenced by `artifact_versions.lineage_snapshot_hash` / `env_snapshot_hash`. ### Execution history **`execution_log`** — one row per `python`/`r`/`bash`/`repl` cell, in order. `id`, `frame_id`, `cell_index` (monotonic), `kernel_id`, `kernel_kind` (`analysis`/`operon`), `conda_env`, `language`, `source` (exact submitted code), `stdout`, `stderr`, `exit_status` (`ok`/`error`/`kernel_died`/ `cancelled`), `error_lineno`, `files_written` (JSON `[{path, sha256}]`), `created_at`. This is the ground-truth record of everything you've run. **`host_call_log`** — one row per `host.*` SDK call made inside a cell. `id`, `execution_log_id` (→ `execution_log.id`), `seq`, `method` (`query_db`/`llm`/`mcp`/`list_frames`/…), `args_json`, `derivable`, `data_inline`, `data_ref`, `error`, `bytes`, `created_at`. Ordered by `(execution_log_id, seq)`. ### Compute and verification **`compute_usage`** — remote compute jobs. `job_id`, `environment`, `tier_type` (`gpu`/`cpu`), `provider`, `frame_id`, `project_id`, `started_at`, `ended_at` (null ⇒ running), `expires_at`, `state`, `remote_workdir`, `submit_cell_id`. JSON: `output_specs`, `remote_handle`. **`session_claims`** — falsifiable claims extracted for verification. `root_frame_id`, `frame_id`, `step_id`, `claim_text`, `entities` (JSON), `source` (`agent`/`haiku_extracted`). **`verification_checks`** — reviewer verdicts. `root_frame_id`, `artifact_version_id`, `claim_id`, `claim`, `verdict` (`pass`/`warn`/`fail`/`inconclusive`), `severity`, `evidence`, `rebuttal`, `reviewer_model`, `reviewer_frame_id`, `source_ref` (JSON), `status` (`open`/`resolved`/`unaddressed`), `reflag_count`. **`memories`** — durable beliefs (user-scoped; may be absent on some builds). `id` (`mem_*`), `body`, `subject_project_id`, `subject_artifact_id`, `subject_version_id`, `subject_frame_id`, `source_frame_id`, `origin` (`extractor`/`agent_tool`/`user`), `evidence` (`stated`/`observed`/`inferred`), `superseded_by`, `last_surfaced_at`. **`poller_lease`** — single-writer guard for compute polling. `provider`, `holder`, `expires_at`. ## Denied tables These are rejected with `Table '<name>' is not queryable` — use the listed SDK accessor instead. - Secrets (encrypted at rest, blocked defense-in-depth): `oauth_tokens`, `user_secrets`, `anthropic_api_keys`, `cloud_credentials`. → `host.credentials.list()` for non-secret metadata; `.get(name)` for the decrypted fields — usable in client libraries, redacted only from printed cell output. - Agent/skill/connector configuration (enumerating attack surface has no legitimate raw-SQL use): `user_agents`, `agents`, `custom_agent_prompts`, `bundled_agent_settings`, `capability_settings`, `custom_skills`, `agent_skill_assignments`, `custom_mcp_servers`, `mcp_agent_assignments`, `mcp_tool_grants`, `directory_attachments`. → `host.agents.list()` / `host.skills.list()` / `host.agents.list_connectors()` (load the `customize` skill for that API). - Host filesystem mounts: `host_grants`. → the `list_host_grants` tool (present on sandboxed-network builds). - Compute provider configuration: `compute_providers`. → the `list_compute` / `compute_details` tools. The denylist matches on word boundaries anywhere in the SQL, so a column alias or string literal that happens to equal a denied table name will also be rejected. Host identity (hostname, workspace/pod name) is intentionally not exposed anywhere in this DB (and on Linux builds the sandbox masks it as well) — to know where you're running, ask the user or use `list_compute` labels. ## Worked examples All of these run via the `repl` tool. ```python # Token and cost accounting across every frame in THIS PROJECT (all # sessions). Add `WHERE root_frame_id = ?` with the current root's id to # scope to one session tree. Aggregate server-side so the row cap can't # undercount. r = host.query(""" SELECT COUNT(*) AS n_frames, SUM(input_tokens) AS input_tokens, SUM(output_tokens) AS output_tokens, SUM(cache_read_tokens) AS cache_read_tokens, SUM(cache_write_tokens) AS cache_write_tokens, SUM(total_cost) AS total_cost FROM frames """) n, itok, otok, crd, cwr, cost = r["rows"][0] print(f"{n} frames, ${cost or 0:.4f} total") ``` ```python # Last 10 code cells executed in this project (any frame), with outcome. # Add `WHERE e.frame_id = ?` with the current frame's id to scope to one # frame. host.query(""" SELECT e.frame_id, e.cell_index, e.language, e.kernel_kind, e.conda_env, e.exit_status, substr(e.source, 1, 120) AS src, json_array_length(e.files_written) AS n_files FROM execution_log e ORDER BY e.created_at DESC LIMIT 10 """) ``` ```python # How far into context is each root conversation in this project? Reads # _messages length and compaction count without pulling the whole blob. host.query(""" SELECT id, name, json_array_length(context_data, '$._messages') AS n_messages,