Skip to content

Tachograph data parsing, storage and access

1. Scope and guarantees

This document describes the technical path of driver-card C files and vehicle-unit M files from the immutable DDD original through parsing, signature verification, database storage, cross-archive deduplication and repository access.

  • The compressed DDD original is the auditable source of truth.
  • Archive, signature, section and raw-record evidence remains archive-specific.
  • Reparsing replaces only derived observations for that archive.
  • person and vehicle aggregate views deduplicate across every linked archive.
  • A single-archive view still reproduces only that file's observations.
  • Deduplication never changes parser or signature status.
flowchart LR
    A["DDD input"] --> B["Local GZIP original"]
    B --> C["Archive metadata and SHA-256"]
    B --> D{"Binary classification"}
    D -->|"C file"| E["DriverCardFullParser"]
    D -->|"M file"| F["VehicleUnitParser"]
    E --> G["C signature verification"]
    F --> H["M signature verification"]
    G --> I["Archive-specific C tables"]
    H --> J["Archive-specific M tables"]
    I --> K["Canonical aggregate view"]
    J --> K
    K --> L["Repository"]
    L --> M["UI / reporting / compliance"]
    I --> N["Single-archive view"]
    J --> N

2. Archive and common import pipeline

ddd_archive holds the archive ID, file name, original and stored hashes, storage path, sizes, source, timestamps, DDD type, generation, parse/signature status, identity fields and covered date range.

TachographImportService.import_archive_id() performs these steps:

  1. Load the archive row and reject a logically deleted archive unless explicitly allowed.
  2. Read the DDD bytes from the GZIP object.
  3. Classify the binary content; the file name is not authoritative.
  4. Record classification and an audit event under a new correlation ID.
  5. Run the C or M parser.
  6. Verify exact signed bytes against the trust store.
  7. Replace archive-specific derived observations and update archive status.
  8. Reconcile entity links, download deadlines and cached evaluations.
Column Content Purpose
file_hash_sha256 SHA-256 of uncompressed DDD bytes proves the original functional content
stored_hash_sha256 SHA-256 of the stored GZIP object detects storage damage or modification 
sequenceDiagram
    participant Import as TachographImportService
    participant Archive as ddd_archive
    participant File as GZIP original
    participant Parser as C/M parser
    participant Verify as Signature service
    participant DB as Observation tables

    Import->>Archive: Load archive metadata
    Import->>File: Read exact DDD bytes
    Import->>Parser: Classify and parse
    Parser-->>Import: Structure, identity and source references
    Import->>Verify: Verify signed blocks/sections
    Verify-->>Import: VALID / INVALID / UNVERIFIABLE / MISSING
    Import->>DB: Replace observations for archive
    Import->>Archive: Update generation, range and status

3. C-file parser

Segmentation

DriverCardFullParser preserves every EF and signature boundary. Each segment has a five-byte header: two-byte file ID, one-byte kind (0 G1 data, 1 G1 signature, 2 G2 data, 3 G2 signature), and a two-byte big-endian length. Payloads extending past the file or trailing bytes after the last segment are parse errors. Offset, length and SHA-256 are calculated for every payload.

Important EFs include:

EF Data
0501, 0525 application identification, generation and record counts
0520, 0521 card holder and driving-licence identity
0504 daily driver activity
0502, 0503 events and faults
0505 vehicles used and odometers
0506, 0524 places and GNSS positions
0526–0530 G2V2 authentication, borders and loading data
C100, C101, C108, C109 card, CardSign, CA and link certificates

Generation is derived as G1, G2 or G2V2 and mapped to G1, G2_V1 or G2_V2 in the archive row.

The activity ring buffer is followed by its oldest/newest pointers. Daily-record headers and payloads are read cyclically, so a record may start in the final EF bytes and continue at offset zero; traversal stops only after the record identified by activityPointerNewestRecord. A length-guided linear scan is the guarded fallback. Daily records are deduplicated by date and presence counter. Minute changes become intervals for BREAK_REST, AVAILABILITY, WORK and DRIVING. Event/fault arrays use counts from application identification. Vehicle, place and GNSS records retain timestamps, registration, VIN where present, odometers, coordinates, accuracy and authentication state. Empty records are not emitted.

flowchart TD
    A["C file bytes"] --> B["Validate segment headers and boundaries"]
    B --> C["Build EF segments with offset and SHA-256"]
    C --> D["Read application identification"]
    D --> E["Determine generation and record counts"]
    E --> F["Identity and card metadata"]
    E --> G["Activity ring buffer"]
    E --> H["Events and faults"]
    E --> I["Vehicles, places and GNSS"]
    F --> J["Normalised observations"]
    G --> J
    H --> J
    I --> J
    J --> K["Verify signed EFs"]
    K --> L["Replace archive-specific C rows"]

4. M-file parser

Every section starts with 0x76 and a TREP code.

Generation TREP codes Logical sections
G1 01–05 overview, activities, events/faults, speed, technical
G2 v1 21–25 the same areas encoded as RecordArrays
G2 v2 31–35 G2-v2 records including additional GNSS/border/load data

Mixed generations, impossible lengths, data after a signature, or incomplete mandatory structures fail parsing. A missing detailed-speed section is an optional-download warning, not a signature error.

G1 uses fixed or count-driven structures followed by a 128-byte signature. Two 194-byte certificates at the start of the overview are excluded from the signed data as required by Annex IB. VIN is read as 17 characters. Country and code-page bytes are skipped before the 13-character registration number. The covered range comes from VuDownloadablePeriod, not current VU time.

G2 uses RecordArrays with type, size and count. size × count is checked before reading. Certificate records (0x04, 0x0F) and the single signature record (0x08) remain separate from functional records. Each functional record receives section index/code, record index and SHA-256 of exact bytes.

Both paths decode identity, covered period, activities, card sessions, events, faults, overspeed, speed, technical/calibration, position, border, load and midnight-odometer observations. Unknown bounded records remain available as raw evidence and warnings.

Compact speed storage

All generations use ddd_vu_speed_block: Unix start_epoch, exactly 60 speed bytes including 0xFF, and one binary 32-byte source SHA-256. 0xFF stays in the stored block but is omitted from measurement results. Date filtering, offset and limit apply after second-by-second expansion.

flowchart TD
    A["M file bytes"] --> B{"TREP generation"}
    B -->|"G1"| C["Fixed/count-driven sections"]
    B -->|"G2 v1/v2"| D["Validate RecordArrays"]
    C --> E["Separate signed body and 128-byte signature"]
    D --> F["Separate certificates, records and signature"]
    E --> G["Normalise observations"]
    F --> G
    G --> H["SHA-256 per source record"]
    G --> I["60-second speed blocks"]
    H --> J["Verify each section"]
    I --> J
    J --> K["Replace archive-specific M rows"]

5. Signature model

tachograph_trust_anchor stores generation, key ID, SHA-256 fingerprint, certificate/key material, validity, revocation, source and active state.

  • C G1 uses card/CA certificates and RSA PKCS#1 with SHA-1 over exact data blocks.
  • C G2/G2V2 uses CardSign/MSCA and optional link certificates with ECDSA over signed EFs.
  • M G1 uses RSA PKCS#1/SHA-1; M G2 verifies each section with its certificate-defined ECC/hash chain.
  • Per-block results are in ddd_drivercard_signature; per-section results are in ddd_vu_signature.

The newest plausible signed functional timestamp is the certificate reference time. Archive time is a warned fallback. Overall precedence is INVALID, UNVERIFIABLE, MISSING, then VALID.

6. Database tables and indexes

Archive and evidence

ddd_archive, ddd_audit_log, fleet_audit_log, tachograph_trust_anchor and app_schema_migration preserve source, audit, trust and schema-version information.

C tables

ddd_drivercard_import, ddd_drivercard_activity, ddd_drivercard_event, ddd_drivercard_fault, ddd_drivercard_vehicle_used, ddd_drivercard_position and ddd_drivercard_signature hold typed archive observations. Legacy-compatible raw_json and source_json columns remain but new imports store NULL because all required values can be reproduced from the original and typed columns.

M tables

ddd_vehicle_unit_import, ddd_vu_section, ddd_vu_signature, ddd_vu_raw_record, the activity/session/event/fault/overspeed tables, ddd_vu_speed_block, technical/calibration tables, position/border/load tables, and ddd_vu_odometer_midnight hold typed observations. ddd_vu_technical.details_json stores the complete structured VU identification used by the field tree. fleet_vehicle_vu_link links every matching M archive to a vehicle master record.

erDiagram
    DDD_ARCHIVE ||--o| DDD_DRIVERCARD_IMPORT : "is C file"
    DDD_ARCHIVE ||--o| DDD_VEHICLE_UNIT_IMPORT : "is M file"
    DDD_DRIVERCARD_IMPORT ||--o{ DDD_DRIVERCARD_ACTIVITY : contains
    DDD_DRIVERCARD_IMPORT ||--o{ DDD_DRIVERCARD_SIGNATURE : proves
    DDD_VEHICLE_UNIT_IMPORT ||--o{ DDD_VU_SECTION : contains
    DDD_VEHICLE_UNIT_IMPORT ||--o{ DDD_VU_SPEED_BLOCK : contains
    DDD_ARCHIVE ||--o{ DDD_FACT_PROVENANCE : supplies
    DDD_CANONICAL_FACT ||--o{ DDD_FACT_PROVENANCE : sourced_by
    DDD_FACT_KIND ||--o{ DDD_CANONICAL_FACT : types

Important indexes cover archive name/date/type/status, normalised card number plus generation, archive/time and card/time activity access, vehicle/archive links, and canonical fact time/cluster/provenance. ddd_vu_speed_block is WITHOUT ROWID; its primary key beginning with archive and start time is already the required access path.

Runtime connections use WAL, foreign_keys=ON, busy_timeout=30000, synchronous=NORMAL and a 1,000-page automatic checkpoint. Schema work runs once before UI creation; repositories open with ensure_schema=False during normal operation.

7. Cross-archive deduplication

Archive-specific typed tables remain exact provenance. Aggregate views use one of two compatible paths:

  1. Materialised ddd_canonical_fact and ddd_fact_provenance rows.
  2. A compact SQL fallback over typed tables using ROW_NUMBER, COUNT and GROUP_CONCAT when no materialised provenance exists.

The compact production migration deliberately uses the fallback to avoid duplicating all payloads. Both paths return source count, source archive IDs and preferred navigation source.

Materialised normalisation uses Unicode NFC/collapsed whitespace, alphanumeric uppercase identifiers, UTC seconds, exact numeric/coordinate values and exact blobs. Placeholder-only identifiers are empty. fact_hash is a binary SHA-256 over kind and full normalised content; cluster_hash hashes identifying cluster fields. Variants in one cluster remain separate and can be marked as conflicts.

For card observations, the complete normalised card number is the only cross-archive and cross-source identity. card_generation remains source metadata in the raw observation and provenance, but is neither part of the fact hash nor a C/M matching filter. G1 card/G1 vehicle, G1 card/G2 vehicle, G2 card/G1 vehicle and G2 card/G2 vehicle combinations are therefore handled identically.

The fallback partitions by every functional field. Navigation preference is verified/valid, then G2 v2, G2 v1, G1, then newest archive. This never changes functional content. Speed blocks partition by start epoch and the complete 60-byte blob before expansion.

Vehicle technical data is deduplicated by record type, value and the complete normalised detail structure. Existing rows without details_json are reconstructed in a worker from the immutable compressed M original. The vehicle record shows each identical variant once with its source count; archive identifiers and file names remain navigation metadata. New imports persist details_json directly and include it in the technical fact hash.

flowchart TD
    A["Vehicle or person"] --> B["Resolve linked active archives"]
    B --> C{"Materialised provenance exists?"}
    C -->|"Yes"| D["Select fact_hash and group sources"]
    C -->|"No"| E["Partition typed rows with window functions"]
    D --> F["Detect cluster variants"]
    E --> G["Select exact duplicate representative"]
    F --> H["Apply date filter"]
    G --> H
    H --> I["Sort, limit and offset"]
    I --> J["DTOs for UI/reporting"]

8. Repository access

list_vehicle_facts, count_vehicle_facts, list_vehicle_speed_seconds, list_person_facts, and list_archive_observations are the primary internal interfaces. Aggregate access resolves linked archives, deduplicates, filters, sorts and only then paginates. Single-archive access filters directly by archive ID and preserves section/record order.

UI calls execute through UiJobController workers. Each worker opens its own SQLite connection and returns only lists, dictionaries or DTOs. Widgets and models are updated only by the UI-thread callback.

9. Idempotency, migration and diagnostics

Reimport replaces derived rows for one archive. Parser failure does not discard previously verified details. Maintenance migration operates on a SQLite copy, verifies every original/hash and count, removes JSON caches, compacts with VACUUM INTO, runs PRAGMA quick_check, and performs an atomic or next-start staged replacement with a rollback backup.

Administrators may use read-only checks such as:

PRAGMA quick_check;
PRAGMA journal_mode;

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

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

Direct database writes are unsupported; use import, reparse, link, migration and restore services so audit, hashes and cache invalidation remain consistent.