MO-Fuel-Tax-Back/docs/erd.md

SQLite entity-relationship diagram

Local database: ApplicationDocumentsDirectory/FuelTaxTracker/fuel_tax_tracker.db
Schema version: 5 (defined in lib/services/database_service.dart / lib/services/db_schema.dart)

erDiagram
    VEHICLES ||--o{ FUEL_ENTRIES : "owns (vehicle_id)"

    VEHICLES {
        TEXT id PK "UUID, hidden, immutable"
        TEXT vin "required; unique among active rows, app-enforced"
        TEXT nickname "nullable display label"
        INTEGER updated_at "UTC millis; newest-wins merge key"
        INTEGER deleted_at "nullable soft-delete tombstone"
        INTEGER dirty "local-only; 1 = not yet pushed"
    }

    FUEL_ENTRIES {
        TEXT id PK "UUID"
        TEXT vehicle_id FK "references vehicles.id — no SQLite FK"
        INTEGER date "purchase datetime, local millis"
        REAL gallons
        REAL price_per_gallon
        REAL total_cost
        TEXT receipt_image_path "nullable on-device path"
        TEXT receipt_drive_file_id "nullable cloud file id"
        INTEGER updated_at "UTC millis; newest-wins merge key"
        INTEGER deleted_at "nullable soft-delete tombstone"
        INTEGER dirty "local-only; 1 = not yet pushed"
    }

    AD_FREE_ENTITLEMENT {
        INTEGER id PK "always 1 (CHECK id = 1)"
        INTEGER ad_free_until "nullable UTC millis"
        INTEGER updated_at "UTC millis; newest-wins merge key"
    }

Relationships

From	To	Cardinality	Enforced by
vehicles	fuel_entries	1 : 0..n	Application (FuelEntry.vehicleId → Vehicle.id). There is no FOREIGN KEY clause.
ad_free_entitlement	—	singleton	PRIMARY KEY CHECK (id = 1)

VIN is not the foreign key. Fuel entries reference the hidden vehicle id so the user can edit a VIN without breaking receipts or sync history.

Indexes and constraints

• vehicles.id — PRIMARY KEY
• fuel_entries.id — PRIMARY KEY
• idx_fuel_entries_vehicle_id on fuel_entries(vehicle_id)
• ad_free_entitlement.id — PRIMARY KEY CHECK (id = 1)
• VIN uniqueness among active (deleted_at IS NULL) vehicles is enforced in DatabaseService.vinExists / AppState.addVehicle / AppState.updateVehicle, not by a unique index. Two offline devices can independently add the same VIN; that rare conflict is accepted rather than merged field-by-field.

Sync and lifecycle columns

Every synced table carries updated_at. Cloud merge is last-write-wins on that timestamp (INSERT OR REPLACE of a remote row that is new or newer). See mergeVehiclesSql, mergeFuelEntriesSql, and mergeAdFreeEntitlementSql in db_schema.dart.

Column	Scope	Meaning
updated_at	all three tables	Newest value wins when merging a remote copy.
deleted_at	vehicles, fuel_entries	Soft-delete tombstone. Rows are never hard-deleted, so a deletion can propagate to other devices instead of being resurrected by a stale remote copy.
dirty	vehicles, fuel_entries only	Local bookkeeping: 1 = not yet pushed. Cleared to 0 on merge-in. Not mapped onto the Dart models.
receipt_image_path	fuel_entries	Device-local filesystem path. Forced to NULL when merging a remote row — a path from another phone is never valid here.
receipt_drive_file_id	fuel_entries	Opaque id of the uploaded receipt on the active cloud provider. Presence means “already uploaded”; a pending upload is path IS NOT NULL AND drive_file_id IS NULL.

ad_free_entitlement has no dirty or deleted_at. It is a single row that exists so a consumable Play Store purchase (which cannot be restored after consume) can survive reinstall via the same cloud merge as vehicles and fuel entries.

What is not in SQLite

Preferences live in SharedPreferences, not this database: theme, onboarding/agreement flags, cloud provider + folder ids, keep-photos-locally, stale-lock timeout, estimated-refund toggle.