= Other Topics =

== SQL Performance ==
Documented performance analysis for complex analytical queries executed in PostgreSQL (project schema, v04). We utilized the `EXPLAIN (ANALYZE, BUFFERS)` paradigm to isolate structural bottlenecks before and after implementing precise indexes, as well as optimizing time-window filters for high selectivity.

=== Report 1: Top Outbound Connections by Process ===
* '''Query Description:''' Evaluates massive volumes of raw outbound network connection logs (`network_connections_history`) mapped inside a time-bounded window via CTE expressions to detect process-level traffic volume.
* '''Proposed Indexes:'''
{{{
#!sql
CREATE INDEX idx_nch_timestamp_comp ON network_connections_history (timestamp, computer_id);
CREATE INDEX idx_computers_tenant_env ON computers (tenant_id, env_name);
}}}

==== Execution Plan Analysis ====
* '''Before Index Creation:'''
  * Plan Output: `Seq Scan on network_connections_history nc` (Rows Removed by Filter: ~96,647 — the engine is forced to perform an expansive sequential evaluation across log records).
  * Execution Time: 73.836 ms
[[Image(ss1_before.png)]]

* '''After Index Creation (Optimized Time-Window Filter):'''
  * Plan Output: `Bitmap Index Scan using idx_nch_timestamp_comp` feeding a `Bitmap Heap Scan on network_connections_history nc`.
  * Execution Time: 14.924 ms
[[Image(ss1_after.png)]]

* '''Index Usage Verification:''' Yes, the PostgreSQL engine bypassed raw relational sequential evaluation and bound query execution directly via index nodes inside the initial windowed CTE materialization.
* '''Conclusion:''' Performance scaled by over '''79%''' (73.8 ms → 14.9 ms), successfully shifting execution path to targeted bitmap index scans.

---

=== Report 2: Unresolved Security Alerts by Severity ===
* '''Query Description:''' Groups and breaks down security events across custom temporal partitions using aggregations to gauge environmental vulnerability baselines.
* '''Proposed Index:'''
{{{
#!sql
CREATE INDEX idx_sa_timestamp_comp ON security_alerts (timestamp, computer_id, resolved);
}}}

==== Execution Plan Analysis ====
* '''Before Index Creation:'''
  * Plan Output: `Seq Scan on security_alerts sa` (Rows Removed by Filter: ~96,604 — forced sequential full table scan).
  * Execution Time: 59.983 ms
[[Image(ss2_before.png)]]

* '''After Index Creation (Optimized Time-Window Filter):'''
  * Plan Output: `Bitmap Index Scan using idx_sa_timestamp_comp` feeding a `Bitmap Heap Scan on security_alerts sa`.
  * Execution Time: 8.799 ms
[[Image(ss2_after.png)]]

* '''Index Usage Verification:''' Yes, explicitly verified. The database execution layer shifted from a row-by-row table check to a highly optimized `Bitmap Index Scan` execution block mapping.
* '''Conclusion:''' Performance scaled by over '''85%''' (59.9 ms → 8.8 ms), successfully validating index utilization under optimal predicate selectivity conditions.

---

=== Report 3: Resource Hotspots (CPU/RAM Overload) ===
* '''Query Description:''' Evaluates structural system telemetry logs (`computer_history`) over a time range to flag target hosts with utilization breaches.
* '''Proposed Index:'''
{{{
#!sql
CREATE INDEX idx_ch_timestamp_comp ON computer_history (timestamp, computer_id);
}}}

==== Execution Plan Analysis ====
* '''Before Index Creation:'''
  * Plan Output: `Seq Scan on computer_history ch` (Rows Removed by Filter: ~96,671) causing an expensive down-stream HashAggregate step across historical partitions.
  * Execution Time: 61.481 ms
[[Image(ss3_before.png)]]

* '''After Index Creation:'''
  * Plan Output: `Bitmap Index Scan using idx_ch_timestamp_comp` feeding a `Bitmap Heap Scan on computer_history ch`.
  * Execution Time: 3.664 ms
[[Image(ss3_after.png)]]

* '''Index Usage Verification:''' Yes, successfully achieved an Index path, eliminating the need to parse raw heap blocks.
* '''Conclusion:''' Performance scaled up by over '''94%''' (61.5 ms → 3.7 ms), preventing telemetry logging pipelines from bottlenecking.

---

=== Report 6: Sysmon Event Anomaly Detection (Complex CTE) ===
* '''Query Description:''' Deep analytical CTE query calculating overall infrastructural averages to isolate anomalous logging events 1.5x above baseline values using `CROSS JOIN` evaluations.
* '''Proposed Index:'''
{{{
#!sql
CREATE INDEX idx_se_timestamp_comp ON sysmon_events (timestamp, computer_id);
}}}

==== Execution Plan Analysis ====
* '''Before Index Creation:'''
  * Plan Output: `Seq Scan on sysmon_events se` (Rows Removed by Filter: ~96,619) across the CTE sub-trees to calculate environmental averages.
  * Execution Time: 240.350 ms
[[Image(ss6_before.png)]]

* '''After Index Creation:'''
  * Plan Output: `Index Only Scan using idx_se_timestamp_comp on sysmon_events se` (Heap Fetches: 0 — served entirely from the index).
  * Execution Time: 1.403 ms
[[Image(ss6_after.png)]]

* '''Index Usage Verification:''' Yes — achieved an `Index Only Scan` (Heap Fetches: 0), the optimal access path.
* '''Conclusion:''' Execution times dropped by over '''99%''' (240.4 ms → 1.4 ms), allowing heavy statistical parsing to complete efficiently.

---
== Security Measures ==

=== Application-Level Security ===
To secure database interactions within the application stack, the following measures have been programmatically enforced:
 * '''Prevention of SQL Injection (SQLi):''' All queries use parameterized statements (sqlite3 placeholders `?`) in the Flask backend, strictly separating SQL code from parameters. User-supplied values are never concatenated into SQL strings. (The planned PostgreSQL migration adds the SQLAlchemy layer.)
 * '''Prevention of Un-authorized Access:''' Implementation of custom decorators `@require_user()` and `@require_tenant_admin()` to intercept endpoints and force strict JWT validation before any data access.

=== Database-Level Security ===
The database-side protections include:
 * '''Prevention of SQL Injection in Dynamic Queries:''' Avoiding manual string formatting (f-strings or `%s` concatenation) with user input. All dynamically evaluated filtering explicitly passes parameter tuples.
 * '''Prevention of Un-authorized Access to Data:''' Multi-tenant structural architecture where every row manipulation isolates and constrains queries via a verified `tenant_id` (and, for agents, a valid `X-Env-Token`).

---

== Other Developments ==

=== JWT автентикација и авторизација ===
Системот користи JWT (JSON Web Token) за автентикација на корисниците по успешна Google најава.

Процесот се состои од следните чекори:
 1. Корисникот се најавува преку Google OAuth.
 2. Серверот го верификува Google токенот.
 3. Доколку најавата е успешна, серверот креира JWT токен кој содржи: `user_id`, `email`, `role`, `tenant_id`.
 4. JWT токенот се зачувува во HttpOnly cookie со име `session`.
 5. При секое наредно барање прелистувачот автоматски го испраќа cookie-то.
 6. Серверот го верификува JWT токенот и ги чита корисничките информации.

'''Креирање на JWT токен:'''
{{{
#!python
def make_jwt(payload: dict, minutes=60 * 24):
    exp = datetime.utcnow() + timedelta(minutes=minutes)
    data = {**payload, "iss": JWT_ISSUER, "exp": exp}
    return jwt.encode(data, JWT_SECRET, algorithm="HS256")
}}}

'''Проверка на JWT токен:'''
{{{
#!python
def read_jwt(token: str):
    return jwt.decode(
        token,
        JWT_SECRET,
        algorithms=["HS256"],
        issuer=JWT_ISSUER
    )
}}}

Пристапот до заштитените API рути е овозможен преку декораторите `@require_user` и `@require_tenant_admin`.
{{{
#!python
@require_user()
def api_me():
    ...
}}}

=== CORS конфигурација ===
Бидејќи frontend апликацијата и Flask серверот работат на различни адреси, потребно е овозможување на Cross-Origin Resource Sharing (CORS).

Во системот е конфигурирана листа на дозволени домени:
{{{
#!python
DEFAULT_ORIGINS = [
    "http://localhost:5173",
    "http://127.0.0.1:5173",
]
}}}

Конфигурацијата се извршува преку Flask-CORS:
{{{
#!python
CORS(
    app,
    supports_credentials=True,
    origins=ALLOWED_ORIGINS,
    allow_headers=[
        "Content-Type",
        "X-Admin-Session",
        "X-Env",
        "X-Env-Token",
    ],
    methods=["GET", "POST", "OPTIONS"],
)
}}}
 * Се дозволуваат барања само од доверливи frontend адреси.
 * Се дозволува испраќање на JWT cookie преку `supports_credentials=True`.
 * Се ограничуваат HTTP методите на GET, POST и OPTIONS.
 * Се контролира кои HTTP заглавија може да се испраќаат кон серверот.

=== Безбедносен модел ===
Системот користи повеќеслојна безбедност:
 * Google OAuth за верификација на идентитетот.
 * JWT токени за одржување на корисничка сесија.
 * HttpOnly cookies за заштита од JavaScript пристап до токените.
 * CORS политика за ограничување на дозволените клиентски апликации.
 * Tenant изолација преку `tenant_id`.
 * Посебни environment токени (`X-Env-Token`) за комуникација помеѓу агентите и серверот.