Xolo Data Layer: Drift SQLite and Repositories

The data layer is Xolo’s persistence backbone. It owns everything below the domain boundary: the SQLite schema declared as Drift Table classes, the AppDatabase that stitches them together, focused query mixins for each concern, and the DriftXoloRepository that implements the abstract XoloRepository contract and translates raw Drift rows into domain entities. Nothing above this layer ever imports a Drift type directly — the presentation layer always talks to XoloRepository, never to AppDatabase.

Database Tables

AppDatabase registers eight Drift tables declared in tables.dart. Each table is a plain Dart class that extends Table; Drift’s code generator derives the companion and typed-row classes from it.

Collections

Represents both top-level workspace roots and nested folder nodes. The self-referential parentId FK creates the tree structure; when parentId is null, the row is a root collection (exposed by the CollectionEntity.isRoot helper).

Column	Type	Notes
`id`	`int`	Auto-increment PK
`name`	`text`	1–100 chars, required
`description`	`text?`	Nullable free-text
`parentId`	`int?`	Self-FK → `Collections.id`
`authType`	`text?`	`'bearer'`, `'basic'`, `'inherit'`, etc.
`authData`	`text?`	JSON string with credentials
`createdAt`	`dateTime`	Defaults to `currentDateAndTime`

class Collections extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text().withLength(min: 1, max: 100)();
  TextColumn get description => text().nullable()();
  IntColumn get parentId => integer().nullable().references(Collections, #id)();
  TextColumn get authType => text().nullable()();
  TextColumn get authData => text().nullable()();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

SavedRequests

Stores every reusable API request. A request can belong to a Collections row (via collectionId) or be unclassified when that FK is null. Soft-deletion is modelled with isDeleted rather than a physical row delete, preserving history references.

Column	Type	Notes
`id`	`int`	Auto-increment PK
`name`	`text`	1–100 chars
`method`	`text`	`GET`, `POST`, etc.
`url`	`text`	Full or template URL
`headersJson`	`text?`	JSON map of headers
`paramsJson`	`text?`	JSON map of query params
`body`	`text?`	Raw request body
`authType`	`text?`	Per-request auth override
`authData`	`text?`	JSON credentials
`schemaJson`	`text?`	Resolved/dereferenced OpenAPI body schema
`preScriptsJson`	`text?`	Variable extraction rules run before the request
`scriptsJson`	`text?`	Post-request chaining rules (JSONPath → variable)
`assertionsJson`	`text?`	Serialised `List<AssertionRuleEntity>`
`collectionId`	`int?`	FK → `Collections.id`
`createdAt`	`dateTime`	Defaults to `currentDateAndTime`
`updatedAt`	`dateTime`	Defaults to `currentDateAndTime`
`isDeleted`	`bool`	Soft-delete flag, defaults `false`

HistoryEntries

Automatic execution log. Every request fired from the UI appends a row here. workspaceId links the entry to the active collection workspace; savedRequestId optionally links it back to the originating SavedRequests row.

Column	Type	Notes
`id`	`int`	Auto-increment PK
`savedRequestId`	`int?`	FK → `SavedRequests.id` (nullable)
`workspaceId`	`int?`	FK → `Collections.id`
`method`	`text`	HTTP verb
`url`	`text`	Resolved (absolute) URL
`originalUrl`	`text?`	Template URL before variable substitution
`headersJson`	`text?`	Snapshot of sent headers
`paramsJson`	`text?`	Snapshot of query params
`body`	`text?`	Sent body
`authType`	`text?`	Auth snapshot for reproducibility
`authData`	`text?`	Credential snapshot
`statusCode`	`int?`	HTTP response code
`responseBody`	`text?`	Full response body
`durationMs`	`int?`	Round-trip latency
`executedAt`	`dateTime`	Defaults to `currentDateAndTime`

Environments

Named variable scopes (e.g. Dev, Staging, Production) that belong to a specific workspace collection. Setting isActive to true marks the environment currently selected for variable resolution in that workspace.

Column	Type	Notes
`id`	`int`	Auto-increment PK
`name`	`text`	1–50 chars
`collectionId`	`int?`	FK → `Collections.id` (workspace owner)
`isActive`	`bool`	`false` by default
`createdAt`	`dateTime`	Defaults to `currentDateAndTime`

EnvVariables

Stores key-value pairs scoped either to a specific environment (scope = 'env', environmentId != null) or to a workspace globally (scope = 'global', collectionId != null, environmentId == null). When both FKs are null the variable is user-global.

Column	Type	Notes
`id`	`int`	Auto-increment PK
`key`	`text`	1–100 chars
`value`	`text`	Variable value
`environmentId`	`int?`	FK → `Environments.id`
`collectionId`	`int?`	FK → `Collections.id` (workspace scope)
`scope`	`text`	`'global'` or `'env'`, defaults `'global'`
`createdAt`	`dateTime`	Defaults to `currentDateAndTime`

AppSettings

Simple key-value configuration store used for app-wide preferences (e.g. active workspace, last-run collection ID). The key column is the primary key, so upserts are idiomatic.

Column	Type	Notes
`key`	`text`	PK, 1–50 chars
`value`	`text`	Setting value

CollectionRuns

Persists the header record for every collection run execution. Step counts are updated atomically when finishCollectionRun is called.

Column	Type	Notes
`id`	`int`	Auto-increment PK
`collectionId`	`int`	FK → `Collections.id`
`workspaceId`	`int?`	FK → `Collections.id`
`environmentId`	`int?`	FK → `Environments.id`
`status`	`text`	`'running'`, `'completed'`, `'failed'`, `'cancelled'`
`totalSteps`	`int`	Plan size at run creation
`passedSteps`	`int`	Steps whose assertions all passed
`failedSteps`	`int`	Steps with at least one failed assertion
`skippedSteps`	`int`	Steps skipped by `RunOptions`
`stopOnFailure`	`bool`	Propagated from `RunOptions`
`runOptionsJson`	`text?`	Serialised `RunOptions` for replay
`variablesSnapshotJson`	`text?`	Variable state at run completion
`startedAt`	`dateTime`	Defaults to `currentDateAndTime`
`finishedAt`	`dateTime?`	Null while run is in progress

RunStepResults

One row per request step within a CollectionRuns run. assertionResultsJson serialises the full List<AssertionResultEntity> so the UI can replay every pass/fail detail.

Column	Type	Notes
`id`	`int`	Auto-increment PK
`runId`	`int`	FK → `CollectionRuns.id`
`savedRequestId`	`int?`	FK → `SavedRequests.id`
`stepIndex`	`int`	Zero-based position in plan
`name`	`text`	Request name snapshot
`method`	`text`	HTTP verb
`url`	`text`	Resolved URL at execution time
`stepStatus`	`text`	`'passed'`, `'failed'`, `'skipped'`, `'error'`
`statusCode`	`int?`	HTTP response code
`durationMs`	`int?`	Round-trip latency
`passed`	`bool`	Composite pass/fail flag
`assertionResultsJson`	`text?`	Serialised `List<AssertionResultEntity>`
`errorMessage`	`text?`	Network or evaluation error text
`responseBodySnippet`	`text?`	First 500 chars of the response body

DriftXoloRepository

DriftXoloRepository is the single concrete implementation of XoloRepository. It holds a private AppDatabase reference injected via its constructor and delegates every method to the appropriate query helper.

class DriftXoloRepository implements XoloRepository {
  DriftXoloRepository(this._db);
  final AppDatabase _db;
}

To keep files manageable, AppDatabase itself is split into focused part files — one per concern:

collection_queries.dart

CRUD, move, path traversal, and auth-data helpers for Collections and SavedRequests.

environment_queries.dart

Environment lifecycle and variable upsert/resolve logic.

history_queries.dart

watchRecentHistory, addHistoryItem, clear and single-entry helpers.

run_queries.dart

createCollectionRun, finishCollectionRun, insertRunStepResultRow, and watchers.

settings_queries.dart

setSetting, getSetting, watchSetting backed by AppSettings.

DriftXoloRepository acts as a thin adapter: it calls the database method and pipes the result through a mapper function, never leaking Drift row types upstream.

@override
Stream<List<CollectionEntity>> watchAllCollections() =>
    _db.watchAllCollections().map(mapCollections);

@override
Future<CollectionEntity?> findCollectionByName(
  String name,
  int? parentId,
) async {
  final row = await _db.findCollectionByName(name, parentId);
  return row?.toEntity();
}

Entity Mappers

entity_mappers.dart defines Dart extension methods on every Drift-generated row type. Each extension adds a toEntity() method that constructs the corresponding domain class, keeping all field mapping in one place.

extension CollectionEntityMapper on Collection {
  CollectionEntity toEntity() => CollectionEntity(
    id: id,
    name: name,
    description: description,
    parentId: parentId,
    authType: authType,
    authData: authData,
    createdAt: createdAt,
  );
}

extension RunStepResultEntityMapper on RunStepResult {
  RunStepResultEntity toEntity() {
    // Deserialises assertionResultsJson → List<AssertionResultEntity>
    final assertions = <AssertionResultEntity>[];
    if (assertionResultsJson != null && assertionResultsJson!.isNotEmpty) {
      try {
        final list = jsonDecode(assertionResultsJson!) as List<dynamic>;
        for (final item in list) {
          assertions.add(
            AssertionResultEntity.fromJson(item as Map<String, dynamic>),
          );
        }
      } catch (_) {}
    }
    return RunStepResultEntity(
      stepIndex: stepIndex,
      name: name,
      method: method,
      url: url,
      status: RunStepStatus.values.firstWhere(
        (s) => s.name == stepStatus,
        orElse: () => RunStepStatus.error,
      ),
      // ...
    );
  }
}

Top-level convenience functions (mapCollections, mapSavedRequests, mapHistoryEntries, mapEnvironments, mapEnvVariables, mapCollectionRuns, mapRunStepResults) accept a List of Drift rows and return an immutable List of entities for use in .map(...) stream transformers inside DriftXoloRepository. The presentation layer always works with domain entities and never imports anything from package:drift or package:xolo/data.

Reactive Streams

Drift exposes a Stream<T> for every watch* query. These streams re-emit automatically whenever any row in the watched table changes — no manual invalidation required. DriftXoloRepository wraps each stream with .map(mapper) to convert rows into entities before they reach Riverpod. Riverpod StreamProviders subscribe to these streams and expose them to the widget tree:

// From database_providers.dart
final xoloRepositoryProvider = Provider<XoloRepository>((ref) {
  return DriftXoloRepository(ref.watch(databaseProvider));
});

final savedRequestsStreamProvider =
    StreamProvider.autoDispose<List<SavedRequestEntity>>((ref) {
      final repo = ref.watch(xoloRepositoryProvider);
      return repo.watchSavedRequests();
    });

You can follow the same pattern for any new watch* method:

final collectionsProvider = StreamProvider<List<CollectionEntity>>((ref) {
  return ref.watch(xoloRepositoryProvider).watchAllCollections();
});

Because StreamProvider.autoDispose is used, the Drift query subscription is cancelled automatically when the last listener detaches, preventing background database activity when a screen is not visible.

Database Migrations

AppDatabase tracks schema evolution through an integer schemaVersion. When a new table or column is required, bump the version and add a guarded migration block inside onUpgrade. The current version history shows how Xolo has grown:

v1 — Initial schema

Core tables: SavedRequests, HistoryEntries, Collections, Environments, EnvVariables, AppSettings.

v2 — Auth columns

Added authType / authData to SavedRequests and HistoryEntries.

v3 — Body schema

Added schemaJson to SavedRequests for OpenAPI-driven body generation.

v4 — Collection auth

Added authType / authData to Collections for auth inheritance.

v5 — Post-request scripts

Added scriptsJson to SavedRequests for response-chaining rules.

v6 — Pre-request scripts

Added preScriptsJson to SavedRequests.

v7 — Template URL history

Added originalUrl to HistoryEntries to preserve the pre-substitution URL template.

v8 — Collection runs

Added assertionsJson to SavedRequests. Created CollectionRuns and RunStepResults tables.

To add a new migration, open database.dart and add a guarded block:

@override
int get schemaVersion => 9; // bump this

@override
MigrationStrategy get migration {
  return MigrationStrategy(
    onUpgrade: (Migrator m, int from, int to) async {
      // ... existing guards ...
      if (from < 9) {
        await m.addColumn(savedRequests, savedRequests.myNewColumn);
      }
    },
  );
}

Never access AppDatabase directly from presentation code. All data access must go through xoloRepositoryProvider. This keeps the presentation layer decoupled from Drift, makes testing trivial (swap the provider with a mock), and ensures every query is mediated by the mapper layer.

After modifying tables.dart — adding columns, tables, or changing types — you must regenerate the Drift-produced code:

dart run build_runner build --delete-conflicting-outputs

Failing to do so will cause compile errors because database.g.dart will be out of sync with the table declarations.

Project Structure

Contributing

Xolo Data Layer: Drift SQLite and Repositories

Database Tables

DriftXoloRepository

collection_queries.dart

environment_queries.dart

history_queries.dart

run_queries.dart

settings_queries.dart

Entity Mappers

Reactive Streams

Database Migrations

​Database Tables

​DriftXoloRepository

collection_queries.dart

environment_queries.dart

history_queries.dart

run_queries.dart

settings_queries.dart

​Entity Mappers

​Reactive Streams

​Database Migrations

Database Tables

DriftXoloRepository

Entity Mappers

Reactive Streams

Database Migrations