Skip to main content

ADR 008: GRC integrations and automated tests

Status: Accepted

Context​

ADR 007 left every control at Not started because nothing feeds it signals yet. The first real signal source is Google Workspace: today's Risk Assessment page fetches Cloud Identity policies and the browser decides pass or fail. Nothing is saved, there is no history, and the login is a per-user UserToken stored in plain text, so the check stops working when that person leaves.

We want saved, scheduled tests whose latest results move controls to Passing or Failing on their own. That needs a few decisions up front, because every issue in the epic (connect, evaluate, run, signal, read, UI) builds on them:

  • Whose credentials are they, and how are they stored?
  • What can a test result be, and what does a control do with each outcome?
  • What does "failing since Oct 3" mean when a run errors in between?
  • How much do we store per run?

Decision​

1. The model​

TestDefinition ("2-step verification enforced")   ← catalog, seeded by us, no teamId
  └─ TestDefinitionControl ──► ControlTemplate ("mfa-enforced")
Integration (team's Google Workspace)             ← teamId, encrypted credentials, status
  └─ TestRun (one per run: nightly or "Run now")
       └─ TestResult (one per test: PASS / FAIL / EXCUSED / ERROR)
TestException (team excuses a test or org unit)   ← teamId, reason, approver, expiry

Latest results → a ControlSignalSource → ControlStatusService.recalculate → the control turns green or red. The source plugs into the existing CONTROL_SIGNAL_SOURCES (ADR 007 §5); the status rules do not change.

A test links to the control templates it is evidence for. A team's control (a copy of a template) picks up the tests of its template. Custom controls have no template and so no tests.

2. Integrations belong to the team​

An Integration is owned by the team, with one row per provider (@@unique([teamId, provider])). The user who connected it is stored as connectedById for the audit trail only: nothing is authorised through that user, and deleting them sets the column to null and leaves the integration running.

  • v1 uses a Google OAuth refresh token, kept on the integration. It replaces the per-user UserToken for tests.
  • A refresh token is still granted by a Google account. If that Google account is suspended or deleted, Google rejects the token and the integration moves to NEEDS_REAUTH. Losing the RiskFlow user does not do this; losing the Google admin who connected it can. Admins should connect with an account that is meant to stay.
  • Later option: a service account with domain-wide delegation removes the dependency on one human's Google account, at the cost of a one-time setup in the customer's Admin console. We do not build it now. We revisit it if teams hit NEEDS_REAUTH after staff changes.
  • The scopes stay read-only: the same two as the Risk Assessment login today (Cloud Identity policies and Directory org units).

Statuses: CONNECTED, NEEDS_REAUTH (credentials rejected; an admin must reconnect), ERROR (the last run failed for another reason), DISCONNECTED.

3. Credentials are encrypted at rest​

credentialsEnc holds the credentials encrypted with AES-256-GCM using INTEGRATION_ENCRYPTION_KEY, a 32-byte key from the environment, never from the database.

  • The stored value is v1:<iv>:<tag>:<ciphertext> (base64 parts, a fresh random 12-byte IV per write, JSON credentials as the plaintext). The v1 prefix lets a future key rotation tell which envelope wrote a row. Decided and built in #43.
  • The API refuses to boot in production without a valid key, so a missing key is found at deploy, not the first time an admin clicks Connect.
  • Only the integration service and the test runner decrypt. Credentials are never returned by an endpoint and never logged.
  • Disconnecting sets credentialsEnc to null and the status to DISCONNECTED. The runs and results stay as history.

4. Result statuses and what a control does with each​

ResultMeaningSignal sent to the control
PASSThe live setting matches what the test expectsPASS
FAILIt does not, for at least one org unit not covered by an exceptionFAIL
EXCUSEDIt does not, but an unexpired exception covers every failing org unit (or the whole test)PASS
ERRORWe could not tell (API error, permission missing, credentials rejected)none
  • ERROR and stale results send no signal, so a broken connection never turns a control green or red. A result is stale when it is older than 48 hours or its integration is not CONNECTED. A control with no signals shows Not started, which is honest: nothing is currently checking it. The constant lives with the signal source (#46).
  • EXCUSED counts as a pass for the control. The exception is the team's recorded decision with a reason, an approver and an expiry, and it appears in the audit log. The Tests page still shows the test as excused, not passing.
  • Org-unit exceptions excuse only the org units they name. If some failing org units are covered and others are not, the result is FAIL. failingOrgUnits keeps every failing org unit and marks the covered ones excused: true; the API shows the two groups separately. Keeping them lets adding or removing an exception decide the latest result again from the saved row, without calling Google (#47).
  • A control with several tests follows the existing rule: any FAIL makes it Failing; otherwise it is Passing once every test that does send a signal passes.
  • When controls are recalculated (#46): after every run, failed ones included, for all of the team's controls linked to a Google test, and for all of the team's controls after a disconnect. Not only the controls whose test changed: a result that was stale and is fresh again changes the control too, and a failed run can leave the integration ERROR or NEEDS_REAUTH, which takes the signals away. So a single failed nightly run sends linked controls back to Not started until the next good run. Nothing recalculates on read, and no sweep is needed for the 48-hour rule: a CONNECTED team is run (and recalculated) every 24 hours.

5. "Failing since" is firstFailedAt​

TestResult.firstFailedAt is when the test started failing, carried forward from the test's previous result:

  • FAIL: the previous result's firstFailedAt if it has one, otherwise the run's start time.
  • ERROR: the previous result's firstFailedAt, if any. We could not tell, so one failed API call does not reset the clock.
  • PASS or EXCUSED: null. When an exception expires and the test fails again, the clock starts again.

6. One result per test per run​

A run stores one TestResult per test, not one per org unit. Which org units fail is stored as JSON on that row (failingOrgUnits), along with the actual value that was compared. This keeps a run to about 48 rows per team, and the Tests page reads one row per test. We lose the ability to query "all results for org unit X" in SQL; nothing planned needs it.

TestResult repeats teamId from its run, as ControlRequirement does, so tenant-scoped reads do not need a join. An index on (teamId, testDefinitionId, createdAt DESC) serves "latest result per test per team".

7. Catalog rows and team rows are split​

TablesteamIdWritten by
CatalogTestDefinition, TestDefinitionControlnoneidempotent seed scripts only
TeamIntegration, TestRun, TestResult, TestExceptionalwaysthe integration service, the runner and the API

Team rows follow the usual rules: every query filters by teamId taken from the principal, never the body, and a miss across teams returns 404 (ADR 004). Seeds are keyed on TestDefinition.key.

A test that is retired is set isActive = false, not deleted, so old results keep their definition.

8. Permissions and audit​

ActionPermission
Connect, disconnect, Run nowMANAGE_INTEGRATIONS (new, 1 << 25): ORG_ADMIN and MSP_SUPER_ADMIN
Read integrations, runs, resultsACCESS_CONTROLS
Add or remove a test exceptionMANAGE_CONTROLS

As with MANAGE_CONTROLS, "manage implies access" lives in the role presets, and a spec checks every preset holding MANAGE_INTEGRATIONS also holds ACCESS_CONTROLS. A data migration ORs the new bit into existing admin rows.

Audit events (category COMPLIANCE):

ActionActor
compliance.integration_connecteduser
compliance.integration_disconnecteduser
compliance.integration_needs_reauthsystem
compliance.test_run_starteduser for Run now, system for the nightly run
compliance.test_status_changedsystem (a test flipped between PASS and FAIL)
compliance.test_exception_addeduser
compliance.test_exception_removeduser

9. How a Google test decides PASS or FAIL​

Decided and built in #44. The tests live in apps/backend/prisma/catalog/google-workspace-tests.ts; evaluateGoogleTest (modules/grc-tests/google/) is a pure function the runner calls with the fetched policies and org units.

  • TestDefinition.expected is a list of rules. Each rule is { field, op, value } with op one of eq (by value, key order ignored), in, gte, lte, durationLte, durationGte ("43200s" compared as seconds) or notAfter (a timestamp at or before a date or "now"). Rules are written so a setting at least as strict as best practice passes. Fields the rules don't name are ignored.
  • Each org unit gets its effective setting: of the policies on it or an ancestor, the one with the highest sortOrder (without one, the nearer org unit, then ADMIN over SYSTEM). Group-targeted policies are ignored. The test passes only when every org unit passes; failingOrgUnits lists the ones that don't, each with its effective values.
  • A field nobody set takes Google's documented default from TestDefinition.defaults (its own nullable column). With no default it fails as "not configured" (notConfigured on the org unit).
  • An unknown op, a malformed rule or a value of the wrong type gives ERROR, never PASS.
  • Five checks are stricter than the old browser check, because its best practice was the weakest option or not a value Google accepts: Advanced Protection security codes, 2-step verification methods, Meet's two join settings and Chat external file sharing. Teams that need the looser setting (for example patient video visits) record a test exception.

10. Running the tests​

Decided and built in #45. TestRunService (modules/grc-tests/) runs a team's tests on "Run now" (POST /tests/runs) and from Vercel Cron (GET /internal/grc-tests, every 15 minutes).

  • One run at a time per team. Starting a run takes a Postgres advisory lock for the team, then returns the run already RUNNING if there is one. "Run now" and the cron can arrive together without starting two runs.
  • "Run now" is limited to once per team every 5 minutes, counted from saved MANUAL runs, so it holds across serverless instances (429, TEST_RUN_RATE_LIMITED). The nightly run is not limited.
  • A failed run writes no results. Google errors and rejected credentials mark the run FAILED with an errorCode, so the previous run's results stay the latest. The integration moves to ERROR with that code, unless the token service already set NEEDS_REAUTH. The next successful run moves ERROR back to CONNECTED.
  • The cron runs CONNECTED and ERROR integrations whose lastRunAt is more than 24 hours old, oldest first, up to 20 per call, and starts no new run after 240 seconds (Vercel stops a function at 300). ERROR is included because it means "the last run failed", often for a passing reason; leaving it out would stop nightly runs after one Google outage. NEEDS_REAUTH and DISCONNECTED need a person and are skipped.
  • lastRunAt is set by failed runs too, so a failing team waits a day like any other instead of being retried, first in line, every 15 minutes.
  • A run still RUNNING after 10 minutes is marked FAILED with TEST_RUN_TIMED_OUT at the start of each cron call, and before a team's next run. Each Google call times out after 30 seconds.
  • Status changes are audited against the test's result in the team's last completed run, including changes to and from ERROR. A test's first result is not a change.

Consequences​

  • Until a team connects Google, its controls stay Not started, as today.
  • A broken or revoked connection shows up as NEEDS_REAUTH and as controls drifting back to Not started once results go stale. It never shows up as false Failing or false Passing.
  • A control with one passing test and one test that is erroring shows Passing, because the erroring test sends no signal. The Tests page shows the error; the control page should not hide it.
  • Storing every test result every night is about 48 × 365 ≈ 17,500 rows per team per year. That is fine for now; pruning old runs is a later decision and must keep each test's latest result and the run that set its firstFailedAt.
  • A refresh token depends on one Google account staying active (§2). The service-account option is the way out if that proves painful.
  • Audit events need wording in the frontend audit-events.ts. Until then they show through its generic fallback. Each emitting issue (#43, #45, #47) adds its own wording and metadata shape.
  • Adding Microsoft 365 or MDM later means new IntegrationProvider values, new test definitions and a new signal source. The tables here do not change.

References​