Query icon
OutputValues icon
Log icon
If icon
SlackIncomingWebhook icon
Pause icon
Loop icon
Fail icon
Schedule icon

SurrealDB Orphaned Graph Edge Cleanup Guard

Catch SurrealDB RELATE edges that point at a missing record, alert Slack, then delete exactly those edge ids behind an approval and re-verify.

Categories
DataInfrastructure

RELATE a->follows->b succeeds in SurrealDB even when a or b does not exist. There is no referential integrity on a graph edge. A typo in an id, an import that loads edges before their vertices or a restore that drops a vertex table all leave edge records that still carry a target id while the record on the other end is gone. The edge is not empty, so a plain SELECT * FROM follows shows nothing wrong. Graph traversals then return half-built paths and counts drift from reality.

This blueprint tests link resolution rather than row existence. An edge is orphaned when in.id or out.id resolves to NONE, which is exactly the case where the linked record is not there. It reports the orphaned id set, alerts Slack, then deletes those ids one at a time behind an approval and re-selects the set to prove the table is clean.

This blueprint was created by zkasuran.

Findings

Finding Rule Fix
Orphaned edge in.id IS NONE or out.id IS NONE, so one end points at a record that is gone DELETE the exact edge id behind an approval, then re-select
Clean edge both in and out resolve to a record None

Safety

  • Detection tests link resolution (in.id IS NONE OR out.id IS NONE), not whether the edge row exists. A row check would pass every edge, because the edge row is always present. It is the link target that is missing.
  • A bare DELETE follows with no filter wipes the whole edge table. This flow never issues one. Remediation loops over the id set from detection and each statement is DELETE <one id> WHERE in.id IS NONE OR out.id IS NONE, so it names a single record and repeats the guard. An edge that was repaired between detection and remediation is left alone.
  • The DELETE needs auto_remediate true, dry_run false, a non-empty dangling set, then a human answering the Pause. Four conditions, all of them, before an edge is removed.
  • The loop is serial (concurrencyLimit: 1) because concurrent writes to one table race.
  • After the deletes, reselect reads the dangling set again. If any edge still points at a missing record the run fails, so a cleanup that did not land is never reported as success.
  • max_edges caps how many edges one run returns and deletes. A run that reaches the cap says so and asks to be re-run, rather than loading an unbounded result.

How it works

  1. detect (io.kestra.plugin.surrealdb.Query, fetchType: FETCH) selects the orphaned edges with WHERE in.id IS NONE OR out.id IS NONE, up to max_edges. It returns each edge id as a canonical string with type::string, plus which end is missing. size is the orphan count.
  2. count_edges reads the total number of edges in the table for context.
  3. assess (io.kestra.plugin.core.output.OutputValues) derives the id set, the counts, the capped flag and the alert text once, so every branch reads the same values.
  4. report logs the summary. route (io.kestra.plugin.core.flow.If) stops on a clean table and continues on a finding.
  5. On a finding, alert_findings posts the orphan set to Slack and planned logs what a remediation run would delete, on every run including a dry one.
  6. remediate_gate opens only with auto_remediate true, dry_run false and a non-empty set. hold (io.kestra.plugin.core.flow.Pause) waits for an answer. On approval remediate_delete (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) deletes each id with its guard, reselect reads the dangling set again and verify fails the run if any edge still dangles. Without approval declined records the decision and nothing changes.
  7. errors posts to Slack when the flow fails.

What you get

  • The exact id set of edges that point at a record that is gone, not a vague count.
  • Which end is missing for each edge, so you can trace the write path that created it.
  • A Slack alert the moment a scheduled run finds an orphan.
  • A bounded cleanup that deletes only the detected ids, each behind its own guard, with a re-select that fails the run when the change did not land.

Who it's for

  • Teams using SurrealDB graph edges (RELATE) where application code or imports can create an edge whose target record does not exist yet.
  • Platform engineers who have seen graph traversals return half-built paths after a restore or a bulk delete.
  • Anyone who wants referential-integrity cleanup on a store that does not enforce it.

Why orchestrate this with Kestra

SurrealDB has no scheduler, no approval step and no alerting of its own. Kestra runs the check on a cadence, holds the destructive half behind auto_remediate, dry_run and a Pause with the decision recorded on the execution, posts findings to Slack and keeps the read, the delete and the re-read in one auditable run. The id set from detection is carried straight into the delete loop, so remediation acts on what was found and nothing wider.

Tested end to end

Verified on Kestra 2.0.5 OSS against a SurrealDB 3.3.3 container on the same Docker network, with a local HTTP sink standing in for Slack. Every count below was read back from SurrealDB's own surreal sql, not only from the Kestra log. The follows table held one clean edge (person:alice to person:carol) plus, in the degraded runs, two orphans: one from RELATE person:alice->follows->person:ghost where person:ghost was never created, one from CREATE follows:manual SET in=person:nobody where person:nobody was never created.

Run Execution Inputs Result
1 1Buf29LevKA5j7wLhMj69H defaults, clean table 0 orphaned of 1 edge. clean logs, no Slack finding
2 2PTDFFnSLXesva53Rv8bKJ defaults, 2 orphans seeded 2 orphaned of 3. Slack finding posted, planned logged. CLI after: still 3 edges, 2 orphaned
3 5M1cRExCWJKDB5ZXjNNC2z auto_remediate: true, dry_run: true gate held, nothing deleted. CLI after: 2 orphaned
4 2tb5rfL4msdRFcDVGb9Mu0 auto_remediate: true, dry_run: false, resumed approved: true 2 edges deleted, reselect found 0, SUCCESS. CLI before 3 edges with 2 orphaned, after 1 edge with 0 orphaned, the clean edge survived
5 7VzkAkw2lzsCncRJPsvtIC the same, resumed approved: false declined, nothing deleted. CLI after: 2 orphaned
6 1PvCug8JC9y3jR8JaHn6Vr unreachable host detect failed, errors posted to Slack, FAILED
7 2yDy87dbRT7VGBRJtuAqM6 max_edges: 1, resumed approved: true capped at 1, deleted 1 of 2, reselect found 1 remaining, verify failed the run, errors posted. CLI before 2 orphaned, after 1 orphaned with the clean edge still present

Run 1 against run 2 isolates that the detector catches a real orphan. Nothing changed between them except two edges pointing at records that do not exist. The report moved from 0 findings to 2. Run 7 proves the re-verify can fail. One orphan was deleted by exact id while the other orphan and the clean edge were left untouched, so reselect still found an orphan and the run failed rather than reporting a clean table. That the other orphan survived also proves the delete acts on one id at a time, never a blanket DELETE follows.

Prerequisites

  • A SurrealDB instance reachable from the Kestra workers, with a user allowed to SELECT and DELETE on the edge table.
  • The plugin-surrealdb plugin, bundled with Kestra.
  • An edge table built with RELATE, so its records carry in and out links.

Secrets

  • SLACK_WEBHOOK_URL: used by alert_findings and the errors block. In Kestra OSS, provide it as the base64-encoded environment variable SECRET_SLACK_WEBHOOK_URL.
  • SURREALDB_PASSWORD: the database password, the default for the password input.

Inputs

Input Default Purpose
host surrealdb The SurrealDB host
namespace, database test, test Namespace and database to check
username, password root, secret Credentials
edge_table follows RELATE edge table, word chars
max_edges 1000 Ceiling on edges returned and deleted per run
approval_timeout PT2H How long the pause waits
auto_remediate false Allow the DELETE
dry_run true Must be false for a change to apply

The port (8000) and TLS (off) are set on the Query tasks. For a different port or a TLS endpoint, change port and useTls on each task.

Quick start

  1. Add the SLACK_WEBHOOK_URL and SURREALDB_PASSWORD secrets.
  2. Import the flow, set host, namespace, database and edge_table, then run it. A clean table logs and stops. An orphan sends a Slack alert and logs the id set.
  3. To clean up, set auto_remediate: true and dry_run: false, run it, then answer the pause.
  4. Read the reselect step. The run fails if any edge still points at a missing record.
  5. Enable the daily trigger, which always runs in safe mode.

How to extend

  • Guard several edge tables by wrapping the tasks in a Loop over a table list. You can also read the edge tables from INFO FOR DB first.
  • Alert only above a threshold, so a handful of known orphans stays quiet and a spike pages.
  • Replace the Pause with a Slack approval. Or drop it for a table you are sure about.
  • Add a second detection for edges whose in and out resolve but violate a schema rule.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.