Skip to content
Brokkr

Soft Deletion Pattern

Brokkr uses soft deletion as the default deletion strategy for primary entities. Rather than removing records from the database, soft deletion marks records with a deleted_at timestamp while preserving all data. This design enables audit compliance, accidental deletion recovery, and maintains referential integrity across related resources.

How Soft Deletion Works

Section titled “How Soft Deletion Works”

When you delete a resource through the Brokkr API, the system sets the deleted_at timestamp to the current time rather than executing a SQL DELETE. This approach has several consequences:

  • The record remains in the database with all its data intact
  • Standard API queries filter out records where deleted_at IS NOT NULL
  • Related resources may be cascade soft-deleted depending on the entity type
  • The record can potentially be recovered by clearing the deleted_at field
  • Unique constraints are scoped to only active (non-deleted) records

Entities Supporting Soft Deletion

Section titled “Entities Supporting Soft Deletion”
Entity Cascade Behavior API Endpoint
Agents Soft deletes agent; cascade soft-deletes agent events DELETE /api/v1/agents/{id}
Stacks Cascades to deployment objects; creates deletion marker DELETE /api/v1/stacks/{id}
Generators Cascades to stacks and deployment objects DELETE /api/v1/generators/{id}
Templates Soft deletes template only DELETE /api/v1/templates/{id}
Agent Events Cascade soft-deleted when parent agent is soft-deleted Not directly exposed
Deployment Objects Soft deletes object only Generally not exposed directly

Cascade Behavior

Section titled “Cascade Behavior”

Stack Deletion

Section titled “Stack Deletion”

When a stack is soft-deleted, the system triggers several cascading operations through database triggers:

  1. All deployment objects belonging to the stack are soft-deleted
  2. A special deletion marker deployment object is created with is_deletion_marker: true
  3. The deletion marker notifies agents to remove the resources from their clusters
-- Simplified trigger logic
UPDATE deployment_objects
SET deleted_at = NEW.deleted_at
WHERE stack_id = NEW.id AND deleted_at IS NULL;


INSERT INTO deployment_objects (stack_id, yaml_content, is_deletion_marker)
VALUES (NEW.id, '', TRUE);

Generator Deletion

Section titled “Generator Deletion”

When a generator is soft-deleted, the cascade propagates to all resources created by that generator:

  1. All stacks owned by the generator are soft-deleted
  2. All deployment objects in those stacks are soft-deleted
  3. Each stack’s soft deletion also creates deletion markers

This ensures that deleting a generator properly cleans up resources across all clusters that received deployments from that generator.

Agent Deletion

Section titled “Agent Deletion”

When an agent is soft-deleted, a database trigger cascade soft-deletes all associated agent events by setting their deleted_at timestamps.

Unique Constraints

Section titled “Unique Constraints”

Brokkr uses partial unique indexes that exclude soft-deleted records. This design allows you to reuse names after deleting resources:

-- Example: Stack name uniqueness
CREATE UNIQUE INDEX unique_stack_name
ON stacks (name)
WHERE deleted_at IS NULL;

This means:

  • You cannot create two active stacks with the same name
  • After soft-deleting a stack, you can create a new stack with the same name
  • The original soft-deleted stack remains in the database with its historical data

Entities with partial unique constraints:

Entity Unique Fields
Agents (name, cluster_name)
Stacks name
Generators name
Templates (generator_id, name, version)

Querying Deleted Records

Section titled “Querying Deleted Records”

Every API endpoint filters out soft-deleted records; there is no query parameter that includes them. A soft-deleted resource is indistinguishable from a missing one over the API — GET returns 404.

Deleted rows are reachable only from inside the broker (the data-access layer keeps include-deleted variants used for audit trail queries, database maintenance, and recovery) or by querying the database directly.

Hard Deletion

Section titled “Hard Deletion”

Hard deletion (permanent removal from database) is available but used sparingly. When a hard delete occurs, additional cleanup is performed through BEFORE DELETE triggers:

Stack Hard Delete

Section titled “Stack Hard Delete”
-- Cleanup performed before hard delete
DELETE FROM agent_targets WHERE stack_id = OLD.id;
DELETE FROM agent_events WHERE deployment_object_id IN (
    SELECT id FROM deployment_objects WHERE stack_id = OLD.id
);
DELETE FROM deployment_objects WHERE stack_id = OLD.id;

Hard deletion is typically reserved for:

  • Data retention compliance after the retention period
  • Cleaning up test data
  • Emergency data removal scenarios

Recovery

Section titled “Recovery”

Brokkr does not expose a recovery API; recovery is possible only by clearing deleted_at directly in the database (e.g. UPDATE stacks SET deleted_at = NULL WHERE id = ...), and cascade-deleted children carry their own deleted_at values that must be cleared individually.

Constraints on recovery:

  • Cascade-deleted children are recovered individually
  • Deletion markers created during soft deletion remain
  • Unique constraint conflicts arise if a new resource was created with the same name

Database Schema Details

Section titled “Database Schema Details”

deleted_at Column

Section titled “deleted_at Column”

All soft-deletable entities include:

deleted_at TIMESTAMP WITH TIME ZONE

This column is:

  • NULL for active records
  • Set to the deletion timestamp for deleted records
  • Indexed for query performance: CREATE INDEX idx_entity_deleted_at ON entity(deleted_at);

Trigger Functions

Section titled “Trigger Functions”

Soft deletion cascades are implemented as PostgreSQL trigger functions:

Trigger Table Event Function
trigger_handle_stack_soft_delete stacks AFTER UPDATE of deleted_at handle_stack_soft_delete()
cascade_soft_delete_generators generators AFTER UPDATE cascade_soft_delete_generators()
cascade_soft_delete_agents agents AFTER UPDATE cascade_soft_delete_agents()
trigger_stack_hard_delete stacks BEFORE DELETE handle_stack_hard_delete()

Performance Characteristics

Section titled “Performance Characteristics”

Query overhead: Every query includes WHERE deleted_at IS NULL; the deleted_at column is indexed.

Table growth: Soft-deleted records accumulate over time; they are removed only by hard deletion.

Index size: Partial unique indexes only include active records, keeping index size proportional to active data rather than total data.

Section titled “Related Documentation”