# Tables URL: https://better-helpdesk.com/docs/database/tables/ The tables of the helpdesk schema, every column with its type and default, and what each table holds. Better Helpdesk keeps everything in the host's database: in one schema, `helpdesk`, on Postgres and SQL Server, and in tables prefixed `helpdesk_` on MySQL and SQLite. [`better-helpdesk-migrate`](/docs/operations/cli#better-helpdesk-migrate) creates and updates them. The types below are Postgres's; [Choose and connect a database](/docs/database/connect#what-differs-between-them) says what differs on the others. Ids are `uuid`, generated by the app, and every timestamp is `timestamptz`, written from the app's clock; the defaults the columns still carry are left from before and unused. The TypeScript types (`Conversation`, `Message`) use the column names in camel case: `contact_id` is `contactId`. Related guide: [Report from your own database](/docs/database/reporting). A minor release only adds columns and tables. Changing, renaming or dropping one takes a major release, and its notes say so. | Table | Holds | | --------------------------------------------------------- | -------------------------------------------------------- | | [`company`](#company) | Organizations, mapped to the host's by `external_org_id` | | [`contact`](#contact) | People: customers, visitors, leads | | [`identity`](#identity) | The ways a contact is known | | [`agent`](#agent) | Support team members | | [`conversation`](#conversation) | Conversations | | [`participant`](#participant) | Contacts added to a conversation besides its own | | [`message`](#message) | Messages and internal notes | | [`conversation_event`](#conversation_event) | A conversation's timeline | | [`attachment`](#attachment) | Files, by storage key | | [`audit_log`](#audit_log) | What agents changed through the agent routes | | [`canned_reply`](#canned_reply) | Saved answers | | [`setting`](#setting) | Values the package and agents keep | | [`deal`](#deal) | Deals | | [`activity`](#activity) | Notes, calls, meetings and host events on a timeline | | [`job`](#job) | Queued work | | [`rate_limit`](#rate_limit) | Hourly counters | | [`conversation_tag`, `contact_tag`, `company_tag`](#tags) | Tags, in the order they were added | | [`counter`](#counter) | The next conversation number | The migrator records what it applied in `migration`, and takes `migration_lock` while it runs. `__migrations` lists the migrations from before the adapters, which the first of the new ones reads; the sequence `reference_seq` is no longer used. ## company [#company] | Column | Type | Null | Default | Notes | | ----------------- | ------------- | ---- | ------- | ------------------------------------------------------------------------------------------------- | | `id` | `uuid` | no | random | | | `name` | `text` | no | | | | `external_org_id` | `text` | yes | | The host's organization id. Unique. `null` for a company an agent made. | | `domain` | `text` | yes | | Lower-cased. Indexed. | | `lead_stage` | `text` | yes | | One of `leadStages` when written; a stage the host later removed stays until an agent changes it. | | `custom` | `jsonb` | no | `{}` | Values of `customFields.company`. | | `created_at` | `timestamptz` | no | `now()` | | ## contact [#contact] | Column | Type | Null | Default | Notes | | -------------- | ------------- | ---- | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `uuid` | no | random | | | `name` | `text` | yes | | | | `email` | `text` | yes | | Lower-cased. Indexed. | | `company_id` | `uuid` | yes | | `company.id`; set to `null` when the company is deleted. | | `lead_stage` | `text` | yes | | One of `leadStages` when written; a stage the host later removed stays until an agent changes it. A merge keeps the target's stage, or takes the source's when it has none. | | `custom` | `jsonb` | no | `{}` | Values of `customFields.contact`. A merge keeps both contacts' fields, the target's value where both have one. | | `locale` | `text` | yes | | Decides the language of the customer's emails. A merge keeps the target's, or takes the source's when it has none. | | `blocked` | `boolean` | no | `false` | Refused as a sender, and their conversations left out of the inbox. Partially indexed. | | `created_at` | `timestamptz` | no | `now()` | | | `last_seen_at` | `timestamptz` | yes | | The last time the contact used the widget, signed in or as a visitor. Written at most once every 5 minutes. A merge keeps the later of the two values. | ## identity [#identity] | Column | Type | Null | Default | Notes | | -------------- | ------------- | ---- | ------- | ---------------------------------------------------------------------------------------------- | | `id` | `uuid` | no | random | | | `contact_id` | `uuid` | no | | `contact.id`; deleted with the contact. | | `channel` | `text` | no | | `host`, `email` or `visitor`. The type also allows `github`, which the package does not write. | | `external_id` | `text` | no | | The host user id, the email address, or the SHA-256 of a visitor token. | | `verified` | `boolean` | no | | The host or a DKIM signature proved it. | | `created_at` | `timestamptz` | no | `now()` | | | `last_used_at` | `timestamptz` | no | `now()` | A visitor token unused for 30 days stops opening its contact. | A verified `(channel, external_id)` belongs to one contact only. `(channel, external_id, contact_id)` is unique. ## agent [#agent] | Column | Type | Null | Default | Notes | | ------------------ | ------------- | ---- | ------- | ------------------------------------------------------------------------------------------------------------------------------ | | `id` | `uuid` | no | random | | | `external_user_id` | `text` | no | | The host's user id. Unique. | | `name` | `text` | yes | | From `identify`, rewritten on each agent request. | | `email` | `text` | yes | | Where agent emails go. Rewritten on each agent request. | | `avatar_url` | `text` | yes | | From the identity's `image`, rewritten on each agent request. | | `role` | `text` | no | `agent` | `agent`, `supervisor` or `viewer`, from the identity's `role`, rewritten on each agent request. | | `away_until` | `timestamptz` | yes | | Set while away; the widget promises a reply after it. | | `last_seen_at` | `timestamptz` | no | `now()` | Updated on each agent request. | | `deactivated_at` | `timestamptz` | yes | | Set when the agent is removed from the team; cleared on their next agent request. | | `viewing_id` | `uuid` | yes | | `conversation.id` the agent last opened, set to `null` when it is deleted; others see them on it while `viewing_at` is recent. | | `viewing_at` | `timestamptz` | yes | | | A row is written on an agent's first request, keyed by the host's user id. The package never deletes agent rows. ## conversation [#conversation] | Column | Type | Null | Default | Notes | | --------------------- | ------------- | ---- | --------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `uuid` | no | random | | | `number` | `integer` | no | | The number in the reference, from [`counter`](#counter). Unique. | | `inbox` | `text` | no | | A key of `inboxes`. | | `type` | `text` | no | | One of `types`. | | `kind` | `text` | no | `ticket` | `chat` for a message the customer wrote, `ticket` for a form with a subject or an email. | | `status` | `text` | no | `open` | `open`, `pending` or `resolved`. | | `priority` | `text` | no | `normal` | One of `priorities`: `low`, `normal`, `high` or `urgent` unless the host set its own. | | `tier` | `text` | yes | | One of `tiers`, from `resolveTier` or an agent; picks the SLA policy. | | `channel` | `text` | no | `widget` | Where it came in: `widget`, `email`, `agent` or `api`. | | `subject` | `text` | yes | | What the customer called it; the only name they see. | | `title` | `text` | yes | | The team's name for it, from triage or a rename. | | `contact_id` | `uuid` | no | | The contact it belongs to; deleted with the contact. | | `company_id` | `uuid` | yes | | `company.id`; deleted with the company. | | `shared_with_company` | `boolean` | no | `false` | Other contacts of the company may see and join it. | | `assignee_id` | `uuid` | yes | | `agent.id`; set to `null` when the agent row is deleted. | | `context` | `jsonb` | no | `{}` | What the widget captured: `url`, `title`, `userAgent`, `viewport`, `locale`, `appVersion`, `host`, `errors`, `landingPage`, `referrer`, `utm`. | | `ai_suggestion` | `jsonb` | yes | | Triage: `type`, `priority`, `title`, `summary`, `duplicates` (`conversationId`, `reference`, `reason`), and `acceptedAt` once accepted. | | `waiting_since` | `timestamptz` | yes | | Set while the customer has the last word. | | `reminded_at` | `timestamptz` | yes | | When the last reminder was queued. | | `snoozed_until` | `timestamptz` | yes | | On a `pending` conversation, when `runJobs` reopens it. | | `customer_seen_at` | `timestamptz` | yes | | When the customer last read it in the widget. | | `agent_seen_at` | `timestamptz` | yes | | `last_message_at` as of the last time any agent opened it. | | `agent_typing_until` | `timestamptz` | yes | | Set while an agent writes a reply; the customer sees them typing until it passes. | | `last_message_at` | `timestamptz` | no | `now()` | | | `resolved_at` | `timestamptz` | yes | | Retention counts from it. | | `merged_into_id` | `uuid` | yes | | The conversation this one was merged into; set to `null` when that one is deleted. | | `rating` | `text` | yes | | `good` or `bad`. | | `rating_score` | `integer` | yes | | 1 to 5 on the five-point scale; `rating` is derived from it. | | `rating_comment` | `text` | yes | | | | `rated_at` | `timestamptz` | yes | | | | `first_responded_at` | `timestamptz` | yes | | The first public agent reply. Backfilled from the messages when the column arrived. | | `respond_by` | `timestamptz` | yes | | When the first reply is due, from the SLA policy; kept once answered, so a report can tell whether it was met. | | `resolve_by` | `timestamptz` | yes | | When the resolution is due. Moves forward by the time the clock was paused, so it is read as of the last resume. | | `sla_due_at` | `timestamptz` | yes | | The nearest deadline still to meet while the status is `open`; the inbox sorts by it. Indexed. | | `sla_paused_at` | `timestamptz` | yes | | Set while the clock is paused: the status is `pending` or `resolved`. | | `sla_paused_seconds` | `integer` | no | `0` | Time the clock has spent paused, in the hours the target counts. | | `respond_notice` | `text` | yes | | The last notice sent about the response target: `warning` or `breach`. | | `resolve_notice` | `text` | yes | | The same for the resolution target. | | `reply_since` | `timestamptz` | yes | | When the customer's oldest unanswered message after the first response came in, while the next-reply clock runs. | | `reply_by` | `timestamptz` | yes | | When the answer to it is due. Cleared by a public agent reply, or when the conversation leaves `open`. | | `reply_notice` | `text` | yes | | The last notice sent about the next-reply target. | | `created_at` | `timestamptz` | no | `now()` | | | `search` | `tsvector` | | generated | From `title` and `subject`, with the `simple` configuration. | ## participant [#participant] | Column | Type | Null | Notes | | ----------------- | ------ | ---- | ------------------------------ | | `conversation_id` | `uuid` | no | Deleted with the conversation. | | `contact_id` | `uuid` | no | Deleted with the contact. | The primary key is the pair. ## message [#message] | Column | Type | Null | Default | Notes | | ------------------ | ------------- | ---- | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `id` | `uuid` | no | random | | | `conversation_id` | `uuid` | no | | Deleted with the conversation. | | `author_type` | `text` | no | | `contact`, `agent` or `system`. | | `contact_id` | `uuid` | yes | | The author, for a contact's message; set to `null` when the contact is deleted. | | `agent_id` | `uuid` | yes | | The author, for an agent's message; set to `null` when the agent row is deleted. | | `body` | `text` | no | | The text, with Markdown formatting. | | `internal` | `boolean` | no | `false` | A note only agents see. | | `verified` | `boolean` | yes | | A customer message whose author proved who they are: a host user with a verified email, or mail their domain signed. `null` on messages stored before the column existed. | | `email_message_id` | `text` | yes | | Message-ID of the email it came from. Unique. | | `created_at` | `timestamptz` | no | `now()` | | | `search` | `tsvector` | | generated | From `body`, with the `simple` configuration. | ## conversation_event [#conversation_event] | Column | Type | Null | Default | Notes | | ----------------- | ------------- | ---- | ------- | --------------------------------------------------------------------------------------- | | `id` | `uuid` | no | random | | | `conversation_id` | `uuid` | no | | Deleted with the conversation. | | `agent_id` | `uuid` | yes | | The agent who made the change, if one did; set to `null` when the agent row is deleted. | | `kind` | `text` | no | | See below. | | `data` | `jsonb` | no | `{}` | | | `created_at` | `timestamptz` | no | `now()` | | | `kind` | `data` | | ------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `status`, `priority`, `tier`, `assigneeId`, `type`, `inbox`, `title`, `tags`, `snoozedUntil`, `rating` | `{ from, to }`, plus `by: 'customer'` when the customer changed it. Removing an agent from the team writes an `assigneeId` row with no `agent_id` for each conversation it unassigns. | | `sla.warning`, `sla.breach` | `{ target, due }`: `response` or `resolution`, and the deadline as an ISO timestamp | | `reopened` | none; a customer wrote on a resolved conversation | | `email.sent` | `{ kind, to }`: the email kind (`agent-sla` among them), or `agent-reopened`, and the list of addresses | | `mentioned` | `{ agentIds }` | | `participant.added` | `{ contactId }` | | `merged.into`, `merged.from` | `{ conversationId, reference }` | | `suggestion.accepted`, `suggestion.dismissed` | none | ## attachment [#attachment] | Column | Type | Null | Default | Notes | | ----------------- | ------------- | ---- | ------- | -------------------------------------------------------------------------------------------------------------------------------- | | `id` | `uuid` | no | random | | | `conversation_id` | `uuid` | no | | Deleted with the conversation. | | `message_id` | `uuid` | yes | | Deleted with the message. | | `key` | `text` | no | | The storage key. | | `filename` | `text` | no | | | | `content_type` | `text` | no | | | | `size` | `integer` | no | | Bytes. | | `uploaded` | `boolean` | no | `false` | The file is in storage and shows in the conversation. A file added to a reply or a note stays `false` until its message is sent. | | `created_at` | `timestamptz` | no | `now()` | | The files themselves live with the [storage adapter](/docs/reference/adapters#storage). ## audit_log [#audit_log] | Column | Type | Null | Default | Notes | | ------------ | ------------- | ---- | ------- | --------------------------------------------------------------------------- | | `id` | `uuid` | no | | | | `agent_id` | `uuid` | yes | | The agent; set to `null` when the agent row is deleted. | | `action` | `text` | no | | The method and route, such as `PATCH conversations/:id`. | | `target` | `text` | yes | | The id in the route, when it has one. | | `data` | `jsonb` | no | | `{ fields }`: the names of the fields the request sent, never their values. | | `created_at` | `timestamptz` | no | | | One row per agent request that is not a `GET` and succeeded, except the typing indicator and marking notifications read. The package never deletes these rows; `retentionDays` and the delete functions leave them, so what an agent did to a conversation since deleted is still on record. ## canned_reply [#canned_reply] | Column | Type | Null | Default | Notes | | ------------ | ------------- | ---- | ------- | --------------------------------------------------- | | `id` | `uuid` | no | random | | | `title` | `text` | no | | | | `body` | `text` | no | | | | `locale` | `text` | yes | | `en`, `de`, `fr`, `it`, or `null` for any language. | | `created_at` | `timestamptz` | no | `now()` | | ## setting [#setting] | Column | Type | Null | Default | Notes | | ------------ | ------------- | ---- | ------- | ------------ | | `key` | `text` | no | | Primary key. | | `value` | `jsonb` | no | | | | `updated_at` | `timestamptz` | no | `now()` | | | `key` | Holds | | ------------------------------ | --------------------------------------------------------------------------------------------------------- | | `confirmation` | What the widget says after a first message, per locale. | | `ai` | The AI switches the team turned off or on; a missing one is on. | | `closed` | The days closed in the agent UI's settings, each `{ date, name? }`, in date order. | | `recipients` | The agent ids chosen to receive emails about new requests; empty means everyone seen in the last 30 days. | | `rating-link-secret` | The key that signs rating links, generated on first use. | | `views:shared` | Saved views shared with the team. | | `views:agent:{agentId}` | An agent's own saved views. | | `notifications-seen:{agentId}` | When the agent last read their notifications. | ## deal [#deal] | Column | Type | Null | Default | Notes | | ------------------- | ---------------- | ---- | ------- | ----------------------------------------------------------------------------------------------------- | | `id` | `uuid` | no | random | | | `title` | `text` | no | | | | `company_id` | `uuid` | yes | | Deleted with the company. | | `contact_id` | `uuid` | yes | | Set to `null` when the contact is deleted. | | `stage` | `text` | no | | One of `dealStages` when written; a stage the host later removed stays until an agent moves the deal. | | `stage_changed_at` | `timestamptz` | no | `now()` | | | `value` | `numeric(14, 2)` | yes | | | | `currency` | `text` | no | `CHF` | Three letters. | | `expected_close_at` | `timestamptz` | yes | | | | `owner_id` | `uuid` | yes | | `agent.id`; set to `null` when the agent row is deleted. | | `custom` | `jsonb` | no | `{}` | Values of `customFields.deal`. | | `created_at` | `timestamptz` | no | `now()` | | ## activity [#activity] | Column | Type | Null | Default | Notes | | ------------- | ------------- | ---- | ------- | ------------------------------------------------------------------------- | | `id` | `uuid` | no | random | | | `kind` | `text` | no | | `note`, `call` or `meeting` from an agent; `event` from `helpdesk.track`. | | `contact_id` | `uuid` | yes | | Deleted with the contact. | | `company_id` | `uuid` | yes | | Deleted with the company. | | `deal_id` | `uuid` | yes | | Deleted with the deal. | | `agent_id` | `uuid` | yes | | The agent who logged it; set to `null` when the agent row is deleted. | | `body` | `text` | yes | | | | `event` | `text` | yes | | The host event's name. | | `props` | `jsonb` | yes | | The host event's properties. | | `occurred_at` | `timestamptz` | no | `now()` | | ## job [#job] | Column | Type | Null | Default | Notes | | -------------- | ------------- | ---- | ------- | --------------------------------------------------------------------------------------------- | | `id` | `uuid` | no | random | | | `kind` | `text` | no | | See below. | | `payload` | `jsonb` | no | | | | `run_at` | `timestamptz` | no | `now()` | Due from then. 31 December 9999 after the fifth failed attempt, so it is never claimed again. | | `attempts` | `integer` | no | `0` | | | `locked_until` | `timestamptz` | yes | | A claim, two minutes long. | | `last_error` | `text` | yes | | The last failure's message, cut at 2,000 characters. | | `created_at` | `timestamptz` | no | `now()` | | A job's row is deleted once it completes. | `kind` | Does | | ------------------ | ---------------------------------------------------- | | `notify-agents` | Sends `agent-new`. | | `notify-mentioned` | Sends `agent-mention`. | | `notify-customer` | Sends `customer-reply`, two minutes after the reply. | | `send-receipt` | Sends `customer-receipt`. | | `agent-reminder` | Sends `agent-reminder`. | | `sla-notice` | Sends `agent-sla`. | | `ai-triage` | Runs AI triage. | ## rate_limit [#rate_limit] | Column | Type | Null | Default | Notes | | -------------- | ------------- | ---- | ------- | ---------------------------------------------------------- | | `key` | `text` | no | | What is counted, such as `ip:{address}` or `contact:{id}`. | | `window_start` | `timestamptz` | no | | The start of the hour. | | `count` | `integer` | no | `0` | | The primary key is `(key, window_start)`. Windows older than a day are deleted as new hits arrive. ## Tags [#tags] `conversation_tag`, `contact_tag` and `company_tag` hold one row per tag, unique per owner. Conversation tags are lower-cased; a contact's include the answers to inbox qualifying questions. A contact merge adds the source's tags after the target's. | Column | Type | Null | Default | Notes | | ----------------------------------------------- | --------- | ---- | ------- | --------------------------------------- | | `conversation_id`, `contact_id` or `company_id` | `uuid` | no | | The owner; its tags go with it. | | `tag` | `text` | no | | Indexed. | | `position` | `integer` | no | | The order the tag was added in, from 0. | The primary key is the owner and `tag`. ## counter [#counter] | Column | Type | Null | Default | Notes | | ------- | --------- | ---- | ------- | ------------------------------------------------------------------ | | `name` | `text` | no | | `reference`. Primary key. | | `value` | `integer` | no | | The number the next conversation gets; from 1000 on a new install. |