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 "$@"

You can also pipe it into an Alfred Workflow JSON Script Filter using the --json flag like so:

For Alfred v3, the DB is located at $HOME/Library/Application Support/Alfred 3/Databases

By default, there is no such folder, $HOME/Clipboard. and will cause error when running alfred-clipboard.sh

Fixed both issues, thanks @seanlvjie!

Thanks for putting it up.

Some issues i ran into and fixes for future readers:

1. In start, i kept getting - No table named clipboard. I needed to run alfred-clipboard backup and then manually cp ~/Clipboard/<file.sqlite3> all.sqlite3 # to create a non-zero merged db.
2. Backup was duplicating rows and merging was seemingly not happening. i had to put UNIQUE_FILTER directly into the query, and it worked.

PS: @pirate, 0) The workflow you mentioned somehow isn't compatible on Alfred3 1) any automation you have to run daily backups? 2) What do you think about dumping every day's new clipboard items into a new 'bear.app' note (i do see in one of your screenshots that you use it) and use bear.app's search feature?

Edit: I will answer 0) question myself. I was able to get the workflow running for Alfred3 from scratch by looking at the above screenshot and follow. App icons aren’t showing up, but nbd.

Also, if something like this is seen:

prateeka@prateeka-C02XC9SRJG5J ➜  go-code.git git:(main) ~/alfred-clipboard.sh backup                 [11:12:58]
/Users/prateeka/alfred-clipboard.sh: line 192: declare: -A: invalid option
declare: usage: declare [-afFirtx] [-p] [name[=value] ...]

Upgrade your bash to v4.0+ by brew install bash and change the top line of the script to point to /usr/local/bin/bash.

@prat0318
Actually #!/usr/bin/env bash uses the first bash in your path and that should actually be the new one you installed with brew :)

But to be safe you can change it to where brew installed the newer bash.

@pirate any updates on the bumping timestamps in alfred db?

@pirate I have a lot of duplicates in the clipboard db. How do I remove them? In simple instructions please haha, this stuff is a bit overwhelming to keep up with 😅

The MERGE_QUERY or the UNIQUE_FILTER is not working for me, it didn't delete all records from the merged_db that are also in the incoming latest_db, it just merges them anyway. So I have modified the MERGE_QUERY to this:

MERGE_QUERY="
    /* Delete any items that are the same in both databases */
    DELETE FROM merged_db.clipboard
    WHERE item IN (SELECT item FROM latest_db.clipboard);

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

This ignores the 87th and 88th line's setting but it seems to be working for me, records whose content that has already been in the all.sqlite3 database will be deleted before all records in the incoming database get inserted.

I have also noticed that if you run the script with the status option, it creates an empty database file in the backup folder. That's also fixed in my fork here.