A portfolio case study in security-event modeling, database integrity, event streaming, ETL, and operational reporting.
This project models a compact SIEM data platform for security events such as IDS alerts, firewall activity, failed SSH attempts, SQL-injection attempts, and malware signals. The public repository is an evidence-rich showcase: it explains the system design, integrity decisions, data movement, and reporting surface while keeping the complete implementation private.
The system connects a relational security-event model with an event-aware processing layer, a document-oriented reporting model, and a dashboard surface. The design emphasizes traceable data movement and explainable controls rather than a collection of disconnected database features.
| Layer | Design evidence |
|---|---|
| Event model | PostgreSQL schema with constraints, indexes, INET, JSONB, pgcrypto, and pg_trgm. |
| Integrity | PL/pgSQL functions, triggers, procedures, materialized views, and chained audit hashes. |
| Processing | Python ETL with batch synchronization and a LISTEN/NOTIFY streaming pattern. |
| Reporting model | MongoDB collections and aggregations shaped for dashboard reads. |
| Presentation | Streamlit and Plotly views for operational security-event analysis. |
The primary path is detection input → PostgreSQL event model → Python processing → MongoDB reporting model → dashboard. Integrity logic remains close to the source data, while the reporting layer is optimized for analysis and presentation.
| Artifact | What it lets a reader verify |
|---|---|
screenshots/architecture-transparent.png |
End-to-end data flow and system boundaries. |
screenshots/mcd.png |
Core data-model relationships. |
screenshots/dashboard_top.png |
Reporting layout and operational overview. |
screenshots/dashboard_bottom.png |
Lower-level reporting detail. |
code-snippets/alert-audit-pattern.sql |
A small illustrative integrity pattern without exposing the full implementation. |
This case study demonstrates that I can model security context in a relational system, preserve traceability through integrity controls, move events between processing and reporting stores, and explain the resulting evidence through a usable dashboard surface.
The complete SQL, Python, JavaScript, container setup, dashboard source, environment files, and generated archives are not published. This repository deliberately shares architecture, selected snippets, diagrams, screenshots, and engineering decisions without exposing credentials, private infrastructure, or a deployable copy of the system.
- Security context becomes more useful when the schema, integrity rules, and reporting model are designed together.
JSONBsupports variable alert payloads, but relational keys remain important for reliable reporting.- Audit chains and triggers are valuable when their behavior remains simple enough to explain and revalidate.
- A document-oriented reporting model can complement a normalized source model when the data movement is explicit.
- A database showcase is stronger when it demonstrates both schema depth and operational interpretation.
I am Yassir Zahidi, a security engineering student focused on detection, forensics, controlled validation, databases, and practical system design.
- Portfolio: https://y-zahidi.github.io
- GitHub: https://github.com/y-zahidi
Portfolio snapshot only. See LICENSE. The full implementation remains private.