> ## Documentation Index > Fetch the complete documentation index at: https://docs.yourhq.ai/llms.txt > Use this file to discover all available pages before exploring further. # Database schema # Database Schema HQ stores shared state in one Supabase project per workspace. The SQL source of truth lives in [`db/migrations/`](https://github.com/yourhq/yourhq/tree/main/db/migrations) and should be applied in filename order. ## Migrations | File | Contents | | --------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `001_extensions.sql` | Required Postgres extensions (`vector`, `pg_cron`, `pgcrypto`) | | `002_enums.sql` | Shared enum types used across tables | | `003_shared_functions.sql` | `set_updated_at()`, `current_tenant_id()`, `is_valid_timezone()`, `_schema_version` table | | `004_tenants.sql` | Tenant registry, default tenant seed, JWT signup trigger, tenant-scoped RLS | | `005_workspace.sql` | Workspace settings, pipeline stages, field definitions, seed row | | `006_crm.sql` | Tags, campaigns, contacts, organizations, templates, draft sets | | `007_gateways.sql` | Gateway hosts, registration tokens, `consume_gateway_token()` RPC | | `008_agents.sql` | Agent registry, org-chart hierarchy, `agent_reports_chain()` RPC | | `009_interactions.sql` | Contact interaction timeline and sync triggers | | `010_tasks_streams.sql` | Streams, tasks, recurring task series, and scheduling | | `011_comments.sql` | Polymorphic comments with @-mention inbox triggers | | `012_knowledge.sql` | Knowledge items, chunks, semantic/full-text search RPCs | | `013_audit_notifications.sql` | Audit log and notification tables | | `014_agent_inbox.sql` | Agent inbox queue, lease/complete/fail RPCs, task assignment triggers | | `015_agent_commands.sql` | Command queue for agent lifecycle and system operations | | `016_usage_budgets.sql` | LLM usage logging, per-agent budgets, daily usage RPC, and enforcement | | `017_rls_realtime_storage.sql` | Storage bucket setup | | `018_setup_wizard.sql` | `complete_setup()` RPC for the first-run wizard | | `019_entity_links.sql` | Universal polymorphic entity linking table | | `020_routines.sql` | Routines (scheduled and event-driven agent workflows) | | `021_collections.sql` | Custom collections, fields, records, and views | | `022_file_processing.sql` | File processing pipeline for knowledge items | | `023_source_connections.sql` | External source connections for knowledge sync | | `024_routines_extension.sql` | Event triggers for collections, knowledge items, and tasks | | `025_modular_workspace.sql` | Modular workspace onboarding | | `026_secrets.sql` | Encrypted secrets (AES-256-GCM), RLS, Realtime, `source_connections.secret_id` FK | | `027_plugins.sql` | Plugin system: `hq_plugins`, `hq_plugin_events`, `hq_plugin_state`, `hq_plugin_event_queue` tables, `emit_plugin_event()` trigger function, event triggers, pg\_cron cleanup jobs | | `028_task_relations.sql` | Task dependencies and relations (`task_relations` table with `blocked_by`, `blocks`, `relates_to`, `parent_of`, `child_of`), `get_task_relations()` RPC, `notify_blocker_resolved` trigger | | `029_labels.sql` | Managed labels with color and description, `task_labels` junction table | | `030_task_templates.sql` | Reusable task group templates with dependency graphs (JSONB items) | | `031_overdue_escalation.sql` | pg\_cron job to mark overdue tasks as `missed`, inbox notification for assigned agents | | `032_notification_gaps.sql` | Notifications for deliverable submissions and overdue task events | | `033_knowledge_chunk_pipeline.sql` | Knowledge chunk creation, embedding pipeline, and source item indexing fixes | | `034_crm_search_index.sql` | Full-text search index on CRM contacts and organizations | | `035_interactions_nullable_contact.sql` | Allow interactions without a linked contact | | `036_collection_agent_commands.sql` | Agent commands for collection record operations | | `037_source_plugin_support.sql` | Extended source connector plugin support | | `038_gateway_backups.sql` | Gateway backup storage bucket, backup metadata columns on `gateways`, `backup_gateway` command action | | `039_knowledge_library_scope.sql` | `library` scope for knowledge items, `pinned` column removal and migration | 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//` 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 * `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](/concepts/plugins) for the full architecture and [Plugin events](/reference/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` | Status | Meaning | Set by | | -------------- | ----------------------------- | ------------------------ | | `ready` | Operational, heartbeat active | Daemon heartbeat | | `busy` | Actively processing work | Agent runtime | | `error` | Crashed or unreachable | Stale detection / manual | | `provisioning` | Being set up | Provisioning flow | | `hibernating` | System-initiated sleep | Idle detector (hosted) | ### 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.