supabase-data-handling（Supabase 数据 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

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

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

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