Local Project Persistence

Local Project Persistence

Section: Data · Audio-Quality-Enhancement · Home · CLI-and-Developer-Tools

Local persistence is one SQLite database per project, plus filesystem artifacts beside that DB. Implementation lives in Infrastructure. Desktop ViewModels must not own SQL or mutate project state directly.

Architecture and Design

The persistence architecture follows a Repository pattern backed by Dapper for data access and a custom migration engine for schema management.

One Database Per Project

Trackdub utilizes a decentralized storage model where each project owns its own SQLite database file. This file contains the schema for segments, transcripts, and pipeline execution snapshots. Artifacts such as audio slices and transcripts are stored on the file system relative to this project database.

Layered Dependency Rules

Persistence stays in Infrastructure. Architecture tests block Domain and desktop ViewModels from owning SQL.

• Domain: Zero external dependencies; no SQLite knowledge.
• Application: Orchestrates via repository interfaces.
• Infrastructure: SqliteProjectRepository / SqliteProjectDatabase.
• Desktop: reflect state only; no persistence in ViewModels.

Component Relationship

The following diagram illustrates the flow from the Application layer down to the SQLite persistence implementation.

graph TD
    A[Application Services] --> B[IRepository Interfaces]
    subgraph Infrastructure_Layer
        B --> C[SqliteProjectRepository]
        C --> D[SqliteProjectDatabase]
        D --> E[Dapper / Microsoft.Data.Sqlite]
    end
    E --> F[(Project SQLite File)]

Loading

The application interacts with repositories through interfaces, while the infrastructure layer handles the concrete implementation using Dapper and the SQLite engine.

Database Management and Migrations

Trackdub manages schema evolution through dedicated migration runners. There is currently a duality in the migration engine that handles both machine-local settings and per-project schemas.

Migration Engines

The project currently utilizes two SQLite migration runners with identical mechanics:

1. Global Migrator: Handles machine-local data (e.g., global settings).
2. Project Migrator: Manages the schema within the individual project .trackdub or .db files.

Schema Lifecycle

When a project is loaded, the SqliteProjectDatabase ensures the schema is up-to-date before any operations occur. This involves running kebab-case or action-first named migrations to normalize the tables.

Component	Responsibility
SqliteProjectDatabase	Manages the connection string and migration execution for a project file.
SqliteProjectRepository	Provides CRUD operations for project entities using Dapper.
SqliteProjectSchemaMigrations	Contains the SQL scripts for defining project-specific tables.

Data Consistency and Pipeline Artifacts

The persistence system is critical for maintaining the integrity of the AI dubbing pipeline. It records not just the results, but the metadata of the execution.

Execution Snapshots

Pipeline stages use immutable execution snapshots. When a stage runs, the persistence layer records:

• Stage Run Records: Status of the run (Success, Skipped, Failed).
• Skip Reasons: Structured reasons why a stage might have been bypassed (e.g., SkippedLowConfidence).
• Artifact Provenance: References to generated files, ensuring original artifacts are never overwritten.

Artifact Storage

Artifacts are stored on the filesystem, but their metadata and paths are managed within the project database. This allows for features like "idempotent/resumable" behavior, where the application can check the database to see if a specific artifact already exists before re-running a heavy AI model.

sequenceDiagram
    participant P as Pipeline Stage
    participant R as SqliteProjectRepository
    participant D as SqliteProjectDatabase
    P->>R: GetExistingArtifact(artifactId)
    R->>D: SELECT path FROM Artifacts...
    D-->>R: path/metadata
    R-->>P: Artifact info
    Note over P: If artifact exists and valid, skip processing

Loading

Implementation Details

Technology Stack

• Database Engine: SQLite (via Microsoft.Data.Sqlite).
• ORM: Dapper (used for lightweight mapping and performance).
• Version: Centrally managed in Directory.Packages.props (e.g., Microsoft.Data.Sqlite 10.0.6).

Directory Structure

Project databases and logs are generally located in the user's local app data directory or relative to the media being processed.

• Logs: %LOCALAPPDATA%\Trackdub\trackdub.log
• Database Files: Stored per-project, typically within the project folder.

Technical Debt and Future Improvements

As of mid-2026, the persistence layer has identified several areas for consolidation and modernization:

1. Migration Consolidation: A plan is in place to extract a shared migration runner abstraction to serve both global and project-specific databases, reducing redundant mechanics.
2. Dapper.AOT Pilot: To support NativeAOT publishing, the project is piloting Dapper.AOT on simple CRUD repositories.
3. Naming Normalization: Future migrations aim to normalize naming conventions across tables (currently a mix of PascalCase and snake_case).

Conclusion

Local Project Persistence is the bedrock of Trackdub's "local-first" philosophy. By utilizing a "one database per project" model with SQLite, the system achieves a high degree of isolation and reliability. The integration of Dapper and a structured migration system allows the project to scale its data requirements while maintaining the strict architectural boundaries necessary for a complex AI-assisted workstation.