SQLite Repository Design in Desktop Apps

Interfaces, Abstract Base Classes, Safe SQL, and Audit-Aware Persistence in Chrona

Desktop applications often start with direct database access inside view models, commands, or small helper classes.

At first, this looks efficient. A button saves a record. A view model loads a list. A small method opens a SQLite connection, executes SQL, and returns data.

Then the application grows.

More entities appear. More screens need the same data. Audit logging becomes necessary. Formatting rules for dates and times must stay consistent. SQL commands repeat connection logic. Parameters are added manually everywhere. Dynamic sorting or filtering introduces SQL-injection risks. Testing becomes harder because persistence logic is scattered across the UI.

The problem is not SQLite.

The problem is missing repository architecture.

Chrona uses a more disciplined approach: repository interfaces define contracts, abstract base classes provide shared SQLite infrastructure, specialized base classes enforce safety rules, and concrete repositories implement real persistence use cases.

This article analyzes that design as a practical desktop architecture pattern.

The Repository Interface Defines the Application Contract

A repository interface should describe what the application needs from persistence without exposing how the data is stored.

Chrona’s audit repository is a clear example:

public interface IAuditRepository
{
    /// <summary>
    /// Schreibt einen Audit-Eintrag. Fehler werden protokolliert und verschluckt,
    /// damit die Hauptoperation nicht fehlschlägt.
    /// </summary>
    Task AppendAsync(
        AuditEntry entry,
        CancellationToken cancellationToken = default);

    Task<IReadOnlyList<AuditEntry>> GetRecentAsync(
        int maxCount,
        CancellationToken cancellationToken = default);

    /// <summary>
    /// Entfernt alle Audit-Einträge. Gibt die Anzahl gelöschter Zeilen zurück.
    /// </summary>
    Task<int> DeleteAllAsync(
        CancellationToken cancellationToken = default);
}

This interface is not just “clean code decoration”.

It protects the rest of the application from SQLite details.

A service or view model that depends on IAuditRepository does not need to know about connection strings, SQL commands, parameter binding, table names, or transaction details. It only knows the persistence capability it requires.

That matters in a desktop app because the UI should not become the database orchestration layer. The UI should ask for data or trigger an operation. The repository should own persistence.

The interface also creates a test seam. In tests, IAuditRepository can be replaced with an in-memory fake or mock. That allows application logic to be tested without creating a real SQLite database.

The key design point is this:

The interface expresses the persistence contract. It does not leak the persistence mechanism.

Shared SQLite Infrastructure Belongs in an Abstract Base Class

Without a base repository class, every concrete repository usually repeats the same technical code:

• read connection string
• open SQLite connection
• create command
• add parameters
• handle DBNull.Value
• log failures
• format values consistently

Chrona avoids this with RepositoryBase.

public abstract class RepositoryBase
{
    protected readonly string ConnectionString;
    protected readonly ILogger Logger;

    protected RepositoryBase(
        IDatabaseInitializer dbInitializer,
        ILogger logger)
    {
        ConnectionString = dbInitializer.ConnectionString;
        Logger = logger;
    }

    protected static void AddParameters(
        SqliteCommand command,
        params (string name, object? value)[] parameters)
    {
        foreach (var (name, value) in parameters)
        {
            command.Parameters.AddWithValue(
                name,
                value ?? DBNull.Value);
        }
    }

    protected async Task<SqliteConnection> CreateConnectionAsync(
        CancellationToken cancellationToken = default)
    {
        var connection = new SqliteConnection(ConnectionString);

        await connection
            .OpenAsync(cancellationToken)
            .ConfigureAwait(false);

        return connection;
    }
}

This base class has a narrow and useful responsibility.

It does not know about booking codes, projects, time entries, or audit entries. It only knows common SQLite infrastructure.

That is the correct use of an abstract base class.

The base class removes duplication without taking over domain behavior. Concrete repositories still own their SQL and mapping logic, but they no longer repeat low-level connection and parameter code.

This gives the repository layer consistency.

Every repository opens connections the same way. Every repository uses the same parameter helper. Every repository gets its connection string through the database initializer. Every repository has logging available.

That consistency matters more as the application grows.

Why Parameter Helpers Matter

This helper looks simple:

protected static void AddParameters(
    SqliteCommand command,
    params (string name, object? value)[] parameters)
{
    foreach (var (name, value) in parameters)
    {
        command.Parameters.AddWithValue(
            name,
            value ?? DBNull.Value);
    }
}

But it solves a real persistence problem.

SQLite commands often require many parameters. Without a helper, each repository writes repetitive code:

command.Parameters.AddWithValue("@Code", bookingCode.Code);
command.Parameters.AddWithValue("@Title", bookingCode.Title);
command.Parameters.AddWithValue("@IsActive", bookingCode.IsActive ? 1 : 0);

The repetition itself is not the main problem. The problem is inconsistency.

One method handles nulls correctly. Another forgets DBNull.Value. Another formats a date differently. Another passes a boolean differently.

The helper centralizes one small but recurring concern: safe parameter insertion with null handling.

It also reinforces an important rule:

Values belong in SQL parameters, not string interpolation.

A Specialized Base Class for Safe Dynamic SQL

Parameters protect values.

They do not protect SQL identifiers.

This distinction is critical.

A parameter can safely represent a value:

WHERE Code = @Code

But it cannot safely represent a table name or column name:

ORDER BY @ColumnName

Database engines treat parameters as values, not identifiers. Therefore, if an application supports dynamic table names, column names, sorting, or filtering, it must validate identifiers explicitly.

Chrona addresses this with a specialized base class.

public abstract class ChronaRepositoryBase : RepositoryBase
{
    protected abstract IReadOnlySet<string> AllowedTableNames { get; }

    protected abstract IReadOnlySet<string> AllowedColumnNames { get; }

    protected ChronaRepositoryBase(
        IDatabaseInitializer dbInitializer,
        ILogger logger)
        : base(dbInitializer, logger)
    {
    }

    protected sealed override void ValidateTableName(string tableName)
    {
        if (!AllowedTableNames.Contains(tableName))
        {
            throw new ArgumentException(
                $"Ungültiger Tabellenname: {tableName}",
                nameof(tableName));
        }
    }

    protected sealed override void ValidateColumnName(string columnName)
    {
        if (!AllowedColumnNames.Contains(columnName))
        {
            throw new ArgumentException(
                $"Ungültiger Spaltenname: {columnName}",
                nameof(columnName));
        }
    }
}

This is a strong repository-layer design decision.

The class forces concrete repositories to define allowed table and column names. Dynamic identifiers must pass through a whitelist. Invalid identifiers fail immediately.

That matters because desktop apps often assume local databases are automatically safe. That is a mistake. Even local apps can process imported files, user-entered filters, configurable sort orders, or plugin-like data sources. Dynamic SQL must still be treated carefully.

The design separates two safety mechanisms:

SQL values      → parameters
SQL identifiers → whitelist validation

That is the correct model.

Concrete Repository: Combining Contract, Infrastructure, and SQL

A concrete repository in Chrona combines three things:

IBookingCodeRepository
    fachlicher Vertrag

ChronaRepositoryBase
    gemeinsame SQLite-Infrastruktur und Identifier-Schutz

BookingCodeRepository
    konkrete SQL-Implementierung

The class shape shows this clearly:

public class BookingCodeRepository
    : ChronaRepositoryBase, IBookingCodeRepository
{
    private static readonly HashSet<string> AllowedTables =
        new(StringComparer.Ordinal)
        {
            "BookingCodes",
            "TimeEntries"
        };

    public BookingCodeRepository(
        IDatabaseInitializer dbInitializer,
        IAuditRepository auditRepository,
        ILogger<BookingCodeRepository> logger)
        : base(dbInitializer, logger)
    {
        _auditRepository = auditRepository;
    }
}

This is the correct kind of inheritance.

The repository does not inherit business behavior from a base class. It inherits infrastructure behavior: connection handling, logging support, identifier validation.

The interface defines what booking-code persistence must provide. The concrete class defines how that persistence works in SQLite.

The injected IAuditRepository also shows that persistence operations are not isolated database writes only. They can participate in application-level concerns such as audit logging.

That is realistic desktop architecture.

Create Operation with SQLite and Audit Logging

The CreateAsync method demonstrates the repository pattern in a real use case.

public async Task<BookingCode> CreateAsync(
    BookingCode bookingCode,
    CancellationToken cancellationToken = default)
{
    using var connection = await CreateConnectionAsync(
        cancellationToken).ConfigureAwait(false);

    using var command = new SqliteCommand(
        "INSERT INTO BookingCodes " +
        "(Code, Title, FreeTextAllowed, IsActive, CreatedAt, UpdatedAt) " +
        "VALUES (@Code, @Title, @FreeTextAllowed, @IsActive, @CreatedAt, @UpdatedAt); " +
        "SELECT last_insert_rowid();",
        connection);

    AddParameters(
        command,
        ("@Code", bookingCode.Code),
        ("@Title", bookingCode.Title),
        ("@FreeTextAllowed", bookingCode.FreeTextAllowed ? 1 : 0),
        ("@IsActive", bookingCode.IsActive ? 1 : 0),
        ("@CreatedAt", SqliteFormat.SqlRoundTripUtc(bookingCode.CreatedAt)),
        ("@UpdatedAt",
            SqliteFormat.SqlRoundTripUtcNullable(bookingCode.UpdatedAt)
            ?? (object)DBNull.Value));

    var id = Convert.ToInt32(
        await command
            .ExecuteScalarAsync(cancellationToken)
            .ConfigureAwait(false));

    bookingCode.Id = id;

    await _auditRepository.AppendAsync(
        new AuditEntry
        {
            Entity = AuditEntityNames.BookingCode,
            EntityId = bookingCode.Id.ToString(),
            ChangeType = ChangeType.Created,
            NewValue = AuditSnapshotSerializer.BookingCodeSnapshot(bookingCode),
            CreatedAt = DateTime.UtcNow
        },
        cancellationToken);

    return bookingCode;
}

This method shows several important design qualities.

First, the database connection is created through the shared base class. The repository does not manually duplicate connection-string handling.

Second, the SQL command uses parameters for all values. This protects against SQL injection for values and avoids fragile string concatenation.

Third, boolean values are normalized explicitly as 1 or 0. That is practical for SQLite because SQLite does not have a separate strict boolean storage type in the same way as some other relational databases.

Fourth, timestamps are formatted through a dedicated helper. The repository does not rely on current UI culture or machine locale.

Fifth, the generated ID is assigned back to the entity after last_insert_rowid().

Sixth, the audit record is appended immediately after persistence. This makes the creation operation explainable later.

This is what a repository should do in a desktop business application: persist state consistently and keep the persistence boundary auditable.

Why Audit Belongs Near the Mutation Boundary

Audit logging is strongest when it happens close to the actual mutation.

For a create operation, the repository has the final persisted entity and generated ID. That is the right moment to create an audit snapshot.

If audit logging happens later in the UI, the record may be incomplete, inconsistent, or forgotten entirely.

By injecting IAuditRepository into BookingCodeRepository, Chrona makes audit logging part of the persistence workflow.

That does create a design decision: the repository is not purely a table gateway anymore. It also participates in audit side effects.

This is acceptable in a desktop business app if the rule is consistent and intentional.

The important part is that audit writing is still abstracted behind IAuditRepository. The booking-code repository does not know how audit records are stored internally.

SQLite Formatting Must Be Explicit

Local persistence bugs often come from culture and timezone assumptions.

Chrona uses a dedicated formatting helper:

internal static class SqliteFormat
{
    public static string SqlDate(DateTime date)
    {
        return date.ToString(
            "yyyy-MM-dd",
            CultureInfo.InvariantCulture);
    }

    public static string SqlTime(TimeSpan time)
    {
        return time.ToString(
            @"hh\:mm\:ss",
            CultureInfo.InvariantCulture);
    }

    public static string SqlRoundTripUtc(DateTime utc)
    {
        return utc.ToString(
            "O",
            CultureInfo.InvariantCulture);
    }
}

This is small but important.

A desktop app runs on user machines. Those machines may have different regional settings. If date and numeric values are formatted implicitly, the same application may persist different text representations depending on the user environment.

That creates bugs that are hard to reproduce.

Using CultureInfo.InvariantCulture prevents this.

Using "yyyy-MM-dd" for dates produces stable sortable date strings.

Using "O" for UTC timestamps preserves round-trip precision and avoids ambiguous local-time formatting.

This helper keeps SQLite values predictable.

Why This Repository Design Works

Chrona’s repository design works because it separates concerns without fragmenting the codebase unnecessarily.

The interface defines what the application expects.

The abstract base class handles repeated SQLite infrastructure.

The specialized base class protects dynamic SQL identifiers.

The concrete repository implements actual persistence behavior.

The formatter centralizes SQLite-compatible value representation.

The audit repository records explainable changes.

These pieces work together as one persistence architecture.

A simplified dependency view looks like this:

ViewModel / Service
        ↓
IBookingCodeRepository
        ↓
BookingCodeRepository
        ↓
ChronaRepositoryBase
        ↓
RepositoryBase
        ↓
SQLite

And for auditing:

BookingCodeRepository
        ↓
IAuditRepository
        ↓
AuditRepository
        ↓
SQLite audit table

This keeps the UI away from SQL while still giving repositories enough control over efficient database operations.

What This Design Avoids

This architecture avoids several common desktop persistence mistakes.

It avoids SQL inside view models.

It avoids duplicated connection-opening logic.

It avoids inconsistent null handling.

It avoids culture-dependent date persistence.

It avoids raw dynamic SQL identifiers without validation.

It avoids audit logic being scattered across UI workflows.

It avoids tight coupling to concrete persistence implementations in higher layers.

These are practical wins.

Trade-Offs

This design is not free.

Interfaces add structure, but too many interfaces can become noise if they do not protect real boundaries.

Abstract base classes reduce duplication, but they must stay focused. If a base repository starts containing entity-specific logic, inheritance becomes a liability.

Audit logging inside repositories creates a side effect. That side effect should be documented clearly, especially whether audit failure blocks the main operation or is treated as best effort.

SQLite-specific formatting helpers are useful, but they also make the repository layer explicitly tied to SQLite. That is acceptable if SQLite is the chosen persistence engine. Do not pretend the implementation is database-agnostic if it is not.

The strength of this design is not theoretical purity. The strength is disciplined pragmatism.

Testing Strategy

This repository architecture supports several useful test levels.

Interface-based tests can mock repositories when testing services or view models.

Formatter tests can verify stable date, time, and UTC formatting.

Repository integration tests can run against a temporary SQLite database and verify actual SQL behavior.

Audit tests can verify that create, update, and delete operations append the expected audit entries.

Identifier-validation tests can verify that only whitelisted tables and columns are accepted.

The most valuable tests are usually integration tests around concrete repositories because SQL errors often appear only when commands hit a real database.

Good test cases include:

Create booking code persists all fields correctly.
Create booking code returns generated ID.
Null UpdatedAt is stored as DBNull.
CreatedAt uses round-trip UTC format.
Create operation appends audit entry.
Invalid dynamic column name throws exception.

These tests protect the persistence boundary.

Final Thoughts

SQLite is a strong choice for desktop applications, but it still needs architecture.

Chrona’s repository design shows a practical model:

• interfaces for persistence contracts
• abstract base classes for shared SQLite infrastructure
• whitelist validation for dynamic SQL identifiers
• concrete repositories for entity-specific SQL
• explicit formatting for dates, times, and UTC values
• audit integration at the mutation boundary

This is the right level of structure for a serious desktop application.

Not over-engineered.

Not ad hoc.

A repository layer should make persistence boring, predictable, testable, and safe. Chrona’s design moves in exactly that direction.