I just finished migrating my Flask app from Postgres to DuckDB, and pretty much everything seems to be working perfectly. There is one very odd issue though that I’ve been banging my head against the wall on for hours and can’t seem to make heads or tails of it.

In one of my Flask routes there are various things that happen pertaining to stuff a user is uploading. Over the course of the processing the files go through the app makes updates to a few different tables in the db. Everything looks 100% normal and correct in the app, but when I quit Flask, the final three table updates simply disappear. The given Flask route is really long and complicated, but everything works perfectly as it did before up until a certain point…

   ** whole bunch of stuff happens above here **
    
    try:    
        db.session.commit()
    except Exception as e:
        db.session.rollback()

    
    db.session.add(new_audit_history_entry)
    db.session.add(new_audit_log_entry)


    try:
        db.session.commit()
    except Exception as e:
        db.session.rollback()

    update_task = TaskQueue.query.get(task_id)
    update_task.completed_at = datetime.now()
    update_task.status = 'Complete'

    try:
        db.session.commit()
    except Exception as e:
        db.session.rollback()
    finally:
        audit_history = AuditHistory.query.all()
        audit_logs = AuditLog.query.all()
        tasks = TaskQueue.query.all()
        db.session.flush()
        db.session.close()

        return jsonify({'message': 'Files saved and validated!', 'This many files: ': len(files)})

I’m not getting any errors whatsoever, and everything looks normal in the app — I can see everything I would expect to see. All is well. But then if I quit Flask (ctl+c in the CLI) everything from db.session.add(new_audit_history_entry) onwards is simply gone. The entries in audit_history and audit_log tables disappear, and the TaskQueue table has reverted to it’s state further up in the route the last time it was updated (before the final commit to that table seen above).

The only hint I’ve seen is when I start Flask back up I get the following:

Exception in WAL playback: Violates foreign key constraint because key “auditid: 95693563-b229-40d7-9b12-5c4447bdb601” does not exist in the referenced table

So it seems that those final commits are not being fully saved? They’re only being saved in Duck’s write ahead logic or something? I’ve tried every possible combination of commits and flushes to force these final updates to the db and no dice.

To be clear I’m not interrupting the app in the middle of writes or anything, I’m quitting and relaunching it way after these writes would have finished. Like I’m convinced that I could run the app for a day and none of these particular writes would persist after quitting. Also, I am handling the keyboard interrupt elegantly in my app, including a final db commit for good measure.

What am I missing? Is there something special about duck that I need to do?