Laravel development for Bristol engineering and sensor data

Bristol's engineering and environmental firms bring a workload that most web applications never see, and most web development advice is written for the opposite case.

A typical application reads far more than it writes. A sensor platform, a flight test system, an energy monitoring product or an environmental monitoring network does the reverse - thousands of writes a minute arriving continuously, with reads concentrated into reports and dashboards somebody looks at occasionally. Almost every default, from indexing to normalisation to using model events, is tuned for the other shape.

A write-heavy Bristol table is a different animal

The first thing to establish is that this does not necessarily need a specialist database, because reaching for one before measuring is a detour that costs months.

A relational database will take a very large volume of inserts if the table is treated as what it is. That means as few indexes as the queries genuinely require, because every index is a write cost paid on every row. It means inserting in batches rather than one statement per reading. It means keeping foreign key checks and application logic off the ingest path. And it means partitioning by time, so that removing last year is detaching a partition rather than a delete statement that holds a lock for an hour.

Where that genuinely runs out, a purpose-built store is the right answer. It should be a conclusion from a measurement, though, not an assumption from a conference talk.

Writes slowing down means something was added

The most common Bristol support request on these systems is that ingest has become slower without anybody changing the ingest code.

Two causes account for nearly all of it. An index was added to make a report faster, and now every insert maintains it. Or a model observer was attached for a sensible reason, and now a batch insert of ten thousand rows executes ten thousand pieces of application logic, each with its own queries.

Both are visible immediately if you look, and invisible if you only look at the code that does the writing. Queries that multiply quietly is the related pattern on the read side; this is its mirror image, and it is less well known because fewer applications are write-bound.

Summaries belong in the Laravel schema

Raw readings are almost never what anybody wants to see. They want an hourly average, a daily peak, a monthly total, a trend.

Computing those on demand from the raw table works until the raw table is large, at which point every dashboard becomes a full scan. The arrangement that scales is to compute aggregates as the data arrives, store them in their own tables, and have every chart and report read from those.

The detail that matters is making the aggregation re-runnable for a specific period. Readings arrive late. Readings get corrected. A calibration error is discovered and three weeks of data needs recomputing. If the aggregate can only be built once, the summary is permanently wrong and the raw data is right, which is the worst of both.

Retention is a Bristol engineering decision with a date on it

Keeping everything forever is a decision, usually made by not making one, and it is discovered when the disk fills.

The version worth writing down on day one is simple: raw readings at full resolution for a defined window, aggregates kept indefinitely because they are small, and removal implemented as partition detachment rather than deletion. All three are easy at the start. The third one in particular becomes a genuine outage if it is left until the table is a billion rows deep and somebody runs a delete.

A Bristol sensor sends the same reading twice

Devices retry. A gateway loses its connection halfway through a batch, comes back, and sends the whole batch again. Nothing is wrong, and the table now holds every reading twice.

The fix is a deduplication key that the device supplies rather than one the database generates - the device identifier plus the reading's own timestamp, with a unique constraint across the pair, and an insert that ignores a collision. That turns a retry into a no-op at the database rather than a cleanup job at the end of the week.

Arrival order is the second problem and it is worse, because nothing about it looks like an error. Readings from Tuesday afternoon arrive on Wednesday morning, after Tuesday's summary has already been computed. The summary is now wrong and nothing has flagged it. What works is to record, per summary, the latest arrival it accounted for, so a reading that lands behind that watermark marks its period for recomputation instead of being silently lost.

There are two timestamps on every reading and conflating them is the mistake underneath both problems. The device clock says when the measurement was taken and it drifts, sometimes badly. The server clock says when the reading arrived and it is reliable but answers a different question. Store both. Engineering analysis uses the first; pipeline diagnostics use the second; and the gap between them is itself a useful signal about a device that is about to cause trouble.

Calibration, and the Bristol reading that changes after the fact

A sensor is recalibrated and six months of readings turn out to have been two per cent high. The readings were recorded faithfully. They were also wrong, and the correction applies backwards.

An application that stored one number per reading has nowhere to put this. Overwriting destroys the record of what the instrument actually reported, which in engineering work is the thing somebody will eventually need to inspect. Leaving it alone means every downstream figure is quietly off.

The arrangement that survives is to keep the raw value as recorded and derive the corrected value from the calibration in force at the time of the reading. Calibrations become their own records with effective dates, a reading resolves to the one that applies, and a retroactive correction is a new calibration row with an earlier effective date rather than an update to thousands of readings.

Summaries then have to carry which calibration generation they were built under, because a correction invalidates them as surely as late-arriving data does. That is the same watermark mechanism as above, pointed at a different input, and it is worth building once rather than twice. The payoff is that the question "why did last quarter's figure change" has an answer with a date and an instrument on it.

Commercially

Scope fixed in writing before work starts, priced per project. The country-level ground - UK data protection, procurement, invoicing - is on the United Kingdom market page.

Invoiced by Codefacture Yazılım A.Ş. in Türkiye, in pounds by bank transfer, with no Turkish VAT under the export-of-services exemption.