| 483 | } |
| 484 | |
| 485 | func (db *DB) DeepClean(ctx context.Context) error { |
| 486 | err := db.WithContext(ctx).Transaction(func(tx *gorm.DB) error { |
| 487 | // Delete entries for users that have one device and are inactive |
| 488 | r := tx.Exec(` |
| 489 | CREATE TEMP TABLE temp_users_with_one_device AS ( |
| 490 | SELECT user_id |
| 491 | FROM devices |
| 492 | GROUP BY user_id |
| 493 | HAVING COUNT(DISTINCT device_id) = 1 |
| 494 | ) |
| 495 | `) |
| 496 | if r.Error != nil { |
| 497 | return fmt.Errorf("failed to create list of single device users: %w", r.Error) |
| 498 | } |
| 499 | r = tx.Exec(` |
| 500 | CREATE TEMP TABLE temp_inactive_users AS ( |
| 501 | SELECT user_id |
| 502 | FROM usage_data |
| 503 | WHERE last_used <= (now() - INTERVAL '180 days') |
| 504 | ) |
| 505 | `) |
| 506 | if r.Error != nil { |
| 507 | return fmt.Errorf("failed to create list of inactive users: %w", r.Error) |
| 508 | } |
| 509 | r = tx.Exec(` |
| 510 | DELETE FROM enc_history_entries WHERE |
| 511 | date <= (now() - INTERVAL '180 days') |
| 512 | AND user_id IN (SELECT * FROM temp_users_with_one_device) |
| 513 | AND user_id IN (SELECT * FROM temp_inactive_users) |
| 514 | `) |
| 515 | if r.Error != nil { |
| 516 | return fmt.Errorf("failed to delete entries for inactive users: %w", r.Error) |
| 517 | } |
| 518 | fmt.Printf("Ran deep clean for inactive users and deleted %d rows\n", r.RowsAffected) |
| 519 | return nil |
| 520 | }) |
| 521 | if err != nil { |
| 522 | return err |
| 523 | } |
| 524 | err = db.WithContext(ctx).Transaction(func(tx *gorm.DB) error { |
| 525 | // Delete entries for users that have tons and tons of entries (e.g. due to a mis-use or misconfiguration) |
| 526 | r := tx.Exec(` |
| 527 | CREATE TEMP TABLE users_with_too_many_entries AS ( |
| 528 | SELECT user_id |
| 529 | FROM enc_history_entries |
| 530 | GROUP BY user_id |
| 531 | HAVING count(*) > 50000000 |
| 532 | ) |
| 533 | `) |
| 534 | if r.Error != nil { |
| 535 | return fmt.Errorf("failed to create list of users with too many entries: %w", r.Error) |
| 536 | } |
| 537 | r = tx.Raw(` |
| 538 | DELETE FROM enc_history_entries WHERE |
| 539 | date <= (now() - INTERVAL '90 days') |
| 540 | AND user_id IN (SELECT * FROM users_with_too_many_entries) |
| 541 | `) |
| 542 | if r.Error != nil { |