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 creates and updates them. The types below are Postgres's; Choose and connect a database 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.

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`	Organizations, mapped to the host's by `external_org_id`
`contact`	People: customers, visitors, leads
`identity`	The ways a contact is known
`agent`	Support team members
`conversation`	Conversations
`participant`	Contacts added to a conversation besides its own
`message`	Messages and internal notes
`conversation_event`	A conversation's timeline
`attachment`	Files, by storage key
`audit_log`	What agents changed through the agent routes
`canned_reply`	Saved answers
`setting`	Values the package and agents keep
`deal`	Deals
`activity`	Notes, calls, meetings and host events on a timeline
`job`	Queued work
`rate_limit`	Hourly counters
`conversation_tag`, `contact_tag`, `company_tag`	Tags, in the order they were added
`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

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

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

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

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

Column	Type	Null	Default	Notes
`id`	`uuid`	no	random
`number`	`integer`	no		The number in the reference, from `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

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

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

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

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.

audit_log

Column	Type	Null	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

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

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

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

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

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

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.

Column	Type	Null	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.

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.

companyCopy Anchor Link

contactCopy Anchor Link

identityCopy Anchor Link

agentCopy Anchor Link

conversationCopy Anchor Link

participantCopy Anchor Link

messageCopy Anchor Link

conversation_eventCopy Anchor Link

attachmentCopy Anchor Link

audit_logCopy Anchor Link

canned_replyCopy Anchor Link

settingCopy Anchor Link

dealCopy Anchor Link

activityCopy Anchor Link

jobCopy Anchor Link

rate_limitCopy Anchor Link

TagsCopy Anchor Link

counterCopy Anchor Link

On this page