s3 bucket and db cleanup

[ducknoodledance]

It seemed like my minio s3 bucket was getting bigger and bigger and the system was not cleaning up after itself efficiently.

I don't care much for a trash and restore feature in the app as I do in my disk size not running away with orphaned videos and database not ballooning.

So after a bunch of questions to my LLM, it seemed that the easiest thing to do for now until the system evolves to be leaner is to create a helper python script that removes extra junk to clean up the system every once in a while (I have it set to weekly).

I am running it in a docker container alongside the others. Since I am a non techy I am not sure if this code snip will be helpful to anyone, but I wanted to posted here anyways, just in case it helps anyone with direction as its code that seems to work as intended. Sorry if its AI slop, my LLM wrote it and I dont know my way enough python to know how solid it is, but it seems to be working fine for me.

#!/usr/bin/env python3
import os
import sys
import time
import boto3
import psycopg2
from botocore.client import Config

# Operational Control Safety Brake
DRY_RUN = os.environ.get("CLEANER_DRY_RUN", "True").lower() in ("true", "1", "yes")

# Database Connection Constants
DB_USER = os.environ.get("POSTGRES_USER", "freeframe")
DB_PASS = os.environ.get("POSTGRES_PASSWORD", "freeframe")
DB_NAME = os.environ.get("POSTGRES_DB", "freeframe")
DB_HOST = "postgres"
DB_PORT = "5432"

# MinIO S3 Connection Constants
S3_ENDPOINT = os.environ.get("S3_ENDPOINT", "http://minio:9000")
S3_ACCESS = os.environ.get("S3_ACCESS_KEY")
S3_SECRET = os.environ.get("S3_SECRET_KEY")
S3_BUCKET = os.environ.get("S3_BUCKET", "freeframe")

SLEEP_INTERVAL_SECONDS = 7 * 24 * 60 * 60  # Wake up every 7 days

def get_active_db_records():
    """Gets all valid asset and project IDs currently alive in the database."""
    try:
        conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST, port=DB_PORT)
        cursor = conn.cursor()
        
        cursor.execute("SELECT id::text FROM assets;")
        active_assets = set(row[0] for row in cursor.fetchall())
        
        cursor.execute("SELECT id::text FROM projects;")
        active_projects = set(row[0] for row in cursor.fetchall())
        
        cursor.close()
        conn.close()
        return active_assets, active_projects
    except Exception as e:
        print(f"[!] Failed to fetch active DB index map: {str(e)}")
        return set(), set()

def get_soft_deleted_targets():
    """Finds items explicitly marked as deleted in the current DB schema."""
    try:
        conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST, port=DB_PORT)
        cursor = conn.cursor()
        
        cursor.execute("""
            SELECT a.id FROM assets a
            LEFT JOIN projects p ON a.project_id = p.id
            LEFT JOIN folders f ON a.folder_id = f.id
            WHERE a.deleted_at IS NOT NULL OR p.deleted_at IS NOT NULL OR f.deleted_at IS NOT NULL;
        """)
        asset_ids = [row[0] for row in cursor.fetchall()]
        
        cursor.execute("""
            SELECT f.id FROM folders f
            LEFT JOIN projects p ON f.project_id = p.id
            WHERE f.deleted_at IS NOT NULL OR p.deleted_at IS NOT NULL;
        """)
        folder_ids = [row[0] for row in cursor.fetchall()]
        
        s3_keys = set()
        if asset_ids:
            cursor.execute("""
                SELECT mf.s3_key_raw, mf.s3_key_processed, mf.s3_key_thumbnail
                FROM media_files mf
                JOIN asset_versions av ON mf.version_id = av.id
                WHERE av.asset_id IN %s;
            """, (tuple(asset_ids),))
            for row in cursor.fetchall():
                for key in row:
                    if key:
                        s3_keys.add(key)
                        
        cursor.close()
        conn.close()
        return asset_ids, folder_ids, list(s3_keys)
    except Exception as e:
        print(f"[!] Soft-delete compilation query dropped: {str(e)}")
        return [], [], []

def execute_hard_database_purge(asset_ids, folder_ids):
    if not asset_ids and not folder_ids:
        return
    param = tuple(asset_ids) if asset_ids else ()
    conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST, port=DB_PORT)
    
    if DRY_RUN:
        print("\n=== my DIAGNOSTIC DATABASE AUDIT (DRY RUN MODE) ===")
        cursor = conn.cursor()
        if asset_ids:
            cursor.execute("SELECT COUNT(*) FROM assets WHERE id IN %s;", (param,))
            print(f"[DRY RUN] Database Match: {cursor.fetchone()[0]} rows targeted for deletion in [Master Asset Rows]")
        if folder_ids:
            cursor.execute("SELECT COUNT(*) FROM folders WHERE id IN %s;", (tuple(folder_ids),))
            print(f"[DRY RUN] Database Match: {cursor.fetchone()[0]} rows targeted for deletion in [Master Folder Rows]")
        cursor.close()
    else:
        print(f"\n=== my LIVE DATABASE CASCADE PURGE ===")
        if asset_ids:
            asset_queries = [
                ("Ghost Canvas Annotations", "DELETE FROM annotations WHERE comment_id IN (SELECT id FROM comments WHERE asset_id IN %s);"),
                ("Ghost Comment Attachments", "DELETE FROM comment_attachments WHERE comment_id IN (SELECT id FROM comments WHERE asset_id IN %s);"),
                ("Ghost Emoji Reactions", "DELETE FROM comment_reactions WHERE comment_id IN (SELECT id FROM comments WHERE asset_id IN %s);"),
                ("Ghost User Mentions", "DELETE FROM mentions WHERE comment_id IN (SELECT id FROM comments WHERE asset_id IN %s);"),
                ("Ghost Asset Comments", "DELETE FROM comments WHERE asset_id IN %s;"),
                ("Ghost Notifications", "DELETE FROM notifications WHERE asset_id IN %s;"),
                ("Ghost Approval Badges", "DELETE FROM approvals WHERE asset_id IN %s;"),
                ("Ghost Share Activities", "DELETE FROM share_link_activities WHERE share_link_id IN (SELECT id FROM share_links WHERE asset_id IN %s);"),
                ("Ghost Share Items", "DELETE FROM share_link_items WHERE asset_id IN %s;"),
                ("Ghost Share Links", "DELETE FROM share_links WHERE asset_id IN %s;"),
                ("Ghost System Activity Logs", "DELETE FROM activity_logs WHERE asset_id IN %s;"),
                ("Ghost Transcode Records", "DELETE FROM media_files WHERE version_id IN (SELECT id FROM asset_versions WHERE asset_id IN %s);"),
                ("Ghost Asset Versions", "DELETE FROM asset_versions WHERE asset_id IN %s;"),
                ("Master Asset Rows", "DELETE FROM assets WHERE id IN %s;")
            ]
            for label, query in asset_queries:
                try:
                    cursor = conn.cursor()
                    cursor.execute(query, (param,))
                    conn.commit()
                    cursor.close()
                except Exception:
                    conn.rollback()
            print("[+] Complete soft-deleted database relational tree scrubbed.")
        
        if folder_ids:
            try:
                cursor = conn.cursor()
                cursor.execute("UPDATE folders SET parent_id = NULL WHERE id IN %s;", (tuple(folder_ids),))
                cursor.execute("DELETE FROM folders WHERE id IN %s;", (tuple(folder_ids),))
                conn.commit()
                cursor.close()
                print("[+] Complete soft-deleted empty folder configurations cleared.")
            except Exception:
                conn.rollback()
    conn.close()

def hunt_and_kill_s3_orphans(active_assets, active_projects):
    """Scans the physical S3 bucket and drops any keys belonging to dead or wiped installations."""
    print(f"\n=== my UNTRACKED S3 ORPHAN HUNT ===")
    s3 = boto3.client("s3", endpoint_url=S3_ENDPOINT, aws_access_key_id=S3_ACCESS, aws_secret_access_key=S3_SECRET, config=Config(signature_version="s3v4"), region_name="us-east-1")
    
    try:
        paginator = s3.get_paginator('list_objects_v2')
        pages = paginator.paginate(Bucket=S3_BUCKET)
        
        orphan_keys = []
        for page in pages:
            if 'Contents' not in page:
                continue
            for obj in page['Contents']:
                key = obj['Key']
                parts = key.split('/')
                
                # FreeFrame layout path: root_folder/project_uuid/asset_uuid/...
                if len(parts) >= 3 and parts[0] in ('raw', 'processed', 'posters'):
                    project_uuid = parts[1]
                    asset_uuid = parts[2]
                    
                    # If the project or the asset folder hash doesn't exist in the active DB, it's a zombie file
                    if (project_uuid not in active_projects) or (parts[0] in ('raw', 'processed') and asset_uuid not in active_assets):
                        orphan_keys.append(key)
        
        if not orphan_keys:
            print("[+] Zero untracked S3 orphan files found hiding in storage.")
            return
            
        print(f"[*] Detected {len(orphan_keys)} physical files left behind from previous broken installs/wipes.")
        
        purged = 0
        for key in orphan_keys:
            if DRY_RUN:
                print(f"[DRY RUN] Would erase unindexed zombie file: s3://{S3_BUCKET}/{key}")
            else:
                try:
                    s3.delete_object(Bucket=S3_BUCKET, Key=key)
                    purged += 1
                except Exception as e:
                    print(f"[!] S3 failed to drop orphan key {key}: {str(e)}")
        if not DRY_RUN:
            print(f"[+] Reclaimed space by permanently shredding {purged} untracked zombie S3 objects.")
            
    except Exception as e:
        print(f"[!] S3 Object Crawler failed: {str(e)}")

def purge_explicit_deleted_keys(keys):
    if not keys:
        return
    s3 = boto3.client("s3", endpoint_url=S3_ENDPOINT, aws_access_key_id=S3_ACCESS, aws_secret_access_key=S3_SECRET, config=Config(signature_version="s3v4"), region_name="us-east-1")
    purged_count = 0
    for key in keys:
        if DRY_RUN:
            print(f"[DRY RUN] Would erase soft-deleted path: s3://{S3_BUCKET}/{key}")
        else:
            try:
                s3.delete_object(Bucket=S3_BUCKET, Key=key)
                purged_count += 1
            except Exception:
                pass
    if not DRY_RUN:
        print(f"[+] Wiped {purged_count} explicitly soft-deleted S3 tracking files.")

if __name__ == "__main__":
    while True:
        print(f"\n=== my DUAL-ENGINE SYSTEM COMPLIANCE PURGE START ===")
        
        # Phase 1: Handle explicitly deleted items in the current system context
        asset_ids, folder_ids, target_keys = get_soft_deleted_targets()
        purge_explicit_deleted_keys(target_keys)
        execute_hard_database_purge(asset_ids, folder_ids)
        
        # Phase 2: Reverse-audit the files system to drop tracking leak ghosts from old system wipes
        active_assets, active_projects = get_active_db_records()
        if active_assets or active_projects:
            hunt_and_kill_s3_orphans(active_assets, active_projects)
            
        print(f"\n=== SWEEP END. ENTERING COLD STORAGE KERNEL SLEEP FOR 7 DAYS ===")
        time.sleep(SLEEP_INTERVAL_SECONDS)

[ravirajsinh45]

Thanks for the detailed writeup and the script @ducknoodledance — your read is right: nothing in the codebase ever hard-deletes soft-deleted rows or their S3 objects, and ShareLink.expires_at is never enforced either. Filed as issue #65 to track adding a scheduled cleanup task + orphan sweeper.