= Other Topics =

== SQL Performance ==
Documented performance analysis for complex analytical queries executed in PostgreSQL. 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 mapped inside a time-bounded window via CTE expressions to detect process-level traffic volume.
* '''Proposed Indexes:'''
{{{
#!sql
CREATE INDEX idx_nc_timestamp_comp ON network_connections (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 nc` (The engine is forced to perform an expansive sequential evaluation across log records).
  * Execution Time: 18.383 ms
[[Image(ss1_before.png)]]

* '''After Index Creation (Optimized Time-Window Filter):'''
  * Plan Output: `Bitmap Index Scan using idx_nc_timestamp_comp on network_connections nc`.
  * Execution Time: 3.271 ms
[[Image(ss1_after.2.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 '''82%''', 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` (Forced sequential full table scan table reading).
  * Execution Time: 5.521 ms
[[Image(ss2_before.png)]]

* '''After Index Creation (Optimized Time-Window Filter):'''
  * Plan Output: `Bitmap Index Scan using idx_sa_timestamp_comp on security_alerts sa`.
  * Execution Time: 2.853 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 '''48%''' for real-time operation filters, successfully validating index utilization under optimal predicate selectivity conditions.

---

=== Report 3: Resource Hotspots (CPU/RAM Overload) ===
* '''Query Description:''' Evaluates structural system telemetry logs 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` causing an expensive down-stream HashAggregate step across historical partitions.
  * Execution Time: 6.559 ms
[[Image(ss3_before.png)]]

* '''After Index Creation:'''
  * Plan Output: `Bitmap Index Scan using idx_ch_timestamp_comp on computer_history ch`.
  * Execution Time: 1.626 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 '''75%''', 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: Multi-pass sequential loops across table sub-trees to calculate environmental averages.
  * Execution Time: 53.382 ms
[[Image(ss6_before.png)]]

* '''After Index Creation:'''
  * Plan Output: Evaluated via discrete index conditions (`Index Searches: 2`) implemented natively across evaluation scopes of the CTE.
  * Execution Time: 32.772 ms
[[Image(ss6_after.png)]]

* '''Index Usage Verification:''' Yes, the index successfully injected directly inside the localized lookup nodes.
* '''Conclusion:''' Execution times dropped by over '''38%''', 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):''' Introduction of parameterized queries and ORM abstractions in Flask backend layer to strictly split code and parameters. String concats inside query definitions are structurally banned.
 * '''Prevention of Un-authorized Access:''' Implementation of custom decorators `@require_user()` and `@require_tenant_admin()` to intercept endpoints and force strict validation.

=== Database-Level Security ===
The specific architectural database configurations applied directly within PostgreSQL include:
 * '''Prevention of SQL Injection in Dynamic Queries:''' Avoiding the manual execution of raw string formatting (f-strings or %s concatenation). 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 constraints queries via a verified `tenant_id`.

---

== 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`) за комуникација помеѓу агентите и серверот.