Skip to content
QA SyncQA Sync Docs

Database and Row Level Security

QA Sync relies on Postgres Row Level Security (RLS) for every application data boundary. The frontend hides what a role can’t do, but the database is what actually refuses it.

Schema at a glance

Section titled “Schema at a glance”

Identity and access

Section titled “Identity and access”
Table Purpose
auth.users Supabase Auth identities and credentials.
public.profiles The effective application user: role, status, app access (app_ids), tester groups (groups), legacy user ID. Keyed by auth.users.id.
public.access_directory Admin-managed provisioning directory. Holds the intended role, status, apps and groups for an email, including people who haven’t signed in yet.

Product structure and planning

Section titled “Product structure and planning”
Table Purpose
public.apps App catalogue.
public.tester_groups Tester groups, each owned by one app.
public.modules, public.submodules Testable areas.
public.tasks Tasks with instructions, expected result, assignment, planning fields and current_status.
public.milestones, public.sprints Planning containers.
public.workflow_dependencies Dependencies between apps, modules, submodules and tasks.

Apps, modules, submodules and tasks share workflow fields (current_status, progress_percentage, child_status_summary), planning fields, dependency state, latest QA status and release-readiness fields.

Workflow, QA and releases

Section titled “Workflow, QA and releases”
Table Purpose
public.task_requests Missing-coverage requests and developer suggestion escalations.
public.feedback_submissions Tester issue reports and suggestions.
public.qa_results QA runs and their outcomes.
public.approvals Approval records per item and approval type.
public.approval_requirements Approval rules.
public.status_history Workflow stage changes.

Collaboration and audit

Section titled “Collaboration and audit”
Table Purpose
public.entity_comments Comments and mentions.
public.notifications, public.notification_preferences In-app notifications and per-user preferences.
public.audit_logs Immutable audit trail.

Profiles and the access directory

Section titled “Profiles and the access directory”

Signing in only establishes identity. Access comes from public.profiles:

  1. The app reads the profile for auth.uid().
  2. If it’s missing, the app calls public.ensure_current_user_profile(), which finds the matching access_directory entry by email and creates or repairs the profile.
  3. A profile with no role, or a status other than Active, is routed to /access-pending.

Admins write to access_directory (and, for provisioned accounts, create the auth user) from the Users tab. Never fix access by inserting into profiles alone; keep auth, directory and profile aligned.

The RLS helper pattern

Section titled “The RLS helper pattern”

Policies that need the current user’s profile must call SECURITY DEFINER helper functions, not query public.profiles from inside a policy. Recursive policy checks on profiles fail with Postgres error 42P17 (fixed in 20260407113000_fix_profiles_policy_recursion.sql).

Representative helpers:

Helper Answers
current_user_has_active_admin_role() Is the caller an active Admin or Super Admin?
current_user_has_active_app_access(app_id) Can the caller see this app? Admins: always. Others: if the app is in their app_ids.
current_user_can_access_module(module_id) App access via the module’s app.
current_user_can_access_entity(type, id) Access to an app, module, submodule or task.
current_user_can_manage_qa_for_task(task_id) Admin, or a Tester whose groups overlap the task’s groups while it’s in Staging.
current_user_has_active_release_visibility_role() Admin, Super Admin, Product Manager, UI Designer, Client or Viewer.
role_requires_app_access(role) Every role except Admin and Super Admin.

Organisation isolation

Section titled “Organisation isolation”

Tenant isolation sits on top of these checks: every tenant-owned row belongs to one organisation, and policies require the caller to be an active member of that organisation before any role or app check applies. The design is documented in docs/multi-tenancy.md in the repository; follow it for any table you add.

Workflow RPCs

Section titled “Workflow RPCs”

Multi-step or privileged changes run as SECURITY DEFINER functions that re-check the caller:

Function Does
ensure_current_user_profile() Creates or repairs the caller’s profile from the access directory.
start_task_qa_run(task_id) Claims or creates the pending QA run for a staging task.
submit_task_qa_result(...) Finalises a QA run and moves the task (Passed → ready_to_ship, Failed → in_progress). A trigger records the task’s qa_approved approval: approved on Passed, rejected with the tester notes on Failed.
Task-request review Atomically reviews, publishes (creating the task), merges or rejects a request.
Approval decision Applies role rules (Product Manager, Client, Tester, Developer, Admin) and self-approval rules.
load_my_pending_approvals() Pending approvals on Ready to Ship or Production items that the caller’s role decides.
Promote to production Requires ready_to_ship; requires an override reason when not ready.
create_entity_comment(...) Checks entity access and extracts mentions.
mark_all_notifications_read() Marks the caller’s notifications read.
Audit log purge Deletes entries older than at least one month, admin only, and logs the purge.

Triggers maintain status rollups, pending QA runs on entering staging, approvals from rules, readiness, notifications and audit rows.

Who can call functions

Section titled “Who can call functions”

Function EXECUTE is an allowlist:

  • The anon key (signed-out browsers) can’t execute any function in the public schema.
  • Signed-in users can execute only the RPCs the web app calls, the helper functions RLS policies use, and SECURITY INVOKER functions, which run with the caller’s own privileges.
  • The analytics metrics views are read only through the load_analytics_* RPCs, never directly.
  • Platform operators are rows in public.platform_admins, checked with public.is_platform_admin().

npm run validate:local-env fails if the anon key can reach a privileged function or a metrics view.

Storage

Section titled “Storage”
Bucket Contents Access
qa-evidence QA run screenshots Authorised via the related QA run.
task-request-attachments Task request screenshots and recordings Admins and the request owner.
feedback-attachments Feedback screenshots Admins and relevant developers; testers for their own uploads.

All buckets are private. The app reads files through signed URLs, cached in memory by apps/web/src/api/storage-signed-urls.ts.

Changing the schema

Section titled “Changing the schema”
  • Every schema or policy change is a new migration in supabase/migrations/. Never patch tables by hand.
  • Use RLS helper functions for profile-dependent checks.
  • Add audit logging for new privileged mutations.
  • New functions are not callable from the browser by default. When the web app needs a new RPC, grant it in the same migration: grant execute on function public.my_rpc(...) to authenticated;. Never grant anon.
  • Apply locally with supabase db push --local (or supabase db reset to rebuild), then run npm run validate:local-env and npm run validate:core.
  • supabase db push without --local targets production. Run it only when releasing.