Soft Deletes and Audit Trails
Why Not Just DELETE?
Hard deletion (removing rows) has consequences:
- Data is gone: No recovery without backups
- References break: Foreign key constraints or dangling references
- No history: Can't see what was deleted or when
- Compliance issues: Regulations may require retention
Soft deletes and audit trails address these concerns.
Soft Delete Pattern
Instead of deleting rows, mark them as deleted:
CREATE TABLE posts (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid REFERENCES auth.users(id),
title text NOT NULL,
content text,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now(),
deleted_at timestamptz -- NULL = not deleted
);
Soft Delete RLS
-- Only show non-deleted posts
CREATE POLICY "View active posts"
ON posts
AS RESTRICTIVE
FOR SELECT
USING (deleted_at IS NULL);
-- Update policy (for soft delete)
CREATE POLICY "Owner can update/delete own posts"
ON posts FOR UPDATE
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());
Soft Delete Operation
// Instead of delete
await supabase
.from('posts')
.update({ deleted_at: new Date().toISOString() })
.eq('id', postId)
// Or with RPC for cleaner API
await supabase.rpc('soft_delete_post', { post_id: postId })
CREATE FUNCTION soft_delete_post(post_id uuid)
RETURNS void AS $$
UPDATE posts SET deleted_at = now() WHERE id = post_id AND user_id = auth.uid()
$$ LANGUAGE sql SECURITY DEFINER;
Restoring Soft-Deleted Data
// Restore a post
await supabase
.from('posts')
.update({ deleted_at: null })
.eq('id', postId)
Admin View of Deleted Items
-- Admin policy to see all including deleted
CREATE POLICY "Admins see everything"
ON posts FOR SELECT
USING (is_admin()); -- Bypasses the restrictive policy
Automatic Cleanup (Optional)
Permanently delete old soft-deleted records:
-- Function to purge old deleted records
CREATE FUNCTION purge_old_deleted_records()
RETURNS void AS $$
BEGIN
DELETE FROM posts
WHERE deleted_at IS NOT NULL
AND deleted_at < now() - interval '90 days';
END;
$$ LANGUAGE plpgsql;
-- Schedule with pg_cron (if available)
SELECT cron.schedule('purge-deleted', '0 0 * * *', 'SELECT purge_old_deleted_records()');
Audit Trail Pattern
Track all changes to important tables:
CREATE TABLE audit_log (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
table_name text NOT NULL,
record_id uuid NOT NULL,
action text NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
old_data jsonb,
new_data jsonb,
changed_by uuid REFERENCES auth.users(id),
changed_at timestamptz DEFAULT now() NOT NULL,
ip_address inet,
user_agent text
);
-- Index for querying
CREATE INDEX idx_audit_log_table_record ON audit_log(table_name, record_id);
CREATE INDEX idx_audit_log_user ON audit_log(changed_by);
CREATE INDEX idx_audit_log_time ON audit_log(changed_at);
Generic Audit Trigger
CREATE FUNCTION audit_trigger_func()
RETURNS trigger AS $$
BEGIN
INSERT INTO audit_log (
table_name,
record_id,
action,
old_data,
new_data,
changed_by
)
VALUES (
TG_TABLE_NAME,
COALESCE(NEW.id, OLD.id),
TG_OP,
CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END,
auth.uid()
);
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Apply to tables
CREATE TRIGGER posts_audit
AFTER INSERT OR UPDATE OR DELETE ON posts
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();
CREATE TRIGGER profiles_audit
AFTER INSERT OR UPDATE OR DELETE ON profiles
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();
Querying Audit Logs
History of a Record
const { data: history } = await supabase
.from('audit_log')
.select('*')
.eq('table_name', 'posts')
.eq('record_id', postId)
.order('changed_at', { ascending: false })
Changes by User
const { data: userActivity } = await supabase
.from('audit_log')
.select('*')
.eq('changed_by', userId)
.order('changed_at', { ascending: false })
.limit(50)
Changes in Time Range
const { data: recentChanges } = await supabase
.from('audit_log')
.select('*')
.gte('changed_at', startDate)
.lte('changed_at', endDate)
Audit Log RLS
Audit logs need careful access control:
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
-- Users can see audit for their own records
CREATE POLICY "Users see own record audits"
ON audit_log FOR SELECT
USING (changed_by = auth.uid());
-- Admins see all
CREATE POLICY "Admins see all audits"
ON audit_log FOR SELECT
USING (is_admin());
-- No one can modify audit logs (append-only)
-- No INSERT/UPDATE/DELETE policies for regular users
Change Tracking Columns
Simpler alternative for basic tracking:
CREATE TABLE posts (
id uuid PRIMARY KEY,
-- Content
title text,
content text,
-- Tracking
created_at timestamptz DEFAULT now() NOT NULL,
created_by uuid REFERENCES auth.users(id),
updated_at timestamptz DEFAULT now() NOT NULL,
updated_by uuid,
deleted_at timestamptz,
deleted_by uuid
);
-- Auto-update tracking columns
CREATE FUNCTION update_tracking_columns()
RETURNS trigger AS $$
BEGIN
NEW.updated_at = now();
NEW.updated_by = auth.uid();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER posts_tracking
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_tracking_columns();
Combining Patterns
Full-featured data management:
CREATE TABLE documents (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid REFERENCES organizations(id) NOT NULL,
-- Content
title text NOT NULL,
content text,
status text DEFAULT 'draft',
-- Soft delete
deleted_at timestamptz,
deleted_by uuid,
-- Versioning (simple)
version integer DEFAULT 1,
-- Tracking
created_at timestamptz DEFAULT now(),
created_by uuid REFERENCES auth.users(id),
updated_at timestamptz DEFAULT now(),
updated_by uuid
);
-- RLS combines tenant isolation and soft delete
CREATE POLICY "Org members see active documents"
ON documents FOR SELECT
USING (
is_org_member(organization_id)
AND deleted_at IS NULL
);
Key Takeaways
- Soft delete preserves data: Mark as deleted instead of removing
- RESTRICTIVE policies filter deleted: Automatic exclusion from queries
- Audit logs track changes: Who changed what, when
- Triggers automate logging: Consistent, reliable capture
- Immutable audit logs: Append-only for integrity
- Combine patterns: Soft delete + audit + tracking
Module Summary
In this module, you've learned:
- Schema design principles for Supabase
- The user profiles pattern for extending auth
- Multi-tenancy approaches for SaaS applications
- Soft deletes and audit trails for data integrity
Good schema design pays dividends throughout your application's lifecycle. Take the time to design well upfront.
Data deletion is often a business process, not just a technical operation. Soft deletes give you the flexibility to handle undelete requests, compliance requirements, and data recovery without restoring from backups.
التالي · الدرس 33 من 40
Security Fundamentals for Supabase
قراءة 4 دقيقة
أنهيت 32 من 40 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

