README.md

---
license: apache-2.0
language:
- en
base_model:
- unsloth/Llama-3.2-3B-Instruct
tags:
- digital
- forensics
- sqlite

datasets:
- pawlaszc/mobile-forensics-sql
metrics:
- accuracy
model-index:
- name: ForensicSQL-Llama-3.2-3B
  results:
  - task:
      type: text-to-sql
      name: Text-to-SQL Generation
    dataset:
      type: mobile-forensics
      name: Mobile Forensics SQL Dataset
    metrics:
    - type: accuracy
      value: 79.0
      name: Overall Accuracy
    - type: accuracy
      value: 94.3
      name: Easy Queries Accuracy
    - type: accuracy
      value: 80.6
      name: Medium Queries Accuracy
    - type: accuracy
      value: 61.8
      name: Hard Queries Accuracy

      
---

# ForensicSQL-Llama-3.2-3B

## Model Description

**ForensicSQL** is a fine-tuned Llama 3.2 3B model specialised for generating SQLite queries for mobile forensics databases. The model converts natural language forensic investigation requests into executable SQL queries across various mobile app databases (WhatsApp, Signal, iOS Health, Android SMS, etc.).

This model was developed as part of a research project investigating LLM fine-tuning for forensic database analysis.

## Model Details

- **Base Model:** Llama 3.2 3B Instruct
- **Fine-tuning Method:** LoRA (Low-Rank Adaptation)
- **Training Dataset:** 768 forensic SQL examples across 148 categories
- **Training Framework:** Hugging Face Transformers + PEFT
- **Model Size:** 
  - Full (FP16): ~6 GB
  - GGUF Q4_K_M: ~2.3 GB
  - GGUF Q5_K_M: ~2.8 GB
  - GGUF Q8_0: ~3.8 GB

## Performance

### Overall Results
- **Overall Accuracy:** 79.0%
- **Schema Generation Errors:** 0% (completely eliminated)
- **Executable Queries:** 79%

### Breakdown by Difficulty
| Difficulty             | Accuracy | Examples |
|------------------------|----------|----------|
| Easy (single-table)    | 94.3%    | 33/35    |
| Medium (simple joins)  | 80.6%    | 25/31    |
| Hard (complex queries) | 61.8%    | 21/34    |

### Error Analysis
| Error Type           | Percentage | Description                        |
|----------------------|------------|------------------------------------|
| Column Hallucination |        18% | References non-existent columns    |
| Syntax Errors        |         3% | Invalid SQL syntax                 |
| Schema Generation    |         0% | Eliminated through proper training |

## Intended Use

### Primary Use Cases
- Mobile forensics investigations
- Automated SQL query generation for forensic databases
- Educational tool for learning forensic database analysis
- Research in text-to-SQL for specialized domains

### Out-of-Scope Use
- General-purpose SQL generation (use specialized models)
- Production systems requiring >95% accuracy
- Real-time critical forensic decisions without human review

## How to Use

### Quick Start (Transformers)

```python
from transformers import AutoModelForCausalLM, AutoTokenizer
import torch

# Load model and tokenizer
model_name = "pawlaszc/ForensicSQL-Llama-3.2-3B"
tokenizer = AutoTokenizer.from_pretrained(model_name)
model = AutoModelForCausalLM.from_pretrained(
    model_name,
    torch_dtype=torch.float16,
    device_map="auto"
)

# Prepare input
schema = """
CREATE TABLE messages (
    _id INTEGER PRIMARY KEY,
    address TEXT,
    body TEXT,
    date INTEGER,
    read INTEGER
);
"""

request = "Find all unread messages from yesterday"

prompt = f"""Generate a valid SQLite query for this forensic database request.

Database Schema:
{schema}

Request: {request}

SQLite Query:
"""

# Generate SQL
inputs = tokenizer(prompt, return_tensors="pt", truncation=True, max_length=2048)
inputs = {k: v.to(model.device) for k, v in inputs.items()}

with torch.no_grad():
    outputs = model.generate(
        **inputs,
        max_new_tokens=200,
        do_sample=False,
    )

# Decode only the generated part
input_length = inputs['input_ids'].shape[1]
generated_tokens = outputs[0][input_length:]
sql = tokenizer.decode(generated_tokens, skip_special_tokens=True)

print(sql.strip())
# Output: SELECT * FROM messages WHERE read = 0 AND date > ...
```

### Using GGUF Files (llama.cpp / Ollama)

**With llama.cpp:**
```bash
# Download GGUF file
wget https://huggingface.co/pawlaszc/ForensicSQL-Llama-3.2-3B/resolve/main/forensic-sql-q4_k_m.gguf

# Run inference
./llama-cli -m forensic-sql-q4_k_m.gguf -p "Generate SQL..."
```

**With Ollama:**
```bash
# Create Modelfile
FROM ./forensic-sql-q4_k_m.gguf
PARAMETER temperature 0
PARAMETER top_p 0.9

# Import
ollama create forensic-sql -f Modelfile

# Use
ollama run forensic-sql "Schema: ...\nRequest: Find messages\nSQL:"
```

### Python Helper Class

```python
class ForensicSQLGenerator:
    def __init__(self, model_name="pawalaszc/ForensicSQL-Llama-3.2-3B"):
        from transformers import AutoModelForCausalLM, AutoTokenizer
        import torch
        
        self.tokenizer = AutoTokenizer.from_pretrained(model_name)
        self.model = AutoModelForCausalLM.from_pretrained(
            model_name,
            torch_dtype=torch.float16,
            device_map="auto"
        )
        self.model.eval()
    
    def generate_sql(self, schema: str, request: str) -> str:
        prompt = f"""Generate a valid SQLite query for this forensic database request.

Database Schema:
{schema}

Request: {request}

SQLite Query:
"""
        inputs = self.tokenizer(
            prompt, 
            return_tensors="pt", 
            truncation=True, 
            max_length=2048
        )
        inputs = {k: v.to(self.model.device) for k, v in inputs.items()}
        
        input_length = inputs['input_ids'].shape[1]
        
        with torch.no_grad():
            outputs = self.model.generate(
                **inputs,
                max_new_tokens=200,
                do_sample=False,
            )
        
        generated_tokens = outputs[0][input_length:]
        sql = self.tokenizer.decode(generated_tokens, skip_special_tokens=True)
        
        return sql.strip().split("\n")[0].strip().rstrip(";") + ";"

# Usage
generator = ForensicSQLGenerator()
sql = generator.generate_sql(schema, request)
```

## Training Details

### Training Data
- **Size:** 768 examples (original) → 2,304 examples (with augmentation)
- **Categories:** 148 forensic database categories
- **Sources:** WhatsApp, Signal, iMessage, SMS, iOS apps, Android apps
- **Augmentation:** 3x paraphrase augmentation per example

### Training Procedure
- **Method:** LoRA fine-tuning
- **LoRA Rank:** 16
- **LoRA Alpha:** 32
- **Target Modules:** q_proj, k_proj, v_proj, o_proj
- **Epochs:** 5
- **Learning Rate:** 2e-5
- **Batch Size:** 1 (gradient accumulation: 4)
- **Max Sequence Length:** 2048 (critical for preventing truncation)
- **Optimizer:** AdamW
- **Scheduler:** Cosine with warmup (10%)
- **Hardware:** Apple M2 (MPS)
- **Training Time:** ~3.5 hours

### Key Training Insights

**Critical Discovery: Sequence Length Matters**

Initial training attempts with `max_seq_length=512` resulted in only 50% accuracy because 92% of training examples were truncated. The model learned to generate schema definitions (CREATE TABLE) instead of queries.

Increasing to `max_seq_length=2048` eliminated truncation and improved accuracy from 50% to 79% (+29pp).

**Lesson:** Data preprocessing and proper sequence length configuration are critical for fine-tuning success.

## Limitations

### Known Issues
1. **Column Hallucination (18%):** Model sometimes references non-existent columns
2. **Complex Joins:** Performance drops on multi-table queries requiring JOINs (62%)
3. **Schema Understanding:** Limited understanding of foreign key relationships

### When to Use Human Review
- Complex multi-table queries
- Critical forensic investigations
- Queries involving data deletion or modification
- When accuracy >95% is required

## Evaluation

### Test Set
- **Size:** 100 queries (random sample from held-out data)
- **Seed:** 42 (reproducible)
- **Evaluation Metric:** Exact match (query results must match expected results)

### Ablation Studies

| Configuration                  | Accuracy | Notes                |
|--------------------------------|----------|----------------------|
| Zero-shot baseline             | 45%      | No fine-tuning       |
| Final training (max_len=2048)  | 79%      | No truncation        |


## Citation

If you use this model in your research, please cite:

```bibtex
@misc{dirk_pawlaszczyk_2026,
	author       = { Dirk Pawlaszczyk and Ronny Bodach and Christian Hummert and Philipp Engler},
	title        = { DigitalForensicsText2SQLite},
	year         = 2026,
	url          = { https://huggingface.co/pawlaszc/DigitalForensicsText2SQLite },
	doi          = { 10.57967/hf/7675 },
	publisher    = { Hugging Face }
}
```

## Model Card Authors

Dirk Pawlaszczyk

## Model Card Contact

For questions or issues, please open an issue on the (https://github.com/pawlaszczyk/fqlite) or contact pawlaszc@hs-mittweida.de.

## License

This model is released under the Apache 2.0 License, following the base Llama 3.2 license.

## Acknowledgments

- Base model: Meta's Llama 3.2 3B Instruct
- Training framework: Hugging Face Transformers, PEFT
- Dataset creation: Custom forensic database schemas
- Inspiration: Text-to-SQL research community

## Additional Resources

- **Dataset:** pawlaszc/mobile-forensics-sql
- **GitHub:** https://github.com/pawlaszczyk/fqlite
- **Paper:** [Link when published]

---

**Disclaimer:** This model is intended for research and educational purposes. Always validate generated SQL queries before execution in production forensic investigations. The model may produce incorrect queries that could lead to data loss or incorrect conclusions if used without proper review.