Sqlite backed history for advanced capabilities

Start by reviewing the existing Get-History, Invoke-History, and Clear-History behavior and the current history storage approach. The issue lists many possible capabilities, so first establish the intended SQLite scope and command interface; done should mean an agreed, testable subset of database-backed history and search behavior.

Prerequisites

• Write a descriptive title.

Description of the new feature/enhancement

I work in a Linux/Windows hybrid environment where I try to minimize the cognitive load while keeping the security and usability in a reasonable amount. I recently started to use Bashhub and atuin to improve daily workflows on terminals. Even though I do not use the cloud synchronization, I experienced a database backed history provides alternatives without utilizing heavy grep or Select-String usage.

However, this (SQLite backend) would require either adding a Find-History or Search-History command or similar (I prefer the latter) to fully benefit from it, or improving Get-History command.

Currently, we handle basic search requirements with Get-History | Select-String -Pattern..., or parameters of Invoke-History or Clear-History but in order to make use of a database backend, a specific command makes more sense.

Proposed technical implementation details (optional)

Almost all of these can be achieved by using several PowerShell modules already. Yet, a database backend might provide a smoother experience and open way to further improvements.

• Cloud synchronization via OneDrive: Even though it can be provided via text file synchronization too, database synchronization can be provided with more advanced tools as records can have unique IDs minimizing the impact on merge.
• Fuzzy search: It can make use of SQLite extensions to provide fuzzy search on a lower level for performance and convenience.
• Search by system ran the command: When cloud synchronization exists, it can work by saving the computer name.
• Encrypted history: SQLite encryption extensions can provide encryption for privacy. If it is needed to record for security reasons especially for corporate environments, the current log system can provide the data needed when configured.
• Search by exit code/execution status: Find failed commands, commands raised exceptions, etc. without using Select-Object -Property...
• Search by directory: Find commands run in a specific directory (requires another field)
• Search by date: Find commands by StartExecutionTime and EndExecutionTime without using Select-Object -Property... .
• Pipe support: Even though it would be complicated to accept pipe input, it would be easier if it can pipe the result to Invoke-History or Clear-History.
• Deduplicate search results: If needed, the duplicated search results can be ignored by providing a -Unique parameter
• Ignore invalid lines: Due to accidents, it is possible for a user to paste hundreds of lines of text, either a piece of code or a JSON file content. It can be filtered out during search.
• Extended pipeline: Currently, PSReadLine captures some keywords and defaults to not writing them to history. This can be extendable as an add-on object applied before flushing into database. And it might be possible to add a filtering and/or enriching before writing into the database. For instance, encoded commands can be decoded before writing into database while setting a field like Encoded: $true.