Changes between Version 7 and Version 8 of RelationalDesign

Timestamp:

01/22/26 19:27:19 (2 weeks ago)

Author:

231118

Comment:

--

RelationalDesign

@@ -13 +13 @@
 Овие ознаки овозможуваат појасно разбирање на структурата на базата на податоци и релациите помеѓу табелите.
 
+---
+
 === Табели ===
 
-1. Tenants ('''__id PK__''', '''name''', '''owner_email''', '''created_at''')
+1. '''Tenants'''  
+   ('''__id PK__''', '''name''', '''owner_email''', '''created_at''')
 
-2. Users ('''__id PK__''', '''email''' (UNIQUE), name, picture, '''created_at''')
+2. '''Users'''  
+   ('''__id PK__''', '''email''' (UNIQUE), name, picture, '''created_at''')
 
-3. Memberships ('''__user_id PK__ FK*(Users.id)''', '''__tenant_id PK__ FK*(Tenants.id)''', '''role''', '''created_at''')
+3. '''Memberships'''  
+   ('''__user_id PK__ FK*(Users.id)''', '''__tenant_id PK__ FK*(Tenants.id)''', role, created_at)  
    *Забелешка:* Memberships има составен примарен клуч: (user_id, tenant_id).
 
-4. Environments ('''__id PK__''', '''name''', '''created_at''', '''tenant_id FK*(Tenants.id)''', (UNIQUE: tenant_id + name))
+4. '''Environments'''  
+   ('''__id PK__''', '''tenant_id FK*(Tenants.id)''', '''name''', '''created_at''', (UNIQUE: tenant_id + name))
 
-5. ENV_Tokens ('''__id PK__''', '''env_name FK*(Environments.name)''', '''token''' (UNIQUE), '''created_at''', expires_at, '''tenant_id FK*(Tenants.id)''')
+5. '''ENV_Tokens'''  
+   ('''__id PK__''', '''tenant_id FK*(Tenants.id)''', '''env_name''', '''token''' (UNIQUE), '''created_at''', expires_at)
 
-6. Admin_sessions ('''__id PK__''', '''token''' (UNIQUE), '''created_at''')
+6. '''Computers'''  
+   ('''__id PK__''', '''tenant_id FK*(Tenants.id)''', '''env_name''', '''name''', user, ip, os, first_seen, last_seen, sysmon_available, (UNIQUE: tenant_id + name))
 
-7. Computers ('''__id PK__''', '''name''', user, ip, os, first_seen, last_seen, sysmon_available, '''env_name FK*(Environments.name)''', '''tenant_id FK*(Tenants.id)''', (UNIQUE: tenant_id + name))
+7. '''Computer_history'''  
+   ('''__id PK__''', '''computer_id FK*(Computers.id)''', cpu_usage, ram_usage, disk_usage, network_sent_mb, network_recv_mb, timestamp)
 
-8. Computer_history ('''__id PK__''', '''computer_id FK*(Computers.id)''', cpu_usage, ram_usage, disk_usage, network_sent_mb, network_recv_mb, timestamp)
+8. '''Computer_processes_current'''  
+   ('''__id PK__''', '''computer_id FK*(Computers.id)''', pid, name, cpu_percent, memory_mb, username, cmdline, timestamp)
 
-9. Computer_processes ('''__id PK__''', '''computer_id FK*(Computers.id)''', pid, name, cpu_percent, memory_mb, username, cmdline, timestamp)
+9. '''Computer_processes_history'''  
+   ('''__id PK__''', '''computer_id FK*(Computers.id)''', pid, name, cpu_percent, memory_mb, username, cmdline, timestamp)
 
-10. Sysmon_events ('''__id PK__''', '''computer_id FK*(Computers.id)''', event_id, event_type, message, timestamp, details)
+10. '''Sysmon_events'''  
+    ('''__id PK__''', '''computer_id FK*(Computers.id)''', event_id, event_type, message, timestamp, details)
 
-11. Network_connections ('''__id PK__''', '''computer_id FK*(Computers.id)''', pid, local_address, remote_address, status, process_name, timestamp)
+11. '''Network_connections'''  
+    ('''__id PK__''', '''computer_id FK*(Computers.id)''', pid, local_address, remote_address, status, process_name, timestamp)
 
-12. Security_alerts ('''__id PK__''', '''computer_id FK*(Computers.id)''', alert_type, severity, description, timestamp, resolved)
+12. '''Security_alerts'''  
+    ('''__id PK__''', '''computer_id FK*(Computers.id)''', alert_type, severity, description, timestamp, resolved)
 
-=== DDL скрипта за бришење на табелите и креирање на табелите ===
+13. '''Env_settings'''  
+    ('''__tenant_id PK__ FK*(Tenants.id)''', '''__env_name PK__''', save_process_history, created_at, updated_at)
+
+---
+
+=== Забелешки за дизајнот ===
+
+* Табелата '''Computer_processes_current''' содржи само моментална snapshot состојба на процесите.
+* Табелата '''Computer_processes_history''' се користи само ако е овозможено снимање на историја преку '''Env_settings'''.
+* Табелата '''Env_settings''' овозможува конфигурација по environment без потреба од глобални флагови.
+* Не се користи посебна табела за admin sessions – автентикацијата се реализира преку JWT cookie.
+
+---
+
+=== DDL скрипта за креирање на табелите ===
 [attachment:ddl_sql.sql ddl_sql.sql]
 
@@ -46 +74 @@
 [attachment:dml.sql dml.sql]
 
+---
+
 === Релациски дијаграм ===