store

package
v0.1.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Oct 12, 2026 License: Apache-2.0 Imports: 7 Imported by: 0

Documentation

Overview

Package store is Godwit's SQLite storage layer.

Index

Constants

This section is empty.

Variables

View Source 
var ErrNewerDatabase = errors.New("store: database schema is newer than this version")

ErrNewerDatabase is returned when the database was written by a newer Godwit.

View Source 
var Migrations = [][]string{{
	`CREATE TABLE accounts (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		email TEXT NOT NULL UNIQUE,
		name TEXT, alias TEXT, signature TEXT,
		is_default INTEGER DEFAULT 0,
		sync_limit INTEGER DEFAULT 2000,
		auth_type TEXT DEFAULT 'password',
		status TEXT DEFAULT 'ok',
		username TEXT,
		imap_host TEXT, imap_port INTEGER, imap_security TEXT,
		smtp_host TEXT, smtp_port INTEGER, smtp_security TEXT,
		created_at INTEGER)`,
	`CREATE TABLE folders (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		name TEXT,
		role TEXT NOT NULL DEFAULT '',
		uidvalidity INTEGER,
		last_uid INTEGER DEFAULT 0,
		UNIQUE(account_id, name))`,
	`CREATE TABLE threads (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		subject TEXT,
		last_date INTEGER)`,
	`CREATE INDEX threads_account_date ON threads(account_id, last_date DESC)`,
	`CREATE TABLE messages (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		folder_id INTEGER NOT NULL REFERENCES folders(id) ON DELETE CASCADE,
		server_folder_id INTEGER REFERENCES folders(id) ON DELETE SET NULL,
		server_uidvalidity INTEGER,
		server_uid INTEGER,
		thread_id INTEGER REFERENCES threads(id) ON DELETE CASCADE,
		message_id TEXT, in_reply_to TEXT, refs TEXT,
		subject TEXT, from_addr TEXT, to_addrs TEXT, cc_addrs TEXT, reply_to TEXT,
		date INTEGER,
		flags TEXT NOT NULL DEFAULT '',
		size INTEGER,
		snippet TEXT, body_text TEXT, body_html TEXT,
		body_truncated INTEGER DEFAULT 0,
		has_attachments INTEGER,
		UNIQUE(server_folder_id, server_uid))`,
	`CREATE INDEX messages_folder_date ON messages(folder_id, date DESC, id DESC)`,
	`CREATE INDEX messages_thread ON messages(thread_id)`,
	`CREATE INDEX messages_account_mid ON messages(account_id, message_id)`,
	`CREATE TABLE attachments (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		message_id INTEGER NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
		part TEXT, encoding TEXT, charset TEXT, filename TEXT, content_type TEXT,
		size INTEGER, content_id TEXT, inline INTEGER, cache_path TEXT, last_used INTEGER)`,
	`CREATE TABLE thread_refs (
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		message_id_hdr TEXT NOT NULL,
		thread_id INTEGER NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
		PRIMARY KEY(account_id, message_id_hdr))`,
	`CREATE TABLE events_log (
		seq INTEGER PRIMARY KEY AUTOINCREMENT,
		type TEXT, account_id INTEGER, message_id INTEGER, created_at INTEGER)`,
	`CREATE VIRTUAL TABLE messages_fts USING fts5(subject, from_addr, body, tokenize='trigram')`,
	`CREATE TRIGGER messages_ai AFTER INSERT ON messages BEGIN
		INSERT INTO messages_fts(rowid, subject, from_addr, body)
		VALUES (new.id, new.subject, new.from_addr, new.body_text);
	END`,
	`CREATE TRIGGER messages_ai_event AFTER INSERT ON messages BEGIN
		INSERT INTO events_log(type, account_id, message_id, created_at)
		VALUES ('message.received', new.account_id, new.id, unixepoch());
	END`,
	`CREATE TRIGGER messages_au AFTER UPDATE OF subject, from_addr, body_text ON messages BEGIN
		UPDATE messages_fts SET subject = new.subject, from_addr = new.from_addr, body = new.body_text
		WHERE rowid = old.id;
	END`,
	`CREATE TRIGGER messages_ad AFTER DELETE ON messages BEGIN
		DELETE FROM messages_fts WHERE rowid = old.id;
	END`,
	`CREATE TABLE drafts (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		mode TEXT,
		source_message_id INTEGER REFERENCES messages(id) ON DELETE SET NULL,
		to_addrs TEXT, cc_addrs TEXT, bcc_addrs TEXT, reply_to TEXT,
		subject TEXT, body_html TEXT, body_text TEXT, updated_at INTEGER)`,
	`CREATE TABLE draft_attachments (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		draft_id INTEGER NOT NULL REFERENCES drafts(id) ON DELETE CASCADE,
		filename TEXT, content_type TEXT, size INTEGER, path TEXT)`,
	`CREATE TABLE ops (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		message_id INTEGER REFERENCES messages(id) ON DELETE SET NULL,
		kind TEXT,
		dst_folder_id INTEGER,
		payload TEXT,
		depends_on INTEGER,
		state TEXT DEFAULT 'pending',
		attempts INTEGER DEFAULT 0,
		last_error TEXT,
		created_at INTEGER)`,
	`CREATE TABLE outbox (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		message_id_hdr TEXT, raw_path TEXT, rcpts TEXT,
		state TEXT,
		attempts INTEGER DEFAULT 0,
		last_error TEXT,
		created_at INTEGER)`,
}, {
	`ALTER TABLE outbox ADD COLUMN subject TEXT`,
}, {
	`CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)`,
}, {

	`ALTER TABLE accounts ADD COLUMN smtp_reauth INTEGER NOT NULL DEFAULT 0`,
}, {
	`ALTER TABLE accounts ADD COLUMN provider TEXT NOT NULL DEFAULT ''`,

	`ALTER TABLE accounts ADD COLUMN status_detail TEXT NOT NULL DEFAULT ''`,
}, {
	`CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT NOT NULL)`,
	`CREATE TABLE model_services (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, kind TEXT NOT NULL,
		base_url TEXT NOT NULL, models TEXT NOT NULL DEFAULT '[]', version INTEGER NOT NULL DEFAULT 1, created_at INTEGER)`,
	`CREATE TABLE ai_cache (key TEXT PRIMARY KEY, account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		output TEXT NOT NULL, created_at INTEGER)`,
	`CREATE TABLE conversations (id INTEGER PRIMARY KEY AUTOINCREMENT, kind TEXT NOT NULL,
		draft_id INTEGER REFERENCES drafts(id) ON DELETE CASCADE, title TEXT, model TEXT, state BLOB,
		created_at INTEGER, updated_at INTEGER)`,
	`CREATE TABLE runs (id INTEGER PRIMARY KEY AUTOINCREMENT,
		conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
		state TEXT NOT NULL, model TEXT NOT NULL, tools TEXT NOT NULL, snapshot BLOB, created_at INTEGER, updated_at INTEGER)`,
	`CREATE UNIQUE INDEX runs_one_active ON runs(conversation_id) WHERE state IN ('running','interrupted','resuming')`,
	`CREATE TABLE approvals (id INTEGER PRIMARY KEY AUTOINCREMENT, run_id INTEGER NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
		seq INTEGER NOT NULL, call_id TEXT NOT NULL, tool TEXT NOT NULL, args TEXT NOT NULL, summary TEXT,
		target_kind TEXT, target_id INTEGER, target_version INTEGER, state TEXT NOT NULL, result TEXT, undo TEXT,
		created_at INTEGER, updated_at INTEGER, UNIQUE(run_id, seq))`,
	`CREATE TABLE audit_log (id INTEGER PRIMARY KEY AUTOINCREMENT, at INTEGER, source TEXT, tool TEXT,
		target_kind TEXT, target_id INTEGER, decision TEXT)`,
	`ALTER TABLE drafts ADD COLUMN revision INTEGER NOT NULL DEFAULT 1`,
	`ALTER TABLE drafts ADD COLUMN ai_approval_id INTEGER`,
}, {
	`CREATE TABLE plugins (name TEXT PRIMARY KEY, version TEXT NOT NULL, manifest TEXT NOT NULL, source_hash TEXT NOT NULL,
		permission_hash TEXT NOT NULL, config_version INTEGER NOT NULL DEFAULT 1, enabled INTEGER NOT NULL DEFAULT 0, granted_hash TEXT,
		failures INTEGER NOT NULL DEFAULT 0, disabled_reason TEXT NOT NULL DEFAULT '', installed_at INTEGER)`,
	`CREATE TABLE plugin_config (plugin TEXT NOT NULL REFERENCES plugins(name) ON DELETE CASCADE, key TEXT NOT NULL,
		value TEXT NOT NULL, PRIMARY KEY(plugin, key))`,
	`CREATE TABLE plugin_kv (plugin TEXT NOT NULL REFERENCES plugins(name) ON DELETE CASCADE, key TEXT NOT NULL,
		value TEXT NOT NULL, PRIMARY KEY(plugin, key))`,

	`DROP TRIGGER messages_ai_event`,
	`ALTER TABLE events_log ADD COLUMN subject TEXT`,
	`ALTER TABLE events_log ADD COLUMN from_addr TEXT`,
	`ALTER TABLE events_log ADD COLUMN msg_date INTEGER`,
	`CREATE TABLE plugin_cursors (plugin TEXT PRIMARY KEY REFERENCES plugins(name) ON DELETE CASCADE, seq INTEGER NOT NULL)`,
	`CREATE TABLE mcp_servers (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, transport TEXT NOT NULL,
		command TEXT, args TEXT NOT NULL DEFAULT '[]', env_keys TEXT NOT NULL DEFAULT '[]', url TEXT,
		header_keys TEXT NOT NULL DEFAULT '[]', version INTEGER NOT NULL DEFAULT 1, enabled INTEGER NOT NULL DEFAULT 0,
		granted_version INTEGER NOT NULL DEFAULT 0, created_at INTEGER)`,
	`CREATE TABLE mcp_tool_trust (server_id INTEGER NOT NULL REFERENCES mcp_servers(id) ON DELETE CASCADE, tool TEXT NOT NULL,
		definition_hash TEXT NOT NULL, PRIMARY KEY(server_id, tool))`,
	`ALTER TABLE audit_log ADD COLUMN extension TEXT`,
	`ALTER TABLE audit_log ADD COLUMN call_id TEXT`,
	`ALTER TABLE audit_log ADD COLUMN destination TEXT`,
	`ALTER TABLE audit_log ADD COLUMN reason TEXT`,
	`ALTER TABLE approvals ADD COLUMN exec_mode TEXT NOT NULL DEFAULT 'tx'`,
}, {

	`ALTER TABLE plugins ADD COLUMN install_dir TEXT NOT NULL DEFAULT ''`,

	`ALTER TABLE plugins ADD COLUMN secret_keys TEXT NOT NULL DEFAULT '[]'`,

	`ALTER TABLE approvals ADD COLUMN destinations TEXT NOT NULL DEFAULT '[]'`,

	`ALTER TABLE approvals ADD COLUMN binding TEXT NOT NULL DEFAULT ''`,

	`ALTER TABLE approvals ADD COLUMN source TEXT NOT NULL DEFAULT 'wit'`,
	`ALTER TABLE approvals ADD COLUMN extension TEXT NOT NULL DEFAULT ''`,
}, {

	`ALTER TABLE folders ADD COLUMN synced INTEGER NOT NULL DEFAULT 0`,
	`UPDATE folders SET synced = 1 WHERE last_uid > 0`,
}, {

	`ALTER TABLE conversations ADD COLUMN read_mail INTEGER NOT NULL DEFAULT 0`,

	`ALTER TABLE plugins ADD COLUMN holds_mail INTEGER NOT NULL DEFAULT 0`,
}, {

	`CREATE TABLE run_citations (id INTEGER PRIMARY KEY AUTOINCREMENT, run_id INTEGER NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
		call_id TEXT NOT NULL DEFAULT '', message_id INTEGER NOT NULL, subject TEXT NOT NULL, from_addr TEXT NOT NULL, date INTEGER, suspect INTEGER NOT NULL DEFAULT 0)`,
	`CREATE INDEX run_citations_run ON run_citations(run_id)`,

	`ALTER TABLE runs ADD COLUMN hist_from INTEGER`,
	`ALTER TABLE runs ADD COLUMN hist_to INTEGER`,
}, {

	`ALTER TABLE model_services ADD COLUMN slug TEXT`,
	`CREATE UNIQUE INDEX model_services_slug ON model_services(slug)`,
}, {

	`CREATE TABLE model_service_aliases (name TEXT PRIMARY KEY, service_id INTEGER NOT NULL REFERENCES model_services(id) ON DELETE CASCADE)`,

	`ALTER TABLE model_services ADD COLUMN key_version INTEGER NOT NULL DEFAULT 1`,

	`CREATE TABLE model_key_trash (service_id INTEGER NOT NULL, key_version INTEGER NOT NULL, PRIMARY KEY(service_id, key_version))`,
}, {

	`ALTER TABLE run_citations ADD COLUMN quote TEXT NOT NULL DEFAULT ''`,
}, {

	`ALTER TABLE model_services ADD COLUMN provider TEXT NOT NULL DEFAULT 'other'`,
	`UPDATE model_services SET provider = 'openai' WHERE kind IN ('openai', 'openai-responses')
		AND (base_url = 'https://api.openai.com' OR base_url LIKE 'https://api.openai.com/%')`,
	`UPDATE model_services SET provider = 'anthropic' WHERE kind = 'anthropic'
		AND (base_url = 'https://api.anthropic.com' OR base_url LIKE 'https://api.anthropic.com/%')`,
	`ALTER TABLE model_services ADD COLUMN judge_format TEXT NOT NULL DEFAULT ''`,
	`ALTER TABLE model_services ADD COLUMN judge_model TEXT NOT NULL DEFAULT ''`,

	`CREATE TABLE model_service_status (service_id INTEGER NOT NULL REFERENCES model_services(id) ON DELETE CASCADE,
		cap TEXT NOT NULL, state TEXT NOT NULL, latency_ms INTEGER NOT NULL DEFAULT 0, http_status INTEGER NOT NULL DEFAULT 0,
		code TEXT NOT NULL DEFAULT '', at INTEGER NOT NULL, PRIMARY KEY(service_id, cap))`,
}, {

	`ALTER TABLE accounts ADD COLUMN sync_since_months INTEGER NOT NULL DEFAULT 0`,

	`ALTER TABLE folders ADD COLUMN filled_since INTEGER`,
}, {

	`ALTER TABLE messages ADD COLUMN entered_at INTEGER NOT NULL DEFAULT 0`,
	`UPDATE messages SET entered_at = unixepoch()`,

	`ALTER TABLE folders ADD COLUMN role_override TEXT NOT NULL DEFAULT ''`,

	`ALTER TABLE folders ADD COLUMN role_since INTEGER NOT NULL DEFAULT 0`,
	`UPDATE folders SET role_since = unixepoch()`,
	`CREATE TRIGGER folders_role_since AFTER UPDATE OF role, role_override ON folders
		WHEN old.role IS NOT new.role OR old.role_override IS NOT new.role_override BEGIN
		UPDATE folders SET role_since = unixepoch() WHERE id = new.id;
	END`,

	`ALTER TABLE folders ADD COLUMN purged_at INTEGER NOT NULL DEFAULT 0`,

	`CREATE TABLE purge_policies (account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		role TEXT NOT NULL, days INTEGER NOT NULL DEFAULT 0, since INTEGER NOT NULL, folder_id INTEGER,
		blocked INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(account_id, role))`,
}, {

	`CREATE TABLE signature_images (account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
		cid TEXT NOT NULL, content_type TEXT NOT NULL, data BLOB NOT NULL, PRIMARY KEY(account_id, cid))`,
}}

Migrations[i] holds the statements of schema version i+1, one statement each.

Functions

func CleanOrphans 

func CleanOrphans(dir string, keep func(path string) bool) error

CleanOrphans removes leftover .tmp-* files and any regular file in dir (recursively) for which keep returns false.

func Int 

func Int(v any) int64

Int converts a sqlite value to int64 (NULL and non-integers give 0).

func Str 

func Str(v any) string

Str converts a sqlite value to string (NULL gives "").

func WriteFileAtomic 

func WriteFileAtomic(path string, data []byte) error

WriteFileAtomic writes data via a same-directory temp file, fsync and rename, creating missing parent directories. When it returns nil the file, its name and any directory it created survive a crash.

Types

type DB 

type DB struct{ *sqlite.DB }

DB wraps a mygo sqlite connection.

func Open 

func Open(ctx context.Context, path string) (*DB, error)

Open opens (creating if needed) the database at path and applies pending migrations.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.