pawlaszc/Lighthouse-ETSI-CDR-Text2SQL

Lighthouse ETSI CDR Text-to-SQL (V4)

A compact text-to-SQL model that turns questions in German or English about call detail records (CDRs) into SQLite queries. It is the query model of Lighthouse, a cell site analysis plugin for the forensic tool FQLite, and was fine-tuned for the database schema that Lighthouse creates when it imports ETSI TS 102 657 retained-data exports.

The model runs locally with llama.cpp on ordinary hardware, so case data do not have to leave the examiner's computer.

Built with Llama. This model is a fine-tuned derivative of Llama 3.2 3B Instruct and is distributed under the Llama 3.2 Community License.

Files

File	Quantisation	Size	Use
forensic-sqlite-etsi-cdr-v4-llama-3.2-3b-Q8_0.gguf	8-bit	3.4 GB	Default in Lighthouse; no measurable loss against full precision
forensic-sqlite-etsi-cdr-v4-llama-3.2-3b-Q4_K_M.gguf	4-bit	2.0 GB	Computers with little memory; somewhat lower accuracy

Intended use

• For: exploratory questions about one case database created by the Lighthouse ETSI import, asked by an investigator who checks the generated SQL and the returned records.
• Not for: producing findings without review; databases with a different schema (use the general forensic SQLite model pawlaszc/DigitalForensicsText2SQLite instead); statements about where a device was. Recurring standard questions are better answered with Lighthouse's fixed, reviewed analysis presets.

The easiest way to use the model is inside FQLite/Lighthouse, which builds the prompt, routes questions to presets, checks and repairs the generated SQL, and always shows the executed query next to the result.

Prompt format

The model was trained on a plain completion prompt (no chat template):

Generate a valid SQLite query for this forensic database request.

Database Schema:
{context}

Request: {question}

SQLite Query:

{context} is the annotated schema of the import tables plus an automatically generated description of the loaded case: time range and calendar days, the values that occur in categorical columns, hints on time-of-day comparisons, and four read-only helper views (paired call legs, paired SMS legs, partners per number, gaps between records). Lighthouse generates this context with the same code that produced the training data (fqlite.rag.EtsiContext); without it, accuracy drops sharply.

Recommended decoding, as used in Lighthouse and in the evaluation: greedy (temperature 0), no repetition penalty, stop at the first ;, context window 4,096 tokens.

from llama_cpp import Llama
llm = Llama(model_path="forensic-sqlite-etsi-cdr-v4-llama-3.2-3b-Q8_0.gguf", n_ctx=4096, verbose=False)
prompt = ("Generate a valid SQLite query for this forensic database request.\n\n"
          f"Database Schema:\n{context}\n\nRequest: Which IMSI belongs to number 491700000001?\n\nSQLite Query:\n")
out = llm(prompt, max_tokens=256, temperature=0.0, repeat_penalty=1.0, stop=[";"])
sql = out["choices"][0]["text"].strip() + ";"

All timestamps in the Lighthouse database are stored in UTC; clock times in questions are compared with UTC values.

Training

Two stages of LoRA fine-tuning on meta-llama/Llama-3.2-3B-Instruct (r = 16, α = 32, dropout 0.05; completion-only loss):

1. Stage 1 – general forensic SQLite text-to-SQL over the databases of 191 mobile applications (pawlaszc/mobile-forensics-sql), resulting in pawlaszc/DigitalForensicsText2SQLite.
2. Stage 2 – continued training on the Lighthouse ETSI CDR schema: a verified core set, targeted skill examples, paraphrases, and 744 generated basic questions (identifier mappings, calls/SMS/data with and without direction, cells in several notations, first/last records, time windows), all bilingual; plus 200 replayed Stage 1 examples against forgetting and data augmentation. 2,704 training instances, 2 epochs, learning rate 1e-5; checkpoint selected by execution accuracy on a held-out validation split.

All training data are synthetic and published in the Lighthouse repository. No real call detail records were used.

Evaluation

Result-set accuracy on frozen test sets over one synthetic, ETSI-conformant case (a query counts as correct only if it returns exactly the reference result), measured on the complete Lighthouse pipeline with Q8_0. V3 is the previous version.

Test set	Questions	V3	V4
Hold-out D – basic questions, independent	144	47.9 %	87.5 %
Hold-out A – medium/hard	112	71.4 %	76.8 %
Hold-out B – medium/hard, unseen phrasings (100 well-posed instances)	100	64.0 %	63.0 %

German and English versions of a question are counted separately. Hold-out D shares its question families with the V4 training data (with different wording, parameters and reference queries), so it measures whether the model has learned these basic concepts rather than how it copes with freely phrased questions. The test sets, checksums and evaluation scripts are in the Lighthouse repository; the full evaluation, including comparisons with untrained local models and a large cloud-hosted model and an error analysis, is described in the accompanying article.

Limitations

• A query that runs is not necessarily correct. Most remaining errors are plausible-looking: the query returns records, but answers a slightly different question. Typical cases are a reversed direction (outgoing instead of incoming calls, sender instead of recipient, especially with passive phrasings such as "When was X called?"), MIN instead of MAX, and vague questions interpreted differently from the analyst's intent.
• One schema only. The model has been trained and tested only on databases produced by the Lighthouse ETSI import, and only on synthetic data. Real exports may contain field variants that were not covered.
• Wording matters. Accuracy is clearly lower for complex questions in unseen phrasings, and German and English versions of the same question are not always answered alike.
• No location findings. Cell positions in the database are operator-reported coordinates of the serving cell; they do not establish where a device was.

Always check an answer against the displayed SQL and the underlying records before relying on it.

Citation

If you use this model, please cite the accompanying article (submitted to Science & Justice; details will be added on publication):

D. Pawlaszczyk, D. Labudde, C. Hummert, R. Bodach, P. Engler, J. Kolouch, M. Spranger: AI-Assisted Cell Site Analysis: An Open Source Solution for Forensic Investigation of Call Detail Records.

Contact

Dirk Pawlaszczyk, Faculty of Computer Sciences, Hochschule Mittweida, Germany – pawlaszc@hs-mittweida.de