= 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).
[[Image(ss1_before.png)]]

* '''After Index Creation:'''
  * Plan Output: `Index Scan using idx_nc_timestamp_comp on network_connections nc`.
[[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 gains exceeded '''93.3%''', drastically lowering volatile block read latency.

---

=== 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).
[[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`.
  * ''Note: Initial disk I/O cold-reads are cached instantly upon sequential execution runs, dropping execution time to sub-millisecond levels.''
[[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 '''91.8%''' 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.
[[Image(ss3_before.png)]]

* '''After Index Creation:'''
  * Plan Output: `Index Scan using idx_ch_timestamp_comp`.
[[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 '''92%''', 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.
[[Image(ss6_before.png)]]

* '''After Index Creation:'''
  * Plan Output: `Index Scan using idx_se_timestamp_comp` implemented natively across both separate evaluation scopes of the CTE.
[[Image(ss6_after.png)]]

* '''Index Usage Verification:''' Yes, the index successfully injected directly inside the localized cross-join nodes.
* '''Conclusion:''' Execution times dropped by over '''89%''', 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 dynamic database interactions are implemented using Object-Relational Mapping (ORM) safe frameworks or parameterized prepared statements via native database drivers. String concats inside query definitions are structurally banned.
 * '''Authorization and Data Isolation:''' Multi-tenant isolation is programmatically guaranteed at the API layer. Every request session decrypts tokens verifying user scope, ensuring an authenticated user cannot view metrics from an external `tenant_id`.
 * '''Input Validation:''' Strict input sanitization rules drop unrecognized characters or illegal escape patterns before the values reach execution context.

=== Database-Level Security ===
The specific architectural database configurations applied directly within PostgreSQL include:
 * '''Principle of Least Privilege (PoLP):''' The live web application authenticates using a dedicated runtime role (`app_runner`) restricted exclusively to DML operations (`SELECT`, `INSERT`, `UPDATE`). Destructive or structural privileges like `DROP`, `ALTER`, or schema catalog edits are blocked.
 * '''Handling Dynamic Queries Safely:''' Internal procedures or complex triggers generating dynamic SQL paths utilize native safe encapsulation routines (`quote_ident()` and `quote_literal()`) to protect executing buffers from raw injections.
 * '''Network and Transport Security:''' Database sockets are bound strictly to private loopback interfaces (`127.0.0.1`) and secure VPC spaces, completely shielding administrative ports from external public networks.