Database Schema

AllyProof uses Supabase (PostgreSQL) with Row Level Security (RLS) for multi-tenant data isolation. This page documents the core tables, their columns, and relationships.

Entity Relationship Overview

organizations
  ├── org_members (users ↔ organizations, many-to-many)
  ├── sites
  │     ├── scans
  │     │     ├── scan_pages
  │     │     └── violations
  │     │           └── ai_fix_suggestions
  │     ├── accessibility_statements
  │     └── vpat_reports
  ├── api_keys
  └── subscriptions

Core Tables

organizations

Top-level tenant. All data is scoped to an organization.

Column	Type	Description
`id`	uuid (PK)	Primary key
`name`	text	Organization display name
`slug`	text (unique)	URL-safe identifier
`scan_frequency`	text	`daily`, `weekly`, or `manual`
`plan`	text	`starter`, `agency`, or `enterprise`
`contact_email`	text	Accessibility contact email (used in statements)
`created_at`	timestamptz	Creation timestamp
`updated_at`	timestamptz	Last update timestamp

org_members

Join table linking users to organizations with roles.

Column	Type	Description
`id`	uuid (PK)	Primary key
`org_id`	uuid (FK)	References `organizations.id`
`user_id`	uuid (FK)	References `auth.users.id`
`role`	text	`owner`, `admin`, or `member`
`notify_scan_complete`	boolean	Email on scan completion
`notify_critical`	boolean	Email on critical violations
`created_at`	timestamptz	Join date

sites

Websites registered for scanning.

Column	Type	Description
`id`	uuid (PK)	Primary key
`org_id`	uuid (FK)	References `organizations.id`
`name`	text	Display name
`url`	text	Base URL
`verification_method`	text	`dns` or `meta`
`verification_token`	text	Token to verify ownership
`verified_at`	timestamptz	When ownership was verified (null if unverified)
`is_paused`	boolean	Whether scheduled scans are paused
`latest_score`	integer	Cached score from most recent scan
`created_at`	timestamptz	Creation timestamp

scans

Individual scan runs for a site.

Column	Type	Description
`id`	uuid (PK)	Primary key
`site_id`	uuid (FK)	References `sites.id`
`status`	text	`queued`, `running`, `completed`, `failed`
`trigger_source`	text	`manual`, `scheduled`, or `api`
`score`	integer	Calculated accessibility score (0-100)
`pages_scanned`	integer	Number of pages scanned
`violations_count`	integer	Total violation count
`duration_ms`	integer	Scan duration in milliseconds
`started_at`	timestamptz	When scanning began
`completed_at`	timestamptz	When scanning finished
`created_at`	timestamptz	When the scan was queued

scan_pages

Individual pages within a scan.

Column	Type	Description
`id`	uuid (PK)	Primary key
`scan_id`	uuid (FK)	References `scans.id`
`url`	text	Full page URL
`status_code`	integer	HTTP response status
`violations_count`	integer	Violations found on this page
`scanned_at`	timestamptz	When this page was scanned

violations

Individual accessibility violations found during scans.

Column	Type	Description
`id`	uuid (PK)	Primary key
`scan_id`	uuid (FK)	References `scans.id`
`scan_page_id`	uuid (FK)	References `scan_pages.id`
`rule_id`	text	axe-core or HTMLCS rule identifier
`engine`	text	`axe-core`, `htmlcs`, or `apca`
`severity`	text	`critical`, `serious`, `moderate`, `minor`
`wcag_criteria`	text[]	Mapped WCAG success criteria (e.g. `{1.1.1, 4.1.2}`)
`html_snippet`	text	Affected HTML element
`selector`	text	CSS selector for the element
`message`	text	Human-readable violation description
`status`	text	`open`, `in_progress`, `resolved`, `false_positive`
`created_at`	timestamptz	When the violation was found

ai_fix_suggestions

AI-generated fix suggestions for violations (via Claude Haiku).

Column	Type	Description
`id`	uuid (PK)	Primary key
`violation_id`	uuid (FK)	References `violations.id`
`suggestion`	text	AI-generated fix suggestion (markdown)
`fixed_html`	text	Suggested corrected HTML snippet
`confidence`	real	AI confidence score (0-1)
`model`	text	LLM model used (e.g. `claude-haiku-4.5`)
`created_at`	timestamptz	When the suggestion was generated

api_keys

API keys for CI/CD and programmatic access.

Column	Type	Description
`id`	uuid (PK)	Primary key
`org_id`	uuid (FK)	References `organizations.id`
`name`	text	Descriptive key name
`key_hash`	text	SHA-256 hash of the API key (key itself is not stored)
`key_prefix`	text	First 8 characters for identification
`last_used_at`	timestamptz	Last API call timestamp
`created_at`	timestamptz	Creation timestamp

Row Level Security

Every table has RLS enabled. Policies enforce that users can only access data belonging to organizations they are a member of. The general pattern:

-- Example RLS policy on sites table
CREATE POLICY "Users can view sites in their org"
  ON sites FOR SELECT
  USING (
    org_id IN (
      SELECT org_id FROM org_members
      WHERE user_id = auth.uid()
    )
  );

Write policies additionally check the user's role — only owner and admin roles can create, update, or delete records. The member role has read-only access.

Migrations

Schema migrations are managed via Supabase CLI. Migration files are in supabase/migrations/ and are applied with:

npx supabase db push     # Apply pending migrations to remote
npx supabase db reset    # Reset local database and reapply all migrations