Supabase data handling

Supabase Data Handling

Overview

GDPR and CCPA compliance with Supabase uses a layered approach: Row Level Security (RLS) for tenant data isolation, supabase.auth.admin.deleteUser() for right-to-deletion, SQL-based exports for subject access requests, PII detection across columns, automated retention with pg_cron, and point-in-time recovery for backup/restore. Every requirement maps to a real Supabase SDK method or PostgreSQL feature.

When to use: Implement GDPR right-to-deletion, respond to data subject access requests (DSARs), audit PII in the database, configure automated retention, set up tenant isolation with RLS, or plan backup/restore procedures.

Prerequisites

• @supabase/supabase-js v2+ with service role key for admin operations
• Supabase project on Pro plan (for pg_cron and point-in-time recovery)
• Understanding of GDPR Articles 15-17 (access, rectification, erasure)
• Database access via SQL Editor or psql for schema changes

Instructions

Step 1: RLS for Data Isolation and PII Column Management

Enable RLS on every table holding user data, then classify PII columns so later deletion and export steps know what to touch. The RLS skeleton is one USING (auth.uid() = ...) policy per access pattern:

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users_read_own_profile" ON public.profiles
  FOR SELECT USING (auth.uid() = id);

Pair the policies with a PII audit — an information_schema.columns scan by naming pattern, COMMENT ON COLUMN tags, a pii_registry view, and an SDK-side regex scanner for emails, phones, SSNs, and IPs.

See RLS and PII reference for the full multi-tenant policy set, the PII audit SQL, the pii_registry view, and the scanTableForPII() SDK scanner.

Step 2: User Deletion and Data Export

Implement GDPR Article 17 (erasure) and Article 15 (access). Deletion runs in cascade order — application tables, then storage files, then the auth user, then an immutable audit-log entry that must survive the erasure:

// Cascade order matters: children before parents, auth user last
const tablesToPurge = ['comments', 'orders', 'documents', 'profiles'];
// ...delete rows, remove storage files, then:
await supabase.auth.admin.deleteUser(userId);
await supabase.from('gdpr_audit_log').insert({ action: 'USER_DELETION', subject_id: userId });

Export is the inverse: read every user-scoped table plus storage listings into one JSON payload and log a DATA_EXPORT audit row.

See deletion and export reference for the full deleteUserData() pipeline (with the gdpr_audit_log migration and locked-down RLS policy) and the exportUserData() DSAR builder.

Step 3: Retention Policies and Backup/Restore

See retention policies and backup/restore for pg_cron automated retention schedules (30/90/730-day tiers), SDK-based retention monitoring, pg_dump/pg_restore commands, and point-in-time recovery configuration.

Output

Completing this skill produces:

• RLS tenant isolation — row-level policies ensuring users access only their own data
• PII column registry — documented and classified PII columns across all tables
• PII scanner — SDK-based pattern detection for emails, phones, SSNs, and IPs in text columns
• User deletion pipeline — complete auth.admin.deleteUser() flow with cascade table deletion, storage cleanup, and audit logging
• Data export — DSAR-compliant export of all user data from tables and storage
• GDPR audit log — immutable log of all deletion and export operations with legal basis
• Automated retention — pg_cron jobs for 30/90/730-day retention tiers
• Backup/restore — pg_dump/pg_restore commands and PITR configuration

Error Handling

Error	Cause	Solution
auth.admin.deleteUser() returns 404	User already deleted or wrong ID	Check auth.users table; may have been deleted by another process
violates foreign key constraint during deletion	Child rows reference user	Delete in cascade order (comments → orders → profiles) or use ON DELETE CASCADE
permission denied for function cron.schedule	pg_cron not enabled or wrong plan	Enable pg_cron extension; requires Supabase Pro plan
pg_dump: connection refused	Using wrong port or pooler URL	Use direct connection (port 5432), not pooler (port 6543) for pg_dump
RLS policy blocks admin operations	Service role key not used	Use createClient with SUPABASE_SERVICE_ROLE_KEY to bypass RLS
Audit log entries missing	Table has RLS blocking inserts	Use SECURITY DEFINER function or service role for audit writes
Retention job not running	pg_cron job disabled or errored	Check cron.job_run_details for error messages

Examples

Each example uses the functions defined in the reference files above. See deletion and export reference for deleteUserData().

Example 1 — Handle a GDPR deletion request:

// Wraps deleteUserData() behind an API endpoint; GDPR requires completion within 30 days
async function handleDeletionRequest(userId: string) {
  const result = await deleteUserData(userId);
  return { status: 'completed', auditId: result.auditLogId };
}

Example 2 — Quick PII audit:

-- Count rows with email-like patterns in unexpected columns
SELECT 'profiles' AS table_name, 'bio' AS column_name, count(*) AS rows_with_email
FROM public.profiles
WHERE bio ~ '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}';

Example 3 — Verify retention job execution:

async function checkRetentionJobs() {
  const { data, error } = await supabase.rpc('get_cron_status');
  if (error) throw error;
  for (const job of data ?? []) {
    console.log(`Job "${job.jobname}": last_run=${job.last_run}, status=${job.status}`);
  }
}

Resources

• Row Level Security — Supabase Docs
• Auth Admin deleteUser — Supabase Docs
• Database Backups — Supabase Docs
• pg_cron Extension — Supabase Docs
• CCPA Compliance Guide

Next Steps

• For enterprise role-based access control, see supabase-enterprise-rbac
• For security hardening and API key scoping, see supabase-security-basics
• For observability and audit trail monitoring, see supabase-observability