How certification rollups are built and what HQ can and can't see

Why this article exists

The Certifications page (see /help/hq-certifications) puts a number in front of network leadership for every membership: how many certifications are active, how many have expired, how many are expiring, how CEC hours stack up against requirements. The moment a page like that exists, the fair question is: how much of a franchisee's business is actually flowing up to HQ to make it possible? This article answers that question precisely, table by table, column by column. It is the reference for exactly what HQ's Certifications page is built from, where the boundary sits, and why the boundary is structural rather than a policy promise.

The short version

HQ never opens an individual certificate. There is no certificate number, no training vendor, no course cost, and no team member name anywhere on the Certifications page or in the data it reads. What HQ sees is a nightly rollup of counts, computed once per membership: how many certifications total, how many active, how many expired, how many expiring soon, and where CEC hours stand against what is required. That rollup is a different database table from where the certificate detail lives, and the membership's display name on it is encrypted at rest. Verinode surfaces the aggregate; the franchisee's own account holds the record.

The data model: two tables, two schemas

Verinode's database is split by schema, and the split is the mechanism behind the privacy boundary, not a naming convention.

your operator data lives in the PII database, the same database that holds a membership's operator-side business data. This is where the actual certification records sit: which team member holds which credential, its cert type, its issue and expiration dates, its CEC hours earned and required, and any other detail the membership or their team entered or extracted from a document. This table is scoped entirely to the operator's own account. HQ has no query path into your operator data, for this membership or any other. It never appears in any HQ code path, any HQ API response, or any HQ admin session.

the network data lives in the core database, in a schema HQ's application layer can read. This is the aggregate: one row per membership per network (technically, one row per (group_id, operator_id) pair, enforced by a unique constraint so a membership can never appear twice in the same network's rollup). It holds only:

| Column | What it is | |---|---| | cert_count_total | Every certification on file for that membership, any status | | active_count | Certifications currently marked active | | expired_count | Certifications past their expiration date | | expiring_30d / expiring_90d | Active certifications expiring inside the next 30 or 90 days | | distinct_cert_types | How many different kinds of certification that membership's team holds | | cec_earned_total / cec_required_total | Continuing-education hours earned and required, summed | | cec_completion_pct | Earned divided by required, as a percent (null when nothing is required) | | last_cert_issued_at | The date of that membership's most recently issued certification | | franchisee_name / franchisee_name_ciphertext | The membership's display name, plaintext and encrypted (see below) | | computed_at | When this row was last recomputed |

There is no cert type, no cert number, no team member identity, and no document reference anywhere on this row. A count is the only shape of information that crosses from the private layer into the network layer for certifications.

Who computes the rollup, and when

the network data is not written by HQ, and it is not written on demand when someone opens the Certifications page. It is computed by a scheduled process, the aggregate-refresh cron, which runs on its own schedule, reads each membership's your operator data rows for the memberships in a given network, sums them into the columns above, and writes (or updates) that membership's single row. The Certifications page then reads already-computed rows; it never triggers a live read into a membership's PII data.

This matters for two reasons. First, it means the numbers on the Certifications page are as fresh as the last refresh cycle, not real-time to the second, if a team member's certification changed an hour ago in their own account, the network number catches up on the next cycle. Second, it means there is no code path where opening the HQ Certifications page directly touches a membership's PII database. The only thing HQ's application ever reads for this page is the pre-computed summary row.

Encrypted franchisee names

Every column on the network data other than the membership's display name is a count, a percentage, or a date, values with no meaningful privacy exposure on their own. The one column that is a person-identifiable label, franchisee_name, gets an extra layer: it is also stored encrypted, in a sibling column called franchisee_name_ciphertext.

Here is how that works in practice:

1. 1When the aggregate-refresh cron computes a membership's row, it writes the display name twice: once as plaintext in franchisee_name, and once as AES-256-GCM ciphertext in franchisee_name_ciphertext, encrypted under the network's shared data-encryption key (a key wrapped and held by Verinode's key-management layer specifically for this network, not shared across networks).
2. 2When the Certifications page loads, it reads both columns for every row in the network, then unwraps the network's shared key once per page load and decrypts each franchisee_name_ciphertext value back into the name shown on screen.
3. 3If decryption fails for any reason (a key rotation in progress, a malformed value), the page falls back to the plaintext franchisee_name column rather than showing a broken row. That plaintext sibling column exists specifically so a decrypt hiccup degrades to "show the name" instead of "break the page."

Nothing else on this table is encrypted, because nothing else on this table is a specific, person-identifiable detail. A count of expired certifications is not privacy-sensitive by itself; a name attached to it is what makes the row about someone, and that is the value Verinode encrypts.

How this differs from the Compliance section's view

Certifications is not the only place HQ shows credential data, and it is easy to conflate this page with the network's Compliance surface (see /help/hq-compliance). They read from two different tables, built for two different questions:

| | Certifications (this page) | Compliance | |---|---|---| | Table | the network data | the network data | | Row shape | One row per membership | One row per membership per cert type | | Answers | "How is this membership's certification posture overall?" | "Is this specific cert type current, expiring, expired, or missing for this membership?" | | Values shown | Counts, percentages, dates | A status per cert type: current, expiring, expired, or missing, plus its expiration date |

Certifications is the aggregate read: total certs, expired count, expiring count, CEC completion, one number per membership, built to answer "where is credential risk concentrated across the network right now." Compliance is the per-cert-type matrix: it breaks the same underlying posture out by the individual certification type (an IICRC designation, a state license, a specific safety card) so you can see which specific credential is behind a flag for which membership. Both tables are aggregate rollups written by the same aggregate-refresh cron from the same class of underlying PII records, and both encrypt the membership's display name the same way, under the network's shared key, with the same plaintext-column fallback. Neither table, at any depth, exposes certificate numbers, training vendors, costs, or team member identities. Use Certifications when you want the franchisee-level summary number; use Compliance when a specific certification type needs a network-wide look.

What HQ's application actually queries

When an HQ admin opens the Certifications page, the application authenticates that admin's session, resolves which network (group) they belong to, and queries the network data filtered to that one network's group_id. An admin from one network structurally cannot pull rows belonging to a different network, the query itself is scoped before any row is returned. Row Level Security is enabled on the table, and the only grants on it are to Verinode's service-role backend and the HQ application role, there is no broad "authenticated" read policy that would let a client-side request page through it directly.

The rows returned then get sorted (by total certification count, descending, by default) and grouped into the page's tile rows: the ones with any expired certifications, the ones with anything expiring inside 90 days, the ones below 80% CEC completion, and the full by-membership list. None of that grouping or sorting touches PII, it is all arithmetic over the already-aggregated counts.

Best-practice example

A network compliance lead wants to know which memberships need a call this week about credential risk. They open Certifications, see the Expired Certifications row populated with three memberships, and the CEC Completion row flagging two more below 80%. They do not need, and cannot get, a list of which specific team members at those memberships are unlicensed or which specific credential lapsed, that stays in each membership's own account. What they get instead is exactly enough to know where to focus a conversation: which memberships, how many certifications, how urgent. The membership keeps ownership of the underlying record; HQ gets the signal it needs to act on network risk.

Related reading

• Certifications: your network's credential health at a glance, the page overview and its four tile rows
• The network certifications header, the hero panel's numbers explained in depth
• Expired Certifications and Expiring Inside 90 Days, the two risk-focused rows
• Cert Watch, the fast weekly scan on the Network Health home
• HQ compliance, the per-cert-type matrix broken out by certification type
• The HQ privacy boundary, how this same aggregate-only, encrypted-name pattern applies across every HQ surface, not just certifications