Prefect automations do not fire reliably

[alastairtree]

The automation triggers for the sharepoint upload and the i-alirt check do not work so we have to run them with crons instead.

Some combo of the below to fix it

• Check the server logs - any warnings?
• craete a minimal repro, seems to be the eway we do deployments and updates as the first time the deploy works and the second time on it does not
• look for a workaround - deleting the automation and recreating it? Restarting the server?
• File a bug with prefect

[alastairtree]

Found this is the prefect logs:

 13:56:32.170 | ERROR   | prefect.server.utilities.messaging.memory - Failed in consume_loop
Traceback (most recent call last):
  File "/usr/local/lib/python3.13/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 550, in _prepare_and_execute
    self._rows = deque(await prepared_stmt.fetch(*parameters))
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/asyncpg/prepared_stmt.py", line 176, in fetch
    data = await self.__bind_execute(args, 0, timeout)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/asyncpg/prepared_stmt.py", line 267, in __bind_execute
    data, status, _ = await self.__do_execute(
                      ^^^^^^^^^^^^^^^^^^^^^^^^
        lambda protocol: protocol.bind_execute(
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
            self._state, args, '', limit, True, timeout))
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.13/site-packages/asyncpg/prepared_stmt.py", line 256, in __do_execute
    return await executor(protocol)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 206, in bind_execute
asyncpg.exceptions.ForeignKeyViolationError: insert or update on table "automation_bucket" violates foreign key constraint "fk_automation_bucket__automation_id__automation"
DETAIL:  Key (automation_id)=(5e34ad3c-d021-41da-b6a1-ad7e3ac67447) is not present in table "automation".

...

sqlalchemy.exc.IntegrityError: (sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class 'asyncpg.exceptions.ForeignKeyViolationError'>: insert or update on table "automation_bucket" violates foreign key constraint "fk_automation_bucket__automation_id__automation"
DETAIL:  Key (automation_id)=(5e34ad3c-d021-41da-b6a1-ad7e3ac67447) is not present in table "automation".
[SQL: INSERT INTO automation_bucket (automation_id, trigger_id, bucketing_key, last_event, start, "end", count, last_operation, id, created, updated) VALUES ($1::UUID, $2::UUID, $3::JSONB, $4, $5::TIMESTAMP WITH TIME ZONE, $6::TIMESTAMP WITH TIME ZONE, $7::INTEGER, $8::VARCHAR, $9::UUID, $10::TIMESTAMP WITH TIME ZONE, $11::TIMESTAMP WITH TIME ZONE) ON CONFLICT (automation_id, trigger_id, bucketing_key) DO UPDATE SET last_event = $12, count = automation_bucket.count]
[parameters: ('5e34ad3c-d021-41da-b6a1-ad7e3ac67447', 'af9f49fe-e4b8-43b9-803f-a5555799e6f1', '[]', '{"occurred": "2025-11-03T13:56:32.016956Z", "event": "prefect.flow-run.Completed", "resource": {"prefect.resource.id": "prefect.flow-run.019a4929-b9a ... (1290 characters truncated) ... "type": "COMPLETED", "name": "Completed"}}, "id": "019a4a01-5290-705d-8993-493323156e63", "follows": null, "received": "2025-11-03T13:56:32.061408Z"}', datetime.datetime(2025, 11, 3, 13, 41, 32, 16956, tzinfo=TzInfo(0)), datetime.datetime(2025, 11, 3, 13, 56, 32, 16956, tzinfo=TzInfo(0)), 0, 'ensure_bucket[insert]', '61f3abb7-bb06-4458-933d-ee4a65c0b3f7', datetime.datetime(2025, 11, 3, 13, 56, 32, 167930, tzinfo=zoneinfo.ZoneInfo(key='UTC')), datetime.datetime(2025, 11, 3, 13, 56, 32, 167939, tzinfo=zoneinfo.ZoneInfo(key='UTC')), '{"occurred": "2025-11-03T13:56:32.016956Z", "event": "prefect.flow-run.Completed", "resource": {"prefect.resource.id": "prefect.flow-run.019a4929-b9a ... (1290 characters truncated) ... "type": "COMPLETED", "name": "Completed"}}, "id": "019a4a01-5290-705d-8993-493323156e63", "follows": null, "received": "2025-11-03T13:56:32.061408Z"}')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
13:56:32.179 | WARNING | prefect.server.utilities.messaging.memory - Message failed after 4 retries and will be moved to the dead letter queue 

[alastairtree]

Have created PrefectHQ/prefect#19332

[alastairtree]

We now have a workaround - prefect server restart:

# rolling restart
docker service update --force --update-parallelism 1 --update-delay 30s mag-lab-data-platform_prefect

# OR stop and start
docker service update --replicas 0 mag-lab-data-platform_prefect
docker service update --replicas 1 mag-lab-data-platform_prefect