Full RLS Access Inventory

This inventory records the reviewed production access used to build 20260610_critical_full_rls.sql. Tests and fixtures are excluded.

Client Classes

Request scoped: publishable key plus the original user bearer token.
Tenant system: publishable key plus a five-minute ES256 token containing role=authenticated, app_role=system, and one clinic_id.
Privileged: service-role key, restricted by the static import allowlist.

Request-scoped and tenant-system clients share the authenticated database role. Policies additionally validate app_role, the current database user record, and clinic activation state. Tenant-system access requires app_role=system and the fixed system UUID subject.

Authenticated Table Operations

Table	Operations	Production use
`appointments`	`SELECT, INSERT, UPDATE`	Routes, analytics, chatbot, attendance, reminders, tenant jobs
`audit_logs`	`INSERT`	Booking audit event
`chat_sessions`	`SELECT, INSERT, UPDATE`	WhatsApp conversation state
`clinics`	`SELECT`, column-limited `UPDATE`	Settings, subscription checks, WhatsApp, tenant jobs
`doctor_breaks`	`SELECT`	Availability
`doctor_vacations`	`SELECT`	Availability
`doctor_working_hours`	`SELECT, INSERT, DELETE`	Availability and doctor schedule replacement
`doctors`	`SELECT, INSERT, UPDATE`	Doctor routes, calendar, availability, chatbot
`faqs`	`SELECT, INSERT, UPDATE, DELETE`	FAQ routes and chatbot
`patients`	`SELECT, INSERT, UPDATE`	Patient routes, chatbot, attendance, recall, dental
`payment_installments`	`SELECT, INSERT, UPDATE`	Dental payment plans
`payment_plans`	`SELECT, INSERT, UPDATE`	Dental payment plans and reminder job
`payments`	`SELECT, INSERT, UPDATE`	Dental payment transaction RPC and edits
`recall_campaigns`	`SELECT, INSERT, UPDATE`	Dental recall and recall job
`recall_contacts`	`SELECT, INSERT, UPDATE`	Dental recall and recall job
`treatment_plan_templates`	`SELECT, INSERT, DELETE`	Dental templates
`treatment_plans`	`SELECT, INSERT, UPDATE`	Dental treatment plans
`treatment_steps`	`SELECT, INSERT, UPDATE, DELETE`	Dental treatment steps
`users`	`SELECT, INSERT, UPDATE`	Team management, profile, subscription checks
`whatsapp_onboarding_events`	`INSERT`	WhatsApp onboarding audit events

No authenticated table privileges are granted for waitlist, whatsapp_templates, invite_codes, demo_requests, or ls_orders.

Authenticated clinics updates require an active admin or tenant-system claim and are limited to profile, attendance, clinic type, bot, and WhatsApp configuration/status columns. Subscription and activation columns are excluded.

Direct users reads exclude password hashes and recovery-token columns. Direct clinics reads exclude WhatsApp access and verify tokens. Staff can update only their own profile/password/photo fields; admins can create team members and change another member’s role or activation state. Doctor, schedule, clinic, and FAQ mutations enforce the same admin rules as the HTTP routes.

Child-table WITH CHECK policies validate tenant ownership of related doctors, patients, plans, campaigns, appointments, and users. An unexpired JWT stops working immediately when its user or clinic is deactivated or when the stored role no longer matches app_role.

Privileged Table Operations

Table	Operations	Reason
`clinics`	`SELECT, INSERT, UPDATE, DELETE`	Bootstrap, superadmin, enumeration, webhook discovery, subscription events
`users`	`SELECT, INSERT, UPDATE`	Authentication, recovery, superadmin, subscription events
`doctors`	`SELECT`	Public calendar discovery service
`patients`	`SELECT`	Embedded trusted appointment discovery
`appointments`	`SELECT`	Webhook/job/public check-in discovery and superadmin metrics
`invite_codes`	`SELECT, INSERT, UPDATE, DELETE`	Bootstrap, superadmin, LemonSqueezy
`demo_requests`	`SELECT, INSERT, UPDATE`	Public intake and superadmin
`ls_orders`	`SELECT, INSERT`	LemonSqueezy idempotency and superadmin metrics

All other public-table privileges are revoked from service_role. RLS remains unforced so the role keeps its intentional bypass behavior where it has SQL privileges.

The postgres default privileges for new public tables, sequences, and functions are revoked from application roles. Future migrations must grant each required operation explicitly.

RPC Execution

Function	`authenticated`	Tenant source
`increment_no_show_count(uuid)`	`EXECUTE`	JWT `clinic_id`
`decrement_payment_amount(uuid, numeric)`	`EXECUTE`	JWT `clinic_id`
`record_payment_transaction(uuid, numeric, text, text, uuid)`	`EXECUTE`	JWT `clinic_id`; `recorded_by` must equal `auth.uid()`
`get_recall_eligible_patients(integer, integer, uuid)`	`EXECUTE`	JWT `clinic_id`; campaign values validated
`change_current_user_password(text, text, text, text)`	`EXECUTE`	Active `auth.uid()`; verifies the current password and atomically writes a cost-12 bcrypt hash
`clinic_has_whatsapp_access_token()`	`EXECUTE`	Active JWT tenant; returns only a boolean
`get_clinic_whatsapp_credentials()`	`EXECUTE`	Active tenant-system claim only
`increment_payment_amount(uuid, numeric)`	none	Rewritten but not called by production code

Application mutation RPCs use SECURITY INVOKER. The three narrow credential/password RPCs use SECURITY DEFINER with search_path = '', no caller-controlled tenant argument, and explicit active-claim checks. PUBLIC, anon, and service_role execution are revoked.

Storage

The public avatars bucket keeps public downloads. Authenticated Storage API SELECT, INSERT, and UPDATE policies permit only:

users/<auth.uid()>.jpg
users/<auth.uid()>.png
users/<auth.uid()>.webp

SELECT is included because Supabase Storage upsert requires it in addition to INSERT and UPDATE. All three policies also require an active user and clinic.