Back to Technical Documentation

Database Schema

Edit on GitHub

Database Schema

Equalify uses PostgreSQL as its primary database, accessed via direct queries (serverless-postgres) and GraphQL (Hasura).

Core Tables

audits

Stores audit configurations and metadata.

ColumnTypeDescription
idUUIDPrimary key
user_idUUIDOwner's user ID (FK)
nameTEXTAudit display name
intervalTEXTScan frequency (manual, daily, weekly, etc.)
scheduled_atTIMESTAMPNext scheduled scan time
statusTEXTCurrent status (draft, new, processing, complete, failed)
payloadJSONBFull audit configuration
responseJSONBLatest scan response
email_notificationsTEXTEmail alert setting
processed_atTIMESTAMPWhen the audit was last processed
remote_csv_urlTEXTSource URL for remote CSV URL sync
remote_csv_errorTEXTLast error from remote CSV sync
created_atTIMESTAMPCreation timestamp
updated_atTIMESTAMPLast update timestamp

urls

URLs associated with audits for scanning.

ColumnTypeDescription
idUUIDPrimary key
user_idUUIDOwner's user ID (FK)
audit_idUUIDParent audit (FK)
audit_idsJSONBAdditional audits this URL belongs to (array, defaults to [])
urlTEXTFull URL to scan
typeTEXTContent type (html, pdf)
created_atTIMESTAMPCreation timestamp
updated_atTIMESTAMPLast update timestamp

scans

Individual scan runs for audits.

ColumnTypeDescription
idUUIDPrimary key
audit_idUUIDParent audit (FK)
statusTEXTScan status (processing, complete, failed)
percentageNUMERICProgress percentage (0-100)
pagesJSONBArray of pages to scan
processed_pagesJSONBArray of completed page IDs
errorsJSONBArray of scan errors
blocker_countINTEGERDenormalized count of blockers found
equalified_countINTEGERDenormalized count of resolved blockers
created_atTIMESTAMPScan start time
updated_atTIMESTAMPLast update timestamp

blockers

Individual accessibility issues found during scans.

ColumnTypeDescription
idUUIDPrimary key
audit_idUUIDParent audit (FK)
scan_idUUIDParent scan (FK)
url_idUUIDURL where found (FK)
short_idTEXTShort, per-audit-unique identifier
contentTEXTHTML snippet or context
content_normalizedTEXTNormalized HTML used for hashing
content_hash_idUUIDHash for deduplication
targetsJSONBCSS/DOM target selectors
url_textTEXTDenormalized URL snapshot (fallback if the urls row is deleted)
equalifiedBOOLEANMarked as resolved
created_atTIMESTAMPDiscovery timestamp
updated_atTIMESTAMPLast update timestamp

staleblockers / staleblocker_messages

Archive of blockers from scans that are no longer their audit's latest. runEveryDay moves each such scan's rows out of blockers / blocker_messages into these tables in batches (same columns as the live tables, minus url_text), so the live tables only hold the most recent scan per audit. A row lives in exactly one of the two tables at any time, so a past scan is read by unioning both for its scan_id (see getHistoricalBlockers, which powers the Detailed View's Scan History picker and its CSV export). These tables are not tracked in Hasura and are read with plain SQL; their indexes are in db/migrations/add_stale_blockers_indexes.sql. Scans archived before the tables existed (June 2026) kept only part of their rows, which is why the picker only offers scans from then on.

messages

Accessibility rule definitions.

ColumnTypeDescription
idUUIDPrimary key
contentTEXTRule/message text
categoryTEXTMessage category

blocker_messages

Junction table linking blockers to messages.

ColumnTypeDescription
idUUIDPrimary key
blocker_idUUIDBlocker reference (FK)
message_idUUIDMessage reference (FK)
created_atTIMESTAMPCreation timestamp
updated_atTIMESTAMPLast update timestamp

tags

WCAG and other accessibility tags.

ColumnTypeDescription
idUUIDPrimary key
contentTEXTTag name (e.g., "wcag2aa")

message_tags

Junction table for message-tag relationships.

ColumnTypeDescription
idUUIDPrimary key
message_idUUIDMessage reference (FK)
tag_idUUIDTag reference (FK)
created_atTIMESTAMPCreation timestamp
updated_atTIMESTAMPLast update timestamp

ignored_blockers

Blockers marked as ignored/resolved.

ColumnTypeDescription
idUUIDPrimary key
audit_idUUIDAudit reference (FK)
blocker_idUUIDBlocker reference (FK)
content_hash_idUUIDHash reference, used to match ignored blockers across scans
created_atTIMESTAMPWhen ignored
updated_atTIMESTAMPLast update timestamp

users

User accounts and profiles.

ColumnTypeDescription
idUUIDPrimary key (matches Cognito sub)
emailTEXTUser email address
nameTEXTDisplay name
typeTEXTUser type (defaults to member; also admin)
analyticsJSONBAnalytics/tracking data
apikeyUUIDAPI key for programmatic access
created_atTIMESTAMPAccount creation
updated_atTIMESTAMPLast update timestamp

invites

Pending invitations to join as a user.

ColumnTypeDescription
idUUIDPrimary key
user_idUUIDInviting user (FK)
nameTEXTInvitee display name
emailTEXTInvitee email address
typeTEXTInvited user type
expires_onTIMESTAMPInvitation expiration

access_requests

Requests for access under SSO, reviewed by an admin.

ColumnTypeDescription
idUUIDPrimary key
emailTEXTRequesting user's email
statusTEXTRequest status (pending, approved, denied)
created_atTIMESTAMPRequest timestamp

blockerllmsummaries

AI-generated summaries and flags for blockers.

ColumnTypeDescription
idUUIDPrimary key
blocker_idUUIDBlocker reference (FK)
summaryTEXTAI-generated summary text
created_atTIMESTAMPCreation timestamp

options

Key/value configuration store.

ColumnTypeDescription
idUUIDPrimary key
keyTEXTOption name
valueJSONBOption value

options

Key/value settings (key TEXT primary key, value TEXT). Besides workspace settings, three keys carry platform KPI data (see Platform KPIs): kpi_red_sites_total (maintained by hand: the Red adoption denominator and its date), kpi_snapshot and kpi_red_hosts (written daily by runEveryDay; never edit). db/migrations/add_kpi_options.sql seeds the denominator.

logs

Activity audit trail.

ColumnTypeDescription
idUUIDPrimary key
user_idUUIDActing user (FK)
audit_idUUIDRelated audit (FK, nullable)
messageTEXTLog message
dataJSONBStructured log details
created_atTIMESTAMPAction timestamp
updated_atTIMESTAMPLast update timestamp
User accounts are standalone, and invites is how additional users get added.

sessions

One row per authenticated app load or login, written by trackSession. Backs the month-over-month KPIs on the admin Statistics tab (sessions started, active users, units served).

ColumnTypeDescription
idUUIDPrimary key
user_idUUIDAuthenticated user (matches users.id)
auth_methodTEXTcognito or sso
departmentTEXTCampus unit from Microsoft Graph (SSO logins only; null otherwise)
analyticsJSONBRequest analytics (location, device) plus any org fields sent at login
created_atTIMESTAMPSession start

Relationships

users
  └── audits (many)
        ├── urls (many; also tracked via urls.audit_ids for multi-audit membership)
        ├── scans (many)
        │     └── blockers (many, also FK'd directly to audits and urls)
        └── blockers (many, direct FK)
              ├── blocker_messages (many) ─┬── messages (shared/deduplicated across audits)
              │                            └── message_tags (many) ── tags (shared)
              └── ignored_blockers (via content_hash_id)

messages and tags are deduplicated by content hash and shared across audits/scans — a message or tag is not owned exclusively by one blocker, so the relationship is many-to-many via the blocker_messages/message_tags junction tables, not a strict one-way hierarchy.

Content Hashing

Blockers, messages, and tags all use content hashing for deduplication, via a shared hashStringToUuid helper (double SHA-256, truncated to a UUID-shaped hex string). The blocker hash is what the Recommendations view groups by (getAuditRecommendations), and what makes ignoring hash-wide: ignored_blockers.content_hash_id lets both the UI and scanWebhook treat every identical node, on any page or in any future scan, as one ignored blocker:

const contentNormalized = normalizeHtmlWithVdom(blocker.node);
const contentHashId = hashStringToUuid(contentNormalized);

const messageId = hashStringToUuid(blocker.description + blocker.test);
const tagId = hashStringToUuid(tag);

This allows:

  • Tracking blocker persistence across scans (same normalized node content → same content_hash_id)
  • Identifying when blockers are resolved
  • Deduplicating shared messages and tags rows across audits and scans

GraphQL Access (Hasura)

Hasura provides GraphQL access with row-level security:

query GetAuditBlockers($audit_id: uuid!) {
  audits_by_pk(id: $audit_id) {
    scans(order_by: {created_at: desc}, limit: 1) {
      blockers {
        id
        content
        url_id
        blocker_messages {
          message {
            content
            category
            message_tags {
              tag {
                content
              }
            }
          }
        }
      }
    }
  }
}

Row-Level Security

Hasura defines two roles, anonymous and user (no admin role exists in Hasura). JWT claims carry the role and user ID:

{
  "x-hasura-allowed-roles": ["user"],
  "x-hasura-default-role": "user",
  "x-hasura-user-id": "user-uuid",
  "x-hasura-org-id": "org-uuid"
}

Row-level ownership is not broadly enforced by Hasura permission filters — most tracked tables (including audits) have an open {} select filter for the user role. The one exception is urls, where the update permission filters on user_id; its delete permission is open. Ownership checks are instead primarily enforced at the Lambda layer, where handlers filter directly on the authenticated user's ID (event.claims.sub) in SQL queries. Admin-only endpoints (e.g. getSystemStats, reviewAccessRequest) are likewise enforced in the Lambda handler based on users.type, not via a Hasura role.

Atomic Operations

Scan progress uses atomic PostgreSQL operations to prevent race conditions:

UPDATE "scans" 
SET 
  "processed_pages" = CASE 
    WHEN NOT (COALESCE("processed_pages", '[]'::jsonb) @> $1::jsonb)
    THEN COALESCE("processed_pages", '[]'::jsonb) || $1::jsonb
    ELSE "processed_pages"
  END
WHERE "id" = $2
RETURNING "pages", "processed_pages"

For API usage examples, see the Backend API Reference.