Breadcrumbs

DB Breaking Changes Guide

Overview

This document outlines significant database schema changes in v4.0 of the Asgard Management Center. These changes primarily focus on migrating from integer IDs to UUID-based identifiers, implementing optimistic locking, and restructuring relationships for better data integrity.

⚠️ Important: If you have tooling that interacts directly with the database, please review these changes carefully and update your applications accordingly.

Migration Checklist

High-level list of what developers need to audit in their database tooling:

  • Update all ID references from integers to UUIDs

  • Modify JOIN statements for new relationship structures

  • Remove references to deleted columns (asgard_id, consistent, etc.)

  • Map old integer IDs using old_id column (temporary, will be removed)

All changes to the databases are sorted by category below:

Asset Management

clients Table

Primary key changes:

  • uuid column renamed to id (now primary key, UUID)

  • version column renamed to system_version

New columns:

  • created_at (TIMESTAMP) - Record creation time

  • version (INTEGER) - For optimistic locking

  • old_id (INTEGER) - DEPRECATED - Contains the old integer ID

Removed columns:

  • last_seen (data migrated to status_since)

  • asgard_id

  • consistent

client_addresses Table

Foreign key changes:

  • client column now references UUID client ID instead of integer

client_labels Table

Foreign key changes:

  • client column now references UUID client ID instead of integer

Asset Requests

client_requests Table

Primary key changes:

  • uuid column renamed to id (now primary key, UUID)

New columns:

  • created_at (TIMESTAMP) - Record creation time

  • version (INTEGER) - For optimistic locking

  • old_id (INTEGER) - Contains the old integer ID, not filled for new records

Removed columns:

  • interfaces (data already present in client_request_addresses)

  • asgard_id

  • consistent

client_request_addresses Table

Foreign key changes:

  • client_request column now references UUID client request ID instead of integer

client_request_labels Table

Foreign key changes:

  • client_request column now references UUID client request ID instead of integer

Tasks and Services

tasks Table

Primary key changes:

  • uuid column renamed to id (now primary key, UUID)

Foreign key changes:

  • client column now references UUID client ID instead of integer

  • grouped_task column now references UUID group task ID instead of integer

New columns:

  • old_id (INTEGER) - Contains the old integer ID, not filled for new records

  • created_at (TIMESTAMP) - Record creation time

Removed columns:

  • asgard_id

  • consistent

grouped_tasks Table

Primary key changes:

  • uuid column renamed to id (now primary key, UUID)

Foreign key changes:

  • master_grouped_task column now references UUID master grouped task ID instead of integer

  • cron_grouped_task column now references UUID scheduled grouped task ID instead of integer

New columns:

  • created_at (TIMESTAMP) - Record creation time

  • old_id (INTEGER) - Contains the old integer ID, not filled for new records

Removed columns:

  • asgard_id

  • consistent

grouped_task_labels Table

Foreign key changes:

  • grouped_task column now references UUID grouped task ID instead of integer

cron_grouped_tasks Table

Primary key changes:

  • uuid column renamed to id (now primary key, UUID)

Foreign key changes:

  • master_grouped_task column now references UUID master grouped task ID

New columns:

  • created_at (TIMESTAMP) - Record creation time

  • old_id (INTEGER) - Contains the old integer ID, not filled for new records

Removed columns:

  • asgard_id

  • consistent

cron_grouped_task_labels Table

Foreign key changes:

  • cron_grouped_task column now references UUID scheduled group task ID instead of integer

services Table

Primary key changes:

  • uuid column renamed to id (now primary key, UUID)

Foreign key changes:

  • client column now references UUID client ID

New columns:

  • created_at (TIMESTAMP) - Record creation time

  • old_id (INTEGER) - DEPRECATED - Contains the old integer ID

Removed columns:

  • asgard_id

  • consistent

services_history Table

Foreign key changes:

  • service column now references UUID service ID

Collected Samples

collected_samples Table

Primary key changes:

  • uuid column renamed to id (now primary key, UUID)

New columns:

  • created_at (TIMESTAMP) - Record creation time

  • old_id (INTEGER) - DEPRECATED - Contains the old integer ID

User Management

users Table

Primary key changes:

  • id converted to UUID type (UUID)

Foreign key changes:

  • creator replaced by creator_user_id (UUID reference)

  • role replaced by role_id (UUID reference)

New columns:

  • updated_at (TIMESTAMP) - Last update time

  • version (INTEGER) - For optimistic locking

  • old_id (INTEGER) - DEPRECATED - Contains the old integer ID

Columns moved to separate tables:

  • last_login, last_login_from → user_last_login table

  • apikey_used, apikey_from → user_last_api_use table

user_last_login Table

New table for login tracking:

SQL
CREATE TABLE `user_last_login`
(
    `user_id`         UUID PRIMARY KEY,
    `last_login_at`   DATETIME     NOT NULL,
    `last_login_from` VARCHAR(127) NOT NULL DEFAULT ''
);


user_last_api_use Table

New table for API usage tracking:

SQL
CREATE TABLE `user_last_api_use`
(
    `user_id`           UUID PRIMARY KEY,
    `last_api_use_at`   DATETIME     NOT NULL,
    `last_api_use_from` VARCHAR(127) NOT NULL DEFAULT ''
);


user_frontend_preferences Table

Replaces user_settings with UUID references:

SQL
CREATE TABLE `user_frontend_preferences`
(
    `user_id` UUID     NOT NULL,
    `key`     VARCHAR(255) NOT NULL,
    `value`   TEXT         NOT NULL,
    UNIQUE (`user_id`, `key`)
);

Role Management

roles Table

Primary key changes:

  • id converted to UUID type (UUID)

New columns:

  • created_at (TIMESTAMP) - Role creation time

  • updated_at (TIMESTAMP) - Last update time

  • version (INTEGER) - For optimistic locking

  • old_id (INTEGER) - DEPRECATED - Contains the old integer ID


role_rights Table

Replaced with new structure using UUID references:

SQL
CREATE TABLE `role_rights`
(
    `role_id` UUID     NOT NULL,
    `right`   VARCHAR(255) NOT NULL
);

LDAP Integration

ldap_roles Table

Primary key changes:

  • id converted to UUID type (UUID)

  • old_id (INTEGER) - DEPRECATED - Contains the old integer ID

Foreign key changes:

  • role replaced by role_id (UUID reference)

Migration Examples

Example 1: Querying clients with the new UUID primary key

SQL
-- Before: Integer ID
SELECT * FROM clients WHERE id = 12345;

-- After: UUID primary key
SELECT * FROM clients WHERE id = '550e8400-e29b-41d4-a716-446655440000';

-- Mapping old IDs (temporary, old_id will be removed in future release)
SELECT * FROM clients WHERE old_id = 12345;

Example 2: Joining tables with new UUID foreign keys

SQL
-- Before: Integer foreign keys
SELECT c.*, ca.address 
FROM clients c
JOIN client_addresses ca ON c.id = ca.client
WHERE c.id = 12345;

-- After: UUID foreign keys
SELECT c.*, ca.address 
FROM clients c
JOIN client_addresses ca ON c.id = ca.client
WHERE c.id = '550e8400-e29b-41d4-a716-446655440000';

Important Notes

old_id Column

The old_id column is provided for migration and mapping purposes only:

  • Contains the previous integer ID for existing records

  • Not populated for new records created after migration

  • Will be removed in a future release

  • Should only be used temporarily during migration period

Getting Help

If you need assistance updating your internal tooling or have questions about these changes, please contact our support team before upgrading.