Skip to content

Data Storage

Warning

Currently, all the data stored is in plain. This means no encryption is done on the content sent by the users. While this is fine as long as you deploy the bot only for your personal groups, it can have unwanted implications if it is shared publicly.

Patrizio keeps three kinds of data:

  • Filters and their metadata - stored in a pure-Go SQLite database (modernc.org/sqlite). The repository lives in internal/adapter/sqlite/repository.go. It uses queries generated by sqlc from the .sql files under internal/database/queries.

  • Conversation messages - stored in the same SQLite database. The conversation_messages table tracks every message exchanged through the /prompt feature, including the role (user or assistant), content, and parent message pointers for thread reconstruction. The repository lives in internal/adapter/sqlite/conversation.go.

  • Media files - stored on the local filesystem via afero. The storage adapter in internal/adapter/storage/storage.go simply reads and writes files. It writes to the directory defined by Config.MediaPath().

SQLite Repository

The repository is instantiated in cmd/patrizio/main.go. sqlite.New(db) returns a *sqlite.Repository that implements the FilterRepository interface. It provides CRUD helpers such as FindMatchingFilters and CreateFilter.

The schema itself is defined by goose migrations in the migrations/ directory. internal/database/database.go opens the database and database.Migrate applies any pending migrations.

Conversation Repository

sqlite.NewConversationRepository(db) returns a *sqlite.ConversationRepository that implements domain.ConversationRepository. It provides three operations: saving a message, checking whether a Delta Chat message ID belongs to an active conversation, and retrieving the full thread chain from a leaf message up to the root using a recursive CTE query. The chain is ordered chronologically and capped by openai_max_history.

Media Repository

The storage adapter uses afero.NewOsFs() to read/write files. When a media filter is added the bot writes the file to the configured media directory and stores its SHA-512 hash in the database. Later the handler looks up the file path with deps.MediaStorage.Path(filter.MediaHash).