Dieses Dokument enthält das komplette DDL-Schema aller registrierten Tabellen in der relationalen SQLite-Datenbank des Portals.
CREATE TABLE entities (
id VARCHAR(36) NOT NULL,
tenant_id VARCHAR(50),
type VARCHAR(50),
created_at DATETIME,
PRIMARY KEY (id)
);
CREATE INDEX ix_entities_tenant_id ON entities (tenant_id);
CREATE TABLE collaborative_documents (
document_id VARCHAR(36) NOT NULL,
tenant_id VARCHAR(50) NOT NULL,
context_type VARCHAR(50) NOT NULL,
ydoc_state_vector BLOB NOT NULL,
updated_at DATETIME,
PRIMARY KEY (document_id)
);
CREATE TABLE entity_links (
source_id VARCHAR(36) NOT NULL,
target_id VARCHAR(36) NOT NULL,
link_type VARCHAR(50),
created_at DATETIME,
PRIMARY KEY (source_id, target_id),
FOREIGN KEY(source_id) REFERENCES entities (id),
FOREIGN KEY(target_id) REFERENCES entities (id)
);
CREATE TABLE user_accounts (
email VARCHAR(255) NOT NULL,
display_name VARCHAR(255),
hashed_password VARCHAR(255),
is_active BOOLEAN,
is_superuser BOOLEAN,
role VARCHAR(50),
mfa_enabled BOOLEAN,
mfa_secret VARCHAR(64),
oauth_providers JSON,
supervisor_id VARCHAR(36),
department_id VARCHAR(50),
job_title VARCHAR(100),
job_description TEXT,
responsibilities VARCHAR(500),
phone_number VARCHAR(50),
address VARCHAR(255),
company VARCHAR(255),
employee_id VARCHAR(50),
kiosk_token VARCHAR(100),
onboarding_completed BOOLEAN,
onboarding_data JSON,
onboarding_start_date VARCHAR(20),
favorite_modules VARCHAR(500),
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(supervisor_id) REFERENCES user_accounts (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX ix_user_accounts_email ON user_accounts (email);
CREATE UNIQUE INDEX ix_user_accounts_kiosk_token ON user_accounts (kiosk_token);
CREATE TABLE mod_absences (
user_id VARCHAR(36) NOT NULL,
delegate_id VARCHAR(36),
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
reason VARCHAR(200),
is_active BOOLEAN,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE INDEX ix_mod_absences_user_id ON mod_absences (user_id);
CREATE TABLE articles (
sku VARCHAR(50) NOT NULL,
name VARCHAR(150) NOT NULL,
description TEXT,
manufacturer VARCHAR(100),
min_stock INTEGER,
width INTEGER,
height INTEGER,
depth INTEGER,
weight INTEGER,
created_at DATETIME,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX ix_articles_sku ON articles (sku);
CREATE TABLE locations (
name VARCHAR(100) NOT NULL,
type VARCHAR(20) NOT NULL,
description TEXT,
is_storage BOOLEAN,
width INTEGER,
height INTEGER,
depth INTEGER,
max_weight INTEGER,
layout TEXT,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE mod_calendar_events (
id VARCHAR(36) NOT NULL,
title VARCHAR(255) NOT NULL,
description VARCHAR(1000),
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
location VARCHAR(255),
color VARCHAR(7),
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (id, entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE mod_cloud_files (
filename VARCHAR(255) NOT NULL,
file_type VARCHAR(100),
size INTEGER,
storage_path VARCHAR(512) NOT NULL,
owner_id VARCHAR(36) NOT NULL,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE mod_ticket_comments (
content TEXT NOT NULL,
comment_type VARCHAR(20),
author_id VARCHAR(36) NOT NULL,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE mod_ticket_history (
id VARCHAR(36) NOT NULL,
field_name VARCHAR(50) NOT NULL,
old_value TEXT,
new_value TEXT,
changed_at DATETIME,
changed_by_name VARCHAR(100),
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (id, entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE mod_journal_entries (
date_key VARCHAR(10) NOT NULL,
main_tasks TEXT NOT NULL,
learned_today TEXT NOT NULL,
challenges TEXT,
topics_to_explore TEXT,
has_plan_for_tomorrow VARCHAR(10) NOT NULL,
no_plan_reason TEXT,
plan_for_tomorrow TEXT NOT NULL,
ticket_ids TEXT,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE INDEX ix_mod_journal_entries_date_key ON mod_journal_entries (date_key);
CREATE TABLE mod_project_tasks (
title VARCHAR(255) NOT NULL,
duration INTEGER,
status VARCHAR(50),
is_fixed BOOLEAN,
fixed_start_time INTEGER,
urgency_score FLOAT,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE mod_project_details (
name VARCHAR(200) NOT NULL,
description VARCHAR(1000),
status VARCHAR(50),
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE mod_repair_details (
repair_vendor VARCHAR(50),
repair_serial VARCHAR(100),
lenovo_mtm VARCHAR(50),
lenovo_productname VARCHAR(100),
lenovo_warrantytyp VARCHAR(50),
lenovo_warrantyaddon VARCHAR(100),
lenovo_warrantyenddate DATETIME,
repair_error TEXT,
checkin_photos JSON,
checkin_accessories JSON,
action_plans JSON,
shipping_in_tracking VARCHAR(100),
shipping_out_tracking VARCHAR(100),
shipping_parts_tracking JSON,
device_in_date DATETIME,
device_out_date DATETIME,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE INDEX ix_mod_repair_details_repair_serial ON mod_repair_details (repair_serial);
CREATE TABLE mod_subtasks (
parent_id VARCHAR(36) NOT NULL,
title VARCHAR(255) NOT NULL,
status VARCHAR(50),
description TEXT,
due_date DATETIME,
task_type VARCHAR(50),
tracking_number VARCHAR(255),
location_id VARCHAR(36),
items JSON,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE INDEX ix_mod_subtasks_parent_id ON mod_subtasks (parent_id);
CREATE TABLE mod_ticket_tasks (
id VARCHAR(36) NOT NULL,
title VARCHAR(255) NOT NULL,
status VARCHAR(50),
is_mandatory BOOLEAN,
qr_id VARCHAR(50),
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (id, entity_id),
UNIQUE (qr_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE mod_task_details (
title VARCHAR(200) NOT NULL,
description TEXT,
status VARCHAR(50),
priority VARCHAR(20),
owner_id VARCHAR(36) NOT NULL,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE mod_team_details (
entity_id VARCHAR(36) NOT NULL,
name VARCHAR(100) NOT NULL,
description VARCHAR(500),
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE,
UNIQUE (name)
);
CREATE TABLE mod_ticket_details (
title VARCHAR(200) NOT NULL,
description TEXT,
status VARCHAR(50),
priority VARCHAR(20),
ticket_type VARCHAR(50),
source VARCHAR(50),
impact VARCHAR(20),
urgency VARCHAR(20),
sla_status VARCHAR(20),
sla_expires_at DATETIME,
serial_number VARCHAR(100),
warranty_type VARCHAR(50),
part_status VARCHAR(50),
reporter_email VARCHAR(255),
reporter_name VARCHAR(255),
reporter_phone VARCHAR(50),
reporter_street VARCHAR(255),
reporter_city VARCHAR(100),
reporter_company VARCHAR(255),
external_id VARCHAR(100),
waiting_for_id VARCHAR(36),
responded_at DATETIME,
resolved_at DATETIME,
start_at DATETIME,
sla_deadline DATETIME,
global_status VARCHAR(50),
repair_status VARCHAR(50),
ticket_eta DATETIME,
repair_onsite_required VARCHAR(10),
isescalated BOOLEAN,
paused_at DATETIME,
sla_paused_duration_seconds INTEGER,
resolution_text TEXT,
resolution_code VARCHAR(50),
cab_approved BOOLEAN,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE mod_time_logs (
ticket_id VARCHAR(36) NOT NULL,
user_id VARCHAR(36) NOT NULL,
duration_seconds INTEGER NOT NULL,
created_at DATETIME,
activity_type VARCHAR(50) NOT NULL,
billable BOOLEAN,
travel_km FLOAT,
description TEXT,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE INDEX ix_mod_time_logs_ticket_id ON mod_time_logs (ticket_id);
CREATE INDEX ix_mod_time_logs_user_id ON mod_time_logs (user_id);
CREATE TABLE training_modules (
title VARCHAR(200) NOT NULL,
description VARCHAR(500),
content TEXT NOT NULL,
category VARCHAR(50),
min_score_percent INTEGER,
is_mandatory BOOLEAN,
estimated_minutes INTEGER,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE mod_warehouse_items (
name VARCHAR(200) NOT NULL,
serial_number VARCHAR(100),
category VARCHAR(50),
status VARCHAR(50),
location_id VARCHAR(36),
photo_path VARCHAR(255),
delivery_note_path VARCHAR(255),
ocr_content VARCHAR,
received_at VARCHAR(50),
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX ix_mod_warehouse_items_serial_number ON mod_warehouse_items (serial_number);
CREATE TABLE mod_wiki_articles (
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
category VARCHAR(100),
tags VARCHAR(255),
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE api_keys (
key_hash VARCHAR(255) NOT NULL,
name VARCHAR(100) NOT NULL,
user_id VARCHAR(36) NOT NULL,
is_active BOOLEAN,
last_used VARCHAR(50),
expires_at VARCHAR(50),
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(user_id) REFERENCES user_accounts (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX ix_api_keys_key_hash ON api_keys (key_hash);
CREATE TABLE inventory_stocks (
article_id VARCHAR(36) NOT NULL,
location_id VARCHAR(36) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (article_id, location_id),
FOREIGN KEY(article_id) REFERENCES articles (entity_id) ON DELETE CASCADE,
FOREIGN KEY(location_id) REFERENCES locations (entity_id) ON DELETE CASCADE
);
CREATE TABLE assets (
type VARCHAR(20) NOT NULL,
article_id VARCHAR(36),
current_location_id VARCHAR(36),
last_user_id VARCHAR(36) NOT NULL,
status VARCHAR(50) NOT NULL,
created_at DATETIME,
updated_at DATETIME,
entity_id VARCHAR(36) NOT NULL,
PRIMARY KEY (entity_id),
FOREIGN KEY(article_id) REFERENCES articles (entity_id) ON DELETE SET NULL,
FOREIGN KEY(current_location_id) REFERENCES locations (entity_id) ON DELETE SET NULL,
FOREIGN KEY(last_user_id) REFERENCES user_accounts (entity_id),
FOREIGN KEY(entity_id) REFERENCES entities (id) ON DELETE CASCADE
);
CREATE TABLE training_questions (
id VARCHAR(36) NOT NULL,
module_id VARCHAR(36) NOT NULL,
text TEXT NOT NULL,
type VARCHAR(20),
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES entities (id) ON DELETE CASCADE,
FOREIGN KEY(module_id) REFERENCES training_modules (entity_id) ON DELETE CASCADE
);
CREATE TABLE training_progress (
id VARCHAR(36) NOT NULL,
user_id VARCHAR(36) NOT NULL,
module_id VARCHAR(36) NOT NULL,
completed_at DATETIME,
score FLOAT,
is_passed BOOLEAN,
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES entities (id) ON DELETE CASCADE,
FOREIGN KEY(user_id) REFERENCES user_accounts (entity_id) ON DELETE CASCADE,
FOREIGN KEY(module_id) REFERENCES training_modules (entity_id) ON DELETE CASCADE
);
CREATE TABLE asset_logs (
id INTEGER NOT NULL,
asset_id VARCHAR(36) NOT NULL,
location_id VARCHAR(36) NOT NULL,
user_id VARCHAR(36) NOT NULL,
action VARCHAR(50) NOT NULL,
notes TEXT,
timestamp DATETIME,
PRIMARY KEY (id),
FOREIGN KEY(asset_id) REFERENCES assets (entity_id) ON DELETE CASCADE,
FOREIGN KEY(location_id) REFERENCES locations (entity_id),
FOREIGN KEY(user_id) REFERENCES user_accounts (entity_id)
);
CREATE TABLE training_answers (
id VARCHAR(36) NOT NULL,
question_id VARCHAR(36) NOT NULL,
text TEXT NOT NULL,
is_correct BOOLEAN,
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES entities (id) ON DELETE CASCADE,
FOREIGN KEY(question_id) REFERENCES training_questions (id) ON DELETE CASCADE
);