#!/usr/bin/env python3 """ Generate detailed Apple Notes attachment report with actionable recommendations. This script creates: 1. HTML report with interactive filtering 2. CSV export for spreadsheet analysis 3. Deletion recommendations based on size and type """ import csv import sqlite3 from collections import defaultdict from datetime import datetime from pathlib import Path def get_human_readable_size(bytes_val): """Convert bytes to human readable format.""" for unit in ["B", "KB", "MB", "GB", "TB"]: if bytes_val < 1024: return f"{bytes_val:.2f} {unit}" bytes_val /= 1024 return f"{bytes_val:.2f} PB" def analyze_database(): """Extract attachment data from NoteStore.sqlite.""" db_path = Path.home() / "Library/Group Containers/group.com.apple.notes/NoteStore.sqlite" if not db_path.exists(): print(f"āŒ Database not found at {db_path}") return None try: conn = sqlite3.connect(str(db_path)) conn.row_factory = sqlite3.Row cursor = conn.cursor() query = """ SELECT obj.Z_PK AS object_id, obj.ZFILENAME AS filename, obj.ZTITLE AS title, obj.ZTYPEUTI AS mime_type, obj.ZFILESIZE AS file_size, obj.ZMODIFICATIONDATE AS modification_date, obj.ZNOTE AS note_fk, COALESCE(note.ZTITLE1, '(Untitled)') AS note_title, note.Z_PK AS note_id, note.ZIDENTIFIER AS note_uuid FROM ZICCLOUDSYNCINGOBJECT obj LEFT JOIN ZICCLOUDSYNCINGOBJECT note ON obj.ZNOTE = note.Z_PK WHERE obj.ZFILESIZE > 0 ORDER BY obj.ZFILESIZE DESC """ cursor.execute(query) results = [dict(row) for row in cursor.fetchall()] # Also fetch note identifiers for deep linking note_ids_query = """ SELECT Z_PK, ZIDENTIFIER, ZTITLE1 FROM ZICCLOUDSYNCINGOBJECT WHERE ZTYPE IS NULL AND ZTITLE1 IS NOT NULL """ cursor.execute(note_ids_query) note_identifiers = {row[0]: {"id": row[1], "title": row[2]} for row in cursor.fetchall()} conn.close() return results, note_identifiers except sqlite3.OperationalError as e: print(f"āŒ Cannot access database: {e}") print("\nYou need to grant Full Disk Access to Terminal:") print(" 1. System Settings > Security & Privacy > Privacy") print(" 2. Full Disk Access > + button > Terminal.app") return None def get_file_extension(mime_type): """Get common file extension from MIME type.""" mime_to_ext = { "public.jpeg": "jpg", "public.png": "png", "public.heic": "heic", "public.tiff": "tiff", "com.adobe.pdf": "pdf", "public.mpeg-4": "mp4", "com.apple.quicktime-movie": "mov", "com.apple.m4a-audio": "m4a", "public.data": "data", } return mime_to_ext.get(mime_type, mime_type.split(".")[-1] if mime_type else "unknown") def get_notes_app_deep_link(note_uuid): """Generate a deep link to open a note in the Notes app.""" if not note_uuid: return None return f"notes://showNote?identifier={note_uuid}" def categorize_by_type_size(attachments): """Identify large attachments by type for recommendations.""" by_type = defaultdict(lambda: {"count": 0, "total_size": 0, "files": []}) for att in attachments: mime_type = att.get("mime_type") or "unknown" size = att.get("file_size", 0) by_type[mime_type]["count"] += 1 by_type[mime_type]["total_size"] += size by_type[mime_type]["files"].append( { "filename": att.get("filename") or att.get("title") or f"Object_{att.get('object_id')}", "size": size, "note": att.get("note_title", "Untitled"), "object_id": att.get("object_id"), "note_uuid": att.get("note_uuid"), } ) return by_type def generate_csv_report(attachments, output_path): """Generate detailed CSV report.""" sorted_files = sorted(attachments, key=lambda x: x.get("file_size", 0), reverse=True) with open(output_path, "w", newline="", encoding="utf-8") as f: writer = csv.writer(f) writer.writerow( ["note_title", "attachment_name", "size_bytes", "size_human", "mime_type", "object_id"] ) for att in sorted_files: writer.writerow( [ att.get("note_title") or "(Untitled)", att.get("filename") or att.get("title") or f"Object_{att.get('object_id')}", att.get("file_size", 0), get_human_readable_size(att.get("file_size", 0)), att.get("mime_type", "unknown"), att.get("object_id"), ] ) print(f"āœ… CSV report: {output_path}") def generate_html_report(attachments, output_path): """Generate interactive HTML report grouped by note.""" total_size = sum(att.get("file_size", 0) for att in attachments) total_count = len(attachments) # Count unique file types file_types = set(att.get("mime_type", "unknown") for att in attachments) num_file_types = len(file_types) # Group attachments by note notes_dict = {} for att in attachments: note_title = att.get("note_title") or "(Untitled)" note_uuid = att.get("note_uuid") if note_title not in notes_dict: notes_dict[note_title] = {"uuid": note_uuid, "size": 0, "files": []} notes_dict[note_title]["size"] += att.get("file_size", 0) notes_dict[note_title]["files"].append( { "filename": att.get("filename") or att.get("title") or f"Object_{att.get('object_id')}", "size": att.get("file_size", 0), } ) # Sort notes by total size (largest first) sorted_notes = sorted(notes_dict.items(), key=lambda x: x[1]["size"], reverse=True) html = f""" Apple Notes Attachment Report

šŸ“± Apple Notes Attachment Analysis

Generated on {datetime.now().strftime("%Y-%m-%d %H:%M:%S")}

Files

{total_count:,}

Total Size

{get_human_readable_size(total_size)}

Types

{num_file_types}

Avg

{get_human_readable_size(total_size // max(total_count, 1))}

Notes by Storage

""" # Add notes grouped by size with their files for note_title, note_data in sorted_notes: note_uuid = note_data["uuid"] total_note_size = note_data["size"] files = sorted(note_data["files"], key=lambda x: x["size"], reverse=True) if note_uuid: note_link = get_notes_app_deep_link(note_uuid) note_display = f'{note_title}↗' else: note_display = note_title # Build file list (newline separated) files_text = "\n".join([f["filename"] for f in files]) html += f"""
{note_display}
{get_human_readable_size(total_note_size)}
{files_text}
""" html += """
""" with open(output_path, "w", encoding="utf-8") as f: f.write(html) print(f"āœ… HTML report: {output_path}") def main(): print("šŸ” Generating Apple Notes attachment report...\n") # Analyze database print("šŸ“š Analyzing database...") result = analyze_database() if result is None: return db_attachments, note_identifiers = result print(f" Found {len(db_attachments)} attachments\n") # Generate reports output_dir = Path.cwd() # CSV Report csv_path = output_dir / "notes_attachment_detailed.csv" generate_csv_report(db_attachments, csv_path) # HTML Report html_path = output_dir / "notes_attachment_report.html" generate_html_report(db_attachments, html_path) print("\n" + "=" * 80) print("āœ… Reports generated successfully!") print("=" * 80) print(f"\nšŸ“„ Files created:") print(f" 1. {html_path.name} - Open in your browser for interactive view") print(f" 2. {csv_path.name} - Import into spreadsheet for detailed analysis") print(f"\nšŸ’” Next steps:") print(f" 1. Review the HTML report to identify large attachments") print(f" 2. Use the CSV for sorting and filtering in Excel/Sheets") print(f" 3. Delete large attachments from Notes app") print(f" 4. Re-run analyze-notes-space.py to verify cleanup\n") if __name__ == "__main__": main()