pythonflask

How can I make a simple idempotent POST endpoint in a Flask micro-service?

I’m building a small internal micro-service in Python 3.12 / Flask 3.0. The service accepts POST /upload requests that insert a record into PostgreSQL.

Problem Mobile clients sometimes retry the request when the network is flaky, so I end up with duplicate rows:

@app.post("/upload")
def upload():
    payload = request.get_json()
    db.execute(
        "INSERT INTO photos (user_id, filename, uploaded_at) VALUES (%s, %s, NOW())",
        (payload["user_id"], payload["filename"]),
    )
    return jsonify({"status": "ok"}), 201

What I tried

  1. Added a UNIQUE (user_id, filename) constraint – works, but clients now get a raw SQL error on duplicate inserts.

  2. Wrapped the insert in ON CONFLICT DO NOTHING – avoids the error but I can’t tell whether the row was really inserted.

  3. Googled for “Flask idempotent POST” and found libraries like Flask-Idem, but they feel heavyweight for a single route.

Question: What’s the simplest, idiomatic way in Flask to make this endpoint idempotent so that:

  1. POSTing the same photo twice is harmless;

  2. the client still gets a clear 201 Created the first time and 200 OK for retries; and

  3. I don’t have to introduce extra infrastructure (Kafka, Redis, etc.)?

Solution

    1. Give the table a uniqueness guarantee so duplicates physically can’t happen.

    2. Use an UPSERT (INSERT … ON CONFLICT) with RETURNING so you know whether the row was really inserted.

    3. Map that to HTTP status codes.