How I built Jayme da Costa’s customer portal: WordPress, SQL Server, SharePoint, and the engineering nobody sees

Real technical case study of a customer portal with complex integrations: WordPress as presentation layer, SQL Server as source of truth for equipment data, SharePoint for document management, Auth0 as SSO, and a nightly pre-computed cache that makes the portal load in milliseconds.

This article is different from the ones I usually publish. Instead of generic principles, it tells the technical story of a concrete system: the customer portal for Jayme da Costa (JdC), a Portuguese industrial company with over 80 years of operations in electrical equipment and energy.

The portal serves thousands of pieces of equipment spread across hundreds of corporate clients. When a customer logs in, they see only their own equipment, with all the relevant technical documentation (test certificates, manuals), and they can authorise additional users within their company. Documentation lives in SharePoint. Equipment data lives in a SQL Server data warehouse fed by JdC’s internal systems. Authentication is federated via Auth0. And all of this runs on WordPress.

I’m publishing this with the client’s express authorisation. I’ll show real architecture, real code, and the concrete problems I had to solve — because I think the technical decisions I made have value for other developers facing enterprise integrations.

Why WordPress (and not something “more serious”)

The first question any architect asks when they see a proposal like this is: why WordPress? It’s a fair question. JdC had a competent IT team and could have commissioned a dedicated .NET application from an enterprise agency.

The decision was made based on three realities:

Speed of delivery. A custom .NET application for this would have cost several times more and taken 6-12 months longer. WordPress lets me reach a functional version in weeks, not months.

Content management autonomy. JdC’s marketing team needs to be able to manage institutional pages, FAQs, and announcements without calling IT. WordPress gives this out of the box.

The real work isn’t the WordPress part. The system is, at its core, an aggregator of data from other systems. WordPress is the presentation and user management layer. The integrations with SQL Server and SharePoint, the Auth0 authentication, the multi-layered permission logic — that’s where the complexity lives. And that complexity is platform-independent.

The rule I apply: WordPress is suitable for read-heavy portals with editorial weight. It’s unsuitable for transactional systems with high write volume or ultra-low latency requirements. The JdC Portal falls clearly into the first group.

The architecture, in three layers

flowchart TB
    subgraph Browser["Browser"]
        UI["Vanilla JS<br/>+ HTML/CSS"]
    end

    subgraph WP["WordPress + JDC Portal Plugin (PHP 8.3, Apache)"]
        REST["REST API<br/>31 endpoints"]
        AUTH["Auth Layer<br/>(Auth0 + Local)"]
        CACHE[("Transients<br/>+ MySQL")]
        WARM["CacheWarmer<br/>(nightly cron)"]
    end

    subgraph External["External Systems"]
        AUTH0[("Auth0<br/>OAuth 2.0")]
        SQL[("SQL Server<br/>Data Warehouse")]
        SP[("SharePoint<br/>Microsoft Graph")]
        SMTP["SMTP"]
    end

    UI -->|"X-WP-Nonce"| REST
    REST --> AUTH
    REST --> CACHE
    AUTH -.->|"OAuth flow"| AUTH0
    REST -.->|"PDO + ODBC 18"| SQL
    REST -.->|"OAuth client_credentials<br/>+ token cache"| SP
    REST -.->|"PHPMailer"| SMTP
    WARM -->|"3am local"| SP
    WARM -->|"populate"| CACHE

WordPress is the orchestrator. It doesn’t store equipment data or documents — only:

• Local user identity (when the system creates accounts from Auth0)
• Auxiliary tables: wp_jdc_audit (logs), wp_jdc_tokens (invites and password resets)
• Transient cache of everything coming from external systems

External systems are the source of truth. When a JdC team adds equipment to the ERP, or a technician updates a test certificate in SharePoint, the change happens in those systems. The portal reflects — it doesn’t replace.

The 6 technical problems worth telling

1. Federated authentication with layered permission mapping

JDC didn’t want customers to manage another password. They wanted SSO via Auth0, with the option for customers to later integrate their own Identity Provider (IdP) — Microsoft Entra ID, Google Workspace, Okta — without changing a line of my code. Auth0 handles that abstraction.

The flow is the standard OAuth 2.0 Authorization Code, with PKCE and a state cookie for CSRF defence. The interesting part is what happens after the Auth0 callback:

// includes/Integrations/Auth0/CallbackHandler.php
private static function check_email_authorization(string $email): ?array {
    global $wpdb;

    // 1. Pending invite (master invited this email via portal)
    $invite = $wpdb->get_row($wpdb->prepare(
        "SELECT * FROM {$wpdb->prefix}jdc_tokens
         WHERE email = %s AND type = 'INVITE'
           AND used_at IS NULL AND expires_at > %s
         ORDER BY id DESC LIMIT 1",
        $email, gmdate('Y-m-d H:i:s')
    ), ARRAY_A);

    if ($invite) { /* ... auto-onboard as regular user ... */ }

    // 2. DW: contact authorised by the company in v_contacts
    $contact = SqlServer::one(
        "SELECT TOP 1 ClienteID, IsMaster
         FROM integration.v_contacts
         WHERE LOWER(ContactEmail) = LOWER(?)",
        [$email]
    );
    if ($contact) { /* ... auto-onboard with computed role ... */ }

    // 3. DW: company primary email in v_empresas
    $company = SqlServer::one(
        "SELECT TOP 1 CustomerNumber FROM integration.v_empresas
         WHERE LOWER(Email) = LOWER(?) AND ISNULL(IsBlocked,0) = 0",
        [$email]
    );
    if ($company) { /* ... auto-onboard as master ... */ }

    return null; // not authorised
}

There are three sources of authorisation, checked in order of priority. A user whose email is in none of the three is rejected even if they have a valid Auth0 account.

The next layer is continuous verification. Even a user with an existing portal account can have access revoked — by JDC (status revoked) or by their company’s master user (status inactive). Auth::can_user_access() checks this on every request:

public static function can_user_access(int $user_id): bool {
    if ($user_id <= 0) return false;
    $u = get_user_by('id', $user_id);
    if (!$u) return false;

    $roles = (array) $u->roles;
    $is_wp_admin = in_array('administrator', $roles, true);
    $is_jdc_admin = in_array('jdc_admin', $roles, true);

    // WP admins always pass — they need a way back in to fix things.
    // Don't mark your only sysadmin as jdc_admin-only.
    if ($is_wp_admin) return true;

    $status = (string) get_user_meta($user_id, 'jdc_access_status', true) ?: 'active';
    if ($status !== 'active') return false;

    if ($is_jdc_admin) return true; // jdc_admin has no expiry

    // Regular users expire after 1 year
    $expires = (string) get_user_meta($user_id, 'jdc_access_expires_at', true);
    if ($expires !== '') {
        $ts = strtotime($expires . ' UTC');
        if ($ts !== false && time() > $ts) return false;
    }
    return true;
}

The comment about “don’t mark your only sysadmin as jdc_admin-only” isn’t there for style. It’s there because this was a real problem during development, and if I hadn’t documented it, it would come back to bite me in 6 months when someone — probably me — would make a quick change and lock themselves out.

2. SQL Server query performance: the invisible problem

The v_equipamentos view has tens of thousands of rows. Each customer has between dozens and hundreds of pieces of equipment. Listing equipment for a customer is a simple query — but the table itself is already large, and it sits on a remote server separated from WordPress by VPN.

The first temptation is to cache everything. But that introduces inconsistencies: when JDC ships new equipment to a customer, that customer can’t wait 24 hours to see it on the portal. The business rule is that ERP data must appear on the portal within minutes.

The solution lies in query discipline:

// includes/Integrations/SqlServer/Client.php
public function listEquipmentsByCustomer(
    string $customerNumber,
    int $limit = 5000,
    array $filters = []
): array {
    $limit  = max(1, min(10000, $limit)); // defensive bound
    $where  = ["e.CustomerNumber = ?"];
    $params = [$customerNumber];

    if (!empty($filters['serial']))  { /* LIKE ? with parameter binding */ }
    if (!empty($filters['product'])) { /* LIKE across two columns */ }
    if (!empty($filters['family']))  { /* equality */ }

    $sql = "SELECT TOP {$limit} ...
            FROM integration.v_equipamentos e
            LEFT JOIN integration.v_familias f
              ON f.FamID COLLATE Latin1_General_100_CI_AS
                = e.FamID COLLATE Latin1_General_100_CI_AS
            WHERE " . implode(' AND ', $where) . "
            ORDER BY e.InvoiceDate DESC";
    // ...
}

Three things that matter more than they look:

COLLATE Latin1_General_100_CI_AS on the JOIN. When two SQL Server tables have columns with different collations (one case-sensitive, one case-insensitive), the JOIN can fail silently or fall back to NESTED LOOP instead of HASH JOIN. Forcing explicit collation fixes this.

TOP {$limit} interpolated rather than bound. Controversial. SQL Server doesn’t accept TOP ? in parameterised queries; it has to be a literal. The defence against injection is max(1, min(10000, $limit)) at the entry point, which guarantees $limit is always an integer between 1 and 10,000.

ORDER BY InvoiceDate DESC. Without this, pagination becomes non-deterministic. SQL Server doesn’t guarantee order without explicit ORDER BY, even if it appears consistent in tests.

3. SharePoint: from “60 seconds per dashboard” to “milliseconds”

This is the most interesting story in the project.

Technical documentation lives in two SharePoint libraries: Boletins de Ensaios (quality control — one test certificate per piece of equipment) and Manuais (technical manuals organised by product family). Each piece of equipment, when viewed in the portal, displays the relevant documentation.

Naive version: when a user opens an equipment page, I make 4-6 Microsoft Graph queries to find the relevant documents. Each query takes 200-800ms (network latency + Graph processing). Total: 2-5 seconds per equipment. Unacceptable.

Better version: per-equipment cache. If the same equipment is viewed twice, the second view doesn’t hit Graph. This works for repeat users, but the first access of the day is equally slow — and a master’s equipment list shows dozens, each needing to pre-fetch documentation.

Final version: the “bank”.

I built a pre-computed data structure containing all visible documentation, indexed in several ways. This “bank” is built once a night by a cron job and lives in a single WordPress transient. Each individual lookup becomes an O(1) in-memory operation.

flowchart LR
    subgraph Night["3am — Nightly Cache Warmer"]
        direction TB
        START["Start"]
        FLUSH["Flush volatile caches"]
        DOCS["Build docs-index<br/>(~5s)"]
        BANK["Build bank<br/>(~60-90s)"]
        ADM["Pre-warm admin lists"]
        END_["Audit log + status"]
        START --> FLUSH --> DOCS --> BANK --> ADM --> END_
    end

    subgraph Day["During the day"]
        REQ["Request: docs for equipment X"]
        CHECK{"Bank exists<br/>in transient?"}
        FAST["O(1) lookup<br/>~5ms"]
        SLOW["Live fallback<br/>~2-5s"]
        REQ --> CHECK
        CHECK -->|yes| FAST
        CHECK -->|no| SLOW
    end

    BANK -.->|"set_transient<br/>(2-day TTL)"| CHECK

The bank has this structure:

boletins_by_serial[NumSerie]    → [docs without file_url]
boletins_by_product[NumProduto] → [docs without file_url]
docs_by_folder_id[folder_id]    → [docs with .url follows resolved]
folder_ids_by_product[product]  → [folder_id, ...] (tier 1+2)
folder_ids_by_family[family_id] → [folder_id, ...] (tier 3)

The result: the first access to any piece of equipment, from any customer, on any day, is instantaneous. The cost is a 60-90 second nightly window where the server rebuilds everything.

There are nuances worth documenting:

Explicit memory limit during build. The bank can peak at ~320MB when all structures are in memory before being serialised:

@ini_set('memory_limit', '512M');
@set_time_limit(300);

Throttled progress writes. The CacheWarmer publishes progress to the admin UI via transient. But writing to wp_options 105 times during the build (once per SharePoint folder) is expensive. Solution: throttle to 0.5s between writes:

$last_push = 0.0;
$bank = $sp->bank_build(function(int $done, int $total, string $label) use ($start, &$last_push) {
    $now = microtime(true);
    if ($now - $last_push < 0.5 && $done < $total) return;
    $last_push = $now;
    self::set_progress(/* ... */);
});

Token caching with conservative TTL. Microsoft Graph OAuth tokens expire after 1 hour. I cache for 58 minutes (ttl - 120s) so it never comes close to the limit and have time to renew before failure. The cache lives in a transient to survive between requests:

self::$token_exp = time() + ((int)($body['expires_in'] ?? 3500) - 120);
set_transient('jdc_sp_token', self::$token, max(60, $ttl));

4. WordPress cron jobs: the pseudo-cron problem

The CacheWarmer schedules itself to run every night at 3am local time. But WordPress, by default, uses a “pseudo-cron” system that only fires when someone visits the site. This means if nobody visits the portal between 3am and 8am, the warmer doesn’t run — and the first user of the morning hits the cold version.

The solution is twofold. First, in production, I disable WordPress pseudo-cron in wp-config.php:

define('DISABLE_WP_CRON', true);

And in the server’s crontab:

*/5 * * * * curl -s https://suporte.jaymedacosta.pt/wp-cron.php?doing_wp_cron > /dev/null

Second problem: inside Docker, WordPress’s spawn_cron() tries to make an HTTP request to the site itself, using the public URL. But inside the container, that public URL isn’t reachable (localhost:8080 is the host from outside; inside, only the local Apache port 80 exists). The fix is a filter:

add_filter('cron_request', function(array $req) {
    $parsed = parse_url($req['url'] ?? '');
    $qs = $parsed['query'] ?? '';
    $req['url'] = 'http://127.0.0.1/wp-cron.php' . ($qs ? '?' . $qs : '');
    $req['args']['sslverify'] = false; // we're talking to ourselves
    return $req;
});

Small, but it solved three hours of staging debugging where cron was failing silently.

5. Rate limiting: defence in multiple layers

Sensitive endpoints (login, password reset, onboarding) need rate limiting. WordPress doesn’t provide this out of the box. The solution is simple but effective — a service that uses transients as a sliding counter:

// includes/Services/RateLimiter.php
public static function hit(string $key, int $limit, int $window_seconds): array {
    $now  = time();
    $data = get_transient($key);

    if (!is_array($data) || empty($data['reset_at']) || (int)$data['reset_at'] <= $now) {
        $data = ['count' => 0, 'reset_at' => $now + $window_seconds];
    }

    $data['count'] = (int)($data['count'] ?? 0) + 1;
    set_transient($key, $data, $window_seconds);

    return [
        'allowed'   => ((int)$data['count'] <= $limit),
        'remaining' => max(0, $limit - (int)$data['count']),
        'reset_in'  => max(0, (int)$data['reset_at'] - $now),
    ];
}

public static function ip(): string {
    // Order matters: Cloudflare > X-Forwarded-For > X-Real-IP > REMOTE_ADDR
    $keys = ['HTTP_CF_CONNECTING_IP', 'HTTP_X_FORWARDED_FOR', 'HTTP_X_REAL_IP', 'REMOTE_ADDR'];
    foreach ($keys as $k) {
        if (!empty($_SERVER[$k])) {
            $v = trim(explode(',', (string) $_SERVER[$k])[0]);
            if ($v !== '') return $v;
        }
    }
    return '0.0.0.0';
}

Each sensitive endpoint calls RateLimiter::hit() with a composite key (action + IP + email). If it exceeds the limit, it returns 429 with a Retry-After header. The detail in RateLimiter::ip() — reading CF-Connecting-IP before X-Forwarded-For — matters because the site sits behind Cloudflare; without this, all users would share the same Cloudflare edge node IP and rate limiting would block everyone when blocking one.

6. Multi-tenancy: the paranoia that paid off

The most serious possible error in this system would be a customer being able to see another customer’s data. It doesn’t matter how many optimisations I make if this happens once.

Defence is layered:

Layer 1: query-level. Every SQL query that returns equipment data has CustomerNumber = ? in the WHERE clause. No exceptions. No “almost exceptions”. This predicate is part of the query itself, not added by later code:

public function getEquipmentBySerialForCustomer(
    string $customerNumber,
    string $serial
): ?array {
    $sql = "SELECT TOP 1 ... WHERE e.CustomerNumber = ? AND e.SerialNumber = ?";
    // ...
}

There’s a separate admin version (getEquipmentBySerial) without the customer filter — and that’s only called by endpoints with a permission_callback that verifies Auth::is_admin().

Layer 2: cache-level. The SharePoint bank is shared across all users. But the bank doesn’t contain real file_urls — only doc_keys. The real file_url is built per equipment in proxy_url($file_base, $serial, $doc_key), and the endpoint that serves the file verifies the user has access to the requested serial before proxying to Graph.

Layer 3: audit log. Every document or equipment access generates a row in wp_jdc_audit. Even if something escapes layers 1 and 2, the log lets me detect and respond. After 6 months in production: zero incidents.

What I learned from this project

WordPress is a defensible choice for corporate portals when the design reflects it. The JDC Portal isn’t “WordPress with plugins”. It’s a custom application that uses WordPress as a hosting + auth + admin + content management layer. The bulk of the logic lives in PHP classes organised with namespaces, SOLID patterns, and zero dependency on third-party plugins for core functionality.

Pre-computed caches beat reactive caches for read-heavy systems. The jump from “first access slow, subsequent fast” to “all accesses fast” changed the perception of the product. Cost: 60-90 seconds per night and ~50MB of transient. Benefit: sub-100ms median latency across all views.

Defence in layers survives bugs. The combination of query-level filtering, cache scoping, and audit logging means that any bug in one layer is caught by the next. In multi-tenant systems, this isn’t paranoia — it’s the minimum professional standard.

Small details matter more than they seem. The comment about DISABLE_WP_CRON, the cron_request filter for Docker, the header order in RateLimiter::ip(), the explicit COLLATE in the JOIN — each of these details represents debugging hours that don’t recur because they were documented in the code where someone will find them when needed.

Final stack, summarised

Component	Choice	Reason
CMS / framework	WordPress 6.8+ + custom plugin	Delivery speed, content management, ecosystem
Backend language	PHP 8.3	Strict types, named arguments, performance
Hosting layer	Apache 2.4 + Docker	Standard stack, reproducible Dockerfile
Identity	Auth0 (OAuth 2.0)	Flexible federation, multi-IdP support
Source of truth (data)	SQL Server (DW) via PDO + ODBC 18	Where JDC already keeps the data
Source of truth (docs)	SharePoint via Microsoft Graph API	Where JDC already keeps the documents
Cache	WP Transients (MySQL) + PHP memory	Simplicity, no extra stack
Email	PHPMailer + corporate SMTP	WordPress standard
Frontend	Vanilla JS + custom CSS properties	No build step, no framework drift
i18n	Custom system (4 languages)	Full control over keys and fallbacks

Nothing exotic here. It’s all conservative, justified, sustainable-at-5-years choices.

If you have a similar project — a corporate system with heavy integrations but pragmatic requirements — and want to discuss architecture, contact me directly. It costs me 30 minutes to assess whether it’s worth pursuing, and you get an honest technical diagnosis regardless of whether we end up working together.