Storage Layers
State is distributed across local SQLite (per node), two active Supabase projects plus one legacy, Strapi content, and a small set of on-disk blobs. This page maps which data lives where.
Local SQLite
openclaw (Pi)
Lives under ~/.openclaw/ on oracle.
| Path | Purpose |
|---|---|
memory/main.sqlite | Agent memory + auto-memory store. Backs MEMORY.md-style persistence for openclaw sessions. |
tasks/runs.sqlite | Task/run history for the openclaw harness (TaskCreate / TaskUpdate / TaskList / TaskStop). |
The openclaw cron mirror (Loom strand openclaw-cron-mirror, every 5 min) reads cron metadata from openclaw and writes synthetic loom_runs / loom_step_runs rows into Supabase keyed by openclaw sessionId.
OptimalOS (per compute node)
Every OptimalOS instance ships its own SQLite at ~/.optimalos/optimalos.db. Local-first: agents and workflows can run without network access; cloud sync is a layer on top.
Tables created by optimalOS/src/db/migrations.ts:
| Table | Purpose |
|---|---|
tasks | Kanban tasks. v5 added track, current_score, current_lane, current_rank, last_scored_at, score_locked, locked_lane. |
briefs | Morning Ritual briefs (source, body, tags, consumed_in_run_id). |
triage_runs | Per-run record (started, completed, status, model, tokens, summaries, error). |
score_history | Per-task score per run (I/U/C/E, score, rank, lane, reasoning, clash_resolution, sources_used). |
score_overrides | User corrections (dimension, old/new value, reason, applied_to_run_id). |
loom_runs | One row per workflow run launch (run_id, workflow_id, trigger, status, host). |
loom_step_runs | One row per step per run (status, started/finished, exit_code, output_tail, error, result, ack_at). |
activity_log | Append-only log of significant events for the orchestration board. |
Most of these tables also sync to Supabase asynchronously. Single-user; no per-user partitioning.
Supabase
Three projects total — two active, one legacy.
OptimalOS Supabase
hbfalrpswysryltysonm.supabase.co — the main cloud truth for Pi/desktop/Hetzner orchestration.
| Table family | Used by |
|---|---|
tasks, task_* | CLI board, OptimalOS orchestration board, Morning Ritual via local mirror. |
activity_log | CLI + OptimalOS, append-only. |
bot_* | CLI agent group: heartbeats, claims, coordination. |
tx_* | CLI tx group: bank transactions. |
content_* | Newsletter, social drafts before Strapi sync. |
research_* | Research pipeline. |
loom_workflows, loom_workflow_steps, loom_runs, loom_step_runs, loom_job_queue | Loom cloud sync + cross-node dispatch. |
loom_workflow_defs | Workflow definitions (mirrored from filesystem on each node). |
openclaw_instances | Per-compute-node heartbeat, capabilities, role. |
scenarios, scenario_* | CLI scenario group. |
| OptimalVault tables (see below) | Fabric vault + paired-device daemon. |
OptimalVault tables
Applied 2026-05-04 migration; T2 RLS forced on all six tables 2026-05-06 (rowsecurity=force, single service_role_all allow policy each, anon + authenticated deny-by-default).
| Table | Purpose |
|---|---|
vault_entries | age-encrypted secret entries. Per-recipient × per-entry rewrap on add/remove. |
vault_recipients | Registered age public keys, one row per (device × recipient label). |
vault_access_log | Append-only audit trail of unlocks. |
devices | Paired devices with capability flags + per-device JWT signing keys. |
pairing_tokens | Short-lived device-grant pairing codes minted by the cockpit. |
vault_install | Per-install Argon2id salt (T11 hardening). |
Current state: 6 active recipients spread across 3 devices (oracle Pi, pop-os desktop, Hetzner CCX13). 0 entries written yet; recovery flow exercised end-to-end during Phase 14.
ReturnPro Supabase
vvutttwunexshxkmygik.supabase.co — FP&A truth.
| Table | Used by |
|---|---|
stg_financials_raw | NetSuite uploads (numeric account_code) and R1 volume detail (text account_code). UNION table — DELETEs must scope by account_code ~ '^[0-9]{5}$'. |
confirmed_income_statements | Confirmed GL, upserted by optimal upload-income-statements. |
dim_account, dim_program_id, dim_master_program, dim_client | Chart of accounts, program / master / client dimensions. |
fpa_baseline_units | FP&A baseline (renamed from fpa_wes_imports in migration 20260330300000). |
fpa_yield_assumptions | Per master-program WIP units + yield overrides. |
user_profiles | Auth roles, including sales_access flag for Pallets For Sale. |
auth.users | Standard Supabase auth. |
| Pallets shared upload + audit | Pallets For Sale shared files + rule audit log. |
Sales (Lovable legacy) Supabase
tgysafeenbcosudycymw.supabase.co — pallets-only.
| Table family | Used by |
|---|---|
| Pallets exclusion rules (8 groups) | /sales/pallets UI on dashboard-returnpro + Alejandro's Lovable app (round-trip). |
| Pallets trend snapshots | Trend chart on /sales/pallets. |
No auth on this instance. The dashboard fronts it with a plain createClient (persistSession: false); auth still happens against ReturnPro.
Strapi CMS storage
Pi-hosted at ~/strapi-cms, exposed via Cloudflare Tunnel as strapi.op-hub.com (and strapi.optimal.miami). Strapi keeps its own DB (SQLite by default; Postgres configurable) for content. Active content types:
| Content type | Purpose |
|---|---|
newsletter | CRE / insurance newsletter drafts. draftAndPublish enabled. Delivery tracking fields (delivery_status, delivered_at, delivery_errors, recipients_count, ghl_campaign_id). |
blog-post | Multi-site blog. site field (portfolio, insurance, …) filters by tenant. |
social-post | Per-platform social ad posts. Brand enum (CRE-11TRUST, LIFEINSUR, OPTIMAL, …). Lifecycle hook publishes on afterCreate. |
brand-config | Per-brand platform IDs + tokens (meta_ig_account_id, meta_access_token, alert_email). Backs the social-post hook. |
On-disk blobs
| Path | Node | Purpose |
|---|---|---|
/opt/optimalos/secrets.env | Hetzner | Fabric cockpit secrets (Supabase service key, JWT signing key, age keys). Read by optimalos.service. Permissions 0600, owner optimal. |
~/.optimalos/transfers/ | All nodes | Transient handoff drop-box (fabric design specs, audit JSONs, work queues). |
~/.optimalos/config.json | All nodes | Per-node OptimalOS config (Phoenix toggle, OTLP URL, etc.). |
~/.openclaw/workspace/optimal-docs/.vitepress/dist/ | Pi | Built VitePress output served by serve.ts on :3001. |
Loom workflow state
Loom is the OptimalOS workflow engine. Definitions and runs live in OptimalOS Supabase:
| Table | Purpose |
|---|---|
loom_workflow_defs | Workflow definitions (mirrored from filesystem on each node). |
loom_workflows | Active strand registrations. |
loom_workflow_steps | Step definitions per workflow. |
loom_runs | One row per run launch. |
loom_step_runs | One row per step per run. |
loom_job_queue | Pending cross-node dispatch jobs keyed by target_host. |
Each compute node mirrors its own runs to local SQLite (~/.optimalos/optimalos.db) so an offline node can still execute scheduled strands.
Phoenix tracing
Phoenix (Arize) runs in Docker on the Pi with SQLite local to the container. Trace data is not synced to Supabase; it lives only on the Pi. A dormant phoenix.service is pre-staged on Hetzner CCX13 (with a snapshot of the Pi data) waiting for the Cloudflare DNS CNAME flip per kanban f997057e. See Phoenix Tracing.
Future: Snowflake (not yet integrated)
The R1 MCP server is in design (Slava's AI team builds the Snowflake + MCP infra; Carlos is the business liaison). Once shipped, R1 volume detail (currently text-account_code rows in stg_financials_raw) and Snowflake-sourced GL extracts will read directly from Snowflake via MCP rather than passing through XLSX uploads. Tracked under the Snowflake-ingest seed strand; not in the storage map yet.