How the forms rollup is built (nightly aggregation)

What this pipeline does

Every franchisee running Verinode IQ collects data in three related tools built on one underlying primitive: surveys, checklist audits, and review programs. None of that raw activity, the actual survey questions, individual responses, or a specific audit's findings, ever reaches HQ. What HQ sees on the Forms & Audits page is a nightly rollup: one summary row per franchisee, holding counts, rates, and averages only. This article documents the pipeline that builds that rollup, table by table, column by column, and the encryption and access boundary that keeps it that way.

The pipeline has three parts:

1. A nightly cron job, hq-aggregate-refresh, that reads franchisee-side data and writes aggregate rows.
2. A single destination table, the network data, one row per franchisee per network.
3. A read path, getFormsRollup, that HQ's Forms & Audits page calls to turn those rows into the hero numbers and franchisee tiles you see.

Where to find it

Since the Compliance surface reorganization on July 11, 2026, Forms & Audits is not its own sidebar item. It lives as a tile inside Compliance (sidebar label "Compliance", clean URL hq.verinode.ai/compliance), under the Standards & Audits row, alongside a Process Standards tile (see /help/hq-standards). The tile is labeled Forms & Audits, and its headline reads either an average audit score (for example "84 avg score") when the network has completed audits, or a plain form count ("12 forms") when it does not. Beneath that, a line reads "X open · Y unresolved finding(s)." A row of dots below the tile shows open forms as dots, with any tied to an unresolved finding marked in a different tone. Clicking the tile, or its "Open detail" link, opens the full page at hq.verinode.ai/forms. The full page is also directly bookmarkable at that URL even though it no longer has its own sidebar entry.

The Forms & Audits page

The hero band

At the top, the eyebrow reads "Network forms" over a large count, the total number of forms across the whole network (see "What counts as a form" below for exactly what that includes). Beside it, a pill reads either "N franchisees active" (pluralized correctly for N=1) or, if nothing has happened yet, "No activity yet." That pill's color follows the same scale as the "Open findings" metric described below.

Under the headline, a line of context reads, for example: "14 surveys · 6 audits · 3 reviews · 5 closed in last 30d." When there is no data at all, that line instead reads: "Form data will appear as franchisees run surveys, audits, and review programs."

Three secondary numbers sit beside the headline:

• Response rate: the network-wide percentage of survey invites that received a response, labeled "Across all active programs" when there is a number, or "Awaiting responses" when there is none yet. It reads green (Expand tone) at 70% or above, neutral in the 40-69% range, and amber (Maintain tone) below 40%.
• Avg audit score: the network's audit score, weighted by how many audits each franchisee has completed (a franchisee with ten audits at 80 counts more than one franchisee with a single audit at 100), labeled "Weighted across franchisees," or "No completed audits" when there is nothing to average. It reads green at 80 or above, neutral from 60 to 79, and red (Analyse tone) below 60.
• Open findings: the total count of audit findings across the network that have not yet been marked resolved, labeled "Across all audits." It reads green at zero, neutral at one to three, amber at four to ten, and red above ten.

Open Audit Findings row

This row surfaces franchisees carrying unresolved audit findings, worst first. Each tile shows a label of "Open" or, once a franchisee has five or more open findings, "Multiple," the franchisee's name, a line reading "N open finding(s)," and, when the franchisee has a computed audit score, "Avg score N" beneath it. The tile's accent turns red once a franchisee reaches five or more open findings; below that it stays amber. When no franchisee in the network has an unresolved finding, the row reads: "No open audit findings across the network. Findings surface here when a franchisee logs an audit that flagged an item without a remediation date."

That "without a remediation date" detail is exact: an audit finding only counts as open in this rollup while your operator data.remediation_closed_at is null. The moment a franchisee marks a finding remediated on their own side, it drops out of every HQ view on the next nightly run.

Response Rate row

This row surfaces franchisees whose survey response rate needs attention, worst first: it only includes franchisees who have sent a meaningful number of invites (at least three) so a single unanswered invite doesn't misleadingly show as a 0% rate, and only those currently under 50%. Each tile shows the rate ("38% rate"), the franchisee's name, "N of M responded," and how many programs are still open beneath it. A small marker chart on the tile plots the rate against the 50% line. When every active program across the network is clearing 50%, the row reads: "All active form programs are achieving at least 50% response rate."

Activity by Franchisee row

This is the full roster: every franchisee with at least one form on record, sorted by total form count, highest first (capped at the first twelve on the page). Each tile shows the form count ("1 form" or "N forms"), the franchisee's name, "Last activity" phrased relative to now (today, yesterday, "3d ago," "2w ago," "4mo ago," "1y ago," depending on how long it's been), and either "N open" or, if nothing is open, "N closed 30d." A small segmented bar splits the tile's recent activity between what closed in the last 30 days and what's still open. When no franchisee has any form activity yet, the row reads: "Activity will appear as franchisees launch their first survey or audit."

Clicking any tile in any of these three rows opens that franchisee's detail record on the Network page.

What counts as a form

Verinode's Forms, Audits, and Reviews tools on the franchisee side all sit on one underlying table, your operator data, distinguished by a mode column (survey, audit, or review). Separately, franchisees running structured checklist audits use a dedicated your operator data table, which is a different mechanism from a mode='audit' row in your operator data. The rollup combines both, and it matters where a given number lands:

• Surveys count, Audits count, and Reviews count on the hero and franchisee rows come only from your operator data, split by its mode column. "Audits count" here reflects only survey-shaped audits, not checklist audits.
• The total form count you see everywhere (the hero headline, and each franchisee tile's "N forms") adds every your operator data row, in any mode, to every your operator data row. A checklist audit counts toward the total exactly once.
• Avg audit score and open findings, wherever they appear, are computed from both audit sources combined: survey-shaped audits plus checklist audits. Internally the rollup tracks this as audit_count_total, the sum of audits_count (from your operator data) and the checklist-audit count (from your operator data). That combined total is what weights the network average and what open findings are measured against; it isn't currently broken out as its own number on the page, but it's the reason the audit score you see reflects a franchisee's whole audit practice, not just one half of it.

The nightly refresh, step by step

The pipeline runs inside hq-aggregate-refresh, the single scheduled route that bridges every franchisee-side table into every the network data* rollup table HQ reads, forms among roughly twenty others. It's the only place in the codebase allowed to read both pii.* and write core.* in the same request.

1. 1The cron fires on a schedule of */10 6-7 * * * UTC, meaning it wakes up every ten minutes across a roughly two-hour early-morning maintenance window. A single run only has five minutes to work (a 300-second serverless ceiling), and a large network can't finish in one pass, so the sweep is checkpointed: each invocation picks up from a saved cursor recording which group it reached last time, works through as many groups as the time budget allows, and saves the cursor again before it has to stop.
2. 2The run that reaches the last group in the network marks the sweep complete for that day; any later ten-minute invocation in the same window sees the sweep is done and does nothing. If the window closes with the sweep still incomplete, that's flagged as a partial sweep on Verinode's internal health monitoring, not on anything you see, but it means that day's numbers may be a run behind for the franchisees that weren't reached.
3. 3For each franchisee network (group) it processes, the route first makes sure every active franchisee has a proper display name on file (self-healing a placeholder like "Unnamed office" from the franchisee's own company name if one is missing), then runs each group_*_summary refresher for that network, forms included.
4. 4Inside the forms refresher specifically: it reads every your operator data row for every franchisee in the network (id, mode, status, timestamps), every your operator data row tied to those surveys (to compute response health), every your operator data row (checklist audits, with score and completion date), and every your operator data row still missing a remediation_closed_at (open findings). All four reads are scoped to the specific franchisees in that network; the refresher never scans the whole platform at once.
5. 5It tallies all four sources per franchisee: form counts by mode, which forms are still open (status of open, active, or draft), which closed in the last 30 days, invite and response counts, audit counts and their average score, and the open-findings count.
6. 6It writes (upserts) one row per franchisee into the network data, keyed on (group_id, operator_id). A second run for the same franchisee the same night overwrites the prior row rather than duplicating it.
7. 7If a single franchisee's row fails to write (a database error, for example), the refresher logs it and moves on to the next franchisee rather than failing the whole network's rollup.

Every column in the network data

This is the exact table the nightly refresher writes to and the Forms & Audits page reads from. One row exists per (group_id, operator_id) pair, meaning per franchisee per network.

| Column | What it holds | Where it surfaces | |---|---|---| | franchisee_name / franchisee_name_ciphertext | The franchisee's display name, in plaintext and, when available, encrypted (see below) | Every tile's headline name | | form_count | Total forms: every your operator data row (any mode) plus every your operator data row | Hero headline; each franchisee tile's "N forms" | | surveys_count | Count of your operator data rows with mode = 'survey' | Hero subtext ("N surveys") | | audits_count | Count of your operator data rows with mode = 'audit' (survey-shaped audits only) | Hero subtext ("N audits") | | reviews_count | Count of your operator data rows with mode = 'review' | Hero subtext ("N reviews") | | open_forms_count | Forms currently in open, active, or draft status | Franchisee tiles' "N open" | | closed_last_30d | Forms whose closed_at falls in the trailing 30 days | Hero subtext; franchisee tiles' "N closed 30d" | | total_invites | Every your operator data row across that franchisee's surveys | Response Rate row's "N of M responded" | | total_responses | Invites with a responded_at timestamp set | Response Rate row's "N of M responded" | | response_rate_pct | total_responses ÷ total_invites × 100, null when there have been no invites | Hero "Response rate"; Response Rate row | | audit_count_total | audits_count (survey-shaped audits) plus the count of your operator data rows (checklist audits) | Weighting factor for the network average; not shown as a standalone figure today | | avg_audit_score | Average score across completed your operator data rows scored 0 to 100, null with no completed audits | Hero "Avg audit score"; findings-row tiles' "Avg score N" | | open_audit_findings_count | your operator data rows for that franchisee with no remediation_closed_at set | Hero "Open findings"; Open Audit Findings row | | last_form_activity_at | The most recent of any survey's closed_at, sent_at, or created_at, or any audit's completed_at or created_at | Activity row's "Last activity" | | computed_at | Timestamp of the run that produced this row | Internal only; not shown on the page |

Franchisee names: encrypted at rest, decrypted for the page

franchisee_name is written in plaintext on every refresh. Alongside it, the same refresh attempts to write franchisee_name_ciphertext: the name encrypted under the network's shared data-encryption key (a key generated once per network and wrapped by Verinode's key-management service so that server-side jobs, this cron included, can encrypt on write without an admin session open).

When the Forms & Audits page loads, it fetches both columns, unwraps that shared key once per page load, and decrypts franchisee_name_ciphertext for every row in a single pass. Only if that decryption fails, for example if the key isn't available at that moment, does the page fall back to the plaintext franchisee_name column so the name still renders rather than the row breaking. The plaintext column exists specifically as that fail-open safety net, not because franchisee names are meant to be hidden from HQ. HQ leadership is meant to see which franchisee is which; the point of the encryption is that a franchisee's identity, sitting in a network-wide rollup table, is not stored as bare readable text at the database layer.

This mirrors the same pattern used across every other group_*_summary table (workforce, materials, exterior and roofing, and more): counts, percentages, and averages are aggregate network dimensions and are written in plaintext by design, since they're already safe to hold in the open once aggregated across a franchisee. Only the identifying name column gets the encrypted-at-rest treatment.

The privacy boundary this pipeline protects

This is the whole reason the pipeline exists as a nightly batch job rather than HQ querying franchisee data live. HQ's read path (hqAdmin) connects to Verinode's database as a distinct Postgres role, hq_app, that has an explicit, database-enforced revocation on the entire the private layer schema: a query against pii.* from that connection returns a permission-denied error at the database layer itself, before Verinode's application code even runs. HQ's Forms & Audits page can only ever read the network data, the pre-aggregated table this pipeline writes; it has no path to a franchisee's individual survey answers, an individual audit's write-up, or an individual finding's detail, because those live in your operator data, your operator data, and related tables that hq_app cannot reach.

That database-level rule is backed up by two more layers: every file under HQ's code path is required to use that same restricted connection (a separate automated check rejects any commit that tries to import the franchisee-side database client from HQ code), and every write this pipeline makes is recorded in an audit trail. The only code in the entire platform permitted to read franchisee-side data and write network-side aggregates in the same request is this one nightly cron. That's a deliberate, narrow bridge, not an oversight: it means the privacy boundary between "your data" and "the network's aggregate" is enforced by the database itself, not just by page-level filtering.

Franchisees missing from the page

A franchisee only gets a row on this page once they have an active, named entry in the network's member directory. A franchisee who has joined the network but whose office record HQ hasn't named yet is automatically backfilled with their real company name (drawn from their own profile) on the next nightly run, so this should self-correct without action. If a franchisee genuinely has zero forms, zero audits, and zero reviews on record, they still get a row in the underlying table with every count at zero; they simply won't appear in the Open Audit Findings, Response Rate, or Activity by Franchisee rows, since all three only list franchisees with something to show.

Related reading

• /help/hq-compliance: the Compliance surface this tile lives on, and how it rolls up cert posture, safety, and the audit log alongside forms.
• /help/hq-standards: Process Standards, the sibling tile in the same Standards & Audits row.
• /help/network-health: how franchisee identity and directory names are curated network-wide.
• /help/hq-overview: how HQ's aggregate-only model works across every section, not just Forms.