AgTech

Deterministic ETL Safety Gates: Restricting Agrochemicals via Embedded SQLite FTS5

October 15, 2026 · 12 min read

An LLM should never decide whether a farmer sprays Chlorpyrifos or Neem oil. That decision must be deterministic, auditable, and grounded in verified agronomic data. This post describes how we built the ETL safety gate — a hard-coded threshold system backed by embedded SQLite FTS5 — that controls agrochemical prescriptions entirely on-device, with zero LLM involvement in dosage decisions.

Why LLMs Cannot Prescribe Chemicals

Language models hallucinate. A fine-tuned Qwen 1.5B will confidently recommend "Imidacloprid 17.8% SL at 0.5 ml/L" for a pest that actually requires 0.3 ml/L, or suggest a chemical that's banned under India's Insecticide Act. In agriculture, a wrong dosage doesn't return a 400 error — it contaminates soil, kills pollinators, or poisons the farmer.

Our architecture enforces a strict separation: the LLM handles natural language understanding (parsing the farmer's Hindi/Hinglish description into a structured pest + crop + stage tuple), and the deterministic pipeline handles prescriptions (looking up verified ICAR advisories, checking thresholds, and returning exact dosages from a locked registry).

Economic Threshold Levels: The First Gate

Every crop-pest combination has an Economic Threshold Level (ETL) — the pest population density at which chemical intervention becomes economically justified. Below ETL, biological controls are sufficient and chemical spraying is wasteful and harmful.

Below ETL → Bio-Only Lock

Chemical prescriptions are completely disabled. The system returns only biological alternatives: Trichoderma viride, Beauveria bassiana, Neem oil, Pseudomonas fluorescens. No override possible.

Above ETL → Chemical Authorized

Chemical pesticides unlocked, but still served from the verified dosage registry. The LLM never generates dosage values — it selects from a pre-validated lookup table.

The ETL table is hard-coded in the SQLite database, sourced directly from ICAR (Indian Council of Agricultural Research) and state agricultural university bulletins. Example thresholds:

-- ETL threshold table (excerpt)

INSERT INTO etl_thresholds VALUES

('mustard', 'aphid', 50, 'per_plant',

'50 aphids per plant at flowering stage'),

('rice', 'stem_borer', 10, 'percent_dead_hearts',

'10% dead hearts at tillering stage'),

('cotton', 'bollworm', 1, 'per_plant',

'1 larva per plant at square formation');

SQLite FTS5 for Advisory Retrieval

Android's bundled SQLite does not include FTS5. The fts5 extension is compiled out of the system library. We solved this by bundling the SQLite 3.45 amalgamation and compiling it ourselves via NDK/CMake with -DSQLITE_ENABLE_FTS5=1.

The FTS5 index stores ICAR pest management advisories — each advisory is a structured document containing crop, pest, stage, biological interventions, chemical interventions, dosages, and spray window constraints. Retrieval uses BM25 ranking:

-- BM25-ranked advisory retrieval

SELECT *, bm25(advisories_fts) as rank

FROM advisories_fts

WHERE advisories_fts

MATCH 'mustard aphid flowering'

ORDER BY rank

LIMIT 3;

Zero network calls. Zero external dependencies. The entire advisory database (50+ crop-pest combinations, 200+ advisories) fits in a 2.5 MB SQLite file shipped with the APK.

Hinglish Synonym Expansion

Indian farmers describe pests in a mix of Hindi, regional dialects, and transliterated English. A farmer might say "माहू" (Hindi for aphid), "mahu" (transliterated), or "tela" (colloquial). The FTS5 query for "aphid" alone misses all of these.

We maintain a synonym expansion table that maps colloquial terms to their canonical FTS5 tokens:

-- Synonym expansion

INSERT INTO pest_synonyms VALUES

('माहू', 'aphid'),

('mahu', 'aphid'),

('tela', 'aphid'),

('चेपा', 'aphid'),

('गेहूं का माहू', 'wheat aphid'),

('सुंडी', 'bollworm'),

('sundi', 'bollworm'),

('इल्ली', 'caterpillar');

The pipeline: STT output (Hindi) → Devanagari↔Latin transliteration → synonym lookup → expanded FTS5 query. This achieves 100% recall on our 30-query benchmark — every pest description, regardless of dialect or transliteration variant, retrieves the correct advisory.

The Dosage Registry: Chemical Values Are Never Generated

This is the most important safety constraint in the system. Chemical dosage values are never generated by the LLM. They are retrieved from a locked registry table:

-- Verified dosage registry (read-only)

SELECT chemical_name, formulation,

dosage_per_litre, dosage_per_acre,

phi_days, safety_notes

FROM dosage_registry

WHERE crop = 'mustard'

AND pest = 'aphid'

AND banned = 0;

The registry includes Pre-Harvest Interval (PHI) days — the minimum number of days between last spray and harvest. It also flags chemicals banned under India's Insecticide Act 1968 and its amendments. A banned chemical can never appear in output, regardless of what the LLM might want to suggest.

Bio-First Spray Window Constraints

Biological controls have physics-aware constraints that chemical pesticides don't. Beauveria bassiana (a fungal biocontrol) loses 90% efficacy if sprayed in direct sunlight — UV radiation kills the spores. Trichoderma needs soil moisture above a threshold to colonize effectively.

When the system prescribes a biological control, it also prescribes a spray window:

Beauveria bassiana: Apply after 4 PM or on overcast days. Morning application under direct sun reduces efficacy by 90%+.

Neem oil (Azadirachtin): Apply in evening. Acts as antifeedant and IGR — effective at any temperature but degrades in UV.

Trichoderma viride: Apply to moist soil. Ineffective on dry, sun-baked fields. Best during or after irrigation.

These constraints are encoded in the advisory records, not generated by the model. The output template interpolates them directly.

The Full Pipeline

# Step-by-step inference pipeline

1. Voice input → Shruti STT → Hindi text

2. LLM extracts (crop, pest, stage, severity)

3. Synonym expansion → canonical pest name

4. ETL lookup → {bio_only | chemical_authorized}

5. FTS5 query → top-3 ICAR advisories (BM25)

6. Dosage registry lookup → exact formulation + ml/L

7. Template render → Hindi prescription card

# LLM involved: step 2 only

# Everything else: deterministic SQL queries

Production Results

10/10

Scope gate accuracy

9/9

Search retrieval accuracy

Banned chemicals served

LLM-generated dosages

Key Takeaways

Never let an LLM generate safety-critical values. Dosages, chemical names, and banned substance lists must come from verified, read-only data stores. The LLM's job is natural language understanding — extracting structured fields from unstructured input. Everything downstream is deterministic SQL.

Bundle your own SQLite. Android's system SQLite lacks FTS5. Compiling the amalgamation via NDK/CMake with SQLITE_ENABLE_FTS5=1 takes 30 seconds and gives you full-text search with BM25 ranking on-device.

Synonym expansion is non-optional for multilingual search. FTS5 is English-centric. Hinglish, Devanagari, and regional dialect terms require explicit synonym tables. Without expansion, recall drops below 60%.

Hard-code your safety gates. ETL thresholds are not model hyperparameters — they're deterministic business rules from agricultural research institutions. They belong in SQL, not in prompts.