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