DEV Community

Software Solutions
Software Solutions

Posted on

Building an AI-Powered Invoice Processing System with PHP and MySQL

Automating Invoice Data Extraction, Validation and Storage with AI

Manual invoice processing remains one of the biggest operational bottlenecks for accounting teams, wholesalers, and ERP-driven businesses. Every incoming vendor bill—whether a scanned PDF, a mobile photo of a thermal receipt, or a digital GST invoice—typically requires an operator to open the document, manually type line items into an ERP form, verify tax calculations, and check for duplicate invoice numbers.

At scale, manual data entry is slow, expensive, and prone to human typos. Traditional template-based Optical Character Recognition (OCR) tools often break the moment a supplier changes their invoice layout or shifts a table column by a few pixels.

By combining PHP 8+, CodeIgniter 4, MySQL, and modern multimodal AI APIs with strict JSON-based structured output, developers can build resilient AI document processing pipelines that extract, validate, and store invoice records automatically.


1. Understanding AI-Powered Document Processing

Legacy OCR engines simply convert pixels into raw text strings, forcing developers to write fragile regular expressions (preg_match) to guess which number represents the invoice total versus the purchase order number.

Modern multimodal AI models take a fundamentally different approach:

  • Layout & Visual Context Awareness: The model processes the document image or PDF directly, understanding table rows, header blocks, vendor stamps, and multi-column line items simultaneously.
  • Semantic Field Mapping: Instead of relying on fixed X/Y coordinates, the AI understands semantic concepts such as Vendor GSTIN, Taxable Value, CGST/SGST/IGST splits, and Due Date regardless of vendor layout.
  • Schema-Enforced JSON Output: Using structured output schemas, the API guarantees that the extracted data returns in a predictable, strongly typed JSON structure ready for backend validation.

2. Designing the Application Architecture

Because external AI API calls take anywhere from 2 to 8 seconds per document, processing invoices synchronously during an HTTP upload request leads to browser timeouts and poor user experience. A production-ready PHP MySQL application decouples file ingestion from AI extraction using background workers.

Invoice Upload (PDF / JPG / PNG)
              |
              v
PHP 8+ / CodeIgniter 4 Controller
              |
              v
Secure Document Storage (Outside public/)
              |
              v
Background Queue Worker (CLI / Cron)
              |
              v
AI Document Extraction API
              |
              v
Structured JSON Response
              |
              v
PHP Data Validation & Math Audit
              |
+---> [Discrepancy / Low Confidence] ---> Human Review Queue
              |
              v [Passed]
MySQL Relational Database (Invoices + Line Items)
              |
              v
Invoice Management Dashboard
Enter fullscreen mode Exit fullscreen mode

Database Schema Design (MySQL)

To support multi-item invoices, audit trails, and human review workflows, structure your MySQL schema into three core tables:

CREATE TABLE invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vendor_name VARCHAR(255) NOT NULL,
    vendor_tax_id VARCHAR(50) NULL,
    invoice_number VARCHAR(100) NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE NULL,
    currency VARCHAR(10) DEFAULT 'INR',
    subtotal DECIMAL(12, 2) NOT NULL,
    tax_amount DECIMAL(12, 2) NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_hash CHAR(64) NOT NULL,
    raw_ai_json JSON NULL,
    confidence_score DECIMAL(4, 2) DEFAULT 0.00,
    status ENUM('queued', 'processing', 'needs_review', 'approved', 'failed') DEFAULT 'queued',
    review_notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_vendor_invoice (vendor_tax_id, invoice_number),
    INDEX idx_file_hash (file_hash),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE invoice_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id BIGINT UNSIGNED NOT NULL,
    description VARCHAR(500) NOT NULL,
    hsn_sac_code VARCHAR(30) NULL,
    quantity DECIMAL(10, 3) NOT NULL DEFAULT 1.000,
    unit_price DECIMAL(12, 2) NOT NULL,
    tax_rate DECIMAL(5, 2) DEFAULT 0.00,
    line_total DECIMAL(12, 2) NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Enter fullscreen mode Exit fullscreen mode
  1. Uploading Invoice Documents Securely

Never store financial documents inside your web server's public public/uploads/ directory where direct URLs could expose sensitive vendor pricing or tax records. In CodeIgniter 4, store files inside WRITEPATH . 'uploads/invoices/' and serve them only through authenticated controller streams.

<?php

namespace App\Controllers;

use App\Models\InvoiceModel;
use CodeIgniter\HTTP\ResponseInterface;

class InvoiceUploadController extends BaseController
{
    public function upload(): ResponseInterface
    {
        $validationRules = [
            'invoice_file' => [
                'label' => 'Invoice Document',
                'rules' => 'uploaded[invoice_file]|max_size[invoice_file,10240]|mime_in[invoice_file,application/pdf,image/jpeg,image/png]',
            ],
        ];

        if (!$this->validate($validationRules)) {
            return $this->response->setStatusCode(422)->setJSON([
                'status' => 'error',
                'errors' => $this->validator->getErrors(),
            ]);
        }

        $file =$this->request->getFile('invoice_file');
        $fileHash = hash_file('sha256',$file->getTempName());

        $invoiceModel = new InvoiceModel();

        // Check exact file hash duplicate before storing
        if ($existing = $invoiceModel->where('file_hash',$fileHash)->first()) {
            return $this->response->setStatusCode(409)->setJSON([
                'status'     => 'duplicate',
                'message'    => 'This exact document has already been uploaded.',
                'invoice_id' => $existing['id'],
            ]);
        }

        $newName = $file->getRandomName();$targetDir = WRITEPATH . 'uploads/invoices/' . date('Y/m');
        $file->move($targetDir,$newName);

        $invoiceId =$invoiceModel->insert([
            'vendor_name'    => 'Pending Extraction',
            'invoice_number' => 'PENDING-' . uniqid(),
            'invoice_date'   => date('Y-m-d'),
            'subtotal'       => 0.00,
            'tax_amount'     => 0.00,
            'total_amount'   => 0.00,
            'file_path'      => 'uploads/invoices/' . date('Y/m') . '/' . $newName,
            'file_hash'      => $fileHash,
            'status'         => 'queued',
        ]);

        return $this->response->setStatusCode(201)->setJSON([
            'status'     => 'queued',
            'invoice_id' => $invoiceId,
            'message'    => 'Invoice uploaded and queued for AI extraction.',
        ]);
    }
}
Enter fullscreen mode Exit fullscreen mode

4. Extracting Invoice Information & Structured JSON

To make PHP AI integration reliable, enforce a strict JSON schema in your API request. Instead of asking the model to "extract the invoice details," define exact property names, data types, and a self-reported confidence score.
Service Layer Implementation

<?php

namespace App\Services;

use RuntimeException;

class AiInvoiceExtractor
{
    private string $apiKey;
    private string $endpoint = '[https://api.openai.com/v1/chat/completions](https://api.openai.com/v1/chat/completions)';

    public function __construct()
    {
        $this->apiKey = env('AI_API_KEY', '');
    }

    public function extractFromFile(string $absoluteFilePath): array
    {
        if (!file_exists($absoluteFilePath)) {
            throw new RuntimeException("Invoice file not found: {$absoluteFilePath}");
        }

        $mimeType = mime_content_type($absoluteFilePath);$base64Data = base64_encode(file_get_contents($absoluteFilePath));$dataUri = "data:{$mimeType};base64,{$base64Data}";

        $schema = [
            'type' => 'object',
            'properties' => [
                'vendor_name'      => ['type' => 'string'],
                'vendor_tax_id'    => ['type' => 'string'],
                'invoice_number'   => ['type' => 'string'],
                'invoice_date'     => ['type' => 'string', 'description' => 'ISO format YYYY-MM-DD'],
                'due_date'         => ['type' => ['string', 'null']],
                'currency'         => ['type' => 'string'],
                'subtotal'         => ['type' => 'number'],
                'tax_amount'       => ['type' => 'number'],
                'total_amount'     => ['type' => 'number'],
                'confidence_score' => ['type' => 'number', 'description' => '0.0 to 1.0 extraction certainty'],
                'line_items'       => [
                    'type' => 'array',
                    'items' => [
                        'type' => 'object',
                        'properties' => [
                            'description'  => ['type' => 'string'],
                            'hsn_sac_code' => ['type' => ['string', 'null']],
                            'quantity'     => ['type' => 'number'],
                            'unit_price'   => ['type' => 'number'],
                            'tax_rate'     => ['type' => 'number'],
                            'line_total'   => ['type' => 'number'],
                        ],
                        'required' => ['description', 'quantity', 'unit_price', 'line_total'],
                        'additionalProperties' => false,
                    ],
                ],
            ],
            'required' => [
                'vendor_name', 'invoice_number', 'invoice_date',
                'subtotal', 'tax_amount', 'total_amount',
                'confidence_score', 'line_items'
            ],
            'additionalProperties' => false,
        ];

        $payload = [
            'model' => 'gpt-4o-mini',
            'temperature' => 0.0,
            'messages' => [
                [
                    'role' => 'system',
                    'content' => 'You are an expert financial data extraction engine. Extract all invoice fields accurately. Convert all dates to YYYY-MM-DD format. If a field is blurry or ambiguous, lower the confidence_score accordingly.'
                ],
                [
                    'role' => 'user',
                    'content' => [
                        ['type' => 'text', 'text' => 'Extract structured invoice data from this document.'],
                        ['type' => 'image_url', 'image_url' => ['url' => $dataUri, 'detail' => 'high']],
                    ],
                ],
            ],
            'response_format' => [
                'type' => 'json_schema',
                'json_schema' => [
                    'name'   => 'invoice_extraction',
                    'strict' => true,
                    'schema' => $schema,
                ],
            ],
        ];

        return $this->sendRequest($payload);
    }

    private function sendRequest(array $payload): array
    {
        $ch = curl_init($this->endpoint);
        curl_setopt_array($ch, [
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_POST           => true,
            CURLOPT_TIMEOUT        => 45,
            CURLOPT_HTTPHEADER     => [
                'Content-Type: application/json',
                'Authorization: Bearer ' . $this->apiKey,
            ],
            CURLOPT_POSTFIELDS     => json_encode($payload),
        ]);

        $response = curl_exec($ch);
        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
        $curlErr  = curl_error($ch);
        curl_close($ch);

        if ($curlErr \vert{}\vert{}$httpCode !== 200) {
            throw new RuntimeException("AI API Error (HTTP {$httpCode}): {$curlErr} {$response}");
        }

        $decoded = json_decode($response, true);
        $content =$decoded['choices'][0]['message']['content'] ?? '';

        $extracted = json_decode($content, true);
        if (json_last_error() !== JSON_ERROR_NONE) {
            throw new RuntimeException('Failed to decode structured JSON from AI response.');
        }

        return $extracted;
    }
}
Enter fullscreen mode Exit fullscreen mode

5. Validating Extracted Numbers, Dates, and Tax Math

Never trust AI-extracted financial numbers blindly. Even the best models can occasionally misread a smudged 8 as a 3 on a low-resolution scan. Deterministic PHP validation acts as your financial firewall.

Deterministic Audit Rules

  • Line-Item Summation Check: Verify that the sum of all line_total values equals subtotal within a minor rounding tolerance (0.05).
  • Grand Total Formula Check: Verify that subtotal + tax_amount == total_amount.
  • Date Sanity Check: Ensure invoice_date is a valid calendar date (DateTime::createFromFormat('Y-m-d', ...)) and is not in the future.
  • Confidence Threshold: Flag any document where confidence_score < 0.85 for human verification.
<?php

namespace App\Services;

use DateTime;

class InvoiceValidator
{
    public function validate(array $data): array
    {
        $issues = [];

        // 1. Validate required identifiers
        if (empty(trim($data['invoice_number'] ?? ''))) {$issues[] = 'Missing invoice number.';
        }

        // 2. Validate date format and logic
        $invoiceDate = DateTime::createFromFormat('Y-m-d',$data['invoice_date'] ?? '');
        if (!$invoiceDate \vert{}\vert{}$invoiceDate->format('Y-m-d') !== $data['invoice_date']) {$issues[] = 'Invalid invoice date format.';
        } elseif ($invoiceDate > new DateTime('tomorrow')) {$issues[] = 'Invoice date cannot be in the future.';
        }

        // 3. Audit line item math
        $calculatedSubtotal = 0.0;
        foreach ($data['line_items'] ?? [] as$index => $item) {$expectedLine = round($item['quantity'] *$item['unit_price'], 2);
            if (abs($expectedLine - $item['line_total']) > 0.10) {$issues[] = "Line item #" . ($index + 1) . " math mismatch ({$item['quantity']} x {$item['unit_price']} != {$item['line_total']}).";
            }
            $calculatedSubtotal += (float)$item['line_total'];
        }

        if (!empty($data['line_items']) && abs($calculatedSubtotal - $data['subtotal']) > 0.50) {$issues[] = "Sum of line items ({$calculatedSubtotal}) does not match subtotal ({$data['subtotal']}).";
        }

        // 4. Audit grand total math
        $expectedTotal = round((float) $data['subtotal'] + (float)$data['tax_amount'], 2);
        if (abs($expectedTotal - (float) $data['total_amount']) > 0.50) {$issues[] = "Subtotal + Tax ({$expectedTotal}) does not match Grand Total ({$data['total_amount']}).";
        }

        // 5. Check AI confidence score
        if (($data['confidence_score'] ?? 0) < 0.85) {
            $issues[] = 'Low AI extraction confidence score (' . ($data['confidence_score'] ?? 0) . ').';
        }

        return [
            'is_valid' => empty($issues),
            'issues'   => $issues,
        ];
    }
}
Enter fullscreen mode Exit fullscreen mode

6. Storing Validated Records & Human-in-the-Loop Review

When a background CLI worker processes queued invoices, it wraps database writes inside a MySQL transaction ($db->transStart()) so partial line items are never committed if an error occurs.

<?php

namespace App\Commands;

use CodeIgniter\CLI\BaseCommand;
use CodeIgniter\CLI\CLI;
use App\Models\InvoiceModel;
use App\Services\AiInvoiceExtractor;
use App\Services\InvoiceValidator;
use Throwable;

class ProcessInvoices extends BaseCommand
{
    protected $group       = 'Invoices';
    protected $name        = 'invoices:process';
    protected $description = 'Processes queued invoices using AI extraction and validation.';

    public function run(array $params)
    {
        $invoiceModel = new InvoiceModel();$extractor    = new AiInvoiceExtractor();
        $validator    = new InvoiceValidator();$db           = \Config\Database::connect();

        $queuedInvoices =$invoiceModel->where('status', 'queued')->findAll(10);

        foreach ($queuedInvoices as$invoice) {
            $invoiceModel->update($invoice['id'], ['status' => 'processing']);

            try {
                $fullPath  = WRITEPATH .$invoice['file_path'];
                $extracted =$extractor->extractFromFile($fullPath);$audit     = $validator->validate($extracted);

                $finalStatus =$audit['is_valid'] ? 'approved' : 'needs_review';

                $db->transStart();

                $invoiceModel->update($invoice['id'], [
                    'vendor_name'      => $extracted['vendor_name'],
                    'vendor_tax_id'    => $extracted['vendor_tax_id'] ?: null,
                    'invoice_number'   => $extracted['invoice_number'],
                    'invoice_date'     => $extracted['invoice_date'],
                    'due_date'         => $extracted['due_date'] ?: null,
                    'currency'         => $extracted['currency'] ?: 'INR',
                    'subtotal'         => $extracted['subtotal'],
                    'tax_amount'       => $extracted['tax_amount'],
                    'total_amount'     => $extracted['total_amount'],
                    'raw_ai_json'      => json_encode($extracted),
                    'confidence_score' => $extracted['confidence_score'],
                    'status'           => $finalStatus,
                    'review_notes'     => empty($audit['issues']) ? null : implode("\n", $audit['issues']),
                ]);

                $itemBuilder =$db->table('invoice_items');
                foreach ($extracted['line_items'] as $item) {$itemBuilder->insert([
                        'invoice_id'   => $invoice['id'],
                        'description'  => $item['description'],
                        'hsn_sac_code' => $item['hsn_sac_code'] ?? null,
                        'quantity'     => $item['quantity'],
                        'unit_price'   => $item['unit_price'],
                        'tax_rate'     => $item['tax_rate'] ?? 0.00, 'line_total'   =>$item['line_total'],
                    ]);
                }

                $db->transComplete();
                CLI::write("Invoice #{$invoice['id']} processed -> {$finalStatus}", 'green');

            } catch (Throwable $e) {
                $invoiceModel->update($invoice['id'], [
                    'status'       => 'failed',
                    'review_notes' => $e->getMessage(),
                ]);
                CLI::error("Invoice #{$invoice['id']} failed: " . $e->getMessage());
            }
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Designing the Human Review Interface

Invoices marked needs_review appear in an accounting queue featuring a split-screen view:

  • Left Pane: Embedded PDF viewer or zoomable high-res image of the original invoice served via a secure PHP file-streaming endpoint.
  • Right Pane: Pre-filled HTML form displaying the AI-extracted fields alongside highlighted warning banners (e.g., "Subtotal + Tax does not match Grand Total"). The accountant corrects the single flagged field and clicks Approve, updating status to approved in seconds rather than typing 25 fields from scratch.
    
  • 7. Handling API Errors, Retries, and Duplicate Invoices

Production document pipelines must gracefully survive network hiccups and messy real-world accounting habits:

  • Exponential Backoff for Rate Limits:
    If the AI provider returns HTTP 429 (Rate Limit) or 503 (Service Unavailable), increment a retry_count column and delay the next attempt (30s, 2m, 10m) instead of immediately marking the document as permanently failed.

  • Two-Layer Duplicate Detection:

  • File-Level Deduplication: Computing hash_file('sha256', ...) catches identical files uploaded twice before spending any AI API tokens.

  •     Logical Business Deduplication: A composite unique index on (vendor_tax_id, invoice_number) prevents the same supplier bill from being entered twice—even if one copy was a clean digital PDF and the second copy was a smartphone photo of a printed sheet.
    

8. Security Considerations and Production Deployment

Financial documents contain sensitive vendor banking details, tax registration numbers, and unit pricing. Harden your deployment before going live:

  • Private Storage & Streamed Access: Keep WRITEPATH completely outside the web root. Authenticate user session permissions before streaming PDFs with Content-Disposition: inline.
  • API Key Isolation: Store AI API keys in .env variables with strict 0600 file permissions, never hardcoded in repositories.
  • PII Redaction & Retention Policies: Configure zero-data-retention agreements on your AI API enterprise endpoints so vendor invoices are not used for model training.
  • Supervisor Process Management: Run php spark invoices:process via Supervisor or a locked cron job (flock) to prevent overlapping worker processes from picking up the same queued row simultaneously.

Conclusion

Combining PHP 8+, CodeIgniter 4, and MySQL with structured AI extraction transforms accounts payable from a manual data-entry chore into an automated, auditable workflow. By pairing schema-enforced JSON responses with deterministic PHP math checks and a targeted human review queue, businesses achieve both high automation speed and strict financial accuracy.

Once this core architecture is in place, the exact same pipeline can be extended to automate purchase orders, delivery challans, customer KYC documents, and logistics bills of lading across your ERP system.

Have you integrated structured AI extraction into your PHP or Laravel/CodeIgniter backends? Share your approach or questions in the comments!

Top comments (0)