Database Schema

mahpastes uses SQLite for local data storage. The database file is clips.db stored in the platform-specific data directory.

Tables

clips

Stores all clipboard content.

CREATE TABLE clips (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content_type TEXT NOT NULL,
    data BLOB NOT NULL,
    filename TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_archived INTEGER DEFAULT 0,
    expires_at DATETIME,
    content_hash TEXT DEFAULT '',
    metadata TEXT DEFAULT '{}'
);

Column	Type	Description
id	INTEGER	Auto-incrementing primary key
content_type	TEXT	MIME type (e.g., "image/png", "text/plain")
data	BLOB	Raw binary content
filename	TEXT	Original filename (nullable)
created_at	DATETIME	Timestamp of creation
is_archived	INTEGER	0 = active, 1 = archived
expires_at	DATETIME	Auto-delete timestamp (nullable)
content_hash	TEXT	SHA-256 hash for duplicate detection (empty string default)
metadata	TEXT	JSON object of user-defined key-value pairs

Indexes:

• Primary key on id
• Index on content_hash for duplicate group queries

watched_folders

Configuration for folders being watched.

CREATE TABLE watched_folders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT NOT NULL UNIQUE,
    filter_mode TEXT NOT NULL DEFAULT 'all',
    filter_presets TEXT,
    filter_regex TEXT,
    process_existing INTEGER DEFAULT 0,
    auto_archive INTEGER DEFAULT 0,
    is_paused INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Column	Type	Description
id	INTEGER	Auto-incrementing primary key
path	TEXT	Absolute path to folder (unique)
filter_mode	TEXT	"all", "presets", or "custom"
filter_presets	TEXT	JSON array of preset names
filter_regex	TEXT	Regex pattern for custom filter
process_existing	INTEGER	Import existing files on add
auto_archive	INTEGER	Archive imports automatically
is_paused	INTEGER	Per-folder pause state
created_at	DATETIME	When folder was added

Constraints:

• path must be unique

Migrations:

ALTER TABLE watched_folders ADD COLUMN auto_tag_id INTEGER

Column	Type	Description
auto_tag_id	INTEGER	Tag ID to auto-apply on import (nullable)

settings

Application settings as key-value pairs.

CREATE TABLE settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

Column	Type	Description
key	TEXT	Setting name (primary key)
value	TEXT	Setting value

Current settings:

Key	Values	Description
global_watch_paused	"true" / "false"	Global watching pause state
hidden_tags	JSON array of int64	Tag IDs to hide from gallery by default

tags

Stores tag definitions for organizing clips.

CREATE TABLE tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    color TEXT NOT NULL
);

Note: Colors are auto-assigned from a palette when creating tags, not via a SQL default.

Column	Type	Description
id	INTEGER	Auto-incrementing primary key
name	TEXT	Tag name (unique)
color	TEXT	Hex color code for display

Tag hierarchy convention: There is no parent_id column or separate hierarchy table. Tag hierarchy is derived entirely from the / separator in tag names. For example, the tag work/client1/projectABC is a child of work/client1, which is a child of work. Descendant queries use LIKE 'prefix/%' against the name column. This keeps the schema flat and avoids recursive joins.

clip_tags

Junction table linking clips to tags (many-to-many).

CREATE TABLE clip_tags (
    clip_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    PRIMARY KEY (clip_id, tag_id),
    FOREIGN KEY (clip_id) REFERENCES clips(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

Column	Type	Description
clip_id	INTEGER	Foreign key to clips table
tag_id	INTEGER	Foreign key to tags table

Constraints:

• Composite primary key on (clip_id, tag_id)
• Cascading deletes when clip or tag is removed

plugins

Stores installed plugin metadata and state.

CREATE TABLE plugins (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    version TEXT,
    enabled INTEGER DEFAULT 1,
    status TEXT DEFAULT 'enabled',
    error_count INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Column	Type	Description
id	INTEGER	Auto-incrementing primary key
filename	TEXT	Plugin filename (unique)
name	TEXT	Human-readable plugin name
version	TEXT	Plugin version string (nullable)
enabled	INTEGER	0 = disabled, 1 = enabled
status	TEXT	Current status (enabled, error, disabled)
error_count	INTEGER	Number of runtime errors
created_at	DATETIME	When plugin was installed

Migration:

ALTER TABLE plugins ADD COLUMN source_url TEXT DEFAULT ''

Column	Type	Description
source_url	TEXT	URL the plugin was installed from (empty for local imports)

plugin_permissions

Stores granted permissions for plugins.

CREATE TABLE plugin_permissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    plugin_id INTEGER NOT NULL,
    permission_type TEXT NOT NULL,
    path TEXT NOT NULL,
    granted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (plugin_id) REFERENCES plugins(id) ON DELETE CASCADE
);

Migration adds pending_reconfirm column:

ALTER TABLE plugin_permissions ADD COLUMN pending_reconfirm INTEGER DEFAULT 0

Column	Type	Description
id	INTEGER	Auto-incrementing primary key
plugin_id	INTEGER	Foreign key to plugins table
permission_type	TEXT	Permission type (http, fs, etc.)
path	TEXT	Specific path/domain granted
granted_at	DATETIME	When permission was granted
pending_reconfirm	INTEGER	1 if permission needs re-confirmation after restore

plugin_storage

Key-value storage scoped to individual plugins.

CREATE TABLE plugin_storage (
    plugin_id INTEGER NOT NULL,
    key TEXT NOT NULL,
    value BLOB,
    PRIMARY KEY (plugin_id, key),
    FOREIGN KEY (plugin_id) REFERENCES plugins(id) ON DELETE CASCADE
);

Column	Type	Description
plugin_id	INTEGER	Foreign key to plugins table
key	TEXT	Storage key
value	BLOB	Stored value (JSON-encoded)

Constraints:

• Composite primary key on (plugin_id, key)
• Cascading delete when plugin is removed

app_settings

Application settings used by internal services (e.g., plugin update interval).

CREATE TABLE IF NOT EXISTS app_settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

Column	Type	Description
key	TEXT	Setting name (primary key)
value	TEXT	Setting value

Current settings:

Key	Values	Description
plugin_update_interval	"startup", "6h", "24h", "disabled"	How often to check for plugin updates

api_keys

Stores hashed API keys for REST API authentication.

CREATE TABLE IF NOT EXISTS api_keys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    key_hash TEXT NOT NULL UNIQUE,
    key_prefix TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'viewer',
    scoped_tag_id INTEGER,
    is_revoked INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_used_at DATETIME,
    FOREIGN KEY (scoped_tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

Column	Type	Description
id	INTEGER	Auto-incrementing primary key
name	TEXT	Human-readable key name
key_hash	TEXT	SHA-256 hash of the raw key (unique)
key_prefix	TEXT	First 8 characters of the raw key, for identification in listings
role	TEXT	"viewer" (read-only, default), "editor" (create/modify clips and tags), or "admin" (full access)
scoped_tag_id	INTEGER	Tag ID to restrict access to (nullable — NULL means global access)
is_revoked	INTEGER	0 = active, 1 = revoked
created_at	DATETIME	When the key was created
last_used_at	DATETIME	Last time the key was used for authentication (nullable)

Constraints:

• key_hash must be unique
• Foreign key on scoped_tag_id with cascading delete when tag is removed
• Raw keys are never stored — only the SHA-256 hash is persisted. The raw key is returned once at creation time and cannot be retrieved afterward.

Schema Migrations

Migrations are handled inline in initDB():

// Initial table creation
db.Exec(createTableSQL)

// Migrations (idempotent - ALTER TABLE silently fails if column exists)
db.Exec("ALTER TABLE clips ADD COLUMN is_archived INTEGER DEFAULT 0")
db.Exec("ALTER TABLE clips ADD COLUMN expires_at DATETIME")
db.Exec("ALTER TABLE clips ADD COLUMN content_hash TEXT DEFAULT ''")
db.Exec("CREATE INDEX IF NOT EXISTS idx_clips_content_hash ON clips(content_hash)")
db.Exec("ALTER TABLE clips ADD COLUMN metadata TEXT DEFAULT '{}'")
db.Exec("ALTER TABLE watched_folders ADD COLUMN auto_tag_id INTEGER")
db.Exec("ALTER TABLE plugin_permissions ADD COLUMN pending_reconfirm INTEGER DEFAULT 0")
db.Exec("ALTER TABLE plugins ADD COLUMN source_url TEXT DEFAULT ''")

Migrations use ALTER TABLE which silently fails if column exists.

Database Configuration

DSN-Based Pragmas

All pragmas are set in the DSN string so they apply to every pooled connection:

dsn := dbPath +
    "?_pragma=busy_timeout%3D5000" +
    "&_pragma=journal_mode%3Dwal" +
    "&_pragma=foreign_keys%3Don"
db, err := sql.Open("sqlite", dsn)

Pragma	Value	Purpose
busy_timeout	5000	Wait up to 5 seconds when database is locked
journal_mode	WAL	Write-Ahead Logging for better read/write concurrency
foreign_keys	ON	Required for CASCADE deletes on clip_tags, plugin_permissions, plugin_storage, api_keys

Setting pragmas via DSN (not db.Exec) is important because sql.Open may use a connection pool. A db.Exec("PRAGMA ...") call only applies to one connection, while DSN-based pragmas apply to all connections the pool creates.

Closed on application shutdown:

func (a *App) shutdown(ctx context.Context) {
    if a.db != nil {
        a.db.Close()
    }
}

Common Queries

Get clips for gallery

Basic query (no tag filters):

SELECT c.id, c.content_type, c.filename, c.created_at, c.expires_at,
       SUBSTR(c.data, 1, 500), c.is_archived, LENGTH(c.data),
       (SELECT COUNT(*) FROM clips c2
        WHERE c2.content_hash = c.content_hash
          AND c2.content_hash != '' AND c2.id != c.id)
FROM clips c
WHERE c.is_archived = ?
  AND (c.expires_at IS NULL OR c.expires_at > CURRENT_TIMESTAMP)
ORDER BY c.created_at DESC
LIMIT 50

Note: Only first 500 bytes of data fetched for preview. LENGTH(c.data) provides the file size and the subquery counts duplicates sharing the same content_hash. When tag filters or hidden tags are active, the query uses EXISTS/NOT EXISTS with clip_tags for filtering.

Get full clip data

SELECT content_type, data, filename
FROM clips
WHERE id = ?

Insert new clip

-- UploadFileAndGetID (single file, no expiration)
INSERT INTO clips (content_type, data, filename, content_hash)
VALUES (?, ?, ?, ?)

-- UploadFiles (batch upload with optional expiration)
INSERT INTO clips (content_type, data, filename, expires_at, content_hash)
VALUES (?, ?, ?, ?, ?)

Toggle archive status

UPDATE clips
SET is_archived = NOT is_archived
WHERE id = ?

Delete expired clips

DELETE FROM clips
WHERE expires_at IS NOT NULL
  AND expires_at <= CURRENT_TIMESTAMP

Runs every 60 seconds via cleanup job.

Bulk operations

-- Bulk delete
DELETE FROM clips WHERE id IN (?, ?, ?)

-- Bulk archive (always archives — does not toggle)
UPDATE clips SET is_archived = 1 WHERE id IN (?, ?, ?)

Data Storage Paths

Platform	Path
macOS	~/Library/Application Support/mahpastes/clips.db
Windows	%APPDATA%\mahpastes\clips.db
Linux	~/.config/mahpastes/clips.db

Backup

To backup your data:

# macOS
cp ~/Library/Application\ Support/mahpastes/clips.db backup.db

# Windows
copy %APPDATA%\mahpastes\clips.db backup.db

# Linux
cp ~/.config/mahpastes/clips.db backup.db

Data Size Considerations

• Each clip stores full binary data
• Large images/files can grow database quickly
• No automatic cleanup except expiration
• Consider archiving + periodically clearing old clips

Viewing the Database

Use any SQLite viewer:

# Command line
sqlite3 ~/Library/Application\ Support/mahpastes/clips.db

# Queries
.schema              -- Show all tables
SELECT * FROM clips LIMIT 5;
SELECT * FROM watched_folders;
SELECT * FROM settings;

Popular GUI tools:

• DB Browser for SQLite
• TablePlus
• DBeaver