Database Schema

Complete reference for the WarmDesk database schema — tables, columns, relationships, and design decisions

WarmDesk uses GORM with automatic migration (AutoMigrate) — there are no separate migration files. Every time the server starts it compares the model structs to the live schema and adds any missing columns or tables. You should never need to touch the database directly for routine upgrades.

Supported engines: SQLite (default), PostgreSQL, and MySQL.

Design decisions

Fractional position ordering

Columns and cards use a position REAL (float) column. Inserting between two items assigns the midpoint of the surrounding values. This avoids renumbering the entire list on every drag-and-drop.

Atomic card numbering

Each project has a card_counter INTEGER column. The backend increments it atomically when creating a card and uses the result as the human-readable number (e.g. PRJ-42).

Polymorphic attachments

The attachments table stores files for cards, card comments, chat messages, and direct messages through two columns: owner_type (string) and owner_id (integer). Valid owner types: card, card_comment, chat_message, conv_message.

System settings

Operational settings (SMTP, branding, locale defaults, session timeout, …) live in a system_settings key/value table and are read at request time so changes take effect without a restart.

Core tables

users

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

GORM auto-field

updated_at

TIMESTAMP

GORM auto-field

deleted_at

TIMESTAMP

nullable, index

Soft-delete support

email

VARCHAR

unique, not null

Login identifier

username

VARCHAR

unique, not null

Display name

password_hash

VARCHAR

not null

bcrypt, cost 12

global_role

VARCHAR

default user

user, admin

locale

VARCHAR

default en

UI language preference

theme

VARCHAR

default light

light, dark

avatar_url

VARCHAR

nullable

Profile picture path

totp_enabled

BOOLEAN

default false

MFA active flag

totp_secret

VARCHAR

nullable

Encrypted TOTP secret

active

BOOLEAN

default true

Account enabled

must_change_password

BOOLEAN

default false

Force password reset on next login

projects

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

deleted_at

TIMESTAMP

nullable, index

name

VARCHAR

not null

Display name

slug

VARCHAR

unique, not null

URL-safe identifier used in all API routes

description

TEXT

nullable

key_prefix

VARCHAR

not null

Prefix for card numbers (e.g. PRJ)

card_counter

INTEGER

default 0

Atomic counter, incremented per card

board_type

VARCHAR

default kanban

kanban, scrum

customer_id

BIGINT

FK → customers, nullable

Owning customer

color

VARCHAR

nullable

Hex accent colour for the project card

is_archived

BOOLEAN

default false

project_members

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

project_id

BIGINT

FK → projects

user_id

BIGINT

FK → users

role

VARCHAR

default member

viewer, member, owner; admins bypass all role checks

Board tables

columns

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

project_id

BIGINT

FK → projects

name

VARCHAR

not null

position

REAL

not null

Fractional ordering

wip_limit

INTEGER

default 0

0 = unlimited

color

VARCHAR

nullable

Column header accent

cards

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

deleted_at

TIMESTAMP

nullable, index

column_id

BIGINT

FK → columns

Current column

project_id

BIGINT

FK → projects

title

VARCHAR

not null

description

TEXT

nullable

Markdown body

position

REAL

not null

Fractional ordering within column

priority

VARCHAR

default medium

low, medium, high, critical

status

VARCHAR

default open

open, closed

due_date

DATE

nullable

estimated_hours

REAL

default 0

card_number

INTEGER

not null

Human-readable number (PRJ-N)

story_points

INTEGER

default 0

Scrum only

parent_card_id

BIGINT

FK → cards, nullable

Sub-card relationship

reporter_id

BIGINT

FK → users, nullable

Who created the card

card_assignees

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

card_id

BIGINT

FK → cards

user_id

BIGINT

FK → users

card_checklist_items

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

card_id

BIGINT

FK → cards

text

VARCHAR

not null

checked

BOOLEAN

default false

position

REAL

not null

Fractional ordering

card_comments

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

deleted_at

TIMESTAMP

nullable, index

card_id

BIGINT

FK → cards

user_id

BIGINT

FK → users

body

TEXT

not null

Markdown

labels

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

project_id

BIGINT

FK → projects

name

VARCHAR

not null

color

VARCHAR

not null

Hex colour

card_labels

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

card_id

BIGINT

FK → cards

label_id

BIGINT

FK → labels

card_references

Cross-references between cards (e.g. "blocks", "is blocked by", "relates to").

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

card_id

BIGINT

FK → cards

Source card

target_card_id

BIGINT

FK → cards

Referenced card

ref_type

VARCHAR

not null

blocks, blocked_by, relates_to, duplicates

card_history

Audit log for card changes.

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

card_id

BIGINT

FK → cards

user_id

BIGINT

FK → users

field

VARCHAR

not null

Changed field name

old_value

TEXT

nullable

new_value

TEXT

nullable

Customer & contract tables

customers

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

name

VARCHAR(200)

not null

description

TEXT

nullable

logo_url

VARCHAR(500)

nullable

Path to uploaded logo

position

INTEGER

default 0

Display order in the customer list

time_tracking_only

BOOLEAN

default false

Hides from helpdesk/board views; visible in time-tracking only

created_by_id

BIGINT

FK → users, nullable, index

User who created the record (nil = system / seeded)

contracts

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

customer_id

BIGINT

FK → customers, not null, index

name

VARCHAR(200)

not null

description

TEXT

nullable

start_date

TIMESTAMP

nullable

end_date

TIMESTAMP

nullable

price_per_hour

REAL

nullable

Base hourly rate

currency

VARCHAR(3)

default €

Displayed alongside monetary values

contract_time_slots

Defines an alternative rate window on a contract — for example a standby/on-call surcharge outside regular hours. When end_time < start_time the slot crosses midnight; end_day_offset says how many calendar days later the end time falls (1 = next morning, 3 = Monday morning for a Friday–Monday weekend slot).

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

contract_id

BIGINT

FK → contracts, not null, index

label

VARCHAR(100)

nullable

Free-text name, e.g. Standby - week

start_time

VARCHAR(5)

not null

Wall-clock HH:MM

end_time

VARCHAR(5)

not null

Wall-clock HH:MM; if < start_time the slot is overnight

day_type

VARCHAR(10)

default all

all, weekdays, weekends, monday … sunday

end_day_offset

INTEGER

default 0

Calendar days after anchor day when end_time applies (overnight slots)

multiplication_factor

REAL

nullable

Multiplier on price_per_hour (e.g. 1.5 = 150 %)

hourly_rate

REAL

nullable

Flat rate override; multiplied by multiplication_factor if both are set

customer_favorites

Composite primary key — no surrogate id column.

ColumnTypeConstraintsNotes

user_id

BIGINT

PK, FK → users

customer_id

BIGINT

PK, FK → customers

customer_access

Grants a non-admin user explicit visibility of a customer. Non-admin users with no row (direct or via group) cannot see the customer at all. Admins always see all customers regardless of this table.

ColumnTypeConstraintsNotes

user_id

BIGINT

PK, FK → users

customer_id

BIGINT

PK, FK → customers

role

VARCHAR

default member

member = read; admin = read + manage contracts and members

Groups tables

user_groups

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

name

VARCHAR

not null

description

TEXT

nullable

group_members

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

group_id

BIGINT

FK → user_groups

user_id

BIGINT

FK → users

group_project_access

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

group_id

BIGINT

FK → user_groups

project_id

BIGINT

FK → projects

role

VARCHAR

default member

viewer, member, owner

group_customer_access

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

group_id

BIGINT

FK → user_groups

customer_id

BIGINT

FK → customers

role

VARCHAR

default member

viewer, member, owner

Scrum tables

sprints

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

project_id

BIGINT

FK → projects

name

VARCHAR

not null

goal

TEXT

nullable

Sprint goal description

start_date

DATE

nullable

end_date

DATE

nullable

status

VARCHAR

default planned

planned, active, completed

sprint_cards

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

sprint_id

BIGINT

FK → sprints

card_id

BIGINT

FK → cards

Time tracking

time_entries

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

user_id

BIGINT

FK → users

customer_id

BIGINT

FK → customers, nullable

project_id

BIGINT

FK → projects, nullable

card_id

BIGINT

FK → cards, nullable

contract_id

BIGINT

FK → contracts, nullable

date

DATE

not null

minutes

INTEGER

not null

Duration in minutes

description

TEXT

nullable

Discussion tables

topics

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

deleted_at

TIMESTAMP

nullable, index

project_id

BIGINT

FK → projects

user_id

BIGINT

FK → users

Author

title

VARCHAR

not null

body

TEXT

not null

Markdown

is_pinned

BOOLEAN

default false

is_locked

BOOLEAN

default false

No new replies when locked

topic_replies

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

deleted_at

TIMESTAMP

nullable, index

topic_id

BIGINT

FK → topics

user_id

BIGINT

FK → users

body

TEXT

not null

Markdown

Chat tables

chat_messages

Project-level channel messages.

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

deleted_at

TIMESTAMP

nullable, index

project_id

BIGINT

FK → projects

user_id

BIGINT

FK → users

body

TEXT

not null

Markdown

is_bot

BOOLEAN

default false

Messages from webhook/CI integrations

conversations

Direct messages and group DMs.

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

name

VARCHAR

nullable

Set for group conversations

is_group

BOOLEAN

default false

conversation_members

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

conversation_id

BIGINT

FK → conversations

user_id

BIGINT

FK → users

conversation_messages

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

deleted_at

TIMESTAMP

nullable, index

conversation_id

BIGINT

FK → conversations

user_id

BIGINT

FK → users

body

TEXT

not null

Markdown

Files

attachments

Polymorphic file table — one row per uploaded file, regardless of where it is attached.

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

owner_type

VARCHAR

not null

card, card_comment, chat_message, conv_message

owner_id

BIGINT

not null

PK of the owning row

filename

VARCHAR

not null

Stored filename (randomised hex)

original_name

VARCHAR

not null

Original filename as uploaded

mime_type

VARCHAR

not null

Detected server-side from file content

size

BIGINT

not null

Bytes

uploader_id

BIGINT

FK → users

Note
MIME type is detected from the first 512 bytes of the file using net/http.DetectContentType. The Content-Type header sent by the client is ignored.

Integration tables

card_links

Git commit and pull-request links attached to cards.

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

card_id

BIGINT

FK → cards

platform

VARCHAR

not null

github, gitlab, gitea

link_type

VARCHAR

not null

commit, pull_request

url

VARCHAR

not null

title

VARCHAR

nullable

PR/commit title

status

VARCHAR

nullable

PR state (e.g. open, merged)

project_webhooks

Inbound webhooks that push events from external systems into WarmDesk.

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

project_id

BIGINT

FK → projects

token

VARCHAR

not null

Secret used to verify the request

type

VARCHAR

not null

github, gitlab, gitea

active

BOOLEAN

default true

api_keys

Long-lived API keys, primarily for CI/CD automation via the Ticket API.

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

user_id

BIGINT

FK → users

Owning user

project_id

BIGINT

FK → projects, nullable

Scope-limited to one project if set

name

VARCHAR

not null

Human label

key_hash

VARCHAR

not null

SHA-256 of the raw key

key_prefix

VARCHAR

not null

First 8 chars, shown in the UI

last_used_at

TIMESTAMP

nullable

expires_at

TIMESTAMP

nullable

Null = no expiry

System settings

system_settings

ColumnTypeConstraintsNotes

id

BIGINT

PK, auto

created_at

TIMESTAMP

updated_at

TIMESTAMP

key

VARCHAR

unique, not null

Setting identifier

value

TEXT

not null

String value

Settings are read at request time so changes take effect without restarting the server. All valid keys are defined as constants in handlers/system.go.

Entity-relationship overview

users ────────────────── project_members ──── projects
  │                                               │
  │                                           columns
  │                                               │
  ├── group_members ── user_groups               cards
  │                        │                      │
  │              group_project_access ─────────── │
  │              group_customer_access ──── customers
  │                                               │
  └── time_entries ──────────────────────    contracts
        │
        ├── projects
        ├── customers
        └── cards

Key relationships:

  • A project belongs to a customer (optional) and has many columns and cards.

  • project_members is the direct user↔project join table; group_project_access grants access to all group members at once.

  • cards belong to a column and a project; they can have a parent_card_id for sub-cards.

  • attachments are polymorphic: owner_type + owner_id point to any of card, card_comment, chat_message, or conv_message.

  • time_entries link a user to any combination of customer, project, card, and contract.