Database Schema & Repositories

Relevant source files

The following files were used as context for generating this wiki page:

• Sources/MacParakeetCore/Database/DatabaseManager.swift
• Sources/MacParakeetCore/Database/TranscriptionRepository.swift
• Sources/MacParakeetCore/Database/DictationRepository.swift
• Sources/MacParakeetCore/Database/README.md
• spec/01-data-model.md
• AGENTS.md

The MacParakeet database architecture is built on a "local-first" posture, utilizing SQLite via the GRDB (Good Resources for Database) toolkit. The system is designed for high-performance speech-to-text workflows on Apple Silicon Macs, providing persistent storage for dictation history, file transcriptions, meeting artifacts, and AI-driven prompt results.

The architecture follows a repository pattern where each table in the schema is managed by a corresponding repository class. The DatabaseManager serves as the central entry point, handling connection setup, process-serialized migrations, and configuration for concurrent access between the macOS application, CLI tools, and background agents.

Sources: Sources/MacParakeetCore/Database/README.md, AGENTS.md

Core Architecture & Connection Management

The database resides in a single SQLite file named macparakeet.db located within the application support directory. The DatabaseManager owns the DatabaseQueue and is responsible for running inline migrations during initialization.

Database Configuration

To handle concurrent access from different processes (App, CLI, and Agents), the database is configured with specific SQLite settings:

• Foreign Keys: Explicitly enabled to maintain referential integrity.
• Busy Mode: Set to a 5-second timeout to prevent immediate SQLITE_BUSY failures during brief write locks.
• Process Serialization: Uses a .migration.lock file to prevent race conditions when multiple processes attempt to run migrations on an empty database simultaneously.

Sources: Sources/MacParakeetCore/Database/DatabaseManager.swift:65-85, Sources/MacParakeetCore/Database/README.md

Data Access Layer Diagram

The following diagram illustrates the relationship between the application layers and the persistence engine:

This diagram shows how different interfaces consume repositories, which in turn use the central Manager to access the SQLite file.

Sources: Sources/MacParakeetCore/Database/README.md, AGENTS.md

Database Schema & Migrations

Migrations in MacParakeet are defined inline within DatabaseManager using migrator.registerMigration. This ledger serves as both the schema definition and a release-version trail.

Key Tables

Table	Description	Derived Source
dictations	Stores system-wide dictation history, raw/clean transcripts, and metadata.	spec/01-data-model.md
transcriptions	Stores file, YouTube, and meeting transcriptions.	spec/01-data-model.md
prompts	Library of system prompts for AI summaries and transforms.	Sources/MacParakeetCore/Database/README.md
summaries	Stores saved prompt outputs linked to transcriptions.	Sources/MacParakeetCore/Database/README.md
custom_words	Vocabulary entries for the text processing pipeline.	Sources/MacParakeetCore/Database/README.md
llm_runs	Local metadata ledger for persisted LLM provider runs.	Sources/MacParakeetCore/Database/README.md

Schema Relationships

The entity relationship diagram below details the core data structures:

Repository Pattern Implementation

The project implements one repository per table, each adhering to a protocol to facilitate testing with mocks. Repositories handle CRUD operations and table-specific logic like Full-Text Search (FTS) or statistical aggregation.

TranscriptionRepository

Manages file-based and meeting transcriptions. It includes logic for status tracking (e.g., .pending, .transcribing, .completed) and batch fetching for the library view.

• Primary Functions: fetch(id:), fetchAll(), save(_:), delete(id:).
• Search: Implements search(query:) using SQLite LIKE patterns.
• Metadata: Handles derived fields such as derivedTitle and derivedSnippet.

Sources: Sources/MacParakeetCore/Database/TranscriptionRepository.swift:15-80

DictationRepository

Handles history for system-wide dictation. A critical feature of this repository is the maintenance of a singleton row for Lifetime Stats.

// Example of stat increment logic
// Sources: [Sources/MacParakeetCore/Database/DictationRepository.swift:110-125]
public func save(_ dictation: Dictation) throws {
    try dbQueue.write { db in
        try dictation.insert(db)
        // Increment lifetime stats in the same transaction
        try db.execute(sql: """
            UPDATE lifetime_dictation_stats 
            SET totalWords = totalWords + ?, 
                totalDuration = totalDuration + ?
            """, arguments: [dictation.wordCount, dictation.duration])
    }
}

Critical Implementation Note: UUID Storage

A common pitfall identified in the codebase is the handling of UUIDs. GRDB encodes UUID types in a specific format that does not always match UUID.uuidString. Developers are instructed to use GRDB's fetchOne(key:) or Codable-aware filter expressions rather than raw SQL WHERE id = ? with string representations.

Sources: Sources/MacParakeetCore/Database/README.md

Testing Strategy

Database testing relies on in-memory SQLite instances to ensure speed and isolation.

• In-Memory Fixtures: Calling DatabaseManager() without arguments returns an in-memory queue with all migrations pre-applied.
• Migration Snapshots: Tests confirm that new migrations apply cleanly to both empty databases and snapshots of previous versions.
• SQL Tracing: For debugging, setting the environment variable MACPARAKEET_DEBUG_SQL=1 prints every executed statement to the console.

Sources: Sources/MacParakeetCore/Database/README.md, spec/09-testing.md

Conclusion

The database layer of MacParakeet is designed to balance the performance requirements of local AI inference with the reliability of a durable user data store. By utilizing GRDB and a strict repository pattern, the system maintains a clean separation of concerns while providing advanced features like process-serialized migrations and crash-resilient session locks for meeting recordings.