hermes-config/skills/self-hosting/homarr-dashboard-setup/references/db-schema-queries.md

Homarr DB Schema & Reference Queries

Collected from a Homarr v16.2.10 (next-server) instance.

Key table schemas

app

CREATE TABLE IF NOT EXISTS "app" (
    id          TEXT PRIMARY KEY NOT NULL,
    name        TEXT NOT NULL,
    description TEXT,
    icon_url    TEXT NOT NULL,
    href        TEXT,
    ping_url    TEXT
);

item

CREATE TABLE IF NOT EXISTS "item" (
    id               TEXT PRIMARY KEY NOT NULL,
    board_id         TEXT NOT NULL,
    kind             TEXT NOT NULL,
    options          TEXT DEFAULT '{"json": {}}' NOT NULL,
    advanced_options TEXT DEFAULT '{"json": {}}' NOT NULL,
    FOREIGN KEY (board_id) REFERENCES board(id) ON UPDATE no action ON DELETE cascade
);

item_layout

CREATE TABLE IF NOT EXISTS "item_layout" (
    item_id    TEXT NOT NULL,
    section_id TEXT NOT NULL,
    layout_id  TEXT NOT NULL,
    x_offset   INTEGER NOT NULL,
    y_offset   INTEGER NOT NULL,
    width      INTEGER NOT NULL,
    height     INTEGER NOT NULL,
    PRIMARY KEY(item_id, section_id, layout_id),
    FOREIGN KEY (item_id) REFERENCES item(id) ON UPDATE no action ON DELETE cascade,
    FOREIGN KEY (section_id) REFERENCES section(id) ON UPDATE no action ON DELETE cascade,
    FOREIGN KEY (layout_id) REFERENCES layout(id) ON UPDATE no action ON DELETE cascade
);

board

CREATE TABLE IF NOT EXISTS "board" (
    id                          TEXT PRIMARY KEY NOT NULL,
    name                        TEXT NOT NULL UNIQUE,
    is_public                   INTEGER DEFAULT false NOT NULL,
    creator_id                  TEXT,
    page_title                  TEXT,
    meta_title                  TEXT,
    logo_image_url              TEXT,
    favicon_image_url           TEXT,
    background_image_url        TEXT,
    background_image_attachment TEXT DEFAULT 'fixed' NOT NULL,
    background_image_repeat     TEXT DEFAULT 'no-repeat' NOT NULL,
    background_image_size       TEXT DEFAULT 'cover' NOT NULL,
    primary_color               TEXT DEFAULT '#fa5252' NOT NULL,
    secondary_color             TEXT DEFAULT '#fd7e14' NOT NULL,
    opacity                     INTEGER DEFAULT 100 NOT NULL,
    custom_css                  TEXT,
    disable_status              INTEGER DEFAULT false NOT NULL,
    item_radius                 TEXT DEFAULT 'lg' NOT NULL,
    icon_color                  TEXT,
    FOREIGN KEY (creator_id) REFERENCES user(id) ON UPDATE no action ON DELETE set null
);

section

CREATE TABLE IF NOT EXISTS "section" (
    id       TEXT PRIMARY KEY NOT NULL,
    board_id TEXT NOT NULL,
    kind     TEXT DEFAULT 'empty' NOT NULL,
    x_offset INTEGER DEFAULT 0 NOT NULL,
    y_offset INTEGER DEFAULT 0 NOT NULL,
    min_width INTEGER,
    min_height INTEGER,
    FOREIGN KEY (board_id) REFERENCES board(id) ON UPDATE no action ON DELETE cascade
);

section_layout

CREATE TABLE IF NOT EXISTS "section_layout" (
    id         TEXT PRIMARY KEY NOT NULL,
    section_id TEXT NOT NULL,
    layout_id  TEXT NOT NULL,
    FOREIGN KEY (section_id) REFERENCES section(id) ON UPDATE no action ON DELETE cascade,
    FOREIGN KEY (layout_id) REFERENCES layout(id) ON UPDATE no action ON DELETE cascade
);

user

CREATE TABLE IF NOT EXISTS "user" (
    id               TEXT PRIMARY KEY NOT NULL,
    name             TEXT,
    email            TEXT,
    email_verified   INTEGER,
    image            TEXT,
    password         TEXT,
    provider         TEXT DEFAULT 'credentials' NOT NULL,
    home_board_id    TEXT,
    mobile_home_board_id TEXT,
    ...
    FOREIGN KEY (home_board_id) REFERENCES board(id) ON UPDATE no action ON DELETE set null
);

Useful queries

List all apps

SELECT id, name, href, icon_url FROM app ORDER BY name;

List all items on a board with their app names

SELECT i.id, i.kind, a.name
FROM item i
LEFT JOIN app a ON json_extract(i.options, '$.json.appId') = a.id
WHERE i.board_id = '<board_id>'
ORDER BY a.name;

Full layout grid with service names

SELECT l.x_offset, l.y_offset, l.width, l.height, a.name
FROM item_layout l
JOIN item i ON l.item_id = i.id
LEFT JOIN app a ON json_extract(i.options, '$.json.appId') = a.id
ORDER BY l.y_offset, l.x_offset;

Delete all items for a board (clean slate)

DELETE FROM item_layout WHERE item_id IN (SELECT id FROM item WHERE board_id = '<board_id>');
DELETE FROM item WHERE board_id = '<board_id>';

Count items per kind

SELECT kind, COUNT(*) FROM item GROUP BY kind;