Building a Cascade-Aware Delete System Across the Stack

Most admin panels I've worked with handle deletion the same way: a confirmation dialog that says "Are you sure?" and nothing else. You click yes, rows disappear, and if something downstream breaks, you find out later. I wanted something better for Dursley Donkey Coffee's admin dashboard — a system where the admin sees exactly what a delete will do before it happens.

The result is a two-part system. The Go backend recursively walks foreign key relationships and returns a full impact tree. The Next.js frontend renders that tree with a traffic-light severity indicator and expandable row previews. But building the first version revealed a more fundamental question: should every cascading relationship actually result in a delete?

The Problem

Dursley Donkey Coffee is a coffee ordering system I'm building for a friend's new business, with the longer-term goal of turning it into a white-label product for independent coffee shops. The backend is Go with SQLite, and the admin dashboard is Next.js.

The database has clear relationships: users have orders, orders have order items, and menu items are referenced by order items. My first implementation treated all of these the same way — if a parent row is deleted, its children are deleted too. Delete a user, delete their orders, delete those orders' items.

That was wrong. Orders are business records. They represent revenue, stock movement, and data a shop owner needs for tax returns. Deleting a user account shouldn't destroy the financial history associated with it. The relationship between users and orders isn't the same as the relationship between orders and order items. One is ownership that should be severed; the other is composition that should be destroyed.

This distinction — between data that should be de-linked and data that should be deleted — became the core of the system.

SQLite supports ON DELETE CASCADE in foreign key constraints, but that treats every relationship the same way: delete the parent, delete the children. For a system where some children should be preserved, I needed the logic in application code.

The Backend: Delete vs De-link

The cascade logic lives in tables.go. Each relationship is defined explicitly in Go with an Action field that determines what happens when the parent is removed:

type cascadeRule struct {
	Table    string `json:"table"`
	FKColumn string `json:"fk_column"`
	Action   string `json:"action"`
}

var cascadeRules = map[string][]cascadeRule{
	"users":      {{Table: "orders", FKColumn: "user_id", Action: "delink"}},
	"orders":     {{Table: "order_items", FKColumn: "order_id", Action: "delete"}},
	"menu_items": {{Table: "order_items", FKColumn: "menu_item_id", Action: "delete"}},
}

The Action field is the key distinction. "delete" means the child rows are destroyed — order items can't exist without their order. "delink" means the foreign key is set to NULL — orders continue to exist as anonymous business records, just no longer associated with a user account.

This is a deliberate choice over introspecting SQLite's foreign key metadata at runtime. Defining the rules in code makes them explicit, testable, and — critically — lets each relationship specify its own behaviour. A foreign key constraint in the schema can only say "cascade" or "restrict". The application code can say "this one de-links, that one deletes."

Building the Impact Tree

When an admin clicks delete, the frontend first hits GET /api/admin/tables/{table}/{id}/delete-preview. The handler calls buildCascadeImpact, which recursively walks the rules. The important detail is that de-link rules don't recurse — if orders are being de-linked rather than deleted, their order items are unaffected:

func buildCascadeImpact(db *sql.DB, table string, id string) []CascadeImpact {
	rules := cascadeRules[table]
	if len(rules) == 0 {
		return nil
	}

	var impacts []CascadeImpact
	for _, rule := range rules {
		var count int
		db.QueryRow(
			fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s = ?", rule.Table, rule.FKColumn),
			id,
		).Scan(&count)

		if count == 0 {
			continue
		}

		rows, err := db.Query(
			fmt.Sprintf("SELECT * FROM %s WHERE %s = ? LIMIT 3", rule.Table, rule.FKColumn),
			id,
		)
		var samples []map[string]any
		if err == nil {
			samples, _ = scanRowsToMaps(rows)
			rows.Close()
		}

		// For delink rules, children are unaffected — skip recursion
		var nestedImpacts []CascadeImpact
		if rule.Action != "delink" {
			// Only recurse for delete rules
			depRows, err := db.Query(
				fmt.Sprintf("SELECT id FROM %s WHERE %s = ?", rule.Table, rule.FKColumn),
				id,
			)
			if err == nil {
				defer depRows.Close()
				for depRows.Next() {
					var depID string
					if depRows.Scan(&depID) == nil {
						nested := buildCascadeImpact(db, rule.Table, depID)
						for _, n := range nested {
							// Merge nested impacts by table+column
							merged := false
							for i := range nestedImpacts {
								if nestedImpacts[i].Table == n.Table && nestedImpacts[i].FKColumn == n.FKColumn {
									nestedImpacts[i].Count += n.Count
									nestedImpacts[i].SampleRows = append(nestedImpacts[i].SampleRows, n.SampleRows...)
									if len(nestedImpacts[i].SampleRows) > 3 {
										nestedImpacts[i].SampleRows = nestedImpacts[i].SampleRows[:3]
									}
									merged = true
									break
								}
							}
							if !merged {
								nestedImpacts = append(nestedImpacts, n)
							}
						}
					}
				}
			}
		}

		impacts = append(impacts, CascadeImpact{
			Table:      rule.Table,
			FKColumn:   rule.FKColumn,
			Action:     rule.Action,
			Count:      count,
			SampleRows: samples,
			Dependents: nestedImpacts,
		})
	}

	return impacts
}

The recursion merges impacts from the same table — if a user has 10 orders being deleted, and each order has items, the nested order_items impacts get rolled up into a single entry with a combined count. Sample rows are capped at 3. But the key line is if rule.Action != "delink" — de-linked rows aren't being destroyed, so their children don't need to be inspected. When you delete a user, their orders are de-linked (preserved), so the order items stay exactly as they are.

The preview response separates the counts too:

type DeletePreviewResponse struct {
	Table         string          `json:"table"`
	ID            string          `json:"id"`
	Row           map[string]any  `json:"row"`
	Cascade       []CascadeImpact `json:"cascade"`
	TotalDeleted  int             `json:"total_deleted"`
	TotalDelinked int             `json:"total_delinked"`
}

This distinction matters for the frontend — the admin needs to know that 1 row will be permanently destroyed and 5 orders will be preserved but de-linked, not that "6 rows will be affected."

The Actual Delete

Once the admin confirms, the operation is wrapped in a transaction. The cascadeDelete function checks each rule's action — de-link rules set the foreign key to NULL and stamp a deletion_batch_id; delete rules recurse and destroy:

func cascadeDelete(tx *sql.Tx, table string, id string, batchID string) error {
	rules := cascadeRules[table]
	for _, rule := range rules {
		if rule.Action == "delink" {
			_, err := tx.Exec(
				fmt.Sprintf("UPDATE %s SET %s = NULL, deletion_batch_id = ? WHERE %s = ?",
					rule.Table, rule.FKColumn, rule.FKColumn),
				batchID, id,
			)
			if err != nil {
				return fmt.Errorf("delinking %s: %w", rule.Table, err)
			}
		} else {
			depIDs, err := getDependentIDs(tx, rule.Table, rule.FKColumn, id)
			if err != nil {
				return fmt.Errorf("finding %s dependents: %w", rule.Table, err)
			}
			for _, depID := range depIDs {
				if err := cascadeDelete(tx, rule.Table, depID, batchID); err != nil {
					return err
				}
			}
		}
	}

	_, err := tx.Exec(fmt.Sprintf("DELETE FROM %s WHERE id = ?", table), id)
	return err
}

The deletion_batch_id is a UUID generated per delete operation. It serves two purposes: it marks which orders were de-linked as part of the same action, and it enables a recovery path. If the admin realises they deleted the wrong user, or a new user account needs to take ownership of those orders, there's a relink endpoint:

result, err := db.Exec(
	"UPDATE orders SET user_id = ?, deletion_batch_id = NULL, updated_at = CURRENT_TIMESTAMP WHERE deletion_batch_id = ?",
	req.UserID, req.DeletionBatchID,
)

The entire operation — deletes and de-links together — runs in a single transaction. If anything fails, the whole thing rolls back. The admin either gets a clean result or no change at all.

The Frontend: Making the Impact Visible

The Go backend returns a structured impact tree with action types. The frontend's job is to make that tree understandable at a glance — and to clearly communicate the difference between data that will be destroyed and data that will be preserved.

Severity at a Glance

The first thing the admin sees is a colour-coded indicator. The system uses four states rather than a simple traffic light:

function SeverityIndicator({ depth, delinkOnly }: { depth: number; delinkOnly: boolean }) {
  if (depth === 0) {
    return (
      <div className="flex items-center gap-2">
        <div className="h-3 w-3 rounded-full bg-green-500" />
        <span className="text-xs font-medium text-green-600">Safe to delete</span>
      </div>
    );
  }
  if (delinkOnly) {
    return (
      <div className="flex items-center gap-2">
        <div className="h-3 w-3 rounded-full bg-blue-500" />
        <span className="text-xs font-medium text-blue-600">Related data will be de-linked, not deleted</span>
      </div>
    );
  }
  // ... amber for single-level deletes, red for deep cascades
}

Green means no cascades — safe to delete. Blue means related data exists but will be preserved (de-linked, not destroyed). Amber means one level of related data will be removed. Red means the delete cascades through multiple levels. The blue state is the most important addition — it tells the admin "this won't destroy business data" at a glance, which is a fundamentally different message from amber or red.

The dialog description also changes based on the action type. If all cascades are de-links, the admin sees "de-link 5 orders (they will remain in the database with no user attached)" rather than "delete 6 rows across multiple tables."

Expandable Impact Cards

Each cascade level gets its own collapsible card. The badge colour now reflects the action type — red for deletes, blue for de-links:

function CascadeImpactCard({ impact }: { impact: CascadeImpact }) {
  const [expanded, setExpanded] = useState(false);

  return (
    <div className="overflow-hidden rounded-md border">
      <button
        type="button"
        className="flex w-full items-center justify-between px-3 py-2 text-left text-sm hover:bg-muted/50"
        onClick={() => setExpanded(!expanded)}
      >
        <div className="flex items-center gap-2">
          <Database className="h-3.5 w-3.5 text-muted-foreground" />
          <code className="font-mono text-xs">{impact.table}</code>
          <span className="text-xs text-muted-foreground">via</span>
          <code className="font-mono text-xs text-muted-foreground">{impact.fk_column}</code>
        </div>
        <span className={`rounded px-1.5 py-0.5 font-mono text-xs font-medium ${
          impact.action === "delink"
            ? "bg-blue-500/10 text-blue-600"
            : "bg-destructive/10 text-destructive"
        }`}>
          {impact.count} row{impact.count !== 1 ? "s" : ""} — {impact.action === "delink" ? "de-link" : "delete"}
        </span>
      </button>

      {expanded && (
        <div className="border-t bg-muted/30 p-3">
          {impact.sample_rows?.map((sampleRow, i) => (
            <div key={i} className="rounded border bg-background p-2">
              <ExpandableRowPreview data={sampleRow} />
            </div>
          ))}

          {impact.dependents?.map((dep, i) => (
            <CascadeImpactCard key={i} impact={dep} />
          ))}
        </div>
      )}
    </div>
  );
}

The CascadeImpactCard is recursive — it renders its own children as nested cards. This means the component handles any depth of cascading without the frontend needing to know the schema in advance. And because each impact carries its own action type, a single delete operation can show a mix of blue de-link cards and red delete cards in the same tree.

Row Previews

Inside each impact card, sample rows are shown with an expandable preview. Rows with many columns start collapsed, showing only the first four fields:

function ExpandableRowPreview({ data }: { data: Record<string, unknown> }) {
  const [expanded, setExpanded] = useState(false);
  const entries = Object.entries(data);
  const hasMore = entries.length > 4;
  const visible = expanded ? entries : entries.slice(0, 4);

  return (
    <div className="space-y-1">
      {visible.map(([key, value]) => (
        <div key={key} className="flex min-w-0 gap-2 font-mono text-xs">
          <span className="shrink-0 text-muted-foreground">{key}:</span>
          <span className="truncate">
            {value === null ? "NULL" : String(value)}
          </span>
        </div>
      ))}
      {hasMore && (
        <button onClick={() => setExpanded(!expanded)}>
          Show {entries.length - 4} more fields
        </button>
      )}
    </div>
  );
}

This matters because the admin might recognise a specific order or customer name in the preview and realise they shouldn't delete. The data isn't abstract — it's real rows with real values.

Why Not Just Use SQLite's ON DELETE CASCADE?

SQLite can handle cascading deletes natively. I could set ON DELETE CASCADE on every foreign key and let the database do the work. There are three reasons I didn't.

First, the preview. There's no way to ask SQLite "what would this delete affect?" without actually doing the delete (or running it in a transaction and rolling back, which is fragile and wasteful). By building the impact tree in application code, I can show the admin exactly what will happen without touching the data.

Second, the delete/de-link distinction. SQLite's foreign key actions are all-or-nothing per constraint: CASCADE, SET NULL, RESTRICT. You can't conditionally choose based on context, and you can't stamp a batch ID on the nulled rows for later recovery. The application-level rules give me per-relationship behaviour with recovery built in.

Third, whitelisting. The cascade rules in Go serve as an allowlist. Not every foreign key relationship should be exposed to the admin browser. As the schema grows, new tables are safe by default — they won't appear in cascade previews or be deletable through the generic browser until explicitly added.

What I'd Do Differently

The current implementation queries the database multiple times during the recursive tree walk — once to count, once to sample, once to get IDs for recursion. For a coffee shop with a few hundred orders, this is fine. For a larger dataset, I'd batch these into fewer queries or use CTEs to walk the tree in SQL. The N+1 pattern is the classic trade-off of readable recursive code versus query efficiency.

I'd also consider caching the cascade rules from the schema itself as a startup step, cross-referencing them with an explicit allowlist, rather than maintaining the rules entirely by hand. Right now they're simple enough that manual definition is clearer, but that won't scale forever.

Lessons Learned

Not every relationship is the same kind of relationship. My first implementation treated all foreign keys as ownership — delete the parent, delete the children. The bug was a design error, not a code error. Orders aren't owned by users; they're associated with them. That distinction changes everything about how deletion should work.

Show the damage, not just the warning. "Are you sure?" teaches nothing. Showing the admin that deleting this user will de-link 5 orders and delete 0 items, with sample data they can inspect, turns a blind confirmation into an informed decision. Colour-coding the action type (blue for de-link, red for delete) makes the distinction immediate.

Build recovery into destructive operations. The deletion_batch_id costs almost nothing to implement but provides a clear recovery path. If orders are de-linked by mistake, they can be re-linked to another user by batch ID. Destructive operations should always leave a breadcrumb.