Ghost CMS and Knex.js: Interoperability of Write Retries Without Duplicate Records

Question

Ghost CMS & Knex.js: Handling Retries Without Duplicate Writes
The goal is to ensure that a transient failure during a Ghost write operation does not create duplicate content when retried. Ghost relies on Knex.js transaction rollback and unique slug indexes, turning a repeated insert into a 409 Conflict instead of a duplicate row.
An open design decision is whether Ghost should generate and store an idempotency key (e.g., a UUID) for each write request, allowing safe retries without relying on database‑level uniqueness. This raises questions about where to persist the key, how to validate it against existing records, and what performance or compatibility implications exist across MySQL, PostgreSQL, and SQLite adapters.

Should Ghost generate an idempotency key for each write operation?
How should the key be stored and validated to prevent duplicates?
What impact would this approach have on performance and adapter compatibility?

Zoha · Answer

Answer the Question First
p>Ghost already protects against duplicate content by wrapping each Admin write in a Knex.js transaction and relying on database constraints (e.g., posts.slug). When a transient failure occurs and the client retries, the second insert will hit the unique index and return a 409 Conflict instead of creating a duplicate row. Adding an application-level idempotency key is optional and only necessary if you want to avoid the brief conflict response or if you need to support idempotent updates.
When to Generate an Idempotency Key

Client--side retries that send the exact same request payload.
Operations that modify multiple tables or require a deterministic response.
Environments where database constraints might not be sufficient (e.g., SQLite in a multi-process setup).

How to Store and Validate the Key

Create a lightweight idempotency_keys table with columns:

key: UUID, unique.
entity_type: e.g., post.
entity_id: nullable, used for updates.
response_status: HTTP status returned on first success.
response_body: JSON of the first successful response.
created_at: timestamp.

In the same Knex transaction that performs the write, first query the table for the supplied key. If found, immediately return the stored response_status and response_body.
If not found, proceed with the write, then insert a row into idempotency_keys with the key and the outcome.

Performance & Adapter Compatibility

MySQL & PostgreSQL both support UNIQUE constraints and transactions, so the extra index lookup is negligible for typical traffic.
SQLite supports unique indexes but has weaker concurrency; transactional isolation and writer behavior differ.

To provide a specific recommendation on transaction isolation and performance overhead for the idempotency table, which database adapter (MySQL, PostgreSQL, or SQLite) are you using?

Ghost CMS and Knex.js: Interoperability of Write Retries Without Duplicate Records

Ghost CMS & Knex.js: Handling Retries Without Duplicate Writes

1 answer

Answer the Question First

When to Generate an Idempotency Key

How to Store and Validate the Key

Performance & Adapter Compatibility

Your perspective belongs here.

0 question comments