284313f908
Some checks are pending
Bidi Control Character Guard / bidi-control-guard (push) Waiting to run
Circular Dependency Check / Check for new circular dependencies (push) Waiting to run
Citus Migration Smoke / Combined migrations on single-node Citus (push) Waiting to run
E2E Fresh Install Tests / fresh-install-e2e (push) Waiting to run
ext-v2 guardrails / Run ext-v2 guard and ESLint (push) Waiting to run
Integration Tests / Check for relevant changes (push) Waiting to run
Integration Tests / ${{ (github.event_name == 'schedule' || github.event.inputs.suite == 'full') && 'Full integration suite' || 'Tier-1 integration subset' }} (push) Blocked by required conditions
Mobile checks / Mobile lint + typecheck (push) Waiting to run
Mobile checks / Mobile unit tests (push) Waiting to run
Mobile checks / Mobile dependency audit (report) (push) Waiting to run
Mobile checks / Mobile reproducibility checks (push) Waiting to run
Secrets guard (env backups) / Ensure no tracked env backup files (push) Waiting to run
Temporal Readiness / fast-readiness (push) Waiting to run
Temporal Readiness / docker-parity (push) Waiting to run
TypeScript Type Check / Nx affected typecheck (push) Waiting to run
Unit Tests / Skipped-test budget (push) Waiting to run
Unit Tests / Nx affected unit tests (push) Waiting to run
Unit Tests / Server unit coverage (informational) (push) Waiting to run
Validate Tenant Management Schema / Check for relevant changes (push) Waiting to run
Validate Tenant Management Schema / Validate Tenant Management Schema (push) Blocked by required conditions
EE Workflows Build Guard / ee-workflows-build-guard (push) Waiting to run
Excluded: .git, node_modules, secrets/, compose.env, assemblyscript tgz Source: /opt/alga-psa on psa.joliet.tech
739 lines
31 KiB
Plaintext
739 lines
31 KiB
Plaintext
#!/usr/bin/env nu
|
|
|
|
# Tenant Cleanup Tool for Alga PSA
|
|
#
|
|
# Usage:
|
|
# ./cleanup-tenant.nu list # List all tenants
|
|
# ./cleanup-tenant.nu inspect <tenant-id> # Inspect tenant data
|
|
# ./cleanup-tenant.nu cleanup <tenant-id> # Cleanup tenant (dry-run)
|
|
# ./cleanup-tenant.nu cleanup <tenant-id> --execute # Actually delete data
|
|
#
|
|
# Options:
|
|
# --environment <local|production> Environment to use (default: local)
|
|
# --execute Actually delete data (without this, it's a dry run)
|
|
# --preserve-tenant Keep the tenant record itself
|
|
# --force Skip confirmation prompts
|
|
|
|
# Read secret from file
|
|
def read-secret [filename: string] {
|
|
let secret_path = $"($env.PWD)/secrets/($filename)"
|
|
if ($secret_path | path exists) {
|
|
open $secret_path | str trim
|
|
} else {
|
|
null
|
|
}
|
|
}
|
|
|
|
# Get database configuration
|
|
def get-db-config [env_name: string = "local"] {
|
|
if $env_name == "local" {
|
|
{
|
|
host: ($env.DB_HOST? | default "localhost")
|
|
port: ($env.DB_PORT? | default 5432)
|
|
database: ($env.DB_NAME? | default "server")
|
|
user: ($env.DB_USER? | default "postgres")
|
|
password: ($env.DB_PASSWORD? | default "postpass123")
|
|
}
|
|
} else {
|
|
let prod_password = (read-secret "db_password_prod") | default $env.PROD_DB_PASSWORD?
|
|
let prod_host = (read-secret "db_host_prod") | default ($env.PROD_DB_HOST? | default "localhost")
|
|
let prod_port = (read-secret "db_port_prod") | default ($env.PROD_DB_PORT? | default "5433")
|
|
let prod_database = (read-secret "db_name_prod") | default ($env.PROD_DB_NAME? | default "server")
|
|
let prod_user = (read-secret "db_user_prod") | default ($env.PROD_DB_USER? | default "postgres")
|
|
|
|
if $prod_password == null {
|
|
print "Production database password not found!"
|
|
print "Please either:"
|
|
print "1. Create secrets/db_password_prod file with the password"
|
|
print "2. Or set PROD_DB_PASSWORD environment variable"
|
|
exit 1
|
|
}
|
|
|
|
{
|
|
host: $prod_host
|
|
port: ($prod_port | into int)
|
|
database: $prod_database
|
|
user: $prod_user
|
|
password: $prod_password
|
|
}
|
|
}
|
|
}
|
|
|
|
# Build PostgreSQL connection string
|
|
def build-connection-string [config: record] {
|
|
$"postgresql://($config.user):($config.password)@($config.host):($config.port)/($config.database)"
|
|
}
|
|
|
|
# Execute SQL query
|
|
def execute-sql [
|
|
query: string
|
|
--env-name: string = "local"
|
|
] {
|
|
let config = get-db-config $env_name
|
|
let conn_str = build-connection-string $config
|
|
|
|
psql $conn_str -t -A -c $query | lines | where {|line| $line != "" }
|
|
}
|
|
|
|
# Execute SQL query and return as table
|
|
def query-sql [
|
|
query: string
|
|
--env-name: string = "local"
|
|
] {
|
|
let config = get-db-config $env_name
|
|
let conn_str = build-connection-string $config
|
|
|
|
psql $conn_str --csv -c $query | from csv
|
|
}
|
|
|
|
# List all tenants
|
|
def "main list" [
|
|
--environment: string = "local" # Environment to use
|
|
] {
|
|
print $"Environment: ($environment)"
|
|
print "\nFetching tenants...\n"
|
|
|
|
let query = "
|
|
SELECT
|
|
t.tenant,
|
|
t.client_name,
|
|
t.created_at,
|
|
COUNT(DISTINCT u.user_id) as user_count,
|
|
COUNT(DISTINCT tk.ticket_id) as ticket_count,
|
|
MAX(tk.entered_at) as last_ticket_date
|
|
FROM tenants t
|
|
LEFT JOIN users u ON t.tenant = u.tenant
|
|
LEFT JOIN tickets tk ON t.tenant = tk.tenant
|
|
GROUP BY t.tenant, t.client_name, t.created_at
|
|
ORDER BY t.created_at DESC
|
|
"
|
|
|
|
let tenants = query-sql $query --env-name $environment
|
|
|
|
print $"Found ($tenants | length) tenants:\n"
|
|
|
|
# Format and display the table with quick total counts
|
|
let enriched_tenants = $tenants | each { |row|
|
|
let user_count = if ($row.user_count? | is-empty) { 0 } else { $row.user_count | into int }
|
|
let ticket_count = if ($row.ticket_count? | is-empty) { 0 } else { $row.ticket_count | into int }
|
|
|
|
# Quick count of key tables for est_total_records (for performance)
|
|
let count_query = "
|
|
SELECT
|
|
(SELECT COUNT(*) FROM users WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM clients WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM contacts WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM tickets WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM projects WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM documents WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM time_entries WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM invoices WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM comments WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM roles WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM user_roles WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM role_permissions WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM workflow_executions WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM workflow_events WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM task_checklist_items WHERE tenant = '" + $row.tenant + "') +
|
|
(SELECT COUNT(*) FROM project_tasks WHERE tenant = '" + $row.tenant + "')
|
|
as est_total_records
|
|
"
|
|
|
|
let total_result = query-sql $count_query --env-name $environment | first
|
|
let est_total_records = if ($total_result.est_total_records? | is-empty) { 0 } else { $total_result.est_total_records | into int }
|
|
|
|
{
|
|
tenant: $row.tenant
|
|
client: $row.client_name
|
|
created: ($row.created_at | str substring 0..10)
|
|
users: $user_count
|
|
tickets: $ticket_count
|
|
last_ticket: (if ($row.last_ticket_date? | is-empty) { "never" } else { $row.last_ticket_date | str substring 0..10 })
|
|
est_total_records: $est_total_records
|
|
}
|
|
}
|
|
|
|
$enriched_tenants | table
|
|
}
|
|
|
|
# Inspect tenant data
|
|
def "main inspect" [
|
|
tenant_id: string # Tenant ID to inspect
|
|
--environment: string = "local" # Environment to use
|
|
] {
|
|
print $"\nInspecting tenant: ($tenant_id)\n"
|
|
|
|
# Get tenant info
|
|
let tenant_query = "SELECT * FROM tenants WHERE tenant = '" + $tenant_id + "'"
|
|
let tenant = query-sql $tenant_query --env-name $environment | first
|
|
|
|
if ($tenant | is-empty) {
|
|
print "Tenant not found!"
|
|
exit 1
|
|
}
|
|
|
|
print $"Client: ($tenant.client_name)"
|
|
print $"Created: ($tenant.created_at)"
|
|
print "\nData breakdown:\n"
|
|
|
|
# Get all tables with tenant column
|
|
let tables_query = "
|
|
SELECT
|
|
c.table_name,
|
|
c.column_name as tenant_column
|
|
FROM information_schema.columns c
|
|
JOIN information_schema.tables t ON c.table_name = t.table_name
|
|
AND c.table_schema = t.table_schema
|
|
WHERE c.column_name IN ('tenant', 'tenant_id')
|
|
AND c.table_schema = 'public'
|
|
AND t.table_type = 'BASE TABLE'
|
|
ORDER BY c.table_name
|
|
"
|
|
|
|
let tables = query-sql $tables_query --env-name $environment
|
|
|
|
# Count records in each table
|
|
let counts = $tables | par-each { |table|
|
|
let count_query = "SELECT COUNT(*) as count FROM " + $table.table_name + " WHERE " + $table.tenant_column + " = '" + $tenant_id + "'"
|
|
|
|
try {
|
|
let result = query-sql $count_query --env-name $environment | first
|
|
let count = if ($result.count? | is-empty) { 0 } else { $result.count | into int }
|
|
|
|
if $count > 0 {
|
|
{ table: $table.table_name, records: $count }
|
|
} else {
|
|
null
|
|
}
|
|
} catch {
|
|
null
|
|
}
|
|
} | where {|item| $item != null } | sort-by records --reverse
|
|
|
|
# Display results
|
|
$counts | table
|
|
|
|
print $"\nTables with data: ($counts | length)"
|
|
print $"Estimated total records: ($counts | get records | math sum)"
|
|
}
|
|
|
|
# Cleanup tenant
|
|
def "main cleanup" [
|
|
tenant_id: string # Tenant ID to cleanup
|
|
--environment: string = "local" # Environment to use
|
|
--execute # Actually delete (default is dry-run)
|
|
--preserve-tenant # Keep the tenant record itself
|
|
--force # Skip confirmation prompts
|
|
] {
|
|
let is_dry_run = not $execute
|
|
|
|
print ("=" | fill -w 60)
|
|
let mode = if $is_dry_run { "DRY RUN" } else { "*** ACTUAL DELETION ***" }
|
|
print $"TENANT CLEANUP - ($mode)"
|
|
print ("=" | fill -w 60)
|
|
|
|
# Get tenant info
|
|
let tenant_query = "SELECT * FROM tenants WHERE tenant = '" + $tenant_id + "'"
|
|
let tenant = query-sql $tenant_query --env-name $environment | first
|
|
|
|
if ($tenant | is-empty) {
|
|
print "Tenant not found!"
|
|
exit 1
|
|
}
|
|
|
|
print $"Tenant ID: ($tenant_id)"
|
|
print $"Client: ($tenant.client_name)"
|
|
print $"Created: ($tenant.created_at)"
|
|
print $"Preserve tenant record: ($preserve_tenant)"
|
|
print $"Environment: ($environment)"
|
|
|
|
# Check if this looks like production data
|
|
let activity_query = (
|
|
"SELECT " +
|
|
"(SELECT COUNT(*) FROM users WHERE tenant = '" + $tenant_id + "') as user_count, " +
|
|
"(SELECT COUNT(*) FROM invoices WHERE tenant = '" + $tenant_id + "' AND created_at > NOW() - INTERVAL '30 days') as recent_invoices, " +
|
|
"(SELECT COUNT(*) FROM tickets WHERE tenant = '" + $tenant_id + "' AND entered_at > NOW() - INTERVAL '7 days') as recent_tickets"
|
|
)
|
|
|
|
let activity = query-sql $activity_query --env-name $environment | first
|
|
let user_count = $activity.user_count | into int
|
|
let recent_invoices = $activity.recent_invoices | into int
|
|
let recent_tickets = $activity.recent_tickets | into int
|
|
|
|
let is_production = ($user_count > 20) or ($recent_invoices > 0) or ($recent_tickets > 5)
|
|
|
|
if $is_production {
|
|
print "\n*** WARNING: This appears to be an ACTIVE/PRODUCTION tenant! ***"
|
|
print $"Users: ($user_count)"
|
|
print $"Recent invoices \(30d\): ($recent_invoices)"
|
|
print $"Recent tickets \(7d\): ($recent_tickets)"
|
|
|
|
if (not $force) and (not $is_dry_run) {
|
|
let answer = input "\nAre you SURE you want to delete this production tenant? (yes/no): "
|
|
if $answer != "yes" {
|
|
print "Cleanup cancelled."
|
|
exit 0
|
|
}
|
|
}
|
|
}
|
|
|
|
if (not $is_dry_run) and (not $force) {
|
|
let answer = input "\nThis will PERMANENTLY delete data. Continue? (yes/no): "
|
|
if $answer != "yes" {
|
|
print "Cleanup cancelled."
|
|
exit 0
|
|
}
|
|
}
|
|
|
|
print "\nStarting cleanup...\n"
|
|
|
|
# Tables to delete from (in dependency order - most dependent first)
|
|
# The order is critical due to foreign key constraints
|
|
let tables = [
|
|
# === LEVEL 0: Sessions (CRITICAL - must be deleted before users/tenants) ===
|
|
"sessions"
|
|
|
|
# === LEVEL 1: Leaf tables with no dependencies ===
|
|
# Global search index (no FKs, denormalized projection)
|
|
"app_search_index"
|
|
|
|
# Workflow details
|
|
"workflow_action_results" "workflow_event_attachments" "workflow_snapshots"
|
|
"workflow_action_dependencies" "workflow_sync_points" "workflow_timers"
|
|
"workflow_task_history" "workflow_form_schemas"
|
|
|
|
# Workflow runtime V2 (child tables first, then parent) — keep in sync
|
|
# with TENANT_TABLES_DELETION_ORDER in tenant-deletion-activities.ts
|
|
"workflow_run_steps" "workflow_run_waits" "workflow_run_snapshots"
|
|
"workflow_action_invocations" "workflow_definition_versions"
|
|
"workflow_run_logs" "workflow_runtime_events" "workflow_step_usage_periods"
|
|
"workflow_runs" "tenant_workflow_schedule" "workflow_definitions"
|
|
|
|
# Task/project details
|
|
"task_checklist_items" "project_task_dependencies" "task_resources"
|
|
"project_ticket_links" "project_task_comments"
|
|
|
|
# Project template details
|
|
"project_template_checklist_items" "project_template_dependencies"
|
|
"project_template_task_resources" "project_template_status_mappings"
|
|
"project_template_tasks" "project_template_phases" "project_templates"
|
|
|
|
# Quote details
|
|
"quote_activities" "quote_items" "quote_document_template_assignments"
|
|
"quote_document_templates" "standard_quote_document_templates" "quotes"
|
|
|
|
# Invoice details
|
|
"invoice_charges" "invoice_annotations" "invoice_time_entries" "invoice_usage_records"
|
|
"invoice_charge_details" "invoice_charge_fixed_details" "invoice_items"
|
|
"invoice_payment_links" "invoice_payments" "invoice_template_assignments"
|
|
|
|
# Time tracking
|
|
"time_sheet_comments" "time_entries" "time_sheets"
|
|
|
|
# Document details
|
|
"document_block_content" "document_versions" "document_content"
|
|
"document_folders" "document_system_entries"
|
|
|
|
# Messages and comments
|
|
# comment_threads is the parent of comments.thread_id / project_task_comments.thread_id
|
|
# (CASCADE) and email_sending_logs.comment_thread_id (SET NULL); delete AFTER comments
|
|
# and project_task_comments (which is removed at the project_task_comments line above).
|
|
"comments" "comment_threads"
|
|
"gmail_processed_history" "email_processed_messages"
|
|
"email_reply_tokens" "email_sending_logs" "email_rate_limits"
|
|
|
|
# User related details
|
|
"user_activity_group_items" "user_activity_groups"
|
|
"user_notification_preferences" "user_internal_notification_preferences" "user_preferences"
|
|
"role_permissions" "user_roles" "user_auth_accounts"
|
|
|
|
# Apple IAP billing
|
|
"apple_iap_subscriptions" "apple_iap_notifications"
|
|
|
|
# Schedule and team
|
|
"schedule_entry_assignees" "schedule_conflicts" "team_members"
|
|
"availability_exceptions" "availability_settings"
|
|
|
|
# Calendar
|
|
"calendar_event_mappings" "calendar_provider_health"
|
|
"google_calendar_provider_config" "microsoft_calendar_provider_config" "calendar_providers"
|
|
|
|
# Tags and resources
|
|
"tag_mappings" "ticket_resources"
|
|
|
|
# Logs and notifications
|
|
"job_details" "jobs" "audit_logs" "notification_logs" "internal_notifications"
|
|
|
|
# Import/export
|
|
"import_job_items" "import_jobs" "import_sources"
|
|
"accounting_export_errors" "accounting_export_lines" "accounting_export_batches"
|
|
|
|
# Asset details
|
|
"asset_maintenance_notifications" "asset_maintenance_history" "asset_service_history"
|
|
"asset_ticket_associations" "asset_document_associations" "asset_relationships"
|
|
"asset_history" "asset_associations" "asset_software"
|
|
"workstation_assets" "server_assets" "network_device_assets" "mobile_device_assets" "printer_assets"
|
|
# Asset type registry (RESTRICT FK to tenants; delete before tenants)
|
|
"asset_type_registry"
|
|
|
|
# Software catalog
|
|
"software_catalog"
|
|
|
|
# RMM
|
|
"rmm_alert_rules" "rmm_alerts" "rmm_organization_mappings" "rmm_integrations"
|
|
|
|
# Hudu integration (one connection row per tenant)
|
|
"hudu_integrations"
|
|
|
|
# Survey
|
|
"survey_responses" "survey_invitations" "survey_triggers" "survey_templates"
|
|
|
|
# Appointment
|
|
"appointment_requests"
|
|
|
|
# SLA leaf tables (must be before tickets, statuses, priorities, boards)
|
|
"sla_notifications_sent" "sla_audit_log"
|
|
"sla_notification_thresholds" "sla_policy_targets"
|
|
"status_sla_pause_config"
|
|
"business_hours_entries" "holidays"
|
|
"escalation_managers"
|
|
"sla_settings"
|
|
|
|
# === LEVEL 2: Tables that depend on level 3+ ===
|
|
# Payment/Stripe
|
|
"stripe_webhook_events" "stripe_subscriptions" "stripe_prices" "stripe_products"
|
|
"stripe_customers" "stripe_accounts"
|
|
"payment_webhook_events" "payment_provider_configs" "client_payment_customers"
|
|
|
|
# Billing details
|
|
"credit_allocations" "credit_reconciliation_reports" "credit_tracking"
|
|
"usage_tracking" "bucket_usage" "transactions"
|
|
"client_contracts" "contract_line_service_rate_tiers" "contract_line_service_bucket_config"
|
|
"contract_line_service_hourly_config" "contract_line_service_hourly_configs" "contract_line_service_usage_config"
|
|
"contract_line_service_fixed_config" "contract_line_service_configuration"
|
|
"contract_line_service_defaults" "contract_pricing_schedules"
|
|
"service_rate_tiers" "service_prices" "contract_line_discounts" "discounts"
|
|
"client_billing_cycles" "client_billing_settings"
|
|
"contract_line_services" "contract_lines" "contracts"
|
|
|
|
# Contract line presets
|
|
"contract_line_preset_fixed_config" "contract_line_preset_services" "contract_line_presets"
|
|
|
|
# Contract templates (must be deleted before contracts)
|
|
"contract_template_compare_view" "contract_template_line_defaults"
|
|
"contract_template_line_fixed_config" "contract_template_line_service_bucket_config"
|
|
"contract_template_line_service_configuration" "contract_template_line_service_hourly_config"
|
|
"contract_template_line_service_usage_config" "contract_template_line_services"
|
|
"contract_template_line_terms" "contract_template_lines"
|
|
"contract_template_pricing_schedules" "contract_template_services" "contract_templates"
|
|
|
|
# Client details (must come before clients)
|
|
"client_tax_rates" "client_tax_settings" "client_inbound_email_domains"
|
|
"client_name_aliases"
|
|
"tenant_companies"
|
|
|
|
# Project/task entities
|
|
"project_tasks" "project_phases" "project_status_mappings"
|
|
|
|
# Workflow entities
|
|
"workflow_tasks" "workflow_executions" "workflow_events" "workflow_event_processing"
|
|
"workflow_registration_versions" "workflow_triggers" "workflow_form_definitions"
|
|
"workflow_task_definitions"
|
|
|
|
# === LEVEL 3: Mid-level entities ===
|
|
# Document folder templates: items and init rows reference document_folder_templates
|
|
# (CASCADE / SET NULL), so we delete the children first for a clean trail.
|
|
"document_folder_template_items" "document_entity_folder_init" "document_folder_templates"
|
|
# Document associations must come before documents
|
|
"document_associations"
|
|
|
|
# Assets must come after asset details
|
|
"asset_maintenance_schedules" "assets"
|
|
|
|
# Contract Lines
|
|
"contract_lines" "payment_methods"
|
|
|
|
# Interactions must come BEFORE tickets (tickets reference interactions in some cases)
|
|
"interactions" "interaction_types"
|
|
|
|
# Schedule entries
|
|
"schedule_entries"
|
|
|
|
# Service catalog
|
|
"service_catalog" "service_types" "service_categories"
|
|
|
|
# Settings that might be referenced
|
|
"approval_thresholds"
|
|
|
|
# Conditional display rules must come BEFORE invoice_templates
|
|
"conditional_display_rules"
|
|
|
|
# === LEVEL 4: Core business entities ===
|
|
# Invoice templates (after conditional_display_rules)
|
|
"invoice_templates"
|
|
|
|
# Invoices (after invoice_templates)
|
|
"invoices"
|
|
|
|
# Projects
|
|
"projects"
|
|
|
|
# External files and documents
|
|
"external_files" "documents" "document_types"
|
|
|
|
# Workflow templates
|
|
"workflow_registrations" "workflow_templates" "workflow_template_categories"
|
|
|
|
# === Ticket close rules (2026-06-10) ===
|
|
# All seven FK to tickets / boards / statuses, so they must be deleted
|
|
# BEFORE those. Internal order follows the FKs among them:
|
|
# ticket_auto_close_state → board_auto_close_rules, and
|
|
# checklist_template_items / _apply_rules → checklist_templates.
|
|
"ticket_auto_close_state" "board_auto_close_rules"
|
|
"ticket_checklist_items"
|
|
"checklist_template_apply_rules" "checklist_template_items" "checklist_templates"
|
|
"board_close_rules"
|
|
|
|
# === LEVEL 5: Tickets and related ===
|
|
# Tickets MUST be deleted BEFORE categories, statuses, etc that it references
|
|
# AND BEFORE client_locations that tickets reference via location_id
|
|
"tickets"
|
|
|
|
# === LEVEL 6: Client locations (referenced by tickets.location_id) ===
|
|
# Must be deleted AFTER tickets
|
|
"client_locations"
|
|
|
|
# === LEVEL 6: Lookup tables referenced by tickets ===
|
|
# These can only be deleted AFTER tickets
|
|
"categories"
|
|
"standard_statuses" "statuses"
|
|
"priorities" "severities" "urgencies" "impacts"
|
|
|
|
# === LEVEL 7: Boards (referenced by categories) ===
|
|
# Boards must be deleted AFTER categories (renamed from channels)
|
|
"boards"
|
|
|
|
# === LEVEL 8: Breaking circular dependencies ===
|
|
# There's a complex circular dependency:
|
|
# - users.contact_id → contacts (with ON DELETE SET NULL that fails on NOT NULL constraint)
|
|
# - contacts.client_id → clients
|
|
# - clients.account_manager → users
|
|
|
|
# Tax configuration (no dependencies on core entities)
|
|
"tax_components" "tax_rates" "tax_regions"
|
|
|
|
# Permissions and roles (must be deleted before users)
|
|
"permissions" "roles" "teams"
|
|
|
|
# The correct order to avoid constraint violations:
|
|
# 1. Delete clients first (after NULLing account_manager)
|
|
# 2. Delete contacts second (after NULLing client_id, before users that reference them)
|
|
# 3. Delete users last (they have NOT NULL contact_id that references contacts)
|
|
|
|
"clients" # Delete clients FIRST (after NULLing account_manager references)
|
|
"contacts" # Delete contacts SECOND (after clients, before users that have NOT NULL contact_id)
|
|
"users" # Delete users LAST (they have NOT NULL contact_id → contacts)
|
|
|
|
# SLA policies (referenced by clients.sla_policy_id and boards.sla_policy_id - must come after both)
|
|
"sla_policies"
|
|
# Business hours (referenced by sla_policies.business_hours_schedule_id - must come after sla_policies)
|
|
"business_hours_schedules"
|
|
|
|
# === LEVEL 7: Configuration and settings ===
|
|
# API and auth
|
|
"mobile_auth_otts" "mobile_refresh_tokens"
|
|
"api_keys" "portal_invitations" "password_reset_tokens"
|
|
"portal_domain_session_otts" "portal_domains"
|
|
|
|
# Policies and resources
|
|
"policies" "resources"
|
|
|
|
# Email configuration
|
|
"google_email_provider_config" "microsoft_email_provider_config"
|
|
"email_provider_health" "email_provider_configs" "email_providers"
|
|
"email_templates" "email_domains" "tenant_email_settings"
|
|
|
|
# Storage configuration
|
|
"storage_records" "storage_schemas" "storage_usage"
|
|
"storage_configurations" "storage_providers"
|
|
"ext_storage_records" "ext_storage_schemas" "ext_storage_usage"
|
|
|
|
# Templates and layouts
|
|
"tenant_email_templates" "template_sections"
|
|
"approval_levels" # After approval_thresholds
|
|
|
|
# Custom fields and attributes
|
|
"attribute_definitions" "custom_fields"
|
|
"layout_blocks" "tag_definitions" "custom_task_types"
|
|
|
|
# Time period settings (tenant_time_period_settings must come BEFORE time_period_types)
|
|
"tenant_time_period_settings"
|
|
"time_periods" "time_period_types" "time_period_settings"
|
|
|
|
# External entity mappings and tax
|
|
"external_entity_mappings" "external_tax_imports"
|
|
|
|
# Tenant notification settings
|
|
"tenant_internal_notification_category_settings" "tenant_internal_notification_subtype_settings"
|
|
"tenant_notification_category_settings" "tenant_notification_subtype_settings"
|
|
|
|
# Other tenant settings
|
|
"tenant_telemetry_settings"
|
|
"tenant_external_entity_mappings" "telemetry_consent_log"
|
|
"default_billing_settings" "notification_settings"
|
|
# inbound_email_rules references inbound_ticket_defaults (fallback destination)
|
|
"inbound_email_rules"
|
|
"inbound_ticket_defaults" "user_type_rates" "next_number"
|
|
"event_catalog" "provider_events"
|
|
|
|
# Tenant settings last
|
|
"tenant_settings"
|
|
]
|
|
|
|
let config = get-db-config $environment
|
|
let conn_str = build-connection-string $config
|
|
|
|
mut total_deleted = 0
|
|
mut tables_affected = 0
|
|
|
|
# First, break circular dependencies by NULLing foreign keys
|
|
if not $is_dry_run {
|
|
print "Breaking circular dependencies..."
|
|
|
|
# The circular dependency chain:
|
|
# clients.account_manager_id → users.user_id
|
|
# users.contact_id → contacts.contact_id (NOT NULL constraint!)
|
|
# contacts.client_id → clients.client_id
|
|
|
|
# Step 1: NULL out account_manager_id in clients to break clients → users dependency
|
|
try {
|
|
let null_query = "UPDATE clients SET account_manager_id = NULL WHERE tenant = '" + $tenant_id + "'"
|
|
execute-sql $null_query --env-name $environment
|
|
print " Cleared account_manager_id references in clients"
|
|
} catch {
|
|
# Ignore if column doesn't exist or already NULL
|
|
}
|
|
|
|
# Step 2: NULL out client_id in contacts to break contacts → clients dependency
|
|
try {
|
|
let null_query = "UPDATE contacts SET client_id = NULL WHERE tenant = '" + $tenant_id + "'"
|
|
execute-sql $null_query --env-name $environment
|
|
print " Cleared client_id references in contacts"
|
|
} catch {
|
|
# Ignore if column doesn't exist or already NULL
|
|
}
|
|
|
|
# Step 3: NULL out client_id in inbound_ticket_defaults to break the
|
|
# inbound_ticket_defaults → clients dependency. inbound_ticket_defaults
|
|
# is deleted late in the order, so this lets clients be deleted first.
|
|
try {
|
|
let null_query = "UPDATE inbound_ticket_defaults SET client_id = NULL WHERE tenant = '" + $tenant_id + "'"
|
|
execute-sql $null_query --env-name $environment
|
|
print " Cleared client_id references in inbound_ticket_defaults"
|
|
} catch {
|
|
# Ignore if column doesn't exist or already NULL
|
|
}
|
|
|
|
# Note: We cannot NULL users.contact_id because it has a NOT NULL constraint
|
|
# Instead, we'll delete in the order: clients → contacts → users
|
|
# This way contacts are deleted before users tries to reference them
|
|
}
|
|
|
|
# Delete from each table
|
|
for table in $tables {
|
|
# Check if table has tenant column
|
|
let check_query = (
|
|
"SELECT column_name " +
|
|
"FROM information_schema.columns " +
|
|
"WHERE table_name = '" + $table + "' " +
|
|
"AND column_name IN ('tenant', 'tenant_id') " +
|
|
"AND table_schema = 'public' " +
|
|
"LIMIT 1"
|
|
)
|
|
|
|
let column_result = execute-sql $check_query --env-name $environment
|
|
|
|
if ($column_result | length) > 0 {
|
|
let column_name = $column_result | first
|
|
|
|
# Count records
|
|
let count_query = "SELECT COUNT(*) FROM " + $table + " WHERE " + $column_name + " = '" + $tenant_id + "'"
|
|
let count_result = execute-sql $count_query --env-name $environment | first
|
|
let count = if ($count_result | is-empty) { 0 } else { $count_result | into int }
|
|
|
|
if $count > 0 {
|
|
if $is_dry_run {
|
|
print $" Would delete ($count) records from ($table)"
|
|
} else {
|
|
let delete_query = "DELETE FROM " + $table + " WHERE " + $column_name + " = '" + $tenant_id + "'"
|
|
execute-sql $delete_query --env-name $environment
|
|
print $" Deleted ($count) records from ($table)"
|
|
}
|
|
$total_deleted = $total_deleted + $count
|
|
$tables_affected = $tables_affected + 1
|
|
}
|
|
}
|
|
}
|
|
|
|
# Handle tenant record
|
|
if not $preserve_tenant {
|
|
if $is_dry_run {
|
|
print " Would delete tenant record from tenants table"
|
|
} else {
|
|
let delete_tenant_query = "DELETE FROM tenants WHERE tenant = '" + $tenant_id + "'"
|
|
execute-sql $delete_tenant_query --env-name $environment
|
|
print " Deleted tenant record from tenants table"
|
|
}
|
|
$total_deleted = $total_deleted + 1
|
|
$tables_affected = $tables_affected + 1
|
|
} else {
|
|
print " Preserving tenant record in tenants table (as requested)"
|
|
}
|
|
|
|
print ""
|
|
print ("=" | fill -w 60)
|
|
print "CLEANUP SUMMARY"
|
|
print ("=" | fill -w 60)
|
|
let mode_text = if $is_dry_run { "DRY RUN" } else { "ACTUAL DELETION" }
|
|
print $"Mode: ($mode_text)"
|
|
print $"Tenant: ($tenant_id) \(($tenant.client_name)\)"
|
|
print $"Tables affected: ($tables_affected)"
|
|
let action_text = if $is_dry_run { "to delete" } else { "deleted" }
|
|
print $"Estimated total records ($action_text): ($total_deleted)"
|
|
|
|
if $is_dry_run {
|
|
print "\n*** This was a DRY RUN - no data was actually deleted ***"
|
|
print "*** Add --execute flag to actually delete data ***"
|
|
} else {
|
|
print "\n*** Data has been PERMANENTLY DELETED ***"
|
|
}
|
|
}
|
|
|
|
# Show help
|
|
def main [] {
|
|
print "Tenant Cleanup Tool for Alga PSA\n"
|
|
print "Commands:"
|
|
print " list List all tenants"
|
|
print " inspect <tenant-id> Inspect tenant data"
|
|
print " cleanup <tenant-id> Cleanup tenant (dry-run by default)"
|
|
print ""
|
|
print "Options:"
|
|
print " --environment <local|production> Environment to use (default: local)"
|
|
print " --execute Actually delete data (for cleanup command)"
|
|
print " --preserve-tenant Keep the tenant record itself"
|
|
print " --force Skip confirmation prompts"
|
|
print ""
|
|
print "Examples:"
|
|
print " nu cli/cleanup-tenant.nu list"
|
|
print " nu cli/cleanup-tenant.nu list --environment production"
|
|
print " nu cli/cleanup-tenant.nu inspect 12345678-1234-1234-1234-123456789012"
|
|
print " nu cli/cleanup-tenant.nu cleanup 12345678-1234-1234-1234-123456789012"
|
|
print " nu cli/cleanup-tenant.nu cleanup 12345678-1234-1234-1234-123456789012 --execute"
|
|
print ""
|
|
print "Workflow:"
|
|
print " 1. List tenants to identify test ones: nu cli/cleanup-tenant.nu list --environment production"
|
|
print " 2. Inspect a tenant: nu cli/cleanup-tenant.nu inspect <id> --environment production"
|
|
print " 3. Dry run first: nu cli/cleanup-tenant.nu cleanup <id> --environment production"
|
|
print " 4. Execute if safe: nu cli/cleanup-tenant.nu cleanup <id> --environment production --execute"
|
|
}
|