Carrier Driver Recruiting MVR Automation: Continuous Monitoring & Annual Review Pipelines

Core Database Schema Patterns

The foundation of an auditable system is a well-normalized driver qualification schema. Key tables include:

drivers

CREATE TABLE drivers (
    id UUID PRIMARY KEY,
    carrier_id UUID NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    cdl_number TEXT NOT NULL,
    cdl_state CHAR(2) NOT NULL,
    date_of_birth DATE NOT NULL,
    hire_date DATE NOT NULL,
    termination_date DATE,
    qualification_status TEXT CHECK (qualification_status IN ('qualified', 'pending_review', 'disqualified', 'suspended')),
    last_mvr_pull_date DATE,
    last_annual_review_date DATE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

mvr_events

CREATE TABLE mvr_events (
    id UUID PRIMARY KEY,
    driver_id UUID REFERENCES drivers(id),
    event_type TEXT NOT NULL, -- 'license_status_change', 'new_citation', 'suspension', 'reinstatement'
    event_date DATE NOT NULL,
    description TEXT NOT NULL,
    source TEXT NOT NULL, -- 'state_api', 'continuous_monitor', 'manual_pull'
    raw_payload JSONB,
    processed_at TIMESTAMPTZ DEFAULT NOW(),
    requires_review BOOLEAN DEFAULT FALSE
);

annual_reviews

CREATE TABLE annual_reviews (
    id UUID PRIMARY KEY,
    driver_id UUID REFERENCES drivers(id),
    review_due_date DATE NOT NULL,
    review_completed_date DATE,
    certificate_signed_at TIMESTAMPTZ,
    certificate_signature TEXT,
    violations_reported JSONB,
    mvr_attached BOOLEAN DEFAULT FALSE,
    reviewer_id UUID,
    status TEXT CHECK (status IN ('due', 'in_progress', 'completed', 'overdue')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Materialized views or scheduled queries maintain the current qualification_status by combining the most recent MVR event with completion status of the required annual review.