Designing Idempotent Operations with SQLite

Making Retries Safe Without Processing the Same Operation Twice

In the previous article, SQLite as a Durable Store-and-Forward Buffer, we built a system that could keep outbound data safe when the network disappeared.

Instead of assuming every request would succeed immediately, the application stored work locally, retried failed deliveries, used backoff, recovered abandoned work after crashes, and continued once connectivity returned.

That reliability creates another problem.

Retries create duplicates.

Imagine a remote device sends this operation:

Record payment: $50
Operation ID: pay-7842

The server receives it and records the payment.

But before the success response reaches the device, the connection drops.

From the device’s point of view, the result is unknown.

So it retries:

Record payment: $50
Operation ID: pay-7842

Without protection, the server might record another $50 payment.

The retry was correct. Processing the operation twice was not.

This is where idempotency becomes essential.

An idempotent operation can be attempted repeatedly without repeating the business effect.

SQLite gives us several useful building blocks for implementing this safely: unique constraints, transactions, conditional writes, stored results, and durable operation records.

The goal is not to prevent retries.

The goal is to make retries safe.

What Idempotency Actually Means

An operation is idempotent when performing the same logical operation multiple times produces the same intended result as performing it once.

For example:

Set device mode = maintenance

Running that five times still leaves:

device mode = maintenance

But this operation is different:

Increase retry counter by 1

Run it five times and the counter increases five times.

Likewise:

Charge customer $50

cannot simply be repeated.

The important distinction is between the request and the business effect.

We may receive the request more than once.

We want the business effect to happen once.

Why Retries Are Unavoidable

Retries occur for many legitimate reasons:

• Network timeouts

• Lost responses

• Application crashes

• Worker restarts

• Mobile connectivity changes

• Server overload

• Temporary service failures

• Queue redelivery

• Client-side retry policies

We saw this same reliability problem when

where network failures require queued changes to survive locally and be retried safely when communication resumes.

Consider:

Client
  ↓
Send request
  ↓
Server processes request
  ↓
Server commits transaction
  ↓
Response lost
  ↓
Client times out

The client cannot know whether the server committed the operation.

There are two dangerous assumptions.

Assumption one:

The timeout means the operation failed.

That may cause duplicate processing.

Assumption two:

The timeout means the operation succeeded.

That may cause lost work if the server never processed it.

The safe response is usually:

Retry the same logical operation using the same identity.

That requires the receiving system to recognize it.

Give Every Logical Operation an Idempotency Key

The foundation of the design is a stable identifier.

For example:

01J7R3B4Y5H2Q8P9M6N4K1T0VX

or:

device-42:command:91827

or:

order-8301:payment:1

The exact format matters less than the rule:

Every retry of the same logical operation must use the same key.

A new operation gets a new key.

A retry does not.

Suppose the client sends:

Idempotency-Key: pay-7842

The first attempt uses:

pay-7842

A timeout occurs.

The retry must also use:

pay-7842

If the client generates:

pay-7843

for the retry, the server sees a completely new operation.

Idempotency is lost.

Store the Operation in SQLite

A basic table might look like this:

CREATE TABLE IdempotencyRecord (
    IdempotencyKey TEXT PRIMARY KEY,
    OperationType TEXT NOT NULL,
    Status TEXT NOT NULL,
    CreatedAt INTEGER NOT NULL,
    CompletedAt INTEGER,
    ResponseCode INTEGER,
    ResponseBody TEXT
);

The table gives SQLite durable memory.

Instead of asking:

Have I seen something similar before?

the application asks:

Have I already processed this exact logical operation?

That distinction is critical.

Let SQLite Enforce Uniqueness

Do not rely only on application code such as:

SELECT key
↓
Not found
↓
INSERT key

Two workers could perform the check simultaneously.

Worker A:

Key not found

Worker B:

Key not found

Both then try to process the operation.

This is a classic check-then-act race.

Instead, make uniqueness a database invariant:

IdempotencyKey TEXT PRIMARY KEY

or:

CREATE UNIQUE INDEX ux_idempotency_key
ON IdempotencyRecord(IdempotencyKey);

Now SQLite becomes the final authority.

Two workers may race.

Only one can successfully claim that key.

Claim the Operation Atomically

A useful pattern is:

INSERT INTO IdempotencyRecord (
    IdempotencyKey,
    OperationType,
    Status,
    CreatedAt
)
VALUES (?, ?, 'processing', ?)
ON CONFLICT(IdempotencyKey) DO NOTHING;

The application then checks whether the insert actually created a row.

If yes:

This worker owns the new operation.

If no:

This key already exists.

That gives us an atomic claim.

No separate existence check is required.

Duplicate Does Not Automatically Mean Completed

Suppose a duplicate request arrives and the key already exists.

We still need to inspect its state.

It might be:

processing

or:

completed

or perhaps:

failed

These situations mean different things.

A completed operation can usually return its stored result.

A processing operation may tell the caller that the request is still underway.

A failed operation requires a defined retry policy.

So the idempotency table is not merely a list of keys.

It is a small operation state machine.

Store the Result

Consider an API request that creates an order.

First request:

POST /orders
Idempotency-Key: order-create-9921

The server creates:

OrderID = 5817

Then the response disappears.

The client retries.

We should not create order 5818.

Instead, SQLite can remember the original response:

IdempotencyKey = order-create-9921
Status         = completed
ResponseCode   = 201
ResponseBody   = {"orderId":5817}

The retry can return the previously stored result.

From the client’s perspective, both attempts resolve to the same logical outcome.

The Business Change and Idempotency Record Must Agree

This is where implementations often become unsafe.

Imagine:

Create order
↓
COMMIT

Application crashes

Write idempotency record

The order exists, but the idempotency record does not.

After restart, the retry looks new.

The application creates another order.

Reversing the order does not solve it:

Write idempotency record
↓
COMMIT

Application crashes

Create order

Now the database claims the operation happened even though the business effect did not.

When both pieces of state live in the same SQLite database, use one transaction.

BEGIN IMMEDIATE;

INSERT INTO IdempotencyRecord (
    IdempotencyKey,
    OperationType,
    Status,
    CreatedAt
)
VALUES (?, 'create_order', 'processing', ?);

INSERT INTO Orders (
    CustomerID,
    CreatedAt
)
VALUES (?, ?);

UPDATE IdempotencyRecord
SET
    Status = 'completed',
    CompletedAt = ?,
    ResponseCode = 201,
    ResponseBody = ?
WHERE IdempotencyKey = ?;

COMMIT;

Now either the complete operation commits or none of it does.

That atomic boundary is one of SQLite’s strongest advantages for local application infrastructure.

Rollback Protects the Operation

Suppose the order insert fails.

As we covered in Error Handling in SQLite: Best Practices, transactions let related database changes roll back together when an operation fails, preventing partially completed work from leaving the database in an inconsistent state.

The transaction rolls back.

That means the processing idempotency record created inside the same transaction also disappears.

The operation can safely be retried.

This gives us:

Business effect succeeds
+
Idempotency state succeeds

or:

Neither succeeds

rather than allowing the two to drift apart.

Some Operations Are Naturally Idempotent

Not every operation needs an idempotency table.

Consider:

UPDATE Device
SET DesiredMode = 'maintenance'
WHERE DeviceID = 42;

Running this repeatedly still produces the same desired state.

Compare it with:

UPDATE Inventory
SET Quantity = Quantity - 1
WHERE ProductID = 42;

Repeating that statement changes the result every time.

Similarly:

Set temperature limit to 80°C

is naturally much easier to retry than:

Increase temperature limit by 5°C

When designing APIs and internal operations, prefer commands that express the desired final state when that fits the business problem.

It can reduce the amount of explicit deduplication required.

Business Keys Can Sometimes Provide Idempotency

Suppose each sensor reading has a globally stable identity:

ReadingID = sensor-14:1738491200123

The telemetry table can enforce:

CREATE TABLE Telemetry (
    ReadingID TEXT PRIMARY KEY,
    SensorID INTEGER NOT NULL,
    RecordedAt INTEGER NOT NULL,
    Value REAL NOT NULL
);

Then ingestion can use:

INSERT INTO Telemetry (
    ReadingID,
    SensorID,
    RecordedAt,
    Value
)
VALUES (?, ?, ?, ?)
ON CONFLICT(ReadingID) DO NOTHING;

The business table itself provides deduplication.

A separate idempotency table may not be necessary.

This is particularly useful for event ingestion, synchronization, telemetry, imports, and replicated records where every item already has a durable identity.

Be Careful with INSERT OR REPLACE

A tempting solution is:

INSERT OR REPLACE INTO ...

But replacement is not the same as ignoring a duplicate.

Conceptually, you may want:

If this already exists, leave it alone.

Replacing a row can have different effects from preserving the original record.

For idempotency, be explicit about the desired conflict behavior.

Often:

ON CONFLICT(...) DO NOTHING

or a carefully designed DO UPDATE is clearer.

The database should express the actual business rule, not merely suppress an error.

The Same Key Must Mean the Same Request

Here is a subtle problem.

First request:

Idempotency-Key: pay-7842
Amount: $50

Later:

Idempotency-Key: pay-7842
Amount: $500

Should the second request simply receive the first result?

No.

The caller has reused an idempotency key for different input.

That is a client error.

One way to detect this is to store a fingerprint of the relevant request data:

ALTER TABLE IdempotencyRecord
ADD COLUMN RequestHash TEXT;

For example, calculate a hash from canonicalized inputs such as:

operation type
customer ID
order ID
amount
currency

On retry:

Same key + same request hash
→ legitimate retry

Same key + different request hash
→ reject

An idempotency key identifies one logical operation, not an unlimited collection of requests.

Canonicalization Matters

If request hashing is used, equivalent inputs need a stable representation.

These JSON documents may mean the same thing:

{"amount":50,"currency":"USD"}

and:

{"currency":"USD","amount":50}

Hashing the raw strings could produce different values.

Likewise:

50
50.0
50.00

may or may not be equivalent depending on the domain.

Define exactly which fields determine operation identity and normalize them before hashing.

Idempotency is a business rule first and a hashing problem second.

Scope Your Keys Correctly

A globally unique UUID may need no additional scope.

But simple client-generated keys can collide.

Imagine two devices both send:

operation-100

If the database treats that as globally unique, one device could accidentally block the other.

Instead, uniqueness might be:

ClientID + IdempotencyKey

For example:

CREATE TABLE IdempotencyRecord (
    ClientID TEXT NOT NULL,
    IdempotencyKey TEXT NOT NULL,
    OperationType TEXT NOT NULL,
    Status TEXT NOT NULL,
    RequestHash TEXT,
    CreatedAt INTEGER NOT NULL,
    CompletedAt INTEGER,
    ResponseCode INTEGER,
    ResponseBody TEXT,

    PRIMARY KEY (ClientID, IdempotencyKey)
);

The correct scope might instead be:

DeviceID
TenantID
UserID
API client
Destination

Choose the scope according to who is allowed to create keys.

Idempotency and Concurrency Are Closely Connected

Idempotency is often explained as duplicate detection.

In production systems, it is also a concurrency problem.

Two identical requests may arrive nearly simultaneously:

Request A ──────┐
                ├── same operation
Request B ──────┘

The application cannot depend on one request finishing before the other begins.

SQLite’s uniqueness constraints and transactions let the database arbitrate that race.

One request claims the operation.

The other observes that the operation already exists.

This is far safer than trying to coordinate workers using application memory.

What Should a Concurrent Duplicate Receive?

Suppose request A owns the operation and request B arrives while A is still processing.

Possible policies include:

Return "processing"

or:

Wait briefly, then reread the result

or:

Return a conflict / retry-later response

There is no universal answer.

The important rule is that request B must not independently execute the business operation.

The API contract should define what a caller receives when an identical operation is already in progress.

What About Crashed processing Operations?

Suppose we deliberately commit a claim before performing work that cannot occur inside the same SQLite transaction.

Then:

Status = processing

may survive a crash.

That creates the same lease problem we encountered with durable queues.

Add fields such as:

ClaimedAt
ClaimedBy

and define when a stale operation can be recovered.

But use this pattern only when needed.

If the business change and idempotency state can be committed atomically in one local SQLite transaction, that is simpler and stronger.

Local Transactions Cannot Make Remote Side Effects Atomic

Suppose an operation does this:

Write SQLite record
↓
Call external payment service
↓
Update SQLite status

SQLite cannot include the remote service inside its local transaction.

This boundary becomes especially important in SQLite-based distributed systems, where network latency, synchronization, and failures between separate nodes introduce consistency problems that a single local transaction cannot solve by itself.

Holding:

BEGIN IMMEDIATE;

open while waiting on the network does not make the remote operation atomic.

It only creates a long-lived database transaction.

Instead, split the workflow into durable stages.

For example:

Requested
↓
Persist locally
↓
Queue remote action
↓
Perform remote call
↓
Record confirmed result

And give the remote operation its own stable idempotency key whenever the external service supports one.

This is where idempotency connects directly to durable queues and store-and-forward architecture.

Idempotency Does Not Mean Ignoring Every Duplicate

Suppose an alert acknowledgement request arrives twice.

The second copy may safely produce no additional state change.

But you may still want to record:

Duplicate request observed

for diagnostics.

Likewise, a repeated payment request should not charge twice, but returning the original payment result is more useful than silently discarding the retry.

Idempotency controls the business effect.

It does not require pretending the duplicate request never arrived.

Side Effects Need Protection Too

Imagine the transaction correctly prevents a duplicate order:

Order created once

but after every request the application sends:

Order confirmation email

The client retries three times.

The order exists once, but the customer receives three emails.

The database write was idempotent.

The entire operation was not.

Other side effects may include:

• Emails

• Push notifications

• Webhooks

• Audit events

• Queue messages

• File creation

• Remote API calls

Each important side effect needs its own delivery identity or durable state.

A common pattern is:

Business transaction
       ↓
Record outbound work
       ↓
Commit
       ↓
Background delivery

This avoids treating an external side effect as though it were part of the SQLite transaction.

We will return to this architecture later when we build a transactional outbox.

Don’t Confuse Idempotency with Debouncing

Suppose a button is clicked twice within 500 milliseconds.

The UI may debounce the clicks and send only one request.

That improves user experience.

It is not a replacement for idempotency.

Duplicates can still come from:

Network retry
Worker retry
Message redelivery
Application restart
Another client

Idempotency must be enforced where the business effect occurs.

Client-side prevention is only an optimization.

Don’t Confuse Idempotency with Rate Limiting

Rate limiting asks:

How often may this caller perform operations?

Idempotency asks:

Is this request another attempt at an operation we already know about?

A user may legitimately create 100 different orders.

Rate limiting may permit or restrict that.

But if one of those orders is retried five times using the same idempotency key, idempotency should prevent five copies regardless of the rate limit.

These are separate controls.

How Long Should Idempotency Records Live?

Keeping every key forever may eventually create a huge table.

Deleting keys too early can allow an old retry to execute again.

Retention must match the retry window.

Suppose clients may retry for:

24 hours

Keeping records for only:

10 minutes

is unsafe.

A practical policy might retain completed records for:

7 days

or:

30 days

depending on the application.

For financial, audit, or compliance-sensitive operations, the business record itself may provide permanent uniqueness and make short idempotency retention unnecessary.

The right question is:

How long could this operation realistically be retried, replayed, or redelivered?

Clean Up in Batches

When records expire, avoid deleting a massive history in one transaction.

For example:

DELETE FROM IdempotencyRecord
WHERE IdempotencyKey IN (
    SELECT IdempotencyKey
    FROM IdempotencyRecord
    WHERE Status = 'completed'
      AND CompletedAt < ?
    ORDER BY CompletedAt
    LIMIT 5000
);

Repeat periodically.

As with our previous queue and telemetry retention designs, bounded maintenance is easier to operate than occasional enormous cleanup jobs.

Monitor Idempotency Behavior

Useful metrics include:

New operations
Duplicate requests
Duplicate percentage
Operations currently processing
Stale processing records
Hash mismatches
Failed operations
Average processing duration
Idempotency table size

A sudden rise in duplicate requests may indicate:

• Network instability

• Aggressive client retry behavior

• Slow server responses

• Worker crashes

• Incorrect acknowledgement handling

Idempotency isn’t only protection.

Its data can reveal reliability problems elsewhere in the system.

Example: Remote Maintenance Command

Consider an industrial monitoring system.

A technician sends:

Restart pump controller

The control platform assigns:

CommandID = cmd-91827

The edge gateway receives it.

Before acting, SQLite records the command identity.

The controller restarts.

During the restart, the network connection disappears before the gateway confirms completion.

The cloud retries:

CommandID = cmd-91827

The gateway checks SQLite.

It already knows that command.

Instead of restarting the controller again, it returns the recorded result.

Now imagine a genuinely new restart request ten minutes later.

It receives:

CommandID = cmd-91828

That operation is processed normally.

The system is not asking:

Has a restart happened recently?

It is asking:

Have I processed this exact command?

That is a much stronger guarantee.

A Production Idempotency Flow

A robust request path can look like this:

Request arrives
      ↓
Read idempotency key
      ↓
Validate scope and request fingerprint
      ↓
Attempt atomic claim
      ↓
Already completed?
      ├── Yes → return stored result
      ↓
Already processing?
      ├── Yes → follow in-progress policy
      ↓
New operation
      ↓
Perform business change
      ↓
Store result
      ↓
Commit
      ↓
Return response

If the response disappears:

Client retries
      ↓
Same idempotency key
      ↓
SQLite finds completed operation
      ↓
Return original result

No duplicate business effect occurs.

Best Practices

When designing idempotent operations with SQLite:

• Give each logical operation a stable identity.

• Reuse the same identity for every retry.

• Let SQLite enforce uniqueness.

• Avoid check-then-insert races.

• Store completed results when callers need consistent retry responses.

• Keep the business change and idempotency record in one transaction whenever possible.

• Prefer naturally idempotent state-setting operations where appropriate.

• Use existing business identities when they already guarantee uniqueness.

• Reject reuse of the same key with different request data.

• Scope client-generated keys correctly.

• Define behavior for concurrent duplicate requests.

• Recover stale claims when operations must span multiple stages.

• Never assume a SQLite transaction can make a remote side effect atomic.

• Protect emails, webhooks, messages, and other external side effects too.

• Keep idempotency separate from debouncing and rate limiting.

• Retain keys for at least the realistic retry window.

• Clean old records incrementally.

• Monitor duplicates because they often expose wider reliability problems.

Closing Thoughts

Retries are not a defect in a reliable system.

They are one of the mechanisms that make the system reliable.

The danger appears when an application treats every retry as a brand-new business operation.

SQLite gives us a durable place to remember operation identity, enforce uniqueness, coordinate concurrent attempts, preserve results, and commit idempotency state together with local business changes.

The key architectural principle is simple:

Retry the request. Do not repeat the effect.

Once operations can be retried safely, we can build more sophisticated infrastructure on top of them.

The next step is moving from individual retried operations to durable background work that can survive application restarts, coordinate multiple workers, recover abandoned jobs, and process tasks over time.

That takes us to the next article in Phase 2:

Building Durable Job Queues with SQLite

Creating restart-safe background workers with claiming, leasing, retries, and failure recovery.

Subscribe Now

Build Reliable Applications with SQLite

Retries, crashes, and duplicate requests are normal parts of real-world applications. The difference is whether your architecture can handle them safely.

Subscribe to SQLite Forum for practical tutorials on idempotency, durable queues, reliable data pipelines, edge systems, performance, and production-ready SQLite architecture.

Subscribe and keep building SQLite applications that stay correct even when operations have to be tried again.