Database schema

Overview

organizations (workspaces — Better Auth)
  └── workspaces
       └── projects (subject + standard binding)
            └── audits (point-in-time assessments)
                 └── results
                      └── evidence

users (Better Auth)
  └── user_profiles (is_staff flag)

audit_standards (e.g. WCAG 2.2, WCAG 3.0)
  └── criteria (e.g. 1.4.3 Contrast Minimum)

A project is the standard-bound engagement (e.g. “Acme marketing site — WCAG 2.2”). Audits underneath it are iterations over time: a baseline, a post-remediation re-audit, an annual recheck. Each audit is a point-in-time snapshot that holds results and can be locked.

All domain entities (projects, audits) are scoped to a workspace. Users access entities through workspace membership (via the owning organization).

Auth tables

Managed by Better Auth. Do not modify directly.

• users — core user record (id, email, name, emailVerified)
• sessions — active sessions
• accounts — OAuth provider links
• verifications — email verification tokens

Domain tables

user_profiles

Extends the Better Auth users table. One row per user.

Column	Type	Notes
user_id	text	PK, FK → users.id
is_staff	boolean	Internal team flag; default false. Staff can see beta/draft standards.
created_at	timestamp
updated_at	timestamp

Tier has moved to the workspace level (organizations.tier). Tier limits are enforced at the application layer via TIER_LIMITS (exported from @inntrig/db).

audit_standards

Versioned audit standards (e.g. WCAG 2.2, WCAG 3.0, EN 301 549). Generic naming to support non-WCAG standards.

Column	Type	Notes
id	text	PK, e.g. "wcag-2.2", "wcag-3.0"
name	text	e.g. “WCAG 2.2”
version	text	e.g. “2.2” (nullable)
status	enum	draft, beta, published; default published
published_at	timestamp	Required when status is published (enforced by CHECK constraint)
is_global	boolean	true = Inntrig-maintained; false = custom org standard (future)
config	jsonb	Defines criterionFields and resultFields for the standard

GET /standards returns only published standards unless the session user has is_staff = true, in which case all statuses are returned. Creating a project with a non-published standard requires staff access.

The config column drives the UI. For example, WCAG 3.0 defines resultFields: { score: { type: "number", min: 0, max: 4 }, hasCriticalError: { type: "boolean" } } which renders score and critical error controls instead of the default pass/fail buttons.

criteria

Individual criteria within a standard. Supports hierarchical structures via parent_id.

Column	Type	Notes
id	text	PK, e.g. "wcag-2.2-1.4.3"
standard_id	text	FK → audit_standards.id
ref	text	e.g. “1.4.3” for WCAG 2.x; outcome ID for WCAG 3
name	text	e.g. “Contrast (Minimum)”
summary	text	Short description
guidance	text	Full guidance text
attributes	jsonb	Standard-specific data defined by config.criterionFields (nullable)
parent_id	text	Self-referencing FK for hierarchical standards (nullable = top-level)
sort_order	text	Controls display order within the standard

For WCAG 2.x, attributes contains {"level": "AA"}. For WCAG 3.0, it contains {"guideline": "1", "guidelineName": "Perceivable"}.

projects

The standard-bound engagement — typically one per subject (site, product, client) being assessed against one standard. Scoped to a workspace.

Column	Type	Notes
id	uuid
workspace_id	uuid	FK → workspaces.id
created_by	text	FK → users.id
standard_id	text	FK → audit_standards.id — the standard every audit under this project is assessed against
name	text
slug	text	Unique per workspace
description	text	nullable
subject	text	User-defined identifier for the thing being audited (product/site/client). nullable. Two projects with the same subject roll up together — see below.
target_url	text	Canonical URL of the thing being audited. nullable.
status	enum	active, archived, completed; default active
created_at	timestamp
updated_at	timestamp

Cross-standard rollups. A project binds to exactly one standard. To assess the same subject against multiple standards (e.g. WCAG 2.2 and WCAG 3.0), create one project per standard and give them the same subject value. subject is compared case-insensitively; the UI autocompletes from existing values within the workspace.

audits

A point-in-time assessment within a project — the exportable deliverable. Inherits its standard from the parent project.

Column	Type	Notes
id	uuid
workspace_id	uuid	FK → workspaces.id
created_by	text	FK → users.id
project_id	uuid	FK → projects.id
title	text
slug	text	Unique per workspace
description	text	nullable
reported_at	timestamp	Date of the assessment
locked_at	timestamp	null = in progress (editable). Non-null = locked (read-only).
created_at	timestamp
updated_at	timestamp

When locked_at is set, the audit is fully read-only: criteria links and status/notes editing are hidden in the UI. Use PATCH /audits/:slug { locked: true } and PATCH /audits/:slug { locked: false } to transition.

results

A result for one criterion within one audit.

Column	Type	Notes
id	uuid
audit_id	uuid	FK → audits.id
criterion_id	text	FK → criteria.id
status	text	pass, fail, na, todo
notes	text	nullable
attributes	jsonb	Standard-specific result data (nullable). e.g. WCAG 3: {"score": 3, "hasCriticalError": false}
created_at	timestamp
updated_at	timestamp

One result per criterion per audit (enforced by a unique constraint on audit_id, criterion_id).

evidence

File attachments per result (for standards like GDPR, ISO that require supporting documentation).

Column	Type	Notes
id	uuid
result_id	uuid	FK → results.id
user_id	text	FK → users.id
name	text	Display name
type	text	document, screenshot, policy, url
url	text	R2 object key or external URL
notes	text	nullable

assertions

Organisational commitments per audit (for WCAG 3.0 Silver/Gold conformance).

Column	Type	Notes
id	uuid
audit_id	uuid	FK → audits.id
criterion_id	text	FK → criteria.id (nullable)
statement	text	The assertion text
scope	text	nullable
contact_info	text	nullable
asserted_at	timestamp
attributes	jsonb	nullable

Tier limits

Enforced at the application layer (not database constraints):

Tier	Workspaces	Projects	Audits/project
Free	1	1	10
Pro	Unlimited	Unlimited	Unlimited
Org	Unlimited	Unlimited	Unlimited