No duplicates in auto-posting: a key, ON CONFLICT and a queue

Auto-posting and integrations sooner or later start sending repeats: a workflow ran twice, a service retried a request, an export arrived with the same data. The most reliable protection lives in the database, not in the workflow itself.

Where duplicates come from

• A repeated run on a schedule or by hand.
• Repeated webhook delivery: the sender got no reply and sent the request again.
• Overlapping sources: the same offer arrives from two places.
• A failure in the middle: the message went out but the mark was not saved, so on a retry it goes out again.

Step 1: a unique offer key

Come up with a stable key by which two records count as the same offer. For flights it is route, date and price range, for listings address and price, for leads the request ID.

CREATE TABLE offers (
    id           bigserial PRIMARY KEY,
    dedup_key    text NOT NULL UNIQUE,
    payload      jsonb NOT NULL,
    status       text NOT NULL DEFAULT 'new',   -- new, sending, sent, failed
    created_at   timestamptz NOT NULL DEFAULT now(),
    published_at timestamptz
);

The UNIQUE constraint will not let the same thing in twice, even if two workflows run at the same time.

Step 2: inserting without duplicates

INSERT INTO offers (dedup_key, payload)
VALUES ($1, $2)
ON CONFLICT (dedup_key) DO NOTHING
RETURNING id;

If the record is new the query returns its id. If the key already exists the result is empty and you simply skip the offer. This works without a separate “does it exist” check, which is itself prone to races.

Step 3: the send queue

Do the sending as a separate step that takes records from the queue. To keep two workers from taking the same record use a row lock that skips busy rows:

UPDATE offers
SET status = 'sending'
WHERE id = (
    SELECT id FROM offers
    WHERE status = 'new'
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
RETURNING id, payload;

FOR UPDATE SKIP LOCKED locks the chosen row and skips those already taken by another worker. Each worker gets its own record.

Step 4: statuses and retries

After sending, mark the result:

UPDATE offers SET status = 'sent', published_at = now() WHERE id = $1;
-- on error:
UPDATE offers SET status = 'failed' WHERE id = $1;

Now any step can be safely repeated:

• records with the failed status can be sent again;
• records stuck in sending for more than a few minutes are returned to new by a separate job (so you do not lose messages when a worker crashes).

Nuances

• A message may have gone out without the mark. If the process died between sending and updating the status, a retry sends the message again. For critical cases store the sent message ID and check before retrying. For channel posts a small chance of a repeat is usually acceptable.
• The key must be stable. Do not include fields that change on every export (the request time).
• Old records. Clean or archive the table on a schedule: the uniqueness constraint works while the record exists. If you need to remember a key longer, keep only keys.
• A pause between sends protects against Telegram limits.

How it is built into n8n

In an n8n workflow these are PostgreSQL nodes: an insert with ON CONFLICT, a queue select with a lock, sending to Telegram and a status update. The state lives in the database, so restarting the workflow breaks nothing. The general pipeline scheme is in Telegram auto-posting with n8n and PostgreSQL, and receiving data from a site in securing an n8n webhook. This approach is used in HotTrip and Da Nang House. Service: Content and social media.