Laravel Analytics: Safe Additive Upserts Under Concurrent Traffic

When I started persisting historical analytics in ICanUp, one of the first requirements looked trivial: every eligible view should increment the relevant counter by one.

For a single request, the obvious implementation is to read the current value, add 1 in PHP, and save it back.

But a web application does not process views strictly one at a time.

If two requests arrive at nearly the same moment, that simple sequence can lose one of the views without producing an error.

In simplified form, the code may look like this:

For one request, the result is correct.

The problem appears when two processes execute the sequence concurrently.

There were two new views, so the correct result should have been 102.

Both requests were working from the same previous value.

The second write does not add another increment. It simply writes 101 again.

For this kind of counter, I need another property: the increment should happen as an atomic database operation against the current stored value.

PHP no longer reads 100 in order to later write 101.

The database applies views = views + 1 directly to the current row value.

Daily analytics adds another condition.

The first view for a particular combination of dimensions on a given day may not have a row yet.

The required contract becomes: create the row if it does not exist, otherwise atomically increase the existing value.

This is where an upsert-style operation becomes useful, although the important property is atomicity rather than the method name.

The first request creates the row with 1.

Later requests do not perform a read-modify-save loop. They accumulate directly in the database.

An atomic insert-or-update is only correct when the target row has an unambiguous identity.

A daily analytics aggregate is not identified by date alone.

For example, a Ukrainian and an English view of the same Post should not accidentally accumulate into one localized bucket when analytics needs a locale breakdown.

The same principle applies to other supported dimensions such as country, device type, browser family, operating system, traffic source, and other bounded values intentionally stored by the analytics system.

If two requests have different dimension sets, they need to increment different aggregates.

If aggregate identity exists only in PHP, two concurrent requests can still both decide that the row does not exist yet.

The database schema should therefore help guarantee that one identity cannot create duplicate daily rows.

ICanUp tracks regular views and deduplicated views separately.

Deduplication decides whether the second metric should be incremented for a request.

Once the decision to increment is made, however, the accumulation itself still needs to be safe under concurrent writes.

The purpose of an atomic write is not to wrap every page view in a large manual lock.

The shorter the increment operation, the better it fits a frequently executed public request path.

I want the database to perform the smallest possible change against one aggregate bucket.

Once the aggregate identity includes a date, another question becomes critical: when exactly does the next day begin?

One component cannot use the PHP process timezone, another the MySQL timezone, and a third UTC by accident.

The aggregate date needs to be derived deterministically from one reporting-time contract. In the current architecture, this is tied to Europe/Kyiv.

A server may run in UTC. A queue worker or another process may also have different environment settings.

If code simply asks for "today" without an explicit contract, views around midnight can land in the wrong reporting day.

I do not want logic such as "Kyiv is always UTC+3".

A named timezone allows the standard time library to apply daylight-saving rules correctly.

Correct day calculation during writes is not enough.

Today filters, daily charts, and period comparisons need to use the same reporting-time contract.

For this analytics problem, I do not need an unlimited store of every HTTP request.

The current questions can be answered efficiently through accumulated values per day and bounded dimensions.

Without raw events, an incorrect aggregate cannot simply be reconstructed later from a complete event history.

Three properties therefore become especially important:

• correct aggregate identity;
• atomic increments;
• deterministic reporting date.

Every additional dimension splits data into smaller groups.

That is useful, but it has a cost.

This is why I keep analytics dimensions bounded and normalized rather than storing arbitrary request values.

For country analytics, for example, a normalized country code is sufficient. Raw IP is not needed.

When a dimension is unknown, its representation also needs to be consistent during identity construction.

Otherwise semantically identical requests can end up in different buckets because one uses null, another an empty string, and another unknown.

Atomic accumulation protects against concurrent overwrites, but it does not solve every analytics problem automatically.

If the same logical view is accidentally processed twice, the database will correctly increment the counter twice.

Concurrency safety and retry idempotency are separate concerns.

Another architectural decision is that historical metrics live in persistent database storage.

Cache may optimize reads, but clearing it must not erase view history.

A test that sequentially records two views and receives 2 does not prove that the implementation is safe under concurrency.

The persistence contract should also protect aggregate uniqueness and avoid a read-modify-save counter implementation.

• the first write creates an aggregate;
• the next write increments the same aggregate;
• different dimension sets create different aggregates;
• one identity does not create duplicate daily rows;
• views and deduplicated views accumulate independently;
• events around the reporting-day boundary land on different dates;
• UK and EN remain separate when locale is a dimension;
• clearing cache does not delete historical metrics.

This is a small test that protects the entire daily chart from a very subtle midnight shift.

• Do not read the current views value into PHP just to write views + 1 back.
• Do not rely only on an application-level "does the row exist?" check before INSERT.
• Do not define aggregate uniqueness only in application code.
• Do not let a process timezone accidentally define reporting dates.
• Do not merge different dimension sets into the same aggregate.
• Do not persist arbitrary unbounded values as analytics dimensions without a reason.
• Do not call deduplicated views an exact count of unique people.
• Do not use cache as the only historical store.
• Do not treat atomic increments as automatic protection from processing the same logical event twice.

• Define the exact identity of one aggregate first.
• Protect that identity with a database constraint.
• Avoid read -> increment -> save for a concurrent counter.
• Increment values atomically in the database.
• Support first-row creation and existing-row accumulation through one safe contract.
• Normalize dimension values before building the identity.
• Use one reporting timezone.
• Use the same day boundaries for writes and reads.
• Treat concurrency, deduplication, and retry idempotency as separate concerns.
• Test day boundaries and multiple dimension combinations.
• Keep history in persistent storage and treat cache only as an optimization.

In ICanUp daily analytics, the main problem was not incrementing views itself. It was guaranteeing that concurrent requests could not overwrite each other.

A read -> add 1 -> save cycle works sequentially but can silently lose updates under concurrency.

Accumulation therefore needs to happen atomically in the database, while each daily aggregate is identified by a stable combination of date, metric, subject type, optional subject identity, and the required dimensions.

The reporting date is equally important. If different parts of the application disagree about when a day begins, even perfectly atomic counters will produce an incorrect daily chart. Writes and reads therefore need one reporting-time contract.

My main lesson is that an analytics counter has to be correct not only for one request, but also when many requests try to change it at the same time.