# Storage and Containment Module SQLite-based storage, containment, alerting, and LLM-analysis persistence for the **AI-Powered Honeypot for Malware Detection at the Network Edge** project. This module is the shared persistence layer used by the detection pipeline and the Admin-side services. Other components should call the repository functions instead of writing SQL directly. ## Current Scope The module is responsible for: - Registering every file captured by Cowrie with a unique `file_id` UUID. - Storing Hash, YARA, and AI malware-detection results. - Storing structured LLM threat-analysis results returned on the Admin side. - Maintaining a local table of known malicious SHA-256 hashes. - Quarantining files already classified as malicious. - Recording containment attempts and failures. - Creating and acknowledging security alerts. - Providing read functions for detections, LLM analyses, containment, alerts, known hashes, and dashboard summaries. - Enforcing shared validation rules and database relationships. ## Current Detection and Enrichment Flow ```text Cowrie captures a file | v Register file + metadata | v SHA-256 lookup | malicious +-----------------------------+ | unknown | v | YARA scan | | malicious | +-----------------------------+ | unknown | v | Supported static feature extraction | v AI malware model | benign -> store only | malicious ------------------+ | unknown/error -> store for review | +--> Containment + Alert | +--> Evidence sent to Admin | v LLM | v Structured JSON analysis | v llm_analysis ``` Important rules: 1. A Hash miss or YARA miss is **not** a benign verdict. 2. The LLM may enrich any file already detected as malicious by Hash, YARA, or AI. 3. The LLM is an enrichment/interpretation stage; it is not the primary malware verdict engine. 4. Containment is triggered by the malicious detection and does **not** wait for the LLM result. 5. `file_id` identifies the capture event; `detection_id` identifies the exact detection record that triggered an LLM analysis or containment action. ## Project Structure ```text storage_containment/ ├── __init__.py ├── alerts.py ├── config.py ├── database.py ├── demo.py ├── models.py ├── quarantine.py ├── repositories.py ├── requirements.txt ├── schema.sql ├── README.md ├── runtime/ # generated locally; ignored by Git │ ├── database/ │ ├── quarantine/ │ └── test_samples/ └── tests/ ├── __init__.py ├── test_detection_cases.py └── test_quarantine_alerts.py ``` ### File Responsibilities | File | Responsibility | |---|---| | `config.py` | Runtime, database, and quarantine paths. | | `database.py` | SQLite connections and schema initialization. | | `schema.sql` | Tables, constraints, indexes, and relationships. | | `models.py` | Shared constants and allowed values. | | `repositories.py` | Data-access and validation functions. | | `quarantine.py` | Malicious-file quarantine and containment records. | | `alerts.py` | Security-alert creation and acknowledgement. | | `demo.py` | Safe local demonstration of storage, detection, containment, and LLM persistence. | | `tests/` | Automated tests for detection, LLM storage, containment, and alerts. | ## Requirements - Python 3.12 or newer is recommended. - SQLite support is included with Python. - This module itself uses only Python standard-library packages. The Admin APIs may have their own dependencies, such as FastAPI, Uvicorn, and Requests; those are outside this module. ## Database Schema Schema version: **2.0.0** The database contains six application tables: ```text files | +-- detections | | | +-- llm_analysis | +-- containment | +-- alerts known_hashes ``` ### `files` One row represents one Cowrie capture event. Main fields: ```text file_id TEXT PRIMARY KEY (UUID) file_name TEXT file_path TEXT file_size INTEGER file_type TEXT sha256_hash TEXT source_ip TEXT capture_timestamp TEXT ``` A new UUID is generated for every capture event, even when the same SHA-256 value has been seen previously. This preserves per-capture traceability. ### `detections` Stores individual Hash, YARA, and AI results. ```text detection_id INTEGER PRIMARY KEY file_id TEXT -> files.file_id method hash | yara | ai classification benign | malicious | unknown | error confidence REAL 0.0-1.0 details_json JSON serialized as TEXT timestamp TEXT ``` A single file may have multiple detection records as it moves through the layered pipeline. ### `llm_analysis` Stores structured threat-enrichment results returned by the Admin-side LLM. ```text analysis_id INTEGER PRIMARY KEY file_id TEXT -> files.file_id detection_id INTEGER -> detections.detection_id malware_family TEXT confidence REAL 0.0-1.0 reasoning TEXT key_evidence JSON array serialized as TEXT recommendations JSON array serialized as TEXT limitations TEXT status pending | completed | failed llm_model TEXT created_at TEXT completed_at TEXT ``` `detection_id` must belong to the same `file_id`, and LLM analysis is accepted only for a detection whose classification is `malicious`. For completed analyses, the agreed LLM output contract is: ```json { "malware_family": "Backdoor", "confidence": 0.90, "reasoning": "...", "key_evidence": ["..."], "recommendations": ["..."], "limitations": "..." } ``` The six fields above are the LLM output. Storage/API metadata such as `file_id`, `detection_id`, `status`, timestamps, and `llm_model` are added by the system around that output. ### `containment` Stores each quarantine attempt. ```text containment_id file_id triggered_by_detection_id original_path quarantine_path status reason alert_generated timestamp error_message ``` Only detections classified as `malicious` can trigger quarantine. Containment does not depend on LLM completion. ### `alerts` Stores security alerts associated with files, detections, and optionally containment attempts. Supported alert types: ```text malware_detected quarantine_success quarantine_failed system_error ``` Supported severities: ```text low medium high critical ``` ### `known_hashes` Local reference table for known malicious SHA-256 values. ```text sha256_hash family_name source_name added_at ``` ## Public Repository Interface Import functions from the package when integrating from the project root: ```python from storage_containment.repositories import ( register_file, get_file_by_id, save_detection_result, get_detection_results, save_llm_analysis, get_llm_analysis, add_known_hash, find_known_hash, get_known_hashes, get_containment_records, get_alerts, get_dashboard_records, ) ``` ### Register a Captured File ```python result = register_file( { "file_name": "sample.exe", "file_path": "/path/to/sample.exe", "file_size": 4096, "file_type": "PE", "sha256_hash": "a" * 64, "source_ip": "192.168.1.20", "capture_timestamp": "2026-08-19T03:00:00+00:00", } ) ``` Success returns a new `file_id` UUID. ### Store a Detection Result ```python result = save_detection_result( { "file_id": file_id, "method": "yara", "classification": "malicious", "confidence": 1.0, "details": {"matched_rules": ["Example_Rule"]}, "timestamp": "2026-08-19T03:01:00+00:00", } ) ``` For a malicious result, the response includes: ```text requires_quarantine = True ``` ### Store a Completed LLM Analysis ```python result = save_llm_analysis( { "file_id": file_id, "detection_id": detection_id, "malware_family": "Backdoor", "confidence": 0.90, "reasoning": "The supplied static evidence is consistent with ...", "key_evidence": [ "Suspicious imported functions", "High malicious-model confidence", ], "recommendations": [ "Keep the file quarantined", "Review related network activity", ], "limitations": "Analysis is based on supplied evidence only.", "status": "completed", "llm_model": "model-name-or-version", "created_at": "2026-08-19T03:02:00+00:00", "completed_at": "2026-08-19T03:02:05+00:00", } ) ``` `key_evidence` and `recommendations` are validated as lists of strings, serialized to JSON text for SQLite, and restored to Python lists by `get_llm_analysis()`. ### Store Pending or Failed LLM State A pending record requires the relationship and creation metadata: ```python save_llm_analysis( { "file_id": file_id, "detection_id": detection_id, "status": "pending", "llm_model": "model-name-or-version", "created_at": "2026-08-19T03:02:00+00:00", } ) ``` A failed record can use `limitations` to record a short failure/limitation message. ### Read LLM Analyses ```python result = get_llm_analysis( file_id=file_id, status="completed", limit=100, ) ``` The returned `key_evidence` and `recommendations` values are Python lists. ### Known-Hash Operations ```python from storage_containment.repositories import add_known_hash, find_known_hash add_known_hash( { "sha256_hash": "b" * 64, "family_name": "ExampleFamily", "source_name": "local_reference", "added_at": "2026-08-19T03:00:00+00:00", } ) result = find_known_hash("b" * 64) ``` ## Containment Interface ```python from storage_containment.quarantine import quarantine_file result = quarantine_file( file_id=file_id, detection_id=detection_id, file_path="/path/to/sample.exe", ) ``` The function validates that: - the detection exists; - the detection belongs to the same captured file; - the classification is `malicious`; - an already quarantined capture is not moved again. It records success/failure and generates a corresponding alert. ## Alert Interface ```python from storage_containment.alerts import acknowledge_alert result = acknowledge_alert(alert_id) ``` ## Database Initialization From the project root: ```powershell python -c "from storage_containment.database import initialize_database; print(initialize_database())" ``` `schema.sql` uses `CREATE TABLE IF NOT EXISTS`. It initializes a new database but does **not** migrate an existing schema automatically. ### Schema v1 to v2 Note The old design used `behavior_predictions`. Schema v2 replaces that concept with `llm_analysis`. During development, use a fresh ignored runtime database after changing schemas. For any persistent/production database, use an explicit migration rather than deleting data. ## Running the Demo From the project root: ```powershell python -m storage_containment.demo ``` The demo uses a safe dummy file. It does not execute malware. ## Running Automated Tests From the project root: ```powershell python -m unittest discover -s storage_containment/tests -v ``` The current suite covers: - benign, malicious, unknown, and error detection outcomes; - confidence and classification validation; - LLM analysis after malicious Hash, YARA, and AI detections; - rejection of LLM analysis for benign detections; - `file_id` / `detection_id` relationship validation; - LLM JSON-list serialization and restoration; - pending/completed LLM states; - quarantine success and failure; - duplicate-quarantine prevention; - rejection of an unrelated `file_path` during quarantine; - alert generation and acknowledgement. ## Git Hygiene Generated runtime data must not be committed. `.gitignore` excludes: ```text __pycache__/ *.pyc .vscode/ runtime/database/ runtime/quarantine/ runtime/test_samples/ *.db *.db-shm *.db-wal ``` If any ignored runtime/cache files were already tracked by Git before the ignore rules were added, remove them from the Git index before committing. ## Integration Contract For the final system integration: 1. Register each capture once and preserve the returned `file_id`. 2. Store every Hash/YARA/AI result with the same `file_id`. 3. Preserve the returned `detection_id` for the exact malicious detection that triggers downstream actions. 4. Trigger containment directly from a malicious detection; do not wait for the LLM. 5. When LLM enrichment is performed, submit both `file_id` and `detection_id` with the six-field structured LLM output. 6. External APIs should call `save_llm_analysis()` / `get_llm_analysis()` rather than duplicating storage SQL where practical. 7. Keep `confidence` normalized to `0.0-1.0` across detection and LLM interfaces. 8. Perform final end-to-end validation only after the latest Edge, Admin API, LLM, dashboard, and storage versions are integrated. ## Status The storage/containment module is independently testable; the current suite contains 28 passing tests and includes the current LLM persistence contract. Admin API transport and the final cross-device LLM integration are handled by their respective project components and must be validated again after the team's latest integration changes are combined.