Skip to main content

​
Database Schema

HQ stores shared state in one Supabase project per workspace. The SQL source of truth lives in db/migrations/ and should be applied in filename order.

​
Migrations

For a fresh Supabase project, the onboarding wizard handles schema installation automatically with a one-click install. You can also run all files in filename order from the SQL Editor manually. For an existing project, review each migration before applying. All files are idempotent (IF NOT EXISTS, CREATE OR REPLACE) and safe to re-run.

​
Trust Model

HQ uses a multi-workspace isolation model.
  • Each Supabase project is a fully-isolated workspace. Within each database, every table enforces tenant-scoped RLS via tenant_id.
  • Every tenant-owned table has a tenant_id column referencing the tenants table. RLS policies enforce tenant_id = current_tenant_id() for the authenticated role, where current_tenant_id() extracts the tenant ID from the JWT’s app_metadata.
  • The service_role key bypasses RLS and has unrestricted access (used by daemons and server actions).
  • Self-hosted deployments use a single default tenant (00000000-0000-0000-0000-000000000000).
Disable public email signup in Supabase unless you intentionally want every signed-up user to have admin-level HQ access.

​
Core Tables

​
Workspace

  • workspace — singleton workspace profile, owner preferences, setup state, and budget defaults.
  • audit_log — append-style audit events for important UI and system actions.
  • notifications — operator-visible notifications such as budget warnings and command outcomes.

​
Agents and Gateways

  • agents — agent metadata, slug, status, gateway assignment, manager assignment, model/thinking configuration, and template metadata.
  • gateways — registered gateway hosts, heartbeat state, labels, reachable URLs, and backup metadata (last_backup_at, last_backup_size_bytes).
  • gateway_registration_tokens — short-lived hashed tokens used by Settings → Gateways → Add Gateway.
  • agent_commands — command queue consumed by the runner daemon.
  • agent_inbox_items — background-work queue consumed by agents through the dispatcher/runtime path.
Important RPCs:
  • lease_command(...) atomically leases pending commands for one gateway.
  • start_command(...), complete_command(...), and fail_command(...) update command lifecycle state.
  • lease_inbox_item(...), complete_inbox_item(...), and fail_inbox_item(...) update inbox lifecycle state.
  • agent_reports_chain(...) returns an agent’s manager chain for cycle detection.

​
Usage and Budgets

  • agent_usage — append-only LLM call records with provider, model, tokens, cache tokens, estimated cost, and metadata.
  • agent_budgets — per-agent budget config and current monthly rollup.
The agent_usage_rollup trigger updates agent_budgets after each usage row. Budget status changes can create budget.warned and budget.exceeded notifications. Important RPCs:
  • recompute_agent_budget(...) — recalculates the budget rollup from raw usage data.
  • get_agent_daily_usage(...) — returns daily spend and token aggregates for the current period.

​
CRM

  • contacts — people records, core fields, pipeline status, priority, relationship strength, and extended custom data.
  • organizations — company/group records and extended custom data.
  • contact_organizations — links between contacts and organizations.
  • interactions — timeline entries for calls, messages, notes, meetings, and other relationship events.
  • templates, campaigns, draft_sets — outreach and campaign workflow data.

​
Tasks

  • tasks — operator and agent work items. Supports model_override and thinking_override columns for per-task model selection when assigned to an agent. Status enum includes missed for overdue escalation. Tasks support subtasks via the parent_id column — tasks with parent_id set are subtasks of the referenced parent task. The main task list filters on parent_id IS NULL to show only top-level tasks; subtasks are displayed inline under their parent.
  • streams — task grouping and workflow lanes.
  • task_series — recurring task definitions. Series-level model_override and thinking_override propagate to spawned instances.
  • task_relations — task-to-task dependency links. Relation types: blocked_by, blocks, relates_to, parent_of, child_of. The get_task_relations(p_task_id) RPC returns relations with joined task metadata. A notify_blocker_resolved trigger fires when a blocking task reaches done or cancelled, creating inbox items for the unblocked task’s assignee.
  • labels — managed workspace labels with name, color, and optional description.
  • task_labels — junction table linking tasks to labels.
  • task_templates — reusable task group templates. Each template has a name, description, and items JSONB array containing task definitions with ref, title, priority, assignee_role, and blocked_by dependency references.
  • comments — polymorphic comments with mention support.
  • entity_links — universal polymorphic links from tasks (and other entities) to knowledge items, contacts, organizations, collection records, and URLs. Extended with deliverable support: is_deliverable flag, review_status (draft, in_review, approved, revision_requested, rejected), review_note, reviewed_at, and submitted_by_agent_id. Agents submit deliverables via the hq_submit_deliverable.py skill; humans review them in the task form’s Deliverables tab.
Task assignment and comment mention triggers enqueue agent_inbox_items for affected agents. When a task has model/thinking overrides, they are included in the inbox item’s context JSONB and passed to the agent’s session at wake time. The escalate_overdue_tasks pg_cron job (every minute) marks tasks past their due date as missed and creates inbox items to notify assigned agents.

​
Knowledge

  • knowledge_items — unified knowledge store for pages, skills, files, and sources. Supports scope (workspace, agent, or library), folders, tags, embeddings, and full-text search.
  • knowledge_folders — folder hierarchy for organizing knowledge items.
  • knowledge_item_agents — junction table linking agent-scoped items to specific agents. Also used to track agent-created skills.
  • knowledge_chunks — chunked content with per-chunk embeddings for granular retrieval. References knowledge_item_id.
Boot context is controlled by scope:
  • A workspace-scoped item is injected in full into every agent’s startup context.
  • An agent-scoped item assigned to specific agents via knowledge_item_agents is index-only in those agents’ boot context and fetched on demand.
  • A library-scoped item is not included in boot context. It remains searchable and fetchable on demand.
Agents can autonomously create skills (kind = 'skill', scope = 'agent') via hq_skill_upsert.py. The script inserts into knowledge_items, links via knowledge_item_agents, and logs an audit entry with actor_type = 'agent'. The audit_log.summary field carries the agent’s one-line reason, which is surfaced on the agent detail page. Important RPCs:
  • search_knowledge_items(...) — semantic vector search over items.
  • search_knowledge_items_text(...) — full-text search over items.
  • search_knowledge_chunks(...) / search_knowledge_chunks_text(...) — search at the chunk level, joining back to the parent item.
  • lease_knowledge_items_for_indexing(...) — atomic lease for the embedder daemon.
  • mark_knowledge_item_indexed(...) / mark_knowledge_item_failed(...) — embedding pipeline status updates.
  • lease_knowledge_items_for_processing(...) / mark_knowledge_item_processed(...) — file processing pipeline.

​
Collections

  • collection_definitions — user-defined table schemas with typed fields.
  • collection_fields — field definitions for each collection (text, number, date, select, etc.).
  • collection_records — JSONB-backed rows belonging to a collection.
  • collection_views — saved views (table, kanban, calendar) with filters and sorting.

​
Secrets

  • secrets — encrypted credentials stored as AES-256-GCM ciphertext (enc:v1: prefix format). Scoped per gateway, optionally per agent. Used for channel tokens (Telegram/Discord/Slack), integration OAuth tokens (Notion, Google Drive), and user-managed API keys.
Key columns:
  • gateway_id (required) — which gateway can decrypt and use this secret.
  • agent_id (nullable) — if set, the secret is scoped to one agent; if NULL, it’s available to all agents on the gateway.
  • key — the environment variable name (e.g. TELEGRAM_BOT_TOKEN, NOTION_SOURCE_A1B2C3D4). Validated /^[A-Z][A-Z0-9_]*$/.
  • encrypted_value — the ciphertext. Never returned in API responses (excluded from SELECT columns in server actions).
  • category — user (operator-managed), channel (auto-created at agent provision), or integration (auto-created by OAuth flows).
  • sync_status — pending → active (synced to gateway filesystem), or error/waiting.
Unique indexes enforce one value per key at each scope level (gateway-wide or per-agent). When both exist, agent-scoped secrets override gateway-level ones. The table is subscribed to Supabase Realtime (REPLICA IDENTITY FULL). The gateway’s secrets_sync daemon picks up INSERT/UPDATE/DELETE events and regenerates .env files within seconds.

​
Source Connections

  • source_connections — external source integrations with credentials and sync schedules. Providers are plugin-based — each connector is a self-contained folder under gateway/connectors/<provider>/ with a manifest.json that defines auth, UI metadata, and capabilities. The writable flag controls whether agents can write back to the source. References secrets via secret_id FK for encrypted OAuth token storage.
  • source_sync_runs — sync execution history and status tracking.
  • entity_links — universal polymorphic linking table. Any owner (task, routine, collection_record, agent) can link to any target (knowledge_item, collection_record, contact, organization, task, or URL).
Entity links replace the earlier task-specific attachments with a generalized model. When an agent claims a task, it receives all linked entities as context. Entity links also serve as the deliverable system for tasks. When is_deliverable = true, the link represents an agent-submitted work product with a review workflow:
  • review_status: draft → in_review → approved / revision_requested / rejected
  • review_note: human feedback on revision requests or rejections
  • submitted_by_agent_id: which agent submitted the deliverable
  • reviewed_at: timestamp of the last review action
Agents submit deliverables via hq_submit_deliverable.py (page, URL, or record types). Humans review and approve/reject from the task form UI.

​
Routines

  • routines — event-driven and schedule-driven rules that turn CRM/task/collection/knowledge changes or cron ticks into agent inbox items.
  • agent_inbox_items — stores the resulting work items, lease state, retry counters, dedup keys, and failure state.
Event routines are evaluated by trigger functions on contacts, tasks, knowledge items, and collection records. Schedule routines use spawn_routine_schedule_items via pg_cron to create inbox items at defined intervals.

​
Plugins

  • hq_plugins — plugin registry. One row per installed plugin. Source types: builtin, local, webhook, marketplace. Key columns: hooks (text array of subscribed events), config (operator settings JSONB), config_schema (JSON Schema for validation), webhook_url/webhook_secret (for remote plugins), capabilities (declared permissions), is_enabled (toggle). Subscribed to Supabase Realtime for live UI updates.
  • hq_plugin_events — execution log. Every hook dispatch is recorded with plugin_id, hook, status (success/error/timeout/skipped), duration_ms, and error_message. 30-day retention via pg_cron.
  • hq_plugin_state — scoped key-value store for plugins. Keyed by (plugin_id, scope_kind, scope_id, state_key). Scope kinds: global, agent, task, workspace.
  • hq_plugin_event_queue — lightweight append-only queue bridging SQL triggers to the plugin runner daemon. Triggers on tasks, agents, knowledge_items, inbox, comments, and secrets write here. The plugin runner polls/subscribes and dispatches to matching plugins. Processed rows are cleaned up hourly via pg_cron.
The emit_plugin_event() function is a generic trigger function that writes to the event queue and strips sensitive fields (e.g. encrypted_value). It’s attached to 11 triggers across core tables. See Plugins for the full architecture and Plugin events for the complete event reference.

​
Configuration Tables

  • field_definitions — custom fields by entity type.
  • pipeline_stages — runtime-configurable pipeline stages.
  • streams — task stream definitions.
  • labels — managed labels for task categorization (Settings → Labels).
  • task_templates — reusable task group templates with dependency graphs (Settings → Templates).
These are configured from the UI and read by forms, tables, kanban views, filters, and agent skills.

​
Status Enums

agent_status: ready, busy, error, provisioning, hibernating gateway_status: ready, error, paused, provisioning, hibernating

​
Schema Version

The _schema_version table tracks the current schema version (currently at v31). The UI reads EXPECTED_SCHEMA_VERSION and compares it to the applied version to show update banners when migrations are pending.

​
Realtime

The runner and dispatcher use Supabase Realtime subscriptions for low-latency wakeups:
  • Runner listens for inserts into agent_commands.
  • Dispatcher listens for inserts into agent_inbox_items.
Both daemons also poll periodically so missed websocket events are eventually reconciled.

​
Applying Migrations

Fresh project:
  1. Create a Supabase project.
  2. Open SQL Editor.
  3. Run the one-click schema install from the onboarding wizard, or manually run every .sql file in db/migrations/ in filename order.
  4. Create an auth user and disable public signup unless needed.
Existing project:
  1. Back up the database.
  2. Run all migration files — they are idempotent (IF NOT EXISTS, CREATE OR REPLACE) and safe to re-run.
  3. Confirm the UI onboarding validator passes.
The schema version table currently tracks v31.