supabase-data-handling

Supabase Data Handling

Overview

GDPR and CCPA compliance with Supabase requires a layered approach: Row Level Security (RLS) for tenant data isolation, supabase.auth.admin.deleteUser() for right-to-deletion requests, SQL-based data exports for subject access requests, PII detection across database columns, automated retention policies using pg_cron, and point-in-time recovery for backup/restore. This skill implements every compliance requirement using real Supabase SDK methods and PostgreSQL features.

When to use: Implementing GDPR right-to-deletion, responding to data subject access requests (DSARs), auditing PII in your database, configuring automated data retention, setting up tenant isolation with RLS, or planning 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

Configure Row Level Security to ensure users can only access their own data, and identify which columns contain PII.

Tenant isolation with RLS:

-- Enable RLS on all tables containing user data
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;

-- Users can only read their own profile
CREATE POLICY "users_read_own_profile" ON public.profiles
  FOR SELECT USING (auth.uid() = id);

-- Users can update their own profile
CREATE POLICY "users_update_own_profile" ON public.profiles
  FOR UPDATE USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- Users can only see their own orders
CREATE POLICY "users_read_own_orders" ON public.orders
  FOR SELECT USING (auth.uid() = user_id);

-- Organization-scoped isolation (multi-tenant)
CREATE POLICY "org_members_read_documents" ON public.documents
  FOR SELECT USING (
    org_id IN (
      SELECT org_id FROM public.org_members
      WHERE user_id = auth.uid()
    )
  );

PII column audit — identify sensitive data across your schema:

-- Find columns likely containing PII based on naming patterns
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND (
    column_name ILIKE '%email%'
    OR column_name ILIKE '%phone%'
    OR column_name ILIKE '%name%'
    OR column_name ILIKE '%address%'
    OR column_name ILIKE '%ssn%'
    OR column_name ILIKE '%birth%'
    OR column_name ILIKE '%ip%'
    OR column_name ILIKE '%location%'
  )
ORDER BY table_name, column_name;

-- Add comments to mark PII columns for documentation
COMMENT ON COLUMN public.profiles.email IS 'PII: email address — GDPR Art. 4(1)';
COMMENT ON COLUMN public.profiles.full_name IS 'PII: personal name — GDPR Art. 4(1)';
COMMENT ON COLUMN public.profiles.phone IS 'PII: phone number — GDPR Art. 4(1)';

-- Create a PII registry view
CREATE OR REPLACE VIEW pii_registry AS
SELECT c.table_name, c.column_name, c.data_type,
       pg_catalog.col_description(
         (quote_ident(c.table_schema) || '.' || quote_ident(c.table_name))::regclass,
         c.ordinal_position
       ) AS pii_classification
FROM information_schema.columns c
WHERE c.table_schema = 'public'
  AND pg_catalog.col_description(
    (quote_ident(c.table_schema) || '.' || quote_ident(c.table_name))::regclass,
    c.ordinal_position
  ) LIKE 'PII:%';

PII detection from the SDK:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  { auth: { autoRefreshToken: false, persistSession: false } }
);

// Scan a table for PII patterns in text columns
async function scanTableForPII(tableName: string, sampleSize = 100) {
  const PII_PATTERNS = [
    { type: 'email', regex: /[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}/g },
    { type: 'phone', regex: /\b\d{3}[-.]?\d{3}[-.]?\d{4}\b/g },
    { type: 'ssn', regex: /\b\d{3}-\d{2}-\d{4}\b/g },
    { type: 'ip_address', regex: /\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b/g },
  ];

  const { data, error } = await supabase
    .from(tableName)
    .select('*')
    .limit(sampleSize);

  if (error) throw error;

  const findings: { column: string; type: string; count: number }[] = [];

  for (const row of data ?? []) {
    for (const [column, value] of Object.entries(row)) {
      if (typeof value !== 'string') continue;
      for (const pattern of PII_PATTERNS) {
        const matches = value.match(pattern.regex);
        if (matches) {
          findings.push({ column, type: pattern.type, count: matches.length });
        }
      }
    }
  }

  return findings;
}

Step 2: User Deletion and Data Export

Implement GDPR Article 17 (right to erasure) with auth.admin.deleteUser() and Article 15 (right of access) with SQL-based data export.

Right to deletion — complete user erasure:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  { auth: { autoRefreshToken: false, persistSession: false } }
);

interface DeletionResult {
  userId: string;
  tablesProcessed: string[];
  storageFilesDeleted: number;
  authDeleted: boolean;
  auditLogId: string;
  completedAt: string;
}

async function deleteUserData(userId: string): Promise<DeletionResult> {
  const tablesProcessed: string[] = [];
  let storageFilesDeleted = 0;

  // 1. Delete user data from application tables (cascade order)
  const tablesToPurge = ['comments', 'orders', 'documents', 'profiles'];

  for (const table of tablesToPurge) {
    const { error } = await supabase
      .from(table)
      .delete()
      .eq('user_id', userId);

    if (error && !error.message.includes('does not exist')) {
      console.error(`Failed to delete from ${table}:`, error.message);
    } else {
      tablesProcessed.push(table);
    }
  }

  // 2. Delete user files from storage
  const { data: buckets } = await supabase.storage.listBuckets();
  for (const bucket of buckets ?? []) {
    const { data: files } = await supabase.storage
      .from(bucket.name)
      .list(`users/${userId}`);

    if (files && files.length > 0) {
      const paths = files.map((f) => `users/${userId}/${f.name}`);
      const { error } = await supabase.storage
        .from(bucket.name)
        .remove(paths);

      if (!error) storageFilesDeleted += paths.length;
    }
  }

  // 3. Delete the auth user (removes from auth.users)
  const { error: authError } = await supabase.auth.admin.deleteUser(userId);
  const authDeleted = !authError;

  if (authError) {
    console.error('Auth deletion failed:', authError.message);
  }

  // 4. Create audit log entry (required — must survive deletion)
  const { data: auditEntry } = await supabase
    .from('gdpr_audit_log')
    .insert({
      action: 'USER_DELETION',
      subject_id: userId,
      tables_purged: tablesProcessed,
      storage_files_deleted: storageFilesDeleted,
      auth_deleted: authDeleted,
      performed_by: 'system',
      legal_basis: 'GDPR Article 17 — Right to Erasure',
    })
    .select('id')
    .single();

  return {
    userId,
    tablesProcessed,
    storageFilesDeleted,
    authDeleted,
    auditLogId: auditEntry?.id ?? 'unknown',
    completedAt: new Date().toISOString(),
  };
}

// GDPR audit log table (create this migration)
// CREATE TABLE gdpr_audit_log (
//   id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
//   action text NOT NULL,
//   subject_id uuid NOT NULL,
//   tables_purged text[] DEFAULT '{}',
//   storage_files_deleted int DEFAULT 0,
//   auth_deleted boolean DEFAULT false,
//   performed_by text NOT NULL,
//   legal_basis text,
//   created_at timestamptz DEFAULT now()
// );
// -- Audit logs must NEVER be deleted (compliance requirement)
// ALTER TABLE gdpr_audit_log ENABLE ROW LEVEL SECURITY;
// CREATE POLICY "admin_only" ON gdpr_audit_log FOR ALL USING (false);

Data subject access request (DSAR) — export all user data:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  { auth: { autoRefreshToken: false, persistSession: false } }
);

interface DataExport {
  exportedAt: string;
  subjectId: string;
  legalBasis: string;
  data: Record<string, unknown[]>;
  storageFiles: string[];
}

async function exportUserData(userId: string): Promise<DataExport> {
  const exportData: Record<string, unknown[]> = {};

  // Export from each table containing user data
  const tables = ['profiles', 'orders', 'documents', 'comments'];

  for (const table of tables) {
    const { data, error } = await supabase
      .from(table)
      .select('*')
      .eq('user_id', userId);

    if (!error && data) {
      exportData[table] = data;
    }
  }

  // List user files in storage
  const storageFiles: string[] = [];
  const { data: buckets } = await supabase.storage.listBuckets();
  for (const bucket of buckets ?? []) {
    const { data: files } = await supabase.storage
      .from(bucket.name)
      .list(`users/${userId}`);

    for (const file of files ?? []) {
      storageFiles.push(`${bucket.name}/users/${userId}/${file.name}`);
    }
  }

  // Log the export for compliance
  await supabase.from('gdpr_audit_log').insert({
    action: 'DATA_EXPORT',
    subject_id: userId,
    performed_by: 'system',
    legal_basis: 'GDPR Article 15 — Right of Access',
  });

  return {
    exportedAt: new Date().toISOString(),
    subjectId: userId,
    legalBasis: 'GDPR Article 15 — Right of Access',
    data: exportData,
    storageFiles,
  };
}

Step 3: Retention Policies and Backup/Restore

Automate data retention with pg_cron scheduled jobs and configure backup/restore using Supabase's point-in-time recovery.

Automated retention policies with pg_cron:

-- Enable pg_cron extension (Supabase Pro plan)
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Retention policy: delete API logs older than 30 days
SELECT cron.schedule(
  'cleanup-api-logs',
  '0 3 * * *',  -- Run at 3 AM UTC daily
  $$DELETE FROM public.api_logs
    WHERE created_at < now() - interval '30 days'$$
);

-- Retention policy: delete error logs older than 90 days
SELECT cron.schedule(
  'cleanup-error-logs',
  '0 4 * * *',  -- Run at 4 AM UTC daily
  $$DELETE FROM public.error_logs
    WHERE created_at < now() - interval '90 days'$$
);

-- Retention policy: anonymize inactive user profiles after 2 years
SELECT cron.schedule(
  'anonymize-inactive-users',
  '0 5 * * 0',  -- Run weekly on Sunday at 5 AM UTC
  $$UPDATE public.profiles
    SET email = 'anonymized-' || id || '@deleted.local',
        full_name = 'Deleted User',
        phone = NULL,
        avatar_url = NULL,
        anonymized_at = now()
    WHERE last_active_at < now() - interval '2 years'
      AND anonymized_at IS NULL$$
);

-- View scheduled jobs
SELECT jobid, schedule, command, nodename
FROM cron.job
ORDER BY jobid;

-- Monitor job execution history
SELECT jobid, start_time, end_time, status, return_message
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 20;

Retention tracking from the SDK:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  { auth: { autoRefreshToken: false, persistSession: false } }
);

// Get retention policy status
async function getRetentionStatus() {
  const policies = [
    { table: 'api_logs', retentionDays: 30 },
    { table: 'error_logs', retentionDays: 90 },
    { table: 'audit_logs', retentionDays: null },  // Never delete
  ];

  for (const policy of policies) {
    const { count } = await supabase
      .from(policy.table)
      .select('*', { count: 'exact', head: true });

    let expiredCount = 0;
    if (policy.retentionDays) {
      const cutoff = new Date();
      cutoff.setDate(cutoff.getDate() - policy.retentionDays);

      const { count: expired } = await supabase
        .from(policy.table)
        .select('*', { count: 'exact', head: true })
        .lt('created_at', cutoff.toISOString());

      expiredCount = expired ?? 0;
    }

    console.log(`${policy.table}: ${count} total, ${expiredCount} expired, retention=${policy.retentionDays ?? 'forever'}`);
  }
}

Backup and restore:

# Point-in-time recovery (PITR) — available on Pro plan
# Configured in Supabase Dashboard → Database → Backups

# Manual backup via pg_dump (for migration or offline backup)
pg_dump "postgres://postgres.<project-ref>:<password>@aws-0-<region>.pooler.supabase.com:5432/postgres" \
  --format=custom \
  --no-owner \
  --no-privileges \
  --file=backup-$(date +%Y%m%d).dump

# Restore to a different project (e.g., staging)
pg_restore \
  --dbname="postgres://postgres.<staging-ref>:<password>@aws-0-<region>.pooler.supabase.com:5432/postgres" \
  --no-owner \
  --no-privileges \
  --clean \
  backup-20260322.dump

# Export specific tables as CSV for DSAR compliance
psql "postgres://postgres.<project-ref>:<password>@aws-0-<region>.pooler.supabase.com:5432/postgres" \
  -c "\COPY (SELECT * FROM profiles WHERE id = 'user-uuid') TO 'user-export.csv' CSV HEADER"

Output

After completing this skill, you will have:

• RLS tenant isolation — row-level security policies ensuring users only access 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

Example 1 — Handle a GDPR deletion request:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(url, serviceRoleKey, {
  auth: { autoRefreshToken: false, persistSession: false },
});

// API endpoint for GDPR deletion
async function handleDeletionRequest(userId: string) {
  // Verify the request is legitimate (e.g., authenticated user or admin)
  const result = await deleteUserData(userId);

  console.log(`User ${userId} deleted:`, {
    tables: result.tablesProcessed.join(', '),
    files: result.storageFilesDeleted,
    auth: result.authDeleted,
    auditId: result.auditLogId,
  });

  // GDPR requires completion within 30 days
  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,}'
UNION ALL
SELECT 'orders', 'notes',
       count(*)
FROM public.orders
WHERE notes ~ '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}';

Example 3 — Verify retention job execution:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(url, serviceRoleKey, {
  auth: { autoRefreshToken: false, persistSession: false },
});

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
• GDPR Developer Guide
• 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