Script to manage searching, backing up, and collecting infinite clipboard history from the Alfred Clipboard History on macOS.

alfred-clipboard.sh

#!/usr/bin/env bash
# This is a script that provides infinite history to get around Alfred's 3-month limit.
# It works by regularly backing up and appending the items in the alfred db to a
# sqlite database in the user's home folder. It also provides search functionality.

# https://www.alfredforum.com/topic/10969-keep-clipboard-history-forever/?tab=comments#comment-68859
# https://www.reddit.com/r/Alfred/comments/cde29x/script_to_manage_searching_backing_up_and/

# Example Usage:
#    alfred-clipboard.sh backup
#    alfred-clipboard.sh status
#    alfred-clipboard.sh shell
#    alfred-clipboard.sh dump > ~/Desktop/clipboard_db.sqlite3
#    alfred-clipboard.sh search 'some_string' --separator=, --limit=2 --fields=ts,item,app

shopt -s extglob
set +o pipefail

# *************************************************************************
# --------------------------- Why this exists -----------------------------
# *************************************************************************

# I'd be willing to pay >another $30 on top of my existing Legendary license for 
# unlimited clipboard history, and I fully accept any CPU/Memory hit necessary to get it.
#
# I use Clipboard History as a general buffer for everything in my life, 
# and losing everything beyond 3 months is a frequent source of headache.  
# Here's a small sample of a few recent things I've lost due to history expiring:
#
#  - flight confirmation details
#  - commit summaries with commit ids (detached commits that are hard to find due to deleted branches)
#  - important UUIDs
#  - ssh public keys
#  - many many many file paths (lots of obscure config file paths that I never bother to remember)
#  - entire config files 
#  - blog post drafts
#  - comments on social media
#  - form fields on websites
#
# It's always stuff that I don't realize at the time would be important later
# so it would be pointless to try and use snippets to solve this issue.
#
# Having a massive index of every meaningful string that's passed through my 
# brain is incredibly useful. In fact I rely on it so much that I'd even 
# willing to manage an entire separate server with Elasticsearch/Redis 
# full-text search to handle storage and indexing beyond 3 months (if 
# that's really what it takes to keep history indefinitely).
#
# If needed you could hide "6 months" "12 months" and "unlimited" behind an 
# "Advanced settings" pane and display a big warning about potential performance 
# downsides.
#
# For now I just periodically back up `~/Library/Application Support/Alfred/Databases/clipboard.alfdb` 
# to a separate folder, and merge the rows in it with a main database.  This at 
# least allows me to query further back by querying the merged database directly.
#  Maybe I'll build a workflow to do that if I have time, but no promises.
#
# I've created a script that handles the backup of the db, merging it with an
# infinite-history sqlite db in my home folder, and searching functionality.
# https://gist.github.com/pirate/6551e1c00a7c4b0c607762930e22804c
#
# I also tried hacking around the limit by changing the Alfred binary directly
# but unfortunately I was only able to find the limit in the .nib file (which
# is useless as it's just the GUI definition).
# I'd have to properly decompile Alfred it to find the actual limit logic...
# $ ggrep --byte-offset --only-matching --text '3 Months' \
#       '/Applications/Alfred.app/Contents/Frameworks/Alfred Framework.framework/Versions/A/Resources/AlfredFeatureClipboard.nib'
# 12590:3 Months
#
# (Now I just have to convince the Google Chrome team to also allow storing 
# browser history longer than 3 months... then the two biggest sources of 
# data-loss pain in my life will be eliminated).


# *************************************************************************
# --------------------------- Config Options ------------------------------
# *************************************************************************

BACKUP_DATA_DIR="${BACKUP_DATA_DIR:-$HOME/Clipboard}"
ALFRED_DATA_DIR="${ALFRED_DATA_DIR:-$HOME/Library/Application Support/Alfred/Databases}"
ALFRED_DB_NAME="${ALFRED_DB_NAME:-clipboard.alfdb}"
BACKUP_DB_NAME="${BACKUP_DB_NAME:-$(date +'%Y-%m-%d_%H:%M:%S').sqlite3}"
MERGED_DB_NAME="${MERGED_DB_NAME:-all.sqlite3}"

# uncomment the second option if you also to store the duplicate item history
# entries for whenever the same value was copied again at a different time
UNIQUE_FILTER="${UNIQUE_FILTER:-'latest.item = item'}"
# UNIQUE_FILTER="${UNIQUE_FILTER:-'latest.item = item AND latest.ts = ts'}"


# *************************************************************************
# -------------------------------------------------------------------------
# *************************************************************************


ALFRED_DB="$ALFRED_DATA_DIR/$ALFRED_DB_NAME"
BACKUP_DB="$BACKUP_DATA_DIR/$BACKUP_DB_NAME"
MERGED_DB="$BACKUP_DATA_DIR/$MERGED_DB_NAME"
MERGE_QUERY="
    /* Delete any items that are the same in both databases */
    DELETE FROM merged_db.clipboard
        WHERE EXISTS(
            SELECT 1 FROM latest_db.clipboard latest
            WHERE $UNIQUE_FILTER
        );

    /* Insert all items from the latest_db backup  */
    INSERT INTO merged_db.clipboard
        SELECT * FROM latest_db.clipboard;
"

# clipboard timestamps are in Mac epoch format (Mac epoch started on 1/1/2001, not 1/1/1970)
# to convert them to standard UTC UNIX timestamps, add 978307200
# 2 months = 60 days * 24 hr * 60 min * 60 sec = 5184000 seconds
# for all rows WHERE ts <(current_timestamp - 5184000) aka older than 2 months ago, set ts = ts + (60 * 60 * 24 * 30) aka one month ago
BUMP_QUERY="
    UPDATE
        ts = ts + 2592000
    WHERE
        ts < (current_timestamp - 5184000)

    TODO finish this
"



backup_rows=0
existing_rows=0
merged_rows=0

function backup_alfred_db {
    echo "[+] Backing up Alfred Clipboard History DB..."
    cp "$ALFRED_DB" "$BACKUP_DB"
    backup_rows=$(sqlite3 "$BACKUP_DB" 'select count(*) from clipboard;')
    echo "    √ Read     $backup_rows items from $ALFRED_DB_NAME"
    echo "    √ Wrote    $backup_rows items to $BACKUP_DB_NAME"
}

function init_master_db {
    echo -e "\n[+] Initializing new clipboard database with $backup_rows items..."
    cp "$BACKUP_DB" "$MERGED_DB"
    echo "    √ Copied new db $MERGED_DB"
    echo
    sqlite3 "$MERGED_DB" ".schema" | sed 's/^/    /'
}

function update_master_db {
    existing_rows=$(sqlite3 "$MERGED_DB" 'select count(*) from clipboard;')

    echo -e "\n[*] Updating Master Clipboard History DB..."
    echo "    √ Read     $existing_rows existing items from "$(basename "$MERGED_DB")
    sqlite3 "$MERGED_DB" "
        attach '$MERGED_DB' as merged_db;
        attach '$BACKUP_DB' as latest_db;
        BEGIN;
        $MERGE_QUERY
        COMMIT;
        detach latest_db;
        detach merged_db;
    "
    merged_rows=$(sqlite3 "$MERGED_DB" 'select count(*) from clipboard;')
    new_rows=$(( merged_rows - existing_rows ))
    echo "    √ Merged   $backup_rows items from backup into Master DB"
    echo "    √ Added    $new_rows new items to Master DB"
    echo "    √ Wrote    $merged_rows total items to $MERGED_DB_NAME"
}

function bump_alfred_db_timestamps {
    echo -e "\n[+] Bumping timestamps in clipboard database so that older items don't expire..."
    echo "NOT YET IMPLEMENTED"
    exit 4
    # TODO
    sqlite3 "$ALFRED_DB" "
        BEGIN;
        $BUMP_QUERY
        COMMIT;
    "
}

# *************************************************************************
# -------------------------------------------------------------------------
# *************************************************************************

function summary {
    backup_rows=$(sqlite3 "$BACKUP_DB" 'select count(*) from clipboard;')
    existing_rows=$(sqlite3 "$MERGED_DB" 'select count(*) from clipboard;')
    merged_rows=$(sqlite3 "$MERGED_DB" 'select count(*) from clipboard;')
    echo "    Original   $ALFRED_DB ($backup_rows items)"
    echo "    Backup     $BACKUP_DB ($backup_rows items)"
    echo "    Master     $MERGED_DB ($merged_rows items)"
}

function backup {
    backup_alfred_db
    [[ -f "$MERGED_DB" ]] || init_master_db
    update_master_db

    echo -e "\n[√] Done backing up clipboard history."
    summary
}

function bump {
    backup_alfred_db
    bump_alfred_db_timestamps
    echo -e "\n[√] Done bumping clipboard history timestamps."
    summary
}

function print_help {
    echo "Usage: TODO"
}

function unrecognized {
    echo "Error: Unrecognized argument $1" >&2
    print_help
    exit 2
}

# *************************************************************************
# -------------------------------------------------------------------------
# *************************************************************************

function main {
    COMMAND=''
    declare -a ARGS=()
    declare -A KWARGS=( [style]='csv' [separator]="|" [fields]='item' [verbose]='' [limit]=10)

    mkdir -p "$BACKUP_DATA_DIR"

    while (( "$#" )); do
        case "$1" in
            help|-h|--help)
                COMMAND='help'
                print_help
                exit 0;;

            -v|--verbose)
                KWARGS[verbose]='yes'
                shift;;

            -j|--json)
                KWARGS[style]='json'
                shift;;

            --separator|--separator=*)
                if [[ "$1" == *'='* ]]; then
                    KWARGS[separator]="${1#*=}"
                else
                    shift
                    KWARGS[separator]="$1"
                fi
                shift;;

            -s|--style|-s=*|--style=*)
                if [[ "$1" == *'='* ]]; then
                    KWARGS[style]="${1#*=}"
                else
                    shift
                    KWARGS[style]="$1"
                fi
                shift;;

            -l|--limit|-l=*|--limit=*)
                if [[ "$1" == *'='* ]]; then
                    KWARGS[limit]="${1#*=}"
                else
                    shift
                    KWARGS[limit]="$1"
                fi
                shift;;

            -f|--fields|-f=*|--fields=*)
                if [[ "$1" == *'='* ]]; then
                    KWARGS[fields]="${1#*=}"
                else
                    shift
                    KWARGS[fields]="$1"
                fi
                shift;;

            +([a-z]))
                if [[ "$COMMAND" ]]; then
                    ARGS+=("$1")
                else
                    COMMAND="$1"
                fi
                shift;;

            --)
                shift;
                ARGS+=("$@")
                break;;
            *)
                [[ "$COMMAND" != "search" ]] && unrecognized "$1"
                ARGS+=("$1")
                shift;;
        esac
    done

    # echo "COMMAND=$COMMAND"
    # echo "ARGS=${ARGS[*]}"
    # for key in "${!KWARGS[@]}"; do
    #     echo "$key=${KWARGS[$key]}"
    # done

    if [[ "$COMMAND" == "status" ]]; then
        summary
    elif [[ "$COMMAND" == "backup" ]]; then
        backup
    elif [[ "$COMMAND" == "shell" ]]; then
        sqlite3 "$MERGED_DB"
    elif [[ "$COMMAND" == "dump" ]]; then
        sqlite3 "$MERGED_DB" ".dump"
    elif [[ "$COMMAND" == "bump" ]]; then
        bump_alfred_db_timestamps
    elif [[ "$COMMAND" == "search" ]]; then
        if [[ "${KWARGS[style]}" == "json" ]]; then
            sqlite3 "$MERGED_DB" "
                SELECT '{\"items\": [' || group_concat(match) || ']}'
                FROM (
                    SELECT json_object(
                        'valid', 1,
                        'uuid', ts,
                        'title', substr(item, 1, 120),
                        'arg', item
                    ) as match
                    FROM clipboard
                    WHERE item LIKE '%${ARGS[*]}%'
                    ORDER BY ts DESC
                    LIMIT ${KWARGS[limit]}
                );
            "
        else
            sqlite3 -separator "${KWARGS[separator]}" "$MERGED_DB" "
                SELECT ${KWARGS[fields]}
                FROM clipboard
                WHERE item LIKE '%${ARGS[*]}%'
                ORDER BY ts DESC
                LIMIT ${KWARGS[limit]};
            "
        fi

    else
        unrecognized "$COMMAND"
    fi
}

main "$@"