Groundwater Agency Software Problems: The 5 That Cost Real Money, and How to Avoid Them

Why does the allocation model turn out to be the biggest scope failure?

Nearly every agency starts the same way, with one row per well and an annual allotment in a column. It is the obvious shape and it collapses inside two seasons, usually right after the first transfer or the first mid year ownership change.

Allocations attach to parcels in some basins and to wells in others. A well can serve several parcels and a parcel can be served by several wells, so the relationship is many to many before you have added anything interesting. Then parcels split. Owners change in July. A landowner fallows ground and carries forward unused allotment under a multi year window, which makes the balance a running account rather than an annual figure. Another borrows against next year. A row with a number cannot express any of it, and staff start keeping the real answer in a second sheet.

The fix is to treat allocation as a double entry ledger from the first line of code. Every change is a posting: initial assignment, annual accrual, metered debit, carryover, transfer in or out, and adjustment with a reason code and the staff member who made it. The balance is derived and never typed. The reason this matters beyond tidiness is evidential. What an attorney receives is a statement of account, which is a form everybody in the room already understands and nobody argues with structurally. Ask any developer to model the many to many parcel and well relationship before you sign, because a design that puts an allotment field on a well record has been built for asset tracking.

What goes wrong when you migrate the allocation spreadsheet and meter history?

The spreadsheet migrates in an afternoon and that is exactly the problem. It contains balances with no history behind them, and importing a balance means importing an assertion you cannot support.

What you find during a careful migration is uncomfortable and worth finding. Meter replacements recorded as two unconnected totals rather than as a final read and a starting read. Estimated volumes filling telemetry gaps that were never flagged as estimates and are now indistinguishable from measured data. Adjustments in cells with no note. Carryover balances that were recalculated at some point using a formula nobody can reproduce. Every one of those is a figure that has already been published to a landowner.

The fix is to import events rather than balances, and to mark what cannot be reconstructed as unknown rather than deriving it. Bring in raw meter reads and manual submissions as postings with their original dates, rebuild balances from those postings, and compare the result against the spreadsheet balance for every account. Where the two differ, that difference is a finding for the board rather than a rounding decision for a developer. Run this before any go live date is announced, and give it to someone senior enough to take the reconciliation to the board with a straight face.

Why do the meter and parcel roll feeds break after launch?

Two external feeds carry a groundwater system and both fail quietly. Meter loggers come first. A basin ends up with cellular units from several manufacturers, a handful of agency wells on supervisory control, and a long tail of pumpers reading a totaliser onto a card. Each vendor exposes data differently, in different units, and totalisers roll over. After launch, the failures are field failures: a logger goes offline for eleven days, a unit is swapped and keeps reporting under the old device identifier, a firmware update changes the timestamp format.

The county assessor roll is the second. Fees attach to parcels, the authoritative parcel record belongs to the county, and it changes without telling you. Splits, mergers, ownership transfers and address corrections arrive once a year in whatever format the county publishes, and if your billing entity is a parcel number somebody typed once, invoices start bouncing in year three.

The fix is normalisation with permanent raw retention on one side and versioning on the other. Store the raw meter reading forever, derive volume with the conversion recorded, and treat every correction as a superseding record rather than an overwrite. Make meter replacement a first class event linking the old final read to the new starting read. On the parcel side, import the roll as new versions with staff resolving splits and merges in a review screen, and stamp every invoice with the parcel version it was issued against. If the county collects on the tax roll for you, their file format and deadline are fixed design constraints from day one rather than acceptance testing surprises.

What happens when enforcement and credit transfers are left out of scope?

Both get deferred because they feel like policy rather than software, and both are where the process risk concentrates.

Enforcement is a sequence of statuses with clocks attached: notice of exceedance, cure period, penalty assessment, appeal, settlement. Kept in Outlook folders, it produces a specific and avoidable failure. If a notice went out late, the penalty is voidable. If two staff gave conflicting guidance by email, the agency is bound by the friendlier answer. Neither is a hydrology problem and both end with the agency settling.

Transfers create a different exposure. Once credits can move between pumpers, the agency is operating a small registry, and a registry bolted onto a spreadsheet meets its first double spend the week a landowner sells the same forty acre feet to two neighbours.

The fix is a state machine in both cases. Enforcement steps generate from the ledger, service is timestamped, and no step can advance without the prior one evidenced. Credits are serialised and move through issued, held, listed, transferred and retired, with both parties, an effective date and an approval on every transfer, plus validity rules encoding your basin plan's restrictions on movement between management areas. Do not build a marketplace with matching and pricing in the first release even if the board is enthusiastic. Get issuance and retirement provably correct and let the trading happen by telephone for another year.

Should you build custom or configure what you already own?

Be honest about the small case. If you administer a few dozen wells on annual paper reports, allocations are not transferable and no figure has ever been formally contested, a well maintained spreadsheet and a consulting hydrologist is cheaper, faster and entirely defensible. Building would be spending public money to reach the same place with more steps.

Look hard at what you already run before adding anything. Geographic information systems belong in this stack and should be integrated rather than replaced, since parcel and well geometry is exactly what they are for. If your agency has a utility billing package, it may already handle invoicing and receivables adequately, and pushing charges into it is a smaller job than building billing. And if a consultant maintains a hydrology database, the question is whether it can hold an auditable ledger, not whether it holds numbers.

Build when a published figure has been contested, when credits are about to become transferable, when telemetry volume has outgrown manual entry, or when the one person who understands the spreadsheet is within sight of retirement. In our experience that last trigger is the most common and the most rational reason agencies move.

How do hidden costs get into the quote?

Meter vendor count is the first driver and it is routinely summarised as meter integration. Each manufacturer is separate work and vendor support quality varies a great deal, so a basin with four logger brands is four integrations with four maintenance tails, not one line item.

Multiple management areas with different rules inside one basin is the second, because it doubles the rule engine rather than adding a filter. Feeding a numerical groundwater model is third, since the model wants time series in its own structure and that transformation is real work. Public records obligations are fourth and they are frequently discovered late: redaction rules and an export path designed for a records request cost far less to build in than to retrofit. Joint powers arrangements are fifth, where several agencies share one system while each keeps its own ordinance, which is genuinely a multi tenant build and should be priced as one.

The fix is to procure in phases against your own reporting deadline, launch with the wells that are already metered, and resist modelling the entire basin plan before a single reading has been ingested. Agencies that try to encode rules still in draft pay to implement a document that changes before go live.

What separates a groundwater build that works from one that fails?

Working systems can produce, on demand and without staff intervention, a statement of account for any well or parcel showing every posting, every adjustment with its reason and author, and the share of the annual volume that was measured against the share that was estimated. That last column has ended more disputes than any amount of additional precision, and it costs almost nothing to carry.

The second marker is how corrections are handled. Ask the developer directly, and listen for superseding records with retained history. The answer you do not want involves an update statement and an audit log table that nobody reads, because an audit log is a record of what a database did rather than a record of what the agency decided.

The third is the meter replacement scenario. Put it on a whiteboard before signing: the meter is swapped in June, the replacement starts at zero, and staff have to reconcile the year. A developer with regulated measurement experience separates raw reading from derived volume immediately and asks what happens to the old totaliser. One who proposes a current reading field on the well record has built asset trackers.

Finally, settle ownership at the start. The repository, the database and the cloud accounts belong in the agency's name, with the developer holding access rather than ownership. An allocation ledger is a public record with a retention period measured in decades. Ask what a defensible export looks like on the day a records request arrives.