Stop Repeated Enqueue Requests With a SQLite Idempotency Key
Reproduce duplicate enqueue rows, bind a stable request key to its original prompt, and verify the correction with seven local SQLite checks.

Calling broken_enqueue twice with the exact same request key and payload produces two independent rows in the unkeyed table. In our verified baseline test, executing broken_enqueue with key 'a' and prompt 'one', followed immediately by an identical call with key 'a' and prompt 'one', leaves the storage table holding two records. The database does not know the two calls represent the exact same logical submission because the schema assigns no unique identity to the operation. This failure happens entirely at the point of ingestion before any consumer process reads the table. It is not an issue of worker locks, consumer leases, or competing threads claiming pending jobs from a backlog. The defect exists because the entry boundary treats every invocation as an independent demand for work, recording identical duplicate rows long before any execution step takes place.
A repeated submission is a request identity problem
When duplicate processing occurs in asynchronous systems, engineering teams frequently direct their attention toward worker concurrency and queue consumption. They examine distributed locks, transaction isolation levels during job pickup, or state machine transitions from pending to running. Those mechanisms are essential during the dispatch phase, but they cannot fix an ingestion layer that actively duplicates requests. If two identical rows are written to a database, downstream workers behave correctly when they pick up and execute both. From the worker perspective, two distinct database records represent two distinct tasks that each demand completion. Preventing redundant processing must therefore begin at the storage ingestion boundary, ensuring that the database records an incoming submission exactly once regardless of how many times a client transmits it.
The request needs an identity that can survive a retry. In this example the caller supplies that key, rather than enqueue generating a new value each time it runs. An application can choose other protocols for establishing identity, but it must make that contract explicit. After a timeout, the caller may need to reconcile the earlier operation before deciding to retry. If it does retry enqueue, it should retain the same key for the same intent. Generating another key would make the two invocations distinct according to this schema, even if their prompt text matched. The seven local tests did not simulate a network timeout or a client retry protocol.
Supplying a persistent key shifts the burden of request identity onto relational constraints. A table that tracks caller-supplied keys can determine whether an incoming transmission is a legitimate repeat of an earlier operation or a brand-new task. However, identity is not solely a question of key presence; it is an explicit contract binding that specific key to its exact payload. If a client transmits a request key with one prompt and later sends the same key with an altered prompt, the system cannot safely treat the second call as an innocuous replay. A silent acceptance would mask caller-side programming errors or race conditions, while overwriting the existing row would mutate an in-flight operation. A robust identity contract must confirm that the payload associated with a repeated key matches the original submission exactly.
Enforcing identity at the SQLite storage level provides a clear, verifiable boundary, but its operational scope must remain strictly defined. Enforcing uniqueness on a request key inside a local requests table prevents duplicate queue rows within that database. It guarantees that repeated calls with an identical key and prompt return cleanly without inserting additional work items. It does not, however, eliminate duplicate external side effects if an application contacts third-party services or triggers remote provider calls before committing to SQLite. Furthermore, it cannot prevent duplication if the caller mistakenly generates a new key for every retry attempt. Treating request identity as a database contract isolates the ingestion boundary, creating a dependable foundation before background workers ever interact with the data.
Run the duplicate-row baseline and correction
SQLite documents ON CONFLICT as part of UPSERT for handling uniqueness constraints. The example targets the request_key primary key explicitly. SQLite UPSERT documentation
In an ingestion pipeline that lacks request tracking, duplicate records accumulate rapidly because the database treats every incoming call as a novel instruction. The baseline function broken_enqueue demonstrates this failure mode in its simplest form. When given an open connection, a request key of 'a', and a prompt value of 'one', it executes an unconstrained insert statement against an unkeyed table. Repeating the exact same function call with the identical key and prompt immediately creates a second row. In the verified test suite, two sequential calls to broken_enqueue resulted in a count of two records in the unkeyed table. The storage layer had no mechanism to recognize that the second call was a retransmission of an operation that had already succeeded. If this pattern runs in an active queue, an upstream client retrying after a brief network hesitation will silently double the workload, generating redundant processing jobs and downstream resource consumption.
In recoverable local publishing, a retry decision depends on what the earlier operation may already have committed. A stable local enqueue identity is one boundary in that workflow; it does not by itself reconcile a remote creation whose response was lost.
Preventing duplicate queue entries requires transforming the database interaction from an unconstrained append into an idempotent contract. The schema initialization defined in setup establishes this foundation by creating the requests table with request_key configured as a primary key alongside a non-nullable text prompt. By making request_key the primary key, SQLite builds an underlying unique index that rejects duplicate keys at the relational boundary. However, relying on a naive insert that crashes on constraint violations forces the application layer to catch and inspect low-level database errors, which complicates error handling and risks masking unrelated failures. The corrected enqueue function pairs the relational constraint with SQLite's UPSERT syntax, using an explicit conflict target to dictate how the database must resolve duplicate keys.
The enqueue function begins by validating input types before interacting with the database connection. It ensures that request_key is a non-empty string and that prompt is a string, raising a standard ValueError if either argument fails inspection. Once input validation passes, the function opens Python's connection context manager. Within this transaction block, it executes an insert statement using parameter substitution: INSERT INTO requests VALUES (?, ?) ON CONFLICT(request_key) DO NOTHING. Parameter substitution prevents malformed input from disrupting query evaluation. The ON CONFLICT clause targets the request_key column directly. When a new key arrives, SQLite inserts the row normally. When an existing key arrives, the DO NOTHING instruction tells the engine to bypass the insert entirely rather than raising an integrity exception.
Absorbing the conflict without an exception is only half of the necessary identity guarantee. An application cannot assume that every retransmitted key carries the same payload as the initial call. To complete the contract, enqueue immediately queries the requests table within the same transaction to inspect the stored prompt associated with the request key. If the returned prompt differs from the prompt supplied in the current function call, the function raises a ValueError indicating that the key already belongs to a different prompt. Because this check executes inside the connection context manager, any caller attempting to repurpose an existing key with altered data is immediately rejected, leaving the original stored record completely intact and unmodified.
When the incoming prompt matches the stored prompt, enqueue completes without altering the database and returns the validated request key back to the caller. Running enqueue twice with key 'a' and prompt 'one' results in exactly one row in the requests table, proving that the replayed call is fully idempotent. The caller receives the expected key confirmation, no duplicate work row enters the queue, and no application-level error is raised. By establishing a primary key constraint, employing ON CONFLICT DO NOTHING, and verifying payload consistency in a single transactional pass, the corrected implementation replaces the uncontrolled accumulation of broken_enqueue with a predictable ingestion boundary.
request_queue.py
import sqlite3
def setup(connection):
connection.execute('CREATE TABLE IF NOT EXISTS requests (request_key TEXT PRIMARY KEY, prompt TEXT NOT NULL)')
def broken_enqueue(connection, request_key, prompt):
connection.execute('CREATE TABLE IF NOT EXISTS unkeyed (request_key TEXT, prompt TEXT)')
connection.execute('INSERT INTO unkeyed VALUES (?, ?)', (request_key, prompt))
def enqueue(connection, request_key, prompt):
if not isinstance(request_key, str) or not request_key.strip():
raise ValueError('Provide a nonempty request key')
if not isinstance(prompt, str):
raise ValueError('Prompt must be a string')
with connection:
connection.execute('INSERT INTO requests VALUES (?, ?) ON CONFLICT(request_key) DO NOTHING', (request_key, prompt))
stored = connection.execute('SELECT prompt FROM requests WHERE request_key=?', (request_key,)).fetchone()[0]
if stored != prompt:
raise ValueError('The key already belongs to a different prompt')
return request_key
Recorded local check: Seven local single-connection SQLite in-memory tests. Repeated enqueue, key conflict and parameterized strings only; no concurrent processes, file-backed crash recovery, network response loss, provider request, worker claim or exactly-once external effect was tested.; environment: Python3.12.3 on Windows; SQLite3.45.1
Bind one key to one payload
A resilient idempotency mechanism does not simply absorb duplicate network calls; it verifies that a repeated transmission represents the exact same operation as the original. A naive enqueue routine often relies solely on inserting with conflict suppression, assuming that any primary key collision represents an identical request. Under that unverified approach, if a client accidentally reuses an active key while transmitting a modified payload, the database silently drops the second write while returning an apparent success status. The caller proceeds under the false assumption that the updated parameters entered the queue, while the downstream worker eventually processes the stale original data. Treating collisions as silent successes without verifying the underlying arguments introduces invisible state corruption across distributed systems. Binding the identity key directly to its payload prevents this drift by establishing an explicit contract: a key is not merely an arbitrary collision filter, but an identifier for one specific request payload within the chosen retention policy.
The corrected function inserts with ON CONFLICT(request_key) DO NOTHING, then reads the stored prompt and compares it with the incoming string. Equality means the key and prompt satisfy this local replay contract; it does not authenticate a caller or prove that two network attempts came from the same user. A changed prompt raises ValueError. The local fixture confirms that the earlier prompt remains one when a later call tries two under the same key. Treat this as a conflict to investigate rather than automatically retrying with altered data. Authentication and ownership checks belong outside this function and were not implemented or tested here.
This design draws a clean boundary between request identity and payload deduplication. The enqueue function deliberately allows distinct keys to carry identical prompts without triggering conflicts. Calling the function with key 'a' and prompt 'one' followed by key 'b' and prompt 'one' generates two discrete rows in the requests table. This behavior is intentional. Two separate users or workflows may genuinely issue identical prompts to a queue, and each of those distinct requests deserves its own lifecycle, execution path, and tracking identifier. The idempotency key exists specifically to guarantee that an individual caller retry does not multiply a single logical action into duplicate work items. Deciding whether identical prompts across different requests should share work is the responsibility of an application-level result cache or downstream batching system, not the ingestion queue identity layer. Conflating prompt uniqueness with request uniqueness would incorrectly prevent legitimate concurrent operations that happen to share input text.
Enforcing payload binding at the database boundary also highlights several operational considerations regarding input validation and data formats. In the verified test implementation, basic guards verify that the request key is a non-empty string and the prompt is of type string before touching the database connection. The implementation checks that stripping whitespace from the key leaves a non-empty value, but it does not alter or normalize whitespace within valid keys. In production architectures where payloads extend beyond a single string to encompass complex dictionaries or nested configuration options, serialization consistency becomes critical. If an application serializes JSON payloads with unstable key orderings or varying whitespace before storing them, a valid retry might fail equality checks against the stored record, raising a false-positive payload conflict. Engineering teams adopting this pattern must standardize how request payloads are hashed or normalized before evaluating equality against existing queue records. Rejecting malformed inputs early and comparing deterministic representations keeps the idempotency boundary predictable and resilient against ambiguous client payloads.
Read the seven actual SQLite checks
Seven unittest methods ran under Python3.12.3 and SQLite3.45.1. Each starts with a fresh in-memory connection and calls setup; teardown closes that connection. The assertions cover the duplicate-row baseline, a repeated key and prompt, different keys, a changed prompt, a blank key, a nonstring prompt and one SQL-like string retained as data. These are deterministic synthetic checks, not a load test or security audit. In particular, different keys in a fixture do not represent authenticated different callers. Retain the test names and inputs when explaining the result so the scope remains reproducible instead of turning the count into a broader reliability claim.
The first three tests establish the operational baseline and core queue mechanics. The test named test_broken_repeats executes the broken_enqueue helper twice using the same key and payload, subsequently asserting that the unkeyed table contains two separate rows. This test documents the exact failure mode that unkeyed ingestion introduces into an application. Directly countering that failure, test_same_request_once invokes the corrected enqueue function twice using the key a and the prompt text one. The test queries the total count in the requests table and confirms that only one record exists, verifying that the ON CONFLICT clause silently ignored the second insertion attempt. Immediately following this, test_distinct_keys enqueues two separate entries sharing identical prompt text under keys a and b. The test asserts that the resulting row count is two, confirming that deduplication binds strictly to the caller-provided key rather than treating duplicate prompt text as a conflict.
The fourth and fifth tests inspect how the enqueue implementation protects the contract against invalid caller behavior. In test_changed_payload_conflicts, the test first enqueues key a with the prompt one. It then attempts to reuse key a with the prompt two, wrapping the call in an assertion that expects a ValueError. After catching the exception, the test queries the stored prompt for key a directly from SQLite and confirms that the value remains one. This step proves two critical operational guarantees: reusing an active key with conflicting instructions fails immediately, and the failed submission does not overwrite or corrupt the initial record. In test_blank_key, the test passes a single whitespace character as the request key. The implementation validates the string prior to issuing any SQL command, raising a ValueError and ensuring that the requests table retains a row count of zero rather than storing untrackable blank identifiers.
The remaining two checks evaluate type safety and query parameterization at the database boundary. In test_prompt_type, the test attempts to pass None in place of a string prompt. The validation logic intercepts the invalid object, raises a ValueError, and prevents SQLite from encountering unhandled type conversions. Finally, test_sql_like_text_is_data provides an injection test payload containing single quotes, semicolons, and a simulated DROP TABLE statement. The enqueue function passes this string through standard parameter substitution placeholders into the database. The test reads the prompt back from SQLite and asserts that the string returned matches the exact literal text supplied, demonstrating that the table schema remains intact and that dangerous input characters are safely handled as inert string data.
Recognizing the strict limits of these seven local tests is essential when planning production usage. The test suite verifies parameter handling, conflict detection, and row stability exclusively within a single synchronous connection running in volatile memory. It does not test or verify concurrent write operations across multiple threads, multi-process file locking, crash recovery after process termination, or network failures between clients and an ingestion server. Furthermore, these tests do not touch worker claim processes, job execution states, or downstream external provider interactions. The verified suite proves only that the proposed SQLite schema and enqueue logic maintain deterministic request identity and reject payload mutations under isolated single-connection conditions.
Keep database commitment separate from remote work
Recording the request and executing remote work are separate application decisions. The tested enqueue function only changes a local table and returns a key. Its tests use an in-memory database, so they do not demonstrate survival across a process restart. There is no provider call, billing action, worker state or result field in the example. If a reader extends it into a service, define what a successful enqueue response acknowledges: a committed database record under the chosen storage policy, not a finished generation. Keeping that distinction visible prevents a local row-count assertion from being presented as evidence that an external action occurred exactly once.
A frequent architectural misstep involves initiating remote network calls inside the same execution block or database transaction that writes the request row. If an application handler receives an enqueue request, inserts the row into SQLite, and immediately triggers an HTTP call to a remote provider before exiting the transaction, a network timeout leaves the system in an ambiguous state. Should the remote endpoint accept the work and begin execution while the client-facing HTTP connection times out, rolling back the local transaction leaves the database with no record of the request even though the external provider is actively processing it. Conversely, committing the transaction after an external call has started risks abandoning the local record if the application process crashes mid-flight.
Python sqlite3 supports parameter substitution for query values and a connection context manager for transaction handling; the context manager does not close the connection. Python3.12 sqlite3 reference
The single-connection tests establish the returned values and stored rows for the supplied fixtures. They do not establish distributed exactly-once execution. Separating a remote call from the enqueue transaction can reduce coupling, but it does not guarantee that ingestion stays fast or available under every external failure. A real service still needs bounded resources, connection ownership, failure handling and its own measurements. Treat these as proposed integration work. The value of the small example is that it gives request identity one inspectable responsibility rather than hiding remote execution inside a function whose apparent success means several different things.
A possible extension is to acknowledge an enqueue after the request record commits, then let a separate worker manage execution. That extension would need new lifecycle fields, a claim protocol and tests; the supplied requests table has only a key and a prompt and cannot track pending, running or completed work. Define those states before writing a worker that scans rows, otherwise every scan may find the same requests again. A response should distinguish acceptance from completion. None of these endpoint or worker behaviors was implemented by the seven tests, and a reader must not infer them from enqueue returning the original key.
After a remote timeout, a worker should reconcile the provider operation before deciding whether another creation is appropriate. A retry policy must distinguish a definite rejection from a response that may have been lost after acceptance. The local request row alone cannot answer that question. A proposed extension could retain remote operation identifiers and observed outcomes, but this schema does not contain them. If reconciliation is unavailable, preserve the uncertain state rather than claiming the database makes another external request safe. This keeps the boundary between local identity and remote side effects explicit without adding an untested provider-recovery mechanism to the example.
Add application lifecycle rules before deployment
The deterministic behavior demonstrated in the verified test suite depends entirely on caller discipline regarding request identity. If an upstream service or client application generates a new identifier on every transmission attempt, the SQLite idempotency layer cannot recognize the operation as an existing transaction. The client must construct a stable identifier, such as a universally unique identifier generated at the exact moment of initial user intent, and preserve that exact token across all subsequent transport attempts. Reusing that token allows gateway timeouts, proxy drops, and network disconnects to be resolved transparently by the existing database row rather than triggering redundant enqueue operations.
In multi-user or multi-tenant deployments, storing caller-supplied keys in an unpartitioned primary key column creates an unacceptable risk of cross-account collisions. Two independent callers could generate an identical string key by coincidence or deliberate probing, causing one tenant to trigger conflict errors against records owned by another. Operational schemas mitigate this by implementing composite primary keys that bind an authenticated account or organization identifier directly to the incoming request key. While the tested implementation validates the conflict mechanism on a single column, production environments must partition identity boundaries so that key validation remains strictly scoped to individual caller domains.
Unbounded growth of the requests table will eventually degrade index lookups and consume unnecessary storage. Deploying an idempotency mechanism requires an explicit retention policy defining how long request keys remain active. A service might retain keys for twenty-four hours or seven days, ensuring that any client retry received within that window encounters the original record and avoids duplicate enqueue entries. Once records cross the expiration threshold, automated background cleanup routines must remove them. Engineering teams must understand that deleting an expired key resets identity: a severely delayed retry arriving after a record has been pruned will be processed as a brand-new submission.
The verified test suite validates request consistency using direct string equality against a single prompt column. Production pipelines, however, frequently accept structured payloads containing model parameters, system directives, temperature values, and nested metadata. Comparing structured objects through raw string equality is fragile because variations in key ordering or whitespace can cause identical requests to appear different. To adapt the verified pattern for complex payloads, applications should normalize incoming data into a canonical representation, compute a deterministic cryptographic digest of that normalized structure, and store the digest in the database. The validation query then compares stored digests rather than unformatted strings to detect payload drift.
The tests use a fresh connection dedicated to the example. A reader integrating enqueue must define connection and transaction ownership: with connection can commit or roll back other pending changes on that connection, and it does not close it. The example should not be dropped into an unrelated active transaction without reviewing that behavior. A file-backed or multiprocess deployment also needs its own locking, timeout, storage and recovery checks. Write-ahead logging is an option to evaluate under those requirements, not a setting the seven tests establish as universally necessary or sufficient. Preserve the tested single-connection result and label such deployment work as unverified until it is implemented and checked.
Prepared with AI assistance and automated editorial reviews. The original code was checked locally against synthetic fixtures; no provider call, real video generation or production workload was tested.



