Database schema

Overview

organizations (workspaces — Better Auth)
  └── projects
       └── audits
            └── reports
                 └── 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)

All domain entities (projects, audits, reports) are scoped to an organization (workspace). Users access entities through workspace membership.

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 an audit 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

Top-level grouping, typically one per client. Scoped to a workspace.

Column	Type	Notes
id	uuid
organization_id	text	FK → organizations.id
created_by	text	FK → users.id
name	text
description	text	nullable
created_at	timestamp
updated_at	timestamp

audits

A scoped body of work within a project, tied to a specific standard.

Column	Type	Notes
id	uuid
organization_id	text	FK → organizations.id
created_by	text	FK → users.id
project_id	uuid	FK → projects.id
standard_id	text	FK → audit_standards.id
title	text
description	text	nullable
created_at	timestamp
updated_at	timestamp

reports

A point-in-time snapshot within an audit. The exportable deliverable.

Column	Type	Notes
id	uuid
organization_id	text	FK → organizations.id
created_by	text	FK → users.id
audit_id	uuid	FK → audits.id
title	text
description	text	nullable
reported_at	timestamp	Date of the report
locked_at	timestamp	null = in progress (editable). Non-null = locked (read-only).
created_at	timestamp
updated_at	timestamp

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

results

A result for one criterion within one report.

Column	Type	Notes
id	uuid
report_id	uuid	FK → reports.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

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 report (for WCAG 3.0 Silver/Gold conformance).

Column	Type	Notes
id	uuid
report_id	uuid	FK → reports.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	Projects	Audits/project	Reports/audit
Free	1	3	10
Pro	Unlimited	Unlimited	Unlimited
Org	Unlimited	Unlimited	Unlimited