> For the complete documentation index, see [llms.txt](https://mcp.conserver.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mcp.conserver.io/reference/agent_database_schema.md). # Database Schema **Purpose:** Single place to understand what exists in the vCon MCP Supabase database after all migrations in `supabase/migrations/` have been applied. **Not a substitute for migrations.** If this document disagrees with SQL in `supabase/migrations/`, the migrations win. Reconcile by reading migrations in timestamp order or by introspecting a live database. **Related:** IETF field naming and v0.4.0 semantics are summarized in [QUICK\_REFERENCE.md](/reference/quick_reference.md). MCP search tools are described in [Search tools guide](/guide/search.md). PostgreSQL RPC signatures live in `supabase/migrations/` (see migrations that define `search_vcons_keyword`, `search_vcons_semantic`, and related functions). *** ## 1. Extensions Typical extensions enabled (see early migrations): | Extension | Use | | ------------------------ | --------------------------------------------------------------- | | `uuid-ossp` / `pgcrypto` | UUID generation (`gen_random_uuid()`) | | `pg_trgm` | Trigram and GIN ops (often under schema `extensions`) | | `vector` | `pgvector` embeddings (`vector(384)` after dimension migration) | *** ## 2. Core vCon tables (normalized) ### `vcons` Root row per vCon. Internal primary key is `id`; `uuid` is the vCon document UUID. | Column | Type | Notes | | -------------------------------------- | ----------- | --------------------------------------------------------------------------------------------------------- | | `id` | UUID | PK | | `uuid` | UUID | Unique vCon id (document) | | `vcon_version` | VARCHAR(10) | DB default from initial migration is still `'0.3.0'` unless changed; **application code targets `0.4.0`** | | `subject` | TEXT | | | `created_at`, `updated_at` | TIMESTAMPTZ | | | `basename`, `filename` | TEXT | | | `done`, `corrupt` | BOOLEAN | | | `processed_by` | TEXT | | | `privacy_processed`, `redaction_rules` | JSONB | Privacy extension | | `redacted` | JSONB | | | `group_data` | JSONB | | | `extensions` | TEXT\[] | | | `must_support` | TEXT\[] | **Deprecated** name; kept in sync with `critical` (see below) | | `critical` | TEXT\[] | v0.4.0 name (Section 4.1.4) | | `appended` | JSONB | **Deprecated** name; kept in sync with `amended` | | `amended` | JSONB | v0.4.0 name (Section 4.1.9) | | `tenant_id` | TEXT | Multi-tenant scope; `NULL` can mean shared (see RLS) | | `subject_tsvector` | TSVECTOR | Materialized for FTS; maintained by trigger | **Triggers:** `sync_deprecated_fields_trigger` keeps `must_support`/`critical` and `appended`/`amended` aligned during transition. **Views:** `vcons_legacy` exposes old column names; `vcon_field_usage` shows which side is populated. *** ### `parties` | Column | Type | Notes | | ----------------------------------------------------------- | -------- | ----------------------------------------------------------------------- | | `id` | UUID | PK | | `vcon_id` | UUID | FK → `vcons(id)` CASCADE | | `party_index` | INTEGER | Index in vCon `parties` array | | `tel`, `sip`, `stir`, `mailto`, `name`, `did`, `validation` | TEXT | | | `jcard` | JSONB | | | `gmlpos`, `timezone` | TEXT | | | `civicaddress` | JSONB | | | `uuid` | TEXT | **Not a UUID type:** migration widened to TEXT for flexible identifiers | | `data_subject_id` | TEXT | Privacy | | `metadata` | JSONB | | | `tenant_id` | TEXT | Denormalized for RLS | | `party_tsvector` | TSVECTOR | FTS helper | Unique: `(vcon_id, party_index)`. *** ### `dialog` | Column | Type | Notes | | -------------------------------------------------------------- | ----------- | --------------------------------------------------------------- | | `id` | UUID | PK | | `vcon_id` | UUID | FK → `vcons(id)` CASCADE | | `dialog_index` | INTEGER | | | `type` | TEXT | CHECK: `recording`, `text`, `transfer`, `incomplete` | | `start_time` | TIMESTAMPTZ | | | `duration_seconds` | REAL | | | `parties` | INTEGER\[] | Party indices | | `originator` | INTEGER | | | `mediatype`, `filename` | TEXT | | | `body`, `encoding`, `url`, `content_hash` | TEXT | `encoding` constrained to `base64url`, `json`, `none`, or NULL | | `disposition` | TEXT | **No longer restricted** to spec enum (migration dropped CHECK) | | `session_id` | JSONB | v0.4.0 SessionId object shape; use this | | `session_id_legacy` | TEXT | Legacy plain string column after rename | | `application`, `message_id` | TEXT | | | `size_bytes` | BIGINT | | | `metadata` | JSONB | | | `transferee`, `transferor` | INTEGER | Transfer dialog (Section 4.3.12) | | `transfer_target`, `original`, `consultation`, `target_dialog` | INTEGER\[] | | | `tenant_id` | TEXT | | | `body_tsvector` | TSVECTOR | FTS helper | Unique: `(vcon_id, dialog_index)`. *** ### `party_history` | Column | Type | Notes | | ------------- | ----------- | -------------------------------------------------------- | | `id` | UUID | PK | | `dialog_id` | UUID | FK → `dialog(id)` CASCADE | | `party_index` | INTEGER | | | `time` | TIMESTAMPTZ | | | `event` | TEXT | Includes `dtmfdown`, `dtmfup` plus join/hold/mute events | | `dtmf` | TEXT | When event is DTMF | | `tenant_id` | TEXT | | *** ### `attachments` | Column | Type | Notes | | ----------------------------------------------------- | ----------- | ------------------------------------------------------- | | `id` | UUID | PK | | `vcon_id` | UUID | FK → `vcons(id)` CASCADE | | `attachment_index` | INTEGER | | | `type`, `purpose` | TEXT | `purpose` added for spec alignment | | `start_time` | TIMESTAMPTZ | | | `party`, `dialog` | INTEGER | Indices into parties/dialog | | `mimetype` | TEXT | **Legacy column name** in DB; spec field is `mediatype` | | `filename`, `body`, `encoding`, `url`, `content_hash` | TEXT | | | `size_bytes` | BIGINT | | | `metadata` | JSONB | | | `created_at`, `updated_at` | TIMESTAMPTZ | Tag rows drive MV refresh | | `tenant_id` | TEXT | | **Tags:** Stored as attachment `type = 'tags'`, `encoding = 'json'`, `body` a JSON array of `"key:value"` strings. Unique: `(vcon_id, attachment_index)`. **Partial index:** `idx_attachments_tags_partial ON attachments (vcon_id, body) WHERE type = 'tags'` — speeds up tag scans and `getUniqueTags` fallback path (migration `20260416000000`). *** ### `analysis` | Column | Type | Notes | | ----------------------------------------- | ----------- | ---------------------------------- | | `id` | UUID | PK | | `vcon_id` | UUID | FK → `vcons(id)` CASCADE | | `analysis_index` | INTEGER | | | `type` | TEXT | NOT NULL | | `dialog_indices` | INTEGER\[] | | | `mediatype`, `filename` | TEXT | | | `vendor` | TEXT | NOT NULL | | `product`, `schema` | TEXT | Use `schema`, not `schema_version` | | `body`, `encoding`, `url`, `content_hash` | TEXT | | | `created_at` | TIMESTAMPTZ | | | `confidence` | REAL | | | `metadata` | JSONB | | | `tenant_id` | TEXT | | | `body_tsvector` | TSVECTOR | FTS helper | Unique: `(vcon_id, analysis_index)`. *** ### `groups` | Column | Type | Notes | | ----------------------------------------- | ------- | ------------------------ | | `id` | UUID | PK | | `vcon_id` | UUID | FK → `vcons(id)` CASCADE | | `group_index` | INTEGER | | | `uuid` | UUID | Referenced vCon | | `body`, `encoding`, `url`, `content_hash` | TEXT | | | `tenant_id` | TEXT | | Unique: `(vcon_id, group_index)`. *** ## 3. Operational and extension tables ### `vcon_embeddings` Semantic search vectors (dimension **384** after `switch_embeddings_to_384` migration; default model `sentence-transformers/all-MiniLM-L6-v2`). | Column | Notes | | ---------------------------------------- | ------------------------------------ | | `vcon_id` | FK → `vcons(id)` | | `content_type` | e.g. `subject`, `dialog`, `analysis` | | `content_reference` | e.g. dialog index as text | | `content_text` | Source text | | `embedding` | `vector(384)` | | `embedding_model`, `embedding_dimension` | | | `created_at`, `updated_at` | | | `tenant_id` | | Unique `(vcon_id, content_type, content_reference)`. *** ### `embedding_queue` | Column | Notes | | ------------ | ------------ | | `id` | BIGSERIAL PK | | `vcon_id` | FK | | `created_at` | | | `tenant_id` | | Trigger on `vcons` insert can enqueue work. *** ### `s3_sync_tracking` Tracks S3 sync per vCon: `vcon_id` PK, `vcon_uuid`, `s3_key`, `synced_at`, `updated_at`, optional embedding model fields, `tenant_id`. *** ### `privacy_requests` GDPR-style request log (no `tenant_id` in migrations). Columns include `request_id`, `party_identifier`, `request_type`, `request_status`, dates, `metadata`, etc. *** ### `migration_reports` Internal migration audit: `migration_name`, `run_date`, counts, `report_data` JSONB. **RLS:** service-role style access only (see migration). *** ## 4. Materialized view `vcon_tags_mv` Built from tag attachments. Typical columns (see latest MV migration, e.g. `20251210120000_optimize_mv_tags_timestamps.sql`): | Column | Purpose | | ---------------------------------- | --------------------------------------------- | | `tenant_id` | Tenant filter | | `vcon_id` | | | `tags` | JSONB object map from `key:value` tag strings | | `tag_updated_at`, `tag_created_at` | From tag attachment row | **Refresh:** Must be refreshed after tag changes; application or jobs should use `REFRESH MATERIALIZED VIEW CONCURRENTLY` where supported and indexed appropriately. *** ## 5. Notable views | View | Purpose | | ------------------ | -------------------------------------------------------------------- | | `vcons_legacy` | Maps `critical`→`must_support`, `amended`→`appended` for old readers | | `vcon_field_usage` | Inspects which legacy vs new fields are used | *** ## 6. Multi-tenancy and RLS * **`tenant_id`** is denormalized onto child tables for fast policy checks. * Policies typically allow rows where `tenant_id IS NULL OR tenant_id = get_current_tenant_id()` for role `authenticated` (see `20251122175918_create_tenant_rls_policies.sql` and later fixes). * **NULL tenant** rows may be visible across tenants by design; confirm product expectations before writing queries. Helper RPCs such as `set_tenant_context` / `get_current_tenant_id` appear in tenant migrations; use those definitions in migrations as source of truth. *** ## 7. Search and SQL functions Main user-facing RPCs (signatures evolve; confirm in latest migration): * `search_vcons_keyword`, `search_vcons_semantic` (**`vector(384)`**), `search_vcons_hybrid` * `search_vcons_by_tags` (may include time filters) * `backfill_search_vector_batch` for tsvector backfill * `explain_query(q text, run_analyze boolean DEFAULT false)` → `SETOF text` — runs `EXPLAIN` or `EXPLAIN (ANALYZE, BUFFERS)` on a SELECT query and returns the plan as text lines. Cannot use `exec_sql` for this because `exec_sql` wraps queries in `SELECT jsonb_agg(...) FROM (...) t`, making EXPLAIN a subquery (migration `20260416000001`). **Note:** parameter is `run_analyze`, not `analyze` — `analyze` is a PostgreSQL reserved word. * `exec_sql(q text, params jsonb DEFAULT '{}')` — general dynamic SQL RPC; wraps query as `SELECT jsonb_agg(row_to_json(t)) FROM () t`; cannot be used for statements like EXPLAIN * `refresh_vcon_tags_mv()` — refreshes the `vcon_tags_mv` materialized view concurrently Semantic search joins **`vcon_tags_mv`** for tag filtering in current implementations. See [Search tools guide](/guide/search.md) and the `optimize_search_*` / `fix_search_*` migrations for behavior; for exact SQL signatures use the latest migration that defines each function. *** ## 8. Table inventory (quick) | Kind | Name | | ----- | ---------------------------------------------------------------------------------- | | Core | `vcons`, `parties`, `dialog`, `party_history`, `attachments`, `analysis`, `groups` | | Ops | `vcon_embeddings`, `embedding_queue`, `s3_sync_tracking` | | Other | `privacy_requests`, `migration_reports` | | MV | `vcon_tags_mv` | *** ## 9. Common agent mistakes to avoid 1. **Assuming docs/api/schema.md** without checking: prefer this file or migrations. 2. **`must_support` / `appended` vs `critical` / `amended`:** write new code against **`critical`** and **`amended`**; legacy columns exist for transition. 3. **`attachments.mimetype`:** column name in DB is still `mimetype` in many deployments; API/spec use **`mediatype`**. 4. **`parties.uuid`:** type is **TEXT**, not UUID. 5. **`dialog.session_id`:** JSONB object; legacy text may live in `session_id_legacy`. 6. **Embeddings:** **384** dimensions and cosine HNSW index; do not assume 1536 unless you confirm an old branch. 7. **Tags:** not a separate table; attachment type `tags` plus MV for fast search. *** *Last reviewed against migration set including 2026-04-14 fixes. Update this file when schema changes land.*