Database schema V2 reference

1. Purpose and scope

This document is the technical reference for the SQLite database format used by FLEET Mira storage format V2. It covers the physical database contract, versioned bootstrap, the normalized tachograph fact model, provenance and deduplication, the surrounding business schemas, integrity rules, migration from the legacy format, and supported operational access.

The schema is an internal application contract. Integrations must use the documented application services and APIs for writes. Read-only SQL is useful for diagnostics and controlled reporting, but direct inserts, updates, deletes, schema changes, or trigger changes are unsupported because they can bypass audit, hash verification, derived-data refresh, and migration bookkeeping.

Related documents:

• Tachograph data parsing, storage and access
• Asynchronous UI and background services
• Archiving, backup and restore

2. V2 invariants

Storage format V2 is identified by all of the following conditions:

1. app_schema_state.storage_format is 2.
2. ddd_archive has the integer surrogate key archive_pk in addition to the stable public text ID id.
3. The normalized fact core (ddd_fact_kind, ddd_fact, ddd_fact_source, and all typed payload tables) is complete.
4. Required fact and card/time indexes exist.
5. No legacy archive-owned fact table listed by the migration map remains in the database.
6. The stored schema fingerprint matches the SQL definitions of the V2 archive, fact, payload, and fact-index objects.

The bootstrap rejects a populated ddd_archive table without archive_pk. A legacy database must therefore be rebuilt with the dedicated V2 migration; it is never upgraded in place by normal application startup.

flowchart LR
    S["SQLite file"] --> M["app_schema_state"]
    M --> F{"storage_format = 2"}
    F --> A["ddd_archive.archive_pk"]
    F --> C["normalized fact core"]
    F --> I["required indexes"]
    F --> H["schema fingerprint"]
    A --> V["validate_storage_schema"]
    C --> V
    I --> V
    H --> V

3. Physical SQLite configuration

Setting	Runtime value	Rationale
Page size	8 KiB for newly created databases	Reduces B-tree depth and page overhead for the fact store.
Journal mode	WAL during application runtime	Allows readers to continue while the serialized write lane commits.
synchronous	NORMAL	WAL durability/performance balance.
Busy timeout	30,000 ms	Gives bounded concurrent work time to finish instead of immediately returning SQLITE_BUSY.
WAL auto-checkpoint	1,000 pages	Bounds WAL growth during ordinary operation.
Foreign keys	Enabled on every application connection	Enforces declared relationships and cascades.
Auto-vacuum	INCREMENTAL on a new database	Permits controlled space reclamation.

The distributable empty database is finalized as a compact single file in DELETE journal mode and vacuumed. Application startup switches it to WAL. The -wal and -shm files are therefore normal runtime companions and must be included in shutdown/checkpoint planning, but not copied independently as a database backup.

PRAGMA user_version is not the migration authority. FLEET Mira uses component rows in app_schema_migration and format state in app_schema_state.

4. Versioned bootstrap

bootstrap_database() is the only supported schema initialization entry point. It runs before the UI and background services construct repositories. Each component migration is recorded once with component, version, UTC application time, and duration.

Component	Current bootstrap version	Schema owner
archive	2	archive metadata, transfer state, and global audit dependencies
authentication	1	users, roles, permissions, login security, and UI state
device_management	1	devices, tenants, locations, groups, and status history
drivercard	2	driver-card import identity and signature evidence
fleet	2	persons, vehicles, links, due dates, RFID, licence checks, and notifications
finance	1	suppliers, invoices, costs, financial originals, e-invoices, and exports
vehicle_unit	4	VU import evidence, trust anchors, and normalized V2 fact schema
compliance	4	snapshots, rule-module configuration, and immutable catalog metadata
backup	1	targets, replication state, jobs, and database snapshots
reporting	3	odometer history, cost-report parameters, and report templates

The version is a per-component migration level, not the product version and not the storage-format number. A schema change must increment the owning component version. After migrations, startup validates V2, calculates its schema fingerprint, updates app_schema_state, commits, and runs PRAGMA quick_check when any migration was applied.

Migration ledger

app_schema_migration uses (component, version) as its primary key. app_schema_state is a key/value registry and currently owns at least:

Key	Meaning
storage_format	Physical tachograph storage generation; must equal 2.
storage_schema_fingerprint	SHA-256 over the normalized schema object's SQL definitions in deterministic order.

If no component migration ran and the calculated fingerprint differs from the registered fingerprint, startup fails. This prevents an unversioned manual DDL change from being silently accepted.

5. High-level data model

erDiagram
    DDD_ARCHIVE ||--o{ DDD_FACT_SOURCE : contains
    DDD_FACT_KIND ||--o{ DDD_FACT : classifies
    DDD_FACT_KIND ||--o{ DDD_FACT_SOURCE : classifies
    DDD_FACT ||--o{ DDD_FACT_SOURCE : proven_by
    DDD_FACT ||--|| TYPED_PAYLOAD : stores
    DDD_CARD_IDENTITY ||--o{ TYPED_PAYLOAD : normalizes
    DDD_ARCHIVE ||--o| DDD_DRIVERCARD_IMPORT : describes
    DDD_ARCHIVE ||--o| DDD_VEHICLE_UNIT_IMPORT : describes
    DDD_ARCHIVE ||--o{ DDD_VU_SECTION : segments
    DDD_ARCHIVE ||--o{ DDD_COMPLIANCE_SNAPSHOT : evaluates
    FLEET_PERSON ||--o{ FLEET_DRIVERCARD_LINK : owns
    FLEET_VEHICLE ||--o{ FLEET_VEHICLE_VU_LINK : owns
    DDD_ARCHIVE ||--o{ FLEET_VEHICLE_VU_LINK : links
    FLEET_FOREIGN_INVOICE ||--o{ FLEET_VEHICLE_COST_ENTRY : allocates
    FLEET_FINANCIAL_DOCUMENT }o--|| FLEET_FOREIGN_INVOICE : evidences
    BACKUP_TARGET ||--o{ BACKUP_ARCHIVE_STATE : replicates

The schema separates four concerns:

• Evidence: immutable originals, archive metadata, parser/signature records, raw hashes, and audit.
• Canonical facts: deduplicated observations independent of any one archive.
• Provenance: every occurrence of a fact in an archive, including section and raw-record identity.
• Business projections: people, vehicles, due dates, odometers, costs, reports, and backup state derived from or linked to evidence.

6. Archive and evidence layer

ddd_archive

ddd_archive is the root for every C- or M-file import.

Column group	Important columns	Meaning
Keys	archive_pk, id	Internal integer join key and stable public archive ID.
File identity	file_name, file_name_normalized, file_hash_sha256	Display/search name and SHA-256 of uncompressed DDD bytes.
Stored object	stored_path, compressed_size, stored_hash_sha256	GZIP object location, size, and hash.
Source	source_id, task_id, source_host, remote_path, source/download timestamps	Transfer provenance.
Lifecycle	archived_at, is_deleted, deletion actor/time/reason	Logical archive lifecycle.
Classification	ddd_type, generation, parse_status, signature_status	Parser and trust outcome.
Identity/range	identity_label, VIN, registration, data_from, data_to	Search and entity-linking projection.
Parser result	parsed_at, parser_message	Last parse attempt.

Active archives are unique by uncompressed file_hash_sha256. Logical deletion preserves the archive and audit row while removing it from active aggregate queries.

Parser and signature evidence

Table	Purpose
ddd_drivercard_import	Parsed card identity, generation, holder/licence metadata, parser version, signature summary, and import result.
ddd_drivercard_signature	Per signed EF block: generation, file ID, algorithm, certificate/trust fingerprints, verification time, and result.
ddd_vehicle_unit_import	VU generation, VIN, registration, serial, covered range, warnings, signature summary, and correlation ID.
ddd_vu_section	Section index/code, generation, source byte range, and SHA-256 of the exact signed bytes.
ddd_vu_signature	Per VU section signature result and certificate evidence.
ddd_vu_unknown_record	Bounded unsupported records with offsets, raw payload, and 32-byte raw hash so parsing remains loss-aware.
tachograph_trust_anchor	Versioned trust material used by signature verification.

Archive-owned evidence tables reference the public archive ID and normally use ON DELETE CASCADE. The compressed original remains the authoritative source for a future reparse.

7. Normalized fact model

Core tables

Table	Key	Responsibility
ddd_fact_kind	kind_id	Stable registry of typed fact kinds.
ddd_card_identity	card_pk	Deduplicated normalized card number plus the best display form.
ddd_fact	fact_pk	One canonical semantic observation, its full hash, cluster hash, kind, and optional event epoch.
ddd_fact_source	archive/kind/time/ordinal	One occurrence of a fact in one archive, including section, record, raw hash, and source-only generation.
ddd_fact_<kind>	fact_pk	Typed payload in compact SQLite-native fields.

ddd_fact_source is a WITHOUT ROWID table. Its composite primary key is ordered for deterministic per-archive replacement. It references ddd_archive, ddd_fact_kind, and ddd_fact; deleting an archive cascades its source rows. Orphan canonical facts are removed explicitly after archive replacement or removal.

Encodings

Logical value	Stored form
UTC timestamp	Signed integer Unix seconds.
Calendar day	Integer day offset from 1970-01-01.
Activity	0=BREAK_REST, 1=AVAILABILITY, 2=WORK, 3=DRIVING.
Card number	card_pk into ddd_card_identity.
Text	Unicode NFC, control-character removal, and collapsed whitespace.
Raw digest	Exactly 32 bytes.
Speed data	One BLOB containing the complete 60-second block; 0xFF remains a missing sample.

Card normalization uppercases and retains alphanumeric characters only. All-placeholder values are treated as absent. The longer available display representation wins without changing the normalized identity.

Fact and cluster hashes

The canonical fact_hash is SHA-256 over:

1. ASCII fact-kind name and a zero byte;
2. deterministic compact JSON of all encoded payload fields, sorted by key;
3. a zero byte and the raw BLOB for fact kinds such as speed blocks.

UNIQUE(kind_id, fact_hash) stores an identical observation once even when many archives contain it.

The cluster_hash uses only the fact kind's domain-specific cluster fields. It groups records that represent the same logical slot/time/key but may disagree in other values. If a cluster has more than one distinct full fact hash, aggregate queries set has_conflict=true; variants are retained rather than overwritten.

Provenance

ddd_fact_source records:

• archive and kind;
• source ordinal;
• canonical fact;
• event epoch or the reserved no-event sentinel for time-free facts;
• section index and code;
• record index;
• SHA-256 of the exact raw record, or a deterministic fallback hash;
• card generation when generation belongs to source evidence rather than semantic identity.

This design allows a canonical row to be deduplicated without losing which files, sections, records, or generations proved it.

Reparse transaction

For one archive and one fact kind, replace_archive_records() performs:

1. delete the old ddd_fact_source rows for that archive/kind;
2. normalize and encode each parser record;
3. calculate full and cluster hashes;
4. insert or reuse ddd_fact;
5. insert the typed payload if this is a new fact;
6. insert the archive-specific source row;
7. remove canonical facts no longer referenced by any archive.

The original archive row, signature evidence, and other archives remain unchanged.

8. Fact-kind catalog

Kind / payload table	Stored semantic fields	Cluster identity
vu.activity / ddd_fact_vu_activity	slot, change time, activity, card-present flag, crew flag, card	slot + change time
vu.card_session / ddd_fact_vu_card_session	slot, card, names, expiry, insert/withdraw times	slot + card + insert/withdraw times
vu.event / ddd_fact_vu_event	type, begin, end	type + begin + end
vu.fault / ddd_fact_vu_fault	type, begin, end	type + begin + end
vu.overspeed / ddd_fact_vu_overspeed	begin/end, maximum and average speed	begin + end
vu.speed_block / ddd_fact_vu_speed_block	start epoch, 60-byte speed BLOB	start epoch
vu.technical / ddd_fact_vu_technical	record type, value, detail JSON	record type
vu.calibration / ddd_fact_vu_calibration	purpose, workshop, VIN, registration, calibration times	purpose + calibration time
vu.position / ddd_fact_vu_position	position type, time, coordinates, raw hex	type + time
vu.border / ddd_fact_vu_border	crossing time, coordinates, raw hex	crossing time
vu.load / ddd_fact_vu_load	time, operation, coordinates, raw hex	time + operation
vu.odometer / ddd_fact_vu_odometer	source day, midnight time, odometer	source day
card.activity / ddd_fact_card_activity	card, activity, start/end, duration, day, crew, slot	card + start/end + slot
card.event / ddd_fact_card_event	card, event codes/text, begin/end, vehicle, place	card + numeric type + begin/end
card.fault / ddd_fact_card_fault	card, fault codes/text, begin/end, vehicle, place	card + numeric type + begin/end
card.vehicle / ddd_fact_card_vehicle	card, use interval, odometers, distance, registration, VIN, block counter	card + use interval + registration
card.position / ddd_fact_card_position	card, source, record/position times, country/region, odometer, coordinates, accuracy, authentication	card + source + record/position times

9. Query semantics and indexes

Aggregate queries

list_facts() returns one canonical fact across a caller-supplied archive scope. It:

• ignores logically deleted archives;
• filters by ddd_fact.event_epoch;
• returns source count and archive IDs;
• chooses a preferred source by verified parse/signature status, then generation (G2_V2, G2_V1, older), then newest archive;
• reports conflicting variants by cluster;
• caps a page at 1,000,000 rows.

Single-archive queries

list_archive_observations() starts at ddd_fact_source, preserves the source ordinal, section/record identity, raw hash, and generation, and returns only observations from that file. It deliberately does not collapse repeated source records.

Required indexes

• ix_ddd_fact_kind_time supports kind/time pagination.
• ix_ddd_fact_source_fact supports source aggregation and orphan checks.
• Card-bearing, time-bearing payloads have (card_pk, time, fact_pk) indexes.
• Archive search indexes cover active hash uniqueness, type/date, parse/signature status, normalized name, and archive time.

Indexes are dropped during bulk V2 rebuild and recreated only after all archives have been reparsed, followed by ANALYZE and PRAGMA optimize.

10. Compliance and audit

ddd_compliance_snapshot stores immutable, reproducible evaluation output: archive or subject scope, assessed range, ruleset and algorithm versions, source manifest, module manifest, configuration hash, report JSON, report SHA-256, creator, and time. Triggers reject updates and deletes.

compliance_rule_module_config stores the global enabled state and JSON settings per rule module. compliance_balm_catalog stores immutable catalog-version metadata; triggers reject update and delete operations.

Audit is split by responsibility:

Table	Scope
audit_log	Global user, administration, and cross-domain actions.
ddd_audit_log	Archive, parser, signature, export, and tachograph lifecycle.
fleet_audit_log	Fleet, deadline, RFID, finance, and related business objects.

Audit rows store technical object IDs and actors. They are append-oriented and are resolved by the UI to the current business record.

11. Business schema catalog

Authentication and application state

Tables	Purpose
app_role, app_permission, app_role_permission	Fixed roles and permission grants.
app_user	Argon2id login identity, role, locale, lock state, and password lifecycle.
app_login_attempt	Authentication attempt history and lockout evidence.
app_user_ui_state	Per-user view, table, filter, and layout state.
app_user_migration, user_roles	Controlled compatibility/migration state for earlier user models.
app_schema_migration, app_schema_state	Version ledger and physical-format contract.

Device management and transfer state

Tables	Purpose
customers, locations	Tenant/site reference data.
devices	Device identity, endpoint configuration, protected access fields, inventory, status projection, and lifecycle.
device_status_history	Time-ordered status checks per device.
device_groups, device_group_members	Operational grouping.
processed_files	Download/import idempotency state for source files.

Fleet master data and links

Tables	Purpose
fleet_person, fleet_vehicle	Person and vehicle master records with logical archival.
fleet_drivercard_link	Unique card-to-person assignment and source archive evidence.
fleet_vehicle_vu_link	Vehicle-to-M-file assignment, match basis, and confirmation state.
fleet_mobile_device	Fleet-related mobile-device assignments.
fleet_due_type, fleet_due	Configurable person/vehicle deadlines and managed download schedules; vehicle rows support time, mileage and earliest-of-both limits with captured completion evidence.
fleet_license_check	Driving-licence check results.
fleet_rfid_assignment	Current and historical RFID-to-person assignments.
fleet_rfid_import_file, fleet_rfid_import_row	Imported terminal files, row fingerprints, validation, and processing results.

Open tachograph download schedules are protected by a partial unique index on object, manager, and active state. Active RFID UIDs and current person assignments are also unique.

Notifications

Tables	Purpose
fleet_notification_rule	Enabled rules, schedules, thresholds, and message configuration.
fleet_notification_recipient	Rule recipients and delivery targets.
fleet_notification_delivery	Durable delivery attempt, retry, and result history.

Finance and e-invoices

Tables	Purpose
fleet_supplier	Supplier identity, contact, tax, VAT, endpoint, and company identifiers.
fleet_cost_category	Cost classification and fixed/variable defaults.
fleet_foreign_invoice	Invoice/credit-note header, dates, totals, workflow, duplicate override, reversal, and audit actors.
fleet_vehicle_cost_entry	Vehicle/category allocation, validity period, signed amounts, tax, workflow, and reversal links.
fleet_financial_document	Local original/manifest identity, SHA-256, archive status, optional backup state, retention, legal hold, and supersession.
fleet_document	Versioned personnel/vehicle record documents with SHA-256 verified local archive, backup identity, archive lifecycle and controlled purge evidence.
fleet_einvoice_import	Syntax/profile, original/XML hashes, validator result, documents, and supplier match.
fleet_einvoice_line	Structured source lines and adjustments with exact monetary values and allocation status.
fleet_einvoice_allocation	Cent-accurate line split into vehicle cost entries, including confirmed suggestions.
fleet_finance_export	Durable accounting export/outbox state and retries.
fleet_finance_settings	Finance/export/report calculation settings.

Money uses integer cents; VAT rates use integer basis points. Reversal objects retain links to their originals. A non-overridden active invoice is unique by supplier, case-insensitive document number, and document date. Original files live in the financial archive, not in SQLite; the database stores paths, hashes, sizes, manifests, and state.

Odometer and reporting

Tables	Purpose
fleet_vehicle_odometer_reading	M-, C-, manual, and imported readings with source, quality, approval, and archive link.
fleet_report_template	Versioned system/user report templates, context, ownership, defaults, and JSON definition.

M-file midnight readings are authoritative. Manual and C-file values may fill missing boundaries but cannot replace a valid M value. Cost/km is calculated from complete vehicle-month intersections rather than persisted as an uncontrolled aggregate.

Backup and restore

Tables	Purpose
backup_target	Provider, root, active state, protected credentials/key, encryption, and health timestamps.
backup_archive_state	Per-target tachograph archive replication state, attempts, hashes, and next retry.
backup_database_snapshot	Snapshot hashes, sizes, remote identity, retention class, verification, and correlation ID.
backup_job	Coarse-grained run progress and result.

A partial unique index permits only one active backup target. Backup metadata does not replace the local archive state in ddd_archive or fleet_financial_document.

12. Complete table inventory

The current V2 schema contains these logical groups. A deployed database may contain no business rows, but its reference rows and schema objects are initialized.

Group	Tables
Schema/authentication	app_schema_migration, app_schema_state, app_role, app_permission, app_role_permission, app_user, app_login_attempt, app_user_migration, app_user_ui_state, user_roles
Audit	audit_log, ddd_audit_log, fleet_audit_log
Device/transfer	customers, locations, devices, device_status_history, device_groups, device_group_members, processed_files
Archive/parser evidence	ddd_archive, ddd_drivercard_import, ddd_drivercard_signature, ddd_vehicle_unit_import, ddd_vu_section, ddd_vu_signature, ddd_vu_unknown_record, tachograph_trust_anchor
Fact core	ddd_fact_kind, ddd_card_identity, ddd_fact, ddd_fact_source
VU fact payloads	ddd_fact_vu_activity, ddd_fact_vu_card_session, ddd_fact_vu_event, ddd_fact_vu_fault, ddd_fact_vu_overspeed, ddd_fact_vu_speed_block, ddd_fact_vu_technical, ddd_fact_vu_calibration, ddd_fact_vu_position, ddd_fact_vu_border, ddd_fact_vu_load, ddd_fact_vu_odometer
Card fact payloads	ddd_fact_card_activity, ddd_fact_card_event, ddd_fact_card_fault, ddd_fact_card_vehicle, ddd_fact_card_position
Compliance	ddd_compliance_snapshot, compliance_rule_module_config, compliance_balm_catalog
Fleet	fleet_person, fleet_vehicle, fleet_drivercard_link, fleet_vehicle_vu_link, fleet_mobile_device, fleet_due_type, fleet_due, fleet_document, fleet_license_check, fleet_rfid_assignment, fleet_rfid_import_file, fleet_rfid_import_row
Notifications	fleet_notification_rule, fleet_notification_recipient, fleet_notification_delivery
Finance/e-invoice	fleet_supplier, fleet_cost_category, fleet_foreign_invoice, fleet_vehicle_cost_entry, fleet_financial_document, fleet_einvoice_import, fleet_einvoice_line, fleet_einvoice_allocation, fleet_finance_export, fleet_finance_settings
Reporting	fleet_vehicle_odometer_reading, fleet_report_template
Backup	backup_target, backup_archive_state, backup_database_snapshot, backup_job

13. Legacy-to-V2 migration

The supported migration is an out-of-place rebuild performed by scripts/migrate_demo_database_v2.py.

flowchart TD
    A["Legacy database"] --> B["Preflight quick/FK checks"]
    B --> C["Verify every GZIP and original DDD hash"]
    C --> D["Bootstrap empty V2 work database"]
    D --> E["Copy authoritative business rows"]
    E --> F["Reparse every active C/M original"]
    F --> G["Rebuild odometers and download due dates"]
    G --> H["Compare counts, distinct facts, ranges, speed digests, signatures"]
    H --> I["Create indexes, ANALYZE, optimize, FK/quick checks"]
    I --> J["VACUUM and size check"]
    J --> K{"--apply"}
    K -->|"no"| L["Keep validated candidate"]
    K -->|"yes"| M["Rename source to V1 backup, candidate to source"]

Preflight

• source exists and is not already V2;
• work database, WAL, and SHM do not exist;
• a requested backup target does not already exist;
• free space is at least 512 MiB or 110% of source size, whichever is larger;
• source quick_check and foreign_key_check pass;
• each active C/M archive has a readable GZIP object;
• stored-object and uncompressed-original SHA-256 values match the archive row.

Rebuild

Authoritative business tables are copied by common column name. Derived tachograph tables, migration state, audit projections, automatically managed download due dates, and non-manual odometer projections are rebuilt. Active archives are reparsed from original bytes into the new fact model. Fact indexes are created after bulk loading.

Semantic validation

For every fact kind the migration compares:

• observation count;
• distinct semantic count;
• minimum and maximum event time;
• per-archive observation counts;
• exact expanded speed-byte count and digest;
• archive signature status.

It then validates the schema, foreign keys, quick check, 8 KiB page size, and post-vacuum database. A large source must meet the migration's compaction threshold.

Atomic activation

--apply is refused while source WAL/SHM files exist. The source is renamed to the V1 backup, then the validated candidate is renamed to the source path. If the second rename fails, the backup is restored. The V1 backup is not deleted automatically.

14. Deployment database contract

scripts/create_deployment_database.py creates a fresh V2 database, validates integrity and foreign keys, validates the storage schema and 8 KiB page size, confirms format 2, and verifies the expected seed counts. Business tables must be empty. The initial administrator is active, uses English/en-GB, and must change the initial password.

After validation, the database is vacuumed into one file and accompanied by archive.db.sha256. Release tooling must verify that checksum against the exact database file after the final write.

15. Operational checks

Use a read-only connection for diagnostics:

SELECT key, value
FROM app_schema_state
ORDER BY key;

SELECT component, version, applied_at, duration_ms
FROM app_schema_migration
ORDER BY component, version;

PRAGMA quick_check;
PRAGMA foreign_key_check;

Fact counts and provenance:

SELECT k.name,
       COUNT(DISTINCT f.fact_pk) AS canonical_facts,
       COUNT(s.fact_pk) AS source_observations,
       COUNT(DISTINCT s.archive_pk) AS source_archives
FROM ddd_fact_kind k
LEFT JOIN ddd_fact f ON f.kind_id = k.kind_id
LEFT JOIN ddd_fact_source s ON s.fact_pk = f.fact_pk
GROUP BY k.kind_id, k.name
ORDER BY k.kind_id;

Archive health:

SELECT ddd_type, generation, parse_status, signature_status, COUNT(*) AS archives
FROM ddd_archive
WHERE is_deleted = 0
GROUP BY ddd_type, generation, parse_status, signature_status
ORDER BY ddd_type, generation, parse_status, signature_status;

Do not use diagnostic SQL to repair data. Restore a verified database or re-run the owning import, migration, reconciliation, or rebuild service so hashes, provenance, audit, and projections remain consistent.