┌──────────────────────────────────────────────────────────────┐
│                  Branch Hard Delete Sequence                  │
├──────────────────────────────────────────────────────────────┤
│                                                              │
│  Step 1: Validation                                          │
│  ┌──────────────────────────────────────────────────────┐    │
│  │ • Verify caller has delete permission                │    │
│  │ • Check branch is not in provisioning state          │    │
│  │ • Confirm no active restore in progress              │    │
│  └──────────────────────────────────────────────────────┘    │
│                          │                                   │
│                          ▼                                   │
│  Step 2: Database Drop                                       │
│  ┌──────────────────────────────────────────────────────┐    │
│  │ • Terminate all active connections to branch DB      │    │
│  │ • DROP DATABASE <branch_db_name>                     │    │
│  └──────────────────────────────────────────────────────┘    │
│                          │                                   │
│                          ▼                                   │
│  Step 3: Role Cleanup                                        │
│  ┌──────────────────────────────────────────────────────┐    │
│  │ • REVOKE all privileges                              │    │
│  │ • DROP ROLE <branch_owner_role>                      │    │
│  │ • DROP ROLE <branch_readonly_role> (if exists)       │    │
│  │ • DROP ROLE <branch_readwrite_role> (if exists)      │    │
│  └──────────────────────────────────────────────────────┘    │
│                          │                                   │
│                          ▼                                   │
│  Step 4: PgBouncer Cleanup                                   │
│  ┌──────────────────────────────────────────────────────┐    │
│  │ • Remove database entry from pgbouncer.ini           │    │
│  │ • Remove user entries from pgbouncer userlist        │    │
│  │ • Reload PgBouncer configuration                     │    │
│  └──────────────────────────────────────────────────────┘    │
│                          │                                   │
│                          ▼                                   │
│  Step 5: Credential Revocation                               │
│  ┌──────────────────────────────────────────────────────┐    │
│  │ • Revoke all connection credentials                  │    │
│  │ • Invalidate API keys associated with branch         │    │
│  │ • Remove credentials from secrets manager            │    │
│  └──────────────────────────────────────────────────────┘    │
│                          │                                   │
│                          ▼                                   │
│  Step 6: Network Grant Cleanup                               │
│  ┌──────────────────────────────────────────────────────┐    │
│  │ • Remove pg_hba.conf entries for branch              │    │
│  │ • Remove CIDR grants from network policy             │    │
│  │ • Reload PostgreSQL for pg_hba changes               │    │
│  └──────────────────────────────────────────────────────┘    │
│                          │                                   │
│                          ▼                                   │
│  Step 7: Metadata Removal                                    │
│  ┌──────────────────────────────────────────────────────┐    │
│  │ • Delete branch record from AxiomDB metadata DB      │    │
│  │ • Remove from project's branch list                  │    │
│  │ • Update project storage usage                       │    │
│  └──────────────────────────────────────────────────────┘    │
│                          │                                   │
│                          ▼                                   │
│  Step 8: Audit Log                                           │
│  ┌──────────────────────────────────────────────────────┐    │
│  │ • Record deletion event in audit log                 │    │
│  │ • PRESERVED indefinitely for compliance              │    │
│  └──────────────────────────────────────────────────────┘    │
│                                                              │
└──────────────────────────────────────────────────────────────┘

# Delete a branch via the Gateway API
curl -X DELETE http://127.0.0.1:4060/api/branches/<branch_id> \
  -H "Authorization: Bearer <token>" \
  -H "Content-Type: application/json"

{
  "status": "deleted",
  "branch_id": "br_abc123def456",
  "branch_name": "feature-auth",
  "deleted_at": "2025-01-15T14:30:00Z",
  "resources_removed": {
    "database": "branch_feature_auth",
    "roles_dropped": [
      "branch_feature_auth_owner",
      "branch_feature_auth_ro",
      "branch_feature_auth_rw"
    ],
    "pgbouncer_users_removed": 3,
    "credentials_revoked": 2,
    "network_grants_removed": 1
  }
}

# Delete a branch
square-dbctl delete --branch feature-auth --confirm

# Force delete (skip confirmation prompt)
square-dbctl delete --branch feature-auth --force

┌──────────────────────────────────────────────────────┐
│              Project Deletion Confirmation            │
├──────────────────────────────────────────────────────┤
│                                                      │
│  API Request:                                        │
│  DELETE /api/projects/<project_id>                   │
│                                                      │
│  Required Headers:                                   │
│  Authorization: Bearer <owner_token>                 │
│  Content-Type: application/json                      │
│                                                      │
│  Required Body:                                      │
│  {                                                   │
│    "confirm": "<exact_project_name>",                │
│    "acknowledge_data_loss": true                     │
│  }                                                   │
│                                                      │
│  Validation:                                         │
│  ✓ Caller is project owner                           │
│  ✓ confirm matches project name exactly              │
│  ✓ acknowledge_data_loss is true                     │
│  ✓ All branches are in deletable state               │
│                                                      │
│  If any check fails → 403 Forbidden                  │
│                                                      │
└──────────────────────────────────────────────────────┘

# Delete a project (requires owner auth + typed confirmation)
curl -X DELETE http://127.0.0.1:4060/api/projects/<project_id> \
  -H "Authorization: Bearer <owner_token>" \
  -H "Content-Type: application/json" \
  -d '{
    "confirm": "my-project-name",
    "acknowledge_data_loss": true
  }'

{
  "status": "deleted",
  "project_id": "proj_xyz789",
  "project_name": "my-project-name",
  "deleted_at": "2025-01-15T14:30:00Z",
  "branches_deleted": [
    {
      "branch_id": "br_abc123",
      "branch_name": "main"
    },
    {
      "branch_id": "br_def456",
      "branch_name": "staging"
    },
    {
      "branch_id": "br_ghi789",
      "branch_name": "feature-auth"
    }
  ],
  "total_resources_removed": {
    "databases": 3,
    "roles": 9,
    "credentials": 6,
    "network_grants": 3
  }
}

// Not the project owner
{
  "error": "forbidden",
  "message": "Only the project owner can delete a project",
  "status": 403
}

// Confirmation doesn't match
{
  "error": "confirmation_mismatch",
  "message": "The 'confirm' field must match the project name exactly: 'my-project-name'",
  "status": 400
}

// acknowledge_data_loss not set
{
  "error": "missing_acknowledgement",
  "message": "You must set acknowledge_data_loss to true to delete a project",
  "status": 400
}

// Branch stuck in provisioning
{
  "error": "branch_not_deletable",
  "message": "Branch 'feature-auth' is currently provisioning and cannot be deleted",
  "status": 409
}

-- Verify database is dropped
SELECT datname FROM pg_database WHERE datname = 'branch_feature_auth';
-- Should return 0 rows

-- Verify roles are dropped
SELECT rolname FROM pg_roles WHERE rolname LIKE 'branch_feature_auth%';
-- Should return 0 rows

-- Verify no orphaned connections
SELECT datname, usename FROM pg_stat_activity
WHERE datname = 'branch_feature_auth';
-- Should return 0 rows

# Verify PgBouncer cleanup
psql -h 127.0.0.1 -p 6432 -U pgbouncer -d pgbouncer -c "SHOW DATABASES;" | grep feature_auth
# Should return no results

# Verify network grants removed
psql -h 127.0.0.1 -p 5432 -U axiomdb -c "
    SELECT * FROM pg_hba_file_rules WHERE database LIKE '%feature_auth%';
"
-- Should return 0 rows

{
  "event_id": "evt_20250115143000001",
  "event_type": "branch.hard_delete",
  "timestamp": "2025-01-15T14:30:00.000Z",
  "actor": {
    "user_id": "usr_abc123",
    "email": "admin@example.com",
    "ip_address": "203.0.113.42",
    "user_agent": "curl/8.1.2"
  },
  "target": {
    "branch_id": "br_abc123def456",
    "branch_name": "feature-auth",
    "project_id": "proj_xyz789",
    "project_name": "my-project"
  },
  "resources_removed": {
    "database": "branch_feature_auth",
    "roles": [
      "branch_feature_auth_owner",
      "branch_feature_auth_ro",
      "branch_feature_auth_rw"
    ],
    "pgbouncer_users": 3,
    "credentials_revoked": 2,
    "network_grants": 1
  },
  "metadata": {
    "reason": "user_requested",
    "storage_freed_bytes": 1073741824,
    "duration_ms": 1250
  }
}

# List deletion events for a project
curl -s "http://127.0.0.1:4060/api/projects/<project_id>/audit?event_type=branch.hard_delete" \
  -H "Authorization: Bearer <token>" | jq .

# List all deletion events (admin only)
curl -s "http://127.0.0.1:4060/api/audit?event_type=branch.hard_delete,project.hard_delete&limit=50" \
  -H "Authorization: Bearer <admin_token>" | jq .

-- Direct audit log query (admin access)
SELECT
    event_id,
    event_type,
    timestamp,
    actor_email,
    target_branch_name,
    target_project_name,
    reason
FROM audit_log
WHERE event_type IN ('branch.hard_delete', 'project.hard_delete')
ORDER BY timestamp DESC
LIMIT 50;

# Step 1: Find the backup to restore from
pgbackrest --stanza=axiomdb info
# Look for backups that include the deleted branch's data

# Step 2: Create a new branch from the backup
curl -X POST http://127.0.0.1:4060/api/branches \
  -H "Authorization: Bearer <token>" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "restored-feature-auth",
    "project_id": "proj_xyz789",
    "restore_from": {
      "backup_label": "20250115-020001F",
      "database_name": "branch_feature_auth"
    }
  }'

# Step 3: Verify the restored branch
psql -h 127.0.0.1 -p 5432 -U axiomdb_restored_feature_auth \
  -d restored_feature_auth -c "
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = 'public' ORDER BY table_name;
"

# Step 4: Reconfigure network grants
curl -X POST http://127.0.0.1:4060/api/branches/<new_branch_id>/network-grants \
  -H "Content-Type: application/json" \
  -d '{"cidr": "10.0.0.0/8", "description": "internal network"}'

# Step 5: Generate new credentials
curl -X POST http://127.0.0.1:4060/api/branches/<new_branch_id>/credentials \
  -H "Authorization: Bearer <token>"

□ Enable deletion protection on production branches
□ Set up alerts for deletion API calls
□ Require manual review for deletions in CI/CD pipelines
□ Use branch naming conventions that distinguish prod from dev
□ Regular backup verification to ensure recovery is possible
□ Document branch ownership and purpose
□ Use separate projects for production and development

# Enable deletion protection on a branch
curl -X PATCH http://127.0.0.1:4060/api/branches/<branch_id> \
  -H "Authorization: Bearer <token>" \
  -H "Content-Type: application/json" \
  -d '{"deletion_protection": true}'

# Attempting to delete a protected branch returns:
# {
#   "error": "deletion_protected",
#   "message": "This branch has deletion protection enabled. Disable it first.",
#   "status": 403
# }

# Disable deletion protection (requires confirmation)
curl -X PATCH http://127.0.0.1:4060/api/branches/<branch_id> \
  -H "Authorization: Bearer <token>" \
  -H "Content-Type: application/json" \
  -d '{
    "deletion_protection": false,
    "confirm_disable_protection": true
  }'

# Add to your alerting script
# Alert on any deletion API call
DELETION_COUNT=$(psql -h 127.0.0.1 -p 5432 -U axiomdb -t -c "
    SELECT count(*) FROM audit_log
    WHERE event_type IN ('branch.hard_delete', 'project.hard_delete')
      AND timestamp > now() - interval '5 minutes';
" | tr -d ' ')

if [ "$DELETION_COUNT" -gt 0 ]; then
    curl -s -X POST "$SLACK_WEBHOOK" -d "{
        \"text\": \"⚠️ $DELETION_COUNT deletion(s) in the last 5 minutes. Check audit log.\"
    }"
fi