Implement Supabase PII handling, data retention, and GDPR/CCPA compliance patterns.
Use when handling sensitive data, implementing data redaction, configuring retention policies,
or ensuring compliance with privacy regulations for Supabase integrations.
Trigger with phrases like "supabase data", "supabase PII",
"supabase GDPR", "supabase data retention", "supabase privacy", "supabase CCPA".
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 policy per access pattern:
USING (auth.uid() = ...)
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}`);
}
}
