How to Implement Simple Document Management for Trucking Companies

This approach has several advantages:

Direct mapping to UI: Each document type corresponds to a specific field in the user interface
Simple queries: No need to filter by document type
One row per carrier: Each carrier has a single record with all their documents
Easy to track: Missing documents are immediately apparent (NULL values)

Backend Implementation for document management

The Express.js routes to manage these documents are straightforward:

CREATE TABLE `carrier_documents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `carrier_id` bigint(20) unsigned NOT NULL,
  `dispatch_agreement` varchar(255) DEFAULT NULL,
  `certificate_insurance` varchar(255) DEFAULT NULL,
  `w9` varchar(255) DEFAULT NULL,
  `mc_authority` varchar(255) DEFAULT NULL,
  `driver_license` varchar(255) DEFAULT NULL,
  `workers_comp_policy` varchar(255) DEFAULT NULL,
  `voided_check` varchar(255) DEFAULT NULL,
  `carb` varchar(255) DEFAULT NULL,
  `setup_packet` varchar(255) DEFAULT NULL,
  `other_docs_1` varchar(255) DEFAULT NULL,
  `other_docs_2` varchar(255) DEFAULT NULL,
  `other_docs_3` varchar(255) DEFAULT NULL,
  `other_docs_4` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_carrier_id` (`carrier_id`),
  CONSTRAINT `fk_carrier_documents_carrier` FOREIGN KEY (`carrier_id`) REFERENCES `carriers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

This approach has several advantages:

Direct mapping to UI: Each document type corresponds to a specific field in the user interface
Simple queries: No need to filter by document type
One row per carrier: Each carrier has a single record with all their documents
Easy to track: Missing documents are immediately apparent (NULL values)

Backend Implementation

The Express.js routes to manage these documents are straightforward:

import express from 'express';
import multer from 'multer';
import path from 'path';
import fs from 'fs';
import db from '../db';
import { OkPacket, RowDataPacket } from 'mysql2';

const router = express.Router();

// Configure multer for file uploads
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    // Create directory if it doesn't exist
    const uploadDir = path.join(__dirname, '../../uploads/carrier-documents');
    if (!fs.existsSync(uploadDir)) {
      fs.mkdirSync(uploadDir, { recursive: true });
    }
    cb(null, uploadDir);
  },
  filename: (req, file, cb) => {
    // Generate unique filename with original extension
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    const ext = path.extname(file.originalname);
    cb(null, `${file.fieldname}-${uniqueSuffix}${ext}`);
  }
});

// File filter to allow only certain file types
const fileFilter = (req, file, cb) => {
  const allowedTypes = [
    'application/pdf',
    'image/jpeg',
    'image/png',
    'image/jpg',
    'application/msword',
    'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
    'application/vnd.ms-excel',
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
  ];
  
  if (allowedTypes.includes(file.mimetype)) {
    cb(null, true);
  } else {
    cb(new Error('Invalid file type. Only PDF, JPEG, PNG, DOC, DOCX, XLS, and XLSX files are allowed.'));
  }
};

// Configure multer upload
const upload = multer({
  storage,
  fileFilter,
  limits: { fileSize: 10 * 1024 * 1024 } // 10MB limit
});

// Define the fields for document uploads
const documentFields = [
  { name: 'dispatch_agreement', maxCount: 1 },
  { name: 'certificate_insurance', maxCount: 1 },
  { name: 'w9', maxCount: 1 },
  { name: 'mc_authority', maxCount: 1 },
  { name: 'driver_license', maxCount: 1 },
  { name: 'workers_comp_policy', maxCount: 1 },
  { name: 'voided_check', maxCount: 1 },
  { name: 'carb', maxCount: 1 },
  { name: 'setup_packet', maxCount: 1 },
  { name: 'other_docs_1', maxCount: 1 },
  { name: 'other_docs_2', maxCount: 1 },
  { name: 'other_docs_3', maxCount: 1 },
  { name: 'other_docs_4', maxCount: 1 }
];

// Get documents for a specific carrier
router.get('/:carrierId', async (req, res) => {
  const connection = await db.getConnection();
  try {
    const [rows] = await connection.query(
      'SELECT * FROM carrier_documents WHERE carrier_id = ?',
      [req.params.carrierId]
    );
    
    if (rows.length === 0) {
      return res.status(200).json({ error: 'No documents found for this carrier' });
    }
    
    res.json(rows[0]);
  } catch (error) {
    console.error('Error fetching carrier documents:', error);
    res.status(500).json({ error: 'Internal server error' });
  } finally {
    connection.release();
  }
});

// Upload/update documents for a carrier
router.post('/:carrierId', upload.fields(documentFields), async (req, res) => {
  const connection = await db.getConnection();
  try {
    const carrierId = req.params.carrierId;
    const files = req.files;
    
    // Check if carrier exists
    const [carrierRows] = await connection.query(
      'SELECT * FROM carriers WHERE id = ?',
      [carrierId]
    );
    
    if (carrierRows.length === 0) {
      return res.status(404).json({ error: 'Carrier not found' });
    }
    
    // Check if document record already exists
    const [docRows] = await connection.query(
      'SELECT * FROM carrier_documents WHERE carrier_id = ?',
      [carrierId]
    );
    
    const updateFields = {};
    
    // Process uploaded files
    for (const field of documentFields) {
      if (files[field.name] && files[field.name].length > 0) {
        const file = files[field.name][0];
        // Store the file path relative to the uploads directory
        updateFields[field.name] = `/uploads/carrier-documents/${file.filename}`;
      }
    }
    
    let result;
    
    if (docRows.length === 0) {
      // Create new document record
      const insertFields = { carrier_id: carrierId, ...updateFields };
      const [insertResult] = await connection.query(
        `INSERT INTO carrier_documents SET ?`,
        [insertFields]
      );
      result = { id: insertResult.insertId, ...insertFields };
    } else {
      // Update existing document record
      if (Object.keys(updateFields).length === 0) {
        return res.status(400).json({ error: 'No files uploaded' });
      }
      
      // Get existing document record to handle file deletion
      const existingDoc = docRows[0];
      
      // Delete old files if they're being replaced
      for (const field in updateFields) {
        if (existingDoc[field]) {
          const oldFilePath = path.join(__dirname, '../../', existingDoc[field]);
          if (fs.existsSync(oldFilePath)) {
            fs.unlinkSync(oldFilePath);
          }
        }
      }
      
      // Update document record
      await connection.query(
        `UPDATE carrier_documents SET ? WHERE carrier_id = ?`,
        [updateFields, carrierId]
      );
      
      // Get updated record
      const [updatedRows] = await connection.query(
        'SELECT * FROM carrier_documents WHERE carrier_id = ?',
        [carrierId]
      );
      
      result = updatedRows[0];
    }
    
    res.status(200).json(result);
  } catch (error) {
    console.error('Error uploading carrier documents:', error);
    res.status(500).json({ error: 'Internal server error' });
  } finally {
    connection.release();
  }
});

// Delete a specific document for a carrier
router.delete('/:carrierId/:documentType', async (req, res) => {
  const connection = await db.getConnection();
  try {
    const { carrierId, documentType } = req.params;
    
    // Validate document type
    const validDocumentTypes = documentFields.map(field => field.name);
    if (!validDocumentTypes.includes(documentType)) {
      return res.status(400).json({ error: 'Invalid document type' });
    }
    
    // Get existing document record
    const [docRows] = await connection.query(
      'SELECT * FROM carrier_documents WHERE carrier_id = ?',
      [carrierId]
    );
    
    if (docRows.length === 0) {
      return res.status(404).json({ error: 'No documents found for this carrier' });
    }
    
    const existingDoc = docRows[0];
    
    // Check if document exists
    if (!existingDoc[documentType]) {
      return res.status(404).json({ error: 'Document not found' });
    }
    
    // Delete file
    const filePath = path.join(__dirname, '../../', existingDoc[documentType]);
    if (fs.existsSync(filePath)) {
      fs.unlinkSync(filePath);
    }
    
    // Update database
    const updateFields = { [documentType]: null };
    await connection.query(
      `UPDATE carrier_documents SET ? WHERE carrier_id = ?`,
      [updateFields, carrierId]
    );
    
    res.json({ success: true });
  } catch (error) {
    console.error('Error deleting carrier document:', error);
    res.status(500).json({ error: 'Internal server error' });
  } finally {
    connection.release();
  }
});

export default router;

These routes provide a complete API for managing carrier documents:

GET /:carrierId – Retrieve all documents for a carrier
POST /:carrierId – Upload new documents or update existing ones
DELETE /:carrierId/:documentType – Remove a specific document

The implementation handles file storage, validation, and database updates in a clean, organized way.

When This Approach Works Best

This fixed-schema design is ideal when:

Document types are stable: Your regulatory environment doesn’t frequently change
Simple versioning needs: Only the latest document version matters
Direct file references: You store file paths rather than complex metadata
Clear document set: All carriers need the same set of documents

The user interface maps directly to this table structure, making it intuitive for users to see which documents they’ve uploaded and which are still missing.

Front-End Implementation

The front-end implementation uses React to create an intuitive document management interface. Each document type has its own upload section with clear visual indicators for uploaded and missing documents:

// Document item renderer
const renderDocumentItem = (carrierId, docType, docPath) => {
  return (
    <div className="flex items-center justify-between p-2 hover:bg-gray-50">
      <div className="flex items-center gap-2">
        <Paperclip className="h-4 w-4 text-gray-400" />
        <span className="capitalize">{formatDocumentName(docType)}</span>
      </div>
      {docPath ? (
        <div className="flex items-center gap-2">
          <a
            href={`${process.env.NEXT_PUBLIC_SERVER_URL}${docPath}`}
            target="_blank"
            rel="noopener noreferrer"
            className="text-blue-500 hover:underline text-sm flex items-center"
          >
            <File className="h-4 w-4 mr-1" /> View
          </a>
          <Button
            variant="ghost"
            size="sm"
            onClick={() => handleDeleteDocument(carrierId, docType)}
            className="text-red-500"
          >
            <Trash2 className="h-4 w-4" />
          </Button>
        </div>
      ) : (
        <Button
          variant="outline"
          size="sm"
          onClick={() => handleFileInputClick(docType)}
        >
          <Upload className="h-4 w-4 mr-1" /> Upload
        </Button>
      )}
      <input
        type="file"
        ref={fileInputRefs[docType]}
        className="hidden"
        onChange={(e) => handleFileChange(e, carrierId, docType)}
        accept=".pdf,.doc,.docx,.xls,.xlsx,.jpg,.jpeg,.png"
      />
    </div>
  );
};

The UI implementation matched this structure perfectly, with a clean interface for users to upload and manage their documents:

Implementation Benefits

For small to medium logistics operations with stable document requirements, this approach offers:

Development speed: Quick to implement and understand
Operational simplicity: Easy for non-technical staff to grasp
Maintenance efficiency: Fewer moving parts means fewer bugs
Query performance: Simple database structure leads to faster queries
UI clarity: Direct mapping between database and interface

For our truckboard’s initial needs, this structure provided the right balance of functionality and simplicity. If your document management needs are straightforward and stable, consider this approach before implementing more complex systems.

Looking Ahead

While this approach worked well initially, I later discovered that more complex requirements would necessitate a different design. I’ll cover that evolution in a future article, discussing how we adapted when document types needed to be dynamic, versioning became important, and regulatory changes required more flexibility.