Quartz 4

Supabase Integration Hub

9 min read

Supabase Integration Hub

Supabase is the primary data substrate for all OpenClaw state — 73+ production tables in the CCP project, 44 agent memory SQLite DBs rooted here, POSTGRES-CHOKEPOINT enforcement anchor, and single source of truth for LLM call audit, infra config changes, webhook dedup, deal tracking, and vendor-deep-audit datasets. Read this hub before touching any Supabase table, running a migration, or wiring an agent to persistent storage.

Project Map

ProjectIDStatusUse
CCP (Command Center Pro)svueekfvfrvhylxygktbPRODUCTIONPrimary DB — 73 tables: deals, contacts, hubspot_calls, processed_webhook_events, tool_calls, infra_config_changes, webhook_audit_log, sms_messages, properties, and more. Single source of truth.
RERI WebsitepxzxcfjgpteitwktkkizActivereri.co site data
BetterPhonetwhjimxeakiyulfqmzroOrphanedTest data only — do not use for production writes
TradelinespagfmoevzacupoljtxalInactiveLegacy — no active data flow
hmqlqelg(deprecated)DO NOT USEDeprecated project — zero reads, zero writes

Rule: All OpenClaw state mutation goes to CCP (svueekfvfrvhylxygktb). No other project receives production writes. A PR/script targeting any other project ID must be flagged for Henry review.

Quick Reference

FieldValue
VendorSupabase (PostgreSQL-as-a-service)
URLhttps://supabase.com
CCP Dashboardhttps://supabase.com/dashboard/project/svueekfvfrvhylxygktb
KB docREADME
Auth methodService Role Key (server-side, bypasses RLS) / Anon Key (client-side, RLS-gated)
Auth credentialop://Aurora/supabase-ccp/service_role_key (see 1password)
Cred-proxy portn/a
Webhook portn/a (Supabase is a data store, not an inbound webhook receiver)
Webhook handlern/a (Supabase is a data store, not a webhook receiver)
Tunnel pathn/a
Webhook dedup tableprocessed_webhook_events (24h TTL, CCP project)
Outbound API basehttps://svueekfvfrvhylxygktb.supabase.co/rest/v1/
Realtime endpointwss://svueekfvfrvhylxygktb.supabase.co/realtime/v1
Rate limitsPostgREST: practical limit ~1,000 req/s; Realtime: 200 concurrent connections (Pro plan)
Rate-limit action429 → exponential backoff (3 retries), Discord ops alert
CostPro plan (flat monthly); CCP project is the billed entity
Backup/recoverySupabase PITR (point-in-time recovery, Pro plan); daily logical backups
Discord alert channelops
Drift cadenceWeekly via security-audit-funnel.timer; CHOKEPOINT-1 drift checked every 5 min
Statusproduction

POSTGRES-CHOKEPOINT — CRITICAL MANDATE

Three rules. All state mutation obeys them. Violation = governance flag.

CHOKEPOINT-1 — Every LLM Call Writes tool_calls Before Returning

Mechanism: portkey enforces this at the proxy layer (:18900). Every call to Anthropic / OpenAI / Moonshot writes one row to tool_calls in CCP before the response is surfaced.

Schema (tool_calls table — CCP):

ColumnTypeNullableNotes
idUUIDNOT NULLPK
agent_idTEXTNOT NULLper migration 2026-05-02-002
tool_nameTEXTNOT NULLper migration 2026-05-02-002
parametersJSONBnullableMUST contain tier + model keys if present (CHECK constraint)
result_okBOOLNOT NULLper migration 2026-05-02-002
latency_msINTNOT NULLper migration 2026-05-02-002
called_atTIMESTAMPTZNOT NULLper migration 2026-05-02-002
cost_usdNUMERICnullable intentionallyNULL = plan-tier (Max, flat-rate); >0 = API-tier (paid)
tokens_inINTnullable intentionallyNULL = plan-tier
tokens_outINTnullable intentionallyNULL = plan-tier
cache_read_tokensINTnullable intentionallyNULL = plan-tier
cache_write_tokensINTnullable intentionallyNULL = plan-tier

Fallback: If CCP is unreachable → write to /tmp/openclaw/tool-calls-fallback.jsonl → drain to Postgres within 1h or escalate.

Drift detection: tool-calls-health-check.timer runs every 5 min. Compares Portkey call count vs tool_calls INSERT count; >10% delta = Discord #ops alert. Script: workspace/scripts/tool-calls-health-check.js.

Migration ref: workspace/migrations/2026-05-02-002-tool-calls-not-null.sql

Cross-ref: cost-tracking — cost flows from tool_calls to the cost dashboard.

CHOKEPOINT-2 — Config Changes Write infra_config_changes BEFORE Going Live

Applies to: Portkey config edits, Langfuse project rotations, model routing changes, CCR provider list edits, agent route changes, MCP registry edits, port registry edits, skills registry edits, system prompt changes, service config edits.

Schema (infra_config_changes table — CCP):

ColumnRequiredNotes
change_typeNOT NULLportkey_config / langfuse_project / model_routing / ccr_provider / agent_route / service_config / mcp_registry / port_registry / skills_registry / system_prompt / schema_migration / other
target_systemNOT NULLe.g. pc-sonnet-df3a4c / aurora.SOUL.md / proxy.js
before_statenullablesnapshot of config before change
after_stateNOT NULLsnapshot of config after change
reasonNOT NULLwhy the change was made
approved_byNOT NULLHenry / auto-cron / agent-self-approved
applied_viaNOT NULLchat / cron / cli / mcp / migration
rollback_commandnullableexact revert command
smoke_test_resultnullablepost-change test outcome (JSONB)

Rule: A change without an infra_config_changes row is invalid (governance violation). This row must exist BEFORE the change goes live.

Migration ref: workspace/migrations/2026-05-02-001-infra-config-changes.sql

Cross-ref: portkey — all Portkey config rotations trigger a row here.

CHOKEPOINT-3 — Schema Migrations Through workspace/migrations/ Only

Pattern: Timestamp-prefixed SQL files: workspace/migrations/YYYY-MM-DD-NNN-<topic>.sql

Apply:

psql "$SUPABASE_DB_URL" -f workspace/migrations/YYYY-MM-DD-NNN-<topic>.sql

Rules:

  • Each file: BEGIN; … COMMIT; with rollback notes in comments
  • Never raw ALTER TABLE from chat / scripts / hot fixes
  • An infra_config_changes row with change_type='schema_migration' MUST be inserted post-migration
  • Pre-flight checks (data backfill, null counts) commented in file BEFORE BEGIN

Existing migrations:

  • workspace/migrations/2026-05-02-001-infra-config-changes.sql — creates infra_config_changes table (CHOKEPOINT-2)
  • workspace/migrations/2026-05-02-002-tool-calls-not-null.sql — adds NOT NULL + CHECK constraint to tool_calls (CHOKEPOINT-1)

CCP Production Tables (Key Subset)

Full catalog: 73 tables. Below are the ones most referenced by agents and process hubs.

TablePurposeKey FK / Relation
tool_callsLLM call audit (CHOKEPOINT-1)agent_id
infra_config_changesConfig change audit (CHOKEPOINT-2)change_type, target_system
processed_webhook_eventsWebhook dedup (24h TTL)event_id, provider
webhook_audit_logNon-blocking inbound webhook recordprovider, handler_port
dealsReal estate deal recordshubspot_deal_id, stage
contactsContact recordsphone, hubspot_contact_id
propertiesProperty recordsaddress, deal_id
hubspot_callsCall activity synced from HubSpotcontact_id, deal_id
sms_messagesSMS thread recordscontact_phone, direction
il_dealsInvestorLift deal recordsil_deal_id, property_id
il_contactsInvestorLift contact/wholesaler recordsil_contact_id
vendor_audit_findingsVendor deep-audit datasetvendor, tier, finding_type

Vendor deep-audit data (PF-A finding): CCP currently holds 104 vendor records and 4,750 audit findings indexed from the Vendor Deep-Audit Comprehensive run (2026-05-02). This is the live dataset used by vendor-deep-audit-comprehensive-2026-05-02.

Agent Memory / Embeddings Architecture

OpenClaw uses a separate SQLite-per-agent memory layer rather than storing agent memories in Supabase. This is a deliberate design: low-latency reads, no network hop, vec0 native support.

Specs

PropertyValue
Total agent memory DBs44 SQLite DBs
Location/home/opsadmin/.openclaw/memory/*.sqlite
Schema per DBchunks + chunks_fts (FTS5) + chunks_vec (vec0) + dead_letters + embedding_cache + pending_saves + meta + files
Total chunks fleet-wide~5,568 (as of 2026-05-01 audit)
Embedding modelVoyage-4 (70% vector weight)
Search strategyHybrid: 70% vector (Voyage-4 via vec0) + 30% keyword (FTS5)
CLI`python3 /home/opsadmin/.openclaw/tools/claude-memory.py search
MCP toolmemory_search (openclaw-tools MCP server)

Anomaly — Acquisitions DB

acquisitions.sqlite has only 1 chunk (as of 2026-05-01). Investigation pending Phase 7.5 of openclaw-fragmentation-fix-2026-05-01. Do not treat this DB as representative of normal agent memory density.

Relationship to Supabase

Agent memories are not stored in CCP. However:

  • tool_calls captures every LLM call that produces or retrieves memories (CHOKEPOINT-1)
  • Embedding cache hits/misses can be tracked via tool_calls.cache_read_tokens
  • Future OSIL phase may add a Supabase-backed memory federation layer — see openclaw-self-improvement-layer-2026-05-03

Components

  • workspace/migrations/2026-05-02-001-infra-config-changes.sql — CHOKEPOINT-2 schema
  • workspace/migrations/2026-05-02-002-tool-calls-not-null.sql — CHOKEPOINT-1 NOT NULL enforcement
  • workspace/scripts/tool-calls-health-check.js — CHOKEPOINT-1 drift detection cron (5-min cadence)
  • workspace/knowledge-base/supabase/API.md — PostgREST, RLS, Auth, Storage docs
  • workspace/knowledge-base/supabase/WEBHOOKS.md — Database webhooks, Realtime, Edge Functions
  • workspace/knowledge-base/supabase/_audit/ — Audit artifacts and findings
  • /home/opsadmin/.openclaw/memory/*.sqlite — 44 agent memory SQLite DBs (vec0 + FTS5)
  • /home/opsadmin/.openclaw/tools/claude-memory.py — Memory CLI (search/save/list/stats/delete)

How It’s Used

  • Webhook dedup: Every inbound webhook from HubSpot, OpenPhone, SalesMsg, Twilio checks processed_webhook_events before processing. 24h TTL. Prevents double-dispatch.
  • LLM audit trail: Every call routed through Portkey (:18900) writes a tool_calls row. tool-calls-health-check.timer monitors drift every 5 min.
  • Config governance: Before any Portkey config change, model routing change, or service edit, a row is written to infra_config_changes. The change only goes live after this row exists.
  • Deal pipeline state: deals, contacts, properties tables are the canonical source for all RERI acquisition and disposition deal state. deal-ingestion writes here; hubspot syncs bidirectionally.
  • Failure mode: If CCP is unreachable, agents fall back to /tmp/openclaw/tool-calls-fallback.jsonl for CHOKEPOINT-1 and local file logs for webhook audit. Fallback queue drains back to CCP within 1h.
  • Success criteria: tool-calls-health-check.timer shows <10% delta between Portkey calls and tool_calls inserts. processed_webhook_events dedup rate matches expected inbound volume. No infra_config_changes rows with null smoke_test_result older than 1h.

API Access Patterns

JavaScript (CommonJS — project standard)

const { createClient } = require('@supabase/supabase-js');
 
// Server-side (service role — bypasses RLS)
const supabase = createClient(
  process.env.SUPABASE_URL,           // https://svueekfvfrvhylxygktb.supabase.co
  process.env.SUPABASE_SERVICE_KEY    // op://Aurora/supabase-ccp/service_role_key
);
 
// Insert with error handling
const { data, error } = await supabase
  .from('tool_calls')
  .insert({ agent_id: 'aurora', tool_name: 'llm_call', ... });
if (error) throw error;

Pagination (always required for large tables)

// Supabase default max page = 1000 rows. Always paginate.
let page = 0;
const PAGE_SIZE = 500;
let rows = [];
while (true) {
  const { data, error } = await supabase
    .from('deals')
    .select('*')
    .range(page * PAGE_SIZE, (page + 1) * PAGE_SIZE - 1);
  if (error) throw error;
  rows = rows.concat(data);
  if (data.length < PAGE_SIZE) break;
  page++;
}

See feedback_supabase_pagination — unguarded queries silently truncate at 1,000 rows.

Agents That Touch This

  • _summary — primary LLM agent; every call writes tool_calls row
  • _summary — reads/writes deals, contacts, sms_messages
  • _summary — reads deals, writes dispo stage transitions
  • _summary — property enrichment; writes properties table

Skills That Invoke This

Plans That Govern This

Feedback Rules

KB / Source Docs

  • README — top-level KB index
  • API — PostgREST, RLS, Auth, Storage, Management API
  • WEBHOOKS — Database webhooks, Realtime, Edge Functions, pg_net

System Maps

Related Cluster: Memory / Embeddings

Supabase is the anchor for the Memory/Embeddings cluster. Peer hubs:

HubRelationship
anthropicLLM outputs → tool_calls rows via CHOKEPOINT-1
portkeyProxy enforces tool_calls write before returning response
1passwordCCP service role key stored at op://Aurora/supabase-ccp/service_role_key
[[voyage-Tier2]]Voyage-4 embeddings power vec0 hybrid search in the 44 SQLite memory DBs

All process hubs reference Supabase tables. Key cross-refs authored from this hub:

HubTable(s) Involved
cost-trackingtool_calls, infra_config_changes (CHOKEPOINT-1 + CHOKEPOINT-2 data sources)
deal-ingestiondeals, contacts, properties, processed_webhook_events
outreach-stage1sms_messages, deals, processed_webhook_events
followup-stages-2-3-4sms_messages, follow-up state tables
hubspotBidirectional sync with deals, contacts, hubspot_calls
salesmsgsms_messages, processed_webhook_events dedup
openphone-quosms_messages, call records
twiliosms_messages, webhook_audit_log

Credential Reference

G-NO-PLAINTEXT-CREDS: zero plaintext secrets in any tracked file.

CredentialReference
CCP Service Role Keyop://Aurora/supabase-ccp/service_role_key
CCP Anon Keyop://Aurora/supabase-ccp/anon_key
CCP Database URLop://Aurora/supabase-ccp/db_url
RERI Site Service Role Keyop://Aurora/supabase-reri/service_role_key

See 1password for vault Aurora structure and op CLI usage.

Open Issues / TODOs

  • acquisitions.sqlite has only 1 chunk — Phase 7.5 of openclaw-fragmentation-fix-2026-05-01 investigation pending
  • 139 EMPTY tables in CCP (73 total tracked in CLAUDE.md; full count per supabase-substrate-proactive-intelligence-2026-05-01 Phase 0 in progress)
  • 7 hybrid_search RPCs are unwired (5 months) — Phase 3 of substrate plan
  • 60 vector columns: 11 empty — need embedding backfill
  • infra_config_changes migration (2026-05-02-001) is DRAFTED but awaiting Henry execution authorization
  • tool-calls-not-null migration (2026-05-02-002) is DRAFTED but awaiting Henry execution authorization
  • BetterPhone KB docs (API.md, WEBHOOKS.md) reference the orphaned project ID twhjimxeakiyulfqmzro — update KB to reference CCP project for all new documentation

Recent Activity

  • 2026-05-03: hub created (W1-S4, Wave 1 hub-authoring sprint)
  • 2026-05-02: migrations 001 + 002 drafted (POSTGRES-CHOKEPOINT enforcement, Phase 1.4)
  • 2026-05-02: tool-calls-health-check.js shipped (CHOKEPOINT-1 drift detection)
  • 2026-05-01: Vendor deep-audit data indexed — 104 vendors, 4,750 findings in CCP
  • 2026-05-01: CCP table audit: 73 tables, 139 EMPTY discovered; 7 hybrid_search RPCs unwired

Graph View

Backlinks