From Excel to Agents: Modernizing SDWIS the Right Way

Safe drinking water programs run on data, deadlines, and discipline. Many SDWIS processes still lean on spreadsheets, email, and heroic manual effort. That approach worked when volumes were smaller and rules were simpler. It strains under today’s reporting cadence, rule complexity, and public expectations. This article lays out a clear, technical path from spreadsheets to agentic AI—with each step adding capability without losing control.

I’ll anchor the discussion in SDWIS-style work: ingesting lab results, determining compliance, managing violations and enforcement, scheduling monitoring, and communicating with systems and the public. The goal is not hype; it’s a sequence you can implement, audit, and evolve.

The Landscape: Six Levels of Capability

#

Think of modernization as a staircase. Each step solves concrete problems; you can stop at any landing and still be better than when you started.

1. Spreadsheets (Excel-First)

#

Ad hoc tables, VLOOKUPs, pivot logic, email-driven workflows, manual data cleanup.

• Explanation: The system lives in spreadsheets. Every update is manual, logic is hidden in cells, and collaboration happens over email.
• Pros: Easy to start, accessible to non-technical staff, zero setup cost.
• Cons: Fragile, error-prone, no audit trail, logic hidden in formulas, cannot scale.

Spreadsheets get you moving fast, but eventually they become a thicket of hidden formulas and brittle workflows. At some point, you crave structure—rules written once, enforced everywhere.

2. Rules-Based (Declarative)

#

Business logic expressed as rules and SQL, not buried in imperative code. Deterministic, testable, and explainable.

• Explanation: Instead of hand-coding if/else conditions in software, you define what must hold true (e.g., “if MCL exceeds X, trigger alert”) and let the engine enforce it.
• Pros: Transparent, auditable, policy-driven, easier to govern.
• Cons: Rigid; complex scenarios require large rule sets; cannot adapt beyond encoded cases.

Rules bring order, but life rarely stays within tidy boundaries. Edge cases multiply, exceptions creep in, and soon you need models that can learn patterns you never explicitly wrote down.

3. Machine Learning (ML)

#

Trained models predict outcomes—likelihood of late sampling, risk of MCL violations—under human oversight.

• Explanation: Instead of fixed rules, you train models on historical data to recognize patterns and predict outcomes.
• Pros: Finds subtle correlations; improves over time; augments human decision-making.
• Cons: Requires quality data; predictions can be opaque; risk of bias; must remain under expert supervision.

With ML, systems no longer just execute rules; they generalize from history. Yet, as soon as messy, unstructured reality enters—scanned reports, handwritten notes, sensor images—ML alone falters. That’s where deep learning earns its place.

4. Deep Learning (DL)

#

Extracts structure from unstructured inputs—OCR/NLP for lab PDFs, image classification for field artifacts, entity resolution across messy sources.

• Explanation: Neural nets handle data types where rules or simple ML break down, such as free-form text, scanned reports, or sensor images.
• Pros: Unlocks hidden insights in unstructured data; reduces manual transcription and classification.
• Cons: High compute and data requirements; even less explainable than ML; harder to audit in regulated domains.

Deep learning unlocks the unstructured world, but it speaks in vectors and probabilities. Humans still need polished reports, clear summaries, and natural answers. Enter generative AI.

5. Generative AI (GenAI + RAG)

#

Systems that draft, summarize, and answer questions, grounded in your rules, guidance, and historical decisions via retrieval-augmented generation.

• Explanation: Rather than just predicting outcomes, models generate new artifacts—draft compliance reports, summarize inspection notes, or answer regulatory queries—while referencing your organization’s own documents.
• Pros: Accelerates writing, reporting, and communication; adapts to natural language; contextual grounding via RAG makes it practical.
• Cons: Still reactive; output quality depends on retrieval and prompt design; requires human review for compliance.

Generative AI feels like magic: words, reports, and explanations appear on demand. But it waits for you to ask. It doesn’t remember, plan, or adapt on its own. For that, you need systems that behave less like scribes and more like colleagues—agentic AI.

6. Agentic AI (Plan–Act–Adapt)

#

Goal-driven automations that plan steps, call tools, monitor progress, adapt to issues, and request approvals at the right gates.

• Explanation: You give a goal (“ensure quarterly compliance across all systems”). The agent breaks it into steps, executes them, checks progress, adjusts strategy, and loops you in only for approval.
• Pros: Proactive, adaptive, context-aware; reduces coordination overhead; closes the loop from intention to action.
• Cons: Complex to build; governance and safety mechanisms critical; requires trust, observability, and strong human-in-the-loop controls.

Programming Mindsets: Imperative → Declarative → Learned → Generative → Agentic

#

• Imperative (traditional software): You spell out how to do the work: if exceedance then open case. Good for precise control. Hard to keep consistent as rules proliferate.

• Declarative (functional/rules): You declare what must hold: “A Tier 2 public notice is required when condition X is true.” A rules engine (or SQL) evaluates facts against rule sets. Clear, testable, and auditable.

• Learned (ML/DL): You provide labeled examples; the model learns patterns you did not hand-code. Use for prediction (risk, prioritization) and extraction (OCR/NLP), not for statutory determinations.

• Generative: You supply context; the model drafts artifacts—summaries, letters, checklists. With RAG, the model cites the pages and sections it used.

• Agentic: You set a goal (“Ensure sampling and reporting are complete and timely for PWS X this quarter”). The system plans the steps, calls tools, monitors signals, adapts when something slips, and loops you in for approvals.

Each layer complements—not replaces—the others. Deterministic rules remain the source of truth for compliance; learned and generative components add speed, scale, and guidance around them.

SDWIS Case Study: From Excel to Agentic AI

#

We’ll walk one scenario end-to-end: monitoring, detecting, and responding to potential drinking water violations (e.g., MCL exceedances, missed monitoring, public notice).

Level 1 — Spreadsheets

#

• Reality: CSVs and emails arrive from labs. Analysts paste values into workbooks. Pivot tables flag outliers. A macro drafts a boilerplate notice.
• Pain: Copy-paste errors, stale lookups, version sprawl, and no single audit trail. Work is person-dependent.

Level 2 — Rules-Based (Declarative)

#

• Design: Move determinations into rules tables and SQL views. Examples:

  • “If analyte = lead and result ≥ threshold for sample type S in period P → flag potential exceedance.”
  • “If sampling window ends in N days with no result → create pending alert.”

• What improves: One source of truth for compliance logic; test suites and fixtures; deterministic outcomes; better explainability to program owners and auditors.

Level 3 — ML for Prioritization

#

• Use ML where it adds value, not for the law itself. Examples:

  • Predict which systems are at high risk of missed monitoring based on history, staffing patterns, weather, and seasonality.
  • Rank which violations will require more coordination effort.

• Guardrails: Keep statutory determinations rules-based. Use ML for ordering work and allocating scarce attention.

Level 4 — DL for Unstructured Inputs

#

• Ingest PDFs and images: OCR lab sheets; extract analyte, units, method, sample time, detection limit.
• Resolve entities: Map misspelled system names and IDs to a canonical PWSID.
• Outcome: Fewer manual transcriptions; faster availability of clean data for the rules engine.

Level 5 — Generative AI (with RAG)

#

• Grounded drafting and Q&A:

  • Draft a public notice from the facts of record, citing the applicable rule section.
  • Summarize a system’s compliance history for an upcoming sanitary survey.
  • Explain why a determination was made, pointing to the exact paragraphs in guidance and the specific data rows.

• Safety pattern: Retrieval-augmented generation over a curated corpus (rule text, guidance, state SOPs, past approved notices), with citations and redaction rules.

Level 6 — Agentic AI (Plan, Act, Adapt)

#

• Goal: “Maintain monitoring compliance and timely public notices for all PWS in region R this quarter.”

• Agent behavior:

  1. Plan: Build a weekly plan per PWS: expected samples, due dates, lab turnaround assumptions.
  2. Act: Poll inbox/SFTP for incoming results; call the validation pipeline; update the compliance state machine; open tasks in the case system.
  3. Draft: Prepare notices and internal memos (with citations) and route for approval.
  4. Coordinate: Put follow-ups on calendars, email systems about missing samples, and file records to the repository.
  5. Adapt: If lab delays threaten deadlines, expand outreach, escalate to alternates, or adjust the plan—asking for approval when policy requires.
  6. Audit: Log every tool call, input, draft, and approval with timestamps and IDs.

You still hold the pen on approvals. The agent moves the work forward, keeps the ledger, and makes the state visible.

Capability Matrix (SDWIS Focus)

#

◐ = partial or manual

Architecture You Can Build and Govern

#

Data plane

• Landing → Validation → Curation: raw lab files to validated facts.
• Rules service: declarative logic in tables and SQL; versioned snapshots.
• Model service: ML/DL endpoints for risk scoring and extraction.
• Vector store: curated guidance, rule text, SOPs, and approved exemplars for RAG.
• Case system: violations, tasks, and correspondence with status.

Control plane

• Orchestrator/Agent runtime: plans, invokes tools, tracks state, retries with backoff, and requests approvals.
• Tool adapters: email, calendar, SFTP/inbox, ticketing/case, document store—idempotent, time-bounded, well-logged.
• Policy & guardrails: PII redaction, role-based permissions, approval thresholds, retention windows.
• Observability: structured logs, traces for tool calls, decision records, and human approvals.
• Evals: regression tests for rules, retrieval quality, prompt answers (cited), and end-to-end task success.

Principle: compliance remains rules-first; learned and generative components make the system faster, not fuzzier.

Design Choices That Age Well

#

1. Keep rule logic declarative and versioned. Every change is diffable, testable, and explainable.
2. Separate “determine” from “predict.” Rules determine statutory outcomes; models predict risk and effort.
3. Use RAG as a reader, not an oracle. Always cite the source passages used.
4. Bound the blast radius of tools. Idempotency keys, timeouts, circuit breakers; approvals at policy gates.
5. Observe everything. If it isn’t logged, it didn’t happen.
6. Prefer small, shippable steps. Move one workflow up one level; measure, then continue.
7. Data contracts over tribal knowledge. Make lab feeds, units, and mappings explicit.
8. Human-in-the-loop by default. Especially for notices, escalations, and public communications.
9. Security by construction. Least privilege, encrypted stores, redaction at retrieval, and per-tool scopes.
10. Test like you mean it. Unit tests for rules, golden sets for OCR/NLP, offline evals for retrieval, and scenario tests for the agent.

How Each Level Changes Day-to-Day Work

#

• Excel → Rules: Fewer “Which tab?” conversations. One system says what’s due and why.
• Rules → ML/DL: Staff time shifts from hunting to prioritizing and resolving.
• ML/DL → GenAI: The first draft of notices, memos, and summaries is always ready—with citations and consistent tone.
• GenAI → Agentic: The system stops waiting for you. It plans the week, nudges the right people, adapts to delays, and brings you the exact approvals to sign.

Practical Starting Sequence

#

1. Inventory the logic. Extract compliance rules from spreadsheets and code into a rules table with tests.
2. Stabilize ingestion. Automate lab intake, validation, and units. Add OCR/NLP only where you actually receive unstructured inputs.
3. Introduce ML where you triage. Risk of missed monitoring, likelihood of late results—use to order work, not to decide the law.
4. Add RAG for explanations and drafts. Ground everything in your guidance corpus; require citations.
5. Wrap tools with policy. Email, calendar, ticketing, document store—adapters with guardrails.
6. Pilot a narrow agent. One goal, one region, one quarter. Approvals baked in. Measure time-to-notice, rework rate, and staff hours saved.

Small, well-governed wins build trust. Trust earns you the mandate to modernize the rest.

Closing Thought

#

Public health depends on timely, accurate, explainable action. You don’t get that by leaping to the fanciest stack. You get it by moving from imperative sprawl to declarative rules, adding learning where it helps, using generative systems to draft and explain, and finally letting agents plan and push work forward—under your policies and approval.

If this roadmap matches the reality you see in SDWIS today, the next step is straightforward: pick one workflow, elevate it one level, and prove the gain. The rest will follow.