Symfony: Build an AI-Powered Website to Company Contact List from Spreadsheets

Mihajlo
Sep 10, 2026
Tutorials

A spreadsheet full of company websites looks like a useful lead list, but it is really a queue of unfinished research. Someone still has to visit each site, identify the company, find its public contact details, and turn inconsistent pages into rows that can be reviewed.

This tutorial builds that workflow as a Symfony command. It reads a CSV exported from a spreadsheet, calls the Website to Company data service for each website, maps the response into a small domain object, and writes an enrichment CSV containing company, contact, email, phone, and people data. Failed lookups remain visible as structured rows instead of disappearing into a log.

Prerequisites

You will need PHP 8.3 or newer, Composer, and an existing Symfony application. Create a small project and install the first-party HTTP and console components plus logging and testing support:

composer create-project symfony/skeleton contact-research
cd contact-research
composer require symfony/http-client symfony/console symfony/monolog-bundle
composer require --dev symfony/test-pack

The input will be ordinary CSV with a required website header. Exporting to CSV keeps this implementation independent of spreadsheet vendors and avoids adding an XLSX parser merely for transport.

website
https://example.com
https://www.example.org
company.example

Get access and copy the service token

Register through the registration page, or use the sign-in page if you already have an account.
Open the Website to Company data service page.
Choose the available Free, Plus, or Pro plan appropriate for your workload and complete its activation.
Open the official service documentation.
Find the Service token panel and copy the service-scoped token shown there.

This service requires a token. Regenerating the token revokes the previously active token, so token rotation must update every deployed application that uses it. Keep it in environment-backed configuration, never in PHP source, test fixtures, screenshots, or committed configuration.

Confirm the exact request

The API contract is an HTTP GET request to https://ai.mihajlo.mk/api/website-to-company-data/v1/extract. Authentication uses the token={serviceToken} query parameter, while the target site is supplied through the website query parameter.

Make one minimal request before writing integration code. Using an environment variable prevents the credential itself from being saved in the command text:

export WEBSITE_TO_COMPANY_TOKEN='YOUR_SERVICE_TOKEN'

curl --get \
  --data-urlencode "token=$WEBSITE_TO_COMPANY_TOKEN" \
  --data-urlencode "website=https://example.com" \
  https://ai.mihajlo.mk/api/website-to-company-data/v1/extract

Do not enable verbose HTTP output in shared logs because query strings can expose the token.

Store the credential in Symfony configuration

For local development, add the placeholder to .env.local. That file should remain uncommitted:

WEBSITE_TO_COMPANY_TOKEN=YOUR_SERVICE_TOKEN

Bind the environment value to the client’s constructor in config/services.yaml:

services:
    _defaults:
        autowire: true
        autoconfigure: true

    App\:
        resource: '../src/'

    App\ContactResearch\WebsiteToCompanyClient:
        arguments:
            $serviceToken: '%env(WEBSITE_TO_COMPANY_TOKEN)%'

Architecture: a durable batch, not a hidden loop

The project deliberately uses a console command instead of a web upload endpoint. Researching many sites can exceed a browser request deadline, while a command can run under a scheduler, CI job, or process supervisor. Symfony Messenger becomes worthwhile when rows must be distributed across workers, but it would add queue semantics without improving this modest sequential workflow.

Sequential requests also make quota behavior predictable. A fast concurrent fan-out can turn a recoverable rate limit into hundreds of simultaneous failures.

src/
  Command/BuildContactResearchCommand.php
  ContactResearch/ApiFailure.php
  ContactResearch/CompanyResearch.php
  ContactResearch/WebsiteToCompanyClient.php
tests/
  ContactResearch/WebsiteToCompanyClientTest.php
var/
  imports/companies.csv
  exports/contact-research.csv

The API boundary owns transport, retries, JSON decoding, and response validation. The command owns CSV concerns. Keeping those responsibilities separate makes both parts independently testable.

Map uncertain JSON into a stable domain object

The documented application fields are company, contact, email, phone, and people. Individual values may be scalar or structured, so the mapper accepts JSON-compatible values and serializes composite data rather than guessing an undocumented nested schema.

<?php
// src/ContactResearch/ApiFailure.php
namespace App\ContactResearch;

final class ApiFailure extends \RuntimeException
{
    public function __construct(
        public readonly string $kind,
        public readonly ?int $status = null,
        string $message = 'Website research failed.',
    ) {
        parent::__construct($message);
    }
}

// src/ContactResearch/CompanyResearch.php
namespace App\ContactResearch;

final readonly class CompanyResearch
{
    public function __construct(
        public ?string $company,
        public ?string $contact,
        public ?string $email,
        public ?string $phone,
        public ?string $people,
    ) {}

    public static function fromPayload(array $payload): self
    {
        $fields = ['company', 'contact', 'email', 'phone', 'people'];

        $hasKnownField = false;
        foreach ($fields as $field) {
            $hasKnownField = $hasKnownField || array_key_exists($field, $payload);
        }

        if (!$hasKnownField) {
            throw new ApiFailure(
                'invalid_response',
                null,
                'The response contained none of the expected fields.',
            );
        }

        return new self(
            self::normalize($payload['company'] ?? null),
            self::normalize($payload['contact'] ?? null),
            self::normalize($payload['email'] ?? null),
            self::normalize($payload['phone'] ?? null),
            self::normalize($payload['people'] ?? null),
        );
    }

    private static function normalize(mixed $value): ?string
    {
        if ($value === null) {
            return null;
        }

        if (is_string($value)) {
            $value = trim($value);

            return $value === '' ? null : $value;
        }

        if (is_int($value) || is_float($value) || is_bool($value)) {
            return (string) $value;
        }

        if (is_array($value)) {
            try {
                return json_encode(
                    $value,
                    JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES,
                );
            } catch (\JsonException) {
                throw new ApiFailure('invalid_response');
            }
        }

        throw new ApiFailure('invalid_response');
    }
}

A successful HTTP status is not enough: malformed JSON and payloads missing every expected field become explicit invalid_response failures.

Build a bounded, retry-aware HTTP client

The client uses Symfony’s HttpClientInterface, a 20-second inactivity timeout, and a 30-second total duration. It retries transport failures, HTTP 408, HTTP 429, and server errors up to three total attempts. Authentication and other client errors fail immediately.

<?php
// src/ContactResearch/WebsiteToCompanyClient.php
namespace App\ContactResearch;

use Psr\Log\LoggerInterface;
use Symfony\Contracts\HttpClient\Exception\TransportExceptionInterface;
use Symfony\Contracts\HttpClient\HttpClientInterface;

final class WebsiteToCompanyClient
{
    private const ENDPOINT =
        'https://ai.mihajlo.mk/api/website-to-company-data/v1/extract';

    private \Closure $sleep;

    public function __construct(
        private readonly HttpClientInterface $httpClient,
        private readonly LoggerInterface $logger,
        private readonly string $serviceToken,
        ?callable $sleep = null,
    ) {
        if ($serviceToken === '' || $serviceToken === 'YOUR_SERVICE_TOKEN') {
            throw new \InvalidArgumentException('A service token is required.');
        }

        $this->sleep = $sleep === null
            ? static function (int $microseconds): void {
                usleep($microseconds);
            }
            : \Closure::fromCallable($sleep);
    }

    public function research(string $website): CompanyResearch
    {
        $host = (string) parse_url($website, PHP_URL_HOST);

        for ($attempt = 1; $attempt <= 3; ++$attempt) {
            try {
                $response = $this->httpClient->request('GET', self::ENDPOINT, [
                    'query' => [
                        'token' => $this->serviceToken,
                        'website' => $website,
                    ],
                    'timeout' => 20.0,
                    'max_duration' => 30.0,
                ]);

                $status = $response->getStatusCode();
            } catch (TransportExceptionInterface $exception) {
                $this->logger->warning('Company research transport failure.', [
                    'host' => $host,
                    'attempt' => $attempt,
                    'exception_class' => $exception::class,
                ]);

                if ($attempt === 3) {
                    throw new ApiFailure(
                        'transport',
                        null,
                        'The research service could not be reached.',
                    );
                }

                $this->pause($attempt, null);
                continue;
            }

            if ($status >= 200 && $status < 300) {
                try {
                    $payload = json_decode(
                        $response->getContent(false),
                        true,
                        512,
                        JSON_THROW_ON_ERROR,
                    );
                } catch (\JsonException) {
                    throw new ApiFailure(
                        'invalid_response',
                        $status,
                        'The service returned invalid JSON.',
                    );
                }

                if (!is_array($payload)) {
                    throw new ApiFailure('invalid_response', $status);
                }

                return CompanyResearch::fromPayload($payload);
            }

            $retryable = $status === 408 || $status === 429 || $status >= 500;

            if ($retryable && $attempt < 3) {
                $headers = $response->getHeaders(false);

                $this->logger->warning('Company research request will retry.', [
                    'host' => $host,
                    'status' => $status,
                    'attempt' => $attempt,
                ]);

                $this->pause($attempt, $headers['retry-after'][0] ?? null);
                continue;
            }

            if ($status === 401 || $status === 403) {
                throw new ApiFailure(
                    'authentication',
                    $status,
                    'The service token was rejected.',
                );
            }

            if ($status === 429) {
                throw new ApiFailure(
                    'rate_limited',
                    $status,
                    'The service rate limit remained active.',
                );
            }

            if ($status >= 400 && $status < 500) {
                throw new ApiFailure(
                    'request_rejected',
                    $status,
                    'The website request was rejected.',
                );
            }

            throw new ApiFailure(
                'service_unavailable',
                $status,
                'The research service remained unavailable.',
            );
        }

        throw new ApiFailure('service_unavailable');
    }

    private function pause(int $attempt, ?string $retryAfter): void
    {
        if ($retryAfter !== null && ctype_digit($retryAfter)) {
            $seconds = min(5, max(0, (int) $retryAfter));
            ($this->sleep)($seconds * 1_000_000);

            return;
        }

        $microseconds = min(2_000_000, 250_000 * (2 ** ($attempt - 1)));
        ($this->sleep)($microseconds);
    }
}

The client never logs response bodies, query strings, or transport exception messages. Those can contain sensitive information, including the query-parameter token. Retry delays are bounded so a single row cannot hold the batch indefinitely.

Turn the spreadsheet into a reviewable list

The command normalizes bare domains to HTTPS, rejects malformed and obviously local targets, and writes one result row for every input row. It also guards against spreadsheet-formula injection by prefixing cells beginning with =, +, -, or @.

<?php
// src/Command/BuildContactResearchCommand.php
namespace App\Command;

use App\ContactResearch\ApiFailure;
use App\ContactResearch\WebsiteToCompanyClient;
use Symfony\Component\Console\Attribute\AsCommand;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

#[AsCommand(
    name: 'app:build-contact-research',
    description: 'Enrich a CSV of company websites.',
)]
final class BuildContactResearchCommand extends Command
{
    public function __construct(
        private readonly WebsiteToCompanyClient $client,
    ) {
        parent::__construct();
    }

    protected function configure(): void
    {
        $this
            ->addArgument('input', InputArgument::REQUIRED, 'Source CSV')
            ->addArgument('output', InputArgument::REQUIRED, 'Result CSV');
    }

    protected function execute(
        InputInterface $input,
        OutputInterface $output,
    ): int {
        $inputPath = (string) $input->getArgument('input');
        $outputPath = (string) $input->getArgument('output');

        $source = @fopen($inputPath, 'rb');
        if ($source === false) {
            throw new \RuntimeException('The input CSV cannot be opened.');
        }

        $directory = dirname($outputPath);
        $temporaryPath = tempnam($directory, '.contact-research-');
        if ($temporaryPath === false) {
            fclose($source);
            throw new \RuntimeException('A temporary output cannot be created.');
        }

        $target = @fopen($temporaryPath, 'wb');
        if ($target === false) {
            fclose($source);
            @unlink($temporaryPath);
            throw new \RuntimeException('The temporary output cannot be opened.');
        }

        $failures = 0;

        try {
            $header = fgetcsv($source, 0, ',', '"', '\\');
            if (!is_array($header)) {
                throw new \RuntimeException('The input CSV is empty.');
            }

            $header = array_map(
                static fn (string $value): string =>
                    strtolower(trim($value, "\xEF\xBB\xBF \t\n\r\0\x0B")),
                $header,
            );

            $websiteColumn = array_search('website', $header, true);
            if ($websiteColumn === false) {
                throw new \RuntimeException(
                    'The input CSV needs a website column.',
                );
            }

            fputcsv($target, [
                'source_website', 'status', 'company', 'contact',
                'email', 'phone', 'people', 'error',
            ]);

            while (($row = fgetcsv($source, 0, ',', '"', '\\')) !== false) {
                $original = trim((string) ($row[$websiteColumn] ?? ''));

                try {
                    $website = $this->normalizeWebsite($original);
                    $result = $this->client->research($website);

                    $values = [
                        $website, 'ready', $result->company ?? '',
                        $result->contact ?? '', $result->email ?? '',
                        $result->phone ?? '', $result->people ?? '', '',
                    ];
                } catch (\InvalidArgumentException $exception) {
                    ++$failures;
                    $values = [
                        $original, 'invalid_input', '', '', '', '', '',
                        $exception->getMessage(),
                    ];
                } catch (ApiFailure $exception) {
                    ++$failures;
                    $values = [
                        $original, $exception->kind, '', '', '', '', '',
                        $exception->getMessage(),
                    ];
                }

                fputcsv($target, array_map($this->csvCell(...), $values));
            }
        } catch (\Throwable $exception) {
            fclose($source);
            fclose($target);
            @unlink($temporaryPath);
            throw $exception;
        }

        fclose($source);
        fclose($target);

        if (!@rename($temporaryPath, $outputPath)) {
            @unlink($temporaryPath);
            throw new \RuntimeException('The final output cannot be replaced.');
        }

        $output->writeln(sprintf(
            'Contact research written with %d failed row(s).',
            $failures,
        ));

        return $failures === 0 ? Command::SUCCESS : Command::FAILURE;
    }

    private function normalizeWebsite(string $website): string
    {
        if ($website === '') {
            throw new \InvalidArgumentException('Website is empty.');
        }

        if (!str_contains($website, '://')) {
            $website = 'https://' . $website;
        }

        if (filter_var($website, FILTER_VALIDATE_URL) === false) {
            throw new \InvalidArgumentException('Website is not a valid URL.');
        }

        $parts = parse_url($website);
        $scheme = strtolower((string) ($parts['scheme'] ?? ''));
        $host = strtolower((string) ($parts['host'] ?? ''));

        if (!in_array($scheme, ['http', 'https'], true) || $host === '') {
            throw new \InvalidArgumentException('Only HTTP websites are accepted.');
        }

        if (isset($parts['user']) || isset($parts['pass']) || $host === 'localhost') {
            throw new \InvalidArgumentException('Local or credentialed URLs are rejected.');
        }

        if (filter_var($host, FILTER_VALIDATE_IP) !== false
            && filter_var(
                $host,
                FILTER_VALIDATE_IP,
                FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE,
            ) === false
        ) {
            throw new \InvalidArgumentException('Private IP addresses are rejected.');
        }

        return $website;
    }

    private function csvCell(string $value): string
    {
        return preg_match('/^[=+\-@]/u', $value) === 1
            ? "'" . $value
            : $value;
    }
}

The temporary file and final rename prevent readers from seeing a partially written export. A nonzero exit code reports incomplete enrichment to automation, while the CSV still preserves successful rows and actionable failure categories.

Test the API boundary without making network calls

MockHttpClient provides a deterministic fake transport. The first test verifies query construction, mapping, and rate-limit retry. The second confirms that authentication failures are not retried.

<?php
// tests/ContactResearch/WebsiteToCompanyClientTest.php
namespace App\Tests\ContactResearch;

use App\ContactResearch\ApiFailure;
use App\ContactResearch\WebsiteToCompanyClient;
use PHPUnit\Framework\TestCase;
use Psr\Log\NullLogger;
use Symfony\Component\HttpClient\MockHttpClient;
use Symfony\Component\HttpClient\Response\MockResponse;

final class WebsiteToCompanyClientTest extends TestCase
{
    public function testItRetriesRateLimitAndMapsResponse(): void
    {
        $http = new MockHttpClient([
            new MockResponse('', [
                'http_code' => 429,
                'response_headers' => ['retry-after: 1'],
            ]),
            new MockResponse(json_encode([
                'company' => 'Example Ltd',
                'contact' => 'Sales',
                'email' => '[email protected]',
                'phone' => '+1 555 0100',
                'people' => [['name' => 'Alex']],
            ], JSON_THROW_ON_ERROR)),
        ]);

        $client = new WebsiteToCompanyClient(
            $http,
            new NullLogger(),
            'test-service-token',
            static function (int $microseconds): void {},
        );

        $result = $client->research('https://example.com');

        self::assertSame('Example Ltd', $result->company);
        self::assertSame('[{"name":"Alex"}]', $result->people);
        self::assertSame(2, $http->getRequestsCount());
    }

    public function testItDoesNotRetryAuthenticationFailure(): void
    {
        $http = new MockHttpClient(
            new MockResponse('', ['http_code' => 401]),
        );

        $client = new WebsiteToCompanyClient(
            $http,
            new NullLogger(),
            'rejected-token',
        );

        try {
            $client->research('https://example.com');
            self::fail('An ApiFailure was expected.');
        } catch (ApiFailure $exception) {
            self::assertSame('authentication', $exception->kind);
            self::assertSame(1, $http->getRequestsCount());
        }
    }
}

php bin/phpunit
mkdir -p var/imports var/exports
php bin/console app:build-contact-research \
  var/imports/companies.csv \
  var/exports/contact-research.csv

Security, observability, and deployment

Inject WEBSITE_TO_COMPANY_TOKEN from the deployment platform’s secret manager rather than shipping .env.local. During rotation, update the secret and restart the consuming process immediately because regeneration revokes the previous active token.

Restrict input file ownership and output access: contact details may be public at their source but still deserve controlled handling when aggregated. Apply an appropriate retention policy and ensure your intended enrichment workflow complies with applicable privacy, outreach, and data-use obligations.

Monitor counts by final row status, command duration, HTTP status, and retry count. The sample logs hostnames rather than full URLs and never records tokens or bodies. Alert separately on authentication, because repeating the batch cannot repair a revoked credential, and on sustained rate_limited or service_unavailable results.

Run one batch at a time unless the activated plan and operational limits justify more concurrency. Schedule the command under a process supervisor with a runtime limit longer than the batch’s worst-case bounded retries. Deploy application code and configuration first, inject the token, clear or warm Symfony’s production cache as your deployment normally requires, and then run a small canary CSV before processing the full file.

Common failures and final verification

Every row says authentication: verify the service-scoped token, plan activation, and deployment secret. If the token was regenerated, replace the revoked value.
Rows remain rate limited: reduce batch frequency or workload. The command already honors numeric Retry-After values within a five-second bound.
The response is invalid: inspect status and correlation information available through controlled diagnostics, but do not copy credentials or raw personal data into shared logs.
The output cannot be written: create the destination directory and grant the command’s operating user write permission.
A website is rejected locally: use an HTTP or HTTPS public URL without embedded credentials; encode internationalized domains in their ASCII form when necessary.

Before calling the workflow complete, confirm that:

The token exists only in environment-backed secret configuration.
The minimal GET request succeeds with both required query parameters.
Automated tests pass without reaching the real service.
A canary CSV produces company, contact, email, phone, and people columns.
Invalid sites and API failures remain visible as reviewable rows.
Logs contain useful status context but no token, query string, or response body.
The process returns failure when any row needs attention.

The important result is not merely an enriched spreadsheet. It is a controlled research pipeline: inputs are validated, the external contract has one boundary, transient failures receive bounded retries, permanent failures remain explicit, and every exported row is safe to open for human review. That is what turns a convenient API call into a production feature a small team can trust.