Database Schema

this document contains the sql create table statements for the database tables that are directly involved in the commandit auto update service it includes the core tables the updater client and server will interact with, along with necessary dependencies for context \ commandit database schema (core tables for updater service v1 4) \ dialect postgresql \ ============================================= \ core updater service tables \ ============================================= \ stores metadata for available agent update packages, including download location, \ verification info, and target specifics this table is queried by the update server \ api (/v1/check) to determine available updates for requesting clients \ assumes the users table exists for the published by user id foreign key create table updatepackages ( package id uuid primary key default gen random uuid(), unique id for this package entry component name varchar(50) not null check (component name in ('service', 'ui', 'probe', 'updater')), which component this package updates version varchar(50) not null, semantic version (e g , '1 2 3', '2 0 0 beta 1') release channel varchar(20) not null check (release channel in ('stable', 'beta', 'alpha')), target release channel target os varchar(20) not null check (target os in ('windows', 'macos', 'linux')), target operating system target arch varchar(10) not null check (target arch in ('amd64', 'arm64')), target architecture download url text not null, url to download the package (e g , do spaces url) file hash sha256 varchar(64) not null, sha 256 hash of the package file for integrity check signature base64 text not null, base64 encoded digital signature of the package hash release notes url text null, optional url to release notes dependencies jsonb null, optional store dependencies, e g , {"service" ">=1 2 0"} published at timestamptz not null default current timestamp, when this package version was made available published by user id uuid null references users(user id) on delete set null, user who published this package is active boolean not null default true, can this package be served? \ ensure only one package per unique combination of component, version, channel, os, and architecture constraint updatepackages unique version unique (component name, version, release channel, target os, target arch) ); comment on table updatepackages is 'stores metadata for available agent update packages, including download location, verification info, and target specifics '; comment on column updatepackages component name is 'the specific commandit agent component this package updates (service, ui, probe, updater) '; comment on column updatepackages version is 'the semantic version string of the component in this package '; comment on column updatepackages release channel is 'the release channel (stable, beta, alpha) this package belongs to '; comment on column updatepackages target os is 'the target operating system (windows, macos, linux) '; comment on column updatepackages target arch is 'the target cpu architecture (amd64, arm64) '; comment on column updatepackages download url is 'url where the client updater can download the package file '; comment on column updatepackages file hash sha256 is 'sha 256 hash of the package file used for integrity verification '; comment on column updatepackages signature base64 is 'base64 encoded digital signature of the package file hash, used for authenticity verification '; comment on column updatepackages dependencies is 'optional jsonb defining minimum versions of other components required by this package '; comment on column updatepackages published at is 'timestamp when this package version was published to the server '; comment on column updatepackages is active is 'indicates if this package version should be considered available for deployment '; \ indexes for common lookup patterns by the server's /v1/check endpoint create index idx updatepackages lookup on updatepackages(release channel, target os, target arch, component name, is active); create index idx updatepackages published on updatepackages(published at desc); \ ============================================= \ core device table (relevant columns) \ ============================================= \ core configuration management database (cmdb) table storing inventory, status, location, \ and key details for all managed or discovered assets the updater client reads/updates \ agent version fields and potentially health status \ assumes referenced tables like organizations, locations, users, vendors, racks, slas etc exist create table devices ( device id uuid primary key, org id uuid not null references organizations(org id) on delete cascade, custodian org id uuid null references organizations(org id) on delete set null, assignment status varchar(30) null check (assignment status in ('instock', 'reserved', 'loanedout', 'rentedout', 'intransittoclient', 'intransittomsp')), related agreement addition id bigint null, references agreementadditions(addition id) on delete set null, managed by server device id uuid null references devices(device id) on delete set null, azure arc resource id text null unique, intune device id text null, sync source varchar(20) null check (sync source in ('agent', 'intune', 'manual', 'otherintegration')), managed network record id uuid null, references managednetworks(managed network record id) on delete set null, floorplan location id uuid null references locations(location id) on delete set null, link to specific floor/room location containing floorplan floorplan x coordinate real null, x coordinate on the linked floorplan floorplan y coordinate real null, y coordinate on the linked floorplan name varchar(255) not null, smbios uuid uuid null, device type varchar(100) not null, is virtual boolean not null default false, status varchar(50) not null default 'active' check (status in ('active', 'inactive', 'inrepair', 'disposed', 'archived', 'lost', 'stolen', 'pendingmatch')), added 'pendingmatch' lifecycle status varchar(30) null check (lifecycle status in ('active', 'watch', 'requiresreview', 'approvedforreplacement', 'pendingdisposal', 'endoflife')), location id uuid references locations(location id) on delete set null, general location (e g , building or site) primary contact user id uuid references users(user id) on delete set null, manufacturer id uuid references manufacturers(manufacturer id) on delete set null, model number varchar(255), mfg part number varchar(255), serial number varchar(255), asset tag varchar(100), imei varchar(20), meid varchar(18), iccid varchar(22), cpu architecture varchar(50), agent version varchar(50) null, legacy/overall version? agent service version varchar(50) null, updated by updater agent ui version varchar(50) null, updated by updater agent probe version varchar(50) null, updated by updater agent updater version varchar(50) null, updated by updater (self update) agent last full sync timestamptz null, agent health status varchar(20) null, potentially updated by updater status reporting agent last health check timestamptz null, potentially updated by updater status reporting agent health details text null, potentially updated by updater status reporting is probe boolean default false, last agent checkin timestamptz null, intune last checkin at timestamptz null, populated from intune sync inventory detail level varchar(20) not null default 'normal', enhanced mode expires at timestamptz, install date timestamptz, installed by user id uuid references users(user id) on delete set null, purchase vendor id uuid null, references vendors(vendor id) on delete set null, purchase date date, warranty expiry date date, warranty provider varchar(255), warranty status varchar(100), last warranty check timestamp timestamptz, warranty details url text, custom sla id uuid null, references slas(sla id) on delete set null, manual replacement date date, ip address inet, external ip address inet, default gateway inet, os type varchar(50), os version varchar(100), firmware version varchar(100) null, new field is reboot pending boolean null, rack id uuid null references racks(rack id) on delete set null, rack position u integer null, rack units consumed integer null default 1 check (rack units consumed is null or rack units consumed >= 1), btu output standard integer null, btu output peak integer null, power consumption watts standard integer null, power consumption watts peak integer null, power draw amps peak numeric(6, 2) null, last logged in user varchar(255), last reported latitude numeric(9, 6) null, last reported longitude numeric(9, 6) null, last location accuracy meters integer null, last location source varchar(30) null check (last location source in ('os gps', 'os wifi', 'os network', 'ip geolocation', 'unknown')), last location report timestamp timestamptz null, windows11 compatibility status varchar(20) check (windows11 compatibility status in ('compatible', 'notcompatible', 'unknown', 'checkfailed')), windows11 incompatibility reasons text\[] null, windows11 last check time timestamptz null, configuration jsonb, scheduled deletion date date null, disposal date date null, disposal method varchar(50) null check (disposal method in ('resale', 'recycle certified', 'recycle uncertified', 'donation', 'destruction physical', 'returntolessor', 'internalreuse', 'unknown', 'vmdeleted')), sanitization method varchar(50) null check (sanitization method in ('wipe nist clear', 'wipe nist purge', 'degauss', 'physical shred', 'physical crush', 'cryptoerase', 'notapplicable', 'unknown')), disposal vendor id uuid null, references vendors(vendor id) on delete set null, disposal certificate attachment id uuid null, references attachments(attachment id) on delete set null, disposal notes text null, created at timestamptz not null default current timestamp, updated at timestamptz, constraint devices org serial unique unique nulls not distinct (org id, serial number), constraint devices org asset tag unique unique nulls not distinct (org id, asset tag), check ((assignment status = 'loanedout' and custodian org id is not null) or assignment status != 'loanedout'), check ((assignment status = 'rentedout' and custodian org id is not null and related agreement addition id is not null) or assignment status != 'rentedout'), unique nulls not distinct (org id, intune device id) ); comment on table devices is 'core configuration management database (cmdb) table storing inventory, status, location, and key details for all managed or discovered assets (physical, virtual, cloud devices, network gear, etc ) '; comment on column devices agent service version is 'version of the core background service component '; comment on column devices agent ui version is 'version of the user interface component '; comment on column devices agent probe version is 'version of the network probe component '; comment on column devices agent updater version is 'version of the standalone updater component '; comment on column devices agent health status is 'overall health status reported by the agent or updater '; comment on column devices agent last health check is 'timestamp of the last health check performed or reported '; comment on column devices agent health details is 'additional details regarding agent health or last error '; \ indexes for devices (copied from provided schema for relevant columns) create index idx devices org id on devices(org id); create index idx devices location id on devices(location id); create index idx devices last checkin on devices(last agent checkin); create index idx devices status on devices(status); create index idx devices intune id on devices(intune device id) where intune device id is not null; \ ============================================= \ agent update logging \ ============================================= \ logs the status and outcome of agent component update attempts on devices \ the client updater writes records to this table create table agentupdatestatuslog ( update log id bigserial primary key, device id uuid not null references devices(device id) on delete cascade, component name varchar(50) not null check (component name in ('service', 'ui', 'probe', 'updater', 'agentbundle')), component being updated trigger source varchar(50) null, e g , 'scheduledpolicy', 'manualcommand', 'initialinstall' requested version varchar(50) not null, the version the update process aimed for current version varchar(50) null, version before the update attempt started status varchar(20) not null check (status in ('pending', 'downloading', 'installing', 'success', 'failed', 'cancelled', 'skipped')), attempt timestamp timestamptz not null default current timestamp, when the update process started completion timestamp timestamptz null, when the update process finished (successfully or not) error message text null, details if status is 'failed' details jsonb null additional details (e g , download size, exit code) ); comment on table agentupdatestatuslog is 'logs the status and outcome of agent component update attempts on devices '; comment on column agentupdatestatuslog component name is 'the specific agent component targeted by the update '; comment on column agentupdatestatuslog status is 'the final status of the update attempt for this component '; comment on column agentupdatestatuslog error message is 'error details if the update attempt failed '; \ indexes for agentupdatestatuslog (copied from provided schema) create index idx agentupdatelog device time on agentupdatestatuslog(device id, attempt timestamp desc); create index idx agentupdatelog status on agentupdatestatuslog(status);