81 lines
2.7 KiB
Markdown
81 lines
2.7 KiB
Markdown
# Database — Drift Schema & Conventions
|
|
|
|
## Tables
|
|
|
|
### projects
|
|
| Column | Type | Notes |
|
|
|--------------|-----------|------------------------------|
|
|
| id | INTEGER | PK, autoincrement |
|
|
| name | TEXT | NOT NULL, unique |
|
|
| color_value | INTEGER | NOT NULL (ARGB int) |
|
|
| description | TEXT | nullable |
|
|
| archived_at | DATETIME | nullable; null = active |
|
|
| created_at | DATETIME | NOT NULL, default now |
|
|
|
|
### time_entries
|
|
| Column | Type | Notes |
|
|
|-------------|----------|--------------------------------------|
|
|
| id | INTEGER | PK, autoincrement |
|
|
| project_id | INTEGER | FK → projects.id, NOT NULL |
|
|
| start_time | DATETIME | NOT NULL |
|
|
| end_time | DATETIME | nullable; null = timer still running |
|
|
| duration_s | INTEGER | seconds; computed on stop |
|
|
| note | TEXT | nullable |
|
|
| created_at | DATETIME | NOT NULL, default now |
|
|
|
|
### tags
|
|
| Column | Type | Notes |
|
|
|--------|---------|-------------------|
|
|
| id | INTEGER | PK, autoincrement |
|
|
| name | TEXT | NOT NULL, unique |
|
|
|
|
### time_entry_tags (junction)
|
|
| Column | Type | Notes |
|
|
|---------------|---------|--------------------|
|
|
| time_entry_id | INTEGER | FK → time_entries |
|
|
| tag_id | INTEGER | FK → tags |
|
|
| PRIMARY KEY (time_entry_id, tag_id) | | |
|
|
|
|
## Drift Setup
|
|
|
|
```dart
|
|
// lib/core/database/app_database.dart
|
|
@DriftDatabase(tables: [Projects, TimeEntries, Tags, TimeEntryTags])
|
|
class AppDatabase extends _$AppDatabase {
|
|
AppDatabase() : super(_openConnection());
|
|
|
|
@override
|
|
int get schemaVersion => 1;
|
|
}
|
|
|
|
LazyDatabase _openConnection() {
|
|
return LazyDatabase(() async {
|
|
final dir = await getApplicationDocumentsDirectory();
|
|
final file = File(p.join(dir.path, 'timetrack.db'));
|
|
return NativeDatabase.createInBackground(file);
|
|
});
|
|
}
|
|
```
|
|
|
|
## DAOs
|
|
- `ProjectsDao` — CRUD for projects, watchAll(), watchActive()
|
|
- `TimeEntriesDao` — CRUD, watchByProject(), watchByDateRange(), getActiveEntry()
|
|
- `TagsDao` — CRUD, watchAll()
|
|
|
|
## Migration Strategy
|
|
- Schema version starts at `1`
|
|
- Increment `schemaVersion` for every breaking change
|
|
- Add a `MigrationStrategy` with `onUpgrade` steps
|
|
- Never drop columns — use nullable columns for additions
|
|
- Test migrations with `drift_dev` schema tests
|
|
|
|
## Riverpod Provider
|
|
|
|
```dart
|
|
@Riverpod(keepAlive: true)
|
|
AppDatabase appDatabase(AppDatabaseRef ref) {
|
|
final db = AppDatabase();
|
|
ref.onDispose(db.close);
|
|
return db;
|
|
}
|
|
```
|